liquid_feedback_core

annotate update/core-update.v3.0.1-v3.0.2.sql @ 531:37d6d15919f1

find changesets by author, revision, files, or words in the commit message
pgLatLon is now a stand-alone extension for PostgreSQL
author jbe
date Sun Aug 21 17:31:44 2016 +0200 (2016-08-21)
parents 603f6e4bbedf
children
rev   line source
jbe@423 1 BEGIN;
jbe@423 2
jbe@423 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@423 4 SELECT * FROM (VALUES ('3.0.2', 3, 0, 2))
jbe@423 5 AS "subquery"("string", "major", "minor", "revision");
jbe@423 6
jbe@430 7
jbe@430 8 CREATE TYPE "defeat_strength" AS ENUM ('simple', 'tuple');
jbe@430 9
jbe@430 10 COMMENT ON TYPE "defeat_strength" IS 'How pairwise defeats are measured for the Schulze method: ''simple'' = only the number of winning votes, ''tuple'' = primarily the number of winning votes, secondarily the number of losing votes';
jbe@430 11
jbe@430 12
jbe@430 13 CREATE TYPE "tie_breaking" AS ENUM ('simple', 'variant1', 'variant2');
jbe@430 14
jbe@430 15 COMMENT ON TYPE "tie_breaking" IS 'Tie-breaker for the Schulze method: ''simple'' = only initiative ids are used, ''variant1'' = use initiative ids in variant 1 for tie breaking of the links (TBRL) and sequentially forbid shared links, ''variant2'' = use initiative ids in variant 2 for tie breaking of the links (TBRL) and sequentially forbid shared links';
jbe@430 16
jbe@430 17
jbe@430 18 ALTER TABLE "policy" ADD COLUMN "defeat_strength" "defeat_strength" NOT NULL DEFAULT 'tuple';
jbe@430 19 ALTER TABLE "policy" ADD COLUMN "tie_breaking" "tie_breaking" NOT NULL DEFAULT 'variant1';
jbe@430 20
jbe@430 21 ALTER TABLE "policy" ADD
jbe@430 22 CONSTRAINT "no_reverse_beat_path_requires_tuple_defeat_strength" CHECK (
jbe@430 23 ("defeat_strength" = 'tuple'::"defeat_strength" OR "no_reverse_beat_path" = FALSE)
jbe@430 24 );
jbe@430 25
jbe@430 26 COMMENT ON COLUMN "policy"."defeat_strength" IS 'How pairwise defeats are measured for the Schulze method; see type "defeat_strength"; ''tuple'' is the recommended setting';
jbe@430 27 COMMENT ON COLUMN "policy"."tie_breaking" IS 'Tie-breaker for the Schulze method; see type "tie_breaking"; ''variant1'' or ''variant2'' are recommended';
jbe@430 28 COMMENT ON COLUMN "initiative"."reverse_beat_path" IS 'TRUE, if there is a beat path (may include ties) from this initiative to the status quo; set to NULL if "policy"."defeat_strength" is set to ''simple''';
jbe@430 29
jbe@430 30
jbe@433 31 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@433 32 RETURNS VOID
jbe@433 33 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@433 34 DECLARE
jbe@433 35 "area_id_v" "area"."id"%TYPE;
jbe@433 36 "unit_id_v" "unit"."id"%TYPE;
jbe@433 37 "member_id_v" "member"."id"%TYPE;
jbe@433 38 BEGIN
jbe@433 39 PERFORM "require_transaction_isolation"();
jbe@433 40 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@433 41 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
jbe@433 42 -- override protection triggers:
jbe@433 43 INSERT INTO "temporary_transaction_data" ("key", "value")
jbe@433 44 VALUES ('override_protection_triggers', TRUE::TEXT);
jbe@433 45 -- delete timestamp of voting comment:
jbe@433 46 UPDATE "direct_voter" SET "comment_changed" = NULL
jbe@433 47 WHERE "issue_id" = "issue_id_p";
jbe@433 48 -- delete delegating votes (in cases of manual reset of issue state):
jbe@433 49 DELETE FROM "delegating_voter"
jbe@433 50 WHERE "issue_id" = "issue_id_p";
jbe@433 51 -- delete votes from non-privileged voters:
jbe@433 52 DELETE FROM "direct_voter"
jbe@433 53 USING (
jbe@433 54 SELECT
jbe@433 55 "direct_voter"."member_id"
jbe@433 56 FROM "direct_voter"
jbe@433 57 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@433 58 LEFT JOIN "privilege"
jbe@433 59 ON "privilege"."unit_id" = "unit_id_v"
jbe@433 60 AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@433 61 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@433 62 "member"."active" = FALSE OR
jbe@433 63 "privilege"."voting_right" ISNULL OR
jbe@433 64 "privilege"."voting_right" = FALSE
jbe@433 65 )
jbe@433 66 ) AS "subquery"
jbe@433 67 WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@433 68 AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@433 69 -- consider delegations:
jbe@433 70 UPDATE "direct_voter" SET "weight" = 1
jbe@433 71 WHERE "issue_id" = "issue_id_p";
jbe@433 72 PERFORM "add_vote_delegations"("issue_id_p");
jbe@433 73 -- mark first preferences:
jbe@433 74 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
jbe@433 75 FROM (
jbe@433 76 SELECT
jbe@433 77 "vote"."initiative_id",
jbe@433 78 "vote"."member_id",
jbe@433 79 CASE WHEN "vote"."grade" > 0 THEN
jbe@433 80 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
jbe@433 81 ELSE NULL
jbe@433 82 END AS "first_preference"
jbe@433 83 FROM "vote"
jbe@433 84 JOIN "initiative" -- NOTE: due to missing index on issue_id
jbe@433 85 ON "vote"."issue_id" = "initiative"."issue_id"
jbe@433 86 JOIN "vote" AS "agg"
jbe@433 87 ON "initiative"."id" = "agg"."initiative_id"
jbe@433 88 AND "vote"."member_id" = "agg"."member_id"
jbe@433 89 GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
jbe@433 90 ) AS "subquery"
jbe@433 91 WHERE "vote"."issue_id" = "issue_id_p"
jbe@433 92 AND "vote"."initiative_id" = "subquery"."initiative_id"
jbe@433 93 AND "vote"."member_id" = "subquery"."member_id";
jbe@433 94 -- finish overriding protection triggers (avoids garbage):
jbe@433 95 DELETE FROM "temporary_transaction_data"
jbe@433 96 WHERE "key" = 'override_protection_triggers';
jbe@433 97 -- materialize battle_view:
jbe@433 98 -- NOTE: "closed" column of issue must be set at this point
jbe@433 99 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@433 100 INSERT INTO "battle" (
jbe@433 101 "issue_id",
jbe@433 102 "winning_initiative_id", "losing_initiative_id",
jbe@433 103 "count"
jbe@433 104 ) SELECT
jbe@433 105 "issue_id",
jbe@433 106 "winning_initiative_id", "losing_initiative_id",
jbe@433 107 "count"
jbe@433 108 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@433 109 -- set voter count:
jbe@433 110 UPDATE "issue" SET
jbe@433 111 "voter_count" = (
jbe@433 112 SELECT coalesce(sum("weight"), 0)
jbe@433 113 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@433 114 )
jbe@433 115 WHERE "id" = "issue_id_p";
jbe@433 116 -- calculate "first_preference_votes":
jbe@433 117 UPDATE "initiative"
jbe@433 118 SET "first_preference_votes" = coalesce("subquery"."sum", 0)
jbe@433 119 FROM (
jbe@433 120 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
jbe@433 121 FROM "vote" JOIN "direct_voter"
jbe@433 122 ON "vote"."issue_id" = "direct_voter"."issue_id"
jbe@433 123 AND "vote"."member_id" = "direct_voter"."member_id"
jbe@433 124 WHERE "vote"."first_preference"
jbe@433 125 GROUP BY "vote"."initiative_id"
jbe@433 126 ) AS "subquery"
jbe@433 127 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@433 128 AND "initiative"."admitted"
jbe@433 129 AND "initiative"."id" = "subquery"."initiative_id";
jbe@433 130 -- copy "positive_votes" and "negative_votes" from "battle" table:
jbe@433 131 UPDATE "initiative" SET
jbe@433 132 "positive_votes" = "battle_win"."count",
jbe@433 133 "negative_votes" = "battle_lose"."count"
jbe@433 134 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
jbe@433 135 WHERE
jbe@433 136 "battle_win"."issue_id" = "issue_id_p" AND
jbe@433 137 "battle_win"."winning_initiative_id" = "initiative"."id" AND
jbe@433 138 "battle_win"."losing_initiative_id" ISNULL AND
jbe@433 139 "battle_lose"."issue_id" = "issue_id_p" AND
jbe@433 140 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
jbe@433 141 "battle_lose"."winning_initiative_id" ISNULL;
jbe@433 142 END;
jbe@433 143 $$;
jbe@433 144
jbe@433 145
jbe@430 146 DROP FUNCTION "calculate_ranks"("issue"."id"%TYPE);
jbe@430 147 DROP FUNCTION "defeat_strength"(INT4, INT4);
jbe@430 148
jbe@430 149
jbe@430 150 CREATE FUNCTION "defeat_strength"
jbe@430 151 ( "positive_votes_p" INT4,
jbe@430 152 "negative_votes_p" INT4,
jbe@430 153 "defeat_strength_p" "defeat_strength" )
jbe@430 154 RETURNS INT8
jbe@430 155 LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@430 156 BEGIN
jbe@430 157 IF "defeat_strength_p" = 'simple'::"defeat_strength" THEN
jbe@430 158 IF "positive_votes_p" > "negative_votes_p" THEN
jbe@430 159 RETURN "positive_votes_p";
jbe@430 160 ELSE
jbe@430 161 RETURN 0;
jbe@430 162 END IF;
jbe@430 163 ELSE
jbe@430 164 IF "positive_votes_p" > "negative_votes_p" THEN
jbe@430 165 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
jbe@430 166 ELSIF "positive_votes_p" = "negative_votes_p" THEN
jbe@430 167 RETURN 0;
jbe@430 168 ELSE
jbe@430 169 RETURN -1;
jbe@430 170 END IF;
jbe@430 171 END IF;
jbe@430 172 END;
jbe@430 173 $$;
jbe@430 174
jbe@430 175 COMMENT ON FUNCTION "defeat_strength"(INT4, INT4, "defeat_strength") IS 'Calculates defeat strength (INT8!) according to the "defeat_strength" option (see comment on type "defeat_strength")';
jbe@430 176
jbe@430 177
jbe@430 178 CREATE FUNCTION "secondary_link_strength"
jbe@430 179 ( "initiative1_ord_p" INT4,
jbe@430 180 "initiative2_ord_p" INT4,
jbe@430 181 "tie_breaking_p" "tie_breaking" )
jbe@430 182 RETURNS INT8
jbe@430 183 LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@430 184 BEGIN
jbe@430 185 IF "initiative1_ord_p" = "initiative2_ord_p" THEN
jbe@430 186 RAISE EXCEPTION 'Identical initiative ids passed to "secondary_link_strength" function (should not happen)';
jbe@430 187 END IF;
jbe@430 188 RETURN (
jbe@430 189 CASE WHEN "tie_breaking_p" = 'simple'::"tie_breaking" THEN
jbe@430 190 0
jbe@430 191 ELSE
jbe@430 192 CASE WHEN "initiative1_ord_p" < "initiative2_ord_p" THEN
jbe@430 193 1::INT8 << 62
jbe@430 194 ELSE 0 END
jbe@430 195 +
jbe@430 196 CASE WHEN "tie_breaking_p" = 'variant2'::"tie_breaking" THEN
jbe@430 197 ("initiative2_ord_p"::INT8 << 31) - "initiative1_ord_p"::INT8
jbe@430 198 ELSE
jbe@430 199 "initiative2_ord_p"::INT8 - ("initiative1_ord_p"::INT8 << 31)
jbe@430 200 END
jbe@430 201 END
jbe@430 202 );
jbe@430 203 END;
jbe@430 204 $$;
jbe@430 205
jbe@430 206 COMMENT ON FUNCTION "secondary_link_strength"(INT4, INT4, "tie_breaking") IS 'Calculates a secondary criterion for the defeat strength (tie-breaking of the links)';
jbe@430 207
jbe@430 208
jbe@430 209 CREATE TYPE "link_strength" AS (
jbe@430 210 "primary" INT8,
jbe@430 211 "secondary" INT8 );
jbe@430 212
jbe@430 213 COMMENT ON TYPE "link_strength" IS 'Type to store the defeat strength of a link between two candidates plus a secondary criterion to create unique link strengths between the candidates (needed for tie-breaking ''variant1'' and ''variant2'')';
jbe@430 214
jbe@430 215
jbe@430 216 CREATE FUNCTION "find_best_paths"("matrix_d" "link_strength"[][])
jbe@430 217 RETURNS "link_strength"[][]
jbe@430 218 LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@430 219 DECLARE
jbe@430 220 "dimension_v" INT4;
jbe@430 221 "matrix_p" "link_strength"[][];
jbe@430 222 "i" INT4;
jbe@430 223 "j" INT4;
jbe@430 224 "k" INT4;
jbe@430 225 BEGIN
jbe@430 226 "dimension_v" := array_upper("matrix_d", 1);
jbe@430 227 "matrix_p" := "matrix_d";
jbe@430 228 "i" := 1;
jbe@430 229 LOOP
jbe@430 230 "j" := 1;
jbe@430 231 LOOP
jbe@430 232 IF "i" != "j" THEN
jbe@430 233 "k" := 1;
jbe@430 234 LOOP
jbe@430 235 IF "i" != "k" AND "j" != "k" THEN
jbe@430 236 IF "matrix_p"["j"]["i"] < "matrix_p"["i"]["k"] THEN
jbe@430 237 IF "matrix_p"["j"]["i"] > "matrix_p"["j"]["k"] THEN
jbe@430 238 "matrix_p"["j"]["k"] := "matrix_p"["j"]["i"];
jbe@430 239 END IF;
jbe@430 240 ELSE
jbe@430 241 IF "matrix_p"["i"]["k"] > "matrix_p"["j"]["k"] THEN
jbe@430 242 "matrix_p"["j"]["k"] := "matrix_p"["i"]["k"];
jbe@430 243 END IF;
jbe@430 244 END IF;
jbe@430 245 END IF;
jbe@430 246 EXIT WHEN "k" = "dimension_v";
jbe@430 247 "k" := "k" + 1;
jbe@430 248 END LOOP;
jbe@430 249 END IF;
jbe@430 250 EXIT WHEN "j" = "dimension_v";
jbe@430 251 "j" := "j" + 1;
jbe@430 252 END LOOP;
jbe@430 253 EXIT WHEN "i" = "dimension_v";
jbe@430 254 "i" := "i" + 1;
jbe@430 255 END LOOP;
jbe@430 256 RETURN "matrix_p";
jbe@430 257 END;
jbe@430 258 $$;
jbe@430 259
jbe@430 260 COMMENT ON FUNCTION "find_best_paths"("link_strength"[][]) IS 'Computes the strengths of the best beat-paths from a square matrix';
jbe@430 261
jbe@430 262
jbe@430 263 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
jbe@430 264 RETURNS VOID
jbe@430 265 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@430 266 DECLARE
jbe@430 267 "issue_row" "issue"%ROWTYPE;
jbe@430 268 "policy_row" "policy"%ROWTYPE;
jbe@430 269 "dimension_v" INT4;
jbe@430 270 "matrix_a" INT4[][]; -- absolute votes
jbe@430 271 "matrix_d" "link_strength"[][]; -- defeat strength (direct)
jbe@430 272 "matrix_p" "link_strength"[][]; -- defeat strength (best path)
jbe@430 273 "matrix_t" "link_strength"[][]; -- defeat strength (tie-breaking)
jbe@430 274 "matrix_f" BOOLEAN[][]; -- forbidden link (tie-breaking)
jbe@430 275 "matrix_b" BOOLEAN[][]; -- final order (who beats who)
jbe@430 276 "i" INT4;
jbe@430 277 "j" INT4;
jbe@430 278 "m" INT4;
jbe@430 279 "n" INT4;
jbe@430 280 "battle_row" "battle"%ROWTYPE;
jbe@430 281 "rank_ary" INT4[];
jbe@430 282 "rank_v" INT4;
jbe@430 283 "initiative_id_v" "initiative"."id"%TYPE;
jbe@430 284 BEGIN
jbe@430 285 PERFORM "require_transaction_isolation"();
jbe@430 286 SELECT * INTO "issue_row"
jbe@430 287 FROM "issue" WHERE "id" = "issue_id_p";
jbe@430 288 SELECT * INTO "policy_row"
jbe@430 289 FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@430 290 SELECT count(1) INTO "dimension_v"
jbe@430 291 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
jbe@430 292 -- create "matrix_a" with absolute number of votes in pairwise
jbe@430 293 -- comparison:
jbe@430 294 "matrix_a" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
jbe@430 295 "i" := 1;
jbe@430 296 "j" := 2;
jbe@430 297 FOR "battle_row" IN
jbe@430 298 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
jbe@430 299 ORDER BY
jbe@430 300 "winning_initiative_id" NULLS FIRST,
jbe@430 301 "losing_initiative_id" NULLS FIRST
jbe@430 302 LOOP
jbe@430 303 "matrix_a"["i"]["j"] := "battle_row"."count";
jbe@430 304 IF "j" = "dimension_v" THEN
jbe@430 305 "i" := "i" + 1;
jbe@430 306 "j" := 1;
jbe@430 307 ELSE
jbe@430 308 "j" := "j" + 1;
jbe@430 309 IF "j" = "i" THEN
jbe@430 310 "j" := "j" + 1;
jbe@430 311 END IF;
jbe@430 312 END IF;
jbe@430 313 END LOOP;
jbe@430 314 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
jbe@430 315 RAISE EXCEPTION 'Wrong battle count (should not happen)';
jbe@430 316 END IF;
jbe@430 317 -- store direct defeat strengths in "matrix_d" using "defeat_strength"
jbe@430 318 -- and "secondary_link_strength" functions:
jbe@430 319 "matrix_d" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
jbe@430 320 "i" := 1;
jbe@430 321 LOOP
jbe@430 322 "j" := 1;
jbe@430 323 LOOP
jbe@430 324 IF "i" != "j" THEN
jbe@430 325 "matrix_d"["i"]["j"] := (
jbe@430 326 "defeat_strength"(
jbe@430 327 "matrix_a"["i"]["j"],
jbe@430 328 "matrix_a"["j"]["i"],
jbe@430 329 "policy_row"."defeat_strength"
jbe@430 330 ),
jbe@430 331 "secondary_link_strength"(
jbe@430 332 "i",
jbe@430 333 "j",
jbe@430 334 "policy_row"."tie_breaking"
jbe@430 335 )
jbe@430 336 )::"link_strength";
jbe@430 337 END IF;
jbe@430 338 EXIT WHEN "j" = "dimension_v";
jbe@430 339 "j" := "j" + 1;
jbe@430 340 END LOOP;
jbe@430 341 EXIT WHEN "i" = "dimension_v";
jbe@430 342 "i" := "i" + 1;
jbe@430 343 END LOOP;
jbe@430 344 -- find best paths:
jbe@430 345 "matrix_p" := "find_best_paths"("matrix_d");
jbe@430 346 -- create partial order:
jbe@430 347 "matrix_b" := array_fill(NULL::BOOLEAN, ARRAY["dimension_v", "dimension_v"]);
jbe@430 348 "i" := 1;
jbe@430 349 LOOP
jbe@430 350 "j" := "i" + 1;
jbe@430 351 LOOP
jbe@430 352 IF "i" != "j" THEN
jbe@430 353 IF "matrix_p"["i"]["j"] > "matrix_p"["j"]["i"] THEN
jbe@430 354 "matrix_b"["i"]["j"] := TRUE;
jbe@430 355 "matrix_b"["j"]["i"] := FALSE;
jbe@430 356 ELSIF "matrix_p"["i"]["j"] < "matrix_p"["j"]["i"] THEN
jbe@430 357 "matrix_b"["i"]["j"] := FALSE;
jbe@430 358 "matrix_b"["j"]["i"] := TRUE;
jbe@430 359 END IF;
jbe@430 360 END IF;
jbe@430 361 EXIT WHEN "j" = "dimension_v";
jbe@430 362 "j" := "j" + 1;
jbe@430 363 END LOOP;
jbe@430 364 EXIT WHEN "i" = "dimension_v" - 1;
jbe@430 365 "i" := "i" + 1;
jbe@430 366 END LOOP;
jbe@430 367 -- tie-breaking by forbidding shared weakest links in beat-paths
jbe@430 368 -- (unless "tie_breaking" is set to 'simple', in which case tie-breaking
jbe@430 369 -- is performed later by initiative id):
jbe@430 370 IF "policy_row"."tie_breaking" != 'simple'::"tie_breaking" THEN
jbe@430 371 "m" := 1;
jbe@430 372 LOOP
jbe@430 373 "n" := "m" + 1;
jbe@430 374 LOOP
jbe@430 375 -- only process those candidates m and n, which are tied:
jbe@430 376 IF "matrix_b"["m"]["n"] ISNULL THEN
jbe@430 377 -- start with beat-paths prior tie-breaking:
jbe@430 378 "matrix_t" := "matrix_p";
jbe@430 379 -- start with all links allowed:
jbe@430 380 "matrix_f" := array_fill(FALSE, ARRAY["dimension_v", "dimension_v"]);
jbe@430 381 LOOP
jbe@430 382 -- determine (and forbid) that link that is the weakest link
jbe@430 383 -- in both the best path from candidate m to candidate n and
jbe@430 384 -- from candidate n to candidate m:
jbe@430 385 "i" := 1;
jbe@430 386 <<forbid_one_link>>
jbe@430 387 LOOP
jbe@430 388 "j" := 1;
jbe@430 389 LOOP
jbe@430 390 IF "i" != "j" THEN
jbe@430 391 IF "matrix_d"["i"]["j"] = "matrix_t"["m"]["n"] THEN
jbe@430 392 "matrix_f"["i"]["j"] := TRUE;
jbe@430 393 -- exit for performance reasons,
jbe@430 394 -- as exactly one link will be found:
jbe@430 395 EXIT forbid_one_link;
jbe@430 396 END IF;
jbe@430 397 END IF;
jbe@430 398 EXIT WHEN "j" = "dimension_v";
jbe@430 399 "j" := "j" + 1;
jbe@430 400 END LOOP;
jbe@430 401 IF "i" = "dimension_v" THEN
jbe@430 402 RAISE EXCEPTION 'Did not find shared weakest link for tie-breaking (should not happen)';
jbe@430 403 END IF;
jbe@430 404 "i" := "i" + 1;
jbe@430 405 END LOOP;
jbe@430 406 -- calculate best beat-paths while ignoring forbidden links:
jbe@430 407 "i" := 1;
jbe@430 408 LOOP
jbe@430 409 "j" := 1;
jbe@430 410 LOOP
jbe@430 411 IF "i" != "j" THEN
jbe@430 412 "matrix_t"["i"]["j"] := CASE
jbe@430 413 WHEN "matrix_f"["i"]["j"]
jbe@432 414 THEN ((-1::INT8) << 63, 0)::"link_strength" -- worst possible value
jbe@430 415 ELSE "matrix_d"["i"]["j"] END;
jbe@430 416 END IF;
jbe@430 417 EXIT WHEN "j" = "dimension_v";
jbe@430 418 "j" := "j" + 1;
jbe@430 419 END LOOP;
jbe@430 420 EXIT WHEN "i" = "dimension_v";
jbe@430 421 "i" := "i" + 1;
jbe@430 422 END LOOP;
jbe@430 423 "matrix_t" := "find_best_paths"("matrix_t");
jbe@430 424 -- extend partial order, if tie-breaking was successful:
jbe@430 425 IF "matrix_t"["m"]["n"] > "matrix_t"["n"]["m"] THEN
jbe@430 426 "matrix_b"["m"]["n"] := TRUE;
jbe@430 427 "matrix_b"["n"]["m"] := FALSE;
jbe@430 428 EXIT;
jbe@430 429 ELSIF "matrix_t"["m"]["n"] < "matrix_t"["n"]["m"] THEN
jbe@430 430 "matrix_b"["m"]["n"] := FALSE;
jbe@430 431 "matrix_b"["n"]["m"] := TRUE;
jbe@430 432 EXIT;
jbe@430 433 END IF;
jbe@430 434 END LOOP;
jbe@430 435 END IF;
jbe@430 436 EXIT WHEN "n" = "dimension_v";
jbe@430 437 "n" := "n" + 1;
jbe@430 438 END LOOP;
jbe@430 439 EXIT WHEN "m" = "dimension_v" - 1;
jbe@430 440 "m" := "m" + 1;
jbe@430 441 END LOOP;
jbe@430 442 END IF;
jbe@430 443 -- store a unique ranking in "rank_ary":
jbe@430 444 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
jbe@430 445 "rank_v" := 1;
jbe@430 446 LOOP
jbe@430 447 "i" := 1;
jbe@430 448 <<assign_next_rank>>
jbe@430 449 LOOP
jbe@430 450 IF "rank_ary"["i"] ISNULL THEN
jbe@430 451 "j" := 1;
jbe@430 452 LOOP
jbe@430 453 IF
jbe@430 454 "i" != "j" AND
jbe@430 455 "rank_ary"["j"] ISNULL AND
jbe@430 456 ( "matrix_b"["j"]["i"] OR
jbe@430 457 -- tie-breaking by "id"
jbe@430 458 ( "matrix_b"["j"]["i"] ISNULL AND
jbe@430 459 "j" < "i" ) )
jbe@430 460 THEN
jbe@430 461 -- someone else is better
jbe@430 462 EXIT;
jbe@430 463 END IF;
jbe@430 464 IF "j" = "dimension_v" THEN
jbe@430 465 -- noone is better
jbe@430 466 "rank_ary"["i"] := "rank_v";
jbe@430 467 EXIT assign_next_rank;
jbe@430 468 END IF;
jbe@430 469 "j" := "j" + 1;
jbe@430 470 END LOOP;
jbe@430 471 END IF;
jbe@430 472 "i" := "i" + 1;
jbe@430 473 IF "i" > "dimension_v" THEN
jbe@430 474 RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
jbe@430 475 END IF;
jbe@430 476 END LOOP;
jbe@430 477 EXIT WHEN "rank_v" = "dimension_v";
jbe@430 478 "rank_v" := "rank_v" + 1;
jbe@430 479 END LOOP;
jbe@430 480 -- write preliminary results:
jbe@430 481 "i" := 2; -- omit status quo with "i" = 1
jbe@430 482 FOR "initiative_id_v" IN
jbe@430 483 SELECT "id" FROM "initiative"
jbe@430 484 WHERE "issue_id" = "issue_id_p" AND "admitted"
jbe@430 485 ORDER BY "id"
jbe@430 486 LOOP
jbe@430 487 UPDATE "initiative" SET
jbe@430 488 "direct_majority" =
jbe@430 489 CASE WHEN "policy_row"."direct_majority_strict" THEN
jbe@430 490 "positive_votes" * "policy_row"."direct_majority_den" >
jbe@430 491 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
jbe@430 492 ELSE
jbe@430 493 "positive_votes" * "policy_row"."direct_majority_den" >=
jbe@430 494 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
jbe@430 495 END
jbe@430 496 AND "positive_votes" >= "policy_row"."direct_majority_positive"
jbe@430 497 AND "issue_row"."voter_count"-"negative_votes" >=
jbe@430 498 "policy_row"."direct_majority_non_negative",
jbe@430 499 "indirect_majority" =
jbe@430 500 CASE WHEN "policy_row"."indirect_majority_strict" THEN
jbe@430 501 "positive_votes" * "policy_row"."indirect_majority_den" >
jbe@430 502 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
jbe@430 503 ELSE
jbe@430 504 "positive_votes" * "policy_row"."indirect_majority_den" >=
jbe@430 505 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
jbe@430 506 END
jbe@430 507 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
jbe@430 508 AND "issue_row"."voter_count"-"negative_votes" >=
jbe@430 509 "policy_row"."indirect_majority_non_negative",
jbe@430 510 "schulze_rank" = "rank_ary"["i"],
jbe@430 511 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
jbe@430 512 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"[1],
jbe@430 513 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"[1],
jbe@430 514 "reverse_beat_path" = CASE WHEN "policy_row"."defeat_strength" = 'simple'::"defeat_strength"
jbe@430 515 THEN NULL
jbe@430 516 ELSE "matrix_p"[1]["i"]."primary" >= 0 END,
jbe@430 517 "eligible" = FALSE,
jbe@430 518 "winner" = FALSE,
jbe@430 519 "rank" = NULL -- NOTE: in cases of manual reset of issue state
jbe@430 520 WHERE "id" = "initiative_id_v";
jbe@430 521 "i" := "i" + 1;
jbe@430 522 END LOOP;
jbe@430 523 IF "i" != "dimension_v" + 1 THEN
jbe@430 524 RAISE EXCEPTION 'Wrong winner count (should not happen)';
jbe@430 525 END IF;
jbe@430 526 -- take indirect majorities into account:
jbe@430 527 LOOP
jbe@430 528 UPDATE "initiative" SET "indirect_majority" = TRUE
jbe@430 529 FROM (
jbe@430 530 SELECT "new_initiative"."id" AS "initiative_id"
jbe@430 531 FROM "initiative" "old_initiative"
jbe@430 532 JOIN "initiative" "new_initiative"
jbe@430 533 ON "new_initiative"."issue_id" = "issue_id_p"
jbe@430 534 AND "new_initiative"."indirect_majority" = FALSE
jbe@430 535 JOIN "battle" "battle_win"
jbe@430 536 ON "battle_win"."issue_id" = "issue_id_p"
jbe@430 537 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
jbe@430 538 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
jbe@430 539 JOIN "battle" "battle_lose"
jbe@430 540 ON "battle_lose"."issue_id" = "issue_id_p"
jbe@430 541 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
jbe@430 542 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
jbe@430 543 WHERE "old_initiative"."issue_id" = "issue_id_p"
jbe@430 544 AND "old_initiative"."indirect_majority" = TRUE
jbe@430 545 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
jbe@430 546 "battle_win"."count" * "policy_row"."indirect_majority_den" >
jbe@430 547 "policy_row"."indirect_majority_num" *
jbe@430 548 ("battle_win"."count"+"battle_lose"."count")
jbe@430 549 ELSE
jbe@430 550 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
jbe@430 551 "policy_row"."indirect_majority_num" *
jbe@430 552 ("battle_win"."count"+"battle_lose"."count")
jbe@430 553 END
jbe@430 554 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
jbe@430 555 AND "issue_row"."voter_count"-"battle_lose"."count" >=
jbe@430 556 "policy_row"."indirect_majority_non_negative"
jbe@430 557 ) AS "subquery"
jbe@430 558 WHERE "id" = "subquery"."initiative_id";
jbe@430 559 EXIT WHEN NOT FOUND;
jbe@430 560 END LOOP;
jbe@430 561 -- set "multistage_majority" for remaining matching initiatives:
jbe@430 562 UPDATE "initiative" SET "multistage_majority" = TRUE
jbe@430 563 FROM (
jbe@430 564 SELECT "losing_initiative"."id" AS "initiative_id"
jbe@430 565 FROM "initiative" "losing_initiative"
jbe@430 566 JOIN "initiative" "winning_initiative"
jbe@430 567 ON "winning_initiative"."issue_id" = "issue_id_p"
jbe@430 568 AND "winning_initiative"."admitted"
jbe@430 569 JOIN "battle" "battle_win"
jbe@430 570 ON "battle_win"."issue_id" = "issue_id_p"
jbe@430 571 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
jbe@430 572 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
jbe@430 573 JOIN "battle" "battle_lose"
jbe@430 574 ON "battle_lose"."issue_id" = "issue_id_p"
jbe@430 575 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
jbe@430 576 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
jbe@430 577 WHERE "losing_initiative"."issue_id" = "issue_id_p"
jbe@430 578 AND "losing_initiative"."admitted"
jbe@430 579 AND "winning_initiative"."schulze_rank" <
jbe@430 580 "losing_initiative"."schulze_rank"
jbe@430 581 AND "battle_win"."count" > "battle_lose"."count"
jbe@430 582 AND (
jbe@430 583 "battle_win"."count" > "winning_initiative"."positive_votes" OR
jbe@430 584 "battle_lose"."count" < "losing_initiative"."negative_votes" )
jbe@430 585 ) AS "subquery"
jbe@430 586 WHERE "id" = "subquery"."initiative_id";
jbe@430 587 -- mark eligible initiatives:
jbe@430 588 UPDATE "initiative" SET "eligible" = TRUE
jbe@430 589 WHERE "issue_id" = "issue_id_p"
jbe@430 590 AND "initiative"."direct_majority"
jbe@430 591 AND "initiative"."indirect_majority"
jbe@430 592 AND "initiative"."better_than_status_quo"
jbe@430 593 AND (
jbe@430 594 "policy_row"."no_multistage_majority" = FALSE OR
jbe@430 595 "initiative"."multistage_majority" = FALSE )
jbe@430 596 AND (
jbe@430 597 "policy_row"."no_reverse_beat_path" = FALSE OR
jbe@430 598 coalesce("initiative"."reverse_beat_path", FALSE) = FALSE );
jbe@430 599 -- mark final winner:
jbe@430 600 UPDATE "initiative" SET "winner" = TRUE
jbe@430 601 FROM (
jbe@430 602 SELECT "id" AS "initiative_id"
jbe@430 603 FROM "initiative"
jbe@430 604 WHERE "issue_id" = "issue_id_p" AND "eligible"
jbe@430 605 ORDER BY
jbe@430 606 "schulze_rank",
jbe@430 607 "id"
jbe@430 608 LIMIT 1
jbe@430 609 ) AS "subquery"
jbe@430 610 WHERE "id" = "subquery"."initiative_id";
jbe@430 611 -- write (final) ranks:
jbe@430 612 "rank_v" := 1;
jbe@430 613 FOR "initiative_id_v" IN
jbe@430 614 SELECT "id"
jbe@430 615 FROM "initiative"
jbe@430 616 WHERE "issue_id" = "issue_id_p" AND "admitted"
jbe@430 617 ORDER BY
jbe@430 618 "winner" DESC,
jbe@430 619 "eligible" DESC,
jbe@430 620 "schulze_rank",
jbe@430 621 "id"
jbe@430 622 LOOP
jbe@430 623 UPDATE "initiative" SET "rank" = "rank_v"
jbe@430 624 WHERE "id" = "initiative_id_v";
jbe@430 625 "rank_v" := "rank_v" + 1;
jbe@430 626 END LOOP;
jbe@430 627 -- set schulze rank of status quo and mark issue as finished:
jbe@430 628 UPDATE "issue" SET
jbe@430 629 "status_quo_schulze_rank" = "rank_ary"[1],
jbe@430 630 "state" =
jbe@430 631 CASE WHEN EXISTS (
jbe@430 632 SELECT NULL FROM "initiative"
jbe@430 633 WHERE "issue_id" = "issue_id_p" AND "winner"
jbe@430 634 ) THEN
jbe@430 635 'finished_with_winner'::"issue_state"
jbe@430 636 ELSE
jbe@430 637 'finished_without_winner'::"issue_state"
jbe@430 638 END,
jbe@430 639 "closed" = "phase_finished",
jbe@430 640 "phase_finished" = NULL
jbe@430 641 WHERE "id" = "issue_id_p";
jbe@430 642 RETURN;
jbe@430 643 END;
jbe@430 644 $$;
jbe@430 645
jbe@423 646
jbe@423 647 COMMIT;

Impressum / About Us