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