liquid_feedback_core
view core.sql @ 503:986d8f3a6895
Exclude revoked initiatives from updated/featured/leading initiatives in notification system
| author | jbe |
|---|---|
| date | Tue Apr 05 00:19:33 2016 +0200 (2016-04-05) |
| parents | f39c673420cb |
| children | d07e6a046d41 |
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 ('3.2.0', 3, 2, 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 "temporary_transaction_data" (
58 PRIMARY KEY ("txid", "key"),
59 "txid" INT8 DEFAULT txid_current(),
60 "key" TEXT,
61 "value" TEXT NOT NULL );
63 COMMENT ON TABLE "temporary_transaction_data" IS 'Table to store temporary transaction data; shall be emptied before a transaction is committed';
65 COMMENT ON COLUMN "temporary_transaction_data"."txid" IS 'Value returned by function txid_current(); should be added to WHERE clause, when doing SELECT on this table, but ignored when doing DELETE on this table';
68 CREATE TABLE "system_setting" (
69 "member_ttl" INTERVAL );
70 CREATE UNIQUE INDEX "system_setting_singleton_idx" ON "system_setting" ((1));
72 COMMENT ON TABLE "system_setting" IS 'This table contains only one row with different settings in each column.';
73 COMMENT ON INDEX "system_setting_singleton_idx" IS 'This index ensures that "system_setting" only contains one row maximum.';
75 COMMENT ON COLUMN "system_setting"."member_ttl" IS 'Time after members get their "active" flag set to FALSE, if they do not show any activity.';
78 CREATE TABLE "contingent" (
79 PRIMARY KEY ("polling", "time_frame"),
80 "polling" BOOLEAN,
81 "time_frame" INTERVAL,
82 "text_entry_limit" INT4,
83 "initiative_limit" INT4 );
85 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.';
87 COMMENT ON COLUMN "contingent"."polling" IS 'Determines if settings are for creating initiatives and new drafts of initiatives with "polling" flag set';
88 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';
89 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
92 CREATE TABLE "member" (
93 "id" SERIAL4 PRIMARY KEY,
94 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
95 "invite_code" TEXT UNIQUE,
96 "invite_code_expiry" TIMESTAMPTZ,
97 "admin_comment" TEXT,
98 "activated" TIMESTAMPTZ,
99 "last_activity" DATE,
100 "last_login" TIMESTAMPTZ,
101 "last_delegation_check" TIMESTAMPTZ,
102 "login" TEXT UNIQUE,
103 "password" TEXT,
104 "authority" TEXT,
105 "authority_uid" TEXT,
106 "authority_login" TEXT,
107 "locked" BOOLEAN NOT NULL DEFAULT FALSE,
108 "active" BOOLEAN NOT NULL DEFAULT FALSE,
109 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
110 "lang" TEXT,
111 "notify_email" TEXT,
112 "notify_email_unconfirmed" TEXT,
113 "notify_email_secret" TEXT UNIQUE,
114 "notify_email_secret_expiry" TIMESTAMPTZ,
115 "notify_email_lock_expiry" TIMESTAMPTZ,
116 "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE,
117 "notification_counter" INT4 NOT NULL DEFAULT 1,
118 "notification_sample_size" INT4 NOT NULL DEFAULT 3,
119 "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6),
120 "notification_hour" INT4 CHECK ("notification_hour" BETWEEN 0 AND 23),
121 "login_recovery_expiry" TIMESTAMPTZ,
122 "password_reset_secret" TEXT UNIQUE,
123 "password_reset_secret_expiry" TIMESTAMPTZ,
124 "name" TEXT UNIQUE,
125 "identification" TEXT UNIQUE,
126 "authentication" TEXT,
127 "organizational_unit" TEXT,
128 "internal_posts" TEXT,
129 "realname" TEXT,
130 "birthday" DATE,
131 "address" TEXT,
132 "email" TEXT,
133 "xmpp_address" TEXT,
134 "website" TEXT,
135 "phone" TEXT,
136 "mobile_phone" TEXT,
137 "profession" TEXT,
138 "external_memberships" TEXT,
139 "external_posts" TEXT,
140 "formatting_engine" TEXT,
141 "statement" TEXT,
142 "text_search_data" TSVECTOR,
143 CONSTRAINT "active_requires_activated_and_last_activity"
144 CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)),
145 CONSTRAINT "authority_requires_uid_and_vice_versa"
146 CHECK (("authority" NOTNULL) = ("authority_uid" NOTNULL)),
147 CONSTRAINT "authority_uid_unique_per_authority"
148 UNIQUE ("authority", "authority_uid"),
149 CONSTRAINT "authority_login_requires_authority"
150 CHECK ("authority" NOTNULL OR "authority_login" ISNULL),
151 CONSTRAINT "name_not_null_if_activated"
152 CHECK ("activated" ISNULL OR "name" NOTNULL) );
153 CREATE INDEX "member_authority_login_idx" ON "member" ("authority_login");
154 CREATE INDEX "member_active_idx" ON "member" ("active");
155 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
156 CREATE TRIGGER "update_text_search_data"
157 BEFORE INSERT OR UPDATE ON "member"
158 FOR EACH ROW EXECUTE PROCEDURE
159 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
160 "name", "identification", "organizational_unit", "internal_posts",
161 "realname", "external_memberships", "external_posts", "statement" );
163 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
165 COMMENT ON COLUMN "member"."created" IS 'Creation of member record and/or invite code';
166 COMMENT ON COLUMN "member"."invite_code" IS 'Optional invite code, to allow a member to initialize his/her account the first time';
167 COMMENT ON COLUMN "member"."invite_code_expiry" IS 'Expiry data/time for "invite_code"';
168 COMMENT ON COLUMN "member"."admin_comment" IS 'Hidden comment for administrative purposes';
169 COMMENT ON COLUMN "member"."activated" IS 'Timestamp of first activation of account (i.e. usage of "invite_code"); required to be set for "active" members';
170 COMMENT ON COLUMN "member"."last_activity" IS 'Date of last activity of member; required to be set for "active" members';
171 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';
172 COMMENT ON COLUMN "member"."last_delegation_check" IS 'Timestamp of last delegation check (i.e. confirmation of all unit and area delegations)';
173 COMMENT ON COLUMN "member"."login" IS 'Login name';
174 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
175 COMMENT ON COLUMN "member"."authority" IS 'NULL if LiquidFeedback Core is authoritative for the member account; otherwise a string that indicates the source/authority of the external account (e.g. ''LDAP'' for an LDAP account)';
176 COMMENT ON COLUMN "member"."authority_uid" IS 'Unique identifier (unique per "authority") that allows to identify an external account (e.g. even if the login name changes)';
177 COMMENT ON COLUMN "member"."authority_login" IS 'Login name for external accounts (field is not unique!)';
178 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
179 COMMENT ON COLUMN "member"."active" IS 'Memberships, support and votes are taken into account when corresponding members are marked as active. Automatically set to FALSE, if "last_activity" is older than "system_setting"."member_ttl".';
180 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
181 COMMENT ON COLUMN "member"."lang" IS 'Language code of the preferred language of the member';
182 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
183 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
184 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
185 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
186 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
187 COMMENT ON COLUMN "member"."disable_notifications" IS 'TRUE if member does not want to receive notifications';
188 COMMENT ON COLUMN "member"."login_recovery_expiry" IS 'Date/time after which another login recovery attempt is allowed';
189 COMMENT ON COLUMN "member"."password_reset_secret" IS 'Secret string sent via e-mail for password recovery';
190 COMMENT ON COLUMN "member"."password_reset_secret_expiry" IS 'Date/time until the password recovery secret is valid, and date/time after which another password recovery attempt is allowed';
191 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member, may be NULL if account has not been activated yet';
192 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
193 COMMENT ON COLUMN "member"."authentication" IS 'Information about how this member was authenticated';
194 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
195 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
196 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
197 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
198 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
199 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
200 COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"';
201 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his/her profile';
204 CREATE TABLE "member_history" (
205 "id" SERIAL8 PRIMARY KEY,
206 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
207 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
208 "active" BOOLEAN NOT NULL,
209 "name" TEXT NOT NULL );
210 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
212 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
214 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
215 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
218 CREATE TABLE "rendered_member_statement" (
219 PRIMARY KEY ("member_id", "format"),
220 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
221 "format" TEXT,
222 "content" TEXT NOT NULL );
224 COMMENT ON TABLE "rendered_member_statement" IS 'This table may be used by frontends to cache "rendered" member statements (e.g. HTML output generated from wiki text)';
227 CREATE TABLE "setting" (
228 PRIMARY KEY ("member_id", "key"),
229 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
230 "key" TEXT NOT NULL,
231 "value" TEXT NOT NULL );
232 CREATE INDEX "setting_key_idx" ON "setting" ("key");
234 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
236 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
239 CREATE TABLE "setting_map" (
240 PRIMARY KEY ("member_id", "key", "subkey"),
241 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
242 "key" TEXT NOT NULL,
243 "subkey" TEXT NOT NULL,
244 "value" TEXT NOT NULL );
245 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
247 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
249 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
250 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
251 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
254 CREATE TABLE "member_relation_setting" (
255 PRIMARY KEY ("member_id", "key", "other_member_id"),
256 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
257 "key" TEXT NOT NULL,
258 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
259 "value" TEXT NOT NULL );
261 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
264 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
266 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
269 CREATE TABLE "member_image" (
270 PRIMARY KEY ("member_id", "image_type", "scaled"),
271 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
272 "image_type" "member_image_type",
273 "scaled" BOOLEAN,
274 "content_type" TEXT,
275 "data" BYTEA NOT NULL );
277 COMMENT ON TABLE "member_image" IS 'Images of members';
279 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
282 CREATE TABLE "member_count" (
283 "calculated" TIMESTAMPTZ NOT NULL DEFAULT now(),
284 "total_count" INT4 NOT NULL );
286 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';
288 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
289 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
292 CREATE TABLE "contact" (
293 PRIMARY KEY ("member_id", "other_member_id"),
294 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
295 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
296 "public" BOOLEAN NOT NULL DEFAULT FALSE,
297 CONSTRAINT "cant_save_yourself_as_contact"
298 CHECK ("member_id" != "other_member_id") );
299 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
301 COMMENT ON TABLE "contact" IS 'Contact lists';
303 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
304 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
305 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
308 CREATE TABLE "ignored_member" (
309 PRIMARY KEY ("member_id", "other_member_id"),
310 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
311 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
312 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
314 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
316 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';
317 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
320 CREATE TABLE "session" (
321 "ident" TEXT PRIMARY KEY,
322 "additional_secret" TEXT,
323 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
324 "member_id" INT4 REFERENCES "member" ("id") ON DELETE SET NULL,
325 "authority" TEXT,
326 "authority_uid" TEXT,
327 "authority_login" TEXT,
328 "needs_delegation_check" BOOLEAN NOT NULL DEFAULT FALSE,
329 "lang" TEXT );
330 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
332 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
334 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
335 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
336 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
337 COMMENT ON COLUMN "session"."authority" IS 'Temporary store for "member"."authority" during member account creation';
338 COMMENT ON COLUMN "session"."authority_uid" IS 'Temporary store for "member"."authority_uid" during member account creation';
339 COMMENT ON COLUMN "session"."authority_login" IS 'Temporary store for "member"."authority_login" during member account creation';
340 COMMENT ON COLUMN "session"."needs_delegation_check" IS 'Set to TRUE, if member must perform a delegation check to proceed with login; see column "last_delegation_check" in "member" table';
341 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
344 CREATE TYPE "defeat_strength" AS ENUM ('simple', 'tuple');
346 COMMENT ON TYPE "defeat_strength" IS 'How pairwise defeats are measured for the Schulze method: ''simple'' = only the number of winning votes, ''tuple'' = primarily the number of winning votes, secondarily the number of losing votes';
349 CREATE TYPE "tie_breaking" AS ENUM ('simple', 'variant1', 'variant2');
351 COMMENT ON TYPE "tie_breaking" IS 'Tie-breaker for the Schulze method: ''simple'' = only initiative ids are used, ''variant1'' = use initiative ids in variant 1 for tie breaking of the links (TBRL) and sequentially forbid shared links, ''variant2'' = use initiative ids in variant 2 for tie breaking of the links (TBRL) and sequentially forbid shared links';
354 CREATE TABLE "policy" (
355 "id" SERIAL4 PRIMARY KEY,
356 "index" INT4 NOT NULL,
357 "active" BOOLEAN NOT NULL DEFAULT TRUE,
358 "name" TEXT NOT NULL UNIQUE,
359 "description" TEXT NOT NULL DEFAULT '',
360 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
361 "min_admission_time" INTERVAL,
362 "max_admission_time" INTERVAL,
363 "discussion_time" INTERVAL,
364 "verification_time" INTERVAL,
365 "voting_time" INTERVAL,
366 "issue_quorum_num" INT4,
367 "issue_quorum_den" INT4,
368 "initiative_quorum_num" INT4 NOT NULL,
369 "initiative_quorum_den" INT4 NOT NULL,
370 "defeat_strength" "defeat_strength" NOT NULL DEFAULT 'tuple',
371 "tie_breaking" "tie_breaking" NOT NULL DEFAULT 'variant1',
372 "direct_majority_num" INT4 NOT NULL DEFAULT 1,
373 "direct_majority_den" INT4 NOT NULL DEFAULT 2,
374 "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
375 "direct_majority_positive" INT4 NOT NULL DEFAULT 0,
376 "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0,
377 "indirect_majority_num" INT4 NOT NULL DEFAULT 1,
378 "indirect_majority_den" INT4 NOT NULL DEFAULT 2,
379 "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
380 "indirect_majority_positive" INT4 NOT NULL DEFAULT 0,
381 "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0,
382 "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT FALSE,
383 "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE,
384 CONSTRAINT "timing" CHECK (
385 ( "polling" = FALSE AND
386 "min_admission_time" NOTNULL AND "max_admission_time" NOTNULL AND
387 "min_admission_time" <= "max_admission_time" AND
388 "discussion_time" NOTNULL AND
389 "verification_time" NOTNULL AND
390 "voting_time" NOTNULL ) OR
391 ( "polling" = TRUE AND
392 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
393 "discussion_time" NOTNULL AND
394 "verification_time" NOTNULL AND
395 "voting_time" NOTNULL ) OR
396 ( "polling" = TRUE AND
397 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
398 "discussion_time" ISNULL AND
399 "verification_time" ISNULL AND
400 "voting_time" ISNULL ) ),
401 CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK (
402 "polling" = ("issue_quorum_num" ISNULL) AND
403 "polling" = ("issue_quorum_den" ISNULL) ),
404 CONSTRAINT "no_reverse_beat_path_requires_tuple_defeat_strength" CHECK (
405 "defeat_strength" = 'tuple'::"defeat_strength" OR
406 "no_reverse_beat_path" = FALSE ) );
407 CREATE INDEX "policy_active_idx" ON "policy" ("active");
409 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
411 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
412 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
413 COMMENT ON COLUMN "policy"."polling" IS 'TRUE = special policy for non-user-generated issues without issue quorum, where certain initiatives (those having the "polling" flag set) do not need to pass the initiative quorum; "min_admission_time" and "max_admission_time" MUST be set to NULL, the other timings may be set to NULL altogether, allowing individual timing for those issues';
414 COMMENT ON COLUMN "policy"."min_admission_time" IS 'Minimum duration of issue state ''admission''; Minimum time an issue stays open';
415 COMMENT ON COLUMN "policy"."max_admission_time" IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';
416 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"';
417 COMMENT ON COLUMN "policy"."verification_time" IS 'Duration of issue state ''verification''; Regular time until an issue is "fully_frozen" (e.g. entering issue state ''voting'') after being "half_frozen"';
418 COMMENT ON COLUMN "policy"."voting_time" IS 'Duration of issue state ''voting''; Time after an issue is "fully_frozen" but not "closed" (duration of issue state ''voting'')';
419 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" and enter issue state ''discussion''';
420 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" and enter issue state ''discussion''';
421 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
422 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
423 COMMENT ON COLUMN "policy"."defeat_strength" IS 'How pairwise defeats are measured for the Schulze method; see type "defeat_strength"; ''tuple'' is the recommended setting';
424 COMMENT ON COLUMN "policy"."tie_breaking" IS 'Tie-breaker for the Schulze method; see type "tie_breaking"; ''variant1'' or ''variant2'' are recommended';
425 COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner';
426 COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner';
427 COMMENT ON COLUMN "policy"."direct_majority_strict" IS 'If TRUE, then the direct majority must be strictly greater than "direct_majority_num"/"direct_majority_den", otherwise it may also be equal.';
428 COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner';
429 COMMENT ON COLUMN "policy"."direct_majority_non_negative" IS 'Absolute number of sum of "positive_votes" and abstentions neccessary for an initiative to be attainable as winner';
430 COMMENT ON COLUMN "policy"."indirect_majority_num" IS 'Numerator of fraction of neccessary indirect majority (through beat path) for initiatives to be attainable as winner';
431 COMMENT ON COLUMN "policy"."indirect_majority_den" IS 'Denominator of fraction of neccessary indirect majority (through beat path) for initiatives to be attainable as winner';
432 COMMENT ON COLUMN "policy"."indirect_majority_strict" IS 'If TRUE, then the indirect majority must be strictly greater than "indirect_majority_num"/"indirect_majority_den", otherwise it may also be equal.';
433 COMMENT ON COLUMN "policy"."indirect_majority_positive" IS 'Absolute number of votes in favor of the winner neccessary in a beat path to the status quo for an initaitive to be attainable as winner';
434 COMMENT ON COLUMN "policy"."indirect_majority_non_negative" IS 'Absolute number of sum of votes in favor and abstentions in a beat path to the status quo for an initiative to be attainable as winner';
435 COMMENT ON COLUMN "policy"."no_reverse_beat_path" IS 'EXPERIMENTAL FEATURE: Causes initiatives with "reverse_beat_path" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."reverse_beat_path". This option ensures both that a winning initiative is never tied in a (weak) condorcet paradox with the status quo and a winning initiative always beats the status quo directly with a simple majority.';
436 COMMENT ON COLUMN "policy"."no_multistage_majority" IS 'EXPERIMENTAL FEATURE: Causes initiatives with "multistage_majority" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."multistage_majority". This disqualifies initiatives which could cause an instable result. An instable result in this meaning is a result such that repeating the ballot with same preferences but with the winner of the first ballot as status quo would lead to a different winner in the second ballot. If there are no direct majorities required for the winner, or if in direct comparison only simple majorities are required and "no_reverse_beat_path" is true, then results are always stable and this flag does not have any effect on the winner (but still affects the "eligible" flag of an "initiative").';
439 CREATE TABLE "unit" (
440 "id" SERIAL4 PRIMARY KEY,
441 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
442 "active" BOOLEAN NOT NULL DEFAULT TRUE,
443 "name" TEXT NOT NULL,
444 "description" TEXT NOT NULL DEFAULT '',
445 "external_reference" TEXT,
446 "member_count" INT4,
447 "text_search_data" TSVECTOR );
448 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
449 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
450 CREATE INDEX "unit_active_idx" ON "unit" ("active");
451 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
452 CREATE TRIGGER "update_text_search_data"
453 BEFORE INSERT OR UPDATE ON "unit"
454 FOR EACH ROW EXECUTE PROCEDURE
455 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
456 "name", "description" );
458 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
460 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
461 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this unit';
462 COMMENT ON COLUMN "unit"."external_reference" IS 'Opaque data field to store an external reference';
463 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
466 CREATE TABLE "subscription" (
467 PRIMARY KEY ("member_id", "unit_id"),
468 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
469 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
470 CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id");
472 COMMENT ON TABLE "subscription" IS 'An entry in this table denotes that the member wishes to receive notifications regardless of his/her privileges in the given unit';
475 CREATE TABLE "unit_setting" (
476 PRIMARY KEY ("member_id", "key", "unit_id"),
477 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
478 "key" TEXT NOT NULL,
479 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
480 "value" TEXT NOT NULL );
482 COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings';
485 CREATE TABLE "area" (
486 "id" SERIAL4 PRIMARY KEY,
487 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
488 "active" BOOLEAN NOT NULL DEFAULT TRUE,
489 "name" TEXT NOT NULL,
490 "description" TEXT NOT NULL DEFAULT '',
491 "external_reference" TEXT,
492 "direct_member_count" INT4,
493 "member_weight" INT4,
494 "text_search_data" TSVECTOR );
495 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
496 CREATE INDEX "area_active_idx" ON "area" ("active");
497 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
498 CREATE TRIGGER "update_text_search_data"
499 BEFORE INSERT OR UPDATE ON "area"
500 FOR EACH ROW EXECUTE PROCEDURE
501 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
502 "name", "description" );
504 COMMENT ON TABLE "area" IS 'Subject areas';
506 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
507 COMMENT ON COLUMN "area"."external_reference" IS 'Opaque data field to store an external reference';
508 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"';
509 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
512 CREATE TABLE "ignored_area" (
513 PRIMARY KEY ("member_id", "area_id"),
514 "member_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
515 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
516 CREATE INDEX "ignored_area_area_id_idx" ON "ignored_area" ("area_id");
518 COMMENT ON TABLE "ignored_area" IS 'An entry in this table denotes that the member does not wish to receive notifications for the given subject area unless he/she declared interested in a particular issue';
521 CREATE TABLE "area_setting" (
522 PRIMARY KEY ("member_id", "key", "area_id"),
523 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
524 "key" TEXT NOT NULL,
525 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
526 "value" TEXT NOT NULL );
528 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
531 CREATE TABLE "allowed_policy" (
532 PRIMARY KEY ("area_id", "policy_id"),
533 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
534 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
535 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
536 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
538 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
540 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
543 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
545 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';
548 CREATE TYPE "issue_state" AS ENUM (
549 'admission', 'discussion', 'verification', 'voting',
550 'canceled_by_admin',
551 'canceled_revoked_before_accepted',
552 'canceled_issue_not_accepted',
553 'canceled_after_revocation_during_discussion',
554 'canceled_after_revocation_during_verification',
555 'canceled_no_initiative_admitted',
556 'finished_without_winner', 'finished_with_winner');
558 COMMENT ON TYPE "issue_state" IS 'State of issues';
561 CREATE TABLE "issue" (
562 "id" SERIAL4 PRIMARY KEY,
563 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
564 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
565 "admin_notice" TEXT,
566 "external_reference" TEXT,
567 "state" "issue_state" NOT NULL DEFAULT 'admission',
568 "phase_finished" TIMESTAMPTZ,
569 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
570 "accepted" TIMESTAMPTZ,
571 "half_frozen" TIMESTAMPTZ,
572 "fully_frozen" TIMESTAMPTZ,
573 "closed" TIMESTAMPTZ,
574 "cleaned" TIMESTAMPTZ,
575 "min_admission_time" INTERVAL,
576 "max_admission_time" INTERVAL,
577 "discussion_time" INTERVAL NOT NULL,
578 "verification_time" INTERVAL NOT NULL,
579 "voting_time" INTERVAL NOT NULL,
580 "snapshot" TIMESTAMPTZ,
581 "latest_snapshot_event" "snapshot_event",
582 "population" INT4,
583 "voter_count" INT4,
584 "status_quo_schulze_rank" INT4,
585 CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (
586 ("min_admission_time" NOTNULL) = ("max_admission_time" NOTNULL) AND
587 ("min_admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created")) ),
588 CONSTRAINT "valid_state" CHECK (
589 (
590 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
591 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
592 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL ) OR
593 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL)
594 ) AND (
595 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
596 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
597 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
598 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
599 ("state" = 'canceled_by_admin' AND "closed" NOTNULL) OR
600 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
601 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
602 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
603 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
604 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" = "fully_frozen") OR
605 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen") OR
606 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen")
607 )),
608 CONSTRAINT "phase_finished_only_when_not_closed" CHECK (
609 "phase_finished" ISNULL OR "closed" ISNULL ),
610 CONSTRAINT "state_change_order" CHECK (
611 "created" <= "accepted" AND
612 "accepted" <= "half_frozen" AND
613 "half_frozen" <= "fully_frozen" AND
614 "fully_frozen" <= "closed" ),
615 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
616 "cleaned" ISNULL OR "closed" NOTNULL ),
617 CONSTRAINT "last_snapshot_on_full_freeze"
618 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
619 CONSTRAINT "freeze_requires_snapshot"
620 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
621 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
622 CHECK (("snapshot" NOTNULL) = ("latest_snapshot_event" NOTNULL)) );
623 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
624 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
625 CREATE INDEX "issue_created_idx" ON "issue" ("created");
626 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
627 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
628 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
629 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
630 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
631 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
633 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
635 COMMENT ON COLUMN "issue"."admin_notice" IS 'Public notice by admin to explain manual interventions, or to announce corrections';
636 COMMENT ON COLUMN "issue"."external_reference" IS 'Opaque data field to store an external reference';
637 COMMENT ON COLUMN "issue"."phase_finished" IS 'Set to a value NOTNULL, if the current phase has finished, but calculations are pending; No changes in this issue shall be made by the frontend or API when this value is set';
638 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
639 COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed; 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.';
640 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.';
641 COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "max_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.';
642 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
643 COMMENT ON COLUMN "issue"."min_admission_time" IS 'Copied from "policy" table at creation of issue';
644 COMMENT ON COLUMN "issue"."max_admission_time" IS 'Copied from "policy" table at creation of issue';
645 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
646 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
647 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
648 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
649 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';
650 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
651 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';
652 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
655 CREATE TABLE "issue_order_in_admission_state" (
656 "id" INT8 PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
657 "order_in_area" INT4,
658 "order_in_unit" INT4 );
660 COMMENT ON TABLE "issue_order_in_admission_state" IS 'Ordering information for issues that are not stored in the "issue" table to avoid locking of multiple issues at once; Filled/updated by "lf_update_issue_order"';
662 COMMENT ON COLUMN "issue_order_in_admission_state"."id" IS 'References "issue" ("id") but has no referential integrity trigger associated, due to performance/locking issues';
663 COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_area" IS 'Order of issues in admission state within a single area; NULL values sort last';
664 COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_unit" IS 'Order of issues in admission state within all areas of a unit; NULL values sort last';
667 CREATE TABLE "issue_setting" (
668 PRIMARY KEY ("member_id", "key", "issue_id"),
669 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
670 "key" TEXT NOT NULL,
671 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
672 "value" TEXT NOT NULL );
674 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
677 CREATE TABLE "initiative" (
678 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
679 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
680 "id" SERIAL4 PRIMARY KEY,
681 "name" TEXT NOT NULL,
682 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
683 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
684 "revoked" TIMESTAMPTZ,
685 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
686 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
687 "external_reference" TEXT,
688 "admitted" BOOLEAN,
689 "supporter_count" INT4,
690 "informed_supporter_count" INT4,
691 "satisfied_supporter_count" INT4,
692 "satisfied_informed_supporter_count" INT4,
693 "harmonic_weight" NUMERIC(12, 3),
694 "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE,
695 "first_preference_votes" INT4,
696 "positive_votes" INT4,
697 "negative_votes" INT4,
698 "direct_majority" BOOLEAN,
699 "indirect_majority" BOOLEAN,
700 "schulze_rank" INT4,
701 "better_than_status_quo" BOOLEAN,
702 "worse_than_status_quo" BOOLEAN,
703 "reverse_beat_path" BOOLEAN,
704 "multistage_majority" BOOLEAN,
705 "eligible" BOOLEAN,
706 "winner" BOOLEAN,
707 "rank" INT4,
708 "text_search_data" TSVECTOR,
709 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
710 CHECK (("revoked" NOTNULL) = ("revoked_by_member_id" NOTNULL)),
711 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
712 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
713 CONSTRAINT "revoked_initiatives_cant_be_admitted"
714 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
715 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
716 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
717 ( "first_preference_votes" ISNULL AND
718 "positive_votes" ISNULL AND "negative_votes" ISNULL AND
719 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
720 "schulze_rank" ISNULL AND
721 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
722 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
723 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ),
724 CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")),
725 CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (
726 "eligible" = FALSE OR
727 ("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ),
728 CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE),
729 CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1),
730 CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE),
731 CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") );
732 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
733 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
734 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
735 CREATE TRIGGER "update_text_search_data"
736 BEFORE INSERT OR UPDATE ON "initiative"
737 FOR EACH ROW EXECUTE PROCEDURE
738 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "name");
740 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.';
742 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative does not need to pass the initiative quorum (see "policy"."polling")';
743 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
744 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoke the initiative';
745 COMMENT ON COLUMN "initiative"."external_reference" IS 'Opaque data field to store an external reference';
746 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
747 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
748 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
749 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
750 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
751 COMMENT ON COLUMN "initiative"."harmonic_weight" IS 'Indicates the relevancy of the initiative, calculated from the potential supporters weighted with the harmonic series to avoid a large number of clones affecting other initiative''s sorting positions too much; shall be used as secondary sorting key after "admitted" as primary sorting key';
752 COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time';
753 COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice';
754 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Number of direct and delegating voters who ranked this initiative better than the status quo';
755 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo';
756 COMMENT ON COLUMN "initiative"."direct_majority" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "direct_majority_num"/"direct_majority_den", and "positive_votes" is greater-equal than "direct_majority_positive", and ("positive_votes"+abstentions) is greater-equal than "direct_majority_non_negative"';
757 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths';
758 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking';
759 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo';
760 COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (DEPRECATED, since schulze-ranking is unique per issue; use "better_than_status_quo"=FALSE)';
761 COMMENT ON COLUMN "initiative"."reverse_beat_path" IS 'TRUE, if there is a beat path (may include ties) from this initiative to the status quo; set to NULL if "policy"."defeat_strength" is set to ''simple''';
762 COMMENT ON COLUMN "initiative"."multistage_majority" IS 'TRUE, if either (a) this initiative has no better rank than the status quo, or (b) there exists a better ranked initiative X, which directly beats this initiative, and either more voters prefer X to this initiative than voters preferring X to the status quo or less voters prefer this initiative to X than voters preferring the status quo to X';
763 COMMENT ON COLUMN "initiative"."eligible" IS 'Initiative has a "direct_majority" and an "indirect_majority", is "better_than_status_quo" and depending on selected policy the initiative has no "reverse_beat_path" or "multistage_majority"';
764 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank"';
765 COMMENT ON COLUMN "initiative"."rank" IS 'Unique ranking for all "admitted" initiatives per issue; lower rank is better; a winner always has rank 1, but rank 1 does not imply that an initiative is winner; initiatives with "direct_majority" AND "indirect_majority" always have a better (lower) rank than other initiatives';
768 CREATE TABLE "battle" (
769 "issue_id" INT4 NOT NULL,
770 "winning_initiative_id" INT4,
771 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
772 "losing_initiative_id" INT4,
773 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
774 "count" INT4 NOT NULL,
775 CONSTRAINT "initiative_ids_not_equal" CHECK (
776 "winning_initiative_id" != "losing_initiative_id" OR
777 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
778 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) );
779 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
780 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
781 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
783 COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue; NULL as initiative_id denotes virtual "status-quo" initiative';
786 CREATE TABLE "ignored_initiative" (
787 PRIMARY KEY ("member_id", "initiative_id"),
788 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
789 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
790 CREATE INDEX "ignored_initiative_initiative_id_idx" ON "ignored_initiative" ("initiative_id");
792 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
795 CREATE TABLE "initiative_setting" (
796 PRIMARY KEY ("member_id", "key", "initiative_id"),
797 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
798 "key" TEXT NOT NULL,
799 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
800 "value" TEXT NOT NULL );
802 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
805 CREATE TABLE "draft" (
806 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
807 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
808 "id" SERIAL8 PRIMARY KEY,
809 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
810 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
811 "formatting_engine" TEXT,
812 "content" TEXT NOT NULL,
813 "external_reference" TEXT,
814 "text_search_data" TSVECTOR );
815 CREATE INDEX "draft_created_idx" ON "draft" ("created");
816 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
817 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
818 CREATE TRIGGER "update_text_search_data"
819 BEFORE INSERT OR UPDATE ON "draft"
820 FOR EACH ROW EXECUTE PROCEDURE
821 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
823 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.';
825 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
826 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
827 COMMENT ON COLUMN "draft"."external_reference" IS 'Opaque data field to store an external reference';
830 CREATE TABLE "rendered_draft" (
831 PRIMARY KEY ("draft_id", "format"),
832 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
833 "format" TEXT,
834 "content" TEXT NOT NULL );
836 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)';
839 CREATE TABLE "suggestion" (
840 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
841 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
842 "id" SERIAL8 PRIMARY KEY,
843 "draft_id" INT8 NOT NULL,
844 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
845 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
846 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
847 "name" TEXT NOT NULL,
848 "formatting_engine" TEXT,
849 "content" TEXT NOT NULL DEFAULT '',
850 "external_reference" TEXT,
851 "text_search_data" TSVECTOR,
852 "minus2_unfulfilled_count" INT4,
853 "minus2_fulfilled_count" INT4,
854 "minus1_unfulfilled_count" INT4,
855 "minus1_fulfilled_count" INT4,
856 "plus1_unfulfilled_count" INT4,
857 "plus1_fulfilled_count" INT4,
858 "plus2_unfulfilled_count" INT4,
859 "plus2_fulfilled_count" INT4,
860 "proportional_order" INT4 );
861 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
862 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
863 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
864 CREATE TRIGGER "update_text_search_data"
865 BEFORE INSERT OR UPDATE ON "suggestion"
866 FOR EACH ROW EXECUTE PROCEDURE
867 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
868 "name", "content");
870 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';
872 COMMENT ON COLUMN "suggestion"."draft_id" IS 'Draft, which the author has seen when composing the suggestion; should always be set by a frontend, but defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
873 COMMENT ON COLUMN "suggestion"."external_reference" IS 'Opaque data field to store an external reference';
874 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
875 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
876 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
877 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
878 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
879 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
880 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
881 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
882 COMMENT ON COLUMN "suggestion"."proportional_order" IS 'To be used for sorting suggestions within an initiative; NULL values sort last; updated by "lf_update_suggestion_order"';
885 CREATE TABLE "rendered_suggestion" (
886 PRIMARY KEY ("suggestion_id", "format"),
887 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
888 "format" TEXT,
889 "content" TEXT NOT NULL );
891 COMMENT ON TABLE "rendered_suggestion" IS 'This table may be used by frontends to cache "rendered" drafts (e.g. HTML output generated from wiki text)';
894 CREATE TABLE "suggestion_setting" (
895 PRIMARY KEY ("member_id", "key", "suggestion_id"),
896 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
897 "key" TEXT NOT NULL,
898 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
899 "value" TEXT NOT NULL );
901 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
904 CREATE TABLE "privilege" (
905 PRIMARY KEY ("unit_id", "member_id"),
906 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
907 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
908 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
909 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
910 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
911 "member_manager" BOOLEAN NOT NULL DEFAULT FALSE,
912 "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE,
913 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE,
914 "polling_right" BOOLEAN NOT NULL DEFAULT FALSE );
916 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
918 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke any privileges to/from other members';
919 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create and disable sub units';
920 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create and disable areas and set area parameters';
921 COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';
922 COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';
923 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote';
924 COMMENT ON COLUMN "privilege"."polling_right" IS 'Right to create issues with policies having the "policy"."polling" flag set, and to add initiatives having the "initiative"."polling" flag set to those issues';
927 CREATE TABLE "membership" (
928 PRIMARY KEY ("area_id", "member_id"),
929 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
930 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
931 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
933 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
936 CREATE TABLE "interest" (
937 PRIMARY KEY ("issue_id", "member_id"),
938 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
939 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
940 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
942 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.';
945 CREATE TABLE "initiator" (
946 PRIMARY KEY ("initiative_id", "member_id"),
947 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
948 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
949 "accepted" BOOLEAN );
950 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
952 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.';
954 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.';
957 CREATE TABLE "supporter" (
958 "issue_id" INT4 NOT NULL,
959 PRIMARY KEY ("initiative_id", "member_id"),
960 "initiative_id" INT4,
961 "member_id" INT4,
962 "draft_id" INT8 NOT NULL,
963 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
964 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE );
965 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
967 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.';
969 COMMENT ON COLUMN "supporter"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
970 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft; should always be set by a frontend, but defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
973 CREATE TABLE "opinion" (
974 "initiative_id" INT4 NOT NULL,
975 PRIMARY KEY ("suggestion_id", "member_id"),
976 "suggestion_id" INT8,
977 "member_id" INT4,
978 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
979 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
980 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
981 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
982 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
984 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.';
986 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
989 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
991 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
994 CREATE TABLE "delegation" (
995 "id" SERIAL8 PRIMARY KEY,
996 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
997 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
998 "scope" "delegation_scope" NOT NULL,
999 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1000 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1001 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1002 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
1003 CONSTRAINT "no_unit_delegation_to_null"
1004 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
1005 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
1006 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
1007 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
1008 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
1009 UNIQUE ("unit_id", "truster_id"),
1010 UNIQUE ("area_id", "truster_id"),
1011 UNIQUE ("issue_id", "truster_id") );
1012 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
1013 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
1015 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
1017 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
1018 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
1019 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
1022 CREATE TABLE "direct_population_snapshot" (
1023 PRIMARY KEY ("issue_id", "event", "member_id"),
1024 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1025 "event" "snapshot_event",
1026 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1027 "weight" INT4 );
1028 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
1030 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"; for corrections refer to column "issue_notice" of "issue" table';
1032 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1033 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
1036 CREATE TABLE "delegating_population_snapshot" (
1037 PRIMARY KEY ("issue_id", "event", "member_id"),
1038 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1039 "event" "snapshot_event",
1040 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1041 "weight" INT4,
1042 "scope" "delegation_scope" NOT NULL,
1043 "delegate_member_ids" INT4[] NOT NULL );
1044 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
1046 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table; for corrections refer to column "issue_notice" of "issue" table';
1048 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1049 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
1050 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
1051 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"';
1054 CREATE TABLE "direct_interest_snapshot" (
1055 PRIMARY KEY ("issue_id", "event", "member_id"),
1056 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1057 "event" "snapshot_event",
1058 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1059 "weight" INT4 );
1060 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
1062 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"; for corrections refer to column "issue_notice" of "issue" table';
1064 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1065 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
1068 CREATE TABLE "delegating_interest_snapshot" (
1069 PRIMARY KEY ("issue_id", "event", "member_id"),
1070 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1071 "event" "snapshot_event",
1072 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1073 "weight" INT4,
1074 "scope" "delegation_scope" NOT NULL,
1075 "delegate_member_ids" INT4[] NOT NULL );
1076 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
1078 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table; for corrections refer to column "issue_notice" of "issue" table';
1080 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1081 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
1082 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
1083 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"';
1086 CREATE TABLE "direct_supporter_snapshot" (
1087 "issue_id" INT4 NOT NULL,
1088 PRIMARY KEY ("initiative_id", "event", "member_id"),
1089 "initiative_id" INT4,
1090 "event" "snapshot_event",
1091 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1092 "draft_id" INT8 NOT NULL,
1093 "informed" BOOLEAN NOT NULL,
1094 "satisfied" BOOLEAN NOT NULL,
1095 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1096 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
1097 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1098 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
1100 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot"); for corrections refer to column "issue_notice" of "issue" table';
1102 COMMENT ON COLUMN "direct_supporter_snapshot"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
1103 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1104 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
1105 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
1108 CREATE TABLE "non_voter" (
1109 PRIMARY KEY ("issue_id", "member_id"),
1110 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1111 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
1112 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
1114 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
1117 CREATE TABLE "direct_voter" (
1118 PRIMARY KEY ("issue_id", "member_id"),
1119 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1120 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1121 "weight" INT4,
1122 "comment_changed" TIMESTAMPTZ,
1123 "formatting_engine" TEXT,
1124 "comment" TEXT,
1125 "text_search_data" TSVECTOR );
1126 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
1127 CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data");
1128 CREATE TRIGGER "update_text_search_data"
1129 BEFORE INSERT OR UPDATE ON "direct_voter"
1130 FOR EACH ROW EXECUTE PROCEDURE
1131 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment");
1133 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; for corrections refer to column "issue_notice" of "issue" table';
1135 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
1136 COMMENT ON COLUMN "direct_voter"."comment_changed" IS 'Shall be set on comment change, to indicate a comment being modified after voting has been finished; Automatically set to NULL after voting phase; Automatically set to NULL by trigger, if "comment" is set to NULL';
1137 COMMENT ON COLUMN "direct_voter"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "direct_voter"."comment"; Automatically set to NULL by trigger, if "comment" is set to NULL';
1138 COMMENT ON COLUMN "direct_voter"."comment" 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.';
1141 CREATE TABLE "rendered_voter_comment" (
1142 PRIMARY KEY ("issue_id", "member_id", "format"),
1143 FOREIGN KEY ("issue_id", "member_id")
1144 REFERENCES "direct_voter" ("issue_id", "member_id")
1145 ON DELETE CASCADE ON UPDATE CASCADE,
1146 "issue_id" INT4,
1147 "member_id" INT4,
1148 "format" TEXT,
1149 "content" TEXT NOT NULL );
1151 COMMENT ON TABLE "rendered_voter_comment" IS 'This table may be used by frontends to cache "rendered" voter comments (e.g. HTML output generated from wiki text)';
1154 CREATE TABLE "delegating_voter" (
1155 PRIMARY KEY ("issue_id", "member_id"),
1156 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1157 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1158 "weight" INT4,
1159 "scope" "delegation_scope" NOT NULL,
1160 "delegate_member_ids" INT4[] NOT NULL );
1161 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
1163 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table; for corrections refer to column "issue_notice" of "issue" table';
1165 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
1166 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
1167 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"';
1170 CREATE TABLE "vote" (
1171 "issue_id" INT4 NOT NULL,
1172 PRIMARY KEY ("initiative_id", "member_id"),
1173 "initiative_id" INT4,
1174 "member_id" INT4,
1175 "grade" INT4 NOT NULL,
1176 "first_preference" BOOLEAN,
1177 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1178 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
1179 CONSTRAINT "first_preference_flag_only_set_on_positive_grades"
1180 CHECK ("grade" > 0 OR "first_preference" ISNULL) );
1181 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
1183 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; for corrections refer to column "issue_notice" of "issue" table';
1185 COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
1186 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.';
1187 COMMENT ON COLUMN "vote"."first_preference" IS 'Value is automatically set after voting is finished. For positive grades, this value is set to true for the highest (i.e. best) grade.';
1190 CREATE TYPE "event_type" AS ENUM (
1191 'issue_state_changed',
1192 'initiative_created_in_new_issue',
1193 'initiative_created_in_existing_issue',
1194 'initiative_revoked',
1195 'new_draft_created',
1196 'suggestion_created');
1198 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
1201 CREATE TABLE "event" (
1202 "id" SERIAL8 PRIMARY KEY,
1203 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
1204 "event" "event_type" NOT NULL,
1205 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1206 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1207 "state" "issue_state",
1208 "initiative_id" INT4,
1209 "draft_id" INT8,
1210 "suggestion_id" INT8,
1211 FOREIGN KEY ("issue_id", "initiative_id")
1212 REFERENCES "initiative" ("issue_id", "id")
1213 ON DELETE CASCADE ON UPDATE CASCADE,
1214 FOREIGN KEY ("initiative_id", "draft_id")
1215 REFERENCES "draft" ("initiative_id", "id")
1216 ON DELETE CASCADE ON UPDATE CASCADE,
1217 FOREIGN KEY ("initiative_id", "suggestion_id")
1218 REFERENCES "suggestion" ("initiative_id", "id")
1219 ON DELETE CASCADE ON UPDATE CASCADE,
1220 CONSTRAINT "null_constr_for_issue_state_changed" CHECK (
1221 "event" != 'issue_state_changed' OR (
1222 "member_id" ISNULL AND
1223 "issue_id" NOTNULL AND
1224 "state" NOTNULL AND
1225 "initiative_id" ISNULL AND
1226 "draft_id" ISNULL AND
1227 "suggestion_id" ISNULL )),
1228 CONSTRAINT "null_constr_for_initiative_creation_or_revocation_or_new_draft" CHECK (
1229 "event" NOT IN (
1230 'initiative_created_in_new_issue',
1231 'initiative_created_in_existing_issue',
1232 'initiative_revoked',
1233 'new_draft_created'
1234 ) OR (
1235 "member_id" NOTNULL AND
1236 "issue_id" NOTNULL AND
1237 "state" NOTNULL AND
1238 "initiative_id" NOTNULL AND
1239 "draft_id" NOTNULL AND
1240 "suggestion_id" ISNULL )),
1241 CONSTRAINT "null_constr_for_suggestion_creation" CHECK (
1242 "event" != 'suggestion_created' OR (
1243 "member_id" NOTNULL AND
1244 "issue_id" NOTNULL AND
1245 "state" NOTNULL AND
1246 "initiative_id" NOTNULL AND
1247 "draft_id" ISNULL AND
1248 "suggestion_id" NOTNULL )) );
1249 CREATE INDEX "event_occurrence_idx" ON "event" ("occurrence");
1251 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
1253 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
1254 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
1255 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
1256 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
1259 CREATE TABLE "notification_sent" (
1260 "event_id" INT8 NOT NULL );
1261 CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1));
1263 COMMENT ON TABLE "notification_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';
1264 COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';
1267 CREATE TABLE "initiative_notification_sent" (
1268 PRIMARY KEY ("member_id", "initiative_id"),
1269 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1270 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1271 "last_draft_id" INT8 NOT NULL,
1272 "last_suggestion_id" INT8 );
1273 CREATE INDEX "initiative_notification_sent_initiative_idx" ON "initiative_notification_sent" ("initiative_id");
1276 CREATE TABLE "newsletter" (
1277 "id" SERIAL4 PRIMARY KEY,
1278 "published" TIMESTAMPTZ NOT NULL,
1279 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1280 "include_all_members" BOOLEAN NOT NULL,
1281 "sent" TIMESTAMPTZ,
1282 "subject" TEXT NOT NULL,
1283 "content" TEXT NOT NULL );
1284 CREATE INDEX "newsletter_unit_id_idx" ON "newsletter" ("unit_id", "published");
1285 CREATE INDEX "newsletter_all_units_published_idx" ON "newsletter" ("published") WHERE "unit_id" ISNULL;
1286 CREATE INDEX "newsletter_published_idx" ON "newsletter" ("published");
1290 ----------------------------------------------
1291 -- Writing of history entries and event log --
1292 ----------------------------------------------
1295 CREATE FUNCTION "write_member_history_trigger"()
1296 RETURNS TRIGGER
1297 LANGUAGE 'plpgsql' VOLATILE AS $$
1298 BEGIN
1299 IF
1300 ( NEW."active" != OLD."active" OR
1301 NEW."name" != OLD."name" ) AND
1302 OLD."activated" NOTNULL
1303 THEN
1304 INSERT INTO "member_history"
1305 ("member_id", "active", "name")
1306 VALUES (NEW."id", OLD."active", OLD."name");
1307 END IF;
1308 RETURN NULL;
1309 END;
1310 $$;
1312 CREATE TRIGGER "write_member_history"
1313 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1314 "write_member_history_trigger"();
1316 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1317 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1320 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1321 RETURNS TRIGGER
1322 LANGUAGE 'plpgsql' VOLATILE AS $$
1323 BEGIN
1324 IF NEW."state" != OLD."state" THEN
1325 INSERT INTO "event" ("event", "issue_id", "state")
1326 VALUES ('issue_state_changed', NEW."id", NEW."state");
1327 END IF;
1328 RETURN NULL;
1329 END;
1330 $$;
1332 CREATE TRIGGER "write_event_issue_state_changed"
1333 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1334 "write_event_issue_state_changed_trigger"();
1336 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1337 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1340 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1341 RETURNS TRIGGER
1342 LANGUAGE 'plpgsql' VOLATILE AS $$
1343 DECLARE
1344 "initiative_row" "initiative"%ROWTYPE;
1345 "issue_row" "issue"%ROWTYPE;
1346 "event_v" "event_type";
1347 BEGIN
1348 SELECT * INTO "initiative_row" FROM "initiative"
1349 WHERE "id" = NEW."initiative_id";
1350 SELECT * INTO "issue_row" FROM "issue"
1351 WHERE "id" = "initiative_row"."issue_id";
1352 IF EXISTS (
1353 SELECT NULL FROM "draft"
1354 WHERE "initiative_id" = NEW."initiative_id"
1355 AND "id" != NEW."id"
1356 ) THEN
1357 "event_v" := 'new_draft_created';
1358 ELSE
1359 IF EXISTS (
1360 SELECT NULL FROM "initiative"
1361 WHERE "issue_id" = "initiative_row"."issue_id"
1362 AND "id" != "initiative_row"."id"
1363 ) THEN
1364 "event_v" := 'initiative_created_in_existing_issue';
1365 ELSE
1366 "event_v" := 'initiative_created_in_new_issue';
1367 END IF;
1368 END IF;
1369 INSERT INTO "event" (
1370 "event", "member_id",
1371 "issue_id", "state", "initiative_id", "draft_id"
1372 ) VALUES (
1373 "event_v",
1374 NEW."author_id",
1375 "initiative_row"."issue_id",
1376 "issue_row"."state",
1377 "initiative_row"."id",
1378 NEW."id" );
1379 RETURN NULL;
1380 END;
1381 $$;
1383 CREATE TRIGGER "write_event_initiative_or_draft_created"
1384 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1385 "write_event_initiative_or_draft_created_trigger"();
1387 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1388 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1391 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1392 RETURNS TRIGGER
1393 LANGUAGE 'plpgsql' VOLATILE AS $$
1394 DECLARE
1395 "issue_row" "issue"%ROWTYPE;
1396 "draft_id_v" "draft"."id"%TYPE;
1397 BEGIN
1398 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1399 SELECT * INTO "issue_row" FROM "issue"
1400 WHERE "id" = NEW."issue_id";
1401 SELECT "id" INTO "draft_id_v" FROM "current_draft"
1402 WHERE "initiative_id" = NEW."id";
1403 INSERT INTO "event" (
1404 "event", "member_id", "issue_id", "state", "initiative_id", "draft_id"
1405 ) VALUES (
1406 'initiative_revoked',
1407 NEW."revoked_by_member_id",
1408 NEW."issue_id",
1409 "issue_row"."state",
1410 NEW."id",
1411 "draft_id_v");
1412 END IF;
1413 RETURN NULL;
1414 END;
1415 $$;
1417 CREATE TRIGGER "write_event_initiative_revoked"
1418 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1419 "write_event_initiative_revoked_trigger"();
1421 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1422 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1425 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1426 RETURNS TRIGGER
1427 LANGUAGE 'plpgsql' VOLATILE AS $$
1428 DECLARE
1429 "initiative_row" "initiative"%ROWTYPE;
1430 "issue_row" "issue"%ROWTYPE;
1431 BEGIN
1432 SELECT * INTO "initiative_row" FROM "initiative"
1433 WHERE "id" = NEW."initiative_id";
1434 SELECT * INTO "issue_row" FROM "issue"
1435 WHERE "id" = "initiative_row"."issue_id";
1436 INSERT INTO "event" (
1437 "event", "member_id",
1438 "issue_id", "state", "initiative_id", "suggestion_id"
1439 ) VALUES (
1440 'suggestion_created',
1441 NEW."author_id",
1442 "initiative_row"."issue_id",
1443 "issue_row"."state",
1444 "initiative_row"."id",
1445 NEW."id" );
1446 RETURN NULL;
1447 END;
1448 $$;
1450 CREATE TRIGGER "write_event_suggestion_created"
1451 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1452 "write_event_suggestion_created_trigger"();
1454 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1455 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1459 ----------------------------
1460 -- Additional constraints --
1461 ----------------------------
1464 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1465 RETURNS TRIGGER
1466 LANGUAGE 'plpgsql' VOLATILE AS $$
1467 BEGIN
1468 IF NOT EXISTS (
1469 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1470 ) THEN
1471 RAISE EXCEPTION 'Cannot create issue without an initial initiative.' USING
1472 ERRCODE = 'integrity_constraint_violation',
1473 HINT = 'Create issue, initiative, and draft within the same transaction.';
1474 END IF;
1475 RETURN NULL;
1476 END;
1477 $$;
1479 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1480 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1481 FOR EACH ROW EXECUTE PROCEDURE
1482 "issue_requires_first_initiative_trigger"();
1484 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1485 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1488 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1489 RETURNS TRIGGER
1490 LANGUAGE 'plpgsql' VOLATILE AS $$
1491 DECLARE
1492 "reference_lost" BOOLEAN;
1493 BEGIN
1494 IF TG_OP = 'DELETE' THEN
1495 "reference_lost" := TRUE;
1496 ELSE
1497 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1498 END IF;
1499 IF
1500 "reference_lost" AND NOT EXISTS (
1501 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1502 )
1503 THEN
1504 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1505 END IF;
1506 RETURN NULL;
1507 END;
1508 $$;
1510 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1511 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1512 FOR EACH ROW EXECUTE PROCEDURE
1513 "last_initiative_deletes_issue_trigger"();
1515 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1516 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1519 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1520 RETURNS TRIGGER
1521 LANGUAGE 'plpgsql' VOLATILE AS $$
1522 BEGIN
1523 IF NOT EXISTS (
1524 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1525 ) THEN
1526 RAISE EXCEPTION 'Cannot create initiative without an initial draft.' USING
1527 ERRCODE = 'integrity_constraint_violation',
1528 HINT = 'Create issue, initiative and draft within the same transaction.';
1529 END IF;
1530 RETURN NULL;
1531 END;
1532 $$;
1534 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1535 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1536 FOR EACH ROW EXECUTE PROCEDURE
1537 "initiative_requires_first_draft_trigger"();
1539 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1540 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1543 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1544 RETURNS TRIGGER
1545 LANGUAGE 'plpgsql' VOLATILE AS $$
1546 DECLARE
1547 "reference_lost" BOOLEAN;
1548 BEGIN
1549 IF TG_OP = 'DELETE' THEN
1550 "reference_lost" := TRUE;
1551 ELSE
1552 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1553 END IF;
1554 IF
1555 "reference_lost" AND NOT EXISTS (
1556 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1557 )
1558 THEN
1559 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1560 END IF;
1561 RETURN NULL;
1562 END;
1563 $$;
1565 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1566 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1567 FOR EACH ROW EXECUTE PROCEDURE
1568 "last_draft_deletes_initiative_trigger"();
1570 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1571 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1574 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1575 RETURNS TRIGGER
1576 LANGUAGE 'plpgsql' VOLATILE AS $$
1577 BEGIN
1578 IF NOT EXISTS (
1579 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1580 ) THEN
1581 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.' USING
1582 ERRCODE = 'integrity_constraint_violation',
1583 HINT = 'Create suggestion and opinion within the same transaction.';
1584 END IF;
1585 RETURN NULL;
1586 END;
1587 $$;
1589 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1590 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1591 FOR EACH ROW EXECUTE PROCEDURE
1592 "suggestion_requires_first_opinion_trigger"();
1594 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1595 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1598 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1599 RETURNS TRIGGER
1600 LANGUAGE 'plpgsql' VOLATILE AS $$
1601 DECLARE
1602 "reference_lost" BOOLEAN;
1603 BEGIN
1604 IF TG_OP = 'DELETE' THEN
1605 "reference_lost" := TRUE;
1606 ELSE
1607 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1608 END IF;
1609 IF
1610 "reference_lost" AND NOT EXISTS (
1611 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1612 )
1613 THEN
1614 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1615 END IF;
1616 RETURN NULL;
1617 END;
1618 $$;
1620 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1621 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1622 FOR EACH ROW EXECUTE PROCEDURE
1623 "last_opinion_deletes_suggestion_trigger"();
1625 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1626 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1629 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()
1630 RETURNS TRIGGER
1631 LANGUAGE 'plpgsql' VOLATILE AS $$
1632 BEGIN
1633 DELETE FROM "direct_voter"
1634 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1635 RETURN NULL;
1636 END;
1637 $$;
1639 CREATE TRIGGER "non_voter_deletes_direct_voter"
1640 AFTER INSERT OR UPDATE ON "non_voter"
1641 FOR EACH ROW EXECUTE PROCEDURE
1642 "non_voter_deletes_direct_voter_trigger"();
1644 COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';
1645 COMMENT ON TRIGGER "non_voter_deletes_direct_voter" ON "non_voter" IS 'An entry in the "non_voter" table deletes an entry in the "direct_voter" table (and vice versa due to trigger "direct_voter_deletes_non_voter" on table "direct_voter")';
1648 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()
1649 RETURNS TRIGGER
1650 LANGUAGE 'plpgsql' VOLATILE AS $$
1651 BEGIN
1652 DELETE FROM "non_voter"
1653 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1654 RETURN NULL;
1655 END;
1656 $$;
1658 CREATE TRIGGER "direct_voter_deletes_non_voter"
1659 AFTER INSERT OR UPDATE ON "direct_voter"
1660 FOR EACH ROW EXECUTE PROCEDURE
1661 "direct_voter_deletes_non_voter_trigger"();
1663 COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';
1664 COMMENT ON TRIGGER "direct_voter_deletes_non_voter" ON "direct_voter" IS 'An entry in the "direct_voter" table deletes an entry in the "non_voter" table (and vice versa due to trigger "non_voter_deletes_direct_voter" on table "non_voter")';
1667 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()
1668 RETURNS TRIGGER
1669 LANGUAGE 'plpgsql' VOLATILE AS $$
1670 BEGIN
1671 IF NEW."comment" ISNULL THEN
1672 NEW."comment_changed" := NULL;
1673 NEW."formatting_engine" := NULL;
1674 END IF;
1675 RETURN NEW;
1676 END;
1677 $$;
1679 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"
1680 BEFORE INSERT OR UPDATE ON "direct_voter"
1681 FOR EACH ROW EXECUTE PROCEDURE
1682 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();
1684 COMMENT ON FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"() IS 'Implementation of trigger "voter_comment_fields_only_set_when_voter_comment_is_set" ON table "direct_voter"';
1685 COMMENT ON TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set" ON "direct_voter" IS 'If "comment" is set to NULL, then other comment related fields are also set to NULL.';
1688 ---------------------------------------------------------------
1689 -- Ensure that votes are not modified when issues are closed --
1690 ---------------------------------------------------------------
1692 -- NOTE: Frontends should ensure this anyway, but in case of programming
1693 -- errors the following triggers ensure data integrity.
1696 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1697 RETURNS TRIGGER
1698 LANGUAGE 'plpgsql' VOLATILE AS $$
1699 DECLARE
1700 "issue_id_v" "issue"."id"%TYPE;
1701 "issue_row" "issue"%ROWTYPE;
1702 BEGIN
1703 IF EXISTS (
1704 SELECT NULL FROM "temporary_transaction_data"
1705 WHERE "txid" = txid_current()
1706 AND "key" = 'override_protection_triggers'
1707 AND "value" = TRUE::TEXT
1708 ) THEN
1709 RETURN NULL;
1710 END IF;
1711 IF TG_OP = 'DELETE' THEN
1712 "issue_id_v" := OLD."issue_id";
1713 ELSE
1714 "issue_id_v" := NEW."issue_id";
1715 END IF;
1716 SELECT INTO "issue_row" * FROM "issue"
1717 WHERE "id" = "issue_id_v" FOR SHARE;
1718 IF (
1719 "issue_row"."closed" NOTNULL OR (
1720 "issue_row"."state" = 'voting' AND
1721 "issue_row"."phase_finished" NOTNULL
1722 )
1723 ) THEN
1724 IF
1725 TG_RELID = 'direct_voter'::regclass AND
1726 TG_OP = 'UPDATE'
1727 THEN
1728 IF
1729 OLD."issue_id" = NEW."issue_id" AND
1730 OLD."member_id" = NEW."member_id" AND
1731 OLD."weight" = NEW."weight"
1732 THEN
1733 RETURN NULL; -- allows changing of voter comment
1734 END IF;
1735 END IF;
1736 RAISE EXCEPTION 'Tried to modify data after voting has been closed.' USING
1737 ERRCODE = 'integrity_constraint_violation';
1738 END IF;
1739 RETURN NULL;
1740 END;
1741 $$;
1743 CREATE TRIGGER "forbid_changes_on_closed_issue"
1744 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1745 FOR EACH ROW EXECUTE PROCEDURE
1746 "forbid_changes_on_closed_issue_trigger"();
1748 CREATE TRIGGER "forbid_changes_on_closed_issue"
1749 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1750 FOR EACH ROW EXECUTE PROCEDURE
1751 "forbid_changes_on_closed_issue_trigger"();
1753 CREATE TRIGGER "forbid_changes_on_closed_issue"
1754 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1755 FOR EACH ROW EXECUTE PROCEDURE
1756 "forbid_changes_on_closed_issue_trigger"();
1758 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"';
1759 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';
1760 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';
1761 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';
1765 --------------------------------------------------------------------
1766 -- Auto-retrieval of fields only needed for referential integrity --
1767 --------------------------------------------------------------------
1770 CREATE FUNCTION "autofill_issue_id_trigger"()
1771 RETURNS TRIGGER
1772 LANGUAGE 'plpgsql' VOLATILE AS $$
1773 BEGIN
1774 IF NEW."issue_id" ISNULL THEN
1775 SELECT "issue_id" INTO NEW."issue_id"
1776 FROM "initiative" WHERE "id" = NEW."initiative_id";
1777 END IF;
1778 RETURN NEW;
1779 END;
1780 $$;
1782 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1783 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1785 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1786 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1788 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1789 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1790 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1793 CREATE FUNCTION "autofill_initiative_id_trigger"()
1794 RETURNS TRIGGER
1795 LANGUAGE 'plpgsql' VOLATILE AS $$
1796 BEGIN
1797 IF NEW."initiative_id" ISNULL THEN
1798 SELECT "initiative_id" INTO NEW."initiative_id"
1799 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1800 END IF;
1801 RETURN NEW;
1802 END;
1803 $$;
1805 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1806 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1808 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1809 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1813 -----------------------------------------------------
1814 -- Automatic calculation of certain default values --
1815 -----------------------------------------------------
1818 CREATE FUNCTION "copy_timings_trigger"()
1819 RETURNS TRIGGER
1820 LANGUAGE 'plpgsql' VOLATILE AS $$
1821 DECLARE
1822 "policy_row" "policy"%ROWTYPE;
1823 BEGIN
1824 SELECT * INTO "policy_row" FROM "policy"
1825 WHERE "id" = NEW."policy_id";
1826 IF NEW."min_admission_time" ISNULL THEN
1827 NEW."min_admission_time" := "policy_row"."min_admission_time";
1828 END IF;
1829 IF NEW."max_admission_time" ISNULL THEN
1830 NEW."max_admission_time" := "policy_row"."max_admission_time";
1831 END IF;
1832 IF NEW."discussion_time" ISNULL THEN
1833 NEW."discussion_time" := "policy_row"."discussion_time";
1834 END IF;
1835 IF NEW."verification_time" ISNULL THEN
1836 NEW."verification_time" := "policy_row"."verification_time";
1837 END IF;
1838 IF NEW."voting_time" ISNULL THEN
1839 NEW."voting_time" := "policy_row"."voting_time";
1840 END IF;
1841 RETURN NEW;
1842 END;
1843 $$;
1845 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1846 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1848 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1849 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1852 CREATE FUNCTION "default_for_draft_id_trigger"()
1853 RETURNS TRIGGER
1854 LANGUAGE 'plpgsql' VOLATILE AS $$
1855 BEGIN
1856 IF NEW."draft_id" ISNULL THEN
1857 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1858 WHERE "initiative_id" = NEW."initiative_id";
1859 END IF;
1860 RETURN NEW;
1861 END;
1862 $$;
1864 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
1865 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1866 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1867 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1869 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
1870 COMMENT ON TRIGGER "default_for_draft_id" ON "suggestion" IS 'If "draft_id" is NULL, then use the current draft of the initiative as default';
1871 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';
1875 ----------------------------------------
1876 -- Automatic creation of dependencies --
1877 ----------------------------------------
1880 CREATE FUNCTION "autocreate_interest_trigger"()
1881 RETURNS TRIGGER
1882 LANGUAGE 'plpgsql' VOLATILE AS $$
1883 BEGIN
1884 IF NOT EXISTS (
1885 SELECT NULL FROM "initiative" JOIN "interest"
1886 ON "initiative"."issue_id" = "interest"."issue_id"
1887 WHERE "initiative"."id" = NEW."initiative_id"
1888 AND "interest"."member_id" = NEW."member_id"
1889 ) THEN
1890 BEGIN
1891 INSERT INTO "interest" ("issue_id", "member_id")
1892 SELECT "issue_id", NEW."member_id"
1893 FROM "initiative" WHERE "id" = NEW."initiative_id";
1894 EXCEPTION WHEN unique_violation THEN END;
1895 END IF;
1896 RETURN NEW;
1897 END;
1898 $$;
1900 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1901 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1903 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1904 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';
1907 CREATE FUNCTION "autocreate_supporter_trigger"()
1908 RETURNS TRIGGER
1909 LANGUAGE 'plpgsql' VOLATILE AS $$
1910 BEGIN
1911 IF NOT EXISTS (
1912 SELECT NULL FROM "suggestion" JOIN "supporter"
1913 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1914 WHERE "suggestion"."id" = NEW."suggestion_id"
1915 AND "supporter"."member_id" = NEW."member_id"
1916 ) THEN
1917 BEGIN
1918 INSERT INTO "supporter" ("initiative_id", "member_id")
1919 SELECT "initiative_id", NEW."member_id"
1920 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1921 EXCEPTION WHEN unique_violation THEN END;
1922 END IF;
1923 RETURN NEW;
1924 END;
1925 $$;
1927 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1928 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1930 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1931 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.';
1935 ------------------------------------------
1936 -- Views and helper functions for views --
1937 ------------------------------------------
1940 CREATE VIEW "unit_delegation" AS
1941 SELECT
1942 "unit"."id" AS "unit_id",
1943 "delegation"."id",
1944 "delegation"."truster_id",
1945 "delegation"."trustee_id",
1946 "delegation"."scope"
1947 FROM "unit"
1948 JOIN "delegation"
1949 ON "delegation"."unit_id" = "unit"."id"
1950 JOIN "member"
1951 ON "delegation"."truster_id" = "member"."id"
1952 JOIN "privilege"
1953 ON "delegation"."unit_id" = "privilege"."unit_id"
1954 AND "delegation"."truster_id" = "privilege"."member_id"
1955 WHERE "member"."active" AND "privilege"."voting_right";
1957 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1960 CREATE VIEW "area_delegation" AS
1961 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1962 "area"."id" AS "area_id",
1963 "delegation"."id",
1964 "delegation"."truster_id",
1965 "delegation"."trustee_id",
1966 "delegation"."scope"
1967 FROM "area"
1968 JOIN "delegation"
1969 ON "delegation"."unit_id" = "area"."unit_id"
1970 OR "delegation"."area_id" = "area"."id"
1971 JOIN "member"
1972 ON "delegation"."truster_id" = "member"."id"
1973 JOIN "privilege"
1974 ON "area"."unit_id" = "privilege"."unit_id"
1975 AND "delegation"."truster_id" = "privilege"."member_id"
1976 WHERE "member"."active" AND "privilege"."voting_right"
1977 ORDER BY
1978 "area"."id",
1979 "delegation"."truster_id",
1980 "delegation"."scope" DESC;
1982 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1985 CREATE VIEW "issue_delegation" AS
1986 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1987 "issue"."id" AS "issue_id",
1988 "delegation"."id",
1989 "delegation"."truster_id",
1990 "delegation"."trustee_id",
1991 "delegation"."scope"
1992 FROM "issue"
1993 JOIN "area"
1994 ON "area"."id" = "issue"."area_id"
1995 JOIN "delegation"
1996 ON "delegation"."unit_id" = "area"."unit_id"
1997 OR "delegation"."area_id" = "area"."id"
1998 OR "delegation"."issue_id" = "issue"."id"
1999 JOIN "member"
2000 ON "delegation"."truster_id" = "member"."id"
2001 JOIN "privilege"
2002 ON "area"."unit_id" = "privilege"."unit_id"
2003 AND "delegation"."truster_id" = "privilege"."member_id"
2004 WHERE "member"."active" AND "privilege"."voting_right"
2005 ORDER BY
2006 "issue"."id",
2007 "delegation"."truster_id",
2008 "delegation"."scope" DESC;
2010 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
2013 CREATE FUNCTION "membership_weight_with_skipping"
2014 ( "area_id_p" "area"."id"%TYPE,
2015 "member_id_p" "member"."id"%TYPE,
2016 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
2017 RETURNS INT4
2018 LANGUAGE 'plpgsql' STABLE AS $$
2019 DECLARE
2020 "sum_v" INT4;
2021 "delegation_row" "area_delegation"%ROWTYPE;
2022 BEGIN
2023 "sum_v" := 1;
2024 FOR "delegation_row" IN
2025 SELECT "area_delegation".*
2026 FROM "area_delegation" LEFT JOIN "membership"
2027 ON "membership"."area_id" = "area_id_p"
2028 AND "membership"."member_id" = "area_delegation"."truster_id"
2029 WHERE "area_delegation"."area_id" = "area_id_p"
2030 AND "area_delegation"."trustee_id" = "member_id_p"
2031 AND "membership"."member_id" ISNULL
2032 LOOP
2033 IF NOT
2034 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
2035 THEN
2036 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
2037 "area_id_p",
2038 "delegation_row"."truster_id",
2039 "skip_member_ids_p" || "delegation_row"."truster_id"
2040 );
2041 END IF;
2042 END LOOP;
2043 RETURN "sum_v";
2044 END;
2045 $$;
2047 COMMENT ON FUNCTION "membership_weight_with_skipping"
2048 ( "area"."id"%TYPE,
2049 "member"."id"%TYPE,
2050 INT4[] )
2051 IS 'Helper function for "membership_weight" function';
2054 CREATE FUNCTION "membership_weight"
2055 ( "area_id_p" "area"."id"%TYPE,
2056 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
2057 RETURNS INT4
2058 LANGUAGE 'plpgsql' STABLE AS $$
2059 BEGIN
2060 RETURN "membership_weight_with_skipping"(
2061 "area_id_p",
2062 "member_id_p",
2063 ARRAY["member_id_p"]
2064 );
2065 END;
2066 $$;
2068 COMMENT ON FUNCTION "membership_weight"
2069 ( "area"."id"%TYPE,
2070 "member"."id"%TYPE )
2071 IS 'Calculates the potential voting weight of a member in a given area';
2074 CREATE VIEW "member_count_view" AS
2075 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
2077 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
2080 CREATE VIEW "unit_member_count" AS
2081 SELECT
2082 "unit"."id" AS "unit_id",
2083 count("member"."id") AS "member_count"
2084 FROM "unit"
2085 LEFT JOIN "privilege"
2086 ON "privilege"."unit_id" = "unit"."id"
2087 AND "privilege"."voting_right"
2088 LEFT JOIN "member"
2089 ON "member"."id" = "privilege"."member_id"
2090 AND "member"."active"
2091 GROUP BY "unit"."id";
2093 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
2096 CREATE VIEW "area_member_count" AS
2097 SELECT
2098 "area"."id" AS "area_id",
2099 count("member"."id") AS "direct_member_count",
2100 coalesce(
2101 sum(
2102 CASE WHEN "member"."id" NOTNULL THEN
2103 "membership_weight"("area"."id", "member"."id")
2104 ELSE 0 END
2105 )
2106 ) AS "member_weight"
2107 FROM "area"
2108 LEFT JOIN "membership"
2109 ON "area"."id" = "membership"."area_id"
2110 LEFT JOIN "privilege"
2111 ON "privilege"."unit_id" = "area"."unit_id"
2112 AND "privilege"."member_id" = "membership"."member_id"
2113 AND "privilege"."voting_right"
2114 LEFT JOIN "member"
2115 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
2116 AND "member"."active"
2117 GROUP BY "area"."id";
2119 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';
2122 CREATE VIEW "opening_draft" AS
2123 SELECT "draft".* FROM (
2124 SELECT
2125 "initiative"."id" AS "initiative_id",
2126 min("draft"."id") AS "draft_id"
2127 FROM "initiative" JOIN "draft"
2128 ON "initiative"."id" = "draft"."initiative_id"
2129 GROUP BY "initiative"."id"
2130 ) AS "subquery"
2131 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
2133 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
2136 CREATE VIEW "current_draft" AS
2137 SELECT "draft".* FROM (
2138 SELECT
2139 "initiative"."id" AS "initiative_id",
2140 max("draft"."id") AS "draft_id"
2141 FROM "initiative" JOIN "draft"
2142 ON "initiative"."id" = "draft"."initiative_id"
2143 GROUP BY "initiative"."id"
2144 ) AS "subquery"
2145 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
2147 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
2150 CREATE VIEW "critical_opinion" AS
2151 SELECT * FROM "opinion"
2152 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
2153 OR ("degree" = -2 AND "fulfilled" = TRUE);
2155 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
2158 CREATE VIEW "issue_supporter_in_admission_state" AS
2159 SELECT DISTINCT -- TODO: DISTINCT needed?
2160 "area"."unit_id",
2161 "issue"."area_id",
2162 "issue"."id" AS "issue_id",
2163 "supporter"."member_id",
2164 "direct_interest_snapshot"."weight"
2165 FROM "issue"
2166 JOIN "area" ON "area"."id" = "issue"."area_id"
2167 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
2168 JOIN "direct_interest_snapshot"
2169 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
2170 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
2171 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
2172 WHERE "issue"."state" = 'admission'::"issue_state";
2174 COMMENT ON VIEW "issue_supporter_in_admission_state" IS 'Helper view for "lf_update_issue_order" to allow a (proportional) ordering of issues within an area';
2177 CREATE VIEW "initiative_suggestion_order_calculation" AS
2178 SELECT
2179 "initiative"."id" AS "initiative_id",
2180 ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final"
2181 FROM "initiative" JOIN "issue"
2182 ON "initiative"."issue_id" = "issue"."id"
2183 WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL)
2184 OR ("initiative"."final_suggestion_order_calculated" = FALSE);
2186 COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';
2188 COMMENT ON COLUMN "initiative_suggestion_order_calculation"."final" IS 'Set to TRUE, if the issue is fully frozen or closed, and the calculation has to be done only once for one last time';
2191 CREATE VIEW "individual_suggestion_ranking" AS
2192 SELECT
2193 "opinion"."initiative_id",
2194 "opinion"."member_id",
2195 "direct_interest_snapshot"."weight",
2196 CASE WHEN
2197 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
2198 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
2199 THEN 1 ELSE
2200 CASE WHEN
2201 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
2202 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
2203 THEN 2 ELSE
2204 CASE WHEN
2205 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
2206 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
2207 THEN 3 ELSE 4 END
2208 END
2209 END AS "preference",
2210 "opinion"."suggestion_id"
2211 FROM "opinion"
2212 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
2213 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2214 JOIN "direct_interest_snapshot"
2215 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
2216 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
2217 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
2219 COMMENT ON VIEW "individual_suggestion_ranking" IS 'Helper view for "lf_update_suggestion_order" to allow a proportional ordering of suggestions within an initiative';
2222 CREATE VIEW "battle_participant" AS
2223 SELECT "initiative"."id", "initiative"."issue_id"
2224 FROM "issue" JOIN "initiative"
2225 ON "issue"."id" = "initiative"."issue_id"
2226 WHERE "initiative"."admitted"
2227 UNION ALL
2228 SELECT NULL, "id" AS "issue_id"
2229 FROM "issue";
2231 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
2234 CREATE VIEW "battle_view" AS
2235 SELECT
2236 "issue"."id" AS "issue_id",
2237 "winning_initiative"."id" AS "winning_initiative_id",
2238 "losing_initiative"."id" AS "losing_initiative_id",
2239 sum(
2240 CASE WHEN
2241 coalesce("better_vote"."grade", 0) >
2242 coalesce("worse_vote"."grade", 0)
2243 THEN "direct_voter"."weight" ELSE 0 END
2244 ) AS "count"
2245 FROM "issue"
2246 LEFT JOIN "direct_voter"
2247 ON "issue"."id" = "direct_voter"."issue_id"
2248 JOIN "battle_participant" AS "winning_initiative"
2249 ON "issue"."id" = "winning_initiative"."issue_id"
2250 JOIN "battle_participant" AS "losing_initiative"
2251 ON "issue"."id" = "losing_initiative"."issue_id"
2252 LEFT JOIN "vote" AS "better_vote"
2253 ON "direct_voter"."member_id" = "better_vote"."member_id"
2254 AND "winning_initiative"."id" = "better_vote"."initiative_id"
2255 LEFT JOIN "vote" AS "worse_vote"
2256 ON "direct_voter"."member_id" = "worse_vote"."member_id"
2257 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
2258 WHERE "issue"."state" = 'voting'
2259 AND "issue"."phase_finished" NOTNULL
2260 AND (
2261 "winning_initiative"."id" != "losing_initiative"."id" OR
2262 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
2263 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
2264 GROUP BY
2265 "issue"."id",
2266 "winning_initiative"."id",
2267 "losing_initiative"."id";
2269 COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative (or status-quo) to another initiative (or status-quo); Used to fill "battle" table';
2272 CREATE VIEW "expired_session" AS
2273 SELECT * FROM "session" WHERE now() > "expiry";
2275 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
2276 DELETE FROM "session" WHERE "ident" = OLD."ident";
2278 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
2279 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
2282 CREATE VIEW "open_issue" AS
2283 SELECT * FROM "issue" WHERE "closed" ISNULL;
2285 COMMENT ON VIEW "open_issue" IS 'All open issues';
2288 CREATE VIEW "member_contingent" AS
2289 SELECT
2290 "member"."id" AS "member_id",
2291 "contingent"."polling",
2292 "contingent"."time_frame",
2293 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
2294 (
2295 SELECT count(1) FROM "draft"
2296 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
2297 WHERE "draft"."author_id" = "member"."id"
2298 AND "initiative"."polling" = "contingent"."polling"
2299 AND "draft"."created" > now() - "contingent"."time_frame"
2300 ) + (
2301 SELECT count(1) FROM "suggestion"
2302 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
2303 WHERE "suggestion"."author_id" = "member"."id"
2304 AND "contingent"."polling" = FALSE
2305 AND "suggestion"."created" > now() - "contingent"."time_frame"
2306 )
2307 ELSE NULL END AS "text_entry_count",
2308 "contingent"."text_entry_limit",
2309 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
2310 SELECT count(1) FROM "opening_draft" AS "draft"
2311 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
2312 WHERE "draft"."author_id" = "member"."id"
2313 AND "initiative"."polling" = "contingent"."polling"
2314 AND "draft"."created" > now() - "contingent"."time_frame"
2315 ) ELSE NULL END AS "initiative_count",
2316 "contingent"."initiative_limit"
2317 FROM "member" CROSS JOIN "contingent";
2319 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
2321 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
2322 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
2325 CREATE VIEW "member_contingent_left" AS
2326 SELECT
2327 "member_id",
2328 "polling",
2329 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
2330 max("initiative_limit" - "initiative_count") AS "initiatives_left"
2331 FROM "member_contingent" GROUP BY "member_id", "polling";
2333 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.';
2336 CREATE VIEW "event_for_notification" AS
2337 SELECT
2338 "member"."id" AS "recipient_id",
2339 "event".*
2340 FROM "member" CROSS JOIN "event"
2341 JOIN "issue" ON "issue"."id" = "event"."issue_id"
2342 JOIN "area" ON "area"."id" = "issue"."area_id"
2343 LEFT JOIN "privilege" ON
2344 "privilege"."member_id" = "member"."id" AND
2345 "privilege"."unit_id" = "area"."unit_id" AND
2346 "privilege"."voting_right" = TRUE
2347 LEFT JOIN "subscription" ON
2348 "subscription"."member_id" = "member"."id" AND
2349 "subscription"."unit_id" = "area"."unit_id"
2350 LEFT JOIN "ignored_area" ON
2351 "ignored_area"."member_id" = "member"."id" AND
2352 "ignored_area"."area_id" = "issue"."area_id"
2353 LEFT JOIN "interest" ON
2354 "interest"."member_id" = "member"."id" AND
2355 "interest"."issue_id" = "event"."issue_id"
2356 LEFT JOIN "supporter" ON
2357 "supporter"."member_id" = "member"."id" AND
2358 "supporter"."initiative_id" = "event"."initiative_id"
2359 WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL)
2360 AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
2361 AND (
2362 "event"."event" = 'issue_state_changed'::"event_type" OR
2363 ( "event"."event" = 'initiative_revoked'::"event_type" AND
2364 "supporter"."member_id" NOTNULL ) );
2366 COMMENT ON VIEW "event_for_notification" IS 'TODO: documentation';
2369 CREATE VIEW "updated_initiative" AS
2370 SELECT
2371 "supporter"."member_id" AS "recipient_id",
2372 FALSE AS "featured",
2373 "supporter"."initiative_id"
2374 FROM "supporter"
2375 JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id"
2376 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2377 LEFT JOIN "initiative_notification_sent" AS "sent" ON
2378 "sent"."member_id" = "supporter"."member_id" AND
2379 "sent"."initiative_id" = "supporter"."initiative_id"
2380 LEFT JOIN "ignored_initiative" ON
2381 "ignored_initiative"."member_id" = "supporter"."member_id" AND
2382 "ignored_initiative"."initiative_id" = "supporter"."initiative_id"
2383 WHERE "issue"."state" IN ('admission', 'discussion')
2384 AND "initiative"."revoked" ISNULL
2385 AND "ignored_initiative"."member_id" ISNULL
2386 AND (
2387 EXISTS (
2388 SELECT NULL FROM "draft"
2389 LEFT JOIN "ignored_member" ON
2390 "ignored_member"."member_id" = "supporter"."member_id" AND
2391 "ignored_member"."other_member_id" = "draft"."author_id"
2392 WHERE "draft"."initiative_id" = "supporter"."initiative_id"
2393 AND "draft"."id" > "supporter"."draft_id"
2394 AND "ignored_member"."member_id" ISNULL
2395 ) OR EXISTS (
2396 SELECT NULL FROM "suggestion"
2397 LEFT JOIN "opinion" ON
2398 "opinion"."member_id" = "supporter"."member_id" AND
2399 "opinion"."suggestion_id" = "suggestion"."id"
2400 LEFT JOIN "ignored_member" ON
2401 "ignored_member"."member_id" = "supporter"."member_id" AND
2402 "ignored_member"."other_member_id" = "suggestion"."author_id"
2403 WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"
2404 AND "opinion"."member_id" ISNULL
2405 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
2406 AND "ignored_member"."member_id" ISNULL
2407 )
2408 );
2410 CREATE FUNCTION "featured_initiative"
2411 ( "recipient_id_p" "member"."id"%TYPE,
2412 "area_id_p" "area"."id"%TYPE )
2413 RETURNS SETOF "initiative"."id"%TYPE
2414 LANGUAGE 'plpgsql' STABLE AS $$
2415 DECLARE
2416 "counter_v" "member"."notification_counter"%TYPE;
2417 "sample_size_v" "member"."notification_sample_size"%TYPE;
2418 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
2419 "match_v" BOOLEAN;
2420 "member_id_v" "member"."id"%TYPE;
2421 "seed_v" TEXT;
2422 "initiative_id_v" "initiative"."id"%TYPE;
2423 BEGIN
2424 SELECT "notification_counter", "notification_sample_size"
2425 INTO "counter_v", "sample_size_v"
2426 FROM "member" WHERE "id" = "recipient_id_p";
2427 "initiative_id_ary" := '{}';
2428 LOOP
2429 "match_v" := FALSE;
2430 FOR "member_id_v", "seed_v" IN
2431 SELECT * FROM (
2432 SELECT DISTINCT
2433 "supporter"."member_id",
2434 md5(
2435 "recipient_id_p" || '-' ||
2436 "counter_v" || '-' ||
2437 "area_id_p" || '-' ||
2438 "supporter"."member_id"
2439 ) AS "seed"
2440 FROM "supporter"
2441 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
2442 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2443 WHERE "supporter"."member_id" != "recipient_id_p"
2444 AND "issue"."area_id" = "area_id_p"
2445 AND "issue"."state" IN ('admission', 'discussion', 'verification')
2446 ) AS "subquery"
2447 ORDER BY "seed"
2448 LOOP
2449 SELECT "initiative"."id" INTO "initiative_id_v"
2450 FROM "initiative"
2451 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2452 JOIN "area" ON "area"."id" = "issue"."area_id"
2453 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
2454 LEFT JOIN "supporter" AS "self_support" ON
2455 "self_support"."initiative_id" = "initiative"."id" AND
2456 "self_support"."member_id" = "recipient_id_p"
2457 LEFT JOIN "privilege" ON
2458 "privilege"."member_id" = "recipient_id_p" AND
2459 "privilege"."unit_id" = "area"."unit_id" AND
2460 "privilege"."voting_right" = TRUE
2461 LEFT JOIN "subscription" ON
2462 "subscription"."member_id" = "recipient_id_p" AND
2463 "subscription"."unit_id" = "area"."unit_id"
2464 LEFT JOIN "ignored_initiative" ON
2465 "ignored_initiative"."member_id" = "recipient_id_p" AND
2466 "ignored_initiative"."initiative_id" = "initiative"."id"
2467 WHERE "supporter"."member_id" = "member_id_v"
2468 AND "issue"."area_id" = "area_id_p"
2469 AND "issue"."state" IN ('admission', 'discussion', 'verification')
2470 AND "initiative"."revoked" ISNULL
2471 AND "self_support"."member_id" ISNULL
2472 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
2473 AND (
2474 "privilege"."member_id" NOTNULL OR
2475 "subscription"."member_id" NOTNULL )
2476 AND "ignored_initiative"."member_id" ISNULL
2477 AND NOT EXISTS (
2478 SELECT NULL FROM "draft"
2479 JOIN "ignored_member" ON
2480 "ignored_member"."member_id" = "recipient_id_p" AND
2481 "ignored_member"."other_member_id" = "draft"."author_id"
2482 WHERE "draft"."initiative_id" = "initiative"."id"
2483 )
2484 ORDER BY md5("seed_v" || '-' || "initiative"."id")
2485 LIMIT 1;
2486 IF FOUND THEN
2487 "match_v" := TRUE;
2488 RETURN NEXT "initiative_id_v";
2489 IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
2490 RETURN;
2491 END IF;
2492 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
2493 END IF;
2494 END LOOP;
2495 EXIT WHEN NOT "match_v";
2496 END LOOP;
2497 RETURN;
2498 END;
2499 $$;
2501 CREATE VIEW "updated_or_featured_initiative" AS
2502 SELECT
2503 "subquery".*,
2504 NOT EXISTS (
2505 SELECT NULL FROM "initiative" AS "better_initiative"
2506 WHERE "better_initiative"."issue_id" = "initiative"."issue_id"
2507 AND
2508 ( COALESCE("better_initiative"."supporter_count", -1),
2509 -"better_initiative"."id" ) >
2510 ( COALESCE("initiative"."supporter_count", -1),
2511 -"initiative"."id" )
2512 ) AS "leading"
2513 FROM (
2514 SELECT * FROM "updated_initiative"
2515 UNION ALL
2516 SELECT
2517 "member"."id" AS "recipient_id",
2518 TRUE AS "featured",
2519 "featured_initiative_id" AS "initiative_id"
2520 FROM "member" CROSS JOIN "area"
2521 CROSS JOIN LATERAL
2522 "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id"
2523 JOIN "initiative" ON "initiative"."id" = "featured_initiative_id"
2524 ) AS "subquery"
2525 JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id";
2527 CREATE VIEW "leading_complement_initiative" AS
2528 SELECT * FROM (
2529 SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id")
2530 "uf_initiative"."recipient_id",
2531 FALSE AS "featured",
2532 "uf_initiative"."initiative_id",
2533 TRUE AS "leading"
2534 FROM "updated_or_featured_initiative" AS "uf_initiative"
2535 JOIN "initiative" AS "uf_initiative_full" ON
2536 "uf_initiative_full"."id" = "uf_initiative"."initiative_id"
2537 JOIN "initiative" ON
2538 "initiative"."issue_id" = "uf_initiative_full"."issue_id"
2539 WHERE "initiative"."revoked" ISNULL
2540 ORDER BY
2541 "uf_initiative"."recipient_id",
2542 "initiative"."issue_id",
2543 "initiative"."supporter_count" DESC,
2544 "initiative"."id"
2545 ) AS "subquery"
2546 WHERE NOT EXISTS (
2547 SELECT NULL FROM "updated_or_featured_initiative" AS "other"
2548 WHERE "other"."recipient_id" = "subquery"."recipient_id"
2549 AND "other"."initiative_id" = "subquery"."initiative_id"
2550 );
2552 CREATE VIEW "unfiltered_initiative_for_notification" AS
2553 SELECT
2554 "subquery".*,
2555 "supporter"."member_id" NOTNULL AS "supported",
2556 CASE WHEN "supporter"."member_id" NOTNULL THEN
2557 EXISTS (
2558 SELECT NULL FROM "draft"
2559 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
2560 AND "draft"."id" > "supporter"."draft_id"
2561 )
2562 ELSE
2563 EXISTS (
2564 SELECT NULL FROM "draft"
2565 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
2566 AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE)
2567 )
2568 END AS "new_draft",
2569 CASE WHEN "supporter"."member_id" NOTNULL THEN
2570 ( SELECT count(1) FROM "suggestion"
2571 LEFT JOIN "opinion" ON
2572 "opinion"."member_id" = "supporter"."member_id" AND
2573 "opinion"."suggestion_id" = "suggestion"."id"
2574 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
2575 AND "opinion"."member_id" ISNULL
2576 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
2577 )
2578 ELSE
2579 ( SELECT count(1) FROM "suggestion"
2580 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
2581 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
2582 )
2583 END AS "new_suggestion_count"
2584 FROM (
2585 SELECT * FROM "updated_or_featured_initiative"
2586 UNION ALL
2587 SELECT * FROM "leading_complement_initiative"
2588 ) AS "subquery"
2589 LEFT JOIN "supporter" ON
2590 "supporter"."member_id" = "subquery"."recipient_id" AND
2591 "supporter"."initiative_id" = "subquery"."initiative_id"
2592 LEFT JOIN "initiative_notification_sent" AS "sent" ON
2593 "sent"."member_id" = "subquery"."recipient_id" AND
2594 "sent"."initiative_id" = "subquery"."initiative_id";
2596 CREATE VIEW "initiative_for_notification" AS
2597 SELECT "unfiltered1".*
2598 FROM "unfiltered_initiative_for_notification" "unfiltered1"
2599 JOIN "initiative" AS "initiative1" ON
2600 "initiative1"."id" = "unfiltered1"."initiative_id"
2601 JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id"
2602 WHERE EXISTS (
2603 SELECT NULL
2604 FROM "unfiltered_initiative_for_notification" "unfiltered2"
2605 JOIN "initiative" AS "initiative2" ON
2606 "initiative2"."id" = "unfiltered2"."initiative_id"
2607 JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id"
2608 WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"
2609 AND "issue1"."area_id" = "issue2"."area_id"
2610 AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 )
2611 );
2613 CREATE VIEW "newsletter_to_send" AS
2614 SELECT
2615 "member"."id" AS "recipient_id",
2616 "newsletter"."id" AS "newsletter_id"
2617 FROM "newsletter" CROSS JOIN "member"
2618 LEFT JOIN "privilege" ON
2619 "privilege"."member_id" = "member"."id" AND
2620 "privilege"."unit_id" = "newsletter"."unit_id" AND
2621 "privilege"."voting_right" = TRUE
2622 LEFT JOIN "subscription" ON
2623 "subscription"."member_id" = "member"."id" AND
2624 "subscription"."unit_id" = "newsletter"."unit_id"
2625 WHERE "newsletter"."published" <= now()
2626 AND "newsletter"."sent" ISNULL
2627 AND "member"."locked" = FALSE
2628 AND (
2629 "member"."disable_notifications" = FALSE OR
2630 "newsletter"."include_all_members" = TRUE )
2631 AND (
2632 "newsletter"."unit_id" ISNULL OR
2633 "privilege"."member_id" NOTNULL OR
2634 "subscription"."member_id" NOTNULL );
2638 ------------------------------------------------------
2639 -- Row set returning function for delegation chains --
2640 ------------------------------------------------------
2643 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2644 ('first', 'intermediate', 'last', 'repetition');
2646 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2649 CREATE TYPE "delegation_chain_row" AS (
2650 "index" INT4,
2651 "member_id" INT4,
2652 "member_valid" BOOLEAN,
2653 "participation" BOOLEAN,
2654 "overridden" BOOLEAN,
2655 "scope_in" "delegation_scope",
2656 "scope_out" "delegation_scope",
2657 "disabled_out" BOOLEAN,
2658 "loop" "delegation_chain_loop_tag" );
2660 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
2662 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2663 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';
2664 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2665 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2666 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2667 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2668 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2671 CREATE FUNCTION "delegation_chain_for_closed_issue"
2672 ( "member_id_p" "member"."id"%TYPE,
2673 "issue_id_p" "issue"."id"%TYPE )
2674 RETURNS SETOF "delegation_chain_row"
2675 LANGUAGE 'plpgsql' STABLE AS $$
2676 DECLARE
2677 "output_row" "delegation_chain_row";
2678 "direct_voter_row" "direct_voter"%ROWTYPE;
2679 "delegating_voter_row" "delegating_voter"%ROWTYPE;
2680 BEGIN
2681 "output_row"."index" := 0;
2682 "output_row"."member_id" := "member_id_p";
2683 "output_row"."member_valid" := TRUE;
2684 "output_row"."participation" := FALSE;
2685 "output_row"."overridden" := FALSE;
2686 "output_row"."disabled_out" := FALSE;
2687 LOOP
2688 SELECT INTO "direct_voter_row" * FROM "direct_voter"
2689 WHERE "issue_id" = "issue_id_p"
2690 AND "member_id" = "output_row"."member_id";
2691 IF "direct_voter_row"."member_id" NOTNULL THEN
2692 "output_row"."participation" := TRUE;
2693 "output_row"."scope_out" := NULL;
2694 "output_row"."disabled_out" := NULL;
2695 RETURN NEXT "output_row";
2696 RETURN;
2697 END IF;
2698 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
2699 WHERE "issue_id" = "issue_id_p"
2700 AND "member_id" = "output_row"."member_id";
2701 IF "delegating_voter_row"."member_id" ISNULL THEN
2702 RETURN;
2703 END IF;
2704 "output_row"."scope_out" := "delegating_voter_row"."scope";
2705 RETURN NEXT "output_row";
2706 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
2707 "output_row"."scope_in" := "output_row"."scope_out";
2708 END LOOP;
2709 END;
2710 $$;
2712 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
2713 ( "member"."id"%TYPE,
2714 "member"."id"%TYPE )
2715 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
2718 CREATE FUNCTION "delegation_chain"
2719 ( "member_id_p" "member"."id"%TYPE,
2720 "unit_id_p" "unit"."id"%TYPE,
2721 "area_id_p" "area"."id"%TYPE,
2722 "issue_id_p" "issue"."id"%TYPE,
2723 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2724 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2725 RETURNS SETOF "delegation_chain_row"
2726 LANGUAGE 'plpgsql' STABLE AS $$
2727 DECLARE
2728 "scope_v" "delegation_scope";
2729 "unit_id_v" "unit"."id"%TYPE;
2730 "area_id_v" "area"."id"%TYPE;
2731 "issue_row" "issue"%ROWTYPE;
2732 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2733 "loop_member_id_v" "member"."id"%TYPE;
2734 "output_row" "delegation_chain_row";
2735 "output_rows" "delegation_chain_row"[];
2736 "simulate_v" BOOLEAN;
2737 "simulate_here_v" BOOLEAN;
2738 "delegation_row" "delegation"%ROWTYPE;
2739 "row_count" INT4;
2740 "i" INT4;
2741 "loop_v" BOOLEAN;
2742 BEGIN
2743 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
2744 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
2745 END IF;
2746 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
2747 "simulate_v" := TRUE;
2748 ELSE
2749 "simulate_v" := FALSE;
2750 END IF;
2751 IF
2752 "unit_id_p" NOTNULL AND
2753 "area_id_p" ISNULL AND
2754 "issue_id_p" ISNULL
2755 THEN
2756 "scope_v" := 'unit';
2757 "unit_id_v" := "unit_id_p";
2758 ELSIF
2759 "unit_id_p" ISNULL AND
2760 "area_id_p" NOTNULL AND
2761 "issue_id_p" ISNULL
2762 THEN
2763 "scope_v" := 'area';
2764 "area_id_v" := "area_id_p";
2765 SELECT "unit_id" INTO "unit_id_v"
2766 FROM "area" WHERE "id" = "area_id_v";
2767 ELSIF
2768 "unit_id_p" ISNULL AND
2769 "area_id_p" ISNULL AND
2770 "issue_id_p" NOTNULL
2771 THEN
2772 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
2773 IF "issue_row"."id" ISNULL THEN
2774 RETURN;
2775 END IF;
2776 IF "issue_row"."closed" NOTNULL THEN
2777 IF "simulate_v" THEN
2778 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
2779 END IF;
2780 FOR "output_row" IN
2781 SELECT * FROM
2782 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
2783 LOOP
2784 RETURN NEXT "output_row";
2785 END LOOP;
2786 RETURN;
2787 END IF;
2788 "scope_v" := 'issue';
2789 SELECT "area_id" INTO "area_id_v"
2790 FROM "issue" WHERE "id" = "issue_id_p";
2791 SELECT "unit_id" INTO "unit_id_v"
2792 FROM "area" WHERE "id" = "area_id_v";
2793 ELSE
2794 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2795 END IF;
2796 "visited_member_ids" := '{}';
2797 "loop_member_id_v" := NULL;
2798 "output_rows" := '{}';
2799 "output_row"."index" := 0;
2800 "output_row"."member_id" := "member_id_p";
2801 "output_row"."member_valid" := TRUE;
2802 "output_row"."participation" := FALSE;
2803 "output_row"."overridden" := FALSE;
2804 "output_row"."disabled_out" := FALSE;
2805 "output_row"."scope_out" := NULL;
2806 LOOP
2807 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2808 "loop_member_id_v" := "output_row"."member_id";
2809 ELSE
2810 "visited_member_ids" :=
2811 "visited_member_ids" || "output_row"."member_id";
2812 END IF;
2813 IF "output_row"."participation" ISNULL THEN
2814 "output_row"."overridden" := NULL;
2815 ELSIF "output_row"."participation" THEN
2816 "output_row"."overridden" := TRUE;
2817 END IF;
2818 "output_row"."scope_in" := "output_row"."scope_out";
2819 "output_row"."member_valid" := EXISTS (
2820 SELECT NULL FROM "member" JOIN "privilege"
2821 ON "privilege"."member_id" = "member"."id"
2822 AND "privilege"."unit_id" = "unit_id_v"
2823 WHERE "id" = "output_row"."member_id"
2824 AND "member"."active" AND "privilege"."voting_right"
2825 );
2826 "simulate_here_v" := (
2827 "simulate_v" AND
2828 "output_row"."member_id" = "member_id_p"
2829 );
2830 "delegation_row" := ROW(NULL);
2831 IF "output_row"."member_valid" OR "simulate_here_v" THEN
2832 IF "scope_v" = 'unit' THEN
2833 IF NOT "simulate_here_v" THEN
2834 SELECT * INTO "delegation_row" FROM "delegation"
2835 WHERE "truster_id" = "output_row"."member_id"
2836 AND "unit_id" = "unit_id_v";
2837 END IF;
2838 ELSIF "scope_v" = 'area' THEN
2839 "output_row"."participation" := EXISTS (
2840 SELECT NULL FROM "membership"
2841 WHERE "area_id" = "area_id_p"
2842 AND "member_id" = "output_row"."member_id"
2843 );
2844 IF "simulate_here_v" THEN
2845 IF "simulate_trustee_id_p" ISNULL THEN
2846 SELECT * INTO "delegation_row" FROM "delegation"
2847 WHERE "truster_id" = "output_row"."member_id"
2848 AND "unit_id" = "unit_id_v";
2849 END IF;
2850 ELSE
2851 SELECT * INTO "delegation_row" FROM "delegation"
2852 WHERE "truster_id" = "output_row"."member_id"
2853 AND (
2854 "unit_id" = "unit_id_v" OR
2855 "area_id" = "area_id_v"
2856 )
2857 ORDER BY "scope" DESC;
2858 END IF;
2859 ELSIF "scope_v" = 'issue' THEN
2860 IF "issue_row"."fully_frozen" ISNULL THEN
2861 "output_row"."participation" := EXISTS (
2862 SELECT NULL FROM "interest"
2863 WHERE "issue_id" = "issue_id_p"
2864 AND "member_id" = "output_row"."member_id"
2865 );
2866 ELSE
2867 IF "output_row"."member_id" = "member_id_p" THEN
2868 "output_row"."participation" := EXISTS (
2869 SELECT NULL FROM "direct_voter"
2870 WHERE "issue_id" = "issue_id_p"
2871 AND "member_id" = "output_row"."member_id"
2872 );
2873 ELSE
2874 "output_row"."participation" := NULL;
2875 END IF;
2876 END IF;
2877 IF "simulate_here_v" THEN
2878 IF "simulate_trustee_id_p" ISNULL THEN
2879 SELECT * INTO "delegation_row" FROM "delegation"
2880 WHERE "truster_id" = "output_row"."member_id"
2881 AND (
2882 "unit_id" = "unit_id_v" OR
2883 "area_id" = "area_id_v"
2884 )
2885 ORDER BY "scope" DESC;
2886 END IF;
2887 ELSE
2888 SELECT * INTO "delegation_row" FROM "delegation"
2889 WHERE "truster_id" = "output_row"."member_id"
2890 AND (
2891 "unit_id" = "unit_id_v" OR
2892 "area_id" = "area_id_v" OR
2893 "issue_id" = "issue_id_p"
2894 )
2895 ORDER BY "scope" DESC;
2896 END IF;
2897 END IF;
2898 ELSE
2899 "output_row"."participation" := FALSE;
2900 END IF;
2901 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
2902 "output_row"."scope_out" := "scope_v";
2903 "output_rows" := "output_rows" || "output_row";
2904 "output_row"."member_id" := "simulate_trustee_id_p";
2905 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2906 "output_row"."scope_out" := "delegation_row"."scope";
2907 "output_rows" := "output_rows" || "output_row";
2908 "output_row"."member_id" := "delegation_row"."trustee_id";
2909 ELSIF "delegation_row"."scope" NOTNULL THEN
2910 "output_row"."scope_out" := "delegation_row"."scope";
2911 "output_row"."disabled_out" := TRUE;
2912 "output_rows" := "output_rows" || "output_row";
2913 EXIT;
2914 ELSE
2915 "output_row"."scope_out" := NULL;
2916 "output_rows" := "output_rows" || "output_row";
2917 EXIT;
2918 END IF;
2919 EXIT WHEN "loop_member_id_v" NOTNULL;
2920 "output_row"."index" := "output_row"."index" + 1;
2921 END LOOP;
2922 "row_count" := array_upper("output_rows", 1);
2923 "i" := 1;
2924 "loop_v" := FALSE;
2925 LOOP
2926 "output_row" := "output_rows"["i"];
2927 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2928 IF "loop_v" THEN
2929 IF "i" + 1 = "row_count" THEN
2930 "output_row"."loop" := 'last';
2931 ELSIF "i" = "row_count" THEN
2932 "output_row"."loop" := 'repetition';
2933 ELSE
2934 "output_row"."loop" := 'intermediate';
2935 END IF;
2936 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2937 "output_row"."loop" := 'first';
2938 "loop_v" := TRUE;
2939 END IF;
2940 IF "scope_v" = 'unit' THEN
2941 "output_row"."participation" := NULL;
2942 END IF;
2943 RETURN NEXT "output_row";
2944 "i" := "i" + 1;
2945 END LOOP;
2946 RETURN;
2947 END;
2948 $$;
2950 COMMENT ON FUNCTION "delegation_chain"
2951 ( "member"."id"%TYPE,
2952 "unit"."id"%TYPE,
2953 "area"."id"%TYPE,
2954 "issue"."id"%TYPE,
2955 "member"."id"%TYPE,
2956 BOOLEAN )
2957 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
2961 ---------------------------------------------------------
2962 -- Single row returning function for delegation chains --
2963 ---------------------------------------------------------
2966 CREATE TYPE "delegation_info_loop_type" AS ENUM
2967 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
2969 COMMENT ON TYPE "delegation_info_loop_type" IS 'Type of "delegation_loop" in "delegation_info_type"; ''own'' means loop to self, ''first'' means loop to first trustee, ''first_ellipsis'' means loop to ellipsis after first trustee, ''other'' means loop to other trustee, ''other_ellipsis'' means loop to ellipsis after other trustee''';
2972 CREATE TYPE "delegation_info_type" AS (
2973 "own_participation" BOOLEAN,
2974 "own_delegation_scope" "delegation_scope",
2975 "first_trustee_id" INT4,
2976 "first_trustee_participation" BOOLEAN,
2977 "first_trustee_ellipsis" BOOLEAN,
2978 "other_trustee_id" INT4,
2979 "other_trustee_participation" BOOLEAN,
2980 "other_trustee_ellipsis" BOOLEAN,
2981 "delegation_loop" "delegation_info_loop_type",
2982 "participating_member_id" INT4 );
2984 COMMENT ON TYPE "delegation_info_type" IS 'Type of result returned by "delegation_info" function; For meaning of "participation" check comment on "delegation_chain_row" type';
2986 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
2987 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
2988 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
2989 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
2990 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
2991 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
2992 COMMENT ON COLUMN "delegation_info_type"."other_trustee_participation" IS 'Another trustee is participating (redundant field: if "other_trustee_id" is set, then "other_trustee_participation" is always TRUE, else "other_trustee_participation" is NULL)';
2993 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
2994 COMMENT ON COLUMN "delegation_info_type"."delegation_loop" IS 'Non-NULL value, if delegation chain contains a circle; See comment on "delegation_info_loop_type" for details';
2995 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';
2998 CREATE FUNCTION "delegation_info"
2999 ( "member_id_p" "member"."id"%TYPE,
3000 "unit_id_p" "unit"."id"%TYPE,
3001 "area_id_p" "area"."id"%TYPE,
3002 "issue_id_p" "issue"."id"%TYPE,
3003 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
3004 "simulate_default_p" BOOLEAN DEFAULT FALSE )
3005 RETURNS "delegation_info_type"
3006 LANGUAGE 'plpgsql' STABLE AS $$
3007 DECLARE
3008 "current_row" "delegation_chain_row";
3009 "result" "delegation_info_type";
3010 BEGIN
3011 "result"."own_participation" := FALSE;
3012 FOR "current_row" IN
3013 SELECT * FROM "delegation_chain"(
3014 "member_id_p",
3015 "unit_id_p", "area_id_p", "issue_id_p",
3016 "simulate_trustee_id_p", "simulate_default_p")
3017 LOOP
3018 IF
3019 "result"."participating_member_id" ISNULL AND
3020 "current_row"."participation"
3021 THEN
3022 "result"."participating_member_id" := "current_row"."member_id";
3023 END IF;
3024 IF "current_row"."member_id" = "member_id_p" THEN
3025 "result"."own_participation" := "current_row"."participation";
3026 "result"."own_delegation_scope" := "current_row"."scope_out";
3027 IF "current_row"."loop" = 'first' THEN
3028 "result"."delegation_loop" := 'own';
3029 END IF;
3030 ELSIF
3031 "current_row"."member_valid" AND
3032 ( "current_row"."loop" ISNULL OR
3033 "current_row"."loop" != 'repetition' )
3034 THEN
3035 IF "result"."first_trustee_id" ISNULL THEN
3036 "result"."first_trustee_id" := "current_row"."member_id";
3037 "result"."first_trustee_participation" := "current_row"."participation";
3038 "result"."first_trustee_ellipsis" := FALSE;
3039 IF "current_row"."loop" = 'first' THEN
3040 "result"."delegation_loop" := 'first';
3041 END IF;
3042 ELSIF "result"."other_trustee_id" ISNULL THEN
3043 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
3044 "result"."other_trustee_id" := "current_row"."member_id";
3045 "result"."other_trustee_participation" := TRUE;
3046 "result"."other_trustee_ellipsis" := FALSE;
3047 IF "current_row"."loop" = 'first' THEN
3048 "result"."delegation_loop" := 'other';
3049 END IF;
3050 ELSE
3051 "result"."first_trustee_ellipsis" := TRUE;
3052 IF "current_row"."loop" = 'first' THEN
3053 "result"."delegation_loop" := 'first_ellipsis';
3054 END IF;
3055 END IF;
3056 ELSE
3057 "result"."other_trustee_ellipsis" := TRUE;
3058 IF "current_row"."loop" = 'first' THEN
3059 "result"."delegation_loop" := 'other_ellipsis';
3060 END IF;
3061 END IF;
3062 END IF;
3063 END LOOP;
3064 RETURN "result";
3065 END;
3066 $$;
3068 COMMENT ON FUNCTION "delegation_info"
3069 ( "member"."id"%TYPE,
3070 "unit"."id"%TYPE,
3071 "area"."id"%TYPE,
3072 "issue"."id"%TYPE,
3073 "member"."id"%TYPE,
3074 BOOLEAN )
3075 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
3079 ---------------------------
3080 -- Transaction isolation --
3081 ---------------------------
3084 CREATE FUNCTION "require_transaction_isolation"()
3085 RETURNS VOID
3086 LANGUAGE 'plpgsql' VOLATILE AS $$
3087 BEGIN
3088 IF
3089 current_setting('transaction_isolation') NOT IN
3090 ('repeatable read', 'serializable')
3091 THEN
3092 RAISE EXCEPTION 'Insufficient transaction isolation level' USING
3093 HINT = 'Consider using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ.';
3094 END IF;
3095 RETURN;
3096 END;
3097 $$;
3099 COMMENT ON FUNCTION "require_transaction_isolation"() IS 'Throws an exception, if transaction isolation level is too low to provide a consistent snapshot';
3102 CREATE FUNCTION "dont_require_transaction_isolation"()
3103 RETURNS VOID
3104 LANGUAGE 'plpgsql' VOLATILE AS $$
3105 BEGIN
3106 IF
3107 current_setting('transaction_isolation') IN
3108 ('repeatable read', 'serializable')
3109 THEN
3110 RAISE WARNING 'Unneccessary transaction isolation level: %',
3111 current_setting('transaction_isolation');
3112 END IF;
3113 RETURN;
3114 END;
3115 $$;
3117 COMMENT ON FUNCTION "dont_require_transaction_isolation"() IS 'Raises a warning, if transaction isolation level is higher than READ COMMITTED';
3121 -------------------------
3122 -- Notification system --
3123 -------------------------
3125 CREATE FUNCTION "get_initiatives_for_notification"
3126 ( "recipient_id_p" "member"."id"%TYPE )
3127 RETURNS SETOF "initiative_for_notification"
3128 LANGUAGE 'plpgsql' VOLATILE AS $$
3129 DECLARE
3130 "result_row" "initiative_for_notification"%ROWTYPE;
3131 "last_draft_id_v" "draft"."id"%TYPE;
3132 "last_suggestion_id_v" "suggestion"."id"%TYPE;
3133 BEGIN
3134 PERFORM "require_transaction_isolation"();
3135 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
3136 FOR "result_row" IN
3137 SELECT * FROM "initiative_for_notification"
3138 WHERE "recipient_id" = "recipient_id_p"
3139 LOOP
3140 SELECT "id" INTO "last_draft_id_v" FROM "draft"
3141 WHERE "draft"."initiative_id" = "result_row"."initiative_id"
3142 ORDER BY "id" DESC LIMIT 1;
3143 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
3144 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
3145 ORDER BY "id" DESC LIMIT 1;
3146 INSERT INTO "initiative_notification_sent"
3147 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
3148 VALUES (
3149 "recipient_id_p",
3150 "result_row"."initiative_id",
3151 "last_draft_id_v",
3152 "last_suggestion_id_v" )
3153 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
3154 "last_draft_id" = CASE
3155 WHEN "initiative_notification_sent"."last_draft_id" > "last_draft_id_v"
3156 THEN "initiative_notification_sent"."last_draft_id"
3157 ELSE "last_draft_id_v"
3158 END,
3159 "last_suggestion_id" = CASE
3160 WHEN "initiative_notification_sent"."last_suggestion_id" > "last_suggestion_id_v"
3161 THEN "initiative_notification_sent"."last_suggestion_id"
3162 ELSE "last_suggestion_id_v"
3163 END;
3164 RETURN NEXT "result_row";
3165 END LOOP;
3166 DELETE FROM "initiative_notification_sent"
3167 USING "initiative", "issue"
3168 WHERE "initiative_notification_sent"."member_id" = "recipient_id_p"
3169 AND "initiative"."id" = "initiative_notification_sent"."initiative_id"
3170 AND "issue"."id" = "initiative"."issue_id"
3171 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
3172 UPDATE "member" SET "notification_counter" = "notification_counter" + 1
3173 WHERE "id" = "recipient_id_p";
3174 RETURN;
3175 END;
3176 $$;
3180 ------------------------------------------------------------------------
3181 -- Regular tasks, except calculcation of snapshots and voting results --
3182 ------------------------------------------------------------------------
3185 CREATE FUNCTION "check_activity"()
3186 RETURNS VOID
3187 LANGUAGE 'plpgsql' VOLATILE AS $$
3188 DECLARE
3189 "system_setting_row" "system_setting"%ROWTYPE;
3190 BEGIN
3191 PERFORM "dont_require_transaction_isolation"();
3192 SELECT * INTO "system_setting_row" FROM "system_setting";
3193 IF "system_setting_row"."member_ttl" NOTNULL THEN
3194 UPDATE "member" SET "active" = FALSE
3195 WHERE "active" = TRUE
3196 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
3197 END IF;
3198 RETURN;
3199 END;
3200 $$;
3202 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
3205 CREATE FUNCTION "calculate_member_counts"()
3206 RETURNS VOID
3207 LANGUAGE 'plpgsql' VOLATILE AS $$
3208 BEGIN
3209 PERFORM "require_transaction_isolation"();
3210 DELETE FROM "member_count";
3211 INSERT INTO "member_count" ("total_count")
3212 SELECT "total_count" FROM "member_count_view";
3213 UPDATE "unit" SET "member_count" = "view"."member_count"
3214 FROM "unit_member_count" AS "view"
3215 WHERE "view"."unit_id" = "unit"."id";
3216 UPDATE "area" SET
3217 "direct_member_count" = "view"."direct_member_count",
3218 "member_weight" = "view"."member_weight"
3219 FROM "area_member_count" AS "view"
3220 WHERE "view"."area_id" = "area"."id";
3221 RETURN;
3222 END;
3223 $$;
3225 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"';
3229 ------------------------------------
3230 -- Calculation of harmonic weight --
3231 ------------------------------------
3234 CREATE VIEW "remaining_harmonic_supporter_weight" AS
3235 SELECT
3236 "direct_interest_snapshot"."issue_id",
3237 "direct_interest_snapshot"."event",
3238 "direct_interest_snapshot"."member_id",
3239 "direct_interest_snapshot"."weight" AS "weight_num",
3240 count("initiative"."id") AS "weight_den"
3241 FROM "issue"
3242 JOIN "direct_interest_snapshot"
3243 ON "issue"."id" = "direct_interest_snapshot"."issue_id"
3244 AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
3245 JOIN "initiative"
3246 ON "issue"."id" = "initiative"."issue_id"
3247 AND "initiative"."harmonic_weight" ISNULL
3248 JOIN "direct_supporter_snapshot"
3249 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
3250 AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event"
3251 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
3252 AND (
3253 "direct_supporter_snapshot"."satisfied" = TRUE OR
3254 coalesce("initiative"."admitted", FALSE) = FALSE
3255 )
3256 GROUP BY
3257 "direct_interest_snapshot"."issue_id",
3258 "direct_interest_snapshot"."event",
3259 "direct_interest_snapshot"."member_id",
3260 "direct_interest_snapshot"."weight";
3262 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
3265 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
3266 SELECT
3267 "initiative"."issue_id",
3268 "initiative"."id" AS "initiative_id",
3269 "initiative"."admitted",
3270 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
3271 "remaining_harmonic_supporter_weight"."weight_den"
3272 FROM "remaining_harmonic_supporter_weight"
3273 JOIN "initiative"
3274 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
3275 AND "initiative"."harmonic_weight" ISNULL
3276 JOIN "direct_supporter_snapshot"
3277 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
3278 AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"
3279 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
3280 AND (
3281 "direct_supporter_snapshot"."satisfied" = TRUE OR
3282 coalesce("initiative"."admitted", FALSE) = FALSE
3283 )
3284 GROUP BY
3285 "initiative"."issue_id",
3286 "initiative"."id",
3287 "initiative"."admitted",
3288 "remaining_harmonic_supporter_weight"."weight_den";
3290 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
3293 CREATE VIEW "remaining_harmonic_initiative_weight_dummies" AS
3294 SELECT
3295 "issue_id",
3296 "id" AS "initiative_id",
3297 "admitted",
3298 0 AS "weight_num",
3299 1 AS "weight_den"
3300 FROM "initiative"
3301 WHERE "harmonic_weight" ISNULL;
3303 COMMENT ON VIEW "remaining_harmonic_initiative_weight_dummies" IS 'Helper view for function "set_harmonic_initiative_weights" providing dummy weights of zero value, which are needed for corner cases where there are no supporters for an initiative at all';
3306 CREATE FUNCTION "set_harmonic_initiative_weights"
3307 ( "issue_id_p" "issue"."id"%TYPE )
3308 RETURNS VOID
3309 LANGUAGE 'plpgsql' VOLATILE AS $$
3310 DECLARE
3311 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
3312 "i" INT4;
3313 "count_v" INT4;
3314 "summand_v" FLOAT;
3315 "id_ary" INT4[];
3316 "weight_ary" FLOAT[];
3317 "min_weight_v" FLOAT;
3318 BEGIN
3319 PERFORM "require_transaction_isolation"();
3320 UPDATE "initiative" SET "harmonic_weight" = NULL
3321 WHERE "issue_id" = "issue_id_p";
3322 LOOP
3323 "min_weight_v" := NULL;
3324 "i" := 0;
3325 "count_v" := 0;
3326 FOR "weight_row" IN
3327 SELECT * FROM "remaining_harmonic_initiative_weight_summands"
3328 WHERE "issue_id" = "issue_id_p"
3329 AND (
3330 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
3331 SELECT NULL FROM "initiative"
3332 WHERE "issue_id" = "issue_id_p"
3333 AND "harmonic_weight" ISNULL
3334 AND coalesce("admitted", FALSE) = FALSE
3335 )
3336 )
3337 UNION ALL -- needed for corner cases
3338 SELECT * FROM "remaining_harmonic_initiative_weight_dummies"
3339 WHERE "issue_id" = "issue_id_p"
3340 AND (
3341 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
3342 SELECT NULL FROM "initiative"
3343 WHERE "issue_id" = "issue_id_p"
3344 AND "harmonic_weight" ISNULL
3345 AND coalesce("admitted", FALSE) = FALSE
3346 )
3347 )
3348 ORDER BY "initiative_id" DESC, "weight_den" DESC
3349 -- NOTE: non-admitted initiatives placed first (at last positions),
3350 -- latest initiatives treated worse in case of tie
3351 LOOP
3352 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
3353 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
3354 "i" := "i" + 1;
3355 "count_v" := "i";
3356 "id_ary"["i"] := "weight_row"."initiative_id";
3357 "weight_ary"["i"] := "summand_v";
3358 ELSE
3359 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
3360 END IF;
3361 END LOOP;
3362 EXIT WHEN "count_v" = 0;
3363 "i" := 1;
3364 LOOP
3365 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
3366 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
3367 "min_weight_v" := "weight_ary"["i"];
3368 END IF;
3369 "i" := "i" + 1;
3370 EXIT WHEN "i" > "count_v";
3371 END LOOP;
3372 "i" := 1;
3373 LOOP
3374 IF "weight_ary"["i"] = "min_weight_v" THEN
3375 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
3376 WHERE "id" = "id_ary"["i"];
3377 EXIT;
3378 END IF;
3379 "i" := "i" + 1;
3380 END LOOP;
3381 END LOOP;
3382 UPDATE "initiative" SET "harmonic_weight" = 0
3383 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
3384 END;
3385 $$;
3387 COMMENT ON FUNCTION "set_harmonic_initiative_weights"
3388 ( "issue"."id"%TYPE )
3389 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
3393 ------------------------------
3394 -- Calculation of snapshots --
3395 ------------------------------
3398 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
3399 ( "issue_id_p" "issue"."id"%TYPE,
3400 "member_id_p" "member"."id"%TYPE,
3401 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3402 RETURNS "direct_population_snapshot"."weight"%TYPE
3403 LANGUAGE 'plpgsql' VOLATILE AS $$
3404 DECLARE
3405 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3406 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
3407 "weight_v" INT4;
3408 "sub_weight_v" INT4;
3409 BEGIN
3410 PERFORM "require_transaction_isolation"();
3411 "weight_v" := 0;
3412 FOR "issue_delegation_row" IN
3413 SELECT * FROM "issue_delegation"
3414 WHERE "trustee_id" = "member_id_p"
3415 AND "issue_id" = "issue_id_p"
3416 LOOP
3417 IF NOT EXISTS (
3418 SELECT NULL FROM "direct_population_snapshot"
3419 WHERE "issue_id" = "issue_id_p"
3420 AND "event" = 'periodic'
3421 AND "member_id" = "issue_delegation_row"."truster_id"
3422 ) AND NOT EXISTS (
3423 SELECT NULL FROM "delegating_population_snapshot"
3424 WHERE "issue_id" = "issue_id_p"
3425 AND "event" = 'periodic'
3426 AND "member_id" = "issue_delegation_row"."truster_id"
3427 ) THEN
3428 "delegate_member_ids_v" :=
3429 "member_id_p" || "delegate_member_ids_p";
3430 INSERT INTO "delegating_population_snapshot" (
3431 "issue_id",
3432 "event",
3433 "member_id",
3434 "scope",
3435 "delegate_member_ids"
3436 ) VALUES (
3437 "issue_id_p",
3438 'periodic',
3439 "issue_delegation_row"."truster_id",
3440 "issue_delegation_row"."scope",
3441 "delegate_member_ids_v"
3442 );
3443 "sub_weight_v" := 1 +
3444 "weight_of_added_delegations_for_population_snapshot"(
3445 "issue_id_p",
3446 "issue_delegation_row"."truster_id",
3447 "delegate_member_ids_v"
3448 );
3449 UPDATE "delegating_population_snapshot"
3450 SET "weight" = "sub_weight_v"
3451 WHERE "issue_id" = "issue_id_p"
3452 AND "event" = 'periodic'
3453 AND "member_id" = "issue_delegation_row"."truster_id";
3454 "weight_v" := "weight_v" + "sub_weight_v";
3455 END IF;
3456 END LOOP;
3457 RETURN "weight_v";
3458 END;
3459 $$;
3461 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
3462 ( "issue"."id"%TYPE,
3463 "member"."id"%TYPE,
3464 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3465 IS 'Helper function for "create_population_snapshot" function';
3468 CREATE FUNCTION "create_population_snapshot"
3469 ( "issue_id_p" "issue"."id"%TYPE )
3470 RETURNS VOID
3471 LANGUAGE 'plpgsql' VOLATILE AS $$
3472 DECLARE
3473 "member_id_v" "member"."id"%TYPE;
3474 BEGIN
3475 PERFORM "require_transaction_isolation"();
3476 DELETE FROM "direct_population_snapshot"
3477 WHERE "issue_id" = "issue_id_p"
3478 AND "event" = 'periodic';
3479 DELETE FROM "delegating_population_snapshot"
3480 WHERE "issue_id" = "issue_id_p"
3481 AND "event" = 'periodic';
3482 INSERT INTO "direct_population_snapshot"
3483 ("issue_id", "event", "member_id")
3484 SELECT
3485 "issue_id_p" AS "issue_id",
3486 'periodic'::"snapshot_event" AS "event",
3487 "member"."id" AS "member_id"
3488 FROM "issue"
3489 JOIN "area" ON "issue"."area_id" = "area"."id"
3490 JOIN "membership" ON "area"."id" = "membership"."area_id"
3491 JOIN "member" ON "membership"."member_id" = "member"."id"
3492 JOIN "privilege"
3493 ON "privilege"."unit_id" = "area"."unit_id"
3494 AND "privilege"."member_id" = "member"."id"
3495 WHERE "issue"."id" = "issue_id_p"
3496 AND "member"."active" AND "privilege"."voting_right"
3497 UNION
3498 SELECT
3499 "issue_id_p" AS "issue_id",
3500 'periodic'::"snapshot_event" AS "event",
3501 "member"."id" AS "member_id"
3502 FROM "issue"
3503 JOIN "area" ON "issue"."area_id" = "area"."id"
3504 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3505 JOIN "member" ON "interest"."member_id" = "member"."id"
3506 JOIN "privilege"
3507 ON "privilege"."unit_id" = "area"."unit_id"
3508 AND "privilege"."member_id" = "member"."id"
3509 WHERE "issue"."id" = "issue_id_p"
3510 AND "member"."active" AND "privilege"."voting_right";
3511 FOR "member_id_v" IN
3512 SELECT "member_id" FROM "direct_population_snapshot"
3513 WHERE "issue_id" = "issue_id_p"
3514 AND "event" = 'periodic'
3515 LOOP
3516 UPDATE "direct_population_snapshot" SET
3517 "weight" = 1 +
3518 "weight_of_added_delegations_for_population_snapshot"(
3519 "issue_id_p",
3520 "member_id_v",
3521 '{}'
3522 )
3523 WHERE "issue_id" = "issue_id_p"
3524 AND "event" = 'periodic'
3525 AND "member_id" = "member_id_v";
3526 END LOOP;
3527 RETURN;
3528 END;
3529 $$;
3531 COMMENT ON FUNCTION "create_population_snapshot"
3532 ( "issue"."id"%TYPE )
3533 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.';
3536 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3537 ( "issue_id_p" "issue"."id"%TYPE,
3538 "member_id_p" "member"."id"%TYPE,
3539 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3540 RETURNS "direct_interest_snapshot"."weight"%TYPE
3541 LANGUAGE 'plpgsql' VOLATILE AS $$
3542 DECLARE
3543 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3544 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
3545 "weight_v" INT4;
3546 "sub_weight_v" INT4;
3547 BEGIN
3548 PERFORM "require_transaction_isolation"();
3549 "weight_v" := 0;
3550 FOR "issue_delegation_row" IN
3551 SELECT * FROM "issue_delegation"
3552 WHERE "trustee_id" = "member_id_p"
3553 AND "issue_id" = "issue_id_p"
3554 LOOP
3555 IF NOT EXISTS (
3556 SELECT NULL FROM "direct_interest_snapshot"
3557 WHERE "issue_id" = "issue_id_p"
3558 AND "event" = 'periodic'
3559 AND "member_id" = "issue_delegation_row"."truster_id"
3560 ) AND NOT EXISTS (
3561 SELECT NULL FROM "delegating_interest_snapshot"
3562 WHERE "issue_id" = "issue_id_p"
3563 AND "event" = 'periodic'
3564 AND "member_id" = "issue_delegation_row"."truster_id"
3565 ) THEN
3566 "delegate_member_ids_v" :=
3567 "member_id_p" || "delegate_member_ids_p";
3568 INSERT INTO "delegating_interest_snapshot" (
3569 "issue_id",
3570 "event",
3571 "member_id",
3572 "scope",
3573 "delegate_member_ids"
3574 ) VALUES (
3575 "issue_id_p",
3576 'periodic',
3577 "issue_delegation_row"."truster_id",
3578 "issue_delegation_row"."scope",
3579 "delegate_member_ids_v"
3580 );
3581 "sub_weight_v" := 1 +
3582 "weight_of_added_delegations_for_interest_snapshot"(
3583 "issue_id_p",
3584 "issue_delegation_row"."truster_id",
3585 "delegate_member_ids_v"
3586 );
3587 UPDATE "delegating_interest_snapshot"
3588 SET "weight" = "sub_weight_v"
3589 WHERE "issue_id" = "issue_id_p"
3590 AND "event" = 'periodic'
3591 AND "member_id" = "issue_delegation_row"."truster_id";
3592 "weight_v" := "weight_v" + "sub_weight_v";
3593 END IF;
3594 END LOOP;
3595 RETURN "weight_v";
3596 END;
3597 $$;
3599 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3600 ( "issue"."id"%TYPE,
3601 "member"."id"%TYPE,
3602 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3603 IS 'Helper function for "create_interest_snapshot" function';
3606 CREATE FUNCTION "create_interest_snapshot"
3607 ( "issue_id_p" "issue"."id"%TYPE )
3608 RETURNS VOID
3609 LANGUAGE 'plpgsql' VOLATILE AS $$
3610 DECLARE
3611 "member_id_v" "member"."id"%TYPE;
3612 BEGIN
3613 PERFORM "require_transaction_isolation"();
3614 DELETE FROM "direct_interest_snapshot"
3615 WHERE "issue_id" = "issue_id_p"
3616 AND "event" = 'periodic';
3617 DELETE FROM "delegating_interest_snapshot"
3618 WHERE "issue_id" = "issue_id_p"
3619 AND "event" = 'periodic';
3620 DELETE FROM "direct_supporter_snapshot"
3621 USING "initiative" -- NOTE: due to missing index on issue_id
3622 WHERE "initiative"."issue_id" = "issue_id_p"
3623 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
3624 AND "direct_supporter_snapshot"."event" = 'periodic';
3625 INSERT INTO "direct_interest_snapshot"
3626 ("issue_id", "event", "member_id")
3627 SELECT
3628 "issue_id_p" AS "issue_id",
3629 'periodic' AS "event",
3630 "member"."id" AS "member_id"
3631 FROM "issue"
3632 JOIN "area" ON "issue"."area_id" = "area"."id"
3633 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3634 JOIN "member" ON "interest"."member_id" = "member"."id"
3635 JOIN "privilege"
3636 ON "privilege"."unit_id" = "area"."unit_id"
3637 AND "privilege"."member_id" = "member"."id"
3638 WHERE "issue"."id" = "issue_id_p"
3639 AND "member"."active" AND "privilege"."voting_right";
3640 FOR "member_id_v" IN
3641 SELECT "member_id" FROM "direct_interest_snapshot"
3642 WHERE "issue_id" = "issue_id_p"
3643 AND "event" = 'periodic'
3644 LOOP
3645 UPDATE "direct_interest_snapshot" SET
3646 "weight" = 1 +
3647 "weight_of_added_delegations_for_interest_snapshot"(
3648 "issue_id_p",
3649 "member_id_v",
3650 '{}'
3651 )
3652 WHERE "issue_id" = "issue_id_p"
3653 AND "event" = 'periodic'
3654 AND "member_id" = "member_id_v";
3655 END LOOP;
3656 INSERT INTO "direct_supporter_snapshot"
3657 ( "issue_id", "initiative_id", "event", "member_id",
3658 "draft_id", "informed", "satisfied" )
3659 SELECT
3660 "issue_id_p" AS "issue_id",
3661 "initiative"."id" AS "initiative_id",
3662 'periodic' AS "event",
3663 "supporter"."member_id" AS "member_id",
3664 "supporter"."draft_id" AS "draft_id",
3665 "supporter"."draft_id" = "current_draft"."id" AS "informed",
3666 NOT EXISTS (
3667 SELECT NULL FROM "critical_opinion"
3668 WHERE "initiative_id" = "initiative"."id"
3669 AND "member_id" = "supporter"."member_id"
3670 ) AS "satisfied"
3671 FROM "initiative"
3672 JOIN "supporter"
3673 ON "supporter"."initiative_id" = "initiative"."id"
3674 JOIN "current_draft"
3675 ON "initiative"."id" = "current_draft"."initiative_id"
3676 JOIN "direct_interest_snapshot"
3677 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
3678 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
3679 AND "event" = 'periodic'
3680 WHERE "initiative"."issue_id" = "issue_id_p";
3681 RETURN;
3682 END;
3683 $$;
3685 COMMENT ON FUNCTION "create_interest_snapshot"
3686 ( "issue"."id"%TYPE )
3687 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.';
3690 CREATE FUNCTION "create_snapshot"
3691 ( "issue_id_p" "issue"."id"%TYPE )
3692 RETURNS VOID
3693 LANGUAGE 'plpgsql' VOLATILE AS $$
3694 DECLARE
3695 "initiative_id_v" "initiative"."id"%TYPE;
3696 "suggestion_id_v" "suggestion"."id"%TYPE;
3697 BEGIN
3698 PERFORM "require_transaction_isolation"();
3699 PERFORM "create_population_snapshot"("issue_id_p");
3700 PERFORM "create_interest_snapshot"("issue_id_p");
3701 UPDATE "issue" SET
3702 "snapshot" = coalesce("phase_finished", now()),
3703 "latest_snapshot_event" = 'periodic',
3704 "population" = (
3705 SELECT coalesce(sum("weight"), 0)
3706 FROM "direct_population_snapshot"
3707 WHERE "issue_id" = "issue_id_p"
3708 AND "event" = 'periodic'
3709 )
3710 WHERE "id" = "issue_id_p";
3711 FOR "initiative_id_v" IN
3712 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
3713 LOOP
3714 UPDATE "initiative" SET
3715 "supporter_count" = (
3716 SELECT coalesce(sum("di"."weight"), 0)
3717 FROM "direct_interest_snapshot" AS "di"
3718 JOIN "direct_supporter_snapshot" AS "ds"
3719 ON "di"."member_id" = "ds"."member_id"
3720 WHERE "di"."issue_id" = "issue_id_p"
3721 AND "di"."event" = 'periodic'
3722 AND "ds"."initiative_id" = "initiative_id_v"
3723 AND "ds"."event" = 'periodic'
3724 ),
3725 "informed_supporter_count" = (
3726 SELECT coalesce(sum("di"."weight"), 0)
3727 FROM "direct_interest_snapshot" AS "di"
3728 JOIN "direct_supporter_snapshot" AS "ds"
3729 ON "di"."member_id" = "ds"."member_id"
3730 WHERE "di"."issue_id" = "issue_id_p"
3731 AND "di"."event" = 'periodic'
3732 AND "ds"."initiative_id" = "initiative_id_v"
3733 AND "ds"."event" = 'periodic'
3734 AND "ds"."informed"
3735 ),
3736 "satisfied_supporter_count" = (
3737 SELECT coalesce(sum("di"."weight"), 0)
3738 FROM "direct_interest_snapshot" AS "di"
3739 JOIN "direct_supporter_snapshot" AS "ds"
3740 ON "di"."member_id" = "ds"."member_id"
3741 WHERE "di"."issue_id" = "issue_id_p"
3742 AND "di"."event" = 'periodic'
3743 AND "ds"."initiative_id" = "initiative_id_v"
3744 AND "ds"."event" = 'periodic'
3745 AND "ds"."satisfied"
3746 ),
3747 "satisfied_informed_supporter_count" = (
3748 SELECT coalesce(sum("di"."weight"), 0)
3749 FROM "direct_interest_snapshot" AS "di"
3750 JOIN "direct_supporter_snapshot" AS "ds"
3751 ON "di"."member_id" = "ds"."member_id"
3752 WHERE "di"."issue_id" = "issue_id_p"
3753 AND "di"."event" = 'periodic'
3754 AND "ds"."initiative_id" = "initiative_id_v"
3755 AND "ds"."event" = 'periodic'
3756 AND "ds"."informed"
3757 AND "ds"."satisfied"
3758 )
3759 WHERE "id" = "initiative_id_v";
3760 FOR "suggestion_id_v" IN
3761 SELECT "id" FROM "suggestion"
3762 WHERE "initiative_id" = "initiative_id_v"
3763 LOOP
3764 UPDATE "suggestion" SET
3765 "minus2_unfulfilled_count" = (
3766 SELECT coalesce(sum("snapshot"."weight"), 0)
3767 FROM "issue" CROSS JOIN "opinion"
3768 JOIN "direct_interest_snapshot" AS "snapshot"
3769 ON "snapshot"."issue_id" = "issue"."id"
3770 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3771 AND "snapshot"."member_id" = "opinion"."member_id"
3772 WHERE "issue"."id" = "issue_id_p"
3773 AND "opinion"."suggestion_id" = "suggestion_id_v"
3774 AND "opinion"."degree" = -2
3775 AND "opinion"."fulfilled" = FALSE
3776 ),
3777 "minus2_fulfilled_count" = (
3778 SELECT coalesce(sum("snapshot"."weight"), 0)
3779 FROM "issue" CROSS JOIN "opinion"
3780 JOIN "direct_interest_snapshot" AS "snapshot"
3781 ON "snapshot"."issue_id" = "issue"."id"
3782 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3783 AND "snapshot"."member_id" = "opinion"."member_id"
3784 WHERE "issue"."id" = "issue_id_p"
3785 AND "opinion"."suggestion_id" = "suggestion_id_v"
3786 AND "opinion"."degree" = -2
3787 AND "opinion"."fulfilled" = TRUE
3788 ),
3789 "minus1_unfulfilled_count" = (
3790 SELECT coalesce(sum("snapshot"."weight"), 0)
3791 FROM "issue" CROSS JOIN "opinion"
3792 JOIN "direct_interest_snapshot" AS "snapshot"
3793 ON "snapshot"."issue_id" = "issue"."id"
3794 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3795 AND "snapshot"."member_id" = "opinion"."member_id"
3796 WHERE "issue"."id" = "issue_id_p"
3797 AND "opinion"."suggestion_id" = "suggestion_id_v"
3798 AND "opinion"."degree" = -1
3799 AND "opinion"."fulfilled" = FALSE
3800 ),
3801 "minus1_fulfilled_count" = (
3802 SELECT coalesce(sum("snapshot"."weight"), 0)
3803 FROM "issue" CROSS JOIN "opinion"
3804 JOIN "direct_interest_snapshot" AS "snapshot"
3805 ON "snapshot"."issue_id" = "issue"."id"
3806 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3807 AND "snapshot"."member_id" = "opinion"."member_id"
3808 WHERE "issue"."id" = "issue_id_p"
3809 AND "opinion"."suggestion_id" = "suggestion_id_v"
3810 AND "opinion"."degree" = -1
3811 AND "opinion"."fulfilled" = TRUE
3812 ),
3813 "plus1_unfulfilled_count" = (
3814 SELECT coalesce(sum("snapshot"."weight"), 0)
3815 FROM "issue" CROSS JOIN "opinion"
3816 JOIN "direct_interest_snapshot" AS "snapshot"
3817 ON "snapshot"."issue_id" = "issue"."id"
3818 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3819 AND "snapshot"."member_id" = "opinion"."member_id"
3820 WHERE "issue"."id" = "issue_id_p"
3821 AND "opinion"."suggestion_id" = "suggestion_id_v"
3822 AND "opinion"."degree" = 1
3823 AND "opinion"."fulfilled" = FALSE
3824 ),
3825 "plus1_fulfilled_count" = (
3826 SELECT coalesce(sum("snapshot"."weight"), 0)
3827 FROM "issue" CROSS JOIN "opinion"
3828 JOIN "direct_interest_snapshot" AS "snapshot"
3829 ON "snapshot"."issue_id" = "issue"."id"
3830 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3831 AND "snapshot"."member_id" = "opinion"."member_id"
3832 WHERE "issue"."id" = "issue_id_p"
3833 AND "opinion"."suggestion_id" = "suggestion_id_v"
3834 AND "opinion"."degree" = 1
3835 AND "opinion"."fulfilled" = TRUE
3836 ),
3837 "plus2_unfulfilled_count" = (
3838 SELECT coalesce(sum("snapshot"."weight"), 0)
3839 FROM "issue" CROSS JOIN "opinion"
3840 JOIN "direct_interest_snapshot" AS "snapshot"
3841 ON "snapshot"."issue_id" = "issue"."id"
3842 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3843 AND "snapshot"."member_id" = "opinion"."member_id"
3844 WHERE "issue"."id" = "issue_id_p"
3845 AND "opinion"."suggestion_id" = "suggestion_id_v"
3846 AND "opinion"."degree" = 2
3847 AND "opinion"."fulfilled" = FALSE
3848 ),
3849 "plus2_fulfilled_count" = (
3850 SELECT coalesce(sum("snapshot"."weight"), 0)
3851 FROM "issue" CROSS JOIN "opinion"
3852 JOIN "direct_interest_snapshot" AS "snapshot"
3853 ON "snapshot"."issue_id" = "issue"."id"
3854 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3855 AND "snapshot"."member_id" = "opinion"."member_id"
3856 WHERE "issue"."id" = "issue_id_p"
3857 AND "opinion"."suggestion_id" = "suggestion_id_v"
3858 AND "opinion"."degree" = 2
3859 AND "opinion"."fulfilled" = TRUE
3860 )
3861 WHERE "suggestion"."id" = "suggestion_id_v";
3862 END LOOP;
3863 END LOOP;
3864 RETURN;
3865 END;
3866 $$;
3868 COMMENT ON FUNCTION "create_snapshot"
3869 ( "issue"."id"%TYPE )
3870 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.';
3873 CREATE FUNCTION "set_snapshot_event"
3874 ( "issue_id_p" "issue"."id"%TYPE,
3875 "event_p" "snapshot_event" )
3876 RETURNS VOID
3877 LANGUAGE 'plpgsql' VOLATILE AS $$
3878 DECLARE
3879 "event_v" "issue"."latest_snapshot_event"%TYPE;
3880 BEGIN
3881 PERFORM "require_transaction_isolation"();
3882 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
3883 WHERE "id" = "issue_id_p" FOR UPDATE;
3884 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
3885 WHERE "id" = "issue_id_p";
3886 UPDATE "direct_population_snapshot" SET "event" = "event_p"
3887 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3888 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
3889 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3890 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
3891 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3892 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
3893 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3894 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
3895 FROM "initiative" -- NOTE: due to missing index on issue_id
3896 WHERE "initiative"."issue_id" = "issue_id_p"
3897 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
3898 AND "direct_supporter_snapshot"."event" = "event_v";
3899 RETURN;
3900 END;
3901 $$;
3903 COMMENT ON FUNCTION "set_snapshot_event"
3904 ( "issue"."id"%TYPE,
3905 "snapshot_event" )
3906 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
3910 -----------------------
3911 -- Counting of votes --
3912 -----------------------
3915 CREATE FUNCTION "weight_of_added_vote_delegations"
3916 ( "issue_id_p" "issue"."id"%TYPE,
3917 "member_id_p" "member"."id"%TYPE,
3918 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
3919 RETURNS "direct_voter"."weight"%TYPE
3920 LANGUAGE 'plpgsql' VOLATILE AS $$
3921 DECLARE
3922 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3923 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
3924 "weight_v" INT4;
3925 "sub_weight_v" INT4;
3926 BEGIN
3927 PERFORM "require_transaction_isolation"();
3928 "weight_v" := 0;
3929 FOR "issue_delegation_row" IN
3930 SELECT * FROM "issue_delegation"
3931 WHERE "trustee_id" = "member_id_p"
3932 AND "issue_id" = "issue_id_p"
3933 LOOP
3934 IF NOT EXISTS (
3935 SELECT NULL FROM "direct_voter"
3936 WHERE "member_id" = "issue_delegation_row"."truster_id"
3937 AND "issue_id" = "issue_id_p"
3938 ) AND NOT EXISTS (
3939 SELECT NULL FROM "delegating_voter"
3940 WHERE "member_id" = "issue_delegation_row"."truster_id"
3941 AND "issue_id" = "issue_id_p"
3942 ) THEN
3943 "delegate_member_ids_v" :=
3944 "member_id_p" || "delegate_member_ids_p";
3945 INSERT INTO "delegating_voter" (
3946 "issue_id",
3947 "member_id",
3948 "scope",
3949 "delegate_member_ids"
3950 ) VALUES (
3951 "issue_id_p",
3952 "issue_delegation_row"."truster_id",
3953 "issue_delegation_row"."scope",
3954 "delegate_member_ids_v"
3955 );
3956 "sub_weight_v" := 1 +
3957 "weight_of_added_vote_delegations"(
3958 "issue_id_p",
3959 "issue_delegation_row"."truster_id",
3960 "delegate_member_ids_v"
3961 );
3962 UPDATE "delegating_voter"
3963 SET "weight" = "sub_weight_v"
3964 WHERE "issue_id" = "issue_id_p"
3965 AND "member_id" = "issue_delegation_row"."truster_id";
3966 "weight_v" := "weight_v" + "sub_weight_v";
3967 END IF;
3968 END LOOP;
3969 RETURN "weight_v";
3970 END;
3971 $$;
3973 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
3974 ( "issue"."id"%TYPE,
3975 "member"."id"%TYPE,
3976 "delegating_voter"."delegate_member_ids"%TYPE )
3977 IS 'Helper function for "add_vote_delegations" function';
3980 CREATE FUNCTION "add_vote_delegations"
3981 ( "issue_id_p" "issue"."id"%TYPE )
3982 RETURNS VOID
3983 LANGUAGE 'plpgsql' VOLATILE AS $$
3984 DECLARE
3985 "member_id_v" "member"."id"%TYPE;
3986 BEGIN
3987 PERFORM "require_transaction_isolation"();
3988 FOR "member_id_v" IN
3989 SELECT "member_id" FROM "direct_voter"
3990 WHERE "issue_id" = "issue_id_p"
3991 LOOP
3992 UPDATE "direct_voter" SET
3993 "weight" = "weight" + "weight_of_added_vote_delegations"(
3994 "issue_id_p",
3995 "member_id_v",
3996 '{}'
3997 )
3998 WHERE "member_id" = "member_id_v"
3999 AND "issue_id" = "issue_id_p";
4000 END LOOP;
4001 RETURN;
4002 END;
4003 $$;
4005 COMMENT ON FUNCTION "add_vote_delegations"
4006 ( "issue_id_p" "issue"."id"%TYPE )
4007 IS 'Helper function for "close_voting" function';
4010 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
4011 RETURNS VOID
4012 LANGUAGE 'plpgsql' VOLATILE AS $$
4013 DECLARE
4014 "area_id_v" "area"."id"%TYPE;
4015 "unit_id_v" "unit"."id"%TYPE;
4016 "member_id_v" "member"."id"%TYPE;
4017 BEGIN
4018 PERFORM "require_transaction_isolation"();
4019 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
4020 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
4021 -- override protection triggers:
4022 INSERT INTO "temporary_transaction_data" ("key", "value")
4023 VALUES ('override_protection_triggers', TRUE::TEXT);
4024 -- delete timestamp of voting comment:
4025 UPDATE "direct_voter" SET "comment_changed" = NULL
4026 WHERE "issue_id" = "issue_id_p";
4027 -- delete delegating votes (in cases of manual reset of issue state):
4028 DELETE FROM "delegating_voter"
4029 WHERE "issue_id" = "issue_id_p";
4030 -- delete votes from non-privileged voters:
4031 DELETE FROM "direct_voter"
4032 USING (
4033 SELECT
4034 "direct_voter"."member_id"
4035 FROM "direct_voter"
4036 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
4037 LEFT JOIN "privilege"
4038 ON "privilege"."unit_id" = "unit_id_v"
4039 AND "privilege"."member_id" = "direct_voter"."member_id"
4040 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
4041 "member"."active" = FALSE OR
4042 "privilege"."voting_right" ISNULL OR
4043 "privilege"."voting_right" = FALSE
4044 )
4045 ) AS "subquery"
4046 WHERE "direct_voter"."issue_id" = "issue_id_p"
4047 AND "direct_voter"."member_id" = "subquery"."member_id";
4048 -- consider delegations:
4049 UPDATE "direct_voter" SET "weight" = 1
4050 WHERE "issue_id" = "issue_id_p";
4051 PERFORM "add_vote_delegations"("issue_id_p");
4052 -- mark first preferences:
4053 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
4054 FROM (
4055 SELECT
4056 "vote"."initiative_id",
4057 "vote"."member_id",
4058 CASE WHEN "vote"."grade" > 0 THEN
4059 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
4060 ELSE NULL
4061 END AS "first_preference"
4062 FROM "vote"
4063 JOIN "initiative" -- NOTE: due to missing index on issue_id
4064 ON "vote"."issue_id" = "initiative"."issue_id"
4065 JOIN "vote" AS "agg"
4066 ON "initiative"."id" = "agg"."initiative_id"
4067 AND "vote"."member_id" = "agg"."member_id"
4068 GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
4069 ) AS "subquery"
4070 WHERE "vote"."issue_id" = "issue_id_p"
4071 AND "vote"."initiative_id" = "subquery"."initiative_id"
4072 AND "vote"."member_id" = "subquery"."member_id";
4073 -- finish overriding protection triggers (avoids garbage):
4074 DELETE FROM "temporary_transaction_data"
4075 WHERE "key" = 'override_protection_triggers';
4076 -- materialize battle_view:
4077 -- NOTE: "closed" column of issue must be set at this point
4078 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
4079 INSERT INTO "battle" (
4080 "issue_id",
4081 "winning_initiative_id", "losing_initiative_id",
4082 "count"
4083 ) SELECT
4084 "issue_id",
4085 "winning_initiative_id", "losing_initiative_id",
4086 "count"
4087 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
4088 -- set voter count:
4089 UPDATE "issue" SET
4090 "voter_count" = (
4091 SELECT coalesce(sum("weight"), 0)
4092 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
4093 )
4094 WHERE "id" = "issue_id_p";
4095 -- copy "positive_votes" and "negative_votes" from "battle" table:
4096 -- NOTE: "first_preference_votes" is set to a default of 0 at this step
4097 UPDATE "initiative" SET
4098 "first_preference_votes" = 0,
4099 "positive_votes" = "battle_win"."count",
4100 "negative_votes" = "battle_lose"."count"
4101 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
4102 WHERE
4103 "battle_win"."issue_id" = "issue_id_p" AND
4104 "battle_win"."winning_initiative_id" = "initiative"."id" AND
4105 "battle_win"."losing_initiative_id" ISNULL AND
4106 "battle_lose"."issue_id" = "issue_id_p" AND
4107 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
4108 "battle_lose"."winning_initiative_id" ISNULL;
4109 -- calculate "first_preference_votes":
4110 -- NOTE: will only set values not equal to zero
4111 UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum"
4112 FROM (
4113 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
4114 FROM "vote" JOIN "direct_voter"
4115 ON "vote"."issue_id" = "direct_voter"."issue_id"
4116 AND "vote"."member_id" = "direct_voter"."member_id"
4117 WHERE "vote"."first_preference"
4118 GROUP BY "vote"."initiative_id"
4119 ) AS "subquery"
4120 WHERE "initiative"."issue_id" = "issue_id_p"
4121 AND "initiative"."admitted"
4122 AND "initiative"."id" = "subquery"."initiative_id";
4123 END;
4124 $$;
4126 COMMENT ON FUNCTION "close_voting"
4127 ( "issue"."id"%TYPE )
4128 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.';
4131 CREATE FUNCTION "defeat_strength"
4132 ( "positive_votes_p" INT4,
4133 "negative_votes_p" INT4,
4134 "defeat_strength_p" "defeat_strength" )
4135 RETURNS INT8
4136 LANGUAGE 'plpgsql' IMMUTABLE AS $$
4137 BEGIN
4138 IF "defeat_strength_p" = 'simple'::"defeat_strength" THEN
4139 IF "positive_votes_p" > "negative_votes_p" THEN
4140 RETURN "positive_votes_p";
4141 ELSE
4142 RETURN 0;
4143 END IF;
4144 ELSE
4145 IF "positive_votes_p" > "negative_votes_p" THEN
4146 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
4147 ELSIF "positive_votes_p" = "negative_votes_p" THEN
4148 RETURN 0;
4149 ELSE
4150 RETURN -1;
4151 END IF;
4152 END IF;
4153 END;
4154 $$;
4156 COMMENT ON FUNCTION "defeat_strength"(INT4, INT4, "defeat_strength") IS 'Calculates defeat strength (INT8!) according to the "defeat_strength" option (see comment on type "defeat_strength")';
4159 CREATE FUNCTION "secondary_link_strength"
4160 ( "initiative1_ord_p" INT4,
4161 "initiative2_ord_p" INT4,
4162 "tie_breaking_p" "tie_breaking" )
4163 RETURNS INT8
4164 LANGUAGE 'plpgsql' IMMUTABLE AS $$
4165 BEGIN
4166 IF "initiative1_ord_p" = "initiative2_ord_p" THEN
4167 RAISE EXCEPTION 'Identical initiative ids passed to "secondary_link_strength" function (should not happen)';
4168 END IF;
4169 RETURN (
4170 CASE WHEN "tie_breaking_p" = 'simple'::"tie_breaking" THEN
4171 0
4172 ELSE
4173 CASE WHEN "initiative1_ord_p" < "initiative2_ord_p" THEN
4174 1::INT8 << 62
4175 ELSE 0 END
4176 +
4177 CASE WHEN "tie_breaking_p" = 'variant2'::"tie_breaking" THEN
4178 ("initiative2_ord_p"::INT8 << 31) - "initiative1_ord_p"::INT8
4179 ELSE
4180 "initiative2_ord_p"::INT8 - ("initiative1_ord_p"::INT8 << 31)
4181 END
4182 END
4183 );
4184 END;
4185 $$;
4187 COMMENT ON FUNCTION "secondary_link_strength"(INT4, INT4, "tie_breaking") IS 'Calculates a secondary criterion for the defeat strength (tie-breaking of the links)';
4190 CREATE TYPE "link_strength" AS (
4191 "primary" INT8,
4192 "secondary" INT8 );
4194 COMMENT ON TYPE "link_strength" IS 'Type to store the defeat strength of a link between two candidates plus a secondary criterion to create unique link strengths between the candidates (needed for tie-breaking ''variant1'' and ''variant2'')';
4197 CREATE FUNCTION "find_best_paths"("matrix_d" "link_strength"[][])
4198 RETURNS "link_strength"[][]
4199 LANGUAGE 'plpgsql' IMMUTABLE AS $$
4200 DECLARE
4201 "dimension_v" INT4;
4202 "matrix_p" "link_strength"[][];
4203 "i" INT4;
4204 "j" INT4;
4205 "k" INT4;
4206 BEGIN
4207 "dimension_v" := array_upper("matrix_d", 1);
4208 "matrix_p" := "matrix_d";
4209 "i" := 1;
4210 LOOP
4211 "j" := 1;
4212 LOOP
4213 IF "i" != "j" THEN
4214 "k" := 1;
4215 LOOP
4216 IF "i" != "k" AND "j" != "k" THEN
4217 IF "matrix_p"["j"]["i"] < "matrix_p"["i"]["k"] THEN
4218 IF "matrix_p"["j"]["i"] > "matrix_p"["j"]["k"] THEN
4219 "matrix_p"["j"]["k"] := "matrix_p"["j"]["i"];
4220 END IF;
4221 ELSE
4222 IF "matrix_p"["i"]["k"] > "matrix_p"["j"]["k"] THEN
4223 "matrix_p"["j"]["k"] := "matrix_p"["i"]["k"];
4224 END IF;
4225 END IF;
4226 END IF;
4227 EXIT WHEN "k" = "dimension_v";
4228 "k" := "k" + 1;
4229 END LOOP;
4230 END IF;
4231 EXIT WHEN "j" = "dimension_v";
4232 "j" := "j" + 1;
4233 END LOOP;
4234 EXIT WHEN "i" = "dimension_v";
4235 "i" := "i" + 1;
4236 END LOOP;
4237 RETURN "matrix_p";
4238 END;
4239 $$;
4241 COMMENT ON FUNCTION "find_best_paths"("link_strength"[][]) IS 'Computes the strengths of the best beat-paths from a square matrix';
4244 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
4245 RETURNS VOID
4246 LANGUAGE 'plpgsql' VOLATILE AS $$
4247 DECLARE
4248 "issue_row" "issue"%ROWTYPE;
4249 "policy_row" "policy"%ROWTYPE;
4250 "dimension_v" INT4;
4251 "matrix_a" INT4[][]; -- absolute votes
4252 "matrix_d" "link_strength"[][]; -- defeat strength (direct)
4253 "matrix_p" "link_strength"[][]; -- defeat strength (best path)
4254 "matrix_t" "link_strength"[][]; -- defeat strength (tie-breaking)
4255 "matrix_f" BOOLEAN[][]; -- forbidden link (tie-breaking)
4256 "matrix_b" BOOLEAN[][]; -- final order (who beats who)
4257 "i" INT4;
4258 "j" INT4;
4259 "m" INT4;
4260 "n" INT4;
4261 "battle_row" "battle"%ROWTYPE;
4262 "rank_ary" INT4[];
4263 "rank_v" INT4;
4264 "initiative_id_v" "initiative"."id"%TYPE;
4265 BEGIN
4266 PERFORM "require_transaction_isolation"();
4267 SELECT * INTO "issue_row"
4268 FROM "issue" WHERE "id" = "issue_id_p";
4269 SELECT * INTO "policy_row"
4270 FROM "policy" WHERE "id" = "issue_row"."policy_id";
4271 SELECT count(1) INTO "dimension_v"
4272 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
4273 -- create "matrix_a" with absolute number of votes in pairwise
4274 -- comparison:
4275 "matrix_a" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
4276 "i" := 1;
4277 "j" := 2;
4278 FOR "battle_row" IN
4279 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
4280 ORDER BY
4281 "winning_initiative_id" NULLS FIRST,
4282 "losing_initiative_id" NULLS FIRST
4283 LOOP
4284 "matrix_a"["i"]["j"] := "battle_row"."count";
4285 IF "j" = "dimension_v" THEN
4286 "i" := "i" + 1;
4287 "j" := 1;
4288 ELSE
4289 "j" := "j" + 1;
4290 IF "j" = "i" THEN
4291 "j" := "j" + 1;
4292 END IF;
4293 END IF;
4294 END LOOP;
4295 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
4296 RAISE EXCEPTION 'Wrong battle count (should not happen)';
4297 END IF;
4298 -- store direct defeat strengths in "matrix_d" using "defeat_strength"
4299 -- and "secondary_link_strength" functions:
4300 "matrix_d" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
4301 "i" := 1;
4302 LOOP
4303 "j" := 1;
4304 LOOP
4305 IF "i" != "j" THEN
4306 "matrix_d"["i"]["j"] := (
4307 "defeat_strength"(
4308 "matrix_a"["i"]["j"],
4309 "matrix_a"["j"]["i"],
4310 "policy_row"."defeat_strength"
4311 ),
4312 "secondary_link_strength"(
4313 "i",
4314 "j",
4315 "policy_row"."tie_breaking"
4316 )
4317 )::"link_strength";
4318 END IF;
4319 EXIT WHEN "j" = "dimension_v";
4320 "j" := "j" + 1;
4321 END LOOP;
4322 EXIT WHEN "i" = "dimension_v";
4323 "i" := "i" + 1;
4324 END LOOP;
4325 -- find best paths:
4326 "matrix_p" := "find_best_paths"("matrix_d");
4327 -- create partial order:
4328 "matrix_b" := array_fill(NULL::BOOLEAN, ARRAY["dimension_v", "dimension_v"]);
4329 "i" := 1;
4330 LOOP
4331 "j" := "i" + 1;
4332 LOOP
4333 IF "i" != "j" THEN
4334 IF "matrix_p"["i"]["j"] > "matrix_p"["j"]["i"] THEN
4335 "matrix_b"["i"]["j"] := TRUE;
4336 "matrix_b"["j"]["i"] := FALSE;
4337 ELSIF "matrix_p"["i"]["j"] < "matrix_p"["j"]["i"] THEN
4338 "matrix_b"["i"]["j"] := FALSE;
4339 "matrix_b"["j"]["i"] := TRUE;
4340 END IF;
4341 END IF;
4342 EXIT WHEN "j" = "dimension_v";
4343 "j" := "j" + 1;
4344 END LOOP;
4345 EXIT WHEN "i" = "dimension_v" - 1;
4346 "i" := "i" + 1;
4347 END LOOP;
4348 -- tie-breaking by forbidding shared weakest links in beat-paths
4349 -- (unless "tie_breaking" is set to 'simple', in which case tie-breaking
4350 -- is performed later by initiative id):
4351 IF "policy_row"."tie_breaking" != 'simple'::"tie_breaking" THEN
4352 "m" := 1;
4353 LOOP
4354 "n" := "m" + 1;
4355 LOOP
4356 -- only process those candidates m and n, which are tied:
4357 IF "matrix_b"["m"]["n"] ISNULL THEN
4358 -- start with beat-paths prior tie-breaking:
4359 "matrix_t" := "matrix_p";
4360 -- start with all links allowed:
4361 "matrix_f" := array_fill(FALSE, ARRAY["dimension_v", "dimension_v"]);
4362 LOOP
4363 -- determine (and forbid) that link that is the weakest link
4364 -- in both the best path from candidate m to candidate n and
4365 -- from candidate n to candidate m:
4366 "i" := 1;
4367 <<forbid_one_link>>
4368 LOOP
4369 "j" := 1;
4370 LOOP
4371 IF "i" != "j" THEN
4372 IF "matrix_d"["i"]["j"] = "matrix_t"["m"]["n"] THEN
4373 "matrix_f"["i"]["j"] := TRUE;
4374 -- exit for performance reasons,
4375 -- as exactly one link will be found:
4376 EXIT forbid_one_link;
4377 END IF;
4378 END IF;
4379 EXIT WHEN "j" = "dimension_v";
4380 "j" := "j" + 1;
4381 END LOOP;
4382 IF "i" = "dimension_v" THEN
4383 RAISE EXCEPTION 'Did not find shared weakest link for tie-breaking (should not happen)';
4384 END IF;
4385 "i" := "i" + 1;
4386 END LOOP;
4387 -- calculate best beat-paths while ignoring forbidden links:
4388 "i" := 1;
4389 LOOP
4390 "j" := 1;
4391 LOOP
4392 IF "i" != "j" THEN
4393 "matrix_t"["i"]["j"] := CASE
4394 WHEN "matrix_f"["i"]["j"]
4395 THEN ((-1::INT8) << 63, 0)::"link_strength" -- worst possible value
4396 ELSE "matrix_d"["i"]["j"] END;
4397 END IF;
4398 EXIT WHEN "j" = "dimension_v";
4399 "j" := "j" + 1;
4400 END LOOP;
4401 EXIT WHEN "i" = "dimension_v";
4402 "i" := "i" + 1;
4403 END LOOP;
4404 "matrix_t" := "find_best_paths"("matrix_t");
4405 -- extend partial order, if tie-breaking was successful:
4406 IF "matrix_t"["m"]["n"] > "matrix_t"["n"]["m"] THEN
4407 "matrix_b"["m"]["n"] := TRUE;
4408 "matrix_b"["n"]["m"] := FALSE;
4409 EXIT;
4410 ELSIF "matrix_t"["m"]["n"] < "matrix_t"["n"]["m"] THEN
4411 "matrix_b"["m"]["n"] := FALSE;
4412 "matrix_b"["n"]["m"] := TRUE;
4413 EXIT;
4414 END IF;
4415 END LOOP;
4416 END IF;
4417 EXIT WHEN "n" = "dimension_v";
4418 "n" := "n" + 1;
4419 END LOOP;
4420 EXIT WHEN "m" = "dimension_v" - 1;
4421 "m" := "m" + 1;
4422 END LOOP;
4423 END IF;
4424 -- store a unique ranking in "rank_ary":
4425 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
4426 "rank_v" := 1;
4427 LOOP
4428 "i" := 1;
4429 <<assign_next_rank>>
4430 LOOP
4431 IF "rank_ary"["i"] ISNULL THEN
4432 "j" := 1;
4433 LOOP
4434 IF
4435 "i" != "j" AND
4436 "rank_ary"["j"] ISNULL AND
4437 ( "matrix_b"["j"]["i"] OR
4438 -- tie-breaking by "id"
4439 ( "matrix_b"["j"]["i"] ISNULL AND
4440 "j" < "i" ) )
4441 THEN
4442 -- someone else is better
4443 EXIT;
4444 END IF;
4445 IF "j" = "dimension_v" THEN
4446 -- noone is better
4447 "rank_ary"["i"] := "rank_v";
4448 EXIT assign_next_rank;
4449 END IF;
4450 "j" := "j" + 1;
4451 END LOOP;
4452 END IF;
4453 "i" := "i" + 1;
4454 IF "i" > "dimension_v" THEN
4455 RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
4456 END IF;
4457 END LOOP;
4458 EXIT WHEN "rank_v" = "dimension_v";
4459 "rank_v" := "rank_v" + 1;
4460 END LOOP;
4461 -- write preliminary results:
4462 "i" := 2; -- omit status quo with "i" = 1
4463 FOR "initiative_id_v" IN
4464 SELECT "id" FROM "initiative"
4465 WHERE "issue_id" = "issue_id_p" AND "admitted"
4466 ORDER BY "id"
4467 LOOP
4468 UPDATE "initiative" SET
4469 "direct_majority" =
4470 CASE WHEN "policy_row"."direct_majority_strict" THEN
4471 "positive_votes" * "policy_row"."direct_majority_den" >
4472 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
4473 ELSE
4474 "positive_votes" * "policy_row"."direct_majority_den" >=
4475 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
4476 END
4477 AND "positive_votes" >= "policy_row"."direct_majority_positive"
4478 AND "issue_row"."voter_count"-"negative_votes" >=
4479 "policy_row"."direct_majority_non_negative",
4480 "indirect_majority" =
4481 CASE WHEN "policy_row"."indirect_majority_strict" THEN
4482 "positive_votes" * "policy_row"."indirect_majority_den" >
4483 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4484 ELSE
4485 "positive_votes" * "policy_row"."indirect_majority_den" >=
4486 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4487 END
4488 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
4489 AND "issue_row"."voter_count"-"negative_votes" >=
4490 "policy_row"."indirect_majority_non_negative",
4491 "schulze_rank" = "rank_ary"["i"],
4492 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
4493 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"[1],
4494 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"[1],
4495 "reverse_beat_path" = CASE WHEN "policy_row"."defeat_strength" = 'simple'::"defeat_strength"
4496 THEN NULL
4497 ELSE "matrix_p"[1]["i"]."primary" >= 0 END,
4498 "eligible" = FALSE,
4499 "winner" = FALSE,
4500 "rank" = NULL -- NOTE: in cases of manual reset of issue state
4501 WHERE "id" = "initiative_id_v";
4502 "i" := "i" + 1;
4503 END LOOP;
4504 IF "i" != "dimension_v" + 1 THEN
4505 RAISE EXCEPTION 'Wrong winner count (should not happen)';
4506 END IF;
4507 -- take indirect majorities into account:
4508 LOOP
4509 UPDATE "initiative" SET "indirect_majority" = TRUE
4510 FROM (
4511 SELECT "new_initiative"."id" AS "initiative_id"
4512 FROM "initiative" "old_initiative"
4513 JOIN "initiative" "new_initiative"
4514 ON "new_initiative"."issue_id" = "issue_id_p"
4515 AND "new_initiative"."indirect_majority" = FALSE
4516 JOIN "battle" "battle_win"
4517 ON "battle_win"."issue_id" = "issue_id_p"
4518 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
4519 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
4520 JOIN "battle" "battle_lose"
4521 ON "battle_lose"."issue_id" = "issue_id_p"
4522 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
4523 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
4524 WHERE "old_initiative"."issue_id" = "issue_id_p"
4525 AND "old_initiative"."indirect_majority" = TRUE
4526 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
4527 "battle_win"."count" * "policy_row"."indirect_majority_den" >
4528 "policy_row"."indirect_majority_num" *
4529 ("battle_win"."count"+"battle_lose"."count")
4530 ELSE
4531 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
4532 "policy_row"."indirect_majority_num" *
4533 ("battle_win"."count"+"battle_lose"."count")
4534 END
4535 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
4536 AND "issue_row"."voter_count"-"battle_lose"."count" >=
4537 "policy_row"."indirect_majority_non_negative"
4538 ) AS "subquery"
4539 WHERE "id" = "subquery"."initiative_id";
4540 EXIT WHEN NOT FOUND;
4541 END LOOP;
4542 -- set "multistage_majority" for remaining matching initiatives:
4543 UPDATE "initiative" SET "multistage_majority" = TRUE
4544 FROM (
4545 SELECT "losing_initiative"."id" AS "initiative_id"
4546 FROM "initiative" "losing_initiative"
4547 JOIN "initiative" "winning_initiative"
4548 ON "winning_initiative"."issue_id" = "issue_id_p"
4549 AND "winning_initiative"."admitted"
4550 JOIN "battle" "battle_win"
4551 ON "battle_win"."issue_id" = "issue_id_p"
4552 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
4553 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
4554 JOIN "battle" "battle_lose"
4555 ON "battle_lose"."issue_id" = "issue_id_p"
4556 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
4557 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
4558 WHERE "losing_initiative"."issue_id" = "issue_id_p"
4559 AND "losing_initiative"."admitted"
4560 AND "winning_initiative"."schulze_rank" <
4561 "losing_initiative"."schulze_rank"
4562 AND "battle_win"."count" > "battle_lose"."count"
4563 AND (
4564 "battle_win"."count" > "winning_initiative"."positive_votes" OR
4565 "battle_lose"."count" < "losing_initiative"."negative_votes" )
4566 ) AS "subquery"
4567 WHERE "id" = "subquery"."initiative_id";
4568 -- mark eligible initiatives:
4569 UPDATE "initiative" SET "eligible" = TRUE
4570 WHERE "issue_id" = "issue_id_p"
4571 AND "initiative"."direct_majority"
4572 AND "initiative"."indirect_majority"
4573 AND "initiative"."better_than_status_quo"
4574 AND (
4575 "policy_row"."no_multistage_majority" = FALSE OR
4576 "initiative"."multistage_majority" = FALSE )
4577 AND (
4578 "policy_row"."no_reverse_beat_path" = FALSE OR
4579 coalesce("initiative"."reverse_beat_path", FALSE) = FALSE );
4580 -- mark final winner:
4581 UPDATE "initiative" SET "winner" = TRUE
4582 FROM (
4583 SELECT "id" AS "initiative_id"
4584 FROM "initiative"
4585 WHERE "issue_id" = "issue_id_p" AND "eligible"
4586 ORDER BY
4587 "schulze_rank",
4588 "id"
4589 LIMIT 1
4590 ) AS "subquery"
4591 WHERE "id" = "subquery"."initiative_id";
4592 -- write (final) ranks:
4593 "rank_v" := 1;
4594 FOR "initiative_id_v" IN
4595 SELECT "id"
4596 FROM "initiative"
4597 WHERE "issue_id" = "issue_id_p" AND "admitted"
4598 ORDER BY
4599 "winner" DESC,
4600 "eligible" DESC,
4601 "schulze_rank",
4602 "id"
4603 LOOP
4604 UPDATE "initiative" SET "rank" = "rank_v"
4605 WHERE "id" = "initiative_id_v";
4606 "rank_v" := "rank_v" + 1;
4607 END LOOP;
4608 -- set schulze rank of status quo and mark issue as finished:
4609 UPDATE "issue" SET
4610 "status_quo_schulze_rank" = "rank_ary"[1],
4611 "state" =
4612 CASE WHEN EXISTS (
4613 SELECT NULL FROM "initiative"
4614 WHERE "issue_id" = "issue_id_p" AND "winner"
4615 ) THEN
4616 'finished_with_winner'::"issue_state"
4617 ELSE
4618 'finished_without_winner'::"issue_state"
4619 END,
4620 "closed" = "phase_finished",
4621 "phase_finished" = NULL
4622 WHERE "id" = "issue_id_p";
4623 RETURN;
4624 END;
4625 $$;
4627 COMMENT ON FUNCTION "calculate_ranks"
4628 ( "issue"."id"%TYPE )
4629 IS 'Determine ranking (Votes have to be counted first)';
4633 -----------------------------
4634 -- Automatic state changes --
4635 -----------------------------
4638 CREATE TYPE "check_issue_persistence" AS (
4639 "state" "issue_state",
4640 "phase_finished" BOOLEAN,
4641 "issue_revoked" BOOLEAN,
4642 "snapshot_created" BOOLEAN,
4643 "harmonic_weights_set" BOOLEAN,
4644 "closed_voting" BOOLEAN );
4646 COMMENT ON TYPE "check_issue_persistence" IS 'Type of data returned by "check_issue" function, to be passed to subsequent calls of the same function';
4649 CREATE FUNCTION "check_issue"
4650 ( "issue_id_p" "issue"."id"%TYPE,
4651 "persist" "check_issue_persistence" )
4652 RETURNS "check_issue_persistence"
4653 LANGUAGE 'plpgsql' VOLATILE AS $$
4654 DECLARE
4655 "issue_row" "issue"%ROWTYPE;
4656 "policy_row" "policy"%ROWTYPE;
4657 "initiative_row" "initiative"%ROWTYPE;
4658 "state_v" "issue_state";
4659 BEGIN
4660 PERFORM "require_transaction_isolation"();
4661 IF "persist" ISNULL THEN
4662 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4663 FOR UPDATE;
4664 IF "issue_row"."closed" NOTNULL THEN
4665 RETURN NULL;
4666 END IF;
4667 "persist"."state" := "issue_row"."state";
4668 IF
4669 ( "issue_row"."state" = 'admission' AND now() >=
4670 "issue_row"."created" + "issue_row"."max_admission_time" ) OR
4671 ( "issue_row"."state" = 'discussion' AND now() >=
4672 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
4673 ( "issue_row"."state" = 'verification' AND now() >=
4674 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
4675 ( "issue_row"."state" = 'voting' AND now() >=
4676 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
4677 THEN
4678 "persist"."phase_finished" := TRUE;
4679 ELSE
4680 "persist"."phase_finished" := FALSE;
4681 END IF;
4682 IF
4683 NOT EXISTS (
4684 -- all initiatives are revoked
4685 SELECT NULL FROM "initiative"
4686 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4687 ) AND (
4688 -- and issue has not been accepted yet
4689 "persist"."state" = 'admission' OR
4690 -- or verification time has elapsed
4691 ( "persist"."state" = 'verification' AND
4692 "persist"."phase_finished" ) OR
4693 -- or no initiatives have been revoked lately
4694 NOT EXISTS (
4695 SELECT NULL FROM "initiative"
4696 WHERE "issue_id" = "issue_id_p"
4697 AND now() < "revoked" + "issue_row"."verification_time"
4698 )
4699 )
4700 THEN
4701 "persist"."issue_revoked" := TRUE;
4702 ELSE
4703 "persist"."issue_revoked" := FALSE;
4704 END IF;
4705 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
4706 UPDATE "issue" SET "phase_finished" = now()
4707 WHERE "id" = "issue_row"."id";
4708 RETURN "persist";
4709 ELSIF
4710 "persist"."state" IN ('admission', 'discussion', 'verification')
4711 THEN
4712 RETURN "persist";
4713 ELSE
4714 RETURN NULL;
4715 END IF;
4716 END IF;
4717 IF
4718 "persist"."state" IN ('admission', 'discussion', 'verification') AND
4719 coalesce("persist"."snapshot_created", FALSE) = FALSE
4720 THEN
4721 PERFORM "create_snapshot"("issue_id_p");
4722 "persist"."snapshot_created" = TRUE;
4723 IF "persist"."phase_finished" THEN
4724 IF "persist"."state" = 'admission' THEN
4725 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
4726 ELSIF "persist"."state" = 'discussion' THEN
4727 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
4728 ELSIF "persist"."state" = 'verification' THEN
4729 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
4730 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4731 SELECT * INTO "policy_row" FROM "policy"
4732 WHERE "id" = "issue_row"."policy_id";
4733 FOR "initiative_row" IN
4734 SELECT * FROM "initiative"
4735 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4736 FOR UPDATE
4737 LOOP
4738 IF
4739 "initiative_row"."polling" OR (
4740 "initiative_row"."satisfied_supporter_count" > 0 AND
4741 "initiative_row"."satisfied_supporter_count" *
4742 "policy_row"."initiative_quorum_den" >=
4743 "issue_row"."population" * "policy_row"."initiative_quorum_num"
4744 )
4745 THEN
4746 UPDATE "initiative" SET "admitted" = TRUE
4747 WHERE "id" = "initiative_row"."id";
4748 ELSE
4749 UPDATE "initiative" SET "admitted" = FALSE
4750 WHERE "id" = "initiative_row"."id";
4751 END IF;
4752 END LOOP;
4753 END IF;
4754 END IF;
4755 RETURN "persist";
4756 END IF;
4757 IF
4758 "persist"."state" IN ('admission', 'discussion', 'verification') AND
4759 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
4760 THEN
4761 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
4762 "persist"."harmonic_weights_set" = TRUE;
4763 IF
4764 "persist"."phase_finished" OR
4765 "persist"."issue_revoked" OR
4766 "persist"."state" = 'admission'
4767 THEN
4768 RETURN "persist";
4769 ELSE
4770 RETURN NULL;
4771 END IF;
4772 END IF;
4773 IF "persist"."issue_revoked" THEN
4774 IF "persist"."state" = 'admission' THEN
4775 "state_v" := 'canceled_revoked_before_accepted';
4776 ELSIF "persist"."state" = 'discussion' THEN
4777 "state_v" := 'canceled_after_revocation_during_discussion';
4778 ELSIF "persist"."state" = 'verification' THEN
4779 "state_v" := 'canceled_after_revocation_during_verification';
4780 END IF;
4781 UPDATE "issue" SET
4782 "state" = "state_v",
4783 "closed" = "phase_finished",
4784 "phase_finished" = NULL
4785 WHERE "id" = "issue_id_p";
4786 RETURN NULL;
4787 END IF;
4788 IF "persist"."state" = 'admission' THEN
4789 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4790 FOR UPDATE;
4791 SELECT * INTO "policy_row"
4792 FROM "policy" WHERE "id" = "issue_row"."policy_id";
4793 IF
4794 ( now() >=
4795 "issue_row"."created" + "issue_row"."min_admission_time" ) AND
4796 EXISTS (
4797 SELECT NULL FROM "initiative"
4798 WHERE "issue_id" = "issue_id_p"
4799 AND "supporter_count" > 0
4800 AND "supporter_count" * "policy_row"."issue_quorum_den"
4801 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
4802 )
4803 THEN
4804 UPDATE "issue" SET
4805 "state" = 'discussion',
4806 "accepted" = coalesce("phase_finished", now()),
4807 "phase_finished" = NULL
4808 WHERE "id" = "issue_id_p";
4809 ELSIF "issue_row"."phase_finished" NOTNULL THEN
4810 UPDATE "issue" SET
4811 "state" = 'canceled_issue_not_accepted',
4812 "closed" = "phase_finished",
4813 "phase_finished" = NULL
4814 WHERE "id" = "issue_id_p";
4815 END IF;
4816 RETURN NULL;
4817 END IF;
4818 IF "persist"."phase_finished" THEN
4819 IF "persist"."state" = 'discussion' THEN
4820 UPDATE "issue" SET
4821 "state" = 'verification',
4822 "half_frozen" = "phase_finished",
4823 "phase_finished" = NULL
4824 WHERE "id" = "issue_id_p";
4825 RETURN NULL;
4826 END IF;
4827 IF "persist"."state" = 'verification' THEN
4828 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4829 FOR UPDATE;
4830 SELECT * INTO "policy_row" FROM "policy"
4831 WHERE "id" = "issue_row"."policy_id";
4832 IF EXISTS (
4833 SELECT NULL FROM "initiative"
4834 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
4835 ) THEN
4836 UPDATE "issue" SET
4837 "state" = 'voting',
4838 "fully_frozen" = "phase_finished",
4839 "phase_finished" = NULL
4840 WHERE "id" = "issue_id_p";
4841 ELSE
4842 UPDATE "issue" SET
4843 "state" = 'canceled_no_initiative_admitted',
4844 "fully_frozen" = "phase_finished",
4845 "closed" = "phase_finished",
4846 "phase_finished" = NULL
4847 WHERE "id" = "issue_id_p";
4848 -- NOTE: The following DELETE statements have effect only when
4849 -- issue state has been manipulated
4850 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
4851 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
4852 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
4853 END IF;
4854 RETURN NULL;
4855 END IF;
4856 IF "persist"."state" = 'voting' THEN
4857 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
4858 PERFORM "close_voting"("issue_id_p");
4859 "persist"."closed_voting" = TRUE;
4860 RETURN "persist";
4861 END IF;
4862 PERFORM "calculate_ranks"("issue_id_p");
4863 RETURN NULL;
4864 END IF;
4865 END IF;
4866 RAISE WARNING 'should not happen';
4867 RETURN NULL;
4868 END;
4869 $$;
4871 COMMENT ON FUNCTION "check_issue"
4872 ( "issue"."id"%TYPE,
4873 "check_issue_persistence" )
4874 IS 'Precalculate supporter counts etc. for a given issue, and check, if status change is required, and perform the status change when necessary; Function must be called multiple times with the previous result as second parameter, until the result is NULL (see source code of function "check_everything")';
4877 CREATE FUNCTION "check_everything"()
4878 RETURNS VOID
4879 LANGUAGE 'plpgsql' VOLATILE AS $$
4880 DECLARE
4881 "issue_id_v" "issue"."id"%TYPE;
4882 "persist_v" "check_issue_persistence";
4883 BEGIN
4884 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
4885 DELETE FROM "expired_session";
4886 PERFORM "check_activity"();
4887 PERFORM "calculate_member_counts"();
4888 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
4889 "persist_v" := NULL;
4890 LOOP
4891 "persist_v" := "check_issue"("issue_id_v", "persist_v");
4892 EXIT WHEN "persist_v" ISNULL;
4893 END LOOP;
4894 END LOOP;
4895 RETURN;
4896 END;
4897 $$;
4899 COMMENT ON FUNCTION "check_everything"() IS 'Amongst other regular tasks this function performs "check_issue" for every open issue. Use this function only for development and debugging purposes, as you may run into locking and/or serialization problems in productive environments.';
4903 ----------------------
4904 -- Deletion of data --
4905 ----------------------
4908 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
4909 RETURNS VOID
4910 LANGUAGE 'plpgsql' VOLATILE AS $$
4911 BEGIN
4912 IF EXISTS (
4913 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
4914 ) THEN
4915 -- override protection triggers:
4916 INSERT INTO "temporary_transaction_data" ("key", "value")
4917 VALUES ('override_protection_triggers', TRUE::TEXT);
4918 -- clean data:
4919 DELETE FROM "delegating_voter"
4920 WHERE "issue_id" = "issue_id_p";
4921 DELETE FROM "direct_voter"
4922 WHERE "issue_id" = "issue_id_p";
4923 DELETE FROM "delegating_interest_snapshot"
4924 WHERE "issue_id" = "issue_id_p";
4925 DELETE FROM "direct_interest_snapshot"
4926 WHERE "issue_id" = "issue_id_p";
4927 DELETE FROM "delegating_population_snapshot"
4928 WHERE "issue_id" = "issue_id_p";
4929 DELETE FROM "direct_population_snapshot"
4930 WHERE "issue_id" = "issue_id_p";
4931 DELETE FROM "non_voter"
4932 WHERE "issue_id" = "issue_id_p";
4933 DELETE FROM "delegation"
4934 WHERE "issue_id" = "issue_id_p";
4935 DELETE FROM "supporter"
4936 USING "initiative" -- NOTE: due to missing index on issue_id
4937 WHERE "initiative"."issue_id" = "issue_id_p"
4938 AND "supporter"."initiative_id" = "initiative_id";
4939 -- mark issue as cleaned:
4940 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
4941 -- finish overriding protection triggers (avoids garbage):
4942 DELETE FROM "temporary_transaction_data"
4943 WHERE "key" = 'override_protection_triggers';
4944 END IF;
4945 RETURN;
4946 END;
4947 $$;
4949 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
4952 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
4953 RETURNS VOID
4954 LANGUAGE 'plpgsql' VOLATILE AS $$
4955 BEGIN
4956 UPDATE "member" SET
4957 "last_login" = NULL,
4958 "last_delegation_check" = NULL,
4959 "login" = NULL,
4960 "password" = NULL,
4961 "authority" = NULL,
4962 "authority_uid" = NULL,
4963 "authority_login" = NULL,
4964 "locked" = TRUE,
4965 "active" = FALSE,
4966 "notify_email" = NULL,
4967 "notify_email_unconfirmed" = NULL,
4968 "notify_email_secret" = NULL,
4969 "notify_email_secret_expiry" = NULL,
4970 "notify_email_lock_expiry" = NULL,
4971 "disable_notifications" = NULL,
4972 "notification_counter" = NULL,
4973 "notification_sample_size" = NULL,
4974 "notification_dow" = NULL,
4975 "notification_hour" = NULL,
4976 "login_recovery_expiry" = NULL,
4977 "password_reset_secret" = NULL,
4978 "password_reset_secret_expiry" = NULL,
4979 "organizational_unit" = NULL,
4980 "internal_posts" = NULL,
4981 "realname" = NULL,
4982 "birthday" = NULL,
4983 "address" = NULL,
4984 "email" = NULL,
4985 "xmpp_address" = NULL,
4986 "website" = NULL,
4987 "phone" = NULL,
4988 "mobile_phone" = NULL,
4989 "profession" = NULL,
4990 "external_memberships" = NULL,
4991 "external_posts" = NULL,
4992 "statement" = NULL
4993 WHERE "id" = "member_id_p";
4994 -- "text_search_data" is updated by triggers
4995 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
4996 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
4997 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
4998 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
4999 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
5000 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
5001 DELETE FROM "session" WHERE "member_id" = "member_id_p";
5002 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
5003 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
5004 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
5005 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
5006 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
5007 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
5008 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
5009 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
5010 DELETE FROM "direct_voter" USING "issue"
5011 WHERE "direct_voter"."issue_id" = "issue"."id"
5012 AND "issue"."closed" ISNULL
5013 AND "member_id" = "member_id_p";
5014 RETURN;
5015 END;
5016 $$;
5018 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)';
5021 CREATE FUNCTION "delete_private_data"()
5022 RETURNS VOID
5023 LANGUAGE 'plpgsql' VOLATILE AS $$
5024 BEGIN
5025 DELETE FROM "temporary_transaction_data";
5026 DELETE FROM "member" WHERE "activated" ISNULL;
5027 UPDATE "member" SET
5028 "invite_code" = NULL,
5029 "invite_code_expiry" = NULL,
5030 "admin_comment" = NULL,
5031 "last_login" = NULL,
5032 "last_delegation_check" = NULL,
5033 "login" = NULL,
5034 "password" = NULL,
5035 "authority" = NULL,
5036 "authority_uid" = NULL,
5037 "authority_login" = NULL,
5038 "lang" = NULL,
5039 "notify_email" = NULL,
5040 "notify_email_unconfirmed" = NULL,
5041 "notify_email_secret" = NULL,
5042 "notify_email_secret_expiry" = NULL,
5043 "notify_email_lock_expiry" = NULL,
5044 "disable_notifications" = NULL,
5045 "notification_counter" = NULL,
5046 "notification_sample_size" = NULL,
5047 "notification_dow" = NULL,
5048 "notification_hour" = NULL,
5049 "login_recovery_expiry" = NULL,
5050 "password_reset_secret" = NULL,
5051 "password_reset_secret_expiry" = NULL,
5052 "organizational_unit" = NULL,
5053 "internal_posts" = NULL,
5054 "realname" = NULL,
5055 "birthday" = NULL,
5056 "address" = NULL,
5057 "email" = NULL,
5058 "xmpp_address" = NULL,
5059 "website" = NULL,
5060 "phone" = NULL,
5061 "mobile_phone" = NULL,
5062 "profession" = NULL,
5063 "external_memberships" = NULL,
5064 "external_posts" = NULL,
5065 "formatting_engine" = NULL,
5066 "statement" = NULL;
5067 -- "text_search_data" is updated by triggers
5068 DELETE FROM "setting";
5069 DELETE FROM "setting_map";
5070 DELETE FROM "member_relation_setting";
5071 DELETE FROM "member_image";
5072 DELETE FROM "contact";
5073 DELETE FROM "ignored_member";
5074 DELETE FROM "session";
5075 DELETE FROM "area_setting";
5076 DELETE FROM "issue_setting";
5077 DELETE FROM "ignored_initiative";
5078 DELETE FROM "initiative_setting";
5079 DELETE FROM "suggestion_setting";
5080 DELETE FROM "non_voter";
5081 DELETE FROM "direct_voter" USING "issue"
5082 WHERE "direct_voter"."issue_id" = "issue"."id"
5083 AND "issue"."closed" ISNULL;
5084 RETURN;
5085 END;
5086 $$;
5088 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. See source code to see which data is deleted. If you need a different behaviour, copy this function and modify lf_export accordingly, to avoid data-leaks after updating.';
5092 COMMIT;