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 ])