liquid_feedback_core

annotate update/core-update.v2.2.6-v3.0.1.sql @ 467:1e7e8b025346

find changesets by author, revision, files, or words in the commit message
Stub VIEW that selects issues which are to be included in notifications
author jbe
date Sat Mar 26 02:13:42 2016 +0100 (2016-03-26)
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