class Account(db.Model): __tablename__ = "accounts" id = db.Column(db.Integer, primary_key=True, autoincrement=True) user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False, index=True) currency_id = db.Column(db.Integer, db.ForeignKey('currencies.id'), nullable=False, index=True) primary_account = db.Column(db.Boolean, nullable=False, default=False) created_on = db.Column(db.DateTime, nullable=False, default=datetime.datetime.utcnow) transactions = db.relationship('Transaction', backref='user', lazy='dynamic') currency = db.relationship('Currency', backref='accounts', lazy='joined') def __init__(self, user_id, currency_id, primary_account=False): self.user_id = user_id self.currency_id = currency_id self.primary_account = primary_account self.created_on = datetime.datetime.utcnow()
class TasksToTournamets(db.Model): id = db.Column(db.Integer, primary_key=True) tournament_id = db.Column(db.Integer, db.ForeignKey('tournament.id')) task_id = db.Column(db.Integer, db.ForeignKey('task.id')) tournament = db.relationship('Tournament', backref=db.backref('taskstotournaments', lazy='select'), lazy='subquery') task = db.relationship('Task', backref=db.backref('taskstotournaments', lazy='select'), lazy='subquery') def __init__(self, tournament_id, task_id, task, tournament): self.tournament_id = tournament_id self.task_id = task_id self.task = task self.tournament = tournament def add(self): db.session.add(self) db.session.commit() return self.id def delete(self): db.session.delete(self) db.session.commit() @staticmethod def get_all(): return TasksToTournamets.query.all()
class IrradiationPositionTbl(Base, db.Model): identifier = db.Column(db.String(80)) position = db.Column(db.Integer) levelID = db.Column(db.Integer, db.ForeignKey('LevelTbl.id')) level = db.relationship('LevelTbl') sampleID = db.Column(db.Integer, db.ForeignKey('SampleTbl.id')) sample = db.relationship('SampleTbl')
class Attempt(db.Model): id = db.Column(db.Integer, primary_key=True) task_id = db.Column(db.Integer(), db.ForeignKey('task.id')) try_flag = db.Column(db.String(256)) success = db.Column(db.Boolean()) time = db.Column(db.DateTime()) score = db.Column(db.Integer()) tournament_to_object_id = db.Column( db.Integer, db.ForeignKey('tournaments_to_object.id')) tournament_to_object = db.relationship('TournamentsToObject', backref=db.backref('attempts', lazy='select'), lazy='select') task = db.relationship('Task', backref=db.backref('task', lazy='select'), lazy='select') def __init__(self, flag, success, **kwargs): self.try_flag = flag self.success = success self.score = 0 self.time = dt.now() if 'tournament_to_object' in kwargs: self.tournament_to_object_id = kwargs['tournament_to_object'] if 'task' in kwargs: self.task_id = kwargs['task'] self.score = Task.get_by_id(self.task_id).score @staticmethod def get_scoreboard_for_contestant(tournament_to_object): scorehistory = db.session.query(Attempt). \ filter((Attempt.tournament_to_object_id == tournament_to_object) & Attempt.success).all() return scorehistory @staticmethod def already_solved(tournament_to_object_id, task_id): return bool( db.session.query(Attempt).filter( (Attempt.tournament_to_object_id == tournament_to_object_id) & (Attempt.task_id == task_id) & (Attempt.success == True)).first()) def save(self): db.session.add(self) db.session.commit() return self.id def delete(self): db.session.delete(self) db.session.commit() def get_all(self): return db.session(Attempt).query.all()
class Team(Contestant): id = db.Column(db.Integer, db.ForeignKey('contestant.id'), primary_key=True) name = db.Column(db.String(128)) city = db.Column(db.String(128)) invite_code = db.Column(db.String(1024)) creator_id = db.Column(db.Integer, db.ForeignKey('user.id')) creator = db.relationship('User', backref=db.backref('created_teams', lazy='dynamic'), foreign_keys='Team.creator_id') members = db.relationship('User', secondary=user_to_team, backref=db.backref('teams', lazy='select')) __mapper_args__ = { 'polymorphic_identity': 'team' } def __init__(self, name, city, invite_code, creator): self.name = name self.city = city self.invite_code = invite_code self.creator_id = creator.id self.creator = creator self.members.append(creator) def save(self): db.session.add(self) db.session.commit() return self.id def delete(self): db.session.delete(self) db.session.commit() @staticmethod def get_all(): return Team.query.all() @staticmethod def get_by_code(code): return Team.query.filter(Team.invite_code == code).first() def add_new_member(self, user): user.team_id = self.id self.members.append(user) db.session.commit() def remove_member(self, user): self.members.remove(user) db.session.commit() def if_user_in_the_team(self, user): return user in self.members @staticmethod def get_by_id(id): return db.session.query(Team).filter(Team.id == id).first()
class AnalysisTbl(Base, db.Model): timestamp = db.Column(db.DateTime) aliquot = db.Column(db.Integer) increment = db.Column(db.Integer, nullable=True) analysis_type = db.Column(db.String(80)) irradiation_positionID = db.Column(db.Integer, db.ForeignKey('IrradiationPositionTbl.id')) irradiation_position = db.relationship('IrradiationPositionTbl') @property def step(self): inc = self.increment if inc is not None: s = alphas(inc) else: s = '' return s @property def runid(self): return '{}-{}{}'.format(self.irradiation_position.identifier, self.aliquot, self.step) @property def irradiation_info(self): level = self.irradiation_position.level irrad = level.irradiation return '{}{} {}'.format(irrad.name, level.name, self.irradiation_position.position)
class Urls(db.Model): __tablename__ = "urls" id = db.Column(db.Integer, primary_key=True, autoincrement=True) url = db.Column(db.String(255), nullable=False) user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=True) ipv4_origin = db.Column(db.String(255), nullable=True) ipv6_origin = db.Column(db.String(255), nullable=True) protocol = db.Column(db.String(255), nullable=True) searched_on = db.Column(db.DateTime, nullable=True) # def __init__(self, email, password, admin=False): # self.email = email # self.password = bcrypt.generate_password_hash( # password, app.config.get('BCRYPT_LOG_ROUNDS') # ) # self.registered_on = datetime.datetime.now() # self.admin = admin # # def is_authenticated(self): # return True # # def is_active(self): # return True # # def is_anonymous(self): # return False def get_id(self): return self.id def __repr__(self): return '<Urls {0}>'.format(self.url)
class Stocktaking(db.Model): __versioned__ = {} id = db.Column(db.Integer, primary_key=True) timestamp = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) comment = db.Column(db.Text) close_timestamp = db.Column(db.DateTime) close_user_id = db.Column(db.Integer, db.ForeignKey('user.id')) close_user = db.relationship('User', foreign_keys=[close_user_id], lazy='joined') items = db.relationship('StocktakingItem', lazy='dynamic') def __repr__(self) -> str: return '{!s} [{!s}]'.format(self.id, self.timestamp) def are_items_frozen(self) -> bool: return self.are_items_closed() def are_items_closed(self) -> bool: return self.close_user_id is not None def close_items(self, user: User): if self.close_user_id: raise RuntimeError("Items have been closed.") self.close_user_id = user.id self.close_timestamp = datetime.utcnow()
class Messages(db.Model): id = db.Column(db.Integer, primary_key=True) text = db.Column(db.String(1024)) creator_id = db.Column(db.Integer(), db.ForeignKey('user.id')) tournament_id = db.Column(db.Integer(), db.ForeignKey('tournament.id')) def __init__(self, text, tournament_id): self.text = text self.tournament_id = tournament_id def save(self): db.session.add(self) db.session.commit() return self.id def get_all_messages_in_tournament(self, id_tournament): return db.session.query(Messages.text).filter(Messages.tournament_id == id_tournament)
class Work(db.Model): __versioned__ = {} id = db.Column(db.Integer, primary_key=True) customer_id = db.Column(db.Integer, db.ForeignKey('customer.id'), nullable=False) comment = db.Column(db.Text) outbound_close_timestamp = db.Column(db.DateTime) outbound_close_user_id = db.Column(db.Integer, db.ForeignKey('user.id')) returned_close_timestamp = db.Column(db.DateTime) returned_close_user_id = db.Column(db.Integer, db.ForeignKey('user.id')) customer = db.relationship('Customer', lazy='joined') outbound_close_user = db.relationship( 'User', foreign_keys=[outbound_close_user_id], lazy='joined') returned_close_user = db.relationship( 'User', foreign_keys=[returned_close_user_id], lazy='joined') work_items = db.relationship('WorkItem', lazy='dynamic') def __repr__(self) -> str: return '{!s} [{!r}]'.format(self.id, self.customer) def are_items_frozen(self) -> bool: return self.are_outbound_items_closed() def are_outbound_items_closed(self) -> bool: return self.outbound_close_user_id is not None def are_returned_items_closed(self) -> bool: return self.returned_close_user_id is not None def close_outbound_items(self, user: User): if self.outbound_close_user_id: raise RuntimeError("Outbound items have been closed.") self.outbound_close_user_id = user.id self.outbound_close_timestamp = datetime.utcnow() def close_returned_items(self, user: User): if not self.outbound_close_user_id: raise RuntimeError("Outbound items have not been closed.") if self.returned_close_user_id: raise RuntimeError("Returned items have been closed.") self.returned_close_user_id = user.id self.returned_close_timestamp = datetime.utcnow()
class StocktakingItem(db.Model): __versioned__ = {} id = db.Column(db.Integer, primary_key=True) stocktaking_id = db.Column(db.Integer, db.ForeignKey('stocktaking.id'), nullable=False) item_id = db.Column(db.Integer, db.ForeignKey('item.id'), nullable=False) quantity = db.Column(db.Float, nullable=False) stocktaking = db.relationship('Stocktaking', lazy='joined') item = db.relationship( 'Item', lazy='joined') # required for _apply_quantity_changes() __table_args__ = (db.Index('stocktaking_item__can_not_add_one_item_twice', 'stocktaking_id', 'item_id', unique=True), ) def __repr__(self) -> str: return '{!s} [{!r}]'.format(self.id, self.item)
class WorkItem(db.Model): __versioned__ = {} id = db.Column(db.Integer, primary_key=True) work_id = db.Column(db.Integer, db.ForeignKey('work.id'), nullable=False) item_id = db.Column(db.Integer, db.ForeignKey('item.id'), nullable=False) outbound_quantity = db.Column(db.Float, nullable=False) returned_quantity = db.Column(db.Float) work = db.relationship('Work', lazy='joined') item = db.relationship( 'Item', lazy='joined') # required for _apply_quantity_changes() __table_args__ = (db.Index('work_item__can_not_add_one_item_twice', 'work_id', 'item_id', unique=True), ) def __repr__(self) -> str: return '{!s} [-{!s}, +{!s}]'.format(self.item, self.outbound_quantity, self.returned_quantity)
class Issue(db.Model): id = db.Column(db.Integer, primary_key=True) project = db.relationship('Project', backref='issue') project_id = db.Column(db.Integer, db.ForeignKey('project.id'), nullable=False) tag = db.relationship('Tag', backref='issue') tag_id = db.Column(db.Integer, db.ForeignKey('tag.id'), nullable=True) milestone = db.relationship('Milestone', backref='issue') milestone_id = db.Column(db.Integer, db.ForeignKey('milestone.id'), nullable=True) effort = db.relationship('Effort', backref='issue') effort_id = db.Column(db.Integer, db.ForeignKey('effort.id'), nullable=True) assigned_to = db.relationship('Contact', backref='issue') assigned_to_id = db.Column(db.Integer, db.ForeignKey('contact.id'), nullable=True) column_id = db.Column(db.Integer, db.ForeignKey('column.id'), nullable=False, default=1) column = db.relationship('Column', backref='tasks') title = db.Column(db.String(120), nullable=False) description = db.Column(db.Text, nullable=True) created_at = db.Column(db.DateTime, default=db.func.now()) def __init__(self, title, description, project_id, column_id, tag_id, milestone_id, effort_id, assigned_to_id): self.title = title self.description = description self.project_id = project_id self.column_id = column_id self.tag_id = tag_id self.milestone_id = milestone_id self.effort_id = effort_id self.assigned_to_id = assigned_to_id def __repr__(self): return '<Issue %r>' % self.title
class Transaction(db.Model): __tablename__ = "transactions" id = db.Column(db.Integer, primary_key=True, autoincrement=True) user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False, index=True) datetime = db.Column(db.DateTime, nullable=False, default=datetime.datetime.utcnow) value = db.Column(db.Float, nullable=False) from_currency = db.Column(db.Integer, db.ForeignKey('currencies.id'), nullable=False, index=True) from_volume = db.Column(db.Float, nullable=False) from_wallet = db.Column(db.String(256), nullable=False, default='') to_currency = db.Column(db.Integer, db.ForeignKey('currencies.id'), nullable=False, index=True) to_volume = db.Column(db.Float, nullable=False) to_wallet = db.Column(db.String(256), nullable=False, default='') stake = db.Column(db.Float, nullable=False) broker = db.Column(db.String(256), nullable=False, default='') tx_id = db.Column(db.String(256), nullable=False, default='') notes = db.Column(db.String(256), nullable=False, default='') def __init__(self, user_id, value, from_currency, from_volume, to_currency, to_volume, stake=0, from_wallet='', to_wallet='', broker='', tx_id='', notes='', trans_datetime=None): self.user_id = user_id self.datetime = trans_datetime or datetime.datetime.utcnow() self.value = value self.from_currency = from_currency self.from_volume = from_volume self.to_currency = to_currency self.to_volume = to_volume self.stake = stake self.from_wallet = from_wallet self.to_wallet = to_wallet self.broker = broker self.tx_id = tx_id self.notes = notes
class Post(db.Model): id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(120), nullable=False) body = db.Column(db.Text, nullable=False) user_id = db.Column(db.Integer, db.ForeignKey('user.id')) created_at = db.Column(db.DateTime, default=db.func.now()) def __init__(self, title, body): self.title = title self.body = body self.user_id = g.user.id def __repr__(self): return '<Post %r>' % self.title
class Barcode(db.Model): __versioned__ = {} id = db.Column(db.Integer, primary_key=True) barcode = db.Column(db.String(15), nullable=False, unique=True, index=True) quantity = db.Column(db.Float, nullable=False, default=1.0) item_id = db.Column(db.Integer, db.ForeignKey('item.id'), nullable=False) master = db.Column(db.Boolean, default=False) main = db.Column(db.Boolean, default=False) item = db.relationship('Item') def __repr__(self) -> str: return '{!s} [quantity={!r}, main={!s}, master={!s}]'.format( self.barcode, self.quantity, self.main, self.master)
class Project(db.Model): id = db.Column(db.Integer, primary_key=True) user = db.relationship('Contact', backref='project') user_id = db.Column(db.Integer, db.ForeignKey('contact.id'), nullable=False) name = db.Column(db.String(120), nullable=False) description = db.Column(db.Text, nullable=True) created_at = db.Column(db.DateTime, default=db.func.now()) def __init__(self, name, description, user_id): self.name = name self.description = description self.user_id = user_id def __repr__(self): return '<Project %r>' % self.name
class Item(db.Model): __versioned__ = {} id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(60), nullable=False, unique=True) vendor_id = db.Column(db.Integer, db.ForeignKey('vendor.id'), nullable=False) article_number = db.Column(db.String(20)) quantity = db.Column(db.Float, nullable=False, default=0.0) warning_quantity = db.Column(db.Float, nullable=False, default=0.0) purchase_price = db.Column(db.Float, nullable=False, default=0.0, server_default='0') unit_id = db.Column(db.Integer, db.ForeignKey('unit.id'), nullable=False) location = db.Column(db.String(15)) vendor = db.relationship('Vendor', lazy='joined') unit = db.relationship('Unit', lazy='joined') barcodes = db.relationship('Barcode', lazy='dynamic') def __repr__(self) -> str: return '{!s}'.format(self.name)
class Safe(db.Model): __tablename__ = 'safe' pass_id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(255), unique=True, nullable=False) password = db.Column(db.String(255)) entered_on = db.Column(db.DateTime()) user_id = db.Column(db.Integer, db.ForeignKey('user.id')) user = db.relationship('User', backref=db.backref('safe')) def __init__(self, name=None, password=None): self.username = username self.password = password self.registered_on = datetime.datetime.now()
class UserConfig(db.Model): __versioned__ = {} id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False) name = db.Column(db.String(40), index=True, nullable=False) value = db.Column(db.String(200), nullable=False) configs = db.relationship('User') __table_args__ = (db.Index( 'user_config__can_not_add_one_name_twice_to_a_user', 'user_id', 'name', unique=True), ) def __repr__(self) -> str: return '{!s} [{!r}]'.format(self.id, self.name)
class Recommendation(db.Model): id = db.Column(db.Integer, primary_key=True, autoincrement=True) recommendation_id = db.Column(db.Integer) type = db.Column(db.String) rank = db.Column(db.Float) parent_book_id = db.Column(db.Integer, db.ForeignKey('book.id')) def tojson(self): rec_book = Book.query.filter_by(book_id=self.recommendation_id).first() return { "id": self.id, "parent_book_id": self.parent_book_id, "title": rec_book.title, "author": rec_book.authors, "pub_year": rec_book.pub_year, "avg_rating": rec_book.avg_rating, "img_url": rec_book.img_url, "rank": self.rank, "type": self.type }
class UsersExt(db.Model): __tablename__ = "users_ext" id = db.Column(db.Integer, primary_key=True, autoincrement=True) user_id = db.Column(db.Integer, db.ForeignKey('users.id')) nicename = db.Column(db.String(255), nullable=True) url = db.Column(db.String(255), nullable=True) activation_key = db.Column(db.String(255), nullable=True) status = db.Column(db.Boolean, nullable=True) facebook_id = db.Column(db.String(255), nullable=True) created_at = db.Column(db.DateTime, default=datetime.datetime.now, nullable=True) updated_at = db.Column(db.DateTime, default=datetime.datetime.now, onupdate=datetime.datetime.now, nullable=True) def __init__(self, user_id, nicename, activation_key, facebook_id, status=False, url=None): self.user_id = user_id self.nicename = nicename self.activation_key = activation_key self.status = status self.facebook_id = facebook_id self.url = url def has_activated(self): return self.activation_key is not None def get_id(self): return self.id def __repr__(self): return '<UsersExt {0}>'.format(self.user_id)
class TournamentsToObject(db.Model): id = db.Column(db.Integer, primary_key=True) tournament_id = db.Column(db.Integer, db.ForeignKey('tournament.id')) contestant_id = db.Column(db.Integer, db.ForeignKey('contestant.id')) tournament = db.relationship('Tournament', backref=db.backref('tournamentstoobject', lazy='select'), lazy='subquery') contestant = db.relationship('Contestant', backref=db.backref('tournamentstoobject', lazy='select'), lazy='subquery') def __init__(self, tournament_id, contestant_id): self.tournament_id = tournament_id self.contestant_id = contestant_id def add(self): db.session.add(self) db.session.commit() return self.id @staticmethod def delete(tournament_id, contestant_id): TournamentsToObject.query.filter( TournamentsToObject.tournament_id == tournament_id and TournamentsToObject.contestant_id == contestant_id).delete() db.session.commit() @staticmethod def get_all_obj_in_tournament(id_tournament): return db.session.query(TournamentsToObject.contestant).filter( TournamentsToObject.tournament_id == id_tournament).all() @staticmethod def get_all_objects_to_tournament(tournament_id): return db.session.query(TournamentsToObject).filter( TournamentsToObject.tournament_id == tournament_id).all() @staticmethod def is_exist(tournament_id, contestant_id): if db.session.query(TournamentsToObject).filter( TournamentsToObject.tournament_id == tournament_id and TournamentsToObject.contestant_id == contestant_id).first(): return True return False @staticmethod def get_one_or_none(tournament_id, contestant_id): return db.session.query(TournamentsToObject).filter( TournamentsToObject.tournament_id == tournament_id and TournamentsToObject.contestant_id == contestant_id).first() @staticmethod def get_all(): return TournamentsToObject.query.all() @staticmethod def get_all_people_in_tournament(id_tournament): return db.session.query(TournamentsToObject).filter( TournamentsToObject.tournament_id == id_tournament).all() @staticmethod def get_all_by_user_id(contestant_id): return db.session.query(TournamentsToObject).filter( TournamentsToObject.contestant_id == contestant_id).all() def get_solved_tasks_for_tournament_for_contestant_indices(self): return db.session.query(Attempt.task_id).filter( (Attempt.success == True) & (Attempt.tournament_to_object_id == self.id))
from app.server import db user_to_team = db.Table( 'user_to_team', db.Column('id', db.Integer, primary_key=True), db.Column('user_id', db.Integer, db.ForeignKey('user.id')), db.Column('team_id', db.Integer, db.ForeignKey('team.id')))
class ProjectTbl(Base, Named, db.Model): comment = db.Column(db.BLOB) checkin_date = db.Column(db.Date) principal_investigatorID = db.Column(db.Integer, db.ForeignKey('PrincipalInvestigatorTbl.id')) principal_investigator = db.relationship('PrincipalInvestigatorTbl', backref=db.backref('projects', lazy=True))
class Tournament(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(256)) description = db.Column(db.String(1024)) private = db.Column(db.Boolean()) platform = db.Column(db.Boolean()) invite_link = db.Column(db.String(1024)) creator_id = db.Column(db.Integer(), db.ForeignKey('contestant.id')) creator = db.relationship("Contestant", backref=db.backref("tournaments", lazy='dynamic')) time = db.Column(db.DateTime()) time_to_live = db.Column(db.DateTime()) place = db.Column(db.String(256)) online = db.Column(db.Boolean()) for_team_allowed = db.Column(db.Boolean()) def __init__(self, name, description, creator, time, time_to_live, place, invite_link, private=False, platform=True, online=False, for_team_allowed=True): self.name = name self.description = description self.private = private self.platform = platform self.creator = creator self.time = time self.time_to_live = time_to_live self.place = place.replace(';', ',') self.online = online self.invite_link = invite_link self.for_team_allowed = for_team_allowed def add(self): db.session.add(self) db.session.commit() return self.id def delete(self): db.session.delete(self) db.session.commit() @staticmethod def get_all(): return Tournament.query.all() @staticmethod def get_tournaments_in_future(time_to_live): return db.session.query(Tournament).filter( Tournament.time > time_to_live).all() @staticmethod def allowed_to_user(user_id, tournament_id): return True # TODO: check @staticmethod def update_by_id(id, key, value): db.session.query(Tournament).filter(Tournament.id == id).update( {key: value}, synchronize_session='evaluate') db.session.commit() @staticmethod def is_exist_by_id(id): if Tournament.query.filter(Tournament.id == id): return True return False @staticmethod def get_info(tournament_id): return db.session.query(Tournament).filter( Tournament.id == tournament_id).one()
class LevelTbl(Base, Named, db.Model): irradiationID = db.Column(db.Integer, db.ForeignKey('IrradiationTbl.id')) irradiation = db.relationship('IrradiationTbl')
class SampleTbl(Base, Named, db.Model): projectID = db.Column(db.Integer, db.ForeignKey('ProjectTbl.id')) materialID = db.Column(db.Integer, db.ForeignKey('MaterialTbl.id')) project = db.relationship('ProjectTbl', backref=db.backref('samples', lazy=True)) material = db.relationship('MaterialTbl', backref=db.backref('materials', lazy=True))
class User(Contestant): id = db.Column(db.Integer, db.ForeignKey('contestant.id'), primary_key=True) name = db.Column(db.String(256)) surname = db.Column(db.String(256), nullable=True) email = db.Column(db.String(256)) login = db.Column(db.String(256)) password = db.Column(db.String(256)) code = db.Column(db.String(256)) phone = db.Column(db.String(256), nullable=True) photo = db.Column(db.String(20), nullable=True) is_confirmed = db.Column(db.Boolean(), default=False) __mapper_args__ = { 'polymorphic_identity': 'user' } def __init__(self, name, email, login, password, code): self.type = 'user' self.name = name self.email = email self.login = login self.password = password self.code = code def save(self): db.session.add(self) db.session.commit() return self.id @staticmethod def get_user_by_id(user_id): return db.session.query(User).filter(User.id == user_id).first() @staticmethod def last_id(): return db.session.query(User.id).order_by(User.id.desc()).first() @staticmethod def get_users(): return User.query.all() @staticmethod def is_exist_by_id(id): if User.query.filter(User.id == id).first(): return True return False @staticmethod def update_by_login(login, key, value): db.session.query(User).filter(User.login == login).update({key: value}, synchronize_session='evaluate') db.session.commit() @staticmethod def change_password_by_email(email, password): secret_password = sha256(password.encode()).hexdigest() User.query.filter(User.email == email).update(dict(password=secret_password)) db.session.commit() @staticmethod def user_auth(): if session['logged_in'] and 'username' in session: return True return False @staticmethod def get_all(): return User.query.all() @staticmethod def get_id_from_login(s): return db.session.query(User.id).filter(User.login == s) __mapper_args__ = { 'polymorphic_identity': 'user' }
class User(BaseModel): """ Creates user object """ __tablename__ = "users" given_names = db.Column(db.String(length=35), nullable=False) surname = db.Column(db.String(length=35), nullable=False) _identification = db.Column(JSONB, default={}, nullable=True) email = db.Column(db.String, index=True, nullable=True, unique=True) phone = db.Column(db.String(length=13), index=True, nullable=True, unique=True) address = db.Column(db.String) date_of_birth = db.Column(db.Date) password_hash = db.Column(db.String(200)) _otp_secret = db.Column(db.String(200)) is_activated = db.Column(db.Boolean, default=False) signup_method = db.Column(db.Enum(SignupMethod)) password_reset_tokens = db.Column(MutableList.as_mutable(ARRAY(db.String))) parent_organization_id = db.Column(db.Integer, db.ForeignKey("organizations.id")) parent_organization = db.relationship( "Organization", primaryjoin=Organization.id == parent_organization_id, lazy=True, uselist=False, ) role_id = db.Column(db.Integer, db.ForeignKey("roles.id")) role = db.relationship("Role", back_populates="users") @hybrid_property def identification(self): return self._identification def set_identification_details(self, id_type: str, id_value: str): """ :param id_type: ID type of user [app/server/constants] :param id_value: ID value. :return: JSON object with id details eg: {'NATIONAL_ID': '12345678'}. """ # check if id type is in identification types if id_type not in IDENTIFICATION_TYPES: raise IdentificationTypeNotFoundException( f"Identification type {id_type} not valid") # check that id value is supplied if id_value is None: raise ValueError("ID cannot be empty") # corrective method in case identification is set to none if self._identification is None: self._identification = {} # set values self._identification[id_type] = id_value flag_modified(self, "_identification") @staticmethod def salt_hash_secret(password: str): """ :param password: user password. :return: encrypted password with system password pepper. """ fernet_key = Fernet(config.PASSWORD_PEPPER) return fernet_key.encrypt( bcrypt.hashpw(password.encode(), bcrypt.gensalt())).decode() @staticmethod def check_salt_hashed_secret(password, hashed_password): """ :param password: provided user password. :param hashed_password: hashed password stored in db. :return: boolean if password matches. """ fernet_key = Fernet(config.PASSWORD_PEPPER) hashed_password = fernet_key.decrypt(hashed_password.encode()) return bcrypt.checkpw(password.encode(), hashed_password) def hash_password(self, password): """ :param password: user password. :return: hashed password with salt + pepper. """ self.password_hash = self.salt_hash_secret(password) def verify_password(self, password): """ :param password: user password. :return: boolean if password matches hashed password in db. """ return self.check_salt_hashed_secret(password, self.password_hash) def encode_auth_token(self): """ Generates the authentication token. :return: JSON Web Token. """ try: payload = { "exp": datetime.utcnow() + timedelta(days=7, seconds=0), "iat": datetime.utcnow(), "id": self.id, "role": self.role.name, } return jwt.encode(payload, config.SECRET_KEY, algorithm="HS256") except Exception as exception: return exception @staticmethod def decode_auth_token(token, token_type="authentication"): """ Validates the auth token :param token_type: defined token type. :param token: JSON Web Token :return: integer|string """ try: payload = jwt.decode(jwt=token, key=config.SECRET_KEY, algorithms="HS256") is_blacklisted_token = BlacklistedToken.check_if_blacklisted( token=token) if is_blacklisted_token: return "Token is blacklisted. Please log in again." else: return payload except jwt.ExpiredSignatureError: return f"{token_type} Token Signature expired." except jwt.InvalidTokenError: return f"Invalid {token_type} Token." def encode_single_use_jws(self, token_type): """ :param token_type: token type to sign. :return: JSON Web Signature. """ signature = TimedJSONWebSignatureSerializer(config.SECRET_KEY, expires_in=(60 * 60 * 24)) return signature.dumps({ "id": self.id, "type": token_type }).decode("utf-8") @classmethod def decode_single_use_jws(cls, token, required_token_type): """ :param token: JSON Web Token to verify. :param required_token_type: token type expected in JSON Web Signature. :return: JSON response. """ try: # define signature with application signature = TimedJSONWebSignatureSerializer(config.SECRET_KEY) # get data from signature data = signature.loads(token.encode("utf-8")) # get user_id user_id = data.get("id") # get token type token_type = data.get("type") # check if token type is equivalent if token_type != required_token_type: return { "status": "Fail", "message": f"Wrong token type (needed {required_token_type})", } # check if user_id is present if not user_id: return {"status": "Fail", "message": "No User ID provided."} # check if user exists in DB user = (cls.query.filter_by(id=user_id).execution_options( show_all=True).first()) # if user is not found if not user: return {"status": "Fail", "message": "User not found."} return {"status": "Success", "user": user} except BadSignature: return {"status": "Fail", "message": "Token signature not valid."} except SignatureExpired: return {"status": "Fail", "message": "Token has expired."} except Exception as exception: return {"status": "Fail", "message": exception} def clear_invalid_password_reset_tokens(self): if self.password_reset_tokens is None: self.password_reset_tokens = [] valid_tokens = [] for token in self.password_reset_tokens: decoded_token_response = self.decode_single_use_jws( token=token, required_token_type="password_reset") is_valid_token = decoded_token_response.get("status") == "Success" if is_valid_token: valid_tokens.append(token) return valid_tokens def save_password_reset_token(self, password_reset_token): if self.password_reset_tokens is None: self.password_reset_tokens = [] self.password_reset_tokens.append(password_reset_token) def check_is_used_password_reset_token(self, password_reset_token): self.clear_invalid_password_reset_tokens() is_used = password_reset_token not in self.password_reset_tokens return is_used def remove_all_password_reset_tokens(self): self.password_reset_tokens = [] def set_otp_secret(self): # generate random otp_secret otp_secret = pyotp.random_base32() # encrypt otp secret token = fernet_encrypt(otp_secret) # save otp secret self._otp_secret = token.decode("utf-8") # generate one time password [expires in 1 hour] one_time_password = pyotp.TOTP(otp_secret, interval=3600).now() return one_time_password def get_otp_secret(self): return fernet_decrypt(self._otp_secret.encode("utf-8")) def verify_otp(self, one_time_password, expiry_interval): # get secret used to create one time password otp_secret = self.get_otp_secret() # verify one time password validity is_valid = pyotp.TOTP( otp_secret, interval=expiry_interval).verify(one_time_password) return is_valid def bind_user_to_organization(self, organization: Organization): if not self.parent_organization: self.parent_organization = organization def get_user_organization(self): return self.organization # method responsible for returning user details that make it easier to identify a user. def user_details(self): if self.given_names and self.surname: return f"{self.given_names} {self.surname} {self.phone}" return f"{self.phone}" def set_role(self, role: str): # check that role is supported in system constants if role not in SUPPORTED_ROLES: raise RoleNotFoundException("The provided role is not supported") user_role = Role.query.filter_by(name=role).first() self.role_id = user_role.id def __repr__(self): return "User %r" % self.phone