liquid_feedback_core

view core.sql @ 524:bc6d9dc60ca4

Require members to be "activated" in order to receive messages/notifications; New views "member_eligible_to_be_notified" and "member_to_notify"
author jbe
date Thu May 05 21:13:09 2016 +0200 (2016-05-05)
parents 2f1c06608def
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 ('3.2.2', 3, 2, 2))
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_num" INT4,
375 "issue_quorum_den" INT4,
376 "initiative_quorum_num" INT4 NOT NULL,
377 "initiative_quorum_den" INT4 NOT NULL,
378 "defeat_strength" "defeat_strength" NOT NULL DEFAULT 'tuple',
379 "tie_breaking" "tie_breaking" NOT NULL DEFAULT 'variant1',
380 "direct_majority_num" INT4 NOT NULL DEFAULT 1,
381 "direct_majority_den" INT4 NOT NULL DEFAULT 2,
382 "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
383 "direct_majority_positive" INT4 NOT NULL DEFAULT 0,
384 "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0,
385 "indirect_majority_num" INT4 NOT NULL DEFAULT 1,
386 "indirect_majority_den" INT4 NOT NULL DEFAULT 2,
387 "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
388 "indirect_majority_positive" INT4 NOT NULL DEFAULT 0,
389 "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0,
390 "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT FALSE,
391 "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE,
392 CONSTRAINT "timing" CHECK (
393 ( "polling" = FALSE AND
394 "min_admission_time" NOTNULL AND "max_admission_time" NOTNULL AND
395 "min_admission_time" <= "max_admission_time" AND
396 "discussion_time" NOTNULL AND
397 "verification_time" NOTNULL AND
398 "voting_time" NOTNULL ) OR
399 ( "polling" = TRUE AND
400 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
401 "discussion_time" NOTNULL AND
402 "verification_time" NOTNULL AND
403 "voting_time" NOTNULL ) OR
404 ( "polling" = TRUE AND
405 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
406 "discussion_time" ISNULL AND
407 "verification_time" ISNULL AND
408 "voting_time" ISNULL ) ),
409 CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK (
410 "polling" = ("issue_quorum_num" ISNULL) AND
411 "polling" = ("issue_quorum_den" ISNULL) ),
412 CONSTRAINT "no_reverse_beat_path_requires_tuple_defeat_strength" CHECK (
413 "defeat_strength" = 'tuple'::"defeat_strength" OR
414 "no_reverse_beat_path" = FALSE ) );
415 CREATE INDEX "policy_active_idx" ON "policy" ("active");
417 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
419 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
420 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
421 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';
422 COMMENT ON COLUMN "policy"."min_admission_time" IS 'Minimum duration of issue state ''admission''; Minimum time an issue stays open';
423 COMMENT ON COLUMN "policy"."max_admission_time" IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';
424 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"';
425 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"';
426 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'')';
427 COMMENT ON COLUMN "policy"."issue_quorum_num" IS 'Numerator of potential supporter quorum to be reached by one initiative of an issue to be "accepted" and enter issue state ''discussion''';
428 COMMENT ON COLUMN "policy"."issue_quorum_den" IS 'Denominator of potential supporter quorum to be reached by one initiative of an issue to be "accepted" and enter issue state ''discussion''';
429 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
430 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
431 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';
432 COMMENT ON COLUMN "policy"."tie_breaking" IS 'Tie-breaker for the Schulze method; see type "tie_breaking"; ''variant1'' or ''variant2'' are recommended';
433 COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner';
434 COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner';
435 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.';
436 COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner';
437 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';
438 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';
439 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';
440 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.';
441 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';
442 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';
443 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.';
444 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").';
447 CREATE TABLE "unit" (
448 "id" SERIAL4 PRIMARY KEY,
449 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
450 "active" BOOLEAN NOT NULL DEFAULT TRUE,
451 "name" TEXT NOT NULL,
452 "description" TEXT NOT NULL DEFAULT '',
453 "external_reference" TEXT,
454 "member_count" INT4,
455 "text_search_data" TSVECTOR );
456 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
457 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
458 CREATE INDEX "unit_active_idx" ON "unit" ("active");
459 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
460 CREATE TRIGGER "update_text_search_data"
461 BEFORE INSERT OR UPDATE ON "unit"
462 FOR EACH ROW EXECUTE PROCEDURE
463 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
464 "name", "description" );
466 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
468 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
469 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this unit';
470 COMMENT ON COLUMN "unit"."external_reference" IS 'Opaque data field to store an external reference';
471 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
474 CREATE TABLE "subscription" (
475 PRIMARY KEY ("member_id", "unit_id"),
476 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
477 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
478 CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id");
480 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';
483 CREATE TABLE "unit_setting" (
484 PRIMARY KEY ("member_id", "key", "unit_id"),
485 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
486 "key" TEXT NOT NULL,
487 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
488 "value" TEXT NOT NULL );
490 COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings';
493 CREATE TABLE "area" (
494 "id" SERIAL4 PRIMARY KEY,
495 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
496 "active" BOOLEAN NOT NULL DEFAULT TRUE,
497 "name" TEXT NOT NULL,
498 "description" TEXT NOT NULL DEFAULT '',
499 "external_reference" TEXT,
500 "direct_member_count" INT4,
501 "member_weight" INT4,
502 "text_search_data" TSVECTOR );
503 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
504 CREATE INDEX "area_active_idx" ON "area" ("active");
505 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
506 CREATE TRIGGER "update_text_search_data"
507 BEFORE INSERT OR UPDATE ON "area"
508 FOR EACH ROW EXECUTE PROCEDURE
509 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
510 "name", "description" );
512 COMMENT ON TABLE "area" IS 'Subject areas';
514 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
515 COMMENT ON COLUMN "area"."external_reference" IS 'Opaque data field to store an external reference';
516 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"';
517 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
520 CREATE TABLE "ignored_area" (
521 PRIMARY KEY ("member_id", "area_id"),
522 "member_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
523 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
524 CREATE INDEX "ignored_area_area_id_idx" ON "ignored_area" ("area_id");
526 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';
529 CREATE TABLE "area_setting" (
530 PRIMARY KEY ("member_id", "key", "area_id"),
531 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
532 "key" TEXT NOT NULL,
533 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
534 "value" TEXT NOT NULL );
536 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
539 CREATE TABLE "allowed_policy" (
540 PRIMARY KEY ("area_id", "policy_id"),
541 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
542 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
543 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
544 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
546 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
548 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
551 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
553 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';
556 CREATE TYPE "issue_state" AS ENUM (
557 'admission', 'discussion', 'verification', 'voting',
558 'canceled_by_admin',
559 'canceled_revoked_before_accepted',
560 'canceled_issue_not_accepted',
561 'canceled_after_revocation_during_discussion',
562 'canceled_after_revocation_during_verification',
563 'canceled_no_initiative_admitted',
564 'finished_without_winner', 'finished_with_winner');
566 COMMENT ON TYPE "issue_state" IS 'State of issues';
569 CREATE TABLE "issue" (
570 "id" SERIAL4 PRIMARY KEY,
571 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
572 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
573 "admin_notice" TEXT,
574 "external_reference" TEXT,
575 "state" "issue_state" NOT NULL DEFAULT 'admission',
576 "phase_finished" TIMESTAMPTZ,
577 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
578 "accepted" TIMESTAMPTZ,
579 "half_frozen" TIMESTAMPTZ,
580 "fully_frozen" TIMESTAMPTZ,
581 "closed" TIMESTAMPTZ,
582 "cleaned" TIMESTAMPTZ,
583 "min_admission_time" INTERVAL,
584 "max_admission_time" INTERVAL,
585 "discussion_time" INTERVAL NOT NULL,
586 "verification_time" INTERVAL NOT NULL,
587 "voting_time" INTERVAL NOT NULL,
588 "snapshot" TIMESTAMPTZ,
589 "latest_snapshot_event" "snapshot_event",
590 "population" INT4,
591 "voter_count" INT4,
592 "status_quo_schulze_rank" INT4,
593 CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (
594 ("min_admission_time" NOTNULL) = ("max_admission_time" NOTNULL) AND
595 ("min_admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created")) ),
596 CONSTRAINT "valid_state" CHECK (
597 (
598 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
599 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
600 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL ) OR
601 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL)
602 ) AND (
603 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
604 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
605 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
606 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
607 ("state" = 'canceled_by_admin' AND "closed" NOTNULL) OR
608 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
609 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
610 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
611 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
612 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" = "fully_frozen") OR
613 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen") OR
614 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen")
615 )),
616 CONSTRAINT "phase_finished_only_when_not_closed" CHECK (
617 "phase_finished" ISNULL OR "closed" ISNULL ),
618 CONSTRAINT "state_change_order" CHECK (
619 "created" <= "accepted" AND
620 "accepted" <= "half_frozen" AND
621 "half_frozen" <= "fully_frozen" AND
622 "fully_frozen" <= "closed" ),
623 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
624 "cleaned" ISNULL OR "closed" NOTNULL ),
625 CONSTRAINT "last_snapshot_on_full_freeze"
626 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
627 CONSTRAINT "freeze_requires_snapshot"
628 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
629 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
630 CHECK (("snapshot" NOTNULL) = ("latest_snapshot_event" NOTNULL)) );
631 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
632 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
633 CREATE INDEX "issue_created_idx" ON "issue" ("created");
634 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
635 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
636 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
637 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
638 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
639 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
641 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
643 COMMENT ON COLUMN "issue"."admin_notice" IS 'Public notice by admin to explain manual interventions, or to announce corrections';
644 COMMENT ON COLUMN "issue"."external_reference" IS 'Opaque data field to store an external reference';
645 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';
646 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
647 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.';
648 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.';
649 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.';
650 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
651 COMMENT ON COLUMN "issue"."min_admission_time" IS 'Copied from "policy" table at creation of issue';
652 COMMENT ON COLUMN "issue"."max_admission_time" IS 'Copied from "policy" table at creation of issue';
653 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
654 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
655 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
656 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
657 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';
658 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
659 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';
660 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
663 CREATE TABLE "issue_order_in_admission_state" (
664 "id" INT8 PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
665 "order_in_area" INT4,
666 "order_in_unit" INT4 );
668 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"';
670 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';
671 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';
672 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';
675 CREATE TABLE "issue_setting" (
676 PRIMARY KEY ("member_id", "key", "issue_id"),
677 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
678 "key" TEXT NOT NULL,
679 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
680 "value" TEXT NOT NULL );
682 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
685 CREATE TABLE "initiative" (
686 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
687 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
688 "id" SERIAL4 PRIMARY KEY,
689 "name" TEXT NOT NULL,
690 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
691 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
692 "revoked" TIMESTAMPTZ,
693 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
694 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
695 "external_reference" TEXT,
696 "admitted" BOOLEAN,
697 "supporter_count" INT4,
698 "informed_supporter_count" INT4,
699 "satisfied_supporter_count" INT4,
700 "satisfied_informed_supporter_count" INT4,
701 "harmonic_weight" NUMERIC(12, 3),
702 "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE,
703 "first_preference_votes" INT4,
704 "positive_votes" INT4,
705 "negative_votes" INT4,
706 "direct_majority" BOOLEAN,
707 "indirect_majority" BOOLEAN,
708 "schulze_rank" INT4,
709 "better_than_status_quo" BOOLEAN,
710 "worse_than_status_quo" BOOLEAN,
711 "reverse_beat_path" BOOLEAN,
712 "multistage_majority" BOOLEAN,
713 "eligible" BOOLEAN,
714 "winner" BOOLEAN,
715 "rank" INT4,
716 "text_search_data" TSVECTOR,
717 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
718 CHECK (("revoked" NOTNULL) = ("revoked_by_member_id" NOTNULL)),
719 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
720 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
721 CONSTRAINT "revoked_initiatives_cant_be_admitted"
722 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
723 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
724 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
725 ( "first_preference_votes" ISNULL AND
726 "positive_votes" ISNULL AND "negative_votes" ISNULL AND
727 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
728 "schulze_rank" ISNULL AND
729 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
730 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
731 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ),
732 CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")),
733 CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (
734 "eligible" = FALSE OR
735 ("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ),
736 CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE),
737 CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1),
738 CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE),
739 CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") );
740 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
741 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
742 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
743 CREATE TRIGGER "update_text_search_data"
744 BEFORE INSERT OR UPDATE ON "initiative"
745 FOR EACH ROW EXECUTE PROCEDURE
746 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "name");
748 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.';
750 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative does not need to pass the initiative quorum (see "policy"."polling")';
751 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
752 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoke the initiative';
753 COMMENT ON COLUMN "initiative"."external_reference" IS 'Opaque data field to store an external reference';
754 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
755 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
756 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
757 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
758 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
759 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';
760 COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time';
761 COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice';
762 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Number of direct and delegating voters who ranked this initiative better than the status quo';
763 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo';
764 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"';
765 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths';
766 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking';
767 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo';
768 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)';
769 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''';
770 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';
771 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"';
772 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank"';
773 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';
776 CREATE TABLE "battle" (
777 "issue_id" INT4 NOT NULL,
778 "winning_initiative_id" INT4,
779 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
780 "losing_initiative_id" INT4,
781 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
782 "count" INT4 NOT NULL,
783 CONSTRAINT "initiative_ids_not_equal" CHECK (
784 "winning_initiative_id" != "losing_initiative_id" OR
785 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
786 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) );
787 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
788 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
789 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
791 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';
794 CREATE TABLE "ignored_initiative" (
795 PRIMARY KEY ("member_id", "initiative_id"),
796 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
797 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
798 CREATE INDEX "ignored_initiative_initiative_id_idx" ON "ignored_initiative" ("initiative_id");
800 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';
803 CREATE TABLE "initiative_setting" (
804 PRIMARY KEY ("member_id", "key", "initiative_id"),
805 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
806 "key" TEXT NOT NULL,
807 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
808 "value" TEXT NOT NULL );
810 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
813 CREATE TABLE "draft" (
814 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
815 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
816 "id" SERIAL8 PRIMARY KEY,
817 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
818 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
819 "formatting_engine" TEXT,
820 "content" TEXT NOT NULL,
821 "external_reference" TEXT,
822 "text_search_data" TSVECTOR );
823 CREATE INDEX "draft_created_idx" ON "draft" ("created");
824 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
825 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
826 CREATE TRIGGER "update_text_search_data"
827 BEFORE INSERT OR UPDATE ON "draft"
828 FOR EACH ROW EXECUTE PROCEDURE
829 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
831 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.';
833 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
834 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
835 COMMENT ON COLUMN "draft"."external_reference" IS 'Opaque data field to store an external reference';
838 CREATE TABLE "rendered_draft" (
839 PRIMARY KEY ("draft_id", "format"),
840 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
841 "format" TEXT,
842 "content" TEXT NOT NULL );
844 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)';
847 CREATE TABLE "suggestion" (
848 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
849 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
850 "id" SERIAL8 PRIMARY KEY,
851 "draft_id" INT8 NOT NULL,
852 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
853 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
854 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
855 "name" TEXT NOT NULL,
856 "formatting_engine" TEXT,
857 "content" TEXT NOT NULL DEFAULT '',
858 "external_reference" TEXT,
859 "text_search_data" TSVECTOR,
860 "minus2_unfulfilled_count" INT4,
861 "minus2_fulfilled_count" INT4,
862 "minus1_unfulfilled_count" INT4,
863 "minus1_fulfilled_count" INT4,
864 "plus1_unfulfilled_count" INT4,
865 "plus1_fulfilled_count" INT4,
866 "plus2_unfulfilled_count" INT4,
867 "plus2_fulfilled_count" INT4,
868 "proportional_order" INT4 );
869 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
870 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
871 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
872 CREATE TRIGGER "update_text_search_data"
873 BEFORE INSERT OR UPDATE ON "suggestion"
874 FOR EACH ROW EXECUTE PROCEDURE
875 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
876 "name", "content");
878 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';
880 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")';
881 COMMENT ON COLUMN "suggestion"."external_reference" IS 'Opaque data field to store an external reference';
882 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
883 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
884 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
885 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
886 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
887 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
888 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
889 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
890 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"';
893 CREATE TABLE "rendered_suggestion" (
894 PRIMARY KEY ("suggestion_id", "format"),
895 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
896 "format" TEXT,
897 "content" TEXT NOT NULL );
899 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)';
902 CREATE TABLE "suggestion_setting" (
903 PRIMARY KEY ("member_id", "key", "suggestion_id"),
904 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
905 "key" TEXT NOT NULL,
906 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
907 "value" TEXT NOT NULL );
909 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
912 CREATE TABLE "privilege" (
913 PRIMARY KEY ("unit_id", "member_id"),
914 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
915 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
916 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
917 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
918 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
919 "member_manager" BOOLEAN NOT NULL DEFAULT FALSE,
920 "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE,
921 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE,
922 "polling_right" BOOLEAN NOT NULL DEFAULT FALSE );
924 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
926 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke any privileges to/from other members';
927 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create and disable sub units';
928 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create and disable areas and set area parameters';
929 COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';
930 COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';
931 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote';
932 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';
935 CREATE TABLE "membership" (
936 PRIMARY KEY ("area_id", "member_id"),
937 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
938 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
939 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
941 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
944 CREATE TABLE "interest" (
945 PRIMARY KEY ("issue_id", "member_id"),
946 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
947 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
948 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
950 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.';
953 CREATE TABLE "initiator" (
954 PRIMARY KEY ("initiative_id", "member_id"),
955 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
956 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
957 "accepted" BOOLEAN );
958 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
960 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.';
962 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.';
965 CREATE TABLE "supporter" (
966 "issue_id" INT4 NOT NULL,
967 PRIMARY KEY ("initiative_id", "member_id"),
968 "initiative_id" INT4,
969 "member_id" INT4,
970 "draft_id" INT8 NOT NULL,
971 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
972 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE );
973 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
975 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.';
977 COMMENT ON COLUMN "supporter"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
978 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")';
981 CREATE TABLE "opinion" (
982 "initiative_id" INT4 NOT NULL,
983 PRIMARY KEY ("suggestion_id", "member_id"),
984 "suggestion_id" INT8,
985 "member_id" INT4,
986 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
987 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
988 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
989 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
990 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
992 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.';
994 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
997 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
999 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
1002 CREATE TABLE "delegation" (
1003 "id" SERIAL8 PRIMARY KEY,
1004 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1005 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1006 "scope" "delegation_scope" NOT NULL,
1007 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1008 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1009 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1010 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
1011 CONSTRAINT "no_unit_delegation_to_null"
1012 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
1013 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
1014 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
1015 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
1016 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
1017 UNIQUE ("unit_id", "truster_id"),
1018 UNIQUE ("area_id", "truster_id"),
1019 UNIQUE ("issue_id", "truster_id") );
1020 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
1021 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
1023 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
1025 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
1026 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
1027 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
1030 CREATE TABLE "direct_population_snapshot" (
1031 PRIMARY KEY ("issue_id", "event", "member_id"),
1032 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1033 "event" "snapshot_event",
1034 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1035 "weight" INT4 );
1036 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
1038 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';
1040 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1041 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
1044 CREATE TABLE "delegating_population_snapshot" (
1045 PRIMARY KEY ("issue_id", "event", "member_id"),
1046 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1047 "event" "snapshot_event",
1048 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1049 "weight" INT4,
1050 "scope" "delegation_scope" NOT NULL,
1051 "delegate_member_ids" INT4[] NOT NULL );
1052 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
1054 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';
1056 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1057 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
1058 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
1059 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"';
1062 CREATE TABLE "direct_interest_snapshot" (
1063 PRIMARY KEY ("issue_id", "event", "member_id"),
1064 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1065 "event" "snapshot_event",
1066 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1067 "weight" INT4 );
1068 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
1070 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';
1072 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1073 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
1076 CREATE TABLE "delegating_interest_snapshot" (
1077 PRIMARY KEY ("issue_id", "event", "member_id"),
1078 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1079 "event" "snapshot_event",
1080 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1081 "weight" INT4,
1082 "scope" "delegation_scope" NOT NULL,
1083 "delegate_member_ids" INT4[] NOT NULL );
1084 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
1086 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';
1088 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1089 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
1090 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
1091 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"';
1094 CREATE TABLE "direct_supporter_snapshot" (
1095 "issue_id" INT4 NOT NULL,
1096 PRIMARY KEY ("initiative_id", "event", "member_id"),
1097 "initiative_id" INT4,
1098 "event" "snapshot_event",
1099 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1100 "draft_id" INT8 NOT NULL,
1101 "informed" BOOLEAN NOT NULL,
1102 "satisfied" BOOLEAN NOT NULL,
1103 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1104 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
1105 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1106 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
1108 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';
1110 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';
1111 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1112 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
1113 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
1116 CREATE TABLE "non_voter" (
1117 PRIMARY KEY ("issue_id", "member_id"),
1118 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1119 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
1120 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
1122 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
1125 CREATE TABLE "direct_voter" (
1126 PRIMARY KEY ("issue_id", "member_id"),
1127 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1128 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1129 "weight" INT4,
1130 "comment_changed" TIMESTAMPTZ,
1131 "formatting_engine" TEXT,
1132 "comment" TEXT,
1133 "text_search_data" TSVECTOR );
1134 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
1135 CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data");
1136 CREATE TRIGGER "update_text_search_data"
1137 BEFORE INSERT OR UPDATE ON "direct_voter"
1138 FOR EACH ROW EXECUTE PROCEDURE
1139 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment");
1141 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';
1143 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
1144 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';
1145 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';
1146 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.';
1149 CREATE TABLE "rendered_voter_comment" (
1150 PRIMARY KEY ("issue_id", "member_id", "format"),
1151 FOREIGN KEY ("issue_id", "member_id")
1152 REFERENCES "direct_voter" ("issue_id", "member_id")
1153 ON DELETE CASCADE ON UPDATE CASCADE,
1154 "issue_id" INT4,
1155 "member_id" INT4,
1156 "format" TEXT,
1157 "content" TEXT NOT NULL );
1159 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)';
1162 CREATE TABLE "delegating_voter" (
1163 PRIMARY KEY ("issue_id", "member_id"),
1164 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1165 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1166 "weight" INT4,
1167 "scope" "delegation_scope" NOT NULL,
1168 "delegate_member_ids" INT4[] NOT NULL );
1169 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
1171 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';
1173 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
1174 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
1175 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"';
1178 CREATE TABLE "vote" (
1179 "issue_id" INT4 NOT NULL,
1180 PRIMARY KEY ("initiative_id", "member_id"),
1181 "initiative_id" INT4,
1182 "member_id" INT4,
1183 "grade" INT4 NOT NULL,
1184 "first_preference" BOOLEAN,
1185 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1186 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
1187 CONSTRAINT "first_preference_flag_only_set_on_positive_grades"
1188 CHECK ("grade" > 0 OR "first_preference" ISNULL) );
1189 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
1191 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';
1193 COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
1194 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.';
1195 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.';
1198 CREATE TYPE "event_type" AS ENUM (
1199 'issue_state_changed',
1200 'initiative_created_in_new_issue',
1201 'initiative_created_in_existing_issue',
1202 'initiative_revoked',
1203 'new_draft_created',
1204 'suggestion_created');
1206 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
1209 CREATE TABLE "event" (
1210 "id" SERIAL8 PRIMARY KEY,
1211 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
1212 "event" "event_type" NOT NULL,
1213 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1214 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1215 "state" "issue_state",
1216 "initiative_id" INT4,
1217 "draft_id" INT8,
1218 "suggestion_id" INT8,
1219 FOREIGN KEY ("issue_id", "initiative_id")
1220 REFERENCES "initiative" ("issue_id", "id")
1221 ON DELETE CASCADE ON UPDATE CASCADE,
1222 FOREIGN KEY ("initiative_id", "draft_id")
1223 REFERENCES "draft" ("initiative_id", "id")
1224 ON DELETE CASCADE ON UPDATE CASCADE,
1225 FOREIGN KEY ("initiative_id", "suggestion_id")
1226 REFERENCES "suggestion" ("initiative_id", "id")
1227 ON DELETE CASCADE ON UPDATE CASCADE,
1228 CONSTRAINT "null_constr_for_issue_state_changed" CHECK (
1229 "event" != 'issue_state_changed' OR (
1230 "member_id" ISNULL AND
1231 "issue_id" NOTNULL AND
1232 "state" NOTNULL AND
1233 "initiative_id" ISNULL AND
1234 "draft_id" ISNULL AND
1235 "suggestion_id" ISNULL )),
1236 CONSTRAINT "null_constr_for_initiative_creation_or_revocation_or_new_draft" CHECK (
1237 "event" NOT IN (
1238 'initiative_created_in_new_issue',
1239 'initiative_created_in_existing_issue',
1240 'initiative_revoked',
1241 'new_draft_created'
1242 ) OR (
1243 "member_id" NOTNULL AND
1244 "issue_id" NOTNULL AND
1245 "state" NOTNULL AND
1246 "initiative_id" NOTNULL AND
1247 "draft_id" NOTNULL AND
1248 "suggestion_id" ISNULL )),
1249 CONSTRAINT "null_constr_for_suggestion_creation" CHECK (
1250 "event" != 'suggestion_created' OR (
1251 "member_id" NOTNULL AND
1252 "issue_id" NOTNULL AND
1253 "state" NOTNULL AND
1254 "initiative_id" NOTNULL AND
1255 "draft_id" ISNULL AND
1256 "suggestion_id" NOTNULL )) );
1257 CREATE INDEX "event_occurrence_idx" ON "event" ("occurrence");
1259 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
1261 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
1262 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
1263 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
1264 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
1267 CREATE TABLE "notification_event_sent" (
1268 "event_id" INT8 NOT NULL );
1269 CREATE UNIQUE INDEX "notification_event_sent_singleton_idx" ON "notification_event_sent" ((1));
1271 COMMENT ON TABLE "notification_event_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';
1272 COMMENT ON INDEX "notification_event_sent_singleton_idx" IS 'This index ensures that "notification_event_sent" only contains one row maximum.';
1275 CREATE TABLE "notification_initiative_sent" (
1276 PRIMARY KEY ("member_id", "initiative_id"),
1277 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1278 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1279 "last_draft_id" INT8 NOT NULL,
1280 "last_suggestion_id" INT8 );
1281 CREATE INDEX "notification_initiative_sent_initiative_idx" ON "notification_initiative_sent" ("initiative_id");
1283 COMMENT ON TABLE "notification_initiative_sent" IS 'Information which initiatives have been promoted to a member in a scheduled notification mail';
1285 COMMENT ON COLUMN "notification_initiative_sent"."last_draft_id" IS 'Current (i.e. last) draft_id when initiative had been promoted';
1286 COMMENT ON COLUMN "notification_initiative_sent"."last_suggestion_id" IS 'Current (i.e. last) draft_id when initiative had been promoted';
1289 CREATE TABLE "newsletter" (
1290 "id" SERIAL4 PRIMARY KEY,
1291 "published" TIMESTAMPTZ NOT NULL,
1292 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1293 "include_all_members" BOOLEAN NOT NULL,
1294 "sent" TIMESTAMPTZ,
1295 "subject" TEXT NOT NULL,
1296 "content" TEXT NOT NULL );
1297 CREATE INDEX "newsletter_unit_id_idx" ON "newsletter" ("unit_id", "published");
1298 CREATE INDEX "newsletter_all_units_published_idx" ON "newsletter" ("published") WHERE "unit_id" ISNULL;
1299 CREATE INDEX "newsletter_published_idx" ON "newsletter" ("published");
1301 COMMENT ON TABLE "newsletter" IS 'Contains newsletters created by administrators to be sent out and for further reference';
1303 COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter is to be sent out (and made available in the frontend)';
1304 COMMENT ON COLUMN "newsletter"."unit_id" IS 'If set, only members with voting right in the given unit are considered to be recipients';
1305 COMMENT ON COLUMN "newsletter"."include_all_members" IS 'TRUE = include all members regardless of their ''disable_notifications'' setting';
1306 COMMENT ON COLUMN "newsletter"."sent" IS 'Timestamp when the newsletter has been mailed out';
1307 COMMENT ON COLUMN "newsletter"."subject" IS 'Subject line (e.g. to be used for the email)';
1308 COMMENT ON COLUMN "newsletter"."content" IS 'Plain text content of the newsletter';
1312 ----------------------------------------------
1313 -- Writing of history entries and event log --
1314 ----------------------------------------------
1317 CREATE FUNCTION "write_member_history_trigger"()
1318 RETURNS TRIGGER
1319 LANGUAGE 'plpgsql' VOLATILE AS $$
1320 BEGIN
1321 IF
1322 ( NEW."active" != OLD."active" OR
1323 NEW."name" != OLD."name" ) AND
1324 OLD."activated" NOTNULL
1325 THEN
1326 INSERT INTO "member_history"
1327 ("member_id", "active", "name")
1328 VALUES (NEW."id", OLD."active", OLD."name");
1329 END IF;
1330 RETURN NULL;
1331 END;
1332 $$;
1334 CREATE TRIGGER "write_member_history"
1335 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1336 "write_member_history_trigger"();
1338 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1339 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1342 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1343 RETURNS TRIGGER
1344 LANGUAGE 'plpgsql' VOLATILE AS $$
1345 BEGIN
1346 IF NEW."state" != OLD."state" THEN
1347 INSERT INTO "event" ("event", "issue_id", "state")
1348 VALUES ('issue_state_changed', NEW."id", NEW."state");
1349 END IF;
1350 RETURN NULL;
1351 END;
1352 $$;
1354 CREATE TRIGGER "write_event_issue_state_changed"
1355 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1356 "write_event_issue_state_changed_trigger"();
1358 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1359 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1362 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1363 RETURNS TRIGGER
1364 LANGUAGE 'plpgsql' VOLATILE AS $$
1365 DECLARE
1366 "initiative_row" "initiative"%ROWTYPE;
1367 "issue_row" "issue"%ROWTYPE;
1368 "event_v" "event_type";
1369 BEGIN
1370 SELECT * INTO "initiative_row" FROM "initiative"
1371 WHERE "id" = NEW."initiative_id";
1372 SELECT * INTO "issue_row" FROM "issue"
1373 WHERE "id" = "initiative_row"."issue_id";
1374 IF EXISTS (
1375 SELECT NULL FROM "draft"
1376 WHERE "initiative_id" = NEW."initiative_id"
1377 AND "id" != NEW."id"
1378 ) THEN
1379 "event_v" := 'new_draft_created';
1380 ELSE
1381 IF EXISTS (
1382 SELECT NULL FROM "initiative"
1383 WHERE "issue_id" = "initiative_row"."issue_id"
1384 AND "id" != "initiative_row"."id"
1385 ) THEN
1386 "event_v" := 'initiative_created_in_existing_issue';
1387 ELSE
1388 "event_v" := 'initiative_created_in_new_issue';
1389 END IF;
1390 END IF;
1391 INSERT INTO "event" (
1392 "event", "member_id",
1393 "issue_id", "state", "initiative_id", "draft_id"
1394 ) VALUES (
1395 "event_v",
1396 NEW."author_id",
1397 "initiative_row"."issue_id",
1398 "issue_row"."state",
1399 "initiative_row"."id",
1400 NEW."id" );
1401 RETURN NULL;
1402 END;
1403 $$;
1405 CREATE TRIGGER "write_event_initiative_or_draft_created"
1406 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1407 "write_event_initiative_or_draft_created_trigger"();
1409 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1410 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1413 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1414 RETURNS TRIGGER
1415 LANGUAGE 'plpgsql' VOLATILE AS $$
1416 DECLARE
1417 "issue_row" "issue"%ROWTYPE;
1418 "draft_id_v" "draft"."id"%TYPE;
1419 BEGIN
1420 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1421 SELECT * INTO "issue_row" FROM "issue"
1422 WHERE "id" = NEW."issue_id";
1423 SELECT "id" INTO "draft_id_v" FROM "current_draft"
1424 WHERE "initiative_id" = NEW."id";
1425 INSERT INTO "event" (
1426 "event", "member_id", "issue_id", "state", "initiative_id", "draft_id"
1427 ) VALUES (
1428 'initiative_revoked',
1429 NEW."revoked_by_member_id",
1430 NEW."issue_id",
1431 "issue_row"."state",
1432 NEW."id",
1433 "draft_id_v");
1434 END IF;
1435 RETURN NULL;
1436 END;
1437 $$;
1439 CREATE TRIGGER "write_event_initiative_revoked"
1440 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1441 "write_event_initiative_revoked_trigger"();
1443 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1444 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1447 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1448 RETURNS TRIGGER
1449 LANGUAGE 'plpgsql' VOLATILE AS $$
1450 DECLARE
1451 "initiative_row" "initiative"%ROWTYPE;
1452 "issue_row" "issue"%ROWTYPE;
1453 BEGIN
1454 SELECT * INTO "initiative_row" FROM "initiative"
1455 WHERE "id" = NEW."initiative_id";
1456 SELECT * INTO "issue_row" FROM "issue"
1457 WHERE "id" = "initiative_row"."issue_id";
1458 INSERT INTO "event" (
1459 "event", "member_id",
1460 "issue_id", "state", "initiative_id", "suggestion_id"
1461 ) VALUES (
1462 'suggestion_created',
1463 NEW."author_id",
1464 "initiative_row"."issue_id",
1465 "issue_row"."state",
1466 "initiative_row"."id",
1467 NEW."id" );
1468 RETURN NULL;
1469 END;
1470 $$;
1472 CREATE TRIGGER "write_event_suggestion_created"
1473 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1474 "write_event_suggestion_created_trigger"();
1476 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1477 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1481 ----------------------------
1482 -- Additional constraints --
1483 ----------------------------
1486 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1487 RETURNS TRIGGER
1488 LANGUAGE 'plpgsql' VOLATILE AS $$
1489 BEGIN
1490 IF NOT EXISTS (
1491 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1492 ) THEN
1493 RAISE EXCEPTION 'Cannot create issue without an initial initiative.' USING
1494 ERRCODE = 'integrity_constraint_violation',
1495 HINT = 'Create issue, initiative, and draft within the same transaction.';
1496 END IF;
1497 RETURN NULL;
1498 END;
1499 $$;
1501 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1502 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1503 FOR EACH ROW EXECUTE PROCEDURE
1504 "issue_requires_first_initiative_trigger"();
1506 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1507 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1510 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1511 RETURNS TRIGGER
1512 LANGUAGE 'plpgsql' VOLATILE AS $$
1513 DECLARE
1514 "reference_lost" BOOLEAN;
1515 BEGIN
1516 IF TG_OP = 'DELETE' THEN
1517 "reference_lost" := TRUE;
1518 ELSE
1519 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1520 END IF;
1521 IF
1522 "reference_lost" AND NOT EXISTS (
1523 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1525 THEN
1526 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1527 END IF;
1528 RETURN NULL;
1529 END;
1530 $$;
1532 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1533 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1534 FOR EACH ROW EXECUTE PROCEDURE
1535 "last_initiative_deletes_issue_trigger"();
1537 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1538 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1541 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1542 RETURNS TRIGGER
1543 LANGUAGE 'plpgsql' VOLATILE AS $$
1544 BEGIN
1545 IF NOT EXISTS (
1546 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1547 ) THEN
1548 RAISE EXCEPTION 'Cannot create initiative without an initial draft.' USING
1549 ERRCODE = 'integrity_constraint_violation',
1550 HINT = 'Create issue, initiative and draft within the same transaction.';
1551 END IF;
1552 RETURN NULL;
1553 END;
1554 $$;
1556 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1557 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1558 FOR EACH ROW EXECUTE PROCEDURE
1559 "initiative_requires_first_draft_trigger"();
1561 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1562 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1565 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1566 RETURNS TRIGGER
1567 LANGUAGE 'plpgsql' VOLATILE AS $$
1568 DECLARE
1569 "reference_lost" BOOLEAN;
1570 BEGIN
1571 IF TG_OP = 'DELETE' THEN
1572 "reference_lost" := TRUE;
1573 ELSE
1574 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1575 END IF;
1576 IF
1577 "reference_lost" AND NOT EXISTS (
1578 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1580 THEN
1581 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1582 END IF;
1583 RETURN NULL;
1584 END;
1585 $$;
1587 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1588 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1589 FOR EACH ROW EXECUTE PROCEDURE
1590 "last_draft_deletes_initiative_trigger"();
1592 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1593 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1596 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1597 RETURNS TRIGGER
1598 LANGUAGE 'plpgsql' VOLATILE AS $$
1599 BEGIN
1600 IF NOT EXISTS (
1601 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1602 ) THEN
1603 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.' USING
1604 ERRCODE = 'integrity_constraint_violation',
1605 HINT = 'Create suggestion and opinion within the same transaction.';
1606 END IF;
1607 RETURN NULL;
1608 END;
1609 $$;
1611 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1612 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1613 FOR EACH ROW EXECUTE PROCEDURE
1614 "suggestion_requires_first_opinion_trigger"();
1616 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1617 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1620 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1621 RETURNS TRIGGER
1622 LANGUAGE 'plpgsql' VOLATILE AS $$
1623 DECLARE
1624 "reference_lost" BOOLEAN;
1625 BEGIN
1626 IF TG_OP = 'DELETE' THEN
1627 "reference_lost" := TRUE;
1628 ELSE
1629 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1630 END IF;
1631 IF
1632 "reference_lost" AND NOT EXISTS (
1633 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1635 THEN
1636 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1637 END IF;
1638 RETURN NULL;
1639 END;
1640 $$;
1642 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1643 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1644 FOR EACH ROW EXECUTE PROCEDURE
1645 "last_opinion_deletes_suggestion_trigger"();
1647 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1648 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1651 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()
1652 RETURNS TRIGGER
1653 LANGUAGE 'plpgsql' VOLATILE AS $$
1654 BEGIN
1655 DELETE FROM "direct_voter"
1656 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1657 RETURN NULL;
1658 END;
1659 $$;
1661 CREATE TRIGGER "non_voter_deletes_direct_voter"
1662 AFTER INSERT OR UPDATE ON "non_voter"
1663 FOR EACH ROW EXECUTE PROCEDURE
1664 "non_voter_deletes_direct_voter_trigger"();
1666 COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';
1667 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")';
1670 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()
1671 RETURNS TRIGGER
1672 LANGUAGE 'plpgsql' VOLATILE AS $$
1673 BEGIN
1674 DELETE FROM "non_voter"
1675 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1676 RETURN NULL;
1677 END;
1678 $$;
1680 CREATE TRIGGER "direct_voter_deletes_non_voter"
1681 AFTER INSERT OR UPDATE ON "direct_voter"
1682 FOR EACH ROW EXECUTE PROCEDURE
1683 "direct_voter_deletes_non_voter_trigger"();
1685 COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';
1686 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")';
1689 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()
1690 RETURNS TRIGGER
1691 LANGUAGE 'plpgsql' VOLATILE AS $$
1692 BEGIN
1693 IF NEW."comment" ISNULL THEN
1694 NEW."comment_changed" := NULL;
1695 NEW."formatting_engine" := NULL;
1696 END IF;
1697 RETURN NEW;
1698 END;
1699 $$;
1701 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"
1702 BEFORE INSERT OR UPDATE ON "direct_voter"
1703 FOR EACH ROW EXECUTE PROCEDURE
1704 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();
1706 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"';
1707 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.';
1710 ---------------------------------------------------------------
1711 -- Ensure that votes are not modified when issues are closed --
1712 ---------------------------------------------------------------
1714 -- NOTE: Frontends should ensure this anyway, but in case of programming
1715 -- errors the following triggers ensure data integrity.
1718 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1719 RETURNS TRIGGER
1720 LANGUAGE 'plpgsql' VOLATILE AS $$
1721 DECLARE
1722 "issue_id_v" "issue"."id"%TYPE;
1723 "issue_row" "issue"%ROWTYPE;
1724 BEGIN
1725 IF EXISTS (
1726 SELECT NULL FROM "temporary_transaction_data"
1727 WHERE "txid" = txid_current()
1728 AND "key" = 'override_protection_triggers'
1729 AND "value" = TRUE::TEXT
1730 ) THEN
1731 RETURN NULL;
1732 END IF;
1733 IF TG_OP = 'DELETE' THEN
1734 "issue_id_v" := OLD."issue_id";
1735 ELSE
1736 "issue_id_v" := NEW."issue_id";
1737 END IF;
1738 SELECT INTO "issue_row" * FROM "issue"
1739 WHERE "id" = "issue_id_v" FOR SHARE;
1740 IF (
1741 "issue_row"."closed" NOTNULL OR (
1742 "issue_row"."state" = 'voting' AND
1743 "issue_row"."phase_finished" NOTNULL
1745 ) THEN
1746 IF
1747 TG_RELID = 'direct_voter'::regclass AND
1748 TG_OP = 'UPDATE'
1749 THEN
1750 IF
1751 OLD."issue_id" = NEW."issue_id" AND
1752 OLD."member_id" = NEW."member_id" AND
1753 OLD."weight" = NEW."weight"
1754 THEN
1755 RETURN NULL; -- allows changing of voter comment
1756 END IF;
1757 END IF;
1758 RAISE EXCEPTION 'Tried to modify data after voting has been closed.' USING
1759 ERRCODE = 'integrity_constraint_violation';
1760 END IF;
1761 RETURN NULL;
1762 END;
1763 $$;
1765 CREATE TRIGGER "forbid_changes_on_closed_issue"
1766 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1767 FOR EACH ROW EXECUTE PROCEDURE
1768 "forbid_changes_on_closed_issue_trigger"();
1770 CREATE TRIGGER "forbid_changes_on_closed_issue"
1771 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1772 FOR EACH ROW EXECUTE PROCEDURE
1773 "forbid_changes_on_closed_issue_trigger"();
1775 CREATE TRIGGER "forbid_changes_on_closed_issue"
1776 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1777 FOR EACH ROW EXECUTE PROCEDURE
1778 "forbid_changes_on_closed_issue_trigger"();
1780 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"';
1781 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';
1782 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';
1783 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';
1787 --------------------------------------------------------------------
1788 -- Auto-retrieval of fields only needed for referential integrity --
1789 --------------------------------------------------------------------
1792 CREATE FUNCTION "autofill_issue_id_trigger"()
1793 RETURNS TRIGGER
1794 LANGUAGE 'plpgsql' VOLATILE AS $$
1795 BEGIN
1796 IF NEW."issue_id" ISNULL THEN
1797 SELECT "issue_id" INTO NEW."issue_id"
1798 FROM "initiative" WHERE "id" = NEW."initiative_id";
1799 END IF;
1800 RETURN NEW;
1801 END;
1802 $$;
1804 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1805 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1807 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1808 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1810 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1811 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1812 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1815 CREATE FUNCTION "autofill_initiative_id_trigger"()
1816 RETURNS TRIGGER
1817 LANGUAGE 'plpgsql' VOLATILE AS $$
1818 BEGIN
1819 IF NEW."initiative_id" ISNULL THEN
1820 SELECT "initiative_id" INTO NEW."initiative_id"
1821 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1822 END IF;
1823 RETURN NEW;
1824 END;
1825 $$;
1827 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1828 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1830 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1831 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1835 -----------------------------------------------------
1836 -- Automatic calculation of certain default values --
1837 -----------------------------------------------------
1840 CREATE FUNCTION "copy_timings_trigger"()
1841 RETURNS TRIGGER
1842 LANGUAGE 'plpgsql' VOLATILE AS $$
1843 DECLARE
1844 "policy_row" "policy"%ROWTYPE;
1845 BEGIN
1846 SELECT * INTO "policy_row" FROM "policy"
1847 WHERE "id" = NEW."policy_id";
1848 IF NEW."min_admission_time" ISNULL THEN
1849 NEW."min_admission_time" := "policy_row"."min_admission_time";
1850 END IF;
1851 IF NEW."max_admission_time" ISNULL THEN
1852 NEW."max_admission_time" := "policy_row"."max_admission_time";
1853 END IF;
1854 IF NEW."discussion_time" ISNULL THEN
1855 NEW."discussion_time" := "policy_row"."discussion_time";
1856 END IF;
1857 IF NEW."verification_time" ISNULL THEN
1858 NEW."verification_time" := "policy_row"."verification_time";
1859 END IF;
1860 IF NEW."voting_time" ISNULL THEN
1861 NEW."voting_time" := "policy_row"."voting_time";
1862 END IF;
1863 RETURN NEW;
1864 END;
1865 $$;
1867 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1868 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1870 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1871 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1874 CREATE FUNCTION "default_for_draft_id_trigger"()
1875 RETURNS TRIGGER
1876 LANGUAGE 'plpgsql' VOLATILE AS $$
1877 BEGIN
1878 IF NEW."draft_id" ISNULL THEN
1879 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1880 WHERE "initiative_id" = NEW."initiative_id";
1881 END IF;
1882 RETURN NEW;
1883 END;
1884 $$;
1886 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
1887 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1888 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1889 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1891 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
1892 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';
1893 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';
1897 ----------------------------------------
1898 -- Automatic creation of dependencies --
1899 ----------------------------------------
1902 CREATE FUNCTION "autocreate_interest_trigger"()
1903 RETURNS TRIGGER
1904 LANGUAGE 'plpgsql' VOLATILE AS $$
1905 BEGIN
1906 IF NOT EXISTS (
1907 SELECT NULL FROM "initiative" JOIN "interest"
1908 ON "initiative"."issue_id" = "interest"."issue_id"
1909 WHERE "initiative"."id" = NEW."initiative_id"
1910 AND "interest"."member_id" = NEW."member_id"
1911 ) THEN
1912 BEGIN
1913 INSERT INTO "interest" ("issue_id", "member_id")
1914 SELECT "issue_id", NEW."member_id"
1915 FROM "initiative" WHERE "id" = NEW."initiative_id";
1916 EXCEPTION WHEN unique_violation THEN END;
1917 END IF;
1918 RETURN NEW;
1919 END;
1920 $$;
1922 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1923 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1925 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1926 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';
1929 CREATE FUNCTION "autocreate_supporter_trigger"()
1930 RETURNS TRIGGER
1931 LANGUAGE 'plpgsql' VOLATILE AS $$
1932 BEGIN
1933 IF NOT EXISTS (
1934 SELECT NULL FROM "suggestion" JOIN "supporter"
1935 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1936 WHERE "suggestion"."id" = NEW."suggestion_id"
1937 AND "supporter"."member_id" = NEW."member_id"
1938 ) THEN
1939 BEGIN
1940 INSERT INTO "supporter" ("initiative_id", "member_id")
1941 SELECT "initiative_id", NEW."member_id"
1942 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1943 EXCEPTION WHEN unique_violation THEN END;
1944 END IF;
1945 RETURN NEW;
1946 END;
1947 $$;
1949 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1950 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1952 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1953 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.';
1957 ------------------------------------------
1958 -- Views and helper functions for views --
1959 ------------------------------------------
1962 CREATE VIEW "member_eligible_to_be_notified" AS
1963 SELECT * FROM "member"
1964 WHERE "activated" NOTNULL AND "locked" = FALSE;
1966 COMMENT ON VIEW "member_eligible_to_be_notified" IS 'Filtered "member" table containing only activated and non-locked members (used as helper view for "member_to_notify" and "newsletter_to_send")';
1969 CREATE VIEW "member_to_notify" AS
1970 SELECT * FROM "member_eligible_to_be_notified"
1971 WHERE "disable_notifications" = FALSE;
1973 COMMENT ON VIEW "member_to_notify" IS 'Filtered "member" table containing only members that are eligible to and wish to receive notifications; NOTE: "notify_email" may still be NULL and might need to be checked by frontend (this allows other means of messaging)';
1976 CREATE VIEW "unit_delegation" AS
1977 SELECT
1978 "unit"."id" AS "unit_id",
1979 "delegation"."id",
1980 "delegation"."truster_id",
1981 "delegation"."trustee_id",
1982 "delegation"."scope"
1983 FROM "unit"
1984 JOIN "delegation"
1985 ON "delegation"."unit_id" = "unit"."id"
1986 JOIN "member"
1987 ON "delegation"."truster_id" = "member"."id"
1988 JOIN "privilege"
1989 ON "delegation"."unit_id" = "privilege"."unit_id"
1990 AND "delegation"."truster_id" = "privilege"."member_id"
1991 WHERE "member"."active" AND "privilege"."voting_right";
1993 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1996 CREATE VIEW "area_delegation" AS
1997 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1998 "area"."id" AS "area_id",
1999 "delegation"."id",
2000 "delegation"."truster_id",
2001 "delegation"."trustee_id",
2002 "delegation"."scope"
2003 FROM "area"
2004 JOIN "delegation"
2005 ON "delegation"."unit_id" = "area"."unit_id"
2006 OR "delegation"."area_id" = "area"."id"
2007 JOIN "member"
2008 ON "delegation"."truster_id" = "member"."id"
2009 JOIN "privilege"
2010 ON "area"."unit_id" = "privilege"."unit_id"
2011 AND "delegation"."truster_id" = "privilege"."member_id"
2012 WHERE "member"."active" AND "privilege"."voting_right"
2013 ORDER BY
2014 "area"."id",
2015 "delegation"."truster_id",
2016 "delegation"."scope" DESC;
2018 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
2021 CREATE VIEW "issue_delegation" AS
2022 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
2023 "issue"."id" AS "issue_id",
2024 "delegation"."id",
2025 "delegation"."truster_id",
2026 "delegation"."trustee_id",
2027 "delegation"."scope"
2028 FROM "issue"
2029 JOIN "area"
2030 ON "area"."id" = "issue"."area_id"
2031 JOIN "delegation"
2032 ON "delegation"."unit_id" = "area"."unit_id"
2033 OR "delegation"."area_id" = "area"."id"
2034 OR "delegation"."issue_id" = "issue"."id"
2035 JOIN "member"
2036 ON "delegation"."truster_id" = "member"."id"
2037 JOIN "privilege"
2038 ON "area"."unit_id" = "privilege"."unit_id"
2039 AND "delegation"."truster_id" = "privilege"."member_id"
2040 WHERE "member"."active" AND "privilege"."voting_right"
2041 ORDER BY
2042 "issue"."id",
2043 "delegation"."truster_id",
2044 "delegation"."scope" DESC;
2046 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
2049 CREATE FUNCTION "membership_weight_with_skipping"
2050 ( "area_id_p" "area"."id"%TYPE,
2051 "member_id_p" "member"."id"%TYPE,
2052 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
2053 RETURNS INT4
2054 LANGUAGE 'plpgsql' STABLE AS $$
2055 DECLARE
2056 "sum_v" INT4;
2057 "delegation_row" "area_delegation"%ROWTYPE;
2058 BEGIN
2059 "sum_v" := 1;
2060 FOR "delegation_row" IN
2061 SELECT "area_delegation".*
2062 FROM "area_delegation" LEFT JOIN "membership"
2063 ON "membership"."area_id" = "area_id_p"
2064 AND "membership"."member_id" = "area_delegation"."truster_id"
2065 WHERE "area_delegation"."area_id" = "area_id_p"
2066 AND "area_delegation"."trustee_id" = "member_id_p"
2067 AND "membership"."member_id" ISNULL
2068 LOOP
2069 IF NOT
2070 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
2071 THEN
2072 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
2073 "area_id_p",
2074 "delegation_row"."truster_id",
2075 "skip_member_ids_p" || "delegation_row"."truster_id"
2076 );
2077 END IF;
2078 END LOOP;
2079 RETURN "sum_v";
2080 END;
2081 $$;
2083 COMMENT ON FUNCTION "membership_weight_with_skipping"
2084 ( "area"."id"%TYPE,
2085 "member"."id"%TYPE,
2086 INT4[] )
2087 IS 'Helper function for "membership_weight" function';
2090 CREATE FUNCTION "membership_weight"
2091 ( "area_id_p" "area"."id"%TYPE,
2092 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
2093 RETURNS INT4
2094 LANGUAGE 'plpgsql' STABLE AS $$
2095 BEGIN
2096 RETURN "membership_weight_with_skipping"(
2097 "area_id_p",
2098 "member_id_p",
2099 ARRAY["member_id_p"]
2100 );
2101 END;
2102 $$;
2104 COMMENT ON FUNCTION "membership_weight"
2105 ( "area"."id"%TYPE,
2106 "member"."id"%TYPE )
2107 IS 'Calculates the potential voting weight of a member in a given area';
2110 CREATE VIEW "member_count_view" AS
2111 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
2113 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
2116 CREATE VIEW "unit_member_count" AS
2117 SELECT
2118 "unit"."id" AS "unit_id",
2119 count("member"."id") AS "member_count"
2120 FROM "unit"
2121 LEFT JOIN "privilege"
2122 ON "privilege"."unit_id" = "unit"."id"
2123 AND "privilege"."voting_right"
2124 LEFT JOIN "member"
2125 ON "member"."id" = "privilege"."member_id"
2126 AND "member"."active"
2127 GROUP BY "unit"."id";
2129 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
2132 CREATE VIEW "area_member_count" AS
2133 SELECT
2134 "area"."id" AS "area_id",
2135 count("member"."id") AS "direct_member_count",
2136 coalesce(
2137 sum(
2138 CASE WHEN "member"."id" NOTNULL THEN
2139 "membership_weight"("area"."id", "member"."id")
2140 ELSE 0 END
2142 ) AS "member_weight"
2143 FROM "area"
2144 LEFT JOIN "membership"
2145 ON "area"."id" = "membership"."area_id"
2146 LEFT JOIN "privilege"
2147 ON "privilege"."unit_id" = "area"."unit_id"
2148 AND "privilege"."member_id" = "membership"."member_id"
2149 AND "privilege"."voting_right"
2150 LEFT JOIN "member"
2151 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
2152 AND "member"."active"
2153 GROUP BY "area"."id";
2155 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';
2158 CREATE VIEW "opening_draft" AS
2159 SELECT "draft".* FROM (
2160 SELECT
2161 "initiative"."id" AS "initiative_id",
2162 min("draft"."id") AS "draft_id"
2163 FROM "initiative" JOIN "draft"
2164 ON "initiative"."id" = "draft"."initiative_id"
2165 GROUP BY "initiative"."id"
2166 ) AS "subquery"
2167 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
2169 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
2172 CREATE VIEW "current_draft" AS
2173 SELECT "draft".* FROM (
2174 SELECT
2175 "initiative"."id" AS "initiative_id",
2176 max("draft"."id") AS "draft_id"
2177 FROM "initiative" JOIN "draft"
2178 ON "initiative"."id" = "draft"."initiative_id"
2179 GROUP BY "initiative"."id"
2180 ) AS "subquery"
2181 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
2183 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
2186 CREATE VIEW "critical_opinion" AS
2187 SELECT * FROM "opinion"
2188 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
2189 OR ("degree" = -2 AND "fulfilled" = TRUE);
2191 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
2194 CREATE VIEW "issue_supporter_in_admission_state" AS
2195 SELECT DISTINCT -- TODO: DISTINCT needed?
2196 "area"."unit_id",
2197 "issue"."area_id",
2198 "issue"."id" AS "issue_id",
2199 "supporter"."member_id",
2200 "direct_interest_snapshot"."weight"
2201 FROM "issue"
2202 JOIN "area" ON "area"."id" = "issue"."area_id"
2203 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
2204 JOIN "direct_interest_snapshot"
2205 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
2206 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
2207 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
2208 WHERE "issue"."state" = 'admission'::"issue_state";
2210 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';
2213 CREATE VIEW "initiative_suggestion_order_calculation" AS
2214 SELECT
2215 "initiative"."id" AS "initiative_id",
2216 ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final"
2217 FROM "initiative" JOIN "issue"
2218 ON "initiative"."issue_id" = "issue"."id"
2219 WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL)
2220 OR ("initiative"."final_suggestion_order_calculated" = FALSE);
2222 COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';
2224 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';
2227 CREATE VIEW "individual_suggestion_ranking" AS
2228 SELECT
2229 "opinion"."initiative_id",
2230 "opinion"."member_id",
2231 "direct_interest_snapshot"."weight",
2232 CASE WHEN
2233 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
2234 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
2235 THEN 1 ELSE
2236 CASE WHEN
2237 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
2238 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
2239 THEN 2 ELSE
2240 CASE WHEN
2241 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
2242 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
2243 THEN 3 ELSE 4 END
2244 END
2245 END AS "preference",
2246 "opinion"."suggestion_id"
2247 FROM "opinion"
2248 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
2249 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2250 JOIN "direct_interest_snapshot"
2251 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
2252 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
2253 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
2255 COMMENT ON VIEW "individual_suggestion_ranking" IS 'Helper view for "lf_update_suggestion_order" to allow a proportional ordering of suggestions within an initiative';
2258 CREATE VIEW "battle_participant" AS
2259 SELECT "initiative"."id", "initiative"."issue_id"
2260 FROM "issue" JOIN "initiative"
2261 ON "issue"."id" = "initiative"."issue_id"
2262 WHERE "initiative"."admitted"
2263 UNION ALL
2264 SELECT NULL, "id" AS "issue_id"
2265 FROM "issue";
2267 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
2270 CREATE VIEW "battle_view" AS
2271 SELECT
2272 "issue"."id" AS "issue_id",
2273 "winning_initiative"."id" AS "winning_initiative_id",
2274 "losing_initiative"."id" AS "losing_initiative_id",
2275 sum(
2276 CASE WHEN
2277 coalesce("better_vote"."grade", 0) >
2278 coalesce("worse_vote"."grade", 0)
2279 THEN "direct_voter"."weight" ELSE 0 END
2280 ) AS "count"
2281 FROM "issue"
2282 LEFT JOIN "direct_voter"
2283 ON "issue"."id" = "direct_voter"."issue_id"
2284 JOIN "battle_participant" AS "winning_initiative"
2285 ON "issue"."id" = "winning_initiative"."issue_id"
2286 JOIN "battle_participant" AS "losing_initiative"
2287 ON "issue"."id" = "losing_initiative"."issue_id"
2288 LEFT JOIN "vote" AS "better_vote"
2289 ON "direct_voter"."member_id" = "better_vote"."member_id"
2290 AND "winning_initiative"."id" = "better_vote"."initiative_id"
2291 LEFT JOIN "vote" AS "worse_vote"
2292 ON "direct_voter"."member_id" = "worse_vote"."member_id"
2293 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
2294 WHERE "issue"."state" = 'voting'
2295 AND "issue"."phase_finished" NOTNULL
2296 AND (
2297 "winning_initiative"."id" != "losing_initiative"."id" OR
2298 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
2299 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
2300 GROUP BY
2301 "issue"."id",
2302 "winning_initiative"."id",
2303 "losing_initiative"."id";
2305 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';
2308 CREATE VIEW "expired_session" AS
2309 SELECT * FROM "session" WHERE now() > "expiry";
2311 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
2312 DELETE FROM "session" WHERE "ident" = OLD."ident";
2314 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
2315 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
2318 CREATE VIEW "open_issue" AS
2319 SELECT * FROM "issue" WHERE "closed" ISNULL;
2321 COMMENT ON VIEW "open_issue" IS 'All open issues';
2324 CREATE VIEW "member_contingent" AS
2325 SELECT
2326 "member"."id" AS "member_id",
2327 "contingent"."polling",
2328 "contingent"."time_frame",
2329 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
2331 SELECT count(1) FROM "draft"
2332 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
2333 WHERE "draft"."author_id" = "member"."id"
2334 AND "initiative"."polling" = "contingent"."polling"
2335 AND "draft"."created" > now() - "contingent"."time_frame"
2336 ) + (
2337 SELECT count(1) FROM "suggestion"
2338 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
2339 WHERE "suggestion"."author_id" = "member"."id"
2340 AND "contingent"."polling" = FALSE
2341 AND "suggestion"."created" > now() - "contingent"."time_frame"
2343 ELSE NULL END AS "text_entry_count",
2344 "contingent"."text_entry_limit",
2345 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
2346 SELECT count(1) FROM "opening_draft" AS "draft"
2347 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
2348 WHERE "draft"."author_id" = "member"."id"
2349 AND "initiative"."polling" = "contingent"."polling"
2350 AND "draft"."created" > now() - "contingent"."time_frame"
2351 ) ELSE NULL END AS "initiative_count",
2352 "contingent"."initiative_limit"
2353 FROM "member" CROSS JOIN "contingent";
2355 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
2357 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
2358 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
2361 CREATE VIEW "member_contingent_left" AS
2362 SELECT
2363 "member_id",
2364 "polling",
2365 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
2366 max("initiative_limit" - "initiative_count") AS "initiatives_left"
2367 FROM "member_contingent" GROUP BY "member_id", "polling";
2369 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.';
2372 CREATE VIEW "event_for_notification" AS
2373 SELECT
2374 "member"."id" AS "recipient_id",
2375 "event".*
2376 FROM "member" CROSS JOIN "event"
2377 JOIN "issue" ON "issue"."id" = "event"."issue_id"
2378 JOIN "area" ON "area"."id" = "issue"."area_id"
2379 LEFT JOIN "privilege" ON
2380 "privilege"."member_id" = "member"."id" AND
2381 "privilege"."unit_id" = "area"."unit_id" AND
2382 "privilege"."voting_right" = TRUE
2383 LEFT JOIN "subscription" ON
2384 "subscription"."member_id" = "member"."id" AND
2385 "subscription"."unit_id" = "area"."unit_id"
2386 LEFT JOIN "ignored_area" ON
2387 "ignored_area"."member_id" = "member"."id" AND
2388 "ignored_area"."area_id" = "issue"."area_id"
2389 LEFT JOIN "interest" ON
2390 "interest"."member_id" = "member"."id" AND
2391 "interest"."issue_id" = "event"."issue_id"
2392 LEFT JOIN "supporter" ON
2393 "supporter"."member_id" = "member"."id" AND
2394 "supporter"."initiative_id" = "event"."initiative_id"
2395 WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL)
2396 AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
2397 AND (
2398 "event"."event" = 'issue_state_changed'::"event_type" OR
2399 ( "event"."event" = 'initiative_revoked'::"event_type" AND
2400 "supporter"."member_id" NOTNULL ) );
2402 COMMENT ON VIEW "event_for_notification" IS 'Entries of the "event" table which are of interest for a particular notification mail recipient';
2404 COMMENT ON COLUMN "event_for_notification"."recipient_id" IS 'member_id of the recipient of a notification mail';
2407 CREATE VIEW "updated_initiative" AS
2408 SELECT
2409 "supporter"."member_id" AS "recipient_id",
2410 FALSE AS "featured",
2411 "supporter"."initiative_id"
2412 FROM "supporter"
2413 JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id"
2414 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2415 LEFT JOIN "notification_initiative_sent" AS "sent" ON
2416 "sent"."member_id" = "supporter"."member_id" AND
2417 "sent"."initiative_id" = "supporter"."initiative_id"
2418 LEFT JOIN "ignored_initiative" ON
2419 "ignored_initiative"."member_id" = "supporter"."member_id" AND
2420 "ignored_initiative"."initiative_id" = "supporter"."initiative_id"
2421 WHERE "issue"."state" IN ('admission', 'discussion')
2422 AND "initiative"."revoked" ISNULL
2423 AND "ignored_initiative"."member_id" ISNULL
2424 AND (
2425 EXISTS (
2426 SELECT NULL FROM "draft"
2427 LEFT JOIN "ignored_member" ON
2428 "ignored_member"."member_id" = "supporter"."member_id" AND
2429 "ignored_member"."other_member_id" = "draft"."author_id"
2430 WHERE "draft"."initiative_id" = "supporter"."initiative_id"
2431 AND "draft"."id" > "supporter"."draft_id"
2432 AND "ignored_member"."member_id" ISNULL
2433 ) OR EXISTS (
2434 SELECT NULL FROM "suggestion"
2435 LEFT JOIN "opinion" ON
2436 "opinion"."member_id" = "supporter"."member_id" AND
2437 "opinion"."suggestion_id" = "suggestion"."id"
2438 LEFT JOIN "ignored_member" ON
2439 "ignored_member"."member_id" = "supporter"."member_id" AND
2440 "ignored_member"."other_member_id" = "suggestion"."author_id"
2441 WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"
2442 AND "opinion"."member_id" ISNULL
2443 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
2444 AND "ignored_member"."member_id" ISNULL
2446 );
2448 COMMENT ON VIEW "updated_initiative" IS 'Helper view for view "updated_or_featured_initiative"';
2451 CREATE FUNCTION "featured_initiative"
2452 ( "recipient_id_p" "member"."id"%TYPE,
2453 "area_id_p" "area"."id"%TYPE )
2454 RETURNS SETOF "initiative"."id"%TYPE
2455 LANGUAGE 'plpgsql' STABLE AS $$
2456 DECLARE
2457 "counter_v" "member"."notification_counter"%TYPE;
2458 "sample_size_v" "member"."notification_sample_size"%TYPE;
2459 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
2460 "match_v" BOOLEAN;
2461 "member_id_v" "member"."id"%TYPE;
2462 "seed_v" TEXT;
2463 "initiative_id_v" "initiative"."id"%TYPE;
2464 BEGIN
2465 SELECT "notification_counter", "notification_sample_size"
2466 INTO "counter_v", "sample_size_v"
2467 FROM "member" WHERE "id" = "recipient_id_p";
2468 IF COALESCE("sample_size_v" <= 0, TRUE) THEN
2469 RETURN;
2470 END IF;
2471 "initiative_id_ary" := '{}';
2472 LOOP
2473 "match_v" := FALSE;
2474 FOR "member_id_v", "seed_v" IN
2475 SELECT * FROM (
2476 SELECT DISTINCT
2477 "supporter"."member_id",
2478 md5(
2479 "recipient_id_p" || '-' ||
2480 "counter_v" || '-' ||
2481 "area_id_p" || '-' ||
2482 "supporter"."member_id"
2483 ) AS "seed"
2484 FROM "supporter"
2485 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
2486 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2487 WHERE "supporter"."member_id" != "recipient_id_p"
2488 AND "issue"."area_id" = "area_id_p"
2489 AND "issue"."state" IN ('admission', 'discussion', 'verification')
2490 ) AS "subquery"
2491 ORDER BY "seed"
2492 LOOP
2493 SELECT "initiative"."id" INTO "initiative_id_v"
2494 FROM "initiative"
2495 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2496 JOIN "area" ON "area"."id" = "issue"."area_id"
2497 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
2498 LEFT JOIN "supporter" AS "self_support" ON
2499 "self_support"."initiative_id" = "initiative"."id" AND
2500 "self_support"."member_id" = "recipient_id_p"
2501 LEFT JOIN "privilege" ON
2502 "privilege"."member_id" = "recipient_id_p" AND
2503 "privilege"."unit_id" = "area"."unit_id" AND
2504 "privilege"."voting_right" = TRUE
2505 LEFT JOIN "subscription" ON
2506 "subscription"."member_id" = "recipient_id_p" AND
2507 "subscription"."unit_id" = "area"."unit_id"
2508 LEFT JOIN "ignored_initiative" ON
2509 "ignored_initiative"."member_id" = "recipient_id_p" AND
2510 "ignored_initiative"."initiative_id" = "initiative"."id"
2511 WHERE "supporter"."member_id" = "member_id_v"
2512 AND "issue"."area_id" = "area_id_p"
2513 AND "issue"."state" IN ('admission', 'discussion', 'verification')
2514 AND "initiative"."revoked" ISNULL
2515 AND "self_support"."member_id" ISNULL
2516 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
2517 AND (
2518 "privilege"."member_id" NOTNULL OR
2519 "subscription"."member_id" NOTNULL )
2520 AND "ignored_initiative"."member_id" ISNULL
2521 AND NOT EXISTS (
2522 SELECT NULL FROM "draft"
2523 JOIN "ignored_member" ON
2524 "ignored_member"."member_id" = "recipient_id_p" AND
2525 "ignored_member"."other_member_id" = "draft"."author_id"
2526 WHERE "draft"."initiative_id" = "initiative"."id"
2528 ORDER BY md5("seed_v" || '-' || "initiative"."id")
2529 LIMIT 1;
2530 IF FOUND THEN
2531 "match_v" := TRUE;
2532 RETURN NEXT "initiative_id_v";
2533 IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
2534 RETURN;
2535 END IF;
2536 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
2537 END IF;
2538 END LOOP;
2539 EXIT WHEN NOT "match_v";
2540 END LOOP;
2541 RETURN;
2542 END;
2543 $$;
2545 COMMENT ON FUNCTION "featured_initiative"
2546 ( "recipient_id_p" "member"."id"%TYPE,
2547 "area_id_p" "area"."id"%TYPE )
2548 IS 'Helper function for view "updated_or_featured_initiative"';
2551 CREATE VIEW "updated_or_featured_initiative" AS
2552 SELECT
2553 "subquery".*,
2554 NOT EXISTS (
2555 SELECT NULL FROM "initiative" AS "better_initiative"
2556 WHERE "better_initiative"."issue_id" = "initiative"."issue_id"
2557 AND
2558 ( COALESCE("better_initiative"."supporter_count", -1),
2559 -"better_initiative"."id" ) >
2560 ( COALESCE("initiative"."supporter_count", -1),
2561 -"initiative"."id" )
2562 ) AS "leading"
2563 FROM (
2564 SELECT * FROM "updated_initiative"
2565 UNION ALL
2566 SELECT
2567 "member"."id" AS "recipient_id",
2568 TRUE AS "featured",
2569 "featured_initiative_id" AS "initiative_id"
2570 FROM "member" CROSS JOIN "area"
2571 CROSS JOIN LATERAL
2572 "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id"
2573 JOIN "initiative" ON "initiative"."id" = "featured_initiative_id"
2574 ) AS "subquery"
2575 JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id";
2577 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';
2579 COMMENT ON COLUMN "updated_or_featured_initiative"."recipient_id" IS '"id" of the member who receives the notification mail';
2580 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")';
2581 COMMENT ON COLUMN "updated_or_featured_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
2582 COMMENT ON COLUMN "updated_or_featured_initiative"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue';
2585 CREATE VIEW "leading_complement_initiative" AS
2586 SELECT * FROM (
2587 SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id")
2588 "uf_initiative"."recipient_id",
2589 FALSE AS "featured",
2590 "uf_initiative"."initiative_id",
2591 TRUE AS "leading"
2592 FROM "updated_or_featured_initiative" AS "uf_initiative"
2593 JOIN "initiative" AS "uf_initiative_full" ON
2594 "uf_initiative_full"."id" = "uf_initiative"."initiative_id"
2595 JOIN "initiative" ON
2596 "initiative"."issue_id" = "uf_initiative_full"."issue_id"
2597 WHERE "initiative"."revoked" ISNULL
2598 ORDER BY
2599 "uf_initiative"."recipient_id",
2600 "initiative"."issue_id",
2601 "initiative"."supporter_count" DESC,
2602 "initiative"."id"
2603 ) AS "subquery"
2604 WHERE NOT EXISTS (
2605 SELECT NULL FROM "updated_or_featured_initiative" AS "other"
2606 WHERE "other"."recipient_id" = "subquery"."recipient_id"
2607 AND "other"."initiative_id" = "subquery"."initiative_id"
2608 );
2610 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';
2611 COMMENT ON COLUMN "leading_complement_initiative"."featured" IS 'Always FALSE in this view';
2612 COMMENT ON COLUMN "leading_complement_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
2613 COMMENT ON COLUMN "leading_complement_initiative"."leading" IS 'Always TRUE in this view';
2616 CREATE VIEW "unfiltered_initiative_for_notification" AS
2617 SELECT
2618 "subquery".*,
2619 "supporter"."member_id" NOTNULL AS "supported",
2620 CASE WHEN "supporter"."member_id" NOTNULL THEN
2621 EXISTS (
2622 SELECT NULL FROM "draft"
2623 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
2624 AND "draft"."id" > "supporter"."draft_id"
2626 ELSE
2627 EXISTS (
2628 SELECT NULL FROM "draft"
2629 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
2630 AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE)
2632 END AS "new_draft",
2633 CASE WHEN "supporter"."member_id" NOTNULL THEN
2634 ( SELECT count(1) FROM "suggestion"
2635 LEFT JOIN "opinion" ON
2636 "opinion"."member_id" = "supporter"."member_id" AND
2637 "opinion"."suggestion_id" = "suggestion"."id"
2638 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
2639 AND "opinion"."member_id" ISNULL
2640 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
2642 ELSE
2643 ( SELECT count(1) FROM "suggestion"
2644 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
2645 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
2647 END AS "new_suggestion_count"
2648 FROM (
2649 SELECT * FROM "updated_or_featured_initiative"
2650 UNION ALL
2651 SELECT * FROM "leading_complement_initiative"
2652 ) AS "subquery"
2653 LEFT JOIN "supporter" ON
2654 "supporter"."member_id" = "subquery"."recipient_id" AND
2655 "supporter"."initiative_id" = "subquery"."initiative_id"
2656 LEFT JOIN "notification_initiative_sent" AS "sent" ON
2657 "sent"."member_id" = "subquery"."recipient_id" AND
2658 "sent"."initiative_id" = "subquery"."initiative_id";
2660 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';
2662 COMMENT ON COLUMN "unfiltered_initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient';
2663 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)';
2664 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")';
2667 CREATE VIEW "initiative_for_notification" AS
2668 SELECT "unfiltered1".*
2669 FROM "unfiltered_initiative_for_notification" "unfiltered1"
2670 JOIN "initiative" AS "initiative1" ON
2671 "initiative1"."id" = "unfiltered1"."initiative_id"
2672 JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id"
2673 WHERE EXISTS (
2674 SELECT NULL
2675 FROM "unfiltered_initiative_for_notification" "unfiltered2"
2676 JOIN "initiative" AS "initiative2" ON
2677 "initiative2"."id" = "unfiltered2"."initiative_id"
2678 JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id"
2679 WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"
2680 AND "issue1"."area_id" = "issue2"."area_id"
2681 AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 )
2682 );
2684 COMMENT ON VIEW "initiative_for_notification" IS 'Initiatives to be included in a scheduled notification mail';
2686 COMMENT ON COLUMN "initiative_for_notification"."recipient_id" IS '"id" of the member who receives the notification mail';
2687 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")';
2688 COMMENT ON COLUMN "initiative_for_notification"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
2689 COMMENT ON COLUMN "initiative_for_notification"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue';
2690 COMMENT ON COLUMN "initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient';
2691 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)';
2692 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")';
2695 CREATE VIEW "scheduled_notification_to_send" AS
2696 SELECT * FROM (
2697 SELECT
2698 "id" AS "recipient_id",
2699 now() - CASE WHEN "notification_dow" ISNULL THEN
2700 ( "notification_sent"::DATE + CASE
2701 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
2702 THEN 0 ELSE 1 END
2703 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
2704 ELSE
2705 ( "notification_sent"::DATE +
2706 ( 7 + "notification_dow" -
2707 EXTRACT(DOW FROM
2708 ( "notification_sent"::DATE + CASE
2709 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
2710 THEN 0 ELSE 1 END
2711 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
2712 )::INTEGER
2713 ) % 7 +
2714 CASE
2715 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
2716 THEN 0 ELSE 1
2717 END
2718 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
2719 END AS "pending"
2720 FROM (
2721 SELECT
2722 "id",
2723 COALESCE("notification_sent", "activated") AS "notification_sent",
2724 "notification_dow",
2725 "notification_hour"
2726 FROM "member_to_notify"
2727 WHERE "notification_hour" NOTNULL
2728 ) AS "subquery1"
2729 ) AS "subquery2"
2730 WHERE "pending" > '0'::INTERVAL;
2732 COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending';
2734 COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail';
2735 COMMENT ON COLUMN "scheduled_notification_to_send"."pending" IS 'Duration for which the notification mail has already been pending';
2738 CREATE VIEW "newsletter_to_send" AS
2739 SELECT
2740 "member"."id" AS "recipient_id",
2741 "newsletter"."id" AS "newsletter_id",
2742 "newsletter"."published"
2743 FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"
2744 LEFT JOIN "privilege" ON
2745 "privilege"."member_id" = "member"."id" AND
2746 "privilege"."unit_id" = "newsletter"."unit_id" AND
2747 "privilege"."voting_right" = TRUE
2748 LEFT JOIN "subscription" ON
2749 "subscription"."member_id" = "member"."id" AND
2750 "subscription"."unit_id" = "newsletter"."unit_id"
2751 WHERE "newsletter"."published" <= now()
2752 AND "newsletter"."sent" ISNULL
2753 AND (
2754 "member"."disable_notifications" = FALSE OR
2755 "newsletter"."include_all_members" = TRUE )
2756 AND (
2757 "newsletter"."unit_id" ISNULL OR
2758 "privilege"."member_id" NOTNULL OR
2759 "subscription"."member_id" NOTNULL );
2761 COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';
2763 COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter was supposed to be sent out (can be used for ordering)';
2767 ------------------------------------------------------
2768 -- Row set returning function for delegation chains --
2769 ------------------------------------------------------
2772 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2773 ('first', 'intermediate', 'last', 'repetition');
2775 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2778 CREATE TYPE "delegation_chain_row" AS (
2779 "index" INT4,
2780 "member_id" INT4,
2781 "member_valid" BOOLEAN,
2782 "participation" BOOLEAN,
2783 "overridden" BOOLEAN,
2784 "scope_in" "delegation_scope",
2785 "scope_out" "delegation_scope",
2786 "disabled_out" BOOLEAN,
2787 "loop" "delegation_chain_loop_tag" );
2789 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
2791 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2792 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';
2793 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2794 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2795 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2796 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2797 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2800 CREATE FUNCTION "delegation_chain_for_closed_issue"
2801 ( "member_id_p" "member"."id"%TYPE,
2802 "issue_id_p" "issue"."id"%TYPE )
2803 RETURNS SETOF "delegation_chain_row"
2804 LANGUAGE 'plpgsql' STABLE AS $$
2805 DECLARE
2806 "output_row" "delegation_chain_row";
2807 "direct_voter_row" "direct_voter"%ROWTYPE;
2808 "delegating_voter_row" "delegating_voter"%ROWTYPE;
2809 BEGIN
2810 "output_row"."index" := 0;
2811 "output_row"."member_id" := "member_id_p";
2812 "output_row"."member_valid" := TRUE;
2813 "output_row"."participation" := FALSE;
2814 "output_row"."overridden" := FALSE;
2815 "output_row"."disabled_out" := FALSE;
2816 LOOP
2817 SELECT INTO "direct_voter_row" * FROM "direct_voter"
2818 WHERE "issue_id" = "issue_id_p"
2819 AND "member_id" = "output_row"."member_id";
2820 IF "direct_voter_row"."member_id" NOTNULL THEN
2821 "output_row"."participation" := TRUE;
2822 "output_row"."scope_out" := NULL;
2823 "output_row"."disabled_out" := NULL;
2824 RETURN NEXT "output_row";
2825 RETURN;
2826 END IF;
2827 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
2828 WHERE "issue_id" = "issue_id_p"
2829 AND "member_id" = "output_row"."member_id";
2830 IF "delegating_voter_row"."member_id" ISNULL THEN
2831 RETURN;
2832 END IF;
2833 "output_row"."scope_out" := "delegating_voter_row"."scope";
2834 RETURN NEXT "output_row";
2835 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
2836 "output_row"."scope_in" := "output_row"."scope_out";
2837 END LOOP;
2838 END;
2839 $$;
2841 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
2842 ( "member"."id"%TYPE,
2843 "member"."id"%TYPE )
2844 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
2847 CREATE FUNCTION "delegation_chain"
2848 ( "member_id_p" "member"."id"%TYPE,
2849 "unit_id_p" "unit"."id"%TYPE,
2850 "area_id_p" "area"."id"%TYPE,
2851 "issue_id_p" "issue"."id"%TYPE,
2852 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2853 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2854 RETURNS SETOF "delegation_chain_row"
2855 LANGUAGE 'plpgsql' STABLE AS $$
2856 DECLARE
2857 "scope_v" "delegation_scope";
2858 "unit_id_v" "unit"."id"%TYPE;
2859 "area_id_v" "area"."id"%TYPE;
2860 "issue_row" "issue"%ROWTYPE;
2861 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2862 "loop_member_id_v" "member"."id"%TYPE;
2863 "output_row" "delegation_chain_row";
2864 "output_rows" "delegation_chain_row"[];
2865 "simulate_v" BOOLEAN;
2866 "simulate_here_v" BOOLEAN;
2867 "delegation_row" "delegation"%ROWTYPE;
2868 "row_count" INT4;
2869 "i" INT4;
2870 "loop_v" BOOLEAN;
2871 BEGIN
2872 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
2873 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
2874 END IF;
2875 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
2876 "simulate_v" := TRUE;
2877 ELSE
2878 "simulate_v" := FALSE;
2879 END IF;
2880 IF
2881 "unit_id_p" NOTNULL AND
2882 "area_id_p" ISNULL AND
2883 "issue_id_p" ISNULL
2884 THEN
2885 "scope_v" := 'unit';
2886 "unit_id_v" := "unit_id_p";
2887 ELSIF
2888 "unit_id_p" ISNULL AND
2889 "area_id_p" NOTNULL AND
2890 "issue_id_p" ISNULL
2891 THEN
2892 "scope_v" := 'area';
2893 "area_id_v" := "area_id_p";
2894 SELECT "unit_id" INTO "unit_id_v"
2895 FROM "area" WHERE "id" = "area_id_v";
2896 ELSIF
2897 "unit_id_p" ISNULL AND
2898 "area_id_p" ISNULL AND
2899 "issue_id_p" NOTNULL
2900 THEN
2901 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
2902 IF "issue_row"."id" ISNULL THEN
2903 RETURN;
2904 END IF;
2905 IF "issue_row"."closed" NOTNULL THEN
2906 IF "simulate_v" THEN
2907 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
2908 END IF;
2909 FOR "output_row" IN
2910 SELECT * FROM
2911 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
2912 LOOP
2913 RETURN NEXT "output_row";
2914 END LOOP;
2915 RETURN;
2916 END IF;
2917 "scope_v" := 'issue';
2918 SELECT "area_id" INTO "area_id_v"
2919 FROM "issue" WHERE "id" = "issue_id_p";
2920 SELECT "unit_id" INTO "unit_id_v"
2921 FROM "area" WHERE "id" = "area_id_v";
2922 ELSE
2923 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2924 END IF;
2925 "visited_member_ids" := '{}';
2926 "loop_member_id_v" := NULL;
2927 "output_rows" := '{}';
2928 "output_row"."index" := 0;
2929 "output_row"."member_id" := "member_id_p";
2930 "output_row"."member_valid" := TRUE;
2931 "output_row"."participation" := FALSE;
2932 "output_row"."overridden" := FALSE;
2933 "output_row"."disabled_out" := FALSE;
2934 "output_row"."scope_out" := NULL;
2935 LOOP
2936 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2937 "loop_member_id_v" := "output_row"."member_id";
2938 ELSE
2939 "visited_member_ids" :=
2940 "visited_member_ids" || "output_row"."member_id";
2941 END IF;
2942 IF "output_row"."participation" ISNULL THEN
2943 "output_row"."overridden" := NULL;
2944 ELSIF "output_row"."participation" THEN
2945 "output_row"."overridden" := TRUE;
2946 END IF;
2947 "output_row"."scope_in" := "output_row"."scope_out";
2948 "output_row"."member_valid" := EXISTS (
2949 SELECT NULL FROM "member" JOIN "privilege"
2950 ON "privilege"."member_id" = "member"."id"
2951 AND "privilege"."unit_id" = "unit_id_v"
2952 WHERE "id" = "output_row"."member_id"
2953 AND "member"."active" AND "privilege"."voting_right"
2954 );
2955 "simulate_here_v" := (
2956 "simulate_v" AND
2957 "output_row"."member_id" = "member_id_p"
2958 );
2959 "delegation_row" := ROW(NULL);
2960 IF "output_row"."member_valid" OR "simulate_here_v" THEN
2961 IF "scope_v" = 'unit' THEN
2962 IF NOT "simulate_here_v" THEN
2963 SELECT * INTO "delegation_row" FROM "delegation"
2964 WHERE "truster_id" = "output_row"."member_id"
2965 AND "unit_id" = "unit_id_v";
2966 END IF;
2967 ELSIF "scope_v" = 'area' THEN
2968 "output_row"."participation" := EXISTS (
2969 SELECT NULL FROM "membership"
2970 WHERE "area_id" = "area_id_p"
2971 AND "member_id" = "output_row"."member_id"
2972 );
2973 IF "simulate_here_v" THEN
2974 IF "simulate_trustee_id_p" ISNULL THEN
2975 SELECT * INTO "delegation_row" FROM "delegation"
2976 WHERE "truster_id" = "output_row"."member_id"
2977 AND "unit_id" = "unit_id_v";
2978 END IF;
2979 ELSE
2980 SELECT * INTO "delegation_row" FROM "delegation"
2981 WHERE "truster_id" = "output_row"."member_id"
2982 AND (
2983 "unit_id" = "unit_id_v" OR
2984 "area_id" = "area_id_v"
2986 ORDER BY "scope" DESC;
2987 END IF;
2988 ELSIF "scope_v" = 'issue' THEN
2989 IF "issue_row"."fully_frozen" ISNULL THEN
2990 "output_row"."participation" := EXISTS (
2991 SELECT NULL FROM "interest"
2992 WHERE "issue_id" = "issue_id_p"
2993 AND "member_id" = "output_row"."member_id"
2994 );
2995 ELSE
2996 IF "output_row"."member_id" = "member_id_p" THEN
2997 "output_row"."participation" := EXISTS (
2998 SELECT NULL FROM "direct_voter"
2999 WHERE "issue_id" = "issue_id_p"
3000 AND "member_id" = "output_row"."member_id"
3001 );
3002 ELSE
3003 "output_row"."participation" := NULL;
3004 END IF;
3005 END IF;
3006 IF "simulate_here_v" THEN
3007 IF "simulate_trustee_id_p" ISNULL THEN
3008 SELECT * INTO "delegation_row" FROM "delegation"
3009 WHERE "truster_id" = "output_row"."member_id"
3010 AND (
3011 "unit_id" = "unit_id_v" OR
3012 "area_id" = "area_id_v"
3014 ORDER BY "scope" DESC;
3015 END IF;
3016 ELSE
3017 SELECT * INTO "delegation_row" FROM "delegation"
3018 WHERE "truster_id" = "output_row"."member_id"
3019 AND (
3020 "unit_id" = "unit_id_v" OR
3021 "area_id" = "area_id_v" OR
3022 "issue_id" = "issue_id_p"
3024 ORDER BY "scope" DESC;
3025 END IF;
3026 END IF;
3027 ELSE
3028 "output_row"."participation" := FALSE;
3029 END IF;
3030 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
3031 "output_row"."scope_out" := "scope_v";
3032 "output_rows" := "output_rows" || "output_row";
3033 "output_row"."member_id" := "simulate_trustee_id_p";
3034 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
3035 "output_row"."scope_out" := "delegation_row"."scope";
3036 "output_rows" := "output_rows" || "output_row";
3037 "output_row"."member_id" := "delegation_row"."trustee_id";
3038 ELSIF "delegation_row"."scope" NOTNULL THEN
3039 "output_row"."scope_out" := "delegation_row"."scope";
3040 "output_row"."disabled_out" := TRUE;
3041 "output_rows" := "output_rows" || "output_row";
3042 EXIT;
3043 ELSE
3044 "output_row"."scope_out" := NULL;
3045 "output_rows" := "output_rows" || "output_row";
3046 EXIT;
3047 END IF;
3048 EXIT WHEN "loop_member_id_v" NOTNULL;
3049 "output_row"."index" := "output_row"."index" + 1;
3050 END LOOP;
3051 "row_count" := array_upper("output_rows", 1);
3052 "i" := 1;
3053 "loop_v" := FALSE;
3054 LOOP
3055 "output_row" := "output_rows"["i"];
3056 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
3057 IF "loop_v" THEN
3058 IF "i" + 1 = "row_count" THEN
3059 "output_row"."loop" := 'last';
3060 ELSIF "i" = "row_count" THEN
3061 "output_row"."loop" := 'repetition';
3062 ELSE
3063 "output_row"."loop" := 'intermediate';
3064 END IF;
3065 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
3066 "output_row"."loop" := 'first';
3067 "loop_v" := TRUE;
3068 END IF;
3069 IF "scope_v" = 'unit' THEN
3070 "output_row"."participation" := NULL;
3071 END IF;
3072 RETURN NEXT "output_row";
3073 "i" := "i" + 1;
3074 END LOOP;
3075 RETURN;
3076 END;
3077 $$;
3079 COMMENT ON FUNCTION "delegation_chain"
3080 ( "member"."id"%TYPE,
3081 "unit"."id"%TYPE,
3082 "area"."id"%TYPE,
3083 "issue"."id"%TYPE,
3084 "member"."id"%TYPE,
3085 BOOLEAN )
3086 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
3090 ---------------------------------------------------------
3091 -- Single row returning function for delegation chains --
3092 ---------------------------------------------------------
3095 CREATE TYPE "delegation_info_loop_type" AS ENUM
3096 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
3098 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''';
3101 CREATE TYPE "delegation_info_type" AS (
3102 "own_participation" BOOLEAN,
3103 "own_delegation_scope" "delegation_scope",
3104 "first_trustee_id" INT4,
3105 "first_trustee_participation" BOOLEAN,
3106 "first_trustee_ellipsis" BOOLEAN,
3107 "other_trustee_id" INT4,
3108 "other_trustee_participation" BOOLEAN,
3109 "other_trustee_ellipsis" BOOLEAN,
3110 "delegation_loop" "delegation_info_loop_type",
3111 "participating_member_id" INT4 );
3113 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';
3115 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
3116 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
3117 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
3118 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
3119 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
3120 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
3121 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)';
3122 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
3123 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';
3124 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';
3127 CREATE FUNCTION "delegation_info"
3128 ( "member_id_p" "member"."id"%TYPE,
3129 "unit_id_p" "unit"."id"%TYPE,
3130 "area_id_p" "area"."id"%TYPE,
3131 "issue_id_p" "issue"."id"%TYPE,
3132 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
3133 "simulate_default_p" BOOLEAN DEFAULT FALSE )
3134 RETURNS "delegation_info_type"
3135 LANGUAGE 'plpgsql' STABLE AS $$
3136 DECLARE
3137 "current_row" "delegation_chain_row";
3138 "result" "delegation_info_type";
3139 BEGIN
3140 "result"."own_participation" := FALSE;
3141 FOR "current_row" IN
3142 SELECT * FROM "delegation_chain"(
3143 "member_id_p",
3144 "unit_id_p", "area_id_p", "issue_id_p",
3145 "simulate_trustee_id_p", "simulate_default_p")
3146 LOOP
3147 IF
3148 "result"."participating_member_id" ISNULL AND
3149 "current_row"."participation"
3150 THEN
3151 "result"."participating_member_id" := "current_row"."member_id";
3152 END IF;
3153 IF "current_row"."member_id" = "member_id_p" THEN
3154 "result"."own_participation" := "current_row"."participation";
3155 "result"."own_delegation_scope" := "current_row"."scope_out";
3156 IF "current_row"."loop" = 'first' THEN
3157 "result"."delegation_loop" := 'own';
3158 END IF;
3159 ELSIF
3160 "current_row"."member_valid" AND
3161 ( "current_row"."loop" ISNULL OR
3162 "current_row"."loop" != 'repetition' )
3163 THEN
3164 IF "result"."first_trustee_id" ISNULL THEN
3165 "result"."first_trustee_id" := "current_row"."member_id";
3166 "result"."first_trustee_participation" := "current_row"."participation";
3167 "result"."first_trustee_ellipsis" := FALSE;
3168 IF "current_row"."loop" = 'first' THEN
3169 "result"."delegation_loop" := 'first';
3170 END IF;
3171 ELSIF "result"."other_trustee_id" ISNULL THEN
3172 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
3173 "result"."other_trustee_id" := "current_row"."member_id";
3174 "result"."other_trustee_participation" := TRUE;
3175 "result"."other_trustee_ellipsis" := FALSE;
3176 IF "current_row"."loop" = 'first' THEN
3177 "result"."delegation_loop" := 'other';
3178 END IF;
3179 ELSE
3180 "result"."first_trustee_ellipsis" := TRUE;
3181 IF "current_row"."loop" = 'first' THEN
3182 "result"."delegation_loop" := 'first_ellipsis';
3183 END IF;
3184 END IF;
3185 ELSE
3186 "result"."other_trustee_ellipsis" := TRUE;
3187 IF "current_row"."loop" = 'first' THEN
3188 "result"."delegation_loop" := 'other_ellipsis';
3189 END IF;
3190 END IF;
3191 END IF;
3192 END LOOP;
3193 RETURN "result";
3194 END;
3195 $$;
3197 COMMENT ON FUNCTION "delegation_info"
3198 ( "member"."id"%TYPE,
3199 "unit"."id"%TYPE,
3200 "area"."id"%TYPE,
3201 "issue"."id"%TYPE,
3202 "member"."id"%TYPE,
3203 BOOLEAN )
3204 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
3208 ---------------------------
3209 -- Transaction isolation --
3210 ---------------------------
3213 CREATE FUNCTION "require_transaction_isolation"()
3214 RETURNS VOID
3215 LANGUAGE 'plpgsql' VOLATILE AS $$
3216 BEGIN
3217 IF
3218 current_setting('transaction_isolation') NOT IN
3219 ('repeatable read', 'serializable')
3220 THEN
3221 RAISE EXCEPTION 'Insufficient transaction isolation level' USING
3222 HINT = 'Consider using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ.';
3223 END IF;
3224 RETURN;
3225 END;
3226 $$;
3228 COMMENT ON FUNCTION "require_transaction_isolation"() IS 'Throws an exception, if transaction isolation level is too low to provide a consistent snapshot';
3231 CREATE FUNCTION "dont_require_transaction_isolation"()
3232 RETURNS VOID
3233 LANGUAGE 'plpgsql' VOLATILE AS $$
3234 BEGIN
3235 IF
3236 current_setting('transaction_isolation') IN
3237 ('repeatable read', 'serializable')
3238 THEN
3239 RAISE WARNING 'Unneccessary transaction isolation level: %',
3240 current_setting('transaction_isolation');
3241 END IF;
3242 RETURN;
3243 END;
3244 $$;
3246 COMMENT ON FUNCTION "dont_require_transaction_isolation"() IS 'Raises a warning, if transaction isolation level is higher than READ COMMITTED';
3250 -------------------------
3251 -- Notification system --
3252 -------------------------
3254 CREATE FUNCTION "get_initiatives_for_notification"
3255 ( "recipient_id_p" "member"."id"%TYPE )
3256 RETURNS SETOF "initiative_for_notification"
3257 LANGUAGE 'plpgsql' VOLATILE AS $$
3258 DECLARE
3259 "result_row" "initiative_for_notification"%ROWTYPE;
3260 "last_draft_id_v" "draft"."id"%TYPE;
3261 "last_suggestion_id_v" "suggestion"."id"%TYPE;
3262 BEGIN
3263 PERFORM "require_transaction_isolation"();
3264 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
3265 FOR "result_row" IN
3266 SELECT * FROM "initiative_for_notification"
3267 WHERE "recipient_id" = "recipient_id_p"
3268 LOOP
3269 SELECT "id" INTO "last_draft_id_v" FROM "draft"
3270 WHERE "draft"."initiative_id" = "result_row"."initiative_id"
3271 ORDER BY "id" DESC LIMIT 1;
3272 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
3273 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
3274 ORDER BY "id" DESC LIMIT 1;
3275 /* compatibility with PostgreSQL 9.1 */
3276 DELETE FROM "notification_initiative_sent"
3277 WHERE "member_id" = "recipient_id_p"
3278 AND "initiative_id" = "result_row"."initiative_id";
3279 INSERT INTO "notification_initiative_sent"
3280 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
3281 VALUES (
3282 "recipient_id_p",
3283 "result_row"."initiative_id",
3284 "last_draft_id_v",
3285 "last_suggestion_id_v" );
3286 /* TODO: use alternative code below, requires PostgreSQL 9.5 or higher
3287 INSERT INTO "notification_initiative_sent"
3288 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
3289 VALUES (
3290 "recipient_id_p",
3291 "result_row"."initiative_id",
3292 "last_draft_id_v",
3293 "last_suggestion_id_v" )
3294 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
3295 "last_draft_id" = "last_draft_id_v",
3296 "last_suggestion_id" = "last_suggestion_id_v";
3297 */
3298 RETURN NEXT "result_row";
3299 END LOOP;
3300 DELETE FROM "notification_initiative_sent"
3301 USING "initiative", "issue"
3302 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
3303 AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
3304 AND "issue"."id" = "initiative"."issue_id"
3305 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
3306 UPDATE "member" SET
3307 "notification_counter" = "notification_counter" + 1,
3308 "notification_sent" = now()
3309 WHERE "id" = "recipient_id_p";
3310 RETURN;
3311 END;
3312 $$;
3314 COMMENT ON FUNCTION "get_initiatives_for_notification"
3315 ( "member"."id"%TYPE )
3316 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';
3320 ------------------------------------------------------------------------
3321 -- Regular tasks, except calculcation of snapshots and voting results --
3322 ------------------------------------------------------------------------
3325 CREATE FUNCTION "check_activity"()
3326 RETURNS VOID
3327 LANGUAGE 'plpgsql' VOLATILE AS $$
3328 DECLARE
3329 "system_setting_row" "system_setting"%ROWTYPE;
3330 BEGIN
3331 PERFORM "dont_require_transaction_isolation"();
3332 SELECT * INTO "system_setting_row" FROM "system_setting";
3333 IF "system_setting_row"."member_ttl" NOTNULL THEN
3334 UPDATE "member" SET "active" = FALSE
3335 WHERE "active" = TRUE
3336 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
3337 END IF;
3338 RETURN;
3339 END;
3340 $$;
3342 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
3345 CREATE FUNCTION "calculate_member_counts"()
3346 RETURNS VOID
3347 LANGUAGE 'plpgsql' VOLATILE AS $$
3348 BEGIN
3349 PERFORM "require_transaction_isolation"();
3350 DELETE FROM "member_count";
3351 INSERT INTO "member_count" ("total_count")
3352 SELECT "total_count" FROM "member_count_view";
3353 UPDATE "unit" SET "member_count" = "view"."member_count"
3354 FROM "unit_member_count" AS "view"
3355 WHERE "view"."unit_id" = "unit"."id";
3356 UPDATE "area" SET
3357 "direct_member_count" = "view"."direct_member_count",
3358 "member_weight" = "view"."member_weight"
3359 FROM "area_member_count" AS "view"
3360 WHERE "view"."area_id" = "area"."id";
3361 RETURN;
3362 END;
3363 $$;
3365 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"';
3369 ------------------------------------
3370 -- Calculation of harmonic weight --
3371 ------------------------------------
3374 CREATE VIEW "remaining_harmonic_supporter_weight" AS
3375 SELECT
3376 "direct_interest_snapshot"."issue_id",
3377 "direct_interest_snapshot"."event",
3378 "direct_interest_snapshot"."member_id",
3379 "direct_interest_snapshot"."weight" AS "weight_num",
3380 count("initiative"."id") AS "weight_den"
3381 FROM "issue"
3382 JOIN "direct_interest_snapshot"
3383 ON "issue"."id" = "direct_interest_snapshot"."issue_id"
3384 AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
3385 JOIN "initiative"
3386 ON "issue"."id" = "initiative"."issue_id"
3387 AND "initiative"."harmonic_weight" ISNULL
3388 JOIN "direct_supporter_snapshot"
3389 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
3390 AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event"
3391 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
3392 AND (
3393 "direct_supporter_snapshot"."satisfied" = TRUE OR
3394 coalesce("initiative"."admitted", FALSE) = FALSE
3396 GROUP BY
3397 "direct_interest_snapshot"."issue_id",
3398 "direct_interest_snapshot"."event",
3399 "direct_interest_snapshot"."member_id",
3400 "direct_interest_snapshot"."weight";
3402 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
3405 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
3406 SELECT
3407 "initiative"."issue_id",
3408 "initiative"."id" AS "initiative_id",
3409 "initiative"."admitted",
3410 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
3411 "remaining_harmonic_supporter_weight"."weight_den"
3412 FROM "remaining_harmonic_supporter_weight"
3413 JOIN "initiative"
3414 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
3415 AND "initiative"."harmonic_weight" ISNULL
3416 JOIN "direct_supporter_snapshot"
3417 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
3418 AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"
3419 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
3420 AND (
3421 "direct_supporter_snapshot"."satisfied" = TRUE OR
3422 coalesce("initiative"."admitted", FALSE) = FALSE
3424 GROUP BY
3425 "initiative"."issue_id",
3426 "initiative"."id",
3427 "initiative"."admitted",
3428 "remaining_harmonic_supporter_weight"."weight_den";
3430 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
3433 CREATE VIEW "remaining_harmonic_initiative_weight_dummies" AS
3434 SELECT
3435 "issue_id",
3436 "id" AS "initiative_id",
3437 "admitted",
3438 0 AS "weight_num",
3439 1 AS "weight_den"
3440 FROM "initiative"
3441 WHERE "harmonic_weight" ISNULL;
3443 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';
3446 CREATE FUNCTION "set_harmonic_initiative_weights"
3447 ( "issue_id_p" "issue"."id"%TYPE )
3448 RETURNS VOID
3449 LANGUAGE 'plpgsql' VOLATILE AS $$
3450 DECLARE
3451 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
3452 "i" INT4;
3453 "count_v" INT4;
3454 "summand_v" FLOAT;
3455 "id_ary" INT4[];
3456 "weight_ary" FLOAT[];
3457 "min_weight_v" FLOAT;
3458 BEGIN
3459 PERFORM "require_transaction_isolation"();
3460 UPDATE "initiative" SET "harmonic_weight" = NULL
3461 WHERE "issue_id" = "issue_id_p";
3462 LOOP
3463 "min_weight_v" := NULL;
3464 "i" := 0;
3465 "count_v" := 0;
3466 FOR "weight_row" IN
3467 SELECT * FROM "remaining_harmonic_initiative_weight_summands"
3468 WHERE "issue_id" = "issue_id_p"
3469 AND (
3470 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
3471 SELECT NULL FROM "initiative"
3472 WHERE "issue_id" = "issue_id_p"
3473 AND "harmonic_weight" ISNULL
3474 AND coalesce("admitted", FALSE) = FALSE
3477 UNION ALL -- needed for corner cases
3478 SELECT * FROM "remaining_harmonic_initiative_weight_dummies"
3479 WHERE "issue_id" = "issue_id_p"
3480 AND (
3481 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
3482 SELECT NULL FROM "initiative"
3483 WHERE "issue_id" = "issue_id_p"
3484 AND "harmonic_weight" ISNULL
3485 AND coalesce("admitted", FALSE) = FALSE
3488 ORDER BY "initiative_id" DESC, "weight_den" DESC
3489 -- NOTE: non-admitted initiatives placed first (at last positions),
3490 -- latest initiatives treated worse in case of tie
3491 LOOP
3492 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
3493 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
3494 "i" := "i" + 1;
3495 "count_v" := "i";
3496 "id_ary"["i"] := "weight_row"."initiative_id";
3497 "weight_ary"["i"] := "summand_v";
3498 ELSE
3499 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
3500 END IF;
3501 END LOOP;
3502 EXIT WHEN "count_v" = 0;
3503 "i" := 1;
3504 LOOP
3505 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
3506 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
3507 "min_weight_v" := "weight_ary"["i"];
3508 END IF;
3509 "i" := "i" + 1;
3510 EXIT WHEN "i" > "count_v";
3511 END LOOP;
3512 "i" := 1;
3513 LOOP
3514 IF "weight_ary"["i"] = "min_weight_v" THEN
3515 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
3516 WHERE "id" = "id_ary"["i"];
3517 EXIT;
3518 END IF;
3519 "i" := "i" + 1;
3520 END LOOP;
3521 END LOOP;
3522 UPDATE "initiative" SET "harmonic_weight" = 0
3523 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
3524 END;
3525 $$;
3527 COMMENT ON FUNCTION "set_harmonic_initiative_weights"
3528 ( "issue"."id"%TYPE )
3529 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
3533 ------------------------------
3534 -- Calculation of snapshots --
3535 ------------------------------
3538 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
3539 ( "issue_id_p" "issue"."id"%TYPE,
3540 "member_id_p" "member"."id"%TYPE,
3541 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3542 RETURNS "direct_population_snapshot"."weight"%TYPE
3543 LANGUAGE 'plpgsql' VOLATILE AS $$
3544 DECLARE
3545 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3546 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
3547 "weight_v" INT4;
3548 "sub_weight_v" INT4;
3549 BEGIN
3550 PERFORM "require_transaction_isolation"();
3551 "weight_v" := 0;
3552 FOR "issue_delegation_row" IN
3553 SELECT * FROM "issue_delegation"
3554 WHERE "trustee_id" = "member_id_p"
3555 AND "issue_id" = "issue_id_p"
3556 LOOP
3557 IF NOT EXISTS (
3558 SELECT NULL FROM "direct_population_snapshot"
3559 WHERE "issue_id" = "issue_id_p"
3560 AND "event" = 'periodic'
3561 AND "member_id" = "issue_delegation_row"."truster_id"
3562 ) AND NOT EXISTS (
3563 SELECT NULL FROM "delegating_population_snapshot"
3564 WHERE "issue_id" = "issue_id_p"
3565 AND "event" = 'periodic'
3566 AND "member_id" = "issue_delegation_row"."truster_id"
3567 ) THEN
3568 "delegate_member_ids_v" :=
3569 "member_id_p" || "delegate_member_ids_p";
3570 INSERT INTO "delegating_population_snapshot" (
3571 "issue_id",
3572 "event",
3573 "member_id",
3574 "scope",
3575 "delegate_member_ids"
3576 ) VALUES (
3577 "issue_id_p",
3578 'periodic',
3579 "issue_delegation_row"."truster_id",
3580 "issue_delegation_row"."scope",
3581 "delegate_member_ids_v"
3582 );
3583 "sub_weight_v" := 1 +
3584 "weight_of_added_delegations_for_population_snapshot"(
3585 "issue_id_p",
3586 "issue_delegation_row"."truster_id",
3587 "delegate_member_ids_v"
3588 );
3589 UPDATE "delegating_population_snapshot"
3590 SET "weight" = "sub_weight_v"
3591 WHERE "issue_id" = "issue_id_p"
3592 AND "event" = 'periodic'
3593 AND "member_id" = "issue_delegation_row"."truster_id";
3594 "weight_v" := "weight_v" + "sub_weight_v";
3595 END IF;
3596 END LOOP;
3597 RETURN "weight_v";
3598 END;
3599 $$;
3601 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
3602 ( "issue"."id"%TYPE,
3603 "member"."id"%TYPE,
3604 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3605 IS 'Helper function for "create_population_snapshot" function';
3608 CREATE FUNCTION "create_population_snapshot"
3609 ( "issue_id_p" "issue"."id"%TYPE )
3610 RETURNS VOID
3611 LANGUAGE 'plpgsql' VOLATILE AS $$
3612 DECLARE
3613 "member_id_v" "member"."id"%TYPE;
3614 BEGIN
3615 PERFORM "require_transaction_isolation"();
3616 DELETE FROM "direct_population_snapshot"
3617 WHERE "issue_id" = "issue_id_p"
3618 AND "event" = 'periodic';
3619 DELETE FROM "delegating_population_snapshot"
3620 WHERE "issue_id" = "issue_id_p"
3621 AND "event" = 'periodic';
3622 INSERT INTO "direct_population_snapshot"
3623 ("issue_id", "event", "member_id")
3624 SELECT
3625 "issue_id_p" AS "issue_id",
3626 'periodic'::"snapshot_event" AS "event",
3627 "member"."id" AS "member_id"
3628 FROM "issue"
3629 JOIN "area" ON "issue"."area_id" = "area"."id"
3630 JOIN "membership" ON "area"."id" = "membership"."area_id"
3631 JOIN "member" ON "membership"."member_id" = "member"."id"
3632 JOIN "privilege"
3633 ON "privilege"."unit_id" = "area"."unit_id"
3634 AND "privilege"."member_id" = "member"."id"
3635 WHERE "issue"."id" = "issue_id_p"
3636 AND "member"."active" AND "privilege"."voting_right"
3637 UNION
3638 SELECT
3639 "issue_id_p" AS "issue_id",
3640 'periodic'::"snapshot_event" AS "event",
3641 "member"."id" AS "member_id"
3642 FROM "issue"
3643 JOIN "area" ON "issue"."area_id" = "area"."id"
3644 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3645 JOIN "member" ON "interest"."member_id" = "member"."id"
3646 JOIN "privilege"
3647 ON "privilege"."unit_id" = "area"."unit_id"
3648 AND "privilege"."member_id" = "member"."id"
3649 WHERE "issue"."id" = "issue_id_p"
3650 AND "member"."active" AND "privilege"."voting_right";
3651 FOR "member_id_v" IN
3652 SELECT "member_id" FROM "direct_population_snapshot"
3653 WHERE "issue_id" = "issue_id_p"
3654 AND "event" = 'periodic'
3655 LOOP
3656 UPDATE "direct_population_snapshot" SET
3657 "weight" = 1 +
3658 "weight_of_added_delegations_for_population_snapshot"(
3659 "issue_id_p",
3660 "member_id_v",
3661 '{}'
3663 WHERE "issue_id" = "issue_id_p"
3664 AND "event" = 'periodic'
3665 AND "member_id" = "member_id_v";
3666 END LOOP;
3667 RETURN;
3668 END;
3669 $$;
3671 COMMENT ON FUNCTION "create_population_snapshot"
3672 ( "issue"."id"%TYPE )
3673 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.';
3676 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3677 ( "issue_id_p" "issue"."id"%TYPE,
3678 "member_id_p" "member"."id"%TYPE,
3679 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3680 RETURNS "direct_interest_snapshot"."weight"%TYPE
3681 LANGUAGE 'plpgsql' VOLATILE AS $$
3682 DECLARE
3683 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3684 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
3685 "weight_v" INT4;
3686 "sub_weight_v" INT4;
3687 BEGIN
3688 PERFORM "require_transaction_isolation"();
3689 "weight_v" := 0;
3690 FOR "issue_delegation_row" IN
3691 SELECT * FROM "issue_delegation"
3692 WHERE "trustee_id" = "member_id_p"
3693 AND "issue_id" = "issue_id_p"
3694 LOOP
3695 IF NOT EXISTS (
3696 SELECT NULL FROM "direct_interest_snapshot"
3697 WHERE "issue_id" = "issue_id_p"
3698 AND "event" = 'periodic'
3699 AND "member_id" = "issue_delegation_row"."truster_id"
3700 ) AND NOT EXISTS (
3701 SELECT NULL FROM "delegating_interest_snapshot"
3702 WHERE "issue_id" = "issue_id_p"
3703 AND "event" = 'periodic'
3704 AND "member_id" = "issue_delegation_row"."truster_id"
3705 ) THEN
3706 "delegate_member_ids_v" :=
3707 "member_id_p" || "delegate_member_ids_p";
3708 INSERT INTO "delegating_interest_snapshot" (
3709 "issue_id",
3710 "event",
3711 "member_id",
3712 "scope",
3713 "delegate_member_ids"
3714 ) VALUES (
3715 "issue_id_p",
3716 'periodic',
3717 "issue_delegation_row"."truster_id",
3718 "issue_delegation_row"."scope",
3719 "delegate_member_ids_v"
3720 );
3721 "sub_weight_v" := 1 +
3722 "weight_of_added_delegations_for_interest_snapshot"(
3723 "issue_id_p",
3724 "issue_delegation_row"."truster_id",
3725 "delegate_member_ids_v"
3726 );
3727 UPDATE "delegating_interest_snapshot"
3728 SET "weight" = "sub_weight_v"
3729 WHERE "issue_id" = "issue_id_p"
3730 AND "event" = 'periodic'
3731 AND "member_id" = "issue_delegation_row"."truster_id";
3732 "weight_v" := "weight_v" + "sub_weight_v";
3733 END IF;
3734 END LOOP;
3735 RETURN "weight_v";
3736 END;
3737 $$;
3739 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3740 ( "issue"."id"%TYPE,
3741 "member"."id"%TYPE,
3742 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3743 IS 'Helper function for "create_interest_snapshot" function';
3746 CREATE FUNCTION "create_interest_snapshot"
3747 ( "issue_id_p" "issue"."id"%TYPE )
3748 RETURNS VOID
3749 LANGUAGE 'plpgsql' VOLATILE AS $$
3750 DECLARE
3751 "member_id_v" "member"."id"%TYPE;
3752 BEGIN
3753 PERFORM "require_transaction_isolation"();
3754 DELETE FROM "direct_interest_snapshot"
3755 WHERE "issue_id" = "issue_id_p"
3756 AND "event" = 'periodic';
3757 DELETE FROM "delegating_interest_snapshot"
3758 WHERE "issue_id" = "issue_id_p"
3759 AND "event" = 'periodic';
3760 DELETE FROM "direct_supporter_snapshot"
3761 USING "initiative" -- NOTE: due to missing index on issue_id
3762 WHERE "initiative"."issue_id" = "issue_id_p"
3763 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
3764 AND "direct_supporter_snapshot"."event" = 'periodic';
3765 INSERT INTO "direct_interest_snapshot"
3766 ("issue_id", "event", "member_id")
3767 SELECT
3768 "issue_id_p" AS "issue_id",
3769 'periodic' AS "event",
3770 "member"."id" AS "member_id"
3771 FROM "issue"
3772 JOIN "area" ON "issue"."area_id" = "area"."id"
3773 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3774 JOIN "member" ON "interest"."member_id" = "member"."id"
3775 JOIN "privilege"
3776 ON "privilege"."unit_id" = "area"."unit_id"
3777 AND "privilege"."member_id" = "member"."id"
3778 WHERE "issue"."id" = "issue_id_p"
3779 AND "member"."active" AND "privilege"."voting_right";
3780 FOR "member_id_v" IN
3781 SELECT "member_id" FROM "direct_interest_snapshot"
3782 WHERE "issue_id" = "issue_id_p"
3783 AND "event" = 'periodic'
3784 LOOP
3785 UPDATE "direct_interest_snapshot" SET
3786 "weight" = 1 +
3787 "weight_of_added_delegations_for_interest_snapshot"(
3788 "issue_id_p",
3789 "member_id_v",
3790 '{}'
3792 WHERE "issue_id" = "issue_id_p"
3793 AND "event" = 'periodic'
3794 AND "member_id" = "member_id_v";
3795 END LOOP;
3796 INSERT INTO "direct_supporter_snapshot"
3797 ( "issue_id", "initiative_id", "event", "member_id",
3798 "draft_id", "informed", "satisfied" )
3799 SELECT
3800 "issue_id_p" AS "issue_id",
3801 "initiative"."id" AS "initiative_id",
3802 'periodic' AS "event",
3803 "supporter"."member_id" AS "member_id",
3804 "supporter"."draft_id" AS "draft_id",
3805 "supporter"."draft_id" = "current_draft"."id" AS "informed",
3806 NOT EXISTS (
3807 SELECT NULL FROM "critical_opinion"
3808 WHERE "initiative_id" = "initiative"."id"
3809 AND "member_id" = "supporter"."member_id"
3810 ) AS "satisfied"
3811 FROM "initiative"
3812 JOIN "supporter"
3813 ON "supporter"."initiative_id" = "initiative"."id"
3814 JOIN "current_draft"
3815 ON "initiative"."id" = "current_draft"."initiative_id"
3816 JOIN "direct_interest_snapshot"
3817 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
3818 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
3819 AND "event" = 'periodic'
3820 WHERE "initiative"."issue_id" = "issue_id_p";
3821 RETURN;
3822 END;
3823 $$;
3825 COMMENT ON FUNCTION "create_interest_snapshot"
3826 ( "issue"."id"%TYPE )
3827 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.';
3830 CREATE FUNCTION "create_snapshot"
3831 ( "issue_id_p" "issue"."id"%TYPE )
3832 RETURNS VOID
3833 LANGUAGE 'plpgsql' VOLATILE AS $$
3834 DECLARE
3835 "initiative_id_v" "initiative"."id"%TYPE;
3836 "suggestion_id_v" "suggestion"."id"%TYPE;
3837 BEGIN
3838 PERFORM "require_transaction_isolation"();
3839 PERFORM "create_population_snapshot"("issue_id_p");
3840 PERFORM "create_interest_snapshot"("issue_id_p");
3841 UPDATE "issue" SET
3842 "snapshot" = coalesce("phase_finished", now()),
3843 "latest_snapshot_event" = 'periodic',
3844 "population" = (
3845 SELECT coalesce(sum("weight"), 0)
3846 FROM "direct_population_snapshot"
3847 WHERE "issue_id" = "issue_id_p"
3848 AND "event" = 'periodic'
3850 WHERE "id" = "issue_id_p";
3851 FOR "initiative_id_v" IN
3852 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
3853 LOOP
3854 UPDATE "initiative" SET
3855 "supporter_count" = (
3856 SELECT coalesce(sum("di"."weight"), 0)
3857 FROM "direct_interest_snapshot" AS "di"
3858 JOIN "direct_supporter_snapshot" AS "ds"
3859 ON "di"."member_id" = "ds"."member_id"
3860 WHERE "di"."issue_id" = "issue_id_p"
3861 AND "di"."event" = 'periodic'
3862 AND "ds"."initiative_id" = "initiative_id_v"
3863 AND "ds"."event" = 'periodic'
3864 ),
3865 "informed_supporter_count" = (
3866 SELECT coalesce(sum("di"."weight"), 0)
3867 FROM "direct_interest_snapshot" AS "di"
3868 JOIN "direct_supporter_snapshot" AS "ds"
3869 ON "di"."member_id" = "ds"."member_id"
3870 WHERE "di"."issue_id" = "issue_id_p"
3871 AND "di"."event" = 'periodic'
3872 AND "ds"."initiative_id" = "initiative_id_v"
3873 AND "ds"."event" = 'periodic'
3874 AND "ds"."informed"
3875 ),
3876 "satisfied_supporter_count" = (
3877 SELECT coalesce(sum("di"."weight"), 0)
3878 FROM "direct_interest_snapshot" AS "di"
3879 JOIN "direct_supporter_snapshot" AS "ds"
3880 ON "di"."member_id" = "ds"."member_id"
3881 WHERE "di"."issue_id" = "issue_id_p"
3882 AND "di"."event" = 'periodic'
3883 AND "ds"."initiative_id" = "initiative_id_v"
3884 AND "ds"."event" = 'periodic'
3885 AND "ds"."satisfied"
3886 ),
3887 "satisfied_informed_supporter_count" = (
3888 SELECT coalesce(sum("di"."weight"), 0)
3889 FROM "direct_interest_snapshot" AS "di"
3890 JOIN "direct_supporter_snapshot" AS "ds"
3891 ON "di"."member_id" = "ds"."member_id"
3892 WHERE "di"."issue_id" = "issue_id_p"
3893 AND "di"."event" = 'periodic'
3894 AND "ds"."initiative_id" = "initiative_id_v"
3895 AND "ds"."event" = 'periodic'
3896 AND "ds"."informed"
3897 AND "ds"."satisfied"
3899 WHERE "id" = "initiative_id_v";
3900 FOR "suggestion_id_v" IN
3901 SELECT "id" FROM "suggestion"
3902 WHERE "initiative_id" = "initiative_id_v"
3903 LOOP
3904 UPDATE "suggestion" SET
3905 "minus2_unfulfilled_count" = (
3906 SELECT coalesce(sum("snapshot"."weight"), 0)
3907 FROM "issue" CROSS JOIN "opinion"
3908 JOIN "direct_interest_snapshot" AS "snapshot"
3909 ON "snapshot"."issue_id" = "issue"."id"
3910 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3911 AND "snapshot"."member_id" = "opinion"."member_id"
3912 WHERE "issue"."id" = "issue_id_p"
3913 AND "opinion"."suggestion_id" = "suggestion_id_v"
3914 AND "opinion"."degree" = -2
3915 AND "opinion"."fulfilled" = FALSE
3916 ),
3917 "minus2_fulfilled_count" = (
3918 SELECT coalesce(sum("snapshot"."weight"), 0)
3919 FROM "issue" CROSS JOIN "opinion"
3920 JOIN "direct_interest_snapshot" AS "snapshot"
3921 ON "snapshot"."issue_id" = "issue"."id"
3922 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3923 AND "snapshot"."member_id" = "opinion"."member_id"
3924 WHERE "issue"."id" = "issue_id_p"
3925 AND "opinion"."suggestion_id" = "suggestion_id_v"
3926 AND "opinion"."degree" = -2
3927 AND "opinion"."fulfilled" = TRUE
3928 ),
3929 "minus1_unfulfilled_count" = (
3930 SELECT coalesce(sum("snapshot"."weight"), 0)
3931 FROM "issue" CROSS JOIN "opinion"
3932 JOIN "direct_interest_snapshot" AS "snapshot"
3933 ON "snapshot"."issue_id" = "issue"."id"
3934 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3935 AND "snapshot"."member_id" = "opinion"."member_id"
3936 WHERE "issue"."id" = "issue_id_p"
3937 AND "opinion"."suggestion_id" = "suggestion_id_v"
3938 AND "opinion"."degree" = -1
3939 AND "opinion"."fulfilled" = FALSE
3940 ),
3941 "minus1_fulfilled_count" = (
3942 SELECT coalesce(sum("snapshot"."weight"), 0)
3943 FROM "issue" CROSS JOIN "opinion"
3944 JOIN "direct_interest_snapshot" AS "snapshot"
3945 ON "snapshot"."issue_id" = "issue"."id"
3946 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3947 AND "snapshot"."member_id" = "opinion"."member_id"
3948 WHERE "issue"."id" = "issue_id_p"
3949 AND "opinion"."suggestion_id" = "suggestion_id_v"
3950 AND "opinion"."degree" = -1
3951 AND "opinion"."fulfilled" = TRUE
3952 ),
3953 "plus1_unfulfilled_count" = (
3954 SELECT coalesce(sum("snapshot"."weight"), 0)
3955 FROM "issue" CROSS JOIN "opinion"
3956 JOIN "direct_interest_snapshot" AS "snapshot"
3957 ON "snapshot"."issue_id" = "issue"."id"
3958 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3959 AND "snapshot"."member_id" = "opinion"."member_id"
3960 WHERE "issue"."id" = "issue_id_p"
3961 AND "opinion"."suggestion_id" = "suggestion_id_v"
3962 AND "opinion"."degree" = 1
3963 AND "opinion"."fulfilled" = FALSE
3964 ),
3965 "plus1_fulfilled_count" = (
3966 SELECT coalesce(sum("snapshot"."weight"), 0)
3967 FROM "issue" CROSS JOIN "opinion"
3968 JOIN "direct_interest_snapshot" AS "snapshot"
3969 ON "snapshot"."issue_id" = "issue"."id"
3970 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3971 AND "snapshot"."member_id" = "opinion"."member_id"
3972 WHERE "issue"."id" = "issue_id_p"
3973 AND "opinion"."suggestion_id" = "suggestion_id_v"
3974 AND "opinion"."degree" = 1
3975 AND "opinion"."fulfilled" = TRUE
3976 ),
3977 "plus2_unfulfilled_count" = (
3978 SELECT coalesce(sum("snapshot"."weight"), 0)
3979 FROM "issue" CROSS JOIN "opinion"
3980 JOIN "direct_interest_snapshot" AS "snapshot"
3981 ON "snapshot"."issue_id" = "issue"."id"
3982 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3983 AND "snapshot"."member_id" = "opinion"."member_id"
3984 WHERE "issue"."id" = "issue_id_p"
3985 AND "opinion"."suggestion_id" = "suggestion_id_v"
3986 AND "opinion"."degree" = 2
3987 AND "opinion"."fulfilled" = FALSE
3988 ),
3989 "plus2_fulfilled_count" = (
3990 SELECT coalesce(sum("snapshot"."weight"), 0)
3991 FROM "issue" CROSS JOIN "opinion"
3992 JOIN "direct_interest_snapshot" AS "snapshot"
3993 ON "snapshot"."issue_id" = "issue"."id"
3994 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3995 AND "snapshot"."member_id" = "opinion"."member_id"
3996 WHERE "issue"."id" = "issue_id_p"
3997 AND "opinion"."suggestion_id" = "suggestion_id_v"
3998 AND "opinion"."degree" = 2
3999 AND "opinion"."fulfilled" = TRUE
4001 WHERE "suggestion"."id" = "suggestion_id_v";
4002 END LOOP;
4003 END LOOP;
4004 RETURN;
4005 END;
4006 $$;
4008 COMMENT ON FUNCTION "create_snapshot"
4009 ( "issue"."id"%TYPE )
4010 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.';
4013 CREATE FUNCTION "set_snapshot_event"
4014 ( "issue_id_p" "issue"."id"%TYPE,
4015 "event_p" "snapshot_event" )
4016 RETURNS VOID
4017 LANGUAGE 'plpgsql' VOLATILE AS $$
4018 DECLARE
4019 "event_v" "issue"."latest_snapshot_event"%TYPE;
4020 BEGIN
4021 PERFORM "require_transaction_isolation"();
4022 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
4023 WHERE "id" = "issue_id_p" FOR UPDATE;
4024 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
4025 WHERE "id" = "issue_id_p";
4026 UPDATE "direct_population_snapshot" SET "event" = "event_p"
4027 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
4028 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
4029 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
4030 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
4031 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
4032 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
4033 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
4034 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
4035 FROM "initiative" -- NOTE: due to missing index on issue_id
4036 WHERE "initiative"."issue_id" = "issue_id_p"
4037 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
4038 AND "direct_supporter_snapshot"."event" = "event_v";
4039 RETURN;
4040 END;
4041 $$;
4043 COMMENT ON FUNCTION "set_snapshot_event"
4044 ( "issue"."id"%TYPE,
4045 "snapshot_event" )
4046 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
4050 -----------------------
4051 -- Counting of votes --
4052 -----------------------
4055 CREATE FUNCTION "weight_of_added_vote_delegations"
4056 ( "issue_id_p" "issue"."id"%TYPE,
4057 "member_id_p" "member"."id"%TYPE,
4058 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
4059 RETURNS "direct_voter"."weight"%TYPE
4060 LANGUAGE 'plpgsql' VOLATILE AS $$
4061 DECLARE
4062 "issue_delegation_row" "issue_delegation"%ROWTYPE;
4063 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
4064 "weight_v" INT4;
4065 "sub_weight_v" INT4;
4066 BEGIN
4067 PERFORM "require_transaction_isolation"();
4068 "weight_v" := 0;
4069 FOR "issue_delegation_row" IN
4070 SELECT * FROM "issue_delegation"
4071 WHERE "trustee_id" = "member_id_p"
4072 AND "issue_id" = "issue_id_p"
4073 LOOP
4074 IF NOT EXISTS (
4075 SELECT NULL FROM "direct_voter"
4076 WHERE "member_id" = "issue_delegation_row"."truster_id"
4077 AND "issue_id" = "issue_id_p"
4078 ) AND NOT EXISTS (
4079 SELECT NULL FROM "delegating_voter"
4080 WHERE "member_id" = "issue_delegation_row"."truster_id"
4081 AND "issue_id" = "issue_id_p"
4082 ) THEN
4083 "delegate_member_ids_v" :=
4084 "member_id_p" || "delegate_member_ids_p";
4085 INSERT INTO "delegating_voter" (
4086 "issue_id",
4087 "member_id",
4088 "scope",
4089 "delegate_member_ids"
4090 ) VALUES (
4091 "issue_id_p",
4092 "issue_delegation_row"."truster_id",
4093 "issue_delegation_row"."scope",
4094 "delegate_member_ids_v"
4095 );
4096 "sub_weight_v" := 1 +
4097 "weight_of_added_vote_delegations"(
4098 "issue_id_p",
4099 "issue_delegation_row"."truster_id",
4100 "delegate_member_ids_v"
4101 );
4102 UPDATE "delegating_voter"
4103 SET "weight" = "sub_weight_v"
4104 WHERE "issue_id" = "issue_id_p"
4105 AND "member_id" = "issue_delegation_row"."truster_id";
4106 "weight_v" := "weight_v" + "sub_weight_v";
4107 END IF;
4108 END LOOP;
4109 RETURN "weight_v";
4110 END;
4111 $$;
4113 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
4114 ( "issue"."id"%TYPE,
4115 "member"."id"%TYPE,
4116 "delegating_voter"."delegate_member_ids"%TYPE )
4117 IS 'Helper function for "add_vote_delegations" function';
4120 CREATE FUNCTION "add_vote_delegations"
4121 ( "issue_id_p" "issue"."id"%TYPE )
4122 RETURNS VOID
4123 LANGUAGE 'plpgsql' VOLATILE AS $$
4124 DECLARE
4125 "member_id_v" "member"."id"%TYPE;
4126 BEGIN
4127 PERFORM "require_transaction_isolation"();
4128 FOR "member_id_v" IN
4129 SELECT "member_id" FROM "direct_voter"
4130 WHERE "issue_id" = "issue_id_p"
4131 LOOP
4132 UPDATE "direct_voter" SET
4133 "weight" = "weight" + "weight_of_added_vote_delegations"(
4134 "issue_id_p",
4135 "member_id_v",
4136 '{}'
4138 WHERE "member_id" = "member_id_v"
4139 AND "issue_id" = "issue_id_p";
4140 END LOOP;
4141 RETURN;
4142 END;
4143 $$;
4145 COMMENT ON FUNCTION "add_vote_delegations"
4146 ( "issue_id_p" "issue"."id"%TYPE )
4147 IS 'Helper function for "close_voting" function';
4150 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
4151 RETURNS VOID
4152 LANGUAGE 'plpgsql' VOLATILE AS $$
4153 DECLARE
4154 "area_id_v" "area"."id"%TYPE;
4155 "unit_id_v" "unit"."id"%TYPE;
4156 "member_id_v" "member"."id"%TYPE;
4157 BEGIN
4158 PERFORM "require_transaction_isolation"();
4159 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
4160 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
4161 -- override protection triggers:
4162 INSERT INTO "temporary_transaction_data" ("key", "value")
4163 VALUES ('override_protection_triggers', TRUE::TEXT);
4164 -- delete timestamp of voting comment:
4165 UPDATE "direct_voter" SET "comment_changed" = NULL
4166 WHERE "issue_id" = "issue_id_p";
4167 -- delete delegating votes (in cases of manual reset of issue state):
4168 DELETE FROM "delegating_voter"
4169 WHERE "issue_id" = "issue_id_p";
4170 -- delete votes from non-privileged voters:
4171 DELETE FROM "direct_voter"
4172 USING (
4173 SELECT
4174 "direct_voter"."member_id"
4175 FROM "direct_voter"
4176 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
4177 LEFT JOIN "privilege"
4178 ON "privilege"."unit_id" = "unit_id_v"
4179 AND "privilege"."member_id" = "direct_voter"."member_id"
4180 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
4181 "member"."active" = FALSE OR
4182 "privilege"."voting_right" ISNULL OR
4183 "privilege"."voting_right" = FALSE
4185 ) AS "subquery"
4186 WHERE "direct_voter"."issue_id" = "issue_id_p"
4187 AND "direct_voter"."member_id" = "subquery"."member_id";
4188 -- consider delegations:
4189 UPDATE "direct_voter" SET "weight" = 1
4190 WHERE "issue_id" = "issue_id_p";
4191 PERFORM "add_vote_delegations"("issue_id_p");
4192 -- mark first preferences:
4193 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
4194 FROM (
4195 SELECT
4196 "vote"."initiative_id",
4197 "vote"."member_id",
4198 CASE WHEN "vote"."grade" > 0 THEN
4199 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
4200 ELSE NULL
4201 END AS "first_preference"
4202 FROM "vote"
4203 JOIN "initiative" -- NOTE: due to missing index on issue_id
4204 ON "vote"."issue_id" = "initiative"."issue_id"
4205 JOIN "vote" AS "agg"
4206 ON "initiative"."id" = "agg"."initiative_id"
4207 AND "vote"."member_id" = "agg"."member_id"
4208 GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
4209 ) AS "subquery"
4210 WHERE "vote"."issue_id" = "issue_id_p"
4211 AND "vote"."initiative_id" = "subquery"."initiative_id"
4212 AND "vote"."member_id" = "subquery"."member_id";
4213 -- finish overriding protection triggers (avoids garbage):
4214 DELETE FROM "temporary_transaction_data"
4215 WHERE "key" = 'override_protection_triggers';
4216 -- materialize battle_view:
4217 -- NOTE: "closed" column of issue must be set at this point
4218 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
4219 INSERT INTO "battle" (
4220 "issue_id",
4221 "winning_initiative_id", "losing_initiative_id",
4222 "count"
4223 ) SELECT
4224 "issue_id",
4225 "winning_initiative_id", "losing_initiative_id",
4226 "count"
4227 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
4228 -- set voter count:
4229 UPDATE "issue" SET
4230 "voter_count" = (
4231 SELECT coalesce(sum("weight"), 0)
4232 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
4234 WHERE "id" = "issue_id_p";
4235 -- copy "positive_votes" and "negative_votes" from "battle" table:
4236 -- NOTE: "first_preference_votes" is set to a default of 0 at this step
4237 UPDATE "initiative" SET
4238 "first_preference_votes" = 0,
4239 "positive_votes" = "battle_win"."count",
4240 "negative_votes" = "battle_lose"."count"
4241 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
4242 WHERE
4243 "battle_win"."issue_id" = "issue_id_p" AND
4244 "battle_win"."winning_initiative_id" = "initiative"."id" AND
4245 "battle_win"."losing_initiative_id" ISNULL AND
4246 "battle_lose"."issue_id" = "issue_id_p" AND
4247 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
4248 "battle_lose"."winning_initiative_id" ISNULL;
4249 -- calculate "first_preference_votes":
4250 -- NOTE: will only set values not equal to zero
4251 UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum"
4252 FROM (
4253 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
4254 FROM "vote" JOIN "direct_voter"
4255 ON "vote"."issue_id" = "direct_voter"."issue_id"
4256 AND "vote"."member_id" = "direct_voter"."member_id"
4257 WHERE "vote"."first_preference"
4258 GROUP BY "vote"."initiative_id"
4259 ) AS "subquery"
4260 WHERE "initiative"."issue_id" = "issue_id_p"
4261 AND "initiative"."admitted"
4262 AND "initiative"."id" = "subquery"."initiative_id";
4263 END;
4264 $$;
4266 COMMENT ON FUNCTION "close_voting"
4267 ( "issue"."id"%TYPE )
4268 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.';
4271 CREATE FUNCTION "defeat_strength"
4272 ( "positive_votes_p" INT4,
4273 "negative_votes_p" INT4,
4274 "defeat_strength_p" "defeat_strength" )
4275 RETURNS INT8
4276 LANGUAGE 'plpgsql' IMMUTABLE AS $$
4277 BEGIN
4278 IF "defeat_strength_p" = 'simple'::"defeat_strength" THEN
4279 IF "positive_votes_p" > "negative_votes_p" THEN
4280 RETURN "positive_votes_p";
4281 ELSE
4282 RETURN 0;
4283 END IF;
4284 ELSE
4285 IF "positive_votes_p" > "negative_votes_p" THEN
4286 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
4287 ELSIF "positive_votes_p" = "negative_votes_p" THEN
4288 RETURN 0;
4289 ELSE
4290 RETURN -1;
4291 END IF;
4292 END IF;
4293 END;
4294 $$;
4296 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")';
4299 CREATE FUNCTION "secondary_link_strength"
4300 ( "initiative1_ord_p" INT4,
4301 "initiative2_ord_p" INT4,
4302 "tie_breaking_p" "tie_breaking" )
4303 RETURNS INT8
4304 LANGUAGE 'plpgsql' IMMUTABLE AS $$
4305 BEGIN
4306 IF "initiative1_ord_p" = "initiative2_ord_p" THEN
4307 RAISE EXCEPTION 'Identical initiative ids passed to "secondary_link_strength" function (should not happen)';
4308 END IF;
4309 RETURN (
4310 CASE WHEN "tie_breaking_p" = 'simple'::"tie_breaking" THEN
4312 ELSE
4313 CASE WHEN "initiative1_ord_p" < "initiative2_ord_p" THEN
4314 1::INT8 << 62
4315 ELSE 0 END
4317 CASE WHEN "tie_breaking_p" = 'variant2'::"tie_breaking" THEN
4318 ("initiative2_ord_p"::INT8 << 31) - "initiative1_ord_p"::INT8
4319 ELSE
4320 "initiative2_ord_p"::INT8 - ("initiative1_ord_p"::INT8 << 31)
4321 END
4322 END
4323 );
4324 END;
4325 $$;
4327 COMMENT ON FUNCTION "secondary_link_strength"(INT4, INT4, "tie_breaking") IS 'Calculates a secondary criterion for the defeat strength (tie-breaking of the links)';
4330 CREATE TYPE "link_strength" AS (
4331 "primary" INT8,
4332 "secondary" INT8 );
4334 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'')';
4337 CREATE FUNCTION "find_best_paths"("matrix_d" "link_strength"[][])
4338 RETURNS "link_strength"[][]
4339 LANGUAGE 'plpgsql' IMMUTABLE AS $$
4340 DECLARE
4341 "dimension_v" INT4;
4342 "matrix_p" "link_strength"[][];
4343 "i" INT4;
4344 "j" INT4;
4345 "k" INT4;
4346 BEGIN
4347 "dimension_v" := array_upper("matrix_d", 1);
4348 "matrix_p" := "matrix_d";
4349 "i" := 1;
4350 LOOP
4351 "j" := 1;
4352 LOOP
4353 IF "i" != "j" THEN
4354 "k" := 1;
4355 LOOP
4356 IF "i" != "k" AND "j" != "k" THEN
4357 IF "matrix_p"["j"]["i"] < "matrix_p"["i"]["k"] THEN
4358 IF "matrix_p"["j"]["i"] > "matrix_p"["j"]["k"] THEN
4359 "matrix_p"["j"]["k"] := "matrix_p"["j"]["i"];
4360 END IF;
4361 ELSE
4362 IF "matrix_p"["i"]["k"] > "matrix_p"["j"]["k"] THEN
4363 "matrix_p"["j"]["k"] := "matrix_p"["i"]["k"];
4364 END IF;
4365 END IF;
4366 END IF;
4367 EXIT WHEN "k" = "dimension_v";
4368 "k" := "k" + 1;
4369 END LOOP;
4370 END IF;
4371 EXIT WHEN "j" = "dimension_v";
4372 "j" := "j" + 1;
4373 END LOOP;
4374 EXIT WHEN "i" = "dimension_v";
4375 "i" := "i" + 1;
4376 END LOOP;
4377 RETURN "matrix_p";
4378 END;
4379 $$;
4381 COMMENT ON FUNCTION "find_best_paths"("link_strength"[][]) IS 'Computes the strengths of the best beat-paths from a square matrix';
4384 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
4385 RETURNS VOID
4386 LANGUAGE 'plpgsql' VOLATILE AS $$
4387 DECLARE
4388 "issue_row" "issue"%ROWTYPE;
4389 "policy_row" "policy"%ROWTYPE;
4390 "dimension_v" INT4;
4391 "matrix_a" INT4[][]; -- absolute votes
4392 "matrix_d" "link_strength"[][]; -- defeat strength (direct)
4393 "matrix_p" "link_strength"[][]; -- defeat strength (best path)
4394 "matrix_t" "link_strength"[][]; -- defeat strength (tie-breaking)
4395 "matrix_f" BOOLEAN[][]; -- forbidden link (tie-breaking)
4396 "matrix_b" BOOLEAN[][]; -- final order (who beats who)
4397 "i" INT4;
4398 "j" INT4;
4399 "m" INT4;
4400 "n" INT4;
4401 "battle_row" "battle"%ROWTYPE;
4402 "rank_ary" INT4[];
4403 "rank_v" INT4;
4404 "initiative_id_v" "initiative"."id"%TYPE;
4405 BEGIN
4406 PERFORM "require_transaction_isolation"();
4407 SELECT * INTO "issue_row"
4408 FROM "issue" WHERE "id" = "issue_id_p";
4409 SELECT * INTO "policy_row"
4410 FROM "policy" WHERE "id" = "issue_row"."policy_id";
4411 SELECT count(1) INTO "dimension_v"
4412 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
4413 -- create "matrix_a" with absolute number of votes in pairwise
4414 -- comparison:
4415 "matrix_a" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
4416 "i" := 1;
4417 "j" := 2;
4418 FOR "battle_row" IN
4419 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
4420 ORDER BY
4421 "winning_initiative_id" NULLS FIRST,
4422 "losing_initiative_id" NULLS FIRST
4423 LOOP
4424 "matrix_a"["i"]["j"] := "battle_row"."count";
4425 IF "j" = "dimension_v" THEN
4426 "i" := "i" + 1;
4427 "j" := 1;
4428 ELSE
4429 "j" := "j" + 1;
4430 IF "j" = "i" THEN
4431 "j" := "j" + 1;
4432 END IF;
4433 END IF;
4434 END LOOP;
4435 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
4436 RAISE EXCEPTION 'Wrong battle count (should not happen)';
4437 END IF;
4438 -- store direct defeat strengths in "matrix_d" using "defeat_strength"
4439 -- and "secondary_link_strength" functions:
4440 "matrix_d" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
4441 "i" := 1;
4442 LOOP
4443 "j" := 1;
4444 LOOP
4445 IF "i" != "j" THEN
4446 "matrix_d"["i"]["j"] := (
4447 "defeat_strength"(
4448 "matrix_a"["i"]["j"],
4449 "matrix_a"["j"]["i"],
4450 "policy_row"."defeat_strength"
4451 ),
4452 "secondary_link_strength"(
4453 "i",
4454 "j",
4455 "policy_row"."tie_breaking"
4457 )::"link_strength";
4458 END IF;
4459 EXIT WHEN "j" = "dimension_v";
4460 "j" := "j" + 1;
4461 END LOOP;
4462 EXIT WHEN "i" = "dimension_v";
4463 "i" := "i" + 1;
4464 END LOOP;
4465 -- find best paths:
4466 "matrix_p" := "find_best_paths"("matrix_d");
4467 -- create partial order:
4468 "matrix_b" := array_fill(NULL::BOOLEAN, ARRAY["dimension_v", "dimension_v"]);
4469 "i" := 1;
4470 LOOP
4471 "j" := "i" + 1;
4472 LOOP
4473 IF "i" != "j" THEN
4474 IF "matrix_p"["i"]["j"] > "matrix_p"["j"]["i"] THEN
4475 "matrix_b"["i"]["j"] := TRUE;
4476 "matrix_b"["j"]["i"] := FALSE;
4477 ELSIF "matrix_p"["i"]["j"] < "matrix_p"["j"]["i"] THEN
4478 "matrix_b"["i"]["j"] := FALSE;
4479 "matrix_b"["j"]["i"] := TRUE;
4480 END IF;
4481 END IF;
4482 EXIT WHEN "j" = "dimension_v";
4483 "j" := "j" + 1;
4484 END LOOP;
4485 EXIT WHEN "i" = "dimension_v" - 1;
4486 "i" := "i" + 1;
4487 END LOOP;
4488 -- tie-breaking by forbidding shared weakest links in beat-paths
4489 -- (unless "tie_breaking" is set to 'simple', in which case tie-breaking
4490 -- is performed later by initiative id):
4491 IF "policy_row"."tie_breaking" != 'simple'::"tie_breaking" THEN
4492 "m" := 1;
4493 LOOP
4494 "n" := "m" + 1;
4495 LOOP
4496 -- only process those candidates m and n, which are tied:
4497 IF "matrix_b"["m"]["n"] ISNULL THEN
4498 -- start with beat-paths prior tie-breaking:
4499 "matrix_t" := "matrix_p";
4500 -- start with all links allowed:
4501 "matrix_f" := array_fill(FALSE, ARRAY["dimension_v", "dimension_v"]);
4502 LOOP
4503 -- determine (and forbid) that link that is the weakest link
4504 -- in both the best path from candidate m to candidate n and
4505 -- from candidate n to candidate m:
4506 "i" := 1;
4507 <<forbid_one_link>>
4508 LOOP
4509 "j" := 1;
4510 LOOP
4511 IF "i" != "j" THEN
4512 IF "matrix_d"["i"]["j"] = "matrix_t"["m"]["n"] THEN
4513 "matrix_f"["i"]["j"] := TRUE;
4514 -- exit for performance reasons,
4515 -- as exactly one link will be found:
4516 EXIT forbid_one_link;
4517 END IF;
4518 END IF;
4519 EXIT WHEN "j" = "dimension_v";
4520 "j" := "j" + 1;
4521 END LOOP;
4522 IF "i" = "dimension_v" THEN
4523 RAISE EXCEPTION 'Did not find shared weakest link for tie-breaking (should not happen)';
4524 END IF;
4525 "i" := "i" + 1;
4526 END LOOP;
4527 -- calculate best beat-paths while ignoring forbidden links:
4528 "i" := 1;
4529 LOOP
4530 "j" := 1;
4531 LOOP
4532 IF "i" != "j" THEN
4533 "matrix_t"["i"]["j"] := CASE
4534 WHEN "matrix_f"["i"]["j"]
4535 THEN ((-1::INT8) << 63, 0)::"link_strength" -- worst possible value
4536 ELSE "matrix_d"["i"]["j"] END;
4537 END IF;
4538 EXIT WHEN "j" = "dimension_v";
4539 "j" := "j" + 1;
4540 END LOOP;
4541 EXIT WHEN "i" = "dimension_v";
4542 "i" := "i" + 1;
4543 END LOOP;
4544 "matrix_t" := "find_best_paths"("matrix_t");
4545 -- extend partial order, if tie-breaking was successful:
4546 IF "matrix_t"["m"]["n"] > "matrix_t"["n"]["m"] THEN
4547 "matrix_b"["m"]["n"] := TRUE;
4548 "matrix_b"["n"]["m"] := FALSE;
4549 EXIT;
4550 ELSIF "matrix_t"["m"]["n"] < "matrix_t"["n"]["m"] THEN
4551 "matrix_b"["m"]["n"] := FALSE;
4552 "matrix_b"["n"]["m"] := TRUE;
4553 EXIT;
4554 END IF;
4555 END LOOP;
4556 END IF;
4557 EXIT WHEN "n" = "dimension_v";
4558 "n" := "n" + 1;
4559 END LOOP;
4560 EXIT WHEN "m" = "dimension_v" - 1;
4561 "m" := "m" + 1;
4562 END LOOP;
4563 END IF;
4564 -- store a unique ranking in "rank_ary":
4565 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
4566 "rank_v" := 1;
4567 LOOP
4568 "i" := 1;
4569 <<assign_next_rank>>
4570 LOOP
4571 IF "rank_ary"["i"] ISNULL THEN
4572 "j" := 1;
4573 LOOP
4574 IF
4575 "i" != "j" AND
4576 "rank_ary"["j"] ISNULL AND
4577 ( "matrix_b"["j"]["i"] OR
4578 -- tie-breaking by "id"
4579 ( "matrix_b"["j"]["i"] ISNULL AND
4580 "j" < "i" ) )
4581 THEN
4582 -- someone else is better
4583 EXIT;
4584 END IF;
4585 IF "j" = "dimension_v" THEN
4586 -- noone is better
4587 "rank_ary"["i"] := "rank_v";
4588 EXIT assign_next_rank;
4589 END IF;
4590 "j" := "j" + 1;
4591 END LOOP;
4592 END IF;
4593 "i" := "i" + 1;
4594 IF "i" > "dimension_v" THEN
4595 RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
4596 END IF;
4597 END LOOP;
4598 EXIT WHEN "rank_v" = "dimension_v";
4599 "rank_v" := "rank_v" + 1;
4600 END LOOP;
4601 -- write preliminary results:
4602 "i" := 2; -- omit status quo with "i" = 1
4603 FOR "initiative_id_v" IN
4604 SELECT "id" FROM "initiative"
4605 WHERE "issue_id" = "issue_id_p" AND "admitted"
4606 ORDER BY "id"
4607 LOOP
4608 UPDATE "initiative" SET
4609 "direct_majority" =
4610 CASE WHEN "policy_row"."direct_majority_strict" THEN
4611 "positive_votes" * "policy_row"."direct_majority_den" >
4612 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
4613 ELSE
4614 "positive_votes" * "policy_row"."direct_majority_den" >=
4615 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
4616 END
4617 AND "positive_votes" >= "policy_row"."direct_majority_positive"
4618 AND "issue_row"."voter_count"-"negative_votes" >=
4619 "policy_row"."direct_majority_non_negative",
4620 "indirect_majority" =
4621 CASE WHEN "policy_row"."indirect_majority_strict" THEN
4622 "positive_votes" * "policy_row"."indirect_majority_den" >
4623 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4624 ELSE
4625 "positive_votes" * "policy_row"."indirect_majority_den" >=
4626 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4627 END
4628 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
4629 AND "issue_row"."voter_count"-"negative_votes" >=
4630 "policy_row"."indirect_majority_non_negative",
4631 "schulze_rank" = "rank_ary"["i"],
4632 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
4633 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"[1],
4634 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"[1],
4635 "reverse_beat_path" = CASE WHEN "policy_row"."defeat_strength" = 'simple'::"defeat_strength"
4636 THEN NULL
4637 ELSE "matrix_p"[1]["i"]."primary" >= 0 END,
4638 "eligible" = FALSE,
4639 "winner" = FALSE,
4640 "rank" = NULL -- NOTE: in cases of manual reset of issue state
4641 WHERE "id" = "initiative_id_v";
4642 "i" := "i" + 1;
4643 END LOOP;
4644 IF "i" != "dimension_v" + 1 THEN
4645 RAISE EXCEPTION 'Wrong winner count (should not happen)';
4646 END IF;
4647 -- take indirect majorities into account:
4648 LOOP
4649 UPDATE "initiative" SET "indirect_majority" = TRUE
4650 FROM (
4651 SELECT "new_initiative"."id" AS "initiative_id"
4652 FROM "initiative" "old_initiative"
4653 JOIN "initiative" "new_initiative"
4654 ON "new_initiative"."issue_id" = "issue_id_p"
4655 AND "new_initiative"."indirect_majority" = FALSE
4656 JOIN "battle" "battle_win"
4657 ON "battle_win"."issue_id" = "issue_id_p"
4658 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
4659 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
4660 JOIN "battle" "battle_lose"
4661 ON "battle_lose"."issue_id" = "issue_id_p"
4662 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
4663 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
4664 WHERE "old_initiative"."issue_id" = "issue_id_p"
4665 AND "old_initiative"."indirect_majority" = TRUE
4666 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
4667 "battle_win"."count" * "policy_row"."indirect_majority_den" >
4668 "policy_row"."indirect_majority_num" *
4669 ("battle_win"."count"+"battle_lose"."count")
4670 ELSE
4671 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
4672 "policy_row"."indirect_majority_num" *
4673 ("battle_win"."count"+"battle_lose"."count")
4674 END
4675 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
4676 AND "issue_row"."voter_count"-"battle_lose"."count" >=
4677 "policy_row"."indirect_majority_non_negative"
4678 ) AS "subquery"
4679 WHERE "id" = "subquery"."initiative_id";
4680 EXIT WHEN NOT FOUND;
4681 END LOOP;
4682 -- set "multistage_majority" for remaining matching initiatives:
4683 UPDATE "initiative" SET "multistage_majority" = TRUE
4684 FROM (
4685 SELECT "losing_initiative"."id" AS "initiative_id"
4686 FROM "initiative" "losing_initiative"
4687 JOIN "initiative" "winning_initiative"
4688 ON "winning_initiative"."issue_id" = "issue_id_p"
4689 AND "winning_initiative"."admitted"
4690 JOIN "battle" "battle_win"
4691 ON "battle_win"."issue_id" = "issue_id_p"
4692 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
4693 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
4694 JOIN "battle" "battle_lose"
4695 ON "battle_lose"."issue_id" = "issue_id_p"
4696 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
4697 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
4698 WHERE "losing_initiative"."issue_id" = "issue_id_p"
4699 AND "losing_initiative"."admitted"
4700 AND "winning_initiative"."schulze_rank" <
4701 "losing_initiative"."schulze_rank"
4702 AND "battle_win"."count" > "battle_lose"."count"
4703 AND (
4704 "battle_win"."count" > "winning_initiative"."positive_votes" OR
4705 "battle_lose"."count" < "losing_initiative"."negative_votes" )
4706 ) AS "subquery"
4707 WHERE "id" = "subquery"."initiative_id";
4708 -- mark eligible initiatives:
4709 UPDATE "initiative" SET "eligible" = TRUE
4710 WHERE "issue_id" = "issue_id_p"
4711 AND "initiative"."direct_majority"
4712 AND "initiative"."indirect_majority"
4713 AND "initiative"."better_than_status_quo"
4714 AND (
4715 "policy_row"."no_multistage_majority" = FALSE OR
4716 "initiative"."multistage_majority" = FALSE )
4717 AND (
4718 "policy_row"."no_reverse_beat_path" = FALSE OR
4719 coalesce("initiative"."reverse_beat_path", FALSE) = FALSE );
4720 -- mark final winner:
4721 UPDATE "initiative" SET "winner" = TRUE
4722 FROM (
4723 SELECT "id" AS "initiative_id"
4724 FROM "initiative"
4725 WHERE "issue_id" = "issue_id_p" AND "eligible"
4726 ORDER BY
4727 "schulze_rank",
4728 "id"
4729 LIMIT 1
4730 ) AS "subquery"
4731 WHERE "id" = "subquery"."initiative_id";
4732 -- write (final) ranks:
4733 "rank_v" := 1;
4734 FOR "initiative_id_v" IN
4735 SELECT "id"
4736 FROM "initiative"
4737 WHERE "issue_id" = "issue_id_p" AND "admitted"
4738 ORDER BY
4739 "winner" DESC,
4740 "eligible" DESC,
4741 "schulze_rank",
4742 "id"
4743 LOOP
4744 UPDATE "initiative" SET "rank" = "rank_v"
4745 WHERE "id" = "initiative_id_v";
4746 "rank_v" := "rank_v" + 1;
4747 END LOOP;
4748 -- set schulze rank of status quo and mark issue as finished:
4749 UPDATE "issue" SET
4750 "status_quo_schulze_rank" = "rank_ary"[1],
4751 "state" =
4752 CASE WHEN EXISTS (
4753 SELECT NULL FROM "initiative"
4754 WHERE "issue_id" = "issue_id_p" AND "winner"
4755 ) THEN
4756 'finished_with_winner'::"issue_state"
4757 ELSE
4758 'finished_without_winner'::"issue_state"
4759 END,
4760 "closed" = "phase_finished",
4761 "phase_finished" = NULL
4762 WHERE "id" = "issue_id_p";
4763 RETURN;
4764 END;
4765 $$;
4767 COMMENT ON FUNCTION "calculate_ranks"
4768 ( "issue"."id"%TYPE )
4769 IS 'Determine ranking (Votes have to be counted first)';
4773 -----------------------------
4774 -- Automatic state changes --
4775 -----------------------------
4778 CREATE TYPE "check_issue_persistence" AS (
4779 "state" "issue_state",
4780 "phase_finished" BOOLEAN,
4781 "issue_revoked" BOOLEAN,
4782 "snapshot_created" BOOLEAN,
4783 "harmonic_weights_set" BOOLEAN,
4784 "closed_voting" BOOLEAN );
4786 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';
4789 CREATE FUNCTION "check_issue"
4790 ( "issue_id_p" "issue"."id"%TYPE,
4791 "persist" "check_issue_persistence" )
4792 RETURNS "check_issue_persistence"
4793 LANGUAGE 'plpgsql' VOLATILE AS $$
4794 DECLARE
4795 "issue_row" "issue"%ROWTYPE;
4796 "policy_row" "policy"%ROWTYPE;
4797 "initiative_row" "initiative"%ROWTYPE;
4798 "state_v" "issue_state";
4799 BEGIN
4800 PERFORM "require_transaction_isolation"();
4801 IF "persist" ISNULL THEN
4802 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4803 FOR UPDATE;
4804 IF "issue_row"."closed" NOTNULL THEN
4805 RETURN NULL;
4806 END IF;
4807 "persist"."state" := "issue_row"."state";
4808 IF
4809 ( "issue_row"."state" = 'admission' AND now() >=
4810 "issue_row"."created" + "issue_row"."max_admission_time" ) OR
4811 ( "issue_row"."state" = 'discussion' AND now() >=
4812 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
4813 ( "issue_row"."state" = 'verification' AND now() >=
4814 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
4815 ( "issue_row"."state" = 'voting' AND now() >=
4816 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
4817 THEN
4818 "persist"."phase_finished" := TRUE;
4819 ELSE
4820 "persist"."phase_finished" := FALSE;
4821 END IF;
4822 IF
4823 NOT EXISTS (
4824 -- all initiatives are revoked
4825 SELECT NULL FROM "initiative"
4826 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4827 ) AND (
4828 -- and issue has not been accepted yet
4829 "persist"."state" = 'admission' OR
4830 -- or verification time has elapsed
4831 ( "persist"."state" = 'verification' AND
4832 "persist"."phase_finished" ) OR
4833 -- or no initiatives have been revoked lately
4834 NOT EXISTS (
4835 SELECT NULL FROM "initiative"
4836 WHERE "issue_id" = "issue_id_p"
4837 AND now() < "revoked" + "issue_row"."verification_time"
4840 THEN
4841 "persist"."issue_revoked" := TRUE;
4842 ELSE
4843 "persist"."issue_revoked" := FALSE;
4844 END IF;
4845 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
4846 UPDATE "issue" SET "phase_finished" = now()
4847 WHERE "id" = "issue_row"."id";
4848 RETURN "persist";
4849 ELSIF
4850 "persist"."state" IN ('admission', 'discussion', 'verification')
4851 THEN
4852 RETURN "persist";
4853 ELSE
4854 RETURN NULL;
4855 END IF;
4856 END IF;
4857 IF
4858 "persist"."state" IN ('admission', 'discussion', 'verification') AND
4859 coalesce("persist"."snapshot_created", FALSE) = FALSE
4860 THEN
4861 PERFORM "create_snapshot"("issue_id_p");
4862 "persist"."snapshot_created" = TRUE;
4863 IF "persist"."phase_finished" THEN
4864 IF "persist"."state" = 'admission' THEN
4865 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
4866 ELSIF "persist"."state" = 'discussion' THEN
4867 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
4868 ELSIF "persist"."state" = 'verification' THEN
4869 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
4870 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4871 SELECT * INTO "policy_row" FROM "policy"
4872 WHERE "id" = "issue_row"."policy_id";
4873 FOR "initiative_row" IN
4874 SELECT * FROM "initiative"
4875 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4876 FOR UPDATE
4877 LOOP
4878 IF
4879 "initiative_row"."polling" OR (
4880 "initiative_row"."satisfied_supporter_count" > 0 AND
4881 "initiative_row"."satisfied_supporter_count" *
4882 "policy_row"."initiative_quorum_den" >=
4883 "issue_row"."population" * "policy_row"."initiative_quorum_num"
4885 THEN
4886 UPDATE "initiative" SET "admitted" = TRUE
4887 WHERE "id" = "initiative_row"."id";
4888 ELSE
4889 UPDATE "initiative" SET "admitted" = FALSE
4890 WHERE "id" = "initiative_row"."id";
4891 END IF;
4892 END LOOP;
4893 END IF;
4894 END IF;
4895 RETURN "persist";
4896 END IF;
4897 IF
4898 "persist"."state" IN ('admission', 'discussion', 'verification') AND
4899 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
4900 THEN
4901 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
4902 "persist"."harmonic_weights_set" = TRUE;
4903 IF
4904 "persist"."phase_finished" OR
4905 "persist"."issue_revoked" OR
4906 "persist"."state" = 'admission'
4907 THEN
4908 RETURN "persist";
4909 ELSE
4910 RETURN NULL;
4911 END IF;
4912 END IF;
4913 IF "persist"."issue_revoked" THEN
4914 IF "persist"."state" = 'admission' THEN
4915 "state_v" := 'canceled_revoked_before_accepted';
4916 ELSIF "persist"."state" = 'discussion' THEN
4917 "state_v" := 'canceled_after_revocation_during_discussion';
4918 ELSIF "persist"."state" = 'verification' THEN
4919 "state_v" := 'canceled_after_revocation_during_verification';
4920 END IF;
4921 UPDATE "issue" SET
4922 "state" = "state_v",
4923 "closed" = "phase_finished",
4924 "phase_finished" = NULL
4925 WHERE "id" = "issue_id_p";
4926 RETURN NULL;
4927 END IF;
4928 IF "persist"."state" = 'admission' THEN
4929 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4930 FOR UPDATE;
4931 SELECT * INTO "policy_row"
4932 FROM "policy" WHERE "id" = "issue_row"."policy_id";
4933 IF
4934 ( now() >=
4935 "issue_row"."created" + "issue_row"."min_admission_time" ) AND
4936 EXISTS (
4937 SELECT NULL FROM "initiative"
4938 WHERE "issue_id" = "issue_id_p"
4939 AND "supporter_count" > 0
4940 AND "supporter_count" * "policy_row"."issue_quorum_den"
4941 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
4943 THEN
4944 UPDATE "issue" SET
4945 "state" = 'discussion',
4946 "accepted" = coalesce("phase_finished", now()),
4947 "phase_finished" = NULL
4948 WHERE "id" = "issue_id_p";
4949 ELSIF "issue_row"."phase_finished" NOTNULL THEN
4950 UPDATE "issue" SET
4951 "state" = 'canceled_issue_not_accepted',
4952 "closed" = "phase_finished",
4953 "phase_finished" = NULL
4954 WHERE "id" = "issue_id_p";
4955 END IF;
4956 RETURN NULL;
4957 END IF;
4958 IF "persist"."phase_finished" THEN
4959 IF "persist"."state" = 'discussion' THEN
4960 UPDATE "issue" SET
4961 "state" = 'verification',
4962 "half_frozen" = "phase_finished",
4963 "phase_finished" = NULL
4964 WHERE "id" = "issue_id_p";
4965 RETURN NULL;
4966 END IF;
4967 IF "persist"."state" = 'verification' THEN
4968 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4969 FOR UPDATE;
4970 SELECT * INTO "policy_row" FROM "policy"
4971 WHERE "id" = "issue_row"."policy_id";
4972 IF EXISTS (
4973 SELECT NULL FROM "initiative"
4974 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
4975 ) THEN
4976 UPDATE "issue" SET
4977 "state" = 'voting',
4978 "fully_frozen" = "phase_finished",
4979 "phase_finished" = NULL
4980 WHERE "id" = "issue_id_p";
4981 ELSE
4982 UPDATE "issue" SET
4983 "state" = 'canceled_no_initiative_admitted',
4984 "fully_frozen" = "phase_finished",
4985 "closed" = "phase_finished",
4986 "phase_finished" = NULL
4987 WHERE "id" = "issue_id_p";
4988 -- NOTE: The following DELETE statements have effect only when
4989 -- issue state has been manipulated
4990 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
4991 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
4992 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
4993 END IF;
4994 RETURN NULL;
4995 END IF;
4996 IF "persist"."state" = 'voting' THEN
4997 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
4998 PERFORM "close_voting"("issue_id_p");
4999 "persist"."closed_voting" = TRUE;
5000 RETURN "persist";
5001 END IF;
5002 PERFORM "calculate_ranks"("issue_id_p");
5003 RETURN NULL;
5004 END IF;
5005 END IF;
5006 RAISE WARNING 'should not happen';
5007 RETURN NULL;
5008 END;
5009 $$;
5011 COMMENT ON FUNCTION "check_issue"
5012 ( "issue"."id"%TYPE,
5013 "check_issue_persistence" )
5014 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")';
5017 CREATE FUNCTION "check_everything"()
5018 RETURNS VOID
5019 LANGUAGE 'plpgsql' VOLATILE AS $$
5020 DECLARE
5021 "issue_id_v" "issue"."id"%TYPE;
5022 "persist_v" "check_issue_persistence";
5023 BEGIN
5024 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
5025 DELETE FROM "expired_session";
5026 PERFORM "check_activity"();
5027 PERFORM "calculate_member_counts"();
5028 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
5029 "persist_v" := NULL;
5030 LOOP
5031 "persist_v" := "check_issue"("issue_id_v", "persist_v");
5032 EXIT WHEN "persist_v" ISNULL;
5033 END LOOP;
5034 END LOOP;
5035 RETURN;
5036 END;
5037 $$;
5039 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.';
5043 ----------------------
5044 -- Deletion of data --
5045 ----------------------
5048 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
5049 RETURNS VOID
5050 LANGUAGE 'plpgsql' VOLATILE AS $$
5051 BEGIN
5052 IF EXISTS (
5053 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
5054 ) THEN
5055 -- override protection triggers:
5056 INSERT INTO "temporary_transaction_data" ("key", "value")
5057 VALUES ('override_protection_triggers', TRUE::TEXT);
5058 -- clean data:
5059 DELETE FROM "delegating_voter"
5060 WHERE "issue_id" = "issue_id_p";
5061 DELETE FROM "direct_voter"
5062 WHERE "issue_id" = "issue_id_p";
5063 DELETE FROM "delegating_interest_snapshot"
5064 WHERE "issue_id" = "issue_id_p";
5065 DELETE FROM "direct_interest_snapshot"
5066 WHERE "issue_id" = "issue_id_p";
5067 DELETE FROM "delegating_population_snapshot"
5068 WHERE "issue_id" = "issue_id_p";
5069 DELETE FROM "direct_population_snapshot"
5070 WHERE "issue_id" = "issue_id_p";
5071 DELETE FROM "non_voter"
5072 WHERE "issue_id" = "issue_id_p";
5073 DELETE FROM "delegation"
5074 WHERE "issue_id" = "issue_id_p";
5075 DELETE FROM "supporter"
5076 USING "initiative" -- NOTE: due to missing index on issue_id
5077 WHERE "initiative"."issue_id" = "issue_id_p"
5078 AND "supporter"."initiative_id" = "initiative_id";
5079 -- mark issue as cleaned:
5080 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
5081 -- finish overriding protection triggers (avoids garbage):
5082 DELETE FROM "temporary_transaction_data"
5083 WHERE "key" = 'override_protection_triggers';
5084 END IF;
5085 RETURN;
5086 END;
5087 $$;
5089 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
5092 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
5093 RETURNS VOID
5094 LANGUAGE 'plpgsql' VOLATILE AS $$
5095 BEGIN
5096 UPDATE "member" SET
5097 "last_login" = NULL,
5098 "last_delegation_check" = NULL,
5099 "login" = NULL,
5100 "password" = NULL,
5101 "authority" = NULL,
5102 "authority_uid" = NULL,
5103 "authority_login" = NULL,
5104 "locked" = TRUE,
5105 "active" = FALSE,
5106 "notify_email" = NULL,
5107 "notify_email_unconfirmed" = NULL,
5108 "notify_email_secret" = NULL,
5109 "notify_email_secret_expiry" = NULL,
5110 "notify_email_lock_expiry" = NULL,
5111 "disable_notifications" = TRUE,
5112 "notification_counter" = DEFAULT,
5113 "notification_sample_size" = 0,
5114 "notification_dow" = NULL,
5115 "notification_hour" = NULL,
5116 "login_recovery_expiry" = NULL,
5117 "password_reset_secret" = NULL,
5118 "password_reset_secret_expiry" = NULL,
5119 "organizational_unit" = NULL,
5120 "internal_posts" = NULL,
5121 "realname" = NULL,
5122 "birthday" = NULL,
5123 "address" = NULL,
5124 "email" = NULL,
5125 "xmpp_address" = NULL,
5126 "website" = NULL,
5127 "phone" = NULL,
5128 "mobile_phone" = NULL,
5129 "profession" = NULL,
5130 "external_memberships" = NULL,
5131 "external_posts" = NULL,
5132 "statement" = NULL
5133 WHERE "id" = "member_id_p";
5134 -- "text_search_data" is updated by triggers
5135 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
5136 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
5137 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
5138 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
5139 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
5140 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
5141 DELETE FROM "session" WHERE "member_id" = "member_id_p";
5142 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
5143 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
5144 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
5145 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
5146 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
5147 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
5148 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
5149 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
5150 DELETE FROM "direct_voter" USING "issue"
5151 WHERE "direct_voter"."issue_id" = "issue"."id"
5152 AND "issue"."closed" ISNULL
5153 AND "member_id" = "member_id_p";
5154 RETURN;
5155 END;
5156 $$;
5158 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)';
5161 CREATE FUNCTION "delete_private_data"()
5162 RETURNS VOID
5163 LANGUAGE 'plpgsql' VOLATILE AS $$
5164 BEGIN
5165 DELETE FROM "temporary_transaction_data";
5166 DELETE FROM "member" WHERE "activated" ISNULL;
5167 UPDATE "member" SET
5168 "invite_code" = NULL,
5169 "invite_code_expiry" = NULL,
5170 "admin_comment" = NULL,
5171 "last_login" = NULL,
5172 "last_delegation_check" = NULL,
5173 "login" = NULL,
5174 "password" = NULL,
5175 "authority" = NULL,
5176 "authority_uid" = NULL,
5177 "authority_login" = NULL,
5178 "lang" = NULL,
5179 "notify_email" = NULL,
5180 "notify_email_unconfirmed" = NULL,
5181 "notify_email_secret" = NULL,
5182 "notify_email_secret_expiry" = NULL,
5183 "notify_email_lock_expiry" = NULL,
5184 "disable_notifications" = TRUE,
5185 "notification_counter" = DEFAULT,
5186 "notification_sample_size" = 0,
5187 "notification_dow" = NULL,
5188 "notification_hour" = NULL,
5189 "login_recovery_expiry" = NULL,
5190 "password_reset_secret" = NULL,
5191 "password_reset_secret_expiry" = NULL,
5192 "organizational_unit" = NULL,
5193 "internal_posts" = NULL,
5194 "realname" = NULL,
5195 "birthday" = NULL,
5196 "address" = NULL,
5197 "email" = NULL,
5198 "xmpp_address" = NULL,
5199 "website" = NULL,
5200 "phone" = NULL,
5201 "mobile_phone" = NULL,
5202 "profession" = NULL,
5203 "external_memberships" = NULL,
5204 "external_posts" = NULL,
5205 "formatting_engine" = NULL,
5206 "statement" = NULL;
5207 -- "text_search_data" is updated by triggers
5208 DELETE FROM "setting";
5209 DELETE FROM "setting_map";
5210 DELETE FROM "member_relation_setting";
5211 DELETE FROM "member_image";
5212 DELETE FROM "contact";
5213 DELETE FROM "ignored_member";
5214 DELETE FROM "session";
5215 DELETE FROM "area_setting";
5216 DELETE FROM "issue_setting";
5217 DELETE FROM "ignored_initiative";
5218 DELETE FROM "initiative_setting";
5219 DELETE FROM "suggestion_setting";
5220 DELETE FROM "non_voter";
5221 DELETE FROM "direct_voter" USING "issue"
5222 WHERE "direct_voter"."issue_id" = "issue"."id"
5223 AND "issue"."closed" ISNULL;
5224 RETURN;
5225 END;
5226 $$;
5228 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.';
5232 COMMIT;

Impressum / About Us