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 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 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 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 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 register_ip(event): if ("test-login" in event.request.session and event.request.session["test-login"] or event.request.path.startswith('/static')): return None team_id = event.request.authenticated_userid t = transaction.savepoint() if not team_id: return ip = unicode(event.request.client_addr) try: DBSession.add(TeamIP(team_id=team_id, ip=ip)) DBSession.flush() except IntegrityError: t.rollback()
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 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 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_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 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 main(global_config, **settings): """ This function returns a Pyramid WSGI application. """ fix_setting_types(settings) # Database engine = engine_from_config(settings, 'sqlalchemy.') DBSession.configure(bind=engine) # Session & Auth session_factory = session_factory_from_settings(settings) authn_policy = SessionAuthenticationPolicy(callback=groupfinder) authz_policy = ACLAuthorizationPolicy() # Add reCAPTCHA to registration form pub_key = settings["recaptcha.public_key"] priv_key = settings["recaptcha.private_key"] RegisterForm.captcha = RecaptchaField(public_key=pub_key, private_key=priv_key, secure=True) config = Configurator(settings=settings, session_factory=session_factory, authentication_policy=authn_policy, authorization_policy=authz_policy, root_factory=RootFactory, ) # Add request properties config.add_request_method(load_settings, b'settings', reify=True) config.add_request_method(get_team, b'team', reify=True) # Routes & Views static_dir = 'static' subdirectory = settings.get("subdirectory", "") if subdirectory: static_dir = subdirectory + "/" + static_dir config.add_static_view(static_dir, 'static', cache_max_age=3600) init_routes(config, settings, subdirectory) for mod in dynamic_challenges.registry.values(): mod.activate(config, settings) config.scan() return config.make_wsgi_app()
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 _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 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 register_team(form, request): """ Create a new team from a form and send a confirmation email. Args: ``form``: A filled out :class:`fluxscoreboard.forms.front.RegisterForm`. ``request``: The corresponding request. Returns: The :class:`Team` that was created. """ team = Team(name=form.name.data, email=form.email.data, password=form.password.data, country=form.country.data, timezone=form.timezone.data, size=form.size.data, ) DBSession.add(team) DBSession.flush() send_activation_mail(team, request) return team
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 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