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
}
