liquid_feedback_core
view core.sql @ 199:859d82b14c38
Added missing core-update.v1.4.0_rc4-v1.4.0.sql file for v1.4.0
| author | jbe |
|---|---|
| date | Sun Sep 11 01:43:36 2011 +0200 (2011-09-11) |
| parents | d53e276f32ba |
| children | e8c6035b03f3 |
line source
2 -- Execute the following command manually for PostgreSQL prior version 9.0:
3 -- CREATE LANGUAGE plpgsql;
5 -- NOTE: In PostgreSQL every UNIQUE constraint implies creation of an index
7 BEGIN;
9 CREATE VIEW "liquid_feedback_version" AS
10 SELECT * FROM (VALUES ('1.4.0', 1, 4, 0))
11 AS "subquery"("string", "major", "minor", "revision");
15 ----------------------
16 -- Full text search --
17 ----------------------
20 CREATE FUNCTION "text_search_query"("query_text_p" TEXT)
21 RETURNS TSQUERY
22 LANGUAGE 'plpgsql' IMMUTABLE AS $$
23 BEGIN
24 RETURN plainto_tsquery('pg_catalog.simple', "query_text_p");
25 END;
26 $$;
28 COMMENT ON FUNCTION "text_search_query"(TEXT) IS 'Usage: WHERE "text_search_data" @@ "text_search_query"(''<user query>'')';
31 CREATE FUNCTION "highlight"
32 ( "body_p" TEXT,
33 "query_text_p" TEXT )
34 RETURNS TEXT
35 LANGUAGE 'plpgsql' IMMUTABLE AS $$
36 BEGIN
37 RETURN ts_headline(
38 'pg_catalog.simple',
39 replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'),
40 "text_search_query"("query_text_p"),
41 'StartSel=* StopSel=* HighlightAll=TRUE' );
42 END;
43 $$;
45 COMMENT ON FUNCTION "highlight"
46 ( "body_p" TEXT,
47 "query_text_p" TEXT )
48 IS 'For a given a user query this function encapsulates all matches with asterisks. Asterisks and backslashes being already present are preceeded with one extra backslash.';
52 -------------------------
53 -- Tables and indicies --
54 -------------------------
57 CREATE TABLE "system_setting" (
58 "member_ttl" INTERVAL );
59 CREATE UNIQUE INDEX "system_setting_singleton_idx" ON "system_setting" ((1));
61 COMMENT ON TABLE "system_setting" IS 'This table contains only one row with different settings in each column.';
62 COMMENT ON INDEX "system_setting_singleton_idx" IS 'This index ensures that "system_setting" only contains one row maximum.';
64 COMMENT ON COLUMN "system_setting"."member_ttl" IS 'Time after members get their "active" flag set to FALSE, if they do not login anymore.';
67 CREATE TABLE "contingent" (
68 "time_frame" INTERVAL PRIMARY KEY,
69 "text_entry_limit" INT4,
70 "initiative_limit" INT4 );
72 COMMENT ON TABLE "contingent" IS 'Amount of text entries or initiatives a user may create within a given time frame. Only one row needs to be fulfilled for a member to be allowed to post. This table must not be empty.';
74 COMMENT ON COLUMN "contingent"."text_entry_limit" IS 'Number of new drafts or suggestions to be submitted by each member within the given time frame';
75 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
78 CREATE TYPE "notify_level" AS ENUM
79 ('none', 'voting', 'verification', 'discussion', 'all');
81 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';
84 CREATE TABLE "member" (
85 "id" SERIAL4 PRIMARY KEY,
86 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
87 "last_login" TIMESTAMPTZ,
88 "last_login_public" DATE,
89 "login" TEXT UNIQUE,
90 "password" TEXT,
91 "locked" BOOLEAN NOT NULL DEFAULT FALSE,
92 "active" BOOLEAN NOT NULL DEFAULT TRUE,
93 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
94 "notify_email" TEXT,
95 "notify_email_unconfirmed" TEXT,
96 "notify_email_secret" TEXT UNIQUE,
97 "notify_email_secret_expiry" TIMESTAMPTZ,
98 "notify_email_lock_expiry" TIMESTAMPTZ,
99 "notify_level" "notify_level" NOT NULL DEFAULT 'none',
100 "notify_event_id" INT8,
101 "password_reset_secret" TEXT UNIQUE,
102 "password_reset_secret_expiry" TIMESTAMPTZ,
103 "name" TEXT NOT NULL UNIQUE,
104 "identification" TEXT UNIQUE,
105 "organizational_unit" TEXT,
106 "internal_posts" TEXT,
107 "realname" TEXT,
108 "birthday" DATE,
109 "address" TEXT,
110 "email" TEXT,
111 "xmpp_address" TEXT,
112 "website" TEXT,
113 "phone" TEXT,
114 "mobile_phone" TEXT,
115 "profession" TEXT,
116 "external_memberships" TEXT,
117 "external_posts" TEXT,
118 "statement" TEXT,
119 "text_search_data" TSVECTOR );
120 CREATE INDEX "member_active_idx" ON "member" ("active");
121 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
122 CREATE TRIGGER "update_text_search_data"
123 BEFORE INSERT OR UPDATE ON "member"
124 FOR EACH ROW EXECUTE PROCEDURE
125 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
126 "name", "identification", "organizational_unit", "internal_posts",
127 "realname", "external_memberships", "external_posts", "statement" );
129 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
131 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';
132 COMMENT ON COLUMN "member"."last_login_public" IS 'Date of last login (time stripped for privacy reasons, updated only after day change)';
133 COMMENT ON COLUMN "member"."login" IS 'Login name';
134 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
135 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
136 COMMENT ON COLUMN "member"."active" IS 'Memberships, support and votes are taken into account when corresponding members are marked as active. When the user does not log in for an extended period of time, this flag may be set to FALSE. If the user is not locked, he/she may reset the active flag by logging in.';
137 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
138 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
139 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
140 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
141 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
142 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
143 COMMENT ON COLUMN "member"."notify_level" IS 'Selects which event notifications are to be sent to the "notify_email" mail address';
144 COMMENT ON COLUMN "member"."notify_event_id" IS 'Latest "id" of an "event" the member was notified about';
145 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member';
146 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
147 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
148 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
149 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
150 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
151 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
152 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
153 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system';
156 CREATE TABLE "member_history" (
157 "id" SERIAL8 PRIMARY KEY,
158 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
159 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
160 "active" BOOLEAN NOT NULL,
161 "name" TEXT NOT NULL );
162 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
164 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
166 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
167 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
170 CREATE TABLE "invite_code" (
171 "id" SERIAL8 PRIMARY KEY,
172 "code" TEXT NOT NULL UNIQUE,
173 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
174 "used" TIMESTAMPTZ,
175 "member_id" INT4 UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
176 "comment" TEXT,
177 CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) );
179 COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';
181 COMMENT ON COLUMN "invite_code"."code" IS 'Secret code';
182 COMMENT ON COLUMN "invite_code"."created" IS 'Time of creation of the secret code';
183 COMMENT ON COLUMN "invite_code"."used" IS 'NULL, if not used yet, otherwise tells when this code was used to create a member account';
184 COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code';
185 COMMENT ON COLUMN "invite_code"."comment" IS 'Comment on the code, which is to be used for administrative reasons only';
188 CREATE TABLE "setting" (
189 PRIMARY KEY ("member_id", "key"),
190 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
191 "key" TEXT NOT NULL,
192 "value" TEXT NOT NULL );
193 CREATE INDEX "setting_key_idx" ON "setting" ("key");
195 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
197 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
200 CREATE TABLE "setting_map" (
201 PRIMARY KEY ("member_id", "key", "subkey"),
202 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
203 "key" TEXT NOT NULL,
204 "subkey" TEXT NOT NULL,
205 "value" TEXT NOT NULL );
206 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
208 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
210 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
211 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
212 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
215 CREATE TABLE "member_relation_setting" (
216 PRIMARY KEY ("member_id", "key", "other_member_id"),
217 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
218 "key" TEXT NOT NULL,
219 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
220 "value" TEXT NOT NULL );
222 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
225 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
227 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
230 CREATE TABLE "member_image" (
231 PRIMARY KEY ("member_id", "image_type", "scaled"),
232 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
233 "image_type" "member_image_type",
234 "scaled" BOOLEAN,
235 "content_type" TEXT,
236 "data" BYTEA NOT NULL );
238 COMMENT ON TABLE "member_image" IS 'Images of members';
240 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
243 CREATE TABLE "member_count" (
244 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
245 "total_count" INT4 NOT NULL );
247 COMMENT ON TABLE "member_count" IS 'Contains one row which contains the total count of active(!) members and a timestamp indicating when the total member count and area member counts were calculated';
249 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
250 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
253 CREATE TABLE "contact" (
254 PRIMARY KEY ("member_id", "other_member_id"),
255 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
256 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
257 "public" BOOLEAN NOT NULL DEFAULT FALSE,
258 CONSTRAINT "cant_save_yourself_as_contact"
259 CHECK ("member_id" != "other_member_id") );
260 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
262 COMMENT ON TABLE "contact" IS 'Contact lists';
264 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
265 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
266 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
269 CREATE TABLE "ignored_member" (
270 PRIMARY KEY ("member_id", "other_member_id"),
271 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
272 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
273 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
275 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
277 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';
278 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
281 CREATE TABLE "session" (
282 "ident" TEXT PRIMARY KEY,
283 "additional_secret" TEXT,
284 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
285 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
286 "lang" TEXT );
287 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
289 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
291 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
292 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
293 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
294 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
297 CREATE TABLE "policy" (
298 "id" SERIAL4 PRIMARY KEY,
299 "index" INT4 NOT NULL,
300 "active" BOOLEAN NOT NULL DEFAULT TRUE,
301 "name" TEXT NOT NULL UNIQUE,
302 "description" TEXT NOT NULL DEFAULT '',
303 "admission_time" INTERVAL NOT NULL,
304 "discussion_time" INTERVAL NOT NULL,
305 "verification_time" INTERVAL NOT NULL,
306 "voting_time" INTERVAL NOT NULL,
307 "issue_quorum_num" INT4 NOT NULL,
308 "issue_quorum_den" INT4 NOT NULL,
309 "initiative_quorum_num" INT4 NOT NULL,
310 "initiative_quorum_den" INT4 NOT NULL,
311 "majority_num" INT4 NOT NULL DEFAULT 1,
312 "majority_den" INT4 NOT NULL DEFAULT 2,
313 "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE );
314 CREATE INDEX "policy_active_idx" ON "policy" ("active");
316 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
318 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
319 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
320 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
321 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';
322 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
323 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';
324 COMMENT ON COLUMN "policy"."issue_quorum_num" IS 'Numerator of potential supporter quorum to be reached by one initiative of an issue to be "accepted"';
325 COMMENT ON COLUMN "policy"."issue_quorum_den" IS 'Denominator of potential supporter quorum to be reached by one initiative of an issue to be "accepted"';
326 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
327 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
328 COMMENT ON COLUMN "policy"."majority_num" IS 'Numerator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
329 COMMENT ON COLUMN "policy"."majority_den" IS 'Denominator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
330 COMMENT ON COLUMN "policy"."majority_strict" IS 'If TRUE, then the majority must be strictly greater than "majority_num"/"majority_den", otherwise it may also be equal.';
333 CREATE TABLE "unit" (
334 "id" SERIAL4 PRIMARY KEY,
335 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
336 "active" BOOLEAN NOT NULL DEFAULT TRUE,
337 "name" TEXT NOT NULL,
338 "description" TEXT NOT NULL DEFAULT '',
339 "member_count" INT4,
340 "text_search_data" TSVECTOR );
341 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
342 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
343 CREATE INDEX "unit_active_idx" ON "unit" ("active");
344 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
345 CREATE TRIGGER "update_text_search_data"
346 BEFORE INSERT OR UPDATE ON "unit"
347 FOR EACH ROW EXECUTE PROCEDURE
348 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
349 "name", "description" );
351 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
353 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
354 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in units of this area';
355 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
358 CREATE TABLE "area" (
359 "id" SERIAL4 PRIMARY KEY,
360 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
361 "active" BOOLEAN NOT NULL DEFAULT TRUE,
362 "name" TEXT NOT NULL,
363 "description" TEXT NOT NULL DEFAULT '',
364 "direct_member_count" INT4,
365 "member_weight" INT4,
366 "autoreject_weight" INT4,
367 "text_search_data" TSVECTOR );
368 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
369 CREATE INDEX "area_active_idx" ON "area" ("active");
370 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
371 CREATE TRIGGER "update_text_search_data"
372 BEFORE INSERT OR UPDATE ON "area"
373 FOR EACH ROW EXECUTE PROCEDURE
374 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
375 "name", "description" );
377 COMMENT ON TABLE "area" IS 'Subject areas';
379 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
380 COMMENT ON COLUMN "area"."direct_member_count" IS 'Number of active members of that area (ignoring their weight), as calculated from view "area_member_count"';
381 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
382 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature';
385 CREATE TABLE "area_setting" (
386 PRIMARY KEY ("member_id", "key", "area_id"),
387 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
388 "key" TEXT NOT NULL,
389 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
390 "value" TEXT NOT NULL );
392 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
395 CREATE TABLE "allowed_policy" (
396 PRIMARY KEY ("area_id", "policy_id"),
397 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
398 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
399 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
400 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
402 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
404 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
407 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
409 COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''half_freeze'' = saved state at end of discussion period, ''full_freeze'' = saved state at end of verification period';
412 CREATE TYPE "issue_state" AS ENUM (
413 'admission', 'discussion', 'verification', 'voting',
414 'canceled_revoked_before_accepted',
415 'canceled_issue_not_accepted',
416 'canceled_after_revocation_during_discussion',
417 'canceled_after_revocation_during_verification',
418 'calculation',
419 'canceled_no_initiative_admitted',
420 'finished_without_winner', 'finished_with_winner');
422 COMMENT ON TYPE "issue_state" IS 'State of issues';
425 CREATE TABLE "issue" (
426 "id" SERIAL4 PRIMARY KEY,
427 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
428 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
429 "state" "issue_state" NOT NULL DEFAULT 'admission',
430 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
431 "accepted" TIMESTAMPTZ,
432 "half_frozen" TIMESTAMPTZ,
433 "fully_frozen" TIMESTAMPTZ,
434 "closed" TIMESTAMPTZ,
435 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
436 "cleaned" TIMESTAMPTZ,
437 "admission_time" INTERVAL NOT NULL,
438 "discussion_time" INTERVAL NOT NULL,
439 "verification_time" INTERVAL NOT NULL,
440 "voting_time" INTERVAL NOT NULL,
441 "snapshot" TIMESTAMPTZ,
442 "latest_snapshot_event" "snapshot_event",
443 "population" INT4,
444 "vote_now" INT4,
445 "vote_later" INT4,
446 "voter_count" INT4,
447 CONSTRAINT "valid_state" CHECK ((
448 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
449 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
450 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
451 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
452 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
453 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
454 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
455 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
456 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)) AND (
457 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
458 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
459 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
460 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
461 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
462 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
463 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
464 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
465 ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
466 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
467 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
468 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
469 )),
470 CONSTRAINT "state_change_order" CHECK (
471 "created" <= "accepted" AND
472 "accepted" <= "half_frozen" AND
473 "half_frozen" <= "fully_frozen" AND
474 "fully_frozen" <= "closed" ),
475 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
476 "cleaned" ISNULL OR "closed" NOTNULL ),
477 CONSTRAINT "last_snapshot_on_full_freeze"
478 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
479 CONSTRAINT "freeze_requires_snapshot"
480 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
481 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
482 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
483 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
484 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
485 CREATE INDEX "issue_created_idx" ON "issue" ("created");
486 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
487 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
488 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
489 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
490 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
491 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
493 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
495 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
496 COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.';
497 COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed and voting has started; Frontends must ensure that for fully_frozen issues additionally to the restrictions for half_frozen issues a) initiatives are not created, b) no interest is created or removed, c) no supporters are added or removed, d) no opinions are created, changed or deleted.';
498 COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active; Frontends must ensure that for closed issues additionally to the restrictions for half_frozen and fully_frozen issues a) no voter is added or removed to/from the direct_voter table, b) no votes are added, modified or removed.';
499 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
500 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
501 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
502 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
503 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
504 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
505 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population", "vote_now", "vote_later" and *_count values were precalculated';
506 COMMENT ON COLUMN "issue"."latest_snapshot_event" IS 'Event type of latest snapshot for issue; Can be used to select the latest snapshot data in the snapshot tables';
507 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
508 COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
509 COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
510 COMMENT ON COLUMN "issue"."voter_count" IS 'Total number of direct and delegating voters; This value is related to the final voting, while "population" is related to snapshots before the final voting';
513 CREATE TABLE "issue_setting" (
514 PRIMARY KEY ("member_id", "key", "issue_id"),
515 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
516 "key" TEXT NOT NULL,
517 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
518 "value" TEXT NOT NULL );
520 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
523 CREATE TABLE "initiative" (
524 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
525 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
526 "id" SERIAL4 PRIMARY KEY,
527 "name" TEXT NOT NULL,
528 "discussion_url" TEXT,
529 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
530 "revoked" TIMESTAMPTZ,
531 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
532 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
533 "admitted" BOOLEAN,
534 "supporter_count" INT4,
535 "informed_supporter_count" INT4,
536 "satisfied_supporter_count" INT4,
537 "satisfied_informed_supporter_count" INT4,
538 "positive_votes" INT4,
539 "negative_votes" INT4,
540 "agreed" BOOLEAN,
541 "rank" INT4,
542 "text_search_data" TSVECTOR,
543 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
544 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
545 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
546 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
547 CONSTRAINT "revoked_initiatives_cant_be_admitted"
548 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
549 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
550 CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)),
551 CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"
552 CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL),
553 CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"
554 CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) );
555 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
556 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
557 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
558 CREATE TRIGGER "update_text_search_data"
559 BEFORE INSERT OR UPDATE ON "initiative"
560 FOR EACH ROW EXECUTE PROCEDURE
561 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
562 "name", "discussion_url");
564 COMMENT ON TABLE "initiative" IS 'Group of members publishing drafts for resolutions to be passed; Frontends must ensure that initiatives of half_frozen issues are not revoked, and that initiatives of fully_frozen or closed issues are neither revoked nor created.';
566 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
567 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
568 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoked the initiative';
569 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
570 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
571 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
572 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
573 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
574 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
575 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
576 COMMENT ON COLUMN "initiative"."agreed" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "majority_num"/"majority_den"';
577 COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
580 CREATE TABLE "battle" (
581 PRIMARY KEY ("issue_id", "winning_initiative_id", "losing_initiative_id"),
582 "issue_id" INT4,
583 "winning_initiative_id" INT4,
584 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
585 "losing_initiative_id" INT4,
586 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
587 "count" INT4 NOT NULL);
589 COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue';
592 CREATE TABLE "ignored_initiative" (
593 PRIMARY KEY ("initiative_id", "member_id"),
594 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
595 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
596 CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
598 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
601 CREATE TABLE "initiative_setting" (
602 PRIMARY KEY ("member_id", "key", "initiative_id"),
603 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
604 "key" TEXT NOT NULL,
605 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
606 "value" TEXT NOT NULL );
608 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
611 CREATE TABLE "draft" (
612 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
613 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
614 "id" SERIAL8 PRIMARY KEY,
615 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
616 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
617 "formatting_engine" TEXT,
618 "content" TEXT NOT NULL,
619 "text_search_data" TSVECTOR );
620 CREATE INDEX "draft_created_idx" ON "draft" ("created");
621 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
622 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
623 CREATE TRIGGER "update_text_search_data"
624 BEFORE INSERT OR UPDATE ON "draft"
625 FOR EACH ROW EXECUTE PROCEDURE
626 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
628 COMMENT ON TABLE "draft" IS 'Drafts of initiatives to solve issues; Frontends must ensure that new drafts for initiatives of half_frozen, fully_frozen or closed issues can''t be created.';
630 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
631 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
634 CREATE TABLE "rendered_draft" (
635 PRIMARY KEY ("draft_id", "format"),
636 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
637 "format" TEXT,
638 "content" TEXT NOT NULL );
640 COMMENT ON TABLE "rendered_draft" IS 'This table may be used by frontends to cache "rendered" drafts (e.g. HTML output generated from wiki text)';
643 CREATE TABLE "suggestion" (
644 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
645 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
646 "id" SERIAL8 PRIMARY KEY,
647 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
648 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
649 "name" TEXT NOT NULL,
650 "description" TEXT NOT NULL DEFAULT '',
651 "text_search_data" TSVECTOR,
652 "minus2_unfulfilled_count" INT4,
653 "minus2_fulfilled_count" INT4,
654 "minus1_unfulfilled_count" INT4,
655 "minus1_fulfilled_count" INT4,
656 "plus1_unfulfilled_count" INT4,
657 "plus1_fulfilled_count" INT4,
658 "plus2_unfulfilled_count" INT4,
659 "plus2_fulfilled_count" INT4 );
660 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
661 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
662 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
663 CREATE TRIGGER "update_text_search_data"
664 BEFORE INSERT OR UPDATE ON "suggestion"
665 FOR EACH ROW EXECUTE PROCEDURE
666 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
667 "name", "description");
669 COMMENT ON TABLE "suggestion" IS 'Suggestions to initiators, to change the current draft; must not be deleted explicitly, as they vanish automatically if the last opinion is deleted';
671 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
672 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
673 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
674 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
675 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
676 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
677 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
678 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
681 CREATE TABLE "suggestion_setting" (
682 PRIMARY KEY ("member_id", "key", "suggestion_id"),
683 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
684 "key" TEXT NOT NULL,
685 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
686 "value" TEXT NOT NULL );
688 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
691 CREATE TABLE "invite_code_unit" (
692 PRIMARY KEY ("invite_code_id", "unit_id"),
693 "invite_code_id" INT8 REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
694 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
696 COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights';
699 CREATE TABLE "privilege" (
700 PRIMARY KEY ("unit_id", "member_id"),
701 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
702 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
703 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
704 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
705 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
706 "voting_right_manager" BOOLEAN NOT NULL DEFAULT FALSE,
707 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE );
709 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
711 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other users';
712 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create or lock sub units';
713 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create or lock areas and set area parameters';
714 COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote inside the unit';
715 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to discuss and vote';
718 CREATE TABLE "membership" (
719 PRIMARY KEY ("area_id", "member_id"),
720 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
721 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
722 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
723 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
725 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
727 COMMENT ON COLUMN "membership"."autoreject" IS 'TRUE = member votes against all initiatives, if he is neither direct_ or delegating_voter; Entries in the "interest" table can override this setting.';
730 CREATE TABLE "interest" (
731 PRIMARY KEY ("issue_id", "member_id"),
732 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
733 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
734 "autoreject" BOOLEAN,
735 "voting_requested" BOOLEAN );
736 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
738 COMMENT ON TABLE "interest" IS 'Interest of members in a particular issue; Frontends must ensure that interest for fully_frozen or closed issues is not added or removed.';
740 COMMENT ON COLUMN "interest"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
741 COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply';
744 CREATE TABLE "initiator" (
745 PRIMARY KEY ("initiative_id", "member_id"),
746 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
747 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
748 "accepted" BOOLEAN );
749 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
751 COMMENT ON TABLE "initiator" IS 'Members who are allowed to post new drafts; Frontends must ensure that initiators are not added or removed from half_frozen, fully_frozen or closed initiatives.';
753 COMMENT ON COLUMN "initiator"."accepted" IS 'If "accepted" is NULL, then the member was invited to be a co-initiator, but has not answered yet. If it is TRUE, the member has accepted the invitation, if it is FALSE, the member has rejected the invitation.';
756 CREATE TABLE "supporter" (
757 "issue_id" INT4 NOT NULL,
758 PRIMARY KEY ("initiative_id", "member_id"),
759 "initiative_id" INT4,
760 "member_id" INT4,
761 "draft_id" INT8 NOT NULL,
762 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
763 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
764 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
766 COMMENT ON TABLE "supporter" IS 'Members who support an initiative (conditionally); Frontends must ensure that supporters are not added or removed from fully_frozen or closed initiatives.';
768 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
771 CREATE TABLE "opinion" (
772 "initiative_id" INT4 NOT NULL,
773 PRIMARY KEY ("suggestion_id", "member_id"),
774 "suggestion_id" INT8,
775 "member_id" INT4,
776 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
777 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
778 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
779 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
780 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
782 COMMENT ON TABLE "opinion" IS 'Opinion on suggestions (criticism related to initiatives); Frontends must ensure that opinions are not created modified or deleted when related to fully_frozen or closed issues.';
784 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
787 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
789 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
792 CREATE TABLE "delegation" (
793 "id" SERIAL8 PRIMARY KEY,
794 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
795 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
796 "scope" "delegation_scope" NOT NULL,
797 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
798 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
799 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
800 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
801 CONSTRAINT "no_unit_delegation_to_null"
802 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
803 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
804 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
805 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
806 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
807 UNIQUE ("unit_id", "truster_id"),
808 UNIQUE ("area_id", "truster_id"),
809 UNIQUE ("issue_id", "truster_id") );
810 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
811 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
813 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
815 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
816 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
817 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
820 CREATE TABLE "direct_population_snapshot" (
821 PRIMARY KEY ("issue_id", "event", "member_id"),
822 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
823 "event" "snapshot_event",
824 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
825 "weight" INT4 );
826 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
828 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
830 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
831 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
834 CREATE TABLE "delegating_population_snapshot" (
835 PRIMARY KEY ("issue_id", "event", "member_id"),
836 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
837 "event" "snapshot_event",
838 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
839 "weight" INT4,
840 "scope" "delegation_scope" NOT NULL,
841 "delegate_member_ids" INT4[] NOT NULL );
842 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
844 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
846 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
847 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
848 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
849 COMMENT ON COLUMN "delegating_population_snapshot"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_population_snapshot"';
852 CREATE TABLE "direct_interest_snapshot" (
853 PRIMARY KEY ("issue_id", "event", "member_id"),
854 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
855 "event" "snapshot_event",
856 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
857 "weight" INT4,
858 "voting_requested" BOOLEAN );
859 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
861 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
863 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
864 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
865 COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
868 CREATE TABLE "delegating_interest_snapshot" (
869 PRIMARY KEY ("issue_id", "event", "member_id"),
870 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
871 "event" "snapshot_event",
872 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
873 "weight" INT4,
874 "scope" "delegation_scope" NOT NULL,
875 "delegate_member_ids" INT4[] NOT NULL );
876 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
878 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
880 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
881 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
882 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
883 COMMENT ON COLUMN "delegating_interest_snapshot"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_interest_snapshot"';
886 CREATE TABLE "direct_supporter_snapshot" (
887 "issue_id" INT4 NOT NULL,
888 PRIMARY KEY ("initiative_id", "event", "member_id"),
889 "initiative_id" INT4,
890 "event" "snapshot_event",
891 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
892 "informed" BOOLEAN NOT NULL,
893 "satisfied" BOOLEAN NOT NULL,
894 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
895 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
896 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
898 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
900 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
901 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
902 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
905 CREATE TABLE "non_voter" (
906 PRIMARY KEY ("issue_id", "member_id"),
907 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
908 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
909 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
911 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
914 CREATE TABLE "direct_voter" (
915 PRIMARY KEY ("issue_id", "member_id"),
916 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
917 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
918 "weight" INT4,
919 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
920 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
922 COMMENT ON TABLE "direct_voter" IS 'Members having directly voted for/against initiatives of an issue; Frontends must ensure that no voters are added or removed to/from this table when the issue has been closed.';
924 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
925 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
928 CREATE TABLE "delegating_voter" (
929 PRIMARY KEY ("issue_id", "member_id"),
930 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
931 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
932 "weight" INT4,
933 "scope" "delegation_scope" NOT NULL,
934 "delegate_member_ids" INT4[] NOT NULL );
935 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
937 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
939 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
940 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
941 COMMENT ON COLUMN "delegating_voter"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_voter"';
944 CREATE TABLE "vote" (
945 "issue_id" INT4 NOT NULL,
946 PRIMARY KEY ("initiative_id", "member_id"),
947 "initiative_id" INT4,
948 "member_id" INT4,
949 "grade" INT4,
950 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
951 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
952 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
954 COMMENT ON TABLE "vote" IS 'Manual and delegated votes without abstentions; Frontends must ensure that no votes are added modified or removed when the issue has been closed.';
956 COMMENT ON COLUMN "vote"."grade" IS 'Values smaller than zero mean reject, values greater than zero mean acceptance, zero or missing row means abstention. Preferences are expressed by different positive or negative numbers.';
959 CREATE TABLE "issue_comment" (
960 PRIMARY KEY ("issue_id", "member_id"),
961 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
962 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
963 "changed" TIMESTAMPTZ NOT NULL DEFAULT now(),
964 "formatting_engine" TEXT,
965 "content" TEXT NOT NULL,
966 "text_search_data" TSVECTOR );
967 CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id");
968 CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data");
969 CREATE TRIGGER "update_text_search_data"
970 BEFORE INSERT OR UPDATE ON "issue_comment"
971 FOR EACH ROW EXECUTE PROCEDURE
972 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
974 COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues';
976 COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed';
979 CREATE TABLE "rendered_issue_comment" (
980 PRIMARY KEY ("issue_id", "member_id", "format"),
981 FOREIGN KEY ("issue_id", "member_id")
982 REFERENCES "issue_comment" ("issue_id", "member_id")
983 ON DELETE CASCADE ON UPDATE CASCADE,
984 "issue_id" INT4,
985 "member_id" INT4,
986 "format" TEXT,
987 "content" TEXT NOT NULL );
989 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)';
992 CREATE TABLE "voting_comment" (
993 PRIMARY KEY ("issue_id", "member_id"),
994 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
995 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
996 "changed" TIMESTAMPTZ,
997 "formatting_engine" TEXT,
998 "content" TEXT NOT NULL,
999 "text_search_data" TSVECTOR );
1000 CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");
1001 CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");
1002 CREATE TRIGGER "update_text_search_data"
1003 BEFORE INSERT OR UPDATE ON "voting_comment"
1004 FOR EACH ROW EXECUTE PROCEDURE
1005 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
1007 COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';
1009 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.';
1012 CREATE TABLE "rendered_voting_comment" (
1013 PRIMARY KEY ("issue_id", "member_id", "format"),
1014 FOREIGN KEY ("issue_id", "member_id")
1015 REFERENCES "voting_comment" ("issue_id", "member_id")
1016 ON DELETE CASCADE ON UPDATE CASCADE,
1017 "issue_id" INT4,
1018 "member_id" INT4,
1019 "format" TEXT,
1020 "content" TEXT NOT NULL );
1022 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)';
1025 CREATE TYPE "event_type" AS ENUM (
1026 'issue_state_changed',
1027 'initiative_created_in_new_issue',
1028 'initiative_created_in_existing_issue',
1029 'initiative_revoked',
1030 'new_draft_created',
1031 'suggestion_created');
1033 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
1036 CREATE TABLE "event" (
1037 "id" SERIAL8 PRIMARY KEY,
1038 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
1039 "event" "event_type" NOT NULL,
1040 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1041 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1042 "state" "issue_state" CHECK ("state" != 'calculation'),
1043 "initiative_id" INT4,
1044 "draft_id" INT8,
1045 "suggestion_id" INT8,
1046 FOREIGN KEY ("issue_id", "initiative_id")
1047 REFERENCES "initiative" ("issue_id", "id")
1048 ON DELETE CASCADE ON UPDATE CASCADE,
1049 FOREIGN KEY ("initiative_id", "draft_id")
1050 REFERENCES "draft" ("initiative_id", "id")
1051 ON DELETE CASCADE ON UPDATE CASCADE,
1052 FOREIGN KEY ("initiative_id", "suggestion_id")
1053 REFERENCES "suggestion" ("initiative_id", "id")
1054 ON DELETE CASCADE ON UPDATE CASCADE,
1055 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
1056 "event" != 'issue_state_changed' OR (
1057 "member_id" ISNULL AND
1058 "issue_id" NOTNULL AND
1059 "state" NOTNULL AND
1060 "initiative_id" ISNULL AND
1061 "draft_id" ISNULL AND
1062 "suggestion_id" ISNULL )),
1063 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
1064 "event" NOT IN (
1065 'initiative_created_in_new_issue',
1066 'initiative_created_in_existing_issue',
1067 'initiative_revoked',
1068 'new_draft_created'
1069 ) OR (
1070 "member_id" NOTNULL AND
1071 "issue_id" NOTNULL AND
1072 "state" NOTNULL AND
1073 "initiative_id" NOTNULL AND
1074 "draft_id" NOTNULL AND
1075 "suggestion_id" ISNULL )),
1076 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
1077 "event" != 'suggestion_created' OR (
1078 "member_id" NOTNULL AND
1079 "issue_id" NOTNULL AND
1080 "state" NOTNULL AND
1081 "initiative_id" NOTNULL AND
1082 "draft_id" ISNULL AND
1083 "suggestion_id" NOTNULL )) );
1085 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
1087 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
1088 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
1089 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
1090 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
1094 ----------------------------------------------
1095 -- Writing of history entries and event log --
1096 ----------------------------------------------
1098 CREATE FUNCTION "write_member_history_trigger"()
1099 RETURNS TRIGGER
1100 LANGUAGE 'plpgsql' VOLATILE AS $$
1101 BEGIN
1102 IF
1103 NEW."active" != OLD."active" OR
1104 NEW."name" != OLD."name"
1105 THEN
1106 INSERT INTO "member_history"
1107 ("member_id", "active", "name")
1108 VALUES (NEW."id", OLD."active", OLD."name");
1109 END IF;
1110 RETURN NULL;
1111 END;
1112 $$;
1114 CREATE TRIGGER "write_member_history"
1115 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1116 "write_member_history_trigger"();
1118 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1119 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1122 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1123 RETURNS TRIGGER
1124 LANGUAGE 'plpgsql' VOLATILE AS $$
1125 BEGIN
1126 IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
1127 INSERT INTO "event" ("event", "issue_id", "state")
1128 VALUES ('issue_state_changed', NEW."id", NEW."state");
1129 END IF;
1130 RETURN NULL;
1131 END;
1132 $$;
1134 CREATE TRIGGER "write_event_issue_state_changed"
1135 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1136 "write_event_issue_state_changed_trigger"();
1138 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1139 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1142 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1143 RETURNS TRIGGER
1144 LANGUAGE 'plpgsql' VOLATILE AS $$
1145 DECLARE
1146 "initiative_row" "initiative"%ROWTYPE;
1147 "issue_row" "issue"%ROWTYPE;
1148 "event_v" "event_type";
1149 BEGIN
1150 SELECT * INTO "initiative_row" FROM "initiative"
1151 WHERE "id" = NEW."initiative_id";
1152 SELECT * INTO "issue_row" FROM "issue"
1153 WHERE "id" = "initiative_row"."issue_id";
1154 IF EXISTS (
1155 SELECT NULL FROM "draft"
1156 WHERE "initiative_id" = NEW."initiative_id"
1157 AND "id" != NEW."id"
1158 ) THEN
1159 "event_v" := 'new_draft_created';
1160 ELSE
1161 IF EXISTS (
1162 SELECT NULL FROM "initiative"
1163 WHERE "issue_id" = "initiative_row"."issue_id"
1164 AND "id" != "initiative_row"."id"
1165 ) THEN
1166 "event_v" := 'initiative_created_in_existing_issue';
1167 ELSE
1168 "event_v" := 'initiative_created_in_new_issue';
1169 END IF;
1170 END IF;
1171 INSERT INTO "event" (
1172 "event", "member_id",
1173 "issue_id", "state", "initiative_id", "draft_id"
1174 ) VALUES (
1175 "event_v",
1176 NEW."author_id",
1177 "initiative_row"."issue_id",
1178 "issue_row"."state",
1179 "initiative_row"."id",
1180 NEW."id" );
1181 RETURN NULL;
1182 END;
1183 $$;
1185 CREATE TRIGGER "write_event_initiative_or_draft_created"
1186 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1187 "write_event_initiative_or_draft_created_trigger"();
1189 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1190 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1193 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1194 RETURNS TRIGGER
1195 LANGUAGE 'plpgsql' VOLATILE AS $$
1196 DECLARE
1197 "issue_row" "issue"%ROWTYPE;
1198 BEGIN
1199 SELECT * INTO "issue_row" FROM "issue"
1200 WHERE "id" = NEW."issue_id";
1201 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1202 INSERT INTO "event" (
1203 "event", "member_id", "issue_id", "state", "initiative_id"
1204 ) VALUES (
1205 'initiative_revoked',
1206 NEW."revoked_by_member_id",
1207 NEW."issue_id",
1208 "issue_row"."state",
1209 NEW."id" );
1210 END IF;
1211 RETURN NULL;
1212 END;
1213 $$;
1215 CREATE TRIGGER "write_event_initiative_revoked"
1216 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1217 "write_event_initiative_revoked_trigger"();
1219 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1220 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1223 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1224 RETURNS TRIGGER
1225 LANGUAGE 'plpgsql' VOLATILE AS $$
1226 DECLARE
1227 "initiative_row" "initiative"%ROWTYPE;
1228 "issue_row" "issue"%ROWTYPE;
1229 BEGIN
1230 SELECT * INTO "initiative_row" FROM "initiative"
1231 WHERE "id" = NEW."initiative_id";
1232 SELECT * INTO "issue_row" FROM "issue"
1233 WHERE "id" = "initiative_row"."issue_id";
1234 INSERT INTO "event" (
1235 "event", "member_id",
1236 "issue_id", "state", "initiative_id", "suggestion_id"
1237 ) VALUES (
1238 'suggestion_created',
1239 NEW."author_id",
1240 "initiative_row"."issue_id",
1241 "issue_row"."state",
1242 "initiative_row"."id",
1243 NEW."id" );
1244 RETURN NULL;
1245 END;
1246 $$;
1248 CREATE TRIGGER "write_event_suggestion_created"
1249 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1250 "write_event_suggestion_created_trigger"();
1252 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1253 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1257 ----------------------------
1258 -- Additional constraints --
1259 ----------------------------
1262 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1263 RETURNS TRIGGER
1264 LANGUAGE 'plpgsql' VOLATILE AS $$
1265 BEGIN
1266 IF NOT EXISTS (
1267 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1268 ) THEN
1269 --RAISE 'Cannot create issue without an initial initiative.' USING
1270 -- ERRCODE = 'integrity_constraint_violation',
1271 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
1272 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
1273 END IF;
1274 RETURN NULL;
1275 END;
1276 $$;
1278 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1279 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1280 FOR EACH ROW EXECUTE PROCEDURE
1281 "issue_requires_first_initiative_trigger"();
1283 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1284 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1287 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1288 RETURNS TRIGGER
1289 LANGUAGE 'plpgsql' VOLATILE AS $$
1290 DECLARE
1291 "reference_lost" BOOLEAN;
1292 BEGIN
1293 IF TG_OP = 'DELETE' THEN
1294 "reference_lost" := TRUE;
1295 ELSE
1296 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1297 END IF;
1298 IF
1299 "reference_lost" AND NOT EXISTS (
1300 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1301 )
1302 THEN
1303 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1304 END IF;
1305 RETURN NULL;
1306 END;
1307 $$;
1309 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1310 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1311 FOR EACH ROW EXECUTE PROCEDURE
1312 "last_initiative_deletes_issue_trigger"();
1314 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1315 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1318 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1319 RETURNS TRIGGER
1320 LANGUAGE 'plpgsql' VOLATILE AS $$
1321 BEGIN
1322 IF NOT EXISTS (
1323 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1324 ) THEN
1325 --RAISE 'Cannot create initiative without an initial draft.' USING
1326 -- ERRCODE = 'integrity_constraint_violation',
1327 -- HINT = 'Create issue, initiative and draft within the same transaction.';
1328 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
1329 END IF;
1330 RETURN NULL;
1331 END;
1332 $$;
1334 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1335 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1336 FOR EACH ROW EXECUTE PROCEDURE
1337 "initiative_requires_first_draft_trigger"();
1339 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1340 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1343 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1344 RETURNS TRIGGER
1345 LANGUAGE 'plpgsql' VOLATILE AS $$
1346 DECLARE
1347 "reference_lost" BOOLEAN;
1348 BEGIN
1349 IF TG_OP = 'DELETE' THEN
1350 "reference_lost" := TRUE;
1351 ELSE
1352 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1353 END IF;
1354 IF
1355 "reference_lost" AND NOT EXISTS (
1356 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1357 )
1358 THEN
1359 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1360 END IF;
1361 RETURN NULL;
1362 END;
1363 $$;
1365 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1366 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1367 FOR EACH ROW EXECUTE PROCEDURE
1368 "last_draft_deletes_initiative_trigger"();
1370 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1371 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1374 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1375 RETURNS TRIGGER
1376 LANGUAGE 'plpgsql' VOLATILE AS $$
1377 BEGIN
1378 IF NOT EXISTS (
1379 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1380 ) THEN
1381 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
1382 END IF;
1383 RETURN NULL;
1384 END;
1385 $$;
1387 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1388 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1389 FOR EACH ROW EXECUTE PROCEDURE
1390 "suggestion_requires_first_opinion_trigger"();
1392 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1393 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1396 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1397 RETURNS TRIGGER
1398 LANGUAGE 'plpgsql' VOLATILE AS $$
1399 DECLARE
1400 "reference_lost" BOOLEAN;
1401 BEGIN
1402 IF TG_OP = 'DELETE' THEN
1403 "reference_lost" := TRUE;
1404 ELSE
1405 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1406 END IF;
1407 IF
1408 "reference_lost" AND NOT EXISTS (
1409 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1410 )
1411 THEN
1412 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1413 END IF;
1414 RETURN NULL;
1415 END;
1416 $$;
1418 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1419 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1420 FOR EACH ROW EXECUTE PROCEDURE
1421 "last_opinion_deletes_suggestion_trigger"();
1423 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1424 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1428 ---------------------------------------------------------------
1429 -- Ensure that votes are not modified when issues are frozen --
1430 ---------------------------------------------------------------
1432 -- NOTE: Frontends should ensure this anyway, but in case of programming
1433 -- errors the following triggers ensure data integrity.
1436 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1437 RETURNS TRIGGER
1438 LANGUAGE 'plpgsql' VOLATILE AS $$
1439 DECLARE
1440 "issue_id_v" "issue"."id"%TYPE;
1441 "issue_row" "issue"%ROWTYPE;
1442 BEGIN
1443 IF TG_OP = 'DELETE' THEN
1444 "issue_id_v" := OLD."issue_id";
1445 ELSE
1446 "issue_id_v" := NEW."issue_id";
1447 END IF;
1448 SELECT INTO "issue_row" * FROM "issue"
1449 WHERE "id" = "issue_id_v" FOR SHARE;
1450 IF "issue_row"."closed" NOTNULL THEN
1451 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
1452 END IF;
1453 RETURN NULL;
1454 END;
1455 $$;
1457 CREATE TRIGGER "forbid_changes_on_closed_issue"
1458 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1459 FOR EACH ROW EXECUTE PROCEDURE
1460 "forbid_changes_on_closed_issue_trigger"();
1462 CREATE TRIGGER "forbid_changes_on_closed_issue"
1463 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1464 FOR EACH ROW EXECUTE PROCEDURE
1465 "forbid_changes_on_closed_issue_trigger"();
1467 CREATE TRIGGER "forbid_changes_on_closed_issue"
1468 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1469 FOR EACH ROW EXECUTE PROCEDURE
1470 "forbid_changes_on_closed_issue_trigger"();
1472 COMMENT ON FUNCTION "forbid_changes_on_closed_issue_trigger"() IS 'Implementation of triggers "forbid_changes_on_closed_issue" on tables "direct_voter", "delegating_voter" and "vote"';
1473 COMMENT ON TRIGGER "forbid_changes_on_closed_issue" ON "direct_voter" IS 'Ensures that frontends can''t tamper with votings of closed issues, in case of programming errors';
1474 COMMENT ON TRIGGER "forbid_changes_on_closed_issue" ON "delegating_voter" IS 'Ensures that frontends can''t tamper with votings of closed issues, in case of programming errors';
1475 COMMENT ON TRIGGER "forbid_changes_on_closed_issue" ON "vote" IS 'Ensures that frontends can''t tamper with votings of closed issues, in case of programming errors';
1479 --------------------------------------------------------------------
1480 -- Auto-retrieval of fields only needed for referential integrity --
1481 --------------------------------------------------------------------
1484 CREATE FUNCTION "autofill_issue_id_trigger"()
1485 RETURNS TRIGGER
1486 LANGUAGE 'plpgsql' VOLATILE AS $$
1487 BEGIN
1488 IF NEW."issue_id" ISNULL THEN
1489 SELECT "issue_id" INTO NEW."issue_id"
1490 FROM "initiative" WHERE "id" = NEW."initiative_id";
1491 END IF;
1492 RETURN NEW;
1493 END;
1494 $$;
1496 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1497 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1499 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1500 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1502 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1503 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1504 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1507 CREATE FUNCTION "autofill_initiative_id_trigger"()
1508 RETURNS TRIGGER
1509 LANGUAGE 'plpgsql' VOLATILE AS $$
1510 BEGIN
1511 IF NEW."initiative_id" ISNULL THEN
1512 SELECT "initiative_id" INTO NEW."initiative_id"
1513 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1514 END IF;
1515 RETURN NEW;
1516 END;
1517 $$;
1519 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1520 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1522 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1523 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1527 -----------------------------------------------------
1528 -- Automatic calculation of certain default values --
1529 -----------------------------------------------------
1532 CREATE FUNCTION "copy_timings_trigger"()
1533 RETURNS TRIGGER
1534 LANGUAGE 'plpgsql' VOLATILE AS $$
1535 DECLARE
1536 "policy_row" "policy"%ROWTYPE;
1537 BEGIN
1538 SELECT * INTO "policy_row" FROM "policy"
1539 WHERE "id" = NEW."policy_id";
1540 IF NEW."admission_time" ISNULL THEN
1541 NEW."admission_time" := "policy_row"."admission_time";
1542 END IF;
1543 IF NEW."discussion_time" ISNULL THEN
1544 NEW."discussion_time" := "policy_row"."discussion_time";
1545 END IF;
1546 IF NEW."verification_time" ISNULL THEN
1547 NEW."verification_time" := "policy_row"."verification_time";
1548 END IF;
1549 IF NEW."voting_time" ISNULL THEN
1550 NEW."voting_time" := "policy_row"."voting_time";
1551 END IF;
1552 RETURN NEW;
1553 END;
1554 $$;
1556 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1557 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1559 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1560 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1563 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
1564 RETURNS TRIGGER
1565 LANGUAGE 'plpgsql' VOLATILE AS $$
1566 BEGIN
1567 IF NEW."draft_id" ISNULL THEN
1568 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1569 WHERE "initiative_id" = NEW."initiative_id";
1570 END IF;
1571 RETURN NEW;
1572 END;
1573 $$;
1575 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1576 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
1578 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
1579 COMMENT ON TRIGGER "default_for_draft_id" ON "supporter" IS 'If "draft_id" is NULL, then use the current draft of the initiative as default';
1583 ----------------------------------------
1584 -- Automatic creation of dependencies --
1585 ----------------------------------------
1588 CREATE FUNCTION "autocreate_interest_trigger"()
1589 RETURNS TRIGGER
1590 LANGUAGE 'plpgsql' VOLATILE AS $$
1591 BEGIN
1592 IF NOT EXISTS (
1593 SELECT NULL FROM "initiative" JOIN "interest"
1594 ON "initiative"."issue_id" = "interest"."issue_id"
1595 WHERE "initiative"."id" = NEW."initiative_id"
1596 AND "interest"."member_id" = NEW."member_id"
1597 ) THEN
1598 BEGIN
1599 INSERT INTO "interest" ("issue_id", "member_id")
1600 SELECT "issue_id", NEW."member_id"
1601 FROM "initiative" WHERE "id" = NEW."initiative_id";
1602 EXCEPTION WHEN unique_violation THEN END;
1603 END IF;
1604 RETURN NEW;
1605 END;
1606 $$;
1608 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1609 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1611 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1612 COMMENT ON TRIGGER "autocreate_interest" ON "supporter" IS 'Supporting an initiative implies interest in the issue, thus automatically creates an entry in the "interest" table';
1615 CREATE FUNCTION "autocreate_supporter_trigger"()
1616 RETURNS TRIGGER
1617 LANGUAGE 'plpgsql' VOLATILE AS $$
1618 BEGIN
1619 IF NOT EXISTS (
1620 SELECT NULL FROM "suggestion" JOIN "supporter"
1621 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1622 WHERE "suggestion"."id" = NEW."suggestion_id"
1623 AND "supporter"."member_id" = NEW."member_id"
1624 ) THEN
1625 BEGIN
1626 INSERT INTO "supporter" ("initiative_id", "member_id")
1627 SELECT "initiative_id", NEW."member_id"
1628 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1629 EXCEPTION WHEN unique_violation THEN END;
1630 END IF;
1631 RETURN NEW;
1632 END;
1633 $$;
1635 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1636 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1638 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1639 COMMENT ON TRIGGER "autocreate_supporter" ON "opinion" IS 'Opinions can only be added for supported initiatives. This trigger automatrically creates an entry in the "supporter" table, if not existent yet.';
1643 ------------------------------------------
1644 -- Views and helper functions for views --
1645 ------------------------------------------
1648 CREATE VIEW "unit_delegation" AS
1649 SELECT
1650 "unit"."id" AS "unit_id",
1651 "delegation"."id",
1652 "delegation"."truster_id",
1653 "delegation"."trustee_id",
1654 "delegation"."scope"
1655 FROM "unit"
1656 JOIN "delegation"
1657 ON "delegation"."unit_id" = "unit"."id"
1658 JOIN "member"
1659 ON "delegation"."truster_id" = "member"."id"
1660 JOIN "privilege"
1661 ON "delegation"."unit_id" = "privilege"."unit_id"
1662 AND "delegation"."truster_id" = "privilege"."member_id"
1663 WHERE "member"."active" AND "privilege"."voting_right";
1665 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1668 CREATE VIEW "area_delegation" AS
1669 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1670 "area"."id" AS "area_id",
1671 "delegation"."id",
1672 "delegation"."truster_id",
1673 "delegation"."trustee_id",
1674 "delegation"."scope"
1675 FROM "area"
1676 JOIN "delegation"
1677 ON "delegation"."unit_id" = "area"."unit_id"
1678 OR "delegation"."area_id" = "area"."id"
1679 JOIN "member"
1680 ON "delegation"."truster_id" = "member"."id"
1681 JOIN "privilege"
1682 ON "area"."unit_id" = "privilege"."unit_id"
1683 AND "delegation"."truster_id" = "privilege"."member_id"
1684 WHERE "member"."active" AND "privilege"."voting_right"
1685 ORDER BY
1686 "area"."id",
1687 "delegation"."truster_id",
1688 "delegation"."scope" DESC;
1690 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1693 CREATE VIEW "issue_delegation" AS
1694 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1695 "issue"."id" AS "issue_id",
1696 "delegation"."id",
1697 "delegation"."truster_id",
1698 "delegation"."trustee_id",
1699 "delegation"."scope"
1700 FROM "issue"
1701 JOIN "area"
1702 ON "area"."id" = "issue"."area_id"
1703 JOIN "delegation"
1704 ON "delegation"."unit_id" = "area"."unit_id"
1705 OR "delegation"."area_id" = "area"."id"
1706 OR "delegation"."issue_id" = "issue"."id"
1707 JOIN "member"
1708 ON "delegation"."truster_id" = "member"."id"
1709 JOIN "privilege"
1710 ON "area"."unit_id" = "privilege"."unit_id"
1711 AND "delegation"."truster_id" = "privilege"."member_id"
1712 WHERE "member"."active" AND "privilege"."voting_right"
1713 ORDER BY
1714 "issue"."id",
1715 "delegation"."truster_id",
1716 "delegation"."scope" DESC;
1718 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
1721 CREATE FUNCTION "membership_weight_with_skipping"
1722 ( "area_id_p" "area"."id"%TYPE,
1723 "member_id_p" "member"."id"%TYPE,
1724 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1725 RETURNS INT4
1726 LANGUAGE 'plpgsql' STABLE AS $$
1727 DECLARE
1728 "sum_v" INT4;
1729 "delegation_row" "area_delegation"%ROWTYPE;
1730 BEGIN
1731 "sum_v" := 1;
1732 FOR "delegation_row" IN
1733 SELECT "area_delegation".*
1734 FROM "area_delegation" LEFT JOIN "membership"
1735 ON "membership"."area_id" = "area_id_p"
1736 AND "membership"."member_id" = "area_delegation"."truster_id"
1737 WHERE "area_delegation"."area_id" = "area_id_p"
1738 AND "area_delegation"."trustee_id" = "member_id_p"
1739 AND "membership"."member_id" ISNULL
1740 LOOP
1741 IF NOT
1742 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1743 THEN
1744 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1745 "area_id_p",
1746 "delegation_row"."truster_id",
1747 "skip_member_ids_p" || "delegation_row"."truster_id"
1748 );
1749 END IF;
1750 END LOOP;
1751 RETURN "sum_v";
1752 END;
1753 $$;
1755 COMMENT ON FUNCTION "membership_weight_with_skipping"
1756 ( "area"."id"%TYPE,
1757 "member"."id"%TYPE,
1758 INT4[] )
1759 IS 'Helper function for "membership_weight" function';
1762 CREATE FUNCTION "membership_weight"
1763 ( "area_id_p" "area"."id"%TYPE,
1764 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1765 RETURNS INT4
1766 LANGUAGE 'plpgsql' STABLE AS $$
1767 BEGIN
1768 RETURN "membership_weight_with_skipping"(
1769 "area_id_p",
1770 "member_id_p",
1771 ARRAY["member_id_p"]
1772 );
1773 END;
1774 $$;
1776 COMMENT ON FUNCTION "membership_weight"
1777 ( "area"."id"%TYPE,
1778 "member"."id"%TYPE )
1779 IS 'Calculates the potential voting weight of a member in a given area';
1782 CREATE VIEW "member_count_view" AS
1783 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1785 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1788 CREATE VIEW "unit_member_count" AS
1789 SELECT
1790 "unit"."id" AS "unit_id",
1791 sum("member"."id") AS "member_count"
1792 FROM "unit"
1793 LEFT JOIN "privilege"
1794 ON "privilege"."unit_id" = "unit"."id"
1795 AND "privilege"."voting_right"
1796 LEFT JOIN "member"
1797 ON "member"."id" = "privilege"."member_id"
1798 AND "member"."active"
1799 GROUP BY "unit"."id";
1801 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
1804 CREATE VIEW "area_member_count" AS
1805 SELECT
1806 "area"."id" AS "area_id",
1807 count("member"."id") AS "direct_member_count",
1808 coalesce(
1809 sum(
1810 CASE WHEN "member"."id" NOTNULL THEN
1811 "membership_weight"("area"."id", "member"."id")
1812 ELSE 0 END
1813 )
1814 ) AS "member_weight",
1815 coalesce(
1816 sum(
1817 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
1818 "membership_weight"("area"."id", "member"."id")
1819 ELSE 0 END
1820 )
1821 ) AS "autoreject_weight"
1822 FROM "area"
1823 LEFT JOIN "membership"
1824 ON "area"."id" = "membership"."area_id"
1825 LEFT JOIN "privilege"
1826 ON "privilege"."unit_id" = "area"."unit_id"
1827 AND "privilege"."member_id" = "membership"."member_id"
1828 AND "privilege"."voting_right"
1829 LEFT JOIN "member"
1830 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
1831 AND "member"."active"
1832 GROUP BY "area"."id";
1834 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count", "member_weight" and "autoreject_weight" columns of table "area"';
1837 CREATE VIEW "opening_draft" AS
1838 SELECT "draft".* FROM (
1839 SELECT
1840 "initiative"."id" AS "initiative_id",
1841 min("draft"."id") AS "draft_id"
1842 FROM "initiative" JOIN "draft"
1843 ON "initiative"."id" = "draft"."initiative_id"
1844 GROUP BY "initiative"."id"
1845 ) AS "subquery"
1846 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1848 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1851 CREATE VIEW "current_draft" AS
1852 SELECT "draft".* FROM (
1853 SELECT
1854 "initiative"."id" AS "initiative_id",
1855 max("draft"."id") AS "draft_id"
1856 FROM "initiative" JOIN "draft"
1857 ON "initiative"."id" = "draft"."initiative_id"
1858 GROUP BY "initiative"."id"
1859 ) AS "subquery"
1860 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1862 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1865 CREATE VIEW "critical_opinion" AS
1866 SELECT * FROM "opinion"
1867 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1868 OR ("degree" = -2 AND "fulfilled" = TRUE);
1870 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1873 CREATE VIEW "battle_view" AS
1874 SELECT
1875 "issue"."id" AS "issue_id",
1876 "winning_initiative"."id" AS "winning_initiative_id",
1877 "losing_initiative"."id" AS "losing_initiative_id",
1878 sum(
1879 CASE WHEN
1880 coalesce("better_vote"."grade", 0) >
1881 coalesce("worse_vote"."grade", 0)
1882 THEN "direct_voter"."weight" ELSE 0 END
1883 ) AS "count"
1884 FROM "issue"
1885 LEFT JOIN "direct_voter"
1886 ON "issue"."id" = "direct_voter"."issue_id"
1887 JOIN "initiative" AS "winning_initiative"
1888 ON "issue"."id" = "winning_initiative"."issue_id"
1889 AND "winning_initiative"."agreed"
1890 JOIN "initiative" AS "losing_initiative"
1891 ON "issue"."id" = "losing_initiative"."issue_id"
1892 AND "losing_initiative"."agreed"
1893 LEFT JOIN "vote" AS "better_vote"
1894 ON "direct_voter"."member_id" = "better_vote"."member_id"
1895 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1896 LEFT JOIN "vote" AS "worse_vote"
1897 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1898 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
1899 WHERE "issue"."closed" NOTNULL
1900 AND "issue"."cleaned" ISNULL
1901 AND "winning_initiative"."id" != "losing_initiative"."id"
1902 GROUP BY
1903 "issue"."id",
1904 "winning_initiative"."id",
1905 "losing_initiative"."id";
1907 COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative to another; Used to fill "battle" table';
1910 CREATE VIEW "expired_session" AS
1911 SELECT * FROM "session" WHERE now() > "expiry";
1913 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
1914 DELETE FROM "session" WHERE "ident" = OLD."ident";
1916 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
1917 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
1920 CREATE VIEW "open_issue" AS
1921 SELECT * FROM "issue" WHERE "closed" ISNULL;
1923 COMMENT ON VIEW "open_issue" IS 'All open issues';
1926 CREATE VIEW "issue_with_ranks_missing" AS
1927 SELECT * FROM "issue"
1928 WHERE "fully_frozen" NOTNULL
1929 AND "closed" NOTNULL
1930 AND "ranks_available" = FALSE;
1932 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
1935 CREATE VIEW "member_contingent" AS
1936 SELECT
1937 "member"."id" AS "member_id",
1938 "contingent"."time_frame",
1939 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
1940 (
1941 SELECT count(1) FROM "draft"
1942 WHERE "draft"."author_id" = "member"."id"
1943 AND "draft"."created" > now() - "contingent"."time_frame"
1944 ) + (
1945 SELECT count(1) FROM "suggestion"
1946 WHERE "suggestion"."author_id" = "member"."id"
1947 AND "suggestion"."created" > now() - "contingent"."time_frame"
1948 )
1949 ELSE NULL END AS "text_entry_count",
1950 "contingent"."text_entry_limit",
1951 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
1952 SELECT count(1) FROM "opening_draft"
1953 WHERE "opening_draft"."author_id" = "member"."id"
1954 AND "opening_draft"."created" > now() - "contingent"."time_frame"
1955 ) ELSE NULL END AS "initiative_count",
1956 "contingent"."initiative_limit"
1957 FROM "member" CROSS JOIN "contingent";
1959 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
1961 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
1962 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
1965 CREATE VIEW "member_contingent_left" AS
1966 SELECT
1967 "member_id",
1968 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
1969 max("initiative_limit" - "initiative_count") AS "initiatives_left"
1970 FROM "member_contingent" GROUP BY "member_id";
1972 COMMENT ON VIEW "member_contingent_left" IS 'Amount of text entries or initiatives which can be posted now instantly by a member. This view should be used by a frontend to determine, if the contingent for posting is exhausted.';
1975 CREATE VIEW "event_seen_by_member" AS
1976 SELECT
1977 "member"."id" AS "seen_by_member_id",
1978 CASE WHEN "event"."state" IN (
1979 'voting',
1980 'finished_without_winner',
1981 'finished_with_winner'
1982 ) THEN
1983 'voting'::"notify_level"
1984 ELSE
1985 CASE WHEN "event"."state" IN (
1986 'verification',
1987 'canceled_after_revocation_during_verification',
1988 'canceled_no_initiative_admitted'
1989 ) THEN
1990 'verification'::"notify_level"
1991 ELSE
1992 CASE WHEN "event"."state" IN (
1993 'discussion',
1994 'canceled_after_revocation_during_discussion'
1995 ) THEN
1996 'discussion'::"notify_level"
1997 ELSE
1998 'all'::"notify_level"
1999 END
2000 END
2001 END AS "notify_level",
2002 "event".*
2003 FROM "member" CROSS JOIN "event"
2004 LEFT JOIN "issue"
2005 ON "event"."issue_id" = "issue"."id"
2006 LEFT JOIN "membership"
2007 ON "member"."id" = "membership"."member_id"
2008 AND "issue"."area_id" = "membership"."area_id"
2009 LEFT JOIN "interest"
2010 ON "member"."id" = "interest"."member_id"
2011 AND "event"."issue_id" = "interest"."issue_id"
2012 LEFT JOIN "supporter"
2013 ON "member"."id" = "supporter"."member_id"
2014 AND "event"."initiative_id" = "supporter"."initiative_id"
2015 LEFT JOIN "ignored_member"
2016 ON "member"."id" = "ignored_member"."member_id"
2017 AND "event"."member_id" = "ignored_member"."other_member_id"
2018 LEFT JOIN "ignored_initiative"
2019 ON "member"."id" = "ignored_initiative"."member_id"
2020 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2021 WHERE (
2022 "supporter"."member_id" NOTNULL OR
2023 "interest"."member_id" NOTNULL OR
2024 ( "membership"."member_id" NOTNULL AND
2025 "event"."event" IN (
2026 'issue_state_changed',
2027 'initiative_created_in_new_issue',
2028 'initiative_created_in_existing_issue',
2029 'initiative_revoked' ) ) )
2030 AND "ignored_member"."member_id" ISNULL
2031 AND "ignored_initiative"."member_id" ISNULL;
2033 COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support';
2036 CREATE VIEW "pending_notification" AS
2037 SELECT
2038 "member"."id" AS "seen_by_member_id",
2039 "event".*
2040 FROM "member" CROSS JOIN "event"
2041 LEFT JOIN "issue"
2042 ON "event"."issue_id" = "issue"."id"
2043 LEFT JOIN "membership"
2044 ON "member"."id" = "membership"."member_id"
2045 AND "issue"."area_id" = "membership"."area_id"
2046 LEFT JOIN "interest"
2047 ON "member"."id" = "interest"."member_id"
2048 AND "event"."issue_id" = "interest"."issue_id"
2049 LEFT JOIN "supporter"
2050 ON "member"."id" = "supporter"."member_id"
2051 AND "event"."initiative_id" = "supporter"."initiative_id"
2052 LEFT JOIN "ignored_member"
2053 ON "member"."id" = "ignored_member"."member_id"
2054 AND "event"."member_id" = "ignored_member"."other_member_id"
2055 LEFT JOIN "ignored_initiative"
2056 ON "member"."id" = "ignored_initiative"."member_id"
2057 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2058 WHERE (
2059 "member"."notify_event_id" ISNULL OR
2060 ( "member"."notify_event_id" NOTNULL AND
2061 "member"."notify_event_id" < "event"."id" ) )
2062 AND (
2063 ( "member"."notify_level" >= 'all' ) OR
2064 ( "member"."notify_level" >= 'voting' AND
2065 "event"."state" IN (
2066 'voting',
2067 'finished_without_winner',
2068 'finished_with_winner' ) ) OR
2069 ( "member"."notify_level" >= 'verification' AND
2070 "event"."state" IN (
2071 'verification',
2072 'canceled_after_revocation_during_verification',
2073 'canceled_no_initiative_admitted' ) ) OR
2074 ( "member"."notify_level" >= 'discussion' AND
2075 "event"."state" IN (
2076 'discussion',
2077 'canceled_after_revocation_during_discussion' ) ) )
2078 AND (
2079 "supporter"."member_id" NOTNULL OR
2080 "interest"."member_id" NOTNULL OR
2081 ( "membership"."member_id" NOTNULL AND
2082 "event"."event" IN (
2083 'issue_state_changed',
2084 'initiative_created_in_new_issue',
2085 'initiative_created_in_existing_issue',
2086 'initiative_revoked' ) ) )
2087 AND "ignored_member"."member_id" ISNULL
2088 AND "ignored_initiative"."member_id" ISNULL;
2090 COMMENT ON VIEW "pending_notification" IS 'Events to be sent to "notify_email" address of member referred to by "seen_by_member_id"';
2093 CREATE TYPE "timeline_event" AS ENUM (
2094 'issue_created',
2095 'issue_canceled',
2096 'issue_accepted',
2097 'issue_half_frozen',
2098 'issue_finished_without_voting',
2099 'issue_voting_started',
2100 'issue_finished_after_voting',
2101 'initiative_created',
2102 'initiative_revoked',
2103 'draft_created',
2104 'suggestion_created');
2106 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
2109 CREATE VIEW "timeline_issue" AS
2110 SELECT
2111 "created" AS "occurrence",
2112 'issue_created'::"timeline_event" AS "event",
2113 "id" AS "issue_id"
2114 FROM "issue"
2115 UNION ALL
2116 SELECT
2117 "closed" AS "occurrence",
2118 'issue_canceled'::"timeline_event" AS "event",
2119 "id" AS "issue_id"
2120 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
2121 UNION ALL
2122 SELECT
2123 "accepted" AS "occurrence",
2124 'issue_accepted'::"timeline_event" AS "event",
2125 "id" AS "issue_id"
2126 FROM "issue" WHERE "accepted" NOTNULL
2127 UNION ALL
2128 SELECT
2129 "half_frozen" AS "occurrence",
2130 'issue_half_frozen'::"timeline_event" AS "event",
2131 "id" AS "issue_id"
2132 FROM "issue" WHERE "half_frozen" NOTNULL
2133 UNION ALL
2134 SELECT
2135 "fully_frozen" AS "occurrence",
2136 'issue_voting_started'::"timeline_event" AS "event",
2137 "id" AS "issue_id"
2138 FROM "issue"
2139 WHERE "fully_frozen" NOTNULL
2140 AND ("closed" ISNULL OR "closed" != "fully_frozen")
2141 UNION ALL
2142 SELECT
2143 "closed" AS "occurrence",
2144 CASE WHEN "fully_frozen" = "closed" THEN
2145 'issue_finished_without_voting'::"timeline_event"
2146 ELSE
2147 'issue_finished_after_voting'::"timeline_event"
2148 END AS "event",
2149 "id" AS "issue_id"
2150 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
2152 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
2155 CREATE VIEW "timeline_initiative" AS
2156 SELECT
2157 "created" AS "occurrence",
2158 'initiative_created'::"timeline_event" AS "event",
2159 "id" AS "initiative_id"
2160 FROM "initiative"
2161 UNION ALL
2162 SELECT
2163 "revoked" AS "occurrence",
2164 'initiative_revoked'::"timeline_event" AS "event",
2165 "id" AS "initiative_id"
2166 FROM "initiative" WHERE "revoked" NOTNULL;
2168 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
2171 CREATE VIEW "timeline_draft" AS
2172 SELECT
2173 "created" AS "occurrence",
2174 'draft_created'::"timeline_event" AS "event",
2175 "id" AS "draft_id"
2176 FROM "draft";
2178 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
2181 CREATE VIEW "timeline_suggestion" AS
2182 SELECT
2183 "created" AS "occurrence",
2184 'suggestion_created'::"timeline_event" AS "event",
2185 "id" AS "suggestion_id"
2186 FROM "suggestion";
2188 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
2191 CREATE VIEW "timeline" AS
2192 SELECT
2193 "occurrence",
2194 "event",
2195 "issue_id",
2196 NULL AS "initiative_id",
2197 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
2198 NULL::INT8 AS "suggestion_id"
2199 FROM "timeline_issue"
2200 UNION ALL
2201 SELECT
2202 "occurrence",
2203 "event",
2204 NULL AS "issue_id",
2205 "initiative_id",
2206 NULL AS "draft_id",
2207 NULL AS "suggestion_id"
2208 FROM "timeline_initiative"
2209 UNION ALL
2210 SELECT
2211 "occurrence",
2212 "event",
2213 NULL AS "issue_id",
2214 NULL AS "initiative_id",
2215 "draft_id",
2216 NULL AS "suggestion_id"
2217 FROM "timeline_draft"
2218 UNION ALL
2219 SELECT
2220 "occurrence",
2221 "event",
2222 NULL AS "issue_id",
2223 NULL AS "initiative_id",
2224 NULL AS "draft_id",
2225 "suggestion_id"
2226 FROM "timeline_suggestion";
2228 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
2232 --------------------------------------------------
2233 -- Set returning function for delegation chains --
2234 --------------------------------------------------
2237 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2238 ('first', 'intermediate', 'last', 'repetition');
2240 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2243 CREATE TYPE "delegation_chain_row" AS (
2244 "index" INT4,
2245 "member_id" INT4,
2246 "member_valid" BOOLEAN,
2247 "participation" BOOLEAN,
2248 "overridden" BOOLEAN,
2249 "scope_in" "delegation_scope",
2250 "scope_out" "delegation_scope",
2251 "disabled_out" BOOLEAN,
2252 "loop" "delegation_chain_loop_tag" );
2254 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
2256 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2257 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';
2258 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2259 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2260 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2261 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2262 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2265 CREATE FUNCTION "delegation_chain"
2266 ( "member_id_p" "member"."id"%TYPE,
2267 "unit_id_p" "unit"."id"%TYPE,
2268 "area_id_p" "area"."id"%TYPE,
2269 "issue_id_p" "issue"."id"%TYPE,
2270 "simulate_trustee_id_p" "member"."id"%TYPE )
2271 RETURNS SETOF "delegation_chain_row"
2272 LANGUAGE 'plpgsql' STABLE AS $$
2273 DECLARE
2274 "scope_v" "delegation_scope";
2275 "unit_id_v" "unit"."id"%TYPE;
2276 "area_id_v" "area"."id"%TYPE;
2277 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2278 "loop_member_id_v" "member"."id"%TYPE;
2279 "output_row" "delegation_chain_row";
2280 "output_rows" "delegation_chain_row"[];
2281 "delegation_row" "delegation"%ROWTYPE;
2282 "row_count" INT4;
2283 "i" INT4;
2284 "loop_v" BOOLEAN;
2285 BEGIN
2286 IF
2287 "unit_id_p" NOTNULL AND
2288 "area_id_p" ISNULL AND
2289 "issue_id_p" ISNULL
2290 THEN
2291 "scope_v" := 'unit';
2292 "unit_id_v" := "unit_id_p";
2293 ELSIF
2294 "unit_id_p" ISNULL AND
2295 "area_id_p" NOTNULL AND
2296 "issue_id_p" ISNULL
2297 THEN
2298 "scope_v" := 'area';
2299 "area_id_v" := "area_id_p";
2300 SELECT "unit_id" INTO "unit_id_v"
2301 FROM "area" WHERE "id" = "area_id_v";
2302 ELSIF
2303 "unit_id_p" ISNULL AND
2304 "area_id_p" ISNULL AND
2305 "issue_id_p" NOTNULL
2306 THEN
2307 "scope_v" := 'issue';
2308 SELECT "area_id" INTO "area_id_v"
2309 FROM "issue" WHERE "id" = "issue_id_p";
2310 SELECT "unit_id" INTO "unit_id_v"
2311 FROM "area" WHERE "id" = "area_id_v";
2312 ELSE
2313 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2314 END IF;
2315 "visited_member_ids" := '{}';
2316 "loop_member_id_v" := NULL;
2317 "output_rows" := '{}';
2318 "output_row"."index" := 0;
2319 "output_row"."member_id" := "member_id_p";
2320 "output_row"."member_valid" := TRUE;
2321 "output_row"."participation" := FALSE;
2322 "output_row"."overridden" := FALSE;
2323 "output_row"."disabled_out" := FALSE;
2324 "output_row"."scope_out" := NULL;
2325 LOOP
2326 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2327 "loop_member_id_v" := "output_row"."member_id";
2328 ELSE
2329 "visited_member_ids" :=
2330 "visited_member_ids" || "output_row"."member_id";
2331 END IF;
2332 IF "output_row"."participation" THEN
2333 "output_row"."overridden" := TRUE;
2334 END IF;
2335 "output_row"."scope_in" := "output_row"."scope_out";
2336 IF EXISTS (
2337 SELECT NULL FROM "member" JOIN "privilege"
2338 ON "privilege"."member_id" = "member"."id"
2339 AND "privilege"."unit_id" = "unit_id_v"
2340 WHERE "id" = "output_row"."member_id"
2341 AND "member"."active" AND "privilege"."voting_right"
2342 ) THEN
2343 IF "scope_v" = 'unit' THEN
2344 SELECT * INTO "delegation_row" FROM "delegation"
2345 WHERE "truster_id" = "output_row"."member_id"
2346 AND "unit_id" = "unit_id_v";
2347 ELSIF "scope_v" = 'area' THEN
2348 "output_row"."participation" := EXISTS (
2349 SELECT NULL FROM "membership"
2350 WHERE "area_id" = "area_id_p"
2351 AND "member_id" = "output_row"."member_id"
2352 );
2353 SELECT * INTO "delegation_row" FROM "delegation"
2354 WHERE "truster_id" = "output_row"."member_id"
2355 AND (
2356 "unit_id" = "unit_id_v" OR
2357 "area_id" = "area_id_v"
2358 )
2359 ORDER BY "scope" DESC;
2360 ELSIF "scope_v" = 'issue' THEN
2361 "output_row"."participation" := EXISTS (
2362 SELECT NULL FROM "interest"
2363 WHERE "issue_id" = "issue_id_p"
2364 AND "member_id" = "output_row"."member_id"
2365 );
2366 SELECT * INTO "delegation_row" FROM "delegation"
2367 WHERE "truster_id" = "output_row"."member_id"
2368 AND (
2369 "unit_id" = "unit_id_v" OR
2370 "area_id" = "area_id_v" OR
2371 "issue_id" = "issue_id_p"
2372 )
2373 ORDER BY "scope" DESC;
2374 END IF;
2375 ELSE
2376 "output_row"."member_valid" := FALSE;
2377 "output_row"."participation" := FALSE;
2378 "output_row"."scope_out" := NULL;
2379 "delegation_row" := ROW(NULL);
2380 END IF;
2381 IF
2382 "output_row"."member_id" = "member_id_p" AND
2383 "simulate_trustee_id_p" NOTNULL
2384 THEN
2385 "output_row"."scope_out" := "scope_v";
2386 "output_rows" := "output_rows" || "output_row";
2387 "output_row"."member_id" := "simulate_trustee_id_p";
2388 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2389 "output_row"."scope_out" := "delegation_row"."scope";
2390 "output_rows" := "output_rows" || "output_row";
2391 "output_row"."member_id" := "delegation_row"."trustee_id";
2392 ELSIF "delegation_row"."scope" NOTNULL THEN
2393 "output_row"."scope_out" := "delegation_row"."scope";
2394 "output_row"."disabled_out" := TRUE;
2395 "output_rows" := "output_rows" || "output_row";
2396 EXIT;
2397 ELSE
2398 "output_row"."scope_out" := NULL;
2399 "output_rows" := "output_rows" || "output_row";
2400 EXIT;
2401 END IF;
2402 EXIT WHEN "loop_member_id_v" NOTNULL;
2403 "output_row"."index" := "output_row"."index" + 1;
2404 END LOOP;
2405 "row_count" := array_upper("output_rows", 1);
2406 "i" := 1;
2407 "loop_v" := FALSE;
2408 LOOP
2409 "output_row" := "output_rows"["i"];
2410 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2411 IF "loop_v" THEN
2412 IF "i" + 1 = "row_count" THEN
2413 "output_row"."loop" := 'last';
2414 ELSIF "i" = "row_count" THEN
2415 "output_row"."loop" := 'repetition';
2416 ELSE
2417 "output_row"."loop" := 'intermediate';
2418 END IF;
2419 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2420 "output_row"."loop" := 'first';
2421 "loop_v" := TRUE;
2422 END IF;
2423 IF "scope_v" = 'unit' THEN
2424 "output_row"."participation" := NULL;
2425 END IF;
2426 RETURN NEXT "output_row";
2427 "i" := "i" + 1;
2428 END LOOP;
2429 RETURN;
2430 END;
2431 $$;
2433 COMMENT ON FUNCTION "delegation_chain"
2434 ( "member"."id"%TYPE,
2435 "unit"."id"%TYPE,
2436 "area"."id"%TYPE,
2437 "issue"."id"%TYPE,
2438 "member"."id"%TYPE )
2439 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
2442 CREATE FUNCTION "delegation_chain"
2443 ( "member_id_p" "member"."id"%TYPE,
2444 "unit_id_p" "unit"."id"%TYPE,
2445 "area_id_p" "area"."id"%TYPE,
2446 "issue_id_p" "issue"."id"%TYPE )
2447 RETURNS SETOF "delegation_chain_row"
2448 LANGUAGE 'plpgsql' STABLE AS $$
2449 DECLARE
2450 "result_row" "delegation_chain_row";
2451 BEGIN
2452 FOR "result_row" IN
2453 SELECT * FROM "delegation_chain"(
2454 "member_id_p", "unit_id_p", "area_id_p", "issue_id_p", NULL
2455 )
2456 LOOP
2457 RETURN NEXT "result_row";
2458 END LOOP;
2459 RETURN;
2460 END;
2461 $$;
2463 COMMENT ON FUNCTION "delegation_chain"
2464 ( "member"."id"%TYPE,
2465 "unit"."id"%TYPE,
2466 "area"."id"%TYPE,
2467 "issue"."id"%TYPE )
2468 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
2472 ------------------------------
2473 -- Comparison by vote count --
2474 ------------------------------
2476 CREATE FUNCTION "vote_ratio"
2477 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
2478 "negative_votes_p" "initiative"."negative_votes"%TYPE )
2479 RETURNS FLOAT8
2480 LANGUAGE 'plpgsql' STABLE AS $$
2481 BEGIN
2482 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
2483 RETURN
2484 "positive_votes_p"::FLOAT8 /
2485 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
2486 ELSIF "positive_votes_p" > 0 THEN
2487 RETURN "positive_votes_p";
2488 ELSIF "negative_votes_p" > 0 THEN
2489 RETURN 1 - "negative_votes_p";
2490 ELSE
2491 RETURN 0.5;
2492 END IF;
2493 END;
2494 $$;
2496 COMMENT ON FUNCTION "vote_ratio"
2497 ( "initiative"."positive_votes"%TYPE,
2498 "initiative"."negative_votes"%TYPE )
2499 IS 'Returns a number, which can be used for comparison of initiatives based on count of approvals and disapprovals. Greater numbers indicate a better result. This function is NOT injective.';
2503 ------------------------------------------------
2504 -- Locking for snapshots and voting procedure --
2505 ------------------------------------------------
2508 CREATE FUNCTION "share_row_lock_issue_trigger"()
2509 RETURNS TRIGGER
2510 LANGUAGE 'plpgsql' VOLATILE AS $$
2511 BEGIN
2512 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2513 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
2514 END IF;
2515 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2516 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
2517 RETURN NEW;
2518 ELSE
2519 RETURN OLD;
2520 END IF;
2521 END;
2522 $$;
2524 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
2527 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
2528 RETURNS TRIGGER
2529 LANGUAGE 'plpgsql' VOLATILE AS $$
2530 BEGIN
2531 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2532 PERFORM NULL FROM "issue"
2533 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2534 WHERE "initiative"."id" = OLD."initiative_id"
2535 FOR SHARE OF "issue";
2536 END IF;
2537 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2538 PERFORM NULL FROM "issue"
2539 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2540 WHERE "initiative"."id" = NEW."initiative_id"
2541 FOR SHARE OF "issue";
2542 RETURN NEW;
2543 ELSE
2544 RETURN OLD;
2545 END IF;
2546 END;
2547 $$;
2549 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
2552 CREATE TRIGGER "share_row_lock_issue"
2553 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
2554 FOR EACH ROW EXECUTE PROCEDURE
2555 "share_row_lock_issue_trigger"();
2557 CREATE TRIGGER "share_row_lock_issue"
2558 BEFORE INSERT OR UPDATE OR DELETE ON "interest"
2559 FOR EACH ROW EXECUTE PROCEDURE
2560 "share_row_lock_issue_trigger"();
2562 CREATE TRIGGER "share_row_lock_issue"
2563 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
2564 FOR EACH ROW EXECUTE PROCEDURE
2565 "share_row_lock_issue_trigger"();
2567 CREATE TRIGGER "share_row_lock_issue_via_initiative"
2568 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
2569 FOR EACH ROW EXECUTE PROCEDURE
2570 "share_row_lock_issue_via_initiative_trigger"();
2572 CREATE TRIGGER "share_row_lock_issue"
2573 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
2574 FOR EACH ROW EXECUTE PROCEDURE
2575 "share_row_lock_issue_trigger"();
2577 CREATE TRIGGER "share_row_lock_issue"
2578 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
2579 FOR EACH ROW EXECUTE PROCEDURE
2580 "share_row_lock_issue_trigger"();
2582 CREATE TRIGGER "share_row_lock_issue"
2583 BEFORE INSERT OR UPDATE OR DELETE ON "vote"
2584 FOR EACH ROW EXECUTE PROCEDURE
2585 "share_row_lock_issue_trigger"();
2587 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';
2588 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';
2589 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';
2590 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';
2591 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';
2592 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';
2593 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';
2596 CREATE FUNCTION "lock_issue"
2597 ( "issue_id_p" "issue"."id"%TYPE )
2598 RETURNS VOID
2599 LANGUAGE 'plpgsql' VOLATILE AS $$
2600 BEGIN
2601 LOCK TABLE "member" IN SHARE MODE;
2602 LOCK TABLE "privilege" IN SHARE MODE;
2603 LOCK TABLE "membership" IN SHARE MODE;
2604 LOCK TABLE "policy" IN SHARE MODE;
2605 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2606 -- NOTE: The row-level exclusive lock in combination with the
2607 -- share_row_lock_issue(_via_initiative)_trigger functions (which
2608 -- acquire a row-level share lock on the issue) ensure that no data
2609 -- is changed, which could affect calculation of snapshots or
2610 -- counting of votes. Table "delegation" must be table-level-locked,
2611 -- as it also contains issue- and global-scope delegations.
2612 LOCK TABLE "delegation" IN SHARE MODE;
2613 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
2614 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
2615 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
2616 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
2617 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
2618 RETURN;
2619 END;
2620 $$;
2622 COMMENT ON FUNCTION "lock_issue"
2623 ( "issue"."id"%TYPE )
2624 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
2628 ------------------------------------------------------------------------
2629 -- Regular tasks, except calculcation of snapshots and voting results --
2630 ------------------------------------------------------------------------
2632 CREATE FUNCTION "check_last_login"()
2633 RETURNS VOID
2634 LANGUAGE 'plpgsql' VOLATILE AS $$
2635 DECLARE
2636 "system_setting_row" "system_setting"%ROWTYPE;
2637 BEGIN
2638 SELECT * INTO "system_setting_row" FROM "system_setting";
2639 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
2640 UPDATE "member" SET "last_login_public" = "last_login"::date
2641 FROM (
2642 SELECT DISTINCT "member"."id"
2643 FROM "member" LEFT JOIN "member_history"
2644 ON "member"."id" = "member_history"."member_id"
2645 WHERE "member"."last_login"::date < 'today' OR (
2646 "member_history"."until"::date >= 'today' AND
2647 "member_history"."active" = FALSE AND "member"."active" = TRUE
2648 )
2649 ) AS "subquery"
2650 WHERE "member"."id" = "subquery"."id";
2651 IF "system_setting_row"."member_ttl" NOTNULL THEN
2652 UPDATE "member" SET "active" = FALSE
2653 WHERE "active" = TRUE
2654 AND "last_login"::date < 'today'
2655 AND "last_login_public" <
2656 (now() - "system_setting_row"."member_ttl")::date;
2657 END IF;
2658 RETURN;
2659 END;
2660 $$;
2662 COMMENT ON FUNCTION "check_last_login"() IS 'Updates "last_login_public" field, which contains the date but not the time of the last login, and deactivates members who do not login for the time specified in "system_setting"."member_ttl". For privacy reasons this function does not update "last_login_public", if the last login of a member has been today (except when member was reactivated today).';
2665 CREATE FUNCTION "calculate_member_counts"()
2666 RETURNS VOID
2667 LANGUAGE 'plpgsql' VOLATILE AS $$
2668 BEGIN
2669 LOCK TABLE "member" IN SHARE MODE;
2670 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
2671 LOCK TABLE "unit" IN EXCLUSIVE MODE;
2672 LOCK TABLE "area" IN EXCLUSIVE MODE;
2673 LOCK TABLE "privilege" IN SHARE MODE;
2674 LOCK TABLE "membership" IN SHARE MODE;
2675 DELETE FROM "member_count";
2676 INSERT INTO "member_count" ("total_count")
2677 SELECT "total_count" FROM "member_count_view";
2678 UPDATE "unit" SET "member_count" = "view"."member_count"
2679 FROM "unit_member_count" AS "view"
2680 WHERE "view"."unit_id" = "unit"."id";
2681 UPDATE "area" SET
2682 "direct_member_count" = "view"."direct_member_count",
2683 "member_weight" = "view"."member_weight",
2684 "autoreject_weight" = "view"."autoreject_weight"
2685 FROM "area_member_count" AS "view"
2686 WHERE "view"."area_id" = "area"."id";
2687 RETURN;
2688 END;
2689 $$;
2691 COMMENT ON FUNCTION "calculate_member_counts"() IS 'Updates "member_count" table and "member_count" column of table "area" by materializing data from views "member_count_view" and "area_member_count"';
2695 ------------------------------
2696 -- Calculation of snapshots --
2697 ------------------------------
2699 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
2700 ( "issue_id_p" "issue"."id"%TYPE,
2701 "member_id_p" "member"."id"%TYPE,
2702 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2703 RETURNS "direct_population_snapshot"."weight"%TYPE
2704 LANGUAGE 'plpgsql' VOLATILE AS $$
2705 DECLARE
2706 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2707 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
2708 "weight_v" INT4;
2709 "sub_weight_v" INT4;
2710 BEGIN
2711 "weight_v" := 0;
2712 FOR "issue_delegation_row" IN
2713 SELECT * FROM "issue_delegation"
2714 WHERE "trustee_id" = "member_id_p"
2715 AND "issue_id" = "issue_id_p"
2716 LOOP
2717 IF NOT EXISTS (
2718 SELECT NULL FROM "direct_population_snapshot"
2719 WHERE "issue_id" = "issue_id_p"
2720 AND "event" = 'periodic'
2721 AND "member_id" = "issue_delegation_row"."truster_id"
2722 ) AND NOT EXISTS (
2723 SELECT NULL FROM "delegating_population_snapshot"
2724 WHERE "issue_id" = "issue_id_p"
2725 AND "event" = 'periodic'
2726 AND "member_id" = "issue_delegation_row"."truster_id"
2727 ) THEN
2728 "delegate_member_ids_v" :=
2729 "member_id_p" || "delegate_member_ids_p";
2730 INSERT INTO "delegating_population_snapshot" (
2731 "issue_id",
2732 "event",
2733 "member_id",
2734 "scope",
2735 "delegate_member_ids"
2736 ) VALUES (
2737 "issue_id_p",
2738 'periodic',
2739 "issue_delegation_row"."truster_id",
2740 "issue_delegation_row"."scope",
2741 "delegate_member_ids_v"
2742 );
2743 "sub_weight_v" := 1 +
2744 "weight_of_added_delegations_for_population_snapshot"(
2745 "issue_id_p",
2746 "issue_delegation_row"."truster_id",
2747 "delegate_member_ids_v"
2748 );
2749 UPDATE "delegating_population_snapshot"
2750 SET "weight" = "sub_weight_v"
2751 WHERE "issue_id" = "issue_id_p"
2752 AND "event" = 'periodic'
2753 AND "member_id" = "issue_delegation_row"."truster_id";
2754 "weight_v" := "weight_v" + "sub_weight_v";
2755 END IF;
2756 END LOOP;
2757 RETURN "weight_v";
2758 END;
2759 $$;
2761 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
2762 ( "issue"."id"%TYPE,
2763 "member"."id"%TYPE,
2764 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2765 IS 'Helper function for "create_population_snapshot" function';
2768 CREATE FUNCTION "create_population_snapshot"
2769 ( "issue_id_p" "issue"."id"%TYPE )
2770 RETURNS VOID
2771 LANGUAGE 'plpgsql' VOLATILE AS $$
2772 DECLARE
2773 "member_id_v" "member"."id"%TYPE;
2774 BEGIN
2775 DELETE FROM "direct_population_snapshot"
2776 WHERE "issue_id" = "issue_id_p"
2777 AND "event" = 'periodic';
2778 DELETE FROM "delegating_population_snapshot"
2779 WHERE "issue_id" = "issue_id_p"
2780 AND "event" = 'periodic';
2781 INSERT INTO "direct_population_snapshot"
2782 ("issue_id", "event", "member_id")
2783 SELECT
2784 "issue_id_p" AS "issue_id",
2785 'periodic'::"snapshot_event" AS "event",
2786 "member"."id" AS "member_id"
2787 FROM "issue"
2788 JOIN "area" ON "issue"."area_id" = "area"."id"
2789 JOIN "membership" ON "area"."id" = "membership"."area_id"
2790 JOIN "member" ON "membership"."member_id" = "member"."id"
2791 JOIN "privilege"
2792 ON "privilege"."unit_id" = "area"."unit_id"
2793 AND "privilege"."member_id" = "member"."id"
2794 WHERE "issue"."id" = "issue_id_p"
2795 AND "member"."active" AND "privilege"."voting_right"
2796 UNION
2797 SELECT
2798 "issue_id_p" AS "issue_id",
2799 'periodic'::"snapshot_event" AS "event",
2800 "member"."id" AS "member_id"
2801 FROM "issue"
2802 JOIN "area" ON "issue"."area_id" = "area"."id"
2803 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2804 JOIN "member" ON "interest"."member_id" = "member"."id"
2805 JOIN "privilege"
2806 ON "privilege"."unit_id" = "area"."unit_id"
2807 AND "privilege"."member_id" = "member"."id"
2808 WHERE "issue"."id" = "issue_id_p"
2809 AND "member"."active" AND "privilege"."voting_right";
2810 FOR "member_id_v" IN
2811 SELECT "member_id" FROM "direct_population_snapshot"
2812 WHERE "issue_id" = "issue_id_p"
2813 AND "event" = 'periodic'
2814 LOOP
2815 UPDATE "direct_population_snapshot" SET
2816 "weight" = 1 +
2817 "weight_of_added_delegations_for_population_snapshot"(
2818 "issue_id_p",
2819 "member_id_v",
2820 '{}'
2821 )
2822 WHERE "issue_id" = "issue_id_p"
2823 AND "event" = 'periodic'
2824 AND "member_id" = "member_id_v";
2825 END LOOP;
2826 RETURN;
2827 END;
2828 $$;
2830 COMMENT ON FUNCTION "create_population_snapshot"
2831 ( "issue"."id"%TYPE )
2832 IS 'This function creates a new ''periodic'' population snapshot for the given issue. It does neither lock any tables, nor updates precalculated values in other tables.';
2835 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2836 ( "issue_id_p" "issue"."id"%TYPE,
2837 "member_id_p" "member"."id"%TYPE,
2838 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2839 RETURNS "direct_interest_snapshot"."weight"%TYPE
2840 LANGUAGE 'plpgsql' VOLATILE AS $$
2841 DECLARE
2842 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2843 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2844 "weight_v" INT4;
2845 "sub_weight_v" INT4;
2846 BEGIN
2847 "weight_v" := 0;
2848 FOR "issue_delegation_row" IN
2849 SELECT * FROM "issue_delegation"
2850 WHERE "trustee_id" = "member_id_p"
2851 AND "issue_id" = "issue_id_p"
2852 LOOP
2853 IF NOT EXISTS (
2854 SELECT NULL FROM "direct_interest_snapshot"
2855 WHERE "issue_id" = "issue_id_p"
2856 AND "event" = 'periodic'
2857 AND "member_id" = "issue_delegation_row"."truster_id"
2858 ) AND NOT EXISTS (
2859 SELECT NULL FROM "delegating_interest_snapshot"
2860 WHERE "issue_id" = "issue_id_p"
2861 AND "event" = 'periodic'
2862 AND "member_id" = "issue_delegation_row"."truster_id"
2863 ) THEN
2864 "delegate_member_ids_v" :=
2865 "member_id_p" || "delegate_member_ids_p";
2866 INSERT INTO "delegating_interest_snapshot" (
2867 "issue_id",
2868 "event",
2869 "member_id",
2870 "scope",
2871 "delegate_member_ids"
2872 ) VALUES (
2873 "issue_id_p",
2874 'periodic',
2875 "issue_delegation_row"."truster_id",
2876 "issue_delegation_row"."scope",
2877 "delegate_member_ids_v"
2878 );
2879 "sub_weight_v" := 1 +
2880 "weight_of_added_delegations_for_interest_snapshot"(
2881 "issue_id_p",
2882 "issue_delegation_row"."truster_id",
2883 "delegate_member_ids_v"
2884 );
2885 UPDATE "delegating_interest_snapshot"
2886 SET "weight" = "sub_weight_v"
2887 WHERE "issue_id" = "issue_id_p"
2888 AND "event" = 'periodic'
2889 AND "member_id" = "issue_delegation_row"."truster_id";
2890 "weight_v" := "weight_v" + "sub_weight_v";
2891 END IF;
2892 END LOOP;
2893 RETURN "weight_v";
2894 END;
2895 $$;
2897 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2898 ( "issue"."id"%TYPE,
2899 "member"."id"%TYPE,
2900 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2901 IS 'Helper function for "create_interest_snapshot" function';
2904 CREATE FUNCTION "create_interest_snapshot"
2905 ( "issue_id_p" "issue"."id"%TYPE )
2906 RETURNS VOID
2907 LANGUAGE 'plpgsql' VOLATILE AS $$
2908 DECLARE
2909 "member_id_v" "member"."id"%TYPE;
2910 BEGIN
2911 DELETE FROM "direct_interest_snapshot"
2912 WHERE "issue_id" = "issue_id_p"
2913 AND "event" = 'periodic';
2914 DELETE FROM "delegating_interest_snapshot"
2915 WHERE "issue_id" = "issue_id_p"
2916 AND "event" = 'periodic';
2917 DELETE FROM "direct_supporter_snapshot"
2918 WHERE "issue_id" = "issue_id_p"
2919 AND "event" = 'periodic';
2920 INSERT INTO "direct_interest_snapshot"
2921 ("issue_id", "event", "member_id", "voting_requested")
2922 SELECT
2923 "issue_id_p" AS "issue_id",
2924 'periodic' AS "event",
2925 "member"."id" AS "member_id",
2926 "interest"."voting_requested"
2927 FROM "issue"
2928 JOIN "area" ON "issue"."area_id" = "area"."id"
2929 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2930 JOIN "member" ON "interest"."member_id" = "member"."id"
2931 JOIN "privilege"
2932 ON "privilege"."unit_id" = "area"."unit_id"
2933 AND "privilege"."member_id" = "member"."id"
2934 WHERE "issue"."id" = "issue_id_p"
2935 AND "member"."active" AND "privilege"."voting_right";
2936 FOR "member_id_v" IN
2937 SELECT "member_id" FROM "direct_interest_snapshot"
2938 WHERE "issue_id" = "issue_id_p"
2939 AND "event" = 'periodic'
2940 LOOP
2941 UPDATE "direct_interest_snapshot" SET
2942 "weight" = 1 +
2943 "weight_of_added_delegations_for_interest_snapshot"(
2944 "issue_id_p",
2945 "member_id_v",
2946 '{}'
2947 )
2948 WHERE "issue_id" = "issue_id_p"
2949 AND "event" = 'periodic'
2950 AND "member_id" = "member_id_v";
2951 END LOOP;
2952 INSERT INTO "direct_supporter_snapshot"
2953 ( "issue_id", "initiative_id", "event", "member_id",
2954 "informed", "satisfied" )
2955 SELECT
2956 "issue_id_p" AS "issue_id",
2957 "initiative"."id" AS "initiative_id",
2958 'periodic' AS "event",
2959 "supporter"."member_id" AS "member_id",
2960 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2961 NOT EXISTS (
2962 SELECT NULL FROM "critical_opinion"
2963 WHERE "initiative_id" = "initiative"."id"
2964 AND "member_id" = "supporter"."member_id"
2965 ) AS "satisfied"
2966 FROM "initiative"
2967 JOIN "supporter"
2968 ON "supporter"."initiative_id" = "initiative"."id"
2969 JOIN "current_draft"
2970 ON "initiative"."id" = "current_draft"."initiative_id"
2971 JOIN "direct_interest_snapshot"
2972 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
2973 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
2974 AND "event" = 'periodic'
2975 WHERE "initiative"."issue_id" = "issue_id_p";
2976 RETURN;
2977 END;
2978 $$;
2980 COMMENT ON FUNCTION "create_interest_snapshot"
2981 ( "issue"."id"%TYPE )
2982 IS 'This function creates a new ''periodic'' interest/supporter snapshot for the given issue. It does neither lock any tables, nor updates precalculated values in other tables.';
2985 CREATE FUNCTION "create_snapshot"
2986 ( "issue_id_p" "issue"."id"%TYPE )
2987 RETURNS VOID
2988 LANGUAGE 'plpgsql' VOLATILE AS $$
2989 DECLARE
2990 "initiative_id_v" "initiative"."id"%TYPE;
2991 "suggestion_id_v" "suggestion"."id"%TYPE;
2992 BEGIN
2993 PERFORM "lock_issue"("issue_id_p");
2994 PERFORM "create_population_snapshot"("issue_id_p");
2995 PERFORM "create_interest_snapshot"("issue_id_p");
2996 UPDATE "issue" SET
2997 "snapshot" = now(),
2998 "latest_snapshot_event" = 'periodic',
2999 "population" = (
3000 SELECT coalesce(sum("weight"), 0)
3001 FROM "direct_population_snapshot"
3002 WHERE "issue_id" = "issue_id_p"
3003 AND "event" = 'periodic'
3004 ),
3005 "vote_now" = (
3006 SELECT coalesce(sum("weight"), 0)
3007 FROM "direct_interest_snapshot"
3008 WHERE "issue_id" = "issue_id_p"
3009 AND "event" = 'periodic'
3010 AND "voting_requested" = TRUE
3011 ),
3012 "vote_later" = (
3013 SELECT coalesce(sum("weight"), 0)
3014 FROM "direct_interest_snapshot"
3015 WHERE "issue_id" = "issue_id_p"
3016 AND "event" = 'periodic'
3017 AND "voting_requested" = FALSE
3018 )
3019 WHERE "id" = "issue_id_p";
3020 FOR "initiative_id_v" IN
3021 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
3022 LOOP
3023 UPDATE "initiative" SET
3024 "supporter_count" = (
3025 SELECT coalesce(sum("di"."weight"), 0)
3026 FROM "direct_interest_snapshot" AS "di"
3027 JOIN "direct_supporter_snapshot" AS "ds"
3028 ON "di"."member_id" = "ds"."member_id"
3029 WHERE "di"."issue_id" = "issue_id_p"
3030 AND "di"."event" = 'periodic'
3031 AND "ds"."initiative_id" = "initiative_id_v"
3032 AND "ds"."event" = 'periodic'
3033 ),
3034 "informed_supporter_count" = (
3035 SELECT coalesce(sum("di"."weight"), 0)
3036 FROM "direct_interest_snapshot" AS "di"
3037 JOIN "direct_supporter_snapshot" AS "ds"
3038 ON "di"."member_id" = "ds"."member_id"
3039 WHERE "di"."issue_id" = "issue_id_p"
3040 AND "di"."event" = 'periodic'
3041 AND "ds"."initiative_id" = "initiative_id_v"
3042 AND "ds"."event" = 'periodic'
3043 AND "ds"."informed"
3044 ),
3045 "satisfied_supporter_count" = (
3046 SELECT coalesce(sum("di"."weight"), 0)
3047 FROM "direct_interest_snapshot" AS "di"
3048 JOIN "direct_supporter_snapshot" AS "ds"
3049 ON "di"."member_id" = "ds"."member_id"
3050 WHERE "di"."issue_id" = "issue_id_p"
3051 AND "di"."event" = 'periodic'
3052 AND "ds"."initiative_id" = "initiative_id_v"
3053 AND "ds"."event" = 'periodic'
3054 AND "ds"."satisfied"
3055 ),
3056 "satisfied_informed_supporter_count" = (
3057 SELECT coalesce(sum("di"."weight"), 0)
3058 FROM "direct_interest_snapshot" AS "di"
3059 JOIN "direct_supporter_snapshot" AS "ds"
3060 ON "di"."member_id" = "ds"."member_id"
3061 WHERE "di"."issue_id" = "issue_id_p"
3062 AND "di"."event" = 'periodic'
3063 AND "ds"."initiative_id" = "initiative_id_v"
3064 AND "ds"."event" = 'periodic'
3065 AND "ds"."informed"
3066 AND "ds"."satisfied"
3067 )
3068 WHERE "id" = "initiative_id_v";
3069 FOR "suggestion_id_v" IN
3070 SELECT "id" FROM "suggestion"
3071 WHERE "initiative_id" = "initiative_id_v"
3072 LOOP
3073 UPDATE "suggestion" SET
3074 "minus2_unfulfilled_count" = (
3075 SELECT coalesce(sum("snapshot"."weight"), 0)
3076 FROM "issue" CROSS JOIN "opinion"
3077 JOIN "direct_interest_snapshot" AS "snapshot"
3078 ON "snapshot"."issue_id" = "issue"."id"
3079 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3080 AND "snapshot"."member_id" = "opinion"."member_id"
3081 WHERE "issue"."id" = "issue_id_p"
3082 AND "opinion"."suggestion_id" = "suggestion_id_v"
3083 AND "opinion"."degree" = -2
3084 AND "opinion"."fulfilled" = FALSE
3085 ),
3086 "minus2_fulfilled_count" = (
3087 SELECT coalesce(sum("snapshot"."weight"), 0)
3088 FROM "issue" CROSS JOIN "opinion"
3089 JOIN "direct_interest_snapshot" AS "snapshot"
3090 ON "snapshot"."issue_id" = "issue"."id"
3091 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3092 AND "snapshot"."member_id" = "opinion"."member_id"
3093 WHERE "issue"."id" = "issue_id_p"
3094 AND "opinion"."suggestion_id" = "suggestion_id_v"
3095 AND "opinion"."degree" = -2
3096 AND "opinion"."fulfilled" = TRUE
3097 ),
3098 "minus1_unfulfilled_count" = (
3099 SELECT coalesce(sum("snapshot"."weight"), 0)
3100 FROM "issue" CROSS JOIN "opinion"
3101 JOIN "direct_interest_snapshot" AS "snapshot"
3102 ON "snapshot"."issue_id" = "issue"."id"
3103 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3104 AND "snapshot"."member_id" = "opinion"."member_id"
3105 WHERE "issue"."id" = "issue_id_p"
3106 AND "opinion"."suggestion_id" = "suggestion_id_v"
3107 AND "opinion"."degree" = -1
3108 AND "opinion"."fulfilled" = FALSE
3109 ),
3110 "minus1_fulfilled_count" = (
3111 SELECT coalesce(sum("snapshot"."weight"), 0)
3112 FROM "issue" CROSS JOIN "opinion"
3113 JOIN "direct_interest_snapshot" AS "snapshot"
3114 ON "snapshot"."issue_id" = "issue"."id"
3115 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3116 AND "snapshot"."member_id" = "opinion"."member_id"
3117 WHERE "issue"."id" = "issue_id_p"
3118 AND "opinion"."suggestion_id" = "suggestion_id_v"
3119 AND "opinion"."degree" = -1
3120 AND "opinion"."fulfilled" = TRUE
3121 ),
3122 "plus1_unfulfilled_count" = (
3123 SELECT coalesce(sum("snapshot"."weight"), 0)
3124 FROM "issue" CROSS JOIN "opinion"
3125 JOIN "direct_interest_snapshot" AS "snapshot"
3126 ON "snapshot"."issue_id" = "issue"."id"
3127 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3128 AND "snapshot"."member_id" = "opinion"."member_id"
3129 WHERE "issue"."id" = "issue_id_p"
3130 AND "opinion"."suggestion_id" = "suggestion_id_v"
3131 AND "opinion"."degree" = 1
3132 AND "opinion"."fulfilled" = FALSE
3133 ),
3134 "plus1_fulfilled_count" = (
3135 SELECT coalesce(sum("snapshot"."weight"), 0)
3136 FROM "issue" CROSS JOIN "opinion"
3137 JOIN "direct_interest_snapshot" AS "snapshot"
3138 ON "snapshot"."issue_id" = "issue"."id"
3139 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3140 AND "snapshot"."member_id" = "opinion"."member_id"
3141 WHERE "issue"."id" = "issue_id_p"
3142 AND "opinion"."suggestion_id" = "suggestion_id_v"
3143 AND "opinion"."degree" = 1
3144 AND "opinion"."fulfilled" = TRUE
3145 ),
3146 "plus2_unfulfilled_count" = (
3147 SELECT coalesce(sum("snapshot"."weight"), 0)
3148 FROM "issue" CROSS JOIN "opinion"
3149 JOIN "direct_interest_snapshot" AS "snapshot"
3150 ON "snapshot"."issue_id" = "issue"."id"
3151 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3152 AND "snapshot"."member_id" = "opinion"."member_id"
3153 WHERE "issue"."id" = "issue_id_p"
3154 AND "opinion"."suggestion_id" = "suggestion_id_v"
3155 AND "opinion"."degree" = 2
3156 AND "opinion"."fulfilled" = FALSE
3157 ),
3158 "plus2_fulfilled_count" = (
3159 SELECT coalesce(sum("snapshot"."weight"), 0)
3160 FROM "issue" CROSS JOIN "opinion"
3161 JOIN "direct_interest_snapshot" AS "snapshot"
3162 ON "snapshot"."issue_id" = "issue"."id"
3163 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3164 AND "snapshot"."member_id" = "opinion"."member_id"
3165 WHERE "issue"."id" = "issue_id_p"
3166 AND "opinion"."suggestion_id" = "suggestion_id_v"
3167 AND "opinion"."degree" = 2
3168 AND "opinion"."fulfilled" = TRUE
3169 )
3170 WHERE "suggestion"."id" = "suggestion_id_v";
3171 END LOOP;
3172 END LOOP;
3173 RETURN;
3174 END;
3175 $$;
3177 COMMENT ON FUNCTION "create_snapshot"
3178 ( "issue"."id"%TYPE )
3179 IS 'This function creates a complete new ''periodic'' snapshot of population, interest and support for the given issue. All involved tables are locked, and after completion precalculated values in the source tables are updated.';
3182 CREATE FUNCTION "set_snapshot_event"
3183 ( "issue_id_p" "issue"."id"%TYPE,
3184 "event_p" "snapshot_event" )
3185 RETURNS VOID
3186 LANGUAGE 'plpgsql' VOLATILE AS $$
3187 DECLARE
3188 "event_v" "issue"."latest_snapshot_event"%TYPE;
3189 BEGIN
3190 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
3191 WHERE "id" = "issue_id_p" FOR UPDATE;
3192 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
3193 WHERE "id" = "issue_id_p";
3194 UPDATE "direct_population_snapshot" SET "event" = "event_p"
3195 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3196 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
3197 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3198 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
3199 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3200 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
3201 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3202 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
3203 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3204 RETURN;
3205 END;
3206 $$;
3208 COMMENT ON FUNCTION "set_snapshot_event"
3209 ( "issue"."id"%TYPE,
3210 "snapshot_event" )
3211 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
3215 ---------------------
3216 -- Freezing issues --
3217 ---------------------
3219 CREATE FUNCTION "freeze_after_snapshot"
3220 ( "issue_id_p" "issue"."id"%TYPE )
3221 RETURNS VOID
3222 LANGUAGE 'plpgsql' VOLATILE AS $$
3223 DECLARE
3224 "issue_row" "issue"%ROWTYPE;
3225 "policy_row" "policy"%ROWTYPE;
3226 "initiative_row" "initiative"%ROWTYPE;
3227 BEGIN
3228 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3229 SELECT * INTO "policy_row"
3230 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3231 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
3232 FOR "initiative_row" IN
3233 SELECT * FROM "initiative"
3234 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3235 LOOP
3236 IF
3237 "initiative_row"."satisfied_supporter_count" > 0 AND
3238 "initiative_row"."satisfied_supporter_count" *
3239 "policy_row"."initiative_quorum_den" >=
3240 "issue_row"."population" * "policy_row"."initiative_quorum_num"
3241 THEN
3242 UPDATE "initiative" SET "admitted" = TRUE
3243 WHERE "id" = "initiative_row"."id";
3244 ELSE
3245 UPDATE "initiative" SET "admitted" = FALSE
3246 WHERE "id" = "initiative_row"."id";
3247 END IF;
3248 END LOOP;
3249 IF EXISTS (
3250 SELECT NULL FROM "initiative"
3251 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
3252 ) THEN
3253 UPDATE "issue" SET
3254 "state" = 'voting',
3255 "accepted" = coalesce("accepted", now()),
3256 "half_frozen" = coalesce("half_frozen", now()),
3257 "fully_frozen" = now()
3258 WHERE "id" = "issue_id_p";
3259 ELSE
3260 UPDATE "issue" SET
3261 "state" = 'canceled_no_initiative_admitted',
3262 "accepted" = coalesce("accepted", now()),
3263 "half_frozen" = coalesce("half_frozen", now()),
3264 "fully_frozen" = now(),
3265 "closed" = now(),
3266 "ranks_available" = TRUE
3267 WHERE "id" = "issue_id_p";
3268 -- NOTE: The following DELETE statements have effect only when
3269 -- issue state has been manipulated
3270 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
3271 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
3272 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3273 END IF;
3274 RETURN;
3275 END;
3276 $$;
3278 COMMENT ON FUNCTION "freeze_after_snapshot"
3279 ( "issue"."id"%TYPE )
3280 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
3283 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
3284 RETURNS VOID
3285 LANGUAGE 'plpgsql' VOLATILE AS $$
3286 DECLARE
3287 "issue_row" "issue"%ROWTYPE;
3288 BEGIN
3289 PERFORM "create_snapshot"("issue_id_p");
3290 PERFORM "freeze_after_snapshot"("issue_id_p");
3291 RETURN;
3292 END;
3293 $$;
3295 COMMENT ON FUNCTION "manual_freeze"
3296 ( "issue"."id"%TYPE )
3297 IS 'Freeze an issue manually (fully) and start voting';
3301 -----------------------
3302 -- Counting of votes --
3303 -----------------------
3306 CREATE FUNCTION "weight_of_added_vote_delegations"
3307 ( "issue_id_p" "issue"."id"%TYPE,
3308 "member_id_p" "member"."id"%TYPE,
3309 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
3310 RETURNS "direct_voter"."weight"%TYPE
3311 LANGUAGE 'plpgsql' VOLATILE AS $$
3312 DECLARE
3313 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3314 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
3315 "weight_v" INT4;
3316 "sub_weight_v" INT4;
3317 BEGIN
3318 "weight_v" := 0;
3319 FOR "issue_delegation_row" IN
3320 SELECT * FROM "issue_delegation"
3321 WHERE "trustee_id" = "member_id_p"
3322 AND "issue_id" = "issue_id_p"
3323 LOOP
3324 IF NOT EXISTS (
3325 SELECT NULL FROM "direct_voter"
3326 WHERE "member_id" = "issue_delegation_row"."truster_id"
3327 AND "issue_id" = "issue_id_p"
3328 ) AND NOT EXISTS (
3329 SELECT NULL FROM "delegating_voter"
3330 WHERE "member_id" = "issue_delegation_row"."truster_id"
3331 AND "issue_id" = "issue_id_p"
3332 ) THEN
3333 "delegate_member_ids_v" :=
3334 "member_id_p" || "delegate_member_ids_p";
3335 INSERT INTO "delegating_voter" (
3336 "issue_id",
3337 "member_id",
3338 "scope",
3339 "delegate_member_ids"
3340 ) VALUES (
3341 "issue_id_p",
3342 "issue_delegation_row"."truster_id",
3343 "issue_delegation_row"."scope",
3344 "delegate_member_ids_v"
3345 );
3346 "sub_weight_v" := 1 +
3347 "weight_of_added_vote_delegations"(
3348 "issue_id_p",
3349 "issue_delegation_row"."truster_id",
3350 "delegate_member_ids_v"
3351 );
3352 UPDATE "delegating_voter"
3353 SET "weight" = "sub_weight_v"
3354 WHERE "issue_id" = "issue_id_p"
3355 AND "member_id" = "issue_delegation_row"."truster_id";
3356 "weight_v" := "weight_v" + "sub_weight_v";
3357 END IF;
3358 END LOOP;
3359 RETURN "weight_v";
3360 END;
3361 $$;
3363 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
3364 ( "issue"."id"%TYPE,
3365 "member"."id"%TYPE,
3366 "delegating_voter"."delegate_member_ids"%TYPE )
3367 IS 'Helper function for "add_vote_delegations" function';
3370 CREATE FUNCTION "add_vote_delegations"
3371 ( "issue_id_p" "issue"."id"%TYPE )
3372 RETURNS VOID
3373 LANGUAGE 'plpgsql' VOLATILE AS $$
3374 DECLARE
3375 "member_id_v" "member"."id"%TYPE;
3376 BEGIN
3377 FOR "member_id_v" IN
3378 SELECT "member_id" FROM "direct_voter"
3379 WHERE "issue_id" = "issue_id_p"
3380 LOOP
3381 UPDATE "direct_voter" SET
3382 "weight" = "weight" + "weight_of_added_vote_delegations"(
3383 "issue_id_p",
3384 "member_id_v",
3385 '{}'
3386 )
3387 WHERE "member_id" = "member_id_v"
3388 AND "issue_id" = "issue_id_p";
3389 END LOOP;
3390 RETURN;
3391 END;
3392 $$;
3394 COMMENT ON FUNCTION "add_vote_delegations"
3395 ( "issue_id_p" "issue"."id"%TYPE )
3396 IS 'Helper function for "close_voting" function';
3399 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
3400 RETURNS VOID
3401 LANGUAGE 'plpgsql' VOLATILE AS $$
3402 DECLARE
3403 "area_id_v" "area"."id"%TYPE;
3404 "unit_id_v" "unit"."id"%TYPE;
3405 "member_id_v" "member"."id"%TYPE;
3406 BEGIN
3407 PERFORM "lock_issue"("issue_id_p");
3408 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
3409 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
3410 DELETE FROM "delegating_voter"
3411 WHERE "issue_id" = "issue_id_p";
3412 DELETE FROM "direct_voter"
3413 WHERE "issue_id" = "issue_id_p"
3414 AND "autoreject" = TRUE;
3415 DELETE FROM "direct_voter"
3416 USING (
3417 SELECT
3418 "direct_voter"."member_id"
3419 FROM "direct_voter"
3420 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
3421 LEFT JOIN "privilege"
3422 ON "privilege"."unit_id" = "unit_id_v"
3423 AND "privilege"."member_id" = "direct_voter"."member_id"
3424 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
3425 "member"."active" = FALSE OR
3426 "privilege"."voting_right" ISNULL OR
3427 "privilege"."voting_right" = FALSE
3428 )
3429 ) AS "subquery"
3430 WHERE "direct_voter"."issue_id" = "issue_id_p"
3431 AND "direct_voter"."member_id" = "subquery"."member_id";
3432 UPDATE "direct_voter" SET "weight" = 1
3433 WHERE "issue_id" = "issue_id_p";
3434 PERFORM "add_vote_delegations"("issue_id_p");
3435 FOR "member_id_v" IN
3436 SELECT "interest"."member_id"
3437 FROM "interest"
3438 JOIN "member"
3439 ON "interest"."member_id" = "member"."id"
3440 JOIN "privilege"
3441 ON "privilege"."unit_id" = "unit_id_v"
3442 AND "privilege"."member_id" = "member"."id"
3443 LEFT JOIN "direct_voter"
3444 ON "interest"."member_id" = "direct_voter"."member_id"
3445 AND "interest"."issue_id" = "direct_voter"."issue_id"
3446 LEFT JOIN "delegating_voter"
3447 ON "interest"."member_id" = "delegating_voter"."member_id"
3448 AND "interest"."issue_id" = "delegating_voter"."issue_id"
3449 WHERE "interest"."issue_id" = "issue_id_p"
3450 AND "interest"."autoreject" = TRUE
3451 AND "member"."active"
3452 AND "privilege"."voting_right"
3453 AND "direct_voter"."member_id" ISNULL
3454 AND "delegating_voter"."member_id" ISNULL
3455 UNION SELECT "membership"."member_id"
3456 FROM "membership"
3457 JOIN "member"
3458 ON "membership"."member_id" = "member"."id"
3459 JOIN "privilege"
3460 ON "privilege"."unit_id" = "unit_id_v"
3461 AND "privilege"."member_id" = "member"."id"
3462 LEFT JOIN "interest"
3463 ON "membership"."member_id" = "interest"."member_id"
3464 AND "interest"."issue_id" = "issue_id_p"
3465 LEFT JOIN "direct_voter"
3466 ON "membership"."member_id" = "direct_voter"."member_id"
3467 AND "direct_voter"."issue_id" = "issue_id_p"
3468 LEFT JOIN "delegating_voter"
3469 ON "membership"."member_id" = "delegating_voter"."member_id"
3470 AND "delegating_voter"."issue_id" = "issue_id_p"
3471 WHERE "membership"."area_id" = "area_id_v"
3472 AND "membership"."autoreject" = TRUE
3473 AND "member"."active"
3474 AND "privilege"."voting_right"
3475 AND "interest"."autoreject" ISNULL
3476 AND "direct_voter"."member_id" ISNULL
3477 AND "delegating_voter"."member_id" ISNULL
3478 LOOP
3479 INSERT INTO "direct_voter"
3480 ("member_id", "issue_id", "weight", "autoreject") VALUES
3481 ("member_id_v", "issue_id_p", 1, TRUE);
3482 INSERT INTO "vote" (
3483 "member_id",
3484 "issue_id",
3485 "initiative_id",
3486 "grade"
3487 ) SELECT
3488 "member_id_v" AS "member_id",
3489 "issue_id_p" AS "issue_id",
3490 "id" AS "initiative_id",
3491 -1 AS "grade"
3492 FROM "initiative"
3493 WHERE "issue_id" = "issue_id_p" AND "admitted";
3494 END LOOP;
3495 PERFORM "add_vote_delegations"("issue_id_p");
3496 UPDATE "issue" SET
3497 "state" = 'calculation',
3498 "closed" = now(),
3499 "voter_count" = (
3500 SELECT coalesce(sum("weight"), 0)
3501 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
3502 )
3503 WHERE "id" = "issue_id_p";
3504 UPDATE "initiative" SET
3505 "positive_votes" = "vote_counts"."positive_votes",
3506 "negative_votes" = "vote_counts"."negative_votes",
3507 "agreed" = CASE WHEN "majority_strict" THEN
3508 "vote_counts"."positive_votes" * "majority_den" >
3509 "majority_num" *
3510 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
3511 ELSE
3512 "vote_counts"."positive_votes" * "majority_den" >=
3513 "majority_num" *
3514 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
3515 END
3516 FROM
3517 ( SELECT
3518 "initiative"."id" AS "initiative_id",
3519 coalesce(
3520 sum(
3521 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
3522 ),
3523 0
3524 ) AS "positive_votes",
3525 coalesce(
3526 sum(
3527 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
3528 ),
3529 0
3530 ) AS "negative_votes"
3531 FROM "initiative"
3532 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
3533 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
3534 LEFT JOIN "direct_voter"
3535 ON "direct_voter"."issue_id" = "initiative"."issue_id"
3536 LEFT JOIN "vote"
3537 ON "vote"."initiative_id" = "initiative"."id"
3538 AND "vote"."member_id" = "direct_voter"."member_id"
3539 WHERE "initiative"."issue_id" = "issue_id_p"
3540 AND "initiative"."admitted" -- NOTE: NULL case is handled too
3541 GROUP BY "initiative"."id"
3542 ) AS "vote_counts",
3543 "issue",
3544 "policy"
3545 WHERE "vote_counts"."initiative_id" = "initiative"."id"
3546 AND "issue"."id" = "initiative"."issue_id"
3547 AND "policy"."id" = "issue"."policy_id";
3548 -- NOTE: "closed" column of issue must be set at this point
3549 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3550 INSERT INTO "battle" (
3551 "issue_id",
3552 "winning_initiative_id", "losing_initiative_id",
3553 "count"
3554 ) SELECT
3555 "issue_id",
3556 "winning_initiative_id", "losing_initiative_id",
3557 "count"
3558 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
3559 END;
3560 $$;
3562 COMMENT ON FUNCTION "close_voting"
3563 ( "issue"."id"%TYPE )
3564 IS 'Closes the voting on an issue, and calculates positive and negative votes for each initiative; The ranking is not calculated yet, to keep the (locking) transaction short.';
3567 CREATE FUNCTION "defeat_strength"
3568 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
3569 RETURNS INT8
3570 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3571 BEGIN
3572 IF "positive_votes_p" > "negative_votes_p" THEN
3573 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
3574 ELSIF "positive_votes_p" = "negative_votes_p" THEN
3575 RETURN 0;
3576 ELSE
3577 RETURN -1;
3578 END IF;
3579 END;
3580 $$;
3582 COMMENT ON FUNCTION "defeat_strength"(INT4, INT4) IS 'Calculates defeat strength (INT8!) of a pairwise defeat primarily by the absolute number of votes for the winner and secondarily by the absolute number of votes for the loser';
3585 CREATE FUNCTION "array_init_string"("dim_p" INTEGER)
3586 RETURNS TEXT
3587 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3588 DECLARE
3589 "i" INTEGER;
3590 "ary_text_v" TEXT;
3591 BEGIN
3592 IF "dim_p" >= 1 THEN
3593 "ary_text_v" := '{NULL';
3594 "i" := "dim_p";
3595 LOOP
3596 "i" := "i" - 1;
3597 EXIT WHEN "i" = 0;
3598 "ary_text_v" := "ary_text_v" || ',NULL';
3599 END LOOP;
3600 "ary_text_v" := "ary_text_v" || '}';
3601 RETURN "ary_text_v";
3602 ELSE
3603 RAISE EXCEPTION 'Dimension needs to be at least 1.';
3604 END IF;
3605 END;
3606 $$;
3608 COMMENT ON FUNCTION "array_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
3611 CREATE FUNCTION "square_matrix_init_string"("dim_p" INTEGER)
3612 RETURNS TEXT
3613 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3614 DECLARE
3615 "i" INTEGER;
3616 "row_text_v" TEXT;
3617 "ary_text_v" TEXT;
3618 BEGIN
3619 IF "dim_p" >= 1 THEN
3620 "row_text_v" := '{NULL';
3621 "i" := "dim_p";
3622 LOOP
3623 "i" := "i" - 1;
3624 EXIT WHEN "i" = 0;
3625 "row_text_v" := "row_text_v" || ',NULL';
3626 END LOOP;
3627 "row_text_v" := "row_text_v" || '}';
3628 "ary_text_v" := '{' || "row_text_v";
3629 "i" := "dim_p";
3630 LOOP
3631 "i" := "i" - 1;
3632 EXIT WHEN "i" = 0;
3633 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
3634 END LOOP;
3635 "ary_text_v" := "ary_text_v" || '}';
3636 RETURN "ary_text_v";
3637 ELSE
3638 RAISE EXCEPTION 'Dimension needs to be at least 1.';
3639 END IF;
3640 END;
3641 $$;
3643 COMMENT ON FUNCTION "square_matrix_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
3646 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
3647 RETURNS VOID
3648 LANGUAGE 'plpgsql' VOLATILE AS $$
3649 DECLARE
3650 "dimension_v" INTEGER;
3651 "vote_matrix" INT4[][]; -- absolute votes
3652 "matrix" INT8[][]; -- defeat strength / best paths
3653 "i" INTEGER;
3654 "j" INTEGER;
3655 "k" INTEGER;
3656 "battle_row" "battle"%ROWTYPE;
3657 "rank_ary" INT4[];
3658 "rank_v" INT4;
3659 "done_v" INTEGER;
3660 "winners_ary" INTEGER[];
3661 "initiative_id_v" "initiative"."id"%TYPE;
3662 BEGIN
3663 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
3664 SELECT count(1) INTO "dimension_v" FROM "initiative"
3665 WHERE "issue_id" = "issue_id_p" AND "agreed";
3666 IF "dimension_v" = 1 THEN
3667 UPDATE "initiative" SET "rank" = 1
3668 WHERE "issue_id" = "issue_id_p" AND "agreed";
3669 ELSIF "dimension_v" > 1 THEN
3670 -- Create "vote_matrix" with absolute number of votes in pairwise
3671 -- comparison:
3672 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3673 "i" := 1;
3674 "j" := 2;
3675 FOR "battle_row" IN
3676 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
3677 ORDER BY "winning_initiative_id", "losing_initiative_id"
3678 LOOP
3679 "vote_matrix"["i"]["j"] := "battle_row"."count";
3680 IF "j" = "dimension_v" THEN
3681 "i" := "i" + 1;
3682 "j" := 1;
3683 ELSE
3684 "j" := "j" + 1;
3685 IF "j" = "i" THEN
3686 "j" := "j" + 1;
3687 END IF;
3688 END IF;
3689 END LOOP;
3690 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
3691 RAISE EXCEPTION 'Wrong battle count (should not happen)';
3692 END IF;
3693 -- Store defeat strengths in "matrix" using "defeat_strength"
3694 -- function:
3695 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3696 "i" := 1;
3697 LOOP
3698 "j" := 1;
3699 LOOP
3700 IF "i" != "j" THEN
3701 "matrix"["i"]["j"] := "defeat_strength"(
3702 "vote_matrix"["i"]["j"],
3703 "vote_matrix"["j"]["i"]
3704 );
3705 END IF;
3706 EXIT WHEN "j" = "dimension_v";
3707 "j" := "j" + 1;
3708 END LOOP;
3709 EXIT WHEN "i" = "dimension_v";
3710 "i" := "i" + 1;
3711 END LOOP;
3712 -- Find best paths:
3713 "i" := 1;
3714 LOOP
3715 "j" := 1;
3716 LOOP
3717 IF "i" != "j" THEN
3718 "k" := 1;
3719 LOOP
3720 IF "i" != "k" AND "j" != "k" THEN
3721 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
3722 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
3723 "matrix"["j"]["k"] := "matrix"["j"]["i"];
3724 END IF;
3725 ELSE
3726 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
3727 "matrix"["j"]["k"] := "matrix"["i"]["k"];
3728 END IF;
3729 END IF;
3730 END IF;
3731 EXIT WHEN "k" = "dimension_v";
3732 "k" := "k" + 1;
3733 END LOOP;
3734 END IF;
3735 EXIT WHEN "j" = "dimension_v";
3736 "j" := "j" + 1;
3737 END LOOP;
3738 EXIT WHEN "i" = "dimension_v";
3739 "i" := "i" + 1;
3740 END LOOP;
3741 -- Determine order of winners:
3742 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3743 "rank_v" := 1;
3744 "done_v" := 0;
3745 LOOP
3746 "winners_ary" := '{}';
3747 "i" := 1;
3748 LOOP
3749 IF "rank_ary"["i"] ISNULL THEN
3750 "j" := 1;
3751 LOOP
3752 IF
3753 "i" != "j" AND
3754 "rank_ary"["j"] ISNULL AND
3755 "matrix"["j"]["i"] > "matrix"["i"]["j"]
3756 THEN
3757 -- someone else is better
3758 EXIT;
3759 END IF;
3760 IF "j" = "dimension_v" THEN
3761 -- noone is better
3762 "winners_ary" := "winners_ary" || "i";
3763 EXIT;
3764 END IF;
3765 "j" := "j" + 1;
3766 END LOOP;
3767 END IF;
3768 EXIT WHEN "i" = "dimension_v";
3769 "i" := "i" + 1;
3770 END LOOP;
3771 "i" := 1;
3772 LOOP
3773 "rank_ary"["winners_ary"["i"]] := "rank_v";
3774 "done_v" := "done_v" + 1;
3775 EXIT WHEN "i" = array_upper("winners_ary", 1);
3776 "i" := "i" + 1;
3777 END LOOP;
3778 EXIT WHEN "done_v" = "dimension_v";
3779 "rank_v" := "rank_v" + 1;
3780 END LOOP;
3781 -- write preliminary ranks:
3782 "i" := 1;
3783 FOR "initiative_id_v" IN
3784 SELECT "id" FROM "initiative"
3785 WHERE "issue_id" = "issue_id_p" AND "agreed"
3786 ORDER BY "id"
3787 LOOP
3788 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
3789 WHERE "id" = "initiative_id_v";
3790 "i" := "i" + 1;
3791 END LOOP;
3792 IF "i" != "dimension_v" + 1 THEN
3793 RAISE EXCEPTION 'Wrong winner count (should not happen)';
3794 END IF;
3795 -- straighten ranks (start counting with 1, no equal ranks):
3796 "rank_v" := 1;
3797 FOR "initiative_id_v" IN
3798 SELECT "id" FROM "initiative"
3799 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
3800 ORDER BY
3801 "rank",
3802 "vote_ratio"("positive_votes", "negative_votes") DESC,
3803 "id"
3804 LOOP
3805 UPDATE "initiative" SET "rank" = "rank_v"
3806 WHERE "id" = "initiative_id_v";
3807 "rank_v" := "rank_v" + 1;
3808 END LOOP;
3809 END IF;
3810 -- mark issue as finished
3811 UPDATE "issue" SET
3812 "state" =
3813 CASE WHEN "dimension_v" = 0 THEN
3814 'finished_without_winner'::"issue_state"
3815 ELSE
3816 'finished_with_winner'::"issue_state"
3817 END,
3818 "ranks_available" = TRUE
3819 WHERE "id" = "issue_id_p";
3820 RETURN;
3821 END;
3822 $$;
3824 COMMENT ON FUNCTION "calculate_ranks"
3825 ( "issue"."id"%TYPE )
3826 IS 'Determine ranking (Votes have to be counted first)';
3830 -----------------------------
3831 -- Automatic state changes --
3832 -----------------------------
3835 CREATE FUNCTION "check_issue"
3836 ( "issue_id_p" "issue"."id"%TYPE )
3837 RETURNS VOID
3838 LANGUAGE 'plpgsql' VOLATILE AS $$
3839 DECLARE
3840 "issue_row" "issue"%ROWTYPE;
3841 "policy_row" "policy"%ROWTYPE;
3842 "voting_requested_v" BOOLEAN;
3843 BEGIN
3844 PERFORM "lock_issue"("issue_id_p");
3845 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3846 -- only process open issues:
3847 IF "issue_row"."closed" ISNULL THEN
3848 SELECT * INTO "policy_row" FROM "policy"
3849 WHERE "id" = "issue_row"."policy_id";
3850 -- create a snapshot, unless issue is already fully frozen:
3851 IF "issue_row"."fully_frozen" ISNULL THEN
3852 PERFORM "create_snapshot"("issue_id_p");
3853 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3854 END IF;
3855 -- eventually close or accept issues, which have not been accepted:
3856 IF "issue_row"."accepted" ISNULL THEN
3857 IF EXISTS (
3858 SELECT NULL FROM "initiative"
3859 WHERE "issue_id" = "issue_id_p"
3860 AND "supporter_count" > 0
3861 AND "supporter_count" * "policy_row"."issue_quorum_den"
3862 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
3863 ) THEN
3864 -- accept issues, if supporter count is high enough
3865 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3866 -- NOTE: "issue_row" used later
3867 "issue_row"."state" := 'discussion';
3868 "issue_row"."accepted" := now();
3869 UPDATE "issue" SET
3870 "state" = "issue_row"."state",
3871 "accepted" = "issue_row"."accepted"
3872 WHERE "id" = "issue_row"."id";
3873 ELSIF
3874 now() >= "issue_row"."created" + "issue_row"."admission_time"
3875 THEN
3876 -- close issues, if admission time has expired
3877 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3878 UPDATE "issue" SET
3879 "state" = 'canceled_issue_not_accepted',
3880 "closed" = now()
3881 WHERE "id" = "issue_row"."id";
3882 END IF;
3883 END IF;
3884 -- eventually half freeze issues:
3885 IF
3886 -- NOTE: issue can't be closed at this point, if it has been accepted
3887 "issue_row"."accepted" NOTNULL AND
3888 "issue_row"."half_frozen" ISNULL
3889 THEN
3890 SELECT
3891 CASE
3892 WHEN "vote_now" * 2 > "issue_row"."population" THEN
3893 TRUE
3894 WHEN "vote_later" * 2 > "issue_row"."population" THEN
3895 FALSE
3896 ELSE NULL
3897 END
3898 INTO "voting_requested_v"
3899 FROM "issue" WHERE "id" = "issue_id_p";
3900 IF
3901 "voting_requested_v" OR (
3902 "voting_requested_v" ISNULL AND
3903 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
3904 )
3905 THEN
3906 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
3907 -- NOTE: "issue_row" used later
3908 "issue_row"."state" := 'verification';
3909 "issue_row"."half_frozen" := now();
3910 UPDATE "issue" SET
3911 "state" = "issue_row"."state",
3912 "half_frozen" = "issue_row"."half_frozen"
3913 WHERE "id" = "issue_row"."id";
3914 END IF;
3915 END IF;
3916 -- close issues after some time, if all initiatives have been revoked:
3917 IF
3918 "issue_row"."closed" ISNULL AND
3919 NOT EXISTS (
3920 -- all initiatives are revoked
3921 SELECT NULL FROM "initiative"
3922 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3923 ) AND (
3924 -- and issue has not been accepted yet
3925 "issue_row"."accepted" ISNULL OR
3926 NOT EXISTS (
3927 -- or no initiatives have been revoked lately
3928 SELECT NULL FROM "initiative"
3929 WHERE "issue_id" = "issue_id_p"
3930 AND now() < "revoked" + "issue_row"."verification_time"
3931 ) OR (
3932 -- or verification time has elapsed
3933 "issue_row"."half_frozen" NOTNULL AND
3934 "issue_row"."fully_frozen" ISNULL AND
3935 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3936 )
3937 )
3938 THEN
3939 -- NOTE: "issue_row" used later
3940 IF "issue_row"."accepted" ISNULL THEN
3941 "issue_row"."state" := 'canceled_revoked_before_accepted';
3942 ELSIF "issue_row"."half_frozen" ISNULL THEN
3943 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
3944 ELSE
3945 "issue_row"."state" := 'canceled_after_revocation_during_verification';
3946 END IF;
3947 "issue_row"."closed" := now();
3948 UPDATE "issue" SET
3949 "state" = "issue_row"."state",
3950 "closed" = "issue_row"."closed"
3951 WHERE "id" = "issue_row"."id";
3952 END IF;
3953 -- fully freeze issue after verification time:
3954 IF
3955 "issue_row"."half_frozen" NOTNULL AND
3956 "issue_row"."fully_frozen" ISNULL AND
3957 "issue_row"."closed" ISNULL AND
3958 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3959 THEN
3960 PERFORM "freeze_after_snapshot"("issue_id_p");
3961 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
3962 END IF;
3963 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3964 -- close issue by calling close_voting(...) after voting time:
3965 IF
3966 "issue_row"."closed" ISNULL AND
3967 "issue_row"."fully_frozen" NOTNULL AND
3968 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
3969 THEN
3970 PERFORM "close_voting"("issue_id_p");
3971 -- calculate ranks will not consume much time and can be done now
3972 PERFORM "calculate_ranks"("issue_id_p");
3973 END IF;
3974 END IF;
3975 RETURN;
3976 END;
3977 $$;
3979 COMMENT ON FUNCTION "check_issue"
3980 ( "issue"."id"%TYPE )
3981 IS 'Precalculate supporter counts etc. for a given issue, and check, if status change is required; At end of voting the ranking is not calculated by this function, but must be calculated in a seperate transaction using the "calculate_ranks" function.';
3984 CREATE FUNCTION "check_everything"()
3985 RETURNS VOID
3986 LANGUAGE 'plpgsql' VOLATILE AS $$
3987 DECLARE
3988 "issue_id_v" "issue"."id"%TYPE;
3989 BEGIN
3990 DELETE FROM "expired_session";
3991 PERFORM "check_last_login"();
3992 PERFORM "calculate_member_counts"();
3993 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
3994 PERFORM "check_issue"("issue_id_v");
3995 END LOOP;
3996 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
3997 PERFORM "calculate_ranks"("issue_id_v");
3998 END LOOP;
3999 RETURN;
4000 END;
4001 $$;
4003 COMMENT ON FUNCTION "check_everything"() IS 'Amongst other regular tasks this function performs "check_issue" for every open issue, and if possible, automatically calculates ranks. Use this function only for development and debugging purposes, as long transactions with exclusive locking may result. In productive environments you should call the lf_update program instead.';
4007 ----------------------
4008 -- Deletion of data --
4009 ----------------------
4012 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
4013 RETURNS VOID
4014 LANGUAGE 'plpgsql' VOLATILE AS $$
4015 DECLARE
4016 "issue_row" "issue"%ROWTYPE;
4017 BEGIN
4018 SELECT * INTO "issue_row"
4019 FROM "issue" WHERE "id" = "issue_id_p"
4020 FOR UPDATE;
4021 IF "issue_row"."cleaned" ISNULL THEN
4022 UPDATE "issue" SET
4023 "state" = 'voting',
4024 "closed" = NULL,
4025 "ranks_available" = FALSE
4026 WHERE "id" = "issue_id_p";
4027 DELETE FROM "issue_comment"
4028 WHERE "issue_id" = "issue_id_p";
4029 DELETE FROM "voting_comment"
4030 WHERE "issue_id" = "issue_id_p";
4031 DELETE FROM "delegating_voter"
4032 WHERE "issue_id" = "issue_id_p";
4033 DELETE FROM "direct_voter"
4034 WHERE "issue_id" = "issue_id_p";
4035 DELETE FROM "delegating_interest_snapshot"
4036 WHERE "issue_id" = "issue_id_p";
4037 DELETE FROM "direct_interest_snapshot"
4038 WHERE "issue_id" = "issue_id_p";
4039 DELETE FROM "delegating_population_snapshot"
4040 WHERE "issue_id" = "issue_id_p";
4041 DELETE FROM "direct_population_snapshot"
4042 WHERE "issue_id" = "issue_id_p";
4043 DELETE FROM "non_voter"
4044 WHERE "issue_id" = "issue_id_p";
4045 DELETE FROM "delegation"
4046 WHERE "issue_id" = "issue_id_p";
4047 DELETE FROM "supporter"
4048 WHERE "issue_id" = "issue_id_p";
4049 UPDATE "issue" SET
4050 "state" = "issue_row"."state",
4051 "closed" = "issue_row"."closed",
4052 "ranks_available" = "issue_row"."ranks_available",
4053 "cleaned" = now()
4054 WHERE "id" = "issue_id_p";
4055 END IF;
4056 RETURN;
4057 END;
4058 $$;
4060 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
4063 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
4064 RETURNS VOID
4065 LANGUAGE 'plpgsql' VOLATILE AS $$
4066 BEGIN
4067 UPDATE "member" SET
4068 "last_login" = NULL,
4069 "last_login_public" = NULL,
4070 "login" = NULL,
4071 "password" = NULL,
4072 "locked" = TRUE,
4073 "active" = FALSE,
4074 "notify_email" = NULL,
4075 "notify_email_unconfirmed" = NULL,
4076 "notify_email_secret" = NULL,
4077 "notify_email_secret_expiry" = NULL,
4078 "notify_email_lock_expiry" = NULL,
4079 "password_reset_secret" = NULL,
4080 "password_reset_secret_expiry" = NULL,
4081 "organizational_unit" = NULL,
4082 "internal_posts" = NULL,
4083 "realname" = NULL,
4084 "birthday" = NULL,
4085 "address" = NULL,
4086 "email" = NULL,
4087 "xmpp_address" = NULL,
4088 "website" = NULL,
4089 "phone" = NULL,
4090 "mobile_phone" = NULL,
4091 "profession" = NULL,
4092 "external_memberships" = NULL,
4093 "external_posts" = NULL,
4094 "statement" = NULL
4095 WHERE "id" = "member_id_p";
4096 -- "text_search_data" is updated by triggers
4097 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
4098 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
4099 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
4100 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
4101 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
4102 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
4103 DELETE FROM "session" WHERE "member_id" = "member_id_p";
4104 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
4105 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
4106 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
4107 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
4108 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
4109 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
4110 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
4111 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
4112 DELETE FROM "direct_voter" USING "issue"
4113 WHERE "direct_voter"."issue_id" = "issue"."id"
4114 AND "issue"."closed" ISNULL
4115 AND "member_id" = "member_id_p";
4116 RETURN;
4117 END;
4118 $$;
4120 COMMENT ON FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) IS 'Deactivate member and clear certain settings and data of this member (data protection)';
4123 CREATE FUNCTION "delete_private_data"()
4124 RETURNS VOID
4125 LANGUAGE 'plpgsql' VOLATILE AS $$
4126 BEGIN
4127 UPDATE "member" SET
4128 "last_login" = NULL,
4129 "login" = NULL,
4130 "password" = NULL,
4131 "notify_email" = NULL,
4132 "notify_email_unconfirmed" = NULL,
4133 "notify_email_secret" = NULL,
4134 "notify_email_secret_expiry" = NULL,
4135 "notify_email_lock_expiry" = NULL,
4136 "password_reset_secret" = NULL,
4137 "password_reset_secret_expiry" = NULL,
4138 "organizational_unit" = NULL,
4139 "internal_posts" = NULL,
4140 "realname" = NULL,
4141 "birthday" = NULL,
4142 "address" = NULL,
4143 "email" = NULL,
4144 "xmpp_address" = NULL,
4145 "website" = NULL,
4146 "phone" = NULL,
4147 "mobile_phone" = NULL,
4148 "profession" = NULL,
4149 "external_memberships" = NULL,
4150 "external_posts" = NULL,
4151 "statement" = NULL;
4152 -- "text_search_data" is updated by triggers
4153 DELETE FROM "invite_code";
4154 DELETE FROM "setting";
4155 DELETE FROM "setting_map";
4156 DELETE FROM "member_relation_setting";
4157 DELETE FROM "member_image";
4158 DELETE FROM "contact";
4159 DELETE FROM "ignored_member";
4160 DELETE FROM "session";
4161 DELETE FROM "area_setting";
4162 DELETE FROM "issue_setting";
4163 DELETE FROM "ignored_initiative";
4164 DELETE FROM "initiative_setting";
4165 DELETE FROM "suggestion_setting";
4166 DELETE FROM "non_voter";
4167 DELETE FROM "direct_voter" USING "issue"
4168 WHERE "direct_voter"."issue_id" = "issue"."id"
4169 AND "issue"."closed" ISNULL;
4170 RETURN;
4171 END;
4172 $$;
4174 COMMENT ON FUNCTION "delete_private_data"() IS 'Used by lf_export script. DO NOT USE on productive database, but only on a copy! This function deletes all data which should not be publicly available, and can be used to create a database dump for publication.';
4178 COMMIT;