Bump version and update Changes

[gruntmaster-data.git] / db.sql

diff --git a/db.sql b/db.sql
index eafd89ed66f0f79870200ef168a763e3a3e744c8..d06521796abc8adeb0df656d78490520dc21e9f7 100644 (file)
--- a/db.sql
+++ b/db.sql
@@ -1,16 +1,13 @@
-CREATE TYPE ULEVEL    AS ENUM ('Highschool', 'Undergraduate', 'Master', 'Doctorate', 'Other');
-CREATE TYPE PLEVEL    AS ENUM ('beginner', 'easy', 'medium', 'hard');
-CREATE TYPE GENERATOR AS ENUM ('File', 'Run', 'Undef');
-CREATE TYPE RUNNER    AS ENUM ('File', 'Verifier', 'Interactive');
-CREATE TYPE JUDGE     AS ENUM ('Absolute', 'Points');
-
 CREATE TABLE users (
-       id         TEXT  PRIMARY KEY,
+       id         TEXT    PRIMARY KEY,
+       passphrase TEXT,   -- NOT NULL,
+       admin      BOOLEAN NOT NULL DEFAULT FALSE,
        name       TEXT,  -- NOT NULL,
        email      TEXT,  -- NOT NULL,
+       phone      TEXT,  -- NOT NULL,
        town       TEXT,  -- NOT NULL,
        university TEXT,  -- NOT NULL,
-       level      TEXT,  --ULEVEL NOT NULL,
+       level      TEXT,  -- NOT NULL,
        lastjob    BIGINT
 );
 
@@ -19,57 +16,60 @@ CREATE TABLE contests (
        name  TEXT NOT NULL,
        start INT  NOT NULL,
        stop  INT  NOT NULL,
-       owner TEXT NOT NULL REFERENCES users,
+       owner TEXT NOT NULL REFERENCES users ON DELETE CASCADE,
        CONSTRAINT positive_duration CHECK (stop > start)
 );
 
 CREATE TABLE problems (
        id        TEXT      PRIMARY KEY,
-       author    TEXT      NOT NULL,
-       generator GENERATOR NOT NULL,
-       judge     JUDGE     NOT NULL,
-       level     PLEVEL    NOT NULL,
-       name      TEXT      NOT NULL,
+       author    TEXT,
+       writer    TEXT,
+       generator TEXT    NOT NULL,
+       judge     TEXT    NOT NULL,
+       level     TEXT    NOT NULL,
+       name      TEXT    NOT NULL,
        olimit    INT,
-       owner     TEXT      NOT NULL REFERENCES users,
-       private   BOOLEAN   NOT NULL DEFAULT FALSE,
-       runner    RUNNER    NOT NULL,
-       statement TEXT      NOT NULL,
-       testcnt   INT       NOT NULL,
+       owner     TEXT    NOT NULL REFERENCES users ON DELETE CASCADE,
+       private   BOOLEAN NOT NULL DEFAULT FALSE,
+       runner    TEXT    NOT NULL,
+       statement TEXT    NOT NULL,
+       testcnt   INT     NOT NULL,
        tests     TEXT,
-       timeout   REAL      NOT NULL,
+       timeout   REAL    NOT NULL,
        value     INT,
+       genformat TEXT,
+       gensource TEXT,
        verformat TEXT,
        versource TEXT
 );
 
 CREATE TABLE contest_problems (
-       contest TEXT REFERENCES contests,
-       problem TEXT NOT NULL REFERENCES problems,
+       contest TEXT REFERENCES contests ON DELETE CASCADE,
+       problem TEXT NOT NULL REFERENCES problems ON DELETE CASCADE,
        PRIMARY KEY (contest, problem)
 );
 
 CREATE TABLE jobs (
        id          SERIAL  PRIMARY KEY,
-       contest     TEXT    REFERENCES contests,
+       contest     TEXT    REFERENCES contests ON DELETE CASCADE,
        daemon      TEXT,
        date        BIGINT  NOT NULL,
        errors      TEXT,
        extension   TEXT    NOT NULL,
        format      TEXT    NOT NULL,
        private     BOOLEAN NOT NULL DEFAULT FALSE,
-       problem     TEXT    NOT NULL REFERENCES problems,
+       problem     TEXT    NOT NULL REFERENCES problems ON DELETE CASCADE,
        result      INT,
        result_text TEXT,
-       results     JSON,
+       results     TEXT,
        source      TEXT    NOT NULL,
-       owner       TEXT    NOT NULL REFERENCES users
+       owner       TEXT    NOT NULL REFERENCES users ON DELETE CASCADE
 );
 
 CREATE TABLE opens (
-       contest TEXT   NOT NULL REFERENCES contests,
-       problem TEXT   NOT NULL REFERENCES problems,
-       owner   TEXT   NOT NULL REFERENCES users,
+       contest TEXT   NOT NULL REFERENCES contests ON DELETE CASCADE,
+       problem TEXT   NOT NULL REFERENCES problems ON DELETE CASCADE,
+       owner   TEXT   NOT NULL REFERENCES users ON DELETE CASCADE,
        time    BIGINT NOT NULL,
        PRIMARY KEY (contest, problem, owner)
 );