liquid_feedback_core
view update/core-update.v2.1.0-v2.2.0.sql @ 347:77d9eccc167c
Execute update script from v2.1.0 to v2.2.0 in isolation level REPEATABLE READ
as needed by function "set_harmonic_initiative_weights"
as needed by function "set_harmonic_initiative_weights"
| author | jbe |
|---|---|
| date | Thu Feb 21 20:08:04 2013 +0100 (2013-02-21) |
| parents | 6c8209b711af |
| children | a5d4df7f4e22 |
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 FUNCTION "set_harmonic_initiative_weights"
451 ( "issue_id_p" "issue"."id"%TYPE )
452 RETURNS VOID
453 LANGUAGE 'plpgsql' VOLATILE AS $$
454 DECLARE
455 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
456 "i" INT4;
457 "count_v" INT4;
458 "summand_v" FLOAT;
459 "id_ary" INT4[];
460 "weight_ary" FLOAT[];
461 "min_weight_v" FLOAT;
462 BEGIN
463 PERFORM "require_transaction_isolation"();
464 UPDATE "initiative" SET "harmonic_weight" = NULL
465 WHERE "issue_id" = "issue_id_p";
466 LOOP
467 "min_weight_v" := NULL;
468 "i" := 0;
469 "count_v" := 0;
470 FOR "weight_row" IN
471 SELECT * FROM "remaining_harmonic_initiative_weight_summands"
472 WHERE "issue_id" = "issue_id_p"
473 AND (
474 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
475 SELECT NULL FROM "initiative"
476 WHERE "issue_id" = "issue_id_p"
477 AND "harmonic_weight" ISNULL
478 AND coalesce("admitted", FALSE) = FALSE
479 )
480 )
481 ORDER BY "initiative_id" DESC, "weight_den" DESC
482 -- NOTE: non-admitted initiatives placed first (at last positions),
483 -- latest initiatives treated worse in case of tie
484 LOOP
485 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
486 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
487 "i" := "i" + 1;
488 "count_v" := "i";
489 "id_ary"["i"] := "weight_row"."initiative_id";
490 "weight_ary"["i"] := "summand_v";
491 ELSE
492 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
493 END IF;
494 END LOOP;
495 EXIT WHEN "count_v" = 0;
496 "i" := 1;
497 LOOP
498 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
499 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
500 "min_weight_v" := "weight_ary"["i"];
501 END IF;
502 "i" := "i" + 1;
503 EXIT WHEN "i" > "count_v";
504 END LOOP;
505 "i" := 1;
506 LOOP
507 IF "weight_ary"["i"] = "min_weight_v" THEN
508 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
509 WHERE "id" = "id_ary"["i"];
510 EXIT;
511 END IF;
512 "i" := "i" + 1;
513 END LOOP;
514 END LOOP;
515 UPDATE "initiative" SET "harmonic_weight" = 0
516 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
517 END;
518 $$;
519 COMMENT ON FUNCTION "set_harmonic_initiative_weights"
520 ( "issue"."id"%TYPE )
521 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
523 CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_population_snapshot"
524 ( "issue_id_p" "issue"."id"%TYPE,
525 "member_id_p" "member"."id"%TYPE,
526 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
527 RETURNS "direct_population_snapshot"."weight"%TYPE
528 LANGUAGE 'plpgsql' VOLATILE AS $$
529 DECLARE
530 "issue_delegation_row" "issue_delegation"%ROWTYPE;
531 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
532 "weight_v" INT4;
533 "sub_weight_v" INT4;
534 BEGIN
535 PERFORM "require_transaction_isolation"();
536 "weight_v" := 0;
537 FOR "issue_delegation_row" IN
538 SELECT * FROM "issue_delegation"
539 WHERE "trustee_id" = "member_id_p"
540 AND "issue_id" = "issue_id_p"
541 LOOP
542 IF NOT EXISTS (
543 SELECT NULL FROM "direct_population_snapshot"
544 WHERE "issue_id" = "issue_id_p"
545 AND "event" = 'periodic'
546 AND "member_id" = "issue_delegation_row"."truster_id"
547 ) AND NOT EXISTS (
548 SELECT NULL FROM "delegating_population_snapshot"
549 WHERE "issue_id" = "issue_id_p"
550 AND "event" = 'periodic'
551 AND "member_id" = "issue_delegation_row"."truster_id"
552 ) THEN
553 "delegate_member_ids_v" :=
554 "member_id_p" || "delegate_member_ids_p";
555 INSERT INTO "delegating_population_snapshot" (
556 "issue_id",
557 "event",
558 "member_id",
559 "scope",
560 "delegate_member_ids"
561 ) VALUES (
562 "issue_id_p",
563 'periodic',
564 "issue_delegation_row"."truster_id",
565 "issue_delegation_row"."scope",
566 "delegate_member_ids_v"
567 );
568 "sub_weight_v" := 1 +
569 "weight_of_added_delegations_for_population_snapshot"(
570 "issue_id_p",
571 "issue_delegation_row"."truster_id",
572 "delegate_member_ids_v"
573 );
574 UPDATE "delegating_population_snapshot"
575 SET "weight" = "sub_weight_v"
576 WHERE "issue_id" = "issue_id_p"
577 AND "event" = 'periodic'
578 AND "member_id" = "issue_delegation_row"."truster_id";
579 "weight_v" := "weight_v" + "sub_weight_v";
580 END IF;
581 END LOOP;
582 RETURN "weight_v";
583 END;
584 $$;
586 CREATE OR REPLACE FUNCTION "create_population_snapshot"
587 ( "issue_id_p" "issue"."id"%TYPE )
588 RETURNS VOID
589 LANGUAGE 'plpgsql' VOLATILE AS $$
590 DECLARE
591 "member_id_v" "member"."id"%TYPE;
592 BEGIN
593 PERFORM "require_transaction_isolation"();
594 DELETE FROM "direct_population_snapshot"
595 WHERE "issue_id" = "issue_id_p"
596 AND "event" = 'periodic';
597 DELETE FROM "delegating_population_snapshot"
598 WHERE "issue_id" = "issue_id_p"
599 AND "event" = 'periodic';
600 INSERT INTO "direct_population_snapshot"
601 ("issue_id", "event", "member_id")
602 SELECT
603 "issue_id_p" AS "issue_id",
604 'periodic'::"snapshot_event" AS "event",
605 "member"."id" AS "member_id"
606 FROM "issue"
607 JOIN "area" ON "issue"."area_id" = "area"."id"
608 JOIN "membership" ON "area"."id" = "membership"."area_id"
609 JOIN "member" ON "membership"."member_id" = "member"."id"
610 JOIN "privilege"
611 ON "privilege"."unit_id" = "area"."unit_id"
612 AND "privilege"."member_id" = "member"."id"
613 WHERE "issue"."id" = "issue_id_p"
614 AND "member"."active" AND "privilege"."voting_right"
615 UNION
616 SELECT
617 "issue_id_p" AS "issue_id",
618 'periodic'::"snapshot_event" AS "event",
619 "member"."id" AS "member_id"
620 FROM "issue"
621 JOIN "area" ON "issue"."area_id" = "area"."id"
622 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
623 JOIN "member" ON "interest"."member_id" = "member"."id"
624 JOIN "privilege"
625 ON "privilege"."unit_id" = "area"."unit_id"
626 AND "privilege"."member_id" = "member"."id"
627 WHERE "issue"."id" = "issue_id_p"
628 AND "member"."active" AND "privilege"."voting_right";
629 FOR "member_id_v" IN
630 SELECT "member_id" FROM "direct_population_snapshot"
631 WHERE "issue_id" = "issue_id_p"
632 AND "event" = 'periodic'
633 LOOP
634 UPDATE "direct_population_snapshot" SET
635 "weight" = 1 +
636 "weight_of_added_delegations_for_population_snapshot"(
637 "issue_id_p",
638 "member_id_v",
639 '{}'
640 )
641 WHERE "issue_id" = "issue_id_p"
642 AND "event" = 'periodic'
643 AND "member_id" = "member_id_v";
644 END LOOP;
645 RETURN;
646 END;
647 $$;
649 CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
650 ( "issue_id_p" "issue"."id"%TYPE,
651 "member_id_p" "member"."id"%TYPE,
652 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
653 RETURNS "direct_interest_snapshot"."weight"%TYPE
654 LANGUAGE 'plpgsql' VOLATILE AS $$
655 DECLARE
656 "issue_delegation_row" "issue_delegation"%ROWTYPE;
657 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
658 "weight_v" INT4;
659 "sub_weight_v" INT4;
660 BEGIN
661 PERFORM "require_transaction_isolation"();
662 "weight_v" := 0;
663 FOR "issue_delegation_row" IN
664 SELECT * FROM "issue_delegation"
665 WHERE "trustee_id" = "member_id_p"
666 AND "issue_id" = "issue_id_p"
667 LOOP
668 IF NOT EXISTS (
669 SELECT NULL FROM "direct_interest_snapshot"
670 WHERE "issue_id" = "issue_id_p"
671 AND "event" = 'periodic'
672 AND "member_id" = "issue_delegation_row"."truster_id"
673 ) AND NOT EXISTS (
674 SELECT NULL FROM "delegating_interest_snapshot"
675 WHERE "issue_id" = "issue_id_p"
676 AND "event" = 'periodic'
677 AND "member_id" = "issue_delegation_row"."truster_id"
678 ) THEN
679 "delegate_member_ids_v" :=
680 "member_id_p" || "delegate_member_ids_p";
681 INSERT INTO "delegating_interest_snapshot" (
682 "issue_id",
683 "event",
684 "member_id",
685 "scope",
686 "delegate_member_ids"
687 ) VALUES (
688 "issue_id_p",
689 'periodic',
690 "issue_delegation_row"."truster_id",
691 "issue_delegation_row"."scope",
692 "delegate_member_ids_v"
693 );
694 "sub_weight_v" := 1 +
695 "weight_of_added_delegations_for_interest_snapshot"(
696 "issue_id_p",
697 "issue_delegation_row"."truster_id",
698 "delegate_member_ids_v"
699 );
700 UPDATE "delegating_interest_snapshot"
701 SET "weight" = "sub_weight_v"
702 WHERE "issue_id" = "issue_id_p"
703 AND "event" = 'periodic'
704 AND "member_id" = "issue_delegation_row"."truster_id";
705 "weight_v" := "weight_v" + "sub_weight_v";
706 END IF;
707 END LOOP;
708 RETURN "weight_v";
709 END;
710 $$;
712 CREATE OR REPLACE FUNCTION "create_interest_snapshot"
713 ( "issue_id_p" "issue"."id"%TYPE )
714 RETURNS VOID
715 LANGUAGE 'plpgsql' VOLATILE AS $$
716 DECLARE
717 "member_id_v" "member"."id"%TYPE;
718 BEGIN
719 PERFORM "require_transaction_isolation"();
720 DELETE FROM "direct_interest_snapshot"
721 WHERE "issue_id" = "issue_id_p"
722 AND "event" = 'periodic';
723 DELETE FROM "delegating_interest_snapshot"
724 WHERE "issue_id" = "issue_id_p"
725 AND "event" = 'periodic';
726 DELETE FROM "direct_supporter_snapshot"
727 USING "initiative" -- NOTE: due to missing index on issue_id
728 WHERE "initiative"."issue_id" = "issue_id_p"
729 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
730 AND "direct_supporter_snapshot"."event" = 'periodic';
731 INSERT INTO "direct_interest_snapshot"
732 ("issue_id", "event", "member_id")
733 SELECT
734 "issue_id_p" AS "issue_id",
735 'periodic' AS "event",
736 "member"."id" AS "member_id"
737 FROM "issue"
738 JOIN "area" ON "issue"."area_id" = "area"."id"
739 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
740 JOIN "member" ON "interest"."member_id" = "member"."id"
741 JOIN "privilege"
742 ON "privilege"."unit_id" = "area"."unit_id"
743 AND "privilege"."member_id" = "member"."id"
744 WHERE "issue"."id" = "issue_id_p"
745 AND "member"."active" AND "privilege"."voting_right";
746 FOR "member_id_v" IN
747 SELECT "member_id" FROM "direct_interest_snapshot"
748 WHERE "issue_id" = "issue_id_p"
749 AND "event" = 'periodic'
750 LOOP
751 UPDATE "direct_interest_snapshot" SET
752 "weight" = 1 +
753 "weight_of_added_delegations_for_interest_snapshot"(
754 "issue_id_p",
755 "member_id_v",
756 '{}'
757 )
758 WHERE "issue_id" = "issue_id_p"
759 AND "event" = 'periodic'
760 AND "member_id" = "member_id_v";
761 END LOOP;
762 INSERT INTO "direct_supporter_snapshot"
763 ( "issue_id", "initiative_id", "event", "member_id",
764 "draft_id", "informed", "satisfied" )
765 SELECT
766 "issue_id_p" AS "issue_id",
767 "initiative"."id" AS "initiative_id",
768 'periodic' AS "event",
769 "supporter"."member_id" AS "member_id",
770 "supporter"."draft_id" AS "draft_id",
771 "supporter"."draft_id" = "current_draft"."id" AS "informed",
772 NOT EXISTS (
773 SELECT NULL FROM "critical_opinion"
774 WHERE "initiative_id" = "initiative"."id"
775 AND "member_id" = "supporter"."member_id"
776 ) AS "satisfied"
777 FROM "initiative"
778 JOIN "supporter"
779 ON "supporter"."initiative_id" = "initiative"."id"
780 JOIN "current_draft"
781 ON "initiative"."id" = "current_draft"."initiative_id"
782 JOIN "direct_interest_snapshot"
783 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
784 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
785 AND "event" = 'periodic'
786 WHERE "initiative"."issue_id" = "issue_id_p";
787 RETURN;
788 END;
789 $$;
791 CREATE OR REPLACE FUNCTION "create_snapshot"
792 ( "issue_id_p" "issue"."id"%TYPE )
793 RETURNS VOID
794 LANGUAGE 'plpgsql' VOLATILE AS $$
795 DECLARE
796 "initiative_id_v" "initiative"."id"%TYPE;
797 "suggestion_id_v" "suggestion"."id"%TYPE;
798 BEGIN
799 PERFORM "require_transaction_isolation"();
800 PERFORM "create_population_snapshot"("issue_id_p");
801 PERFORM "create_interest_snapshot"("issue_id_p");
802 UPDATE "issue" SET
803 "snapshot" = coalesce("phase_finished", now()),
804 "latest_snapshot_event" = 'periodic',
805 "population" = (
806 SELECT coalesce(sum("weight"), 0)
807 FROM "direct_population_snapshot"
808 WHERE "issue_id" = "issue_id_p"
809 AND "event" = 'periodic'
810 )
811 WHERE "id" = "issue_id_p";
812 FOR "initiative_id_v" IN
813 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
814 LOOP
815 UPDATE "initiative" SET
816 "supporter_count" = (
817 SELECT coalesce(sum("di"."weight"), 0)
818 FROM "direct_interest_snapshot" AS "di"
819 JOIN "direct_supporter_snapshot" AS "ds"
820 ON "di"."member_id" = "ds"."member_id"
821 WHERE "di"."issue_id" = "issue_id_p"
822 AND "di"."event" = 'periodic'
823 AND "ds"."initiative_id" = "initiative_id_v"
824 AND "ds"."event" = 'periodic'
825 ),
826 "informed_supporter_count" = (
827 SELECT coalesce(sum("di"."weight"), 0)
828 FROM "direct_interest_snapshot" AS "di"
829 JOIN "direct_supporter_snapshot" AS "ds"
830 ON "di"."member_id" = "ds"."member_id"
831 WHERE "di"."issue_id" = "issue_id_p"
832 AND "di"."event" = 'periodic'
833 AND "ds"."initiative_id" = "initiative_id_v"
834 AND "ds"."event" = 'periodic'
835 AND "ds"."informed"
836 ),
837 "satisfied_supporter_count" = (
838 SELECT coalesce(sum("di"."weight"), 0)
839 FROM "direct_interest_snapshot" AS "di"
840 JOIN "direct_supporter_snapshot" AS "ds"
841 ON "di"."member_id" = "ds"."member_id"
842 WHERE "di"."issue_id" = "issue_id_p"
843 AND "di"."event" = 'periodic'
844 AND "ds"."initiative_id" = "initiative_id_v"
845 AND "ds"."event" = 'periodic'
846 AND "ds"."satisfied"
847 ),
848 "satisfied_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 AND "ds"."satisfied"
859 )
860 WHERE "id" = "initiative_id_v";
861 FOR "suggestion_id_v" IN
862 SELECT "id" FROM "suggestion"
863 WHERE "initiative_id" = "initiative_id_v"
864 LOOP
865 UPDATE "suggestion" SET
866 "minus2_unfulfilled_count" = (
867 SELECT coalesce(sum("snapshot"."weight"), 0)
868 FROM "issue" CROSS JOIN "opinion"
869 JOIN "direct_interest_snapshot" AS "snapshot"
870 ON "snapshot"."issue_id" = "issue"."id"
871 AND "snapshot"."event" = "issue"."latest_snapshot_event"
872 AND "snapshot"."member_id" = "opinion"."member_id"
873 WHERE "issue"."id" = "issue_id_p"
874 AND "opinion"."suggestion_id" = "suggestion_id_v"
875 AND "opinion"."degree" = -2
876 AND "opinion"."fulfilled" = FALSE
877 ),
878 "minus2_fulfilled_count" = (
879 SELECT coalesce(sum("snapshot"."weight"), 0)
880 FROM "issue" CROSS JOIN "opinion"
881 JOIN "direct_interest_snapshot" AS "snapshot"
882 ON "snapshot"."issue_id" = "issue"."id"
883 AND "snapshot"."event" = "issue"."latest_snapshot_event"
884 AND "snapshot"."member_id" = "opinion"."member_id"
885 WHERE "issue"."id" = "issue_id_p"
886 AND "opinion"."suggestion_id" = "suggestion_id_v"
887 AND "opinion"."degree" = -2
888 AND "opinion"."fulfilled" = TRUE
889 ),
890 "minus1_unfulfilled_count" = (
891 SELECT coalesce(sum("snapshot"."weight"), 0)
892 FROM "issue" CROSS JOIN "opinion"
893 JOIN "direct_interest_snapshot" AS "snapshot"
894 ON "snapshot"."issue_id" = "issue"."id"
895 AND "snapshot"."event" = "issue"."latest_snapshot_event"
896 AND "snapshot"."member_id" = "opinion"."member_id"
897 WHERE "issue"."id" = "issue_id_p"
898 AND "opinion"."suggestion_id" = "suggestion_id_v"
899 AND "opinion"."degree" = -1
900 AND "opinion"."fulfilled" = FALSE
901 ),
902 "minus1_fulfilled_count" = (
903 SELECT coalesce(sum("snapshot"."weight"), 0)
904 FROM "issue" CROSS JOIN "opinion"
905 JOIN "direct_interest_snapshot" AS "snapshot"
906 ON "snapshot"."issue_id" = "issue"."id"
907 AND "snapshot"."event" = "issue"."latest_snapshot_event"
908 AND "snapshot"."member_id" = "opinion"."member_id"
909 WHERE "issue"."id" = "issue_id_p"
910 AND "opinion"."suggestion_id" = "suggestion_id_v"
911 AND "opinion"."degree" = -1
912 AND "opinion"."fulfilled" = TRUE
913 ),
914 "plus1_unfulfilled_count" = (
915 SELECT coalesce(sum("snapshot"."weight"), 0)
916 FROM "issue" CROSS JOIN "opinion"
917 JOIN "direct_interest_snapshot" AS "snapshot"
918 ON "snapshot"."issue_id" = "issue"."id"
919 AND "snapshot"."event" = "issue"."latest_snapshot_event"
920 AND "snapshot"."member_id" = "opinion"."member_id"
921 WHERE "issue"."id" = "issue_id_p"
922 AND "opinion"."suggestion_id" = "suggestion_id_v"
923 AND "opinion"."degree" = 1
924 AND "opinion"."fulfilled" = FALSE
925 ),
926 "plus1_fulfilled_count" = (
927 SELECT coalesce(sum("snapshot"."weight"), 0)
928 FROM "issue" CROSS JOIN "opinion"
929 JOIN "direct_interest_snapshot" AS "snapshot"
930 ON "snapshot"."issue_id" = "issue"."id"
931 AND "snapshot"."event" = "issue"."latest_snapshot_event"
932 AND "snapshot"."member_id" = "opinion"."member_id"
933 WHERE "issue"."id" = "issue_id_p"
934 AND "opinion"."suggestion_id" = "suggestion_id_v"
935 AND "opinion"."degree" = 1
936 AND "opinion"."fulfilled" = TRUE
937 ),
938 "plus2_unfulfilled_count" = (
939 SELECT coalesce(sum("snapshot"."weight"), 0)
940 FROM "issue" CROSS JOIN "opinion"
941 JOIN "direct_interest_snapshot" AS "snapshot"
942 ON "snapshot"."issue_id" = "issue"."id"
943 AND "snapshot"."event" = "issue"."latest_snapshot_event"
944 AND "snapshot"."member_id" = "opinion"."member_id"
945 WHERE "issue"."id" = "issue_id_p"
946 AND "opinion"."suggestion_id" = "suggestion_id_v"
947 AND "opinion"."degree" = 2
948 AND "opinion"."fulfilled" = FALSE
949 ),
950 "plus2_fulfilled_count" = (
951 SELECT coalesce(sum("snapshot"."weight"), 0)
952 FROM "issue" CROSS JOIN "opinion"
953 JOIN "direct_interest_snapshot" AS "snapshot"
954 ON "snapshot"."issue_id" = "issue"."id"
955 AND "snapshot"."event" = "issue"."latest_snapshot_event"
956 AND "snapshot"."member_id" = "opinion"."member_id"
957 WHERE "issue"."id" = "issue_id_p"
958 AND "opinion"."suggestion_id" = "suggestion_id_v"
959 AND "opinion"."degree" = 2
960 AND "opinion"."fulfilled" = TRUE
961 )
962 WHERE "suggestion"."id" = "suggestion_id_v";
963 END LOOP;
964 END LOOP;
965 RETURN;
966 END;
967 $$;
969 CREATE OR REPLACE FUNCTION "set_snapshot_event"
970 ( "issue_id_p" "issue"."id"%TYPE,
971 "event_p" "snapshot_event" )
972 RETURNS VOID
973 LANGUAGE 'plpgsql' VOLATILE AS $$
974 DECLARE
975 "event_v" "issue"."latest_snapshot_event"%TYPE;
976 BEGIN
977 PERFORM "require_transaction_isolation"();
978 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
979 WHERE "id" = "issue_id_p" FOR UPDATE;
980 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
981 WHERE "id" = "issue_id_p";
982 UPDATE "direct_population_snapshot" SET "event" = "event_p"
983 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
984 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
985 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
986 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
987 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
988 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
989 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
990 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
991 FROM "initiative" -- NOTE: due to missing index on issue_id
992 WHERE "initiative"."issue_id" = "issue_id_p"
993 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
994 AND "direct_supporter_snapshot"."event" = "event_v";
995 RETURN;
996 END;
997 $$;
999 DROP FUNCTION "freeze_after_snapshot"("issue"."id"%TYPE);
1000 DROP FUNCTION "manual_freeze"("issue"."id"%TYPE);
1002 CREATE OR REPLACE FUNCTION "weight_of_added_vote_delegations"
1003 ( "issue_id_p" "issue"."id"%TYPE,
1004 "member_id_p" "member"."id"%TYPE,
1005 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
1006 RETURNS "direct_voter"."weight"%TYPE
1007 LANGUAGE 'plpgsql' VOLATILE AS $$
1008 DECLARE
1009 "issue_delegation_row" "issue_delegation"%ROWTYPE;
1010 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
1011 "weight_v" INT4;
1012 "sub_weight_v" INT4;
1013 BEGIN
1014 PERFORM "require_transaction_isolation"();
1015 "weight_v" := 0;
1016 FOR "issue_delegation_row" IN
1017 SELECT * FROM "issue_delegation"
1018 WHERE "trustee_id" = "member_id_p"
1019 AND "issue_id" = "issue_id_p"
1020 LOOP
1021 IF NOT EXISTS (
1022 SELECT NULL FROM "direct_voter"
1023 WHERE "member_id" = "issue_delegation_row"."truster_id"
1024 AND "issue_id" = "issue_id_p"
1025 ) AND NOT EXISTS (
1026 SELECT NULL FROM "delegating_voter"
1027 WHERE "member_id" = "issue_delegation_row"."truster_id"
1028 AND "issue_id" = "issue_id_p"
1029 ) THEN
1030 "delegate_member_ids_v" :=
1031 "member_id_p" || "delegate_member_ids_p";
1032 INSERT INTO "delegating_voter" (
1033 "issue_id",
1034 "member_id",
1035 "scope",
1036 "delegate_member_ids"
1037 ) VALUES (
1038 "issue_id_p",
1039 "issue_delegation_row"."truster_id",
1040 "issue_delegation_row"."scope",
1041 "delegate_member_ids_v"
1042 );
1043 "sub_weight_v" := 1 +
1044 "weight_of_added_vote_delegations"(
1045 "issue_id_p",
1046 "issue_delegation_row"."truster_id",
1047 "delegate_member_ids_v"
1048 );
1049 UPDATE "delegating_voter"
1050 SET "weight" = "sub_weight_v"
1051 WHERE "issue_id" = "issue_id_p"
1052 AND "member_id" = "issue_delegation_row"."truster_id";
1053 "weight_v" := "weight_v" + "sub_weight_v";
1054 END IF;
1055 END LOOP;
1056 RETURN "weight_v";
1057 END;
1058 $$;
1060 CREATE OR REPLACE FUNCTION "add_vote_delegations"
1061 ( "issue_id_p" "issue"."id"%TYPE )
1062 RETURNS VOID
1063 LANGUAGE 'plpgsql' VOLATILE AS $$
1064 DECLARE
1065 "member_id_v" "member"."id"%TYPE;
1066 BEGIN
1067 PERFORM "require_transaction_isolation"();
1068 FOR "member_id_v" IN
1069 SELECT "member_id" FROM "direct_voter"
1070 WHERE "issue_id" = "issue_id_p"
1071 LOOP
1072 UPDATE "direct_voter" SET
1073 "weight" = "weight" + "weight_of_added_vote_delegations"(
1074 "issue_id_p",
1075 "member_id_v",
1076 '{}'
1077 )
1078 WHERE "member_id" = "member_id_v"
1079 AND "issue_id" = "issue_id_p";
1080 END LOOP;
1081 RETURN;
1082 END;
1083 $$;
1085 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
1086 RETURNS VOID
1087 LANGUAGE 'plpgsql' VOLATILE AS $$
1088 DECLARE
1089 "area_id_v" "area"."id"%TYPE;
1090 "unit_id_v" "unit"."id"%TYPE;
1091 "member_id_v" "member"."id"%TYPE;
1092 BEGIN
1093 PERFORM "require_transaction_isolation"();
1094 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
1095 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
1096 -- delete timestamp of voting comment:
1097 UPDATE "direct_voter" SET "comment_changed" = NULL
1098 WHERE "issue_id" = "issue_id_p";
1099 -- delete delegating votes (in cases of manual reset of issue state):
1100 DELETE FROM "delegating_voter"
1101 WHERE "issue_id" = "issue_id_p";
1102 -- delete votes from non-privileged voters:
1103 DELETE FROM "direct_voter"
1104 USING (
1105 SELECT
1106 "direct_voter"."member_id"
1107 FROM "direct_voter"
1108 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
1109 LEFT JOIN "privilege"
1110 ON "privilege"."unit_id" = "unit_id_v"
1111 AND "privilege"."member_id" = "direct_voter"."member_id"
1112 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
1113 "member"."active" = FALSE OR
1114 "privilege"."voting_right" ISNULL OR
1115 "privilege"."voting_right" = FALSE
1116 )
1117 ) AS "subquery"
1118 WHERE "direct_voter"."issue_id" = "issue_id_p"
1119 AND "direct_voter"."member_id" = "subquery"."member_id";
1120 -- consider delegations:
1121 UPDATE "direct_voter" SET "weight" = 1
1122 WHERE "issue_id" = "issue_id_p";
1123 PERFORM "add_vote_delegations"("issue_id_p");
1124 -- materialize battle_view:
1125 -- NOTE: "closed" column of issue must be set at this point
1126 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
1127 INSERT INTO "battle" (
1128 "issue_id",
1129 "winning_initiative_id", "losing_initiative_id",
1130 "count"
1131 ) SELECT
1132 "issue_id",
1133 "winning_initiative_id", "losing_initiative_id",
1134 "count"
1135 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
1136 -- set voter count:
1137 UPDATE "issue" SET
1138 "voter_count" = (
1139 SELECT coalesce(sum("weight"), 0)
1140 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
1141 )
1142 WHERE "id" = "issue_id_p";
1143 -- copy "positive_votes" and "negative_votes" from "battle" table:
1144 UPDATE "initiative" SET
1145 "positive_votes" = "battle_win"."count",
1146 "negative_votes" = "battle_lose"."count"
1147 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
1148 WHERE
1149 "battle_win"."issue_id" = "issue_id_p" AND
1150 "battle_win"."winning_initiative_id" = "initiative"."id" AND
1151 "battle_win"."losing_initiative_id" ISNULL AND
1152 "battle_lose"."issue_id" = "issue_id_p" AND
1153 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
1154 "battle_lose"."winning_initiative_id" ISNULL;
1155 END;
1156 $$;
1158 CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
1159 RETURNS VOID
1160 LANGUAGE 'plpgsql' VOLATILE AS $$
1161 DECLARE
1162 "issue_row" "issue"%ROWTYPE;
1163 "policy_row" "policy"%ROWTYPE;
1164 "dimension_v" INTEGER;
1165 "vote_matrix" INT4[][]; -- absolute votes
1166 "matrix" INT8[][]; -- defeat strength / best paths
1167 "i" INTEGER;
1168 "j" INTEGER;
1169 "k" INTEGER;
1170 "battle_row" "battle"%ROWTYPE;
1171 "rank_ary" INT4[];
1172 "rank_v" INT4;
1173 "done_v" INTEGER;
1174 "winners_ary" INTEGER[];
1175 "initiative_id_v" "initiative"."id"%TYPE;
1176 BEGIN
1177 PERFORM "require_transaction_isolation"();
1178 SELECT * INTO "issue_row"
1179 FROM "issue" WHERE "id" = "issue_id_p";
1180 SELECT * INTO "policy_row"
1181 FROM "policy" WHERE "id" = "issue_row"."policy_id";
1182 SELECT count(1) INTO "dimension_v"
1183 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
1184 -- Create "vote_matrix" with absolute number of votes in pairwise
1185 -- comparison:
1186 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
1187 "i" := 1;
1188 "j" := 2;
1189 FOR "battle_row" IN
1190 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
1191 ORDER BY
1192 "winning_initiative_id" NULLS LAST,
1193 "losing_initiative_id" NULLS LAST
1194 LOOP
1195 "vote_matrix"["i"]["j"] := "battle_row"."count";
1196 IF "j" = "dimension_v" THEN
1197 "i" := "i" + 1;
1198 "j" := 1;
1199 ELSE
1200 "j" := "j" + 1;
1201 IF "j" = "i" THEN
1202 "j" := "j" + 1;
1203 END IF;
1204 END IF;
1205 END LOOP;
1206 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
1207 RAISE EXCEPTION 'Wrong battle count (should not happen)';
1208 END IF;
1209 -- Store defeat strengths in "matrix" using "defeat_strength"
1210 -- function:
1211 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
1212 "i" := 1;
1213 LOOP
1214 "j" := 1;
1215 LOOP
1216 IF "i" != "j" THEN
1217 "matrix"["i"]["j"] := "defeat_strength"(
1218 "vote_matrix"["i"]["j"],
1219 "vote_matrix"["j"]["i"]
1220 );
1221 END IF;
1222 EXIT WHEN "j" = "dimension_v";
1223 "j" := "j" + 1;
1224 END LOOP;
1225 EXIT WHEN "i" = "dimension_v";
1226 "i" := "i" + 1;
1227 END LOOP;
1228 -- Find best paths:
1229 "i" := 1;
1230 LOOP
1231 "j" := 1;
1232 LOOP
1233 IF "i" != "j" THEN
1234 "k" := 1;
1235 LOOP
1236 IF "i" != "k" AND "j" != "k" THEN
1237 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
1238 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
1239 "matrix"["j"]["k"] := "matrix"["j"]["i"];
1240 END IF;
1241 ELSE
1242 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
1243 "matrix"["j"]["k"] := "matrix"["i"]["k"];
1244 END IF;
1245 END IF;
1246 END IF;
1247 EXIT WHEN "k" = "dimension_v";
1248 "k" := "k" + 1;
1249 END LOOP;
1250 END IF;
1251 EXIT WHEN "j" = "dimension_v";
1252 "j" := "j" + 1;
1253 END LOOP;
1254 EXIT WHEN "i" = "dimension_v";
1255 "i" := "i" + 1;
1256 END LOOP;
1257 -- Determine order of winners:
1258 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
1259 "rank_v" := 1;
1260 "done_v" := 0;
1261 LOOP
1262 "winners_ary" := '{}';
1263 "i" := 1;
1264 LOOP
1265 IF "rank_ary"["i"] ISNULL THEN
1266 "j" := 1;
1267 LOOP
1268 IF
1269 "i" != "j" AND
1270 "rank_ary"["j"] ISNULL AND
1271 "matrix"["j"]["i"] > "matrix"["i"]["j"]
1272 THEN
1273 -- someone else is better
1274 EXIT;
1275 END IF;
1276 IF "j" = "dimension_v" THEN
1277 -- noone is better
1278 "winners_ary" := "winners_ary" || "i";
1279 EXIT;
1280 END IF;
1281 "j" := "j" + 1;
1282 END LOOP;
1283 END IF;
1284 EXIT WHEN "i" = "dimension_v";
1285 "i" := "i" + 1;
1286 END LOOP;
1287 "i" := 1;
1288 LOOP
1289 "rank_ary"["winners_ary"["i"]] := "rank_v";
1290 "done_v" := "done_v" + 1;
1291 EXIT WHEN "i" = array_upper("winners_ary", 1);
1292 "i" := "i" + 1;
1293 END LOOP;
1294 EXIT WHEN "done_v" = "dimension_v";
1295 "rank_v" := "rank_v" + 1;
1296 END LOOP;
1297 -- write preliminary results:
1298 "i" := 1;
1299 FOR "initiative_id_v" IN
1300 SELECT "id" FROM "initiative"
1301 WHERE "issue_id" = "issue_id_p" AND "admitted"
1302 ORDER BY "id"
1303 LOOP
1304 UPDATE "initiative" SET
1305 "direct_majority" =
1306 CASE WHEN "policy_row"."direct_majority_strict" THEN
1307 "positive_votes" * "policy_row"."direct_majority_den" >
1308 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
1309 ELSE
1310 "positive_votes" * "policy_row"."direct_majority_den" >=
1311 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
1312 END
1313 AND "positive_votes" >= "policy_row"."direct_majority_positive"
1314 AND "issue_row"."voter_count"-"negative_votes" >=
1315 "policy_row"."direct_majority_non_negative",
1316 "indirect_majority" =
1317 CASE WHEN "policy_row"."indirect_majority_strict" THEN
1318 "positive_votes" * "policy_row"."indirect_majority_den" >
1319 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
1320 ELSE
1321 "positive_votes" * "policy_row"."indirect_majority_den" >=
1322 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
1323 END
1324 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
1325 AND "issue_row"."voter_count"-"negative_votes" >=
1326 "policy_row"."indirect_majority_non_negative",
1327 "schulze_rank" = "rank_ary"["i"],
1328 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
1329 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
1330 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
1331 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,
1332 "eligible" = FALSE,
1333 "winner" = FALSE,
1334 "rank" = NULL -- NOTE: in cases of manual reset of issue state
1335 WHERE "id" = "initiative_id_v";
1336 "i" := "i" + 1;
1337 END LOOP;
1338 IF "i" != "dimension_v" THEN
1339 RAISE EXCEPTION 'Wrong winner count (should not happen)';
1340 END IF;
1341 -- take indirect majorities into account:
1342 LOOP
1343 UPDATE "initiative" SET "indirect_majority" = TRUE
1344 FROM (
1345 SELECT "new_initiative"."id" AS "initiative_id"
1346 FROM "initiative" "old_initiative"
1347 JOIN "initiative" "new_initiative"
1348 ON "new_initiative"."issue_id" = "issue_id_p"
1349 AND "new_initiative"."indirect_majority" = FALSE
1350 JOIN "battle" "battle_win"
1351 ON "battle_win"."issue_id" = "issue_id_p"
1352 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
1353 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
1354 JOIN "battle" "battle_lose"
1355 ON "battle_lose"."issue_id" = "issue_id_p"
1356 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
1357 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
1358 WHERE "old_initiative"."issue_id" = "issue_id_p"
1359 AND "old_initiative"."indirect_majority" = TRUE
1360 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
1361 "battle_win"."count" * "policy_row"."indirect_majority_den" >
1362 "policy_row"."indirect_majority_num" *
1363 ("battle_win"."count"+"battle_lose"."count")
1364 ELSE
1365 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
1366 "policy_row"."indirect_majority_num" *
1367 ("battle_win"."count"+"battle_lose"."count")
1368 END
1369 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
1370 AND "issue_row"."voter_count"-"battle_lose"."count" >=
1371 "policy_row"."indirect_majority_non_negative"
1372 ) AS "subquery"
1373 WHERE "id" = "subquery"."initiative_id";
1374 EXIT WHEN NOT FOUND;
1375 END LOOP;
1376 -- set "multistage_majority" for remaining matching initiatives:
1377 UPDATE "initiative" SET "multistage_majority" = TRUE
1378 FROM (
1379 SELECT "losing_initiative"."id" AS "initiative_id"
1380 FROM "initiative" "losing_initiative"
1381 JOIN "initiative" "winning_initiative"
1382 ON "winning_initiative"."issue_id" = "issue_id_p"
1383 AND "winning_initiative"."admitted"
1384 JOIN "battle" "battle_win"
1385 ON "battle_win"."issue_id" = "issue_id_p"
1386 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
1387 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
1388 JOIN "battle" "battle_lose"
1389 ON "battle_lose"."issue_id" = "issue_id_p"
1390 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
1391 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
1392 WHERE "losing_initiative"."issue_id" = "issue_id_p"
1393 AND "losing_initiative"."admitted"
1394 AND "winning_initiative"."schulze_rank" <
1395 "losing_initiative"."schulze_rank"
1396 AND "battle_win"."count" > "battle_lose"."count"
1397 AND (
1398 "battle_win"."count" > "winning_initiative"."positive_votes" OR
1399 "battle_lose"."count" < "losing_initiative"."negative_votes" )
1400 ) AS "subquery"
1401 WHERE "id" = "subquery"."initiative_id";
1402 -- mark eligible initiatives:
1403 UPDATE "initiative" SET "eligible" = TRUE
1404 WHERE "issue_id" = "issue_id_p"
1405 AND "initiative"."direct_majority"
1406 AND "initiative"."indirect_majority"
1407 AND "initiative"."better_than_status_quo"
1408 AND (
1409 "policy_row"."no_multistage_majority" = FALSE OR
1410 "initiative"."multistage_majority" = FALSE )
1411 AND (
1412 "policy_row"."no_reverse_beat_path" = FALSE OR
1413 "initiative"."reverse_beat_path" = FALSE );
1414 -- mark final winner:
1415 UPDATE "initiative" SET "winner" = TRUE
1416 FROM (
1417 SELECT "id" AS "initiative_id"
1418 FROM "initiative"
1419 WHERE "issue_id" = "issue_id_p" AND "eligible"
1420 ORDER BY
1421 "schulze_rank",
1422 "id"
1423 LIMIT 1
1424 ) AS "subquery"
1425 WHERE "id" = "subquery"."initiative_id";
1426 -- write (final) ranks:
1427 "rank_v" := 1;
1428 FOR "initiative_id_v" IN
1429 SELECT "id"
1430 FROM "initiative"
1431 WHERE "issue_id" = "issue_id_p" AND "admitted"
1432 ORDER BY
1433 "winner" DESC,
1434 "eligible" DESC,
1435 "schulze_rank",
1436 "id"
1437 LOOP
1438 UPDATE "initiative" SET "rank" = "rank_v"
1439 WHERE "id" = "initiative_id_v";
1440 "rank_v" := "rank_v" + 1;
1441 END LOOP;
1442 -- set schulze rank of status quo and mark issue as finished:
1443 UPDATE "issue" SET
1444 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
1445 "state" =
1446 CASE WHEN EXISTS (
1447 SELECT NULL FROM "initiative"
1448 WHERE "issue_id" = "issue_id_p" AND "winner"
1449 ) THEN
1450 'finished_with_winner'::"issue_state"
1451 ELSE
1452 'finished_without_winner'::"issue_state"
1453 END,
1454 "closed" = "phase_finished",
1455 "phase_finished" = NULL
1456 WHERE "id" = "issue_id_p";
1457 RETURN;
1458 END;
1459 $$;
1461 DROP FUNCTION "check_issue"("issue"."id"%TYPE);
1463 CREATE TYPE "check_issue_persistence" AS (
1464 "state" "issue_state",
1465 "phase_finished" BOOLEAN,
1466 "issue_revoked" BOOLEAN,
1467 "snapshot_created" BOOLEAN,
1468 "harmonic_weights_set" BOOLEAN,
1469 "closed_voting" BOOLEAN );
1470 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';
1472 CREATE FUNCTION "check_issue"
1473 ( "issue_id_p" "issue"."id"%TYPE,
1474 "persist" "check_issue_persistence" )
1475 RETURNS "check_issue_persistence"
1476 LANGUAGE 'plpgsql' VOLATILE AS $$
1477 DECLARE
1478 "issue_row" "issue"%ROWTYPE;
1479 "policy_row" "policy"%ROWTYPE;
1480 "initiative_row" "initiative"%ROWTYPE;
1481 "state_v" "issue_state";
1482 BEGIN
1483 PERFORM "require_transaction_isolation"();
1484 IF "persist" ISNULL THEN
1485 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
1486 FOR UPDATE;
1487 IF "issue_row"."closed" NOTNULL THEN
1488 RETURN NULL;
1489 END IF;
1490 "persist"."state" := "issue_row"."state";
1491 IF
1492 ( "issue_row"."state" = 'admission' AND now() >=
1493 "issue_row"."created" + "issue_row"."admission_time" ) OR
1494 ( "issue_row"."state" = 'discussion' AND now() >=
1495 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
1496 ( "issue_row"."state" = 'verification' AND now() >=
1497 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
1498 ( "issue_row"."state" = 'voting' AND now() >=
1499 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
1500 THEN
1501 "persist"."phase_finished" := TRUE;
1502 ELSE
1503 "persist"."phase_finished" := FALSE;
1504 END IF;
1505 IF
1506 NOT EXISTS (
1507 -- all initiatives are revoked
1508 SELECT NULL FROM "initiative"
1509 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
1510 ) AND (
1511 -- and issue has not been accepted yet
1512 "persist"."state" = 'admission' OR
1513 -- or verification time has elapsed
1514 ( "persist"."state" = 'verification' AND
1515 "persist"."phase_finished" ) OR
1516 -- or no initiatives have been revoked lately
1517 NOT EXISTS (
1518 SELECT NULL FROM "initiative"
1519 WHERE "issue_id" = "issue_id_p"
1520 AND now() < "revoked" + "issue_row"."verification_time"
1521 )
1522 )
1523 THEN
1524 "persist"."issue_revoked" := TRUE;
1525 ELSE
1526 "persist"."issue_revoked" := FALSE;
1527 END IF;
1528 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
1529 UPDATE "issue" SET "phase_finished" = now()
1530 WHERE "id" = "issue_row"."id";
1531 RETURN "persist";
1532 ELSIF
1533 "persist"."state" IN ('admission', 'discussion', 'verification')
1534 THEN
1535 RETURN "persist";
1536 ELSE
1537 RETURN NULL;
1538 END IF;
1539 END IF;
1540 IF
1541 "persist"."state" IN ('admission', 'discussion', 'verification') AND
1542 coalesce("persist"."snapshot_created", FALSE) = FALSE
1543 THEN
1544 PERFORM "create_snapshot"("issue_id_p");
1545 "persist"."snapshot_created" = TRUE;
1546 IF "persist"."phase_finished" THEN
1547 IF "persist"."state" = 'admission' THEN
1548 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
1549 ELSIF "persist"."state" = 'discussion' THEN
1550 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
1551 ELSIF "persist"."state" = 'verification' THEN
1552 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
1553 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1554 SELECT * INTO "policy_row" FROM "policy"
1555 WHERE "id" = "issue_row"."policy_id";
1556 FOR "initiative_row" IN
1557 SELECT * FROM "initiative"
1558 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
1559 FOR UPDATE
1560 LOOP
1561 IF
1562 "initiative_row"."polling" OR (
1563 "initiative_row"."satisfied_supporter_count" > 0 AND
1564 "initiative_row"."satisfied_supporter_count" *
1565 "policy_row"."initiative_quorum_den" >=
1566 "issue_row"."population" * "policy_row"."initiative_quorum_num"
1567 )
1568 THEN
1569 UPDATE "initiative" SET "admitted" = TRUE
1570 WHERE "id" = "initiative_row"."id";
1571 ELSE
1572 UPDATE "initiative" SET "admitted" = FALSE
1573 WHERE "id" = "initiative_row"."id";
1574 END IF;
1575 END LOOP;
1576 END IF;
1577 END IF;
1578 RETURN "persist";
1579 END IF;
1580 IF
1581 "persist"."state" IN ('admission', 'discussion', 'verification') AND
1582 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
1583 THEN
1584 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
1585 "persist"."harmonic_weights_set" = TRUE;
1586 IF
1587 "persist"."phase_finished" OR
1588 "persist"."issue_revoked" OR
1589 "persist"."state" = 'admission'
1590 THEN
1591 RETURN "persist";
1592 ELSE
1593 RETURN NULL;
1594 END IF;
1595 END IF;
1596 IF "persist"."issue_revoked" THEN
1597 IF "persist"."state" = 'admission' THEN
1598 "state_v" := 'canceled_revoked_before_accepted';
1599 ELSIF "persist"."state" = 'discussion' THEN
1600 "state_v" := 'canceled_after_revocation_during_discussion';
1601 ELSIF "persist"."state" = 'verification' THEN
1602 "state_v" := 'canceled_after_revocation_during_verification';
1603 END IF;
1604 UPDATE "issue" SET
1605 "state" = "state_v",
1606 "closed" = "phase_finished",
1607 "phase_finished" = NULL
1608 WHERE "id" = "issue_id_p";
1609 RETURN NULL;
1610 END IF;
1611 IF "persist"."state" = 'admission' THEN
1612 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
1613 FOR UPDATE;
1614 SELECT * INTO "policy_row"
1615 FROM "policy" WHERE "id" = "issue_row"."policy_id";
1616 IF EXISTS (
1617 SELECT NULL FROM "initiative"
1618 WHERE "issue_id" = "issue_id_p"
1619 AND "supporter_count" > 0
1620 AND "supporter_count" * "policy_row"."issue_quorum_den"
1621 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
1622 ) THEN
1623 UPDATE "issue" SET
1624 "state" = 'discussion',
1625 "accepted" = coalesce("phase_finished", now()),
1626 "phase_finished" = NULL
1627 WHERE "id" = "issue_id_p";
1628 ELSIF "issue_row"."phase_finished" NOTNULL THEN
1629 UPDATE "issue" SET
1630 "state" = 'canceled_issue_not_accepted',
1631 "closed" = "phase_finished",
1632 "phase_finished" = NULL
1633 WHERE "id" = "issue_id_p";
1634 END IF;
1635 RETURN NULL;
1636 END IF;
1637 IF "persist"."phase_finished" THEN
1638 if "persist"."state" = 'discussion' THEN
1639 UPDATE "issue" SET
1640 "state" = 'verification',
1641 "half_frozen" = "phase_finished",
1642 "phase_finished" = NULL
1643 WHERE "id" = "issue_id_p";
1644 RETURN NULL;
1645 END IF;
1646 IF "persist"."state" = 'verification' THEN
1647 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
1648 FOR UPDATE;
1649 SELECT * INTO "policy_row" FROM "policy"
1650 WHERE "id" = "issue_row"."policy_id";
1651 IF EXISTS (
1652 SELECT NULL FROM "initiative"
1653 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
1654 ) THEN
1655 UPDATE "issue" SET
1656 "state" = 'voting',
1657 "fully_frozen" = "phase_finished",
1658 "phase_finished" = NULL
1659 WHERE "id" = "issue_id_p";
1660 ELSE
1661 UPDATE "issue" SET
1662 "state" = 'canceled_no_initiative_admitted',
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 SELECT "set_harmonic_initiative_weights"("id") FROM "issue";
1763 COMMIT;