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