liquid_feedback_core
annotate update/core-update.v2.2.0-v2.2.1.sql @ 423:73c2ab2d068f
Work on configuration of complexity of counting of the votes (extent of tie-breaking):
- added configuration field in "policy" table
- calculation of secondary criterion for the defeat strength (tie-breaking of the links) based on initiative id's
- added configuration field in "policy" table
- calculation of secondary criterion for the defeat strength (tie-breaking of the links) based on initiative id's
| author | jbe |
|---|---|
| date | Thu Apr 10 00:20:03 2014 +0200 (2014-04-10) |
| parents | e88d0606891f |
| children |
| rev | line source |
|---|---|
| jbe@360 | 1 BEGIN; |
| jbe@360 | 2 |
| jbe@360 | 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS |
| jbe@360 | 4 SELECT * FROM (VALUES ('2.2.1', 2, 2, 1)) |
| jbe@360 | 5 AS "subquery"("string", "major", "minor", "revision"); |
| jbe@360 | 6 |
| jbe@360 | 7 ALTER TABLE "initiative" ADD COLUMN "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE; |
| jbe@360 | 8 COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time'; |
| jbe@360 | 9 |
| jbe@360 | 10 ALTER TABLE "suggestion" ADD COLUMN "proportional_order" INT4; |
| jbe@378 | 11 COMMENT ON COLUMN "suggestion"."proportional_order" IS 'To be used for sorting suggestions within an initiative; NULL values sort last; updated by "lf_update_suggestion_order"'; |
| jbe@360 | 12 |
| jbe@360 | 13 CREATE VIEW "initiative_suggestion_order_calculation" AS |
| jbe@360 | 14 SELECT |
| jbe@360 | 15 "initiative"."id" AS "initiative_id", |
| jbe@360 | 16 ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final" |
| jbe@360 | 17 FROM "initiative" JOIN "issue" |
| jbe@360 | 18 ON "initiative"."issue_id" = "issue"."id" |
| jbe@360 | 19 WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL) |
| jbe@360 | 20 OR ("initiative"."final_suggestion_order_calculated" = FALSE); |
| jbe@360 | 21 COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated'; |
| jbe@360 | 22 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'; |
| jbe@360 | 23 |
| jbe@360 | 24 CREATE VIEW "individual_suggestion_ranking" AS |
| jbe@360 | 25 SELECT |
| jbe@360 | 26 "opinion"."initiative_id", |
| jbe@360 | 27 "opinion"."member_id", |
| jbe@360 | 28 "direct_interest_snapshot"."weight", |
| jbe@360 | 29 CASE WHEN |
| jbe@360 | 30 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR |
| jbe@360 | 31 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE) |
| jbe@360 | 32 THEN 1 ELSE |
| jbe@360 | 33 CASE WHEN |
| jbe@360 | 34 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR |
| jbe@360 | 35 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE) |
| jbe@360 | 36 THEN 2 ELSE |
| jbe@360 | 37 CASE WHEN |
| jbe@360 | 38 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR |
| jbe@360 | 39 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE) |
| jbe@360 | 40 THEN 3 ELSE 4 END |
| jbe@360 | 41 END |
| jbe@360 | 42 END AS "preference", |
| jbe@360 | 43 "opinion"."suggestion_id" |
| jbe@360 | 44 FROM "opinion" |
| jbe@360 | 45 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id" |
| jbe@360 | 46 JOIN "issue" ON "issue"."id" = "initiative"."issue_id" |
| jbe@360 | 47 JOIN "direct_interest_snapshot" |
| jbe@360 | 48 ON "direct_interest_snapshot"."issue_id" = "issue"."id" |
| jbe@360 | 49 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event" |
| jbe@360 | 50 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id"; |
| jbe@360 | 51 COMMENT ON VIEW "individual_suggestion_ranking" IS 'Helper view for "lf_update_suggestion_order" to allow a proportional ordering of suggestions within an initiative'; |
| jbe@360 | 52 |
| jbe@360 | 53 COMMIT; |