db_conn_sqlite.rs 35.6 KB
Newer Older
1
extern crate rusqlite;
2
extern crate bcrypt;
3
4
5
6
7
8

use self::rusqlite::Connection;

use db_conn::{MedalConnection, MedalObject};
use db_objects::*;

9
use rand::{thread_rng, Rng, distributions::Alphanumeric};
Robert Czechowski's avatar
Robert Czechowski committed
10

11

12
use time;
13
use self::time::Duration;
14

15
16
use std::path::{Path};

Daniel Brüning's avatar
Daniel Brüning committed
17
use self::bcrypt::{verify};
18

19
20
use ::functions; // todo: remove (usertype in db)

21
22
23
24
25
26
fn verify_password(password: &str, salt: &str, password_hash: &str) -> bool {
   let password_and_salt = [password, salt].concat().to_string();
   match verify(password_and_salt, password_hash) {
       Ok(result) => result,
       _ => false
   }
Robert Czechowski's avatar
Robert Czechowski committed
27
28
}

29
impl MedalConnection for Connection {
30
31
    fn create(file: &Path) -> Connection {
        Connection::open(file).unwrap()
32
33
34
    }

    fn dbtype(&self) -> &'static str {
35
        "sqlite"
36
37
    }

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

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

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

49
        let tx = self.transaction().unwrap();
50

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

54
        tx.commit().unwrap();
55

56
57
58
        println!("OK.");
    }

59
    // fn get_session<T: ToSql>(&self, key: T, keyname: &str) -> Option<SessionUser> {
60
    fn get_session(&self, key: &str) -> Option<SessionUser> {
61
        let res = self.query_row("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, pms_id, pms_school_id, salt FROM session_user WHERE session_token = ?1", &[&key], |row| {
62
63
            SessionUser {
                id: row.get(0),
64
                session_token: Some(key.to_string()),
65
66
67
68
                csrf_token: row.get(1),
                last_login: row.get(2),
                last_activity: row.get(3),
                permanent_login: row.get(4),
69

70
71
                username: row.get(5),
                password: row.get(6),
72
                salt: row.get(22),
73
74
75
76
                logincode: row.get(7),
                email: row.get(8),
                email_unconfirmed: row.get(9),
                email_confirmationcode: row.get(10),
77

78
79
80
81
82
83
84
                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),
85

86
87
88
89
90
                is_teacher: row.get(18),
                managed_by: row.get(19),
                pms_id: row.get(20),
                pms_school_id: row.get(21),
            }
91
92
        });
        match res {
93
94
95
96
97
98
99
100
101
102
103
104
105
106
            Ok(session) => {
                let duration = if session.permanent_login { Duration::days(90) } else { Duration::minutes(90) };
                let now = time::get_time();
                if now - session.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
                }
            },
            _ => None // no session found, should create new session in get_session_or_new()
107
        }
108
    }
Robert Czechowski's avatar
Robert Czechowski committed
109
110
111
112
    fn save_session(&self, session: SessionUser) {
        self.execute("UPDATE session_user SET
                      username = ?1,
                      password = ?2,
113
114
115
116
117
                      salt = ?3,
                      logincode = ?4,
                      firstname = ?5,
                      lastname = ?6,
                      grade = ?7 WHERE id = ?", &[&session.username, &session.password, &session.salt, &session.logincode, &session.firstname, &session.lastname, &session.grade, &session.id]).unwrap();
Robert Czechowski's avatar
Robert Czechowski committed
118
    }
Robert Czechowski's avatar
Robert Czechowski committed
119
    fn new_session(&self) -> SessionUser {
120
121
        let session_token = "123".to_string();
        let csrf_token = "123".to_string();
122

123
124
        self.execute("INSERT INTO session_user (session_token, csrf_token, permanent_login, is_teacher)
                      VALUES (?1, ?2, 0, 0)",
125
126
            &[&session_token, &csrf_token]).unwrap();
        let id = self.query_row("SELECT last_insert_rowid()", &[], |row| {row.get(0)}).unwrap();
127

128
129
        SessionUser::minimal(id, session_token, csrf_token)
    }
130
131
    fn get_session_or_new(&self, key: &str) -> SessionUser {
        self.get_session(&key).unwrap_or_else(|| self.new_session())
132
    }
133

134
    fn get_user_by_id(&self, user_id: u32) -> Option<SessionUser> {
135
136
137
138
139
140
141
142
        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, pms_id, pms_school_id FROM session_user WHERE id = ?1", &[&user_id], |row| {
            SessionUser {
                id: user_id,
                session_token: row.get(0),
                csrf_token: row.get(1),
                last_login: row.get(2),
                last_activity: row.get(3),
                permanent_login: row.get(4),
143

144
145
146
147
148
149
150
                username: row.get(5),
                password: row.get(6),
                salt: None,//"".to_string(),
                logincode: row.get(7),
                email: row.get(8),
                email_unconfirmed: row.get(9),
                email_confirmationcode: row.get(10),
151

152
153
154
155
156
157
158
                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),
159

160
161
162
163
164
165
                is_teacher: row.get(18),
                managed_by: row.get(19),
                pms_id: row.get(20),
                pms_school_id: row.get(21),
            }
        });
166
167
        res.ok()
    }
168

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

172
173
174
175
        let group_id = match session.managed_by {
            Some(id) => id,
            None => return Some((session, None))
        };
176

177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
        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(),
            }
        });
        match res {
            Ok(group) => Some((session, Some(group))),
            _ => Some((session, None))
        }
    }
192

Daniel Brüning's avatar
Daniel Brüning committed
193
194
    //TODO: use session
    fn login(&self, _session: Option<&str>, username: &str, password: &str) -> Result<String,()> {
Robert Czechowski's avatar
Robert Czechowski committed
195
196
197
198
        match self.query_row(
            "SELECT id, password, salt FROM session_user WHERE username = ?1",
            &[&username],
            |row| -> (u32, Option<String>, Option<String>) {
199
                (row.get(0), row.get(1), row.get(2))
Robert Czechowski's avatar
Robert Czechowski committed
200
            }) {
201
            Ok((id, password_hash, salt)) => {                          //password_hash ist das, was in der Datenbank steht
202
                if verify_password(&password, &salt.expect("salt from database empty"), &password_hash.expect("password from database empty")) { // TODO: fail more pleasantly
Robert Czechowski's avatar
Robert Czechowski committed
203
                    // Login okay, update session now!
204

205
206
                    let session_token: String = thread_rng().sample_iter(&Alphanumeric).take(10).collect();
                    let csrf_token: String = thread_rng().sample_iter(&Alphanumeric).take(10).collect();
Robert Czechowski's avatar
Robert Czechowski committed
207
                    let now = time::get_time();
208

Robert Czechowski's avatar
Robert Czechowski committed
209
                    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();
210

Robert Czechowski's avatar
Robert Czechowski committed
211
212
                    Ok(session_token)
                }
213
                else {Err(()) }
214

Robert Czechowski's avatar
Robert Czechowski committed
215
            },
216
            _ => {Err(()) }
Robert Czechowski's avatar
Robert Czechowski committed
217
        }
218
    }
Daniel Brüning's avatar
Daniel Brüning committed
219
220
221

    //TODO: use session
    fn login_with_code(&self, _session: Option<&str>, logincode: &str) -> Result<String,()> {
Robert Czechowski's avatar
Robert Czechowski committed
222
223
224
225
        match self.query_row(
            "SELECT id FROM session_user WHERE logincode = ?1",
            &[&logincode],
            |row| -> u32 {
226
                row.get(0)
Robert Czechowski's avatar
Robert Czechowski committed
227
228
229
            }) {
            Ok(id) => {
                // Login okay, update session now!
230

231
232
                let session_token: String = thread_rng().sample_iter(&Alphanumeric).take(10).collect();
                let csrf_token: String = thread_rng().sample_iter(&Alphanumeric).take(10).collect();
Robert Czechowski's avatar
Robert Czechowski committed
233
                let now = time::get_time();
234

Robert Czechowski's avatar
Robert Czechowski committed
235
                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();
236

Robert Czechowski's avatar
Robert Czechowski committed
237
238
                Ok(session_token)
            },
239
            _ => {Err(()) }
Robert Czechowski's avatar
Robert Czechowski committed
240
241
        }
    }
242

Daniel Brüning's avatar
Daniel Brüning committed
243
244
    //TODO: use session
    fn login_foreign(&self, _session: Option<&str>, foreign_id: u32, foreign_type: functions::UserType, firstname: &str, lastname: &str) -> Result<String,()> {
245
246
        let session_token: String = thread_rng().sample_iter(&Alphanumeric).take(10).collect();
        let csrf_token: String = thread_rng().sample_iter(&Alphanumeric).take(10).collect();
247
        let now = time::get_time();
248

249
250
251
252
        match self.query_row(
            "SELECT id FROM session_user WHERE pms_id = ?1",
            &[&foreign_id],
            |row| -> u32 {row.get(0)}) {
253
            Ok(id) => {
254
                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();
255
256

                Ok(session_token)
257
258
259
260
            },
            // Add!
            _ => {
                self.execute("INSERT INTO session_user (session_token, csrf_token, last_login, last_activity, permanent_login, grade, is_teacher, pms_id, firstname, lastname) VALUES (?1, ?2, ?3, ?3, ?4, ?5, ?6, ?7, ?8, ?9)", &[&session_token, &csrf_token, &now, &false, &0, &(foreign_type != functions::UserType::User), &foreign_id, &firstname, &lastname]).unwrap();
261

262
263
264
265
                Ok(session_token)
            }
        }
    }
Robert Czechowski's avatar
Robert Czechowski committed
266

Daniel Brüning's avatar
Daniel Brüning committed
267
268
    //TODO: use session
    fn create_user_with_groupcode(&self, _session: Option<&str>, groupcode: &str) -> Result<String,()> {
Robert Czechowski's avatar
Robert Czechowski committed
269
270
271
272
        match self.query_row(
            "SELECT id FROM usergroup WHERE groupcode = ?1",
            &[&groupcode],
            |row| -> u32 {
273
                row.get(0)
Robert Czechowski's avatar
Robert Czechowski committed
274
275
276
            }) {
            Ok(group_id) => {
                // Login okay, create session_user!
277

278
279
280
                let session_token: String = thread_rng().sample_iter(&Alphanumeric).take(10).collect();
                let csrf_token: String = thread_rng().sample_iter(&Alphanumeric).take(10).collect();
                let login_code: String = Some('u').into_iter().chain(thread_rng().sample_iter(&Alphanumeric))
Robert Czechowski's avatar
Robert Czechowski committed
281
                    .filter(|x| {let x = *x; !(x == 'l' || x == 'I' || x == '1' || x == 'O' || x == 'o' || x == '0')})
282
283
                    .take(9).collect();
                // todo: check for collisions
Robert Czechowski's avatar
Robert Czechowski committed
284
                let now = time::get_time();
285

Robert Czechowski's avatar
Robert Czechowski committed
286
                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();
287

Robert Czechowski's avatar
Robert Czechowski committed
288
289
                Ok(session_token)
            },
290
            _ => {Err(()) }
Robert Czechowski's avatar
Robert Czechowski committed
291
292
        }
    }
293
294

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

298
299

    fn load_submission(&self, session: &SessionUser, task: u32, subtask: Option<&str>) -> Option<Submission> {
Robert Czechowski's avatar
Robert Czechowski committed
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
        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),
323
                    subtask_identifier: Some(subtask_id.to_string()),
Robert Czechowski's avatar
Robert Czechowski committed
324
325
326
327
328
329
330
331
332
                    value: row.get(4),
                    date: row.get(5),
                    needs_validation: row.get(6),
                }
            }).ok()
        }
    }
    fn submit_submission(&self, mut submission: Submission) {
        submission.save(self);
333
334

        let mut grade = self.get_grade_by_submission(submission.id.unwrap());
335
336
        if grade.grade.is_none() || submission.grade > grade.grade.unwrap() {
            grade.grade = Some(submission.grade);
337
338
339
            grade.validated = false;
            grade.save(self);
        }
340

Robert Czechowski's avatar
Robert Czechowski committed
341
    }
342
    fn get_grade_by_submission(&self, submission_id: u32) -> Grade {
343
        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| {
344
345
346
347
348
349
350
            Grade {
                taskgroup: row.get(0),
                user: row.get(1),
                grade: row.get(2),
                validated: row.get(3),
            }
        }).unwrap_or_else(|_| {
351
            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| {
352
353
354
                Grade {
                    taskgroup: row.get(0),
                    user: row.get(1),
355
                    grade: None,
356
                    validated: false,
357
                }
358
359
360
            }).unwrap() // should this unwrap?
        })
    }
361

362
363
    fn get_contest_groups_grades(&self, session_id: u32, contest_id: u32) -> (Vec<String>, Vec<(Group, Vec<(UserInfo, Vec<Grade>)>)>) {
        let mut stmt = self.prepare("SELECT id, name FROM taskgroup WHERE contest = ?1 ORDER BY id ASC").unwrap();
364
        let tasknames_iter = stmt.query_map(&[&contest_id], |row| {
365
366
367
368
369
370
371
372
            let x : (u32, String) = (row.get(0), row.get(1));
            x
        }).unwrap();

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

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

377
        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
378
379
380
                                     FROM grade
                                     JOIN taskgroup ON grade.taskgroup = taskgroup.id
                                     JOIN session_user AS student ON grade.user = student.id
381
382
383
                                     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();
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
        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();

406
407
        if let Some(t/*Ok((grade, mut group, mut userinfo))*/) = gradeinfo_iter.next() {
            let (grade, mut group, mut userinfo) = t.unwrap();
408

409
410
411
            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();
412

413
414
            let index = grade.taskgroup;
            grades[taskindex[&index]] = grade;
415

416
417
418
            // TODO: does
            // https://stackoverflow.com/questions/29859892/mutating-an-item-inside-of-nested-loops
            // help to spare all these clones?
419

420
421
422
423
424
            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];
425

426
427
428
429
430
431
432
433
434
                        groups.push((group.clone(), users));
                        users = Vec::new();
                    }
                    else if ui.id != userinfo.id {
                        users.push((userinfo.clone(), grades));
                        grades = vec![Default::default(); n_tasks];
                    }
                    let index = g.taskgroup;
                    grades[taskindex[&index]] = g;
435
436
                }
            }
437
438
            users.push((userinfo, grades));
            groups.push((group, users));
439

440
441
442
            (tasknames.iter().map(|(_, name)| name.clone()).collect(), groups)
        }
        else {
443
            (Vec::new(), Vec::new()) // should those be default filled?
444
        }
445
    }
446
447
    fn get_contest_user_grades(&self, session_token: String, contest_id: u32) -> Vec<Grade> {
        let mut stmt = self.prepare("SELECT id, name FROM taskgroup WHERE contest = ?1 ORDER BY id ASC").unwrap();
448
        let tasknames_iter = stmt.query_map(&[&contest_id], |row| {
449
450
451
452
453
454
455
456
            let x : (u32, String) = (row.get(0), row.get(1));
            x
        }).unwrap();

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

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

461
462
463
464
465
466
        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
                                     ORDER BY taskgroup.id ASC").unwrap();
467
        let gradeinfo_iter = stmt.query_map(&[&session_token, &contest_id], |row| {
468
469
470
471
472
473
474
475
476
477
478
479
480
481
            Grade {
                taskgroup: row.get(0),
                user: row.get(1),
                grade: row.get(2),
                validated: row.get(3),
            }
        }).unwrap();

        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;
482
        }
483

484
        grades
485
    }
486

Robert Czechowski's avatar
Robert Czechowski committed
487
488
    fn get_contest_list(&self) -> Vec<Contest> {
        let mut stmt = self.prepare("SELECT id, location, filename, name, duration, public, start_date, end_date FROM contest").unwrap();
489
        let res = stmt.query_map(&[], |row| {
Robert Czechowski's avatar
Robert Czechowski committed
490
491
492
493
494
495
496
497
498
            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),
499
                taskgroups: Vec::new(),
Robert Czechowski's avatar
Robert Czechowski committed
500
501
            }
        }).unwrap().filter_map(|row| {row.ok()}).collect();
502
        res
Robert Czechowski's avatar
Robert Czechowski committed
503
    }
504

Robert Czechowski's avatar
Robert Czechowski committed
505
506
    fn get_contest_by_id(&self, contest_id : u32) -> Contest {
        self.query_row("SELECT location, filename, name, duration, public, start_date, end_date FROM contest WHERE id = ?1", &[&contest_id], |row| {
507
508
509
510
511
512
513
514
515
            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),
516
                taskgroups: Vec::new(),
517
            }
Robert Czechowski's avatar
Robert Czechowski committed
518
        }).unwrap()
519
    }
520

Robert Czechowski's avatar
Robert Czechowski committed
521
    fn get_contest_by_id_complete(&self, contest_id : u32) -> Contest {
522
        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();
523

524
525
526
527
528
529
530
531
532
533
        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),
534
                taskgroups: Vec::new(),
535
536
537
538
            },Taskgroup {
                id: Some(row.get(7)),
                contest: contest_id,
                name: row.get(8),
539
                tasks: Vec::new(),
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
            },Task {
                id: Some(row.get(9)),
                taskgroup: row.get(7),
                location: row.get(10),
                stars: row.get(11),
            })
        }).unwrap();

        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;
                }
556
                taskgroup.tasks.push(t);
557
558
559
560
561
            }
        }
        contest.taskgroups.push(taskgroup);
        contest
    }
562
    fn get_participation(&self, session: &str, contest_id: u32) -> Option<Participation> {
Robert Czechowski's avatar
Robert Czechowski committed
563
564
565
566
567
568
569
570
        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()
    }
571
    fn new_participation(&self, session: &str, contest_id: u32) -> Result<Participation, ()> {
572
        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
573
574
575
576
577
578
579
580
            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();

581
                Ok(self.get_participation(session, contest_id).unwrap()) // TODO: This errors if not logged in …
Robert Czechowski's avatar
Robert Czechowski committed
582
583
            }
        }
584

Robert Czechowski's avatar
Robert Czechowski committed
585
    }
Robert Czechowski's avatar
Robert Czechowski committed
586
    fn get_task_by_id(&self, task_id : u32) -> Task {
587
        self.query_row(
Robert Czechowski's avatar
Robert Czechowski committed
588
            "SELECT location, stars, taskgroup FROM task WHERE id = ?1",
589
590
591
592
593
594
595
596
597
598
            &[&task_id],
            |row| {
                Task {
                    id: Some(task_id),
                    taskgroup: row.get(2),
                    location: row.get(0),
                    stars: row.get(1)
                }
            }).unwrap()
    }
Robert Czechowski's avatar
Robert Czechowski committed
599
    fn get_task_by_id_complete(&self, task_id : u32) -> (Task, Taskgroup, Contest) {
600
        self.query_row(
601
            "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",
602
603
604
605
606
607
608
609
610
611
612
            &[&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),
613
                    tasks: Vec::new(),
614
615
616
617
618
619
620
621
622
623
624
625
626
                }, 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()
    }
627
628

    fn get_submission_to_validate(&self, tasklocation: &str, subtask: Option<&str>) -> u32{
629
630
631
632
633
634
        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(),
        }
    }

Robert Czechowski's avatar
Robert Czechowski committed
635
    fn find_next_submission_to_validate(&self, userid: u32, taskgroupid: u32) {
636
        let (id, validated) : (u32, 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();;
637
638
639
640
        if !validated {
            self.execute("UPDATE submission SET needs_validation = 1 WHERE id = ?1", &[&id]).unwrap();
        }
    }
Robert Czechowski's avatar
Robert Czechowski committed
641
642
643
644
645
646
647
648


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

    fn get_groups(&self, session_id: u32) -> Vec<Group> {
        let mut stmt = self.prepare("SELECT id, name, groupcode, tag FROM usergroup WHERE admin = ?1").unwrap();
649
        let res = stmt.query_map(&[&session_id], |row| {
Robert Czechowski's avatar
Robert Czechowski committed
650
651
652
653
654
655
656
657
658
            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();
659
        res
Robert Czechowski's avatar
Robert Czechowski committed
660
    }
661
    fn get_groups_complete(&self, _session_id: u32) -> Vec<Group> {unimplemented!();}
Robert Czechowski's avatar
Robert Czechowski committed
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
    fn get_group_complete(&self, group_id: u32) -> Option<Group> {
        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

        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, pms_id, pms_school_id 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),
683

Robert Czechowski's avatar
Robert Czechowski committed
684
685
686
687
688
689
690
                username: row.get(6),
                password: row.get(7),
                salt: None,//"".to_string(),
                logincode: row.get(8),
                email: row.get(9),
                email_unconfirmed: row.get(10),
                email_confirmationcode: row.get(11),
691

Robert Czechowski's avatar
Robert Czechowski committed
692
693
694
695
696
697
698
                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),
699

Robert Czechowski's avatar
Robert Czechowski committed
700
701
702
703
704
705
706
707
708
709
710
711
                is_teacher: row.get(19),
                managed_by: Some(group_id),
                pms_id: row.get(20),
                pms_school_id: row.get(21),
            }
        }).unwrap();

        for user in rows {
            group.members.push(user.unwrap());
        }
        Some(group)
    }
712
713
714
715
}


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

720
        let id = match self.get_id() {
721
722
723
724
725
726
727
            Some(id) => {
                conn.execute(
                    "UPDATE task SET taskgroup = ?1, location = ?2, stars = ?3
                     WHERE id = ?4",
                    &[&self.taskgroup, &self.location, &self.stars, &id]).unwrap();
                id
            }
728
            None => {
729
730
731
732
733
734
735
                conn.execute(
                    "INSERT INTO task (taskgroup, location, stars)
                     VALUES (?1, ?2, ?3)",
                    &[&self.taskgroup, &self.location, &self.stars]).unwrap();
                conn.query_row("SELECT last_insert_rowid()", &[], |row| {row.get(0)}).unwrap()
            }
        };
736
        self.set_id(id);
737
738
739
740
741
    }
}


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

746
        let id = match self.get_id() {
747
748
749
750
751
752
753
            Some(id) => {
                conn.execute(
                    "UPDATE taskgroup SET contest = ?1, name = ?2
                     WHERE id = ?3",
                    &[&self.contest, &self.name, &id]).unwrap();
                id
            }
754
            None => {
755
756
757
758
759
760
761
                conn.execute(
                    "INSERT INTO taskgroup (contest, name)
                     VALUES (?1, ?2)",
                    &[&self.contest, &self.name]).unwrap();
                conn.query_row("SELECT last_insert_rowid()", &[], |row| {row.get(0)}).unwrap()
            }
        };
762
        self.set_id(id);
763
764
765
766
767
768
769
770
        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
771
    fn save(&mut self, conn: &Connection) {
772
        conn.query_row("SELECT id FROM contest WHERE location = ?1 AND filename = ?2", &[&self.location, &self.filename], |row| {row.get(0)})
773
            .and_then(|id| { self.set_id(id); Ok(()) }).unwrap_or(()); // Err means no entry yet and is expected result
774

775
        let id = match self.get_id() {
776
777
778
779
780
781
782
783
784
785
            Some(id) => {
                conn.execute(
                    "UPDATE contest SET location = ?1,filename = ?2,
                     name = ?3, duration = ?4, public = ?5, start_date = ?6,
                     end_date = ?7 WHERE id = ?8",
                    &[&self.location, &self.filename, &self.name,
                      &self.duration, &self.public, &self.start, &self.end,
                      &id]).unwrap();
                id
            }
786
            None => {
787
788
789
790
791
792
793
794
                conn.execute(
                    "INSERT INTO contest (location, filename, name, duration, public, start_date, end_date)
                     VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)",
                    &[&self.location, &self.filename, &self.name,
                      &self.duration, &self.public, &self.start, &self.end]).unwrap();
                conn.query_row("SELECT last_insert_rowid()", &[], |row| {row.get(0)}).unwrap()
            }
        };
795
        self.set_id(id);
796
797
798
799
800
801
802
803
        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
804
    fn save(&mut self, conn: &Connection) {
805
806
807
808
809
        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();
    }
}

impl MedalObject<Connection> for Participation {
Robert Czechowski's avatar
Robert Czechowski committed
810
    fn save(&mut self, conn: &Connection) {
811
812
813
814
        conn.execute("INSERT INTO participation (contest, user, start_date) VALUES (?1, ?2, ?3)", &[&self.contest, &self.user, &self.start]).unwrap();
    }
}

Robert Czechowski's avatar
Robert Czechowski committed
815
816
817

impl MedalObject<Connection> for Submission {
    fn save(&mut self, conn: &Connection) {
818
        match self.get_id() {
819
            Some(_id) =>
Robert Czechowski's avatar
Robert Czechowski committed
820
821
822
                unimplemented!(),
            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();
823
                self.set_id(conn.query_row("SELECT last_insert_rowid()", &[], |row| {row.get(0)}).unwrap());
Robert Czechowski's avatar
Robert Czechowski committed
824
825
826
827
828
829
830
            }
        }
    }
}

impl MedalObject<Connection> for Group {
    fn save(&mut self, conn: &Connection) {
831
        match self.get_id() {
832
            Some(_id) =>
Robert Czechowski's avatar
Robert Czechowski committed
833
834
835
                unimplemented!(),
            None => {
                conn.execute("INSERT INTO usergroup (name, groupcode, tag, admin) VALUES (?1, ?2, ?3, ?4)", &[&self.name, &self.groupcode, &self.tag, &self.admin]).unwrap();
836
                self.set_id(conn.query_row("SELECT last_insert_rowid()", &[], |row| {row.get(0)}).unwrap());
Robert Czechowski's avatar
Robert Czechowski committed
837
838
839
840
            }
        }
    }
}