liquid_feedback_core
annotate update/core-update.v2.0.11-v2.0.12.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 | 5c98265b39a0 |
children |
rev | line source |
---|---|
jbe@305 | 1 BEGIN; |
jbe@305 | 2 |
jbe@305 | 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS |
jbe@305 | 4 SELECT * FROM (VALUES ('2.0.12', 2, 0, 12)) |
jbe@305 | 5 AS "subquery"("string", "major", "minor", "revision"); |
jbe@305 | 6 |
jbe@305 | 7 CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE) |
jbe@305 | 8 RETURNS VOID |
jbe@305 | 9 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@305 | 10 DECLARE |
jbe@305 | 11 "issue_row" "issue"%ROWTYPE; |
jbe@305 | 12 "policy_row" "policy"%ROWTYPE; |
jbe@305 | 13 "dimension_v" INTEGER; |
jbe@305 | 14 "vote_matrix" INT4[][]; -- absolute votes |
jbe@305 | 15 "matrix" INT8[][]; -- defeat strength / best paths |
jbe@305 | 16 "i" INTEGER; |
jbe@305 | 17 "j" INTEGER; |
jbe@305 | 18 "k" INTEGER; |
jbe@305 | 19 "battle_row" "battle"%ROWTYPE; |
jbe@305 | 20 "rank_ary" INT4[]; |
jbe@305 | 21 "rank_v" INT4; |
jbe@305 | 22 "done_v" INTEGER; |
jbe@305 | 23 "winners_ary" INTEGER[]; |
jbe@305 | 24 "initiative_id_v" "initiative"."id"%TYPE; |
jbe@305 | 25 BEGIN |
jbe@305 | 26 SELECT * INTO "issue_row" |
jbe@305 | 27 FROM "issue" WHERE "id" = "issue_id_p" |
jbe@305 | 28 FOR UPDATE; |
jbe@305 | 29 SELECT * INTO "policy_row" |
jbe@305 | 30 FROM "policy" WHERE "id" = "issue_row"."policy_id"; |
jbe@305 | 31 SELECT count(1) INTO "dimension_v" |
jbe@305 | 32 FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; |
jbe@305 | 33 -- Create "vote_matrix" with absolute number of votes in pairwise |
jbe@305 | 34 -- comparison: |
jbe@305 | 35 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]); |
jbe@305 | 36 "i" := 1; |
jbe@305 | 37 "j" := 2; |
jbe@305 | 38 FOR "battle_row" IN |
jbe@305 | 39 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" |
jbe@305 | 40 ORDER BY |
jbe@305 | 41 "winning_initiative_id" NULLS LAST, |
jbe@305 | 42 "losing_initiative_id" NULLS LAST |
jbe@305 | 43 LOOP |
jbe@305 | 44 "vote_matrix"["i"]["j"] := "battle_row"."count"; |
jbe@305 | 45 IF "j" = "dimension_v" THEN |
jbe@305 | 46 "i" := "i" + 1; |
jbe@305 | 47 "j" := 1; |
jbe@305 | 48 ELSE |
jbe@305 | 49 "j" := "j" + 1; |
jbe@305 | 50 IF "j" = "i" THEN |
jbe@305 | 51 "j" := "j" + 1; |
jbe@305 | 52 END IF; |
jbe@305 | 53 END IF; |
jbe@305 | 54 END LOOP; |
jbe@305 | 55 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN |
jbe@305 | 56 RAISE EXCEPTION 'Wrong battle count (should not happen)'; |
jbe@305 | 57 END IF; |
jbe@305 | 58 -- Store defeat strengths in "matrix" using "defeat_strength" |
jbe@305 | 59 -- function: |
jbe@305 | 60 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]); |
jbe@305 | 61 "i" := 1; |
jbe@305 | 62 LOOP |
jbe@305 | 63 "j" := 1; |
jbe@305 | 64 LOOP |
jbe@305 | 65 IF "i" != "j" THEN |
jbe@305 | 66 "matrix"["i"]["j"] := "defeat_strength"( |
jbe@305 | 67 "vote_matrix"["i"]["j"], |
jbe@305 | 68 "vote_matrix"["j"]["i"] |
jbe@305 | 69 ); |
jbe@305 | 70 END IF; |
jbe@305 | 71 EXIT WHEN "j" = "dimension_v"; |
jbe@305 | 72 "j" := "j" + 1; |
jbe@305 | 73 END LOOP; |
jbe@305 | 74 EXIT WHEN "i" = "dimension_v"; |
jbe@305 | 75 "i" := "i" + 1; |
jbe@305 | 76 END LOOP; |
jbe@305 | 77 -- Find best paths: |
jbe@305 | 78 "i" := 1; |
jbe@305 | 79 LOOP |
jbe@305 | 80 "j" := 1; |
jbe@305 | 81 LOOP |
jbe@305 | 82 IF "i" != "j" THEN |
jbe@305 | 83 "k" := 1; |
jbe@305 | 84 LOOP |
jbe@305 | 85 IF "i" != "k" AND "j" != "k" THEN |
jbe@305 | 86 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN |
jbe@305 | 87 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN |
jbe@305 | 88 "matrix"["j"]["k"] := "matrix"["j"]["i"]; |
jbe@305 | 89 END IF; |
jbe@305 | 90 ELSE |
jbe@305 | 91 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN |
jbe@305 | 92 "matrix"["j"]["k"] := "matrix"["i"]["k"]; |
jbe@305 | 93 END IF; |
jbe@305 | 94 END IF; |
jbe@305 | 95 END IF; |
jbe@305 | 96 EXIT WHEN "k" = "dimension_v"; |
jbe@305 | 97 "k" := "k" + 1; |
jbe@305 | 98 END LOOP; |
jbe@305 | 99 END IF; |
jbe@305 | 100 EXIT WHEN "j" = "dimension_v"; |
jbe@305 | 101 "j" := "j" + 1; |
jbe@305 | 102 END LOOP; |
jbe@305 | 103 EXIT WHEN "i" = "dimension_v"; |
jbe@305 | 104 "i" := "i" + 1; |
jbe@305 | 105 END LOOP; |
jbe@305 | 106 -- Determine order of winners: |
jbe@305 | 107 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]); |
jbe@305 | 108 "rank_v" := 1; |
jbe@305 | 109 "done_v" := 0; |
jbe@305 | 110 LOOP |
jbe@305 | 111 "winners_ary" := '{}'; |
jbe@305 | 112 "i" := 1; |
jbe@305 | 113 LOOP |
jbe@305 | 114 IF "rank_ary"["i"] ISNULL THEN |
jbe@305 | 115 "j" := 1; |
jbe@305 | 116 LOOP |
jbe@305 | 117 IF |
jbe@305 | 118 "i" != "j" AND |
jbe@305 | 119 "rank_ary"["j"] ISNULL AND |
jbe@305 | 120 "matrix"["j"]["i"] > "matrix"["i"]["j"] |
jbe@305 | 121 THEN |
jbe@305 | 122 -- someone else is better |
jbe@305 | 123 EXIT; |
jbe@305 | 124 END IF; |
jbe@305 | 125 IF "j" = "dimension_v" THEN |
jbe@305 | 126 -- noone is better |
jbe@305 | 127 "winners_ary" := "winners_ary" || "i"; |
jbe@305 | 128 EXIT; |
jbe@305 | 129 END IF; |
jbe@305 | 130 "j" := "j" + 1; |
jbe@305 | 131 END LOOP; |
jbe@305 | 132 END IF; |
jbe@305 | 133 EXIT WHEN "i" = "dimension_v"; |
jbe@305 | 134 "i" := "i" + 1; |
jbe@305 | 135 END LOOP; |
jbe@305 | 136 "i" := 1; |
jbe@305 | 137 LOOP |
jbe@305 | 138 "rank_ary"["winners_ary"["i"]] := "rank_v"; |
jbe@305 | 139 "done_v" := "done_v" + 1; |
jbe@305 | 140 EXIT WHEN "i" = array_upper("winners_ary", 1); |
jbe@305 | 141 "i" := "i" + 1; |
jbe@305 | 142 END LOOP; |
jbe@305 | 143 EXIT WHEN "done_v" = "dimension_v"; |
jbe@305 | 144 "rank_v" := "rank_v" + 1; |
jbe@305 | 145 END LOOP; |
jbe@305 | 146 -- write preliminary results: |
jbe@305 | 147 "i" := 1; |
jbe@305 | 148 FOR "initiative_id_v" IN |
jbe@305 | 149 SELECT "id" FROM "initiative" |
jbe@305 | 150 WHERE "issue_id" = "issue_id_p" AND "admitted" |
jbe@305 | 151 ORDER BY "id" |
jbe@305 | 152 LOOP |
jbe@305 | 153 UPDATE "initiative" SET |
jbe@305 | 154 "direct_majority" = |
jbe@305 | 155 CASE WHEN "policy_row"."direct_majority_strict" THEN |
jbe@305 | 156 "positive_votes" * "policy_row"."direct_majority_den" > |
jbe@305 | 157 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") |
jbe@305 | 158 ELSE |
jbe@305 | 159 "positive_votes" * "policy_row"."direct_majority_den" >= |
jbe@305 | 160 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") |
jbe@305 | 161 END |
jbe@305 | 162 AND "positive_votes" >= "policy_row"."direct_majority_positive" |
jbe@305 | 163 AND "issue_row"."voter_count"-"negative_votes" >= |
jbe@305 | 164 "policy_row"."direct_majority_non_negative", |
jbe@305 | 165 "indirect_majority" = |
jbe@305 | 166 CASE WHEN "policy_row"."indirect_majority_strict" THEN |
jbe@305 | 167 "positive_votes" * "policy_row"."indirect_majority_den" > |
jbe@305 | 168 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") |
jbe@305 | 169 ELSE |
jbe@305 | 170 "positive_votes" * "policy_row"."indirect_majority_den" >= |
jbe@305 | 171 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") |
jbe@305 | 172 END |
jbe@305 | 173 AND "positive_votes" >= "policy_row"."indirect_majority_positive" |
jbe@305 | 174 AND "issue_row"."voter_count"-"negative_votes" >= |
jbe@305 | 175 "policy_row"."indirect_majority_non_negative", |
jbe@305 | 176 "schulze_rank" = "rank_ary"["i"], |
jbe@305 | 177 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"], |
jbe@305 | 178 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"], |
jbe@305 | 179 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"], |
jbe@305 | 180 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0, |
jbe@305 | 181 "eligible" = FALSE, |
jbe@305 | 182 "winner" = FALSE, |
jbe@305 | 183 "rank" = NULL -- NOTE: in cases of manual reset of issue state |
jbe@305 | 184 WHERE "id" = "initiative_id_v"; |
jbe@305 | 185 "i" := "i" + 1; |
jbe@305 | 186 END LOOP; |
jbe@305 | 187 IF "i" != "dimension_v" THEN |
jbe@305 | 188 RAISE EXCEPTION 'Wrong winner count (should not happen)'; |
jbe@305 | 189 END IF; |
jbe@305 | 190 -- take indirect majorities into account: |
jbe@305 | 191 LOOP |
jbe@305 | 192 UPDATE "initiative" SET "indirect_majority" = TRUE |
jbe@305 | 193 FROM ( |
jbe@305 | 194 SELECT "new_initiative"."id" AS "initiative_id" |
jbe@305 | 195 FROM "initiative" "old_initiative" |
jbe@305 | 196 JOIN "initiative" "new_initiative" |
jbe@305 | 197 ON "new_initiative"."issue_id" = "issue_id_p" |
jbe@305 | 198 AND "new_initiative"."indirect_majority" = FALSE |
jbe@305 | 199 JOIN "battle" "battle_win" |
jbe@305 | 200 ON "battle_win"."issue_id" = "issue_id_p" |
jbe@305 | 201 AND "battle_win"."winning_initiative_id" = "new_initiative"."id" |
jbe@305 | 202 AND "battle_win"."losing_initiative_id" = "old_initiative"."id" |
jbe@305 | 203 JOIN "battle" "battle_lose" |
jbe@305 | 204 ON "battle_lose"."issue_id" = "issue_id_p" |
jbe@305 | 205 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id" |
jbe@305 | 206 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id" |
jbe@305 | 207 WHERE "old_initiative"."issue_id" = "issue_id_p" |
jbe@305 | 208 AND "old_initiative"."indirect_majority" = TRUE |
jbe@305 | 209 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN |
jbe@305 | 210 "battle_win"."count" * "policy_row"."indirect_majority_den" > |
jbe@305 | 211 "policy_row"."indirect_majority_num" * |
jbe@305 | 212 ("battle_win"."count"+"battle_lose"."count") |
jbe@305 | 213 ELSE |
jbe@305 | 214 "battle_win"."count" * "policy_row"."indirect_majority_den" >= |
jbe@305 | 215 "policy_row"."indirect_majority_num" * |
jbe@305 | 216 ("battle_win"."count"+"battle_lose"."count") |
jbe@305 | 217 END |
jbe@305 | 218 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive" |
jbe@305 | 219 AND "issue_row"."voter_count"-"battle_lose"."count" >= |
jbe@305 | 220 "policy_row"."indirect_majority_non_negative" |
jbe@305 | 221 ) AS "subquery" |
jbe@305 | 222 WHERE "id" = "subquery"."initiative_id"; |
jbe@305 | 223 EXIT WHEN NOT FOUND; |
jbe@305 | 224 END LOOP; |
jbe@305 | 225 -- set "multistage_majority" for remaining matching initiatives: |
jbe@305 | 226 UPDATE "initiative" SET "multistage_majority" = TRUE |
jbe@305 | 227 FROM ( |
jbe@305 | 228 SELECT "losing_initiative"."id" AS "initiative_id" |
jbe@305 | 229 FROM "initiative" "losing_initiative" |
jbe@305 | 230 JOIN "initiative" "winning_initiative" |
jbe@305 | 231 ON "winning_initiative"."issue_id" = "issue_id_p" |
jbe@305 | 232 AND "winning_initiative"."admitted" |
jbe@305 | 233 JOIN "battle" "battle_win" |
jbe@305 | 234 ON "battle_win"."issue_id" = "issue_id_p" |
jbe@305 | 235 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id" |
jbe@305 | 236 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id" |
jbe@305 | 237 JOIN "battle" "battle_lose" |
jbe@305 | 238 ON "battle_lose"."issue_id" = "issue_id_p" |
jbe@305 | 239 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id" |
jbe@305 | 240 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id" |
jbe@305 | 241 WHERE "losing_initiative"."issue_id" = "issue_id_p" |
jbe@305 | 242 AND "losing_initiative"."admitted" |
jbe@305 | 243 AND "winning_initiative"."schulze_rank" < |
jbe@305 | 244 "losing_initiative"."schulze_rank" |
jbe@305 | 245 AND "battle_win"."count" > "battle_lose"."count" |
jbe@305 | 246 AND ( |
jbe@305 | 247 "battle_win"."count" > "winning_initiative"."positive_votes" OR |
jbe@305 | 248 "battle_lose"."count" < "losing_initiative"."negative_votes" ) |
jbe@305 | 249 ) AS "subquery" |
jbe@305 | 250 WHERE "id" = "subquery"."initiative_id"; |
jbe@305 | 251 -- mark eligible initiatives: |
jbe@305 | 252 UPDATE "initiative" SET "eligible" = TRUE |
jbe@305 | 253 WHERE "issue_id" = "issue_id_p" |
jbe@305 | 254 AND "initiative"."direct_majority" |
jbe@305 | 255 AND "initiative"."indirect_majority" |
jbe@305 | 256 AND "initiative"."better_than_status_quo" |
jbe@305 | 257 AND ( |
jbe@305 | 258 "policy_row"."no_multistage_majority" = FALSE OR |
jbe@305 | 259 "initiative"."multistage_majority" = FALSE ) |
jbe@305 | 260 AND ( |
jbe@305 | 261 "policy_row"."no_reverse_beat_path" = FALSE OR |
jbe@305 | 262 "initiative"."reverse_beat_path" = FALSE ); |
jbe@305 | 263 -- mark final winner: |
jbe@305 | 264 UPDATE "initiative" SET "winner" = TRUE |
jbe@305 | 265 FROM ( |
jbe@305 | 266 SELECT "id" AS "initiative_id" |
jbe@305 | 267 FROM "initiative" |
jbe@305 | 268 WHERE "issue_id" = "issue_id_p" AND "eligible" |
jbe@305 | 269 ORDER BY |
jbe@305 | 270 "schulze_rank", |
jbe@305 | 271 "id" |
jbe@305 | 272 LIMIT 1 |
jbe@305 | 273 ) AS "subquery" |
jbe@305 | 274 WHERE "id" = "subquery"."initiative_id"; |
jbe@305 | 275 -- write (final) ranks: |
jbe@305 | 276 "rank_v" := 1; |
jbe@305 | 277 FOR "initiative_id_v" IN |
jbe@305 | 278 SELECT "id" |
jbe@305 | 279 FROM "initiative" |
jbe@305 | 280 WHERE "issue_id" = "issue_id_p" AND "admitted" |
jbe@305 | 281 ORDER BY |
jbe@305 | 282 "winner" DESC, |
jbe@305 | 283 "eligible" DESC, |
jbe@305 | 284 "schulze_rank", |
jbe@305 | 285 "id" |
jbe@305 | 286 LOOP |
jbe@305 | 287 UPDATE "initiative" SET "rank" = "rank_v" |
jbe@305 | 288 WHERE "id" = "initiative_id_v"; |
jbe@305 | 289 "rank_v" := "rank_v" + 1; |
jbe@305 | 290 END LOOP; |
jbe@305 | 291 -- set schulze rank of status quo and mark issue as finished: |
jbe@305 | 292 UPDATE "issue" SET |
jbe@305 | 293 "status_quo_schulze_rank" = "rank_ary"["dimension_v"], |
jbe@305 | 294 "state" = |
jbe@305 | 295 CASE WHEN EXISTS ( |
jbe@305 | 296 SELECT NULL FROM "initiative" |
jbe@305 | 297 WHERE "issue_id" = "issue_id_p" AND "winner" |
jbe@305 | 298 ) THEN |
jbe@305 | 299 'finished_with_winner'::"issue_state" |
jbe@305 | 300 ELSE |
jbe@305 | 301 'finished_without_winner'::"issue_state" |
jbe@305 | 302 END, |
jbe@305 | 303 "ranks_available" = TRUE |
jbe@305 | 304 WHERE "id" = "issue_id_p"; |
jbe@305 | 305 RETURN; |
jbe@305 | 306 END; |
jbe@305 | 307 $$; |
jbe@305 | 308 |
jbe@308 | 309 DROP FUNCTION IF EXISTS "vote_ratio" |
jbe@308 | 310 ( "initiative"."positive_votes"%TYPE, |
jbe@308 | 311 "initiative"."negative_votes"%TYPE ); |
jbe@308 | 312 |
jbe@305 | 313 COMMIT; |