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         lastjob    BIGINT
  12 );
  13
  14 CREATE TABLE contests (
  15         id    TEXT PRIMARY KEY,
  16         name  TEXT NOT NULL,
  17         start INT  NOT NULL,
  18         stop  INT  NOT NULL,
  19         owner TEXT NOT NULL REFERENCES users ON DELETE CASCADE,
  20         CONSTRAINT positive_duration CHECK (stop > start)
  21 );
  22
  23 CREATE TABLE problems (
  24         id        TEXT      PRIMARY KEY,
  25         author    TEXT,
  26         writer    TEXT,
  27         generator TEXT    NOT NULL,
  28         judge     TEXT    NOT NULL,
  29         level     TEXT    NOT NULL,
  30         name      TEXT    NOT NULL,
  31         olimit    INT,
  32         owner     TEXT    NOT NULL REFERENCES users ON DELETE CASCADE,
  33         private   BOOLEAN NOT NULL DEFAULT FALSE,
  34         runner    TEXT    NOT NULL,
  35         statement TEXT    NOT NULL,
  36         testcnt   INT     NOT NULL,
  37         tests     TEXT,
  38         timeout   REAL    NOT NULL,
  39         value     INT,
  40         genformat TEXT,
  41         gensource TEXT,
  42         verformat TEXT,
  43         versource TEXT
  44 );
  45
  46 CREATE TABLE contest_problems (
  47         contest TEXT REFERENCES contests ON DELETE CASCADE,
  48         problem TEXT NOT NULL REFERENCES problems ON DELETE CASCADE,
  49         PRIMARY KEY (contest, problem)
  50 );
  51
  52 CREATE TABLE jobs (
  53         id          SERIAL  PRIMARY KEY,
  54         contest     TEXT    REFERENCES contests ON DELETE CASCADE,
  55         daemon      TEXT,
  56         date        BIGINT  NOT NULL,
  57         errors      TEXT,
  58         extension   TEXT    NOT NULL,
  59         format      TEXT    NOT NULL,
  60         private     BOOLEAN NOT NULL DEFAULT FALSE,
  61         problem     TEXT    NOT NULL REFERENCES problems ON DELETE CASCADE,
  62         result      INT,
  63         result_text TEXT,
  64         results     TEXT,
  65         source      TEXT    NOT NULL,
  66         owner       TEXT    NOT NULL REFERENCES users ON DELETE CASCADE
  67 );
  68
  69 CREATE TABLE opens (
  70         contest TEXT   NOT NULL REFERENCES contests ON DELETE CASCADE,
  71         problem TEXT   NOT NULL REFERENCES problems ON DELETE CASCADE,
  72         owner   TEXT   NOT NULL REFERENCES users ON DELETE CASCADE,
  73         time    BIGINT NOT NULL,
  74         PRIMARY KEY (contest, problem, owner)
  75 );