X-Git-Url: http://git.ieval.ro/?a=blobdiff_plain;f=db.sql;h=696cec175e2f0a3d5d7574dae80cee94cc271897;hb=adb42d4d148fdb3dd145e4a7452b7b7eda88f66c;hp=5992b412da70e5e0b206f1eb30500027a778b059;hpb=4ed3f8e7c64594bb4ea26abb1c4fb51a5a8258d6;p=gruntmaster-data.git

diff --git a/db.sql b/db.sql
index 5992b41..696cec1 100644
--- 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,56 +16,79 @@ 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 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      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,
-	timeout   REAL      NOT NULL,
-	value     INT,
+	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,
+	timeout   REAL    NOT NULL,
+	value     INT     NOT NULL,
+	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 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 (
-	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)
 );