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

use self::rusqlite::Connection;

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

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

10

11
use time;
12
use self::time::{Timespec, Duration};
13

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

16
17
use ::functions; // todo: remove (usertype in db)

18

Robert Czechowski's avatar
Robert Czechowski committed
19
fn hash_password(password: &str, hash: &str) -> String {
20
   password.to_string()
Robert Czechowski's avatar
Robert Czechowski committed
21
22
}

23
impl MedalConnection for Connection {
24
25
    fn create(file: &Path) -> Connection {
        Connection::open(file).unwrap()
26
27
28
29
30
31
    }

    fn dbtype(&self) -> &'static str {
        return "sqlite";
    }

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

36
37
38
        let mut stmt = self.prepare("SELECT name FROM migrations WHERE name = ?1").unwrap();
        stmt.exists(&[&name]).unwrap()
    }
39
40

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

43
        let tx = self.transaction().unwrap();
44

45
46
        tx.execute(&contents, &[]).unwrap();
        tx.execute("INSERT INTO migrations (name) VALUES (?1)", &[&name]).unwrap();
47

48
        tx.commit().unwrap();
49

50
51
52
        println!("OK.");
    }

53
    // fn get_session<T: ToSql>(&self, key: T, keyname: &str) -> Option<SessionUser> {
54
    fn get_session(&self, key: &str) -> Option<SessionUser> {
55
        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 FROM session_user WHERE session_token = ?1", &[&key], |row| {
56
57
            SessionUser {
                id: row.get(0),
58
                session_token: Some(key.to_string()),
59
60
61
62
                csrf_token: row.get(1),
                last_login: row.get(2),
                last_activity: row.get(3),
                permanent_login: row.get(4),
63

64
65
                username: row.get(5),
                password: row.get(6),
Robert Czechowski's avatar
Robert Czechowski committed
66
                salt: None,//"".to_string(),
67
68
69
70
                logincode: row.get(7),
                email: row.get(8),
                email_unconfirmed: row.get(9),
                email_confirmationcode: row.get(10),
71

72
73
74
75
76
77
78
                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),
79

80
81
82
83
84
                is_teacher: row.get(18),
                managed_by: row.get(19),
                pms_id: row.get(20),
                pms_school_id: row.get(21),
            }
85
86
        });
        match res {
87
88
89
90
91
92
93
94
95
96
97
98
99
100
            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()
101
        }
102
    }
Robert Czechowski's avatar
Robert Czechowski committed
103
104
105
106
    fn save_session(&self, session: SessionUser) {
        self.execute("UPDATE session_user SET
                      username = ?1,
                      password = ?2,
107
108
109
110
111
                      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
112
    }
Robert Czechowski's avatar
Robert Czechowski committed
113
    fn new_session(&self) -> SessionUser {
114
115
        let session_token = "123".to_string();
        let csrf_token = "123".to_string();
116

117
118
        self.execute("INSERT INTO session_user (session_token, csrf_token, permanent_login, is_teacher)
                      VALUES (?1, ?2, 0, 0)",
119
120
            &[&session_token, &csrf_token]).unwrap();
        let id = self.query_row("SELECT last_insert_rowid()", &[], |row| {row.get(0)}).unwrap();
121

122
123
        SessionUser::minimal(id, session_token, csrf_token)
    }
124
125
    fn get_session_or_new(&self, key: &str) -> SessionUser {
        self.get_session(&key).unwrap_or_else(|| self.new_session())
126
    }
127

128
    fn get_user_by_id(&self, user_id: u32) -> Option<SessionUser> {
129
130
131
132
133
134
135
136
        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),
137

138
139
140
141
142
143
144
                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),
145

146
147
148
149
150
151
152
                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),
153

154
155
156
157
158
159
                is_teacher: row.get(18),
                managed_by: row.get(19),
                pms_id: row.get(20),
                pms_school_id: row.get(21),
            }
        });
160
161
162
163
164
165
        res.ok()
    }
        
    fn get_user_and_group_by_id(&self, user_id: u32) -> Option<(SessionUser, Option<Group>)> {
        let session = self.get_user_by_id(user_id)?;
        
166
167
168
169
170
171
        println!("A");
        let group_id = match session.managed_by {
            Some(id) => id,
            None => return Some((session, None))
        };
        println!("B");
172

173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
        let res = self.query_row("SELECT name, groupcode, tag, admin FROM usergroup WHERE id = ?1", &[&group_id], |row| {
            println!("D");
            Group {
                id: Some(group_id),
                name: row.get(0),
                groupcode: row.get(1),
                tag: row.get(2),
                admin: row.get(3),
                members: Vec::new(),
            }
        });
        println!("C");
        match res {
            Ok(group) => Some((session, Some(group))),
            _ => Some((session, None))
        }
    }
190

191
    fn login(&self, session: Option<&str>, username: &str, password: &str) -> Result<String,()> {
Robert Czechowski's avatar
Robert Czechowski committed
192
193
194
195
196
        println!("a {} {}", username, password);
        match self.query_row(
            "SELECT id, password, salt FROM session_user WHERE username = ?1",
            &[&username],
            |row| -> (u32, Option<String>, Option<String>) {
197
                (row.get(0), row.get(1), row.get(2))
Robert Czechowski's avatar
Robert Czechowski committed
198
199
200
            }) {
            Ok((id, password_hash, salt)) => {
                //println!("{}, {}", password, password_hash.unwrap());
201
                if hash_password(&password, &salt.unwrap()) == password_hash.unwrap() { // TODO: fail more pleasantly
Robert Czechowski's avatar
Robert Czechowski committed
202
                    // Login okay, update session now!
203

204
205
                    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
206
                    let now = time::get_time();
207

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

Robert Czechowski's avatar
Robert Czechowski committed
210
211
212
                    Ok(session_token)
                }
                else {println!("b");Err(()) }
213

Robert Czechowski's avatar
Robert Czechowski committed
214
215
216
            },
            _ => {println!("c"); Err(()) }
        }
217
218
    }
    fn login_with_code(&self, session: Option<&str>, logincode: &str) -> Result<String,()> {
Robert Czechowski's avatar
Robert Czechowski committed
219
220
221
222
223
        println!("a {}", logincode);
        match self.query_row(
            "SELECT id FROM session_user WHERE logincode = ?1",
            &[&logincode],
            |row| -> u32 {
224
                row.get(0)
Robert Czechowski's avatar
Robert Czechowski committed
225
226
227
            }) {
            Ok(id) => {
                // Login okay, update session now!
228

229
230
                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
231
                let now = time::get_time();
232

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

Robert Czechowski's avatar
Robert Czechowski committed
235
236
237
238
239
                Ok(session_token)
            },
            _ => {println!("c"); Err(()) }
        }
    }
240
241

    fn login_foreign(&self, session: Option<&str>, foreign_id: u32, foreign_type: functions::UserType, firstname: &str, lastname: &str) -> Result<String,()> {
242
243
        let session_token: String = thread_rng().sample_iter(&Alphanumeric).take(10).collect();
        let csrf_token: String = thread_rng().sample_iter(&Alphanumeric).take(10).collect();
244
        let now = time::get_time();
245

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

                Ok(session_token)
255
256
257
258
            },
            // 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();
259

260
261
262
263
                Ok(session_token)
            }
        }
    }
Robert Czechowski's avatar
Robert Czechowski committed
264

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

276
277
278
                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
279
                    .filter(|x| {let x = *x; !(x == 'l' || x == 'I' || x == '1' || x == 'O' || x == 'o' || x == '0')})
280
281
                    .take(9).collect();
                // todo: check for collisions
Robert Czechowski's avatar
Robert Czechowski committed
282
                let now = time::get_time();
283

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

Robert Czechowski's avatar
Robert Czechowski committed
286
287
288
289
290
                Ok(session_token)
            },
            _ => {println!("c"); Err(()) }
        }
    }
291
292

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

296
297

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

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

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

360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
    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();
        let mut tasknames_iter = stmt.query_map(&[&contest_id], |row| {
            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();
        let mut index = 0;
        for (i, _) in &tasknames {
            taskindex.insert(*i, index);
            index = index + 1
        }
       
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
408
        if let Some(t/*Ok((grade, mut group, mut userinfo))*/) = gradeinfo_iter.next() {
            let (grade, mut group, mut userinfo) = t.unwrap();
            println!("yes");
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
            let mut grades: Vec<Grade> = vec![Default::default(); n_tasks];
            let mut users: Vec<(UserInfo, Vec<Grade>)> = Vec::new();
            let mut groups: Vec<(Group, Vec<(UserInfo, Vec<Grade>)>)> = Vec::new();
            
            let index = grade.taskgroup;
            grades[taskindex[&index]] = grade;
            
            // TODO: does
            // https://stackoverflow.com/questions/29859892/mutating-an-item-inside-of-nested-loops
            // help to spare all these clones?
            
            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];
                        
                        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
439
            users.push((userinfo, grades));
            groups.push((group, users));
            
440
441
442
443
            (tasknames.iter().map(|(_, name)| name.clone()).collect(), groups)
        }
        else {
            println!("no");
444
            (Vec::new(), Vec::new()) // should those be default filled?
445
        }
446
    }
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
    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();
        let mut tasknames_iter = stmt.query_map(&[&contest_id], |row| {
            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();
        let mut index = 0;
        for (i, _) in &tasknames {
            taskindex.insert(*i, index);
            index = index + 1
        }
       
        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();
        let mut 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();

        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;
        }
        
        grades
        
        /*else {
            println!("no");
            Vec::new()
        }*/
    }
494

Robert Czechowski's avatar
Robert Czechowski committed
495
496
497
498
499
500
501
502
503
504
505
506
    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();
        let rows = 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),
507
                taskgroups: Vec::new(),
Robert Czechowski's avatar
Robert Czechowski committed
508
509
510
511
            }
        }).unwrap().filter_map(|row| {row.ok()}).collect();
        rows
    }
512

Robert Czechowski's avatar
Robert Czechowski committed
513
514
    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| {
515
516
517
518
519
520
521
522
523
            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),
524
                taskgroups: Vec::new(),
525
            }
Robert Czechowski's avatar
Robert Czechowski committed
526
        }).unwrap()
527
    }
528

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

532
533
534
535
536
537
538
539
540
541
        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),
542
                taskgroups: Vec::new(),
543
544
545
546
            },Taskgroup {
                id: Some(row.get(7)),
                contest: contest_id,
                name: row.get(8),
547
                tasks: Vec::new(),
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
            },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;
                }
564
                taskgroup.tasks.push(t);
565
566
567
568
569
            }
        }
        contest.taskgroups.push(taskgroup);
        contest
    }
570
    fn get_participation(&self, session: &str, contest_id: u32) -> Option<Participation> {
Robert Czechowski's avatar
Robert Czechowski committed
571
572
573
574
575
576
577
578
        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()
    }
579
    fn new_participation(&self, session: &str, contest_id: u32) -> Result<Participation, ()> {
Robert Czechowski's avatar
Robert Czechowski committed
580
581
582
583
584
585
586
587
588
        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], |row| {()}) {
            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();

589
                Ok(self.get_participation(session, contest_id).unwrap()) // TODO: This errors if not logged in …
Robert Czechowski's avatar
Robert Czechowski committed
590
591
            }
        }
592

Robert Czechowski's avatar
Robert Czechowski committed
593
    }
Robert Czechowski's avatar
Robert Czechowski committed
594
    fn get_task_by_id(&self, task_id : u32) -> Task {
595
        self.query_row(
Robert Czechowski's avatar
Robert Czechowski committed
596
            "SELECT location, stars, taskgroup FROM task WHERE id = ?1",
597
598
599
600
601
602
603
604
605
606
            &[&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
607
    fn get_task_by_id_complete(&self, task_id : u32) -> (Task, Taskgroup, Contest) {
608
        println!("{}!!", task_id);
609
        self.query_row(
610
            "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",
611
612
613
614
615
616
617
618
619
620
621
            &[&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),
622
                    tasks: Vec::new(),
623
624
625
626
627
628
629
630
631
632
633
634
635
                }, 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()
    }
636
637

    fn get_submission_to_validate(&self, tasklocation: &str, subtask: Option<&str>) -> u32{
638
639
640
641
642
643
        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
644
    fn find_next_submission_to_validate(&self, userid: u32, taskgroupid: u32) {
645
        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();;
646
647
648
649
        if !validated {
            self.execute("UPDATE submission SET needs_validation = 1 WHERE id = ?1", &[&id]).unwrap();
        }
    }
Robert Czechowski's avatar
Robert Czechowski committed
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670


    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();
        let rows = 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();
        rows
    }
    fn get_groups_complete(&self, session_id: u32) -> Vec<Group> {unimplemented!();}
Robert Czechowski's avatar
Robert Czechowski committed
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
    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),
692

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

Robert Czechowski's avatar
Robert Czechowski committed
701
702
703
704
705
706
707
                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),
708

Robert Czechowski's avatar
Robert Czechowski committed
709
710
711
712
713
714
715
716
717
718
719
720
                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)
    }
721
722
723
724
}


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

729
        let id = match self.get_id() {
730
731
732
733
734
735
736
            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
            }
737
            None => {
738
739
740
741
742
743
744
                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()
            }
        };
745
        self.set_id(id);
746
747
748
749
750
    }
}


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

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

784
        let id = match self.get_id() {
785
786
787
788
789
790
791
792
793
794
            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
            }
795
            None => {
796
797
798
799
800
801
802
803
                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()
            }
        };
804
        self.set_id(id);
805
806
807
808
809
810
811
812
        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
813
    fn save(&mut self, conn: &Connection) {
814
815
816
817
818
        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
819
    fn save(&mut self, conn: &Connection) {
820
821
822
823
        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
824
825
826

impl MedalObject<Connection> for Submission {
    fn save(&mut self, conn: &Connection) {
827
        match self.get_id() {
828
            Some(_id) =>
Robert Czechowski's avatar
Robert Czechowski committed
829
830
831
                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();
832
                self.set_id(conn.query_row("SELECT last_insert_rowid()", &[], |row| {row.get(0)}).unwrap());
Robert Czechowski's avatar
Robert Czechowski committed
833
834
835
836
837
838
839
            }
        }
    }
}

impl MedalObject<Connection> for Group {
    fn save(&mut self, conn: &Connection) {
840
        match self.get_id() {
841
            Some(_id) =>
Robert Czechowski's avatar
Robert Czechowski committed
842
843
844
                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();
845
                self.set_id(conn.query_row("SELECT last_insert_rowid()", &[], |row| {row.get(0)}).unwrap());
Robert Czechowski's avatar
Robert Czechowski committed
846
847
848
849
            }
        }
    }
}
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864

    
pub trait SetPassword {
    fn set_password(&mut self, &str) -> Option<()>;
}
impl SetPassword for SessionUser {
    fn set_password(&mut self, password: &str) -> Option<()> {
        let salt = "blub";
        let hash = hash_password(password, salt);

        self.password = Some(hash);
        self.salt = Some(salt.into());
        Some(())
    }
}