X-Git-Url: http://git.ieval.ro/?a=blobdiff_plain;ds=sidebyside;f=db.sql;fp=db.sql;h=f9fc0f37edc26a243960e1027204c2447e7f7bed;hb=52172a1a697be94c99123f265ebcd64871d6379e;hp=a9ea727f3e64f5f861fd03150604dfb60e50e49f;hpb=e1250e203047d108bef4640e19d1384a503826e0;p=gruntmaster-data.git

diff --git a/db.sql b/db.sql
index a9ea727..f9fc0f3 100644
--- a/db.sql
+++ b/db.sql
@@ -169,3 +169,68 @@ INSERT INTO column_comments VALUES ('jobs', 'result_text', 'Job result (human-re
 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;
+-- $$