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