db_conn.base.rs 81.2 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
/*  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   *
\*  <http://www.gnu.org/licenses/>.                                                                                  */

15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
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)
45
            .map(|id| {
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
                self.set_id(id);
            })
            .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)
80
                .map(|id| {
81
82
83
84
85
86
87
88
                    self.set_id(id);
                })
                .unwrap_or(()); // Err means no entry yet and is expected result
        }

        let id = match self.get_id() {
            Some(id) => {
                let query = "UPDATE taskgroup
89
90
91
                             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();
92
93
94
                id
            }
            None => {
95
96
97
                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();
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
                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)
117
            .map(|id| {
118
119
120
121
122
123
124
                self.set_id(id);
            })
            .unwrap_or(()); // Err means no entry yet and is expected result

        let id = match self.get_id() {
            Some(id) => {
                let query = "UPDATE contest
125
126
                             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,
127
                                 requires_login = $12, secret = $13, message = $14
128
                             WHERE id = $1";
129
                conn.execute(query,
130
131
                             &[&id,
                               &self.location,
132
133
134
135
136
137
                               &self.filename,
                               &self.name,
                               &self.duration,
                               &self.public,
                               &self.start,
                               &self.end,
138
139
                               &self.min_grade,
                               &self.max_grade,
140
                               &self.positionalnumber,
141
                               &self.requires_login,
142
143
                               &self.secret,
                               &self.message])
144
145
146
147
                    .unwrap();
                id
            }
            None => {
148
                let query = "INSERT INTO contest (location, filename, name, duration, public, start_date, end_date,
149
150
                                                  min_grade, max_grade, positionalnumber, requires_login, secret, message)
                             VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)";
151
152
153
154
155
156
157
                conn.execute(query,
                             &[&self.location,
                               &self.filename,
                               &self.name,
                               &self.duration,
                               &self.public,
                               &self.start,
158
159
                               &self.end,
                               &self.min_grade,
160
                               &self.max_grade,
161
162
                               &self.positionalnumber,
                               &self.requires_login,
163
164
                               &self.secret,
                               &self.message])
165
166
167
168
169
170
171
172
173
174
175
176
177
                    .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 {
178
179
    fn reconnect(config: &config::Config) -> Self { Self::reconnect_concrete(config) }

180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
    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<T: ToSql>(&self, key: T, keyname: &str) -> Option<SessionUser> {
    fn get_session(&self, key: &str) -> Option<SessionUser> {
205
206
        let query = "SELECT id, csrf_token, last_login, last_activity, account_created, username, password,
                            salt, logincode, email, email_unconfirmed, email_confirmationcode, firstname, lastname, street,
207
                            zip, city, nation, grade, sex, is_admin, is_teacher, managed_by, oauth_provider, oauth_foreign_id
208
209
210
211
212
213
214
                     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),
215
                                                                             account_created: row.get(4),
216
217
218

                                                                             username: row.get(5),
                                                                             password: row.get(6),
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
                                                                             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),

234
235
236
                                                                             is_admin: row.get(20),
                                                                             is_teacher: row.get(21),
                                                                             managed_by: row.get(22),
237

238
239
                                                                             oauth_provider: row.get(23),
                                                                             oauth_foreign_id: row.get(24) })
240
241
                          .ok()??;

242
        let duration = Duration::hours(12);
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
        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) {
263
264
265
266
267
268
269
270
271
272
273
274
        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,
275
276
                          is_admin = $12,
                          is_teacher = $13,
277
                          managed_by = $14,
278
279
280
                          email = $15,
                          email_unconfirmed = $16
                      WHERE id = $17",
281
282
283
284
285
286
287
288
289
290
                     &[&session.username,
                       &session.password,
                       &session.salt,
                       &session.logincode,
                       &session.firstname,
                       &session.lastname,
                       &session.street,
                       &session.zip,
                       &session.city,
                       &session.grade,
291
                       &session.sex,
292
                       &session.is_admin,
293
                       &session.is_teacher,
294
                       &session.managed_by,
295
296
                       &session.email,
                       &session.email_unconfirmed,
297
298
299
300
301
302
303
                       &session.id])
            .unwrap();
    }
    fn new_session(&self, session_token: &str) -> SessionUser {
        let csrf_token = helpers::make_csrf_token();

        let now = time::get_time();
304
        let query = "INSERT INTO session (session_token, csrf_token, last_activity, account_created, grade, sex,
305
                                          is_teacher)
306
                     VALUES ($1, $2, $3, $4, $5, $6, $7)";
307
        self.execute(query, &[&session_token, &csrf_token, &now, &None::<time::Timespec>, &0, &None::<i32>, &false]).unwrap();
308
309
310
311
312

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

        SessionUser::minimal(id, session_token.to_owned(), csrf_token)
    }
313
314
315
316
317
318
    fn session_set_activity_dates(&self, session_id: i32, account_created: Option<time::Timespec>, last_login: Option<time::Timespec>, last_activity: Option<time::Timespec>) {
        let query = "UPDATE session
                     SET account_created = $2, last_login = $3, last_activity = $4
                     WHERE id = $1";
        self.execute(query, &[&session_id, &account_created, &last_login, &last_activity]).unwrap();
    }
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
    fn get_session_or_new(&self, key: &str) -> Result<SessionUser, ()> {
        fn disable_old_session_and_create_new(conn: &Connection, key: &str) -> Result<SessionUser, ()> {
            let query = "UPDATE session
                         SET session_token = $1
                         WHERE session_token = $2";
            // TODO: Should a new session key be generated every time?
            conn.execute(query, &[&Option::<String>::None, &key]).map_err(|_| ())?;
            Ok(conn.new_session(&key))
        }

        if let Some(session) = self.get_session(&key).ensure_alive() {
            Ok(session)
        } else {
            disable_old_session_and_create_new(self, key)
        }
334
335
336
    }

    fn get_user_by_id(&self, user_id: i32) -> Option<SessionUser> {
337
        let query = "SELECT session_token, csrf_token, last_login, last_activity, account_created, username, password,
338
                            salt, logincode, email, email_unconfirmed, email_confirmationcode, firstname, lastname,
339
                            street, zip, city, nation, grade, sex, is_admin, is_teacher, managed_by, oauth_provider,
340
                            oauth_foreign_id
341
342
343
344
345
346
347
                     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),
348
                                                                   account_created: row.get(4),
349
350
351

                                                                   username: row.get(5),
                                                                   password: row.get(6),
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
                                                                   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),

367
368
369
                                                                   is_admin: row.get(20),
                                                                   is_teacher: row.get(21),
                                                                   managed_by: row.get(22),
370

371
372
                                                                   oauth_provider: row.get(23),
                                                                   oauth_foreign_id: row.get(24) })
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
            .ok()?
    }

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

        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<String, ()> {
        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<String>, Option<String>) =
                    (row.get(0), row.get(1), row.get(2));

                //password_hash ist das, was in der Datenbank steht
                if helpers::verify_password(&password,
411
412
                                            &salt.ok_or_else(|| println!("salt from database empty"))?,
                                            &password_hash.ok_or_else(|| println!("password from database empty"))?)
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
                {
                    // 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<String, ()> {
437
438
439
440
        if logincode == "" {
            return Err(());
        }

441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
        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
464
465
    fn login_foreign(&self, _session: Option<&str>, provider_id: &str, foreign_id: &str,
                     (is_teacher, is_admin, firstname, lastname, sex): (bool, bool, &str, &str, Option<i32>))
466
                     -> Result<(String, Option<time::Timespec>), ()>
467
468
469
470
471
    {
        let session_token = helpers::make_session_token();
        let csrf_token = helpers::make_csrf_token();
        let now = time::get_time();

472
        let query = "SELECT id, last_activity
473
                     FROM session
474
475
                     WHERE oauth_foreign_id = $1
                           AND oauth_provider = $2";
476
477
478
479
        match self.query_map_one(query, &[&foreign_id, &provider_id], |row| -> (i32, time::Timespec) {
                      (row.get(0), row.get(1))
                  }) {
            Ok(Some((id, last_activity))) => {
480
                let query = "UPDATE session
481
                             SET session_token = $1, csrf_token = $2, last_login = $3, last_activity = $3,
482
483
                                 is_teacher = $4, is_admin = $5,  firstname = $6, lastname = $7, sex = $8
                             WHERE id = $9";
484
485
486
487
488
489
490
491
492
493
494
                self.execute(query,
                             &[&session_token,
                               &csrf_token,
                               &now,
                               &is_teacher,
                               &is_admin,
                               &firstname,
                               &lastname,
                               &sex,
                               &id])
                    .unwrap();
495

496
                Ok((session_token, Some(last_activity)))
497
498
499
500
            }
            // Add!
            _ => {
                let query = "INSERT INTO session (session_token, csrf_token, last_login, last_activity,
501
                                                  account_created, grade, sex, is_teacher, is_admin, oauth_foreign_id,
502
                                                  oauth_provider, firstname, lastname)
503
                             VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)";
504
505
506
507
                self.execute(query,
                             &[&session_token,
                               &csrf_token,
                               &now,
508
509
                               &now,
                               &now,
510
                               &(if is_teacher { 255 } else { 0 }),
511
                               &sex,
512
                               &is_teacher,
513
                               &is_admin,
514
                               &foreign_id,
515
                               &provider_id,
516
517
518
519
                               &firstname,
                               &lastname])
                    .unwrap();

520
                Ok((session_token, None))
521
522
523
524
525
526
            }
        }
    }

    //TODO: use session
    fn create_user_with_groupcode(&self, _session: Option<&str>, groupcode: &str) -> Result<String, ()> {
527
528
529
530
        if groupcode == "" {
            return Err(());
        }

531
532
533
534
535
536
537
538
539
540
541
542
        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();

543
        let query = "INSERT INTO session (session_token, csrf_token, last_login, last_activity, account_created,
544
                                          logincode, grade, sex, is_teacher, managed_by)
545
                     VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)";
546
547
548
549
550
        self.execute(query,
                     &[&session_token,
                       &csrf_token,
                       &now,
                       &now,
551
                       &now,
552
553
554
555
556
557
                       &login_code,
                       &0,
                       &None::<i32>,
                       &false,
                       &group_id])
            .unwrap();
558
559
560
561
562
563
564

        Ok(session_token)
    }

    fn create_group_with_users(&self, mut group: Group) {
        // Generate group ID:
        group.save(self);
565

566
        let now = time::get_time();
567
568
569
570
571

        for user in group.members {
            let csrf_token = helpers::make_csrf_token();
            let login_code = helpers::make_login_code(); // TODO: check for collisions

572
            let query = "INSERT INTO session (firstname, lastname, csrf_token, account_created, logincode, grade, sex,
573
                                              is_teacher, managed_by)
574
                         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)";
575
576
577
578
            self.execute(query,
                         &[&user.firstname,
                           &user.lastname,
                           &csrf_token,
579
                           &now,
580
581
                           &login_code,
                           &user.grade,
582
                           &None::<i32>,
583
584
585
586
587
588
589
590
591
592
593
594
595
                           &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();
    }

Robert Czechowski's avatar
Robert Czechowski committed
596
597
    fn signup(&self, session_token: &str, username: &str, email: &str, password_hash: String, salt: &str)
              -> SignupResult {
598
        let mut session_user = self.get_session_or_new(&session_token).unwrap();
599
600

        if session_user.is_logged_in() {
Robert Czechowski's avatar
Robert Czechowski committed
601
            return SignupResult::UserLoggedIn;
602
603
604
        }

        if let Ok(None) = self.query_map_one("SELECT username FROM session WHERE username = $1",
Robert Czechowski's avatar
Robert Czechowski committed
605
606
607
608
                                             &[&username],
                                             |row| -> Option<String> { row.get(0) })
        {
        } else {
609
            //This username already exists!
Robert Czechowski's avatar
Robert Czechowski committed
610
            return SignupResult::UsernameTaken;
611
612
        }
        if let Ok(None) = self.query_map_one("SELECT email, email_unconfirmed FROM session WHERE email = $1 OR email_unconfirmed = $1",
Robert Czechowski's avatar
Robert Czechowski committed
613
614
615
616
                                             &[&email],
                                             |row| -> (Option<String>, Option<String>) { (row.get(0), row.get(1)) })
        {
        } else {
617
            //This email already exists!
Robert Czechowski's avatar
Robert Czechowski committed
618
            return SignupResult::EmailTaken;
619
620
621
622
623
624
625
626
627
628
629
        }

        session_user.username = Some(username.to_string());
        session_user.email_unconfirmed = Some(email.to_string());
        session_user.password = Some(password_hash);
        session_user.salt = Some(salt.to_string());

        self.save_session(session_user);
        SignupResult::SignedUp
    }

630
631
632
633
634
635
636
637
638
639
    fn load_submission(&self, session: &SessionUser, task: i32, subtask: Option<&str>) -> Option<Submission> {
        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)),
Robert Czechowski's avatar
Robert Czechowski committed
640
                                                                                    task,
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
                                                                                    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)),
Robert Czechowski's avatar
Robert Czechowski committed
661
                                     task,
662
663
664
665
666
667
668
669
670
671
672
673
674
                                     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()?
            }
        }
    }
675
676
677
678
679
680
681
682
    fn get_all_submissions(&self, session_id: i32, task: i32, subtask: Option<&str>) -> Vec<Submission> {
        match subtask {
            None => {
                let query = "SELECT id, grade, validated, nonvalidated_grade, value, date, needs_validation
                             FROM submission
                             WHERE task = $1
                             AND session = $2";
                self.query_map_many(query, &[&task, &session_id], |row| Submission { id: Some(row.get(0)),
Robert Czechowski's avatar
Robert Czechowski committed
683
                                                                                     task,
684
685
686
687
688
689
690
                                                                                     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),
691
692
693
                                                                                     needs_validation: row.get(6) })
                    .unwrap()
            }
694
            _ => unimplemented!(),
695
696
        }
    }
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
    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<String>, Vec<(Group, Vec<(UserInfo, Vec<Grade>)>)>) {
        let query = "SELECT id, name
                     FROM taskgroup
                     WHERE contest = $1
738
739
                     AND active = $2
                     ORDER BY positionalnumber";
740
        let tasknames: Vec<(i32, String)> =
741
            self.query_map_many(query, &[&contest_id, &true], |row| (row.get(0), row.get(1))).unwrap();
742
743
744
745
746
747
748
749
750
751

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

        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,
752
                            student.firstname, student.lastname, student.grade AS sgrade
753
754
755
756
757
758
                     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
759
                     AND taskgroup.active = $3
760
761
                     ORDER BY usergroup.id, sgrade, student.lastname, student.firstname, student.id,
                              taskgroup.positionalnumber";
762
        let gradeinfo =
763
            self.query_map_many(query, &[&session_id, &contest_id, &true], |row| {
764
765
766
767
768
769
770
771
772
773
774
                    (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),
775
                                lastname: row.get(12),
Robert Czechowski's avatar
Robert Czechowski committed
776
                                grade: row.get(13) })
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
                })
                .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<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;
            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<Grade> {
        let query = "SELECT id, name
                     FROM taskgroup
                     WHERE contest = $1
821
822
                     AND active = $2
                     ORDER BY positionalnumber";
823
        let tasknames: Vec<(i32, String)> =
824
            self.query_map_many(query, &[&contest_id, &true], |row| (row.get(0), row.get(1))).unwrap();
825
826
827
828
829
830
831
832
833
834
835
836
837
        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);
        }

        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
838
839
                     AND taskgroup.active = $3
                     ORDER BY taskgroup.positionalnumber";
840
        let gradeinfo =
841
            self.query_map_many(query, &[&session_token, &contest_id, &true], |row| Grade { taskgroup: row.get(0),
Robert Czechowski's avatar
Robert Czechowski committed
842
843
844
                                                                                            user: row.get(1),
                                                                                            grade: row.get(2),
                                                                                            validated: row.get(3) })
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
                .unwrap();
        let gradeinfo_iter = gradeinfo.iter();

        let mut grades: Vec<Grade> = 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()
    }

872
    /* Warning: This function makes no use of rusts typeb safety. Handle with care when changeing */
873
874
    fn export_contest_results_to_file(&self, contest_id: i32, taskgroups: &[(i32, String)], filename: &str) {
        use std::fs::OpenOptions;
875
        let file = OpenOptions::new().write(true).create(true).truncate(true).open(filename).unwrap();
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
        let mut headers = vec!["id",
                               "username",
                               "logincode",
                               "oauth_foreign_id",
                               "oauth_provider",
                               "firstname",
                               "lastname",
                               "grade",
                               "sex",
                               "is_teacher",
                               "group_id",
                               "group_name",
                               "group_tag",
                               "teacher_id",
                               "teacher_firstname",
                               "teacher_lastname",
                               "teacher_oauth_foreign_id",
893
			       "teacher_oauth_school_id",
894
895
896
                               "teacher_oauth_provider",
                               "contest_id",
                               "start_date"];
897
898
899

        let mut select_part = String::new();
        let mut join_part = String::new();
900
901
902

        let mut join_params = gen_tosql_vector();

903
        join_params.push(&contest_id);
904
905

        for (n, (id, name)) in taskgroups.iter().enumerate() {
906
907
908
909
910
            select_part.push_str(&format!(",\n g{}.grade ", n));
            join_part.push_str(&format!("\n LEFT JOIN grade AS g{} ON session.id = g{}.session AND g{}.taskgroup = ${} ", n, n, n, n + 2));
            join_params.push(id);
            headers.push(&name);
        }
911

912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
        let query = format!("SELECT session.id,
                                    session.username,
                                    session.logincode,
                                    session.oauth_foreign_id,
                                    session.oauth_provider,
                                    session.firstname,
                                    session.lastname,
                                    session.grade,
                                    session.sex,
                                    session.is_teacher,
                                    session.managed_by,
                                    usergroup.name,
                                    usergroup.tag,
                                    teacher.id,
                                    teacher.firstname,
                                    teacher.lastname,
                                    teacher.oauth_foreign_id,
                                    teacher.oauth_provider,
                                    participation.contest,
                                    participation.start_date
                                    {}
                             FROM participation
                             JOIN session ON participation.session = session.id
                             {}
                             LEFT JOIN usergroup ON session.managed_by = usergroup.id
                             LEFT JOIN session AS teacher ON usergroup.admin = teacher.id
938
939
                             WHERE participation.contest = $1",
                            select_part, join_part);
940
941

        use csv::Writer;
942
        let mut wtr = Writer::from_writer(file);
943
944
        wtr.serialize(&headers).unwrap();
        wtr.flush().unwrap();
945

946
947
        let file = wtr.into_inner().unwrap();
        let mut wtr = Writer::from_writer(file);
948
949
950
951
952
953

        self.query_map_many(&query, join_params.as_slice(), |row| {
                let mut points = Vec::new();
                for i in 20..20 + taskgroups.len() {
                    points.push(row.get::<_, Option<i32>>(i));
                }
954
955
956
957
958
959
960
961
962
963
964

	        let teacher_oauth_and_school_id = row.get::<_, Option<String>>(15);
		let (teacher_oauth_id, teacher_school_id) = if let Some(toasi) = teacher_oauth_and_school_id {
                    let mut v = toasi.split('/');
                    let oid: Option<String> = v.next().map(|s| s.to_owned());
                    let sid: Option<String> = v.next().map(|s| s.to_owned());
                    (oid, sid)
                } else {
                    (None, None)
                };

965
966
                // Serialized as several tuples because Serde only supports tuples up to a certain length
                // (16 according to https://docs.serde.rs/serde/trait.Deserialize.html)
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
                wtr.serialize(((row.get::<_, i32>(0),
                                row.get::<_, Option<String>>(1),
                                row.get::<_, Option<String>>(2),
                                row.get::<_, Option<String>>(3),
                                row.get::<_, Option<String>>(4),
                                row.get::<_, Option<String>>(5),
                                row.get::<_, Option<String>>(6),
                                row.get::<_, i32>(7),
                                row.get::<_, Option<i32>>(8),
                                row.get::<_, bool>(9)),
                               (row.get::<_, Option<i32>>(10),
                                row.get::<_, Option<String>>(11),
                                row.get::<_, Option<String>>(12),
                                row.get::<_, Option<i32>>(13),
                                row.get::<_, Option<String>>(14),
                                row.get::<_, Option<String>>(15),
983
984
				teacher_oauth_id,
				teacher_school_id,
985
986
                                row.get::<_, Option<String>>(17)),
                               row.get::<_, Option<i32>>(18),
987
988
                               row.get::<_, Option<time::Timespec>>(19)
                                  .map(|ts| self::time::strftime("%FT%T%z", &time::at(ts)).unwrap()),
989
990
991
992
                               points))
                   .unwrap();
            })
            .unwrap();
993
        wtr.flush().unwrap();
994
995
    }

996
    fn get_contest_list(&self) -> Vec<Contest> {
997
        let query = "SELECT id, location, filename, name, duration, public, start_date, end_date, min_grade, max_grade,
998
                            positionalnumber, requires_login, secret, message
999
                     FROM contest
1000
                     ORDER BY positionalnumber";
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
        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),
1011
                                                        positionalnumber: row.get(10),
1012
1013
                                                        requires_login: row.get(11),
                                                        secret: row.get(12),
1014
                                                        message: row.get(13),
1015
1016
1017
1018
1019
                                                        taskgroups: Vec::new() })
            .unwrap()
    }

    fn get_contest_by_id(&self, contest_id: i32) -> Contest {
1020
        let query = "SELECT location, filename, name, duration, public, start_date, end_date, min_grade, max_grade,
1021
                            requires_login, secret, message
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
                     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),
1034
                                                                  positionalnumber: None,
1035
1036
                                                                  requires_login: row.get(9),
                                                                  secret: row.get(10),
1037
                                                                  message: row.get(11),
1038
1039
1040
1041
1042
1043
1044
                                                                  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,
1045
                            contest.start_date, contest.end_date, contest.min_grade, contest.max_grade,
1046
1047
1048
                            contest.requires_login, contest.secret, contest.message,
                            taskgroup.id, taskgroup.name,
                            task.id, task.location, task.stars
1049
1050
1051
1052
                     FROM contest
                     JOIN taskgroup ON contest.id = taskgroup.contest
                     JOIN task ON taskgroup.id = task.taskgroup
                     WHERE contest.id = $1
1053
                     AND taskgroup.active = $2
1054
1055
                     ORDER BY taskgroup.positionalnumber";
        let taskgroupcontest =
1056
            self.query_map_many(query, &[&contest_id, &true], |row| {
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
                    (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),
1067
                               positionalnumber: None,
1068
1069
                               requires_login: row.get(9),
                               secret: row.get(10),
1070
                               message: row.get(11),
1071
                               taskgroups: Vec::new() },
1072
                     Taskgroup { id: Some(row.get(12)),
1073
                                 contest: contest_id,
1074
                                 name: row.get(13),
1075
                                 active: true,
1076
1077
                                 positionalnumber: None,
                                 tasks: Vec::new() },
1078
                     Task { id: Some(row.get(14)), taskgroup: row.get(12), location: row.get(15), stars: row.get(16) })
1079
1080
1081
1082
                })
                .unwrap();
        let mut taskgroupcontest_iter = taskgroupcontest.into_iter();

1083
1084
1085
1086
1087
1088
1089
1090
1091
        if let Some((mut contest, mut taskgroup, task)) = taskgroupcontest_iter.next() {
            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);
1092
            }
1093
1094
1095
1096
1097
1098
            contest.taskgroups.push(taskgroup);
            contest
        } else {
            // If the contest has no tasks, we fall back to the function, that does not try to gather the task
            // information
            self.get_contest_by_id(contest_id)
1099
1100
1101
1102
1103
        }
    }

    fn get_contest_by_id_partial(&self, contest_id: i32) -> Contest {
        let query = "SELECT contest.location, contest.filename, contest.name, contest.duration, contest.public,
1104
                            contest.start_date, contest.end_date, contest.min_grade, contest.max_grade,
1105
1106
                            contest.requires_login, contest.secret, contest.message,
                            taskgroup.id, taskgroup.name
1107
1108
                     FROM contest
                     JOIN taskgroup ON contest.id = taskgroup.contest
1109
1110
1111
                     WHERE contest.id = $1
                     AND taskgroup.active = $2";
        let taskgroupcontest = self.query_map_many(query, &[&contest_id, &true], |row| {
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
                                       (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),
1122
                                                  positionalnumber: None,
1123
1124
                                                  requires_login: row.get(9),
                                                  secret: row.get(10),
1125
                                                  message: row.get(11),
1126
                                                  taskgroups: Vec::new() },
1127
                                        Taskgroup { id: Some(row.get(12)),
1128
                                                    contest: contest_id,
1129
                                                    name: row.get(13),
1130
                                                    active: true,
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
                                                    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
    }

1146
1147
1148
1149
1150
1151
    fn get_participation(&self, session_id: i32, contest_id: i32) -> Option<Participation> {
        let query = "SELECT start_date
                     FROM participation
                     WHERE session = $1
                     AND contest = $2";
        self.query_map_one(query, &[&session_id, &contest_id], |row| Participation { contest: contest_id,
1152
1153
                                                                                     user: session_id,
                                                                                     start: row.get(0) })
1154
1155
1156
1157
            .ok()?
    }

    fn get_own_participation(&self, session: &str, contest_id: i32) -> Option<Participation> {
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
        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()?
    }
1168
1169
1170

    fn get_all_participations_complete(&self, session_id: i32) -> Vec<(Participation, Contest)> {
        let query = "SELECT participation.start_date, contest.id, location, filename, name, duration, public,
1171
                            contest.start_date, end_date, min_grade, max_grade, requires_login, secret, message
1172
1173
1174
1175
                     FROM participation
                     JOIN contest ON participation.contest = contest.id
                     WHERE participation.session = $1";
        self.query_map_many(query, &[&session_id], |row| {
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
                (Participation { contest: row.get(1), user: session_id, start: row.get(0) },
                 Contest { id: Some(row.get(1)),
                           location: row.get(2),
                           filename: row.get(3),
                           name: row.get(4),
                           duration: row.get(5),
                           public: row.get(6),
                           start: row.get(7),
                           end: row.get(8),
                           min_grade: row.get(9),
                           max_grade: row.get(10),
                           positionalnumber: None,
                           requires_login: row.get(11),
                           secret: row.get(12),
1190
                           message: row.get(13),
1191
1192
1193
                           taskgroups: Vec::new() })
            })
            .unwrap()
1194
1195
    }

1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
    fn new_participation(&self, session: &str, contest_id: i32) -> Result<Participation, ()> {
        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();

1213
                Ok(self.get_own_participation(session, contest_id).unwrap()) // TODO: This errors if not logged in …
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
            }
        }
    }
    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) {
1229
1230
        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,
1231
                            contest.start_date, contest.end_date, contest.min_grade, contest.max_grade,
1232
                            contest.requires_login, contest.secret, contest.message
1233
1234
1235
1236
1237
1238
1239
                     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)),
1240
                             contest: row.get(5),
1241
                             name: row.get(3),
1242
                             active: row.get(4),
1243
1244
                             positionalnumber: None,
                             tasks: Vec::new() },
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
                 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),
1255
                           positionalnumber: None,
1256
1257
                           requires_login: row.get(15),
                           secret: row.get(16),
1258
                           message: row.get(17),
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
                           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<Group> {
        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<Group> {
        unimplemented!();
    }
    fn get_group_complete(&self, group_id: i32) -> Option<Group> {
        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

1337
        let query = "SELECT id, session_token, csrf_token, last_login, last_activity, account_created, username,
1338
                            password, logincode, email, email_unconfirmed, email_confirmationcode, firstname, lastname,
1339
1340
                            street, zip, city, nation, grade, sex, is_admin, is_teacher, oauth_provider,
                            oauth_foreign_id, salt
1341
1342
1343
1344
1345
1346
1347
                     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),
1348
                                                                                     account_created: row.get(5),
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365

                                                                                     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),
1366
                                                                                     sex: row.get(19),
1367

1368
1369
                                                                                     is_admin: row.get(20),
                                                                                     is_teacher: row.get(21),
1370
1371
                                                                                     managed_by: Some(group_id),

1372
1373
                                                                                     oauth_provider: row.get(22),
                                                                                     oauth_foreign_id: row.get(23) })
1374
1375
1376
                            .unwrap();
        Some(group)
    }