liquid_feedback_core
changeset 170:b52f3281e769
Save Schulze rank of status quo in "issue" table
author | jbe |
---|---|
date | Tue Jun 07 19:39:22 2011 +0200 (2011-06-07) |
parents | ddd8e17d8f82 |
children | 60aaf3b11ca3 |
files | core.sql |
line diff
1.1 --- a/core.sql Tue Jun 07 18:02:58 2011 +0200 1.2 +++ b/core.sql Tue Jun 07 19:39:22 2011 +0200 1.3 @@ -469,6 +469,7 @@ 1.4 "latest_snapshot_event" "snapshot_event", 1.5 "population" INT4, 1.6 "voter_count" INT4, 1.7 + "status_quo_schulze_rank" INT4, 1.8 CONSTRAINT "valid_state" CHECK (( 1.9 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR 1.10 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR 1.11 @@ -517,20 +518,21 @@ 1.12 1.13 COMMENT ON TABLE "issue" IS 'Groups of initiatives'; 1.14 1.15 -COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"'; 1.16 -COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.'; 1.17 -COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed and voting has started; Frontends must ensure that for fully_frozen issues additionally to the restrictions for half_frozen issues a) initiatives are not created, b) no interest is created or removed, c) no supporters are added or removed, d) no opinions are created, changed or deleted.'; 1.18 -COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active; Frontends must ensure that for closed issues additionally to the restrictions for half_frozen and fully_frozen issues a) no voter is added or removed to/from the direct_voter table, b) no votes are added, modified or removed.'; 1.19 -COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated'; 1.20 -COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted'; 1.21 -COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue'; 1.22 -COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue'; 1.23 -COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue'; 1.24 -COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue'; 1.25 -COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated'; 1.26 -COMMENT ON COLUMN "issue"."latest_snapshot_event" IS 'Event type of latest snapshot for issue; Can be used to select the latest snapshot data in the snapshot tables'; 1.27 -COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"'; 1.28 -COMMENT ON COLUMN "issue"."voter_count" IS 'Total number of direct and delegating voters; This value is related to the final voting, while "population" is related to snapshots before the final voting'; 1.29 +COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"'; 1.30 +COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.'; 1.31 +COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed and voting has started; Frontends must ensure that for fully_frozen issues additionally to the restrictions for half_frozen issues a) initiatives are not created, b) no interest is created or removed, c) no supporters are added or removed, d) no opinions are created, changed or deleted.'; 1.32 +COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active; Frontends must ensure that for closed issues additionally to the restrictions for half_frozen and fully_frozen issues a) no voter is added or removed to/from the direct_voter table, b) no votes are added, modified or removed.'; 1.33 +COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated'; 1.34 +COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted'; 1.35 +COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue'; 1.36 +COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue'; 1.37 +COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue'; 1.38 +COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue'; 1.39 +COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated'; 1.40 +COMMENT ON COLUMN "issue"."latest_snapshot_event" IS 'Event type of latest snapshot for issue; Can be used to select the latest snapshot data in the snapshot tables'; 1.41 +COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"'; 1.42 +COMMENT ON COLUMN "issue"."voter_count" IS 'Total number of direct and delegating voters; This value is related to the final voting, while "population" is related to snapshots before the final voting'; 1.43 +COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function'; 1.44 1.45 1.46 CREATE TABLE "issue_setting" ( 1.47 @@ -562,9 +564,9 @@ 1.48 "negative_votes" INT4, 1.49 "direct_majority" BOOLEAN, 1.50 "indirect_majority" BOOLEAN, 1.51 + "schulze_rank" INT4, 1.52 "better_than_status_quo" BOOLEAN, 1.53 "worse_than_status_quo" BOOLEAN, 1.54 - "schulze_rank" INT4, 1.55 "reverse_beat_path" BOOLEAN, 1.56 "multistage_majority" BOOLEAN, 1.57 "eligible" BOOLEAN, 1.58 @@ -608,9 +610,9 @@ 1.59 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"'; 1.60 COMMENT ON COLUMN "initiative"."direct_majority" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "direct_majority_num"/"direct_majority_den", and "positive_votes" is greater-equal than "direct_majority_positive", and ("positive_votes"+abstentions) is greater-equal than "direct_majority_non_negative"'; 1.61 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths'; 1.62 +COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking'; 1.63 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)'; 1.64 COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)'; 1.65 -COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking'; 1.66 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'; 1.67 COMMENT ON COLUMN "initiative"."multistage_majority" IS 'TRUE, if either (a) this initiative has no better rank than the status quo, or (b) there exists a better ranked initiative X, which directly beats this initiative, and either more voters prefer X to this initiative than voters preferring X to the status quo or less voters prefer this initiative to X than voters preferring the status quo to X'; 1.68 COMMENT ON COLUMN "initiative"."eligible" IS 'Initiative is "attainable" and depending on selected policy has no "reverse_beat_path" or "multistage_majority"'; 1.69 @@ -3557,257 +3559,256 @@ 1.70 FROM "policy" WHERE "id" = "issue_row"."policy_id"; 1.71 SELECT count(1) INTO "dimension_v" 1.72 FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; 1.73 - IF "dimension_v" > 1 THEN 1.74 - -- Create "vote_matrix" with absolute number of votes in pairwise 1.75 - -- comparison: 1.76 - "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]); 1.77 - "i" := 1; 1.78 - "j" := 2; 1.79 - FOR "battle_row" IN 1.80 - SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" 1.81 - ORDER BY 1.82 - "winning_initiative_id" NULLS LAST, 1.83 - "losing_initiative_id" NULLS LAST 1.84 + -- Create "vote_matrix" with absolute number of votes in pairwise 1.85 + -- comparison: 1.86 + "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]); 1.87 + "i" := 1; 1.88 + "j" := 2; 1.89 + FOR "battle_row" IN 1.90 + SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" 1.91 + ORDER BY 1.92 + "winning_initiative_id" NULLS LAST, 1.93 + "losing_initiative_id" NULLS LAST 1.94 + LOOP 1.95 + "vote_matrix"["i"]["j"] := "battle_row"."count"; 1.96 + IF "j" = "dimension_v" THEN 1.97 + "i" := "i" + 1; 1.98 + "j" := 1; 1.99 + ELSE 1.100 + "j" := "j" + 1; 1.101 + IF "j" = "i" THEN 1.102 + "j" := "j" + 1; 1.103 + END IF; 1.104 + END IF; 1.105 + END LOOP; 1.106 + IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN 1.107 + RAISE EXCEPTION 'Wrong battle count (should not happen)'; 1.108 + END IF; 1.109 + -- Store defeat strengths in "matrix" using "defeat_strength" 1.110 + -- function: 1.111 + "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]); 1.112 + "i" := 1; 1.113 + LOOP 1.114 + "j" := 1; 1.115 LOOP 1.116 - "vote_matrix"["i"]["j"] := "battle_row"."count"; 1.117 - IF "j" = "dimension_v" THEN 1.118 - "i" := "i" + 1; 1.119 - "j" := 1; 1.120 - ELSE 1.121 - "j" := "j" + 1; 1.122 - IF "j" = "i" THEN 1.123 - "j" := "j" + 1; 1.124 - END IF; 1.125 + IF "i" != "j" THEN 1.126 + "matrix"["i"]["j"] := "defeat_strength"( 1.127 + "vote_matrix"["i"]["j"], 1.128 + "vote_matrix"["j"]["i"] 1.129 + ); 1.130 END IF; 1.131 + EXIT WHEN "j" = "dimension_v"; 1.132 + "j" := "j" + 1; 1.133 END LOOP; 1.134 - IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN 1.135 - RAISE EXCEPTION 'Wrong battle count (should not happen)'; 1.136 - END IF; 1.137 - -- Store defeat strengths in "matrix" using "defeat_strength" 1.138 - -- function: 1.139 - "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]); 1.140 + EXIT WHEN "i" = "dimension_v"; 1.141 + "i" := "i" + 1; 1.142 + END LOOP; 1.143 + -- Find best paths: 1.144 + "i" := 1; 1.145 + LOOP 1.146 + "j" := 1; 1.147 + LOOP 1.148 + IF "i" != "j" THEN 1.149 + "k" := 1; 1.150 + LOOP 1.151 + IF "i" != "k" AND "j" != "k" THEN 1.152 + IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN 1.153 + IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN 1.154 + "matrix"["j"]["k"] := "matrix"["j"]["i"]; 1.155 + END IF; 1.156 + ELSE 1.157 + IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN 1.158 + "matrix"["j"]["k"] := "matrix"["i"]["k"]; 1.159 + END IF; 1.160 + END IF; 1.161 + END IF; 1.162 + EXIT WHEN "k" = "dimension_v"; 1.163 + "k" := "k" + 1; 1.164 + END LOOP; 1.165 + END IF; 1.166 + EXIT WHEN "j" = "dimension_v"; 1.167 + "j" := "j" + 1; 1.168 + END LOOP; 1.169 + EXIT WHEN "i" = "dimension_v"; 1.170 + "i" := "i" + 1; 1.171 + END LOOP; 1.172 + -- Determine order of winners: 1.173 + "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]); 1.174 + "rank_v" := 1; 1.175 + "done_v" := 0; 1.176 + LOOP 1.177 + "winners_ary" := '{}'; 1.178 "i" := 1; 1.179 LOOP 1.180 - "j" := 1; 1.181 - LOOP 1.182 - IF "i" != "j" THEN 1.183 - "matrix"["i"]["j"] := "defeat_strength"( 1.184 - "vote_matrix"["i"]["j"], 1.185 - "vote_matrix"["j"]["i"] 1.186 - ); 1.187 - END IF; 1.188 - EXIT WHEN "j" = "dimension_v"; 1.189 - "j" := "j" + 1; 1.190 - END LOOP; 1.191 - EXIT WHEN "i" = "dimension_v"; 1.192 - "i" := "i" + 1; 1.193 - END LOOP; 1.194 - -- Find best paths: 1.195 - "i" := 1; 1.196 - LOOP 1.197 - "j" := 1; 1.198 - LOOP 1.199 - IF "i" != "j" THEN 1.200 - "k" := 1; 1.201 - LOOP 1.202 - IF "i" != "k" AND "j" != "k" THEN 1.203 - IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN 1.204 - IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN 1.205 - "matrix"["j"]["k"] := "matrix"["j"]["i"]; 1.206 - END IF; 1.207 - ELSE 1.208 - IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN 1.209 - "matrix"["j"]["k"] := "matrix"["i"]["k"]; 1.210 - END IF; 1.211 - END IF; 1.212 - END IF; 1.213 - EXIT WHEN "k" = "dimension_v"; 1.214 - "k" := "k" + 1; 1.215 - END LOOP; 1.216 - END IF; 1.217 - EXIT WHEN "j" = "dimension_v"; 1.218 - "j" := "j" + 1; 1.219 - END LOOP; 1.220 + IF "rank_ary"["i"] ISNULL THEN 1.221 + "j" := 1; 1.222 + LOOP 1.223 + IF 1.224 + "i" != "j" AND 1.225 + "rank_ary"["j"] ISNULL AND 1.226 + "matrix"["j"]["i"] > "matrix"["i"]["j"] 1.227 + THEN 1.228 + -- someone else is better 1.229 + EXIT; 1.230 + END IF; 1.231 + IF "j" = "dimension_v" THEN 1.232 + -- noone is better 1.233 + "winners_ary" := "winners_ary" || "i"; 1.234 + EXIT; 1.235 + END IF; 1.236 + "j" := "j" + 1; 1.237 + END LOOP; 1.238 + END IF; 1.239 EXIT WHEN "i" = "dimension_v"; 1.240 "i" := "i" + 1; 1.241 END LOOP; 1.242 - -- Determine order of winners: 1.243 - "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]); 1.244 - "rank_v" := 1; 1.245 - "done_v" := 0; 1.246 + "i" := 1; 1.247 LOOP 1.248 - "winners_ary" := '{}'; 1.249 - "i" := 1; 1.250 - LOOP 1.251 - IF "rank_ary"["i"] ISNULL THEN 1.252 - "j" := 1; 1.253 - LOOP 1.254 - IF 1.255 - "i" != "j" AND 1.256 - "rank_ary"["j"] ISNULL AND 1.257 - "matrix"["j"]["i"] > "matrix"["i"]["j"] 1.258 - THEN 1.259 - -- someone else is better 1.260 - EXIT; 1.261 - END IF; 1.262 - IF "j" = "dimension_v" THEN 1.263 - -- noone is better 1.264 - "winners_ary" := "winners_ary" || "i"; 1.265 - EXIT; 1.266 - END IF; 1.267 - "j" := "j" + 1; 1.268 - END LOOP; 1.269 - END IF; 1.270 - EXIT WHEN "i" = "dimension_v"; 1.271 - "i" := "i" + 1; 1.272 - END LOOP; 1.273 - "i" := 1; 1.274 - LOOP 1.275 - "rank_ary"["winners_ary"["i"]] := "rank_v"; 1.276 - "done_v" := "done_v" + 1; 1.277 - EXIT WHEN "i" = array_upper("winners_ary", 1); 1.278 - "i" := "i" + 1; 1.279 - END LOOP; 1.280 - EXIT WHEN "done_v" = "dimension_v"; 1.281 - "rank_v" := "rank_v" + 1; 1.282 - END LOOP; 1.283 - -- write preliminary results: 1.284 - "i" := 1; 1.285 - FOR "initiative_id_v" IN 1.286 - SELECT "id" FROM "initiative" 1.287 - WHERE "issue_id" = "issue_id_p" AND "admitted" 1.288 - ORDER BY "id" 1.289 - LOOP 1.290 - UPDATE "initiative" SET 1.291 - "direct_majority" = 1.292 - CASE WHEN "policy_row"."direct_majority_strict" THEN 1.293 - "positive_votes" * "policy_row"."direct_majority_den" > 1.294 - "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 1.295 - ELSE 1.296 - "positive_votes" * "policy_row"."direct_majority_den" >= 1.297 - "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 1.298 - END 1.299 - AND "positive_votes" >= "policy_row"."direct_majority_positive" 1.300 - AND "issue_row"."voter_count"-"negative_votes" >= 1.301 - "policy_row"."direct_majority_non_negative", 1.302 - "indirect_majority" = 1.303 - CASE WHEN "policy_row"."indirect_majority_strict" THEN 1.304 - "positive_votes" * "policy_row"."indirect_majority_den" > 1.305 - "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 1.306 - ELSE 1.307 - "positive_votes" * "policy_row"."indirect_majority_den" >= 1.308 - "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 1.309 - END 1.310 - AND "positive_votes" >= "policy_row"."indirect_majority_positive" 1.311 - AND "issue_row"."voter_count"-"negative_votes" >= 1.312 - "policy_row"."indirect_majority_non_negative", 1.313 - "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"], 1.314 - "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"], 1.315 - "schulze_rank" = "rank_ary"["i"], 1.316 - "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"], 1.317 - "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0 1.318 - WHERE "id" = "initiative_id_v"; 1.319 + "rank_ary"["winners_ary"["i"]] := "rank_v"; 1.320 + "done_v" := "done_v" + 1; 1.321 + EXIT WHEN "i" = array_upper("winners_ary", 1); 1.322 "i" := "i" + 1; 1.323 END LOOP; 1.324 - IF "i" != "dimension_v" THEN 1.325 - RAISE EXCEPTION 'Wrong winner count (should not happen)'; 1.326 - END IF; 1.327 - -- take indirect majorities into account: 1.328 - LOOP 1.329 - UPDATE "initiative" SET "indirect_majority" = TRUE 1.330 - FROM ( 1.331 - SELECT "new_initiative"."id" AS "initiative_id" 1.332 - FROM "initiative" "old_initiative" 1.333 - JOIN "initiative" "new_initiative" 1.334 - ON "new_initiative"."issue_id" = "issue_id_p" 1.335 - AND "new_initiative"."indirect_majority" = FALSE 1.336 - JOIN "battle" "battle_win" 1.337 - ON "battle_win"."issue_id" = "issue_id_p" 1.338 - AND "battle_win"."winning_initiative_id" = "new_initiative"."id" 1.339 - AND "battle_win"."losing_initiative_id" = "old_initiative"."id" 1.340 - JOIN "battle" "battle_lose" 1.341 - ON "battle_lose"."issue_id" = "issue_id_p" 1.342 - AND "battle_lose"."losing_initiative_id" = "new_initiative"."id" 1.343 - AND "battle_lose"."winning_initiative_id" = "old_initiative"."id" 1.344 - WHERE "old_initiative"."issue_id" = "issue_id_p" 1.345 - AND "old_initiative"."indirect_majority" = TRUE 1.346 - AND CASE WHEN "policy_row"."indirect_majority_strict" THEN 1.347 - "battle_win"."count" * "policy_row"."indirect_majority_den" > 1.348 - "policy_row"."indirect_majority_num" * 1.349 - ("battle_win"."count"+"battle_lose"."count") 1.350 - ELSE 1.351 - "battle_win"."count" * "policy_row"."indirect_majority_den" >= 1.352 - "policy_row"."indirect_majority_num" * 1.353 - ("battle_win"."count"+"battle_lose"."count") 1.354 - END 1.355 - AND "battle_win"."count" >= "policy_row"."indirect_majority_positive" 1.356 - AND "issue_row"."voter_count"-"battle_lose"."count" >= 1.357 - "policy_row"."indirect_majority_non_negative" 1.358 - ) AS "subquery" 1.359 - WHERE "id" = "subquery"."initiative_id"; 1.360 - EXIT WHEN NOT FOUND; 1.361 - END LOOP; 1.362 - -- set "multistage_majority" for remaining matching initiatives: 1.363 - UPDATE "initiative" SET "multistage_majority" = TRUE 1.364 + EXIT WHEN "done_v" = "dimension_v"; 1.365 + "rank_v" := "rank_v" + 1; 1.366 + END LOOP; 1.367 + -- write preliminary results: 1.368 + "i" := 1; 1.369 + FOR "initiative_id_v" IN 1.370 + SELECT "id" FROM "initiative" 1.371 + WHERE "issue_id" = "issue_id_p" AND "admitted" 1.372 + ORDER BY "id" 1.373 + LOOP 1.374 + UPDATE "initiative" SET 1.375 + "direct_majority" = 1.376 + CASE WHEN "policy_row"."direct_majority_strict" THEN 1.377 + "positive_votes" * "policy_row"."direct_majority_den" > 1.378 + "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 1.379 + ELSE 1.380 + "positive_votes" * "policy_row"."direct_majority_den" >= 1.381 + "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 1.382 + END 1.383 + AND "positive_votes" >= "policy_row"."direct_majority_positive" 1.384 + AND "issue_row"."voter_count"-"negative_votes" >= 1.385 + "policy_row"."direct_majority_non_negative", 1.386 + "indirect_majority" = 1.387 + CASE WHEN "policy_row"."indirect_majority_strict" THEN 1.388 + "positive_votes" * "policy_row"."indirect_majority_den" > 1.389 + "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 1.390 + ELSE 1.391 + "positive_votes" * "policy_row"."indirect_majority_den" >= 1.392 + "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 1.393 + END 1.394 + AND "positive_votes" >= "policy_row"."indirect_majority_positive" 1.395 + AND "issue_row"."voter_count"-"negative_votes" >= 1.396 + "policy_row"."indirect_majority_non_negative", 1.397 + "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"], 1.398 + "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"], 1.399 + "schulze_rank" = "rank_ary"["i"], 1.400 + "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"], 1.401 + "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0 1.402 + WHERE "id" = "initiative_id_v"; 1.403 + "i" := "i" + 1; 1.404 + END LOOP; 1.405 + IF "i" != "dimension_v" THEN 1.406 + RAISE EXCEPTION 'Wrong winner count (should not happen)'; 1.407 + END IF; 1.408 + -- take indirect majorities into account: 1.409 + LOOP 1.410 + UPDATE "initiative" SET "indirect_majority" = TRUE 1.411 FROM ( 1.412 - SELECT "losing_initiative"."id" AS "initiative_id" 1.413 - FROM "initiative" "losing_initiative" 1.414 - JOIN "initiative" "winning_initiative" 1.415 - ON "winning_initiative"."issue_id" = "issue_id_p" 1.416 - AND "winning_initiative"."admitted" 1.417 + SELECT "new_initiative"."id" AS "initiative_id" 1.418 + FROM "initiative" "old_initiative" 1.419 + JOIN "initiative" "new_initiative" 1.420 + ON "new_initiative"."issue_id" = "issue_id_p" 1.421 + AND "new_initiative"."indirect_majority" = FALSE 1.422 JOIN "battle" "battle_win" 1.423 ON "battle_win"."issue_id" = "issue_id_p" 1.424 - AND "battle_win"."winning_initiative_id" = "winning_initiative"."id" 1.425 - AND "battle_win"."losing_initiative_id" = "losing_initiative"."id" 1.426 + AND "battle_win"."winning_initiative_id" = "new_initiative"."id" 1.427 + AND "battle_win"."losing_initiative_id" = "old_initiative"."id" 1.428 JOIN "battle" "battle_lose" 1.429 ON "battle_lose"."issue_id" = "issue_id_p" 1.430 - AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id" 1.431 - AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id" 1.432 - WHERE "losing_initiative"."issue_id" = "issue_id_p" 1.433 - AND "losing_initiative"."admitted" 1.434 - AND "winning_initiative"."schulze_rank" < 1.435 - "losing_initiative"."schulze_rank" 1.436 - AND "battle_win"."count" > "battle_lose"."count" 1.437 - AND ( 1.438 - "battle_win"."count" > "winning_initiative"."positive_votes" OR 1.439 - "battle_lose"."count" < "losing_initiative"."negative_votes" ) 1.440 + AND "battle_lose"."losing_initiative_id" = "new_initiative"."id" 1.441 + AND "battle_lose"."winning_initiative_id" = "old_initiative"."id" 1.442 + WHERE "old_initiative"."issue_id" = "issue_id_p" 1.443 + AND "old_initiative"."indirect_majority" = TRUE 1.444 + AND CASE WHEN "policy_row"."indirect_majority_strict" THEN 1.445 + "battle_win"."count" * "policy_row"."indirect_majority_den" > 1.446 + "policy_row"."indirect_majority_num" * 1.447 + ("battle_win"."count"+"battle_lose"."count") 1.448 + ELSE 1.449 + "battle_win"."count" * "policy_row"."indirect_majority_den" >= 1.450 + "policy_row"."indirect_majority_num" * 1.451 + ("battle_win"."count"+"battle_lose"."count") 1.452 + END 1.453 + AND "battle_win"."count" >= "policy_row"."indirect_majority_positive" 1.454 + AND "issue_row"."voter_count"-"battle_lose"."count" >= 1.455 + "policy_row"."indirect_majority_non_negative" 1.456 ) AS "subquery" 1.457 WHERE "id" = "subquery"."initiative_id"; 1.458 - -- mark eligible initiatives: 1.459 - "rank_v" := 1; 1.460 - UPDATE "initiative" SET "eligible" = TRUE 1.461 - FROM ( 1.462 - SELECT "initiative"."id" AS "initiative_id" 1.463 - FROM "issue" 1.464 - JOIN "policy" 1.465 - ON "issue"."policy_id" = "policy"."id" 1.466 - JOIN "initiative" 1.467 - ON "issue"."id" = "initiative"."issue_id" 1.468 - WHERE "issue_id" = "issue_id_p" 1.469 - AND "initiative"."direct_majority" 1.470 - AND "initiative"."indirect_majority" 1.471 - AND "initiative"."better_than_status_quo" 1.472 - AND ( 1.473 - "policy"."no_multistage_majority" = FALSE OR 1.474 - "initiative"."multistage_majority" = FALSE ) 1.475 - AND ( 1.476 - "policy"."no_reverse_beat_path" = FALSE OR 1.477 - "initiative"."reverse_beat_path" = FALSE ) 1.478 - ) AS "subquery" 1.479 - WHERE "id" = "subquery"."initiative_id"; 1.480 - -- mark final winner: 1.481 - UPDATE "initiative" SET "winner" = TRUE 1.482 - FROM ( 1.483 - SELECT "id" AS "initiative_id" 1.484 - FROM "initiative" 1.485 - WHERE "issue_id" = "issue_id_p" AND "eligible" 1.486 - ORDER BY "schulze_rank", "id" 1.487 - LIMIT 1 1.488 - ) AS "subquery" 1.489 - WHERE "id" = "subquery"."initiative_id"; 1.490 - END IF; 1.491 - -- mark issue as finished: 1.492 + EXIT WHEN NOT FOUND; 1.493 + END LOOP; 1.494 + -- set "multistage_majority" for remaining matching initiatives: 1.495 + UPDATE "initiative" SET "multistage_majority" = TRUE 1.496 + FROM ( 1.497 + SELECT "losing_initiative"."id" AS "initiative_id" 1.498 + FROM "initiative" "losing_initiative" 1.499 + JOIN "initiative" "winning_initiative" 1.500 + ON "winning_initiative"."issue_id" = "issue_id_p" 1.501 + AND "winning_initiative"."admitted" 1.502 + JOIN "battle" "battle_win" 1.503 + ON "battle_win"."issue_id" = "issue_id_p" 1.504 + AND "battle_win"."winning_initiative_id" = "winning_initiative"."id" 1.505 + AND "battle_win"."losing_initiative_id" = "losing_initiative"."id" 1.506 + JOIN "battle" "battle_lose" 1.507 + ON "battle_lose"."issue_id" = "issue_id_p" 1.508 + AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id" 1.509 + AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id" 1.510 + WHERE "losing_initiative"."issue_id" = "issue_id_p" 1.511 + AND "losing_initiative"."admitted" 1.512 + AND "winning_initiative"."schulze_rank" < 1.513 + "losing_initiative"."schulze_rank" 1.514 + AND "battle_win"."count" > "battle_lose"."count" 1.515 + AND ( 1.516 + "battle_win"."count" > "winning_initiative"."positive_votes" OR 1.517 + "battle_lose"."count" < "losing_initiative"."negative_votes" ) 1.518 + ) AS "subquery" 1.519 + WHERE "id" = "subquery"."initiative_id"; 1.520 + -- mark eligible initiatives: 1.521 + "rank_v" := 1; 1.522 + UPDATE "initiative" SET "eligible" = TRUE 1.523 + FROM ( 1.524 + SELECT "initiative"."id" AS "initiative_id" 1.525 + FROM "issue" 1.526 + JOIN "policy" 1.527 + ON "issue"."policy_id" = "policy"."id" 1.528 + JOIN "initiative" 1.529 + ON "issue"."id" = "initiative"."issue_id" 1.530 + WHERE "issue_id" = "issue_id_p" 1.531 + AND "initiative"."direct_majority" 1.532 + AND "initiative"."indirect_majority" 1.533 + AND "initiative"."better_than_status_quo" 1.534 + AND ( 1.535 + "policy"."no_multistage_majority" = FALSE OR 1.536 + "initiative"."multistage_majority" = FALSE ) 1.537 + AND ( 1.538 + "policy"."no_reverse_beat_path" = FALSE OR 1.539 + "initiative"."reverse_beat_path" = FALSE ) 1.540 + ) AS "subquery" 1.541 + WHERE "id" = "subquery"."initiative_id"; 1.542 + -- mark final winner: 1.543 + UPDATE "initiative" SET "winner" = TRUE 1.544 + FROM ( 1.545 + SELECT "id" AS "initiative_id" 1.546 + FROM "initiative" 1.547 + WHERE "issue_id" = "issue_id_p" AND "eligible" 1.548 + ORDER BY "schulze_rank", "id" 1.549 + LIMIT 1 1.550 + ) AS "subquery" 1.551 + WHERE "id" = "subquery"."initiative_id"; 1.552 + -- set schulze rank of status quo and mark issue as finished: 1.553 UPDATE "issue" SET 1.554 + "status_quo_schulze_rank" = "rank_ary"["dimension_v"], 1.555 "state" = 1.556 CASE WHEN EXISTS ( 1.557 SELECT NULL FROM "initiative"