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