class Permissions(db.Model):
__tablename__ = 'Permissions'
id = db.Column(db.String(100), primary_key=True)
isUser = db.Column(db.Boolean, default=False)
isAdmin = db.Column(db.Boolean, default=False)
isSales = db.Column(db.Boolean, default=False)
user_id = db.Column(db.String(100),
db.ForeignKey('User.id', ondelete='CASCADE'))
def save_to_db(self):
self.id = str(uuid.uuid4())
db.session.add(self)
try:
db.session.commit()
except Exception as e:
db.session.rollback()
db.session.flush()
print(e)
@classmethod
def getPermissions(cls, permissions_id):
return cls.query.filter_by(id=permissions_id).first()
@classmethod
def get_by_uid(cls, uid=None):
return cls.query.filter_by(user_id=uid).first()
class Admin(db.Model):
__tablename__ = 'Admin'
id = db.Column(db.String(100), primary_key=True)
username = db.Column(db.String(80))
password = db.Column(db.String(100))
email = db.Column(db.String(100))
photoURL = db.Column(db.String())
def __init__(self, id_, username, password, email, photoURL=None):
self.id = id_
self.username = username
if password:
self.password = generate_password_hash(password)
self.email = email
if photoURL:
self.photoURL = photoURL
else:
self.photoURL = 'Some default asset'
def save_to_db(self):
db.session.add(self)
try:
db.session.commit()
except Exception as e:
db.session.rollback()
db.session.flush()
print(e)
@classmethod
def getAdmin(cls, admin_id=None, username=None):
if username is None:
return cls.query.filter_by(id=admin_id).first()
if admin_id is None:
return cls.query.filter_by(username=username).first()
class Track(db.Model, JSONStripped):
__tablename__ = 'track'
__tracks__ = [
'Marketing',
'FM',
'Logistics',
'HR',
'IM',
]
def __init__(self, *args, **kwargs):
super(Track, self).__init__(*args, **kwargs)
id = \
db.Column(db.Integer, primary_key=True)
name = \
db.Column(db.String(30), unique=True)
def __repr__(self):
return '<Track %r>' % self.name
@staticmethod
def fill():
db.session.add_all([Track(name=name) for name in Track.__tracks__])
db.session.commit()
@staticmethod
def get_one(**kwargs):
return Track.query.filter_by(**kwargs).first()
class Module(db.Model):
__tablename__ = 'Modules'
id = db.Column(db.Integer, primary_key=True)
ticketInclude = db.Column(db.Boolean, default=False)
paymentInclude = db.Column(db.Boolean, default=False)
donationInclude = db.Column(db.Boolean, default=False)
def __init__(self,
ticketInclude=True,
paymentInclude=True,
donationInclude=True):
self.id = 1
self.donationInclude = donationInclude
self.paymentInclude = paymentInclude
self.ticketInclude = ticketInclude
@classmethod
def set_default(cls):
if len(Module.query.filter_by(id=1).all()) == 0:
db.session.add(Module())
db.session.commit()
else:
print('Module already created')
def save_to_db(self):
db.session.add(self)
try:
db.session.commit()
except Exception as e:
db.session.rollback()
db.session.flush()
print(e)
class Notes(Base):
__tablename__ = 'notes'
id = db.Column(db.Integer, primary_key=True)
notes = db.Column(db.String)
# for the game and player I use the back_populates argument on both
# side of the relationship, instead of backref on one
player_id = db.Column(db.Integer, db.ForeignKey('users.id'))
player = relationship("User",
foreign_keys=[player_id],
back_populates="own_notes")
game_id = db.Column(db.Integer, db.ForeignKey('games.id'))
game = relationship("GameModel",
foreign_keys=[game_id],
back_populates="player_notes")
def __init__(self, player, game):
self.notes = ""
self.player = player
self.game = game
def dict(self):
# return the notes in dict format
notes = {}
notes['id'] = self.id
notes['notes'] = self.notes
notes['player_id'] = self.player.id
notes['game_id'] = self.game.id
return notes
def __repr__(self):
return f"Notes({self.id}, {self.player.id}, {self.game.id}, {self.notes})"
class Room(db.Model):
__tablename__ = 'room'
id = db.Column(db.Integer, primary_key=True)
hash = db.Column(db.String, unique=True)
name = db.Column(db.String, nullable=False)
private = db.Column(db.Boolean, default=False)
admin_address = db.Column(db.String, nullable=True)
members = db.relationship("Contact", secondary=rooms_contacts_association)
messages = db.relationship(
"Message", back_populates="room",
cascade="all, delete"
)
def save(self):
if not self.id:
db.session.add(self)
db.session.commit()
def delete(self):
db.session.delete(self)
db.session.commit()
def update(self, **kwargs):
for key, value in kwargs.items():
if hasattr(self, key):
setattr(self, key, value)
self.save()
class MessageQueue(db.Model):
__tablename__ = 'message_queue'
id = db.Column(db.Integer, primary_key=True)
receiver_id = db.Column(db.Integer, db.ForeignKey('contact.id'))
msg_id = db.Column(db.Integer, db.ForeignKey('message.id'))
status = db.Column(db.Enum(MessageStatus))
timestamp = db.Column(db.DateTime, default=datetime.utcnow)
receiver = db.relationship("Contact")
message = db.relationship("Message")
def save(self):
if not self.id:
db.session.add(self)
db.session.commit()
def delete(self):
db.session.delete(self)
db.session.commit()
def update(self, **kwargs):
for key, value in kwargs.items():
if hasattr(self, key):
setattr(self, key, value)
self.save()
# If all queued messages of a message id had been send we edit the message status to DISPATCHED
queued_messages = MessageQueue.query.filter_by(
msg_id=self.msg_id,
status=MessageStatus.QUEUED
).all()
if len(queued_messages) == 0:
message = Message.query.get(self.msg_id)
message.update(status=MessageStatus.DISPATCHED)
class Item(db.Model):
__tablename__ = 'items'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64), unique=True, nullable=False)
price = db.Column(db.Integer(), nullable=False)
def __repr__(self):
return f'<Item {self.name}>'
@staticmethod
def get_item(name):
item = Item.query.filter(Item.name == name).first()
return item
def add_item(self):
db.session.add(self)
db.session.commit()
@staticmethod
def change_item(name, price):
db.session.query(Item).filter(Item.name == name).update({'price': price})
db.session.commit()
def delete_item(self):
db.session.delete(self)
db.session.commit()
@staticmethod
def get_items():
return Item.query.all()
@staticmethod
def delete_items():
db.session.query(Item).delete()
db.session.commit()
class User(db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(64), unique=True, nullable=False)
password = db.Column(db.String(32), nullable=False)
def __repr__(self):
return f'<User {self.username}>'
def add_user(self):
db.session.add(self)
db.session.commit()
def remove_user(self):
db.session.delete(self)
db.session.commit()
@staticmethod
def find_by_username(value):
db.create_all()
return db.session.query(User).filter_by(username=value).first()
@staticmethod
def find_by_id(value):
db.create_all()
return db.session.query(User).filter_by(id=value).first()
class Product(db.Model):
# Bind schema to external database
__bind_key__ = 'external'
# Composite key (name, valuation_date)
name = db.Column(db.String(32), primary_key=True)
valuation_date = db.Column(db.Date, primary_key=True)
market_price = db.Column(db.Float, nullable=False)
currency_code = db.Column(db.CHAR(3), db.ForeignKey('currency.code'))
class Whitelist(db.Model):
__tablename__ = "whitelist"
id = db.Column(db.Integer, primary_key=True)
word = db.Column(db.String, nullable=True)
def __repr__(self):
return self.word
class Employee(db.Model):
__tablename__ = "employee"
id = db.Column(UUIDType(), db.ForeignKey(User.id), primary_key=True)
user = db.relationship('User')
name = db.Column(db.String(45), nullable=False)
gender = db.Column(db.String(45), nullable=False)
tel = db.Column(db.String(45), nullable=False)
# stat = db.Column(db.Integer, nullable=False)
level = db.Column(db.Integer, nullable=False)
@staticmethod
def new_employee(id, name, gender, tel, level=0) -> "Employee":
# Test that employee id exists in user table
user = User.get_by_id(id)
return Employee(
id=id,
name=name,
gender=gender,
tel=tel,
level=level,
# stat = stat
)
@staticmethod
def get_by_id(id) -> "Employee":
if type(id) != UUID:
try:
id = UUID(id)
except:
raise AttributeError("Invalid UUID")
query = Employee.query.filter_by(id=id)
if query.count() == 0:
raise ValueError(f"Employee {str(id)} not found.")
return query.first()
@staticmethod
def id_exists(id) -> bool:
id = to_uuid(id)
query = Employee.query.filter_by(id=id)
if query.count() > 0:
return True
return False
def json(self) -> dict:
return {
"id": str(self.id),
"name": self.name,
"gender": self.gender,
"tel": self.tel,
"level": self.level,
}
class Profile(db.Model):
__tablename__ = "profiles"
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(None, db.ForeignKey('users.id'))
name = db.Column(db.String, nullable=True)
def __repr__(self):
return self.name
class ReportHead(db.Model):
id = db.Column(db.Integer, primary_key=True)
target_date = db.Column(db.Date, nullable=False)
creation_date = db.Column(db.Date, nullable=False)
version = db.Column(db.Integer, nullable=False)
derivative_count = db.Column(db.Integer, nullable=False)
def __str__(self):
return f'<ReportHead : {self.id}>'
class History(db.Model):
__tablename__ = "history"
id = db.Column(db.Integer, primary_key=True)
domain = db.Column(db.String, nullable=False)
errors = db.Column(db.ARRAY(db.String))
created = db.Column(db.DateTime(), default=datetime.datetime.utcnow())
def __repr__(self):
return self.email
class ProductSeller(db.Model):
# Bind schema to external database
__bind_key__ = 'external'
# Composite key (company_id, product_name)
company_id = db.Column(db.String(6),
db.ForeignKey('company.id'),
primary_key=True)
product_name = db.Column(db.String(32),
db.ForeignKey('product.name'),
primary_key=True)
class ChatModel(Base):
__tablename__ = 'mainchat'
id = db.Column(db.Integer, primary_key=True)
chat = db.Column(db.PickleType)
def __init__(self):
self.chat = jsonpickle.encode([])
def __repr__(self):
return f"ChatModel({self.id}, {self.chat})"
class CompanyStock(db.Model):
# Bind schema to external database
__bind_key__ = 'external'
# Composite key (company_id, valuation_date)
company_id = db.Column(db.String(6),
db.ForeignKey('company.id'),
primary_key=True)
valuation_date = db.Column(db.Date, primary_key=True)
stock_price = db.Column(db.Float, nullable=False)
currency_code = db.Column(db.CHAR(3), db.ForeignKey('currency.code'))
class Specialization(db.Model):
__tablename__ = 'specializations'
id = db.Column(db.Integer, primary_key=True)
spec = db.Column(db.String(50), unique=True)
def __init__(self, spec):
self.spec = spec
def serialize(self):
return {"id": self.id, "spec": self.spec}
class User(db.Model):
__tablename__ = "users"
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String, nullable=False, unique=True)
hashed_password = db.Column(db.String, nullable=False)
registered_on = db.Column(db.DateTime(), default=datetime.datetime.utcnow())
role = db.Column(db.String)
def __repr__(self):
return self.email
class Currency(db.Model):
# Bind schema to external database
__bind_key__ = 'external'
# Composite key (code, valuation_date)
code = db.Column(db.CHAR(3), nullable=False, primary_key=True)
valuation_date = db.Column(db.Date, primary_key=True)
usd_exchange_rate = db.Column(db.Float, nullable=False)
@property
def symbol(self):
return utils.getCurrencySymbol(self.code) or '?'
class Session(db.Model):
__tablename__ = 'session'
id = db.Column(UUIDType(), primary_key=True)
uid = db.Column(UUIDType(), db.ForeignKey('user.id'), nullable=False)
user = db.relationship('User', backref=db.backref('sessions', lazy=True))
expire = db.Column(db.DateTime(), nullable=False)
@staticmethod
def new_session(username, expire=None) -> 'Session':
if expire == None:
expire = datetime.now() + timedelta(hours=3)
if type(expire) in (int, float):
expire = datetime.fromtimestamp(expire)
user = User.get_by_username(username)
return Session(
id=uuid4(),
user=user,
expire=expire,
)
@staticmethod
def get_by_id(id) -> 'Session':
if type(id) != UUID:
try:
id = UUID(id)
except:
raise AttributeError("Invalid UUID")
query = Session.query.filter_by(id=id)
if query.count() == 0:
raise ValueError(f"Session {str(id)} not found.")
session = query.first()
if session.expired():
db.session.delete(session)
db.session.commit()
raise ValueError(f"Session {str(id)} not found.")
return session
def json(self) -> dict:
return {
"id": str(self.id),
"user": self.user.json(),
"expire": int(self.expire.timestamp()),
}
def expired(self) -> bool:
return self.expire < datetime.now()
class User(db.Model, JSONStripped):
__tablename__ = 'user'
def __init__(self, *args, **kwargs):
super(User, self).__init__(*args, **kwargs)
id = \
db.Column(db.Integer, primary_key=True)
login = \
db.Column(db.String(50), unique=True, nullable=False)
password_hash = \
db.Column(db.String(255), nullable=False)
creation_time = \
db.Column(db.DateTime, index=True, default=datetime.utcnow)
st_login = \
db.Column(db.String(10), unique=True, nullable=True)
course = \
db.Column(db.String(255), index=True, nullable=True)
username = \
db.Column(db.String(50), index=True, nullable=False)
is_public = \
db.Column(db.Boolean, default=True)
score_second_lang = \
db.Column(db.Boolean, default=False)
gpa = \
db.Column(db.Float, default=0)
scores = \
db.relationship('UserSubject', lazy='subquery', cascade='all, delete-orphan')
priorities = \
db.relationship('UserTrack', lazy='subquery', cascade='all, delete-orphan')
def __repr__(self):
return '<User %r>' % self.login
@staticmethod
def insert(**kwargs):
user = User(**kwargs)
db.session.add(user)
db.session.commit()
return user
@staticmethod
def get_all(**kwargs):
return User.query.filter_by(**kwargs)
@staticmethod
def get_one(**kwargs):
return User.query.filter_by(**kwargs).first()
class Myself(db.Model):
id = db.Column(db.Integer, primary_key=True)
address = db.Column(db.String, nullable=False)
email = db.Column(db.String, nullable=True)
def save(self):
if not self.id:
db.session.add(self)
db.session.commit()
def delete(self):
db.session.delete(self)
db.session.commit()
def update(self, **kwargs):
for key, value in kwargs.items():
if hasattr(self, key):
setattr(self, key, value)
self.save()
class UserTrack(db.Model, JSONStripped):
__tablename__ = '$user$track'
def __init__(self, *args, **kwargs):
super(UserTrack, self).__init__(*args, **kwargs)
user_id = \
db.Column('user_id', db.Integer, db.ForeignKey('user.id'), primary_key=True)
track_id = \
db.Column('track_id', db.Integer, db.ForeignKey('track.id'), primary_key=True)
priority = \
db.Column('priority', db.Integer, nullable=False)
track = db.relationship('Track', lazy='subquery')
def __repr__(self):
return '<UserTrack %r %r>' % self.user_id % self.track_id
class Message(db.Model):
__tablename__ = 'message'
id = db.Column(db.Integer, primary_key=True)
sender_address = db.Column(db.String, db.ForeignKey('contact.address'))
sender_nickname = db.Column(db.String, nullable=False)
room_hash = db.Column(db.String, db.ForeignKey('room.hash'))
msg = db.Column(db.String, nullable=False)
status = db.Column(db.Enum(MessageStatus))
timestamp = db.Column(db.DateTime, default=datetime.utcnow)
sender = db.relationship("Contact")
# room = db.relationship("Room")
room = db.relationship("Room", back_populates="messages")
def save(self):
if not self.id:
db.session.add(self)
db.session.commit()
def delete(self):
db.session.delete(self)
db.session.commit()
def update(self, **kwargs):
for key, value in kwargs.items():
if hasattr(self, key):
setattr(self, key, value)
self.save()
class File(db.Model):
__tablename__ = 'File'
id = db.Column(db.String(100))
filename = db.Column(db.String(100), nullable=False, primary_key=True)
filetype = db.Column(db.String(100), nullable=False)
user_id = db.Column(db.String(100),
db.ForeignKey('User.id', ondelete='CASCADE'))
def save_to_db(self):
self.id = str(uuid.uuid4())
db.session.add(self)
try:
db.session.commit()
except Exception as e:
db.session.rollback()
db.session.flush()
print(e)
class GameModel(Base):
__tablename__ = 'games'
id = db.Column(db.Integer, primary_key=True)
advanced = db.Column(db.Boolean, nullable=False, default=False)
nplayers = db.Column(db.Integer, nullable=False)
# backref="games" establishes that we can refer to User.games automatically
players = relationship("User",
secondary=games_players_association,
backref="games")
# for the owner and active player I use the back_populates argument on both
# side of the relationship, instead of backref on one
owner_id = db.Column(db.Integer, db.ForeignKey('users.id'))
owner = relationship("User",
foreign_keys=[owner_id],
back_populates="own_games")
active_id = db.Column(db.Integer, db.ForeignKey('users.id'))
active = relationship("User",
foreign_keys=[active_id],
back_populates="active_games")
game = db.Column(db.PickleType)
chat = db.Column(db.PickleType)
status = db.Column(db.String)
# status can be: "new", "running", "finished"
player_notes = relationship("Notes",
back_populates="game",
foreign_keys="[Notes.game_id]")
def __init__(self, advanced, nplayers, owner):
self.advanced = advanced
self.nplayers = nplayers
self.owner = owner
self.status = "new"
self.chat = jsonpickle.encode([])
def dict(self):
# return the game in dict format
game = {}
game['id'] = self.id
game['advanced'] = self.advanced
game['nplayers'] = self.nplayers
game['owner'] = self.owner.dict()
game['active'] = self.active.dict() if self.active else None
game['status'] = self.status
game['players'] = []
for plr in self.players:
game['players'].append(plr.dict())
#game['player_notes'] = []
#for notes in self.player_notes:
# game['player_notes'].append(notes.dict())
return game
def __repr__(self):
return f"GameModel({self.id}, {self.status}, {self.advanced}, "\
f"{self.nplayers} )"
class ResetPasswordToken(db.Model):
__tablename__ = 'Reset Password Token'
id = db.Column(db.String, primary_key=True)
token = db.Column(db.String, nullable=False)
def __init__(self, uid, token):
self.id = uid
self.token = token
def save_to_db(self):
try:
db.session.add(self)
db.session.commit()
except Exception as e:
db.session.rollback()
db.session.flush()
print(e)
class UserSubject(db.Model, JSONStripped):
__tablename__ = '$user$subject'
def __init__(self, *args, **kwargs):
super(UserSubject, self).__init__(*args, **kwargs)
user_id = \
db.Column('user_id', db.Integer, db.ForeignKey('user.id'), primary_key=True)
subject_id = \
db.Column('subject_id', db.Integer, db.ForeignKey('subject.id'), primary_key=True)
mark = \
db.Column('mark', db.String(2), nullable=False)
is_relevant = \
db.Column('is_relevant', db.Boolean, default=True)
subject = db.relationship('Subject', lazy='subquery')
def __repr__(self):
return '<UserSubject %r %r>' % self.user_id % self.subject_id
