liquid_feedback_core
view update/core-update.v1.2.4-v1.2.5.sql @ 354:c23bb5ebcdec
Free memory for candidate and ballot structure in "lf_update_suggestion_order"
| author | jbe |
|---|---|
| date | Sat Mar 16 18:23:06 2013 +0100 (2013-03-16) |
| parents | 72e5356b5454 |
| children |
line source
1 BEGIN;
4 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
5 SELECT * FROM (VALUES ('1.2.5', 1, 2, 5))
6 AS "subquery"("string", "major", "minor", "revision");
9 CREATE FUNCTION "share_row_lock_issue_trigger"()
10 RETURNS TRIGGER
11 LANGUAGE 'plpgsql' VOLATILE AS $$
12 BEGIN
13 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
14 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
15 END IF;
16 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
17 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
18 RETURN NEW;
19 ELSE
20 RETURN OLD;
21 END IF;
22 END;
23 $$;
25 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
28 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
29 RETURNS TRIGGER
30 LANGUAGE 'plpgsql' VOLATILE AS $$
31 BEGIN
32 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
33 PERFORM NULL FROM "issue"
34 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
35 WHERE "initiative"."id" = OLD."initiative_id"
36 FOR SHARE OF "issue";
37 END IF;
38 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
39 PERFORM NULL FROM "issue"
40 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
41 WHERE "initiative"."id" = NEW."initiative_id"
42 FOR SHARE OF "issue";
43 RETURN NEW;
44 ELSE
45 RETURN OLD;
46 END IF;
47 END;
48 $$;
50 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
53 CREATE TRIGGER "share_row_lock_issue"
54 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
55 FOR EACH ROW EXECUTE PROCEDURE
56 "share_row_lock_issue_trigger"();
58 CREATE TRIGGER "share_row_lock_issue"
59 BEFORE INSERT OR UPDATE OR DELETE ON "interest"
60 FOR EACH ROW EXECUTE PROCEDURE
61 "share_row_lock_issue_trigger"();
63 CREATE TRIGGER "share_row_lock_issue"
64 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
65 FOR EACH ROW EXECUTE PROCEDURE
66 "share_row_lock_issue_trigger"();
68 CREATE TRIGGER "share_row_lock_issue_via_initiative"
69 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
70 FOR EACH ROW EXECUTE PROCEDURE
71 "share_row_lock_issue_via_initiative_trigger"();
73 CREATE TRIGGER "share_row_lock_issue"
74 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
75 FOR EACH ROW EXECUTE PROCEDURE
76 "share_row_lock_issue_trigger"();
78 CREATE TRIGGER "share_row_lock_issue"
79 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
80 FOR EACH ROW EXECUTE PROCEDURE
81 "share_row_lock_issue_trigger"();
83 CREATE TRIGGER "share_row_lock_issue"
84 BEFORE INSERT OR UPDATE OR DELETE ON "vote"
85 FOR EACH ROW EXECUTE PROCEDURE
86 "share_row_lock_issue_trigger"();
88 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';
89 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';
90 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';
91 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';
92 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';
93 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';
94 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';
97 CREATE FUNCTION "lock_issue"
98 ( "issue_id_p" "issue"."id"%TYPE )
99 RETURNS VOID
100 LANGUAGE 'plpgsql' VOLATILE AS $$
101 BEGIN
102 LOCK TABLE "member" IN SHARE MODE;
103 LOCK TABLE "membership" IN SHARE MODE;
104 LOCK TABLE "policy" IN SHARE MODE;
105 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
106 -- NOTE: The row-level exclusive lock in combination with the
107 -- share_row_lock_issue(_via_initiative)_trigger functions (which
108 -- acquire a row-level share lock on the issue) ensure that no data
109 -- is changed, which could affect calculation of snapshots or
110 -- counting of votes. Table "delegation" must be table-level-locked,
111 -- as it also contains issue- and global-scope delegations.
112 LOCK TABLE "delegation" IN SHARE MODE;
113 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
114 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
115 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
116 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
117 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
118 RETURN;
119 END;
120 $$;
122 COMMENT ON FUNCTION "lock_issue"
123 ( "issue"."id"%TYPE )
124 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
127 CREATE OR REPLACE FUNCTION "calculate_member_counts"()
128 RETURNS VOID
129 LANGUAGE 'plpgsql' VOLATILE AS $$
130 BEGIN
131 LOCK TABLE "member" IN SHARE MODE;
132 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
133 LOCK TABLE "area" IN EXCLUSIVE MODE;
134 LOCK TABLE "membership" IN SHARE MODE;
135 DELETE FROM "member_count";
136 INSERT INTO "member_count" ("total_count")
137 SELECT "total_count" FROM "member_count_view";
138 UPDATE "area" SET
139 "direct_member_count" = "view"."direct_member_count",
140 "member_weight" = "view"."member_weight",
141 "autoreject_weight" = "view"."autoreject_weight"
142 FROM "area_member_count" AS "view"
143 WHERE "view"."area_id" = "area"."id";
144 RETURN;
145 END;
146 $$;
148 CREATE OR REPLACE FUNCTION "create_snapshot"
149 ( "issue_id_p" "issue"."id"%TYPE )
150 RETURNS VOID
151 LANGUAGE 'plpgsql' VOLATILE AS $$
152 DECLARE
153 "initiative_id_v" "initiative"."id"%TYPE;
154 "suggestion_id_v" "suggestion"."id"%TYPE;
155 BEGIN
156 PERFORM "lock_issue"("issue_id_p");
157 PERFORM "create_population_snapshot"("issue_id_p");
158 PERFORM "create_interest_snapshot"("issue_id_p");
159 UPDATE "issue" SET
160 "snapshot" = now(),
161 "latest_snapshot_event" = 'periodic',
162 "population" = (
163 SELECT coalesce(sum("weight"), 0)
164 FROM "direct_population_snapshot"
165 WHERE "issue_id" = "issue_id_p"
166 AND "event" = 'periodic'
167 ),
168 "vote_now" = (
169 SELECT coalesce(sum("weight"), 0)
170 FROM "direct_interest_snapshot"
171 WHERE "issue_id" = "issue_id_p"
172 AND "event" = 'periodic'
173 AND "voting_requested" = TRUE
174 ),
175 "vote_later" = (
176 SELECT coalesce(sum("weight"), 0)
177 FROM "direct_interest_snapshot"
178 WHERE "issue_id" = "issue_id_p"
179 AND "event" = 'periodic'
180 AND "voting_requested" = FALSE
181 )
182 WHERE "id" = "issue_id_p";
183 FOR "initiative_id_v" IN
184 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
185 LOOP
186 UPDATE "initiative" SET
187 "supporter_count" = (
188 SELECT coalesce(sum("di"."weight"), 0)
189 FROM "direct_interest_snapshot" AS "di"
190 JOIN "direct_supporter_snapshot" AS "ds"
191 ON "di"."member_id" = "ds"."member_id"
192 WHERE "di"."issue_id" = "issue_id_p"
193 AND "di"."event" = 'periodic'
194 AND "ds"."initiative_id" = "initiative_id_v"
195 AND "ds"."event" = 'periodic'
196 ),
197 "informed_supporter_count" = (
198 SELECT coalesce(sum("di"."weight"), 0)
199 FROM "direct_interest_snapshot" AS "di"
200 JOIN "direct_supporter_snapshot" AS "ds"
201 ON "di"."member_id" = "ds"."member_id"
202 WHERE "di"."issue_id" = "issue_id_p"
203 AND "di"."event" = 'periodic'
204 AND "ds"."initiative_id" = "initiative_id_v"
205 AND "ds"."event" = 'periodic'
206 AND "ds"."informed"
207 ),
208 "satisfied_supporter_count" = (
209 SELECT coalesce(sum("di"."weight"), 0)
210 FROM "direct_interest_snapshot" AS "di"
211 JOIN "direct_supporter_snapshot" AS "ds"
212 ON "di"."member_id" = "ds"."member_id"
213 WHERE "di"."issue_id" = "issue_id_p"
214 AND "di"."event" = 'periodic'
215 AND "ds"."initiative_id" = "initiative_id_v"
216 AND "ds"."event" = 'periodic'
217 AND "ds"."satisfied"
218 ),
219 "satisfied_informed_supporter_count" = (
220 SELECT coalesce(sum("di"."weight"), 0)
221 FROM "direct_interest_snapshot" AS "di"
222 JOIN "direct_supporter_snapshot" AS "ds"
223 ON "di"."member_id" = "ds"."member_id"
224 WHERE "di"."issue_id" = "issue_id_p"
225 AND "di"."event" = 'periodic'
226 AND "ds"."initiative_id" = "initiative_id_v"
227 AND "ds"."event" = 'periodic'
228 AND "ds"."informed"
229 AND "ds"."satisfied"
230 )
231 WHERE "id" = "initiative_id_v";
232 FOR "suggestion_id_v" IN
233 SELECT "id" FROM "suggestion"
234 WHERE "initiative_id" = "initiative_id_v"
235 LOOP
236 UPDATE "suggestion" SET
237 "minus2_unfulfilled_count" = (
238 SELECT coalesce(sum("snapshot"."weight"), 0)
239 FROM "issue" CROSS JOIN "opinion"
240 JOIN "direct_interest_snapshot" AS "snapshot"
241 ON "snapshot"."issue_id" = "issue"."id"
242 AND "snapshot"."event" = "issue"."latest_snapshot_event"
243 AND "snapshot"."member_id" = "opinion"."member_id"
244 WHERE "issue"."id" = "issue_id_p"
245 AND "opinion"."suggestion_id" = "suggestion_id_v"
246 AND "opinion"."degree" = -2
247 AND "opinion"."fulfilled" = FALSE
248 ),
249 "minus2_fulfilled_count" = (
250 SELECT coalesce(sum("snapshot"."weight"), 0)
251 FROM "issue" CROSS JOIN "opinion"
252 JOIN "direct_interest_snapshot" AS "snapshot"
253 ON "snapshot"."issue_id" = "issue"."id"
254 AND "snapshot"."event" = "issue"."latest_snapshot_event"
255 AND "snapshot"."member_id" = "opinion"."member_id"
256 WHERE "issue"."id" = "issue_id_p"
257 AND "opinion"."suggestion_id" = "suggestion_id_v"
258 AND "opinion"."degree" = -2
259 AND "opinion"."fulfilled" = TRUE
260 ),
261 "minus1_unfulfilled_count" = (
262 SELECT coalesce(sum("snapshot"."weight"), 0)
263 FROM "issue" CROSS JOIN "opinion"
264 JOIN "direct_interest_snapshot" AS "snapshot"
265 ON "snapshot"."issue_id" = "issue"."id"
266 AND "snapshot"."event" = "issue"."latest_snapshot_event"
267 AND "snapshot"."member_id" = "opinion"."member_id"
268 WHERE "issue"."id" = "issue_id_p"
269 AND "opinion"."suggestion_id" = "suggestion_id_v"
270 AND "opinion"."degree" = -1
271 AND "opinion"."fulfilled" = FALSE
272 ),
273 "minus1_fulfilled_count" = (
274 SELECT coalesce(sum("snapshot"."weight"), 0)
275 FROM "issue" CROSS JOIN "opinion"
276 JOIN "direct_interest_snapshot" AS "snapshot"
277 ON "snapshot"."issue_id" = "issue"."id"
278 AND "snapshot"."event" = "issue"."latest_snapshot_event"
279 AND "snapshot"."member_id" = "opinion"."member_id"
280 WHERE "issue"."id" = "issue_id_p"
281 AND "opinion"."suggestion_id" = "suggestion_id_v"
282 AND "opinion"."degree" = -1
283 AND "opinion"."fulfilled" = TRUE
284 ),
285 "plus1_unfulfilled_count" = (
286 SELECT coalesce(sum("snapshot"."weight"), 0)
287 FROM "issue" CROSS JOIN "opinion"
288 JOIN "direct_interest_snapshot" AS "snapshot"
289 ON "snapshot"."issue_id" = "issue"."id"
290 AND "snapshot"."event" = "issue"."latest_snapshot_event"
291 AND "snapshot"."member_id" = "opinion"."member_id"
292 WHERE "issue"."id" = "issue_id_p"
293 AND "opinion"."suggestion_id" = "suggestion_id_v"
294 AND "opinion"."degree" = 1
295 AND "opinion"."fulfilled" = FALSE
296 ),
297 "plus1_fulfilled_count" = (
298 SELECT coalesce(sum("snapshot"."weight"), 0)
299 FROM "issue" CROSS JOIN "opinion"
300 JOIN "direct_interest_snapshot" AS "snapshot"
301 ON "snapshot"."issue_id" = "issue"."id"
302 AND "snapshot"."event" = "issue"."latest_snapshot_event"
303 AND "snapshot"."member_id" = "opinion"."member_id"
304 WHERE "issue"."id" = "issue_id_p"
305 AND "opinion"."suggestion_id" = "suggestion_id_v"
306 AND "opinion"."degree" = 1
307 AND "opinion"."fulfilled" = TRUE
308 ),
309 "plus2_unfulfilled_count" = (
310 SELECT coalesce(sum("snapshot"."weight"), 0)
311 FROM "issue" CROSS JOIN "opinion"
312 JOIN "direct_interest_snapshot" AS "snapshot"
313 ON "snapshot"."issue_id" = "issue"."id"
314 AND "snapshot"."event" = "issue"."latest_snapshot_event"
315 AND "snapshot"."member_id" = "opinion"."member_id"
316 WHERE "issue"."id" = "issue_id_p"
317 AND "opinion"."suggestion_id" = "suggestion_id_v"
318 AND "opinion"."degree" = 2
319 AND "opinion"."fulfilled" = FALSE
320 ),
321 "plus2_fulfilled_count" = (
322 SELECT coalesce(sum("snapshot"."weight"), 0)
323 FROM "issue" CROSS JOIN "opinion"
324 JOIN "direct_interest_snapshot" AS "snapshot"
325 ON "snapshot"."issue_id" = "issue"."id"
326 AND "snapshot"."event" = "issue"."latest_snapshot_event"
327 AND "snapshot"."member_id" = "opinion"."member_id"
328 WHERE "issue"."id" = "issue_id_p"
329 AND "opinion"."suggestion_id" = "suggestion_id_v"
330 AND "opinion"."degree" = 2
331 AND "opinion"."fulfilled" = TRUE
332 )
333 WHERE "suggestion"."id" = "suggestion_id_v";
334 END LOOP;
335 END LOOP;
336 RETURN;
337 END;
338 $$;
340 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
341 RETURNS VOID
342 LANGUAGE 'plpgsql' VOLATILE AS $$
343 DECLARE
344 "issue_row" "issue"%ROWTYPE;
345 "member_id_v" "member"."id"%TYPE;
346 BEGIN
347 PERFORM "lock_issue"("issue_id_p");
348 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
349 DELETE FROM "delegating_voter"
350 WHERE "issue_id" = "issue_id_p";
351 DELETE FROM "direct_voter"
352 WHERE "issue_id" = "issue_id_p"
353 AND "autoreject" = TRUE;
354 DELETE FROM "direct_voter" USING "member"
355 WHERE "direct_voter"."member_id" = "member"."id"
356 AND "direct_voter"."issue_id" = "issue_id_p"
357 AND "member"."active" = FALSE;
358 UPDATE "direct_voter" SET "weight" = 1
359 WHERE "issue_id" = "issue_id_p";
360 PERFORM "add_vote_delegations"("issue_id_p");
361 FOR "member_id_v" IN
362 SELECT "interest"."member_id"
363 FROM "interest"
364 LEFT JOIN "direct_voter"
365 ON "interest"."member_id" = "direct_voter"."member_id"
366 AND "interest"."issue_id" = "direct_voter"."issue_id"
367 LEFT JOIN "delegating_voter"
368 ON "interest"."member_id" = "delegating_voter"."member_id"
369 AND "interest"."issue_id" = "delegating_voter"."issue_id"
370 WHERE "interest"."issue_id" = "issue_id_p"
371 AND "interest"."autoreject" = TRUE
372 AND "direct_voter"."member_id" ISNULL
373 AND "delegating_voter"."member_id" ISNULL
374 UNION SELECT "membership"."member_id"
375 FROM "membership"
376 LEFT JOIN "interest"
377 ON "membership"."member_id" = "interest"."member_id"
378 AND "interest"."issue_id" = "issue_id_p"
379 LEFT JOIN "direct_voter"
380 ON "membership"."member_id" = "direct_voter"."member_id"
381 AND "direct_voter"."issue_id" = "issue_id_p"
382 LEFT JOIN "delegating_voter"
383 ON "membership"."member_id" = "delegating_voter"."member_id"
384 AND "delegating_voter"."issue_id" = "issue_id_p"
385 WHERE "membership"."area_id" = "issue_row"."area_id"
386 AND "membership"."autoreject" = TRUE
387 AND "interest"."autoreject" ISNULL
388 AND "direct_voter"."member_id" ISNULL
389 AND "delegating_voter"."member_id" ISNULL
390 LOOP
391 INSERT INTO "direct_voter"
392 ("member_id", "issue_id", "weight", "autoreject") VALUES
393 ("member_id_v", "issue_id_p", 1, TRUE);
394 INSERT INTO "vote" (
395 "member_id",
396 "issue_id",
397 "initiative_id",
398 "grade"
399 ) SELECT
400 "member_id_v" AS "member_id",
401 "issue_id_p" AS "issue_id",
402 "id" AS "initiative_id",
403 -1 AS "grade"
404 FROM "initiative" WHERE "issue_id" = "issue_id_p";
405 END LOOP;
406 PERFORM "add_vote_delegations"("issue_id_p");
407 UPDATE "issue" SET
408 "closed" = now(),
409 "voter_count" = (
410 SELECT coalesce(sum("weight"), 0)
411 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
412 )
413 WHERE "id" = "issue_id_p";
414 UPDATE "initiative" SET
415 "positive_votes" = "vote_counts"."positive_votes",
416 "negative_votes" = "vote_counts"."negative_votes",
417 "agreed" = CASE WHEN "majority_strict" THEN
418 "vote_counts"."positive_votes" * "majority_den" >
419 "majority_num" *
420 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
421 ELSE
422 "vote_counts"."positive_votes" * "majority_den" >=
423 "majority_num" *
424 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
425 END
426 FROM
427 ( SELECT
428 "initiative"."id" AS "initiative_id",
429 coalesce(
430 sum(
431 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
432 ),
433 0
434 ) AS "positive_votes",
435 coalesce(
436 sum(
437 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
438 ),
439 0
440 ) AS "negative_votes"
441 FROM "initiative"
442 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
443 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
444 LEFT JOIN "direct_voter"
445 ON "direct_voter"."issue_id" = "initiative"."issue_id"
446 LEFT JOIN "vote"
447 ON "vote"."initiative_id" = "initiative"."id"
448 AND "vote"."member_id" = "direct_voter"."member_id"
449 WHERE "initiative"."issue_id" = "issue_id_p"
450 AND "initiative"."admitted" -- NOTE: NULL case is handled too
451 GROUP BY "initiative"."id"
452 ) AS "vote_counts",
453 "issue",
454 "policy"
455 WHERE "vote_counts"."initiative_id" = "initiative"."id"
456 AND "issue"."id" = "initiative"."issue_id"
457 AND "policy"."id" = "issue"."policy_id";
458 -- NOTE: "closed" column of issue must be set at this point
459 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
460 INSERT INTO "battle" (
461 "issue_id",
462 "winning_initiative_id", "losing_initiative_id",
463 "count"
464 ) SELECT
465 "issue_id",
466 "winning_initiative_id", "losing_initiative_id",
467 "count"
468 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
469 END;
470 $$;
472 CREATE OR REPLACE FUNCTION "check_issue"
473 ( "issue_id_p" "issue"."id"%TYPE )
474 RETURNS VOID
475 LANGUAGE 'plpgsql' VOLATILE AS $$
476 DECLARE
477 "issue_row" "issue"%ROWTYPE;
478 "policy_row" "policy"%ROWTYPE;
479 "voting_requested_v" BOOLEAN;
480 BEGIN
481 PERFORM "lock_issue"("issue_id_p");
482 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
483 -- only process open issues:
484 IF "issue_row"."closed" ISNULL THEN
485 SELECT * INTO "policy_row" FROM "policy"
486 WHERE "id" = "issue_row"."policy_id";
487 -- create a snapshot, unless issue is already fully frozen:
488 IF "issue_row"."fully_frozen" ISNULL THEN
489 PERFORM "create_snapshot"("issue_id_p");
490 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
491 END IF;
492 -- eventually close or accept issues, which have not been accepted:
493 IF "issue_row"."accepted" ISNULL THEN
494 IF EXISTS (
495 SELECT NULL FROM "initiative"
496 WHERE "issue_id" = "issue_id_p"
497 AND "supporter_count" > 0
498 AND "supporter_count" * "policy_row"."issue_quorum_den"
499 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
500 ) THEN
501 -- accept issues, if supporter count is high enough
502 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
503 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
504 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
505 WHERE "id" = "issue_row"."id";
506 ELSIF
507 now() >= "issue_row"."created" + "issue_row"."admission_time"
508 THEN
509 -- close issues, if admission time has expired
510 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
511 UPDATE "issue" SET "closed" = now()
512 WHERE "id" = "issue_row"."id";
513 END IF;
514 END IF;
515 -- eventually half freeze issues:
516 IF
517 -- NOTE: issue can't be closed at this point, if it has been accepted
518 "issue_row"."accepted" NOTNULL AND
519 "issue_row"."half_frozen" ISNULL
520 THEN
521 SELECT
522 CASE
523 WHEN "vote_now" * 2 > "issue_row"."population" THEN
524 TRUE
525 WHEN "vote_later" * 2 > "issue_row"."population" THEN
526 FALSE
527 ELSE NULL
528 END
529 INTO "voting_requested_v"
530 FROM "issue" WHERE "id" = "issue_id_p";
531 IF
532 "voting_requested_v" OR (
533 "voting_requested_v" ISNULL AND
534 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
535 )
536 THEN
537 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
538 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later
539 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
540 WHERE "id" = "issue_row"."id";
541 END IF;
542 END IF;
543 -- close issues after some time, if all initiatives have been revoked:
544 IF
545 "issue_row"."closed" ISNULL AND
546 NOT EXISTS (
547 -- all initiatives are revoked
548 SELECT NULL FROM "initiative"
549 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
550 ) AND (
551 NOT EXISTS (
552 -- and no initiatives have been revoked lately
553 SELECT NULL FROM "initiative"
554 WHERE "issue_id" = "issue_id_p"
555 AND now() < "revoked" + "issue_row"."verification_time"
556 ) OR (
557 -- or verification time has elapsed
558 "issue_row"."half_frozen" NOTNULL AND
559 "issue_row"."fully_frozen" ISNULL AND
560 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
561 )
562 )
563 THEN
564 "issue_row"."closed" = now(); -- NOTE: "issue_row" used later
565 UPDATE "issue" SET "closed" = "issue_row"."closed"
566 WHERE "id" = "issue_row"."id";
567 END IF;
568 -- fully freeze issue after verification time:
569 IF
570 "issue_row"."half_frozen" NOTNULL AND
571 "issue_row"."fully_frozen" ISNULL AND
572 "issue_row"."closed" ISNULL AND
573 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
574 THEN
575 PERFORM "freeze_after_snapshot"("issue_id_p");
576 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
577 END IF;
578 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
579 -- close issue by calling close_voting(...) after voting time:
580 IF
581 "issue_row"."closed" ISNULL AND
582 "issue_row"."fully_frozen" NOTNULL AND
583 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
584 THEN
585 PERFORM "close_voting"("issue_id_p");
586 END IF;
587 END IF;
588 RETURN;
589 END;
590 $$;
593 DROP FUNCTION "global_lock"();
596 COMMIT;