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