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 contests (
23 owner TEXT NOT NULL REFERENCES users
ON DELETE CASCADE,
24 CONSTRAINT positive_duration
CHECK (stop
> start)
27 CREATE TABLE 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 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 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
)
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 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
)
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 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.026587 seconds and 5 git commands to generate.