liquid_feedback_core
view core.sql @ 378:e88d0606891f
Bugfix regarding "proportional_order" of suggestions:
Use NULL values explicitly to be sorted last
(includes new suggestions as well as suggestions without any individual rankings)
Use NULL values explicitly to be sorted last
(includes new suggestions as well as suggestions without any individual rankings)
| author | jbe |
|---|---|
| date | Mon Mar 18 09:36:21 2013 +0100 (2013-03-18) |
| parents | 47965760b1b8 |
| children | 1c991490f075 |
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.1', 2, 2, 1))
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 "system_setting" (
58 "member_ttl" INTERVAL );
59 CREATE UNIQUE INDEX "system_setting_singleton_idx" ON "system_setting" ((1));
61 COMMENT ON TABLE "system_setting" IS 'This table contains only one row with different settings in each column.';
62 COMMENT ON INDEX "system_setting_singleton_idx" IS 'This index ensures that "system_setting" only contains one row maximum.';
64 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.';
67 CREATE TABLE "contingent" (
68 PRIMARY KEY ("polling", "time_frame"),
69 "polling" BOOLEAN,
70 "time_frame" INTERVAL,
71 "text_entry_limit" INT4,
72 "initiative_limit" INT4 );
74 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.';
76 COMMENT ON COLUMN "contingent"."polling" IS 'Determines if settings are for creating initiatives and new drafts of initiatives with "polling" flag set';
77 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';
78 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
81 CREATE TYPE "notify_level" AS ENUM
82 ('none', 'voting', 'verification', 'discussion', 'all');
84 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';
87 CREATE TABLE "member" (
88 "id" SERIAL4 PRIMARY KEY,
89 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
90 "invite_code" TEXT UNIQUE,
91 "invite_code_expiry" TIMESTAMPTZ,
92 "admin_comment" TEXT,
93 "activated" TIMESTAMPTZ,
94 "last_activity" DATE,
95 "last_login" TIMESTAMPTZ,
96 "login" TEXT UNIQUE,
97 "password" TEXT,
98 "locked" BOOLEAN NOT NULL DEFAULT FALSE,
99 "active" BOOLEAN NOT NULL DEFAULT FALSE,
100 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
101 "lang" TEXT,
102 "notify_email" TEXT,
103 "notify_email_unconfirmed" TEXT,
104 "notify_email_secret" TEXT UNIQUE,
105 "notify_email_secret_expiry" TIMESTAMPTZ,
106 "notify_email_lock_expiry" TIMESTAMPTZ,
107 "notify_level" "notify_level",
108 "password_reset_secret" TEXT UNIQUE,
109 "password_reset_secret_expiry" TIMESTAMPTZ,
110 "name" TEXT UNIQUE,
111 "identification" TEXT UNIQUE,
112 "authentication" TEXT,
113 "organizational_unit" TEXT,
114 "internal_posts" TEXT,
115 "realname" TEXT,
116 "birthday" DATE,
117 "address" TEXT,
118 "email" TEXT,
119 "xmpp_address" TEXT,
120 "website" TEXT,
121 "phone" TEXT,
122 "mobile_phone" TEXT,
123 "profession" TEXT,
124 "external_memberships" TEXT,
125 "external_posts" TEXT,
126 "formatting_engine" TEXT,
127 "statement" TEXT,
128 "text_search_data" TSVECTOR,
129 CONSTRAINT "active_requires_activated_and_last_activity"
130 CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)),
131 CONSTRAINT "name_not_null_if_activated"
132 CHECK ("activated" ISNULL OR "name" NOTNULL) );
133 CREATE INDEX "member_active_idx" ON "member" ("active");
134 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
135 CREATE TRIGGER "update_text_search_data"
136 BEFORE INSERT OR UPDATE ON "member"
137 FOR EACH ROW EXECUTE PROCEDURE
138 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
139 "name", "identification", "organizational_unit", "internal_posts",
140 "realname", "external_memberships", "external_posts", "statement" );
142 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
144 COMMENT ON COLUMN "member"."created" IS 'Creation of member record and/or invite code';
145 COMMENT ON COLUMN "member"."invite_code" IS 'Optional invite code, to allow a member to initialize his/her account the first time';
146 COMMENT ON COLUMN "member"."invite_code_expiry" IS 'Expiry data/time for "invite_code"';
147 COMMENT ON COLUMN "member"."admin_comment" IS 'Hidden comment for administrative purposes';
148 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';
149 COMMENT ON COLUMN "member"."last_activity" IS 'Date of last activity of member; required to be set for "active" members';
150 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';
151 COMMENT ON COLUMN "member"."login" IS 'Login name';
152 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
153 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
154 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".';
155 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
156 COMMENT ON COLUMN "member"."lang" IS 'Language code of the preferred language of the member';
157 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
158 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
159 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
160 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
161 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
162 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';
163 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member, may be NULL if account has not been activated yet';
164 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
165 COMMENT ON COLUMN "member"."authentication" IS 'Information about how this member was authenticated';
166 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
167 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
168 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
169 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
170 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
171 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
172 COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"';
173 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his/her profile';
176 -- DEPRECATED API TABLES --
178 CREATE TYPE "application_access_level" AS ENUM
179 ('member', 'full', 'pseudonymous', 'anonymous');
181 COMMENT ON TYPE "application_access_level" IS 'DEPRECATED, WILL BE REMOVED! Access privileges for applications using the API';
184 CREATE TABLE "member_application" (
185 "id" SERIAL8 PRIMARY KEY,
186 UNIQUE ("member_id", "name"),
187 "member_id" INT4 NOT NULL REFERENCES "member" ("id")
188 ON DELETE CASCADE ON UPDATE CASCADE,
189 "name" TEXT NOT NULL,
190 "comment" TEXT,
191 "access_level" "application_access_level" NOT NULL,
192 "key" TEXT NOT NULL UNIQUE,
193 "last_usage" TIMESTAMPTZ );
195 COMMENT ON TABLE "member_application" IS 'DEPRECATED, WILL BE REMOVED! Registered application being allowed to use the API';
197 -- END OF DEPRECARED API TABLES --
200 CREATE TABLE "member_history" (
201 "id" SERIAL8 PRIMARY KEY,
202 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
203 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
204 "active" BOOLEAN NOT NULL,
205 "name" TEXT NOT NULL );
206 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
208 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
210 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
211 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
214 CREATE TABLE "rendered_member_statement" (
215 PRIMARY KEY ("member_id", "format"),
216 "member_id" INT8 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
217 "format" TEXT,
218 "content" TEXT NOT NULL );
220 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)';
223 CREATE TABLE "setting" (
224 PRIMARY KEY ("member_id", "key"),
225 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
226 "key" TEXT NOT NULL,
227 "value" TEXT NOT NULL );
228 CREATE INDEX "setting_key_idx" ON "setting" ("key");
230 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
232 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
235 CREATE TABLE "setting_map" (
236 PRIMARY KEY ("member_id", "key", "subkey"),
237 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
238 "key" TEXT NOT NULL,
239 "subkey" TEXT NOT NULL,
240 "value" TEXT NOT NULL );
241 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
243 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
245 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
246 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
247 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
250 CREATE TABLE "member_relation_setting" (
251 PRIMARY KEY ("member_id", "key", "other_member_id"),
252 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
253 "key" TEXT NOT NULL,
254 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
255 "value" TEXT NOT NULL );
257 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
260 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
262 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
265 CREATE TABLE "member_image" (
266 PRIMARY KEY ("member_id", "image_type", "scaled"),
267 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
268 "image_type" "member_image_type",
269 "scaled" BOOLEAN,
270 "content_type" TEXT,
271 "data" BYTEA NOT NULL );
273 COMMENT ON TABLE "member_image" IS 'Images of members';
275 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
278 CREATE TABLE "member_count" (
279 "calculated" TIMESTAMPTZ NOT NULL DEFAULT now(),
280 "total_count" INT4 NOT NULL );
282 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';
284 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
285 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
288 CREATE TABLE "contact" (
289 PRIMARY KEY ("member_id", "other_member_id"),
290 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
291 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
292 "public" BOOLEAN NOT NULL DEFAULT FALSE,
293 CONSTRAINT "cant_save_yourself_as_contact"
294 CHECK ("member_id" != "other_member_id") );
295 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
297 COMMENT ON TABLE "contact" IS 'Contact lists';
299 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
300 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
301 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
304 CREATE TABLE "ignored_member" (
305 PRIMARY KEY ("member_id", "other_member_id"),
306 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
307 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
308 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
310 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
312 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';
313 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
316 CREATE TABLE "session" (
317 "ident" TEXT PRIMARY KEY,
318 "additional_secret" TEXT,
319 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
320 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
321 "lang" TEXT );
322 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
324 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
326 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
327 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
328 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
329 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
332 CREATE TABLE "policy" (
333 "id" SERIAL4 PRIMARY KEY,
334 "index" INT4 NOT NULL,
335 "active" BOOLEAN NOT NULL DEFAULT TRUE,
336 "name" TEXT NOT NULL UNIQUE,
337 "description" TEXT NOT NULL DEFAULT '',
338 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
339 "admission_time" INTERVAL,
340 "discussion_time" INTERVAL,
341 "verification_time" INTERVAL,
342 "voting_time" INTERVAL,
343 "issue_quorum_num" INT4,
344 "issue_quorum_den" INT4,
345 "initiative_quorum_num" INT4 NOT NULL,
346 "initiative_quorum_den" INT4 NOT NULL,
347 "direct_majority_num" INT4 NOT NULL DEFAULT 1,
348 "direct_majority_den" INT4 NOT NULL DEFAULT 2,
349 "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
350 "direct_majority_positive" INT4 NOT NULL DEFAULT 0,
351 "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0,
352 "indirect_majority_num" INT4 NOT NULL DEFAULT 1,
353 "indirect_majority_den" INT4 NOT NULL DEFAULT 2,
354 "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
355 "indirect_majority_positive" INT4 NOT NULL DEFAULT 0,
356 "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0,
357 "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT TRUE,
358 "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE,
359 CONSTRAINT "timing" CHECK (
360 ( "polling" = FALSE AND
361 "admission_time" NOTNULL AND "discussion_time" NOTNULL AND
362 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
363 ( "polling" = TRUE AND
364 "admission_time" ISNULL AND "discussion_time" NOTNULL AND
365 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
366 ( "polling" = TRUE AND
367 "admission_time" ISNULL AND "discussion_time" ISNULL AND
368 "verification_time" ISNULL AND "voting_time" ISNULL ) ),
369 CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK (
370 "polling" = "issue_quorum_num" ISNULL AND
371 "polling" = "issue_quorum_den" ISNULL ) );
372 CREATE INDEX "policy_active_idx" ON "policy" ("active");
374 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
376 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
377 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
378 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';
379 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';
380 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"';
381 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"';
382 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'')';
383 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''';
384 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''';
385 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
386 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
387 COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner';
388 COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner';
389 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.';
390 COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner';
391 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';
392 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';
393 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';
394 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.';
395 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';
396 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';
397 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.';
398 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").';
401 CREATE TABLE "unit" (
402 "id" SERIAL4 PRIMARY KEY,
403 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
404 "active" BOOLEAN NOT NULL DEFAULT TRUE,
405 "name" TEXT NOT NULL,
406 "description" TEXT NOT NULL DEFAULT '',
407 "member_count" INT4,
408 "text_search_data" TSVECTOR );
409 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
410 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
411 CREATE INDEX "unit_active_idx" ON "unit" ("active");
412 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
413 CREATE TRIGGER "update_text_search_data"
414 BEFORE INSERT OR UPDATE ON "unit"
415 FOR EACH ROW EXECUTE PROCEDURE
416 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
417 "name", "description" );
419 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
421 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
422 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this unit';
423 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
426 CREATE TABLE "unit_setting" (
427 PRIMARY KEY ("member_id", "key", "unit_id"),
428 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
429 "key" TEXT NOT NULL,
430 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
431 "value" TEXT NOT NULL );
433 COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings';
436 CREATE TABLE "area" (
437 "id" SERIAL4 PRIMARY KEY,
438 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
439 "active" BOOLEAN NOT NULL DEFAULT TRUE,
440 "name" TEXT NOT NULL,
441 "description" TEXT NOT NULL DEFAULT '',
442 "direct_member_count" INT4,
443 "member_weight" INT4,
444 "text_search_data" TSVECTOR );
445 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
446 CREATE INDEX "area_active_idx" ON "area" ("active");
447 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
448 CREATE TRIGGER "update_text_search_data"
449 BEFORE INSERT OR UPDATE ON "area"
450 FOR EACH ROW EXECUTE PROCEDURE
451 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
452 "name", "description" );
454 COMMENT ON TABLE "area" IS 'Subject areas';
456 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
457 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"';
458 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
461 CREATE TABLE "area_setting" (
462 PRIMARY KEY ("member_id", "key", "area_id"),
463 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
464 "key" TEXT NOT NULL,
465 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
466 "value" TEXT NOT NULL );
468 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
471 CREATE TABLE "allowed_policy" (
472 PRIMARY KEY ("area_id", "policy_id"),
473 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
474 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
475 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
476 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
478 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
480 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
483 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
485 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';
488 CREATE TYPE "issue_state" AS ENUM (
489 'admission', 'discussion', 'verification', 'voting',
490 'canceled_revoked_before_accepted',
491 'canceled_issue_not_accepted',
492 'canceled_after_revocation_during_discussion',
493 'canceled_after_revocation_during_verification',
494 'canceled_no_initiative_admitted',
495 'finished_without_winner', 'finished_with_winner');
497 COMMENT ON TYPE "issue_state" IS 'State of issues';
500 CREATE TABLE "issue" (
501 "id" SERIAL4 PRIMARY KEY,
502 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
503 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
504 "state" "issue_state" NOT NULL DEFAULT 'admission',
505 "phase_finished" TIMESTAMPTZ,
506 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
507 "accepted" TIMESTAMPTZ,
508 "half_frozen" TIMESTAMPTZ,
509 "fully_frozen" TIMESTAMPTZ,
510 "closed" TIMESTAMPTZ,
511 "cleaned" TIMESTAMPTZ,
512 "admission_time" INTERVAL,
513 "discussion_time" INTERVAL NOT NULL,
514 "verification_time" INTERVAL NOT NULL,
515 "voting_time" INTERVAL NOT NULL,
516 "snapshot" TIMESTAMPTZ,
517 "latest_snapshot_event" "snapshot_event",
518 "population" INT4,
519 "voter_count" INT4,
520 "status_quo_schulze_rank" INT4,
521 CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (
522 "admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created") ),
523 CONSTRAINT "valid_state" CHECK (
524 (
525 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
526 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
527 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL ) OR
528 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL)
529 ) AND (
530 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
531 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
532 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
533 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
534 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
535 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
536 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
537 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
538 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" = "fully_frozen") OR
539 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen") OR
540 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen")
541 )),
542 CONSTRAINT "phase_finished_only_when_not_closed" CHECK (
543 "phase_finished" ISNULL OR "closed" ISNULL ),
544 CONSTRAINT "state_change_order" CHECK (
545 "created" <= "accepted" AND
546 "accepted" <= "half_frozen" AND
547 "half_frozen" <= "fully_frozen" AND
548 "fully_frozen" <= "closed" ),
549 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
550 "cleaned" ISNULL OR "closed" NOTNULL ),
551 CONSTRAINT "last_snapshot_on_full_freeze"
552 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
553 CONSTRAINT "freeze_requires_snapshot"
554 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
555 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
556 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
557 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
558 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
559 CREATE INDEX "issue_created_idx" ON "issue" ("created");
560 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
561 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
562 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
563 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
564 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
565 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
567 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
569 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';
570 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
571 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.';
572 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.';
573 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.';
574 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
575 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
576 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
577 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
578 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
579 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
580 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';
581 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
582 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';
583 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
586 CREATE TABLE "issue_setting" (
587 PRIMARY KEY ("member_id", "key", "issue_id"),
588 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
589 "key" TEXT NOT NULL,
590 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
591 "value" TEXT NOT NULL );
593 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
596 CREATE TABLE "initiative" (
597 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
598 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
599 "id" SERIAL4 PRIMARY KEY,
600 "name" TEXT NOT NULL,
601 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
602 "discussion_url" TEXT,
603 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
604 "revoked" TIMESTAMPTZ,
605 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
606 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
607 "admitted" BOOLEAN,
608 "supporter_count" INT4,
609 "informed_supporter_count" INT4,
610 "satisfied_supporter_count" INT4,
611 "satisfied_informed_supporter_count" INT4,
612 "harmonic_weight" NUMERIC(12, 3),
613 "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE,
614 "positive_votes" INT4,
615 "negative_votes" INT4,
616 "direct_majority" BOOLEAN,
617 "indirect_majority" BOOLEAN,
618 "schulze_rank" INT4,
619 "better_than_status_quo" BOOLEAN,
620 "worse_than_status_quo" BOOLEAN,
621 "reverse_beat_path" BOOLEAN,
622 "multistage_majority" BOOLEAN,
623 "eligible" BOOLEAN,
624 "winner" BOOLEAN,
625 "rank" INT4,
626 "text_search_data" TSVECTOR,
627 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
628 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
629 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
630 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
631 CONSTRAINT "revoked_initiatives_cant_be_admitted"
632 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
633 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
634 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
635 ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND
636 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
637 "schulze_rank" ISNULL AND
638 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
639 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
640 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ),
641 CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")),
642 CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (
643 "eligible" = FALSE OR
644 ("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ),
645 CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE),
646 CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1),
647 CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE),
648 CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") );
649 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
650 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
651 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
652 CREATE TRIGGER "update_text_search_data"
653 BEFORE INSERT OR UPDATE ON "initiative"
654 FOR EACH ROW EXECUTE PROCEDURE
655 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
656 "name", "discussion_url");
658 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.';
660 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative does not need to pass the initiative quorum (see "policy"."polling")';
661 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
662 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
663 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoke the initiative';
664 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
665 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
666 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
667 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
668 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
669 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';
670 COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time';
671 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
672 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
673 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"';
674 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths';
675 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking';
676 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)';
677 COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';
678 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';
679 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';
680 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"';
681 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"';
682 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';
685 CREATE TABLE "battle" (
686 "issue_id" INT4 NOT NULL,
687 "winning_initiative_id" INT4,
688 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
689 "losing_initiative_id" INT4,
690 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
691 "count" INT4 NOT NULL,
692 CONSTRAINT "initiative_ids_not_equal" CHECK (
693 "winning_initiative_id" != "losing_initiative_id" OR
694 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
695 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) );
696 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
697 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
698 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
700 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';
703 CREATE TABLE "ignored_initiative" (
704 PRIMARY KEY ("initiative_id", "member_id"),
705 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
706 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
707 CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
709 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
712 CREATE TABLE "initiative_setting" (
713 PRIMARY KEY ("member_id", "key", "initiative_id"),
714 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
715 "key" TEXT NOT NULL,
716 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
717 "value" TEXT NOT NULL );
719 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
722 CREATE TABLE "draft" (
723 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
724 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
725 "id" SERIAL8 PRIMARY KEY,
726 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
727 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
728 "formatting_engine" TEXT,
729 "content" TEXT NOT NULL,
730 "text_search_data" TSVECTOR );
731 CREATE INDEX "draft_created_idx" ON "draft" ("created");
732 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
733 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
734 CREATE TRIGGER "update_text_search_data"
735 BEFORE INSERT OR UPDATE ON "draft"
736 FOR EACH ROW EXECUTE PROCEDURE
737 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
739 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.';
741 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
742 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
745 CREATE TABLE "rendered_draft" (
746 PRIMARY KEY ("draft_id", "format"),
747 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
748 "format" TEXT,
749 "content" TEXT NOT NULL );
751 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)';
754 CREATE TABLE "suggestion" (
755 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
756 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
757 "id" SERIAL8 PRIMARY KEY,
758 "draft_id" INT8 NOT NULL,
759 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
760 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
761 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
762 "name" TEXT NOT NULL,
763 "formatting_engine" TEXT,
764 "content" TEXT NOT NULL DEFAULT '',
765 "text_search_data" TSVECTOR,
766 "minus2_unfulfilled_count" INT4,
767 "minus2_fulfilled_count" INT4,
768 "minus1_unfulfilled_count" INT4,
769 "minus1_fulfilled_count" INT4,
770 "plus1_unfulfilled_count" INT4,
771 "plus1_fulfilled_count" INT4,
772 "plus2_unfulfilled_count" INT4,
773 "plus2_fulfilled_count" INT4,
774 "proportional_order" INT4 );
775 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
776 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
777 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
778 CREATE TRIGGER "update_text_search_data"
779 BEFORE INSERT OR UPDATE ON "suggestion"
780 FOR EACH ROW EXECUTE PROCEDURE
781 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
782 "name", "content");
784 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';
786 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")';
787 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
788 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
789 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
790 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
791 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
792 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
793 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
794 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
795 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"';
798 CREATE TABLE "rendered_suggestion" (
799 PRIMARY KEY ("suggestion_id", "format"),
800 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
801 "format" TEXT,
802 "content" TEXT NOT NULL );
804 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)';
807 CREATE TABLE "suggestion_setting" (
808 PRIMARY KEY ("member_id", "key", "suggestion_id"),
809 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
810 "key" TEXT NOT NULL,
811 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
812 "value" TEXT NOT NULL );
814 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
817 CREATE TABLE "privilege" (
818 PRIMARY KEY ("unit_id", "member_id"),
819 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
820 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
821 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
822 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
823 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
824 "member_manager" BOOLEAN NOT NULL DEFAULT FALSE,
825 "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE,
826 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE,
827 "polling_right" BOOLEAN NOT NULL DEFAULT FALSE );
829 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
831 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke any privileges to/from other members';
832 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create and disable sub units';
833 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create and disable areas and set area parameters';
834 COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';
835 COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';
836 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote';
837 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';
840 CREATE TABLE "membership" (
841 PRIMARY KEY ("area_id", "member_id"),
842 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
843 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
844 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
846 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
849 CREATE TABLE "interest" (
850 PRIMARY KEY ("issue_id", "member_id"),
851 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
852 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
853 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
855 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.';
858 CREATE TABLE "initiator" (
859 PRIMARY KEY ("initiative_id", "member_id"),
860 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
861 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
862 "accepted" BOOLEAN );
863 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
865 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.';
867 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.';
870 CREATE TABLE "supporter" (
871 "issue_id" INT4 NOT NULL,
872 PRIMARY KEY ("initiative_id", "member_id"),
873 "initiative_id" INT4,
874 "member_id" INT4,
875 "draft_id" INT8 NOT NULL,
876 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
877 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE );
878 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
880 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.';
882 COMMENT ON COLUMN "supporter"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
883 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")';
886 CREATE TABLE "opinion" (
887 "initiative_id" INT4 NOT NULL,
888 PRIMARY KEY ("suggestion_id", "member_id"),
889 "suggestion_id" INT8,
890 "member_id" INT4,
891 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
892 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
893 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
894 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
895 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
897 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.';
899 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
902 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
904 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
907 CREATE TABLE "delegation" (
908 "id" SERIAL8 PRIMARY KEY,
909 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
910 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
911 "scope" "delegation_scope" NOT NULL,
912 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
913 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
914 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
915 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
916 CONSTRAINT "no_unit_delegation_to_null"
917 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
918 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
919 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
920 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
921 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
922 UNIQUE ("unit_id", "truster_id"),
923 UNIQUE ("area_id", "truster_id"),
924 UNIQUE ("issue_id", "truster_id") );
925 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
926 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
928 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
930 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
931 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
932 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
935 CREATE TABLE "direct_population_snapshot" (
936 PRIMARY KEY ("issue_id", "event", "member_id"),
937 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
938 "event" "snapshot_event",
939 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
940 "weight" INT4 );
941 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
943 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
945 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
946 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
949 CREATE TABLE "delegating_population_snapshot" (
950 PRIMARY KEY ("issue_id", "event", "member_id"),
951 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
952 "event" "snapshot_event",
953 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
954 "weight" INT4,
955 "scope" "delegation_scope" NOT NULL,
956 "delegate_member_ids" INT4[] NOT NULL );
957 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
959 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
961 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
962 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
963 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
964 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"';
967 CREATE TABLE "direct_interest_snapshot" (
968 PRIMARY KEY ("issue_id", "event", "member_id"),
969 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
970 "event" "snapshot_event",
971 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
972 "weight" INT4 );
973 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
975 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
977 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
978 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
981 CREATE TABLE "delegating_interest_snapshot" (
982 PRIMARY KEY ("issue_id", "event", "member_id"),
983 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
984 "event" "snapshot_event",
985 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
986 "weight" INT4,
987 "scope" "delegation_scope" NOT NULL,
988 "delegate_member_ids" INT4[] NOT NULL );
989 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
991 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
993 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
994 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
995 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
996 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"';
999 CREATE TABLE "direct_supporter_snapshot" (
1000 "issue_id" INT4 NOT NULL,
1001 PRIMARY KEY ("initiative_id", "event", "member_id"),
1002 "initiative_id" INT4,
1003 "event" "snapshot_event",
1004 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1005 "draft_id" INT8 NOT NULL,
1006 "informed" BOOLEAN NOT NULL,
1007 "satisfied" BOOLEAN NOT NULL,
1008 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1009 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
1010 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1011 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
1013 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
1015 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';
1016 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1017 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
1018 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
1021 CREATE TABLE "non_voter" (
1022 PRIMARY KEY ("issue_id", "member_id"),
1023 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1024 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
1025 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
1027 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
1030 CREATE TABLE "direct_voter" (
1031 PRIMARY KEY ("issue_id", "member_id"),
1032 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1033 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1034 "weight" INT4,
1035 "comment_changed" TIMESTAMPTZ,
1036 "formatting_engine" TEXT,
1037 "comment" TEXT,
1038 "text_search_data" TSVECTOR );
1039 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
1040 CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data");
1041 CREATE TRIGGER "update_text_search_data"
1042 BEFORE INSERT OR UPDATE ON "direct_voter"
1043 FOR EACH ROW EXECUTE PROCEDURE
1044 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment");
1046 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.';
1048 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
1049 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';
1050 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';
1051 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.';
1054 CREATE TABLE "rendered_voter_comment" (
1055 PRIMARY KEY ("issue_id", "member_id", "format"),
1056 FOREIGN KEY ("issue_id", "member_id")
1057 REFERENCES "direct_voter" ("issue_id", "member_id")
1058 ON DELETE CASCADE ON UPDATE CASCADE,
1059 "issue_id" INT4,
1060 "member_id" INT4,
1061 "format" TEXT,
1062 "content" TEXT NOT NULL );
1064 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)';
1067 CREATE TABLE "delegating_voter" (
1068 PRIMARY KEY ("issue_id", "member_id"),
1069 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1070 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1071 "weight" INT4,
1072 "scope" "delegation_scope" NOT NULL,
1073 "delegate_member_ids" INT4[] NOT NULL );
1074 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
1076 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
1078 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
1079 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
1080 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"';
1083 CREATE TABLE "vote" (
1084 "issue_id" INT4 NOT NULL,
1085 PRIMARY KEY ("initiative_id", "member_id"),
1086 "initiative_id" INT4,
1087 "member_id" INT4,
1088 "grade" INT4,
1089 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1090 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1091 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
1093 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.';
1095 COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
1096 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.';
1099 CREATE TYPE "event_type" AS ENUM (
1100 'issue_state_changed',
1101 'initiative_created_in_new_issue',
1102 'initiative_created_in_existing_issue',
1103 'initiative_revoked',
1104 'new_draft_created',
1105 'suggestion_created');
1107 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
1110 CREATE TABLE "event" (
1111 "id" SERIAL8 PRIMARY KEY,
1112 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
1113 "event" "event_type" NOT NULL,
1114 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1115 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1116 "state" "issue_state",
1117 "initiative_id" INT4,
1118 "draft_id" INT8,
1119 "suggestion_id" INT8,
1120 FOREIGN KEY ("issue_id", "initiative_id")
1121 REFERENCES "initiative" ("issue_id", "id")
1122 ON DELETE CASCADE ON UPDATE CASCADE,
1123 FOREIGN KEY ("initiative_id", "draft_id")
1124 REFERENCES "draft" ("initiative_id", "id")
1125 ON DELETE CASCADE ON UPDATE CASCADE,
1126 FOREIGN KEY ("initiative_id", "suggestion_id")
1127 REFERENCES "suggestion" ("initiative_id", "id")
1128 ON DELETE CASCADE ON UPDATE CASCADE,
1129 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
1130 "event" != 'issue_state_changed' OR (
1131 "member_id" ISNULL AND
1132 "issue_id" NOTNULL AND
1133 "state" NOTNULL AND
1134 "initiative_id" ISNULL AND
1135 "draft_id" ISNULL AND
1136 "suggestion_id" ISNULL )),
1137 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
1138 "event" NOT IN (
1139 'initiative_created_in_new_issue',
1140 'initiative_created_in_existing_issue',
1141 'initiative_revoked',
1142 'new_draft_created'
1143 ) OR (
1144 "member_id" NOTNULL AND
1145 "issue_id" NOTNULL AND
1146 "state" NOTNULL AND
1147 "initiative_id" NOTNULL AND
1148 "draft_id" NOTNULL AND
1149 "suggestion_id" ISNULL )),
1150 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
1151 "event" != 'suggestion_created' OR (
1152 "member_id" NOTNULL AND
1153 "issue_id" NOTNULL AND
1154 "state" NOTNULL AND
1155 "initiative_id" NOTNULL AND
1156 "draft_id" ISNULL AND
1157 "suggestion_id" NOTNULL )) );
1158 CREATE INDEX "event_occurrence_idx" ON "event" ("occurrence");
1160 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
1162 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
1163 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
1164 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
1165 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
1168 CREATE TABLE "notification_sent" (
1169 "event_id" INT8 NOT NULL );
1170 CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1));
1172 COMMENT ON TABLE "notification_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';
1173 COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';
1177 ----------------------------------------------
1178 -- Writing of history entries and event log --
1179 ----------------------------------------------
1182 CREATE FUNCTION "write_member_history_trigger"()
1183 RETURNS TRIGGER
1184 LANGUAGE 'plpgsql' VOLATILE AS $$
1185 BEGIN
1186 IF
1187 ( NEW."active" != OLD."active" OR
1188 NEW."name" != OLD."name" ) AND
1189 OLD."activated" NOTNULL
1190 THEN
1191 INSERT INTO "member_history"
1192 ("member_id", "active", "name")
1193 VALUES (NEW."id", OLD."active", OLD."name");
1194 END IF;
1195 RETURN NULL;
1196 END;
1197 $$;
1199 CREATE TRIGGER "write_member_history"
1200 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1201 "write_member_history_trigger"();
1203 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1204 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1207 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1208 RETURNS TRIGGER
1209 LANGUAGE 'plpgsql' VOLATILE AS $$
1210 BEGIN
1211 IF NEW."state" != OLD."state" THEN
1212 INSERT INTO "event" ("event", "issue_id", "state")
1213 VALUES ('issue_state_changed', NEW."id", NEW."state");
1214 END IF;
1215 RETURN NULL;
1216 END;
1217 $$;
1219 CREATE TRIGGER "write_event_issue_state_changed"
1220 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1221 "write_event_issue_state_changed_trigger"();
1223 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1224 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1227 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1228 RETURNS TRIGGER
1229 LANGUAGE 'plpgsql' VOLATILE AS $$
1230 DECLARE
1231 "initiative_row" "initiative"%ROWTYPE;
1232 "issue_row" "issue"%ROWTYPE;
1233 "event_v" "event_type";
1234 BEGIN
1235 SELECT * INTO "initiative_row" FROM "initiative"
1236 WHERE "id" = NEW."initiative_id";
1237 SELECT * INTO "issue_row" FROM "issue"
1238 WHERE "id" = "initiative_row"."issue_id";
1239 IF EXISTS (
1240 SELECT NULL FROM "draft"
1241 WHERE "initiative_id" = NEW."initiative_id"
1242 AND "id" != NEW."id"
1243 ) THEN
1244 "event_v" := 'new_draft_created';
1245 ELSE
1246 IF EXISTS (
1247 SELECT NULL FROM "initiative"
1248 WHERE "issue_id" = "initiative_row"."issue_id"
1249 AND "id" != "initiative_row"."id"
1250 ) THEN
1251 "event_v" := 'initiative_created_in_existing_issue';
1252 ELSE
1253 "event_v" := 'initiative_created_in_new_issue';
1254 END IF;
1255 END IF;
1256 INSERT INTO "event" (
1257 "event", "member_id",
1258 "issue_id", "state", "initiative_id", "draft_id"
1259 ) VALUES (
1260 "event_v",
1261 NEW."author_id",
1262 "initiative_row"."issue_id",
1263 "issue_row"."state",
1264 "initiative_row"."id",
1265 NEW."id" );
1266 RETURN NULL;
1267 END;
1268 $$;
1270 CREATE TRIGGER "write_event_initiative_or_draft_created"
1271 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1272 "write_event_initiative_or_draft_created_trigger"();
1274 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1275 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1278 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1279 RETURNS TRIGGER
1280 LANGUAGE 'plpgsql' VOLATILE AS $$
1281 DECLARE
1282 "issue_row" "issue"%ROWTYPE;
1283 "draft_id_v" "draft"."id"%TYPE;
1284 BEGIN
1285 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1286 SELECT * INTO "issue_row" FROM "issue"
1287 WHERE "id" = NEW."issue_id";
1288 SELECT "id" INTO "draft_id_v" FROM "current_draft"
1289 WHERE "initiative_id" = NEW."id";
1290 INSERT INTO "event" (
1291 "event", "member_id", "issue_id", "state", "initiative_id", "draft_id"
1292 ) VALUES (
1293 'initiative_revoked',
1294 NEW."revoked_by_member_id",
1295 NEW."issue_id",
1296 "issue_row"."state",
1297 NEW."id",
1298 "draft_id_v");
1299 END IF;
1300 RETURN NULL;
1301 END;
1302 $$;
1304 CREATE TRIGGER "write_event_initiative_revoked"
1305 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1306 "write_event_initiative_revoked_trigger"();
1308 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1309 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1312 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1313 RETURNS TRIGGER
1314 LANGUAGE 'plpgsql' VOLATILE AS $$
1315 DECLARE
1316 "initiative_row" "initiative"%ROWTYPE;
1317 "issue_row" "issue"%ROWTYPE;
1318 BEGIN
1319 SELECT * INTO "initiative_row" FROM "initiative"
1320 WHERE "id" = NEW."initiative_id";
1321 SELECT * INTO "issue_row" FROM "issue"
1322 WHERE "id" = "initiative_row"."issue_id";
1323 INSERT INTO "event" (
1324 "event", "member_id",
1325 "issue_id", "state", "initiative_id", "suggestion_id"
1326 ) VALUES (
1327 'suggestion_created',
1328 NEW."author_id",
1329 "initiative_row"."issue_id",
1330 "issue_row"."state",
1331 "initiative_row"."id",
1332 NEW."id" );
1333 RETURN NULL;
1334 END;
1335 $$;
1337 CREATE TRIGGER "write_event_suggestion_created"
1338 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1339 "write_event_suggestion_created_trigger"();
1341 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1342 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1346 ----------------------------
1347 -- Additional constraints --
1348 ----------------------------
1351 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1352 RETURNS TRIGGER
1353 LANGUAGE 'plpgsql' VOLATILE AS $$
1354 BEGIN
1355 IF NOT EXISTS (
1356 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1357 ) THEN
1358 --RAISE 'Cannot create issue without an initial initiative.' USING
1359 -- ERRCODE = 'integrity_constraint_violation',
1360 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
1361 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
1362 END IF;
1363 RETURN NULL;
1364 END;
1365 $$;
1367 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1368 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1369 FOR EACH ROW EXECUTE PROCEDURE
1370 "issue_requires_first_initiative_trigger"();
1372 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1373 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1376 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1377 RETURNS TRIGGER
1378 LANGUAGE 'plpgsql' VOLATILE AS $$
1379 DECLARE
1380 "reference_lost" BOOLEAN;
1381 BEGIN
1382 IF TG_OP = 'DELETE' THEN
1383 "reference_lost" := TRUE;
1384 ELSE
1385 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1386 END IF;
1387 IF
1388 "reference_lost" AND NOT EXISTS (
1389 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1390 )
1391 THEN
1392 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1393 END IF;
1394 RETURN NULL;
1395 END;
1396 $$;
1398 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1399 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1400 FOR EACH ROW EXECUTE PROCEDURE
1401 "last_initiative_deletes_issue_trigger"();
1403 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1404 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1407 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1408 RETURNS TRIGGER
1409 LANGUAGE 'plpgsql' VOLATILE AS $$
1410 BEGIN
1411 IF NOT EXISTS (
1412 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1413 ) THEN
1414 --RAISE 'Cannot create initiative without an initial draft.' USING
1415 -- ERRCODE = 'integrity_constraint_violation',
1416 -- HINT = 'Create issue, initiative and draft within the same transaction.';
1417 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
1418 END IF;
1419 RETURN NULL;
1420 END;
1421 $$;
1423 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1424 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1425 FOR EACH ROW EXECUTE PROCEDURE
1426 "initiative_requires_first_draft_trigger"();
1428 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1429 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1432 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1433 RETURNS TRIGGER
1434 LANGUAGE 'plpgsql' VOLATILE AS $$
1435 DECLARE
1436 "reference_lost" BOOLEAN;
1437 BEGIN
1438 IF TG_OP = 'DELETE' THEN
1439 "reference_lost" := TRUE;
1440 ELSE
1441 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1442 END IF;
1443 IF
1444 "reference_lost" AND NOT EXISTS (
1445 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1446 )
1447 THEN
1448 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1449 END IF;
1450 RETURN NULL;
1451 END;
1452 $$;
1454 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1455 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1456 FOR EACH ROW EXECUTE PROCEDURE
1457 "last_draft_deletes_initiative_trigger"();
1459 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1460 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1463 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1464 RETURNS TRIGGER
1465 LANGUAGE 'plpgsql' VOLATILE AS $$
1466 BEGIN
1467 IF NOT EXISTS (
1468 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1469 ) THEN
1470 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
1471 END IF;
1472 RETURN NULL;
1473 END;
1474 $$;
1476 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1477 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1478 FOR EACH ROW EXECUTE PROCEDURE
1479 "suggestion_requires_first_opinion_trigger"();
1481 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1482 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1485 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1486 RETURNS TRIGGER
1487 LANGUAGE 'plpgsql' VOLATILE AS $$
1488 DECLARE
1489 "reference_lost" BOOLEAN;
1490 BEGIN
1491 IF TG_OP = 'DELETE' THEN
1492 "reference_lost" := TRUE;
1493 ELSE
1494 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1495 END IF;
1496 IF
1497 "reference_lost" AND NOT EXISTS (
1498 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1499 )
1500 THEN
1501 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1502 END IF;
1503 RETURN NULL;
1504 END;
1505 $$;
1507 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1508 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1509 FOR EACH ROW EXECUTE PROCEDURE
1510 "last_opinion_deletes_suggestion_trigger"();
1512 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1513 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1516 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()
1517 RETURNS TRIGGER
1518 LANGUAGE 'plpgsql' VOLATILE AS $$
1519 BEGIN
1520 DELETE FROM "direct_voter"
1521 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1522 RETURN NULL;
1523 END;
1524 $$;
1526 CREATE TRIGGER "non_voter_deletes_direct_voter"
1527 AFTER INSERT OR UPDATE ON "non_voter"
1528 FOR EACH ROW EXECUTE PROCEDURE
1529 "non_voter_deletes_direct_voter_trigger"();
1531 COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';
1532 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")';
1535 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()
1536 RETURNS TRIGGER
1537 LANGUAGE 'plpgsql' VOLATILE AS $$
1538 BEGIN
1539 DELETE FROM "non_voter"
1540 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1541 RETURN NULL;
1542 END;
1543 $$;
1545 CREATE TRIGGER "direct_voter_deletes_non_voter"
1546 AFTER INSERT OR UPDATE ON "direct_voter"
1547 FOR EACH ROW EXECUTE PROCEDURE
1548 "direct_voter_deletes_non_voter_trigger"();
1550 COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';
1551 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")';
1554 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()
1555 RETURNS TRIGGER
1556 LANGUAGE 'plpgsql' VOLATILE AS $$
1557 BEGIN
1558 IF NEW."comment" ISNULL THEN
1559 NEW."comment_changed" := NULL;
1560 NEW."formatting_engine" := NULL;
1561 END IF;
1562 RETURN NEW;
1563 END;
1564 $$;
1566 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"
1567 BEFORE INSERT OR UPDATE ON "direct_voter"
1568 FOR EACH ROW EXECUTE PROCEDURE
1569 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();
1571 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"';
1572 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.';
1575 ---------------------------------------------------------------
1576 -- Ensure that votes are not modified when issues are closed --
1577 ---------------------------------------------------------------
1579 -- NOTE: Frontends should ensure this anyway, but in case of programming
1580 -- errors the following triggers ensure data integrity.
1583 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1584 RETURNS TRIGGER
1585 LANGUAGE 'plpgsql' VOLATILE AS $$
1586 DECLARE
1587 "issue_id_v" "issue"."id"%TYPE;
1588 "issue_row" "issue"%ROWTYPE;
1589 BEGIN
1590 IF TG_OP = 'DELETE' THEN
1591 "issue_id_v" := OLD."issue_id";
1592 ELSE
1593 "issue_id_v" := NEW."issue_id";
1594 END IF;
1595 SELECT INTO "issue_row" * FROM "issue"
1596 WHERE "id" = "issue_id_v" FOR SHARE;
1597 IF "issue_row"."closed" NOTNULL THEN
1598 IF
1599 TG_RELID = 'direct_voter'::regclass AND
1600 TG_OP = 'UPDATE'
1601 THEN
1602 IF
1603 OLD."issue_id" = NEW."issue_id" AND
1604 OLD."member_id" = NEW."member_id" AND
1605 OLD."weight" = NEW."weight"
1606 THEN
1607 RETURN NULL; -- allows changing of voter comment
1608 END IF;
1609 END IF;
1610 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
1611 ELSIF
1612 "issue_row"."state" = 'voting' AND
1613 "issue_row"."phase_finished" NOTNULL
1614 THEN
1615 IF TG_RELID = 'vote'::regclass THEN
1616 RAISE EXCEPTION 'Tried to modify data after voting has been closed.';
1617 END IF;
1618 END IF;
1619 RETURN NULL;
1620 END;
1621 $$;
1623 CREATE TRIGGER "forbid_changes_on_closed_issue"
1624 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1625 FOR EACH ROW EXECUTE PROCEDURE
1626 "forbid_changes_on_closed_issue_trigger"();
1628 CREATE TRIGGER "forbid_changes_on_closed_issue"
1629 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1630 FOR EACH ROW EXECUTE PROCEDURE
1631 "forbid_changes_on_closed_issue_trigger"();
1633 CREATE TRIGGER "forbid_changes_on_closed_issue"
1634 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1635 FOR EACH ROW EXECUTE PROCEDURE
1636 "forbid_changes_on_closed_issue_trigger"();
1638 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"';
1639 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';
1640 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';
1641 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';
1645 --------------------------------------------------------------------
1646 -- Auto-retrieval of fields only needed for referential integrity --
1647 --------------------------------------------------------------------
1650 CREATE FUNCTION "autofill_issue_id_trigger"()
1651 RETURNS TRIGGER
1652 LANGUAGE 'plpgsql' VOLATILE AS $$
1653 BEGIN
1654 IF NEW."issue_id" ISNULL THEN
1655 SELECT "issue_id" INTO NEW."issue_id"
1656 FROM "initiative" WHERE "id" = NEW."initiative_id";
1657 END IF;
1658 RETURN NEW;
1659 END;
1660 $$;
1662 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1663 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1665 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1666 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1668 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1669 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1670 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1673 CREATE FUNCTION "autofill_initiative_id_trigger"()
1674 RETURNS TRIGGER
1675 LANGUAGE 'plpgsql' VOLATILE AS $$
1676 BEGIN
1677 IF NEW."initiative_id" ISNULL THEN
1678 SELECT "initiative_id" INTO NEW."initiative_id"
1679 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1680 END IF;
1681 RETURN NEW;
1682 END;
1683 $$;
1685 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1686 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1688 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1689 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1693 -----------------------------------------------------
1694 -- Automatic calculation of certain default values --
1695 -----------------------------------------------------
1698 CREATE FUNCTION "copy_timings_trigger"()
1699 RETURNS TRIGGER
1700 LANGUAGE 'plpgsql' VOLATILE AS $$
1701 DECLARE
1702 "policy_row" "policy"%ROWTYPE;
1703 BEGIN
1704 SELECT * INTO "policy_row" FROM "policy"
1705 WHERE "id" = NEW."policy_id";
1706 IF NEW."admission_time" ISNULL THEN
1707 NEW."admission_time" := "policy_row"."admission_time";
1708 END IF;
1709 IF NEW."discussion_time" ISNULL THEN
1710 NEW."discussion_time" := "policy_row"."discussion_time";
1711 END IF;
1712 IF NEW."verification_time" ISNULL THEN
1713 NEW."verification_time" := "policy_row"."verification_time";
1714 END IF;
1715 IF NEW."voting_time" ISNULL THEN
1716 NEW."voting_time" := "policy_row"."voting_time";
1717 END IF;
1718 RETURN NEW;
1719 END;
1720 $$;
1722 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1723 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1725 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1726 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1729 CREATE FUNCTION "default_for_draft_id_trigger"()
1730 RETURNS TRIGGER
1731 LANGUAGE 'plpgsql' VOLATILE AS $$
1732 BEGIN
1733 IF NEW."draft_id" ISNULL THEN
1734 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1735 WHERE "initiative_id" = NEW."initiative_id";
1736 END IF;
1737 RETURN NEW;
1738 END;
1739 $$;
1741 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
1742 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1743 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1744 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1746 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
1747 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';
1748 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';
1752 ----------------------------------------
1753 -- Automatic creation of dependencies --
1754 ----------------------------------------
1757 CREATE FUNCTION "autocreate_interest_trigger"()
1758 RETURNS TRIGGER
1759 LANGUAGE 'plpgsql' VOLATILE AS $$
1760 BEGIN
1761 IF NOT EXISTS (
1762 SELECT NULL FROM "initiative" JOIN "interest"
1763 ON "initiative"."issue_id" = "interest"."issue_id"
1764 WHERE "initiative"."id" = NEW."initiative_id"
1765 AND "interest"."member_id" = NEW."member_id"
1766 ) THEN
1767 BEGIN
1768 INSERT INTO "interest" ("issue_id", "member_id")
1769 SELECT "issue_id", NEW."member_id"
1770 FROM "initiative" WHERE "id" = NEW."initiative_id";
1771 EXCEPTION WHEN unique_violation THEN END;
1772 END IF;
1773 RETURN NEW;
1774 END;
1775 $$;
1777 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1778 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1780 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1781 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';
1784 CREATE FUNCTION "autocreate_supporter_trigger"()
1785 RETURNS TRIGGER
1786 LANGUAGE 'plpgsql' VOLATILE AS $$
1787 BEGIN
1788 IF NOT EXISTS (
1789 SELECT NULL FROM "suggestion" JOIN "supporter"
1790 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1791 WHERE "suggestion"."id" = NEW."suggestion_id"
1792 AND "supporter"."member_id" = NEW."member_id"
1793 ) THEN
1794 BEGIN
1795 INSERT INTO "supporter" ("initiative_id", "member_id")
1796 SELECT "initiative_id", NEW."member_id"
1797 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1798 EXCEPTION WHEN unique_violation THEN END;
1799 END IF;
1800 RETURN NEW;
1801 END;
1802 $$;
1804 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1805 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1807 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1808 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.';
1812 ------------------------------------------
1813 -- Views and helper functions for views --
1814 ------------------------------------------
1817 CREATE VIEW "unit_delegation" AS
1818 SELECT
1819 "unit"."id" AS "unit_id",
1820 "delegation"."id",
1821 "delegation"."truster_id",
1822 "delegation"."trustee_id",
1823 "delegation"."scope"
1824 FROM "unit"
1825 JOIN "delegation"
1826 ON "delegation"."unit_id" = "unit"."id"
1827 JOIN "member"
1828 ON "delegation"."truster_id" = "member"."id"
1829 JOIN "privilege"
1830 ON "delegation"."unit_id" = "privilege"."unit_id"
1831 AND "delegation"."truster_id" = "privilege"."member_id"
1832 WHERE "member"."active" AND "privilege"."voting_right";
1834 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1837 CREATE VIEW "area_delegation" AS
1838 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1839 "area"."id" AS "area_id",
1840 "delegation"."id",
1841 "delegation"."truster_id",
1842 "delegation"."trustee_id",
1843 "delegation"."scope"
1844 FROM "area"
1845 JOIN "delegation"
1846 ON "delegation"."unit_id" = "area"."unit_id"
1847 OR "delegation"."area_id" = "area"."id"
1848 JOIN "member"
1849 ON "delegation"."truster_id" = "member"."id"
1850 JOIN "privilege"
1851 ON "area"."unit_id" = "privilege"."unit_id"
1852 AND "delegation"."truster_id" = "privilege"."member_id"
1853 WHERE "member"."active" AND "privilege"."voting_right"
1854 ORDER BY
1855 "area"."id",
1856 "delegation"."truster_id",
1857 "delegation"."scope" DESC;
1859 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1862 CREATE VIEW "issue_delegation" AS
1863 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1864 "issue"."id" AS "issue_id",
1865 "delegation"."id",
1866 "delegation"."truster_id",
1867 "delegation"."trustee_id",
1868 "delegation"."scope"
1869 FROM "issue"
1870 JOIN "area"
1871 ON "area"."id" = "issue"."area_id"
1872 JOIN "delegation"
1873 ON "delegation"."unit_id" = "area"."unit_id"
1874 OR "delegation"."area_id" = "area"."id"
1875 OR "delegation"."issue_id" = "issue"."id"
1876 JOIN "member"
1877 ON "delegation"."truster_id" = "member"."id"
1878 JOIN "privilege"
1879 ON "area"."unit_id" = "privilege"."unit_id"
1880 AND "delegation"."truster_id" = "privilege"."member_id"
1881 WHERE "member"."active" AND "privilege"."voting_right"
1882 ORDER BY
1883 "issue"."id",
1884 "delegation"."truster_id",
1885 "delegation"."scope" DESC;
1887 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
1890 CREATE FUNCTION "membership_weight_with_skipping"
1891 ( "area_id_p" "area"."id"%TYPE,
1892 "member_id_p" "member"."id"%TYPE,
1893 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1894 RETURNS INT4
1895 LANGUAGE 'plpgsql' STABLE AS $$
1896 DECLARE
1897 "sum_v" INT4;
1898 "delegation_row" "area_delegation"%ROWTYPE;
1899 BEGIN
1900 "sum_v" := 1;
1901 FOR "delegation_row" IN
1902 SELECT "area_delegation".*
1903 FROM "area_delegation" LEFT JOIN "membership"
1904 ON "membership"."area_id" = "area_id_p"
1905 AND "membership"."member_id" = "area_delegation"."truster_id"
1906 WHERE "area_delegation"."area_id" = "area_id_p"
1907 AND "area_delegation"."trustee_id" = "member_id_p"
1908 AND "membership"."member_id" ISNULL
1909 LOOP
1910 IF NOT
1911 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1912 THEN
1913 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1914 "area_id_p",
1915 "delegation_row"."truster_id",
1916 "skip_member_ids_p" || "delegation_row"."truster_id"
1917 );
1918 END IF;
1919 END LOOP;
1920 RETURN "sum_v";
1921 END;
1922 $$;
1924 COMMENT ON FUNCTION "membership_weight_with_skipping"
1925 ( "area"."id"%TYPE,
1926 "member"."id"%TYPE,
1927 INT4[] )
1928 IS 'Helper function for "membership_weight" function';
1931 CREATE FUNCTION "membership_weight"
1932 ( "area_id_p" "area"."id"%TYPE,
1933 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1934 RETURNS INT4
1935 LANGUAGE 'plpgsql' STABLE AS $$
1936 BEGIN
1937 RETURN "membership_weight_with_skipping"(
1938 "area_id_p",
1939 "member_id_p",
1940 ARRAY["member_id_p"]
1941 );
1942 END;
1943 $$;
1945 COMMENT ON FUNCTION "membership_weight"
1946 ( "area"."id"%TYPE,
1947 "member"."id"%TYPE )
1948 IS 'Calculates the potential voting weight of a member in a given area';
1951 CREATE VIEW "member_count_view" AS
1952 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1954 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1957 CREATE VIEW "unit_member_count" AS
1958 SELECT
1959 "unit"."id" AS "unit_id",
1960 count("member"."id") AS "member_count"
1961 FROM "unit"
1962 LEFT JOIN "privilege"
1963 ON "privilege"."unit_id" = "unit"."id"
1964 AND "privilege"."voting_right"
1965 LEFT JOIN "member"
1966 ON "member"."id" = "privilege"."member_id"
1967 AND "member"."active"
1968 GROUP BY "unit"."id";
1970 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
1973 CREATE VIEW "area_member_count" AS
1974 SELECT
1975 "area"."id" AS "area_id",
1976 count("member"."id") AS "direct_member_count",
1977 coalesce(
1978 sum(
1979 CASE WHEN "member"."id" NOTNULL THEN
1980 "membership_weight"("area"."id", "member"."id")
1981 ELSE 0 END
1982 )
1983 ) AS "member_weight"
1984 FROM "area"
1985 LEFT JOIN "membership"
1986 ON "area"."id" = "membership"."area_id"
1987 LEFT JOIN "privilege"
1988 ON "privilege"."unit_id" = "area"."unit_id"
1989 AND "privilege"."member_id" = "membership"."member_id"
1990 AND "privilege"."voting_right"
1991 LEFT JOIN "member"
1992 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
1993 AND "member"."active"
1994 GROUP BY "area"."id";
1996 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';
1999 CREATE VIEW "opening_draft" AS
2000 SELECT "draft".* FROM (
2001 SELECT
2002 "initiative"."id" AS "initiative_id",
2003 min("draft"."id") AS "draft_id"
2004 FROM "initiative" JOIN "draft"
2005 ON "initiative"."id" = "draft"."initiative_id"
2006 GROUP BY "initiative"."id"
2007 ) AS "subquery"
2008 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
2010 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
2013 CREATE VIEW "current_draft" AS
2014 SELECT "draft".* FROM (
2015 SELECT
2016 "initiative"."id" AS "initiative_id",
2017 max("draft"."id") AS "draft_id"
2018 FROM "initiative" JOIN "draft"
2019 ON "initiative"."id" = "draft"."initiative_id"
2020 GROUP BY "initiative"."id"
2021 ) AS "subquery"
2022 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
2024 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
2027 CREATE VIEW "critical_opinion" AS
2028 SELECT * FROM "opinion"
2029 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
2030 OR ("degree" = -2 AND "fulfilled" = TRUE);
2032 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
2035 CREATE VIEW "initiative_suggestion_order_calculation" AS
2036 SELECT
2037 "initiative"."id" AS "initiative_id",
2038 ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final"
2039 FROM "initiative" JOIN "issue"
2040 ON "initiative"."issue_id" = "issue"."id"
2041 WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL)
2042 OR ("initiative"."final_suggestion_order_calculated" = FALSE);
2044 COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';
2046 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';
2049 CREATE VIEW "individual_suggestion_ranking" AS
2050 SELECT
2051 "opinion"."initiative_id",
2052 "opinion"."member_id",
2053 "direct_interest_snapshot"."weight",
2054 CASE WHEN
2055 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
2056 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
2057 THEN 1 ELSE
2058 CASE WHEN
2059 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
2060 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
2061 THEN 2 ELSE
2062 CASE WHEN
2063 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
2064 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
2065 THEN 3 ELSE 4 END
2066 END
2067 END AS "preference",
2068 "opinion"."suggestion_id"
2069 FROM "opinion"
2070 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
2071 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2072 JOIN "direct_interest_snapshot"
2073 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
2074 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
2075 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
2077 COMMENT ON VIEW "individual_suggestion_ranking" IS 'Helper view for "lf_update_suggestion_order" to allow a proportional ordering of suggestions within an initiative';
2080 CREATE VIEW "battle_participant" AS
2081 SELECT "initiative"."id", "initiative"."issue_id"
2082 FROM "issue" JOIN "initiative"
2083 ON "issue"."id" = "initiative"."issue_id"
2084 WHERE "initiative"."admitted"
2085 UNION ALL
2086 SELECT NULL, "id" AS "issue_id"
2087 FROM "issue";
2089 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
2092 CREATE VIEW "battle_view" AS
2093 SELECT
2094 "issue"."id" AS "issue_id",
2095 "winning_initiative"."id" AS "winning_initiative_id",
2096 "losing_initiative"."id" AS "losing_initiative_id",
2097 sum(
2098 CASE WHEN
2099 coalesce("better_vote"."grade", 0) >
2100 coalesce("worse_vote"."grade", 0)
2101 THEN "direct_voter"."weight" ELSE 0 END
2102 ) AS "count"
2103 FROM "issue"
2104 LEFT JOIN "direct_voter"
2105 ON "issue"."id" = "direct_voter"."issue_id"
2106 JOIN "battle_participant" AS "winning_initiative"
2107 ON "issue"."id" = "winning_initiative"."issue_id"
2108 JOIN "battle_participant" AS "losing_initiative"
2109 ON "issue"."id" = "losing_initiative"."issue_id"
2110 LEFT JOIN "vote" AS "better_vote"
2111 ON "direct_voter"."member_id" = "better_vote"."member_id"
2112 AND "winning_initiative"."id" = "better_vote"."initiative_id"
2113 LEFT JOIN "vote" AS "worse_vote"
2114 ON "direct_voter"."member_id" = "worse_vote"."member_id"
2115 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
2116 WHERE "issue"."state" = 'voting'
2117 AND "issue"."phase_finished" NOTNULL
2118 AND (
2119 "winning_initiative"."id" != "losing_initiative"."id" OR
2120 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
2121 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
2122 GROUP BY
2123 "issue"."id",
2124 "winning_initiative"."id",
2125 "losing_initiative"."id";
2127 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';
2130 CREATE VIEW "expired_session" AS
2131 SELECT * FROM "session" WHERE now() > "expiry";
2133 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
2134 DELETE FROM "session" WHERE "ident" = OLD."ident";
2136 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
2137 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
2140 CREATE VIEW "open_issue" AS
2141 SELECT * FROM "issue" WHERE "closed" ISNULL;
2143 COMMENT ON VIEW "open_issue" IS 'All open issues';
2146 CREATE VIEW "member_contingent" AS
2147 SELECT
2148 "member"."id" AS "member_id",
2149 "contingent"."polling",
2150 "contingent"."time_frame",
2151 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
2152 (
2153 SELECT count(1) FROM "draft"
2154 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
2155 WHERE "draft"."author_id" = "member"."id"
2156 AND "initiative"."polling" = "contingent"."polling"
2157 AND "draft"."created" > now() - "contingent"."time_frame"
2158 ) + (
2159 SELECT count(1) FROM "suggestion"
2160 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
2161 WHERE "suggestion"."author_id" = "member"."id"
2162 AND "contingent"."polling" = FALSE
2163 AND "suggestion"."created" > now() - "contingent"."time_frame"
2164 )
2165 ELSE NULL END AS "text_entry_count",
2166 "contingent"."text_entry_limit",
2167 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
2168 SELECT count(1) FROM "opening_draft" AS "draft"
2169 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
2170 WHERE "draft"."author_id" = "member"."id"
2171 AND "initiative"."polling" = "contingent"."polling"
2172 AND "draft"."created" > now() - "contingent"."time_frame"
2173 ) ELSE NULL END AS "initiative_count",
2174 "contingent"."initiative_limit"
2175 FROM "member" CROSS JOIN "contingent";
2177 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
2179 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
2180 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
2183 CREATE VIEW "member_contingent_left" AS
2184 SELECT
2185 "member_id",
2186 "polling",
2187 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
2188 max("initiative_limit" - "initiative_count") AS "initiatives_left"
2189 FROM "member_contingent" GROUP BY "member_id", "polling";
2191 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.';
2194 CREATE VIEW "event_seen_by_member" AS
2195 SELECT
2196 "member"."id" AS "seen_by_member_id",
2197 CASE WHEN "event"."state" IN (
2198 'voting',
2199 'finished_without_winner',
2200 'finished_with_winner'
2201 ) THEN
2202 'voting'::"notify_level"
2203 ELSE
2204 CASE WHEN "event"."state" IN (
2205 'verification',
2206 'canceled_after_revocation_during_verification',
2207 'canceled_no_initiative_admitted'
2208 ) THEN
2209 'verification'::"notify_level"
2210 ELSE
2211 CASE WHEN "event"."state" IN (
2212 'discussion',
2213 'canceled_after_revocation_during_discussion'
2214 ) THEN
2215 'discussion'::"notify_level"
2216 ELSE
2217 'all'::"notify_level"
2218 END
2219 END
2220 END AS "notify_level",
2221 "event".*
2222 FROM "member" CROSS JOIN "event"
2223 LEFT JOIN "issue"
2224 ON "event"."issue_id" = "issue"."id"
2225 LEFT JOIN "membership"
2226 ON "member"."id" = "membership"."member_id"
2227 AND "issue"."area_id" = "membership"."area_id"
2228 LEFT JOIN "interest"
2229 ON "member"."id" = "interest"."member_id"
2230 AND "event"."issue_id" = "interest"."issue_id"
2231 LEFT JOIN "supporter"
2232 ON "member"."id" = "supporter"."member_id"
2233 AND "event"."initiative_id" = "supporter"."initiative_id"
2234 LEFT JOIN "ignored_member"
2235 ON "member"."id" = "ignored_member"."member_id"
2236 AND "event"."member_id" = "ignored_member"."other_member_id"
2237 LEFT JOIN "ignored_initiative"
2238 ON "member"."id" = "ignored_initiative"."member_id"
2239 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2240 WHERE (
2241 "supporter"."member_id" NOTNULL OR
2242 "interest"."member_id" NOTNULL OR
2243 ( "membership"."member_id" NOTNULL AND
2244 "event"."event" IN (
2245 'issue_state_changed',
2246 'initiative_created_in_new_issue',
2247 'initiative_created_in_existing_issue',
2248 'initiative_revoked' ) ) )
2249 AND "ignored_member"."member_id" ISNULL
2250 AND "ignored_initiative"."member_id" ISNULL;
2252 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"';
2255 CREATE VIEW "selected_event_seen_by_member" AS
2256 SELECT
2257 "member"."id" AS "seen_by_member_id",
2258 CASE WHEN "event"."state" IN (
2259 'voting',
2260 'finished_without_winner',
2261 'finished_with_winner'
2262 ) THEN
2263 'voting'::"notify_level"
2264 ELSE
2265 CASE WHEN "event"."state" IN (
2266 'verification',
2267 'canceled_after_revocation_during_verification',
2268 'canceled_no_initiative_admitted'
2269 ) THEN
2270 'verification'::"notify_level"
2271 ELSE
2272 CASE WHEN "event"."state" IN (
2273 'discussion',
2274 'canceled_after_revocation_during_discussion'
2275 ) THEN
2276 'discussion'::"notify_level"
2277 ELSE
2278 'all'::"notify_level"
2279 END
2280 END
2281 END AS "notify_level",
2282 "event".*
2283 FROM "member" CROSS JOIN "event"
2284 LEFT JOIN "issue"
2285 ON "event"."issue_id" = "issue"."id"
2286 LEFT JOIN "membership"
2287 ON "member"."id" = "membership"."member_id"
2288 AND "issue"."area_id" = "membership"."area_id"
2289 LEFT JOIN "interest"
2290 ON "member"."id" = "interest"."member_id"
2291 AND "event"."issue_id" = "interest"."issue_id"
2292 LEFT JOIN "supporter"
2293 ON "member"."id" = "supporter"."member_id"
2294 AND "event"."initiative_id" = "supporter"."initiative_id"
2295 LEFT JOIN "ignored_member"
2296 ON "member"."id" = "ignored_member"."member_id"
2297 AND "event"."member_id" = "ignored_member"."other_member_id"
2298 LEFT JOIN "ignored_initiative"
2299 ON "member"."id" = "ignored_initiative"."member_id"
2300 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2301 WHERE (
2302 ( "member"."notify_level" >= 'all' ) OR
2303 ( "member"."notify_level" >= 'voting' AND
2304 "event"."state" IN (
2305 'voting',
2306 'finished_without_winner',
2307 'finished_with_winner' ) ) OR
2308 ( "member"."notify_level" >= 'verification' AND
2309 "event"."state" IN (
2310 'verification',
2311 'canceled_after_revocation_during_verification',
2312 'canceled_no_initiative_admitted' ) ) OR
2313 ( "member"."notify_level" >= 'discussion' AND
2314 "event"."state" IN (
2315 'discussion',
2316 'canceled_after_revocation_during_discussion' ) ) )
2317 AND (
2318 "supporter"."member_id" NOTNULL OR
2319 "interest"."member_id" NOTNULL OR
2320 ( "membership"."member_id" NOTNULL AND
2321 "event"."event" IN (
2322 'issue_state_changed',
2323 'initiative_created_in_new_issue',
2324 'initiative_created_in_existing_issue',
2325 'initiative_revoked' ) ) )
2326 AND "ignored_member"."member_id" ISNULL
2327 AND "ignored_initiative"."member_id" ISNULL;
2329 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"';
2333 ------------------------------------------------------
2334 -- Row set returning function for delegation chains --
2335 ------------------------------------------------------
2338 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2339 ('first', 'intermediate', 'last', 'repetition');
2341 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2344 CREATE TYPE "delegation_chain_row" AS (
2345 "index" INT4,
2346 "member_id" INT4,
2347 "member_valid" BOOLEAN,
2348 "participation" BOOLEAN,
2349 "overridden" BOOLEAN,
2350 "scope_in" "delegation_scope",
2351 "scope_out" "delegation_scope",
2352 "disabled_out" BOOLEAN,
2353 "loop" "delegation_chain_loop_tag" );
2355 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
2357 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2358 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';
2359 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2360 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2361 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2362 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2363 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2366 CREATE FUNCTION "delegation_chain_for_closed_issue"
2367 ( "member_id_p" "member"."id"%TYPE,
2368 "issue_id_p" "issue"."id"%TYPE )
2369 RETURNS SETOF "delegation_chain_row"
2370 LANGUAGE 'plpgsql' STABLE AS $$
2371 DECLARE
2372 "output_row" "delegation_chain_row";
2373 "direct_voter_row" "direct_voter"%ROWTYPE;
2374 "delegating_voter_row" "delegating_voter"%ROWTYPE;
2375 BEGIN
2376 "output_row"."index" := 0;
2377 "output_row"."member_id" := "member_id_p";
2378 "output_row"."member_valid" := TRUE;
2379 "output_row"."participation" := FALSE;
2380 "output_row"."overridden" := FALSE;
2381 "output_row"."disabled_out" := FALSE;
2382 LOOP
2383 SELECT INTO "direct_voter_row" * FROM "direct_voter"
2384 WHERE "issue_id" = "issue_id_p"
2385 AND "member_id" = "output_row"."member_id";
2386 IF "direct_voter_row"."member_id" NOTNULL THEN
2387 "output_row"."participation" := TRUE;
2388 "output_row"."scope_out" := NULL;
2389 "output_row"."disabled_out" := NULL;
2390 RETURN NEXT "output_row";
2391 RETURN;
2392 END IF;
2393 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
2394 WHERE "issue_id" = "issue_id_p"
2395 AND "member_id" = "output_row"."member_id";
2396 IF "delegating_voter_row"."member_id" ISNULL THEN
2397 RETURN;
2398 END IF;
2399 "output_row"."scope_out" := "delegating_voter_row"."scope";
2400 RETURN NEXT "output_row";
2401 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
2402 "output_row"."scope_in" := "output_row"."scope_out";
2403 END LOOP;
2404 END;
2405 $$;
2407 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
2408 ( "member"."id"%TYPE,
2409 "member"."id"%TYPE )
2410 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
2413 CREATE FUNCTION "delegation_chain"
2414 ( "member_id_p" "member"."id"%TYPE,
2415 "unit_id_p" "unit"."id"%TYPE,
2416 "area_id_p" "area"."id"%TYPE,
2417 "issue_id_p" "issue"."id"%TYPE,
2418 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2419 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2420 RETURNS SETOF "delegation_chain_row"
2421 LANGUAGE 'plpgsql' STABLE AS $$
2422 DECLARE
2423 "scope_v" "delegation_scope";
2424 "unit_id_v" "unit"."id"%TYPE;
2425 "area_id_v" "area"."id"%TYPE;
2426 "issue_row" "issue"%ROWTYPE;
2427 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2428 "loop_member_id_v" "member"."id"%TYPE;
2429 "output_row" "delegation_chain_row";
2430 "output_rows" "delegation_chain_row"[];
2431 "simulate_v" BOOLEAN;
2432 "simulate_here_v" BOOLEAN;
2433 "delegation_row" "delegation"%ROWTYPE;
2434 "row_count" INT4;
2435 "i" INT4;
2436 "loop_v" BOOLEAN;
2437 BEGIN
2438 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
2439 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
2440 END IF;
2441 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
2442 "simulate_v" := TRUE;
2443 ELSE
2444 "simulate_v" := FALSE;
2445 END IF;
2446 IF
2447 "unit_id_p" NOTNULL AND
2448 "area_id_p" ISNULL AND
2449 "issue_id_p" ISNULL
2450 THEN
2451 "scope_v" := 'unit';
2452 "unit_id_v" := "unit_id_p";
2453 ELSIF
2454 "unit_id_p" ISNULL AND
2455 "area_id_p" NOTNULL AND
2456 "issue_id_p" ISNULL
2457 THEN
2458 "scope_v" := 'area';
2459 "area_id_v" := "area_id_p";
2460 SELECT "unit_id" INTO "unit_id_v"
2461 FROM "area" WHERE "id" = "area_id_v";
2462 ELSIF
2463 "unit_id_p" ISNULL AND
2464 "area_id_p" ISNULL AND
2465 "issue_id_p" NOTNULL
2466 THEN
2467 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
2468 IF "issue_row"."id" ISNULL THEN
2469 RETURN;
2470 END IF;
2471 IF "issue_row"."closed" NOTNULL THEN
2472 IF "simulate_v" THEN
2473 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
2474 END IF;
2475 FOR "output_row" IN
2476 SELECT * FROM
2477 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
2478 LOOP
2479 RETURN NEXT "output_row";
2480 END LOOP;
2481 RETURN;
2482 END IF;
2483 "scope_v" := 'issue';
2484 SELECT "area_id" INTO "area_id_v"
2485 FROM "issue" WHERE "id" = "issue_id_p";
2486 SELECT "unit_id" INTO "unit_id_v"
2487 FROM "area" WHERE "id" = "area_id_v";
2488 ELSE
2489 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2490 END IF;
2491 "visited_member_ids" := '{}';
2492 "loop_member_id_v" := NULL;
2493 "output_rows" := '{}';
2494 "output_row"."index" := 0;
2495 "output_row"."member_id" := "member_id_p";
2496 "output_row"."member_valid" := TRUE;
2497 "output_row"."participation" := FALSE;
2498 "output_row"."overridden" := FALSE;
2499 "output_row"."disabled_out" := FALSE;
2500 "output_row"."scope_out" := NULL;
2501 LOOP
2502 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2503 "loop_member_id_v" := "output_row"."member_id";
2504 ELSE
2505 "visited_member_ids" :=
2506 "visited_member_ids" || "output_row"."member_id";
2507 END IF;
2508 IF "output_row"."participation" ISNULL THEN
2509 "output_row"."overridden" := NULL;
2510 ELSIF "output_row"."participation" THEN
2511 "output_row"."overridden" := TRUE;
2512 END IF;
2513 "output_row"."scope_in" := "output_row"."scope_out";
2514 "output_row"."member_valid" := EXISTS (
2515 SELECT NULL FROM "member" JOIN "privilege"
2516 ON "privilege"."member_id" = "member"."id"
2517 AND "privilege"."unit_id" = "unit_id_v"
2518 WHERE "id" = "output_row"."member_id"
2519 AND "member"."active" AND "privilege"."voting_right"
2520 );
2521 "simulate_here_v" := (
2522 "simulate_v" AND
2523 "output_row"."member_id" = "member_id_p"
2524 );
2525 "delegation_row" := ROW(NULL);
2526 IF "output_row"."member_valid" OR "simulate_here_v" THEN
2527 IF "scope_v" = 'unit' THEN
2528 IF NOT "simulate_here_v" THEN
2529 SELECT * INTO "delegation_row" FROM "delegation"
2530 WHERE "truster_id" = "output_row"."member_id"
2531 AND "unit_id" = "unit_id_v";
2532 END IF;
2533 ELSIF "scope_v" = 'area' THEN
2534 "output_row"."participation" := EXISTS (
2535 SELECT NULL FROM "membership"
2536 WHERE "area_id" = "area_id_p"
2537 AND "member_id" = "output_row"."member_id"
2538 );
2539 IF "simulate_here_v" THEN
2540 IF "simulate_trustee_id_p" ISNULL THEN
2541 SELECT * INTO "delegation_row" FROM "delegation"
2542 WHERE "truster_id" = "output_row"."member_id"
2543 AND "unit_id" = "unit_id_v";
2544 END IF;
2545 ELSE
2546 SELECT * INTO "delegation_row" FROM "delegation"
2547 WHERE "truster_id" = "output_row"."member_id"
2548 AND (
2549 "unit_id" = "unit_id_v" OR
2550 "area_id" = "area_id_v"
2551 )
2552 ORDER BY "scope" DESC;
2553 END IF;
2554 ELSIF "scope_v" = 'issue' THEN
2555 IF "issue_row"."fully_frozen" ISNULL THEN
2556 "output_row"."participation" := EXISTS (
2557 SELECT NULL FROM "interest"
2558 WHERE "issue_id" = "issue_id_p"
2559 AND "member_id" = "output_row"."member_id"
2560 );
2561 ELSE
2562 IF "output_row"."member_id" = "member_id_p" THEN
2563 "output_row"."participation" := EXISTS (
2564 SELECT NULL FROM "direct_voter"
2565 WHERE "issue_id" = "issue_id_p"
2566 AND "member_id" = "output_row"."member_id"
2567 );
2568 ELSE
2569 "output_row"."participation" := NULL;
2570 END IF;
2571 END IF;
2572 IF "simulate_here_v" THEN
2573 IF "simulate_trustee_id_p" ISNULL THEN
2574 SELECT * INTO "delegation_row" FROM "delegation"
2575 WHERE "truster_id" = "output_row"."member_id"
2576 AND (
2577 "unit_id" = "unit_id_v" OR
2578 "area_id" = "area_id_v"
2579 )
2580 ORDER BY "scope" DESC;
2581 END IF;
2582 ELSE
2583 SELECT * INTO "delegation_row" FROM "delegation"
2584 WHERE "truster_id" = "output_row"."member_id"
2585 AND (
2586 "unit_id" = "unit_id_v" OR
2587 "area_id" = "area_id_v" OR
2588 "issue_id" = "issue_id_p"
2589 )
2590 ORDER BY "scope" DESC;
2591 END IF;
2592 END IF;
2593 ELSE
2594 "output_row"."participation" := FALSE;
2595 END IF;
2596 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
2597 "output_row"."scope_out" := "scope_v";
2598 "output_rows" := "output_rows" || "output_row";
2599 "output_row"."member_id" := "simulate_trustee_id_p";
2600 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2601 "output_row"."scope_out" := "delegation_row"."scope";
2602 "output_rows" := "output_rows" || "output_row";
2603 "output_row"."member_id" := "delegation_row"."trustee_id";
2604 ELSIF "delegation_row"."scope" NOTNULL THEN
2605 "output_row"."scope_out" := "delegation_row"."scope";
2606 "output_row"."disabled_out" := TRUE;
2607 "output_rows" := "output_rows" || "output_row";
2608 EXIT;
2609 ELSE
2610 "output_row"."scope_out" := NULL;
2611 "output_rows" := "output_rows" || "output_row";
2612 EXIT;
2613 END IF;
2614 EXIT WHEN "loop_member_id_v" NOTNULL;
2615 "output_row"."index" := "output_row"."index" + 1;
2616 END LOOP;
2617 "row_count" := array_upper("output_rows", 1);
2618 "i" := 1;
2619 "loop_v" := FALSE;
2620 LOOP
2621 "output_row" := "output_rows"["i"];
2622 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2623 IF "loop_v" THEN
2624 IF "i" + 1 = "row_count" THEN
2625 "output_row"."loop" := 'last';
2626 ELSIF "i" = "row_count" THEN
2627 "output_row"."loop" := 'repetition';
2628 ELSE
2629 "output_row"."loop" := 'intermediate';
2630 END IF;
2631 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2632 "output_row"."loop" := 'first';
2633 "loop_v" := TRUE;
2634 END IF;
2635 IF "scope_v" = 'unit' THEN
2636 "output_row"."participation" := NULL;
2637 END IF;
2638 RETURN NEXT "output_row";
2639 "i" := "i" + 1;
2640 END LOOP;
2641 RETURN;
2642 END;
2643 $$;
2645 COMMENT ON FUNCTION "delegation_chain"
2646 ( "member"."id"%TYPE,
2647 "unit"."id"%TYPE,
2648 "area"."id"%TYPE,
2649 "issue"."id"%TYPE,
2650 "member"."id"%TYPE,
2651 BOOLEAN )
2652 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
2656 ---------------------------------------------------------
2657 -- Single row returning function for delegation chains --
2658 ---------------------------------------------------------
2661 CREATE TYPE "delegation_info_loop_type" AS ENUM
2662 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
2664 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''';
2667 CREATE TYPE "delegation_info_type" AS (
2668 "own_participation" BOOLEAN,
2669 "own_delegation_scope" "delegation_scope",
2670 "first_trustee_id" INT4,
2671 "first_trustee_participation" BOOLEAN,
2672 "first_trustee_ellipsis" BOOLEAN,
2673 "other_trustee_id" INT4,
2674 "other_trustee_participation" BOOLEAN,
2675 "other_trustee_ellipsis" BOOLEAN,
2676 "delegation_loop" "delegation_info_loop_type",
2677 "participating_member_id" INT4 );
2679 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';
2681 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
2682 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
2683 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
2684 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
2685 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
2686 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
2687 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)';
2688 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
2689 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';
2690 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';
2693 CREATE FUNCTION "delegation_info"
2694 ( "member_id_p" "member"."id"%TYPE,
2695 "unit_id_p" "unit"."id"%TYPE,
2696 "area_id_p" "area"."id"%TYPE,
2697 "issue_id_p" "issue"."id"%TYPE,
2698 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2699 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2700 RETURNS "delegation_info_type"
2701 LANGUAGE 'plpgsql' STABLE AS $$
2702 DECLARE
2703 "current_row" "delegation_chain_row";
2704 "result" "delegation_info_type";
2705 BEGIN
2706 "result"."own_participation" := FALSE;
2707 FOR "current_row" IN
2708 SELECT * FROM "delegation_chain"(
2709 "member_id_p",
2710 "unit_id_p", "area_id_p", "issue_id_p",
2711 "simulate_trustee_id_p", "simulate_default_p")
2712 LOOP
2713 IF
2714 "result"."participating_member_id" ISNULL AND
2715 "current_row"."participation"
2716 THEN
2717 "result"."participating_member_id" := "current_row"."member_id";
2718 END IF;
2719 IF "current_row"."member_id" = "member_id_p" THEN
2720 "result"."own_participation" := "current_row"."participation";
2721 "result"."own_delegation_scope" := "current_row"."scope_out";
2722 IF "current_row"."loop" = 'first' THEN
2723 "result"."delegation_loop" := 'own';
2724 END IF;
2725 ELSIF
2726 "current_row"."member_valid" AND
2727 ( "current_row"."loop" ISNULL OR
2728 "current_row"."loop" != 'repetition' )
2729 THEN
2730 IF "result"."first_trustee_id" ISNULL THEN
2731 "result"."first_trustee_id" := "current_row"."member_id";
2732 "result"."first_trustee_participation" := "current_row"."participation";
2733 "result"."first_trustee_ellipsis" := FALSE;
2734 IF "current_row"."loop" = 'first' THEN
2735 "result"."delegation_loop" := 'first';
2736 END IF;
2737 ELSIF "result"."other_trustee_id" ISNULL THEN
2738 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
2739 "result"."other_trustee_id" := "current_row"."member_id";
2740 "result"."other_trustee_participation" := TRUE;
2741 "result"."other_trustee_ellipsis" := FALSE;
2742 IF "current_row"."loop" = 'first' THEN
2743 "result"."delegation_loop" := 'other';
2744 END IF;
2745 ELSE
2746 "result"."first_trustee_ellipsis" := TRUE;
2747 IF "current_row"."loop" = 'first' THEN
2748 "result"."delegation_loop" := 'first_ellipsis';
2749 END IF;
2750 END IF;
2751 ELSE
2752 "result"."other_trustee_ellipsis" := TRUE;
2753 IF "current_row"."loop" = 'first' THEN
2754 "result"."delegation_loop" := 'other_ellipsis';
2755 END IF;
2756 END IF;
2757 END IF;
2758 END LOOP;
2759 RETURN "result";
2760 END;
2761 $$;
2763 COMMENT ON FUNCTION "delegation_info"
2764 ( "member"."id"%TYPE,
2765 "unit"."id"%TYPE,
2766 "area"."id"%TYPE,
2767 "issue"."id"%TYPE,
2768 "member"."id"%TYPE,
2769 BOOLEAN )
2770 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
2774 ---------------------------
2775 -- Transaction isolation --
2776 ---------------------------
2779 CREATE FUNCTION "require_transaction_isolation"()
2780 RETURNS VOID
2781 LANGUAGE 'plpgsql' VOLATILE AS $$
2782 BEGIN
2783 IF
2784 current_setting('transaction_isolation') NOT IN
2785 ('repeatable read', 'serializable')
2786 THEN
2787 RAISE EXCEPTION 'Insufficient transaction isolation level';
2788 END IF;
2789 RETURN;
2790 END;
2791 $$;
2793 COMMENT ON FUNCTION "require_transaction_isolation"() IS 'Throws an exception, if transaction isolation level is too low to provide a consistent snapshot';
2796 CREATE FUNCTION "dont_require_transaction_isolation"()
2797 RETURNS VOID
2798 LANGUAGE 'plpgsql' VOLATILE AS $$
2799 BEGIN
2800 IF
2801 current_setting('transaction_isolation') IN
2802 ('repeatable read', 'serializable')
2803 THEN
2804 RAISE WARNING 'Unneccessary transaction isolation level: %',
2805 current_setting('transaction_isolation');
2806 END IF;
2807 RETURN;
2808 END;
2809 $$;
2811 COMMENT ON FUNCTION "dont_require_transaction_isolation"() IS 'Raises a warning, if transaction isolation level is higher than READ COMMITTED';
2815 ------------------------------------------------------------------------
2816 -- Regular tasks, except calculcation of snapshots and voting results --
2817 ------------------------------------------------------------------------
2820 CREATE FUNCTION "check_activity"()
2821 RETURNS VOID
2822 LANGUAGE 'plpgsql' VOLATILE AS $$
2823 DECLARE
2824 "system_setting_row" "system_setting"%ROWTYPE;
2825 BEGIN
2826 PERFORM "dont_require_transaction_isolation"();
2827 SELECT * INTO "system_setting_row" FROM "system_setting";
2828 IF "system_setting_row"."member_ttl" NOTNULL THEN
2829 UPDATE "member" SET "active" = FALSE
2830 WHERE "active" = TRUE
2831 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
2832 END IF;
2833 RETURN;
2834 END;
2835 $$;
2837 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
2840 CREATE FUNCTION "calculate_member_counts"()
2841 RETURNS VOID
2842 LANGUAGE 'plpgsql' VOLATILE AS $$
2843 BEGIN
2844 PERFORM "require_transaction_isolation"();
2845 DELETE FROM "member_count";
2846 INSERT INTO "member_count" ("total_count")
2847 SELECT "total_count" FROM "member_count_view";
2848 UPDATE "unit" SET "member_count" = "view"."member_count"
2849 FROM "unit_member_count" AS "view"
2850 WHERE "view"."unit_id" = "unit"."id";
2851 UPDATE "area" SET
2852 "direct_member_count" = "view"."direct_member_count",
2853 "member_weight" = "view"."member_weight"
2854 FROM "area_member_count" AS "view"
2855 WHERE "view"."area_id" = "area"."id";
2856 RETURN;
2857 END;
2858 $$;
2860 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"';
2864 ------------------------------------
2865 -- Calculation of harmonic weight --
2866 ------------------------------------
2869 CREATE VIEW "remaining_harmonic_supporter_weight" AS
2870 SELECT
2871 "direct_interest_snapshot"."issue_id",
2872 "direct_interest_snapshot"."event",
2873 "direct_interest_snapshot"."member_id",
2874 "direct_interest_snapshot"."weight" AS "weight_num",
2875 count("initiative"."id") AS "weight_den"
2876 FROM "issue"
2877 JOIN "direct_interest_snapshot"
2878 ON "issue"."id" = "direct_interest_snapshot"."issue_id"
2879 AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
2880 JOIN "initiative"
2881 ON "issue"."id" = "initiative"."issue_id"
2882 AND "initiative"."harmonic_weight" ISNULL
2883 JOIN "direct_supporter_snapshot"
2884 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
2885 AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event"
2886 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
2887 AND (
2888 "direct_supporter_snapshot"."satisfied" = TRUE OR
2889 coalesce("initiative"."admitted", FALSE) = FALSE
2890 )
2891 GROUP BY
2892 "direct_interest_snapshot"."issue_id",
2893 "direct_interest_snapshot"."event",
2894 "direct_interest_snapshot"."member_id",
2895 "direct_interest_snapshot"."weight";
2897 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
2900 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
2901 SELECT
2902 "initiative"."issue_id",
2903 "initiative"."id" AS "initiative_id",
2904 "initiative"."admitted",
2905 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
2906 "remaining_harmonic_supporter_weight"."weight_den"
2907 FROM "remaining_harmonic_supporter_weight"
2908 JOIN "initiative"
2909 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
2910 AND "initiative"."harmonic_weight" ISNULL
2911 JOIN "direct_supporter_snapshot"
2912 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
2913 AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"
2914 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
2915 AND (
2916 "direct_supporter_snapshot"."satisfied" = TRUE OR
2917 coalesce("initiative"."admitted", FALSE) = FALSE
2918 )
2919 GROUP BY
2920 "initiative"."issue_id",
2921 "initiative"."id",
2922 "initiative"."admitted",
2923 "remaining_harmonic_supporter_weight"."weight_den";
2925 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
2928 CREATE VIEW "remaining_harmonic_initiative_weight_dummies" AS
2929 SELECT
2930 "issue_id",
2931 "id" AS "initiative_id",
2932 "admitted",
2933 0 AS "weight_num",
2934 1 AS "weight_den"
2935 FROM "initiative"
2936 WHERE "harmonic_weight" ISNULL;
2938 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';
2941 CREATE FUNCTION "set_harmonic_initiative_weights"
2942 ( "issue_id_p" "issue"."id"%TYPE )
2943 RETURNS VOID
2944 LANGUAGE 'plpgsql' VOLATILE AS $$
2945 DECLARE
2946 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
2947 "i" INT4;
2948 "count_v" INT4;
2949 "summand_v" FLOAT;
2950 "id_ary" INT4[];
2951 "weight_ary" FLOAT[];
2952 "min_weight_v" FLOAT;
2953 BEGIN
2954 PERFORM "require_transaction_isolation"();
2955 UPDATE "initiative" SET "harmonic_weight" = NULL
2956 WHERE "issue_id" = "issue_id_p";
2957 LOOP
2958 "min_weight_v" := NULL;
2959 "i" := 0;
2960 "count_v" := 0;
2961 FOR "weight_row" IN
2962 SELECT * FROM "remaining_harmonic_initiative_weight_summands"
2963 WHERE "issue_id" = "issue_id_p"
2964 AND (
2965 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
2966 SELECT NULL FROM "initiative"
2967 WHERE "issue_id" = "issue_id_p"
2968 AND "harmonic_weight" ISNULL
2969 AND coalesce("admitted", FALSE) = FALSE
2970 )
2971 )
2972 UNION ALL -- needed for corner cases
2973 SELECT * FROM "remaining_harmonic_initiative_weight_dummies"
2974 WHERE "issue_id" = "issue_id_p"
2975 AND (
2976 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
2977 SELECT NULL FROM "initiative"
2978 WHERE "issue_id" = "issue_id_p"
2979 AND "harmonic_weight" ISNULL
2980 AND coalesce("admitted", FALSE) = FALSE
2981 )
2982 )
2983 ORDER BY "initiative_id" DESC, "weight_den" DESC
2984 -- NOTE: non-admitted initiatives placed first (at last positions),
2985 -- latest initiatives treated worse in case of tie
2986 LOOP
2987 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
2988 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
2989 "i" := "i" + 1;
2990 "count_v" := "i";
2991 "id_ary"["i"] := "weight_row"."initiative_id";
2992 "weight_ary"["i"] := "summand_v";
2993 ELSE
2994 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
2995 END IF;
2996 END LOOP;
2997 EXIT WHEN "count_v" = 0;
2998 "i" := 1;
2999 LOOP
3000 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
3001 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
3002 "min_weight_v" := "weight_ary"["i"];
3003 END IF;
3004 "i" := "i" + 1;
3005 EXIT WHEN "i" > "count_v";
3006 END LOOP;
3007 "i" := 1;
3008 LOOP
3009 IF "weight_ary"["i"] = "min_weight_v" THEN
3010 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
3011 WHERE "id" = "id_ary"["i"];
3012 EXIT;
3013 END IF;
3014 "i" := "i" + 1;
3015 END LOOP;
3016 END LOOP;
3017 UPDATE "initiative" SET "harmonic_weight" = 0
3018 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
3019 END;
3020 $$;
3022 COMMENT ON FUNCTION "set_harmonic_initiative_weights"
3023 ( "issue"."id"%TYPE )
3024 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
3028 ------------------------------
3029 -- Calculation of snapshots --
3030 ------------------------------
3033 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
3034 ( "issue_id_p" "issue"."id"%TYPE,
3035 "member_id_p" "member"."id"%TYPE,
3036 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3037 RETURNS "direct_population_snapshot"."weight"%TYPE
3038 LANGUAGE 'plpgsql' VOLATILE AS $$
3039 DECLARE
3040 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3041 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
3042 "weight_v" INT4;
3043 "sub_weight_v" INT4;
3044 BEGIN
3045 PERFORM "require_transaction_isolation"();
3046 "weight_v" := 0;
3047 FOR "issue_delegation_row" IN
3048 SELECT * FROM "issue_delegation"
3049 WHERE "trustee_id" = "member_id_p"
3050 AND "issue_id" = "issue_id_p"
3051 LOOP
3052 IF NOT EXISTS (
3053 SELECT NULL FROM "direct_population_snapshot"
3054 WHERE "issue_id" = "issue_id_p"
3055 AND "event" = 'periodic'
3056 AND "member_id" = "issue_delegation_row"."truster_id"
3057 ) AND NOT EXISTS (
3058 SELECT NULL FROM "delegating_population_snapshot"
3059 WHERE "issue_id" = "issue_id_p"
3060 AND "event" = 'periodic'
3061 AND "member_id" = "issue_delegation_row"."truster_id"
3062 ) THEN
3063 "delegate_member_ids_v" :=
3064 "member_id_p" || "delegate_member_ids_p";
3065 INSERT INTO "delegating_population_snapshot" (
3066 "issue_id",
3067 "event",
3068 "member_id",
3069 "scope",
3070 "delegate_member_ids"
3071 ) VALUES (
3072 "issue_id_p",
3073 'periodic',
3074 "issue_delegation_row"."truster_id",
3075 "issue_delegation_row"."scope",
3076 "delegate_member_ids_v"
3077 );
3078 "sub_weight_v" := 1 +
3079 "weight_of_added_delegations_for_population_snapshot"(
3080 "issue_id_p",
3081 "issue_delegation_row"."truster_id",
3082 "delegate_member_ids_v"
3083 );
3084 UPDATE "delegating_population_snapshot"
3085 SET "weight" = "sub_weight_v"
3086 WHERE "issue_id" = "issue_id_p"
3087 AND "event" = 'periodic'
3088 AND "member_id" = "issue_delegation_row"."truster_id";
3089 "weight_v" := "weight_v" + "sub_weight_v";
3090 END IF;
3091 END LOOP;
3092 RETURN "weight_v";
3093 END;
3094 $$;
3096 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
3097 ( "issue"."id"%TYPE,
3098 "member"."id"%TYPE,
3099 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3100 IS 'Helper function for "create_population_snapshot" function';
3103 CREATE FUNCTION "create_population_snapshot"
3104 ( "issue_id_p" "issue"."id"%TYPE )
3105 RETURNS VOID
3106 LANGUAGE 'plpgsql' VOLATILE AS $$
3107 DECLARE
3108 "member_id_v" "member"."id"%TYPE;
3109 BEGIN
3110 PERFORM "require_transaction_isolation"();
3111 DELETE FROM "direct_population_snapshot"
3112 WHERE "issue_id" = "issue_id_p"
3113 AND "event" = 'periodic';
3114 DELETE FROM "delegating_population_snapshot"
3115 WHERE "issue_id" = "issue_id_p"
3116 AND "event" = 'periodic';
3117 INSERT INTO "direct_population_snapshot"
3118 ("issue_id", "event", "member_id")
3119 SELECT
3120 "issue_id_p" AS "issue_id",
3121 'periodic'::"snapshot_event" AS "event",
3122 "member"."id" AS "member_id"
3123 FROM "issue"
3124 JOIN "area" ON "issue"."area_id" = "area"."id"
3125 JOIN "membership" ON "area"."id" = "membership"."area_id"
3126 JOIN "member" ON "membership"."member_id" = "member"."id"
3127 JOIN "privilege"
3128 ON "privilege"."unit_id" = "area"."unit_id"
3129 AND "privilege"."member_id" = "member"."id"
3130 WHERE "issue"."id" = "issue_id_p"
3131 AND "member"."active" AND "privilege"."voting_right"
3132 UNION
3133 SELECT
3134 "issue_id_p" AS "issue_id",
3135 'periodic'::"snapshot_event" AS "event",
3136 "member"."id" AS "member_id"
3137 FROM "issue"
3138 JOIN "area" ON "issue"."area_id" = "area"."id"
3139 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3140 JOIN "member" ON "interest"."member_id" = "member"."id"
3141 JOIN "privilege"
3142 ON "privilege"."unit_id" = "area"."unit_id"
3143 AND "privilege"."member_id" = "member"."id"
3144 WHERE "issue"."id" = "issue_id_p"
3145 AND "member"."active" AND "privilege"."voting_right";
3146 FOR "member_id_v" IN
3147 SELECT "member_id" FROM "direct_population_snapshot"
3148 WHERE "issue_id" = "issue_id_p"
3149 AND "event" = 'periodic'
3150 LOOP
3151 UPDATE "direct_population_snapshot" SET
3152 "weight" = 1 +
3153 "weight_of_added_delegations_for_population_snapshot"(
3154 "issue_id_p",
3155 "member_id_v",
3156 '{}'
3157 )
3158 WHERE "issue_id" = "issue_id_p"
3159 AND "event" = 'periodic'
3160 AND "member_id" = "member_id_v";
3161 END LOOP;
3162 RETURN;
3163 END;
3164 $$;
3166 COMMENT ON FUNCTION "create_population_snapshot"
3167 ( "issue"."id"%TYPE )
3168 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.';
3171 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3172 ( "issue_id_p" "issue"."id"%TYPE,
3173 "member_id_p" "member"."id"%TYPE,
3174 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3175 RETURNS "direct_interest_snapshot"."weight"%TYPE
3176 LANGUAGE 'plpgsql' VOLATILE AS $$
3177 DECLARE
3178 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3179 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
3180 "weight_v" INT4;
3181 "sub_weight_v" INT4;
3182 BEGIN
3183 PERFORM "require_transaction_isolation"();
3184 "weight_v" := 0;
3185 FOR "issue_delegation_row" IN
3186 SELECT * FROM "issue_delegation"
3187 WHERE "trustee_id" = "member_id_p"
3188 AND "issue_id" = "issue_id_p"
3189 LOOP
3190 IF NOT EXISTS (
3191 SELECT NULL FROM "direct_interest_snapshot"
3192 WHERE "issue_id" = "issue_id_p"
3193 AND "event" = 'periodic'
3194 AND "member_id" = "issue_delegation_row"."truster_id"
3195 ) AND NOT EXISTS (
3196 SELECT NULL FROM "delegating_interest_snapshot"
3197 WHERE "issue_id" = "issue_id_p"
3198 AND "event" = 'periodic'
3199 AND "member_id" = "issue_delegation_row"."truster_id"
3200 ) THEN
3201 "delegate_member_ids_v" :=
3202 "member_id_p" || "delegate_member_ids_p";
3203 INSERT INTO "delegating_interest_snapshot" (
3204 "issue_id",
3205 "event",
3206 "member_id",
3207 "scope",
3208 "delegate_member_ids"
3209 ) VALUES (
3210 "issue_id_p",
3211 'periodic',
3212 "issue_delegation_row"."truster_id",
3213 "issue_delegation_row"."scope",
3214 "delegate_member_ids_v"
3215 );
3216 "sub_weight_v" := 1 +
3217 "weight_of_added_delegations_for_interest_snapshot"(
3218 "issue_id_p",
3219 "issue_delegation_row"."truster_id",
3220 "delegate_member_ids_v"
3221 );
3222 UPDATE "delegating_interest_snapshot"
3223 SET "weight" = "sub_weight_v"
3224 WHERE "issue_id" = "issue_id_p"
3225 AND "event" = 'periodic'
3226 AND "member_id" = "issue_delegation_row"."truster_id";
3227 "weight_v" := "weight_v" + "sub_weight_v";
3228 END IF;
3229 END LOOP;
3230 RETURN "weight_v";
3231 END;
3232 $$;
3234 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3235 ( "issue"."id"%TYPE,
3236 "member"."id"%TYPE,
3237 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3238 IS 'Helper function for "create_interest_snapshot" function';
3241 CREATE FUNCTION "create_interest_snapshot"
3242 ( "issue_id_p" "issue"."id"%TYPE )
3243 RETURNS VOID
3244 LANGUAGE 'plpgsql' VOLATILE AS $$
3245 DECLARE
3246 "member_id_v" "member"."id"%TYPE;
3247 BEGIN
3248 PERFORM "require_transaction_isolation"();
3249 DELETE FROM "direct_interest_snapshot"
3250 WHERE "issue_id" = "issue_id_p"
3251 AND "event" = 'periodic';
3252 DELETE FROM "delegating_interest_snapshot"
3253 WHERE "issue_id" = "issue_id_p"
3254 AND "event" = 'periodic';
3255 DELETE FROM "direct_supporter_snapshot"
3256 USING "initiative" -- NOTE: due to missing index on issue_id
3257 WHERE "initiative"."issue_id" = "issue_id_p"
3258 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
3259 AND "direct_supporter_snapshot"."event" = 'periodic';
3260 INSERT INTO "direct_interest_snapshot"
3261 ("issue_id", "event", "member_id")
3262 SELECT
3263 "issue_id_p" AS "issue_id",
3264 'periodic' AS "event",
3265 "member"."id" AS "member_id"
3266 FROM "issue"
3267 JOIN "area" ON "issue"."area_id" = "area"."id"
3268 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3269 JOIN "member" ON "interest"."member_id" = "member"."id"
3270 JOIN "privilege"
3271 ON "privilege"."unit_id" = "area"."unit_id"
3272 AND "privilege"."member_id" = "member"."id"
3273 WHERE "issue"."id" = "issue_id_p"
3274 AND "member"."active" AND "privilege"."voting_right";
3275 FOR "member_id_v" IN
3276 SELECT "member_id" FROM "direct_interest_snapshot"
3277 WHERE "issue_id" = "issue_id_p"
3278 AND "event" = 'periodic'
3279 LOOP
3280 UPDATE "direct_interest_snapshot" SET
3281 "weight" = 1 +
3282 "weight_of_added_delegations_for_interest_snapshot"(
3283 "issue_id_p",
3284 "member_id_v",
3285 '{}'
3286 )
3287 WHERE "issue_id" = "issue_id_p"
3288 AND "event" = 'periodic'
3289 AND "member_id" = "member_id_v";
3290 END LOOP;
3291 INSERT INTO "direct_supporter_snapshot"
3292 ( "issue_id", "initiative_id", "event", "member_id",
3293 "draft_id", "informed", "satisfied" )
3294 SELECT
3295 "issue_id_p" AS "issue_id",
3296 "initiative"."id" AS "initiative_id",
3297 'periodic' AS "event",
3298 "supporter"."member_id" AS "member_id",
3299 "supporter"."draft_id" AS "draft_id",
3300 "supporter"."draft_id" = "current_draft"."id" AS "informed",
3301 NOT EXISTS (
3302 SELECT NULL FROM "critical_opinion"
3303 WHERE "initiative_id" = "initiative"."id"
3304 AND "member_id" = "supporter"."member_id"
3305 ) AS "satisfied"
3306 FROM "initiative"
3307 JOIN "supporter"
3308 ON "supporter"."initiative_id" = "initiative"."id"
3309 JOIN "current_draft"
3310 ON "initiative"."id" = "current_draft"."initiative_id"
3311 JOIN "direct_interest_snapshot"
3312 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
3313 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
3314 AND "event" = 'periodic'
3315 WHERE "initiative"."issue_id" = "issue_id_p";
3316 RETURN;
3317 END;
3318 $$;
3320 COMMENT ON FUNCTION "create_interest_snapshot"
3321 ( "issue"."id"%TYPE )
3322 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.';
3325 CREATE FUNCTION "create_snapshot"
3326 ( "issue_id_p" "issue"."id"%TYPE )
3327 RETURNS VOID
3328 LANGUAGE 'plpgsql' VOLATILE AS $$
3329 DECLARE
3330 "initiative_id_v" "initiative"."id"%TYPE;
3331 "suggestion_id_v" "suggestion"."id"%TYPE;
3332 BEGIN
3333 PERFORM "require_transaction_isolation"();
3334 PERFORM "create_population_snapshot"("issue_id_p");
3335 PERFORM "create_interest_snapshot"("issue_id_p");
3336 UPDATE "issue" SET
3337 "snapshot" = coalesce("phase_finished", now()),
3338 "latest_snapshot_event" = 'periodic',
3339 "population" = (
3340 SELECT coalesce(sum("weight"), 0)
3341 FROM "direct_population_snapshot"
3342 WHERE "issue_id" = "issue_id_p"
3343 AND "event" = 'periodic'
3344 )
3345 WHERE "id" = "issue_id_p";
3346 FOR "initiative_id_v" IN
3347 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
3348 LOOP
3349 UPDATE "initiative" SET
3350 "supporter_count" = (
3351 SELECT coalesce(sum("di"."weight"), 0)
3352 FROM "direct_interest_snapshot" AS "di"
3353 JOIN "direct_supporter_snapshot" AS "ds"
3354 ON "di"."member_id" = "ds"."member_id"
3355 WHERE "di"."issue_id" = "issue_id_p"
3356 AND "di"."event" = 'periodic'
3357 AND "ds"."initiative_id" = "initiative_id_v"
3358 AND "ds"."event" = 'periodic'
3359 ),
3360 "informed_supporter_count" = (
3361 SELECT coalesce(sum("di"."weight"), 0)
3362 FROM "direct_interest_snapshot" AS "di"
3363 JOIN "direct_supporter_snapshot" AS "ds"
3364 ON "di"."member_id" = "ds"."member_id"
3365 WHERE "di"."issue_id" = "issue_id_p"
3366 AND "di"."event" = 'periodic'
3367 AND "ds"."initiative_id" = "initiative_id_v"
3368 AND "ds"."event" = 'periodic'
3369 AND "ds"."informed"
3370 ),
3371 "satisfied_supporter_count" = (
3372 SELECT coalesce(sum("di"."weight"), 0)
3373 FROM "direct_interest_snapshot" AS "di"
3374 JOIN "direct_supporter_snapshot" AS "ds"
3375 ON "di"."member_id" = "ds"."member_id"
3376 WHERE "di"."issue_id" = "issue_id_p"
3377 AND "di"."event" = 'periodic'
3378 AND "ds"."initiative_id" = "initiative_id_v"
3379 AND "ds"."event" = 'periodic'
3380 AND "ds"."satisfied"
3381 ),
3382 "satisfied_informed_supporter_count" = (
3383 SELECT coalesce(sum("di"."weight"), 0)
3384 FROM "direct_interest_snapshot" AS "di"
3385 JOIN "direct_supporter_snapshot" AS "ds"
3386 ON "di"."member_id" = "ds"."member_id"
3387 WHERE "di"."issue_id" = "issue_id_p"
3388 AND "di"."event" = 'periodic'
3389 AND "ds"."initiative_id" = "initiative_id_v"
3390 AND "ds"."event" = 'periodic'
3391 AND "ds"."informed"
3392 AND "ds"."satisfied"
3393 )
3394 WHERE "id" = "initiative_id_v";
3395 FOR "suggestion_id_v" IN
3396 SELECT "id" FROM "suggestion"
3397 WHERE "initiative_id" = "initiative_id_v"
3398 LOOP
3399 UPDATE "suggestion" SET
3400 "minus2_unfulfilled_count" = (
3401 SELECT coalesce(sum("snapshot"."weight"), 0)
3402 FROM "issue" CROSS JOIN "opinion"
3403 JOIN "direct_interest_snapshot" AS "snapshot"
3404 ON "snapshot"."issue_id" = "issue"."id"
3405 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3406 AND "snapshot"."member_id" = "opinion"."member_id"
3407 WHERE "issue"."id" = "issue_id_p"
3408 AND "opinion"."suggestion_id" = "suggestion_id_v"
3409 AND "opinion"."degree" = -2
3410 AND "opinion"."fulfilled" = FALSE
3411 ),
3412 "minus2_fulfilled_count" = (
3413 SELECT coalesce(sum("snapshot"."weight"), 0)
3414 FROM "issue" CROSS JOIN "opinion"
3415 JOIN "direct_interest_snapshot" AS "snapshot"
3416 ON "snapshot"."issue_id" = "issue"."id"
3417 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3418 AND "snapshot"."member_id" = "opinion"."member_id"
3419 WHERE "issue"."id" = "issue_id_p"
3420 AND "opinion"."suggestion_id" = "suggestion_id_v"
3421 AND "opinion"."degree" = -2
3422 AND "opinion"."fulfilled" = TRUE
3423 ),
3424 "minus1_unfulfilled_count" = (
3425 SELECT coalesce(sum("snapshot"."weight"), 0)
3426 FROM "issue" CROSS JOIN "opinion"
3427 JOIN "direct_interest_snapshot" AS "snapshot"
3428 ON "snapshot"."issue_id" = "issue"."id"
3429 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3430 AND "snapshot"."member_id" = "opinion"."member_id"
3431 WHERE "issue"."id" = "issue_id_p"
3432 AND "opinion"."suggestion_id" = "suggestion_id_v"
3433 AND "opinion"."degree" = -1
3434 AND "opinion"."fulfilled" = FALSE
3435 ),
3436 "minus1_fulfilled_count" = (
3437 SELECT coalesce(sum("snapshot"."weight"), 0)
3438 FROM "issue" CROSS JOIN "opinion"
3439 JOIN "direct_interest_snapshot" AS "snapshot"
3440 ON "snapshot"."issue_id" = "issue"."id"
3441 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3442 AND "snapshot"."member_id" = "opinion"."member_id"
3443 WHERE "issue"."id" = "issue_id_p"
3444 AND "opinion"."suggestion_id" = "suggestion_id_v"
3445 AND "opinion"."degree" = -1
3446 AND "opinion"."fulfilled" = TRUE
3447 ),
3448 "plus1_unfulfilled_count" = (
3449 SELECT coalesce(sum("snapshot"."weight"), 0)
3450 FROM "issue" CROSS JOIN "opinion"
3451 JOIN "direct_interest_snapshot" AS "snapshot"
3452 ON "snapshot"."issue_id" = "issue"."id"
3453 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3454 AND "snapshot"."member_id" = "opinion"."member_id"
3455 WHERE "issue"."id" = "issue_id_p"
3456 AND "opinion"."suggestion_id" = "suggestion_id_v"
3457 AND "opinion"."degree" = 1
3458 AND "opinion"."fulfilled" = FALSE
3459 ),
3460 "plus1_fulfilled_count" = (
3461 SELECT coalesce(sum("snapshot"."weight"), 0)
3462 FROM "issue" CROSS JOIN "opinion"
3463 JOIN "direct_interest_snapshot" AS "snapshot"
3464 ON "snapshot"."issue_id" = "issue"."id"
3465 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3466 AND "snapshot"."member_id" = "opinion"."member_id"
3467 WHERE "issue"."id" = "issue_id_p"
3468 AND "opinion"."suggestion_id" = "suggestion_id_v"
3469 AND "opinion"."degree" = 1
3470 AND "opinion"."fulfilled" = TRUE
3471 ),
3472 "plus2_unfulfilled_count" = (
3473 SELECT coalesce(sum("snapshot"."weight"), 0)
3474 FROM "issue" CROSS JOIN "opinion"
3475 JOIN "direct_interest_snapshot" AS "snapshot"
3476 ON "snapshot"."issue_id" = "issue"."id"
3477 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3478 AND "snapshot"."member_id" = "opinion"."member_id"
3479 WHERE "issue"."id" = "issue_id_p"
3480 AND "opinion"."suggestion_id" = "suggestion_id_v"
3481 AND "opinion"."degree" = 2
3482 AND "opinion"."fulfilled" = FALSE
3483 ),
3484 "plus2_fulfilled_count" = (
3485 SELECT coalesce(sum("snapshot"."weight"), 0)
3486 FROM "issue" CROSS JOIN "opinion"
3487 JOIN "direct_interest_snapshot" AS "snapshot"
3488 ON "snapshot"."issue_id" = "issue"."id"
3489 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3490 AND "snapshot"."member_id" = "opinion"."member_id"
3491 WHERE "issue"."id" = "issue_id_p"
3492 AND "opinion"."suggestion_id" = "suggestion_id_v"
3493 AND "opinion"."degree" = 2
3494 AND "opinion"."fulfilled" = TRUE
3495 )
3496 WHERE "suggestion"."id" = "suggestion_id_v";
3497 END LOOP;
3498 END LOOP;
3499 RETURN;
3500 END;
3501 $$;
3503 COMMENT ON FUNCTION "create_snapshot"
3504 ( "issue"."id"%TYPE )
3505 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.';
3508 CREATE FUNCTION "set_snapshot_event"
3509 ( "issue_id_p" "issue"."id"%TYPE,
3510 "event_p" "snapshot_event" )
3511 RETURNS VOID
3512 LANGUAGE 'plpgsql' VOLATILE AS $$
3513 DECLARE
3514 "event_v" "issue"."latest_snapshot_event"%TYPE;
3515 BEGIN
3516 PERFORM "require_transaction_isolation"();
3517 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
3518 WHERE "id" = "issue_id_p" FOR UPDATE;
3519 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
3520 WHERE "id" = "issue_id_p";
3521 UPDATE "direct_population_snapshot" SET "event" = "event_p"
3522 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3523 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
3524 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3525 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
3526 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3527 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
3528 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3529 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
3530 FROM "initiative" -- NOTE: due to missing index on issue_id
3531 WHERE "initiative"."issue_id" = "issue_id_p"
3532 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
3533 AND "direct_supporter_snapshot"."event" = "event_v";
3534 RETURN;
3535 END;
3536 $$;
3538 COMMENT ON FUNCTION "set_snapshot_event"
3539 ( "issue"."id"%TYPE,
3540 "snapshot_event" )
3541 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
3545 -----------------------
3546 -- Counting of votes --
3547 -----------------------
3550 CREATE FUNCTION "weight_of_added_vote_delegations"
3551 ( "issue_id_p" "issue"."id"%TYPE,
3552 "member_id_p" "member"."id"%TYPE,
3553 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
3554 RETURNS "direct_voter"."weight"%TYPE
3555 LANGUAGE 'plpgsql' VOLATILE AS $$
3556 DECLARE
3557 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3558 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
3559 "weight_v" INT4;
3560 "sub_weight_v" INT4;
3561 BEGIN
3562 PERFORM "require_transaction_isolation"();
3563 "weight_v" := 0;
3564 FOR "issue_delegation_row" IN
3565 SELECT * FROM "issue_delegation"
3566 WHERE "trustee_id" = "member_id_p"
3567 AND "issue_id" = "issue_id_p"
3568 LOOP
3569 IF NOT EXISTS (
3570 SELECT NULL FROM "direct_voter"
3571 WHERE "member_id" = "issue_delegation_row"."truster_id"
3572 AND "issue_id" = "issue_id_p"
3573 ) AND NOT EXISTS (
3574 SELECT NULL FROM "delegating_voter"
3575 WHERE "member_id" = "issue_delegation_row"."truster_id"
3576 AND "issue_id" = "issue_id_p"
3577 ) THEN
3578 "delegate_member_ids_v" :=
3579 "member_id_p" || "delegate_member_ids_p";
3580 INSERT INTO "delegating_voter" (
3581 "issue_id",
3582 "member_id",
3583 "scope",
3584 "delegate_member_ids"
3585 ) VALUES (
3586 "issue_id_p",
3587 "issue_delegation_row"."truster_id",
3588 "issue_delegation_row"."scope",
3589 "delegate_member_ids_v"
3590 );
3591 "sub_weight_v" := 1 +
3592 "weight_of_added_vote_delegations"(
3593 "issue_id_p",
3594 "issue_delegation_row"."truster_id",
3595 "delegate_member_ids_v"
3596 );
3597 UPDATE "delegating_voter"
3598 SET "weight" = "sub_weight_v"
3599 WHERE "issue_id" = "issue_id_p"
3600 AND "member_id" = "issue_delegation_row"."truster_id";
3601 "weight_v" := "weight_v" + "sub_weight_v";
3602 END IF;
3603 END LOOP;
3604 RETURN "weight_v";
3605 END;
3606 $$;
3608 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
3609 ( "issue"."id"%TYPE,
3610 "member"."id"%TYPE,
3611 "delegating_voter"."delegate_member_ids"%TYPE )
3612 IS 'Helper function for "add_vote_delegations" function';
3615 CREATE FUNCTION "add_vote_delegations"
3616 ( "issue_id_p" "issue"."id"%TYPE )
3617 RETURNS VOID
3618 LANGUAGE 'plpgsql' VOLATILE AS $$
3619 DECLARE
3620 "member_id_v" "member"."id"%TYPE;
3621 BEGIN
3622 PERFORM "require_transaction_isolation"();
3623 FOR "member_id_v" IN
3624 SELECT "member_id" FROM "direct_voter"
3625 WHERE "issue_id" = "issue_id_p"
3626 LOOP
3627 UPDATE "direct_voter" SET
3628 "weight" = "weight" + "weight_of_added_vote_delegations"(
3629 "issue_id_p",
3630 "member_id_v",
3631 '{}'
3632 )
3633 WHERE "member_id" = "member_id_v"
3634 AND "issue_id" = "issue_id_p";
3635 END LOOP;
3636 RETURN;
3637 END;
3638 $$;
3640 COMMENT ON FUNCTION "add_vote_delegations"
3641 ( "issue_id_p" "issue"."id"%TYPE )
3642 IS 'Helper function for "close_voting" function';
3645 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
3646 RETURNS VOID
3647 LANGUAGE 'plpgsql' VOLATILE AS $$
3648 DECLARE
3649 "area_id_v" "area"."id"%TYPE;
3650 "unit_id_v" "unit"."id"%TYPE;
3651 "member_id_v" "member"."id"%TYPE;
3652 BEGIN
3653 PERFORM "require_transaction_isolation"();
3654 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
3655 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
3656 -- delete timestamp of voting comment:
3657 UPDATE "direct_voter" SET "comment_changed" = NULL
3658 WHERE "issue_id" = "issue_id_p";
3659 -- delete delegating votes (in cases of manual reset of issue state):
3660 DELETE FROM "delegating_voter"
3661 WHERE "issue_id" = "issue_id_p";
3662 -- delete votes from non-privileged voters:
3663 DELETE FROM "direct_voter"
3664 USING (
3665 SELECT
3666 "direct_voter"."member_id"
3667 FROM "direct_voter"
3668 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
3669 LEFT JOIN "privilege"
3670 ON "privilege"."unit_id" = "unit_id_v"
3671 AND "privilege"."member_id" = "direct_voter"."member_id"
3672 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
3673 "member"."active" = FALSE OR
3674 "privilege"."voting_right" ISNULL OR
3675 "privilege"."voting_right" = FALSE
3676 )
3677 ) AS "subquery"
3678 WHERE "direct_voter"."issue_id" = "issue_id_p"
3679 AND "direct_voter"."member_id" = "subquery"."member_id";
3680 -- consider delegations:
3681 UPDATE "direct_voter" SET "weight" = 1
3682 WHERE "issue_id" = "issue_id_p";
3683 PERFORM "add_vote_delegations"("issue_id_p");
3684 -- materialize battle_view:
3685 -- NOTE: "closed" column of issue must be set at this point
3686 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3687 INSERT INTO "battle" (
3688 "issue_id",
3689 "winning_initiative_id", "losing_initiative_id",
3690 "count"
3691 ) SELECT
3692 "issue_id",
3693 "winning_initiative_id", "losing_initiative_id",
3694 "count"
3695 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
3696 -- set voter count:
3697 UPDATE "issue" SET
3698 "voter_count" = (
3699 SELECT coalesce(sum("weight"), 0)
3700 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
3701 )
3702 WHERE "id" = "issue_id_p";
3703 -- copy "positive_votes" and "negative_votes" from "battle" table:
3704 UPDATE "initiative" SET
3705 "positive_votes" = "battle_win"."count",
3706 "negative_votes" = "battle_lose"."count"
3707 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
3708 WHERE
3709 "battle_win"."issue_id" = "issue_id_p" AND
3710 "battle_win"."winning_initiative_id" = "initiative"."id" AND
3711 "battle_win"."losing_initiative_id" ISNULL AND
3712 "battle_lose"."issue_id" = "issue_id_p" AND
3713 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
3714 "battle_lose"."winning_initiative_id" ISNULL;
3715 END;
3716 $$;
3718 COMMENT ON FUNCTION "close_voting"
3719 ( "issue"."id"%TYPE )
3720 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.';
3723 CREATE FUNCTION "defeat_strength"
3724 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
3725 RETURNS INT8
3726 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3727 BEGIN
3728 IF "positive_votes_p" > "negative_votes_p" THEN
3729 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
3730 ELSIF "positive_votes_p" = "negative_votes_p" THEN
3731 RETURN 0;
3732 ELSE
3733 RETURN -1;
3734 END IF;
3735 END;
3736 $$;
3738 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';
3741 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
3742 RETURNS VOID
3743 LANGUAGE 'plpgsql' VOLATILE AS $$
3744 DECLARE
3745 "issue_row" "issue"%ROWTYPE;
3746 "policy_row" "policy"%ROWTYPE;
3747 "dimension_v" INTEGER;
3748 "vote_matrix" INT4[][]; -- absolute votes
3749 "matrix" INT8[][]; -- defeat strength / best paths
3750 "i" INTEGER;
3751 "j" INTEGER;
3752 "k" INTEGER;
3753 "battle_row" "battle"%ROWTYPE;
3754 "rank_ary" INT4[];
3755 "rank_v" INT4;
3756 "done_v" INTEGER;
3757 "winners_ary" INTEGER[];
3758 "initiative_id_v" "initiative"."id"%TYPE;
3759 BEGIN
3760 PERFORM "require_transaction_isolation"();
3761 SELECT * INTO "issue_row"
3762 FROM "issue" WHERE "id" = "issue_id_p";
3763 SELECT * INTO "policy_row"
3764 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3765 SELECT count(1) INTO "dimension_v"
3766 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
3767 -- Create "vote_matrix" with absolute number of votes in pairwise
3768 -- comparison:
3769 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
3770 "i" := 1;
3771 "j" := 2;
3772 FOR "battle_row" IN
3773 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
3774 ORDER BY
3775 "winning_initiative_id" NULLS LAST,
3776 "losing_initiative_id" NULLS LAST
3777 LOOP
3778 "vote_matrix"["i"]["j"] := "battle_row"."count";
3779 IF "j" = "dimension_v" THEN
3780 "i" := "i" + 1;
3781 "j" := 1;
3782 ELSE
3783 "j" := "j" + 1;
3784 IF "j" = "i" THEN
3785 "j" := "j" + 1;
3786 END IF;
3787 END IF;
3788 END LOOP;
3789 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
3790 RAISE EXCEPTION 'Wrong battle count (should not happen)';
3791 END IF;
3792 -- Store defeat strengths in "matrix" using "defeat_strength"
3793 -- function:
3794 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
3795 "i" := 1;
3796 LOOP
3797 "j" := 1;
3798 LOOP
3799 IF "i" != "j" THEN
3800 "matrix"["i"]["j"] := "defeat_strength"(
3801 "vote_matrix"["i"]["j"],
3802 "vote_matrix"["j"]["i"]
3803 );
3804 END IF;
3805 EXIT WHEN "j" = "dimension_v";
3806 "j" := "j" + 1;
3807 END LOOP;
3808 EXIT WHEN "i" = "dimension_v";
3809 "i" := "i" + 1;
3810 END LOOP;
3811 -- Find best paths:
3812 "i" := 1;
3813 LOOP
3814 "j" := 1;
3815 LOOP
3816 IF "i" != "j" THEN
3817 "k" := 1;
3818 LOOP
3819 IF "i" != "k" AND "j" != "k" THEN
3820 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
3821 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
3822 "matrix"["j"]["k"] := "matrix"["j"]["i"];
3823 END IF;
3824 ELSE
3825 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
3826 "matrix"["j"]["k"] := "matrix"["i"]["k"];
3827 END IF;
3828 END IF;
3829 END IF;
3830 EXIT WHEN "k" = "dimension_v";
3831 "k" := "k" + 1;
3832 END LOOP;
3833 END IF;
3834 EXIT WHEN "j" = "dimension_v";
3835 "j" := "j" + 1;
3836 END LOOP;
3837 EXIT WHEN "i" = "dimension_v";
3838 "i" := "i" + 1;
3839 END LOOP;
3840 -- Determine order of winners:
3841 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
3842 "rank_v" := 1;
3843 "done_v" := 0;
3844 LOOP
3845 "winners_ary" := '{}';
3846 "i" := 1;
3847 LOOP
3848 IF "rank_ary"["i"] ISNULL THEN
3849 "j" := 1;
3850 LOOP
3851 IF
3852 "i" != "j" AND
3853 "rank_ary"["j"] ISNULL AND
3854 "matrix"["j"]["i"] > "matrix"["i"]["j"]
3855 THEN
3856 -- someone else is better
3857 EXIT;
3858 END IF;
3859 IF "j" = "dimension_v" THEN
3860 -- noone is better
3861 "winners_ary" := "winners_ary" || "i";
3862 EXIT;
3863 END IF;
3864 "j" := "j" + 1;
3865 END LOOP;
3866 END IF;
3867 EXIT WHEN "i" = "dimension_v";
3868 "i" := "i" + 1;
3869 END LOOP;
3870 "i" := 1;
3871 LOOP
3872 "rank_ary"["winners_ary"["i"]] := "rank_v";
3873 "done_v" := "done_v" + 1;
3874 EXIT WHEN "i" = array_upper("winners_ary", 1);
3875 "i" := "i" + 1;
3876 END LOOP;
3877 EXIT WHEN "done_v" = "dimension_v";
3878 "rank_v" := "rank_v" + 1;
3879 END LOOP;
3880 -- write preliminary results:
3881 "i" := 1;
3882 FOR "initiative_id_v" IN
3883 SELECT "id" FROM "initiative"
3884 WHERE "issue_id" = "issue_id_p" AND "admitted"
3885 ORDER BY "id"
3886 LOOP
3887 UPDATE "initiative" SET
3888 "direct_majority" =
3889 CASE WHEN "policy_row"."direct_majority_strict" THEN
3890 "positive_votes" * "policy_row"."direct_majority_den" >
3891 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
3892 ELSE
3893 "positive_votes" * "policy_row"."direct_majority_den" >=
3894 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
3895 END
3896 AND "positive_votes" >= "policy_row"."direct_majority_positive"
3897 AND "issue_row"."voter_count"-"negative_votes" >=
3898 "policy_row"."direct_majority_non_negative",
3899 "indirect_majority" =
3900 CASE WHEN "policy_row"."indirect_majority_strict" THEN
3901 "positive_votes" * "policy_row"."indirect_majority_den" >
3902 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
3903 ELSE
3904 "positive_votes" * "policy_row"."indirect_majority_den" >=
3905 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
3906 END
3907 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
3908 AND "issue_row"."voter_count"-"negative_votes" >=
3909 "policy_row"."indirect_majority_non_negative",
3910 "schulze_rank" = "rank_ary"["i"],
3911 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
3912 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
3913 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
3914 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,
3915 "eligible" = FALSE,
3916 "winner" = FALSE,
3917 "rank" = NULL -- NOTE: in cases of manual reset of issue state
3918 WHERE "id" = "initiative_id_v";
3919 "i" := "i" + 1;
3920 END LOOP;
3921 IF "i" != "dimension_v" THEN
3922 RAISE EXCEPTION 'Wrong winner count (should not happen)';
3923 END IF;
3924 -- take indirect majorities into account:
3925 LOOP
3926 UPDATE "initiative" SET "indirect_majority" = TRUE
3927 FROM (
3928 SELECT "new_initiative"."id" AS "initiative_id"
3929 FROM "initiative" "old_initiative"
3930 JOIN "initiative" "new_initiative"
3931 ON "new_initiative"."issue_id" = "issue_id_p"
3932 AND "new_initiative"."indirect_majority" = FALSE
3933 JOIN "battle" "battle_win"
3934 ON "battle_win"."issue_id" = "issue_id_p"
3935 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
3936 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
3937 JOIN "battle" "battle_lose"
3938 ON "battle_lose"."issue_id" = "issue_id_p"
3939 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
3940 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
3941 WHERE "old_initiative"."issue_id" = "issue_id_p"
3942 AND "old_initiative"."indirect_majority" = TRUE
3943 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
3944 "battle_win"."count" * "policy_row"."indirect_majority_den" >
3945 "policy_row"."indirect_majority_num" *
3946 ("battle_win"."count"+"battle_lose"."count")
3947 ELSE
3948 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
3949 "policy_row"."indirect_majority_num" *
3950 ("battle_win"."count"+"battle_lose"."count")
3951 END
3952 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
3953 AND "issue_row"."voter_count"-"battle_lose"."count" >=
3954 "policy_row"."indirect_majority_non_negative"
3955 ) AS "subquery"
3956 WHERE "id" = "subquery"."initiative_id";
3957 EXIT WHEN NOT FOUND;
3958 END LOOP;
3959 -- set "multistage_majority" for remaining matching initiatives:
3960 UPDATE "initiative" SET "multistage_majority" = TRUE
3961 FROM (
3962 SELECT "losing_initiative"."id" AS "initiative_id"
3963 FROM "initiative" "losing_initiative"
3964 JOIN "initiative" "winning_initiative"
3965 ON "winning_initiative"."issue_id" = "issue_id_p"
3966 AND "winning_initiative"."admitted"
3967 JOIN "battle" "battle_win"
3968 ON "battle_win"."issue_id" = "issue_id_p"
3969 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
3970 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
3971 JOIN "battle" "battle_lose"
3972 ON "battle_lose"."issue_id" = "issue_id_p"
3973 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
3974 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
3975 WHERE "losing_initiative"."issue_id" = "issue_id_p"
3976 AND "losing_initiative"."admitted"
3977 AND "winning_initiative"."schulze_rank" <
3978 "losing_initiative"."schulze_rank"
3979 AND "battle_win"."count" > "battle_lose"."count"
3980 AND (
3981 "battle_win"."count" > "winning_initiative"."positive_votes" OR
3982 "battle_lose"."count" < "losing_initiative"."negative_votes" )
3983 ) AS "subquery"
3984 WHERE "id" = "subquery"."initiative_id";
3985 -- mark eligible initiatives:
3986 UPDATE "initiative" SET "eligible" = TRUE
3987 WHERE "issue_id" = "issue_id_p"
3988 AND "initiative"."direct_majority"
3989 AND "initiative"."indirect_majority"
3990 AND "initiative"."better_than_status_quo"
3991 AND (
3992 "policy_row"."no_multistage_majority" = FALSE OR
3993 "initiative"."multistage_majority" = FALSE )
3994 AND (
3995 "policy_row"."no_reverse_beat_path" = FALSE OR
3996 "initiative"."reverse_beat_path" = FALSE );
3997 -- mark final winner:
3998 UPDATE "initiative" SET "winner" = TRUE
3999 FROM (
4000 SELECT "id" AS "initiative_id"
4001 FROM "initiative"
4002 WHERE "issue_id" = "issue_id_p" AND "eligible"
4003 ORDER BY
4004 "schulze_rank",
4005 "id"
4006 LIMIT 1
4007 ) AS "subquery"
4008 WHERE "id" = "subquery"."initiative_id";
4009 -- write (final) ranks:
4010 "rank_v" := 1;
4011 FOR "initiative_id_v" IN
4012 SELECT "id"
4013 FROM "initiative"
4014 WHERE "issue_id" = "issue_id_p" AND "admitted"
4015 ORDER BY
4016 "winner" DESC,
4017 "eligible" DESC,
4018 "schulze_rank",
4019 "id"
4020 LOOP
4021 UPDATE "initiative" SET "rank" = "rank_v"
4022 WHERE "id" = "initiative_id_v";
4023 "rank_v" := "rank_v" + 1;
4024 END LOOP;
4025 -- set schulze rank of status quo and mark issue as finished:
4026 UPDATE "issue" SET
4027 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
4028 "state" =
4029 CASE WHEN EXISTS (
4030 SELECT NULL FROM "initiative"
4031 WHERE "issue_id" = "issue_id_p" AND "winner"
4032 ) THEN
4033 'finished_with_winner'::"issue_state"
4034 ELSE
4035 'finished_without_winner'::"issue_state"
4036 END,
4037 "closed" = "phase_finished",
4038 "phase_finished" = NULL
4039 WHERE "id" = "issue_id_p";
4040 RETURN;
4041 END;
4042 $$;
4044 COMMENT ON FUNCTION "calculate_ranks"
4045 ( "issue"."id"%TYPE )
4046 IS 'Determine ranking (Votes have to be counted first)';
4050 -----------------------------
4051 -- Automatic state changes --
4052 -----------------------------
4055 CREATE TYPE "check_issue_persistence" AS (
4056 "state" "issue_state",
4057 "phase_finished" BOOLEAN,
4058 "issue_revoked" BOOLEAN,
4059 "snapshot_created" BOOLEAN,
4060 "harmonic_weights_set" BOOLEAN,
4061 "closed_voting" BOOLEAN );
4063 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';
4066 CREATE FUNCTION "check_issue"
4067 ( "issue_id_p" "issue"."id"%TYPE,
4068 "persist" "check_issue_persistence" )
4069 RETURNS "check_issue_persistence"
4070 LANGUAGE 'plpgsql' VOLATILE AS $$
4071 DECLARE
4072 "issue_row" "issue"%ROWTYPE;
4073 "policy_row" "policy"%ROWTYPE;
4074 "initiative_row" "initiative"%ROWTYPE;
4075 "state_v" "issue_state";
4076 BEGIN
4077 PERFORM "require_transaction_isolation"();
4078 IF "persist" ISNULL THEN
4079 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4080 FOR UPDATE;
4081 IF "issue_row"."closed" NOTNULL THEN
4082 RETURN NULL;
4083 END IF;
4084 "persist"."state" := "issue_row"."state";
4085 IF
4086 ( "issue_row"."state" = 'admission' AND now() >=
4087 "issue_row"."created" + "issue_row"."admission_time" ) OR
4088 ( "issue_row"."state" = 'discussion' AND now() >=
4089 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
4090 ( "issue_row"."state" = 'verification' AND now() >=
4091 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
4092 ( "issue_row"."state" = 'voting' AND now() >=
4093 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
4094 THEN
4095 "persist"."phase_finished" := TRUE;
4096 ELSE
4097 "persist"."phase_finished" := FALSE;
4098 END IF;
4099 IF
4100 NOT EXISTS (
4101 -- all initiatives are revoked
4102 SELECT NULL FROM "initiative"
4103 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4104 ) AND (
4105 -- and issue has not been accepted yet
4106 "persist"."state" = 'admission' OR
4107 -- or verification time has elapsed
4108 ( "persist"."state" = 'verification' AND
4109 "persist"."phase_finished" ) OR
4110 -- or no initiatives have been revoked lately
4111 NOT EXISTS (
4112 SELECT NULL FROM "initiative"
4113 WHERE "issue_id" = "issue_id_p"
4114 AND now() < "revoked" + "issue_row"."verification_time"
4115 )
4116 )
4117 THEN
4118 "persist"."issue_revoked" := TRUE;
4119 ELSE
4120 "persist"."issue_revoked" := FALSE;
4121 END IF;
4122 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
4123 UPDATE "issue" SET "phase_finished" = now()
4124 WHERE "id" = "issue_row"."id";
4125 RETURN "persist";
4126 ELSIF
4127 "persist"."state" IN ('admission', 'discussion', 'verification')
4128 THEN
4129 RETURN "persist";
4130 ELSE
4131 RETURN NULL;
4132 END IF;
4133 END IF;
4134 IF
4135 "persist"."state" IN ('admission', 'discussion', 'verification') AND
4136 coalesce("persist"."snapshot_created", FALSE) = FALSE
4137 THEN
4138 PERFORM "create_snapshot"("issue_id_p");
4139 "persist"."snapshot_created" = TRUE;
4140 IF "persist"."phase_finished" THEN
4141 IF "persist"."state" = 'admission' THEN
4142 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
4143 ELSIF "persist"."state" = 'discussion' THEN
4144 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
4145 ELSIF "persist"."state" = 'verification' THEN
4146 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
4147 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4148 SELECT * INTO "policy_row" FROM "policy"
4149 WHERE "id" = "issue_row"."policy_id";
4150 FOR "initiative_row" IN
4151 SELECT * FROM "initiative"
4152 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4153 FOR UPDATE
4154 LOOP
4155 IF
4156 "initiative_row"."polling" OR (
4157 "initiative_row"."satisfied_supporter_count" > 0 AND
4158 "initiative_row"."satisfied_supporter_count" *
4159 "policy_row"."initiative_quorum_den" >=
4160 "issue_row"."population" * "policy_row"."initiative_quorum_num"
4161 )
4162 THEN
4163 UPDATE "initiative" SET "admitted" = TRUE
4164 WHERE "id" = "initiative_row"."id";
4165 ELSE
4166 UPDATE "initiative" SET "admitted" = FALSE
4167 WHERE "id" = "initiative_row"."id";
4168 END IF;
4169 END LOOP;
4170 END IF;
4171 END IF;
4172 RETURN "persist";
4173 END IF;
4174 IF
4175 "persist"."state" IN ('admission', 'discussion', 'verification') AND
4176 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
4177 THEN
4178 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
4179 "persist"."harmonic_weights_set" = TRUE;
4180 IF
4181 "persist"."phase_finished" OR
4182 "persist"."issue_revoked" OR
4183 "persist"."state" = 'admission'
4184 THEN
4185 RETURN "persist";
4186 ELSE
4187 RETURN NULL;
4188 END IF;
4189 END IF;
4190 IF "persist"."issue_revoked" THEN
4191 IF "persist"."state" = 'admission' THEN
4192 "state_v" := 'canceled_revoked_before_accepted';
4193 ELSIF "persist"."state" = 'discussion' THEN
4194 "state_v" := 'canceled_after_revocation_during_discussion';
4195 ELSIF "persist"."state" = 'verification' THEN
4196 "state_v" := 'canceled_after_revocation_during_verification';
4197 END IF;
4198 UPDATE "issue" SET
4199 "state" = "state_v",
4200 "closed" = "phase_finished",
4201 "phase_finished" = NULL
4202 WHERE "id" = "issue_id_p";
4203 RETURN NULL;
4204 END IF;
4205 IF "persist"."state" = 'admission' THEN
4206 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4207 FOR UPDATE;
4208 SELECT * INTO "policy_row"
4209 FROM "policy" WHERE "id" = "issue_row"."policy_id";
4210 IF EXISTS (
4211 SELECT NULL FROM "initiative"
4212 WHERE "issue_id" = "issue_id_p"
4213 AND "supporter_count" > 0
4214 AND "supporter_count" * "policy_row"."issue_quorum_den"
4215 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
4216 ) THEN
4217 UPDATE "issue" SET
4218 "state" = 'discussion',
4219 "accepted" = coalesce("phase_finished", now()),
4220 "phase_finished" = NULL
4221 WHERE "id" = "issue_id_p";
4222 ELSIF "issue_row"."phase_finished" NOTNULL THEN
4223 UPDATE "issue" SET
4224 "state" = 'canceled_issue_not_accepted',
4225 "closed" = "phase_finished",
4226 "phase_finished" = NULL
4227 WHERE "id" = "issue_id_p";
4228 END IF;
4229 RETURN NULL;
4230 END IF;
4231 IF "persist"."phase_finished" THEN
4232 if "persist"."state" = 'discussion' THEN
4233 UPDATE "issue" SET
4234 "state" = 'verification',
4235 "half_frozen" = "phase_finished",
4236 "phase_finished" = NULL
4237 WHERE "id" = "issue_id_p";
4238 RETURN NULL;
4239 END IF;
4240 IF "persist"."state" = 'verification' THEN
4241 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4242 FOR UPDATE;
4243 SELECT * INTO "policy_row" FROM "policy"
4244 WHERE "id" = "issue_row"."policy_id";
4245 IF EXISTS (
4246 SELECT NULL FROM "initiative"
4247 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
4248 ) THEN
4249 UPDATE "issue" SET
4250 "state" = 'voting',
4251 "fully_frozen" = "phase_finished",
4252 "phase_finished" = NULL
4253 WHERE "id" = "issue_id_p";
4254 ELSE
4255 UPDATE "issue" SET
4256 "state" = 'canceled_no_initiative_admitted',
4257 "fully_frozen" = "phase_finished",
4258 "closed" = "phase_finished",
4259 "phase_finished" = NULL
4260 WHERE "id" = "issue_id_p";
4261 -- NOTE: The following DELETE statements have effect only when
4262 -- issue state has been manipulated
4263 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
4264 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
4265 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
4266 END IF;
4267 RETURN NULL;
4268 END IF;
4269 IF "persist"."state" = 'voting' THEN
4270 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
4271 PERFORM "close_voting"("issue_id_p");
4272 "persist"."closed_voting" = TRUE;
4273 RETURN "persist";
4274 END IF;
4275 PERFORM "calculate_ranks"("issue_id_p");
4276 RETURN NULL;
4277 END IF;
4278 END IF;
4279 RAISE WARNING 'should not happen';
4280 RETURN NULL;
4281 END;
4282 $$;
4284 COMMENT ON FUNCTION "check_issue"
4285 ( "issue"."id"%TYPE,
4286 "check_issue_persistence" )
4287 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")';
4290 CREATE FUNCTION "check_everything"()
4291 RETURNS VOID
4292 LANGUAGE 'plpgsql' VOLATILE AS $$
4293 DECLARE
4294 "issue_id_v" "issue"."id"%TYPE;
4295 "persist_v" "check_issue_persistence";
4296 BEGIN
4297 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
4298 DELETE FROM "expired_session";
4299 PERFORM "check_activity"();
4300 PERFORM "calculate_member_counts"();
4301 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
4302 "persist_v" := NULL;
4303 LOOP
4304 "persist_v" := "check_issue"("issue_id_v", "persist_v");
4305 EXIT WHEN "persist_v" ISNULL;
4306 END LOOP;
4307 END LOOP;
4308 RETURN;
4309 END;
4310 $$;
4312 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.';
4316 ----------------------
4317 -- Deletion of data --
4318 ----------------------
4321 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
4322 RETURNS VOID
4323 LANGUAGE 'plpgsql' VOLATILE AS $$
4324 DECLARE
4325 "issue_row" "issue"%ROWTYPE;
4326 BEGIN
4327 SELECT * INTO "issue_row"
4328 FROM "issue" WHERE "id" = "issue_id_p"
4329 FOR UPDATE;
4330 IF "issue_row"."cleaned" ISNULL THEN
4331 UPDATE "issue" SET
4332 "state" = 'voting',
4333 "closed" = NULL
4334 WHERE "id" = "issue_id_p";
4335 DELETE FROM "delegating_voter"
4336 WHERE "issue_id" = "issue_id_p";
4337 DELETE FROM "direct_voter"
4338 WHERE "issue_id" = "issue_id_p";
4339 DELETE FROM "delegating_interest_snapshot"
4340 WHERE "issue_id" = "issue_id_p";
4341 DELETE FROM "direct_interest_snapshot"
4342 WHERE "issue_id" = "issue_id_p";
4343 DELETE FROM "delegating_population_snapshot"
4344 WHERE "issue_id" = "issue_id_p";
4345 DELETE FROM "direct_population_snapshot"
4346 WHERE "issue_id" = "issue_id_p";
4347 DELETE FROM "non_voter"
4348 WHERE "issue_id" = "issue_id_p";
4349 DELETE FROM "delegation"
4350 WHERE "issue_id" = "issue_id_p";
4351 DELETE FROM "supporter"
4352 USING "initiative" -- NOTE: due to missing index on issue_id
4353 WHERE "initiative"."issue_id" = "issue_id_p"
4354 AND "supporter"."initiative_id" = "initiative_id";
4355 UPDATE "issue" SET
4356 "state" = "issue_row"."state",
4357 "closed" = "issue_row"."closed",
4358 "cleaned" = now()
4359 WHERE "id" = "issue_id_p";
4360 END IF;
4361 RETURN;
4362 END;
4363 $$;
4365 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
4368 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
4369 RETURNS VOID
4370 LANGUAGE 'plpgsql' VOLATILE AS $$
4371 BEGIN
4372 UPDATE "member" SET
4373 "last_login" = NULL,
4374 "login" = NULL,
4375 "password" = NULL,
4376 "locked" = TRUE,
4377 "active" = FALSE,
4378 "notify_email" = NULL,
4379 "notify_email_unconfirmed" = NULL,
4380 "notify_email_secret" = NULL,
4381 "notify_email_secret_expiry" = NULL,
4382 "notify_email_lock_expiry" = NULL,
4383 "password_reset_secret" = NULL,
4384 "password_reset_secret_expiry" = NULL,
4385 "organizational_unit" = NULL,
4386 "internal_posts" = NULL,
4387 "realname" = NULL,
4388 "birthday" = NULL,
4389 "address" = NULL,
4390 "email" = NULL,
4391 "xmpp_address" = NULL,
4392 "website" = NULL,
4393 "phone" = NULL,
4394 "mobile_phone" = NULL,
4395 "profession" = NULL,
4396 "external_memberships" = NULL,
4397 "external_posts" = NULL,
4398 "statement" = NULL
4399 WHERE "id" = "member_id_p";
4400 -- "text_search_data" is updated by triggers
4401 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
4402 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
4403 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
4404 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
4405 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
4406 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
4407 DELETE FROM "session" WHERE "member_id" = "member_id_p";
4408 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
4409 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
4410 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
4411 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
4412 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
4413 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
4414 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
4415 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
4416 DELETE FROM "direct_voter" USING "issue"
4417 WHERE "direct_voter"."issue_id" = "issue"."id"
4418 AND "issue"."closed" ISNULL
4419 AND "member_id" = "member_id_p";
4420 RETURN;
4421 END;
4422 $$;
4424 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)';
4427 CREATE FUNCTION "delete_private_data"()
4428 RETURNS VOID
4429 LANGUAGE 'plpgsql' VOLATILE AS $$
4430 BEGIN
4431 DELETE FROM "member" WHERE "activated" ISNULL;
4432 UPDATE "member" SET
4433 "invite_code" = NULL,
4434 "invite_code_expiry" = NULL,
4435 "admin_comment" = NULL,
4436 "last_login" = NULL,
4437 "login" = NULL,
4438 "password" = NULL,
4439 "lang" = NULL,
4440 "notify_email" = NULL,
4441 "notify_email_unconfirmed" = NULL,
4442 "notify_email_secret" = NULL,
4443 "notify_email_secret_expiry" = NULL,
4444 "notify_email_lock_expiry" = NULL,
4445 "notify_level" = NULL,
4446 "password_reset_secret" = NULL,
4447 "password_reset_secret_expiry" = NULL,
4448 "organizational_unit" = NULL,
4449 "internal_posts" = NULL,
4450 "realname" = NULL,
4451 "birthday" = NULL,
4452 "address" = NULL,
4453 "email" = NULL,
4454 "xmpp_address" = NULL,
4455 "website" = NULL,
4456 "phone" = NULL,
4457 "mobile_phone" = NULL,
4458 "profession" = NULL,
4459 "external_memberships" = NULL,
4460 "external_posts" = NULL,
4461 "formatting_engine" = NULL,
4462 "statement" = NULL;
4463 -- "text_search_data" is updated by triggers
4464 DELETE FROM "setting";
4465 DELETE FROM "setting_map";
4466 DELETE FROM "member_relation_setting";
4467 DELETE FROM "member_image";
4468 DELETE FROM "contact";
4469 DELETE FROM "ignored_member";
4470 DELETE FROM "session";
4471 DELETE FROM "area_setting";
4472 DELETE FROM "issue_setting";
4473 DELETE FROM "ignored_initiative";
4474 DELETE FROM "initiative_setting";
4475 DELETE FROM "suggestion_setting";
4476 DELETE FROM "non_voter";
4477 DELETE FROM "direct_voter" USING "issue"
4478 WHERE "direct_voter"."issue_id" = "issue"."id"
4479 AND "issue"."closed" ISNULL;
4480 RETURN;
4481 END;
4482 $$;
4484 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.';
4488 COMMIT;