]> iEval git - gruntmaster-data.git/blame - db.sql
Add gruntmaster-opener to EXE_FILES in Makefile.PL
[gruntmaster-data.git] / db.sql
CommitLineData
1ea1adc8 1CREATE TABLE IF NOT EXISTS users (
2fec2d56 2 id TEXT PRIMARY KEY,
9bb39921 3 passphrase TEXT, -- NOT NULL,
2fec2d56 4 admin BOOLEAN NOT NULL DEFAULT FALSE,
4ed3f8e7
MG
5 name TEXT, -- NOT NULL,
6 email TEXT, -- NOT NULL,
9a987597 7 phone TEXT, -- NOT NULL,
4ed3f8e7
MG
8 town TEXT, -- NOT NULL,
9 university TEXT, -- NOT NULL,
9bb39921 10 level TEXT, -- NOT NULL,
73243865 11 country TEXT,
8c7ef664
MG
12 lastjob BIGINT,
13 since BIGINT DEFAULT CAST(EXTRACT(epoch from now()) AS bigint)
4ed3f8e7
MG
14);
15
1ea1adc8 16CREATE TABLE IF NOT EXISTS contests (
b596b2a9
MG
17 id TEXT PRIMARY KEY,
18 name TEXT NOT NULL,
19 editorial TEXT,
20 description TEXT,
21 start INT NOT NULL,
22 stop INT NOT NULL,
23 owner TEXT NOT NULL REFERENCES users ON DELETE CASCADE,
24 CONSTRAINT positive_duration CHECK (stop > start)
4ed3f8e7
MG
25);
26
1ea1adc8 27CREATE TABLE IF NOT EXISTS contest_status (
adb42d4d
MG
28 contest TEXT NOT NULL REFERENCES contests ON DELETE CASCADE,
29 owner TEXT NOT NULL REFERENCES users ON DELETE CASCADE,
30 score INT NOT NULL,
31 rank INT NOT NULL,
32
33 PRIMARY KEY (owner, contest)
34);
35
1ea1adc8 36CREATE TABLE IF NOT EXISTS problems (
4ed3f8e7 37 id TEXT PRIMARY KEY,
20fa6cfc 38 author TEXT,
6a03bf0d 39 writer TEXT,
9bb39921
MG
40 generator TEXT NOT NULL,
41 judge TEXT NOT NULL,
42 level TEXT NOT NULL,
43 name TEXT NOT NULL,
4ed3f8e7 44 olimit INT,
9bb39921
MG
45 owner TEXT NOT NULL REFERENCES users ON DELETE CASCADE,
46 private BOOLEAN NOT NULL DEFAULT FALSE,
47 runner TEXT NOT NULL,
1980106a 48 solution TEXT ,
9bb39921
MG
49 statement TEXT NOT NULL,
50 testcnt INT NOT NULL,
f7c095d3 51 precnt INT,
b2725d9d 52 tests TEXT,
9bb39921 53 timeout REAL NOT NULL,
aaa9eb7d 54 value INT NOT NULL,
85d3f015
MG
55 genformat TEXT,
56 gensource TEXT,
4ed3f8e7
MG
57 verformat TEXT,
58 versource TEXT
59);
60
1ea1adc8 61CREATE TABLE IF NOT EXISTS contest_problems (
9bb39921
MG
62 contest TEXT REFERENCES contests ON DELETE CASCADE,
63 problem TEXT NOT NULL REFERENCES problems ON DELETE CASCADE,
4ed3f8e7
MG
64 PRIMARY KEY (contest, problem)
65);
66
1ea1adc8 67CREATE TABLE IF NOT EXISTS jobs (
4ed3f8e7 68 id SERIAL PRIMARY KEY,
9bb39921 69 contest TEXT REFERENCES contests ON DELETE CASCADE,
4ed3f8e7 70 daemon TEXT,
5cc71db9 71 date BIGINT NOT NULL DEFAULT CAST(EXTRACT(epoch from now()) AS bigint),
4ed3f8e7
MG
72 errors TEXT,
73 extension TEXT NOT NULL,
74 format TEXT NOT NULL,
75 private BOOLEAN NOT NULL DEFAULT FALSE,
9bb39921 76 problem TEXT NOT NULL REFERENCES problems ON DELETE CASCADE,
987ee14e 77 reference INT,
4ed3f8e7
MG
78 result INT,
79 result_text TEXT,
20fa6cfc 80 results TEXT,
4ed3f8e7 81 source TEXT NOT NULL,
9bb39921 82 owner TEXT NOT NULL REFERENCES users ON DELETE CASCADE
4ed3f8e7
MG
83);
84
1ea1adc8 85CREATE TABLE IF NOT EXISTS problem_status (
adb42d4d
MG
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,
90
91 PRIMARY KEY (owner, problem)
92);
93
1ea1adc8 94CREATE TABLE IF NOT EXISTS opens (
9bb39921
MG
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,
4ed3f8e7
MG
98 time BIGINT NOT NULL,
99 PRIMARY KEY (contest, problem, owner)
100);
be2f7678 101
1ea1adc8 102CREATE TABLE IF NOT EXISTS limits (
6dda1f23
MG
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)
107);
108
902c527f
MG
109COMMENT ON TABLE users IS 'List of users';
110COMMENT ON TABLE contests IS 'List of contests';
111COMMENT ON TABLE contest_status IS 'List of (contest, user, result)';
112COMMENT ON TABLE problems IS 'List of problems';
113COMMENT ON TABLE contest_problems IS 'Many-to-many bridge between contests and problems';
114COMMENT ON TABLE jobs IS 'List of jobs';
115COMMENT ON TABLE problem_status IS 'List of (problem, user, result)';
116COMMENT ON TABLE opens IS 'List of (contest, problem, user, time when user opened problem)';
117COMMENT ON TABLE limits IS 'Time limit overrides for certain problem/format pairs';
118
119COMMENT ON COLUMN users.passphrase IS 'RFC2307-encoded passphrase';
120COMMENT ON COLUMN users.name IS 'Full name of user';
121COMMENT ON COLUMN users.level IS 'Highschool, Undergraduate, Master, Doctorate or Other';
122COMMENT ON COLUMN users.lastjob IS 'Unix time when this user last submitted a job';
123COMMENT ON COLUMN users.since IS 'Unix time when this user was created';
124
125COMMENT ON COLUMN contests.start IS 'Unix time when contest starts';
126COMMENT ON COLUMN contests.stop IS 'Unix time when contest ends';
127COMMENT ON COLUMN contests.editorial IS 'HTML fragment placed before the editorial';
128COMMENT ON COLUMN contests.description IS 'HTML fragment placed on contest page';
129
130COMMENT ON COLUMN problems.author IS 'Full name(s) of problem author(s)/problemsetter(s)/tester(s)/etc';
131COMMENT ON COLUMN problems.writer IS 'Full name(s) of statement writer(s) (DEPRECATED)';
132COMMENT ON COLUMN problems.generator IS 'Generator class, without the leading Gruntmaster::Daemon::Generator::';
133COMMENT ON COLUMN problems.runner IS 'Runner class, without the leading Gruntmaster::Daemon::Runner::';
134COMMENT ON COLUMN problems.judge IS 'Judge class, without the leading Gruntmaster::Daemon::Judge::';
135COMMENT ON COLUMN problems.level IS 'Problem level, one of beginner, easy, medium, hard';
136COMMENT ON COLUMN problems.olimit IS 'Output limit (in bytes)';
137COMMENT ON COLUMN problems.timeout IS 'Time limit (in seconds)';
138COMMENT ON COLUMN problems.solution IS 'Solution (HTML)';
139COMMENT ON COLUMN problems.statement IS 'Statement (HTML)';
140COMMENT ON COLUMN problems.testcnt IS 'Number of tests';
141COMMENT ON COLUMN problems.precnt IS 'Number of pretests. NULL indicates full feedback.';
142COMMENT ON COLUMN problems.tests IS 'JSON array of test values for ::Runner::File';
143COMMENT ON COLUMN problems.value IS 'Problem value when used in a contest.';
144COMMENT ON COLUMN problems.genformat IS 'Format (programming language) of the generator if using the Run generator';
145COMMENT ON COLUMN problems.gensource IS 'Source code of generator if using the Run generator';
146COMMENT ON COLUMN problems.verformat IS 'Format (programming language) of the verifier if using the Verifier runner';
147COMMENT ON COLUMN problems.versource IS 'Source code of verifier if using the Verifier runner';
148
149COMMENT ON COLUMN jobs.daemon IS 'hostname:PID of daemon that last executed this job. NULL if never executed';
150COMMENT ON COLUMN jobs.date IS 'Unix time when job was submitted';
151COMMENT ON COLUMN jobs.errors IS 'Compiler errors';
152COMMENT ON COLUMN jobs.extension IS 'File extension of submitted program, without a leading dot';
153COMMENT ON COLUMN jobs.format IS 'Format (programming language) of submitted program';
154COMMENT 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';
155COMMENT ON COLUMN jobs.result IS 'Job result (integer constant from Gruntmaster::Daemon::Constants)';
156COMMENT ON COLUMN jobs.result_text IS 'Job result (human-readable text)';
157COMMENT 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))';
52172a1a
MG
158
159CREATE OR REPLACE VIEW user_data AS (SELECT
160 id,admin,name,town,university,country,level,lastjob
161 FROM users
162);
163
52172a1a
MG
164CREATE OR REPLACE VIEW user_list AS (SELECT
165 dt.*,
cc6974a9
MG
166 COALESCE(solved, 0) as solved,
167 COALESCE(attempted, 0) as attempted,
168 COALESCE(contests, 0) as contests
52172a1a 169 FROM user_data dt
cc6974a9
MG
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)
52172a1a
MG
173 ORDER BY solved DESC, attempted DESC, id);
174
175CREATE OR REPLACE VIEW contest_entry AS (SELECT
89dadd7b 176 id,name,description,start,stop,owner,
52172a1a
MG
177 (EXTRACT(epoch from NOW()) >= start) AS started,
178 (EXTRACT(epoch from NOW()) >= stop) AS finished
179 FROM contests
180 ORDER BY start DESC);
181
182CREATE 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
185 FROM jobs
186 ORDER BY id DESC);
This page took 0.056962 seconds and 4 git commands to generate.