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