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