liquid_feedback_core
view core.sql @ 426:c83b2ac5477b
Use a composite type of two INT8's to describe the strength of a link for the Schulze method (necessary for tie-breaking of the links); Removed "no_reverse_beat_path" option
| author | jbe |
|---|---|
| date | Wed May 21 22:01:43 2014 +0200 (2014-05-21) |
| parents | 514eacf18e36 |
| children | 97f85b51e3d9 |
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.2', 3, 0, 2))
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_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE,
390 CONSTRAINT "timing" CHECK (
391 ( "polling" = FALSE AND
392 "admission_time" NOTNULL AND "discussion_time" NOTNULL AND
393 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
394 ( "polling" = TRUE AND
395 "admission_time" ISNULL AND "discussion_time" NOTNULL AND
396 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
397 ( "polling" = TRUE AND
398 "admission_time" ISNULL AND "discussion_time" ISNULL AND
399 "verification_time" ISNULL AND "voting_time" ISNULL ) ),
400 CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK (
401 "polling" = "issue_quorum_num" ISNULL AND
402 "polling" = "issue_quorum_den" ISNULL ) );
403 CREATE INDEX "policy_active_idx" ON "policy" ("active");
405 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
407 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
408 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
409 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';
410 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';
411 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"';
412 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"';
413 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'')';
414 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''';
415 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''';
416 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
417 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
418 COMMENT ON COLUMN "policy"."defeat_strength" IS 'How pairwise defeats are measured for the Schulze method; see type "defeat_strength"';
419 COMMENT ON COLUMN "policy"."tie_breaking" IS 'Tie-breaker for the Schulze method; see type "tie_breaking"';
420 COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner';
421 COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner';
422 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.';
423 COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner';
424 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';
425 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';
426 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';
427 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.';
428 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';
429 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';
430 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.';
433 CREATE TABLE "unit" (
434 "id" SERIAL4 PRIMARY KEY,
435 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
436 "active" BOOLEAN NOT NULL DEFAULT TRUE,
437 "name" TEXT NOT NULL,
438 "description" TEXT NOT NULL DEFAULT '',
439 "member_count" INT4,
440 "text_search_data" TSVECTOR );
441 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
442 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
443 CREATE INDEX "unit_active_idx" ON "unit" ("active");
444 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
445 CREATE TRIGGER "update_text_search_data"
446 BEFORE INSERT OR UPDATE ON "unit"
447 FOR EACH ROW EXECUTE PROCEDURE
448 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
449 "name", "description" );
451 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
453 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
454 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this unit';
455 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
458 CREATE TABLE "unit_setting" (
459 PRIMARY KEY ("member_id", "key", "unit_id"),
460 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
461 "key" TEXT NOT NULL,
462 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
463 "value" TEXT NOT NULL );
465 COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings';
468 CREATE TABLE "area" (
469 "id" SERIAL4 PRIMARY KEY,
470 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
471 "active" BOOLEAN NOT NULL DEFAULT TRUE,
472 "name" TEXT NOT NULL,
473 "description" TEXT NOT NULL DEFAULT '',
474 "direct_member_count" INT4,
475 "member_weight" INT4,
476 "text_search_data" TSVECTOR );
477 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
478 CREATE INDEX "area_active_idx" ON "area" ("active");
479 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
480 CREATE TRIGGER "update_text_search_data"
481 BEFORE INSERT OR UPDATE ON "area"
482 FOR EACH ROW EXECUTE PROCEDURE
483 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
484 "name", "description" );
486 COMMENT ON TABLE "area" IS 'Subject areas';
488 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
489 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"';
490 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
493 CREATE TABLE "area_setting" (
494 PRIMARY KEY ("member_id", "key", "area_id"),
495 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
496 "key" TEXT NOT NULL,
497 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
498 "value" TEXT NOT NULL );
500 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
503 CREATE TABLE "allowed_policy" (
504 PRIMARY KEY ("area_id", "policy_id"),
505 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
506 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
507 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
508 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
510 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
512 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
515 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
517 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';
520 CREATE TYPE "issue_state" AS ENUM (
521 'admission', 'discussion', 'verification', 'voting',
522 'canceled_by_admin',
523 'canceled_revoked_before_accepted',
524 'canceled_issue_not_accepted',
525 'canceled_after_revocation_during_discussion',
526 'canceled_after_revocation_during_verification',
527 'canceled_no_initiative_admitted',
528 'finished_without_winner', 'finished_with_winner');
530 COMMENT ON TYPE "issue_state" IS 'State of issues';
533 CREATE TABLE "issue" (
534 "id" SERIAL4 PRIMARY KEY,
535 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
536 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
537 "admin_notice" TEXT,
538 "state" "issue_state" NOT NULL DEFAULT 'admission',
539 "phase_finished" TIMESTAMPTZ,
540 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
541 "accepted" TIMESTAMPTZ,
542 "half_frozen" TIMESTAMPTZ,
543 "fully_frozen" TIMESTAMPTZ,
544 "closed" TIMESTAMPTZ,
545 "cleaned" TIMESTAMPTZ,
546 "admission_time" INTERVAL,
547 "discussion_time" INTERVAL NOT NULL,
548 "verification_time" INTERVAL NOT NULL,
549 "voting_time" INTERVAL NOT NULL,
550 "snapshot" TIMESTAMPTZ,
551 "latest_snapshot_event" "snapshot_event",
552 "population" INT4,
553 "voter_count" INT4,
554 "status_quo_schulze_rank" INT4,
555 CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (
556 "admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created") ),
557 CONSTRAINT "valid_state" CHECK (
558 (
559 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
560 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
561 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL ) OR
562 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL)
563 ) AND (
564 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
565 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
566 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
567 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
568 ("state" = 'canceled_by_admin' AND "closed" NOTNULL) OR
569 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
570 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
571 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
572 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
573 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" = "fully_frozen") OR
574 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen") OR
575 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen")
576 )),
577 CONSTRAINT "phase_finished_only_when_not_closed" CHECK (
578 "phase_finished" ISNULL OR "closed" ISNULL ),
579 CONSTRAINT "state_change_order" CHECK (
580 "created" <= "accepted" AND
581 "accepted" <= "half_frozen" AND
582 "half_frozen" <= "fully_frozen" AND
583 "fully_frozen" <= "closed" ),
584 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
585 "cleaned" ISNULL OR "closed" NOTNULL ),
586 CONSTRAINT "last_snapshot_on_full_freeze"
587 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
588 CONSTRAINT "freeze_requires_snapshot"
589 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
590 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
591 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
592 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
593 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
594 CREATE INDEX "issue_created_idx" ON "issue" ("created");
595 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
596 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
597 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
598 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
599 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
600 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
602 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
604 COMMENT ON COLUMN "issue"."admin_notice" IS 'Public notice by admin to explain manual interventions, or to announce corrections';
605 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';
606 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
607 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.';
608 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.';
609 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.';
610 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
611 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
612 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
613 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
614 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
615 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
616 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';
617 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
618 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';
619 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
622 CREATE TABLE "issue_order_in_admission_state" (
623 "id" INT8 PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
624 "order_in_area" INT4,
625 "order_in_unit" INT4 );
627 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"';
629 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';
630 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';
631 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';
634 CREATE TABLE "issue_setting" (
635 PRIMARY KEY ("member_id", "key", "issue_id"),
636 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
637 "key" TEXT NOT NULL,
638 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
639 "value" TEXT NOT NULL );
641 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
644 CREATE TABLE "initiative" (
645 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
646 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
647 "id" SERIAL4 PRIMARY KEY,
648 "name" TEXT NOT NULL,
649 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
650 "discussion_url" TEXT,
651 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
652 "revoked" TIMESTAMPTZ,
653 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
654 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
655 "admitted" BOOLEAN,
656 "supporter_count" INT4,
657 "informed_supporter_count" INT4,
658 "satisfied_supporter_count" INT4,
659 "satisfied_informed_supporter_count" INT4,
660 "harmonic_weight" NUMERIC(12, 3),
661 "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE,
662 "first_preference_votes" INT4,
663 "positive_votes" INT4,
664 "negative_votes" INT4,
665 "direct_majority" BOOLEAN,
666 "indirect_majority" BOOLEAN,
667 "schulze_rank" INT4,
668 "better_than_status_quo" BOOLEAN,
669 "worse_than_status_quo" BOOLEAN,
670 "multistage_majority" BOOLEAN,
671 "eligible" BOOLEAN,
672 "winner" BOOLEAN,
673 "rank" INT4,
674 "text_search_data" TSVECTOR,
675 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
676 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
677 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
678 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
679 CONSTRAINT "revoked_initiatives_cant_be_admitted"
680 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
681 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
682 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
683 ( "first_preference_votes" ISNULL AND
684 "positive_votes" ISNULL AND "negative_votes" ISNULL AND
685 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
686 "schulze_rank" ISNULL AND
687 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
688 "multistage_majority" ISNULL AND
689 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ),
690 CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")),
691 CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (
692 "eligible" = FALSE OR
693 ("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ),
694 CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE),
695 CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1),
696 CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE),
697 CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") );
698 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
699 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
700 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
701 CREATE TRIGGER "update_text_search_data"
702 BEFORE INSERT OR UPDATE ON "initiative"
703 FOR EACH ROW EXECUTE PROCEDURE
704 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
705 "name", "discussion_url");
707 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.';
709 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative does not need to pass the initiative quorum (see "policy"."polling")';
710 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
711 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
712 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoke the initiative';
713 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
714 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
715 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
716 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
717 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
718 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';
719 COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time';
720 COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice';
721 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Number of direct and delegating voters who ranked this initiative better than the status quo';
722 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo';
723 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"';
724 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths';
725 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking';
726 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo';
727 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)';
728 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';
729 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 "multistage_majority"';
730 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank"';
731 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';
734 CREATE TABLE "battle" (
735 "issue_id" INT4 NOT NULL,
736 "winning_initiative_id" INT4,
737 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
738 "losing_initiative_id" INT4,
739 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
740 "count" INT4 NOT NULL,
741 CONSTRAINT "initiative_ids_not_equal" CHECK (
742 "winning_initiative_id" != "losing_initiative_id" OR
743 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
744 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) );
745 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
746 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
747 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
749 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';
752 CREATE TABLE "ignored_initiative" (
753 PRIMARY KEY ("initiative_id", "member_id"),
754 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
755 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
756 CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
758 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
761 CREATE TABLE "initiative_setting" (
762 PRIMARY KEY ("member_id", "key", "initiative_id"),
763 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
764 "key" TEXT NOT NULL,
765 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
766 "value" TEXT NOT NULL );
768 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
771 CREATE TABLE "draft" (
772 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
773 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
774 "id" SERIAL8 PRIMARY KEY,
775 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
776 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
777 "formatting_engine" TEXT,
778 "content" TEXT NOT NULL,
779 "text_search_data" TSVECTOR );
780 CREATE INDEX "draft_created_idx" ON "draft" ("created");
781 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
782 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
783 CREATE TRIGGER "update_text_search_data"
784 BEFORE INSERT OR UPDATE ON "draft"
785 FOR EACH ROW EXECUTE PROCEDURE
786 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
788 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.';
790 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
791 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
794 CREATE TABLE "rendered_draft" (
795 PRIMARY KEY ("draft_id", "format"),
796 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
797 "format" TEXT,
798 "content" TEXT NOT NULL );
800 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)';
803 CREATE TABLE "suggestion" (
804 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
805 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
806 "id" SERIAL8 PRIMARY KEY,
807 "draft_id" INT8 NOT NULL,
808 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
809 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
810 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
811 "name" TEXT NOT NULL,
812 "formatting_engine" TEXT,
813 "content" TEXT NOT NULL DEFAULT '',
814 "text_search_data" TSVECTOR,
815 "minus2_unfulfilled_count" INT4,
816 "minus2_fulfilled_count" INT4,
817 "minus1_unfulfilled_count" INT4,
818 "minus1_fulfilled_count" INT4,
819 "plus1_unfulfilled_count" INT4,
820 "plus1_fulfilled_count" INT4,
821 "plus2_unfulfilled_count" INT4,
822 "plus2_fulfilled_count" INT4,
823 "proportional_order" INT4 );
824 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
825 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
826 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
827 CREATE TRIGGER "update_text_search_data"
828 BEFORE INSERT OR UPDATE ON "suggestion"
829 FOR EACH ROW EXECUTE PROCEDURE
830 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
831 "name", "content");
833 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';
835 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")';
836 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
837 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
838 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
839 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
840 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
841 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
842 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
843 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
844 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"';
847 CREATE TABLE "rendered_suggestion" (
848 PRIMARY KEY ("suggestion_id", "format"),
849 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
850 "format" TEXT,
851 "content" TEXT NOT NULL );
853 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)';
856 CREATE TABLE "suggestion_setting" (
857 PRIMARY KEY ("member_id", "key", "suggestion_id"),
858 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
859 "key" TEXT NOT NULL,
860 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
861 "value" TEXT NOT NULL );
863 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
866 CREATE TABLE "privilege" (
867 PRIMARY KEY ("unit_id", "member_id"),
868 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
869 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
870 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
871 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
872 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
873 "member_manager" BOOLEAN NOT NULL DEFAULT FALSE,
874 "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE,
875 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE,
876 "polling_right" BOOLEAN NOT NULL DEFAULT FALSE );
878 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
880 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke any privileges to/from other members';
881 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create and disable sub units';
882 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create and disable areas and set area parameters';
883 COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';
884 COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';
885 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote';
886 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';
889 CREATE TABLE "membership" (
890 PRIMARY KEY ("area_id", "member_id"),
891 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
892 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
893 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
895 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
898 CREATE TABLE "interest" (
899 PRIMARY KEY ("issue_id", "member_id"),
900 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
901 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
902 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
904 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.';
907 CREATE TABLE "initiator" (
908 PRIMARY KEY ("initiative_id", "member_id"),
909 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
910 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
911 "accepted" BOOLEAN );
912 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
914 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.';
916 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.';
919 CREATE TABLE "supporter" (
920 "issue_id" INT4 NOT NULL,
921 PRIMARY KEY ("initiative_id", "member_id"),
922 "initiative_id" INT4,
923 "member_id" INT4,
924 "draft_id" INT8 NOT NULL,
925 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
926 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE );
927 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
929 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.';
931 COMMENT ON COLUMN "supporter"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
932 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")';
935 CREATE TABLE "opinion" (
936 "initiative_id" INT4 NOT NULL,
937 PRIMARY KEY ("suggestion_id", "member_id"),
938 "suggestion_id" INT8,
939 "member_id" INT4,
940 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
941 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
942 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
943 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
944 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
946 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.';
948 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
951 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
953 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
956 CREATE TABLE "delegation" (
957 "id" SERIAL8 PRIMARY KEY,
958 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
959 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
960 "scope" "delegation_scope" NOT NULL,
961 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
962 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
963 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
964 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
965 CONSTRAINT "no_unit_delegation_to_null"
966 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
967 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
968 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
969 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
970 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
971 UNIQUE ("unit_id", "truster_id"),
972 UNIQUE ("area_id", "truster_id"),
973 UNIQUE ("issue_id", "truster_id") );
974 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
975 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
977 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
979 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
980 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
981 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
984 CREATE TABLE "direct_population_snapshot" (
985 PRIMARY KEY ("issue_id", "event", "member_id"),
986 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
987 "event" "snapshot_event",
988 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
989 "weight" INT4 );
990 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
992 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';
994 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
995 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
998 CREATE TABLE "delegating_population_snapshot" (
999 PRIMARY KEY ("issue_id", "event", "member_id"),
1000 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1001 "event" "snapshot_event",
1002 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1003 "weight" INT4,
1004 "scope" "delegation_scope" NOT NULL,
1005 "delegate_member_ids" INT4[] NOT NULL );
1006 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
1008 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';
1010 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1011 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
1012 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
1013 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"';
1016 CREATE TABLE "direct_interest_snapshot" (
1017 PRIMARY KEY ("issue_id", "event", "member_id"),
1018 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1019 "event" "snapshot_event",
1020 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1021 "weight" INT4 );
1022 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
1024 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';
1026 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1027 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
1030 CREATE TABLE "delegating_interest_snapshot" (
1031 PRIMARY KEY ("issue_id", "event", "member_id"),
1032 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1033 "event" "snapshot_event",
1034 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1035 "weight" INT4,
1036 "scope" "delegation_scope" NOT NULL,
1037 "delegate_member_ids" INT4[] NOT NULL );
1038 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
1040 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';
1042 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1043 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
1044 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
1045 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"';
1048 CREATE TABLE "direct_supporter_snapshot" (
1049 "issue_id" INT4 NOT NULL,
1050 PRIMARY KEY ("initiative_id", "event", "member_id"),
1051 "initiative_id" INT4,
1052 "event" "snapshot_event",
1053 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1054 "draft_id" INT8 NOT NULL,
1055 "informed" BOOLEAN NOT NULL,
1056 "satisfied" BOOLEAN NOT NULL,
1057 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1058 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
1059 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1060 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
1062 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';
1064 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';
1065 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1066 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
1067 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
1070 CREATE TABLE "non_voter" (
1071 PRIMARY KEY ("issue_id", "member_id"),
1072 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1073 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
1074 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
1076 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
1079 CREATE TABLE "direct_voter" (
1080 PRIMARY KEY ("issue_id", "member_id"),
1081 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1082 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1083 "weight" INT4,
1084 "comment_changed" TIMESTAMPTZ,
1085 "formatting_engine" TEXT,
1086 "comment" TEXT,
1087 "text_search_data" TSVECTOR );
1088 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
1089 CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data");
1090 CREATE TRIGGER "update_text_search_data"
1091 BEFORE INSERT OR UPDATE ON "direct_voter"
1092 FOR EACH ROW EXECUTE PROCEDURE
1093 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment");
1095 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';
1097 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
1098 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';
1099 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';
1100 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.';
1103 CREATE TABLE "rendered_voter_comment" (
1104 PRIMARY KEY ("issue_id", "member_id", "format"),
1105 FOREIGN KEY ("issue_id", "member_id")
1106 REFERENCES "direct_voter" ("issue_id", "member_id")
1107 ON DELETE CASCADE ON UPDATE CASCADE,
1108 "issue_id" INT4,
1109 "member_id" INT4,
1110 "format" TEXT,
1111 "content" TEXT NOT NULL );
1113 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)';
1116 CREATE TABLE "delegating_voter" (
1117 PRIMARY KEY ("issue_id", "member_id"),
1118 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1119 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1120 "weight" INT4,
1121 "scope" "delegation_scope" NOT NULL,
1122 "delegate_member_ids" INT4[] NOT NULL );
1123 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
1125 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';
1127 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
1128 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
1129 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"';
1132 CREATE TABLE "vote" (
1133 "issue_id" INT4 NOT NULL,
1134 PRIMARY KEY ("initiative_id", "member_id"),
1135 "initiative_id" INT4,
1136 "member_id" INT4,
1137 "grade" INT4 NOT NULL,
1138 "first_preference" BOOLEAN,
1139 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1140 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
1141 CONSTRAINT "first_preference_flag_only_set_on_positive_grades"
1142 CHECK ("grade" > 0 OR "first_preference" ISNULL) );
1143 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
1145 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';
1147 COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
1148 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.';
1149 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.';
1152 CREATE TYPE "event_type" AS ENUM (
1153 'issue_state_changed',
1154 'initiative_created_in_new_issue',
1155 'initiative_created_in_existing_issue',
1156 'initiative_revoked',
1157 'new_draft_created',
1158 'suggestion_created');
1160 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
1163 CREATE TABLE "event" (
1164 "id" SERIAL8 PRIMARY KEY,
1165 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
1166 "event" "event_type" NOT NULL,
1167 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1168 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1169 "state" "issue_state",
1170 "initiative_id" INT4,
1171 "draft_id" INT8,
1172 "suggestion_id" INT8,
1173 FOREIGN KEY ("issue_id", "initiative_id")
1174 REFERENCES "initiative" ("issue_id", "id")
1175 ON DELETE CASCADE ON UPDATE CASCADE,
1176 FOREIGN KEY ("initiative_id", "draft_id")
1177 REFERENCES "draft" ("initiative_id", "id")
1178 ON DELETE CASCADE ON UPDATE CASCADE,
1179 FOREIGN KEY ("initiative_id", "suggestion_id")
1180 REFERENCES "suggestion" ("initiative_id", "id")
1181 ON DELETE CASCADE ON UPDATE CASCADE,
1182 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
1183 "event" != 'issue_state_changed' OR (
1184 "member_id" ISNULL AND
1185 "issue_id" NOTNULL AND
1186 "state" NOTNULL AND
1187 "initiative_id" ISNULL AND
1188 "draft_id" ISNULL AND
1189 "suggestion_id" ISNULL )),
1190 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
1191 "event" NOT IN (
1192 'initiative_created_in_new_issue',
1193 'initiative_created_in_existing_issue',
1194 'initiative_revoked',
1195 'new_draft_created'
1196 ) OR (
1197 "member_id" NOTNULL AND
1198 "issue_id" NOTNULL AND
1199 "state" NOTNULL AND
1200 "initiative_id" NOTNULL AND
1201 "draft_id" NOTNULL AND
1202 "suggestion_id" ISNULL )),
1203 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
1204 "event" != 'suggestion_created' OR (
1205 "member_id" NOTNULL AND
1206 "issue_id" NOTNULL AND
1207 "state" NOTNULL AND
1208 "initiative_id" NOTNULL AND
1209 "draft_id" ISNULL AND
1210 "suggestion_id" NOTNULL )) );
1211 CREATE INDEX "event_occurrence_idx" ON "event" ("occurrence");
1213 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
1215 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
1216 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
1217 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
1218 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
1221 CREATE TABLE "notification_sent" (
1222 "event_id" INT8 NOT NULL );
1223 CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1));
1225 COMMENT ON TABLE "notification_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';
1226 COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';
1230 ----------------------------------------------
1231 -- Writing of history entries and event log --
1232 ----------------------------------------------
1235 CREATE FUNCTION "write_member_history_trigger"()
1236 RETURNS TRIGGER
1237 LANGUAGE 'plpgsql' VOLATILE AS $$
1238 BEGIN
1239 IF
1240 ( NEW."active" != OLD."active" OR
1241 NEW."name" != OLD."name" ) AND
1242 OLD."activated" NOTNULL
1243 THEN
1244 INSERT INTO "member_history"
1245 ("member_id", "active", "name")
1246 VALUES (NEW."id", OLD."active", OLD."name");
1247 END IF;
1248 RETURN NULL;
1249 END;
1250 $$;
1252 CREATE TRIGGER "write_member_history"
1253 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1254 "write_member_history_trigger"();
1256 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1257 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1260 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1261 RETURNS TRIGGER
1262 LANGUAGE 'plpgsql' VOLATILE AS $$
1263 BEGIN
1264 IF NEW."state" != OLD."state" THEN
1265 INSERT INTO "event" ("event", "issue_id", "state")
1266 VALUES ('issue_state_changed', NEW."id", NEW."state");
1267 END IF;
1268 RETURN NULL;
1269 END;
1270 $$;
1272 CREATE TRIGGER "write_event_issue_state_changed"
1273 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1274 "write_event_issue_state_changed_trigger"();
1276 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1277 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1280 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1281 RETURNS TRIGGER
1282 LANGUAGE 'plpgsql' VOLATILE AS $$
1283 DECLARE
1284 "initiative_row" "initiative"%ROWTYPE;
1285 "issue_row" "issue"%ROWTYPE;
1286 "event_v" "event_type";
1287 BEGIN
1288 SELECT * INTO "initiative_row" FROM "initiative"
1289 WHERE "id" = NEW."initiative_id";
1290 SELECT * INTO "issue_row" FROM "issue"
1291 WHERE "id" = "initiative_row"."issue_id";
1292 IF EXISTS (
1293 SELECT NULL FROM "draft"
1294 WHERE "initiative_id" = NEW."initiative_id"
1295 AND "id" != NEW."id"
1296 ) THEN
1297 "event_v" := 'new_draft_created';
1298 ELSE
1299 IF EXISTS (
1300 SELECT NULL FROM "initiative"
1301 WHERE "issue_id" = "initiative_row"."issue_id"
1302 AND "id" != "initiative_row"."id"
1303 ) THEN
1304 "event_v" := 'initiative_created_in_existing_issue';
1305 ELSE
1306 "event_v" := 'initiative_created_in_new_issue';
1307 END IF;
1308 END IF;
1309 INSERT INTO "event" (
1310 "event", "member_id",
1311 "issue_id", "state", "initiative_id", "draft_id"
1312 ) VALUES (
1313 "event_v",
1314 NEW."author_id",
1315 "initiative_row"."issue_id",
1316 "issue_row"."state",
1317 "initiative_row"."id",
1318 NEW."id" );
1319 RETURN NULL;
1320 END;
1321 $$;
1323 CREATE TRIGGER "write_event_initiative_or_draft_created"
1324 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1325 "write_event_initiative_or_draft_created_trigger"();
1327 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1328 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1331 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1332 RETURNS TRIGGER
1333 LANGUAGE 'plpgsql' VOLATILE AS $$
1334 DECLARE
1335 "issue_row" "issue"%ROWTYPE;
1336 "draft_id_v" "draft"."id"%TYPE;
1337 BEGIN
1338 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1339 SELECT * INTO "issue_row" FROM "issue"
1340 WHERE "id" = NEW."issue_id";
1341 SELECT "id" INTO "draft_id_v" FROM "current_draft"
1342 WHERE "initiative_id" = NEW."id";
1343 INSERT INTO "event" (
1344 "event", "member_id", "issue_id", "state", "initiative_id", "draft_id"
1345 ) VALUES (
1346 'initiative_revoked',
1347 NEW."revoked_by_member_id",
1348 NEW."issue_id",
1349 "issue_row"."state",
1350 NEW."id",
1351 "draft_id_v");
1352 END IF;
1353 RETURN NULL;
1354 END;
1355 $$;
1357 CREATE TRIGGER "write_event_initiative_revoked"
1358 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1359 "write_event_initiative_revoked_trigger"();
1361 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1362 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1365 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1366 RETURNS TRIGGER
1367 LANGUAGE 'plpgsql' VOLATILE AS $$
1368 DECLARE
1369 "initiative_row" "initiative"%ROWTYPE;
1370 "issue_row" "issue"%ROWTYPE;
1371 BEGIN
1372 SELECT * INTO "initiative_row" FROM "initiative"
1373 WHERE "id" = NEW."initiative_id";
1374 SELECT * INTO "issue_row" FROM "issue"
1375 WHERE "id" = "initiative_row"."issue_id";
1376 INSERT INTO "event" (
1377 "event", "member_id",
1378 "issue_id", "state", "initiative_id", "suggestion_id"
1379 ) VALUES (
1380 'suggestion_created',
1381 NEW."author_id",
1382 "initiative_row"."issue_id",
1383 "issue_row"."state",
1384 "initiative_row"."id",
1385 NEW."id" );
1386 RETURN NULL;
1387 END;
1388 $$;
1390 CREATE TRIGGER "write_event_suggestion_created"
1391 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1392 "write_event_suggestion_created_trigger"();
1394 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1395 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1399 ----------------------------
1400 -- Additional constraints --
1401 ----------------------------
1404 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1405 RETURNS TRIGGER
1406 LANGUAGE 'plpgsql' VOLATILE AS $$
1407 BEGIN
1408 IF NOT EXISTS (
1409 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1410 ) THEN
1411 --RAISE 'Cannot create issue without an initial initiative.' USING
1412 -- ERRCODE = 'integrity_constraint_violation',
1413 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
1414 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
1415 END IF;
1416 RETURN NULL;
1417 END;
1418 $$;
1420 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1421 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1422 FOR EACH ROW EXECUTE PROCEDURE
1423 "issue_requires_first_initiative_trigger"();
1425 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1426 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1429 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1430 RETURNS TRIGGER
1431 LANGUAGE 'plpgsql' VOLATILE AS $$
1432 DECLARE
1433 "reference_lost" BOOLEAN;
1434 BEGIN
1435 IF TG_OP = 'DELETE' THEN
1436 "reference_lost" := TRUE;
1437 ELSE
1438 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1439 END IF;
1440 IF
1441 "reference_lost" AND NOT EXISTS (
1442 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1443 )
1444 THEN
1445 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1446 END IF;
1447 RETURN NULL;
1448 END;
1449 $$;
1451 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1452 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1453 FOR EACH ROW EXECUTE PROCEDURE
1454 "last_initiative_deletes_issue_trigger"();
1456 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1457 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1460 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1461 RETURNS TRIGGER
1462 LANGUAGE 'plpgsql' VOLATILE AS $$
1463 BEGIN
1464 IF NOT EXISTS (
1465 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1466 ) THEN
1467 --RAISE 'Cannot create initiative without an initial draft.' USING
1468 -- ERRCODE = 'integrity_constraint_violation',
1469 -- HINT = 'Create issue, initiative and draft within the same transaction.';
1470 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
1471 END IF;
1472 RETURN NULL;
1473 END;
1474 $$;
1476 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1477 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1478 FOR EACH ROW EXECUTE PROCEDURE
1479 "initiative_requires_first_draft_trigger"();
1481 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1482 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1485 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1486 RETURNS TRIGGER
1487 LANGUAGE 'plpgsql' VOLATILE AS $$
1488 DECLARE
1489 "reference_lost" BOOLEAN;
1490 BEGIN
1491 IF TG_OP = 'DELETE' THEN
1492 "reference_lost" := TRUE;
1493 ELSE
1494 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1495 END IF;
1496 IF
1497 "reference_lost" AND NOT EXISTS (
1498 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1499 )
1500 THEN
1501 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1502 END IF;
1503 RETURN NULL;
1504 END;
1505 $$;
1507 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1508 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1509 FOR EACH ROW EXECUTE PROCEDURE
1510 "last_draft_deletes_initiative_trigger"();
1512 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1513 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1516 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1517 RETURNS TRIGGER
1518 LANGUAGE 'plpgsql' VOLATILE AS $$
1519 BEGIN
1520 IF NOT EXISTS (
1521 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1522 ) THEN
1523 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
1524 END IF;
1525 RETURN NULL;
1526 END;
1527 $$;
1529 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1530 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1531 FOR EACH ROW EXECUTE PROCEDURE
1532 "suggestion_requires_first_opinion_trigger"();
1534 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1535 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1538 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1539 RETURNS TRIGGER
1540 LANGUAGE 'plpgsql' VOLATILE AS $$
1541 DECLARE
1542 "reference_lost" BOOLEAN;
1543 BEGIN
1544 IF TG_OP = 'DELETE' THEN
1545 "reference_lost" := TRUE;
1546 ELSE
1547 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1548 END IF;
1549 IF
1550 "reference_lost" AND NOT EXISTS (
1551 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1552 )
1553 THEN
1554 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1555 END IF;
1556 RETURN NULL;
1557 END;
1558 $$;
1560 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1561 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1562 FOR EACH ROW EXECUTE PROCEDURE
1563 "last_opinion_deletes_suggestion_trigger"();
1565 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1566 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1569 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()
1570 RETURNS TRIGGER
1571 LANGUAGE 'plpgsql' VOLATILE AS $$
1572 BEGIN
1573 DELETE FROM "direct_voter"
1574 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1575 RETURN NULL;
1576 END;
1577 $$;
1579 CREATE TRIGGER "non_voter_deletes_direct_voter"
1580 AFTER INSERT OR UPDATE ON "non_voter"
1581 FOR EACH ROW EXECUTE PROCEDURE
1582 "non_voter_deletes_direct_voter_trigger"();
1584 COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';
1585 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")';
1588 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()
1589 RETURNS TRIGGER
1590 LANGUAGE 'plpgsql' VOLATILE AS $$
1591 BEGIN
1592 DELETE FROM "non_voter"
1593 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1594 RETURN NULL;
1595 END;
1596 $$;
1598 CREATE TRIGGER "direct_voter_deletes_non_voter"
1599 AFTER INSERT OR UPDATE ON "direct_voter"
1600 FOR EACH ROW EXECUTE PROCEDURE
1601 "direct_voter_deletes_non_voter_trigger"();
1603 COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';
1604 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")';
1607 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()
1608 RETURNS TRIGGER
1609 LANGUAGE 'plpgsql' VOLATILE AS $$
1610 BEGIN
1611 IF NEW."comment" ISNULL THEN
1612 NEW."comment_changed" := NULL;
1613 NEW."formatting_engine" := NULL;
1614 END IF;
1615 RETURN NEW;
1616 END;
1617 $$;
1619 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"
1620 BEFORE INSERT OR UPDATE ON "direct_voter"
1621 FOR EACH ROW EXECUTE PROCEDURE
1622 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();
1624 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"';
1625 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.';
1628 ---------------------------------------------------------------
1629 -- Ensure that votes are not modified when issues are closed --
1630 ---------------------------------------------------------------
1632 -- NOTE: Frontends should ensure this anyway, but in case of programming
1633 -- errors the following triggers ensure data integrity.
1636 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1637 RETURNS TRIGGER
1638 LANGUAGE 'plpgsql' VOLATILE AS $$
1639 DECLARE
1640 "issue_id_v" "issue"."id"%TYPE;
1641 "issue_row" "issue"%ROWTYPE;
1642 BEGIN
1643 IF EXISTS (
1644 SELECT NULL FROM "temporary_transaction_data"
1645 WHERE "txid" = txid_current()
1646 AND "key" = 'override_protection_triggers'
1647 AND "value" = TRUE::TEXT
1648 ) THEN
1649 RETURN NULL;
1650 END IF;
1651 IF TG_OP = 'DELETE' THEN
1652 "issue_id_v" := OLD."issue_id";
1653 ELSE
1654 "issue_id_v" := NEW."issue_id";
1655 END IF;
1656 SELECT INTO "issue_row" * FROM "issue"
1657 WHERE "id" = "issue_id_v" FOR SHARE;
1658 IF (
1659 "issue_row"."closed" NOTNULL OR (
1660 "issue_row"."state" = 'voting' AND
1661 "issue_row"."phase_finished" NOTNULL
1662 )
1663 ) THEN
1664 IF
1665 TG_RELID = 'direct_voter'::regclass AND
1666 TG_OP = 'UPDATE'
1667 THEN
1668 IF
1669 OLD."issue_id" = NEW."issue_id" AND
1670 OLD."member_id" = NEW."member_id" AND
1671 OLD."weight" = NEW."weight"
1672 THEN
1673 RETURN NULL; -- allows changing of voter comment
1674 END IF;
1675 END IF;
1676 RAISE EXCEPTION 'Tried to modify data after voting has been closed.';
1677 END IF;
1678 RETURN NULL;
1679 END;
1680 $$;
1682 CREATE TRIGGER "forbid_changes_on_closed_issue"
1683 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1684 FOR EACH ROW EXECUTE PROCEDURE
1685 "forbid_changes_on_closed_issue_trigger"();
1687 CREATE TRIGGER "forbid_changes_on_closed_issue"
1688 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1689 FOR EACH ROW EXECUTE PROCEDURE
1690 "forbid_changes_on_closed_issue_trigger"();
1692 CREATE TRIGGER "forbid_changes_on_closed_issue"
1693 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1694 FOR EACH ROW EXECUTE PROCEDURE
1695 "forbid_changes_on_closed_issue_trigger"();
1697 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"';
1698 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';
1699 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';
1700 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';
1704 --------------------------------------------------------------------
1705 -- Auto-retrieval of fields only needed for referential integrity --
1706 --------------------------------------------------------------------
1709 CREATE FUNCTION "autofill_issue_id_trigger"()
1710 RETURNS TRIGGER
1711 LANGUAGE 'plpgsql' VOLATILE AS $$
1712 BEGIN
1713 IF NEW."issue_id" ISNULL THEN
1714 SELECT "issue_id" INTO NEW."issue_id"
1715 FROM "initiative" WHERE "id" = NEW."initiative_id";
1716 END IF;
1717 RETURN NEW;
1718 END;
1719 $$;
1721 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1722 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1724 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1725 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1727 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1728 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1729 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1732 CREATE FUNCTION "autofill_initiative_id_trigger"()
1733 RETURNS TRIGGER
1734 LANGUAGE 'plpgsql' VOLATILE AS $$
1735 BEGIN
1736 IF NEW."initiative_id" ISNULL THEN
1737 SELECT "initiative_id" INTO NEW."initiative_id"
1738 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1739 END IF;
1740 RETURN NEW;
1741 END;
1742 $$;
1744 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1745 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1747 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1748 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1752 -----------------------------------------------------
1753 -- Automatic calculation of certain default values --
1754 -----------------------------------------------------
1757 CREATE FUNCTION "copy_timings_trigger"()
1758 RETURNS TRIGGER
1759 LANGUAGE 'plpgsql' VOLATILE AS $$
1760 DECLARE
1761 "policy_row" "policy"%ROWTYPE;
1762 BEGIN
1763 SELECT * INTO "policy_row" FROM "policy"
1764 WHERE "id" = NEW."policy_id";
1765 IF NEW."admission_time" ISNULL THEN
1766 NEW."admission_time" := "policy_row"."admission_time";
1767 END IF;
1768 IF NEW."discussion_time" ISNULL THEN
1769 NEW."discussion_time" := "policy_row"."discussion_time";
1770 END IF;
1771 IF NEW."verification_time" ISNULL THEN
1772 NEW."verification_time" := "policy_row"."verification_time";
1773 END IF;
1774 IF NEW."voting_time" ISNULL THEN
1775 NEW."voting_time" := "policy_row"."voting_time";
1776 END IF;
1777 RETURN NEW;
1778 END;
1779 $$;
1781 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1782 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1784 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1785 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1788 CREATE FUNCTION "default_for_draft_id_trigger"()
1789 RETURNS TRIGGER
1790 LANGUAGE 'plpgsql' VOLATILE AS $$
1791 BEGIN
1792 IF NEW."draft_id" ISNULL THEN
1793 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1794 WHERE "initiative_id" = NEW."initiative_id";
1795 END IF;
1796 RETURN NEW;
1797 END;
1798 $$;
1800 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
1801 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1802 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1803 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1805 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
1806 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';
1807 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';
1811 ----------------------------------------
1812 -- Automatic creation of dependencies --
1813 ----------------------------------------
1816 CREATE FUNCTION "autocreate_interest_trigger"()
1817 RETURNS TRIGGER
1818 LANGUAGE 'plpgsql' VOLATILE AS $$
1819 BEGIN
1820 IF NOT EXISTS (
1821 SELECT NULL FROM "initiative" JOIN "interest"
1822 ON "initiative"."issue_id" = "interest"."issue_id"
1823 WHERE "initiative"."id" = NEW."initiative_id"
1824 AND "interest"."member_id" = NEW."member_id"
1825 ) THEN
1826 BEGIN
1827 INSERT INTO "interest" ("issue_id", "member_id")
1828 SELECT "issue_id", NEW."member_id"
1829 FROM "initiative" WHERE "id" = NEW."initiative_id";
1830 EXCEPTION WHEN unique_violation THEN END;
1831 END IF;
1832 RETURN NEW;
1833 END;
1834 $$;
1836 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1837 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1839 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1840 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';
1843 CREATE FUNCTION "autocreate_supporter_trigger"()
1844 RETURNS TRIGGER
1845 LANGUAGE 'plpgsql' VOLATILE AS $$
1846 BEGIN
1847 IF NOT EXISTS (
1848 SELECT NULL FROM "suggestion" JOIN "supporter"
1849 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1850 WHERE "suggestion"."id" = NEW."suggestion_id"
1851 AND "supporter"."member_id" = NEW."member_id"
1852 ) THEN
1853 BEGIN
1854 INSERT INTO "supporter" ("initiative_id", "member_id")
1855 SELECT "initiative_id", NEW."member_id"
1856 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1857 EXCEPTION WHEN unique_violation THEN END;
1858 END IF;
1859 RETURN NEW;
1860 END;
1861 $$;
1863 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1864 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1866 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1867 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.';
1871 ------------------------------------------
1872 -- Views and helper functions for views --
1873 ------------------------------------------
1876 CREATE VIEW "unit_delegation" AS
1877 SELECT
1878 "unit"."id" AS "unit_id",
1879 "delegation"."id",
1880 "delegation"."truster_id",
1881 "delegation"."trustee_id",
1882 "delegation"."scope"
1883 FROM "unit"
1884 JOIN "delegation"
1885 ON "delegation"."unit_id" = "unit"."id"
1886 JOIN "member"
1887 ON "delegation"."truster_id" = "member"."id"
1888 JOIN "privilege"
1889 ON "delegation"."unit_id" = "privilege"."unit_id"
1890 AND "delegation"."truster_id" = "privilege"."member_id"
1891 WHERE "member"."active" AND "privilege"."voting_right";
1893 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1896 CREATE VIEW "area_delegation" AS
1897 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1898 "area"."id" AS "area_id",
1899 "delegation"."id",
1900 "delegation"."truster_id",
1901 "delegation"."trustee_id",
1902 "delegation"."scope"
1903 FROM "area"
1904 JOIN "delegation"
1905 ON "delegation"."unit_id" = "area"."unit_id"
1906 OR "delegation"."area_id" = "area"."id"
1907 JOIN "member"
1908 ON "delegation"."truster_id" = "member"."id"
1909 JOIN "privilege"
1910 ON "area"."unit_id" = "privilege"."unit_id"
1911 AND "delegation"."truster_id" = "privilege"."member_id"
1912 WHERE "member"."active" AND "privilege"."voting_right"
1913 ORDER BY
1914 "area"."id",
1915 "delegation"."truster_id",
1916 "delegation"."scope" DESC;
1918 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1921 CREATE VIEW "issue_delegation" AS
1922 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1923 "issue"."id" AS "issue_id",
1924 "delegation"."id",
1925 "delegation"."truster_id",
1926 "delegation"."trustee_id",
1927 "delegation"."scope"
1928 FROM "issue"
1929 JOIN "area"
1930 ON "area"."id" = "issue"."area_id"
1931 JOIN "delegation"
1932 ON "delegation"."unit_id" = "area"."unit_id"
1933 OR "delegation"."area_id" = "area"."id"
1934 OR "delegation"."issue_id" = "issue"."id"
1935 JOIN "member"
1936 ON "delegation"."truster_id" = "member"."id"
1937 JOIN "privilege"
1938 ON "area"."unit_id" = "privilege"."unit_id"
1939 AND "delegation"."truster_id" = "privilege"."member_id"
1940 WHERE "member"."active" AND "privilege"."voting_right"
1941 ORDER BY
1942 "issue"."id",
1943 "delegation"."truster_id",
1944 "delegation"."scope" DESC;
1946 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
1949 CREATE FUNCTION "membership_weight_with_skipping"
1950 ( "area_id_p" "area"."id"%TYPE,
1951 "member_id_p" "member"."id"%TYPE,
1952 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1953 RETURNS INT4
1954 LANGUAGE 'plpgsql' STABLE AS $$
1955 DECLARE
1956 "sum_v" INT4;
1957 "delegation_row" "area_delegation"%ROWTYPE;
1958 BEGIN
1959 "sum_v" := 1;
1960 FOR "delegation_row" IN
1961 SELECT "area_delegation".*
1962 FROM "area_delegation" LEFT JOIN "membership"
1963 ON "membership"."area_id" = "area_id_p"
1964 AND "membership"."member_id" = "area_delegation"."truster_id"
1965 WHERE "area_delegation"."area_id" = "area_id_p"
1966 AND "area_delegation"."trustee_id" = "member_id_p"
1967 AND "membership"."member_id" ISNULL
1968 LOOP
1969 IF NOT
1970 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1971 THEN
1972 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1973 "area_id_p",
1974 "delegation_row"."truster_id",
1975 "skip_member_ids_p" || "delegation_row"."truster_id"
1976 );
1977 END IF;
1978 END LOOP;
1979 RETURN "sum_v";
1980 END;
1981 $$;
1983 COMMENT ON FUNCTION "membership_weight_with_skipping"
1984 ( "area"."id"%TYPE,
1985 "member"."id"%TYPE,
1986 INT4[] )
1987 IS 'Helper function for "membership_weight" function';
1990 CREATE FUNCTION "membership_weight"
1991 ( "area_id_p" "area"."id"%TYPE,
1992 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1993 RETURNS INT4
1994 LANGUAGE 'plpgsql' STABLE AS $$
1995 BEGIN
1996 RETURN "membership_weight_with_skipping"(
1997 "area_id_p",
1998 "member_id_p",
1999 ARRAY["member_id_p"]
2000 );
2001 END;
2002 $$;
2004 COMMENT ON FUNCTION "membership_weight"
2005 ( "area"."id"%TYPE,
2006 "member"."id"%TYPE )
2007 IS 'Calculates the potential voting weight of a member in a given area';
2010 CREATE VIEW "member_count_view" AS
2011 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
2013 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
2016 CREATE VIEW "unit_member_count" AS
2017 SELECT
2018 "unit"."id" AS "unit_id",
2019 count("member"."id") AS "member_count"
2020 FROM "unit"
2021 LEFT JOIN "privilege"
2022 ON "privilege"."unit_id" = "unit"."id"
2023 AND "privilege"."voting_right"
2024 LEFT JOIN "member"
2025 ON "member"."id" = "privilege"."member_id"
2026 AND "member"."active"
2027 GROUP BY "unit"."id";
2029 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
2032 CREATE VIEW "area_member_count" AS
2033 SELECT
2034 "area"."id" AS "area_id",
2035 count("member"."id") AS "direct_member_count",
2036 coalesce(
2037 sum(
2038 CASE WHEN "member"."id" NOTNULL THEN
2039 "membership_weight"("area"."id", "member"."id")
2040 ELSE 0 END
2041 )
2042 ) AS "member_weight"
2043 FROM "area"
2044 LEFT JOIN "membership"
2045 ON "area"."id" = "membership"."area_id"
2046 LEFT JOIN "privilege"
2047 ON "privilege"."unit_id" = "area"."unit_id"
2048 AND "privilege"."member_id" = "membership"."member_id"
2049 AND "privilege"."voting_right"
2050 LEFT JOIN "member"
2051 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
2052 AND "member"."active"
2053 GROUP BY "area"."id";
2055 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';
2058 CREATE VIEW "opening_draft" AS
2059 SELECT "draft".* FROM (
2060 SELECT
2061 "initiative"."id" AS "initiative_id",
2062 min("draft"."id") AS "draft_id"
2063 FROM "initiative" JOIN "draft"
2064 ON "initiative"."id" = "draft"."initiative_id"
2065 GROUP BY "initiative"."id"
2066 ) AS "subquery"
2067 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
2069 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
2072 CREATE VIEW "current_draft" AS
2073 SELECT "draft".* FROM (
2074 SELECT
2075 "initiative"."id" AS "initiative_id",
2076 max("draft"."id") AS "draft_id"
2077 FROM "initiative" JOIN "draft"
2078 ON "initiative"."id" = "draft"."initiative_id"
2079 GROUP BY "initiative"."id"
2080 ) AS "subquery"
2081 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
2083 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
2086 CREATE VIEW "critical_opinion" AS
2087 SELECT * FROM "opinion"
2088 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
2089 OR ("degree" = -2 AND "fulfilled" = TRUE);
2091 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
2094 CREATE VIEW "issue_supporter_in_admission_state" AS
2095 SELECT DISTINCT
2096 "area"."unit_id",
2097 "issue"."area_id",
2098 "issue"."id" AS "issue_id",
2099 "supporter"."member_id",
2100 "direct_interest_snapshot"."weight"
2101 FROM "issue"
2102 JOIN "area" ON "area"."id" = "issue"."area_id"
2103 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
2104 JOIN "direct_interest_snapshot"
2105 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
2106 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
2107 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
2108 WHERE "issue"."state" = 'admission'::"issue_state";
2110 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';
2113 CREATE VIEW "initiative_suggestion_order_calculation" AS
2114 SELECT
2115 "initiative"."id" AS "initiative_id",
2116 ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final"
2117 FROM "initiative" JOIN "issue"
2118 ON "initiative"."issue_id" = "issue"."id"
2119 WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL)
2120 OR ("initiative"."final_suggestion_order_calculated" = FALSE);
2122 COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';
2124 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';
2127 CREATE VIEW "individual_suggestion_ranking" AS
2128 SELECT
2129 "opinion"."initiative_id",
2130 "opinion"."member_id",
2131 "direct_interest_snapshot"."weight",
2132 CASE WHEN
2133 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
2134 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
2135 THEN 1 ELSE
2136 CASE WHEN
2137 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
2138 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
2139 THEN 2 ELSE
2140 CASE WHEN
2141 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
2142 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
2143 THEN 3 ELSE 4 END
2144 END
2145 END AS "preference",
2146 "opinion"."suggestion_id"
2147 FROM "opinion"
2148 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
2149 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2150 JOIN "direct_interest_snapshot"
2151 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
2152 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
2153 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
2155 COMMENT ON VIEW "individual_suggestion_ranking" IS 'Helper view for "lf_update_suggestion_order" to allow a proportional ordering of suggestions within an initiative';
2158 CREATE VIEW "battle_participant" AS
2159 SELECT "initiative"."id", "initiative"."issue_id"
2160 FROM "issue" JOIN "initiative"
2161 ON "issue"."id" = "initiative"."issue_id"
2162 WHERE "initiative"."admitted"
2163 UNION ALL
2164 SELECT NULL, "id" AS "issue_id"
2165 FROM "issue";
2167 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
2170 CREATE VIEW "battle_view" AS
2171 SELECT
2172 "issue"."id" AS "issue_id",
2173 "winning_initiative"."id" AS "winning_initiative_id",
2174 "losing_initiative"."id" AS "losing_initiative_id",
2175 sum(
2176 CASE WHEN
2177 coalesce("better_vote"."grade", 0) >
2178 coalesce("worse_vote"."grade", 0)
2179 THEN "direct_voter"."weight" ELSE 0 END
2180 ) AS "count"
2181 FROM "issue"
2182 LEFT JOIN "direct_voter"
2183 ON "issue"."id" = "direct_voter"."issue_id"
2184 JOIN "battle_participant" AS "winning_initiative"
2185 ON "issue"."id" = "winning_initiative"."issue_id"
2186 JOIN "battle_participant" AS "losing_initiative"
2187 ON "issue"."id" = "losing_initiative"."issue_id"
2188 LEFT JOIN "vote" AS "better_vote"
2189 ON "direct_voter"."member_id" = "better_vote"."member_id"
2190 AND "winning_initiative"."id" = "better_vote"."initiative_id"
2191 LEFT JOIN "vote" AS "worse_vote"
2192 ON "direct_voter"."member_id" = "worse_vote"."member_id"
2193 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
2194 WHERE "issue"."state" = 'voting'
2195 AND "issue"."phase_finished" NOTNULL
2196 AND (
2197 "winning_initiative"."id" != "losing_initiative"."id" OR
2198 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
2199 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
2200 GROUP BY
2201 "issue"."id",
2202 "winning_initiative"."id",
2203 "losing_initiative"."id";
2205 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';
2208 CREATE VIEW "expired_session" AS
2209 SELECT * FROM "session" WHERE now() > "expiry";
2211 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
2212 DELETE FROM "session" WHERE "ident" = OLD."ident";
2214 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
2215 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
2218 CREATE VIEW "open_issue" AS
2219 SELECT * FROM "issue" WHERE "closed" ISNULL;
2221 COMMENT ON VIEW "open_issue" IS 'All open issues';
2224 CREATE VIEW "member_contingent" AS
2225 SELECT
2226 "member"."id" AS "member_id",
2227 "contingent"."polling",
2228 "contingent"."time_frame",
2229 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
2230 (
2231 SELECT count(1) FROM "draft"
2232 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
2233 WHERE "draft"."author_id" = "member"."id"
2234 AND "initiative"."polling" = "contingent"."polling"
2235 AND "draft"."created" > now() - "contingent"."time_frame"
2236 ) + (
2237 SELECT count(1) FROM "suggestion"
2238 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
2239 WHERE "suggestion"."author_id" = "member"."id"
2240 AND "contingent"."polling" = FALSE
2241 AND "suggestion"."created" > now() - "contingent"."time_frame"
2242 )
2243 ELSE NULL END AS "text_entry_count",
2244 "contingent"."text_entry_limit",
2245 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
2246 SELECT count(1) FROM "opening_draft" AS "draft"
2247 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
2248 WHERE "draft"."author_id" = "member"."id"
2249 AND "initiative"."polling" = "contingent"."polling"
2250 AND "draft"."created" > now() - "contingent"."time_frame"
2251 ) ELSE NULL END AS "initiative_count",
2252 "contingent"."initiative_limit"
2253 FROM "member" CROSS JOIN "contingent";
2255 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
2257 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
2258 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
2261 CREATE VIEW "member_contingent_left" AS
2262 SELECT
2263 "member_id",
2264 "polling",
2265 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
2266 max("initiative_limit" - "initiative_count") AS "initiatives_left"
2267 FROM "member_contingent" GROUP BY "member_id", "polling";
2269 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.';
2272 CREATE VIEW "event_seen_by_member" AS
2273 SELECT
2274 "member"."id" AS "seen_by_member_id",
2275 CASE WHEN "event"."state" IN (
2276 'voting',
2277 'finished_without_winner',
2278 'finished_with_winner'
2279 ) THEN
2280 'voting'::"notify_level"
2281 ELSE
2282 CASE WHEN "event"."state" IN (
2283 'verification',
2284 'canceled_after_revocation_during_verification',
2285 'canceled_no_initiative_admitted'
2286 ) THEN
2287 'verification'::"notify_level"
2288 ELSE
2289 CASE WHEN "event"."state" IN (
2290 'discussion',
2291 'canceled_after_revocation_during_discussion'
2292 ) THEN
2293 'discussion'::"notify_level"
2294 ELSE
2295 'all'::"notify_level"
2296 END
2297 END
2298 END AS "notify_level",
2299 "event".*
2300 FROM "member" CROSS JOIN "event"
2301 LEFT JOIN "issue"
2302 ON "event"."issue_id" = "issue"."id"
2303 LEFT JOIN "membership"
2304 ON "member"."id" = "membership"."member_id"
2305 AND "issue"."area_id" = "membership"."area_id"
2306 LEFT JOIN "interest"
2307 ON "member"."id" = "interest"."member_id"
2308 AND "event"."issue_id" = "interest"."issue_id"
2309 LEFT JOIN "ignored_member"
2310 ON "member"."id" = "ignored_member"."member_id"
2311 AND "event"."member_id" = "ignored_member"."other_member_id"
2312 LEFT JOIN "ignored_initiative"
2313 ON "member"."id" = "ignored_initiative"."member_id"
2314 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2315 WHERE (
2316 "interest"."member_id" NOTNULL OR
2317 ( "membership"."member_id" NOTNULL AND
2318 "event"."event" IN (
2319 'issue_state_changed',
2320 'initiative_created_in_new_issue',
2321 'initiative_created_in_existing_issue',
2322 'initiative_revoked' ) ) )
2323 AND "ignored_member"."member_id" ISNULL
2324 AND "ignored_initiative"."member_id" ISNULL;
2326 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"';
2329 CREATE VIEW "selected_event_seen_by_member" AS
2330 SELECT
2331 "member"."id" AS "seen_by_member_id",
2332 CASE WHEN "event"."state" IN (
2333 'voting',
2334 'finished_without_winner',
2335 'finished_with_winner'
2336 ) THEN
2337 'voting'::"notify_level"
2338 ELSE
2339 CASE WHEN "event"."state" IN (
2340 'verification',
2341 'canceled_after_revocation_during_verification',
2342 'canceled_no_initiative_admitted'
2343 ) THEN
2344 'verification'::"notify_level"
2345 ELSE
2346 CASE WHEN "event"."state" IN (
2347 'discussion',
2348 'canceled_after_revocation_during_discussion'
2349 ) THEN
2350 'discussion'::"notify_level"
2351 ELSE
2352 'all'::"notify_level"
2353 END
2354 END
2355 END AS "notify_level",
2356 "event".*
2357 FROM "member" CROSS JOIN "event"
2358 LEFT JOIN "issue"
2359 ON "event"."issue_id" = "issue"."id"
2360 LEFT JOIN "membership"
2361 ON "member"."id" = "membership"."member_id"
2362 AND "issue"."area_id" = "membership"."area_id"
2363 LEFT JOIN "interest"
2364 ON "member"."id" = "interest"."member_id"
2365 AND "event"."issue_id" = "interest"."issue_id"
2366 LEFT JOIN "ignored_member"
2367 ON "member"."id" = "ignored_member"."member_id"
2368 AND "event"."member_id" = "ignored_member"."other_member_id"
2369 LEFT JOIN "ignored_initiative"
2370 ON "member"."id" = "ignored_initiative"."member_id"
2371 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2372 WHERE (
2373 ( "member"."notify_level" >= 'all' ) OR
2374 ( "member"."notify_level" >= 'voting' AND
2375 "event"."state" IN (
2376 'voting',
2377 'finished_without_winner',
2378 'finished_with_winner' ) ) OR
2379 ( "member"."notify_level" >= 'verification' AND
2380 "event"."state" IN (
2381 'verification',
2382 'canceled_after_revocation_during_verification',
2383 'canceled_no_initiative_admitted' ) ) OR
2384 ( "member"."notify_level" >= 'discussion' AND
2385 "event"."state" IN (
2386 'discussion',
2387 'canceled_after_revocation_during_discussion' ) ) )
2388 AND (
2389 "interest"."member_id" NOTNULL OR
2390 ( "membership"."member_id" NOTNULL AND
2391 "event"."event" IN (
2392 'issue_state_changed',
2393 'initiative_created_in_new_issue',
2394 'initiative_created_in_existing_issue',
2395 'initiative_revoked' ) ) )
2396 AND "ignored_member"."member_id" ISNULL
2397 AND "ignored_initiative"."member_id" ISNULL;
2399 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"';
2403 ------------------------------------------------------
2404 -- Row set returning function for delegation chains --
2405 ------------------------------------------------------
2408 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2409 ('first', 'intermediate', 'last', 'repetition');
2411 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2414 CREATE TYPE "delegation_chain_row" AS (
2415 "index" INT4,
2416 "member_id" INT4,
2417 "member_valid" BOOLEAN,
2418 "participation" BOOLEAN,
2419 "overridden" BOOLEAN,
2420 "scope_in" "delegation_scope",
2421 "scope_out" "delegation_scope",
2422 "disabled_out" BOOLEAN,
2423 "loop" "delegation_chain_loop_tag" );
2425 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
2427 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2428 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';
2429 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2430 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2431 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2432 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2433 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2436 CREATE FUNCTION "delegation_chain_for_closed_issue"
2437 ( "member_id_p" "member"."id"%TYPE,
2438 "issue_id_p" "issue"."id"%TYPE )
2439 RETURNS SETOF "delegation_chain_row"
2440 LANGUAGE 'plpgsql' STABLE AS $$
2441 DECLARE
2442 "output_row" "delegation_chain_row";
2443 "direct_voter_row" "direct_voter"%ROWTYPE;
2444 "delegating_voter_row" "delegating_voter"%ROWTYPE;
2445 BEGIN
2446 "output_row"."index" := 0;
2447 "output_row"."member_id" := "member_id_p";
2448 "output_row"."member_valid" := TRUE;
2449 "output_row"."participation" := FALSE;
2450 "output_row"."overridden" := FALSE;
2451 "output_row"."disabled_out" := FALSE;
2452 LOOP
2453 SELECT INTO "direct_voter_row" * FROM "direct_voter"
2454 WHERE "issue_id" = "issue_id_p"
2455 AND "member_id" = "output_row"."member_id";
2456 IF "direct_voter_row"."member_id" NOTNULL THEN
2457 "output_row"."participation" := TRUE;
2458 "output_row"."scope_out" := NULL;
2459 "output_row"."disabled_out" := NULL;
2460 RETURN NEXT "output_row";
2461 RETURN;
2462 END IF;
2463 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
2464 WHERE "issue_id" = "issue_id_p"
2465 AND "member_id" = "output_row"."member_id";
2466 IF "delegating_voter_row"."member_id" ISNULL THEN
2467 RETURN;
2468 END IF;
2469 "output_row"."scope_out" := "delegating_voter_row"."scope";
2470 RETURN NEXT "output_row";
2471 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
2472 "output_row"."scope_in" := "output_row"."scope_out";
2473 END LOOP;
2474 END;
2475 $$;
2477 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
2478 ( "member"."id"%TYPE,
2479 "member"."id"%TYPE )
2480 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
2483 CREATE FUNCTION "delegation_chain"
2484 ( "member_id_p" "member"."id"%TYPE,
2485 "unit_id_p" "unit"."id"%TYPE,
2486 "area_id_p" "area"."id"%TYPE,
2487 "issue_id_p" "issue"."id"%TYPE,
2488 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2489 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2490 RETURNS SETOF "delegation_chain_row"
2491 LANGUAGE 'plpgsql' STABLE AS $$
2492 DECLARE
2493 "scope_v" "delegation_scope";
2494 "unit_id_v" "unit"."id"%TYPE;
2495 "area_id_v" "area"."id"%TYPE;
2496 "issue_row" "issue"%ROWTYPE;
2497 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2498 "loop_member_id_v" "member"."id"%TYPE;
2499 "output_row" "delegation_chain_row";
2500 "output_rows" "delegation_chain_row"[];
2501 "simulate_v" BOOLEAN;
2502 "simulate_here_v" BOOLEAN;
2503 "delegation_row" "delegation"%ROWTYPE;
2504 "row_count" INT4;
2505 "i" INT4;
2506 "loop_v" BOOLEAN;
2507 BEGIN
2508 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
2509 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
2510 END IF;
2511 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
2512 "simulate_v" := TRUE;
2513 ELSE
2514 "simulate_v" := FALSE;
2515 END IF;
2516 IF
2517 "unit_id_p" NOTNULL AND
2518 "area_id_p" ISNULL AND
2519 "issue_id_p" ISNULL
2520 THEN
2521 "scope_v" := 'unit';
2522 "unit_id_v" := "unit_id_p";
2523 ELSIF
2524 "unit_id_p" ISNULL AND
2525 "area_id_p" NOTNULL AND
2526 "issue_id_p" ISNULL
2527 THEN
2528 "scope_v" := 'area';
2529 "area_id_v" := "area_id_p";
2530 SELECT "unit_id" INTO "unit_id_v"
2531 FROM "area" WHERE "id" = "area_id_v";
2532 ELSIF
2533 "unit_id_p" ISNULL AND
2534 "area_id_p" ISNULL AND
2535 "issue_id_p" NOTNULL
2536 THEN
2537 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
2538 IF "issue_row"."id" ISNULL THEN
2539 RETURN;
2540 END IF;
2541 IF "issue_row"."closed" NOTNULL THEN
2542 IF "simulate_v" THEN
2543 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
2544 END IF;
2545 FOR "output_row" IN
2546 SELECT * FROM
2547 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
2548 LOOP
2549 RETURN NEXT "output_row";
2550 END LOOP;
2551 RETURN;
2552 END IF;
2553 "scope_v" := 'issue';
2554 SELECT "area_id" INTO "area_id_v"
2555 FROM "issue" WHERE "id" = "issue_id_p";
2556 SELECT "unit_id" INTO "unit_id_v"
2557 FROM "area" WHERE "id" = "area_id_v";
2558 ELSE
2559 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2560 END IF;
2561 "visited_member_ids" := '{}';
2562 "loop_member_id_v" := NULL;
2563 "output_rows" := '{}';
2564 "output_row"."index" := 0;
2565 "output_row"."member_id" := "member_id_p";
2566 "output_row"."member_valid" := TRUE;
2567 "output_row"."participation" := FALSE;
2568 "output_row"."overridden" := FALSE;
2569 "output_row"."disabled_out" := FALSE;
2570 "output_row"."scope_out" := NULL;
2571 LOOP
2572 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2573 "loop_member_id_v" := "output_row"."member_id";
2574 ELSE
2575 "visited_member_ids" :=
2576 "visited_member_ids" || "output_row"."member_id";
2577 END IF;
2578 IF "output_row"."participation" ISNULL THEN
2579 "output_row"."overridden" := NULL;
2580 ELSIF "output_row"."participation" THEN
2581 "output_row"."overridden" := TRUE;
2582 END IF;
2583 "output_row"."scope_in" := "output_row"."scope_out";
2584 "output_row"."member_valid" := EXISTS (
2585 SELECT NULL FROM "member" JOIN "privilege"
2586 ON "privilege"."member_id" = "member"."id"
2587 AND "privilege"."unit_id" = "unit_id_v"
2588 WHERE "id" = "output_row"."member_id"
2589 AND "member"."active" AND "privilege"."voting_right"
2590 );
2591 "simulate_here_v" := (
2592 "simulate_v" AND
2593 "output_row"."member_id" = "member_id_p"
2594 );
2595 "delegation_row" := ROW(NULL);
2596 IF "output_row"."member_valid" OR "simulate_here_v" THEN
2597 IF "scope_v" = 'unit' THEN
2598 IF NOT "simulate_here_v" THEN
2599 SELECT * INTO "delegation_row" FROM "delegation"
2600 WHERE "truster_id" = "output_row"."member_id"
2601 AND "unit_id" = "unit_id_v";
2602 END IF;
2603 ELSIF "scope_v" = 'area' THEN
2604 "output_row"."participation" := EXISTS (
2605 SELECT NULL FROM "membership"
2606 WHERE "area_id" = "area_id_p"
2607 AND "member_id" = "output_row"."member_id"
2608 );
2609 IF "simulate_here_v" THEN
2610 IF "simulate_trustee_id_p" ISNULL THEN
2611 SELECT * INTO "delegation_row" FROM "delegation"
2612 WHERE "truster_id" = "output_row"."member_id"
2613 AND "unit_id" = "unit_id_v";
2614 END IF;
2615 ELSE
2616 SELECT * INTO "delegation_row" FROM "delegation"
2617 WHERE "truster_id" = "output_row"."member_id"
2618 AND (
2619 "unit_id" = "unit_id_v" OR
2620 "area_id" = "area_id_v"
2621 )
2622 ORDER BY "scope" DESC;
2623 END IF;
2624 ELSIF "scope_v" = 'issue' THEN
2625 IF "issue_row"."fully_frozen" ISNULL THEN
2626 "output_row"."participation" := EXISTS (
2627 SELECT NULL FROM "interest"
2628 WHERE "issue_id" = "issue_id_p"
2629 AND "member_id" = "output_row"."member_id"
2630 );
2631 ELSE
2632 IF "output_row"."member_id" = "member_id_p" THEN
2633 "output_row"."participation" := EXISTS (
2634 SELECT NULL FROM "direct_voter"
2635 WHERE "issue_id" = "issue_id_p"
2636 AND "member_id" = "output_row"."member_id"
2637 );
2638 ELSE
2639 "output_row"."participation" := NULL;
2640 END IF;
2641 END IF;
2642 IF "simulate_here_v" THEN
2643 IF "simulate_trustee_id_p" ISNULL THEN
2644 SELECT * INTO "delegation_row" FROM "delegation"
2645 WHERE "truster_id" = "output_row"."member_id"
2646 AND (
2647 "unit_id" = "unit_id_v" OR
2648 "area_id" = "area_id_v"
2649 )
2650 ORDER BY "scope" DESC;
2651 END IF;
2652 ELSE
2653 SELECT * INTO "delegation_row" FROM "delegation"
2654 WHERE "truster_id" = "output_row"."member_id"
2655 AND (
2656 "unit_id" = "unit_id_v" OR
2657 "area_id" = "area_id_v" OR
2658 "issue_id" = "issue_id_p"
2659 )
2660 ORDER BY "scope" DESC;
2661 END IF;
2662 END IF;
2663 ELSE
2664 "output_row"."participation" := FALSE;
2665 END IF;
2666 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
2667 "output_row"."scope_out" := "scope_v";
2668 "output_rows" := "output_rows" || "output_row";
2669 "output_row"."member_id" := "simulate_trustee_id_p";
2670 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2671 "output_row"."scope_out" := "delegation_row"."scope";
2672 "output_rows" := "output_rows" || "output_row";
2673 "output_row"."member_id" := "delegation_row"."trustee_id";
2674 ELSIF "delegation_row"."scope" NOTNULL THEN
2675 "output_row"."scope_out" := "delegation_row"."scope";
2676 "output_row"."disabled_out" := TRUE;
2677 "output_rows" := "output_rows" || "output_row";
2678 EXIT;
2679 ELSE
2680 "output_row"."scope_out" := NULL;
2681 "output_rows" := "output_rows" || "output_row";
2682 EXIT;
2683 END IF;
2684 EXIT WHEN "loop_member_id_v" NOTNULL;
2685 "output_row"."index" := "output_row"."index" + 1;
2686 END LOOP;
2687 "row_count" := array_upper("output_rows", 1);
2688 "i" := 1;
2689 "loop_v" := FALSE;
2690 LOOP
2691 "output_row" := "output_rows"["i"];
2692 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2693 IF "loop_v" THEN
2694 IF "i" + 1 = "row_count" THEN
2695 "output_row"."loop" := 'last';
2696 ELSIF "i" = "row_count" THEN
2697 "output_row"."loop" := 'repetition';
2698 ELSE
2699 "output_row"."loop" := 'intermediate';
2700 END IF;
2701 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2702 "output_row"."loop" := 'first';
2703 "loop_v" := TRUE;
2704 END IF;
2705 IF "scope_v" = 'unit' THEN
2706 "output_row"."participation" := NULL;
2707 END IF;
2708 RETURN NEXT "output_row";
2709 "i" := "i" + 1;
2710 END LOOP;
2711 RETURN;
2712 END;
2713 $$;
2715 COMMENT ON FUNCTION "delegation_chain"
2716 ( "member"."id"%TYPE,
2717 "unit"."id"%TYPE,
2718 "area"."id"%TYPE,
2719 "issue"."id"%TYPE,
2720 "member"."id"%TYPE,
2721 BOOLEAN )
2722 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
2726 ---------------------------------------------------------
2727 -- Single row returning function for delegation chains --
2728 ---------------------------------------------------------
2731 CREATE TYPE "delegation_info_loop_type" AS ENUM
2732 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
2734 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''';
2737 CREATE TYPE "delegation_info_type" AS (
2738 "own_participation" BOOLEAN,
2739 "own_delegation_scope" "delegation_scope",
2740 "first_trustee_id" INT4,
2741 "first_trustee_participation" BOOLEAN,
2742 "first_trustee_ellipsis" BOOLEAN,
2743 "other_trustee_id" INT4,
2744 "other_trustee_participation" BOOLEAN,
2745 "other_trustee_ellipsis" BOOLEAN,
2746 "delegation_loop" "delegation_info_loop_type",
2747 "participating_member_id" INT4 );
2749 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';
2751 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
2752 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
2753 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
2754 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
2755 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
2756 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
2757 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)';
2758 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
2759 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';
2760 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';
2763 CREATE FUNCTION "delegation_info"
2764 ( "member_id_p" "member"."id"%TYPE,
2765 "unit_id_p" "unit"."id"%TYPE,
2766 "area_id_p" "area"."id"%TYPE,
2767 "issue_id_p" "issue"."id"%TYPE,
2768 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2769 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2770 RETURNS "delegation_info_type"
2771 LANGUAGE 'plpgsql' STABLE AS $$
2772 DECLARE
2773 "current_row" "delegation_chain_row";
2774 "result" "delegation_info_type";
2775 BEGIN
2776 "result"."own_participation" := FALSE;
2777 FOR "current_row" IN
2778 SELECT * FROM "delegation_chain"(
2779 "member_id_p",
2780 "unit_id_p", "area_id_p", "issue_id_p",
2781 "simulate_trustee_id_p", "simulate_default_p")
2782 LOOP
2783 IF
2784 "result"."participating_member_id" ISNULL AND
2785 "current_row"."participation"
2786 THEN
2787 "result"."participating_member_id" := "current_row"."member_id";
2788 END IF;
2789 IF "current_row"."member_id" = "member_id_p" THEN
2790 "result"."own_participation" := "current_row"."participation";
2791 "result"."own_delegation_scope" := "current_row"."scope_out";
2792 IF "current_row"."loop" = 'first' THEN
2793 "result"."delegation_loop" := 'own';
2794 END IF;
2795 ELSIF
2796 "current_row"."member_valid" AND
2797 ( "current_row"."loop" ISNULL OR
2798 "current_row"."loop" != 'repetition' )
2799 THEN
2800 IF "result"."first_trustee_id" ISNULL THEN
2801 "result"."first_trustee_id" := "current_row"."member_id";
2802 "result"."first_trustee_participation" := "current_row"."participation";
2803 "result"."first_trustee_ellipsis" := FALSE;
2804 IF "current_row"."loop" = 'first' THEN
2805 "result"."delegation_loop" := 'first';
2806 END IF;
2807 ELSIF "result"."other_trustee_id" ISNULL THEN
2808 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
2809 "result"."other_trustee_id" := "current_row"."member_id";
2810 "result"."other_trustee_participation" := TRUE;
2811 "result"."other_trustee_ellipsis" := FALSE;
2812 IF "current_row"."loop" = 'first' THEN
2813 "result"."delegation_loop" := 'other';
2814 END IF;
2815 ELSE
2816 "result"."first_trustee_ellipsis" := TRUE;
2817 IF "current_row"."loop" = 'first' THEN
2818 "result"."delegation_loop" := 'first_ellipsis';
2819 END IF;
2820 END IF;
2821 ELSE
2822 "result"."other_trustee_ellipsis" := TRUE;
2823 IF "current_row"."loop" = 'first' THEN
2824 "result"."delegation_loop" := 'other_ellipsis';
2825 END IF;
2826 END IF;
2827 END IF;
2828 END LOOP;
2829 RETURN "result";
2830 END;
2831 $$;
2833 COMMENT ON FUNCTION "delegation_info"
2834 ( "member"."id"%TYPE,
2835 "unit"."id"%TYPE,
2836 "area"."id"%TYPE,
2837 "issue"."id"%TYPE,
2838 "member"."id"%TYPE,
2839 BOOLEAN )
2840 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
2844 ---------------------------
2845 -- Transaction isolation --
2846 ---------------------------
2849 CREATE FUNCTION "require_transaction_isolation"()
2850 RETURNS VOID
2851 LANGUAGE 'plpgsql' VOLATILE AS $$
2852 BEGIN
2853 IF
2854 current_setting('transaction_isolation') NOT IN
2855 ('repeatable read', 'serializable')
2856 THEN
2857 RAISE EXCEPTION 'Insufficient transaction isolation level';
2858 END IF;
2859 RETURN;
2860 END;
2861 $$;
2863 COMMENT ON FUNCTION "require_transaction_isolation"() IS 'Throws an exception, if transaction isolation level is too low to provide a consistent snapshot';
2866 CREATE FUNCTION "dont_require_transaction_isolation"()
2867 RETURNS VOID
2868 LANGUAGE 'plpgsql' VOLATILE AS $$
2869 BEGIN
2870 IF
2871 current_setting('transaction_isolation') IN
2872 ('repeatable read', 'serializable')
2873 THEN
2874 RAISE WARNING 'Unneccessary transaction isolation level: %',
2875 current_setting('transaction_isolation');
2876 END IF;
2877 RETURN;
2878 END;
2879 $$;
2881 COMMENT ON FUNCTION "dont_require_transaction_isolation"() IS 'Raises a warning, if transaction isolation level is higher than READ COMMITTED';
2885 ------------------------------------------------------------------------
2886 -- Regular tasks, except calculcation of snapshots and voting results --
2887 ------------------------------------------------------------------------
2890 CREATE FUNCTION "check_activity"()
2891 RETURNS VOID
2892 LANGUAGE 'plpgsql' VOLATILE AS $$
2893 DECLARE
2894 "system_setting_row" "system_setting"%ROWTYPE;
2895 BEGIN
2896 PERFORM "dont_require_transaction_isolation"();
2897 SELECT * INTO "system_setting_row" FROM "system_setting";
2898 IF "system_setting_row"."member_ttl" NOTNULL THEN
2899 UPDATE "member" SET "active" = FALSE
2900 WHERE "active" = TRUE
2901 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
2902 END IF;
2903 RETURN;
2904 END;
2905 $$;
2907 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
2910 CREATE FUNCTION "calculate_member_counts"()
2911 RETURNS VOID
2912 LANGUAGE 'plpgsql' VOLATILE AS $$
2913 BEGIN
2914 PERFORM "require_transaction_isolation"();
2915 DELETE FROM "member_count";
2916 INSERT INTO "member_count" ("total_count")
2917 SELECT "total_count" FROM "member_count_view";
2918 UPDATE "unit" SET "member_count" = "view"."member_count"
2919 FROM "unit_member_count" AS "view"
2920 WHERE "view"."unit_id" = "unit"."id";
2921 UPDATE "area" SET
2922 "direct_member_count" = "view"."direct_member_count",
2923 "member_weight" = "view"."member_weight"
2924 FROM "area_member_count" AS "view"
2925 WHERE "view"."area_id" = "area"."id";
2926 RETURN;
2927 END;
2928 $$;
2930 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"';
2934 ------------------------------------
2935 -- Calculation of harmonic weight --
2936 ------------------------------------
2939 CREATE VIEW "remaining_harmonic_supporter_weight" AS
2940 SELECT
2941 "direct_interest_snapshot"."issue_id",
2942 "direct_interest_snapshot"."event",
2943 "direct_interest_snapshot"."member_id",
2944 "direct_interest_snapshot"."weight" AS "weight_num",
2945 count("initiative"."id") AS "weight_den"
2946 FROM "issue"
2947 JOIN "direct_interest_snapshot"
2948 ON "issue"."id" = "direct_interest_snapshot"."issue_id"
2949 AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
2950 JOIN "initiative"
2951 ON "issue"."id" = "initiative"."issue_id"
2952 AND "initiative"."harmonic_weight" ISNULL
2953 JOIN "direct_supporter_snapshot"
2954 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
2955 AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event"
2956 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
2957 AND (
2958 "direct_supporter_snapshot"."satisfied" = TRUE OR
2959 coalesce("initiative"."admitted", FALSE) = FALSE
2960 )
2961 GROUP BY
2962 "direct_interest_snapshot"."issue_id",
2963 "direct_interest_snapshot"."event",
2964 "direct_interest_snapshot"."member_id",
2965 "direct_interest_snapshot"."weight";
2967 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
2970 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
2971 SELECT
2972 "initiative"."issue_id",
2973 "initiative"."id" AS "initiative_id",
2974 "initiative"."admitted",
2975 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
2976 "remaining_harmonic_supporter_weight"."weight_den"
2977 FROM "remaining_harmonic_supporter_weight"
2978 JOIN "initiative"
2979 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
2980 AND "initiative"."harmonic_weight" ISNULL
2981 JOIN "direct_supporter_snapshot"
2982 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
2983 AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"
2984 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
2985 AND (
2986 "direct_supporter_snapshot"."satisfied" = TRUE OR
2987 coalesce("initiative"."admitted", FALSE) = FALSE
2988 )
2989 GROUP BY
2990 "initiative"."issue_id",
2991 "initiative"."id",
2992 "initiative"."admitted",
2993 "remaining_harmonic_supporter_weight"."weight_den";
2995 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
2998 CREATE VIEW "remaining_harmonic_initiative_weight_dummies" AS
2999 SELECT
3000 "issue_id",
3001 "id" AS "initiative_id",
3002 "admitted",
3003 0 AS "weight_num",
3004 1 AS "weight_den"
3005 FROM "initiative"
3006 WHERE "harmonic_weight" ISNULL;
3008 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';
3011 CREATE FUNCTION "set_harmonic_initiative_weights"
3012 ( "issue_id_p" "issue"."id"%TYPE )
3013 RETURNS VOID
3014 LANGUAGE 'plpgsql' VOLATILE AS $$
3015 DECLARE
3016 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
3017 "i" INT4;
3018 "count_v" INT4;
3019 "summand_v" FLOAT;
3020 "id_ary" INT4[];
3021 "weight_ary" FLOAT[];
3022 "min_weight_v" FLOAT;
3023 BEGIN
3024 PERFORM "require_transaction_isolation"();
3025 UPDATE "initiative" SET "harmonic_weight" = NULL
3026 WHERE "issue_id" = "issue_id_p";
3027 LOOP
3028 "min_weight_v" := NULL;
3029 "i" := 0;
3030 "count_v" := 0;
3031 FOR "weight_row" IN
3032 SELECT * FROM "remaining_harmonic_initiative_weight_summands"
3033 WHERE "issue_id" = "issue_id_p"
3034 AND (
3035 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
3036 SELECT NULL FROM "initiative"
3037 WHERE "issue_id" = "issue_id_p"
3038 AND "harmonic_weight" ISNULL
3039 AND coalesce("admitted", FALSE) = FALSE
3040 )
3041 )
3042 UNION ALL -- needed for corner cases
3043 SELECT * FROM "remaining_harmonic_initiative_weight_dummies"
3044 WHERE "issue_id" = "issue_id_p"
3045 AND (
3046 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
3047 SELECT NULL FROM "initiative"
3048 WHERE "issue_id" = "issue_id_p"
3049 AND "harmonic_weight" ISNULL
3050 AND coalesce("admitted", FALSE) = FALSE
3051 )
3052 )
3053 ORDER BY "initiative_id" DESC, "weight_den" DESC
3054 -- NOTE: non-admitted initiatives placed first (at last positions),
3055 -- latest initiatives treated worse in case of tie
3056 LOOP
3057 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
3058 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
3059 "i" := "i" + 1;
3060 "count_v" := "i";
3061 "id_ary"["i"] := "weight_row"."initiative_id";
3062 "weight_ary"["i"] := "summand_v";
3063 ELSE
3064 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
3065 END IF;
3066 END LOOP;
3067 EXIT WHEN "count_v" = 0;
3068 "i" := 1;
3069 LOOP
3070 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
3071 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
3072 "min_weight_v" := "weight_ary"["i"];
3073 END IF;
3074 "i" := "i" + 1;
3075 EXIT WHEN "i" > "count_v";
3076 END LOOP;
3077 "i" := 1;
3078 LOOP
3079 IF "weight_ary"["i"] = "min_weight_v" THEN
3080 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
3081 WHERE "id" = "id_ary"["i"];
3082 EXIT;
3083 END IF;
3084 "i" := "i" + 1;
3085 END LOOP;
3086 END LOOP;
3087 UPDATE "initiative" SET "harmonic_weight" = 0
3088 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
3089 END;
3090 $$;
3092 COMMENT ON FUNCTION "set_harmonic_initiative_weights"
3093 ( "issue"."id"%TYPE )
3094 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
3098 ------------------------------
3099 -- Calculation of snapshots --
3100 ------------------------------
3103 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
3104 ( "issue_id_p" "issue"."id"%TYPE,
3105 "member_id_p" "member"."id"%TYPE,
3106 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3107 RETURNS "direct_population_snapshot"."weight"%TYPE
3108 LANGUAGE 'plpgsql' VOLATILE AS $$
3109 DECLARE
3110 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3111 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
3112 "weight_v" INT4;
3113 "sub_weight_v" INT4;
3114 BEGIN
3115 PERFORM "require_transaction_isolation"();
3116 "weight_v" := 0;
3117 FOR "issue_delegation_row" IN
3118 SELECT * FROM "issue_delegation"
3119 WHERE "trustee_id" = "member_id_p"
3120 AND "issue_id" = "issue_id_p"
3121 LOOP
3122 IF NOT EXISTS (
3123 SELECT NULL FROM "direct_population_snapshot"
3124 WHERE "issue_id" = "issue_id_p"
3125 AND "event" = 'periodic'
3126 AND "member_id" = "issue_delegation_row"."truster_id"
3127 ) AND NOT EXISTS (
3128 SELECT NULL FROM "delegating_population_snapshot"
3129 WHERE "issue_id" = "issue_id_p"
3130 AND "event" = 'periodic'
3131 AND "member_id" = "issue_delegation_row"."truster_id"
3132 ) THEN
3133 "delegate_member_ids_v" :=
3134 "member_id_p" || "delegate_member_ids_p";
3135 INSERT INTO "delegating_population_snapshot" (
3136 "issue_id",
3137 "event",
3138 "member_id",
3139 "scope",
3140 "delegate_member_ids"
3141 ) VALUES (
3142 "issue_id_p",
3143 'periodic',
3144 "issue_delegation_row"."truster_id",
3145 "issue_delegation_row"."scope",
3146 "delegate_member_ids_v"
3147 );
3148 "sub_weight_v" := 1 +
3149 "weight_of_added_delegations_for_population_snapshot"(
3150 "issue_id_p",
3151 "issue_delegation_row"."truster_id",
3152 "delegate_member_ids_v"
3153 );
3154 UPDATE "delegating_population_snapshot"
3155 SET "weight" = "sub_weight_v"
3156 WHERE "issue_id" = "issue_id_p"
3157 AND "event" = 'periodic'
3158 AND "member_id" = "issue_delegation_row"."truster_id";
3159 "weight_v" := "weight_v" + "sub_weight_v";
3160 END IF;
3161 END LOOP;
3162 RETURN "weight_v";
3163 END;
3164 $$;
3166 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
3167 ( "issue"."id"%TYPE,
3168 "member"."id"%TYPE,
3169 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3170 IS 'Helper function for "create_population_snapshot" function';
3173 CREATE FUNCTION "create_population_snapshot"
3174 ( "issue_id_p" "issue"."id"%TYPE )
3175 RETURNS VOID
3176 LANGUAGE 'plpgsql' VOLATILE AS $$
3177 DECLARE
3178 "member_id_v" "member"."id"%TYPE;
3179 BEGIN
3180 PERFORM "require_transaction_isolation"();
3181 DELETE FROM "direct_population_snapshot"
3182 WHERE "issue_id" = "issue_id_p"
3183 AND "event" = 'periodic';
3184 DELETE FROM "delegating_population_snapshot"
3185 WHERE "issue_id" = "issue_id_p"
3186 AND "event" = 'periodic';
3187 INSERT INTO "direct_population_snapshot"
3188 ("issue_id", "event", "member_id")
3189 SELECT
3190 "issue_id_p" AS "issue_id",
3191 'periodic'::"snapshot_event" AS "event",
3192 "member"."id" AS "member_id"
3193 FROM "issue"
3194 JOIN "area" ON "issue"."area_id" = "area"."id"
3195 JOIN "membership" ON "area"."id" = "membership"."area_id"
3196 JOIN "member" ON "membership"."member_id" = "member"."id"
3197 JOIN "privilege"
3198 ON "privilege"."unit_id" = "area"."unit_id"
3199 AND "privilege"."member_id" = "member"."id"
3200 WHERE "issue"."id" = "issue_id_p"
3201 AND "member"."active" AND "privilege"."voting_right"
3202 UNION
3203 SELECT
3204 "issue_id_p" AS "issue_id",
3205 'periodic'::"snapshot_event" AS "event",
3206 "member"."id" AS "member_id"
3207 FROM "issue"
3208 JOIN "area" ON "issue"."area_id" = "area"."id"
3209 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3210 JOIN "member" ON "interest"."member_id" = "member"."id"
3211 JOIN "privilege"
3212 ON "privilege"."unit_id" = "area"."unit_id"
3213 AND "privilege"."member_id" = "member"."id"
3214 WHERE "issue"."id" = "issue_id_p"
3215 AND "member"."active" AND "privilege"."voting_right";
3216 FOR "member_id_v" IN
3217 SELECT "member_id" FROM "direct_population_snapshot"
3218 WHERE "issue_id" = "issue_id_p"
3219 AND "event" = 'periodic'
3220 LOOP
3221 UPDATE "direct_population_snapshot" SET
3222 "weight" = 1 +
3223 "weight_of_added_delegations_for_population_snapshot"(
3224 "issue_id_p",
3225 "member_id_v",
3226 '{}'
3227 )
3228 WHERE "issue_id" = "issue_id_p"
3229 AND "event" = 'periodic'
3230 AND "member_id" = "member_id_v";
3231 END LOOP;
3232 RETURN;
3233 END;
3234 $$;
3236 COMMENT ON FUNCTION "create_population_snapshot"
3237 ( "issue"."id"%TYPE )
3238 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.';
3241 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3242 ( "issue_id_p" "issue"."id"%TYPE,
3243 "member_id_p" "member"."id"%TYPE,
3244 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3245 RETURNS "direct_interest_snapshot"."weight"%TYPE
3246 LANGUAGE 'plpgsql' VOLATILE AS $$
3247 DECLARE
3248 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3249 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
3250 "weight_v" INT4;
3251 "sub_weight_v" INT4;
3252 BEGIN
3253 PERFORM "require_transaction_isolation"();
3254 "weight_v" := 0;
3255 FOR "issue_delegation_row" IN
3256 SELECT * FROM "issue_delegation"
3257 WHERE "trustee_id" = "member_id_p"
3258 AND "issue_id" = "issue_id_p"
3259 LOOP
3260 IF NOT EXISTS (
3261 SELECT NULL FROM "direct_interest_snapshot"
3262 WHERE "issue_id" = "issue_id_p"
3263 AND "event" = 'periodic'
3264 AND "member_id" = "issue_delegation_row"."truster_id"
3265 ) AND NOT EXISTS (
3266 SELECT NULL FROM "delegating_interest_snapshot"
3267 WHERE "issue_id" = "issue_id_p"
3268 AND "event" = 'periodic'
3269 AND "member_id" = "issue_delegation_row"."truster_id"
3270 ) THEN
3271 "delegate_member_ids_v" :=
3272 "member_id_p" || "delegate_member_ids_p";
3273 INSERT INTO "delegating_interest_snapshot" (
3274 "issue_id",
3275 "event",
3276 "member_id",
3277 "scope",
3278 "delegate_member_ids"
3279 ) VALUES (
3280 "issue_id_p",
3281 'periodic',
3282 "issue_delegation_row"."truster_id",
3283 "issue_delegation_row"."scope",
3284 "delegate_member_ids_v"
3285 );
3286 "sub_weight_v" := 1 +
3287 "weight_of_added_delegations_for_interest_snapshot"(
3288 "issue_id_p",
3289 "issue_delegation_row"."truster_id",
3290 "delegate_member_ids_v"
3291 );
3292 UPDATE "delegating_interest_snapshot"
3293 SET "weight" = "sub_weight_v"
3294 WHERE "issue_id" = "issue_id_p"
3295 AND "event" = 'periodic'
3296 AND "member_id" = "issue_delegation_row"."truster_id";
3297 "weight_v" := "weight_v" + "sub_weight_v";
3298 END IF;
3299 END LOOP;
3300 RETURN "weight_v";
3301 END;
3302 $$;
3304 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3305 ( "issue"."id"%TYPE,
3306 "member"."id"%TYPE,
3307 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3308 IS 'Helper function for "create_interest_snapshot" function';
3311 CREATE FUNCTION "create_interest_snapshot"
3312 ( "issue_id_p" "issue"."id"%TYPE )
3313 RETURNS VOID
3314 LANGUAGE 'plpgsql' VOLATILE AS $$
3315 DECLARE
3316 "member_id_v" "member"."id"%TYPE;
3317 BEGIN
3318 PERFORM "require_transaction_isolation"();
3319 DELETE FROM "direct_interest_snapshot"
3320 WHERE "issue_id" = "issue_id_p"
3321 AND "event" = 'periodic';
3322 DELETE FROM "delegating_interest_snapshot"
3323 WHERE "issue_id" = "issue_id_p"
3324 AND "event" = 'periodic';
3325 DELETE FROM "direct_supporter_snapshot"
3326 USING "initiative" -- NOTE: due to missing index on issue_id
3327 WHERE "initiative"."issue_id" = "issue_id_p"
3328 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
3329 AND "direct_supporter_snapshot"."event" = 'periodic';
3330 INSERT INTO "direct_interest_snapshot"
3331 ("issue_id", "event", "member_id")
3332 SELECT
3333 "issue_id_p" AS "issue_id",
3334 'periodic' AS "event",
3335 "member"."id" AS "member_id"
3336 FROM "issue"
3337 JOIN "area" ON "issue"."area_id" = "area"."id"
3338 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3339 JOIN "member" ON "interest"."member_id" = "member"."id"
3340 JOIN "privilege"
3341 ON "privilege"."unit_id" = "area"."unit_id"
3342 AND "privilege"."member_id" = "member"."id"
3343 WHERE "issue"."id" = "issue_id_p"
3344 AND "member"."active" AND "privilege"."voting_right";
3345 FOR "member_id_v" IN
3346 SELECT "member_id" FROM "direct_interest_snapshot"
3347 WHERE "issue_id" = "issue_id_p"
3348 AND "event" = 'periodic'
3349 LOOP
3350 UPDATE "direct_interest_snapshot" SET
3351 "weight" = 1 +
3352 "weight_of_added_delegations_for_interest_snapshot"(
3353 "issue_id_p",
3354 "member_id_v",
3355 '{}'
3356 )
3357 WHERE "issue_id" = "issue_id_p"
3358 AND "event" = 'periodic'
3359 AND "member_id" = "member_id_v";
3360 END LOOP;
3361 INSERT INTO "direct_supporter_snapshot"
3362 ( "issue_id", "initiative_id", "event", "member_id",
3363 "draft_id", "informed", "satisfied" )
3364 SELECT
3365 "issue_id_p" AS "issue_id",
3366 "initiative"."id" AS "initiative_id",
3367 'periodic' AS "event",
3368 "supporter"."member_id" AS "member_id",
3369 "supporter"."draft_id" AS "draft_id",
3370 "supporter"."draft_id" = "current_draft"."id" AS "informed",
3371 NOT EXISTS (
3372 SELECT NULL FROM "critical_opinion"
3373 WHERE "initiative_id" = "initiative"."id"
3374 AND "member_id" = "supporter"."member_id"
3375 ) AS "satisfied"
3376 FROM "initiative"
3377 JOIN "supporter"
3378 ON "supporter"."initiative_id" = "initiative"."id"
3379 JOIN "current_draft"
3380 ON "initiative"."id" = "current_draft"."initiative_id"
3381 JOIN "direct_interest_snapshot"
3382 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
3383 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
3384 AND "event" = 'periodic'
3385 WHERE "initiative"."issue_id" = "issue_id_p";
3386 RETURN;
3387 END;
3388 $$;
3390 COMMENT ON FUNCTION "create_interest_snapshot"
3391 ( "issue"."id"%TYPE )
3392 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.';
3395 CREATE FUNCTION "create_snapshot"
3396 ( "issue_id_p" "issue"."id"%TYPE )
3397 RETURNS VOID
3398 LANGUAGE 'plpgsql' VOLATILE AS $$
3399 DECLARE
3400 "initiative_id_v" "initiative"."id"%TYPE;
3401 "suggestion_id_v" "suggestion"."id"%TYPE;
3402 BEGIN
3403 PERFORM "require_transaction_isolation"();
3404 PERFORM "create_population_snapshot"("issue_id_p");
3405 PERFORM "create_interest_snapshot"("issue_id_p");
3406 UPDATE "issue" SET
3407 "snapshot" = coalesce("phase_finished", now()),
3408 "latest_snapshot_event" = 'periodic',
3409 "population" = (
3410 SELECT coalesce(sum("weight"), 0)
3411 FROM "direct_population_snapshot"
3412 WHERE "issue_id" = "issue_id_p"
3413 AND "event" = 'periodic'
3414 )
3415 WHERE "id" = "issue_id_p";
3416 FOR "initiative_id_v" IN
3417 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
3418 LOOP
3419 UPDATE "initiative" SET
3420 "supporter_count" = (
3421 SELECT coalesce(sum("di"."weight"), 0)
3422 FROM "direct_interest_snapshot" AS "di"
3423 JOIN "direct_supporter_snapshot" AS "ds"
3424 ON "di"."member_id" = "ds"."member_id"
3425 WHERE "di"."issue_id" = "issue_id_p"
3426 AND "di"."event" = 'periodic'
3427 AND "ds"."initiative_id" = "initiative_id_v"
3428 AND "ds"."event" = 'periodic'
3429 ),
3430 "informed_supporter_count" = (
3431 SELECT coalesce(sum("di"."weight"), 0)
3432 FROM "direct_interest_snapshot" AS "di"
3433 JOIN "direct_supporter_snapshot" AS "ds"
3434 ON "di"."member_id" = "ds"."member_id"
3435 WHERE "di"."issue_id" = "issue_id_p"
3436 AND "di"."event" = 'periodic'
3437 AND "ds"."initiative_id" = "initiative_id_v"
3438 AND "ds"."event" = 'periodic'
3439 AND "ds"."informed"
3440 ),
3441 "satisfied_supporter_count" = (
3442 SELECT coalesce(sum("di"."weight"), 0)
3443 FROM "direct_interest_snapshot" AS "di"
3444 JOIN "direct_supporter_snapshot" AS "ds"
3445 ON "di"."member_id" = "ds"."member_id"
3446 WHERE "di"."issue_id" = "issue_id_p"
3447 AND "di"."event" = 'periodic'
3448 AND "ds"."initiative_id" = "initiative_id_v"
3449 AND "ds"."event" = 'periodic'
3450 AND "ds"."satisfied"
3451 ),
3452 "satisfied_informed_supporter_count" = (
3453 SELECT coalesce(sum("di"."weight"), 0)
3454 FROM "direct_interest_snapshot" AS "di"
3455 JOIN "direct_supporter_snapshot" AS "ds"
3456 ON "di"."member_id" = "ds"."member_id"
3457 WHERE "di"."issue_id" = "issue_id_p"
3458 AND "di"."event" = 'periodic'
3459 AND "ds"."initiative_id" = "initiative_id_v"
3460 AND "ds"."event" = 'periodic'
3461 AND "ds"."informed"
3462 AND "ds"."satisfied"
3463 )
3464 WHERE "id" = "initiative_id_v";
3465 FOR "suggestion_id_v" IN
3466 SELECT "id" FROM "suggestion"
3467 WHERE "initiative_id" = "initiative_id_v"
3468 LOOP
3469 UPDATE "suggestion" SET
3470 "minus2_unfulfilled_count" = (
3471 SELECT coalesce(sum("snapshot"."weight"), 0)
3472 FROM "issue" CROSS JOIN "opinion"
3473 JOIN "direct_interest_snapshot" AS "snapshot"
3474 ON "snapshot"."issue_id" = "issue"."id"
3475 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3476 AND "snapshot"."member_id" = "opinion"."member_id"
3477 WHERE "issue"."id" = "issue_id_p"
3478 AND "opinion"."suggestion_id" = "suggestion_id_v"
3479 AND "opinion"."degree" = -2
3480 AND "opinion"."fulfilled" = FALSE
3481 ),
3482 "minus2_fulfilled_count" = (
3483 SELECT coalesce(sum("snapshot"."weight"), 0)
3484 FROM "issue" CROSS JOIN "opinion"
3485 JOIN "direct_interest_snapshot" AS "snapshot"
3486 ON "snapshot"."issue_id" = "issue"."id"
3487 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3488 AND "snapshot"."member_id" = "opinion"."member_id"
3489 WHERE "issue"."id" = "issue_id_p"
3490 AND "opinion"."suggestion_id" = "suggestion_id_v"
3491 AND "opinion"."degree" = -2
3492 AND "opinion"."fulfilled" = TRUE
3493 ),
3494 "minus1_unfulfilled_count" = (
3495 SELECT coalesce(sum("snapshot"."weight"), 0)
3496 FROM "issue" CROSS JOIN "opinion"
3497 JOIN "direct_interest_snapshot" AS "snapshot"
3498 ON "snapshot"."issue_id" = "issue"."id"
3499 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3500 AND "snapshot"."member_id" = "opinion"."member_id"
3501 WHERE "issue"."id" = "issue_id_p"
3502 AND "opinion"."suggestion_id" = "suggestion_id_v"
3503 AND "opinion"."degree" = -1
3504 AND "opinion"."fulfilled" = FALSE
3505 ),
3506 "minus1_fulfilled_count" = (
3507 SELECT coalesce(sum("snapshot"."weight"), 0)
3508 FROM "issue" CROSS JOIN "opinion"
3509 JOIN "direct_interest_snapshot" AS "snapshot"
3510 ON "snapshot"."issue_id" = "issue"."id"
3511 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3512 AND "snapshot"."member_id" = "opinion"."member_id"
3513 WHERE "issue"."id" = "issue_id_p"
3514 AND "opinion"."suggestion_id" = "suggestion_id_v"
3515 AND "opinion"."degree" = -1
3516 AND "opinion"."fulfilled" = TRUE
3517 ),
3518 "plus1_unfulfilled_count" = (
3519 SELECT coalesce(sum("snapshot"."weight"), 0)
3520 FROM "issue" CROSS JOIN "opinion"
3521 JOIN "direct_interest_snapshot" AS "snapshot"
3522 ON "snapshot"."issue_id" = "issue"."id"
3523 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3524 AND "snapshot"."member_id" = "opinion"."member_id"
3525 WHERE "issue"."id" = "issue_id_p"
3526 AND "opinion"."suggestion_id" = "suggestion_id_v"
3527 AND "opinion"."degree" = 1
3528 AND "opinion"."fulfilled" = FALSE
3529 ),
3530 "plus1_fulfilled_count" = (
3531 SELECT coalesce(sum("snapshot"."weight"), 0)
3532 FROM "issue" CROSS JOIN "opinion"
3533 JOIN "direct_interest_snapshot" AS "snapshot"
3534 ON "snapshot"."issue_id" = "issue"."id"
3535 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3536 AND "snapshot"."member_id" = "opinion"."member_id"
3537 WHERE "issue"."id" = "issue_id_p"
3538 AND "opinion"."suggestion_id" = "suggestion_id_v"
3539 AND "opinion"."degree" = 1
3540 AND "opinion"."fulfilled" = TRUE
3541 ),
3542 "plus2_unfulfilled_count" = (
3543 SELECT coalesce(sum("snapshot"."weight"), 0)
3544 FROM "issue" CROSS JOIN "opinion"
3545 JOIN "direct_interest_snapshot" AS "snapshot"
3546 ON "snapshot"."issue_id" = "issue"."id"
3547 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3548 AND "snapshot"."member_id" = "opinion"."member_id"
3549 WHERE "issue"."id" = "issue_id_p"
3550 AND "opinion"."suggestion_id" = "suggestion_id_v"
3551 AND "opinion"."degree" = 2
3552 AND "opinion"."fulfilled" = FALSE
3553 ),
3554 "plus2_fulfilled_count" = (
3555 SELECT coalesce(sum("snapshot"."weight"), 0)
3556 FROM "issue" CROSS JOIN "opinion"
3557 JOIN "direct_interest_snapshot" AS "snapshot"
3558 ON "snapshot"."issue_id" = "issue"."id"
3559 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3560 AND "snapshot"."member_id" = "opinion"."member_id"
3561 WHERE "issue"."id" = "issue_id_p"
3562 AND "opinion"."suggestion_id" = "suggestion_id_v"
3563 AND "opinion"."degree" = 2
3564 AND "opinion"."fulfilled" = TRUE
3565 )
3566 WHERE "suggestion"."id" = "suggestion_id_v";
3567 END LOOP;
3568 END LOOP;
3569 RETURN;
3570 END;
3571 $$;
3573 COMMENT ON FUNCTION "create_snapshot"
3574 ( "issue"."id"%TYPE )
3575 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.';
3578 CREATE FUNCTION "set_snapshot_event"
3579 ( "issue_id_p" "issue"."id"%TYPE,
3580 "event_p" "snapshot_event" )
3581 RETURNS VOID
3582 LANGUAGE 'plpgsql' VOLATILE AS $$
3583 DECLARE
3584 "event_v" "issue"."latest_snapshot_event"%TYPE;
3585 BEGIN
3586 PERFORM "require_transaction_isolation"();
3587 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
3588 WHERE "id" = "issue_id_p" FOR UPDATE;
3589 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
3590 WHERE "id" = "issue_id_p";
3591 UPDATE "direct_population_snapshot" SET "event" = "event_p"
3592 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3593 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
3594 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3595 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
3596 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3597 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
3598 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3599 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
3600 FROM "initiative" -- NOTE: due to missing index on issue_id
3601 WHERE "initiative"."issue_id" = "issue_id_p"
3602 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
3603 AND "direct_supporter_snapshot"."event" = "event_v";
3604 RETURN;
3605 END;
3606 $$;
3608 COMMENT ON FUNCTION "set_snapshot_event"
3609 ( "issue"."id"%TYPE,
3610 "snapshot_event" )
3611 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
3615 -----------------------
3616 -- Counting of votes --
3617 -----------------------
3620 CREATE FUNCTION "weight_of_added_vote_delegations"
3621 ( "issue_id_p" "issue"."id"%TYPE,
3622 "member_id_p" "member"."id"%TYPE,
3623 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
3624 RETURNS "direct_voter"."weight"%TYPE
3625 LANGUAGE 'plpgsql' VOLATILE AS $$
3626 DECLARE
3627 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3628 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
3629 "weight_v" INT4;
3630 "sub_weight_v" INT4;
3631 BEGIN
3632 PERFORM "require_transaction_isolation"();
3633 "weight_v" := 0;
3634 FOR "issue_delegation_row" IN
3635 SELECT * FROM "issue_delegation"
3636 WHERE "trustee_id" = "member_id_p"
3637 AND "issue_id" = "issue_id_p"
3638 LOOP
3639 IF NOT EXISTS (
3640 SELECT NULL FROM "direct_voter"
3641 WHERE "member_id" = "issue_delegation_row"."truster_id"
3642 AND "issue_id" = "issue_id_p"
3643 ) AND NOT EXISTS (
3644 SELECT NULL FROM "delegating_voter"
3645 WHERE "member_id" = "issue_delegation_row"."truster_id"
3646 AND "issue_id" = "issue_id_p"
3647 ) THEN
3648 "delegate_member_ids_v" :=
3649 "member_id_p" || "delegate_member_ids_p";
3650 INSERT INTO "delegating_voter" (
3651 "issue_id",
3652 "member_id",
3653 "scope",
3654 "delegate_member_ids"
3655 ) VALUES (
3656 "issue_id_p",
3657 "issue_delegation_row"."truster_id",
3658 "issue_delegation_row"."scope",
3659 "delegate_member_ids_v"
3660 );
3661 "sub_weight_v" := 1 +
3662 "weight_of_added_vote_delegations"(
3663 "issue_id_p",
3664 "issue_delegation_row"."truster_id",
3665 "delegate_member_ids_v"
3666 );
3667 UPDATE "delegating_voter"
3668 SET "weight" = "sub_weight_v"
3669 WHERE "issue_id" = "issue_id_p"
3670 AND "member_id" = "issue_delegation_row"."truster_id";
3671 "weight_v" := "weight_v" + "sub_weight_v";
3672 END IF;
3673 END LOOP;
3674 RETURN "weight_v";
3675 END;
3676 $$;
3678 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
3679 ( "issue"."id"%TYPE,
3680 "member"."id"%TYPE,
3681 "delegating_voter"."delegate_member_ids"%TYPE )
3682 IS 'Helper function for "add_vote_delegations" function';
3685 CREATE FUNCTION "add_vote_delegations"
3686 ( "issue_id_p" "issue"."id"%TYPE )
3687 RETURNS VOID
3688 LANGUAGE 'plpgsql' VOLATILE AS $$
3689 DECLARE
3690 "member_id_v" "member"."id"%TYPE;
3691 BEGIN
3692 PERFORM "require_transaction_isolation"();
3693 FOR "member_id_v" IN
3694 SELECT "member_id" FROM "direct_voter"
3695 WHERE "issue_id" = "issue_id_p"
3696 LOOP
3697 UPDATE "direct_voter" SET
3698 "weight" = "weight" + "weight_of_added_vote_delegations"(
3699 "issue_id_p",
3700 "member_id_v",
3701 '{}'
3702 )
3703 WHERE "member_id" = "member_id_v"
3704 AND "issue_id" = "issue_id_p";
3705 END LOOP;
3706 RETURN;
3707 END;
3708 $$;
3710 COMMENT ON FUNCTION "add_vote_delegations"
3711 ( "issue_id_p" "issue"."id"%TYPE )
3712 IS 'Helper function for "close_voting" function';
3715 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
3716 RETURNS VOID
3717 LANGUAGE 'plpgsql' VOLATILE AS $$
3718 DECLARE
3719 "area_id_v" "area"."id"%TYPE;
3720 "unit_id_v" "unit"."id"%TYPE;
3721 "member_id_v" "member"."id"%TYPE;
3722 BEGIN
3723 PERFORM "require_transaction_isolation"();
3724 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
3725 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
3726 -- override protection triggers:
3727 INSERT INTO "temporary_transaction_data" ("key", "value")
3728 VALUES ('override_protection_triggers', TRUE::TEXT);
3729 -- delete timestamp of voting comment:
3730 UPDATE "direct_voter" SET "comment_changed" = NULL
3731 WHERE "issue_id" = "issue_id_p";
3732 -- delete delegating votes (in cases of manual reset of issue state):
3733 DELETE FROM "delegating_voter"
3734 WHERE "issue_id" = "issue_id_p";
3735 -- delete votes from non-privileged voters:
3736 DELETE FROM "direct_voter"
3737 USING (
3738 SELECT
3739 "direct_voter"."member_id"
3740 FROM "direct_voter"
3741 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
3742 LEFT JOIN "privilege"
3743 ON "privilege"."unit_id" = "unit_id_v"
3744 AND "privilege"."member_id" = "direct_voter"."member_id"
3745 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
3746 "member"."active" = FALSE OR
3747 "privilege"."voting_right" ISNULL OR
3748 "privilege"."voting_right" = FALSE
3749 )
3750 ) AS "subquery"
3751 WHERE "direct_voter"."issue_id" = "issue_id_p"
3752 AND "direct_voter"."member_id" = "subquery"."member_id";
3753 -- consider delegations:
3754 UPDATE "direct_voter" SET "weight" = 1
3755 WHERE "issue_id" = "issue_id_p";
3756 PERFORM "add_vote_delegations"("issue_id_p");
3757 -- mark first preferences:
3758 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
3759 FROM (
3760 SELECT
3761 "vote"."initiative_id",
3762 "vote"."member_id",
3763 CASE WHEN "vote"."grade" > 0 THEN
3764 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
3765 ELSE NULL
3766 END AS "first_preference"
3767 FROM "vote"
3768 JOIN "initiative" -- NOTE: due to missing index on issue_id
3769 ON "vote"."issue_id" = "initiative"."issue_id"
3770 JOIN "vote" AS "agg"
3771 ON "initiative"."id" = "agg"."initiative_id"
3772 AND "vote"."member_id" = "agg"."member_id"
3773 GROUP BY "vote"."initiative_id", "vote"."member_id"
3774 ) AS "subquery"
3775 WHERE "vote"."issue_id" = "issue_id_p"
3776 AND "vote"."initiative_id" = "subquery"."initiative_id"
3777 AND "vote"."member_id" = "subquery"."member_id";
3778 -- finish overriding protection triggers (avoids garbage):
3779 DELETE FROM "temporary_transaction_data"
3780 WHERE "key" = 'override_protection_triggers';
3781 -- materialize battle_view:
3782 -- NOTE: "closed" column of issue must be set at this point
3783 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3784 INSERT INTO "battle" (
3785 "issue_id",
3786 "winning_initiative_id", "losing_initiative_id",
3787 "count"
3788 ) SELECT
3789 "issue_id",
3790 "winning_initiative_id", "losing_initiative_id",
3791 "count"
3792 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
3793 -- set voter count:
3794 UPDATE "issue" SET
3795 "voter_count" = (
3796 SELECT coalesce(sum("weight"), 0)
3797 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
3798 )
3799 WHERE "id" = "issue_id_p";
3800 -- calculate "first_preference_votes":
3801 UPDATE "initiative"
3802 SET "first_preference_votes" = coalesce("subquery"."sum", 0)
3803 FROM (
3804 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
3805 FROM "vote" JOIN "direct_voter"
3806 ON "vote"."issue_id" = "direct_voter"."issue_id"
3807 AND "vote"."member_id" = "direct_voter"."member_id"
3808 WHERE "vote"."first_preference"
3809 GROUP BY "vote"."initiative_id"
3810 ) AS "subquery"
3811 WHERE "initiative"."issue_id" = "issue_id_p"
3812 AND "initiative"."admitted"
3813 AND "initiative"."id" = "subquery"."initiative_id";
3814 -- copy "positive_votes" and "negative_votes" from "battle" table:
3815 UPDATE "initiative" SET
3816 "positive_votes" = "battle_win"."count",
3817 "negative_votes" = "battle_lose"."count"
3818 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
3819 WHERE
3820 "battle_win"."issue_id" = "issue_id_p" AND
3821 "battle_win"."winning_initiative_id" = "initiative"."id" AND
3822 "battle_win"."losing_initiative_id" ISNULL AND
3823 "battle_lose"."issue_id" = "issue_id_p" AND
3824 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
3825 "battle_lose"."winning_initiative_id" ISNULL;
3826 END;
3827 $$;
3829 COMMENT ON FUNCTION "close_voting"
3830 ( "issue"."id"%TYPE )
3831 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.';
3834 CREATE FUNCTION "defeat_strength"
3835 ( "positive_votes_p" INT4,
3836 "negative_votes_p" INT4,
3837 "defeat_strength_p" "defeat_strength" )
3838 RETURNS INT8
3839 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3840 BEGIN
3841 IF "defeat_strength_p" = 'simple'::"defeat_strength" THEN
3842 IF "positive_votes_p" > "negative_votes_p" THEN
3843 RETURN "positive_votes_p";
3844 ELSE
3845 RETURN 0;
3846 END IF;
3847 ELSE
3848 IF "positive_votes_p" > "negative_votes_p" THEN
3849 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
3850 ELSIF "positive_votes_p" = "negative_votes_p" THEN
3851 RETURN 0;
3852 ELSE
3853 RETURN -1;
3854 END IF;
3855 END IF;
3856 END;
3857 $$;
3859 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")';
3862 CREATE FUNCTION "secondary_link_strength"
3863 ( "initiative1_ord_p" INT4,
3864 "initiative2_ord_p" INT4,
3865 "tie_breaking_p" "tie_breaking" )
3866 RETURNS INT8
3867 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3868 BEGIN
3869 IF "initiative1_ord_p" = "initiative2_ord_p" THEN
3870 RAISE EXCEPTION 'Identical initiative ids passed to "secondary_link_strength" function (should not happen)';
3871 END IF;
3872 RETURN (
3873 CASE WHEN "tie_breaking_p" = 'simple'::"tie_breaking" THEN
3874 0
3875 ELSE
3876 CASE WHEN "initiative1_ord_p" < "initiative2_ord_p" THEN
3877 1::INT8 << 62
3878 ELSE 0 END
3879 +
3880 CASE WHEN "tie_breaking_p" = 'variant2'::"tie_breaking" THEN
3881 ("initiative2_ord_p"::INT8 << 31) - "initiative1_ord_p"::INT8
3882 ELSE
3883 "initiative2_ord_p"::INT8 - ("initiative1_ord_p"::INT8 << 31)
3884 END
3885 END
3886 );
3887 END;
3888 $$;
3890 COMMENT ON FUNCTION "secondary_link_strength"(INT4, INT4, "tie_breaking") IS 'Calculates a secondary criterion for the defeat strength (tie-breaking of the links)';
3893 CREATE TYPE "link_strength" AS (
3894 "primary" INT8,
3895 "secondary" INT8 );
3899 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
3900 RETURNS VOID
3901 LANGUAGE 'plpgsql' VOLATILE AS $$
3902 DECLARE
3903 "issue_row" "issue"%ROWTYPE;
3904 "policy_row" "policy"%ROWTYPE;
3905 "dimension_v" INT4;
3906 "vote_matrix" INT4[][]; -- absolute votes
3907 "matrix" "link_strength"[][]; -- defeat strength / best paths
3908 "i" INT4;
3909 "j" INT4;
3910 "k" INT4;
3911 "battle_row" "battle"%ROWTYPE;
3912 "rank_ary" INT4[];
3913 "rank_v" INT4;
3914 "initiative_id_v" "initiative"."id"%TYPE;
3915 BEGIN
3916 PERFORM "require_transaction_isolation"();
3917 SELECT * INTO "issue_row"
3918 FROM "issue" WHERE "id" = "issue_id_p";
3919 SELECT * INTO "policy_row"
3920 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3921 SELECT count(1) INTO "dimension_v"
3922 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
3923 -- Create "vote_matrix" with absolute number of votes in pairwise
3924 -- comparison:
3925 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
3926 "i" := 1;
3927 "j" := 2;
3928 FOR "battle_row" IN
3929 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
3930 ORDER BY
3931 "winning_initiative_id" NULLS FIRST,
3932 "losing_initiative_id" NULLS FIRST
3933 LOOP
3934 "vote_matrix"["i"]["j"] := "battle_row"."count";
3935 IF "j" = "dimension_v" THEN
3936 "i" := "i" + 1;
3937 "j" := 1;
3938 ELSE
3939 "j" := "j" + 1;
3940 IF "j" = "i" THEN
3941 "j" := "j" + 1;
3942 END IF;
3943 END IF;
3944 END LOOP;
3945 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
3946 RAISE EXCEPTION 'Wrong battle count (should not happen)';
3947 END IF;
3948 -- Store defeat strengths in "matrix" using "defeat_strength"
3949 -- function:
3950 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
3951 "i" := 1;
3952 LOOP
3953 "j" := 1;
3954 LOOP
3955 IF "i" != "j" THEN
3956 "matrix"["i"]["j"] := (
3957 "defeat_strength"(
3958 "vote_matrix"["i"]["j"],
3959 "vote_matrix"["j"]["i"],
3960 "policy_row"."defeat_strength"
3961 ),
3962 "secondary_link_strength"(
3963 "i",
3964 "j",
3965 "policy_row"."tie_breaking"
3966 )
3967 )::"link_strength";
3968 END IF;
3969 EXIT WHEN "j" = "dimension_v";
3970 "j" := "j" + 1;
3971 END LOOP;
3972 EXIT WHEN "i" = "dimension_v";
3973 "i" := "i" + 1;
3974 END LOOP;
3975 -- Find best paths:
3976 "i" := 1;
3977 LOOP
3978 "j" := 1;
3979 LOOP
3980 IF "i" != "j" THEN
3981 "k" := 1;
3982 LOOP
3983 IF "i" != "k" AND "j" != "k" THEN
3984 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
3985 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
3986 "matrix"["j"]["k"] := "matrix"["j"]["i"];
3987 END IF;
3988 ELSE
3989 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
3990 "matrix"["j"]["k"] := "matrix"["i"]["k"];
3991 END IF;
3992 END IF;
3993 END IF;
3994 EXIT WHEN "k" = "dimension_v";
3995 "k" := "k" + 1;
3996 END LOOP;
3997 END IF;
3998 EXIT WHEN "j" = "dimension_v";
3999 "j" := "j" + 1;
4000 END LOOP;
4001 EXIT WHEN "i" = "dimension_v";
4002 "i" := "i" + 1;
4003 END LOOP;
4004 -- Determine order of winners:
4005 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
4006 "rank_v" := 1;
4007 LOOP
4008 "i" := 1;
4009 LOOP
4010 IF "rank_ary"["i"] ISNULL THEN
4011 "j" := 1;
4012 LOOP
4013 IF
4014 "i" != "j" AND
4015 "rank_ary"["j"] ISNULL AND
4016 ( "matrix"["j"]["i"] > "matrix"["i"]["j"] OR
4017 -- tie-breaking by "id"
4018 ( "matrix"["j"]["i"] = "matrix"["i"]["j"] AND
4019 "j" < "i" ) )
4020 THEN
4021 -- someone else is better
4022 EXIT;
4023 END IF;
4024 "j" := "j" + 1;
4025 IF "j" = "dimension_v" + 1 THEN
4026 -- noone is better
4027 "rank_ary"["i"] := "rank_v";
4028 EXIT;
4029 END IF;
4030 END LOOP;
4031 EXIT WHEN "j" = "dimension_v" + 1;
4032 END IF;
4033 "i" := "i" + 1;
4034 IF "i" > "dimension_v" THEN
4035 RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
4036 END IF;
4037 END LOOP;
4038 EXIT WHEN "rank_v" = "dimension_v";
4039 "rank_v" := "rank_v" + 1;
4040 END LOOP;
4041 -- write preliminary results:
4042 "i" := 2; -- omit status quo with "i" = 1
4043 FOR "initiative_id_v" IN
4044 SELECT "id" FROM "initiative"
4045 WHERE "issue_id" = "issue_id_p" AND "admitted"
4046 ORDER BY "id"
4047 LOOP
4048 UPDATE "initiative" SET
4049 "direct_majority" =
4050 CASE WHEN "policy_row"."direct_majority_strict" THEN
4051 "positive_votes" * "policy_row"."direct_majority_den" >
4052 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
4053 ELSE
4054 "positive_votes" * "policy_row"."direct_majority_den" >=
4055 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
4056 END
4057 AND "positive_votes" >= "policy_row"."direct_majority_positive"
4058 AND "issue_row"."voter_count"-"negative_votes" >=
4059 "policy_row"."direct_majority_non_negative",
4060 "indirect_majority" =
4061 CASE WHEN "policy_row"."indirect_majority_strict" THEN
4062 "positive_votes" * "policy_row"."indirect_majority_den" >
4063 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4064 ELSE
4065 "positive_votes" * "policy_row"."indirect_majority_den" >=
4066 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4067 END
4068 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
4069 AND "issue_row"."voter_count"-"negative_votes" >=
4070 "policy_row"."indirect_majority_non_negative",
4071 "schulze_rank" = "rank_ary"["i"],
4072 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
4073 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"[1],
4074 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"[1],
4075 "eligible" = FALSE,
4076 "winner" = FALSE,
4077 "rank" = NULL -- NOTE: in cases of manual reset of issue state
4078 WHERE "id" = "initiative_id_v";
4079 "i" := "i" + 1;
4080 END LOOP;
4081 IF "i" != "dimension_v" + 1 THEN
4082 RAISE EXCEPTION 'Wrong winner count (should not happen)';
4083 END IF;
4084 -- take indirect majorities into account:
4085 LOOP
4086 UPDATE "initiative" SET "indirect_majority" = TRUE
4087 FROM (
4088 SELECT "new_initiative"."id" AS "initiative_id"
4089 FROM "initiative" "old_initiative"
4090 JOIN "initiative" "new_initiative"
4091 ON "new_initiative"."issue_id" = "issue_id_p"
4092 AND "new_initiative"."indirect_majority" = FALSE
4093 JOIN "battle" "battle_win"
4094 ON "battle_win"."issue_id" = "issue_id_p"
4095 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
4096 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
4097 JOIN "battle" "battle_lose"
4098 ON "battle_lose"."issue_id" = "issue_id_p"
4099 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
4100 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
4101 WHERE "old_initiative"."issue_id" = "issue_id_p"
4102 AND "old_initiative"."indirect_majority" = TRUE
4103 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
4104 "battle_win"."count" * "policy_row"."indirect_majority_den" >
4105 "policy_row"."indirect_majority_num" *
4106 ("battle_win"."count"+"battle_lose"."count")
4107 ELSE
4108 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
4109 "policy_row"."indirect_majority_num" *
4110 ("battle_win"."count"+"battle_lose"."count")
4111 END
4112 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
4113 AND "issue_row"."voter_count"-"battle_lose"."count" >=
4114 "policy_row"."indirect_majority_non_negative"
4115 ) AS "subquery"
4116 WHERE "id" = "subquery"."initiative_id";
4117 EXIT WHEN NOT FOUND;
4118 END LOOP;
4119 -- set "multistage_majority" for remaining matching initiatives:
4120 UPDATE "initiative" SET "multistage_majority" = TRUE
4121 FROM (
4122 SELECT "losing_initiative"."id" AS "initiative_id"
4123 FROM "initiative" "losing_initiative"
4124 JOIN "initiative" "winning_initiative"
4125 ON "winning_initiative"."issue_id" = "issue_id_p"
4126 AND "winning_initiative"."admitted"
4127 JOIN "battle" "battle_win"
4128 ON "battle_win"."issue_id" = "issue_id_p"
4129 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
4130 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
4131 JOIN "battle" "battle_lose"
4132 ON "battle_lose"."issue_id" = "issue_id_p"
4133 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
4134 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
4135 WHERE "losing_initiative"."issue_id" = "issue_id_p"
4136 AND "losing_initiative"."admitted"
4137 AND "winning_initiative"."schulze_rank" <
4138 "losing_initiative"."schulze_rank"
4139 AND "battle_win"."count" > "battle_lose"."count"
4140 AND (
4141 "battle_win"."count" > "winning_initiative"."positive_votes" OR
4142 "battle_lose"."count" < "losing_initiative"."negative_votes" )
4143 ) AS "subquery"
4144 WHERE "id" = "subquery"."initiative_id";
4145 -- mark eligible initiatives:
4146 UPDATE "initiative" SET "eligible" = TRUE
4147 WHERE "issue_id" = "issue_id_p"
4148 AND "initiative"."direct_majority"
4149 AND "initiative"."indirect_majority"
4150 AND "initiative"."better_than_status_quo"
4151 AND (
4152 "policy_row"."no_multistage_majority" = FALSE OR
4153 "initiative"."multistage_majority" = FALSE );
4154 -- mark final winner:
4155 UPDATE "initiative" SET "winner" = TRUE
4156 FROM (
4157 SELECT "id" AS "initiative_id"
4158 FROM "initiative"
4159 WHERE "issue_id" = "issue_id_p" AND "eligible"
4160 ORDER BY
4161 "schulze_rank",
4162 "id"
4163 LIMIT 1
4164 ) AS "subquery"
4165 WHERE "id" = "subquery"."initiative_id";
4166 -- write (final) ranks:
4167 "rank_v" := 1;
4168 FOR "initiative_id_v" IN
4169 SELECT "id"
4170 FROM "initiative"
4171 WHERE "issue_id" = "issue_id_p" AND "admitted"
4172 ORDER BY
4173 "winner" DESC,
4174 "eligible" DESC,
4175 "schulze_rank",
4176 "id"
4177 LOOP
4178 UPDATE "initiative" SET "rank" = "rank_v"
4179 WHERE "id" = "initiative_id_v";
4180 "rank_v" := "rank_v" + 1;
4181 END LOOP;
4182 -- set schulze rank of status quo and mark issue as finished:
4183 UPDATE "issue" SET
4184 "status_quo_schulze_rank" = "rank_ary"[1],
4185 "state" =
4186 CASE WHEN EXISTS (
4187 SELECT NULL FROM "initiative"
4188 WHERE "issue_id" = "issue_id_p" AND "winner"
4189 ) THEN
4190 'finished_with_winner'::"issue_state"
4191 ELSE
4192 'finished_without_winner'::"issue_state"
4193 END,
4194 "closed" = "phase_finished",
4195 "phase_finished" = NULL
4196 WHERE "id" = "issue_id_p";
4197 RETURN;
4198 END;
4199 $$;
4201 COMMENT ON FUNCTION "calculate_ranks"
4202 ( "issue"."id"%TYPE )
4203 IS 'Determine ranking (Votes have to be counted first)';
4207 -----------------------------
4208 -- Automatic state changes --
4209 -----------------------------
4212 CREATE TYPE "check_issue_persistence" AS (
4213 "state" "issue_state",
4214 "phase_finished" BOOLEAN,
4215 "issue_revoked" BOOLEAN,
4216 "snapshot_created" BOOLEAN,
4217 "harmonic_weights_set" BOOLEAN,
4218 "closed_voting" BOOLEAN );
4220 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';
4223 CREATE FUNCTION "check_issue"
4224 ( "issue_id_p" "issue"."id"%TYPE,
4225 "persist" "check_issue_persistence" )
4226 RETURNS "check_issue_persistence"
4227 LANGUAGE 'plpgsql' VOLATILE AS $$
4228 DECLARE
4229 "issue_row" "issue"%ROWTYPE;
4230 "policy_row" "policy"%ROWTYPE;
4231 "initiative_row" "initiative"%ROWTYPE;
4232 "state_v" "issue_state";
4233 BEGIN
4234 PERFORM "require_transaction_isolation"();
4235 IF "persist" ISNULL THEN
4236 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4237 FOR UPDATE;
4238 IF "issue_row"."closed" NOTNULL THEN
4239 RETURN NULL;
4240 END IF;
4241 "persist"."state" := "issue_row"."state";
4242 IF
4243 ( "issue_row"."state" = 'admission' AND now() >=
4244 "issue_row"."created" + "issue_row"."admission_time" ) OR
4245 ( "issue_row"."state" = 'discussion' AND now() >=
4246 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
4247 ( "issue_row"."state" = 'verification' AND now() >=
4248 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
4249 ( "issue_row"."state" = 'voting' AND now() >=
4250 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
4251 THEN
4252 "persist"."phase_finished" := TRUE;
4253 ELSE
4254 "persist"."phase_finished" := FALSE;
4255 END IF;
4256 IF
4257 NOT EXISTS (
4258 -- all initiatives are revoked
4259 SELECT NULL FROM "initiative"
4260 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4261 ) AND (
4262 -- and issue has not been accepted yet
4263 "persist"."state" = 'admission' OR
4264 -- or verification time has elapsed
4265 ( "persist"."state" = 'verification' AND
4266 "persist"."phase_finished" ) OR
4267 -- or no initiatives have been revoked lately
4268 NOT EXISTS (
4269 SELECT NULL FROM "initiative"
4270 WHERE "issue_id" = "issue_id_p"
4271 AND now() < "revoked" + "issue_row"."verification_time"
4272 )
4273 )
4274 THEN
4275 "persist"."issue_revoked" := TRUE;
4276 ELSE
4277 "persist"."issue_revoked" := FALSE;
4278 END IF;
4279 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
4280 UPDATE "issue" SET "phase_finished" = now()
4281 WHERE "id" = "issue_row"."id";
4282 RETURN "persist";
4283 ELSIF
4284 "persist"."state" IN ('admission', 'discussion', 'verification')
4285 THEN
4286 RETURN "persist";
4287 ELSE
4288 RETURN NULL;
4289 END IF;
4290 END IF;
4291 IF
4292 "persist"."state" IN ('admission', 'discussion', 'verification') AND
4293 coalesce("persist"."snapshot_created", FALSE) = FALSE
4294 THEN
4295 PERFORM "create_snapshot"("issue_id_p");
4296 "persist"."snapshot_created" = TRUE;
4297 IF "persist"."phase_finished" THEN
4298 IF "persist"."state" = 'admission' THEN
4299 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
4300 ELSIF "persist"."state" = 'discussion' THEN
4301 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
4302 ELSIF "persist"."state" = 'verification' THEN
4303 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
4304 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4305 SELECT * INTO "policy_row" FROM "policy"
4306 WHERE "id" = "issue_row"."policy_id";
4307 FOR "initiative_row" IN
4308 SELECT * FROM "initiative"
4309 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4310 FOR UPDATE
4311 LOOP
4312 IF
4313 "initiative_row"."polling" OR (
4314 "initiative_row"."satisfied_supporter_count" > 0 AND
4315 "initiative_row"."satisfied_supporter_count" *
4316 "policy_row"."initiative_quorum_den" >=
4317 "issue_row"."population" * "policy_row"."initiative_quorum_num"
4318 )
4319 THEN
4320 UPDATE "initiative" SET "admitted" = TRUE
4321 WHERE "id" = "initiative_row"."id";
4322 ELSE
4323 UPDATE "initiative" SET "admitted" = FALSE
4324 WHERE "id" = "initiative_row"."id";
4325 END IF;
4326 END LOOP;
4327 END IF;
4328 END IF;
4329 RETURN "persist";
4330 END IF;
4331 IF
4332 "persist"."state" IN ('admission', 'discussion', 'verification') AND
4333 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
4334 THEN
4335 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
4336 "persist"."harmonic_weights_set" = TRUE;
4337 IF
4338 "persist"."phase_finished" OR
4339 "persist"."issue_revoked" OR
4340 "persist"."state" = 'admission'
4341 THEN
4342 RETURN "persist";
4343 ELSE
4344 RETURN NULL;
4345 END IF;
4346 END IF;
4347 IF "persist"."issue_revoked" THEN
4348 IF "persist"."state" = 'admission' THEN
4349 "state_v" := 'canceled_revoked_before_accepted';
4350 ELSIF "persist"."state" = 'discussion' THEN
4351 "state_v" := 'canceled_after_revocation_during_discussion';
4352 ELSIF "persist"."state" = 'verification' THEN
4353 "state_v" := 'canceled_after_revocation_during_verification';
4354 END IF;
4355 UPDATE "issue" SET
4356 "state" = "state_v",
4357 "closed" = "phase_finished",
4358 "phase_finished" = NULL
4359 WHERE "id" = "issue_id_p";
4360 RETURN NULL;
4361 END IF;
4362 IF "persist"."state" = 'admission' THEN
4363 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4364 FOR UPDATE;
4365 SELECT * INTO "policy_row"
4366 FROM "policy" WHERE "id" = "issue_row"."policy_id";
4367 IF EXISTS (
4368 SELECT NULL FROM "initiative"
4369 WHERE "issue_id" = "issue_id_p"
4370 AND "supporter_count" > 0
4371 AND "supporter_count" * "policy_row"."issue_quorum_den"
4372 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
4373 ) THEN
4374 UPDATE "issue" SET
4375 "state" = 'discussion',
4376 "accepted" = coalesce("phase_finished", now()),
4377 "phase_finished" = NULL
4378 WHERE "id" = "issue_id_p";
4379 ELSIF "issue_row"."phase_finished" NOTNULL THEN
4380 UPDATE "issue" SET
4381 "state" = 'canceled_issue_not_accepted',
4382 "closed" = "phase_finished",
4383 "phase_finished" = NULL
4384 WHERE "id" = "issue_id_p";
4385 END IF;
4386 RETURN NULL;
4387 END IF;
4388 IF "persist"."phase_finished" THEN
4389 if "persist"."state" = 'discussion' THEN
4390 UPDATE "issue" SET
4391 "state" = 'verification',
4392 "half_frozen" = "phase_finished",
4393 "phase_finished" = NULL
4394 WHERE "id" = "issue_id_p";
4395 RETURN NULL;
4396 END IF;
4397 IF "persist"."state" = 'verification' THEN
4398 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4399 FOR UPDATE;
4400 SELECT * INTO "policy_row" FROM "policy"
4401 WHERE "id" = "issue_row"."policy_id";
4402 IF EXISTS (
4403 SELECT NULL FROM "initiative"
4404 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
4405 ) THEN
4406 UPDATE "issue" SET
4407 "state" = 'voting',
4408 "fully_frozen" = "phase_finished",
4409 "phase_finished" = NULL
4410 WHERE "id" = "issue_id_p";
4411 ELSE
4412 UPDATE "issue" SET
4413 "state" = 'canceled_no_initiative_admitted',
4414 "fully_frozen" = "phase_finished",
4415 "closed" = "phase_finished",
4416 "phase_finished" = NULL
4417 WHERE "id" = "issue_id_p";
4418 -- NOTE: The following DELETE statements have effect only when
4419 -- issue state has been manipulated
4420 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
4421 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
4422 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
4423 END IF;
4424 RETURN NULL;
4425 END IF;
4426 IF "persist"."state" = 'voting' THEN
4427 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
4428 PERFORM "close_voting"("issue_id_p");
4429 "persist"."closed_voting" = TRUE;
4430 RETURN "persist";
4431 END IF;
4432 PERFORM "calculate_ranks"("issue_id_p");
4433 RETURN NULL;
4434 END IF;
4435 END IF;
4436 RAISE WARNING 'should not happen';
4437 RETURN NULL;
4438 END;
4439 $$;
4441 COMMENT ON FUNCTION "check_issue"
4442 ( "issue"."id"%TYPE,
4443 "check_issue_persistence" )
4444 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")';
4447 CREATE FUNCTION "check_everything"()
4448 RETURNS VOID
4449 LANGUAGE 'plpgsql' VOLATILE AS $$
4450 DECLARE
4451 "issue_id_v" "issue"."id"%TYPE;
4452 "persist_v" "check_issue_persistence";
4453 BEGIN
4454 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
4455 DELETE FROM "expired_session";
4456 PERFORM "check_activity"();
4457 PERFORM "calculate_member_counts"();
4458 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
4459 "persist_v" := NULL;
4460 LOOP
4461 "persist_v" := "check_issue"("issue_id_v", "persist_v");
4462 EXIT WHEN "persist_v" ISNULL;
4463 END LOOP;
4464 END LOOP;
4465 RETURN;
4466 END;
4467 $$;
4469 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.';
4473 ----------------------
4474 -- Deletion of data --
4475 ----------------------
4478 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
4479 RETURNS VOID
4480 LANGUAGE 'plpgsql' VOLATILE AS $$
4481 BEGIN
4482 IF EXISTS (
4483 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
4484 ) THEN
4485 -- override protection triggers:
4486 INSERT INTO "temporary_transaction_data" ("key", "value")
4487 VALUES ('override_protection_triggers', TRUE::TEXT);
4488 -- clean data:
4489 DELETE FROM "delegating_voter"
4490 WHERE "issue_id" = "issue_id_p";
4491 DELETE FROM "direct_voter"
4492 WHERE "issue_id" = "issue_id_p";
4493 DELETE FROM "delegating_interest_snapshot"
4494 WHERE "issue_id" = "issue_id_p";
4495 DELETE FROM "direct_interest_snapshot"
4496 WHERE "issue_id" = "issue_id_p";
4497 DELETE FROM "delegating_population_snapshot"
4498 WHERE "issue_id" = "issue_id_p";
4499 DELETE FROM "direct_population_snapshot"
4500 WHERE "issue_id" = "issue_id_p";
4501 DELETE FROM "non_voter"
4502 WHERE "issue_id" = "issue_id_p";
4503 DELETE FROM "delegation"
4504 WHERE "issue_id" = "issue_id_p";
4505 DELETE FROM "supporter"
4506 USING "initiative" -- NOTE: due to missing index on issue_id
4507 WHERE "initiative"."issue_id" = "issue_id_p"
4508 AND "supporter"."initiative_id" = "initiative_id";
4509 -- mark issue as cleaned:
4510 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
4511 -- finish overriding protection triggers (avoids garbage):
4512 DELETE FROM "temporary_transaction_data"
4513 WHERE "key" = 'override_protection_triggers';
4514 END IF;
4515 RETURN;
4516 END;
4517 $$;
4519 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
4522 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
4523 RETURNS VOID
4524 LANGUAGE 'plpgsql' VOLATILE AS $$
4525 BEGIN
4526 UPDATE "member" SET
4527 "last_login" = NULL,
4528 "last_delegation_check" = NULL,
4529 "login" = NULL,
4530 "password" = NULL,
4531 "locked" = TRUE,
4532 "active" = FALSE,
4533 "notify_email" = NULL,
4534 "notify_email_unconfirmed" = NULL,
4535 "notify_email_secret" = NULL,
4536 "notify_email_secret_expiry" = NULL,
4537 "notify_email_lock_expiry" = NULL,
4538 "login_recovery_expiry" = NULL,
4539 "password_reset_secret" = NULL,
4540 "password_reset_secret_expiry" = NULL,
4541 "organizational_unit" = NULL,
4542 "internal_posts" = NULL,
4543 "realname" = NULL,
4544 "birthday" = NULL,
4545 "address" = NULL,
4546 "email" = NULL,
4547 "xmpp_address" = NULL,
4548 "website" = NULL,
4549 "phone" = NULL,
4550 "mobile_phone" = NULL,
4551 "profession" = NULL,
4552 "external_memberships" = NULL,
4553 "external_posts" = NULL,
4554 "statement" = NULL
4555 WHERE "id" = "member_id_p";
4556 -- "text_search_data" is updated by triggers
4557 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
4558 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
4559 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
4560 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
4561 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
4562 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
4563 DELETE FROM "session" WHERE "member_id" = "member_id_p";
4564 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
4565 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
4566 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
4567 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
4568 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
4569 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
4570 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
4571 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
4572 DELETE FROM "direct_voter" USING "issue"
4573 WHERE "direct_voter"."issue_id" = "issue"."id"
4574 AND "issue"."closed" ISNULL
4575 AND "member_id" = "member_id_p";
4576 RETURN;
4577 END;
4578 $$;
4580 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)';
4583 CREATE FUNCTION "delete_private_data"()
4584 RETURNS VOID
4585 LANGUAGE 'plpgsql' VOLATILE AS $$
4586 BEGIN
4587 DELETE FROM "temporary_transaction_data";
4588 DELETE FROM "member" WHERE "activated" ISNULL;
4589 UPDATE "member" SET
4590 "invite_code" = NULL,
4591 "invite_code_expiry" = NULL,
4592 "admin_comment" = NULL,
4593 "last_login" = NULL,
4594 "last_delegation_check" = NULL,
4595 "login" = NULL,
4596 "password" = NULL,
4597 "lang" = NULL,
4598 "notify_email" = NULL,
4599 "notify_email_unconfirmed" = NULL,
4600 "notify_email_secret" = NULL,
4601 "notify_email_secret_expiry" = NULL,
4602 "notify_email_lock_expiry" = NULL,
4603 "notify_level" = NULL,
4604 "login_recovery_expiry" = NULL,
4605 "password_reset_secret" = NULL,
4606 "password_reset_secret_expiry" = NULL,
4607 "organizational_unit" = NULL,
4608 "internal_posts" = NULL,
4609 "realname" = NULL,
4610 "birthday" = NULL,
4611 "address" = NULL,
4612 "email" = NULL,
4613 "xmpp_address" = NULL,
4614 "website" = NULL,
4615 "phone" = NULL,
4616 "mobile_phone" = NULL,
4617 "profession" = NULL,
4618 "external_memberships" = NULL,
4619 "external_posts" = NULL,
4620 "formatting_engine" = NULL,
4621 "statement" = NULL;
4622 -- "text_search_data" is updated by triggers
4623 DELETE FROM "setting";
4624 DELETE FROM "setting_map";
4625 DELETE FROM "member_relation_setting";
4626 DELETE FROM "member_image";
4627 DELETE FROM "contact";
4628 DELETE FROM "ignored_member";
4629 DELETE FROM "session";
4630 DELETE FROM "area_setting";
4631 DELETE FROM "issue_setting";
4632 DELETE FROM "ignored_initiative";
4633 DELETE FROM "initiative_setting";
4634 DELETE FROM "suggestion_setting";
4635 DELETE FROM "non_voter";
4636 DELETE FROM "direct_voter" USING "issue"
4637 WHERE "direct_voter"."issue_id" = "issue"."id"
4638 AND "issue"."closed" ISNULL;
4639 RETURN;
4640 END;
4641 $$;
4643 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.';
4647 COMMIT;