Remove DBIC

[gruntmaster-data.git] / db.sql

diff --git a/db.sql b/db.sql
index 706265ea6b49f0eff9ce6f6c220b6e74ad86d0e6..f9fc0f37edc26a243960e1027204c2447e7f7bed 100644 (file)
--- a/db.sql
+++ b/db.sql
@@ -74,6 +74,7 @@ CREATE TABLE jobs (
        format      TEXT    NOT NULL,
        private     BOOLEAN NOT NULL DEFAULT FALSE,
        problem     TEXT    NOT NULL REFERENCES problems ON DELETE CASCADE,
+       reference   INT,
        result      INT,
        result_text TEXT,
        results     TEXT,
@@ -98,6 +99,13 @@ CREATE TABLE opens (
        PRIMARY KEY (contest, problem, owner)
 );
 
+CREATE TABLE limits (
+       problem TEXT NOT NULL REFERENCES problems ON DELETE CASCADE,
+       format  TEXT NOT NULL,
+       timeout REAL NOT NULL,
+       PRIMARY KEY (problem, format)
+);
+
 CREATE TABLE table_comments (
        table_name   TEXT NOT NULL PRIMARY KEY,
        comment_text TEXT NOT NULL
@@ -118,6 +126,7 @@ INSERT INTO table_comments VALUES ('contest_problems', 'Many-to-many bridge betw
 INSERT INTO table_comments VALUES ('jobs',   'List of jobs');
 INSERT INTO table_comments VALUES ('problem_status', 'List of (problem, user, result)');
 INSERT INTO table_comments VALUES ('opens', 'List of (contest, problem, user, time when user opened problem)');
+INSERT INTO table_comments VALUES ('limits', 'Time limit overrides for certain problem/format pairs');
 
 INSERT INTO column_comments VALUES ('users', 'passphrase', 'RFC2307-encoded passphrase');
 INSERT INTO column_comments VALUES ('users', 'name', 'Full name of user');
@@ -154,8 +163,74 @@ INSERT INTO column_comments VALUES ('jobs', 'date', 'Unix time when job was subm
 INSERT INTO column_comments VALUES ('jobs', 'errors', 'Compiler errors');
 INSERT INTO column_comments VALUES ('jobs', 'extension', 'File extension of submitted program, without a leading dot');
 INSERT INTO column_comments VALUES ('jobs', 'format', 'Format (programming language) of submitted program');
+INSERT INTO column_comments VALUES ('jobs', 'reference', 'If not null, this is a reference solution that should get this result. For example, set reference=0 on jobs that should be accepted, reference=3 on jobs that should get TLE, etc');
 INSERT INTO column_comments VALUES ('jobs', 'result', 'Job result (integer constant from Gruntmaster::Daemon::Constants)');
 INSERT INTO column_comments VALUES ('jobs', 'result_text', 'Job result (human-readable text)');
 INSERT INTO column_comments VALUES ('jobs', 'results', 'Per-test results (JSON array of hashes with keys id (test number, counting from 1), result (integer constant from Gruntmaster::Daemon::Constants), result_text (human-readable text), time (execution time in decimal seconds))');
 
 INSERT INTO column_comments VALUES ('problem_status', 'solved', 'True if the result is Accepted, False otherwise');
+
+CREATE OR REPLACE VIEW user_data AS (SELECT
+       id,admin,name,town,university,country,level,lastjob
+       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
+       FROM user_data dt
+       JOIN user_contests ct USING (id)
+       JOIN user_solved_problems sp USING (id)
+       JOIN user_attempted_problems ap USING (id)
+       ORDER BY solved DESC, attempted DESC, id);
+
+CREATE OR REPLACE VIEW contest_entry AS (SELECT
+       id,name,description,editorial,start,stop,owner,
+       (EXTRACT(epoch from NOW()) >= start) AS started,
+       (EXTRACT(epoch from NOW()) >= stop) AS finished
+       FROM contests
+       ORDER BY start DESC);
+
+CREATE OR REPLACE VIEW job_entry AS (SELECT
+       id,contest,date,errors,extension,format,private,problem,result,result_text,results,owner,
+       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;
+-- $$