liquid_feedback_core

annotate update/core-update.v4.2.0-v4.2.1.sql @ 625:7f488033d5ee

find changesets by author, revision, files, or words in the commit message
Fixed mistake in last-level tie-breaking
author jbe
date Wed Jan 01 16:49:21 2025 +0100 (10 months ago)
parents 63092784fe9d
children
rev   line source
jbe@619 1 BEGIN;
jbe@619 2
jbe@615 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@619 4 SELECT * FROM (VALUES ('4.2.1', 4, 2, 1))
jbe@615 5 AS "subquery"("string", "major", "minor", "revision");
jbe@615 6
jbe@616 7 ALTER TABLE "unit" ADD COLUMN "attr" JSONB NOT NULL DEFAULT '{}' CHECK (jsonb_typeof("attr") = 'object');
jbe@616 8 COMMENT ON COLUMN "unit"."attr" IS 'Opaque data structure to store any extended attributes used by frontend or middleware';
jbe@616 9
jbe@615 10 ALTER TABLE "unit" ADD COLUMN "member_weight" INT4;
jbe@615 11 COMMENT ON COLUMN "unit"."member_weight" IS 'Sum of active members'' voting weight';
jbe@615 12
jbe@615 13 ALTER TABLE "snapshot_population" ADD COLUMN "weight" INT4 NOT NULL DEFAULT 1;
jbe@615 14 ALTER TABLE "snapshot_population" ALTER COLUMN "weight" DROP DEFAULT;
jbe@615 15
jbe@615 16 ALTER TABLE "privilege" ADD COLUMN "weight" INT4 NOT NULL DEFAULT 1 CHECK ("weight" >= 0);
jbe@615 17 COMMENT ON COLUMN "privilege"."weight" IS 'Voting weight of member in unit';
jbe@615 18
jbe@615 19 CREATE TABLE "issue_privilege" (
jbe@615 20 PRIMARY KEY ("issue_id", "member_id"),
jbe@615 21 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@615 22 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@615 23 "initiative_right" BOOLEAN,
jbe@615 24 "voting_right" BOOLEAN,
jbe@615 25 "polling_right" BOOLEAN,
jbe@615 26 "weight" INT4 CHECK ("weight" >= 0) );
jbe@615 27 CREATE INDEX "issue_privilege_idx" ON "issue_privilege" ("member_id");
jbe@615 28 COMMENT ON TABLE "issue_privilege" IS 'Override of "privilege" table for rights of members in certain issues';
jbe@615 29
jbe@615 30 ALTER TABLE "direct_interest_snapshot" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1;
jbe@615 31 ALTER TABLE "direct_interest_snapshot" ALTER COLUMN "ownweight" DROP DEFAULT;
jbe@615 32 COMMENT ON COLUMN "direct_interest_snapshot"."ownweight" IS 'Own voting weight of member, disregading delegations';
jbe@615 33 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Voting weight of member according to own weight and "delegating_interest_snapshot"';
jbe@615 34
jbe@615 35 ALTER TABLE "delegating_interest_snapshot" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1;
jbe@615 36 ALTER TABLE "delegating_interest_snapshot" ALTER COLUMN "ownweight" DROP DEFAULT;
jbe@615 37 COMMENT ON COLUMN "delegating_interest_snapshot"."ownweight" IS 'Own voting weight of member, disregading delegations';
jbe@615 38 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate voting weight considering incoming delegations';
jbe@615 39
jbe@615 40 ALTER TABLE "direct_voter" ADD COLUMN "ownweight" INT4 DEFAULT 1;
jbe@615 41 ALTER TABLE "direct_voter" ALTER COLUMN "ownweight" DROP DEFAULT;
jbe@615 42 COMMENT ON COLUMN "direct_voter"."ownweight" IS 'Own voting weight of member, disregarding delegations';
jbe@615 43 COMMENT ON COLUMN "direct_voter"."weight" IS 'Voting weight of member according to own weight and "delegating_interest_snapshot"';
jbe@615 44
jbe@615 45 ALTER TABLE "delegating_voter" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1;
jbe@615 46 ALTER TABLE "delegating_voter" ALTER COLUMN "ownweight" DROP DEFAULT;
jbe@615 47 COMMENT ON COLUMN "delegating_voter"."ownweight" IS 'Own voting weight of member, disregarding delegations';
jbe@615 48 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate voting weight considering incoming delegations';
jbe@615 49
jbe@618 50 ALTER TABLE "posting" ADD FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id");
jbe@618 51
jbe@615 52 DROP VIEW "issue_delegation";
jbe@615 53 CREATE VIEW "issue_delegation" AS
jbe@615 54 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
jbe@615 55 "issue"."id" AS "issue_id",
jbe@615 56 "delegation"."id",
jbe@615 57 "delegation"."truster_id",
jbe@615 58 "delegation"."trustee_id",
jbe@615 59 COALESCE("issue_privilege"."weight", "privilege"."weight") AS "weight",
jbe@615 60 "delegation"."scope"
jbe@615 61 FROM "issue"
jbe@615 62 JOIN "area"
jbe@615 63 ON "area"."id" = "issue"."area_id"
jbe@615 64 JOIN "delegation"
jbe@615 65 ON "delegation"."unit_id" = "area"."unit_id"
jbe@615 66 OR "delegation"."area_id" = "area"."id"
jbe@615 67 OR "delegation"."issue_id" = "issue"."id"
jbe@615 68 JOIN "member"
jbe@615 69 ON "delegation"."truster_id" = "member"."id"
jbe@615 70 LEFT JOIN "privilege"
jbe@615 71 ON "area"."unit_id" = "privilege"."unit_id"
jbe@615 72 AND "delegation"."truster_id" = "privilege"."member_id"
jbe@615 73 LEFT JOIN "issue_privilege"
jbe@615 74 ON "issue"."id" = "issue_privilege"."issue_id"
jbe@615 75 AND "delegation"."truster_id" = "issue_privilege"."member_id"
jbe@615 76 WHERE "member"."active"
jbe@615 77 AND COALESCE("issue_privilege"."voting_right", "privilege"."voting_right")
jbe@615 78 ORDER BY
jbe@615 79 "issue"."id",
jbe@615 80 "delegation"."truster_id",
jbe@615 81 "delegation"."scope" DESC;
jbe@615 82 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
jbe@615 83
jbe@615 84 CREATE OR REPLACE VIEW "unit_member" AS
jbe@615 85 SELECT
jbe@615 86 "privilege"."unit_id" AS "unit_id",
jbe@615 87 "member"."id" AS "member_id",
jbe@615 88 "privilege"."weight"
jbe@615 89 FROM "privilege" JOIN "member" ON "member"."id" = "privilege"."member_id"
jbe@615 90 WHERE "privilege"."voting_right" AND "member"."active";
jbe@615 91
jbe@615 92 CREATE OR REPLACE VIEW "unit_member_count" AS
jbe@615 93 SELECT
jbe@615 94 "unit"."id" AS "unit_id",
jbe@615 95 count("unit_member"."member_id") AS "member_count",
jbe@615 96 sum("unit_member"."weight") AS "member_weight"
jbe@615 97 FROM "unit" LEFT JOIN "unit_member"
jbe@615 98 ON "unit"."id" = "unit_member"."unit_id"
jbe@615 99 GROUP BY "unit"."id";
jbe@615 100
jbe@615 101 CREATE OR REPLACE VIEW "event_for_notification" AS
jbe@615 102 SELECT
jbe@615 103 "member"."id" AS "recipient_id",
jbe@615 104 "event".*
jbe@615 105 FROM "member" CROSS JOIN "event"
jbe@615 106 JOIN "issue" ON "issue"."id" = "event"."issue_id"
jbe@615 107 JOIN "area" ON "area"."id" = "issue"."area_id"
jbe@615 108 LEFT JOIN "privilege" ON
jbe@615 109 "privilege"."member_id" = "member"."id" AND
jbe@615 110 "privilege"."unit_id" = "area"."unit_id"
jbe@615 111 LEFT JOIN "issue_privilege" ON
jbe@615 112 "issue_privilege"."member_id" = "member"."id" AND
jbe@615 113 "issue_privilege"."issue_id" = "event"."issue_id"
jbe@615 114 LEFT JOIN "subscription" ON
jbe@615 115 "subscription"."member_id" = "member"."id" AND
jbe@615 116 "subscription"."unit_id" = "area"."unit_id"
jbe@615 117 LEFT JOIN "ignored_area" ON
jbe@615 118 "ignored_area"."member_id" = "member"."id" AND
jbe@615 119 "ignored_area"."area_id" = "issue"."area_id"
jbe@615 120 LEFT JOIN "interest" ON
jbe@615 121 "interest"."member_id" = "member"."id" AND
jbe@615 122 "interest"."issue_id" = "event"."issue_id"
jbe@615 123 LEFT JOIN "supporter" ON
jbe@615 124 "supporter"."member_id" = "member"."id" AND
jbe@615 125 "supporter"."initiative_id" = "event"."initiative_id"
jbe@615 126 WHERE (
jbe@615 127 COALESCE("issue_privilege"."voting_right", "privilege"."voting_right") OR
jbe@615 128 "subscription"."member_id" NOTNULL
jbe@615 129 ) AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
jbe@615 130 AND (
jbe@615 131 "event"."event" = 'issue_state_changed'::"event_type" OR
jbe@615 132 ( "event"."event" = 'initiative_revoked'::"event_type" AND
jbe@615 133 "supporter"."member_id" NOTNULL ) );
jbe@615 134
jbe@615 135 CREATE OR REPLACE FUNCTION "featured_initiative"
jbe@615 136 ( "recipient_id_p" "member"."id"%TYPE,
jbe@615 137 "area_id_p" "area"."id"%TYPE )
jbe@615 138 RETURNS SETOF "initiative"."id"%TYPE
jbe@615 139 LANGUAGE 'plpgsql' STABLE AS $$
jbe@615 140 DECLARE
jbe@615 141 "counter_v" "member"."notification_counter"%TYPE;
jbe@615 142 "sample_size_v" "member"."notification_sample_size"%TYPE;
jbe@615 143 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
jbe@615 144 "match_v" BOOLEAN;
jbe@615 145 "member_id_v" "member"."id"%TYPE;
jbe@615 146 "seed_v" TEXT;
jbe@615 147 "initiative_id_v" "initiative"."id"%TYPE;
jbe@615 148 BEGIN
jbe@615 149 SELECT "notification_counter", "notification_sample_size"
jbe@615 150 INTO "counter_v", "sample_size_v"
jbe@615 151 FROM "member" WHERE "id" = "recipient_id_p";
jbe@615 152 IF COALESCE("sample_size_v" <= 0, TRUE) THEN
jbe@615 153 RETURN;
jbe@615 154 END IF;
jbe@615 155 "initiative_id_ary" := '{}';
jbe@615 156 LOOP
jbe@615 157 "match_v" := FALSE;
jbe@615 158 FOR "member_id_v", "seed_v" IN
jbe@615 159 SELECT * FROM (
jbe@615 160 SELECT DISTINCT
jbe@615 161 "supporter"."member_id",
jbe@615 162 md5(
jbe@615 163 "recipient_id_p" || '-' ||
jbe@615 164 "counter_v" || '-' ||
jbe@615 165 "area_id_p" || '-' ||
jbe@615 166 "supporter"."member_id"
jbe@615 167 ) AS "seed"
jbe@615 168 FROM "supporter"
jbe@615 169 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
jbe@615 170 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@615 171 WHERE "supporter"."member_id" != "recipient_id_p"
jbe@615 172 AND "issue"."area_id" = "area_id_p"
jbe@615 173 AND "issue"."state" IN ('admission', 'discussion', 'verification')
jbe@615 174 ) AS "subquery"
jbe@615 175 ORDER BY "seed"
jbe@615 176 LOOP
jbe@615 177 SELECT "initiative"."id" INTO "initiative_id_v"
jbe@615 178 FROM "initiative"
jbe@615 179 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@615 180 JOIN "area" ON "area"."id" = "issue"."area_id"
jbe@615 181 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
jbe@615 182 LEFT JOIN "supporter" AS "self_support" ON
jbe@615 183 "self_support"."initiative_id" = "initiative"."id" AND
jbe@615 184 "self_support"."member_id" = "recipient_id_p"
jbe@615 185 LEFT JOIN "privilege" ON
jbe@615 186 "privilege"."member_id" = "recipient_id_p" AND
jbe@615 187 "privilege"."unit_id" = "area"."unit_id"
jbe@615 188 LEFT JOIN "issue_privilege" ON
jbe@617 189 "issue_privilege"."member_id" = "recipient_id_p" AND
jbe@617 190 "issue_privilege"."issue_id" = "initiative"."issue_id"
jbe@615 191 LEFT JOIN "subscription" ON
jbe@615 192 "subscription"."member_id" = "recipient_id_p" AND
jbe@615 193 "subscription"."unit_id" = "area"."unit_id"
jbe@615 194 LEFT JOIN "ignored_initiative" ON
jbe@615 195 "ignored_initiative"."member_id" = "recipient_id_p" AND
jbe@615 196 "ignored_initiative"."initiative_id" = "initiative"."id"
jbe@615 197 WHERE "supporter"."member_id" = "member_id_v"
jbe@615 198 AND "issue"."area_id" = "area_id_p"
jbe@615 199 AND "issue"."state" IN ('admission', 'discussion', 'verification')
jbe@615 200 AND "initiative"."revoked" ISNULL
jbe@615 201 AND "self_support"."member_id" ISNULL
jbe@615 202 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
jbe@615 203 AND (
jbe@615 204 COALESCE(
jbe@615 205 "issue_privilege"."voting_right", "privilege"."voting_right"
jbe@615 206 ) OR "subscription"."member_id" NOTNULL )
jbe@615 207 AND "ignored_initiative"."member_id" ISNULL
jbe@615 208 AND NOT EXISTS (
jbe@615 209 SELECT NULL FROM "draft"
jbe@615 210 JOIN "ignored_member" ON
jbe@615 211 "ignored_member"."member_id" = "recipient_id_p" AND
jbe@615 212 "ignored_member"."other_member_id" = "draft"."author_id"
jbe@615 213 WHERE "draft"."initiative_id" = "initiative"."id"
jbe@615 214 )
jbe@615 215 ORDER BY md5("seed_v" || '-' || "initiative"."id")
jbe@615 216 LIMIT 1;
jbe@615 217 IF FOUND THEN
jbe@615 218 "match_v" := TRUE;
jbe@615 219 RETURN NEXT "initiative_id_v";
jbe@615 220 IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
jbe@615 221 RETURN;
jbe@615 222 END IF;
jbe@615 223 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
jbe@615 224 END IF;
jbe@615 225 END LOOP;
jbe@615 226 EXIT WHEN NOT "match_v";
jbe@615 227 END LOOP;
jbe@615 228 RETURN;
jbe@615 229 END;
jbe@615 230 $$;
jbe@615 231
jbe@615 232 CREATE OR REPLACE FUNCTION "delegation_chain"
jbe@615 233 ( "member_id_p" "member"."id"%TYPE,
jbe@615 234 "unit_id_p" "unit"."id"%TYPE,
jbe@615 235 "area_id_p" "area"."id"%TYPE,
jbe@615 236 "issue_id_p" "issue"."id"%TYPE,
jbe@615 237 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
jbe@615 238 "simulate_default_p" BOOLEAN DEFAULT FALSE )
jbe@615 239 RETURNS SETOF "delegation_chain_row"
jbe@615 240 LANGUAGE 'plpgsql' STABLE AS $$
jbe@615 241 DECLARE
jbe@615 242 "scope_v" "delegation_scope";
jbe@615 243 "unit_id_v" "unit"."id"%TYPE;
jbe@615 244 "area_id_v" "area"."id"%TYPE;
jbe@615 245 "issue_row" "issue"%ROWTYPE;
jbe@615 246 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
jbe@615 247 "loop_member_id_v" "member"."id"%TYPE;
jbe@615 248 "output_row" "delegation_chain_row";
jbe@615 249 "output_rows" "delegation_chain_row"[];
jbe@615 250 "simulate_v" BOOLEAN;
jbe@615 251 "simulate_here_v" BOOLEAN;
jbe@615 252 "delegation_row" "delegation"%ROWTYPE;
jbe@615 253 "row_count" INT4;
jbe@615 254 "i" INT4;
jbe@615 255 "loop_v" BOOLEAN;
jbe@615 256 BEGIN
jbe@615 257 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
jbe@615 258 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
jbe@615 259 END IF;
jbe@615 260 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
jbe@615 261 "simulate_v" := TRUE;
jbe@615 262 ELSE
jbe@615 263 "simulate_v" := FALSE;
jbe@615 264 END IF;
jbe@615 265 IF
jbe@615 266 "unit_id_p" NOTNULL AND
jbe@615 267 "area_id_p" ISNULL AND
jbe@615 268 "issue_id_p" ISNULL
jbe@615 269 THEN
jbe@615 270 "scope_v" := 'unit';
jbe@615 271 "unit_id_v" := "unit_id_p";
jbe@615 272 ELSIF
jbe@615 273 "unit_id_p" ISNULL AND
jbe@615 274 "area_id_p" NOTNULL AND
jbe@615 275 "issue_id_p" ISNULL
jbe@615 276 THEN
jbe@615 277 "scope_v" := 'area';
jbe@615 278 "area_id_v" := "area_id_p";
jbe@615 279 SELECT "unit_id" INTO "unit_id_v"
jbe@615 280 FROM "area" WHERE "id" = "area_id_v";
jbe@615 281 ELSIF
jbe@615 282 "unit_id_p" ISNULL AND
jbe@615 283 "area_id_p" ISNULL AND
jbe@615 284 "issue_id_p" NOTNULL
jbe@615 285 THEN
jbe@615 286 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
jbe@615 287 IF "issue_row"."id" ISNULL THEN
jbe@615 288 RETURN;
jbe@615 289 END IF;
jbe@615 290 IF "issue_row"."closed" NOTNULL THEN
jbe@615 291 IF "simulate_v" THEN
jbe@615 292 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
jbe@615 293 END IF;
jbe@615 294 FOR "output_row" IN
jbe@615 295 SELECT * FROM
jbe@615 296 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
jbe@615 297 LOOP
jbe@615 298 RETURN NEXT "output_row";
jbe@615 299 END LOOP;
jbe@615 300 RETURN;
jbe@615 301 END IF;
jbe@615 302 "scope_v" := 'issue';
jbe@615 303 SELECT "area_id" INTO "area_id_v"
jbe@615 304 FROM "issue" WHERE "id" = "issue_id_p";
jbe@615 305 SELECT "unit_id" INTO "unit_id_v"
jbe@615 306 FROM "area" WHERE "id" = "area_id_v";
jbe@615 307 ELSE
jbe@615 308 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
jbe@615 309 END IF;
jbe@615 310 "visited_member_ids" := '{}';
jbe@615 311 "loop_member_id_v" := NULL;
jbe@615 312 "output_rows" := '{}';
jbe@615 313 "output_row"."index" := 0;
jbe@615 314 "output_row"."member_id" := "member_id_p";
jbe@615 315 "output_row"."member_valid" := TRUE;
jbe@615 316 "output_row"."participation" := FALSE;
jbe@615 317 "output_row"."overridden" := FALSE;
jbe@615 318 "output_row"."disabled_out" := FALSE;
jbe@615 319 "output_row"."scope_out" := NULL;
jbe@615 320 LOOP
jbe@615 321 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
jbe@615 322 "loop_member_id_v" := "output_row"."member_id";
jbe@615 323 ELSE
jbe@615 324 "visited_member_ids" :=
jbe@615 325 "visited_member_ids" || "output_row"."member_id";
jbe@615 326 END IF;
jbe@615 327 IF "output_row"."participation" ISNULL THEN
jbe@615 328 "output_row"."overridden" := NULL;
jbe@615 329 ELSIF "output_row"."participation" THEN
jbe@615 330 "output_row"."overridden" := TRUE;
jbe@615 331 END IF;
jbe@615 332 "output_row"."scope_in" := "output_row"."scope_out";
jbe@615 333 "output_row"."member_valid" := EXISTS (
jbe@615 334 SELECT NULL FROM "member"
jbe@615 335 LEFT JOIN "privilege"
jbe@615 336 ON "privilege"."member_id" = "member"."id"
jbe@615 337 AND "privilege"."unit_id" = "unit_id_v"
jbe@615 338 LEFT JOIN "issue_privilege"
jbe@615 339 ON "issue_privilege"."member_id" = "member"."id"
jbe@615 340 AND "issue_privilege"."issue_id" = "issue_id_p"
jbe@615 341 WHERE "id" = "output_row"."member_id"
jbe@615 342 AND "member"."active"
jbe@615 343 AND COALESCE(
jbe@615 344 "issue_privilege"."voting_right", "privilege"."voting_right")
jbe@615 345 );
jbe@615 346 "simulate_here_v" := (
jbe@615 347 "simulate_v" AND
jbe@615 348 "output_row"."member_id" = "member_id_p"
jbe@615 349 );
jbe@615 350 "delegation_row" := ROW(NULL);
jbe@615 351 IF "output_row"."member_valid" OR "simulate_here_v" THEN
jbe@615 352 IF "scope_v" = 'unit' THEN
jbe@615 353 IF NOT "simulate_here_v" THEN
jbe@615 354 SELECT * INTO "delegation_row" FROM "delegation"
jbe@615 355 WHERE "truster_id" = "output_row"."member_id"
jbe@615 356 AND "unit_id" = "unit_id_v";
jbe@615 357 END IF;
jbe@615 358 ELSIF "scope_v" = 'area' THEN
jbe@615 359 IF "simulate_here_v" THEN
jbe@615 360 IF "simulate_trustee_id_p" ISNULL THEN
jbe@615 361 SELECT * INTO "delegation_row" FROM "delegation"
jbe@615 362 WHERE "truster_id" = "output_row"."member_id"
jbe@615 363 AND "unit_id" = "unit_id_v";
jbe@615 364 END IF;
jbe@615 365 ELSE
jbe@615 366 SELECT * INTO "delegation_row" FROM "delegation"
jbe@615 367 WHERE "truster_id" = "output_row"."member_id"
jbe@615 368 AND (
jbe@615 369 "unit_id" = "unit_id_v" OR
jbe@615 370 "area_id" = "area_id_v"
jbe@615 371 )
jbe@615 372 ORDER BY "scope" DESC;
jbe@615 373 END IF;
jbe@615 374 ELSIF "scope_v" = 'issue' THEN
jbe@615 375 IF "issue_row"."fully_frozen" ISNULL THEN
jbe@615 376 "output_row"."participation" := EXISTS (
jbe@615 377 SELECT NULL FROM "interest"
jbe@615 378 WHERE "issue_id" = "issue_id_p"
jbe@615 379 AND "member_id" = "output_row"."member_id"
jbe@615 380 );
jbe@615 381 ELSE
jbe@615 382 IF "output_row"."member_id" = "member_id_p" THEN
jbe@615 383 "output_row"."participation" := EXISTS (
jbe@615 384 SELECT NULL FROM "direct_voter"
jbe@615 385 WHERE "issue_id" = "issue_id_p"
jbe@615 386 AND "member_id" = "output_row"."member_id"
jbe@615 387 );
jbe@615 388 ELSE
jbe@615 389 "output_row"."participation" := NULL;
jbe@615 390 END IF;
jbe@615 391 END IF;
jbe@615 392 IF "simulate_here_v" THEN
jbe@615 393 IF "simulate_trustee_id_p" ISNULL THEN
jbe@615 394 SELECT * INTO "delegation_row" FROM "delegation"
jbe@615 395 WHERE "truster_id" = "output_row"."member_id"
jbe@615 396 AND (
jbe@615 397 "unit_id" = "unit_id_v" OR
jbe@615 398 "area_id" = "area_id_v"
jbe@615 399 )
jbe@615 400 ORDER BY "scope" DESC;
jbe@615 401 END IF;
jbe@615 402 ELSE
jbe@615 403 SELECT * INTO "delegation_row" FROM "delegation"
jbe@615 404 WHERE "truster_id" = "output_row"."member_id"
jbe@615 405 AND (
jbe@615 406 "unit_id" = "unit_id_v" OR
jbe@615 407 "area_id" = "area_id_v" OR
jbe@615 408 "issue_id" = "issue_id_p"
jbe@615 409 )
jbe@615 410 ORDER BY "scope" DESC;
jbe@615 411 END IF;
jbe@615 412 END IF;
jbe@615 413 ELSE
jbe@615 414 "output_row"."participation" := FALSE;
jbe@615 415 END IF;
jbe@615 416 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
jbe@615 417 "output_row"."scope_out" := "scope_v";
jbe@615 418 "output_rows" := "output_rows" || "output_row";
jbe@615 419 "output_row"."member_id" := "simulate_trustee_id_p";
jbe@615 420 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
jbe@615 421 "output_row"."scope_out" := "delegation_row"."scope";
jbe@615 422 "output_rows" := "output_rows" || "output_row";
jbe@615 423 "output_row"."member_id" := "delegation_row"."trustee_id";
jbe@615 424 ELSIF "delegation_row"."scope" NOTNULL THEN
jbe@615 425 "output_row"."scope_out" := "delegation_row"."scope";
jbe@615 426 "output_row"."disabled_out" := TRUE;
jbe@615 427 "output_rows" := "output_rows" || "output_row";
jbe@615 428 EXIT;
jbe@615 429 ELSE
jbe@615 430 "output_row"."scope_out" := NULL;
jbe@615 431 "output_rows" := "output_rows" || "output_row";
jbe@615 432 EXIT;
jbe@615 433 END IF;
jbe@615 434 EXIT WHEN "loop_member_id_v" NOTNULL;
jbe@615 435 "output_row"."index" := "output_row"."index" + 1;
jbe@615 436 END LOOP;
jbe@615 437 "row_count" := array_upper("output_rows", 1);
jbe@615 438 "i" := 1;
jbe@615 439 "loop_v" := FALSE;
jbe@615 440 LOOP
jbe@615 441 "output_row" := "output_rows"["i"];
jbe@615 442 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
jbe@615 443 IF "loop_v" THEN
jbe@615 444 IF "i" + 1 = "row_count" THEN
jbe@615 445 "output_row"."loop" := 'last';
jbe@615 446 ELSIF "i" = "row_count" THEN
jbe@615 447 "output_row"."loop" := 'repetition';
jbe@615 448 ELSE
jbe@615 449 "output_row"."loop" := 'intermediate';
jbe@615 450 END IF;
jbe@615 451 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
jbe@615 452 "output_row"."loop" := 'first';
jbe@615 453 "loop_v" := TRUE;
jbe@615 454 END IF;
jbe@615 455 IF "scope_v" = 'unit' THEN
jbe@615 456 "output_row"."participation" := NULL;
jbe@615 457 END IF;
jbe@615 458 RETURN NEXT "output_row";
jbe@615 459 "i" := "i" + 1;
jbe@615 460 END LOOP;
jbe@615 461 RETURN;
jbe@615 462 END;
jbe@615 463 $$;
jbe@615 464
jbe@615 465 CREATE OR REPLACE FUNCTION "calculate_member_counts"()
jbe@615 466 RETURNS VOID
jbe@615 467 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@615 468 BEGIN
jbe@615 469 PERFORM "require_transaction_isolation"();
jbe@615 470 DELETE FROM "member_count";
jbe@615 471 INSERT INTO "member_count" ("total_count")
jbe@615 472 SELECT "total_count" FROM "member_count_view";
jbe@615 473 UPDATE "unit" SET
jbe@615 474 "member_count" = "view"."member_count",
jbe@615 475 "member_weight" = "view"."member_weight"
jbe@615 476 FROM "unit_member_count" AS "view"
jbe@615 477 WHERE "view"."unit_id" = "unit"."id";
jbe@615 478 RETURN;
jbe@615 479 END;
jbe@615 480 $$;
jbe@615 481 COMMENT ON FUNCTION "calculate_member_counts"() IS 'Updates "member_count" table and "member_count" and "member_weight" columns of table "area" by materializing data from views "member_count_view" and "unit_member_count"';
jbe@615 482
jbe@615 483 CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_snapshot"
jbe@615 484 ( "snapshot_id_p" "snapshot"."id"%TYPE,
jbe@615 485 "issue_id_p" "issue"."id"%TYPE,
jbe@615 486 "member_id_p" "member"."id"%TYPE,
jbe@615 487 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@615 488 RETURNS "direct_interest_snapshot"."weight"%TYPE
jbe@615 489 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@615 490 DECLARE
jbe@615 491 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@615 492 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
jbe@615 493 "weight_v" INT4;
jbe@615 494 "sub_weight_v" INT4;
jbe@615 495 BEGIN
jbe@615 496 PERFORM "require_transaction_isolation"();
jbe@615 497 "weight_v" := 0;
jbe@615 498 FOR "issue_delegation_row" IN
jbe@615 499 SELECT * FROM "issue_delegation"
jbe@615 500 WHERE "trustee_id" = "member_id_p"
jbe@615 501 AND "issue_id" = "issue_id_p"
jbe@615 502 LOOP
jbe@615 503 IF NOT EXISTS (
jbe@615 504 SELECT NULL FROM "direct_interest_snapshot"
jbe@615 505 WHERE "snapshot_id" = "snapshot_id_p"
jbe@615 506 AND "issue_id" = "issue_id_p"
jbe@615 507 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@615 508 ) AND NOT EXISTS (
jbe@615 509 SELECT NULL FROM "delegating_interest_snapshot"
jbe@615 510 WHERE "snapshot_id" = "snapshot_id_p"
jbe@615 511 AND "issue_id" = "issue_id_p"
jbe@615 512 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@615 513 ) THEN
jbe@615 514 "delegate_member_ids_v" :=
jbe@615 515 "member_id_p" || "delegate_member_ids_p";
jbe@615 516 INSERT INTO "delegating_interest_snapshot" (
jbe@615 517 "snapshot_id",
jbe@615 518 "issue_id",
jbe@615 519 "member_id",
jbe@615 520 "ownweight",
jbe@615 521 "scope",
jbe@615 522 "delegate_member_ids"
jbe@615 523 ) VALUES (
jbe@615 524 "snapshot_id_p",
jbe@615 525 "issue_id_p",
jbe@615 526 "issue_delegation_row"."truster_id",
jbe@615 527 "issue_delegation_row"."weight",
jbe@615 528 "issue_delegation_row"."scope",
jbe@615 529 "delegate_member_ids_v"
jbe@615 530 );
jbe@615 531 "sub_weight_v" := "issue_delegation_row"."weight" +
jbe@615 532 "weight_of_added_delegations_for_snapshot"(
jbe@615 533 "snapshot_id_p",
jbe@615 534 "issue_id_p",
jbe@615 535 "issue_delegation_row"."truster_id",
jbe@615 536 "delegate_member_ids_v"
jbe@615 537 );
jbe@615 538 UPDATE "delegating_interest_snapshot"
jbe@615 539 SET "weight" = "sub_weight_v"
jbe@615 540 WHERE "snapshot_id" = "snapshot_id_p"
jbe@615 541 AND "issue_id" = "issue_id_p"
jbe@615 542 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@615 543 "weight_v" := "weight_v" + "sub_weight_v";
jbe@615 544 END IF;
jbe@615 545 END LOOP;
jbe@615 546 RETURN "weight_v";
jbe@615 547 END;
jbe@615 548 $$;
jbe@615 549
jbe@615 550 CREATE OR REPLACE FUNCTION "take_snapshot"
jbe@615 551 ( "issue_id_p" "issue"."id"%TYPE,
jbe@615 552 "area_id_p" "area"."id"%TYPE = NULL )
jbe@615 553 RETURNS "snapshot"."id"%TYPE
jbe@615 554 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@615 555 DECLARE
jbe@615 556 "area_id_v" "area"."id"%TYPE;
jbe@615 557 "unit_id_v" "unit"."id"%TYPE;
jbe@615 558 "snapshot_id_v" "snapshot"."id"%TYPE;
jbe@615 559 "issue_id_v" "issue"."id"%TYPE;
jbe@615 560 "member_id_v" "member"."id"%TYPE;
jbe@615 561 BEGIN
jbe@615 562 IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN
jbe@615 563 RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL';
jbe@615 564 END IF;
jbe@615 565 PERFORM "require_transaction_isolation"();
jbe@615 566 IF "issue_id_p" ISNULL THEN
jbe@615 567 "area_id_v" := "area_id_p";
jbe@615 568 ELSE
jbe@615 569 SELECT "area_id" INTO "area_id_v"
jbe@615 570 FROM "issue" WHERE "id" = "issue_id_p";
jbe@615 571 END IF;
jbe@615 572 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
jbe@615 573 INSERT INTO "snapshot" ("area_id", "issue_id")
jbe@615 574 VALUES ("area_id_v", "issue_id_p")
jbe@615 575 RETURNING "id" INTO "snapshot_id_v";
jbe@615 576 INSERT INTO "snapshot_population" ("snapshot_id", "member_id", "weight")
jbe@615 577 SELECT
jbe@615 578 "snapshot_id_v",
jbe@615 579 "member"."id",
jbe@615 580 COALESCE("issue_privilege"."weight", "privilege"."weight")
jbe@615 581 FROM "member"
jbe@615 582 LEFT JOIN "privilege"
jbe@615 583 ON "privilege"."unit_id" = "unit_id_v"
jbe@615 584 AND "privilege"."member_id" = "member"."id"
jbe@615 585 LEFT JOIN "issue_privilege"
jbe@615 586 ON "issue_privilege"."issue_id" = "issue_id_p"
jbe@615 587 AND "issue_privilege"."member_id" = "member"."id"
jbe@615 588 WHERE "member"."active" AND COALESCE(
jbe@615 589 "issue_privilege"."voting_right", "privilege"."voting_right");
jbe@615 590 UPDATE "snapshot" SET
jbe@615 591 "population" = (
jbe@615 592 SELECT sum("weight") FROM "snapshot_population"
jbe@615 593 WHERE "snapshot_id" = "snapshot_id_v"
jbe@615 594 ) WHERE "id" = "snapshot_id_v";
jbe@615 595 FOR "issue_id_v" IN
jbe@615 596 SELECT "id" FROM "issue"
jbe@615 597 WHERE CASE WHEN "issue_id_p" ISNULL THEN
jbe@615 598 "area_id" = "area_id_p" AND
jbe@615 599 "state" = 'admission'
jbe@615 600 ELSE
jbe@615 601 "id" = "issue_id_p"
jbe@615 602 END
jbe@615 603 LOOP
jbe@615 604 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
jbe@615 605 VALUES ("snapshot_id_v", "issue_id_v");
jbe@615 606 INSERT INTO "direct_interest_snapshot"
jbe@615 607 ("snapshot_id", "issue_id", "member_id", "ownweight")
jbe@615 608 SELECT
jbe@615 609 "snapshot_id_v" AS "snapshot_id",
jbe@615 610 "issue_id_v" AS "issue_id",
jbe@615 611 "member"."id" AS "member_id",
jbe@615 612 COALESCE(
jbe@615 613 "issue_privilege"."weight", "privilege"."weight"
jbe@615 614 ) AS "ownweight"
jbe@615 615 FROM "issue"
jbe@615 616 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@615 617 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
jbe@615 618 JOIN "member" ON "interest"."member_id" = "member"."id"
jbe@615 619 LEFT JOIN "privilege"
jbe@615 620 ON "privilege"."unit_id" = "area"."unit_id"
jbe@615 621 AND "privilege"."member_id" = "member"."id"
jbe@615 622 LEFT JOIN "issue_privilege"
jbe@615 623 ON "issue_privilege"."issue_id" = "issue_id_v"
jbe@615 624 AND "issue_privilege"."member_id" = "member"."id"
jbe@615 625 WHERE "issue"."id" = "issue_id_v"
jbe@615 626 AND "member"."active" AND COALESCE(
jbe@615 627 "issue_privilege"."voting_right", "privilege"."voting_right");
jbe@615 628 FOR "member_id_v" IN
jbe@615 629 SELECT "member_id" FROM "direct_interest_snapshot"
jbe@615 630 WHERE "snapshot_id" = "snapshot_id_v"
jbe@615 631 AND "issue_id" = "issue_id_v"
jbe@615 632 LOOP
jbe@615 633 UPDATE "direct_interest_snapshot" SET
jbe@615 634 "weight" = "ownweight" +
jbe@615 635 "weight_of_added_delegations_for_snapshot"(
jbe@615 636 "snapshot_id_v",
jbe@615 637 "issue_id_v",
jbe@615 638 "member_id_v",
jbe@615 639 '{}'
jbe@615 640 )
jbe@615 641 WHERE "snapshot_id" = "snapshot_id_v"
jbe@615 642 AND "issue_id" = "issue_id_v"
jbe@615 643 AND "member_id" = "member_id_v";
jbe@615 644 END LOOP;
jbe@615 645 INSERT INTO "direct_supporter_snapshot"
jbe@615 646 ( "snapshot_id", "issue_id", "initiative_id", "member_id",
jbe@615 647 "draft_id", "informed", "satisfied" )
jbe@615 648 SELECT
jbe@615 649 "snapshot_id_v" AS "snapshot_id",
jbe@615 650 "issue_id_v" AS "issue_id",
jbe@615 651 "initiative"."id" AS "initiative_id",
jbe@615 652 "supporter"."member_id" AS "member_id",
jbe@615 653 "supporter"."draft_id" AS "draft_id",
jbe@615 654 "supporter"."draft_id" = "current_draft"."id" AS "informed",
jbe@615 655 NOT EXISTS (
jbe@615 656 SELECT NULL FROM "critical_opinion"
jbe@615 657 WHERE "initiative_id" = "initiative"."id"
jbe@615 658 AND "member_id" = "supporter"."member_id"
jbe@615 659 ) AS "satisfied"
jbe@615 660 FROM "initiative"
jbe@615 661 JOIN "supporter"
jbe@615 662 ON "supporter"."initiative_id" = "initiative"."id"
jbe@615 663 JOIN "current_draft"
jbe@615 664 ON "initiative"."id" = "current_draft"."initiative_id"
jbe@615 665 JOIN "direct_interest_snapshot"
jbe@615 666 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"
jbe@615 667 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"
jbe@615 668 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
jbe@615 669 WHERE "initiative"."issue_id" = "issue_id_v";
jbe@615 670 DELETE FROM "temporary_suggestion_counts";
jbe@615 671 INSERT INTO "temporary_suggestion_counts"
jbe@615 672 ( "id",
jbe@615 673 "minus2_unfulfilled_count", "minus2_fulfilled_count",
jbe@615 674 "minus1_unfulfilled_count", "minus1_fulfilled_count",
jbe@615 675 "plus1_unfulfilled_count", "plus1_fulfilled_count",
jbe@615 676 "plus2_unfulfilled_count", "plus2_fulfilled_count" )
jbe@615 677 SELECT
jbe@615 678 "suggestion"."id",
jbe@615 679 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@615 680 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@615 681 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@615 682 AND "di"."issue_id" = "issue_id_v"
jbe@615 683 AND "di"."member_id" = "opinion"."member_id"
jbe@615 684 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@615 685 AND "opinion"."degree" = -2
jbe@615 686 AND "opinion"."fulfilled" = FALSE
jbe@615 687 ) AS "minus2_unfulfilled_count",
jbe@615 688 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@615 689 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@615 690 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@615 691 AND "di"."issue_id" = "issue_id_v"
jbe@615 692 AND "di"."member_id" = "opinion"."member_id"
jbe@615 693 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@615 694 AND "opinion"."degree" = -2
jbe@615 695 AND "opinion"."fulfilled" = TRUE
jbe@615 696 ) AS "minus2_fulfilled_count",
jbe@615 697 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@615 698 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@615 699 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@615 700 AND "di"."issue_id" = "issue_id_v"
jbe@615 701 AND "di"."member_id" = "opinion"."member_id"
jbe@615 702 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@615 703 AND "opinion"."degree" = -1
jbe@615 704 AND "opinion"."fulfilled" = FALSE
jbe@615 705 ) AS "minus1_unfulfilled_count",
jbe@615 706 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@615 707 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@615 708 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@615 709 AND "di"."issue_id" = "issue_id_v"
jbe@615 710 AND "di"."member_id" = "opinion"."member_id"
jbe@615 711 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@615 712 AND "opinion"."degree" = -1
jbe@615 713 AND "opinion"."fulfilled" = TRUE
jbe@615 714 ) AS "minus1_fulfilled_count",
jbe@615 715 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@615 716 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@615 717 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@615 718 AND "di"."issue_id" = "issue_id_v"
jbe@615 719 AND "di"."member_id" = "opinion"."member_id"
jbe@615 720 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@615 721 AND "opinion"."degree" = 1
jbe@615 722 AND "opinion"."fulfilled" = FALSE
jbe@615 723 ) AS "plus1_unfulfilled_count",
jbe@615 724 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@615 725 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@615 726 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@615 727 AND "di"."issue_id" = "issue_id_v"
jbe@615 728 AND "di"."member_id" = "opinion"."member_id"
jbe@615 729 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@615 730 AND "opinion"."degree" = 1
jbe@615 731 AND "opinion"."fulfilled" = TRUE
jbe@615 732 ) AS "plus1_fulfilled_count",
jbe@615 733 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@615 734 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@615 735 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@615 736 AND "di"."issue_id" = "issue_id_v"
jbe@615 737 AND "di"."member_id" = "opinion"."member_id"
jbe@615 738 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@615 739 AND "opinion"."degree" = 2
jbe@615 740 AND "opinion"."fulfilled" = FALSE
jbe@615 741 ) AS "plus2_unfulfilled_count",
jbe@615 742 ( SELECT coalesce(sum("di"."weight"), 0)
jbe@615 743 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
jbe@615 744 ON "di"."snapshot_id" = "snapshot_id_v"
jbe@615 745 AND "di"."issue_id" = "issue_id_v"
jbe@615 746 AND "di"."member_id" = "opinion"."member_id"
jbe@615 747 WHERE "opinion"."suggestion_id" = "suggestion"."id"
jbe@615 748 AND "opinion"."degree" = 2
jbe@615 749 AND "opinion"."fulfilled" = TRUE
jbe@615 750 ) AS "plus2_fulfilled_count"
jbe@615 751 FROM "suggestion" JOIN "initiative"
jbe@615 752 ON "suggestion"."initiative_id" = "initiative"."id"
jbe@615 753 WHERE "initiative"."issue_id" = "issue_id_v";
jbe@615 754 END LOOP;
jbe@615 755 RETURN "snapshot_id_v";
jbe@615 756 END;
jbe@615 757 $$;
jbe@615 758
jbe@615 759 CREATE OR REPLACE FUNCTION "weight_of_added_vote_delegations"
jbe@615 760 ( "issue_id_p" "issue"."id"%TYPE,
jbe@615 761 "member_id_p" "member"."id"%TYPE,
jbe@615 762 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
jbe@615 763 RETURNS "direct_voter"."weight"%TYPE
jbe@615 764 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@615 765 DECLARE
jbe@615 766 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@615 767 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
jbe@615 768 "weight_v" INT4;
jbe@615 769 "sub_weight_v" INT4;
jbe@615 770 BEGIN
jbe@615 771 PERFORM "require_transaction_isolation"();
jbe@615 772 "weight_v" := 0;
jbe@615 773 FOR "issue_delegation_row" IN
jbe@615 774 SELECT * FROM "issue_delegation"
jbe@615 775 WHERE "trustee_id" = "member_id_p"
jbe@615 776 AND "issue_id" = "issue_id_p"
jbe@615 777 LOOP
jbe@615 778 IF NOT EXISTS (
jbe@615 779 SELECT NULL FROM "direct_voter"
jbe@615 780 WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@615 781 AND "issue_id" = "issue_id_p"
jbe@615 782 ) AND NOT EXISTS (
jbe@615 783 SELECT NULL FROM "delegating_voter"
jbe@615 784 WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@615 785 AND "issue_id" = "issue_id_p"
jbe@615 786 ) THEN
jbe@615 787 "delegate_member_ids_v" :=
jbe@615 788 "member_id_p" || "delegate_member_ids_p";
jbe@615 789 INSERT INTO "delegating_voter" (
jbe@615 790 "issue_id",
jbe@615 791 "member_id",
jbe@615 792 "ownweight",
jbe@615 793 "scope",
jbe@615 794 "delegate_member_ids"
jbe@615 795 ) VALUES (
jbe@615 796 "issue_id_p",
jbe@615 797 "issue_delegation_row"."truster_id",
jbe@615 798 "issue_delegation_row"."weight",
jbe@615 799 "issue_delegation_row"."scope",
jbe@615 800 "delegate_member_ids_v"
jbe@615 801 );
jbe@615 802 "sub_weight_v" := "issue_delegation_row"."weight" +
jbe@615 803 "weight_of_added_vote_delegations"(
jbe@615 804 "issue_id_p",
jbe@615 805 "issue_delegation_row"."truster_id",
jbe@615 806 "delegate_member_ids_v"
jbe@615 807 );
jbe@615 808 UPDATE "delegating_voter"
jbe@615 809 SET "weight" = "sub_weight_v"
jbe@615 810 WHERE "issue_id" = "issue_id_p"
jbe@615 811 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@615 812 "weight_v" := "weight_v" + "sub_weight_v";
jbe@615 813 END IF;
jbe@615 814 END LOOP;
jbe@615 815 RETURN "weight_v";
jbe@615 816 END;
jbe@615 817 $$;
jbe@615 818
jbe@615 819 CREATE OR REPLACE FUNCTION "add_vote_delegations"
jbe@615 820 ( "issue_id_p" "issue"."id"%TYPE )
jbe@615 821 RETURNS VOID
jbe@615 822 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@615 823 DECLARE
jbe@615 824 "member_id_v" "member"."id"%TYPE;
jbe@615 825 BEGIN
jbe@615 826 PERFORM "require_transaction_isolation"();
jbe@615 827 FOR "member_id_v" IN
jbe@615 828 SELECT "member_id" FROM "direct_voter"
jbe@615 829 WHERE "issue_id" = "issue_id_p"
jbe@615 830 LOOP
jbe@615 831 UPDATE "direct_voter" SET
jbe@615 832 "weight" = "ownweight" + "weight_of_added_vote_delegations"(
jbe@615 833 "issue_id_p",
jbe@615 834 "member_id_v",
jbe@615 835 '{}'
jbe@615 836 )
jbe@615 837 WHERE "member_id" = "member_id_v"
jbe@615 838 AND "issue_id" = "issue_id_p";
jbe@615 839 END LOOP;
jbe@615 840 RETURN;
jbe@615 841 END;
jbe@615 842 $$;
jbe@615 843
jbe@615 844 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@615 845 RETURNS VOID
jbe@615 846 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@615 847 DECLARE
jbe@615 848 "area_id_v" "area"."id"%TYPE;
jbe@615 849 "unit_id_v" "unit"."id"%TYPE;
jbe@615 850 "member_id_v" "member"."id"%TYPE;
jbe@615 851 BEGIN
jbe@615 852 PERFORM "require_transaction_isolation"();
jbe@615 853 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@615 854 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
jbe@615 855 -- override protection triggers:
jbe@615 856 INSERT INTO "temporary_transaction_data" ("key", "value")
jbe@615 857 VALUES ('override_protection_triggers', TRUE::TEXT);
jbe@615 858 -- delete timestamp of voting comment:
jbe@615 859 UPDATE "direct_voter" SET "comment_changed" = NULL
jbe@615 860 WHERE "issue_id" = "issue_id_p";
jbe@615 861 -- delete delegating votes (in cases of manual reset of issue state):
jbe@615 862 DELETE FROM "delegating_voter"
jbe@615 863 WHERE "issue_id" = "issue_id_p";
jbe@615 864 -- delete votes from non-privileged voters:
jbe@615 865 DELETE FROM "direct_voter"
jbe@615 866 USING (
jbe@615 867 SELECT "direct_voter"."member_id"
jbe@615 868 FROM "direct_voter"
jbe@615 869 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@615 870 LEFT JOIN "privilege"
jbe@615 871 ON "privilege"."unit_id" = "unit_id_v"
jbe@615 872 AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@615 873 LEFT JOIN "issue_privilege"
jbe@615 874 ON "issue_privilege"."issue_id" = "issue_id_p"
jbe@615 875 AND "issue_privilege"."member_id" = "direct_voter"."member_id"
jbe@615 876 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@615 877 "member"."active" = FALSE OR
jbe@615 878 COALESCE(
jbe@615 879 "issue_privilege"."voting_right",
jbe@615 880 "privilege"."voting_right",
jbe@615 881 FALSE
jbe@615 882 ) = FALSE
jbe@615 883 )
jbe@615 884 ) AS "subquery"
jbe@615 885 WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@615 886 AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@615 887 -- consider voting weight and delegations:
jbe@615 888 UPDATE "direct_voter" SET "ownweight" = "privilege"."weight"
jbe@615 889 FROM "privilege"
jbe@615 890 WHERE "issue_id" = "issue_id_p"
jbe@615 891 AND "privilege"."unit_id" = "unit_id_v"
jbe@615 892 AND "privilege"."member_id" = "direct_voter"."member_id";
jbe@615 893 UPDATE "direct_voter" SET "ownweight" = "issue_privilege"."weight"
jbe@615 894 FROM "issue_privilege"
jbe@615 895 WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@615 896 AND "issue_privilege"."issue_id" = "issue_id_p"
jbe@615 897 AND "issue_privilege"."member_id" = "direct_voter"."member_id";
jbe@615 898 PERFORM "add_vote_delegations"("issue_id_p");
jbe@615 899 -- mark first preferences:
jbe@615 900 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
jbe@615 901 FROM (
jbe@615 902 SELECT
jbe@615 903 "vote"."initiative_id",
jbe@615 904 "vote"."member_id",
jbe@615 905 CASE WHEN "vote"."grade" > 0 THEN
jbe@615 906 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
jbe@615 907 ELSE NULL
jbe@615 908 END AS "first_preference"
jbe@615 909 FROM "vote"
jbe@615 910 JOIN "initiative" -- NOTE: due to missing index on issue_id
jbe@615 911 ON "vote"."issue_id" = "initiative"."issue_id"
jbe@615 912 JOIN "vote" AS "agg"
jbe@615 913 ON "initiative"."id" = "agg"."initiative_id"
jbe@615 914 AND "vote"."member_id" = "agg"."member_id"
jbe@615 915 GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
jbe@615 916 ) AS "subquery"
jbe@615 917 WHERE "vote"."issue_id" = "issue_id_p"
jbe@615 918 AND "vote"."initiative_id" = "subquery"."initiative_id"
jbe@615 919 AND "vote"."member_id" = "subquery"."member_id";
jbe@615 920 -- finish overriding protection triggers (avoids garbage):
jbe@615 921 DELETE FROM "temporary_transaction_data"
jbe@615 922 WHERE "key" = 'override_protection_triggers';
jbe@615 923 -- materialize battle_view:
jbe@615 924 -- NOTE: "closed" column of issue must be set at this point
jbe@615 925 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@615 926 INSERT INTO "battle" (
jbe@615 927 "issue_id",
jbe@615 928 "winning_initiative_id", "losing_initiative_id",
jbe@615 929 "count"
jbe@615 930 ) SELECT
jbe@615 931 "issue_id",
jbe@615 932 "winning_initiative_id", "losing_initiative_id",
jbe@615 933 "count"
jbe@615 934 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@615 935 -- set voter count:
jbe@615 936 UPDATE "issue" SET
jbe@615 937 "voter_count" = (
jbe@615 938 SELECT coalesce(sum("weight"), 0)
jbe@615 939 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@615 940 )
jbe@615 941 WHERE "id" = "issue_id_p";
jbe@615 942 -- copy "positive_votes" and "negative_votes" from "battle" table:
jbe@615 943 -- NOTE: "first_preference_votes" is set to a default of 0 at this step
jbe@615 944 UPDATE "initiative" SET
jbe@615 945 "first_preference_votes" = 0,
jbe@615 946 "positive_votes" = "battle_win"."count",
jbe@615 947 "negative_votes" = "battle_lose"."count"
jbe@615 948 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
jbe@615 949 WHERE
jbe@615 950 "battle_win"."issue_id" = "issue_id_p" AND
jbe@615 951 "battle_win"."winning_initiative_id" = "initiative"."id" AND
jbe@615 952 "battle_win"."losing_initiative_id" ISNULL AND
jbe@615 953 "battle_lose"."issue_id" = "issue_id_p" AND
jbe@615 954 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
jbe@615 955 "battle_lose"."winning_initiative_id" ISNULL;
jbe@615 956 -- calculate "first_preference_votes":
jbe@615 957 -- NOTE: will only set values not equal to zero
jbe@615 958 UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum"
jbe@615 959 FROM (
jbe@615 960 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
jbe@615 961 FROM "vote" JOIN "direct_voter"
jbe@615 962 ON "vote"."issue_id" = "direct_voter"."issue_id"
jbe@615 963 AND "vote"."member_id" = "direct_voter"."member_id"
jbe@615 964 WHERE "vote"."first_preference"
jbe@615 965 GROUP BY "vote"."initiative_id"
jbe@615 966 ) AS "subquery"
jbe@615 967 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@615 968 AND "initiative"."admitted"
jbe@615 969 AND "initiative"."id" = "subquery"."initiative_id";
jbe@615 970 END;
jbe@615 971 $$;
jbe@615 972
jbe@619 973 DROP INDEX "posting_global_idx";
jbe@619 974 DROP INDEX "posting_unit_idx";
jbe@619 975 DROP INDEX "posting_area_idx";
jbe@619 976 DROP INDEX "posting_policy_idx";
jbe@619 977 DROP INDEX "posting_issue_idx";
jbe@619 978 DROP INDEX "posting_initiative_idx";
jbe@619 979 DROP INDEX "posting_suggestion_idx";
jbe@619 980
jbe@619 981 DROP INDEX "posting_lexeme_idx";
jbe@619 982
jbe@619 983 DROP INDEX "event_tl_global_idx";
jbe@619 984 DROP INDEX "event_tl_unit_idx";
jbe@619 985 DROP INDEX "event_tl_area_idx";
jbe@619 986 DROP INDEX "event_tl_policy_idx";
jbe@619 987 DROP INDEX "event_tl_issue_idx";
jbe@619 988 DROP INDEX "event_tl_initiative_idx";
jbe@619 989 DROP INDEX "event_tl_suggestion_idx";
jbe@619 990
jbe@619 991 DROP EXTENSION IF EXISTS conflux;
jbe@619 992 DROP EXTENSION IF EXISTS btree_gist;
jbe@619 993
jbe@615 994 COMMIT;

Impressum / About Us