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 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 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 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 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 Action(db.Model): id = db.Column(db.BigInteger, primary_key=True) derivative_id = db.Column(db.BigInteger, db.ForeignKey('derivative.id')) user_id = db.Column(db.Integer, db.ForeignKey('user.id')) tree_id = db.Column(db.Integer) type = db.Column(db.Enum(ActionType), nullable=False) timestamp = db.Column(db.DateTime, nullable=False, default=datetime.now) update_log = db.Column(db.JSON) def __str__(self): return f'<Action : {self.id}>'
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 Appointment(db.Model): __tablename__ = 'appointments' id = db.Column(db.Integer, primary_key=True) patient_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False) doctor_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False) description = db.Column(db.String(120)) doctor_notes = db.Column(db.String(120)) date = db.Column(db.Date, nullable=False) start = db.Column(db.Time, nullable=False) status = db.Column(db.Enum(AppointmentStatus), nullable=False) patient = db.relationship('User', foreign_keys=[patient_id], backref='p_appointments') doctor = db.relationship('User', foreign_keys=[doctor_id], backref='d_appointments') def __init__(self, description, date, start, status=AppointmentStatus.PENDING): self.description = description self.date = date self.start = start self.status = status def serialize(self, user_type): payload = { "id": self.id, "description": self.description, "doctor_notes": self.doctor_notes, "date": self.date.strftime("%Y-%m-%d"), "start": self.start.strftime("%H:%M"), "status": self.status.name } if user_type == UserType.DOCTOR: payload['patient'] = self.patient.serialize() elif user_type == UserType.PATIENT: payload['doctor'] = self.doctor.serialize() else: payload['patient'] = self.patient.serialize() payload['doctor'] = self.doctor.serialize() return payload
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 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 Prescription(db.Model): __tablename__ = 'prescriptions' id = db.Column(db.Integer, primary_key=True) patient_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False) doctor_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False) drug = db.Column(db.String(50), nullable=False) dosage = db.Column(db.String(50), nullable=False) date = db.Column(db.Date, nullable=False) status = db.Column(db.Enum(PrescriptionStatus), nullable=False) patient = db.relationship('User', foreign_keys=[patient_id], backref='p_prescriptions') doctor = db.relationship('User', foreign_keys=[doctor_id], backref='d_prescriptions') def __init__(self, drug, dosage, date, status=PrescriptionStatus.ACTIVE): self.drug = drug self.date = date self.dosage = dosage self.status = status def serialize(self, user_type): payload = { "id": self.id, "drug": self.drug, "date": self.date.strftime("%Y-%m-%d"), "dosage": self.dosage, "status": self.status.name } if user_type == UserType.DOCTOR: payload['patient'] = self.patient.serialize() elif user_type == UserType.PATIENT: payload['doctor'] = self.doctor.serialize() else: payload['patient'] = self.patient.serialize() payload['doctor'] = self.doctor.serialize() return payload
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 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 Badges(db.Model): __tablename__ = 'Badges' id = db.Column(db.String(100), primary_key=True) image = db.Column(db.String, nullable=False) csv = db.Column(db.String(100), nullable=False) csv_type = db.Column(db.String(100), nullable=False) ticket_types = db.Column(db.String, nullable=False) badge_size = db.Column(db.String(100), nullable=False) download_link = db.Column(db.String) image_link = db.Column(db.String) logo_image_link = db.Column(db.String) created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) deleted_at = db.Column(db.DateTime, nullable=True) badge_name = db.Column(db.String(100), default='My Badge') user_id = db.Column(db.String(100), db.ForeignKey('User.id', ondelete='CASCADE')) logo_text = db.Column(db.String) logo_color = db.Column(db.String) logo_image = db.Column(db.String) font_color_1 = db.Column(db.String(100), nullable=False) font_color_2 = db.Column(db.String(100), nullable=False) font_color_3 = db.Column(db.String(100), nullable=False) font_color_4 = db.Column(db.String(100), nullable=False) font_color_5 = db.Column(db.String(100), nullable=False) font_size_1 = db.Column(db.String) font_size_2 = db.Column(db.String) font_size_3 = db.Column(db.String) font_size_4 = db.Column(db.String) font_size_5 = db.Column(db.String) font_type_1 = db.Column(db.String(100), nullable=False) font_type_2 = db.Column(db.String(100), nullable=False) font_type_3 = db.Column(db.String(100), nullable=False) font_type_4 = db.Column(db.String(100), nullable=False) font_type_5 = db.Column(db.String(100), nullable=False) paper_size = db.Column(db.String(100), nullable=False) download_link = db.Column(db.String) 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) def delete_from_db(self): self.deleted_at = datetime.utcnow() self.save_to_db() @classmethod def getBadge(cls, badge_id): return cls.query.filter_by(id=badge_id)
class User(db.Model): __tablename__ = 'users' id = db.Column(db.Integer, primary_key=True) email = db.Column(db.String(120), unique=True, nullable=False) username = db.Column(db.String(120), unique=True, nullable=False) password = db.Column(db.String(200), nullable=False) first_name = db.Column(db.String(30), nullable=False) last_name = db.Column(db.String(30), nullable=False) dob = db.Column(db.Date) phone_number = db.Column(db.String(20)) created_at = db.Column(db.DateTime, server_default=func.current_timestamp()) user_type = db.Column(db.Enum(UserType), nullable=False) user_status = db.Column(db.Enum(UserStatus), nullable=False) specialization_id = db.Column(db.Integer, db.ForeignKey('specializations.id')) specialization = db.relationship('Specialization', backref='doctors') def __init__(self, email, username, first_name, last_name, dob, phone_number, user_type, user_status): self.email = email self.username = username self.first_name = first_name self.last_name = last_name self.dob = dob self.phone_number = phone_number self.user_type = user_type self.user_status = user_status def set_password(self, password): self.password = generate_password_hash(password) def check_password(self, password): return check_password_hash(self.password, password) def serialize(self): payload = { "id": self.id, "email": self.email, "username": self.username, "first_name": self.first_name, "last_name": self.last_name, "user_type": self.user_type.name, "user_status": self.user_status.name } if self.user_type == UserType.PATIENT: payload['dob'] = self.dob.strftime("%Y-%m-%d") payload['phone_number'] = self.phone_number elif self.user_type == UserType.DOCTOR: payload['dob'] = self.dob.strftime("%Y-%m-%d") payload['phone_number'] = self.phone_number payload['specialization'] = self.specialization.spec return payload
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
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 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 Edge(db.Model): id = db.Column(db.Integer, primary_key=True, autoincrement=True) request = db.Column(db.Integer, db.ForeignKey('request.id')) visit_number = db.Column(db.Integer) src_url = db.Column(db.String(1024), nullable=False) dst_url = db.Column(db.String(1024), nullable=False) depth = db.Column(db.Integer, nullable=False) had_keyword = db.Column(db.Boolean) create_date = db.Column(db.DateTime) db.UniqueConstraint('request', 'src_url', 'dst_url', 'depth', name="_request_src_dst_uc") def as_dict(self): return {c.name: getattr(self, c.name) for c in self.__table__.columns} def __init__(self, request, visit_number, src_url, dst_url, depth, had_keyword=False): self.request = request self.visit_number = visit_number self.src_url = src_url self.dst_url = dst_url self.depth = depth self.had_keyword = had_keyword self.create_date = datetime.datetime.now() def __repr__(self): return 'Edge([request: %d] %s ----> %s)' % (self.request, self.src_url, self.dst_url)
import datetime from itsdangerous import TimedJSONWebSignatureSerializer as Serializer from flask import current_app from backend.db import Base, db from backend import Session from werkzeug.security import generate_password_hash, check_password_hash from sqlalchemy.orm import relationship import jsonpickle #from flask_login import UserMixin games_players_association = db.Table( 'games_players', Base.metadata, db.Column('game_id', db.Integer, db.ForeignKey('games.id')), db.Column('player_id', db.Integer, db.ForeignKey('users.id'))) class User(Base): #, UserMixin): __tablename__ = 'users' id = db.Column(db.Integer, primary_key=True, autoincrement=True) username = db.Column(db.String(20), unique=True, nullable=False) email = db.Column(db.String(120), unique=True, nullable=False) #image_file = db.Column(db.String(20), nullable=False, default='default.jpg') password = db.Column(db.String(60), nullable=False) registered_on = db.Column(db.DateTime, nullable=False, default=datetime.datetime.utcnow) confirmed = db.Column(db.Boolean) admin = db.Column(db.Boolean, nullable=False, default=False) #posts = db.relationship('Post', backref='author', lazy=True) #games = db.relationship('GameModel', backref='author', lazy=True) own_games = relationship("GameModel",
class Order(db.Model): __tablename__ = "orders" id = db.Column(UUIDType(), primary_key=True) item = db.Column(db.String(45), nullable=False) customer_id = db.Column(UUIDType(), db.ForeignKey(Customer.id), nullable=False) employee_id = db.Column(UUIDType(), db.ForeignKey(Employee.id), nullable=False) customer = db.relationship('Customer', backref=db.backref('orders', lazy=True)) employee = db.relationship('Employee', backref=db.backref('orders', lazy=True)) order_time = db.Column(db.String(45), nullable=False) star_rating = db.Column(db.Integer, nullable=True) stat = db.Column(db.Integer, nullable=False) @staticmethod def new_order(id, item, customer_id, employee_id, order_time, stat=0, star_rating=None) -> "Order": # Test that customer & employee ids are valid customer = Customer.get_by_id(customer_id) employee = Employee.get_by_id(employee_id) return Order(id=id, item=item, customer_id=customer_id, employee_id=employee_id, order_time=order_time, star_rating=star_rating, stat=stat) @staticmethod def get_by_id(id) -> "Order": id = to_uuid(id) query = Order.query.filter_by(id=id) if query.count() == 0: raise ValueError(f"Order {str(id)} not found.") return query.first() def json(self) -> dict: return { "id": str(self.id), "item": self.item, "customer": self.customer, "employee": self.employee, "order_time": self.order_time, "star_rating": self.star_rating, "stat": self.stat, } @staticmethod def get_by_participant_id(role, id) -> list: id = to_uuid(id) if role == 'customer': query = Order.query.filter_by(customer_id=id) elif role == 'employee': query = Order.query.filter_by(employee_id=id) else: raise AttributeError(f"Role {role} not valid for order {str(id)}") return query.all()
import enum from datetime import datetime from backend.db import db rooms_contacts_association = db.Table('rooms_contacts_association', db.Column('room_id', db.Integer, db.ForeignKey('room.id'), primary_key=True), db.Column('contact_address', db.String, db.ForeignKey('contact.address'), primary_key=True) # TODO: change foreignkey to id? ) 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)