liquid_feedback_core
changeset 218:2571bb3bdf56 v2.0.3
Update script to v2.0.3
author | jbe |
---|---|
date | Wed Feb 22 21:30:41 2012 +0100 (2012-02-22) |
parents | 9474e2038590 |
children | cd0a8fd2059d |
files | core.sql update/core-update.v2.0.2-v2.0.3.sql |
line diff
1.1 --- a/core.sql Wed Feb 22 20:50:59 2012 +0100 1.2 +++ b/core.sql Wed Feb 22 21:30:41 2012 +0100 1.3 @@ -7,7 +7,7 @@ 1.4 BEGIN; 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('2.0.2', 2, 0, 2)) 1.8 + SELECT * FROM (VALUES ('2.0.3', 2, 0, 3)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 2.2 +++ b/update/core-update.v2.0.2-v2.0.3.sql Wed Feb 22 21:30:41 2012 +0100 2.3 @@ -0,0 +1,321 @@ 2.4 +BEGIN; 2.5 + 2.6 +-- update version number: 2.7 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 2.8 + SELECT * FROM (VALUES ('2.0.3', 2, 0, 3)) 2.9 + AS "subquery"("string", "major", "minor", "revision"); 2.10 + 2.11 +-- update function "calculate_ranks"("issue"."id") 2.12 +-- regarding setting "eligible"=FALSE for non-eligible admitted initiatives 2.13 +-- and regarding "rank" ordering: 2.14 +CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE) 2.15 + RETURNS VOID 2.16 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.17 + DECLARE 2.18 + "issue_row" "issue"%ROWTYPE; 2.19 + "policy_row" "policy"%ROWTYPE; 2.20 + "dimension_v" INTEGER; 2.21 + "vote_matrix" INT4[][]; -- absolute votes 2.22 + "matrix" INT8[][]; -- defeat strength / best paths 2.23 + "i" INTEGER; 2.24 + "j" INTEGER; 2.25 + "k" INTEGER; 2.26 + "battle_row" "battle"%ROWTYPE; 2.27 + "rank_ary" INT4[]; 2.28 + "rank_v" INT4; 2.29 + "done_v" INTEGER; 2.30 + "winners_ary" INTEGER[]; 2.31 + "initiative_id_v" "initiative"."id"%TYPE; 2.32 + BEGIN 2.33 + SELECT * INTO "issue_row" 2.34 + FROM "issue" WHERE "id" = "issue_id_p" 2.35 + FOR UPDATE; 2.36 + SELECT * INTO "policy_row" 2.37 + FROM "policy" WHERE "id" = "issue_row"."policy_id"; 2.38 + SELECT count(1) INTO "dimension_v" 2.39 + FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; 2.40 + -- Create "vote_matrix" with absolute number of votes in pairwise 2.41 + -- comparison: 2.42 + "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]); 2.43 + "i" := 1; 2.44 + "j" := 2; 2.45 + FOR "battle_row" IN 2.46 + SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" 2.47 + ORDER BY 2.48 + "winning_initiative_id" NULLS LAST, 2.49 + "losing_initiative_id" NULLS LAST 2.50 + LOOP 2.51 + "vote_matrix"["i"]["j"] := "battle_row"."count"; 2.52 + IF "j" = "dimension_v" THEN 2.53 + "i" := "i" + 1; 2.54 + "j" := 1; 2.55 + ELSE 2.56 + "j" := "j" + 1; 2.57 + IF "j" = "i" THEN 2.58 + "j" := "j" + 1; 2.59 + END IF; 2.60 + END IF; 2.61 + END LOOP; 2.62 + IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN 2.63 + RAISE EXCEPTION 'Wrong battle count (should not happen)'; 2.64 + END IF; 2.65 + -- Store defeat strengths in "matrix" using "defeat_strength" 2.66 + -- function: 2.67 + "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]); 2.68 + "i" := 1; 2.69 + LOOP 2.70 + "j" := 1; 2.71 + LOOP 2.72 + IF "i" != "j" THEN 2.73 + "matrix"["i"]["j"] := "defeat_strength"( 2.74 + "vote_matrix"["i"]["j"], 2.75 + "vote_matrix"["j"]["i"] 2.76 + ); 2.77 + END IF; 2.78 + EXIT WHEN "j" = "dimension_v"; 2.79 + "j" := "j" + 1; 2.80 + END LOOP; 2.81 + EXIT WHEN "i" = "dimension_v"; 2.82 + "i" := "i" + 1; 2.83 + END LOOP; 2.84 + -- Find best paths: 2.85 + "i" := 1; 2.86 + LOOP 2.87 + "j" := 1; 2.88 + LOOP 2.89 + IF "i" != "j" THEN 2.90 + "k" := 1; 2.91 + LOOP 2.92 + IF "i" != "k" AND "j" != "k" THEN 2.93 + IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN 2.94 + IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN 2.95 + "matrix"["j"]["k"] := "matrix"["j"]["i"]; 2.96 + END IF; 2.97 + ELSE 2.98 + IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN 2.99 + "matrix"["j"]["k"] := "matrix"["i"]["k"]; 2.100 + END IF; 2.101 + END IF; 2.102 + END IF; 2.103 + EXIT WHEN "k" = "dimension_v"; 2.104 + "k" := "k" + 1; 2.105 + END LOOP; 2.106 + END IF; 2.107 + EXIT WHEN "j" = "dimension_v"; 2.108 + "j" := "j" + 1; 2.109 + END LOOP; 2.110 + EXIT WHEN "i" = "dimension_v"; 2.111 + "i" := "i" + 1; 2.112 + END LOOP; 2.113 + -- Determine order of winners: 2.114 + "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]); 2.115 + "rank_v" := 1; 2.116 + "done_v" := 0; 2.117 + LOOP 2.118 + "winners_ary" := '{}'; 2.119 + "i" := 1; 2.120 + LOOP 2.121 + IF "rank_ary"["i"] ISNULL THEN 2.122 + "j" := 1; 2.123 + LOOP 2.124 + IF 2.125 + "i" != "j" AND 2.126 + "rank_ary"["j"] ISNULL AND 2.127 + "matrix"["j"]["i"] > "matrix"["i"]["j"] 2.128 + THEN 2.129 + -- someone else is better 2.130 + EXIT; 2.131 + END IF; 2.132 + IF "j" = "dimension_v" THEN 2.133 + -- noone is better 2.134 + "winners_ary" := "winners_ary" || "i"; 2.135 + EXIT; 2.136 + END IF; 2.137 + "j" := "j" + 1; 2.138 + END LOOP; 2.139 + END IF; 2.140 + EXIT WHEN "i" = "dimension_v"; 2.141 + "i" := "i" + 1; 2.142 + END LOOP; 2.143 + "i" := 1; 2.144 + LOOP 2.145 + "rank_ary"["winners_ary"["i"]] := "rank_v"; 2.146 + "done_v" := "done_v" + 1; 2.147 + EXIT WHEN "i" = array_upper("winners_ary", 1); 2.148 + "i" := "i" + 1; 2.149 + END LOOP; 2.150 + EXIT WHEN "done_v" = "dimension_v"; 2.151 + "rank_v" := "rank_v" + 1; 2.152 + END LOOP; 2.153 + -- write preliminary results: 2.154 + "i" := 1; 2.155 + FOR "initiative_id_v" IN 2.156 + SELECT "id" FROM "initiative" 2.157 + WHERE "issue_id" = "issue_id_p" AND "admitted" 2.158 + ORDER BY "id" 2.159 + LOOP 2.160 + UPDATE "initiative" SET 2.161 + "direct_majority" = 2.162 + CASE WHEN "policy_row"."direct_majority_strict" THEN 2.163 + "positive_votes" * "policy_row"."direct_majority_den" > 2.164 + "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 2.165 + ELSE 2.166 + "positive_votes" * "policy_row"."direct_majority_den" >= 2.167 + "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 2.168 + END 2.169 + AND "positive_votes" >= "policy_row"."direct_majority_positive" 2.170 + AND "issue_row"."voter_count"-"negative_votes" >= 2.171 + "policy_row"."direct_majority_non_negative", 2.172 + "indirect_majority" = 2.173 + CASE WHEN "policy_row"."indirect_majority_strict" THEN 2.174 + "positive_votes" * "policy_row"."indirect_majority_den" > 2.175 + "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 2.176 + ELSE 2.177 + "positive_votes" * "policy_row"."indirect_majority_den" >= 2.178 + "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 2.179 + END 2.180 + AND "positive_votes" >= "policy_row"."indirect_majority_positive" 2.181 + AND "issue_row"."voter_count"-"negative_votes" >= 2.182 + "policy_row"."indirect_majority_non_negative", 2.183 + "schulze_rank" = "rank_ary"["i"], 2.184 + "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"], 2.185 + "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"], 2.186 + "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"], 2.187 + "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0, 2.188 + "eligible" = FALSE, 2.189 + "winner" = FALSE 2.190 + WHERE "id" = "initiative_id_v"; 2.191 + "i" := "i" + 1; 2.192 + END LOOP; 2.193 + IF "i" != "dimension_v" THEN 2.194 + RAISE EXCEPTION 'Wrong winner count (should not happen)'; 2.195 + END IF; 2.196 + -- take indirect majorities into account: 2.197 + LOOP 2.198 + UPDATE "initiative" SET "indirect_majority" = TRUE 2.199 + FROM ( 2.200 + SELECT "new_initiative"."id" AS "initiative_id" 2.201 + FROM "initiative" "old_initiative" 2.202 + JOIN "initiative" "new_initiative" 2.203 + ON "new_initiative"."issue_id" = "issue_id_p" 2.204 + AND "new_initiative"."indirect_majority" = FALSE 2.205 + JOIN "battle" "battle_win" 2.206 + ON "battle_win"."issue_id" = "issue_id_p" 2.207 + AND "battle_win"."winning_initiative_id" = "new_initiative"."id" 2.208 + AND "battle_win"."losing_initiative_id" = "old_initiative"."id" 2.209 + JOIN "battle" "battle_lose" 2.210 + ON "battle_lose"."issue_id" = "issue_id_p" 2.211 + AND "battle_lose"."losing_initiative_id" = "new_initiative"."id" 2.212 + AND "battle_lose"."winning_initiative_id" = "old_initiative"."id" 2.213 + WHERE "old_initiative"."issue_id" = "issue_id_p" 2.214 + AND "old_initiative"."indirect_majority" = TRUE 2.215 + AND CASE WHEN "policy_row"."indirect_majority_strict" THEN 2.216 + "battle_win"."count" * "policy_row"."indirect_majority_den" > 2.217 + "policy_row"."indirect_majority_num" * 2.218 + ("battle_win"."count"+"battle_lose"."count") 2.219 + ELSE 2.220 + "battle_win"."count" * "policy_row"."indirect_majority_den" >= 2.221 + "policy_row"."indirect_majority_num" * 2.222 + ("battle_win"."count"+"battle_lose"."count") 2.223 + END 2.224 + AND "battle_win"."count" >= "policy_row"."indirect_majority_positive" 2.225 + AND "issue_row"."voter_count"-"battle_lose"."count" >= 2.226 + "policy_row"."indirect_majority_non_negative" 2.227 + ) AS "subquery" 2.228 + WHERE "id" = "subquery"."initiative_id"; 2.229 + EXIT WHEN NOT FOUND; 2.230 + END LOOP; 2.231 + -- set "multistage_majority" for remaining matching initiatives: 2.232 + UPDATE "initiative" SET "multistage_majority" = TRUE 2.233 + FROM ( 2.234 + SELECT "losing_initiative"."id" AS "initiative_id" 2.235 + FROM "initiative" "losing_initiative" 2.236 + JOIN "initiative" "winning_initiative" 2.237 + ON "winning_initiative"."issue_id" = "issue_id_p" 2.238 + AND "winning_initiative"."admitted" 2.239 + JOIN "battle" "battle_win" 2.240 + ON "battle_win"."issue_id" = "issue_id_p" 2.241 + AND "battle_win"."winning_initiative_id" = "winning_initiative"."id" 2.242 + AND "battle_win"."losing_initiative_id" = "losing_initiative"."id" 2.243 + JOIN "battle" "battle_lose" 2.244 + ON "battle_lose"."issue_id" = "issue_id_p" 2.245 + AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id" 2.246 + AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id" 2.247 + WHERE "losing_initiative"."issue_id" = "issue_id_p" 2.248 + AND "losing_initiative"."admitted" 2.249 + AND "winning_initiative"."schulze_rank" < 2.250 + "losing_initiative"."schulze_rank" 2.251 + AND "battle_win"."count" > "battle_lose"."count" 2.252 + AND ( 2.253 + "battle_win"."count" > "winning_initiative"."positive_votes" OR 2.254 + "battle_lose"."count" < "losing_initiative"."negative_votes" ) 2.255 + ) AS "subquery" 2.256 + WHERE "id" = "subquery"."initiative_id"; 2.257 + -- mark eligible initiatives: 2.258 + UPDATE "initiative" SET "eligible" = TRUE 2.259 + WHERE "issue_id" = "issue_id_p" 2.260 + AND "initiative"."direct_majority" 2.261 + AND "initiative"."indirect_majority" 2.262 + AND "initiative"."better_than_status_quo" 2.263 + AND ( 2.264 + "policy_row"."no_multistage_majority" = FALSE OR 2.265 + "initiative"."multistage_majority" = FALSE ) 2.266 + AND ( 2.267 + "policy_row"."no_reverse_beat_path" = FALSE OR 2.268 + "initiative"."reverse_beat_path" = FALSE ); 2.269 + -- mark final winner: 2.270 + UPDATE "initiative" SET "winner" = TRUE 2.271 + FROM ( 2.272 + SELECT "id" AS "initiative_id" 2.273 + FROM "initiative" 2.274 + WHERE "issue_id" = "issue_id_p" AND "eligible" 2.275 + ORDER BY 2.276 + "schulze_rank", 2.277 + "vote_ratio"("positive_votes", "negative_votes"), 2.278 + "id" 2.279 + LIMIT 1 2.280 + ) AS "subquery" 2.281 + WHERE "id" = "subquery"."initiative_id"; 2.282 + -- write (final) ranks: 2.283 + "rank_v" := 1; 2.284 + FOR "initiative_id_v" IN 2.285 + SELECT "id" 2.286 + FROM "initiative" 2.287 + WHERE "issue_id" = "issue_id_p" AND "admitted" 2.288 + ORDER BY 2.289 + "winner" DESC, 2.290 + "eligible" DESC, 2.291 + "schulze_rank", 2.292 + "vote_ratio"("positive_votes", "negative_votes"), 2.293 + "id" 2.294 + LOOP 2.295 + UPDATE "initiative" SET "rank" = "rank_v" 2.296 + WHERE "id" = "initiative_id_v"; 2.297 + "rank_v" := "rank_v" + 1; 2.298 + END LOOP; 2.299 + -- set schulze rank of status quo and mark issue as finished: 2.300 + UPDATE "issue" SET 2.301 + "status_quo_schulze_rank" = "rank_ary"["dimension_v"], 2.302 + "state" = 2.303 + CASE WHEN EXISTS ( 2.304 + SELECT NULL FROM "initiative" 2.305 + WHERE "issue_id" = "issue_id_p" AND "winner" 2.306 + ) THEN 2.307 + 'finished_with_winner'::"issue_state" 2.308 + ELSE 2.309 + 'finished_without_winner'::"issue_state" 2.310 + END, 2.311 + "ranks_available" = TRUE 2.312 + WHERE "id" = "issue_id_p"; 2.313 + RETURN; 2.314 + END; 2.315 + $$; 2.316 + 2.317 +-- set "eligible" to FALSE for all non-eligible 2.318 +-- but admitted initiatives of closed issues: 2.319 +UPDATE "initiative" SET "eligible" = FALSE WHERE "eligible" ISNULL AND "rank" NOTNULL; 2.320 + 2.321 +-- NOTE: "rank" ordering of initiatives of closed issues are 2.322 +-- not changed, as v2.0.2 should not be in productive use yet. 2.323 + 2.324 +COMMIT;