Bump version and update Changes

[gruntmaster-data.git] / db.sql

diff --git a/db.sql b/db.sql
index 107fa7195adf0426a8d3c9cfd2001163272ac395..a9f03c694aa185c6055b7ab03db1d6c25e64233c 100644 (file)
--- a/db.sql
+++ b/db.sql
@@ -1,4 +1,4 @@
-CREATE TABLE users (
+CREATE TABLE IF NOT EXISTS users (
        id         TEXT    PRIMARY KEY,
        passphrase TEXT,   -- NOT NULL,
        admin      BOOLEAN NOT NULL DEFAULT FALSE,
@@ -13,7 +13,7 @@ CREATE TABLE users (
        since      BIGINT DEFAULT CAST(EXTRACT(epoch from now()) AS bigint)
 );
 
-CREATE TABLE contests (
+CREATE TABLE IF NOT EXISTS contests (
        id          TEXT PRIMARY KEY,
        name        TEXT NOT NULL,
        editorial   TEXT,
@@ -24,7 +24,7 @@ CREATE TABLE contests (
        CONSTRAINT  positive_duration CHECK (stop > start)
 );
 
-CREATE TABLE contest_status (
+CREATE TABLE IF NOT EXISTS contest_status (
        contest TEXT NOT NULL REFERENCES contests ON DELETE CASCADE,
        owner   TEXT NOT NULL REFERENCES users ON DELETE CASCADE,
        score   INT  NOT NULL,
@@ -33,7 +33,7 @@ CREATE TABLE contest_status (
        PRIMARY KEY (owner, contest)
 );
 
-CREATE TABLE problems (
+CREATE TABLE IF NOT EXISTS problems (
        id        TEXT      PRIMARY KEY,
        author    TEXT,
        writer    TEXT,
@@ -58,17 +58,17 @@ CREATE TABLE problems (
        versource TEXT
 );
 
-CREATE TABLE contest_problems (
+CREATE TABLE IF NOT EXISTS contest_problems (
        contest TEXT REFERENCES contests ON DELETE CASCADE,
        problem TEXT NOT NULL REFERENCES problems ON DELETE CASCADE,
        PRIMARY KEY (contest, problem)
 );
 
-CREATE TABLE jobs (
+CREATE TABLE IF NOT EXISTS jobs (
        id          SERIAL  PRIMARY KEY,
        contest     TEXT    REFERENCES contests ON DELETE CASCADE,
        daemon      TEXT,
-       date        BIGINT  NOT NULL,
+       date        BIGINT  NOT NULL DEFAULT CAST(EXTRACT(epoch from now()) AS bigint),
        errors      TEXT,
        extension   TEXT    NOT NULL,
        format      TEXT    NOT NULL,
@@ -82,7 +82,7 @@ CREATE TABLE jobs (
        owner       TEXT    NOT NULL REFERENCES users ON DELETE CASCADE
 );
 
-CREATE TABLE problem_status (
+CREATE TABLE IF NOT EXISTS 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,
@@ -91,7 +91,7 @@ CREATE TABLE problem_status (
        PRIMARY KEY (owner, problem)
 );
 
-CREATE TABLE opens (
+CREATE TABLE IF NOT EXISTS opens (
        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,
@@ -99,7 +99,7 @@ CREATE TABLE opens (
        PRIMARY KEY (contest, problem, owner)
 );
 
-CREATE TABLE limits (
+CREATE TABLE IF NOT EXISTS limits (
        problem TEXT NOT NULL REFERENCES problems ON DELETE CASCADE,
        format  TEXT NOT NULL,
        timeout REAL NOT NULL,
@@ -161,40 +161,19 @@ CREATE OR REPLACE VIEW user_data AS (SELECT
        FROM users
 );
 
-CREATE OR REPLACE VIEW user_solved_problems AS (SELECT
-       us.id,
-       COALESCE(array_agg(ps.problem) FILTER (WHERE ps.problem IS NOT NULL), ARRAY[]::text[]) AS solved
-       FROM users us
-       LEFT JOIN (SELECT * FROM problem_status WHERE solved = TRUE) ps ON ps.owner=id
-       GROUP BY us.id);
-
-CREATE OR REPLACE VIEW user_attempted_problems AS (SELECT
-       us.id,
-       COALESCE(array_agg(ps.problem) FILTER (WHERE ps.problem IS NOT NULL), ARRAY[]::text[]) AS attempted
-       FROM users us
-       LEFT JOIN (SELECT * FROM problem_status WHERE solved = FALSE) ps ON ps.owner=id
-       GROUP BY us.id);
-
-CREATE OR REPLACE VIEW user_contests AS (SELECT
-       us.id,
-       COALESCE(array_agg(cs.contest) FILTER (WHERE cs.contest IS NOT NULL), ARRAY[]::text[]) AS contests
-       FROM users us
-       LEFT JOIN contest_status cs ON cs.owner=id
-       GROUP BY us.id);
-
 CREATE OR REPLACE VIEW user_list AS (SELECT
        dt.*,
-       COALESCE(array_length(solved, 1), 0) AS solved,
-       COALESCE(array_length(attempted, 1), 0) AS attempted,
-       COALESCE(array_length(contests, 1), 0) AS contests
+       COALESCE(solved, 0) as solved,
+       COALESCE(attempted, 0) as attempted,
+       COALESCE(contests, 0) as contests
        FROM user_data dt
-       JOIN user_contests ct USING (id)
-       JOIN user_solved_problems sp USING (id)
-       JOIN user_attempted_problems ap USING (id)
+       LEFT JOIN (SELECT owner as id, COUNT(*) as solved FROM problem_status WHERE solved=TRUE GROUP BY owner) ps USING (id)
+       LEFT JOIN (SELECT owner as id, COUNT(*) as attempted FROM problem_status WHERE solved=FALSE GROUP BY owner) pa USING (id)
+       LEFT JOIN (SELECT owner as id, COUNT(*) as contests FROM contest_status GROUP BY owner) ct USING (id)
        ORDER BY solved DESC, attempted DESC, id);
 
 CREATE OR REPLACE VIEW contest_entry AS (SELECT
-       id,name,description,editorial,start,stop,owner,
+       id,name,description,start,stop,owner,
        (EXTRACT(epoch from NOW()) >= start) AS started,
        (EXTRACT(epoch from NOW()) >= stop) AS finished
        FROM contests
@@ -205,18 +184,3 @@ CREATE OR REPLACE VIEW job_entry AS (SELECT
        LENGTH(source) AS size
        FROM jobs
        ORDER BY id DESC);
-
--- CREATE OR REPLACE FUCNTION source_private(jobid INT) RETURNS BOOLEAN AS $$
--- BEGIN
---     private BOOLEAN;
---     problem TEXT;
---     contest TEXT;
-
---     SELECT private, problem, contest INTO STRICT private, problem, contest FROM jobs WHERE id = jobid;
---     IF private THEN
---             RETURN TRUE;
---     END IF
-
---     IF 
--- END;
--- $$