liquid_feedback_core
view core.sql @ 385:e474e9e1240a
Code cleanup regarding temporary storage of session/transaction data to disable protection triggers
| author | jbe |
|---|---|
| date | Sat Mar 23 13:05:43 2013 +0100 (2013-03-23) |
| parents | 1c991490f075 |
| children | ae69cf82c05f |
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.3', 2, 2, 3))
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 "login" TEXT UNIQUE,
108 "password" TEXT,
109 "locked" BOOLEAN NOT NULL DEFAULT FALSE,
110 "active" BOOLEAN NOT NULL DEFAULT FALSE,
111 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
112 "lang" TEXT,
113 "notify_email" TEXT,
114 "notify_email_unconfirmed" TEXT,
115 "notify_email_secret" TEXT UNIQUE,
116 "notify_email_secret_expiry" TIMESTAMPTZ,
117 "notify_email_lock_expiry" TIMESTAMPTZ,
118 "notify_level" "notify_level",
119 "password_reset_secret" TEXT UNIQUE,
120 "password_reset_secret_expiry" TIMESTAMPTZ,
121 "name" TEXT UNIQUE,
122 "identification" TEXT UNIQUE,
123 "authentication" TEXT,
124 "organizational_unit" TEXT,
125 "internal_posts" TEXT,
126 "realname" TEXT,
127 "birthday" DATE,
128 "address" TEXT,
129 "email" TEXT,
130 "xmpp_address" TEXT,
131 "website" TEXT,
132 "phone" TEXT,
133 "mobile_phone" TEXT,
134 "profession" TEXT,
135 "external_memberships" TEXT,
136 "external_posts" TEXT,
137 "formatting_engine" TEXT,
138 "statement" TEXT,
139 "text_search_data" TSVECTOR,
140 CONSTRAINT "active_requires_activated_and_last_activity"
141 CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)),
142 CONSTRAINT "name_not_null_if_activated"
143 CHECK ("activated" ISNULL OR "name" NOTNULL) );
144 CREATE INDEX "member_active_idx" ON "member" ("active");
145 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
146 CREATE TRIGGER "update_text_search_data"
147 BEFORE INSERT OR UPDATE ON "member"
148 FOR EACH ROW EXECUTE PROCEDURE
149 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
150 "name", "identification", "organizational_unit", "internal_posts",
151 "realname", "external_memberships", "external_posts", "statement" );
153 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
155 COMMENT ON COLUMN "member"."created" IS 'Creation of member record and/or invite code';
156 COMMENT ON COLUMN "member"."invite_code" IS 'Optional invite code, to allow a member to initialize his/her account the first time';
157 COMMENT ON COLUMN "member"."invite_code_expiry" IS 'Expiry data/time for "invite_code"';
158 COMMENT ON COLUMN "member"."admin_comment" IS 'Hidden comment for administrative purposes';
159 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';
160 COMMENT ON COLUMN "member"."last_activity" IS 'Date of last activity of member; required to be set for "active" members';
161 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';
162 COMMENT ON COLUMN "member"."login" IS 'Login name';
163 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
164 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
165 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".';
166 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
167 COMMENT ON COLUMN "member"."lang" IS 'Language code of the preferred language of the member';
168 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
169 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
170 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
171 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
172 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
173 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';
174 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member, may be NULL if account has not been activated yet';
175 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
176 COMMENT ON COLUMN "member"."authentication" IS 'Information about how this member was authenticated';
177 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
178 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
179 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
180 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
181 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
182 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
183 COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"';
184 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his/her profile';
187 -- DEPRECATED API TABLES --
189 CREATE TYPE "application_access_level" AS ENUM
190 ('member', 'full', 'pseudonymous', 'anonymous');
192 COMMENT ON TYPE "application_access_level" IS 'DEPRECATED, WILL BE REMOVED! Access privileges for applications using the API';
195 CREATE TABLE "member_application" (
196 "id" SERIAL8 PRIMARY KEY,
197 UNIQUE ("member_id", "name"),
198 "member_id" INT4 NOT NULL REFERENCES "member" ("id")
199 ON DELETE CASCADE ON UPDATE CASCADE,
200 "name" TEXT NOT NULL,
201 "comment" TEXT,
202 "access_level" "application_access_level" NOT NULL,
203 "key" TEXT NOT NULL UNIQUE,
204 "last_usage" TIMESTAMPTZ );
206 COMMENT ON TABLE "member_application" IS 'DEPRECATED, WILL BE REMOVED! Registered application being allowed to use the API';
208 -- END OF DEPRECARED API TABLES --
211 CREATE TABLE "member_history" (
212 "id" SERIAL8 PRIMARY KEY,
213 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
214 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
215 "active" BOOLEAN NOT NULL,
216 "name" TEXT NOT NULL );
217 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
219 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
221 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
222 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
225 CREATE TABLE "rendered_member_statement" (
226 PRIMARY KEY ("member_id", "format"),
227 "member_id" INT8 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
228 "format" TEXT,
229 "content" TEXT NOT NULL );
231 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)';
234 CREATE TABLE "setting" (
235 PRIMARY KEY ("member_id", "key"),
236 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
237 "key" TEXT NOT NULL,
238 "value" TEXT NOT NULL );
239 CREATE INDEX "setting_key_idx" ON "setting" ("key");
241 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
243 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
246 CREATE TABLE "setting_map" (
247 PRIMARY KEY ("member_id", "key", "subkey"),
248 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
249 "key" TEXT NOT NULL,
250 "subkey" TEXT NOT NULL,
251 "value" TEXT NOT NULL );
252 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
254 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
256 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
257 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
258 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
261 CREATE TABLE "member_relation_setting" (
262 PRIMARY KEY ("member_id", "key", "other_member_id"),
263 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
264 "key" TEXT NOT NULL,
265 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
266 "value" TEXT NOT NULL );
268 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
271 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
273 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
276 CREATE TABLE "member_image" (
277 PRIMARY KEY ("member_id", "image_type", "scaled"),
278 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
279 "image_type" "member_image_type",
280 "scaled" BOOLEAN,
281 "content_type" TEXT,
282 "data" BYTEA NOT NULL );
284 COMMENT ON TABLE "member_image" IS 'Images of members';
286 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
289 CREATE TABLE "member_count" (
290 "calculated" TIMESTAMPTZ NOT NULL DEFAULT now(),
291 "total_count" INT4 NOT NULL );
293 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';
295 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
296 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
299 CREATE TABLE "contact" (
300 PRIMARY KEY ("member_id", "other_member_id"),
301 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
302 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
303 "public" BOOLEAN NOT NULL DEFAULT FALSE,
304 CONSTRAINT "cant_save_yourself_as_contact"
305 CHECK ("member_id" != "other_member_id") );
306 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
308 COMMENT ON TABLE "contact" IS 'Contact lists';
310 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
311 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
312 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
315 CREATE TABLE "ignored_member" (
316 PRIMARY KEY ("member_id", "other_member_id"),
317 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
318 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
319 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
321 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
323 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';
324 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
327 CREATE TABLE "session" (
328 "ident" TEXT PRIMARY KEY,
329 "additional_secret" TEXT,
330 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
331 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
332 "lang" TEXT );
333 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
335 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
337 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
338 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
339 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
340 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
343 CREATE TABLE "policy" (
344 "id" SERIAL4 PRIMARY KEY,
345 "index" INT4 NOT NULL,
346 "active" BOOLEAN NOT NULL DEFAULT TRUE,
347 "name" TEXT NOT NULL UNIQUE,
348 "description" TEXT NOT NULL DEFAULT '',
349 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
350 "admission_time" INTERVAL,
351 "discussion_time" INTERVAL,
352 "verification_time" INTERVAL,
353 "voting_time" INTERVAL,
354 "issue_quorum_num" INT4,
355 "issue_quorum_den" INT4,
356 "initiative_quorum_num" INT4 NOT NULL,
357 "initiative_quorum_den" INT4 NOT NULL,
358 "direct_majority_num" INT4 NOT NULL DEFAULT 1,
359 "direct_majority_den" INT4 NOT NULL DEFAULT 2,
360 "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
361 "direct_majority_positive" INT4 NOT NULL DEFAULT 0,
362 "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0,
363 "indirect_majority_num" INT4 NOT NULL DEFAULT 1,
364 "indirect_majority_den" INT4 NOT NULL DEFAULT 2,
365 "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
366 "indirect_majority_positive" INT4 NOT NULL DEFAULT 0,
367 "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0,
368 "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT TRUE,
369 "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE,
370 CONSTRAINT "timing" CHECK (
371 ( "polling" = FALSE AND
372 "admission_time" NOTNULL AND "discussion_time" NOTNULL AND
373 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
374 ( "polling" = TRUE AND
375 "admission_time" ISNULL AND "discussion_time" NOTNULL AND
376 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
377 ( "polling" = TRUE AND
378 "admission_time" ISNULL AND "discussion_time" ISNULL AND
379 "verification_time" ISNULL AND "voting_time" ISNULL ) ),
380 CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK (
381 "polling" = "issue_quorum_num" ISNULL AND
382 "polling" = "issue_quorum_den" ISNULL ) );
383 CREATE INDEX "policy_active_idx" ON "policy" ("active");
385 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
387 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
388 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
389 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';
390 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';
391 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"';
392 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"';
393 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'')';
394 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''';
395 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''';
396 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
397 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
398 COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner';
399 COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner';
400 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.';
401 COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner';
402 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';
403 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';
404 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';
405 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.';
406 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';
407 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';
408 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.';
409 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").';
412 CREATE TABLE "unit" (
413 "id" SERIAL4 PRIMARY KEY,
414 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
415 "active" BOOLEAN NOT NULL DEFAULT TRUE,
416 "name" TEXT NOT NULL,
417 "description" TEXT NOT NULL DEFAULT '',
418 "member_count" INT4,
419 "text_search_data" TSVECTOR );
420 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
421 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
422 CREATE INDEX "unit_active_idx" ON "unit" ("active");
423 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
424 CREATE TRIGGER "update_text_search_data"
425 BEFORE INSERT OR UPDATE ON "unit"
426 FOR EACH ROW EXECUTE PROCEDURE
427 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
428 "name", "description" );
430 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
432 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
433 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this unit';
434 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
437 CREATE TABLE "unit_setting" (
438 PRIMARY KEY ("member_id", "key", "unit_id"),
439 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
440 "key" TEXT NOT NULL,
441 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
442 "value" TEXT NOT NULL );
444 COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings';
447 CREATE TABLE "area" (
448 "id" SERIAL4 PRIMARY KEY,
449 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
450 "active" BOOLEAN NOT NULL DEFAULT TRUE,
451 "name" TEXT NOT NULL,
452 "description" TEXT NOT NULL DEFAULT '',
453 "direct_member_count" INT4,
454 "member_weight" INT4,
455 "text_search_data" TSVECTOR );
456 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
457 CREATE INDEX "area_active_idx" ON "area" ("active");
458 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
459 CREATE TRIGGER "update_text_search_data"
460 BEFORE INSERT OR UPDATE ON "area"
461 FOR EACH ROW EXECUTE PROCEDURE
462 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
463 "name", "description" );
465 COMMENT ON TABLE "area" IS 'Subject areas';
467 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
468 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"';
469 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
472 CREATE TABLE "area_setting" (
473 PRIMARY KEY ("member_id", "key", "area_id"),
474 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
475 "key" TEXT NOT NULL,
476 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
477 "value" TEXT NOT NULL );
479 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
482 CREATE TABLE "allowed_policy" (
483 PRIMARY KEY ("area_id", "policy_id"),
484 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
485 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
486 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
487 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
489 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
491 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
494 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
496 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';
499 CREATE TYPE "issue_state" AS ENUM (
500 'admission', 'discussion', 'verification', 'voting',
501 'canceled_revoked_before_accepted',
502 'canceled_issue_not_accepted',
503 'canceled_after_revocation_during_discussion',
504 'canceled_after_revocation_during_verification',
505 'canceled_no_initiative_admitted',
506 'finished_without_winner', 'finished_with_winner');
508 COMMENT ON TYPE "issue_state" IS 'State of issues';
511 CREATE TABLE "issue" (
512 "id" SERIAL4 PRIMARY KEY,
513 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
514 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
515 "state" "issue_state" NOT NULL DEFAULT 'admission',
516 "phase_finished" TIMESTAMPTZ,
517 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
518 "accepted" TIMESTAMPTZ,
519 "half_frozen" TIMESTAMPTZ,
520 "fully_frozen" TIMESTAMPTZ,
521 "closed" TIMESTAMPTZ,
522 "cleaned" TIMESTAMPTZ,
523 "admission_time" INTERVAL,
524 "discussion_time" INTERVAL NOT NULL,
525 "verification_time" INTERVAL NOT NULL,
526 "voting_time" INTERVAL NOT NULL,
527 "snapshot" TIMESTAMPTZ,
528 "latest_snapshot_event" "snapshot_event",
529 "population" INT4,
530 "voter_count" INT4,
531 "status_quo_schulze_rank" INT4,
532 CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (
533 "admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created") ),
534 CONSTRAINT "valid_state" CHECK (
535 (
536 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
537 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
538 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL ) OR
539 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL)
540 ) AND (
541 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
542 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
543 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
544 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
545 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
546 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
547 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
548 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
549 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" = "fully_frozen") OR
550 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen") OR
551 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen")
552 )),
553 CONSTRAINT "phase_finished_only_when_not_closed" CHECK (
554 "phase_finished" ISNULL OR "closed" ISNULL ),
555 CONSTRAINT "state_change_order" CHECK (
556 "created" <= "accepted" AND
557 "accepted" <= "half_frozen" AND
558 "half_frozen" <= "fully_frozen" AND
559 "fully_frozen" <= "closed" ),
560 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
561 "cleaned" ISNULL OR "closed" NOTNULL ),
562 CONSTRAINT "last_snapshot_on_full_freeze"
563 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
564 CONSTRAINT "freeze_requires_snapshot"
565 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
566 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
567 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
568 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
569 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
570 CREATE INDEX "issue_created_idx" ON "issue" ("created");
571 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
572 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
573 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
574 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
575 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
576 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
578 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
580 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';
581 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
582 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.';
583 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.';
584 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.';
585 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
586 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
587 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
588 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
589 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
590 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
591 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';
592 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
593 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';
594 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
597 CREATE TABLE "issue_setting" (
598 PRIMARY KEY ("member_id", "key", "issue_id"),
599 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
600 "key" TEXT NOT NULL,
601 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
602 "value" TEXT NOT NULL );
604 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
607 CREATE TABLE "initiative" (
608 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
609 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
610 "id" SERIAL4 PRIMARY KEY,
611 "name" TEXT NOT NULL,
612 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
613 "discussion_url" TEXT,
614 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
615 "revoked" TIMESTAMPTZ,
616 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
617 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
618 "admitted" BOOLEAN,
619 "supporter_count" INT4,
620 "informed_supporter_count" INT4,
621 "satisfied_supporter_count" INT4,
622 "satisfied_informed_supporter_count" INT4,
623 "harmonic_weight" NUMERIC(12, 3),
624 "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE,
625 "positive_votes" INT4,
626 "negative_votes" INT4,
627 "direct_majority" BOOLEAN,
628 "indirect_majority" BOOLEAN,
629 "schulze_rank" INT4,
630 "better_than_status_quo" BOOLEAN,
631 "worse_than_status_quo" BOOLEAN,
632 "reverse_beat_path" BOOLEAN,
633 "multistage_majority" BOOLEAN,
634 "eligible" BOOLEAN,
635 "winner" BOOLEAN,
636 "rank" INT4,
637 "text_search_data" TSVECTOR,
638 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
639 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
640 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
641 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
642 CONSTRAINT "revoked_initiatives_cant_be_admitted"
643 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
644 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
645 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
646 ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND
647 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
648 "schulze_rank" ISNULL AND
649 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
650 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
651 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ),
652 CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")),
653 CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (
654 "eligible" = FALSE OR
655 ("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ),
656 CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE),
657 CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1),
658 CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE),
659 CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") );
660 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
661 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
662 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
663 CREATE TRIGGER "update_text_search_data"
664 BEFORE INSERT OR UPDATE ON "initiative"
665 FOR EACH ROW EXECUTE PROCEDURE
666 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
667 "name", "discussion_url");
669 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.';
671 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative does not need to pass the initiative quorum (see "policy"."polling")';
672 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
673 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
674 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoke the initiative';
675 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
676 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
677 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
678 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
679 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
680 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';
681 COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time';
682 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
683 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
684 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"';
685 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths';
686 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking';
687 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)';
688 COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';
689 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';
690 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';
691 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"';
692 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"';
693 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';
696 CREATE TABLE "battle" (
697 "issue_id" INT4 NOT NULL,
698 "winning_initiative_id" INT4,
699 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
700 "losing_initiative_id" INT4,
701 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
702 "count" INT4 NOT NULL,
703 CONSTRAINT "initiative_ids_not_equal" CHECK (
704 "winning_initiative_id" != "losing_initiative_id" OR
705 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
706 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) );
707 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
708 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
709 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
711 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';
714 CREATE TABLE "ignored_initiative" (
715 PRIMARY KEY ("initiative_id", "member_id"),
716 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
717 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
718 CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
720 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
723 CREATE TABLE "initiative_setting" (
724 PRIMARY KEY ("member_id", "key", "initiative_id"),
725 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
726 "key" TEXT NOT NULL,
727 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
728 "value" TEXT NOT NULL );
730 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
733 CREATE TABLE "draft" (
734 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
735 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
736 "id" SERIAL8 PRIMARY KEY,
737 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
738 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
739 "formatting_engine" TEXT,
740 "content" TEXT NOT NULL,
741 "text_search_data" TSVECTOR );
742 CREATE INDEX "draft_created_idx" ON "draft" ("created");
743 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
744 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
745 CREATE TRIGGER "update_text_search_data"
746 BEFORE INSERT OR UPDATE ON "draft"
747 FOR EACH ROW EXECUTE PROCEDURE
748 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
750 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.';
752 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
753 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
756 CREATE TABLE "rendered_draft" (
757 PRIMARY KEY ("draft_id", "format"),
758 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
759 "format" TEXT,
760 "content" TEXT NOT NULL );
762 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)';
765 CREATE TABLE "suggestion" (
766 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
767 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
768 "id" SERIAL8 PRIMARY KEY,
769 "draft_id" INT8 NOT NULL,
770 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
771 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
772 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
773 "name" TEXT NOT NULL,
774 "formatting_engine" TEXT,
775 "content" TEXT NOT NULL DEFAULT '',
776 "text_search_data" TSVECTOR,
777 "minus2_unfulfilled_count" INT4,
778 "minus2_fulfilled_count" INT4,
779 "minus1_unfulfilled_count" INT4,
780 "minus1_fulfilled_count" INT4,
781 "plus1_unfulfilled_count" INT4,
782 "plus1_fulfilled_count" INT4,
783 "plus2_unfulfilled_count" INT4,
784 "plus2_fulfilled_count" INT4,
785 "proportional_order" INT4 );
786 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
787 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
788 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
789 CREATE TRIGGER "update_text_search_data"
790 BEFORE INSERT OR UPDATE ON "suggestion"
791 FOR EACH ROW EXECUTE PROCEDURE
792 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
793 "name", "content");
795 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';
797 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")';
798 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
799 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
800 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
801 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
802 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
803 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
804 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
805 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
806 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"';
809 CREATE TABLE "rendered_suggestion" (
810 PRIMARY KEY ("suggestion_id", "format"),
811 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
812 "format" TEXT,
813 "content" TEXT NOT NULL );
815 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)';
818 CREATE TABLE "suggestion_setting" (
819 PRIMARY KEY ("member_id", "key", "suggestion_id"),
820 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
821 "key" TEXT NOT NULL,
822 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
823 "value" TEXT NOT NULL );
825 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
828 CREATE TABLE "privilege" (
829 PRIMARY KEY ("unit_id", "member_id"),
830 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
831 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
832 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
833 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
834 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
835 "member_manager" BOOLEAN NOT NULL DEFAULT FALSE,
836 "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE,
837 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE,
838 "polling_right" BOOLEAN NOT NULL DEFAULT FALSE );
840 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
842 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke any privileges to/from other members';
843 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create and disable sub units';
844 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create and disable areas and set area parameters';
845 COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';
846 COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';
847 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote';
848 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';
851 CREATE TABLE "membership" (
852 PRIMARY KEY ("area_id", "member_id"),
853 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
854 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
855 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
857 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
860 CREATE TABLE "interest" (
861 PRIMARY KEY ("issue_id", "member_id"),
862 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
863 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
864 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
866 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.';
869 CREATE TABLE "initiator" (
870 PRIMARY KEY ("initiative_id", "member_id"),
871 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
872 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
873 "accepted" BOOLEAN );
874 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
876 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.';
878 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.';
881 CREATE TABLE "supporter" (
882 "issue_id" INT4 NOT NULL,
883 PRIMARY KEY ("initiative_id", "member_id"),
884 "initiative_id" INT4,
885 "member_id" INT4,
886 "draft_id" INT8 NOT NULL,
887 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
888 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE );
889 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
891 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.';
893 COMMENT ON COLUMN "supporter"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
894 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")';
897 CREATE TABLE "opinion" (
898 "initiative_id" INT4 NOT NULL,
899 PRIMARY KEY ("suggestion_id", "member_id"),
900 "suggestion_id" INT8,
901 "member_id" INT4,
902 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
903 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
904 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
905 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
906 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
908 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.';
910 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
913 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
915 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
918 CREATE TABLE "delegation" (
919 "id" SERIAL8 PRIMARY KEY,
920 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
921 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
922 "scope" "delegation_scope" NOT NULL,
923 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
924 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
925 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
926 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
927 CONSTRAINT "no_unit_delegation_to_null"
928 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
929 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
930 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
931 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
932 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
933 UNIQUE ("unit_id", "truster_id"),
934 UNIQUE ("area_id", "truster_id"),
935 UNIQUE ("issue_id", "truster_id") );
936 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
937 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
939 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
941 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
942 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
943 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
946 CREATE TABLE "direct_population_snapshot" (
947 PRIMARY KEY ("issue_id", "event", "member_id"),
948 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
949 "event" "snapshot_event",
950 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
951 "weight" INT4 );
952 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
954 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
956 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
957 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
960 CREATE TABLE "delegating_population_snapshot" (
961 PRIMARY KEY ("issue_id", "event", "member_id"),
962 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
963 "event" "snapshot_event",
964 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
965 "weight" INT4,
966 "scope" "delegation_scope" NOT NULL,
967 "delegate_member_ids" INT4[] NOT NULL );
968 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
970 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
972 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
973 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
974 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
975 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"';
978 CREATE TABLE "direct_interest_snapshot" (
979 PRIMARY KEY ("issue_id", "event", "member_id"),
980 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
981 "event" "snapshot_event",
982 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
983 "weight" INT4 );
984 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
986 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
988 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
989 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
992 CREATE TABLE "delegating_interest_snapshot" (
993 PRIMARY KEY ("issue_id", "event", "member_id"),
994 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
995 "event" "snapshot_event",
996 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
997 "weight" INT4,
998 "scope" "delegation_scope" NOT NULL,
999 "delegate_member_ids" INT4[] NOT NULL );
1000 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
1002 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
1004 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1005 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
1006 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
1007 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"';
1010 CREATE TABLE "direct_supporter_snapshot" (
1011 "issue_id" INT4 NOT NULL,
1012 PRIMARY KEY ("initiative_id", "event", "member_id"),
1013 "initiative_id" INT4,
1014 "event" "snapshot_event",
1015 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1016 "draft_id" INT8 NOT NULL,
1017 "informed" BOOLEAN NOT NULL,
1018 "satisfied" BOOLEAN NOT NULL,
1019 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1020 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
1021 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1022 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
1024 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
1026 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';
1027 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1028 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
1029 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
1032 CREATE TABLE "non_voter" (
1033 PRIMARY KEY ("issue_id", "member_id"),
1034 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1035 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
1036 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
1038 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
1041 CREATE TABLE "direct_voter" (
1042 PRIMARY KEY ("issue_id", "member_id"),
1043 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1044 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1045 "weight" INT4,
1046 "comment_changed" TIMESTAMPTZ,
1047 "formatting_engine" TEXT,
1048 "comment" TEXT,
1049 "text_search_data" TSVECTOR );
1050 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
1051 CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data");
1052 CREATE TRIGGER "update_text_search_data"
1053 BEFORE INSERT OR UPDATE ON "direct_voter"
1054 FOR EACH ROW EXECUTE PROCEDURE
1055 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment");
1057 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.';
1059 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
1060 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';
1061 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';
1062 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.';
1065 CREATE TABLE "rendered_voter_comment" (
1066 PRIMARY KEY ("issue_id", "member_id", "format"),
1067 FOREIGN KEY ("issue_id", "member_id")
1068 REFERENCES "direct_voter" ("issue_id", "member_id")
1069 ON DELETE CASCADE ON UPDATE CASCADE,
1070 "issue_id" INT4,
1071 "member_id" INT4,
1072 "format" TEXT,
1073 "content" TEXT NOT NULL );
1075 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)';
1078 CREATE TABLE "delegating_voter" (
1079 PRIMARY KEY ("issue_id", "member_id"),
1080 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1081 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1082 "weight" INT4,
1083 "scope" "delegation_scope" NOT NULL,
1084 "delegate_member_ids" INT4[] NOT NULL );
1085 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
1087 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
1089 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
1090 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
1091 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"';
1094 CREATE TABLE "vote" (
1095 "issue_id" INT4 NOT NULL,
1096 PRIMARY KEY ("initiative_id", "member_id"),
1097 "initiative_id" INT4,
1098 "member_id" INT4,
1099 "grade" INT4,
1100 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1101 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1102 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
1104 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.';
1106 COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
1107 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.';
1110 CREATE TYPE "event_type" AS ENUM (
1111 'issue_state_changed',
1112 'initiative_created_in_new_issue',
1113 'initiative_created_in_existing_issue',
1114 'initiative_revoked',
1115 'new_draft_created',
1116 'suggestion_created');
1118 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
1121 CREATE TABLE "event" (
1122 "id" SERIAL8 PRIMARY KEY,
1123 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
1124 "event" "event_type" NOT NULL,
1125 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1126 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1127 "state" "issue_state",
1128 "initiative_id" INT4,
1129 "draft_id" INT8,
1130 "suggestion_id" INT8,
1131 FOREIGN KEY ("issue_id", "initiative_id")
1132 REFERENCES "initiative" ("issue_id", "id")
1133 ON DELETE CASCADE ON UPDATE CASCADE,
1134 FOREIGN KEY ("initiative_id", "draft_id")
1135 REFERENCES "draft" ("initiative_id", "id")
1136 ON DELETE CASCADE ON UPDATE CASCADE,
1137 FOREIGN KEY ("initiative_id", "suggestion_id")
1138 REFERENCES "suggestion" ("initiative_id", "id")
1139 ON DELETE CASCADE ON UPDATE CASCADE,
1140 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
1141 "event" != 'issue_state_changed' OR (
1142 "member_id" ISNULL AND
1143 "issue_id" NOTNULL AND
1144 "state" NOTNULL AND
1145 "initiative_id" ISNULL AND
1146 "draft_id" ISNULL AND
1147 "suggestion_id" ISNULL )),
1148 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
1149 "event" NOT IN (
1150 'initiative_created_in_new_issue',
1151 'initiative_created_in_existing_issue',
1152 'initiative_revoked',
1153 'new_draft_created'
1154 ) OR (
1155 "member_id" NOTNULL AND
1156 "issue_id" NOTNULL AND
1157 "state" NOTNULL AND
1158 "initiative_id" NOTNULL AND
1159 "draft_id" NOTNULL AND
1160 "suggestion_id" ISNULL )),
1161 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
1162 "event" != 'suggestion_created' OR (
1163 "member_id" NOTNULL AND
1164 "issue_id" NOTNULL AND
1165 "state" NOTNULL AND
1166 "initiative_id" NOTNULL AND
1167 "draft_id" ISNULL AND
1168 "suggestion_id" NOTNULL )) );
1169 CREATE INDEX "event_occurrence_idx" ON "event" ("occurrence");
1171 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
1173 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
1174 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
1175 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
1176 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
1179 CREATE TABLE "notification_sent" (
1180 "event_id" INT8 NOT NULL );
1181 CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1));
1183 COMMENT ON TABLE "notification_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';
1184 COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';
1188 ----------------------------------------------
1189 -- Writing of history entries and event log --
1190 ----------------------------------------------
1193 CREATE FUNCTION "write_member_history_trigger"()
1194 RETURNS TRIGGER
1195 LANGUAGE 'plpgsql' VOLATILE AS $$
1196 BEGIN
1197 IF
1198 ( NEW."active" != OLD."active" OR
1199 NEW."name" != OLD."name" ) AND
1200 OLD."activated" NOTNULL
1201 THEN
1202 INSERT INTO "member_history"
1203 ("member_id", "active", "name")
1204 VALUES (NEW."id", OLD."active", OLD."name");
1205 END IF;
1206 RETURN NULL;
1207 END;
1208 $$;
1210 CREATE TRIGGER "write_member_history"
1211 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1212 "write_member_history_trigger"();
1214 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1215 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1218 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1219 RETURNS TRIGGER
1220 LANGUAGE 'plpgsql' VOLATILE AS $$
1221 BEGIN
1222 IF NEW."state" != OLD."state" THEN
1223 INSERT INTO "event" ("event", "issue_id", "state")
1224 VALUES ('issue_state_changed', NEW."id", NEW."state");
1225 END IF;
1226 RETURN NULL;
1227 END;
1228 $$;
1230 CREATE TRIGGER "write_event_issue_state_changed"
1231 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1232 "write_event_issue_state_changed_trigger"();
1234 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1235 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1238 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1239 RETURNS TRIGGER
1240 LANGUAGE 'plpgsql' VOLATILE AS $$
1241 DECLARE
1242 "initiative_row" "initiative"%ROWTYPE;
1243 "issue_row" "issue"%ROWTYPE;
1244 "event_v" "event_type";
1245 BEGIN
1246 SELECT * INTO "initiative_row" FROM "initiative"
1247 WHERE "id" = NEW."initiative_id";
1248 SELECT * INTO "issue_row" FROM "issue"
1249 WHERE "id" = "initiative_row"."issue_id";
1250 IF EXISTS (
1251 SELECT NULL FROM "draft"
1252 WHERE "initiative_id" = NEW."initiative_id"
1253 AND "id" != NEW."id"
1254 ) THEN
1255 "event_v" := 'new_draft_created';
1256 ELSE
1257 IF EXISTS (
1258 SELECT NULL FROM "initiative"
1259 WHERE "issue_id" = "initiative_row"."issue_id"
1260 AND "id" != "initiative_row"."id"
1261 ) THEN
1262 "event_v" := 'initiative_created_in_existing_issue';
1263 ELSE
1264 "event_v" := 'initiative_created_in_new_issue';
1265 END IF;
1266 END IF;
1267 INSERT INTO "event" (
1268 "event", "member_id",
1269 "issue_id", "state", "initiative_id", "draft_id"
1270 ) VALUES (
1271 "event_v",
1272 NEW."author_id",
1273 "initiative_row"."issue_id",
1274 "issue_row"."state",
1275 "initiative_row"."id",
1276 NEW."id" );
1277 RETURN NULL;
1278 END;
1279 $$;
1281 CREATE TRIGGER "write_event_initiative_or_draft_created"
1282 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1283 "write_event_initiative_or_draft_created_trigger"();
1285 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1286 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1289 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1290 RETURNS TRIGGER
1291 LANGUAGE 'plpgsql' VOLATILE AS $$
1292 DECLARE
1293 "issue_row" "issue"%ROWTYPE;
1294 "draft_id_v" "draft"."id"%TYPE;
1295 BEGIN
1296 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1297 SELECT * INTO "issue_row" FROM "issue"
1298 WHERE "id" = NEW."issue_id";
1299 SELECT "id" INTO "draft_id_v" FROM "current_draft"
1300 WHERE "initiative_id" = NEW."id";
1301 INSERT INTO "event" (
1302 "event", "member_id", "issue_id", "state", "initiative_id", "draft_id"
1303 ) VALUES (
1304 'initiative_revoked',
1305 NEW."revoked_by_member_id",
1306 NEW."issue_id",
1307 "issue_row"."state",
1308 NEW."id",
1309 "draft_id_v");
1310 END IF;
1311 RETURN NULL;
1312 END;
1313 $$;
1315 CREATE TRIGGER "write_event_initiative_revoked"
1316 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1317 "write_event_initiative_revoked_trigger"();
1319 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1320 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1323 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1324 RETURNS TRIGGER
1325 LANGUAGE 'plpgsql' VOLATILE AS $$
1326 DECLARE
1327 "initiative_row" "initiative"%ROWTYPE;
1328 "issue_row" "issue"%ROWTYPE;
1329 BEGIN
1330 SELECT * INTO "initiative_row" FROM "initiative"
1331 WHERE "id" = NEW."initiative_id";
1332 SELECT * INTO "issue_row" FROM "issue"
1333 WHERE "id" = "initiative_row"."issue_id";
1334 INSERT INTO "event" (
1335 "event", "member_id",
1336 "issue_id", "state", "initiative_id", "suggestion_id"
1337 ) VALUES (
1338 'suggestion_created',
1339 NEW."author_id",
1340 "initiative_row"."issue_id",
1341 "issue_row"."state",
1342 "initiative_row"."id",
1343 NEW."id" );
1344 RETURN NULL;
1345 END;
1346 $$;
1348 CREATE TRIGGER "write_event_suggestion_created"
1349 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1350 "write_event_suggestion_created_trigger"();
1352 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1353 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1357 ----------------------------
1358 -- Additional constraints --
1359 ----------------------------
1362 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1363 RETURNS TRIGGER
1364 LANGUAGE 'plpgsql' VOLATILE AS $$
1365 BEGIN
1366 IF NOT EXISTS (
1367 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1368 ) THEN
1369 --RAISE 'Cannot create issue without an initial initiative.' USING
1370 -- ERRCODE = 'integrity_constraint_violation',
1371 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
1372 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
1373 END IF;
1374 RETURN NULL;
1375 END;
1376 $$;
1378 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1379 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1380 FOR EACH ROW EXECUTE PROCEDURE
1381 "issue_requires_first_initiative_trigger"();
1383 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1384 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1387 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1388 RETURNS TRIGGER
1389 LANGUAGE 'plpgsql' VOLATILE AS $$
1390 DECLARE
1391 "reference_lost" BOOLEAN;
1392 BEGIN
1393 IF TG_OP = 'DELETE' THEN
1394 "reference_lost" := TRUE;
1395 ELSE
1396 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1397 END IF;
1398 IF
1399 "reference_lost" AND NOT EXISTS (
1400 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1401 )
1402 THEN
1403 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1404 END IF;
1405 RETURN NULL;
1406 END;
1407 $$;
1409 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1410 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1411 FOR EACH ROW EXECUTE PROCEDURE
1412 "last_initiative_deletes_issue_trigger"();
1414 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1415 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1418 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1419 RETURNS TRIGGER
1420 LANGUAGE 'plpgsql' VOLATILE AS $$
1421 BEGIN
1422 IF NOT EXISTS (
1423 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1424 ) THEN
1425 --RAISE 'Cannot create initiative without an initial draft.' USING
1426 -- ERRCODE = 'integrity_constraint_violation',
1427 -- HINT = 'Create issue, initiative and draft within the same transaction.';
1428 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
1429 END IF;
1430 RETURN NULL;
1431 END;
1432 $$;
1434 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1435 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1436 FOR EACH ROW EXECUTE PROCEDURE
1437 "initiative_requires_first_draft_trigger"();
1439 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1440 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1443 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1444 RETURNS TRIGGER
1445 LANGUAGE 'plpgsql' VOLATILE AS $$
1446 DECLARE
1447 "reference_lost" BOOLEAN;
1448 BEGIN
1449 IF TG_OP = 'DELETE' THEN
1450 "reference_lost" := TRUE;
1451 ELSE
1452 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1453 END IF;
1454 IF
1455 "reference_lost" AND NOT EXISTS (
1456 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1457 )
1458 THEN
1459 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1460 END IF;
1461 RETURN NULL;
1462 END;
1463 $$;
1465 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1466 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1467 FOR EACH ROW EXECUTE PROCEDURE
1468 "last_draft_deletes_initiative_trigger"();
1470 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1471 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1474 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1475 RETURNS TRIGGER
1476 LANGUAGE 'plpgsql' VOLATILE AS $$
1477 BEGIN
1478 IF NOT EXISTS (
1479 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1480 ) THEN
1481 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
1482 END IF;
1483 RETURN NULL;
1484 END;
1485 $$;
1487 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1488 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1489 FOR EACH ROW EXECUTE PROCEDURE
1490 "suggestion_requires_first_opinion_trigger"();
1492 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1493 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1496 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1497 RETURNS TRIGGER
1498 LANGUAGE 'plpgsql' VOLATILE AS $$
1499 DECLARE
1500 "reference_lost" BOOLEAN;
1501 BEGIN
1502 IF TG_OP = 'DELETE' THEN
1503 "reference_lost" := TRUE;
1504 ELSE
1505 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1506 END IF;
1507 IF
1508 "reference_lost" AND NOT EXISTS (
1509 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1510 )
1511 THEN
1512 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1513 END IF;
1514 RETURN NULL;
1515 END;
1516 $$;
1518 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1519 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1520 FOR EACH ROW EXECUTE PROCEDURE
1521 "last_opinion_deletes_suggestion_trigger"();
1523 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1524 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1527 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()
1528 RETURNS TRIGGER
1529 LANGUAGE 'plpgsql' VOLATILE AS $$
1530 BEGIN
1531 DELETE FROM "direct_voter"
1532 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1533 RETURN NULL;
1534 END;
1535 $$;
1537 CREATE TRIGGER "non_voter_deletes_direct_voter"
1538 AFTER INSERT OR UPDATE ON "non_voter"
1539 FOR EACH ROW EXECUTE PROCEDURE
1540 "non_voter_deletes_direct_voter_trigger"();
1542 COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';
1543 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")';
1546 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()
1547 RETURNS TRIGGER
1548 LANGUAGE 'plpgsql' VOLATILE AS $$
1549 BEGIN
1550 DELETE FROM "non_voter"
1551 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1552 RETURN NULL;
1553 END;
1554 $$;
1556 CREATE TRIGGER "direct_voter_deletes_non_voter"
1557 AFTER INSERT OR UPDATE ON "direct_voter"
1558 FOR EACH ROW EXECUTE PROCEDURE
1559 "direct_voter_deletes_non_voter_trigger"();
1561 COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';
1562 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")';
1565 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()
1566 RETURNS TRIGGER
1567 LANGUAGE 'plpgsql' VOLATILE AS $$
1568 BEGIN
1569 IF NEW."comment" ISNULL THEN
1570 NEW."comment_changed" := NULL;
1571 NEW."formatting_engine" := NULL;
1572 END IF;
1573 RETURN NEW;
1574 END;
1575 $$;
1577 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"
1578 BEFORE INSERT OR UPDATE ON "direct_voter"
1579 FOR EACH ROW EXECUTE PROCEDURE
1580 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();
1582 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"';
1583 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.';
1586 ---------------------------------------------------------------
1587 -- Ensure that votes are not modified when issues are closed --
1588 ---------------------------------------------------------------
1590 -- NOTE: Frontends should ensure this anyway, but in case of programming
1591 -- errors the following triggers ensure data integrity.
1594 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1595 RETURNS TRIGGER
1596 LANGUAGE 'plpgsql' VOLATILE AS $$
1597 DECLARE
1598 "issue_id_v" "issue"."id"%TYPE;
1599 "issue_row" "issue"%ROWTYPE;
1600 BEGIN
1601 IF EXISTS (
1602 SELECT NULL FROM "temporary_transaction_data"
1603 WHERE "txid" = txid_current()
1604 AND "key" = 'override_protection_triggers'
1605 AND "value" = TRUE::TEXT
1606 ) THEN
1607 RETURN NULL;
1608 END IF;
1609 IF TG_OP = 'DELETE' THEN
1610 "issue_id_v" := OLD."issue_id";
1611 ELSE
1612 "issue_id_v" := NEW."issue_id";
1613 END IF;
1614 SELECT INTO "issue_row" * FROM "issue"
1615 WHERE "id" = "issue_id_v" FOR SHARE;
1616 IF (
1617 "issue_row"."closed" NOTNULL OR (
1618 "issue_row"."state" = 'voting' AND
1619 "issue_row"."phase_finished" NOTNULL
1620 )
1621 ) THEN
1622 IF
1623 TG_RELID = 'direct_voter'::regclass AND
1624 TG_OP = 'UPDATE'
1625 THEN
1626 IF
1627 OLD."issue_id" = NEW."issue_id" AND
1628 OLD."member_id" = NEW."member_id" AND
1629 OLD."weight" = NEW."weight"
1630 THEN
1631 RETURN NULL; -- allows changing of voter comment
1632 END IF;
1633 END IF;
1634 RAISE EXCEPTION 'Tried to modify data after voting has been closed.';
1635 END IF;
1636 RETURN NULL;
1637 END;
1638 $$;
1640 CREATE TRIGGER "forbid_changes_on_closed_issue"
1641 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1642 FOR EACH ROW EXECUTE PROCEDURE
1643 "forbid_changes_on_closed_issue_trigger"();
1645 CREATE TRIGGER "forbid_changes_on_closed_issue"
1646 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1647 FOR EACH ROW EXECUTE PROCEDURE
1648 "forbid_changes_on_closed_issue_trigger"();
1650 CREATE TRIGGER "forbid_changes_on_closed_issue"
1651 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1652 FOR EACH ROW EXECUTE PROCEDURE
1653 "forbid_changes_on_closed_issue_trigger"();
1655 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"';
1656 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';
1657 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';
1658 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';
1662 --------------------------------------------------------------------
1663 -- Auto-retrieval of fields only needed for referential integrity --
1664 --------------------------------------------------------------------
1667 CREATE FUNCTION "autofill_issue_id_trigger"()
1668 RETURNS TRIGGER
1669 LANGUAGE 'plpgsql' VOLATILE AS $$
1670 BEGIN
1671 IF NEW."issue_id" ISNULL THEN
1672 SELECT "issue_id" INTO NEW."issue_id"
1673 FROM "initiative" WHERE "id" = NEW."initiative_id";
1674 END IF;
1675 RETURN NEW;
1676 END;
1677 $$;
1679 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1680 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1682 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1683 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1685 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1686 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1687 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1690 CREATE FUNCTION "autofill_initiative_id_trigger"()
1691 RETURNS TRIGGER
1692 LANGUAGE 'plpgsql' VOLATILE AS $$
1693 BEGIN
1694 IF NEW."initiative_id" ISNULL THEN
1695 SELECT "initiative_id" INTO NEW."initiative_id"
1696 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1697 END IF;
1698 RETURN NEW;
1699 END;
1700 $$;
1702 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1703 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1705 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1706 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1710 -----------------------------------------------------
1711 -- Automatic calculation of certain default values --
1712 -----------------------------------------------------
1715 CREATE FUNCTION "copy_timings_trigger"()
1716 RETURNS TRIGGER
1717 LANGUAGE 'plpgsql' VOLATILE AS $$
1718 DECLARE
1719 "policy_row" "policy"%ROWTYPE;
1720 BEGIN
1721 SELECT * INTO "policy_row" FROM "policy"
1722 WHERE "id" = NEW."policy_id";
1723 IF NEW."admission_time" ISNULL THEN
1724 NEW."admission_time" := "policy_row"."admission_time";
1725 END IF;
1726 IF NEW."discussion_time" ISNULL THEN
1727 NEW."discussion_time" := "policy_row"."discussion_time";
1728 END IF;
1729 IF NEW."verification_time" ISNULL THEN
1730 NEW."verification_time" := "policy_row"."verification_time";
1731 END IF;
1732 IF NEW."voting_time" ISNULL THEN
1733 NEW."voting_time" := "policy_row"."voting_time";
1734 END IF;
1735 RETURN NEW;
1736 END;
1737 $$;
1739 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1740 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1742 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1743 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1746 CREATE FUNCTION "default_for_draft_id_trigger"()
1747 RETURNS TRIGGER
1748 LANGUAGE 'plpgsql' VOLATILE AS $$
1749 BEGIN
1750 IF NEW."draft_id" ISNULL THEN
1751 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1752 WHERE "initiative_id" = NEW."initiative_id";
1753 END IF;
1754 RETURN NEW;
1755 END;
1756 $$;
1758 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
1759 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1760 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1761 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1763 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
1764 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';
1765 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';
1769 ----------------------------------------
1770 -- Automatic creation of dependencies --
1771 ----------------------------------------
1774 CREATE FUNCTION "autocreate_interest_trigger"()
1775 RETURNS TRIGGER
1776 LANGUAGE 'plpgsql' VOLATILE AS $$
1777 BEGIN
1778 IF NOT EXISTS (
1779 SELECT NULL FROM "initiative" JOIN "interest"
1780 ON "initiative"."issue_id" = "interest"."issue_id"
1781 WHERE "initiative"."id" = NEW."initiative_id"
1782 AND "interest"."member_id" = NEW."member_id"
1783 ) THEN
1784 BEGIN
1785 INSERT INTO "interest" ("issue_id", "member_id")
1786 SELECT "issue_id", NEW."member_id"
1787 FROM "initiative" WHERE "id" = NEW."initiative_id";
1788 EXCEPTION WHEN unique_violation THEN END;
1789 END IF;
1790 RETURN NEW;
1791 END;
1792 $$;
1794 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1795 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1797 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1798 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';
1801 CREATE FUNCTION "autocreate_supporter_trigger"()
1802 RETURNS TRIGGER
1803 LANGUAGE 'plpgsql' VOLATILE AS $$
1804 BEGIN
1805 IF NOT EXISTS (
1806 SELECT NULL FROM "suggestion" JOIN "supporter"
1807 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1808 WHERE "suggestion"."id" = NEW."suggestion_id"
1809 AND "supporter"."member_id" = NEW."member_id"
1810 ) THEN
1811 BEGIN
1812 INSERT INTO "supporter" ("initiative_id", "member_id")
1813 SELECT "initiative_id", NEW."member_id"
1814 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1815 EXCEPTION WHEN unique_violation THEN END;
1816 END IF;
1817 RETURN NEW;
1818 END;
1819 $$;
1821 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1822 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1824 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1825 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.';
1829 ------------------------------------------
1830 -- Views and helper functions for views --
1831 ------------------------------------------
1834 CREATE VIEW "unit_delegation" AS
1835 SELECT
1836 "unit"."id" AS "unit_id",
1837 "delegation"."id",
1838 "delegation"."truster_id",
1839 "delegation"."trustee_id",
1840 "delegation"."scope"
1841 FROM "unit"
1842 JOIN "delegation"
1843 ON "delegation"."unit_id" = "unit"."id"
1844 JOIN "member"
1845 ON "delegation"."truster_id" = "member"."id"
1846 JOIN "privilege"
1847 ON "delegation"."unit_id" = "privilege"."unit_id"
1848 AND "delegation"."truster_id" = "privilege"."member_id"
1849 WHERE "member"."active" AND "privilege"."voting_right";
1851 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1854 CREATE VIEW "area_delegation" AS
1855 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1856 "area"."id" AS "area_id",
1857 "delegation"."id",
1858 "delegation"."truster_id",
1859 "delegation"."trustee_id",
1860 "delegation"."scope"
1861 FROM "area"
1862 JOIN "delegation"
1863 ON "delegation"."unit_id" = "area"."unit_id"
1864 OR "delegation"."area_id" = "area"."id"
1865 JOIN "member"
1866 ON "delegation"."truster_id" = "member"."id"
1867 JOIN "privilege"
1868 ON "area"."unit_id" = "privilege"."unit_id"
1869 AND "delegation"."truster_id" = "privilege"."member_id"
1870 WHERE "member"."active" AND "privilege"."voting_right"
1871 ORDER BY
1872 "area"."id",
1873 "delegation"."truster_id",
1874 "delegation"."scope" DESC;
1876 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1879 CREATE VIEW "issue_delegation" AS
1880 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1881 "issue"."id" AS "issue_id",
1882 "delegation"."id",
1883 "delegation"."truster_id",
1884 "delegation"."trustee_id",
1885 "delegation"."scope"
1886 FROM "issue"
1887 JOIN "area"
1888 ON "area"."id" = "issue"."area_id"
1889 JOIN "delegation"
1890 ON "delegation"."unit_id" = "area"."unit_id"
1891 OR "delegation"."area_id" = "area"."id"
1892 OR "delegation"."issue_id" = "issue"."id"
1893 JOIN "member"
1894 ON "delegation"."truster_id" = "member"."id"
1895 JOIN "privilege"
1896 ON "area"."unit_id" = "privilege"."unit_id"
1897 AND "delegation"."truster_id" = "privilege"."member_id"
1898 WHERE "member"."active" AND "privilege"."voting_right"
1899 ORDER BY
1900 "issue"."id",
1901 "delegation"."truster_id",
1902 "delegation"."scope" DESC;
1904 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
1907 CREATE FUNCTION "membership_weight_with_skipping"
1908 ( "area_id_p" "area"."id"%TYPE,
1909 "member_id_p" "member"."id"%TYPE,
1910 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1911 RETURNS INT4
1912 LANGUAGE 'plpgsql' STABLE AS $$
1913 DECLARE
1914 "sum_v" INT4;
1915 "delegation_row" "area_delegation"%ROWTYPE;
1916 BEGIN
1917 "sum_v" := 1;
1918 FOR "delegation_row" IN
1919 SELECT "area_delegation".*
1920 FROM "area_delegation" LEFT JOIN "membership"
1921 ON "membership"."area_id" = "area_id_p"
1922 AND "membership"."member_id" = "area_delegation"."truster_id"
1923 WHERE "area_delegation"."area_id" = "area_id_p"
1924 AND "area_delegation"."trustee_id" = "member_id_p"
1925 AND "membership"."member_id" ISNULL
1926 LOOP
1927 IF NOT
1928 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1929 THEN
1930 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1931 "area_id_p",
1932 "delegation_row"."truster_id",
1933 "skip_member_ids_p" || "delegation_row"."truster_id"
1934 );
1935 END IF;
1936 END LOOP;
1937 RETURN "sum_v";
1938 END;
1939 $$;
1941 COMMENT ON FUNCTION "membership_weight_with_skipping"
1942 ( "area"."id"%TYPE,
1943 "member"."id"%TYPE,
1944 INT4[] )
1945 IS 'Helper function for "membership_weight" function';
1948 CREATE FUNCTION "membership_weight"
1949 ( "area_id_p" "area"."id"%TYPE,
1950 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1951 RETURNS INT4
1952 LANGUAGE 'plpgsql' STABLE AS $$
1953 BEGIN
1954 RETURN "membership_weight_with_skipping"(
1955 "area_id_p",
1956 "member_id_p",
1957 ARRAY["member_id_p"]
1958 );
1959 END;
1960 $$;
1962 COMMENT ON FUNCTION "membership_weight"
1963 ( "area"."id"%TYPE,
1964 "member"."id"%TYPE )
1965 IS 'Calculates the potential voting weight of a member in a given area';
1968 CREATE VIEW "member_count_view" AS
1969 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1971 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1974 CREATE VIEW "unit_member_count" AS
1975 SELECT
1976 "unit"."id" AS "unit_id",
1977 count("member"."id") AS "member_count"
1978 FROM "unit"
1979 LEFT JOIN "privilege"
1980 ON "privilege"."unit_id" = "unit"."id"
1981 AND "privilege"."voting_right"
1982 LEFT JOIN "member"
1983 ON "member"."id" = "privilege"."member_id"
1984 AND "member"."active"
1985 GROUP BY "unit"."id";
1987 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
1990 CREATE VIEW "area_member_count" AS
1991 SELECT
1992 "area"."id" AS "area_id",
1993 count("member"."id") AS "direct_member_count",
1994 coalesce(
1995 sum(
1996 CASE WHEN "member"."id" NOTNULL THEN
1997 "membership_weight"("area"."id", "member"."id")
1998 ELSE 0 END
1999 )
2000 ) AS "member_weight"
2001 FROM "area"
2002 LEFT JOIN "membership"
2003 ON "area"."id" = "membership"."area_id"
2004 LEFT JOIN "privilege"
2005 ON "privilege"."unit_id" = "area"."unit_id"
2006 AND "privilege"."member_id" = "membership"."member_id"
2007 AND "privilege"."voting_right"
2008 LEFT JOIN "member"
2009 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
2010 AND "member"."active"
2011 GROUP BY "area"."id";
2013 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';
2016 CREATE VIEW "opening_draft" AS
2017 SELECT "draft".* FROM (
2018 SELECT
2019 "initiative"."id" AS "initiative_id",
2020 min("draft"."id") AS "draft_id"
2021 FROM "initiative" JOIN "draft"
2022 ON "initiative"."id" = "draft"."initiative_id"
2023 GROUP BY "initiative"."id"
2024 ) AS "subquery"
2025 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
2027 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
2030 CREATE VIEW "current_draft" AS
2031 SELECT "draft".* FROM (
2032 SELECT
2033 "initiative"."id" AS "initiative_id",
2034 max("draft"."id") AS "draft_id"
2035 FROM "initiative" JOIN "draft"
2036 ON "initiative"."id" = "draft"."initiative_id"
2037 GROUP BY "initiative"."id"
2038 ) AS "subquery"
2039 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
2041 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
2044 CREATE VIEW "critical_opinion" AS
2045 SELECT * FROM "opinion"
2046 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
2047 OR ("degree" = -2 AND "fulfilled" = TRUE);
2049 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
2052 CREATE VIEW "initiative_suggestion_order_calculation" AS
2053 SELECT
2054 "initiative"."id" AS "initiative_id",
2055 ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final"
2056 FROM "initiative" JOIN "issue"
2057 ON "initiative"."issue_id" = "issue"."id"
2058 WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL)
2059 OR ("initiative"."final_suggestion_order_calculated" = FALSE);
2061 COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';
2063 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';
2066 CREATE VIEW "individual_suggestion_ranking" AS
2067 SELECT
2068 "opinion"."initiative_id",
2069 "opinion"."member_id",
2070 "direct_interest_snapshot"."weight",
2071 CASE WHEN
2072 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
2073 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
2074 THEN 1 ELSE
2075 CASE WHEN
2076 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
2077 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
2078 THEN 2 ELSE
2079 CASE WHEN
2080 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
2081 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
2082 THEN 3 ELSE 4 END
2083 END
2084 END AS "preference",
2085 "opinion"."suggestion_id"
2086 FROM "opinion"
2087 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
2088 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2089 JOIN "direct_interest_snapshot"
2090 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
2091 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
2092 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
2094 COMMENT ON VIEW "individual_suggestion_ranking" IS 'Helper view for "lf_update_suggestion_order" to allow a proportional ordering of suggestions within an initiative';
2097 CREATE VIEW "battle_participant" AS
2098 SELECT "initiative"."id", "initiative"."issue_id"
2099 FROM "issue" JOIN "initiative"
2100 ON "issue"."id" = "initiative"."issue_id"
2101 WHERE "initiative"."admitted"
2102 UNION ALL
2103 SELECT NULL, "id" AS "issue_id"
2104 FROM "issue";
2106 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
2109 CREATE VIEW "battle_view" AS
2110 SELECT
2111 "issue"."id" AS "issue_id",
2112 "winning_initiative"."id" AS "winning_initiative_id",
2113 "losing_initiative"."id" AS "losing_initiative_id",
2114 sum(
2115 CASE WHEN
2116 coalesce("better_vote"."grade", 0) >
2117 coalesce("worse_vote"."grade", 0)
2118 THEN "direct_voter"."weight" ELSE 0 END
2119 ) AS "count"
2120 FROM "issue"
2121 LEFT JOIN "direct_voter"
2122 ON "issue"."id" = "direct_voter"."issue_id"
2123 JOIN "battle_participant" AS "winning_initiative"
2124 ON "issue"."id" = "winning_initiative"."issue_id"
2125 JOIN "battle_participant" AS "losing_initiative"
2126 ON "issue"."id" = "losing_initiative"."issue_id"
2127 LEFT JOIN "vote" AS "better_vote"
2128 ON "direct_voter"."member_id" = "better_vote"."member_id"
2129 AND "winning_initiative"."id" = "better_vote"."initiative_id"
2130 LEFT JOIN "vote" AS "worse_vote"
2131 ON "direct_voter"."member_id" = "worse_vote"."member_id"
2132 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
2133 WHERE "issue"."state" = 'voting'
2134 AND "issue"."phase_finished" NOTNULL
2135 AND (
2136 "winning_initiative"."id" != "losing_initiative"."id" OR
2137 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
2138 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
2139 GROUP BY
2140 "issue"."id",
2141 "winning_initiative"."id",
2142 "losing_initiative"."id";
2144 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';
2147 CREATE VIEW "expired_session" AS
2148 SELECT * FROM "session" WHERE now() > "expiry";
2150 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
2151 DELETE FROM "session" WHERE "ident" = OLD."ident";
2153 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
2154 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
2157 CREATE VIEW "open_issue" AS
2158 SELECT * FROM "issue" WHERE "closed" ISNULL;
2160 COMMENT ON VIEW "open_issue" IS 'All open issues';
2163 CREATE VIEW "member_contingent" AS
2164 SELECT
2165 "member"."id" AS "member_id",
2166 "contingent"."polling",
2167 "contingent"."time_frame",
2168 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
2169 (
2170 SELECT count(1) FROM "draft"
2171 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
2172 WHERE "draft"."author_id" = "member"."id"
2173 AND "initiative"."polling" = "contingent"."polling"
2174 AND "draft"."created" > now() - "contingent"."time_frame"
2175 ) + (
2176 SELECT count(1) FROM "suggestion"
2177 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
2178 WHERE "suggestion"."author_id" = "member"."id"
2179 AND "contingent"."polling" = FALSE
2180 AND "suggestion"."created" > now() - "contingent"."time_frame"
2181 )
2182 ELSE NULL END AS "text_entry_count",
2183 "contingent"."text_entry_limit",
2184 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
2185 SELECT count(1) FROM "opening_draft" AS "draft"
2186 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
2187 WHERE "draft"."author_id" = "member"."id"
2188 AND "initiative"."polling" = "contingent"."polling"
2189 AND "draft"."created" > now() - "contingent"."time_frame"
2190 ) ELSE NULL END AS "initiative_count",
2191 "contingent"."initiative_limit"
2192 FROM "member" CROSS JOIN "contingent";
2194 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
2196 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
2197 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
2200 CREATE VIEW "member_contingent_left" AS
2201 SELECT
2202 "member_id",
2203 "polling",
2204 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
2205 max("initiative_limit" - "initiative_count") AS "initiatives_left"
2206 FROM "member_contingent" GROUP BY "member_id", "polling";
2208 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.';
2211 CREATE VIEW "event_seen_by_member" AS
2212 SELECT
2213 "member"."id" AS "seen_by_member_id",
2214 CASE WHEN "event"."state" IN (
2215 'voting',
2216 'finished_without_winner',
2217 'finished_with_winner'
2218 ) THEN
2219 'voting'::"notify_level"
2220 ELSE
2221 CASE WHEN "event"."state" IN (
2222 'verification',
2223 'canceled_after_revocation_during_verification',
2224 'canceled_no_initiative_admitted'
2225 ) THEN
2226 'verification'::"notify_level"
2227 ELSE
2228 CASE WHEN "event"."state" IN (
2229 'discussion',
2230 'canceled_after_revocation_during_discussion'
2231 ) THEN
2232 'discussion'::"notify_level"
2233 ELSE
2234 'all'::"notify_level"
2235 END
2236 END
2237 END AS "notify_level",
2238 "event".*
2239 FROM "member" CROSS JOIN "event"
2240 LEFT JOIN "issue"
2241 ON "event"."issue_id" = "issue"."id"
2242 LEFT JOIN "membership"
2243 ON "member"."id" = "membership"."member_id"
2244 AND "issue"."area_id" = "membership"."area_id"
2245 LEFT JOIN "interest"
2246 ON "member"."id" = "interest"."member_id"
2247 AND "event"."issue_id" = "interest"."issue_id"
2248 LEFT JOIN "supporter"
2249 ON "member"."id" = "supporter"."member_id"
2250 AND "event"."initiative_id" = "supporter"."initiative_id"
2251 LEFT JOIN "ignored_member"
2252 ON "member"."id" = "ignored_member"."member_id"
2253 AND "event"."member_id" = "ignored_member"."other_member_id"
2254 LEFT JOIN "ignored_initiative"
2255 ON "member"."id" = "ignored_initiative"."member_id"
2256 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2257 WHERE (
2258 "supporter"."member_id" NOTNULL OR
2259 "interest"."member_id" NOTNULL OR
2260 ( "membership"."member_id" NOTNULL AND
2261 "event"."event" IN (
2262 'issue_state_changed',
2263 'initiative_created_in_new_issue',
2264 'initiative_created_in_existing_issue',
2265 'initiative_revoked' ) ) )
2266 AND "ignored_member"."member_id" ISNULL
2267 AND "ignored_initiative"."member_id" ISNULL;
2269 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"';
2272 CREATE VIEW "selected_event_seen_by_member" AS
2273 SELECT
2274 "member"."id" AS "seen_by_member_id",
2275 CASE WHEN "event"."state" IN (
2276 'voting',
2277 'finished_without_winner',
2278 'finished_with_winner'
2279 ) THEN
2280 'voting'::"notify_level"
2281 ELSE
2282 CASE WHEN "event"."state" IN (
2283 'verification',
2284 'canceled_after_revocation_during_verification',
2285 'canceled_no_initiative_admitted'
2286 ) THEN
2287 'verification'::"notify_level"
2288 ELSE
2289 CASE WHEN "event"."state" IN (
2290 'discussion',
2291 'canceled_after_revocation_during_discussion'
2292 ) THEN
2293 'discussion'::"notify_level"
2294 ELSE
2295 'all'::"notify_level"
2296 END
2297 END
2298 END AS "notify_level",
2299 "event".*
2300 FROM "member" CROSS JOIN "event"
2301 LEFT JOIN "issue"
2302 ON "event"."issue_id" = "issue"."id"
2303 LEFT JOIN "membership"
2304 ON "member"."id" = "membership"."member_id"
2305 AND "issue"."area_id" = "membership"."area_id"
2306 LEFT JOIN "interest"
2307 ON "member"."id" = "interest"."member_id"
2308 AND "event"."issue_id" = "interest"."issue_id"
2309 LEFT JOIN "supporter"
2310 ON "member"."id" = "supporter"."member_id"
2311 AND "event"."initiative_id" = "supporter"."initiative_id"
2312 LEFT JOIN "ignored_member"
2313 ON "member"."id" = "ignored_member"."member_id"
2314 AND "event"."member_id" = "ignored_member"."other_member_id"
2315 LEFT JOIN "ignored_initiative"
2316 ON "member"."id" = "ignored_initiative"."member_id"
2317 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2318 WHERE (
2319 ( "member"."notify_level" >= 'all' ) OR
2320 ( "member"."notify_level" >= 'voting' AND
2321 "event"."state" IN (
2322 'voting',
2323 'finished_without_winner',
2324 'finished_with_winner' ) ) OR
2325 ( "member"."notify_level" >= 'verification' AND
2326 "event"."state" IN (
2327 'verification',
2328 'canceled_after_revocation_during_verification',
2329 'canceled_no_initiative_admitted' ) ) OR
2330 ( "member"."notify_level" >= 'discussion' AND
2331 "event"."state" IN (
2332 'discussion',
2333 'canceled_after_revocation_during_discussion' ) ) )
2334 AND (
2335 "supporter"."member_id" NOTNULL OR
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 "login" = NULL,
4395 "password" = NULL,
4396 "locked" = TRUE,
4397 "active" = FALSE,
4398 "notify_email" = NULL,
4399 "notify_email_unconfirmed" = NULL,
4400 "notify_email_secret" = NULL,
4401 "notify_email_secret_expiry" = NULL,
4402 "notify_email_lock_expiry" = NULL,
4403 "password_reset_secret" = NULL,
4404 "password_reset_secret_expiry" = NULL,
4405 "organizational_unit" = NULL,
4406 "internal_posts" = NULL,
4407 "realname" = NULL,
4408 "birthday" = NULL,
4409 "address" = NULL,
4410 "email" = NULL,
4411 "xmpp_address" = NULL,
4412 "website" = NULL,
4413 "phone" = NULL,
4414 "mobile_phone" = NULL,
4415 "profession" = NULL,
4416 "external_memberships" = NULL,
4417 "external_posts" = NULL,
4418 "statement" = NULL
4419 WHERE "id" = "member_id_p";
4420 -- "text_search_data" is updated by triggers
4421 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
4422 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
4423 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
4424 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
4425 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
4426 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
4427 DELETE FROM "session" WHERE "member_id" = "member_id_p";
4428 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
4429 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
4430 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
4431 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
4432 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
4433 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
4434 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
4435 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
4436 DELETE FROM "direct_voter" USING "issue"
4437 WHERE "direct_voter"."issue_id" = "issue"."id"
4438 AND "issue"."closed" ISNULL
4439 AND "member_id" = "member_id_p";
4440 RETURN;
4441 END;
4442 $$;
4444 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)';
4447 CREATE FUNCTION "delete_private_data"()
4448 RETURNS VOID
4449 LANGUAGE 'plpgsql' VOLATILE AS $$
4450 BEGIN
4451 DELETE FROM "temporary_transaction_data";
4452 DELETE FROM "member" WHERE "activated" ISNULL;
4453 UPDATE "member" SET
4454 "invite_code" = NULL,
4455 "invite_code_expiry" = NULL,
4456 "admin_comment" = NULL,
4457 "last_login" = NULL,
4458 "login" = NULL,
4459 "password" = NULL,
4460 "lang" = NULL,
4461 "notify_email" = NULL,
4462 "notify_email_unconfirmed" = NULL,
4463 "notify_email_secret" = NULL,
4464 "notify_email_secret_expiry" = NULL,
4465 "notify_email_lock_expiry" = NULL,
4466 "notify_level" = NULL,
4467 "password_reset_secret" = NULL,
4468 "password_reset_secret_expiry" = NULL,
4469 "organizational_unit" = NULL,
4470 "internal_posts" = NULL,
4471 "realname" = NULL,
4472 "birthday" = NULL,
4473 "address" = NULL,
4474 "email" = NULL,
4475 "xmpp_address" = NULL,
4476 "website" = NULL,
4477 "phone" = NULL,
4478 "mobile_phone" = NULL,
4479 "profession" = NULL,
4480 "external_memberships" = NULL,
4481 "external_posts" = NULL,
4482 "formatting_engine" = NULL,
4483 "statement" = NULL;
4484 -- "text_search_data" is updated by triggers
4485 DELETE FROM "setting";
4486 DELETE FROM "setting_map";
4487 DELETE FROM "member_relation_setting";
4488 DELETE FROM "member_image";
4489 DELETE FROM "contact";
4490 DELETE FROM "ignored_member";
4491 DELETE FROM "session";
4492 DELETE FROM "area_setting";
4493 DELETE FROM "issue_setting";
4494 DELETE FROM "ignored_initiative";
4495 DELETE FROM "initiative_setting";
4496 DELETE FROM "suggestion_setting";
4497 DELETE FROM "non_voter";
4498 DELETE FROM "direct_voter" USING "issue"
4499 WHERE "direct_voter"."issue_id" = "issue"."id"
4500 AND "issue"."closed" ISNULL;
4501 RETURN;
4502 END;
4503 $$;
4505 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.';
4509 COMMIT;