-
Notifications
You must be signed in to change notification settings - Fork 0
/
create.sql
7470 lines (7373 loc) · 330 KB
/
create.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
DROP SCHEMA IF EXISTS tanuki CASCADE;
CREATE SCHEMA IF NOT EXISTS tanuki;
CREATE TABLE tanuki.contests (
name varchar(64) ,
short_name varchar(16) UNIQUE NOT NULL ,
is_active boolean NOT NULL ,
id serial PRIMARY KEY
);
CREATE TABLE tanuki.languages (
name varchar(50) NOT NULL ,
id serial PRIMARY KEY
);
CREATE TABLE tanuki.permissions_for_contests (
name varchar(64) UNIQUE NOT NULL ,
description varchar(256) ,
id serial PRIMARY KEY
);
CREATE TABLE tanuki.scoring_methods (
name varchar(32) UNIQUE NOT NULL ,
id serial PRIMARY KEY
);
CREATE TABLE tanuki.statuses (
name char(5) NOT NULL ,
description varchar(128) ,
priority integer NOT NULL ,
id serial PRIMARY KEY
);
CREATE TABLE tanuki.users (
login varchar(32) NOT NULL UNIQUE ,
name varchar(64) NOT NULL ,
surname varchar(64) NOT NULL ,
password_hash varchar(256) NOT NULL ,
email varchar(64) NOT NULL ,
id serial PRIMARY KEY
);
CREATE TABLE tanuki.administrators (
user_id integer PRIMARY KEY REFERENCES tanuki.users
);
CREATE TABLE tanuki.contest_permissions (
user_id integer NOT NULL REFERENCES tanuki.users ,
permission_id integer NOT NULL REFERENCES tanuki.permissions_for_contests ,
contest_id integer NOT NULL REFERENCES tanuki.contests,
UNIQUE ( user_id, permission_id, contest_id )
);
CREATE TABLE tanuki.extensions (
extension char(8) NOT NULL ,
language_id integer NOT NULL REFERENCES tanuki.languages ,
UNIQUE ( extension, language_id )
);
CREATE TABLE tanuki.problems (
name varchar(64) NOT NULL ,
short_name varchar(16) NOT NULL ,
contest_id integer NOT NULL REFERENCES tanuki.contests ,
statement_uri varchar(256) NOT NULL ,
uses_points boolean NOT NULL ,
points numeric ,
due_date timestamptz NOT NULL,
closing_date timestamptz NOT NULL,
published boolean NOT NULL ,
scoring_method integer REFERENCES tanuki.scoring_methods ,
source_limit integer ,
id serial PRIMARY KEY,
UNIQUE (contest_id, short_name),
CHECK ( due_date <= closing_date )
);
CREATE TABLE tanuki.submits (
source_uri char(128) NOT NULL ,
user_id integer NOT NULL REFERENCES tanuki.users ,
problem_id integer NOT NULL REFERENCES tanuki.problems ,
language_id integer REFERENCES tanuki.languages,
submission_time timestamptz NOT NULL ,
id serial PRIMARY KEY
);
CREATE TABLE tanuki.task_groups (
problem_id integer NOT NULL REFERENCES tanuki.problems ,
name varchar(32) NOT NULL ,
requires_all_ok boolean NOT NULL ,
id serial PRIMARY KEY
);
CREATE TABLE tanuki.tasks (
task_group integer NOT NULL REFERENCES tanuki.task_groups ,
name varchar(32) NOT NULL ,
test_uri varchar(256) NOT NULL ,
points numeric NOT NULL ,
time_limit numeric NOT NULL ,
memory_limit integer NOT NULL ,
show_output boolean NOT NULL ,
id serial PRIMARY KEY
);
CREATE TABLE tanuki.problem_languages (
problem_id integer NOT NULL REFERENCES tanuki.problems ,
language_id integer NOT NULL REFERENCES tanuki.languages,
UNIQUE ( problem_id, language_id )
);
CREATE TABLE tanuki.submit_results (
submit_id integer PRIMARY KEY REFERENCES tanuki.submits,
score numeric NOT NULL ,
status integer NOT NULL REFERENCES tanuki.statuses
);
CREATE TABLE tanuki.task_results (
submit_id integer NOT NULL REFERENCES tanuki.submits ,
task_id integer NOT NULL REFERENCES tanuki.tasks ,
status_id integer NOT NULL REFERENCES tanuki.statuses ,
points numeric NOT NULL ,
summary varchar,
execution_time numeric(6,3) NOT NULL ,
used_memory integer ,
PRIMARY KEY ( submit_id, task_id ),
CHECK ( points >= 0 ),
CHECK ( used_memory >= 0 ),
CHECK ( execution_time >= 0 )
);
COMMENT ON COLUMN tanuki.contests.short_name IS 'abbreviated name of the contest';
COMMENT ON COLUMN tanuki.statuses.priority IS 'in case of multiple statuses in group the one with highest priority is displayed';
COMMENT ON COLUMN tanuki.problems.short_name IS 'abbreviated name of the problem';
COMMENT ON COLUMN tanuki.problems.statement_uri IS 'link to statement that is rendered on the problem page';
COMMENT ON COLUMN tanuki.problems.source_limit IS 'Limits the size of submitted file (in bytes).';
COMMENT ON COLUMN tanuki.submits.source_uri IS 'link to submitted source file';
COMMENT ON COLUMN tanuki.task_groups.requires_all_ok IS 'has every task to be completed for group to have points';
COMMENT ON COLUMN tanuki.tasks.test_uri IS 'link to test that is invoked on submit';
COMMENT ON COLUMN tanuki.tasks.time_limit IS 'time limit in seconds';
COMMENT ON COLUMN tanuki.tasks.memory_limit IS 'memory limit (in bytes)';
COMMENT ON COLUMN tanuki.task_results.summary IS 'additional description of the results e.g. expected output';
COMMENT ON COLUMN tanuki.task_results.execution_time IS 'execution time in seconds';
COMMENT ON COLUMN tanuki.task_results.used_memory IS 'used memory (in bytes)';
SET search_path TO tanuki;
-- A custom comparator, tasked with comparing results in a way that prioritizes status that's NOT OK (and then sorting by timestamp)
CREATE OR REPLACE FUNCTION compare_submits(result INTEGER, submission_time TIMESTAMPTZ)
RETURNS TIMESTAMPTZ AS
$$
BEGIN
-- 4 is id of OK
IF result = 4 THEN
RETURN TIMESTAMPTZ 'infinity';
END IF;
RETURN submission_time;
END;
$$
LANGUAGE plpgsql;-- For each submit retrieves basic information about it
CREATE OR REPLACE VIEW all_submits AS
SELECT
s.id,
s.problem_id,
(
SELECT sr.status
FROM submit_results sr
WHERE sr.submit_id = s.id
)
AS result,
s.user_id,
s.submission_time
FROM
submits s;
--A view that for each (user, problem) pair shows the newest submit (or a submit with an OK status)
CREATE VIEW newest_submits_in_problems AS
SELECT
a1.user_id,
a1.problem_id,
a1.id,
result
FROM all_submits a1
WHERE
a1.id = (
SELECT a2.id
FROM all_submits a2
WHERE a2.user_id = a1.user_id AND a2.problem_id = a1.problem_id
ORDER BY compare_submits(a2.result, a2.submission_time) DESC
LIMIT 1
);
-- For each user find contests they can access
CREATE OR REPLACE VIEW user_contests AS
SELECT
DISTINCT
u.id "user_id",
c.contest_id
FROM
users u
JOIN contest_permissions c ON u.id = c.user_id;
-- Find contests where user is a teacher i.e. has management permissions
CREATE OR REPLACE VIEW teacher_contests AS
SELECT
DISTINCT
u.id "user_id",
c.contest_id
FROM
users u
JOIN contest_permissions c ON u.id = c.user_id
WHERE
c.permission_id = 1; -- MANAGE
-- Find contests where user is a student i.e. has submission permissions
CREATE OR REPLACE VIEW student_contests AS
SELECT
DISTINCT
u.id "user_id",
c.contest_id
FROM
users u
JOIN contest_permissions c ON u.id = c.user_id
WHERE
c.permission_id = 2; -- SUBMIT--Triggers that are responsible for deleting things without causing any errors related to foreign keys
CREATE OR REPLACE FUNCTION delete_user() RETURNS trigger AS $$
BEGIN
DELETE FROM contest_permissions WHERE user_id = OLD.id;
DELETE
FROM task_results
WHERE submit_id IN (SELECT s.id FROM submits s WHERE s.user_id = OLD.id);
DELETE
FROM submit_results
WHERE submit_id IN (SELECT s.id FROM submits s WHERE s.user_id = OLD.id);
DELETE FROM submits WHERE user_id = OLD.id;
DELETE FROM administrators WHERE user_id = OLD.id;
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION delete_contest() RETURNS trigger AS $$
BEGIN
DELETE FROM contest_permissions WHERE contest_id = OLD.id;
DELETE FROM problems WHERE contest_id = OLD.id;
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION delete_problem() RETURNS trigger AS $$
BEGIN
DELETE FROM problem_languages WHERE problem_id = OLD.id;
DELETE FROM submits WHERE problem_id = OLD.id;
DELETE FROM task_groups WHERE problem_id = OLD.id;
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION delete_submit() RETURNS trigger AS $$
BEGIN
DELETE FROM submit_results WHERE submit_id = OLD.id;
DELETE FROM task_results WHERE submit_id = OLD.id;
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION delete_task_group() RETURNS trigger AS $$
BEGIN
DELETE FROM tasks WHERE task_group = OLD.id;
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER delete_user
BEFORE DELETE ON users FOR EACH ROW EXECUTE PROCEDURE delete_user();
CREATE TRIGGER delete_contest
BEFORE DELETE ON contests FOR EACH ROW EXECUTE PROCEDURE delete_contest();
CREATE TRIGGER delete_problem
BEFORE DELETE ON problems FOR EACH ROW EXECUTE PROCEDURE delete_problem();
CREATE TRIGGER delete_submit
BEFORE DELETE ON submits FOR EACH ROW EXECUTE PROCEDURE delete_submit();
CREATE TRIGGER delete_task_group
BEFORE DELETE ON task_groups FOR EACH ROW EXECUTE PROCEDURE delete_task_group();-- Finds status_id of given group for given submit
-- Essentially it retrieves status with the highest priority from results of all tasks that belong to this group
CREATE OR REPLACE FUNCTION task_group_status(submit_id INT, task_group_id INT)
RETURNS INT AS
$$
SELECT
tr.status_id
FROM
tasks t
LEFT JOIN (SELECT * FROM task_results tr WHERE tr.submit_id = $1) tr ON (tr.task_id = t.id)
LEFT JOIN statuses s ON (tr.status_id = s.id)
WHERE t.task_group = $2
ORDER BY s.priority DESC NULLS FIRST
LIMIT 1;
$$
LANGUAGE SQL;
CREATE OR REPLACE FUNCTION task_group_points(submit_id INT, task_group_id INT)
RETURNS NUMERIC AS $$
SELECT
CASE
WHEN (SELECT requires_all_ok FROM task_groups WHERE id = $2) AND task_group_status($1, $2) != 4 THEN 0
ELSE
(
SELECT SUM(
CASE
WHEN tr.status_id IS NULL THEN NULL
WHEN tr.status_id = 4 THEN t.points -- OK
ELSE 0
END
)
FROM tasks t
LEFT JOIN (SELECT * FROM task_results tr WHERE tr.submit_id = $1) tr ON (tr.task_id = t.id)
WHERE t.task_group = $2
)
END
$$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION submit_status(submit_id INT)
RETURNS INT AS $$
WITH tg_statuses AS (
SELECT tg.id AS "id", task_group_status($1, tg.id) AS "status"
FROM submits s
JOIN task_groups tg USING(problem_id)
WHERE s.id = $1
)
SELECT tgs.status
FROM tg_statuses tgs
JOIN statuses s ON(tgs.status = s.id)
ORDER BY s.priority DESC NULLS FIRST
LIMIT 1;
$$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION submit_points(submit_id INT)
RETURNS NUMERIC AS $$
SELECT SUM (
CASE
WHEN task_group_status($1, tg.id) IS NULL THEN NULL
ELSE task_group_points($1, tg.id)
END
)
FROM submits s
JOIN task_groups tg USING(problem_id)
WHERE s.id = $1
$$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION max_task_group_points(group_id INT)
RETURNS NUMERIC AS $$
SELECT SUM(points)
FROM tasks t
JOIN task_groups tg ON (t.task_group = tg.id)
WHERE tg.id = $1
$$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION max_points(problem_id INT)
RETURNS NUMERIC AS $$
SELECT SUM(max_task_group_points(tg.id))
FROM
task_groups tg
WHERE tg.problem_id = $1;
$$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION score_multiplier(due_date timestamptz, closing_date timestamptz, submit_time timestamptz, scoring_method INT)
RETURNS NUMERIC AS $$
SELECT CASE scoring_method
WHEN 1 THEN CASE -- ZERO
WHEN submit_time < due_date THEN 1.0::numeric
ELSE 0::numeric
END
WHEN 2 THEN CASE -- LINEAR_TO_ZERO
WHEN submit_time < due_date THEN 1.0::numeric
WHEN submit_time < closing_date THEN (EXTRACT(EPOCH FROM (closing_date - submit_time)) / EXTRACT(EPOCH FROM (closing_date - due_date)))::numeric
ELSE 0::numeric
END
WHEN 3 THEN CASE -- LINEAR_TO_NEGATIVE
WHEN submit_time < due_date THEN 1.0::numeric
ELSE (EXTRACT(EPOCH FROM (closing_date - submit_time)) / EXTRACT(EPOCH FROM (closing_date - due_date)))::numeric
END
END
$$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION submit_score(submit_id INT)
RETURNS NUMERIC AS $$
SELECT
CASE p.uses_points
WHEN true THEN p.points * submit_points(s.id) / max_points(p.id) * score_multiplier(p.due_date, p.closing_date, s.submission_time, p.scoring_method)
ELSE CASE submit_status(submit_id)
WHEN 4 THEN p.points -- 4 is OK
ELSE 0
END
END
FROM submits s JOIN problems p ON (p.id = s.problem_id)
WHERE s.id = $1;
$$ LANGUAGE SQL;
CREATE OR REPLACE VIEW task_scores AS
SELECT
tr.submit_id, tr.task_id, tr.status_id, tr.points AS points,
tr.summary, tr.execution_time, tr.used_memory,
t.task_group, t.name, t.test_uri, t.points AS max_points,
t.memory_limit, t.show_output
FROM task_results tr
JOIN tasks t ON tr.task_id = t.id;
-- Information needed to display a single row representing the submit
-- e.g. in the table of all submits
CREATE OR REPLACE VIEW short_submit_results AS
SELECT
s.id,
s.problem_id,
p.short_name "short_problem_name",
p.contest_id,
c.short_name "short_contest_name",
s.user_id,
CONCAT(u.name, ' ', u.surname) "user_name",
submission_time,
statuses.id "status_id",
statuses.name "status_name",
submit_points(s.id) "points",
max_points(s.problem_id) "max_points",
sr.score
FROM
submits s
LEFT JOIN submit_results sr ON s.id = sr.submit_id
LEFT JOIN statuses ON statuses.id = sr.status
JOIN problems p ON s.problem_id = p.id
JOIN contests c ON p.contest_id = c.id
JOIN users u ON s.user_id = u.id
ORDER BY
submission_time DESC;
-- Information for rendering an invididual submit
-- In addition to all short information we also need the language and path to the source file
CREATE OR REPLACE VIEW full_submit_results AS
SELECT
short.*,
l.name "language_name",
s.source_uri
FROM
short_submit_results short
JOIN submits s ON short.id = s.id
JOIN languages l ON s.language_id = l.id;SET search_path TO tanuki;
COPY users (login, name, surname, password_hash, email) FROM STDIN;
jamesvaughan Wanda Mason $2b$12$oU2wDsn/XB3CHlxlSAY62OVcXeA/qQtb8Cd5xApvE512uhe1W7L32 [email protected]
dmatthews Monica Mason $2b$12$JQpF2GtA6fN8zk83cX60YeZyU95xYBgDLkIp.dHHG403ECX9G1gka [email protected]
macdonalddaniel Jennifer Reed $2b$12$GA.zI1UtRJWnavHS6Zm4guChwc7ghChghcdGn1FuVo3iZ/C4k7FKq [email protected]
floresadam Robert Logan $2b$12$qS50RnHDrZlr64G2iV0bD.UISNVnX1JJD/pdwya7ixIQ.NZBJFtOK [email protected]
kruegertonya Joseph Evans $2b$12$YuY/bC.hpiz6QyGzo6xD9.JMrJKtnYqfYqd9ij.LfqUXxQQsYKgfO [email protected]
nicholasvillanueva Anna Wood $2b$12$UnZrxtRf5aLPy1aljp5NQ.gc94a91ZW9sdLDYcNpksDy3F9eNYesq [email protected]
kblanchard Carrie Ware $2b$12$ETYmB3Vuk3uZDsgwf/VacuBfDnp51Bkb9ZDJ1vx6.VJN2RUAfWu3S [email protected]
jeffery11 Kevin Tapia $2b$12$ZDxtZFOA8fd5/wYVpWyWh.ciPqIwTZmjAGsShFjlhGo13eTcx1lT. [email protected]
justinbradford Cynthia Taylor $2b$12$lRXyIrIEAlJmiLocHM.pJeQ/9z./mWy5ZIPUblPJU0pYQW12PcFHO [email protected]
russellhowe Joanna Williams $2b$12$RCat2awc3v1RopvcV/GrKurW4TmI5BXg.7MRDS40rX6ZbLQ3flwBy [email protected]
admin A0 D $2b$12$DQYNGefKveE1sIzT/3KJu.DmBtqEBViIvDA4fpCRuXNLfUjEY1m22 [email protected]
admin1 A1 D $2b$12$/z5niQtC19/AkDsUvfMZve82UO737qqlvAY1ihNE93VgyZEr6pQzi [email protected]
admin2 A2 D $2b$12$6A0JitRjj802BeI8YthPruVwEcnpZPB3ehFDqEowwfkqvL0A5B7TO [email protected]
admin3 A3 D $2b$12$H48zhTxzqLO7FVblTpZuAOAnL3rd7UrZJnDCiyXxLex.K4xfjwisC [email protected]
admin4 A4 D $2b$12$/I1O3sllKEPllthQ/n62ouIrb1x7N3p9IFiQvy/TjQWvArLh80XBq [email protected]
\.
COPY administrators (user_id) FROM STDIN;
12
15
11
14
13
\.
COPY statuses (name, description, priority) FROM STDIN;
INT Internal error -1
REJ Manually rejected -1
ERR Checking error -1
OK Program works correctly 0
ANS Program produces wrong answer 1
TLE Time limit exceeded 2
MEM Memory limit exceeded 3
RTE Runtime error 4
CME Compilation failed 5
RUL Program is incompliant with rules 6
\.
COPY scoring_methods (name) FROM STDIN;
ZERO
LINEAR_TO_ZERO
LINEAR_TO_NEGATIVE
\.
COPY contests (name, short_name, is_active) FROM STDIN;
Indeed picture under case lead. Military. True
Energy collection security son. Third east. True
Part what once. Interesting. True
Spend point stop goal. Teach. True
Control reason despite attack. Executive. True
Author along other human. You hold. True
Answer management once. Staff. True
Public laugh result agent. Dark. True
Allow put watch structure. Forget. True
White home seven adult. Wife. True
\.
COPY permissions_for_contests (name, description) FROM STDIN;
MANAGE Allows user to edit contest and manage participants
SUBMIT Allows user to view problems and sending submissions
\.
COPY contest_permissions (user_id, permission_id, contest_id) FROM STDIN;
10 2 3
2 2 5
4 2 7
3 2 1
3 1 5
4 1 2
8 2 2
6 2 9
10 2 5
2 2 7
6 1 10
5 2 6
3 1 7
6 2 2
8 2 4
7 2 2
9 2 4
2 2 9
5 2 8
8 2 6
7 2 4
9 2 6
4 2 4
10 1 4
5 2 1
5 2 10
1 2 8
3 2 10
6 2 6
8 2 8
7 2 6
9 2 8
2 2 4
4 2 6
5 2 3
1 2 1
3 2 3
1 2 10
8 2 1
9 2 1
7 2 8
9 2 10
10 2 7
5 2 5
1 2 3
3 2 5
7 2 1
9 2 3
7 2 10
10 2 9
5 2 7
1 2 5
3 1 8
3 2 7
7 2 3
9 2 5
10 2 2
1 2 7
3 2 9
6 2 8
8 2 10
9 2 7
10 2 4
2 2 6
5 2 2
3 2 2
1 2 9
4 1 3
8 2 3
6 2 10
7 2 7
9 2 9
10 2 6
4 2 1
2 1 9
2 2 8
1 2 2
3 2 4
6 2 3
8 2 5
9 2 2
4 2 3
2 2 10
1 2 4
3 2 6
5 2 9
6 2 5
8 2 7
10 2 1
2 2 3
10 2 10
6 1 6
1 2 6
3 2 8
8 1 1
6 2 7
8 2 9
\.
COPY languages (name) FROM STDIN;
Python 2
Python 3
C++
C
Fortran
Java
Haskell
\.
COPY extensions (extension, language_id) FROM STDIN;
py 1
py 2
cpp 3
hpp 3
h 3
c 4
h 4
f 5
java 6
hs 7
\.
COPY problems (name, short_name, contest_id, statement_uri, uses_points, points, due_date, closing_date, published, scoring_method, source_limit) FROM STDIN;
Middle water how. A 1 /problems/dummy True 0.2 2021-08-21, 07:50:31 2021-09-01, 00:43:43 True 2 54272
Establish remain year. B 1 /problems/dummy False 0.3 2022-05-26, 15:45:44 2022-11-18, 08:23:19 False 1 72704
Year six agree significant. C 1 /problems/dummy True 0.1 2022-04-08, 14:13:20 2022-11-24, 12:00:34 True 3 84992
Still note pressure. D 1 /problems/dummy True 0.3 2021-08-03, 12:03:44 2021-10-01, 10:10:06 False 3 72704
Town trouble person song. E 1 /problems/dummy True 0.2 2021-06-27, 19:17:11 2022-08-13, 05:58:48 True 1 77824
Real will method morning lot. F 1 /problems/dummy False 0.4 2022-02-15, 04:03:03 2022-09-26, 23:40:19 True 1 51200
Finally at candidate area. G 1 /problems/dummy False 0.3 2022-02-08, 14:14:54 2023-05-25, 09:27:22 False 1 74752
Window education else option. H 1 /problems/dummy False 0.3 2022-11-24, 14:19:25 2023-04-18, 18:37:00 True 2 26624
Thought fly impact hospital. I 1 /problems/dummy False 0.3 2022-07-13, 11:25:01 2023-02-21, 22:10:05 True 3 76800
Might hear strong. A 2 /problems/dummy False 0.4 2021-10-23, 21:22:24 2021-12-17, 04:55:48 True 1 38912
Drug top continue such. B 2 /problems/dummy False 0.2 2021-06-14, 21:04:31 2022-03-15, 12:49:31 True 3 26624
Foot economy talk. A 3 /problems/dummy False 0.4 2021-10-06, 02:47:24 2022-10-08, 14:17:47 False 3 38912
Like Mr detail picture be. B 3 /problems/dummy False 0.3 2021-09-29, 13:48:56 2022-07-28, 12:17:14 True 2 59392
Since star employee wonder. C 3 /problems/dummy False 0.3 2022-09-11, 15:44:45 2022-09-24, 03:35:32 True 2 81920
Right worry out movie real. D 3 /problems/dummy False 0.4 2021-11-20, 19:49:04 2022-11-28, 04:38:20 True 3 33792
North cause relate education. E 3 /problems/dummy False 0.4 2021-09-07, 11:52:23 2023-01-09, 08:11:36 False 1 83968
Foreign left price. F 3 /problems/dummy False 0.1 2021-10-07, 18:39:53 2022-07-21, 23:50:47 True 3 65536
Down book grow sing across. G 3 /problems/dummy False 0.1 2021-11-05, 00:30:02 2022-06-07, 21:09:37 True 1 81920
See similar follow. H 3 /problems/dummy False 0.4 2021-07-01, 13:44:27 2022-09-28, 05:16:12 True 2 58368
Situation local I. I 3 /problems/dummy True 0.2 2022-09-16, 22:13:22 2022-12-21, 17:02:57 False 1 27648
Soon up look whether accept. A 4 /problems/dummy True 0.0 2021-06-09, 10:30:25 2021-12-01, 20:26:42 True 2 14336
Special no pass. B 4 /problems/dummy True 0.2 2022-05-19, 17:55:17 2022-07-19, 03:30:58 True 2 40960
Seven new because. C 4 /problems/dummy True 0.4 2021-12-25, 14:32:18 2022-10-27, 01:44:52 True 3 60416
Probably least adult laugh. D 4 /problems/dummy True 0.0 2021-11-24, 04:53:26 2023-04-06, 01:38:31 True 2 51200
Point training win act way. E 4 /problems/dummy True 0.1 2021-06-21, 08:54:41 2022-03-24, 21:25:01 True 1 9216
Peace style speech air. F 4 /problems/dummy True 0.4 2021-06-23, 13:37:03 2022-03-03, 06:46:05 False 2 11264
Away project agree. G 4 /problems/dummy False 0.0 2022-11-24, 23:17:34 2023-06-07, 00:11:34 True 2 33792
Couple out condition already. H 4 /problems/dummy False 0.4 2022-01-02, 02:21:04 2022-05-23, 12:47:46 True 1 30720
In stuff join something. I 4 /problems/dummy True 0.3 2022-02-18, 02:30:05 2023-05-24, 21:51:12 True 3 24576
Want that guess ten. J 4 /problems/dummy True 0.3 2021-10-17, 13:06:12 2022-03-30, 20:17:43 False 1 54272
Five left into herself. A 5 /problems/dummy True 0.1 2022-06-01, 09:56:05 2022-07-13, 15:49:09 True 3 81920
Least garden still. B 5 /problems/dummy False 0.0 2021-07-16, 14:48:55 2022-01-28, 15:47:24 True 1 79872
Clearly among white when. C 5 /problems/dummy True 0.2 2022-06-09, 11:05:03 2022-10-13, 03:29:21 True 2 92160
We name you money. A 6 /problems/dummy False 0.4 2021-07-18, 23:57:50 2022-01-25, 01:09:13 True 1 81920
Themselves determine reduce. B 6 /problems/dummy False 0.3 2021-06-09, 16:50:37 2021-08-11, 08:57:34 True 1 22528
With run team benefit bag. C 6 /problems/dummy True 0.1 2022-10-23, 03:50:12 2023-04-15, 11:58:02 True 2 54272
Personal writer position age. D 6 /problems/dummy True 0.2 2021-08-24, 15:32:36 2021-12-11, 17:28:09 True 2 10240
Lead front oil get. E 6 /problems/dummy False 0.0 2022-04-24, 22:01:17 2022-11-02, 16:31:29 False 1 54272
Always left sometimes. F 6 /problems/dummy False 0.2 2021-10-13, 04:10:52 2022-12-16, 10:51:15 True 2 102400
Other order maybe them. G 6 /problems/dummy True 0.0 2023-02-20, 07:02:59 2023-03-08, 03:27:45 False 3 50176
Son morning show. H 6 /problems/dummy True 0.1 2022-09-09, 12:20:51 2022-11-29, 19:16:14 False 2 29696
Early yeah issue cold maybe. I 6 /problems/dummy True 0.4 2022-09-29, 00:06:48 2022-11-27, 07:31:05 False 2 33792
Trouble they scene imagine. J 6 /problems/dummy False 0.1 2021-09-22, 10:29:20 2022-10-07, 06:38:57 True 1 80896
Majority make more. A 7 /problems/dummy True 0.2 2022-02-06, 06:55:14 2022-04-01, 06:27:21 False 3 40960
Marriage nor away protect. B 7 /problems/dummy False 0.4 2022-02-04, 14:11:52 2022-02-09, 18:40:31 False 3 21504
Time reason take sure easy. A 8 /problems/dummy True 0.3 2022-02-23, 01:31:03 2022-12-07, 14:58:25 True 3 57344
Letter cultural mind cover. B 8 /problems/dummy False 0.3 2021-08-07, 03:45:43 2022-11-24, 18:42:18 True 3 70656
Oil use win thus candidate. C 8 /problems/dummy True 0.1 2022-12-13, 08:54:07 2023-01-31, 11:21:01 True 3 29696
Popular history page dog. D 8 /problems/dummy True 0.4 2022-02-15, 20:12:34 2023-04-05, 10:39:55 False 1 44032
Including way night reduce. E 8 /problems/dummy False 0.4 2021-11-26, 17:44:57 2023-04-10, 01:20:32 True 2 13312
Want four body morning. F 8 /problems/dummy True 0.2 2022-03-30, 04:17:13 2022-08-25, 08:53:33 True 1 21504
Spring when recent. A 9 /problems/dummy False 0.0 2022-05-30, 00:12:43 2022-10-06, 09:14:41 False 1 86016
Night guess accept leg. B 9 /problems/dummy True 0.3 2022-08-25, 15:17:02 2022-09-08, 16:04:48 False 2 68608
May back play manage low way. C 9 /problems/dummy True 0.1 2021-07-15, 20:49:30 2022-05-27, 16:28:52 False 3 8192
Kid choice new serious hot. D 9 /problems/dummy False 0.3 2021-10-25, 18:04:26 2023-06-06, 09:42:06 True 1 49152
Bad adult partner show. E 9 /problems/dummy False 0.3 2021-08-14, 07:18:52 2022-10-02, 07:40:27 True 2 9216
Beyond morning others name. F 9 /problems/dummy True 0.0 2021-07-03, 12:53:29 2022-08-11, 06:30:09 True 1 99328
Hour why modern less. G 9 /problems/dummy False 0.2 2021-07-01, 05:23:55 2023-04-04, 19:14:10 True 1 34816
Region thought road. H 9 /problems/dummy False 0.2 2022-01-06, 07:23:52 2023-02-09, 13:03:39 False 2 63488
Build test stuff. I 9 /problems/dummy False 0.2 2021-12-17, 14:08:43 2023-01-15, 20:58:45 True 2 29696
Choice never analysis he. J 9 /problems/dummy True 0.4 2021-07-26, 09:11:42 2023-01-06, 23:14:59 True 2 52224
Media few there attack teach. A 10 /problems/dummy True 0.0 2021-09-17, 12:27:20 2022-06-25, 02:30:05 False 2 16384
Energy more whose. B 10 /problems/dummy False 0.1 2022-07-25, 11:16:42 2022-08-08, 18:44:54 True 3 36864
For ahead set realize. C 10 /problems/dummy False 0.2 2022-04-02, 01:41:59 2022-11-28, 12:27:35 True 1 67584
\.
COPY problem_languages (problem_id, language_id) FROM STDIN;
36 7
50 6
10 6
11 5
32 3
57 2
6 2
53 7
64 7
4 2
59 2
3 6
42 4
46 4
35 1
15 7
26 7
29 6
22 3
47 2
59 4
37 1
5 5
8 4
17 7
19 4
28 7
51 2
60 5
24 3
18 5
54 1
34 7
47 4
27 1
60 7
9 5
16 1
7 7
55 1
14 3
58 3
51 6
41 5
57 1
33 1
39 7
45 2
5 2
32 4
44 3
26 6
16 5
31 5
22 2
56 4
23 1
3 7
9 2
13 2
25 1
7 4
39 2
63 3
56 6
21 6
12 3
23 3
37 2
51 3
61 5
52 2
10 5
13 4
48 7
62 6
29 3
40 3
63 5
37 4
58 2
50 7
41 4
43 1
1 4
2 3
25 5
6 3
14 4
36 1
27 7
17 6
44 2
57 5
62 1
49 1
40 7
31 4
55 7
9 4
38 3
20 6
7 6
64 3
52 1
8 7
44 6
10 4
30 7
11 3
49 5
29 2
\.
COPY submits (source_uri, user_id, problem_id, language_id, submission_time) FROM STDIN;
/submitted/dummy 1 7 6 1995-03-29, 08:44:06
/submitted/dummy 1 45 2 2018-04-26, 13:33:36
/submitted/dummy 1 27 7 2020-04-11, 16:36:27
/submitted/dummy 1 53 7 2010-01-31, 18:17:39
/submitted/dummy 1 46 4 2011-12-27, 20:12:45
/submitted/dummy 1 55 1 2015-10-11, 03:59:51
/submitted/dummy 1 50 7 2017-04-27, 19:25:50
/submitted/dummy 1 39 2 2007-02-26, 20:16:59
/submitted/dummy 1 12 3 2014-05-17, 00:24:43
/submitted/dummy 1 28 7 2001-04-18, 15:00:34
/submitted/dummy 1 56 4 2021-03-03, 09:42:44
/submitted/dummy 1 10 6 1995-11-15, 05:12:57
/submitted/dummy 1 57 5 1998-03-22, 22:26:41
/submitted/dummy 1 64 7 2003-11-09, 21:38:19
/submitted/dummy 1 7 6 2010-05-03, 23:35:14
/submitted/dummy 1 49 5 2006-07-28, 04:28:31
/submitted/dummy 1 64 7 2020-10-25, 19:31:30
/submitted/dummy 1 26 6 2002-05-13, 20:42:17
/submitted/dummy 1 38 3 1998-05-29, 19:12:05
/submitted/dummy 1 11 5 2019-05-16, 15:58:55
/submitted/dummy 1 15 7 2013-04-29, 17:02:39
/submitted/dummy 1 30 7 2004-04-28, 07:10:03
/submitted/dummy 1 14 4 2021-03-12, 00:21:10
/submitted/dummy 1 2 3 1994-03-14, 21:55:11
/submitted/dummy 1 31 5 2021-04-01, 11:40:15
/submitted/dummy 1 4 2 2010-07-06, 14:31:29
/submitted/dummy 1 45 2 2002-08-31, 01:31:37
/submitted/dummy 1 22 3 2005-07-10, 18:57:43
/submitted/dummy 1 36 7 1993-02-11, 19:00:13
/submitted/dummy 1 30 7 2000-05-21, 10:14:37
/submitted/dummy 1 11 3 2020-12-08, 21:27:31
/submitted/dummy 1 30 7 2020-07-01, 15:43:14
/submitted/dummy 1 64 3 2021-08-24, 16:25:46
/submitted/dummy 2 19 4 2014-05-02, 08:48:16
/submitted/dummy 2 21 6 2018-12-16, 06:09:36
/submitted/dummy 2 48 7 1995-10-03, 09:18:53
/submitted/dummy 2 34 7 2012-12-17, 18:09:28
/submitted/dummy 2 50 7 2016-06-20, 15:00:52
/submitted/dummy 2 49 1 2010-11-29, 23:27:25
/submitted/dummy 2 23 1 2020-12-26, 07:04:01
/submitted/dummy 2 33 1 2005-05-21, 04:12:32
/submitted/dummy 2 30 7 2013-12-13, 20:12:46
/submitted/dummy 2 56 4 1992-07-27, 08:13:47
/submitted/dummy 2 46 4 1992-12-31, 06:04:23
/submitted/dummy 2 59 2 2019-12-01, 22:41:50
/submitted/dummy 2 14 3 2013-11-17, 15:32:25
/submitted/dummy 2 47 2 1992-07-01, 12:50:06
/submitted/dummy 2 23 1 2012-07-11, 08:07:31
/submitted/dummy 2 62 6 2022-02-06, 10:01:44
/submitted/dummy 2 42 4 2012-06-15, 16:14:52
/submitted/dummy 2 47 2 1998-09-14, 00:35:42
/submitted/dummy 2 18 5 2019-10-14, 16:16:58
/submitted/dummy 2 15 7 2007-01-03, 06:26:24
/submitted/dummy 2 51 2 2013-03-31, 04:28:30
/submitted/dummy 2 33 1 1999-01-25, 11:31:36
/submitted/dummy 2 57 2 2020-10-10, 17:33:11
/submitted/dummy 2 56 6 2010-07-06, 09:40:20
/submitted/dummy 2 64 7 1995-08-31, 01:35:37
/submitted/dummy 2 40 7 2007-03-19, 03:59:44
/submitted/dummy 2 45 2 2015-05-18, 16:41:10
/submitted/dummy 2 44 6 1996-08-08, 00:18:08
/submitted/dummy 2 25 1 2018-07-22, 08:31:25
/submitted/dummy 2 50 6 2008-09-27, 10:15:22
/submitted/dummy 2 33 1 2018-05-05, 05:54:46
/submitted/dummy 2 15 7 1999-08-17, 09:21:57
/submitted/dummy 2 59 2 1996-12-06, 17:32:16
/submitted/dummy 2 23 1 2015-02-17, 11:35:44
/submitted/dummy 2 13 4 2010-04-20, 19:22:14
/submitted/dummy 2 26 6 2022-02-25, 13:22:14
/submitted/dummy 2 16 1 1994-09-10, 21:45:37
/submitted/dummy 2 51 6 1995-05-27, 12:52:44
/submitted/dummy 2 38 3 2013-03-27, 04:31:11
/submitted/dummy 2 31 4 2021-06-09, 05:28:48
/submitted/dummy 2 35 1 2017-08-02, 15:46:09
/submitted/dummy 2 25 1 2006-12-24, 23:52:50
/submitted/dummy 2 22 2 1994-10-07, 08:52:24
/submitted/dummy 2 58 3 2015-03-16, 06:15:30
/submitted/dummy 2 57 2 1999-12-13, 19:40:02
/submitted/dummy 2 21 6 2003-11-23, 08:22:04
/submitted/dummy 2 53 7 2005-04-17, 08:25:22
/submitted/dummy 2 46 4 2011-08-04, 04:32:03
/submitted/dummy 3 40 3 2018-02-28, 00:49:45
/submitted/dummy 3 28 7 2008-05-18, 06:36:40
/submitted/dummy 3 30 7 1995-07-18, 20:15:56
/submitted/dummy 3 17 7 2006-07-18, 19:35:21
/submitted/dummy 3 8 7 2005-07-01, 01:08:58
/submitted/dummy 3 56 4 2007-11-30, 22:22:32
/submitted/dummy 3 36 7 2009-08-28, 08:34:01
/submitted/dummy 3 20 6 1996-10-08, 05:27:12
/submitted/dummy 3 2 3 2019-09-16, 22:26:46
/submitted/dummy 3 60 7 2021-07-08, 14:45:39
/submitted/dummy 3 62 1 2010-10-13, 19:44:16
/submitted/dummy 3 7 7 2005-02-20, 06:08:07
/submitted/dummy 3 40 3 2000-03-10, 20:35:17
/submitted/dummy 3 25 5 1997-10-16, 18:31:32
/submitted/dummy 3 22 2 1997-01-03, 09:11:24
/submitted/dummy 3 39 7 1998-03-12, 11:31:30
/submitted/dummy 3 20 6 2003-12-06, 16:12:40
/submitted/dummy 3 22 3 2001-02-02, 05:50:07
/submitted/dummy 3 23 3 1995-03-13, 20:13:25
/submitted/dummy 3 49 5 2019-03-09, 13:41:05
/submitted/dummy 3 50 7 2008-01-28, 08:00:29
/submitted/dummy 3 10 5 2014-04-22, 15:06:39
/submitted/dummy 3 52 2 2015-04-26, 07:56:31
/submitted/dummy 3 31 5 2007-03-30, 12:23:43
/submitted/dummy 3 15 7 2014-07-15, 18:16:37
/submitted/dummy 3 56 4 1992-08-19, 23:16:41
/submitted/dummy 3 23 3 1998-06-15, 01:48:39
/submitted/dummy 3 6 3 1996-05-13, 02:39:23
/submitted/dummy 3 20 6 2019-06-06, 12:27:04
/submitted/dummy 3 51 2 2000-08-08, 13:51:39
/submitted/dummy 3 1 4 1993-09-24, 12:23:26
/submitted/dummy 3 42 4 1998-12-14, 15:45:03
/submitted/dummy 3 42 4 2000-02-23, 16:24:12
/submitted/dummy 3 6 2 2009-02-27, 03:23:03
/submitted/dummy 3 35 1 1995-12-15, 07:56:36
/submitted/dummy 3 62 1 2014-06-09, 00:03:26
/submitted/dummy 3 62 6 1997-08-30, 19:24:38
/submitted/dummy 3 56 4 2021-12-05, 20:54:15
/submitted/dummy 3 49 1 1997-05-27, 10:53:08
/submitted/dummy 4 20 6 2019-08-04, 14:22:14
/submitted/dummy 4 43 1 2016-06-15, 16:31:23
/submitted/dummy 4 43 1 2016-04-19, 21:24:36
/submitted/dummy 4 26 6 2019-03-01, 07:18:35
/submitted/dummy 4 39 7 2018-08-03, 13:49:24
/submitted/dummy 4 25 1 2009-06-14, 14:04:21
/submitted/dummy 4 40 7 2008-06-11, 07:35:06
/submitted/dummy 4 44 3 2019-09-22, 01:09:11
/submitted/dummy 4 25 1 2021-08-26, 23:36:08
/submitted/dummy 4 25 1 1997-06-05, 13:54:00
/submitted/dummy 4 34 7 2002-11-06, 10:05:30
/submitted/dummy 4 18 5 2013-04-10, 15:10:11
/submitted/dummy 4 43 1 2021-09-24, 03:54:44
/submitted/dummy 4 27 7 1999-04-28, 19:03:46
/submitted/dummy 4 3 7 2002-10-02, 07:38:30
/submitted/dummy 4 26 6 2000-08-19, 07:43:47
/submitted/dummy 4 6 3 2020-05-27, 07:42:41
/submitted/dummy 4 27 1 1994-10-20, 16:22:52
/submitted/dummy 4 20 6 2009-03-25, 17:03:10
/submitted/dummy 4 42 4 1997-06-02, 14:15:38
/submitted/dummy 4 30 7 2003-07-07, 21:33:46
/submitted/dummy 4 40 3 1992-06-22, 08:12:34
/submitted/dummy 4 10 4 2019-06-23, 05:26:09
/submitted/dummy 4 42 4 2008-11-09, 18:20:59
/submitted/dummy 4 19 4 2011-06-27, 15:17:30
/submitted/dummy 4 8 7 2006-11-21, 22:58:03
/submitted/dummy 4 18 5 1999-11-02, 17:21:14
/submitted/dummy 4 2 3 2011-03-19, 01:37:09
/submitted/dummy 4 25 1 2001-10-02, 23:49:57
/submitted/dummy 4 7 7 2006-01-21, 03:16:49
/submitted/dummy 4 24 3 2006-05-25, 08:37:17
/submitted/dummy 4 16 1 2017-08-12, 16:24:20
/submitted/dummy 4 34 7 2006-05-05, 14:23:32
/submitted/dummy 4 42 4 2005-11-07, 05:46:07
/submitted/dummy 4 4 2 2018-11-16, 14:30:23
/submitted/dummy 4 28 7 2012-12-07, 11:43:42
/submitted/dummy 4 12 3 2019-07-29, 09:06:56
/submitted/dummy 4 14 3 2008-11-29, 05:28:42
/submitted/dummy 4 1 4 2022-03-29, 10:59:06
/submitted/dummy 4 8 7 2012-05-29, 14:41:54
/submitted/dummy 5 12 3 2019-10-11, 01:13:43
/submitted/dummy 5 40 3 1992-12-27, 03:53:17
/submitted/dummy 5 55 1 2019-06-25, 04:33:32
/submitted/dummy 5 52 2 2002-04-25, 10:50:52
/submitted/dummy 5 11 5 2001-07-17, 16:21:29