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