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 new_logincode(&self, user_id: i64) -> Option<String> {
1113 let mut logincode = String::new();
1114 for i in 0..10 {
1115 if i == 9 {
1116 eprintln!("ERROR: Too many logincode collisions! Give up ...");
1117 return None;
1118 }
1119 logincode = helpers::make_logincode();
1120 if !self.code_exists(&logincode) {
1121 break;
1122 }
1123 println!("WARNING: Logincode collision! Retrying ...");
1124 }
1125
1126 let query = "UPDATE session
1127 SET logincode = ?2
1128 WHERE id = ?1";
1129 self.execute(query, &[&user_id, &logincode]).unwrap();
1130
1131 Some(logincode)
1132 }
1133
1134 fn new_groupcode(&self, group_id: i64) -> Option<String> {
1135 let mut groupcode = String::new();
1136 for i in 0..10 {
1137 if i == 9 {
1138 eprintln!("ERROR: Too many groupcode collisions! Give up ...");
1139 return None;
1140 }
1141 groupcode = helpers::make_groupcode();
1142 if !self.code_exists(&groupcode) {
1143 break;
1144 }
1145 println!("WARNING: Logincode collision! Retrying ...");
1146 }
1147
1148 let query = "UPDATE usergroup
1149 SET groupcode = ?2
1150 WHERE id = ?1";
1151 self.execute(query, &[&group_id, &groupcode]).unwrap();
1152
1153 Some(groupcode)
1154 }
1155
1156 fn add_admin_to_group(&self, group: &mut Group, admin: i64) {
1157 let query = "INSERT INTO usergroup_admin (usergroup, session)
1158 VALUES (?1, ?2)";
1159 self.execute(query, &[&group.get_id(), &admin]).unwrap();
1160
1161 group.admins.push(admin);
1162 }
1163
1164 fn remove_admin_from_group(&self, group: &mut Group, admin: i64) {
1165 let query = "DELETE FROM usergroup_admin
1166 WHERE usergroup = ?1
1167 AND session = ?2";
1168 self.execute(query, &[&group.get_id(), &admin]).unwrap();
1169
1170 if let Some(index) = group.admins.iter().position(|x| *x == admin) {
1171 group.admins.remove(index);
1172 }
1173 }
1174
1175 fn logout(&self, session: &str) {
1176 let query = "UPDATE session
1177 SET session_token = NULL
1178 WHERE session_token = ?1";
1179 self.execute(query, &[&session]).unwrap();
1180 }
1181
1182 fn signup(&self, session_token: &str, username: &str, email: &str, password_hash: String, salt: &str)
1183 -> SignupResult {
1184 let mut session_user = self.get_session_or_new(&session_token).unwrap();
1185
1186 if session_user.is_logged_in() {
1187 return SignupResult::UserLoggedIn;
1188 }
1189
1190 if let Ok(None) = self.query_map_one("SELECT username FROM session WHERE username = ?1",
1191 &[&username],
1192 |row| -> Option<String> { row.get(0) })
1193 {
1194 } else {
1195 return SignupResult::UsernameTaken;
1197 }
1198 if let Ok(None) = self.query_map_one("SELECT email, email_unconfirmed FROM session WHERE email = ?1 OR email_unconfirmed = ?1",
1199 &[&email],
1200 |row| -> (Option<String>, Option<String>) { (row.get(0), row.get(1)) })
1201 {
1202 } else {
1203 return SignupResult::EmailTaken;
1205 }
1206
1207 session_user.username = Some(username.to_string());
1208 session_user.email_unconfirmed = Some(email.to_string());
1209 session_user.password = Some(password_hash);
1210 session_user.salt = Some(salt.to_string());
1211
1212 self.save_session(session_user);
1213 SignupResult::SignedUp
1214 }
1215
1216 fn load_submission(&self, session: &SessionUser, task_id: i64, subtask: Option<&str>) -> Option<Submission> {
1217 match subtask {
1218 None => {
1219 let query = "SELECT id, grade, validated, nonvalidated_grade, value, date, needs_validation
1220 FROM submission
1221 WHERE task = ?1
1222 AND session = ?2
1223 ORDER BY id DESC
1224 LIMIT 1";
1225 self.query_map_one(query, &[&task_id, &session.id], |row| Submission { id: Some(row.get(0)),
1226 task: task_id,
1227 user: session.id,
1228 grade: row.get(1),
1229 validated: row.get(2),
1230 nonvalidated_grade:
1231 row.get(3),
1232 subtask_identifier: None,
1233 value: row.get(4),
1234 date: row.get(5),
1235 needs_validation: row.get(6),
1236 autosave: false,
1237 higher_grade: false,
1238 latest: false,
1239 highest_grade_latest: false })
1240 .ok()?
1241 }
1242 Some(subtask_id) => {
1243 let query = "SELECT id, grade, validated, nonvalidated_grade, value, date, needs_validation
1244 FROM submission
1245 WHERE task = ?1
1246 AND session = ?2
1247 AND subtask_identifier = ?3
1248 ORDER BY id DESC
1249 LIMIT 1";
1250 self.query_map_one(query, &[&task_id, &session.id, &subtask_id], |row| {
1251 Submission { id: Some(row.get(0)),
1252 task: task_id,
1253 user: session.id,
1254 grade: row.get(1),
1255 validated: row.get(2),
1256 nonvalidated_grade: row.get(3),
1257 subtask_identifier: Some(subtask_id.to_string()),
1258 value: row.get(4),
1259 date: row.get(5),
1260 needs_validation: row.get(6),
1261 autosave: false,
1262 higher_grade: false,
1263 latest: false,
1264 highest_grade_latest: false }
1265 })
1266 .ok()?
1267 }
1268 }
1269 }
1270 fn get_all_submissions(&self, session_id: i64, task_id: i64, subtask: Option<&str>) -> Vec<Submission> {
1271 match subtask {
1272 None => {
1273 let query = "SELECT id, grade, validated, nonvalidated_grade, value, date, needs_validation, autosave,
1274 higher_grade, latest, highest_grade_latest
1275 FROM submission
1276 WHERE task = ?1
1277 AND session = ?2
1278 ORDER BY date";
1279 self.query_map_many(query, &[&task_id, &session_id], |row| Submission { id: Some(row.get(0)),
1280 task: task_id,
1281 user: session_id,
1282 grade: row.get(1),
1283 validated: row.get(2),
1284 nonvalidated_grade:
1285 row.get(3),
1286 subtask_identifier: None,
1287 value: row.get(4),
1288 date: row.get(5),
1289 needs_validation: row.get(6),
1290 autosave: row.get(7),
1291 higher_grade: row.get(8),
1292 latest: row.get(9),
1293 highest_grade_latest:
1294 row.get(10) })
1295 .unwrap()
1296 }
1297 _ => unimplemented!(),
1298 }
1299 }
1300 fn submit_submission(&self, mut submission: Submission) {
1301 let mut grade = self.get_grade_by_user_and_task(submission.user, submission.task);
1302 if grade.grade.is_none() || submission.grade > grade.grade.unwrap() {
1303 grade.grade = Some(submission.grade);
1304 grade.validated = false;
1305 grade.save(self);
1306
1307 submission.higher_grade = true;
1308 submission.autosave = false;
1309 }
1310
1311 if submission.autosave {
1315 submission.latest = false;
1316 submission.highest_grade_latest = false;
1317 } else if grade.grade.is_none() || submission.grade >= grade.grade.unwrap() {
1318 let query = "UPDATE submission
1319 SET latest = ?3,
1320 highest_grade_latest = ?3
1321 WHERE session = ?1
1322 AND task = ?2";
1323 self.execute(query, &[&submission.user, &submission.task, &false]).unwrap();
1324 submission.latest = true;
1325 submission.highest_grade_latest = true;
1326 } else {
1327 let query = "UPDATE submission
1328 SET latest = ?3
1329 WHERE session = ?1
1330 AND task = ?2";
1331 self.execute(query, &[&submission.user, &submission.task, &false]).unwrap();
1332 submission.latest = true;
1333 submission.highest_grade_latest = false;
1334 }
1335 submission.save(self);
1336 }
1337 fn get_grade_by_user_and_task(&self, session_id: i64, task_id: i64) -> Grade {
1338 let query = "SELECT grade.taskgroup, grade.grade, grade.validated
1339 FROM grade
1340 JOIN task ON grade.taskgroup = task.taskgroup
1341 WHERE grade.session = ?1
1342 AND task.id = ?2";
1343 self.query_map_one(query, &[&session_id, &task_id], |row| Grade { taskgroup: row.get(0),
1344 user: session_id,
1345 grade: row.get(1),
1346 validated: row.get(2) })
1347 .unwrap_or(None)
1348 .unwrap_or_else(|| {
1349 let query = "SELECT taskgroup
1350 FROM task
1351 WHERE id = ?1";
1352 self.query_map_one(query, &[&task_id], |row| Grade { taskgroup: row.get(0),
1353 user: session_id,
1354 grade: None,
1355 validated: false })
1356 .unwrap()
1357 .unwrap() })
1359 }
1360
1361 fn get_contest_groups_grades(&self, session_id: i64, contest_id: i64)
1362 -> (Vec<String>, Vec<(Group, Vec<(UserInfo, Vec<Grade>)>)>) {
1363 let query = "SELECT id, name
1364 FROM taskgroup
1365 WHERE contest = ?1
1366 AND active = ?2
1367 ORDER BY positionalnumber";
1368 let tasknames: Vec<(i64, String)> =
1369 self.query_map_many(query, &[&contest_id, &true], |row| (row.get(0), row.get(1))).unwrap();
1370
1371 let mut taskindex: ::std::collections::BTreeMap<i64, usize> = ::std::collections::BTreeMap::new();
1372
1373 let n_tasks = tasknames.len();
1374 for (index, (i, _)) in tasknames.iter().enumerate() {
1375 taskindex.insert(*i, index);
1376 }
1377
1378 let query = "SELECT grade.taskgroup, grade.session, grade.grade, grade.validated, usergroup.id, usergroup.name,
1379 usergroup.groupcode, usergroup.tag, student.id, student.username, student.logincode,
1380 student.firstname, student.lastname, student.grade AS sgrade, participation.annotation,
1381 participation.team
1382 FROM grade
1383 JOIN taskgroup ON grade.taskgroup = taskgroup.id
1384 JOIN participation ON (participation.session = grade.session OR participation.team = grade.session) AND participation.contest = ?2
1385 JOIN session AS student ON participation.session = student.id
1386 JOIN usergroup ON student.managed_by = usergroup.id
1387 JOIN usergroup_admin ON usergroup.id = usergroup_admin.usergroup
1388 WHERE usergroup_admin.session = ?1
1389 AND taskgroup.contest = ?2
1390 AND taskgroup.active = ?3
1391 ORDER BY usergroup.id, sgrade, student.lastname, student.firstname, student.id,
1392 taskgroup.positionalnumber";
1393 let gradeinfo =
1394 self.query_map_many(query, &[&session_id, &contest_id, &true], |row| {
1395 (Grade { taskgroup: row.get(0), user: row.get(1), grade: row.get(2), validated: row.get(3) },
1396 Group { id: Some(row.get(4)),
1397 name: row.get(5),
1398 groupcode: row.get(6),
1399 tag: row.get(7),
1400 admins: Vec::new(),
1401 members: Vec::new() },
1402 UserInfo { id: row.get(8),
1403 username: row.get(9),
1404 logincode: row.get(10),
1405 firstname: row.get(11),
1406 lastname: row.get(12),
1407 grade: row.get(13),
1408 annotation: row.get(14),
1409 team: row.get(15) })
1410 })
1411 .unwrap();
1412 let mut gradeinfo_iter = gradeinfo.iter();
1413
1414 if let Some(t ) = gradeinfo_iter.next() {
1415 let (grade, mut group, mut userinfo) = t.clone();
1416
1417 let mut grades: Vec<Grade> = vec![Default::default(); n_tasks];
1418 let mut users: Vec<(UserInfo, Vec<Grade>)> = Vec::new();
1419 let mut groups: Vec<(Group, Vec<(UserInfo, Vec<Grade>)>)> = Vec::new();
1420
1421 let index = grade.taskgroup;
1422 grades[taskindex[&index]] = grade;
1423
1424 for ggu in gradeinfo_iter {
1425 let (g, gr, ui) = ggu;
1426 if gr.id != group.id {
1427 users.push((userinfo, grades));
1428 userinfo = ui.clone();
1429 grades = vec![Default::default(); n_tasks];
1430
1431 groups.push((group, users));
1432 group = gr.clone();
1433 users = Vec::new();
1434 } else if ui.id != userinfo.id {
1435 users.push((userinfo, grades));
1436 userinfo = ui.clone();
1437 grades = vec![Default::default(); n_tasks];
1438 }
1439 let index = g.taskgroup;
1440 grades[taskindex[&index]] = *g;
1441 }
1442 users.push((userinfo, grades));
1443 groups.push((group, users));
1444
1445 (tasknames.iter().map(|(_, name)| name.clone()).collect(), groups)
1446 } else {
1447 (Vec::new(), Vec::new()) }
1449 }
1450
1451 fn get_contest_user_grades(&self, session_id: i64, contest_id: i64) -> Vec<Grade> {
1452 let query = "SELECT id, name
1453 FROM taskgroup
1454 WHERE contest = ?1
1455 AND active = ?2
1456 ORDER BY positionalnumber";
1457 let tasknames: Vec<(i64, String)> =
1458 self.query_map_many(query, &[&contest_id, &true], |row| (row.get(0), row.get(1))).unwrap();
1459 let mut taskindex: ::std::collections::BTreeMap<i64, usize> = ::std::collections::BTreeMap::new();
1460
1461 let n_tasks = tasknames.len();
1462 for (index, (i, _)) in tasknames.iter().enumerate() {
1463 taskindex.insert(*i, index);
1464 }
1465
1466 let query = "SELECT grade.taskgroup, grade.session, grade.grade, grade.validated
1467 FROM grade
1468 JOIN taskgroup ON grade.taskgroup = taskgroup.id
1469 WHERE grade.session = ?1
1470 AND taskgroup.contest = ?2
1471 AND taskgroup.active = ?3
1472 ORDER BY taskgroup.positionalnumber";
1473 let gradeinfo =
1474 self.query_map_many(query, &[&session_id, &contest_id, &true], |row| Grade { taskgroup: row.get(0),
1475 user: row.get(1),
1476 grade: row.get(2),
1477 validated: row.get(3) })
1478 .unwrap();
1479 let gradeinfo_iter = gradeinfo.iter();
1480
1481 let mut grades: Vec<Grade> = vec![Default::default(); n_tasks];
1482
1483 for g in gradeinfo_iter {
1484 let index = g.taskgroup;
1485 grades[taskindex[&index]] = *g;
1486 }
1487
1488 grades
1489 }
1490
1491 fn get_taskgroup_user_grade(&self, session_id: i64, taskgroup_id: i64) -> Grade {
1492 let query = "SELECT grade.taskgroup, grade.session, grade.grade, grade.validated
1493 FROM grade
1494 WHERE grade.session = ?1
1495 AND grade.taskgroup = ?2";
1496 self.query_map_one(query, &[&session_id, &taskgroup_id], |row| Grade { taskgroup: row.get(0),
1497 user: row.get(1),
1498 grade: row.get(2),
1499 validated: row.get(3) })
1500 .unwrap_or(None)
1501 .unwrap_or_default()
1502 }
1503
1504 fn export_contest_results_to_file(&self, contest_id: i64, taskgroups: &[(i64, String)], filename: &str) {
1506 use std::fs::OpenOptions;
1507 let file = OpenOptions::new().write(true).create(true).truncate(true).open(filename).unwrap();
1508 let mut headers = vec!["id",
1509 "username",
1510 "logincode",
1511 "oauth_foreign_id",
1512 "oauth_provider",
1513 "firstname",
1514 "lastname",
1515 "grade",
1516 "sex",
1517 "anonymous",
1518 "additional_contest_time_percent",
1519 "data_protection_clearance",
1520 "is_teacher",
1521 "group_id",
1522 "group_name",
1523 "group_tag",
1524 "teacher_id",
1525 "teacher_firstname",
1526 "teacher_lastname",
1527 "teacher_oauth_foreign_id",
1528 "teacher_oauth_school_id",
1529 "teacher_oauth_provider",
1530 "contest_id",
1531 "is_team_participation",
1532 "team_lead",
1533 "start_date"];
1534
1535 let mut select_part = String::new();
1536 let mut join_part = String::new();
1537
1538 let mut join_params = gen_tosql_vector();
1539
1540 join_params.push(&contest_id);
1541
1542 for (n, (id, name)) in taskgroups.iter().enumerate() {
1543 use std::fmt::Write;
1544
1545 write!(select_part, ",\n g{}.grade ", n).unwrap();
1546 write!(join_part,
1547 "\n LEFT JOIN grade AS g{} ON gsession.id = g{}.session AND g{}.taskgroup = ?{} ",
1548 n,
1549 n,
1550 n,
1551 n + 2).unwrap();
1552 join_params.push(id);
1553 headers.push(&name);
1554 }
1555
1556 let query = format!("SELECT session.id,
1557 session.username,
1558 session.logincode,
1559 session.oauth_foreign_id,
1560 session.oauth_provider,
1561 session.firstname,
1562 session.lastname,
1563 session.grade,
1564 session.sex,
1565 session.anonymous,
1566 session.additional_contest_time,
1567 session.data_protection_clearance,
1568 session.is_teacher,
1569 session.managed_by,
1570 ug.name,
1571 ug.tag,
1572 teacher.id,
1573 teacher.firstname,
1574 teacher.lastname,
1575 teacher.oauth_foreign_id,
1576 teacher.oauth_provider,
1577 participation.contest,
1578 participation.team,
1579 participation.start_date
1580 {}
1581 FROM participation
1582 JOIN session ON participation.session = session.id
1583 JOIN session AS gsession ON participation.team = gsession.id OR (participation.team IS NULL AND participation.session = gsession.id)
1584 {}
1585 LEFT JOIN usergroup AS ug ON session.managed_by = ug.id
1586 LEFT JOIN session AS teacher ON teacher.id = (SELECT MIN(usergroup_admin.session) FROM usergroup_admin WHERE usergroup_admin.usergroup = ug.id)
1587 WHERE participation.contest = ?1",
1588 select_part, join_part);
1589
1590 use csv::Writer;
1591 let mut wtr = Writer::from_writer(file);
1592 wtr.serialize(&headers).unwrap();
1593 wtr.flush().unwrap();
1594
1595 let file = wtr.into_inner().unwrap();
1596 let mut wtr = Writer::from_writer(file);
1597
1598 self.query_map_many(&query, join_params.as_slice(), |row| {
1599 let mut points = Vec::new();
1600 for i in 24..24 + taskgroups.len() {
1602 points.push(row.get::<_, Option<i32>>(i));
1603 }
1604
1605 let teacher_oauth_and_school_id = row.get::<_, Option<String>>(19);
1606 let (teacher_oauth_id, teacher_school_id) = if let Some(toasi) = teacher_oauth_and_school_id {
1607 let mut v = toasi.split('/');
1608 let oid: Option<String> = v.next().map(|s| s.to_owned());
1609 let sid: Option<String> = v.next().map(|s| s.to_owned());
1610 (oid, sid)
1611 } else {
1612 (None, None)
1613 };
1614
1615 let team_lead = row.get::<_, Option<i64>>(22);
1616
1617 wtr.serialize(((row.get::<_, i64>(0),
1620 row.get::<_, Option<String>>(1),
1621 row.get::<_, Option<String>>(2),
1622 row.get::<_, Option<String>>(3),
1623 row.get::<_, Option<String>>(4),
1624 row.get::<_, Option<String>>(5),
1625 row.get::<_, Option<String>>(6),
1626 row.get::<_, i32>(7),
1627 row.get::<_, Option<i32>>(8),
1628 row.get::<_, bool>(9)),
1629 (row.get::<_, Option<i32>>(10),
1630 row.get::<_, i32>(11),
1631 row.get::<_, bool>(12),
1632 row.get::<_, Option<i64>>(13),
1633 row.get::<_, Option<String>>(14),
1634 row.get::<_, Option<String>>(15),
1635 row.get::<_, Option<i64>>(16),
1636 row.get::<_, Option<String>>(17),
1637 row.get::<_, Option<String>>(18),
1638 teacher_oauth_id),
1639 (teacher_school_id,
1640 row.get::<_, Option<String>>(20),
1641 row.get::<_, Option<i64>>(21),
1642 team_lead.is_some(),
1643 team_lead,
1644 row.get::<_, Option<time::Timespec>>(23)
1645 .map(|ts| self::time::strftime("%FT%T%z", &time::at(ts)).unwrap()),
1646 points)))
1647 .unwrap();
1648 })
1649 .unwrap();
1650 wtr.flush().unwrap();
1651 }
1652
1653 fn insert_contest_annotations(&self, contest_id: i64, annotations: Vec<(i64, Option<String>)>) -> i64 {
1654 let batch_size = 10;
1655 let query_batch = if self.dbtype() == "postgres" {
1656 "UPDATE participation
1657 SET annotation = batchdata.annotation
1658 FROM (SELECT ?2::int AS userid, ?3 as annotation
1659 UNION ALL SELECT ?4::int AS userid, ?5 as annotation
1660 UNION ALL SELECT ?6::int AS userid, ?7 as annotation
1661 UNION ALL SELECT ?8::int AS userid, ?9 as annotation
1662 UNION ALL SELECT ?10::int AS userid, ?11 as annotation
1663 UNION ALL SELECT ?12::int AS userid, ?13 as annotation
1664 UNION ALL SELECT ?14::int AS userid, ?15 as annotation
1665 UNION ALL SELECT ?16::int AS userid, ?17 as annotation
1666 UNION ALL SELECT ?18::int AS userid, ?19 as annotation
1667 UNION ALL SELECT ?20::int AS userid, ?21 as annotation
1668 ) AS batchdata
1669 WHERE session = batchdata.userid
1670 AND contest = ?1"
1671 } else {
1672 "UPDATE participation
1673 SET annotation = batchdata.annotation
1674 FROM (SELECT ?2 AS userid, ?3 as annotation
1675 UNION ALL SELECT ?4 AS userid, ?5 as annotation
1676 UNION ALL SELECT ?6 AS userid, ?7 as annotation
1677 UNION ALL SELECT ?8 AS userid, ?9 as annotation
1678 UNION ALL SELECT ?10 AS userid, ?11 as annotation
1679 UNION ALL SELECT ?12 AS userid, ?13 as annotation
1680 UNION ALL SELECT ?14 AS userid, ?15 as annotation
1681 UNION ALL SELECT ?16 AS userid, ?17 as annotation
1682 UNION ALL SELECT ?18 AS userid, ?19 as annotation
1683 UNION ALL SELECT ?20 AS userid, ?21 as annotation
1684 ) AS batchdata
1685 WHERE session = batchdata.userid
1686 AND contest = ?1"
1687 };
1688
1689 let query_single = "UPDATE participation
1690 SET annotation = ?3
1691 WHERE session = ?2
1692 AND contest = ?1";
1693
1694 let n_annotations = annotations.len();
1695 let n_batches = n_annotations / batch_size;
1696 let n_single = n_annotations % batch_size;
1697
1698 #[cfg(feature = "debug")]
1699 println!("Annotations: {}, {} batches a {}, {} single", n_annotations, n_batches, batch_size, n_single);
1700
1701 let mut rows_modified = 0;
1702
1703 for batch in 0..n_batches {
1704 let off = batch * batch_size;
1705 rows_modified += self.execute(query_batch,
1706 &[&contest_id,
1707 &annotations[off].0,
1708 &annotations[off].1,
1709 &annotations[off + 1].0,
1710 &annotations[off + 1].1,
1711 &annotations[off + 2].0,
1712 &annotations[off + 2].1,
1713 &annotations[off + 3].0,
1714 &annotations[off + 3].1,
1715 &annotations[off + 4].0,
1716 &annotations[off + 4].1,
1717 &annotations[off + 5].0,
1718 &annotations[off + 5].1,
1719 &annotations[off + 6].0,
1720 &annotations[off + 6].1,
1721 &annotations[off + 7].0,
1722 &annotations[off + 7].1,
1723 &annotations[off + 8].0,
1724 &annotations[off + 8].1,
1725 &annotations[off + 9].0,
1726 &annotations[off + 9].1])
1727 .unwrap();
1728 }
1729
1730 let off = n_annotations - n_single;
1731 for single in 0..n_single {
1732 rows_modified += self.execute(query_single,
1733 &[&contest_id, &annotations[off + single].0, &annotations[off + single].1])
1734 .unwrap();
1735 }
1736
1737 rows_modified as i64
1738 }
1739
1740 fn get_submission_by_id_complete_shallow_contest(&self, submission_id: i64)
1741 -> Option<(Submission, Task, Taskgroup, Contest)> {
1742 let query = "SELECT submission.session, submission.grade, submission.validated, submission.nonvalidated_grade,
1743 submission.needs_validation, submission.subtask_identifier, submission.value,
1744 submission.date,
1745 task.id, task.location, task.language, task.stars,
1746 taskgroup.id, taskgroup.name, taskgroup.active, taskgroup.positionalnumber,
1747 contest.id, contest.location, contest.filename, contest.name, contest.duration,
1748 contest.public, contest.protected
1749 FROM submission
1750 JOIN task ON task.id = submission.task
1751 JOIN taskgroup ON taskgroup.id = task.taskgroup
1752 JOIN contest ON contest.id = taskgroup.contest
1753 WHERE submission.id = ?1";
1754 self.query_map_one(query, &[&submission_id], |row| {
1755 (Submission { id: Some(submission_id),
1756 user: row.get(0),
1757 task: row.get(8),
1758 grade: row.get(1),
1759 validated: row.get(2),
1760 nonvalidated_grade: row.get(3),
1761 needs_validation: row.get(4),
1762 subtask_identifier: row.get(5),
1763 value: row.get(6),
1764 date: row.get(7),
1765 autosave: false,
1766 higher_grade: false,
1767 latest: false,
1768 highest_grade_latest: false },
1769 Task { id: Some(row.get(8)),
1770 taskgroup: row.get(12),
1771 location: row.get(9),
1772 language: row.get(10),
1773 stars: row.get(11) },
1774 Taskgroup { id: row.get(12),
1775 contest: row.get(16),
1776 name: row.get(13),
1777 active: row.get(14),
1778 positionalnumber: row.get(15),
1779 tasks: Vec::new() },
1780 Contest { id: row.get(16),
1781 location: row.get(17),
1782 filename: row.get(18),
1783 name: row.get(19),
1784 duration: row.get(20),
1785 public: row.get(21),
1786 start: None,
1787 end: None,
1788 review_start: None,
1789 review_end: None,
1790 min_grade: None,
1791 max_grade: None,
1792 max_teamsize: None,
1793 positionalnumber: None,
1794 requires_login: None,
1795 requires_contests: Vec::new(),
1796 protected: row.get(22),
1797 secret: None,
1798 message: None,
1799 image: None,
1800 language: None,
1801 category: None,
1802 standalone_task: None,
1803 colour: None,
1804 tags: Vec::new(),
1805 stickers: Vec::new(),
1806 taskgroups: Vec::new() })
1807 })
1808 .unwrap()
1809 }
1810
1811 fn get_contest_list(&self) -> Vec<Contest> {
1812 let query = "SELECT id, location, filename, name, duration, public, start_date, end_date, review_start_date,
1813 review_end_date, min_grade, max_grade, positionalnumber, protected, requires_login,
1814 secret, message, image, language, category, standalone_task,
1815 max_teamsize, colour, contest_tags.tags
1816 FROM contest
1817 LEFT JOIN contest_tags USING (id)
1818 ORDER BY positionalnumber ASC, end_date DESC, location ASC, filename ASC";
1819 self.query_map_many(query, &[], |row| Contest { id: Some(row.get(0)),
1820 location: row.get(1),
1821 filename: row.get(2),
1822 name: row.get(3),
1823 duration: row.get(4),
1824 public: row.get(5),
1825 start: row.get(6),
1826 end: row.get(7),
1827 review_start: row.get(8),
1828 review_end: row.get(9),
1829 min_grade: row.get(10),
1830 max_grade: row.get(11),
1831 positionalnumber: row.get(12),
1832 protected: row.get(13),
1833 requires_login: row.get(14),
1834 requires_contests: Vec::new(),
1835 secret: row.get(15),
1836 message: row.get(16),
1837 image: row.get(17),
1838 language: row.get(18),
1839 category: row.get(19),
1840 standalone_task: row.get(20),
1841 max_teamsize: row.get(21),
1842 colour: row.get(22),
1843 tags: row.get::<_, Option<String>>(23)
1844 .map(|tags| {
1845 tags.split(',').map(|tag| tag.to_owned()).collect()
1846 })
1847 .unwrap_or_else(Vec::new),
1848 stickers: Vec::new(),
1849 taskgroups: Vec::new() })
1850 .unwrap()
1851 }
1852
1853 fn get_contest_list_with_group_member_participations(&self, session_id: i64) -> Vec<Contest> {
1854 let query = "SELECT DISTINCT contest.id, contest.location, contest.filename, contest.name, contest.duration,
1855 contest.public, contest.start_date, contest.end_date, contest.review_start_date,
1856 contest.review_end_date, contest.min_grade, contest.max_grade, contest.positionalnumber,
1857 contest.protected, contest.requires_login, contest.secret,
1858 contest.message, contest.image, contest.language, contest.category, contest.standalone_task,
1859 contest.max_teamsize, contest.colour, contest_tags.tags
1860 FROM contest
1861 JOIN participation ON participation.contest = contest.id
1862 JOIN session ON session.id = participation.session
1863 JOIN usergroup ON usergroup.id = session.managed_by
1864 JOIN usergroup_admin ON usergroup_admin.usergroup = usergroup.id
1865 LEFT JOIN contest_tags ON contest.id = contest_tags.id
1866 WHERE usergroup_admin.session = ?1
1867 ORDER BY positionalnumber ASC, end_date DESC, location ASC, filename ASC";
1868 self.query_map_many(query, &[&session_id], |row| Contest { id: Some(row.get(0)),
1869 location: row.get(1),
1870 filename: row.get(2),
1871 name: row.get(3),
1872 duration: row.get(4),
1873 public: row.get(5),
1874 start: row.get(6),
1875 end: row.get(7),
1876 review_start: row.get(8),
1877 review_end: row.get(9),
1878 min_grade: row.get(10),
1879 max_grade: row.get(11),
1880 positionalnumber: row.get(12),
1881 protected: row.get(13),
1882 requires_login: row.get(14),
1883 requires_contests: Vec::new(),
1884 secret: row.get(15),
1885 message: row.get(16),
1886 image: row.get(17),
1887 language: row.get(18),
1888 category: row.get(19),
1889 standalone_task: row.get(20),
1890 max_teamsize: row.get(21),
1891 colour: row.get(22),
1892 tags: row.get::<_, Option<String>>(23)
1893 .map(|tags| {
1894 tags.split(',')
1895 .map(|tag| tag.to_owned())
1896 .collect()
1897 })
1898 .unwrap_or_else(Vec::new),
1899 stickers: Vec::new(),
1900 taskgroups: Vec::new() })
1901 .unwrap()
1902 }
1903
1904 fn get_contest_by_id(&self, contest_id: i64) -> Option<Contest> {
1905 let query = "SELECT location, filename, name, duration, public, start_date, end_date, review_start_date,
1906 review_end_date, min_grade, max_grade, protected, requires_login, secret,
1907 message, image, language, category, standalone_task, max_teamsize, colour
1908 FROM contest
1909 WHERE id = ?1";
1910 self.query_map_one(query, &[&contest_id], |row| Contest { id: Some(contest_id),
1911 location: row.get(0),
1912 filename: row.get(1),
1913 name: row.get(2),
1914 duration: row.get(3),
1915 public: row.get(4),
1916 start: row.get(5),
1917 end: row.get(6),
1918 review_start: row.get(7),
1919 review_end: row.get(8),
1920 min_grade: row.get(9),
1921 max_grade: row.get(10),
1922 positionalnumber: None,
1923 protected: row.get(11),
1924 requires_login: row.get(12),
1925 requires_contests: Vec::new(),
1926 secret: row.get(13),
1927 message: row.get(14),
1928 image: row.get(15),
1929 language: row.get(16),
1930 category: row.get(17),
1931 standalone_task: row.get(18),
1932 max_teamsize: row.get(19),
1933 colour: row.get(20),
1934 tags: Vec::new(),
1935 stickers: Vec::new(),
1936 taskgroups: Vec::new() })
1937 .unwrap()
1938 }
1939
1940 fn get_contest_by_id_complete(&self, contest_id: i64) -> Option<Contest> {
1941 let query = "SELECT contest.location, contest.filename, contest.name, contest.duration, contest.public,
1942 contest.start_date, contest.end_date, contest.review_start_date, contest.review_end_date,
1943 contest.min_grade, contest.max_grade, contest.protected, contest.requires_login,
1944 contest.secret, contest.message, contest.image, contest.language,
1945 contest.category, contest.standalone_task, contest.max_teamsize, contest.colour,
1946 taskgroup.id, taskgroup.name,
1947 task.id, task.location, task.language, task.stars,
1948 contest_requires_contests.requires_contests
1949 FROM contest
1950 JOIN taskgroup ON contest.id = taskgroup.contest
1951 JOIN task ON taskgroup.id = task.taskgroup
1952 LEFT JOIN contest_requires_contests ON contest.id = contest_requires_contests.id
1953 WHERE contest.id = ?1
1954 AND taskgroup.active = ?2
1955 ORDER BY taskgroup.positionalnumber";
1956 let taskgroupcontest = self.query_map_many(query, &[&contest_id, &true], |row| {
1957 (Contest { id: Some(contest_id),
1958 location: row.get(0),
1959 filename: row.get(1),
1960 name: row.get(2),
1961 duration: row.get(3),
1962 public: row.get(4),
1963 start: row.get(5),
1964 end: row.get(6),
1965 review_start: row.get(7),
1966 review_end: row.get(8),
1967 min_grade: row.get(9),
1968 max_grade: row.get(10),
1969 positionalnumber: None,
1970 protected: row.get(11),
1971 requires_login: row.get(12),
1972 secret: row.get(13),
1973 message: row.get(14),
1974 image: row.get(15),
1975 language: row.get(16),
1976 category: row.get(17),
1977 standalone_task: row.get(18),
1978 max_teamsize: row.get(19),
1979 colour: row.get(20),
1980 tags: Vec::new(),
1981 requires_contests: row.get::<_, Option<String>>(27)
1982 .map(|requires_contests_string| {
1983 requires_contests_string.split("\x1e")
1985 .map(|requires_contests| {
1986 let mut reqs =
1988 requires_contests.split("\x1f");
1989 RequiredContest {
1990 filename: reqs.next()
1991 .unwrap_or("")
1992 .to_string(),
1993 required_stars: reqs.next()
1994 .unwrap_or("")
1995 .parse::<i32>()
1996 .unwrap_or(0),
1997 min_grade: reqs.next()
1998 .unwrap_or("")
1999 .parse::<i32>()
2000 .unwrap_or(0),
2001 max_grade: reqs.next()
2002 .unwrap_or("")
2003 .parse::<i32>()
2004 .unwrap_or(255)
2005 }
2006 })
2007 .collect()
2008 })
2009 .unwrap_or_else(Vec::new),
2010 stickers: Vec::new(),
2011 taskgroups: Vec::new() },
2012 Taskgroup { id: Some(row.get(21)),
2013 contest: contest_id,
2014 name: row.get(22),
2015 active: true,
2016 positionalnumber: None,
2017 tasks: Vec::new() },
2018 Task { id: Some(row.get(23)),
2019 taskgroup: row.get(21),
2020 location: row.get(24),
2021 language: row.get(25),
2022 stars: row.get(26) })
2023 })
2024 .unwrap();
2025 let mut taskgroupcontest_iter = taskgroupcontest.into_iter();
2026
2027 if let Some((mut contest, mut taskgroup, task)) = taskgroupcontest_iter.next() {
2028 taskgroup.tasks.push(task);
2029 for tgc in taskgroupcontest_iter {
2030 let (_, tg, t) = tgc;
2031 if tg.id != taskgroup.id {
2032 contest.taskgroups.push(taskgroup);
2033 taskgroup = tg;
2034 }
2035 taskgroup.tasks.push(t);
2036 }
2037 contest.taskgroups.push(taskgroup);
2038 Some(contest)
2039 } else {
2040 self.get_contest_by_id(contest_id)
2043 }
2044 }
2045
2046 fn get_contest_by_id_partial(&self, contest_id: i64) -> Option<Contest> {
2047 let query = "SELECT contest.location, contest.filename, contest.name, contest.duration, contest.public,
2048 contest.start_date, contest.end_date, contest.review_start_date, contest.review_end_date,
2049 contest.min_grade, contest.max_grade, contest.protected, contest.requires_login,
2050 contest.secret, contest.message, contest.image, contest.language,
2051 contest.category, contest.standalone_task, contest.max_teamsize, contest.colour,
2052 taskgroup.id, taskgroup.name
2053 FROM contest
2054 JOIN taskgroup ON contest.id = taskgroup.contest
2055 WHERE contest.id = ?1
2056 AND taskgroup.active = ?2";
2057 let taskgroupcontest = self.query_map_many(query, &[&contest_id, &true], |row| {
2058 (Contest { id: Some(contest_id),
2059 location: row.get(0),
2060 filename: row.get(1),
2061 name: row.get(2),
2062 duration: row.get(3),
2063 public: row.get(4),
2064 start: row.get(5),
2065 end: row.get(6),
2066 review_start: row.get(7),
2067 review_end: row.get(8),
2068 min_grade: row.get(9),
2069 max_grade: row.get(10),
2070 positionalnumber: None,
2071 protected: row.get(11),
2072 requires_login: row.get(12),
2073 requires_contests: Vec::new(),
2074 secret: row.get(13),
2075 message: row.get(14),
2076 image: row.get(15),
2077 language: row.get(16),
2078 category: row.get(17),
2079 standalone_task: row.get(18),
2080 max_teamsize: row.get(19),
2081 colour: row.get(20),
2082 tags: Vec::new(),
2083 stickers: Vec::new(),
2084 taskgroups: Vec::new() },
2085 Taskgroup { id: Some(row.get(21)),
2086 contest: contest_id,
2087 name: row.get(22),
2088 active: true,
2089 positionalnumber: None,
2090 tasks: Vec::new() })
2091 })
2092 .unwrap();
2093 let mut taskgroupcontest_iter = taskgroupcontest.into_iter();
2094
2095 if let Some((mut contest, taskgroup)) = taskgroupcontest_iter.next() {
2096 contest.taskgroups.push(taskgroup);
2097 for tgc in taskgroupcontest_iter {
2098 let (_, tg) = tgc;
2099 contest.taskgroups.push(tg);
2100 }
2101 Some(contest)
2102 } else {
2103 self.get_contest_by_id(contest_id)
2106 }
2107 }
2108
2109 fn get_participation(&self, session_id: i64, contest_id: i64) -> Option<Participation> {
2110 let query = "SELECT start_date, team
2111 FROM participation
2112 WHERE session = ?1
2113 AND contest = ?2";
2114 self.query_map_one(query, &[&session_id, &contest_id], |row| Participation { contest: contest_id,
2115 user: session_id,
2116 start: row.get(0),
2117 team: row.get(1),
2118 annotation: None })
2119 .ok()?
2120 }
2121
2122 fn get_own_participation(&self, session_id: i64, contest_id: i64) -> Option<Participation> {
2123 let query = "SELECT start_date, team
2124 FROM participation
2125 WHERE session = ?1
2126 AND contest = ?2";
2127 self.query_map_one(query, &[&session_id, &contest_id], |row| Participation { contest: contest_id,
2128 user: session_id,
2129 start: row.get(0),
2130 team: row.get(1),
2131 annotation: None })
2132 .ok()?
2133 }
2134
2135 fn get_all_participations_complete(&self, session_id: i64) -> Vec<(Participation, Contest)> {
2136 let query = "SELECT contest.id, location, filename, name, duration, public, contest.start_date, end_date,
2137 review_start_date, review_end_date, min_grade, max_grade, protected, requires_login,
2138 secret, message, category, colour,
2139 participation.start_date, participation.team, participation.annotation,
2140 contest_stickers.stickers
2141 FROM participation
2142 JOIN contest ON participation.contest = contest.id
2143 LEFT JOIN contest_stickers ON participation.contest = contest_stickers.id
2144 WHERE participation.session = ?1
2145 AND (standalone_task IS NULL OR standalone_task = FALSE)
2146 ORDER BY contest.category ASC, contest.positionalnumber ASC, contest.end_date DESC,
2147 contest.location ASC, filename ASC";
2148 self.query_map_many(query, &[&session_id], |row| {
2149 (Participation { contest: row.get(0),
2150 user: session_id,
2151 start: row.get(18),
2152 team: row.get(19),
2153 annotation: row.get(20) },
2154 Contest { id: Some(row.get(0)),
2155 location: row.get(1),
2156 filename: row.get(2),
2157 name: row.get(3),
2158 duration: row.get(4),
2159 public: row.get(5),
2160 start: row.get(6),
2161 end: row.get(7),
2162 review_start: row.get(8),
2163 review_end: row.get(9),
2164 min_grade: row.get(10),
2165 max_grade: row.get(11),
2166 positionalnumber: None,
2167 protected: row.get(12),
2168 requires_login: row.get(13),
2169 requires_contests: Vec::new(),
2170 secret: row.get(14),
2171 message: row.get(15),
2172 image: None,
2173 language: None,
2174 category: row.get(16),
2175 max_teamsize: None,
2176 colour: row.get(17),
2177 standalone_task: None,
2178 tags: Vec::new(),
2179 stickers: row.get::<_, Option<String>>(21)
2180 .map(|stickerstring| {
2181 stickerstring.split("\x1e")
2183 .map(|sticker| {
2184 let mut stckr = sticker.split("\x1f");
2186 (stckr.next().unwrap_or("").to_string(),
2187 stckr.next().unwrap_or("").parse::<i32>().unwrap_or(0))
2188 })
2189 .collect()
2190 })
2191 .unwrap_or_else(Vec::new),
2192 taskgroups: Vec::new() })
2193 })
2194 .unwrap()
2195 }
2196
2197 fn count_all_stars(&self, session_id: i64) -> i32 {
2198 let query = "SELECT COALESCE(SUM(grade.grade), 0) AS stars
2199 FROM participation
2200 JOIN taskgroup ON participation.contest = taskgroup.contest
2201 JOIN session ON session.id = participation.team
2202 OR (participation.team IS NULL AND session.id = participation.session)
2203 JOIN grade ON grade.taskgroup = taskgroup.id AND grade.session = session.id
2204 WHERE participation.session = ?1";
2205 self.query_map_one(query, &[&session_id], |row| -> i64 { row.get(0) }).unwrap().unwrap() as i32
2206 }
2207
2208 fn count_all_stars_by_contest(&self, session_id: i64) -> Vec<(i64, i32)> {
2209 let query = "SELECT participation.contest, COALESCE(SUM(grade.grade), 0) AS stars
2210 FROM participation
2211 JOIN taskgroup ON participation.contest = taskgroup.contest
2212 JOIN session ON session.id = participation.team
2213 OR (participation.team IS NULL AND session.id = participation.session)
2214 JOIN grade ON grade.taskgroup = taskgroup.id AND grade.session = session.id
2215 WHERE participation.session = ?1
2216 GROUP BY participation.contest";
2217 self.query_map_many(query, &[&session_id], |row| -> (i64, i32) {
2218 (row.get::<_, i64>(0), row.get::<_, i64>(1) as i32)
2219 })
2220 .unwrap()
2221 }
2222
2223 fn has_participation_by_contest_file(&self, session_id: i64, location: &str, filename: &str) -> bool {
2224 let query = "SELECT participation.contest
2225 FROM participation
2226 JOIN contest ON participation.contest = contest.id
2227 WHERE participation.session = ?1
2228 AND contest.location = ?2
2229 AND contest.filename = ?3";
2230 self.exists(query, &[&session_id, &location, &filename])
2231 }
2232
2233 fn has_participation_by_contest_file_and_stars(&self, session_id: i64, location: &str, filename: &str,
2234 required_stars: i32)
2235 -> bool {
2236 let query = "SELECT *
2237 FROM
2238 (
2239 SELECT participation.contest, COALESCE(SUM(grade.grade), 0) AS stars
2240 FROM participation
2241 JOIN taskgroup ON participation.contest = taskgroup.contest
2242 JOIN session ON session.id = participation.team
2243 OR (participation.team IS NULL AND session.id = participation.session)
2244 JOIN grade ON grade.taskgroup = taskgroup.id AND grade.session = session.id
2245 JOIN contest ON participation.contest = contest.id
2246 WHERE participation.session = ?1
2247 AND contest.location = ?2
2248 AND contest.filename = ?3
2249 GROUP BY participation.contest
2250 ) contests_with_stars
2251 WHERE stars >= ?4";
2252 self.exists(query, &[&session_id, &location, &filename, &(required_stars as i64)])
2253 }
2254
2255 fn new_participation(&self, session_id: i64, contest_id: i64, team: Option<i64>) -> Result<Participation, ()> {
2256 let query = "SELECT start_date
2257 FROM participation
2258 WHERE session = ?1
2259 AND contest = ?2";
2260 match self.query_map_one(query, &[&session_id, &contest_id], |_| {}).map_err(|_| ())? {
2261 Some(()) => Err(()),
2262 None => {
2263 let insert = "INSERT INTO participation (contest, session, start_date, team)
2264 VALUES (?1, ?2, ?3, ?4)";
2265
2266 let now = time::get_time();
2267 self.execute(insert, &[&contest_id, &session_id, &now, &team]).unwrap();
2268
2269 Ok(self.get_own_participation(session_id, contest_id).unwrap()) }
2271 }
2272 }
2273
2274 fn move_participation_complete(&self, contest_id: i64, old_session_id: i64, new_session_id: i64) {
2275 let query = "UPDATE participation
2276 SET session = ?3
2277 WHERE contest = ?1
2278 AND session = ?2";
2279 self.execute(query, &[&contest_id, &old_session_id, &new_session_id]).unwrap();
2280
2281 let query = "UPDATE participation
2282 SET team = ?3
2283 WHERE contest = ?1
2284 AND team = ?2";
2285 self.execute(query, &[&contest_id, &old_session_id, &new_session_id]).unwrap();
2286
2287 let query = "UPDATE grade
2288 SET session = ?3
2289 FROM taskgroup
2290 WHERE taskgroup.id = grade.taskgroup
2291 AND taskgroup.contest = ?1
2292 AND grade.session = ?2";
2293 self.execute(query, &[&contest_id, &old_session_id, &new_session_id]).unwrap();
2294
2295 let query = "UPDATE submission
2296 SET session = ?3
2297 FROM task
2298 JOIN taskgroup ON taskgroup.id = task.taskgroup
2299 WHERE task.id = submission.task
2300 AND taskgroup.contest = ?1
2301 AND submission.session = ?2";
2302 self.execute(query, &[&contest_id, &old_session_id, &new_session_id]).unwrap();
2303 }
2304
2305 fn get_task_by_id(&self, task_id: i64) -> Option<Task> {
2306 let query = "SELECT location, language, stars, taskgroup
2307 FROM task
2308 WHERE id = ?1";
2309 self.query_map_one(query, &[&task_id], |row| Task { id: Some(task_id),
2310 taskgroup: row.get(3),
2311 location: row.get(0),
2312 language: row.get(1),
2313 stars: row.get(2) })
2314 .unwrap()
2315 }
2316 fn get_task_by_id_complete(&self, task_id: i64) -> Option<(Task, Taskgroup, Contest)> {
2317 let query = "SELECT task.location, task.language, task.stars,
2318 taskgroup.id, taskgroup.name, taskgroup.active,
2319 contest.id, contest.location, contest.filename, contest.name, contest.duration,
2320 contest.public, contest.start_date, contest.end_date, contest.review_start_date,
2321 contest.review_end_date, contest.min_grade, contest.max_grade, contest.protected,
2322 contest.requires_login, contest.secret, contest.message,
2323 contest.category, contest.standalone_task, contest.colour
2324 FROM contest
2325 JOIN taskgroup ON taskgroup.contest = contest.id
2326 JOIN task ON task.taskgroup = taskgroup.id
2327
2328 WHERE task.id = ?1";
2329 self.query_map_one(query, &[&task_id], |row| {
2330 (Task { id: Some(task_id),
2331 taskgroup: row.get(3),
2332 location: row.get(0),
2333 language: row.get(1),
2334 stars: row.get(2) },
2335 Taskgroup { id: Some(row.get(3)),
2336 contest: row.get(6),
2337 name: row.get(4),
2338 active: row.get(5),
2339 positionalnumber: None,
2340 tasks: Vec::new() },
2341 Contest { id: Some(row.get(6)),
2342 location: row.get(7),
2343 filename: row.get(8),
2344 name: row.get(9),
2345 duration: row.get(10),
2346 public: row.get(11),
2347 start: row.get(12),
2348 end: row.get(13),
2349 review_start: row.get(14),
2350 review_end: row.get(15),
2351 min_grade: row.get(16),
2352 max_grade: row.get(17),
2353 positionalnumber: None,
2354 protected: row.get(18),
2355 requires_login: row.get(19),
2356 secret: row.get(20),
2357 message: row.get(21),
2358 image: None,
2359 language: None,
2360 category: row.get(22),
2361 standalone_task: row.get(23),
2362 colour: row.get(24),
2363 tags: Vec::new(),
2364 stickers: Vec::new(),
2365 requires_contests: Vec::new(),
2366 max_teamsize: None,
2367 taskgroups: Vec::new() })
2368 })
2369 .unwrap()
2370 }
2371
2372 fn get_submission_to_validate(&self, tasklocation: &str, subtask: Option<&str>) -> i64 {
2373 match subtask {
2374 Some(st) => {
2375 let query = "SELECT id
2376 FROM submission
2377 JOIN task ON submission.task = task.id
2378 WHERE task.location = ?1
2379 AND subtask_identifier = ?2
2380 AND needs_validation = 1
2381 LIMIT 1";
2382 self.query_map_one(query, &[&tasklocation, &st], |row| row.get(0)).unwrap().unwrap()
2383 }
2384 None => {
2385 let query = "SELECT id
2386 FROM submission
2387 JOIN task ON submission.task = task.id
2388 WHERE task.location = ?1
2389 AND needs_validation = 1
2390 LIMIT 1";
2391 self.query_map_one(query, &[&tasklocation], |row| row.get(0)).unwrap().unwrap()
2392 }
2393 }
2394 }
2395
2396 fn find_next_submission_to_validate(&self, user_id: i64, taskgroup_id: i64) {
2397 let query = "SELECT id, validated
2398 FROM submission
2399 JOIN task ON submission.task = task.id
2400 WHERE task.taskgroup = ?1
2401 AND submission.session = ?2
2402 ORDER BY value DESC id DESC
2403 LIMIT 1";
2404 let (id, validated): (i64, bool) =
2405 self.query_map_one(query, &[&taskgroup_id, &user_id], |row| (row.get(0), row.get(1))).unwrap().unwrap();
2406 if !validated {
2407 let query = "UPDATE submission
2408 SET needs_validation = 1
2409 WHERE id = ?1";
2410 self.execute(query, &[&id]).unwrap();
2411 }
2412 }
2413
2414 fn add_group(&self, group: &mut Group) { group.save(self); }
2415
2416 fn save_group(&self, group: &mut Group) { group.save(self); }
2417
2418 fn get_groups(&self, session_id: i64) -> Vec<Group> {
2419 let query = "SELECT id, name, groupcode, tag
2420 FROM usergroup
2421 JOIN usergroup_admin ON usergroup.id = usergroup_admin.usergroup
2422 WHERE usergroup_admin.session = ?1";
2423 self.query_map_many(query, &[&session_id], |row| Group { id: Some(row.get(0)),
2424 name: row.get(1),
2425 groupcode: row.get(2),
2426 tag: row.get(3),
2427 admins: Vec::new(),
2428 members: Vec::new() })
2429 .unwrap()
2430 }
2431 fn get_groups_complete(&self, _session_id: i64) -> Vec<Group> {
2432 unimplemented!();
2433 }
2434
2435 fn get_group(&self, group_id: i64) -> Option<Group> {
2436 let query = "SELECT name, groupcode, tag
2437 FROM usergroup
2438 WHERE id = ?1";
2439 let mut group = self.query_map_one(query, &[&group_id], |row| Group { id: Some(group_id),
2440 name: row.get(0),
2441 groupcode: row.get(1),
2442 tag: row.get(2),
2443 admins: Vec::new(),
2444 members: Vec::new() })
2445 .unwrap()?;
2446
2447 let query = "SELECT session FROM usergroup_admin WHERE usergroup = ?1 ORDER BY session";
2448 group.admins = self.query_map_many(query, &[&group_id], |row| row.get(0)).unwrap();
2449
2450 Some(group)
2451 }
2452
2453 fn group_has_protected_participations(&self, group_id: i64) -> bool {
2454 let query = "SELECT EXISTS(
2455 SELECT session.id
2456 FROM session
2457 JOIN participation ON participation.session = session.id
2458 JOIN contest ON contest.id = participation.contest
2459 WHERE managed_by = ?1
2460 AND contest.protected = ?2
2461 )";
2462 self.query_map_one(query, &[&group_id, &true], |row| row.get(0)).unwrap().unwrap()
2463 }
2464
2465 fn get_group_complete(&self, group_id: i64) -> Option<Group> {
2466 let query = "SELECT name, groupcode, tag
2467 FROM usergroup
2468 WHERE id = ?1";
2469 let mut group = self.query_map_one(query, &[&group_id], |row| Group { id: Some(group_id),
2470 name: row.get(0),
2471 groupcode: row.get(1),
2472 tag: row.get(2),
2473 admins: Vec::new(),
2474 members: Vec::new() })
2475 .unwrap()?;
2476
2477 let query = "SELECT session FROM usergroup_admin WHERE usergroup = ?1 ORDER BY session";
2478 group.admins = self.query_map_many(query, &[&group_id], |row| row.get(0)).unwrap();
2479
2480 let query = "SELECT id, session_token, csrf_token, last_login, last_activity, account_created, username,
2481 password, salt, logincode, email, email_unconfirmed, email_confirmationcode, firstname,
2482 lastname, street, zip, city, nation, grade, sex, anonymous, additional_contest_time,
2483 data_protection_clearance, is_admin, is_teacher, school_name, oauth_provider,
2484 oauth_foreign_id
2485 FROM session
2486 WHERE managed_by = ?1
2487 ORDER BY id";
2488 group.members =
2489 self.query_map_many(query, &[&group_id], |row| SessionUser { id: row.get(0),
2490 session_token: row.get(1),
2491 csrf_token: row.get(2),
2492 last_login: row.get(3),
2493 last_activity: row.get(4),
2494 account_created: row.get(5),
2495
2496 username: row.get(6),
2497 password: row.get(7),
2498 salt: row.get(8),
2499 logincode: row.get(9),
2500 email: row.get(10),
2501 email_unconfirmed: row.get(11),
2502 email_confirmationcode: row.get(12),
2503
2504 firstname: row.get(13),
2505 lastname: row.get(14),
2506 street: row.get(15),
2507 zip: row.get(16),
2508 city: row.get(17),
2509 nation: row.get(18),
2510 grade: row.get(19),
2511 sex: row.get(20),
2512 anonymous: row.get(21),
2513 additional_contest_time: row.get(22),
2514 data_protection_clearance: row.get(23),
2515
2516 is_admin: row.get(24),
2517 is_teacher: row.get(25),
2518 managed_by: Some(group_id),
2519 school_name: row.get(26),
2520
2521 oauth_provider: row.get(27),
2522 oauth_foreign_id: row.get(28) })
2523 .unwrap();
2524 Some(group)
2525 }
2526
2527 fn delete_user(&self, user_id: i64) {
2528 let query = "DELETE FROM session
2529 WHERE id = ?1";
2530 self.execute(query, &[&user_id]).unwrap();
2531 }
2532 fn delete_all_users_for_group(&self, group_id: i64) {
2533 let query = "DELETE FROM session
2534 WHERE managed_by = ?1
2535 AND oauth_foreign_id IS NULL";
2536 self.execute(query, &[&group_id]).unwrap();
2537
2538 let query = "UPDATE session
2539 SET managed_by = NULL
2540 WHERE managed_by = ?1
2541 AND oauth_foreign_id IS NOT NULL";
2542 self.execute(query, &[&group_id]).unwrap();
2543 }
2544 fn set_data_clearance_for_user(&self, user_id: i64, clearance_state: bool) {
2545 let clearance_value = clearance_state as i32;
2546 let query = "UPDATE session
2547 SET data_protection_clearance = ?2
2548 WHERE id = ?1";
2549 self.execute(query, &[&user_id, &clearance_value]).unwrap();
2550 }
2551 fn set_data_clearance_for_group(&self, group_id: i64, clearance_state: bool) {
2552 let clearance_value = clearance_state as i32;
2553 let query = "UPDATE session
2554 SET data_protection_clearance = ?2
2555 WHERE managed_by = ?1";
2556 self.execute(query, &[&group_id, &clearance_value]).unwrap();
2557 }
2558 fn delete_group(&self, group_id: i64) {
2559 let query = "DELETE FROM usergroup
2560 WHERE id = ?1";
2561 self.execute(query, &[&group_id]).unwrap();
2562 }
2563 fn delete_participation(&self, user_id: i64, contest_id: i64) {
2564 let query = "DELETE FROM submission
2565 WHERE id IN (
2566 SELECT submission.id FROM submission
2567 JOIN task ON submission.task = task.id
2568 JOIN taskgroup ON task.taskgroup = taskgroup.id
2569 WHERE taskgroup.contest = ?1
2570 AND submission.session = ?2
2571 )";
2572 self.execute(query, &[&contest_id, &user_id]).unwrap();
2573
2574 let query = "DELETE FROM grade
2575 WHERE taskgroup IN (
2576 SELECT id FROM taskgroup
2577 WHERE taskgroup.contest = ?1
2578 )
2579 AND session = ?2";
2580 self.execute(query, &[&contest_id, &user_id]).unwrap();
2581
2582 let query = "DELETE FROM participation
2583 WHERE contest = ?1
2584 AND session = ?2";
2585 self.execute(query, &[&contest_id, &user_id]).unwrap();
2586 }
2587
2588 fn get_search_users(
2589 &self,
2590 (s_id, s_firstname, s_lastname, s_logincode, s_groupname, s_groupcode, s_pms_id, s_anything): (Option<i64>,
2591 Option<String>,
2592 Option<String>,
2593 Option<String>,
2594 Option<String>,
2595 Option<String>,
2596 Option<String>,
2597 Option<String>))
2598 -> (Vec<(i64, Option<String>, Option<String>, Option<String>, Option<String>, Option<String>)>,
2599 Vec<(i64, String, String, String)>) {
2600 let mut users = Vec::new();
2601 let mut groups = Vec::new();
2602
2603 if let Some(id) = s_id {
2604 let query = "SELECT id, firstname, lastname, logincode, oauth_foreign_id, oauth_provider
2605 FROM session
2606 WHERE id = ?1
2607 LIMIT 201";
2608 users = self.query_map_many(query, &[&id], |row| {
2609 (row.get(0), row.get(1), row.get(2), row.get(3), row.get(4), row.get(5))
2610 })
2611 .unwrap();
2612 } else if let Some(logincode) = s_logincode {
2613 let query = "SELECT id, firstname, lastname, logincode, oauth_foreign_id, oauth_provider
2614 FROM session
2615 WHERE logincode = ?1
2616 LIMIT 201";
2617 users = self.query_map_many(query, &[&logincode], |row| {
2618 (row.get(0), row.get(1), row.get(2), row.get(3), row.get(4), row.get(5))
2619 })
2620 .unwrap()
2621 } else if let Some(groupcode) = s_groupcode {
2622 let query = "SELECT id, name, tag, groupcode
2623 FROM usergroup
2624 WHERE groupcode = ?1
2625 LIMIT 201";
2626 groups = self.query_map_many(query, &[&groupcode], |row| (row.get(0), row.get(1), row.get(2), row.get(3)))
2627 .unwrap()
2628 } else if let Some(groupname) = s_groupname {
2629 let query = "SELECT id, name, tag, groupcode
2630 FROM usergroup
2631 WHERE name LIKE ?1
2632 LIMIT 201";
2633 groups = self.query_map_many(query, &[&groupname], |row| (row.get(0), row.get(1), row.get(2), row.get(3)))
2634 .unwrap()
2635 } else if let Some(pms_id) = s_pms_id {
2636 let query = "SELECT id, firstname, lastname, logincode, oauth_foreign_id, oauth_provider
2637 FROM session
2638 WHERE oauth_foreign_id = ?1
2639 OR oauth_foreign_id LIKE ?2
2640 LIMIT 201";
2641 users = self.query_map_many(query, &[&pms_id, &format!("{}/%", pms_id)], |row| {
2642 (row.get(0), row.get(1), row.get(2), row.get(3), row.get(4), row.get(5))
2643 })
2644 .unwrap()
2645 } else if let (Some(firstname), Some(lastname)) = (s_firstname, s_lastname) {
2646 let query = "SELECT id, firstname, lastname, logincode, oauth_foreign_id, oauth_provider
2647 FROM session
2648 WHERE firstname LIKE ?1
2649 AND lastname LIKE ?2
2650 ORDER BY id DESC
2651 LIMIT 201";
2652 users = self.query_map_many(query, &[&format!("%{}%", firstname), &format!("%{}%", lastname)], |row| {
2653 (row.get(0), row.get(1), row.get(2), row.get(3), row.get(4), row.get(5))
2654 })
2655 .unwrap()
2656 } else if let Some(anything) = s_anything {
2657 if anything.len() == 0 {
2658 return (users, groups);
2659 }
2660
2661 let query = "SELECT id, name, tag, groupcode
2662 FROM usergroup
2663 WHERE groupcode = ?1
2664 OR name LIKE ?2
2665 LIMIT 201";
2666 groups = self.query_map_many(query, &[&anything, &format!("%{}%", anything)], |row| {
2667 (row.get(0), row.get(1), row.get(2), row.get(3))
2668 })
2669 .unwrap();
2670
2671 if let Ok(id) = anything.parse::<i64>() {
2672 let query = "SELECT id, firstname, lastname, logincode, oauth_foreign_id, oauth_provider
2673 FROM session
2674 WHERE id = ?4
2675 OR logincode = ?1
2676 OR firstname LIKE ?2
2677 OR lastname LIKE ?2
2678 OR oauth_foreign_id = ?1
2679 OR oauth_foreign_id LIKE ?3
2680 LIMIT 201";
2681 users =
2682 self.query_map_many(query,
2683 &[&anything, &format!("%{}%", anything), &format!("{}/%", anything), &id],
2684 |row| (row.get(0), row.get(1), row.get(2), row.get(3), row.get(4), row.get(5)))
2685 .unwrap()
2686 } else {
2687 let v: Vec<&str> = anything.split(' ').collect();
2688 match v.len() {
2689 0 => (),
2690 1 => {
2691 let query = "SELECT id, firstname, lastname, logincode, oauth_foreign_id, oauth_provider
2692 FROM session
2693 WHERE logincode = ?1
2694 OR firstname LIKE ?2
2695 OR lastname LIKE ?2
2696 OR oauth_foreign_id = ?1
2697 OR oauth_foreign_id LIKE ?3
2698 LIMIT 201";
2699 users =
2700 self.query_map_many(query,
2701 &[&anything, &format!("%{}%", anything), &format!("{}/%", anything)],
2702 |row| {
2703 (row.get(0),
2704 row.get(1),
2705 row.get(2),
2706 row.get(3),
2707 row.get(4),
2708 row.get(5))
2709 })
2710 .unwrap()
2711 }
2712 _ => {
2714 let query = "SELECT id, firstname, lastname, logincode, oauth_foreign_id, oauth_provider
2715 FROM session
2716 WHERE (logincode = ?1
2717 OR firstname LIKE ?2
2718 OR lastname LIKE ?2
2719 OR oauth_foreign_id = ?1
2720 OR oauth_foreign_id LIKE ?3)
2721 AND (logincode = ?4
2722 OR firstname LIKE ?5
2723 OR lastname LIKE ?5
2724 OR oauth_foreign_id = ?4
2725 OR oauth_foreign_id LIKE ?6)
2726 LIMIT 201";
2727 users = self.query_map_many(query,
2728 &[&v[0],
2729 &format!("%{}%", v[0]),
2730 &format!("{}/%", v[0]),
2731 &v[1],
2732 &format!("%{}%", v[1]),
2733 &format!("{}/%", v[1])],
2734 |row| {
2735 (row.get(0),
2736 row.get(1),
2737 row.get(2),
2738 row.get(3),
2739 row.get(4),
2740 row.get(5))
2741 })
2742 .unwrap()
2743 }
2744 }
2745 }
2746 }
2747
2748 (users, groups)
2749 }
2750
2751 fn remove_old_users_and_groups(&self, maxstudentage: time::Timespec, maxteacherage: Option<time::Timespec>,
2753 maxage: Option<time::Timespec>)
2754 -> Result<(i64, i64, i64, i64), ()> {
2755 let query = "SELECT managed_by
2757 FROM session
2758 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
2759 AND ((last_login < ?1 AND last_activity < ?1)
2760 OR (last_login < ?1 AND last_activity IS NULL)
2761 OR (last_login IS NULL AND last_activity < ?1)
2762 OR (last_login IS NULL AND last_activity IS NULL AND account_created < ?1))";
2763 let mut groups: Vec<i64> = self.query_map_many(query, &[&maxstudentage], |row| row.get(0)).unwrap();
2764
2765 let query = "DELETE
2767 FROM session
2768 WHERE username IS NULL AND password IS NULL AND oauth_foreign_id IS NULL AND oauth_provider IS NULL
2769 AND ((last_login < ?1 AND last_activity < ?1)
2770 OR (last_login < ?1 AND last_activity IS NULL)
2771 OR (last_login IS NULL AND last_activity < ?1)
2772 OR (last_login IS NULL AND last_activity IS NULL AND account_created < ?1))";
2773 self.execute(query, &[&maxstudentage]).unwrap();
2774
2775 let n_users = groups.len() as i64;
2777 let mut n_groups: i64 = 0;
2778 let mut n_teachers: i64 = 0;
2779 let mut n_other: i64 = 0;
2780
2781 groups.sort_unstable();
2783 groups.dedup();
2784
2785 let query = "SELECT count(*)
2787 FROM session
2788 WHERE managed_by = ?1;";
2789 for group in groups {
2790 let groupsize: i64 = self.query_map_one(query, &[&group], |row| row.get(0)).unwrap().unwrap();
2791
2792 if groupsize == 0 {
2793 let query = "DELETE
2794 FROM usergroup
2795 WHERE id = ?1";
2796 self.execute(query, &[&group]).unwrap();
2797
2798 n_groups += 1;
2799 }
2800 }
2801
2802 let query = "SELECT id
2804 FROM usergroup
2805 WHERE group_created < ?1";
2806 let groups: Vec<i64> = self.query_map_many(query, &[&maxstudentage], |row| row.get(0)).unwrap();
2807 let query = "SELECT count(*)
2808 FROM session
2809 WHERE managed_by = ?1;";
2810 for group in groups {
2811 let groupsize: i64 = self.query_map_one(query, &[&group], |row| row.get(0)).unwrap().unwrap();
2812
2813 if groupsize == 0 {
2814 let query = "DELETE
2815 FROM usergroup
2816 WHERE id = ?1";
2817 self.execute(query, &[&group]).unwrap();
2818
2819 n_groups += 1;
2820 }
2821 }
2822
2823 let query = "SELECT id
2825 FROM session
2826 WHERE is_teacher = ?1
2827 AND ((last_login < ?2 AND last_activity < ?2)
2828 OR (last_login < ?2 AND last_activity IS NULL)
2829 OR (last_login IS NULL AND last_activity < ?2)
2830 OR (last_login IS NULL AND last_activity IS NULL AND account_created < ?2))";
2831 if let Some(maxteacherage) = maxteacherage {
2832 let teachers: Vec<i64> = self.query_map_many(query, &[&true, &maxteacherage], |row| row.get(0)).unwrap();
2833
2834 let query = "SELECT count(*)
2836 FROM usergroup_admin
2837 WHERE session = ?1;";
2838 for teacher in teachers {
2839 let groupcount: i64 = self.query_map_one(query, &[&teacher], |row| row.get(0)).unwrap().unwrap();
2840
2841 if groupcount == 0 {
2842 let query = "DELETE
2843 FROM session
2844 WHERE id = ?1";
2845 self.execute(query, &[&teacher]).unwrap();
2846
2847 n_teachers += 1;
2848 }
2849 }
2850 }
2851
2852 if let Some(maxage) = maxage {
2854 let query = "SELECT count(*)
2855 FROM session
2856 WHERE ((last_login < ?1 AND last_activity < ?1)
2857 OR (last_login < ?1 AND last_activity IS NULL)
2858 OR (last_login IS NULL AND last_activity < ?1)
2859 OR (last_login IS NULL AND last_activity IS NULL AND account_created < ?1))";
2860 n_other = self.query_map_one(query, &[&maxage], |row| row.get::<_, i64>(0)).unwrap().unwrap();
2861
2862 let query = "DELETE
2863 FROM session
2864 WHERE ((last_login < ?1 AND last_activity < ?1)
2865 OR (last_login < ?1 AND last_activity IS NULL)
2866 OR (last_login IS NULL AND last_activity < ?1)
2867 OR (last_login IS NULL AND last_activity IS NULL AND account_created < ?1))";
2868 self.execute(query, &[&maxage]).unwrap();
2869 }
2870
2871 Ok((n_users, n_groups, n_teachers, n_other))
2872 }
2873
2874 fn count_temporary_sessions(&self, maxage: time::Timespec) -> i64 {
2875 let query = "SELECT count(*)
2876 FROM session
2877 WHERE (last_activity < ?1 OR last_activity IS NULL)
2878 AND logincode IS NULL
2879 AND password IS NULL
2880 AND oauth_foreign_id IS NULL";
2881 self.query_map_one(query, &[&maxage], |row| row.get::<_, i64>(0)).unwrap().unwrap()
2882 }
2883
2884 fn remove_temporary_sessions(&self, maxage: time::Timespec, limit: Option<i64>) {
2885 if let Some(limit) = limit {
2890 let query = "DELETE
2891 FROM session
2892 WHERE id IN (SELECT id
2893 FROM session
2894 WHERE (last_activity < ?1 OR last_activity IS NULL)
2895 AND logincode IS NULL
2896 AND password IS NULL
2897 AND oauth_foreign_id IS NULL
2898 ORDER BY last_activity
2899 LIMIT ?2)";
2900 self.execute(query, &[&maxage, &limit]).unwrap();
2901 } else {
2902 let query = "DELETE
2903 FROM session
2904 WHERE (last_activity < ?1 OR last_activity IS NULL)
2905 AND logincode IS NULL
2906 AND password IS NULL
2907 AND oauth_foreign_id IS NULL";
2908 self.execute(query, &[&maxage]).unwrap();
2909 }
2910 }
2911
2912 fn remove_autosaved_submissions(&self, maxage: time::Timespec, limit: Option<i64>) {
2913 if let Some(limit) = limit {
2914 let query = "DELETE
2915 FROM submission
2916 WHERE id IN (SELECT id
2917 FROM submission
2918 WHERE date < ?1
2919 AND autosave = ?2
2920 ORDER BY date
2921 LIMIT ?3)";
2922 self.execute(query, &[&maxage, &true, &limit]).unwrap();
2923 } else {
2924 let query = "DELETE
2925 FROM submission
2926 WHERE date < ?1
2927 AND autosave = ?2";
2928 self.execute(query, &[&maxage, &true]).unwrap();
2929 }
2930 }
2931
2932 fn remove_all_but_latest_submissions(&self, maxage: time::Timespec, limit: Option<i64>) {
2933 if let Some(limit) = limit {
2934 let query = "DELETE
2935 FROM submission
2936 WHERE id IN (SELECT id
2937 FROM submission
2938 WHERE date < ?1
2939 AND higher_grade = ?2
2940 AND latest = ?2
2941 AND highest_grade_latest = ?2
2942 ORDER BY date
2943 LIMIT ?3)";
2944 self.execute(query, &[&maxage, &false, &limit]).unwrap();
2945 } else {
2946 let query = "DELETE
2947 FROM submission
2948 WHERE date < ?1
2949 AND higher_grade = ?2
2950 AND latest = ?2
2951 AND highest_grade_latest = ?2";
2952 self.execute(query, &[&maxage, &false]).unwrap();
2953 }
2954 }
2955
2956 fn move_task_location(&self, old_location: &str, new_location: &str, contest: &str) -> Option<()> {
2957 use std::path::Path;
2958 let contest_path = Path::new(contest);
2959 let contest_location = format!("{}/", contest_path.parent().unwrap().to_str()?);
2960 let contest_filename = contest_path.file_name().to_owned()?.to_str()?;
2961
2962 let query = "SELECT contest.id, taskgroup.id, task.id
2963 FROM contest
2964 JOIN taskgroup ON taskgroup.contest = contest.id
2965 JOIN task ON task.taskgroup = taskgroup.id
2966 WHERE task.location = ?1
2967 AND contest.location LIKE ?2
2968 AND contest.filename = ?3";
2969
2970 let old_task: (i64, i64, i64) =
2971 self.query_map_one(query, &[&old_location, &format!("%{}", contest_location), &contest_filename], |row| {
2972 (row.get(0), row.get(1), row.get(2))
2973 })
2974 .unwrap()?;
2975
2976 let query = "UPDATE task
2977 SET location = ?2
2978 WHERE id = ?1";
2979
2980 self.execute(query, &[&old_task.2, &new_location]).unwrap();
2981
2982 Some(())
2983 }
2984
2985 fn move_task_contest(&self, old_location: &str, new_location: &str, old_contest: &str, new_contest: &str)
2986 -> Option<()> {
2987 use std::path::Path;
2988 let old_contest_path = Path::new(old_contest);
2989 let old_contest_location = format!("{}/", old_contest_path.parent().unwrap().to_str()?);
2990 let old_contest_filename = old_contest_path.file_name().to_owned()?.to_str()?;
2991
2992 let new_contest_path = Path::new(new_contest);
2993 let new_contest_location = format!("{}/", new_contest_path.parent().unwrap().to_str()?);
2994 let new_contest_filename = new_contest_path.file_name().to_owned()?.to_str()?;
2995
2996 let query = "SELECT contest.id, taskgroup.id, task.id
2997 FROM contest
2998 JOIN taskgroup ON taskgroup.contest = contest.id
2999 JOIN task ON task.taskgroup = taskgroup.id
3000 WHERE task.location = ?1
3001 AND contest.location LIKE ?2
3002 AND contest.filename = ?3";
3003
3004 let old_task: (i64, i64, i64) =
3005 self.query_map_one(query,
3006 &[&old_location, &format!("%{}", old_contest_location), &old_contest_filename],
3007 |row| (row.get(0), row.get(1), row.get(2)))
3008 .unwrap()?;
3009
3010 let query = "SELECT contest.id, contest.location
3011 FROM contest
3012 WHERE contest.location LIKE ?1
3013 AND contest.filename = ?2";
3014
3015 let new_contest: (i64, String) = self.query_map_one(query,
3016 &[&format!("%{}", new_contest_location),
3017 &new_contest_filename],
3018 |row| (row.get(0), row.get(1)))
3019 .unwrap()?;
3020
3021 let query = "UPDATE task
3022 SET location = ?2
3023 WHERE id = ?1";
3024
3025 self.execute(query, &[&old_task.2, &new_location]).unwrap();
3026
3027 let query = "UPDATE taskgroup
3028 SET contest = ?2
3029 WHERE id = ?1";
3030
3031 self.execute(query, &[&old_task.1, &new_contest.0]).unwrap();
3032
3033 Some(())
3034 }
3035
3036 fn move_contest_location(&self, old_location: &str, new_location: &str) -> Option<()> {
3037 use std::path::Path;
3038 let old_contest_path = Path::new(old_location);
3039 let old_contest_location = format!("{}/", old_contest_path.parent().unwrap().to_str()?);
3040 let old_contest_filename = old_contest_path.file_name().to_owned()?.to_str()?;
3041
3042 let new_contest_path = Path::new(new_location);
3043 let new_contest_location = format!("{}/", new_contest_path.parent().unwrap().to_str()?);
3044 let new_contest_filename = new_contest_path.file_name().to_owned()?.to_str()?;
3045
3046 let query = "SELECT contest.id, contest.location
3047 FROM contest
3048 WHERE contest.location LIKE ?1
3049 AND contest.filename = ?2";
3050
3051 let old_contest: (i64, String) = self.query_map_one(query,
3052 &[&format!("%{}", old_contest_location),
3053 &old_contest_filename],
3054 |row| (row.get(0), row.get(1)))
3055 .unwrap()?;
3056
3057 let prefix = old_contest.1.strip_suffix(&old_contest_location).unwrap();
3058
3059 let real_new_contest_location = format!("{}{}", prefix, new_contest_location);
3060
3061 let query = "UPDATE contest
3062 SET location = ?2,
3063 filename = ?3
3064 WHERE id = ?1";
3065
3066 self.execute(query, &[&old_contest.0, &real_new_contest_location, &new_contest_filename]).unwrap();
3067
3068 Some(())
3069 }
3070
3071 fn move_task_submissions_grades(&self, old_location: &str, new_location: &str, old_contest: &str,
3072 new_contest: &str)
3073 -> Option<()> {
3074 use std::path::Path;
3075 let old_contest_path = Path::new(old_contest);
3076 let old_contest_location = format!("{}/", old_contest_path.parent().unwrap().to_str()?);
3077 let old_contest_filename = old_contest_path.file_name().to_owned()?.to_str()?;
3078
3079 let new_contest_path = Path::new(new_contest);
3080 let new_contest_location = format!("{}/", new_contest_path.parent().unwrap().to_str()?);
3081 let new_contest_filename = new_contest_path.file_name().to_owned()?.to_str()?;
3082
3083 let query = "SELECT contest.id, taskgroup.id, task.id
3084 FROM contest
3085 JOIN taskgroup ON taskgroup.contest = contest.id
3086 JOIN task ON task.taskgroup = taskgroup.id
3087 WHERE task.location = ?1
3088 AND contest.location LIKE ?2
3089 AND contest.filename = ?3";
3090
3091 let old_task: (i64, i64, i64) =
3092 self.query_map_one(query,
3093 &[&old_location, &format!("%{}", old_contest_location), &old_contest_filename],
3094 |row| (row.get(0), row.get(1), row.get(2)))
3095 .unwrap()?;
3096
3097 let new_task: (i64, i64, i64) =
3098 self.query_map_one(query,
3099 &[&new_location, &format!("%{}", new_contest_location), &new_contest_filename],
3100 |row| (row.get(0), row.get(1), row.get(2)))
3101 .unwrap()?;
3102
3103 let query = "WITH cte AS (
3104 SELECT ?2 AS taskgroup, oldgrade.session, GREATEST(oldgrade.grade, newgrade.grade) AS grade, oldgrade.validated
3105 FROM grade AS oldgrade
3106 LEFT JOIN grade AS newgrade ON oldgrade.session = newgrade.session AND newgrade.taskgroup = ?2
3107 WHERE oldgrade.taskgroup = ?1
3108 )
3109 INSERT INTO grade (taskgroup, session, grade, validated)
3110 SELECT * FROM CTE
3111 ON CONFLICT (taskgroup, session)
3112 DO UPDATE SET grade = excluded.grade, validated = excluded.validated;";
3113
3114 self.execute(query, &[&old_task.1, &new_task.1]).unwrap();
3115
3116 let query = "DELETE
3117 FROM grade
3118 WHERE taskgroup = ?1;";
3119
3120 self.execute(query, &[&old_task.1]).unwrap();
3121
3122 let limitquery = "SELECT id
3123 FROM submission
3124 WHERE task = ?1
3125 LIMIT 1;";
3126
3127 let query = "WITH cte AS (
3128 SELECT id
3129 FROM submission
3130 WHERE task = ?1
3131 LIMIT 10000
3132 )
3133 UPDATE submission s
3134 SET task = ?2
3135 FROM cte
3136 WHERE s.id = cte.id;";
3137
3138 while self.query_map_one(limitquery, &[&old_task.2], |_| ()).unwrap().is_some() {
3139 self.execute(query, &[&old_task.2, &new_task.2]).unwrap();
3140 }
3141
3142 Some(())
3143 }
3144
3145 fn get_debug_information(&self) -> String {
3146 let now = time::get_time();
3147 let cache_key = "dbstatus";
3148
3149 let query = "SELECT value, date
3150 FROM string_cache
3151 WHERE key = ?1";
3152
3153 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() {
3155 if cache_date.sec / 60 >= now.sec / 60 {
3157 return cached_value;
3158 }
3159 true
3160 } else {
3161 false
3162 };
3163
3164 let duration = Duration::minutes(60);
3165 let then = now - duration;
3166
3167 let query = "SELECT count(*)
3169 FROM session
3170 WHERE last_activity > ?1;";
3171 let n_asession: i64 = self.query_map_one(query, &[&then], |row| row.get(0)).unwrap().unwrap();
3172
3173 let query = "SELECT count(*)
3175 FROM participation
3176 WHERE start_date > ?1;";
3177 let n_apart: i64 = self.query_map_one(query, &[&then], |row| row.get(0)).unwrap().unwrap();
3178
3179 let query = "SELECT count(*)
3181 FROM session;";
3182 let n_session: i64 = self.query_map_one(query, &[], |row| row.get(0)).unwrap().unwrap();
3183
3184 let query = "SELECT count(*)
3186 FROM session
3187 WHERE oauth_foreign_id IS NOT NULL OR logincode IS NOT NULL;";
3188 let n_user: i64 = self.query_map_one(query, &[], |row| row.get(0)).unwrap().unwrap();
3189
3190 let query = "SELECT count(*)
3192 FROM session
3193 WHERE oauth_foreign_id IS NOT NULL;";
3194 let n_pmsuser: i64 = self.query_map_one(query, &[], |row| row.get(0)).unwrap().unwrap();
3195
3196 let query = "SELECT count(*)
3198 FROM session
3199 WHERE is_teacher = ?1;";
3200 let n_teacher: i64 = self.query_map_one(query, &[&true], |row| row.get(0)).unwrap().unwrap();
3201
3202 let query = "SELECT count(*)
3204 FROM participation;";
3205 let n_part: i64 = self.query_map_one(query, &[], |row| row.get(0)).unwrap().unwrap();
3206
3207 let n_sub: i64 = 0; let query = "SELECT contest, count(*)
3215 FROM participation
3216 GROUP BY contest
3217 ORDER BY contest DESC;";
3218 let n_participations_by_id: Vec<(i64, i64)> =
3219 self.query_map_many(query, &[], |row| (row.get(0), row.get(1))).unwrap();
3220
3221 let result = format!(
3222 "{{
3223 \"timestamp\": {},
3224 \"active_sessions\": {},
3225 \"active_participations\": {},
3226 \"sessions\": {},
3227 \"users\": {},
3228 \"pms_users\": {},
3229 \"teachers\": {},
3230 \"participations\": {},
3231 \"submissions\": {},
3232 \"participations_by_contest_id\": {{
3233 {}
3234 }}
3235}}
3236",
3237 now.sec,
3238 n_asession,
3239 n_apart,
3240 n_session,
3241 n_user,
3242 n_pmsuser,
3243 n_teacher,
3244 n_part,
3245 n_sub,
3246 n_participations_by_id.iter()
3247 .map(|(x, y)| -> String { format!("\"{}\": {}", x, y) })
3248 .collect::<Vec<String>>()
3249 .join(",\n ")
3250 );
3251
3252 let query = if db_has_value {
3253 "UPDATE string_cache
3254 SET value = ?2, date = ?3
3255 WHERE key = ?1"
3256 } else {
3257 "INSERT INTO string_cache (key, value, date)
3258 VALUES (?1, ?2, ?3)"
3259 };
3260 self.execute(query, &[&cache_key, &result, &now]).unwrap();
3261
3262 result
3263 }
3264
3265 fn reset_all_contest_visibilities(&self) { self.execute("UPDATE contest SET public = ?1", &[&false]).unwrap(); }
3266 fn reset_all_taskgroup_visibilities(&self) { self.execute("UPDATE taskgroup SET active = ?1", &[&false]).unwrap(); }
3267}
3268
3269fn years_past_august1(last_login: Option<time::Timespec>, now: time::Timespec) -> i32 {
3270 if let Some(last_login) = last_login {
3271 let last_login_cal = time::at_utc(last_login);
3272 let now_cal = time::at_utc(now);
3273
3274 let year_now = now_cal.tm_year + if now_cal.tm_mon >= 7 { 1 } else { 0 };
3276 let year_then = last_login_cal.tm_year + if last_login_cal.tm_mon >= 7 { 1 } else { 0 };
3277
3278 year_now - year_then
3279 } else {
3280 0
3281 }
3282}