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