liquid_feedback_core
view update/core-update.v1.3.1-v1.4.0_rc1.sql @ 116:1cec513e27ac
Fixed file name of SQL update script (v1.3.1(!) to v1.4.0_rc1)
| author | jbe |
|---|---|
| date | Mon Mar 07 03:50:00 2011 +0100 (2011-03-07) |
| parents | update/core-update.v1.3.0-v1.4.0_rc1.sql@30e0200f82e9 |
| children | a5d39efbfe5b |
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 -- TODO: Table is filled after this transaction (see below)
141 -- New table "privilege":
143 CREATE TABLE "privilege" (
144 PRIMARY KEY ("unit_id", "member_id"),
145 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
146 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
147 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
148 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
149 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
150 "voting_right_manager" BOOLEAN NOT NULL DEFAULT FALSE,
151 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE );
153 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
155 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other users';
156 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create or lock sub units';
157 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create or lock areas and set area parameters';
158 COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote inside the unit';
159 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to discuss and vote';
162 -- Remove table "ignored_issue", which is no longer existent:
164 DROP TABLE "ignored_issue";
167 -- Replace TYPE "delegation_scope" with a new type, where 'global' is replaced by 'unit':
169 ALTER TYPE "delegation_scope" RENAME TO "delegation_scope_old"; -- NOTE: dropped later
170 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
171 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
174 -- Delete views and functions being dependent on TYPE "delegation_scope":
176 DROP FUNCTION "delegation_chain"
177 ( "member_id_p" "member"."id"%TYPE,
178 "area_id_p" "area"."id"%TYPE,
179 "issue_id_p" "issue"."id"%TYPE );
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,
185 "simulate_trustee_id_p" "member"."id"%TYPE );
187 DROP TYPE "delegation_chain_row";
189 DROP VIEW "issue_delegation";
190 DROP VIEW "area_delegation";
191 DROP VIEW "global_delegation";
192 DROP VIEW "active_delegation";
195 -- Modify "delegation" table to use new "delegation_scope" TYPE:
197 ALTER TABLE "delegation" DROP CONSTRAINT "no_global_delegation_to_null";
198 ALTER TABLE "delegation" DROP CONSTRAINT "area_id_and_issue_id_set_according_to_scope";
200 DROP INDEX "delegation_global_truster_id_unique_idx";
202 ALTER TABLE "delegation" ALTER "scope" TYPE "delegation_scope"
203 USING CASE WHEN "scope" = 'global'
204 THEN 'unit'::"delegation_scope"
205 ELSE "scope"::text::"delegation_scope" END;
207 ALTER TABLE "delegation" ADD "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
209 ALTER TABLE "delegation" ADD CONSTRAINT "no_unit_delegation_to_null"
210 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit');
212 ALTER TABLE "delegation" ADD UNIQUE ("unit_id", "truster_id");
214 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
216 -- NOTE: Column "unit_id" filled after transaction (see below)
219 -- Modify snapshot tables to use new "delegation_scope" TYPE:
221 ALTER TABLE "delegating_population_snapshot" ALTER "scope" TYPE "delegation_scope"
222 USING CASE WHEN "scope" = 'global'
223 THEN 'unit'::"delegation_scope"
224 ELSE "scope"::text::"delegation_scope" END;
226 ALTER TABLE "delegating_interest_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_voter" ALTER "scope" TYPE "delegation_scope"
232 USING CASE WHEN "scope" = 'global'
233 THEN 'unit'::"delegation_scope"
234 ELSE "scope"::text::"delegation_scope" END;
237 -- New table "non_voter":
239 CREATE TABLE "non_voter" (
240 PRIMARY KEY ("issue_id", "member_id"),
241 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
242 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
243 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
245 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
248 -- New table "issue_comment":
250 CREATE TABLE "issue_comment" (
251 PRIMARY KEY ("issue_id", "member_id"),
252 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
253 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
254 "changed" TIMESTAMPTZ NOT NULL DEFAULT now(),
255 "formatting_engine" TEXT,
256 "content" TEXT NOT NULL,
257 "text_search_data" TSVECTOR );
258 CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id");
259 CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data");
260 CREATE TRIGGER "update_text_search_data"
261 BEFORE INSERT OR UPDATE ON "issue_comment"
262 FOR EACH ROW EXECUTE PROCEDURE
263 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
265 COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues';
267 COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed';
270 -- New table "rendered_issue_comment":
272 CREATE TABLE "rendered_issue_comment" (
273 PRIMARY KEY ("issue_id", "member_id", "format"),
274 FOREIGN KEY ("issue_id", "member_id")
275 REFERENCES "issue_comment" ("issue_id", "member_id")
276 ON DELETE CASCADE ON UPDATE CASCADE,
277 "issue_id" INT4,
278 "member_id" INT4,
279 "format" TEXT,
280 "content" TEXT NOT NULL );
282 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)';
285 -- New table "voting_comment":
287 CREATE TABLE "voting_comment" (
288 PRIMARY KEY ("issue_id", "member_id"),
289 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
290 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
291 "changed" TIMESTAMPTZ,
292 "formatting_engine" TEXT,
293 "content" TEXT NOT NULL,
294 "text_search_data" TSVECTOR );
295 CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");
296 CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");
297 CREATE TRIGGER "update_text_search_data"
298 BEFORE INSERT OR UPDATE ON "voting_comment"
299 FOR EACH ROW EXECUTE PROCEDURE
300 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
302 COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';
304 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.';
307 -- New table "rendered_voting_comment":
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", "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 -- Modified core 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 '{}'
1112 )
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 '{}'
1164 )
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 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
1284 )
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"
1349 )
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 ),
1370 0
1371 ) AS "positive_votes",
1372 coalesce(
1373 sum(
1374 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
1375 ),
1376 0
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 NOT EXISTS (
1577 SELECT NULL FROM "initiative"
1578 WHERE "issue_id" = "issue_id_p" AND "admitted"
1579 ) THEN
1580 'canceled_no_initiative_admitted'::"issue_state"
1581 ELSE
1582 CASE WHEN "dimension_v" = 0 THEN
1583 'finished_without_winner'::"issue_state"
1584 ELSE
1585 'finished_with_winner'::"issue_state"
1586 END
1587 END,
1588 "ranks_available" = TRUE
1589 WHERE "id" = "issue_id_p";
1590 RETURN;
1591 END;
1592 $$;
1594 CREATE OR REPLACE FUNCTION "check_issue"
1595 ( "issue_id_p" "issue"."id"%TYPE )
1596 RETURNS VOID
1597 LANGUAGE 'plpgsql' VOLATILE AS $$
1598 DECLARE
1599 "issue_row" "issue"%ROWTYPE;
1600 "policy_row" "policy"%ROWTYPE;
1601 "voting_requested_v" BOOLEAN;
1602 BEGIN
1603 PERFORM "lock_issue"("issue_id_p");
1604 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1605 -- only process open issues:
1606 IF "issue_row"."closed" ISNULL THEN
1607 SELECT * INTO "policy_row" FROM "policy"
1608 WHERE "id" = "issue_row"."policy_id";
1609 -- create a snapshot, unless issue is already fully frozen:
1610 IF "issue_row"."fully_frozen" ISNULL THEN
1611 PERFORM "create_snapshot"("issue_id_p");
1612 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1613 END IF;
1614 -- eventually close or accept issues, which have not been accepted:
1615 IF "issue_row"."accepted" ISNULL THEN
1616 IF EXISTS (
1617 SELECT NULL FROM "initiative"
1618 WHERE "issue_id" = "issue_id_p"
1619 AND "supporter_count" > 0
1620 AND "supporter_count" * "policy_row"."issue_quorum_den"
1621 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
1622 ) THEN
1623 -- accept issues, if supporter count is high enough
1624 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
1625 -- NOTE: "issue_row" used later
1626 "issue_row"."state" := 'discussion';
1627 "issue_row"."accepted" := now();
1628 UPDATE "issue" SET
1629 "state" = "issue_row"."state",
1630 "accepted" = "issue_row"."accepted"
1631 WHERE "id" = "issue_row"."id";
1632 ELSIF
1633 now() >= "issue_row"."created" + "issue_row"."admission_time"
1634 THEN
1635 -- close issues, if admission time has expired
1636 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
1637 UPDATE "issue" SET
1638 "state" = 'canceled_issue_not_accepted',
1639 "closed" = now()
1640 WHERE "id" = "issue_row"."id";
1641 END IF;
1642 END IF;
1643 -- eventually half freeze issues:
1644 IF
1645 -- NOTE: issue can't be closed at this point, if it has been accepted
1646 "issue_row"."accepted" NOTNULL AND
1647 "issue_row"."half_frozen" ISNULL
1648 THEN
1649 SELECT
1650 CASE
1651 WHEN "vote_now" * 2 > "issue_row"."population" THEN
1652 TRUE
1653 WHEN "vote_later" * 2 > "issue_row"."population" THEN
1654 FALSE
1655 ELSE NULL
1656 END
1657 INTO "voting_requested_v"
1658 FROM "issue" WHERE "id" = "issue_id_p";
1659 IF
1660 "voting_requested_v" OR (
1661 "voting_requested_v" ISNULL AND
1662 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
1663 )
1664 THEN
1665 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
1666 -- NOTE: "issue_row" used later
1667 "issue_row"."state" := 'verification';
1668 "issue_row"."half_frozen" := now();
1669 UPDATE "issue" SET
1670 "state" = "issue_row"."state",
1671 "half_frozen" = "issue_row"."half_frozen"
1672 WHERE "id" = "issue_row"."id";
1673 END IF;
1674 END IF;
1675 -- close issues after some time, if all initiatives have been revoked:
1676 IF
1677 "issue_row"."closed" ISNULL AND
1678 NOT EXISTS (
1679 -- all initiatives are revoked
1680 SELECT NULL FROM "initiative"
1681 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
1682 ) AND (
1683 -- and issue has not been accepted yet
1684 "issue_row"."accepted" ISNULL OR
1685 NOT EXISTS (
1686 -- or no initiatives have been revoked lately
1687 SELECT NULL FROM "initiative"
1688 WHERE "issue_id" = "issue_id_p"
1689 AND now() < "revoked" + "issue_row"."verification_time"
1690 ) OR (
1691 -- or verification time has elapsed
1692 "issue_row"."half_frozen" NOTNULL AND
1693 "issue_row"."fully_frozen" ISNULL AND
1694 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
1695 )
1696 )
1697 THEN
1698 -- NOTE: "issue_row" used later
1699 IF "issue_row"."accepted" ISNULL THEN
1700 "issue_row"."state" := 'canceled_revoked_before_accepted';
1701 ELSIF "issue_row"."half_frozen" ISNULL THEN
1702 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
1703 ELSE
1704 "issue_row"."state" := 'canceled_after_revocation_during_verification';
1705 END IF;
1706 "issue_row"."closed" := now();
1707 UPDATE "issue" SET
1708 "state" = "issue_row"."state",
1709 "closed" = "issue_row"."closed"
1710 WHERE "id" = "issue_row"."id";
1711 END IF;
1712 -- fully freeze issue after verification time:
1713 IF
1714 "issue_row"."half_frozen" NOTNULL AND
1715 "issue_row"."fully_frozen" ISNULL AND
1716 "issue_row"."closed" ISNULL AND
1717 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
1718 THEN
1719 PERFORM "freeze_after_snapshot"("issue_id_p");
1720 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
1721 END IF;
1722 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1723 -- close issue by calling close_voting(...) after voting time:
1724 IF
1725 "issue_row"."closed" ISNULL AND
1726 "issue_row"."fully_frozen" NOTNULL AND
1727 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
1728 THEN
1729 PERFORM "close_voting"("issue_id_p");
1730 -- calculate ranks will not consume much time and can be done now
1731 PERFORM "calculate_ranks"("issue_id_p");
1732 END IF;
1733 END IF;
1734 RETURN;
1735 END;
1736 $$;
1738 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
1739 RETURNS VOID
1740 LANGUAGE 'plpgsql' VOLATILE AS $$
1741 DECLARE
1742 "issue_row" "issue"%ROWTYPE;
1743 BEGIN
1744 SELECT * INTO "issue_row"
1745 FROM "issue" WHERE "id" = "issue_id_p"
1746 FOR UPDATE;
1747 IF "issue_row"."cleaned" ISNULL THEN
1748 UPDATE "issue" SET
1749 "closed" = NULL,
1750 "ranks_available" = FALSE
1751 WHERE "id" = "issue_id_p";
1752 DELETE FROM "delegating_voter"
1753 WHERE "issue_id" = "issue_id_p";
1754 DELETE FROM "direct_voter"
1755 WHERE "issue_id" = "issue_id_p";
1756 DELETE FROM "delegating_interest_snapshot"
1757 WHERE "issue_id" = "issue_id_p";
1758 DELETE FROM "direct_interest_snapshot"
1759 WHERE "issue_id" = "issue_id_p";
1760 DELETE FROM "delegating_population_snapshot"
1761 WHERE "issue_id" = "issue_id_p";
1762 DELETE FROM "direct_population_snapshot"
1763 WHERE "issue_id" = "issue_id_p";
1764 DELETE FROM "non_voter"
1765 WHERE "issue_id" = "issue_id_p";
1766 DELETE FROM "delegation"
1767 WHERE "issue_id" = "issue_id_p";
1768 DELETE FROM "supporter"
1769 WHERE "issue_id" = "issue_id_p";
1770 UPDATE "issue" SET
1771 "closed" = "issue_row"."closed",
1772 "ranks_available" = "issue_row"."ranks_available",
1773 "cleaned" = now()
1774 WHERE "id" = "issue_id_p";
1775 END IF;
1776 RETURN;
1777 END;
1778 $$;
1780 CREATE OR REPLACE FUNCTION "check_issue"
1781 ( "issue_id_p" "issue"."id"%TYPE )
1782 RETURNS VOID
1783 LANGUAGE 'plpgsql' VOLATILE AS $$
1784 DECLARE
1785 "issue_row" "issue"%ROWTYPE;
1786 "policy_row" "policy"%ROWTYPE;
1787 "voting_requested_v" BOOLEAN;
1788 BEGIN
1789 PERFORM "lock_issue"("issue_id_p");
1790 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1791 -- only process open issues:
1792 IF "issue_row"."closed" ISNULL THEN
1793 SELECT * INTO "policy_row" FROM "policy"
1794 WHERE "id" = "issue_row"."policy_id";
1795 -- create a snapshot, unless issue is already fully frozen:
1796 IF "issue_row"."fully_frozen" ISNULL THEN
1797 PERFORM "create_snapshot"("issue_id_p");
1798 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1799 END IF;
1800 -- eventually close or accept issues, which have not been accepted:
1801 IF "issue_row"."accepted" ISNULL THEN
1802 IF EXISTS (
1803 SELECT NULL FROM "initiative"
1804 WHERE "issue_id" = "issue_id_p"
1805 AND "supporter_count" > 0
1806 AND "supporter_count" * "policy_row"."issue_quorum_den"
1807 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
1808 ) THEN
1809 -- accept issues, if supporter count is high enough
1810 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
1811 -- NOTE: "issue_row" used later
1812 "issue_row"."state" := 'discussion';
1813 "issue_row"."accepted" := now();
1814 UPDATE "issue" SET
1815 "state" = "issue_row"."state",
1816 "accepted" = "issue_row"."accepted"
1817 WHERE "id" = "issue_row"."id";
1818 ELSIF
1819 now() >= "issue_row"."created" + "issue_row"."admission_time"
1820 THEN
1821 -- close issues, if admission time has expired
1822 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
1823 UPDATE "issue" SET
1824 "state" = 'canceled_issue_not_accepted',
1825 "closed" = now()
1826 WHERE "id" = "issue_row"."id";
1827 END IF;
1828 END IF;
1829 -- eventually half freeze issues:
1830 IF
1831 -- NOTE: issue can't be closed at this point, if it has been accepted
1832 "issue_row"."accepted" NOTNULL AND
1833 "issue_row"."half_frozen" ISNULL
1834 THEN
1835 SELECT
1836 CASE
1837 WHEN "vote_now" * 2 > "issue_row"."population" THEN
1838 TRUE
1839 WHEN "vote_later" * 2 > "issue_row"."population" THEN
1840 FALSE
1841 ELSE NULL
1842 END
1843 INTO "voting_requested_v"
1844 FROM "issue" WHERE "id" = "issue_id_p";
1845 IF
1846 "voting_requested_v" OR (
1847 "voting_requested_v" ISNULL AND
1848 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
1849 )
1850 THEN
1851 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
1852 -- NOTE: "issue_row" used later
1853 "issue_row"."state" := 'verification';
1854 "issue_row"."half_frozen" := now();
1855 UPDATE "issue" SET
1856 "state" = "issue_row"."state",
1857 "half_frozen" = "issue_row"."half_frozen"
1858 WHERE "id" = "issue_row"."id";
1859 END IF;
1860 END IF;
1861 -- close issues after some time, if all initiatives have been revoked:
1862 IF
1863 "issue_row"."closed" ISNULL AND
1864 NOT EXISTS (
1865 -- all initiatives are revoked
1866 SELECT NULL FROM "initiative"
1867 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
1868 ) AND (
1869 -- and issue has not been accepted yet
1870 "issue_row"."accepted" ISNULL OR
1871 NOT EXISTS (
1872 -- or no initiatives have been revoked lately
1873 SELECT NULL FROM "initiative"
1874 WHERE "issue_id" = "issue_id_p"
1875 AND now() < "revoked" + "issue_row"."verification_time"
1876 ) OR (
1877 -- or verification time has elapsed
1878 "issue_row"."half_frozen" NOTNULL AND
1879 "issue_row"."fully_frozen" ISNULL AND
1880 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
1881 )
1882 )
1883 THEN
1884 -- NOTE: "issue_row" used later
1885 IF "issue_row"."accepted" ISNULL THEN
1886 "issue_row"."state" := 'canceled_revoked_before_accepted';
1887 ELSIF "issue_row"."half_frozen" ISNULL THEN
1888 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
1889 ELSE
1890 "issue_row"."state" := 'canceled_after_revocation_during_verification';
1891 END IF;
1892 "issue_row"."closed" := now();
1893 UPDATE "issue" SET
1894 "state" = "issue_row"."state",
1895 "closed" = "issue_row"."closed"
1896 WHERE "id" = "issue_row"."id";
1897 END IF;
1898 -- fully freeze issue after verification time:
1899 IF
1900 "issue_row"."half_frozen" NOTNULL AND
1901 "issue_row"."fully_frozen" ISNULL AND
1902 "issue_row"."closed" ISNULL AND
1903 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
1904 THEN
1905 PERFORM "freeze_after_snapshot"("issue_id_p");
1906 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
1907 END IF;
1908 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1909 -- close issue by calling close_voting(...) after voting time:
1910 IF
1911 "issue_row"."closed" ISNULL AND
1912 "issue_row"."fully_frozen" NOTNULL AND
1913 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
1914 THEN
1915 PERFORM "close_voting"("issue_id_p");
1916 -- calculate ranks will not consume much time and can be done now
1917 PERFORM "calculate_ranks"("issue_id_p");
1918 END IF;
1919 END IF;
1920 RETURN;
1921 END;
1922 $$;
1924 CREATE OR REPLACE FUNCTION "delete_private_data"()
1925 RETURNS VOID
1926 LANGUAGE 'plpgsql' VOLATILE AS $$
1927 BEGIN
1928 UPDATE "member" SET
1929 "last_login" = NULL,
1930 "login" = NULL,
1931 "password" = NULL,
1932 "notify_email" = NULL,
1933 "notify_email_unconfirmed" = NULL,
1934 "notify_email_secret" = NULL,
1935 "notify_email_secret_expiry" = NULL,
1936 "notify_email_lock_expiry" = NULL,
1937 "password_reset_secret" = NULL,
1938 "password_reset_secret_expiry" = NULL,
1939 "organizational_unit" = NULL,
1940 "internal_posts" = NULL,
1941 "realname" = NULL,
1942 "birthday" = NULL,
1943 "address" = NULL,
1944 "email" = NULL,
1945 "xmpp_address" = NULL,
1946 "website" = NULL,
1947 "phone" = NULL,
1948 "mobile_phone" = NULL,
1949 "profession" = NULL,
1950 "external_memberships" = NULL,
1951 "external_posts" = NULL,
1952 "statement" = NULL;
1953 -- "text_search_data" is updated by triggers
1954 DELETE FROM "invite_code";
1955 DELETE FROM "setting";
1956 DELETE FROM "setting_map";
1957 DELETE FROM "member_relation_setting";
1958 DELETE FROM "member_image";
1959 DELETE FROM "contact";
1960 DELETE FROM "ignored_member";
1961 DELETE FROM "session";
1962 DELETE FROM "area_setting";
1963 DELETE FROM "issue_setting";
1964 DELETE FROM "ignored_initiative";
1965 DELETE FROM "initiative_setting";
1966 DELETE FROM "suggestion_setting";
1967 DELETE FROM "non_voter";
1968 DELETE FROM "direct_voter" USING "issue"
1969 WHERE "direct_voter"."issue_id" = "issue"."id"
1970 AND "issue"."closed" ISNULL;
1971 RETURN;
1972 END;
1973 $$;
1976 -- Delete old "delegation_scope" TYPE:
1978 DROP TYPE "delegation_scope_old";
1981 COMMIT;
1984 -- Generate issue states:
1986 UPDATE "issue" SET "state" =
1987 CASE
1988 WHEN "closed" ISNULL THEN
1989 CASE
1990 WHEN "accepted" ISNULL THEN
1991 'admission'::"issue_state"
1992 WHEN "half_frozen" ISNULL THEN
1993 'discussion'::"issue_state"
1994 WHEN "fully_frozen" ISNULL THEN
1995 'verification'::"issue_state"
1996 ELSE
1997 'voting'::"issue_state"
1998 END
1999 WHEN "fully_frozen" NOTNULL THEN
2000 CASE
2001 WHEN "fully_frozen" = "closed" THEN
2002 'canceled_no_initiative_admitted'::"issue_state"
2003 ELSE
2004 'finished_without_winner'::"issue_state" -- NOTE: corrected later
2005 END
2006 WHEN "half_frozen" NOTNULL THEN
2007 'canceled_after_revocation_during_verification'::"issue_state"
2008 WHEN "accepted" NOTNULL THEN
2009 'canceled_after_revocation_during_discussion'::"issue_state"
2010 ELSE
2011 'canceled_revoked_before_accepted'::"issue_state" -- NOTE: corrected later
2012 END;
2014 ALTER TABLE "issue" ALTER "state" SET NOT NULL;
2016 ALTER TABLE "issue" DROP CONSTRAINT "valid_state";
2017 ALTER TABLE "issue" ADD CONSTRAINT "valid_state" CHECK ((
2018 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
2019 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
2020 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
2021 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
2022 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
2023 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
2024 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
2025 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
2026 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)
2027 ) AND (
2028 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
2029 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
2030 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
2031 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
2032 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
2033 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
2034 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
2035 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
2036 ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
2037 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
2038 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
2039 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
2040 ));
2042 UPDATE "issue" SET "state" = 'finished_with_winner'
2043 FROM "initiative"
2044 WHERE "issue"."id" = "initiative"."issue_id"
2045 AND "issue"."state" = 'finished_without_winner'
2046 AND "initiative"."agreed";
2047 UPDATE "issue" SET "state" = 'canceled_issue_not_accepted'
2048 FROM "initiative"
2049 WHERE "issue"."id" = "initiative"."issue_id"
2050 AND "issue"."state" = 'canceled_revoked_before_accepted'
2051 AND "initiative"."revoked" ISNULL;
2054 -- Guess "revoked_by_member_id" values based on author of current draft:
2056 UPDATE "initiative" SET "revoked_by_member_id" = "author_id"
2057 FROM "current_draft"
2058 WHERE "initiative"."id" = "current_draft"."initiative_id"
2059 AND "initiative"."revoked" NOTNULL;
2061 ALTER TABLE "initiative" ADD
2062 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
2063 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL);
2066 -- Attach existing and unused invite codes with unit id 1:
2068 INSERT INTO "invite_code_unit" ("invite_code_id", "unit_id")
2069 SELECT "id" AS "invite_code_id", 1 AS "unit_id"
2070 FROM "invite_code" WHERE "used" ISNULL;
2073 -- Fill "unit_id" column with default value where neccessary:
2075 UPDATE "delegation" SET "unit_id" = 1 WHERE "scope" = 'unit';
2077 ALTER TABLE "delegation" ADD CONSTRAINT "area_id_and_issue_id_set_according_to_scope"
2078 CHECK (
2079 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
2080 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
2081 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) );