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