class ContestAttendance(db.Model):
__tablename__ = 'contest_attendance'
contest_id = db.Column(db.Integer,
db.ForeignKey('contests.id'),
primary_key=True)
student_id = db.Column(db.Integer,
db.ForeignKey('students.id'),
primary_key=True)
team_id = db.Column(db.Integer,
db.ForeignKey('teams.id'),
nullable=True)
attended = db.Column(db.Boolean,
nullable=False)
contest = db.relationship('Contest',
backref='attendance')
student = db.relationship('Student',
backref='attendance')
team = db.relationship('Team',
backref='contest_attendances')
def __init__(self, contest_id, student_id, attended, team_id=None):
self.contest_id = contest_id
self.student_id = student_id
self.team_id = team_id
self.attended = attended
class Team(db.Model):
__tablename__ = 'teams'
__table_args__ = (
db.UniqueConstraint(
'division_id',
'school_id',
'name',
),
)
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(32), nullable=False)
school_id = db.Column(db.Integer,
db.ForeignKey('schools.id'),
nullable=False)
division_id = db.Column(db.Integer, db.ForeignKey('divisions.id'),
nullable=False)
school = db.relationship('School', back_populates='teams')
division = db.relationship('Division', back_populates='teams')
scores = db.relationship('Score', back_populates='team')
# students backref'd
def __init__(self, name, school_id, division_id):
self.name = name
self.school_id = school_id
self.division_id = division_id
class School(db.Model):
__tablename__ = 'schools'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(256), nullable=False)
url = db.Column(db.String(64), nullable=False)
school_grouping_id = db.Column(db.Integer,
db.ForeignKey('school_groupings.id'),
nullable=False)
# teams, schools, students, coaches
teams = db.relationship('Team', back_populates='school')
coaches = db.relationship('User', back_populates='school')
students = db.relationship('Student', back_populates='school')
# divisions backref'd
codes = db.relationship('RegistrationCode', back_populates='school')
school_grouping = db.relationship('SchoolGrouping',
back_populates='schools')
def __init__(self, name, url, groupId):
self.name = name
self.url = url
self.school_grouping_id = groupId
# TODO - sqlify this method
def getDivisionsList(self):
schools_divisions = []
for team in self.teams:
division = team.division
if division not in schools_divisions:
schools_divisions.append(division)
return schools_divisions
class RegistrationCode(db.Model):
__tablename__ = 'register_codes'
school_id = db.Column(
db.Integer,
db.ForeignKey('schools.id'),
nullable=False,
)
code = db.Column(db.String(16),
nullable=False,
unique=True,
primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=True)
issuer_id = db.Column(db.Integer,
db.ForeignKey('users.id'),
nullable=False)
school = db.relationship('School', back_populates='codes')
used_by = db.relationship('User',
foreign_keys=[user_id],
backref='code',
uselist=False)
issuer = db.relationship('User',
foreign_keys=[issuer_id],
backref='issued_codes')
def __init__(self, school_id, issuer_id, code=None):
# TODO - edge case check for redundant codes
if not code:
self.code = hex(0x100000 +
secrets.randbelow(0xffffff - 0x100000))[2:]
else:
self.code = code
self.school_id = school_id
self.issuer_id = issuer_id
class SchoolGrouping(db.Model):
__tablename__ = 'school_groupings'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64), nullable=False)
url = db.Column(db.String(64), nullable=False)
schools = db.relationship('School', back_populates='school_grouping')
def __init__(self, name, url):
self.name = name
self.url = url
class User(db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
first = db.Column(db.String(32), nullable=False)
last = db.Column(db.String(64), nullable=False)
email = db.Column(db.String(64), nullable=False)
phone_num = db.Column(db.String(32), nullable=True)
username = db.Column(db.String(32), nullable=False)
password = db.Column(db.Binary(60), nullable=False)
is_admin = db.Column(db.Boolean, nullable=False, default=False)
approval_status = db.Column(db.Integer, nullable=False, default=1)
school_id = db.Column(db.Integer, db.ForeignKey('schools.id'))
school = db.relationship('School', back_populates='coaches')
scores = db.relationship('Score', back_populates='coach')
# code and issued_codes backref'd
students = db.relationship('Student',
secondary='schools',
secondaryjoin='School.id==Student.school_id',
primaryjoin='School.id==User.school_id',
backref='coaches')
def __init__(self, first, last, email, phone_num, username, password,
is_admin):
self.first = first
self.last = last
self.email = email
self.phone_num = phone_num
self.is_admin = is_admin
self.username = username
self.setPassword(password)
def setPassword(self, newpass):
self.password = bcrypt.hashpw(newpass.encode("utf-8"),
bcrypt.gensalt(12))
def checkPassword(self, password):
return bcrypt.checkpw(password.encode("utf-8"), self.password)
def isAdmin(self):
return self.is_admin
def isApproved(self):
return self.isAdmin() or self.approval_status > 0
def isSchoolAdmin(self, school):
return self.isAdmin() or (self.isApproved()
and self.school_id == school.id)
class Category(db.Model):
__tablename__ = 'categories'
id = db.Column(db.Integer, primary_key=True)
parent_id = db.Column(db.Integer, db.ForeignKey('categories.id'))
name = db.Column(db.String(128))
description = db.Column(db.UnicodeText())
subcategories = db.relationship(
'Category',
backref=db.backref('supercategory', remote_side=[id]))
first_order_questions = db.relationship(
'Question',
secondary=question_category_table,
backref='categories')
class StudentDivisionAssociation(db.Model):
__tablename__ = 'student_division_assoc'
student_id = db.Column(db.Integer,
db.ForeignKey('students.id'),
nullable=False,
primary_key=True)
division_id = db.Column(db.Integer,
db.ForeignKey('divisions.id'),
nullable=False,
primary_key=True)
is_alternate = db.Column(db.Boolean, nullable=False, default=False)
team_id = db.Column(db.Integer, db.ForeignKey('teams.id'), nullable=True)
student = db.relationship('Student', backref='division_associations')
division = db.relationship('Division', backref='student_associations')
team = db.relationship('Team', backref='student_associations')
class Season(db.Model):
__tablename__ = 'seasons'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64), unique=True, nullable=False)
url = db.Column(db.String(32), unique=True, nullable=False)
start_date = db.Column(db.Date(), nullable=False)
end_date = db.Column(db.Date(), nullable=False)
divisions = db.relationship('Division', back_populates='season')
def __init__(self, name, url, start_date, end_date):
self.name = name
self.url = url
self.start_date = start_date
self.end_date = end_date
# TODO : write this
@classmethod
def current(cls):
current_time = datetime.datetime.utcnow()
return Season.query.filter(Season.start_date <= current_time,
Season.end_date >= current_time)
class Question(db.Model):
__tablename__ = 'questions'
__table_args__ = (
db.ForeignKeyConstraint(
['contest_id'],
['contests.id']),
db.UniqueConstraint('contest_id', 'question_num'),
)
id = db.Column(db.Integer,
primary_key=True)
contest_id = db.Column(db.Integer,
nullable=False)
question_num = db.Column(db.Integer,
autoincrement=False,
nullable=False)
question_string = db.Column(db.UnicodeText(),
nullable=True)
question_value = db.Column(db.Integer,
nullable=False,
default=1)
# categories relation back-reffed
scores = db.relationship('Score',
back_populates='question')
contest = db.relationship('Contest',
back_populates='questions')
def __init__(self, contest_id, question_num,
question_string=None,
question_value=1):
self.contest_id = contest_id
self.question_num = question_num
self.question_string = question_string
self.question_value = question_value
def getMaxScore(self):
return self.question_value
class PasswordReset(db.Model):
__tablename__ = 'password_resets'
__table_args__ = (db.UniqueConstraint('code', ), )
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
code = db.Column(db.String(16), nullable=False)
expiration_time = db.Column(db.DateTime(), nullable=False)
used = db.Column(db.Boolean, nullable=False, default=False)
def __init__(self, user_id, code=None, expiration_time=None):
self.user_id = user_id
if code:
self.code = code
else:
self.code = hex(0x100000 +
secrets.randbelow(0xffffff - 0x100000))[2:]
while (PasswordReset.query.filter_by(code=self.code).first()):
self.code = hex(0x100000 +
secrets.randbelow(0xffffff - 0x100000))[2:]
if expiration_time:
self.expiration_time = expiration_time
else:
self.expiration_time = datetime.datetime.utcnow() \
+ datetime.timedelta(minutes=30)
class Score(db.Model):
__tablename__ = 'scores'
id = db.Column(db.Integer, primary_key=True)
points_awarded = db.Column(db.Integer,
nullable=False)
student_id = db.Column(db.Integer,
db.ForeignKey('students.id'),
nullable=False)
question_id = db.Column(db.Integer,
db.ForeignKey('questions.id'),
nullable=False)
coach_id = db.Column(db.Integer,
db.ForeignKey('users.id'),
nullable=False)
team_id = db.Column(db.Integer,
db.ForeignKey('teams.id'),
nullable=True)
timestamp = db.Column(db.DateTime(),
nullable=False)
# relationships
student = db.relationship('Student', back_populates='scores')
coach = db.relationship('User', back_populates='scores')
team = db.relationship('Team', back_populates='scores')
question = db.relationship('Question', back_populates='scores')
contest = db.relationship(
'Contest',
secondary='questions',
secondaryjoin='Contest.id==Question.contest_id',
primaryjoin='Question.id==Score.question_id',
backref='scores',
uselist=False
)
def getValue(self):
return self.points_awarded
def setValue(self, value):
self.points_awarded = value
def getQuestionNum(self):
return self.question_num
def getQuestion(self):
return self.question
def getMaxPoints(self):
return self.getQuestion().getMaxScore()
class Contest(db.Model):
__tablename__ = 'contests'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(32), nullable=False)
start_time = db.Column(db.DateTime(), nullable=False)
question_count = db.Column(db.Integer,
nullable=False,
default=6)
team_size = db.Column(db.Integer, nullable=False,
default=5)
# determines whether competition is active.
# Negative means disabled, other status codes to be added later
status = db.Column(db.Integer, nullable=False,
default=1)
division_id = db.Column(db.Integer,
db.ForeignKey('divisions.id'),
nullable=False)
division = db.relationship('Division', back_populates='contests')
questions = db.relationship('Question', back_populates='contest')
# attendance backreffed
attending_students = association_proxy(
'attendance',
'student',
)
def __init__(self, name, start_time, question_count=6, team_size=5):
self.name = name
self.start_time = start_time
self.question_count = question_count
self.team_size = team_size
# scores backref'd
def getQuestionCount(self):
return self.question_count
def getTeamSize(self):
return self.team_size
def getScores(self):
return self.scores
# returns a list of students who have scores for a contest
def getAttendees(self):
return self.attending_students
def isActive(self):
return self.status < 0
def getDate(self):
return self.start_time.date()
def getQuestion(self, number):
import iml.models.question as questionModule
Question = questionModule.Question
return Question.query. \
filter_by(contest_id=self.id,
question_num=number). \
first()
def getHighestPossibleScore(self):
total = 0
for i in range(1, self.question_count+1):
total += self.getQuestion(i).getMaxScore()
return total
class Division(db.Model):
__tablename__ = 'divisions'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64), nullable=False)
url = db.Column(db.String(64), nullable=False)
alternate_limit = db.Column(db.Integer, default=4, nullable=False)
# hard max on teams, outside of alternates
# for example, some competitions may have less than
# 5 people per team, but this determines how many
# could join the team max (if contest max tends to be under
# this, this gives teams a stricter alternate that can only
# play one game, which is a decision IML admins may make
# at some point
team_size = db.Column(db.Integer, default=5, nullable=False)
season_id = db.Column(db.Integer,
db.ForeignKey('seasons.id'),
nullable=False)
successor_id = db.Column(db.Integer,
db.ForeignKey('divisions.id'),
nullable=True,
unique=True)
teams = db.relationship('Team', back_populates='division')
# students backref'd
contests = db.relationship('Contest', back_populates='division')
schools = db.relationship('School',
secondary=school_division_table,
backref='divisions')
season = db.relationship('Season', back_populates='divisions')
parents = db.relationship('Division',
backref=db.backref('successor',
remote_side=[id]))
def __init__(self,
name,
url,
season_id,
alternate_limit=4,
successor_id=None):
self.name = name
self.url = url
self.season_id = season_id
self.alternate_limit = alternate_limit
self.successor_id = successor_id
# only returns students who have scores
def getParticipants(self):
import iml.models.student as studentModule
import iml.models.score as scoreModule
import iml.models.contest as contestModule
Student = studentModule.Student
Score = scoreModule.Score
Contest = contestModule.Contest
div_id = self.id
return Student.query.filter(
Student.scores.any(
Score.contest.has(Contest.division_id == div_id)))
from iml import db
school_division_table \
= db.Table('school_division',
db.Column('school_id',
db.Integer,
db.ForeignKey('schools.id')),
db.Column('division_id',
db.Integer,
db.ForeignKey('divisions.id'))
)
class Division(db.Model):
__tablename__ = 'divisions'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64), nullable=False)
url = db.Column(db.String(64), nullable=False)
alternate_limit = db.Column(db.Integer, default=4, nullable=False)
# hard max on teams, outside of alternates
# for example, some competitions may have less than
# 5 people per team, but this determines how many
# could join the team max (if contest max tends to be under
# this, this gives teams a stricter alternate that can only
# play one game, which is a decision IML admins may make
# at some point
team_size = db.Column(db.Integer, default=5, nullable=False)
season_id = db.Column(db.Integer,
from iml import db
question_category_table \
= db.Table('question_category',
db.Column('question_id',
db.Integer,
db.ForeignKey('questions.id')),
db.Column('category_id',
db.Integer,
db.ForeignKey('categories.id')))
class Category(db.Model):
__tablename__ = 'categories'
id = db.Column(db.Integer, primary_key=True)
parent_id = db.Column(db.Integer, db.ForeignKey('categories.id'))
name = db.Column(db.String(128))
description = db.Column(db.UnicodeText())
subcategories = db.relationship(
'Category',
backref=db.backref('supercategory', remote_side=[id]))
first_order_questions = db.relationship(
'Question',
secondary=question_category_table,
backref='categories')
class Student(db.Model):
__tablename__ = 'students'
id = db.Column(db.Integer, primary_key=True)
first = db.Column(db.String(32), nullable=False)
last = db.Column(db.String(64), nullable=False)
username = db.Column(db.String(64), nullable=False)
nickname = db.Column(db.String(32), nullable=True)
graduation_year = db.Column(db.Integer, nullable=False)
creation_timestamp = db.Column(db.DateTime(), nullable=False)
current_division_id = db.Column(db.Integer,
db.ForeignKey('divisions.id'),
nullable=True)
current_team_id = db.Column(db.Integer,
db.ForeignKey('teams.id'),
nullable=True)
school_id = db.Column(db.Integer,
db.ForeignKey('schools.id'),
nullable=False)
school = db.relationship('School', back_populates='students')
scores = db.relationship('Score', back_populates='student')
teams = association_proxy('division_associations', 'team')
divisions = association_proxy('division_associations', 'division')
# contests backreff'd
current_division = db.relationship('Division',
foreign_keys=[current_division_id],
backref='current_students')
current_team = db.relationship('Team',
foreign_keys=[current_team_id],
backref='current_students')
current_division_assoc = db.relationship(
'StudentDivisionAssociation',
primaryjoin="and_(Student.id==StudentDivisionAssociation.student_id,"
"Student.current_division_id==StudentDivisionAssociation.division_id)",
uselist=False)
is_alternate = association_proxy('current_division_assoc', 'is_alternate')
current_scores = db.relationship(
'Score',
viewonly=True,
secondary="join(Question, Contest, Contest.id==Question.contest_id)."
"join( Score, Question.id==Score.question_id )",
primaryjoin="and_(Student.current_division_id==Contest.division_id"
",Student.id==Score.student_id)",
secondaryjoin='Score.question_id==Question.id')
# if current_team is null, then they are an alternate
# TODO - replaec team with current team
# team = db.relationship('Team', back_populates='students')
def __init__(
self,
first,
last,
graduation_year,
school_id,
current_division_id=None,
current_team_id=None,
nickname=None,
):
self.first = first
self.last = last
self.graduation_year = graduation_year
self.nickname = nickname
self.current_division_id = current_division_id
self.current_team_id = current_team_id
username_base = '{}_{}'.format(first[:16], last[:16]).replace(' ', '_')
username_num = Student.query.filter(
Student.username.contains(username_base)).count() + 1
self.username = '******'.format(username_base, username_num).lower()
self.school_id = school_id
self.creation_timestamp = datetime.datetime.utcnow()
def isParticipant(self, contest, team=None) -> bool:
import iml.models.score as score
Score = score.Score
scoresQuery = Score.query.filter_by(contest_id=contest.id,
student_id=self.id)
if team:
scoresQuery = scoresQuery.filter_by(team_id=team.id)
return scoresQuery.count() == contest.getQuestionCount()
# participant is valid if they don't have scores for another team or are on the team
# ie they are on the team or they're not on a team
# AND them being a participant implies they participated for that team. Essetially serves as a
# check that they have not already had scores entered for another team
def isValidParticipant(self, contest, team) -> bool:
return (self.team == team) or (self.team is None and
(not (self.isParticipant(contest))
or self.isParticipant(contest, team)))
# returns score in a dictionary
def getScoresDict(self,
contest,
division=None,
team=None) -> Dict[int, int]:
import iml.models.contest as contestModule
import iml.models.score as score
# the name contest was used!
Score = score.Score
if contest is None:
return {}
else:
scoresQuery = Score.query.filter_by(contest_id=contest.id,
student_id=self.id)
if division:
scoresQuery = scoresQuery.filter_by(division_id=division.id)
if team:
scoresQuery = scoresQuery.filter_by(team_id=team.id)
scoresDict = {}
for scoreObj in scoresQuery:
scoresDict[scoreObj.getQuestionNum()] = scoreObj.getValue()
return scoresDict
def getAllScoresDict(self,
division=None,
team=None) -> List[Dict[int, int]]:
import iml.models.contest as contestModule
contestsQuery = contestModule.Contest.query.all()
if division:
contestsQuery = contestsQuery.filter_by(division_id=division.id)
if team:
contestsQuery = contestsQuery.filter_by(team_id=team.id)
scores = []
for contest in contestsQuery:
contestScores = self.getScoresDict(contest)
if contestScores == {}:
scores.append(contestScores)
return scores
def getTeam(self, contest):
import iml.models.contest as contestModule
import iml.models.score as score
Score = score.Score
score_sample = Score.query.filter_by(contest_id=contest.id,
student_id=self.id).first()
if score_sample:
return score_sample.team
return None
# returns actual final score
def getFinalContestScore(self, contest):
return sum(self.getScoresDict(contest).values())
def getFinalScore(self):
return sum(self.getAllScoresDict().values())
def getUserId(self):
return self.id
def get_user_id(self):
return self.getUserId()