liquid_feedback_core

annotate update/core-update.v2.0.11-v2.0.12.sql @ 494:b4b660562322

find changesets by author, revision, files, or words in the commit message
Another bugfix in function "get_initiatives_for_notification"
author jbe
date Sun Apr 03 20:00:20 2016 +0200 (2016-04-03)
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;

Impressum / About Us