liquid_feedback_core

view update/core-update.v1.3.1-v1.4.0_rc1.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_rc1', 1, 4, -1))
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 "id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
1266 SELECT "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 LEFT JOIN "direct_voter"
1298 ON "interest"."member_id" = "direct_voter"."member_id"
1299 AND "interest"."issue_id" = "direct_voter"."issue_id"
1300 LEFT JOIN "delegating_voter"
1301 ON "interest"."member_id" = "delegating_voter"."member_id"
1302 AND "interest"."issue_id" = "delegating_voter"."issue_id"
1303 WHERE "interest"."issue_id" = "issue_id_p"
1304 AND "interest"."autoreject" = TRUE
1305 AND "member"."active"
1306 AND "direct_voter"."member_id" ISNULL
1307 AND "delegating_voter"."member_id" ISNULL
1308 UNION SELECT "membership"."member_id"
1309 FROM "membership"
1310 JOIN "member"
1311 ON "membership"."member_id" = "member"."id"
1312 LEFT JOIN "interest"
1313 ON "membership"."member_id" = "interest"."member_id"
1314 AND "interest"."issue_id" = "issue_id_p"
1315 LEFT JOIN "direct_voter"
1316 ON "membership"."member_id" = "direct_voter"."member_id"
1317 AND "direct_voter"."issue_id" = "issue_id_p"
1318 LEFT JOIN "delegating_voter"
1319 ON "membership"."member_id" = "delegating_voter"."member_id"
1320 AND "delegating_voter"."issue_id" = "issue_id_p"
1321 WHERE "membership"."area_id" = "area_id_v"
1322 AND "membership"."autoreject" = TRUE
1323 AND "member"."active"
1324 AND "interest"."autoreject" ISNULL
1325 AND "direct_voter"."member_id" ISNULL
1326 AND "delegating_voter"."member_id" ISNULL
1327 LOOP
1328 INSERT INTO "direct_voter"
1329 ("member_id", "issue_id", "weight", "autoreject") VALUES
1330 ("member_id_v", "issue_id_p", 1, TRUE);
1331 INSERT INTO "vote" (
1332 "member_id",
1333 "issue_id",
1334 "initiative_id",
1335 "grade"
1336 ) SELECT
1337 "member_id_v" AS "member_id",
1338 "issue_id_p" AS "issue_id",
1339 "id" AS "initiative_id",
1340 -1 AS "grade"
1341 FROM "initiative" WHERE "issue_id" = "issue_id_p";
1342 END LOOP;
1343 PERFORM "add_vote_delegations"("issue_id_p");
1344 UPDATE "issue" SET
1345 "state" = 'calculation',
1346 "closed" = now(),
1347 "voter_count" = (
1348 SELECT coalesce(sum("weight"), 0)
1349 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
1351 WHERE "id" = "issue_id_p";
1352 UPDATE "initiative" SET
1353 "positive_votes" = "vote_counts"."positive_votes",
1354 "negative_votes" = "vote_counts"."negative_votes",
1355 "agreed" = CASE WHEN "majority_strict" THEN
1356 "vote_counts"."positive_votes" * "majority_den" >
1357 "majority_num" *
1358 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
1359 ELSE
1360 "vote_counts"."positive_votes" * "majority_den" >=
1361 "majority_num" *
1362 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
1363 END
1364 FROM
1365 ( SELECT
1366 "initiative"."id" AS "initiative_id",
1367 coalesce(
1368 sum(
1369 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
1370 ),
1372 ) AS "positive_votes",
1373 coalesce(
1374 sum(
1375 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
1376 ),
1378 ) AS "negative_votes"
1379 FROM "initiative"
1380 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
1381 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
1382 LEFT JOIN "direct_voter"
1383 ON "direct_voter"."issue_id" = "initiative"."issue_id"
1384 LEFT JOIN "vote"
1385 ON "vote"."initiative_id" = "initiative"."id"
1386 AND "vote"."member_id" = "direct_voter"."member_id"
1387 WHERE "initiative"."issue_id" = "issue_id_p"
1388 AND "initiative"."admitted" -- NOTE: NULL case is handled too
1389 GROUP BY "initiative"."id"
1390 ) AS "vote_counts",
1391 "issue",
1392 "policy"
1393 WHERE "vote_counts"."initiative_id" = "initiative"."id"
1394 AND "issue"."id" = "initiative"."issue_id"
1395 AND "policy"."id" = "issue"."policy_id";
1396 -- NOTE: "closed" column of issue must be set at this point
1397 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
1398 INSERT INTO "battle" (
1399 "issue_id",
1400 "winning_initiative_id", "losing_initiative_id",
1401 "count"
1402 ) SELECT
1403 "issue_id",
1404 "winning_initiative_id", "losing_initiative_id",
1405 "count"
1406 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
1407 END;
1408 $$;
1410 CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
1411 RETURNS VOID
1412 LANGUAGE 'plpgsql' VOLATILE AS $$
1413 DECLARE
1414 "dimension_v" INTEGER;
1415 "vote_matrix" INT4[][]; -- absolute votes
1416 "matrix" INT8[][]; -- defeat strength / best paths
1417 "i" INTEGER;
1418 "j" INTEGER;
1419 "k" INTEGER;
1420 "battle_row" "battle"%ROWTYPE;
1421 "rank_ary" INT4[];
1422 "rank_v" INT4;
1423 "done_v" INTEGER;
1424 "winners_ary" INTEGER[];
1425 "initiative_id_v" "initiative"."id"%TYPE;
1426 BEGIN
1427 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
1428 SELECT count(1) INTO "dimension_v" FROM "initiative"
1429 WHERE "issue_id" = "issue_id_p" AND "agreed";
1430 IF "dimension_v" = 1 THEN
1431 UPDATE "initiative" SET "rank" = 1
1432 WHERE "issue_id" = "issue_id_p" AND "agreed";
1433 ELSIF "dimension_v" > 1 THEN
1434 -- Create "vote_matrix" with absolute number of votes in pairwise
1435 -- comparison:
1436 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
1437 "i" := 1;
1438 "j" := 2;
1439 FOR "battle_row" IN
1440 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
1441 ORDER BY "winning_initiative_id", "losing_initiative_id"
1442 LOOP
1443 "vote_matrix"["i"]["j"] := "battle_row"."count";
1444 IF "j" = "dimension_v" THEN
1445 "i" := "i" + 1;
1446 "j" := 1;
1447 ELSE
1448 "j" := "j" + 1;
1449 IF "j" = "i" THEN
1450 "j" := "j" + 1;
1451 END IF;
1452 END IF;
1453 END LOOP;
1454 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
1455 RAISE EXCEPTION 'Wrong battle count (should not happen)';
1456 END IF;
1457 -- Store defeat strengths in "matrix" using "defeat_strength"
1458 -- function:
1459 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
1460 "i" := 1;
1461 LOOP
1462 "j" := 1;
1463 LOOP
1464 IF "i" != "j" THEN
1465 "matrix"["i"]["j"] := "defeat_strength"(
1466 "vote_matrix"["i"]["j"],
1467 "vote_matrix"["j"]["i"]
1468 );
1469 END IF;
1470 EXIT WHEN "j" = "dimension_v";
1471 "j" := "j" + 1;
1472 END LOOP;
1473 EXIT WHEN "i" = "dimension_v";
1474 "i" := "i" + 1;
1475 END LOOP;
1476 -- Find best paths:
1477 "i" := 1;
1478 LOOP
1479 "j" := 1;
1480 LOOP
1481 IF "i" != "j" THEN
1482 "k" := 1;
1483 LOOP
1484 IF "i" != "k" AND "j" != "k" THEN
1485 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
1486 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
1487 "matrix"["j"]["k"] := "matrix"["j"]["i"];
1488 END IF;
1489 ELSE
1490 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
1491 "matrix"["j"]["k"] := "matrix"["i"]["k"];
1492 END IF;
1493 END IF;
1494 END IF;
1495 EXIT WHEN "k" = "dimension_v";
1496 "k" := "k" + 1;
1497 END LOOP;
1498 END IF;
1499 EXIT WHEN "j" = "dimension_v";
1500 "j" := "j" + 1;
1501 END LOOP;
1502 EXIT WHEN "i" = "dimension_v";
1503 "i" := "i" + 1;
1504 END LOOP;
1505 -- Determine order of winners:
1506 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
1507 "rank_v" := 1;
1508 "done_v" := 0;
1509 LOOP
1510 "winners_ary" := '{}';
1511 "i" := 1;
1512 LOOP
1513 IF "rank_ary"["i"] ISNULL THEN
1514 "j" := 1;
1515 LOOP
1516 IF
1517 "i" != "j" AND
1518 "rank_ary"["j"] ISNULL AND
1519 "matrix"["j"]["i"] > "matrix"["i"]["j"]
1520 THEN
1521 -- someone else is better
1522 EXIT;
1523 END IF;
1524 IF "j" = "dimension_v" THEN
1525 -- noone is better
1526 "winners_ary" := "winners_ary" || "i";
1527 EXIT;
1528 END IF;
1529 "j" := "j" + 1;
1530 END LOOP;
1531 END IF;
1532 EXIT WHEN "i" = "dimension_v";
1533 "i" := "i" + 1;
1534 END LOOP;
1535 "i" := 1;
1536 LOOP
1537 "rank_ary"["winners_ary"["i"]] := "rank_v";
1538 "done_v" := "done_v" + 1;
1539 EXIT WHEN "i" = array_upper("winners_ary", 1);
1540 "i" := "i" + 1;
1541 END LOOP;
1542 EXIT WHEN "done_v" = "dimension_v";
1543 "rank_v" := "rank_v" + 1;
1544 END LOOP;
1545 -- write preliminary ranks:
1546 "i" := 1;
1547 FOR "initiative_id_v" IN
1548 SELECT "id" FROM "initiative"
1549 WHERE "issue_id" = "issue_id_p" AND "agreed"
1550 ORDER BY "id"
1551 LOOP
1552 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
1553 WHERE "id" = "initiative_id_v";
1554 "i" := "i" + 1;
1555 END LOOP;
1556 IF "i" != "dimension_v" + 1 THEN
1557 RAISE EXCEPTION 'Wrong winner count (should not happen)';
1558 END IF;
1559 -- straighten ranks (start counting with 1, no equal ranks):
1560 "rank_v" := 1;
1561 FOR "initiative_id_v" IN
1562 SELECT "id" FROM "initiative"
1563 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
1564 ORDER BY
1565 "rank",
1566 "vote_ratio"("positive_votes", "negative_votes") DESC,
1567 "id"
1568 LOOP
1569 UPDATE "initiative" SET "rank" = "rank_v"
1570 WHERE "id" = "initiative_id_v";
1571 "rank_v" := "rank_v" + 1;
1572 END LOOP;
1573 END IF;
1574 -- mark issue as finished
1575 UPDATE "issue" SET
1576 "state" =
1577 CASE WHEN "dimension_v" = 0 THEN
1578 'finished_without_winner'::"issue_state"
1579 ELSE
1580 'finished_with_winner'::"issue_state"
1581 END,
1582 "ranks_available" = TRUE
1583 WHERE "id" = "issue_id_p";
1584 RETURN;
1585 END;
1586 $$;
1588 CREATE OR REPLACE FUNCTION "check_issue"
1589 ( "issue_id_p" "issue"."id"%TYPE )
1590 RETURNS VOID
1591 LANGUAGE 'plpgsql' VOLATILE AS $$
1592 DECLARE
1593 "issue_row" "issue"%ROWTYPE;
1594 "policy_row" "policy"%ROWTYPE;
1595 "voting_requested_v" BOOLEAN;
1596 BEGIN
1597 PERFORM "lock_issue"("issue_id_p");
1598 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1599 -- only process open issues:
1600 IF "issue_row"."closed" ISNULL THEN
1601 SELECT * INTO "policy_row" FROM "policy"
1602 WHERE "id" = "issue_row"."policy_id";
1603 -- create a snapshot, unless issue is already fully frozen:
1604 IF "issue_row"."fully_frozen" ISNULL THEN
1605 PERFORM "create_snapshot"("issue_id_p");
1606 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1607 END IF;
1608 -- eventually close or accept issues, which have not been accepted:
1609 IF "issue_row"."accepted" ISNULL THEN
1610 IF EXISTS (
1611 SELECT NULL FROM "initiative"
1612 WHERE "issue_id" = "issue_id_p"
1613 AND "supporter_count" > 0
1614 AND "supporter_count" * "policy_row"."issue_quorum_den"
1615 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
1616 ) THEN
1617 -- accept issues, if supporter count is high enough
1618 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
1619 -- NOTE: "issue_row" used later
1620 "issue_row"."state" := 'discussion';
1621 "issue_row"."accepted" := now();
1622 UPDATE "issue" SET
1623 "state" = "issue_row"."state",
1624 "accepted" = "issue_row"."accepted"
1625 WHERE "id" = "issue_row"."id";
1626 ELSIF
1627 now() >= "issue_row"."created" + "issue_row"."admission_time"
1628 THEN
1629 -- close issues, if admission time has expired
1630 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
1631 UPDATE "issue" SET
1632 "state" = 'canceled_issue_not_accepted',
1633 "closed" = now()
1634 WHERE "id" = "issue_row"."id";
1635 END IF;
1636 END IF;
1637 -- eventually half freeze issues:
1638 IF
1639 -- NOTE: issue can't be closed at this point, if it has been accepted
1640 "issue_row"."accepted" NOTNULL AND
1641 "issue_row"."half_frozen" ISNULL
1642 THEN
1643 SELECT
1644 CASE
1645 WHEN "vote_now" * 2 > "issue_row"."population" THEN
1646 TRUE
1647 WHEN "vote_later" * 2 > "issue_row"."population" THEN
1648 FALSE
1649 ELSE NULL
1650 END
1651 INTO "voting_requested_v"
1652 FROM "issue" WHERE "id" = "issue_id_p";
1653 IF
1654 "voting_requested_v" OR (
1655 "voting_requested_v" ISNULL AND
1656 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
1658 THEN
1659 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
1660 -- NOTE: "issue_row" used later
1661 "issue_row"."state" := 'verification';
1662 "issue_row"."half_frozen" := now();
1663 UPDATE "issue" SET
1664 "state" = "issue_row"."state",
1665 "half_frozen" = "issue_row"."half_frozen"
1666 WHERE "id" = "issue_row"."id";
1667 END IF;
1668 END IF;
1669 -- close issues after some time, if all initiatives have been revoked:
1670 IF
1671 "issue_row"."closed" ISNULL AND
1672 NOT EXISTS (
1673 -- all initiatives are revoked
1674 SELECT NULL FROM "initiative"
1675 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
1676 ) AND (
1677 -- and issue has not been accepted yet
1678 "issue_row"."accepted" ISNULL OR
1679 NOT EXISTS (
1680 -- or no initiatives have been revoked lately
1681 SELECT NULL FROM "initiative"
1682 WHERE "issue_id" = "issue_id_p"
1683 AND now() < "revoked" + "issue_row"."verification_time"
1684 ) OR (
1685 -- or verification time has elapsed
1686 "issue_row"."half_frozen" NOTNULL AND
1687 "issue_row"."fully_frozen" ISNULL AND
1688 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
1691 THEN
1692 -- NOTE: "issue_row" used later
1693 IF "issue_row"."accepted" ISNULL THEN
1694 "issue_row"."state" := 'canceled_revoked_before_accepted';
1695 ELSIF "issue_row"."half_frozen" ISNULL THEN
1696 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
1697 ELSE
1698 "issue_row"."state" := 'canceled_after_revocation_during_verification';
1699 END IF;
1700 "issue_row"."closed" := now();
1701 UPDATE "issue" SET
1702 "state" = "issue_row"."state",
1703 "closed" = "issue_row"."closed"
1704 WHERE "id" = "issue_row"."id";
1705 END IF;
1706 -- fully freeze issue after verification time:
1707 IF
1708 "issue_row"."half_frozen" NOTNULL AND
1709 "issue_row"."fully_frozen" ISNULL AND
1710 "issue_row"."closed" ISNULL AND
1711 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
1712 THEN
1713 PERFORM "freeze_after_snapshot"("issue_id_p");
1714 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
1715 END IF;
1716 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1717 -- close issue by calling close_voting(...) after voting time:
1718 IF
1719 "issue_row"."closed" ISNULL AND
1720 "issue_row"."fully_frozen" NOTNULL AND
1721 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
1722 THEN
1723 PERFORM "close_voting"("issue_id_p");
1724 -- calculate ranks will not consume much time and can be done now
1725 PERFORM "calculate_ranks"("issue_id_p");
1726 END IF;
1727 END IF;
1728 RETURN;
1729 END;
1730 $$;
1732 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
1733 RETURNS VOID
1734 LANGUAGE 'plpgsql' VOLATILE AS $$
1735 DECLARE
1736 "issue_row" "issue"%ROWTYPE;
1737 BEGIN
1738 SELECT * INTO "issue_row"
1739 FROM "issue" WHERE "id" = "issue_id_p"
1740 FOR UPDATE;
1741 IF "issue_row"."cleaned" ISNULL THEN
1742 UPDATE "issue" SET
1743 "closed" = NULL,
1744 "ranks_available" = FALSE
1745 WHERE "id" = "issue_id_p";
1746 DELETE FROM "delegating_voter"
1747 WHERE "issue_id" = "issue_id_p";
1748 DELETE FROM "direct_voter"
1749 WHERE "issue_id" = "issue_id_p";
1750 DELETE FROM "delegating_interest_snapshot"
1751 WHERE "issue_id" = "issue_id_p";
1752 DELETE FROM "direct_interest_snapshot"
1753 WHERE "issue_id" = "issue_id_p";
1754 DELETE FROM "delegating_population_snapshot"
1755 WHERE "issue_id" = "issue_id_p";
1756 DELETE FROM "direct_population_snapshot"
1757 WHERE "issue_id" = "issue_id_p";
1758 DELETE FROM "non_voter"
1759 WHERE "issue_id" = "issue_id_p";
1760 DELETE FROM "delegation"
1761 WHERE "issue_id" = "issue_id_p";
1762 DELETE FROM "supporter"
1763 WHERE "issue_id" = "issue_id_p";
1764 UPDATE "issue" SET
1765 "closed" = "issue_row"."closed",
1766 "ranks_available" = "issue_row"."ranks_available",
1767 "cleaned" = now()
1768 WHERE "id" = "issue_id_p";
1769 END IF;
1770 RETURN;
1771 END;
1772 $$;
1774 CREATE OR REPLACE FUNCTION "check_issue"
1775 ( "issue_id_p" "issue"."id"%TYPE )
1776 RETURNS VOID
1777 LANGUAGE 'plpgsql' VOLATILE AS $$
1778 DECLARE
1779 "issue_row" "issue"%ROWTYPE;
1780 "policy_row" "policy"%ROWTYPE;
1781 "voting_requested_v" BOOLEAN;
1782 BEGIN
1783 PERFORM "lock_issue"("issue_id_p");
1784 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1785 -- only process open issues:
1786 IF "issue_row"."closed" ISNULL THEN
1787 SELECT * INTO "policy_row" FROM "policy"
1788 WHERE "id" = "issue_row"."policy_id";
1789 -- create a snapshot, unless issue is already fully frozen:
1790 IF "issue_row"."fully_frozen" ISNULL THEN
1791 PERFORM "create_snapshot"("issue_id_p");
1792 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1793 END IF;
1794 -- eventually close or accept issues, which have not been accepted:
1795 IF "issue_row"."accepted" ISNULL THEN
1796 IF EXISTS (
1797 SELECT NULL FROM "initiative"
1798 WHERE "issue_id" = "issue_id_p"
1799 AND "supporter_count" > 0
1800 AND "supporter_count" * "policy_row"."issue_quorum_den"
1801 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
1802 ) THEN
1803 -- accept issues, if supporter count is high enough
1804 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
1805 -- NOTE: "issue_row" used later
1806 "issue_row"."state" := 'discussion';
1807 "issue_row"."accepted" := now();
1808 UPDATE "issue" SET
1809 "state" = "issue_row"."state",
1810 "accepted" = "issue_row"."accepted"
1811 WHERE "id" = "issue_row"."id";
1812 ELSIF
1813 now() >= "issue_row"."created" + "issue_row"."admission_time"
1814 THEN
1815 -- close issues, if admission time has expired
1816 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
1817 UPDATE "issue" SET
1818 "state" = 'canceled_issue_not_accepted',
1819 "closed" = now()
1820 WHERE "id" = "issue_row"."id";
1821 END IF;
1822 END IF;
1823 -- eventually half freeze issues:
1824 IF
1825 -- NOTE: issue can't be closed at this point, if it has been accepted
1826 "issue_row"."accepted" NOTNULL AND
1827 "issue_row"."half_frozen" ISNULL
1828 THEN
1829 SELECT
1830 CASE
1831 WHEN "vote_now" * 2 > "issue_row"."population" THEN
1832 TRUE
1833 WHEN "vote_later" * 2 > "issue_row"."population" THEN
1834 FALSE
1835 ELSE NULL
1836 END
1837 INTO "voting_requested_v"
1838 FROM "issue" WHERE "id" = "issue_id_p";
1839 IF
1840 "voting_requested_v" OR (
1841 "voting_requested_v" ISNULL AND
1842 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
1844 THEN
1845 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
1846 -- NOTE: "issue_row" used later
1847 "issue_row"."state" := 'verification';
1848 "issue_row"."half_frozen" := now();
1849 UPDATE "issue" SET
1850 "state" = "issue_row"."state",
1851 "half_frozen" = "issue_row"."half_frozen"
1852 WHERE "id" = "issue_row"."id";
1853 END IF;
1854 END IF;
1855 -- close issues after some time, if all initiatives have been revoked:
1856 IF
1857 "issue_row"."closed" ISNULL AND
1858 NOT EXISTS (
1859 -- all initiatives are revoked
1860 SELECT NULL FROM "initiative"
1861 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
1862 ) AND (
1863 -- and issue has not been accepted yet
1864 "issue_row"."accepted" ISNULL OR
1865 NOT EXISTS (
1866 -- or no initiatives have been revoked lately
1867 SELECT NULL FROM "initiative"
1868 WHERE "issue_id" = "issue_id_p"
1869 AND now() < "revoked" + "issue_row"."verification_time"
1870 ) OR (
1871 -- or verification time has elapsed
1872 "issue_row"."half_frozen" NOTNULL AND
1873 "issue_row"."fully_frozen" ISNULL AND
1874 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
1877 THEN
1878 -- NOTE: "issue_row" used later
1879 IF "issue_row"."accepted" ISNULL THEN
1880 "issue_row"."state" := 'canceled_revoked_before_accepted';
1881 ELSIF "issue_row"."half_frozen" ISNULL THEN
1882 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
1883 ELSE
1884 "issue_row"."state" := 'canceled_after_revocation_during_verification';
1885 END IF;
1886 "issue_row"."closed" := now();
1887 UPDATE "issue" SET
1888 "state" = "issue_row"."state",
1889 "closed" = "issue_row"."closed"
1890 WHERE "id" = "issue_row"."id";
1891 END IF;
1892 -- fully freeze issue after verification time:
1893 IF
1894 "issue_row"."half_frozen" NOTNULL AND
1895 "issue_row"."fully_frozen" ISNULL AND
1896 "issue_row"."closed" ISNULL AND
1897 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
1898 THEN
1899 PERFORM "freeze_after_snapshot"("issue_id_p");
1900 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
1901 END IF;
1902 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1903 -- close issue by calling close_voting(...) after voting time:
1904 IF
1905 "issue_row"."closed" ISNULL AND
1906 "issue_row"."fully_frozen" NOTNULL AND
1907 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
1908 THEN
1909 PERFORM "close_voting"("issue_id_p");
1910 -- calculate ranks will not consume much time and can be done now
1911 PERFORM "calculate_ranks"("issue_id_p");
1912 END IF;
1913 END IF;
1914 RETURN;
1915 END;
1916 $$;
1918 CREATE OR REPLACE FUNCTION "delete_private_data"()
1919 RETURNS VOID
1920 LANGUAGE 'plpgsql' VOLATILE AS $$
1921 BEGIN
1922 UPDATE "member" SET
1923 "last_login" = NULL,
1924 "login" = NULL,
1925 "password" = NULL,
1926 "notify_email" = NULL,
1927 "notify_email_unconfirmed" = NULL,
1928 "notify_email_secret" = NULL,
1929 "notify_email_secret_expiry" = NULL,
1930 "notify_email_lock_expiry" = NULL,
1931 "password_reset_secret" = NULL,
1932 "password_reset_secret_expiry" = NULL,
1933 "organizational_unit" = NULL,
1934 "internal_posts" = NULL,
1935 "realname" = NULL,
1936 "birthday" = NULL,
1937 "address" = NULL,
1938 "email" = NULL,
1939 "xmpp_address" = NULL,
1940 "website" = NULL,
1941 "phone" = NULL,
1942 "mobile_phone" = NULL,
1943 "profession" = NULL,
1944 "external_memberships" = NULL,
1945 "external_posts" = NULL,
1946 "statement" = NULL;
1947 -- "text_search_data" is updated by triggers
1948 DELETE FROM "invite_code";
1949 DELETE FROM "setting";
1950 DELETE FROM "setting_map";
1951 DELETE FROM "member_relation_setting";
1952 DELETE FROM "member_image";
1953 DELETE FROM "contact";
1954 DELETE FROM "ignored_member";
1955 DELETE FROM "session";
1956 DELETE FROM "area_setting";
1957 DELETE FROM "issue_setting";
1958 DELETE FROM "ignored_initiative";
1959 DELETE FROM "initiative_setting";
1960 DELETE FROM "suggestion_setting";
1961 DELETE FROM "non_voter";
1962 DELETE FROM "direct_voter" USING "issue"
1963 WHERE "direct_voter"."issue_id" = "issue"."id"
1964 AND "issue"."closed" ISNULL;
1965 RETURN;
1966 END;
1967 $$;
1970 -- Delete old "delegation_scope" TYPE:
1972 DROP TYPE "delegation_scope_old";
1975 COMMIT;
1978 -- Generate issue states and add constraints:
1980 UPDATE "issue" SET "state" =
1981 CASE
1982 WHEN "closed" ISNULL THEN
1983 CASE
1984 WHEN "accepted" ISNULL THEN
1985 'admission'::"issue_state"
1986 WHEN "half_frozen" ISNULL THEN
1987 'discussion'::"issue_state"
1988 WHEN "fully_frozen" ISNULL THEN
1989 'verification'::"issue_state"
1990 ELSE
1991 'voting'::"issue_state"
1992 END
1993 WHEN "fully_frozen" NOTNULL THEN
1994 CASE
1995 WHEN "fully_frozen" = "closed" THEN
1996 'canceled_no_initiative_admitted'::"issue_state"
1997 ELSE
1998 'finished_without_winner'::"issue_state" -- NOTE: corrected later
1999 END
2000 WHEN "half_frozen" NOTNULL THEN
2001 'canceled_after_revocation_during_verification'::"issue_state"
2002 WHEN "accepted" NOTNULL THEN
2003 'canceled_after_revocation_during_discussion'::"issue_state"
2004 ELSE
2005 'canceled_revoked_before_accepted'::"issue_state" -- NOTE: corrected later
2006 END;
2007 UPDATE "issue" SET "state" = 'finished_with_winner'
2008 FROM "initiative"
2009 WHERE "issue"."id" = "initiative"."issue_id"
2010 AND "issue"."state" = 'finished_without_winner'
2011 AND "initiative"."agreed";
2012 UPDATE "issue" SET "state" = 'canceled_issue_not_accepted'
2013 FROM "initiative"
2014 WHERE "issue"."id" = "initiative"."issue_id"
2015 AND "issue"."state" = 'canceled_revoked_before_accepted'
2016 AND "initiative"."revoked" ISNULL;
2018 ALTER TABLE "issue" ALTER "state" SET NOT NULL;
2020 ALTER TABLE "issue" DROP CONSTRAINT "valid_state";
2021 ALTER TABLE "issue" ADD CONSTRAINT "valid_state" CHECK ((
2022 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
2023 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
2024 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
2025 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
2026 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
2027 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
2028 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
2029 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
2030 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)
2031 ) AND (
2032 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
2033 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
2034 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
2035 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
2036 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
2037 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
2038 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
2039 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
2040 ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
2041 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
2042 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
2043 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
2044 ));
2047 -- Guess "revoked_by_member_id" values based on author of current draft and add constraint:
2049 UPDATE "initiative" SET "revoked_by_member_id" = "author_id"
2050 FROM "current_draft"
2051 WHERE "initiative"."id" = "current_draft"."initiative_id"
2052 AND "initiative"."revoked" NOTNULL;
2054 ALTER TABLE "initiative" ADD
2055 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
2056 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL);
2059 -- Fill "unit_id" column with default value where neccessary and add constraints:
2061 UPDATE "delegation" SET "unit_id" = 1 WHERE "scope" = 'unit';
2063 ALTER TABLE "delegation" ADD CONSTRAINT "area_id_and_issue_id_set_according_to_scope"
2064 CHECK (
2065 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
2066 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
2067 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) );
2070 -- Filling of "event" table with old (reconstructed) events:
2072 DELETE FROM "event";
2073 SELECT setval('event_id_seq', 1, false);
2075 INSERT INTO "event"
2076 ( "occurrence", "event", "member_id", "issue_id", "state",
2077 "initiative_id", "draft_id", "suggestion_id" )
2078 SELECT * FROM (
2079 SELECT * FROM (
2080 SELECT DISTINCT ON ("initiative"."id")
2081 "timeline"."occurrence",
2082 CASE WHEN "issue_creation"."issue_id" NOTNULL THEN
2083 'initiative_created_in_new_issue'::"event_type"
2084 ELSE
2085 'initiative_created_in_existing_issue'::"event_type"
2086 END,
2087 "draft"."author_id",
2088 "issue"."id",
2089 CASE
2090 WHEN "timeline"."occurrence" < "issue"."accepted" THEN
2091 'admission'::"issue_state"
2092 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
2093 'discussion'::"issue_state"
2094 ELSE
2095 'verification'::"issue_state"
2096 END,
2097 "initiative"."id",
2098 "draft"."id",
2099 NULL::INT8
2100 FROM "timeline"
2101 JOIN "initiative" ON "timeline"."initiative_id" = "initiative"."id"
2102 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2103 LEFT JOIN "timeline" AS "issue_creation"
2104 ON "initiative"."issue_id" = "issue_creation"."issue_id"
2105 AND "issue_creation"."event" = 'issue_created'
2106 AND "timeline"."occurrence" = "issue_creation"."occurrence"
2107 JOIN "draft"
2108 ON "initiative"."id" = "draft"."initiative_id"
2109 WHERE "timeline"."event" = 'initiative_created'
2110 ORDER BY "initiative"."id", "draft"."id"
2111 ) AS "subquery" -- NOTE: subquery needed due to DISTINCT/ORDER
2112 UNION ALL
2113 SELECT
2114 "timeline"."occurrence",
2115 'issue_state_changed'::"event_type",
2116 NULL,
2117 "issue"."id",
2118 CASE
2119 WHEN "timeline"."event" IN (
2120 'issue_canceled',
2121 'issue_finished_without_voting',
2122 'issue_finished_after_voting'
2123 ) THEN
2124 "issue"."state"
2125 WHEN "timeline"."event" = 'issue_accepted' THEN
2126 'discussion'::"issue_state"
2127 WHEN "timeline"."event" = 'issue_half_frozen' THEN
2128 'verification'::"issue_state"
2129 WHEN "timeline"."event" = 'issue_voting_started' THEN
2130 'voting'::"issue_state"
2131 END,
2132 NULL,
2133 NULL,
2134 NULL
2135 FROM "timeline"
2136 JOIN "issue" ON "timeline"."issue_id" = "issue"."id"
2137 WHERE "timeline"."event" IN (
2138 'issue_canceled',
2139 'issue_accepted',
2140 'issue_half_frozen',
2141 'issue_finished_without_voting',
2142 'issue_voting_started',
2143 'issue_finished_after_voting' )
2144 UNION ALL
2145 SELECT
2146 "timeline"."occurrence",
2147 'initiative_revoked'::"event_type",
2148 "initiative"."revoked_by_member_id",
2149 "issue"."id",
2150 CASE
2151 WHEN "timeline"."occurrence" < "issue"."accepted" THEN
2152 'admission'::"issue_state"
2153 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
2154 'discussion'::"issue_state"
2155 ELSE
2156 'verification'::"issue_state"
2157 END,
2158 "initiative"."id",
2159 "current_draft"."id",
2160 NULL
2161 FROM "timeline"
2162 JOIN "initiative" ON "timeline"."initiative_id" = "initiative"."id"
2163 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2164 JOIN "current_draft" ON "initiative"."id" = "current_draft"."initiative_id"
2165 WHERE "timeline"."event" = 'initiative_revoked'
2166 UNION ALL
2167 SELECT
2168 "timeline"."occurrence",
2169 'new_draft_created'::"event_type",
2170 "draft"."author_id",
2171 "issue"."id",
2172 CASE
2173 WHEN "timeline"."occurrence" < "issue"."accepted" THEN
2174 'admission'::"issue_state"
2175 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
2176 'discussion'::"issue_state"
2177 ELSE
2178 'verification'::"issue_state"
2179 END,
2180 "initiative"."id",
2181 "draft"."id",
2182 NULL
2183 FROM "timeline"
2184 JOIN "draft" ON "timeline"."draft_id" = "draft"."id"
2185 JOIN "initiative" ON "draft"."initiative_id" = "initiative"."id"
2186 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
2187 LEFT JOIN "timeline" AS "initiative_creation"
2188 ON "initiative"."id" = "initiative_creation"."initiative_id"
2189 AND "initiative_creation"."event" = 'initiative_created'
2190 AND "timeline"."occurrence" = "initiative_creation"."occurrence"
2191 WHERE "timeline"."event" = 'draft_created'
2192 AND "initiative_creation"."initiative_id" ISNULL
2193 UNION ALL
2194 SELECT
2195 "timeline"."occurrence",
2196 'suggestion_created'::"event_type",
2197 "suggestion"."author_id",
2198 "issue"."id",
2199 CASE
2200 WHEN "timeline"."occurrence" < "issue"."accepted" THEN
2201 'admission'::"issue_state"
2202 WHEN "timeline"."occurrence" < "issue"."half_frozen" THEN
2203 'discussion'::"issue_state"
2204 ELSE
2205 'verification'::"issue_state"
2206 END,
2207 "initiative"."id",
2208 NULL,
2209 "suggestion"."id"
2210 FROM "timeline"
2211 JOIN "suggestion" ON "timeline"."suggestion_id" = "suggestion"."id"
2212 JOIN "initiative" ON "suggestion"."initiative_id" = "initiative"."id"
2213 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
2214 WHERE "timeline"."event" = 'suggestion_created'
2215 ) AS "subquery"
2216 ORDER BY "occurrence";

Impressum / About Us