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