/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* *
* WARNING *
* *
* This file is auto generated by ./generate_connectors.sh *
* *
* Do not edit this file directly. Instead edit one of the corresponding *
* .header.rs oder .base.rs files. *
* *
* *
* *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
/* medal *\
* Copyright (C) 2020 Bundesweite Informatikwettbewerbe *
* *
* This program is free software: you can redistribute it and/or modify it under the terms of the GNU Affero *
* General Public License as published by the Free Software Foundation, either version 3 of the License, or (at *
* your option) any later version. *
* *
* This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the *
* implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Affero General Public *
* License for more details. *
* *
* You should have received a copy of the GNU Affero General Public License along with this program. If not, see *
\* . */
#![cfg(feature = "postgres")]
extern crate postgres;
use postgres::Connection;
use time;
use time::Duration;
use db_conn::{MedalConnection, MedalObject};
use db_objects::*;
use helpers;
trait Queryable {
fn query_map_one(&self, sql: &str, params: &[&dyn postgres::types::ToSql], f: F)
-> postgres::Result>
where F: FnOnce(postgres::rows::Row<'_>) -> T;
fn query_map_many(&self, sql: &str, params: &[&dyn postgres::types::ToSql], f: F) -> postgres::Result>
where F: FnMut(postgres::rows::Row<'_>) -> T;
fn exists(&self, sql: &str, params: &[&dyn postgres::types::ToSql]) -> bool;
fn get_last_id(&self) -> Option;
}
impl Queryable for Connection {
fn query_map_one(&self, sql: &str, params: &[&dyn postgres::types::ToSql], f: F)
-> postgres::Result>
where F: FnOnce(postgres::rows::Row<'_>) -> T {
let rows = self.query(sql, params)?;
Ok(rows.iter().next().map(f))
}
fn query_map_many(&self, sql: &str, params: &[&dyn postgres::types::ToSql], f: F) -> postgres::Result>
where F: FnMut(postgres::rows::Row<'_>) -> T {
Ok(self.query(sql, params)?.iter().map(f).collect())
}
fn exists(&self, sql: &str, params: &[&dyn postgres::types::ToSql]) -> bool {
let stmt = self.prepare(sql).unwrap();
!stmt.query(params).unwrap().is_empty()
}
fn get_last_id(&self) -> Option {
self.query("SELECT lastval()", &[]).unwrap().iter().next().map(|row| {
let r: i64 = row.get(0);
r as i32
})
}
// Empty line intended
}
impl MedalObject for Submission {
fn save(&mut self, conn: &Connection) {
match self.get_id() {
Some(_id) => unimplemented!(),
None => {
let query = "INSERT INTO submission (task, session, grade, validated, nonvalidated_grade,
subtask_identifier, value, date, needs_validation)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)";
conn.execute(query,
&[&self.task,
&self.session_user,
&self.grade,
&self.validated,
&self.nonvalidated_grade,
&self.subtask_identifier,
&self.value,
&self.date,
&self.needs_validation])
.unwrap();
self.set_id(conn.get_last_id().unwrap());
}
}
}
}
impl MedalObject for Grade {
fn save(&mut self, conn: &Connection) {
let query = "INSERT INTO grade (taskgroup, session, grade, validated)
VALUES ($1, $2, $3, $4)
ON CONFLICT ON CONSTRAINT grade_pkey DO UPDATE SET grade = excluded.grade, validated = excluded.validated";
conn.execute(query, &[&self.taskgroup, &self.user, &self.grade, &self.validated]).unwrap();
}
}
/* medal *\
* Copyright (C) 2020 Bundesweite Informatikwettbewerbe *
* *
* This program is free software: you can redistribute it and/or modify it under the terms of the GNU Affero *
* General Public License as published by the Free Software Foundation, either version 3 of the License, or (at *
* your option) any later version. *
* *
* This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the *
* implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Affero General Public *
* License for more details. *
* *
* You should have received a copy of the GNU Affero General Public License along with this program. If not, see *
\* . */
impl MedalObject for Participation {
fn save(&mut self, conn: &Connection) {
let query = "INSERT INTO participation (contest, session, start_date)
VALUES ($1, $2, $3)";
conn.execute(query, &[&self.contest, &self.user, &self.start]).unwrap();
}
}
impl MedalObject for Group {
fn save(&mut self, conn: &Connection) {
match self.get_id() {
Some(_id) => unimplemented!(),
None => {
let query = "INSERT INTO usergroup (name, groupcode, tag, admin)
VALUES ($1, $2, $3, $4)";
conn.execute(query, &[&self.name, &self.groupcode, &self.tag, &self.admin]).unwrap();
self.set_id(conn.get_last_id().unwrap());
}
}
}
}
impl MedalObject for Task {
fn save(&mut self, conn: &Connection) {
let query = "SELECT id
FROM task
WHERE taskgroup = $1
AND location = $2";
conn.query_map_one(query, &[&self.taskgroup, &self.location], |row| row.get(0))
.unwrap_or(None)
.and_then(|id| {
self.set_id(id);
Some(())
})
.unwrap_or(()); // Err means no entry yet and is expected result
let id = match self.get_id() {
Some(id) => {
let query = "UPDATE task
SET taskgroup = $1, location = $2, stars = $3
WHERE id = $4";
conn.execute(query, &[&self.taskgroup, &self.location, &self.stars, &id]).unwrap();
id
}
None => {
let query = "INSERT INTO task (taskgroup, location, stars)
VALUES ($1, $2, $3)";
conn.execute(query, &[&self.taskgroup, &self.location, &self.stars]).unwrap();
conn.get_last_id().unwrap()
}
};
self.set_id(id);
}
}
impl MedalObject for Taskgroup {
fn save(&mut self, conn: &Connection) {
if let Some(first_task) = self.tasks.get(0) {
let query = "SELECT taskgroup.id
FROM taskgroup
JOIN task
ON task.taskgroup = taskgroup.id
WHERE contest = $1
AND task.location = $2";
conn.query_map_one(query, &[&self.contest, &first_task.location], |row| row.get(0))
.unwrap_or(None)
.and_then(|id| {
self.set_id(id);
Some(())
})
.unwrap_or(()); // Err means no entry yet and is expected result
}
let id = match self.get_id() {
Some(id) => {
let query = "UPDATE taskgroup
SET contest = $1, name = $2, active = $3, positionalnumber = $4
WHERE id = $5";
conn.execute(query, &[&self.contest, &self.name, &self.active, &self.positionalnumber, &id]).unwrap();
id
}
None => {
let query = "INSERT INTO taskgroup (contest, name, active, positionalnumber)
VALUES ($1, $2, $3, $4)";
conn.execute(query, &[&self.contest, &self.name, &self.active, &self.positionalnumber]).unwrap();
conn.get_last_id().unwrap()
}
};
self.set_id(id);
for mut task in &mut self.tasks {
task.taskgroup = id;
task.save(conn);
}
}
}
impl MedalObject for Contest {
fn save(&mut self, conn: &Connection) {
let query = "SELECT id
FROM contest
WHERE location = $1
AND filename = $2";
conn.query_map_one(query, &[&self.location, &self.filename], |row| row.get(0))
.unwrap_or(None)
.and_then(|id| {
self.set_id(id);
Some(())
})
.unwrap_or(()); // Err means no entry yet and is expected result
let id = match self.get_id() {
Some(id) => {
let query = "UPDATE contest
SET location = $2,filename = $3, name = $4, duration = $5, public = $6, start_date = $7,
end_date = $8, min_grade = $9, max_grade = $10, positionalnumber = $11,
requires_login = $12, secret = $13
WHERE id = $1";
conn.execute(query,
&[&id,
&self.location,
&self.filename,
&self.name,
&self.duration,
&self.public,
&self.start,
&self.end,
&self.min_grade,
&self.max_grade,
&self.positionalnumber,
&self.requires_login,
&self.secret])
.unwrap();
id
}
None => {
let query = "INSERT INTO contest (location, filename, name, duration, public, start_date, end_date,
min_grade, max_grade, positionalnumber, requires_login, secret)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)";
conn.execute(query,
&[&self.location,
&self.filename,
&self.name,
&self.duration,
&self.public,
&self.start,
&self.end,
&self.min_grade,
&self.max_grade,
&self.positionalnumber,
&self.requires_login,
&self.secret])
.unwrap();
conn.get_last_id().unwrap()
}
};
self.set_id(id);
for mut taskgroup in &mut self.taskgroups {
taskgroup.contest = id;
taskgroup.save(conn);
}
}
}
impl MedalConnection for Connection {
fn dbtype(&self) -> &'static str { "postgres" }
fn migration_already_applied(&self, name: &str) -> bool {
let create_string = "CREATE TABLE IF NOT EXISTS migrations (name TEXT PRIMARY KEY);";
self.execute(create_string, &[]).unwrap();
let query = "SELECT name FROM migrations WHERE name = $1";
self.exists(query, &[&name])
}
fn apply_migration(&mut self, name: &str, contents: &str) {
print!("Applying migration `{}` … ", name);
let tx = self.transaction().unwrap();
tx.batch_execute(&contents).unwrap();
tx.execute("INSERT INTO migrations (name) VALUES ($1)", &[&name]).unwrap();
tx.commit().unwrap();
println!("OK.");
}
// fn get_session(&self, key: T, keyname: &str) -> Option {
fn get_session(&self, key: &str) -> Option {
let query = "SELECT id, csrf_token, last_login, last_activity, permanent_login, username, password, salt,
logincode, email, email_unconfirmed, email_confirmationcode, firstname, lastname, street,
zip, city, nation, grade, sex, is_admin, is_teacher, managed_by, oauth_provider, oauth_foreign_id
FROM session
WHERE session_token = $1";
let session = self.query_map_one(query, &[&key], |row| SessionUser { id: row.get(0),
session_token: Some(key.to_string()),
csrf_token: row.get(1),
last_login: row.get(2),
last_activity: row.get(3),
permanent_login: row.get(4),
username: row.get(5),
password: row.get(6),
salt: row.get(7),
logincode: row.get(8),
email: row.get(9),
email_unconfirmed: row.get(10),
email_confirmationcode: row.get(11),
firstname: row.get(12),
lastname: row.get(13),
street: row.get(14),
zip: row.get(15),
city: row.get(16),
nation: row.get(17),
grade: row.get(18),
sex: row.get(19),
is_admin: row.get(20),
is_teacher: row.get(21),
managed_by: row.get(22),
oauth_provider: row.get(23),
oauth_foreign_id: row.get(24) })
.ok()??;
let duration = if session.permanent_login { Duration::days(90) } else { Duration::minutes(90) };
let now = time::get_time();
if let Some(last_activity) = session.last_activity {
if now - last_activity < duration {
let query = "UPDATE session
SET last_activity = $1
WHERE id = $2";
self.execute(query, &[&now, &session.id]).unwrap();
return Some(session);
} else {
// Session timed out
// Should remove session token from session
return None;
}
}
// last_activity undefined
// TODO: What should happen here?
None
}
fn save_session(&self, session: SessionUser) {
self.execute("UPDATE session
SET username = $1,
password = $2,
salt = $3,
logincode = $4,
firstname = $5,
lastname = $6,
street = $7,
zip = $8,
city = $9,
grade = $10,
sex = $11,
is_admin = $12,
is_teacher = $13,
permanent_login = $14
WHERE id = $15",
&[&session.username,
&session.password,
&session.salt,
&session.logincode,
&session.firstname,
&session.lastname,
&session.street,
&session.zip,
&session.city,
&session.grade,
&session.sex,
&session.is_admin,
&session.is_teacher,
&session.permanent_login,
&session.id])
.unwrap();
}
fn new_session(&self, session_token: &str) -> SessionUser {
let csrf_token = helpers::make_csrf_token();
let now = time::get_time();
let query = "INSERT INTO session (session_token, csrf_token, last_activity, permanent_login, grade, sex,
is_teacher)
VALUES ($1, $2, $3, $4, $5, $6, $7)";
self.execute(query, &[&session_token, &csrf_token, &now, &false, &0, &None::, &false]).unwrap();
let id = self.get_last_id().expect("Expected to get last row id");
SessionUser::minimal(id, session_token.to_owned(), csrf_token)
}
fn get_session_or_new(&self, key: &str) -> SessionUser {
let query = "UPDATE session
SET session_token = $1
WHERE session_token = $2";
self.get_session(&key).ensure_alive().unwrap_or_else(|| {
// TODO: Factor this out in own function
// TODO: Should a new session key be generated every time?
self.execute(query, &[&Option::::None, &key]).unwrap();
self.new_session(&key)
})
}
fn get_user_by_id(&self, user_id: i32) -> Option {
let query = "SELECT session_token, csrf_token, last_login, last_activity, permanent_login, username, password,
salt, logincode, email, email_unconfirmed, email_confirmationcode, firstname, lastname,
street, zip, city, nation, grade, sex, is_admin, is_teacher, managed_by, oauth_provider,
oauth_foreign_id
FROM session
WHERE id = $1";
self.query_map_one(query, &[&user_id], |row| SessionUser { id: user_id,
session_token: row.get(0),
csrf_token: row.get(1),
last_login: row.get(2),
last_activity: row.get(3),
permanent_login: row.get(4),
username: row.get(5),
password: row.get(6),
salt: row.get(7),
logincode: row.get(8),
email: row.get(9),
email_unconfirmed: row.get(10),
email_confirmationcode: row.get(11),
firstname: row.get(12),
lastname: row.get(13),
street: row.get(14),
zip: row.get(15),
city: row.get(16),
nation: row.get(17),
grade: row.get(18),
sex: row.get(19),
is_admin: row.get(20),
is_teacher: row.get(21),
managed_by: row.get(22),
oauth_provider: row.get(23),
oauth_foreign_id: row.get(24) })
.ok()?
}
fn get_user_and_group_by_id(&self, user_id: i32) -> Option<(SessionUser, Option)> {
let session = self.get_user_by_id(user_id)?;
let group_id = match session.managed_by {
Some(id) => id,
None => return Some((session, None)),
};
let query = "SELECT name, groupcode, tag, admin
FROM usergroup
WHERE id = $1";
let res = self.query_map_one(query, &[&group_id], |row| Group { id: Some(group_id),
name: row.get(0),
groupcode: row.get(1),
tag: row.get(2),
admin: row.get(3),
members: Vec::new() })
.ok()?;
match res {
Some(group) => Some((session, Some(group))),
_ => Some((session, None)),
}
}
//TODO: use session
fn login(&self, _session: Option<&str>, username: &str, password: &str) -> Result {
let query = "SELECT id, password, salt
FROM session
WHERE username = $1";
self.query_map_one(query, &[&username], |row| {
let (id, password_hash, salt): (i32, Option, Option) =
(row.get(0), row.get(1), row.get(2));
//password_hash ist das, was in der Datenbank steht
if helpers::verify_password(&password,
&salt.ok_or_else(|| println!("salt from database empty"))?,
&password_hash.ok_or_else(|| println!("password from database empty"))?)
{
// TODO: fail more pleasantly
// Login okay, update session now!
let session_token = helpers::make_session_token();
let csrf_token = helpers::make_csrf_token();
let now = time::get_time();
let query = "UPDATE session
SET session_token = $1, csrf_token = $2, last_login = $3, last_activity = $3
WHERE id = $4";
self.execute(query, &[&session_token, &csrf_token, &now, &id]).unwrap();
Ok(session_token)
} else {
Err(())
}
})
.map_err(|_| ())?
.ok_or(())?
}
//TODO: use session
fn login_with_code(&self, _session: Option<&str>, logincode: &str) -> Result {
let query = "SELECT id
FROM session
WHERE logincode = $1";
self.query_map_one(query, &[&logincode], |row| {
// Login okay, update session now!
let id: i32 = row.get(0);
let session_token = helpers::make_session_token();
let csrf_token = helpers::make_csrf_token();
let now = time::get_time();
let query = "UPDATE session
SET session_token = $1, csrf_token = $2, last_login = $3, last_activity = $3
WHERE id = $4";
self.execute(query, &[&session_token, &csrf_token, &now, &id]).unwrap();
session_token
})
.map_err(|_| ())?
.ok_or(())
}
//TODO: use session
fn login_foreign(&self, _session: Option<&str>, provider_id: &str, foreign_id: &str, is_teacher: bool,
firstname: &str, lastname: &str)
-> Result
{
let session_token = helpers::make_session_token();
let csrf_token = helpers::make_csrf_token();
let now = time::get_time();
let query = "SELECT id
FROM session
WHERE oauth_foreign_id = $1
AND oauth_provider = $2";
match self.query_map_one(query, &[&foreign_id, &provider_id], |row| -> i32 { row.get(0) }) {
Ok(Some(id)) => {
let query = "UPDATE session
SET session_token = $1, csrf_token = $2, last_login = $3, last_activity = $3
WHERE id = $4";
self.execute(query, &[&session_token, &csrf_token, &now, &id]).unwrap();
Ok(session_token)
}
// Add!
_ => {
let query = "INSERT INTO session (session_token, csrf_token, last_login, last_activity,
permanent_login, grade, sex, is_teacher, oauth_foreign_id,
oauth_provider, firstname, lastname)
VALUES ($1, $2, $3, $3, $4, $5, $6, $7, $8, $9, $10, $11)";
self.execute(query,
&[&session_token,
&csrf_token,
&now,
&false,
&(if is_teacher { 255 } else { 0 }),
&None::,
&is_teacher,
&foreign_id,
&provider_id,
&firstname,
&lastname])
.unwrap();
Ok(session_token)
}
}
}
//TODO: use session
fn create_user_with_groupcode(&self, _session: Option<&str>, groupcode: &str) -> Result {
let query = "SELECT id
FROM usergroup
WHERE groupcode = $1";
let group_id =
self.query_map_one(query, &[&groupcode], |row| -> i32 { row.get(0) }).map_err(|_| ())?.ok_or(())?;
// Login okay, create session!
let session_token = helpers::make_session_token();
let csrf_token = helpers::make_csrf_token();
let login_code = helpers::make_login_code(); // TODO: check for collisions
let now = time::get_time();
let query = "INSERT INTO session (session_token, csrf_token, last_login, last_activity, permanent_login,
logincode, grade, sex, is_teacher, managed_by)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)";
self.execute(query,
&[&session_token,
&csrf_token,
&now,
&now,
&false,
&login_code,
&0,
&None::,
&false,
&group_id])
.unwrap();
Ok(session_token)
}
fn create_group_with_users(&self, mut group: Group) {
// Generate group ID:
group.save(self);
for user in group.members {
let csrf_token = helpers::make_csrf_token();
let login_code = helpers::make_login_code(); // TODO: check for collisions
let query = "INSERT INTO session (firstname, lastname, csrf_token, permanent_login, logincode, grade, sex,
is_teacher, managed_by)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)";
self.execute(query,
&[&user.firstname,
&user.lastname,
&csrf_token,
&false,
&login_code,
&user.grade,
&None::,
&false,
&group.id])
.unwrap();
}
}
fn logout(&self, session: &str) {
let query = "UPDATE session
SET session_token = NULL
WHERE session_token = $1";
self.execute(query, &[&session]).unwrap();
}
fn load_submission(&self, session: &SessionUser, task: i32, subtask: Option<&str>) -> Option {
match subtask {
None => {
let query = "SELECT id, grade, validated, nonvalidated_grade, value, date, needs_validation
FROM submission
WHERE task = $1
AND session = $2
ORDER BY id DESC
LIMIT 1";
self.query_map_one(query, &[&task, &session.id], |row| Submission { id: Some(row.get(0)),
task: task,
session_user: session.id,
grade: row.get(1),
validated: row.get(2),
nonvalidated_grade: row.get(3),
subtask_identifier: None,
value: row.get(4),
date: row.get(5),
needs_validation: row.get(6) })
.ok()?
}
Some(subtask_id) => {
let query = "SELECT id, grade, validated, nonvalidated_grade, value, date, needs_validation
FROM submission
WHERE task = $1
AND session = $2
AND subtask_identifier = $3
ORDER BY id DESC
LIMIT 1";
self.query_map_one(query, &[&task, &session.id, &subtask_id], |row| {
Submission { id: Some(row.get(0)),
task: task,
session_user: session.id,
grade: row.get(1),
validated: row.get(2),
nonvalidated_grade: row.get(3),
subtask_identifier: Some(subtask_id.to_string()),
value: row.get(4),
date: row.get(5),
needs_validation: row.get(6) }
})
.ok()?
}
}
}
fn submit_submission(&self, mut submission: Submission) {
submission.save(self);
let mut grade = self.get_grade_by_submission(submission.id.unwrap());
if grade.grade.is_none() || submission.grade > grade.grade.unwrap() {
grade.grade = Some(submission.grade);
grade.validated = false;
grade.save(self);
}
}
fn get_grade_by_submission(&self, submission_id: i32) -> Grade {
let query = "SELECT grade.taskgroup, grade.session, grade.grade, grade.validated
FROM grade
JOIN task ON grade.taskgroup = task.taskgroup
JOIN submission ON task.id = submission.task
AND grade.session = submission.session
WHERE submission.id = $1";
self.query_map_one(query, &[&submission_id], |row| Grade { taskgroup: row.get(0),
user: row.get(1),
grade: row.get(2),
validated: row.get(3) })
.unwrap_or(None)
.unwrap_or_else(|| {
let query = "SELECT task.taskgroup, submission.session
FROM submission
JOIN task ON task.id = submission.task
WHERE submission.id = $1";
self.query_map_one(query, &[&submission_id], |row| Grade { taskgroup: row.get(0),
user: row.get(1),
grade: None,
validated: false })
.unwrap()
.unwrap() // should this unwrap?
})
}
fn get_contest_groups_grades(&self, session_id: i32, contest_id: i32)
-> (Vec, Vec<(Group, Vec<(UserInfo, Vec)>)>) {
let query = "SELECT id, name
FROM taskgroup
WHERE contest = $1
AND active = $2
ORDER BY positionalnumber";
let tasknames: Vec<(i32, String)> =
self.query_map_many(query, &[&contest_id, &true], |row| (row.get(0), row.get(1))).unwrap();
let mut taskindex: ::std::collections::BTreeMap = ::std::collections::BTreeMap::new();
let n_tasks = tasknames.len();
for (index, (i, _)) in tasknames.iter().enumerate() {
taskindex.insert(*i, index);
}
let query = "SELECT grade.taskgroup, grade.session, grade.grade, grade.validated, usergroup.id, usergroup.name,
usergroup.groupcode, usergroup.tag, student.id, student.username, student.logincode,
student.firstname, student.lastname, student.grade AS sgrade
FROM grade
JOIN taskgroup ON grade.taskgroup = taskgroup.id
JOIN session AS student ON grade.session = student.id
JOIN usergroup ON student.managed_by = usergroup.id
WHERE usergroup.admin = $1
AND taskgroup.contest = $2
AND taskgroup.active = $3
ORDER BY usergroup.id, sgrade, student.lastname, student.firstname, student.id,
taskgroup.positionalnumber";
let gradeinfo =
self.query_map_many(query, &[&session_id, &contest_id, &true], |row| {
(Grade { taskgroup: row.get(0), user: row.get(1), grade: row.get(2), validated: row.get(3) },
Group { id: Some(row.get(4)),
name: row.get(5),
groupcode: row.get(6),
tag: row.get(7),
admin: session_id,
members: Vec::new() },
UserInfo { id: row.get(8),
username: row.get(9),
logincode: row.get(10),
firstname: row.get(11),
lastname: row.get(12),
grade: row.get(13) })
})
.unwrap();
let mut gradeinfo_iter = gradeinfo.iter();
if let Some(t /*Ok((grade, mut group, mut userinfo))*/) = gradeinfo_iter.next() {
let (grade, mut group, mut userinfo) = t.clone();
let mut grades: Vec = vec![Default::default(); n_tasks];
let mut users: Vec<(UserInfo, Vec)> = Vec::new();
let mut groups: Vec<(Group, Vec<(UserInfo, Vec)>)> = Vec::new();
let index = grade.taskgroup;
grades[taskindex[&index]] = grade;
for ggu in gradeinfo_iter {
let (g, gr, ui) = ggu;
if gr.id != group.id {
users.push((userinfo, grades));
userinfo = ui.clone();
grades = vec![Default::default(); n_tasks];
groups.push((group, users));
group = gr.clone();
users = Vec::new();
} else if ui.id != userinfo.id {
users.push((userinfo, grades));
userinfo = ui.clone();
grades = vec![Default::default(); n_tasks];
}
let index = g.taskgroup;
grades[taskindex[&index]] = *g;
}
users.push((userinfo, grades));
groups.push((group, users));
(tasknames.iter().map(|(_, name)| name.clone()).collect(), groups)
} else {
(Vec::new(), Vec::new()) // should those be default filled?
}
}
fn get_contest_user_grades(&self, session_token: &str, contest_id: i32) -> Vec {
let query = "SELECT id, name
FROM taskgroup
WHERE contest = $1
AND active = $2
ORDER BY positionalnumber";
let tasknames: Vec<(i32, String)> =
self.query_map_many(query, &[&contest_id, &true], |row| (row.get(0), row.get(1))).unwrap();
let mut taskindex: ::std::collections::BTreeMap = ::std::collections::BTreeMap::new();
let n_tasks = tasknames.len();
for (index, (i, _)) in tasknames.iter().enumerate() {
taskindex.insert(*i, index);
}
let query = "SELECT grade.taskgroup, grade.session, grade.grade, grade.validated
FROM grade
JOIN taskgroup ON grade.taskgroup = taskgroup.id
JOIN session ON session.id = grade.session
WHERE session.session_token = $1
AND taskgroup.contest = $2
AND taskgroup.active = $3
ORDER BY taskgroup.positionalnumber";
let gradeinfo =
self.query_map_many(query, &[&session_token, &contest_id, &true], |row| Grade { taskgroup: row.get(0),
user: row.get(1),
grade: row.get(2),
validated: row.get(3) })
.unwrap();
let gradeinfo_iter = gradeinfo.iter();
let mut grades: Vec = vec![Default::default(); n_tasks];
for g in gradeinfo_iter {
let index = g.taskgroup;
grades[taskindex[&index]] = *g;
}
grades
}
fn get_taskgroup_user_grade(&self, session_token: &str, taskgroup_id: i32) -> Grade {
let query = "SELECT grade.taskgroup, grade.session, grade.grade, grade.validated
FROM grade
JOIN session ON session.id = grade.session
WHERE session.session_token = $1
AND grade.taskgroup = $2";
self.query_map_one(query, &[&session_token, &taskgroup_id], |row| Grade { taskgroup: row.get(0),
user: row.get(1),
grade: row.get(2),
validated: row.get(3) })
.unwrap_or(None)
.unwrap_or_default()
}
fn get_contest_list(&self) -> Vec {
let query = "SELECT id, location, filename, name, duration, public, start_date, end_date, min_grade, max_grade,
positionalnumber, requires_login, secret
FROM contest
ORDER BY positionalnumber";
self.query_map_many(query, &[], |row| Contest { id: Some(row.get(0)),
location: row.get(1),
filename: row.get(2),
name: row.get(3),
duration: row.get(4),
public: row.get(5),
start: row.get(6),
end: row.get(7),
min_grade: row.get(8),
max_grade: row.get(9),
positionalnumber: row.get(10),
requires_login: row.get(11),
secret: row.get(12),
taskgroups: Vec::new() })
.unwrap()
}
fn get_contest_by_id(&self, contest_id: i32) -> Contest {
let query = "SELECT location, filename, name, duration, public, start_date, end_date, min_grade, max_grade,
requires_login, secret
FROM contest
WHERE id = $1";
self.query_map_one(query, &[&contest_id], |row| Contest { id: Some(contest_id),
location: row.get(0),
filename: row.get(1),
name: row.get(2),
duration: row.get(3),
public: row.get(4),
start: row.get(5),
end: row.get(6),
min_grade: row.get(7),
max_grade: row.get(8),
positionalnumber: None,
requires_login: row.get(9),
secret: row.get(10),
taskgroups: Vec::new() })
.unwrap()
.unwrap() // TODO: Should return Option?
}
fn get_contest_by_id_complete(&self, contest_id: i32) -> Contest {
let query = "SELECT contest.location, contest.filename, contest.name, contest.duration, contest.public,
contest.start_date, contest.end_date, contest.min_grade, contest.max_grade,
contest.requires_login, contest.secret, taskgroup.id, taskgroup.name, task.id,
task.location, task.stars
FROM contest
JOIN taskgroup ON contest.id = taskgroup.contest
JOIN task ON taskgroup.id = task.taskgroup
WHERE contest.id = $1
AND taskgroup.active = $2
ORDER BY taskgroup.positionalnumber";
let taskgroupcontest =
self.query_map_many(query, &[&contest_id, &true], |row| {
(Contest { id: Some(contest_id),
location: row.get(0),
filename: row.get(1),
name: row.get(2),
duration: row.get(3),
public: row.get(4),
start: row.get(5),
end: row.get(6),
min_grade: row.get(7),
max_grade: row.get(8),
positionalnumber: None,
requires_login: row.get(9),
secret: row.get(10),
taskgroups: Vec::new() },
Taskgroup { id: Some(row.get(11)),
contest: contest_id,
name: row.get(12),
active: true,
positionalnumber: None,
tasks: Vec::new() },
Task { id: Some(row.get(13)), taskgroup: row.get(11), location: row.get(14), stars: row.get(15) })
})
.unwrap();
let mut taskgroupcontest_iter = taskgroupcontest.into_iter();
let (mut contest, mut taskgroup, task) = taskgroupcontest_iter.next().unwrap();
taskgroup.tasks.push(task);
for tgc in taskgroupcontest_iter {
let (_, tg, t) = tgc;
if tg.id != taskgroup.id {
contest.taskgroups.push(taskgroup);
taskgroup = tg;
}
taskgroup.tasks.push(t);
}
contest.taskgroups.push(taskgroup);
contest
}
fn get_contest_by_id_partial(&self, contest_id: i32) -> Contest {
let query = "SELECT contest.location, contest.filename, contest.name, contest.duration, contest.public,
contest.start_date, contest.end_date, contest.min_grade, contest.max_grade,
contest.requires_login, contest_secret, taskgroup.id, taskgroup.name
FROM contest
JOIN taskgroup ON contest.id = taskgroup.contest
WHERE contest.id = $1
AND taskgroup.active = $2";
let taskgroupcontest = self.query_map_many(query, &[&contest_id, &true], |row| {
(Contest { id: Some(contest_id),
location: row.get(0),
filename: row.get(1),
name: row.get(2),
duration: row.get(3),
public: row.get(4),
start: row.get(5),
end: row.get(6),
min_grade: row.get(7),
max_grade: row.get(8),
positionalnumber: None,
requires_login: row.get(9),
secret: row.get(10),
taskgroups: Vec::new() },
Taskgroup { id: Some(row.get(11)),
contest: contest_id,
name: row.get(12),
active: true,
positionalnumber: None,
tasks: Vec::new() })
})
.unwrap();
let mut taskgroupcontest_iter = taskgroupcontest.into_iter();
let (mut contest, taskgroup) = taskgroupcontest_iter.next().unwrap();
contest.taskgroups.push(taskgroup);
for tgc in taskgroupcontest_iter {
let (_, tg) = tgc;
contest.taskgroups.push(tg);
}
contest
}
fn get_participation(&self, session: &str, contest_id: i32) -> Option {
let query = "SELECT session, start_date
FROM participation
JOIN session ON session.id = session
WHERE session.session_token = $1
AND contest = $2";
self.query_map_one(query, &[&session, &contest_id], |row| Participation { contest: contest_id,
user: row.get(0),
start: row.get(1) })
.ok()?
}
fn new_participation(&self, session: &str, contest_id: i32) -> Result {
let query = "SELECT session, start_date
FROM participation
JOIN session ON session.id = session
WHERE session.session_token = $1
AND contest = $2";
match self.query_map_one(query, &[&session, &contest_id], |_| {}).map_err(|_| ())? {
Some(()) => Err(()),
None => {
let now = time::get_time();
self.execute(
"INSERT INTO participation (contest, session, start_date)
SELECT $1, id, $2 FROM session WHERE session_token = $3",
&[&contest_id, &now, &session],
)
.unwrap();
Ok(self.get_participation(session, contest_id).unwrap()) // TODO: This errors if not logged in …
}
}
}
fn get_task_by_id(&self, task_id: i32) -> Task {
let query = "SELECT location, stars, taskgroup
FROM task
WHERE id = $1";
self.query_map_one(query, &[&task_id], |row| Task { id: Some(task_id),
taskgroup: row.get(2),
location: row.get(0),
stars: row.get(1) })
.unwrap()
.unwrap()
}
fn get_task_by_id_complete(&self, task_id: i32) -> (Task, Taskgroup, Contest) {
let query = "SELECT task.location, task.stars, taskgroup.id, taskgroup.name, taskgroup.active, contest.id,
contest.location, contest.filename, contest.name, contest.duration, contest.public,
contest.start_date, contest.end_date, contest.min_grade, contest.max_grade,
contest.requires_login, contest.secret
FROM contest
JOIN taskgroup ON taskgroup.contest = contest.id
JOIN task ON task.taskgroup = taskgroup.id
WHERE task.id = $1";
self.query_map_one(query, &[&task_id], |row| {
(Task { id: Some(task_id), taskgroup: row.get(2), location: row.get(0), stars: row.get(1) },
Taskgroup { id: Some(row.get(2)),
contest: row.get(5),
name: row.get(3),
active: row.get(4),
positionalnumber: None,
tasks: Vec::new() },
Contest { id: Some(row.get(5)),
location: row.get(6),
filename: row.get(7),
name: row.get(8),
duration: row.get(9),
public: row.get(10),
start: row.get(11),
end: row.get(12),
min_grade: row.get(13),
max_grade: row.get(14),
positionalnumber: None,
requires_login: row.get(15),
secret: row.get(16),
taskgroups: Vec::new() })
})
.unwrap()
.unwrap()
}
fn get_submission_to_validate(&self, tasklocation: &str, subtask: Option<&str>) -> i32 {
match subtask {
Some(st) => {
let query = "SELECT id
FROM submission
JOIN task ON submission.task = task.id
WHERE task.location = $1
AND subtask_identifier = $2
AND needs_validation = 1
LIMIT 1";
self.query_map_one(query, &[&tasklocation, &st], |row| row.get(0)).unwrap().unwrap()
}
None => {
let query = "SELECT id
FROM submission
JOIN task ON submission.task = task.id
WHERE task.location = $1
AND needs_validation = 1
LIMIT 1";
self.query_map_one(query, &[&tasklocation], |row| row.get(0)).unwrap().unwrap()
}
}
}
fn find_next_submission_to_validate(&self, userid: i32, taskgroupid: i32) {
let query = "SELECT id, validated
FROM submission
JOIN task ON submission.task = task.id
WHERE task.taskgroup = $1
AND submission.session = $2
ORDER BY value DESC id DESC
LIMIT 1";
let (id, validated): (i32, bool) =
self.query_map_one(query, &[&taskgroupid, &userid], |row| (row.get(0), row.get(1))).unwrap().unwrap();
if !validated {
let query = "UPDATE submission
SET needs_validation = 1
WHERE id = $1";
self.execute(query, &[&id]).unwrap();
}
}
fn add_group(&self, group: &mut Group) { group.save(self); }
fn get_groups(&self, session_id: i32) -> Vec {
let query = "SELECT id, name, groupcode, tag
FROM usergroup
WHERE admin = $1";
self.query_map_many(query, &[&session_id], |row| Group { id: Some(row.get(0)),
name: row.get(1),
groupcode: row.get(2),
tag: row.get(3),
admin: session_id,
members: Vec::new() })
.unwrap()
}
fn get_groups_complete(&self, _session_id: i32) -> Vec {
unimplemented!();
}
fn get_group_complete(&self, group_id: i32) -> Option {
let query = "SELECT name, groupcode, tag, admin
FROM usergroup
WHERE id = $1";
let mut group = self.query_map_one(query, &[&group_id], |row| Group { id: Some(group_id),
name: row.get(0),
groupcode: row.get(1),
tag: row.get(2),
admin: row.get(3),
members: Vec::new() })
.unwrap()
.unwrap(); // TODO handle error
let query = "SELECT id, session_token, csrf_token, last_login, last_activity, permanent_login, username,
password, logincode, email, email_unconfirmed, email_confirmationcode, firstname, lastname,
street, zip, city, nation, grade, sex, is_admin, is_teacher, oauth_provider, oauth_foreign_id, salt
FROM session
WHERE managed_by = $1";
group.members = self.query_map_many(query, &[&group_id], |row| SessionUser { id: row.get(0),
session_token: row.get(1),
csrf_token: row.get(2),
last_login: row.get(3),
last_activity: row.get(4),
permanent_login: row.get(5),
username: row.get(6),
password: row.get(7),
salt: row.get(22),
logincode: row.get(8),
email: row.get(9),
email_unconfirmed: row.get(10),
email_confirmationcode:
row.get(11),
firstname: row.get(12),
lastname: row.get(13),
street: row.get(14),
zip: row.get(15),
city: row.get(16),
nation: row.get(17),
grade: row.get(18),
sex: row.get(19),
is_admin: row.get(20),
is_teacher: row.get(21),
managed_by: Some(group_id),
oauth_provider: row.get(22),
oauth_foreign_id: row.get(23) })
.unwrap();
Some(group)
}
fn get_search_users(&self,
(s_id, s_firstname, s_lastname, s_logincode, s_groupcode, s_pms_id): (Option,
Option,
Option,
Option,
Option,
Option))
-> Result, Vec<(i32, String, String)>>
{
if let Some(id) = s_id {
let query = "SELECT id, firstname, lastname
FROM session
WHERE id = $1
LIMIT 30";
Ok(self.query_map_many(query, &[&id], |row| (row.get(0), row.get(1), row.get(2))).unwrap())
} else if let Some(logincode) = s_logincode {
let query = "SELECT id, firstname, lastname
FROM session
WHERE logincode = $1
LIMIT 30";
Ok(self.query_map_many(query, &[&logincode], |row| (row.get(0), row.get(1), row.get(2))).unwrap())
} else if let Some(groupcode) = s_groupcode {
let query = "SELECT id, name, tag
FROM usergroup
WHERE groupcode = $1
LIMIT 30";
Err(self.query_map_many(query, &[&groupcode], |row| {
(row.get(0),
format!("Gruppe: {}", row.get::<_, String>(1)),
format!("(Marker: {})", row.get::<_, String>(2)))
})
.unwrap())
} else if let Some(pms_id) = s_pms_id {
let query = "SELECT id, firstname, lastname
FROM session
WHERE oauth_foreign_id = $1
LIMIT 30";
Ok(self.query_map_many(query, &[&pms_id], |row| (row.get(0), row.get(1), row.get(2))).unwrap())
} else if let (Some(firstname), Some(lastname)) = (s_firstname, s_lastname) {
let query = "SELECT id, firstname, lastname
FROM session
WHERE firstname LIKE $1
AND lastname LIKE $2
LIMIT 30";
Ok(self.query_map_many(query, &[&firstname, &lastname], |row| (row.get(0), row.get(1), row.get(2)))
.unwrap())
} else {
Ok(Vec::new())
}
}
fn get_debug_information(&self) -> String {
let duration = Duration::minutes(60);
let now = time::get_time();
let then = now - duration;
let query = "SELECT count(*)
FROM session
WHERE last_activity > $1;";
let n_asession: i64 = self.query_map_one(query, &[&then], |row| row.get(0)).unwrap().unwrap();
let query = "SELECT count(*)
FROM participation
WHERE start_date > $1;";
let n_apart: i64 = self.query_map_one(query, &[&then], |row| row.get(0)).unwrap().unwrap();
let query = "SELECT count(*)
FROM session;";
let n_session: i64 = self.query_map_one(query, &[], |row| row.get(0)).unwrap().unwrap();
let query = "SELECT count(*)
FROM session
WHERE oauth_foreign_id IS NOT NULL OR logincode IS NOT NULL;";
let n_user: i64 = self.query_map_one(query, &[], |row| row.get(0)).unwrap().unwrap();
let query = "SELECT count(*)
FROM session
WHERE oauth_foreign_id IS NOT NULL;";
let n_pmsuser: i64 = self.query_map_one(query, &[], |row| row.get(0)).unwrap().unwrap();
let query = "SELECT count(*)
FROM session
WHERE is_teacher = $1;";
let n_teacher: i64 = self.query_map_one(query, &[&true], |row| row.get(0)).unwrap().unwrap();
let query = "SELECT count(*)
FROM participation;";
let n_part: i64 = self.query_map_one(query, &[], |row| row.get(0)).unwrap().unwrap();
let query = "SELECT count(*)
FROM submission;";
let n_sub: i64 = self.query_map_one(query, &[], |row| row.get(0)).unwrap().unwrap();
let query = "SELECT contest, count(*)
FROM participation
GROUP BY contest
ORDER BY contest DESC;";
let n_participations_by_id: Vec<(i32, i64)> =
self.query_map_many(query, &[], |row| (row.get(0), row.get(1))).unwrap();
format!(
"{{
\"timestamp\": {},
\"active_sessions\": {},
\"active_participations\": {},
\"sessions\": {},
\"users\": {},
\"pms_users\": {},
\"teachers\": {},
\"participations\": {},
\"submissions\": {},
\"participations_by_contest_id\": {{
{}
}}
}}
",
now.sec,
n_asession,
n_apart,
n_session,
n_user,
n_pmsuser,
n_teacher,
n_part,
n_sub,
n_participations_by_id.iter()
.map(|(x, y)| -> String { format!("\"{}\": {}", x, y) })
.collect::>()
.join(",\n ")
)
}
fn reset_all_contest_visibilities(&self) { self.execute("UPDATE contest SET public = $1", &[&false]).unwrap(); }
fn reset_all_taskgroup_visibilities(&self) { self.execute("UPDATE taskgroup SET active = $1", &[&false]).unwrap(); }
}