liquid_feedback_core
view update/core-update.v2.0.8-v2.0.9.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 | 7b8966b801e5 |
children |
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('2.0.9', 2, 0, 9))
5 AS "subquery"("string", "major", "minor", "revision");
7 -- Bugfix of error in update script to v2.0.0:
8 ALTER TABLE "battle" ALTER COLUMN "winning_initiative_id" DROP NOT NULL;
9 ALTER TABLE "battle" ALTER COLUMN "losing_initiative_id" DROP NOT NULL;
11 CREATE OR REPLACE VIEW "unit_member_count" AS
12 SELECT
13 "unit"."id" AS "unit_id",
14 count("member"."id") AS "member_count"
15 FROM "unit"
16 LEFT JOIN "privilege"
17 ON "privilege"."unit_id" = "unit"."id"
18 AND "privilege"."voting_right"
19 LEFT JOIN "member"
20 ON "member"."id" = "privilege"."member_id"
21 AND "member"."active"
22 GROUP BY "unit"."id";
24 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
26 CREATE FUNCTION "delegation_chain_for_closed_issue"
27 ( "member_id_p" "member"."id"%TYPE,
28 "issue_id_p" "issue"."id"%TYPE )
29 RETURNS SETOF "delegation_chain_row"
30 LANGUAGE 'plpgsql' STABLE AS $$
31 DECLARE
32 "output_row" "delegation_chain_row";
33 "direct_voter_row" "direct_voter"%ROWTYPE;
34 "delegating_voter_row" "delegating_voter"%ROWTYPE;
35 BEGIN
36 "output_row"."index" := 0;
37 "output_row"."member_id" := "member_id_p";
38 "output_row"."member_valid" := TRUE;
39 "output_row"."participation" := FALSE;
40 "output_row"."overridden" := FALSE;
41 "output_row"."disabled_out" := FALSE;
42 LOOP
43 SELECT INTO "direct_voter_row" * FROM "direct_voter"
44 WHERE "issue_id" = "issue_id_p"
45 AND "member_id" = "output_row"."member_id";
46 IF "direct_voter_row"."member_id" NOTNULL THEN
47 "output_row"."participation" := TRUE;
48 "output_row"."scope_out" := NULL;
49 "output_row"."disabled_out" := NULL;
50 RETURN NEXT "output_row";
51 RETURN;
52 END IF;
53 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
54 WHERE "issue_id" = "issue_id_p"
55 AND "member_id" = "output_row"."member_id";
56 IF "delegating_voter_row"."member_id" ISNULL THEN
57 RETURN;
58 END IF;
59 "output_row"."scope_out" := "delegating_voter_row"."scope";
60 RETURN NEXT "output_row";
61 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
62 "output_row"."scope_in" := "output_row"."scope_out";
63 END LOOP;
64 END;
65 $$;
67 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
68 ( "member"."id"%TYPE,
69 "member"."id"%TYPE )
70 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
72 DROP FUNCTION "delegation_chain"
73 ( "member"."id"%TYPE,
74 "unit"."id"%TYPE,
75 "area"."id"%TYPE,
76 "issue"."id"%TYPE );
78 DROP FUNCTION "delegation_chain"
79 ( "member"."id"%TYPE,
80 "unit"."id"%TYPE,
81 "area"."id"%TYPE,
82 "issue"."id"%TYPE,
83 "member"."id"%TYPE );
85 CREATE FUNCTION "delegation_chain"
86 ( "member_id_p" "member"."id"%TYPE,
87 "unit_id_p" "unit"."id"%TYPE,
88 "area_id_p" "area"."id"%TYPE,
89 "issue_id_p" "issue"."id"%TYPE,
90 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL )
91 RETURNS SETOF "delegation_chain_row"
92 LANGUAGE 'plpgsql' STABLE AS $$
93 DECLARE
94 "scope_v" "delegation_scope";
95 "unit_id_v" "unit"."id"%TYPE;
96 "area_id_v" "area"."id"%TYPE;
97 "issue_row" "issue"%ROWTYPE;
98 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
99 "loop_member_id_v" "member"."id"%TYPE;
100 "output_row" "delegation_chain_row";
101 "output_rows" "delegation_chain_row"[];
102 "delegation_row" "delegation"%ROWTYPE;
103 "row_count" INT4;
104 "i" INT4;
105 "loop_v" BOOLEAN;
106 BEGIN
107 IF
108 "unit_id_p" NOTNULL AND
109 "area_id_p" ISNULL AND
110 "issue_id_p" ISNULL
111 THEN
112 "scope_v" := 'unit';
113 "unit_id_v" := "unit_id_p";
114 ELSIF
115 "unit_id_p" ISNULL AND
116 "area_id_p" NOTNULL AND
117 "issue_id_p" ISNULL
118 THEN
119 "scope_v" := 'area';
120 "area_id_v" := "area_id_p";
121 SELECT "unit_id" INTO "unit_id_v"
122 FROM "area" WHERE "id" = "area_id_v";
123 ELSIF
124 "unit_id_p" ISNULL AND
125 "area_id_p" ISNULL AND
126 "issue_id_p" NOTNULL
127 THEN
128 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
129 IF "issue_row"."id" ISNULL THEN
130 RETURN;
131 END IF;
132 IF "issue_row"."closed" NOTNULL THEN
133 IF "simulate_trustee_id_p" NOTNULL THEN
134 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
135 END IF;
136 FOR "output_row" IN
137 SELECT * FROM
138 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
139 LOOP
140 RETURN NEXT "output_row";
141 END LOOP;
142 RETURN;
143 END IF;
144 "scope_v" := 'issue';
145 SELECT "area_id" INTO "area_id_v"
146 FROM "issue" WHERE "id" = "issue_id_p";
147 SELECT "unit_id" INTO "unit_id_v"
148 FROM "area" WHERE "id" = "area_id_v";
149 ELSE
150 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
151 END IF;
152 "visited_member_ids" := '{}';
153 "loop_member_id_v" := NULL;
154 "output_rows" := '{}';
155 "output_row"."index" := 0;
156 "output_row"."member_id" := "member_id_p";
157 "output_row"."member_valid" := TRUE;
158 "output_row"."participation" := FALSE;
159 "output_row"."overridden" := FALSE;
160 "output_row"."disabled_out" := FALSE;
161 "output_row"."scope_out" := NULL;
162 LOOP
163 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
164 "loop_member_id_v" := "output_row"."member_id";
165 ELSE
166 "visited_member_ids" :=
167 "visited_member_ids" || "output_row"."member_id";
168 END IF;
169 IF "output_row"."participation" ISNULL THEN
170 "output_row"."overridden" := NULL;
171 ELSIF "output_row"."participation" THEN
172 "output_row"."overridden" := TRUE;
173 END IF;
174 "output_row"."scope_in" := "output_row"."scope_out";
175 IF EXISTS (
176 SELECT NULL FROM "member" JOIN "privilege"
177 ON "privilege"."member_id" = "member"."id"
178 AND "privilege"."unit_id" = "unit_id_v"
179 WHERE "id" = "output_row"."member_id"
180 AND "member"."active" AND "privilege"."voting_right"
181 ) THEN
182 IF "scope_v" = 'unit' THEN
183 SELECT * INTO "delegation_row" FROM "delegation"
184 WHERE "truster_id" = "output_row"."member_id"
185 AND "unit_id" = "unit_id_v";
186 ELSIF "scope_v" = 'area' THEN
187 "output_row"."participation" := EXISTS (
188 SELECT NULL FROM "membership"
189 WHERE "area_id" = "area_id_p"
190 AND "member_id" = "output_row"."member_id"
191 );
192 SELECT * INTO "delegation_row" FROM "delegation"
193 WHERE "truster_id" = "output_row"."member_id"
194 AND (
195 "unit_id" = "unit_id_v" OR
196 "area_id" = "area_id_v"
197 )
198 ORDER BY "scope" DESC;
199 ELSIF "scope_v" = 'issue' THEN
200 IF "issue_row"."fully_frozen" ISNULL THEN
201 "output_row"."participation" := EXISTS (
202 SELECT NULL FROM "interest"
203 WHERE "issue_id" = "issue_id_p"
204 AND "member_id" = "output_row"."member_id"
205 );
206 ELSE
207 IF "output_row"."member_id" = "member_id_p" THEN
208 "output_row"."participation" := EXISTS (
209 SELECT NULL FROM "direct_voter"
210 WHERE "issue_id" = "issue_id_p"
211 AND "member_id" = "output_row"."member_id"
212 );
213 ELSE
214 "output_row"."participation" := NULL;
215 END IF;
216 END IF;
217 SELECT * INTO "delegation_row" FROM "delegation"
218 WHERE "truster_id" = "output_row"."member_id"
219 AND (
220 "unit_id" = "unit_id_v" OR
221 "area_id" = "area_id_v" OR
222 "issue_id" = "issue_id_p"
223 )
224 ORDER BY "scope" DESC;
225 END IF;
226 ELSE
227 "output_row"."member_valid" := FALSE;
228 "output_row"."participation" := FALSE;
229 "output_row"."scope_out" := NULL;
230 "delegation_row" := ROW(NULL);
231 END IF;
232 IF
233 "output_row"."member_id" = "member_id_p" AND
234 "simulate_trustee_id_p" NOTNULL
235 THEN
236 "output_row"."scope_out" := "scope_v";
237 "output_rows" := "output_rows" || "output_row";
238 "output_row"."member_id" := "simulate_trustee_id_p";
239 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
240 "output_row"."scope_out" := "delegation_row"."scope";
241 "output_rows" := "output_rows" || "output_row";
242 "output_row"."member_id" := "delegation_row"."trustee_id";
243 ELSIF "delegation_row"."scope" NOTNULL THEN
244 "output_row"."scope_out" := "delegation_row"."scope";
245 "output_row"."disabled_out" := TRUE;
246 "output_rows" := "output_rows" || "output_row";
247 EXIT;
248 ELSE
249 "output_row"."scope_out" := NULL;
250 "output_rows" := "output_rows" || "output_row";
251 EXIT;
252 END IF;
253 EXIT WHEN "loop_member_id_v" NOTNULL;
254 "output_row"."index" := "output_row"."index" + 1;
255 END LOOP;
256 "row_count" := array_upper("output_rows", 1);
257 "i" := 1;
258 "loop_v" := FALSE;
259 LOOP
260 "output_row" := "output_rows"["i"];
261 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
262 IF "loop_v" THEN
263 IF "i" + 1 = "row_count" THEN
264 "output_row"."loop" := 'last';
265 ELSIF "i" = "row_count" THEN
266 "output_row"."loop" := 'repetition';
267 ELSE
268 "output_row"."loop" := 'intermediate';
269 END IF;
270 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
271 "output_row"."loop" := 'first';
272 "loop_v" := TRUE;
273 END IF;
274 IF "scope_v" = 'unit' THEN
275 "output_row"."participation" := NULL;
276 END IF;
277 RETURN NEXT "output_row";
278 "i" := "i" + 1;
279 END LOOP;
280 RETURN;
281 END;
282 $$;
284 COMMENT ON FUNCTION "delegation_chain"
285 ( "member"."id"%TYPE,
286 "unit"."id"%TYPE,
287 "area"."id"%TYPE,
288 "issue"."id"%TYPE,
289 "member"."id"%TYPE )
290 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
292 CREATE TYPE "delegation_info_loop_type" AS ENUM
293 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
295 COMMENT ON TYPE "delegation_info_loop_type" IS 'Type of "delegation_loop" in "delegation_info_type"; ''own'' means loop to self, ''first'' means loop to first trustee, ''first_ellipsis'' means loop to ellipsis after first trustee, ''other'' means loop to other trustee, ''other_ellipsis'' means loop to ellipsis after other trustee''';
297 CREATE TYPE "delegation_info_type" AS (
298 "own_participation" BOOLEAN,
299 "own_delegation_scope" "delegation_scope",
300 "first_trustee_id" INT4,
301 "first_trustee_participation" BOOLEAN,
302 "first_trustee_ellipsis" BOOLEAN,
303 "other_trustee_id" INT4,
304 "other_trustee_participation" BOOLEAN,
305 "other_trustee_ellipsis" BOOLEAN,
306 "delegation_loop" "delegation_info_loop_type");
308 COMMENT ON TYPE "delegation_info_type" IS 'Type of result returned by "delegation_info" function; For meaning of "participation" check comment on "delegation_chain_row" type';
310 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
311 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
312 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
313 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
314 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
315 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
316 COMMENT ON COLUMN "delegation_info_type"."other_trustee_participation" IS 'Another trustee is participating (redundant field: if "other_trustee_id" is set, then "other_trustee_participation" is always TRUE, else "other_trustee_participation" is NULL)';
317 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
318 COMMENT ON COLUMN "delegation_info_type"."delegation_loop" IS 'Non-NULL value, if delegation chain contains a circle; See comment on "delegation_info_loop_type" for details';
320 CREATE FUNCTION "delegation_info"
321 ( "member_id_p" "member"."id"%TYPE,
322 "unit_id_p" "unit"."id"%TYPE,
323 "area_id_p" "area"."id"%TYPE,
324 "issue_id_p" "issue"."id"%TYPE,
325 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL )
326 RETURNS "delegation_info_type"
327 LANGUAGE 'plpgsql' STABLE AS $$
328 DECLARE
329 "current_row" "delegation_chain_row";
330 "result" "delegation_info_type";
331 BEGIN
332 "result"."own_participation" := FALSE;
333 FOR "current_row" IN
334 SELECT * FROM "delegation_chain"(
335 "member_id_p",
336 "unit_id_p", "area_id_p", "issue_id_p",
337 "simulate_trustee_id_p")
338 LOOP
339 IF "current_row"."member_id" = "member_id_p" THEN
340 "result"."own_participation" := "current_row"."participation";
341 "result"."own_delegation_scope" := "current_row"."scope_out";
342 IF "current_row"."loop" = 'first' THEN
343 "result"."delegation_loop" := 'own';
344 END IF;
345 ELSIF
346 "current_row"."member_valid" AND
347 ( "current_row"."loop" ISNULL OR
348 "current_row"."loop" != 'repetition' )
349 THEN
350 IF "result"."first_trustee_id" ISNULL THEN
351 "result"."first_trustee_id" := "current_row"."member_id";
352 "result"."first_trustee_participation" := "current_row"."participation";
353 "result"."first_trustee_ellipsis" := FALSE;
354 IF "current_row"."loop" = 'first' THEN
355 "result"."delegation_loop" := 'first';
356 END IF;
357 ELSIF "result"."other_trustee_id" ISNULL THEN
358 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
359 "result"."other_trustee_id" := "current_row"."member_id";
360 "result"."other_trustee_participation" := TRUE;
361 "result"."other_trustee_ellipsis" := FALSE;
362 IF "current_row"."loop" = 'first' THEN
363 "result"."delegation_loop" := 'other';
364 END IF;
365 ELSE
366 "result"."first_trustee_ellipsis" := TRUE;
367 IF "current_row"."loop" = 'first' THEN
368 "result"."delegation_loop" := 'first_ellipsis';
369 END IF;
370 END IF;
371 ELSE
372 "result"."other_trustee_ellipsis" := TRUE;
373 IF "current_row"."loop" = 'first' THEN
374 "result"."delegation_loop" := 'other_ellipsis';
375 END IF;
376 END IF;
377 END IF;
378 END LOOP;
379 RETURN "result";
380 END;
381 $$;
383 COMMENT ON FUNCTION "delegation_info"
384 ( "member"."id"%TYPE,
385 "unit"."id"%TYPE,
386 "area"."id"%TYPE,
387 "issue"."id"%TYPE,
388 "member"."id"%TYPE )
389 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
391 CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
392 RETURNS VOID
393 LANGUAGE 'plpgsql' VOLATILE AS $$
394 DECLARE
395 "issue_row" "issue"%ROWTYPE;
396 "policy_row" "policy"%ROWTYPE;
397 "dimension_v" INTEGER;
398 "vote_matrix" INT4[][]; -- absolute votes
399 "matrix" INT8[][]; -- defeat strength / best paths
400 "i" INTEGER;
401 "j" INTEGER;
402 "k" INTEGER;
403 "battle_row" "battle"%ROWTYPE;
404 "rank_ary" INT4[];
405 "rank_v" INT4;
406 "done_v" INTEGER;
407 "winners_ary" INTEGER[];
408 "initiative_id_v" "initiative"."id"%TYPE;
409 BEGIN
410 SELECT * INTO "issue_row"
411 FROM "issue" WHERE "id" = "issue_id_p"
412 FOR UPDATE;
413 SELECT * INTO "policy_row"
414 FROM "policy" WHERE "id" = "issue_row"."policy_id";
415 SELECT count(1) INTO "dimension_v"
416 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
417 -- Create "vote_matrix" with absolute number of votes in pairwise
418 -- comparison:
419 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
420 "i" := 1;
421 "j" := 2;
422 FOR "battle_row" IN
423 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
424 ORDER BY
425 "winning_initiative_id" NULLS LAST,
426 "losing_initiative_id" NULLS LAST
427 LOOP
428 "vote_matrix"["i"]["j"] := "battle_row"."count";
429 IF "j" = "dimension_v" THEN
430 "i" := "i" + 1;
431 "j" := 1;
432 ELSE
433 "j" := "j" + 1;
434 IF "j" = "i" THEN
435 "j" := "j" + 1;
436 END IF;
437 END IF;
438 END LOOP;
439 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
440 RAISE EXCEPTION 'Wrong battle count (should not happen)';
441 END IF;
442 -- Store defeat strengths in "matrix" using "defeat_strength"
443 -- function:
444 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
445 "i" := 1;
446 LOOP
447 "j" := 1;
448 LOOP
449 IF "i" != "j" THEN
450 "matrix"["i"]["j"] := "defeat_strength"(
451 "vote_matrix"["i"]["j"],
452 "vote_matrix"["j"]["i"]
453 );
454 END IF;
455 EXIT WHEN "j" = "dimension_v";
456 "j" := "j" + 1;
457 END LOOP;
458 EXIT WHEN "i" = "dimension_v";
459 "i" := "i" + 1;
460 END LOOP;
461 -- Find best paths:
462 "i" := 1;
463 LOOP
464 "j" := 1;
465 LOOP
466 IF "i" != "j" THEN
467 "k" := 1;
468 LOOP
469 IF "i" != "k" AND "j" != "k" THEN
470 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
471 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
472 "matrix"["j"]["k"] := "matrix"["j"]["i"];
473 END IF;
474 ELSE
475 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
476 "matrix"["j"]["k"] := "matrix"["i"]["k"];
477 END IF;
478 END IF;
479 END IF;
480 EXIT WHEN "k" = "dimension_v";
481 "k" := "k" + 1;
482 END LOOP;
483 END IF;
484 EXIT WHEN "j" = "dimension_v";
485 "j" := "j" + 1;
486 END LOOP;
487 EXIT WHEN "i" = "dimension_v";
488 "i" := "i" + 1;
489 END LOOP;
490 -- Determine order of winners:
491 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
492 "rank_v" := 1;
493 "done_v" := 0;
494 LOOP
495 "winners_ary" := '{}';
496 "i" := 1;
497 LOOP
498 IF "rank_ary"["i"] ISNULL THEN
499 "j" := 1;
500 LOOP
501 IF
502 "i" != "j" AND
503 "rank_ary"["j"] ISNULL AND
504 "matrix"["j"]["i"] > "matrix"["i"]["j"]
505 THEN
506 -- someone else is better
507 EXIT;
508 END IF;
509 IF "j" = "dimension_v" THEN
510 -- noone is better
511 "winners_ary" := "winners_ary" || "i";
512 EXIT;
513 END IF;
514 "j" := "j" + 1;
515 END LOOP;
516 END IF;
517 EXIT WHEN "i" = "dimension_v";
518 "i" := "i" + 1;
519 END LOOP;
520 "i" := 1;
521 LOOP
522 "rank_ary"["winners_ary"["i"]] := "rank_v";
523 "done_v" := "done_v" + 1;
524 EXIT WHEN "i" = array_upper("winners_ary", 1);
525 "i" := "i" + 1;
526 END LOOP;
527 EXIT WHEN "done_v" = "dimension_v";
528 "rank_v" := "rank_v" + 1;
529 END LOOP;
530 -- write preliminary results:
531 "i" := 1;
532 FOR "initiative_id_v" IN
533 SELECT "id" FROM "initiative"
534 WHERE "issue_id" = "issue_id_p" AND "admitted"
535 ORDER BY "id"
536 LOOP
537 UPDATE "initiative" SET
538 "direct_majority" =
539 CASE WHEN "policy_row"."direct_majority_strict" THEN
540 "positive_votes" * "policy_row"."direct_majority_den" >
541 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
542 ELSE
543 "positive_votes" * "policy_row"."direct_majority_den" >=
544 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
545 END
546 AND "positive_votes" >= "policy_row"."direct_majority_positive"
547 AND "issue_row"."voter_count"-"negative_votes" >=
548 "policy_row"."direct_majority_non_negative",
549 "indirect_majority" =
550 CASE WHEN "policy_row"."indirect_majority_strict" THEN
551 "positive_votes" * "policy_row"."indirect_majority_den" >
552 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
553 ELSE
554 "positive_votes" * "policy_row"."indirect_majority_den" >=
555 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
556 END
557 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
558 AND "issue_row"."voter_count"-"negative_votes" >=
559 "policy_row"."indirect_majority_non_negative",
560 "schulze_rank" = "rank_ary"["i"],
561 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
562 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
563 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
564 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,
565 "eligible" = FALSE,
566 "winner" = FALSE,
567 "rank" = NULL -- NOTE: in cases of manual reset of issue state
568 WHERE "id" = "initiative_id_v";
569 "i" := "i" + 1;
570 END LOOP;
571 IF "i" != "dimension_v" THEN
572 RAISE EXCEPTION 'Wrong winner count (should not happen)';
573 END IF;
574 -- take indirect majorities into account:
575 LOOP
576 UPDATE "initiative" SET "indirect_majority" = TRUE
577 FROM (
578 SELECT "new_initiative"."id" AS "initiative_id"
579 FROM "initiative" "old_initiative"
580 JOIN "initiative" "new_initiative"
581 ON "new_initiative"."issue_id" = "issue_id_p"
582 AND "new_initiative"."indirect_majority" = FALSE
583 JOIN "battle" "battle_win"
584 ON "battle_win"."issue_id" = "issue_id_p"
585 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
586 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
587 JOIN "battle" "battle_lose"
588 ON "battle_lose"."issue_id" = "issue_id_p"
589 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
590 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
591 WHERE "old_initiative"."issue_id" = "issue_id_p"
592 AND "old_initiative"."indirect_majority" = TRUE
593 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
594 "battle_win"."count" * "policy_row"."indirect_majority_den" >
595 "policy_row"."indirect_majority_num" *
596 ("battle_win"."count"+"battle_lose"."count")
597 ELSE
598 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
599 "policy_row"."indirect_majority_num" *
600 ("battle_win"."count"+"battle_lose"."count")
601 END
602 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
603 AND "issue_row"."voter_count"-"battle_lose"."count" >=
604 "policy_row"."indirect_majority_non_negative"
605 ) AS "subquery"
606 WHERE "id" = "subquery"."initiative_id";
607 EXIT WHEN NOT FOUND;
608 END LOOP;
609 -- set "multistage_majority" for remaining matching initiatives:
610 UPDATE "initiative" SET "multistage_majority" = TRUE
611 FROM (
612 SELECT "losing_initiative"."id" AS "initiative_id"
613 FROM "initiative" "losing_initiative"
614 JOIN "initiative" "winning_initiative"
615 ON "winning_initiative"."issue_id" = "issue_id_p"
616 AND "winning_initiative"."admitted"
617 JOIN "battle" "battle_win"
618 ON "battle_win"."issue_id" = "issue_id_p"
619 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
620 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
621 JOIN "battle" "battle_lose"
622 ON "battle_lose"."issue_id" = "issue_id_p"
623 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
624 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
625 WHERE "losing_initiative"."issue_id" = "issue_id_p"
626 AND "losing_initiative"."admitted"
627 AND "winning_initiative"."schulze_rank" <
628 "losing_initiative"."schulze_rank"
629 AND "battle_win"."count" > "battle_lose"."count"
630 AND (
631 "battle_win"."count" > "winning_initiative"."positive_votes" OR
632 "battle_lose"."count" < "losing_initiative"."negative_votes" )
633 ) AS "subquery"
634 WHERE "id" = "subquery"."initiative_id";
635 -- mark eligible initiatives:
636 UPDATE "initiative" SET "eligible" = TRUE
637 WHERE "issue_id" = "issue_id_p"
638 AND "initiative"."direct_majority"
639 AND "initiative"."indirect_majority"
640 AND "initiative"."better_than_status_quo"
641 AND (
642 "policy_row"."no_multistage_majority" = FALSE OR
643 "initiative"."multistage_majority" = FALSE )
644 AND (
645 "policy_row"."no_reverse_beat_path" = FALSE OR
646 "initiative"."reverse_beat_path" = FALSE );
647 -- mark final winner:
648 UPDATE "initiative" SET "winner" = TRUE
649 FROM (
650 SELECT "id" AS "initiative_id"
651 FROM "initiative"
652 WHERE "issue_id" = "issue_id_p" AND "eligible"
653 ORDER BY
654 "schulze_rank",
655 "vote_ratio"("positive_votes", "negative_votes"),
656 "id"
657 LIMIT 1
658 ) AS "subquery"
659 WHERE "id" = "subquery"."initiative_id";
660 -- write (final) ranks:
661 "rank_v" := 1;
662 FOR "initiative_id_v" IN
663 SELECT "id"
664 FROM "initiative"
665 WHERE "issue_id" = "issue_id_p" AND "admitted"
666 ORDER BY
667 "winner" DESC,
668 "eligible" DESC,
669 "schulze_rank",
670 "vote_ratio"("positive_votes", "negative_votes"),
671 "id"
672 LOOP
673 UPDATE "initiative" SET "rank" = "rank_v"
674 WHERE "id" = "initiative_id_v";
675 "rank_v" := "rank_v" + 1;
676 END LOOP;
677 -- set schulze rank of status quo and mark issue as finished:
678 UPDATE "issue" SET
679 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
680 "state" =
681 CASE WHEN EXISTS (
682 SELECT NULL FROM "initiative"
683 WHERE "issue_id" = "issue_id_p" AND "winner"
684 ) THEN
685 'finished_with_winner'::"issue_state"
686 ELSE
687 'finished_without_winner'::"issue_state"
688 END,
689 "ranks_available" = TRUE
690 WHERE "id" = "issue_id_p";
691 RETURN;
692 END;
693 $$;
695 COMMIT;