liquid_feedback_core
view update/core-update.v2.1.0-v2.2.0.sql @ 413:e024c50cfe3d
Added tag v3.0.0 for changeset 399dc1a86398
| author | jbe |
|---|---|
| date | Fri Jan 31 12:46:17 2014 +0100 (2014-01-31) |
| parents | a5d4df7f4e22 |
| children |
line source
1 BEGIN;
3 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
5 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
6 SELECT * FROM (VALUES ('2.2.0', 2, 2, 0))
7 AS "subquery"("string", "major", "minor", "revision");
9 DROP VIEW "issue_with_ranks_missing";
10 DROP VIEW "open_issue"; -- recreated later
11 DROP VIEW "event_seen_by_member"; -- recreated later
12 DROP VIEW "selected_event_seen_by_member"; -- recreated later
13 ALTER TABLE "issue" DROP CONSTRAINT "valid_state";
14 ALTER TABLE "issue" DROP COLUMN "ranks_available";
15 ALTER TABLE "event" DROP CONSTRAINT "event_state_check";
16 ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_issue_state_changed"; -- recreated later
17 ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft"; -- recreated later
18 ALTER TABLE "event" DROP CONSTRAINT "null_constraints_for_suggestion_creation"; -- recreated later
19 ALTER TYPE "issue_state" RENAME TO "issue_state_old";
20 CREATE TYPE "issue_state" AS ENUM (
21 'admission', 'discussion', 'verification', 'voting',
22 'canceled_revoked_before_accepted',
23 'canceled_issue_not_accepted',
24 'canceled_after_revocation_during_discussion',
25 'canceled_after_revocation_during_verification',
26 'canceled_no_initiative_admitted',
27 'finished_without_winner', 'finished_with_winner');
28 ALTER TABLE "issue" ALTER COLUMN "state" DROP DEFAULT;
29 ALTER TABLE "issue" ALTER COLUMN "state" TYPE "issue_state" USING "state"::text::"issue_state";
30 ALTER TABLE "event" ALTER COLUMN "state" TYPE "issue_state" USING "state"::text::"issue_state";
31 DROP TYPE "issue_state_old";
32 ALTER TABLE "issue" ALTER COLUMN "state" SET DEFAULT 'admission';
33 ALTER TABLE "issue" ADD CONSTRAINT "valid_state"
34 CHECK ((
35 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
36 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
37 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL ) OR
38 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL)
39 ) AND (
40 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
41 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
42 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
43 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
44 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
45 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
46 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
47 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
48 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" = "fully_frozen") OR
49 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen") OR
50 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen")
51 ));
52 ALTER TABLE "issue" ADD COLUMN "phase_finished" TIMESTAMPTZ;
53 COMMENT ON COLUMN "issue"."phase_finished" IS 'Set to a value NOTNULL, if the current phase has finished, but calculations are pending; No changes in this issue shall be made by the frontend or API when this value is set';
54 ALTER TABLE "issue" ADD CONSTRAINT "phase_finished_only_when_not_closed"
55 CHECK ("phase_finished" ISNULL OR "closed" ISNULL);
56 ALTER TABLE "event" ADD CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
57 "event" != 'issue_state_changed' OR (
58 "member_id" ISNULL AND
59 "issue_id" NOTNULL AND
60 "state" NOTNULL AND
61 "initiative_id" ISNULL AND
62 "draft_id" ISNULL AND
63 "suggestion_id" ISNULL ));
64 ALTER TABLE "event" ADD CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
65 "event" NOT IN (
66 'initiative_created_in_new_issue',
67 'initiative_created_in_existing_issue',
68 'initiative_revoked',
69 'new_draft_created'
70 ) OR (
71 "member_id" NOTNULL AND
72 "issue_id" NOTNULL AND
73 "state" NOTNULL AND
74 "initiative_id" NOTNULL AND
75 "draft_id" NOTNULL AND
76 "suggestion_id" ISNULL ));
77 ALTER TABLE "event" ADD CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
78 "event" != 'suggestion_created' OR (
79 "member_id" NOTNULL AND
80 "issue_id" NOTNULL AND
81 "state" NOTNULL AND
82 "initiative_id" NOTNULL AND
83 "draft_id" ISNULL AND
84 "suggestion_id" NOTNULL ));
85 CREATE VIEW "open_issue" AS
86 SELECT * FROM "issue" WHERE "closed" ISNULL;
87 COMMENT ON VIEW "open_issue" IS 'All open issues';
88 CREATE VIEW "event_seen_by_member" AS
89 SELECT
90 "member"."id" AS "seen_by_member_id",
91 CASE WHEN "event"."state" IN (
92 'voting',
93 'finished_without_winner',
94 'finished_with_winner'
95 ) THEN
96 'voting'::"notify_level"
97 ELSE
98 CASE WHEN "event"."state" IN (
99 'verification',
100 'canceled_after_revocation_during_verification',
101 'canceled_no_initiative_admitted'
102 ) THEN
103 'verification'::"notify_level"
104 ELSE
105 CASE WHEN "event"."state" IN (
106 'discussion',
107 'canceled_after_revocation_during_discussion'
108 ) THEN
109 'discussion'::"notify_level"
110 ELSE
111 'all'::"notify_level"
112 END
113 END
114 END AS "notify_level",
115 "event".*
116 FROM "member" CROSS JOIN "event"
117 LEFT JOIN "issue"
118 ON "event"."issue_id" = "issue"."id"
119 LEFT JOIN "membership"
120 ON "member"."id" = "membership"."member_id"
121 AND "issue"."area_id" = "membership"."area_id"
122 LEFT JOIN "interest"
123 ON "member"."id" = "interest"."member_id"
124 AND "event"."issue_id" = "interest"."issue_id"
125 LEFT JOIN "supporter"
126 ON "member"."id" = "supporter"."member_id"
127 AND "event"."initiative_id" = "supporter"."initiative_id"
128 LEFT JOIN "ignored_member"
129 ON "member"."id" = "ignored_member"."member_id"
130 AND "event"."member_id" = "ignored_member"."other_member_id"
131 LEFT JOIN "ignored_initiative"
132 ON "member"."id" = "ignored_initiative"."member_id"
133 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
134 WHERE (
135 "supporter"."member_id" NOTNULL OR
136 "interest"."member_id" NOTNULL OR
137 ( "membership"."member_id" NOTNULL AND
138 "event"."event" IN (
139 'issue_state_changed',
140 'initiative_created_in_new_issue',
141 'initiative_created_in_existing_issue',
142 'initiative_revoked' ) ) )
143 AND "ignored_member"."member_id" ISNULL
144 AND "ignored_initiative"."member_id" ISNULL;
145 COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support, but ignoring members "notify_level"';
146 CREATE VIEW "selected_event_seen_by_member" AS
147 SELECT
148 "member"."id" AS "seen_by_member_id",
149 CASE WHEN "event"."state" IN (
150 'voting',
151 'finished_without_winner',
152 'finished_with_winner'
153 ) THEN
154 'voting'::"notify_level"
155 ELSE
156 CASE WHEN "event"."state" IN (
157 'verification',
158 'canceled_after_revocation_during_verification',
159 'canceled_no_initiative_admitted'
160 ) THEN
161 'verification'::"notify_level"
162 ELSE
163 CASE WHEN "event"."state" IN (
164 'discussion',
165 'canceled_after_revocation_during_discussion'
166 ) THEN
167 'discussion'::"notify_level"
168 ELSE
169 'all'::"notify_level"
170 END
171 END
172 END AS "notify_level",
173 "event".*
174 FROM "member" CROSS JOIN "event"
175 LEFT JOIN "issue"
176 ON "event"."issue_id" = "issue"."id"
177 LEFT JOIN "membership"
178 ON "member"."id" = "membership"."member_id"
179 AND "issue"."area_id" = "membership"."area_id"
180 LEFT JOIN "interest"
181 ON "member"."id" = "interest"."member_id"
182 AND "event"."issue_id" = "interest"."issue_id"
183 LEFT JOIN "supporter"
184 ON "member"."id" = "supporter"."member_id"
185 AND "event"."initiative_id" = "supporter"."initiative_id"
186 LEFT JOIN "ignored_member"
187 ON "member"."id" = "ignored_member"."member_id"
188 AND "event"."member_id" = "ignored_member"."other_member_id"
189 LEFT JOIN "ignored_initiative"
190 ON "member"."id" = "ignored_initiative"."member_id"
191 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
192 WHERE (
193 ( "member"."notify_level" >= 'all' ) OR
194 ( "member"."notify_level" >= 'voting' AND
195 "event"."state" IN (
196 'voting',
197 'finished_without_winner',
198 'finished_with_winner' ) ) OR
199 ( "member"."notify_level" >= 'verification' AND
200 "event"."state" IN (
201 'verification',
202 'canceled_after_revocation_during_verification',
203 'canceled_no_initiative_admitted' ) ) OR
204 ( "member"."notify_level" >= 'discussion' AND
205 "event"."state" IN (
206 'discussion',
207 'canceled_after_revocation_during_discussion' ) ) )
208 AND (
209 "supporter"."member_id" NOTNULL OR
210 "interest"."member_id" NOTNULL OR
211 ( "membership"."member_id" NOTNULL AND
212 "event"."event" IN (
213 'issue_state_changed',
214 'initiative_created_in_new_issue',
215 'initiative_created_in_existing_issue',
216 'initiative_revoked' ) ) )
217 AND "ignored_member"."member_id" ISNULL
218 AND "ignored_initiative"."member_id" ISNULL;
219 COMMENT ON VIEW "selected_event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests, support and members "notify_level"';
221 ALTER TABLE "initiative" ADD COLUMN "harmonic_weight" NUMERIC(12, 3);
222 COMMENT ON COLUMN "initiative"."harmonic_weight" IS 'Indicates the relevancy of the initiative, calculated from the potential supporters weighted with the harmonic series to avoid a large number of clones affecting other initiative''s sorting positions too much; shall be used as secondary sorting key after "admitted" as primary sorting key';
224 CREATE OR REPLACE FUNCTION "write_event_issue_state_changed_trigger"()
225 RETURNS TRIGGER
226 LANGUAGE 'plpgsql' VOLATILE AS $$
227 BEGIN
228 IF NEW."state" != OLD."state" THEN
229 INSERT INTO "event" ("event", "issue_id", "state")
230 VALUES ('issue_state_changed', NEW."id", NEW."state");
231 END IF;
232 RETURN NULL;
233 END;
234 $$;
236 CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"()
237 RETURNS TRIGGER
238 LANGUAGE 'plpgsql' VOLATILE AS $$
239 DECLARE
240 "issue_id_v" "issue"."id"%TYPE;
241 "issue_row" "issue"%ROWTYPE;
242 BEGIN
243 IF TG_OP = 'DELETE' THEN
244 "issue_id_v" := OLD."issue_id";
245 ELSE
246 "issue_id_v" := NEW."issue_id";
247 END IF;
248 SELECT INTO "issue_row" * FROM "issue"
249 WHERE "id" = "issue_id_v" FOR SHARE;
250 IF "issue_row"."closed" NOTNULL THEN
251 IF
252 TG_RELID = 'direct_voter'::regclass AND
253 TG_OP = 'UPDATE'
254 THEN
255 IF
256 OLD."issue_id" = NEW."issue_id" AND
257 OLD."member_id" = NEW."member_id" AND
258 OLD."weight" = NEW."weight"
259 THEN
260 RETURN NULL; -- allows changing of voter comment
261 END IF;
262 END IF;
263 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
264 ELSIF
265 "issue_row"."state" = 'voting' AND
266 "issue_row"."phase_finished" NOTNULL
267 THEN
268 IF TG_RELID = 'vote'::regclass THEN
269 RAISE EXCEPTION 'Tried to modify data after voting has been closed.';
270 END IF;
271 END IF;
272 RETURN NULL;
273 END;
274 $$;
276 CREATE OR REPLACE VIEW "battle_view" AS
277 SELECT
278 "issue"."id" AS "issue_id",
279 "winning_initiative"."id" AS "winning_initiative_id",
280 "losing_initiative"."id" AS "losing_initiative_id",
281 sum(
282 CASE WHEN
283 coalesce("better_vote"."grade", 0) >
284 coalesce("worse_vote"."grade", 0)
285 THEN "direct_voter"."weight" ELSE 0 END
286 ) AS "count"
287 FROM "issue"
288 LEFT JOIN "direct_voter"
289 ON "issue"."id" = "direct_voter"."issue_id"
290 JOIN "battle_participant" AS "winning_initiative"
291 ON "issue"."id" = "winning_initiative"."issue_id"
292 JOIN "battle_participant" AS "losing_initiative"
293 ON "issue"."id" = "losing_initiative"."issue_id"
294 LEFT JOIN "vote" AS "better_vote"
295 ON "direct_voter"."member_id" = "better_vote"."member_id"
296 AND "winning_initiative"."id" = "better_vote"."initiative_id"
297 LEFT JOIN "vote" AS "worse_vote"
298 ON "direct_voter"."member_id" = "worse_vote"."member_id"
299 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
300 WHERE "issue"."state" = 'voting'
301 AND "issue"."phase_finished" NOTNULL
302 AND (
303 "winning_initiative"."id" != "losing_initiative"."id" OR
304 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
305 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
306 GROUP BY
307 "issue"."id",
308 "winning_initiative"."id",
309 "losing_initiative"."id";
311 DROP VIEW "timeline";
312 DROP VIEW "timeline_issue";
313 DROP VIEW "timeline_initiative";
314 DROP VIEW "timeline_draft";
315 DROP VIEW "timeline_suggestion";
316 DROP TYPE "timeline_event";
318 DROP TRIGGER "share_row_lock_issue" ON "initiative";
319 DROP TRIGGER "share_row_lock_issue" ON "interest";
320 DROP TRIGGER "share_row_lock_issue" ON "supporter";
321 DROP TRIGGER "share_row_lock_issue_via_initiative" ON "opinion";
322 DROP TRIGGER "share_row_lock_issue" ON "direct_voter";
323 DROP TRIGGER "share_row_lock_issue" ON "delegating_voter";
324 DROP TRIGGER "share_row_lock_issue" ON "vote";
325 DROP FUNCTION "share_row_lock_issue_trigger"();
327 CREATE FUNCTION "require_transaction_isolation"()
328 RETURNS VOID
329 LANGUAGE 'plpgsql' VOLATILE AS $$
330 BEGIN
331 IF
332 current_setting('transaction_isolation') NOT IN
333 ('repeatable read', 'serializable')
334 THEN
335 RAISE EXCEPTION 'Insufficient transaction isolation level';
336 END IF;
337 RETURN;
338 END;
339 $$;
340 COMMENT ON FUNCTION "require_transaction_isolation"() IS 'Throws an exception, if transaction isolation level is too low to provide a consistent snapshot';
342 CREATE FUNCTION "dont_require_transaction_isolation"()
343 RETURNS VOID
344 LANGUAGE 'plpgsql' VOLATILE AS $$
345 BEGIN
346 IF
347 current_setting('transaction_isolation') IN
348 ('repeatable read', 'serializable')
349 THEN
350 RAISE WARNING 'Unneccessary transaction isolation level: %',
351 current_setting('transaction_isolation');
352 END IF;
353 RETURN;
354 END;
355 $$;
356 COMMENT ON FUNCTION "dont_require_transaction_isolation"() IS 'Raises a warning, if transaction isolation level is higher than READ COMMITTED';
358 CREATE OR REPLACE FUNCTION "check_activity"()
359 RETURNS VOID
360 LANGUAGE 'plpgsql' VOLATILE AS $$
361 DECLARE
362 "system_setting_row" "system_setting"%ROWTYPE;
363 BEGIN
364 PERFORM "dont_require_transaction_isolation"();
365 SELECT * INTO "system_setting_row" FROM "system_setting";
366 IF "system_setting_row"."member_ttl" NOTNULL THEN
367 UPDATE "member" SET "active" = FALSE
368 WHERE "active" = TRUE
369 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
370 END IF;
371 RETURN;
372 END;
373 $$;
375 CREATE OR REPLACE FUNCTION "calculate_member_counts"()
376 RETURNS VOID
377 LANGUAGE 'plpgsql' VOLATILE AS $$
378 BEGIN
379 PERFORM "require_transaction_isolation"();
380 DELETE FROM "member_count";
381 INSERT INTO "member_count" ("total_count")
382 SELECT "total_count" FROM "member_count_view";
383 UPDATE "unit" SET "member_count" = "view"."member_count"
384 FROM "unit_member_count" AS "view"
385 WHERE "view"."unit_id" = "unit"."id";
386 UPDATE "area" SET
387 "direct_member_count" = "view"."direct_member_count",
388 "member_weight" = "view"."member_weight"
389 FROM "area_member_count" AS "view"
390 WHERE "view"."area_id" = "area"."id";
391 RETURN;
392 END;
393 $$;
395 CREATE VIEW "remaining_harmonic_supporter_weight" AS
396 SELECT
397 "direct_interest_snapshot"."issue_id",
398 "direct_interest_snapshot"."event",
399 "direct_interest_snapshot"."member_id",
400 "direct_interest_snapshot"."weight" AS "weight_num",
401 count("initiative"."id") AS "weight_den"
402 FROM "issue"
403 JOIN "direct_interest_snapshot"
404 ON "issue"."id" = "direct_interest_snapshot"."issue_id"
405 AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
406 JOIN "initiative"
407 ON "issue"."id" = "initiative"."issue_id"
408 AND "initiative"."harmonic_weight" ISNULL
409 JOIN "direct_supporter_snapshot"
410 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
411 AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event"
412 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
413 AND (
414 "direct_supporter_snapshot"."satisfied" = TRUE OR
415 coalesce("initiative"."admitted", FALSE) = FALSE
416 )
417 GROUP BY
418 "direct_interest_snapshot"."issue_id",
419 "direct_interest_snapshot"."event",
420 "direct_interest_snapshot"."member_id",
421 "direct_interest_snapshot"."weight";
422 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
424 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
425 SELECT
426 "initiative"."issue_id",
427 "initiative"."id" AS "initiative_id",
428 "initiative"."admitted",
429 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
430 "remaining_harmonic_supporter_weight"."weight_den"
431 FROM "remaining_harmonic_supporter_weight"
432 JOIN "initiative"
433 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
434 AND "initiative"."harmonic_weight" ISNULL
435 JOIN "direct_supporter_snapshot"
436 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
437 AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"
438 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
439 AND (
440 "direct_supporter_snapshot"."satisfied" = TRUE OR
441 coalesce("initiative"."admitted", FALSE) = FALSE
442 )
443 GROUP BY
444 "initiative"."issue_id",
445 "initiative"."id",
446 "initiative"."admitted",
447 "remaining_harmonic_supporter_weight"."weight_den";
448 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
450 CREATE VIEW "remaining_harmonic_initiative_weight_dummies" AS
451 SELECT
452 "issue_id",
453 "id" AS "initiative_id",
454 "admitted",
455 0 AS "weight_num",
456 1 AS "weight_den"
457 FROM "initiative"
458 WHERE "harmonic_weight" ISNULL;
459 COMMENT ON VIEW "remaining_harmonic_initiative_weight_dummies" IS 'Helper view for function "set_harmonic_initiative_weights" providing dummy weights of zero value, which are needed for corner cases where there are no supporters for an initiative at all';
461 CREATE FUNCTION "set_harmonic_initiative_weights"
462 ( "issue_id_p" "issue"."id"%TYPE )
463 RETURNS VOID
464 LANGUAGE 'plpgsql' VOLATILE AS $$
465 DECLARE
466 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
467 "i" INT4;
468 "count_v" INT4;
469 "summand_v" FLOAT;
470 "id_ary" INT4[];
471 "weight_ary" FLOAT[];
472 "min_weight_v" FLOAT;
473 BEGIN
474 PERFORM "require_transaction_isolation"();
475 UPDATE "initiative" SET "harmonic_weight" = NULL
476 WHERE "issue_id" = "issue_id_p";
477 LOOP
478 "min_weight_v" := NULL;
479 "i" := 0;
480 "count_v" := 0;
481 FOR "weight_row" IN
482 SELECT * FROM "remaining_harmonic_initiative_weight_summands"
483 WHERE "issue_id" = "issue_id_p"
484 AND (
485 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
486 SELECT NULL FROM "initiative"
487 WHERE "issue_id" = "issue_id_p"
488 AND "harmonic_weight" ISNULL
489 AND coalesce("admitted", FALSE) = FALSE
490 )
491 )
492 UNION ALL -- needed for corner cases
493 SELECT * FROM "remaining_harmonic_initiative_weight_dummies"
494 WHERE "issue_id" = "issue_id_p"
495 AND (
496 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
497 SELECT NULL FROM "initiative"
498 WHERE "issue_id" = "issue_id_p"
499 AND "harmonic_weight" ISNULL
500 AND coalesce("admitted", FALSE) = FALSE
501 )
502 )
503 ORDER BY "initiative_id" DESC, "weight_den" DESC
504 -- NOTE: non-admitted initiatives placed first (at last positions),
505 -- latest initiatives treated worse in case of tie
506 LOOP
507 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
508 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
509 "i" := "i" + 1;
510 "count_v" := "i";
511 "id_ary"["i"] := "weight_row"."initiative_id";
512 "weight_ary"["i"] := "summand_v";
513 ELSE
514 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
515 END IF;
516 END LOOP;
517 EXIT WHEN "count_v" = 0;
518 "i" := 1;
519 LOOP
520 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
521 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
522 "min_weight_v" := "weight_ary"["i"];
523 END IF;
524 "i" := "i" + 1;
525 EXIT WHEN "i" > "count_v";
526 END LOOP;
527 "i" := 1;
528 LOOP
529 IF "weight_ary"["i"] = "min_weight_v" THEN
530 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
531 WHERE "id" = "id_ary"["i"];
532 EXIT;
533 END IF;
534 "i" := "i" + 1;
535 END LOOP;
536 END LOOP;
537 UPDATE "initiative" SET "harmonic_weight" = 0
538 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
539 END;
540 $$;
541 COMMENT ON FUNCTION "set_harmonic_initiative_weights"
542 ( "issue"."id"%TYPE )
543 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
545 CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_population_snapshot"
546 ( "issue_id_p" "issue"."id"%TYPE,
547 "member_id_p" "member"."id"%TYPE,
548 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
549 RETURNS "direct_population_snapshot"."weight"%TYPE
550 LANGUAGE 'plpgsql' VOLATILE AS $$
551 DECLARE
552 "issue_delegation_row" "issue_delegation"%ROWTYPE;
553 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
554 "weight_v" INT4;
555 "sub_weight_v" INT4;
556 BEGIN
557 PERFORM "require_transaction_isolation"();
558 "weight_v" := 0;
559 FOR "issue_delegation_row" IN
560 SELECT * FROM "issue_delegation"
561 WHERE "trustee_id" = "member_id_p"
562 AND "issue_id" = "issue_id_p"
563 LOOP
564 IF NOT EXISTS (
565 SELECT NULL FROM "direct_population_snapshot"
566 WHERE "issue_id" = "issue_id_p"
567 AND "event" = 'periodic'
568 AND "member_id" = "issue_delegation_row"."truster_id"
569 ) AND NOT EXISTS (
570 SELECT NULL FROM "delegating_population_snapshot"
571 WHERE "issue_id" = "issue_id_p"
572 AND "event" = 'periodic'
573 AND "member_id" = "issue_delegation_row"."truster_id"
574 ) THEN
575 "delegate_member_ids_v" :=
576 "member_id_p" || "delegate_member_ids_p";
577 INSERT INTO "delegating_population_snapshot" (
578 "issue_id",
579 "event",
580 "member_id",
581 "scope",
582 "delegate_member_ids"
583 ) VALUES (
584 "issue_id_p",
585 'periodic',
586 "issue_delegation_row"."truster_id",
587 "issue_delegation_row"."scope",
588 "delegate_member_ids_v"
589 );
590 "sub_weight_v" := 1 +
591 "weight_of_added_delegations_for_population_snapshot"(
592 "issue_id_p",
593 "issue_delegation_row"."truster_id",
594 "delegate_member_ids_v"
595 );
596 UPDATE "delegating_population_snapshot"
597 SET "weight" = "sub_weight_v"
598 WHERE "issue_id" = "issue_id_p"
599 AND "event" = 'periodic'
600 AND "member_id" = "issue_delegation_row"."truster_id";
601 "weight_v" := "weight_v" + "sub_weight_v";
602 END IF;
603 END LOOP;
604 RETURN "weight_v";
605 END;
606 $$;
608 CREATE OR REPLACE FUNCTION "create_population_snapshot"
609 ( "issue_id_p" "issue"."id"%TYPE )
610 RETURNS VOID
611 LANGUAGE 'plpgsql' VOLATILE AS $$
612 DECLARE
613 "member_id_v" "member"."id"%TYPE;
614 BEGIN
615 PERFORM "require_transaction_isolation"();
616 DELETE FROM "direct_population_snapshot"
617 WHERE "issue_id" = "issue_id_p"
618 AND "event" = 'periodic';
619 DELETE FROM "delegating_population_snapshot"
620 WHERE "issue_id" = "issue_id_p"
621 AND "event" = 'periodic';
622 INSERT INTO "direct_population_snapshot"
623 ("issue_id", "event", "member_id")
624 SELECT
625 "issue_id_p" AS "issue_id",
626 'periodic'::"snapshot_event" AS "event",
627 "member"."id" AS "member_id"
628 FROM "issue"
629 JOIN "area" ON "issue"."area_id" = "area"."id"
630 JOIN "membership" ON "area"."id" = "membership"."area_id"
631 JOIN "member" ON "membership"."member_id" = "member"."id"
632 JOIN "privilege"
633 ON "privilege"."unit_id" = "area"."unit_id"
634 AND "privilege"."member_id" = "member"."id"
635 WHERE "issue"."id" = "issue_id_p"
636 AND "member"."active" AND "privilege"."voting_right"
637 UNION
638 SELECT
639 "issue_id_p" AS "issue_id",
640 'periodic'::"snapshot_event" AS "event",
641 "member"."id" AS "member_id"
642 FROM "issue"
643 JOIN "area" ON "issue"."area_id" = "area"."id"
644 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
645 JOIN "member" ON "interest"."member_id" = "member"."id"
646 JOIN "privilege"
647 ON "privilege"."unit_id" = "area"."unit_id"
648 AND "privilege"."member_id" = "member"."id"
649 WHERE "issue"."id" = "issue_id_p"
650 AND "member"."active" AND "privilege"."voting_right";
651 FOR "member_id_v" IN
652 SELECT "member_id" FROM "direct_population_snapshot"
653 WHERE "issue_id" = "issue_id_p"
654 AND "event" = 'periodic'
655 LOOP
656 UPDATE "direct_population_snapshot" SET
657 "weight" = 1 +
658 "weight_of_added_delegations_for_population_snapshot"(
659 "issue_id_p",
660 "member_id_v",
661 '{}'
662 )
663 WHERE "issue_id" = "issue_id_p"
664 AND "event" = 'periodic'
665 AND "member_id" = "member_id_v";
666 END LOOP;
667 RETURN;
668 END;
669 $$;
671 CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
672 ( "issue_id_p" "issue"."id"%TYPE,
673 "member_id_p" "member"."id"%TYPE,
674 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
675 RETURNS "direct_interest_snapshot"."weight"%TYPE
676 LANGUAGE 'plpgsql' VOLATILE AS $$
677 DECLARE
678 "issue_delegation_row" "issue_delegation"%ROWTYPE;
679 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
680 "weight_v" INT4;
681 "sub_weight_v" INT4;
682 BEGIN
683 PERFORM "require_transaction_isolation"();
684 "weight_v" := 0;
685 FOR "issue_delegation_row" IN
686 SELECT * FROM "issue_delegation"
687 WHERE "trustee_id" = "member_id_p"
688 AND "issue_id" = "issue_id_p"
689 LOOP
690 IF NOT EXISTS (
691 SELECT NULL FROM "direct_interest_snapshot"
692 WHERE "issue_id" = "issue_id_p"
693 AND "event" = 'periodic'
694 AND "member_id" = "issue_delegation_row"."truster_id"
695 ) AND NOT EXISTS (
696 SELECT NULL FROM "delegating_interest_snapshot"
697 WHERE "issue_id" = "issue_id_p"
698 AND "event" = 'periodic'
699 AND "member_id" = "issue_delegation_row"."truster_id"
700 ) THEN
701 "delegate_member_ids_v" :=
702 "member_id_p" || "delegate_member_ids_p";
703 INSERT INTO "delegating_interest_snapshot" (
704 "issue_id",
705 "event",
706 "member_id",
707 "scope",
708 "delegate_member_ids"
709 ) VALUES (
710 "issue_id_p",
711 'periodic',
712 "issue_delegation_row"."truster_id",
713 "issue_delegation_row"."scope",
714 "delegate_member_ids_v"
715 );
716 "sub_weight_v" := 1 +
717 "weight_of_added_delegations_for_interest_snapshot"(
718 "issue_id_p",
719 "issue_delegation_row"."truster_id",
720 "delegate_member_ids_v"
721 );
722 UPDATE "delegating_interest_snapshot"
723 SET "weight" = "sub_weight_v"
724 WHERE "issue_id" = "issue_id_p"
725 AND "event" = 'periodic'
726 AND "member_id" = "issue_delegation_row"."truster_id";
727 "weight_v" := "weight_v" + "sub_weight_v";
728 END IF;
729 END LOOP;
730 RETURN "weight_v";
731 END;
732 $$;
734 CREATE OR REPLACE FUNCTION "create_interest_snapshot"
735 ( "issue_id_p" "issue"."id"%TYPE )
736 RETURNS VOID
737 LANGUAGE 'plpgsql' VOLATILE AS $$
738 DECLARE
739 "member_id_v" "member"."id"%TYPE;
740 BEGIN
741 PERFORM "require_transaction_isolation"();
742 DELETE FROM "direct_interest_snapshot"
743 WHERE "issue_id" = "issue_id_p"
744 AND "event" = 'periodic';
745 DELETE FROM "delegating_interest_snapshot"
746 WHERE "issue_id" = "issue_id_p"
747 AND "event" = 'periodic';
748 DELETE FROM "direct_supporter_snapshot"
749 USING "initiative" -- NOTE: due to missing index on issue_id
750 WHERE "initiative"."issue_id" = "issue_id_p"
751 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
752 AND "direct_supporter_snapshot"."event" = 'periodic';
753 INSERT INTO "direct_interest_snapshot"
754 ("issue_id", "event", "member_id")
755 SELECT
756 "issue_id_p" AS "issue_id",
757 'periodic' AS "event",
758 "member"."id" AS "member_id"
759 FROM "issue"
760 JOIN "area" ON "issue"."area_id" = "area"."id"
761 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
762 JOIN "member" ON "interest"."member_id" = "member"."id"
763 JOIN "privilege"
764 ON "privilege"."unit_id" = "area"."unit_id"
765 AND "privilege"."member_id" = "member"."id"
766 WHERE "issue"."id" = "issue_id_p"
767 AND "member"."active" AND "privilege"."voting_right";
768 FOR "member_id_v" IN
769 SELECT "member_id" FROM "direct_interest_snapshot"
770 WHERE "issue_id" = "issue_id_p"
771 AND "event" = 'periodic'
772 LOOP
773 UPDATE "direct_interest_snapshot" SET
774 "weight" = 1 +
775 "weight_of_added_delegations_for_interest_snapshot"(
776 "issue_id_p",
777 "member_id_v",
778 '{}'
779 )
780 WHERE "issue_id" = "issue_id_p"
781 AND "event" = 'periodic'
782 AND "member_id" = "member_id_v";
783 END LOOP;
784 INSERT INTO "direct_supporter_snapshot"
785 ( "issue_id", "initiative_id", "event", "member_id",
786 "draft_id", "informed", "satisfied" )
787 SELECT
788 "issue_id_p" AS "issue_id",
789 "initiative"."id" AS "initiative_id",
790 'periodic' AS "event",
791 "supporter"."member_id" AS "member_id",
792 "supporter"."draft_id" AS "draft_id",
793 "supporter"."draft_id" = "current_draft"."id" AS "informed",
794 NOT EXISTS (
795 SELECT NULL FROM "critical_opinion"
796 WHERE "initiative_id" = "initiative"."id"
797 AND "member_id" = "supporter"."member_id"
798 ) AS "satisfied"
799 FROM "initiative"
800 JOIN "supporter"
801 ON "supporter"."initiative_id" = "initiative"."id"
802 JOIN "current_draft"
803 ON "initiative"."id" = "current_draft"."initiative_id"
804 JOIN "direct_interest_snapshot"
805 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
806 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
807 AND "event" = 'periodic'
808 WHERE "initiative"."issue_id" = "issue_id_p";
809 RETURN;
810 END;
811 $$;
813 CREATE OR REPLACE FUNCTION "create_snapshot"
814 ( "issue_id_p" "issue"."id"%TYPE )
815 RETURNS VOID
816 LANGUAGE 'plpgsql' VOLATILE AS $$
817 DECLARE
818 "initiative_id_v" "initiative"."id"%TYPE;
819 "suggestion_id_v" "suggestion"."id"%TYPE;
820 BEGIN
821 PERFORM "require_transaction_isolation"();
822 PERFORM "create_population_snapshot"("issue_id_p");
823 PERFORM "create_interest_snapshot"("issue_id_p");
824 UPDATE "issue" SET
825 "snapshot" = coalesce("phase_finished", now()),
826 "latest_snapshot_event" = 'periodic',
827 "population" = (
828 SELECT coalesce(sum("weight"), 0)
829 FROM "direct_population_snapshot"
830 WHERE "issue_id" = "issue_id_p"
831 AND "event" = 'periodic'
832 )
833 WHERE "id" = "issue_id_p";
834 FOR "initiative_id_v" IN
835 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
836 LOOP
837 UPDATE "initiative" SET
838 "supporter_count" = (
839 SELECT coalesce(sum("di"."weight"), 0)
840 FROM "direct_interest_snapshot" AS "di"
841 JOIN "direct_supporter_snapshot" AS "ds"
842 ON "di"."member_id" = "ds"."member_id"
843 WHERE "di"."issue_id" = "issue_id_p"
844 AND "di"."event" = 'periodic'
845 AND "ds"."initiative_id" = "initiative_id_v"
846 AND "ds"."event" = 'periodic'
847 ),
848 "informed_supporter_count" = (
849 SELECT coalesce(sum("di"."weight"), 0)
850 FROM "direct_interest_snapshot" AS "di"
851 JOIN "direct_supporter_snapshot" AS "ds"
852 ON "di"."member_id" = "ds"."member_id"
853 WHERE "di"."issue_id" = "issue_id_p"
854 AND "di"."event" = 'periodic'
855 AND "ds"."initiative_id" = "initiative_id_v"
856 AND "ds"."event" = 'periodic'
857 AND "ds"."informed"
858 ),
859 "satisfied_supporter_count" = (
860 SELECT coalesce(sum("di"."weight"), 0)
861 FROM "direct_interest_snapshot" AS "di"
862 JOIN "direct_supporter_snapshot" AS "ds"
863 ON "di"."member_id" = "ds"."member_id"
864 WHERE "di"."issue_id" = "issue_id_p"
865 AND "di"."event" = 'periodic'
866 AND "ds"."initiative_id" = "initiative_id_v"
867 AND "ds"."event" = 'periodic'
868 AND "ds"."satisfied"
869 ),
870 "satisfied_informed_supporter_count" = (
871 SELECT coalesce(sum("di"."weight"), 0)
872 FROM "direct_interest_snapshot" AS "di"
873 JOIN "direct_supporter_snapshot" AS "ds"
874 ON "di"."member_id" = "ds"."member_id"
875 WHERE "di"."issue_id" = "issue_id_p"
876 AND "di"."event" = 'periodic'
877 AND "ds"."initiative_id" = "initiative_id_v"
878 AND "ds"."event" = 'periodic'
879 AND "ds"."informed"
880 AND "ds"."satisfied"
881 )
882 WHERE "id" = "initiative_id_v";
883 FOR "suggestion_id_v" IN
884 SELECT "id" FROM "suggestion"
885 WHERE "initiative_id" = "initiative_id_v"
886 LOOP
887 UPDATE "suggestion" SET
888 "minus2_unfulfilled_count" = (
889 SELECT coalesce(sum("snapshot"."weight"), 0)
890 FROM "issue" CROSS JOIN "opinion"
891 JOIN "direct_interest_snapshot" AS "snapshot"
892 ON "snapshot"."issue_id" = "issue"."id"
893 AND "snapshot"."event" = "issue"."latest_snapshot_event"
894 AND "snapshot"."member_id" = "opinion"."member_id"
895 WHERE "issue"."id" = "issue_id_p"
896 AND "opinion"."suggestion_id" = "suggestion_id_v"
897 AND "opinion"."degree" = -2
898 AND "opinion"."fulfilled" = FALSE
899 ),
900 "minus2_fulfilled_count" = (
901 SELECT coalesce(sum("snapshot"."weight"), 0)
902 FROM "issue" CROSS JOIN "opinion"
903 JOIN "direct_interest_snapshot" AS "snapshot"
904 ON "snapshot"."issue_id" = "issue"."id"
905 AND "snapshot"."event" = "issue"."latest_snapshot_event"
906 AND "snapshot"."member_id" = "opinion"."member_id"
907 WHERE "issue"."id" = "issue_id_p"
908 AND "opinion"."suggestion_id" = "suggestion_id_v"
909 AND "opinion"."degree" = -2
910 AND "opinion"."fulfilled" = TRUE
911 ),
912 "minus1_unfulfilled_count" = (
913 SELECT coalesce(sum("snapshot"."weight"), 0)
914 FROM "issue" CROSS JOIN "opinion"
915 JOIN "direct_interest_snapshot" AS "snapshot"
916 ON "snapshot"."issue_id" = "issue"."id"
917 AND "snapshot"."event" = "issue"."latest_snapshot_event"
918 AND "snapshot"."member_id" = "opinion"."member_id"
919 WHERE "issue"."id" = "issue_id_p"
920 AND "opinion"."suggestion_id" = "suggestion_id_v"
921 AND "opinion"."degree" = -1
922 AND "opinion"."fulfilled" = FALSE
923 ),
924 "minus1_fulfilled_count" = (
925 SELECT coalesce(sum("snapshot"."weight"), 0)
926 FROM "issue" CROSS JOIN "opinion"
927 JOIN "direct_interest_snapshot" AS "snapshot"
928 ON "snapshot"."issue_id" = "issue"."id"
929 AND "snapshot"."event" = "issue"."latest_snapshot_event"
930 AND "snapshot"."member_id" = "opinion"."member_id"
931 WHERE "issue"."id" = "issue_id_p"
932 AND "opinion"."suggestion_id" = "suggestion_id_v"
933 AND "opinion"."degree" = -1
934 AND "opinion"."fulfilled" = TRUE
935 ),
936 "plus1_unfulfilled_count" = (
937 SELECT coalesce(sum("snapshot"."weight"), 0)
938 FROM "issue" CROSS JOIN "opinion"
939 JOIN "direct_interest_snapshot" AS "snapshot"
940 ON "snapshot"."issue_id" = "issue"."id"
941 AND "snapshot"."event" = "issue"."latest_snapshot_event"
942 AND "snapshot"."member_id" = "opinion"."member_id"
943 WHERE "issue"."id" = "issue_id_p"
944 AND "opinion"."suggestion_id" = "suggestion_id_v"
945 AND "opinion"."degree" = 1
946 AND "opinion"."fulfilled" = FALSE
947 ),
948 "plus1_fulfilled_count" = (
949 SELECT coalesce(sum("snapshot"."weight"), 0)
950 FROM "issue" CROSS JOIN "opinion"
951 JOIN "direct_interest_snapshot" AS "snapshot"
952 ON "snapshot"."issue_id" = "issue"."id"
953 AND "snapshot"."event" = "issue"."latest_snapshot_event"
954 AND "snapshot"."member_id" = "opinion"."member_id"
955 WHERE "issue"."id" = "issue_id_p"
956 AND "opinion"."suggestion_id" = "suggestion_id_v"
957 AND "opinion"."degree" = 1
958 AND "opinion"."fulfilled" = TRUE
959 ),
960 "plus2_unfulfilled_count" = (
961 SELECT coalesce(sum("snapshot"."weight"), 0)
962 FROM "issue" CROSS JOIN "opinion"
963 JOIN "direct_interest_snapshot" AS "snapshot"
964 ON "snapshot"."issue_id" = "issue"."id"
965 AND "snapshot"."event" = "issue"."latest_snapshot_event"
966 AND "snapshot"."member_id" = "opinion"."member_id"
967 WHERE "issue"."id" = "issue_id_p"
968 AND "opinion"."suggestion_id" = "suggestion_id_v"
969 AND "opinion"."degree" = 2
970 AND "opinion"."fulfilled" = FALSE
971 ),
972 "plus2_fulfilled_count" = (
973 SELECT coalesce(sum("snapshot"."weight"), 0)
974 FROM "issue" CROSS JOIN "opinion"
975 JOIN "direct_interest_snapshot" AS "snapshot"
976 ON "snapshot"."issue_id" = "issue"."id"
977 AND "snapshot"."event" = "issue"."latest_snapshot_event"
978 AND "snapshot"."member_id" = "opinion"."member_id"
979 WHERE "issue"."id" = "issue_id_p"
980 AND "opinion"."suggestion_id" = "suggestion_id_v"
981 AND "opinion"."degree" = 2
982 AND "opinion"."fulfilled" = TRUE
983 )
984 WHERE "suggestion"."id" = "suggestion_id_v";
985 END LOOP;
986 END LOOP;
987 RETURN;
988 END;
989 $$;
991 CREATE OR REPLACE FUNCTION "set_snapshot_event"
992 ( "issue_id_p" "issue"."id"%TYPE,
993 "event_p" "snapshot_event" )
994 RETURNS VOID
995 LANGUAGE 'plpgsql' VOLATILE AS $$
996 DECLARE
997 "event_v" "issue"."latest_snapshot_event"%TYPE;
998 BEGIN
999 PERFORM "require_transaction_isolation"();
1000 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
1001 WHERE "id" = "issue_id_p" FOR UPDATE;
1002 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
1003 WHERE "id" = "issue_id_p";
1004 UPDATE "direct_population_snapshot" SET "event" = "event_p"
1005 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
1006 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
1007 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
1008 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
1009 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
1010 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
1011 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
1012 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
1013 FROM "initiative" -- NOTE: due to missing index on issue_id
1014 WHERE "initiative"."issue_id" = "issue_id_p"
1015 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
1016 AND "direct_supporter_snapshot"."event" = "event_v";
1017 RETURN;
1018 END;
1019 $$;
1021 DROP FUNCTION "freeze_after_snapshot"("issue"."id"%TYPE);
1022 DROP FUNCTION "manual_freeze"("issue"."id"%TYPE);
1024 CREATE OR REPLACE FUNCTION "weight_of_added_vote_delegations"
1025 ( "issue_id_p" "issue"."id"%TYPE,
1026 "member_id_p" "member"."id"%TYPE,
1027 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
1028 RETURNS "direct_voter"."weight"%TYPE
1029 LANGUAGE 'plpgsql' VOLATILE AS $$
1030 DECLARE
1031 "issue_delegation_row" "issue_delegation"%ROWTYPE;
1032 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
1033 "weight_v" INT4;
1034 "sub_weight_v" INT4;
1035 BEGIN
1036 PERFORM "require_transaction_isolation"();
1037 "weight_v" := 0;
1038 FOR "issue_delegation_row" IN
1039 SELECT * FROM "issue_delegation"
1040 WHERE "trustee_id" = "member_id_p"
1041 AND "issue_id" = "issue_id_p"
1042 LOOP
1043 IF NOT EXISTS (
1044 SELECT NULL FROM "direct_voter"
1045 WHERE "member_id" = "issue_delegation_row"."truster_id"
1046 AND "issue_id" = "issue_id_p"
1047 ) AND NOT EXISTS (
1048 SELECT NULL FROM "delegating_voter"
1049 WHERE "member_id" = "issue_delegation_row"."truster_id"
1050 AND "issue_id" = "issue_id_p"
1051 ) THEN
1052 "delegate_member_ids_v" :=
1053 "member_id_p" || "delegate_member_ids_p";
1054 INSERT INTO "delegating_voter" (
1055 "issue_id",
1056 "member_id",
1057 "scope",
1058 "delegate_member_ids"
1059 ) VALUES (
1060 "issue_id_p",
1061 "issue_delegation_row"."truster_id",
1062 "issue_delegation_row"."scope",
1063 "delegate_member_ids_v"
1064 );
1065 "sub_weight_v" := 1 +
1066 "weight_of_added_vote_delegations"(
1067 "issue_id_p",
1068 "issue_delegation_row"."truster_id",
1069 "delegate_member_ids_v"
1070 );
1071 UPDATE "delegating_voter"
1072 SET "weight" = "sub_weight_v"
1073 WHERE "issue_id" = "issue_id_p"
1074 AND "member_id" = "issue_delegation_row"."truster_id";
1075 "weight_v" := "weight_v" + "sub_weight_v";
1076 END IF;
1077 END LOOP;
1078 RETURN "weight_v";
1079 END;
1080 $$;
1082 CREATE OR REPLACE FUNCTION "add_vote_delegations"
1083 ( "issue_id_p" "issue"."id"%TYPE )
1084 RETURNS VOID
1085 LANGUAGE 'plpgsql' VOLATILE AS $$
1086 DECLARE
1087 "member_id_v" "member"."id"%TYPE;
1088 BEGIN
1089 PERFORM "require_transaction_isolation"();
1090 FOR "member_id_v" IN
1091 SELECT "member_id" FROM "direct_voter"
1092 WHERE "issue_id" = "issue_id_p"
1093 LOOP
1094 UPDATE "direct_voter" SET
1095 "weight" = "weight" + "weight_of_added_vote_delegations"(
1096 "issue_id_p",
1097 "member_id_v",
1098 '{}'
1099 )
1100 WHERE "member_id" = "member_id_v"
1101 AND "issue_id" = "issue_id_p";
1102 END LOOP;
1103 RETURN;
1104 END;
1105 $$;
1107 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
1108 RETURNS VOID
1109 LANGUAGE 'plpgsql' VOLATILE AS $$
1110 DECLARE
1111 "area_id_v" "area"."id"%TYPE;
1112 "unit_id_v" "unit"."id"%TYPE;
1113 "member_id_v" "member"."id"%TYPE;
1114 BEGIN
1115 PERFORM "require_transaction_isolation"();
1116 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
1117 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
1118 -- delete timestamp of voting comment:
1119 UPDATE "direct_voter" SET "comment_changed" = NULL
1120 WHERE "issue_id" = "issue_id_p";
1121 -- delete delegating votes (in cases of manual reset of issue state):
1122 DELETE FROM "delegating_voter"
1123 WHERE "issue_id" = "issue_id_p";
1124 -- delete votes from non-privileged voters:
1125 DELETE FROM "direct_voter"
1126 USING (
1127 SELECT
1128 "direct_voter"."member_id"
1129 FROM "direct_voter"
1130 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
1131 LEFT JOIN "privilege"
1132 ON "privilege"."unit_id" = "unit_id_v"
1133 AND "privilege"."member_id" = "direct_voter"."member_id"
1134 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
1135 "member"."active" = FALSE OR
1136 "privilege"."voting_right" ISNULL OR
1137 "privilege"."voting_right" = FALSE
1138 )
1139 ) AS "subquery"
1140 WHERE "direct_voter"."issue_id" = "issue_id_p"
1141 AND "direct_voter"."member_id" = "subquery"."member_id";
1142 -- consider delegations:
1143 UPDATE "direct_voter" SET "weight" = 1
1144 WHERE "issue_id" = "issue_id_p";
1145 PERFORM "add_vote_delegations"("issue_id_p");
1146 -- materialize battle_view:
1147 -- NOTE: "closed" column of issue must be set at this point
1148 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
1149 INSERT INTO "battle" (
1150 "issue_id",
1151 "winning_initiative_id", "losing_initiative_id",
1152 "count"
1153 ) SELECT
1154 "issue_id",
1155 "winning_initiative_id", "losing_initiative_id",
1156 "count"
1157 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
1158 -- set voter count:
1159 UPDATE "issue" SET
1160 "voter_count" = (
1161 SELECT coalesce(sum("weight"), 0)
1162 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
1163 )
1164 WHERE "id" = "issue_id_p";
1165 -- copy "positive_votes" and "negative_votes" from "battle" table:
1166 UPDATE "initiative" SET
1167 "positive_votes" = "battle_win"."count",
1168 "negative_votes" = "battle_lose"."count"
1169 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
1170 WHERE
1171 "battle_win"."issue_id" = "issue_id_p" AND
1172 "battle_win"."winning_initiative_id" = "initiative"."id" AND
1173 "battle_win"."losing_initiative_id" ISNULL AND
1174 "battle_lose"."issue_id" = "issue_id_p" AND
1175 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
1176 "battle_lose"."winning_initiative_id" ISNULL;
1177 END;
1178 $$;
1180 CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
1181 RETURNS VOID
1182 LANGUAGE 'plpgsql' VOLATILE AS $$
1183 DECLARE
1184 "issue_row" "issue"%ROWTYPE;
1185 "policy_row" "policy"%ROWTYPE;
1186 "dimension_v" INTEGER;
1187 "vote_matrix" INT4[][]; -- absolute votes
1188 "matrix" INT8[][]; -- defeat strength / best paths
1189 "i" INTEGER;
1190 "j" INTEGER;
1191 "k" INTEGER;
1192 "battle_row" "battle"%ROWTYPE;
1193 "rank_ary" INT4[];
1194 "rank_v" INT4;
1195 "done_v" INTEGER;
1196 "winners_ary" INTEGER[];
1197 "initiative_id_v" "initiative"."id"%TYPE;
1198 BEGIN
1199 PERFORM "require_transaction_isolation"();
1200 SELECT * INTO "issue_row"
1201 FROM "issue" WHERE "id" = "issue_id_p";
1202 SELECT * INTO "policy_row"
1203 FROM "policy" WHERE "id" = "issue_row"."policy_id";
1204 SELECT count(1) INTO "dimension_v"
1205 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
1206 -- Create "vote_matrix" with absolute number of votes in pairwise
1207 -- comparison:
1208 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
1209 "i" := 1;
1210 "j" := 2;
1211 FOR "battle_row" IN
1212 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
1213 ORDER BY
1214 "winning_initiative_id" NULLS LAST,
1215 "losing_initiative_id" NULLS LAST
1216 LOOP
1217 "vote_matrix"["i"]["j"] := "battle_row"."count";
1218 IF "j" = "dimension_v" THEN
1219 "i" := "i" + 1;
1220 "j" := 1;
1221 ELSE
1222 "j" := "j" + 1;
1223 IF "j" = "i" THEN
1224 "j" := "j" + 1;
1225 END IF;
1226 END IF;
1227 END LOOP;
1228 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
1229 RAISE EXCEPTION 'Wrong battle count (should not happen)';
1230 END IF;
1231 -- Store defeat strengths in "matrix" using "defeat_strength"
1232 -- function:
1233 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
1234 "i" := 1;
1235 LOOP
1236 "j" := 1;
1237 LOOP
1238 IF "i" != "j" THEN
1239 "matrix"["i"]["j"] := "defeat_strength"(
1240 "vote_matrix"["i"]["j"],
1241 "vote_matrix"["j"]["i"]
1242 );
1243 END IF;
1244 EXIT WHEN "j" = "dimension_v";
1245 "j" := "j" + 1;
1246 END LOOP;
1247 EXIT WHEN "i" = "dimension_v";
1248 "i" := "i" + 1;
1249 END LOOP;
1250 -- Find best paths:
1251 "i" := 1;
1252 LOOP
1253 "j" := 1;
1254 LOOP
1255 IF "i" != "j" THEN
1256 "k" := 1;
1257 LOOP
1258 IF "i" != "k" AND "j" != "k" THEN
1259 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
1260 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
1261 "matrix"["j"]["k"] := "matrix"["j"]["i"];
1262 END IF;
1263 ELSE
1264 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
1265 "matrix"["j"]["k"] := "matrix"["i"]["k"];
1266 END IF;
1267 END IF;
1268 END IF;
1269 EXIT WHEN "k" = "dimension_v";
1270 "k" := "k" + 1;
1271 END LOOP;
1272 END IF;
1273 EXIT WHEN "j" = "dimension_v";
1274 "j" := "j" + 1;
1275 END LOOP;
1276 EXIT WHEN "i" = "dimension_v";
1277 "i" := "i" + 1;
1278 END LOOP;
1279 -- Determine order of winners:
1280 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
1281 "rank_v" := 1;
1282 "done_v" := 0;
1283 LOOP
1284 "winners_ary" := '{}';
1285 "i" := 1;
1286 LOOP
1287 IF "rank_ary"["i"] ISNULL THEN
1288 "j" := 1;
1289 LOOP
1290 IF
1291 "i" != "j" AND
1292 "rank_ary"["j"] ISNULL AND
1293 "matrix"["j"]["i"] > "matrix"["i"]["j"]
1294 THEN
1295 -- someone else is better
1296 EXIT;
1297 END IF;
1298 IF "j" = "dimension_v" THEN
1299 -- noone is better
1300 "winners_ary" := "winners_ary" || "i";
1301 EXIT;
1302 END IF;
1303 "j" := "j" + 1;
1304 END LOOP;
1305 END IF;
1306 EXIT WHEN "i" = "dimension_v";
1307 "i" := "i" + 1;
1308 END LOOP;
1309 "i" := 1;
1310 LOOP
1311 "rank_ary"["winners_ary"["i"]] := "rank_v";
1312 "done_v" := "done_v" + 1;
1313 EXIT WHEN "i" = array_upper("winners_ary", 1);
1314 "i" := "i" + 1;
1315 END LOOP;
1316 EXIT WHEN "done_v" = "dimension_v";
1317 "rank_v" := "rank_v" + 1;
1318 END LOOP;
1319 -- write preliminary results:
1320 "i" := 1;
1321 FOR "initiative_id_v" IN
1322 SELECT "id" FROM "initiative"
1323 WHERE "issue_id" = "issue_id_p" AND "admitted"
1324 ORDER BY "id"
1325 LOOP
1326 UPDATE "initiative" SET
1327 "direct_majority" =
1328 CASE WHEN "policy_row"."direct_majority_strict" THEN
1329 "positive_votes" * "policy_row"."direct_majority_den" >
1330 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
1331 ELSE
1332 "positive_votes" * "policy_row"."direct_majority_den" >=
1333 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
1334 END
1335 AND "positive_votes" >= "policy_row"."direct_majority_positive"
1336 AND "issue_row"."voter_count"-"negative_votes" >=
1337 "policy_row"."direct_majority_non_negative",
1338 "indirect_majority" =
1339 CASE WHEN "policy_row"."indirect_majority_strict" THEN
1340 "positive_votes" * "policy_row"."indirect_majority_den" >
1341 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
1342 ELSE
1343 "positive_votes" * "policy_row"."indirect_majority_den" >=
1344 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
1345 END
1346 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
1347 AND "issue_row"."voter_count"-"negative_votes" >=
1348 "policy_row"."indirect_majority_non_negative",
1349 "schulze_rank" = "rank_ary"["i"],
1350 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
1351 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
1352 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
1353 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,
1354 "eligible" = FALSE,
1355 "winner" = FALSE,
1356 "rank" = NULL -- NOTE: in cases of manual reset of issue state
1357 WHERE "id" = "initiative_id_v";
1358 "i" := "i" + 1;
1359 END LOOP;
1360 IF "i" != "dimension_v" THEN
1361 RAISE EXCEPTION 'Wrong winner count (should not happen)';
1362 END IF;
1363 -- take indirect majorities into account:
1364 LOOP
1365 UPDATE "initiative" SET "indirect_majority" = TRUE
1366 FROM (
1367 SELECT "new_initiative"."id" AS "initiative_id"
1368 FROM "initiative" "old_initiative"
1369 JOIN "initiative" "new_initiative"
1370 ON "new_initiative"."issue_id" = "issue_id_p"
1371 AND "new_initiative"."indirect_majority" = FALSE
1372 JOIN "battle" "battle_win"
1373 ON "battle_win"."issue_id" = "issue_id_p"
1374 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
1375 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
1376 JOIN "battle" "battle_lose"
1377 ON "battle_lose"."issue_id" = "issue_id_p"
1378 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
1379 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
1380 WHERE "old_initiative"."issue_id" = "issue_id_p"
1381 AND "old_initiative"."indirect_majority" = TRUE
1382 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
1383 "battle_win"."count" * "policy_row"."indirect_majority_den" >
1384 "policy_row"."indirect_majority_num" *
1385 ("battle_win"."count"+"battle_lose"."count")
1386 ELSE
1387 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
1388 "policy_row"."indirect_majority_num" *
1389 ("battle_win"."count"+"battle_lose"."count")
1390 END
1391 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
1392 AND "issue_row"."voter_count"-"battle_lose"."count" >=
1393 "policy_row"."indirect_majority_non_negative"
1394 ) AS "subquery"
1395 WHERE "id" = "subquery"."initiative_id";
1396 EXIT WHEN NOT FOUND;
1397 END LOOP;
1398 -- set "multistage_majority" for remaining matching initiatives:
1399 UPDATE "initiative" SET "multistage_majority" = TRUE
1400 FROM (
1401 SELECT "losing_initiative"."id" AS "initiative_id"
1402 FROM "initiative" "losing_initiative"
1403 JOIN "initiative" "winning_initiative"
1404 ON "winning_initiative"."issue_id" = "issue_id_p"
1405 AND "winning_initiative"."admitted"
1406 JOIN "battle" "battle_win"
1407 ON "battle_win"."issue_id" = "issue_id_p"
1408 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
1409 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
1410 JOIN "battle" "battle_lose"
1411 ON "battle_lose"."issue_id" = "issue_id_p"
1412 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
1413 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
1414 WHERE "losing_initiative"."issue_id" = "issue_id_p"
1415 AND "losing_initiative"."admitted"
1416 AND "winning_initiative"."schulze_rank" <
1417 "losing_initiative"."schulze_rank"
1418 AND "battle_win"."count" > "battle_lose"."count"
1419 AND (
1420 "battle_win"."count" > "winning_initiative"."positive_votes" OR
1421 "battle_lose"."count" < "losing_initiative"."negative_votes" )
1422 ) AS "subquery"
1423 WHERE "id" = "subquery"."initiative_id";
1424 -- mark eligible initiatives:
1425 UPDATE "initiative" SET "eligible" = TRUE
1426 WHERE "issue_id" = "issue_id_p"
1427 AND "initiative"."direct_majority"
1428 AND "initiative"."indirect_majority"
1429 AND "initiative"."better_than_status_quo"
1430 AND (
1431 "policy_row"."no_multistage_majority" = FALSE OR
1432 "initiative"."multistage_majority" = FALSE )
1433 AND (
1434 "policy_row"."no_reverse_beat_path" = FALSE OR
1435 "initiative"."reverse_beat_path" = FALSE );
1436 -- mark final winner:
1437 UPDATE "initiative" SET "winner" = TRUE
1438 FROM (
1439 SELECT "id" AS "initiative_id"
1440 FROM "initiative"
1441 WHERE "issue_id" = "issue_id_p" AND "eligible"
1442 ORDER BY
1443 "schulze_rank",
1444 "id"
1445 LIMIT 1
1446 ) AS "subquery"
1447 WHERE "id" = "subquery"."initiative_id";
1448 -- write (final) ranks:
1449 "rank_v" := 1;
1450 FOR "initiative_id_v" IN
1451 SELECT "id"
1452 FROM "initiative"
1453 WHERE "issue_id" = "issue_id_p" AND "admitted"
1454 ORDER BY
1455 "winner" DESC,
1456 "eligible" DESC,
1457 "schulze_rank",
1458 "id"
1459 LOOP
1460 UPDATE "initiative" SET "rank" = "rank_v"
1461 WHERE "id" = "initiative_id_v";
1462 "rank_v" := "rank_v" + 1;
1463 END LOOP;
1464 -- set schulze rank of status quo and mark issue as finished:
1465 UPDATE "issue" SET
1466 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
1467 "state" =
1468 CASE WHEN EXISTS (
1469 SELECT NULL FROM "initiative"
1470 WHERE "issue_id" = "issue_id_p" AND "winner"
1471 ) THEN
1472 'finished_with_winner'::"issue_state"
1473 ELSE
1474 'finished_without_winner'::"issue_state"
1475 END,
1476 "closed" = "phase_finished",
1477 "phase_finished" = NULL
1478 WHERE "id" = "issue_id_p";
1479 RETURN;
1480 END;
1481 $$;
1483 DROP FUNCTION "check_issue"("issue"."id"%TYPE);
1485 CREATE TYPE "check_issue_persistence" AS (
1486 "state" "issue_state",
1487 "phase_finished" BOOLEAN,
1488 "issue_revoked" BOOLEAN,
1489 "snapshot_created" BOOLEAN,
1490 "harmonic_weights_set" BOOLEAN,
1491 "closed_voting" BOOLEAN );
1492 COMMENT ON TYPE "check_issue_persistence" IS 'Type of data returned by "check_issue" function, to be passed to subsequent calls of the same function';
1494 CREATE FUNCTION "check_issue"
1495 ( "issue_id_p" "issue"."id"%TYPE,
1496 "persist" "check_issue_persistence" )
1497 RETURNS "check_issue_persistence"
1498 LANGUAGE 'plpgsql' VOLATILE AS $$
1499 DECLARE
1500 "issue_row" "issue"%ROWTYPE;
1501 "policy_row" "policy"%ROWTYPE;
1502 "initiative_row" "initiative"%ROWTYPE;
1503 "state_v" "issue_state";
1504 BEGIN
1505 PERFORM "require_transaction_isolation"();
1506 IF "persist" ISNULL THEN
1507 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
1508 FOR UPDATE;
1509 IF "issue_row"."closed" NOTNULL THEN
1510 RETURN NULL;
1511 END IF;
1512 "persist"."state" := "issue_row"."state";
1513 IF
1514 ( "issue_row"."state" = 'admission' AND now() >=
1515 "issue_row"."created" + "issue_row"."admission_time" ) OR
1516 ( "issue_row"."state" = 'discussion' AND now() >=
1517 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
1518 ( "issue_row"."state" = 'verification' AND now() >=
1519 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
1520 ( "issue_row"."state" = 'voting' AND now() >=
1521 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
1522 THEN
1523 "persist"."phase_finished" := TRUE;
1524 ELSE
1525 "persist"."phase_finished" := FALSE;
1526 END IF;
1527 IF
1528 NOT EXISTS (
1529 -- all initiatives are revoked
1530 SELECT NULL FROM "initiative"
1531 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
1532 ) AND (
1533 -- and issue has not been accepted yet
1534 "persist"."state" = 'admission' OR
1535 -- or verification time has elapsed
1536 ( "persist"."state" = 'verification' AND
1537 "persist"."phase_finished" ) OR
1538 -- or no initiatives have been revoked lately
1539 NOT EXISTS (
1540 SELECT NULL FROM "initiative"
1541 WHERE "issue_id" = "issue_id_p"
1542 AND now() < "revoked" + "issue_row"."verification_time"
1543 )
1544 )
1545 THEN
1546 "persist"."issue_revoked" := TRUE;
1547 ELSE
1548 "persist"."issue_revoked" := FALSE;
1549 END IF;
1550 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
1551 UPDATE "issue" SET "phase_finished" = now()
1552 WHERE "id" = "issue_row"."id";
1553 RETURN "persist";
1554 ELSIF
1555 "persist"."state" IN ('admission', 'discussion', 'verification')
1556 THEN
1557 RETURN "persist";
1558 ELSE
1559 RETURN NULL;
1560 END IF;
1561 END IF;
1562 IF
1563 "persist"."state" IN ('admission', 'discussion', 'verification') AND
1564 coalesce("persist"."snapshot_created", FALSE) = FALSE
1565 THEN
1566 PERFORM "create_snapshot"("issue_id_p");
1567 "persist"."snapshot_created" = TRUE;
1568 IF "persist"."phase_finished" THEN
1569 IF "persist"."state" = 'admission' THEN
1570 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
1571 ELSIF "persist"."state" = 'discussion' THEN
1572 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
1573 ELSIF "persist"."state" = 'verification' THEN
1574 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
1575 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1576 SELECT * INTO "policy_row" FROM "policy"
1577 WHERE "id" = "issue_row"."policy_id";
1578 FOR "initiative_row" IN
1579 SELECT * FROM "initiative"
1580 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
1581 FOR UPDATE
1582 LOOP
1583 IF
1584 "initiative_row"."polling" OR (
1585 "initiative_row"."satisfied_supporter_count" > 0 AND
1586 "initiative_row"."satisfied_supporter_count" *
1587 "policy_row"."initiative_quorum_den" >=
1588 "issue_row"."population" * "policy_row"."initiative_quorum_num"
1589 )
1590 THEN
1591 UPDATE "initiative" SET "admitted" = TRUE
1592 WHERE "id" = "initiative_row"."id";
1593 ELSE
1594 UPDATE "initiative" SET "admitted" = FALSE
1595 WHERE "id" = "initiative_row"."id";
1596 END IF;
1597 END LOOP;
1598 END IF;
1599 END IF;
1600 RETURN "persist";
1601 END IF;
1602 IF
1603 "persist"."state" IN ('admission', 'discussion', 'verification') AND
1604 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
1605 THEN
1606 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
1607 "persist"."harmonic_weights_set" = TRUE;
1608 IF
1609 "persist"."phase_finished" OR
1610 "persist"."issue_revoked" OR
1611 "persist"."state" = 'admission'
1612 THEN
1613 RETURN "persist";
1614 ELSE
1615 RETURN NULL;
1616 END IF;
1617 END IF;
1618 IF "persist"."issue_revoked" THEN
1619 IF "persist"."state" = 'admission' THEN
1620 "state_v" := 'canceled_revoked_before_accepted';
1621 ELSIF "persist"."state" = 'discussion' THEN
1622 "state_v" := 'canceled_after_revocation_during_discussion';
1623 ELSIF "persist"."state" = 'verification' THEN
1624 "state_v" := 'canceled_after_revocation_during_verification';
1625 END IF;
1626 UPDATE "issue" SET
1627 "state" = "state_v",
1628 "closed" = "phase_finished",
1629 "phase_finished" = NULL
1630 WHERE "id" = "issue_id_p";
1631 RETURN NULL;
1632 END IF;
1633 IF "persist"."state" = 'admission' THEN
1634 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
1635 FOR UPDATE;
1636 SELECT * INTO "policy_row"
1637 FROM "policy" WHERE "id" = "issue_row"."policy_id";
1638 IF EXISTS (
1639 SELECT NULL FROM "initiative"
1640 WHERE "issue_id" = "issue_id_p"
1641 AND "supporter_count" > 0
1642 AND "supporter_count" * "policy_row"."issue_quorum_den"
1643 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
1644 ) THEN
1645 UPDATE "issue" SET
1646 "state" = 'discussion',
1647 "accepted" = coalesce("phase_finished", now()),
1648 "phase_finished" = NULL
1649 WHERE "id" = "issue_id_p";
1650 ELSIF "issue_row"."phase_finished" NOTNULL THEN
1651 UPDATE "issue" SET
1652 "state" = 'canceled_issue_not_accepted',
1653 "closed" = "phase_finished",
1654 "phase_finished" = NULL
1655 WHERE "id" = "issue_id_p";
1656 END IF;
1657 RETURN NULL;
1658 END IF;
1659 IF "persist"."phase_finished" THEN
1660 if "persist"."state" = 'discussion' THEN
1661 UPDATE "issue" SET
1662 "state" = 'verification',
1663 "half_frozen" = "phase_finished",
1664 "phase_finished" = NULL
1665 WHERE "id" = "issue_id_p";
1666 RETURN NULL;
1667 END IF;
1668 IF "persist"."state" = 'verification' THEN
1669 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
1670 FOR UPDATE;
1671 SELECT * INTO "policy_row" FROM "policy"
1672 WHERE "id" = "issue_row"."policy_id";
1673 IF EXISTS (
1674 SELECT NULL FROM "initiative"
1675 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
1676 ) THEN
1677 UPDATE "issue" SET
1678 "state" = 'voting',
1679 "fully_frozen" = "phase_finished",
1680 "phase_finished" = NULL
1681 WHERE "id" = "issue_id_p";
1682 ELSE
1683 UPDATE "issue" SET
1684 "state" = 'canceled_no_initiative_admitted',
1685 "fully_frozen" = "phase_finished",
1686 "closed" = "phase_finished",
1687 "phase_finished" = NULL
1688 WHERE "id" = "issue_id_p";
1689 -- NOTE: The following DELETE statements have effect only when
1690 -- issue state has been manipulated
1691 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
1692 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
1693 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
1694 END IF;
1695 RETURN NULL;
1696 END IF;
1697 IF "persist"."state" = 'voting' THEN
1698 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
1699 PERFORM "close_voting"("issue_id_p");
1700 "persist"."closed_voting" = TRUE;
1701 RETURN "persist";
1702 END IF;
1703 PERFORM "calculate_ranks"("issue_id_p");
1704 RETURN NULL;
1705 END IF;
1706 END IF;
1707 RAISE WARNING 'should not happen';
1708 RETURN NULL;
1709 END;
1710 $$;
1711 COMMENT ON FUNCTION "check_issue"
1712 ( "issue"."id"%TYPE,
1713 "check_issue_persistence" )
1714 IS 'Precalculate supporter counts etc. for a given issue, and check, if status change is required, and perform the status change when necessary; Function must be called multiple times with the previous result as second parameter, until the result is NULL (see source code of function "check_everything")';
1716 CREATE OR REPLACE FUNCTION "check_everything"()
1717 RETURNS VOID
1718 LANGUAGE 'plpgsql' VOLATILE AS $$
1719 DECLARE
1720 "issue_id_v" "issue"."id"%TYPE;
1721 "persist_v" "check_issue_persistence";
1722 BEGIN
1723 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
1724 DELETE FROM "expired_session";
1725 PERFORM "check_activity"();
1726 PERFORM "calculate_member_counts"();
1727 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
1728 "persist_v" := NULL;
1729 LOOP
1730 "persist_v" := "check_issue"("issue_id_v", "persist_v");
1731 EXIT WHEN "persist_v" ISNULL;
1732 END LOOP;
1733 END LOOP;
1734 RETURN;
1735 END;
1736 $$;
1737 COMMENT ON FUNCTION "check_everything"() IS 'Amongst other regular tasks this function performs "check_issue" for every open issue. Use this function only for development and debugging purposes, as you may run into locking and/or serialization problems in productive environments.';
1739 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
1740 RETURNS VOID
1741 LANGUAGE 'plpgsql' VOLATILE AS $$
1742 DECLARE
1743 "issue_row" "issue"%ROWTYPE;
1744 BEGIN
1745 SELECT * INTO "issue_row"
1746 FROM "issue" WHERE "id" = "issue_id_p"
1747 FOR UPDATE;
1748 IF "issue_row"."cleaned" ISNULL THEN
1749 UPDATE "issue" SET
1750 "state" = 'voting',
1751 "closed" = NULL
1752 WHERE "id" = "issue_id_p";
1753 DELETE FROM "delegating_voter"
1754 WHERE "issue_id" = "issue_id_p";
1755 DELETE FROM "direct_voter"
1756 WHERE "issue_id" = "issue_id_p";
1757 DELETE FROM "delegating_interest_snapshot"
1758 WHERE "issue_id" = "issue_id_p";
1759 DELETE FROM "direct_interest_snapshot"
1760 WHERE "issue_id" = "issue_id_p";
1761 DELETE FROM "delegating_population_snapshot"
1762 WHERE "issue_id" = "issue_id_p";
1763 DELETE FROM "direct_population_snapshot"
1764 WHERE "issue_id" = "issue_id_p";
1765 DELETE FROM "non_voter"
1766 WHERE "issue_id" = "issue_id_p";
1767 DELETE FROM "delegation"
1768 WHERE "issue_id" = "issue_id_p";
1769 DELETE FROM "supporter"
1770 USING "initiative" -- NOTE: due to missing index on issue_id
1771 WHERE "initiative"."issue_id" = "issue_id_p"
1772 AND "supporter"."initiative_id" = "initiative_id";
1773 UPDATE "issue" SET
1774 "state" = "issue_row"."state",
1775 "closed" = "issue_row"."closed",
1776 "cleaned" = now()
1777 WHERE "id" = "issue_id_p";
1778 END IF;
1779 RETURN;
1780 END;
1781 $$;
1783 SELECT "set_harmonic_initiative_weights"("id") FROM "issue";
1785 COMMIT;