db.sql

   1 CREATE TABLE 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 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 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 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 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 jobs (
  68         id          SERIAL  PRIMARY KEY,
  69         contest     TEXT    REFERENCES contests ON DELETE CASCADE,
  70         daemon      TEXT,
  71         date        BIGINT  NOT NULL,
  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 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 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 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_solved_problems AS (SELECT
 165         us.id,
 166         COALESCE(array_agg(ps.problem) FILTER (WHERE ps.problem IS NOT NULL), ARRAY[]::text[]) AS solved
 167         FROM users us
 168         LEFT JOIN (SELECT * FROM problem_status WHERE solved = TRUE) ps ON ps.owner=id
 169         GROUP BY us.id);
 170
 171 CREATE OR REPLACE VIEW user_attempted_problems AS (SELECT
 172         us.id,
 173         COALESCE(array_agg(ps.problem) FILTER (WHERE ps.problem IS NOT NULL), ARRAY[]::text[]) AS attempted
 174         FROM users us
 175         LEFT JOIN (SELECT * FROM problem_status WHERE solved = FALSE) ps ON ps.owner=id
 176         GROUP BY us.id);
 177
 178 CREATE OR REPLACE VIEW user_contests AS (SELECT
 179         us.id,
 180         COALESCE(array_agg(cs.contest) FILTER (WHERE cs.contest IS NOT NULL), ARRAY[]::text[]) AS contests
 181         FROM users us
 182         LEFT JOIN contest_status cs ON cs.owner=id
 183         GROUP BY us.id);
 184
 185 CREATE OR REPLACE VIEW user_list AS (SELECT
 186         dt.*,
 187         COALESCE(array_length(solved, 1), 0) AS solved,
 188         COALESCE(array_length(attempted, 1), 0) AS attempted,
 189         COALESCE(array_length(contests, 1), 0) AS contests
 190         FROM user_data dt
 191         JOIN user_contests ct USING (id)
 192         JOIN user_solved_problems sp USING (id)
 193         JOIN user_attempted_problems ap USING (id)
 194         ORDER BY solved DESC, attempted DESC, id);
 195
 196 CREATE OR REPLACE VIEW contest_entry AS (SELECT
 197         id,name,description,editorial,start,stop,owner,
 198         (EXTRACT(epoch from NOW()) >= start) AS started,
 199         (EXTRACT(epoch from NOW()) >= stop) AS finished
 200         FROM contests
 201         ORDER BY start DESC);
 202
 203 CREATE OR REPLACE VIEW job_entry AS (SELECT
 204         id,contest,date,errors,extension,format,private,problem,result,result_text,results,owner,
 205         LENGTH(source) AS size
 206         FROM jobs
 207         ORDER BY id DESC);