-CREATE TABLE users (
+CREATE TABLE IF NOT EXISTS users (
id TEXT PRIMARY KEY,
passphrase TEXT, -- NOT NULL,
admin BOOLEAN NOT NULL DEFAULT FALSE,
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,
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,
PRIMARY KEY (owner, contest)
);
-CREATE TABLE problems (
+CREATE TABLE IF NOT EXISTS problems (
id TEXT PRIMARY KEY,
author TEXT,
writer 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)
);
-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,
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,
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,
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,
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
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;
--- $$