liquid_feedback_core
diff core.sql @ 352:98c14d8d07f1
Support for proportional ordering of suggestions in core.sql; Begin of work on "lf_update_suggestion_order" (a second background job for sorting suggestions based on a proportional preferential voting system)
author | jbe |
---|---|
date | Sat Mar 16 17:22:01 2013 +0100 (2013-03-16) |
parents | a5d4df7f4e22 |
children | 47965760b1b8 |
line diff
1.1 --- a/core.sql Sun Mar 10 19:50:54 2013 +0100 1.2 +++ b/core.sql Sat Mar 16 17:22:01 2013 +0100 1.3 @@ -7,7 +7,7 @@ 1.4 BEGIN; 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('2.2.0', 2, 2, 0)) 1.8 + SELECT * FROM (VALUES ('2.2.1', 2, 2, 1)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11 1.12 @@ -610,6 +610,7 @@ 1.13 "satisfied_supporter_count" INT4, 1.14 "satisfied_informed_supporter_count" INT4, 1.15 "harmonic_weight" NUMERIC(12, 3), 1.16 + "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE, 1.17 "positive_votes" INT4, 1.18 "negative_votes" INT4, 1.19 "direct_majority" BOOLEAN, 1.20 @@ -666,6 +667,7 @@ 1.21 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.22 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"'; 1.23 COMMENT ON COLUMN "initiative"."harmonic_weight" IS 'Indicates the relevancy of the initiative, calculated from the potential supporters weighted with the harmonic series to avoid a large number of clones affecting other initiative''s sorting positions too much; shall be used as secondary sorting key after "admitted" as primary sorting key'; 1.24 +COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time'; 1.25 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"'; 1.26 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"'; 1.27 COMMENT ON COLUMN "initiative"."direct_majority" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "direct_majority_num"/"direct_majority_den", and "positive_votes" is greater-equal than "direct_majority_positive", and ("positive_votes"+abstentions) is greater-equal than "direct_majority_non_negative"'; 1.28 @@ -768,7 +770,8 @@ 1.29 "plus1_unfulfilled_count" INT4, 1.30 "plus1_fulfilled_count" INT4, 1.31 "plus2_unfulfilled_count" INT4, 1.32 - "plus2_fulfilled_count" INT4 ); 1.33 + "plus2_fulfilled_count" INT4, 1.34 + "proportional_order" INT4 ); 1.35 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created"); 1.36 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created"); 1.37 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data"); 1.38 @@ -789,6 +792,7 @@ 1.39 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters'; 1.40 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters'; 1.41 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters'; 1.42 +COMMENT ON COLUMN "suggestion"."proportional_order" IS 'To be used for sorting suggestions within an initiative; updated by "lf_update_suggestion_order"'; 1.43 1.44 1.45 CREATE TABLE "rendered_suggestion" ( 1.46 @@ -2028,6 +2032,51 @@ 1.47 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction'; 1.48 1.49 1.50 +CREATE VIEW "initiative_suggestion_order_calculation" AS 1.51 + SELECT 1.52 + "initiative"."id" AS "initiative_id", 1.53 + ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final" 1.54 + FROM "initiative" JOIN "issue" 1.55 + ON "initiative"."issue_id" = "issue"."id" 1.56 + WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL) 1.57 + OR ("initiative"."final_suggestion_order_calculated" = FALSE); 1.58 + 1.59 +COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated'; 1.60 + 1.61 +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.62 + 1.63 + 1.64 +CREATE VIEW "individual_suggestion_ranking" AS 1.65 + SELECT 1.66 + "opinion"."initiative_id", 1.67 + "opinion"."member_id", 1.68 + "direct_interest_snapshot"."weight", 1.69 + CASE WHEN 1.70 + ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR 1.71 + ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE) 1.72 + THEN 1 ELSE 1.73 + CASE WHEN 1.74 + ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR 1.75 + ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE) 1.76 + THEN 2 ELSE 1.77 + CASE WHEN 1.78 + ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR 1.79 + ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE) 1.80 + THEN 3 ELSE 4 END 1.81 + END 1.82 + END AS "preference", 1.83 + "opinion"."suggestion_id" 1.84 + FROM "opinion" 1.85 + JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id" 1.86 + JOIN "issue" ON "issue"."id" = "initiative"."issue_id" 1.87 + JOIN "direct_interest_snapshot" 1.88 + ON "direct_interest_snapshot"."issue_id" = "issue"."id" 1.89 + AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event" 1.90 + AND "direct_interest_snapshot"."member_id" = "opinion"."member_id"; 1.91 + 1.92 +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.93 + 1.94 + 1.95 CREATE VIEW "battle_participant" AS 1.96 SELECT "initiative"."id", "initiative"."issue_id" 1.97 FROM "issue" JOIN "initiative"