]>
iEval git - gruntmaster-data.git/blob - db.sql
1 CREATE TABLE IF NOT EXISTS users (
3 passphrase
TEXT , -- NOT NULL,
4 admin BOOLEAN NOT NULL DEFAULT FALSE ,
5 name TEXT , -- NOT NULL,
6 email
TEXT , -- NOT NULL,
7 phone
TEXT , -- NOT NULL,
8 town
TEXT , -- NOT NULL,
9 university
TEXT , -- NOT NULL,
10 level TEXT , -- NOT NULL,
13 since
BIGINT DEFAULT CAST ( EXTRACT ( epoch
from now ()) AS bigint )
16 CREATE TABLE IF NOT EXISTS contests (
23 owner TEXT NOT NULL REFERENCES users
ON DELETE CASCADE ,
24 CONSTRAINT positive_duration
CHECK ( stop
> start )
27 CREATE TABLE IF NOT EXISTS contest_status (
28 contest
TEXT NOT NULL REFERENCES contests
ON DELETE CASCADE ,
29 owner TEXT NOT NULL REFERENCES users
ON DELETE CASCADE ,
33 PRIMARY KEY ( owner , contest
)
36 CREATE TABLE IF NOT EXISTS problems (
40 generator
TEXT NOT NULL ,
45 owner TEXT NOT NULL REFERENCES users
ON DELETE CASCADE ,
46 private
BOOLEAN NOT NULL DEFAULT FALSE ,
49 statement TEXT NOT NULL ,
53 timeout
REAL NOT NULL ,
61 CREATE TABLE IF NOT EXISTS contest_problems (
62 contest
TEXT REFERENCES contests
ON DELETE CASCADE ,
63 problem
TEXT NOT NULL REFERENCES problems
ON DELETE CASCADE ,
64 PRIMARY KEY ( contest
, problem
)
67 CREATE TABLE IF NOT EXISTS jobs (
68 id SERIAL
PRIMARY KEY ,
69 contest
TEXT REFERENCES contests
ON DELETE CASCADE ,
71 date BIGINT NOT NULL DEFAULT CAST ( EXTRACT ( epoch
from now ()) AS bigint ),
73 extension
TEXT NOT NULL ,
75 private
BOOLEAN NOT NULL DEFAULT FALSE ,
76 problem
TEXT NOT NULL REFERENCES problems
ON DELETE CASCADE ,
82 owner TEXT NOT NULL REFERENCES users
ON DELETE CASCADE
85 CREATE TABLE IF NOT EXISTS problem_status (
86 problem
TEXT NOT NULL REFERENCES problems
ON DELETE CASCADE ,
87 owner TEXT NOT NULL REFERENCES users
ON DELETE CASCADE ,
88 job SERIAL
NOT NULL REFERENCES jobs
ON DELETE CASCADE ,
89 solved
BOOLEAN NOT NULL DEFAULT FALSE ,
91 PRIMARY KEY ( owner , problem
)
94 CREATE TABLE IF NOT EXISTS opens (
95 contest
TEXT NOT NULL REFERENCES contests
ON DELETE CASCADE ,
96 problem
TEXT NOT NULL REFERENCES problems
ON DELETE CASCADE ,
97 owner TEXT NOT NULL REFERENCES users
ON DELETE CASCADE ,
99 PRIMARY KEY ( contest
, problem
, owner )
102 CREATE TABLE IF NOT EXISTS limits (
103 problem
TEXT NOT NULL REFERENCES problems
ON DELETE CASCADE ,
104 format
TEXT NOT NULL ,
105 timeout
REAL NOT NULL ,
106 PRIMARY KEY ( problem
, format
)
109 COMMENT ON TABLE users
IS 'List of users' ;
110 COMMENT ON TABLE contests
IS 'List of contests' ;
111 COMMENT ON TABLE contest_status
IS 'List of (contest, user, result)' ;
112 COMMENT ON TABLE problems
IS 'List of problems' ;
113 COMMENT ON TABLE contest_problems
IS 'Many-to-many bridge between contests and problems' ;
114 COMMENT ON TABLE jobs
IS 'List of jobs' ;
115 COMMENT ON TABLE problem_status
IS 'List of (problem, user, result)' ;
116 COMMENT ON TABLE opens
IS 'List of (contest, problem, user, time when user opened problem)' ;
117 COMMENT ON TABLE limits
IS 'Time limit overrides for certain problem/format pairs' ;
119 COMMENT ON COLUMN users.passphrase
IS 'RFC2307-encoded passphrase' ;
120 COMMENT ON COLUMN users.
name IS 'Full name of user' ;
121 COMMENT ON COLUMN users.
level IS 'Highschool, Undergraduate, Master, Doctorate or Other' ;
122 COMMENT ON COLUMN users.lastjob
IS 'Unix time when this user last submitted a job' ;
123 COMMENT ON COLUMN users.since
IS 'Unix time when this user was created' ;
125 COMMENT ON COLUMN contests.
start IS 'Unix time when contest starts' ;
126 COMMENT ON COLUMN contests.stop
IS 'Unix time when contest ends' ;
127 COMMENT ON COLUMN contests.editorial
IS 'HTML fragment placed before the editorial' ;
128 COMMENT ON COLUMN contests.description
IS 'HTML fragment placed on contest page' ;
130 COMMENT ON COLUMN problems.author
IS 'Full name(s) of problem author(s)/problemsetter(s)/tester(s)/etc' ;
131 COMMENT ON COLUMN problems.writer
IS 'Full name(s) of statement writer(s) (DEPRECATED)' ;
132 COMMENT ON COLUMN problems.generator
IS 'Generator class, without the leading Gruntmaster::Daemon::Generator::' ;
133 COMMENT ON COLUMN problems.runner
IS 'Runner class, without the leading Gruntmaster::Daemon::Runner::' ;
134 COMMENT ON COLUMN problems.judge
IS 'Judge class, without the leading Gruntmaster::Daemon::Judge::' ;
135 COMMENT ON COLUMN problems.
level IS 'Problem level, one of beginner, easy, medium, hard' ;
136 COMMENT ON COLUMN problems.olimit
IS 'Output limit (in bytes)' ;
137 COMMENT ON COLUMN problems.timeout
IS 'Time limit (in seconds)' ;
138 COMMENT ON COLUMN problems.solution
IS 'Solution (HTML)' ;
139 COMMENT ON COLUMN problems.
statement IS 'Statement (HTML)' ;
140 COMMENT ON COLUMN problems.testcnt
IS 'Number of tests' ;
141 COMMENT ON COLUMN problems.precnt
IS 'Number of pretests. NULL indicates full feedback.' ;
142 COMMENT ON COLUMN problems.tests
IS 'JSON array of test values for ::Runner::File' ;
143 COMMENT ON COLUMN problems.
value IS 'Problem value when used in a contest.' ;
144 COMMENT ON COLUMN problems.genformat
IS 'Format (programming language) of the generator if using the Run generator' ;
145 COMMENT ON COLUMN problems.gensource
IS 'Source code of generator if using the Run generator' ;
146 COMMENT ON COLUMN problems.verformat
IS 'Format (programming language) of the verifier if using the Verifier runner' ;
147 COMMENT ON COLUMN problems.versource
IS 'Source code of verifier if using the Verifier runner' ;
149 COMMENT ON COLUMN jobs.daemon
IS 'hostname:PID of daemon that last executed this job. NULL if never executed' ;
150 COMMENT ON COLUMN jobs.
date IS 'Unix time when job was submitted' ;
151 COMMENT ON COLUMN jobs.errors
IS 'Compiler errors' ;
152 COMMENT ON COLUMN jobs.extension
IS 'File extension of submitted program, without a leading dot' ;
153 COMMENT ON COLUMN jobs.format
IS 'Format (programming language) of submitted program' ;
154 COMMENT ON COLUMN jobs.reference
IS 'If not null, this is a reference solution that should get this result. For example, set reference=0 on jobs that should be accepted, reference=3 on jobs that should get TLE, etc' ;
155 COMMENT ON COLUMN jobs.
result IS 'Job result (integer constant from Gruntmaster::Daemon::Constants)' ;
156 COMMENT ON COLUMN jobs.result_text
IS 'Job result (human-readable text)' ;
157 COMMENT ON COLUMN jobs.results
IS 'Per-test results (JSON array of hashes with keys id (test number, counting from 1), result (integer constant from Gruntmaster::Daemon::Constants), result_text (human-readable text), time (execution time in decimal seconds))' ;
159 CREATE OR REPLACE VIEW user_data
AS ( SELECT
160 id , admin , name , town
, university
, country
, level , lastjob
164 CREATE OR REPLACE VIEW user_list
AS ( SELECT
166 COALESCE ( solved
, 0 ) as solved
,
167 COALESCE ( attempted
, 0 ) as attempted
,
168 COALESCE ( contests
, 0 ) as contests
170 LEFT JOIN ( SELECT owner as id , COUNT (*) as solved
FROM problem_status
WHERE solved
= TRUE GROUP BY owner ) ps
USING ( id )
171 LEFT JOIN ( SELECT owner as id , COUNT (*) as attempted
FROM problem_status
WHERE solved
= FALSE GROUP BY owner ) pa
USING ( id )
172 LEFT JOIN ( SELECT owner as id , COUNT (*) as contests
FROM contest_status
GROUP BY owner ) ct
USING ( id )
173 ORDER BY solved
DESC , attempted
DESC , id );
175 CREATE OR REPLACE VIEW contest_entry
AS ( SELECT
176 id , name , description
, start , stop
, owner ,
177 ( EXTRACT ( epoch
from NOW ()) >= start ) AS started
,
178 ( EXTRACT ( epoch
from NOW ()) >= stop
) AS finished
180 ORDER BY start DESC );
182 CREATE OR REPLACE VIEW job_entry
AS ( SELECT
183 id , contest
, date , errors
, extension
, format
, private
, problem
, result , result_text
, results
, owner ,
184 LENGTH ( source ) AS size
This page took 0.074423 seconds and 4 git commands to generate.