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