liquid_feedback_core

view update/core-update.v1.3.1-v1.4.0.sql @ 593:e7f772ca0621

Removed an index on table "initiative"
author jbe
date Sat Dec 08 01:37:23 2018 +0100 (20 months ago)
parents eee75cff3e5a
children
line source
1 BEGIN; -- NOTE: file contains additional statements AFTER this BEGIN/COMMIT block!
4 -- Update version information:
6 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
7 SELECT * FROM (VALUES ('1.4.0', 1, 4, 0))
8 AS "subquery"("string", "major", "minor", "revision");
11 -- New columns "notify_level" and "notify_event_id" in "member" table:
13 CREATE TYPE "notify_level" AS ENUM
14 ('none', 'voting', 'verification', 'discussion', 'all');
16 COMMENT ON TYPE "notify_level" IS 'Level of notification: ''none'' = no notifications, ''voting'' = notifications about finished issues and issues in voting, ''verification'' = notifications about finished issues, issues in voting and verification phase, ''discussion'' = notifications about everything except issues in admission phase, ''all'' = notifications about everything';
18 ALTER TABLE "member" ADD "notify_level" "notify_level" NOT NULL DEFAULT 'none';
19 ALTER TABLE "member" ADD "notify_event_id" INT8;
21 COMMENT ON COLUMN "member"."notify_level" IS 'Selects which event notifications are to be sent to the "notify_email" mail address';
22 COMMENT ON COLUMN "member"."notify_event_id" IS 'Latest "id" of an "event" the member was notified about';
25 -- Add primary key with type SERIAL8 (INT8) for "invite_code" table:
27 ALTER TABLE "invite_code" DROP CONSTRAINT "invite_code_pkey";
28 ALTER TABLE "invite_code" ALTER "code" SET NOT NULL;
29 ALTER TABLE "invite_code" ADD UNIQUE ("code");
30 ALTER TABLE "invite_code" ADD "id" SERIAL8 PRIMARY KEY;
33 -- Add index for "other_member_id" column of "contact" table:
35 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
38 -- New table "ignored_member":
40 CREATE TABLE "ignored_member" (
41 PRIMARY KEY ("member_id", "other_member_id"),
42 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
43 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
44 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
46 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
48 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';
49 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
52 -- New table "unit" with default entry:
54 CREATE TABLE "unit" (
55 "id" SERIAL4 PRIMARY KEY,
56 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
57 "active" BOOLEAN NOT NULL DEFAULT TRUE,
58 "name" TEXT NOT NULL,
59 "description" TEXT NOT NULL DEFAULT '',
60 "member_count" INT4,
61 "text_search_data" TSVECTOR );
62 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
63 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
64 CREATE INDEX "unit_active_idx" ON "unit" ("active");
65 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
66 CREATE TRIGGER "update_text_search_data"
67 BEFORE INSERT OR UPDATE ON "unit"
68 FOR EACH ROW EXECUTE PROCEDURE
69 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
70 "name", "description" );
72 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
74 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
75 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in units of this area';
76 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
78 INSERT INTO "unit" ("name") VALUES ('Main'); -- NOTE: gets id 1
81 -- New column "unit_id" in table "area":
83 ALTER TABLE "area" ADD "unit_id" INT4 DEFAULT 1
84 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
85 ALTER TABLE "area" ALTER "unit_id" DROP DEFAULT;
87 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
90 -- Issue states:
92 CREATE TYPE "issue_state" AS ENUM (
93 'admission', 'discussion', 'verification', 'voting',
94 'canceled_revoked_before_accepted',
95 'canceled_issue_not_accepted',
96 'canceled_after_revocation_during_discussion',
97 'canceled_after_revocation_during_verification',
98 'calculation',
99 'canceled_no_initiative_admitted',
100 'finished_without_winner', 'finished_with_winner');
102 COMMENT ON TYPE "issue_state" IS 'State of issues';
104 ALTER TABLE "issue" ADD "state" "issue_state" DEFAULT NULL;
105 ALTER TABLE "issue" ALTER "state" SET DEFAULT 'admission';
107 -- NOTE: Filling new column with values is done after this transaction (see below)
110 -- New column "revoked_by_member_id" in table "initiative":
112 ALTER TABLE "initiative" ADD "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
114 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoked the initiative';
116 -- NOTE: Filling new column with values is done after this transaction (see below)
119 -- New table "ignored_initiative":
121 CREATE TABLE "ignored_initiative" (
122 PRIMARY KEY ("initiative_id", "member_id"),
123 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
124 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
125 CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
127 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
130 -- New table "invite_code_unit":
132 CREATE TABLE "invite_code_unit" (
133 PRIMARY KEY ("invite_code_id", "unit_id"),
134 "invite_code_id" INT8 REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
135 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
137 COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights';
139 INSERT INTO "invite_code_unit" ("invite_code_id", "unit_id")
140 SELECT "id" AS "invite_code_id", 1 AS "unit_id" FROM "invite_code";
143 -- New table "privilege":
145 CREATE TABLE "privilege" (
146 PRIMARY KEY ("unit_id", "member_id"),
147 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
148 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
149 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
150 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
151 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
152 "voting_right_manager" BOOLEAN NOT NULL DEFAULT FALSE,
153 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE );
155 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
157 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other users';
158 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create or lock sub units';
159 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create or lock areas and set area parameters';
160 COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote inside the unit';
161 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to discuss and vote';
163 INSERT INTO "privilege" ("unit_id", "member_id", "voting_right")
164 SELECT 1 AS "unit_id", "id" AS "member_id", TRUE AS "voting_right"
165 FROM "member";
168 -- Remove table "ignored_issue", which is no longer existent:
170 DROP TABLE "ignored_issue";
173 -- Replace TYPE "delegation_scope" with a new type, where 'global' is replaced by 'unit':
175 ALTER TYPE "delegation_scope" RENAME TO "delegation_scope_old"; -- NOTE: dropped later
176 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
177 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
180 -- Delete views and functions being dependent on type "delegation_scope":
182 DROP FUNCTION "delegation_chain"
183 ( "member_id_p" "member"."id"%TYPE,
184 "area_id_p" "area"."id"%TYPE,
185 "issue_id_p" "issue"."id"%TYPE );
187 DROP FUNCTION "delegation_chain"
188 ( "member_id_p" "member"."id"%TYPE,
189 "area_id_p" "area"."id"%TYPE,
190 "issue_id_p" "issue"."id"%TYPE,
191 "simulate_trustee_id_p" "member"."id"%TYPE );
193 DROP TYPE "delegation_chain_row";
195 DROP VIEW "issue_delegation";
196 DROP VIEW "area_delegation";
197 DROP VIEW "global_delegation";
198 DROP VIEW "active_delegation";
201 -- Modify "delegation" table to use new "delegation_scope" type:
203 ALTER TABLE "delegation" DROP CONSTRAINT "no_global_delegation_to_null";
204 ALTER TABLE "delegation" DROP CONSTRAINT "area_id_and_issue_id_set_according_to_scope";
206 DROP INDEX "delegation_global_truster_id_unique_idx";
208 ALTER TABLE "delegation" ALTER "scope" TYPE "delegation_scope"
209 USING CASE WHEN "scope" = 'global'
210 THEN 'unit'::"delegation_scope"
211 ELSE "scope"::text::"delegation_scope" END;
213 ALTER TABLE "delegation" ADD "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
215 ALTER TABLE "delegation" ADD CONSTRAINT "no_unit_delegation_to_null"
216 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit');
218 ALTER TABLE "delegation" ADD UNIQUE ("unit_id", "truster_id");
220 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
222 -- NOTE: Column "unit_id" filled after transaction (see below)
225 -- Modify snapshot tables to use new "delegation_scope" type:
227 ALTER TABLE "delegating_population_snapshot" ALTER "scope" TYPE "delegation_scope"
228 USING CASE WHEN "scope" = 'global'
229 THEN 'unit'::"delegation_scope"
230 ELSE "scope"::text::"delegation_scope" END;
232 ALTER TABLE "delegating_interest_snapshot" ALTER "scope" TYPE "delegation_scope"
233 USING CASE WHEN "scope" = 'global'
234 THEN 'unit'::"delegation_scope"
235 ELSE "scope"::text::"delegation_scope" END;
237 ALTER TABLE "delegating_voter" ALTER "scope" TYPE "delegation_scope"
238 USING CASE WHEN "scope" = 'global'
239 THEN 'unit'::"delegation_scope"
240 ELSE "scope"::text::"delegation_scope" END;
243 -- New table "non_voter":
245 CREATE TABLE "non_voter" (
246 PRIMARY KEY ("issue_id", "member_id"),
247 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
248 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
249 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
251 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
254 -- New tables "issue_comment" and "rendered_issue_comment":
256 CREATE TABLE "issue_comment" (
257 PRIMARY KEY ("issue_id", "member_id"),
258 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
259 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
260 "changed" TIMESTAMPTZ NOT NULL DEFAULT now(),
261 "formatting_engine" TEXT,
262 "content" TEXT NOT NULL,
263 "text_search_data" TSVECTOR );
264 CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id");
265 CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data");
266 CREATE TRIGGER "update_text_search_data"
267 BEFORE INSERT OR UPDATE ON "issue_comment"
268 FOR EACH ROW EXECUTE PROCEDURE
269 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
271 COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues';
273 COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed';
275 CREATE TABLE "rendered_issue_comment" (
276 PRIMARY KEY ("issue_id", "member_id", "format"),
277 FOREIGN KEY ("issue_id", "member_id")
278 REFERENCES "issue_comment" ("issue_id", "member_id")
279 ON DELETE CASCADE ON UPDATE CASCADE,
280 "issue_id" INT4,
281 "member_id" INT4,
282 "format" TEXT,
283 "content" TEXT NOT NULL );
285 COMMENT ON TABLE "rendered_issue_comment" IS 'This table may be used by frontends to cache "rendered" issue comments (e.g. HTML output generated from wiki text)';
288 -- New tables "voting_comment" and "rendered_voting_comment":
290 CREATE TABLE "voting_comment" (
291 PRIMARY KEY ("issue_id", "member_id"),
292 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
293 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
294 "changed" TIMESTAMPTZ,
295 "formatting_engine" TEXT,
296 "content" TEXT NOT NULL,
297 "text_search_data" TSVECTOR );
298 CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");
299 CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");
300 CREATE TRIGGER "update_text_search_data"
301 BEFORE INSERT OR UPDATE ON "voting_comment"
302 FOR EACH ROW EXECUTE PROCEDURE
303 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
305 COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';
307 COMMENT ON COLUMN "voting_comment"."changed" IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.';
309 CREATE TABLE "rendered_voting_comment" (
310 PRIMARY KEY ("issue_id", "member_id", "format"),
311 FOREIGN KEY ("issue_id", "member_id")
312 REFERENCES "voting_comment" ("issue_id", "member_id")
313 ON DELETE CASCADE ON UPDATE CASCADE,
314 "issue_id" INT4,
315 "member_id" INT4,
316 "format" TEXT,
317 "content" TEXT NOT NULL );
319 COMMENT ON TABLE "rendered_voting_comment" IS 'This table may be used by frontends to cache "rendered" voting comments (e.g. HTML output generated from wiki text)';
322 -- New table "event":
324 CREATE TYPE "event_type" AS ENUM (
325 'issue_state_changed',
326 'initiative_created_in_new_issue',
327 'initiative_created_in_existing_issue',
328 'initiative_revoked',
329 'new_draft_created',
330 'suggestion_created');
332 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
334 CREATE TABLE "event" (
335 "id" SERIAL8 PRIMARY KEY,
336 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
337 "event" "event_type" NOT NULL,
338 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
339 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
340 "state" "issue_state" CHECK ("state" != 'calculation'),
341 "initiative_id" INT4,
342 "draft_id" INT8,
343 "suggestion_id" INT8,
344 FOREIGN KEY ("issue_id", "initiative_id")
345 REFERENCES "initiative" ("issue_id", "id")
346 ON DELETE CASCADE ON UPDATE CASCADE,
347 FOREIGN KEY ("initiative_id", "draft_id")
348 REFERENCES "draft" ("initiative_id", "id")
349 ON DELETE CASCADE ON UPDATE CASCADE,
350 FOREIGN KEY ("initiative_id", "suggestion_id")
351 REFERENCES "suggestion" ("initiative_id", "id")
352 ON DELETE CASCADE ON UPDATE CASCADE,
353 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
354 "event" != 'issue_state_changed' OR (
355 "member_id" ISNULL AND
356 "issue_id" NOTNULL AND
357 "state" NOTNULL AND
358 "initiative_id" ISNULL AND
359 "draft_id" ISNULL AND
360 "suggestion_id" ISNULL )),
361 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
362 "event" NOT IN (
363 'initiative_created_in_new_issue',
364 'initiative_created_in_existing_issue',
365 'initiative_revoked',
366 'new_draft_created'
367 ) OR (
368 "member_id" NOTNULL AND
369 "issue_id" NOTNULL AND
370 "state" NOTNULL AND
371 "initiative_id" NOTNULL AND
372 "draft_id" NOTNULL AND
373 "suggestion_id" ISNULL )),
374 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
375 "event" != 'suggestion_created' OR (
376 "member_id" NOTNULL AND
377 "issue_id" NOTNULL AND
378 "state" NOTNULL AND
379 "initiative_id" NOTNULL AND
380 "draft_id" ISNULL AND
381 "suggestion_id" NOTNULL )) );
383 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
385 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
386 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
387 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
388 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
391 -- Triggers to fill "event" table:
393 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
394 RETURNS TRIGGER
395 LANGUAGE 'plpgsql' VOLATILE AS $$
396 BEGIN
397 IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
398 INSERT INTO "event" ("event", "issue_id", "state")
399 VALUES ('issue_state_changed', NEW."id", NEW."state");
400 END IF;
401 RETURN NULL;
402 END;
403 $$;
405 CREATE TRIGGER "write_event_issue_state_changed"
406 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
407 "write_event_issue_state_changed_trigger"();
409 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
410 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
412 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
413 RETURNS TRIGGER
414 LANGUAGE 'plpgsql' VOLATILE AS $$
415 DECLARE
416 "initiative_row" "initiative"%ROWTYPE;
417 "issue_row" "issue"%ROWTYPE;
418 "event_v" "event_type";
419 BEGIN
420 SELECT * INTO "initiative_row" FROM "initiative"
421 WHERE "id" = NEW."initiative_id";
422 SELECT * INTO "issue_row" FROM "issue"
423 WHERE "id" = "initiative_row"."issue_id";
424 IF EXISTS (
425 SELECT NULL FROM "draft"
426 WHERE "initiative_id" = NEW."initiative_id"
427 AND "id" != NEW."id"
428 ) THEN
429 "event_v" := 'new_draft_created';
430 ELSE
431 IF EXISTS (
432 SELECT NULL FROM "initiative"
433 WHERE "issue_id" = "initiative_row"."issue_id"
434 AND "id" != "initiative_row"."id"
435 ) THEN
436 "event_v" := 'initiative_created_in_existing_issue';
437 ELSE
438 "event_v" := 'initiative_created_in_new_issue';
439 END IF;
440 END IF;
441 INSERT INTO "event" (
442 "event", "member_id",
443 "issue_id", "state", "initiative_id", "draft_id"
444 ) VALUES (
445 "event_v",
446 NEW."author_id",
447 "initiative_row"."issue_id",
448 "issue_row"."state",
449 "initiative_row"."id",
450 NEW."id" );
451 RETURN NULL;
452 END;
453 $$;
455 CREATE TRIGGER "write_event_initiative_or_draft_created"
456 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
457 "write_event_initiative_or_draft_created_trigger"();
459 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
460 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
462 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
463 RETURNS TRIGGER
464 LANGUAGE 'plpgsql' VOLATILE AS $$
465 DECLARE
466 "issue_row" "issue"%ROWTYPE;
467 BEGIN
468 SELECT * INTO "issue_row" FROM "issue"
469 WHERE "id" = NEW."issue_id";
470 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
471 INSERT INTO "event" (
472 "event", "member_id", "issue_id", "state", "initiative_id"
473 ) VALUES (
474 'initiative_revoked',
475 NEW."revoked_by_member_id",
476 NEW."issue_id",
477 "issue_row"."state",
478 NEW."id" );
479 END IF;
480 RETURN NULL;
481 END;
482 $$;
484 CREATE TRIGGER "write_event_initiative_revoked"
485 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
486 "write_event_initiative_revoked_trigger"();
488 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
489 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
491 CREATE FUNCTION "write_event_suggestion_created_trigger"()
492 RETURNS TRIGGER
493 LANGUAGE 'plpgsql' VOLATILE AS $$
494 DECLARE
495 "initiative_row" "initiative"%ROWTYPE;
496 "issue_row" "issue"%ROWTYPE;
497 BEGIN
498 SELECT * INTO "initiative_row" FROM "initiative"
499 WHERE "id" = NEW."initiative_id";
500 SELECT * INTO "issue_row" FROM "issue"
501 WHERE "id" = "initiative_row"."issue_id";
502 INSERT INTO "event" (
503 "event", "member_id",
504 "issue_id", "state", "initiative_id", "suggestion_id"
505 ) VALUES (
506 'suggestion_created',
507 NEW."author_id",
508 "initiative_row"."issue_id",
509 "issue_row"."state",
510 "initiative_row"."id",
511 NEW."id" );
512 RETURN NULL;
513 END;
514 $$;
516 CREATE TRIGGER "write_event_suggestion_created"
517 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
518 "write_event_suggestion_created_trigger"();
520 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
521 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
524 -- Modified views:
526 CREATE VIEW "unit_delegation" AS
527 SELECT
528 "unit"."id" AS "unit_id",
529 "delegation"."id",
530 "delegation"."truster_id",
531 "delegation"."trustee_id",
532 "delegation"."scope"
533 FROM "unit"
534 JOIN "delegation"
535 ON "delegation"."unit_id" = "unit"."id"
536 JOIN "member"
537 ON "delegation"."truster_id" = "member"."id"
538 JOIN "privilege"
539 ON "delegation"."unit_id" = "privilege"."unit_id"
540 AND "delegation"."truster_id" = "privilege"."member_id"
541 WHERE "member"."active" AND "privilege"."voting_right";
543 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
545 CREATE VIEW "area_delegation" AS
546 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
547 "area"."id" AS "area_id",
548 "delegation"."id",
549 "delegation"."truster_id",
550 "delegation"."trustee_id",
551 "delegation"."scope"
552 FROM "area"
553 JOIN "delegation"
554 ON "delegation"."unit_id" = "area"."unit_id"
555 OR "delegation"."area_id" = "area"."id"
556 JOIN "member"
557 ON "delegation"."truster_id" = "member"."id"
558 JOIN "privilege"
559 ON "area"."unit_id" = "privilege"."unit_id"
560 AND "delegation"."truster_id" = "privilege"."member_id"
561 WHERE "member"."active" AND "privilege"."voting_right"
562 ORDER BY
563 "area"."id",
564 "delegation"."truster_id",
565 "delegation"."scope" DESC;
567 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
569 CREATE VIEW "issue_delegation" AS
570 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
571 "issue"."id" AS "issue_id",
572 "delegation"."id",
573 "delegation"."truster_id",
574 "delegation"."trustee_id",
575 "delegation"."scope"
576 FROM "issue"
577 JOIN "area"
578 ON "area"."id" = "issue"."area_id"
579 JOIN "delegation"
580 ON "delegation"."unit_id" = "area"."unit_id"
581 OR "delegation"."area_id" = "area"."id"
582 OR "delegation"."issue_id" = "issue"."id"
583 JOIN "member"
584 ON "delegation"."truster_id" = "member"."id"
585 JOIN "privilege"
586 ON "area"."unit_id" = "privilege"."unit_id"
587 AND "delegation"."truster_id" = "privilege"."member_id"
588 WHERE "member"."active" AND "privilege"."voting_right"
589 ORDER BY
590 "issue"."id",
591 "delegation"."truster_id",
592 "delegation"."scope" DESC;
594 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
596 CREATE VIEW "unit_member_count" AS
597 SELECT
598 "unit"."id" AS "unit_id",
599 sum("member"."id") AS "member_count"
600 FROM "unit"
601 LEFT JOIN "privilege"
602 ON "privilege"."unit_id" = "unit"."id"
603 AND "privilege"."voting_right"
604 LEFT JOIN "member"
605 ON "member"."id" = "privilege"."member_id"
606 AND "member"."active"
607 GROUP BY "unit"."id";
609 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
611 DROP VIEW "area_member_count";
612 CREATE VIEW "area_member_count" AS
613 SELECT
614 "area"."id" AS "area_id",
615 count("member"."id") AS "direct_member_count",
616 coalesce(
617 sum(
618 CASE WHEN "member"."id" NOTNULL THEN
619 "membership_weight"("area"."id", "member"."id")
620 ELSE 0 END
621 )
622 ) AS "member_weight",
623 coalesce(
624 sum(
625 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
626 "membership_weight"("area"."id", "member"."id")
627 ELSE 0 END
628 )
629 ) AS "autoreject_weight"
630 FROM "area"
631 LEFT JOIN "membership"
632 ON "area"."id" = "membership"."area_id"
633 LEFT JOIN "privilege"
634 ON "privilege"."unit_id" = "area"."unit_id"
635 AND "privilege"."member_id" = "membership"."member_id"
636 AND "privilege"."voting_right"
637 LEFT JOIN "member"
638 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
639 AND "member"."active"
640 GROUP BY "area"."id";
642 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count", "member_weight" and "autoreject_weight" columns of table "area"';
645 -- New view "event_seen_by_member":
647 CREATE VIEW "event_seen_by_member" AS
648 SELECT
649 "member"."id" AS "seen_by_member_id",
650 CASE WHEN "event"."state" IN (
651 'voting',
652 'finished_without_winner',
653 'finished_with_winner'
654 ) THEN
655 'voting'::"notify_level"
656 ELSE
657 CASE WHEN "event"."state" IN (
658 'verification',
659 'canceled_after_revocation_during_verification',
660 'canceled_no_initiative_admitted'
661 ) THEN
662 'verification'::"notify_level"
663 ELSE
664 CASE WHEN "event"."state" IN (
665 'discussion',
666 'canceled_after_revocation_during_discussion'
667 ) THEN
668 'discussion'::"notify_level"
669 ELSE
670 'all'::"notify_level"
671 END
672 END
673 END AS "notify_level",
674 "event".*
675 FROM "member" CROSS JOIN "event"
676 LEFT JOIN "issue"
677 ON "event"."issue_id" = "issue"."id"
678 LEFT JOIN "membership"
679 ON "member"."id" = "membership"."member_id"
680 AND "issue"."area_id" = "membership"."area_id"
681 LEFT JOIN "interest"
682 ON "member"."id" = "interest"."member_id"
683 AND "event"."issue_id" = "interest"."issue_id"
684 LEFT JOIN "supporter"
685 ON "member"."id" = "supporter"."member_id"
686 AND "event"."initiative_id" = "supporter"."initiative_id"
687 LEFT JOIN "ignored_member"
688 ON "member"."id" = "ignored_member"."member_id"
689 AND "event"."member_id" = "ignored_member"."other_member_id"
690 LEFT JOIN "ignored_initiative"
691 ON "member"."id" = "ignored_initiative"."member_id"
692 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
693 WHERE (
694 "supporter"."member_id" NOTNULL OR
695 "interest"."member_id" NOTNULL OR
696 ( "membership"."member_id" NOTNULL AND
697 "event"."event" IN (
698 'issue_state_changed',
699 'initiative_created_in_new_issue',
700 'initiative_created_in_existing_issue',
701 'initiative_revoked' ) ) )
702 AND "ignored_member"."member_id" ISNULL
703 AND "ignored_initiative"."member_id" ISNULL;
705 COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support';
708 -- New view "pending_notification":
710 CREATE VIEW "pending_notification" AS
711 SELECT
712 "member"."id" AS "seen_by_member_id",
713 "event".*
714 FROM "member" CROSS JOIN "event"
715 LEFT JOIN "issue"
716 ON "event"."issue_id" = "issue"."id"
717 LEFT JOIN "membership"
718 ON "member"."id" = "membership"."member_id"
719 AND "issue"."area_id" = "membership"."area_id"
720 LEFT JOIN "interest"
721 ON "member"."id" = "interest"."member_id"
722 AND "event"."issue_id" = "interest"."issue_id"
723 LEFT JOIN "supporter"
724 ON "member"."id" = "supporter"."member_id"
725 AND "event"."initiative_id" = "supporter"."initiative_id"
726 LEFT JOIN "ignored_member"
727 ON "member"."id" = "ignored_member"."member_id"
728 AND "event"."member_id" = "ignored_member"."other_member_id"
729 LEFT JOIN "ignored_initiative"
730 ON "member"."id" = "ignored_initiative"."member_id"
731 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
732 WHERE (
733 "member"."notify_event_id" ISNULL OR
734 ( "member"."notify_event_id" NOTNULL AND
735 "member"."notify_event_id" < "event"."id" ) )
736 AND (
737 ( "member"."notify_level" >= 'all' ) OR
738 ( "member"."notify_level" >= 'voting' AND
739 "event"."state" IN (
740 'voting',
741 'finished_without_winner',
742 'finished_with_winner' ) ) OR
743 ( "member"."notify_level" >= 'verification' AND
744 "event"."state" IN (
745 'verification',
746 'canceled_after_revocation_during_verification',
747 'canceled_no_initiative_admitted' ) ) OR
748 ( "member"."notify_level" >= 'discussion' AND
749 "event"."state" IN (
750 'discussion',
751 'canceled_after_revocation_during_discussion' ) ) )
752 AND (
753 "supporter"."member_id" NOTNULL OR
754 "interest"."member_id" NOTNULL OR
755 ( "membership"."member_id" NOTNULL AND
756 "event"."event" IN (
757 'issue_state_changed',
758 'initiative_created_in_new_issue',
759 'initiative_created_in_existing_issue',
760 'initiative_revoked' ) ) )
761 AND "ignored_member"."member_id" ISNULL
762 AND "ignored_initiative"."member_id" ISNULL;
764 COMMENT ON VIEW "pending_notification" IS 'Events to be sent to "notify_email" address of member referred to by "seen_by_member_id"';
767 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
768 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
769 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
770 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
771 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
772 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
775 -- Modified "delegation_chain" functions:
777 CREATE TYPE "delegation_chain_row" AS (
778 "index" INT4,
779 "member_id" INT4,
780 "member_valid" BOOLEAN,
781 "participation" BOOLEAN,
782 "overridden" BOOLEAN,
783 "scope_in" "delegation_scope",
784 "scope_out" "delegation_scope",
785 "disabled_out" BOOLEAN,
786 "loop" "delegation_chain_loop_tag" );
788 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
790 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
791 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest, for areas: membership, for global delegation chains: always null';
792 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
793 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
794 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
795 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
796 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
799 CREATE FUNCTION "delegation_chain"
800 ( "member_id_p" "member"."id"%TYPE,
801 "unit_id_p" "unit"."id"%TYPE,
802 "area_id_p" "area"."id"%TYPE,
803 "issue_id_p" "issue"."id"%TYPE,
804 "simulate_trustee_id_p" "member"."id"%TYPE )
805 RETURNS SETOF "delegation_chain_row"
806 LANGUAGE 'plpgsql' STABLE AS $$
807 DECLARE
808 "scope_v" "delegation_scope";
809 "unit_id_v" "unit"."id"%TYPE;
810 "area_id_v" "area"."id"%TYPE;
811 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
812 "loop_member_id_v" "member"."id"%TYPE;
813 "output_row" "delegation_chain_row";
814 "output_rows" "delegation_chain_row"[];
815 "delegation_row" "delegation"%ROWTYPE;
816 "row_count" INT4;
817 "i" INT4;
818 "loop_v" BOOLEAN;
819 BEGIN
820 IF
821 "unit_id_p" NOTNULL AND
822 "area_id_p" ISNULL AND
823 "issue_id_p" ISNULL
824 THEN
825 "scope_v" := 'unit';
826 "unit_id_v" := "unit_id_p";
827 ELSIF
828 "unit_id_p" ISNULL AND
829 "area_id_p" NOTNULL AND
830 "issue_id_p" ISNULL
831 THEN
832 "scope_v" := 'area';
833 "area_id_v" := "area_id_p";
834 SELECT "unit_id" INTO "unit_id_v"
835 FROM "area" WHERE "id" = "area_id_v";
836 ELSIF
837 "unit_id_p" ISNULL AND
838 "area_id_p" ISNULL AND
839 "issue_id_p" NOTNULL
840 THEN
841 "scope_v" := 'issue';
842 SELECT "area_id" INTO "area_id_v"
843 FROM "issue" WHERE "id" = "issue_id_p";
844 SELECT "unit_id" INTO "unit_id_v"
845 FROM "area" WHERE "id" = "area_id_v";
846 ELSE
847 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
848 END IF;
849 "visited_member_ids" := '{}';
850 "loop_member_id_v" := NULL;
851 "output_rows" := '{}';
852 "output_row"."index" := 0;
853 "output_row"."member_id" := "member_id_p";
854 "output_row"."member_valid" := TRUE;
855 "output_row"."participation" := FALSE;
856 "output_row"."overridden" := FALSE;
857 "output_row"."disabled_out" := FALSE;
858 "output_row"."scope_out" := NULL;
859 LOOP
860 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
861 "loop_member_id_v" := "output_row"."member_id";
862 ELSE
863 "visited_member_ids" :=
864 "visited_member_ids" || "output_row"."member_id";
865 END IF;
866 IF "output_row"."participation" THEN
867 "output_row"."overridden" := TRUE;
868 END IF;
869 "output_row"."scope_in" := "output_row"."scope_out";
870 IF EXISTS (
871 SELECT NULL FROM "member" JOIN "privilege"
872 ON "privilege"."member_id" = "member"."id"
873 AND "privilege"."unit_id" = "unit_id_v"
874 WHERE "id" = "output_row"."member_id"
875 AND "member"."active" AND "privilege"."voting_right"
876 ) THEN
877 IF "scope_v" = 'unit' THEN
878 SELECT * INTO "delegation_row" FROM "delegation"
879 WHERE "truster_id" = "output_row"."member_id"
880 AND "unit_id" = "unit_id_v";
881 ELSIF "scope_v" = 'area' THEN
882 "output_row"."participation" := EXISTS (
883 SELECT NULL FROM "membership"
884 WHERE "area_id" = "area_id_p"
885 AND "member_id" = "output_row"."member_id"
886 );
887 SELECT * INTO "delegation_row" FROM "delegation"
888 WHERE "truster_id" = "output_row"."member_id"
889 AND (
890 "unit_id" = "unit_id_v" OR
891 "area_id" = "area_id_v"
892 )
893 ORDER BY "scope" DESC;
894 ELSIF "scope_v" = 'issue' THEN
895 "output_row"."participation" := EXISTS (
896 SELECT NULL FROM "interest"
897 WHERE "issue_id" = "issue_id_p"
898 AND "member_id" = "output_row"."member_id"
899 );
900 SELECT * INTO "delegation_row" FROM "delegation"
901 WHERE "truster_id" = "output_row"."member_id"
902 AND (
903 "unit_id" = "unit_id_v" OR
904 "area_id" = "area_id_v" OR
905 "issue_id" = "issue_id_p"
906 )
907 ORDER BY "scope" DESC;
908 END IF;
909 ELSE
910 "output_row"."member_valid" := FALSE;
911 "output_row"."participation" := FALSE;
912 "output_row"."scope_out" := NULL;
913 "delegation_row" := ROW(NULL);
914 END IF;
915 IF
916 "output_row"."member_id" = "member_id_p" AND
917 "simulate_trustee_id_p" NOTNULL
918 THEN
919 "output_row"."scope_out" := "scope_v";
920 "output_rows" := "output_rows" || "output_row";
921 "output_row"."member_id" := "simulate_trustee_id_p";
922 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
923 "output_row"."scope_out" := "delegation_row"."scope";
924 "output_rows" := "output_rows" || "output_row";
925 "output_row"."member_id" := "delegation_row"."trustee_id";
926 ELSIF "delegation_row"."scope" NOTNULL THEN
927 "output_row"."scope_out" := "delegation_row"."scope";
928 "output_row"."disabled_out" := TRUE;
929 "output_rows" := "output_rows" || "output_row";
930 EXIT;
931 ELSE
932 "output_row"."scope_out" := NULL;
933 "output_rows" := "output_rows" || "output_row";
934 EXIT;
935 END IF;
936 EXIT WHEN "loop_member_id_v" NOTNULL;
937 "output_row"."index" := "output_row"."index" + 1;
938 END LOOP;
939 "row_count" := array_upper("output_rows", 1);
940 "i" := 1;
941 "loop_v" := FALSE;
942 LOOP
943 "output_row" := "output_rows"["i"];
944 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
945 IF "loop_v" THEN
946 IF "i" + 1 = "row_count" THEN
947 "output_row"."loop" := 'last';
948 ELSIF "i" = "row_count" THEN
949 "output_row"."loop" := 'repetition';
950 ELSE
951 "output_row"."loop" := 'intermediate';
952 END IF;
953 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
954 "output_row"."loop" := 'first';
955 "loop_v" := TRUE;
956 END IF;
957 IF "scope_v" = 'unit' THEN
958 "output_row"."participation" := NULL;
959 END IF;
960 RETURN NEXT "output_row";
961 "i" := "i" + 1;
962 END LOOP;
963 RETURN;
964 END;
965 $$;
967 COMMENT ON FUNCTION "delegation_chain"
968 ( "member"."id"%TYPE,
969 "unit"."id"%TYPE,
970 "area"."id"%TYPE,
971 "issue"."id"%TYPE,
972 "member"."id"%TYPE )
973 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
976 CREATE FUNCTION "delegation_chain"
977 ( "member_id_p" "member"."id"%TYPE,
978 "unit_id_p" "unit"."id"%TYPE,
979 "area_id_p" "area"."id"%TYPE,
980 "issue_id_p" "issue"."id"%TYPE )
981 RETURNS SETOF "delegation_chain_row"
982 LANGUAGE 'plpgsql' STABLE AS $$
983 DECLARE
984 "result_row" "delegation_chain_row";
985 BEGIN
986 FOR "result_row" IN
987 SELECT * FROM "delegation_chain"(
988 "member_id_p", "unit_id_p", "area_id_p", "issue_id_p", NULL
989 )
990 LOOP
991 RETURN NEXT "result_row";
992 END LOOP;
993 RETURN;
994 END;
995 $$;
997 COMMENT ON FUNCTION "delegation_chain"
998 ( "member"."id"%TYPE,
999 "unit"."id"%TYPE,
1000 "area"."id"%TYPE,
1001 "issue"."id"%TYPE )
1002 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
1005 -- Other modified functions:
1007 CREATE OR REPLACE FUNCTION "lock_issue"
1008 ( "issue_id_p" "issue"."id"%TYPE )
1009 RETURNS VOID
1010 LANGUAGE 'plpgsql' VOLATILE AS $$
1011 BEGIN
1012 LOCK TABLE "member" IN SHARE MODE;
1013 LOCK TABLE "privilege" IN SHARE MODE;
1014 LOCK TABLE "membership" IN SHARE MODE;
1015 LOCK TABLE "policy" IN SHARE MODE;
1016 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
1017 -- NOTE: The row-level exclusive lock in combination with the
1018 -- share_row_lock_issue(_via_initiative)_trigger functions (which
1019 -- acquire a row-level share lock on the issue) ensure that no data
1020 -- is changed, which could affect calculation of snapshots or
1021 -- counting of votes. Table "delegation" must be table-level-locked,
1022 -- as it also contains issue- and global-scope delegations.
1023 LOCK TABLE "delegation" IN SHARE MODE;
1024 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
1025 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
1026 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
1027 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
1028 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
1029 RETURN;
1030 END;
1031 $$;
1033 CREATE OR REPLACE FUNCTION "calculate_member_counts"()
1034 RETURNS VOID
1035 LANGUAGE 'plpgsql' VOLATILE AS $$
1036 BEGIN
1037 LOCK TABLE "member" IN SHARE MODE;
1038 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
1039 LOCK TABLE "unit" IN EXCLUSIVE MODE;
1040 LOCK TABLE "area" IN EXCLUSIVE MODE;
1041 LOCK TABLE "privilege" IN SHARE MODE;
1042 LOCK TABLE "membership" IN SHARE MODE;
1043 DELETE FROM "member_count";
1044 INSERT INTO "member_count" ("total_count")
1045 SELECT "total_count" FROM "member_count_view";
1046 UPDATE "unit" SET "member_count" = "view"."member_count"
1047 FROM "unit_member_count" AS "view"
1048 WHERE "view"."unit_id" = "unit"."id";
1049 UPDATE "area" SET
1050 "direct_member_count" = "view"."direct_member_count",
1051 "member_weight" = "view"."member_weight",
1052 "autoreject_weight" = "view"."autoreject_weight"
1053 FROM "area_member_count" AS "view"
1054 WHERE "view"."area_id" = "area"."id";
1055 RETURN;
1056 END;
1057 $$;
1059 CREATE OR REPLACE FUNCTION "create_population_snapshot"
1060 ( "issue_id_p" "issue"."id"%TYPE )
1061 RETURNS VOID
1062 LANGUAGE 'plpgsql' VOLATILE AS $$
1063 DECLARE
1064 "member_id_v" "member"."id"%TYPE;
1065 BEGIN
1066 DELETE FROM "direct_population_snapshot"
1067 WHERE "issue_id" = "issue_id_p"
1068 AND "event" = 'periodic';
1069 DELETE FROM "delegating_population_snapshot"
1070 WHERE "issue_id" = "issue_id_p"
1071 AND "event" = 'periodic';
1072 INSERT INTO "direct_population_snapshot"
1073 ("issue_id", "event", "member_id")
1074 SELECT
1075 "issue_id_p" AS "issue_id",
1076 'periodic'::"snapshot_event" AS "event",
1077 "member"."id" AS "member_id"
1078 FROM "issue"
1079 JOIN "area" ON "issue"."area_id" = "area"."id"
1080 JOIN "membership" ON "area"."id" = "membership"."area_id"
1081 JOIN "member" ON "membership"."member_id" = "member"."id"
1082 JOIN "privilege"
1083 ON "privilege"."unit_id" = "area"."unit_id"
1084 AND "privilege"."member_id" = "member"."id"
1085 WHERE "issue"."id" = "issue_id_p"
1086 AND "member"."active" AND "privilege"."voting_right"
1087 UNION
1088 SELECT
1089 "issue_id_p" AS "issue_id",
1090 'periodic'::"snapshot_event" AS "event",
1091 "member"."id" AS "member_id"
1092 FROM "issue"
1093 JOIN "area" ON "issue"."area_id" = "area"."id"
1094 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
1095 JOIN "member" ON "interest"."member_id" = "member"."id"
1096 JOIN "privilege"
1097 ON "privilege"."unit_id" = "area"."unit_id"
1098 AND "privilege"."member_id" = "member"."id"
1099 WHERE "issue"."id" = "issue_id_p"
1100 AND "member"."active" AND "privilege"."voting_right";
1101 FOR "member_id_v" IN
1102 SELECT "member_id" FROM "direct_population_snapshot"
1103 WHERE "issue_id" = "issue_id_p"
1104 AND "event" = 'periodic'
1105 LOOP
1106 UPDATE "direct_population_snapshot" SET
1107 "weight" = 1 +
1108 "weight_of_added_delegations_for_population_snapshot"(
1109 "issue_id_p",
1110 "member_id_v",
1111 '{}'
1113 WHERE "issue_id" = "issue_id_p"
1114 AND "event" = 'periodic'
1115 AND "member_id" = "member_id_v";
1116 END LOOP;
1117 RETURN;
1118 END;
1119 $$;
1121 CREATE OR REPLACE FUNCTION "create_interest_snapshot"
1122 ( "issue_id_p" "issue"."id"%TYPE )
1123 RETURNS VOID
1124 LANGUAGE 'plpgsql' VOLATILE AS $$
1125 DECLARE
1126 "member_id_v" "member"."id"%TYPE;
1127 BEGIN
1128 DELETE FROM "direct_interest_snapshot"
1129 WHERE "issue_id" = "issue_id_p"
1130 AND "event" = 'periodic';
1131 DELETE FROM "delegating_interest_snapshot"
1132 WHERE "issue_id" = "issue_id_p"
1133 AND "event" = 'periodic';
1134 DELETE FROM "direct_supporter_snapshot"
1135 WHERE "issue_id" = "issue_id_p"
1136 AND "event" = 'periodic';
1137 INSERT INTO "direct_interest_snapshot"
1138 ("issue_id", "event", "member_id", "voting_requested")
1139 SELECT
1140 "issue_id_p" AS "issue_id",
1141 'periodic' AS "event",
1142 "member"."id" AS "member_id",
1143 "interest"."voting_requested"
1144 FROM "issue"
1145 JOIN "area" ON "issue"."area_id" = "area"."id"
1146 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
1147 JOIN "member" ON "interest"."member_id" = "member"."id"
1148 JOIN "privilege"
1149 ON "privilege"."unit_id" = "area"."unit_id"
1150 AND "privilege"."member_id" = "member"."id"
1151 WHERE "issue"."id" = "issue_id_p"
1152 AND "member"."active" AND "privilege"."voting_right";
1153 FOR "member_id_v" IN
1154 SELECT "member_id" FROM "direct_interest_snapshot"
1155 WHERE "issue_id" = "issue_id_p"
1156 AND "event" = 'periodic'
1157 LOOP
1158 UPDATE "direct_interest_snapshot" SET
1159 "weight" = 1 +
1160 "weight_of_added_delegations_for_interest_snapshot"(
1161 "issue_id_p",
1162 "member_id_v",
1163 '{}'
1165 WHERE "issue_id" = "issue_id_p"
1166 AND "event" = 'periodic'
1167 AND "member_id" = "member_id_v";
1168 END LOOP;
1169 INSERT INTO "direct_supporter_snapshot"
1170 ( "issue_id", "initiative_id", "event", "member_id",
1171 "informed", "satisfied" )
1172 SELECT
1173 "issue_id_p" AS "issue_id",
1174 "initiative"."id" AS "initiative_id",
1175 'periodic' AS "event",
1176 "supporter"."member_id" AS "member_id",
1177 "supporter"."draft_id" = "current_draft"."id" AS "informed",
1178 NOT EXISTS (
1179 SELECT NULL FROM "critical_opinion"
1180 WHERE "initiative_id" = "initiative"."id"
1181 AND "member_id" = "supporter"."member_id"
1182 ) AS "satisfied"
1183 FROM "initiative"
1184 JOIN "supporter"
1185 ON "supporter"."initiative_id" = "initiative"."id"
1186 JOIN "current_draft"
1187 ON "initiative"."id" = "current_draft"."initiative_id"
1188 JOIN "direct_interest_snapshot"
1189 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
1190 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
1191 AND "event" = 'periodic'
1192 WHERE "initiative"."issue_id" = "issue_id_p";
1193 RETURN;
1194 END;
1195 $$;
1197 CREATE OR REPLACE FUNCTION "freeze_after_snapshot"
1198 ( "issue_id_p" "issue"."id"%TYPE )
1199 RETURNS VOID
1200 LANGUAGE 'plpgsql' VOLATILE AS $$
1201 DECLARE
1202 "issue_row" "issue"%ROWTYPE;
1203 "policy_row" "policy"%ROWTYPE;
1204 "initiative_row" "initiative"%ROWTYPE;
1205 BEGIN
1206 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1207 SELECT * INTO "policy_row"
1208 FROM "policy" WHERE "id" = "issue_row"."policy_id";
1209 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
1210 FOR "initiative_row" IN
1211 SELECT * FROM "initiative"
1212 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
1213 LOOP
1214 IF
1215 "initiative_row"."satisfied_supporter_count" > 0 AND
1216 "initiative_row"."satisfied_supporter_count" *
1217 "policy_row"."initiative_quorum_den" >=
1218 "issue_row"."population" * "policy_row"."initiative_quorum_num"
1219 THEN
1220 UPDATE "initiative" SET "admitted" = TRUE
1221 WHERE "id" = "initiative_row"."id";
1222 ELSE
1223 UPDATE "initiative" SET "admitted" = FALSE
1224 WHERE "id" = "initiative_row"."id";
1225 END IF;
1226 END LOOP;
1227 IF EXISTS (
1228 SELECT NULL FROM "initiative"
1229 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
1230 ) THEN
1231 UPDATE "issue" SET
1232 "state" = 'voting',
1233 "accepted" = coalesce("accepted", now()),
1234 "half_frozen" = coalesce("half_frozen", now()),
1235 "fully_frozen" = now()
1236 WHERE "id" = "issue_id_p";
1237 ELSE
1238 UPDATE "issue" SET
1239 "state" = 'canceled_no_initiative_admitted',
1240 "accepted" = coalesce("accepted", now()),
1241 "half_frozen" = coalesce("half_frozen", now()),
1242 "fully_frozen" = now(),
1243 "closed" = now(),
1244 "ranks_available" = TRUE
1245 WHERE "id" = "issue_id_p";
1246 -- NOTE: The following DELETE statements have effect only when
1247 -- issue state has been manipulated
1248 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
1249 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
1250 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
1251 END IF;
1252 RETURN;
1253 END;
1254 $$;
1256 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
1257 RETURNS VOID
1258 LANGUAGE 'plpgsql' VOLATILE AS $$
1259 DECLARE
1260 "area_id_v" "area"."id"%TYPE;
1261 "unit_id_v" "unit"."id"%TYPE;
1262 "member_id_v" "member"."id"%TYPE;
1263 BEGIN
1264 PERFORM "lock_issue"("issue_id_p");
1265 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
1266 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
1267 DELETE FROM "delegating_voter"
1268 WHERE "issue_id" = "issue_id_p";
1269 DELETE FROM "direct_voter"
1270 WHERE "issue_id" = "issue_id_p"
1271 AND "autoreject" = TRUE;
1272 DELETE FROM "direct_voter"
1273 USING (
1274 SELECT
1275 "direct_voter"."member_id"
1276 FROM "direct_voter"
1277 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
1278 LEFT JOIN "privilege"
1279 ON "privilege"."unit_id" = "unit_id_v"
1280 AND "privilege"."member_id" = "direct_voter"."member_id"
1281 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
1282 "member"."active" = FALSE OR
1283 "privilege"."voting_right" ISNULL OR
1284 "privilege"."voting_right" = FALSE
1286 ) AS "subquery"
1287 WHERE "direct_voter"."issue_id" = "issue_id_p"
1288 AND "direct_voter"."member_id" = "subquery"."member_id";
1289 UPDATE "direct_voter" SET "weight" = 1
1290 WHERE "issue_id" = "issue_id_p";
1291 PERFORM "add_vote_delegations"("issue_id_p");
1292 FOR "member_id_v" IN
1293 SELECT "interest"."member_id"
1294 FROM "interest"
1295 JOIN "member"
1296 ON "interest"."member_id" = "member"."id"
1297 JOIN "privilege"
1298 ON "privilege"."unit_id" = "unit_id_v"
1299 AND "privilege"."member_id" = "member"."id"
1300 LEFT JOIN "direct_voter"
1301 ON "interest"."member_id" = "direct_voter"."member_id"
1302 AND "interest"."issue_id" = "direct_voter"."issue_id"
1303 LEFT JOIN "delegating_voter"
1304 ON "interest"."member_id" = "delegating_voter"."member_id"
1305 AND "interest"."issue_id" = "delegating_voter"."issue_id"
1306 WHERE "interest"."issue_id" = "issue_id_p"
1307 AND "interest"."autoreject" = TRUE
1308 AND "member"."active"
1309 AND "privilege"."voting_right"
1310 AND "direct_voter"."member_id" ISNULL
1311 AND "delegating_voter"."member_id" ISNULL
1312 UNION SELECT "membership"."member_id"
1313 FROM "membership"
1314 JOIN "member"
1315 ON "membership"."member_id" = "member"."id"
1316 JOIN "privilege"
1317 ON "privilege"."unit_id" = "unit_id_v"
1318 AND "privilege"."member_id" = "member"."id"
1319 LEFT JOIN "interest"
1320 ON "membership"."member_id" = "interest"."member_id"
1321 AND "interest"."issue_id" = "issue_id_p"
1322 LEFT JOIN "direct_voter"
1323 ON "membership"."member_id" = "direct_voter"."member_id"
1324 AND "direct_voter"."issue_id" = "issue_id_p"
1325 LEFT JOIN "delegating_voter"
1326 ON "membership"."member_id" = "delegating_voter"."member_id"
1327 AND "delegating_voter"."issue_id" = "issue_id_p"
1328 WHERE "membership"."area_id" = "area_id_v"
1329 AND "membership"."autoreject" = TRUE
1330 AND "member"."active"
1331 AND "privilege"."voting_right"
1332 AND "interest"."autoreject" ISNULL
1333 AND "direct_voter"."member_id" ISNULL
1334 AND "delegating_voter"."member_id" ISNULL
1335 LOOP
1336 INSERT INTO "direct_voter"
1337 ("member_id", "issue_id", "weight", "autoreject") VALUES
1338 ("member_id_v", "issue_id_p", 1, TRUE);
1339 INSERT INTO "vote" (
1340 "member_id",
1341 "issue_id",
1342 "initiative_id",
1343 "grade"
1344 ) SELECT
1345 "member_id_v" AS "member_id",
1346 "issue_id_p" AS "issue_id",
1347 "id" AS "initiative_id",
1348 -1 AS "grade"
1349 FROM "initiative"
1350 WHERE "issue_id" = "issue_id_p" AND "admitted";
1351 END LOOP;
1352 PERFORM "add_vote_delegations"("issue_id_p");
1353 UPDATE "issue" SET
1354 "state" = 'calculation',
1355 "closed" = now(),
1356 "voter_count" = (
1357 SELECT coalesce(sum("weight"), 0)
1358 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
1360 WHERE "id" = "issue_id_p";
1361 UPDATE "initiative" SET
1362 "positive_votes" = "vote_counts"."positive_votes",
1363 "negative_votes" = "vote_counts"."negative_votes",
1364 "agreed" = CASE WHEN "majority_strict" THEN
1365 "vote_counts"."positive_votes" * "majority_den" >
1366 "majority_num" *
1367 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
1368 ELSE
1369 "vote_counts"."positive_votes" * "majority_den" >=
1370 "majority_num" *
1371 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
1372 END
1373 FROM
1374 ( SELECT
1375 "initiative"."id" AS "initiative_id",
1376 coalesce(
1377 sum(
1378 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
1379 ),
1381 ) AS "positive_votes",
1382 coalesce(
1383 sum(
1384 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
1385 ),
1387 ) AS "negative_votes"
1388 FROM "initiative"
1389 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
1390 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
1391 LEFT JOIN "direct_voter"
1392 ON "direct_voter"."issue_id" = "initiative"."issue_id"
1393 LEFT JOIN "vote"
1394 ON "vote"."initiative_id" = "initiative"."id"
1395 AND "vote"."member_id" = "direct_voter"."member_id"
1396 WHERE "initiative"."issue_id" = "issue_id_p"
1397 AND "initiative"."admitted" -- NOTE: NULL case is handled too
1398 GROUP BY "initiative"."id"
1399 ) AS "vote_counts",
1400 "issue",
1401 "policy"
1402 WHERE "vote_counts"."initiative_id" = "initiative"."id"
1403 AND "issue"."id" = "initiative"."issue_id"
1404 AND "policy"."id" = "issue"."policy_id";
1405 -- NOTE: "closed" column of issue must be set at this point
1406 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
1407 INSERT INTO "battle" (
1408 "issue_id",
1409 "winning_initiative_id", "losing_initiative_id",
1410 "count"
1411 ) SELECT
1412 "issue_id",
1413 "winning_initiative_id", "losing_initiative_id",
1414 "count"
1415 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
1416 END;
1417 $$;
1419 CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
1420 RETURNS VOID
1421 LANGUAGE 'plpgsql' VOLATILE AS $$
1422 DECLARE
1423 "dimension_v" INTEGER;
1424 "vote_matrix" INT4[][]; -- absolute votes
1425 "matrix" INT8[][]; -- defeat strength / best paths
1426 "i" INTEGER;
1427 "j" INTEGER;
1428 "k" INTEGER;
1429 "battle_row" "battle"%ROWTYPE;
1430 "rank_ary" INT4[];
1431 "rank_v" INT4;
1432 "done_v" INTEGER;
1433 "winners_ary" INTEGER[];
1434 "initiative_id_v" "initiative"."id"%TYPE;
1435 BEGIN
1436 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
1437 SELECT count(1) INTO "dimension_v" FROM "initiative"
1438 WHERE "issue_id" = "issue_id_p" AND "agreed";
1439 IF "dimension_v" = 1 THEN
1440 UPDATE "initiative" SET "rank" = 1
1441 WHERE "issue_id" = "issue_id_p" AND "agreed";
1442 ELSIF "dimension_v" > 1 THEN
1443 -- Create "vote_matrix" with absolute number of votes in pairwise
1444 -- comparison:
1445 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
1446 "i" := 1;
1447 "j" := 2;
1448 FOR "battle_row" IN
1449 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
1450 ORDER BY "winning_initiative_id", "losing_initiative_id"
1451 LOOP
1452 "vote_matrix"["i"]["j"] := "battle_row"."count";
1453 IF "j" = "dimension_v" THEN
1454 "i" := "i" + 1;
1455 "j" := 1;
1456 ELSE
1457 "j" := "j" + 1;
1458 IF "j" = "i" THEN
1459 "j" := "j" + 1;
1460 END IF;
1461 END IF;
1462 END LOOP;
1463 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
1464 RAISE EXCEPTION 'Wrong battle count (should not happen)';
1465 END IF;
1466 -- Store defeat strengths in "matrix" using "defeat_strength"
1467 -- function:
1468 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
1469 "i" := 1;
1470 LOOP
1471 "j" := 1;
1472 LOOP
1473 IF "i" != "j" THEN
1474 "matrix"["i"]["j"] := "defeat_strength"(
1475 "vote_matrix"["i"]["j"],
1476 "vote_matrix"["j"]["i"]
1477 );
1478 END IF;
1479 EXIT WHEN "j" = "dimension_v";
1480 "j" := "j" + 1;
1481 END LOOP;
1482 EXIT WHEN "i" = "dimension_v";
1483 "i" := "i" + 1;
1484 END LOOP;
1485 -- Find best paths:
1486 "i" := 1;
1487 LOOP
1488 "j" := 1;
1489 LOOP
1490 IF "i" != "j" THEN
1491 "k" := 1;
1492 LOOP
1493 IF "i" != "k" AND "j" != "k" THEN
1494 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
1495 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
1496 "matrix"["j"]["k"] := "matrix"["j"]["i"];
1497 END IF;
1498 ELSE
1499 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
1500 "matrix"["j"]["k"] := "matrix"["i"]["k"];
1501 END IF;
1502 END IF;
1503 END IF;
1504 EXIT WHEN "k" = "dimension_v";
1505 "k" := "k" + 1;
1506 END LOOP;
1507 END IF;
1508 EXIT WHEN "j" = "dimension_v";
1509 "j" := "j" + 1;
1510 END LOOP;
1511 EXIT WHEN "i" = "dimension_v";
1512 "i" := "i" + 1;
1513 END LOOP;
1514 -- Determine order of winners:
1515 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
1516 "rank_v" := 1;
1517 "done_v" := 0;
1518 LOOP
1519 "winners_ary" := '{}';
1520 "i" := 1;
1521 LOOP
1522 IF "rank_ary"["i"] ISNULL THEN
1523 "j" := 1;
1524 LOOP
1525 IF
1526 "i" != "j" AND
1527 "rank_ary"["j"] ISNULL AND
1528 "matrix"["j"]["i"] > "matrix"["i"]["j"]
1529 THEN
1530 -- someone else is better
1531 EXIT;
1532 END IF;
1533 IF "j" = "dimension_v" THEN
1534 -- noone is better
1535 "winners_ary" := "winners_ary" || "i";
1536 EXIT;
1537 END IF;
1538 "j" := "j" + 1;
1539 END LOOP;
1540 END IF;
1541 EXIT WHEN "i" = "dimension_v";
1542 "i" := "i" + 1;
1543 END LOOP;
1544 "i" := 1;
1545 LOOP
1546 "rank_ary"["winners_ary"["i"]] := "rank_v";
1547 "done_v" := "done_v" + 1;
1548 EXIT WHEN "i" = array_upper("winners_ary", 1);
1549 "i" := "i" + 1;
1550 END LOOP;
1551 EXIT WHEN "done_v" = "dimension_v";
1552 "rank_v" := "rank_v" + 1;
1553 END LOOP;
1554 -- write preliminary ranks:
1555 "i" := 1;
1556 FOR "initiative_id_v" IN
1557 SELECT "id" FROM "initiative"
1558 WHERE "issue_id" = "issue_id_p" AND "agreed"
1559 ORDER BY "id"
1560 LOOP
1561 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
1562 WHERE "id" = "initiative_id_v";
1563 "i" := "i" + 1;
1564 END LOOP;
1565 IF "i" != "dimension_v" + 1 THEN
1566 RAISE EXCEPTION 'Wrong winner count (should not happen)';
1567 END IF;
1568 -- straighten ranks (start counting with 1, no equal ranks):
1569 "rank_v" := 1;
1570 FOR "initiative_id_v" IN
1571 SELECT "id" FROM "initiative"
1572 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
1573 ORDER BY
1574 "rank",
1575 "vote_ratio"("positive_votes", "negative_votes") DESC,
1576 "id"
1577 LOOP
1578 UPDATE "initiative" SET "rank" = "rank_v"
1579 WHERE "id" = "initiative_id_v";
1580 "rank_v" := "rank_v" + 1;
1581 END LOOP;
1582 END IF;
1583 -- mark issue as finished
1584 UPDATE "issue" SET
1585 "state" =
1586 CASE WHEN "dimension_v" = 0 THEN
1587 'finished_without_winner'::"issue_state"
1588 ELSE
1589 'finished_with_winner'::"issue_state"
1590 END,
1591 "ranks_available" = TRUE
1592 WHERE "id" = "issue_id_p";
1593 RETURN;
1594 END;
1595 $$;
1597 CREATE OR REPLACE FUNCTION "check_issue"
1598 ( "issue_id_p" "issue"."id"%TYPE )
1599 RETURNS VOID
1600 LANGUAGE 'plpgsql' VOLATILE AS $$
1601 DECLARE
1602 "issue_row" "issue"%ROWTYPE;
1603 "policy_row" "policy"%ROWTYPE;
1604 "voting_requested_v" BOOLEAN;
1605 BEGIN
1606 PERFORM "lock_issue"("issue_id_p");
1607 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1608 -- only process open issues:
1609 IF "issue_row"."closed" ISNULL THEN
1610 SELECT * INTO "policy_row" FROM "policy"
1611 WHERE "id" = "issue_row"."policy_id";
1612 -- create a snapshot, unless issue is already fully frozen:
1613 IF "issue_row"."fully_frozen" ISNULL THEN
1614 PERFORM "create_snapshot"("issue_id_p");
1615 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1616 END IF;
1617 -- eventually close or accept issues, which have not been accepted:
1618 IF "issue_row"."accepted" ISNULL THEN
1619 IF EXISTS (
1620 SELECT NULL FROM "initiative"
1621 WHERE "issue_id" = "issue_id_p"
1622 AND "supporter_count" > 0
1623 AND "supporter_count" * "policy_row"."issue_quorum_den"
1624 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
1625 ) THEN
1626 -- accept issues, if supporter count is high enough
1627 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
1628 -- NOTE: "issue_row" used later
1629 "issue_row"."state" := 'discussion';
1630 "issue_row"."accepted" := now();
1631 UPDATE "issue" SET
1632 "state" = "issue_row"."state",
1633 "accepted" = "issue_row"."accepted"
1634 WHERE "id" = "issue_row"."id";
1635 ELSIF
1636 now() >= "issue_row"."created" + "issue_row"."admission_time"
1637 THEN
1638 -- close issues, if admission time has expired
1639 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
1640 UPDATE "issue" SET
1641 "state" = 'canceled_issue_not_accepted',
1642 "closed" = now()
1643 WHERE "id" = "issue_row"."id";
1644 END IF;
1645 END IF;
1646 -- eventually half freeze issues:
1647 IF
1648 -- NOTE: issue can't be closed at this point, if it has been accepted
1649 "issue_row"."accepted" NOTNULL AND
1650 "issue_row"."half_frozen" ISNULL
1651 THEN
1652 SELECT
1653 CASE
1654 WHEN "vote_now" * 2 > "issue_row"."population" THEN
1655 TRUE
1656 WHEN "vote_later" * 2 > "issue_row"."population" THEN
1657 FALSE
1658 ELSE NULL
1659 END
1660 INTO "voting_requested_v"
1661 FROM "issue" WHERE "id" = "issue_id_p";
1662 IF
1663 "voting_requested_v" OR (
1664 "voting_requested_v" ISNULL AND
1665 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
1667 THEN
1668 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
1669 -- NOTE: "issue_row" used later
1670 "issue_row"."state" := 'verification';
1671 "issue_row"."half_frozen" := now();
1672 UPDATE "issue" SET
1673 "state" = "issue_row"."state",
1674 "half_frozen" = "issue_row"."half_frozen"
1675 WHERE "id" = "issue_row"."id";
1676 END IF;
1677 END IF;
1678 -- close issues after some time, if all initiatives have been revoked:
1679 IF
1680 "issue_row"."closed" ISNULL AND
1681 NOT EXISTS (
1682 -- all initiatives are revoked
1683 SELECT NULL FROM "initiative"
1684 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
1685 ) AND (
1686 -- and issue has not been accepted yet
1687 "issue_row"."accepted" ISNULL OR
1688 NOT EXISTS (
1689 -- or no initiatives have been revoked lately
1690 SELECT NULL FROM "initiative"
1691 WHERE "issue_id" = "issue_id_p"
1692 AND now() < "revoked" + "issue_row"."verification_time"
1693 ) OR (
1694 -- or verification time has elapsed
1695 "issue_row"."half_frozen" NOTNULL AND
1696 "issue_row"."fully_frozen" ISNULL AND
1697 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
1700 THEN
1701 -- NOTE: "issue_row" used later
1702 IF "issue_row"."accepted" ISNULL THEN
1703 "issue_row"."state" := 'canceled_revoked_before_accepted';
1704 ELSIF "issue_row"."half_frozen" ISNULL THEN
1705 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
1706 ELSE
1707 "issue_row"."state" := 'canceled_after_revocation_during_verification';
1708 END IF;
1709 "issue_row"."closed" := now();
1710 UPDATE "issue" SET
1711 "state" = "issue_row"."state",
1712 "closed" = "issue_row"."closed"
1713 WHERE "id" = "issue_row"."id";
1714 END IF;
1715 -- fully freeze issue after verification time:
1716 IF
1717 "issue_row"."half_frozen" NOTNULL AND
1718 "issue_row"."fully_frozen" ISNULL AND
1719 "issue_row"."closed" ISNULL AND
1720 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
1721 THEN
1722 PERFORM "freeze_after_snapshot"("issue_id_p");
1723 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
1724 END IF;
1725 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1726 -- close issue by calling close_voting(...) after voting time:
1727 IF
1728 "issue_row"."closed" ISNULL AND
1729 "issue_row"."fully_frozen" NOTNULL AND
1730 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
1731 THEN
1732 PERFORM "close_voting"("issue_id_p");
1733 -- calculate ranks will not consume much time and can be done now
1734 PERFORM "calculate_ranks"("issue_id_p");
1735 END IF;
1736 END IF;
1737 RETURN;
1738 END;
1739 $$;
1741 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
1742 RETURNS VOID
1743 LANGUAGE 'plpgsql' VOLATILE AS $$
1744 DECLARE
1745 "issue_row" "issue"%ROWTYPE;
1746 BEGIN
1747 SELECT * INTO "issue_row"
1748 FROM "issue" WHERE "id" = "issue_id_p"
1749 FOR UPDATE;
1750 IF "issue_row"."cleaned" ISNULL THEN
1751 UPDATE "issue" SET
1752 "state" = 'voting',
1753 "closed" = NULL,
1754 "ranks_available" = FALSE
1755 WHERE "id" = "issue_id_p";
1756 DELETE FROM "issue_comment"
1757 WHERE "issue_id" = "issue_id_p";
1758 DELETE FROM "voting_comment"
1759 WHERE "issue_id" = "issue_id_p";
1760 DELETE FROM "delegating_voter"
1761 WHERE "issue_id" = "issue_id_p";
1762 DELETE FROM "direct_voter"
1763 WHERE "issue_id" = "issue_id_p";
1764 DELETE FROM "delegating_interest_snapshot"
1765 WHERE "issue_id" = "issue_id_p";
1766 DELETE FROM "direct_interest_snapshot"
1767 WHERE "issue_id" = "issue_id_p";
1768 DELETE FROM "delegating_population_snapshot"
1769 WHERE "issue_id" = "issue_id_p";
1770 DELETE FROM "direct_population_snapshot"
1771 WHERE "issue_id" = "issue_id_p";
1772 DELETE FROM "non_voter"
1773 WHERE "issue_id" = "issue_id_p";
1774 DELETE FROM "delegation"
1775 WHERE "issue_id" = "issue_id_p";
1776 DELETE FROM "supporter"
1777 WHERE "issue_id" = "issue_id_p";
1778 UPDATE "issue" SET
1779 "state" = "issue_row"."state",
1780 "closed" = "issue_row"."closed",
1781 "ranks_available" = "issue_row"."ranks_available",
1782 "cleaned" = now()
1783 WHERE "id" = "issue_id_p";
1784 END IF;
1785 RETURN;
1786 END;
1787 $$;
1789 CREATE OR REPLACE FUNCTION "check_issue"
1790 ( "issue_id_p" "issue"."id"%TYPE )
1791 RETURNS VOID
1792 LANGUAGE 'plpgsql' VOLATILE AS $$
1793 DECLARE
1794 "issue_row" "issue"%ROWTYPE;
1795 "policy_row" "policy"%ROWTYPE;
1796 "voting_requested_v" BOOLEAN;
1797 BEGIN
1798 PERFORM "lock_issue"("issue_id_p");
1799 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1800 -- only process open issues:
1801 IF "issue_row"."closed" ISNULL THEN
1802 SELECT * INTO "policy_row" FROM "policy"
1803 WHERE "id" = "issue_row"."policy_id";
1804 -- create a snapshot, unless issue is already fully frozen:
1805 IF "issue_row"."fully_frozen" ISNULL THEN
1806 PERFORM "create_snapshot"("issue_id_p");
1807 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1808 END IF;
1809 -- eventually close or accept issues, which have not been accepted:
1810 IF "issue_row"."accepted" ISNULL THEN
1811 IF EXISTS (
1812 SELECT NULL FROM "initiative"
1813 WHERE "issue_id" = "issue_id_p"
1814 AND "supporter_count" > 0
1815 AND "supporter_count" * "policy_row"."issue_quorum_den"
1816 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
1817 ) THEN
1818 -- accept issues, if supporter count is high enough
1819 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
1820 -- NOTE: "issue_row" used later
1821 "issue_row"."state" := 'discussion';
1822 "issue_row"."accepted" := now();
1823 UPDATE "issue" SET
1824 "state" = "issue_row"."state",
1825 "accepted" = "issue_row"."accepted"
1826 WHERE "id" = "issue_row"."id";
1827 ELSIF
1828 now() >= "issue_row"."created" + "issue_row"."admission_time"
1829 THEN
1830 -- close issues, if admission time has expired
1831 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
1832 UPDATE "issue" SET
1833 "state" = 'canceled_issue_not_accepted',
1834 "closed" = now()
1835 WHERE "id" = "issue_row"."id";
1836 END IF;
1837 END IF;
1838 -- eventually half freeze issues:
1839 IF
1840 -- NOTE: issue can't be closed at this point, if it has been accepted
1841 "issue_row"."accepted" NOTNULL AND
1842 "issue_row"."half_frozen" ISNULL
1843 THEN
1844 SELECT
1845 CASE
1846 WHEN "vote_now" * 2 > "issue_row"."population" THEN
1847 TRUE
1848 WHEN "vote_later" * 2 > "issue_row"."population" THEN
1849 FALSE
1850 ELSE NULL
1851 END
1852 INTO "voting_requested_v"
1853 FROM "issue" WHERE "id" = "issue_id_p";
1854 IF
1855 "voting_requested_v" OR (
1856 "voting_requested_v" ISNULL AND
1857 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
1859 THEN
1860 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
1861 -- NOTE: "issue_row" used later
1862 "issue_row"."state" := 'verification';
1863 "issue_row"."half_frozen" := now();
1864 UPDATE "issue" SET
1865 "state" = "issue_row"."state",
1866 "half_frozen" = "issue_row"."half_frozen"
1867 WHERE "id" = "issue_row"."id";
1868 END IF;
1869 END IF;
1870 -- close issues after some time, if all initiatives have been revoked:
1871 IF
1872 "issue_row"."closed" ISNULL AND
1873 NOT EXISTS (
1874 -- all initiatives are revoked
1875 SELECT NULL FROM "initiative"
1876 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
1877 ) AND (
1878 -- and issue has not been accepted yet
1879 "issue_row"."accepted" ISNULL OR
1880 NOT EXISTS (
1881 -- or no initiatives have been revoked lately
1882 SELECT NULL FROM "initiative"
1883 WHERE "issue_id" = "issue_id_p"
1884 AND now() < "revoked" + "issue_row"."verification_time"
1885 ) OR (
1886 -- or verification time has elapsed
1887 "issue_row"."half_frozen" NOTNULL AND
1888 "issue_row"."fully_frozen" ISNULL AND
1889 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
1892 THEN
1893 -- NOTE: "issue_row" used later
1894 IF "issue_row"."accepted" ISNULL THEN
1895 "issue_row"."state" := 'canceled_revoked_before_accepted';
1896 ELSIF "issue_row"."half_frozen" ISNULL THEN
1897 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
1898 ELSE
1899 "issue_row"."state" := 'canceled_after_revocation_during_verification';
1900 END IF;
1901 "issue_row"."closed" := now();
1902 UPDATE "issue" SET
1903 "state" = "issue_row"."state",
1904 "closed" = "issue_row"."closed"
1905 WHERE "id" = "issue_row"."id";
1906 END IF;
1907 -- fully freeze issue after verification time:
1908 IF
1909 "issue_row"."half_frozen" NOTNULL AND
1910 "issue_row"."fully_frozen" ISNULL AND
1911 "issue_row"."closed" ISNULL AND
1912 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
1913 THEN
1914 PERFORM "freeze_after_snapshot"("issue_id_p");
1915 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
1916 END IF;
1917 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1918 -- close issue by calling close_voting(...) after voting time:
1919 IF
1920 "issue_row"."closed" ISNULL AND
1921 "issue_row"."fully_frozen" NOTNULL AND
1922 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
1923 THEN
1924 PERFORM "close_voting"("issue_id_p");
1925 -- calculate ranks will not consume much time and can be done now
1926 PERFORM "calculate_ranks"("issue_id_p");
1927 END IF;
1928 END IF;
1929 RETURN;
1930 END;
1931 $$;
1933 CREATE OR REPLACE FUNCTION "delete_private_data"()
1934 RETURNS VOID
1935 LANGUAGE 'plpgsql' VOLATILE AS $$
1936 BEGIN
1937 UPDATE "member" SET
1938 "last_login" = NULL,
1939 "login" = NULL,
1940 "password" = NULL,
1941 "notify_email" = NULL,
1942 "notify_email_unconfirmed" = NULL,
1943 "notify_email_secret" = NULL,
1944 "notify_email_secret_expiry" = NULL,
1945 "notify_email_lock_expiry" = NULL,
1946 "password_reset_secret" = NULL,
1947 "password_reset_secret_expiry" = NULL,
1948 "organizational_unit" = NULL,
1949 "internal_posts" = NULL,
1950 "realname" = NULL,
1951 "birthday" = NULL,
1952 "address" = NULL,
1953 "email" = NULL,
1954 "xmpp_address" = NULL,
1955 "website" = NULL,
1956 "phone" = NULL,
1957 "mobile_phone" = NULL,
1958 "profession" = NULL,
1959 "external_memberships" = NULL,
1960 "external_posts" = NULL,
1961 "statement" = NULL;
1962 -- "text_search_data" is updated by triggers
1963 DELETE FROM "invite_code";
1964 DELETE FROM "setting";
1965 DELETE FROM "setting_map";
1966 DELETE FROM "member_relation_setting";
1967 DELETE FROM "member_image";
1968 DELETE FROM "contact";
1969 DELETE FROM "ignored_member";
1970 DELETE FROM "session";
1971 DELETE FROM "area_setting";
1972 DELETE FROM "issue_setting";
1973 DELETE FROM "ignored_initiative";
1974 DELETE FROM "initiative_setting";
1975 DELETE FROM "suggestion_setting";
1976 DELETE FROM "non_voter";
1977 DELETE FROM "direct_voter" USING "issue"
1978 WHERE "direct_voter"."issue_id" = "issue"."id"
1979 AND "issue"."closed" ISNULL;
1980 RETURN;
1981 END;
1982 $$;
1985 -- Delete old "delegation_scope" TYPE:
1987 DROP TYPE "delegation_scope_old";
1990 COMMIT;
1993 -- Generate issue states and add constraints:
1995 UPDATE "issue" SET "state" =
1996 CASE
1997 WHEN "closed" ISNULL THEN
1998 CASE
1999 WHEN "accepted" ISNULL THEN
2000 'admission'::"issue_state"
2001 WHEN "half_frozen" ISNULL THEN
2002 'discussion'::"issue_state"
2003 WHEN "fully_frozen" ISNULL THEN
2004 'verification'::"issue_state"
2005 ELSE
2006 'voting'::"issue_state"
2007 END
2008 WHEN "fully_frozen" NOTNULL THEN
2009 CASE
2010 WHEN "fully_frozen" = "closed" THEN
2011 'canceled_no_initiative_admitted'::"issue_state"
2012 ELSE
2013 'finished_without_winner'::"issue_state" -- NOTE: corrected later
2014 END
2015 WHEN "half_frozen" NOTNULL THEN
2016 'canceled_after_revocation_during_verification'::"issue_state"
2017 WHEN "accepted" NOTNULL THEN
2018 'canceled_after_revocation_during_discussion'::"issue_state"
2019 ELSE
2020 'canceled_revoked_before_accepted'::"issue_state" -- NOTE: corrected later
2021 END;
2022 UPDATE "issue" SET "state" = 'finished_with_winner'
2023 FROM "initiative"
2024 WHERE "issue"."id" = "initiative"."issue_id"
2025 AND "issue"."state" = 'finished_without_winner'
2026 AND "initiative"."agreed";
2027 UPDATE "issue" SET "state" = 'canceled_issue_not_accepted'
2028 FROM "initiative"
2029 WHERE "issue"."id" = "initiative"."issue_id"
2030 AND "issue"."state" = 'canceled_revoked_before_accepted'
2031 AND "initiative"."revoked" ISNULL;
2033 ALTER TABLE "issue" ALTER "state" SET NOT NULL;
2035 ALTER TABLE "issue" DROP CONSTRAINT "valid_state";
2036 ALTER TABLE "issue" ADD CONSTRAINT "valid_state" CHECK ((
2037 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
2038 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
2039 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
2040 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
2041 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
2042 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
2043 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
2044 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
2045 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)
2046 ) AND (
2047 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
2048 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
2049 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
2050 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
2051 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
2052 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
2053 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
2054 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
2055 ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
2056 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
2057 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
2058 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
2059 ));
2062 -- Guess "revoked_by_member_id" values based on author of current draft and add constraint:
2064 UPDATE "initiative" SET "revoked_by_member_id" = "author_id"
2065 FROM "current_draft"
2066 WHERE "initiative"."id" = "current_draft"."initiative_id"
2067 AND "initiative"."revoked" NOTNULL;
2069 ALTER TABLE "initiative" ADD
2070 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
2071 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL);
2074 -- Fill "unit_id" column with default value where neccessary and add constraints:
2076 UPDATE "delegation" SET "unit_id" = 1 WHERE "scope" = 'unit';
2078 ALTER TABLE "delegation" ADD CONSTRAINT "area_id_and_issue_id_set_according_to_scope"
2079 CHECK (
2080 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
2081 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
2082 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) );
2085 -- Filling of "event" table with old (reconstructed) events:
2087 DELETE FROM "event";
2088 SELECT setval('event_id_seq', 1, false);
2090 INSERT INTO "event"
2091 ( "occurrence", "event", "member_id", "issue_id", "state",
2092 "initiative_id", "draft_id", "suggestion_id" )
2093 SELECT * FROM (
2094 SELECT * FROM (
2095 SELECT DISTINCT ON ("initiative"."id")
2096 "timeline"."occurrence",
2097 CASE WHEN "issue_creation"."issue_id" NOTNULL THEN
2098 'initiative_created_in_new_issue'::"event_type"
2099 ELSE
2100 'initiative_created_in_existing_issue'::"event_type"
2101 END,
2102 "draft"."author_id",
2103 "issue"."id",
2104 CASE
2105 WHEN "timeline"."occurrence" < "issue"."accepted" THEN
2106 'admission'::"issue_state"
2107 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
2108 'discussion'::"issue_state"
2109 ELSE
2110 'verification'::"issue_state"
2111 END,
2112 "initiative"."id",
2113 "draft"."id",
2114 NULL::INT8
2115 FROM "timeline"
2116 JOIN "initiative" ON "timeline"."initiative_id" = "initiative"."id"
2117 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2118 LEFT JOIN "timeline" AS "issue_creation"
2119 ON "initiative"."issue_id" = "issue_creation"."issue_id"
2120 AND "issue_creation"."event" = 'issue_created'
2121 AND "timeline"."occurrence" = "issue_creation"."occurrence"
2122 JOIN "draft"
2123 ON "initiative"."id" = "draft"."initiative_id"
2124 WHERE "timeline"."event" = 'initiative_created'
2125 ORDER BY "initiative"."id", "draft"."id"
2126 ) AS "subquery" -- NOTE: subquery needed due to DISTINCT/ORDER
2127 UNION ALL
2128 SELECT
2129 "timeline"."occurrence",
2130 'issue_state_changed'::"event_type",
2131 NULL,
2132 "issue"."id",
2133 CASE
2134 WHEN "timeline"."event" IN (
2135 'issue_canceled',
2136 'issue_finished_without_voting',
2137 'issue_finished_after_voting'
2138 ) THEN
2139 "issue"."state"
2140 WHEN "timeline"."event" = 'issue_accepted' THEN
2141 'discussion'::"issue_state"
2142 WHEN "timeline"."event" = 'issue_half_frozen' THEN
2143 'verification'::"issue_state"
2144 WHEN "timeline"."event" = 'issue_voting_started' THEN
2145 'voting'::"issue_state"
2146 END,
2147 NULL,
2148 NULL,
2149 NULL
2150 FROM "timeline"
2151 JOIN "issue" ON "timeline"."issue_id" = "issue"."id"
2152 WHERE "timeline"."event" IN (
2153 'issue_canceled',
2154 'issue_accepted',
2155 'issue_half_frozen',
2156 'issue_finished_without_voting',
2157 'issue_voting_started',
2158 'issue_finished_after_voting' )
2159 UNION ALL
2160 SELECT
2161 "timeline"."occurrence",
2162 'initiative_revoked'::"event_type",
2163 "initiative"."revoked_by_member_id",
2164 "issue"."id",
2165 CASE
2166 WHEN "timeline"."occurrence" < "issue"."accepted" THEN
2167 'admission'::"issue_state"
2168 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
2169 'discussion'::"issue_state"
2170 ELSE
2171 'verification'::"issue_state"
2172 END,
2173 "initiative"."id",
2174 "current_draft"."id",
2175 NULL
2176 FROM "timeline"
2177 JOIN "initiative" ON "timeline"."initiative_id" = "initiative"."id"
2178 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2179 JOIN "current_draft" ON "initiative"."id" = "current_draft"."initiative_id"
2180 WHERE "timeline"."event" = 'initiative_revoked'
2181 UNION ALL
2182 SELECT
2183 "timeline"."occurrence",
2184 'new_draft_created'::"event_type",
2185 "draft"."author_id",
2186 "issue"."id",
2187 CASE
2188 WHEN "timeline"."occurrence" < "issue"."accepted" THEN
2189 'admission'::"issue_state"
2190 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
2191 'discussion'::"issue_state"
2192 ELSE
2193 'verification'::"issue_state"
2194 END,
2195 "initiative"."id",
2196 "draft"."id",
2197 NULL
2198 FROM "timeline"
2199 JOIN "draft" ON "timeline"."draft_id" = "draft"."id"
2200 JOIN "initiative" ON "draft"."initiative_id" = "initiative"."id"
2201 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
2202 LEFT JOIN "timeline" AS "initiative_creation"
2203 ON "initiative"."id" = "initiative_creation"."initiative_id"
2204 AND "initiative_creation"."event" = 'initiative_created'
2205 AND "timeline"."occurrence" = "initiative_creation"."occurrence"
2206 WHERE "timeline"."event" = 'draft_created'
2207 AND "initiative_creation"."initiative_id" ISNULL
2208 UNION ALL
2209 SELECT
2210 "timeline"."occurrence",
2211 'suggestion_created'::"event_type",
2212 "suggestion"."author_id",
2213 "issue"."id",
2214 CASE
2215 WHEN "timeline"."occurrence" < "issue"."accepted" THEN
2216 'admission'::"issue_state"
2217 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
2218 'discussion'::"issue_state"
2219 ELSE
2220 'verification'::"issue_state"
2221 END,
2222 "initiative"."id",
2223 NULL,
2224 "suggestion"."id"
2225 FROM "timeline"
2226 JOIN "suggestion" ON "timeline"."suggestion_id" = "suggestion"."id"
2227 JOIN "initiative" ON "suggestion"."initiative_id" = "initiative"."id"
2228 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
2229 WHERE "timeline"."event" = 'suggestion_created'
2230 ) AS "subquery"
2231 ORDER BY "occurrence";

Impressum / About Us