liquid_feedback_core
changeset 360:47965760b1b8
Update script to v2.2.1
author | jbe |
---|---|
date | Sun Mar 17 11:30:44 2013 +0100 (2013-03-17) |
parents | c37c0b4f2027 |
children | 3bb60d324a22 |
files | core.sql update/core-update.v2.2.0-v2.2.1.sql |
line diff
1.1 --- a/core.sql Sun Mar 17 11:18:24 2013 +0100 1.2 +++ b/core.sql Sun Mar 17 11:30:44 2013 +0100 1.3 @@ -2043,7 +2043,7 @@ 1.4 1.5 COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated'; 1.6 1.7 -COMMENT ON COLUMN "initiative_suggestion_order_calculation"."final" IS 'Set to TRUE, if the issue is fully frozen or closed, and the calculation has only be done for one last time'; 1.8 +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.9 1.10 1.11 CREATE VIEW "individual_suggestion_ranking" AS
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 2.2 +++ b/update/core-update.v2.2.0-v2.2.1.sql Sun Mar 17 11:30:44 2013 +0100 2.3 @@ -0,0 +1,53 @@ 2.4 +BEGIN; 2.5 + 2.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 2.7 + SELECT * FROM (VALUES ('2.2.1', 2, 2, 1)) 2.8 + AS "subquery"("string", "major", "minor", "revision"); 2.9 + 2.10 +ALTER TABLE "initiative" ADD COLUMN "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE; 2.11 +COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time'; 2.12 + 2.13 +ALTER TABLE "suggestion" ADD COLUMN "proportional_order" INT4; 2.14 +COMMENT ON COLUMN "suggestion"."proportional_order" IS 'To be used for sorting suggestions within an initiative; updated by "lf_update_suggestion_order"'; 2.15 + 2.16 +CREATE VIEW "initiative_suggestion_order_calculation" AS 2.17 + SELECT 2.18 + "initiative"."id" AS "initiative_id", 2.19 + ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final" 2.20 + FROM "initiative" JOIN "issue" 2.21 + ON "initiative"."issue_id" = "issue"."id" 2.22 + WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL) 2.23 + OR ("initiative"."final_suggestion_order_calculated" = FALSE); 2.24 +COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated'; 2.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'; 2.26 + 2.27 +CREATE VIEW "individual_suggestion_ranking" AS 2.28 + SELECT 2.29 + "opinion"."initiative_id", 2.30 + "opinion"."member_id", 2.31 + "direct_interest_snapshot"."weight", 2.32 + CASE WHEN 2.33 + ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR 2.34 + ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE) 2.35 + THEN 1 ELSE 2.36 + CASE WHEN 2.37 + ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR 2.38 + ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE) 2.39 + THEN 2 ELSE 2.40 + CASE WHEN 2.41 + ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR 2.42 + ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE) 2.43 + THEN 3 ELSE 4 END 2.44 + END 2.45 + END AS "preference", 2.46 + "opinion"."suggestion_id" 2.47 + FROM "opinion" 2.48 + JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id" 2.49 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 2.50 + JOIN "direct_interest_snapshot" 2.51 + ON "direct_interest_snapshot"."issue_id" = "issue"."id" 2.52 + AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event" 2.53 + AND "direct_interest_snapshot"."member_id" = "opinion"."member_id"; 2.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'; 2.55 + 2.56 +COMMIT;