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