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 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 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 CourseTheme(db.Model): __tablename__ = 'course_themes' id = db.Column(UUID, server_default=db.text('gen_random_uuid()'), primary_key=True) name = db.Column(db.VARCHAR(255)) course_number = db.relationship('CourseNumber') course_number_lazy = db.relationship('CourseNumber', lazy='dynamic')
class StoreModel(db.Model): __tabelname__ = 'stores' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80)) items = db.relationship("ItemModel", back_populates="items") items = db.relationship('ItemModel', lazy='dynamic') def __init__(self, name): self.name = name def json(self): return {'name':self.name,'items':[item.json() for item in self.items.all()]} @classmethod def find_by_name(cls,name): # connection = sqlite3.connect('mydata.db') # cursor = connection.cursor() # # query = "select * from items where name=?" # result = cursor.execute(query, (name,)) # row = result.fetchone() # connection.close() # # if row: # return cls(*row) # cls(row[0],row[1]) #return ItemModel.query.filter_by(name=name).first() return cls.query.filter_by(name=name).first() def save_to_db(self): # def insert(self): # connection = sqlite3.connect('mydata.db') # cursor = connection.cursor() # query = "insert into items values(?,?)" # cursor.execute(query, (self.name, self.price,)) # connection.commit() # connection.close() db.session.add(self) db.session.commit() def delete_from_db(self): db.session.delete(self) db.session.commit() # def update(self): # connection = sqlite3.connect('mydata.db') # cursor = connection.cursor() # query = "update items set price=? where name=?" # cursor.execute(query, (self.price, self.name,)) # connection.commit() # connection.close()
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 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 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 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 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 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 StoreModel(db.Model): __tablename__ = 'stores' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80)) # 80 characters limit # List of ItemModels; many-to-1 rel. (Back reference) # lazy: to not create an StoreModel for each item yet items = db.relationship( 'ItemModel', lazy='dynamic' ) # self.items is no list anymore but a query builder -> .all() def __init__(self, name): self.name = name def json(self) -> dict: """ Returns the name & items as .json string. :return: {'id': Int, 'name': String, 'items': String} """ return { 'id': self.id, 'name': self.name, 'items': [item.json() for item in self.items.all()] } # List comprehension @classmethod def find_by_name(cls, name: str) -> object: """ Find an object by its name. :param name: Item name to find. :return: object """ return cls.query.filter_by( name=name).first() # SELECT * FROM items WHERE name=name LIMIT 1 @classmethod def find_all(cls) -> tuple: """ Returns all stores in .db :return: All stores 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 StoreModel(db.Model): __tablename__ = "tblStores" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80)) # knows it is a many to one relationship , so this is a list items = db.relationship('ItemModel', lazy='dynamic') def __init__(self, name): self.name = name def json(self): return { "name": self.name, "items": [item.json() for item in self.items.all()] } def check_empty(self): return next((item.json() for item in self.items.all()), None) @classmethod def find_by_name(cls, name): return StoreModel.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 Category(Base): __tablename__ = "categories" name = db.Column(db.String(100), nullable=False) products = db.relationship("Product", secondary="categories_products", back_populates="categories", lazy="joined")
class LibraryModel(db.Model): __tablename__ = "libraries" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(255)) books = db.relationship('BookModel', lazy='dynamic') def __init__(self, name): self.name = name def json(self): return { 'id': self.id, 'name': self.name, 'items': [item.json() for item in self.books.all()] } @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 StoreModel(db.Model): __tablename__ = 'stores' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80)) items = db.relationship('ItemModel', lazy='dynamic') def __init__(self, name): self.name = name def json(self): return { 'name': self.name, 'items': [item.json() for item in self.items.all()] } @classmethod def find_by_name(cls, name): return cls.query.filter_by(name=name).first() @classmethod def find_all(cls): return cls.query.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 Teacher(db.Model): __tablename__ = 'teachers' id = db.Column(UUID, server_default=db.text('gen_random_uuid()'), primary_key=True) name = db.Column(db.VARCHAR(255)) email = db.Column(db.VARCHAR(255)) subjects = db.relationship('Subject')
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 Collection(db.Model, Serializer): id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(30), unique=True, nullable=False) characters = db.relationship('Character', backref='collection', lazy=True) tags = db.relationship('Tag', secondary=tags, lazy='subquery', backref=db.backref('collections', lazy=True)) default = db.Column(db.Boolean, default=False) def serialize(self): d = Serializer.serialize(self) d['amountOfCharacters'] = len(self.characters) return d def __repr__(self): return '{}'.format(self.name)
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 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 User(db.Model): __tablename__ = 'users' id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(100)) password = db.Column(db.String(100)) email = db.Column(db.String(100)) alerts = db.relationship('AlertModel') registerdate = db.Column(db.DateTime, default=datetime.datetime.utcnow()) def __init__(self, username, password, email): self.username = username self.password = password self.email = email self.registerdate = datetime.datetime.utcnow() @classmethod def get_by_username(cls, username): user = cls.query.filter_by(username=username).first() if user: return user return None @classmethod def get_by_email(cls, email): user = cls.query.filter_by(email=email).first() if user: return user return None @classmethod def get_by_id(cls, _id): user = cls.query.filter_by(id=_id).first() if user: return user return None def add_user(self): db.session.add(self) db.session.commit() def delete_user(self): for alert in self.alerts: alert.delete_alert() db.session.delete(self) db.session.commit()
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 StoreModel(db.Model, DBActionMixin): __tablename__ = 'stores' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(STORE_NAME_LEN)) items = db.relationship('ItemModel', lazy='dynamic') def __init__(self, name): self.name = name def jsonify(self): return { 'name': self.name, 'items': [item.jsonify() for item in self.items] } @classmethod def find_by_name(cls, name): return cls.query.filter_by(name=name).first()
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 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 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 StoreModel(db.Model): __tablename__ = 'stores' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80)) # 'Backreference' allows the a StoreModel object to see which items are in the items table # in the database with store_id equal to the id of this StoreModel object # this is a list of items since we have many items -> one store # lazy = 'dynamic' prevents the creation of the whole list of item objects as soon as we create # a StoreModel object. It changes the self.items from list to a query builder that we are going # to execute when we want to retrieve the list ( self.items.all() ) items = db.relationship('ItemModel', lazy='dynamic') def __init__(self, name, _id=None): self.name = name self.id = _id def json(self): return { "store_id": int(self.id), "name": self.name, "items": [item.json() for item in self.items.all()] } @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 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 }