Add problem_status, contest_status and a method that updates them

[gruntmaster-data.git] / db.sql

diff --git a/db.sql b/db.sql
index e4decc24a2008c9036aa32d96c32de5b593f8814..696cec175e2f0a3d5d7574dae80cee94cc271897 100644 (file)
--- a/db.sql
+++ b/db.sql
@@ -1,17 +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 (
 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,
        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
 );
 
        lastjob    BIGINT
 );
 


@@ -20,57 +16,79 @@ CREATE TABLE contests (
        name  TEXT NOT NULL,
        start INT  NOT NULL,
        stop  INT  NOT NULL,
        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)
 );
 
        CONSTRAINT positive_duration CHECK (stop > start)
 );
 

+CREATE TABLE contest_status (
+       contest TEXT NOT NULL REFERENCES contests ON DELETE CASCADE,
+       owner   TEXT NOT NULL REFERENCES users ON DELETE CASCADE,
+       score   INT  NOT NULL,
+       rank    INT  NOT NULL,
+
+       PRIMARY KEY (owner, contest)
+);
+

 CREATE TABLE problems (
        id        TEXT      PRIMARY KEY,
        author    TEXT,
 CREATE TABLE problems (
        id        TEXT      PRIMARY KEY,
        author    TEXT,

-       generator GENERATOR NOT NULL,
-       judge     JUDGE     NOT NULL,
-       level     PLEVEL    NOT NULL,
-       name      TEXT      NOT NULL,
+       writer    TEXT,
+       generator TEXT    NOT NULL,
+       judge     TEXT    NOT NULL,
+       level     TEXT    NOT NULL,
+       name      TEXT    NOT NULL,

        olimit    INT,
        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,
+       solution  TEXT ,
+       statement TEXT    NOT NULL,
+       testcnt   INT     NOT NULL,

        tests     TEXT,
        tests     TEXT,

-       timeout   REAL      NOT NULL,
-       value     INT,
+       timeout   REAL    NOT NULL,
+       value     INT     NOT NULL,
+       genformat TEXT,
+       gensource TEXT,

        verformat TEXT,
        versource TEXT
 );
 
 CREATE TABLE contest_problems (
        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,
        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,
        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     TEXT,
        source      TEXT    NOT NULL,
        result      INT,
        result_text TEXT,
        results     TEXT,
        source      TEXT    NOT NULL,

-       owner       TEXT    NOT NULL REFERENCES users
+       owner       TEXT    NOT NULL REFERENCES users ON DELETE CASCADE
+);
+
+CREATE TABLE problem_status (
+       problem TEXT    NOT NULL REFERENCES problems ON DELETE CASCADE,
+       owner   TEXT    NOT NULL REFERENCES users ON DELETE CASCADE,
+       job     SERIAL  NOT NULL REFERENCES jobs ON DELETE CASCADE,
+       solved  BOOLEAN NOT NULL DEFAULT FALSE,
+
+       PRIMARY KEY (owner, problem)

 );
 
 CREATE TABLE opens (
 );
 
 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)
 );
        time    BIGINT NOT NULL,
        PRIMARY KEY (contest, problem, owner)
 );