1#![cfg(feature = "rusqlite")]
29
30extern crate rusqlite;
31
32use config;
33use rusqlite::Connection;
34use time;
35use time::Duration;
36
37use db_conn::{MedalConnection, MedalObject, SignupResult};
38use db_objects::*;
39use helpers;
40
41fn gen_tosql_vector() -> Vec<&'static dyn rusqlite::types::ToSql> { Vec::new() }
42
43trait Queryable {
44 fn query_map_one<T, F>(&self, sql: &str, params: &[&dyn rusqlite::types::ToSql], f: F)
45 -> rusqlite::Result<Option<T>>
46 where F: FnOnce(&rusqlite::Row) -> T;
47 fn query_map_many<T, F>(&self, sql: &str, params: &[&dyn rusqlite::types::ToSql], f: F) -> rusqlite::Result<Vec<T>>
48 where F: FnMut(&rusqlite::Row) -> T;
49 fn exists(&self, sql: &str, params: &[&dyn rusqlite::types::ToSql]) -> bool;
50 fn get_last_id(&self) -> Option<i32>;
51
52 fn reconnect_concrete(config: &config::Config) -> Self;
53}
54
55impl Queryable for Connection {
56 fn query_map_one<T, F>(&self, sql: &str, params: &[&dyn rusqlite::types::ToSql], f: F)
57 -> rusqlite::Result<Option<T>>
58 where F: FnOnce(&rusqlite::Row) -> T {
59 let mut stmt = self.prepare(sql)?;
60 let mut rows = stmt.query(params)?;
61 match rows.next() {
62 None => Ok(None),
63 Some(Err(e)) => Err(e),
64 Some(Ok(row)) => Ok(Some(f(&row))),
65 }
66 }
67
68 fn query_map_many<T, F>(&self, sql: &str, params: &[&dyn rusqlite::types::ToSql], f: F) -> rusqlite::Result<Vec<T>>
69 where F: FnMut(&rusqlite::Row) -> T {
70 let mut stmt = self.prepare(sql)?;
71 let rows = stmt.query_map(params, f)?;
72 Ok(rows.map(|x| x.unwrap()).collect())
73 }
74
75 fn exists(&self, sql: &str, params: &[&dyn rusqlite::types::ToSql]) -> bool {
76 let mut stmt = self.prepare(sql).unwrap();
77 stmt.exists(params).unwrap()
78 }
79
80 fn get_last_id(&self) -> Option<i32> { self.query_row("SELECT last_insert_rowid()", &[], |row| row.get(0)).ok() }
81
82 fn reconnect_concrete(config: &config::Config) -> Self {
83 rusqlite::Connection::open(config.database_file.clone().unwrap()).unwrap()
84 }
85}
86
87impl MedalObject<Connection> for Grade {
88 fn save(&mut self, conn: &Connection) {
89 let query = "INSERT OR REPLACE INTO grade (taskgroup, session, grade, validated)
90 VALUES (?1, ?2, ?3, ?4)";
91
92 conn.execute(query, &[&self.taskgroup, &self.user, &self.grade, &self.validated]).unwrap();
93 }
94}
95impl MedalObject<Connection> for Submission {
110 fn save(&mut self, conn: &Connection) {
111 match self.get_id() {
112 Some(_id) => unimplemented!(),
113 None => {
114 let query = "INSERT INTO submission (task, session, grade, validated, nonvalidated_grade,
115 subtask_identifier, value, date, needs_validation,
116 autosave, latest, highest_grade_latest)
117 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12)";
118 conn.execute(query,
119 &[&self.task,
120 &self.user,
121 &self.grade,
122 &self.validated,
123 &self.nonvalidated_grade,
124 &self.subtask_identifier,
125 &self.value,
126 &self.date,
127 &self.needs_validation,
128 &self.autosave,
129 &self.latest,
130 &self.highest_grade_latest])
131 .unwrap();
132 self.set_id(conn.get_last_id().unwrap());
133 }
134 }
135 }
136}
137
138impl MedalObject<Connection> for Participation {
139 fn save(&mut self, conn: &Connection) {
140 let query = "INSERT INTO participation (contest, session, start_date, team)
141 VALUES (?1, ?2, ?3, ?4)";
142 conn.execute(query, &[&self.contest, &self.user, &self.start, &self.team]).unwrap();
143 }
144}
145
146impl MedalObject<Connection> for Group {
147 fn save(&mut self, conn: &Connection) {
148 match self.get_id() {
149 Some(id) => {
150 let query = "UPDATE usergroup
151 SET name = ?2, groupcode = ?3, tag = ?4
152 WHERE id = ?1";
153 conn.execute(query, &[&id, &self.name, &self.groupcode, &self.tag]).unwrap();
154 }
155 None => {
156 let query = "INSERT INTO usergroup (name, groupcode, tag, group_created)
157 VALUES (?1, ?2, ?3, ?4)";
158 let now = time::get_time();
159 conn.execute(query, &[&self.name, &self.groupcode, &self.tag, &now]).unwrap();
160 self.set_id(conn.get_last_id().unwrap());
161
162 let query = "INSERT INTO usergroup_admin (usergroup, session)
163 VALUES (?1, ?2)";
164 for admin in &self.admins {
165 conn.execute(query, &[&self.get_id(), admin]).unwrap();
166 }
167 }
168 }
169 }
170}
171
172impl MedalObject<Connection> for Task {
173 fn save(&mut self, conn: &Connection) {
174 let query = "SELECT id
175 FROM task
176 WHERE taskgroup = ?1
177 AND location = ?2";
178 conn.query_map_one(query, &[&self.taskgroup, &self.location], |row| row.get(0))
179 .unwrap_or(None)
180 .map(|id| {
181 self.set_id(id);
182 })
183 .unwrap_or(()); let id = match self.get_id() {
186 Some(id) => {
187 let query = "UPDATE task
188 SET taskgroup = ?2, location = ?3, language = ?4, stars = ?5
189 WHERE id = ?1";
190 conn.execute(query, &[&id, &self.taskgroup, &self.location, &self.language, &self.stars]).unwrap();
191 id
192 }
193 None => {
194 let query = "INSERT INTO task (taskgroup, location, language, stars)
195 VALUES (?1, ?2, ?3, ?4)";
196 conn.execute(query, &[&self.taskgroup, &self.location, &self.language, &self.stars]).unwrap();
197 conn.get_last_id().unwrap()
198 }
199 };
200 self.set_id(id);
201 }
202}
203
204impl MedalObject<Connection> for Taskgroup {
205 fn save(&mut self, conn: &Connection) {
206 if let Some(first_task) = self.tasks.first() {
207 let query = "SELECT taskgroup.id
208 FROM taskgroup
209 JOIN task
210 ON task.taskgroup = taskgroup.id
211 WHERE contest = ?1
212 AND task.location = ?2";
213 conn.query_map_one(query, &[&self.contest, &first_task.location], |row| row.get(0))
214 .unwrap_or(None)
215 .map(|id| {
216 self.set_id(id);
217 })
218 .unwrap_or(()); }
220
221 let id = match self.get_id() {
222 Some(id) => {
223 let query = "UPDATE taskgroup
224 SET contest = ?1, name = ?2, active = ?3, positionalnumber = ?4
225 WHERE id = ?5";
226 conn.execute(query, &[&self.contest, &self.name, &self.active, &self.positionalnumber, &id]).unwrap();
227 id
228 }
229 None => {
230 let query = "INSERT INTO taskgroup (contest, name, active, positionalnumber)
231 VALUES (?1, ?2, ?3, ?4)";
232 conn.execute(query, &[&self.contest, &self.name, &self.active, &self.positionalnumber]).unwrap();
233 conn.get_last_id().unwrap()
234 }
235 };
236 self.set_id(id);
237 for task in &mut self.tasks {
238 task.taskgroup = id;
239 task.save(conn);
240 }
241 }
242}
243
244impl MedalObject<Connection> for Contest {
245 fn save(&mut self, conn: &Connection) {
246 let query = "SELECT id
247 FROM contest
248 WHERE location = ?1
249 AND filename = ?2";
250 conn.query_map_one(query, &[&self.location, &self.filename], |row| row.get(0))
251 .unwrap_or(None)
252 .map(|id| {
253 self.set_id(id);
254 })
255 .unwrap_or(()); let id = match self.get_id() {
258 Some(id) => {
259 let query = "DELETE FROM contest_tags
260 WHERE id = ?1";
261 conn.execute(query, &[&id]).unwrap();
262
263 let query = "DELETE FROM contest_requires_contests
264 WHERE id = ?1";
265 conn.execute(query, &[&id]).unwrap();
266
267 let query = "DELETE FROM contest_stickers
268 WHERE id = ?1";
269 conn.execute(query, &[&id]).unwrap();
270
271 let query = "UPDATE contest
272 SET location = ?2,filename = ?3, name = ?4, duration = ?5, public = ?6, start_date = ?7,
273 end_date = ?8, review_start_date = ?9, review_end_date = ?10, min_grade = ?11,
274 max_grade = ?12, positionalnumber = ?13, protected = ?14, requires_login = ?15,
275 requires_contest = ?16, secret = ?17, message = ?18, image = ?19, language = ?20,
276 category = ?21, standalone_task = ?22, max_teamsize = ?23, colour = ?24
277 WHERE id = ?1";
278 conn.execute(query,
279 &[&id,
280 &self.location,
281 &self.filename,
282 &self.name,
283 &self.duration,
284 &self.public,
285 &self.start,
286 &self.end,
287 &self.review_start,
288 &self.review_end,
289 &self.min_grade,
290 &self.max_grade,
291 &self.positionalnumber,
292 &self.protected,
293 &self.requires_login,
294 &None::<String>,
295 &self.secret,
296 &self.message,
297 &self.image,
298 &self.language,
299 &self.category,
300 &self.standalone_task,
301 &self.max_teamsize,
302 &self.colour])
303 .unwrap();
304 id
305 }
306 None => {
307 let query = "INSERT INTO contest (location, filename, name, duration, public, start_date, end_date,
308 review_start_date, review_end_date, min_grade, max_grade,
309 positionalnumber, protected, requires_login, requires_contest, secret,
310 message, image, language, category, standalone_task, max_teamsize,
311 colour)
312 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20, ?21, ?22, ?23)";
313 conn.execute(query,
314 &[&self.location,
315 &self.filename,
316 &self.name,
317 &self.duration,
318 &self.public,
319 &self.start,
320 &self.end,
321 &self.review_start,
322 &self.review_end,
323 &self.min_grade,
324 &self.max_grade,
325 &self.positionalnumber,
326 &self.protected,
327 &self.requires_login,
328 &None::<String>,
329 &self.secret,
330 &self.message,
331 &self.image,
332 &self.language,
333 &self.category,
334 &self.standalone_task,
335 &self.max_teamsize,
336 &self.colour])
337 .unwrap();
338 conn.get_last_id().unwrap()
339 }
340 };
341 self.set_id(id);
342
343 if self.tags.len() > 0 {
344 let tagstring = self.tags.join(",");
345 let query = "INSERT INTO contest_tags (id, tags)
346 VALUES (?1, ?2)";
347 conn.execute(query, &[&id, &tagstring]).unwrap();
348 }
349
350 if self.requires_contests.len() > 0 {
351 let requires_contests_strings: Vec<String> =
352 self.requires_contests
353 .iter()
354 .map(|required_contest| {
355 format!("{}\x1f{}", required_contest.filename, required_contest.required_stars)
356 })
357 .collect();
358 let requires_contests_string = requires_contests_strings.join("\x1e");
360 let query = "INSERT INTO contest_requires_contests (id, requires_contests)
361 VALUES (?1, ?2)";
362 conn.execute(query, &[&id, &requires_contests_string]).unwrap();
363 }
364
365 if self.stickers.len() > 0 {
366 let sticker_strings: Vec<String> =
368 self.stickers.iter().map(|sticker| format!("{}\x1f{}", sticker.0, sticker.1)).collect();
369 let sticker_string = sticker_strings.join("\x1e");
371 let query = "INSERT INTO contest_stickers (id, stickers)
372 VALUES (?1, ?2)";
373 conn.execute(query, &[&id, &sticker_string]).unwrap();
374 }
375
376 for taskgroup in &mut self.taskgroups {
377 taskgroup.contest = id;
378 taskgroup.save(conn);
379 }
380 }
381}
382
383impl MedalConnection for Connection {
384 fn reconnect(config: &config::Config) -> Self { Self::reconnect_concrete(config) }
385
386 fn dbtype(&self) -> &'static str { "sqlite_v2" }
387
388 fn migration_already_applied(&self, name: &str) -> bool {
389 let create_string = "CREATE TABLE IF NOT EXISTS migrations (name TEXT PRIMARY KEY);";
390 self.execute(create_string, &[]).unwrap();
391
392 let query = "SELECT name FROM migrations WHERE name = ?1";
393 self.exists(query, &[&name])
394 }
395
396 fn apply_migration(&mut self, name: &str, contents: &str) {
397 print!("Applying migration `{}` … ", name);
398
399 let tx = self.transaction().unwrap();
400
401 tx.execute_batch(&contents).unwrap();
402 tx.execute("INSERT INTO migrations (name) VALUES (?1)", &[&name]).unwrap();
403
404 tx.commit().unwrap();
405
406 println!("OK.");
407 }
408
409 fn code_exists(&self, code: &str) -> bool {
410 let query = "SELECT (
411 SELECT COUNT(*) FROM session WHERE logincode = ?1
412 ) + (
413 SELECT COUNT(*) FROM usergroup WHERE groupcode = ?1
414 ) AS count";
415
416 let n_rows = self.query_map_one(query, &[&code], |row| row.get::<_, i64>(0) as i32).unwrap().unwrap();
417
418 n_rows > 0
419 }
420
421 fn get_session(&self, key: &str) -> Option<SessionUser> {
423 let query = "SELECT id, csrf_token, last_login, last_activity, account_created, username, password,
424 salt, logincode, email, email_unconfirmed, email_confirmationcode, firstname, lastname,
425 street, zip, city, nation, grade, sex, anonymous, additional_contest_time,
426 data_protection_clearance, is_admin, is_teacher, managed_by, school_name, oauth_provider,
427 oauth_foreign_id
428 FROM session
429 WHERE session_token = ?1";
430 let session = self.query_map_one(query, &[&key], |row| SessionUser { id: row.get(0),
431 session_token: Some(key.to_string()),
432 csrf_token: row.get(1),
433 last_login: row.get(2),
434 last_activity: row.get(3),
435 account_created: row.get(4),
436
437 username: row.get(5),
438 password: row.get(6),
439 salt: row.get(7),
440 logincode: row.get(8),
441 email: row.get(9),
442 email_unconfirmed: row.get(10),
443 email_confirmationcode: row.get(11),
444
445 firstname: row.get(12),
446 lastname: row.get(13),
447 street: row.get(14),
448 zip: row.get(15),
449 city: row.get(16),
450 nation: row.get(17),
451 grade: row.get(18),
452 sex: row.get(19),
453 anonymous: row.get(20),
454 additional_contest_time: row.get(21),
455 data_protection_clearance: row.get(22),
456
457 is_admin: row.get(23),
458 is_teacher: row.get(24),
459 managed_by: row.get(25),
460 school_name: row.get(26),
461
462 oauth_provider: row.get(27),
463 oauth_foreign_id: row.get(28) })
464 .unwrap()?;
465
466 let session_duration = Duration::hours(12);
467 let mimimal_activity_update_duration = Duration::minutes(3);
468 let now = time::get_time();
469
470 if let Some(last_activity) = session.last_activity {
471 if now < last_activity + session_duration {
472 if now > last_activity + mimimal_activity_update_duration {
473 let query = "UPDATE session
474 SET last_activity = ?1
475 WHERE id = ?2";
476 self.execute(query, &[&now, &session.id]).unwrap();
477 }
478 return Some(session);
479 } else {
480 return None;
483 }
484 }
485 None
488 }
489 fn save_session(&self, session: SessionUser) {
490 self.execute("UPDATE session
491 SET username = ?2,
492 password = ?3,
493 salt = ?4,
494 logincode = ?5,
495 firstname = ?6,
496 lastname = ?7,
497 street = ?8,
498 zip = ?9,
499 city = ?10,
500 grade = ?11,
501 sex = ?12,
502 additional_contest_time = ?13,
503 data_protection_clearance = ?14,
504 is_admin = ?15,
505 is_teacher = ?16,
506 managed_by = ?17,
507 email = ?18,
508 email_unconfirmed = ?19
509 WHERE id = ?1",
510 &[&session.id,
511 &session.username,
512 &session.password,
513 &session.salt,
514 &session.logincode,
515 &session.firstname,
516 &session.lastname,
517 &session.street,
518 &session.zip,
519 &session.city,
520 &session.grade,
521 &session.sex,
522 &session.additional_contest_time,
523 &session.data_protection_clearance,
524 &session.is_admin,
525 &session.is_teacher,
526 &session.managed_by,
527 &session.email,
528 &session.email_unconfirmed])
529 .unwrap();
530 }
531 fn new_session(&self, session_token: &str) -> SessionUser {
532 let csrf_token = helpers::make_csrf_token();
533
534 let now = time::get_time();
535 let query = "INSERT INTO session (session_token, csrf_token, last_activity, account_created, grade, sex,
536 is_teacher)
537 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)";
538 self.execute(query, &[&session_token, &csrf_token, &now, &None::<time::Timespec>, &0, &None::<i32>, &false])
539 .unwrap();
540
541 let id = self.get_last_id().expect("Expected to get last row id");
542
543 SessionUser::minimal(id, session_token.to_owned(), csrf_token)
544 }
545 fn session_set_activity_dates(&self, session_id: i32, account_created: Option<time::Timespec>,
546 last_login: Option<time::Timespec>, last_activity: Option<time::Timespec>) {
547 let query = "UPDATE session
548 SET account_created = ?2, last_login = ?3, last_activity = ?4
549 WHERE id = ?1";
550 self.execute(query, &[&session_id, &account_created, &last_login, &last_activity]).unwrap();
551 }
552 fn get_session_or_new(&self, key: &str) -> Result<SessionUser, ()> {
553 fn disable_old_session_and_create_new(conn: &Connection, key: &str) -> Result<SessionUser, ()> {
554 let query = "UPDATE session
555 SET session_token = ?1
556 WHERE session_token = ?2";
557 conn.execute(query, &[&Option::<String>::None, &key]).map_err(|_| ())?;
559 Ok(conn.new_session(&key))
560 }
561
562 if let Some(session) = self.get_session(&key).ensure_alive() {
563 Ok(session)
564 } else {
565 disable_old_session_and_create_new(self, key)
566 }
567 }
568
569 fn get_team_partners_by_contest_and_teamlead(&self, contest_id: i32, teamlead_id: i32) -> Vec<SessionUser> {
570 let query = "SELECT id, session_token, csrf_token, last_login, last_activity, account_created, username,
571 password, salt, logincode, email, email_unconfirmed, email_confirmationcode, firstname,
572 lastname, street, zip, city, nation, grade, sex, anonymous, additional_contest_time,
573 data_protection_clearance, is_admin, is_teacher, managed_by, school_name, oauth_provider,
574 oauth_foreign_id
575 FROM session
576 JOIN participation ON session.id = participation.session
577 WHERE participation.contest = ?1
578 AND participation.team = ?2";
579 self.query_map_many(query, &[&contest_id, &teamlead_id], |row| SessionUser { id: row.get(0),
580 session_token: row.get(1),
581 csrf_token: row.get(2),
582 last_login: row.get(3),
583 last_activity: row.get(4),
584 account_created: row.get(5),
585
586 username: row.get(6),
587 password: row.get(7),
588 salt: row.get(8),
589 logincode: row.get(9),
590 email: row.get(10),
591 email_unconfirmed: row.get(11),
592 email_confirmationcode:
593 row.get(12),
594
595 firstname: row.get(13),
596 lastname: row.get(14),
597 street: row.get(15),
598 zip: row.get(16),
599 city: row.get(17),
600 nation: row.get(18),
601 grade: row.get(19),
602 sex: row.get(20),
603 anonymous: row.get(21),
604 additional_contest_time:
605 row.get(22),
606 data_protection_clearance:
607 row.get(23),
608
609 is_admin: row.get(24),
610 is_teacher: row.get(25),
611 managed_by: row.get(26),
612 school_name: row.get(27),
613
614 oauth_provider: row.get(28),
615 oauth_foreign_id: row.get(29) })
616 .unwrap()
617 }
618
619 fn get_user_by_id(&self, user_id: i32) -> Option<SessionUser> {
620 let query = "SELECT session_token, csrf_token, last_login, last_activity, account_created, username, password,
621 salt, logincode, email, email_unconfirmed, email_confirmationcode, firstname, lastname,
622 street, zip, city, nation, grade, sex, anonymous, additional_contest_time,
623 data_protection_clearance, is_admin, is_teacher, managed_by, school_name, oauth_provider,
624 oauth_foreign_id
625 FROM session
626 WHERE id = ?1";
627 self.query_map_one(query, &[&user_id], |row| SessionUser { id: user_id,
628 session_token: row.get(0),
629 csrf_token: row.get(1),
630 last_login: row.get(2),
631 last_activity: row.get(3),
632 account_created: row.get(4),
633
634 username: row.get(5),
635 password: row.get(6),
636 salt: row.get(7),
637 logincode: row.get(8),
638 email: row.get(9),
639 email_unconfirmed: row.get(10),
640 email_confirmationcode: row.get(11),
641
642 firstname: row.get(12),
643 lastname: row.get(13),
644 street: row.get(14),
645 zip: row.get(15),
646 city: row.get(16),
647 nation: row.get(17),
648 grade: row.get(18),
649 sex: row.get(19),
650 anonymous: row.get(20),
651 additional_contest_time: row.get(21),
652 data_protection_clearance: row.get(22),
653
654 is_admin: row.get(23),
655 is_teacher: row.get(24),
656 managed_by: row.get(25),
657 school_name: row.get(26),
658
659 oauth_provider: row.get(27),
660 oauth_foreign_id: row.get(28) })
661 .ok()?
662 }
663
664 fn get_user_and_group_by_id(&self, user_id: i32) -> Option<(SessionUser, Option<Group>)> {
665 let session = self.get_user_by_id(user_id)?;
666
667 let group_id = match session.managed_by {
668 Some(id) => id,
669 None => return Some((session, None)),
670 };
671
672 let query = "SELECT name, groupcode, tag
673 FROM usergroup
674 WHERE id = ?1";
675 let res = self.query_map_one(query, &[&group_id], |row| Group { id: Some(group_id),
676 name: row.get(0),
677 groupcode: row.get(1),
678 tag: row.get(2),
679 admins: Vec::new(),
680 members: Vec::new() })
681 .ok()?;
682 match res {
683 Some(mut group) => {
684 let query = "SELECT session FROM usergroup_admin WHERE usergroup = ?1 ORDER BY session";
685 group.admins = self.query_map_many(query, &[&group_id], |row| row.get(0)).unwrap();
686 Some((session, Some(group)))
687 }
688 _ => Some((session, None)),
689 }
690 }
691
692 fn get_user_and_group_by_logincode(&self, logincode: &str) -> Option<(SessionUser, Option<Group>)> {
693 if logincode.len() == 0 {
694 return None;
695 }
696
697 let query = "SELECT id
698 FROM session
699 WHERE logincode = ?1";
700
701 let user_id = self.query_map_one(query, &[&logincode], |row| row.get(0)).ok()??;
702
703 self.get_user_and_group_by_id(user_id)
704 }
705
706 fn set_webauthn_passkey_registration(&self, user_id: i32, passkey_registration: &str) {
707 let query = "UPDATE session
708 SET webauthn_passkey_registration = ?2
709 WHERE id = ?1";
710 self.execute(query, &[&user_id, &passkey_registration]).unwrap();
711 }
712
713 fn get_webauthn_passkey_registration(&self, user_id: i32) -> Option<String> {
714 let query = "SELECT webauthn_passkey_registration
715 FROM session
716 WHERE id = ?1";
717 self.query_map_one(query, &[&user_id], |row| row.get(0)).unwrap()
718 }
719
720 fn check_webauthn_cred_id_in_use(&self, cred_id: &str) -> bool {
721 let query = "SELECT id
722 FROM webauthn_passkey
723 WHERE cred_id = ?1";
724 self.exists(query, &[&cred_id])
725 }
726
727 fn add_webauthn_passkey(&self, user_id: i32, cred_id: &str, passkey: &str, name: &str) -> i32 {
728 let now = time::get_time();
729
730 let insert = "INSERT INTO webauthn_passkey (session, created, name, passkey, cred_id)
731 VALUES (?1, ?2, ?3, ?4, ?5)";
732 self.execute(insert, &[&user_id, &now, &name, &passkey, &cred_id]).unwrap();
733
734 let passkey_id = self.get_last_id().unwrap();
735
736 let update = "UPDATE session
737 SET webauthn_passkey_registration = NULL
738 WHERE id = ?1";
739 self.execute(update, &[&user_id]).unwrap();
740
741 passkey_id
742 }
743
744 fn delete_webauthn_passkey(&self, user_id: i32, passkey_id: i32) {
745 let query = "DELETE FROM webauthn_passkey
746 WHERE session = ?1
747 AND id = ?2";
748 self.execute(query, &[&user_id, &passkey_id]).unwrap();
749 }
750
751 fn get_webauthn_passkey_names_for_user(&self, user_id: i32) -> Vec<(i32, String)> {
752 let query = "SELECT id, name
753 FROM webauthn_passkey
754 WHERE session = ?1";
755 self.query_map_many(query, &[&user_id], |row| (row.get(0), row.get(1))).unwrap()
756 }
757
758 fn get_all_webauthn_passkeys(&self) -> Vec<String> {
759 let query = "SELECT passkey
760 FROM webauthn_passkey";
761 self.query_map_many(query, &[], |row| row.get(0)).unwrap()
762 }
763
764 fn get_all_webauthn_credentials(&self) -> Vec<String> {
765 let query = "SELECT cred_id
766 FROM webauthn_passkey";
767 self.query_map_many(query, &[], |row| row.get(0)).unwrap()
768 }
769
770 fn store_webauthn_auth_challenge(&self, challenge: &str, authentication: &str) -> i32 {
771 let delete = "DELETE FROM webauthn_challenge
772 WHERE created < ?1";
773
774 let now = time::get_time();
775 let max_challenge_duration = Duration::minutes(3);
776 let before = now - max_challenge_duration;
777
778 self.execute(delete, &[&before]).unwrap();
779
780 let query = "INSERT INTO webauthn_challenge (created, public_challenge, private_authentication)
781 VALUES (?1, ?2, ?3)";
782
783 self.execute(query, &[&now, &challenge, &authentication]).unwrap();
784
785 self.get_last_id().unwrap()
786 }
787
788 fn get_webauthn_auth_challenge_by_id(&self, auth_id: i32) -> Option<String> {
789 let query = "SELECT private_authentication
790 FROM webauthn_challenge
791 WHERE id = ?1";
792
793 self.query_map_one(query, &[&auth_id], |row| row.get(0)).unwrap()
794 }
795
796 fn login(&self, _session: Option<&str>, username: &str, password: &str) -> Result<String, ()> {
798 let query = "SELECT id, password, salt
799 FROM session
800 WHERE username = ?1";
801 self.query_map_one(query, &[&username], |row| {
802 let (id, password_hash, salt): (i32, Option<String>, Option<String>) =
803 (row.get(0), row.get(1), row.get(2));
804
805 if helpers::verify_password(&password,
807 &salt.ok_or_else(|| println!("salt from database empty"))?,
808 &password_hash.ok_or_else(|| println!("password from database empty"))?)
809 {
810 let session_token = helpers::make_session_token();
814 let csrf_token = helpers::make_csrf_token();
815 let now = time::get_time();
816
817 let query = "UPDATE session
818 SET session_token = ?1, csrf_token = ?2, last_login = ?3, last_activity = ?3
819 WHERE id = ?4";
820 self.execute(query, &[&session_token, &csrf_token, &now, &id]).unwrap();
821
822 Ok(session_token)
823 } else {
824 Err(())
825 }
826 })
827 .unwrap()
828 .ok_or(())?
829 }
830
831 fn login_with_code(&self, _session: Option<&str>, logincode: &str) -> Result<String, ()> {
833 if logincode == "" {
834 return Err(());
835 }
836
837 let query = "SELECT id
838 FROM session
839 WHERE logincode = ?1";
840 self.query_map_one(query, &[&logincode], |row| {
841 let id: i32 = row.get(0);
843
844 let session_token = helpers::make_session_token();
845 let csrf_token = helpers::make_csrf_token();
846 let now = time::get_time();
847
848 let query = "UPDATE session
849 SET session_token = ?1, csrf_token = ?2, last_login = ?3, last_activity = ?3
850 WHERE id = ?4";
851 self.execute(query, &[&session_token, &csrf_token, &now, &id]).unwrap();
852
853 session_token
854 })
855 .unwrap()
856 .ok_or(())
857 }
858
859 fn login_with_key(&self, _session: Option<&str>, cred_id: &str) -> Result<String, ()> {
861 if cred_id == "" {
862 return Err(());
863 }
864
865 let query = "SELECT session
866 FROM webauthn_passkey
867 WHERE cred_id = ?1";
868 self.query_map_one(query, &[&cred_id], |row| {
869 let id: i32 = row.get(0);
871
872 let session_token = helpers::make_session_token();
873 let csrf_token = helpers::make_csrf_token();
874 let now = time::get_time();
875
876 let query = "UPDATE session
877 SET session_token = ?1, csrf_token = ?2, last_login = ?3, last_activity = ?3
878 WHERE id = ?4";
879 self.execute(query, &[&session_token, &csrf_token, &now, &id]).unwrap();
880
881 session_token
882 })
883 .unwrap()
884 .ok_or(())
885 }
886
887 fn login_foreign(&self, _session: Option<&str>, provider_id: &str, foreign_id: &str,
889 (is_teacher, is_admin, firstname, lastname, sex, school_name): (bool,
890 bool,
891 &str,
892 &str,
893 Option<i32>,
894 &Option<String>))
895 -> Result<(String, Option<time::Timespec>), ()> {
896 let session_token = helpers::make_session_token();
897 let csrf_token = helpers::make_csrf_token();
898 let now = time::get_time();
899
900 let query = "SELECT id, last_activity
901 FROM session
902 WHERE oauth_foreign_id = ?1
903 AND oauth_provider = ?2";
904 match self.query_map_one(query, &[&foreign_id, &provider_id], |row| -> (i32, time::Timespec) {
905 (row.get(0), row.get(1))
906 }) {
907 Ok(Some((id, last_activity))) => {
908 let query = "UPDATE session
909 SET session_token = ?2, csrf_token = ?3, last_login = ?4, last_activity = ?4,
910 is_teacher = ?5, is_admin = ?6, firstname = ?7, lastname = ?8, sex = ?9,
911 school_name = COALESCE(?10, school_name)
912 WHERE id = ?1";
913 self.execute(query,
914 &[&id,
915 &session_token,
916 &csrf_token,
917 &now,
918 &is_teacher,
919 &is_admin,
920 &firstname,
921 &lastname,
922 &sex,
923 school_name])
924 .unwrap();
925
926 Ok((session_token, Some(last_activity)))
927 }
928 _ => {
930 let query = "INSERT INTO session (session_token, csrf_token, last_login, last_activity,
931 account_created, grade, sex, is_teacher, is_admin, oauth_foreign_id,
932 oauth_provider, firstname, lastname, school_name)
933 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14)";
934 self.execute(query,
935 &[&session_token,
936 &csrf_token,
937 &now,
938 &now,
939 &now,
940 &(if is_teacher { 255 } else { 0 }),
941 &sex,
942 &is_teacher,
943 &is_admin,
944 &foreign_id,
945 &provider_id,
946 &firstname,
947 &lastname,
948 school_name])
949 .unwrap();
950
951 Ok((session_token, None))
952 }
953 }
954 }
955
956 fn create_user_with_groupcode(&self, _session: Option<&str>, groupcode: &str) -> Result<String, ()> {
958 if groupcode == "" {
959 return Err(());
960 }
961
962 let query = "SELECT id
963 FROM usergroup
964 WHERE groupcode = ?1";
965 let group_id =
966 self.query_map_one(query, &[&groupcode], |row| -> i32 { row.get(0) }).map_err(|_| ())?.ok_or(())?;
967
968 let session_token = helpers::make_session_token();
970 let csrf_token = helpers::make_csrf_token();
971 let now = time::get_time();
972
973 let mut logincode = String::new();
975 for i in 0..10 {
976 if i == 9 {
977 panic!("ERROR: Too many logincode collisions! Give up ...");
978 }
979 logincode = helpers::make_logincode();
980 if !self.code_exists(&logincode) {
981 break;
982 }
983 println!("WARNING: Logincode collision! Retrying ...");
984 }
985
986 let query = "INSERT INTO session (session_token, csrf_token, last_login, last_activity, account_created,
987 logincode, grade, sex, is_teacher, managed_by)
988 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10)";
989 self.execute(query,
990 &[&session_token, &csrf_token, &now, &now, &now, &logincode, &0, &None::<i32>, &false, &group_id])
991 .unwrap();
992
993 Ok(session_token)
994 }
995
996 fn update_or_create_group_with_users(&self, mut group: Group, admin: i32) {
997 let query = "SELECT id
998 FROM usergroup
999 JOIN usergroup_admin ON usergroup.id = usergroup_admin.usergroup
1000 WHERE name = ?1
1001 AND usergroup_admin.session = ?2";
1002 if let Ok(Some(id)) = self.query_map_one(query, &[&group.name, &admin], |row| -> i32 { row.get(0) }) {
1003 group.set_id(id);
1005 } else {
1006 group.save(self);
1008 }
1009
1010 let now = time::get_time();
1011
1012 for user in group.members {
1013 if let Ok(Some(id)) =
1014 self.query_map_one("SELECT id FROM session WHERE firstname = ?1 AND lastname = ?2 AND managed_by = ?3",
1015 &[&user.firstname, &user.lastname, &group.id],
1016 |row| -> i32 { row.get(0) })
1017 {
1018 let query = "UPDATE session SET grade = ?2, sex = ?3, anonymous = ?4 WHERE id = ?1";
1020 self.execute(query, &[&id, &user.grade, &user.sex, &user.anonymous]).unwrap();
1021 } else {
1022 let csrf_token = helpers::make_csrf_token();
1024
1025 let mut logincode = String::new();
1026 for i in 0..10 {
1027 if i == 9 {
1028 panic!("ERROR: Too many logincode collisions! Give up ...");
1029 }
1030 logincode = helpers::make_logincode();
1031 if !self.code_exists(&logincode) {
1032 break;
1033 }
1034 println!("WARNING: Logincode collision! Retrying ...");
1035 }
1036
1037 let query =
1038 "INSERT INTO session (firstname, lastname, csrf_token, account_created, logincode, grade, sex,
1039 anonymous, is_teacher, managed_by)
1040 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10)";
1041 self.execute(query,
1042 &[&user.firstname,
1043 &user.lastname,
1044 &csrf_token,
1045 &now,
1046 &logincode,
1047 &user.grade,
1048 &user.sex,
1049 &user.anonymous,
1050 &false,
1051 &group.id])
1052 .unwrap();
1053 }
1054 }
1055 }
1056
1057 fn add_admin_to_group(&self, group: &mut Group, admin: i32) {
1058 let query = "INSERT INTO usergroup_admin (usergroup, session)
1059 VALUES (?1, ?2)";
1060 self.execute(query, &[&group.get_id(), &admin]).unwrap();
1061
1062 group.admins.push(admin);
1063 }
1064
1065 fn remove_admin_from_group(&self, group: &mut Group, admin: i32) {
1066 let query = "DELETE FROM usergroup_admin
1067 WHERE usergroup = ?1
1068 AND session = ?2";
1069 self.execute(query, &[&group.get_id(), &admin]).unwrap();
1070
1071 if let Some(index) = group.admins.iter().position(|x| *x == admin) {
1072 group.admins.remove(index);
1073 }
1074 }
1075
1076 fn logout(&self, session: &str) {
1077 let query = "UPDATE session
1078 SET session_token = NULL
1079 WHERE session_token = ?1";
1080 self.execute(query, &[&session]).unwrap();
1081 }
1082
1083 fn signup(&self, session_token: &str, username: &str, email: &str, password_hash: String, salt: &str)
1084 -> SignupResult {
1085 let mut session_user = self.get_session_or_new(&session_token).unwrap();
1086
1087 if session_user.is_logged_in() {
1088 return SignupResult::UserLoggedIn;
1089 }
1090
1091 if let Ok(None) = self.query_map_one("SELECT username FROM session WHERE username = ?1",
1092 &[&username],
1093 |row| -> Option<String> { row.get(0) })
1094 {
1095 } else {
1096 return SignupResult::UsernameTaken;
1098 }
1099 if let Ok(None) = self.query_map_one("SELECT email, email_unconfirmed FROM session WHERE email = ?1 OR email_unconfirmed = ?1",
1100 &[&email],
1101 |row| -> (Option<String>, Option<String>) { (row.get(0), row.get(1)) })
1102 {
1103 } else {
1104 return SignupResult::EmailTaken;
1106 }
1107
1108 session_user.username = Some(username.to_string());
1109 session_user.email_unconfirmed = Some(email.to_string());
1110 session_user.password = Some(password_hash);
1111 session_user.salt = Some(salt.to_string());
1112
1113 self.save_session(session_user);
1114 SignupResult::SignedUp
1115 }
1116
1117 fn load_submission(&self, session: &SessionUser, task: i32, subtask: Option<&str>) -> Option<Submission> {
1118 match subtask {
1119 None => {
1120 let query = "SELECT id, grade, validated, nonvalidated_grade, value, date, needs_validation
1121 FROM submission
1122 WHERE task = ?1
1123 AND session = ?2
1124 ORDER BY id DESC
1125 LIMIT 1";
1126 self.query_map_one(query, &[&task, &session.id], |row| Submission { id: Some(row.get(0)),
1127 task,
1128 user: session.id,
1129 grade: row.get(1),
1130 validated: row.get(2),
1131 nonvalidated_grade: row.get(3),
1132 subtask_identifier: None,
1133 value: row.get(4),
1134 date: row.get(5),
1135 needs_validation: row.get(6),
1136 autosave: false,
1137 latest: false,
1138 highest_grade_latest: false })
1139 .ok()?
1140 }
1141 Some(subtask_id) => {
1142 let query = "SELECT id, grade, validated, nonvalidated_grade, value, date, needs_validation
1143 FROM submission
1144 WHERE task = ?1
1145 AND session = ?2
1146 AND subtask_identifier = ?3
1147 ORDER BY id DESC
1148 LIMIT 1";
1149 self.query_map_one(query, &[&task, &session.id, &subtask_id], |row| {
1150 Submission { id: Some(row.get(0)),
1151 task,
1152 user: session.id,
1153 grade: row.get(1),
1154 validated: row.get(2),
1155 nonvalidated_grade: row.get(3),
1156 subtask_identifier: Some(subtask_id.to_string()),
1157 value: row.get(4),
1158 date: row.get(5),
1159 needs_validation: row.get(6),
1160 autosave: false,
1161 latest: false,
1162 highest_grade_latest: false }
1163 })
1164 .ok()?
1165 }
1166 }
1167 }
1168 fn get_all_submissions(&self, session_id: i32, task: i32, subtask: Option<&str>) -> Vec<Submission> {
1169 match subtask {
1170 None => {
1171 let query = "SELECT id, grade, validated, nonvalidated_grade, value, date, needs_validation
1172 FROM submission
1173 WHERE task = ?1
1174 AND session = ?2
1175 ORDER BY date";
1176 self.query_map_many(query, &[&task, &session_id], |row| Submission { id: Some(row.get(0)),
1177 task,
1178 user: session_id,
1179 grade: row.get(1),
1180 validated: row.get(2),
1181 nonvalidated_grade: row.get(3),
1182 subtask_identifier: None,
1183 value: row.get(4),
1184 date: row.get(5),
1185 needs_validation: row.get(6),
1186 autosave: false,
1187 latest: false,
1188 highest_grade_latest: false })
1189 .unwrap()
1190 }
1191 _ => unimplemented!(),
1192 }
1193 }
1194 fn submit_submission(&self, mut submission: Submission) {
1195 let mut grade = self.get_grade_by_user_and_task(submission.user, submission.task);
1196 if grade.grade.is_none() || submission.grade > grade.grade.unwrap() {
1197 grade.grade = Some(submission.grade);
1198 grade.validated = false;
1199 grade.save(self);
1200 }
1201
1202 if submission.autosave {
1206 submission.latest = false;
1207 submission.highest_grade_latest = false;
1208 } else if grade.grade.is_none() || submission.grade >= grade.grade.unwrap() {
1209 let query = "UPDATE submission
1210 SET latest = ?3,
1211 highest_grade_latest = ?3
1212 WHERE session = ?1
1213 AND task = ?2";
1214 self.execute(query, &[&submission.user, &submission.task, &false]).unwrap();
1215 submission.latest = true;
1216 submission.highest_grade_latest = true;
1217 } else {
1218 let query = "UPDATE submission
1219 SET latest = ?3
1220 WHERE session = ?1
1221 AND task = ?2";
1222 self.execute(query, &[&submission.user, &submission.task, &false]).unwrap();
1223 submission.latest = true;
1224 submission.highest_grade_latest = false;
1225 }
1226 submission.save(self);
1227 }
1228 fn get_grade_by_user_and_task(&self, session_id: i32, task_id: i32) -> Grade {
1229 let query = "SELECT grade.taskgroup, grade.grade, grade.validated
1230 FROM grade
1231 JOIN task ON grade.taskgroup = task.taskgroup
1232 WHERE grade.session = ?1
1233 AND task.id = ?2";
1234 self.query_map_one(query, &[&session_id, &task_id], |row| Grade { taskgroup: row.get(0),
1235 user: session_id,
1236 grade: row.get(1),
1237 validated: row.get(2) })
1238 .unwrap_or(None)
1239 .unwrap_or_else(|| {
1240 let query = "SELECT taskgroup
1241 FROM task
1242 WHERE id = ?1";
1243 self.query_map_one(query, &[&task_id], |row| Grade { taskgroup: row.get(0),
1244 user: session_id,
1245 grade: None,
1246 validated: false })
1247 .unwrap()
1248 .unwrap() })
1250 }
1251
1252 fn get_contest_groups_grades(&self, session_id: i32, contest_id: i32)
1253 -> (Vec<String>, Vec<(Group, Vec<(UserInfo, Vec<Grade>)>)>) {
1254 let query = "SELECT id, name
1255 FROM taskgroup
1256 WHERE contest = ?1
1257 AND active = ?2
1258 ORDER BY positionalnumber";
1259 let tasknames: Vec<(i32, String)> =
1260 self.query_map_many(query, &[&contest_id, &true], |row| (row.get(0), row.get(1))).unwrap();
1261
1262 let mut taskindex: ::std::collections::BTreeMap<i32, usize> = ::std::collections::BTreeMap::new();
1263
1264 let n_tasks = tasknames.len();
1265 for (index, (i, _)) in tasknames.iter().enumerate() {
1266 taskindex.insert(*i, index);
1267 }
1268
1269 let query = "SELECT grade.taskgroup, grade.session, grade.grade, grade.validated, usergroup.id, usergroup.name,
1270 usergroup.groupcode, usergroup.tag, student.id, student.username, student.logincode,
1271 student.firstname, student.lastname, student.grade AS sgrade, participation.annotation,
1272 participation.team
1273 FROM grade
1274 JOIN taskgroup ON grade.taskgroup = taskgroup.id
1275 JOIN participation ON (participation.session = grade.session OR participation.team = grade.session) AND participation.contest = ?2
1276 JOIN session AS student ON participation.session = student.id
1277 JOIN usergroup ON student.managed_by = usergroup.id
1278 JOIN usergroup_admin ON usergroup.id = usergroup_admin.usergroup
1279 WHERE usergroup_admin.session = ?1
1280 AND taskgroup.contest = ?2
1281 AND taskgroup.active = ?3
1282 ORDER BY usergroup.id, sgrade, student.lastname, student.firstname, student.id,
1283 taskgroup.positionalnumber";
1284 let gradeinfo =
1285 self.query_map_many(query, &[&session_id, &contest_id, &true], |row| {
1286 (Grade { taskgroup: row.get(0), user: row.get(1), grade: row.get(2), validated: row.get(3) },
1287 Group { id: Some(row.get(4)),
1288 name: row.get(5),
1289 groupcode: row.get(6),
1290 tag: row.get(7),
1291 admins: Vec::new(),
1292 members: Vec::new() },
1293 UserInfo { id: row.get(8),
1294 username: row.get(9),
1295 logincode: row.get(10),
1296 firstname: row.get(11),
1297 lastname: row.get(12),
1298 grade: row.get(13),
1299 annotation: row.get(14),
1300 team: row.get(15) })
1301 })
1302 .unwrap();
1303 let mut gradeinfo_iter = gradeinfo.iter();
1304
1305 if let Some(t ) = gradeinfo_iter.next() {
1306 let (grade, mut group, mut userinfo) = t.clone();
1307
1308 let mut grades: Vec<Grade> = vec![Default::default(); n_tasks];
1309 let mut users: Vec<(UserInfo, Vec<Grade>)> = Vec::new();
1310 let mut groups: Vec<(Group, Vec<(UserInfo, Vec<Grade>)>)> = Vec::new();
1311
1312 let index = grade.taskgroup;
1313 grades[taskindex[&index]] = grade;
1314
1315 for ggu in gradeinfo_iter {
1316 let (g, gr, ui) = ggu;
1317 if gr.id != group.id {
1318 users.push((userinfo, grades));
1319 userinfo = ui.clone();
1320 grades = vec![Default::default(); n_tasks];
1321
1322 groups.push((group, users));
1323 group = gr.clone();
1324 users = Vec::new();
1325 } else if ui.id != userinfo.id {
1326 users.push((userinfo, grades));
1327 userinfo = ui.clone();
1328 grades = vec![Default::default(); n_tasks];
1329 }
1330 let index = g.taskgroup;
1331 grades[taskindex[&index]] = *g;
1332 }
1333 users.push((userinfo, grades));
1334 groups.push((group, users));
1335
1336 (tasknames.iter().map(|(_, name)| name.clone()).collect(), groups)
1337 } else {
1338 (Vec::new(), Vec::new()) }
1340 }
1341 fn get_contest_user_grades(&self, session_id: i32, contest_id: i32) -> Vec<Grade> {
1342 let query = "SELECT id, name
1343 FROM taskgroup
1344 WHERE contest = ?1
1345 AND active = ?2
1346 ORDER BY positionalnumber";
1347 let tasknames: Vec<(i32, String)> =
1348 self.query_map_many(query, &[&contest_id, &true], |row| (row.get(0), row.get(1))).unwrap();
1349 let mut taskindex: ::std::collections::BTreeMap<i32, usize> = ::std::collections::BTreeMap::new();
1350
1351 let n_tasks = tasknames.len();
1352 for (index, (i, _)) in tasknames.iter().enumerate() {
1353 taskindex.insert(*i, index);
1354 }
1355
1356 let query = "SELECT grade.taskgroup, grade.session, grade.grade, grade.validated
1357 FROM grade
1358 JOIN taskgroup ON grade.taskgroup = taskgroup.id
1359 WHERE grade.session = ?1
1360 AND taskgroup.contest = ?2
1361 AND taskgroup.active = ?3
1362 ORDER BY taskgroup.positionalnumber";
1363 let gradeinfo =
1364 self.query_map_many(query, &[&session_id, &contest_id, &true], |row| Grade { taskgroup: row.get(0),
1365 user: row.get(1),
1366 grade: row.get(2),
1367 validated: row.get(3) })
1368 .unwrap();
1369 let gradeinfo_iter = gradeinfo.iter();
1370
1371 let mut grades: Vec<Grade> = vec![Default::default(); n_tasks];
1372
1373 for g in gradeinfo_iter {
1374 let index = g.taskgroup;
1375 grades[taskindex[&index]] = *g;
1376 }
1377
1378 grades
1379 }
1380
1381 fn get_taskgroup_user_grade(&self, session_id: i32, taskgroup_id: i32) -> Grade {
1382 let query = "SELECT grade.taskgroup, grade.session, grade.grade, grade.validated
1383 FROM grade
1384 WHERE grade.session = ?1
1385 AND grade.taskgroup = ?2";
1386 self.query_map_one(query, &[&session_id, &taskgroup_id], |row| Grade { taskgroup: row.get(0),
1387 user: row.get(1),
1388 grade: row.get(2),
1389 validated: row.get(3) })
1390 .unwrap_or(None)
1391 .unwrap_or_default()
1392 }
1393
1394 fn export_contest_results_to_file(&self, contest_id: i32, taskgroups: &[(i32, String)], filename: &str) {
1396 use std::fs::OpenOptions;
1397 let file = OpenOptions::new().write(true).create(true).truncate(true).open(filename).unwrap();
1398 let mut headers = vec!["id",
1399 "username",
1400 "logincode",
1401 "oauth_foreign_id",
1402 "oauth_provider",
1403 "firstname",
1404 "lastname",
1405 "grade",
1406 "sex",
1407 "anonymous",
1408 "additional_contest_time_percent",
1409 "data_protection_clearance",
1410 "is_teacher",
1411 "group_id",
1412 "group_name",
1413 "group_tag",
1414 "teacher_id",
1415 "teacher_firstname",
1416 "teacher_lastname",
1417 "teacher_oauth_foreign_id",
1418 "teacher_oauth_school_id",
1419 "teacher_oauth_provider",
1420 "contest_id",
1421 "is_team_participation",
1422 "team_lead",
1423 "start_date"];
1424
1425 let mut select_part = String::new();
1426 let mut join_part = String::new();
1427
1428 let mut join_params = gen_tosql_vector();
1429
1430 join_params.push(&contest_id);
1431
1432 for (n, (id, name)) in taskgroups.iter().enumerate() {
1433 use std::fmt::Write;
1434
1435 write!(select_part, ",\n g{}.grade ", n).unwrap();
1436 write!(join_part,
1437 "\n LEFT JOIN grade AS g{} ON gsession.id = g{}.session AND g{}.taskgroup = ?{} ",
1438 n,
1439 n,
1440 n,
1441 n + 2).unwrap();
1442 join_params.push(id);
1443 headers.push(&name);
1444 }
1445
1446 let query = format!("SELECT session.id,
1447 session.username,
1448 session.logincode,
1449 session.oauth_foreign_id,
1450 session.oauth_provider,
1451 session.firstname,
1452 session.lastname,
1453 session.grade,
1454 session.sex,
1455 session.anonymous,
1456 session.additional_contest_time,
1457 session.data_protection_clearance,
1458 session.is_teacher,
1459 session.managed_by,
1460 ug.name,
1461 ug.tag,
1462 teacher.id,
1463 teacher.firstname,
1464 teacher.lastname,
1465 teacher.oauth_foreign_id,
1466 teacher.oauth_provider,
1467 participation.contest,
1468 participation.team,
1469 participation.start_date
1470 {}
1471 FROM participation
1472 JOIN session ON participation.session = session.id
1473 JOIN session AS gsession ON participation.team = gsession.id OR (participation.team IS NULL AND participation.session = gsession.id)
1474 {}
1475 LEFT JOIN usergroup AS ug ON session.managed_by = ug.id
1476 LEFT JOIN session AS teacher ON teacher.id = (SELECT MIN(usergroup_admin.session) FROM usergroup_admin WHERE usergroup_admin.usergroup = ug.id)
1477 WHERE participation.contest = ?1",
1478 select_part, join_part);
1479
1480 use csv::Writer;
1481 let mut wtr = Writer::from_writer(file);
1482 wtr.serialize(&headers).unwrap();
1483 wtr.flush().unwrap();
1484
1485 let file = wtr.into_inner().unwrap();
1486 let mut wtr = Writer::from_writer(file);
1487
1488 self.query_map_many(&query, join_params.as_slice(), |row| {
1489 let mut points = Vec::new();
1490 for i in 24..24 + taskgroups.len() {
1492 points.push(row.get::<_, Option<i32>>(i));
1493 }
1494
1495 let teacher_oauth_and_school_id = row.get::<_, Option<String>>(19);
1496 let (teacher_oauth_id, teacher_school_id) = if let Some(toasi) = teacher_oauth_and_school_id {
1497 let mut v = toasi.split('/');
1498 let oid: Option<String> = v.next().map(|s| s.to_owned());
1499 let sid: Option<String> = v.next().map(|s| s.to_owned());
1500 (oid, sid)
1501 } else {
1502 (None, None)
1503 };
1504
1505 let team_lead = row.get::<_, Option<i32>>(22);
1506
1507 wtr.serialize(((row.get::<_, i32>(0),
1510 row.get::<_, Option<String>>(1),
1511 row.get::<_, Option<String>>(2),
1512 row.get::<_, Option<String>>(3),
1513 row.get::<_, Option<String>>(4),
1514 row.get::<_, Option<String>>(5),
1515 row.get::<_, Option<String>>(6),
1516 row.get::<_, i32>(7),
1517 row.get::<_, Option<i32>>(8),
1518 row.get::<_, bool>(9)),
1519 (row.get::<_, Option<i32>>(10),
1520 row.get::<_, bool>(11),
1521 row.get::<_, bool>(12),
1522 row.get::<_, Option<i32>>(13),
1523 row.get::<_, Option<String>>(14),
1524 row.get::<_, Option<String>>(15),
1525 row.get::<_, Option<i32>>(16),
1526 row.get::<_, Option<String>>(17),
1527 row.get::<_, Option<String>>(18),
1528 teacher_oauth_id),
1529 (teacher_school_id,
1530 row.get::<_, Option<String>>(20),
1531 row.get::<_, Option<i32>>(21),
1532 team_lead.is_some(),
1533 team_lead,
1534 row.get::<_, Option<time::Timespec>>(23)
1535 .map(|ts| self::time::strftime("%FT%T%z", &time::at(ts)).unwrap()),
1536 points)))
1537 .unwrap();
1538 })
1539 .unwrap();
1540 wtr.flush().unwrap();
1541 }
1542
1543 fn insert_contest_annotations(&self, contest_id: i32, annotations: Vec<(i32, Option<String>)>) -> i32 {
1544 let batch_size = 10;
1545 let query_batch = if self.dbtype() == "postgres" {
1546 "UPDATE participation
1547 SET annotation = batchdata.annotation
1548 FROM (SELECT ?2::int AS userid, ?3 as annotation
1549 UNION ALL SELECT ?4::int AS userid, ?5 as annotation
1550 UNION ALL SELECT ?6::int AS userid, ?7 as annotation
1551 UNION ALL SELECT ?8::int AS userid, ?9 as annotation
1552 UNION ALL SELECT ?10::int AS userid, ?11 as annotation
1553 UNION ALL SELECT ?12::int AS userid, ?13 as annotation
1554 UNION ALL SELECT ?14::int AS userid, ?15 as annotation
1555 UNION ALL SELECT ?16::int AS userid, ?17 as annotation
1556 UNION ALL SELECT ?18::int AS userid, ?19 as annotation
1557 UNION ALL SELECT ?20::int AS userid, ?21 as annotation
1558 ) AS batchdata
1559 WHERE session = batchdata.userid
1560 AND contest = ?1"
1561 } else {
1562 "UPDATE participation
1563 SET annotation = batchdata.annotation
1564 FROM (SELECT ?2 AS userid, ?3 as annotation
1565 UNION ALL SELECT ?4 AS userid, ?5 as annotation
1566 UNION ALL SELECT ?6 AS userid, ?7 as annotation
1567 UNION ALL SELECT ?8 AS userid, ?9 as annotation
1568 UNION ALL SELECT ?10 AS userid, ?11 as annotation
1569 UNION ALL SELECT ?12 AS userid, ?13 as annotation
1570 UNION ALL SELECT ?14 AS userid, ?15 as annotation
1571 UNION ALL SELECT ?16 AS userid, ?17 as annotation
1572 UNION ALL SELECT ?18 AS userid, ?19 as annotation
1573 UNION ALL SELECT ?20 AS userid, ?21 as annotation
1574 ) AS batchdata
1575 WHERE session = batchdata.userid
1576 AND contest = ?1"
1577 };
1578
1579 let query_single = "UPDATE participation
1580 SET annotation = ?3
1581 WHERE session = ?2
1582 AND contest = ?1";
1583
1584 let n_annotations = annotations.len();
1585 let n_batches = n_annotations / batch_size;
1586 let n_single = n_annotations % batch_size;
1587
1588 #[cfg(feature = "debug")]
1589 println!("Annotations: {}, {} batches a {}, {} single", n_annotations, n_batches, batch_size, n_single);
1590
1591 let mut rows_modified = 0;
1592
1593 for batch in 0..n_batches {
1594 let off = batch * batch_size;
1595 rows_modified += self.execute(query_batch,
1596 &[&contest_id,
1597 &annotations[off].0,
1598 &annotations[off].1,
1599 &annotations[off + 1].0,
1600 &annotations[off + 1].1,
1601 &annotations[off + 2].0,
1602 &annotations[off + 2].1,
1603 &annotations[off + 3].0,
1604 &annotations[off + 3].1,
1605 &annotations[off + 4].0,
1606 &annotations[off + 4].1,
1607 &annotations[off + 5].0,
1608 &annotations[off + 5].1,
1609 &annotations[off + 6].0,
1610 &annotations[off + 6].1,
1611 &annotations[off + 7].0,
1612 &annotations[off + 7].1,
1613 &annotations[off + 8].0,
1614 &annotations[off + 8].1,
1615 &annotations[off + 9].0,
1616 &annotations[off + 9].1])
1617 .unwrap();
1618 }
1619
1620 let off = n_annotations - n_single;
1621 for single in 0..n_single {
1622 rows_modified += self.execute(query_single,
1623 &[&contest_id, &annotations[off + single].0, &annotations[off + single].1])
1624 .unwrap();
1625 }
1626
1627 rows_modified as i32
1628 }
1629
1630 fn get_submission_by_id_complete_shallow_contest(&self, submission_id: i32)
1631 -> Option<(Submission, Task, Taskgroup, Contest)> {
1632 let query = "SELECT submission.session, submission.grade, submission.validated, submission.nonvalidated_grade,
1633 submission.needs_validation, submission.subtask_identifier, submission.value,
1634 submission.date,
1635 task.id, task.location, task.language, task.stars,
1636 taskgroup.id, taskgroup.name, taskgroup.active, taskgroup.positionalnumber,
1637 contest.id, contest.location, contest.filename, contest.name, contest.duration,
1638 contest.public, contest.protected
1639 FROM submission
1640 JOIN task ON task.id = submission.task
1641 JOIN taskgroup ON taskgroup.id = task.taskgroup
1642 JOIN contest ON contest.id = taskgroup.contest
1643 WHERE submission.id = ?1";
1644 self.query_map_one(query, &[&submission_id], |row| {
1645 (Submission { id: Some(submission_id),
1646 user: row.get(0),
1647 task: row.get(8),
1648 grade: row.get(1),
1649 validated: row.get(2),
1650 nonvalidated_grade: row.get(3),
1651 needs_validation: row.get(4),
1652 subtask_identifier: row.get(5),
1653 value: row.get(6),
1654 date: row.get(7),
1655 autosave: false,
1656 latest: false,
1657 highest_grade_latest: false },
1658 Task { id: Some(row.get(8)),
1659 taskgroup: row.get(11),
1660 location: row.get(9),
1661 language: row.get(10),
1662 stars: row.get(11) },
1663 Taskgroup { id: row.get(12),
1664 contest: row.get(16),
1665 name: row.get(13),
1666 active: row.get(14),
1667 positionalnumber: row.get(15),
1668 tasks: Vec::new() },
1669 Contest { id: row.get(16),
1670 location: row.get(17),
1671 filename: row.get(18),
1672 name: row.get(19),
1673 duration: row.get(20),
1674 public: row.get(21),
1675 start: None,
1676 end: None,
1677 review_start: None,
1678 review_end: None,
1679 min_grade: None,
1680 max_grade: None,
1681 max_teamsize: None,
1682 positionalnumber: None,
1683 requires_login: None,
1684 requires_contests: Vec::new(),
1685 protected: row.get(22),
1686 secret: None,
1687 message: None,
1688 image: None,
1689 language: None,
1690 category: None,
1691 standalone_task: None,
1692 colour: None,
1693 tags: Vec::new(),
1694 stickers: Vec::new(),
1695 taskgroups: Vec::new() })
1696 })
1697 .unwrap()
1698 }
1699
1700 fn get_contest_list(&self) -> Vec<Contest> {
1701 let query = "SELECT id, location, filename, name, duration, public, start_date, end_date, review_start_date,
1702 review_end_date, min_grade, max_grade, positionalnumber, protected, requires_login,
1703 secret, message, image, language, category, standalone_task,
1704 max_teamsize, colour, contest_tags.tags
1705 FROM contest
1706 LEFT JOIN contest_tags USING (id)
1707 ORDER BY positionalnumber DESC";
1708 self.query_map_many(query, &[], |row| Contest { id: Some(row.get(0)),
1709 location: row.get(1),
1710 filename: row.get(2),
1711 name: row.get(3),
1712 duration: row.get(4),
1713 public: row.get(5),
1714 start: row.get(6),
1715 end: row.get(7),
1716 review_start: row.get(8),
1717 review_end: row.get(9),
1718 min_grade: row.get(10),
1719 max_grade: row.get(11),
1720 positionalnumber: row.get(12),
1721 protected: row.get(13),
1722 requires_login: row.get(14),
1723 requires_contests: Vec::new(),
1724 secret: row.get(15),
1725 message: row.get(16),
1726 image: row.get(17),
1727 language: row.get(18),
1728 category: row.get(19),
1729 standalone_task: row.get(20),
1730 max_teamsize: row.get(21),
1731 colour: row.get(22),
1732 tags: row.get::<_, Option<String>>(23)
1733 .map(|tags| {
1734 tags.split(',').map(|tag| tag.to_owned()).collect()
1735 })
1736 .unwrap_or_else(Vec::new),
1737 stickers: Vec::new(),
1738 taskgroups: Vec::new() })
1739 .unwrap()
1740 }
1741
1742 fn get_contest_list_with_group_member_participations(&self, session_id: i32) -> Vec<Contest> {
1743 let query = "SELECT DISTINCT contest.id, contest.location, contest.filename, contest.name, contest.duration,
1744 contest.public, contest.start_date, contest.end_date, contest.review_start_date,
1745 contest.review_end_date, contest.min_grade, contest.max_grade, contest.positionalnumber,
1746 contest.protected, contest.requires_login, contest.secret,
1747 contest.message, contest.image, contest.language, contest.category, contest.standalone_task,
1748 contest.max_teamsize, contest.colour, contest_tags.tags
1749 FROM contest
1750 JOIN participation ON participation.contest = contest.id
1751 JOIN session ON session.id = participation.session
1752 JOIN usergroup ON usergroup.id = session.managed_by
1753 JOIN usergroup_admin ON usergroup_admin.usergroup = usergroup.id
1754 LEFT JOIN contest_tags ON contest.id = contest_tags.id
1755 WHERE usergroup_admin.session = ?1
1756 ORDER BY positionalnumber DESC";
1757 self.query_map_many(query, &[&session_id], |row| Contest { id: Some(row.get(0)),
1758 location: row.get(1),
1759 filename: row.get(2),
1760 name: row.get(3),
1761 duration: row.get(4),
1762 public: row.get(5),
1763 start: row.get(6),
1764 end: row.get(7),
1765 review_start: row.get(8),
1766 review_end: row.get(9),
1767 min_grade: row.get(10),
1768 max_grade: row.get(11),
1769 positionalnumber: row.get(12),
1770 protected: row.get(13),
1771 requires_login: row.get(14),
1772 requires_contests: Vec::new(),
1773 secret: row.get(15),
1774 message: row.get(16),
1775 image: row.get(17),
1776 language: row.get(18),
1777 category: row.get(19),
1778 standalone_task: row.get(20),
1779 max_teamsize: row.get(21),
1780 colour: row.get(22),
1781 tags: row.get::<_, Option<String>>(23)
1782 .map(|tags| {
1783 tags.split(',')
1784 .map(|tag| tag.to_owned())
1785 .collect()
1786 })
1787 .unwrap_or_else(Vec::new),
1788 stickers: Vec::new(),
1789 taskgroups: Vec::new() })
1790 .unwrap()
1791 }
1792
1793 fn get_contest_by_id(&self, contest_id: i32) -> Option<Contest> {
1794 let query = "SELECT location, filename, name, duration, public, start_date, end_date, review_start_date,
1795 review_end_date, min_grade, max_grade, protected, requires_login, secret,
1796 message, image, language, category, standalone_task, max_teamsize, colour
1797 FROM contest
1798 WHERE id = ?1";
1799 self.query_map_one(query, &[&contest_id], |row| Contest { id: Some(contest_id),
1800 location: row.get(0),
1801 filename: row.get(1),
1802 name: row.get(2),
1803 duration: row.get(3),
1804 public: row.get(4),
1805 start: row.get(5),
1806 end: row.get(6),
1807 review_start: row.get(7),
1808 review_end: row.get(8),
1809 min_grade: row.get(9),
1810 max_grade: row.get(10),
1811 positionalnumber: None,
1812 protected: row.get(11),
1813 requires_login: row.get(12),
1814 requires_contests: Vec::new(),
1815 secret: row.get(13),
1816 message: row.get(14),
1817 image: row.get(15),
1818 language: row.get(16),
1819 category: row.get(17),
1820 standalone_task: row.get(18),
1821 max_teamsize: row.get(19),
1822 colour: row.get(20),
1823 tags: Vec::new(),
1824 stickers: Vec::new(),
1825 taskgroups: Vec::new() })
1826 .unwrap()
1827 }
1828
1829 fn get_contest_by_id_complete(&self, contest_id: i32) -> Option<Contest> {
1830 let query = "SELECT contest.location, contest.filename, contest.name, contest.duration, contest.public,
1831 contest.start_date, contest.end_date, contest.review_start_date, contest.review_end_date,
1832 contest.min_grade, contest.max_grade, contest.protected, contest.requires_login,
1833 contest.secret, contest.message, contest.image, contest.language,
1834 contest.category, contest.standalone_task, contest.max_teamsize, contest.colour,
1835 taskgroup.id, taskgroup.name,
1836 task.id, task.location, task.language, task.stars,
1837 contest_requires_contests.requires_contests
1838 FROM contest
1839 JOIN taskgroup ON contest.id = taskgroup.contest
1840 JOIN task ON taskgroup.id = task.taskgroup
1841 LEFT JOIN contest_requires_contests ON contest.id = contest_requires_contests.id
1842 WHERE contest.id = ?1
1843 AND taskgroup.active = ?2
1844 ORDER BY taskgroup.positionalnumber";
1845 let taskgroupcontest = self.query_map_many(query, &[&contest_id, &true], |row| {
1846 (Contest { id: Some(contest_id),
1847 location: row.get(0),
1848 filename: row.get(1),
1849 name: row.get(2),
1850 duration: row.get(3),
1851 public: row.get(4),
1852 start: row.get(5),
1853 end: row.get(6),
1854 review_start: row.get(7),
1855 review_end: row.get(8),
1856 min_grade: row.get(9),
1857 max_grade: row.get(10),
1858 positionalnumber: None,
1859 protected: row.get(11),
1860 requires_login: row.get(12),
1861 secret: row.get(13),
1862 message: row.get(14),
1863 image: row.get(15),
1864 language: row.get(16),
1865 category: row.get(17),
1866 standalone_task: row.get(18),
1867 max_teamsize: row.get(19),
1868 colour: row.get(20),
1869 tags: Vec::new(),
1870 requires_contests: row.get::<_, Option<String>>(27)
1871 .map(|requires_contests_string| {
1872 requires_contests_string.split("\x1e")
1874 .map(|requires_contests| {
1875 let mut reqs =
1877 requires_contests.split("\x1f");
1878 RequiredContest {
1879 filename: reqs.next()
1880 .unwrap_or("")
1881 .to_string(),
1882 required_stars: reqs.next()
1883 .unwrap_or("")
1884 .parse::<i32>()
1885 .unwrap_or(0)
1886 }
1887 })
1888 .collect()
1889 })
1890 .unwrap_or_else(Vec::new),
1891 stickers: Vec::new(),
1892 taskgroups: Vec::new() },
1893 Taskgroup { id: Some(row.get(21)),
1894 contest: contest_id,
1895 name: row.get(22),
1896 active: true,
1897 positionalnumber: None,
1898 tasks: Vec::new() },
1899 Task { id: Some(row.get(23)),
1900 taskgroup: row.get(21),
1901 location: row.get(24),
1902 language: row.get(25),
1903 stars: row.get(26) })
1904 })
1905 .unwrap();
1906 let mut taskgroupcontest_iter = taskgroupcontest.into_iter();
1907
1908 if let Some((mut contest, mut taskgroup, task)) = taskgroupcontest_iter.next() {
1909 taskgroup.tasks.push(task);
1910 for tgc in taskgroupcontest_iter {
1911 let (_, tg, t) = tgc;
1912 if tg.id != taskgroup.id {
1913 contest.taskgroups.push(taskgroup);
1914 taskgroup = tg;
1915 }
1916 taskgroup.tasks.push(t);
1917 }
1918 contest.taskgroups.push(taskgroup);
1919 Some(contest)
1920 } else {
1921 self.get_contest_by_id(contest_id)
1924 }
1925 }
1926
1927 fn get_contest_by_id_partial(&self, contest_id: i32) -> Option<Contest> {
1928 let query = "SELECT contest.location, contest.filename, contest.name, contest.duration, contest.public,
1929 contest.start_date, contest.end_date, contest.review_start_date, contest.review_end_date,
1930 contest.min_grade, contest.max_grade, contest.protected, contest.requires_login,
1931 contest.secret, contest.message, contest.image, contest.language,
1932 contest.category, contest.standalone_task, contest.max_teamsize, contest.colour,
1933 taskgroup.id, taskgroup.name
1934 FROM contest
1935 JOIN taskgroup ON contest.id = taskgroup.contest
1936 WHERE contest.id = ?1
1937 AND taskgroup.active = ?2";
1938 let taskgroupcontest = self.query_map_many(query, &[&contest_id, &true], |row| {
1939 (Contest { id: Some(contest_id),
1940 location: row.get(0),
1941 filename: row.get(1),
1942 name: row.get(2),
1943 duration: row.get(3),
1944 public: row.get(4),
1945 start: row.get(5),
1946 end: row.get(6),
1947 review_start: row.get(7),
1948 review_end: row.get(8),
1949 min_grade: row.get(9),
1950 max_grade: row.get(10),
1951 positionalnumber: None,
1952 protected: row.get(11),
1953 requires_login: row.get(12),
1954 requires_contests: Vec::new(),
1955 secret: row.get(13),
1956 message: row.get(14),
1957 image: row.get(15),
1958 language: row.get(16),
1959 category: row.get(17),
1960 standalone_task: row.get(18),
1961 max_teamsize: row.get(19),
1962 colour: row.get(20),
1963 tags: Vec::new(),
1964 stickers: Vec::new(),
1965 taskgroups: Vec::new() },
1966 Taskgroup { id: Some(row.get(21)),
1967 contest: contest_id,
1968 name: row.get(22),
1969 active: true,
1970 positionalnumber: None,
1971 tasks: Vec::new() })
1972 })
1973 .unwrap();
1974 let mut taskgroupcontest_iter = taskgroupcontest.into_iter();
1975
1976 if let Some((mut contest, taskgroup)) = taskgroupcontest_iter.next() {
1977 contest.taskgroups.push(taskgroup);
1978 for tgc in taskgroupcontest_iter {
1979 let (_, tg) = tgc;
1980 contest.taskgroups.push(tg);
1981 }
1982 Some(contest)
1983 } else {
1984 self.get_contest_by_id(contest_id)
1987 }
1988 }
1989
1990 fn get_participation(&self, session_id: i32, contest_id: i32) -> Option<Participation> {
1991 let query = "SELECT start_date, team
1992 FROM participation
1993 WHERE session = ?1
1994 AND contest = ?2";
1995 self.query_map_one(query, &[&session_id, &contest_id], |row| Participation { contest: contest_id,
1996 user: session_id,
1997 start: row.get(0),
1998 team: row.get(1),
1999 annotation: None })
2000 .ok()?
2001 }
2002
2003 fn get_own_participation(&self, session_id: i32, contest_id: i32) -> Option<Participation> {
2004 let query = "SELECT start_date, team
2005 FROM participation
2006 WHERE session = ?1
2007 AND contest = ?2";
2008 self.query_map_one(query, &[&session_id, &contest_id], |row| Participation { contest: contest_id,
2009 user: session_id,
2010 start: row.get(0),
2011 team: row.get(1),
2012 annotation: None })
2013 .ok()?
2014 }
2015
2016 fn get_all_participations_complete(&self, session_id: i32) -> Vec<(Participation, Contest)> {
2017 let query = "SELECT contest.id, location, filename, name, duration, public, contest.start_date, end_date,
2018 review_start_date, review_end_date, min_grade, max_grade, protected, requires_login,
2019 secret, message, category, colour,
2020 participation.start_date, participation.team, participation.annotation,
2021 contest_stickers.stickers
2022 FROM participation
2023 JOIN contest ON participation.contest = contest.id
2024 LEFT JOIN contest_stickers ON participation.contest = contest_stickers.id
2025 WHERE participation.session = ?1 AND (standalone_task IS NULL OR standalone_task = FALSE)";
2026 self.query_map_many(query, &[&session_id], |row| {
2027 (Participation { contest: row.get(0),
2028 user: session_id,
2029 start: row.get(18),
2030 team: row.get(19),
2031 annotation: row.get(20) },
2032 Contest { id: Some(row.get(0)),
2033 location: row.get(1),
2034 filename: row.get(2),
2035 name: row.get(3),
2036 duration: row.get(4),
2037 public: row.get(5),
2038 start: row.get(6),
2039 end: row.get(7),
2040 review_start: row.get(8),
2041 review_end: row.get(9),
2042 min_grade: row.get(10),
2043 max_grade: row.get(11),
2044 positionalnumber: None,
2045 protected: row.get(12),
2046 requires_login: row.get(13),
2047 requires_contests: Vec::new(),
2048 secret: row.get(14),
2049 message: row.get(15),
2050 image: None,
2051 language: None,
2052 category: row.get(16),
2053 max_teamsize: None,
2054 colour: row.get(17),
2055 standalone_task: None,
2056 tags: Vec::new(),
2057 stickers: row.get::<_, Option<String>>(21)
2058 .map(|stickerstring| {
2059 stickerstring.split("\x1e")
2061 .map(|sticker| {
2062 let mut stckr = sticker.split("\x1f");
2064 (stckr.next().unwrap_or("").to_string(),
2065 stckr.next().unwrap_or("").parse::<i32>().unwrap_or(0))
2066 })
2067 .collect()
2068 })
2069 .unwrap_or_else(Vec::new),
2070 taskgroups: Vec::new() })
2071 })
2072 .unwrap()
2073 }
2074
2075 fn count_all_stars(&self, session_id: i32) -> i32 {
2076 let query = "SELECT COALESCE(SUM(grade.grade), 0) AS stars
2077 FROM participation
2078 JOIN taskgroup ON participation.contest = taskgroup.contest
2079 JOIN session ON session.id = participation.team
2080 OR (participation.team IS NULL AND session.id = participation.session)
2081 JOIN grade ON grade.taskgroup = taskgroup.id AND grade.session = session.id
2082 WHERE participation.session = ?1";
2083 self.query_map_one(query, &[&session_id], |row| -> i64 { row.get(0) }).unwrap().unwrap() as i32
2084 }
2085
2086 fn count_all_stars_by_contest(&self, session_id: i32) -> Vec<(i32, i32)> {
2087 let query = "SELECT participation.contest, COALESCE(SUM(grade.grade), 0) AS stars
2088 FROM participation
2089 JOIN taskgroup ON participation.contest = taskgroup.contest
2090 JOIN session ON session.id = participation.team
2091 OR (participation.team IS NULL AND session.id = participation.session)
2092 JOIN grade ON grade.taskgroup = taskgroup.id AND grade.session = session.id
2093 WHERE participation.session = ?1
2094 GROUP BY participation.contest";
2095 self.query_map_many(query, &[&session_id], |row| -> (i32, i32) {
2096 (row.get::<_, i32>(0) as i32, row.get::<_, i64>(1) as i32)
2097 })
2098 .unwrap()
2099 }
2100
2101 fn has_participation_by_contest_file(&self, session_id: i32, location: &str, filename: &str) -> bool {
2102 let query = "SELECT participation.contest
2103 FROM participation
2104 JOIN contest ON participation.contest = contest.id
2105 WHERE participation.session = ?1
2106 AND contest.location = ?2
2107 AND contest.filename = ?3";
2108 self.exists(query, &[&session_id, &location, &filename])
2109 }
2110
2111 fn has_participation_by_contest_file_and_stars(&self, session_id: i32, location: &str, filename: &str,
2112 required_stars: i32)
2113 -> bool {
2114 let query = "SELECT participation.contest, COALESCE(SUM(grade.grade), 0) AS stars
2115 FROM participation
2116 JOIN taskgroup ON participation.contest = taskgroup.contest
2117 JOIN session ON session.id = participation.team
2118 OR (participation.team IS NULL AND session.id = participation.session)
2119 JOIN grade ON grade.taskgroup = taskgroup.id AND grade.session = session.id
2120 JOIN contest ON participation.contest = contest.id
2121 WHERE participation.session = ?1
2122 AND contest.location = ?2
2123 AND contest.filename = ?3
2124 GROUP BY participation.contest
2125 HAVING stars >= ?4";
2126 self.exists(query, &[&session_id, &location, &filename, &required_stars])
2127 }
2128
2129 fn new_participation(&self, session_id: i32, contest_id: i32, team: Option<i32>) -> Result<Participation, ()> {
2130 let query = "SELECT start_date
2131 FROM participation
2132 WHERE session = ?1
2133 AND contest = ?2";
2134 match self.query_map_one(query, &[&session_id, &contest_id], |_| {}).map_err(|_| ())? {
2135 Some(()) => Err(()),
2136 None => {
2137 let insert = "INSERT INTO participation (contest, session, start_date, team)
2138 VALUES (?1, ?2, ?3, ?4)";
2139
2140 let now = time::get_time();
2141 self.execute(insert, &[&contest_id, &session_id, &now, &team]).unwrap();
2142
2143 Ok(self.get_own_participation(session_id, contest_id).unwrap()) }
2145 }
2146 }
2147 fn get_task_by_id(&self, task_id: i32) -> Option<Task> {
2148 let query = "SELECT location, language, stars, taskgroup
2149 FROM task
2150 WHERE id = ?1";
2151 self.query_map_one(query, &[&task_id], |row| Task { id: Some(task_id),
2152 taskgroup: row.get(3),
2153 location: row.get(0),
2154 language: row.get(1),
2155 stars: row.get(2) })
2156 .unwrap()
2157 }
2158 fn get_task_by_id_complete(&self, task_id: i32) -> Option<(Task, Taskgroup, Contest)> {
2159 let query = "SELECT task.location, task.language, task.stars,
2160 taskgroup.id, taskgroup.name, taskgroup.active,
2161 contest.id, contest.location, contest.filename, contest.name, contest.duration,
2162 contest.public, contest.start_date, contest.end_date, contest.review_start_date,
2163 contest.review_end_date, contest.min_grade, contest.max_grade, contest.protected,
2164 contest.requires_login, contest.secret, contest.message,
2165 contest.category, contest.standalone_task, contest.colour
2166 FROM contest
2167 JOIN taskgroup ON taskgroup.contest = contest.id
2168 JOIN task ON task.taskgroup = taskgroup.id
2169
2170 WHERE task.id = ?1";
2171 self.query_map_one(query, &[&task_id], |row| {
2172 (Task { id: Some(task_id),
2173 taskgroup: row.get(3),
2174 location: row.get(0),
2175 language: row.get(1),
2176 stars: row.get(2) },
2177 Taskgroup { id: Some(row.get(3)),
2178 contest: row.get(6),
2179 name: row.get(4),
2180 active: row.get(5),
2181 positionalnumber: None,
2182 tasks: Vec::new() },
2183 Contest { id: Some(row.get(6)),
2184 location: row.get(7),
2185 filename: row.get(8),
2186 name: row.get(9),
2187 duration: row.get(10),
2188 public: row.get(11),
2189 start: row.get(12),
2190 end: row.get(13),
2191 review_start: row.get(14),
2192 review_end: row.get(15),
2193 min_grade: row.get(16),
2194 max_grade: row.get(17),
2195 positionalnumber: None,
2196 protected: row.get(18),
2197 requires_login: row.get(19),
2198 secret: row.get(20),
2199 message: row.get(21),
2200 image: None,
2201 language: None,
2202 category: row.get(22),
2203 standalone_task: row.get(23),
2204 colour: row.get(24),
2205 tags: Vec::new(),
2206 stickers: Vec::new(),
2207 requires_contests: Vec::new(),
2208 max_teamsize: None,
2209 taskgroups: Vec::new() })
2210 })
2211 .unwrap()
2212 }
2213
2214 fn get_submission_to_validate(&self, tasklocation: &str, subtask: Option<&str>) -> i32 {
2215 match subtask {
2216 Some(st) => {
2217 let query = "SELECT id
2218 FROM submission
2219 JOIN task ON submission.task = task.id
2220 WHERE task.location = ?1
2221 AND subtask_identifier = ?2
2222 AND needs_validation = 1
2223 LIMIT 1";
2224 self.query_map_one(query, &[&tasklocation, &st], |row| row.get(0)).unwrap().unwrap()
2225 }
2226 None => {
2227 let query = "SELECT id
2228 FROM submission
2229 JOIN task ON submission.task = task.id
2230 WHERE task.location = ?1
2231 AND needs_validation = 1
2232 LIMIT 1";
2233 self.query_map_one(query, &[&tasklocation], |row| row.get(0)).unwrap().unwrap()
2234 }
2235 }
2236 }
2237
2238 fn find_next_submission_to_validate(&self, userid: i32, taskgroupid: i32) {
2239 let query = "SELECT id, validated
2240 FROM submission
2241 JOIN task ON submission.task = task.id
2242 WHERE task.taskgroup = ?1
2243 AND submission.session = ?2
2244 ORDER BY value DESC id DESC
2245 LIMIT 1";
2246 let (id, validated): (i32, bool) =
2247 self.query_map_one(query, &[&taskgroupid, &userid], |row| (row.get(0), row.get(1))).unwrap().unwrap();
2248 if !validated {
2249 let query = "UPDATE submission
2250 SET needs_validation = 1
2251 WHERE id = ?1";
2252 self.execute(query, &[&id]).unwrap();
2253 }
2254 }
2255
2256 fn add_group(&self, group: &mut Group) { group.save(self); }
2257
2258 fn save_group(&self, group: &mut Group) { group.save(self); }
2259
2260 fn get_groups(&self, session_id: i32) -> Vec<Group> {
2261 let query = "SELECT id, name, groupcode, tag
2262 FROM usergroup
2263 JOIN usergroup_admin ON usergroup.id = usergroup_admin.usergroup
2264 WHERE usergroup_admin.session = ?1";
2265 self.query_map_many(query, &[&session_id], |row| Group { id: Some(row.get(0)),
2266 name: row.get(1),
2267 groupcode: row.get(2),
2268 tag: row.get(3),
2269 admins: Vec::new(),
2270 members: Vec::new() })
2271 .unwrap()
2272 }
2273 fn get_groups_complete(&self, _session_id: i32) -> Vec<Group> {
2274 unimplemented!();
2275 }
2276 fn get_group(&self, group_id: i32) -> Option<Group> {
2277 let query = "SELECT name, groupcode, tag
2278 FROM usergroup
2279 WHERE id = ?1";
2280 let mut group = self.query_map_one(query, &[&group_id], |row| Group { id: Some(group_id),
2281 name: row.get(0),
2282 groupcode: row.get(1),
2283 tag: row.get(2),
2284 admins: Vec::new(),
2285 members: Vec::new() })
2286 .unwrap()?;
2287
2288 let query = "SELECT session FROM usergroup_admin WHERE usergroup = ?1 ORDER BY session";
2289 group.admins = self.query_map_many(query, &[&group_id], |row| row.get(0)).unwrap();
2290
2291 Some(group)
2292 }
2293 fn group_has_protected_participations(&self, group_id: i32) -> bool {
2294 let query = "SELECT EXISTS(
2295 SELECT session.id
2296 FROM session
2297 JOIN participation ON participation.session = session.id
2298 JOIN contest ON contest.id = participation.contest
2299 WHERE managed_by = ?1
2300 AND contest.protected = ?2
2301 )";
2302 self.query_map_one(query, &[&group_id, &true], |row| row.get(0)).unwrap().unwrap()
2303 }
2304 fn get_group_complete(&self, group_id: i32) -> Option<Group> {
2305 let query = "SELECT name, groupcode, tag
2306 FROM usergroup
2307 WHERE id = ?1";
2308 let mut group = self.query_map_one(query, &[&group_id], |row| Group { id: Some(group_id),
2309 name: row.get(0),
2310 groupcode: row.get(1),
2311 tag: row.get(2),
2312 admins: Vec::new(),
2313 members: Vec::new() })
2314 .unwrap()?;
2315
2316 let query = "SELECT session FROM usergroup_admin WHERE usergroup = ?1 ORDER BY session";
2317 group.admins = self.query_map_many(query, &[&group_id], |row| row.get(0)).unwrap();
2318
2319 let query = "SELECT id, session_token, csrf_token, last_login, last_activity, account_created, username,
2320 password, salt, logincode, email, email_unconfirmed, email_confirmationcode, firstname,
2321 lastname, street, zip, city, nation, grade, sex, anonymous, additional_contest_time,
2322 data_protection_clearance, is_admin, is_teacher, school_name, oauth_provider,
2323 oauth_foreign_id
2324 FROM session
2325 WHERE managed_by = ?1
2326 ORDER BY id";
2327 group.members =
2328 self.query_map_many(query, &[&group_id], |row| SessionUser { id: row.get(0),
2329 session_token: row.get(1),
2330 csrf_token: row.get(2),
2331 last_login: row.get(3),
2332 last_activity: row.get(4),
2333 account_created: row.get(5),
2334
2335 username: row.get(6),
2336 password: row.get(7),
2337 salt: row.get(8),
2338 logincode: row.get(9),
2339 email: row.get(10),
2340 email_unconfirmed: row.get(11),
2341 email_confirmationcode: row.get(12),
2342
2343 firstname: row.get(13),
2344 lastname: row.get(14),
2345 street: row.get(15),
2346 zip: row.get(16),
2347 city: row.get(17),
2348 nation: row.get(18),
2349 grade: row.get(19),
2350 sex: row.get(20),
2351 anonymous: row.get(21),
2352 additional_contest_time: row.get(22),
2353 data_protection_clearance: row.get(23),
2354
2355 is_admin: row.get(24),
2356 is_teacher: row.get(25),
2357 managed_by: Some(group_id),
2358 school_name: row.get(26),
2359
2360 oauth_provider: row.get(27),
2361 oauth_foreign_id: row.get(28) })
2362 .unwrap();
2363 Some(group)
2364 }
2365
2366 fn delete_user(&self, user_id: i32) {
2367 let query = "DELETE FROM session
2368 WHERE id = ?1";
2369 self.execute(query, &[&user_id]).unwrap();
2370 }
2371 fn delete_all_users_for_group(&self, group_id: i32) {
2372 let query = "DELETE FROM session
2373 WHERE managed_by = ?1
2374 AND oauth_foreign_id IS NULL";
2375 self.execute(query, &[&group_id]).unwrap();
2376
2377 let query = "UPDATE session
2378 SET managed_by = NULL
2379 WHERE managed_by = ?1
2380 AND oauth_foreign_id IS NOT NULL";
2381 self.execute(query, &[&group_id]).unwrap();
2382 }
2383 fn set_data_clearance_for_user(&self, user_id: i32, clearance_state: bool) {
2384 let clearance_value: i32 = if clearance_state { 1 } else { 0 };
2385 let query = "UPDATE session
2386 SET data_protection_clearance = ?2
2387 WHERE id = ?1";
2388 self.execute(query, &[&user_id, &clearance_value]).unwrap();
2389 }
2390 fn set_data_clearance_for_group(&self, group_id: i32, clearance_state: bool) {
2391 let clearance_value: i32 = if clearance_state { 1 } else { 0 };
2392 let query = "UPDATE session
2393 SET data_protection_clearance = ?2
2394 WHERE managed_by = ?1";
2395 self.execute(query, &[&group_id, &clearance_value]).unwrap();
2396 }
2397 fn delete_group(&self, group_id: i32) {
2398 let query = "DELETE FROM usergroup
2399 WHERE id = ?1";
2400 self.execute(query, &[&group_id]).unwrap();
2401 }
2402 fn delete_participation(&self, user_id: i32, contest_id: i32) {
2403 let query = "DELETE FROM submission
2404 WHERE id IN (
2405 SELECT submission.id FROM submission
2406 JOIN task ON submission.task = task.id
2407 JOIN taskgroup ON task.taskgroup = taskgroup.id
2408 WHERE taskgroup.contest = ?1
2409 AND submission.session = ?2
2410 )";
2411 self.execute(query, &[&contest_id, &user_id]).unwrap();
2412
2413 let query = "DELETE FROM grade
2414 WHERE taskgroup IN (
2415 SELECT id FROM taskgroup
2416 WHERE taskgroup.contest = ?1
2417 )
2418 AND session = ?2";
2419 self.execute(query, &[&contest_id, &user_id]).unwrap();
2420
2421 let query = "DELETE FROM participation
2422 WHERE contest = ?1
2423 AND session = ?2";
2424 self.execute(query, &[&contest_id, &user_id]).unwrap();
2425 }
2426
2427 fn get_search_users(
2428 &self,
2429 (s_id, s_firstname, s_lastname, s_logincode, s_groupcode, s_pms_id): (Option<i32>,
2430 Option<String>,
2431 Option<String>,
2432 Option<String>,
2433 Option<String>,
2434 Option<String>))
2435 -> Result<Vec<(i32, Option<String>, Option<String>, Option<String>, Option<String>, Option<String>)>,
2436 Vec<(i32, String, String, String)>> {
2437 if let Some(id) = s_id {
2438 let query = "SELECT id, firstname, lastname, logincode, oauth_foreign_id, oauth_provider
2439 FROM session
2440 WHERE id = ?1
2441 LIMIT 201";
2442 Ok(self.query_map_many(query, &[&id], |row| {
2443 (row.get(0), row.get(1), row.get(2), row.get(3), row.get(4), row.get(5))
2444 })
2445 .unwrap())
2446 } else if let Some(logincode) = s_logincode {
2447 let query = "SELECT id, firstname, lastname, logincode, oauth_foreign_id, oauth_provider
2448 FROM session
2449 WHERE logincode = ?1
2450 LIMIT 201";
2451 Ok(self.query_map_many(query, &[&logincode], |row| {
2452 (row.get(0), row.get(1), row.get(2), row.get(3), row.get(4), row.get(5))
2453 })
2454 .unwrap())
2455 } else if let Some(groupcode) = s_groupcode {
2456 let query = "SELECT id, name, tag
2457 FROM usergroup
2458 WHERE groupcode = ?1
2459 LIMIT 201";
2460 Err(self.query_map_many(query, &[&groupcode], |row| {
2461 (row.get(0), row.get(1), row.get(2), groupcode.clone())
2462 })
2463 .unwrap())
2464 } else if let Some(pms_id) = s_pms_id {
2465 let query = "SELECT id, firstname, lastname, logincode, oauth_foreign_id, oauth_provider
2466 FROM session
2467 WHERE oauth_foreign_id = ?1
2468 OR oauth_foreign_id LIKE ?2
2469 LIMIT 201";
2470 Ok(self.query_map_many(query, &[&pms_id, &format!("{}/%", pms_id)], |row| {
2471 (row.get(0), row.get(1), row.get(2), row.get(3), row.get(4), row.get(5))
2472 })
2473 .unwrap())
2474 } else if let (Some(firstname), Some(lastname)) = (s_firstname, s_lastname) {
2475 let query = "SELECT id, firstname, lastname, logincode, oauth_foreign_id, oauth_provider
2476 FROM session
2477 WHERE firstname LIKE ?1
2478 AND lastname LIKE ?2
2479 ORDER BY id DESC
2480 LIMIT 201";
2481 Ok(self.query_map_many(query, &[&format!("%{}%", firstname), &format!("%{}%", lastname)], |row| {
2482 (row.get(0), row.get(1), row.get(2), row.get(3), row.get(4), row.get(5))
2483 })
2484 .unwrap())
2485 } else {
2486 Ok(Vec::new())
2487 }
2488 }
2489
2490 fn remove_old_users_and_groups(&self, maxstudentage: time::Timespec, maxteacherage: Option<time::Timespec>,
2492 maxage: Option<time::Timespec>)
2493 -> Result<(i32, i32, i32, i32), ()> {
2494 let query = "SELECT managed_by
2496 FROM session
2497 WHERE username IS NULL AND password IS NULL AND oauth_foreign_id IS NULL AND oauth_provider IS NULL AND managed_by IS NOT NULL
2498 AND ((last_login < ?1 AND last_activity < ?1)
2499 OR (last_login < ?1 AND last_activity IS NULL)
2500 OR (last_login IS NULL AND last_activity < ?1)
2501 OR (last_login IS NULL AND last_activity IS NULL AND account_created < ?1))";
2502 let mut groups: Vec<i32> = self.query_map_many(query, &[&maxstudentage], |row| row.get(0)).unwrap();
2503
2504 let query = "DELETE
2506 FROM session
2507 WHERE username IS NULL AND password IS NULL AND oauth_foreign_id IS NULL AND oauth_provider IS NULL
2508 AND ((last_login < ?1 AND last_activity < ?1)
2509 OR (last_login < ?1 AND last_activity IS NULL)
2510 OR (last_login IS NULL AND last_activity < ?1)
2511 OR (last_login IS NULL AND last_activity IS NULL AND account_created < ?1))";
2512 self.execute(query, &[&maxstudentage]).unwrap();
2513
2514 let n_users = groups.len() as i32;
2516 let mut n_groups: i32 = 0;
2517 let mut n_teachers: i32 = 0;
2518 let mut n_other: i32 = 0;
2519
2520 groups.sort_unstable();
2522 groups.dedup();
2523
2524 let query = "SELECT count(*)
2526 FROM session
2527 WHERE managed_by = ?1;";
2528 for group in groups {
2529 let groupsize: i64 = self.query_map_one(query, &[&group], |row| row.get(0)).unwrap().unwrap();
2530
2531 if groupsize == 0 {
2532 let query = "DELETE
2533 FROM usergroup
2534 WHERE id = ?1";
2535 self.execute(query, &[&group]).unwrap();
2536
2537 n_groups += 1;
2538 }
2539 }
2540
2541 let query = "SELECT id
2543 FROM usergroup
2544 WHERE group_created < ?1";
2545 let groups: Vec<i32> = self.query_map_many(query, &[&maxstudentage], |row| row.get(0)).unwrap();
2546 let query = "SELECT count(*)
2547 FROM session
2548 WHERE managed_by = ?1;";
2549 for group in groups {
2550 let groupsize: i64 = self.query_map_one(query, &[&group], |row| row.get(0)).unwrap().unwrap();
2551
2552 if groupsize == 0 {
2553 let query = "DELETE
2554 FROM usergroup
2555 WHERE id = ?1";
2556 self.execute(query, &[&group]).unwrap();
2557
2558 n_groups += 1;
2559 }
2560 }
2561
2562 let query = "SELECT id
2564 FROM session
2565 WHERE is_teacher = ?1
2566 AND ((last_login < ?2 AND last_activity < ?2)
2567 OR (last_login < ?2 AND last_activity IS NULL)
2568 OR (last_login IS NULL AND last_activity < ?2)
2569 OR (last_login IS NULL AND last_activity IS NULL AND account_created < ?2))";
2570 if let Some(maxteacherage) = maxteacherage {
2571 let teachers: Vec<i32> = self.query_map_many(query, &[&true, &maxteacherage], |row| row.get(0)).unwrap();
2572
2573 let query = "SELECT count(*)
2575 FROM usergroup_admin
2576 WHERE session = ?1;";
2577 for teacher in teachers {
2578 let groupcount: i64 = self.query_map_one(query, &[&teacher], |row| row.get(0)).unwrap().unwrap();
2579
2580 if groupcount == 0 {
2581 let query = "DELETE
2582 FROM session
2583 WHERE id = ?1";
2584 self.execute(query, &[&teacher]).unwrap();
2585
2586 n_teachers += 1;
2587 }
2588 }
2589 }
2590
2591 if let Some(maxage) = maxage {
2593 let query = "SELECT count(*)
2594 FROM session
2595 WHERE ((last_login < ?1 AND last_activity < ?1)
2596 OR (last_login < ?1 AND last_activity IS NULL)
2597 OR (last_login IS NULL AND last_activity < ?1)
2598 OR (last_login IS NULL AND last_activity IS NULL AND account_created < ?1))";
2599 n_other = self.query_map_one(query, &[&maxage], |row| row.get::<_, i64>(0) as i32).unwrap().unwrap();
2600
2601 let query = "DELETE
2602 FROM session
2603 WHERE ((last_login < ?1 AND last_activity < ?1)
2604 OR (last_login < ?1 AND last_activity IS NULL)
2605 OR (last_login IS NULL AND last_activity < ?1)
2606 OR (last_login IS NULL AND last_activity IS NULL AND account_created < ?1))";
2607 self.execute(query, &[&maxage]).unwrap();
2608 }
2609
2610 Ok((n_users, n_groups, n_teachers, n_other))
2611 }
2612
2613 fn count_temporary_sessions(&self, maxage: time::Timespec) -> i32 {
2614 let query = "SELECT count(*)
2615 FROM session
2616 WHERE (last_activity < ?1 OR last_activity IS NULL)
2617 AND logincode IS NULL
2618 AND password IS NULL
2619 AND oauth_foreign_id IS NULL";
2620 self.query_map_one(query, &[&maxage], |row| row.get::<_, i64>(0) as i32).unwrap().unwrap()
2621 }
2622
2623 fn remove_temporary_sessions(&self, maxage: time::Timespec, limit: Option<u32>) {
2624 if let Some(limit) = limit {
2629 let query = "DELETE
2630 FROM session
2631 WHERE id IN (SELECT id
2632 FROM session
2633 WHERE (last_activity < ?1 OR last_activity IS NULL)
2634 AND logincode IS NULL
2635 AND password IS NULL
2636 AND oauth_foreign_id IS NULL
2637 ORDER BY last_activity
2638 LIMIT ?2)";
2639 self.execute(query, &[&maxage, &(limit as i64)]).unwrap();
2640 } else {
2641 let query = "DELETE
2642 FROM session
2643 WHERE (last_activity < ?1 OR last_activity IS NULL)
2644 AND logincode IS NULL
2645 AND password IS NULL
2646 AND oauth_foreign_id IS NULL";
2647 self.execute(query, &[&maxage]).unwrap();
2648 }
2649 }
2650
2651 fn remove_autosaved_submissions(&self, maxage: time::Timespec, limit: Option<u32>) {
2652 if let Some(limit) = limit {
2653 let query = "DELETE
2654 FROM submission
2655 WHERE id IN (SELECT id
2656 FROM submission
2657 WHERE date < ?1
2658 AND autosave = ?2
2659 ORDER BY date
2660 LIMIT ?3)";
2661 self.execute(query, &[&maxage, &true, &(limit as i64)]).unwrap();
2662 } else {
2663 let query = "DELETE
2664 FROM submission
2665 WHERE date < ?1
2666 AND autosave = ?2";
2667 self.execute(query, &[&maxage, &true]).unwrap();
2668 }
2669 }
2670
2671 fn remove_all_but_latest_submissions(&self, maxage: time::Timespec, limit: Option<u32>) {
2672 if let Some(limit) = limit {
2673 let query = "DELETE
2674 FROM submission
2675 WHERE id IN (SELECT id
2676 FROM submission
2677 WHERE date < ?1
2678 AND latest = ?2
2679 AND highest_grade_latest = ?2
2680 ORDER BY date
2681 LIMIT ?3)";
2682 self.execute(query, &[&maxage, &false, &(limit as i64)]).unwrap();
2683 } else {
2684 let query = "DELETE
2685 FROM submission
2686 WHERE date < ?1
2687 AND latest = ?2
2688 AND highest_grade_latest = ?2";
2689 self.execute(query, &[&maxage, &false]).unwrap();
2690 }
2691 }
2692
2693 fn move_task_location(&self, old_location: &str, new_location: &str, contest: Option<i32>) -> i32 {
2694 let query = "SELECT contest.id, oldtaskgroup.id, newtaskgroup.id, oldtask.id, newtask.id
2695 FROM contest
2696 JOIN taskgroup AS oldtaskgroup ON oldtaskgroup.contest = contest.id
2697 JOIN task AS oldtask ON oldtask.taskgroup = oldtaskgroup.id
2698 JOIN taskgroup AS newtaskgroup ON newtaskgroup.contest = contest.id
2699 JOIN task AS newtask ON newtask.taskgroup = newtaskgroup.id
2700 WHERE oldtask.location = ?1
2701 AND newtask.location = ?2;";
2702
2703 let mut tasks: Vec<(i32, (i32, i32), (i32, i32))> =
2704 self.query_map_many(query, &[&old_location, &new_location], |row| {
2705 (row.get(0), (row.get(1), row.get(2)), (row.get(3), row.get(4)))
2706 })
2707 .unwrap();
2708
2709 if let Some(contest) = contest {
2710 tasks.retain(|t| t.0 == contest);
2711 }
2712
2713 let query = "WITH cte AS (
2714 SELECT ?2 AS taskgroup, oldgrade.session, GREATEST(oldgrade.grade, newgrade.grade) AS grade, oldgrade.validated
2715 FROM grade AS oldgrade
2716 LEFT JOIN grade AS newgrade ON oldgrade.session = newgrade.session AND newgrade.taskgroup = ?2
2717 WHERE oldgrade.taskgroup = ?1
2718 )
2719 INSERT INTO grade (taskgroup, session, grade, validated)
2720 SELECT * FROM CTE
2721 ON CONFLICT (taskgroup, session)
2722 DO UPDATE SET grade = excluded.grade, validated = excluded.validated;";
2723
2724 for task in &tasks {
2725 self.execute(query, &[&task.1 .0, &task.1 .1]).unwrap();
2726 }
2727
2728 let query = "DELETE
2729 FROM grade
2730 WHERE taskgroup = ?1;";
2731
2732 for task in &tasks {
2733 self.execute(query, &[&task.1 .0]).unwrap();
2734 }
2735
2736 let limitquery = "SELECT id
2737 FROM submission
2738 WHERE task = ?1
2739 LIMIT 1;";
2740
2741 let query = "WITH cte AS (
2742 SELECT id
2743 FROM submission
2744 WHERE task = ?1
2745 LIMIT 10000
2746 )
2747 UPDATE submission s
2748 SET task = ?2
2749 FROM cte
2750 WHERE s.id = cte.id;";
2751
2752 for task in &tasks {
2753 while self.query_map_one(limitquery, &[&task.2 .0], |_| ()).unwrap().is_some() {
2754 self.execute(query, &[&task.2 .0, &task.2 .1]).unwrap();
2755 }
2756 }
2757
2758 tasks.len() as i32
2759 }
2760
2761 fn get_debug_information(&self) -> String {
2762 let now = time::get_time();
2763 let cache_key = "dbstatus";
2764
2765 let query = "SELECT value, date
2766 FROM string_cache
2767 WHERE key = ?1";
2768
2769 let db_has_value = if let Some((cached_value, cache_date))= self.query_map_one(query, &[&cache_key], |row| -> (String, time::Timespec) {(row.get(0), row.get(1))}).unwrap() {
2771 if cache_date.sec / 60 >= now.sec / 60 {
2773 return cached_value;
2774 }
2775 true
2776 } else {
2777 false
2778 };
2779
2780 let duration = Duration::minutes(60);
2781 let then = now - duration;
2782
2783 let query = "SELECT count(*)
2785 FROM session
2786 WHERE last_activity > ?1;";
2787 let n_asession: i64 = self.query_map_one(query, &[&then], |row| row.get(0)).unwrap().unwrap();
2788
2789 let query = "SELECT count(*)
2791 FROM participation
2792 WHERE start_date > ?1;";
2793 let n_apart: i64 = self.query_map_one(query, &[&then], |row| row.get(0)).unwrap().unwrap();
2794
2795 let query = "SELECT count(*)
2797 FROM session;";
2798 let n_session: i64 = self.query_map_one(query, &[], |row| row.get(0)).unwrap().unwrap();
2799
2800 let query = "SELECT count(*)
2802 FROM session
2803 WHERE oauth_foreign_id IS NOT NULL OR logincode IS NOT NULL;";
2804 let n_user: i64 = self.query_map_one(query, &[], |row| row.get(0)).unwrap().unwrap();
2805
2806 let query = "SELECT count(*)
2808 FROM session
2809 WHERE oauth_foreign_id IS NOT NULL;";
2810 let n_pmsuser: i64 = self.query_map_one(query, &[], |row| row.get(0)).unwrap().unwrap();
2811
2812 let query = "SELECT count(*)
2814 FROM session
2815 WHERE is_teacher = ?1;";
2816 let n_teacher: i64 = self.query_map_one(query, &[&true], |row| row.get(0)).unwrap().unwrap();
2817
2818 let query = "SELECT count(*)
2820 FROM participation;";
2821 let n_part: i64 = self.query_map_one(query, &[], |row| row.get(0)).unwrap().unwrap();
2822
2823 let n_sub: i64 = 0; let query = "SELECT contest, count(*)
2831 FROM participation
2832 GROUP BY contest
2833 ORDER BY contest DESC;";
2834 let n_participations_by_id: Vec<(i32, i64)> =
2835 self.query_map_many(query, &[], |row| (row.get(0), row.get(1))).unwrap();
2836
2837 let result = format!(
2838 "{{
2839 \"timestamp\": {},
2840 \"active_sessions\": {},
2841 \"active_participations\": {},
2842 \"sessions\": {},
2843 \"users\": {},
2844 \"pms_users\": {},
2845 \"teachers\": {},
2846 \"participations\": {},
2847 \"submissions\": {},
2848 \"participations_by_contest_id\": {{
2849 {}
2850 }}
2851}}
2852",
2853 now.sec,
2854 n_asession,
2855 n_apart,
2856 n_session,
2857 n_user,
2858 n_pmsuser,
2859 n_teacher,
2860 n_part,
2861 n_sub,
2862 n_participations_by_id.iter()
2863 .map(|(x, y)| -> String { format!("\"{}\": {}", x, y) })
2864 .collect::<Vec<String>>()
2865 .join(",\n ")
2866 );
2867
2868 let query = if db_has_value {
2869 "UPDATE string_cache
2870 SET value = ?2, date = ?3
2871 WHERE key = ?1"
2872 } else {
2873 "INSERT INTO string_cache (key, value, date)
2874 VALUES (?1, ?2, ?3)"
2875 };
2876 self.execute(query, &[&cache_key, &result, &now]).unwrap();
2877
2878 result
2879 }
2880
2881 fn reset_all_contest_visibilities(&self) { self.execute("UPDATE contest SET public = ?1", &[&false]).unwrap(); }
2882 fn reset_all_taskgroup_visibilities(&self) { self.execute("UPDATE taskgroup SET active = ?1", &[&false]).unwrap(); }
2883}