liquid_feedback_core

annotate update/core-update.v2.2.6-v3.0.1.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 34cc98defa8b
children
rev   line source
jbe@396 1 BEGIN;
jbe@396 2
jbe@396 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@420 4 SELECT * FROM (VALUES ('3.0.1', 3, 0, 1))
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@436 339 ALTER TABLE "initiative" ADD COLUMN "first_preference_votes" INT4;
jbe@436 340
jbe@436 341 ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results";
jbe@436 342 ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
jbe@436 343 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
jbe@436 344 ( "first_preference_votes" ISNULL AND
jbe@436 345 "positive_votes" ISNULL AND "negative_votes" ISNULL AND
jbe@436 346 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
jbe@436 347 "schulze_rank" ISNULL AND
jbe@436 348 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
jbe@436 349 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
jbe@436 350 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) );
jbe@436 351
jbe@436 352 COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice';
jbe@436 353 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Number of direct and delegating voters who ranked this initiative better than the status quo';
jbe@436 354 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo';
jbe@436 355
jbe@436 356 -- UPDATE TABLE "vote" SET "grade" = 0 WHERE "grade" ISNULL; -- should not be necessary
jbe@436 357 ALTER TABLE "vote" ALTER COLUMN "grade" SET NOT NULL;
jbe@436 358
jbe@436 359 ALTER TABLE "vote" ADD COLUMN "first_preference" BOOLEAN;
jbe@436 360
jbe@436 361 ALTER TABLE "vote" ADD
jbe@436 362 CONSTRAINT "first_preference_flag_only_set_on_positive_grades"
jbe@436 363 CHECK ("grade" > 0 OR "first_preference" ISNULL);
jbe@436 364
jbe@436 365 COMMENT ON COLUMN "vote"."first_preference" IS 'Value is automatically set after voting is finished. For positive grades, this value is set to true for the highest (i.e. best) grade.';
jbe@436 366
jbe@436 367 INSERT INTO "temporary_transaction_data" ("key", "value")
jbe@436 368 VALUES ('override_protection_triggers', TRUE::TEXT);
jbe@436 369
jbe@436 370 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
jbe@436 371 FROM (
jbe@436 372 SELECT
jbe@436 373 "vote"."initiative_id",
jbe@436 374 "vote"."member_id",
jbe@436 375 CASE WHEN "vote"."grade" > 0 THEN
jbe@436 376 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
jbe@436 377 ELSE NULL
jbe@436 378 END AS "first_preference"
jbe@436 379 FROM "vote"
jbe@436 380 JOIN "initiative" -- NOTE: due to missing index on issue_id
jbe@436 381 ON "vote"."issue_id" = "initiative"."issue_id"
jbe@436 382 JOIN "vote" AS "agg"
jbe@436 383 ON "initiative"."id" = "agg"."initiative_id"
jbe@436 384 AND "vote"."member_id" = "agg"."member_id"
jbe@436 385 GROUP BY "vote"."initiative_id", "vote"."member_id"
jbe@436 386 ) AS "subquery"
jbe@436 387 WHERE "vote"."initiative_id" = "subquery"."initiative_id"
jbe@436 388 AND "vote"."member_id" = "subquery"."member_id";
jbe@436 389
jbe@436 390 DELETE FROM "temporary_transaction_data"
jbe@436 391 WHERE "key" = 'override_protection_triggers';
jbe@436 392
jbe@436 393 UPDATE "initiative"
jbe@436 394 SET "first_preference_votes" = coalesce("subquery"."sum", 0)
jbe@436 395 FROM (
jbe@436 396 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
jbe@436 397 FROM "vote" JOIN "direct_voter"
jbe@436 398 ON "vote"."issue_id" = "direct_voter"."issue_id"
jbe@436 399 AND "vote"."member_id" = "direct_voter"."member_id"
jbe@436 400 WHERE "vote"."first_preference"
jbe@436 401 GROUP BY "vote"."initiative_id"
jbe@436 402 ) AS "subquery"
jbe@436 403 WHERE "initiative"."admitted"
jbe@436 404 AND "initiative"."id" = "subquery"."initiative_id";
jbe@436 405
jbe@436 406 -- reconstruct battle data (originating from LiquidFeedback Core before v2.0.0)
jbe@436 407 -- to avoid future data loss when executing "clean_issue" to delete voting data:
jbe@436 408 INSERT INTO "battle" (
jbe@436 409 "issue_id",
jbe@436 410 "winning_initiative_id",
jbe@436 411 "losing_initiative_id",
jbe@436 412 "count"
jbe@436 413 ) SELECT
jbe@436 414 "battle_view"."issue_id",
jbe@436 415 "battle_view"."winning_initiative_id",
jbe@436 416 "battle_view"."losing_initiative_id",
jbe@436 417 "battle_view"."count"
jbe@436 418 FROM (
jbe@436 419 SELECT
jbe@436 420 "issue"."id" AS "issue_id",
jbe@436 421 "winning_initiative"."id" AS "winning_initiative_id",
jbe@436 422 "losing_initiative"."id" AS "losing_initiative_id",
jbe@436 423 sum(
jbe@436 424 CASE WHEN
jbe@436 425 coalesce("better_vote"."grade", 0) >
jbe@436 426 coalesce("worse_vote"."grade", 0)
jbe@436 427 THEN "direct_voter"."weight" ELSE 0 END
jbe@436 428 ) AS "count"
jbe@436 429 FROM "issue"
jbe@436 430 LEFT JOIN "direct_voter"
jbe@436 431 ON "issue"."id" = "direct_voter"."issue_id"
jbe@436 432 JOIN "battle_participant" AS "winning_initiative"
jbe@436 433 ON "issue"."id" = "winning_initiative"."issue_id"
jbe@436 434 JOIN "battle_participant" AS "losing_initiative"
jbe@436 435 ON "issue"."id" = "losing_initiative"."issue_id"
jbe@436 436 LEFT JOIN "vote" AS "better_vote"
jbe@436 437 ON "direct_voter"."member_id" = "better_vote"."member_id"
jbe@436 438 AND "winning_initiative"."id" = "better_vote"."initiative_id"
jbe@436 439 LEFT JOIN "vote" AS "worse_vote"
jbe@436 440 ON "direct_voter"."member_id" = "worse_vote"."member_id"
jbe@436 441 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
jbe@436 442 WHERE "issue"."state" IN ('finished_with_winner', 'finished_without_winner')
jbe@436 443 AND "winning_initiative"."id" != "losing_initiative"."id"
jbe@436 444 -- NOTE: comparisons with status-quo are intentionally omitted to mark
jbe@436 445 -- issues that were counted prior LiquidFeedback Core v2.0.0
jbe@436 446 GROUP BY
jbe@436 447 "issue"."id",
jbe@436 448 "winning_initiative"."id",
jbe@436 449 "losing_initiative"."id"
jbe@436 450 ) AS "battle_view"
jbe@436 451 LEFT JOIN "battle"
jbe@436 452 ON "battle_view"."winning_initiative_id" = "battle"."winning_initiative_id"
jbe@436 453 AND "battle_view"."losing_initiative_id" = "battle"."losing_initiative_id"
jbe@436 454 WHERE "battle" ISNULL;
jbe@436 455
jbe@436 456 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@436 457 RETURNS VOID
jbe@436 458 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@436 459 DECLARE
jbe@436 460 "area_id_v" "area"."id"%TYPE;
jbe@436 461 "unit_id_v" "unit"."id"%TYPE;
jbe@436 462 "member_id_v" "member"."id"%TYPE;
jbe@436 463 BEGIN
jbe@436 464 PERFORM "require_transaction_isolation"();
jbe@436 465 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@436 466 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
jbe@436 467 -- override protection triggers:
jbe@436 468 INSERT INTO "temporary_transaction_data" ("key", "value")
jbe@436 469 VALUES ('override_protection_triggers', TRUE::TEXT);
jbe@436 470 -- delete timestamp of voting comment:
jbe@436 471 UPDATE "direct_voter" SET "comment_changed" = NULL
jbe@436 472 WHERE "issue_id" = "issue_id_p";
jbe@436 473 -- delete delegating votes (in cases of manual reset of issue state):
jbe@436 474 DELETE FROM "delegating_voter"
jbe@436 475 WHERE "issue_id" = "issue_id_p";
jbe@436 476 -- delete votes from non-privileged voters:
jbe@436 477 DELETE FROM "direct_voter"
jbe@436 478 USING (
jbe@436 479 SELECT
jbe@436 480 "direct_voter"."member_id"
jbe@436 481 FROM "direct_voter"
jbe@436 482 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@436 483 LEFT JOIN "privilege"
jbe@436 484 ON "privilege"."unit_id" = "unit_id_v"
jbe@436 485 AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@436 486 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@436 487 "member"."active" = FALSE OR
jbe@436 488 "privilege"."voting_right" ISNULL OR
jbe@436 489 "privilege"."voting_right" = FALSE
jbe@436 490 )
jbe@436 491 ) AS "subquery"
jbe@436 492 WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@436 493 AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@436 494 -- consider delegations:
jbe@436 495 UPDATE "direct_voter" SET "weight" = 1
jbe@436 496 WHERE "issue_id" = "issue_id_p";
jbe@436 497 PERFORM "add_vote_delegations"("issue_id_p");
jbe@436 498 -- mark first preferences:
jbe@436 499 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
jbe@436 500 FROM (
jbe@436 501 SELECT
jbe@436 502 "vote"."initiative_id",
jbe@436 503 "vote"."member_id",
jbe@436 504 CASE WHEN "vote"."grade" > 0 THEN
jbe@436 505 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
jbe@436 506 ELSE NULL
jbe@436 507 END AS "first_preference"
jbe@436 508 FROM "vote"
jbe@436 509 JOIN "initiative" -- NOTE: due to missing index on issue_id
jbe@436 510 ON "vote"."issue_id" = "initiative"."issue_id"
jbe@436 511 JOIN "vote" AS "agg"
jbe@436 512 ON "initiative"."id" = "agg"."initiative_id"
jbe@436 513 AND "vote"."member_id" = "agg"."member_id"
jbe@436 514 GROUP BY "vote"."initiative_id", "vote"."member_id"
jbe@436 515 ) AS "subquery"
jbe@436 516 WHERE "vote"."issue_id" = "issue_id_p"
jbe@436 517 AND "vote"."initiative_id" = "subquery"."initiative_id"
jbe@436 518 AND "vote"."member_id" = "subquery"."member_id";
jbe@436 519 -- finish overriding protection triggers (avoids garbage):
jbe@436 520 DELETE FROM "temporary_transaction_data"
jbe@436 521 WHERE "key" = 'override_protection_triggers';
jbe@436 522 -- materialize battle_view:
jbe@436 523 -- NOTE: "closed" column of issue must be set at this point
jbe@436 524 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@436 525 INSERT INTO "battle" (
jbe@436 526 "issue_id",
jbe@436 527 "winning_initiative_id", "losing_initiative_id",
jbe@436 528 "count"
jbe@436 529 ) SELECT
jbe@436 530 "issue_id",
jbe@436 531 "winning_initiative_id", "losing_initiative_id",
jbe@436 532 "count"
jbe@436 533 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@436 534 -- set voter count:
jbe@436 535 UPDATE "issue" SET
jbe@436 536 "voter_count" = (
jbe@436 537 SELECT coalesce(sum("weight"), 0)
jbe@436 538 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@436 539 )
jbe@436 540 WHERE "id" = "issue_id_p";
jbe@436 541 -- calculate "first_preference_votes":
jbe@436 542 UPDATE "initiative"
jbe@436 543 SET "first_preference_votes" = coalesce("subquery"."sum", 0)
jbe@436 544 FROM (
jbe@436 545 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
jbe@436 546 FROM "vote" JOIN "direct_voter"
jbe@436 547 ON "vote"."issue_id" = "direct_voter"."issue_id"
jbe@436 548 AND "vote"."member_id" = "direct_voter"."member_id"
jbe@436 549 WHERE "vote"."first_preference"
jbe@436 550 GROUP BY "vote"."initiative_id"
jbe@436 551 ) AS "subquery"
jbe@436 552 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@436 553 AND "initiative"."admitted"
jbe@436 554 AND "initiative"."id" = "subquery"."initiative_id";
jbe@436 555 -- copy "positive_votes" and "negative_votes" from "battle" table:
jbe@436 556 UPDATE "initiative" SET
jbe@436 557 "positive_votes" = "battle_win"."count",
jbe@436 558 "negative_votes" = "battle_lose"."count"
jbe@436 559 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
jbe@436 560 WHERE
jbe@436 561 "battle_win"."issue_id" = "issue_id_p" AND
jbe@436 562 "battle_win"."winning_initiative_id" = "initiative"."id" AND
jbe@436 563 "battle_win"."losing_initiative_id" ISNULL AND
jbe@436 564 "battle_lose"."issue_id" = "issue_id_p" AND
jbe@436 565 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
jbe@436 566 "battle_lose"."winning_initiative_id" ISNULL;
jbe@436 567 END;
jbe@436 568 $$;
jbe@436 569
jbe@396 570 COMMIT;

Impressum / About Us