db_conn_sqlite.rs 44.4 KB
Newer Older
1
2
#![cfg(feature = "rusqlite")]

Robert Czechowski's avatar
Robert Czechowski committed
3
extern crate rusqlite;
4

5
6
7
use rusqlite::Connection;
use time;
use time::Duration;
8
9
10

use db_conn::{MedalConnection, MedalObject};
use db_objects::*;
11
use helpers;
Robert Czechowski's avatar
Robert Czechowski committed
12

13
trait Queryable {
Robert Czechowski's avatar
rustfmt    
Robert Czechowski committed
14
15
    fn query_map_one<T, F>(&self, sql: &str, params: &[&dyn rusqlite::types::ToSql], f: F)
                           -> rusqlite::Result<Option<T>>
16
17
        where F: FnOnce(&rusqlite::Row) -> T;
}
18

19
impl Queryable for Connection {
Robert Czechowski's avatar
rustfmt    
Robert Czechowski committed
20
21
    fn query_map_one<T, F>(&self, sql: &str, params: &[&dyn rusqlite::types::ToSql], f: F)
                           -> rusqlite::Result<Option<T>>
22
23
24
25
26
27
28
29
30
31
32
33
        where F: FnOnce(&rusqlite::Row) -> T {
        let mut stmt = self.prepare(sql)?;
        let mut rows = stmt.query(params)?;
        match rows.next() {
            None => Ok(None),
            Some(Err(e)) => Err(e),
            Some(Ok(row)) => Ok(Some(f(&row))),
        }
    }
}

impl MedalConnection for Connection {
Robert Czechowski's avatar
Robert Czechowski committed
34
    fn dbtype(&self) -> &'static str { "sqlite" }
35

Robert Czechowski's avatar
Robert Czechowski committed
36
    fn migration_already_applied(&self, name: &str) -> bool {
37
38
        let create_string = "CREATE TABLE IF NOT EXISTS migrations (name TEXT PRIMARY KEY);";
        self.execute(create_string, &[]).unwrap();
39

40
41
42
        let mut stmt = self.prepare("SELECT name FROM migrations WHERE name = ?1").unwrap();
        stmt.exists(&[&name]).unwrap()
    }
43
44

    fn apply_migration(&mut self, name: &str, contents: &str) {
45
        print!("Applying migration `{}` … ", name);
46

47
        let tx = self.transaction().unwrap();
48

49
        tx.execute_batch(&contents).unwrap();
50
        tx.execute("INSERT INTO migrations (name) VALUES (?1)", &[&name]).unwrap();
51

52
        tx.commit().unwrap();
53

54
55
56
        println!("OK.");
    }

57
    // fn get_session<T: ToSql>(&self, key: T, keyname: &str) -> Option<SessionUser> {
58
    fn get_session(&self, key: &str) -> Option<SessionUser> {
59
60
61
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
        let query = "SELECT id, csrf_token, last_login, last_activity, permanent_login, username, password, logincode, email, email_unconfirmed, email_confirmationcode, firstname, lastname, street, zip, city, nation, grade, is_teacher, managed_by, oauth_provider, oauth_foreign_id, salt FROM session_user WHERE session_token = ?1";
        let session = self.query_map_one(query, &[&key], |row| SessionUser { id: row.get(0),
                                                                             session_token: Some(key.to_string()),
                                                                             csrf_token: row.get(1),
                                                                             last_login: row.get(2),
                                                                             last_activity: row.get(3),
                                                                             permanent_login: row.get(4),

                                                                             username: row.get(5),
                                                                             password: row.get(6),
                                                                             salt: row.get(22),
                                                                             logincode: row.get(7),
                                                                             email: row.get(8),
                                                                             email_unconfirmed: row.get(9),
                                                                             email_confirmationcode: row.get(10),

                                                                             firstname: row.get(11),
                                                                             lastname: row.get(12),
                                                                             street: row.get(13),
                                                                             zip: row.get(14),
                                                                             city: row.get(15),
                                                                             nation: row.get(16),
                                                                             grade: row.get(17),

                                                                             is_teacher: row.get(18),
                                                                             managed_by: row.get(19),

                                                                             oauth_provider: row.get(20),
                                                                             oauth_foreign_id: row.get(21) })
                          .ok()??;

        let duration = if session.permanent_login { Duration::days(90) } else { Duration::minutes(90) };
        let now = time::get_time();
        if let Some(last_activity) = session.last_activity {
            if now - last_activity < duration {
                self.execute("UPDATE session_user SET last_activity = ?1 WHERE id = ?2", &[&now, &session.id]).unwrap();
                Some(session)
            } else {
                // Session timed out
                // Should remove session token from session_user
                None
Robert Czechowski's avatar
Robert Czechowski committed
100
            }
101
102
103
104
        } else {
            // last_activity undefined
            // TODO: What should happen here?
            None
105
        }
106
    }
Robert Czechowski's avatar
Robert Czechowski committed
107
108
109
110
    fn save_session(&self, session: SessionUser) {
        self.execute("UPDATE session_user SET
                      username = ?1,
                      password = ?2,
111
112
113
114
                      salt = ?3,
                      logincode = ?4,
                      firstname = ?5,
                      lastname = ?6,
115
116
117
                      street = ?7,
                      zip = ?8,
                      city = ?9,
118
119
120
                      grade = ?10,
                      is_teacher = ?11
                      WHERE id = ?12",
Robert Czechowski's avatar
Robert Czechowski committed
121
122
123
124
125
126
                     &[&session.username,
                       &session.password,
                       &session.salt,
                       &session.logincode,
                       &session.firstname,
                       &session.lastname,
127
128
129
                       &session.street,
                       &session.zip,
                       &session.city,
Robert Czechowski's avatar
Robert Czechowski committed
130
                       &session.grade,
131
                       &session.is_teacher,
Robert Czechowski's avatar
Robert Czechowski committed
132
133
                       &session.id])
            .unwrap();
Robert Czechowski's avatar
Robert Czechowski committed
134
    }
135
    fn new_session(&self, session_token: &str) -> SessionUser {
136
        let csrf_token = helpers::make_csrf_token();
137

138
        let now = time::get_time();
Robert Czechowski's avatar
rustfmt    
Robert Czechowski committed
139
140
        self.execute(
            "INSERT INTO session_user (session_token, csrf_token, last_activity, permanent_login, grade, is_teacher)
141
                      VALUES (?1, ?2, ?3, 0, 0, 0)",
Robert Czechowski's avatar
rustfmt    
Robert Czechowski committed
142
143
144
            &[&session_token, &csrf_token, &now],
        )
        .unwrap();
Robert Czechowski's avatar
Robert Czechowski committed
145
        let id = self.query_row("SELECT last_insert_rowid()", &[], |row| row.get(0)).unwrap();
146

147
        SessionUser::minimal(id, session_token.to_owned(), csrf_token)
148
    }
149
    fn get_session_or_new(&self, key: &str) -> SessionUser {
150
151
152
153
154
155
156
157
        self.get_session(&key).ensure_alive().unwrap_or_else(|| {
                                                 // TODO: Factor this out in own function
                                                 // TODO: Should a new session key be generated every time?
                                                 self.execute(
                    "UPDATE session_user SET session_token = ?1 WHERE session_token = ?2",
                    &[&Option::<String>::None, &key]).unwrap();
                                                 self.new_session(&key)
                                             })
158
    }
159

160
    fn get_user_by_id(&self, user_id: i32) -> Option<SessionUser> {
161
        let res = self.query_row("SELECT session_token, csrf_token, last_login, last_activity, permanent_login, username, password, logincode, email, email_unconfirmed, email_confirmationcode, firstname, lastname, street, zip, city, nation, grade, is_teacher, managed_by, oauth_provider, oauth_foreign_id, salt FROM session_user WHERE id = ?1", &[&user_id], |row| {
162
163
164
165
166
167
168
            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),
169

170
171
                username: row.get(5),
                password: row.get(6),
172
                salt: row.get(22),
173
174
175
176
                logincode: row.get(7),
                email: row.get(8),
                email_unconfirmed: row.get(9),
                email_confirmationcode: row.get(10),
177

178
179
180
181
182
183
184
                firstname: row.get(11),
                lastname: row.get(12),
                street: row.get(13),
                zip: row.get(14),
                city: row.get(15),
                nation: row.get(16),
                grade: row.get(17),
185

186
187
                is_teacher: row.get(18),
                managed_by: row.get(19),
188
189
190

                oauth_provider: row.get(20),
                oauth_foreign_id: row.get(21),
191
192
            }
        });
193
194
        res.ok()
    }
195

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

199
200
        let group_id = match session.managed_by {
            Some(id) => id,
Robert Czechowski's avatar
Robert Czechowski committed
201
            None => return Some((session, None)),
202
        };
203

Robert Czechowski's avatar
Robert Czechowski committed
204
205
206
207
208
209
210
211
        let res = self.query_row("SELECT name, groupcode, tag, admin FROM usergroup WHERE id = ?1",
                                 &[&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() });
212
213
        match res {
            Ok(group) => Some((session, Some(group))),
Robert Czechowski's avatar
Robert Czechowski committed
214
            _ => Some((session, None)),
215
216
        }
    }
217

Daniel Brüning's avatar
Daniel Brüning committed
218
    //TODO: use session
Robert Czechowski's avatar
Robert Czechowski committed
219
220
221
    fn login(&self, _session: Option<&str>, username: &str, password: &str) -> Result<String, ()> {
        match self.query_row("SELECT id, password, salt FROM session_user WHERE username = ?1",
                             &[&username],
222
                             |row| -> (i32, Option<String>, Option<String>) { (row.get(0), row.get(1), row.get(2)) })
Robert Czechowski's avatar
Robert Czechowski committed
223
224
225
        {
            Ok((id, password_hash, salt)) => {
                //password_hash ist das, was in der Datenbank steht
226
227
228
                if helpers::verify_password(&password,
                                            &salt.expect("salt from database empty"),
                                            &password_hash.expect("password from database empty"))
Robert Czechowski's avatar
Robert Czechowski committed
229
230
                {
                    // TODO: fail more pleasantly
Robert Czechowski's avatar
Robert Czechowski committed
231
                    // Login okay, update session now!
232

233
234
                    let session_token = helpers::make_session_token();
                    let csrf_token = helpers::make_session_token();
Robert Czechowski's avatar
Robert Czechowski committed
235
                    let now = time::get_time();
236

Robert Czechowski's avatar
Robert Czechowski committed
237
                    self.execute("UPDATE session_user SET session_token = ?1, csrf_token = ?2, last_login = ?3, last_activity = ?3 WHERE id = ?4", &[&session_token, &csrf_token, &now, &id]).unwrap();
238

Robert Czechowski's avatar
Robert Czechowski committed
239
                    Ok(session_token)
Robert Czechowski's avatar
Robert Czechowski committed
240
241
                } else {
                    Err(())
Robert Czechowski's avatar
Robert Czechowski committed
242
                }
Robert Czechowski's avatar
Robert Czechowski committed
243
244
            }
            _ => Err(()),
Robert Czechowski's avatar
Robert Czechowski committed
245
        }
246
    }
Daniel Brüning's avatar
Daniel Brüning committed
247
248

    //TODO: use session
Robert Czechowski's avatar
Robert Czechowski committed
249
    fn login_with_code(&self, _session: Option<&str>, logincode: &str) -> Result<String, ()> {
250
        match self.query_row("SELECT id FROM session_user WHERE logincode = ?1", &[&logincode], |row| -> i32 {
Robert Czechowski's avatar
Robert Czechowski committed
251
252
                      row.get(0)
                  }) {
Robert Czechowski's avatar
Robert Czechowski committed
253
254
            Ok(id) => {
                // Login okay, update session now!
255

256
257
                let session_token = helpers::make_session_token();
                let csrf_token = helpers::make_csrf_token();
Robert Czechowski's avatar
Robert Czechowski committed
258
                let now = time::get_time();
259

Robert Czechowski's avatar
Robert Czechowski committed
260
                self.execute("UPDATE session_user SET session_token = ?1, csrf_token = ?2, last_login = ?3, last_activity = ?3 WHERE id = ?4", &[&session_token, &csrf_token, &now, &id]).unwrap();
261

Robert Czechowski's avatar
Robert Czechowski committed
262
                Ok(session_token)
Robert Czechowski's avatar
Robert Czechowski committed
263
264
            }
            _ => Err(()),
Robert Czechowski's avatar
Robert Czechowski committed
265
266
        }
    }
267

Daniel Brüning's avatar
Daniel Brüning committed
268
    //TODO: use session
269
270
    fn login_foreign(&self, _session: Option<&str>, foreign_id: &str, is_teacher: bool, firstname: &str,
                     lastname: &str)
Robert Czechowski's avatar
Robert Czechowski committed
271
272
                     -> Result<String, ()>
    {
273
274
        let session_token = helpers::make_session_token();
        let csrf_token = helpers::make_csrf_token();
275
        let now = time::get_time();
276

277
        match self.query_row("SELECT id FROM session_user WHERE oauth_foreign_id = ?1", &[&foreign_id], |row| -> i32 {
Robert Czechowski's avatar
Robert Czechowski committed
278
279
                      row.get(0)
                  }) {
280
            Ok(id) => {
281
                self.execute("UPDATE session_user SET session_token = ?1, csrf_token = ?2, last_login = ?3, last_activity = ?3 WHERE id = ?4", &[&session_token, &csrf_token, &now, &id]).unwrap();
282
283

                Ok(session_token)
Robert Czechowski's avatar
Robert Czechowski committed
284
            }
285
286
            // Add!
            _ => {
287
                self.execute("INSERT INTO session_user (session_token, csrf_token, last_login, last_activity, permanent_login, grade, is_teacher, oauth_foreign_id, firstname, lastname) VALUES (?1, ?2, ?3, ?3, ?4, ?5, ?6, ?7, ?8, ?9)", &[&session_token, &csrf_token, &now, &false, &0, &is_teacher, &foreign_id, &firstname, &lastname]).unwrap();
288

289
290
291
292
                Ok(session_token)
            }
        }
    }
Robert Czechowski's avatar
Robert Czechowski committed
293

Daniel Brüning's avatar
Daniel Brüning committed
294
    //TODO: use session
Robert Czechowski's avatar
Robert Czechowski committed
295
    fn create_user_with_groupcode(&self, _session: Option<&str>, groupcode: &str) -> Result<String, ()> {
296
        match self.query_row("SELECT id FROM usergroup WHERE groupcode = ?1", &[&groupcode], |row| -> i32 {
Robert Czechowski's avatar
Robert Czechowski committed
297
298
                      row.get(0)
                  }) {
Robert Czechowski's avatar
Robert Czechowski committed
299
300
            Ok(group_id) => {
                // Login okay, create session_user!
301

302
303
304
                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
Robert Czechowski's avatar
Robert Czechowski committed
305
                let now = time::get_time();
306

Robert Czechowski's avatar
Robert Czechowski committed
307
                self.execute("INSERT INTO session_user (session_token, csrf_token, last_login, last_activity, permanent_login, logincode, grade, is_teacher, managed_by) VALUES (?1, ?2, ?3, ?3, ?4, ?5, ?6, ?7, ?8)", &[&session_token, &csrf_token, &now, &false, &login_code, &0, &false, &group_id]).unwrap();
308

Robert Czechowski's avatar
Robert Czechowski committed
309
                Ok(session_token)
Robert Czechowski's avatar
Robert Czechowski committed
310
311
            }
            _ => Err(()),
Robert Czechowski's avatar
Robert Czechowski committed
312
313
        }
    }
Robert Czechowski's avatar
rustfmt    
Robert Czechowski committed
314

315
316
317
318
319
320
321
    fn create_group_with_users(&self, mut group: Group) {
        // Generate group ID:
        group.save(self);

        for user in group.members {
            let csrf_token = helpers::make_csrf_token();
            let login_code = helpers::make_login_code(); // TODO: check for collisions
Robert Czechowski's avatar
rustfmt    
Robert Czechowski committed
322

323
324
325
            self.execute("INSERT INTO session_user (firstname, lastname, csrf_token, permanent_login, logincode, grade, is_teacher, managed_by) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)", &[&user.firstname, &user.lastname, &csrf_token, &false, &login_code, &user.grade, &false, &group.id]).unwrap();
        }
    }
326
327

    fn logout(&self, session: &str) {
328
        self.execute("UPDATE session_user SET session_token = NULL WHERE session_token = ?1", &[&session]).unwrap();
329
330
    }

331
    fn load_submission(&self, session: &SessionUser, task: i32, subtask: Option<&str>) -> Option<Submission> {
Robert Czechowski's avatar
Robert Czechowski committed
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
        match subtask {
            None => self.query_row("SELECT id, grade, validated, nonvalidated_grade, value, date, needs_validation FROM submission WHERE task = ?1 AND session_user = ?2 ORDER BY id DESC LIMIT 1", &[&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) => self.query_row("SELECT id, grade, validated, nonvalidated_grade, value, date, needs_validation FROM submission WHERE task = ?1 AND session_user = ?2 AND subtask_identifier = ?3 ORDER BY id DESC LIMIT 1", &[&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),
355
                    subtask_identifier: Some(subtask_id.to_string()),
Robert Czechowski's avatar
Robert Czechowski committed
356
357
358
359
360
361
362
363
364
                    value: row.get(4),
                    date: row.get(5),
                    needs_validation: row.get(6),
                }
            }).ok()
        }
    }
    fn submit_submission(&self, mut submission: Submission) {
        submission.save(self);
365
366

        let mut grade = self.get_grade_by_submission(submission.id.unwrap());
367
368
        if grade.grade.is_none() || submission.grade > grade.grade.unwrap() {
            grade.grade = Some(submission.grade);
369
370
371
            grade.validated = false;
            grade.save(self);
        }
Robert Czechowski's avatar
Robert Czechowski committed
372
    }
373
    fn get_grade_by_submission(&self, submission_id: i32) -> Grade {
374
        self.query_row("SELECT grade.taskgroup, grade.user, grade.grade, grade.validated FROM grade JOIN task ON grade.taskgroup = task.taskgroup JOIN submission ON task.id = submission.task AND grade.user = submission.session_user WHERE submission.id = ?1", &[&submission_id], |row| {
375
376
377
378
379
380
381
            Grade {
                taskgroup: row.get(0),
                user: row.get(1),
                grade: row.get(2),
                validated: row.get(3),
            }
        }).unwrap_or_else(|_| {
382
            self.query_row("SELECT task.taskgroup, submission.session_user FROM submission JOIN task ON task.id = submission.task WHERE submission.id = ?1", &[&submission_id], |row| {
383
384
385
                Grade {
                    taskgroup: row.get(0),
                    user: row.get(1),
386
                    grade: None,
387
                    validated: false,
388
                }
389
390
391
            }).unwrap() // should this unwrap?
        })
    }
392

393
    fn get_contest_groups_grades(&self, session_id: i32, contest_id: i32)
Robert Czechowski's avatar
Robert Czechowski committed
394
                                 -> (Vec<String>, Vec<(Group, Vec<(UserInfo, Vec<Grade>)>)>) {
395
        let mut stmt = self.prepare("SELECT id, name FROM taskgroup WHERE contest = ?1 ORDER BY id ASC").unwrap();
396
        let tasknames_iter = stmt.query_map(&[&contest_id], |row| {
397
                                     let x: (i32, String) = (row.get(0), row.get(1));
Robert Czechowski's avatar
Robert Czechowski committed
398
399
400
                                     x
                                 })
                                 .unwrap();
401

402
403
        let tasknames: Vec<(i32, String)> = tasknames_iter.map(|x| x.unwrap()).collect();
        let mut taskindex: ::std::collections::BTreeMap<i32, usize> = ::std::collections::BTreeMap::new();
404
405

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

410
        let mut stmt = self.prepare("SELECT grade.taskgroup, grade.user, grade.grade, grade.validated, usergroup.id, usergroup.name, usergroup.groupcode, usergroup.tag, student.id, student.username, student.logincode, student.firstname, student.lastname
411
412
413
                                     FROM grade
                                     JOIN taskgroup ON grade.taskgroup = taskgroup.id
                                     JOIN session_user AS student ON grade.user = student.id
414
415
416
                                     JOIN usergroup ON student.managed_by = usergroup.id
                                     WHERE usergroup.admin = ?1 AND taskgroup.contest = ?2
                                     ORDER BY usergroup.id, student.id, taskgroup.id ASC").unwrap();
Robert Czechowski's avatar
Robert Czechowski committed
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
        let mut gradeinfo_iter =
            stmt.query_map(&[&session_id, &contest_id], |row| {
                    (Grade { taskgroup: row.get(0), user: row.get(1), grade: row.get(2), validated: row.get(3) },
                     Group { id: Some(row.get(4)),
                             name: row.get(5),
                             groupcode: row.get(6),
                             tag: row.get(7),
                             admin: session_id,
                             members: Vec::new() },
                     UserInfo { id: row.get(8),
                                username: row.get(9),
                                logincode: row.get(10),
                                firstname: row.get(11),
                                lastname: row.get(12) })
                })
                .unwrap();
433

Robert Czechowski's avatar
Robert Czechowski committed
434
        if let Some(t /*Ok((grade, mut group, mut userinfo))*/) = gradeinfo_iter.next() {
435
            let (grade, group, userinfo) = t.unwrap();
436

437
438
439
            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();
440

441
442
            let index = grade.taskgroup;
            grades[taskindex[&index]] = grade;
443

444
445
446
            // TODO: does
            // https://stackoverflow.com/questions/29859892/mutating-an-item-inside-of-nested-loops
            // help to spare all these clones?
447

448
449
450
451
452
            for ggu in gradeinfo_iter {
                if let Ok((g, gr, ui)) = ggu {
                    if gr.id != group.id {
                        users.push((userinfo.clone(), grades));
                        grades = vec![Default::default(); n_tasks];
453

454
455
                        groups.push((group.clone(), users));
                        users = Vec::new();
Robert Czechowski's avatar
Robert Czechowski committed
456
                    } else if ui.id != userinfo.id {
457
458
459
460
461
                        users.push((userinfo.clone(), grades));
                        grades = vec![Default::default(); n_tasks];
                    }
                    let index = g.taskgroup;
                    grades[taskindex[&index]] = g;
462
463
                }
            }
464
465
            users.push((userinfo, grades));
            groups.push((group, users));
466

467
            (tasknames.iter().map(|(_, name)| name.clone()).collect(), groups)
Robert Czechowski's avatar
Robert Czechowski committed
468
        } else {
469
            (Vec::new(), Vec::new()) // should those be default filled?
470
        }
471
    }
472
    fn get_contest_user_grades(&self, session_token: &str, contest_id: i32) -> Vec<Grade> {
473
        let mut stmt = self.prepare("SELECT id, name FROM taskgroup WHERE contest = ?1 ORDER BY id ASC").unwrap();
474
        let tasknames_iter = stmt.query_map(&[&contest_id], |row| {
475
                                     let x: (i32, String) = (row.get(0), row.get(1));
Robert Czechowski's avatar
Robert Czechowski committed
476
477
478
                                     x
                                 })
                                 .unwrap();
479

480
481
        let tasknames: Vec<(i32, String)> = tasknames_iter.map(|x| x.unwrap()).collect();
        let mut taskindex: ::std::collections::BTreeMap<i32, usize> = ::std::collections::BTreeMap::new();
482
483

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

488
489
490
491
492
        let mut stmt = self.prepare("SELECT grade.taskgroup, grade.user, grade.grade, grade.validated
                                     FROM grade
                                     JOIN taskgroup ON grade.taskgroup = taskgroup.id
                                     JOIN session_user ON session_user.id = grade.user
                                     WHERE session_user.session_token = ?1 AND taskgroup.contest = ?2
Robert Czechowski's avatar
Robert Czechowski committed
493
494
495
496
497
498
499
                                     ORDER BY taskgroup.id ASC")
                           .unwrap();
        let gradeinfo_iter = stmt.query_map(&[&session_token, &contest_id], |row| Grade { taskgroup: row.get(0),
                                                                                          user: row.get(1),
                                                                                          grade: row.get(2),
                                                                                          validated: row.get(3) })
                                 .unwrap();
500
501
502
503
504
505
506

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

        for g in gradeinfo_iter {
            let g = g.unwrap();
            let index = g.taskgroup;
            grades[taskindex[&index]] = g;
507
        }
508

509
        grades
510
    }
511

512
    fn get_taskgroup_user_grade(&self, session_token: &str, taskgroup_id: i32) -> Grade {
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
        let mut stmt = self.prepare("SELECT grade.taskgroup, grade.user, grade.grade, grade.validated
                                     FROM grade
                                     JOIN session_user ON session_user.id = grade.user
                                     WHERE session_user.session_token = ?1 AND grade.taskgroup = ?2")
                           .unwrap();
        let gradeinfo_iter = stmt.query_map(&[&session_token, &taskgroup_id], |row| Grade { taskgroup: row.get(0),
                                                                                            user: row.get(1),
                                                                                            grade: row.get(2),
                                                                                            validated: row.get(3) })
                                 .unwrap();

        let grade = gradeinfo_iter.map(|t| t.unwrap_or_default()).next().unwrap_or_default();
        grade
    }

Robert Czechowski's avatar
Robert Czechowski committed
528
    fn get_contest_list(&self) -> Vec<Contest> {
Robert Czechowski's avatar
Robert Czechowski committed
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
        let mut stmt =
            self.prepare("SELECT id, location, filename, name, duration, public, start_date, end_date FROM contest")
                .unwrap();
        let res = stmt.query_map(&[], |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),
                                                      taskgroups: Vec::new() })
                      .unwrap()
                      .filter_map(|row| row.ok())
                      .collect();
544
        res
Robert Czechowski's avatar
Robert Czechowski committed
545
    }
546

547
    fn get_contest_by_id(&self, contest_id: i32) -> Contest {
Robert Czechowski's avatar
rustfmt    
Robert Czechowski committed
548
549
550
551
        self.query_row(
            "SELECT location, filename, name, duration, public, start_date, end_date FROM contest WHERE id = ?1",
            &[&contest_id],
            |row| Contest {
552
553
554
555
556
557
558
559
                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),
560
                taskgroups: Vec::new(),
Robert Czechowski's avatar
rustfmt    
Robert Czechowski committed
561
562
563
            },
        )
        .unwrap()
564
    }
565

566
    fn get_contest_by_id_complete(&self, contest_id: i32) -> Contest {
567
        let mut stmt = self.prepare("SELECT contest.location, contest.filename, contest.name, contest.duration, contest.public, contest.start_date, contest.end_date, taskgroup.id, taskgroup.name, task.id, task.location, task.stars FROM contest JOIN taskgroup ON contest.id = taskgroup.contest JOIN task ON taskgroup.id = task.taskgroup WHERE contest.id = ?1").unwrap();
568

Robert Czechowski's avatar
Robert Czechowski committed
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
        let mut taskgroupcontest_iter =
            stmt.query_map(&[&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),
                               taskgroups: Vec::new() },
                     Taskgroup { id: Some(row.get(7)), contest: contest_id, name: row.get(8), tasks: Vec::new() },
                     Task { id: Some(row.get(9)), taskgroup: row.get(7), location: row.get(10), stars: row.get(11) })
                })
                .unwrap();
584
585
586
587
588
589
590
591
592

        let (mut contest, mut taskgroup, task) = taskgroupcontest_iter.next().unwrap().unwrap();
        taskgroup.tasks.push(task);
        for tgc in taskgroupcontest_iter {
            if let Ok((_, tg, t)) = tgc {
                if tg.id != taskgroup.id {
                    contest.taskgroups.push(taskgroup);
                    taskgroup = tg;
                }
593
                taskgroup.tasks.push(t);
594
595
596
597
598
            }
        }
        contest.taskgroups.push(taskgroup);
        contest
    }
599

600
    fn get_contest_by_id_partial(&self, contest_id: i32) -> Contest {
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
        let mut stmt = self.prepare("SELECT contest.location, contest.filename, contest.name, contest.duration, contest.public, contest.start_date, contest.end_date, taskgroup.id, taskgroup.name FROM contest JOIN taskgroup ON contest.id = taskgroup.contest WHERE contest.id = ?1").unwrap();

        let mut taskgroupcontest_iter =
            stmt.query_map(&[&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),
                               taskgroups: Vec::new() },
                     Taskgroup { id: Some(row.get(7)), contest: contest_id, name: row.get(8), tasks: Vec::new() })
                })
                .unwrap();

        let (mut contest, taskgroup) = taskgroupcontest_iter.next().unwrap().unwrap();
        contest.taskgroups.push(taskgroup);
        for tgc in taskgroupcontest_iter {
            if let Ok((_, tg)) = tgc {
                contest.taskgroups.push(tg);
            }
        }
        contest
    }

628
    fn get_participation(&self, session: &str, contest_id: i32) -> Option<Participation> {
Robert Czechowski's avatar
Robert Czechowski committed
629
630
631
632
633
634
635
636
        self.query_row("SELECT user, start_date FROM participation JOIN session_user ON session_user.id = user WHERE session_user.session_token = ?1 AND contest = ?2", &[&session, &contest_id], |row| {
            Participation {
                contest: contest_id,
                user: row.get(0),
                start: row.get(1)
            }
        }).ok()
    }
637
    fn new_participation(&self, session: &str, contest_id: i32) -> Result<Participation, ()> {
638
        match self.query_row("SELECT user, start_date FROM participation JOIN session_user ON session_user.id = user WHERE session_user.session_token = ?1 AND contest = ?2", &[&session, &contest_id], |_| {}) {
Robert Czechowski's avatar
Robert Czechowski committed
639
640
641
642
643
644
645
646
            Ok(()) => Err(()),
            Err(_) => {
                let now = time::get_time();
                self.execute(
                    "INSERT INTO participation (contest, user, start_date)
                     SELECT ?1, id, ?2 FROM session_user WHERE session_token = ?3",
                     &[&contest_id, &now, &session]).unwrap();

647
                Ok(self.get_participation(session, contest_id).unwrap()) // TODO: This errors if not logged in …
Robert Czechowski's avatar
Robert Czechowski committed
648
649
650
            }
        }
    }
651
    fn get_task_by_id(&self, task_id: i32) -> Task {
Robert Czechowski's avatar
Robert Czechowski committed
652
653
654
655
        self.query_row("SELECT location, stars, taskgroup FROM task WHERE id = ?1", &[&task_id], |row| {
                Task { id: Some(task_id), taskgroup: row.get(2), location: row.get(0), stars: row.get(1) }
            })
            .unwrap()
656
    }
657
    fn get_task_by_id_complete(&self, task_id: i32) -> (Task, Taskgroup, Contest) {
658
        self.query_row(
659
            "SELECT task.location, task.stars, taskgroup.id, taskgroup.name, contest.id, contest.location, contest.filename, contest.name, contest.duration, contest.public, contest.start_date, contest.end_date FROM contest JOIN taskgroup ON taskgroup.contest = contest.id JOIN task ON task.taskgroup = taskgroup.id WHERE task.id = ?1",
660
661
662
663
664
665
666
667
668
669
670
            &[&task_id],
            |row| {
                (Task {
                    id: Some(task_id),
                    taskgroup: row.get(2),
                    location: row.get(0),
                    stars: row.get(1),
                }, Taskgroup {
                    id: Some(row.get(2)),
                    contest: row.get(4),
                    name: row.get(3),
671
                    tasks: Vec::new(),
672
673
674
675
676
677
678
679
680
681
682
683
684
                }, Contest {
                    id: Some(row.get(4)),
                    location: row.get(5),
                    filename: row.get(6),
                    name: row.get(7),
                    duration: row.get(8),
                    public: row.get(9),
                    start: row.get(10),
                    end: row.get(11),
                    taskgroups: Vec::new(),
                })
            }).unwrap()
    }
685

686
    fn get_submission_to_validate(&self, tasklocation: &str, subtask: Option<&str>) -> i32 {
687
688
689
690
691
692
        match subtask {
            Some(st) => self.query_row("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", &[&tasklocation, &st], |row| {row.get(0)}).unwrap(),
            None => self.query_row("SELECT id FROM submission JOIN task ON submission.task = task.id WHERE task.location = ?1 AND needs_validation = 1 LIMIT 1", &[&tasklocation], |row| {row.get(0)}).unwrap(),
        }
    }

693
694
    fn find_next_submission_to_validate(&self, userid: i32, taskgroupid: i32) {
        let (id, validated) : (i32, bool) = self.query_row("SELECT id, validated FROM submission JOIN task ON submission.task = task.id WHERE task.taskgroup = ?1 AND submission.session_user = ?2 ORDER BY value DESC id DESC LIMIT 1", &[&taskgroupid, &userid], |row| {(row.get(0), row.get(1))}).unwrap();;
695
696
697
698
        if !validated {
            self.execute("UPDATE submission SET needs_validation = 1 WHERE id = ?1", &[&id]).unwrap();
        }
    }
Robert Czechowski's avatar
Robert Czechowski committed
699

Robert Czechowski's avatar
Robert Czechowski committed
700
    fn add_group(&self, group: &mut Group) { group.save(self); }
Robert Czechowski's avatar
Robert Czechowski committed
701

702
    fn get_groups(&self, session_id: i32) -> Vec<Group> {
Robert Czechowski's avatar
Robert Czechowski committed
703
        let mut stmt = self.prepare("SELECT id, name, groupcode, tag FROM usergroup WHERE admin = ?1").unwrap();
Robert Czechowski's avatar
Robert Czechowski committed
704
705
706
707
708
709
710
711
712
        let res = stmt.query_map(&[&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()
                      .filter_map(|row| row.ok())
                      .collect();
713
        res
Robert Czechowski's avatar
Robert Czechowski committed
714
    }
715
    fn get_groups_complete(&self, _session_id: i32) -> Vec<Group> {
Robert Czechowski's avatar
Robert Czechowski committed
716
717
        unimplemented!();
    }
718
    fn get_group_complete(&self, group_id: i32) -> Option<Group> {
Robert Czechowski's avatar
Robert Czechowski committed
719
720
721
722
723
724
725
726
727
        let mut group = self.query_row("SELECT name, groupcode, tag, admin FROM usergroup WHERE id  = ?1",
                                       &[&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(); // TODO handle error
Robert Czechowski's avatar
Robert Czechowski committed
728

729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
        let mut stmt = self.prepare("SELECT id, session_token, csrf_token, last_login, last_activity, permanent_login, username, password, logincode, email, email_unconfirmed, email_confirmationcode, firstname, lastname, street, zip, city, nation, grade, is_teacher, oauth_provider, oauth_foreign_id, salt FROM session_user WHERE managed_by = ?1").unwrap();
        let rows = stmt.query_map(&[&group_id], |row| SessionUser { id: row.get(0),
                                                                    session_token: row.get(1),
                                                                    csrf_token: row.get(2),
                                                                    last_login: row.get(3),
                                                                    last_activity: row.get(4),
                                                                    permanent_login: row.get(5),

                                                                    username: row.get(6),
                                                                    password: row.get(7),
                                                                    salt: row.get(22),
                                                                    logincode: row.get(8),
                                                                    email: row.get(9),
                                                                    email_unconfirmed: row.get(10),
                                                                    email_confirmationcode: row.get(11),

                                                                    firstname: row.get(12),
                                                                    lastname: row.get(13),
                                                                    street: row.get(14),
                                                                    zip: row.get(15),
                                                                    city: row.get(16),
                                                                    nation: row.get(17),
                                                                    grade: row.get(18),

                                                                    is_teacher: row.get(19),
                                                                    managed_by: Some(group_id),

                                                                    oauth_provider: row.get(20),
                                                                    oauth_foreign_id: row.get(21) })
Robert Czechowski's avatar
Robert Czechowski committed
758
                       .unwrap();
Robert Czechowski's avatar
Robert Czechowski committed
759
760
761
762
763
764

        for user in rows {
            group.members.push(user.unwrap());
        }
        Some(group)
    }
765
766

    fn reset_all_contest_visibilities(&self) { self.execute("UPDATE contest SET public = ?1", &[&false]).unwrap(); }
767
768
769
}

impl MedalObject<Connection> for Task {
Robert Czechowski's avatar
Robert Czechowski committed
770
    fn save(&mut self, conn: &Connection) {
Robert Czechowski's avatar
Robert Czechowski committed
771
772
773
774
775
776
777
778
        conn.query_row("SELECT id FROM task WHERE taskgroup = ?1 AND location = ?2",
                       &[&self.taskgroup, &self.location],
                       |row| row.get(0))
            .and_then(|id| {
                self.set_id(id);
                Ok(())
            })
            .unwrap_or(()); // Err means no entry yet and is expected result
779

780
        let id = match self.get_id() {
781
782
            Some(id) => {
                conn.execute(
Robert Czechowski's avatar
Robert Czechowski committed
783
                             "UPDATE task SET taskgroup = ?1, location = ?2, stars = ?3
784
                     WHERE id = ?4",
Robert Czechowski's avatar
Robert Czechowski committed
785
786
787
                             &[&self.taskgroup, &self.location, &self.stars, &id],
                )
                    .unwrap();
788
789
                id
            }
790
            None => {
791
                conn.execute(
Robert Czechowski's avatar
Robert Czechowski committed
792
                             "INSERT INTO task (taskgroup, location, stars)
793
                     VALUES (?1, ?2, ?3)",
Robert Czechowski's avatar
Robert Czechowski committed
794
795
796
797
                             &[&self.taskgroup, &self.location, &self.stars],
                )
                    .unwrap();
                conn.query_row("SELECT last_insert_rowid()", &[], |row| row.get(0)).unwrap()
798
799
            }
        };
800
        self.set_id(id);
801
802
803
804
    }
}

impl MedalObject<Connection> for Taskgroup {
Robert Czechowski's avatar
Robert Czechowski committed
805
    fn save(&mut self, conn: &Connection) {
Robert Czechowski's avatar
Robert Czechowski committed
806
807
808
809
810
811
812
813
        conn.query_row("SELECT id FROM taskgroup WHERE contest = ?1 AND name = ?2",
                       &[&self.contest, &self.name],
                       |row| row.get(0))
            .and_then(|id| {
                self.set_id(id);
                Ok(())
            })
            .unwrap_or(()); // Err means no entry yet and is expected result
814

815
        let id = match self.get_id() {
816
817
            Some(id) => {
                conn.execute(
Robert Czechowski's avatar
Robert Czechowski committed
818
                             "UPDATE taskgroup SET contest = ?1, name = ?2
819
                     WHERE id = ?3",
Robert Czechowski's avatar
Robert Czechowski committed
820
821
822
                             &[&self.contest, &self.name, &id],
                )
                    .unwrap();
823
824
                id
            }
825
            None => {
826
                conn.execute(
Robert Czechowski's avatar
Robert Czechowski committed
827
                             "INSERT INTO taskgroup (contest, name)
828
                     VALUES (?1, ?2)",
Robert Czechowski's avatar
Robert Czechowski committed
829
830
831
832
                             &[&self.contest, &self.name],
                )
                    .unwrap();
                conn.query_row("SELECT last_insert_rowid()", &[], |row| row.get(0)).unwrap()
833
834
            }
        };
835
        self.set_id(id);
836
837
838
839
840
841
842
843
        for mut task in &mut self.tasks {
            task.taskgroup = id;
            task.save(conn);
        }
    }
}

impl MedalObject<Connection> for Contest {
Robert Czechowski's avatar
Robert Czechowski committed
844
    fn save(&mut self, conn: &Connection) {
Robert Czechowski's avatar
Robert Czechowski committed
845
846
847
848
849
850
851
852
        conn.query_row("SELECT id FROM contest WHERE location = ?1 AND filename = ?2",
                       &[&self.location, &self.filename],
                       |row| row.get(0))
            .and_then(|id| {
                self.set_id(id);
                Ok(())
            })
            .unwrap_or(()); // Err means no entry yet and is expected result
853

Robert Czechowski's avatar
Robert Czechowski committed
854
855
856
857
858
        let id =
            match self.get_id() {
                Some(id) => {
                    conn.execute(
                                 "UPDATE contest SET location = ?1,filename = ?2,
859
860
                     name = ?3, duration = ?4, public = ?5, start_date = ?6,
                     end_date = ?7 WHERE id = ?8",
Robert Czechowski's avatar
Robert Czechowski committed
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
                                 &[
                        &self.location,
                        &self.filename,
                        &self.name,
                        &self.duration,
                        &self.public,
                        &self.start,
                        &self.end,
                        &id,
                    ],
                    )
                        .unwrap();
                    id
                }
                None => {
                    conn.execute(
877
878
                    "INSERT INTO contest (location, filename, name, duration, public, start_date, end_date)
                     VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)",
Robert Czechowski's avatar
rustfmt    
Robert Czechowski committed
879
880
881
                    &[&self.location, &self.filename, &self.name, &self.duration, &self.public, &self.start, &self.end],
                )
                .unwrap();
Robert Czechowski's avatar
Robert Czechowski committed
882
883
884
                    conn.query_row("SELECT last_insert_rowid()", &[], |row| row.get(0)).unwrap()
                }
            };
885
        self.set_id(id);
886
887
888
889
890
891
892
893
        for mut taskgroup in &mut self.taskgroups {
            taskgroup.contest = id;
            taskgroup.save(conn);
        }
    }
}

impl MedalObject<Connection> for Grade {
Robert Czechowski's avatar
Robert Czechowski committed
894
    fn save(&mut self, conn: &Connection) {
Robert Czechowski's avatar
Robert Czechowski committed
895
896
897
        conn.execute("INSERT OR REPLACE INTO grade (taskgroup, user, grade, validated) VALUES (?1, ?2, ?3, ?4)",
                     &[&self.taskgroup, &self.user, &self.grade, &self.validated])
            .unwrap();
898
899
900
901
    }
}

impl MedalObject<Connection> for Participation {
Robert Czechowski's avatar
Robert Czechowski committed
902
    fn save(&mut self, conn: &Connection) {
903
        conn.execute("INSERT INTO participation (contest, user, start_date) VALUES (?1, ?2, ?3)",
Robert Czechowski's avatar
Robert Czechowski committed
904
905
                     &[&self.contest, &self.user, &self.start])
            .unwrap();
906
907
908
    }
}

Robert Czechowski's avatar
Robert Czechowski committed
909
910
impl MedalObject<Connection> for Submission {
    fn save(&mut self, conn: &Connection) {
911
        match self.get_id() {
Robert Czechowski's avatar
Robert Czechowski committed
912
            Some(_id) => unimplemented!(),
Robert Czechowski's avatar
Robert Czechowski committed
913
914
            None => {
                conn.execute("INSERT INTO submission (task, session_user, grade, validated, nonvalidated_grade, subtask_identifier, value, date, needs_validation) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)", &[&self.task, &self.session_user, &self.grade, &self.validated, &self.nonvalidated_grade, &self.subtask_identifier, &self.value, &self.date, &self.needs_validation]).unwrap();
Robert Czechowski's avatar
Robert Czechowski committed
915
                self.set_id(conn.query_row("SELECT last_insert_rowid()", &[], |row| row.get(0)).unwrap());
Robert Czechowski's avatar
Robert Czechowski committed
916
917
918
919
920
921
922
            }
        }
    }
}

impl MedalObject<Connection> for Group {
    fn save(&mut self, conn: &Connection) {
923
        match self.get_id() {
Robert Czechowski's avatar
Robert Czechowski committed
924
            Some(_id) => unimplemented!(),
Robert Czechowski's avatar
Robert Czechowski committed
925
            None => {
Robert Czechowski's avatar
Robert Czechowski committed
926
927
928
929
                conn.execute("INSERT INTO usergroup (name, groupcode, tag, admin) VALUES (?1, ?2, ?3, ?4)",
                             &[&self.name, &self.groupcode, &self.tag, &self.admin])
                    .unwrap();
                self.set_id(conn.query_row("SELECT last_insert_rowid()", &[], |row| row.get(0)).unwrap());
Robert Czechowski's avatar
Robert Czechowski committed
930
931
932
933
            }
        }
    }
}