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 CREATE TABLE table_comments (
 110         table_name   TEXT NOT NULL PRIMARY KEY,
 111         comment_text TEXT NOT NULL
 112 );
 113
 114 CREATE TABLE column_comments (
 115         table_name   TEXT NOT NULL,
 116         column_name  TEXT NOT NULL,
 117         comment_text TEXT NOT NULL,
 118         PRIMARY KEY (table_name, column_name)
 119 );
 120
 121 INSERT INTO table_comments VALUES ('users',   'List of users');
 122 INSERT INTO table_comments VALUES ('contests',   'List of contests');
 123 INSERT INTO table_comments VALUES ('contest_status',   'List of (contest, user, result)');
 124 INSERT INTO table_comments VALUES ('problems',   'List of problems');
 125 INSERT INTO table_comments VALUES ('contest_problems', 'Many-to-many bridge between contests and problems');
 126 INSERT INTO table_comments VALUES ('jobs',   'List of jobs');
 127 INSERT INTO table_comments VALUES ('problem_status', 'List of (problem, user, result)');
 128 INSERT INTO table_comments VALUES ('opens', 'List of (contest, problem, user, time when user opened problem)');
 129 INSERT INTO table_comments VALUES ('limits', 'Time limit overrides for certain problem/format pairs');
 130
 131 INSERT INTO column_comments VALUES ('users', 'passphrase', 'RFC2307-encoded passphrase');
 132 INSERT INTO column_comments VALUES ('users', 'name', 'Full name of user');
 133 INSERT INTO column_comments VALUES ('users', 'level', 'Highschool, Undergraduate, Master, Doctorate or Other');
 134 INSERT INTO column_comments VALUES ('users', 'lastjob', 'Unix time when this user last submitted a job');
 135 INSERT INTO column_comments VALUES ('users', 'since', 'Unix time when this user was created');
 136
 137 INSERT INTO column_comments VALUES ('contests', 'start', 'Unix time when contest starts');
 138 INSERT INTO column_comments VALUES ('contests', 'stop', 'Unix time when contest ends');
 139 INSERT INTO column_comments VALUES ('contests', 'editorial', 'HTML fragment placed before the editorial');
 140 INSERT INTO column_comments VALUES ('contests', 'description', 'HTML fragment placed on contest page');
 141
 142 INSERT INTO column_comments VALUES ('problems', 'author', 'Full name(s) of problem author(s)/problemsetter(s)/tester(s)/etc');
 143 INSERT INTO column_comments VALUES ('problems', 'writer', 'Full name(s) of statement writer(s) (DEPRECATED)');
 144 INSERT INTO column_comments VALUES ('problems', 'generator', 'Generator class, without the leading Gruntmaster::Daemon::Generator::');
 145 INSERT INTO column_comments VALUES ('problems', 'runner', 'Runner class, without the leading Gruntmaster::Daemon::Runner::');
 146 INSERT INTO column_comments VALUES ('problems', 'judge', 'Judge class, without the leading Gruntmaster::Daemon::Judge::');
 147 INSERT INTO column_comments VALUES ('problems', 'level', 'Problem level, one of beginner, easy, medium, hard');
 148 INSERT INTO column_comments VALUES ('problems', 'olimit', 'Output limit (in bytes)');
 149 INSERT INTO column_comments VALUES ('problems', 'timeout', 'Time limit (in seconds)');
 150 INSERT INTO column_comments VALUES ('problems', 'solution', 'Solution (HTML)');
 151 INSERT INTO column_comments VALUES ('problems', 'statement', 'Statement (HTML)');
 152 INSERT INTO column_comments VALUES ('problems', 'testcnt', 'Number of tests');
 153 INSERT INTO column_comments VALUES ('problems', 'precnt', 'Number of pretests. NULL indicates full feedback.');
 154 INSERT INTO column_comments VALUES ('problems', 'tests', 'JSON array of test values for ::Runner::File');
 155 INSERT INTO column_comments VALUES ('problems', 'value', 'Problem value when used in a contest.');
 156 INSERT INTO column_comments VALUES ('problems', 'genformat', 'Format (programming language) of the generator if using the Run generator');
 157 INSERT INTO column_comments VALUES ('problems', 'gensource', 'Source code of generator if using the Run generator');
 158 INSERT INTO column_comments VALUES ('problems', 'verformat', 'Format (programming language) of the verifier if using the Verifier runner');
 159 INSERT INTO column_comments VALUES ('problems', 'versource', 'Source code of verifier if using the Verifier runner');
 160
 161 INSERT INTO column_comments VALUES ('jobs', 'daemon', 'hostname:PID of daemon that last executed this job. NULL if never executed');
 162 INSERT INTO column_comments VALUES ('jobs', 'date', 'Unix time when job was submitted');
 163 INSERT INTO column_comments VALUES ('jobs', 'errors', 'Compiler errors');
 164 INSERT INTO column_comments VALUES ('jobs', 'extension', 'File extension of submitted program, without a leading dot');
 165 INSERT INTO column_comments VALUES ('jobs', 'format', 'Format (programming language) of submitted program');
 166 INSERT INTO column_comments VALUES ('jobs', 'reference', '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');
 167 INSERT INTO column_comments VALUES ('jobs', 'result', 'Job result (integer constant from Gruntmaster::Daemon::Constants)');
 168 INSERT INTO column_comments VALUES ('jobs', 'result_text', 'Job result (human-readable text)');
 169 INSERT INTO column_comments VALUES ('jobs', 'results', '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))');
 170
 171 INSERT INTO column_comments VALUES ('problem_status', 'solved', 'True if the result is Accepted, False otherwise');
 172
 173 CREATE OR REPLACE VIEW user_data AS (SELECT
 174         id,admin,name,town,university,country,level,lastjob
 175         FROM users
 176 );
 177
 178 CREATE OR REPLACE VIEW user_solved_problems AS (SELECT
 179         us.id,
 180         COALESCE(array_agg(ps.problem) FILTER (WHERE ps.problem IS NOT NULL), ARRAY[]::text[]) AS solved
 181         FROM users us
 182         LEFT JOIN (SELECT * FROM problem_status WHERE solved = TRUE) ps ON ps.owner=id
 183         GROUP BY us.id);
 184
 185 CREATE OR REPLACE VIEW user_attempted_problems AS (SELECT
 186         us.id,
 187         COALESCE(array_agg(ps.problem) FILTER (WHERE ps.problem IS NOT NULL), ARRAY[]::text[]) AS attempted
 188         FROM users us
 189         LEFT JOIN (SELECT * FROM problem_status WHERE solved = FALSE) ps ON ps.owner=id
 190         GROUP BY us.id);
 191
 192 CREATE OR REPLACE VIEW user_contests AS (SELECT
 193         us.id,
 194         COALESCE(array_agg(cs.contest) FILTER (WHERE cs.contest IS NOT NULL), ARRAY[]::text[]) AS contests
 195         FROM users us
 196         LEFT JOIN contest_status cs ON cs.owner=id
 197         GROUP BY us.id);
 198
 199 CREATE OR REPLACE VIEW user_list AS (SELECT
 200         dt.*,
 201         COALESCE(array_length(solved, 1), 0) AS solved,
 202         COALESCE(array_length(attempted, 1), 0) AS attempted,
 203         COALESCE(array_length(contests, 1), 0) AS contests
 204         FROM user_data dt
 205         JOIN user_contests ct USING (id)
 206         JOIN user_solved_problems sp USING (id)
 207         JOIN user_attempted_problems ap USING (id)
 208         ORDER BY solved DESC, attempted DESC, id);
 209
 210 CREATE OR REPLACE VIEW contest_entry AS (SELECT
 211         id,name,description,editorial,start,stop,owner,
 212         (EXTRACT(epoch from NOW()) >= start) AS started,
 213         (EXTRACT(epoch from NOW()) >= stop) AS finished
 214         FROM contests
 215         ORDER BY start DESC);
 216
 217 CREATE OR REPLACE VIEW job_entry AS (SELECT
 218         id,contest,date,errors,extension,format,private,problem,result,result_text,results,owner,
 219         LENGTH(source) AS size
 220         FROM jobs
 221         ORDER BY id DESC);
 222
 223 -- CREATE OR REPLACE FUCNTION source_private(jobid INT) RETURNS BOOLEAN AS $$
 224 -- BEGIN
 225 --      private BOOLEAN;
 226 --      problem TEXT;
 227 --      contest TEXT;
 228
 229 --      SELECT private, problem, contest INTO STRICT private, problem, contest FROM jobs WHERE id = jobid;
 230 --      IF private THEN
 231 --              RETURN TRUE;
 232 --      END IF
 233
 234 --      IF
 235 -- END;
 236 -- $$