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