class UserRoles(db.Model):
__tablename__ = 'user_roles'
id = db.Column(db.Integer(), primary_key=True)
user_id = db.Column(db.Integer(), db.ForeignKey(
'users.id', ondelete='CASCADE'))
role_id = db.Column(db.Integer(), db.ForeignKey(
'roles.id', ondelete='CASCADE'))
class Station(BaseModel, db.Model):
"""Model for the stations table"""
__tablename__ = 'stations'
id = db.Column(db.Integer, primary_key=True)
lat = db.Column(db.Float)
lng = db.Column(db.Float)
class Page(db.Model):
__tablename__ = 'pages'
# noinspection PyTypeChecker
TYPE = IntEnum('Page_type', {
'PROJECTS': 1,
'ABOUT': 2,
'CONTACTS': 3,
'MAINPAGE': 4,
})
id = db.Column(db.Integer, primary_key=True)
type = db.Column(TypeEnum(TYPE), unique=True, nullable=False)
title = db.Column(db.VARCHAR(128))
_blocks = db.relationship(
"BlockPageAssociation",
order_by='BlockPageAssociation.position',
collection_class=ordering_list('position'),
cascade='save-update, merge, delete, delete-orphan',
)
blocks = association_proxy(
'_blocks',
'block',
creator=lambda _pb: BlockPageAssociation(block=_pb))
bl = Resource('bl.page')
def __str__(self):
return '%s (%s)' % (self.title, self.url)
class Token(db.Model):
__tablename__ = 'tokens'
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
user = db.relationship('User', )
token = db.Column(db.String, nullable=False)
bl = Resource('bl.token')
class Contest(db.Model):
__tablename__ = 'contests'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(32), unique=True, nullable=False)
rate = db.Column(db.SMALLINT)
open_to_public = db.Column(db.Boolean, nullable=False)
password = db.Column(db.String(128))
create_dt = db.Column(db.DateTime, nullable=False)
start_dt = db.Column(db.DateTime, nullable=False)
close_dt = db.Column(db.DateTime)
duration = db.Column(db.Time, nullable=False)
fk_owner = db.Column(db.Integer, db.ForeignKey('users.id'), index=True, nullable=False)
owner = db.relationship('User', foreign_keys='Contest.fk_owner')
def hash_password(self, password):
password = password.encode('utf-8')
self.password = pwd_context.encrypt(password)
def verify_password(self, password):
password = password.encode('utf-8')
return pwd_context.verify(password, self.password)
class RegisteredUser(UserMixin, db.Model):
__tablename__ = "registered_user"
id = db.Column(db.BigInteger, primary_key=True, unique=True, nullable=False)
username = db.Column(db.Text, unique=True, nullable=False)
password = db.Column(db.Binary(60), nullable=False)
email = db.Column(db.Text, unique=True, nullable=False)
def __init__(self, id, username, password, email):
self.id = id
self.username = username
self.password = bcrypt.generate_password_hash(password)
self.email = email
def set_password(self, password):
"""Set password."""
self.password = bcrypt.generate_password_hash(password)
def check_password(self, value):
"""Check password."""
return bcrypt.check_password_hash(self.password, value)
def __repr__(self):
return '<User %r>' % self.username
class PageChunk(db.Model):
__tablename__ = 'pagechunks'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.Text, unique=True, nullable=False) # use in template
title = db.Column(db.Text, unique=True, nullable=False)
text = db.Column(db.Text)
bl = Resource('bl.pagechunk')
class Example(db.Model):
__tablename__ = "examples"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(32), nullable=False)
good = db.Column(db.Boolean(), default=True, server_default="TRUE")
def __repr__(self):
return f"<Example({self.name})>"
class BlockPageAssociation(db.Model):
__tablename__ = 'block_page_associations'
page_id = db.Column(db.Integer,
db.ForeignKey('pages.id'),
primary_key=True)
block_id = db.Column(db.Integer,
db.ForeignKey('pageblocks.id'),
primary_key=True)
position = db.Column(db.Integer)
block = db.relationship('PageBlock', )
class City(db.Model):
__tablename__ = 'cities'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(20), nullable=False, unique=True)
bl = Resource('bl.city')
def __str__(self):
return self.name
def __repr__(self):
return "[{}] {}".format(self.__class__.__name__, self.name)
class UserEmail(db.Model):
__tablename__ = 'user_emails'
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
user = db.relationship('User', uselist=False)
# User email information
email = db.Column(db.String(255), nullable=False, unique=True)
email_confirmed_at = db.Column(db.DateTime())
is_primary = db.Column(db.Boolean(), nullable=False, server_default='0')
class Team(db.Model):
__tablename__ = 'teams'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(32), unique=True, nullable=False)
fk_owner = db.Column(db.Integer,
db.ForeignKey('users.id'),
index=True,
nullable=False)
owner = db.relationship('User', foreign_keys='Team.fk_owner')
def to_json(self):
return dict(id=self.id, name=self.name, owner=self.owner.to_json())
class Submit(db.Model):
__tablename__ = 'submits'
id = db.Column(db.Integer, primary_key=True)
status = db.Column(db.String(16), nullable=False)
code = db.Column(db.String(100 * 1024), nullable=False)
language = db.Column(db.String(16), nullable=False)
submitted_on = db.Column(db.DateTime, nullable=False)
fk_user = db.Column(db.Integer,
db.ForeignKey('users.id'),
index=True,
nullable=False)
user = db.relationship('User', foreign_keys='Submit.fk_user')
fk_team = db.Column(db.Integer,
db.ForeignKey('teams.id'),
index=True,
nullable=False)
team = db.relationship('Team', foreign_keys='Submit.fk_team')
fk_problem = db.Column(db.Integer,
db.ForeignKey('problems.id'),
index=True,
nullable=False)
problem = db.relationship('Problem', foreign_keys='Submit.fk_problem')
class Device(db.Model):
__tablename__ = "devices"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
device_id = db.Column(db.String(128), unique=True, nullable=False)
device_type = db.Column(db.String(128), nullable=False)
active = db.Column(db.Boolean, default=True, nullable=False)
pn_token = db.Column(db.String(256), unique=True)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
user = db.relationship('User', backref=db.backref('devices', lazy='joined'))
created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
updated_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow)
def __init__(self, device_id: str, device_type: str, pn_token: str=None, active: bool=True, user=None, created_at: datetime=datetime.utcnow()):
self.device_id = device_id
self.device_type = device_type # "apple" "android"
self.pn_token = pn_token
self.active = active
self.user = user
self.created_at = created_at
self.updated_at = created_at
# noinspection PyPep8
@staticmethod
def query_active_devices_for_user(user: User):
return Device.query.filter(Device.user_id == user.id, Device.active == True, Device.pn_token.isnot(None))
# noinspection PyPep8
@staticmethod
def query_active_devices_for_group(group: Group, discard_user_ids:List[int]=None):
discard_user_ids = discard_user_ids or []
user_ids = [user.user_id for user in group.associated_users if user.user_id not in discard_user_ids]
return Device.query.filter(Device.user_id.in_(tuple(user_ids)), Device.active == True,
Device.pn_token.isnot(None))
@staticmethod
def create_or_update(device_id, device_type: str, user:User=None, active: bool = True, pn_token: str=None):
device = Device.first_by(device_id=device_id)
if not device:
device = Device(device_id=device_id, device_type=device_type, user=user, active=active, pn_token=pn_token)
db.session.add(device)
else:
device.device_type = device_type
device.active = active
if user:
device.user = user
if pn_token:
device.pn_token = pn_token
return device
@staticmethod
def first_by(**kwargs):
"""Get first db device that match to device_id"""
return Device.query.filter_by(**kwargs).first()
@staticmethod
def first(*criterion):
"""Get first db entity that match to criterium"""
return Device.query.filter(*criterion)
class TeamMember(db.Model):
__tablename__ = 'team_members'
fk_user = db.Column(db.Integer,
db.ForeignKey('users.id'),
primary_key=True,
nullable=False)
user = db.relationship('User', foreign_keys='TeamMember.fk_user')
fk_team = db.Column(db.Integer,
db.ForeignKey('teams.id'),
primary_key=True,
nullable=False)
team = db.relationship('Team', foreign_keys='TeamMember.fk_team')
def to_json(self):
return dict(user=self.user.to_json(), team=self.team.to_json())
class FavoriteRecipes(db.Model):
__tablename__ = 'favorite_recipes'
ID = db.Column(db.Integer, primary_key=True, nullable=False)
user = db.Column(db.Integer, db.ForeignKey('users.ID'), nullable=False)
recipe = db.Column(db.Integer,
db.ForeignKey('receitas.ID'),
nullable=False)
active = db.Column(db.Boolean, nullable=False)
def get_id(self):
return self.ID
def is_active(self):
return self.active
def as_dict(self):
return {c.name: getattr(self, c.name) for c in self.__table__.columns}
class EventDescriptor(db.Model):
__tablename__ = "event_descriptors"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(128), nullable=False)
description = db.Column(db.String(128), nullable=False)
created_at = db.Column(db.DateTime,
nullable=False,
default=datetime.utcnow)
updated_at = db.Column(db.DateTime,
nullable=False,
default=datetime.utcnow,
onupdate=datetime.utcnow)
def __init__(self, id: int, name: str, description: str):
self.id = id
self.name = name
self.description = description
class User(db.Model):
__tablename__ = 'users'
# noinspection PyTypeChecker
ROLE = IntEnum('Role', {
'staff': 0,
'superuser': 1,
})
id = db.Column(db.Integer, primary_key=True)
login = db.Column(db.String(30), unique=True, nullable=False)
password = db.Column(db.String(100), nullable=False)
name = db.Column(db.String(30))
surname = db.Column(db.String(30))
email = db.Column(db.String(320), nullable=False, unique=True)
role = db.Column(TypeEnum(ROLE), nullable=False, default=ROLE.staff)
bl = Resource('bl.user')
def __repr__(self):
return '{} ({})'.format(self.login, self.get_full_name())
def get_full_name(self):
return '{} {}'.format(self.name or '', self.surname or '').strip()
def is_superuser(self):
return self.role == self.ROLE.superuser
class User(UserMixin, db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(64), index=True, unique=True)
email = db.Column(db.String(64), unique=True, index=True)
password = db.Column(db.String(128), nullable=False, server_default='')
role_id = db.Column(db.Integer, db.ForeignKey('role.id'))
role = db.relationship('Role', back_populates='users')
# active = db.Column('is_active', db.Boolean(), nullable=False,
# server_default='1')
sign_in_count = db.Column(db.Integer, nullable=False, default=0)
last_login_time = db.Column(db.DateTime())
last_login_ip = db.Column(db.String(128))
current_login_time = db.Column(db.DateTime())
current_login_ip = db.Column(db.String(128))
@classmethod
def encryptpassword(cls, password, salt='$!@>HUI&DWQa`'):
import hashlib
def sha256(ascii_str):
return hashlib.sha256(ascii_str.encode('ascii')).hexdigest()
hash1 = sha256(password)
hash2 = sha256(hash1 + salt)
return hash2
def passwordmatch(self, password=''):
return self.password == User.encryptpassword(password)
@classmethod
def find_by_identity(cls, identity):
return User.query.filter(
or_(User.username == identity, User.email == identity)).first()
def save(self):
db.session.add(self)
db.session.commit()
return None
class Pubs(db.Model, TimestampMixin):
__tablename__ = "pubs"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(128), nullable=False, unique=True)
author = db.Column(db.String(128), db.ForeignKey('users.username'))
text = db.Column(db.Text(), nullable=False)
def __init__(self, name: str, text: str, author: str):
self.name = name
self.text = text
self.author = author
def to_json(self):
return {
'id': self.id,
'name': self.name,
'author': self.author,
'text': self.text
}
class UserGroupAssociation(db.Model):
__tablename__ = "user_group_associations"
user_id = db.Column(db.Integer,
db.ForeignKey('users.id'),
primary_key=True)
group_id = db.Column(db.Integer,
db.ForeignKey('groups.id'),
primary_key=True)
user = db.relationship("User", back_populates="associated_groups")
group = db.relationship("Group", back_populates="associated_users")
created_at = db.Column(db.DateTime,
nullable=False,
default=datetime.utcnow)
updated_at = db.Column(db.DateTime,
nullable=False,
default=datetime.utcnow,
onupdate=datetime.utcnow)
def __init__(self, user: User, group: Group):
self.user = user
self.group = group
class Recipe(db.Model):
__tablename__ = "recipes"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64), nullable=False)
description = db.Column(db.String(512))
created_on = db.Column(db.DateTime(),
nullable=False,
default=datetime.utcnow)
updated_on = db.Column(db.DateTime(),
nullable=False,
default=datetime.utcnow,
onupdate=datetime.utcnow)
@property
def json(self):
return {
"id": self.id,
"name": self.name,
"description": self.description
}
class PageBlock(db.Model):
__tablename__ = 'pageblocks'
# noinspection PyTypeChecker
TYPE = IntEnum(
'Block_type',
{
'img_left': 0,
'img_right': 1,
'no_img': 2,
},
)
id = db.Column(db.Integer, primary_key=True)
block_type = db.Column(TypeEnum(TYPE),
default=TYPE.img_left,
nullable=False)
# header
title = db.Column(db.VARCHAR(128), nullable=True)
text = db.Column(db.Text)
# used for mainpage
short_description = db.Column(db.VARCHAR(256), nullable=True)
image = db.Column(db.Text, nullable=True)
bl = Resource('bl.pageblock')
def __str__(self):
return '%s: %s' % (self.title, self.text or self.short_description)
class UploadedImage(db.Model):
__tablename__ = 'uploaded_images'
IMG_CATEGORY = IntEnum(
'',
{
'other': 0,
'gallery': 1,
},
)
id = db.Column(db.Integer, primary_key=True)
name = db.Column(GUID, nullable=False)
ext = db.Column(db.VARCHAR, nullable=False)
img_category = db.Column(
TypeEnum(IMG_CATEGORY),
default=IMG_CATEGORY.other,
nullable=False,
)
title = db.Column(db.VARCHAR(32))
description = db.Column(db.VARCHAR(128))
__table_args__ = (db.UniqueConstraint(
'name',
'ext',
'img_category',
), )
bl = Resource('bl.uploadedimage')
class Group(db.Model):
__tablename__ = "groups"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(128))
associated_users = db.relationship("UserGroupAssociation",
back_populates="group")
users = association_proxy('associated_users', 'user')
created_at = db.Column(db.DateTime,
nullable=False,
default=datetime.utcnow)
updated_at = db.Column(db.DateTime,
nullable=False,
default=datetime.utcnow,
onupdate=datetime.utcnow)
def __init__(self, name: str):
self.name = name
@staticmethod
def get(id: int):
"""Get db entity that match the id"""
return Group.query.get(id)
class Files(db.Model,TimestampMixin):
__tablename__ = "files"
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(128), nullable=False, unique=True)
extension = db.Column(db.String(10))
data = db.Column(db.LargeBinary)
def __init__(self,
name: str,
extension: str,
data: bytes):
self.name = name
self.extension = extension
self.data = data
def to_json(self):
return {
'id': self.id,
'name': self.name,
'extension': self.extension,
}
class User(db.Model, UserMixin):
__tablename__ = "users"
id = db.Column(db.Integer, primary_key=True)
# Authentication
# 128 characters is a sensible default for the max length of full names
full_name = db.Column(db.String(128))
# What should we call you? (for example, when we send you email?)
preferred_name = db.Column(db.String(128))
# 254 characters is the maximum length of an email address
email = db.Column(db.String(254), unique=True, nullable=False)
email_confirmed = db.Column(db.Boolean, default=False, nullable=False)
password = db.Column(db.String(128), nullable=False)
role = db.Column(db.String(128))
# Activity tracking
created_at = db.Column(db.DateTime, nullable=False,
default=dt.datetime.utcnow)
def __init__(self, **kwargs):
# Call Flask-SQLAlchemy's constructor
super(User, self).__init__(**kwargs)
# Custom setup
self.password = User.encrypt_password(kwargs['password'])
@classmethod
def encrypt_password(cls, password):
return bcrypt.generate_password_hash(password).decode('UTF-8')
@classmethod
def authenticate(cls, email, password):
found_user = cls.query.filter_by(email=email).first()
if found_user:
authenticated_user = bcrypt.check_password_hash(
found_user.password, password)
if authenticated_user:
# Return the user in the event we want to store information in the session
return found_user
return False
def __repr__(self):
return f"{self.name}"
class Problem(db.Model):
__tablename__ = 'problems'
id = db.Column(db.Integer, primary_key=True)
number = db.Column(db.Integer, nullable=False)
rate = db.Column(db.SMALLINT)
time_limit = db.Column(db.SMALLINT, nullable=False) ## seconds
space_limit = db.Column(db.SMALLINT, nullable=False) ## megabytes
body = db.Column(db.String(100 * 1024), nullable=False)
fk_contest = db.Column(db.Integer,
db.ForeignKey('contests.id'),
index=True,
nullable=False)
contest = db.relationship('Contest', foreign_keys='Problem.fk_contest')
__table_args__ = (db.UniqueConstraint('number', 'fk_contest'), )
class MailTemplate(db.Model):
__tablename__ = 'mailtemplates'
# noinspection PyTypeChecker
MAIL = IntEnum('Mail', {
'CV': 0,
'REPLY': 1,
})
id = db.Column(db.Integer, primary_key=True)
mail = db.Column(TypeEnum(MAIL), nullable=False)
title = db.Column(db.String, nullable=False)
subject = db.Column(db.String(79), nullable=False)
html = db.Column(db.Text, nullable=False)
help_msg = db.Column(db.Text)
updated_at = db.Column(db.Date, onupdate=datetime.datetime.now,
default=datetime.datetime.now)
bl = Resource('bl.mailtemplate')
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
who_updated = db.relationship('User')
def __repr__(self):
return str(self.title)
class User(db.Model, UserMixin):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
# User Authentication fields
username = db.Column(db.String(50), nullable=True,
unique=True) # changed nullable to True
password = db.Column(db.String(255), nullable=False)
# User fields
active = db.Column(db.Boolean())
# changed nullable to True
first_name = db.Column(db.String(50), nullable=True)
# changed nullable to True
last_name = db.Column(db.String(50), nullable=True)
# Relationship
user_emails = db.relationship('UserEmail')
roles = db.relationship('Role', secondary='user_roles')
