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 FROM grade
1221 JOIN taskgroup ON grade.taskgroup = taskgroup.id
1222 JOIN participation ON (participation.session = grade.session OR participation.team = grade.session) AND participation.contest = ?2
1223 JOIN session AS student ON participation.session = student.id
1224 JOIN usergroup ON student.managed_by = usergroup.id
1225 JOIN usergroup_admin ON usergroup.id = usergroup_admin.usergroup
1226 WHERE usergroup_admin.session = ?1
1227 AND taskgroup.contest = ?2
1228 AND taskgroup.active = ?3
1229 ORDER BY usergroup.id, sgrade, student.lastname, student.firstname, student.id,
1230 taskgroup.positionalnumber";
1231 let gradeinfo =
1232 self.query_map_many(query, &[&session_id, &contest_id, &true], |row| {
1233 (Grade { taskgroup: row.get(0), user: row.get(1), grade: row.get(2), validated: row.get(3) },
1234 Group { id: Some(row.get(4)),
1235 name: row.get(5),
1236 groupcode: row.get(6),
1237 tag: row.get(7),
1238 admins: Vec::new(),
1239 members: Vec::new() },
1240 UserInfo { id: row.get(8),
1241 username: row.get(9),
1242 logincode: row.get(10),
1243 firstname: row.get(11),
1244 lastname: row.get(12),
1245 grade: row.get(13),
1246 annotation: row.get(14) })
1247 })
1248 .unwrap();
1249 let mut gradeinfo_iter = gradeinfo.iter();
1250
1251 if let Some(t ) = gradeinfo_iter.next() {
1252 let (grade, mut group, mut userinfo) = t.clone();
1253
1254 let mut grades: Vec<Grade> = vec![Default::default(); n_tasks];
1255 let mut users: Vec<(UserInfo, Vec<Grade>)> = Vec::new();
1256 let mut groups: Vec<(Group, Vec<(UserInfo, Vec<Grade>)>)> = Vec::new();
1257
1258 let index = grade.taskgroup;
1259 grades[taskindex[&index]] = grade;
1260
1261 for ggu in gradeinfo_iter {
1262 let (g, gr, ui) = ggu;
1263 if gr.id != group.id {
1264 users.push((userinfo, grades));
1265 userinfo = ui.clone();
1266 grades = vec![Default::default(); n_tasks];
1267
1268 groups.push((group, users));
1269 group = gr.clone();
1270 users = Vec::new();
1271 } else if ui.id != userinfo.id {
1272 users.push((userinfo, grades));
1273 userinfo = ui.clone();
1274 grades = vec![Default::default(); n_tasks];
1275 }
1276 let index = g.taskgroup;
1277 grades[taskindex[&index]] = *g;
1278 }
1279 users.push((userinfo, grades));
1280 groups.push((group, users));
1281
1282 (tasknames.iter().map(|(_, name)| name.clone()).collect(), groups)
1283 } else {
1284 (Vec::new(), Vec::new()) }
1286 }
1287 fn get_contest_user_grades(&self, session_id: i32, contest_id: i32) -> Vec<Grade> {
1288 let query = "SELECT id, name
1289 FROM taskgroup
1290 WHERE contest = ?1
1291 AND active = ?2
1292 ORDER BY positionalnumber";
1293 let tasknames: Vec<(i32, String)> =
1294 self.query_map_many(query, &[&contest_id, &true], |row| (row.get(0), row.get(1))).unwrap();
1295 let mut taskindex: ::std::collections::BTreeMap<i32, usize> = ::std::collections::BTreeMap::new();
1296
1297 let n_tasks = tasknames.len();
1298 for (index, (i, _)) in tasknames.iter().enumerate() {
1299 taskindex.insert(*i, index);
1300 }
1301
1302 let query = "SELECT grade.taskgroup, grade.session, grade.grade, grade.validated
1303 FROM grade
1304 JOIN taskgroup ON grade.taskgroup = taskgroup.id
1305 WHERE grade.session = ?1
1306 AND taskgroup.contest = ?2
1307 AND taskgroup.active = ?3
1308 ORDER BY taskgroup.positionalnumber";
1309 let gradeinfo =
1310 self.query_map_many(query, &[&session_id, &contest_id, &true], |row| Grade { taskgroup: row.get(0),
1311 user: row.get(1),
1312 grade: row.get(2),
1313 validated: row.get(3) })
1314 .unwrap();
1315 let gradeinfo_iter = gradeinfo.iter();
1316
1317 let mut grades: Vec<Grade> = vec![Default::default(); n_tasks];
1318
1319 for g in gradeinfo_iter {
1320 let index = g.taskgroup;
1321 grades[taskindex[&index]] = *g;
1322 }
1323
1324 grades
1325 }
1326
1327 fn get_taskgroup_user_grade(&self, session_id: i32, taskgroup_id: i32) -> Grade {
1328 let query = "SELECT grade.taskgroup, grade.session, grade.grade, grade.validated
1329 FROM grade
1330 WHERE grade.session = ?1
1331 AND grade.taskgroup = ?2";
1332 self.query_map_one(query, &[&session_id, &taskgroup_id], |row| Grade { taskgroup: row.get(0),
1333 user: row.get(1),
1334 grade: row.get(2),
1335 validated: row.get(3) })
1336 .unwrap_or(None)
1337 .unwrap_or_default()
1338 }
1339
1340 fn export_contest_results_to_file(&self, contest_id: i32, taskgroups: &[(i32, String)], filename: &str) {
1342 use std::fs::OpenOptions;
1343 let file = OpenOptions::new().write(true).create(true).truncate(true).open(filename).unwrap();
1344 let mut headers = vec!["id",
1345 "username",
1346 "logincode",
1347 "oauth_foreign_id",
1348 "oauth_provider",
1349 "firstname",
1350 "lastname",
1351 "grade",
1352 "sex",
1353 "anonymous",
1354 "is_teacher",
1355 "group_id",
1356 "group_name",
1357 "group_tag",
1358 "teacher_id",
1359 "teacher_firstname",
1360 "teacher_lastname",
1361 "teacher_oauth_foreign_id",
1362 "teacher_oauth_school_id",
1363 "teacher_oauth_provider",
1364 "contest_id",
1365 "is_team_participation",
1366 "team_lead",
1367 "start_date"];
1368
1369 let mut select_part = String::new();
1370 let mut join_part = String::new();
1371
1372 let mut join_params = gen_tosql_vector();
1373
1374 join_params.push(&contest_id);
1375
1376 for (n, (id, name)) in taskgroups.iter().enumerate() {
1377 use std::fmt::Write;
1378
1379 write!(select_part, ",\n g{}.grade ", n).unwrap();
1380 write!(join_part,
1381 "\n LEFT JOIN grade AS g{} ON gsession.id = g{}.session AND g{}.taskgroup = ?{} ",
1382 n,
1383 n,
1384 n,
1385 n + 2).unwrap();
1386 join_params.push(id);
1387 headers.push(&name);
1388 }
1389
1390 let query = format!("SELECT session.id,
1391 session.username,
1392 session.logincode,
1393 session.oauth_foreign_id,
1394 session.oauth_provider,
1395 session.firstname,
1396 session.lastname,
1397 session.grade,
1398 session.sex,
1399 session.anonymous,
1400 session.is_teacher,
1401 session.managed_by,
1402 ug.name,
1403 ug.tag,
1404 teacher.id,
1405 teacher.firstname,
1406 teacher.lastname,
1407 teacher.oauth_foreign_id,
1408 teacher.oauth_provider,
1409 participation.contest,
1410 participation.team,
1411 participation.start_date
1412 {}
1413 FROM participation
1414 JOIN session ON participation.session = session.id
1415 JOIN session AS gsession ON participation.team = gsession.id OR (participation.team IS NULL AND participation.session = gsession.id)
1416 {}
1417 LEFT JOIN usergroup AS ug ON session.managed_by = ug.id
1418 LEFT JOIN session AS teacher ON teacher.id = (SELECT MIN(usergroup_admin.session) FROM usergroup_admin WHERE usergroup_admin.usergroup = ug.id)
1419 WHERE participation.contest = ?1",
1420 select_part, join_part);
1421
1422 use csv::Writer;
1423 let mut wtr = Writer::from_writer(file);
1424 wtr.serialize(&headers).unwrap();
1425 wtr.flush().unwrap();
1426
1427 let file = wtr.into_inner().unwrap();
1428 let mut wtr = Writer::from_writer(file);
1429
1430 self.query_map_many(&query, join_params.as_slice(), |row| {
1431 let mut points = Vec::new();
1432 for i in 22..22 + taskgroups.len() {
1434 points.push(row.get::<_, Option<i32>>(i));
1435 }
1436
1437 let teacher_oauth_and_school_id = row.get::<_, Option<String>>(17);
1438 let (teacher_oauth_id, teacher_school_id) = if let Some(toasi) = teacher_oauth_and_school_id {
1439 let mut v = toasi.split('/');
1440 let oid: Option<String> = v.next().map(|s| s.to_owned());
1441 let sid: Option<String> = v.next().map(|s| s.to_owned());
1442 (oid, sid)
1443 } else {
1444 (None, None)
1445 };
1446
1447 let team_lead = row.get::<_, Option<i32>>(20);
1448
1449 wtr.serialize(((row.get::<_, i32>(0),
1452 row.get::<_, Option<String>>(1),
1453 row.get::<_, Option<String>>(2),
1454 row.get::<_, Option<String>>(3),
1455 row.get::<_, Option<String>>(4),
1456 row.get::<_, Option<String>>(5),
1457 row.get::<_, Option<String>>(6),
1458 row.get::<_, i32>(7),
1459 row.get::<_, Option<i32>>(8),
1460 row.get::<_, bool>(9)),
1461 (row.get::<_, bool>(10),
1462 row.get::<_, Option<i32>>(11),
1463 row.get::<_, Option<String>>(12),
1464 row.get::<_, Option<String>>(13),
1465 row.get::<_, Option<i32>>(14),
1466 row.get::<_, Option<String>>(15),
1467 row.get::<_, Option<String>>(16),
1468 teacher_oauth_id,
1469 teacher_school_id),
1470 (row.get::<_, Option<String>>(18),
1471 row.get::<_, Option<i32>>(19),
1472 team_lead.is_some(),
1473 team_lead,
1474 row.get::<_, Option<time::Timespec>>(21)
1475 .map(|ts| self::time::strftime("%FT%T%z", &time::at(ts)).unwrap()),
1476 points)))
1477 .unwrap();
1478 })
1479 .unwrap();
1480 wtr.flush().unwrap();
1481 }
1482
1483 fn insert_contest_annotations(&self, contest_id: i32, annotations: Vec<(i32, Option<String>)>) -> i32 {
1484 let batch_size = 10;
1485 let query_batch = if self.dbtype() == "postgres" {
1486 "UPDATE participation
1487 SET annotation = batchdata.annotation
1488 FROM (SELECT ?2::int AS userid, ?3 as annotation
1489 UNION ALL SELECT ?4::int AS userid, ?5 as annotation
1490 UNION ALL SELECT ?6::int AS userid, ?7 as annotation
1491 UNION ALL SELECT ?8::int AS userid, ?9 as annotation
1492 UNION ALL SELECT ?10::int AS userid, ?11 as annotation
1493 UNION ALL SELECT ?12::int AS userid, ?13 as annotation
1494 UNION ALL SELECT ?14::int AS userid, ?15 as annotation
1495 UNION ALL SELECT ?16::int AS userid, ?17 as annotation
1496 UNION ALL SELECT ?18::int AS userid, ?19 as annotation
1497 UNION ALL SELECT ?20::int AS userid, ?21 as annotation
1498 ) AS batchdata
1499 WHERE session = batchdata.userid
1500 AND contest = ?1"
1501 } else {
1502 "UPDATE participation
1503 SET annotation = batchdata.annotation
1504 FROM (SELECT ?2 AS userid, ?3 as annotation
1505 UNION ALL SELECT ?4 AS userid, ?5 as annotation
1506 UNION ALL SELECT ?6 AS userid, ?7 as annotation
1507 UNION ALL SELECT ?8 AS userid, ?9 as annotation
1508 UNION ALL SELECT ?10 AS userid, ?11 as annotation
1509 UNION ALL SELECT ?12 AS userid, ?13 as annotation
1510 UNION ALL SELECT ?14 AS userid, ?15 as annotation
1511 UNION ALL SELECT ?16 AS userid, ?17 as annotation
1512 UNION ALL SELECT ?18 AS userid, ?19 as annotation
1513 UNION ALL SELECT ?20 AS userid, ?21 as annotation
1514 ) AS batchdata
1515 WHERE session = batchdata.userid
1516 AND contest = ?1"
1517 };
1518
1519 let query_single = "UPDATE participation
1520 SET annotation = ?3
1521 WHERE session = ?2
1522 AND contest = ?1";
1523
1524 let n_annotations = annotations.len();
1525 let n_batches = n_annotations / batch_size;
1526 let n_single = n_annotations % batch_size;
1527
1528 #[cfg(feature = "debug")]
1529 println!("Annotations: {}, {} batches a {}, {} single", n_annotations, n_batches, batch_size, n_single);
1530
1531 let mut rows_modified = 0;
1532
1533 for batch in 0..n_batches {
1534 let off = batch * batch_size;
1535 rows_modified += self.execute(query_batch,
1536 &[&contest_id,
1537 &annotations[off].0,
1538 &annotations[off].1,
1539 &annotations[off + 1].0,
1540 &annotations[off + 1].1,
1541 &annotations[off + 2].0,
1542 &annotations[off + 2].1,
1543 &annotations[off + 3].0,
1544 &annotations[off + 3].1,
1545 &annotations[off + 4].0,
1546 &annotations[off + 4].1,
1547 &annotations[off + 5].0,
1548 &annotations[off + 5].1,
1549 &annotations[off + 6].0,
1550 &annotations[off + 6].1,
1551 &annotations[off + 7].0,
1552 &annotations[off + 7].1,
1553 &annotations[off + 8].0,
1554 &annotations[off + 8].1,
1555 &annotations[off + 9].0,
1556 &annotations[off + 9].1])
1557 .unwrap();
1558 }
1559
1560 let off = n_annotations - n_single;
1561 for single in 0..n_single {
1562 rows_modified += self.execute(query_single,
1563 &[&contest_id, &annotations[off + single].0, &annotations[off + single].1])
1564 .unwrap();
1565 }
1566
1567 rows_modified as i32
1568 }
1569
1570 fn get_submission_by_id_complete_shallow_contest(&self, submission_id: i32)
1571 -> Option<(Submission, Task, Taskgroup, Contest)> {
1572 let query = "SELECT submission.session, submission.grade, submission.validated, submission.nonvalidated_grade,
1573 submission.needs_validation, submission.subtask_identifier, submission.value,
1574 submission.date,
1575 task.id, task.location, task.language, task.stars,
1576 taskgroup.id, taskgroup.name, taskgroup.active, taskgroup.positionalnumber,
1577 contest.id, contest.location, contest.filename, contest.name, contest.duration,
1578 contest.public, contest.protected
1579 FROM submission
1580 JOIN task ON task.id = submission.task
1581 JOIN taskgroup ON taskgroup.id = task.taskgroup
1582 JOIN contest ON contest.id = taskgroup.contest
1583 WHERE submission.id = ?1";
1584 self.query_map_one(query, &[&submission_id], |row| {
1585 (Submission { id: Some(submission_id),
1586 user: row.get(0),
1587 task: row.get(8),
1588 grade: row.get(1),
1589 validated: row.get(2),
1590 nonvalidated_grade: row.get(3),
1591 needs_validation: row.get(4),
1592 subtask_identifier: row.get(5),
1593 value: row.get(6),
1594 date: row.get(7),
1595 autosave: false,
1596 latest: false,
1597 highest_grade_latest: false },
1598 Task { id: Some(row.get(8)),
1599 taskgroup: row.get(11),
1600 location: row.get(9),
1601 language: row.get(10),
1602 stars: row.get(11) },
1603 Taskgroup { id: row.get(12),
1604 contest: row.get(16),
1605 name: row.get(13),
1606 active: row.get(14),
1607 positionalnumber: row.get(15),
1608 tasks: Vec::new() },
1609 Contest { id: row.get(16),
1610 location: row.get(17),
1611 filename: row.get(18),
1612 name: row.get(19),
1613 duration: row.get(20),
1614 public: row.get(21),
1615 start: None,
1616 end: None,
1617 review_start: None,
1618 review_end: None,
1619 min_grade: None,
1620 max_grade: None,
1621 max_teamsize: None,
1622 positionalnumber: None,
1623 requires_login: None,
1624 requires_contest: None,
1625 protected: row.get(22),
1626 secret: None,
1627 message: None,
1628 image: None,
1629 language: None,
1630 category: None,
1631 standalone_task: None,
1632 tags: Vec::new(),
1633 taskgroups: Vec::new() })
1634 })
1635 .unwrap()
1636 }
1637
1638 fn get_contest_list(&self) -> Vec<Contest> {
1639 let query = "SELECT id, location, filename, name, duration, public, start_date, end_date, review_start_date,
1640 review_end_date, min_grade, max_grade, positionalnumber, protected, requires_login,
1641 requires_contest, secret, message, image, language, category, standalone_task,
1642 max_teamsize, contest_tags.tags
1643 FROM contest
1644 LEFT JOIN contest_tags USING (id)
1645 ORDER BY positionalnumber DESC";
1646 self.query_map_many(query, &[], |row| Contest { id: Some(row.get(0)),
1647 location: row.get(1),
1648 filename: row.get(2),
1649 name: row.get(3),
1650 duration: row.get(4),
1651 public: row.get(5),
1652 start: row.get(6),
1653 end: row.get(7),
1654 review_start: row.get(8),
1655 review_end: row.get(9),
1656 min_grade: row.get(10),
1657 max_grade: row.get(11),
1658 positionalnumber: row.get(12),
1659 protected: row.get(13),
1660 requires_login: row.get(14),
1661 requires_contest: row.get(15),
1662 secret: row.get(16),
1663 message: row.get(17),
1664 image: row.get(18),
1665 language: row.get(19),
1666 category: row.get(20),
1667 standalone_task: row.get(21),
1668 max_teamsize: row.get(22),
1669 tags: row.get::<_, Option<String>>(23)
1670 .map(|tags| {
1671 tags.split(',').map(|tag| tag.to_owned()).collect()
1672 })
1673 .unwrap_or_else(Vec::new),
1674 taskgroups: Vec::new() })
1675 .unwrap()
1676 }
1677
1678 fn get_contest_list_with_group_member_participations(&self, session_id: i32) -> Vec<Contest> {
1679 let query = "SELECT DISTINCT contest.id, contest.location, contest.filename, contest.name, contest.duration,
1680 contest.public, contest.start_date, contest.end_date, contest.review_start_date,
1681 contest.review_end_date, contest.min_grade, contest.max_grade, contest.positionalnumber,
1682 contest.protected, contest.requires_login, contest.requires_contest, contest.secret,
1683 contest.message, contest.image, contest.language, contest.category, contest.standalone_task,
1684 contest.max_teamsize, contest_tags.tags
1685 FROM contest
1686 JOIN participation ON participation.contest = contest.id
1687 JOIN session ON session.id = participation.session
1688 JOIN usergroup ON usergroup.id = session.managed_by
1689 JOIN usergroup_admin ON usergroup_admin.usergroup = usergroup.id
1690 LEFT JOIN contest_tags ON contest.id = contest_tags.id
1691 WHERE usergroup_admin.session = ?1
1692 ORDER BY positionalnumber DESC";
1693 self.query_map_many(query, &[&session_id], |row| Contest { id: Some(row.get(0)),
1694 location: row.get(1),
1695 filename: row.get(2),
1696 name: row.get(3),
1697 duration: row.get(4),
1698 public: row.get(5),
1699 start: row.get(6),
1700 end: row.get(7),
1701 review_start: row.get(8),
1702 review_end: row.get(9),
1703 min_grade: row.get(10),
1704 max_grade: row.get(11),
1705 positionalnumber: row.get(12),
1706 protected: row.get(13),
1707 requires_login: row.get(14),
1708 requires_contest: row.get(15),
1709 secret: row.get(16),
1710 message: row.get(17),
1711 image: row.get(18),
1712 language: row.get(19),
1713 category: row.get(20),
1714 standalone_task: row.get(21),
1715 max_teamsize: row.get(22),
1716 tags: row.get::<_, Option<String>>(23)
1717 .map(|tags| {
1718 tags.split(',')
1719 .map(|tag| tag.to_owned())
1720 .collect()
1721 })
1722 .unwrap_or_else(Vec::new),
1723 taskgroups: Vec::new() })
1724 .unwrap()
1725 }
1726
1727 fn get_contest_by_id(&self, contest_id: i32) -> Option<Contest> {
1728 let query = "SELECT location, filename, name, duration, public, start_date, end_date, review_start_date,
1729 review_end_date, min_grade, max_grade, protected, requires_login, requires_contest, secret,
1730 message, image, language, category, standalone_task, max_teamsize
1731 FROM contest
1732 WHERE id = ?1";
1733 self.query_map_one(query, &[&contest_id], |row| Contest { id: Some(contest_id),
1734 location: row.get(0),
1735 filename: row.get(1),
1736 name: row.get(2),
1737 duration: row.get(3),
1738 public: row.get(4),
1739 start: row.get(5),
1740 end: row.get(6),
1741 review_start: row.get(7),
1742 review_end: row.get(8),
1743 min_grade: row.get(9),
1744 max_grade: row.get(10),
1745 positionalnumber: None,
1746 protected: row.get(11),
1747 requires_login: row.get(12),
1748 requires_contest: row.get(13),
1749 secret: row.get(14),
1750 message: row.get(15),
1751 image: row.get(16),
1752 language: row.get(17),
1753 category: row.get(18),
1754 standalone_task: row.get(19),
1755 max_teamsize: row.get(20),
1756 tags: Vec::new(),
1757 taskgroups: Vec::new() })
1758 .unwrap()
1759 }
1760
1761 fn get_contest_by_id_complete(&self, contest_id: i32) -> Option<Contest> {
1762 let query = "SELECT contest.location, contest.filename, contest.name, contest.duration, contest.public,
1763 contest.start_date, contest.end_date, contest.review_start_date, contest.review_end_date,
1764 contest.min_grade, contest.max_grade, contest.protected, contest.requires_login,
1765 contest.requires_contest, contest.secret, contest.message, contest.image, contest.language,
1766 contest.category, contest.standalone_task, contest.max_teamsize,
1767 taskgroup.id, taskgroup.name,
1768 task.id, task.location, task.language, task.stars
1769 FROM contest
1770 JOIN taskgroup ON contest.id = taskgroup.contest
1771 JOIN task ON taskgroup.id = task.taskgroup
1772 WHERE contest.id = ?1
1773 AND taskgroup.active = ?2
1774 ORDER BY taskgroup.positionalnumber";
1775 let taskgroupcontest = self.query_map_many(query, &[&contest_id, &true], |row| {
1776 (Contest { id: Some(contest_id),
1777 location: row.get(0),
1778 filename: row.get(1),
1779 name: row.get(2),
1780 duration: row.get(3),
1781 public: row.get(4),
1782 start: row.get(5),
1783 end: row.get(6),
1784 review_start: row.get(7),
1785 review_end: row.get(8),
1786 min_grade: row.get(9),
1787 max_grade: row.get(10),
1788 positionalnumber: None,
1789 protected: row.get(11),
1790 requires_login: row.get(12),
1791 requires_contest: row.get(13),
1792 secret: row.get(14),
1793 message: row.get(15),
1794 image: row.get(16),
1795 language: row.get(17),
1796 category: row.get(18),
1797 standalone_task: row.get(19),
1798 max_teamsize: row.get(20),
1799 tags: Vec::new(),
1800 taskgroups: Vec::new() },
1801 Taskgroup { id: Some(row.get(21)),
1802 contest: contest_id,
1803 name: row.get(22),
1804 active: true,
1805 positionalnumber: None,
1806 tasks: Vec::new() },
1807 Task { id: Some(row.get(23)),
1808 taskgroup: row.get(21),
1809 location: row.get(24),
1810 language: row.get(25),
1811 stars: row.get(26) })
1812 })
1813 .unwrap();
1814 let mut taskgroupcontest_iter = taskgroupcontest.into_iter();
1815
1816 if let Some((mut contest, mut taskgroup, task)) = taskgroupcontest_iter.next() {
1817 taskgroup.tasks.push(task);
1818 for tgc in taskgroupcontest_iter {
1819 let (_, tg, t) = tgc;
1820 if tg.id != taskgroup.id {
1821 contest.taskgroups.push(taskgroup);
1822 taskgroup = tg;
1823 }
1824 taskgroup.tasks.push(t);
1825 }
1826 contest.taskgroups.push(taskgroup);
1827 Some(contest)
1828 } else {
1829 self.get_contest_by_id(contest_id)
1832 }
1833 }
1834
1835 fn get_contest_by_id_partial(&self, contest_id: i32) -> Option<Contest> {
1836 let query = "SELECT contest.location, contest.filename, contest.name, contest.duration, contest.public,
1837 contest.start_date, contest.end_date, contest.review_start_date, contest.review_end_date,
1838 contest.min_grade, contest.max_grade, contest.protected, contest.requires_login,
1839 contest.requires_contest, contest.secret, contest.message, contest.image, contest.language,
1840 contest.category, contest.standalone_task, contest.max_teamsize
1841 taskgroup.id, taskgroup.name
1842 FROM contest
1843 JOIN taskgroup ON contest.id = taskgroup.contest
1844 WHERE contest.id = ?1
1845 AND taskgroup.active = ?2";
1846 let taskgroupcontest = self.query_map_many(query, &[&contest_id, &true], |row| {
1847 (Contest { id: Some(contest_id),
1848 location: row.get(0),
1849 filename: row.get(1),
1850 name: row.get(2),
1851 duration: row.get(3),
1852 public: row.get(4),
1853 start: row.get(5),
1854 end: row.get(6),
1855 review_start: row.get(7),
1856 review_end: row.get(8),
1857 min_grade: row.get(9),
1858 max_grade: row.get(10),
1859 positionalnumber: None,
1860 protected: row.get(11),
1861 requires_login: row.get(12),
1862 requires_contest: row.get(13),
1863 secret: row.get(14),
1864 message: row.get(15),
1865 image: row.get(16),
1866 language: row.get(17),
1867 category: row.get(18),
1868 standalone_task: row.get(19),
1869 max_teamsize: row.get(20),
1870 tags: Vec::new(),
1871 taskgroups: Vec::new() },
1872 Taskgroup { id: Some(row.get(21)),
1873 contest: contest_id,
1874 name: row.get(22),
1875 active: true,
1876 positionalnumber: None,
1877 tasks: Vec::new() })
1878 })
1879 .unwrap();
1880 let mut taskgroupcontest_iter = taskgroupcontest.into_iter();
1881
1882 if let Some((mut contest, taskgroup)) = taskgroupcontest_iter.next() {
1883 contest.taskgroups.push(taskgroup);
1884 for tgc in taskgroupcontest_iter {
1885 let (_, tg) = tgc;
1886 contest.taskgroups.push(tg);
1887 }
1888 Some(contest)
1889 } else {
1890 self.get_contest_by_id(contest_id)
1893 }
1894 }
1895
1896 fn get_participation(&self, session_id: i32, contest_id: i32) -> Option<Participation> {
1897 let query = "SELECT start_date, team
1898 FROM participation
1899 WHERE session = ?1
1900 AND contest = ?2";
1901 self.query_map_one(query, &[&session_id, &contest_id], |row| Participation { contest: contest_id,
1902 user: session_id,
1903 start: row.get(0),
1904 team: row.get(1),
1905 annotation: None })
1906 .ok()?
1907 }
1908
1909 fn get_own_participation(&self, session_id: i32, contest_id: i32) -> Option<Participation> {
1910 let query = "SELECT start_date, team
1911 FROM participation
1912 WHERE session = ?1
1913 AND contest = ?2";
1914 self.query_map_one(query, &[&session_id, &contest_id], |row| Participation { contest: contest_id,
1915 user: session_id,
1916 start: row.get(0),
1917 team: row.get(1),
1918 annotation: None })
1919 .ok()?
1920 }
1921
1922 fn get_all_participations_complete(&self, session_id: i32) -> Vec<(Participation, Contest)> {
1923 let query = "SELECT contest.id, location, filename, name, duration, public, contest.start_date, end_date,
1924 review_start_date, review_end_date, min_grade, max_grade, protected, requires_login,
1925 requires_contest, secret, message, category,
1926 participation.start_date, participation.team, participation.annotation
1927 FROM participation
1928 JOIN contest ON participation.contest = contest.id
1929 WHERE participation.session = ?1 AND (standalone_task IS NULL OR standalone_task = FALSE)";
1930 self.query_map_many(query, &[&session_id], |row| {
1931 (Participation { contest: row.get(0),
1932 user: session_id,
1933 start: row.get(18),
1934 team: row.get(19),
1935 annotation: row.get(20) },
1936 Contest { id: Some(row.get(0)),
1937 location: row.get(1),
1938 filename: row.get(2),
1939 name: row.get(3),
1940 duration: row.get(4),
1941 public: row.get(5),
1942 start: row.get(6),
1943 end: row.get(7),
1944 review_start: row.get(8),
1945 review_end: row.get(9),
1946 min_grade: row.get(10),
1947 max_grade: row.get(11),
1948 positionalnumber: None,
1949 protected: row.get(12),
1950 requires_login: row.get(13),
1951 requires_contest: row.get(14),
1952 secret: row.get(15),
1953 message: row.get(16),
1954 image: None,
1955 language: None,
1956 category: row.get(17),
1957 max_teamsize: None,
1958 standalone_task: None,
1959 tags: Vec::new(),
1960 taskgroups: Vec::new() })
1961 })
1962 .unwrap()
1963 }
1964
1965 fn count_all_stars(&self, session_id: i32) -> i32 {
1966 let query = "SELECT COALESCE(SUM(grade.grade), 0) AS stars
1967 FROM participation
1968 JOIN taskgroup ON participation.contest = taskgroup.contest
1969 JOIN session ON session.id = participation.team
1970 OR (participation.team IS NULL AND session.id = participation.session)
1971 JOIN grade ON grade.taskgroup = taskgroup.id AND grade.session = session.id
1972 WHERE participation.session = ?1";
1973 self.query_map_one(query, &[&session_id], |row| -> i64 { row.get(0) }).unwrap().unwrap() as i32
1974 }
1975
1976 fn count_all_stars_by_contest(&self, session_id: i32) -> Vec<(i32, i32)> {
1977 let query = "SELECT participation.contest, COALESCE(SUM(grade.grade), 0) AS stars
1978 FROM participation
1979 JOIN taskgroup ON participation.contest = taskgroup.contest
1980 JOIN session ON session.id = participation.team
1981 OR (participation.team IS NULL AND session.id = participation.session)
1982 JOIN grade ON grade.taskgroup = taskgroup.id AND grade.session = session.id
1983 WHERE participation.session = ?1
1984 GROUP BY participation.contest";
1985 self.query_map_many(query, &[&session_id], |row| -> (i32, i32) {
1986 (row.get::<_, i32>(0) as i32, row.get::<_, i64>(1) as i32)
1987 })
1988 .unwrap()
1989 }
1990
1991 fn has_participation_by_contest_file(&self, session_id: i32, location: &str, filename: &str) -> bool {
1992 let query = "SELECT participation.contest
1993 FROM participation
1994 JOIN contest ON participation.contest = contest.id
1995 WHERE participation.session = ?1
1996 AND contest.location = ?2
1997 AND contest.filename = ?3";
1998 self.exists(query, &[&session_id, &location, &filename])
1999 }
2000
2001 fn new_participation(&self, session_id: i32, contest_id: i32, team: Option<i32>) -> Result<Participation, ()> {
2002 let query = "SELECT start_date
2003 FROM participation
2004 WHERE session = ?1
2005 AND contest = ?2";
2006 match self.query_map_one(query, &[&session_id, &contest_id], |_| {}).map_err(|_| ())? {
2007 Some(()) => Err(()),
2008 None => {
2009 let insert = "INSERT INTO participation (contest, session, start_date, team)
2010 VALUES (?1, ?2, ?3, ?4)";
2011
2012 let now = time::get_time();
2013 self.execute(insert, &[&contest_id, &session_id, &now, &team]).unwrap();
2014
2015 Ok(self.get_own_participation(session_id, contest_id).unwrap()) }
2017 }
2018 }
2019 fn get_task_by_id(&self, task_id: i32) -> Option<Task> {
2020 let query = "SELECT location, language, stars, taskgroup
2021 FROM task
2022 WHERE id = ?1";
2023 self.query_map_one(query, &[&task_id], |row| Task { id: Some(task_id),
2024 taskgroup: row.get(3),
2025 location: row.get(0),
2026 language: row.get(1),
2027 stars: row.get(2) })
2028 .unwrap()
2029 }
2030 fn get_task_by_id_complete(&self, task_id: i32) -> Option<(Task, Taskgroup, Contest)> {
2031 let query = "SELECT task.location, task.language, task.stars,
2032 taskgroup.id, taskgroup.name, taskgroup.active,
2033 contest.id, contest.location, contest.filename, contest.name, contest.duration,
2034 contest.public, contest.start_date, contest.end_date, contest.review_start_date,
2035 contest.review_end_date, contest.min_grade, contest.max_grade, contest.protected,
2036 contest.requires_login, contest.requires_contest, contest.secret, contest.message,
2037 contest.category, contest.standalone_task
2038 FROM contest
2039 JOIN taskgroup ON taskgroup.contest = contest.id
2040 JOIN task ON task.taskgroup = taskgroup.id
2041 WHERE task.id = ?1";
2042 self.query_map_one(query, &[&task_id], |row| {
2043 (Task { id: Some(task_id),
2044 taskgroup: row.get(3),
2045 location: row.get(0),
2046 language: row.get(1),
2047 stars: row.get(2) },
2048 Taskgroup { id: Some(row.get(3)),
2049 contest: row.get(6),
2050 name: row.get(4),
2051 active: row.get(5),
2052 positionalnumber: None,
2053 tasks: Vec::new() },
2054 Contest { id: Some(row.get(6)),
2055 location: row.get(7),
2056 filename: row.get(8),
2057 name: row.get(9),
2058 duration: row.get(10),
2059 public: row.get(11),
2060 start: row.get(12),
2061 end: row.get(13),
2062 review_start: row.get(14),
2063 review_end: row.get(15),
2064 min_grade: row.get(16),
2065 max_grade: row.get(17),
2066 positionalnumber: None,
2067 protected: row.get(18),
2068 requires_login: row.get(19),
2069 requires_contest: row.get(20),
2070 secret: row.get(21),
2071 message: row.get(22),
2072 image: None,
2073 language: None,
2074 category: row.get(23),
2075 standalone_task: row.get(24),
2076 tags: Vec::new(),
2077 max_teamsize: None,
2078 taskgroups: Vec::new() })
2079 })
2080 .unwrap()
2081 }
2082
2083 fn get_submission_to_validate(&self, tasklocation: &str, subtask: Option<&str>) -> i32 {
2084 match subtask {
2085 Some(st) => {
2086 let query = "SELECT id
2087 FROM submission
2088 JOIN task ON submission.task = task.id
2089 WHERE task.location = ?1
2090 AND subtask_identifier = ?2
2091 AND needs_validation = 1
2092 LIMIT 1";
2093 self.query_map_one(query, &[&tasklocation, &st], |row| row.get(0)).unwrap().unwrap()
2094 }
2095 None => {
2096 let query = "SELECT id
2097 FROM submission
2098 JOIN task ON submission.task = task.id
2099 WHERE task.location = ?1
2100 AND needs_validation = 1
2101 LIMIT 1";
2102 self.query_map_one(query, &[&tasklocation], |row| row.get(0)).unwrap().unwrap()
2103 }
2104 }
2105 }
2106
2107 fn find_next_submission_to_validate(&self, userid: i32, taskgroupid: i32) {
2108 let query = "SELECT id, validated
2109 FROM submission
2110 JOIN task ON submission.task = task.id
2111 WHERE task.taskgroup = ?1
2112 AND submission.session = ?2
2113 ORDER BY value DESC id DESC
2114 LIMIT 1";
2115 let (id, validated): (i32, bool) =
2116 self.query_map_one(query, &[&taskgroupid, &userid], |row| (row.get(0), row.get(1))).unwrap().unwrap();
2117 if !validated {
2118 let query = "UPDATE submission
2119 SET needs_validation = 1
2120 WHERE id = ?1";
2121 self.execute(query, &[&id]).unwrap();
2122 }
2123 }
2124
2125 fn add_group(&self, group: &mut Group) { group.save(self); }
2126
2127 fn save_group(&self, group: &mut Group) { group.save(self); }
2128
2129 fn get_groups(&self, session_id: i32) -> Vec<Group> {
2130 let query = "SELECT id, name, groupcode, tag
2131 FROM usergroup
2132 JOIN usergroup_admin ON usergroup.id = usergroup_admin.usergroup
2133 WHERE usergroup_admin.session = ?1";
2134 self.query_map_many(query, &[&session_id], |row| Group { id: Some(row.get(0)),
2135 name: row.get(1),
2136 groupcode: row.get(2),
2137 tag: row.get(3),
2138 admins: Vec::new(),
2139 members: Vec::new() })
2140 .unwrap()
2141 }
2142 fn get_groups_complete(&self, _session_id: i32) -> Vec<Group> {
2143 unimplemented!();
2144 }
2145 fn get_group(&self, group_id: i32) -> Option<Group> {
2146 let query = "SELECT name, groupcode, tag
2147 FROM usergroup
2148 WHERE id = ?1";
2149 let mut group = self.query_map_one(query, &[&group_id], |row| Group { id: Some(group_id),
2150 name: row.get(0),
2151 groupcode: row.get(1),
2152 tag: row.get(2),
2153 admins: Vec::new(),
2154 members: Vec::new() })
2155 .unwrap()?;
2156
2157 let query = "SELECT session FROM usergroup_admin WHERE usergroup = ?1 ORDER BY session";
2158 group.admins = self.query_map_many(query, &[&group_id], |row| row.get(0)).unwrap();
2159
2160 Some(group)
2161 }
2162 fn group_has_protected_participations(&self, group_id: i32) -> bool {
2163 let query = "SELECT EXISTS(
2164 SELECT session.id
2165 FROM session
2166 JOIN participation ON participation.session = session.id
2167 JOIN contest ON contest.id = participation.contest
2168 WHERE managed_by = ?1
2169 AND contest.protected = ?2
2170 )";
2171 self.query_map_one(query, &[&group_id, &true], |row| row.get(0)).unwrap().unwrap()
2172 }
2173 fn get_group_complete(&self, group_id: i32) -> Option<Group> {
2174 let query = "SELECT name, groupcode, tag
2175 FROM usergroup
2176 WHERE id = ?1";
2177 let mut group = self.query_map_one(query, &[&group_id], |row| Group { id: Some(group_id),
2178 name: row.get(0),
2179 groupcode: row.get(1),
2180 tag: row.get(2),
2181 admins: Vec::new(),
2182 members: Vec::new() })
2183 .unwrap()?;
2184
2185 let query = "SELECT session FROM usergroup_admin WHERE usergroup = ?1 ORDER BY session";
2186 group.admins = self.query_map_many(query, &[&group_id], |row| row.get(0)).unwrap();
2187
2188 let query = "SELECT id, session_token, csrf_token, last_login, last_activity, account_created, username,
2189 password, salt, logincode, email, email_unconfirmed, email_confirmationcode, firstname,
2190 lastname, street, zip, city, nation, grade, sex, anonymous, is_admin, is_teacher,
2191 school_name, oauth_provider, oauth_foreign_id
2192 FROM session
2193 WHERE managed_by = ?1
2194 ORDER BY id";
2195 group.members = self.query_map_many(query, &[&group_id], |row| SessionUser { id: row.get(0),
2196 session_token: row.get(1),
2197 csrf_token: row.get(2),
2198 last_login: row.get(3),
2199 last_activity: row.get(4),
2200 account_created: row.get(5),
2201
2202 username: row.get(6),
2203 password: row.get(7),
2204 salt: row.get(8),
2205 logincode: row.get(9),
2206 email: row.get(10),
2207 email_unconfirmed: row.get(11),
2208 email_confirmationcode:
2209 row.get(12),
2210
2211 firstname: row.get(13),
2212 lastname: row.get(14),
2213 street: row.get(15),
2214 zip: row.get(16),
2215 city: row.get(17),
2216 nation: row.get(18),
2217 grade: row.get(19),
2218 sex: row.get(20),
2219 anonymous: row.get(21),
2220
2221 is_admin: row.get(22),
2222 is_teacher: row.get(23),
2223 managed_by: Some(group_id),
2224 school_name: row.get(24),
2225
2226 oauth_provider: row.get(25),
2227 oauth_foreign_id: row.get(26) })
2228 .unwrap();
2229 Some(group)
2230 }
2231
2232 fn delete_user(&self, user_id: i32) {
2233 let query = "DELETE FROM session
2234 WHERE id = ?1";
2235 self.execute(query, &[&user_id]).unwrap();
2236 }
2237 fn delete_all_users_for_group(&self, group_id: i32) {
2238 let query = "DELETE FROM session
2239 WHERE managed_by = ?1
2240 AND oauth_foreign_id IS NULL";
2241 self.execute(query, &[&group_id]).unwrap();
2242
2243 let query = "UPDATE session
2244 SET managed_by = NULL
2245 WHERE managed_by = ?1
2246 AND oauth_foreign_id IS NOT NULL";
2247 self.execute(query, &[&group_id]).unwrap();
2248 }
2249 fn delete_group(&self, group_id: i32) {
2250 let query = "DELETE FROM usergroup
2251 WHERE id = ?1";
2252 self.execute(query, &[&group_id]).unwrap();
2253 }
2254 fn delete_participation(&self, user_id: i32, contest_id: i32) {
2255 let query = "DELETE FROM submission
2256 WHERE id IN (
2257 SELECT submission.id FROM submission
2258 JOIN task ON submission.task = task.id
2259 JOIN taskgroup ON task.taskgroup = taskgroup.id
2260 WHERE taskgroup.contest = ?1
2261 AND submission.session = ?2
2262 )";
2263 self.execute(query, &[&contest_id, &user_id]).unwrap();
2264
2265 let query = "DELETE FROM grade
2266 WHERE taskgroup IN (
2267 SELECT id FROM taskgroup
2268 WHERE taskgroup.contest = ?1
2269 )
2270 AND session = ?2";
2271 self.execute(query, &[&contest_id, &user_id]).unwrap();
2272
2273 let query = "DELETE FROM participation
2274 WHERE contest = ?1
2275 AND session = ?2";
2276 self.execute(query, &[&contest_id, &user_id]).unwrap();
2277 }
2278
2279 fn get_search_users(
2280 &self,
2281 (s_id, s_firstname, s_lastname, s_logincode, s_groupcode, s_pms_id): (Option<i32>,
2282 Option<String>,
2283 Option<String>,
2284 Option<String>,
2285 Option<String>,
2286 Option<String>))
2287 -> Result<Vec<(i32, Option<String>, Option<String>, Option<String>, Option<String>, Option<String>)>,
2288 Vec<(i32, String, String, String)>> {
2289 if let Some(id) = s_id {
2290 let query = "SELECT id, firstname, lastname, logincode, oauth_foreign_id, oauth_provider
2291 FROM session
2292 WHERE id = ?1
2293 LIMIT 201";
2294 Ok(self.query_map_many(query, &[&id], |row| {
2295 (row.get(0), row.get(1), row.get(2), row.get(3), row.get(4), row.get(5))
2296 })
2297 .unwrap())
2298 } else if let Some(logincode) = s_logincode {
2299 let query = "SELECT id, firstname, lastname, logincode, oauth_foreign_id, oauth_provider
2300 FROM session
2301 WHERE logincode = ?1
2302 LIMIT 201";
2303 Ok(self.query_map_many(query, &[&logincode], |row| {
2304 (row.get(0), row.get(1), row.get(2), row.get(3), row.get(4), row.get(5))
2305 })
2306 .unwrap())
2307 } else if let Some(groupcode) = s_groupcode {
2308 let query = "SELECT id, name, tag
2309 FROM usergroup
2310 WHERE groupcode = ?1
2311 LIMIT 201";
2312 Err(self.query_map_many(query, &[&groupcode], |row| {
2313 (row.get(0), row.get(1), row.get(2), groupcode.clone())
2314 })
2315 .unwrap())
2316 } else if let Some(pms_id) = s_pms_id {
2317 let query = "SELECT id, firstname, lastname, logincode, oauth_foreign_id, oauth_provider
2318 FROM session
2319 WHERE oauth_foreign_id = ?1
2320 OR oauth_foreign_id LIKE ?2
2321 LIMIT 201";
2322 Ok(self.query_map_many(query, &[&pms_id, &format!("{}/%", pms_id)], |row| {
2323 (row.get(0), row.get(1), row.get(2), row.get(3), row.get(4), row.get(5))
2324 })
2325 .unwrap())
2326 } else if let (Some(firstname), Some(lastname)) = (s_firstname, s_lastname) {
2327 let query = "SELECT id, firstname, lastname, logincode, oauth_foreign_id, oauth_provider
2328 FROM session
2329 WHERE firstname LIKE ?1
2330 AND lastname LIKE ?2
2331 ORDER BY id DESC
2332 LIMIT 201";
2333 Ok(self.query_map_many(query, &[&format!("%{}%", firstname), &format!("%{}%", lastname)], |row| {
2334 (row.get(0), row.get(1), row.get(2), row.get(3), row.get(4), row.get(5))
2335 })
2336 .unwrap())
2337 } else {
2338 Ok(Vec::new())
2339 }
2340 }
2341
2342 fn remove_old_users_and_groups(&self, maxstudentage: time::Timespec, maxteacherage: Option<time::Timespec>,
2344 maxage: Option<time::Timespec>)
2345 -> Result<(i32, i32, i32, i32), ()> {
2346 let query = "SELECT managed_by
2348 FROM session
2349 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
2350 AND ((last_login < ?1 AND last_activity < ?1)
2351 OR (last_login < ?1 AND last_activity IS NULL)
2352 OR (last_login IS NULL AND last_activity < ?1)
2353 OR (last_login IS NULL AND last_activity IS NULL AND account_created < ?1))";
2354 let mut groups: Vec<i32> = self.query_map_many(query, &[&maxstudentage], |row| row.get(0)).unwrap();
2355
2356 let query = "DELETE
2358 FROM session
2359 WHERE username IS NULL AND password IS NULL AND oauth_foreign_id IS NULL AND oauth_provider IS NULL
2360 AND ((last_login < ?1 AND last_activity < ?1)
2361 OR (last_login < ?1 AND last_activity IS NULL)
2362 OR (last_login IS NULL AND last_activity < ?1)
2363 OR (last_login IS NULL AND last_activity IS NULL AND account_created < ?1))";
2364 self.execute(query, &[&maxstudentage]).unwrap();
2365
2366 let n_users = groups.len() as i32;
2368 let mut n_groups: i32 = 0;
2369 let mut n_teachers: i32 = 0;
2370 let mut n_other: i32 = 0;
2371
2372 groups.sort_unstable();
2374 groups.dedup();
2375
2376 let query = "SELECT count(*)
2378 FROM session
2379 WHERE managed_by = ?1;";
2380 for group in groups {
2381 let groupsize: i64 = self.query_map_one(query, &[&group], |row| row.get(0)).unwrap().unwrap();
2382
2383 if groupsize == 0 {
2384 let query = "DELETE
2385 FROM usergroup
2386 WHERE id = ?1";
2387 self.execute(query, &[&group]).unwrap();
2388
2389 n_groups += 1;
2390 }
2391 }
2392
2393 let query = "SELECT id
2395 FROM usergroup
2396 WHERE group_created < ?1";
2397 let groups: Vec<i32> = self.query_map_many(query, &[&maxstudentage], |row| row.get(0)).unwrap();
2398 let query = "SELECT count(*)
2399 FROM session
2400 WHERE managed_by = ?1;";
2401 for group in groups {
2402 let groupsize: i64 = self.query_map_one(query, &[&group], |row| row.get(0)).unwrap().unwrap();
2403
2404 if groupsize == 0 {
2405 let query = "DELETE
2406 FROM usergroup
2407 WHERE id = ?1";
2408 self.execute(query, &[&group]).unwrap();
2409
2410 n_groups += 1;
2411 }
2412 }
2413
2414 let query = "SELECT id
2416 FROM session
2417 WHERE is_teacher = ?1
2418 AND ((last_login < ?2 AND last_activity < ?2)
2419 OR (last_login < ?2 AND last_activity IS NULL)
2420 OR (last_login IS NULL AND last_activity < ?2)
2421 OR (last_login IS NULL AND last_activity IS NULL AND account_created < ?2))";
2422 if let Some(maxteacherage) = maxteacherage {
2423 let teachers: Vec<i32> = self.query_map_many(query, &[&true, &maxteacherage], |row| row.get(0)).unwrap();
2424
2425 let query = "SELECT count(*)
2427 FROM usergroup_admin
2428 WHERE session = ?1;";
2429 for teacher in teachers {
2430 let groupcount: i64 = self.query_map_one(query, &[&teacher], |row| row.get(0)).unwrap().unwrap();
2431
2432 if groupcount == 0 {
2433 let query = "DELETE
2434 FROM session
2435 WHERE id = ?1";
2436 self.execute(query, &[&teacher]).unwrap();
2437
2438 n_teachers += 1;
2439 }
2440 }
2441 }
2442
2443 if let Some(maxage) = maxage {
2445 let query = "SELECT count(*)
2446 FROM session
2447 WHERE ((last_login < ?1 AND last_activity < ?1)
2448 OR (last_login < ?1 AND last_activity IS NULL)
2449 OR (last_login IS NULL AND last_activity < ?1)
2450 OR (last_login IS NULL AND last_activity IS NULL AND account_created < ?1))";
2451 n_other = self.query_map_one(query, &[&maxage], |row| row.get::<_, i64>(0) as i32).unwrap().unwrap();
2452
2453 let query = "DELETE
2454 FROM session
2455 WHERE ((last_login < ?1 AND last_activity < ?1)
2456 OR (last_login < ?1 AND last_activity IS NULL)
2457 OR (last_login IS NULL AND last_activity < ?1)
2458 OR (last_login IS NULL AND last_activity IS NULL AND account_created < ?1))";
2459 self.execute(query, &[&maxage]).unwrap();
2460 }
2461
2462 Ok((n_users, n_groups, n_teachers, n_other))
2463 }
2464
2465 fn count_temporary_sessions(&self, maxage: time::Timespec) -> i32 {
2466 let query = "SELECT count(*)
2467 FROM session
2468 WHERE (last_activity < ?1 OR last_activity IS NULL)
2469 AND logincode IS NULL
2470 AND password IS NULL
2471 AND oauth_foreign_id IS NULL";
2472 self.query_map_one(query, &[&maxage], |row| row.get::<_, i64>(0) as i32).unwrap().unwrap()
2473 }
2474
2475 fn remove_temporary_sessions(&self, maxage: time::Timespec, limit: Option<u32>) {
2476 if let Some(limit) = limit {
2481 let query = "DELETE
2482 FROM session
2483 WHERE id IN (SELECT id
2484 FROM session
2485 WHERE (last_activity < ?1 OR last_activity IS NULL)
2486 AND logincode IS NULL
2487 AND password IS NULL
2488 AND oauth_foreign_id IS NULL
2489 ORDER BY last_activity
2490 LIMIT ?2)";
2491 self.execute(query, &[&maxage, &(limit as i64)]).unwrap();
2492 } else {
2493 let query = "DELETE
2494 FROM session
2495 WHERE (last_activity < ?1 OR last_activity IS NULL)
2496 AND logincode IS NULL
2497 AND password IS NULL
2498 AND oauth_foreign_id IS NULL";
2499 self.execute(query, &[&maxage]).unwrap();
2500 }
2501 }
2502
2503 fn remove_autosaved_submissions(&self, maxage: time::Timespec, limit: Option<u32>) {
2504 if let Some(limit) = limit {
2505 let query = "DELETE
2506 FROM submission
2507 WHERE id IN (SELECT id
2508 FROM submission
2509 WHERE (date < ?1 OR date IS NULL)
2510 AND autosave = ?2
2511 ORDER BY date
2512 LIMIT ?3)";
2513 self.execute(query, &[&maxage, &true, &(limit as i64)]).unwrap();
2514 } else {
2515 let query = "DELETE
2516 FROM submission
2517 WHERE (date < ?1 OR date IS NULL)
2518 AND autosave = ?2";
2519 self.execute(query, &[&maxage, &true]).unwrap();
2520 }
2521 }
2522
2523 fn remove_all_but_latest_submissions(&self, maxage: time::Timespec, limit: Option<u32>) {
2524 if let Some(limit) = limit {
2525 let query = "DELETE
2526 FROM submission
2527 WHERE id IN (SELECT id
2528 FROM submission
2529 WHERE (date < ?1 OR date IS NULL)
2530 AND latest = ?2
2531 AND highest_grade_latest = ?2
2532 ORDER BY date
2533 LIMIT ?3)";
2534 self.execute(query, &[&maxage, &false, &(limit as i64)]).unwrap();
2535 } else {
2536 let query = "DELETE
2537 FROM submission
2538 WHERE (date < ?1 OR date IS NULL)
2539 AND latest = ?2
2540 AND highest_grade_latest = ?2";
2541 self.execute(query, &[&maxage]).unwrap();
2542 }
2543 }
2544
2545 fn move_task_location(&self, old_location: &str, new_location: &str, contest: Option<i32>) -> i32 {
2546 let query = "SELECT contest.id, oldtaskgroup.id, newtaskgroup.id, oldtask.id, newtask.id
2547 FROM contest
2548 JOIN taskgroup AS oldtaskgroup ON oldtaskgroup.contest = contest.id
2549 JOIN task AS oldtask ON oldtask.taskgroup = oldtaskgroup.id
2550 JOIN taskgroup AS newtaskgroup ON newtaskgroup.contest = contest.id
2551 JOIN task AS newtask ON newtask.taskgroup = newtaskgroup.id
2552 WHERE oldtask.location = ?1
2553 AND newtask.location = ?2;";
2554
2555 let mut tasks: Vec<(i32, (i32, i32), (i32, i32))> =
2556 self.query_map_many(query, &[&old_location, &new_location], |row| {
2557 (row.get(0), (row.get(1), row.get(2)), (row.get(3), row.get(4)))
2558 })
2559 .unwrap();
2560
2561 if let Some(contest) = contest {
2562 tasks.retain(|t| t.0 == contest);
2563 }
2564
2565 let query = "WITH cte AS (
2566 SELECT ?2 AS taskgroup, oldgrade.session, GREATEST(oldgrade.grade, newgrade.grade) AS grade, oldgrade.validated
2567 FROM grade AS oldgrade
2568 LEFT JOIN grade AS newgrade ON oldgrade.session = newgrade.session AND newgrade.taskgroup = ?2
2569 WHERE oldgrade.taskgroup = ?1
2570 )
2571 INSERT INTO grade (taskgroup, session, grade, validated)
2572 SELECT * FROM CTE
2573 ON CONFLICT (taskgroup, session)
2574 DO UPDATE SET grade = excluded.grade, validated = excluded.validated;";
2575
2576 for task in &tasks {
2577 self.execute(query, &[&task.1 .0, &task.1 .1]).unwrap();
2578 }
2579
2580 let query = "DELETE
2581 FROM grade
2582 WHERE taskgroup = ?1;";
2583
2584 for task in &tasks {
2585 self.execute(query, &[&task.1 .0]).unwrap();
2586 }
2587
2588 let limitquery = "SELECT id
2589 FROM submission
2590 WHERE task = ?1
2591 LIMIT 1;";
2592
2593 let query = "WITH cte AS (
2594 SELECT id
2595 FROM submission
2596 WHERE task = ?1
2597 LIMIT 10000
2598 )
2599 UPDATE submission s
2600 SET task = ?2
2601 FROM cte
2602 WHERE s.id = cte.id;";
2603
2604 for task in &tasks {
2605 while self.query_map_one(limitquery, &[&task.2 .0], |_| ()).unwrap().is_some() {
2606 self.execute(query, &[&task.2 .0, &task.2 .1]).unwrap();
2607 }
2608 }
2609
2610 tasks.len() as i32
2611 }
2612
2613 fn get_debug_information(&self) -> String {
2614 let now = time::get_time();
2615 let cache_key = "dbstatus";
2616
2617 let query = "SELECT value, date
2618 FROM string_cache
2619 WHERE key = ?1";
2620
2621 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() {
2623 if cache_date.sec / 60 >= now.sec / 60 {
2625 return cached_value;
2626 }
2627 true
2628 } else {
2629 false
2630 };
2631
2632 let duration = Duration::minutes(60);
2633 let then = now - duration;
2634
2635 let query = "SELECT count(*)
2637 FROM session
2638 WHERE last_activity > ?1;";
2639 let n_asession: i64 = self.query_map_one(query, &[&then], |row| row.get(0)).unwrap().unwrap();
2640
2641 let query = "SELECT count(*)
2643 FROM participation
2644 WHERE start_date > ?1;";
2645 let n_apart: i64 = self.query_map_one(query, &[&then], |row| row.get(0)).unwrap().unwrap();
2646
2647 let query = "SELECT count(*)
2649 FROM session;";
2650 let n_session: i64 = self.query_map_one(query, &[], |row| row.get(0)).unwrap().unwrap();
2651
2652 let query = "SELECT count(*)
2654 FROM session
2655 WHERE oauth_foreign_id IS NOT NULL OR logincode IS NOT NULL;";
2656 let n_user: i64 = self.query_map_one(query, &[], |row| row.get(0)).unwrap().unwrap();
2657
2658 let query = "SELECT count(*)
2660 FROM session
2661 WHERE oauth_foreign_id IS NOT NULL;";
2662 let n_pmsuser: i64 = self.query_map_one(query, &[], |row| row.get(0)).unwrap().unwrap();
2663
2664 let query = "SELECT count(*)
2666 FROM session
2667 WHERE is_teacher = ?1;";
2668 let n_teacher: i64 = self.query_map_one(query, &[&true], |row| row.get(0)).unwrap().unwrap();
2669
2670 let query = "SELECT count(*)
2672 FROM participation;";
2673 let n_part: i64 = self.query_map_one(query, &[], |row| row.get(0)).unwrap().unwrap();
2674
2675 let n_sub: i64 = 0; let query = "SELECT contest, count(*)
2683 FROM participation
2684 GROUP BY contest
2685 ORDER BY contest DESC;";
2686 let n_participations_by_id: Vec<(i32, i64)> =
2687 self.query_map_many(query, &[], |row| (row.get(0), row.get(1))).unwrap();
2688
2689 let result = format!(
2690 "{{
2691 \"timestamp\": {},
2692 \"active_sessions\": {},
2693 \"active_participations\": {},
2694 \"sessions\": {},
2695 \"users\": {},
2696 \"pms_users\": {},
2697 \"teachers\": {},
2698 \"participations\": {},
2699 \"submissions\": {},
2700 \"participations_by_contest_id\": {{
2701 {}
2702 }}
2703}}
2704",
2705 now.sec,
2706 n_asession,
2707 n_apart,
2708 n_session,
2709 n_user,
2710 n_pmsuser,
2711 n_teacher,
2712 n_part,
2713 n_sub,
2714 n_participations_by_id.iter()
2715 .map(|(x, y)| -> String { format!("\"{}\": {}", x, y) })
2716 .collect::<Vec<String>>()
2717 .join(",\n ")
2718 );
2719
2720 let query = if db_has_value {
2721 "UPDATE string_cache
2722 SET value = ?2, date = ?3
2723 WHERE key = ?1"
2724 } else {
2725 "INSERT INTO string_cache (key, value, date)
2726 VALUES (?1, ?2, ?3)"
2727 };
2728 self.execute(query, &[&cache_key, &result, &now]).unwrap();
2729
2730 result
2731 }
2732
2733 fn reset_all_contest_visibilities(&self) { self.execute("UPDATE contest SET public = ?1", &[&false]).unwrap(); }
2734 fn reset_all_taskgroup_visibilities(&self) { self.execute("UPDATE taskgroup SET active = ?1", &[&false]).unwrap(); }
2735}