class Category(DB.Model):
"""
Category model
A category groups challenges together under a common theme (programming, web, etc).
A category is associated to a single event and should have a unique name within that event
(events A and B can both have a category C, but event A cannot have two categories C).
"""
__tablename__ = 'Categories'
id = DB.Column(DB.Integer, primary_key=True)
"""The unique ID of the category. Should be generated by the database. Used as primary key."""
event_id = DB.Column(DB.Integer, ForeignKey('Events.id'), nullable=True)
"""The ID of the event the category belongs to. Used as foreign key."""
name = DB.Column(DB.String(64), index=True)
"""The name of the category. Max 64 characters."""
__table_args__ = (UniqueConstraint('event_id',
'name',
name='categories_event_name_uc'), )
challenges = relationship('Challenge', lazy='noload')
def __repr__(self):
return '<Category id:{} event_id:{} name:{}>'.format(
self.id, self.event_id, self.name)
def __eq__(self, other):
return self.id == other.id and \
self.name == other.name and \
self.event_id == other.event_id
class EventAdministrator(DB.Model):
"""
EventAdministrators model. This is an association table between administrators and events.
"""
__tablename__ = 'EventAdministrators'
id = DB.Column(DB.Integer, primary_key=True)
"""The unique ID of the row. Should be generated by the database. Used as primary key."""
event_id = DB.Column(DB.Integer, ForeignKey('Events.id'), nullable=True)
"""The ID of the Event that this row associates. Used as foreign key."""
administrator_id = DB.Column(DB.Integer,
ForeignKey('Administrators.id'),
nullable=True)
"""The ID of the Administrator that this row associates. Used as foreign key."""
event = relationship('Event',
back_populates='event_administrators',
lazy='joined')
administrator = relationship('Administrator',
back_populates='event_administrators',
lazy='joined')
roles = relationship('RoleAssociation',
back_populates='event_administrator',
lazy='joined')
__table_args__ = (UniqueConstraint(
'event_id',
'administrator_id',
name='event_administrator_event_administrator_uc'), )
def __repr__(self):
return '<EventAdministrator id:{} event_id:{} administrator_id:{}>' \
.format(self.id, self.event_id, self.administrator_id)
class Participant(DB.Model):
"""
Participant model
An event participant. A participant is an association between an event, a user, and optionally a team.
"""
__tablename__ = 'Participants'
id = DB.Column(DB.Integer, primary_key=True)
"""The unique ID of the participant. Should be generated by the database. Used as primary key."""
event_id = DB.Column(DB.Integer, ForeignKey('Events.id'), nullable=True)
"""The ID of the event the participant is associated with. Used as a foreign key."""
user_id = DB.Column(DB.Integer,
ForeignKey('Users.id'),
nullable=True,
unique=True)
"""The ID of the event the participant is associated with. Used as a foreign key."""
teams = association_proxy('teamInfo', 'team')
user = relationship('User', backref='participant', lazy='joined')
def __repr__(self):
return '<Participant id:{} event_id:{} user_id:{}>'.format(
self.id, self.event_id, self.user_id)
def get_team(self) -> Optional[Team]:
"""
:return: The team of the participant, or none if the participant has no team.
"""
return None if len(self.teams) == 0 else self.teams[0]
class TeamRequest(DB.Model):
"""TeamRequest model"""
id = DB.Column(DB.Integer, primary_key=True)
__tablename__ = 'TeamRequests'
team_id = DB.Column(DB.Integer, ForeignKey('Teams.id'), nullable=True)
participant_id = DB.Column(DB.Integer,
ForeignKey('Participants.id'),
nullable=True)
requested_at = DB.Column(DB.DateTime, server_default=DB.func.now())
team = relationship("Team", back_populates="requests")
participant = relationship("Participant")
def __repr__(self):
return '<TeamRequests id:{} team_id:{} participant_id:{}>'.format(
self.id, self.team_id, self.participant_id)
def __eq__(self, other):
return self.id == other.id and \
self.team_id == other.team_id and \
self.participant_id == other.participant_id and \
self.requested_at == other.requested_at
def __hash__(self):
return hash(self.__dict__.values())
class TeamMember(DB.Model):
"""TeamMember model"""
id = DB.Column(DB.Integer, primary_key=True)
__tablename__ = 'TeamMembers'
team_id = DB.Column(DB.Integer, ForeignKey('Teams.id'), nullable=True)
participant_id = DB.Column(DB.Integer,
ForeignKey('Participants.id'),
nullable=True)
# Let a team have many captain, since a captain can accept members
captain = DB.Column(DB.Boolean, default=False)
team = relationship("Team", back_populates="members")
participant = relationship("Participant", backref=backref('teamInfo'))
def __repr__(self):
return '<Team id:{} team_id:{} participant_id:{} captain:{}'\
.format(self.id, self.team_id, self.participant_id, self.captain)
def __eq__(self, other):
return self.team_id == other.team_id and \
self.participant_id == other.participant_id and \
self.captain == other.captain
def __hash__(self):
return hash(self.__dict__.values())
class User(UserMixin, DB.Model):
"""
User model
A user of the application (event participant or admin). A user is only represented by its username and
authenticates on the platform with its email and password.
"""
__tablename__ = 'Users'
id = DB.Column(DB.Integer, primary_key=True)
"""The unique ID of the user. Should be generated by the database. Used as primary key."""
email = DB.Column(DB.String(255), index=True)
"""The user's email. Unique. Max 255 characters."""
username = DB.Column(DB.String(64), index=True)
"""The user's display name. Unique. Max 64 characters."""
password_hash = DB.Column(DB.String(128))
"""
The hash of the user's password. Should always be set with the 'set_password' method. Max
128 characters.
"""
teams = association_proxy('teamInfo', 'team')
def set_password(self, password: str):
"""Hash and set the user's password"""
self.password_hash = generate_password_hash(password)
def check_password(self, password: str) -> bool:
"""Check whether or not this is the user's password"""
return check_password_hash(self.password_hash, password)
def get_team(self) -> Optional[Team]:
"""
:return: The team of the user, or none if the user has no team.
"""
participant = self.get_participant()
return None if not participant else participant.get_team()
def get_participant(self) -> Optional[Participant]:
"""
:return: The participant associated with the user, or none if the user has no participant.
"""
return Participant.query.filter_by(user_id=self.id).first()
def get_administrator(self) -> Optional[Administrator]:
"""
:return: The administrator associated with the user, or none if the user has no administrator.
"""
return Administrator.query.filter_by(user_id=self.id).first()
def __repr__(self):
return '<User id:{} email:{} username:{}>'.format(
self.id, self.email, self.username)
def __eq__(self, other):
return self.id == other.id and \
self.email == other.email and \
self.username == other.username and \
self.password_hash == other.password_hash
class Administrator(UserMixin, DB.Model):
"""
Administrator model
An administrator is a person who manages events. Depending on his roles, an administrator can
create challenge, edit event info, edit event theme, etc. Administrators use a single account to
manage all events.
"""
__tablename__ = 'Administrators'
id = DB.Column(DB.Integer, primary_key=True)
"""The unique ID of the admin. Should be generated by the database. Used as primary key."""
is_platform_admin = DB.Column(DB.Boolean)
"""
Defines whether or not the administrator is a platform admin. Platform admins have complete
to all of the platform's configuration as well as all events.
"""
user_id = DB.Column(DB.Integer,
ForeignKey('Users.id'),
nullable=True,
unique=True)
"""The ID of the event the administrator is associated with. Used as a foreign key."""
event_administrators = relationship('EventAdministrator')
events = association_proxy('event_administrators', 'event')
user = relationship('User', lazy='joined')
def is_admin_of_event(self, event_id: int) -> bool:
"""
:param event_id:
:return: True if the admin is admin for the given event.
"""
return self.is_platform_admin or event_id in map(
lambda x: x.id, self.events)
def get_roles_for_event(self, event_id: int) -> [Role]:
"""
:param event_id:
:return: The list of all the roles the administrator has for the given event.
"""
event_administrators = filter(lambda x: x.event_id == event_id,
self.event_administrators)
roles = map(lambda x: x.roles, event_administrators)
return set().union(*roles)
def __repr__(self):
return '<Administrator id:{} is_platform_admin:{} user_id:{}>' \
.format(self.id, self.is_platform_admin, self.user_id)
class Role(DB.Model):
"""
Role model
A role defines the actions that an administrator is or is not allowed to perform for a given
event. For example, an administrator needs the role "challenge designer" to be allowed to create
challenges.
"""
__tablename__ = 'Roles'
id = DB.Column(DB.Integer, primary_key=True)
"""The unique ID of the role. Should be generated by the database. Used as primary key."""
label = DB.Column(DB.String(64), unique=True)
"""The label of the role"""
def __repr__(self):
return '<Role id:{} label:{}'.format(self.id, self.label)
class RoleAssociation(DB.Model):
"""
RoleAssociation model. This is an association table for the three-way relationship between
events, roles and EventAdministrators.
"""
__tablename__ = 'RoleAssociations'
event_id = DB.Column(DB.Integer,
ForeignKey('Events.id'),
nullable=True,
primary_key=True)
"""The ID of the Event that this row associates. Used as foreign key."""
event_administrator_id = DB.Column(DB.Integer,
ForeignKey('EventAdministrators.id'),
nullable=True,
primary_key=True)
"""The ID of the Administrator that this row associates. Used as foreign key."""
role_id = DB.Column(DB.Integer,
ForeignKey('Roles.id'),
nullable=True,
primary_key=True)
"""The ID of the Role that this row associates. Used as foreign key."""
event = relationship('Event', backref='role_associations')
event_administrator = relationship('EventAdministrator',
back_populates='roles')
administrator = association_proxy('event_administrator', 'administrator')
role = relationship('Role', backref='role_associations')
__table_args__ = (UniqueConstraint(
'event_id',
'event_administrator_id',
'role_id',
name='role_association_event_administrator_role_uc'), )
def __repr__(self):
return '<EventAdministrator event_id:{} administrator_id:{} role_id:{}>'\
.format(self.id, self.administrator_id, self.role_id)
class Flag(DB.Model):
"""
Flag model
A flag is the answer to an event's challenge. Participants must input it into the platform in
order to be awarded points.
"""
__tablename__ = 'Flags'
id = DB.Column(DB.Integer, primary_key=True)
"""The unique ID of the flag. Should be generated by the database. Used as primary key."""
challenge_id = DB.Column(DB.Integer,
ForeignKey('Challenges.id'),
nullable=True)
"""The ID of the challenge the flag belongs to. Used as foreign key."""
is_regex = DB.Column(DB.Boolean)
"""Whether or not the flag should be evaluated as a regex or as a plain string."""
value = DB.Column(DB.String)
"""The actual value of the flag"""
def __repr__(self):
return '<Flag id:{} challenge_id:{} is_regex:{} value:{}>' \
.format(self.id, self.challenge_id, self.is_regex, self.value)
class Team(DB.Model):
"""
Team model
A team is a grouping of participants who compete together. A team must always have at least one
member.
"""
__tablename__ = 'Teams'
__table_args__ = (UniqueConstraint('event_id',
'name',
name='team_event_name_uc'), )
id = DB.Column(DB.Integer, primary_key=True)
"""The unique ID of the team. Should be generated by the database. Used as primary key."""
event_id = DB.Column(DB.Integer, ForeignKey('Events.id'), nullable=True)
"""The ID of the event the team is registered to. Used as foreign key."""
name = DB.Column(DB.String(64), index=True)
"""The name of the team. Two teams competing in the same event cannot have the same name."""
members = relationship('TeamMember', lazy="joined", back_populates="team")
submissions = relationship('Submission',
lazy="joined",
back_populates="team")
requests = relationship('TeamRequest', lazy='joined')
event = relationship(Event, lazy='noload')
def __repr__(self):
return '<Team id:{} event_id:{} name:{}>'.format(
self.id, self.event_id, self.name)
def __eq__(self, other):
return self.id == other.id and \
self.event_id == other.event_id and \
self.name == other.name
def __hash__(self):
return hash(self.__dict__.values())
class Challenge(DB.Model):
"""
Challenge model
A challenge an event participant has to solve. A challenge is always associated with a
category and is composed of a (relatively) short name, an arbitrarily long description (which
might include nothing at all, or a very complex, multi-chapter backstory, and a number of points
to be awarded upon challenge completion.
A challenge may also be hidden from event participants if it is for example in a draft stage or
simply not yet released.
"""
__tablename__ = 'Challenges'
id = DB.Column(DB.Integer, primary_key=True)
"""The unique ID of the challenge. Should be generated by the database. Used as primary key."""
category_id = DB.Column(DB.Integer,
ForeignKey('Categories.id'),
nullable=True)
"""The ID of the category the challenge belongs to. Used as foreign key."""
name = DB.Column(DB.String(255), index=True)
"""The name of the challenge. Max 255 characters."""
description = DB.Column(DB.Text())
"""The description of the event. Can be arbitrarily long."""
points = DB.Column(DB.Integer)
"""The number of points the challenge is worth."""
hidden = DB.Column(DB.Boolean)
"""Whether or not the challenge should be visible by the event participants."""
flags = relationship('Flag', lazy='joined')
category = relationship('Category', lazy='joined')
category = relationship('Category', lazy='select')
submissions = relationship('Submission', lazy='noload')
solves = relationship(
'Submission',
primaryjoin="and_(Challenge.id==Submission.challenge_id,"
" Submission.is_correct == True)")
is_solved: bool
def __repr__(self):
return '<Challenge id:{} category_id:{} name:{} description:{} points:{}>'\
.format(self.id, self.category_id, self.name, self.description, self.points)
def __eq__(self, other):
return self.id == other.id and \
self.category_id == other.category_id and \
self.name == other.name and \
self.description == other.description and \
self.points == other.points and \
self.hidden == other.hidden
class Event(DB.Model):
"""
Event model
The core of the application. An event's only defining characteristic is its name, which must be
unique (i.e. 'CS Games 2019', 'United CTF 2019', etc). The rest is all handled by relations with
the application's other models.
"""
__tablename__ = 'Events'
id = DB.Column(DB.Integer, primary_key=True)
"""The unique ID of the event. Should be generated by the database. Used as primary key."""
name = DB.Column(DB.String(64), index=True, unique=True)
"""The name of the event. Max 64 characters."""
front_page = DB.Column(DB.Text())
"""The front page content of the event. Markdown text that will be parsed by frontend."""
teams = DB.Column(DB.Boolean)
"""Whether participants have to register as teams or individually."""
url = DB.Column(DB.String(255), default="")
"""The URL of the event."""
flag_format = DB.Column(DB.String(64), default="")
"""The flag format used by most challenges."""
is_open = DB.Column(DB.Boolean, default=False)
"""Whether flag submission is open or not."""
is_visible = DB.Column(DB.Boolean, default=False)
"""Whether the event is currently visible or not."""
event_administrators = relationship('EventAdministrator',
back_populates='event')
administrators = association_proxy('event_administrators', 'administrator')
def __repr__(self):
return '<Event id:{} name:{} teams: {}>'.format(
self.id, self.name, self.teams)
def __eq__(self, other):
return self.id == other.id and \
self.name == other.name and \
self.front_page == other.front_page and \
self.teams == other.teams and \
self.url == other.url and \
self.flag_format == other.flag_format and \
self.is_open == other.is_open and \
self.is_visible == other.is_visible
class Submission(DB.Model):
"""
Submission model
A submission is an attempt from an event participant to solve a challenge.
"""
__tablename__ = 'Submissions'
id = DB.Column(DB.Integer, primary_key=True)
"""The unique ID of the submission. Should be generated by the database. Used as primary key."""
team_id = DB.Column(DB.Integer, ForeignKey('Teams.id'), nullable=True)
"""The ID of the team who made the submission. Used as foreign key."""
challenge_id = DB.Column(DB.Integer, ForeignKey('Challenges.id'), nullable=True)
"""The ID of the challenge that this submission attempted to solve. Used as foreign key."""
input = DB.Column(DB.String(64))
"""The solution that was submitted."""
is_correct = DB.Column(DB.Boolean)
"""Whether or not the submission is correct."""
time = DB.Column(DB.DateTime, server_default=func.now())
"""The date and time of the submission."""
team = relationship(Team, back_populates='submissions')
"""The team that did the submission"""
challenge = relationship(Challenge, lazy='noload')
"""The challenge that the submission belongs to"""
def __repr__(self):
return '<Submission id:{} team_id:{} challenge_id:{} input:{} is_correct:{}>'\
.format(self.id, self.team_id, self.challenge_id, self.input, self.is_correct)
def __eq__(self, other):
return self.id == other.id and \
self.team_id == other.team_id and \
self.challenge_id == other.challenge_id and \
self.input == other.input and \
self.is_correct == other.is_correct and \
self.time == other.time