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
