liquid_feedback_core

view update/core-update.v1.3.1-v1.4.0_rc1.sql @ 124:0144b703b261

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

Impressum / About Us