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