Bump version and update Changes

[gruntmaster-data.git] / db.sql

diff --git a/db.sql b/db.sql
index 44e2420b88fd4ca354e6a9c4e68cf04c98d72c3f..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,


@@ -173,7 +173,7 @@ CREATE OR REPLACE VIEW user_list AS (SELECT
        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