liquid_feedback_core
view update/core-update.v1.2.4-v1.2.5.sql @ 142:54ac8c473263
Use an improved definition for "disqualified" initiatives
"initiative"."disqualified" is TRUE, if the initiative may not win, because it either (a) has no better rank than the status quo, or (b) because there exists a better ranked initiative X, which directly beats this initiative, and either more voters prefer X to this initiative than voters preferring X to the status quo or less voters prefer this initiative to X than voters preferring the status quo to X
"initiative"."disqualified" is TRUE, if the initiative may not win, because it either (a) has no better rank than the status quo, or (b) because there exists a better ranked initiative X, which directly beats this initiative, and either more voters prefer X to this initiative than voters preferring X to the status quo or less voters prefer this initiative to X than voters preferring the status quo to X
| author | jbe |
|---|---|
| date | Wed Jun 01 16:58:00 2011 +0200 (2011-06-01) |
| 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;