liquid_feedback_core

annotate update/core-update.v2.0.2-v2.0.3.sql @ 622:5b7c4082d7b3

find changesets by author, revision, files, or words in the commit message
Version 4.2.2 with geoindices uninstalled by default
author jbe
date Fri Feb 05 12:53:00 2021 +0100 (2021-02-05)
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;

Impressum / About Us