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)
 );
 
        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,


@@ -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,
        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,
        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,
        genformat TEXT,
        gensource TEXT,
        verformat TEXT,


@@ -66,6 +76,15 @@ CREATE TABLE jobs (
        owner       TEXT    NOT NULL REFERENCES users ON DELETE CASCADE
 );
 
        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,
 CREATE TABLE opens (
        contest TEXT   NOT NULL REFERENCES contests ON DELETE CASCADE,
        problem TEXT   NOT NULL REFERENCES problems ON DELETE CASCADE,