liquid_feedback_core
diff update/core-update.v2.2.0-v2.2.1.sql @ 360:47965760b1b8
Update script to v2.2.1
author | jbe |
---|---|
date | Sun Mar 17 11:30:44 2013 +0100 (2013-03-17) |
parents | |
children | e88d0606891f |
line diff
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/update/core-update.v2.2.0-v2.2.1.sql Sun Mar 17 11:30:44 2013 +0100 1.3 @@ -0,0 +1,53 @@ 1.4 +BEGIN; 1.5 + 1.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.7 + SELECT * FROM (VALUES ('2.2.1', 2, 2, 1)) 1.8 + AS "subquery"("string", "major", "minor", "revision"); 1.9 + 1.10 +ALTER TABLE "initiative" ADD COLUMN "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE; 1.11 +COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time'; 1.12 + 1.13 +ALTER TABLE "suggestion" ADD COLUMN "proportional_order" INT4; 1.14 +COMMENT ON COLUMN "suggestion"."proportional_order" IS 'To be used for sorting suggestions within an initiative; updated by "lf_update_suggestion_order"'; 1.15 + 1.16 +CREATE VIEW "initiative_suggestion_order_calculation" AS 1.17 + SELECT 1.18 + "initiative"."id" AS "initiative_id", 1.19 + ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final" 1.20 + FROM "initiative" JOIN "issue" 1.21 + ON "initiative"."issue_id" = "issue"."id" 1.22 + WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL) 1.23 + OR ("initiative"."final_suggestion_order_calculated" = FALSE); 1.24 +COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated'; 1.25 +COMMENT ON COLUMN "initiative_suggestion_order_calculation"."final" IS 'Set to TRUE, if the issue is fully frozen or closed, and the calculation has to be done only once for one last time'; 1.26 + 1.27 +CREATE VIEW "individual_suggestion_ranking" AS 1.28 + SELECT 1.29 + "opinion"."initiative_id", 1.30 + "opinion"."member_id", 1.31 + "direct_interest_snapshot"."weight", 1.32 + CASE WHEN 1.33 + ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR 1.34 + ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE) 1.35 + THEN 1 ELSE 1.36 + CASE WHEN 1.37 + ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR 1.38 + ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE) 1.39 + THEN 2 ELSE 1.40 + CASE WHEN 1.41 + ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR 1.42 + ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE) 1.43 + THEN 3 ELSE 4 END 1.44 + END 1.45 + END AS "preference", 1.46 + "opinion"."suggestion_id" 1.47 + FROM "opinion" 1.48 + JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id" 1.49 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.50 + JOIN "direct_interest_snapshot" 1.51 + ON "direct_interest_snapshot"."issue_id" = "issue"."id" 1.52 + AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event" 1.53 + AND "direct_interest_snapshot"."member_id" = "opinion"."member_id"; 1.54 +COMMENT ON VIEW "individual_suggestion_ranking" IS 'Helper view for "lf_update_suggestion_order" to allow a proportional ordering of suggestions within an initiative'; 1.55 + 1.56 +COMMIT;