class CommentTable(db.Model): __tablename__ = "commenttable" commentid = db.Column(UUID(as_uuid=True), primary_key=True) fileid = db.Column(UUID(as_uuid=True), db.ForeignKey('filetable.fileid', ondelete='CASCADE'), nullable=False) userid = db.Column(UUID(as_uuid=True), db.ForeignKey('usertable.userid', ondelete='CASCADE'), nullable=False) comment = db.Column(db.String(256), nullable=False) date = db.Column(db.DateTime()) user = db.relationship(UserTable, foreign_keys=userid, backref=db.backref('user', lazy='joined', cascade="all, delete-orphan")) commentFile = db.relationship(FileTable, foreign_keys=fileid, backref=db.backref( 'commentFile', lazy='joined', cascade="all, delete-orphan")) def __init__(self, data): self.commentid = str(uuid.uuid1()) self.fileid = data['fileid'] self.userid = data['userid'] self.comment = data['comment'] self.date = data['date']
class CommentReadTable(db.Model): __tablename__ = "commentreadtable" userid = db.Column('userid', UUID(as_uuid=True), db.ForeignKey('usertable.userid', ondelete='CASCADE'), primary_key=True) commentid = db.Column('commentid', UUID(as_uuid=True), db.ForeignKey('commenttable.commentid', ondelete='CASCADE'), primary_key=True) user = db.relationship(UserTable, foreign_keys=userid, backref=db.backref('commenter', lazy='joined', cascade="all, delete-orphan")) comment = db.relationship(CommentTable, foreign_keys=commentid, backref=db.backref('commented', lazy='joined', cascade="all, delete-orphan")) def __init__(self, data): self.commentid = data["commentid"] self.userid = data["userid"]
class Character(db.Model, Serializer): id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(80), unique=True, nullable=False) collection_id = db.Column( db.Integer, db.ForeignKey('collection.id', name='collection_id_character')) image_id = db.Column(db.String(36), db.ForeignKey('image.id', name='image_id_character')) image = db.relationship('Image', uselist=False, foreign_keys=[image_id]) def __repr__(self): return '{}'.format(self.name)
class ClassSchedule(db.Model): __tablename__ = 'class_schedule' id = db.Column(UUID, server_default=db.text('gen_random_uuid()'), primary_key=True) dow = db.Column(db.VARCHAR(5)) seq = db.Column(db.INTEGER) classroom = db.Column(db.VARCHAR(30)) course_group_id = db.Column(UUID, db.ForeignKey('course_groups.id')) course_group = db.relationship('CourseGroup') subject_id = db.Column(UUID, db.ForeignKey('subjects.id')) subject = db.relationship('Subject')
class FileGroupTable(db.Model): __tablename__ = "filegrouptable" fileid = db.Column(UUID(as_uuid=True), db.ForeignKey('filetable.fileid', ondelete='CASCADE'), primary_key=True) groupid = db.Column(UUID(as_uuid=True), db.ForeignKey('grouptable.groupid', ondelete='CASCADE'), primary_key=True) def __init__(self, data): self.groupid = data["groupid"] self.fileid = data["fileid"]
class Comment(Base): __tablename__ = "comments" content = db.Column(db.Text, nullable=False) product_id = db.Column(db.Integer, db.ForeignKey("products.id", ondelete="CASCADE"), nullable=False) user_id = db.Column(Base.get_user_id_field(), db.ForeignKey("users.id", ondelete="CASCADE"), nullable=False) user = db.relationship("User", passive_deletes=True, lazy="joined") # This relation is only used for cascading deletes, sqlalchemy requires backref relation for cascading deletes to work :/ product = db.relationship("Product", passive_deletes=True, lazy="select") def __init__(self, content, product_id, user_id): self.content = content self.product_id = product_id self.user_id = user_id
class UserGroupTable(db.Model): __tablename__ = "usergrouptable" userid = db.Column(UUID(as_uuid=True), db.ForeignKey('usertable.userid', ondelete='CASCADE'), primary_key=True) groupid = db.Column(UUID(as_uuid=True), db.ForeignKey('grouptable.groupid', ondelete='CASCADE'), primary_key=True) def __init__(self, data): self.groupid = data["groupid"] self.userid = data["userid"]
class Vote(db.Model, Serializer): id = db.Column(db.Integer, autoincrement=True, primary_key=True) result = db.Column(db.Boolean, default=False) game_id = db.Column(db.String(36), db.ForeignKey('game.id', name='game_id_vote')) player_id = db.Column(db.String(36), db.ForeignKey('player._id', name='player_id_vote')) player = db.relationship('Player', uselist=False, foreign_keys=[player_id], post_update=True) def __repr__(self): return "<Vote {}: {}; game {}>".format(self.id, self.result, self.game_id)
class ItemModel(db.Model): __tablename__ = 'items' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80)) price = db.Column(db.Float(precision=2)) store_id = db.Column(db.Integer, db.ForeignKey('stores.id')) store = db.relationship('StoreModel') def __init__(self, name, price, store_id): self.name = name self.price = price self.store_id = store_id def json(self): return {'id': self.id, 'name': self.name, 'price': self.price, 'store_id': self.store_id} @classmethod def find_by_name(cls, name): return cls.query.filter_by(name=name).first() def save_to_db(self): db.session.add(self) db.session.commit() def delete_from_db(self): db.session.delete(self) db.session.commit()
class GroupTable(db.Model): __tablename__ = "grouptable" groupid = db.Column(UUID(as_uuid=True), primary_key=True) groupname = db.Column(db.String(64), nullable=False) groupleaderid = db.Column('groupleader', UUID(as_uuid=True), db.ForeignKey('usertable.userid'), nullable=False) groupleader = db.relationship(UserTable, foreign_keys=groupleaderid, backref=db.backref('leader', lazy='joined')) def __init__(self, data): self.groupid = str(uuid.uuid1()) self.groupname = data["groupname"] self.groupleaderid = data["groupleaderid"] def serialise(self): return { "groupid": str(self.groupid), "groupname": str(self.groupname), "groupleaderid": str(self.groupleaderid) }
class CategoryProduct(db.Model): __tablename__ = "categories_products" __table_args__ = (db.PrimaryKeyConstraint("category_id", "product_id"), ) category_id = db.Column(db.Integer, db.ForeignKey("categories.id"), nullable=False) product_id = db.Column(db.Integer, db.ForeignKey("products.id", ondelete="CASCADE"), nullable=False) def add_product_categories(self, product_id, categories): curr_product = Product.query.get(product_id) if not curr_product: return False categories = list(map(lambda cat: int(cat), categories)) # delete the categories not selected in update CategoryProduct.query \ .filter(CategoryProduct.product_id == product_id) \ .filter(CategoryProduct.category_id.notin_(categories)) \ .delete(synchronize_session="fetch") # everytime we access the property categories a query is made # this is why we save it in a variable curr_categories = list(map(lambda cat: cat.id, curr_product.categories)) if len(categories) > 0: db.session().bulk_insert_mappings( CategoryProduct, [{ "category_id": category, "product_id": product_id } for category in [cat for cat in categories if cat not in curr_categories]]) try: db.session().commit() except exc.SQLAlchemyError as err: print("[ERROR] Batch insert product categories " + str(err), sys.stderr) return False return True
class EventLog(db.Model): __tablename__ = 'event_log' id = db.Column(UUID, server_default=db.text('gen_random_uuid()'), primary_key=True) msg = db.Column(db.VARCHAR(255)) course_number_id = db.Column(UUID, db.ForeignKey('course_numbers.id')) course_number = db.relationship('CourseNumber')
class Game(db.Model, Serializer): id = db.Column(db.String(36), primary_key=True, default=generate_uuid, unique=True) key = db.Column(db.String(KEY_LENGTH), unique=True, default=generate_key) created = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) state = db.Column(db.Enum(GameState), default=GameState.WAITING) players = db.relationship('Player', backref='game', lazy=True, cascade="all, delete", foreign_keys=[Player.game_id]) max_players = db.Column(db.Integer) current_player_id = db.Column( db.String(36), db.ForeignKey('player._id', name='current_player_id')) current_player = db.relationship('Player', uselist=False, foreign_keys=[current_player_id], post_update=True) nextVotes = db.relationship('Vote', cascade="all, delete", foreign_keys=[Vote.game_id]) guessVotes = db.relationship('Vote', cascade="all, delete", foreign_keys=[Vote.game_id]) awaitingGuessVote = db.Column(db.Boolean, default=False) used_collections = db.relationship('Collection', secondary=used_collections) def get_connected_players(self): return [player for player in self.players if player.connected] def get_guessing_players(self): return [player for player in self.players if not player.guessed] def get_next_votes(self): return [vote for vote in self.nextVotes if vote.result] def get_correct_guess_votes(self): return [vote for vote in self.guessVotes if vote.result] def get_wrong_guess_votes(self): return [vote for vote in self.guessVotes if not vote.result] def serialize(self): d = Serializer.serialize(self) d['players'] = [{ 'player': player.serialize(), '_id': player._id } for player in self.players] d['correctGuessVotes'] = len(self.get_correct_guess_votes()) d['wrongGuessVotes'] = len(self.get_wrong_guess_votes()) return d def __repr__(self): return '<Game {}>'.format(self.id)
class CourseGroup(db.Model): __tablename__ = 'course_groups' id = db.Column(UUID, server_default=db.text('gen_random_uuid()'), primary_key=True) gid = db.Column(db.VARCHAR(30)) course_number_id = db.Column(UUID, db.ForeignKey('course_numbers.id')) course_number = db.relationship('CourseNumber') students = db.relationship('User') class_schedule = db.relationship('ClassSchedule', lazy='dynamic')
class CourseNumber(db.Model): __tablename__ = 'course_numbers' id = db.Column(UUID, server_default=db.text('gen_random_uuid()'), primary_key=True) number = db.Column(db.INTEGER, default=0) course_theme_id = db.Column(UUID, db.ForeignKey('course_themes.id')) course_theme = db.relationship('CourseTheme') course_group = db.relationship('CourseGroup') subjects = db.relationship('Subject', secondary=course_number_subjects, lazy='dynamic')
class Subject(db.Model): __tablename__ = 'subjects' id = db.Column(UUID, server_default=db.text('gen_random_uuid()'), primary_key=True) name = db.Column(db.VARCHAR(255)) desc = db.Column(db.TEXT) teacher_id = db.Column(UUID, db.ForeignKey('teachers.id')) teacher = db.relationship('Teacher') lessons = db.relationship('ClassSchedule') course_subscribed = db.relationship('CourseNumber', secondary=course_number_subjects, lazy='dynamic')
class Player(db.Model, Serializer): _id = db.Column(db.String(36), primary_key=True, default=generate_uuid, unique=True) username = db.Column(db.String(20), nullable=False) game_id = db.Column(db.String(36), db.ForeignKey('game.id', name='game_id_player')) connected = db.Column(db.Boolean, default=False) ready = db.Column(db.Boolean, default=False) sid = db.Column(db.String()) is_creator = db.Column(db.Boolean) character_id = db.Column( db.Integer, db.ForeignKey('character.id', name='character_id_player')) character = db.relationship('Character', backref="assigned_players") guesses = db.Column(db.Integer, default=0) guessed = db.Column(db.Boolean, default=False) def serialize(self): d = Serializer.serialize(self, exclude=['game', 'sid']) return d def __repr__(self): return '<Player {} [{}]>'.format(self.username, self._id)
class ItemModel(db.Model, DBActionMixin): __tablename__ = 'items' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(ITEM_NAME_LEN), unique=True) price = db.Column(db.Float(precision=2)) store_id = db.Column(db.Integer, db.ForeignKey('stores.id')) store = db.relationship('StoreModel') def __init__(self, **kwargs): super(ItemModel, self).__init__(**kwargs) def jsonify(self): return {'name': self.name, 'price': self.price} @classmethod def find_by_name(cls, name): return cls.query.filter_by(name=name).first()
class MetadataTable(db.Model): __tablename__ = "metadatatable" metadataid = db.Column(UUID(as_uuid=True), primary_key=True) versionid = db.Column(UUID(as_uuid=True), db.ForeignKey('fileversiontable.versionid', ondelete='CASCADE'), nullable=False) title = db.Column(db.String(64), nullable=False) value = db.Column(db.String(128), nullable=False) version = db.relationship(FileVersionTable, foreign_keys=versionid, backref=db.backref('version', lazy='joined', cascade="all, delete-orphan")) def __init__(self, data): self.metadataid = str(uuid.uuid1()) self.versionid = data["versionid"] self.title = data["title"] self.value = data["value"]
class ItemModel(db.Model): __tablename__ = "tblItems" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80)) price = db.Column(db.Float(precision=2)) store_id = db.Column(db.Integer, db.ForeignKey('tblStores.id')) store = db.relationship('StoreModel') def __init__(self, name, price, store_id): self.name = name self.price = price self.store_id = store_id def json(self): return { "id": str(self.id), "name": self.name, "price": self.price, "store_id": self.store_id, "store": self.store.name } @classmethod def find_by_id(cls, id): return ItemModel.query.filter_by(id=int(id)).first() @classmethod def find_by_name(cls, name): return ItemModel.query.filter_by(name=name).first() def save_to_db(self): db.session.add(self) db.session.commit() def delete_from_db(self): db.session.delete(self) db.session.commit()
class BookModel(db.Model): __tablename__ = "books" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(255)) genre = db.Column(db.String(255)) library_id = db.Column(db.Integer, db.ForeignKey('libraries.id')) store = db.relationship('LibraryModel') def __init__(self, name, genre, library_id): self.name = name self.genre = genre self.library_id = library_id def json(self): return { 'name': self.name, 'genre': self.genre, 'store_id': self.library_id } @classmethod def find_by_name(cls, name): return cls.query.filter_by(name=name).first() @classmethod def find_by_genre(cls, genre): return cls.query.filter_by(genre=genre).all() def save_to_db(self): db.session.add(self) db.session.commit() def delete_from_db(self): db.session.delete(self) db.session.commit()
class ItemModel(db.Model): __tablename__ = 'items' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(255)) price = db.Column(db.Float(precision=2)) store_id = db.Column(db.Integer, db.ForeignKey('stores.id')) store = db.relationship('StoreModel') def __init__(self, name, price, store_id): self.name = name self.price = price self.store_id = store_id @classmethod def find_by_name(cls, name): # filtering an item from the db class then returns an object return cls.query.filter_by( name=name).first() # =SELECT * FROM items WHERE name=name LIMIT 1 def save_to_db(self): # saving the item object to the database db.session.add(self) db.session.commit() def delete_from_db(self): db.session.delete(self) db.session.commit() def json(self): return { 'id': self.id, 'name': self.name, 'price': self.price, 'store_id': self.store_id }
class ItemModel(db.Model): __tablename__ = 'items' id = db.Column(db.Integer, primary_key = True) name = db.Column(db.String(80)) price = db.Column(db.Float(precision = 2)) # Create a property 'store_id' for every item that matches the StoreModel object # with the same id in stores database (create a SQL table join relationship) store_id = db.Column(db.Integer, db.ForeignKey('stores.id')) store = db.relationship('StoreModel') def __init__(self, name, price, store_id): self.name = name self.price = price self.store_id = store_id def json(self): return { "name": self.name, "price": self.price } @classmethod def find_by_name(cls, name): return cls.query.filter_by(name = name).first() #SELECT * FROM __tablename__ WHERE name=name LIMIT 1 def save_to_db(self): #upserting method # SQLAlchemy can translate directly form object to row db.session.add(self) db.session.commit() def delete_from_db(self): db.session.delete(self) db.session.commit()
class FileVersionTable(db.Model): __tablename__ = "fileversiontable" versionid = db.Column(UUID(as_uuid=True), primary_key=True) fileid = db.Column(UUID(as_uuid=True), db.ForeignKey('filetable.fileid', ondelete='CASCADE'), nullable=False) versionhash = db.Column(db.String(8), nullable=False) archived = db.Column(db.Boolean(), unique=False, nullable=False, default=False) file = db.relationship(FileTable, foreign_keys=fileid, backref=db.backref('file', lazy='joined', cascade="all, delete-orphan")) def __init__(self, data): self.versionid = str(uuid.uuid1()) self.fileid = data["fileid"] self.versionhash = data["versionhash"] self.archived = False
def __repr__(self): return '<Player {} [{}]>'.format(self.username, self._id) class GameState(enum.Enum): WAITING = 1 RUNNING = 2 FINISHED = 3 used_collections = db.Table( 'used_collections', db.Column('game_id', db.String(36), db.ForeignKey('game.id', name='game_id_used_collection'), primary_key=True), db.Column('collection_id', db.Integer, db.ForeignKey('collection.id', name='collection_id_used_collections'), primary_key=True)) votes = db.Table( 'votes', db.Column('game_id', db.String(36), db.ForeignKey('game.id', name='game_id_votes'), primary_key=True), db.Column('vote_id', db.Integer,
from src.db import db from sqlalchemy.dialects.postgresql import UUID course_number_subjects = db.Table( 'course_number_subjects', db.Column('course_number_id', UUID, db.ForeignKey('course_numbers.id')), db.Column('subject_id', UUID, db.ForeignKey('subjects.id')))
class Task(db.Model): __tablename__ = 'tasks' id = db.Column(db.Integer, primary_key=True) subject = db.Column(db.String(80)) description = db.Column(db.String(500)) status = db.Column(db.Integer) project_id = db.Column(db.Integer, db.ForeignKey('projects.id')) user_id = db.Column(db.Integer, db.ForeignKey('users.id')) def __init__(self, subject: str, description: str, status: TaskStatus, project_id: int, user_id: int, id: int = None, **kwargs): """[summary] Args: subject (str): [description] status (TaskStatus): [description] project_id (int): [description] user_id (int): [description] id (int, optional): [description]. Defaults to None. """ self.id = id self.subject = subject self.description = description self.project_id = project_id self.user_id = user_id self.status = Task.validateStatus(status) @ classmethod def find_by_taskID(cls, tID): return cls.query.filter_by(id=tID).first() def save_to_db(self): db.session.add(self) db.session.commit() def delete_task(self): db.session.delete(self) db.session.commit() def json(self): user = User.find_by_id(self.user_id) username = user.basicDetails()['username'] if user else None return {'id': self.id, 'subject': self.subject, 'description': self.description, 'status': self.status, 'project_id': self.project_id, 'assigned_user': username, 'user_id': self.user_id, } @classmethod def validateStatus(cls, status): """ To prevent status values to go beyond 0-3, if so task status has defalut value 0 Args: status ([int]): [status] Returns: [int]: [valid status] """ if status < 0: status = 0 if status > 3: status = 3 return status
class AlertModel(db.Model): __tablename__ = 'alerts' id = db.Column(db.Integer, primary_key=True) user = db.Column(db.Integer, db.ForeignKey('users.id')) product = db.Column(db.String(255)) price = db.Column(db.Float(precision=2)) currency = db.Column(db.String(3)) active = db.Column(db.Boolean, default=True) def __init__(self, user_id, product, price, currency): self.user = user_id self.product = product self.price = price self.currency = currency self.active = True def to_dict(self): return { 'id': self.id, 'user': self.user, 'product': self.product, 'price': self.price, 'currency': self.currency, 'active': self.active, } def add_alert(self): db.session.add(self) db.session.commit() def delete_alert(self): db.session.delete(self) db.session.commit() @classmethod def get_alerts_by_user_id(cls, user): alerts = cls.query.filter_by(user=user).all() return alerts @classmethod def get_alert_by_id(cls, _id): alert = cls.query.filter_by(id=_id).first() if alert: return alert return None @classmethod def get_all_alerts(cls): alerts = cls.query.all() return alerts @classmethod def get_all_active_alerts(cls): alerts = cls.query.filter_by(active=True).all() return alerts @classmethod def list_to_dict(cls, alertlist): return [alert.to_dict() for alert in alertlist] def update_info(self, product, price, currency): self.product = product self.price = price self.currency = currency db.session.commit() def change_active(self): self.active = not self.active db.session.commit()
class ItemModel(db.Model): __tablename__ = 'items' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80)) # 80 characters limit price = db.Column(db.Float(precision=2)) # numbers after the comma store_id = db.Column( db.Integer, db.ForeignKey('stores.id')) # Foreign key to link items & stores store = db.relationship('StoreModel') # Now no joins necessary def __init__(self, name, price, store_id): self.name = name self.price = price self.store_id = store_id def json(self) -> dict: """ Returns the id, name, price and store_id as .json string. :return: {'id': Int, 'name': String, 'price': Int, 'store_id': Int} """ return { 'id': self.id, 'name': self.name, 'price': self.price, 'store_id': self.store_id } @classmethod def find_by_name(cls, name: str) -> object: """ Find an object by its name. :param name: Item name to find. :return: Object of Item-class. """ return cls.query.filter_by( name=name).first() # SELECT * FROM items WHERE name=name LIMIT 1 @classmethod def find_all(cls) -> dict: """ Returns all items in .db :return: All items found in .db """ return cls.query.all() def save_to_db(self) -> None: """ Insert new or update existing object in data base. """ db.session.add(self) db.session.commit() def delete_from_db(self) -> None: """ Delete object from the data base. """ db.session.delete(self) db.session.commit()
class User(db.Model): """Base user model class.""" __tablename__ = 'users' # identification id = db.Column(UUID, server_default=db.text('gen_random_uuid()'), primary_key=True) tid = db.Column(db.INTEGER) email = db.Column(db.VARCHAR(120)) password = db.Column(db.VARCHAR(256)) course_group_id = db.Column(UUID, db.ForeignKey('course_groups.id')) course_group = db.relationship('CourseGroup') is_admin = db.Column(db.BOOLEAN, default=False) is_banned = db.Column(db.BOOLEAN, default=False) banned_at = db.Column(db.TIMESTAMP(timezone=True)) is_deleted = db.Column(db.BOOLEAN, default=False) deleted_at = db.Column(db.TIMESTAMP(timezone=True)) # Tech info created_at = db.Column(db.TIMESTAMP(timezone=True), server_default=db.text('now()::timestamp(0)')) # password functions def hash_password(self, password): self.password = generate_password_hash(password, method='pbkdf2:sha256', salt_length=15) def verify_password(self, recieved_password): return check_password_hash(self.password, recieved_password) # token finctions def generate_auth_token(self): return generate_token(self.id) @property def is_authenticated(self): return True @property def is_active(self): return not self.is_banned @property def is_anonymous(self): return False def get_id(self): return str(self.id) @staticmethod def verify_auth_token(token): token_claimset = verify_token(token) if not token_claimset: return None current_user = User.query.get(token_claimset['id']) return current_user # ban function def ban(self): self.is_banned = True self.banned_at = db.text('now()::timestamp(0)') db.session.commit() def ban_recovery(self): self.is_banned = False self.banned_at = None db.session.commit() # delete function def delete(self): self.is_deleted = True self.deleted_at = db.text('now()::timestamp(0)') db.session.commit() def recovery(self): self.is_deleted = False self.deleted_at = None db.session.commit()