X-Git-Url: http://git.ieval.ro/?a=blobdiff_plain;f=db.sql;h=d2f658e03f979a8b90ba7942aba685ad67203bc0;hb=686bacca70a42bb30472d8d1db02ed8ce77fda4d;hp=107fa7195adf0426a8d3c9cfd2001163272ac395;hpb=902c527f40edd9dc911441382976f29630faeaf0;p=gruntmaster-data.git

diff --git a/db.sql b/db.sql
index 107fa71..d2f658e 100644
--- a/db.sql
+++ b/db.sql
@@ -161,40 +161,19 @@ CREATE OR REPLACE VIEW user_data AS (SELECT
 	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
@@ -205,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;
--- $$