def challenge_feedback(self):
"""
Display feedback list.
"""
challenge_id = int(self.request.matchdict["id"])
challenge = DBSession.query(Challenge).filter(Challenge.id == challenge_id).one()
items = (
DBSession.query(Feedback)
.filter(Feedback.challenge_id == challenge_id)
.filter(or_(Feedback.rating != None, Feedback.note != None))
.order_by(Feedback.team_id)
)
page = self.page(items)
return {"items": page.items, "challenge": challenge, "page": page}
def submissions_edit(self):
# Prepare parameters
form = SubmissionForm(self.request.POST, csrf_context=self.request)
submissions = get_submissions()
page = self.page(submissions)
redirect = self.redirect("admin_submissions", page.page)
# Cancel button pressed?
if not form.submit.data:
return redirect
is_new = not bool(form.challenge.data and form.team.data)
# Form errors?
if not form.validate():
return self._list_retparams(page, form, is_new=is_new)
# New item or existing one?
try:
submission = (
DBSession.query(Submission)
.filter(Submission.challenge_id == form.challenge.data.id)
.filter(Submission.team_id == form.team.data.id)
.one()
)
self.request.session.flash("Submission edited!")
except NoResultFound:
submission = Submission()
DBSession.add(submission)
self.request.session.flash("Submission added!")
# Transfer edits into databse
form.populate_obj(submission)
return redirect
def get_number_solved_subquery():
"""
Get a subquery that returns how many teams have solved a challenge.
Example usage:
.. code-block:: python
number_of_solved_subquery = get_number_solved_subquery()
challenge_query = (DBSession.query(Challenge,
number_of_solved_subquery)
Here we query for a list of all challenges and additionally fetch the
number of times it has been solved. This subquery will use the outer
challenge to correlate on, so make sure to provide one or this query
makes no sense.
"""
from fluxscoreboard.models import dynamic_challenges
query = (DBSession.query(func.count('*')).
filter(Challenge.id == Submission.challenge_id).
correlate(Challenge).as_scalar())
for name, module in dynamic_challenges.registry.items():
dyn_cnt = module.solved_count_query().filter(Challenge.module == name)
query = query + dyn_cnt.as_scalar()
return query.label("solved_count")
def password_reminder(email, request):
"""
For an email address, find the corresponding team and send a password
reset token. If no team is found send an email that no user was found for
this address.
"""
mailer = get_mailer(request)
team = DBSession.query(Team).filter(Team.email == email).first()
if team:
# send mail with reset token
team.reset_token = random_token()
html = render('mail_password_reset_valid.mako', {'team': team},
request=request)
recipients = [team.email]
else:
# send mail with information that no team was found for that address.
html = render('mail_password_reset_invalid.mako', {'email': email},
request=request)
recipients = [email]
competition = request.registry.settings['competition_title']
message = Message(subject="Password Reset for %s" % competition,
recipients=recipients,
html=html,
)
mailer.send(message)
return team
def check_password_reset_token(token):
"""
Check if an entered password reset token actually exists in the database.
"""
team = (DBSession.query(Team).
filter(Team.reset_token == token).first())
return team
def rank(self):
inner_team = aliased(Team)
return (DBSession.query(func.count('*') + 1).
select_from(inner_team).
filter(inner_team.score > Team.score).
correlate(Team).
label('rank'))
def get_team_solved_subquery(team_id):
"""
Get a query that searches for a submission from a team for a given
challenge. The challenge is supposed to come from an outer query.
Example usage:
.. code-block:: python
team_solved_subquery = get_team_solved_subquery(team_id)
challenge_query = (DBSession.query(Challenge,
team_solved_subquery))
In this example we query for a list of all challenges and additionally
fetch whether the currenttly logged in team has solved it.
"""
# This subquery basically searches for whether the current team has
# solved the corresponding challenge. The correlate statement is
# a SQLAlchemy statement that tells it to use the **outer** challenge
# column.
if team_id:
team_solved_subquery = (DBSession.query(Submission).
filter(Submission.team_id == team_id).
filter(Challenge.id ==
Submission.challenge_id).
correlate(Challenge).
exists().
label("has_solved"))
else:
team_solved_subquery = bindparam("has_solved", 0)
return team_solved_subquery
def massmail(self):
"""
Send a massmail to all users in the system. It also stores the sent
mail and its recipients in the database to keep a permanent record of
sent messages.
"""
form = MassMailForm(self.request.POST, csrf_context=self.request)
if not form.from_.data:
settings = self.request.registry.settings
form.from_.data = settings["mail.default_sender"]
mail_query = DBSession.query(MassMail)
page = self.page(mail_query)
retparams = {"form": form, "items": page.items, "page": page}
if self.request.method == "POST":
if not form.validate():
return retparams
teams = get_active_teams()
# Create a record to keep track of all sent mails
mail_record = MassMail()
form.populate_obj(mail_record)
recipients = [team.email for team in teams]
mail_record.recipients = recipients
mailer = get_mailer(self.request)
message = Message(
subject=mail_record.subject,
bcc=mail_record.recipients,
body=mail_record.message,
sender=mail_record.from_,
)
DBSession.add(mail_record)
mailer.send(message)
self.request.session.flash("Mass mail sent to all %d active users" % len(recipients))
return HTTPFound(location=self.request.route_url("admin_massmail"))
return retparams
def get_team_by_id(team_id):
team = (DBSession.query(Team).
filter(Team.id == team_id).
filter(Team.active == True).
options(subqueryload('submissions').
joinedload('challenge')).
one())
return team
def team_ips(self):
"""A list of IPs per team."""
form = ButtonForm(self.request.POST, csrf_context=self.request)
redir = self.redirect("admin_teams", int(self.request.GET.get("page", 1)))
if not form.validate():
return redir
team = DBSession.query(Team).filter(Team.id == form.id.data).options(subqueryload("team_ips")).one()
return {"team": team}
def get_submissions():
"""
Creates a query to **eagerly** load all submissions. That is, all teams
and challenges that are attached to the submissions are fetched with them.
"""
return (DBSession.query(Submission).
options(joinedload('challenge')).
options(joinedload('team')).
order_by(desc(Submission.timestamp)))
def team_resend_activation(self):
"""Resend the activation mail for a team."""
form = ButtonForm(self.request.POST, csrf_context=self.request)
redir = self.redirect("admin_teams", int(self.request.GET.get("page", 1)))
if not form.validate():
return redir
team = DBSession.query(Team).filter(Team.id == form.id.data).one()
send_activation_mail(team, self.request)
self.request.session.flash('Activation mail for team "%s" resent.' % team.name)
return redir
def get_published_news():
announcements = (
DBSession.query(News)
.outerjoin(News.challenge)
.filter(News.published == True)
.filter(or_(Challenge.published, News.challenge == None))
.filter(News.timestamp <= datetime.utcnow())
.options(contains_eager(News.challenge), Load(Challenge).load_only("title"), lazyload("challenge.category"))
.order_by(desc(News.timestamp))
)
return announcements
def email_unique_validator(form, field):
"""
A validator to make sure the entered email is unique and does not exist
yet.
"""
email = field.data
email_exists = DBSession.query(Team).filter(Team.email == email).all()
if len(email_exists) > 0:
raise ValueError("This email is already registered.")
else:
return True
def name_unique_validator(form, field):
"""
A validator to make sure the entered team name is unique and does not exist
yet.
"""
name = field.data
name_exists = DBSession.query(Team).filter(Team.name == name).all()
if len(name_exists) > 0:
raise ValueError("This name is already registered.")
else:
return True
def stats(self):
_stats = {}
count_query = (DBSession.query(func.count(Challenge.id)).
filter(Challenge.category_id == Category.id).
filter(~Challenge.dynamic).
filter(Challenge.published).
correlate(Category))
submission = (DBSession.query(Submission).
filter(Submission.team_id == self.id).
filter(Submission.challenge_id == Challenge.id).
correlate(Challenge))
team_count_query = count_query.filter(submission.exists())
query = DBSession.query(Category.name, count_query.as_scalar(),
team_count_query.as_scalar())
for name, total, team_count in query:
_stats[name] = (team_count, total)
overall_stats = [0, 0]
for team_stat, total in _stats.values():
overall_stats[0] += team_stat
overall_stats[1] += total
_stats["_overall"] = tuple(overall_stats)
return _stats
def test_login(self):
"""
If there is at least one team, log in as it to see the page.
"""
form = ButtonForm(self.request.POST, csrf_context=self.request)
team_query = DBSession.query(Team)
if form.id.data:
team_query = team_query.filter(Team.id == form.id.data)
team = team_query.first()
if not team:
self.request.session.flash("No team available, add one!")
return HTTPFound(location=self.request.route_url("admin_teams"))
return self._test_login(team)
def database(request, testapp):
dbsession = DBSession()
Base.metadata.create_all(bind=dbsession.connection())
if not dbsession.query(Settings).all():
dbsession.add(Settings())
def _drop():
conn = dbsession.connection()
Base.metadata.drop_all(bind=conn)
# TODO: Why do we have to use this literally?
# If fixed test against MySQL *and* Postgres!
conn.execute("COMMIT")
request.addfinalizer(_drop)
def team_cleanup(self):
"""Remove ALL inactive teams. Warning: **DANGEROUS**"""
form = TeamCleanupForm(self.request.POST, csrf_context=self.request)
redirect = self.redirect("admin_teams", int(self.request.GET.get("page", 1)))
if not form.validate():
return redirect
if not form.team_cleanup.data:
return redirect
inactive_teams = DBSession.query(Team).filter(not_(Team.active)).all()
delete_count = len(inactive_teams)
for team in inactive_teams:
DBSession.delete(team)
self.request.session.flash("Deleted %d teams" % delete_count)
return redirect
def search_ips(self):
form = IPSearchForm(self.request.POST, csrf_context=self.request)
retparams = {"form": form}
redirect = self.redirect("admin_ip_search")
query = DBSession.query(Team).join(TeamIP).options(subqueryload("team_ips"))
if self.request.method == "POST":
if not form.validate():
return redirect
if form.by_ip.data:
query = query.filter(TeamIP.ip == form.term.data)
elif form.by_name.data:
query = query.filter(Team.name == form.term.data)
retparams["results"] = query.all()
return retparams
def team_challenges(self):
from fluxscoreboard.models import dynamic_challenges
try:
team_id = int(self.request.matchdict['team_id'])
team = get_team_by_id(team_id)
except (ValueError, NoResultFound):
raise HTTPNotFound()
dynamic_queries = []
for name, module in dynamic_challenges.registry.items():
q = and_(Challenge.module == name, module.in_progress_query(team))
dynamic_queries.append(q)
dynamics = (DBSession.query(Challenge).
filter(Challenge.dynamic).
filter(or_(*dynamic_queries)))
return {'team': team, 'dynamic_challenges': dynamics}
def team_regenerate_token(self):
"""Manually regenerate the teams challenge token"""
current_page = int(self.request.GET.get("page", 1))
redirect = self.redirect("admin_teams", current_page)
button_form = ButtonForm(self.request.POST, csrf_context=self.request)
if not button_form.validate():
self.request.session.flash("Regenerate failed.")
return redirect
team = DBSession.query(Team).filter(Team.id == button_form.id.data).one()
log.info("Generating new token for team %s, old token: %s" % (team.name, team.challenge_token))
team.challenge_token = str(uuid.uuid4()).decode("ascii")
self.request.session.flash("New token created for team %s" % team.name)
return redirect
def dynamic_check_multiple_allowed(form, field):
"""
Checks if multiple fields are allowed and if they are not and there already
is a challenge with this dynamic type, then fail.
"""
if not form.dynamic.data or not field.data:
return True
from ..models import dynamic_challenges
module = dynamic_challenges.registry[field.data]
instance_exists = (DBSession.query(Challenge).
filter(Challenge.module == field.data))
if form.id.data:
instance_exists = instance_exists.filter(Challenge.id != form.id.data)
if not module.allow_multiple and instance_exists.first():
raise ValueError("Multiple instances of this module are not allowed.")
else:
return True
def challenges(self):
"""
A list of all challenges similar to the scoreboard view in a table.
It has a very complex query that gets all challennges together with
a boolean of whether the current team has solved it, and the number
of times this challenge was solved overall. This list of tuples
``(challenge, team_solved, number_solved_total)`` is then given to the
template and rendered.
"""
team_id = self.request.authenticated_userid
team_solved_subquery = get_team_solved_subquery(team_id)
number_of_solved_subquery = get_number_solved_subquery()
challenges = (DBSession.query(
Challenge, team_solved_subquery, number_of_solved_subquery).
options(joinedload("category")).
filter(Challenge.published).
order_by(Challenge.id))
return {'challenges': challenges}
def rank(self):
"""
Return the teams current rank. Can be used as a hybrid property:
.. code-block:: python
DBSession.query(Team).order_by(Team.rank)
# or
team = Team()
team.rank
In both cases the database will be queried so be careful how you use
it. For equal points the same rank is returned. In general we use a
`"1224" ranking <http://en.wikipedia.org/wiki/Ranking#Standard_competition_ranking_.28.221224.22_ranking.29>`_
here.
"""
rank = (DBSession.query(Team).filter(Team.score > self.score).
order_by(desc(Team.score)).count()) + 1
return rank
def install(settings, cfg_file, test_data=False):
"""
Installs the application. Only required to be called once per installation.
"""
dbsession = DBSession()
transaction.begin()
try:
install_alembic_table(cfg_file)
Base.metadata.create_all(bind=dbsession.connection())
create_country_list(dbsession)
if test_data:
install_test_data(dbsession, settings)
if not dbsession.query(Settings).all():
dbsession.add(Settings())
for dyn_mod in dynamic_challenges.registry.values():
dyn_mod.install(dbsession.connection())
except:
transaction.abort()
raise
else:
transaction.commit()
def confirm_registration(token):
"""
For a token, check the database for the corresponding team and activate it
if found.
Args:
``token``: The token that was sent to the user (a string)
Returns:
Either ``True`` or ``False`` depending on whether the confirmation was
successful.
"""
if token is None:
return False
try:
team = DBSession.query(Team).filter(Team.token == token).one()
except NoResultFound:
return False
team.active = True
team.token = None
return True
def submissions(self):
"""
List, add or edit a submission. This is different because it consists
of composite foreign keys and thus needs separate though similar logic.
But in the end it is basically the same functionality as with the other
list views.
"""
# Prepare parameters
submissions = get_submissions()
page = self.page(submissions)
redirect = self.redirect("admin_submissions", page.page)
challenge_id = None
team_id = None
if self.request.method == "POST":
# Someone wants to edit so make sure to load and check the form
# for CSRF and get the ID.
edit_form = SubmissionButtonForm(self.request.POST, csrf_context=self.request)
if not edit_form.validate():
# Error, most likely CSRF
return redirect
challenge_id = edit_form.challenge_id.data
team_id = edit_form.team_id.data
# Either load a new form or load old data into it.
if challenge_id is None or team_id is None:
form = SubmissionForm(self.request.POST, csrf_context=self.request)
else:
submission = (
DBSession.query(Submission)
.filter(Submission.team_id == team_id)
.filter(Submission.challenge_id == challenge_id)
.one()
)
form = SubmissionForm(None, submission, csrf_context=self.request)
# Display the page
is_new = not bool(form.challenge.data and form.team.data)
return self._list_retparams(page, form, is_new=is_new)
def _admin_edit(self, route_name, FormClass, DatabaseClass, title):
"""
A generic function for a view that is invoked after an edit (or add)
has been performed. It is separate from that of
:meth:`AdminView._admin_list` to keep the code cleaner. It has the
same parameters and return types but can only be invoked as a ``POST``.
"""
# We don't accept GET or others here
assert self.request.method == "POST"
# Prepare parameters
form = FormClass(self.request.POST, csrf_context=self.request)
page = self.page(self.items(DatabaseClass))
redirect = self.redirect(route_name, page.page)
# Cancel button pressed?
if not form.submit.data:
return redirect
# Form errors?
if not form.validate():
return self._list_retparams(page, form)
# New item or existing one?
if not form.id.data:
db_item = DatabaseClass()
DBSession.add(db_item)
self.request.session.flash("%s added!" % title)
else:
db_item = DBSession.query(DatabaseClass).filter(DatabaseClass.id == form.id.data).one()
self.request.session.flash("%s edited!" % title)
# Transfer edits into database
form.populate_obj(db_item)
if db_item.id == "":
# Safe measure to ensure a clean item ID
db_item.id = None
return redirect
def scoreboard(self):
"""
The central most interesting view. This contains a list of all teams
with their points, sorted with the highest points on top. The most
complex part of the query is the query that calculates the sum of
points right in the SQL.
"""
def ranked(teams):
""" Iterator adding ranks to team results. """
last_score = None
for index, (team, score) in enumerate(teams, 1):
if last_score is None or score < last_score:
rank = index
last_score = score
yield (team, score, rank)
# Finally build the complete query. The as_scalar tells SQLAlchemy to
# use this as a single value (i.e. take the first coulmn)
teams = (DBSession.query(Team, Team.score).
filter(Team.active).
options(subqueryload('submissions'),
joinedload('submissions.challenge')).
order_by(desc("score")))
return {'teams': ranked(teams)}
