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