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)}