rev |
line source |
jbe@423
|
1 BEGIN;
|
jbe@423
|
2
|
jbe@423
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
jbe@423
|
4 SELECT * FROM (VALUES ('3.0.2', 3, 0, 2))
|
jbe@423
|
5 AS "subquery"("string", "major", "minor", "revision");
|
jbe@423
|
6
|
jbe@430
|
7
|
jbe@430
|
8 CREATE TYPE "defeat_strength" AS ENUM ('simple', 'tuple');
|
jbe@430
|
9
|
jbe@430
|
10 COMMENT ON TYPE "defeat_strength" IS 'How pairwise defeats are measured for the Schulze method: ''simple'' = only the number of winning votes, ''tuple'' = primarily the number of winning votes, secondarily the number of losing votes';
|
jbe@430
|
11
|
jbe@430
|
12
|
jbe@430
|
13 CREATE TYPE "tie_breaking" AS ENUM ('simple', 'variant1', 'variant2');
|
jbe@430
|
14
|
jbe@430
|
15 COMMENT ON TYPE "tie_breaking" IS 'Tie-breaker for the Schulze method: ''simple'' = only initiative ids are used, ''variant1'' = use initiative ids in variant 1 for tie breaking of the links (TBRL) and sequentially forbid shared links, ''variant2'' = use initiative ids in variant 2 for tie breaking of the links (TBRL) and sequentially forbid shared links';
|
jbe@430
|
16
|
jbe@430
|
17
|
jbe@430
|
18 ALTER TABLE "policy" ADD COLUMN "defeat_strength" "defeat_strength" NOT NULL DEFAULT 'tuple';
|
jbe@430
|
19 ALTER TABLE "policy" ADD COLUMN "tie_breaking" "tie_breaking" NOT NULL DEFAULT 'variant1';
|
jbe@430
|
20
|
jbe@430
|
21 ALTER TABLE "policy" ADD
|
jbe@430
|
22 CONSTRAINT "no_reverse_beat_path_requires_tuple_defeat_strength" CHECK (
|
jbe@430
|
23 ("defeat_strength" = 'tuple'::"defeat_strength" OR "no_reverse_beat_path" = FALSE)
|
jbe@430
|
24 );
|
jbe@430
|
25
|
jbe@430
|
26 COMMENT ON COLUMN "policy"."defeat_strength" IS 'How pairwise defeats are measured for the Schulze method; see type "defeat_strength"; ''tuple'' is the recommended setting';
|
jbe@430
|
27 COMMENT ON COLUMN "policy"."tie_breaking" IS 'Tie-breaker for the Schulze method; see type "tie_breaking"; ''variant1'' or ''variant2'' are recommended';
|
jbe@430
|
28 COMMENT ON COLUMN "initiative"."reverse_beat_path" IS 'TRUE, if there is a beat path (may include ties) from this initiative to the status quo; set to NULL if "policy"."defeat_strength" is set to ''simple''';
|
jbe@430
|
29
|
jbe@430
|
30
|
jbe@430
|
31 DROP FUNCTION "calculate_ranks"("issue"."id"%TYPE);
|
jbe@430
|
32 DROP FUNCTION "defeat_strength"(INT4, INT4);
|
jbe@430
|
33
|
jbe@430
|
34
|
jbe@430
|
35 CREATE FUNCTION "defeat_strength"
|
jbe@430
|
36 ( "positive_votes_p" INT4,
|
jbe@430
|
37 "negative_votes_p" INT4,
|
jbe@430
|
38 "defeat_strength_p" "defeat_strength" )
|
jbe@430
|
39 RETURNS INT8
|
jbe@430
|
40 LANGUAGE 'plpgsql' IMMUTABLE AS $$
|
jbe@430
|
41 BEGIN
|
jbe@430
|
42 IF "defeat_strength_p" = 'simple'::"defeat_strength" THEN
|
jbe@430
|
43 IF "positive_votes_p" > "negative_votes_p" THEN
|
jbe@430
|
44 RETURN "positive_votes_p";
|
jbe@430
|
45 ELSE
|
jbe@430
|
46 RETURN 0;
|
jbe@430
|
47 END IF;
|
jbe@430
|
48 ELSE
|
jbe@430
|
49 IF "positive_votes_p" > "negative_votes_p" THEN
|
jbe@430
|
50 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
|
jbe@430
|
51 ELSIF "positive_votes_p" = "negative_votes_p" THEN
|
jbe@430
|
52 RETURN 0;
|
jbe@430
|
53 ELSE
|
jbe@430
|
54 RETURN -1;
|
jbe@430
|
55 END IF;
|
jbe@430
|
56 END IF;
|
jbe@430
|
57 END;
|
jbe@430
|
58 $$;
|
jbe@430
|
59
|
jbe@430
|
60 COMMENT ON FUNCTION "defeat_strength"(INT4, INT4, "defeat_strength") IS 'Calculates defeat strength (INT8!) according to the "defeat_strength" option (see comment on type "defeat_strength")';
|
jbe@430
|
61
|
jbe@430
|
62
|
jbe@430
|
63 CREATE FUNCTION "secondary_link_strength"
|
jbe@430
|
64 ( "initiative1_ord_p" INT4,
|
jbe@430
|
65 "initiative2_ord_p" INT4,
|
jbe@430
|
66 "tie_breaking_p" "tie_breaking" )
|
jbe@430
|
67 RETURNS INT8
|
jbe@430
|
68 LANGUAGE 'plpgsql' IMMUTABLE AS $$
|
jbe@430
|
69 BEGIN
|
jbe@430
|
70 IF "initiative1_ord_p" = "initiative2_ord_p" THEN
|
jbe@430
|
71 RAISE EXCEPTION 'Identical initiative ids passed to "secondary_link_strength" function (should not happen)';
|
jbe@430
|
72 END IF;
|
jbe@430
|
73 RETURN (
|
jbe@430
|
74 CASE WHEN "tie_breaking_p" = 'simple'::"tie_breaking" THEN
|
jbe@430
|
75 0
|
jbe@430
|
76 ELSE
|
jbe@430
|
77 CASE WHEN "initiative1_ord_p" < "initiative2_ord_p" THEN
|
jbe@430
|
78 1::INT8 << 62
|
jbe@430
|
79 ELSE 0 END
|
jbe@430
|
80 +
|
jbe@430
|
81 CASE WHEN "tie_breaking_p" = 'variant2'::"tie_breaking" THEN
|
jbe@430
|
82 ("initiative2_ord_p"::INT8 << 31) - "initiative1_ord_p"::INT8
|
jbe@430
|
83 ELSE
|
jbe@430
|
84 "initiative2_ord_p"::INT8 - ("initiative1_ord_p"::INT8 << 31)
|
jbe@430
|
85 END
|
jbe@430
|
86 END
|
jbe@430
|
87 );
|
jbe@430
|
88 END;
|
jbe@430
|
89 $$;
|
jbe@430
|
90
|
jbe@430
|
91 COMMENT ON FUNCTION "secondary_link_strength"(INT4, INT4, "tie_breaking") IS 'Calculates a secondary criterion for the defeat strength (tie-breaking of the links)';
|
jbe@430
|
92
|
jbe@430
|
93
|
jbe@430
|
94 CREATE TYPE "link_strength" AS (
|
jbe@430
|
95 "primary" INT8,
|
jbe@430
|
96 "secondary" INT8 );
|
jbe@430
|
97
|
jbe@430
|
98 COMMENT ON TYPE "link_strength" IS 'Type to store the defeat strength of a link between two candidates plus a secondary criterion to create unique link strengths between the candidates (needed for tie-breaking ''variant1'' and ''variant2'')';
|
jbe@430
|
99
|
jbe@430
|
100
|
jbe@430
|
101 CREATE FUNCTION "find_best_paths"("matrix_d" "link_strength"[][])
|
jbe@430
|
102 RETURNS "link_strength"[][]
|
jbe@430
|
103 LANGUAGE 'plpgsql' IMMUTABLE AS $$
|
jbe@430
|
104 DECLARE
|
jbe@430
|
105 "dimension_v" INT4;
|
jbe@430
|
106 "matrix_p" "link_strength"[][];
|
jbe@430
|
107 "i" INT4;
|
jbe@430
|
108 "j" INT4;
|
jbe@430
|
109 "k" INT4;
|
jbe@430
|
110 BEGIN
|
jbe@430
|
111 "dimension_v" := array_upper("matrix_d", 1);
|
jbe@430
|
112 "matrix_p" := "matrix_d";
|
jbe@430
|
113 "i" := 1;
|
jbe@430
|
114 LOOP
|
jbe@430
|
115 "j" := 1;
|
jbe@430
|
116 LOOP
|
jbe@430
|
117 IF "i" != "j" THEN
|
jbe@430
|
118 "k" := 1;
|
jbe@430
|
119 LOOP
|
jbe@430
|
120 IF "i" != "k" AND "j" != "k" THEN
|
jbe@430
|
121 IF "matrix_p"["j"]["i"] < "matrix_p"["i"]["k"] THEN
|
jbe@430
|
122 IF "matrix_p"["j"]["i"] > "matrix_p"["j"]["k"] THEN
|
jbe@430
|
123 "matrix_p"["j"]["k"] := "matrix_p"["j"]["i"];
|
jbe@430
|
124 END IF;
|
jbe@430
|
125 ELSE
|
jbe@430
|
126 IF "matrix_p"["i"]["k"] > "matrix_p"["j"]["k"] THEN
|
jbe@430
|
127 "matrix_p"["j"]["k"] := "matrix_p"["i"]["k"];
|
jbe@430
|
128 END IF;
|
jbe@430
|
129 END IF;
|
jbe@430
|
130 END IF;
|
jbe@430
|
131 EXIT WHEN "k" = "dimension_v";
|
jbe@430
|
132 "k" := "k" + 1;
|
jbe@430
|
133 END LOOP;
|
jbe@430
|
134 END IF;
|
jbe@430
|
135 EXIT WHEN "j" = "dimension_v";
|
jbe@430
|
136 "j" := "j" + 1;
|
jbe@430
|
137 END LOOP;
|
jbe@430
|
138 EXIT WHEN "i" = "dimension_v";
|
jbe@430
|
139 "i" := "i" + 1;
|
jbe@430
|
140 END LOOP;
|
jbe@430
|
141 RETURN "matrix_p";
|
jbe@430
|
142 END;
|
jbe@430
|
143 $$;
|
jbe@430
|
144
|
jbe@430
|
145 COMMENT ON FUNCTION "find_best_paths"("link_strength"[][]) IS 'Computes the strengths of the best beat-paths from a square matrix';
|
jbe@430
|
146
|
jbe@430
|
147
|
jbe@430
|
148 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
|
jbe@430
|
149 RETURNS VOID
|
jbe@430
|
150 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@430
|
151 DECLARE
|
jbe@430
|
152 "issue_row" "issue"%ROWTYPE;
|
jbe@430
|
153 "policy_row" "policy"%ROWTYPE;
|
jbe@430
|
154 "dimension_v" INT4;
|
jbe@430
|
155 "matrix_a" INT4[][]; -- absolute votes
|
jbe@430
|
156 "matrix_d" "link_strength"[][]; -- defeat strength (direct)
|
jbe@430
|
157 "matrix_p" "link_strength"[][]; -- defeat strength (best path)
|
jbe@430
|
158 "matrix_t" "link_strength"[][]; -- defeat strength (tie-breaking)
|
jbe@430
|
159 "matrix_f" BOOLEAN[][]; -- forbidden link (tie-breaking)
|
jbe@430
|
160 "matrix_b" BOOLEAN[][]; -- final order (who beats who)
|
jbe@430
|
161 "i" INT4;
|
jbe@430
|
162 "j" INT4;
|
jbe@430
|
163 "m" INT4;
|
jbe@430
|
164 "n" INT4;
|
jbe@430
|
165 "battle_row" "battle"%ROWTYPE;
|
jbe@430
|
166 "rank_ary" INT4[];
|
jbe@430
|
167 "rank_v" INT4;
|
jbe@430
|
168 "initiative_id_v" "initiative"."id"%TYPE;
|
jbe@430
|
169 BEGIN
|
jbe@430
|
170 PERFORM "require_transaction_isolation"();
|
jbe@430
|
171 SELECT * INTO "issue_row"
|
jbe@430
|
172 FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@430
|
173 SELECT * INTO "policy_row"
|
jbe@430
|
174 FROM "policy" WHERE "id" = "issue_row"."policy_id";
|
jbe@430
|
175 SELECT count(1) INTO "dimension_v"
|
jbe@430
|
176 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
|
jbe@430
|
177 -- create "matrix_a" with absolute number of votes in pairwise
|
jbe@430
|
178 -- comparison:
|
jbe@430
|
179 "matrix_a" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
|
jbe@430
|
180 "i" := 1;
|
jbe@430
|
181 "j" := 2;
|
jbe@430
|
182 FOR "battle_row" IN
|
jbe@430
|
183 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
|
jbe@430
|
184 ORDER BY
|
jbe@430
|
185 "winning_initiative_id" NULLS FIRST,
|
jbe@430
|
186 "losing_initiative_id" NULLS FIRST
|
jbe@430
|
187 LOOP
|
jbe@430
|
188 "matrix_a"["i"]["j"] := "battle_row"."count";
|
jbe@430
|
189 IF "j" = "dimension_v" THEN
|
jbe@430
|
190 "i" := "i" + 1;
|
jbe@430
|
191 "j" := 1;
|
jbe@430
|
192 ELSE
|
jbe@430
|
193 "j" := "j" + 1;
|
jbe@430
|
194 IF "j" = "i" THEN
|
jbe@430
|
195 "j" := "j" + 1;
|
jbe@430
|
196 END IF;
|
jbe@430
|
197 END IF;
|
jbe@430
|
198 END LOOP;
|
jbe@430
|
199 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
|
jbe@430
|
200 RAISE EXCEPTION 'Wrong battle count (should not happen)';
|
jbe@430
|
201 END IF;
|
jbe@430
|
202 -- store direct defeat strengths in "matrix_d" using "defeat_strength"
|
jbe@430
|
203 -- and "secondary_link_strength" functions:
|
jbe@430
|
204 "matrix_d" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
|
jbe@430
|
205 "i" := 1;
|
jbe@430
|
206 LOOP
|
jbe@430
|
207 "j" := 1;
|
jbe@430
|
208 LOOP
|
jbe@430
|
209 IF "i" != "j" THEN
|
jbe@430
|
210 "matrix_d"["i"]["j"] := (
|
jbe@430
|
211 "defeat_strength"(
|
jbe@430
|
212 "matrix_a"["i"]["j"],
|
jbe@430
|
213 "matrix_a"["j"]["i"],
|
jbe@430
|
214 "policy_row"."defeat_strength"
|
jbe@430
|
215 ),
|
jbe@430
|
216 "secondary_link_strength"(
|
jbe@430
|
217 "i",
|
jbe@430
|
218 "j",
|
jbe@430
|
219 "policy_row"."tie_breaking"
|
jbe@430
|
220 )
|
jbe@430
|
221 )::"link_strength";
|
jbe@430
|
222 END IF;
|
jbe@430
|
223 EXIT WHEN "j" = "dimension_v";
|
jbe@430
|
224 "j" := "j" + 1;
|
jbe@430
|
225 END LOOP;
|
jbe@430
|
226 EXIT WHEN "i" = "dimension_v";
|
jbe@430
|
227 "i" := "i" + 1;
|
jbe@430
|
228 END LOOP;
|
jbe@430
|
229 -- find best paths:
|
jbe@430
|
230 "matrix_p" := "find_best_paths"("matrix_d");
|
jbe@430
|
231 -- create partial order:
|
jbe@430
|
232 "matrix_b" := array_fill(NULL::BOOLEAN, ARRAY["dimension_v", "dimension_v"]);
|
jbe@430
|
233 "i" := 1;
|
jbe@430
|
234 LOOP
|
jbe@430
|
235 "j" := "i" + 1;
|
jbe@430
|
236 LOOP
|
jbe@430
|
237 IF "i" != "j" THEN
|
jbe@430
|
238 IF "matrix_p"["i"]["j"] > "matrix_p"["j"]["i"] THEN
|
jbe@430
|
239 "matrix_b"["i"]["j"] := TRUE;
|
jbe@430
|
240 "matrix_b"["j"]["i"] := FALSE;
|
jbe@430
|
241 ELSIF "matrix_p"["i"]["j"] < "matrix_p"["j"]["i"] THEN
|
jbe@430
|
242 "matrix_b"["i"]["j"] := FALSE;
|
jbe@430
|
243 "matrix_b"["j"]["i"] := TRUE;
|
jbe@430
|
244 END IF;
|
jbe@430
|
245 END IF;
|
jbe@430
|
246 EXIT WHEN "j" = "dimension_v";
|
jbe@430
|
247 "j" := "j" + 1;
|
jbe@430
|
248 END LOOP;
|
jbe@430
|
249 EXIT WHEN "i" = "dimension_v" - 1;
|
jbe@430
|
250 "i" := "i" + 1;
|
jbe@430
|
251 END LOOP;
|
jbe@430
|
252 -- tie-breaking by forbidding shared weakest links in beat-paths
|
jbe@430
|
253 -- (unless "tie_breaking" is set to 'simple', in which case tie-breaking
|
jbe@430
|
254 -- is performed later by initiative id):
|
jbe@430
|
255 IF "policy_row"."tie_breaking" != 'simple'::"tie_breaking" THEN
|
jbe@430
|
256 "m" := 1;
|
jbe@430
|
257 LOOP
|
jbe@430
|
258 "n" := "m" + 1;
|
jbe@430
|
259 LOOP
|
jbe@430
|
260 -- only process those candidates m and n, which are tied:
|
jbe@430
|
261 IF "matrix_b"["m"]["n"] ISNULL THEN
|
jbe@430
|
262 -- start with beat-paths prior tie-breaking:
|
jbe@430
|
263 "matrix_t" := "matrix_p";
|
jbe@430
|
264 -- start with all links allowed:
|
jbe@430
|
265 "matrix_f" := array_fill(FALSE, ARRAY["dimension_v", "dimension_v"]);
|
jbe@430
|
266 LOOP
|
jbe@430
|
267 -- determine (and forbid) that link that is the weakest link
|
jbe@430
|
268 -- in both the best path from candidate m to candidate n and
|
jbe@430
|
269 -- from candidate n to candidate m:
|
jbe@430
|
270 "i" := 1;
|
jbe@430
|
271 <<forbid_one_link>>
|
jbe@430
|
272 LOOP
|
jbe@430
|
273 "j" := 1;
|
jbe@430
|
274 LOOP
|
jbe@430
|
275 IF "i" != "j" THEN
|
jbe@430
|
276 IF "matrix_d"["i"]["j"] = "matrix_t"["m"]["n"] THEN
|
jbe@430
|
277 "matrix_f"["i"]["j"] := TRUE;
|
jbe@430
|
278 -- exit for performance reasons,
|
jbe@430
|
279 -- as exactly one link will be found:
|
jbe@430
|
280 EXIT forbid_one_link;
|
jbe@430
|
281 END IF;
|
jbe@430
|
282 END IF;
|
jbe@430
|
283 EXIT WHEN "j" = "dimension_v";
|
jbe@430
|
284 "j" := "j" + 1;
|
jbe@430
|
285 END LOOP;
|
jbe@430
|
286 IF "i" = "dimension_v" THEN
|
jbe@430
|
287 RAISE EXCEPTION 'Did not find shared weakest link for tie-breaking (should not happen)';
|
jbe@430
|
288 END IF;
|
jbe@430
|
289 "i" := "i" + 1;
|
jbe@430
|
290 END LOOP;
|
jbe@430
|
291 -- calculate best beat-paths while ignoring forbidden links:
|
jbe@430
|
292 "i" := 1;
|
jbe@430
|
293 LOOP
|
jbe@430
|
294 "j" := 1;
|
jbe@430
|
295 LOOP
|
jbe@430
|
296 IF "i" != "j" THEN
|
jbe@430
|
297 "matrix_t"["i"]["j"] := CASE
|
jbe@430
|
298 WHEN "matrix_f"["i"]["j"]
|
jbe@432
|
299 THEN ((-1::INT8) << 63, 0)::"link_strength" -- worst possible value
|
jbe@430
|
300 ELSE "matrix_d"["i"]["j"] END;
|
jbe@430
|
301 END IF;
|
jbe@430
|
302 EXIT WHEN "j" = "dimension_v";
|
jbe@430
|
303 "j" := "j" + 1;
|
jbe@430
|
304 END LOOP;
|
jbe@430
|
305 EXIT WHEN "i" = "dimension_v";
|
jbe@430
|
306 "i" := "i" + 1;
|
jbe@430
|
307 END LOOP;
|
jbe@430
|
308 "matrix_t" := "find_best_paths"("matrix_t");
|
jbe@430
|
309 -- extend partial order, if tie-breaking was successful:
|
jbe@430
|
310 IF "matrix_t"["m"]["n"] > "matrix_t"["n"]["m"] THEN
|
jbe@430
|
311 "matrix_b"["m"]["n"] := TRUE;
|
jbe@430
|
312 "matrix_b"["n"]["m"] := FALSE;
|
jbe@430
|
313 EXIT;
|
jbe@430
|
314 ELSIF "matrix_t"["m"]["n"] < "matrix_t"["n"]["m"] THEN
|
jbe@430
|
315 "matrix_b"["m"]["n"] := FALSE;
|
jbe@430
|
316 "matrix_b"["n"]["m"] := TRUE;
|
jbe@430
|
317 EXIT;
|
jbe@430
|
318 END IF;
|
jbe@430
|
319 END LOOP;
|
jbe@430
|
320 END IF;
|
jbe@430
|
321 EXIT WHEN "n" = "dimension_v";
|
jbe@430
|
322 "n" := "n" + 1;
|
jbe@430
|
323 END LOOP;
|
jbe@430
|
324 EXIT WHEN "m" = "dimension_v" - 1;
|
jbe@430
|
325 "m" := "m" + 1;
|
jbe@430
|
326 END LOOP;
|
jbe@430
|
327 END IF;
|
jbe@430
|
328 -- store a unique ranking in "rank_ary":
|
jbe@430
|
329 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
|
jbe@430
|
330 "rank_v" := 1;
|
jbe@430
|
331 LOOP
|
jbe@430
|
332 "i" := 1;
|
jbe@430
|
333 <<assign_next_rank>>
|
jbe@430
|
334 LOOP
|
jbe@430
|
335 IF "rank_ary"["i"] ISNULL THEN
|
jbe@430
|
336 "j" := 1;
|
jbe@430
|
337 LOOP
|
jbe@430
|
338 IF
|
jbe@430
|
339 "i" != "j" AND
|
jbe@430
|
340 "rank_ary"["j"] ISNULL AND
|
jbe@430
|
341 ( "matrix_b"["j"]["i"] OR
|
jbe@430
|
342 -- tie-breaking by "id"
|
jbe@430
|
343 ( "matrix_b"["j"]["i"] ISNULL AND
|
jbe@430
|
344 "j" < "i" ) )
|
jbe@430
|
345 THEN
|
jbe@430
|
346 -- someone else is better
|
jbe@430
|
347 EXIT;
|
jbe@430
|
348 END IF;
|
jbe@430
|
349 IF "j" = "dimension_v" THEN
|
jbe@430
|
350 -- noone is better
|
jbe@430
|
351 "rank_ary"["i"] := "rank_v";
|
jbe@430
|
352 EXIT assign_next_rank;
|
jbe@430
|
353 END IF;
|
jbe@430
|
354 "j" := "j" + 1;
|
jbe@430
|
355 END LOOP;
|
jbe@430
|
356 END IF;
|
jbe@430
|
357 "i" := "i" + 1;
|
jbe@430
|
358 IF "i" > "dimension_v" THEN
|
jbe@430
|
359 RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
|
jbe@430
|
360 END IF;
|
jbe@430
|
361 END LOOP;
|
jbe@430
|
362 EXIT WHEN "rank_v" = "dimension_v";
|
jbe@430
|
363 "rank_v" := "rank_v" + 1;
|
jbe@430
|
364 END LOOP;
|
jbe@430
|
365 -- write preliminary results:
|
jbe@430
|
366 "i" := 2; -- omit status quo with "i" = 1
|
jbe@430
|
367 FOR "initiative_id_v" IN
|
jbe@430
|
368 SELECT "id" FROM "initiative"
|
jbe@430
|
369 WHERE "issue_id" = "issue_id_p" AND "admitted"
|
jbe@430
|
370 ORDER BY "id"
|
jbe@430
|
371 LOOP
|
jbe@430
|
372 UPDATE "initiative" SET
|
jbe@430
|
373 "direct_majority" =
|
jbe@430
|
374 CASE WHEN "policy_row"."direct_majority_strict" THEN
|
jbe@430
|
375 "positive_votes" * "policy_row"."direct_majority_den" >
|
jbe@430
|
376 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
|
jbe@430
|
377 ELSE
|
jbe@430
|
378 "positive_votes" * "policy_row"."direct_majority_den" >=
|
jbe@430
|
379 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
|
jbe@430
|
380 END
|
jbe@430
|
381 AND "positive_votes" >= "policy_row"."direct_majority_positive"
|
jbe@430
|
382 AND "issue_row"."voter_count"-"negative_votes" >=
|
jbe@430
|
383 "policy_row"."direct_majority_non_negative",
|
jbe@430
|
384 "indirect_majority" =
|
jbe@430
|
385 CASE WHEN "policy_row"."indirect_majority_strict" THEN
|
jbe@430
|
386 "positive_votes" * "policy_row"."indirect_majority_den" >
|
jbe@430
|
387 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
|
jbe@430
|
388 ELSE
|
jbe@430
|
389 "positive_votes" * "policy_row"."indirect_majority_den" >=
|
jbe@430
|
390 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
|
jbe@430
|
391 END
|
jbe@430
|
392 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
|
jbe@430
|
393 AND "issue_row"."voter_count"-"negative_votes" >=
|
jbe@430
|
394 "policy_row"."indirect_majority_non_negative",
|
jbe@430
|
395 "schulze_rank" = "rank_ary"["i"],
|
jbe@430
|
396 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
|
jbe@430
|
397 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"[1],
|
jbe@430
|
398 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"[1],
|
jbe@430
|
399 "reverse_beat_path" = CASE WHEN "policy_row"."defeat_strength" = 'simple'::"defeat_strength"
|
jbe@430
|
400 THEN NULL
|
jbe@430
|
401 ELSE "matrix_p"[1]["i"]."primary" >= 0 END,
|
jbe@430
|
402 "eligible" = FALSE,
|
jbe@430
|
403 "winner" = FALSE,
|
jbe@430
|
404 "rank" = NULL -- NOTE: in cases of manual reset of issue state
|
jbe@430
|
405 WHERE "id" = "initiative_id_v";
|
jbe@430
|
406 "i" := "i" + 1;
|
jbe@430
|
407 END LOOP;
|
jbe@430
|
408 IF "i" != "dimension_v" + 1 THEN
|
jbe@430
|
409 RAISE EXCEPTION 'Wrong winner count (should not happen)';
|
jbe@430
|
410 END IF;
|
jbe@430
|
411 -- take indirect majorities into account:
|
jbe@430
|
412 LOOP
|
jbe@430
|
413 UPDATE "initiative" SET "indirect_majority" = TRUE
|
jbe@430
|
414 FROM (
|
jbe@430
|
415 SELECT "new_initiative"."id" AS "initiative_id"
|
jbe@430
|
416 FROM "initiative" "old_initiative"
|
jbe@430
|
417 JOIN "initiative" "new_initiative"
|
jbe@430
|
418 ON "new_initiative"."issue_id" = "issue_id_p"
|
jbe@430
|
419 AND "new_initiative"."indirect_majority" = FALSE
|
jbe@430
|
420 JOIN "battle" "battle_win"
|
jbe@430
|
421 ON "battle_win"."issue_id" = "issue_id_p"
|
jbe@430
|
422 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
|
jbe@430
|
423 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
|
jbe@430
|
424 JOIN "battle" "battle_lose"
|
jbe@430
|
425 ON "battle_lose"."issue_id" = "issue_id_p"
|
jbe@430
|
426 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
|
jbe@430
|
427 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
|
jbe@430
|
428 WHERE "old_initiative"."issue_id" = "issue_id_p"
|
jbe@430
|
429 AND "old_initiative"."indirect_majority" = TRUE
|
jbe@430
|
430 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
|
jbe@430
|
431 "battle_win"."count" * "policy_row"."indirect_majority_den" >
|
jbe@430
|
432 "policy_row"."indirect_majority_num" *
|
jbe@430
|
433 ("battle_win"."count"+"battle_lose"."count")
|
jbe@430
|
434 ELSE
|
jbe@430
|
435 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
|
jbe@430
|
436 "policy_row"."indirect_majority_num" *
|
jbe@430
|
437 ("battle_win"."count"+"battle_lose"."count")
|
jbe@430
|
438 END
|
jbe@430
|
439 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
|
jbe@430
|
440 AND "issue_row"."voter_count"-"battle_lose"."count" >=
|
jbe@430
|
441 "policy_row"."indirect_majority_non_negative"
|
jbe@430
|
442 ) AS "subquery"
|
jbe@430
|
443 WHERE "id" = "subquery"."initiative_id";
|
jbe@430
|
444 EXIT WHEN NOT FOUND;
|
jbe@430
|
445 END LOOP;
|
jbe@430
|
446 -- set "multistage_majority" for remaining matching initiatives:
|
jbe@430
|
447 UPDATE "initiative" SET "multistage_majority" = TRUE
|
jbe@430
|
448 FROM (
|
jbe@430
|
449 SELECT "losing_initiative"."id" AS "initiative_id"
|
jbe@430
|
450 FROM "initiative" "losing_initiative"
|
jbe@430
|
451 JOIN "initiative" "winning_initiative"
|
jbe@430
|
452 ON "winning_initiative"."issue_id" = "issue_id_p"
|
jbe@430
|
453 AND "winning_initiative"."admitted"
|
jbe@430
|
454 JOIN "battle" "battle_win"
|
jbe@430
|
455 ON "battle_win"."issue_id" = "issue_id_p"
|
jbe@430
|
456 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
|
jbe@430
|
457 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
|
jbe@430
|
458 JOIN "battle" "battle_lose"
|
jbe@430
|
459 ON "battle_lose"."issue_id" = "issue_id_p"
|
jbe@430
|
460 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
|
jbe@430
|
461 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
|
jbe@430
|
462 WHERE "losing_initiative"."issue_id" = "issue_id_p"
|
jbe@430
|
463 AND "losing_initiative"."admitted"
|
jbe@430
|
464 AND "winning_initiative"."schulze_rank" <
|
jbe@430
|
465 "losing_initiative"."schulze_rank"
|
jbe@430
|
466 AND "battle_win"."count" > "battle_lose"."count"
|
jbe@430
|
467 AND (
|
jbe@430
|
468 "battle_win"."count" > "winning_initiative"."positive_votes" OR
|
jbe@430
|
469 "battle_lose"."count" < "losing_initiative"."negative_votes" )
|
jbe@430
|
470 ) AS "subquery"
|
jbe@430
|
471 WHERE "id" = "subquery"."initiative_id";
|
jbe@430
|
472 -- mark eligible initiatives:
|
jbe@430
|
473 UPDATE "initiative" SET "eligible" = TRUE
|
jbe@430
|
474 WHERE "issue_id" = "issue_id_p"
|
jbe@430
|
475 AND "initiative"."direct_majority"
|
jbe@430
|
476 AND "initiative"."indirect_majority"
|
jbe@430
|
477 AND "initiative"."better_than_status_quo"
|
jbe@430
|
478 AND (
|
jbe@430
|
479 "policy_row"."no_multistage_majority" = FALSE OR
|
jbe@430
|
480 "initiative"."multistage_majority" = FALSE )
|
jbe@430
|
481 AND (
|
jbe@430
|
482 "policy_row"."no_reverse_beat_path" = FALSE OR
|
jbe@430
|
483 coalesce("initiative"."reverse_beat_path", FALSE) = FALSE );
|
jbe@430
|
484 -- mark final winner:
|
jbe@430
|
485 UPDATE "initiative" SET "winner" = TRUE
|
jbe@430
|
486 FROM (
|
jbe@430
|
487 SELECT "id" AS "initiative_id"
|
jbe@430
|
488 FROM "initiative"
|
jbe@430
|
489 WHERE "issue_id" = "issue_id_p" AND "eligible"
|
jbe@430
|
490 ORDER BY
|
jbe@430
|
491 "schulze_rank",
|
jbe@430
|
492 "id"
|
jbe@430
|
493 LIMIT 1
|
jbe@430
|
494 ) AS "subquery"
|
jbe@430
|
495 WHERE "id" = "subquery"."initiative_id";
|
jbe@430
|
496 -- write (final) ranks:
|
jbe@430
|
497 "rank_v" := 1;
|
jbe@430
|
498 FOR "initiative_id_v" IN
|
jbe@430
|
499 SELECT "id"
|
jbe@430
|
500 FROM "initiative"
|
jbe@430
|
501 WHERE "issue_id" = "issue_id_p" AND "admitted"
|
jbe@430
|
502 ORDER BY
|
jbe@430
|
503 "winner" DESC,
|
jbe@430
|
504 "eligible" DESC,
|
jbe@430
|
505 "schulze_rank",
|
jbe@430
|
506 "id"
|
jbe@430
|
507 LOOP
|
jbe@430
|
508 UPDATE "initiative" SET "rank" = "rank_v"
|
jbe@430
|
509 WHERE "id" = "initiative_id_v";
|
jbe@430
|
510 "rank_v" := "rank_v" + 1;
|
jbe@430
|
511 END LOOP;
|
jbe@430
|
512 -- set schulze rank of status quo and mark issue as finished:
|
jbe@430
|
513 UPDATE "issue" SET
|
jbe@430
|
514 "status_quo_schulze_rank" = "rank_ary"[1],
|
jbe@430
|
515 "state" =
|
jbe@430
|
516 CASE WHEN EXISTS (
|
jbe@430
|
517 SELECT NULL FROM "initiative"
|
jbe@430
|
518 WHERE "issue_id" = "issue_id_p" AND "winner"
|
jbe@430
|
519 ) THEN
|
jbe@430
|
520 'finished_with_winner'::"issue_state"
|
jbe@430
|
521 ELSE
|
jbe@430
|
522 'finished_without_winner'::"issue_state"
|
jbe@430
|
523 END,
|
jbe@430
|
524 "closed" = "phase_finished",
|
jbe@430
|
525 "phase_finished" = NULL
|
jbe@430
|
526 WHERE "id" = "issue_id_p";
|
jbe@430
|
527 RETURN;
|
jbe@430
|
528 END;
|
jbe@430
|
529 $$;
|
jbe@430
|
530
|
jbe@423
|
531
|
jbe@423
|
532 COMMIT;
|