liquid_feedback_core
changeset 430:3031c1973748
Update script to v3.0.2 added
author | jbe |
---|---|
date | Thu May 22 05:29:28 2014 +0200 (2014-05-22) |
parents | a16072fc288a |
children | a1031e47760b |
files | update/core-update.v3.0.1-v3.0.2.sql |
line diff
1.1 --- a/update/core-update.v3.0.1-v3.0.2.sql Thu May 22 05:13:22 2014 +0200 1.2 +++ b/update/core-update.v3.0.1-v3.0.2.sql Thu May 22 05:29:28 2014 +0200 1.3 @@ -4,6 +4,529 @@ 1.4 SELECT * FROM (VALUES ('3.0.2', 3, 0, 2)) 1.5 AS "subquery"("string", "major", "minor", "revision"); 1.6 1.7 -TODO 1.8 + 1.9 +CREATE TYPE "defeat_strength" AS ENUM ('simple', 'tuple'); 1.10 + 1.11 +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'; 1.12 + 1.13 + 1.14 +CREATE TYPE "tie_breaking" AS ENUM ('simple', 'variant1', 'variant2'); 1.15 + 1.16 +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'; 1.17 + 1.18 + 1.19 +ALTER TABLE "policy" ADD COLUMN "defeat_strength" "defeat_strength" NOT NULL DEFAULT 'tuple'; 1.20 +ALTER TABLE "policy" ADD COLUMN "tie_breaking" "tie_breaking" NOT NULL DEFAULT 'variant1'; 1.21 + 1.22 +ALTER TABLE "policy" ADD 1.23 + CONSTRAINT "no_reverse_beat_path_requires_tuple_defeat_strength" CHECK ( 1.24 + ("defeat_strength" = 'tuple'::"defeat_strength" OR "no_reverse_beat_path" = FALSE) 1.25 + ); 1.26 + 1.27 +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'; 1.28 +COMMENT ON COLUMN "policy"."tie_breaking" IS 'Tie-breaker for the Schulze method; see type "tie_breaking"; ''variant1'' or ''variant2'' are recommended'; 1.29 +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'''; 1.30 + 1.31 + 1.32 +DROP FUNCTION "calculate_ranks"("issue"."id"%TYPE); 1.33 +DROP FUNCTION "defeat_strength"(INT4, INT4); 1.34 + 1.35 + 1.36 +CREATE FUNCTION "defeat_strength" 1.37 + ( "positive_votes_p" INT4, 1.38 + "negative_votes_p" INT4, 1.39 + "defeat_strength_p" "defeat_strength" ) 1.40 + RETURNS INT8 1.41 + LANGUAGE 'plpgsql' IMMUTABLE AS $$ 1.42 + BEGIN 1.43 + IF "defeat_strength_p" = 'simple'::"defeat_strength" THEN 1.44 + IF "positive_votes_p" > "negative_votes_p" THEN 1.45 + RETURN "positive_votes_p"; 1.46 + ELSE 1.47 + RETURN 0; 1.48 + END IF; 1.49 + ELSE 1.50 + IF "positive_votes_p" > "negative_votes_p" THEN 1.51 + RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8; 1.52 + ELSIF "positive_votes_p" = "negative_votes_p" THEN 1.53 + RETURN 0; 1.54 + ELSE 1.55 + RETURN -1; 1.56 + END IF; 1.57 + END IF; 1.58 + END; 1.59 + $$; 1.60 + 1.61 +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")'; 1.62 + 1.63 + 1.64 +CREATE FUNCTION "secondary_link_strength" 1.65 + ( "initiative1_ord_p" INT4, 1.66 + "initiative2_ord_p" INT4, 1.67 + "tie_breaking_p" "tie_breaking" ) 1.68 + RETURNS INT8 1.69 + LANGUAGE 'plpgsql' IMMUTABLE AS $$ 1.70 + BEGIN 1.71 + IF "initiative1_ord_p" = "initiative2_ord_p" THEN 1.72 + RAISE EXCEPTION 'Identical initiative ids passed to "secondary_link_strength" function (should not happen)'; 1.73 + END IF; 1.74 + RETURN ( 1.75 + CASE WHEN "tie_breaking_p" = 'simple'::"tie_breaking" THEN 1.76 + 0 1.77 + ELSE 1.78 + CASE WHEN "initiative1_ord_p" < "initiative2_ord_p" THEN 1.79 + 1::INT8 << 62 1.80 + ELSE 0 END 1.81 + + 1.82 + CASE WHEN "tie_breaking_p" = 'variant2'::"tie_breaking" THEN 1.83 + ("initiative2_ord_p"::INT8 << 31) - "initiative1_ord_p"::INT8 1.84 + ELSE 1.85 + "initiative2_ord_p"::INT8 - ("initiative1_ord_p"::INT8 << 31) 1.86 + END 1.87 + END 1.88 + ); 1.89 + END; 1.90 + $$; 1.91 + 1.92 +COMMENT ON FUNCTION "secondary_link_strength"(INT4, INT4, "tie_breaking") IS 'Calculates a secondary criterion for the defeat strength (tie-breaking of the links)'; 1.93 + 1.94 + 1.95 +CREATE TYPE "link_strength" AS ( 1.96 + "primary" INT8, 1.97 + "secondary" INT8 ); 1.98 + 1.99 +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'')'; 1.100 + 1.101 + 1.102 +CREATE FUNCTION "find_best_paths"("matrix_d" "link_strength"[][]) 1.103 + RETURNS "link_strength"[][] 1.104 + LANGUAGE 'plpgsql' IMMUTABLE AS $$ 1.105 + DECLARE 1.106 + "dimension_v" INT4; 1.107 + "matrix_p" "link_strength"[][]; 1.108 + "i" INT4; 1.109 + "j" INT4; 1.110 + "k" INT4; 1.111 + BEGIN 1.112 + "dimension_v" := array_upper("matrix_d", 1); 1.113 + "matrix_p" := "matrix_d"; 1.114 + "i" := 1; 1.115 + LOOP 1.116 + "j" := 1; 1.117 + LOOP 1.118 + IF "i" != "j" THEN 1.119 + "k" := 1; 1.120 + LOOP 1.121 + IF "i" != "k" AND "j" != "k" THEN 1.122 + IF "matrix_p"["j"]["i"] < "matrix_p"["i"]["k"] THEN 1.123 + IF "matrix_p"["j"]["i"] > "matrix_p"["j"]["k"] THEN 1.124 + "matrix_p"["j"]["k"] := "matrix_p"["j"]["i"]; 1.125 + END IF; 1.126 + ELSE 1.127 + IF "matrix_p"["i"]["k"] > "matrix_p"["j"]["k"] THEN 1.128 + "matrix_p"["j"]["k"] := "matrix_p"["i"]["k"]; 1.129 + END IF; 1.130 + END IF; 1.131 + END IF; 1.132 + EXIT WHEN "k" = "dimension_v"; 1.133 + "k" := "k" + 1; 1.134 + END LOOP; 1.135 + END IF; 1.136 + EXIT WHEN "j" = "dimension_v"; 1.137 + "j" := "j" + 1; 1.138 + END LOOP; 1.139 + EXIT WHEN "i" = "dimension_v"; 1.140 + "i" := "i" + 1; 1.141 + END LOOP; 1.142 + RETURN "matrix_p"; 1.143 + END; 1.144 + $$; 1.145 + 1.146 +COMMENT ON FUNCTION "find_best_paths"("link_strength"[][]) IS 'Computes the strengths of the best beat-paths from a square matrix'; 1.147 + 1.148 + 1.149 +CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE) 1.150 + RETURNS VOID 1.151 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.152 + DECLARE 1.153 + "issue_row" "issue"%ROWTYPE; 1.154 + "policy_row" "policy"%ROWTYPE; 1.155 + "dimension_v" INT4; 1.156 + "matrix_a" INT4[][]; -- absolute votes 1.157 + "matrix_d" "link_strength"[][]; -- defeat strength (direct) 1.158 + "matrix_p" "link_strength"[][]; -- defeat strength (best path) 1.159 + "matrix_t" "link_strength"[][]; -- defeat strength (tie-breaking) 1.160 + "matrix_f" BOOLEAN[][]; -- forbidden link (tie-breaking) 1.161 + "matrix_b" BOOLEAN[][]; -- final order (who beats who) 1.162 + "i" INT4; 1.163 + "j" INT4; 1.164 + "m" INT4; 1.165 + "n" INT4; 1.166 + "battle_row" "battle"%ROWTYPE; 1.167 + "rank_ary" INT4[]; 1.168 + "rank_v" INT4; 1.169 + "initiative_id_v" "initiative"."id"%TYPE; 1.170 + BEGIN 1.171 + PERFORM "require_transaction_isolation"(); 1.172 + SELECT * INTO "issue_row" 1.173 + FROM "issue" WHERE "id" = "issue_id_p"; 1.174 + SELECT * INTO "policy_row" 1.175 + FROM "policy" WHERE "id" = "issue_row"."policy_id"; 1.176 + SELECT count(1) INTO "dimension_v" 1.177 + FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; 1.178 + -- create "matrix_a" with absolute number of votes in pairwise 1.179 + -- comparison: 1.180 + "matrix_a" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]); 1.181 + "i" := 1; 1.182 + "j" := 2; 1.183 + FOR "battle_row" IN 1.184 + SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" 1.185 + ORDER BY 1.186 + "winning_initiative_id" NULLS FIRST, 1.187 + "losing_initiative_id" NULLS FIRST 1.188 + LOOP 1.189 + "matrix_a"["i"]["j"] := "battle_row"."count"; 1.190 + IF "j" = "dimension_v" THEN 1.191 + "i" := "i" + 1; 1.192 + "j" := 1; 1.193 + ELSE 1.194 + "j" := "j" + 1; 1.195 + IF "j" = "i" THEN 1.196 + "j" := "j" + 1; 1.197 + END IF; 1.198 + END IF; 1.199 + END LOOP; 1.200 + IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN 1.201 + RAISE EXCEPTION 'Wrong battle count (should not happen)'; 1.202 + END IF; 1.203 + -- store direct defeat strengths in "matrix_d" using "defeat_strength" 1.204 + -- and "secondary_link_strength" functions: 1.205 + "matrix_d" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]); 1.206 + "i" := 1; 1.207 + LOOP 1.208 + "j" := 1; 1.209 + LOOP 1.210 + IF "i" != "j" THEN 1.211 + "matrix_d"["i"]["j"] := ( 1.212 + "defeat_strength"( 1.213 + "matrix_a"["i"]["j"], 1.214 + "matrix_a"["j"]["i"], 1.215 + "policy_row"."defeat_strength" 1.216 + ), 1.217 + "secondary_link_strength"( 1.218 + "i", 1.219 + "j", 1.220 + "policy_row"."tie_breaking" 1.221 + ) 1.222 + )::"link_strength"; 1.223 + END IF; 1.224 + EXIT WHEN "j" = "dimension_v"; 1.225 + "j" := "j" + 1; 1.226 + END LOOP; 1.227 + EXIT WHEN "i" = "dimension_v"; 1.228 + "i" := "i" + 1; 1.229 + END LOOP; 1.230 + -- find best paths: 1.231 + "matrix_p" := "find_best_paths"("matrix_d"); 1.232 + -- create partial order: 1.233 + "matrix_b" := array_fill(NULL::BOOLEAN, ARRAY["dimension_v", "dimension_v"]); 1.234 + "i" := 1; 1.235 + LOOP 1.236 + "j" := "i" + 1; 1.237 + LOOP 1.238 + IF "i" != "j" THEN 1.239 + IF "matrix_p"["i"]["j"] > "matrix_p"["j"]["i"] THEN 1.240 + "matrix_b"["i"]["j"] := TRUE; 1.241 + "matrix_b"["j"]["i"] := FALSE; 1.242 + ELSIF "matrix_p"["i"]["j"] < "matrix_p"["j"]["i"] THEN 1.243 + "matrix_b"["i"]["j"] := FALSE; 1.244 + "matrix_b"["j"]["i"] := TRUE; 1.245 + END IF; 1.246 + END IF; 1.247 + EXIT WHEN "j" = "dimension_v"; 1.248 + "j" := "j" + 1; 1.249 + END LOOP; 1.250 + EXIT WHEN "i" = "dimension_v" - 1; 1.251 + "i" := "i" + 1; 1.252 + END LOOP; 1.253 + -- tie-breaking by forbidding shared weakest links in beat-paths 1.254 + -- (unless "tie_breaking" is set to 'simple', in which case tie-breaking 1.255 + -- is performed later by initiative id): 1.256 + IF "policy_row"."tie_breaking" != 'simple'::"tie_breaking" THEN 1.257 + "m" := 1; 1.258 + LOOP 1.259 + "n" := "m" + 1; 1.260 + LOOP 1.261 + -- only process those candidates m and n, which are tied: 1.262 + IF "matrix_b"["m"]["n"] ISNULL THEN 1.263 + -- start with beat-paths prior tie-breaking: 1.264 + "matrix_t" := "matrix_p"; 1.265 + -- start with all links allowed: 1.266 + "matrix_f" := array_fill(FALSE, ARRAY["dimension_v", "dimension_v"]); 1.267 + LOOP 1.268 + -- determine (and forbid) that link that is the weakest link 1.269 + -- in both the best path from candidate m to candidate n and 1.270 + -- from candidate n to candidate m: 1.271 + "i" := 1; 1.272 + <<forbid_one_link>> 1.273 + LOOP 1.274 + "j" := 1; 1.275 + LOOP 1.276 + IF "i" != "j" THEN 1.277 + IF "matrix_d"["i"]["j"] = "matrix_t"["m"]["n"] THEN 1.278 + "matrix_f"["i"]["j"] := TRUE; 1.279 + -- exit for performance reasons, 1.280 + -- as exactly one link will be found: 1.281 + EXIT forbid_one_link; 1.282 + END IF; 1.283 + END IF; 1.284 + EXIT WHEN "j" = "dimension_v"; 1.285 + "j" := "j" + 1; 1.286 + END LOOP; 1.287 + IF "i" = "dimension_v" THEN 1.288 + RAISE EXCEPTION 'Did not find shared weakest link for tie-breaking (should not happen)'; 1.289 + END IF; 1.290 + "i" := "i" + 1; 1.291 + END LOOP; 1.292 + -- calculate best beat-paths while ignoring forbidden links: 1.293 + "i" := 1; 1.294 + LOOP 1.295 + "j" := 1; 1.296 + LOOP 1.297 + IF "i" != "j" THEN 1.298 + "matrix_t"["i"]["j"] := CASE 1.299 + WHEN "matrix_f"["i"]["j"] 1.300 + THEN (-1::INT8) << 63 -- worst possible value 1.301 + ELSE "matrix_d"["i"]["j"] END; 1.302 + END IF; 1.303 + EXIT WHEN "j" = "dimension_v"; 1.304 + "j" := "j" + 1; 1.305 + END LOOP; 1.306 + EXIT WHEN "i" = "dimension_v"; 1.307 + "i" := "i" + 1; 1.308 + END LOOP; 1.309 + "matrix_t" := "find_best_paths"("matrix_t"); 1.310 + -- extend partial order, if tie-breaking was successful: 1.311 + IF "matrix_t"["m"]["n"] > "matrix_t"["n"]["m"] THEN 1.312 + "matrix_b"["m"]["n"] := TRUE; 1.313 + "matrix_b"["n"]["m"] := FALSE; 1.314 + EXIT; 1.315 + ELSIF "matrix_t"["m"]["n"] < "matrix_t"["n"]["m"] THEN 1.316 + "matrix_b"["m"]["n"] := FALSE; 1.317 + "matrix_b"["n"]["m"] := TRUE; 1.318 + EXIT; 1.319 + END IF; 1.320 + END LOOP; 1.321 + END IF; 1.322 + EXIT WHEN "n" = "dimension_v"; 1.323 + "n" := "n" + 1; 1.324 + END LOOP; 1.325 + EXIT WHEN "m" = "dimension_v" - 1; 1.326 + "m" := "m" + 1; 1.327 + END LOOP; 1.328 + END IF; 1.329 + -- store a unique ranking in "rank_ary": 1.330 + "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]); 1.331 + "rank_v" := 1; 1.332 + LOOP 1.333 + "i" := 1; 1.334 + <<assign_next_rank>> 1.335 + LOOP 1.336 + IF "rank_ary"["i"] ISNULL THEN 1.337 + "j" := 1; 1.338 + LOOP 1.339 + IF 1.340 + "i" != "j" AND 1.341 + "rank_ary"["j"] ISNULL AND 1.342 + ( "matrix_b"["j"]["i"] OR 1.343 + -- tie-breaking by "id" 1.344 + ( "matrix_b"["j"]["i"] ISNULL AND 1.345 + "j" < "i" ) ) 1.346 + THEN 1.347 + -- someone else is better 1.348 + EXIT; 1.349 + END IF; 1.350 + IF "j" = "dimension_v" THEN 1.351 + -- noone is better 1.352 + "rank_ary"["i"] := "rank_v"; 1.353 + EXIT assign_next_rank; 1.354 + END IF; 1.355 + "j" := "j" + 1; 1.356 + END LOOP; 1.357 + END IF; 1.358 + "i" := "i" + 1; 1.359 + IF "i" > "dimension_v" THEN 1.360 + RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)'; 1.361 + END IF; 1.362 + END LOOP; 1.363 + EXIT WHEN "rank_v" = "dimension_v"; 1.364 + "rank_v" := "rank_v" + 1; 1.365 + END LOOP; 1.366 + -- write preliminary results: 1.367 + "i" := 2; -- omit status quo with "i" = 1 1.368 + FOR "initiative_id_v" IN 1.369 + SELECT "id" FROM "initiative" 1.370 + WHERE "issue_id" = "issue_id_p" AND "admitted" 1.371 + ORDER BY "id" 1.372 + LOOP 1.373 + UPDATE "initiative" SET 1.374 + "direct_majority" = 1.375 + CASE WHEN "policy_row"."direct_majority_strict" THEN 1.376 + "positive_votes" * "policy_row"."direct_majority_den" > 1.377 + "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 1.378 + ELSE 1.379 + "positive_votes" * "policy_row"."direct_majority_den" >= 1.380 + "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 1.381 + END 1.382 + AND "positive_votes" >= "policy_row"."direct_majority_positive" 1.383 + AND "issue_row"."voter_count"-"negative_votes" >= 1.384 + "policy_row"."direct_majority_non_negative", 1.385 + "indirect_majority" = 1.386 + CASE WHEN "policy_row"."indirect_majority_strict" THEN 1.387 + "positive_votes" * "policy_row"."indirect_majority_den" > 1.388 + "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 1.389 + ELSE 1.390 + "positive_votes" * "policy_row"."indirect_majority_den" >= 1.391 + "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 1.392 + END 1.393 + AND "positive_votes" >= "policy_row"."indirect_majority_positive" 1.394 + AND "issue_row"."voter_count"-"negative_votes" >= 1.395 + "policy_row"."indirect_majority_non_negative", 1.396 + "schulze_rank" = "rank_ary"["i"], 1.397 + "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1], 1.398 + "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"[1], 1.399 + "multistage_majority" = "rank_ary"["i"] >= "rank_ary"[1], 1.400 + "reverse_beat_path" = CASE WHEN "policy_row"."defeat_strength" = 'simple'::"defeat_strength" 1.401 + THEN NULL 1.402 + ELSE "matrix_p"[1]["i"]."primary" >= 0 END, 1.403 + "eligible" = FALSE, 1.404 + "winner" = FALSE, 1.405 + "rank" = NULL -- NOTE: in cases of manual reset of issue state 1.406 + WHERE "id" = "initiative_id_v"; 1.407 + "i" := "i" + 1; 1.408 + END LOOP; 1.409 + IF "i" != "dimension_v" + 1 THEN 1.410 + RAISE EXCEPTION 'Wrong winner count (should not happen)'; 1.411 + END IF; 1.412 + -- take indirect majorities into account: 1.413 + LOOP 1.414 + UPDATE "initiative" SET "indirect_majority" = TRUE 1.415 + FROM ( 1.416 + SELECT "new_initiative"."id" AS "initiative_id" 1.417 + FROM "initiative" "old_initiative" 1.418 + JOIN "initiative" "new_initiative" 1.419 + ON "new_initiative"."issue_id" = "issue_id_p" 1.420 + AND "new_initiative"."indirect_majority" = FALSE 1.421 + JOIN "battle" "battle_win" 1.422 + ON "battle_win"."issue_id" = "issue_id_p" 1.423 + AND "battle_win"."winning_initiative_id" = "new_initiative"."id" 1.424 + AND "battle_win"."losing_initiative_id" = "old_initiative"."id" 1.425 + JOIN "battle" "battle_lose" 1.426 + ON "battle_lose"."issue_id" = "issue_id_p" 1.427 + AND "battle_lose"."losing_initiative_id" = "new_initiative"."id" 1.428 + AND "battle_lose"."winning_initiative_id" = "old_initiative"."id" 1.429 + WHERE "old_initiative"."issue_id" = "issue_id_p" 1.430 + AND "old_initiative"."indirect_majority" = TRUE 1.431 + AND CASE WHEN "policy_row"."indirect_majority_strict" THEN 1.432 + "battle_win"."count" * "policy_row"."indirect_majority_den" > 1.433 + "policy_row"."indirect_majority_num" * 1.434 + ("battle_win"."count"+"battle_lose"."count") 1.435 + ELSE 1.436 + "battle_win"."count" * "policy_row"."indirect_majority_den" >= 1.437 + "policy_row"."indirect_majority_num" * 1.438 + ("battle_win"."count"+"battle_lose"."count") 1.439 + END 1.440 + AND "battle_win"."count" >= "policy_row"."indirect_majority_positive" 1.441 + AND "issue_row"."voter_count"-"battle_lose"."count" >= 1.442 + "policy_row"."indirect_majority_non_negative" 1.443 + ) AS "subquery" 1.444 + WHERE "id" = "subquery"."initiative_id"; 1.445 + EXIT WHEN NOT FOUND; 1.446 + END LOOP; 1.447 + -- set "multistage_majority" for remaining matching initiatives: 1.448 + UPDATE "initiative" SET "multistage_majority" = TRUE 1.449 + FROM ( 1.450 + SELECT "losing_initiative"."id" AS "initiative_id" 1.451 + FROM "initiative" "losing_initiative" 1.452 + JOIN "initiative" "winning_initiative" 1.453 + ON "winning_initiative"."issue_id" = "issue_id_p" 1.454 + AND "winning_initiative"."admitted" 1.455 + JOIN "battle" "battle_win" 1.456 + ON "battle_win"."issue_id" = "issue_id_p" 1.457 + AND "battle_win"."winning_initiative_id" = "winning_initiative"."id" 1.458 + AND "battle_win"."losing_initiative_id" = "losing_initiative"."id" 1.459 + JOIN "battle" "battle_lose" 1.460 + ON "battle_lose"."issue_id" = "issue_id_p" 1.461 + AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id" 1.462 + AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id" 1.463 + WHERE "losing_initiative"."issue_id" = "issue_id_p" 1.464 + AND "losing_initiative"."admitted" 1.465 + AND "winning_initiative"."schulze_rank" < 1.466 + "losing_initiative"."schulze_rank" 1.467 + AND "battle_win"."count" > "battle_lose"."count" 1.468 + AND ( 1.469 + "battle_win"."count" > "winning_initiative"."positive_votes" OR 1.470 + "battle_lose"."count" < "losing_initiative"."negative_votes" ) 1.471 + ) AS "subquery" 1.472 + WHERE "id" = "subquery"."initiative_id"; 1.473 + -- mark eligible initiatives: 1.474 + UPDATE "initiative" SET "eligible" = TRUE 1.475 + WHERE "issue_id" = "issue_id_p" 1.476 + AND "initiative"."direct_majority" 1.477 + AND "initiative"."indirect_majority" 1.478 + AND "initiative"."better_than_status_quo" 1.479 + AND ( 1.480 + "policy_row"."no_multistage_majority" = FALSE OR 1.481 + "initiative"."multistage_majority" = FALSE ) 1.482 + AND ( 1.483 + "policy_row"."no_reverse_beat_path" = FALSE OR 1.484 + coalesce("initiative"."reverse_beat_path", FALSE) = FALSE ); 1.485 + -- mark final winner: 1.486 + UPDATE "initiative" SET "winner" = TRUE 1.487 + FROM ( 1.488 + SELECT "id" AS "initiative_id" 1.489 + FROM "initiative" 1.490 + WHERE "issue_id" = "issue_id_p" AND "eligible" 1.491 + ORDER BY 1.492 + "schulze_rank", 1.493 + "id" 1.494 + LIMIT 1 1.495 + ) AS "subquery" 1.496 + WHERE "id" = "subquery"."initiative_id"; 1.497 + -- write (final) ranks: 1.498 + "rank_v" := 1; 1.499 + FOR "initiative_id_v" IN 1.500 + SELECT "id" 1.501 + FROM "initiative" 1.502 + WHERE "issue_id" = "issue_id_p" AND "admitted" 1.503 + ORDER BY 1.504 + "winner" DESC, 1.505 + "eligible" DESC, 1.506 + "schulze_rank", 1.507 + "id" 1.508 + LOOP 1.509 + UPDATE "initiative" SET "rank" = "rank_v" 1.510 + WHERE "id" = "initiative_id_v"; 1.511 + "rank_v" := "rank_v" + 1; 1.512 + END LOOP; 1.513 + -- set schulze rank of status quo and mark issue as finished: 1.514 + UPDATE "issue" SET 1.515 + "status_quo_schulze_rank" = "rank_ary"[1], 1.516 + "state" = 1.517 + CASE WHEN EXISTS ( 1.518 + SELECT NULL FROM "initiative" 1.519 + WHERE "issue_id" = "issue_id_p" AND "winner" 1.520 + ) THEN 1.521 + 'finished_with_winner'::"issue_state" 1.522 + ELSE 1.523 + 'finished_without_winner'::"issue_state" 1.524 + END, 1.525 + "closed" = "phase_finished", 1.526 + "phase_finished" = NULL 1.527 + WHERE "id" = "issue_id_p"; 1.528 + RETURN; 1.529 + END; 1.530 + $$; 1.531 + 1.532 1.533 COMMIT;