liquid_feedback_core
diff update/core-update.v1.2.5-v1.2.6.sql @ 70:5745db0b1a34
Removed subqueries which are unneccessary since last bugfix; Update script
author | jbe |
---|---|
date | Mon Aug 30 22:10:45 2010 +0200 (2010-08-30) |
parents | |
children | dcaa1525c388 |
line diff
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/update/core-update.v1.2.5-v1.2.6.sql Mon Aug 30 22:10:45 2010 +0200 1.3 @@ -0,0 +1,55 @@ 1.4 +BEGIN; 1.5 + 1.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.7 + SELECT * FROM (VALUES ('1.2.6', 1, 2, 6)) 1.8 + AS "subquery"("string", "major", "minor", "revision"); 1.9 + 1.10 +CREATE VIEW "active_delegation" AS 1.11 + SELECT "delegation".* FROM "delegation" 1.12 + JOIN "member" ON "delegation"."truster_id" = "member"."id" 1.13 + WHERE "member"."active" = TRUE; 1.14 + 1.15 +COMMENT ON VIEW "active_delegation" IS 'Delegations where the truster_id refers to an active member'; 1.16 + 1.17 +CREATE OR REPLACE VIEW "global_delegation" AS 1.18 + SELECT * FROM "active_delegation" 1.19 + WHERE "scope" = 'global'; 1.20 + 1.21 +COMMENT ON VIEW "global_delegation" IS 'Global delegations from active members'; 1.22 + 1.23 +CREATE OR REPLACE VIEW "area_delegation" AS 1.24 + SELECT DISTINCT ON ("area"."id", "delegation"."truster_id") 1.25 + "area"."id" AS "area_id", 1.26 + "delegation"."id", 1.27 + "delegation"."truster_id", 1.28 + "delegation"."trustee_id", 1.29 + "delegation"."scope" 1.30 + FROM "area" JOIN "active_delegation" AS "delegation" 1.31 + ON "delegation"."scope" = 'global' 1.32 + OR "delegation"."area_id" = "area"."id" 1.33 + ORDER BY 1.34 + "area"."id", 1.35 + "delegation"."truster_id", 1.36 + "delegation"."scope" DESC; 1.37 + 1.38 +COMMENT ON VIEW "area_delegation" IS 'Resulting area delegations from active members'; 1.39 + 1.40 +CREATE OR REPLACE VIEW "issue_delegation" AS 1.41 + SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id") 1.42 + "issue"."id" AS "issue_id", 1.43 + "delegation"."id", 1.44 + "delegation"."truster_id", 1.45 + "delegation"."trustee_id", 1.46 + "delegation"."scope" 1.47 + FROM "issue" JOIN "active_delegation" AS "delegation" 1.48 + ON "delegation"."scope" = 'global' 1.49 + OR "delegation"."area_id" = "issue"."area_id" 1.50 + OR "delegation"."issue_id" = "issue"."id" 1.51 + ORDER BY 1.52 + "issue"."id", 1.53 + "delegation"."truster_id", 1.54 + "delegation"."scope" DESC; 1.55 + 1.56 +COMMENT ON VIEW "issue_delegation" IS 'Resulting issue delegations from active members'; 1.57 + 1.58 +COMMIT;