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