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