db_conn_postgres.rs 64.3 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 *                                                                             *
 *        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.                                             *
 *                                                                             *
 *                                                                             *
 *                                                                             *
 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

14 15
#![cfg(feature = "postgres")]

16 17
extern crate postgres;

18 19 20
use postgres::Connection;
use time;
use time::Duration;
21 22 23

use db_conn::{MedalConnection, MedalObject};
use db_objects::*;
24
use helpers;
25

26
trait Queryable {
Robert Czechowski's avatar
rustfmt  
Robert Czechowski committed
27 28
    fn query_map_one<T, F>(&self, sql: &str, params: &[&dyn postgres::types::ToSql], f: F)
                           -> postgres::Result<Option<T>>
29
        where F: FnOnce(postgres::rows::Row<'_>) -> T;
30 31
    fn query_map_many<T, F>(&self, sql: &str, params: &[&dyn postgres::types::ToSql], f: F) -> postgres::Result<Vec<T>>
        where F: FnMut(postgres::rows::Row<'_>) -> T;
32
    fn exists(&self, sql: &str, params: &[&dyn postgres::types::ToSql]) -> bool;
33
    fn get_last_id(&self) -> Option<i32>;
34 35 36
}

impl Queryable for Connection {
Robert Czechowski's avatar
rustfmt  
Robert Czechowski committed
37 38
    fn query_map_one<T, F>(&self, sql: &str, params: &[&dyn postgres::types::ToSql], f: F)
                           -> postgres::Result<Option<T>>
39 40
        where F: FnOnce(postgres::rows::Row<'_>) -> T {
        let rows = self.query(sql, params)?;
41

42 43
        Ok(rows.iter().next().map(f))
    }
44

45 46 47 48 49
    fn query_map_many<T, F>(&self, sql: &str, params: &[&dyn postgres::types::ToSql], f: F) -> postgres::Result<Vec<T>>
        where F: FnMut(postgres::rows::Row<'_>) -> T {
        Ok(self.query(sql, params)?.iter().map(f).collect())
    }

50 51 52 53 54
    fn exists(&self, sql: &str, params: &[&dyn postgres::types::ToSql]) -> bool {
        let stmt = self.prepare(sql).unwrap();
        !stmt.query(params).unwrap().is_empty()
    }

55 56 57 58 59 60
    fn get_last_id(&self) -> Option<i32> {
        self.query("SELECT lastval()", &[]).unwrap().iter().next().map(|row| {
                                                                      let r: i64 = row.get(0);
                                                                      r as i32
                                                                  })
    }
61
    // Empty line intended
62 63
}

64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172
impl MedalObject<Connection> 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<Connection> 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();
    }
}
impl MedalObject<Connection> 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<Connection> 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<Connection> 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<Connection> 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
173 174 175
                             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();
176 177 178
                id
            }
            None => {
179 180 181
                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();
182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209
                conn.get_last_id().unwrap()
            }
        };
        self.set_id(id);
        for mut task in &mut self.tasks {
            task.taskgroup = id;
            task.save(conn);
        }
    }
}

impl MedalObject<Connection> 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
210 211 212 213
                             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";
214
                conn.execute(query,
215 216
                             &[&id,
                               &self.location,
217 218 219 220 221 222
                               &self.filename,
                               &self.name,
                               &self.duration,
                               &self.public,
                               &self.start,
                               &self.end,
223 224
                               &self.min_grade,
                               &self.max_grade,
225
                               &self.positionalnumber,
226 227
                               &self.requires_login,
                               &self.secret])
228 229 230 231
                    .unwrap();
                id
            }
            None => {
232
                let query = "INSERT INTO contest (location, filename, name, duration, public, start_date, end_date,
233 234
                                                  min_grade, max_grade, positionalnumber, requires_login, secret)
                             VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)";
235 236 237 238 239 240 241
                conn.execute(query,
                             &[&self.location,
                               &self.filename,
                               &self.name,
                               &self.duration,
                               &self.public,
                               &self.start,
242 243
                               &self.end,
                               &self.min_grade,
244
                               &self.max_grade,
245 246 247
                               &self.positionalnumber,
                               &self.requires_login,
                               &self.secret])
248 249 250 251 252 253 254 255 256 257 258 259
                    .unwrap();
                conn.get_last_id().unwrap()
            }
        };
        self.set_id(id);
        for mut taskgroup in &mut self.taskgroups {
            taskgroup.contest = id;
            taskgroup.save(conn);
        }
    }
}

260
impl MedalConnection for Connection {
261 262 263 264 265 266
    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();

267 268
        let query = "SELECT name FROM migrations WHERE name = $1";
        self.exists(query, &[&name])
269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285
    }

    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<T: ToSql>(&self, key: T, keyname: &str) -> Option<SessionUser> {
    fn get_session(&self, key: &str) -> Option<SessionUser> {
286 287 288
        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_teacher, managed_by, oauth_provider, oauth_foreign_id
289 290
                     FROM session
                     WHERE session_token = $1";
291 292 293 294 295 296 297 298 299
        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),
300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319
                                                                             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_teacher: row.get(20),
                                                                             managed_by: row.get(21),

                                                                             oauth_provider: row.get(22),
                                                                             oauth_foreign_id: row.get(23) })
320
                          .ok()??;
321 322 323

        let duration = if session.permanent_login { Duration::days(90) } else { Duration::minutes(90) };
        let now = time::get_time();
324

325 326
        if let Some(last_activity) = session.last_activity {
            if now - last_activity < duration {
327 328 329 330
                let query = "UPDATE session
                             SET last_activity = $1
                             WHERE id = $2";
                self.execute(query, &[&now, &session.id]).unwrap();
331
                return Some(session);
332
            } else {
333 334 335 336
                // Session timed out
                // Should remove session token from session
                return None;
            }
337
        }
338 339 340
        // last_activity undefined
        // TODO: What should happen here?
        None
341 342
    }
    fn save_session(&self, session: SessionUser) {
343 344 345 346 347 348 349 350 351 352 353 354
        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,
355 356 357
                          is_teacher = $12,
                          permanent_login = $13
                      WHERE id = $14",
358 359 360 361 362 363 364 365 366 367
                     &[&session.username,
                       &session.password,
                       &session.salt,
                       &session.logincode,
                       &session.firstname,
                       &session.lastname,
                       &session.street,
                       &session.zip,
                       &session.city,
                       &session.grade,
368
                       &session.sex,
369
                       &session.is_teacher,
370
                       &session.permanent_login,
371 372 373 374
                       &session.id])
            .unwrap();
    }
    fn new_session(&self, session_token: &str) -> SessionUser {
375
        let csrf_token = helpers::make_csrf_token();
376 377

        let now = time::get_time();
378
        let query = "INSERT INTO session (session_token, csrf_token, last_activity, permanent_login, grade, sex,
379
                                          is_teacher)
380 381
                     VALUES ($1, $2, $3, $4, $5, $6, $7)";
        self.execute(query, &[&session_token, &csrf_token, &now, &false, &0, &None::<i32>, &false]).unwrap();
382

383 384
        let id = self.get_last_id().expect("Expected to get last row id");

385
        SessionUser::minimal(id, session_token.to_owned(), csrf_token)
386 387
    }
    fn get_session_or_new(&self, key: &str) -> SessionUser {
388 389 390
        let query = "UPDATE session
                     SET session_token = $1
                     WHERE session_token = $2";
391 392 393
        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?
394
                                                 self.execute(query, &[&Option::<String>::None, &key]).unwrap();
395 396
                                                 self.new_session(&key)
                                             })
397 398 399
    }

    fn get_user_by_id(&self, user_id: i32) -> Option<SessionUser> {
400
        let query = "SELECT session_token, csrf_token, last_login, last_activity, permanent_login, username, password,
401 402 403
                            salt, logincode, email, email_unconfirmed, email_confirmationcode, firstname, lastname,
                            street, zip, city, nation, grade, sex, is_teacher, managed_by, oauth_provider,
                            oauth_foreign_id
404 405
                     FROM session
                     WHERE id = $1";
406 407 408 409 410 411 412 413 414
        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),
415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434
                                                                   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_teacher: row.get(20),
                                                                   managed_by: row.get(21),

                                                                   oauth_provider: row.get(22),
                                                                   oauth_foreign_id: row.get(23) })
435
            .ok()?
436 437 438 439 440 441 442 443 444 445
    }

    fn get_user_and_group_by_id(&self, user_id: i32) -> Option<(SessionUser, Option<Group>)> {
        let session = self.get_user_by_id(user_id)?;

        let group_id = match session.managed_by {
            Some(id) => id,
            None => return Some((session, None)),
        };

446 447 448
        let query = "SELECT name, groupcode, tag, admin
                     FROM usergroup
                     WHERE id = $1";
449 450 451 452 453 454 455 456 457
        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))),
458 459 460 461 462 463
            _ => Some((session, None)),
        }
    }

    //TODO: use session
    fn login(&self, _session: Option<&str>, username: &str, password: &str) -> Result<String, ()> {
464 465 466 467
        let query = "SELECT id, password, salt
                     FROM session
                     WHERE username = $1";
        self.query_map_one(query, &[&username], |row| {
468 469 470 471
                let (id, password_hash, salt): (i32, Option<String>, Option<String>) =
                    (row.get(0), row.get(1), row.get(2));

                //password_hash ist das, was in der Datenbank steht
472
                if helpers::verify_password(&password,
473 474
                                            &salt.ok_or_else(|| println!("salt from database empty"))?,
                                            &password_hash.ok_or_else(|| println!("password from database empty"))?)
475 476 477 478
                {
                    // TODO: fail more pleasantly
                    // Login okay, update session now!

479 480
                    let session_token = helpers::make_session_token();
                    let csrf_token = helpers::make_csrf_token();
481 482
                    let now = time::get_time();

483 484 485
                    let query = "UPDATE session
                                 SET session_token = $1, csrf_token = $2, last_login = $3, last_activity = $3
                                 WHERE id = $4";
486
                    self.execute(query, &[&session_token, &csrf_token, &now, &id]).unwrap();
487 488 489 490 491

                    Ok(session_token)
                } else {
                    Err(())
                }
492 493 494
            })
            .map_err(|_| ())?
            .ok_or(())?
495 496 497 498
    }

    //TODO: use session
    fn login_with_code(&self, _session: Option<&str>, logincode: &str) -> Result<String, ()> {
499 500 501 502
        let query = "SELECT id
                     FROM session
                     WHERE logincode = $1";
        self.query_map_one(query, &[&logincode], |row| {
503 504 505
                // Login okay, update session now!
                let id: i32 = row.get(0);

506 507
                let session_token = helpers::make_session_token();
                let csrf_token = helpers::make_csrf_token();
508 509
                let now = time::get_time();

510 511 512
                let query = "UPDATE session
                             SET session_token = $1, csrf_token = $2, last_login = $3, last_activity = $3
                             WHERE id = $4";
513
                self.execute(query, &[&session_token, &csrf_token, &now, &id]).unwrap();
514

515
                session_token
516 517 518
            })
            .map_err(|_| ())?
            .ok_or(())
519 520 521
    }

    //TODO: use session
522 523
    fn login_foreign(&self, _session: Option<&str>, provider_id: &str, foreign_id: &str, is_teacher: bool,
                     firstname: &str, lastname: &str)
524 525
                     -> Result<String, ()>
    {
526 527
        let session_token = helpers::make_session_token();
        let csrf_token = helpers::make_csrf_token();
528 529
        let now = time::get_time();

530 531
        let query = "SELECT id
                     FROM session
532 533 534
                     WHERE oauth_foreign_id = $1
                           AND oauth_provider = $2";
        match self.query_map_one(query, &[&foreign_id, &provider_id], |row| -> i32 { row.get(0) }) {
535
            Ok(Some(id)) => {
536 537 538
                let query = "UPDATE session
                             SET session_token = $1, csrf_token = $2, last_login = $3, last_activity = $3
                             WHERE id = $4";
539
                self.execute(query, &[&session_token, &csrf_token, &now, &id]).unwrap();
540 541 542 543 544

                Ok(session_token)
            }
            // Add!
            _ => {
545
                let query = "INSERT INTO session (session_token, csrf_token, last_login, last_activity,
546
                                                  permanent_login, grade, sex, is_teacher, oauth_foreign_id,
547
                                                  oauth_provider, firstname, lastname)
548
                             VALUES ($1, $2, $3, $3, $4, $5, $6, $7, $8, $9, $10, $11)";
549 550 551 552 553
                self.execute(query,
                             &[&session_token,
                               &csrf_token,
                               &now,
                               &false,
554
                               &(if is_teacher { 255 } else { 0 }),
555
                               &None::<i32>,
556 557
                               &is_teacher,
                               &foreign_id,
558
                               &provider_id,
559 560 561
                               &firstname,
                               &lastname])
                    .unwrap();
562 563 564 565 566 567 568 569

                Ok(session_token)
            }
        }
    }

    //TODO: use session
    fn create_user_with_groupcode(&self, _session: Option<&str>, groupcode: &str) -> Result<String, ()> {
570 571 572
        let query = "SELECT id
                     FROM usergroup
                     WHERE groupcode = $1";
573 574
        let group_id =
            self.query_map_one(query, &[&groupcode], |row| -> i32 { row.get(0) }).map_err(|_| ())?.ok_or(())?;
575

576 577 578
        // Login okay, create session!
        let session_token = helpers::make_session_token();
        let csrf_token = helpers::make_csrf_token();
579
        let login_code = helpers::make_login_code(); // TODO: check for collisions
580
        let now = time::get_time();
581

582
        let query = "INSERT INTO session (session_token, csrf_token, last_login, last_activity, permanent_login,
583
                                          logincode, grade, sex, is_teacher, managed_by)
584
                     VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)";
585 586 587 588 589 590 591 592 593 594 595 596
        self.execute(query,
                     &[&session_token,
                       &csrf_token,
                       &now,
                       &now,
                       &false,
                       &login_code,
                       &0,
                       &None::<i32>,
                       &false,
                       &group_id])
            .unwrap();
597

598
        Ok(session_token)
599 600
    }

Robert Czechowski's avatar
rustfmt  
Robert Czechowski committed
601
    fn create_group_with_users(&self, mut group: Group) {
602 603 604 605 606 607
        // 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
Robert Czechowski's avatar
rustfmt  
Robert Czechowski committed
608

609
            let query = "INSERT INTO session (firstname, lastname, csrf_token, permanent_login, logincode, grade, sex,
610
                                              is_teacher, managed_by)
611
                         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)";
612 613 614 615 616 617 618
            self.execute(query,
                         &[&user.firstname,
                           &user.lastname,
                           &csrf_token,
                           &false,
                           &login_code,
                           &user.grade,
619
                           &None::<i32>,
620 621 622
                           &false,
                           &group.id])
                .unwrap();
623
        }
624 625
    }

626
    fn logout(&self, session: &str) {
627 628 629
        let query = "UPDATE session
                     SET session_token = NULL
                     WHERE session_token = $1";
630
        self.execute(query, &[&session]).unwrap();
631 632 633 634
    }

    fn load_submission(&self, session: &SessionUser, task: i32, subtask: Option<&str>) -> Option<Submission> {
        match subtask {
635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675
            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()?
            }
676 677 678 679 680 681 682 683 684 685 686 687 688
        }
    }
    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 {
689 690 691 692 693 694
        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";
695 696 697 698 699 700 701
        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
702 703 704
                         FROM submission
                         JOIN task ON task.id = submission.task
                         WHERE submission.id = $1";
705 706 707 708 709 710 711
                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?
            })
712 713 714 715
    }

    fn get_contest_groups_grades(&self, session_id: i32, contest_id: i32)
                                 -> (Vec<String>, Vec<(Group, Vec<(UserInfo, Vec<Grade>)>)>) {
716 717 718
        let query = "SELECT id, name
                     FROM taskgroup
                     WHERE contest = $1
719 720
                     AND active = $2
                     ORDER BY positionalnumber";
721
        let tasknames: Vec<(i32, String)> =
722
            self.query_map_many(query, &[&contest_id, &true], |row| (row.get(0), row.get(1))).unwrap();
723

724 725 726 727 728 729 730
        let mut taskindex: ::std::collections::BTreeMap<i32, usize> = ::std::collections::BTreeMap::new();

        let n_tasks = tasknames.len();
        for (index, (i, _)) in tasknames.iter().enumerate() {
            taskindex.insert(*i, index);
        }

731 732
        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,
733
                            student.firstname, student.lastname, student.grade AS sgrade
734 735 736 737 738 739
                     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
740
                     AND taskgroup.active = $3
741 742
                     ORDER BY usergroup.id, sgrade, student.lastname, student.firstname, student.id,
                              taskgroup.positionalnumber";
743
        let gradeinfo =
744
            self.query_map_many(query, &[&session_id, &contest_id, &true], |row| {
745 746 747 748 749 750 751 752 753 754 755
                    (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),
756
                                lastname: row.get(12),
Robert Czechowski's avatar
Robert Czechowski committed
757
                                grade: row.get(13) })
758 759 760
                })
                .unwrap();
        let mut gradeinfo_iter = gradeinfo.iter();
761 762

        if let Some(t /*Ok((grade, mut group, mut userinfo))*/) = gradeinfo_iter.next() {
763
            let (grade, mut group, mut userinfo) = t.clone();
764 765 766 767 768 769

            let mut grades: Vec<Grade> = vec![Default::default(); n_tasks];
            let mut users: Vec<(UserInfo, Vec<Grade>)> = Vec::new();
            let mut groups: Vec<(Group, Vec<(UserInfo, Vec<Grade>)>)> = Vec::new();

            let index = grade.taskgroup;
770
            grades[taskindex[&index]] = grade;
771 772

            for ggu in gradeinfo_iter {
773 774
                let (g, gr, ui) = ggu;
                if gr.id != group.id {
775 776
                    users.push((userinfo, grades));
                    userinfo = ui.clone();
777 778
                    grades = vec![Default::default(); n_tasks];

779 780
                    groups.push((group, users));
                    group = gr.clone();
781 782
                    users = Vec::new();
                } else if ui.id != userinfo.id {
783 784
                    users.push((userinfo, grades));
                    userinfo = ui.clone();
785
                    grades = vec![Default::default(); n_tasks];
786
                }
787
                let index = g.taskgroup;
788
                grades[taskindex[&index]] = *g;
789
            }
790 791
            users.push((userinfo, grades));
            groups.push((group, users));
792 793 794 795 796 797 798

            (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<Grade> {
799 800 801
        let query = "SELECT id, name
                     FROM taskgroup
                     WHERE contest = $1
802 803
                     AND active = $2
                     ORDER BY positionalnumber";
804
        let tasknames: Vec<(i32, String)> =
805
            self.query_map_many(query, &[&contest_id, &true], |row| (row.get(0), row.get(1))).unwrap();
806 807 808 809 810 811 812
        let mut taskindex: ::std::collections::BTreeMap<i32, usize> = ::std::collections::BTreeMap::new();

        let n_tasks = tasknames.len();
        for (index, (i, _)) in tasknames.iter().enumerate() {
            taskindex.insert(*i, index);
        }

813 814 815 816 817 818
        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
819 820
                     AND taskgroup.active = $3
                     ORDER BY taskgroup.positionalnumber";
821
        let gradeinfo =
822
            self.query_map_many(query, &[&session_token, &contest_id, &true], |row| Grade { taskgroup: row.get(0),
Robert Czechowski's avatar
Robert Czechowski committed
823 824 825
                                                                                            user: row.get(1),
                                                                                            grade: row.get(2),
                                                                                            validated: row.get(3) })
826 827
                .unwrap();
        let gradeinfo_iter = gradeinfo.iter();
828 829 830 831 832

        let mut grades: Vec<Grade> = vec![Default::default(); n_tasks];

        for g in gradeinfo_iter {
            let index = g.taskgroup;
833
            grades[taskindex[&index]] = *g;
834 835 836 837 838 839
        }

        grades
    }

    fn get_taskgroup_user_grade(&self, session_token: &str, taskgroup_id: i32) -> Grade {
840
        let query = "SELECT grade.taskgroup, grade.session, grade.grade, grade.validated
841 842 843 844
                     FROM grade
                     JOIN session ON session.id = grade.session
                     WHERE session.session_token = $1
                     AND grade.taskgroup = $2";
845 846 847 848 849 850
        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()
851 852 853
    }

    fn get_contest_list(&self) -> Vec<Contest> {
854
        let query = "SELECT id, location, filename, name, duration, public, start_date, end_date, min_grade, max_grade,
855
                            positionalnumber, requires_login, secret
856
                     FROM contest
857
                     ORDER BY positionalnumber";
858 859 860 861 862 863 864 865
        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),
866 867
                                                        min_grade: row.get(8),
                                                        max_grade: row.get(9),
868
                                                        positionalnumber: row.get(10),
869 870
                                                        requires_login: row.get(11),
                                                        secret: row.get(12),
871 872
                                                        taskgroups: Vec::new() })
            .unwrap()
873 874 875
    }

    fn get_contest_by_id(&self, contest_id: i32) -> Contest {
876 877
        let query = "SELECT location, filename, name, duration, public, start_date, end_date, min_grade, max_grade,
                            requires_login, secret
878 879
                     FROM contest
                     WHERE id = $1";
880 881 882 883 884 885 886 887
        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),
888 889
                                                                  min_grade: row.get(7),
                                                                  max_grade: row.get(8),
890
                                                                  positionalnumber: None,
891 892
                                                                  requires_login: row.get(9),
                                                                  secret: row.get(10),
893
                                                                  taskgroups: Vec::new() })
894 895 896 897 898
            .unwrap()
            .unwrap() // TODO: Should return Option?
    }

    fn get_contest_by_id_complete(&self, contest_id: i32) -> Contest {
899
        let query = "SELECT contest.location, contest.filename, contest.name, contest.duration, contest.public,
900 901 902
                            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
903 904 905 906
                     FROM contest
                     JOIN taskgroup ON contest.id = taskgroup.contest
                     JOIN task ON taskgroup.id = task.taskgroup
                     WHERE contest.id = $1
907
                     AND taskgroup.active = $2
908
                     ORDER BY taskgroup.positionalnumber";
909
        let taskgroupcontest =
910
            self.query_map_many(query, &[&contest_id, &true], |row| {
911 912 913 914 915 916 917 918
                    (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),
919 920
                               min_grade: row.get(7),
                               max_grade: row.get(8),
921
                               positionalnumber: None,
922 923
                               requires_login: row.get(9),
                               secret: row.get(10),
924
                               taskgroups: Vec::new() },
925
                     Taskgroup { id: Some(row.get(11)),
926
                                 contest: contest_id,
927
                                 name: row.get(12),
928
                                 active: true,
929 930
                                 positionalnumber: None,
                                 tasks: Vec::new() },
931
                     Task { id: Some(row.get(13)), taskgroup: row.get(11), location: row.get(14), stars: row.get(15) })
932 933 934
                })
                .unwrap();
        let mut taskgroupcontest_iter = taskgroupcontest.into_iter();
935 936 937 938

        let (mut contest, mut taskgroup, task) = taskgroupcontest_iter.next().unwrap();
        taskgroup.tasks.push(task);
        for tgc in taskgroupcontest_iter {
939 940 941 942
            let (_, tg, t) = tgc;
            if tg.id != taskgroup.id {
                contest.taskgroups.push(taskgroup);
                taskgroup = tg;
943
            }
944
            taskgroup.tasks.push(t);
945 946 947 948 949 950
        }
        contest.taskgroups.push(taskgroup);
        contest
    }

    fn get_contest_by_id_partial(&self, contest_id: i32) -> Contest {
951
        let query = "SELECT contest.location, contest.filename, contest.name, contest.duration, contest.public,
952 953
                            contest.start_date, contest.end_date, contest.min_grade, contest.max_grade,
                            contest.requires_login, contest_secret, taskgroup.id, taskgroup.name
954 955
                     FROM contest
                     JOIN taskgroup ON contest.id = taskgroup.contest
956 957 958
                     WHERE contest.id = $1
                     AND taskgroup.active = $2";
        let taskgroupcontest = self.query_map_many(query, &[&contest_id, &true], |row| {
959 960 961 962 963 964 965 966
                                       (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),
967 968
                                                  min_grade: row.get(7),
                                                  max_grade: row.get(8),
969
                                                  positionalnumber: None,
970 971
                                                  requires_login: row.get(9),
                                                  secret: row.get(10),
972
                                                  taskgroups: Vec::new() },
973
                                        Taskgroup { id: Some(row.get(11)),
974
                                                    contest: contest_id,
975
                                                    name: row.get(12),
976
                                                    active: true,
977 978 979 980
                                                    positionalnumber: None,
                                                    tasks: Vec::new() })
                                   })
                                   .unwrap();
981
        let mut taskgroupcontest_iter = taskgroupcontest.into_iter();
982 983 984 985

        let (mut contest, taskgroup) = taskgroupcontest_iter.next().unwrap();
        contest.taskgroups.push(taskgroup);
        for tgc in taskgroupcontest_iter {
986 987
            let (_, tg) = tgc;
            contest.taskgroups.push(tg);
988 989 990 991 992
        }
        contest
    }

    fn get_participation(&self, session: &str, contest_id: i32) -> Option<Participation> {
993 994 995 996 997
        let query = "SELECT session, start_date
                     FROM participation
                     JOIN session ON session.id = session
                     WHERE session.session_token = $1
                     AND contest = $2";
998 999 1000 1001
        self.query_map_one(query, &[&session, &contest_id], |row| Participation { contest: contest_id,
                                                                                  user: row.get(0),
                                                                                  start: row.get(1) })
            .ok()?
1002 1003
    }
    fn new_participation(&self, session: &str, contest_id: i32) -> Result<Participation, ()> {
1004 1005 1006 1007 1008
        let query = "SELECT session, start_date
                     FROM participation
                     JOIN session ON session.id = session
                     WHERE session.session_token = $1
                     AND contest = $2";
1009 1010
        match self.query_map_one(query, &[&session, &contest_id], |_| {}).map_err(|_| ())? {
            Some(()) => Err(()),
1011 1012 1013
            None => {
                let now = time::get_time();
                self.execute(
1014
                             "INSERT INTO participation (contest, session, start_date)
1015
                     SELECT $1, id, $2 FROM session WHERE session_token = $3",
1016 1017 1018
                             &[&contest_id, &now, &session],
                )
                    .unwrap();
1019 1020 1021 1022 1023 1024

                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 {
1025 1026 1027
        let query = "SELECT location, stars, taskgroup
                     FROM task
                     WHERE id = $1";
1028 1029 1030 1031
        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) })
1032 1033 1034 1035
            .unwrap()
            .unwrap()
    }
    fn get_task_by_id_complete(&self, task_id: i32) -> (Task, Taskgroup, Contest) {
1036 1037
        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,
1038 1039
                            contest.start_date, contest.end_date, contest.min_grade, contest.max_grade,
                            contest.requires_login, contest.secret
1040 1041 1042 1043
                     FROM contest
                     JOIN taskgroup ON taskgroup.contest = contest.id
                     JOIN task ON task.taskgroup = taskgroup.id
                     WHERE task.id = $1";
1044 1045
        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) },
1046
                 Taskgroup { id: Some(row.get(2)),
1047
                             contest: row.get(5),
1048
                             name: row.get(3),
1049
                             active: row.get(4),
1050 1051
                             positionalnumber: None,
                             tasks: Vec::new() },
1052 1053 1054 1055 1056 1057 1058 1059 1060 1061
                 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),
1062
                           positionalnumber: None,
1063 1064
                           requires_login: row.get(15),
                           secret: row.get(16),
1065 1066 1067 1068
                           taskgroups: Vec::new() })
            })
            .unwrap()
            .unwrap()
1069 1070 1071 1072
    }

    fn get_submission_to_validate(&self, tasklocation: &str, subtask: Option<&str>) -> i32 {
        match subtask {
1073
            Some(st) => {
1074 1075 1076 1077 1078 1079 1080
                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";
1081 1082 1083
                self.query_map_one(query, &[&tasklocation, &st], |row| row.get(0)).unwrap().unwrap()
            }
            None => {
1084 1085 1086 1087 1088 1089
                let query = "SELECT id
                             FROM submission
                             JOIN task ON submission.task = task.id
                             WHERE task.location = $1
                             AND needs_validation = 1
                             LIMIT 1";
1090 1091
                self.query_map_one(query, &[&tasklocation], |row| row.get(0)).unwrap().unwrap()
            }
1092 1093 1094 1095
        }
    }

    fn find_next_submission_to_validate(&self, userid: i32, taskgroupid: i32) {
1096 1097 1098 1099 1100 1101 1102
        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";
1103 1104
        let (id, validated): (i32, bool) =
            self.query_map_one(query, &[&taskgroupid, &userid], |row| (row.get(0), row.get(1))).unwrap().unwrap();
1105
        if !validated {
1106 1107 1108
            let query = "UPDATE submission
                         SET needs_validation = 1
                         WHERE id = $1";
1109
            self.execute(query, &[&id]).unwrap();
1110 1111 1112 1113 1114 1115
        }
    }

    fn add_group(&self, group: &mut Group) { group.save(self); }

    fn get_groups(&self, session_id: i32) -> Vec<Group> {
1116 1117 1118
        let query = "SELECT id, name, groupcode, tag
                     FROM usergroup
                     WHERE admin = $1";
1119 1120 1121 1122 1123 1124
        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() })
1125 1126 1127 1128 1129 1130
            .unwrap()
    }
    fn get_groups_complete(&self, _session_id: i32) -> Vec<Group> {