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