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