Use ON DELETE CASCADE and TEXT instead of enums

[gruntmaster-data.git] / db.sql

diff --git a/db.sql b/db.sql
index 6f15c01af62050e110921fd887516d0bad900104..d06521796abc8adeb0df656d78490520dc21e9f7 100644 (file)
--- a/db.sql
+++ b/db.sql
@@ -1,19 +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,
-       passphrase TEXT    NOT NULL,
+       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
 );
 
@@ -22,7 +16,7 @@ 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)
 );
 
@@ -30,18 +24,18 @@ CREATE TABLE problems (
        id        TEXT      PRIMARY KEY,
        author    TEXT,
        writer    TEXT,
-       generator GENERATOR NOT NULL,
-       judge     JUDGE     NOT NULL,
-       level     PLEVEL    NOT NULL,
-       name      TEXT      NOT NULL,
+       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,
@@ -50,32 +44,32 @@ CREATE TABLE problems (
 );
 
 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     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)
 );