| 1 | CREATE TABLE IF NOT EXISTS users ( |
| 2 | id TEXT PRIMARY KEY, |
| 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, |
| 11 | country TEXT, |
| 12 | lastjob BIGINT, |
| 13 | since BIGINT DEFAULT CAST(EXTRACT(epoch from now()) AS bigint) |
| 14 | ); |
| 15 | |
| 16 | CREATE TABLE IF NOT EXISTS contests ( |
| 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) |
| 25 | ); |
| 26 | |
| 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, |
| 30 | score INT NOT NULL, |
| 31 | rank INT NOT NULL, |
| 32 | |
| 33 | PRIMARY KEY (owner, contest) |
| 34 | ); |
| 35 | |
| 36 | CREATE TABLE IF NOT EXISTS problems ( |
| 37 | id TEXT PRIMARY KEY, |
| 38 | author TEXT, |
| 39 | writer TEXT, |
| 40 | generator TEXT NOT NULL, |
| 41 | judge TEXT NOT NULL, |
| 42 | level TEXT NOT NULL, |
| 43 | name TEXT NOT NULL, |
| 44 | olimit INT, |
| 45 | owner TEXT NOT NULL REFERENCES users ON DELETE CASCADE, |
| 46 | private BOOLEAN NOT NULL DEFAULT FALSE, |
| 47 | runner TEXT NOT NULL, |
| 48 | solution TEXT , |
| 49 | statement TEXT NOT NULL, |
| 50 | testcnt INT NOT NULL, |
| 51 | precnt INT, |
| 52 | tests TEXT, |
| 53 | timeout REAL NOT NULL, |
| 54 | value INT NOT NULL, |
| 55 | genformat TEXT, |
| 56 | gensource TEXT, |
| 57 | verformat TEXT, |
| 58 | versource TEXT |
| 59 | ); |
| 60 | |
| 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) |
| 65 | ); |
| 66 | |
| 67 | CREATE TABLE IF NOT EXISTS jobs ( |
| 68 | id SERIAL PRIMARY KEY, |
| 69 | contest TEXT REFERENCES contests ON DELETE CASCADE, |
| 70 | daemon TEXT, |
| 71 | date BIGINT NOT NULL DEFAULT CAST(EXTRACT(epoch from now()) AS bigint), |
| 72 | errors TEXT, |
| 73 | extension TEXT NOT NULL, |
| 74 | format TEXT NOT NULL, |
| 75 | private BOOLEAN NOT NULL DEFAULT FALSE, |
| 76 | problem TEXT NOT NULL REFERENCES problems ON DELETE CASCADE, |
| 77 | reference INT, |
| 78 | result INT, |
| 79 | result_text TEXT, |
| 80 | results TEXT, |
| 81 | source TEXT NOT NULL, |
| 82 | owner TEXT NOT NULL REFERENCES users ON DELETE CASCADE |
| 83 | ); |
| 84 | |
| 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, |
| 90 | |
| 91 | PRIMARY KEY (owner, problem) |
| 92 | ); |
| 93 | |
| 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, |
| 98 | time BIGINT NOT NULL, |
| 99 | PRIMARY KEY (contest, problem, owner) |
| 100 | ); |
| 101 | |
| 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) |
| 107 | ); |
| 108 | |
| 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'; |
| 118 | |
| 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'; |
| 124 | |
| 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'; |
| 129 | |
| 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'; |
| 148 | |
| 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))'; |
| 158 | |
| 159 | CREATE OR REPLACE VIEW user_data AS (SELECT |
| 160 | id,admin,name,town,university,country,level,lastjob |
| 161 | FROM users |
| 162 | ); |
| 163 | |
| 164 | CREATE OR REPLACE VIEW user_list AS (SELECT |
| 165 | dt.*, |
| 166 | COALESCE(solved, 0) as solved, |
| 167 | COALESCE(attempted, 0) as attempted, |
| 168 | COALESCE(contests, 0) as contests |
| 169 | FROM user_data dt |
| 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); |
| 174 | |
| 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 |
| 179 | FROM contests |
| 180 | ORDER BY start DESC); |
| 181 | |
| 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 |
| 185 | FROM jobs |
| 186 | ORDER BY id DESC); |