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)
