liquid_feedback_core
view core.sql @ 519:003b4cc8e9ef
Merged experimental work on alternative mechanism to the first (issue) quorum
| author | jbe |
|---|---|
| date | Tue May 03 20:35:30 2016 +0200 (2016-05-03) |
| parents | e4f619e87664 fae00a5c1c71 |
| children | 2a2f76da1177 |
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 "matching_admission_rule_condition" AS
2010 SELECT DISTINCT ON ("issue_id", "admission_rule_condition"."admission_rule_id")
2011 "issue"."id" AS "issue_id",
2012 "admission_rule_condition".*
2013 FROM "admission_rule_condition"
2014 JOIN "area" ON "admission_rule_condition"."unit_id" = "area"."unit_id"
2015 JOIN "issue" ON "area"."id" = "issue"."area_id"
2016 WHERE (
2017 "admission_rule_condition"."policy_id" ISNULL OR
2018 "admission_rule_condition"."policy_id" = "issue"."policy_id"
2019 ) AND (
2020 "admission_rule_condition"."area_id" ISNULL OR
2021 "admission_rule_condition"."area_id" = "area"."id"
2022 )
2023 ORDER BY
2024 "issue_id",
2025 "admission_rule_condition"."admission_rule_id",
2026 "admission_rule_condition"."policy_id" ISNULL,
2027 "admission_rule_condition"."area_id" ISNULL;
2029 COMMENT ON VIEW "matching_admission_rule_condition" IS 'Selects the most fitting "admission_rule_condition" for a given pair of "issue" and "admission_rule"';
2032 CREATE VIEW "applicable_admission_rule" AS
2033 SELECT * FROM "admission_rule"
2034 WHERE NOT EXISTS (
2035 SELECT NULL FROM "issue"
2036 JOIN "matching_admission_rule_condition" AS "condition"
2037 ON "issue"."id" = "condition"."issue_id"
2038 WHERE "condition"."admission_rule_id" = "admission_rule"."id"
2039 AND "issue"."accepted" > now() - "condition"."holdoff_time"
2040 );
2042 COMMENT ON VIEW "applicable_admission_rule" IS 'Filters "admission_rule"s which are not blocked by a recently admitted issue';
2045 CREATE VIEW "issue_for_admission" AS
2046 SELECT
2047 "issue".*,
2048 max("initiative"."supporter_count") AS "max_supporter_count"
2049 FROM "issue"
2050 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2051 JOIN "area" ON "issue"."area_id" = "area"."id"
2052 JOIN "admission_rule_condition"
2053 ON "admission_rule_condition"."unit_id" = "area"."unit_id"
2054 AND (
2055 "admission_rule_condition"."policy_id" ISNULL OR
2056 "admission_rule_condition"."policy_id" = "issue"."policy_id"
2057 )
2058 AND (
2059 "admission_rule_condition"."area_id" ISNULL OR
2060 "admission_rule_condition"."area_id" = "area"."id"
2061 )
2062 JOIN "applicable_admission_rule"
2063 ON "admission_rule_condition"."admission_rule_id" = "applicable_admission_rule"."id"
2064 WHERE "issue"."state" = 'admission'::"issue_state"
2065 GROUP BY "issue"."id"
2066 ORDER BY "max_supporter_count" DESC, "issue"."id";
2068 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';
2071 CREATE VIEW "unit_delegation" AS
2072 SELECT
2073 "unit"."id" AS "unit_id",
2074 "delegation"."id",
2075 "delegation"."truster_id",
2076 "delegation"."trustee_id",
2077 "delegation"."scope"
2078 FROM "unit"
2079 JOIN "delegation"
2080 ON "delegation"."unit_id" = "unit"."id"
2081 JOIN "member"
2082 ON "delegation"."truster_id" = "member"."id"
2083 JOIN "privilege"
2084 ON "delegation"."unit_id" = "privilege"."unit_id"
2085 AND "delegation"."truster_id" = "privilege"."member_id"
2086 WHERE "member"."active" AND "privilege"."voting_right";
2088 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
2091 CREATE VIEW "area_delegation" AS
2092 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
2093 "area"."id" AS "area_id",
2094 "delegation"."id",
2095 "delegation"."truster_id",
2096 "delegation"."trustee_id",
2097 "delegation"."scope"
2098 FROM "area"
2099 JOIN "delegation"
2100 ON "delegation"."unit_id" = "area"."unit_id"
2101 OR "delegation"."area_id" = "area"."id"
2102 JOIN "member"
2103 ON "delegation"."truster_id" = "member"."id"
2104 JOIN "privilege"
2105 ON "area"."unit_id" = "privilege"."unit_id"
2106 AND "delegation"."truster_id" = "privilege"."member_id"
2107 WHERE "member"."active" AND "privilege"."voting_right"
2108 ORDER BY
2109 "area"."id",
2110 "delegation"."truster_id",
2111 "delegation"."scope" DESC;
2113 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
2116 CREATE VIEW "issue_delegation" AS
2117 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
2118 "issue"."id" AS "issue_id",
2119 "delegation"."id",
2120 "delegation"."truster_id",
2121 "delegation"."trustee_id",
2122 "delegation"."scope"
2123 FROM "issue"
2124 JOIN "area"
2125 ON "area"."id" = "issue"."area_id"
2126 JOIN "delegation"
2127 ON "delegation"."unit_id" = "area"."unit_id"
2128 OR "delegation"."area_id" = "area"."id"
2129 OR "delegation"."issue_id" = "issue"."id"
2130 JOIN "member"
2131 ON "delegation"."truster_id" = "member"."id"
2132 JOIN "privilege"
2133 ON "area"."unit_id" = "privilege"."unit_id"
2134 AND "delegation"."truster_id" = "privilege"."member_id"
2135 WHERE "member"."active" AND "privilege"."voting_right"
2136 ORDER BY
2137 "issue"."id",
2138 "delegation"."truster_id",
2139 "delegation"."scope" DESC;
2141 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
2144 CREATE FUNCTION "membership_weight_with_skipping"
2145 ( "area_id_p" "area"."id"%TYPE,
2146 "member_id_p" "member"."id"%TYPE,
2147 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
2148 RETURNS INT4
2149 LANGUAGE 'plpgsql' STABLE AS $$
2150 DECLARE
2151 "sum_v" INT4;
2152 "delegation_row" "area_delegation"%ROWTYPE;
2153 BEGIN
2154 "sum_v" := 1;
2155 FOR "delegation_row" IN
2156 SELECT "area_delegation".*
2157 FROM "area_delegation" LEFT JOIN "membership"
2158 ON "membership"."area_id" = "area_id_p"
2159 AND "membership"."member_id" = "area_delegation"."truster_id"
2160 WHERE "area_delegation"."area_id" = "area_id_p"
2161 AND "area_delegation"."trustee_id" = "member_id_p"
2162 AND "membership"."member_id" ISNULL
2163 LOOP
2164 IF NOT
2165 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
2166 THEN
2167 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
2168 "area_id_p",
2169 "delegation_row"."truster_id",
2170 "skip_member_ids_p" || "delegation_row"."truster_id"
2171 );
2172 END IF;
2173 END LOOP;
2174 RETURN "sum_v";
2175 END;
2176 $$;
2178 COMMENT ON FUNCTION "membership_weight_with_skipping"
2179 ( "area"."id"%TYPE,
2180 "member"."id"%TYPE,
2181 INT4[] )
2182 IS 'Helper function for "membership_weight" function';
2185 CREATE FUNCTION "membership_weight"
2186 ( "area_id_p" "area"."id"%TYPE,
2187 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
2188 RETURNS INT4
2189 LANGUAGE 'plpgsql' STABLE AS $$
2190 BEGIN
2191 RETURN "membership_weight_with_skipping"(
2192 "area_id_p",
2193 "member_id_p",
2194 ARRAY["member_id_p"]
2195 );
2196 END;
2197 $$;
2199 COMMENT ON FUNCTION "membership_weight"
2200 ( "area"."id"%TYPE,
2201 "member"."id"%TYPE )
2202 IS 'Calculates the potential voting weight of a member in a given area';
2205 CREATE VIEW "member_count_view" AS
2206 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
2208 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
2211 CREATE VIEW "unit_member_count" AS
2212 SELECT
2213 "unit"."id" AS "unit_id",
2214 count("member"."id") AS "member_count"
2215 FROM "unit"
2216 LEFT JOIN "privilege"
2217 ON "privilege"."unit_id" = "unit"."id"
2218 AND "privilege"."voting_right"
2219 LEFT JOIN "member"
2220 ON "member"."id" = "privilege"."member_id"
2221 AND "member"."active"
2222 GROUP BY "unit"."id";
2224 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
2227 CREATE VIEW "area_member_count" AS
2228 SELECT
2229 "area"."id" AS "area_id",
2230 count("member"."id") AS "direct_member_count",
2231 coalesce(
2232 sum(
2233 CASE WHEN "member"."id" NOTNULL THEN
2234 "membership_weight"("area"."id", "member"."id")
2235 ELSE 0 END
2236 )
2237 ) AS "member_weight"
2238 FROM "area"
2239 LEFT JOIN "membership"
2240 ON "area"."id" = "membership"."area_id"
2241 LEFT JOIN "privilege"
2242 ON "privilege"."unit_id" = "area"."unit_id"
2243 AND "privilege"."member_id" = "membership"."member_id"
2244 AND "privilege"."voting_right"
2245 LEFT JOIN "member"
2246 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
2247 AND "member"."active"
2248 GROUP BY "area"."id";
2250 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';
2253 CREATE VIEW "opening_draft" AS
2254 SELECT "draft".* FROM (
2255 SELECT
2256 "initiative"."id" AS "initiative_id",
2257 min("draft"."id") AS "draft_id"
2258 FROM "initiative" JOIN "draft"
2259 ON "initiative"."id" = "draft"."initiative_id"
2260 GROUP BY "initiative"."id"
2261 ) AS "subquery"
2262 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
2264 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
2267 CREATE VIEW "current_draft" AS
2268 SELECT "draft".* FROM (
2269 SELECT
2270 "initiative"."id" AS "initiative_id",
2271 max("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 "current_draft" IS 'All latest drafts for each initiative';
2281 CREATE VIEW "critical_opinion" AS
2282 SELECT * FROM "opinion"
2283 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
2284 OR ("degree" = -2 AND "fulfilled" = TRUE);
2286 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
2289 CREATE VIEW "issue_supporter_in_admission_state" AS
2290 SELECT DISTINCT -- TODO: DISTINCT needed?
2291 "area"."unit_id",
2292 "issue"."area_id",
2293 "issue"."id" AS "issue_id",
2294 "supporter"."member_id",
2295 "direct_interest_snapshot"."weight"
2296 FROM "issue"
2297 JOIN "area" ON "area"."id" = "issue"."area_id"
2298 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
2299 JOIN "direct_interest_snapshot"
2300 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
2301 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
2302 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
2303 WHERE "issue"."state" = 'admission'::"issue_state";
2305 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';
2308 CREATE VIEW "initiative_suggestion_order_calculation" AS
2309 SELECT
2310 "initiative"."id" AS "initiative_id",
2311 ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final"
2312 FROM "initiative" JOIN "issue"
2313 ON "initiative"."issue_id" = "issue"."id"
2314 WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL)
2315 OR ("initiative"."final_suggestion_order_calculated" = FALSE);
2317 COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';
2319 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';
2322 CREATE VIEW "individual_suggestion_ranking" AS
2323 SELECT
2324 "opinion"."initiative_id",
2325 "opinion"."member_id",
2326 "direct_interest_snapshot"."weight",
2327 CASE WHEN
2328 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
2329 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
2330 THEN 1 ELSE
2331 CASE WHEN
2332 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
2333 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
2334 THEN 2 ELSE
2335 CASE WHEN
2336 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
2337 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
2338 THEN 3 ELSE 4 END
2339 END
2340 END AS "preference",
2341 "opinion"."suggestion_id"
2342 FROM "opinion"
2343 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
2344 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2345 JOIN "direct_interest_snapshot"
2346 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
2347 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
2348 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
2350 COMMENT ON VIEW "individual_suggestion_ranking" IS 'Helper view for "lf_update_suggestion_order" to allow a proportional ordering of suggestions within an initiative';
2353 CREATE VIEW "battle_participant" AS
2354 SELECT "initiative"."id", "initiative"."issue_id"
2355 FROM "issue" JOIN "initiative"
2356 ON "issue"."id" = "initiative"."issue_id"
2357 WHERE "initiative"."admitted"
2358 UNION ALL
2359 SELECT NULL, "id" AS "issue_id"
2360 FROM "issue";
2362 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
2365 CREATE VIEW "battle_view" AS
2366 SELECT
2367 "issue"."id" AS "issue_id",
2368 "winning_initiative"."id" AS "winning_initiative_id",
2369 "losing_initiative"."id" AS "losing_initiative_id",
2370 sum(
2371 CASE WHEN
2372 coalesce("better_vote"."grade", 0) >
2373 coalesce("worse_vote"."grade", 0)
2374 THEN "direct_voter"."weight" ELSE 0 END
2375 ) AS "count"
2376 FROM "issue"
2377 LEFT JOIN "direct_voter"
2378 ON "issue"."id" = "direct_voter"."issue_id"
2379 JOIN "battle_participant" AS "winning_initiative"
2380 ON "issue"."id" = "winning_initiative"."issue_id"
2381 JOIN "battle_participant" AS "losing_initiative"
2382 ON "issue"."id" = "losing_initiative"."issue_id"
2383 LEFT JOIN "vote" AS "better_vote"
2384 ON "direct_voter"."member_id" = "better_vote"."member_id"
2385 AND "winning_initiative"."id" = "better_vote"."initiative_id"
2386 LEFT JOIN "vote" AS "worse_vote"
2387 ON "direct_voter"."member_id" = "worse_vote"."member_id"
2388 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
2389 WHERE "issue"."state" = 'voting'
2390 AND "issue"."phase_finished" NOTNULL
2391 AND (
2392 "winning_initiative"."id" != "losing_initiative"."id" OR
2393 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
2394 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
2395 GROUP BY
2396 "issue"."id",
2397 "winning_initiative"."id",
2398 "losing_initiative"."id";
2400 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';
2403 CREATE VIEW "expired_session" AS
2404 SELECT * FROM "session" WHERE now() > "expiry";
2406 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
2407 DELETE FROM "session" WHERE "ident" = OLD."ident";
2409 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
2410 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
2413 CREATE VIEW "open_issue" AS
2414 SELECT * FROM "issue" WHERE "closed" ISNULL;
2416 COMMENT ON VIEW "open_issue" IS 'All open issues';
2419 CREATE VIEW "member_contingent" AS
2420 SELECT
2421 "member"."id" AS "member_id",
2422 "contingent"."polling",
2423 "contingent"."time_frame",
2424 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
2425 (
2426 SELECT count(1) FROM "draft"
2427 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
2428 WHERE "draft"."author_id" = "member"."id"
2429 AND "initiative"."polling" = "contingent"."polling"
2430 AND "draft"."created" > now() - "contingent"."time_frame"
2431 ) + (
2432 SELECT count(1) FROM "suggestion"
2433 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
2434 WHERE "suggestion"."author_id" = "member"."id"
2435 AND "contingent"."polling" = FALSE
2436 AND "suggestion"."created" > now() - "contingent"."time_frame"
2437 )
2438 ELSE NULL END AS "text_entry_count",
2439 "contingent"."text_entry_limit",
2440 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
2441 SELECT count(1) FROM "opening_draft" AS "draft"
2442 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
2443 WHERE "draft"."author_id" = "member"."id"
2444 AND "initiative"."polling" = "contingent"."polling"
2445 AND "draft"."created" > now() - "contingent"."time_frame"
2446 ) ELSE NULL END AS "initiative_count",
2447 "contingent"."initiative_limit"
2448 FROM "member" CROSS JOIN "contingent";
2450 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
2452 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
2453 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
2456 CREATE VIEW "member_contingent_left" AS
2457 SELECT
2458 "member_id",
2459 "polling",
2460 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
2461 max("initiative_limit" - "initiative_count") AS "initiatives_left"
2462 FROM "member_contingent" GROUP BY "member_id", "polling";
2464 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.';
2467 CREATE VIEW "event_for_notification" AS
2468 SELECT
2469 "member"."id" AS "recipient_id",
2470 "event".*
2471 FROM "member" CROSS JOIN "event"
2472 JOIN "issue" ON "issue"."id" = "event"."issue_id"
2473 JOIN "area" ON "area"."id" = "issue"."area_id"
2474 LEFT JOIN "privilege" ON
2475 "privilege"."member_id" = "member"."id" AND
2476 "privilege"."unit_id" = "area"."unit_id" AND
2477 "privilege"."voting_right" = TRUE
2478 LEFT JOIN "subscription" ON
2479 "subscription"."member_id" = "member"."id" AND
2480 "subscription"."unit_id" = "area"."unit_id"
2481 LEFT JOIN "ignored_area" ON
2482 "ignored_area"."member_id" = "member"."id" AND
2483 "ignored_area"."area_id" = "issue"."area_id"
2484 LEFT JOIN "interest" ON
2485 "interest"."member_id" = "member"."id" AND
2486 "interest"."issue_id" = "event"."issue_id"
2487 LEFT JOIN "supporter" ON
2488 "supporter"."member_id" = "member"."id" AND
2489 "supporter"."initiative_id" = "event"."initiative_id"
2490 WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL)
2491 AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
2492 AND (
2493 "event"."event" = 'issue_state_changed'::"event_type" OR
2494 ( "event"."event" = 'initiative_revoked'::"event_type" AND
2495 "supporter"."member_id" NOTNULL ) );
2497 COMMENT ON VIEW "event_for_notification" IS 'Entries of the "event" table which are of interest for a particular notification mail recipient';
2499 COMMENT ON COLUMN "event_for_notification"."recipient_id" IS 'member_id of the recipient of a notification mail';
2502 CREATE VIEW "updated_initiative" AS
2503 SELECT
2504 "supporter"."member_id" AS "recipient_id",
2505 FALSE AS "featured",
2506 "supporter"."initiative_id"
2507 FROM "supporter"
2508 JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id"
2509 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2510 LEFT JOIN "notification_initiative_sent" AS "sent" ON
2511 "sent"."member_id" = "supporter"."member_id" AND
2512 "sent"."initiative_id" = "supporter"."initiative_id"
2513 LEFT JOIN "ignored_initiative" ON
2514 "ignored_initiative"."member_id" = "supporter"."member_id" AND
2515 "ignored_initiative"."initiative_id" = "supporter"."initiative_id"
2516 WHERE "issue"."state" IN ('admission', 'discussion')
2517 AND "initiative"."revoked" ISNULL
2518 AND "ignored_initiative"."member_id" ISNULL
2519 AND (
2520 EXISTS (
2521 SELECT NULL FROM "draft"
2522 LEFT JOIN "ignored_member" ON
2523 "ignored_member"."member_id" = "supporter"."member_id" AND
2524 "ignored_member"."other_member_id" = "draft"."author_id"
2525 WHERE "draft"."initiative_id" = "supporter"."initiative_id"
2526 AND "draft"."id" > "supporter"."draft_id"
2527 AND "ignored_member"."member_id" ISNULL
2528 ) OR EXISTS (
2529 SELECT NULL FROM "suggestion"
2530 LEFT JOIN "opinion" ON
2531 "opinion"."member_id" = "supporter"."member_id" AND
2532 "opinion"."suggestion_id" = "suggestion"."id"
2533 LEFT JOIN "ignored_member" ON
2534 "ignored_member"."member_id" = "supporter"."member_id" AND
2535 "ignored_member"."other_member_id" = "suggestion"."author_id"
2536 WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"
2537 AND "opinion"."member_id" ISNULL
2538 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
2539 AND "ignored_member"."member_id" ISNULL
2540 )
2541 );
2543 COMMENT ON VIEW "updated_initiative" IS 'Helper view for view "updated_or_featured_initiative"';
2546 CREATE FUNCTION "featured_initiative"
2547 ( "recipient_id_p" "member"."id"%TYPE,
2548 "area_id_p" "area"."id"%TYPE )
2549 RETURNS SETOF "initiative"."id"%TYPE
2550 LANGUAGE 'plpgsql' STABLE AS $$
2551 DECLARE
2552 "counter_v" "member"."notification_counter"%TYPE;
2553 "sample_size_v" "member"."notification_sample_size"%TYPE;
2554 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
2555 "match_v" BOOLEAN;
2556 "member_id_v" "member"."id"%TYPE;
2557 "seed_v" TEXT;
2558 "initiative_id_v" "initiative"."id"%TYPE;
2559 BEGIN
2560 SELECT "notification_counter", "notification_sample_size"
2561 INTO "counter_v", "sample_size_v"
2562 FROM "member" WHERE "id" = "recipient_id_p";
2563 "initiative_id_ary" := '{}';
2564 LOOP
2565 "match_v" := FALSE;
2566 FOR "member_id_v", "seed_v" IN
2567 SELECT * FROM (
2568 SELECT DISTINCT
2569 "supporter"."member_id",
2570 md5(
2571 "recipient_id_p" || '-' ||
2572 "counter_v" || '-' ||
2573 "area_id_p" || '-' ||
2574 "supporter"."member_id"
2575 ) AS "seed"
2576 FROM "supporter"
2577 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
2578 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2579 WHERE "supporter"."member_id" != "recipient_id_p"
2580 AND "issue"."area_id" = "area_id_p"
2581 AND "issue"."state" IN ('admission', 'discussion', 'verification')
2582 ) AS "subquery"
2583 ORDER BY "seed"
2584 LOOP
2585 SELECT "initiative"."id" INTO "initiative_id_v"
2586 FROM "initiative"
2587 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2588 JOIN "area" ON "area"."id" = "issue"."area_id"
2589 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
2590 LEFT JOIN "supporter" AS "self_support" ON
2591 "self_support"."initiative_id" = "initiative"."id" AND
2592 "self_support"."member_id" = "recipient_id_p"
2593 LEFT JOIN "privilege" ON
2594 "privilege"."member_id" = "recipient_id_p" AND
2595 "privilege"."unit_id" = "area"."unit_id" AND
2596 "privilege"."voting_right" = TRUE
2597 LEFT JOIN "subscription" ON
2598 "subscription"."member_id" = "recipient_id_p" AND
2599 "subscription"."unit_id" = "area"."unit_id"
2600 LEFT JOIN "ignored_initiative" ON
2601 "ignored_initiative"."member_id" = "recipient_id_p" AND
2602 "ignored_initiative"."initiative_id" = "initiative"."id"
2603 WHERE "supporter"."member_id" = "member_id_v"
2604 AND "issue"."area_id" = "area_id_p"
2605 AND "issue"."state" IN ('admission', 'discussion', 'verification')
2606 AND "initiative"."revoked" ISNULL
2607 AND "self_support"."member_id" ISNULL
2608 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
2609 AND (
2610 "privilege"."member_id" NOTNULL OR
2611 "subscription"."member_id" NOTNULL )
2612 AND "ignored_initiative"."member_id" ISNULL
2613 AND NOT EXISTS (
2614 SELECT NULL FROM "draft"
2615 JOIN "ignored_member" ON
2616 "ignored_member"."member_id" = "recipient_id_p" AND
2617 "ignored_member"."other_member_id" = "draft"."author_id"
2618 WHERE "draft"."initiative_id" = "initiative"."id"
2619 )
2620 ORDER BY md5("seed_v" || '-' || "initiative"."id")
2621 LIMIT 1;
2622 IF FOUND THEN
2623 "match_v" := TRUE;
2624 RETURN NEXT "initiative_id_v";
2625 IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
2626 RETURN;
2627 END IF;
2628 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
2629 END IF;
2630 END LOOP;
2631 EXIT WHEN NOT "match_v";
2632 END LOOP;
2633 RETURN;
2634 END;
2635 $$;
2637 COMMENT ON FUNCTION "featured_initiative"
2638 ( "recipient_id_p" "member"."id"%TYPE,
2639 "area_id_p" "area"."id"%TYPE )
2640 IS 'Helper function for view "updated_or_featured_initiative"';
2643 CREATE VIEW "updated_or_featured_initiative" AS
2644 SELECT
2645 "subquery".*,
2646 NOT EXISTS (
2647 SELECT NULL FROM "initiative" AS "better_initiative"
2648 WHERE "better_initiative"."issue_id" = "initiative"."issue_id"
2649 AND
2650 ( COALESCE("better_initiative"."supporter_count", -1),
2651 -"better_initiative"."id" ) >
2652 ( COALESCE("initiative"."supporter_count", -1),
2653 -"initiative"."id" )
2654 ) AS "leading"
2655 FROM (
2656 SELECT * FROM "updated_initiative"
2657 UNION ALL
2658 SELECT
2659 "member"."id" AS "recipient_id",
2660 TRUE AS "featured",
2661 "featured_initiative_id" AS "initiative_id"
2662 FROM "member" CROSS JOIN "area"
2663 CROSS JOIN LATERAL
2664 "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id"
2665 JOIN "initiative" ON "initiative"."id" = "featured_initiative_id"
2666 ) AS "subquery"
2667 JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id";
2669 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';
2671 COMMENT ON COLUMN "updated_or_featured_initiative"."recipient_id" IS '"id" of the member who receives the notification mail';
2672 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")';
2673 COMMENT ON COLUMN "updated_or_featured_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
2674 COMMENT ON COLUMN "updated_or_featured_initiative"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue';
2677 CREATE VIEW "leading_complement_initiative" AS
2678 SELECT * FROM (
2679 SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id")
2680 "uf_initiative"."recipient_id",
2681 FALSE AS "featured",
2682 "uf_initiative"."initiative_id",
2683 TRUE AS "leading"
2684 FROM "updated_or_featured_initiative" AS "uf_initiative"
2685 JOIN "initiative" AS "uf_initiative_full" ON
2686 "uf_initiative_full"."id" = "uf_initiative"."initiative_id"
2687 JOIN "initiative" ON
2688 "initiative"."issue_id" = "uf_initiative_full"."issue_id"
2689 WHERE "initiative"."revoked" ISNULL
2690 ORDER BY
2691 "uf_initiative"."recipient_id",
2692 "initiative"."issue_id",
2693 "initiative"."supporter_count" DESC,
2694 "initiative"."id"
2695 ) AS "subquery"
2696 WHERE NOT EXISTS (
2697 SELECT NULL FROM "updated_or_featured_initiative" AS "other"
2698 WHERE "other"."recipient_id" = "subquery"."recipient_id"
2699 AND "other"."initiative_id" = "subquery"."initiative_id"
2700 );
2702 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';
2703 COMMENT ON COLUMN "leading_complement_initiative"."featured" IS 'Always FALSE in this view';
2704 COMMENT ON COLUMN "leading_complement_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
2705 COMMENT ON COLUMN "leading_complement_initiative"."leading" IS 'Always TRUE in this view';
2708 CREATE VIEW "unfiltered_initiative_for_notification" AS
2709 SELECT
2710 "subquery".*,
2711 "supporter"."member_id" NOTNULL AS "supported",
2712 CASE WHEN "supporter"."member_id" NOTNULL THEN
2713 EXISTS (
2714 SELECT NULL FROM "draft"
2715 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
2716 AND "draft"."id" > "supporter"."draft_id"
2717 )
2718 ELSE
2719 EXISTS (
2720 SELECT NULL FROM "draft"
2721 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
2722 AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE)
2723 )
2724 END AS "new_draft",
2725 CASE WHEN "supporter"."member_id" NOTNULL THEN
2726 ( SELECT count(1) FROM "suggestion"
2727 LEFT JOIN "opinion" ON
2728 "opinion"."member_id" = "supporter"."member_id" AND
2729 "opinion"."suggestion_id" = "suggestion"."id"
2730 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
2731 AND "opinion"."member_id" ISNULL
2732 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
2733 )
2734 ELSE
2735 ( SELECT count(1) FROM "suggestion"
2736 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
2737 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
2738 )
2739 END AS "new_suggestion_count"
2740 FROM (
2741 SELECT * FROM "updated_or_featured_initiative"
2742 UNION ALL
2743 SELECT * FROM "leading_complement_initiative"
2744 ) AS "subquery"
2745 LEFT JOIN "supporter" ON
2746 "supporter"."member_id" = "subquery"."recipient_id" AND
2747 "supporter"."initiative_id" = "subquery"."initiative_id"
2748 LEFT JOIN "notification_initiative_sent" AS "sent" ON
2749 "sent"."member_id" = "subquery"."recipient_id" AND
2750 "sent"."initiative_id" = "subquery"."initiative_id";
2752 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';
2754 COMMENT ON COLUMN "unfiltered_initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient';
2755 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)';
2756 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")';
2759 CREATE VIEW "initiative_for_notification" AS
2760 SELECT "unfiltered1".*
2761 FROM "unfiltered_initiative_for_notification" "unfiltered1"
2762 JOIN "initiative" AS "initiative1" ON
2763 "initiative1"."id" = "unfiltered1"."initiative_id"
2764 JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id"
2765 WHERE EXISTS (
2766 SELECT NULL
2767 FROM "unfiltered_initiative_for_notification" "unfiltered2"
2768 JOIN "initiative" AS "initiative2" ON
2769 "initiative2"."id" = "unfiltered2"."initiative_id"
2770 JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id"
2771 WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"
2772 AND "issue1"."area_id" = "issue2"."area_id"
2773 AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 )
2774 );
2776 COMMENT ON VIEW "initiative_for_notification" IS 'Initiatives to be included in a scheduled notification mail';
2778 COMMENT ON COLUMN "initiative_for_notification"."recipient_id" IS '"id" of the member who receives the notification mail';
2779 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")';
2780 COMMENT ON COLUMN "initiative_for_notification"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
2781 COMMENT ON COLUMN "initiative_for_notification"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue';
2782 COMMENT ON COLUMN "initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient';
2783 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)';
2784 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")';
2787 CREATE VIEW "scheduled_notification_to_send" AS
2788 SELECT * FROM (
2789 SELECT
2790 "id" AS "recipient_id",
2791 now() - CASE WHEN "notification_dow" ISNULL THEN
2792 ( "notification_sent"::DATE + CASE
2793 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
2794 THEN 0 ELSE 1 END
2795 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
2796 ELSE
2797 ( "notification_sent"::DATE +
2798 ( 7 + "notification_dow" -
2799 EXTRACT(DOW FROM
2800 ( "notification_sent"::DATE + CASE
2801 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
2802 THEN 0 ELSE 1 END
2803 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
2804 )::INTEGER
2805 ) % 7 +
2806 CASE
2807 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
2808 THEN 0 ELSE 1
2809 END
2810 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
2811 END AS "pending"
2812 FROM (
2813 SELECT
2814 "id",
2815 COALESCE("notification_sent", "activated") AS "notification_sent",
2816 "notification_dow",
2817 "notification_hour"
2818 FROM "member"
2819 WHERE "disable_notifications" = FALSE
2820 AND "notification_hour" NOTNULL
2821 ) AS "subquery1"
2822 ) AS "subquery2"
2823 WHERE "pending" > '0'::INTERVAL;
2825 COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending';
2827 COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail';
2828 COMMENT ON COLUMN "scheduled_notification_to_send"."pending" IS 'Duration for which the notification mail has already been pending';
2831 CREATE VIEW "newsletter_to_send" AS
2832 SELECT
2833 "member"."id" AS "recipient_id",
2834 "newsletter"."id" AS "newsletter_id",
2835 "newsletter"."published"
2836 FROM "newsletter" CROSS JOIN "member"
2837 LEFT JOIN "privilege" ON
2838 "privilege"."member_id" = "member"."id" AND
2839 "privilege"."unit_id" = "newsletter"."unit_id" AND
2840 "privilege"."voting_right" = TRUE
2841 LEFT JOIN "subscription" ON
2842 "subscription"."member_id" = "member"."id" AND
2843 "subscription"."unit_id" = "newsletter"."unit_id"
2844 WHERE "newsletter"."published" <= now()
2845 AND "newsletter"."sent" ISNULL
2846 AND "member"."locked" = FALSE
2847 AND (
2848 "member"."disable_notifications" = FALSE OR
2849 "newsletter"."include_all_members" = TRUE )
2850 AND (
2851 "newsletter"."unit_id" ISNULL OR
2852 "privilege"."member_id" NOTNULL OR
2853 "subscription"."member_id" NOTNULL );
2855 COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';
2857 COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter was supposed to be sent out (can be used for ordering)';
2861 ------------------------------------------------------
2862 -- Row set returning function for delegation chains --
2863 ------------------------------------------------------
2866 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2867 ('first', 'intermediate', 'last', 'repetition');
2869 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2872 CREATE TYPE "delegation_chain_row" AS (
2873 "index" INT4,
2874 "member_id" INT4,
2875 "member_valid" BOOLEAN,
2876 "participation" BOOLEAN,
2877 "overridden" BOOLEAN,
2878 "scope_in" "delegation_scope",
2879 "scope_out" "delegation_scope",
2880 "disabled_out" BOOLEAN,
2881 "loop" "delegation_chain_loop_tag" );
2883 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
2885 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2886 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';
2887 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2888 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2889 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2890 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2891 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2894 CREATE FUNCTION "delegation_chain_for_closed_issue"
2895 ( "member_id_p" "member"."id"%TYPE,
2896 "issue_id_p" "issue"."id"%TYPE )
2897 RETURNS SETOF "delegation_chain_row"
2898 LANGUAGE 'plpgsql' STABLE AS $$
2899 DECLARE
2900 "output_row" "delegation_chain_row";
2901 "direct_voter_row" "direct_voter"%ROWTYPE;
2902 "delegating_voter_row" "delegating_voter"%ROWTYPE;
2903 BEGIN
2904 "output_row"."index" := 0;
2905 "output_row"."member_id" := "member_id_p";
2906 "output_row"."member_valid" := TRUE;
2907 "output_row"."participation" := FALSE;
2908 "output_row"."overridden" := FALSE;
2909 "output_row"."disabled_out" := FALSE;
2910 LOOP
2911 SELECT INTO "direct_voter_row" * FROM "direct_voter"
2912 WHERE "issue_id" = "issue_id_p"
2913 AND "member_id" = "output_row"."member_id";
2914 IF "direct_voter_row"."member_id" NOTNULL THEN
2915 "output_row"."participation" := TRUE;
2916 "output_row"."scope_out" := NULL;
2917 "output_row"."disabled_out" := NULL;
2918 RETURN NEXT "output_row";
2919 RETURN;
2920 END IF;
2921 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
2922 WHERE "issue_id" = "issue_id_p"
2923 AND "member_id" = "output_row"."member_id";
2924 IF "delegating_voter_row"."member_id" ISNULL THEN
2925 RETURN;
2926 END IF;
2927 "output_row"."scope_out" := "delegating_voter_row"."scope";
2928 RETURN NEXT "output_row";
2929 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
2930 "output_row"."scope_in" := "output_row"."scope_out";
2931 END LOOP;
2932 END;
2933 $$;
2935 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
2936 ( "member"."id"%TYPE,
2937 "member"."id"%TYPE )
2938 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
2941 CREATE FUNCTION "delegation_chain"
2942 ( "member_id_p" "member"."id"%TYPE,
2943 "unit_id_p" "unit"."id"%TYPE,
2944 "area_id_p" "area"."id"%TYPE,
2945 "issue_id_p" "issue"."id"%TYPE,
2946 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2947 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2948 RETURNS SETOF "delegation_chain_row"
2949 LANGUAGE 'plpgsql' STABLE AS $$
2950 DECLARE
2951 "scope_v" "delegation_scope";
2952 "unit_id_v" "unit"."id"%TYPE;
2953 "area_id_v" "area"."id"%TYPE;
2954 "issue_row" "issue"%ROWTYPE;
2955 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2956 "loop_member_id_v" "member"."id"%TYPE;
2957 "output_row" "delegation_chain_row";
2958 "output_rows" "delegation_chain_row"[];
2959 "simulate_v" BOOLEAN;
2960 "simulate_here_v" BOOLEAN;
2961 "delegation_row" "delegation"%ROWTYPE;
2962 "row_count" INT4;
2963 "i" INT4;
2964 "loop_v" BOOLEAN;
2965 BEGIN
2966 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
2967 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
2968 END IF;
2969 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
2970 "simulate_v" := TRUE;
2971 ELSE
2972 "simulate_v" := FALSE;
2973 END IF;
2974 IF
2975 "unit_id_p" NOTNULL AND
2976 "area_id_p" ISNULL AND
2977 "issue_id_p" ISNULL
2978 THEN
2979 "scope_v" := 'unit';
2980 "unit_id_v" := "unit_id_p";
2981 ELSIF
2982 "unit_id_p" ISNULL AND
2983 "area_id_p" NOTNULL AND
2984 "issue_id_p" ISNULL
2985 THEN
2986 "scope_v" := 'area';
2987 "area_id_v" := "area_id_p";
2988 SELECT "unit_id" INTO "unit_id_v"
2989 FROM "area" WHERE "id" = "area_id_v";
2990 ELSIF
2991 "unit_id_p" ISNULL AND
2992 "area_id_p" ISNULL AND
2993 "issue_id_p" NOTNULL
2994 THEN
2995 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
2996 IF "issue_row"."id" ISNULL THEN
2997 RETURN;
2998 END IF;
2999 IF "issue_row"."closed" NOTNULL THEN
3000 IF "simulate_v" THEN
3001 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
3002 END IF;
3003 FOR "output_row" IN
3004 SELECT * FROM
3005 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
3006 LOOP
3007 RETURN NEXT "output_row";
3008 END LOOP;
3009 RETURN;
3010 END IF;
3011 "scope_v" := 'issue';
3012 SELECT "area_id" INTO "area_id_v"
3013 FROM "issue" WHERE "id" = "issue_id_p";
3014 SELECT "unit_id" INTO "unit_id_v"
3015 FROM "area" WHERE "id" = "area_id_v";
3016 ELSE
3017 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
3018 END IF;
3019 "visited_member_ids" := '{}';
3020 "loop_member_id_v" := NULL;
3021 "output_rows" := '{}';
3022 "output_row"."index" := 0;
3023 "output_row"."member_id" := "member_id_p";
3024 "output_row"."member_valid" := TRUE;
3025 "output_row"."participation" := FALSE;
3026 "output_row"."overridden" := FALSE;
3027 "output_row"."disabled_out" := FALSE;
3028 "output_row"."scope_out" := NULL;
3029 LOOP
3030 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
3031 "loop_member_id_v" := "output_row"."member_id";
3032 ELSE
3033 "visited_member_ids" :=
3034 "visited_member_ids" || "output_row"."member_id";
3035 END IF;
3036 IF "output_row"."participation" ISNULL THEN
3037 "output_row"."overridden" := NULL;
3038 ELSIF "output_row"."participation" THEN
3039 "output_row"."overridden" := TRUE;
3040 END IF;
3041 "output_row"."scope_in" := "output_row"."scope_out";
3042 "output_row"."member_valid" := EXISTS (
3043 SELECT NULL FROM "member" JOIN "privilege"
3044 ON "privilege"."member_id" = "member"."id"
3045 AND "privilege"."unit_id" = "unit_id_v"
3046 WHERE "id" = "output_row"."member_id"
3047 AND "member"."active" AND "privilege"."voting_right"
3048 );
3049 "simulate_here_v" := (
3050 "simulate_v" AND
3051 "output_row"."member_id" = "member_id_p"
3052 );
3053 "delegation_row" := ROW(NULL);
3054 IF "output_row"."member_valid" OR "simulate_here_v" THEN
3055 IF "scope_v" = 'unit' THEN
3056 IF NOT "simulate_here_v" THEN
3057 SELECT * INTO "delegation_row" FROM "delegation"
3058 WHERE "truster_id" = "output_row"."member_id"
3059 AND "unit_id" = "unit_id_v";
3060 END IF;
3061 ELSIF "scope_v" = 'area' THEN
3062 "output_row"."participation" := EXISTS (
3063 SELECT NULL FROM "membership"
3064 WHERE "area_id" = "area_id_p"
3065 AND "member_id" = "output_row"."member_id"
3066 );
3067 IF "simulate_here_v" THEN
3068 IF "simulate_trustee_id_p" ISNULL THEN
3069 SELECT * INTO "delegation_row" FROM "delegation"
3070 WHERE "truster_id" = "output_row"."member_id"
3071 AND "unit_id" = "unit_id_v";
3072 END IF;
3073 ELSE
3074 SELECT * INTO "delegation_row" FROM "delegation"
3075 WHERE "truster_id" = "output_row"."member_id"
3076 AND (
3077 "unit_id" = "unit_id_v" OR
3078 "area_id" = "area_id_v"
3079 )
3080 ORDER BY "scope" DESC;
3081 END IF;
3082 ELSIF "scope_v" = 'issue' THEN
3083 IF "issue_row"."fully_frozen" ISNULL THEN
3084 "output_row"."participation" := EXISTS (
3085 SELECT NULL FROM "interest"
3086 WHERE "issue_id" = "issue_id_p"
3087 AND "member_id" = "output_row"."member_id"
3088 );
3089 ELSE
3090 IF "output_row"."member_id" = "member_id_p" THEN
3091 "output_row"."participation" := EXISTS (
3092 SELECT NULL FROM "direct_voter"
3093 WHERE "issue_id" = "issue_id_p"
3094 AND "member_id" = "output_row"."member_id"
3095 );
3096 ELSE
3097 "output_row"."participation" := NULL;
3098 END IF;
3099 END IF;
3100 IF "simulate_here_v" THEN
3101 IF "simulate_trustee_id_p" ISNULL THEN
3102 SELECT * INTO "delegation_row" FROM "delegation"
3103 WHERE "truster_id" = "output_row"."member_id"
3104 AND (
3105 "unit_id" = "unit_id_v" OR
3106 "area_id" = "area_id_v"
3107 )
3108 ORDER BY "scope" DESC;
3109 END IF;
3110 ELSE
3111 SELECT * INTO "delegation_row" FROM "delegation"
3112 WHERE "truster_id" = "output_row"."member_id"
3113 AND (
3114 "unit_id" = "unit_id_v" OR
3115 "area_id" = "area_id_v" OR
3116 "issue_id" = "issue_id_p"
3117 )
3118 ORDER BY "scope" DESC;
3119 END IF;
3120 END IF;
3121 ELSE
3122 "output_row"."participation" := FALSE;
3123 END IF;
3124 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
3125 "output_row"."scope_out" := "scope_v";
3126 "output_rows" := "output_rows" || "output_row";
3127 "output_row"."member_id" := "simulate_trustee_id_p";
3128 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
3129 "output_row"."scope_out" := "delegation_row"."scope";
3130 "output_rows" := "output_rows" || "output_row";
3131 "output_row"."member_id" := "delegation_row"."trustee_id";
3132 ELSIF "delegation_row"."scope" NOTNULL THEN
3133 "output_row"."scope_out" := "delegation_row"."scope";
3134 "output_row"."disabled_out" := TRUE;
3135 "output_rows" := "output_rows" || "output_row";
3136 EXIT;
3137 ELSE
3138 "output_row"."scope_out" := NULL;
3139 "output_rows" := "output_rows" || "output_row";
3140 EXIT;
3141 END IF;
3142 EXIT WHEN "loop_member_id_v" NOTNULL;
3143 "output_row"."index" := "output_row"."index" + 1;
3144 END LOOP;
3145 "row_count" := array_upper("output_rows", 1);
3146 "i" := 1;
3147 "loop_v" := FALSE;
3148 LOOP
3149 "output_row" := "output_rows"["i"];
3150 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
3151 IF "loop_v" THEN
3152 IF "i" + 1 = "row_count" THEN
3153 "output_row"."loop" := 'last';
3154 ELSIF "i" = "row_count" THEN
3155 "output_row"."loop" := 'repetition';
3156 ELSE
3157 "output_row"."loop" := 'intermediate';
3158 END IF;
3159 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
3160 "output_row"."loop" := 'first';
3161 "loop_v" := TRUE;
3162 END IF;
3163 IF "scope_v" = 'unit' THEN
3164 "output_row"."participation" := NULL;
3165 END IF;
3166 RETURN NEXT "output_row";
3167 "i" := "i" + 1;
3168 END LOOP;
3169 RETURN;
3170 END;
3171 $$;
3173 COMMENT ON FUNCTION "delegation_chain"
3174 ( "member"."id"%TYPE,
3175 "unit"."id"%TYPE,
3176 "area"."id"%TYPE,
3177 "issue"."id"%TYPE,
3178 "member"."id"%TYPE,
3179 BOOLEAN )
3180 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
3184 ---------------------------------------------------------
3185 -- Single row returning function for delegation chains --
3186 ---------------------------------------------------------
3189 CREATE TYPE "delegation_info_loop_type" AS ENUM
3190 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
3192 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''';
3195 CREATE TYPE "delegation_info_type" AS (
3196 "own_participation" BOOLEAN,
3197 "own_delegation_scope" "delegation_scope",
3198 "first_trustee_id" INT4,
3199 "first_trustee_participation" BOOLEAN,
3200 "first_trustee_ellipsis" BOOLEAN,
3201 "other_trustee_id" INT4,
3202 "other_trustee_participation" BOOLEAN,
3203 "other_trustee_ellipsis" BOOLEAN,
3204 "delegation_loop" "delegation_info_loop_type",
3205 "participating_member_id" INT4 );
3207 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';
3209 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
3210 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
3211 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
3212 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
3213 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
3214 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
3215 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)';
3216 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
3217 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';
3218 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';
3221 CREATE FUNCTION "delegation_info"
3222 ( "member_id_p" "member"."id"%TYPE,
3223 "unit_id_p" "unit"."id"%TYPE,
3224 "area_id_p" "area"."id"%TYPE,
3225 "issue_id_p" "issue"."id"%TYPE,
3226 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
3227 "simulate_default_p" BOOLEAN DEFAULT FALSE )
3228 RETURNS "delegation_info_type"
3229 LANGUAGE 'plpgsql' STABLE AS $$
3230 DECLARE
3231 "current_row" "delegation_chain_row";
3232 "result" "delegation_info_type";
3233 BEGIN
3234 "result"."own_participation" := FALSE;
3235 FOR "current_row" IN
3236 SELECT * FROM "delegation_chain"(
3237 "member_id_p",
3238 "unit_id_p", "area_id_p", "issue_id_p",
3239 "simulate_trustee_id_p", "simulate_default_p")
3240 LOOP
3241 IF
3242 "result"."participating_member_id" ISNULL AND
3243 "current_row"."participation"
3244 THEN
3245 "result"."participating_member_id" := "current_row"."member_id";
3246 END IF;
3247 IF "current_row"."member_id" = "member_id_p" THEN
3248 "result"."own_participation" := "current_row"."participation";
3249 "result"."own_delegation_scope" := "current_row"."scope_out";
3250 IF "current_row"."loop" = 'first' THEN
3251 "result"."delegation_loop" := 'own';
3252 END IF;
3253 ELSIF
3254 "current_row"."member_valid" AND
3255 ( "current_row"."loop" ISNULL OR
3256 "current_row"."loop" != 'repetition' )
3257 THEN
3258 IF "result"."first_trustee_id" ISNULL THEN
3259 "result"."first_trustee_id" := "current_row"."member_id";
3260 "result"."first_trustee_participation" := "current_row"."participation";
3261 "result"."first_trustee_ellipsis" := FALSE;
3262 IF "current_row"."loop" = 'first' THEN
3263 "result"."delegation_loop" := 'first';
3264 END IF;
3265 ELSIF "result"."other_trustee_id" ISNULL THEN
3266 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
3267 "result"."other_trustee_id" := "current_row"."member_id";
3268 "result"."other_trustee_participation" := TRUE;
3269 "result"."other_trustee_ellipsis" := FALSE;
3270 IF "current_row"."loop" = 'first' THEN
3271 "result"."delegation_loop" := 'other';
3272 END IF;
3273 ELSE
3274 "result"."first_trustee_ellipsis" := TRUE;
3275 IF "current_row"."loop" = 'first' THEN
3276 "result"."delegation_loop" := 'first_ellipsis';
3277 END IF;
3278 END IF;
3279 ELSE
3280 "result"."other_trustee_ellipsis" := TRUE;
3281 IF "current_row"."loop" = 'first' THEN
3282 "result"."delegation_loop" := 'other_ellipsis';
3283 END IF;
3284 END IF;
3285 END IF;
3286 END LOOP;
3287 RETURN "result";
3288 END;
3289 $$;
3291 COMMENT ON FUNCTION "delegation_info"
3292 ( "member"."id"%TYPE,
3293 "unit"."id"%TYPE,
3294 "area"."id"%TYPE,
3295 "issue"."id"%TYPE,
3296 "member"."id"%TYPE,
3297 BOOLEAN )
3298 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
3302 ---------------------------
3303 -- Transaction isolation --
3304 ---------------------------
3307 CREATE FUNCTION "require_transaction_isolation"()
3308 RETURNS VOID
3309 LANGUAGE 'plpgsql' VOLATILE AS $$
3310 BEGIN
3311 IF
3312 current_setting('transaction_isolation') NOT IN
3313 ('repeatable read', 'serializable')
3314 THEN
3315 RAISE EXCEPTION 'Insufficient transaction isolation level' USING
3316 HINT = 'Consider using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ.';
3317 END IF;
3318 RETURN;
3319 END;
3320 $$;
3322 COMMENT ON FUNCTION "require_transaction_isolation"() IS 'Throws an exception, if transaction isolation level is too low to provide a consistent snapshot';
3325 CREATE FUNCTION "dont_require_transaction_isolation"()
3326 RETURNS VOID
3327 LANGUAGE 'plpgsql' VOLATILE AS $$
3328 BEGIN
3329 IF
3330 current_setting('transaction_isolation') IN
3331 ('repeatable read', 'serializable')
3332 THEN
3333 RAISE WARNING 'Unneccessary transaction isolation level: %',
3334 current_setting('transaction_isolation');
3335 END IF;
3336 RETURN;
3337 END;
3338 $$;
3340 COMMENT ON FUNCTION "dont_require_transaction_isolation"() IS 'Raises a warning, if transaction isolation level is higher than READ COMMITTED';
3344 -------------------------
3345 -- Notification system --
3346 -------------------------
3348 CREATE FUNCTION "get_initiatives_for_notification"
3349 ( "recipient_id_p" "member"."id"%TYPE )
3350 RETURNS SETOF "initiative_for_notification"
3351 LANGUAGE 'plpgsql' VOLATILE AS $$
3352 DECLARE
3353 "result_row" "initiative_for_notification"%ROWTYPE;
3354 "last_draft_id_v" "draft"."id"%TYPE;
3355 "last_suggestion_id_v" "suggestion"."id"%TYPE;
3356 BEGIN
3357 PERFORM "require_transaction_isolation"();
3358 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
3359 FOR "result_row" IN
3360 SELECT * FROM "initiative_for_notification"
3361 WHERE "recipient_id" = "recipient_id_p"
3362 LOOP
3363 SELECT "id" INTO "last_draft_id_v" FROM "draft"
3364 WHERE "draft"."initiative_id" = "result_row"."initiative_id"
3365 ORDER BY "id" DESC LIMIT 1;
3366 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
3367 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
3368 ORDER BY "id" DESC LIMIT 1;
3369 /* compatibility with PostgreSQL 9.1 */
3370 DELETE FROM "notification_initiative_sent"
3371 WHERE "member_id" = "recipient_id_p"
3372 AND "initiative_id" = "result_row"."initiative_id";
3373 INSERT INTO "notification_initiative_sent"
3374 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
3375 VALUES (
3376 "recipient_id_p",
3377 "result_row"."initiative_id",
3378 "last_draft_id_v",
3379 "last_suggestion_id_v" );
3380 /* TODO: use alternative code below, requires PostgreSQL 9.5 or higher
3381 INSERT INTO "notification_initiative_sent"
3382 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
3383 VALUES (
3384 "recipient_id_p",
3385 "result_row"."initiative_id",
3386 "last_draft_id_v",
3387 "last_suggestion_id_v" )
3388 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
3389 "last_draft_id" = "last_draft_id_v",
3390 "last_suggestion_id" = "last_suggestion_id_v";
3391 */
3392 RETURN NEXT "result_row";
3393 END LOOP;
3394 DELETE FROM "notification_initiative_sent"
3395 USING "initiative", "issue"
3396 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
3397 AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
3398 AND "issue"."id" = "initiative"."issue_id"
3399 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
3400 UPDATE "member" SET
3401 "notification_counter" = "notification_counter" + 1,
3402 "notification_sent" = now()
3403 WHERE "id" = "recipient_id_p";
3404 RETURN;
3405 END;
3406 $$;
3408 COMMENT ON FUNCTION "get_initiatives_for_notification"
3409 ( "member"."id"%TYPE )
3410 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';
3414 ------------------------------------------------------------------------
3415 -- Regular tasks, except calculcation of snapshots and voting results --
3416 ------------------------------------------------------------------------
3419 CREATE FUNCTION "check_activity"()
3420 RETURNS VOID
3421 LANGUAGE 'plpgsql' VOLATILE AS $$
3422 DECLARE
3423 "system_setting_row" "system_setting"%ROWTYPE;
3424 BEGIN
3425 PERFORM "dont_require_transaction_isolation"();
3426 SELECT * INTO "system_setting_row" FROM "system_setting";
3427 IF "system_setting_row"."member_ttl" NOTNULL THEN
3428 UPDATE "member" SET "active" = FALSE
3429 WHERE "active" = TRUE
3430 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
3431 END IF;
3432 RETURN;
3433 END;
3434 $$;
3436 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
3439 CREATE FUNCTION "calculate_member_counts"()
3440 RETURNS VOID
3441 LANGUAGE 'plpgsql' VOLATILE AS $$
3442 BEGIN
3443 PERFORM "require_transaction_isolation"();
3444 DELETE FROM "member_count";
3445 INSERT INTO "member_count" ("total_count")
3446 SELECT "total_count" FROM "member_count_view";
3447 UPDATE "unit" SET "member_count" = "view"."member_count"
3448 FROM "unit_member_count" AS "view"
3449 WHERE "view"."unit_id" = "unit"."id";
3450 UPDATE "area" SET
3451 "direct_member_count" = "view"."direct_member_count",
3452 "member_weight" = "view"."member_weight"
3453 FROM "area_member_count" AS "view"
3454 WHERE "view"."area_id" = "area"."id";
3455 RETURN;
3456 END;
3457 $$;
3459 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"';
3463 ------------------------------------
3464 -- Calculation of harmonic weight --
3465 ------------------------------------
3468 CREATE VIEW "remaining_harmonic_supporter_weight" AS
3469 SELECT
3470 "direct_interest_snapshot"."issue_id",
3471 "direct_interest_snapshot"."event",
3472 "direct_interest_snapshot"."member_id",
3473 "direct_interest_snapshot"."weight" AS "weight_num",
3474 count("initiative"."id") AS "weight_den"
3475 FROM "issue"
3476 JOIN "direct_interest_snapshot"
3477 ON "issue"."id" = "direct_interest_snapshot"."issue_id"
3478 AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
3479 JOIN "initiative"
3480 ON "issue"."id" = "initiative"."issue_id"
3481 AND "initiative"."harmonic_weight" ISNULL
3482 JOIN "direct_supporter_snapshot"
3483 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
3484 AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event"
3485 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
3486 AND (
3487 "direct_supporter_snapshot"."satisfied" = TRUE OR
3488 coalesce("initiative"."admitted", FALSE) = FALSE
3489 )
3490 GROUP BY
3491 "direct_interest_snapshot"."issue_id",
3492 "direct_interest_snapshot"."event",
3493 "direct_interest_snapshot"."member_id",
3494 "direct_interest_snapshot"."weight";
3496 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
3499 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
3500 SELECT
3501 "initiative"."issue_id",
3502 "initiative"."id" AS "initiative_id",
3503 "initiative"."admitted",
3504 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
3505 "remaining_harmonic_supporter_weight"."weight_den"
3506 FROM "remaining_harmonic_supporter_weight"
3507 JOIN "initiative"
3508 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
3509 AND "initiative"."harmonic_weight" ISNULL
3510 JOIN "direct_supporter_snapshot"
3511 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
3512 AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"
3513 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
3514 AND (
3515 "direct_supporter_snapshot"."satisfied" = TRUE OR
3516 coalesce("initiative"."admitted", FALSE) = FALSE
3517 )
3518 GROUP BY
3519 "initiative"."issue_id",
3520 "initiative"."id",
3521 "initiative"."admitted",
3522 "remaining_harmonic_supporter_weight"."weight_den";
3524 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
3527 CREATE VIEW "remaining_harmonic_initiative_weight_dummies" AS
3528 SELECT
3529 "issue_id",
3530 "id" AS "initiative_id",
3531 "admitted",
3532 0 AS "weight_num",
3533 1 AS "weight_den"
3534 FROM "initiative"
3535 WHERE "harmonic_weight" ISNULL;
3537 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';
3540 CREATE FUNCTION "set_harmonic_initiative_weights"
3541 ( "issue_id_p" "issue"."id"%TYPE )
3542 RETURNS VOID
3543 LANGUAGE 'plpgsql' VOLATILE AS $$
3544 DECLARE
3545 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
3546 "i" INT4;
3547 "count_v" INT4;
3548 "summand_v" FLOAT;
3549 "id_ary" INT4[];
3550 "weight_ary" FLOAT[];
3551 "min_weight_v" FLOAT;
3552 BEGIN
3553 PERFORM "require_transaction_isolation"();
3554 UPDATE "initiative" SET "harmonic_weight" = NULL
3555 WHERE "issue_id" = "issue_id_p";
3556 LOOP
3557 "min_weight_v" := NULL;
3558 "i" := 0;
3559 "count_v" := 0;
3560 FOR "weight_row" IN
3561 SELECT * FROM "remaining_harmonic_initiative_weight_summands"
3562 WHERE "issue_id" = "issue_id_p"
3563 AND (
3564 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
3565 SELECT NULL FROM "initiative"
3566 WHERE "issue_id" = "issue_id_p"
3567 AND "harmonic_weight" ISNULL
3568 AND coalesce("admitted", FALSE) = FALSE
3569 )
3570 )
3571 UNION ALL -- needed for corner cases
3572 SELECT * FROM "remaining_harmonic_initiative_weight_dummies"
3573 WHERE "issue_id" = "issue_id_p"
3574 AND (
3575 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
3576 SELECT NULL FROM "initiative"
3577 WHERE "issue_id" = "issue_id_p"
3578 AND "harmonic_weight" ISNULL
3579 AND coalesce("admitted", FALSE) = FALSE
3580 )
3581 )
3582 ORDER BY "initiative_id" DESC, "weight_den" DESC
3583 -- NOTE: non-admitted initiatives placed first (at last positions),
3584 -- latest initiatives treated worse in case of tie
3585 LOOP
3586 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
3587 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
3588 "i" := "i" + 1;
3589 "count_v" := "i";
3590 "id_ary"["i"] := "weight_row"."initiative_id";
3591 "weight_ary"["i"] := "summand_v";
3592 ELSE
3593 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
3594 END IF;
3595 END LOOP;
3596 EXIT WHEN "count_v" = 0;
3597 "i" := 1;
3598 LOOP
3599 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
3600 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
3601 "min_weight_v" := "weight_ary"["i"];
3602 END IF;
3603 "i" := "i" + 1;
3604 EXIT WHEN "i" > "count_v";
3605 END LOOP;
3606 "i" := 1;
3607 LOOP
3608 IF "weight_ary"["i"] = "min_weight_v" THEN
3609 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
3610 WHERE "id" = "id_ary"["i"];
3611 EXIT;
3612 END IF;
3613 "i" := "i" + 1;
3614 END LOOP;
3615 END LOOP;
3616 UPDATE "initiative" SET "harmonic_weight" = 0
3617 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
3618 END;
3619 $$;
3621 COMMENT ON FUNCTION "set_harmonic_initiative_weights"
3622 ( "issue"."id"%TYPE )
3623 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
3627 ------------------------------
3628 -- Calculation of snapshots --
3629 ------------------------------
3632 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
3633 ( "issue_id_p" "issue"."id"%TYPE,
3634 "member_id_p" "member"."id"%TYPE,
3635 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3636 RETURNS "direct_population_snapshot"."weight"%TYPE
3637 LANGUAGE 'plpgsql' VOLATILE AS $$
3638 DECLARE
3639 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3640 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
3641 "weight_v" INT4;
3642 "sub_weight_v" INT4;
3643 BEGIN
3644 PERFORM "require_transaction_isolation"();
3645 "weight_v" := 0;
3646 FOR "issue_delegation_row" IN
3647 SELECT * FROM "issue_delegation"
3648 WHERE "trustee_id" = "member_id_p"
3649 AND "issue_id" = "issue_id_p"
3650 LOOP
3651 IF NOT EXISTS (
3652 SELECT NULL FROM "direct_population_snapshot"
3653 WHERE "issue_id" = "issue_id_p"
3654 AND "event" = 'periodic'
3655 AND "member_id" = "issue_delegation_row"."truster_id"
3656 ) AND NOT EXISTS (
3657 SELECT NULL FROM "delegating_population_snapshot"
3658 WHERE "issue_id" = "issue_id_p"
3659 AND "event" = 'periodic'
3660 AND "member_id" = "issue_delegation_row"."truster_id"
3661 ) THEN
3662 "delegate_member_ids_v" :=
3663 "member_id_p" || "delegate_member_ids_p";
3664 INSERT INTO "delegating_population_snapshot" (
3665 "issue_id",
3666 "event",
3667 "member_id",
3668 "scope",
3669 "delegate_member_ids"
3670 ) VALUES (
3671 "issue_id_p",
3672 'periodic',
3673 "issue_delegation_row"."truster_id",
3674 "issue_delegation_row"."scope",
3675 "delegate_member_ids_v"
3676 );
3677 "sub_weight_v" := 1 +
3678 "weight_of_added_delegations_for_population_snapshot"(
3679 "issue_id_p",
3680 "issue_delegation_row"."truster_id",
3681 "delegate_member_ids_v"
3682 );
3683 UPDATE "delegating_population_snapshot"
3684 SET "weight" = "sub_weight_v"
3685 WHERE "issue_id" = "issue_id_p"
3686 AND "event" = 'periodic'
3687 AND "member_id" = "issue_delegation_row"."truster_id";
3688 "weight_v" := "weight_v" + "sub_weight_v";
3689 END IF;
3690 END LOOP;
3691 RETURN "weight_v";
3692 END;
3693 $$;
3695 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
3696 ( "issue"."id"%TYPE,
3697 "member"."id"%TYPE,
3698 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3699 IS 'Helper function for "create_population_snapshot" function';
3702 CREATE FUNCTION "create_population_snapshot"
3703 ( "issue_id_p" "issue"."id"%TYPE )
3704 RETURNS VOID
3705 LANGUAGE 'plpgsql' VOLATILE AS $$
3706 DECLARE
3707 "member_id_v" "member"."id"%TYPE;
3708 BEGIN
3709 PERFORM "require_transaction_isolation"();
3710 DELETE FROM "direct_population_snapshot"
3711 WHERE "issue_id" = "issue_id_p"
3712 AND "event" = 'periodic';
3713 DELETE FROM "delegating_population_snapshot"
3714 WHERE "issue_id" = "issue_id_p"
3715 AND "event" = 'periodic';
3716 INSERT INTO "direct_population_snapshot"
3717 ("issue_id", "event", "member_id")
3718 SELECT
3719 "issue_id_p" AS "issue_id",
3720 'periodic'::"snapshot_event" AS "event",
3721 "member"."id" AS "member_id"
3722 FROM "issue"
3723 JOIN "area" ON "issue"."area_id" = "area"."id"
3724 JOIN "membership" ON "area"."id" = "membership"."area_id"
3725 JOIN "member" ON "membership"."member_id" = "member"."id"
3726 JOIN "privilege"
3727 ON "privilege"."unit_id" = "area"."unit_id"
3728 AND "privilege"."member_id" = "member"."id"
3729 WHERE "issue"."id" = "issue_id_p"
3730 AND "member"."active" AND "privilege"."voting_right"
3731 UNION
3732 SELECT
3733 "issue_id_p" AS "issue_id",
3734 'periodic'::"snapshot_event" AS "event",
3735 "member"."id" AS "member_id"
3736 FROM "issue"
3737 JOIN "area" ON "issue"."area_id" = "area"."id"
3738 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3739 JOIN "member" ON "interest"."member_id" = "member"."id"
3740 JOIN "privilege"
3741 ON "privilege"."unit_id" = "area"."unit_id"
3742 AND "privilege"."member_id" = "member"."id"
3743 WHERE "issue"."id" = "issue_id_p"
3744 AND "member"."active" AND "privilege"."voting_right";
3745 FOR "member_id_v" IN
3746 SELECT "member_id" FROM "direct_population_snapshot"
3747 WHERE "issue_id" = "issue_id_p"
3748 AND "event" = 'periodic'
3749 LOOP
3750 UPDATE "direct_population_snapshot" SET
3751 "weight" = 1 +
3752 "weight_of_added_delegations_for_population_snapshot"(
3753 "issue_id_p",
3754 "member_id_v",
3755 '{}'
3756 )
3757 WHERE "issue_id" = "issue_id_p"
3758 AND "event" = 'periodic'
3759 AND "member_id" = "member_id_v";
3760 END LOOP;
3761 RETURN;
3762 END;
3763 $$;
3765 COMMENT ON FUNCTION "create_population_snapshot"
3766 ( "issue"."id"%TYPE )
3767 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.';
3770 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3771 ( "issue_id_p" "issue"."id"%TYPE,
3772 "member_id_p" "member"."id"%TYPE,
3773 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3774 RETURNS "direct_interest_snapshot"."weight"%TYPE
3775 LANGUAGE 'plpgsql' VOLATILE AS $$
3776 DECLARE
3777 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3778 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
3779 "weight_v" INT4;
3780 "sub_weight_v" INT4;
3781 BEGIN
3782 PERFORM "require_transaction_isolation"();
3783 "weight_v" := 0;
3784 FOR "issue_delegation_row" IN
3785 SELECT * FROM "issue_delegation"
3786 WHERE "trustee_id" = "member_id_p"
3787 AND "issue_id" = "issue_id_p"
3788 LOOP
3789 IF NOT EXISTS (
3790 SELECT NULL FROM "direct_interest_snapshot"
3791 WHERE "issue_id" = "issue_id_p"
3792 AND "event" = 'periodic'
3793 AND "member_id" = "issue_delegation_row"."truster_id"
3794 ) AND NOT EXISTS (
3795 SELECT NULL FROM "delegating_interest_snapshot"
3796 WHERE "issue_id" = "issue_id_p"
3797 AND "event" = 'periodic'
3798 AND "member_id" = "issue_delegation_row"."truster_id"
3799 ) THEN
3800 "delegate_member_ids_v" :=
3801 "member_id_p" || "delegate_member_ids_p";
3802 INSERT INTO "delegating_interest_snapshot" (
3803 "issue_id",
3804 "event",
3805 "member_id",
3806 "scope",
3807 "delegate_member_ids"
3808 ) VALUES (
3809 "issue_id_p",
3810 'periodic',
3811 "issue_delegation_row"."truster_id",
3812 "issue_delegation_row"."scope",
3813 "delegate_member_ids_v"
3814 );
3815 "sub_weight_v" := 1 +
3816 "weight_of_added_delegations_for_interest_snapshot"(
3817 "issue_id_p",
3818 "issue_delegation_row"."truster_id",
3819 "delegate_member_ids_v"
3820 );
3821 UPDATE "delegating_interest_snapshot"
3822 SET "weight" = "sub_weight_v"
3823 WHERE "issue_id" = "issue_id_p"
3824 AND "event" = 'periodic'
3825 AND "member_id" = "issue_delegation_row"."truster_id";
3826 "weight_v" := "weight_v" + "sub_weight_v";
3827 END IF;
3828 END LOOP;
3829 RETURN "weight_v";
3830 END;
3831 $$;
3833 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3834 ( "issue"."id"%TYPE,
3835 "member"."id"%TYPE,
3836 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3837 IS 'Helper function for "create_interest_snapshot" function';
3840 CREATE FUNCTION "create_interest_snapshot"
3841 ( "issue_id_p" "issue"."id"%TYPE )
3842 RETURNS VOID
3843 LANGUAGE 'plpgsql' VOLATILE AS $$
3844 DECLARE
3845 "member_id_v" "member"."id"%TYPE;
3846 BEGIN
3847 PERFORM "require_transaction_isolation"();
3848 DELETE FROM "direct_interest_snapshot"
3849 WHERE "issue_id" = "issue_id_p"
3850 AND "event" = 'periodic';
3851 DELETE FROM "delegating_interest_snapshot"
3852 WHERE "issue_id" = "issue_id_p"
3853 AND "event" = 'periodic';
3854 DELETE FROM "direct_supporter_snapshot"
3855 USING "initiative" -- NOTE: due to missing index on issue_id
3856 WHERE "initiative"."issue_id" = "issue_id_p"
3857 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
3858 AND "direct_supporter_snapshot"."event" = 'periodic';
3859 INSERT INTO "direct_interest_snapshot"
3860 ("issue_id", "event", "member_id")
3861 SELECT
3862 "issue_id_p" AS "issue_id",
3863 'periodic' AS "event",
3864 "member"."id" AS "member_id"
3865 FROM "issue"
3866 JOIN "area" ON "issue"."area_id" = "area"."id"
3867 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3868 JOIN "member" ON "interest"."member_id" = "member"."id"
3869 JOIN "privilege"
3870 ON "privilege"."unit_id" = "area"."unit_id"
3871 AND "privilege"."member_id" = "member"."id"
3872 WHERE "issue"."id" = "issue_id_p"
3873 AND "member"."active" AND "privilege"."voting_right";
3874 FOR "member_id_v" IN
3875 SELECT "member_id" FROM "direct_interest_snapshot"
3876 WHERE "issue_id" = "issue_id_p"
3877 AND "event" = 'periodic'
3878 LOOP
3879 UPDATE "direct_interest_snapshot" SET
3880 "weight" = 1 +
3881 "weight_of_added_delegations_for_interest_snapshot"(
3882 "issue_id_p",
3883 "member_id_v",
3884 '{}'
3885 )
3886 WHERE "issue_id" = "issue_id_p"
3887 AND "event" = 'periodic'
3888 AND "member_id" = "member_id_v";
3889 END LOOP;
3890 INSERT INTO "direct_supporter_snapshot"
3891 ( "issue_id", "initiative_id", "event", "member_id",
3892 "draft_id", "informed", "satisfied" )
3893 SELECT
3894 "issue_id_p" AS "issue_id",
3895 "initiative"."id" AS "initiative_id",
3896 'periodic' AS "event",
3897 "supporter"."member_id" AS "member_id",
3898 "supporter"."draft_id" AS "draft_id",
3899 "supporter"."draft_id" = "current_draft"."id" AS "informed",
3900 NOT EXISTS (
3901 SELECT NULL FROM "critical_opinion"
3902 WHERE "initiative_id" = "initiative"."id"
3903 AND "member_id" = "supporter"."member_id"
3904 ) AS "satisfied"
3905 FROM "initiative"
3906 JOIN "supporter"
3907 ON "supporter"."initiative_id" = "initiative"."id"
3908 JOIN "current_draft"
3909 ON "initiative"."id" = "current_draft"."initiative_id"
3910 JOIN "direct_interest_snapshot"
3911 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
3912 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
3913 AND "event" = 'periodic'
3914 WHERE "initiative"."issue_id" = "issue_id_p";
3915 RETURN;
3916 END;
3917 $$;
3919 COMMENT ON FUNCTION "create_interest_snapshot"
3920 ( "issue"."id"%TYPE )
3921 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.';
3924 CREATE FUNCTION "create_snapshot"
3925 ( "issue_id_p" "issue"."id"%TYPE )
3926 RETURNS VOID
3927 LANGUAGE 'plpgsql' VOLATILE AS $$
3928 DECLARE
3929 "initiative_id_v" "initiative"."id"%TYPE;
3930 "suggestion_id_v" "suggestion"."id"%TYPE;
3931 BEGIN
3932 PERFORM "require_transaction_isolation"();
3933 PERFORM "create_population_snapshot"("issue_id_p");
3934 PERFORM "create_interest_snapshot"("issue_id_p");
3935 UPDATE "issue" SET
3936 "snapshot" = coalesce("phase_finished", now()),
3937 "latest_snapshot_event" = 'periodic',
3938 "population" = (
3939 SELECT coalesce(sum("weight"), 0)
3940 FROM "direct_population_snapshot"
3941 WHERE "issue_id" = "issue_id_p"
3942 AND "event" = 'periodic'
3943 )
3944 WHERE "id" = "issue_id_p";
3945 FOR "initiative_id_v" IN
3946 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
3947 LOOP
3948 UPDATE "initiative" SET
3949 "supporter_count" = (
3950 SELECT coalesce(sum("di"."weight"), 0)
3951 FROM "direct_interest_snapshot" AS "di"
3952 JOIN "direct_supporter_snapshot" AS "ds"
3953 ON "di"."member_id" = "ds"."member_id"
3954 WHERE "di"."issue_id" = "issue_id_p"
3955 AND "di"."event" = 'periodic'
3956 AND "ds"."initiative_id" = "initiative_id_v"
3957 AND "ds"."event" = 'periodic'
3958 ),
3959 "informed_supporter_count" = (
3960 SELECT coalesce(sum("di"."weight"), 0)
3961 FROM "direct_interest_snapshot" AS "di"
3962 JOIN "direct_supporter_snapshot" AS "ds"
3963 ON "di"."member_id" = "ds"."member_id"
3964 WHERE "di"."issue_id" = "issue_id_p"
3965 AND "di"."event" = 'periodic'
3966 AND "ds"."initiative_id" = "initiative_id_v"
3967 AND "ds"."event" = 'periodic'
3968 AND "ds"."informed"
3969 ),
3970 "satisfied_supporter_count" = (
3971 SELECT coalesce(sum("di"."weight"), 0)
3972 FROM "direct_interest_snapshot" AS "di"
3973 JOIN "direct_supporter_snapshot" AS "ds"
3974 ON "di"."member_id" = "ds"."member_id"
3975 WHERE "di"."issue_id" = "issue_id_p"
3976 AND "di"."event" = 'periodic'
3977 AND "ds"."initiative_id" = "initiative_id_v"
3978 AND "ds"."event" = 'periodic'
3979 AND "ds"."satisfied"
3980 ),
3981 "satisfied_informed_supporter_count" = (
3982 SELECT coalesce(sum("di"."weight"), 0)
3983 FROM "direct_interest_snapshot" AS "di"
3984 JOIN "direct_supporter_snapshot" AS "ds"
3985 ON "di"."member_id" = "ds"."member_id"
3986 WHERE "di"."issue_id" = "issue_id_p"
3987 AND "di"."event" = 'periodic'
3988 AND "ds"."initiative_id" = "initiative_id_v"
3989 AND "ds"."event" = 'periodic'
3990 AND "ds"."informed"
3991 AND "ds"."satisfied"
3992 )
3993 WHERE "id" = "initiative_id_v";
3994 FOR "suggestion_id_v" IN
3995 SELECT "id" FROM "suggestion"
3996 WHERE "initiative_id" = "initiative_id_v"
3997 LOOP
3998 UPDATE "suggestion" SET
3999 "minus2_unfulfilled_count" = (
4000 SELECT coalesce(sum("snapshot"."weight"), 0)
4001 FROM "issue" CROSS JOIN "opinion"
4002 JOIN "direct_interest_snapshot" AS "snapshot"
4003 ON "snapshot"."issue_id" = "issue"."id"
4004 AND "snapshot"."event" = "issue"."latest_snapshot_event"
4005 AND "snapshot"."member_id" = "opinion"."member_id"
4006 WHERE "issue"."id" = "issue_id_p"
4007 AND "opinion"."suggestion_id" = "suggestion_id_v"
4008 AND "opinion"."degree" = -2
4009 AND "opinion"."fulfilled" = FALSE
4010 ),
4011 "minus2_fulfilled_count" = (
4012 SELECT coalesce(sum("snapshot"."weight"), 0)
4013 FROM "issue" CROSS JOIN "opinion"
4014 JOIN "direct_interest_snapshot" AS "snapshot"
4015 ON "snapshot"."issue_id" = "issue"."id"
4016 AND "snapshot"."event" = "issue"."latest_snapshot_event"
4017 AND "snapshot"."member_id" = "opinion"."member_id"
4018 WHERE "issue"."id" = "issue_id_p"
4019 AND "opinion"."suggestion_id" = "suggestion_id_v"
4020 AND "opinion"."degree" = -2
4021 AND "opinion"."fulfilled" = TRUE
4022 ),
4023 "minus1_unfulfilled_count" = (
4024 SELECT coalesce(sum("snapshot"."weight"), 0)
4025 FROM "issue" CROSS JOIN "opinion"
4026 JOIN "direct_interest_snapshot" AS "snapshot"
4027 ON "snapshot"."issue_id" = "issue"."id"
4028 AND "snapshot"."event" = "issue"."latest_snapshot_event"
4029 AND "snapshot"."member_id" = "opinion"."member_id"
4030 WHERE "issue"."id" = "issue_id_p"
4031 AND "opinion"."suggestion_id" = "suggestion_id_v"
4032 AND "opinion"."degree" = -1
4033 AND "opinion"."fulfilled" = FALSE
4034 ),
4035 "minus1_fulfilled_count" = (
4036 SELECT coalesce(sum("snapshot"."weight"), 0)
4037 FROM "issue" CROSS JOIN "opinion"
4038 JOIN "direct_interest_snapshot" AS "snapshot"
4039 ON "snapshot"."issue_id" = "issue"."id"
4040 AND "snapshot"."event" = "issue"."latest_snapshot_event"
4041 AND "snapshot"."member_id" = "opinion"."member_id"
4042 WHERE "issue"."id" = "issue_id_p"
4043 AND "opinion"."suggestion_id" = "suggestion_id_v"
4044 AND "opinion"."degree" = -1
4045 AND "opinion"."fulfilled" = TRUE
4046 ),
4047 "plus1_unfulfilled_count" = (
4048 SELECT coalesce(sum("snapshot"."weight"), 0)
4049 FROM "issue" CROSS JOIN "opinion"
4050 JOIN "direct_interest_snapshot" AS "snapshot"
4051 ON "snapshot"."issue_id" = "issue"."id"
4052 AND "snapshot"."event" = "issue"."latest_snapshot_event"
4053 AND "snapshot"."member_id" = "opinion"."member_id"
4054 WHERE "issue"."id" = "issue_id_p"
4055 AND "opinion"."suggestion_id" = "suggestion_id_v"
4056 AND "opinion"."degree" = 1
4057 AND "opinion"."fulfilled" = FALSE
4058 ),
4059 "plus1_fulfilled_count" = (
4060 SELECT coalesce(sum("snapshot"."weight"), 0)
4061 FROM "issue" CROSS JOIN "opinion"
4062 JOIN "direct_interest_snapshot" AS "snapshot"
4063 ON "snapshot"."issue_id" = "issue"."id"
4064 AND "snapshot"."event" = "issue"."latest_snapshot_event"
4065 AND "snapshot"."member_id" = "opinion"."member_id"
4066 WHERE "issue"."id" = "issue_id_p"
4067 AND "opinion"."suggestion_id" = "suggestion_id_v"
4068 AND "opinion"."degree" = 1
4069 AND "opinion"."fulfilled" = TRUE
4070 ),
4071 "plus2_unfulfilled_count" = (
4072 SELECT coalesce(sum("snapshot"."weight"), 0)
4073 FROM "issue" CROSS JOIN "opinion"
4074 JOIN "direct_interest_snapshot" AS "snapshot"
4075 ON "snapshot"."issue_id" = "issue"."id"
4076 AND "snapshot"."event" = "issue"."latest_snapshot_event"
4077 AND "snapshot"."member_id" = "opinion"."member_id"
4078 WHERE "issue"."id" = "issue_id_p"
4079 AND "opinion"."suggestion_id" = "suggestion_id_v"
4080 AND "opinion"."degree" = 2
4081 AND "opinion"."fulfilled" = FALSE
4082 ),
4083 "plus2_fulfilled_count" = (
4084 SELECT coalesce(sum("snapshot"."weight"), 0)
4085 FROM "issue" CROSS JOIN "opinion"
4086 JOIN "direct_interest_snapshot" AS "snapshot"
4087 ON "snapshot"."issue_id" = "issue"."id"
4088 AND "snapshot"."event" = "issue"."latest_snapshot_event"
4089 AND "snapshot"."member_id" = "opinion"."member_id"
4090 WHERE "issue"."id" = "issue_id_p"
4091 AND "opinion"."suggestion_id" = "suggestion_id_v"
4092 AND "opinion"."degree" = 2
4093 AND "opinion"."fulfilled" = TRUE
4094 )
4095 WHERE "suggestion"."id" = "suggestion_id_v";
4096 END LOOP;
4097 END LOOP;
4098 RETURN;
4099 END;
4100 $$;
4102 COMMENT ON FUNCTION "create_snapshot"
4103 ( "issue"."id"%TYPE )
4104 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.';
4107 CREATE FUNCTION "set_snapshot_event"
4108 ( "issue_id_p" "issue"."id"%TYPE,
4109 "event_p" "snapshot_event" )
4110 RETURNS VOID
4111 LANGUAGE 'plpgsql' VOLATILE AS $$
4112 DECLARE
4113 "event_v" "issue"."latest_snapshot_event"%TYPE;
4114 BEGIN
4115 PERFORM "require_transaction_isolation"();
4116 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
4117 WHERE "id" = "issue_id_p" FOR UPDATE;
4118 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
4119 WHERE "id" = "issue_id_p";
4120 UPDATE "direct_population_snapshot" SET "event" = "event_p"
4121 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
4122 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
4123 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
4124 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
4125 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
4126 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
4127 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
4128 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
4129 FROM "initiative" -- NOTE: due to missing index on issue_id
4130 WHERE "initiative"."issue_id" = "issue_id_p"
4131 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
4132 AND "direct_supporter_snapshot"."event" = "event_v";
4133 RETURN;
4134 END;
4135 $$;
4137 COMMENT ON FUNCTION "set_snapshot_event"
4138 ( "issue"."id"%TYPE,
4139 "snapshot_event" )
4140 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
4144 -----------------------
4145 -- Counting of votes --
4146 -----------------------
4149 CREATE FUNCTION "weight_of_added_vote_delegations"
4150 ( "issue_id_p" "issue"."id"%TYPE,
4151 "member_id_p" "member"."id"%TYPE,
4152 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
4153 RETURNS "direct_voter"."weight"%TYPE
4154 LANGUAGE 'plpgsql' VOLATILE AS $$
4155 DECLARE
4156 "issue_delegation_row" "issue_delegation"%ROWTYPE;
4157 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
4158 "weight_v" INT4;
4159 "sub_weight_v" INT4;
4160 BEGIN
4161 PERFORM "require_transaction_isolation"();
4162 "weight_v" := 0;
4163 FOR "issue_delegation_row" IN
4164 SELECT * FROM "issue_delegation"
4165 WHERE "trustee_id" = "member_id_p"
4166 AND "issue_id" = "issue_id_p"
4167 LOOP
4168 IF NOT EXISTS (
4169 SELECT NULL FROM "direct_voter"
4170 WHERE "member_id" = "issue_delegation_row"."truster_id"
4171 AND "issue_id" = "issue_id_p"
4172 ) AND NOT EXISTS (
4173 SELECT NULL FROM "delegating_voter"
4174 WHERE "member_id" = "issue_delegation_row"."truster_id"
4175 AND "issue_id" = "issue_id_p"
4176 ) THEN
4177 "delegate_member_ids_v" :=
4178 "member_id_p" || "delegate_member_ids_p";
4179 INSERT INTO "delegating_voter" (
4180 "issue_id",
4181 "member_id",
4182 "scope",
4183 "delegate_member_ids"
4184 ) VALUES (
4185 "issue_id_p",
4186 "issue_delegation_row"."truster_id",
4187 "issue_delegation_row"."scope",
4188 "delegate_member_ids_v"
4189 );
4190 "sub_weight_v" := 1 +
4191 "weight_of_added_vote_delegations"(
4192 "issue_id_p",
4193 "issue_delegation_row"."truster_id",
4194 "delegate_member_ids_v"
4195 );
4196 UPDATE "delegating_voter"
4197 SET "weight" = "sub_weight_v"
4198 WHERE "issue_id" = "issue_id_p"
4199 AND "member_id" = "issue_delegation_row"."truster_id";
4200 "weight_v" := "weight_v" + "sub_weight_v";
4201 END IF;
4202 END LOOP;
4203 RETURN "weight_v";
4204 END;
4205 $$;
4207 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
4208 ( "issue"."id"%TYPE,
4209 "member"."id"%TYPE,
4210 "delegating_voter"."delegate_member_ids"%TYPE )
4211 IS 'Helper function for "add_vote_delegations" function';
4214 CREATE FUNCTION "add_vote_delegations"
4215 ( "issue_id_p" "issue"."id"%TYPE )
4216 RETURNS VOID
4217 LANGUAGE 'plpgsql' VOLATILE AS $$
4218 DECLARE
4219 "member_id_v" "member"."id"%TYPE;
4220 BEGIN
4221 PERFORM "require_transaction_isolation"();
4222 FOR "member_id_v" IN
4223 SELECT "member_id" FROM "direct_voter"
4224 WHERE "issue_id" = "issue_id_p"
4225 LOOP
4226 UPDATE "direct_voter" SET
4227 "weight" = "weight" + "weight_of_added_vote_delegations"(
4228 "issue_id_p",
4229 "member_id_v",
4230 '{}'
4231 )
4232 WHERE "member_id" = "member_id_v"
4233 AND "issue_id" = "issue_id_p";
4234 END LOOP;
4235 RETURN;
4236 END;
4237 $$;
4239 COMMENT ON FUNCTION "add_vote_delegations"
4240 ( "issue_id_p" "issue"."id"%TYPE )
4241 IS 'Helper function for "close_voting" function';
4244 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
4245 RETURNS VOID
4246 LANGUAGE 'plpgsql' VOLATILE AS $$
4247 DECLARE
4248 "area_id_v" "area"."id"%TYPE;
4249 "unit_id_v" "unit"."id"%TYPE;
4250 "member_id_v" "member"."id"%TYPE;
4251 BEGIN
4252 PERFORM "require_transaction_isolation"();
4253 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
4254 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
4255 -- override protection triggers:
4256 INSERT INTO "temporary_transaction_data" ("key", "value")
4257 VALUES ('override_protection_triggers', TRUE::TEXT);
4258 -- delete timestamp of voting comment:
4259 UPDATE "direct_voter" SET "comment_changed" = NULL
4260 WHERE "issue_id" = "issue_id_p";
4261 -- delete delegating votes (in cases of manual reset of issue state):
4262 DELETE FROM "delegating_voter"
4263 WHERE "issue_id" = "issue_id_p";
4264 -- delete votes from non-privileged voters:
4265 DELETE FROM "direct_voter"
4266 USING (
4267 SELECT
4268 "direct_voter"."member_id"
4269 FROM "direct_voter"
4270 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
4271 LEFT JOIN "privilege"
4272 ON "privilege"."unit_id" = "unit_id_v"
4273 AND "privilege"."member_id" = "direct_voter"."member_id"
4274 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
4275 "member"."active" = FALSE OR
4276 "privilege"."voting_right" ISNULL OR
4277 "privilege"."voting_right" = FALSE
4278 )
4279 ) AS "subquery"
4280 WHERE "direct_voter"."issue_id" = "issue_id_p"
4281 AND "direct_voter"."member_id" = "subquery"."member_id";
4282 -- consider delegations:
4283 UPDATE "direct_voter" SET "weight" = 1
4284 WHERE "issue_id" = "issue_id_p";
4285 PERFORM "add_vote_delegations"("issue_id_p");
4286 -- mark first preferences:
4287 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
4288 FROM (
4289 SELECT
4290 "vote"."initiative_id",
4291 "vote"."member_id",
4292 CASE WHEN "vote"."grade" > 0 THEN
4293 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
4294 ELSE NULL
4295 END AS "first_preference"
4296 FROM "vote"
4297 JOIN "initiative" -- NOTE: due to missing index on issue_id
4298 ON "vote"."issue_id" = "initiative"."issue_id"
4299 JOIN "vote" AS "agg"
4300 ON "initiative"."id" = "agg"."initiative_id"
4301 AND "vote"."member_id" = "agg"."member_id"
4302 GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
4303 ) AS "subquery"
4304 WHERE "vote"."issue_id" = "issue_id_p"
4305 AND "vote"."initiative_id" = "subquery"."initiative_id"
4306 AND "vote"."member_id" = "subquery"."member_id";
4307 -- finish overriding protection triggers (avoids garbage):
4308 DELETE FROM "temporary_transaction_data"
4309 WHERE "key" = 'override_protection_triggers';
4310 -- materialize battle_view:
4311 -- NOTE: "closed" column of issue must be set at this point
4312 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
4313 INSERT INTO "battle" (
4314 "issue_id",
4315 "winning_initiative_id", "losing_initiative_id",
4316 "count"
4317 ) SELECT
4318 "issue_id",
4319 "winning_initiative_id", "losing_initiative_id",
4320 "count"
4321 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
4322 -- set voter count:
4323 UPDATE "issue" SET
4324 "voter_count" = (
4325 SELECT coalesce(sum("weight"), 0)
4326 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
4327 )
4328 WHERE "id" = "issue_id_p";
4329 -- copy "positive_votes" and "negative_votes" from "battle" table:
4330 -- NOTE: "first_preference_votes" is set to a default of 0 at this step
4331 UPDATE "initiative" SET
4332 "first_preference_votes" = 0,
4333 "positive_votes" = "battle_win"."count",
4334 "negative_votes" = "battle_lose"."count"
4335 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
4336 WHERE
4337 "battle_win"."issue_id" = "issue_id_p" AND
4338 "battle_win"."winning_initiative_id" = "initiative"."id" AND
4339 "battle_win"."losing_initiative_id" ISNULL AND
4340 "battle_lose"."issue_id" = "issue_id_p" AND
4341 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
4342 "battle_lose"."winning_initiative_id" ISNULL;
4343 -- calculate "first_preference_votes":
4344 -- NOTE: will only set values not equal to zero
4345 UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum"
4346 FROM (
4347 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
4348 FROM "vote" JOIN "direct_voter"
4349 ON "vote"."issue_id" = "direct_voter"."issue_id"
4350 AND "vote"."member_id" = "direct_voter"."member_id"
4351 WHERE "vote"."first_preference"
4352 GROUP BY "vote"."initiative_id"
4353 ) AS "subquery"
4354 WHERE "initiative"."issue_id" = "issue_id_p"
4355 AND "initiative"."admitted"
4356 AND "initiative"."id" = "subquery"."initiative_id";
4357 END;
4358 $$;
4360 COMMENT ON FUNCTION "close_voting"
4361 ( "issue"."id"%TYPE )
4362 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.';
4365 CREATE FUNCTION "defeat_strength"
4366 ( "positive_votes_p" INT4,
4367 "negative_votes_p" INT4,
4368 "defeat_strength_p" "defeat_strength" )
4369 RETURNS INT8
4370 LANGUAGE 'plpgsql' IMMUTABLE AS $$
4371 BEGIN
4372 IF "defeat_strength_p" = 'simple'::"defeat_strength" THEN
4373 IF "positive_votes_p" > "negative_votes_p" THEN
4374 RETURN "positive_votes_p";
4375 ELSE
4376 RETURN 0;
4377 END IF;
4378 ELSE
4379 IF "positive_votes_p" > "negative_votes_p" THEN
4380 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
4381 ELSIF "positive_votes_p" = "negative_votes_p" THEN
4382 RETURN 0;
4383 ELSE
4384 RETURN -1;
4385 END IF;
4386 END IF;
4387 END;
4388 $$;
4390 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")';
4393 CREATE FUNCTION "secondary_link_strength"
4394 ( "initiative1_ord_p" INT4,
4395 "initiative2_ord_p" INT4,
4396 "tie_breaking_p" "tie_breaking" )
4397 RETURNS INT8
4398 LANGUAGE 'plpgsql' IMMUTABLE AS $$
4399 BEGIN
4400 IF "initiative1_ord_p" = "initiative2_ord_p" THEN
4401 RAISE EXCEPTION 'Identical initiative ids passed to "secondary_link_strength" function (should not happen)';
4402 END IF;
4403 RETURN (
4404 CASE WHEN "tie_breaking_p" = 'simple'::"tie_breaking" THEN
4405 0
4406 ELSE
4407 CASE WHEN "initiative1_ord_p" < "initiative2_ord_p" THEN
4408 1::INT8 << 62
4409 ELSE 0 END
4410 +
4411 CASE WHEN "tie_breaking_p" = 'variant2'::"tie_breaking" THEN
4412 ("initiative2_ord_p"::INT8 << 31) - "initiative1_ord_p"::INT8
4413 ELSE
4414 "initiative2_ord_p"::INT8 - ("initiative1_ord_p"::INT8 << 31)
4415 END
4416 END
4417 );
4418 END;
4419 $$;
4421 COMMENT ON FUNCTION "secondary_link_strength"(INT4, INT4, "tie_breaking") IS 'Calculates a secondary criterion for the defeat strength (tie-breaking of the links)';
4424 CREATE TYPE "link_strength" AS (
4425 "primary" INT8,
4426 "secondary" INT8 );
4428 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'')';
4431 CREATE FUNCTION "find_best_paths"("matrix_d" "link_strength"[][])
4432 RETURNS "link_strength"[][]
4433 LANGUAGE 'plpgsql' IMMUTABLE AS $$
4434 DECLARE
4435 "dimension_v" INT4;
4436 "matrix_p" "link_strength"[][];
4437 "i" INT4;
4438 "j" INT4;
4439 "k" INT4;
4440 BEGIN
4441 "dimension_v" := array_upper("matrix_d", 1);
4442 "matrix_p" := "matrix_d";
4443 "i" := 1;
4444 LOOP
4445 "j" := 1;
4446 LOOP
4447 IF "i" != "j" THEN
4448 "k" := 1;
4449 LOOP
4450 IF "i" != "k" AND "j" != "k" THEN
4451 IF "matrix_p"["j"]["i"] < "matrix_p"["i"]["k"] THEN
4452 IF "matrix_p"["j"]["i"] > "matrix_p"["j"]["k"] THEN
4453 "matrix_p"["j"]["k"] := "matrix_p"["j"]["i"];
4454 END IF;
4455 ELSE
4456 IF "matrix_p"["i"]["k"] > "matrix_p"["j"]["k"] THEN
4457 "matrix_p"["j"]["k"] := "matrix_p"["i"]["k"];
4458 END IF;
4459 END IF;
4460 END IF;
4461 EXIT WHEN "k" = "dimension_v";
4462 "k" := "k" + 1;
4463 END LOOP;
4464 END IF;
4465 EXIT WHEN "j" = "dimension_v";
4466 "j" := "j" + 1;
4467 END LOOP;
4468 EXIT WHEN "i" = "dimension_v";
4469 "i" := "i" + 1;
4470 END LOOP;
4471 RETURN "matrix_p";
4472 END;
4473 $$;
4475 COMMENT ON FUNCTION "find_best_paths"("link_strength"[][]) IS 'Computes the strengths of the best beat-paths from a square matrix';
4478 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
4479 RETURNS VOID
4480 LANGUAGE 'plpgsql' VOLATILE AS $$
4481 DECLARE
4482 "issue_row" "issue"%ROWTYPE;
4483 "policy_row" "policy"%ROWTYPE;
4484 "dimension_v" INT4;
4485 "matrix_a" INT4[][]; -- absolute votes
4486 "matrix_d" "link_strength"[][]; -- defeat strength (direct)
4487 "matrix_p" "link_strength"[][]; -- defeat strength (best path)
4488 "matrix_t" "link_strength"[][]; -- defeat strength (tie-breaking)
4489 "matrix_f" BOOLEAN[][]; -- forbidden link (tie-breaking)
4490 "matrix_b" BOOLEAN[][]; -- final order (who beats who)
4491 "i" INT4;
4492 "j" INT4;
4493 "m" INT4;
4494 "n" INT4;
4495 "battle_row" "battle"%ROWTYPE;
4496 "rank_ary" INT4[];
4497 "rank_v" INT4;
4498 "initiative_id_v" "initiative"."id"%TYPE;
4499 BEGIN
4500 PERFORM "require_transaction_isolation"();
4501 SELECT * INTO "issue_row"
4502 FROM "issue" WHERE "id" = "issue_id_p";
4503 SELECT * INTO "policy_row"
4504 FROM "policy" WHERE "id" = "issue_row"."policy_id";
4505 SELECT count(1) INTO "dimension_v"
4506 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
4507 -- create "matrix_a" with absolute number of votes in pairwise
4508 -- comparison:
4509 "matrix_a" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
4510 "i" := 1;
4511 "j" := 2;
4512 FOR "battle_row" IN
4513 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
4514 ORDER BY
4515 "winning_initiative_id" NULLS FIRST,
4516 "losing_initiative_id" NULLS FIRST
4517 LOOP
4518 "matrix_a"["i"]["j"] := "battle_row"."count";
4519 IF "j" = "dimension_v" THEN
4520 "i" := "i" + 1;
4521 "j" := 1;
4522 ELSE
4523 "j" := "j" + 1;
4524 IF "j" = "i" THEN
4525 "j" := "j" + 1;
4526 END IF;
4527 END IF;
4528 END LOOP;
4529 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
4530 RAISE EXCEPTION 'Wrong battle count (should not happen)';
4531 END IF;
4532 -- store direct defeat strengths in "matrix_d" using "defeat_strength"
4533 -- and "secondary_link_strength" functions:
4534 "matrix_d" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
4535 "i" := 1;
4536 LOOP
4537 "j" := 1;
4538 LOOP
4539 IF "i" != "j" THEN
4540 "matrix_d"["i"]["j"] := (
4541 "defeat_strength"(
4542 "matrix_a"["i"]["j"],
4543 "matrix_a"["j"]["i"],
4544 "policy_row"."defeat_strength"
4545 ),
4546 "secondary_link_strength"(
4547 "i",
4548 "j",
4549 "policy_row"."tie_breaking"
4550 )
4551 )::"link_strength";
4552 END IF;
4553 EXIT WHEN "j" = "dimension_v";
4554 "j" := "j" + 1;
4555 END LOOP;
4556 EXIT WHEN "i" = "dimension_v";
4557 "i" := "i" + 1;
4558 END LOOP;
4559 -- find best paths:
4560 "matrix_p" := "find_best_paths"("matrix_d");
4561 -- create partial order:
4562 "matrix_b" := array_fill(NULL::BOOLEAN, ARRAY["dimension_v", "dimension_v"]);
4563 "i" := 1;
4564 LOOP
4565 "j" := "i" + 1;
4566 LOOP
4567 IF "i" != "j" THEN
4568 IF "matrix_p"["i"]["j"] > "matrix_p"["j"]["i"] THEN
4569 "matrix_b"["i"]["j"] := TRUE;
4570 "matrix_b"["j"]["i"] := FALSE;
4571 ELSIF "matrix_p"["i"]["j"] < "matrix_p"["j"]["i"] THEN
4572 "matrix_b"["i"]["j"] := FALSE;
4573 "matrix_b"["j"]["i"] := TRUE;
4574 END IF;
4575 END IF;
4576 EXIT WHEN "j" = "dimension_v";
4577 "j" := "j" + 1;
4578 END LOOP;
4579 EXIT WHEN "i" = "dimension_v" - 1;
4580 "i" := "i" + 1;
4581 END LOOP;
4582 -- tie-breaking by forbidding shared weakest links in beat-paths
4583 -- (unless "tie_breaking" is set to 'simple', in which case tie-breaking
4584 -- is performed later by initiative id):
4585 IF "policy_row"."tie_breaking" != 'simple'::"tie_breaking" THEN
4586 "m" := 1;
4587 LOOP
4588 "n" := "m" + 1;
4589 LOOP
4590 -- only process those candidates m and n, which are tied:
4591 IF "matrix_b"["m"]["n"] ISNULL THEN
4592 -- start with beat-paths prior tie-breaking:
4593 "matrix_t" := "matrix_p";
4594 -- start with all links allowed:
4595 "matrix_f" := array_fill(FALSE, ARRAY["dimension_v", "dimension_v"]);
4596 LOOP
4597 -- determine (and forbid) that link that is the weakest link
4598 -- in both the best path from candidate m to candidate n and
4599 -- from candidate n to candidate m:
4600 "i" := 1;
4601 <<forbid_one_link>>
4602 LOOP
4603 "j" := 1;
4604 LOOP
4605 IF "i" != "j" THEN
4606 IF "matrix_d"["i"]["j"] = "matrix_t"["m"]["n"] THEN
4607 "matrix_f"["i"]["j"] := TRUE;
4608 -- exit for performance reasons,
4609 -- as exactly one link will be found:
4610 EXIT forbid_one_link;
4611 END IF;
4612 END IF;
4613 EXIT WHEN "j" = "dimension_v";
4614 "j" := "j" + 1;
4615 END LOOP;
4616 IF "i" = "dimension_v" THEN
4617 RAISE EXCEPTION 'Did not find shared weakest link for tie-breaking (should not happen)';
4618 END IF;
4619 "i" := "i" + 1;
4620 END LOOP;
4621 -- calculate best beat-paths while ignoring forbidden links:
4622 "i" := 1;
4623 LOOP
4624 "j" := 1;
4625 LOOP
4626 IF "i" != "j" THEN
4627 "matrix_t"["i"]["j"] := CASE
4628 WHEN "matrix_f"["i"]["j"]
4629 THEN ((-1::INT8) << 63, 0)::"link_strength" -- worst possible value
4630 ELSE "matrix_d"["i"]["j"] END;
4631 END IF;
4632 EXIT WHEN "j" = "dimension_v";
4633 "j" := "j" + 1;
4634 END LOOP;
4635 EXIT WHEN "i" = "dimension_v";
4636 "i" := "i" + 1;
4637 END LOOP;
4638 "matrix_t" := "find_best_paths"("matrix_t");
4639 -- extend partial order, if tie-breaking was successful:
4640 IF "matrix_t"["m"]["n"] > "matrix_t"["n"]["m"] THEN
4641 "matrix_b"["m"]["n"] := TRUE;
4642 "matrix_b"["n"]["m"] := FALSE;
4643 EXIT;
4644 ELSIF "matrix_t"["m"]["n"] < "matrix_t"["n"]["m"] THEN
4645 "matrix_b"["m"]["n"] := FALSE;
4646 "matrix_b"["n"]["m"] := TRUE;
4647 EXIT;
4648 END IF;
4649 END LOOP;
4650 END IF;
4651 EXIT WHEN "n" = "dimension_v";
4652 "n" := "n" + 1;
4653 END LOOP;
4654 EXIT WHEN "m" = "dimension_v" - 1;
4655 "m" := "m" + 1;
4656 END LOOP;
4657 END IF;
4658 -- store a unique ranking in "rank_ary":
4659 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
4660 "rank_v" := 1;
4661 LOOP
4662 "i" := 1;
4663 <<assign_next_rank>>
4664 LOOP
4665 IF "rank_ary"["i"] ISNULL THEN
4666 "j" := 1;
4667 LOOP
4668 IF
4669 "i" != "j" AND
4670 "rank_ary"["j"] ISNULL AND
4671 ( "matrix_b"["j"]["i"] OR
4672 -- tie-breaking by "id"
4673 ( "matrix_b"["j"]["i"] ISNULL AND
4674 "j" < "i" ) )
4675 THEN
4676 -- someone else is better
4677 EXIT;
4678 END IF;
4679 IF "j" = "dimension_v" THEN
4680 -- noone is better
4681 "rank_ary"["i"] := "rank_v";
4682 EXIT assign_next_rank;
4683 END IF;
4684 "j" := "j" + 1;
4685 END LOOP;
4686 END IF;
4687 "i" := "i" + 1;
4688 IF "i" > "dimension_v" THEN
4689 RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
4690 END IF;
4691 END LOOP;
4692 EXIT WHEN "rank_v" = "dimension_v";
4693 "rank_v" := "rank_v" + 1;
4694 END LOOP;
4695 -- write preliminary results:
4696 "i" := 2; -- omit status quo with "i" = 1
4697 FOR "initiative_id_v" IN
4698 SELECT "id" FROM "initiative"
4699 WHERE "issue_id" = "issue_id_p" AND "admitted"
4700 ORDER BY "id"
4701 LOOP
4702 UPDATE "initiative" SET
4703 "direct_majority" =
4704 CASE WHEN "policy_row"."direct_majority_strict" THEN
4705 "positive_votes" * "policy_row"."direct_majority_den" >
4706 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
4707 ELSE
4708 "positive_votes" * "policy_row"."direct_majority_den" >=
4709 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
4710 END
4711 AND "positive_votes" >= "policy_row"."direct_majority_positive"
4712 AND "issue_row"."voter_count"-"negative_votes" >=
4713 "policy_row"."direct_majority_non_negative",
4714 "indirect_majority" =
4715 CASE WHEN "policy_row"."indirect_majority_strict" THEN
4716 "positive_votes" * "policy_row"."indirect_majority_den" >
4717 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4718 ELSE
4719 "positive_votes" * "policy_row"."indirect_majority_den" >=
4720 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4721 END
4722 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
4723 AND "issue_row"."voter_count"-"negative_votes" >=
4724 "policy_row"."indirect_majority_non_negative",
4725 "schulze_rank" = "rank_ary"["i"],
4726 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
4727 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"[1],
4728 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"[1],
4729 "reverse_beat_path" = CASE WHEN "policy_row"."defeat_strength" = 'simple'::"defeat_strength"
4730 THEN NULL
4731 ELSE "matrix_p"[1]["i"]."primary" >= 0 END,
4732 "eligible" = FALSE,
4733 "winner" = FALSE,
4734 "rank" = NULL -- NOTE: in cases of manual reset of issue state
4735 WHERE "id" = "initiative_id_v";
4736 "i" := "i" + 1;
4737 END LOOP;
4738 IF "i" != "dimension_v" + 1 THEN
4739 RAISE EXCEPTION 'Wrong winner count (should not happen)';
4740 END IF;
4741 -- take indirect majorities into account:
4742 LOOP
4743 UPDATE "initiative" SET "indirect_majority" = TRUE
4744 FROM (
4745 SELECT "new_initiative"."id" AS "initiative_id"
4746 FROM "initiative" "old_initiative"
4747 JOIN "initiative" "new_initiative"
4748 ON "new_initiative"."issue_id" = "issue_id_p"
4749 AND "new_initiative"."indirect_majority" = FALSE
4750 JOIN "battle" "battle_win"
4751 ON "battle_win"."issue_id" = "issue_id_p"
4752 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
4753 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
4754 JOIN "battle" "battle_lose"
4755 ON "battle_lose"."issue_id" = "issue_id_p"
4756 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
4757 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
4758 WHERE "old_initiative"."issue_id" = "issue_id_p"
4759 AND "old_initiative"."indirect_majority" = TRUE
4760 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
4761 "battle_win"."count" * "policy_row"."indirect_majority_den" >
4762 "policy_row"."indirect_majority_num" *
4763 ("battle_win"."count"+"battle_lose"."count")
4764 ELSE
4765 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
4766 "policy_row"."indirect_majority_num" *
4767 ("battle_win"."count"+"battle_lose"."count")
4768 END
4769 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
4770 AND "issue_row"."voter_count"-"battle_lose"."count" >=
4771 "policy_row"."indirect_majority_non_negative"
4772 ) AS "subquery"
4773 WHERE "id" = "subquery"."initiative_id";
4774 EXIT WHEN NOT FOUND;
4775 END LOOP;
4776 -- set "multistage_majority" for remaining matching initiatives:
4777 UPDATE "initiative" SET "multistage_majority" = TRUE
4778 FROM (
4779 SELECT "losing_initiative"."id" AS "initiative_id"
4780 FROM "initiative" "losing_initiative"
4781 JOIN "initiative" "winning_initiative"
4782 ON "winning_initiative"."issue_id" = "issue_id_p"
4783 AND "winning_initiative"."admitted"
4784 JOIN "battle" "battle_win"
4785 ON "battle_win"."issue_id" = "issue_id_p"
4786 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
4787 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
4788 JOIN "battle" "battle_lose"
4789 ON "battle_lose"."issue_id" = "issue_id_p"
4790 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
4791 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
4792 WHERE "losing_initiative"."issue_id" = "issue_id_p"
4793 AND "losing_initiative"."admitted"
4794 AND "winning_initiative"."schulze_rank" <
4795 "losing_initiative"."schulze_rank"
4796 AND "battle_win"."count" > "battle_lose"."count"
4797 AND (
4798 "battle_win"."count" > "winning_initiative"."positive_votes" OR
4799 "battle_lose"."count" < "losing_initiative"."negative_votes" )
4800 ) AS "subquery"
4801 WHERE "id" = "subquery"."initiative_id";
4802 -- mark eligible initiatives:
4803 UPDATE "initiative" SET "eligible" = TRUE
4804 WHERE "issue_id" = "issue_id_p"
4805 AND "initiative"."direct_majority"
4806 AND "initiative"."indirect_majority"
4807 AND "initiative"."better_than_status_quo"
4808 AND (
4809 "policy_row"."no_multistage_majority" = FALSE OR
4810 "initiative"."multistage_majority" = FALSE )
4811 AND (
4812 "policy_row"."no_reverse_beat_path" = FALSE OR
4813 coalesce("initiative"."reverse_beat_path", FALSE) = FALSE );
4814 -- mark final winner:
4815 UPDATE "initiative" SET "winner" = TRUE
4816 FROM (
4817 SELECT "id" AS "initiative_id"
4818 FROM "initiative"
4819 WHERE "issue_id" = "issue_id_p" AND "eligible"
4820 ORDER BY
4821 "schulze_rank",
4822 "id"
4823 LIMIT 1
4824 ) AS "subquery"
4825 WHERE "id" = "subquery"."initiative_id";
4826 -- write (final) ranks:
4827 "rank_v" := 1;
4828 FOR "initiative_id_v" IN
4829 SELECT "id"
4830 FROM "initiative"
4831 WHERE "issue_id" = "issue_id_p" AND "admitted"
4832 ORDER BY
4833 "winner" DESC,
4834 "eligible" DESC,
4835 "schulze_rank",
4836 "id"
4837 LOOP
4838 UPDATE "initiative" SET "rank" = "rank_v"
4839 WHERE "id" = "initiative_id_v";
4840 "rank_v" := "rank_v" + 1;
4841 END LOOP;
4842 -- set schulze rank of status quo and mark issue as finished:
4843 UPDATE "issue" SET
4844 "status_quo_schulze_rank" = "rank_ary"[1],
4845 "state" =
4846 CASE WHEN EXISTS (
4847 SELECT NULL FROM "initiative"
4848 WHERE "issue_id" = "issue_id_p" AND "winner"
4849 ) THEN
4850 'finished_with_winner'::"issue_state"
4851 ELSE
4852 'finished_without_winner'::"issue_state"
4853 END,
4854 "closed" = "phase_finished",
4855 "phase_finished" = NULL
4856 WHERE "id" = "issue_id_p";
4857 RETURN;
4858 END;
4859 $$;
4861 COMMENT ON FUNCTION "calculate_ranks"
4862 ( "issue"."id"%TYPE )
4863 IS 'Determine ranking (Votes have to be counted first)';
4867 -----------------------------
4868 -- Automatic state changes --
4869 -----------------------------
4872 CREATE TYPE "check_issue_persistence" AS (
4873 "state" "issue_state",
4874 "phase_finished" BOOLEAN,
4875 "issue_revoked" BOOLEAN,
4876 "snapshot_created" BOOLEAN,
4877 "harmonic_weights_set" BOOLEAN,
4878 "closed_voting" BOOLEAN );
4880 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';
4883 CREATE FUNCTION "check_issue"
4884 ( "issue_id_p" "issue"."id"%TYPE,
4885 "persist" "check_issue_persistence" )
4886 RETURNS "check_issue_persistence"
4887 LANGUAGE 'plpgsql' VOLATILE AS $$
4888 DECLARE
4889 "issue_row" "issue"%ROWTYPE;
4890 "policy_row" "policy"%ROWTYPE;
4891 "initiative_row" "initiative"%ROWTYPE;
4892 "state_v" "issue_state";
4893 BEGIN
4894 PERFORM "require_transaction_isolation"();
4895 IF "persist" ISNULL THEN
4896 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4897 FOR UPDATE;
4898 IF "issue_row"."closed" NOTNULL THEN
4899 RETURN NULL;
4900 END IF;
4901 "persist"."state" := "issue_row"."state";
4902 IF
4903 ( "issue_row"."state" = 'admission' AND now() >=
4904 "issue_row"."created" + "issue_row"."max_admission_time" ) OR
4905 ( "issue_row"."state" = 'discussion' AND now() >=
4906 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
4907 ( "issue_row"."state" = 'verification' AND now() >=
4908 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
4909 ( "issue_row"."state" = 'voting' AND now() >=
4910 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
4911 THEN
4912 "persist"."phase_finished" := TRUE;
4913 ELSE
4914 "persist"."phase_finished" := FALSE;
4915 END IF;
4916 IF
4917 NOT EXISTS (
4918 -- all initiatives are revoked
4919 SELECT NULL FROM "initiative"
4920 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4921 ) AND (
4922 -- and issue has not been accepted yet
4923 "persist"."state" = 'admission' OR
4924 -- or verification time has elapsed
4925 ( "persist"."state" = 'verification' AND
4926 "persist"."phase_finished" ) OR
4927 -- or no initiatives have been revoked lately
4928 NOT EXISTS (
4929 SELECT NULL FROM "initiative"
4930 WHERE "issue_id" = "issue_id_p"
4931 AND now() < "revoked" + "issue_row"."verification_time"
4932 )
4933 )
4934 THEN
4935 "persist"."issue_revoked" := TRUE;
4936 ELSE
4937 "persist"."issue_revoked" := FALSE;
4938 END IF;
4939 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
4940 UPDATE "issue" SET "phase_finished" = now()
4941 WHERE "id" = "issue_row"."id";
4942 RETURN "persist";
4943 ELSIF
4944 "persist"."state" IN ('admission', 'discussion', 'verification')
4945 THEN
4946 RETURN "persist";
4947 ELSE
4948 RETURN NULL;
4949 END IF;
4950 END IF;
4951 IF
4952 "persist"."state" IN ('admission', 'discussion', 'verification') AND
4953 coalesce("persist"."snapshot_created", FALSE) = FALSE
4954 THEN
4955 PERFORM "create_snapshot"("issue_id_p");
4956 "persist"."snapshot_created" = TRUE;
4957 IF "persist"."phase_finished" THEN
4958 IF "persist"."state" = 'admission' THEN
4959 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
4960 ELSIF "persist"."state" = 'discussion' THEN
4961 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
4962 ELSIF "persist"."state" = 'verification' THEN
4963 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
4964 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4965 SELECT * INTO "policy_row" FROM "policy"
4966 WHERE "id" = "issue_row"."policy_id";
4967 FOR "initiative_row" IN
4968 SELECT * FROM "initiative"
4969 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4970 FOR UPDATE
4971 LOOP
4972 IF
4973 "initiative_row"."polling" OR (
4974 "initiative_row"."satisfied_supporter_count" > 0 AND
4975 "initiative_row"."satisfied_supporter_count" *
4976 "policy_row"."initiative_quorum_den" >=
4977 "issue_row"."population" * "policy_row"."initiative_quorum_num"
4978 )
4979 THEN
4980 UPDATE "initiative" SET "admitted" = TRUE
4981 WHERE "id" = "initiative_row"."id";
4982 ELSE
4983 UPDATE "initiative" SET "admitted" = FALSE
4984 WHERE "id" = "initiative_row"."id";
4985 END IF;
4986 END LOOP;
4987 END IF;
4988 END IF;
4989 RETURN "persist";
4990 END IF;
4991 IF
4992 "persist"."state" IN ('admission', 'discussion', 'verification') AND
4993 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
4994 THEN
4995 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
4996 "persist"."harmonic_weights_set" = TRUE;
4997 IF
4998 "persist"."phase_finished" OR
4999 "persist"."issue_revoked" OR
5000 "persist"."state" = 'admission'
5001 THEN
5002 RETURN "persist";
5003 ELSE
5004 RETURN NULL;
5005 END IF;
5006 END IF;
5007 IF "persist"."issue_revoked" THEN
5008 IF "persist"."state" = 'admission' THEN
5009 "state_v" := 'canceled_revoked_before_accepted';
5010 ELSIF "persist"."state" = 'discussion' THEN
5011 "state_v" := 'canceled_after_revocation_during_discussion';
5012 ELSIF "persist"."state" = 'verification' THEN
5013 "state_v" := 'canceled_after_revocation_during_verification';
5014 END IF;
5015 UPDATE "issue" SET
5016 "state" = "state_v",
5017 "closed" = "phase_finished",
5018 "phase_finished" = NULL
5019 WHERE "id" = "issue_id_p";
5020 RETURN NULL;
5021 END IF;
5022 IF "persist"."state" = 'admission' THEN
5023 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
5024 FOR UPDATE;
5025 SELECT * INTO "policy_row"
5026 FROM "policy" WHERE "id" = "issue_row"."policy_id";
5027 IF
5028 ( now() >=
5029 "issue_row"."created" + "issue_row"."min_admission_time" ) AND
5030 -- TODO: implement new mechanism for issue admission
5031 EXISTS (
5032 SELECT NULL FROM "initiative" WHERE "issue_id" = "issue_id_p"
5033 AND "supporter_count" >= "policy_row"."issue_quorum"
5034 )
5035 THEN
5036 UPDATE "issue" SET
5037 "state" = 'discussion',
5038 "accepted" = coalesce("phase_finished", now()),
5039 "phase_finished" = NULL
5040 WHERE "id" = "issue_id_p";
5041 ELSIF "issue_row"."phase_finished" NOTNULL THEN
5042 UPDATE "issue" SET
5043 "state" = 'canceled_issue_not_accepted',
5044 "closed" = "phase_finished",
5045 "phase_finished" = NULL
5046 WHERE "id" = "issue_id_p";
5047 END IF;
5048 RETURN NULL;
5049 END IF;
5050 IF "persist"."phase_finished" THEN
5051 IF "persist"."state" = 'discussion' THEN
5052 UPDATE "issue" SET
5053 "state" = 'verification',
5054 "half_frozen" = "phase_finished",
5055 "phase_finished" = NULL
5056 WHERE "id" = "issue_id_p";
5057 RETURN NULL;
5058 END IF;
5059 IF "persist"."state" = 'verification' THEN
5060 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
5061 FOR UPDATE;
5062 SELECT * INTO "policy_row" FROM "policy"
5063 WHERE "id" = "issue_row"."policy_id";
5064 IF EXISTS (
5065 SELECT NULL FROM "initiative"
5066 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
5067 ) THEN
5068 UPDATE "issue" SET
5069 "state" = 'voting',
5070 "fully_frozen" = "phase_finished",
5071 "phase_finished" = NULL
5072 WHERE "id" = "issue_id_p";
5073 ELSE
5074 UPDATE "issue" SET
5075 "state" = 'canceled_no_initiative_admitted',
5076 "fully_frozen" = "phase_finished",
5077 "closed" = "phase_finished",
5078 "phase_finished" = NULL
5079 WHERE "id" = "issue_id_p";
5080 -- NOTE: The following DELETE statements have effect only when
5081 -- issue state has been manipulated
5082 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
5083 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
5084 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
5085 END IF;
5086 RETURN NULL;
5087 END IF;
5088 IF "persist"."state" = 'voting' THEN
5089 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
5090 PERFORM "close_voting"("issue_id_p");
5091 "persist"."closed_voting" = TRUE;
5092 RETURN "persist";
5093 END IF;
5094 PERFORM "calculate_ranks"("issue_id_p");
5095 RETURN NULL;
5096 END IF;
5097 END IF;
5098 RAISE WARNING 'should not happen';
5099 RETURN NULL;
5100 END;
5101 $$;
5103 COMMENT ON FUNCTION "check_issue"
5104 ( "issue"."id"%TYPE,
5105 "check_issue_persistence" )
5106 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")';
5109 CREATE FUNCTION "check_everything"()
5110 RETURNS VOID
5111 LANGUAGE 'plpgsql' VOLATILE AS $$
5112 DECLARE
5113 "issue_id_v" "issue"."id"%TYPE;
5114 "persist_v" "check_issue_persistence";
5115 BEGIN
5116 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
5117 DELETE FROM "expired_session";
5118 PERFORM "check_activity"();
5119 PERFORM "calculate_member_counts"();
5120 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
5121 "persist_v" := NULL;
5122 LOOP
5123 "persist_v" := "check_issue"("issue_id_v", "persist_v");
5124 EXIT WHEN "persist_v" ISNULL;
5125 END LOOP;
5126 END LOOP;
5127 RETURN;
5128 END;
5129 $$;
5131 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.';
5135 ----------------------
5136 -- Deletion of data --
5137 ----------------------
5140 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
5141 RETURNS VOID
5142 LANGUAGE 'plpgsql' VOLATILE AS $$
5143 BEGIN
5144 IF EXISTS (
5145 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
5146 ) THEN
5147 -- override protection triggers:
5148 INSERT INTO "temporary_transaction_data" ("key", "value")
5149 VALUES ('override_protection_triggers', TRUE::TEXT);
5150 -- clean data:
5151 DELETE FROM "delegating_voter"
5152 WHERE "issue_id" = "issue_id_p";
5153 DELETE FROM "direct_voter"
5154 WHERE "issue_id" = "issue_id_p";
5155 DELETE FROM "delegating_interest_snapshot"
5156 WHERE "issue_id" = "issue_id_p";
5157 DELETE FROM "direct_interest_snapshot"
5158 WHERE "issue_id" = "issue_id_p";
5159 DELETE FROM "delegating_population_snapshot"
5160 WHERE "issue_id" = "issue_id_p";
5161 DELETE FROM "direct_population_snapshot"
5162 WHERE "issue_id" = "issue_id_p";
5163 DELETE FROM "non_voter"
5164 WHERE "issue_id" = "issue_id_p";
5165 DELETE FROM "delegation"
5166 WHERE "issue_id" = "issue_id_p";
5167 DELETE FROM "supporter"
5168 USING "initiative" -- NOTE: due to missing index on issue_id
5169 WHERE "initiative"."issue_id" = "issue_id_p"
5170 AND "supporter"."initiative_id" = "initiative_id";
5171 -- mark issue as cleaned:
5172 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
5173 -- finish overriding protection triggers (avoids garbage):
5174 DELETE FROM "temporary_transaction_data"
5175 WHERE "key" = 'override_protection_triggers';
5176 END IF;
5177 RETURN;
5178 END;
5179 $$;
5181 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
5184 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
5185 RETURNS VOID
5186 LANGUAGE 'plpgsql' VOLATILE AS $$
5187 BEGIN
5188 UPDATE "member" SET
5189 "last_login" = NULL,
5190 "last_delegation_check" = NULL,
5191 "login" = NULL,
5192 "password" = NULL,
5193 "authority" = NULL,
5194 "authority_uid" = NULL,
5195 "authority_login" = NULL,
5196 "locked" = TRUE,
5197 "active" = FALSE,
5198 "notify_email" = NULL,
5199 "notify_email_unconfirmed" = NULL,
5200 "notify_email_secret" = NULL,
5201 "notify_email_secret_expiry" = NULL,
5202 "notify_email_lock_expiry" = NULL,
5203 "disable_notifications" = NULL,
5204 "notification_counter" = NULL,
5205 "notification_sample_size" = NULL,
5206 "notification_dow" = NULL,
5207 "notification_hour" = NULL,
5208 "login_recovery_expiry" = NULL,
5209 "password_reset_secret" = NULL,
5210 "password_reset_secret_expiry" = NULL,
5211 "organizational_unit" = NULL,
5212 "internal_posts" = NULL,
5213 "realname" = NULL,
5214 "birthday" = NULL,
5215 "address" = NULL,
5216 "email" = NULL,
5217 "xmpp_address" = NULL,
5218 "website" = NULL,
5219 "phone" = NULL,
5220 "mobile_phone" = NULL,
5221 "profession" = NULL,
5222 "external_memberships" = NULL,
5223 "external_posts" = NULL,
5224 "statement" = NULL
5225 WHERE "id" = "member_id_p";
5226 -- "text_search_data" is updated by triggers
5227 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
5228 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
5229 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
5230 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
5231 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
5232 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
5233 DELETE FROM "session" WHERE "member_id" = "member_id_p";
5234 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
5235 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
5236 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
5237 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
5238 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
5239 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
5240 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
5241 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
5242 DELETE FROM "direct_voter" USING "issue"
5243 WHERE "direct_voter"."issue_id" = "issue"."id"
5244 AND "issue"."closed" ISNULL
5245 AND "member_id" = "member_id_p";
5246 RETURN;
5247 END;
5248 $$;
5250 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)';
5253 CREATE FUNCTION "delete_private_data"()
5254 RETURNS VOID
5255 LANGUAGE 'plpgsql' VOLATILE AS $$
5256 BEGIN
5257 DELETE FROM "temporary_transaction_data";
5258 DELETE FROM "member" WHERE "activated" ISNULL;
5259 UPDATE "member" SET
5260 "invite_code" = NULL,
5261 "invite_code_expiry" = NULL,
5262 "admin_comment" = NULL,
5263 "last_login" = NULL,
5264 "last_delegation_check" = NULL,
5265 "login" = NULL,
5266 "password" = NULL,
5267 "authority" = NULL,
5268 "authority_uid" = NULL,
5269 "authority_login" = NULL,
5270 "lang" = NULL,
5271 "notify_email" = NULL,
5272 "notify_email_unconfirmed" = NULL,
5273 "notify_email_secret" = NULL,
5274 "notify_email_secret_expiry" = NULL,
5275 "notify_email_lock_expiry" = NULL,
5276 "disable_notifications" = NULL,
5277 "notification_counter" = NULL,
5278 "notification_sample_size" = NULL,
5279 "notification_dow" = NULL,
5280 "notification_hour" = NULL,
5281 "login_recovery_expiry" = NULL,
5282 "password_reset_secret" = NULL,
5283 "password_reset_secret_expiry" = NULL,
5284 "organizational_unit" = NULL,
5285 "internal_posts" = NULL,
5286 "realname" = NULL,
5287 "birthday" = NULL,
5288 "address" = NULL,
5289 "email" = NULL,
5290 "xmpp_address" = NULL,
5291 "website" = NULL,
5292 "phone" = NULL,
5293 "mobile_phone" = NULL,
5294 "profession" = NULL,
5295 "external_memberships" = NULL,
5296 "external_posts" = NULL,
5297 "formatting_engine" = NULL,
5298 "statement" = NULL;
5299 -- "text_search_data" is updated by triggers
5300 DELETE FROM "setting";
5301 DELETE FROM "setting_map";
5302 DELETE FROM "member_relation_setting";
5303 DELETE FROM "member_image";
5304 DELETE FROM "contact";
5305 DELETE FROM "ignored_member";
5306 DELETE FROM "session";
5307 DELETE FROM "area_setting";
5308 DELETE FROM "issue_setting";
5309 DELETE FROM "ignored_initiative";
5310 DELETE FROM "initiative_setting";
5311 DELETE FROM "suggestion_setting";
5312 DELETE FROM "non_voter";
5313 DELETE FROM "direct_voter" USING "issue"
5314 WHERE "direct_voter"."issue_id" = "issue"."id"
5315 AND "issue"."closed" ISNULL;
5316 RETURN;
5317 END;
5318 $$;
5320 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.';
5324 COMMIT;