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