[gruntmaster-data.git] / db.sql

CREATE TABLE users (
	id         TEXT    PRIMARY KEY,
	passphrase TEXT,   -- NOT NULL,
	admin      BOOLEAN NOT NULL DEFAULT FALSE,
	name       TEXT,  -- NOT NULL,
	email      TEXT,  -- NOT NULL,
	phone      TEXT,  -- NOT NULL,
	town       TEXT,  -- NOT NULL,
	university TEXT,  -- NOT NULL,
	level      TEXT,  -- NOT NULL,
	country    TEXT,
	lastjob    BIGINT,
	since      BIGINT DEFAULT CAST(EXTRACT(epoch from now()) AS bigint)
);

CREATE TABLE contests (
	id          TEXT PRIMARY KEY,
	name        TEXT NOT NULL,
	editorial   TEXT,
	description TEXT,
	start       INT  NOT NULL,
	stop        INT  NOT NULL,
	owner       TEXT NOT NULL REFERENCES users ON DELETE CASCADE,
	CONSTRAINT  positive_duration CHECK (stop > start)
);

CREATE TABLE contest_status (
	contest TEXT NOT NULL REFERENCES contests ON DELETE CASCADE,
	owner   TEXT NOT NULL REFERENCES users ON DELETE CASCADE,
	score   INT  NOT NULL,
	rank    INT  NOT NULL,

	PRIMARY KEY (owner, contest)
);

CREATE TABLE problems (
	id        TEXT      PRIMARY KEY,
	author    TEXT,
	writer    TEXT,
	generator TEXT    NOT NULL,
	judge     TEXT    NOT NULL,
	level     TEXT    NOT NULL,
	name      TEXT    NOT NULL,
	olimit    INT,
	owner     TEXT    NOT NULL REFERENCES users ON DELETE CASCADE,
	private   BOOLEAN NOT NULL DEFAULT FALSE,
	runner    TEXT    NOT NULL,
	solution  TEXT ,
	statement TEXT    NOT NULL,
	testcnt   INT     NOT NULL,
	precnt    INT,
	tests     TEXT,
	timeout   REAL    NOT NULL,
	value     INT     NOT NULL,
	genformat TEXT,
	gensource TEXT,
	verformat TEXT,
	versource TEXT
);

CREATE TABLE 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 (
	id          SERIAL  PRIMARY KEY,
	contest     TEXT    REFERENCES contests ON DELETE CASCADE,
	daemon      TEXT,
	date        BIGINT  NOT NULL,
	errors      TEXT,
	extension   TEXT    NOT NULL,
	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,
	source      TEXT    NOT NULL,
	owner       TEXT    NOT NULL REFERENCES users ON DELETE CASCADE
);

CREATE TABLE 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,
	solved  BOOLEAN NOT NULL DEFAULT FALSE,

	PRIMARY KEY (owner, problem)
);

CREATE TABLE 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,
	time    BIGINT NOT NULL,
	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
);

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');

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;
-- $$
Commit	Line	Data
	1	CREATE TABLE users (
	2	id TEXT PRIMARY KEY,
	3	passphrase TEXT, -- NOT NULL,
	4	admin BOOLEAN NOT NULL DEFAULT FALSE,
	5	name TEXT, -- NOT NULL,
	6	email TEXT, -- NOT NULL,
	7	phone TEXT, -- NOT NULL,
	8	town TEXT, -- NOT NULL,
	9	university TEXT, -- NOT NULL,
	10	level TEXT, -- NOT NULL,
	11	country TEXT,
	12	lastjob BIGINT,
	13	since BIGINT DEFAULT CAST(EXTRACT(epoch from now()) AS bigint)
	14	);
	15
	16	CREATE TABLE contests (
	17	id TEXT PRIMARY KEY,
	18	name TEXT NOT NULL,
	19	editorial TEXT,
	20	description TEXT,
	21	start INT NOT NULL,
	22	stop INT NOT NULL,
	23	owner TEXT NOT NULL REFERENCES users ON DELETE CASCADE,
	24	CONSTRAINT positive_duration CHECK (stop > start)
	25	);
	26
	27	CREATE TABLE contest_status (
	28	contest TEXT NOT NULL REFERENCES contests ON DELETE CASCADE,
	29	owner TEXT NOT NULL REFERENCES users ON DELETE CASCADE,
	30	score INT NOT NULL,
	31	rank INT NOT NULL,
	32
	33	PRIMARY KEY (owner, contest)
	34	);
	35
	36	CREATE TABLE problems (
	37	id TEXT PRIMARY KEY,
	38	author TEXT,
	39	writer TEXT,
	40	generator TEXT NOT NULL,
	41	judge TEXT NOT NULL,
	42	level TEXT NOT NULL,
	43	name TEXT NOT NULL,
	44	olimit INT,
	45	owner TEXT NOT NULL REFERENCES users ON DELETE CASCADE,
	46	private BOOLEAN NOT NULL DEFAULT FALSE,
	47	runner TEXT NOT NULL,
	48	solution TEXT ,
	49	statement TEXT NOT NULL,
	50	testcnt INT NOT NULL,
	51	precnt INT,
	52	tests TEXT,
	53	timeout REAL NOT NULL,
	54	value INT NOT NULL,
	55	genformat TEXT,
	56	gensource TEXT,
	57	verformat TEXT,
	58	versource TEXT
	59	);
	60
	61	CREATE TABLE contest_problems (
	62	contest TEXT REFERENCES contests ON DELETE CASCADE,
	63	problem TEXT NOT NULL REFERENCES problems ON DELETE CASCADE,
	64	PRIMARY KEY (contest, problem)
	65	);
	66
	67	CREATE TABLE jobs (
	68	id SERIAL PRIMARY KEY,
	69	contest TEXT REFERENCES contests ON DELETE CASCADE,
	70	daemon TEXT,
	71	date BIGINT NOT NULL,
	72	errors TEXT,
	73	extension TEXT NOT NULL,
	74	format TEXT NOT NULL,
	75	private BOOLEAN NOT NULL DEFAULT FALSE,
	76	problem TEXT NOT NULL REFERENCES problems ON DELETE CASCADE,
	77	reference INT,
	78	result INT,
	79	result_text TEXT,
	80	results TEXT,
	81	source TEXT NOT NULL,
	82	owner TEXT NOT NULL REFERENCES users ON DELETE CASCADE
	83	);
	84
	85	CREATE TABLE problem_status (
	86	problem TEXT NOT NULL REFERENCES problems ON DELETE CASCADE,
	87	owner TEXT NOT NULL REFERENCES users ON DELETE CASCADE,
	88	job SERIAL NOT NULL REFERENCES jobs ON DELETE CASCADE,
	89	solved BOOLEAN NOT NULL DEFAULT FALSE,
	90
	91	PRIMARY KEY (owner, problem)
	92	);
	93
	94	CREATE TABLE opens (
	95	contest TEXT NOT NULL REFERENCES contests ON DELETE CASCADE,
	96	problem TEXT NOT NULL REFERENCES problems ON DELETE CASCADE,
	97	owner TEXT NOT NULL REFERENCES users ON DELETE CASCADE,
	98	time BIGINT NOT NULL,
	99	PRIMARY KEY (contest, problem, owner)
	100	);
	101
	102	CREATE TABLE limits (
	103	problem TEXT NOT NULL REFERENCES problems ON DELETE CASCADE,
	104	format TEXT NOT NULL,
	105	timeout REAL NOT NULL,
	106	PRIMARY KEY (problem, format)
	107	);
	108
	109	CREATE TABLE table_comments (
	110	table_name TEXT NOT NULL PRIMARY KEY,
	111	comment_text TEXT NOT NULL
	112	);
	113
	114	CREATE TABLE column_comments (
	115	table_name TEXT NOT NULL,
	116	column_name TEXT NOT NULL,
	117	comment_text TEXT NOT NULL,
	118	PRIMARY KEY (table_name, column_name)
	119	);
	120
	121	INSERT INTO table_comments VALUES ('users', 'List of users');
	122	INSERT INTO table_comments VALUES ('contests', 'List of contests');
	123	INSERT INTO table_comments VALUES ('contest_status', 'List of (contest, user, result)');
	124	INSERT INTO table_comments VALUES ('problems', 'List of problems');
	125	INSERT INTO table_comments VALUES ('contest_problems', 'Many-to-many bridge between contests and problems');
	126	INSERT INTO table_comments VALUES ('jobs', 'List of jobs');
	127	INSERT INTO table_comments VALUES ('problem_status', 'List of (problem, user, result)');
	128	INSERT INTO table_comments VALUES ('opens', 'List of (contest, problem, user, time when user opened problem)');
	129	INSERT INTO table_comments VALUES ('limits', 'Time limit overrides for certain problem/format pairs');
	130
	131	INSERT INTO column_comments VALUES ('users', 'passphrase', 'RFC2307-encoded passphrase');
	132	INSERT INTO column_comments VALUES ('users', 'name', 'Full name of user');
	133	INSERT INTO column_comments VALUES ('users', 'level', 'Highschool, Undergraduate, Master, Doctorate or Other');
	134	INSERT INTO column_comments VALUES ('users', 'lastjob', 'Unix time when this user last submitted a job');
	135	INSERT INTO column_comments VALUES ('users', 'since', 'Unix time when this user was created');
	136
	137	INSERT INTO column_comments VALUES ('contests', 'start', 'Unix time when contest starts');
	138	INSERT INTO column_comments VALUES ('contests', 'stop', 'Unix time when contest ends');
	139	INSERT INTO column_comments VALUES ('contests', 'editorial', 'HTML fragment placed before the editorial');
	140	INSERT INTO column_comments VALUES ('contests', 'description', 'HTML fragment placed on contest page');
	141
	142	INSERT INTO column_comments VALUES ('problems', 'author', 'Full name(s) of problem author(s)/problemsetter(s)/tester(s)/etc');
	143	INSERT INTO column_comments VALUES ('problems', 'writer', 'Full name(s) of statement writer(s) (DEPRECATED)');
	144	INSERT INTO column_comments VALUES ('problems', 'generator', 'Generator class, without the leading Gruntmaster::Daemon::Generator::');
	145	INSERT INTO column_comments VALUES ('problems', 'runner', 'Runner class, without the leading Gruntmaster::Daemon::Runner::');
	146	INSERT INTO column_comments VALUES ('problems', 'judge', 'Judge class, without the leading Gruntmaster::Daemon::Judge::');
	147	INSERT INTO column_comments VALUES ('problems', 'level', 'Problem level, one of beginner, easy, medium, hard');
	148	INSERT INTO column_comments VALUES ('problems', 'olimit', 'Output limit (in bytes)');
	149	INSERT INTO column_comments VALUES ('problems', 'timeout', 'Time limit (in seconds)');
	150	INSERT INTO column_comments VALUES ('problems', 'solution', 'Solution (HTML)');
	151	INSERT INTO column_comments VALUES ('problems', 'statement', 'Statement (HTML)');
	152	INSERT INTO column_comments VALUES ('problems', 'testcnt', 'Number of tests');
	153	INSERT INTO column_comments VALUES ('problems', 'precnt', 'Number of pretests. NULL indicates full feedback.');
	154	INSERT INTO column_comments VALUES ('problems', 'tests', 'JSON array of test values for ::Runner::File');
	155	INSERT INTO column_comments VALUES ('problems', 'value', 'Problem value when used in a contest.');
	156	INSERT INTO column_comments VALUES ('problems', 'genformat', 'Format (programming language) of the generator if using the Run generator');
	157	INSERT INTO column_comments VALUES ('problems', 'gensource', 'Source code of generator if using the Run generator');
	158	INSERT INTO column_comments VALUES ('problems', 'verformat', 'Format (programming language) of the verifier if using the Verifier runner');
	159	INSERT INTO column_comments VALUES ('problems', 'versource', 'Source code of verifier if using the Verifier runner');
	160
	161	INSERT INTO column_comments VALUES ('jobs', 'daemon', 'hostname:PID of daemon that last executed this job. NULL if never executed');
	162	INSERT INTO column_comments VALUES ('jobs', 'date', 'Unix time when job was submitted');
	163	INSERT INTO column_comments VALUES ('jobs', 'errors', 'Compiler errors');
	164	INSERT INTO column_comments VALUES ('jobs', 'extension', 'File extension of submitted program, without a leading dot');
	165	INSERT INTO column_comments VALUES ('jobs', 'format', 'Format (programming language) of submitted program');
	166	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');
	167	INSERT INTO column_comments VALUES ('jobs', 'result', 'Job result (integer constant from Gruntmaster::Daemon::Constants)');
	168	INSERT INTO column_comments VALUES ('jobs', 'result_text', 'Job result (human-readable text)');
	169	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))');
	170
	171	INSERT INTO column_comments VALUES ('problem_status', 'solved', 'True if the result is Accepted, False otherwise');
	172
	173	CREATE OR REPLACE VIEW user_data AS (SELECT
	174	id,admin,name,town,university,country,level,lastjob
	175	FROM users
	176	);
	177
	178	CREATE OR REPLACE VIEW user_solved_problems AS (SELECT
	179	us.id,
	180	COALESCE(array_agg(ps.problem) FILTER (WHERE ps.problem IS NOT NULL), ARRAY[]::text[]) AS solved
	181	FROM users us
	182	LEFT JOIN (SELECT * FROM problem_status WHERE solved = TRUE) ps ON ps.owner=id
	183	GROUP BY us.id);
	184
	185	CREATE OR REPLACE VIEW user_attempted_problems AS (SELECT
	186	us.id,
	187	COALESCE(array_agg(ps.problem) FILTER (WHERE ps.problem IS NOT NULL), ARRAY[]::text[]) AS attempted
	188	FROM users us
	189	LEFT JOIN (SELECT * FROM problem_status WHERE solved = FALSE) ps ON ps.owner=id
	190	GROUP BY us.id);
	191
	192	CREATE OR REPLACE VIEW user_contests AS (SELECT
	193	us.id,
	194	COALESCE(array_agg(cs.contest) FILTER (WHERE cs.contest IS NOT NULL), ARRAY[]::text[]) AS contests
	195	FROM users us
	196	LEFT JOIN contest_status cs ON cs.owner=id
	197	GROUP BY us.id);
	198
	199	CREATE OR REPLACE VIEW user_list AS (SELECT
	200	dt.*,
	201	COALESCE(array_length(solved, 1), 0) AS solved,
	202	COALESCE(array_length(attempted, 1), 0) AS attempted,
	203	COALESCE(array_length(contests, 1), 0) AS contests
	204	FROM user_data dt
	205	JOIN user_contests ct USING (id)
	206	JOIN user_solved_problems sp USING (id)
	207	JOIN user_attempted_problems ap USING (id)
	208	ORDER BY solved DESC, attempted DESC, id);
	209
	210	CREATE OR REPLACE VIEW contest_entry AS (SELECT
	211	id,name,description,editorial,start,stop,owner,
	212	(EXTRACT(epoch from NOW()) >= start) AS started,
	213	(EXTRACT(epoch from NOW()) >= stop) AS finished
	214	FROM contests
	215	ORDER BY start DESC);
	216
	217	CREATE OR REPLACE VIEW job_entry AS (SELECT
	218	id,contest,date,errors,extension,format,private,problem,result,result_text,results,owner,
	219	LENGTH(source) AS size
	220	FROM jobs
	221	ORDER BY id DESC);
	222
	223	-- CREATE OR REPLACE FUCNTION source_private(jobid INT) RETURNS BOOLEAN AS $$
	224	-- BEGIN
	225	-- private BOOLEAN;
	226	-- problem TEXT;
	227	-- contest TEXT;
	228
	229	-- SELECT private, problem, contest INTO STRICT private, problem, contest FROM jobs WHERE id = jobid;
	230	-- IF private THEN
	231	-- RETURN TRUE;
	232	-- END IF
	233
	234	-- IF
	235	-- END;
	236	-- $$