liquid_feedback_core
annotate update/core-update.v3.0.1-v3.0.2.sql @ 532:5855ff9e5c8f
Several changes/additions for upcoming major release
- OAuth 2.0 support
- storing profiles as JSON document
- removed subject area membership
- revised snapshot system
- additional issue limiter (dynamic quorum in subject area)
- extended event logging in "event" table
- OAuth 2.0 support
- storing profiles as JSON document
- removed subject area membership
- revised snapshot system
- additional issue limiter (dynamic quorum in subject area)
- extended event logging in "event" table
author | jbe |
---|---|
date | Thu Mar 30 19:42:38 2017 +0200 (2017-03-30) |
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; |