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