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