-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,
PRIMARY KEY (problem, format)
);
-CREATE TABLE table_comments (
- table_name TEXT NOT NULL PRIMARY KEY,
- comment_text TEXT NOT NULL
-);
-
-CREATE TABLE column_comments (
- table_name TEXT NOT NULL,
- column_name TEXT NOT NULL,
- comment_text TEXT NOT NULL,
- PRIMARY KEY (table_name, column_name)
-);
-
-INSERT INTO table_comments VALUES ('users', 'List of users');
-INSERT INTO table_comments VALUES ('contests', 'List of contests');
-INSERT INTO table_comments VALUES ('contest_status', 'List of (contest, user, result)');
-INSERT INTO table_comments VALUES ('problems', 'List of problems');
-INSERT INTO table_comments VALUES ('contest_problems', 'Many-to-many bridge between contests and problems');
-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');
-INSERT INTO column_comments VALUES ('users', 'level', 'Highschool, Undergraduate, Master, Doctorate or Other');
-INSERT INTO column_comments VALUES ('users', 'lastjob', 'Unix time when this user last submitted a job');
-INSERT INTO column_comments VALUES ('users', 'since', 'Unix time when this user was created');
-
-INSERT INTO column_comments VALUES ('contests', 'start', 'Unix time when contest starts');
-INSERT INTO column_comments VALUES ('contests', 'stop', 'Unix time when contest ends');
-INSERT INTO column_comments VALUES ('contests', 'editorial', 'HTML fragment placed before the editorial');
-INSERT INTO column_comments VALUES ('contests', 'description', 'HTML fragment placed on contest page');
-
-INSERT INTO column_comments VALUES ('problems', 'author', 'Full name(s) of problem author(s)/problemsetter(s)/tester(s)/etc');
-INSERT INTO column_comments VALUES ('problems', 'writer', 'Full name(s) of statement writer(s) (DEPRECATED)');
-INSERT INTO column_comments VALUES ('problems', 'generator', 'Generator class, without the leading Gruntmaster::Daemon::Generator::');
-INSERT INTO column_comments VALUES ('problems', 'runner', 'Runner class, without the leading Gruntmaster::Daemon::Runner::');
-INSERT INTO column_comments VALUES ('problems', 'judge', 'Judge class, without the leading Gruntmaster::Daemon::Judge::');
-INSERT INTO column_comments VALUES ('problems', 'level', 'Problem level, one of beginner, easy, medium, hard');
-INSERT INTO column_comments VALUES ('problems', 'olimit', 'Output limit (in bytes)');
-INSERT INTO column_comments VALUES ('problems', 'timeout', 'Time limit (in seconds)');
-INSERT INTO column_comments VALUES ('problems', 'solution', 'Solution (HTML)');
-INSERT INTO column_comments VALUES ('problems', 'statement', 'Statement (HTML)');
-INSERT INTO column_comments VALUES ('problems', 'testcnt', 'Number of tests');
-INSERT INTO column_comments VALUES ('problems', 'precnt', 'Number of pretests. NULL indicates full feedback.');
-INSERT INTO column_comments VALUES ('problems', 'tests', 'JSON array of test values for ::Runner::File');
-INSERT INTO column_comments VALUES ('problems', 'value', 'Problem value when used in a contest.');
-INSERT INTO column_comments VALUES ('problems', 'genformat', 'Format (programming language) of the generator if using the Run generator');
-INSERT INTO column_comments VALUES ('problems', 'gensource', 'Source code of generator if using the Run generator');
-INSERT INTO column_comments VALUES ('problems', 'verformat', 'Format (programming language) of the verifier if using the Verifier runner');
-INSERT INTO column_comments VALUES ('problems', 'versource', 'Source code of verifier if using the Verifier runner');
-
-INSERT INTO column_comments VALUES ('jobs', 'daemon', 'hostname:PID of daemon that last executed this job. NULL if never executed');
-INSERT INTO column_comments VALUES ('jobs', 'date', 'Unix time when job was submitted');
-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');
+COMMENT ON TABLE users IS 'List of users';
+COMMENT ON TABLE contests IS 'List of contests';
+COMMENT ON TABLE contest_status IS 'List of (contest, user, result)';
+COMMENT ON TABLE problems IS 'List of problems';
+COMMENT ON TABLE contest_problems IS 'Many-to-many bridge between contests and problems';
+COMMENT ON TABLE jobs IS 'List of jobs';
+COMMENT ON TABLE problem_status IS 'List of (problem, user, result)';
+COMMENT ON TABLE opens IS 'List of (contest, problem, user, time when user opened problem)';
+COMMENT ON TABLE limits IS 'Time limit overrides for certain problem/format pairs';
+
+COMMENT ON COLUMN users.passphrase IS 'RFC2307-encoded passphrase';
+COMMENT ON COLUMN users.name IS 'Full name of user';
+COMMENT ON COLUMN users.level IS 'Highschool, Undergraduate, Master, Doctorate or Other';
+COMMENT ON COLUMN users.lastjob IS 'Unix time when this user last submitted a job';
+COMMENT ON COLUMN users.since IS 'Unix time when this user was created';
+
+COMMENT ON COLUMN contests.start IS 'Unix time when contest starts';
+COMMENT ON COLUMN contests.stop IS 'Unix time when contest ends';
+COMMENT ON COLUMN contests.editorial IS 'HTML fragment placed before the editorial';
+COMMENT ON COLUMN contests.description IS 'HTML fragment placed on contest page';
+
+COMMENT ON COLUMN problems.author IS 'Full name(s) of problem author(s)/problemsetter(s)/tester(s)/etc';
+COMMENT ON COLUMN problems.writer IS 'Full name(s) of statement writer(s) (DEPRECATED)';
+COMMENT ON COLUMN problems.generator IS 'Generator class, without the leading Gruntmaster::Daemon::Generator::';
+COMMENT ON COLUMN problems.runner IS 'Runner class, without the leading Gruntmaster::Daemon::Runner::';
+COMMENT ON COLUMN problems.judge IS 'Judge class, without the leading Gruntmaster::Daemon::Judge::';
+COMMENT ON COLUMN problems.level IS 'Problem level, one of beginner, easy, medium, hard';
+COMMENT ON COLUMN problems.olimit IS 'Output limit (in bytes)';
+COMMENT ON COLUMN problems.timeout IS 'Time limit (in seconds)';
+COMMENT ON COLUMN problems.solution IS 'Solution (HTML)';
+COMMENT ON COLUMN problems.statement IS 'Statement (HTML)';
+COMMENT ON COLUMN problems.testcnt IS 'Number of tests';
+COMMENT ON COLUMN problems.precnt IS 'Number of pretests. NULL indicates full feedback.';
+COMMENT ON COLUMN problems.tests IS 'JSON array of test values for ::Runner::File';
+COMMENT ON COLUMN problems.value IS 'Problem value when used in a contest.';
+COMMENT ON COLUMN problems.genformat IS 'Format (programming language) of the generator if using the Run generator';
+COMMENT ON COLUMN problems.gensource IS 'Source code of generator if using the Run generator';
+COMMENT ON COLUMN problems.verformat IS 'Format (programming language) of the verifier if using the Verifier runner';
+COMMENT ON COLUMN problems.versource IS 'Source code of verifier if using the Verifier runner';
+
+COMMENT ON COLUMN jobs.daemon IS 'hostname:PID of daemon that last executed this job. NULL if never executed';
+COMMENT ON COLUMN jobs.date IS 'Unix time when job was submitted';
+COMMENT ON COLUMN jobs.errors IS 'Compiler errors';
+COMMENT ON COLUMN jobs.extension IS 'File extension of submitted program, without a leading dot';
+COMMENT ON COLUMN jobs.format IS 'Format (programming language) of submitted program';
+COMMENT ON COLUMN jobs.reference IS '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';
+COMMENT ON COLUMN jobs.result IS 'Job result (integer constant from Gruntmaster::Daemon::Constants)';
+COMMENT ON COLUMN jobs.result_text IS 'Job result (human-readable text)';
+COMMENT ON COLUMN jobs.results IS '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))';
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
+ 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;
--- $$
projects / gruntmaster-data.git / blobdiff