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