class LikePost(db.Model):
__tablename__ = 'LikePost'
PostId = db.Column(db.INT, db.ForeignKey('Post.postID'))
UserId = db.Column(db.CHAR(14), db.ForeignKey('User.userID'))
def __init__(self, pID, uID):
self.PostId = pID
self.UserId = uID
class LikeComment(db.Model):
__tablename__ = 'LikeComment'
CommentId = db.Column(db.INT, db.ForeignKey('Comment.commentID'))
UserId = db.Column(db.CHAR(14), db.ForeignKey('User.userID'))
def __init__(self, cID, uID):
self.CommentId = cID
self.UserId = uID
class Message(db.Model):
__tablename__ = 'Message'
MessageId = db.Column(db.INT, primary_key=True)
MDate = db.Column(db.DATETIME)
MSubject = db.Column(db.VARCHAR(100))
MContent = db.Column(db.TEXT)
MSenderId = db.Column(db.CHAR(14), db.ForeignKey('User.userID'))
MReceiverId = db.Column(db.CHAR(14), db.ForeignKey('User.userID'))
def __init__(self, mid, mdate, msub, mcont, sendid, recvid):
self.MessageId = mid
self.MDate = mdate
self.MSubject = msub
self.MContent = mcont
self.MSenderId = sendid
self.MReceiverId = recvid
class Sales(db.Model):
__tablename__ = 'Sales'
TransactionID = db.Column(db.INT, primary_key=True)
AdvertisementID = db.Column(
db.INT, db.ForeignKey('Advertisements.AdvertisementID'))
NumberofUnits = db.Column(db.INT)
AccountNumber = db.Column(db.INT)
UserID = db.Column(db.CHAR(14))
class Advertisements(db.Model):
__tablename__ = 'Advertisements'
AdvertisementID = db.Column(db.INT, primary_key=True, autoincrement=True)
EmployeeID = db.Column(db.INT, db.ForeignKey('Employee.UserID'))
MerchandiseType = db.Column(db.CHAR(50))
Company = db.Column(db.CHAR(50))
ItemName = db.Column(db.CHAR(70))
UnitPrice = db.Column(db.REAL)
NoAvailableUnits = db.Column(db.INT)
def __init__(self, eid, mt, co, iname, uprice, nounits):
self.EmployeeID = eid
self.MerchandiseType = mt
self.Company = co
self.ItemName = iname
self.UnitPrice = uprice
self.NoAvailableUnite = nounits
class Post(db.Model):
__tablename__ = 'Post'
postID = db.Column(db.INT, primary_key=True, autoincrement=True)
owner = db.Column(db.CHAR(14), db.ForeignKey('User.userID'))
pageID = db.Column(db.CHAR(14), db.ForeignKey('Page.pageID'))
postDate = db.Column(db.DATETIME)
content = db.Column(db.TEXT)
commentCount = db.Column(db.INT)
likesCount = db.Column(db.INT)
def __init__(self,pid,owner,paid,pdate,content,ccount,lcount):
self.postId = pid
self.owner = owner
self.pageID=paid
self.postDate=pdate
self.content = content
self.commentCount = ccount
self.likesCount = lcount
class Page(db.Model):
__tablename__ = 'Page'
pageID = db.Column(db.INT, primary_key=True, autoincrement=True)
Powner = db.Column(db.CHAR(14), db.ForeignKey('User.userID'))
fGroup = db.Column(db.INT, db.ForeignKey('Group.groupID'))
postCount = db.Column(db.INT)
def __init__(self, pID, owner, group, postCount):
self.pageID = pID
self.Powner = owner
self.fGroup = group
self.postCount = postCount
class Member(db.Model):
__tablename__ = 'Member'
groupID = db.Column(db.INT,
db.ForeignKey('fGroup.groupID'),
primary_key=True)
membership = db.column(db.VARCHAR(10))
userID = db.Column(db.CHAR(14),
db.ForeignKey('User.userID'),
primary_key=True)
def __init__(self, groupID, membership, userID):
self.groupID = groupID
self.membership = membership
self.userID = userID
class Comment(db.Model):
__tablename__ = 'Comment'
commentID = db.Column(db.INT, primary_key=True, autoincrement=True)
postID = db.Column(db.INT, db.ForeignKey('Post.postID'))
created = db.Column(db.DATE)
content = db.Column(db.TEXT)
author = db.Column(db.CHAR(14), db.ForeignKey('User.userID'))
likesCount = db.Column(db.INT)
def __init__(self, coID, poID, cre, con, aut, lCnt):
self.commentID = coID
self.postID = poID
self.created = cre
self.content = con
self.author = aut
self.author = lCnt
class User(db.Model, UserMixin):
__tablename__ = 'User'
userID = db.Column(db.CHAR(14), primary_key=True)
passwd = db.Column(db.VARCHAR(100))
lastName = db.Column(db.String(15))
firstName = db.Column(db.String(15))
email = db.Column(db.VARCHAR(50))
address = db.Column(db.String(25))
city = db.Column(db.String(25))
state = db.Column(db.String(25))
rating = db.Column(db.REAL)
userType = db.Column(db.String(15))
def __init__(self, uid, pwd, fname, lname, email, address, city, state,
rating, type):
self.userID = uid
self.passwd = generate_password_hash(pwd)
self.lastName = lname
self.firstName = fname
self.email = email
self.address = address
self.city = city
self.state = state
self.rating = rating
self.userType = type
def check_passwd(self, passwd):
return check_password_hash(self.passwd, passwd)
def get_id(self):
return self.userID
@staticmethod
def registerUser(form):
connection = db.engine.raw_connection()
cursor = connection.cursor()
s = generate_password_hash(form.password.data)
print(s, len(s))
cursor.callproc('registerUser', [
form.username.data, s, form.lastname.data, form.firstname.data,
form.email.data, form.address.data, form.city.data,
form.state.data, 0, form.userType.data
])
cursor.close()
connection.commit()
class Employee(db.Model):
__tablename__ = 'Employee'
SSN = db.Column(db.INT, primary_key=True)
UserID = db.Column(db.CHAR(14))
FirstName = db.Column(db.CHAR(50))
LastName = db.Column(db.CHAR(50))
StartDate = db.Column(db.DATE)
Address = db.Column(db.CHAR(70))
ZipCode = db.Column(db.INT)
State = db.Column(db.CHAR(50))
HourlyRate = db.Column(db.INT)
CName = db.Column(db.CHAR(50))
def __init__(self, uid, usern, firstname, lastname, sdate, address,
zipcode, state, hourlyrate, cname):
self.SSN = uid
self.userId = usern
self.FirstName = firstname
self.LastName = lastname
self.StartDate = sdate
self.Address = address
self.ZipCode = zipcode
self.State = state
self.HourlyRate = hourlyrate
self.CName = cname
@staticmethod
def addEmployee(form):
connection = db.engine.raw_connection()
cursor = connection.cursor()
s = generate_password_hash(form.password.data)
cursor.callproc('AddEmployee', [
form.socialsecurity.data, form.username.data, form.firstname.data,
form.lastname.data, form.address.data, form.zipcode.data,
form.state.data, form.telephone.data, form.hourlypay.data,
form.company.data, s, form.city.data, form.email.data
])
cursor.close()
connection.commit()
@staticmethod
def removeEmployee(form):
connection = db.engine.raw_connection()
cursor = connection.cursor()
cursor.callproc('RemoveEmployee', [form.socialsecurity.data])
cursor.close()
connection.commit()
class Group(db.Model):
__tablename__ = 'fGroup'
groupID = db.Column(db.INT, primary_key=True, autoincrement=True)
groupName = db.Column(db.VARCHAR(25))
groupType = db.Column(db.VARCHAR(15))
Gstatus = db.Column(db.VARCHAR(10))
Gowner = db.Column(db.CHAR(14), db.ForeignKey('User.userID'))
def __init__(self, gID, gname, gtype, gstatus, gowner):
self.groupID = gID
self.groupName = gname
self.groupType = gtype
self.Gstatus = gstatus
self.Gowner = gowner
@staticmethod
def addUser(groupID, user):
conn = db.engine.raw_connection()
cursor = conn.cursor()
cursor.callproc('joinGroup', args=[user.userID, 'user', groupID])
cursor.close()
conn.commit()
class Gene(BioModel):
"""Gene is uniquely identified although has multiple protein isoforms.
The isoforms are always located on the same chromosome, strand and are
a product of the same gene. The major function of this model is to group
isoforms classified as belonging to the same gene and to verify
consistency of chromosomes and strands information across the database.
"""
# HGNC symbols are allowed to be varchar(255) but 40 is still safe
# as for storing symbols that are currently in use. Let's use 2 x 40.
name = db.Column(db.String(80), unique=True, index=True)
# Full name from HGNC
full_name = db.Column(db.Text)
# TRUE represent positive (+) strand, FALSE represents negative (-) strand
# As equivalent to (?) from Generic Feature Format NULL could be used.
strand = db.Column(db.Boolean())
# Chromosome - up to two digits (1-22 inclusive), X and Y and eventually MT
chrom = db.Column(db.CHAR(2))
# "Records in Entrez Gene are assigned unique, stable and tracked integers as identifiers"
# ncbi.nlm.nih.gov/pmc/articles/PMC3013746, doi: 10.1093/nar/gkq1237
# as for Jun 8 2017, there are 18 151 636 genes in Entrez (ncbi.nlm.nih.gov/gene/statistics)
# an integer should suffice up to 2,147,483,647 genes.
entrez_id = db.Column(db.Integer)
isoforms = db.relationship('Protein',
backref=backref('gene', lazy='immediate'),
foreign_keys='Protein.gene_id')
preferred_isoform_id = db.Column(
db.Integer, db.ForeignKey('protein.id', name='fk_preferred_isoform'))
preferred_isoform = db.relationship('Protein',
uselist=False,
foreign_keys=preferred_isoform_id,
post_update=True)
preferred_refseq = association_proxy('preferred_isoform', 'refseq')
@cached_property
def alternative_isoforms(self):
return [
isoform for isoform in self.isoforms
if isoform.id != self.preferred_isoform_id
]
@hybrid_property
def isoforms_count(self):
return len(self.isoforms)
@isoforms_count.expression
def isoforms_count(cls):
return count_expression(cls, Protein)
@hybrid_property
def is_known_kinase(self):
return bool(self.preferred_isoform.kinase)
def __repr__(self):
return f'<Gene {self.name}, with {len(self.isoforms)} isoforms>'
def to_json(self):
return {
'name':
self.name,
'preferred_isoform':
(self.preferred_refseq if self.preferred_isoform else None),
'isoforms_count':
self.isoforms_count
}
class User(db.Model):
def __init__(self, user: dict):
"""
Initialize a User by passing in a dictionary.
:param user: A dictionary with fields matching the User fields
"""
self.username = user.get('username')
self.first = user.get('first')
self.last = user.get('last')
self.salt = user.get('salt')
self.password = user.get('password')
self.profilepic = user.get('profilepic')
self.profilepic_name = user.get('profilepic_name')
self.description = user.get('description')
self.member_since = user.get('member_since')
self.class_year = user.get('class_year')
self.location = user.get('location')
self.favorite_event = user.get('favorite_event')
self.activation_code = user.get('activation_code')
self.email = user.get('email')
self.subscribed = user.get('subscribed')
self.last_signin = user.get('last_signin')
self.week_start = user.get('week_start', 'monday')
self.deleted = user.get('deleted')
self.created_date = user.get('created_date')
self.created_user = user.get('created_user')
self.created_app = user.get('created_app')
self.modified_date = user.get('modified_date')
self.modified_user = user.get('modified_user')
self.modified_app = user.get('modified_app')
self.deleted_date = user.get('deleted_date')
self.deleted_user = user.get('deleted_user')
self.deleted_app = user.get('deleted_app')
__tablename__ = 'users'
# Data Columns
username = Column(db.VARCHAR(20), primary_key=True)
first = Column(db.VARCHAR(30), nullable=False, index=True)
last = Column(db.VARCHAR(30), nullable=False, index=True)
salt = Column(db.VARCHAR(255))
password = Column(db.VARCHAR(255), nullable=False)
profilepic = deferred(Column(LONGBLOB), group='pictures')
profilepic_name = deferred(Column(db.VARCHAR(50)), group='pictures')
description = Column(db.VARCHAR(255))
member_since = Column(db.DATE, nullable=False)
class_year = Column(db.INTEGER, index=True)
location = Column(db.VARCHAR(50))
favorite_event = Column(db.VARCHAR(20))
activation_code = Column(db.VARCHAR(8), nullable=False)
email = Column(db.VARCHAR(50), index=True)
subscribed = Column(db.CHAR(1))
last_signin = Column(db.DATETIME, nullable=False)
week_start = Column(db.VARCHAR(15))
deleted = Column(db.BOOLEAN)
# Audit Columns
created_date = Column(db.DATETIME)
created_user = Column(db.VARCHAR(31))
created_app = Column(db.VARCHAR(31))
modified_date = Column(db.DATETIME)
modified_user = Column(db.VARCHAR(31))
modified_app = Column(db.VARCHAR(31))
deleted_date = Column(db.DATETIME)
deleted_user = Column(db.VARCHAR(31))
deleted_app = Column(db.VARCHAR(31))
flair = db.relationship('Flair', backref='user_flair')
notification = db.relationship('Notification', backref='notification')
log = db.relationship('Log', backref='log')
forgot_password = db.relationship('ForgotPassword', backref='forgot_password')
message = db.relationship('Message', backref='message')
comment = db.relationship('Comment', backref='comment')
def __str__(self):
"""
String representation of a user. This representation is meant to be human readable.
:return: The user in string form.
"""
return f'User: [username: {self.username}, first: {self.first}, last: {self.last}, salt: {self.salt}, ' \
f'password: {self.password}, description: {self.description}, member_since: {self.member_since}, ' \
f'class_year: {self.class_year}, location: {self.location}, favorite_event: {self.favorite_event}, ' \
f'activation_code: {self.activation_code}, email: {self.email}, subscribed: {self.subscribed}, ' \
f'last_signin: {self.last_signin}, week_start: {self.week_start}, deleted: {self.deleted}]'
def __repr__(self):
"""
String representation of a user. This representation is meant to be machine readable.
:return: The user in string form.
"""
return '<User %r>' % self.username
def __eq__(self, other):
"""
Determine value equality between this object and another object.
:param other: Another object to compare to this user.
:return: True if the objects are equal, False otherwise.
"""
return User.compare(self, other)
@classmethod
def compare(cls, user_1, user_2) -> bool:
"""
Helper function used to determine value equality between two objects that are assumed to be users.
:param user_1: The first user object.
:param user_2: The second user object.
:return: True if the objects are equal, False otherwise.
"""
return all([
user_1.username == user_2.username,
user_1.first == user_2.first,
user_1.last == user_2.last,
user_1.salt == user_2.salt,
user_1.password == user_2.password,
user_1.profilepic_name == user_2.profilepic_name,
user_1.description == user_2.description,
str(user_1.member_since) == str(user_2.member_since),
user_1.class_year == user_2.class_year,
user_1.location == user_2.location,
user_1.favorite_event == user_2.favorite_event,
user_1.activation_code == user_2.activation_code,
user_1.email == user_2.email,
user_1.subscribed == user_2.subscribed,
str(user_1.last_signin) == str(user_2.last_signin),
user_1.week_start == user_2.week_start,
user_1.deleted == user_2.deleted
])
