liquid_feedback_core

annotate update/core-update.v2.2.5-v3.0.0.sql @ 610:22c6d1da7d78

find changesets by author, revision, files, or words in the commit message
Bugfix regarding "issue_privilege"."voting_right" in "delegation_chain" function
author jbe
date Sat May 16 17:59:54 2020 +0200 (2020-05-16)
parents 399dc1a86398
children
rev   line source
jbe@396 1 BEGIN;
jbe@396 2
jbe@396 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@412 4 SELECT * FROM (VALUES ('3.0.0', 3, 0, 0))
jbe@396 5 AS "subquery"("string", "major", "minor", "revision");
jbe@396 6
jbe@410 7 CREATE TABLE "issue_order_in_admission_state" (
jbe@400 8 "id" INT8 PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@410 9 "order_in_area" INT4,
jbe@410 10 "order_in_unit" INT4 );
jbe@396 11
jbe@410 12 COMMENT ON TABLE "issue_order_in_admission_state" IS 'Ordering information for issues that are not stored in the "issue" table to avoid locking of multiple issues at once; Filled/updated by "lf_update_issue_order"';
jbe@398 13
jbe@410 14 COMMENT ON COLUMN "issue_order_in_admission_state"."id" IS 'References "issue" ("id") but has no referential integrity trigger associated, due to performance/locking issues';
jbe@410 15 COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_area" IS 'Order of issues in admission state within a single area; NULL values sort last';
jbe@410 16 COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_unit" IS 'Order of issues in admission state within all areas of a unit; NULL values sort last';
jbe@396 17
jbe@396 18 CREATE VIEW "issue_supporter_in_admission_state" AS
jbe@396 19 SELECT DISTINCT
jbe@410 20 "area"."unit_id",
jbe@396 21 "issue"."area_id",
jbe@396 22 "issue"."id" AS "issue_id",
jbe@396 23 "supporter"."member_id",
jbe@396 24 "direct_interest_snapshot"."weight"
jbe@396 25 FROM "issue"
jbe@410 26 JOIN "area" ON "area"."id" = "issue"."area_id"
jbe@396 27 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
jbe@396 28 JOIN "direct_interest_snapshot"
jbe@396 29 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
jbe@396 30 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
jbe@396 31 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
jbe@396 32 WHERE "issue"."state" = 'admission'::"issue_state";
jbe@396 33
jbe@396 34 COMMENT ON VIEW "issue_supporter_in_admission_state" IS 'Helper view for "lf_update_issue_order" to allow a (proportional) ordering of issues within an area';
jbe@396 35
jbe@411 36 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking';
jbe@411 37 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo';
jbe@411 38 COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (DEPRECATED, since schulze-ranking is unique per issue; use "better_than_status_quo"=FALSE)';
jbe@411 39 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank"';
jbe@411 40
jbe@411 41 CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
jbe@411 42 RETURNS VOID
jbe@411 43 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@411 44 DECLARE
jbe@411 45 "issue_row" "issue"%ROWTYPE;
jbe@411 46 "policy_row" "policy"%ROWTYPE;
jbe@411 47 "dimension_v" INTEGER;
jbe@411 48 "vote_matrix" INT4[][]; -- absolute votes
jbe@411 49 "matrix" INT8[][]; -- defeat strength / best paths
jbe@411 50 "i" INTEGER;
jbe@411 51 "j" INTEGER;
jbe@411 52 "k" INTEGER;
jbe@411 53 "battle_row" "battle"%ROWTYPE;
jbe@411 54 "rank_ary" INT4[];
jbe@411 55 "rank_v" INT4;
jbe@411 56 "initiative_id_v" "initiative"."id"%TYPE;
jbe@411 57 BEGIN
jbe@411 58 PERFORM "require_transaction_isolation"();
jbe@411 59 SELECT * INTO "issue_row"
jbe@411 60 FROM "issue" WHERE "id" = "issue_id_p";
jbe@411 61 SELECT * INTO "policy_row"
jbe@411 62 FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@411 63 SELECT count(1) INTO "dimension_v"
jbe@411 64 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
jbe@411 65 -- Create "vote_matrix" with absolute number of votes in pairwise
jbe@411 66 -- comparison:
jbe@411 67 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
jbe@411 68 "i" := 1;
jbe@411 69 "j" := 2;
jbe@411 70 FOR "battle_row" IN
jbe@411 71 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
jbe@411 72 ORDER BY
jbe@411 73 "winning_initiative_id" NULLS FIRST,
jbe@411 74 "losing_initiative_id" NULLS FIRST
jbe@411 75 LOOP
jbe@411 76 "vote_matrix"["i"]["j"] := "battle_row"."count";
jbe@411 77 IF "j" = "dimension_v" THEN
jbe@411 78 "i" := "i" + 1;
jbe@411 79 "j" := 1;
jbe@411 80 ELSE
jbe@411 81 "j" := "j" + 1;
jbe@411 82 IF "j" = "i" THEN
jbe@411 83 "j" := "j" + 1;
jbe@411 84 END IF;
jbe@411 85 END IF;
jbe@411 86 END LOOP;
jbe@411 87 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
jbe@411 88 RAISE EXCEPTION 'Wrong battle count (should not happen)';
jbe@411 89 END IF;
jbe@411 90 -- Store defeat strengths in "matrix" using "defeat_strength"
jbe@411 91 -- function:
jbe@411 92 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
jbe@411 93 "i" := 1;
jbe@411 94 LOOP
jbe@411 95 "j" := 1;
jbe@411 96 LOOP
jbe@411 97 IF "i" != "j" THEN
jbe@411 98 "matrix"["i"]["j"] := "defeat_strength"(
jbe@411 99 "vote_matrix"["i"]["j"],
jbe@411 100 "vote_matrix"["j"]["i"]
jbe@411 101 );
jbe@411 102 END IF;
jbe@411 103 EXIT WHEN "j" = "dimension_v";
jbe@411 104 "j" := "j" + 1;
jbe@411 105 END LOOP;
jbe@411 106 EXIT WHEN "i" = "dimension_v";
jbe@411 107 "i" := "i" + 1;
jbe@411 108 END LOOP;
jbe@411 109 -- Find best paths:
jbe@411 110 "i" := 1;
jbe@411 111 LOOP
jbe@411 112 "j" := 1;
jbe@411 113 LOOP
jbe@411 114 IF "i" != "j" THEN
jbe@411 115 "k" := 1;
jbe@411 116 LOOP
jbe@411 117 IF "i" != "k" AND "j" != "k" THEN
jbe@411 118 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
jbe@411 119 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
jbe@411 120 "matrix"["j"]["k"] := "matrix"["j"]["i"];
jbe@411 121 END IF;
jbe@411 122 ELSE
jbe@411 123 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
jbe@411 124 "matrix"["j"]["k"] := "matrix"["i"]["k"];
jbe@411 125 END IF;
jbe@411 126 END IF;
jbe@411 127 END IF;
jbe@411 128 EXIT WHEN "k" = "dimension_v";
jbe@411 129 "k" := "k" + 1;
jbe@411 130 END LOOP;
jbe@411 131 END IF;
jbe@411 132 EXIT WHEN "j" = "dimension_v";
jbe@411 133 "j" := "j" + 1;
jbe@411 134 END LOOP;
jbe@411 135 EXIT WHEN "i" = "dimension_v";
jbe@411 136 "i" := "i" + 1;
jbe@411 137 END LOOP;
jbe@411 138 -- Determine order of winners:
jbe@411 139 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
jbe@411 140 "rank_v" := 1;
jbe@411 141 LOOP
jbe@411 142 "i" := 1;
jbe@411 143 LOOP
jbe@411 144 IF "rank_ary"["i"] ISNULL THEN
jbe@411 145 "j" := 1;
jbe@411 146 LOOP
jbe@411 147 IF
jbe@411 148 "i" != "j" AND
jbe@411 149 "rank_ary"["j"] ISNULL AND
jbe@411 150 ( "matrix"["j"]["i"] > "matrix"["i"]["j"] OR
jbe@411 151 -- tie-breaking by "id"
jbe@411 152 ( "matrix"["j"]["i"] = "matrix"["i"]["j"] AND
jbe@411 153 "j" < "i" ) )
jbe@411 154 THEN
jbe@411 155 -- someone else is better
jbe@411 156 EXIT;
jbe@411 157 END IF;
jbe@411 158 "j" := "j" + 1;
jbe@411 159 IF "j" = "dimension_v" + 1 THEN
jbe@411 160 -- noone is better
jbe@411 161 "rank_ary"["i"] := "rank_v";
jbe@411 162 EXIT;
jbe@411 163 END IF;
jbe@411 164 END LOOP;
jbe@411 165 EXIT WHEN "j" = "dimension_v" + 1;
jbe@411 166 END IF;
jbe@411 167 "i" := "i" + 1;
jbe@411 168 IF "i" > "dimension_v" THEN
jbe@411 169 RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
jbe@411 170 END IF;
jbe@411 171 END LOOP;
jbe@411 172 EXIT WHEN "rank_v" = "dimension_v";
jbe@411 173 "rank_v" := "rank_v" + 1;
jbe@411 174 END LOOP;
jbe@411 175 -- write preliminary results:
jbe@411 176 "i" := 2; -- omit status quo with "i" = 1
jbe@411 177 FOR "initiative_id_v" IN
jbe@411 178 SELECT "id" FROM "initiative"
jbe@411 179 WHERE "issue_id" = "issue_id_p" AND "admitted"
jbe@411 180 ORDER BY "id"
jbe@411 181 LOOP
jbe@411 182 UPDATE "initiative" SET
jbe@411 183 "direct_majority" =
jbe@411 184 CASE WHEN "policy_row"."direct_majority_strict" THEN
jbe@411 185 "positive_votes" * "policy_row"."direct_majority_den" >
jbe@411 186 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
jbe@411 187 ELSE
jbe@411 188 "positive_votes" * "policy_row"."direct_majority_den" >=
jbe@411 189 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
jbe@411 190 END
jbe@411 191 AND "positive_votes" >= "policy_row"."direct_majority_positive"
jbe@411 192 AND "issue_row"."voter_count"-"negative_votes" >=
jbe@411 193 "policy_row"."direct_majority_non_negative",
jbe@411 194 "indirect_majority" =
jbe@411 195 CASE WHEN "policy_row"."indirect_majority_strict" THEN
jbe@411 196 "positive_votes" * "policy_row"."indirect_majority_den" >
jbe@411 197 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
jbe@411 198 ELSE
jbe@411 199 "positive_votes" * "policy_row"."indirect_majority_den" >=
jbe@411 200 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
jbe@411 201 END
jbe@411 202 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
jbe@411 203 AND "issue_row"."voter_count"-"negative_votes" >=
jbe@411 204 "policy_row"."indirect_majority_non_negative",
jbe@411 205 "schulze_rank" = "rank_ary"["i"],
jbe@411 206 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
jbe@411 207 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"[1],
jbe@411 208 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"[1],
jbe@411 209 "reverse_beat_path" = "matrix"[1]["i"] >= 0,
jbe@411 210 "eligible" = FALSE,
jbe@411 211 "winner" = FALSE,
jbe@411 212 "rank" = NULL -- NOTE: in cases of manual reset of issue state
jbe@411 213 WHERE "id" = "initiative_id_v";
jbe@411 214 "i" := "i" + 1;
jbe@411 215 END LOOP;
jbe@411 216 IF "i" != "dimension_v" + 1 THEN
jbe@411 217 RAISE EXCEPTION 'Wrong winner count (should not happen)';
jbe@411 218 END IF;
jbe@411 219 -- take indirect majorities into account:
jbe@411 220 LOOP
jbe@411 221 UPDATE "initiative" SET "indirect_majority" = TRUE
jbe@411 222 FROM (
jbe@411 223 SELECT "new_initiative"."id" AS "initiative_id"
jbe@411 224 FROM "initiative" "old_initiative"
jbe@411 225 JOIN "initiative" "new_initiative"
jbe@411 226 ON "new_initiative"."issue_id" = "issue_id_p"
jbe@411 227 AND "new_initiative"."indirect_majority" = FALSE
jbe@411 228 JOIN "battle" "battle_win"
jbe@411 229 ON "battle_win"."issue_id" = "issue_id_p"
jbe@411 230 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
jbe@411 231 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
jbe@411 232 JOIN "battle" "battle_lose"
jbe@411 233 ON "battle_lose"."issue_id" = "issue_id_p"
jbe@411 234 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
jbe@411 235 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
jbe@411 236 WHERE "old_initiative"."issue_id" = "issue_id_p"
jbe@411 237 AND "old_initiative"."indirect_majority" = TRUE
jbe@411 238 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
jbe@411 239 "battle_win"."count" * "policy_row"."indirect_majority_den" >
jbe@411 240 "policy_row"."indirect_majority_num" *
jbe@411 241 ("battle_win"."count"+"battle_lose"."count")
jbe@411 242 ELSE
jbe@411 243 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
jbe@411 244 "policy_row"."indirect_majority_num" *
jbe@411 245 ("battle_win"."count"+"battle_lose"."count")
jbe@411 246 END
jbe@411 247 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
jbe@411 248 AND "issue_row"."voter_count"-"battle_lose"."count" >=
jbe@411 249 "policy_row"."indirect_majority_non_negative"
jbe@411 250 ) AS "subquery"
jbe@411 251 WHERE "id" = "subquery"."initiative_id";
jbe@411 252 EXIT WHEN NOT FOUND;
jbe@411 253 END LOOP;
jbe@411 254 -- set "multistage_majority" for remaining matching initiatives:
jbe@411 255 UPDATE "initiative" SET "multistage_majority" = TRUE
jbe@411 256 FROM (
jbe@411 257 SELECT "losing_initiative"."id" AS "initiative_id"
jbe@411 258 FROM "initiative" "losing_initiative"
jbe@411 259 JOIN "initiative" "winning_initiative"
jbe@411 260 ON "winning_initiative"."issue_id" = "issue_id_p"
jbe@411 261 AND "winning_initiative"."admitted"
jbe@411 262 JOIN "battle" "battle_win"
jbe@411 263 ON "battle_win"."issue_id" = "issue_id_p"
jbe@411 264 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
jbe@411 265 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
jbe@411 266 JOIN "battle" "battle_lose"
jbe@411 267 ON "battle_lose"."issue_id" = "issue_id_p"
jbe@411 268 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
jbe@411 269 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
jbe@411 270 WHERE "losing_initiative"."issue_id" = "issue_id_p"
jbe@411 271 AND "losing_initiative"."admitted"
jbe@411 272 AND "winning_initiative"."schulze_rank" <
jbe@411 273 "losing_initiative"."schulze_rank"
jbe@411 274 AND "battle_win"."count" > "battle_lose"."count"
jbe@411 275 AND (
jbe@411 276 "battle_win"."count" > "winning_initiative"."positive_votes" OR
jbe@411 277 "battle_lose"."count" < "losing_initiative"."negative_votes" )
jbe@411 278 ) AS "subquery"
jbe@411 279 WHERE "id" = "subquery"."initiative_id";
jbe@411 280 -- mark eligible initiatives:
jbe@411 281 UPDATE "initiative" SET "eligible" = TRUE
jbe@411 282 WHERE "issue_id" = "issue_id_p"
jbe@411 283 AND "initiative"."direct_majority"
jbe@411 284 AND "initiative"."indirect_majority"
jbe@411 285 AND "initiative"."better_than_status_quo"
jbe@411 286 AND (
jbe@411 287 "policy_row"."no_multistage_majority" = FALSE OR
jbe@411 288 "initiative"."multistage_majority" = FALSE )
jbe@411 289 AND (
jbe@411 290 "policy_row"."no_reverse_beat_path" = FALSE OR
jbe@411 291 "initiative"."reverse_beat_path" = FALSE );
jbe@411 292 -- mark final winner:
jbe@411 293 UPDATE "initiative" SET "winner" = TRUE
jbe@411 294 FROM (
jbe@411 295 SELECT "id" AS "initiative_id"
jbe@411 296 FROM "initiative"
jbe@411 297 WHERE "issue_id" = "issue_id_p" AND "eligible"
jbe@411 298 ORDER BY
jbe@411 299 "schulze_rank",
jbe@411 300 "id"
jbe@411 301 LIMIT 1
jbe@411 302 ) AS "subquery"
jbe@411 303 WHERE "id" = "subquery"."initiative_id";
jbe@411 304 -- write (final) ranks:
jbe@411 305 "rank_v" := 1;
jbe@411 306 FOR "initiative_id_v" IN
jbe@411 307 SELECT "id"
jbe@411 308 FROM "initiative"
jbe@411 309 WHERE "issue_id" = "issue_id_p" AND "admitted"
jbe@411 310 ORDER BY
jbe@411 311 "winner" DESC,
jbe@411 312 "eligible" DESC,
jbe@411 313 "schulze_rank",
jbe@411 314 "id"
jbe@411 315 LOOP
jbe@411 316 UPDATE "initiative" SET "rank" = "rank_v"
jbe@411 317 WHERE "id" = "initiative_id_v";
jbe@411 318 "rank_v" := "rank_v" + 1;
jbe@411 319 END LOOP;
jbe@411 320 -- set schulze rank of status quo and mark issue as finished:
jbe@411 321 UPDATE "issue" SET
jbe@411 322 "status_quo_schulze_rank" = "rank_ary"[1],
jbe@411 323 "state" =
jbe@411 324 CASE WHEN EXISTS (
jbe@411 325 SELECT NULL FROM "initiative"
jbe@411 326 WHERE "issue_id" = "issue_id_p" AND "winner"
jbe@411 327 ) THEN
jbe@411 328 'finished_with_winner'::"issue_state"
jbe@411 329 ELSE
jbe@411 330 'finished_without_winner'::"issue_state"
jbe@411 331 END,
jbe@411 332 "closed" = "phase_finished",
jbe@411 333 "phase_finished" = NULL
jbe@411 334 WHERE "id" = "issue_id_p";
jbe@411 335 RETURN;
jbe@411 336 END;
jbe@411 337 $$;
jbe@411 338
jbe@396 339 COMMIT;

Impressum / About Us