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,
        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)
 );
 
        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,
        id          TEXT PRIMARY KEY,
        name        TEXT NOT NULL,
        editorial   TEXT,


@@ -24,7 +24,7 @@ CREATE TABLE contests (
        CONSTRAINT  positive_duration CHECK (stop > start)
 );
 
        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,
        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)
 );
 
        PRIMARY KEY (owner, contest)
 );
 

-CREATE TABLE problems (
+CREATE TABLE IF NOT EXISTS problems (

        id        TEXT      PRIMARY KEY,
        author    TEXT,
        writer    TEXT,
        id        TEXT      PRIMARY KEY,
        author    TEXT,
        writer    TEXT,


@@ -58,17 +58,17 @@ CREATE TABLE problems (
        versource TEXT
 );
 
        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)
 );
 
        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,
        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,
        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
 );
 
        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,
        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)
 );
 
        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,
        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)
 );
 
        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,
        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
 );
 
        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.*,
 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
        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
        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
        (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);
        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;
--- $$