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