liquid_feedback_core
changeset 70:5745db0b1a34 v1.2.6
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 | c65002c1a509 |
children | 7626e290d537 |
files | core.sql update/core-update.v1.2.5-v1.2.6.sql |
line diff
1.1 --- a/core.sql Mon Aug 30 22:01:08 2010 +0200 1.2 +++ b/core.sql Mon Aug 30 22:10:45 2010 +0200 1.3 @@ -1256,61 +1256,55 @@ 1.4 ------------------------------------------ 1.5 1.6 1.7 +CREATE VIEW "active_delegation" AS 1.8 + SELECT "delegation".* FROM "delegation" 1.9 + JOIN "member" ON "delegation"."truster_id" = "member"."id" 1.10 + WHERE "member"."active" = TRUE; 1.11 + 1.12 +COMMENT ON VIEW "active_delegation" IS 'Delegations where the truster_id refers to an active member'; 1.13 + 1.14 + 1.15 CREATE VIEW "global_delegation" AS 1.16 - SELECT 1.17 - "delegation"."id", 1.18 - "delegation"."truster_id", 1.19 - "delegation"."trustee_id" 1.20 - FROM "delegation" JOIN "member" 1.21 - ON "delegation"."truster_id" = "member"."id" 1.22 - WHERE "delegation"."scope" = 'global' AND "member"."active"; 1.23 + SELECT * FROM "active_delegation" WHERE "scope" = 'global'; 1.24 1.25 COMMENT ON VIEW "global_delegation" IS 'Global delegations from active members'; 1.26 1.27 1.28 CREATE VIEW "area_delegation" AS 1.29 - SELECT "subquery".* FROM ( 1.30 - SELECT DISTINCT ON ("area"."id", "delegation"."truster_id") 1.31 - "area"."id" AS "area_id", 1.32 - "delegation"."id", 1.33 - "delegation"."truster_id", 1.34 - "delegation"."trustee_id", 1.35 - "delegation"."scope" 1.36 - FROM "area" JOIN "delegation" 1.37 - ON "delegation"."scope" = 'global' 1.38 - OR "delegation"."area_id" = "area"."id" 1.39 - ORDER BY 1.40 - "area"."id", 1.41 - "delegation"."truster_id", 1.42 - "delegation"."scope" DESC 1.43 - ) AS "subquery" 1.44 - JOIN "member" ON "subquery"."truster_id" = "member"."id" 1.45 - WHERE "member"."active"; 1.46 - 1.47 -COMMENT ON VIEW "area_delegation" IS 'Active delegations for areas'; 1.48 + SELECT DISTINCT ON ("area"."id", "delegation"."truster_id") 1.49 + "area"."id" AS "area_id", 1.50 + "delegation"."id", 1.51 + "delegation"."truster_id", 1.52 + "delegation"."trustee_id", 1.53 + "delegation"."scope" 1.54 + FROM "area" JOIN "active_delegation" AS "delegation" 1.55 + ON "delegation"."scope" = 'global' 1.56 + OR "delegation"."area_id" = "area"."id" 1.57 + ORDER BY 1.58 + "area"."id", 1.59 + "delegation"."truster_id", 1.60 + "delegation"."scope" DESC; 1.61 + 1.62 +COMMENT ON VIEW "area_delegation" IS 'Resulting area delegations from active members'; 1.63 1.64 1.65 CREATE VIEW "issue_delegation" AS 1.66 - SELECT "subquery".* FROM ( 1.67 - SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id") 1.68 - "issue"."id" AS "issue_id", 1.69 - "delegation"."id", 1.70 - "delegation"."truster_id", 1.71 - "delegation"."trustee_id", 1.72 - "delegation"."scope" 1.73 - FROM "issue" JOIN "delegation" 1.74 - ON "delegation"."scope" = 'global' 1.75 - OR "delegation"."area_id" = "issue"."area_id" 1.76 - OR "delegation"."issue_id" = "issue"."id" 1.77 - ORDER BY 1.78 - "issue"."id", 1.79 - "delegation"."truster_id", 1.80 - "delegation"."scope" DESC 1.81 - ) AS "subquery" 1.82 - JOIN "member" ON "subquery"."truster_id" = "member"."id" 1.83 - WHERE "member"."active"; 1.84 - 1.85 -COMMENT ON VIEW "issue_delegation" IS 'Active delegations for issues'; 1.86 + SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id") 1.87 + "issue"."id" AS "issue_id", 1.88 + "delegation"."id", 1.89 + "delegation"."truster_id", 1.90 + "delegation"."trustee_id", 1.91 + "delegation"."scope" 1.92 + FROM "issue" JOIN "active_delegation" AS "delegation" 1.93 + ON "delegation"."scope" = 'global' 1.94 + OR "delegation"."area_id" = "issue"."area_id" 1.95 + OR "delegation"."issue_id" = "issue"."id" 1.96 + ORDER BY 1.97 + "issue"."id", 1.98 + "delegation"."truster_id", 1.99 + "delegation"."scope" DESC; 1.100 + 1.101 +COMMENT ON VIEW "issue_delegation" IS 'Resulting issue delegations from active members'; 1.102 1.103 1.104 CREATE FUNCTION "membership_weight_with_skipping"
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 2.2 +++ b/update/core-update.v1.2.5-v1.2.6.sql Mon Aug 30 22:10:45 2010 +0200 2.3 @@ -0,0 +1,55 @@ 2.4 +BEGIN; 2.5 + 2.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 2.7 + SELECT * FROM (VALUES ('1.2.6', 1, 2, 6)) 2.8 + AS "subquery"("string", "major", "minor", "revision"); 2.9 + 2.10 +CREATE VIEW "active_delegation" AS 2.11 + SELECT "delegation".* FROM "delegation" 2.12 + JOIN "member" ON "delegation"."truster_id" = "member"."id" 2.13 + WHERE "member"."active" = TRUE; 2.14 + 2.15 +COMMENT ON VIEW "active_delegation" IS 'Delegations where the truster_id refers to an active member'; 2.16 + 2.17 +CREATE OR REPLACE VIEW "global_delegation" AS 2.18 + SELECT * FROM "active_delegation" 2.19 + WHERE "scope" = 'global'; 2.20 + 2.21 +COMMENT ON VIEW "global_delegation" IS 'Global delegations from active members'; 2.22 + 2.23 +CREATE OR REPLACE VIEW "area_delegation" AS 2.24 + SELECT DISTINCT ON ("area"."id", "delegation"."truster_id") 2.25 + "area"."id" AS "area_id", 2.26 + "delegation"."id", 2.27 + "delegation"."truster_id", 2.28 + "delegation"."trustee_id", 2.29 + "delegation"."scope" 2.30 + FROM "area" JOIN "active_delegation" AS "delegation" 2.31 + ON "delegation"."scope" = 'global' 2.32 + OR "delegation"."area_id" = "area"."id" 2.33 + ORDER BY 2.34 + "area"."id", 2.35 + "delegation"."truster_id", 2.36 + "delegation"."scope" DESC; 2.37 + 2.38 +COMMENT ON VIEW "area_delegation" IS 'Resulting area delegations from active members'; 2.39 + 2.40 +CREATE OR REPLACE VIEW "issue_delegation" AS 2.41 + SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id") 2.42 + "issue"."id" AS "issue_id", 2.43 + "delegation"."id", 2.44 + "delegation"."truster_id", 2.45 + "delegation"."trustee_id", 2.46 + "delegation"."scope" 2.47 + FROM "issue" JOIN "active_delegation" AS "delegation" 2.48 + ON "delegation"."scope" = 'global' 2.49 + OR "delegation"."area_id" = "issue"."area_id" 2.50 + OR "delegation"."issue_id" = "issue"."id" 2.51 + ORDER BY 2.52 + "issue"."id", 2.53 + "delegation"."truster_id", 2.54 + "delegation"."scope" DESC; 2.55 + 2.56 +COMMENT ON VIEW "issue_delegation" IS 'Resulting issue delegations from active members'; 2.57 + 2.58 +COMMIT;