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 *
2136 FROM
2137 (
2138 SELECT participation.contest, COALESCE(SUM(grade.grade), 0) AS stars
2139 FROM participation
2140 JOIN taskgroup ON participation.contest = taskgroup.contest
2141 JOIN session ON session.id = participation.team
2142 OR (participation.team IS NULL AND session.id = participation.session)
2143 JOIN grade ON grade.taskgroup = taskgroup.id AND grade.session = session.id
2144 JOIN contest ON participation.contest = contest.id
2145 WHERE participation.session = ?1
2146 AND contest.location = ?2
2147 AND contest.filename = ?3
2148 GROUP BY participation.contest
2149 ) contests_with_stars
2150 WHERE stars >= ?4";
2151 self.exists(query, &[&session_id, &location, &filename, &(required_stars as i64)])
2152 }
2153
2154 fn new_participation(&self, session_id: i32, contest_id: i32, team: Option<i32>) -> Result<Participation, ()> {
2155 let query = "SELECT start_date
2156 FROM participation
2157 WHERE session = ?1
2158 AND contest = ?2";
2159 match self.query_map_one(query, &[&session_id, &contest_id], |_| {}).map_err(|_| ())? {
2160 Some(()) => Err(()),
2161 None => {
2162 let insert = "INSERT INTO participation (contest, session, start_date, team)
2163 VALUES (?1, ?2, ?3, ?4)";
2164
2165 let now = time::get_time();
2166 self.execute(insert, &[&contest_id, &session_id, &now, &team]).unwrap();
2167
2168 Ok(self.get_own_participation(session_id, contest_id).unwrap()) }
2170 }
2171 }
2172 fn get_task_by_id(&self, task_id: i32) -> Option<Task> {
2173 let query = "SELECT location, language, stars, taskgroup
2174 FROM task
2175 WHERE id = ?1";
2176 self.query_map_one(query, &[&task_id], |row| Task { id: Some(task_id),
2177 taskgroup: row.get(3),
2178 location: row.get(0),
2179 language: row.get(1),
2180 stars: row.get(2) })
2181 .unwrap()
2182 }
2183 fn get_task_by_id_complete(&self, task_id: i32) -> Option<(Task, Taskgroup, Contest)> {
2184 let query = "SELECT task.location, task.language, task.stars,
2185 taskgroup.id, taskgroup.name, taskgroup.active,
2186 contest.id, contest.location, contest.filename, contest.name, contest.duration,
2187 contest.public, contest.start_date, contest.end_date, contest.review_start_date,
2188 contest.review_end_date, contest.min_grade, contest.max_grade, contest.protected,
2189 contest.requires_login, contest.secret, contest.message,
2190 contest.category, contest.standalone_task, contest.colour
2191 FROM contest
2192 JOIN taskgroup ON taskgroup.contest = contest.id
2193 JOIN task ON task.taskgroup = taskgroup.id
2194
2195 WHERE task.id = ?1";
2196 self.query_map_one(query, &[&task_id], |row| {
2197 (Task { id: Some(task_id),
2198 taskgroup: row.get(3),
2199 location: row.get(0),
2200 language: row.get(1),
2201 stars: row.get(2) },
2202 Taskgroup { id: Some(row.get(3)),
2203 contest: row.get(6),
2204 name: row.get(4),
2205 active: row.get(5),
2206 positionalnumber: None,
2207 tasks: Vec::new() },
2208 Contest { id: Some(row.get(6)),
2209 location: row.get(7),
2210 filename: row.get(8),
2211 name: row.get(9),
2212 duration: row.get(10),
2213 public: row.get(11),
2214 start: row.get(12),
2215 end: row.get(13),
2216 review_start: row.get(14),
2217 review_end: row.get(15),
2218 min_grade: row.get(16),
2219 max_grade: row.get(17),
2220 positionalnumber: None,
2221 protected: row.get(18),
2222 requires_login: row.get(19),
2223 secret: row.get(20),
2224 message: row.get(21),
2225 image: None,
2226 language: None,
2227 category: row.get(22),
2228 standalone_task: row.get(23),
2229 colour: row.get(24),
2230 tags: Vec::new(),
2231 stickers: Vec::new(),
2232 requires_contests: Vec::new(),
2233 max_teamsize: None,
2234 taskgroups: Vec::new() })
2235 })
2236 .unwrap()
2237 }
2238
2239 fn get_submission_to_validate(&self, tasklocation: &str, subtask: Option<&str>) -> i32 {
2240 match subtask {
2241 Some(st) => {
2242 let query = "SELECT id
2243 FROM submission
2244 JOIN task ON submission.task = task.id
2245 WHERE task.location = ?1
2246 AND subtask_identifier = ?2
2247 AND needs_validation = 1
2248 LIMIT 1";
2249 self.query_map_one(query, &[&tasklocation, &st], |row| row.get(0)).unwrap().unwrap()
2250 }
2251 None => {
2252 let query = "SELECT id
2253 FROM submission
2254 JOIN task ON submission.task = task.id
2255 WHERE task.location = ?1
2256 AND needs_validation = 1
2257 LIMIT 1";
2258 self.query_map_one(query, &[&tasklocation], |row| row.get(0)).unwrap().unwrap()
2259 }
2260 }
2261 }
2262
2263 fn find_next_submission_to_validate(&self, userid: i32, taskgroupid: i32) {
2264 let query = "SELECT id, validated
2265 FROM submission
2266 JOIN task ON submission.task = task.id
2267 WHERE task.taskgroup = ?1
2268 AND submission.session = ?2
2269 ORDER BY value DESC id DESC
2270 LIMIT 1";
2271 let (id, validated): (i32, bool) =
2272 self.query_map_one(query, &[&taskgroupid, &userid], |row| (row.get(0), row.get(1))).unwrap().unwrap();
2273 if !validated {
2274 let query = "UPDATE submission
2275 SET needs_validation = 1
2276 WHERE id = ?1";
2277 self.execute(query, &[&id]).unwrap();
2278 }
2279 }
2280
2281 fn add_group(&self, group: &mut Group) { group.save(self); }
2282
2283 fn save_group(&self, group: &mut Group) { group.save(self); }
2284
2285 fn get_groups(&self, session_id: i32) -> Vec<Group> {
2286 let query = "SELECT id, name, groupcode, tag
2287 FROM usergroup
2288 JOIN usergroup_admin ON usergroup.id = usergroup_admin.usergroup
2289 WHERE usergroup_admin.session = ?1";
2290 self.query_map_many(query, &[&session_id], |row| Group { id: Some(row.get(0)),
2291 name: row.get(1),
2292 groupcode: row.get(2),
2293 tag: row.get(3),
2294 admins: Vec::new(),
2295 members: Vec::new() })
2296 .unwrap()
2297 }
2298 fn get_groups_complete(&self, _session_id: i32) -> Vec<Group> {
2299 unimplemented!();
2300 }
2301 fn get_group(&self, group_id: i32) -> Option<Group> {
2302 let query = "SELECT name, groupcode, tag
2303 FROM usergroup
2304 WHERE id = ?1";
2305 let mut group = self.query_map_one(query, &[&group_id], |row| Group { id: Some(group_id),
2306 name: row.get(0),
2307 groupcode: row.get(1),
2308 tag: row.get(2),
2309 admins: Vec::new(),
2310 members: Vec::new() })
2311 .unwrap()?;
2312
2313 let query = "SELECT session FROM usergroup_admin WHERE usergroup = ?1 ORDER BY session";
2314 group.admins = self.query_map_many(query, &[&group_id], |row| row.get(0)).unwrap();
2315
2316 Some(group)
2317 }
2318 fn group_has_protected_participations(&self, group_id: i32) -> bool {
2319 let query = "SELECT EXISTS(
2320 SELECT session.id
2321 FROM session
2322 JOIN participation ON participation.session = session.id
2323 JOIN contest ON contest.id = participation.contest
2324 WHERE managed_by = ?1
2325 AND contest.protected = ?2
2326 )";
2327 self.query_map_one(query, &[&group_id, &true], |row| row.get(0)).unwrap().unwrap()
2328 }
2329 fn get_group_complete(&self, group_id: i32) -> Option<Group> {
2330 let query = "SELECT name, groupcode, tag
2331 FROM usergroup
2332 WHERE id = ?1";
2333 let mut group = self.query_map_one(query, &[&group_id], |row| Group { id: Some(group_id),
2334 name: row.get(0),
2335 groupcode: row.get(1),
2336 tag: row.get(2),
2337 admins: Vec::new(),
2338 members: Vec::new() })
2339 .unwrap()?;
2340
2341 let query = "SELECT session FROM usergroup_admin WHERE usergroup = ?1 ORDER BY session";
2342 group.admins = self.query_map_many(query, &[&group_id], |row| row.get(0)).unwrap();
2343
2344 let query = "SELECT id, session_token, csrf_token, last_login, last_activity, account_created, username,
2345 password, salt, logincode, email, email_unconfirmed, email_confirmationcode, firstname,
2346 lastname, street, zip, city, nation, grade, sex, anonymous, additional_contest_time,
2347 data_protection_clearance, is_admin, is_teacher, school_name, oauth_provider,
2348 oauth_foreign_id
2349 FROM session
2350 WHERE managed_by = ?1
2351 ORDER BY id";
2352 group.members =
2353 self.query_map_many(query, &[&group_id], |row| SessionUser { id: row.get(0),
2354 session_token: row.get(1),
2355 csrf_token: row.get(2),
2356 last_login: row.get(3),
2357 last_activity: row.get(4),
2358 account_created: row.get(5),
2359
2360 username: row.get(6),
2361 password: row.get(7),
2362 salt: row.get(8),
2363 logincode: row.get(9),
2364 email: row.get(10),
2365 email_unconfirmed: row.get(11),
2366 email_confirmationcode: row.get(12),
2367
2368 firstname: row.get(13),
2369 lastname: row.get(14),
2370 street: row.get(15),
2371 zip: row.get(16),
2372 city: row.get(17),
2373 nation: row.get(18),
2374 grade: row.get(19),
2375 sex: row.get(20),
2376 anonymous: row.get(21),
2377 additional_contest_time: row.get(22),
2378 data_protection_clearance: row.get(23),
2379
2380 is_admin: row.get(24),
2381 is_teacher: row.get(25),
2382 managed_by: Some(group_id),
2383 school_name: row.get(26),
2384
2385 oauth_provider: row.get(27),
2386 oauth_foreign_id: row.get(28) })
2387 .unwrap();
2388 Some(group)
2389 }
2390
2391 fn delete_user(&self, user_id: i32) {
2392 let query = "DELETE FROM session
2393 WHERE id = ?1";
2394 self.execute(query, &[&user_id]).unwrap();
2395 }
2396 fn delete_all_users_for_group(&self, group_id: i32) {
2397 let query = "DELETE FROM session
2398 WHERE managed_by = ?1
2399 AND oauth_foreign_id IS NULL";
2400 self.execute(query, &[&group_id]).unwrap();
2401
2402 let query = "UPDATE session
2403 SET managed_by = NULL
2404 WHERE managed_by = ?1
2405 AND oauth_foreign_id IS NOT NULL";
2406 self.execute(query, &[&group_id]).unwrap();
2407 }
2408 fn set_data_clearance_for_user(&self, user_id: i32, clearance_state: bool) {
2409 let clearance_value: i32 = if clearance_state { 1 } else { 0 };
2410 let query = "UPDATE session
2411 SET data_protection_clearance = ?2
2412 WHERE id = ?1";
2413 self.execute(query, &[&user_id, &clearance_value]).unwrap();
2414 }
2415 fn set_data_clearance_for_group(&self, group_id: i32, clearance_state: bool) {
2416 let clearance_value: i32 = if clearance_state { 1 } else { 0 };
2417 let query = "UPDATE session
2418 SET data_protection_clearance = ?2
2419 WHERE managed_by = ?1";
2420 self.execute(query, &[&group_id, &clearance_value]).unwrap();
2421 }
2422 fn delete_group(&self, group_id: i32) {
2423 let query = "DELETE FROM usergroup
2424 WHERE id = ?1";
2425 self.execute(query, &[&group_id]).unwrap();
2426 }
2427 fn delete_participation(&self, user_id: i32, contest_id: i32) {
2428 let query = "DELETE FROM submission
2429 WHERE id IN (
2430 SELECT submission.id FROM submission
2431 JOIN task ON submission.task = task.id
2432 JOIN taskgroup ON task.taskgroup = taskgroup.id
2433 WHERE taskgroup.contest = ?1
2434 AND submission.session = ?2
2435 )";
2436 self.execute(query, &[&contest_id, &user_id]).unwrap();
2437
2438 let query = "DELETE FROM grade
2439 WHERE taskgroup IN (
2440 SELECT id FROM taskgroup
2441 WHERE taskgroup.contest = ?1
2442 )
2443 AND session = ?2";
2444 self.execute(query, &[&contest_id, &user_id]).unwrap();
2445
2446 let query = "DELETE FROM participation
2447 WHERE contest = ?1
2448 AND session = ?2";
2449 self.execute(query, &[&contest_id, &user_id]).unwrap();
2450 }
2451
2452 fn get_search_users(
2453 &self,
2454 (s_id, s_firstname, s_lastname, s_logincode, s_groupname, s_groupcode, s_pms_id, s_anything): (Option<i32>,
2455 Option<String>,
2456 Option<String>,
2457 Option<String>,
2458 Option<String>,
2459 Option<String>,
2460 Option<String>,
2461 Option<String>))
2462 -> (Vec<(i32, Option<String>, Option<String>, Option<String>, Option<String>, Option<String>)>,
2463 Vec<(i32, String, String, String)>) {
2464 let mut users = Vec::new();
2465 let mut groups = Vec::new();
2466
2467 if let Some(id) = s_id {
2468 let query = "SELECT id, firstname, lastname, logincode, oauth_foreign_id, oauth_provider
2469 FROM session
2470 WHERE id = ?1
2471 LIMIT 201";
2472 users = self.query_map_many(query, &[&id], |row| {
2473 (row.get(0), row.get(1), row.get(2), row.get(3), row.get(4), row.get(5))
2474 })
2475 .unwrap();
2476 } else if let Some(logincode) = s_logincode {
2477 let query = "SELECT id, firstname, lastname, logincode, oauth_foreign_id, oauth_provider
2478 FROM session
2479 WHERE logincode = ?1
2480 LIMIT 201";
2481 users = self.query_map_many(query, &[&logincode], |row| {
2482 (row.get(0), row.get(1), row.get(2), row.get(3), row.get(4), row.get(5))
2483 })
2484 .unwrap()
2485 } else if let Some(groupcode) = s_groupcode {
2486 let query = "SELECT id, name, tag, groupcode
2487 FROM usergroup
2488 WHERE groupcode = ?1
2489 LIMIT 201";
2490 groups = self.query_map_many(query, &[&groupcode], |row| (row.get(0), row.get(1), row.get(2), row.get(3)))
2491 .unwrap()
2492 } else if let Some(groupname) = s_groupname {
2493 let query = "SELECT id, name, tag, groupcode
2494 FROM usergroup
2495 WHERE name LIKE ?1
2496 LIMIT 201";
2497 groups = self.query_map_many(query, &[&groupname], |row| (row.get(0), row.get(1), row.get(2), row.get(3)))
2498 .unwrap()
2499 } else if let Some(pms_id) = s_pms_id {
2500 let query = "SELECT id, firstname, lastname, logincode, oauth_foreign_id, oauth_provider
2501 FROM session
2502 WHERE oauth_foreign_id = ?1
2503 OR oauth_foreign_id LIKE ?2
2504 LIMIT 201";
2505 users = self.query_map_many(query, &[&pms_id, &format!("{}/%", pms_id)], |row| {
2506 (row.get(0), row.get(1), row.get(2), row.get(3), row.get(4), row.get(5))
2507 })
2508 .unwrap()
2509 } else if let (Some(firstname), Some(lastname)) = (s_firstname, s_lastname) {
2510 let query = "SELECT id, firstname, lastname, logincode, oauth_foreign_id, oauth_provider
2511 FROM session
2512 WHERE firstname LIKE ?1
2513 AND lastname LIKE ?2
2514 ORDER BY id DESC
2515 LIMIT 201";
2516 users = self.query_map_many(query, &[&format!("%{}%", firstname), &format!("%{}%", lastname)], |row| {
2517 (row.get(0), row.get(1), row.get(2), row.get(3), row.get(4), row.get(5))
2518 })
2519 .unwrap()
2520 } else if let Some(anything) = s_anything {
2521 if anything.len() == 0 {
2522 return (users, groups);
2523 }
2524
2525 let query = "SELECT id, name, tag, groupcode
2526 FROM usergroup
2527 WHERE groupcode = ?1
2528 OR name LIKE ?2
2529 LIMIT 201";
2530 groups = self.query_map_many(query, &[&anything, &format!("%{}%", anything)], |row| {
2531 (row.get(0), row.get(1), row.get(2), row.get(3))
2532 })
2533 .unwrap();
2534
2535 if let Ok(id) = anything.parse::<i32>() {
2536 let query = "SELECT id, firstname, lastname, logincode, oauth_foreign_id, oauth_provider
2537 FROM session
2538 WHERE id = ?4
2539 OR logincode = ?1
2540 OR firstname LIKE ?2
2541 OR lastname LIKE ?2
2542 OR oauth_foreign_id = ?1
2543 OR oauth_foreign_id LIKE ?3
2544 LIMIT 201";
2545 users =
2546 self.query_map_many(query,
2547 &[&anything, &format!("%{}%", anything), &format!("{}/%", anything), &id],
2548 |row| (row.get(0), row.get(1), row.get(2), row.get(3), row.get(4), row.get(5)))
2549 .unwrap()
2550 } else {
2551 let v: Vec<&str> = anything.split(' ').collect();
2552 match v.len() {
2553 0 => (),
2554 1 => {
2555 let query = "SELECT id, firstname, lastname, logincode, oauth_foreign_id, oauth_provider
2556 FROM session
2557 WHERE logincode = ?1
2558 OR firstname LIKE ?2
2559 OR lastname LIKE ?2
2560 OR oauth_foreign_id = ?1
2561 OR oauth_foreign_id LIKE ?3
2562 LIMIT 201";
2563 users =
2564 self.query_map_many(query,
2565 &[&anything, &format!("%{}%", anything), &format!("{}/%", anything)],
2566 |row| {
2567 (row.get(0),
2568 row.get(1),
2569 row.get(2),
2570 row.get(3),
2571 row.get(4),
2572 row.get(5))
2573 })
2574 .unwrap()
2575 }
2576 _ => {
2578 let query = "SELECT id, firstname, lastname, logincode, oauth_foreign_id, oauth_provider
2579 FROM session
2580 WHERE (logincode = ?1
2581 OR firstname LIKE ?2
2582 OR lastname LIKE ?2
2583 OR oauth_foreign_id = ?1
2584 OR oauth_foreign_id LIKE ?3)
2585 AND (logincode = ?4
2586 OR firstname LIKE ?5
2587 OR lastname LIKE ?5
2588 OR oauth_foreign_id = ?4
2589 OR oauth_foreign_id LIKE ?6)
2590 LIMIT 201";
2591 users = self.query_map_many(query,
2592 &[&v[0],
2593 &format!("%{}%", v[0]),
2594 &format!("{}/%", v[0]),
2595 &v[1],
2596 &format!("%{}%", v[1]),
2597 &format!("{}/%", v[1])],
2598 |row| {
2599 (row.get(0),
2600 row.get(1),
2601 row.get(2),
2602 row.get(3),
2603 row.get(4),
2604 row.get(5))
2605 })
2606 .unwrap()
2607 }
2608 }
2609 }
2610 }
2611
2612 (users, groups)
2613 }
2614
2615 fn remove_old_users_and_groups(&self, maxstudentage: time::Timespec, maxteacherage: Option<time::Timespec>,
2617 maxage: Option<time::Timespec>)
2618 -> Result<(i32, i32, i32, i32), ()> {
2619 let query = "SELECT managed_by
2621 FROM session
2622 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
2623 AND ((last_login < ?1 AND last_activity < ?1)
2624 OR (last_login < ?1 AND last_activity IS NULL)
2625 OR (last_login IS NULL AND last_activity < ?1)
2626 OR (last_login IS NULL AND last_activity IS NULL AND account_created < ?1))";
2627 let mut groups: Vec<i32> = self.query_map_many(query, &[&maxstudentage], |row| row.get(0)).unwrap();
2628
2629 let query = "DELETE
2631 FROM session
2632 WHERE username IS NULL AND password IS NULL AND oauth_foreign_id IS NULL AND oauth_provider IS NULL
2633 AND ((last_login < ?1 AND last_activity < ?1)
2634 OR (last_login < ?1 AND last_activity IS NULL)
2635 OR (last_login IS NULL AND last_activity < ?1)
2636 OR (last_login IS NULL AND last_activity IS NULL AND account_created < ?1))";
2637 self.execute(query, &[&maxstudentage]).unwrap();
2638
2639 let n_users = groups.len() as i32;
2641 let mut n_groups: i32 = 0;
2642 let mut n_teachers: i32 = 0;
2643 let mut n_other: i32 = 0;
2644
2645 groups.sort_unstable();
2647 groups.dedup();
2648
2649 let query = "SELECT count(*)
2651 FROM session
2652 WHERE managed_by = ?1;";
2653 for group in groups {
2654 let groupsize: i64 = self.query_map_one(query, &[&group], |row| row.get(0)).unwrap().unwrap();
2655
2656 if groupsize == 0 {
2657 let query = "DELETE
2658 FROM usergroup
2659 WHERE id = ?1";
2660 self.execute(query, &[&group]).unwrap();
2661
2662 n_groups += 1;
2663 }
2664 }
2665
2666 let query = "SELECT id
2668 FROM usergroup
2669 WHERE group_created < ?1";
2670 let groups: Vec<i32> = self.query_map_many(query, &[&maxstudentage], |row| row.get(0)).unwrap();
2671 let query = "SELECT count(*)
2672 FROM session
2673 WHERE managed_by = ?1;";
2674 for group in groups {
2675 let groupsize: i64 = self.query_map_one(query, &[&group], |row| row.get(0)).unwrap().unwrap();
2676
2677 if groupsize == 0 {
2678 let query = "DELETE
2679 FROM usergroup
2680 WHERE id = ?1";
2681 self.execute(query, &[&group]).unwrap();
2682
2683 n_groups += 1;
2684 }
2685 }
2686
2687 let query = "SELECT id
2689 FROM session
2690 WHERE is_teacher = ?1
2691 AND ((last_login < ?2 AND last_activity < ?2)
2692 OR (last_login < ?2 AND last_activity IS NULL)
2693 OR (last_login IS NULL AND last_activity < ?2)
2694 OR (last_login IS NULL AND last_activity IS NULL AND account_created < ?2))";
2695 if let Some(maxteacherage) = maxteacherage {
2696 let teachers: Vec<i32> = self.query_map_many(query, &[&true, &maxteacherage], |row| row.get(0)).unwrap();
2697
2698 let query = "SELECT count(*)
2700 FROM usergroup_admin
2701 WHERE session = ?1;";
2702 for teacher in teachers {
2703 let groupcount: i64 = self.query_map_one(query, &[&teacher], |row| row.get(0)).unwrap().unwrap();
2704
2705 if groupcount == 0 {
2706 let query = "DELETE
2707 FROM session
2708 WHERE id = ?1";
2709 self.execute(query, &[&teacher]).unwrap();
2710
2711 n_teachers += 1;
2712 }
2713 }
2714 }
2715
2716 if let Some(maxage) = maxage {
2718 let query = "SELECT count(*)
2719 FROM session
2720 WHERE ((last_login < ?1 AND last_activity < ?1)
2721 OR (last_login < ?1 AND last_activity IS NULL)
2722 OR (last_login IS NULL AND last_activity < ?1)
2723 OR (last_login IS NULL AND last_activity IS NULL AND account_created < ?1))";
2724 n_other = self.query_map_one(query, &[&maxage], |row| row.get::<_, i64>(0) as i32).unwrap().unwrap();
2725
2726 let query = "DELETE
2727 FROM session
2728 WHERE ((last_login < ?1 AND last_activity < ?1)
2729 OR (last_login < ?1 AND last_activity IS NULL)
2730 OR (last_login IS NULL AND last_activity < ?1)
2731 OR (last_login IS NULL AND last_activity IS NULL AND account_created < ?1))";
2732 self.execute(query, &[&maxage]).unwrap();
2733 }
2734
2735 Ok((n_users, n_groups, n_teachers, n_other))
2736 }
2737
2738 fn count_temporary_sessions(&self, maxage: time::Timespec) -> i32 {
2739 let query = "SELECT count(*)
2740 FROM session
2741 WHERE (last_activity < ?1 OR last_activity IS NULL)
2742 AND logincode IS NULL
2743 AND password IS NULL
2744 AND oauth_foreign_id IS NULL";
2745 self.query_map_one(query, &[&maxage], |row| row.get::<_, i64>(0) as i32).unwrap().unwrap()
2746 }
2747
2748 fn remove_temporary_sessions(&self, maxage: time::Timespec, limit: Option<u32>) {
2749 if let Some(limit) = limit {
2754 let query = "DELETE
2755 FROM session
2756 WHERE id IN (SELECT id
2757 FROM session
2758 WHERE (last_activity < ?1 OR last_activity IS NULL)
2759 AND logincode IS NULL
2760 AND password IS NULL
2761 AND oauth_foreign_id IS NULL
2762 ORDER BY last_activity
2763 LIMIT ?2)";
2764 self.execute(query, &[&maxage, &(limit as i64)]).unwrap();
2765 } else {
2766 let query = "DELETE
2767 FROM session
2768 WHERE (last_activity < ?1 OR last_activity IS NULL)
2769 AND logincode IS NULL
2770 AND password IS NULL
2771 AND oauth_foreign_id IS NULL";
2772 self.execute(query, &[&maxage]).unwrap();
2773 }
2774 }
2775
2776 fn remove_autosaved_submissions(&self, maxage: time::Timespec, limit: Option<u32>) {
2777 if let Some(limit) = limit {
2778 let query = "DELETE
2779 FROM submission
2780 WHERE id IN (SELECT id
2781 FROM submission
2782 WHERE date < ?1
2783 AND autosave = ?2
2784 ORDER BY date
2785 LIMIT ?3)";
2786 self.execute(query, &[&maxage, &true, &(limit as i64)]).unwrap();
2787 } else {
2788 let query = "DELETE
2789 FROM submission
2790 WHERE date < ?1
2791 AND autosave = ?2";
2792 self.execute(query, &[&maxage, &true]).unwrap();
2793 }
2794 }
2795
2796 fn remove_all_but_latest_submissions(&self, maxage: time::Timespec, limit: Option<u32>) {
2797 if let Some(limit) = limit {
2798 let query = "DELETE
2799 FROM submission
2800 WHERE id IN (SELECT id
2801 FROM submission
2802 WHERE date < ?1
2803 AND higher_grade = ?2
2804 AND latest = ?2
2805 AND highest_grade_latest = ?2
2806 ORDER BY date
2807 LIMIT ?3)";
2808 self.execute(query, &[&maxage, &false, &(limit as i64)]).unwrap();
2809 } else {
2810 let query = "DELETE
2811 FROM submission
2812 WHERE date < ?1
2813 AND higher_grade = ?2
2814 AND latest = ?2
2815 AND highest_grade_latest = ?2";
2816 self.execute(query, &[&maxage, &false]).unwrap();
2817 }
2818 }
2819
2820 fn move_task_location(&self, old_location: &str, new_location: &str, contest: Option<i32>) -> i32 {
2821 let query = "SELECT contest.id, oldtaskgroup.id, newtaskgroup.id, oldtask.id, newtask.id
2822 FROM contest
2823 JOIN taskgroup AS oldtaskgroup ON oldtaskgroup.contest = contest.id
2824 JOIN task AS oldtask ON oldtask.taskgroup = oldtaskgroup.id
2825 JOIN taskgroup AS newtaskgroup ON newtaskgroup.contest = contest.id
2826 JOIN task AS newtask ON newtask.taskgroup = newtaskgroup.id
2827 WHERE oldtask.location = ?1
2828 AND newtask.location = ?2;";
2829
2830 let mut tasks: Vec<(i32, (i32, i32), (i32, i32))> =
2831 self.query_map_many(query, &[&old_location, &new_location], |row| {
2832 (row.get(0), (row.get(1), row.get(2)), (row.get(3), row.get(4)))
2833 })
2834 .unwrap();
2835
2836 if let Some(contest) = contest {
2837 tasks.retain(|t| t.0 == contest);
2838 }
2839
2840 let query = "WITH cte AS (
2841 SELECT ?2 AS taskgroup, oldgrade.session, GREATEST(oldgrade.grade, newgrade.grade) AS grade, oldgrade.validated
2842 FROM grade AS oldgrade
2843 LEFT JOIN grade AS newgrade ON oldgrade.session = newgrade.session AND newgrade.taskgroup = ?2
2844 WHERE oldgrade.taskgroup = ?1
2845 )
2846 INSERT INTO grade (taskgroup, session, grade, validated)
2847 SELECT * FROM CTE
2848 ON CONFLICT (taskgroup, session)
2849 DO UPDATE SET grade = excluded.grade, validated = excluded.validated;";
2850
2851 for task in &tasks {
2852 self.execute(query, &[&task.1 .0, &task.1 .1]).unwrap();
2853 }
2854
2855 let query = "DELETE
2856 FROM grade
2857 WHERE taskgroup = ?1;";
2858
2859 for task in &tasks {
2860 self.execute(query, &[&task.1 .0]).unwrap();
2861 }
2862
2863 let limitquery = "SELECT id
2864 FROM submission
2865 WHERE task = ?1
2866 LIMIT 1;";
2867
2868 let query = "WITH cte AS (
2869 SELECT id
2870 FROM submission
2871 WHERE task = ?1
2872 LIMIT 10000
2873 )
2874 UPDATE submission s
2875 SET task = ?2
2876 FROM cte
2877 WHERE s.id = cte.id;";
2878
2879 for task in &tasks {
2880 while self.query_map_one(limitquery, &[&task.2 .0], |_| ()).unwrap().is_some() {
2881 self.execute(query, &[&task.2 .0, &task.2 .1]).unwrap();
2882 }
2883 }
2884
2885 tasks.len() as i32
2886 }
2887
2888 fn get_debug_information(&self) -> String {
2889 let now = time::get_time();
2890 let cache_key = "dbstatus";
2891
2892 let query = "SELECT value, date
2893 FROM string_cache
2894 WHERE key = ?1";
2895
2896 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() {
2898 if cache_date.sec / 60 >= now.sec / 60 {
2900 return cached_value;
2901 }
2902 true
2903 } else {
2904 false
2905 };
2906
2907 let duration = Duration::minutes(60);
2908 let then = now - duration;
2909
2910 let query = "SELECT count(*)
2912 FROM session
2913 WHERE last_activity > ?1;";
2914 let n_asession: i64 = self.query_map_one(query, &[&then], |row| row.get(0)).unwrap().unwrap();
2915
2916 let query = "SELECT count(*)
2918 FROM participation
2919 WHERE start_date > ?1;";
2920 let n_apart: i64 = self.query_map_one(query, &[&then], |row| row.get(0)).unwrap().unwrap();
2921
2922 let query = "SELECT count(*)
2924 FROM session;";
2925 let n_session: i64 = self.query_map_one(query, &[], |row| row.get(0)).unwrap().unwrap();
2926
2927 let query = "SELECT count(*)
2929 FROM session
2930 WHERE oauth_foreign_id IS NOT NULL OR logincode IS NOT NULL;";
2931 let n_user: i64 = self.query_map_one(query, &[], |row| row.get(0)).unwrap().unwrap();
2932
2933 let query = "SELECT count(*)
2935 FROM session
2936 WHERE oauth_foreign_id IS NOT NULL;";
2937 let n_pmsuser: i64 = self.query_map_one(query, &[], |row| row.get(0)).unwrap().unwrap();
2938
2939 let query = "SELECT count(*)
2941 FROM session
2942 WHERE is_teacher = ?1;";
2943 let n_teacher: i64 = self.query_map_one(query, &[&true], |row| row.get(0)).unwrap().unwrap();
2944
2945 let query = "SELECT count(*)
2947 FROM participation;";
2948 let n_part: i64 = self.query_map_one(query, &[], |row| row.get(0)).unwrap().unwrap();
2949
2950 let n_sub: i64 = 0; let query = "SELECT contest, count(*)
2958 FROM participation
2959 GROUP BY contest
2960 ORDER BY contest DESC;";
2961 let n_participations_by_id: Vec<(i32, i64)> =
2962 self.query_map_many(query, &[], |row| (row.get(0), row.get(1))).unwrap();
2963
2964 let result = format!(
2965 "{{
2966 \"timestamp\": {},
2967 \"active_sessions\": {},
2968 \"active_participations\": {},
2969 \"sessions\": {},
2970 \"users\": {},
2971 \"pms_users\": {},
2972 \"teachers\": {},
2973 \"participations\": {},
2974 \"submissions\": {},
2975 \"participations_by_contest_id\": {{
2976 {}
2977 }}
2978}}
2979",
2980 now.sec,
2981 n_asession,
2982 n_apart,
2983 n_session,
2984 n_user,
2985 n_pmsuser,
2986 n_teacher,
2987 n_part,
2988 n_sub,
2989 n_participations_by_id.iter()
2990 .map(|(x, y)| -> String { format!("\"{}\": {}", x, y) })
2991 .collect::<Vec<String>>()
2992 .join(",\n ")
2993 );
2994
2995 let query = if db_has_value {
2996 "UPDATE string_cache
2997 SET value = ?2, date = ?3
2998 WHERE key = ?1"
2999 } else {
3000 "INSERT INTO string_cache (key, value, date)
3001 VALUES (?1, ?2, ?3)"
3002 };
3003 self.execute(query, &[&cache_key, &result, &now]).unwrap();
3004
3005 result
3006 }
3007
3008 fn reset_all_contest_visibilities(&self) { self.execute("UPDATE contest SET public = ?1", &[&false]).unwrap(); }
3009 fn reset_all_taskgroup_visibilities(&self) { self.execute("UPDATE taskgroup SET active = ?1", &[&false]).unwrap(); }
3010}