def validate_team(db,
team_id,
round_id,
now,
with_member=None,
without_member=None):
""" Raise an exception if the team is invalid for the round.
If with_member is a user, check with the user added the team.
"""
team_members = db.tables.team_members
tm_query = db.query(team_members) \
.where(team_members.team_id == team_id)
n_members = db.count(tm_query.fields(team_members.user_id))
n_qualified = db.count(
tm_query.where(team_members.is_qualified).fields(team_members.user_id))
if with_member is not None:
n_members += 1
if with_member['is_qualified']:
n_qualified += 1
if without_member is not None:
n_members -= 1
if with_member['is_qualified']:
n_qualified -= 1
round_ = load_round(db, round_id, now=now)
if n_members < round_['min_team_size']:
raise ModelError('team too small')
if n_members > round_['max_team_size']:
raise ModelError('team too large')
if n_qualified < n_members * round_['min_team_ratio']:
raise ModelError('not enough qualified members')
def get_task_instance_hint(db, attempt_id, query, now):
# Load the attempt and check that it is still open.
attempt = load_attempt(db, attempt_id, now)
if attempt['is_closed']:
raise ModelError('attempt is closed')
# Load the participation and verify that the round is still open.
participation = load_participation(db, attempt['participation_id'])
round_ = load_round(db, participation['round_id'], now)
if round_['status'] != 'open':
raise ModelError('round not open')
# Obtain task backend URL and Authorization header.
round_task = load_round_task(db, attempt['round_task_id'])
task = load_task(db, round_task['task_id'])
backend_url = task['backend_url']
auth = task['backend_auth']
# Load the task instance.
task_instance = load_task_instance(db, attempt_id, for_update=True)
if task_instance is None:
raise ModelError('no task instance')
full_data = task_instance['full_data']
team_data = task_instance['team_data']
# Get the task backend to validate the hint request and apply the hint
# from full_data onto team_data.
print('grantHint query {}'.format(query))
result = task_grant_hint(backend_url, full_data, team_data, query, auth)
# result: {success, task, full_task}
print('grantHint result {}'.format(result))
team_data = result.get('task', team_data)
full_data = result.get('full_task', full_data)
# If successful, update the task instance.
# 'full_data' is also updated in case the task needs to store extra private
# information there.
if result['success']:
attrs = {}
if team_data is not task_instance['team_data']:
attrs['team_data'] = db.dump_json(team_data)
if full_data is not task_instance['full_data']:
attrs['full_data'] = db.dump_json(full_data)
if len(attrs) != 0:
attrs['updated_at'] = now
task_instances = db.tables.task_instances
db.update_row(task_instances, {'attempt_id': attempt_id}, attrs)
return result['success']
def leave_team(db, user_id, team_id, now):
""" Remove a user from their team.
"""
user = load_user(db, user_id, for_update=True)
team_id = user['team_id']
# The user must be member of a team.
if team_id is None:
raise ModelError('no team')
team = load_team(db, team_id)
# Load the participation.
participation_id = get_team_latest_participation_id(db, team_id)
participation = load_participation(db, participation_id)
round_id = participation['round_id']
round_ = load_round(db, round_id)
# If the team already has an attempt (is_locked=True), verify
# that the team remains valid if the user is removed.
if team['is_locked']:
if round_['allow_team_changes']:
validate_team(db, team_id, round_id, without_member=user, now=now)
else:
raise ModelError('team is locked')
# Clear the user's team_id.
set_user_team_id(db, user_id, None)
# Delete the team_members row.
team_members = db.tables.team_members
tm_query = db.query(team_members) \
.where(team_members.team_id == team_id) \
.where(team_members.user_id == user_id)
(is_creator, ) = db.first(tm_query.fields(team_members.is_creator))
db.delete(tm_query)
# If the user was the team creator, select the earliest member
# as the new creator.
if is_creator:
query = db.query(team_members) \
.where(team_members.team_id == team_id)
row = db.first(query.fields(team_members.user_id).order_by(
team_members.joined_at),
for_update=True)
if row is None:
# Team has become empty, delete it.
teams = db.tables.teams
team_query = db.query(teams) \
.where(teams.id == team_id)
db.delete(team_query)
else:
# Promote the selected user as the creator.
new_creator_id = row[0]
db.update(query.where(team_members.user_id == new_creator_id),
{team_members.is_creator: True})
def load_row(self, table, value, columns, for_update=False):
query = self.row_scoped_query(table, value)
query = query.fields(*[getattr(table, col) for col in columns])
row = self.first(query, for_update=for_update)
if row is None:
raise ModelError('no such row')
return {key: row[i] for i, key in enumerate(columns)}
def store_revision(db, user_id, attempt_id, workspace_id, parent_id, title,
state, now):
if workspace_id is None:
# Use the attempt's default workspace.
workspace_id = get_attempt_default_workspace_id(db, attempt_id)
else:
# Verify that the workspace is attached to the attempt.
# XXX actually, probably safe to verify that the workspace is
# attached to an attempt belonging to the same participation.
workspace = load_workspace(db, workspace_id)
if workspace['attempt_id'] != attempt_id:
raise ModelError('invalid workspace for attempt')
# The parent revision, if set, must belong to the same workspace.
if parent_id is not None:
other_workspace_id = get_revision_workspace_id(db, parent_id)
if other_workspace_id != workspace_id:
parent_id = None
revisions = db.tables.workspace_revisions
revision_id = db.insert_row(
revisions, {
'workspace_id': workspace_id,
'creator_id': user_id,
'parent_id': parent_id,
'title': title,
'created_at': now,
'is_active': False,
'is_precious': True,
'state': db.dump_json(state)
})
return revision_id
def cancel_attempt(db, attempt_id):
attempt = load_attempt(db, attempt_id)
if attempt['started_at'] is not None:
raise ModelError('cannot cancel started attempt')
attempts = db.tables.attempts
query = db.query(attempts) \
.where(attempts.id == attempt_id)
db.delete(query)
def join_team(db, user_id, team_id, now):
""" Add a user to a team.
Registration for the team's round must be open.
Return a boolean indicating if the team member was added.
"""
# Verify that the user does not already belong to a team.
user = load_user(db, user_id, for_update=True)
if user['team_id'] is not None:
raise ModelError('already in a team')
# Verify that the team exists.
team = load_team(db, team_id)
# Verify that the team is open.
if not team['is_open']:
# Team is closed (by its creator).
raise ModelError('team is closed')
# Load the participation.
participation_id = get_team_latest_participation_id(db, team_id)
participation = load_participation(db, participation_id)
round_id = participation['round_id']
# Look up the badges that grant access to the team's round, to
# figure out whether the user is qualified for that round.
user_badges = user['badges']
badges = db.tables.badges
if len(user_badges) == 0:
is_qualified = False
else:
query = db.query(badges) \
.where(badges.round_id == round_id) \
.where(badges.symbol.in_(user_badges)) \
.where(badges.is_active) \
.fields(badges.id)
is_qualified = db.scalar(query) is not None
# If the team has already accessed a task instance (is_locked=True),
# verify that the team remains valid if the user is added.
if team['is_locked']:
round_ = load_round(round_id)
if round_['allow_team_changes']:
user['is_qualified'] = is_qualified
validate_team(db, team_id, round_id, with_member=user, now=now)
else:
raise ModelError('team is locked')
# Create the team_members row.
user_id = user['id']
add_team_member(db, team_id, user_id, now=now, is_qualified=is_qualified)
# Update the user's team_id.
set_user_team_id(db, user_id, team_id)
def get_team_creator(db, team_id):
team_members = db.tables.team_members
tm_query = db.query(team_members) \
.where(team_members.team_id == team_id) \
.where(team_members.is_creator)
row = db.first(tm_query.fields(team_members.user_id))
if row is None:
raise ModelError('team has no creator')
return row[0]
def execute(self, query):
if isinstance(query, tuple):
(stmt, values) = query
elif isinstance(query, Query):
(stmt, values) = mysql_compile(query)
elif isinstance(query, str):
stmt = query
values = ()
else:
raise ModelError("invalid query type: {}".format(query))
try:
if self.log:
print("[SQL] {};".format(stmt % tuple(values)))
cursor = self.db.cursor()
cursor.execute(stmt, values)
return cursor
except mysql.IntegrityError as ex:
raise ModelError('integrity error', ex)
except mysql.OperationalError as ex:
raise ModelError('connection lost', ex)
except (mysql.DataError, mysql.ProgrammingError, mysql.InternalError,
mysql.NotSupportedError) as ex:
raise ModelError('programming error', format(stmt))
def get_user_principals(db, user_id):
user_id = int(user_id)
users = db.tables.users
query = db.query(users) \
.where(users.id == user_id) \
.fields(users.team_id, users.is_admin)
row = db.first(query)
if row is None:
raise ModelError('invalid user')
principals = ['u:{}'.format(user_id)]
team_id = row[0]
if row[1]:
principals.append('g:admin')
if team_id is None:
return principals
team_members = db.tables.team_members
query = db.query(team_members) \
.where(team_members.user_id == user_id) \
.where(team_members.team_id == team_id) \
.fields(team_members.is_qualified, team_members.is_creator)
row = db.first(query)
if row is None:
raise ModelError('missing team_member row')
principals.append('t:{}'.format(team_id))
if db.load_bool(row[0]):
principals.append('ts:{}'.format(team_id))
if db.load_bool(row[1]):
principals.append('tc:{}'.format(team_id))
if False:
# Add credentials for user participations.
participations = db.tables.participations
query = db.query(participations) \
.where(participations.team_id == team_id) \
.fields(participations.id)
for row in db.all(query):
principals.append('p:{}'.format(row[0]))
return principals
def create_user_team(db, user_id, now):
# Check that the user does not already belong to a team.
user = load_user(db, user_id)
if user['team_id'] is not None:
# User is already in a team.
raise ModelError('already in a team')
# Create an empty team.
team_id = create_empty_team(db, now)
# Create the team_members row.
add_team_member(db,
team_id,
user_id,
now=now,
is_qualified=True,
is_creator=True)
return team_id
def reset_to_training_attempt(db, participation_id, round_task_id, now):
attempt_id = get_current_attempt_id(db, participation_id, round_task_id)
if attempt_id is not None:
attempt = load_attempt(db, attempt_id, now=now, for_update=True)
if not attempt['is_completed']:
# Handle case where several users click the reset button,
# avoid giving a confusing error message when the outcome
# is correct.
if attempt['is_training']:
return
raise ModelError('timed attempt not completed')
# Clear 'is_current' flag on current attempt.
set_attempt_current(db, attempt_id, is_current=False)
# Select the new attempt and make it current.
# XXX for_update=True
new_attempt_id = get_latest_training_attempt_id(db, participation_id)
if new_attempt_id is not None:
set_attempt_current(db, new_attempt_id, is_current=True)
def grade_answer(db, attempt_id, submitter_id, revision_id, data, now):
attempt = load_attempt(db, attempt_id, now)
is_training = attempt['is_training']
participation_id = attempt['participation_id']
participation = load_participation(db, participation_id, for_update=True)
round_task = load_round_task(db, attempt['round_task_id'])
task = load_task(db, round_task['task_id']) # backend_url
# Fail if the attempt is closed.
if attempt['is_closed']:
raise ModelError('attempt is closed')
# Get the greatest ordinal and nth most recent submitted_at.
(prev_ordinal, nth_submitted_at) = \
get_attempt_latest_answer_infos(db, attempt_id, nth=2)
# Fail if timed(not training) and there are more answers than
# allowed.
round_ = load_round(db, participation['round_id'], now)
if round_['status'] != 'open':
raise ModelError('round not open')
max_answers = round_task['max_attempt_answers']
if (not is_training and max_answers is not None
and prev_ordinal >= max_answers):
raise ModelError('too many answers')
# Fail if answer was submitted too recently.
if nth_submitted_at is not None:
if now < nth_submitted_at + timedelta(minutes=1):
raise ModelError('too soon')
ordinal = prev_ordinal + 1
# Perform grading.
backend_url = task['backend_url']
auth = task['backend_auth']
task_instance = load_task_instance(db, attempt_id)
full_data = task_instance['full_data']
team_data = task_instance['team_data']
grading = task_grade_answer(backend_url, full_data, team_data, data, auth)
# grading: {feedback, score, is_solution, is_full_solution}
# Update the attempt to indicate if solved, fully solved.
update_attempt_with_grading(db, attempt_id, grading)
# Store the answer and grading.
answers = db.tables.answers
answer = {
'attempt_id': attempt_id,
'submitter_id': submitter_id,
'ordinal': ordinal,
'created_at': now,
'answer': db.dump_json(data),
'grading': db.dump_json(grading),
'score': grading['score'],
'is_solution': grading['is_solution'],
'is_full_solution': grading['is_full_solution'],
'revision_id': revision_id
}
answer['id'] = db.insert_row(answers, answer)
# Best score for the participation?
new_score = Decimal(answer['score'])
if not is_training and (participation['score'] is None
or new_score > participation['score']):
update_participation(db, participation_id, {'score': new_score})
return (answer, grading.get('feedback'))
def ensure_connected(self):
try:
self.db.ping(reconnect=True, attempts=5, delay=2)
self.connected = True
except mysql.InterfaceError:
raise ModelError('database is unavailable')
def create_attempt(db, participation_id, round_task_id, now):
# Load the participation and round_task, check consistency.
participation = load_participation(db, participation_id)
round_task = load_round_task(db, round_task_id)
if participation['round_id'] != round_task['round_id']:
raise ModelError('round mismatch')
# Check that round is open.
round_ = load_round(db, round_task['round_id'], now=now)
if round_['status'] != 'open':
raise ModelError('round not open')
attempt_id = get_current_attempt_id(db, participation_id, round_task_id)
is_training = False
if attempt_id is None:
# Create the initial attempt (which may or may not be a training
# attempt, depending on the task_round's options).
# The team is not locked at this time and may still be changed
# (depending on round options). This requires adding an access
# code when a new member joins the team, and deleting an access
# code when a member leaves the team.
is_training = round_task['have_training_attempt']
else:
attempt = load_attempt(db, attempt_id, now=now, for_update=True)
if attempt['is_training']:
# Current attempt is training. Team must pass training to
# create a timed attempt.
if attempt['is_unsolved']:
raise ModelError('must pass training')
else:
# Timed attempts allow starting the next attempt immediately
# only if completed.
# if not attempt['is_completed']:
most_recent_allowed = now - timedelta(minutes=5)
if have_attempt_after(db, participation_id, round_task_id, most_recent_allowed):
raise ModelError('attempt too soon')
# Optionally limit the number of timed attempts.
if round_task['max_timed_attempts'] is not None:
n_attempts = count_timed_attempts(db, participation_id)
if n_attempts >= round_task['max_timed_attempts']:
raise ModelError('too many attempts')
# Reset the is_current flag on the current attempt.
set_attempt_current(db, attempt_id, is_current=False)
# Find the greatest attempt ordinal for the (participation, round_task).
attempts = db.tables.attempts
query = db.query(attempts) \
.where(attempts.participation_id == participation_id) \
.where(attempts.round_task_id == round_task_id) \
.order_by(attempts.ordinal.desc()) \
.fields(attempts.ordinal)
row = db.first(query)
ordinal = 1 if row is None else (row[0] + 1)
attempt_id = db.insert_row(attempts, {
'participation_id': participation_id,
'round_task_id': round_task_id,
'ordinal': ordinal,
'created_at': now,
'started_at': None, # set when task is accessed
'closes_at': None,
'is_current': True,
'is_training': is_training,
'is_unsolved': True
})
generate_access_codes(db, participation['team_id'], attempt_id)
return attempt_id
def reset_task_instance_hints(db, attempt_id, now, force=False):
# XXX to rewrite
attempt = load_attempt(db, attempt_id, for_update=True)
if not force and not attempt['is_training']:
raise ModelError('forbidden')
raise ModelError('not implemented')
def assign_task_instance(db, attempt_id, now):
""" Assign a task to the attempt.
The team performing the attempt must be valid, otherwise
an exception is raised.
"""
attempt = load_attempt(db, attempt_id, now=now)
if attempt['started_at'] is not None:
return ModelError('already have a task')
participation_id = attempt['participation_id']
participation = load_participation(db, participation_id)
# The team must be valid to obtain a task.
team_id = participation['team_id']
round_id = participation['round_id']
validate_team(db, team_id, round_id, now=now)
# Verify that the round is open for training (and implicitly for
# timed attempts).
round_ = load_round(db, round_id, now=now)
if round_['status'] != 'open':
raise ModelError('round not open')
if now < round_['training_opens_at']:
raise ModelError('training is not open')
# Load the round_task
round_task_id = attempt['round_task_id']
round_task = load_round_task(db, round_task_id)
# TODO: check round_task['have_training_attempt'] if next ordinal is 1
# TODO: check round_task['max_timed_attempts'] if next ordinal is >1
task = load_task(db, round_task['task_id']) # backend_url
backend_url = task['backend_url']
auth = task['backend_auth']
task_params = round_task['generate_params']
seed = str(attempt_id) # TODO add a participation-specific key to the seed
team_data, full_data = task_generate(backend_url, task_params, seed, auth)
try:
# Lock the task_instances table to prevent concurrent inserts.
db.execute('LOCK TABLES task_instances WRITE, attempts READ').close()
attrs = {
'attempt_id': attempt_id,
'created_at': now,
'updated_at': now,
'full_data': db.dump_json(full_data),
'team_data': db.dump_json(team_data)
}
task_instances = db.tables.task_instances
db.insert_row(task_instances, attrs)
finally:
db.execute('UNLOCK TABLES').close()
# Update the attempt.
attempt_attrs = {'started_at': now}
attempt_duration = round_task['attempt_duration']
if attempt_duration is not None:
# Set the closing time on the attempt.
attempt_attrs['closes_at'] = now + timedelta(minutes=attempt_duration)
attempts = db.tables.attempts
db.update_row(attempts, attempt_id, attempt_attrs)
# If starting the first timed attempt, set started_at on the participation.
if attempt['is_training'] == 0 and attempt['ordinal'] == 1:
update_participation(db, participation_id, {'started_at': now})
# Create an initial workspace for the attempt.
create_attempt_workspace(db, attempt_id, now)
def load_user(db, user_id, for_update=False):
results = load_users(db, (user_id,), for_update)
if len(results) == 0:
raise ModelError('no such user')
return results[0]