X-Git-Url: http://git.ieval.ro/?a=blobdiff_plain;f=db.sql;h=a9f03c694aa185c6055b7ab03db1d6c25e64233c;hb=dcf7f640534d6ad6d0273ac5b8bb1e9892fcaa56;hp=f9fc0f37edc26a243960e1027204c2447e7f7bed;hpb=52172a1a697be94c99123f265ebcd64871d6379e;p=gruntmaster-data.git diff --git a/db.sql b/db.sql index f9fc0f3..a9f03c6 100644 --- 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, @@ -13,7 +13,7 @@ CREATE TABLE users ( 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, @@ -24,7 +24,7 @@ CREATE TABLE contests ( 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, @@ -33,7 +33,7 @@ CREATE TABLE contest_status ( PRIMARY KEY (owner, contest) ); -CREATE TABLE problems ( +CREATE TABLE IF NOT EXISTS problems ( id TEXT PRIMARY KEY, author TEXT, writer TEXT, @@ -58,17 +58,17 @@ CREATE TABLE problems ( 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, @@ -82,7 +82,7 @@ CREATE TABLE jobs ( 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, @@ -91,7 +91,7 @@ CREATE TABLE problem_status ( 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, @@ -99,116 +99,81 @@ CREATE TABLE opens ( 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 @@ -219,18 +184,3 @@ CREATE OR REPLACE VIEW job_entry AS (SELECT 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; --- $$