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