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 d06521796abc8adeb0df656d78490520dc21e9f7..696cec175e2f0a3d5d7574dae80cee94cc271897 100644 (file)
--- a/db.sql
+++ b/db.sql
@@ -20,6 +20,15 @@ CREATE TABLE contests (
        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,
@@ -32,11 +41,12 @@ CREATE TABLE problems (
        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,
+       value     INT     NOT NULL,
        genformat TEXT,
        gensource TEXT,
        verformat TEXT,
@@ -66,6 +76,15 @@ CREATE TABLE jobs (
        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 ON DELETE CASCADE,
        problem TEXT   NOT NULL REFERENCES problems ON DELETE CASCADE,