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 0daf3477ae3b7a08045a56c067ef1e6c5b663d6e..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,6 +41,7 @@ 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,
        statement TEXT    NOT NULL,
        testcnt   INT     NOT NULL,
        tests     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,