liquid_feedback_core
view core.sql @ 424:7fbf614ca8cb
Separate configuration options for "defeat_strength" and "tie_breaking"
| author | jbe |
|---|---|
| date | Mon Apr 14 01:06:42 2014 +0200 (2014-04-14) |
| parents | 73c2ab2d068f |
| children | 514eacf18e36 |
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 ('3.0.2', 3, 0, 2))
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 TYPE "defeat_strength" AS ENUM ('simple', 'tuple');
353 COMMENT ON TYPE "defeat_strength" IS 'How pairwise defeats are measured for the Schulze method: ''simple'' = only the number of winning votes, ''tuple'' = primarily the number of winning votes, secondarily the number of losing votes';
356 CREATE TYPE "tie_breaking" AS ENUM ('simple', 'variant1', 'variant2');
358 COMMENT ON TYPE "tie_breaking" IS 'Tie-breaker for the Schulze method: ''simple'' = only initiative ids are used, ''variant1'' = use initiative ids in variant 1 for tie breaking of the links (TBRL) and sequentially forbid shared links, ''variant2'' = use initiative ids in variant 2 for tie breaking of the links (TBRL) and sequentially forbid shared links';
362 CREATE TABLE "policy" (
363 "id" SERIAL4 PRIMARY KEY,
364 "index" INT4 NOT NULL,
365 "active" BOOLEAN NOT NULL DEFAULT TRUE,
366 "name" TEXT NOT NULL UNIQUE,
367 "description" TEXT NOT NULL DEFAULT '',
368 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
369 "admission_time" INTERVAL,
370 "discussion_time" INTERVAL,
371 "verification_time" INTERVAL,
372 "voting_time" INTERVAL,
373 "issue_quorum_num" INT4,
374 "issue_quorum_den" INT4,
375 "initiative_quorum_num" INT4 NOT NULL,
376 "initiative_quorum_den" INT4 NOT NULL,
377 "defeat_strength" "defeat_strength" NOT NULL DEFAULT 'tuple',
378 "tie_breaking" "tie_breaking" NOT NULL DEFAULT 'variant1',
379 "direct_majority_num" INT4 NOT NULL DEFAULT 1,
380 "direct_majority_den" INT4 NOT NULL DEFAULT 2,
381 "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
382 "direct_majority_positive" INT4 NOT NULL DEFAULT 0,
383 "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0,
384 "indirect_majority_num" INT4 NOT NULL DEFAULT 1,
385 "indirect_majority_den" INT4 NOT NULL DEFAULT 2,
386 "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
387 "indirect_majority_positive" INT4 NOT NULL DEFAULT 0,
388 "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0,
389 "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT FALSE,
390 "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE,
391 CONSTRAINT "timing" CHECK (
392 ( "polling" = FALSE AND
393 "admission_time" NOTNULL AND "discussion_time" NOTNULL AND
394 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
395 ( "polling" = TRUE AND
396 "admission_time" ISNULL AND "discussion_time" NOTNULL AND
397 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
398 ( "polling" = TRUE AND
399 "admission_time" ISNULL AND "discussion_time" ISNULL AND
400 "verification_time" ISNULL AND "voting_time" ISNULL ) ),
401 CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK (
402 "polling" = "issue_quorum_num" ISNULL AND
403 "polling" = "issue_quorum_den" ISNULL ) );
404 CREATE INDEX "policy_active_idx" ON "policy" ("active");
406 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
408 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
409 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
410 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';
411 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';
412 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"';
413 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"';
414 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'')';
415 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''';
416 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''';
417 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
418 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
419 COMMENT ON COLUMN "policy"."defeat_strength" IS 'How pairwise defeats are measured for the Schulze method; see type "defeat_strength"';
420 COMMENT ON COLUMN "policy"."tie_breaking" IS 'Tie-breaker for the Schulze method; see type "tie_breaking"';
421 COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner';
422 COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner';
423 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.';
424 COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner';
425 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';
426 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';
427 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';
428 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.';
429 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';
430 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';
431 COMMENT ON COLUMN "policy"."no_reverse_beat_path" IS 'EXPERIMENTAL FEATURE: 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.';
432 COMMENT ON COLUMN "policy"."no_multistage_majority" IS 'EXPERIMENTAL FEATURE: 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").';
435 CREATE TABLE "unit" (
436 "id" SERIAL4 PRIMARY KEY,
437 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
438 "active" BOOLEAN NOT NULL DEFAULT TRUE,
439 "name" TEXT NOT NULL,
440 "description" TEXT NOT NULL DEFAULT '',
441 "member_count" INT4,
442 "text_search_data" TSVECTOR );
443 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
444 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
445 CREATE INDEX "unit_active_idx" ON "unit" ("active");
446 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
447 CREATE TRIGGER "update_text_search_data"
448 BEFORE INSERT OR UPDATE ON "unit"
449 FOR EACH ROW EXECUTE PROCEDURE
450 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
451 "name", "description" );
453 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
455 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
456 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this unit';
457 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
460 CREATE TABLE "unit_setting" (
461 PRIMARY KEY ("member_id", "key", "unit_id"),
462 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
463 "key" TEXT NOT NULL,
464 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
465 "value" TEXT NOT NULL );
467 COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings';
470 CREATE TABLE "area" (
471 "id" SERIAL4 PRIMARY KEY,
472 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
473 "active" BOOLEAN NOT NULL DEFAULT TRUE,
474 "name" TEXT NOT NULL,
475 "description" TEXT NOT NULL DEFAULT '',
476 "direct_member_count" INT4,
477 "member_weight" INT4,
478 "text_search_data" TSVECTOR );
479 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
480 CREATE INDEX "area_active_idx" ON "area" ("active");
481 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
482 CREATE TRIGGER "update_text_search_data"
483 BEFORE INSERT OR UPDATE ON "area"
484 FOR EACH ROW EXECUTE PROCEDURE
485 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
486 "name", "description" );
488 COMMENT ON TABLE "area" IS 'Subject areas';
490 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
491 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"';
492 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
495 CREATE TABLE "area_setting" (
496 PRIMARY KEY ("member_id", "key", "area_id"),
497 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
498 "key" TEXT NOT NULL,
499 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
500 "value" TEXT NOT NULL );
502 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
505 CREATE TABLE "allowed_policy" (
506 PRIMARY KEY ("area_id", "policy_id"),
507 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
508 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
509 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
510 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
512 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
514 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
517 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
519 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';
522 CREATE TYPE "issue_state" AS ENUM (
523 'admission', 'discussion', 'verification', 'voting',
524 'canceled_by_admin',
525 'canceled_revoked_before_accepted',
526 'canceled_issue_not_accepted',
527 'canceled_after_revocation_during_discussion',
528 'canceled_after_revocation_during_verification',
529 'canceled_no_initiative_admitted',
530 'finished_without_winner', 'finished_with_winner');
532 COMMENT ON TYPE "issue_state" IS 'State of issues';
535 CREATE TABLE "issue" (
536 "id" SERIAL4 PRIMARY KEY,
537 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
538 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
539 "admin_notice" TEXT,
540 "state" "issue_state" NOT NULL DEFAULT 'admission',
541 "phase_finished" TIMESTAMPTZ,
542 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
543 "accepted" TIMESTAMPTZ,
544 "half_frozen" TIMESTAMPTZ,
545 "fully_frozen" TIMESTAMPTZ,
546 "closed" TIMESTAMPTZ,
547 "cleaned" TIMESTAMPTZ,
548 "admission_time" INTERVAL,
549 "discussion_time" INTERVAL NOT NULL,
550 "verification_time" INTERVAL NOT NULL,
551 "voting_time" INTERVAL NOT NULL,
552 "snapshot" TIMESTAMPTZ,
553 "latest_snapshot_event" "snapshot_event",
554 "population" INT4,
555 "voter_count" INT4,
556 "status_quo_schulze_rank" INT4,
557 CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (
558 "admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created") ),
559 CONSTRAINT "valid_state" CHECK (
560 (
561 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
562 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
563 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL ) OR
564 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL)
565 ) AND (
566 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
567 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
568 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
569 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
570 ("state" = 'canceled_by_admin' AND "closed" NOTNULL) OR
571 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
572 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
573 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
574 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
575 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" = "fully_frozen") OR
576 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen") OR
577 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen")
578 )),
579 CONSTRAINT "phase_finished_only_when_not_closed" CHECK (
580 "phase_finished" ISNULL OR "closed" ISNULL ),
581 CONSTRAINT "state_change_order" CHECK (
582 "created" <= "accepted" AND
583 "accepted" <= "half_frozen" AND
584 "half_frozen" <= "fully_frozen" AND
585 "fully_frozen" <= "closed" ),
586 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
587 "cleaned" ISNULL OR "closed" NOTNULL ),
588 CONSTRAINT "last_snapshot_on_full_freeze"
589 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
590 CONSTRAINT "freeze_requires_snapshot"
591 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
592 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
593 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
594 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
595 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
596 CREATE INDEX "issue_created_idx" ON "issue" ("created");
597 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
598 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
599 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
600 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
601 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
602 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
604 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
606 COMMENT ON COLUMN "issue"."admin_notice" IS 'Public notice by admin to explain manual interventions, or to announce corrections';
607 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';
608 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
609 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.';
610 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.';
611 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.';
612 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
613 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
614 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
615 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
616 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
617 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
618 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';
619 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
620 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';
621 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
624 CREATE TABLE "issue_order_in_admission_state" (
625 "id" INT8 PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
626 "order_in_area" INT4,
627 "order_in_unit" INT4 );
629 COMMENT ON TABLE "issue_order_in_admission_state" IS 'Ordering information for issues that are not stored in the "issue" table to avoid locking of multiple issues at once; Filled/updated by "lf_update_issue_order"';
631 COMMENT ON COLUMN "issue_order_in_admission_state"."id" IS 'References "issue" ("id") but has no referential integrity trigger associated, due to performance/locking issues';
632 COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_area" IS 'Order of issues in admission state within a single area; NULL values sort last';
633 COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_unit" IS 'Order of issues in admission state within all areas of a unit; NULL values sort last';
636 CREATE TABLE "issue_setting" (
637 PRIMARY KEY ("member_id", "key", "issue_id"),
638 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
639 "key" TEXT NOT NULL,
640 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
641 "value" TEXT NOT NULL );
643 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
646 CREATE TABLE "initiative" (
647 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
648 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
649 "id" SERIAL4 PRIMARY KEY,
650 "name" TEXT NOT NULL,
651 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
652 "discussion_url" TEXT,
653 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
654 "revoked" TIMESTAMPTZ,
655 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
656 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
657 "admitted" BOOLEAN,
658 "supporter_count" INT4,
659 "informed_supporter_count" INT4,
660 "satisfied_supporter_count" INT4,
661 "satisfied_informed_supporter_count" INT4,
662 "harmonic_weight" NUMERIC(12, 3),
663 "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE,
664 "first_preference_votes" INT4,
665 "positive_votes" INT4,
666 "negative_votes" INT4,
667 "direct_majority" BOOLEAN,
668 "indirect_majority" BOOLEAN,
669 "schulze_rank" INT4,
670 "better_than_status_quo" BOOLEAN,
671 "worse_than_status_quo" BOOLEAN,
672 "reverse_beat_path" BOOLEAN,
673 "multistage_majority" BOOLEAN,
674 "eligible" BOOLEAN,
675 "winner" BOOLEAN,
676 "rank" INT4,
677 "text_search_data" TSVECTOR,
678 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
679 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
680 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
681 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
682 CONSTRAINT "revoked_initiatives_cant_be_admitted"
683 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
684 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
685 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
686 ( "first_preference_votes" ISNULL AND
687 "positive_votes" ISNULL AND "negative_votes" ISNULL AND
688 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
689 "schulze_rank" ISNULL AND
690 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
691 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
692 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ),
693 CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")),
694 CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (
695 "eligible" = FALSE OR
696 ("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ),
697 CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE),
698 CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1),
699 CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE),
700 CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") );
701 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
702 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
703 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
704 CREATE TRIGGER "update_text_search_data"
705 BEFORE INSERT OR UPDATE ON "initiative"
706 FOR EACH ROW EXECUTE PROCEDURE
707 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
708 "name", "discussion_url");
710 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.';
712 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative does not need to pass the initiative quorum (see "policy"."polling")';
713 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
714 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
715 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoke the initiative';
716 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
717 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
718 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
719 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
720 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
721 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';
722 COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time';
723 COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice';
724 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Number of direct and delegating voters who ranked this initiative better than the status quo';
725 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo';
726 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"';
727 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths';
728 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking';
729 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo';
730 COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (DEPRECATED, since schulze-ranking is unique per issue; use "better_than_status_quo"=FALSE)';
731 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';
732 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';
733 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"';
734 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank"';
735 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';
738 CREATE TABLE "battle" (
739 "issue_id" INT4 NOT NULL,
740 "winning_initiative_id" INT4,
741 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
742 "losing_initiative_id" INT4,
743 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
744 "count" INT4 NOT NULL,
745 CONSTRAINT "initiative_ids_not_equal" CHECK (
746 "winning_initiative_id" != "losing_initiative_id" OR
747 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
748 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) );
749 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
750 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
751 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
753 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';
756 CREATE TABLE "ignored_initiative" (
757 PRIMARY KEY ("initiative_id", "member_id"),
758 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
759 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
760 CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
762 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
765 CREATE TABLE "initiative_setting" (
766 PRIMARY KEY ("member_id", "key", "initiative_id"),
767 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
768 "key" TEXT NOT NULL,
769 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
770 "value" TEXT NOT NULL );
772 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
775 CREATE TABLE "draft" (
776 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
777 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
778 "id" SERIAL8 PRIMARY KEY,
779 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
780 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
781 "formatting_engine" TEXT,
782 "content" TEXT NOT NULL,
783 "text_search_data" TSVECTOR );
784 CREATE INDEX "draft_created_idx" ON "draft" ("created");
785 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
786 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
787 CREATE TRIGGER "update_text_search_data"
788 BEFORE INSERT OR UPDATE ON "draft"
789 FOR EACH ROW EXECUTE PROCEDURE
790 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
792 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.';
794 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
795 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
798 CREATE TABLE "rendered_draft" (
799 PRIMARY KEY ("draft_id", "format"),
800 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
801 "format" TEXT,
802 "content" TEXT NOT NULL );
804 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)';
807 CREATE TABLE "suggestion" (
808 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
809 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
810 "id" SERIAL8 PRIMARY KEY,
811 "draft_id" INT8 NOT NULL,
812 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
813 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
814 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
815 "name" TEXT NOT NULL,
816 "formatting_engine" TEXT,
817 "content" TEXT NOT NULL DEFAULT '',
818 "text_search_data" TSVECTOR,
819 "minus2_unfulfilled_count" INT4,
820 "minus2_fulfilled_count" INT4,
821 "minus1_unfulfilled_count" INT4,
822 "minus1_fulfilled_count" INT4,
823 "plus1_unfulfilled_count" INT4,
824 "plus1_fulfilled_count" INT4,
825 "plus2_unfulfilled_count" INT4,
826 "plus2_fulfilled_count" INT4,
827 "proportional_order" INT4 );
828 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
829 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
830 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
831 CREATE TRIGGER "update_text_search_data"
832 BEFORE INSERT OR UPDATE ON "suggestion"
833 FOR EACH ROW EXECUTE PROCEDURE
834 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
835 "name", "content");
837 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';
839 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")';
840 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
841 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
842 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
843 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
844 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
845 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
846 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
847 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
848 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"';
851 CREATE TABLE "rendered_suggestion" (
852 PRIMARY KEY ("suggestion_id", "format"),
853 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
854 "format" TEXT,
855 "content" TEXT NOT NULL );
857 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)';
860 CREATE TABLE "suggestion_setting" (
861 PRIMARY KEY ("member_id", "key", "suggestion_id"),
862 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
863 "key" TEXT NOT NULL,
864 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
865 "value" TEXT NOT NULL );
867 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
870 CREATE TABLE "privilege" (
871 PRIMARY KEY ("unit_id", "member_id"),
872 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
873 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
874 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
875 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
876 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
877 "member_manager" BOOLEAN NOT NULL DEFAULT FALSE,
878 "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE,
879 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE,
880 "polling_right" BOOLEAN NOT NULL DEFAULT FALSE );
882 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
884 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke any privileges to/from other members';
885 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create and disable sub units';
886 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create and disable areas and set area parameters';
887 COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';
888 COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';
889 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote';
890 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';
893 CREATE TABLE "membership" (
894 PRIMARY KEY ("area_id", "member_id"),
895 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
896 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
897 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
899 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
902 CREATE TABLE "interest" (
903 PRIMARY KEY ("issue_id", "member_id"),
904 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
905 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
906 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
908 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.';
911 CREATE TABLE "initiator" (
912 PRIMARY KEY ("initiative_id", "member_id"),
913 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
914 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
915 "accepted" BOOLEAN );
916 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
918 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.';
920 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.';
923 CREATE TABLE "supporter" (
924 "issue_id" INT4 NOT NULL,
925 PRIMARY KEY ("initiative_id", "member_id"),
926 "initiative_id" INT4,
927 "member_id" INT4,
928 "draft_id" INT8 NOT NULL,
929 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
930 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE );
931 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
933 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.';
935 COMMENT ON COLUMN "supporter"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
936 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")';
939 CREATE TABLE "opinion" (
940 "initiative_id" INT4 NOT NULL,
941 PRIMARY KEY ("suggestion_id", "member_id"),
942 "suggestion_id" INT8,
943 "member_id" INT4,
944 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
945 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
946 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
947 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
948 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
950 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.';
952 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
955 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
957 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
960 CREATE TABLE "delegation" (
961 "id" SERIAL8 PRIMARY KEY,
962 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
963 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
964 "scope" "delegation_scope" NOT NULL,
965 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
966 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
967 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
968 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
969 CONSTRAINT "no_unit_delegation_to_null"
970 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
971 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
972 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
973 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
974 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
975 UNIQUE ("unit_id", "truster_id"),
976 UNIQUE ("area_id", "truster_id"),
977 UNIQUE ("issue_id", "truster_id") );
978 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
979 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
981 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
983 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
984 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
985 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
988 CREATE TABLE "direct_population_snapshot" (
989 PRIMARY KEY ("issue_id", "event", "member_id"),
990 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
991 "event" "snapshot_event",
992 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
993 "weight" INT4 );
994 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
996 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';
998 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
999 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
1002 CREATE TABLE "delegating_population_snapshot" (
1003 PRIMARY KEY ("issue_id", "event", "member_id"),
1004 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1005 "event" "snapshot_event",
1006 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1007 "weight" INT4,
1008 "scope" "delegation_scope" NOT NULL,
1009 "delegate_member_ids" INT4[] NOT NULL );
1010 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
1012 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';
1014 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1015 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
1016 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
1017 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"';
1020 CREATE TABLE "direct_interest_snapshot" (
1021 PRIMARY KEY ("issue_id", "event", "member_id"),
1022 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1023 "event" "snapshot_event",
1024 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1025 "weight" INT4 );
1026 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
1028 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';
1030 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1031 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
1034 CREATE TABLE "delegating_interest_snapshot" (
1035 PRIMARY KEY ("issue_id", "event", "member_id"),
1036 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1037 "event" "snapshot_event",
1038 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1039 "weight" INT4,
1040 "scope" "delegation_scope" NOT NULL,
1041 "delegate_member_ids" INT4[] NOT NULL );
1042 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
1044 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';
1046 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1047 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
1048 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
1049 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"';
1052 CREATE TABLE "direct_supporter_snapshot" (
1053 "issue_id" INT4 NOT NULL,
1054 PRIMARY KEY ("initiative_id", "event", "member_id"),
1055 "initiative_id" INT4,
1056 "event" "snapshot_event",
1057 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1058 "draft_id" INT8 NOT NULL,
1059 "informed" BOOLEAN NOT NULL,
1060 "satisfied" BOOLEAN NOT NULL,
1061 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1062 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
1063 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1064 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
1066 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';
1068 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';
1069 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1070 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
1071 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
1074 CREATE TABLE "non_voter" (
1075 PRIMARY KEY ("issue_id", "member_id"),
1076 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1077 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
1078 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
1080 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
1083 CREATE TABLE "direct_voter" (
1084 PRIMARY KEY ("issue_id", "member_id"),
1085 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1086 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1087 "weight" INT4,
1088 "comment_changed" TIMESTAMPTZ,
1089 "formatting_engine" TEXT,
1090 "comment" TEXT,
1091 "text_search_data" TSVECTOR );
1092 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
1093 CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data");
1094 CREATE TRIGGER "update_text_search_data"
1095 BEFORE INSERT OR UPDATE ON "direct_voter"
1096 FOR EACH ROW EXECUTE PROCEDURE
1097 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment");
1099 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';
1101 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
1102 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';
1103 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';
1104 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.';
1107 CREATE TABLE "rendered_voter_comment" (
1108 PRIMARY KEY ("issue_id", "member_id", "format"),
1109 FOREIGN KEY ("issue_id", "member_id")
1110 REFERENCES "direct_voter" ("issue_id", "member_id")
1111 ON DELETE CASCADE ON UPDATE CASCADE,
1112 "issue_id" INT4,
1113 "member_id" INT4,
1114 "format" TEXT,
1115 "content" TEXT NOT NULL );
1117 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)';
1120 CREATE TABLE "delegating_voter" (
1121 PRIMARY KEY ("issue_id", "member_id"),
1122 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1123 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1124 "weight" INT4,
1125 "scope" "delegation_scope" NOT NULL,
1126 "delegate_member_ids" INT4[] NOT NULL );
1127 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
1129 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';
1131 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
1132 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
1133 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"';
1136 CREATE TABLE "vote" (
1137 "issue_id" INT4 NOT NULL,
1138 PRIMARY KEY ("initiative_id", "member_id"),
1139 "initiative_id" INT4,
1140 "member_id" INT4,
1141 "grade" INT4 NOT NULL,
1142 "first_preference" BOOLEAN,
1143 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1144 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
1145 CONSTRAINT "first_preference_flag_only_set_on_positive_grades"
1146 CHECK ("grade" > 0 OR "first_preference" ISNULL) );
1147 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
1149 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';
1151 COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
1152 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.';
1153 COMMENT ON COLUMN "vote"."first_preference" IS 'Value is automatically set after voting is finished. For positive grades, this value is set to true for the highest (i.e. best) grade.';
1156 CREATE TYPE "event_type" AS ENUM (
1157 'issue_state_changed',
1158 'initiative_created_in_new_issue',
1159 'initiative_created_in_existing_issue',
1160 'initiative_revoked',
1161 'new_draft_created',
1162 'suggestion_created');
1164 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
1167 CREATE TABLE "event" (
1168 "id" SERIAL8 PRIMARY KEY,
1169 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
1170 "event" "event_type" NOT NULL,
1171 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1172 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1173 "state" "issue_state",
1174 "initiative_id" INT4,
1175 "draft_id" INT8,
1176 "suggestion_id" INT8,
1177 FOREIGN KEY ("issue_id", "initiative_id")
1178 REFERENCES "initiative" ("issue_id", "id")
1179 ON DELETE CASCADE ON UPDATE CASCADE,
1180 FOREIGN KEY ("initiative_id", "draft_id")
1181 REFERENCES "draft" ("initiative_id", "id")
1182 ON DELETE CASCADE ON UPDATE CASCADE,
1183 FOREIGN KEY ("initiative_id", "suggestion_id")
1184 REFERENCES "suggestion" ("initiative_id", "id")
1185 ON DELETE CASCADE ON UPDATE CASCADE,
1186 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
1187 "event" != 'issue_state_changed' OR (
1188 "member_id" ISNULL AND
1189 "issue_id" NOTNULL AND
1190 "state" NOTNULL AND
1191 "initiative_id" ISNULL AND
1192 "draft_id" ISNULL AND
1193 "suggestion_id" ISNULL )),
1194 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
1195 "event" NOT IN (
1196 'initiative_created_in_new_issue',
1197 'initiative_created_in_existing_issue',
1198 'initiative_revoked',
1199 'new_draft_created'
1200 ) OR (
1201 "member_id" NOTNULL AND
1202 "issue_id" NOTNULL AND
1203 "state" NOTNULL AND
1204 "initiative_id" NOTNULL AND
1205 "draft_id" NOTNULL AND
1206 "suggestion_id" ISNULL )),
1207 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
1208 "event" != 'suggestion_created' OR (
1209 "member_id" NOTNULL AND
1210 "issue_id" NOTNULL AND
1211 "state" NOTNULL AND
1212 "initiative_id" NOTNULL AND
1213 "draft_id" ISNULL AND
1214 "suggestion_id" NOTNULL )) );
1215 CREATE INDEX "event_occurrence_idx" ON "event" ("occurrence");
1217 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
1219 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
1220 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
1221 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
1222 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
1225 CREATE TABLE "notification_sent" (
1226 "event_id" INT8 NOT NULL );
1227 CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1));
1229 COMMENT ON TABLE "notification_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';
1230 COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';
1234 ----------------------------------------------
1235 -- Writing of history entries and event log --
1236 ----------------------------------------------
1239 CREATE FUNCTION "write_member_history_trigger"()
1240 RETURNS TRIGGER
1241 LANGUAGE 'plpgsql' VOLATILE AS $$
1242 BEGIN
1243 IF
1244 ( NEW."active" != OLD."active" OR
1245 NEW."name" != OLD."name" ) AND
1246 OLD."activated" NOTNULL
1247 THEN
1248 INSERT INTO "member_history"
1249 ("member_id", "active", "name")
1250 VALUES (NEW."id", OLD."active", OLD."name");
1251 END IF;
1252 RETURN NULL;
1253 END;
1254 $$;
1256 CREATE TRIGGER "write_member_history"
1257 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1258 "write_member_history_trigger"();
1260 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1261 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1264 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1265 RETURNS TRIGGER
1266 LANGUAGE 'plpgsql' VOLATILE AS $$
1267 BEGIN
1268 IF NEW."state" != OLD."state" THEN
1269 INSERT INTO "event" ("event", "issue_id", "state")
1270 VALUES ('issue_state_changed', NEW."id", NEW."state");
1271 END IF;
1272 RETURN NULL;
1273 END;
1274 $$;
1276 CREATE TRIGGER "write_event_issue_state_changed"
1277 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1278 "write_event_issue_state_changed_trigger"();
1280 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1281 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1284 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1285 RETURNS TRIGGER
1286 LANGUAGE 'plpgsql' VOLATILE AS $$
1287 DECLARE
1288 "initiative_row" "initiative"%ROWTYPE;
1289 "issue_row" "issue"%ROWTYPE;
1290 "event_v" "event_type";
1291 BEGIN
1292 SELECT * INTO "initiative_row" FROM "initiative"
1293 WHERE "id" = NEW."initiative_id";
1294 SELECT * INTO "issue_row" FROM "issue"
1295 WHERE "id" = "initiative_row"."issue_id";
1296 IF EXISTS (
1297 SELECT NULL FROM "draft"
1298 WHERE "initiative_id" = NEW."initiative_id"
1299 AND "id" != NEW."id"
1300 ) THEN
1301 "event_v" := 'new_draft_created';
1302 ELSE
1303 IF EXISTS (
1304 SELECT NULL FROM "initiative"
1305 WHERE "issue_id" = "initiative_row"."issue_id"
1306 AND "id" != "initiative_row"."id"
1307 ) THEN
1308 "event_v" := 'initiative_created_in_existing_issue';
1309 ELSE
1310 "event_v" := 'initiative_created_in_new_issue';
1311 END IF;
1312 END IF;
1313 INSERT INTO "event" (
1314 "event", "member_id",
1315 "issue_id", "state", "initiative_id", "draft_id"
1316 ) VALUES (
1317 "event_v",
1318 NEW."author_id",
1319 "initiative_row"."issue_id",
1320 "issue_row"."state",
1321 "initiative_row"."id",
1322 NEW."id" );
1323 RETURN NULL;
1324 END;
1325 $$;
1327 CREATE TRIGGER "write_event_initiative_or_draft_created"
1328 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1329 "write_event_initiative_or_draft_created_trigger"();
1331 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1332 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1335 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1336 RETURNS TRIGGER
1337 LANGUAGE 'plpgsql' VOLATILE AS $$
1338 DECLARE
1339 "issue_row" "issue"%ROWTYPE;
1340 "draft_id_v" "draft"."id"%TYPE;
1341 BEGIN
1342 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1343 SELECT * INTO "issue_row" FROM "issue"
1344 WHERE "id" = NEW."issue_id";
1345 SELECT "id" INTO "draft_id_v" FROM "current_draft"
1346 WHERE "initiative_id" = NEW."id";
1347 INSERT INTO "event" (
1348 "event", "member_id", "issue_id", "state", "initiative_id", "draft_id"
1349 ) VALUES (
1350 'initiative_revoked',
1351 NEW."revoked_by_member_id",
1352 NEW."issue_id",
1353 "issue_row"."state",
1354 NEW."id",
1355 "draft_id_v");
1356 END IF;
1357 RETURN NULL;
1358 END;
1359 $$;
1361 CREATE TRIGGER "write_event_initiative_revoked"
1362 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1363 "write_event_initiative_revoked_trigger"();
1365 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1366 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1369 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1370 RETURNS TRIGGER
1371 LANGUAGE 'plpgsql' VOLATILE AS $$
1372 DECLARE
1373 "initiative_row" "initiative"%ROWTYPE;
1374 "issue_row" "issue"%ROWTYPE;
1375 BEGIN
1376 SELECT * INTO "initiative_row" FROM "initiative"
1377 WHERE "id" = NEW."initiative_id";
1378 SELECT * INTO "issue_row" FROM "issue"
1379 WHERE "id" = "initiative_row"."issue_id";
1380 INSERT INTO "event" (
1381 "event", "member_id",
1382 "issue_id", "state", "initiative_id", "suggestion_id"
1383 ) VALUES (
1384 'suggestion_created',
1385 NEW."author_id",
1386 "initiative_row"."issue_id",
1387 "issue_row"."state",
1388 "initiative_row"."id",
1389 NEW."id" );
1390 RETURN NULL;
1391 END;
1392 $$;
1394 CREATE TRIGGER "write_event_suggestion_created"
1395 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1396 "write_event_suggestion_created_trigger"();
1398 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1399 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1403 ----------------------------
1404 -- Additional constraints --
1405 ----------------------------
1408 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1409 RETURNS TRIGGER
1410 LANGUAGE 'plpgsql' VOLATILE AS $$
1411 BEGIN
1412 IF NOT EXISTS (
1413 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1414 ) THEN
1415 --RAISE 'Cannot create issue without an initial initiative.' USING
1416 -- ERRCODE = 'integrity_constraint_violation',
1417 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
1418 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
1419 END IF;
1420 RETURN NULL;
1421 END;
1422 $$;
1424 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1425 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1426 FOR EACH ROW EXECUTE PROCEDURE
1427 "issue_requires_first_initiative_trigger"();
1429 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1430 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1433 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1434 RETURNS TRIGGER
1435 LANGUAGE 'plpgsql' VOLATILE AS $$
1436 DECLARE
1437 "reference_lost" BOOLEAN;
1438 BEGIN
1439 IF TG_OP = 'DELETE' THEN
1440 "reference_lost" := TRUE;
1441 ELSE
1442 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1443 END IF;
1444 IF
1445 "reference_lost" AND NOT EXISTS (
1446 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1447 )
1448 THEN
1449 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1450 END IF;
1451 RETURN NULL;
1452 END;
1453 $$;
1455 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1456 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1457 FOR EACH ROW EXECUTE PROCEDURE
1458 "last_initiative_deletes_issue_trigger"();
1460 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1461 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1464 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1465 RETURNS TRIGGER
1466 LANGUAGE 'plpgsql' VOLATILE AS $$
1467 BEGIN
1468 IF NOT EXISTS (
1469 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1470 ) THEN
1471 --RAISE 'Cannot create initiative without an initial draft.' USING
1472 -- ERRCODE = 'integrity_constraint_violation',
1473 -- HINT = 'Create issue, initiative and draft within the same transaction.';
1474 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
1475 END IF;
1476 RETURN NULL;
1477 END;
1478 $$;
1480 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1481 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1482 FOR EACH ROW EXECUTE PROCEDURE
1483 "initiative_requires_first_draft_trigger"();
1485 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1486 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1489 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1490 RETURNS TRIGGER
1491 LANGUAGE 'plpgsql' VOLATILE AS $$
1492 DECLARE
1493 "reference_lost" BOOLEAN;
1494 BEGIN
1495 IF TG_OP = 'DELETE' THEN
1496 "reference_lost" := TRUE;
1497 ELSE
1498 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1499 END IF;
1500 IF
1501 "reference_lost" AND NOT EXISTS (
1502 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1503 )
1504 THEN
1505 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1506 END IF;
1507 RETURN NULL;
1508 END;
1509 $$;
1511 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1512 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1513 FOR EACH ROW EXECUTE PROCEDURE
1514 "last_draft_deletes_initiative_trigger"();
1516 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1517 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1520 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1521 RETURNS TRIGGER
1522 LANGUAGE 'plpgsql' VOLATILE AS $$
1523 BEGIN
1524 IF NOT EXISTS (
1525 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1526 ) THEN
1527 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
1528 END IF;
1529 RETURN NULL;
1530 END;
1531 $$;
1533 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1534 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1535 FOR EACH ROW EXECUTE PROCEDURE
1536 "suggestion_requires_first_opinion_trigger"();
1538 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1539 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1542 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1543 RETURNS TRIGGER
1544 LANGUAGE 'plpgsql' VOLATILE AS $$
1545 DECLARE
1546 "reference_lost" BOOLEAN;
1547 BEGIN
1548 IF TG_OP = 'DELETE' THEN
1549 "reference_lost" := TRUE;
1550 ELSE
1551 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1552 END IF;
1553 IF
1554 "reference_lost" AND NOT EXISTS (
1555 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1556 )
1557 THEN
1558 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1559 END IF;
1560 RETURN NULL;
1561 END;
1562 $$;
1564 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1565 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1566 FOR EACH ROW EXECUTE PROCEDURE
1567 "last_opinion_deletes_suggestion_trigger"();
1569 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1570 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1573 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()
1574 RETURNS TRIGGER
1575 LANGUAGE 'plpgsql' VOLATILE AS $$
1576 BEGIN
1577 DELETE FROM "direct_voter"
1578 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1579 RETURN NULL;
1580 END;
1581 $$;
1583 CREATE TRIGGER "non_voter_deletes_direct_voter"
1584 AFTER INSERT OR UPDATE ON "non_voter"
1585 FOR EACH ROW EXECUTE PROCEDURE
1586 "non_voter_deletes_direct_voter_trigger"();
1588 COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';
1589 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")';
1592 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()
1593 RETURNS TRIGGER
1594 LANGUAGE 'plpgsql' VOLATILE AS $$
1595 BEGIN
1596 DELETE FROM "non_voter"
1597 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1598 RETURN NULL;
1599 END;
1600 $$;
1602 CREATE TRIGGER "direct_voter_deletes_non_voter"
1603 AFTER INSERT OR UPDATE ON "direct_voter"
1604 FOR EACH ROW EXECUTE PROCEDURE
1605 "direct_voter_deletes_non_voter_trigger"();
1607 COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';
1608 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")';
1611 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()
1612 RETURNS TRIGGER
1613 LANGUAGE 'plpgsql' VOLATILE AS $$
1614 BEGIN
1615 IF NEW."comment" ISNULL THEN
1616 NEW."comment_changed" := NULL;
1617 NEW."formatting_engine" := NULL;
1618 END IF;
1619 RETURN NEW;
1620 END;
1621 $$;
1623 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"
1624 BEFORE INSERT OR UPDATE ON "direct_voter"
1625 FOR EACH ROW EXECUTE PROCEDURE
1626 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();
1628 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"';
1629 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.';
1632 ---------------------------------------------------------------
1633 -- Ensure that votes are not modified when issues are closed --
1634 ---------------------------------------------------------------
1636 -- NOTE: Frontends should ensure this anyway, but in case of programming
1637 -- errors the following triggers ensure data integrity.
1640 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1641 RETURNS TRIGGER
1642 LANGUAGE 'plpgsql' VOLATILE AS $$
1643 DECLARE
1644 "issue_id_v" "issue"."id"%TYPE;
1645 "issue_row" "issue"%ROWTYPE;
1646 BEGIN
1647 IF EXISTS (
1648 SELECT NULL FROM "temporary_transaction_data"
1649 WHERE "txid" = txid_current()
1650 AND "key" = 'override_protection_triggers'
1651 AND "value" = TRUE::TEXT
1652 ) THEN
1653 RETURN NULL;
1654 END IF;
1655 IF TG_OP = 'DELETE' THEN
1656 "issue_id_v" := OLD."issue_id";
1657 ELSE
1658 "issue_id_v" := NEW."issue_id";
1659 END IF;
1660 SELECT INTO "issue_row" * FROM "issue"
1661 WHERE "id" = "issue_id_v" FOR SHARE;
1662 IF (
1663 "issue_row"."closed" NOTNULL OR (
1664 "issue_row"."state" = 'voting' AND
1665 "issue_row"."phase_finished" NOTNULL
1666 )
1667 ) THEN
1668 IF
1669 TG_RELID = 'direct_voter'::regclass AND
1670 TG_OP = 'UPDATE'
1671 THEN
1672 IF
1673 OLD."issue_id" = NEW."issue_id" AND
1674 OLD."member_id" = NEW."member_id" AND
1675 OLD."weight" = NEW."weight"
1676 THEN
1677 RETURN NULL; -- allows changing of voter comment
1678 END IF;
1679 END IF;
1680 RAISE EXCEPTION 'Tried to modify data after voting has been closed.';
1681 END IF;
1682 RETURN NULL;
1683 END;
1684 $$;
1686 CREATE TRIGGER "forbid_changes_on_closed_issue"
1687 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1688 FOR EACH ROW EXECUTE PROCEDURE
1689 "forbid_changes_on_closed_issue_trigger"();
1691 CREATE TRIGGER "forbid_changes_on_closed_issue"
1692 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1693 FOR EACH ROW EXECUTE PROCEDURE
1694 "forbid_changes_on_closed_issue_trigger"();
1696 CREATE TRIGGER "forbid_changes_on_closed_issue"
1697 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1698 FOR EACH ROW EXECUTE PROCEDURE
1699 "forbid_changes_on_closed_issue_trigger"();
1701 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"';
1702 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';
1703 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';
1704 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';
1708 --------------------------------------------------------------------
1709 -- Auto-retrieval of fields only needed for referential integrity --
1710 --------------------------------------------------------------------
1713 CREATE FUNCTION "autofill_issue_id_trigger"()
1714 RETURNS TRIGGER
1715 LANGUAGE 'plpgsql' VOLATILE AS $$
1716 BEGIN
1717 IF NEW."issue_id" ISNULL THEN
1718 SELECT "issue_id" INTO NEW."issue_id"
1719 FROM "initiative" WHERE "id" = NEW."initiative_id";
1720 END IF;
1721 RETURN NEW;
1722 END;
1723 $$;
1725 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1726 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1728 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1729 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1731 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1732 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1733 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1736 CREATE FUNCTION "autofill_initiative_id_trigger"()
1737 RETURNS TRIGGER
1738 LANGUAGE 'plpgsql' VOLATILE AS $$
1739 BEGIN
1740 IF NEW."initiative_id" ISNULL THEN
1741 SELECT "initiative_id" INTO NEW."initiative_id"
1742 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1743 END IF;
1744 RETURN NEW;
1745 END;
1746 $$;
1748 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1749 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1751 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1752 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1756 -----------------------------------------------------
1757 -- Automatic calculation of certain default values --
1758 -----------------------------------------------------
1761 CREATE FUNCTION "copy_timings_trigger"()
1762 RETURNS TRIGGER
1763 LANGUAGE 'plpgsql' VOLATILE AS $$
1764 DECLARE
1765 "policy_row" "policy"%ROWTYPE;
1766 BEGIN
1767 SELECT * INTO "policy_row" FROM "policy"
1768 WHERE "id" = NEW."policy_id";
1769 IF NEW."admission_time" ISNULL THEN
1770 NEW."admission_time" := "policy_row"."admission_time";
1771 END IF;
1772 IF NEW."discussion_time" ISNULL THEN
1773 NEW."discussion_time" := "policy_row"."discussion_time";
1774 END IF;
1775 IF NEW."verification_time" ISNULL THEN
1776 NEW."verification_time" := "policy_row"."verification_time";
1777 END IF;
1778 IF NEW."voting_time" ISNULL THEN
1779 NEW."voting_time" := "policy_row"."voting_time";
1780 END IF;
1781 RETURN NEW;
1782 END;
1783 $$;
1785 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1786 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1788 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1789 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1792 CREATE FUNCTION "default_for_draft_id_trigger"()
1793 RETURNS TRIGGER
1794 LANGUAGE 'plpgsql' VOLATILE AS $$
1795 BEGIN
1796 IF NEW."draft_id" ISNULL THEN
1797 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1798 WHERE "initiative_id" = NEW."initiative_id";
1799 END IF;
1800 RETURN NEW;
1801 END;
1802 $$;
1804 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
1805 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1806 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1807 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1809 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
1810 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';
1811 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';
1815 ----------------------------------------
1816 -- Automatic creation of dependencies --
1817 ----------------------------------------
1820 CREATE FUNCTION "autocreate_interest_trigger"()
1821 RETURNS TRIGGER
1822 LANGUAGE 'plpgsql' VOLATILE AS $$
1823 BEGIN
1824 IF NOT EXISTS (
1825 SELECT NULL FROM "initiative" JOIN "interest"
1826 ON "initiative"."issue_id" = "interest"."issue_id"
1827 WHERE "initiative"."id" = NEW."initiative_id"
1828 AND "interest"."member_id" = NEW."member_id"
1829 ) THEN
1830 BEGIN
1831 INSERT INTO "interest" ("issue_id", "member_id")
1832 SELECT "issue_id", NEW."member_id"
1833 FROM "initiative" WHERE "id" = NEW."initiative_id";
1834 EXCEPTION WHEN unique_violation THEN END;
1835 END IF;
1836 RETURN NEW;
1837 END;
1838 $$;
1840 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1841 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1843 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1844 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';
1847 CREATE FUNCTION "autocreate_supporter_trigger"()
1848 RETURNS TRIGGER
1849 LANGUAGE 'plpgsql' VOLATILE AS $$
1850 BEGIN
1851 IF NOT EXISTS (
1852 SELECT NULL FROM "suggestion" JOIN "supporter"
1853 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1854 WHERE "suggestion"."id" = NEW."suggestion_id"
1855 AND "supporter"."member_id" = NEW."member_id"
1856 ) THEN
1857 BEGIN
1858 INSERT INTO "supporter" ("initiative_id", "member_id")
1859 SELECT "initiative_id", NEW."member_id"
1860 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1861 EXCEPTION WHEN unique_violation THEN END;
1862 END IF;
1863 RETURN NEW;
1864 END;
1865 $$;
1867 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1868 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1870 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1871 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.';
1875 ------------------------------------------
1876 -- Views and helper functions for views --
1877 ------------------------------------------
1880 CREATE VIEW "unit_delegation" AS
1881 SELECT
1882 "unit"."id" AS "unit_id",
1883 "delegation"."id",
1884 "delegation"."truster_id",
1885 "delegation"."trustee_id",
1886 "delegation"."scope"
1887 FROM "unit"
1888 JOIN "delegation"
1889 ON "delegation"."unit_id" = "unit"."id"
1890 JOIN "member"
1891 ON "delegation"."truster_id" = "member"."id"
1892 JOIN "privilege"
1893 ON "delegation"."unit_id" = "privilege"."unit_id"
1894 AND "delegation"."truster_id" = "privilege"."member_id"
1895 WHERE "member"."active" AND "privilege"."voting_right";
1897 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1900 CREATE VIEW "area_delegation" AS
1901 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1902 "area"."id" AS "area_id",
1903 "delegation"."id",
1904 "delegation"."truster_id",
1905 "delegation"."trustee_id",
1906 "delegation"."scope"
1907 FROM "area"
1908 JOIN "delegation"
1909 ON "delegation"."unit_id" = "area"."unit_id"
1910 OR "delegation"."area_id" = "area"."id"
1911 JOIN "member"
1912 ON "delegation"."truster_id" = "member"."id"
1913 JOIN "privilege"
1914 ON "area"."unit_id" = "privilege"."unit_id"
1915 AND "delegation"."truster_id" = "privilege"."member_id"
1916 WHERE "member"."active" AND "privilege"."voting_right"
1917 ORDER BY
1918 "area"."id",
1919 "delegation"."truster_id",
1920 "delegation"."scope" DESC;
1922 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1925 CREATE VIEW "issue_delegation" AS
1926 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1927 "issue"."id" AS "issue_id",
1928 "delegation"."id",
1929 "delegation"."truster_id",
1930 "delegation"."trustee_id",
1931 "delegation"."scope"
1932 FROM "issue"
1933 JOIN "area"
1934 ON "area"."id" = "issue"."area_id"
1935 JOIN "delegation"
1936 ON "delegation"."unit_id" = "area"."unit_id"
1937 OR "delegation"."area_id" = "area"."id"
1938 OR "delegation"."issue_id" = "issue"."id"
1939 JOIN "member"
1940 ON "delegation"."truster_id" = "member"."id"
1941 JOIN "privilege"
1942 ON "area"."unit_id" = "privilege"."unit_id"
1943 AND "delegation"."truster_id" = "privilege"."member_id"
1944 WHERE "member"."active" AND "privilege"."voting_right"
1945 ORDER BY
1946 "issue"."id",
1947 "delegation"."truster_id",
1948 "delegation"."scope" DESC;
1950 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
1953 CREATE FUNCTION "membership_weight_with_skipping"
1954 ( "area_id_p" "area"."id"%TYPE,
1955 "member_id_p" "member"."id"%TYPE,
1956 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1957 RETURNS INT4
1958 LANGUAGE 'plpgsql' STABLE AS $$
1959 DECLARE
1960 "sum_v" INT4;
1961 "delegation_row" "area_delegation"%ROWTYPE;
1962 BEGIN
1963 "sum_v" := 1;
1964 FOR "delegation_row" IN
1965 SELECT "area_delegation".*
1966 FROM "area_delegation" LEFT JOIN "membership"
1967 ON "membership"."area_id" = "area_id_p"
1968 AND "membership"."member_id" = "area_delegation"."truster_id"
1969 WHERE "area_delegation"."area_id" = "area_id_p"
1970 AND "area_delegation"."trustee_id" = "member_id_p"
1971 AND "membership"."member_id" ISNULL
1972 LOOP
1973 IF NOT
1974 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1975 THEN
1976 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1977 "area_id_p",
1978 "delegation_row"."truster_id",
1979 "skip_member_ids_p" || "delegation_row"."truster_id"
1980 );
1981 END IF;
1982 END LOOP;
1983 RETURN "sum_v";
1984 END;
1985 $$;
1987 COMMENT ON FUNCTION "membership_weight_with_skipping"
1988 ( "area"."id"%TYPE,
1989 "member"."id"%TYPE,
1990 INT4[] )
1991 IS 'Helper function for "membership_weight" function';
1994 CREATE FUNCTION "membership_weight"
1995 ( "area_id_p" "area"."id"%TYPE,
1996 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1997 RETURNS INT4
1998 LANGUAGE 'plpgsql' STABLE AS $$
1999 BEGIN
2000 RETURN "membership_weight_with_skipping"(
2001 "area_id_p",
2002 "member_id_p",
2003 ARRAY["member_id_p"]
2004 );
2005 END;
2006 $$;
2008 COMMENT ON FUNCTION "membership_weight"
2009 ( "area"."id"%TYPE,
2010 "member"."id"%TYPE )
2011 IS 'Calculates the potential voting weight of a member in a given area';
2014 CREATE VIEW "member_count_view" AS
2015 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
2017 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
2020 CREATE VIEW "unit_member_count" AS
2021 SELECT
2022 "unit"."id" AS "unit_id",
2023 count("member"."id") AS "member_count"
2024 FROM "unit"
2025 LEFT JOIN "privilege"
2026 ON "privilege"."unit_id" = "unit"."id"
2027 AND "privilege"."voting_right"
2028 LEFT JOIN "member"
2029 ON "member"."id" = "privilege"."member_id"
2030 AND "member"."active"
2031 GROUP BY "unit"."id";
2033 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
2036 CREATE VIEW "area_member_count" AS
2037 SELECT
2038 "area"."id" AS "area_id",
2039 count("member"."id") AS "direct_member_count",
2040 coalesce(
2041 sum(
2042 CASE WHEN "member"."id" NOTNULL THEN
2043 "membership_weight"("area"."id", "member"."id")
2044 ELSE 0 END
2045 )
2046 ) AS "member_weight"
2047 FROM "area"
2048 LEFT JOIN "membership"
2049 ON "area"."id" = "membership"."area_id"
2050 LEFT JOIN "privilege"
2051 ON "privilege"."unit_id" = "area"."unit_id"
2052 AND "privilege"."member_id" = "membership"."member_id"
2053 AND "privilege"."voting_right"
2054 LEFT JOIN "member"
2055 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
2056 AND "member"."active"
2057 GROUP BY "area"."id";
2059 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';
2062 CREATE VIEW "opening_draft" AS
2063 SELECT "draft".* FROM (
2064 SELECT
2065 "initiative"."id" AS "initiative_id",
2066 min("draft"."id") AS "draft_id"
2067 FROM "initiative" JOIN "draft"
2068 ON "initiative"."id" = "draft"."initiative_id"
2069 GROUP BY "initiative"."id"
2070 ) AS "subquery"
2071 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
2073 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
2076 CREATE VIEW "current_draft" AS
2077 SELECT "draft".* FROM (
2078 SELECT
2079 "initiative"."id" AS "initiative_id",
2080 max("draft"."id") AS "draft_id"
2081 FROM "initiative" JOIN "draft"
2082 ON "initiative"."id" = "draft"."initiative_id"
2083 GROUP BY "initiative"."id"
2084 ) AS "subquery"
2085 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
2087 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
2090 CREATE VIEW "critical_opinion" AS
2091 SELECT * FROM "opinion"
2092 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
2093 OR ("degree" = -2 AND "fulfilled" = TRUE);
2095 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
2098 CREATE VIEW "issue_supporter_in_admission_state" AS
2099 SELECT DISTINCT
2100 "area"."unit_id",
2101 "issue"."area_id",
2102 "issue"."id" AS "issue_id",
2103 "supporter"."member_id",
2104 "direct_interest_snapshot"."weight"
2105 FROM "issue"
2106 JOIN "area" ON "area"."id" = "issue"."area_id"
2107 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
2108 JOIN "direct_interest_snapshot"
2109 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
2110 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
2111 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
2112 WHERE "issue"."state" = 'admission'::"issue_state";
2114 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';
2117 CREATE VIEW "initiative_suggestion_order_calculation" AS
2118 SELECT
2119 "initiative"."id" AS "initiative_id",
2120 ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final"
2121 FROM "initiative" JOIN "issue"
2122 ON "initiative"."issue_id" = "issue"."id"
2123 WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL)
2124 OR ("initiative"."final_suggestion_order_calculated" = FALSE);
2126 COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';
2128 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';
2131 CREATE VIEW "individual_suggestion_ranking" AS
2132 SELECT
2133 "opinion"."initiative_id",
2134 "opinion"."member_id",
2135 "direct_interest_snapshot"."weight",
2136 CASE WHEN
2137 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
2138 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
2139 THEN 1 ELSE
2140 CASE WHEN
2141 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
2142 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
2143 THEN 2 ELSE
2144 CASE WHEN
2145 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
2146 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
2147 THEN 3 ELSE 4 END
2148 END
2149 END AS "preference",
2150 "opinion"."suggestion_id"
2151 FROM "opinion"
2152 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
2153 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2154 JOIN "direct_interest_snapshot"
2155 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
2156 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
2157 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
2159 COMMENT ON VIEW "individual_suggestion_ranking" IS 'Helper view for "lf_update_suggestion_order" to allow a proportional ordering of suggestions within an initiative';
2162 CREATE VIEW "battle_participant" AS
2163 SELECT "initiative"."id", "initiative"."issue_id"
2164 FROM "issue" JOIN "initiative"
2165 ON "issue"."id" = "initiative"."issue_id"
2166 WHERE "initiative"."admitted"
2167 UNION ALL
2168 SELECT NULL, "id" AS "issue_id"
2169 FROM "issue";
2171 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
2174 CREATE VIEW "battle_view" AS
2175 SELECT
2176 "issue"."id" AS "issue_id",
2177 "winning_initiative"."id" AS "winning_initiative_id",
2178 "losing_initiative"."id" AS "losing_initiative_id",
2179 sum(
2180 CASE WHEN
2181 coalesce("better_vote"."grade", 0) >
2182 coalesce("worse_vote"."grade", 0)
2183 THEN "direct_voter"."weight" ELSE 0 END
2184 ) AS "count"
2185 FROM "issue"
2186 LEFT JOIN "direct_voter"
2187 ON "issue"."id" = "direct_voter"."issue_id"
2188 JOIN "battle_participant" AS "winning_initiative"
2189 ON "issue"."id" = "winning_initiative"."issue_id"
2190 JOIN "battle_participant" AS "losing_initiative"
2191 ON "issue"."id" = "losing_initiative"."issue_id"
2192 LEFT JOIN "vote" AS "better_vote"
2193 ON "direct_voter"."member_id" = "better_vote"."member_id"
2194 AND "winning_initiative"."id" = "better_vote"."initiative_id"
2195 LEFT JOIN "vote" AS "worse_vote"
2196 ON "direct_voter"."member_id" = "worse_vote"."member_id"
2197 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
2198 WHERE "issue"."state" = 'voting'
2199 AND "issue"."phase_finished" NOTNULL
2200 AND (
2201 "winning_initiative"."id" != "losing_initiative"."id" OR
2202 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
2203 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
2204 GROUP BY
2205 "issue"."id",
2206 "winning_initiative"."id",
2207 "losing_initiative"."id";
2209 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';
2212 CREATE VIEW "expired_session" AS
2213 SELECT * FROM "session" WHERE now() > "expiry";
2215 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
2216 DELETE FROM "session" WHERE "ident" = OLD."ident";
2218 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
2219 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
2222 CREATE VIEW "open_issue" AS
2223 SELECT * FROM "issue" WHERE "closed" ISNULL;
2225 COMMENT ON VIEW "open_issue" IS 'All open issues';
2228 CREATE VIEW "member_contingent" AS
2229 SELECT
2230 "member"."id" AS "member_id",
2231 "contingent"."polling",
2232 "contingent"."time_frame",
2233 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
2234 (
2235 SELECT count(1) FROM "draft"
2236 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
2237 WHERE "draft"."author_id" = "member"."id"
2238 AND "initiative"."polling" = "contingent"."polling"
2239 AND "draft"."created" > now() - "contingent"."time_frame"
2240 ) + (
2241 SELECT count(1) FROM "suggestion"
2242 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
2243 WHERE "suggestion"."author_id" = "member"."id"
2244 AND "contingent"."polling" = FALSE
2245 AND "suggestion"."created" > now() - "contingent"."time_frame"
2246 )
2247 ELSE NULL END AS "text_entry_count",
2248 "contingent"."text_entry_limit",
2249 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
2250 SELECT count(1) FROM "opening_draft" AS "draft"
2251 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
2252 WHERE "draft"."author_id" = "member"."id"
2253 AND "initiative"."polling" = "contingent"."polling"
2254 AND "draft"."created" > now() - "contingent"."time_frame"
2255 ) ELSE NULL END AS "initiative_count",
2256 "contingent"."initiative_limit"
2257 FROM "member" CROSS JOIN "contingent";
2259 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
2261 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
2262 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
2265 CREATE VIEW "member_contingent_left" AS
2266 SELECT
2267 "member_id",
2268 "polling",
2269 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
2270 max("initiative_limit" - "initiative_count") AS "initiatives_left"
2271 FROM "member_contingent" GROUP BY "member_id", "polling";
2273 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.';
2276 CREATE VIEW "event_seen_by_member" AS
2277 SELECT
2278 "member"."id" AS "seen_by_member_id",
2279 CASE WHEN "event"."state" IN (
2280 'voting',
2281 'finished_without_winner',
2282 'finished_with_winner'
2283 ) THEN
2284 'voting'::"notify_level"
2285 ELSE
2286 CASE WHEN "event"."state" IN (
2287 'verification',
2288 'canceled_after_revocation_during_verification',
2289 'canceled_no_initiative_admitted'
2290 ) THEN
2291 'verification'::"notify_level"
2292 ELSE
2293 CASE WHEN "event"."state" IN (
2294 'discussion',
2295 'canceled_after_revocation_during_discussion'
2296 ) THEN
2297 'discussion'::"notify_level"
2298 ELSE
2299 'all'::"notify_level"
2300 END
2301 END
2302 END AS "notify_level",
2303 "event".*
2304 FROM "member" CROSS JOIN "event"
2305 LEFT JOIN "issue"
2306 ON "event"."issue_id" = "issue"."id"
2307 LEFT JOIN "membership"
2308 ON "member"."id" = "membership"."member_id"
2309 AND "issue"."area_id" = "membership"."area_id"
2310 LEFT JOIN "interest"
2311 ON "member"."id" = "interest"."member_id"
2312 AND "event"."issue_id" = "interest"."issue_id"
2313 LEFT JOIN "ignored_member"
2314 ON "member"."id" = "ignored_member"."member_id"
2315 AND "event"."member_id" = "ignored_member"."other_member_id"
2316 LEFT JOIN "ignored_initiative"
2317 ON "member"."id" = "ignored_initiative"."member_id"
2318 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2319 WHERE (
2320 "interest"."member_id" NOTNULL OR
2321 ( "membership"."member_id" NOTNULL AND
2322 "event"."event" IN (
2323 'issue_state_changed',
2324 'initiative_created_in_new_issue',
2325 'initiative_created_in_existing_issue',
2326 'initiative_revoked' ) ) )
2327 AND "ignored_member"."member_id" ISNULL
2328 AND "ignored_initiative"."member_id" ISNULL;
2330 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"';
2333 CREATE VIEW "selected_event_seen_by_member" AS
2334 SELECT
2335 "member"."id" AS "seen_by_member_id",
2336 CASE WHEN "event"."state" IN (
2337 'voting',
2338 'finished_without_winner',
2339 'finished_with_winner'
2340 ) THEN
2341 'voting'::"notify_level"
2342 ELSE
2343 CASE WHEN "event"."state" IN (
2344 'verification',
2345 'canceled_after_revocation_during_verification',
2346 'canceled_no_initiative_admitted'
2347 ) THEN
2348 'verification'::"notify_level"
2349 ELSE
2350 CASE WHEN "event"."state" IN (
2351 'discussion',
2352 'canceled_after_revocation_during_discussion'
2353 ) THEN
2354 'discussion'::"notify_level"
2355 ELSE
2356 'all'::"notify_level"
2357 END
2358 END
2359 END AS "notify_level",
2360 "event".*
2361 FROM "member" CROSS JOIN "event"
2362 LEFT JOIN "issue"
2363 ON "event"."issue_id" = "issue"."id"
2364 LEFT JOIN "membership"
2365 ON "member"."id" = "membership"."member_id"
2366 AND "issue"."area_id" = "membership"."area_id"
2367 LEFT JOIN "interest"
2368 ON "member"."id" = "interest"."member_id"
2369 AND "event"."issue_id" = "interest"."issue_id"
2370 LEFT JOIN "ignored_member"
2371 ON "member"."id" = "ignored_member"."member_id"
2372 AND "event"."member_id" = "ignored_member"."other_member_id"
2373 LEFT JOIN "ignored_initiative"
2374 ON "member"."id" = "ignored_initiative"."member_id"
2375 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2376 WHERE (
2377 ( "member"."notify_level" >= 'all' ) OR
2378 ( "member"."notify_level" >= 'voting' AND
2379 "event"."state" IN (
2380 'voting',
2381 'finished_without_winner',
2382 'finished_with_winner' ) ) OR
2383 ( "member"."notify_level" >= 'verification' AND
2384 "event"."state" IN (
2385 'verification',
2386 'canceled_after_revocation_during_verification',
2387 'canceled_no_initiative_admitted' ) ) OR
2388 ( "member"."notify_level" >= 'discussion' AND
2389 "event"."state" IN (
2390 'discussion',
2391 'canceled_after_revocation_during_discussion' ) ) )
2392 AND (
2393 "interest"."member_id" NOTNULL OR
2394 ( "membership"."member_id" NOTNULL AND
2395 "event"."event" IN (
2396 'issue_state_changed',
2397 'initiative_created_in_new_issue',
2398 'initiative_created_in_existing_issue',
2399 'initiative_revoked' ) ) )
2400 AND "ignored_member"."member_id" ISNULL
2401 AND "ignored_initiative"."member_id" ISNULL;
2403 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"';
2407 ------------------------------------------------------
2408 -- Row set returning function for delegation chains --
2409 ------------------------------------------------------
2412 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2413 ('first', 'intermediate', 'last', 'repetition');
2415 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2418 CREATE TYPE "delegation_chain_row" AS (
2419 "index" INT4,
2420 "member_id" INT4,
2421 "member_valid" BOOLEAN,
2422 "participation" BOOLEAN,
2423 "overridden" BOOLEAN,
2424 "scope_in" "delegation_scope",
2425 "scope_out" "delegation_scope",
2426 "disabled_out" BOOLEAN,
2427 "loop" "delegation_chain_loop_tag" );
2429 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
2431 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2432 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';
2433 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2434 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2435 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2436 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2437 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2440 CREATE FUNCTION "delegation_chain_for_closed_issue"
2441 ( "member_id_p" "member"."id"%TYPE,
2442 "issue_id_p" "issue"."id"%TYPE )
2443 RETURNS SETOF "delegation_chain_row"
2444 LANGUAGE 'plpgsql' STABLE AS $$
2445 DECLARE
2446 "output_row" "delegation_chain_row";
2447 "direct_voter_row" "direct_voter"%ROWTYPE;
2448 "delegating_voter_row" "delegating_voter"%ROWTYPE;
2449 BEGIN
2450 "output_row"."index" := 0;
2451 "output_row"."member_id" := "member_id_p";
2452 "output_row"."member_valid" := TRUE;
2453 "output_row"."participation" := FALSE;
2454 "output_row"."overridden" := FALSE;
2455 "output_row"."disabled_out" := FALSE;
2456 LOOP
2457 SELECT INTO "direct_voter_row" * FROM "direct_voter"
2458 WHERE "issue_id" = "issue_id_p"
2459 AND "member_id" = "output_row"."member_id";
2460 IF "direct_voter_row"."member_id" NOTNULL THEN
2461 "output_row"."participation" := TRUE;
2462 "output_row"."scope_out" := NULL;
2463 "output_row"."disabled_out" := NULL;
2464 RETURN NEXT "output_row";
2465 RETURN;
2466 END IF;
2467 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
2468 WHERE "issue_id" = "issue_id_p"
2469 AND "member_id" = "output_row"."member_id";
2470 IF "delegating_voter_row"."member_id" ISNULL THEN
2471 RETURN;
2472 END IF;
2473 "output_row"."scope_out" := "delegating_voter_row"."scope";
2474 RETURN NEXT "output_row";
2475 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
2476 "output_row"."scope_in" := "output_row"."scope_out";
2477 END LOOP;
2478 END;
2479 $$;
2481 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
2482 ( "member"."id"%TYPE,
2483 "member"."id"%TYPE )
2484 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
2487 CREATE FUNCTION "delegation_chain"
2488 ( "member_id_p" "member"."id"%TYPE,
2489 "unit_id_p" "unit"."id"%TYPE,
2490 "area_id_p" "area"."id"%TYPE,
2491 "issue_id_p" "issue"."id"%TYPE,
2492 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2493 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2494 RETURNS SETOF "delegation_chain_row"
2495 LANGUAGE 'plpgsql' STABLE AS $$
2496 DECLARE
2497 "scope_v" "delegation_scope";
2498 "unit_id_v" "unit"."id"%TYPE;
2499 "area_id_v" "area"."id"%TYPE;
2500 "issue_row" "issue"%ROWTYPE;
2501 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2502 "loop_member_id_v" "member"."id"%TYPE;
2503 "output_row" "delegation_chain_row";
2504 "output_rows" "delegation_chain_row"[];
2505 "simulate_v" BOOLEAN;
2506 "simulate_here_v" BOOLEAN;
2507 "delegation_row" "delegation"%ROWTYPE;
2508 "row_count" INT4;
2509 "i" INT4;
2510 "loop_v" BOOLEAN;
2511 BEGIN
2512 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
2513 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
2514 END IF;
2515 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
2516 "simulate_v" := TRUE;
2517 ELSE
2518 "simulate_v" := FALSE;
2519 END IF;
2520 IF
2521 "unit_id_p" NOTNULL AND
2522 "area_id_p" ISNULL AND
2523 "issue_id_p" ISNULL
2524 THEN
2525 "scope_v" := 'unit';
2526 "unit_id_v" := "unit_id_p";
2527 ELSIF
2528 "unit_id_p" ISNULL AND
2529 "area_id_p" NOTNULL AND
2530 "issue_id_p" ISNULL
2531 THEN
2532 "scope_v" := 'area';
2533 "area_id_v" := "area_id_p";
2534 SELECT "unit_id" INTO "unit_id_v"
2535 FROM "area" WHERE "id" = "area_id_v";
2536 ELSIF
2537 "unit_id_p" ISNULL AND
2538 "area_id_p" ISNULL AND
2539 "issue_id_p" NOTNULL
2540 THEN
2541 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
2542 IF "issue_row"."id" ISNULL THEN
2543 RETURN;
2544 END IF;
2545 IF "issue_row"."closed" NOTNULL THEN
2546 IF "simulate_v" THEN
2547 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
2548 END IF;
2549 FOR "output_row" IN
2550 SELECT * FROM
2551 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
2552 LOOP
2553 RETURN NEXT "output_row";
2554 END LOOP;
2555 RETURN;
2556 END IF;
2557 "scope_v" := 'issue';
2558 SELECT "area_id" INTO "area_id_v"
2559 FROM "issue" WHERE "id" = "issue_id_p";
2560 SELECT "unit_id" INTO "unit_id_v"
2561 FROM "area" WHERE "id" = "area_id_v";
2562 ELSE
2563 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2564 END IF;
2565 "visited_member_ids" := '{}';
2566 "loop_member_id_v" := NULL;
2567 "output_rows" := '{}';
2568 "output_row"."index" := 0;
2569 "output_row"."member_id" := "member_id_p";
2570 "output_row"."member_valid" := TRUE;
2571 "output_row"."participation" := FALSE;
2572 "output_row"."overridden" := FALSE;
2573 "output_row"."disabled_out" := FALSE;
2574 "output_row"."scope_out" := NULL;
2575 LOOP
2576 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2577 "loop_member_id_v" := "output_row"."member_id";
2578 ELSE
2579 "visited_member_ids" :=
2580 "visited_member_ids" || "output_row"."member_id";
2581 END IF;
2582 IF "output_row"."participation" ISNULL THEN
2583 "output_row"."overridden" := NULL;
2584 ELSIF "output_row"."participation" THEN
2585 "output_row"."overridden" := TRUE;
2586 END IF;
2587 "output_row"."scope_in" := "output_row"."scope_out";
2588 "output_row"."member_valid" := EXISTS (
2589 SELECT NULL FROM "member" JOIN "privilege"
2590 ON "privilege"."member_id" = "member"."id"
2591 AND "privilege"."unit_id" = "unit_id_v"
2592 WHERE "id" = "output_row"."member_id"
2593 AND "member"."active" AND "privilege"."voting_right"
2594 );
2595 "simulate_here_v" := (
2596 "simulate_v" AND
2597 "output_row"."member_id" = "member_id_p"
2598 );
2599 "delegation_row" := ROW(NULL);
2600 IF "output_row"."member_valid" OR "simulate_here_v" THEN
2601 IF "scope_v" = 'unit' THEN
2602 IF NOT "simulate_here_v" THEN
2603 SELECT * INTO "delegation_row" FROM "delegation"
2604 WHERE "truster_id" = "output_row"."member_id"
2605 AND "unit_id" = "unit_id_v";
2606 END IF;
2607 ELSIF "scope_v" = 'area' THEN
2608 "output_row"."participation" := EXISTS (
2609 SELECT NULL FROM "membership"
2610 WHERE "area_id" = "area_id_p"
2611 AND "member_id" = "output_row"."member_id"
2612 );
2613 IF "simulate_here_v" THEN
2614 IF "simulate_trustee_id_p" ISNULL THEN
2615 SELECT * INTO "delegation_row" FROM "delegation"
2616 WHERE "truster_id" = "output_row"."member_id"
2617 AND "unit_id" = "unit_id_v";
2618 END IF;
2619 ELSE
2620 SELECT * INTO "delegation_row" FROM "delegation"
2621 WHERE "truster_id" = "output_row"."member_id"
2622 AND (
2623 "unit_id" = "unit_id_v" OR
2624 "area_id" = "area_id_v"
2625 )
2626 ORDER BY "scope" DESC;
2627 END IF;
2628 ELSIF "scope_v" = 'issue' THEN
2629 IF "issue_row"."fully_frozen" ISNULL THEN
2630 "output_row"."participation" := EXISTS (
2631 SELECT NULL FROM "interest"
2632 WHERE "issue_id" = "issue_id_p"
2633 AND "member_id" = "output_row"."member_id"
2634 );
2635 ELSE
2636 IF "output_row"."member_id" = "member_id_p" THEN
2637 "output_row"."participation" := EXISTS (
2638 SELECT NULL FROM "direct_voter"
2639 WHERE "issue_id" = "issue_id_p"
2640 AND "member_id" = "output_row"."member_id"
2641 );
2642 ELSE
2643 "output_row"."participation" := NULL;
2644 END IF;
2645 END IF;
2646 IF "simulate_here_v" THEN
2647 IF "simulate_trustee_id_p" ISNULL THEN
2648 SELECT * INTO "delegation_row" FROM "delegation"
2649 WHERE "truster_id" = "output_row"."member_id"
2650 AND (
2651 "unit_id" = "unit_id_v" OR
2652 "area_id" = "area_id_v"
2653 )
2654 ORDER BY "scope" DESC;
2655 END IF;
2656 ELSE
2657 SELECT * INTO "delegation_row" FROM "delegation"
2658 WHERE "truster_id" = "output_row"."member_id"
2659 AND (
2660 "unit_id" = "unit_id_v" OR
2661 "area_id" = "area_id_v" OR
2662 "issue_id" = "issue_id_p"
2663 )
2664 ORDER BY "scope" DESC;
2665 END IF;
2666 END IF;
2667 ELSE
2668 "output_row"."participation" := FALSE;
2669 END IF;
2670 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
2671 "output_row"."scope_out" := "scope_v";
2672 "output_rows" := "output_rows" || "output_row";
2673 "output_row"."member_id" := "simulate_trustee_id_p";
2674 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2675 "output_row"."scope_out" := "delegation_row"."scope";
2676 "output_rows" := "output_rows" || "output_row";
2677 "output_row"."member_id" := "delegation_row"."trustee_id";
2678 ELSIF "delegation_row"."scope" NOTNULL THEN
2679 "output_row"."scope_out" := "delegation_row"."scope";
2680 "output_row"."disabled_out" := TRUE;
2681 "output_rows" := "output_rows" || "output_row";
2682 EXIT;
2683 ELSE
2684 "output_row"."scope_out" := NULL;
2685 "output_rows" := "output_rows" || "output_row";
2686 EXIT;
2687 END IF;
2688 EXIT WHEN "loop_member_id_v" NOTNULL;
2689 "output_row"."index" := "output_row"."index" + 1;
2690 END LOOP;
2691 "row_count" := array_upper("output_rows", 1);
2692 "i" := 1;
2693 "loop_v" := FALSE;
2694 LOOP
2695 "output_row" := "output_rows"["i"];
2696 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2697 IF "loop_v" THEN
2698 IF "i" + 1 = "row_count" THEN
2699 "output_row"."loop" := 'last';
2700 ELSIF "i" = "row_count" THEN
2701 "output_row"."loop" := 'repetition';
2702 ELSE
2703 "output_row"."loop" := 'intermediate';
2704 END IF;
2705 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2706 "output_row"."loop" := 'first';
2707 "loop_v" := TRUE;
2708 END IF;
2709 IF "scope_v" = 'unit' THEN
2710 "output_row"."participation" := NULL;
2711 END IF;
2712 RETURN NEXT "output_row";
2713 "i" := "i" + 1;
2714 END LOOP;
2715 RETURN;
2716 END;
2717 $$;
2719 COMMENT ON FUNCTION "delegation_chain"
2720 ( "member"."id"%TYPE,
2721 "unit"."id"%TYPE,
2722 "area"."id"%TYPE,
2723 "issue"."id"%TYPE,
2724 "member"."id"%TYPE,
2725 BOOLEAN )
2726 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
2730 ---------------------------------------------------------
2731 -- Single row returning function for delegation chains --
2732 ---------------------------------------------------------
2735 CREATE TYPE "delegation_info_loop_type" AS ENUM
2736 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
2738 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''';
2741 CREATE TYPE "delegation_info_type" AS (
2742 "own_participation" BOOLEAN,
2743 "own_delegation_scope" "delegation_scope",
2744 "first_trustee_id" INT4,
2745 "first_trustee_participation" BOOLEAN,
2746 "first_trustee_ellipsis" BOOLEAN,
2747 "other_trustee_id" INT4,
2748 "other_trustee_participation" BOOLEAN,
2749 "other_trustee_ellipsis" BOOLEAN,
2750 "delegation_loop" "delegation_info_loop_type",
2751 "participating_member_id" INT4 );
2753 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';
2755 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
2756 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
2757 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
2758 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
2759 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
2760 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
2761 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)';
2762 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
2763 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';
2764 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';
2767 CREATE FUNCTION "delegation_info"
2768 ( "member_id_p" "member"."id"%TYPE,
2769 "unit_id_p" "unit"."id"%TYPE,
2770 "area_id_p" "area"."id"%TYPE,
2771 "issue_id_p" "issue"."id"%TYPE,
2772 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2773 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2774 RETURNS "delegation_info_type"
2775 LANGUAGE 'plpgsql' STABLE AS $$
2776 DECLARE
2777 "current_row" "delegation_chain_row";
2778 "result" "delegation_info_type";
2779 BEGIN
2780 "result"."own_participation" := FALSE;
2781 FOR "current_row" IN
2782 SELECT * FROM "delegation_chain"(
2783 "member_id_p",
2784 "unit_id_p", "area_id_p", "issue_id_p",
2785 "simulate_trustee_id_p", "simulate_default_p")
2786 LOOP
2787 IF
2788 "result"."participating_member_id" ISNULL AND
2789 "current_row"."participation"
2790 THEN
2791 "result"."participating_member_id" := "current_row"."member_id";
2792 END IF;
2793 IF "current_row"."member_id" = "member_id_p" THEN
2794 "result"."own_participation" := "current_row"."participation";
2795 "result"."own_delegation_scope" := "current_row"."scope_out";
2796 IF "current_row"."loop" = 'first' THEN
2797 "result"."delegation_loop" := 'own';
2798 END IF;
2799 ELSIF
2800 "current_row"."member_valid" AND
2801 ( "current_row"."loop" ISNULL OR
2802 "current_row"."loop" != 'repetition' )
2803 THEN
2804 IF "result"."first_trustee_id" ISNULL THEN
2805 "result"."first_trustee_id" := "current_row"."member_id";
2806 "result"."first_trustee_participation" := "current_row"."participation";
2807 "result"."first_trustee_ellipsis" := FALSE;
2808 IF "current_row"."loop" = 'first' THEN
2809 "result"."delegation_loop" := 'first';
2810 END IF;
2811 ELSIF "result"."other_trustee_id" ISNULL THEN
2812 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
2813 "result"."other_trustee_id" := "current_row"."member_id";
2814 "result"."other_trustee_participation" := TRUE;
2815 "result"."other_trustee_ellipsis" := FALSE;
2816 IF "current_row"."loop" = 'first' THEN
2817 "result"."delegation_loop" := 'other';
2818 END IF;
2819 ELSE
2820 "result"."first_trustee_ellipsis" := TRUE;
2821 IF "current_row"."loop" = 'first' THEN
2822 "result"."delegation_loop" := 'first_ellipsis';
2823 END IF;
2824 END IF;
2825 ELSE
2826 "result"."other_trustee_ellipsis" := TRUE;
2827 IF "current_row"."loop" = 'first' THEN
2828 "result"."delegation_loop" := 'other_ellipsis';
2829 END IF;
2830 END IF;
2831 END IF;
2832 END LOOP;
2833 RETURN "result";
2834 END;
2835 $$;
2837 COMMENT ON FUNCTION "delegation_info"
2838 ( "member"."id"%TYPE,
2839 "unit"."id"%TYPE,
2840 "area"."id"%TYPE,
2841 "issue"."id"%TYPE,
2842 "member"."id"%TYPE,
2843 BOOLEAN )
2844 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
2848 ---------------------------
2849 -- Transaction isolation --
2850 ---------------------------
2853 CREATE FUNCTION "require_transaction_isolation"()
2854 RETURNS VOID
2855 LANGUAGE 'plpgsql' VOLATILE AS $$
2856 BEGIN
2857 IF
2858 current_setting('transaction_isolation') NOT IN
2859 ('repeatable read', 'serializable')
2860 THEN
2861 RAISE EXCEPTION 'Insufficient transaction isolation level';
2862 END IF;
2863 RETURN;
2864 END;
2865 $$;
2867 COMMENT ON FUNCTION "require_transaction_isolation"() IS 'Throws an exception, if transaction isolation level is too low to provide a consistent snapshot';
2870 CREATE FUNCTION "dont_require_transaction_isolation"()
2871 RETURNS VOID
2872 LANGUAGE 'plpgsql' VOLATILE AS $$
2873 BEGIN
2874 IF
2875 current_setting('transaction_isolation') IN
2876 ('repeatable read', 'serializable')
2877 THEN
2878 RAISE WARNING 'Unneccessary transaction isolation level: %',
2879 current_setting('transaction_isolation');
2880 END IF;
2881 RETURN;
2882 END;
2883 $$;
2885 COMMENT ON FUNCTION "dont_require_transaction_isolation"() IS 'Raises a warning, if transaction isolation level is higher than READ COMMITTED';
2889 ------------------------------------------------------------------------
2890 -- Regular tasks, except calculcation of snapshots and voting results --
2891 ------------------------------------------------------------------------
2894 CREATE FUNCTION "check_activity"()
2895 RETURNS VOID
2896 LANGUAGE 'plpgsql' VOLATILE AS $$
2897 DECLARE
2898 "system_setting_row" "system_setting"%ROWTYPE;
2899 BEGIN
2900 PERFORM "dont_require_transaction_isolation"();
2901 SELECT * INTO "system_setting_row" FROM "system_setting";
2902 IF "system_setting_row"."member_ttl" NOTNULL THEN
2903 UPDATE "member" SET "active" = FALSE
2904 WHERE "active" = TRUE
2905 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
2906 END IF;
2907 RETURN;
2908 END;
2909 $$;
2911 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
2914 CREATE FUNCTION "calculate_member_counts"()
2915 RETURNS VOID
2916 LANGUAGE 'plpgsql' VOLATILE AS $$
2917 BEGIN
2918 PERFORM "require_transaction_isolation"();
2919 DELETE FROM "member_count";
2920 INSERT INTO "member_count" ("total_count")
2921 SELECT "total_count" FROM "member_count_view";
2922 UPDATE "unit" SET "member_count" = "view"."member_count"
2923 FROM "unit_member_count" AS "view"
2924 WHERE "view"."unit_id" = "unit"."id";
2925 UPDATE "area" SET
2926 "direct_member_count" = "view"."direct_member_count",
2927 "member_weight" = "view"."member_weight"
2928 FROM "area_member_count" AS "view"
2929 WHERE "view"."area_id" = "area"."id";
2930 RETURN;
2931 END;
2932 $$;
2934 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"';
2938 ------------------------------------
2939 -- Calculation of harmonic weight --
2940 ------------------------------------
2943 CREATE VIEW "remaining_harmonic_supporter_weight" AS
2944 SELECT
2945 "direct_interest_snapshot"."issue_id",
2946 "direct_interest_snapshot"."event",
2947 "direct_interest_snapshot"."member_id",
2948 "direct_interest_snapshot"."weight" AS "weight_num",
2949 count("initiative"."id") AS "weight_den"
2950 FROM "issue"
2951 JOIN "direct_interest_snapshot"
2952 ON "issue"."id" = "direct_interest_snapshot"."issue_id"
2953 AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
2954 JOIN "initiative"
2955 ON "issue"."id" = "initiative"."issue_id"
2956 AND "initiative"."harmonic_weight" ISNULL
2957 JOIN "direct_supporter_snapshot"
2958 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
2959 AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event"
2960 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
2961 AND (
2962 "direct_supporter_snapshot"."satisfied" = TRUE OR
2963 coalesce("initiative"."admitted", FALSE) = FALSE
2964 )
2965 GROUP BY
2966 "direct_interest_snapshot"."issue_id",
2967 "direct_interest_snapshot"."event",
2968 "direct_interest_snapshot"."member_id",
2969 "direct_interest_snapshot"."weight";
2971 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
2974 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
2975 SELECT
2976 "initiative"."issue_id",
2977 "initiative"."id" AS "initiative_id",
2978 "initiative"."admitted",
2979 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
2980 "remaining_harmonic_supporter_weight"."weight_den"
2981 FROM "remaining_harmonic_supporter_weight"
2982 JOIN "initiative"
2983 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
2984 AND "initiative"."harmonic_weight" ISNULL
2985 JOIN "direct_supporter_snapshot"
2986 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
2987 AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"
2988 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
2989 AND (
2990 "direct_supporter_snapshot"."satisfied" = TRUE OR
2991 coalesce("initiative"."admitted", FALSE) = FALSE
2992 )
2993 GROUP BY
2994 "initiative"."issue_id",
2995 "initiative"."id",
2996 "initiative"."admitted",
2997 "remaining_harmonic_supporter_weight"."weight_den";
2999 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
3002 CREATE VIEW "remaining_harmonic_initiative_weight_dummies" AS
3003 SELECT
3004 "issue_id",
3005 "id" AS "initiative_id",
3006 "admitted",
3007 0 AS "weight_num",
3008 1 AS "weight_den"
3009 FROM "initiative"
3010 WHERE "harmonic_weight" ISNULL;
3012 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';
3015 CREATE FUNCTION "set_harmonic_initiative_weights"
3016 ( "issue_id_p" "issue"."id"%TYPE )
3017 RETURNS VOID
3018 LANGUAGE 'plpgsql' VOLATILE AS $$
3019 DECLARE
3020 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
3021 "i" INT4;
3022 "count_v" INT4;
3023 "summand_v" FLOAT;
3024 "id_ary" INT4[];
3025 "weight_ary" FLOAT[];
3026 "min_weight_v" FLOAT;
3027 BEGIN
3028 PERFORM "require_transaction_isolation"();
3029 UPDATE "initiative" SET "harmonic_weight" = NULL
3030 WHERE "issue_id" = "issue_id_p";
3031 LOOP
3032 "min_weight_v" := NULL;
3033 "i" := 0;
3034 "count_v" := 0;
3035 FOR "weight_row" IN
3036 SELECT * FROM "remaining_harmonic_initiative_weight_summands"
3037 WHERE "issue_id" = "issue_id_p"
3038 AND (
3039 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
3040 SELECT NULL FROM "initiative"
3041 WHERE "issue_id" = "issue_id_p"
3042 AND "harmonic_weight" ISNULL
3043 AND coalesce("admitted", FALSE) = FALSE
3044 )
3045 )
3046 UNION ALL -- needed for corner cases
3047 SELECT * FROM "remaining_harmonic_initiative_weight_dummies"
3048 WHERE "issue_id" = "issue_id_p"
3049 AND (
3050 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
3051 SELECT NULL FROM "initiative"
3052 WHERE "issue_id" = "issue_id_p"
3053 AND "harmonic_weight" ISNULL
3054 AND coalesce("admitted", FALSE) = FALSE
3055 )
3056 )
3057 ORDER BY "initiative_id" DESC, "weight_den" DESC
3058 -- NOTE: non-admitted initiatives placed first (at last positions),
3059 -- latest initiatives treated worse in case of tie
3060 LOOP
3061 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
3062 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
3063 "i" := "i" + 1;
3064 "count_v" := "i";
3065 "id_ary"["i"] := "weight_row"."initiative_id";
3066 "weight_ary"["i"] := "summand_v";
3067 ELSE
3068 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
3069 END IF;
3070 END LOOP;
3071 EXIT WHEN "count_v" = 0;
3072 "i" := 1;
3073 LOOP
3074 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
3075 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
3076 "min_weight_v" := "weight_ary"["i"];
3077 END IF;
3078 "i" := "i" + 1;
3079 EXIT WHEN "i" > "count_v";
3080 END LOOP;
3081 "i" := 1;
3082 LOOP
3083 IF "weight_ary"["i"] = "min_weight_v" THEN
3084 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
3085 WHERE "id" = "id_ary"["i"];
3086 EXIT;
3087 END IF;
3088 "i" := "i" + 1;
3089 END LOOP;
3090 END LOOP;
3091 UPDATE "initiative" SET "harmonic_weight" = 0
3092 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
3093 END;
3094 $$;
3096 COMMENT ON FUNCTION "set_harmonic_initiative_weights"
3097 ( "issue"."id"%TYPE )
3098 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
3102 ------------------------------
3103 -- Calculation of snapshots --
3104 ------------------------------
3107 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
3108 ( "issue_id_p" "issue"."id"%TYPE,
3109 "member_id_p" "member"."id"%TYPE,
3110 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3111 RETURNS "direct_population_snapshot"."weight"%TYPE
3112 LANGUAGE 'plpgsql' VOLATILE AS $$
3113 DECLARE
3114 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3115 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
3116 "weight_v" INT4;
3117 "sub_weight_v" INT4;
3118 BEGIN
3119 PERFORM "require_transaction_isolation"();
3120 "weight_v" := 0;
3121 FOR "issue_delegation_row" IN
3122 SELECT * FROM "issue_delegation"
3123 WHERE "trustee_id" = "member_id_p"
3124 AND "issue_id" = "issue_id_p"
3125 LOOP
3126 IF NOT EXISTS (
3127 SELECT NULL FROM "direct_population_snapshot"
3128 WHERE "issue_id" = "issue_id_p"
3129 AND "event" = 'periodic'
3130 AND "member_id" = "issue_delegation_row"."truster_id"
3131 ) AND NOT EXISTS (
3132 SELECT NULL FROM "delegating_population_snapshot"
3133 WHERE "issue_id" = "issue_id_p"
3134 AND "event" = 'periodic'
3135 AND "member_id" = "issue_delegation_row"."truster_id"
3136 ) THEN
3137 "delegate_member_ids_v" :=
3138 "member_id_p" || "delegate_member_ids_p";
3139 INSERT INTO "delegating_population_snapshot" (
3140 "issue_id",
3141 "event",
3142 "member_id",
3143 "scope",
3144 "delegate_member_ids"
3145 ) VALUES (
3146 "issue_id_p",
3147 'periodic',
3148 "issue_delegation_row"."truster_id",
3149 "issue_delegation_row"."scope",
3150 "delegate_member_ids_v"
3151 );
3152 "sub_weight_v" := 1 +
3153 "weight_of_added_delegations_for_population_snapshot"(
3154 "issue_id_p",
3155 "issue_delegation_row"."truster_id",
3156 "delegate_member_ids_v"
3157 );
3158 UPDATE "delegating_population_snapshot"
3159 SET "weight" = "sub_weight_v"
3160 WHERE "issue_id" = "issue_id_p"
3161 AND "event" = 'periodic'
3162 AND "member_id" = "issue_delegation_row"."truster_id";
3163 "weight_v" := "weight_v" + "sub_weight_v";
3164 END IF;
3165 END LOOP;
3166 RETURN "weight_v";
3167 END;
3168 $$;
3170 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
3171 ( "issue"."id"%TYPE,
3172 "member"."id"%TYPE,
3173 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3174 IS 'Helper function for "create_population_snapshot" function';
3177 CREATE FUNCTION "create_population_snapshot"
3178 ( "issue_id_p" "issue"."id"%TYPE )
3179 RETURNS VOID
3180 LANGUAGE 'plpgsql' VOLATILE AS $$
3181 DECLARE
3182 "member_id_v" "member"."id"%TYPE;
3183 BEGIN
3184 PERFORM "require_transaction_isolation"();
3185 DELETE FROM "direct_population_snapshot"
3186 WHERE "issue_id" = "issue_id_p"
3187 AND "event" = 'periodic';
3188 DELETE FROM "delegating_population_snapshot"
3189 WHERE "issue_id" = "issue_id_p"
3190 AND "event" = 'periodic';
3191 INSERT INTO "direct_population_snapshot"
3192 ("issue_id", "event", "member_id")
3193 SELECT
3194 "issue_id_p" AS "issue_id",
3195 'periodic'::"snapshot_event" AS "event",
3196 "member"."id" AS "member_id"
3197 FROM "issue"
3198 JOIN "area" ON "issue"."area_id" = "area"."id"
3199 JOIN "membership" ON "area"."id" = "membership"."area_id"
3200 JOIN "member" ON "membership"."member_id" = "member"."id"
3201 JOIN "privilege"
3202 ON "privilege"."unit_id" = "area"."unit_id"
3203 AND "privilege"."member_id" = "member"."id"
3204 WHERE "issue"."id" = "issue_id_p"
3205 AND "member"."active" AND "privilege"."voting_right"
3206 UNION
3207 SELECT
3208 "issue_id_p" AS "issue_id",
3209 'periodic'::"snapshot_event" AS "event",
3210 "member"."id" AS "member_id"
3211 FROM "issue"
3212 JOIN "area" ON "issue"."area_id" = "area"."id"
3213 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3214 JOIN "member" ON "interest"."member_id" = "member"."id"
3215 JOIN "privilege"
3216 ON "privilege"."unit_id" = "area"."unit_id"
3217 AND "privilege"."member_id" = "member"."id"
3218 WHERE "issue"."id" = "issue_id_p"
3219 AND "member"."active" AND "privilege"."voting_right";
3220 FOR "member_id_v" IN
3221 SELECT "member_id" FROM "direct_population_snapshot"
3222 WHERE "issue_id" = "issue_id_p"
3223 AND "event" = 'periodic'
3224 LOOP
3225 UPDATE "direct_population_snapshot" SET
3226 "weight" = 1 +
3227 "weight_of_added_delegations_for_population_snapshot"(
3228 "issue_id_p",
3229 "member_id_v",
3230 '{}'
3231 )
3232 WHERE "issue_id" = "issue_id_p"
3233 AND "event" = 'periodic'
3234 AND "member_id" = "member_id_v";
3235 END LOOP;
3236 RETURN;
3237 END;
3238 $$;
3240 COMMENT ON FUNCTION "create_population_snapshot"
3241 ( "issue"."id"%TYPE )
3242 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.';
3245 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3246 ( "issue_id_p" "issue"."id"%TYPE,
3247 "member_id_p" "member"."id"%TYPE,
3248 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3249 RETURNS "direct_interest_snapshot"."weight"%TYPE
3250 LANGUAGE 'plpgsql' VOLATILE AS $$
3251 DECLARE
3252 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3253 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
3254 "weight_v" INT4;
3255 "sub_weight_v" INT4;
3256 BEGIN
3257 PERFORM "require_transaction_isolation"();
3258 "weight_v" := 0;
3259 FOR "issue_delegation_row" IN
3260 SELECT * FROM "issue_delegation"
3261 WHERE "trustee_id" = "member_id_p"
3262 AND "issue_id" = "issue_id_p"
3263 LOOP
3264 IF NOT EXISTS (
3265 SELECT NULL FROM "direct_interest_snapshot"
3266 WHERE "issue_id" = "issue_id_p"
3267 AND "event" = 'periodic'
3268 AND "member_id" = "issue_delegation_row"."truster_id"
3269 ) AND NOT EXISTS (
3270 SELECT NULL FROM "delegating_interest_snapshot"
3271 WHERE "issue_id" = "issue_id_p"
3272 AND "event" = 'periodic'
3273 AND "member_id" = "issue_delegation_row"."truster_id"
3274 ) THEN
3275 "delegate_member_ids_v" :=
3276 "member_id_p" || "delegate_member_ids_p";
3277 INSERT INTO "delegating_interest_snapshot" (
3278 "issue_id",
3279 "event",
3280 "member_id",
3281 "scope",
3282 "delegate_member_ids"
3283 ) VALUES (
3284 "issue_id_p",
3285 'periodic',
3286 "issue_delegation_row"."truster_id",
3287 "issue_delegation_row"."scope",
3288 "delegate_member_ids_v"
3289 );
3290 "sub_weight_v" := 1 +
3291 "weight_of_added_delegations_for_interest_snapshot"(
3292 "issue_id_p",
3293 "issue_delegation_row"."truster_id",
3294 "delegate_member_ids_v"
3295 );
3296 UPDATE "delegating_interest_snapshot"
3297 SET "weight" = "sub_weight_v"
3298 WHERE "issue_id" = "issue_id_p"
3299 AND "event" = 'periodic'
3300 AND "member_id" = "issue_delegation_row"."truster_id";
3301 "weight_v" := "weight_v" + "sub_weight_v";
3302 END IF;
3303 END LOOP;
3304 RETURN "weight_v";
3305 END;
3306 $$;
3308 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3309 ( "issue"."id"%TYPE,
3310 "member"."id"%TYPE,
3311 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3312 IS 'Helper function for "create_interest_snapshot" function';
3315 CREATE FUNCTION "create_interest_snapshot"
3316 ( "issue_id_p" "issue"."id"%TYPE )
3317 RETURNS VOID
3318 LANGUAGE 'plpgsql' VOLATILE AS $$
3319 DECLARE
3320 "member_id_v" "member"."id"%TYPE;
3321 BEGIN
3322 PERFORM "require_transaction_isolation"();
3323 DELETE FROM "direct_interest_snapshot"
3324 WHERE "issue_id" = "issue_id_p"
3325 AND "event" = 'periodic';
3326 DELETE FROM "delegating_interest_snapshot"
3327 WHERE "issue_id" = "issue_id_p"
3328 AND "event" = 'periodic';
3329 DELETE FROM "direct_supporter_snapshot"
3330 USING "initiative" -- NOTE: due to missing index on issue_id
3331 WHERE "initiative"."issue_id" = "issue_id_p"
3332 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
3333 AND "direct_supporter_snapshot"."event" = 'periodic';
3334 INSERT INTO "direct_interest_snapshot"
3335 ("issue_id", "event", "member_id")
3336 SELECT
3337 "issue_id_p" AS "issue_id",
3338 'periodic' AS "event",
3339 "member"."id" AS "member_id"
3340 FROM "issue"
3341 JOIN "area" ON "issue"."area_id" = "area"."id"
3342 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3343 JOIN "member" ON "interest"."member_id" = "member"."id"
3344 JOIN "privilege"
3345 ON "privilege"."unit_id" = "area"."unit_id"
3346 AND "privilege"."member_id" = "member"."id"
3347 WHERE "issue"."id" = "issue_id_p"
3348 AND "member"."active" AND "privilege"."voting_right";
3349 FOR "member_id_v" IN
3350 SELECT "member_id" FROM "direct_interest_snapshot"
3351 WHERE "issue_id" = "issue_id_p"
3352 AND "event" = 'periodic'
3353 LOOP
3354 UPDATE "direct_interest_snapshot" SET
3355 "weight" = 1 +
3356 "weight_of_added_delegations_for_interest_snapshot"(
3357 "issue_id_p",
3358 "member_id_v",
3359 '{}'
3360 )
3361 WHERE "issue_id" = "issue_id_p"
3362 AND "event" = 'periodic'
3363 AND "member_id" = "member_id_v";
3364 END LOOP;
3365 INSERT INTO "direct_supporter_snapshot"
3366 ( "issue_id", "initiative_id", "event", "member_id",
3367 "draft_id", "informed", "satisfied" )
3368 SELECT
3369 "issue_id_p" AS "issue_id",
3370 "initiative"."id" AS "initiative_id",
3371 'periodic' AS "event",
3372 "supporter"."member_id" AS "member_id",
3373 "supporter"."draft_id" AS "draft_id",
3374 "supporter"."draft_id" = "current_draft"."id" AS "informed",
3375 NOT EXISTS (
3376 SELECT NULL FROM "critical_opinion"
3377 WHERE "initiative_id" = "initiative"."id"
3378 AND "member_id" = "supporter"."member_id"
3379 ) AS "satisfied"
3380 FROM "initiative"
3381 JOIN "supporter"
3382 ON "supporter"."initiative_id" = "initiative"."id"
3383 JOIN "current_draft"
3384 ON "initiative"."id" = "current_draft"."initiative_id"
3385 JOIN "direct_interest_snapshot"
3386 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
3387 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
3388 AND "event" = 'periodic'
3389 WHERE "initiative"."issue_id" = "issue_id_p";
3390 RETURN;
3391 END;
3392 $$;
3394 COMMENT ON FUNCTION "create_interest_snapshot"
3395 ( "issue"."id"%TYPE )
3396 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.';
3399 CREATE FUNCTION "create_snapshot"
3400 ( "issue_id_p" "issue"."id"%TYPE )
3401 RETURNS VOID
3402 LANGUAGE 'plpgsql' VOLATILE AS $$
3403 DECLARE
3404 "initiative_id_v" "initiative"."id"%TYPE;
3405 "suggestion_id_v" "suggestion"."id"%TYPE;
3406 BEGIN
3407 PERFORM "require_transaction_isolation"();
3408 PERFORM "create_population_snapshot"("issue_id_p");
3409 PERFORM "create_interest_snapshot"("issue_id_p");
3410 UPDATE "issue" SET
3411 "snapshot" = coalesce("phase_finished", now()),
3412 "latest_snapshot_event" = 'periodic',
3413 "population" = (
3414 SELECT coalesce(sum("weight"), 0)
3415 FROM "direct_population_snapshot"
3416 WHERE "issue_id" = "issue_id_p"
3417 AND "event" = 'periodic'
3418 )
3419 WHERE "id" = "issue_id_p";
3420 FOR "initiative_id_v" IN
3421 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
3422 LOOP
3423 UPDATE "initiative" SET
3424 "supporter_count" = (
3425 SELECT coalesce(sum("di"."weight"), 0)
3426 FROM "direct_interest_snapshot" AS "di"
3427 JOIN "direct_supporter_snapshot" AS "ds"
3428 ON "di"."member_id" = "ds"."member_id"
3429 WHERE "di"."issue_id" = "issue_id_p"
3430 AND "di"."event" = 'periodic'
3431 AND "ds"."initiative_id" = "initiative_id_v"
3432 AND "ds"."event" = 'periodic'
3433 ),
3434 "informed_supporter_count" = (
3435 SELECT coalesce(sum("di"."weight"), 0)
3436 FROM "direct_interest_snapshot" AS "di"
3437 JOIN "direct_supporter_snapshot" AS "ds"
3438 ON "di"."member_id" = "ds"."member_id"
3439 WHERE "di"."issue_id" = "issue_id_p"
3440 AND "di"."event" = 'periodic'
3441 AND "ds"."initiative_id" = "initiative_id_v"
3442 AND "ds"."event" = 'periodic'
3443 AND "ds"."informed"
3444 ),
3445 "satisfied_supporter_count" = (
3446 SELECT coalesce(sum("di"."weight"), 0)
3447 FROM "direct_interest_snapshot" AS "di"
3448 JOIN "direct_supporter_snapshot" AS "ds"
3449 ON "di"."member_id" = "ds"."member_id"
3450 WHERE "di"."issue_id" = "issue_id_p"
3451 AND "di"."event" = 'periodic'
3452 AND "ds"."initiative_id" = "initiative_id_v"
3453 AND "ds"."event" = 'periodic'
3454 AND "ds"."satisfied"
3455 ),
3456 "satisfied_informed_supporter_count" = (
3457 SELECT coalesce(sum("di"."weight"), 0)
3458 FROM "direct_interest_snapshot" AS "di"
3459 JOIN "direct_supporter_snapshot" AS "ds"
3460 ON "di"."member_id" = "ds"."member_id"
3461 WHERE "di"."issue_id" = "issue_id_p"
3462 AND "di"."event" = 'periodic'
3463 AND "ds"."initiative_id" = "initiative_id_v"
3464 AND "ds"."event" = 'periodic'
3465 AND "ds"."informed"
3466 AND "ds"."satisfied"
3467 )
3468 WHERE "id" = "initiative_id_v";
3469 FOR "suggestion_id_v" IN
3470 SELECT "id" FROM "suggestion"
3471 WHERE "initiative_id" = "initiative_id_v"
3472 LOOP
3473 UPDATE "suggestion" SET
3474 "minus2_unfulfilled_count" = (
3475 SELECT coalesce(sum("snapshot"."weight"), 0)
3476 FROM "issue" CROSS JOIN "opinion"
3477 JOIN "direct_interest_snapshot" AS "snapshot"
3478 ON "snapshot"."issue_id" = "issue"."id"
3479 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3480 AND "snapshot"."member_id" = "opinion"."member_id"
3481 WHERE "issue"."id" = "issue_id_p"
3482 AND "opinion"."suggestion_id" = "suggestion_id_v"
3483 AND "opinion"."degree" = -2
3484 AND "opinion"."fulfilled" = FALSE
3485 ),
3486 "minus2_fulfilled_count" = (
3487 SELECT coalesce(sum("snapshot"."weight"), 0)
3488 FROM "issue" CROSS JOIN "opinion"
3489 JOIN "direct_interest_snapshot" AS "snapshot"
3490 ON "snapshot"."issue_id" = "issue"."id"
3491 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3492 AND "snapshot"."member_id" = "opinion"."member_id"
3493 WHERE "issue"."id" = "issue_id_p"
3494 AND "opinion"."suggestion_id" = "suggestion_id_v"
3495 AND "opinion"."degree" = -2
3496 AND "opinion"."fulfilled" = TRUE
3497 ),
3498 "minus1_unfulfilled_count" = (
3499 SELECT coalesce(sum("snapshot"."weight"), 0)
3500 FROM "issue" CROSS JOIN "opinion"
3501 JOIN "direct_interest_snapshot" AS "snapshot"
3502 ON "snapshot"."issue_id" = "issue"."id"
3503 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3504 AND "snapshot"."member_id" = "opinion"."member_id"
3505 WHERE "issue"."id" = "issue_id_p"
3506 AND "opinion"."suggestion_id" = "suggestion_id_v"
3507 AND "opinion"."degree" = -1
3508 AND "opinion"."fulfilled" = FALSE
3509 ),
3510 "minus1_fulfilled_count" = (
3511 SELECT coalesce(sum("snapshot"."weight"), 0)
3512 FROM "issue" CROSS JOIN "opinion"
3513 JOIN "direct_interest_snapshot" AS "snapshot"
3514 ON "snapshot"."issue_id" = "issue"."id"
3515 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3516 AND "snapshot"."member_id" = "opinion"."member_id"
3517 WHERE "issue"."id" = "issue_id_p"
3518 AND "opinion"."suggestion_id" = "suggestion_id_v"
3519 AND "opinion"."degree" = -1
3520 AND "opinion"."fulfilled" = TRUE
3521 ),
3522 "plus1_unfulfilled_count" = (
3523 SELECT coalesce(sum("snapshot"."weight"), 0)
3524 FROM "issue" CROSS JOIN "opinion"
3525 JOIN "direct_interest_snapshot" AS "snapshot"
3526 ON "snapshot"."issue_id" = "issue"."id"
3527 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3528 AND "snapshot"."member_id" = "opinion"."member_id"
3529 WHERE "issue"."id" = "issue_id_p"
3530 AND "opinion"."suggestion_id" = "suggestion_id_v"
3531 AND "opinion"."degree" = 1
3532 AND "opinion"."fulfilled" = FALSE
3533 ),
3534 "plus1_fulfilled_count" = (
3535 SELECT coalesce(sum("snapshot"."weight"), 0)
3536 FROM "issue" CROSS JOIN "opinion"
3537 JOIN "direct_interest_snapshot" AS "snapshot"
3538 ON "snapshot"."issue_id" = "issue"."id"
3539 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3540 AND "snapshot"."member_id" = "opinion"."member_id"
3541 WHERE "issue"."id" = "issue_id_p"
3542 AND "opinion"."suggestion_id" = "suggestion_id_v"
3543 AND "opinion"."degree" = 1
3544 AND "opinion"."fulfilled" = TRUE
3545 ),
3546 "plus2_unfulfilled_count" = (
3547 SELECT coalesce(sum("snapshot"."weight"), 0)
3548 FROM "issue" CROSS JOIN "opinion"
3549 JOIN "direct_interest_snapshot" AS "snapshot"
3550 ON "snapshot"."issue_id" = "issue"."id"
3551 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3552 AND "snapshot"."member_id" = "opinion"."member_id"
3553 WHERE "issue"."id" = "issue_id_p"
3554 AND "opinion"."suggestion_id" = "suggestion_id_v"
3555 AND "opinion"."degree" = 2
3556 AND "opinion"."fulfilled" = FALSE
3557 ),
3558 "plus2_fulfilled_count" = (
3559 SELECT coalesce(sum("snapshot"."weight"), 0)
3560 FROM "issue" CROSS JOIN "opinion"
3561 JOIN "direct_interest_snapshot" AS "snapshot"
3562 ON "snapshot"."issue_id" = "issue"."id"
3563 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3564 AND "snapshot"."member_id" = "opinion"."member_id"
3565 WHERE "issue"."id" = "issue_id_p"
3566 AND "opinion"."suggestion_id" = "suggestion_id_v"
3567 AND "opinion"."degree" = 2
3568 AND "opinion"."fulfilled" = TRUE
3569 )
3570 WHERE "suggestion"."id" = "suggestion_id_v";
3571 END LOOP;
3572 END LOOP;
3573 RETURN;
3574 END;
3575 $$;
3577 COMMENT ON FUNCTION "create_snapshot"
3578 ( "issue"."id"%TYPE )
3579 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.';
3582 CREATE FUNCTION "set_snapshot_event"
3583 ( "issue_id_p" "issue"."id"%TYPE,
3584 "event_p" "snapshot_event" )
3585 RETURNS VOID
3586 LANGUAGE 'plpgsql' VOLATILE AS $$
3587 DECLARE
3588 "event_v" "issue"."latest_snapshot_event"%TYPE;
3589 BEGIN
3590 PERFORM "require_transaction_isolation"();
3591 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
3592 WHERE "id" = "issue_id_p" FOR UPDATE;
3593 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
3594 WHERE "id" = "issue_id_p";
3595 UPDATE "direct_population_snapshot" SET "event" = "event_p"
3596 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3597 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
3598 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3599 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
3600 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3601 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
3602 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3603 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
3604 FROM "initiative" -- NOTE: due to missing index on issue_id
3605 WHERE "initiative"."issue_id" = "issue_id_p"
3606 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
3607 AND "direct_supporter_snapshot"."event" = "event_v";
3608 RETURN;
3609 END;
3610 $$;
3612 COMMENT ON FUNCTION "set_snapshot_event"
3613 ( "issue"."id"%TYPE,
3614 "snapshot_event" )
3615 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
3619 -----------------------
3620 -- Counting of votes --
3621 -----------------------
3624 CREATE FUNCTION "weight_of_added_vote_delegations"
3625 ( "issue_id_p" "issue"."id"%TYPE,
3626 "member_id_p" "member"."id"%TYPE,
3627 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
3628 RETURNS "direct_voter"."weight"%TYPE
3629 LANGUAGE 'plpgsql' VOLATILE AS $$
3630 DECLARE
3631 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3632 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
3633 "weight_v" INT4;
3634 "sub_weight_v" INT4;
3635 BEGIN
3636 PERFORM "require_transaction_isolation"();
3637 "weight_v" := 0;
3638 FOR "issue_delegation_row" IN
3639 SELECT * FROM "issue_delegation"
3640 WHERE "trustee_id" = "member_id_p"
3641 AND "issue_id" = "issue_id_p"
3642 LOOP
3643 IF NOT EXISTS (
3644 SELECT NULL FROM "direct_voter"
3645 WHERE "member_id" = "issue_delegation_row"."truster_id"
3646 AND "issue_id" = "issue_id_p"
3647 ) AND NOT EXISTS (
3648 SELECT NULL FROM "delegating_voter"
3649 WHERE "member_id" = "issue_delegation_row"."truster_id"
3650 AND "issue_id" = "issue_id_p"
3651 ) THEN
3652 "delegate_member_ids_v" :=
3653 "member_id_p" || "delegate_member_ids_p";
3654 INSERT INTO "delegating_voter" (
3655 "issue_id",
3656 "member_id",
3657 "scope",
3658 "delegate_member_ids"
3659 ) VALUES (
3660 "issue_id_p",
3661 "issue_delegation_row"."truster_id",
3662 "issue_delegation_row"."scope",
3663 "delegate_member_ids_v"
3664 );
3665 "sub_weight_v" := 1 +
3666 "weight_of_added_vote_delegations"(
3667 "issue_id_p",
3668 "issue_delegation_row"."truster_id",
3669 "delegate_member_ids_v"
3670 );
3671 UPDATE "delegating_voter"
3672 SET "weight" = "sub_weight_v"
3673 WHERE "issue_id" = "issue_id_p"
3674 AND "member_id" = "issue_delegation_row"."truster_id";
3675 "weight_v" := "weight_v" + "sub_weight_v";
3676 END IF;
3677 END LOOP;
3678 RETURN "weight_v";
3679 END;
3680 $$;
3682 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
3683 ( "issue"."id"%TYPE,
3684 "member"."id"%TYPE,
3685 "delegating_voter"."delegate_member_ids"%TYPE )
3686 IS 'Helper function for "add_vote_delegations" function';
3689 CREATE FUNCTION "add_vote_delegations"
3690 ( "issue_id_p" "issue"."id"%TYPE )
3691 RETURNS VOID
3692 LANGUAGE 'plpgsql' VOLATILE AS $$
3693 DECLARE
3694 "member_id_v" "member"."id"%TYPE;
3695 BEGIN
3696 PERFORM "require_transaction_isolation"();
3697 FOR "member_id_v" IN
3698 SELECT "member_id" FROM "direct_voter"
3699 WHERE "issue_id" = "issue_id_p"
3700 LOOP
3701 UPDATE "direct_voter" SET
3702 "weight" = "weight" + "weight_of_added_vote_delegations"(
3703 "issue_id_p",
3704 "member_id_v",
3705 '{}'
3706 )
3707 WHERE "member_id" = "member_id_v"
3708 AND "issue_id" = "issue_id_p";
3709 END LOOP;
3710 RETURN;
3711 END;
3712 $$;
3714 COMMENT ON FUNCTION "add_vote_delegations"
3715 ( "issue_id_p" "issue"."id"%TYPE )
3716 IS 'Helper function for "close_voting" function';
3719 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
3720 RETURNS VOID
3721 LANGUAGE 'plpgsql' VOLATILE AS $$
3722 DECLARE
3723 "area_id_v" "area"."id"%TYPE;
3724 "unit_id_v" "unit"."id"%TYPE;
3725 "member_id_v" "member"."id"%TYPE;
3726 BEGIN
3727 PERFORM "require_transaction_isolation"();
3728 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
3729 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
3730 -- override protection triggers:
3731 INSERT INTO "temporary_transaction_data" ("key", "value")
3732 VALUES ('override_protection_triggers', TRUE::TEXT);
3733 -- delete timestamp of voting comment:
3734 UPDATE "direct_voter" SET "comment_changed" = NULL
3735 WHERE "issue_id" = "issue_id_p";
3736 -- delete delegating votes (in cases of manual reset of issue state):
3737 DELETE FROM "delegating_voter"
3738 WHERE "issue_id" = "issue_id_p";
3739 -- delete votes from non-privileged voters:
3740 DELETE FROM "direct_voter"
3741 USING (
3742 SELECT
3743 "direct_voter"."member_id"
3744 FROM "direct_voter"
3745 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
3746 LEFT JOIN "privilege"
3747 ON "privilege"."unit_id" = "unit_id_v"
3748 AND "privilege"."member_id" = "direct_voter"."member_id"
3749 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
3750 "member"."active" = FALSE OR
3751 "privilege"."voting_right" ISNULL OR
3752 "privilege"."voting_right" = FALSE
3753 )
3754 ) AS "subquery"
3755 WHERE "direct_voter"."issue_id" = "issue_id_p"
3756 AND "direct_voter"."member_id" = "subquery"."member_id";
3757 -- consider delegations:
3758 UPDATE "direct_voter" SET "weight" = 1
3759 WHERE "issue_id" = "issue_id_p";
3760 PERFORM "add_vote_delegations"("issue_id_p");
3761 -- mark first preferences:
3762 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
3763 FROM (
3764 SELECT
3765 "vote"."initiative_id",
3766 "vote"."member_id",
3767 CASE WHEN "vote"."grade" > 0 THEN
3768 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
3769 ELSE NULL
3770 END AS "first_preference"
3771 FROM "vote"
3772 JOIN "initiative" -- NOTE: due to missing index on issue_id
3773 ON "vote"."issue_id" = "initiative"."issue_id"
3774 JOIN "vote" AS "agg"
3775 ON "initiative"."id" = "agg"."initiative_id"
3776 AND "vote"."member_id" = "agg"."member_id"
3777 GROUP BY "vote"."initiative_id", "vote"."member_id"
3778 ) AS "subquery"
3779 WHERE "vote"."issue_id" = "issue_id_p"
3780 AND "vote"."initiative_id" = "subquery"."initiative_id"
3781 AND "vote"."member_id" = "subquery"."member_id";
3782 -- finish overriding protection triggers (avoids garbage):
3783 DELETE FROM "temporary_transaction_data"
3784 WHERE "key" = 'override_protection_triggers';
3785 -- materialize battle_view:
3786 -- NOTE: "closed" column of issue must be set at this point
3787 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3788 INSERT INTO "battle" (
3789 "issue_id",
3790 "winning_initiative_id", "losing_initiative_id",
3791 "count"
3792 ) SELECT
3793 "issue_id",
3794 "winning_initiative_id", "losing_initiative_id",
3795 "count"
3796 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
3797 -- set voter count:
3798 UPDATE "issue" SET
3799 "voter_count" = (
3800 SELECT coalesce(sum("weight"), 0)
3801 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
3802 )
3803 WHERE "id" = "issue_id_p";
3804 -- calculate "first_preference_votes":
3805 UPDATE "initiative"
3806 SET "first_preference_votes" = coalesce("subquery"."sum", 0)
3807 FROM (
3808 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
3809 FROM "vote" JOIN "direct_voter"
3810 ON "vote"."issue_id" = "direct_voter"."issue_id"
3811 AND "vote"."member_id" = "direct_voter"."member_id"
3812 WHERE "vote"."first_preference"
3813 GROUP BY "vote"."initiative_id"
3814 ) AS "subquery"
3815 WHERE "initiative"."issue_id" = "issue_id_p"
3816 AND "initiative"."admitted"
3817 AND "initiative"."id" = "subquery"."initiative_id";
3818 -- copy "positive_votes" and "negative_votes" from "battle" table:
3819 UPDATE "initiative" SET
3820 "positive_votes" = "battle_win"."count",
3821 "negative_votes" = "battle_lose"."count"
3822 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
3823 WHERE
3824 "battle_win"."issue_id" = "issue_id_p" AND
3825 "battle_win"."winning_initiative_id" = "initiative"."id" AND
3826 "battle_win"."losing_initiative_id" ISNULL AND
3827 "battle_lose"."issue_id" = "issue_id_p" AND
3828 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
3829 "battle_lose"."winning_initiative_id" ISNULL;
3830 END;
3831 $$;
3833 COMMENT ON FUNCTION "close_voting"
3834 ( "issue"."id"%TYPE )
3835 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.';
3838 CREATE FUNCTION "defeat_strength"
3839 ( "positive_votes_p" INT4,
3840 "negative_votes_p" INT4,
3841 "defeat_strength_p" "defeat_strength" )
3842 RETURNS INT8
3843 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3844 BEGIN
3845 IF "defeat_strength_p" = 'simple'::"defeat_strength" THEN
3846 IF "positive_votes_p" > "negative_votes_p" THEN
3847 RETURN "positive_votes_p";
3848 ELSE
3849 RETURN 0;
3850 END IF;
3851 ELSE
3852 IF "positive_votes_p" > "negative_votes_p" THEN
3853 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
3854 ELSIF "positive_votes_p" = "negative_votes_p" THEN
3855 RETURN 0;
3856 ELSE
3857 RETURN -1;
3858 END IF;
3859 END IF;
3860 END;
3861 $$;
3863 COMMENT ON FUNCTION "defeat_strength"(INT4, INT4, "defeat_strength") 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';
3866 CREATE FUNCTION "secondary_link_strength"
3867 ( "initiative_id1_p" "initiative"."id"%TYPE,
3868 "initiative_id2_p" "initiative"."id"%TYPE,
3869 "tie_breaking_p" "tie_breaking" )
3870 RETURNS INT8
3871 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3872 BEGIN
3873 IF "initiative_id1_p" = "initiative_id2_p" THEN
3874 RAISE EXCEPTION 'Identical initiative ids passed to "secondary_link_strength" function (should not happen)';
3875 END IF;
3876 RETURN (
3877 CASE WHEN "initiative_id1_p" < "initiative_id2_p" THEN
3878 1::INT8 << 62
3879 ELSE 0 END
3880 +
3881 CASE WHEN "tie_breaking_p" = 'variant2'::"tie_breaking" THEN
3882 ("initiative_id2_p"::INT8 << 31) - "initiative_id1_p"::INT8
3883 ELSE
3884 "initiative_id2_p"::INT8 - ("initiative_id1_p"::INT8 << 31)
3885 END
3886 );
3887 END;
3888 $$;
3890 COMMENT ON FUNCTION "secondary_link_strength"(INT4, INT4, "tie_breaking") IS 'Calculates a secondary criterion for the defeat strength (tie-breaking of the links)';
3893 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
3894 RETURNS VOID
3895 LANGUAGE 'plpgsql' VOLATILE AS $$
3896 DECLARE
3897 "issue_row" "issue"%ROWTYPE;
3898 "policy_row" "policy"%ROWTYPE;
3899 "dimension_v" INTEGER;
3900 "vote_matrix" INT4[][]; -- absolute votes
3901 "matrix" INT8[][]; -- defeat strength / best paths
3902 "i" INTEGER;
3903 "j" INTEGER;
3904 "k" INTEGER;
3905 "battle_row" "battle"%ROWTYPE;
3906 "rank_ary" INT4[];
3907 "rank_v" INT4;
3908 "initiative_id_v" "initiative"."id"%TYPE;
3909 BEGIN
3910 PERFORM "require_transaction_isolation"();
3911 SELECT * INTO "issue_row"
3912 FROM "issue" WHERE "id" = "issue_id_p";
3913 SELECT * INTO "policy_row"
3914 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3915 SELECT count(1) INTO "dimension_v"
3916 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
3917 -- Create "vote_matrix" with absolute number of votes in pairwise
3918 -- comparison:
3919 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
3920 "i" := 1;
3921 "j" := 2;
3922 FOR "battle_row" IN
3923 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
3924 ORDER BY
3925 "winning_initiative_id" NULLS FIRST,
3926 "losing_initiative_id" NULLS FIRST
3927 LOOP
3928 "vote_matrix"["i"]["j"] := "battle_row"."count";
3929 IF "j" = "dimension_v" THEN
3930 "i" := "i" + 1;
3931 "j" := 1;
3932 ELSE
3933 "j" := "j" + 1;
3934 IF "j" = "i" THEN
3935 "j" := "j" + 1;
3936 END IF;
3937 END IF;
3938 END LOOP;
3939 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
3940 RAISE EXCEPTION 'Wrong battle count (should not happen)';
3941 END IF;
3942 -- Store defeat strengths in "matrix" using "defeat_strength"
3943 -- function:
3944 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
3945 "i" := 1;
3946 LOOP
3947 "j" := 1;
3948 LOOP
3949 IF "i" != "j" THEN
3950 "matrix"["i"]["j"] := "defeat_strength"(
3951 "vote_matrix"["i"]["j"],
3952 "vote_matrix"["j"]["i"],
3953 "policy_row"."defeat_strength"
3954 );
3955 END IF;
3956 EXIT WHEN "j" = "dimension_v";
3957 "j" := "j" + 1;
3958 END LOOP;
3959 EXIT WHEN "i" = "dimension_v";
3960 "i" := "i" + 1;
3961 END LOOP;
3962 -- Find best paths:
3963 "i" := 1;
3964 LOOP
3965 "j" := 1;
3966 LOOP
3967 IF "i" != "j" THEN
3968 "k" := 1;
3969 LOOP
3970 IF "i" != "k" AND "j" != "k" THEN
3971 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
3972 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
3973 "matrix"["j"]["k"] := "matrix"["j"]["i"];
3974 END IF;
3975 ELSE
3976 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
3977 "matrix"["j"]["k"] := "matrix"["i"]["k"];
3978 END IF;
3979 END IF;
3980 END IF;
3981 EXIT WHEN "k" = "dimension_v";
3982 "k" := "k" + 1;
3983 END LOOP;
3984 END IF;
3985 EXIT WHEN "j" = "dimension_v";
3986 "j" := "j" + 1;
3987 END LOOP;
3988 EXIT WHEN "i" = "dimension_v";
3989 "i" := "i" + 1;
3990 END LOOP;
3991 -- Determine order of winners:
3992 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
3993 "rank_v" := 1;
3994 LOOP
3995 "i" := 1;
3996 LOOP
3997 IF "rank_ary"["i"] ISNULL THEN
3998 "j" := 1;
3999 LOOP
4000 IF
4001 "i" != "j" AND
4002 "rank_ary"["j"] ISNULL AND
4003 ( "matrix"["j"]["i"] > "matrix"["i"]["j"] OR
4004 -- tie-breaking by "id"
4005 ( "matrix"["j"]["i"] = "matrix"["i"]["j"] AND
4006 "j" < "i" ) )
4007 THEN
4008 -- someone else is better
4009 EXIT;
4010 END IF;
4011 "j" := "j" + 1;
4012 IF "j" = "dimension_v" + 1 THEN
4013 -- noone is better
4014 "rank_ary"["i"] := "rank_v";
4015 EXIT;
4016 END IF;
4017 END LOOP;
4018 EXIT WHEN "j" = "dimension_v" + 1;
4019 END IF;
4020 "i" := "i" + 1;
4021 IF "i" > "dimension_v" THEN
4022 RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
4023 END IF;
4024 END LOOP;
4025 EXIT WHEN "rank_v" = "dimension_v";
4026 "rank_v" := "rank_v" + 1;
4027 END LOOP;
4028 -- write preliminary results:
4029 "i" := 2; -- omit status quo with "i" = 1
4030 FOR "initiative_id_v" IN
4031 SELECT "id" FROM "initiative"
4032 WHERE "issue_id" = "issue_id_p" AND "admitted"
4033 ORDER BY "id"
4034 LOOP
4035 UPDATE "initiative" SET
4036 "direct_majority" =
4037 CASE WHEN "policy_row"."direct_majority_strict" THEN
4038 "positive_votes" * "policy_row"."direct_majority_den" >
4039 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
4040 ELSE
4041 "positive_votes" * "policy_row"."direct_majority_den" >=
4042 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
4043 END
4044 AND "positive_votes" >= "policy_row"."direct_majority_positive"
4045 AND "issue_row"."voter_count"-"negative_votes" >=
4046 "policy_row"."direct_majority_non_negative",
4047 "indirect_majority" =
4048 CASE WHEN "policy_row"."indirect_majority_strict" THEN
4049 "positive_votes" * "policy_row"."indirect_majority_den" >
4050 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4051 ELSE
4052 "positive_votes" * "policy_row"."indirect_majority_den" >=
4053 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4054 END
4055 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
4056 AND "issue_row"."voter_count"-"negative_votes" >=
4057 "policy_row"."indirect_majority_non_negative",
4058 "schulze_rank" = "rank_ary"["i"],
4059 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
4060 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"[1],
4061 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"[1],
4062 "reverse_beat_path" = "matrix"[1]["i"] >= 0,
4063 "eligible" = FALSE,
4064 "winner" = FALSE,
4065 "rank" = NULL -- NOTE: in cases of manual reset of issue state
4066 WHERE "id" = "initiative_id_v";
4067 "i" := "i" + 1;
4068 END LOOP;
4069 IF "i" != "dimension_v" + 1 THEN
4070 RAISE EXCEPTION 'Wrong winner count (should not happen)';
4071 END IF;
4072 -- take indirect majorities into account:
4073 LOOP
4074 UPDATE "initiative" SET "indirect_majority" = TRUE
4075 FROM (
4076 SELECT "new_initiative"."id" AS "initiative_id"
4077 FROM "initiative" "old_initiative"
4078 JOIN "initiative" "new_initiative"
4079 ON "new_initiative"."issue_id" = "issue_id_p"
4080 AND "new_initiative"."indirect_majority" = FALSE
4081 JOIN "battle" "battle_win"
4082 ON "battle_win"."issue_id" = "issue_id_p"
4083 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
4084 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
4085 JOIN "battle" "battle_lose"
4086 ON "battle_lose"."issue_id" = "issue_id_p"
4087 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
4088 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
4089 WHERE "old_initiative"."issue_id" = "issue_id_p"
4090 AND "old_initiative"."indirect_majority" = TRUE
4091 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
4092 "battle_win"."count" * "policy_row"."indirect_majority_den" >
4093 "policy_row"."indirect_majority_num" *
4094 ("battle_win"."count"+"battle_lose"."count")
4095 ELSE
4096 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
4097 "policy_row"."indirect_majority_num" *
4098 ("battle_win"."count"+"battle_lose"."count")
4099 END
4100 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
4101 AND "issue_row"."voter_count"-"battle_lose"."count" >=
4102 "policy_row"."indirect_majority_non_negative"
4103 ) AS "subquery"
4104 WHERE "id" = "subquery"."initiative_id";
4105 EXIT WHEN NOT FOUND;
4106 END LOOP;
4107 -- set "multistage_majority" for remaining matching initiatives:
4108 UPDATE "initiative" SET "multistage_majority" = TRUE
4109 FROM (
4110 SELECT "losing_initiative"."id" AS "initiative_id"
4111 FROM "initiative" "losing_initiative"
4112 JOIN "initiative" "winning_initiative"
4113 ON "winning_initiative"."issue_id" = "issue_id_p"
4114 AND "winning_initiative"."admitted"
4115 JOIN "battle" "battle_win"
4116 ON "battle_win"."issue_id" = "issue_id_p"
4117 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
4118 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
4119 JOIN "battle" "battle_lose"
4120 ON "battle_lose"."issue_id" = "issue_id_p"
4121 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
4122 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
4123 WHERE "losing_initiative"."issue_id" = "issue_id_p"
4124 AND "losing_initiative"."admitted"
4125 AND "winning_initiative"."schulze_rank" <
4126 "losing_initiative"."schulze_rank"
4127 AND "battle_win"."count" > "battle_lose"."count"
4128 AND (
4129 "battle_win"."count" > "winning_initiative"."positive_votes" OR
4130 "battle_lose"."count" < "losing_initiative"."negative_votes" )
4131 ) AS "subquery"
4132 WHERE "id" = "subquery"."initiative_id";
4133 -- mark eligible initiatives:
4134 UPDATE "initiative" SET "eligible" = TRUE
4135 WHERE "issue_id" = "issue_id_p"
4136 AND "initiative"."direct_majority"
4137 AND "initiative"."indirect_majority"
4138 AND "initiative"."better_than_status_quo"
4139 AND (
4140 "policy_row"."no_multistage_majority" = FALSE OR
4141 "initiative"."multistage_majority" = FALSE )
4142 AND (
4143 "policy_row"."no_reverse_beat_path" = FALSE OR
4144 "initiative"."reverse_beat_path" = FALSE );
4145 -- mark final winner:
4146 UPDATE "initiative" SET "winner" = TRUE
4147 FROM (
4148 SELECT "id" AS "initiative_id"
4149 FROM "initiative"
4150 WHERE "issue_id" = "issue_id_p" AND "eligible"
4151 ORDER BY
4152 "schulze_rank",
4153 "id"
4154 LIMIT 1
4155 ) AS "subquery"
4156 WHERE "id" = "subquery"."initiative_id";
4157 -- write (final) ranks:
4158 "rank_v" := 1;
4159 FOR "initiative_id_v" IN
4160 SELECT "id"
4161 FROM "initiative"
4162 WHERE "issue_id" = "issue_id_p" AND "admitted"
4163 ORDER BY
4164 "winner" DESC,
4165 "eligible" DESC,
4166 "schulze_rank",
4167 "id"
4168 LOOP
4169 UPDATE "initiative" SET "rank" = "rank_v"
4170 WHERE "id" = "initiative_id_v";
4171 "rank_v" := "rank_v" + 1;
4172 END LOOP;
4173 -- set schulze rank of status quo and mark issue as finished:
4174 UPDATE "issue" SET
4175 "status_quo_schulze_rank" = "rank_ary"[1],
4176 "state" =
4177 CASE WHEN EXISTS (
4178 SELECT NULL FROM "initiative"
4179 WHERE "issue_id" = "issue_id_p" AND "winner"
4180 ) THEN
4181 'finished_with_winner'::"issue_state"
4182 ELSE
4183 'finished_without_winner'::"issue_state"
4184 END,
4185 "closed" = "phase_finished",
4186 "phase_finished" = NULL
4187 WHERE "id" = "issue_id_p";
4188 RETURN;
4189 END;
4190 $$;
4192 COMMENT ON FUNCTION "calculate_ranks"
4193 ( "issue"."id"%TYPE )
4194 IS 'Determine ranking (Votes have to be counted first)';
4198 -----------------------------
4199 -- Automatic state changes --
4200 -----------------------------
4203 CREATE TYPE "check_issue_persistence" AS (
4204 "state" "issue_state",
4205 "phase_finished" BOOLEAN,
4206 "issue_revoked" BOOLEAN,
4207 "snapshot_created" BOOLEAN,
4208 "harmonic_weights_set" BOOLEAN,
4209 "closed_voting" BOOLEAN );
4211 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';
4214 CREATE FUNCTION "check_issue"
4215 ( "issue_id_p" "issue"."id"%TYPE,
4216 "persist" "check_issue_persistence" )
4217 RETURNS "check_issue_persistence"
4218 LANGUAGE 'plpgsql' VOLATILE AS $$
4219 DECLARE
4220 "issue_row" "issue"%ROWTYPE;
4221 "policy_row" "policy"%ROWTYPE;
4222 "initiative_row" "initiative"%ROWTYPE;
4223 "state_v" "issue_state";
4224 BEGIN
4225 PERFORM "require_transaction_isolation"();
4226 IF "persist" ISNULL THEN
4227 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4228 FOR UPDATE;
4229 IF "issue_row"."closed" NOTNULL THEN
4230 RETURN NULL;
4231 END IF;
4232 "persist"."state" := "issue_row"."state";
4233 IF
4234 ( "issue_row"."state" = 'admission' AND now() >=
4235 "issue_row"."created" + "issue_row"."admission_time" ) OR
4236 ( "issue_row"."state" = 'discussion' AND now() >=
4237 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
4238 ( "issue_row"."state" = 'verification' AND now() >=
4239 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
4240 ( "issue_row"."state" = 'voting' AND now() >=
4241 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
4242 THEN
4243 "persist"."phase_finished" := TRUE;
4244 ELSE
4245 "persist"."phase_finished" := FALSE;
4246 END IF;
4247 IF
4248 NOT EXISTS (
4249 -- all initiatives are revoked
4250 SELECT NULL FROM "initiative"
4251 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4252 ) AND (
4253 -- and issue has not been accepted yet
4254 "persist"."state" = 'admission' OR
4255 -- or verification time has elapsed
4256 ( "persist"."state" = 'verification' AND
4257 "persist"."phase_finished" ) OR
4258 -- or no initiatives have been revoked lately
4259 NOT EXISTS (
4260 SELECT NULL FROM "initiative"
4261 WHERE "issue_id" = "issue_id_p"
4262 AND now() < "revoked" + "issue_row"."verification_time"
4263 )
4264 )
4265 THEN
4266 "persist"."issue_revoked" := TRUE;
4267 ELSE
4268 "persist"."issue_revoked" := FALSE;
4269 END IF;
4270 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
4271 UPDATE "issue" SET "phase_finished" = now()
4272 WHERE "id" = "issue_row"."id";
4273 RETURN "persist";
4274 ELSIF
4275 "persist"."state" IN ('admission', 'discussion', 'verification')
4276 THEN
4277 RETURN "persist";
4278 ELSE
4279 RETURN NULL;
4280 END IF;
4281 END IF;
4282 IF
4283 "persist"."state" IN ('admission', 'discussion', 'verification') AND
4284 coalesce("persist"."snapshot_created", FALSE) = FALSE
4285 THEN
4286 PERFORM "create_snapshot"("issue_id_p");
4287 "persist"."snapshot_created" = TRUE;
4288 IF "persist"."phase_finished" THEN
4289 IF "persist"."state" = 'admission' THEN
4290 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
4291 ELSIF "persist"."state" = 'discussion' THEN
4292 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
4293 ELSIF "persist"."state" = 'verification' THEN
4294 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
4295 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4296 SELECT * INTO "policy_row" FROM "policy"
4297 WHERE "id" = "issue_row"."policy_id";
4298 FOR "initiative_row" IN
4299 SELECT * FROM "initiative"
4300 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4301 FOR UPDATE
4302 LOOP
4303 IF
4304 "initiative_row"."polling" OR (
4305 "initiative_row"."satisfied_supporter_count" > 0 AND
4306 "initiative_row"."satisfied_supporter_count" *
4307 "policy_row"."initiative_quorum_den" >=
4308 "issue_row"."population" * "policy_row"."initiative_quorum_num"
4309 )
4310 THEN
4311 UPDATE "initiative" SET "admitted" = TRUE
4312 WHERE "id" = "initiative_row"."id";
4313 ELSE
4314 UPDATE "initiative" SET "admitted" = FALSE
4315 WHERE "id" = "initiative_row"."id";
4316 END IF;
4317 END LOOP;
4318 END IF;
4319 END IF;
4320 RETURN "persist";
4321 END IF;
4322 IF
4323 "persist"."state" IN ('admission', 'discussion', 'verification') AND
4324 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
4325 THEN
4326 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
4327 "persist"."harmonic_weights_set" = TRUE;
4328 IF
4329 "persist"."phase_finished" OR
4330 "persist"."issue_revoked" OR
4331 "persist"."state" = 'admission'
4332 THEN
4333 RETURN "persist";
4334 ELSE
4335 RETURN NULL;
4336 END IF;
4337 END IF;
4338 IF "persist"."issue_revoked" THEN
4339 IF "persist"."state" = 'admission' THEN
4340 "state_v" := 'canceled_revoked_before_accepted';
4341 ELSIF "persist"."state" = 'discussion' THEN
4342 "state_v" := 'canceled_after_revocation_during_discussion';
4343 ELSIF "persist"."state" = 'verification' THEN
4344 "state_v" := 'canceled_after_revocation_during_verification';
4345 END IF;
4346 UPDATE "issue" SET
4347 "state" = "state_v",
4348 "closed" = "phase_finished",
4349 "phase_finished" = NULL
4350 WHERE "id" = "issue_id_p";
4351 RETURN NULL;
4352 END IF;
4353 IF "persist"."state" = 'admission' THEN
4354 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4355 FOR UPDATE;
4356 SELECT * INTO "policy_row"
4357 FROM "policy" WHERE "id" = "issue_row"."policy_id";
4358 IF EXISTS (
4359 SELECT NULL FROM "initiative"
4360 WHERE "issue_id" = "issue_id_p"
4361 AND "supporter_count" > 0
4362 AND "supporter_count" * "policy_row"."issue_quorum_den"
4363 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
4364 ) THEN
4365 UPDATE "issue" SET
4366 "state" = 'discussion',
4367 "accepted" = coalesce("phase_finished", now()),
4368 "phase_finished" = NULL
4369 WHERE "id" = "issue_id_p";
4370 ELSIF "issue_row"."phase_finished" NOTNULL THEN
4371 UPDATE "issue" SET
4372 "state" = 'canceled_issue_not_accepted',
4373 "closed" = "phase_finished",
4374 "phase_finished" = NULL
4375 WHERE "id" = "issue_id_p";
4376 END IF;
4377 RETURN NULL;
4378 END IF;
4379 IF "persist"."phase_finished" THEN
4380 if "persist"."state" = 'discussion' THEN
4381 UPDATE "issue" SET
4382 "state" = 'verification',
4383 "half_frozen" = "phase_finished",
4384 "phase_finished" = NULL
4385 WHERE "id" = "issue_id_p";
4386 RETURN NULL;
4387 END IF;
4388 IF "persist"."state" = 'verification' THEN
4389 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4390 FOR UPDATE;
4391 SELECT * INTO "policy_row" FROM "policy"
4392 WHERE "id" = "issue_row"."policy_id";
4393 IF EXISTS (
4394 SELECT NULL FROM "initiative"
4395 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
4396 ) THEN
4397 UPDATE "issue" SET
4398 "state" = 'voting',
4399 "fully_frozen" = "phase_finished",
4400 "phase_finished" = NULL
4401 WHERE "id" = "issue_id_p";
4402 ELSE
4403 UPDATE "issue" SET
4404 "state" = 'canceled_no_initiative_admitted',
4405 "fully_frozen" = "phase_finished",
4406 "closed" = "phase_finished",
4407 "phase_finished" = NULL
4408 WHERE "id" = "issue_id_p";
4409 -- NOTE: The following DELETE statements have effect only when
4410 -- issue state has been manipulated
4411 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
4412 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
4413 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
4414 END IF;
4415 RETURN NULL;
4416 END IF;
4417 IF "persist"."state" = 'voting' THEN
4418 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
4419 PERFORM "close_voting"("issue_id_p");
4420 "persist"."closed_voting" = TRUE;
4421 RETURN "persist";
4422 END IF;
4423 PERFORM "calculate_ranks"("issue_id_p");
4424 RETURN NULL;
4425 END IF;
4426 END IF;
4427 RAISE WARNING 'should not happen';
4428 RETURN NULL;
4429 END;
4430 $$;
4432 COMMENT ON FUNCTION "check_issue"
4433 ( "issue"."id"%TYPE,
4434 "check_issue_persistence" )
4435 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")';
4438 CREATE FUNCTION "check_everything"()
4439 RETURNS VOID
4440 LANGUAGE 'plpgsql' VOLATILE AS $$
4441 DECLARE
4442 "issue_id_v" "issue"."id"%TYPE;
4443 "persist_v" "check_issue_persistence";
4444 BEGIN
4445 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
4446 DELETE FROM "expired_session";
4447 PERFORM "check_activity"();
4448 PERFORM "calculate_member_counts"();
4449 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
4450 "persist_v" := NULL;
4451 LOOP
4452 "persist_v" := "check_issue"("issue_id_v", "persist_v");
4453 EXIT WHEN "persist_v" ISNULL;
4454 END LOOP;
4455 END LOOP;
4456 RETURN;
4457 END;
4458 $$;
4460 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.';
4464 ----------------------
4465 -- Deletion of data --
4466 ----------------------
4469 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
4470 RETURNS VOID
4471 LANGUAGE 'plpgsql' VOLATILE AS $$
4472 BEGIN
4473 IF EXISTS (
4474 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
4475 ) THEN
4476 -- override protection triggers:
4477 INSERT INTO "temporary_transaction_data" ("key", "value")
4478 VALUES ('override_protection_triggers', TRUE::TEXT);
4479 -- clean data:
4480 DELETE FROM "delegating_voter"
4481 WHERE "issue_id" = "issue_id_p";
4482 DELETE FROM "direct_voter"
4483 WHERE "issue_id" = "issue_id_p";
4484 DELETE FROM "delegating_interest_snapshot"
4485 WHERE "issue_id" = "issue_id_p";
4486 DELETE FROM "direct_interest_snapshot"
4487 WHERE "issue_id" = "issue_id_p";
4488 DELETE FROM "delegating_population_snapshot"
4489 WHERE "issue_id" = "issue_id_p";
4490 DELETE FROM "direct_population_snapshot"
4491 WHERE "issue_id" = "issue_id_p";
4492 DELETE FROM "non_voter"
4493 WHERE "issue_id" = "issue_id_p";
4494 DELETE FROM "delegation"
4495 WHERE "issue_id" = "issue_id_p";
4496 DELETE FROM "supporter"
4497 USING "initiative" -- NOTE: due to missing index on issue_id
4498 WHERE "initiative"."issue_id" = "issue_id_p"
4499 AND "supporter"."initiative_id" = "initiative_id";
4500 -- mark issue as cleaned:
4501 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
4502 -- finish overriding protection triggers (avoids garbage):
4503 DELETE FROM "temporary_transaction_data"
4504 WHERE "key" = 'override_protection_triggers';
4505 END IF;
4506 RETURN;
4507 END;
4508 $$;
4510 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
4513 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
4514 RETURNS VOID
4515 LANGUAGE 'plpgsql' VOLATILE AS $$
4516 BEGIN
4517 UPDATE "member" SET
4518 "last_login" = NULL,
4519 "last_delegation_check" = NULL,
4520 "login" = NULL,
4521 "password" = NULL,
4522 "locked" = TRUE,
4523 "active" = FALSE,
4524 "notify_email" = NULL,
4525 "notify_email_unconfirmed" = NULL,
4526 "notify_email_secret" = NULL,
4527 "notify_email_secret_expiry" = NULL,
4528 "notify_email_lock_expiry" = NULL,
4529 "login_recovery_expiry" = NULL,
4530 "password_reset_secret" = NULL,
4531 "password_reset_secret_expiry" = NULL,
4532 "organizational_unit" = NULL,
4533 "internal_posts" = NULL,
4534 "realname" = NULL,
4535 "birthday" = NULL,
4536 "address" = NULL,
4537 "email" = NULL,
4538 "xmpp_address" = NULL,
4539 "website" = NULL,
4540 "phone" = NULL,
4541 "mobile_phone" = NULL,
4542 "profession" = NULL,
4543 "external_memberships" = NULL,
4544 "external_posts" = NULL,
4545 "statement" = NULL
4546 WHERE "id" = "member_id_p";
4547 -- "text_search_data" is updated by triggers
4548 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
4549 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
4550 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
4551 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
4552 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
4553 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
4554 DELETE FROM "session" WHERE "member_id" = "member_id_p";
4555 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
4556 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
4557 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
4558 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
4559 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
4560 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
4561 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
4562 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
4563 DELETE FROM "direct_voter" USING "issue"
4564 WHERE "direct_voter"."issue_id" = "issue"."id"
4565 AND "issue"."closed" ISNULL
4566 AND "member_id" = "member_id_p";
4567 RETURN;
4568 END;
4569 $$;
4571 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)';
4574 CREATE FUNCTION "delete_private_data"()
4575 RETURNS VOID
4576 LANGUAGE 'plpgsql' VOLATILE AS $$
4577 BEGIN
4578 DELETE FROM "temporary_transaction_data";
4579 DELETE FROM "member" WHERE "activated" ISNULL;
4580 UPDATE "member" SET
4581 "invite_code" = NULL,
4582 "invite_code_expiry" = NULL,
4583 "admin_comment" = NULL,
4584 "last_login" = NULL,
4585 "last_delegation_check" = NULL,
4586 "login" = NULL,
4587 "password" = NULL,
4588 "lang" = NULL,
4589 "notify_email" = NULL,
4590 "notify_email_unconfirmed" = NULL,
4591 "notify_email_secret" = NULL,
4592 "notify_email_secret_expiry" = NULL,
4593 "notify_email_lock_expiry" = NULL,
4594 "notify_level" = NULL,
4595 "login_recovery_expiry" = NULL,
4596 "password_reset_secret" = NULL,
4597 "password_reset_secret_expiry" = NULL,
4598 "organizational_unit" = NULL,
4599 "internal_posts" = NULL,
4600 "realname" = NULL,
4601 "birthday" = NULL,
4602 "address" = NULL,
4603 "email" = NULL,
4604 "xmpp_address" = NULL,
4605 "website" = NULL,
4606 "phone" = NULL,
4607 "mobile_phone" = NULL,
4608 "profession" = NULL,
4609 "external_memberships" = NULL,
4610 "external_posts" = NULL,
4611 "formatting_engine" = NULL,
4612 "statement" = NULL;
4613 -- "text_search_data" is updated by triggers
4614 DELETE FROM "setting";
4615 DELETE FROM "setting_map";
4616 DELETE FROM "member_relation_setting";
4617 DELETE FROM "member_image";
4618 DELETE FROM "contact";
4619 DELETE FROM "ignored_member";
4620 DELETE FROM "session";
4621 DELETE FROM "area_setting";
4622 DELETE FROM "issue_setting";
4623 DELETE FROM "ignored_initiative";
4624 DELETE FROM "initiative_setting";
4625 DELETE FROM "suggestion_setting";
4626 DELETE FROM "non_voter";
4627 DELETE FROM "direct_voter" USING "issue"
4628 WHERE "direct_voter"."issue_id" = "issue"."id"
4629 AND "issue"."closed" ISNULL;
4630 RETURN;
4631 END;
4632 $$;
4634 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.';
4638 COMMIT;