liquid_feedback_core
changeset 412:399dc1a86398 v3.0.0
Marked version as v3.0.0
| author | jbe |
|---|---|
| date | Fri Jan 31 12:46:11 2014 +0100 (2014-01-31) |
| parents | 44a07d8f1bb4 |
| children | e024c50cfe3d |
| files | LICENSE core.sql update/core-update.v2.2.5-v2.2.6.sql update/core-update.v2.2.5-v3.0.0.sql |
line diff
1.1 --- a/LICENSE Mon Dec 23 20:22:32 2013 +0100 1.2 +++ b/LICENSE Fri Jan 31 12:46:11 2014 +0100 1.3 @@ -1,4 +1,4 @@ 1.4 -Copyright (c) 2009-2013 Public Software Group e. V., Berlin, Germany 1.5 +Copyright (c) 2009-2014 Public Software Group e. V., Berlin, Germany 1.6 1.7 Permission is hereby granted, free of charge, to any person obtaining a 1.8 copy of this software and associated documentation files (the "Software"),
2.1 --- a/core.sql Mon Dec 23 20:22:32 2013 +0100 2.2 +++ b/core.sql Fri Jan 31 12:46:11 2014 +0100 2.3 @@ -7,7 +7,7 @@ 2.4 BEGIN; 2.5 2.6 CREATE VIEW "liquid_feedback_version" AS 2.7 - SELECT * FROM (VALUES ('2.2.6', 2, 2, 6)) 2.8 + SELECT * FROM (VALUES ('3.0.0', 3, 0, 0)) 2.9 AS "subquery"("string", "major", "minor", "revision"); 2.10 2.11
3.1 --- a/update/core-update.v2.2.5-v2.2.6.sql Mon Dec 23 20:22:32 2013 +0100 3.2 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 3.3 @@ -1,339 +0,0 @@ 3.4 -BEGIN; 3.5 - 3.6 -CREATE OR REPLACE VIEW "liquid_feedback_version" AS 3.7 - SELECT * FROM (VALUES ('2.2.6', 2, 2, 6)) 3.8 - AS "subquery"("string", "major", "minor", "revision"); 3.9 - 3.10 -CREATE TABLE "issue_order_in_admission_state" ( 3.11 - "id" INT8 PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 3.12 - "order_in_area" INT4, 3.13 - "order_in_unit" INT4 ); 3.14 - 3.15 -COMMENT ON TABLE "issue_order_in_admission_state" IS 'Ordering information for issues that are not stored in the "issue" table to avoid locking of multiple issues at once; Filled/updated by "lf_update_issue_order"'; 3.16 - 3.17 -COMMENT ON COLUMN "issue_order_in_admission_state"."id" IS 'References "issue" ("id") but has no referential integrity trigger associated, due to performance/locking issues'; 3.18 -COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_area" IS 'Order of issues in admission state within a single area; NULL values sort last'; 3.19 -COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_unit" IS 'Order of issues in admission state within all areas of a unit; NULL values sort last'; 3.20 - 3.21 -CREATE VIEW "issue_supporter_in_admission_state" AS 3.22 - SELECT DISTINCT 3.23 - "area"."unit_id", 3.24 - "issue"."area_id", 3.25 - "issue"."id" AS "issue_id", 3.26 - "supporter"."member_id", 3.27 - "direct_interest_snapshot"."weight" 3.28 - FROM "issue" 3.29 - JOIN "area" ON "area"."id" = "issue"."area_id" 3.30 - JOIN "supporter" ON "supporter"."issue_id" = "issue"."id" 3.31 - JOIN "direct_interest_snapshot" 3.32 - ON "direct_interest_snapshot"."issue_id" = "issue"."id" 3.33 - AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event" 3.34 - AND "direct_interest_snapshot"."member_id" = "supporter"."member_id" 3.35 - WHERE "issue"."state" = 'admission'::"issue_state"; 3.36 - 3.37 -COMMENT ON VIEW "issue_supporter_in_admission_state" IS 'Helper view for "lf_update_issue_order" to allow a (proportional) ordering of issues within an area'; 3.38 - 3.39 -COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking'; 3.40 -COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo'; 3.41 -COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (DEPRECATED, since schulze-ranking is unique per issue; use "better_than_status_quo"=FALSE)'; 3.42 -COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank"'; 3.43 - 3.44 -CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE) 3.45 - RETURNS VOID 3.46 - LANGUAGE 'plpgsql' VOLATILE AS $$ 3.47 - DECLARE 3.48 - "issue_row" "issue"%ROWTYPE; 3.49 - "policy_row" "policy"%ROWTYPE; 3.50 - "dimension_v" INTEGER; 3.51 - "vote_matrix" INT4[][]; -- absolute votes 3.52 - "matrix" INT8[][]; -- defeat strength / best paths 3.53 - "i" INTEGER; 3.54 - "j" INTEGER; 3.55 - "k" INTEGER; 3.56 - "battle_row" "battle"%ROWTYPE; 3.57 - "rank_ary" INT4[]; 3.58 - "rank_v" INT4; 3.59 - "initiative_id_v" "initiative"."id"%TYPE; 3.60 - BEGIN 3.61 - PERFORM "require_transaction_isolation"(); 3.62 - SELECT * INTO "issue_row" 3.63 - FROM "issue" WHERE "id" = "issue_id_p"; 3.64 - SELECT * INTO "policy_row" 3.65 - FROM "policy" WHERE "id" = "issue_row"."policy_id"; 3.66 - SELECT count(1) INTO "dimension_v" 3.67 - FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; 3.68 - -- Create "vote_matrix" with absolute number of votes in pairwise 3.69 - -- comparison: 3.70 - "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]); 3.71 - "i" := 1; 3.72 - "j" := 2; 3.73 - FOR "battle_row" IN 3.74 - SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" 3.75 - ORDER BY 3.76 - "winning_initiative_id" NULLS FIRST, 3.77 - "losing_initiative_id" NULLS FIRST 3.78 - LOOP 3.79 - "vote_matrix"["i"]["j"] := "battle_row"."count"; 3.80 - IF "j" = "dimension_v" THEN 3.81 - "i" := "i" + 1; 3.82 - "j" := 1; 3.83 - ELSE 3.84 - "j" := "j" + 1; 3.85 - IF "j" = "i" THEN 3.86 - "j" := "j" + 1; 3.87 - END IF; 3.88 - END IF; 3.89 - END LOOP; 3.90 - IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN 3.91 - RAISE EXCEPTION 'Wrong battle count (should not happen)'; 3.92 - END IF; 3.93 - -- Store defeat strengths in "matrix" using "defeat_strength" 3.94 - -- function: 3.95 - "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]); 3.96 - "i" := 1; 3.97 - LOOP 3.98 - "j" := 1; 3.99 - LOOP 3.100 - IF "i" != "j" THEN 3.101 - "matrix"["i"]["j"] := "defeat_strength"( 3.102 - "vote_matrix"["i"]["j"], 3.103 - "vote_matrix"["j"]["i"] 3.104 - ); 3.105 - END IF; 3.106 - EXIT WHEN "j" = "dimension_v"; 3.107 - "j" := "j" + 1; 3.108 - END LOOP; 3.109 - EXIT WHEN "i" = "dimension_v"; 3.110 - "i" := "i" + 1; 3.111 - END LOOP; 3.112 - -- Find best paths: 3.113 - "i" := 1; 3.114 - LOOP 3.115 - "j" := 1; 3.116 - LOOP 3.117 - IF "i" != "j" THEN 3.118 - "k" := 1; 3.119 - LOOP 3.120 - IF "i" != "k" AND "j" != "k" THEN 3.121 - IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN 3.122 - IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN 3.123 - "matrix"["j"]["k"] := "matrix"["j"]["i"]; 3.124 - END IF; 3.125 - ELSE 3.126 - IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN 3.127 - "matrix"["j"]["k"] := "matrix"["i"]["k"]; 3.128 - END IF; 3.129 - END IF; 3.130 - END IF; 3.131 - EXIT WHEN "k" = "dimension_v"; 3.132 - "k" := "k" + 1; 3.133 - END LOOP; 3.134 - END IF; 3.135 - EXIT WHEN "j" = "dimension_v"; 3.136 - "j" := "j" + 1; 3.137 - END LOOP; 3.138 - EXIT WHEN "i" = "dimension_v"; 3.139 - "i" := "i" + 1; 3.140 - END LOOP; 3.141 - -- Determine order of winners: 3.142 - "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]); 3.143 - "rank_v" := 1; 3.144 - LOOP 3.145 - "i" := 1; 3.146 - LOOP 3.147 - IF "rank_ary"["i"] ISNULL THEN 3.148 - "j" := 1; 3.149 - LOOP 3.150 - IF 3.151 - "i" != "j" AND 3.152 - "rank_ary"["j"] ISNULL AND 3.153 - ( "matrix"["j"]["i"] > "matrix"["i"]["j"] OR 3.154 - -- tie-breaking by "id" 3.155 - ( "matrix"["j"]["i"] = "matrix"["i"]["j"] AND 3.156 - "j" < "i" ) ) 3.157 - THEN 3.158 - -- someone else is better 3.159 - EXIT; 3.160 - END IF; 3.161 - "j" := "j" + 1; 3.162 - IF "j" = "dimension_v" + 1 THEN 3.163 - -- noone is better 3.164 - "rank_ary"["i"] := "rank_v"; 3.165 - EXIT; 3.166 - END IF; 3.167 - END LOOP; 3.168 - EXIT WHEN "j" = "dimension_v" + 1; 3.169 - END IF; 3.170 - "i" := "i" + 1; 3.171 - IF "i" > "dimension_v" THEN 3.172 - RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)'; 3.173 - END IF; 3.174 - END LOOP; 3.175 - EXIT WHEN "rank_v" = "dimension_v"; 3.176 - "rank_v" := "rank_v" + 1; 3.177 - END LOOP; 3.178 - -- write preliminary results: 3.179 - "i" := 2; -- omit status quo with "i" = 1 3.180 - FOR "initiative_id_v" IN 3.181 - SELECT "id" FROM "initiative" 3.182 - WHERE "issue_id" = "issue_id_p" AND "admitted" 3.183 - ORDER BY "id" 3.184 - LOOP 3.185 - UPDATE "initiative" SET 3.186 - "direct_majority" = 3.187 - CASE WHEN "policy_row"."direct_majority_strict" THEN 3.188 - "positive_votes" * "policy_row"."direct_majority_den" > 3.189 - "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 3.190 - ELSE 3.191 - "positive_votes" * "policy_row"."direct_majority_den" >= 3.192 - "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 3.193 - END 3.194 - AND "positive_votes" >= "policy_row"."direct_majority_positive" 3.195 - AND "issue_row"."voter_count"-"negative_votes" >= 3.196 - "policy_row"."direct_majority_non_negative", 3.197 - "indirect_majority" = 3.198 - CASE WHEN "policy_row"."indirect_majority_strict" THEN 3.199 - "positive_votes" * "policy_row"."indirect_majority_den" > 3.200 - "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 3.201 - ELSE 3.202 - "positive_votes" * "policy_row"."indirect_majority_den" >= 3.203 - "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 3.204 - END 3.205 - AND "positive_votes" >= "policy_row"."indirect_majority_positive" 3.206 - AND "issue_row"."voter_count"-"negative_votes" >= 3.207 - "policy_row"."indirect_majority_non_negative", 3.208 - "schulze_rank" = "rank_ary"["i"], 3.209 - "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1], 3.210 - "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"[1], 3.211 - "multistage_majority" = "rank_ary"["i"] >= "rank_ary"[1], 3.212 - "reverse_beat_path" = "matrix"[1]["i"] >= 0, 3.213 - "eligible" = FALSE, 3.214 - "winner" = FALSE, 3.215 - "rank" = NULL -- NOTE: in cases of manual reset of issue state 3.216 - WHERE "id" = "initiative_id_v"; 3.217 - "i" := "i" + 1; 3.218 - END LOOP; 3.219 - IF "i" != "dimension_v" + 1 THEN 3.220 - RAISE EXCEPTION 'Wrong winner count (should not happen)'; 3.221 - END IF; 3.222 - -- take indirect majorities into account: 3.223 - LOOP 3.224 - UPDATE "initiative" SET "indirect_majority" = TRUE 3.225 - FROM ( 3.226 - SELECT "new_initiative"."id" AS "initiative_id" 3.227 - FROM "initiative" "old_initiative" 3.228 - JOIN "initiative" "new_initiative" 3.229 - ON "new_initiative"."issue_id" = "issue_id_p" 3.230 - AND "new_initiative"."indirect_majority" = FALSE 3.231 - JOIN "battle" "battle_win" 3.232 - ON "battle_win"."issue_id" = "issue_id_p" 3.233 - AND "battle_win"."winning_initiative_id" = "new_initiative"."id" 3.234 - AND "battle_win"."losing_initiative_id" = "old_initiative"."id" 3.235 - JOIN "battle" "battle_lose" 3.236 - ON "battle_lose"."issue_id" = "issue_id_p" 3.237 - AND "battle_lose"."losing_initiative_id" = "new_initiative"."id" 3.238 - AND "battle_lose"."winning_initiative_id" = "old_initiative"."id" 3.239 - WHERE "old_initiative"."issue_id" = "issue_id_p" 3.240 - AND "old_initiative"."indirect_majority" = TRUE 3.241 - AND CASE WHEN "policy_row"."indirect_majority_strict" THEN 3.242 - "battle_win"."count" * "policy_row"."indirect_majority_den" > 3.243 - "policy_row"."indirect_majority_num" * 3.244 - ("battle_win"."count"+"battle_lose"."count") 3.245 - ELSE 3.246 - "battle_win"."count" * "policy_row"."indirect_majority_den" >= 3.247 - "policy_row"."indirect_majority_num" * 3.248 - ("battle_win"."count"+"battle_lose"."count") 3.249 - END 3.250 - AND "battle_win"."count" >= "policy_row"."indirect_majority_positive" 3.251 - AND "issue_row"."voter_count"-"battle_lose"."count" >= 3.252 - "policy_row"."indirect_majority_non_negative" 3.253 - ) AS "subquery" 3.254 - WHERE "id" = "subquery"."initiative_id"; 3.255 - EXIT WHEN NOT FOUND; 3.256 - END LOOP; 3.257 - -- set "multistage_majority" for remaining matching initiatives: 3.258 - UPDATE "initiative" SET "multistage_majority" = TRUE 3.259 - FROM ( 3.260 - SELECT "losing_initiative"."id" AS "initiative_id" 3.261 - FROM "initiative" "losing_initiative" 3.262 - JOIN "initiative" "winning_initiative" 3.263 - ON "winning_initiative"."issue_id" = "issue_id_p" 3.264 - AND "winning_initiative"."admitted" 3.265 - JOIN "battle" "battle_win" 3.266 - ON "battle_win"."issue_id" = "issue_id_p" 3.267 - AND "battle_win"."winning_initiative_id" = "winning_initiative"."id" 3.268 - AND "battle_win"."losing_initiative_id" = "losing_initiative"."id" 3.269 - JOIN "battle" "battle_lose" 3.270 - ON "battle_lose"."issue_id" = "issue_id_p" 3.271 - AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id" 3.272 - AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id" 3.273 - WHERE "losing_initiative"."issue_id" = "issue_id_p" 3.274 - AND "losing_initiative"."admitted" 3.275 - AND "winning_initiative"."schulze_rank" < 3.276 - "losing_initiative"."schulze_rank" 3.277 - AND "battle_win"."count" > "battle_lose"."count" 3.278 - AND ( 3.279 - "battle_win"."count" > "winning_initiative"."positive_votes" OR 3.280 - "battle_lose"."count" < "losing_initiative"."negative_votes" ) 3.281 - ) AS "subquery" 3.282 - WHERE "id" = "subquery"."initiative_id"; 3.283 - -- mark eligible initiatives: 3.284 - UPDATE "initiative" SET "eligible" = TRUE 3.285 - WHERE "issue_id" = "issue_id_p" 3.286 - AND "initiative"."direct_majority" 3.287 - AND "initiative"."indirect_majority" 3.288 - AND "initiative"."better_than_status_quo" 3.289 - AND ( 3.290 - "policy_row"."no_multistage_majority" = FALSE OR 3.291 - "initiative"."multistage_majority" = FALSE ) 3.292 - AND ( 3.293 - "policy_row"."no_reverse_beat_path" = FALSE OR 3.294 - "initiative"."reverse_beat_path" = FALSE ); 3.295 - -- mark final winner: 3.296 - UPDATE "initiative" SET "winner" = TRUE 3.297 - FROM ( 3.298 - SELECT "id" AS "initiative_id" 3.299 - FROM "initiative" 3.300 - WHERE "issue_id" = "issue_id_p" AND "eligible" 3.301 - ORDER BY 3.302 - "schulze_rank", 3.303 - "id" 3.304 - LIMIT 1 3.305 - ) AS "subquery" 3.306 - WHERE "id" = "subquery"."initiative_id"; 3.307 - -- write (final) ranks: 3.308 - "rank_v" := 1; 3.309 - FOR "initiative_id_v" IN 3.310 - SELECT "id" 3.311 - FROM "initiative" 3.312 - WHERE "issue_id" = "issue_id_p" AND "admitted" 3.313 - ORDER BY 3.314 - "winner" DESC, 3.315 - "eligible" DESC, 3.316 - "schulze_rank", 3.317 - "id" 3.318 - LOOP 3.319 - UPDATE "initiative" SET "rank" = "rank_v" 3.320 - WHERE "id" = "initiative_id_v"; 3.321 - "rank_v" := "rank_v" + 1; 3.322 - END LOOP; 3.323 - -- set schulze rank of status quo and mark issue as finished: 3.324 - UPDATE "issue" SET 3.325 - "status_quo_schulze_rank" = "rank_ary"[1], 3.326 - "state" = 3.327 - CASE WHEN EXISTS ( 3.328 - SELECT NULL FROM "initiative" 3.329 - WHERE "issue_id" = "issue_id_p" AND "winner" 3.330 - ) THEN 3.331 - 'finished_with_winner'::"issue_state" 3.332 - ELSE 3.333 - 'finished_without_winner'::"issue_state" 3.334 - END, 3.335 - "closed" = "phase_finished", 3.336 - "phase_finished" = NULL 3.337 - WHERE "id" = "issue_id_p"; 3.338 - RETURN; 3.339 - END; 3.340 - $$; 3.341 - 3.342 -COMMIT;
4.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 4.2 +++ b/update/core-update.v2.2.5-v3.0.0.sql Fri Jan 31 12:46:11 2014 +0100 4.3 @@ -0,0 +1,339 @@ 4.4 +BEGIN; 4.5 + 4.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 4.7 + SELECT * FROM (VALUES ('3.0.0', 3, 0, 0)) 4.8 + AS "subquery"("string", "major", "minor", "revision"); 4.9 + 4.10 +CREATE TABLE "issue_order_in_admission_state" ( 4.11 + "id" INT8 PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE, 4.12 + "order_in_area" INT4, 4.13 + "order_in_unit" INT4 ); 4.14 + 4.15 +COMMENT ON TABLE "issue_order_in_admission_state" IS 'Ordering information for issues that are not stored in the "issue" table to avoid locking of multiple issues at once; Filled/updated by "lf_update_issue_order"'; 4.16 + 4.17 +COMMENT ON COLUMN "issue_order_in_admission_state"."id" IS 'References "issue" ("id") but has no referential integrity trigger associated, due to performance/locking issues'; 4.18 +COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_area" IS 'Order of issues in admission state within a single area; NULL values sort last'; 4.19 +COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_unit" IS 'Order of issues in admission state within all areas of a unit; NULL values sort last'; 4.20 + 4.21 +CREATE VIEW "issue_supporter_in_admission_state" AS 4.22 + SELECT DISTINCT 4.23 + "area"."unit_id", 4.24 + "issue"."area_id", 4.25 + "issue"."id" AS "issue_id", 4.26 + "supporter"."member_id", 4.27 + "direct_interest_snapshot"."weight" 4.28 + FROM "issue" 4.29 + JOIN "area" ON "area"."id" = "issue"."area_id" 4.30 + JOIN "supporter" ON "supporter"."issue_id" = "issue"."id" 4.31 + JOIN "direct_interest_snapshot" 4.32 + ON "direct_interest_snapshot"."issue_id" = "issue"."id" 4.33 + AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event" 4.34 + AND "direct_interest_snapshot"."member_id" = "supporter"."member_id" 4.35 + WHERE "issue"."state" = 'admission'::"issue_state"; 4.36 + 4.37 +COMMENT ON VIEW "issue_supporter_in_admission_state" IS 'Helper view for "lf_update_issue_order" to allow a (proportional) ordering of issues within an area'; 4.38 + 4.39 +COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking'; 4.40 +COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo'; 4.41 +COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (DEPRECATED, since schulze-ranking is unique per issue; use "better_than_status_quo"=FALSE)'; 4.42 +COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank"'; 4.43 + 4.44 +CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE) 4.45 + RETURNS VOID 4.46 + LANGUAGE 'plpgsql' VOLATILE AS $$ 4.47 + DECLARE 4.48 + "issue_row" "issue"%ROWTYPE; 4.49 + "policy_row" "policy"%ROWTYPE; 4.50 + "dimension_v" INTEGER; 4.51 + "vote_matrix" INT4[][]; -- absolute votes 4.52 + "matrix" INT8[][]; -- defeat strength / best paths 4.53 + "i" INTEGER; 4.54 + "j" INTEGER; 4.55 + "k" INTEGER; 4.56 + "battle_row" "battle"%ROWTYPE; 4.57 + "rank_ary" INT4[]; 4.58 + "rank_v" INT4; 4.59 + "initiative_id_v" "initiative"."id"%TYPE; 4.60 + BEGIN 4.61 + PERFORM "require_transaction_isolation"(); 4.62 + SELECT * INTO "issue_row" 4.63 + FROM "issue" WHERE "id" = "issue_id_p"; 4.64 + SELECT * INTO "policy_row" 4.65 + FROM "policy" WHERE "id" = "issue_row"."policy_id"; 4.66 + SELECT count(1) INTO "dimension_v" 4.67 + FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; 4.68 + -- Create "vote_matrix" with absolute number of votes in pairwise 4.69 + -- comparison: 4.70 + "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]); 4.71 + "i" := 1; 4.72 + "j" := 2; 4.73 + FOR "battle_row" IN 4.74 + SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" 4.75 + ORDER BY 4.76 + "winning_initiative_id" NULLS FIRST, 4.77 + "losing_initiative_id" NULLS FIRST 4.78 + LOOP 4.79 + "vote_matrix"["i"]["j"] := "battle_row"."count"; 4.80 + IF "j" = "dimension_v" THEN 4.81 + "i" := "i" + 1; 4.82 + "j" := 1; 4.83 + ELSE 4.84 + "j" := "j" + 1; 4.85 + IF "j" = "i" THEN 4.86 + "j" := "j" + 1; 4.87 + END IF; 4.88 + END IF; 4.89 + END LOOP; 4.90 + IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN 4.91 + RAISE EXCEPTION 'Wrong battle count (should not happen)'; 4.92 + END IF; 4.93 + -- Store defeat strengths in "matrix" using "defeat_strength" 4.94 + -- function: 4.95 + "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]); 4.96 + "i" := 1; 4.97 + LOOP 4.98 + "j" := 1; 4.99 + LOOP 4.100 + IF "i" != "j" THEN 4.101 + "matrix"["i"]["j"] := "defeat_strength"( 4.102 + "vote_matrix"["i"]["j"], 4.103 + "vote_matrix"["j"]["i"] 4.104 + ); 4.105 + END IF; 4.106 + EXIT WHEN "j" = "dimension_v"; 4.107 + "j" := "j" + 1; 4.108 + END LOOP; 4.109 + EXIT WHEN "i" = "dimension_v"; 4.110 + "i" := "i" + 1; 4.111 + END LOOP; 4.112 + -- Find best paths: 4.113 + "i" := 1; 4.114 + LOOP 4.115 + "j" := 1; 4.116 + LOOP 4.117 + IF "i" != "j" THEN 4.118 + "k" := 1; 4.119 + LOOP 4.120 + IF "i" != "k" AND "j" != "k" THEN 4.121 + IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN 4.122 + IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN 4.123 + "matrix"["j"]["k"] := "matrix"["j"]["i"]; 4.124 + END IF; 4.125 + ELSE 4.126 + IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN 4.127 + "matrix"["j"]["k"] := "matrix"["i"]["k"]; 4.128 + END IF; 4.129 + END IF; 4.130 + END IF; 4.131 + EXIT WHEN "k" = "dimension_v"; 4.132 + "k" := "k" + 1; 4.133 + END LOOP; 4.134 + END IF; 4.135 + EXIT WHEN "j" = "dimension_v"; 4.136 + "j" := "j" + 1; 4.137 + END LOOP; 4.138 + EXIT WHEN "i" = "dimension_v"; 4.139 + "i" := "i" + 1; 4.140 + END LOOP; 4.141 + -- Determine order of winners: 4.142 + "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]); 4.143 + "rank_v" := 1; 4.144 + LOOP 4.145 + "i" := 1; 4.146 + LOOP 4.147 + IF "rank_ary"["i"] ISNULL THEN 4.148 + "j" := 1; 4.149 + LOOP 4.150 + IF 4.151 + "i" != "j" AND 4.152 + "rank_ary"["j"] ISNULL AND 4.153 + ( "matrix"["j"]["i"] > "matrix"["i"]["j"] OR 4.154 + -- tie-breaking by "id" 4.155 + ( "matrix"["j"]["i"] = "matrix"["i"]["j"] AND 4.156 + "j" < "i" ) ) 4.157 + THEN 4.158 + -- someone else is better 4.159 + EXIT; 4.160 + END IF; 4.161 + "j" := "j" + 1; 4.162 + IF "j" = "dimension_v" + 1 THEN 4.163 + -- noone is better 4.164 + "rank_ary"["i"] := "rank_v"; 4.165 + EXIT; 4.166 + END IF; 4.167 + END LOOP; 4.168 + EXIT WHEN "j" = "dimension_v" + 1; 4.169 + END IF; 4.170 + "i" := "i" + 1; 4.171 + IF "i" > "dimension_v" THEN 4.172 + RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)'; 4.173 + END IF; 4.174 + END LOOP; 4.175 + EXIT WHEN "rank_v" = "dimension_v"; 4.176 + "rank_v" := "rank_v" + 1; 4.177 + END LOOP; 4.178 + -- write preliminary results: 4.179 + "i" := 2; -- omit status quo with "i" = 1 4.180 + FOR "initiative_id_v" IN 4.181 + SELECT "id" FROM "initiative" 4.182 + WHERE "issue_id" = "issue_id_p" AND "admitted" 4.183 + ORDER BY "id" 4.184 + LOOP 4.185 + UPDATE "initiative" SET 4.186 + "direct_majority" = 4.187 + CASE WHEN "policy_row"."direct_majority_strict" THEN 4.188 + "positive_votes" * "policy_row"."direct_majority_den" > 4.189 + "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 4.190 + ELSE 4.191 + "positive_votes" * "policy_row"."direct_majority_den" >= 4.192 + "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 4.193 + END 4.194 + AND "positive_votes" >= "policy_row"."direct_majority_positive" 4.195 + AND "issue_row"."voter_count"-"negative_votes" >= 4.196 + "policy_row"."direct_majority_non_negative", 4.197 + "indirect_majority" = 4.198 + CASE WHEN "policy_row"."indirect_majority_strict" THEN 4.199 + "positive_votes" * "policy_row"."indirect_majority_den" > 4.200 + "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 4.201 + ELSE 4.202 + "positive_votes" * "policy_row"."indirect_majority_den" >= 4.203 + "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 4.204 + END 4.205 + AND "positive_votes" >= "policy_row"."indirect_majority_positive" 4.206 + AND "issue_row"."voter_count"-"negative_votes" >= 4.207 + "policy_row"."indirect_majority_non_negative", 4.208 + "schulze_rank" = "rank_ary"["i"], 4.209 + "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1], 4.210 + "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"[1], 4.211 + "multistage_majority" = "rank_ary"["i"] >= "rank_ary"[1], 4.212 + "reverse_beat_path" = "matrix"[1]["i"] >= 0, 4.213 + "eligible" = FALSE, 4.214 + "winner" = FALSE, 4.215 + "rank" = NULL -- NOTE: in cases of manual reset of issue state 4.216 + WHERE "id" = "initiative_id_v"; 4.217 + "i" := "i" + 1; 4.218 + END LOOP; 4.219 + IF "i" != "dimension_v" + 1 THEN 4.220 + RAISE EXCEPTION 'Wrong winner count (should not happen)'; 4.221 + END IF; 4.222 + -- take indirect majorities into account: 4.223 + LOOP 4.224 + UPDATE "initiative" SET "indirect_majority" = TRUE 4.225 + FROM ( 4.226 + SELECT "new_initiative"."id" AS "initiative_id" 4.227 + FROM "initiative" "old_initiative" 4.228 + JOIN "initiative" "new_initiative" 4.229 + ON "new_initiative"."issue_id" = "issue_id_p" 4.230 + AND "new_initiative"."indirect_majority" = FALSE 4.231 + JOIN "battle" "battle_win" 4.232 + ON "battle_win"."issue_id" = "issue_id_p" 4.233 + AND "battle_win"."winning_initiative_id" = "new_initiative"."id" 4.234 + AND "battle_win"."losing_initiative_id" = "old_initiative"."id" 4.235 + JOIN "battle" "battle_lose" 4.236 + ON "battle_lose"."issue_id" = "issue_id_p" 4.237 + AND "battle_lose"."losing_initiative_id" = "new_initiative"."id" 4.238 + AND "battle_lose"."winning_initiative_id" = "old_initiative"."id" 4.239 + WHERE "old_initiative"."issue_id" = "issue_id_p" 4.240 + AND "old_initiative"."indirect_majority" = TRUE 4.241 + AND CASE WHEN "policy_row"."indirect_majority_strict" THEN 4.242 + "battle_win"."count" * "policy_row"."indirect_majority_den" > 4.243 + "policy_row"."indirect_majority_num" * 4.244 + ("battle_win"."count"+"battle_lose"."count") 4.245 + ELSE 4.246 + "battle_win"."count" * "policy_row"."indirect_majority_den" >= 4.247 + "policy_row"."indirect_majority_num" * 4.248 + ("battle_win"."count"+"battle_lose"."count") 4.249 + END 4.250 + AND "battle_win"."count" >= "policy_row"."indirect_majority_positive" 4.251 + AND "issue_row"."voter_count"-"battle_lose"."count" >= 4.252 + "policy_row"."indirect_majority_non_negative" 4.253 + ) AS "subquery" 4.254 + WHERE "id" = "subquery"."initiative_id"; 4.255 + EXIT WHEN NOT FOUND; 4.256 + END LOOP; 4.257 + -- set "multistage_majority" for remaining matching initiatives: 4.258 + UPDATE "initiative" SET "multistage_majority" = TRUE 4.259 + FROM ( 4.260 + SELECT "losing_initiative"."id" AS "initiative_id" 4.261 + FROM "initiative" "losing_initiative" 4.262 + JOIN "initiative" "winning_initiative" 4.263 + ON "winning_initiative"."issue_id" = "issue_id_p" 4.264 + AND "winning_initiative"."admitted" 4.265 + JOIN "battle" "battle_win" 4.266 + ON "battle_win"."issue_id" = "issue_id_p" 4.267 + AND "battle_win"."winning_initiative_id" = "winning_initiative"."id" 4.268 + AND "battle_win"."losing_initiative_id" = "losing_initiative"."id" 4.269 + JOIN "battle" "battle_lose" 4.270 + ON "battle_lose"."issue_id" = "issue_id_p" 4.271 + AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id" 4.272 + AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id" 4.273 + WHERE "losing_initiative"."issue_id" = "issue_id_p" 4.274 + AND "losing_initiative"."admitted" 4.275 + AND "winning_initiative"."schulze_rank" < 4.276 + "losing_initiative"."schulze_rank" 4.277 + AND "battle_win"."count" > "battle_lose"."count" 4.278 + AND ( 4.279 + "battle_win"."count" > "winning_initiative"."positive_votes" OR 4.280 + "battle_lose"."count" < "losing_initiative"."negative_votes" ) 4.281 + ) AS "subquery" 4.282 + WHERE "id" = "subquery"."initiative_id"; 4.283 + -- mark eligible initiatives: 4.284 + UPDATE "initiative" SET "eligible" = TRUE 4.285 + WHERE "issue_id" = "issue_id_p" 4.286 + AND "initiative"."direct_majority" 4.287 + AND "initiative"."indirect_majority" 4.288 + AND "initiative"."better_than_status_quo" 4.289 + AND ( 4.290 + "policy_row"."no_multistage_majority" = FALSE OR 4.291 + "initiative"."multistage_majority" = FALSE ) 4.292 + AND ( 4.293 + "policy_row"."no_reverse_beat_path" = FALSE OR 4.294 + "initiative"."reverse_beat_path" = FALSE ); 4.295 + -- mark final winner: 4.296 + UPDATE "initiative" SET "winner" = TRUE 4.297 + FROM ( 4.298 + SELECT "id" AS "initiative_id" 4.299 + FROM "initiative" 4.300 + WHERE "issue_id" = "issue_id_p" AND "eligible" 4.301 + ORDER BY 4.302 + "schulze_rank", 4.303 + "id" 4.304 + LIMIT 1 4.305 + ) AS "subquery" 4.306 + WHERE "id" = "subquery"."initiative_id"; 4.307 + -- write (final) ranks: 4.308 + "rank_v" := 1; 4.309 + FOR "initiative_id_v" IN 4.310 + SELECT "id" 4.311 + FROM "initiative" 4.312 + WHERE "issue_id" = "issue_id_p" AND "admitted" 4.313 + ORDER BY 4.314 + "winner" DESC, 4.315 + "eligible" DESC, 4.316 + "schulze_rank", 4.317 + "id" 4.318 + LOOP 4.319 + UPDATE "initiative" SET "rank" = "rank_v" 4.320 + WHERE "id" = "initiative_id_v"; 4.321 + "rank_v" := "rank_v" + 1; 4.322 + END LOOP; 4.323 + -- set schulze rank of status quo and mark issue as finished: 4.324 + UPDATE "issue" SET 4.325 + "status_quo_schulze_rank" = "rank_ary"[1], 4.326 + "state" = 4.327 + CASE WHEN EXISTS ( 4.328 + SELECT NULL FROM "initiative" 4.329 + WHERE "issue_id" = "issue_id_p" AND "winner" 4.330 + ) THEN 4.331 + 'finished_with_winner'::"issue_state" 4.332 + ELSE 4.333 + 'finished_without_winner'::"issue_state" 4.334 + END, 4.335 + "closed" = "phase_finished", 4.336 + "phase_finished" = NULL 4.337 + WHERE "id" = "issue_id_p"; 4.338 + RETURN; 4.339 + END; 4.340 + $$; 4.341 + 4.342 +COMMIT;