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