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