db_conn_sqlite.rs 15 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::*;

Robert Czechowski's avatar
Robert Czechowski committed
8
9
10
11
12
13
use rand::{thread_rng, Rng};

fn hash_password(password: &str, hash: &str) -> String {
   password.to_string() 
}

14
15
16
17
18
19
20
21
22
impl MedalConnection for Connection {
    fn create() -> Connection {
        Connection::open("blub.db").unwrap()
    }

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

Robert Czechowski's avatar
Robert Czechowski committed
23
    fn migration_already_applied(&self, name: &str) -> bool {
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
        let create_string = "CREATE TABLE IF NOT EXISTS migrations (name TEXT PRIMARY KEY);";
        self.execute(create_string, &[]).unwrap();
        
        let mut stmt = self.prepare("SELECT name FROM migrations WHERE name = ?1").unwrap();
        stmt.exists(&[&name]).unwrap()
    }
    
    fn apply_migration(&mut self, name: &str, contents: String) {
        print!("Applying migration `{}` … ", name);
        
        let tx = self.transaction().unwrap();
        
        tx.execute(&contents, &[]).unwrap();
        tx.execute("INSERT INTO migrations (name) VALUES (?1)", &[&name]).unwrap();
        
        tx.commit().unwrap();
        
        println!("OK.");
    }

Robert Czechowski's avatar
Robert Czechowski committed
44
    fn get_session(&self, key: String) -> Option<SessionUser> {
45
46
47
48
49
50
51
52
53
54
55
        self.query_row("SELECT id, session_token, csrf_token, last_login, last_activity, permanent_login, username, password, logincode, email, email_unconfirmed, email_confirmation_code, 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| {
            SessionUser {
                id: row.get(0),
                session_token: Some(key.clone()),
                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),
Robert Czechowski's avatar
Robert Czechowski committed
56
                salt: None,//"".to_string(),
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
                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),
                pms_id: row.get(20),
                pms_school_id: row.get(21),
            }
        }).ok()
    }
Robert Czechowski's avatar
Robert Czechowski committed
77
    fn new_session(&self) -> SessionUser {
78
79
80
81
82
83
84
85
86
87
        let session_token = "123".to_string();
        let csrf_token = "123".to_string();
        
        self.execute("INSERT INTO session_user (session_token, csrf_token)
                      VALUES (?1, ?2)",
            &[&session_token, &csrf_token]).unwrap();
        let id = self.query_row("SELECT last_insert_rowid()", &[], |row| {row.get(0)}).unwrap();
        
        SessionUser::minimal(id, session_token, csrf_token)
    }
Robert Czechowski's avatar
Robert Czechowski committed
88
    fn get_session_or_new(&self, key: String) -> SessionUser {
89
90
91
        self.get_session(key).unwrap_or_else(|| self.new_session())
    }

Robert Czechowski's avatar
Robert Czechowski committed
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
    fn login(&self, session: Option<String>, username: String, password: String) -> Result<String,()> {
        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>) {
                (row.get(0), row.get(1), row.get(2))    
            }) {
            Ok((id, password_hash, salt)) => {
                //println!("{}, {}", password, password_hash.unwrap());
                if (hash_password(&password, &salt.unwrap()) == password_hash.unwrap()) {
                    // Login okay, update session now!
                    
                    let session_token: String = thread_rng().gen_ascii_chars().take(10).collect();
                    let csrf_token: String = thread_rng().gen_ascii_chars().take(10).collect();
                    
                    self.execute("UPDATE session_user SET session_token = ?1, csrf_token = ?2, last_login = date('now'), last_activity = date('now') WHERE id = ?3", &[&session_token, &csrf_token, &id]).unwrap();
                    
                    Ok(session_token)
                }
                else {println!("b");Err(()) }
                
            },
            _ => {println!("c"); Err(()) }
        }
        
    }
    fn login_with_code(&self, session: Option<String>, logincode: String) -> Result<SessionUser,()> {unimplemented!()}
    fn logout(&self, session: String) {
        self.execute("UPDATE session_user SET session_token = NULL WHERE id = ?1", &[&session]).unwrap();
122
123
124
    }

    
Robert Czechowski's avatar
Robert Czechowski committed
125
126
    fn load_submission(&self, session: &SessionUser, task: String, subtask: Option<String>) -> Submission {unimplemented!()}
    fn submit_submission(&self, session: &SessionUser, task: String, subtask: Option<String>, submission: Submission) {unimplemented!()}
127

Robert Czechowski's avatar
Robert Czechowski committed
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
    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),
                taskgroups: Vec::new(),            
            }
        }).unwrap().filter_map(|row| {row.ok()}).collect();
        rows
    }
    
    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| {
148
149
150
151
152
153
154
155
156
157
158
159
160
            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(),            
            }
        }).unwrap()            
    }
Robert Czechowski's avatar
Robert Czechowski committed
161
162
    
    fn get_contest_by_id_complete(&self, contest_id : u32) -> Contest {
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
        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();
        
        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();

        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;
                }
                taskgroup.tasks.push(t);            
            }
        }
        contest.taskgroups.push(taskgroup);
        contest
    }
Robert Czechowski's avatar
Robert Czechowski committed
203
    fn get_task_by_id(&self, task_id : u32) -> Task {
204
        self.query_row(
Robert Czechowski's avatar
Robert Czechowski committed
205
            "SELECT location, stars, taskgroup FROM task WHERE id = ?1",
206
207
208
209
210
211
212
213
214
215
            &[&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
216
    fn get_task_by_id_complete(&self, task_id : u32) -> (Task, Taskgroup, Contest) {
217
        self.query_row(
Robert Czechowski's avatar
Robert Czechowski committed
218
            "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 task JOIN taskgroup ON task.taskgroup = taskgroup.id JOIN contest ON taskgroup.contest = contest.id WHERE task.id = ?1",
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
            &[&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),
                    tasks: Vec::new(),  
                }, 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()
    }
    
Robert Czechowski's avatar
Robert Czechowski committed
245
    fn get_submission_to_validate(&self, tasklocation: String, subtask: Option<String>) -> u32{
246
247
248
249
250
251
        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
252
    fn find_next_submission_to_validate(&self, userid: u32, taskgroupid: u32) {
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
        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.user = ?2 ORDER BY value DESC id DESC LIMIT 1", &[&taskgroupid, &userid], |row| {(row.get(0), row.get(1))}).unwrap();;
        if !validated {
            self.execute("UPDATE submission SET needs_validation = 1 WHERE id = ?1", &[&id]).unwrap();
        }
    }
}


impl MedalObject<Connection> for Task {
    fn save(&mut self, conn: &mut Connection) {
        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.setId(id); Ok(()) });
        
        let id = match self.getId() {
            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
            }
            None => {                
                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()
            }
        };
        self.setId(id);
    }
}


impl MedalObject<Connection> for Taskgroup {
    fn save(&mut self, conn: &mut Connection) {
        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.setId(id); Ok(()) });
        
        let id = match self.getId() {
            Some(id) => {
                conn.execute(
                    "UPDATE taskgroup SET contest = ?1, name = ?2
                     WHERE id = ?3",
                    &[&self.contest, &self.name, &id]).unwrap();
                id
            }
            None => {                
                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()
            }
        };
        self.setId(id);
        for mut task in &mut self.tasks {
            task.taskgroup = id;
            task.save(conn);
        }
    }
}

impl MedalObject<Connection> for Contest {
    fn save(&mut self, conn: &mut Connection) {
        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.setId(id); Ok(()) });
        
        let id = match self.getId() {
            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
            }
            None => {                
                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()
            }
        };
        self.setId(id);
        for mut taskgroup in &mut self.taskgroups {
            taskgroup.contest = id;
            taskgroup.save(conn);
        }
    }
}

impl MedalObject<Connection> for Grade {
    fn save(&mut self, conn: &mut Connection) {
        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 {
    fn save(&mut self, conn: &mut Connection) {
        conn.execute("INSERT INTO participation (contest, user, start_date) VALUES (?1, ?2, ?3)", &[&self.contest, &self.user, &self.start]).unwrap();
    }
}