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