class Item_cart(db.Model):
__tablename__ = 'Item_cart'
id = db.Column(db.Integer, primary_key=True)
Item_name = db.Column(db.String, nullable=True)
created_at = db.Column(db.Integer, default=timestamp)
def to_json(self):
return {
'item_name': self.first_name,
}
def __repr__(self):
return '<INFO ID={id} NAME={first_name} UPDATED_AT={created_at}>'.format(
id=self.id, Item_cart=self.Item_name, created_at=self.created_at)
class TimeDbModel(db.Model):
# SQLAlchemy table definition
# TODO add function to delete all times from the table
# TODO add also column - time and date created
__tablename__ = "timetable"
id = db.Column(db.Integer, primary_key=True)
time_measured = db.Column(db.Interval, nullable=False)
order_number = db.Column(
db.Integer, nullable=False) # number received from TIMY3 [1-99]
def __repr__(self):
return "<TimeDbModel(time_measured='%s', order='%s')>" % (
self.time_measured, self.order_number)
def json(self):
return {
"id": self.id,
"time_measured": str(self.time_measured)[:-4],
"order_number": self.order_number
}
def __init__(self, time_measured, order_number):
self.time_measured = time_measured
self.order_number = order_number
def save_to_db(self):
""" Save instance to a database
:return:
"""
try:
db.session.add(self)
db.session.commit()
except exc.IntegrityError as e:
db.session().rollback()
@classmethod
def get_by_id(cls, timedb_id):
return db.session.query(cls).filter_by(id=timedb_id).one()
@classmethod
def list_all(cls):
return cls.query.all()
class StartlistModel(db.Model):
# SQLAlchemy table definition
__tablename__ = "startlist"
id = db.Column(db.Integer, primary_key=True)
startlist_id = db.Column(db.Integer, db.ForeignKey('startlist_details.id'))
# category_id = db.Column(db.Integer, db.ForeignKey('categories.id'))
participant_id = db.Column(db.Integer, db.ForeignKey('participants.id'))
start_position = db.Column(db.Integer)
start_round = db.Column(db.Integer)
time_measured = db.Column(db.Interval, nullable=True)
# db.PrimaryKeyConstraint('startlist_id', 'category_id', 'participant_id', name='startlist_pk')
startlist_details = db.relationship("StartlistNameModel",
back_populates="startlist")
#category = db.relationship("CategoryModel", back_populates="startlist")
participants = db.relationship("ParticipantModel",
back_populates="startlist")
__table_args__ = (db.UniqueConstraint(
'startlist_id',
'participant_id',
), )
# __table_args__ = (db.UniqueConstraint('startlist_id','category_id', 'participant_id',),)
# def __init__(self, startlist_id, participant_id, start_position, start_round):
def __init__(self, startlist_id, participant_id, start_position,
start_round):
self.startlist_id = startlist_id
#self.category_id = category_id
self.participant_id = participant_id
self.start_position = start_position
self.start_round = start_round
# TODO add time variable
# TODO add category index number
def json(self):
return {
"startlist_id": self.startlist_id,
#"category_id": self.category_id,
"participant_id": self.participant_id,
"start_position": self.start_position,
"start_round": self.start_round,
}
def save_to_db(self):
""" Save instance to a database
:return:
"""
try:
db.session.add(self)
db.session.commit()
except exc.IntegrityError as e:
db.session().rollback()
# WORKING - DO NOT TOUCH
# is this one used?
# @classmethod
# def get_startlist_by_category(cls, category_id):
# return db.session.query(StartlistModel).\
# filter_by(category_id=category_id).\
# order_by(StartlistModel.participant_id).\
# all()
# WORKING - DO NOT TOUCH
# is this one used?
# @classmethod
# def get_startlist_by_category_with_names(cls, category_id):
# return db.session.query(StartlistModel, ParticipantModel).\
# filter(StartlistModel.participant_id == ParticipantModel.id).\
# filter(StartlistModel.category_id == category_id).\
# order_by(ParticipantModel.id).\
# all()
@classmethod
def get_records_by_startlist_id(cls, startlist_name_id):
return db.session.query(StartlistModel, ParticipantModel).\
filter(StartlistModel.participant_id == ParticipantModel.id).\
filter(StartlistModel.startlist_id == startlist_name_id).\
order_by(StartlistModel.id).\
all()
@classmethod
def get_records_by_startlist_id_order_by_round_position(
cls, startlist_name_id):
return db.session.query(StartlistModel, ParticipantModel). \
filter(StartlistModel.participant_id == ParticipantModel.id). \
filter(StartlistModel.startlist_id == startlist_name_id). \
order_by(StartlistModel.start_round). \
order_by(StartlistModel.start_position). \
all()
@classmethod
def get_records_by_startlist_id_and_round_number(cls, startlist_name_id,
round_number):
return db.session.query(StartlistModel, ParticipantModel). \
filter(StartlistModel.participant_id == ParticipantModel.id). \
filter(StartlistModel.startlist_id == startlist_name_id). \
filter(StartlistModel.start_round == round_number). \
order_by(StartlistModel.start_position). \
all()
@classmethod
def get_records_by_startlist_id_order_by_time(cls, startlist_name_id):
return db.session.query(StartlistModel, ParticipantModel). \
filter(StartlistModel.participant_id == ParticipantModel.id). \
filter(StartlistModel.startlist_id == startlist_name_id). \
order_by(StartlistModel.time_measured). \
all()
@classmethod
def get_by_id(cls, startlist_id):
"""
Used by startlist/view.next_round
"""
return db.session.query(StartlistModel).filter_by(
id=startlist_id).one()
@classmethod
def get_by_startlist_id(cls, startlist_id):
"""
Used for deletition of a startlist
"""
return db.session.query(StartlistModel).\
filter_by(startlist_id=startlist_id).\
order_by(StartlistModel.id).\
all()
@classmethod
def list_all(cls):
return cls.query.all()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
@classmethod
def delete_all_rows(cls):
all_rows = cls.list_all()
for row in all_rows:
row.delete_from_db()
class StartlistNameModel(db.Model):
# SQLAlchemy table definition
__tablename__ = "startlist_details"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80), nullable=False)
startline_count = db.Column(db.Integer, nullable=False)
startlist_rounds = db.Column(db.Integer, nullable=True)
measured_flag = db.Column(db.Boolean, unique=False, default=False)
round1_flag = db.Column(db.Boolean, unique=False, default=False)
# TODO add other details like date of creation, name of author, ...
startlist = db.relationship("StartlistModel",
back_populates='startlist_details',
cascade="all, delete, delete-orphan")
def __init__(self, name, startline_count, round1_flag=False):
self.name = name
self.startline_count = startline_count
self.round1_flag = round1_flag
def json(self):
return {
"id": self.id,
"name": self.name,
"startline_count": self.startline_count,
"startlist_rounds": self.startlist_rounds
}
def save_to_db(self):
""" Save instance to a database
:return:
"""
try:
db.session.add(self)
db.session.commit()
except exc.IntegrityError as e:
db.session().rollback()
@classmethod
def get_by_id(cls, startlist_id):
return db.session.query(cls).filter_by(id=startlist_id).one()
@classmethod
def get_name_by_id(cls, startlist_id):
return db.session.query(cls).filter_by(id=startlist_id).one().name
@classmethod
def list_measured_all(cls):
return db.session.query(cls).\
filter_by(measured_flag=True).\
order_by(StartlistNameModel.id).\
all()
@classmethod
def list_measured_and_round1_all(cls):
return db.session.query(cls).\
filter_by(measured_flag=True).\
filter_by(round1_flag=True).\
order_by(StartlistNameModel.id).\
all()
@classmethod
def list_measured_and_not_final_all(cls):
return db.session.query(cls).\
filter_by(measured_flag=True).\
filter_by(round1_flag=False).\
order_by(StartlistNameModel.id).\
all()
@classmethod
def list_round1_all(cls):
"""
Used to list all startlists of round 1 runs
"""
return db.session.query(cls).filter_by(round1_flag=True).order_by(
StartlistNameModel.id).all()
@classmethod
def list_not_round1_all(cls):
"""
Used to list all startlist of final runs
"""
return db.session.query(cls).filter_by(round1_flag=False).order_by(
StartlistNameModel.id).all()
@classmethod
def list_all(cls):
return cls.query.all()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
@classmethod
def delete_all_rows(cls):
all_rows = cls.list_all()
for row in all_rows:
row.delete_from_db()
class ParticipantModel(db.Model):
__tablename__ = "participants"
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String(80), nullable=False)
last_name = db.Column(db.String(80), nullable=False)
gender = db.Column(db.String(6), nullable=False)
year = db.Column(db.Integer, nullable=False)
startlist = db.relationship("StartlistModel",
back_populates='participants',
cascade="all, delete, delete-orphan")
__table_args__ = (db.UniqueConstraint('first_name', 'last_name', 'year'), )
def __init__(self, first_name, last_name, gender, year):
self.first_name = first_name
self.last_name = last_name
self.gender = gender
self.year = int(year)
def json(self):
return {
"first_name": self.first_name,
"last_name": self.last_name,
"gender": self.gender,
"year": self.year,
}
@classmethod
def find_by_year(cls, year):
# 'guery' is a SQLAlchemy query builder
# SELECT FROM items WHERE name=name LIMIT 1
# returned data gets converted into ItemModel object
return cls.query.filter_by(year=int(year))
@classmethod
def find_by_gender_and_year(cls, gender, year):
return cls.query.filter_by(gender=gender, year=year)
def save_to_db(self):
''' Function does update and insert to the DB (upserting)
'''
# SQLAlchemy can translate object into the row
try:
db.session.add(self)
db.session.commit()
except exc.IntegrityError as e:
db.session().rollback()
@classmethod
def get_participants_ordered(cls):
return db.session.query(ParticipantModel.id,
ParticipantModel.last_name,
ParticipantModel.first_name,
ParticipantModel.gender,
ParticipantModel.year).\
order_by(ParticipantModel.last_name).\
order_by(ParticipantModel.first_name).\
all()
@classmethod
def get_by_id(cls, participant_id):
return db.session.query(cls).filter_by(id=participant_id).one()
@staticmethod
def drop_table():
db.drop_all()
@classmethod
def list_all(cls):
return cls.query.all()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
@classmethod
def delete_all_rows(cls):
all_rows = cls.list_all()
for row in all_rows:
row.delete_from_db()
class CategoryModel(db.Model):
# SQLAlchemy table definition
__tablename__ = "categories"
id = db.Column(db.Integer, primary_key=True)
category_name = db.Column(db.String(20), nullable=False)
gender = db.Column(db.String(6), nullable=False)
year_start = db.Column(db.Integer, nullable=False)
year_end = db.Column(db.Integer, nullable=False)
__table_args__ = (db.UniqueConstraint('category_name', 'gender',
'year_start', 'year_end'), )
# startlist = db.relationship("StartlistModel",
# back_populates='category',
# cascade="all, delete, delete-orphan")
# Foreign key definition. For the future
# store_id = db.Column(db.Integer, db.ForeignKey('stores.id'))
# store = db.relationship('StoreModel')
def __init__(self, category_name, gender, year_start, year_end):
self.category_name = category_name
self.gender = gender
self.year_start = int(year_start)
self.year_end = int(year_end)
def json(self):
return {
"category_name": self.category_name,
"gender": self.gender,
"year_start": self.year_start,
"year_end": self.year_end
}
def save_to_db(self):
''' Function does update and insert to the DB (upserting)
'''
# SQLAlchemy can translate object into the row
try:
db.session.add(self)
db.session.commit()
except exc.IntegrityError as e:
db.session().rollback()
@classmethod
def find_by_id(cls, category_id):
return db.session.query(cls).filter_by(id=category_id).one()
@classmethod
def list_categories_ordered(cls):
return db.session.query(CategoryModel.id, CategoryModel.category_name).\
order_by(CategoryModel.id).\
all()
@classmethod
def list_all(cls):
return cls.query.all()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
@classmethod
def delete_all_rows(cls):
all_rows = cls.list_all()
for row in all_rows:
row.delete_from_db()
class User(db.Model):
__tablename__ = 'users'
_id = db.Column(db.String(80), primary_key=True)
username = db.Column(db.String(80))
email = db.Column(db.String(80))
password = db.Column(db.String(80))
permission = db.Column(db.Integer)
active = db.Column(db.Integer)
authenticated = db.Column(db.Integer)
def __init__(self,
username,
email,
password,
active,
permission,
authenticated,
_id=None):
self.username = username
self.email = email
self.password = password
self.permission = 3 if username == 'administrator' else permission
self.active = True if username == 'administrator' else active
self._id = uuid.uuid4().hex if _id is None else _id
self.authenticated = authenticated
def __repr__(self):
return "<User {}>".format(self.email)
@staticmethod
def is_login_valid(username, password):
"""
This method verifies that an e-mail/password combo ( as sent by the site forms) is valid or not.
checks that the e-mail exists, and that the password associated to that e-mail is correct.
:param email: The user's email
:param password: A sha512 hashed password
:return: True if valid, False if otherwise
"""
user_data = User.query.filter_by(
username=username).first() #Password in sha512 - >pbkdf2_sha512
if user_data is None:
raise UserErrors.UserNotExistsError("Your user does not exist.")
if not user_data['active']:
raise UserErrors.UserDisabledError("Your user is disabled.")
if not Utils.check_hashed_password(password, user_data['password']):
raise UserErrors.IncorrectPasswordError("Your password was wrong.")
return True
@staticmethod
def register_user(username, email, password, active, permission):
"""
This method registers a user using e-mail and password.
The password already comes hashed as a sha-512
:param email: user's e-mail (might be invalid
:param password: sha512-hsahed password
:return: True if registered successfully, or False otherwise(exceptions can also be raised)
"""
user_data = User.query.filter_by(email=email).first()
if user_data is not None:
raise UserErrors.UserAlreadyRegisteredError(
"The e-mail you used to register already exists.")
if not Utils.email_is_valid(email):
raise UserErrors.InvalidEmailError(
"The e-mail does not have the right format.")
user_data = User.query.filter_by(username=username).first()
if user_data is not None:
raise UserErrors.UserAlreadyRegisteredError(
"The username you used to register already exists.")
User(username, email, Utils.hash_password(password), active,
permission, False).save_to_db()
return True
def save_to_db(self):
db.session.add(self)
def json(self):
return {
"_id": self._id,
"username": self.username,
"email": self.email,
"password": self.password,
"active": self.active,
"permission": self.permission,
"authenticated": self.authenticated
}
@classmethod
def find_by_email(cls, email):
return cls.query.filter_by(email=email).first()
#return cls(**db.find_one(UserConstants.COLLECTION,{'email': email}))
@classmethod
def find_by_username(cls, username):
return cls.query.filter_by(username=username).first()
#return cls(**db.find_one(UserConstants.COLLECTION,{'username': username}))
@classmethod
def find_all(cls):
return User.query.all()
#return [cls(**elem) for elem in db.find(UserConstants.COLLECTION,{})]
def get_alerts(self):
return Alert.find_by_user_email(self.email)
@classmethod
def find_by_id(cls, _id):
return User.query.filter_by(_id=_id).first()
def deactivate(self):
self.active = False
self.save_to_db()
def activate(self):
self.active = True
self.save_to_mdb()
def is_active(self):
return self.active
def get_id(self):
return self._id
def is_authenticated(self):
"""Return True if the user is authenticated."""
return self.authenticated
def is_anonymous(self):
"""False, as anonymous users aren't supported."""
return False
#def get_users(self):
#return User.find_all()
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
@classmethod
def find_by_username(cls, username):
return cls.query.filter_by(username=username).first(
) # SELECT * FROM items WHERE name = name LIMIT 1