liquid_feedback_core
view update/core-update.v2.0.2-v2.0.3.sql @ 378:e88d0606891f
Bugfix regarding "proportional_order" of suggestions:
Use NULL values explicitly to be sorted last
(includes new suggestions as well as suggestions without any individual rankings)
Use NULL values explicitly to be sorted last
(includes new suggestions as well as suggestions without any individual rankings)
author | jbe |
---|---|
date | Mon Mar 18 09:36:21 2013 +0100 (2013-03-18) |
parents | 2571bb3bdf56 |
children |
line source
1 BEGIN;
3 -- update version number:
4 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
5 SELECT * FROM (VALUES ('2.0.3', 2, 0, 3))
6 AS "subquery"("string", "major", "minor", "revision");
8 -- update function "calculate_ranks"("issue"."id")
9 -- regarding setting "eligible"=FALSE for non-eligible admitted initiatives
10 -- and regarding "rank" ordering:
11 CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
12 RETURNS VOID
13 LANGUAGE 'plpgsql' VOLATILE AS $$
14 DECLARE
15 "issue_row" "issue"%ROWTYPE;
16 "policy_row" "policy"%ROWTYPE;
17 "dimension_v" INTEGER;
18 "vote_matrix" INT4[][]; -- absolute votes
19 "matrix" INT8[][]; -- defeat strength / best paths
20 "i" INTEGER;
21 "j" INTEGER;
22 "k" INTEGER;
23 "battle_row" "battle"%ROWTYPE;
24 "rank_ary" INT4[];
25 "rank_v" INT4;
26 "done_v" INTEGER;
27 "winners_ary" INTEGER[];
28 "initiative_id_v" "initiative"."id"%TYPE;
29 BEGIN
30 SELECT * INTO "issue_row"
31 FROM "issue" WHERE "id" = "issue_id_p"
32 FOR UPDATE;
33 SELECT * INTO "policy_row"
34 FROM "policy" WHERE "id" = "issue_row"."policy_id";
35 SELECT count(1) INTO "dimension_v"
36 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
37 -- Create "vote_matrix" with absolute number of votes in pairwise
38 -- comparison:
39 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
40 "i" := 1;
41 "j" := 2;
42 FOR "battle_row" IN
43 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
44 ORDER BY
45 "winning_initiative_id" NULLS LAST,
46 "losing_initiative_id" NULLS LAST
47 LOOP
48 "vote_matrix"["i"]["j"] := "battle_row"."count";
49 IF "j" = "dimension_v" THEN
50 "i" := "i" + 1;
51 "j" := 1;
52 ELSE
53 "j" := "j" + 1;
54 IF "j" = "i" THEN
55 "j" := "j" + 1;
56 END IF;
57 END IF;
58 END LOOP;
59 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
60 RAISE EXCEPTION 'Wrong battle count (should not happen)';
61 END IF;
62 -- Store defeat strengths in "matrix" using "defeat_strength"
63 -- function:
64 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
65 "i" := 1;
66 LOOP
67 "j" := 1;
68 LOOP
69 IF "i" != "j" THEN
70 "matrix"["i"]["j"] := "defeat_strength"(
71 "vote_matrix"["i"]["j"],
72 "vote_matrix"["j"]["i"]
73 );
74 END IF;
75 EXIT WHEN "j" = "dimension_v";
76 "j" := "j" + 1;
77 END LOOP;
78 EXIT WHEN "i" = "dimension_v";
79 "i" := "i" + 1;
80 END LOOP;
81 -- Find best paths:
82 "i" := 1;
83 LOOP
84 "j" := 1;
85 LOOP
86 IF "i" != "j" THEN
87 "k" := 1;
88 LOOP
89 IF "i" != "k" AND "j" != "k" THEN
90 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
91 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
92 "matrix"["j"]["k"] := "matrix"["j"]["i"];
93 END IF;
94 ELSE
95 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
96 "matrix"["j"]["k"] := "matrix"["i"]["k"];
97 END IF;
98 END IF;
99 END IF;
100 EXIT WHEN "k" = "dimension_v";
101 "k" := "k" + 1;
102 END LOOP;
103 END IF;
104 EXIT WHEN "j" = "dimension_v";
105 "j" := "j" + 1;
106 END LOOP;
107 EXIT WHEN "i" = "dimension_v";
108 "i" := "i" + 1;
109 END LOOP;
110 -- Determine order of winners:
111 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
112 "rank_v" := 1;
113 "done_v" := 0;
114 LOOP
115 "winners_ary" := '{}';
116 "i" := 1;
117 LOOP
118 IF "rank_ary"["i"] ISNULL THEN
119 "j" := 1;
120 LOOP
121 IF
122 "i" != "j" AND
123 "rank_ary"["j"] ISNULL AND
124 "matrix"["j"]["i"] > "matrix"["i"]["j"]
125 THEN
126 -- someone else is better
127 EXIT;
128 END IF;
129 IF "j" = "dimension_v" THEN
130 -- noone is better
131 "winners_ary" := "winners_ary" || "i";
132 EXIT;
133 END IF;
134 "j" := "j" + 1;
135 END LOOP;
136 END IF;
137 EXIT WHEN "i" = "dimension_v";
138 "i" := "i" + 1;
139 END LOOP;
140 "i" := 1;
141 LOOP
142 "rank_ary"["winners_ary"["i"]] := "rank_v";
143 "done_v" := "done_v" + 1;
144 EXIT WHEN "i" = array_upper("winners_ary", 1);
145 "i" := "i" + 1;
146 END LOOP;
147 EXIT WHEN "done_v" = "dimension_v";
148 "rank_v" := "rank_v" + 1;
149 END LOOP;
150 -- write preliminary results:
151 "i" := 1;
152 FOR "initiative_id_v" IN
153 SELECT "id" FROM "initiative"
154 WHERE "issue_id" = "issue_id_p" AND "admitted"
155 ORDER BY "id"
156 LOOP
157 UPDATE "initiative" SET
158 "direct_majority" =
159 CASE WHEN "policy_row"."direct_majority_strict" THEN
160 "positive_votes" * "policy_row"."direct_majority_den" >
161 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
162 ELSE
163 "positive_votes" * "policy_row"."direct_majority_den" >=
164 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
165 END
166 AND "positive_votes" >= "policy_row"."direct_majority_positive"
167 AND "issue_row"."voter_count"-"negative_votes" >=
168 "policy_row"."direct_majority_non_negative",
169 "indirect_majority" =
170 CASE WHEN "policy_row"."indirect_majority_strict" THEN
171 "positive_votes" * "policy_row"."indirect_majority_den" >
172 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
173 ELSE
174 "positive_votes" * "policy_row"."indirect_majority_den" >=
175 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
176 END
177 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
178 AND "issue_row"."voter_count"-"negative_votes" >=
179 "policy_row"."indirect_majority_non_negative",
180 "schulze_rank" = "rank_ary"["i"],
181 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
182 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
183 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
184 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,
185 "eligible" = FALSE,
186 "winner" = FALSE
187 WHERE "id" = "initiative_id_v";
188 "i" := "i" + 1;
189 END LOOP;
190 IF "i" != "dimension_v" THEN
191 RAISE EXCEPTION 'Wrong winner count (should not happen)';
192 END IF;
193 -- take indirect majorities into account:
194 LOOP
195 UPDATE "initiative" SET "indirect_majority" = TRUE
196 FROM (
197 SELECT "new_initiative"."id" AS "initiative_id"
198 FROM "initiative" "old_initiative"
199 JOIN "initiative" "new_initiative"
200 ON "new_initiative"."issue_id" = "issue_id_p"
201 AND "new_initiative"."indirect_majority" = FALSE
202 JOIN "battle" "battle_win"
203 ON "battle_win"."issue_id" = "issue_id_p"
204 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
205 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
206 JOIN "battle" "battle_lose"
207 ON "battle_lose"."issue_id" = "issue_id_p"
208 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
209 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
210 WHERE "old_initiative"."issue_id" = "issue_id_p"
211 AND "old_initiative"."indirect_majority" = TRUE
212 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
213 "battle_win"."count" * "policy_row"."indirect_majority_den" >
214 "policy_row"."indirect_majority_num" *
215 ("battle_win"."count"+"battle_lose"."count")
216 ELSE
217 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
218 "policy_row"."indirect_majority_num" *
219 ("battle_win"."count"+"battle_lose"."count")
220 END
221 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
222 AND "issue_row"."voter_count"-"battle_lose"."count" >=
223 "policy_row"."indirect_majority_non_negative"
224 ) AS "subquery"
225 WHERE "id" = "subquery"."initiative_id";
226 EXIT WHEN NOT FOUND;
227 END LOOP;
228 -- set "multistage_majority" for remaining matching initiatives:
229 UPDATE "initiative" SET "multistage_majority" = TRUE
230 FROM (
231 SELECT "losing_initiative"."id" AS "initiative_id"
232 FROM "initiative" "losing_initiative"
233 JOIN "initiative" "winning_initiative"
234 ON "winning_initiative"."issue_id" = "issue_id_p"
235 AND "winning_initiative"."admitted"
236 JOIN "battle" "battle_win"
237 ON "battle_win"."issue_id" = "issue_id_p"
238 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
239 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
240 JOIN "battle" "battle_lose"
241 ON "battle_lose"."issue_id" = "issue_id_p"
242 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
243 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
244 WHERE "losing_initiative"."issue_id" = "issue_id_p"
245 AND "losing_initiative"."admitted"
246 AND "winning_initiative"."schulze_rank" <
247 "losing_initiative"."schulze_rank"
248 AND "battle_win"."count" > "battle_lose"."count"
249 AND (
250 "battle_win"."count" > "winning_initiative"."positive_votes" OR
251 "battle_lose"."count" < "losing_initiative"."negative_votes" )
252 ) AS "subquery"
253 WHERE "id" = "subquery"."initiative_id";
254 -- mark eligible initiatives:
255 UPDATE "initiative" SET "eligible" = TRUE
256 WHERE "issue_id" = "issue_id_p"
257 AND "initiative"."direct_majority"
258 AND "initiative"."indirect_majority"
259 AND "initiative"."better_than_status_quo"
260 AND (
261 "policy_row"."no_multistage_majority" = FALSE OR
262 "initiative"."multistage_majority" = FALSE )
263 AND (
264 "policy_row"."no_reverse_beat_path" = FALSE OR
265 "initiative"."reverse_beat_path" = FALSE );
266 -- mark final winner:
267 UPDATE "initiative" SET "winner" = TRUE
268 FROM (
269 SELECT "id" AS "initiative_id"
270 FROM "initiative"
271 WHERE "issue_id" = "issue_id_p" AND "eligible"
272 ORDER BY
273 "schulze_rank",
274 "vote_ratio"("positive_votes", "negative_votes"),
275 "id"
276 LIMIT 1
277 ) AS "subquery"
278 WHERE "id" = "subquery"."initiative_id";
279 -- write (final) ranks:
280 "rank_v" := 1;
281 FOR "initiative_id_v" IN
282 SELECT "id"
283 FROM "initiative"
284 WHERE "issue_id" = "issue_id_p" AND "admitted"
285 ORDER BY
286 "winner" DESC,
287 "eligible" DESC,
288 "schulze_rank",
289 "vote_ratio"("positive_votes", "negative_votes"),
290 "id"
291 LOOP
292 UPDATE "initiative" SET "rank" = "rank_v"
293 WHERE "id" = "initiative_id_v";
294 "rank_v" := "rank_v" + 1;
295 END LOOP;
296 -- set schulze rank of status quo and mark issue as finished:
297 UPDATE "issue" SET
298 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
299 "state" =
300 CASE WHEN EXISTS (
301 SELECT NULL FROM "initiative"
302 WHERE "issue_id" = "issue_id_p" AND "winner"
303 ) THEN
304 'finished_with_winner'::"issue_state"
305 ELSE
306 'finished_without_winner'::"issue_state"
307 END,
308 "ranks_available" = TRUE
309 WHERE "id" = "issue_id_p";
310 RETURN;
311 END;
312 $$;
314 -- set "eligible" to FALSE for all non-eligible
315 -- but admitted initiatives of closed issues:
316 UPDATE "initiative" SET "eligible" = FALSE WHERE "eligible" ISNULL AND "rank" NOTNULL;
318 -- NOTE: "rank" ordering of initiatives of closed issues are
319 -- not changed, as v2.0.2 should not be in productive use yet.
321 COMMIT;