class Competition(db.Model):
__tablename__ = 'competition'
# id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255), primary_key=True)
date = db.Column(db.DateTime, primary_key=True)
field_id = db.Column(db.Integer, ForeignKey("field.id"))
# field = relationship("Field", backref=db.backref("competition"), uselist=False)
questions = relationship("Question")
owners = relationship('Administrator',
secondary=Ownership,
backref='competition')
participations = relationship('Participation')
def __init__(self, name, date, field_id):
self.name = name
self.date = date
self.field_id = field_id
def as_dict(self):
return {c.name: getattr(self, c.name) for c in self.__table__.columns}
def is_editable(self, user_id):
""" Returns True if this competition is editable by the user with given id """
return user_id in [o.id for o in self.owners]
class Role(db.Model):
__tablename__ = 'roles'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64), unique=True)
default = db.Column(db.Boolean, default=False, index=True)
permissions = db.Column(db.Integer)
users = db.relationship('User', backref='role', lazy='dynamic')
@staticmethod
def insert_roles():
roles = {
'Administrator': (Permission.FULL_ACCESS, False),
'Student': (Permission.STUDENT_ACCESS, False),
'Unknown': (Permission.PUBLIC_ACCESS, True)
}
for r in roles:
role = Role.query.filter_by(name=r).first()
if role is None:
role = Role(name=r)
role.permissions = roles[r][0]
role.default = roles[r][1]
db.session.add(role)
db.session.commit()
class Field(db.Model):
__tablename__ = 'field'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255), unique=True, nullable=False)
competitions = db.relationship("Competition", backref='field', lazy=False)
def __init__(self, name):
self.name = name
class Result(db.Model):
__tablename__ = 'result'
participation_id = db.Column(db.Integer,
db.ForeignKey('participation.id'),
primary_key=True)
participation = relationship("Participation",
backref=db.backref("result", uselist=False))
points_scored = db.Column(db.Float, nullable=False)
def __init__(self, participation_id, points_scored):
self.participation_id = participation_id
self.points_scored = points_scored
class Student(User):
__tablename__ = 'student'
user_id = db.Column(db.Integer, ForeignKey("user.id"), primary_key=True)
index_number = db.Column(db.String(255), unique=True, nullable=False)
study_year = db.Column(db.Integer, nullable=False)
participations = relationship("Participation")
confirmed = db.Column(db.Boolean, default=False)
__mapper_args__ = {
'polymorphic_identity': 'student',
}
def __init__(self, name, surname, email, index_number, study_year):
super(Student, self).__init__(name, surname, email, 'student')
self.index_number = index_number
self.study_year = study_year
self.confirmed = False
self.role = Role.query.filter_by(
permissions=Permission.STUDENT_ACCESS).first()
def as_dict(self):
return {c.name: getattr(self, c.name) for c in self.__table__.columns}
def is_administrator(self):
return False
# Registration related section
def generate_confirmation_token(self, expiration=3600):
from flask import current_app
s = Serializer(current_app.config.get('SECRET_KEY', 'secret_key'),
expiration)
return s.dumps(dict(confirm=self.user_id))
def confirm_student(self, token):
from flask import current_app
s = Serializer(current_app.config.get('SECRET_KEY', 'secret_key'))
try:
data = s.loads(token)
except:
return False
if data.get('confirm') != self.user_id:
return False
self.confirmed = True
db.session.commit()
return True
class Participation(db.Model):
__tablename__ = 'participation'
__table_args__ = (
db.ForeignKeyConstraint(
['competition_name', 'competition_date'],
['competition.name', 'competition.date']
),
)
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, ForeignKey("student.user_id"))
competition_name = db.Column(db.String(255))
competition_date = db.Column(db.DateTime)
def __init__(self, user_id, competition_name, competition_date):
self.user_id = user_id
self.competition_name = competition_name
self.competition_date = competition_date
def as_dict(self):
return {c.name: getattr(self, c.name) for c in self.__table__.columns}
class Administrator(User):
__tablename__ = 'administrator'
user_id = db.Column(db.Integer, ForeignKey("user.id"), primary_key=True)
position = db.Column(db.String(255), nullable=False)
competitions = relationship("Competition",
secondary=Ownership,
backref='administrator')
__mapper_args__ = {
'polymorphic_identity': 'administrator',
}
def __init__(self, name, surname, email, position):
super(Administrator, self).__init__(name, surname, email,
'administrator')
self.position = position
self.role = Role.query.filter_by(
permissions=Permission.FULL_ACCESS).first()
def is_administrator(self):
return True
class User(UserMixin, db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key=True, index=True)
name = db.Column(db.String(255), nullable=False)
surname = db.Column(db.String(255), nullable=False)
email = db.Column(db.String(255), unique=True, nullable=False, index=True)
password_hash = db.Column(db.String(128), index=True)
type = db.Column(db.String(50), nullable=False)
role_id = db.Column(db.Integer, ForeignKey("roles.id"))
def __init__(self, name, surname, email, type):
self.name = name
self.surname = surname
self.email = email
self.type = type
# Default role with full access
if self.role is None:
self.role = Role.query.filter_by(default=True).first()
def as_dict(self):
return {c.name: getattr(self, c.name) for c in self.__table__.columns}
@property
def password(self):
raise AttributeError('Password is not a readable attribute')
@password.setter
def password(self, pw):
self.password_hash = generate_password_hash(pw)
def verify_password(self, pw):
return check_password_hash(self.password_hash, pw)
def __repr__(self):
return '<User: %r>' % self.name
__mapper_args__ = {'polymorphic_identity': 'user', 'polymorphic_on': type}
# Role verification
def can(self, permissions):
return self.role is not None and \
(self.role.permissions & permissions) == permissions
def is_administrator(self):
return self.can(Permission.FULL_ACCESS)
class Question(db.Model):
__tablename__ = 'question'
__table_args__ = (
db.ForeignKeyConstraint(
['competition_name', 'competition_date'],
['competition.name', 'competition.date']
),
)
id = db.Column(db.Integer, primary_key=True)
text = db.Column(db.String(1024), nullable=False)
ordinal_number = db.Column(db.Integer, nullable=False)
max_score = db.Column(db.Float, nullable=False)
competition_name = db.Column(db.String(255))
competition_date = db.Column(db.DateTime)
def __init__(self, text, ordinal_number, max_score, competition_name, competition_date):
self.text = text
self.ordinal_number = ordinal_number
self.max_score = max_score
self.competition_name = competition_name
self.competition_date = competition_date
from competition import db
Ownership = db.Table(
'ownership', db.Column('id', db.Integer, primary_key=True),
db.Column('user_id', db.Integer, db.ForeignKey("administrator.user_id")),
db.Column('competition_name', db.String(255)),
db.Column('competition_date', db.DateTime),
db.ForeignKeyConstraint(['competition_name', 'competition_date'],
['competition.name', 'competition.date']))
