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