liquid_feedback_core
view update/core-update.v3.0.1-v3.0.2.sql @ 441:f5c78b0590c6
Delete authority_* information from members in functions "delete_member" and "delete_private_data"
| author | jbe |
|---|---|
| date | Wed Jul 23 21:27:50 2014 +0200 (2014-07-23) |
| parents | 603f6e4bbedf |
| children |
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('3.0.2', 3, 0, 2))
5 AS "subquery"("string", "major", "minor", "revision");
8 CREATE TYPE "defeat_strength" AS ENUM ('simple', 'tuple');
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';
13 CREATE TYPE "tie_breaking" AS ENUM ('simple', 'variant1', 'variant2');
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';
18 ALTER TABLE "policy" ADD COLUMN "defeat_strength" "defeat_strength" NOT NULL DEFAULT 'tuple';
19 ALTER TABLE "policy" ADD COLUMN "tie_breaking" "tie_breaking" NOT NULL DEFAULT 'variant1';
21 ALTER TABLE "policy" ADD
22 CONSTRAINT "no_reverse_beat_path_requires_tuple_defeat_strength" CHECK (
23 ("defeat_strength" = 'tuple'::"defeat_strength" OR "no_reverse_beat_path" = FALSE)
24 );
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';
27 COMMENT ON COLUMN "policy"."tie_breaking" IS 'Tie-breaker for the Schulze method; see type "tie_breaking"; ''variant1'' or ''variant2'' are recommended';
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''';
31 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
32 RETURNS VOID
33 LANGUAGE 'plpgsql' VOLATILE AS $$
34 DECLARE
35 "area_id_v" "area"."id"%TYPE;
36 "unit_id_v" "unit"."id"%TYPE;
37 "member_id_v" "member"."id"%TYPE;
38 BEGIN
39 PERFORM "require_transaction_isolation"();
40 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
41 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
42 -- override protection triggers:
43 INSERT INTO "temporary_transaction_data" ("key", "value")
44 VALUES ('override_protection_triggers', TRUE::TEXT);
45 -- delete timestamp of voting comment:
46 UPDATE "direct_voter" SET "comment_changed" = NULL
47 WHERE "issue_id" = "issue_id_p";
48 -- delete delegating votes (in cases of manual reset of issue state):
49 DELETE FROM "delegating_voter"
50 WHERE "issue_id" = "issue_id_p";
51 -- delete votes from non-privileged voters:
52 DELETE FROM "direct_voter"
53 USING (
54 SELECT
55 "direct_voter"."member_id"
56 FROM "direct_voter"
57 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
58 LEFT JOIN "privilege"
59 ON "privilege"."unit_id" = "unit_id_v"
60 AND "privilege"."member_id" = "direct_voter"."member_id"
61 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
62 "member"."active" = FALSE OR
63 "privilege"."voting_right" ISNULL OR
64 "privilege"."voting_right" = FALSE
65 )
66 ) AS "subquery"
67 WHERE "direct_voter"."issue_id" = "issue_id_p"
68 AND "direct_voter"."member_id" = "subquery"."member_id";
69 -- consider delegations:
70 UPDATE "direct_voter" SET "weight" = 1
71 WHERE "issue_id" = "issue_id_p";
72 PERFORM "add_vote_delegations"("issue_id_p");
73 -- mark first preferences:
74 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
75 FROM (
76 SELECT
77 "vote"."initiative_id",
78 "vote"."member_id",
79 CASE WHEN "vote"."grade" > 0 THEN
80 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
81 ELSE NULL
82 END AS "first_preference"
83 FROM "vote"
84 JOIN "initiative" -- NOTE: due to missing index on issue_id
85 ON "vote"."issue_id" = "initiative"."issue_id"
86 JOIN "vote" AS "agg"
87 ON "initiative"."id" = "agg"."initiative_id"
88 AND "vote"."member_id" = "agg"."member_id"
89 GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
90 ) AS "subquery"
91 WHERE "vote"."issue_id" = "issue_id_p"
92 AND "vote"."initiative_id" = "subquery"."initiative_id"
93 AND "vote"."member_id" = "subquery"."member_id";
94 -- finish overriding protection triggers (avoids garbage):
95 DELETE FROM "temporary_transaction_data"
96 WHERE "key" = 'override_protection_triggers';
97 -- materialize battle_view:
98 -- NOTE: "closed" column of issue must be set at this point
99 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
100 INSERT INTO "battle" (
101 "issue_id",
102 "winning_initiative_id", "losing_initiative_id",
103 "count"
104 ) SELECT
105 "issue_id",
106 "winning_initiative_id", "losing_initiative_id",
107 "count"
108 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
109 -- set voter count:
110 UPDATE "issue" SET
111 "voter_count" = (
112 SELECT coalesce(sum("weight"), 0)
113 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
114 )
115 WHERE "id" = "issue_id_p";
116 -- calculate "first_preference_votes":
117 UPDATE "initiative"
118 SET "first_preference_votes" = coalesce("subquery"."sum", 0)
119 FROM (
120 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
121 FROM "vote" JOIN "direct_voter"
122 ON "vote"."issue_id" = "direct_voter"."issue_id"
123 AND "vote"."member_id" = "direct_voter"."member_id"
124 WHERE "vote"."first_preference"
125 GROUP BY "vote"."initiative_id"
126 ) AS "subquery"
127 WHERE "initiative"."issue_id" = "issue_id_p"
128 AND "initiative"."admitted"
129 AND "initiative"."id" = "subquery"."initiative_id";
130 -- copy "positive_votes" and "negative_votes" from "battle" table:
131 UPDATE "initiative" SET
132 "positive_votes" = "battle_win"."count",
133 "negative_votes" = "battle_lose"."count"
134 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
135 WHERE
136 "battle_win"."issue_id" = "issue_id_p" AND
137 "battle_win"."winning_initiative_id" = "initiative"."id" AND
138 "battle_win"."losing_initiative_id" ISNULL AND
139 "battle_lose"."issue_id" = "issue_id_p" AND
140 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
141 "battle_lose"."winning_initiative_id" ISNULL;
142 END;
143 $$;
146 DROP FUNCTION "calculate_ranks"("issue"."id"%TYPE);
147 DROP FUNCTION "defeat_strength"(INT4, INT4);
150 CREATE FUNCTION "defeat_strength"
151 ( "positive_votes_p" INT4,
152 "negative_votes_p" INT4,
153 "defeat_strength_p" "defeat_strength" )
154 RETURNS INT8
155 LANGUAGE 'plpgsql' IMMUTABLE AS $$
156 BEGIN
157 IF "defeat_strength_p" = 'simple'::"defeat_strength" THEN
158 IF "positive_votes_p" > "negative_votes_p" THEN
159 RETURN "positive_votes_p";
160 ELSE
161 RETURN 0;
162 END IF;
163 ELSE
164 IF "positive_votes_p" > "negative_votes_p" THEN
165 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
166 ELSIF "positive_votes_p" = "negative_votes_p" THEN
167 RETURN 0;
168 ELSE
169 RETURN -1;
170 END IF;
171 END IF;
172 END;
173 $$;
175 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")';
178 CREATE FUNCTION "secondary_link_strength"
179 ( "initiative1_ord_p" INT4,
180 "initiative2_ord_p" INT4,
181 "tie_breaking_p" "tie_breaking" )
182 RETURNS INT8
183 LANGUAGE 'plpgsql' IMMUTABLE AS $$
184 BEGIN
185 IF "initiative1_ord_p" = "initiative2_ord_p" THEN
186 RAISE EXCEPTION 'Identical initiative ids passed to "secondary_link_strength" function (should not happen)';
187 END IF;
188 RETURN (
189 CASE WHEN "tie_breaking_p" = 'simple'::"tie_breaking" THEN
190 0
191 ELSE
192 CASE WHEN "initiative1_ord_p" < "initiative2_ord_p" THEN
193 1::INT8 << 62
194 ELSE 0 END
195 +
196 CASE WHEN "tie_breaking_p" = 'variant2'::"tie_breaking" THEN
197 ("initiative2_ord_p"::INT8 << 31) - "initiative1_ord_p"::INT8
198 ELSE
199 "initiative2_ord_p"::INT8 - ("initiative1_ord_p"::INT8 << 31)
200 END
201 END
202 );
203 END;
204 $$;
206 COMMENT ON FUNCTION "secondary_link_strength"(INT4, INT4, "tie_breaking") IS 'Calculates a secondary criterion for the defeat strength (tie-breaking of the links)';
209 CREATE TYPE "link_strength" AS (
210 "primary" INT8,
211 "secondary" INT8 );
213 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'')';
216 CREATE FUNCTION "find_best_paths"("matrix_d" "link_strength"[][])
217 RETURNS "link_strength"[][]
218 LANGUAGE 'plpgsql' IMMUTABLE AS $$
219 DECLARE
220 "dimension_v" INT4;
221 "matrix_p" "link_strength"[][];
222 "i" INT4;
223 "j" INT4;
224 "k" INT4;
225 BEGIN
226 "dimension_v" := array_upper("matrix_d", 1);
227 "matrix_p" := "matrix_d";
228 "i" := 1;
229 LOOP
230 "j" := 1;
231 LOOP
232 IF "i" != "j" THEN
233 "k" := 1;
234 LOOP
235 IF "i" != "k" AND "j" != "k" THEN
236 IF "matrix_p"["j"]["i"] < "matrix_p"["i"]["k"] THEN
237 IF "matrix_p"["j"]["i"] > "matrix_p"["j"]["k"] THEN
238 "matrix_p"["j"]["k"] := "matrix_p"["j"]["i"];
239 END IF;
240 ELSE
241 IF "matrix_p"["i"]["k"] > "matrix_p"["j"]["k"] THEN
242 "matrix_p"["j"]["k"] := "matrix_p"["i"]["k"];
243 END IF;
244 END IF;
245 END IF;
246 EXIT WHEN "k" = "dimension_v";
247 "k" := "k" + 1;
248 END LOOP;
249 END IF;
250 EXIT WHEN "j" = "dimension_v";
251 "j" := "j" + 1;
252 END LOOP;
253 EXIT WHEN "i" = "dimension_v";
254 "i" := "i" + 1;
255 END LOOP;
256 RETURN "matrix_p";
257 END;
258 $$;
260 COMMENT ON FUNCTION "find_best_paths"("link_strength"[][]) IS 'Computes the strengths of the best beat-paths from a square matrix';
263 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
264 RETURNS VOID
265 LANGUAGE 'plpgsql' VOLATILE AS $$
266 DECLARE
267 "issue_row" "issue"%ROWTYPE;
268 "policy_row" "policy"%ROWTYPE;
269 "dimension_v" INT4;
270 "matrix_a" INT4[][]; -- absolute votes
271 "matrix_d" "link_strength"[][]; -- defeat strength (direct)
272 "matrix_p" "link_strength"[][]; -- defeat strength (best path)
273 "matrix_t" "link_strength"[][]; -- defeat strength (tie-breaking)
274 "matrix_f" BOOLEAN[][]; -- forbidden link (tie-breaking)
275 "matrix_b" BOOLEAN[][]; -- final order (who beats who)
276 "i" INT4;
277 "j" INT4;
278 "m" INT4;
279 "n" INT4;
280 "battle_row" "battle"%ROWTYPE;
281 "rank_ary" INT4[];
282 "rank_v" INT4;
283 "initiative_id_v" "initiative"."id"%TYPE;
284 BEGIN
285 PERFORM "require_transaction_isolation"();
286 SELECT * INTO "issue_row"
287 FROM "issue" WHERE "id" = "issue_id_p";
288 SELECT * INTO "policy_row"
289 FROM "policy" WHERE "id" = "issue_row"."policy_id";
290 SELECT count(1) INTO "dimension_v"
291 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
292 -- create "matrix_a" with absolute number of votes in pairwise
293 -- comparison:
294 "matrix_a" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
295 "i" := 1;
296 "j" := 2;
297 FOR "battle_row" IN
298 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
299 ORDER BY
300 "winning_initiative_id" NULLS FIRST,
301 "losing_initiative_id" NULLS FIRST
302 LOOP
303 "matrix_a"["i"]["j"] := "battle_row"."count";
304 IF "j" = "dimension_v" THEN
305 "i" := "i" + 1;
306 "j" := 1;
307 ELSE
308 "j" := "j" + 1;
309 IF "j" = "i" THEN
310 "j" := "j" + 1;
311 END IF;
312 END IF;
313 END LOOP;
314 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
315 RAISE EXCEPTION 'Wrong battle count (should not happen)';
316 END IF;
317 -- store direct defeat strengths in "matrix_d" using "defeat_strength"
318 -- and "secondary_link_strength" functions:
319 "matrix_d" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
320 "i" := 1;
321 LOOP
322 "j" := 1;
323 LOOP
324 IF "i" != "j" THEN
325 "matrix_d"["i"]["j"] := (
326 "defeat_strength"(
327 "matrix_a"["i"]["j"],
328 "matrix_a"["j"]["i"],
329 "policy_row"."defeat_strength"
330 ),
331 "secondary_link_strength"(
332 "i",
333 "j",
334 "policy_row"."tie_breaking"
335 )
336 )::"link_strength";
337 END IF;
338 EXIT WHEN "j" = "dimension_v";
339 "j" := "j" + 1;
340 END LOOP;
341 EXIT WHEN "i" = "dimension_v";
342 "i" := "i" + 1;
343 END LOOP;
344 -- find best paths:
345 "matrix_p" := "find_best_paths"("matrix_d");
346 -- create partial order:
347 "matrix_b" := array_fill(NULL::BOOLEAN, ARRAY["dimension_v", "dimension_v"]);
348 "i" := 1;
349 LOOP
350 "j" := "i" + 1;
351 LOOP
352 IF "i" != "j" THEN
353 IF "matrix_p"["i"]["j"] > "matrix_p"["j"]["i"] THEN
354 "matrix_b"["i"]["j"] := TRUE;
355 "matrix_b"["j"]["i"] := FALSE;
356 ELSIF "matrix_p"["i"]["j"] < "matrix_p"["j"]["i"] THEN
357 "matrix_b"["i"]["j"] := FALSE;
358 "matrix_b"["j"]["i"] := TRUE;
359 END IF;
360 END IF;
361 EXIT WHEN "j" = "dimension_v";
362 "j" := "j" + 1;
363 END LOOP;
364 EXIT WHEN "i" = "dimension_v" - 1;
365 "i" := "i" + 1;
366 END LOOP;
367 -- tie-breaking by forbidding shared weakest links in beat-paths
368 -- (unless "tie_breaking" is set to 'simple', in which case tie-breaking
369 -- is performed later by initiative id):
370 IF "policy_row"."tie_breaking" != 'simple'::"tie_breaking" THEN
371 "m" := 1;
372 LOOP
373 "n" := "m" + 1;
374 LOOP
375 -- only process those candidates m and n, which are tied:
376 IF "matrix_b"["m"]["n"] ISNULL THEN
377 -- start with beat-paths prior tie-breaking:
378 "matrix_t" := "matrix_p";
379 -- start with all links allowed:
380 "matrix_f" := array_fill(FALSE, ARRAY["dimension_v", "dimension_v"]);
381 LOOP
382 -- determine (and forbid) that link that is the weakest link
383 -- in both the best path from candidate m to candidate n and
384 -- from candidate n to candidate m:
385 "i" := 1;
386 <<forbid_one_link>>
387 LOOP
388 "j" := 1;
389 LOOP
390 IF "i" != "j" THEN
391 IF "matrix_d"["i"]["j"] = "matrix_t"["m"]["n"] THEN
392 "matrix_f"["i"]["j"] := TRUE;
393 -- exit for performance reasons,
394 -- as exactly one link will be found:
395 EXIT forbid_one_link;
396 END IF;
397 END IF;
398 EXIT WHEN "j" = "dimension_v";
399 "j" := "j" + 1;
400 END LOOP;
401 IF "i" = "dimension_v" THEN
402 RAISE EXCEPTION 'Did not find shared weakest link for tie-breaking (should not happen)';
403 END IF;
404 "i" := "i" + 1;
405 END LOOP;
406 -- calculate best beat-paths while ignoring forbidden links:
407 "i" := 1;
408 LOOP
409 "j" := 1;
410 LOOP
411 IF "i" != "j" THEN
412 "matrix_t"["i"]["j"] := CASE
413 WHEN "matrix_f"["i"]["j"]
414 THEN ((-1::INT8) << 63, 0)::"link_strength" -- worst possible value
415 ELSE "matrix_d"["i"]["j"] END;
416 END IF;
417 EXIT WHEN "j" = "dimension_v";
418 "j" := "j" + 1;
419 END LOOP;
420 EXIT WHEN "i" = "dimension_v";
421 "i" := "i" + 1;
422 END LOOP;
423 "matrix_t" := "find_best_paths"("matrix_t");
424 -- extend partial order, if tie-breaking was successful:
425 IF "matrix_t"["m"]["n"] > "matrix_t"["n"]["m"] THEN
426 "matrix_b"["m"]["n"] := TRUE;
427 "matrix_b"["n"]["m"] := FALSE;
428 EXIT;
429 ELSIF "matrix_t"["m"]["n"] < "matrix_t"["n"]["m"] THEN
430 "matrix_b"["m"]["n"] := FALSE;
431 "matrix_b"["n"]["m"] := TRUE;
432 EXIT;
433 END IF;
434 END LOOP;
435 END IF;
436 EXIT WHEN "n" = "dimension_v";
437 "n" := "n" + 1;
438 END LOOP;
439 EXIT WHEN "m" = "dimension_v" - 1;
440 "m" := "m" + 1;
441 END LOOP;
442 END IF;
443 -- store a unique ranking in "rank_ary":
444 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
445 "rank_v" := 1;
446 LOOP
447 "i" := 1;
448 <<assign_next_rank>>
449 LOOP
450 IF "rank_ary"["i"] ISNULL THEN
451 "j" := 1;
452 LOOP
453 IF
454 "i" != "j" AND
455 "rank_ary"["j"] ISNULL AND
456 ( "matrix_b"["j"]["i"] OR
457 -- tie-breaking by "id"
458 ( "matrix_b"["j"]["i"] ISNULL AND
459 "j" < "i" ) )
460 THEN
461 -- someone else is better
462 EXIT;
463 END IF;
464 IF "j" = "dimension_v" THEN
465 -- noone is better
466 "rank_ary"["i"] := "rank_v";
467 EXIT assign_next_rank;
468 END IF;
469 "j" := "j" + 1;
470 END LOOP;
471 END IF;
472 "i" := "i" + 1;
473 IF "i" > "dimension_v" THEN
474 RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
475 END IF;
476 END LOOP;
477 EXIT WHEN "rank_v" = "dimension_v";
478 "rank_v" := "rank_v" + 1;
479 END LOOP;
480 -- write preliminary results:
481 "i" := 2; -- omit status quo with "i" = 1
482 FOR "initiative_id_v" IN
483 SELECT "id" FROM "initiative"
484 WHERE "issue_id" = "issue_id_p" AND "admitted"
485 ORDER BY "id"
486 LOOP
487 UPDATE "initiative" SET
488 "direct_majority" =
489 CASE WHEN "policy_row"."direct_majority_strict" THEN
490 "positive_votes" * "policy_row"."direct_majority_den" >
491 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
492 ELSE
493 "positive_votes" * "policy_row"."direct_majority_den" >=
494 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
495 END
496 AND "positive_votes" >= "policy_row"."direct_majority_positive"
497 AND "issue_row"."voter_count"-"negative_votes" >=
498 "policy_row"."direct_majority_non_negative",
499 "indirect_majority" =
500 CASE WHEN "policy_row"."indirect_majority_strict" THEN
501 "positive_votes" * "policy_row"."indirect_majority_den" >
502 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
503 ELSE
504 "positive_votes" * "policy_row"."indirect_majority_den" >=
505 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
506 END
507 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
508 AND "issue_row"."voter_count"-"negative_votes" >=
509 "policy_row"."indirect_majority_non_negative",
510 "schulze_rank" = "rank_ary"["i"],
511 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
512 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"[1],
513 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"[1],
514 "reverse_beat_path" = CASE WHEN "policy_row"."defeat_strength" = 'simple'::"defeat_strength"
515 THEN NULL
516 ELSE "matrix_p"[1]["i"]."primary" >= 0 END,
517 "eligible" = FALSE,
518 "winner" = FALSE,
519 "rank" = NULL -- NOTE: in cases of manual reset of issue state
520 WHERE "id" = "initiative_id_v";
521 "i" := "i" + 1;
522 END LOOP;
523 IF "i" != "dimension_v" + 1 THEN
524 RAISE EXCEPTION 'Wrong winner count (should not happen)';
525 END IF;
526 -- take indirect majorities into account:
527 LOOP
528 UPDATE "initiative" SET "indirect_majority" = TRUE
529 FROM (
530 SELECT "new_initiative"."id" AS "initiative_id"
531 FROM "initiative" "old_initiative"
532 JOIN "initiative" "new_initiative"
533 ON "new_initiative"."issue_id" = "issue_id_p"
534 AND "new_initiative"."indirect_majority" = FALSE
535 JOIN "battle" "battle_win"
536 ON "battle_win"."issue_id" = "issue_id_p"
537 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
538 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
539 JOIN "battle" "battle_lose"
540 ON "battle_lose"."issue_id" = "issue_id_p"
541 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
542 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
543 WHERE "old_initiative"."issue_id" = "issue_id_p"
544 AND "old_initiative"."indirect_majority" = TRUE
545 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
546 "battle_win"."count" * "policy_row"."indirect_majority_den" >
547 "policy_row"."indirect_majority_num" *
548 ("battle_win"."count"+"battle_lose"."count")
549 ELSE
550 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
551 "policy_row"."indirect_majority_num" *
552 ("battle_win"."count"+"battle_lose"."count")
553 END
554 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
555 AND "issue_row"."voter_count"-"battle_lose"."count" >=
556 "policy_row"."indirect_majority_non_negative"
557 ) AS "subquery"
558 WHERE "id" = "subquery"."initiative_id";
559 EXIT WHEN NOT FOUND;
560 END LOOP;
561 -- set "multistage_majority" for remaining matching initiatives:
562 UPDATE "initiative" SET "multistage_majority" = TRUE
563 FROM (
564 SELECT "losing_initiative"."id" AS "initiative_id"
565 FROM "initiative" "losing_initiative"
566 JOIN "initiative" "winning_initiative"
567 ON "winning_initiative"."issue_id" = "issue_id_p"
568 AND "winning_initiative"."admitted"
569 JOIN "battle" "battle_win"
570 ON "battle_win"."issue_id" = "issue_id_p"
571 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
572 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
573 JOIN "battle" "battle_lose"
574 ON "battle_lose"."issue_id" = "issue_id_p"
575 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
576 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
577 WHERE "losing_initiative"."issue_id" = "issue_id_p"
578 AND "losing_initiative"."admitted"
579 AND "winning_initiative"."schulze_rank" <
580 "losing_initiative"."schulze_rank"
581 AND "battle_win"."count" > "battle_lose"."count"
582 AND (
583 "battle_win"."count" > "winning_initiative"."positive_votes" OR
584 "battle_lose"."count" < "losing_initiative"."negative_votes" )
585 ) AS "subquery"
586 WHERE "id" = "subquery"."initiative_id";
587 -- mark eligible initiatives:
588 UPDATE "initiative" SET "eligible" = TRUE
589 WHERE "issue_id" = "issue_id_p"
590 AND "initiative"."direct_majority"
591 AND "initiative"."indirect_majority"
592 AND "initiative"."better_than_status_quo"
593 AND (
594 "policy_row"."no_multistage_majority" = FALSE OR
595 "initiative"."multistage_majority" = FALSE )
596 AND (
597 "policy_row"."no_reverse_beat_path" = FALSE OR
598 coalesce("initiative"."reverse_beat_path", FALSE) = FALSE );
599 -- mark final winner:
600 UPDATE "initiative" SET "winner" = TRUE
601 FROM (
602 SELECT "id" AS "initiative_id"
603 FROM "initiative"
604 WHERE "issue_id" = "issue_id_p" AND "eligible"
605 ORDER BY
606 "schulze_rank",
607 "id"
608 LIMIT 1
609 ) AS "subquery"
610 WHERE "id" = "subquery"."initiative_id";
611 -- write (final) ranks:
612 "rank_v" := 1;
613 FOR "initiative_id_v" IN
614 SELECT "id"
615 FROM "initiative"
616 WHERE "issue_id" = "issue_id_p" AND "admitted"
617 ORDER BY
618 "winner" DESC,
619 "eligible" DESC,
620 "schulze_rank",
621 "id"
622 LOOP
623 UPDATE "initiative" SET "rank" = "rank_v"
624 WHERE "id" = "initiative_id_v";
625 "rank_v" := "rank_v" + 1;
626 END LOOP;
627 -- set schulze rank of status quo and mark issue as finished:
628 UPDATE "issue" SET
629 "status_quo_schulze_rank" = "rank_ary"[1],
630 "state" =
631 CASE WHEN EXISTS (
632 SELECT NULL FROM "initiative"
633 WHERE "issue_id" = "issue_id_p" AND "winner"
634 ) THEN
635 'finished_with_winner'::"issue_state"
636 ELSE
637 'finished_without_winner'::"issue_state"
638 END,
639 "closed" = "phase_finished",
640 "phase_finished" = NULL
641 WHERE "id" = "issue_id_p";
642 RETURN;
643 END;
644 $$;
647 COMMIT;