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