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         start INT  NOT NULL,
  20         stop  INT  NOT NULL,
  21         owner TEXT NOT NULL REFERENCES users ON DELETE CASCADE,
  22         CONSTRAINT positive_duration CHECK (stop > start)
  23 );
  24
  25 CREATE TABLE contest_status (
  26         contest TEXT NOT NULL REFERENCES contests ON DELETE CASCADE,
  27         owner   TEXT NOT NULL REFERENCES users ON DELETE CASCADE,
  28         score   INT  NOT NULL,
  29         rank    INT  NOT NULL,
  30
  31         PRIMARY KEY (owner, contest)
  32 );
  33
  34 CREATE TABLE problems (
  35         id        TEXT      PRIMARY KEY,
  36         author    TEXT,
  37         writer    TEXT,
  38         generator TEXT    NOT NULL,
  39         judge     TEXT    NOT NULL,
  40         level     TEXT    NOT NULL,
  41         name      TEXT    NOT NULL,
  42         olimit    INT,
  43         owner     TEXT    NOT NULL REFERENCES users ON DELETE CASCADE,
  44         private   BOOLEAN NOT NULL DEFAULT FALSE,
  45         runner    TEXT    NOT NULL,
  46         solution  TEXT ,
  47         statement TEXT    NOT NULL,
  48         testcnt   INT     NOT NULL,
  49         precnt    INT,
  50         tests     TEXT,
  51         timeout   REAL    NOT NULL,
  52         value     INT     NOT NULL,
  53         genformat TEXT,
  54         gensource TEXT,
  55         verformat TEXT,
  56         versource TEXT
  57 );
  58
  59 CREATE TABLE contest_problems (
  60         contest TEXT REFERENCES contests ON DELETE CASCADE,
  61         problem TEXT NOT NULL REFERENCES problems ON DELETE CASCADE,
  62         PRIMARY KEY (contest, problem)
  63 );
  64
  65 CREATE TABLE jobs (
  66         id          SERIAL  PRIMARY KEY,
  67         contest     TEXT    REFERENCES contests ON DELETE CASCADE,
  68         daemon      TEXT,
  69         date        BIGINT  NOT NULL,
  70         errors      TEXT,
  71         extension   TEXT    NOT NULL,
  72         format      TEXT    NOT NULL,
  73         private     BOOLEAN NOT NULL DEFAULT FALSE,
  74         problem     TEXT    NOT NULL REFERENCES problems ON DELETE CASCADE,
  75         result      INT,
  76         result_text TEXT,
  77         results     TEXT,
  78         source      TEXT    NOT NULL,
  79         owner       TEXT    NOT NULL REFERENCES users ON DELETE CASCADE
  80 );
  81
  82 CREATE TABLE problem_status (
  83         problem TEXT    NOT NULL REFERENCES problems ON DELETE CASCADE,
  84         owner   TEXT    NOT NULL REFERENCES users ON DELETE CASCADE,
  85         job     SERIAL  NOT NULL REFERENCES jobs ON DELETE CASCADE,
  86         solved  BOOLEAN NOT NULL DEFAULT FALSE,
  87
  88         PRIMARY KEY (owner, problem)
  89 );
  90
  91 CREATE TABLE opens (
  92         contest TEXT   NOT NULL REFERENCES contests ON DELETE CASCADE,
  93         problem TEXT   NOT NULL REFERENCES problems ON DELETE CASCADE,
  94         owner   TEXT   NOT NULL REFERENCES users ON DELETE CASCADE,
  95         time    BIGINT NOT NULL,
  96         PRIMARY KEY (contest, problem, owner)
  97 );
  98
  99 CREATE TABLE table_comments (
 100         table_name   TEXT NOT NULL PRIMARY KEY,
 101         comment_text TEXT NOT NULL
 102 );
 103
 104 CREATE TABLE column_comments (
 105         table_name   TEXT NOT NULL,
 106         column_name  TEXT NOT NULL,
 107         comment_text TEXT NOT NULL,
 108         PRIMARY KEY (table_name, column_name)
 109 );
 110
 111 INSERT INTO table_comments VALUES ('users',   'List of users');
 112 INSERT INTO table_comments VALUES ('contests',   'List of contests');
 113 INSERT INTO table_comments VALUES ('contest_status',   'List of (contest, user, result)');
 114 INSERT INTO table_comments VALUES ('problems',   'List of problems');
 115 INSERT INTO table_comments VALUES ('contest_problems', 'Many-to-many bridge between contests and problems');
 116 INSERT INTO table_comments VALUES ('jobs',   'List of jobs');
 117 INSERT INTO table_comments VALUES ('problem_status', 'List of (problem, user, result)');
 118 INSERT INTO table_comments VALUES ('opens', 'List of (contest, problem, user, time when user opened problem)');
 119
 120 INSERT INTO column_comments VALUES ('users', 'passphrase', 'RFC2307-encoded passphrase');
 121 INSERT INTO column_comments VALUES ('users', 'name', 'Full name of user');
 122 INSERT INTO column_comments VALUES ('users', 'level', 'Highschool, Undergraduate, Master, Doctorate or Other');
 123 INSERT INTO column_comments VALUES ('users', 'lastjob', 'Unix time when this user last submitted a job');
 124 INSERT INTO column_comments VALUES ('users', 'since', 'Unix time when this user was created');
 125
 126 INSERT INTO column_comments VALUES ('contests', 'start', 'Unix time when contest starts');
 127 INSERT INTO column_comments VALUES ('contests', 'stop', 'Unix time when contest ends');
 128
 129 INSERT INTO column_comments VALUES ('problems', 'author', 'Full name(s) of problem author(s)/problemsetter(s)/tester(s)/etc');
 130 INSERT INTO column_comments VALUES ('problems', 'writer', 'Full name(s) of statement writer(s) (DEPRECATED)');
 131 INSERT INTO column_comments VALUES ('problems', 'generator', 'Generator class, without the leading Gruntmaster::Daemon::Generator::');
 132 INSERT INTO column_comments VALUES ('problems', 'runner', 'Runner class, without the leading Gruntmaster::Daemon::Runner::');
 133 INSERT INTO column_comments VALUES ('problems', 'judge', 'Judge class, without the leading Gruntmaster::Daemon::Judge::');
 134 INSERT INTO column_comments VALUES ('problems', 'level', 'Problem level, one of beginner, easy, medium, hard');
 135 INSERT INTO column_comments VALUES ('problems', 'olimit', 'Output limit (in bytes)');
 136 INSERT INTO column_comments VALUES ('problems', 'timeout', 'Time limit (in seconds)');
 137 INSERT INTO column_comments VALUES ('problems', 'solution', 'Solution (HTML)');
 138 INSERT INTO column_comments VALUES ('problems', 'statement', 'Statement (HTML)');
 139 INSERT INTO column_comments VALUES ('problems', 'testcnt', 'Number of tests');
 140 INSERT INTO column_comments VALUES ('problems', 'precnt', 'Number of pretests. NULL indicates full feedback.');
 141 INSERT INTO column_comments VALUES ('problems', 'tests', 'JSON array of test values for ::Runner::File');
 142 INSERT INTO column_comments VALUES ('problems', 'value', 'Problem value when used in a contest.');
 143 INSERT INTO column_comments VALUES ('problems', 'genformat', 'Format (programming language) of the generator if using the Run generator');
 144 INSERT INTO column_comments VALUES ('problems', 'gensource', 'Source code of generator if using the Run generator');
 145 INSERT INTO column_comments VALUES ('problems', 'verformat', 'Format (programming language) of the verifier if using the Verifier runner');
 146 INSERT INTO column_comments VALUES ('problems', 'versource', 'Source code of verifier if using the Verifier runner');
 147
 148 INSERT INTO column_comments VALUES ('jobs', 'daemon', 'hostname:PID of daemon that last executed this job. NULL if never executed');
 149 INSERT INTO column_comments VALUES ('jobs', 'date', 'Unix time when job was submitted');
 150 INSERT INTO column_comments VALUES ('jobs', 'errors', 'Compiler errors');
 151 INSERT INTO column_comments VALUES ('jobs', 'extension', 'File extension of submitted program, without a leading dot');
 152 INSERT INTO column_comments VALUES ('jobs', 'format', 'Format (programming language) of submitted program');
 153 INSERT INTO column_comments VALUES ('jobs', 'result', 'Job result (integer constant from Gruntmaster::Daemon::Constants)');
 154 INSERT INTO column_comments VALUES ('jobs', 'result_text', 'Job result (human-readable text)');
 155 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))');
 156
 157 INSERT INTO column_comments VALUES ('problem_status', 'solved', 'True if the result is Accepted, False otherwise');