liquid_feedback_core
view core.sql @ 498:10b90162e982
Corrected comparison operator in view "newsletter_to_send"
| author | jbe |
|---|---|
| date | Sun Apr 03 20:57:44 2016 +0200 (2016-04-03) |
| parents | 91e3d31c1de2 |
| children | bc4b590a8eec |
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.2.0', 3, 2, 0))
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 "authority" TEXT,
111 "authority_uid" TEXT,
112 "authority_login" TEXT,
113 "locked" BOOLEAN NOT NULL DEFAULT FALSE,
114 "active" BOOLEAN NOT NULL DEFAULT FALSE,
115 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
116 "lang" TEXT,
117 "notify_email" TEXT,
118 "notify_email_unconfirmed" TEXT,
119 "notify_email_secret" TEXT UNIQUE,
120 "notify_email_secret_expiry" TIMESTAMPTZ,
121 "notify_email_lock_expiry" TIMESTAMPTZ,
122 "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE,
123 "notification_counter" INT4 NOT NULL DEFAULT 1,
124 "notification_sample_size" INT4 NOT NULL DEFAULT 3,
125 "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6),
126 "notification_hour" INT4 CHECK ("notification_hour" BETWEEN 0 AND 23),
127 "login_recovery_expiry" TIMESTAMPTZ,
128 "password_reset_secret" TEXT UNIQUE,
129 "password_reset_secret_expiry" TIMESTAMPTZ,
130 "name" TEXT UNIQUE,
131 "identification" TEXT UNIQUE,
132 "authentication" TEXT,
133 "organizational_unit" TEXT,
134 "internal_posts" TEXT,
135 "realname" TEXT,
136 "birthday" DATE,
137 "address" TEXT,
138 "email" TEXT,
139 "xmpp_address" TEXT,
140 "website" TEXT,
141 "phone" TEXT,
142 "mobile_phone" TEXT,
143 "profession" TEXT,
144 "external_memberships" TEXT,
145 "external_posts" TEXT,
146 "formatting_engine" TEXT,
147 "statement" TEXT,
148 "text_search_data" TSVECTOR,
149 CONSTRAINT "active_requires_activated_and_last_activity"
150 CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)),
151 CONSTRAINT "authority_requires_uid_and_vice_versa"
152 CHECK (("authority" NOTNULL) = ("authority_uid" NOTNULL)),
153 CONSTRAINT "authority_uid_unique_per_authority"
154 UNIQUE ("authority", "authority_uid"),
155 CONSTRAINT "authority_login_requires_authority"
156 CHECK ("authority" NOTNULL OR "authority_login" ISNULL),
157 CONSTRAINT "name_not_null_if_activated"
158 CHECK ("activated" ISNULL OR "name" NOTNULL) );
159 CREATE INDEX "member_authority_login_idx" ON "member" ("authority_login");
160 CREATE INDEX "member_active_idx" ON "member" ("active");
161 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
162 CREATE TRIGGER "update_text_search_data"
163 BEFORE INSERT OR UPDATE ON "member"
164 FOR EACH ROW EXECUTE PROCEDURE
165 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
166 "name", "identification", "organizational_unit", "internal_posts",
167 "realname", "external_memberships", "external_posts", "statement" );
169 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
171 COMMENT ON COLUMN "member"."created" IS 'Creation of member record and/or invite code';
172 COMMENT ON COLUMN "member"."invite_code" IS 'Optional invite code, to allow a member to initialize his/her account the first time';
173 COMMENT ON COLUMN "member"."invite_code_expiry" IS 'Expiry data/time for "invite_code"';
174 COMMENT ON COLUMN "member"."admin_comment" IS 'Hidden comment for administrative purposes';
175 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';
176 COMMENT ON COLUMN "member"."last_activity" IS 'Date of last activity of member; required to be set for "active" members';
177 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';
178 COMMENT ON COLUMN "member"."last_delegation_check" IS 'Timestamp of last delegation check (i.e. confirmation of all unit and area delegations)';
179 COMMENT ON COLUMN "member"."login" IS 'Login name';
180 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
181 COMMENT ON COLUMN "member"."authority" IS 'NULL if LiquidFeedback Core is authoritative for the member account; otherwise a string that indicates the source/authority of the external account (e.g. ''LDAP'' for an LDAP account)';
182 COMMENT ON COLUMN "member"."authority_uid" IS 'Unique identifier (unique per "authority") that allows to identify an external account (e.g. even if the login name changes)';
183 COMMENT ON COLUMN "member"."authority_login" IS 'Login name for external accounts (field is not unique!)';
184 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
185 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".';
186 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
187 COMMENT ON COLUMN "member"."lang" IS 'Language code of the preferred language of the member';
188 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
189 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
190 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
191 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
192 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
193 COMMENT ON COLUMN "member"."disable_notifications" IS 'TRUE if member does not want to receive notifications';
194 COMMENT ON COLUMN "member"."login_recovery_expiry" IS 'Date/time after which another login recovery attempt is allowed';
195 COMMENT ON COLUMN "member"."password_reset_secret" IS 'Secret string sent via e-mail for password recovery';
196 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';
197 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member, may be NULL if account has not been activated yet';
198 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
199 COMMENT ON COLUMN "member"."authentication" IS 'Information about how this member was authenticated';
200 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
201 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
202 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
203 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
204 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
205 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
206 COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"';
207 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his/her profile';
210 CREATE TABLE "member_history" (
211 "id" SERIAL8 PRIMARY KEY,
212 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
213 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
214 "active" BOOLEAN NOT NULL,
215 "name" TEXT NOT NULL );
216 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
218 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
220 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
221 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
224 CREATE TABLE "rendered_member_statement" (
225 PRIMARY KEY ("member_id", "format"),
226 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
227 "format" TEXT,
228 "content" TEXT NOT NULL );
230 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)';
233 CREATE TABLE "setting" (
234 PRIMARY KEY ("member_id", "key"),
235 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
236 "key" TEXT NOT NULL,
237 "value" TEXT NOT NULL );
238 CREATE INDEX "setting_key_idx" ON "setting" ("key");
240 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
242 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
245 CREATE TABLE "setting_map" (
246 PRIMARY KEY ("member_id", "key", "subkey"),
247 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
248 "key" TEXT NOT NULL,
249 "subkey" TEXT NOT NULL,
250 "value" TEXT NOT NULL );
251 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
253 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
255 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
256 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
257 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
260 CREATE TABLE "member_relation_setting" (
261 PRIMARY KEY ("member_id", "key", "other_member_id"),
262 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
263 "key" TEXT NOT NULL,
264 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
265 "value" TEXT NOT NULL );
267 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
270 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
272 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
275 CREATE TABLE "member_image" (
276 PRIMARY KEY ("member_id", "image_type", "scaled"),
277 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
278 "image_type" "member_image_type",
279 "scaled" BOOLEAN,
280 "content_type" TEXT,
281 "data" BYTEA NOT NULL );
283 COMMENT ON TABLE "member_image" IS 'Images of members';
285 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
288 CREATE TABLE "member_count" (
289 "calculated" TIMESTAMPTZ NOT NULL DEFAULT now(),
290 "total_count" INT4 NOT NULL );
292 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';
294 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
295 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
298 CREATE TABLE "contact" (
299 PRIMARY KEY ("member_id", "other_member_id"),
300 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
301 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
302 "public" BOOLEAN NOT NULL DEFAULT FALSE,
303 CONSTRAINT "cant_save_yourself_as_contact"
304 CHECK ("member_id" != "other_member_id") );
305 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
307 COMMENT ON TABLE "contact" IS 'Contact lists';
309 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
310 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
311 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
314 CREATE TABLE "ignored_member" (
315 PRIMARY KEY ("member_id", "other_member_id"),
316 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
317 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
318 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
320 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
322 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';
323 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
326 CREATE TABLE "session" (
327 "ident" TEXT PRIMARY KEY,
328 "additional_secret" TEXT,
329 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
330 "member_id" INT4 REFERENCES "member" ("id") ON DELETE SET NULL,
331 "authority" TEXT,
332 "authority_uid" TEXT,
333 "authority_login" TEXT,
334 "needs_delegation_check" BOOLEAN NOT NULL DEFAULT FALSE,
335 "lang" TEXT );
336 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
338 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
340 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
341 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
342 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
343 COMMENT ON COLUMN "session"."authority" IS 'Temporary store for "member"."authority" during member account creation';
344 COMMENT ON COLUMN "session"."authority_uid" IS 'Temporary store for "member"."authority_uid" during member account creation';
345 COMMENT ON COLUMN "session"."authority_login" IS 'Temporary store for "member"."authority_login" during member account creation';
346 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';
347 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
350 CREATE TYPE "defeat_strength" AS ENUM ('simple', 'tuple');
352 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';
355 CREATE TYPE "tie_breaking" AS ENUM ('simple', 'variant1', 'variant2');
357 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';
360 CREATE TABLE "policy" (
361 "id" SERIAL4 PRIMARY KEY,
362 "index" INT4 NOT NULL,
363 "active" BOOLEAN NOT NULL DEFAULT TRUE,
364 "name" TEXT NOT NULL UNIQUE,
365 "description" TEXT NOT NULL DEFAULT '',
366 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
367 "min_admission_time" INTERVAL,
368 "max_admission_time" INTERVAL,
369 "discussion_time" INTERVAL,
370 "verification_time" INTERVAL,
371 "voting_time" INTERVAL,
372 "issue_quorum_num" INT4,
373 "issue_quorum_den" INT4,
374 "initiative_quorum_num" INT4 NOT NULL,
375 "initiative_quorum_den" INT4 NOT NULL,
376 "defeat_strength" "defeat_strength" NOT NULL DEFAULT 'tuple',
377 "tie_breaking" "tie_breaking" NOT NULL DEFAULT 'variant1',
378 "direct_majority_num" INT4 NOT NULL DEFAULT 1,
379 "direct_majority_den" INT4 NOT NULL DEFAULT 2,
380 "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
381 "direct_majority_positive" INT4 NOT NULL DEFAULT 0,
382 "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0,
383 "indirect_majority_num" INT4 NOT NULL DEFAULT 1,
384 "indirect_majority_den" INT4 NOT NULL DEFAULT 2,
385 "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
386 "indirect_majority_positive" INT4 NOT NULL DEFAULT 0,
387 "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0,
388 "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT FALSE,
389 "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE,
390 CONSTRAINT "timing" CHECK (
391 ( "polling" = FALSE AND
392 "min_admission_time" NOTNULL AND "max_admission_time" NOTNULL AND
393 "min_admission_time" <= "max_admission_time" AND
394 "discussion_time" NOTNULL AND
395 "verification_time" NOTNULL AND
396 "voting_time" NOTNULL ) OR
397 ( "polling" = TRUE AND
398 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
399 "discussion_time" NOTNULL AND
400 "verification_time" NOTNULL AND
401 "voting_time" NOTNULL ) OR
402 ( "polling" = TRUE AND
403 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
404 "discussion_time" ISNULL AND
405 "verification_time" ISNULL AND
406 "voting_time" ISNULL ) ),
407 CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK (
408 "polling" = ("issue_quorum_num" ISNULL) AND
409 "polling" = ("issue_quorum_den" ISNULL) ),
410 CONSTRAINT "no_reverse_beat_path_requires_tuple_defeat_strength" CHECK (
411 "defeat_strength" = 'tuple'::"defeat_strength" OR
412 "no_reverse_beat_path" = FALSE ) );
413 CREATE INDEX "policy_active_idx" ON "policy" ("active");
415 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
417 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
418 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
419 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; "min_admission_time" and "max_admission_time" MUST be set to NULL, the other timings may be set to NULL altogether, allowing individual timing for those issues';
420 COMMENT ON COLUMN "policy"."min_admission_time" IS 'Minimum duration of issue state ''admission''; Minimum time an issue stays open';
421 COMMENT ON COLUMN "policy"."max_admission_time" IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';
422 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"';
423 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"';
424 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'')';
425 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''';
426 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''';
427 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
428 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
429 COMMENT ON COLUMN "policy"."defeat_strength" IS 'How pairwise defeats are measured for the Schulze method; see type "defeat_strength"; ''tuple'' is the recommended setting';
430 COMMENT ON COLUMN "policy"."tie_breaking" IS 'Tie-breaker for the Schulze method; see type "tie_breaking"; ''variant1'' or ''variant2'' are recommended';
431 COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner';
432 COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner';
433 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.';
434 COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner';
435 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';
436 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';
437 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';
438 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.';
439 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';
440 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';
441 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.';
442 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").';
445 CREATE TABLE "unit" (
446 "id" SERIAL4 PRIMARY KEY,
447 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
448 "active" BOOLEAN NOT NULL DEFAULT TRUE,
449 "name" TEXT NOT NULL,
450 "description" TEXT NOT NULL DEFAULT '',
451 "external_reference" TEXT,
452 "member_count" INT4,
453 "text_search_data" TSVECTOR );
454 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
455 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
456 CREATE INDEX "unit_active_idx" ON "unit" ("active");
457 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
458 CREATE TRIGGER "update_text_search_data"
459 BEFORE INSERT OR UPDATE ON "unit"
460 FOR EACH ROW EXECUTE PROCEDURE
461 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
462 "name", "description" );
464 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
466 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
467 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this unit';
468 COMMENT ON COLUMN "unit"."external_reference" IS 'Opaque data field to store an external reference';
469 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
472 CREATE TABLE "subscription" (
473 PRIMARY KEY ("member_id", "unit_id"),
474 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
475 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
476 CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id");
478 COMMENT ON TABLE "subscription" IS 'An entry in this table denotes that the member wishes to receive notifications regardless of his/her privileges in the given unit';
481 CREATE TABLE "unit_setting" (
482 PRIMARY KEY ("member_id", "key", "unit_id"),
483 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
484 "key" TEXT NOT NULL,
485 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
486 "value" TEXT NOT NULL );
488 COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings';
491 CREATE TABLE "area" (
492 "id" SERIAL4 PRIMARY KEY,
493 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
494 "active" BOOLEAN NOT NULL DEFAULT TRUE,
495 "name" TEXT NOT NULL,
496 "description" TEXT NOT NULL DEFAULT '',
497 "external_reference" TEXT,
498 "direct_member_count" INT4,
499 "member_weight" INT4,
500 "text_search_data" TSVECTOR );
501 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
502 CREATE INDEX "area_active_idx" ON "area" ("active");
503 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
504 CREATE TRIGGER "update_text_search_data"
505 BEFORE INSERT OR UPDATE ON "area"
506 FOR EACH ROW EXECUTE PROCEDURE
507 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
508 "name", "description" );
510 COMMENT ON TABLE "area" IS 'Subject areas';
512 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
513 COMMENT ON COLUMN "area"."external_reference" IS 'Opaque data field to store an external reference';
514 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"';
515 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
518 CREATE TABLE "ignored_area" (
519 PRIMARY KEY ("member_id", "area_id"),
520 "member_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
521 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
522 CREATE INDEX "ignored_area_area_id_idx" ON "ignored_area" ("area_id");
524 COMMENT ON TABLE "ignored_area" IS 'An entry in this table denotes that the member does not wish to receive notifications for the given subject area unless he/she declared interested in a particular issue';
527 CREATE TABLE "area_setting" (
528 PRIMARY KEY ("member_id", "key", "area_id"),
529 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
530 "key" TEXT NOT NULL,
531 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
532 "value" TEXT NOT NULL );
534 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
537 CREATE TABLE "allowed_policy" (
538 PRIMARY KEY ("area_id", "policy_id"),
539 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
540 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
541 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
542 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
544 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
546 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
549 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
551 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';
554 CREATE TYPE "issue_state" AS ENUM (
555 'admission', 'discussion', 'verification', 'voting',
556 'canceled_by_admin',
557 'canceled_revoked_before_accepted',
558 'canceled_issue_not_accepted',
559 'canceled_after_revocation_during_discussion',
560 'canceled_after_revocation_during_verification',
561 'canceled_no_initiative_admitted',
562 'finished_without_winner', 'finished_with_winner');
564 COMMENT ON TYPE "issue_state" IS 'State of issues';
567 CREATE TABLE "issue" (
568 "id" SERIAL4 PRIMARY KEY,
569 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
570 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
571 "admin_notice" TEXT,
572 "external_reference" TEXT,
573 "state" "issue_state" NOT NULL DEFAULT 'admission',
574 "phase_finished" TIMESTAMPTZ,
575 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
576 "accepted" TIMESTAMPTZ,
577 "half_frozen" TIMESTAMPTZ,
578 "fully_frozen" TIMESTAMPTZ,
579 "closed" TIMESTAMPTZ,
580 "cleaned" TIMESTAMPTZ,
581 "min_admission_time" INTERVAL,
582 "max_admission_time" INTERVAL,
583 "discussion_time" INTERVAL NOT NULL,
584 "verification_time" INTERVAL NOT NULL,
585 "voting_time" INTERVAL NOT NULL,
586 "snapshot" TIMESTAMPTZ,
587 "latest_snapshot_event" "snapshot_event",
588 "population" INT4,
589 "voter_count" INT4,
590 "status_quo_schulze_rank" INT4,
591 CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (
592 ("min_admission_time" NOTNULL) = ("max_admission_time" NOTNULL) AND
593 ("min_admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created")) ),
594 CONSTRAINT "valid_state" CHECK (
595 (
596 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
597 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
598 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL ) OR
599 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL)
600 ) AND (
601 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
602 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
603 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
604 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
605 ("state" = 'canceled_by_admin' AND "closed" NOTNULL) OR
606 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
607 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
608 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
609 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
610 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" = "fully_frozen") OR
611 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen") OR
612 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen")
613 )),
614 CONSTRAINT "phase_finished_only_when_not_closed" CHECK (
615 "phase_finished" ISNULL OR "closed" ISNULL ),
616 CONSTRAINT "state_change_order" CHECK (
617 "created" <= "accepted" AND
618 "accepted" <= "half_frozen" AND
619 "half_frozen" <= "fully_frozen" AND
620 "fully_frozen" <= "closed" ),
621 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
622 "cleaned" ISNULL OR "closed" NOTNULL ),
623 CONSTRAINT "last_snapshot_on_full_freeze"
624 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
625 CONSTRAINT "freeze_requires_snapshot"
626 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
627 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
628 CHECK (("snapshot" NOTNULL) = ("latest_snapshot_event" NOTNULL)) );
629 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
630 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
631 CREATE INDEX "issue_created_idx" ON "issue" ("created");
632 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
633 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
634 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
635 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
636 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
637 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
639 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
641 COMMENT ON COLUMN "issue"."admin_notice" IS 'Public notice by admin to explain manual interventions, or to announce corrections';
642 COMMENT ON COLUMN "issue"."external_reference" IS 'Opaque data field to store an external reference';
643 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';
644 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
645 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.';
646 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.';
647 COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "max_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.';
648 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
649 COMMENT ON COLUMN "issue"."min_admission_time" IS 'Copied from "policy" table at creation of issue';
650 COMMENT ON COLUMN "issue"."max_admission_time" IS 'Copied from "policy" table at creation of issue';
651 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
652 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
653 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
654 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
655 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';
656 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
657 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';
658 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
661 CREATE TABLE "issue_order_in_admission_state" (
662 "id" INT8 PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
663 "order_in_area" INT4,
664 "order_in_unit" INT4 );
666 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"';
668 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';
669 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';
670 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';
673 CREATE TABLE "issue_setting" (
674 PRIMARY KEY ("member_id", "key", "issue_id"),
675 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
676 "key" TEXT NOT NULL,
677 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
678 "value" TEXT NOT NULL );
680 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
683 CREATE TABLE "initiative" (
684 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
685 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
686 "id" SERIAL4 PRIMARY KEY,
687 "name" TEXT NOT NULL,
688 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
689 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
690 "revoked" TIMESTAMPTZ,
691 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
692 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
693 "external_reference" TEXT,
694 "admitted" BOOLEAN,
695 "supporter_count" INT4,
696 "informed_supporter_count" INT4,
697 "satisfied_supporter_count" INT4,
698 "satisfied_informed_supporter_count" INT4,
699 "harmonic_weight" NUMERIC(12, 3),
700 "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE,
701 "first_preference_votes" INT4,
702 "positive_votes" INT4,
703 "negative_votes" INT4,
704 "direct_majority" BOOLEAN,
705 "indirect_majority" BOOLEAN,
706 "schulze_rank" INT4,
707 "better_than_status_quo" BOOLEAN,
708 "worse_than_status_quo" BOOLEAN,
709 "reverse_beat_path" BOOLEAN,
710 "multistage_majority" BOOLEAN,
711 "eligible" BOOLEAN,
712 "winner" BOOLEAN,
713 "rank" INT4,
714 "text_search_data" TSVECTOR,
715 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
716 CHECK (("revoked" NOTNULL) = ("revoked_by_member_id" NOTNULL)),
717 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
718 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
719 CONSTRAINT "revoked_initiatives_cant_be_admitted"
720 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
721 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
722 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
723 ( "first_preference_votes" ISNULL AND
724 "positive_votes" ISNULL AND "negative_votes" ISNULL AND
725 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
726 "schulze_rank" ISNULL AND
727 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
728 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
729 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ),
730 CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")),
731 CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (
732 "eligible" = FALSE OR
733 ("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ),
734 CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE),
735 CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1),
736 CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE),
737 CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") );
738 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
739 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
740 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
741 CREATE TRIGGER "update_text_search_data"
742 BEFORE INSERT OR UPDATE ON "initiative"
743 FOR EACH ROW EXECUTE PROCEDURE
744 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "name");
746 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.';
748 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative does not need to pass the initiative quorum (see "policy"."polling")';
749 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
750 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoke the initiative';
751 COMMENT ON COLUMN "initiative"."external_reference" IS 'Opaque data field to store an external reference';
752 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
753 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
754 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
755 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
756 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
757 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';
758 COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time';
759 COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice';
760 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Number of direct and delegating voters who ranked this initiative better than the status quo';
761 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo';
762 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"';
763 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths';
764 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking';
765 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo';
766 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)';
767 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; set to NULL if "policy"."defeat_strength" is set to ''simple''';
768 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';
769 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"';
770 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank"';
771 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';
774 CREATE TABLE "battle" (
775 "issue_id" INT4 NOT NULL,
776 "winning_initiative_id" INT4,
777 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
778 "losing_initiative_id" INT4,
779 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
780 "count" INT4 NOT NULL,
781 CONSTRAINT "initiative_ids_not_equal" CHECK (
782 "winning_initiative_id" != "losing_initiative_id" OR
783 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
784 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) );
785 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
786 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
787 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
789 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';
792 CREATE TABLE "ignored_initiative" (
793 PRIMARY KEY ("member_id", "initiative_id"),
794 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
795 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
796 CREATE INDEX "ignored_initiative_initiative_id_idx" ON "ignored_initiative" ("initiative_id");
798 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
801 CREATE TABLE "initiative_setting" (
802 PRIMARY KEY ("member_id", "key", "initiative_id"),
803 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
804 "key" TEXT NOT NULL,
805 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
806 "value" TEXT NOT NULL );
808 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
811 CREATE TABLE "draft" (
812 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
813 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
814 "id" SERIAL8 PRIMARY KEY,
815 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
816 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
817 "formatting_engine" TEXT,
818 "content" TEXT NOT NULL,
819 "external_reference" TEXT,
820 "text_search_data" TSVECTOR );
821 CREATE INDEX "draft_created_idx" ON "draft" ("created");
822 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
823 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
824 CREATE TRIGGER "update_text_search_data"
825 BEFORE INSERT OR UPDATE ON "draft"
826 FOR EACH ROW EXECUTE PROCEDURE
827 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
829 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.';
831 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
832 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
833 COMMENT ON COLUMN "draft"."external_reference" IS 'Opaque data field to store an external reference';
836 CREATE TABLE "rendered_draft" (
837 PRIMARY KEY ("draft_id", "format"),
838 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
839 "format" TEXT,
840 "content" TEXT NOT NULL );
842 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)';
845 CREATE TABLE "suggestion" (
846 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
847 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
848 "id" SERIAL8 PRIMARY KEY,
849 "draft_id" INT8 NOT NULL,
850 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
851 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
852 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
853 "name" TEXT NOT NULL,
854 "formatting_engine" TEXT,
855 "content" TEXT NOT NULL DEFAULT '',
856 "external_reference" TEXT,
857 "text_search_data" TSVECTOR,
858 "minus2_unfulfilled_count" INT4,
859 "minus2_fulfilled_count" INT4,
860 "minus1_unfulfilled_count" INT4,
861 "minus1_fulfilled_count" INT4,
862 "plus1_unfulfilled_count" INT4,
863 "plus1_fulfilled_count" INT4,
864 "plus2_unfulfilled_count" INT4,
865 "plus2_fulfilled_count" INT4,
866 "proportional_order" INT4 );
867 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
868 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
869 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
870 CREATE TRIGGER "update_text_search_data"
871 BEFORE INSERT OR UPDATE ON "suggestion"
872 FOR EACH ROW EXECUTE PROCEDURE
873 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
874 "name", "content");
876 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';
878 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")';
879 COMMENT ON COLUMN "suggestion"."external_reference" IS 'Opaque data field to store an external reference';
880 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
881 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
882 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
883 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
884 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
885 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
886 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
887 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
888 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"';
891 CREATE TABLE "rendered_suggestion" (
892 PRIMARY KEY ("suggestion_id", "format"),
893 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
894 "format" TEXT,
895 "content" TEXT NOT NULL );
897 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)';
900 CREATE TABLE "suggestion_setting" (
901 PRIMARY KEY ("member_id", "key", "suggestion_id"),
902 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
903 "key" TEXT NOT NULL,
904 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
905 "value" TEXT NOT NULL );
907 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
910 CREATE TABLE "privilege" (
911 PRIMARY KEY ("unit_id", "member_id"),
912 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
913 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
914 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
915 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
916 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
917 "member_manager" BOOLEAN NOT NULL DEFAULT FALSE,
918 "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE,
919 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE,
920 "polling_right" BOOLEAN NOT NULL DEFAULT FALSE );
922 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
924 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke any privileges to/from other members';
925 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create and disable sub units';
926 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create and disable areas and set area parameters';
927 COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';
928 COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';
929 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote';
930 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';
933 CREATE TABLE "membership" (
934 PRIMARY KEY ("area_id", "member_id"),
935 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
936 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
937 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
939 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
942 CREATE TABLE "interest" (
943 PRIMARY KEY ("issue_id", "member_id"),
944 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
945 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
946 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
948 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.';
951 CREATE TABLE "initiator" (
952 PRIMARY KEY ("initiative_id", "member_id"),
953 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
954 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
955 "accepted" BOOLEAN );
956 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
958 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.';
960 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.';
963 CREATE TABLE "supporter" (
964 "issue_id" INT4 NOT NULL,
965 PRIMARY KEY ("initiative_id", "member_id"),
966 "initiative_id" INT4,
967 "member_id" INT4,
968 "draft_id" INT8 NOT NULL,
969 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
970 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE );
971 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
973 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.';
975 COMMENT ON COLUMN "supporter"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
976 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")';
979 CREATE TABLE "opinion" (
980 "initiative_id" INT4 NOT NULL,
981 PRIMARY KEY ("suggestion_id", "member_id"),
982 "suggestion_id" INT8,
983 "member_id" INT4,
984 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
985 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
986 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
987 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
988 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
990 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.';
992 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
995 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
997 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
1000 CREATE TABLE "delegation" (
1001 "id" SERIAL8 PRIMARY KEY,
1002 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1003 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1004 "scope" "delegation_scope" NOT NULL,
1005 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1006 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1007 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1008 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
1009 CONSTRAINT "no_unit_delegation_to_null"
1010 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
1011 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
1012 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
1013 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
1014 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
1015 UNIQUE ("unit_id", "truster_id"),
1016 UNIQUE ("area_id", "truster_id"),
1017 UNIQUE ("issue_id", "truster_id") );
1018 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
1019 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
1021 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
1023 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
1024 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
1025 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
1028 CREATE TABLE "direct_population_snapshot" (
1029 PRIMARY KEY ("issue_id", "event", "member_id"),
1030 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1031 "event" "snapshot_event",
1032 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1033 "weight" INT4 );
1034 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
1036 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';
1038 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1039 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
1042 CREATE TABLE "delegating_population_snapshot" (
1043 PRIMARY KEY ("issue_id", "event", "member_id"),
1044 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1045 "event" "snapshot_event",
1046 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1047 "weight" INT4,
1048 "scope" "delegation_scope" NOT NULL,
1049 "delegate_member_ids" INT4[] NOT NULL );
1050 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
1052 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';
1054 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1055 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
1056 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
1057 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"';
1060 CREATE TABLE "direct_interest_snapshot" (
1061 PRIMARY KEY ("issue_id", "event", "member_id"),
1062 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1063 "event" "snapshot_event",
1064 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1065 "weight" INT4 );
1066 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
1068 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';
1070 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1071 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
1074 CREATE TABLE "delegating_interest_snapshot" (
1075 PRIMARY KEY ("issue_id", "event", "member_id"),
1076 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1077 "event" "snapshot_event",
1078 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1079 "weight" INT4,
1080 "scope" "delegation_scope" NOT NULL,
1081 "delegate_member_ids" INT4[] NOT NULL );
1082 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
1084 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';
1086 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1087 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
1088 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
1089 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"';
1092 CREATE TABLE "direct_supporter_snapshot" (
1093 "issue_id" INT4 NOT NULL,
1094 PRIMARY KEY ("initiative_id", "event", "member_id"),
1095 "initiative_id" INT4,
1096 "event" "snapshot_event",
1097 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1098 "draft_id" INT8 NOT NULL,
1099 "informed" BOOLEAN NOT NULL,
1100 "satisfied" BOOLEAN NOT NULL,
1101 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1102 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
1103 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1104 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
1106 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';
1108 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';
1109 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1110 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
1111 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
1114 CREATE TABLE "non_voter" (
1115 PRIMARY KEY ("issue_id", "member_id"),
1116 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1117 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
1118 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
1120 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
1123 CREATE TABLE "direct_voter" (
1124 PRIMARY KEY ("issue_id", "member_id"),
1125 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1126 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1127 "weight" INT4,
1128 "comment_changed" TIMESTAMPTZ,
1129 "formatting_engine" TEXT,
1130 "comment" TEXT,
1131 "text_search_data" TSVECTOR );
1132 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
1133 CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data");
1134 CREATE TRIGGER "update_text_search_data"
1135 BEFORE INSERT OR UPDATE ON "direct_voter"
1136 FOR EACH ROW EXECUTE PROCEDURE
1137 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment");
1139 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';
1141 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
1142 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';
1143 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';
1144 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.';
1147 CREATE TABLE "rendered_voter_comment" (
1148 PRIMARY KEY ("issue_id", "member_id", "format"),
1149 FOREIGN KEY ("issue_id", "member_id")
1150 REFERENCES "direct_voter" ("issue_id", "member_id")
1151 ON DELETE CASCADE ON UPDATE CASCADE,
1152 "issue_id" INT4,
1153 "member_id" INT4,
1154 "format" TEXT,
1155 "content" TEXT NOT NULL );
1157 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)';
1160 CREATE TABLE "delegating_voter" (
1161 PRIMARY KEY ("issue_id", "member_id"),
1162 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1163 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1164 "weight" INT4,
1165 "scope" "delegation_scope" NOT NULL,
1166 "delegate_member_ids" INT4[] NOT NULL );
1167 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
1169 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';
1171 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
1172 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
1173 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"';
1176 CREATE TABLE "vote" (
1177 "issue_id" INT4 NOT NULL,
1178 PRIMARY KEY ("initiative_id", "member_id"),
1179 "initiative_id" INT4,
1180 "member_id" INT4,
1181 "grade" INT4 NOT NULL,
1182 "first_preference" BOOLEAN,
1183 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1184 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
1185 CONSTRAINT "first_preference_flag_only_set_on_positive_grades"
1186 CHECK ("grade" > 0 OR "first_preference" ISNULL) );
1187 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
1189 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';
1191 COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
1192 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.';
1193 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.';
1196 CREATE TYPE "event_type" AS ENUM (
1197 'issue_state_changed',
1198 'initiative_created_in_new_issue',
1199 'initiative_created_in_existing_issue',
1200 'initiative_revoked',
1201 'new_draft_created',
1202 'suggestion_created');
1204 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
1207 CREATE TABLE "event" (
1208 "id" SERIAL8 PRIMARY KEY,
1209 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
1210 "event" "event_type" NOT NULL,
1211 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1212 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1213 "state" "issue_state",
1214 "initiative_id" INT4,
1215 "draft_id" INT8,
1216 "suggestion_id" INT8,
1217 FOREIGN KEY ("issue_id", "initiative_id")
1218 REFERENCES "initiative" ("issue_id", "id")
1219 ON DELETE CASCADE ON UPDATE CASCADE,
1220 FOREIGN KEY ("initiative_id", "draft_id")
1221 REFERENCES "draft" ("initiative_id", "id")
1222 ON DELETE CASCADE ON UPDATE CASCADE,
1223 FOREIGN KEY ("initiative_id", "suggestion_id")
1224 REFERENCES "suggestion" ("initiative_id", "id")
1225 ON DELETE CASCADE ON UPDATE CASCADE,
1226 CONSTRAINT "null_constr_for_issue_state_changed" CHECK (
1227 "event" != 'issue_state_changed' OR (
1228 "member_id" ISNULL AND
1229 "issue_id" NOTNULL AND
1230 "state" NOTNULL AND
1231 "initiative_id" ISNULL AND
1232 "draft_id" ISNULL AND
1233 "suggestion_id" ISNULL )),
1234 CONSTRAINT "null_constr_for_initiative_creation_or_revocation_or_new_draft" CHECK (
1235 "event" NOT IN (
1236 'initiative_created_in_new_issue',
1237 'initiative_created_in_existing_issue',
1238 'initiative_revoked',
1239 'new_draft_created'
1240 ) OR (
1241 "member_id" NOTNULL AND
1242 "issue_id" NOTNULL AND
1243 "state" NOTNULL AND
1244 "initiative_id" NOTNULL AND
1245 "draft_id" NOTNULL AND
1246 "suggestion_id" ISNULL )),
1247 CONSTRAINT "null_constr_for_suggestion_creation" CHECK (
1248 "event" != 'suggestion_created' OR (
1249 "member_id" NOTNULL AND
1250 "issue_id" NOTNULL AND
1251 "state" NOTNULL AND
1252 "initiative_id" NOTNULL AND
1253 "draft_id" ISNULL AND
1254 "suggestion_id" NOTNULL )) );
1255 CREATE INDEX "event_occurrence_idx" ON "event" ("occurrence");
1257 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
1259 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
1260 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
1261 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
1262 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
1265 CREATE TABLE "notification_sent" (
1266 "event_id" INT8 NOT NULL );
1267 CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1));
1269 COMMENT ON TABLE "notification_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';
1270 COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';
1273 CREATE TABLE "initiative_notification_sent" (
1274 PRIMARY KEY ("member_id", "initiative_id"),
1275 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1276 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1277 "last_draft_id" INT8 NOT NULL,
1278 "last_suggestion_id" INT8 );
1279 CREATE INDEX "initiative_notification_sent_initiative_idx" ON "initiative_notification_sent" ("initiative_id");
1282 CREATE TABLE "newsletter" (
1283 "id" SERIAL4 PRIMARY KEY,
1284 "published" TIMESTAMPTZ NOT NULL,
1285 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1286 "include_all_members" BOOLEAN NOT NULL,
1287 "sent" TIMESTAMPTZ,
1288 "subject" TEXT NOT NULL,
1289 "content" TEXT NOT NULL );
1290 CREATE INDEX "newsletter_unit_id_idx" ON "newsletter" ("unit_id", "published");
1291 CREATE INDEX "newsletter_all_units_published_idx" ON "newsletter" ("published") WHERE "unit_id" ISNULL;
1292 CREATE INDEX "newsletter_published_idx" ON "newsletter" ("published");
1296 ----------------------------------------------
1297 -- Writing of history entries and event log --
1298 ----------------------------------------------
1301 CREATE FUNCTION "write_member_history_trigger"()
1302 RETURNS TRIGGER
1303 LANGUAGE 'plpgsql' VOLATILE AS $$
1304 BEGIN
1305 IF
1306 ( NEW."active" != OLD."active" OR
1307 NEW."name" != OLD."name" ) AND
1308 OLD."activated" NOTNULL
1309 THEN
1310 INSERT INTO "member_history"
1311 ("member_id", "active", "name")
1312 VALUES (NEW."id", OLD."active", OLD."name");
1313 END IF;
1314 RETURN NULL;
1315 END;
1316 $$;
1318 CREATE TRIGGER "write_member_history"
1319 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1320 "write_member_history_trigger"();
1322 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1323 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1326 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1327 RETURNS TRIGGER
1328 LANGUAGE 'plpgsql' VOLATILE AS $$
1329 BEGIN
1330 IF NEW."state" != OLD."state" THEN
1331 INSERT INTO "event" ("event", "issue_id", "state")
1332 VALUES ('issue_state_changed', NEW."id", NEW."state");
1333 END IF;
1334 RETURN NULL;
1335 END;
1336 $$;
1338 CREATE TRIGGER "write_event_issue_state_changed"
1339 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1340 "write_event_issue_state_changed_trigger"();
1342 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1343 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1346 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1347 RETURNS TRIGGER
1348 LANGUAGE 'plpgsql' VOLATILE AS $$
1349 DECLARE
1350 "initiative_row" "initiative"%ROWTYPE;
1351 "issue_row" "issue"%ROWTYPE;
1352 "event_v" "event_type";
1353 BEGIN
1354 SELECT * INTO "initiative_row" FROM "initiative"
1355 WHERE "id" = NEW."initiative_id";
1356 SELECT * INTO "issue_row" FROM "issue"
1357 WHERE "id" = "initiative_row"."issue_id";
1358 IF EXISTS (
1359 SELECT NULL FROM "draft"
1360 WHERE "initiative_id" = NEW."initiative_id"
1361 AND "id" != NEW."id"
1362 ) THEN
1363 "event_v" := 'new_draft_created';
1364 ELSE
1365 IF EXISTS (
1366 SELECT NULL FROM "initiative"
1367 WHERE "issue_id" = "initiative_row"."issue_id"
1368 AND "id" != "initiative_row"."id"
1369 ) THEN
1370 "event_v" := 'initiative_created_in_existing_issue';
1371 ELSE
1372 "event_v" := 'initiative_created_in_new_issue';
1373 END IF;
1374 END IF;
1375 INSERT INTO "event" (
1376 "event", "member_id",
1377 "issue_id", "state", "initiative_id", "draft_id"
1378 ) VALUES (
1379 "event_v",
1380 NEW."author_id",
1381 "initiative_row"."issue_id",
1382 "issue_row"."state",
1383 "initiative_row"."id",
1384 NEW."id" );
1385 RETURN NULL;
1386 END;
1387 $$;
1389 CREATE TRIGGER "write_event_initiative_or_draft_created"
1390 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1391 "write_event_initiative_or_draft_created_trigger"();
1393 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1394 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1397 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1398 RETURNS TRIGGER
1399 LANGUAGE 'plpgsql' VOLATILE AS $$
1400 DECLARE
1401 "issue_row" "issue"%ROWTYPE;
1402 "draft_id_v" "draft"."id"%TYPE;
1403 BEGIN
1404 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1405 SELECT * INTO "issue_row" FROM "issue"
1406 WHERE "id" = NEW."issue_id";
1407 SELECT "id" INTO "draft_id_v" FROM "current_draft"
1408 WHERE "initiative_id" = NEW."id";
1409 INSERT INTO "event" (
1410 "event", "member_id", "issue_id", "state", "initiative_id", "draft_id"
1411 ) VALUES (
1412 'initiative_revoked',
1413 NEW."revoked_by_member_id",
1414 NEW."issue_id",
1415 "issue_row"."state",
1416 NEW."id",
1417 "draft_id_v");
1418 END IF;
1419 RETURN NULL;
1420 END;
1421 $$;
1423 CREATE TRIGGER "write_event_initiative_revoked"
1424 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1425 "write_event_initiative_revoked_trigger"();
1427 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1428 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1431 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1432 RETURNS TRIGGER
1433 LANGUAGE 'plpgsql' VOLATILE AS $$
1434 DECLARE
1435 "initiative_row" "initiative"%ROWTYPE;
1436 "issue_row" "issue"%ROWTYPE;
1437 BEGIN
1438 SELECT * INTO "initiative_row" FROM "initiative"
1439 WHERE "id" = NEW."initiative_id";
1440 SELECT * INTO "issue_row" FROM "issue"
1441 WHERE "id" = "initiative_row"."issue_id";
1442 INSERT INTO "event" (
1443 "event", "member_id",
1444 "issue_id", "state", "initiative_id", "suggestion_id"
1445 ) VALUES (
1446 'suggestion_created',
1447 NEW."author_id",
1448 "initiative_row"."issue_id",
1449 "issue_row"."state",
1450 "initiative_row"."id",
1451 NEW."id" );
1452 RETURN NULL;
1453 END;
1454 $$;
1456 CREATE TRIGGER "write_event_suggestion_created"
1457 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1458 "write_event_suggestion_created_trigger"();
1460 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1461 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1465 ----------------------------
1466 -- Additional constraints --
1467 ----------------------------
1470 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1471 RETURNS TRIGGER
1472 LANGUAGE 'plpgsql' VOLATILE AS $$
1473 BEGIN
1474 IF NOT EXISTS (
1475 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1476 ) THEN
1477 RAISE EXCEPTION 'Cannot create issue without an initial initiative.' USING
1478 ERRCODE = 'integrity_constraint_violation',
1479 HINT = 'Create issue, initiative, and draft within the same transaction.';
1480 END IF;
1481 RETURN NULL;
1482 END;
1483 $$;
1485 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1486 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1487 FOR EACH ROW EXECUTE PROCEDURE
1488 "issue_requires_first_initiative_trigger"();
1490 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1491 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1494 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1495 RETURNS TRIGGER
1496 LANGUAGE 'plpgsql' VOLATILE AS $$
1497 DECLARE
1498 "reference_lost" BOOLEAN;
1499 BEGIN
1500 IF TG_OP = 'DELETE' THEN
1501 "reference_lost" := TRUE;
1502 ELSE
1503 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1504 END IF;
1505 IF
1506 "reference_lost" AND NOT EXISTS (
1507 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1508 )
1509 THEN
1510 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1511 END IF;
1512 RETURN NULL;
1513 END;
1514 $$;
1516 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1517 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1518 FOR EACH ROW EXECUTE PROCEDURE
1519 "last_initiative_deletes_issue_trigger"();
1521 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1522 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1525 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1526 RETURNS TRIGGER
1527 LANGUAGE 'plpgsql' VOLATILE AS $$
1528 BEGIN
1529 IF NOT EXISTS (
1530 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1531 ) THEN
1532 RAISE EXCEPTION 'Cannot create initiative without an initial draft.' USING
1533 ERRCODE = 'integrity_constraint_violation',
1534 HINT = 'Create issue, initiative and draft within the same transaction.';
1535 END IF;
1536 RETURN NULL;
1537 END;
1538 $$;
1540 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1541 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1542 FOR EACH ROW EXECUTE PROCEDURE
1543 "initiative_requires_first_draft_trigger"();
1545 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1546 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1549 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1550 RETURNS TRIGGER
1551 LANGUAGE 'plpgsql' VOLATILE AS $$
1552 DECLARE
1553 "reference_lost" BOOLEAN;
1554 BEGIN
1555 IF TG_OP = 'DELETE' THEN
1556 "reference_lost" := TRUE;
1557 ELSE
1558 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1559 END IF;
1560 IF
1561 "reference_lost" AND NOT EXISTS (
1562 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1563 )
1564 THEN
1565 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1566 END IF;
1567 RETURN NULL;
1568 END;
1569 $$;
1571 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1572 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1573 FOR EACH ROW EXECUTE PROCEDURE
1574 "last_draft_deletes_initiative_trigger"();
1576 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1577 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1580 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1581 RETURNS TRIGGER
1582 LANGUAGE 'plpgsql' VOLATILE AS $$
1583 BEGIN
1584 IF NOT EXISTS (
1585 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1586 ) THEN
1587 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.' USING
1588 ERRCODE = 'integrity_constraint_violation',
1589 HINT = 'Create suggestion and opinion within the same transaction.';
1590 END IF;
1591 RETURN NULL;
1592 END;
1593 $$;
1595 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1596 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1597 FOR EACH ROW EXECUTE PROCEDURE
1598 "suggestion_requires_first_opinion_trigger"();
1600 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1601 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1604 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1605 RETURNS TRIGGER
1606 LANGUAGE 'plpgsql' VOLATILE AS $$
1607 DECLARE
1608 "reference_lost" BOOLEAN;
1609 BEGIN
1610 IF TG_OP = 'DELETE' THEN
1611 "reference_lost" := TRUE;
1612 ELSE
1613 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1614 END IF;
1615 IF
1616 "reference_lost" AND NOT EXISTS (
1617 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1618 )
1619 THEN
1620 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1621 END IF;
1622 RETURN NULL;
1623 END;
1624 $$;
1626 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1627 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1628 FOR EACH ROW EXECUTE PROCEDURE
1629 "last_opinion_deletes_suggestion_trigger"();
1631 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1632 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1635 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()
1636 RETURNS TRIGGER
1637 LANGUAGE 'plpgsql' VOLATILE AS $$
1638 BEGIN
1639 DELETE FROM "direct_voter"
1640 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1641 RETURN NULL;
1642 END;
1643 $$;
1645 CREATE TRIGGER "non_voter_deletes_direct_voter"
1646 AFTER INSERT OR UPDATE ON "non_voter"
1647 FOR EACH ROW EXECUTE PROCEDURE
1648 "non_voter_deletes_direct_voter_trigger"();
1650 COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';
1651 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")';
1654 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()
1655 RETURNS TRIGGER
1656 LANGUAGE 'plpgsql' VOLATILE AS $$
1657 BEGIN
1658 DELETE FROM "non_voter"
1659 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1660 RETURN NULL;
1661 END;
1662 $$;
1664 CREATE TRIGGER "direct_voter_deletes_non_voter"
1665 AFTER INSERT OR UPDATE ON "direct_voter"
1666 FOR EACH ROW EXECUTE PROCEDURE
1667 "direct_voter_deletes_non_voter_trigger"();
1669 COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';
1670 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")';
1673 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()
1674 RETURNS TRIGGER
1675 LANGUAGE 'plpgsql' VOLATILE AS $$
1676 BEGIN
1677 IF NEW."comment" ISNULL THEN
1678 NEW."comment_changed" := NULL;
1679 NEW."formatting_engine" := NULL;
1680 END IF;
1681 RETURN NEW;
1682 END;
1683 $$;
1685 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"
1686 BEFORE INSERT OR UPDATE ON "direct_voter"
1687 FOR EACH ROW EXECUTE PROCEDURE
1688 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();
1690 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"';
1691 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.';
1694 ---------------------------------------------------------------
1695 -- Ensure that votes are not modified when issues are closed --
1696 ---------------------------------------------------------------
1698 -- NOTE: Frontends should ensure this anyway, but in case of programming
1699 -- errors the following triggers ensure data integrity.
1702 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1703 RETURNS TRIGGER
1704 LANGUAGE 'plpgsql' VOLATILE AS $$
1705 DECLARE
1706 "issue_id_v" "issue"."id"%TYPE;
1707 "issue_row" "issue"%ROWTYPE;
1708 BEGIN
1709 IF EXISTS (
1710 SELECT NULL FROM "temporary_transaction_data"
1711 WHERE "txid" = txid_current()
1712 AND "key" = 'override_protection_triggers'
1713 AND "value" = TRUE::TEXT
1714 ) THEN
1715 RETURN NULL;
1716 END IF;
1717 IF TG_OP = 'DELETE' THEN
1718 "issue_id_v" := OLD."issue_id";
1719 ELSE
1720 "issue_id_v" := NEW."issue_id";
1721 END IF;
1722 SELECT INTO "issue_row" * FROM "issue"
1723 WHERE "id" = "issue_id_v" FOR SHARE;
1724 IF (
1725 "issue_row"."closed" NOTNULL OR (
1726 "issue_row"."state" = 'voting' AND
1727 "issue_row"."phase_finished" NOTNULL
1728 )
1729 ) THEN
1730 IF
1731 TG_RELID = 'direct_voter'::regclass AND
1732 TG_OP = 'UPDATE'
1733 THEN
1734 IF
1735 OLD."issue_id" = NEW."issue_id" AND
1736 OLD."member_id" = NEW."member_id" AND
1737 OLD."weight" = NEW."weight"
1738 THEN
1739 RETURN NULL; -- allows changing of voter comment
1740 END IF;
1741 END IF;
1742 RAISE EXCEPTION 'Tried to modify data after voting has been closed.' USING
1743 ERRCODE = 'integrity_constraint_violation';
1744 END IF;
1745 RETURN NULL;
1746 END;
1747 $$;
1749 CREATE TRIGGER "forbid_changes_on_closed_issue"
1750 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1751 FOR EACH ROW EXECUTE PROCEDURE
1752 "forbid_changes_on_closed_issue_trigger"();
1754 CREATE TRIGGER "forbid_changes_on_closed_issue"
1755 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1756 FOR EACH ROW EXECUTE PROCEDURE
1757 "forbid_changes_on_closed_issue_trigger"();
1759 CREATE TRIGGER "forbid_changes_on_closed_issue"
1760 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1761 FOR EACH ROW EXECUTE PROCEDURE
1762 "forbid_changes_on_closed_issue_trigger"();
1764 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"';
1765 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';
1766 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';
1767 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';
1771 --------------------------------------------------------------------
1772 -- Auto-retrieval of fields only needed for referential integrity --
1773 --------------------------------------------------------------------
1776 CREATE FUNCTION "autofill_issue_id_trigger"()
1777 RETURNS TRIGGER
1778 LANGUAGE 'plpgsql' VOLATILE AS $$
1779 BEGIN
1780 IF NEW."issue_id" ISNULL THEN
1781 SELECT "issue_id" INTO NEW."issue_id"
1782 FROM "initiative" WHERE "id" = NEW."initiative_id";
1783 END IF;
1784 RETURN NEW;
1785 END;
1786 $$;
1788 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1789 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1791 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1792 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1794 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1795 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1796 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1799 CREATE FUNCTION "autofill_initiative_id_trigger"()
1800 RETURNS TRIGGER
1801 LANGUAGE 'plpgsql' VOLATILE AS $$
1802 BEGIN
1803 IF NEW."initiative_id" ISNULL THEN
1804 SELECT "initiative_id" INTO NEW."initiative_id"
1805 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1806 END IF;
1807 RETURN NEW;
1808 END;
1809 $$;
1811 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1812 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1814 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1815 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1819 -----------------------------------------------------
1820 -- Automatic calculation of certain default values --
1821 -----------------------------------------------------
1824 CREATE FUNCTION "copy_timings_trigger"()
1825 RETURNS TRIGGER
1826 LANGUAGE 'plpgsql' VOLATILE AS $$
1827 DECLARE
1828 "policy_row" "policy"%ROWTYPE;
1829 BEGIN
1830 SELECT * INTO "policy_row" FROM "policy"
1831 WHERE "id" = NEW."policy_id";
1832 IF NEW."min_admission_time" ISNULL THEN
1833 NEW."min_admission_time" := "policy_row"."min_admission_time";
1834 END IF;
1835 IF NEW."max_admission_time" ISNULL THEN
1836 NEW."max_admission_time" := "policy_row"."max_admission_time";
1837 END IF;
1838 IF NEW."discussion_time" ISNULL THEN
1839 NEW."discussion_time" := "policy_row"."discussion_time";
1840 END IF;
1841 IF NEW."verification_time" ISNULL THEN
1842 NEW."verification_time" := "policy_row"."verification_time";
1843 END IF;
1844 IF NEW."voting_time" ISNULL THEN
1845 NEW."voting_time" := "policy_row"."voting_time";
1846 END IF;
1847 RETURN NEW;
1848 END;
1849 $$;
1851 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1852 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1854 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1855 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1858 CREATE FUNCTION "default_for_draft_id_trigger"()
1859 RETURNS TRIGGER
1860 LANGUAGE 'plpgsql' VOLATILE AS $$
1861 BEGIN
1862 IF NEW."draft_id" ISNULL THEN
1863 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1864 WHERE "initiative_id" = NEW."initiative_id";
1865 END IF;
1866 RETURN NEW;
1867 END;
1868 $$;
1870 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
1871 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1872 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1873 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1875 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
1876 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';
1877 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';
1881 ----------------------------------------
1882 -- Automatic creation of dependencies --
1883 ----------------------------------------
1886 CREATE FUNCTION "autocreate_interest_trigger"()
1887 RETURNS TRIGGER
1888 LANGUAGE 'plpgsql' VOLATILE AS $$
1889 BEGIN
1890 IF NOT EXISTS (
1891 SELECT NULL FROM "initiative" JOIN "interest"
1892 ON "initiative"."issue_id" = "interest"."issue_id"
1893 WHERE "initiative"."id" = NEW."initiative_id"
1894 AND "interest"."member_id" = NEW."member_id"
1895 ) THEN
1896 BEGIN
1897 INSERT INTO "interest" ("issue_id", "member_id")
1898 SELECT "issue_id", NEW."member_id"
1899 FROM "initiative" WHERE "id" = NEW."initiative_id";
1900 EXCEPTION WHEN unique_violation THEN END;
1901 END IF;
1902 RETURN NEW;
1903 END;
1904 $$;
1906 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1907 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1909 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1910 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';
1913 CREATE FUNCTION "autocreate_supporter_trigger"()
1914 RETURNS TRIGGER
1915 LANGUAGE 'plpgsql' VOLATILE AS $$
1916 BEGIN
1917 IF NOT EXISTS (
1918 SELECT NULL FROM "suggestion" JOIN "supporter"
1919 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1920 WHERE "suggestion"."id" = NEW."suggestion_id"
1921 AND "supporter"."member_id" = NEW."member_id"
1922 ) THEN
1923 BEGIN
1924 INSERT INTO "supporter" ("initiative_id", "member_id")
1925 SELECT "initiative_id", NEW."member_id"
1926 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1927 EXCEPTION WHEN unique_violation THEN END;
1928 END IF;
1929 RETURN NEW;
1930 END;
1931 $$;
1933 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1934 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1936 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1937 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.';
1941 ------------------------------------------
1942 -- Views and helper functions for views --
1943 ------------------------------------------
1946 CREATE VIEW "unit_delegation" AS
1947 SELECT
1948 "unit"."id" AS "unit_id",
1949 "delegation"."id",
1950 "delegation"."truster_id",
1951 "delegation"."trustee_id",
1952 "delegation"."scope"
1953 FROM "unit"
1954 JOIN "delegation"
1955 ON "delegation"."unit_id" = "unit"."id"
1956 JOIN "member"
1957 ON "delegation"."truster_id" = "member"."id"
1958 JOIN "privilege"
1959 ON "delegation"."unit_id" = "privilege"."unit_id"
1960 AND "delegation"."truster_id" = "privilege"."member_id"
1961 WHERE "member"."active" AND "privilege"."voting_right";
1963 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1966 CREATE VIEW "area_delegation" AS
1967 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1968 "area"."id" AS "area_id",
1969 "delegation"."id",
1970 "delegation"."truster_id",
1971 "delegation"."trustee_id",
1972 "delegation"."scope"
1973 FROM "area"
1974 JOIN "delegation"
1975 ON "delegation"."unit_id" = "area"."unit_id"
1976 OR "delegation"."area_id" = "area"."id"
1977 JOIN "member"
1978 ON "delegation"."truster_id" = "member"."id"
1979 JOIN "privilege"
1980 ON "area"."unit_id" = "privilege"."unit_id"
1981 AND "delegation"."truster_id" = "privilege"."member_id"
1982 WHERE "member"."active" AND "privilege"."voting_right"
1983 ORDER BY
1984 "area"."id",
1985 "delegation"."truster_id",
1986 "delegation"."scope" DESC;
1988 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1991 CREATE VIEW "issue_delegation" AS
1992 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1993 "issue"."id" AS "issue_id",
1994 "delegation"."id",
1995 "delegation"."truster_id",
1996 "delegation"."trustee_id",
1997 "delegation"."scope"
1998 FROM "issue"
1999 JOIN "area"
2000 ON "area"."id" = "issue"."area_id"
2001 JOIN "delegation"
2002 ON "delegation"."unit_id" = "area"."unit_id"
2003 OR "delegation"."area_id" = "area"."id"
2004 OR "delegation"."issue_id" = "issue"."id"
2005 JOIN "member"
2006 ON "delegation"."truster_id" = "member"."id"
2007 JOIN "privilege"
2008 ON "area"."unit_id" = "privilege"."unit_id"
2009 AND "delegation"."truster_id" = "privilege"."member_id"
2010 WHERE "member"."active" AND "privilege"."voting_right"
2011 ORDER BY
2012 "issue"."id",
2013 "delegation"."truster_id",
2014 "delegation"."scope" DESC;
2016 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
2019 CREATE FUNCTION "membership_weight_with_skipping"
2020 ( "area_id_p" "area"."id"%TYPE,
2021 "member_id_p" "member"."id"%TYPE,
2022 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
2023 RETURNS INT4
2024 LANGUAGE 'plpgsql' STABLE AS $$
2025 DECLARE
2026 "sum_v" INT4;
2027 "delegation_row" "area_delegation"%ROWTYPE;
2028 BEGIN
2029 "sum_v" := 1;
2030 FOR "delegation_row" IN
2031 SELECT "area_delegation".*
2032 FROM "area_delegation" LEFT JOIN "membership"
2033 ON "membership"."area_id" = "area_id_p"
2034 AND "membership"."member_id" = "area_delegation"."truster_id"
2035 WHERE "area_delegation"."area_id" = "area_id_p"
2036 AND "area_delegation"."trustee_id" = "member_id_p"
2037 AND "membership"."member_id" ISNULL
2038 LOOP
2039 IF NOT
2040 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
2041 THEN
2042 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
2043 "area_id_p",
2044 "delegation_row"."truster_id",
2045 "skip_member_ids_p" || "delegation_row"."truster_id"
2046 );
2047 END IF;
2048 END LOOP;
2049 RETURN "sum_v";
2050 END;
2051 $$;
2053 COMMENT ON FUNCTION "membership_weight_with_skipping"
2054 ( "area"."id"%TYPE,
2055 "member"."id"%TYPE,
2056 INT4[] )
2057 IS 'Helper function for "membership_weight" function';
2060 CREATE FUNCTION "membership_weight"
2061 ( "area_id_p" "area"."id"%TYPE,
2062 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
2063 RETURNS INT4
2064 LANGUAGE 'plpgsql' STABLE AS $$
2065 BEGIN
2066 RETURN "membership_weight_with_skipping"(
2067 "area_id_p",
2068 "member_id_p",
2069 ARRAY["member_id_p"]
2070 );
2071 END;
2072 $$;
2074 COMMENT ON FUNCTION "membership_weight"
2075 ( "area"."id"%TYPE,
2076 "member"."id"%TYPE )
2077 IS 'Calculates the potential voting weight of a member in a given area';
2080 CREATE VIEW "member_count_view" AS
2081 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
2083 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
2086 CREATE VIEW "unit_member_count" AS
2087 SELECT
2088 "unit"."id" AS "unit_id",
2089 count("member"."id") AS "member_count"
2090 FROM "unit"
2091 LEFT JOIN "privilege"
2092 ON "privilege"."unit_id" = "unit"."id"
2093 AND "privilege"."voting_right"
2094 LEFT JOIN "member"
2095 ON "member"."id" = "privilege"."member_id"
2096 AND "member"."active"
2097 GROUP BY "unit"."id";
2099 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
2102 CREATE VIEW "area_member_count" AS
2103 SELECT
2104 "area"."id" AS "area_id",
2105 count("member"."id") AS "direct_member_count",
2106 coalesce(
2107 sum(
2108 CASE WHEN "member"."id" NOTNULL THEN
2109 "membership_weight"("area"."id", "member"."id")
2110 ELSE 0 END
2111 )
2112 ) AS "member_weight"
2113 FROM "area"
2114 LEFT JOIN "membership"
2115 ON "area"."id" = "membership"."area_id"
2116 LEFT JOIN "privilege"
2117 ON "privilege"."unit_id" = "area"."unit_id"
2118 AND "privilege"."member_id" = "membership"."member_id"
2119 AND "privilege"."voting_right"
2120 LEFT JOIN "member"
2121 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
2122 AND "member"."active"
2123 GROUP BY "area"."id";
2125 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';
2128 CREATE VIEW "opening_draft" AS
2129 SELECT "draft".* FROM (
2130 SELECT
2131 "initiative"."id" AS "initiative_id",
2132 min("draft"."id") AS "draft_id"
2133 FROM "initiative" JOIN "draft"
2134 ON "initiative"."id" = "draft"."initiative_id"
2135 GROUP BY "initiative"."id"
2136 ) AS "subquery"
2137 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
2139 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
2142 CREATE VIEW "current_draft" AS
2143 SELECT "draft".* FROM (
2144 SELECT
2145 "initiative"."id" AS "initiative_id",
2146 max("draft"."id") AS "draft_id"
2147 FROM "initiative" JOIN "draft"
2148 ON "initiative"."id" = "draft"."initiative_id"
2149 GROUP BY "initiative"."id"
2150 ) AS "subquery"
2151 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
2153 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
2156 CREATE VIEW "critical_opinion" AS
2157 SELECT * FROM "opinion"
2158 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
2159 OR ("degree" = -2 AND "fulfilled" = TRUE);
2161 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
2164 CREATE VIEW "issue_supporter_in_admission_state" AS
2165 SELECT DISTINCT -- TODO: DISTINCT needed?
2166 "area"."unit_id",
2167 "issue"."area_id",
2168 "issue"."id" AS "issue_id",
2169 "supporter"."member_id",
2170 "direct_interest_snapshot"."weight"
2171 FROM "issue"
2172 JOIN "area" ON "area"."id" = "issue"."area_id"
2173 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
2174 JOIN "direct_interest_snapshot"
2175 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
2176 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
2177 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
2178 WHERE "issue"."state" = 'admission'::"issue_state";
2180 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';
2183 CREATE VIEW "initiative_suggestion_order_calculation" AS
2184 SELECT
2185 "initiative"."id" AS "initiative_id",
2186 ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final"
2187 FROM "initiative" JOIN "issue"
2188 ON "initiative"."issue_id" = "issue"."id"
2189 WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL)
2190 OR ("initiative"."final_suggestion_order_calculated" = FALSE);
2192 COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';
2194 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';
2197 CREATE VIEW "individual_suggestion_ranking" AS
2198 SELECT
2199 "opinion"."initiative_id",
2200 "opinion"."member_id",
2201 "direct_interest_snapshot"."weight",
2202 CASE WHEN
2203 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
2204 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
2205 THEN 1 ELSE
2206 CASE WHEN
2207 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
2208 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
2209 THEN 2 ELSE
2210 CASE WHEN
2211 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
2212 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
2213 THEN 3 ELSE 4 END
2214 END
2215 END AS "preference",
2216 "opinion"."suggestion_id"
2217 FROM "opinion"
2218 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
2219 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2220 JOIN "direct_interest_snapshot"
2221 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
2222 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
2223 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
2225 COMMENT ON VIEW "individual_suggestion_ranking" IS 'Helper view for "lf_update_suggestion_order" to allow a proportional ordering of suggestions within an initiative';
2228 CREATE VIEW "battle_participant" AS
2229 SELECT "initiative"."id", "initiative"."issue_id"
2230 FROM "issue" JOIN "initiative"
2231 ON "issue"."id" = "initiative"."issue_id"
2232 WHERE "initiative"."admitted"
2233 UNION ALL
2234 SELECT NULL, "id" AS "issue_id"
2235 FROM "issue";
2237 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
2240 CREATE VIEW "battle_view" AS
2241 SELECT
2242 "issue"."id" AS "issue_id",
2243 "winning_initiative"."id" AS "winning_initiative_id",
2244 "losing_initiative"."id" AS "losing_initiative_id",
2245 sum(
2246 CASE WHEN
2247 coalesce("better_vote"."grade", 0) >
2248 coalesce("worse_vote"."grade", 0)
2249 THEN "direct_voter"."weight" ELSE 0 END
2250 ) AS "count"
2251 FROM "issue"
2252 LEFT JOIN "direct_voter"
2253 ON "issue"."id" = "direct_voter"."issue_id"
2254 JOIN "battle_participant" AS "winning_initiative"
2255 ON "issue"."id" = "winning_initiative"."issue_id"
2256 JOIN "battle_participant" AS "losing_initiative"
2257 ON "issue"."id" = "losing_initiative"."issue_id"
2258 LEFT JOIN "vote" AS "better_vote"
2259 ON "direct_voter"."member_id" = "better_vote"."member_id"
2260 AND "winning_initiative"."id" = "better_vote"."initiative_id"
2261 LEFT JOIN "vote" AS "worse_vote"
2262 ON "direct_voter"."member_id" = "worse_vote"."member_id"
2263 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
2264 WHERE "issue"."state" = 'voting'
2265 AND "issue"."phase_finished" NOTNULL
2266 AND (
2267 "winning_initiative"."id" != "losing_initiative"."id" OR
2268 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
2269 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
2270 GROUP BY
2271 "issue"."id",
2272 "winning_initiative"."id",
2273 "losing_initiative"."id";
2275 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';
2278 CREATE VIEW "expired_session" AS
2279 SELECT * FROM "session" WHERE now() > "expiry";
2281 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
2282 DELETE FROM "session" WHERE "ident" = OLD."ident";
2284 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
2285 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
2288 CREATE VIEW "open_issue" AS
2289 SELECT * FROM "issue" WHERE "closed" ISNULL;
2291 COMMENT ON VIEW "open_issue" IS 'All open issues';
2294 CREATE VIEW "member_contingent" AS
2295 SELECT
2296 "member"."id" AS "member_id",
2297 "contingent"."polling",
2298 "contingent"."time_frame",
2299 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
2300 (
2301 SELECT count(1) FROM "draft"
2302 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
2303 WHERE "draft"."author_id" = "member"."id"
2304 AND "initiative"."polling" = "contingent"."polling"
2305 AND "draft"."created" > now() - "contingent"."time_frame"
2306 ) + (
2307 SELECT count(1) FROM "suggestion"
2308 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
2309 WHERE "suggestion"."author_id" = "member"."id"
2310 AND "contingent"."polling" = FALSE
2311 AND "suggestion"."created" > now() - "contingent"."time_frame"
2312 )
2313 ELSE NULL END AS "text_entry_count",
2314 "contingent"."text_entry_limit",
2315 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
2316 SELECT count(1) FROM "opening_draft" AS "draft"
2317 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
2318 WHERE "draft"."author_id" = "member"."id"
2319 AND "initiative"."polling" = "contingent"."polling"
2320 AND "draft"."created" > now() - "contingent"."time_frame"
2321 ) ELSE NULL END AS "initiative_count",
2322 "contingent"."initiative_limit"
2323 FROM "member" CROSS JOIN "contingent";
2325 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
2327 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
2328 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
2331 CREATE VIEW "member_contingent_left" AS
2332 SELECT
2333 "member_id",
2334 "polling",
2335 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
2336 max("initiative_limit" - "initiative_count") AS "initiatives_left"
2337 FROM "member_contingent" GROUP BY "member_id", "polling";
2339 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.';
2342 CREATE VIEW "event_seen_by_member" AS
2343 SELECT
2344 "member"."id" AS "seen_by_member_id",
2345 CASE WHEN "event"."state" IN (
2346 'voting',
2347 'finished_without_winner',
2348 'finished_with_winner'
2349 ) THEN
2350 'voting'::"notify_level"
2351 ELSE
2352 CASE WHEN "event"."state" IN (
2353 'verification',
2354 'canceled_after_revocation_during_verification',
2355 'canceled_no_initiative_admitted'
2356 ) THEN
2357 'verification'::"notify_level"
2358 ELSE
2359 CASE WHEN "event"."state" IN (
2360 'discussion',
2361 'canceled_after_revocation_during_discussion'
2362 ) THEN
2363 'discussion'::"notify_level"
2364 ELSE
2365 'all'::"notify_level"
2366 END
2367 END
2368 END AS "notify_level",
2369 "event".*
2370 FROM "member" CROSS JOIN "event"
2371 LEFT JOIN "issue"
2372 ON "event"."issue_id" = "issue"."id"
2373 LEFT JOIN "membership"
2374 ON "member"."id" = "membership"."member_id"
2375 AND "issue"."area_id" = "membership"."area_id"
2376 LEFT JOIN "interest"
2377 ON "member"."id" = "interest"."member_id"
2378 AND "event"."issue_id" = "interest"."issue_id"
2379 LEFT JOIN "ignored_member"
2380 ON "member"."id" = "ignored_member"."member_id"
2381 AND "event"."member_id" = "ignored_member"."other_member_id"
2382 LEFT JOIN "ignored_initiative"
2383 ON "member"."id" = "ignored_initiative"."member_id"
2384 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2385 WHERE (
2386 "interest"."member_id" NOTNULL OR
2387 ( "membership"."member_id" NOTNULL AND
2388 "event"."event" IN (
2389 'issue_state_changed',
2390 'initiative_created_in_new_issue',
2391 'initiative_created_in_existing_issue',
2392 'initiative_revoked' ) ) )
2393 AND "ignored_member"."member_id" ISNULL
2394 AND "ignored_initiative"."member_id" ISNULL;
2396 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"';
2399 CREATE VIEW "updated_initiative" AS
2400 SELECT
2401 "supporter"."member_id" AS "seen_by_member_id",
2402 TRUE AS "supported",
2403 EXISTS (
2404 SELECT NULL FROM "draft"
2405 WHERE "draft"."initiative_id" = "initiative"."id"
2406 AND "draft"."id" > "supporter"."draft_id"
2407 ) AS "new_draft",
2408 ( SELECT count(1) FROM "suggestion"
2409 LEFT JOIN "opinion" ON
2410 "opinion"."member_id" = "supporter"."member_id" AND
2411 "opinion"."suggestion_id" = "suggestion"."id"
2412 WHERE "suggestion"."initiative_id" = "initiative"."id"
2413 AND "opinion"."member_id" ISNULL
2414 AND COALESCE(
2415 "suggestion"."id" > "sent"."last_suggestion_id",
2416 TRUE
2417 )
2418 ) AS "new_suggestion_count",
2419 FALSE AS "featured",
2420 NOT EXISTS (
2421 SELECT NULL FROM "initiative" AS "better_initiative"
2422 WHERE
2423 "better_initiative"."issue_id" = "initiative"."issue_id"
2424 AND
2425 ( COALESCE("better_initiative"."harmonic_weight", -1),
2426 -"better_initiative"."id" ) >
2427 ( COALESCE("initiative"."harmonic_weight", -1),
2428 -"initiative"."id" )
2429 ) AS "leading",
2430 "initiative".*
2431 FROM "supporter" JOIN "initiative"
2432 ON "supporter"."initiative_id" = "initiative"."id"
2433 LEFT JOIN "initiative_notification_sent" AS "sent"
2434 ON "sent"."member_id" = "supporter"."member_id"
2435 AND "sent"."initiative_id" = "initiative"."id"
2436 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2437 WHERE "issue"."state" IN ('admission', 'discussion')
2438 AND (
2439 EXISTS (
2440 SELECT NULL FROM "draft"
2441 WHERE "draft"."initiative_id" = "initiative"."id"
2442 AND "draft"."id" > "supporter"."draft_id"
2443 ) OR EXISTS (
2444 SELECT NULL FROM "suggestion"
2445 LEFT JOIN "opinion" ON
2446 "opinion"."member_id" = "supporter"."member_id" AND
2447 "opinion"."suggestion_id" = "suggestion"."id"
2448 WHERE "suggestion"."initiative_id" = "initiative"."id"
2449 AND "opinion"."member_id" ISNULL
2450 AND COALESCE(
2451 "suggestion"."id" > "sent"."last_suggestion_id",
2452 TRUE
2453 )
2454 )
2455 );
2457 CREATE FUNCTION "featured_initiative"
2458 ( "member_id_p" "member"."id"%TYPE,
2459 "area_id_p" "area"."id"%TYPE )
2460 RETURNS SETOF "initiative"
2461 LANGUAGE 'plpgsql' STABLE AS $$
2462 DECLARE
2463 "member_row" "member"%ROWTYPE;
2464 "member_id_v" "member"."id"%TYPE;
2465 "seed_v" TEXT;
2466 "result_row" "initiative"%ROWTYPE;
2467 "match_v" BOOLEAN;
2468 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
2469 BEGIN
2470 SELECT INTO "member_row" * FROM "member" WHERE "id" = "member_id_p";
2471 "initiative_id_ary" := '{}';
2472 LOOP
2473 "match_v" := FALSE;
2474 FOR "member_id_v", "seed_v" IN
2475 SELECT * FROM (
2476 SELECT DISTINCT
2477 "supporter"."member_id",
2478 md5("member_id_p" || '-' || "member_row"."notification_counter" || '-' || "area_id_p" || '-' || "supporter"."member_id") AS "seed"
2479 FROM "supporter"
2480 JOIN "member" ON "member"."id" = "supporter"."member_id"
2481 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
2482 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2483 WHERE "supporter"."member_id" != "member_id_p"
2484 AND "issue"."area_id" = "area_id_p"
2485 AND "issue"."state" IN ('admission', 'discussion', 'verification')
2486 ) AS "subquery"
2487 ORDER BY "seed"
2488 LOOP
2489 SELECT "initiative".* INTO "result_row"
2490 FROM "initiative"
2491 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2492 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
2493 LEFT JOIN "supporter" AS "self_support" ON
2494 "self_support"."initiative_id" = "initiative"."id" AND
2495 "self_support"."member_id" = "member_id_p"
2496 WHERE "supporter"."member_id" = "member_id_v"
2497 AND "issue"."area_id" = "area_id_p"
2498 AND "issue"."state" IN ('admission', 'discussion', 'verification')
2499 AND "self_support"."member_id" ISNULL
2500 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
2501 ORDER BY md5("seed_v" || '-' || "initiative"."id")
2502 LIMIT 1;
2503 IF FOUND THEN
2504 "match_v" := TRUE;
2505 "initiative_id_ary" := "initiative_id_ary" || "result_row"."id";
2506 RETURN NEXT "result_row";
2507 IF array_length("initiative_id_ary", 1) >= "member_row"."notification_sample_size" THEN
2508 RETURN;
2509 END IF;
2510 END IF;
2511 END LOOP;
2512 EXIT WHEN NOT "match_v";
2513 END LOOP;
2514 RETURN;
2515 END;
2516 $$;
2518 CREATE VIEW "updated_or_featured_initiative" AS
2519 SELECT * FROM "updated_initiative"
2520 UNION ALL
2521 SELECT
2522 "member"."id" AS "seen_by_member_id",
2523 FALSE AS "supported",
2524 EXISTS (
2525 SELECT NULL FROM "draft"
2526 WHERE "draft"."initiative_id" = "initiative"."id"
2527 AND COALESCE(
2528 "draft"."id" > "sent"."last_draft_id",
2529 TRUE
2530 )
2531 ) AS "new_draft",
2532 ( SELECT count(1) FROM "suggestion"
2533 WHERE "suggestion"."initiative_id" = "initiative"."id"
2534 AND COALESCE(
2535 "suggestion"."id" > "sent"."last_suggestion_id",
2536 TRUE
2537 )
2538 ) AS "new_suggestion_count",
2539 TRUE AS "featured",
2540 NOT EXISTS (
2541 SELECT NULL FROM "initiative" AS "better_initiative"
2542 WHERE
2543 "better_initiative"."issue_id" = "initiative"."issue_id"
2544 AND
2545 ( COALESCE("better_initiative"."harmonic_weight", -1),
2546 -"better_initiative"."id" ) >
2547 ( COALESCE("initiative"."harmonic_weight", -1),
2548 -"initiative"."id" )
2549 ) AS "leading",
2550 "initiative".*
2551 FROM "member" CROSS JOIN "area"
2552 CROSS JOIN LATERAL
2553 "featured_initiative"("member"."id", "area"."id") AS "initiative"
2554 LEFT JOIN "initiative_notification_sent" AS "sent"
2555 ON "sent"."member_id" = "member"."id"
2556 AND "sent"."initiative_id" = "initiative"."id";
2558 CREATE VIEW "leading_complement_initiative" AS
2559 SELECT * FROM (
2560 SELECT DISTINCT ON ("seen_by_member_id", "initiative"."issue_id")
2561 "uf_initiative"."seen_by_member_id",
2562 "supporter"."member_id" NOTNULL AS "supported",
2563 CASE WHEN "supporter"."member_id" NOTNULL THEN FALSE ELSE
2564 EXISTS (
2565 SELECT NULL FROM "draft"
2566 WHERE "draft"."initiative_id" = "initiative"."id"
2567 AND COALESCE(
2568 "draft"."id" > "sent"."last_draft_id",
2569 TRUE
2570 )
2571 )
2572 END AS "new_draft",
2573 CASE WHEN "supporter"."member_id" NOTNULL THEN 0 ELSE
2574 ( SELECT count(1) FROM "suggestion"
2575 WHERE "suggestion"."initiative_id" = "initiative"."id"
2576 AND COALESCE(
2577 "suggestion"."id" > "sent"."last_suggestion_id",
2578 TRUE
2579 )
2580 )
2581 END AS "new_suggestion_count",
2582 FALSE AS "featured",
2583 TRUE AS "leading",
2584 "initiative".*
2585 FROM "updated_or_featured_initiative" AS "uf_initiative"
2586 JOIN "initiative" ON
2587 "uf_initiative"."issue_id" = "initiative"."issue_id"
2588 LEFT JOIN "supporter" ON
2589 "supporter"."member_id" = "uf_initiative"."seen_by_member_id" AND
2590 "supporter"."initiative_id" = "initiative"."id"
2591 LEFT JOIN "initiative_notification_sent" AS "sent"
2592 ON "sent"."member_id" = "uf_initiative"."seen_by_member_id"
2593 AND "sent"."initiative_id" = "initiative"."id"
2594 ORDER BY
2595 "seen_by_member_id",
2596 "initiative"."issue_id",
2597 "initiative"."harmonic_weight" DESC,
2598 "initiative"."id"
2599 ) AS "subquery"
2600 WHERE NOT EXISTS (
2601 SELECT NULL FROM "updated_or_featured_initiative" AS "other"
2602 WHERE "other"."seen_by_member_id" = "subquery"."seen_by_member_id"
2603 AND "other"."id" = "subquery"."id"
2604 );
2606 CREATE VIEW "unfiltered_initiative_for_notification" AS
2607 SELECT * FROM "updated_or_featured_initiative"
2608 UNION ALL
2609 SELECT * FROM "leading_complement_initiative";
2611 CREATE VIEW "initiative_for_notification" AS
2612 SELECT "initiative1".*
2613 FROM "unfiltered_initiative_for_notification" "initiative1"
2614 JOIN "issue" AS "issue1" ON "initiative1"."issue_id" = "issue1"."id"
2615 WHERE EXISTS (
2616 SELECT NULL
2617 FROM "unfiltered_initiative_for_notification" "initiative2"
2618 JOIN "issue" AS "issue2" ON "initiative2"."issue_id" = "issue2"."id"
2619 WHERE "initiative1"."seen_by_member_id" = "initiative2"."seen_by_member_id"
2620 AND "issue1"."area_id" = "issue2"."area_id"
2621 AND ( "initiative2"."new_draft" OR "initiative2"."new_suggestion_count" > 0 )
2622 );
2624 CREATE VIEW "newsletter_to_send" AS
2625 SELECT
2626 "newsletter"."id" AS "newsletter_id",
2627 "member"."id" AS "member_id"
2628 FROM "newsletter" CROSS JOIN "member"
2629 LEFT JOIN "privilege" ON
2630 "privilege"."member_id" = "member"."id" AND
2631 "privilege"."unit_id" = "newsletter"."unit_id" AND
2632 "privilege"."voting_right" = TRUE
2633 LEFT JOIN "subscription" ON
2634 "subscription"."member_id" = "member"."id" AND
2635 "subscription"."unit_id" = "newsletter"."unit_id"
2636 WHERE "newsletter"."published" <= now()
2637 AND "newsletter"."sent" ISNULL
2638 AND "member"."locked" = FALSE
2639 AND (
2640 "member"."disable_notifications" = FALSE OR
2641 "newsletter"."include_all_members" = TRUE )
2642 AND (
2643 "newsletter"."unit_id" ISNULL OR
2644 "privilege"."member_id" NOTNULL OR
2645 "subscription"."member_id" NOTNULL );
2649 ------------------------------------------------------
2650 -- Row set returning function for delegation chains --
2651 ------------------------------------------------------
2654 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2655 ('first', 'intermediate', 'last', 'repetition');
2657 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2660 CREATE TYPE "delegation_chain_row" AS (
2661 "index" INT4,
2662 "member_id" INT4,
2663 "member_valid" BOOLEAN,
2664 "participation" BOOLEAN,
2665 "overridden" BOOLEAN,
2666 "scope_in" "delegation_scope",
2667 "scope_out" "delegation_scope",
2668 "disabled_out" BOOLEAN,
2669 "loop" "delegation_chain_loop_tag" );
2671 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
2673 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2674 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';
2675 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2676 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2677 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2678 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2679 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2682 CREATE FUNCTION "delegation_chain_for_closed_issue"
2683 ( "member_id_p" "member"."id"%TYPE,
2684 "issue_id_p" "issue"."id"%TYPE )
2685 RETURNS SETOF "delegation_chain_row"
2686 LANGUAGE 'plpgsql' STABLE AS $$
2687 DECLARE
2688 "output_row" "delegation_chain_row";
2689 "direct_voter_row" "direct_voter"%ROWTYPE;
2690 "delegating_voter_row" "delegating_voter"%ROWTYPE;
2691 BEGIN
2692 "output_row"."index" := 0;
2693 "output_row"."member_id" := "member_id_p";
2694 "output_row"."member_valid" := TRUE;
2695 "output_row"."participation" := FALSE;
2696 "output_row"."overridden" := FALSE;
2697 "output_row"."disabled_out" := FALSE;
2698 LOOP
2699 SELECT INTO "direct_voter_row" * FROM "direct_voter"
2700 WHERE "issue_id" = "issue_id_p"
2701 AND "member_id" = "output_row"."member_id";
2702 IF "direct_voter_row"."member_id" NOTNULL THEN
2703 "output_row"."participation" := TRUE;
2704 "output_row"."scope_out" := NULL;
2705 "output_row"."disabled_out" := NULL;
2706 RETURN NEXT "output_row";
2707 RETURN;
2708 END IF;
2709 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
2710 WHERE "issue_id" = "issue_id_p"
2711 AND "member_id" = "output_row"."member_id";
2712 IF "delegating_voter_row"."member_id" ISNULL THEN
2713 RETURN;
2714 END IF;
2715 "output_row"."scope_out" := "delegating_voter_row"."scope";
2716 RETURN NEXT "output_row";
2717 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
2718 "output_row"."scope_in" := "output_row"."scope_out";
2719 END LOOP;
2720 END;
2721 $$;
2723 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
2724 ( "member"."id"%TYPE,
2725 "member"."id"%TYPE )
2726 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
2729 CREATE FUNCTION "delegation_chain"
2730 ( "member_id_p" "member"."id"%TYPE,
2731 "unit_id_p" "unit"."id"%TYPE,
2732 "area_id_p" "area"."id"%TYPE,
2733 "issue_id_p" "issue"."id"%TYPE,
2734 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2735 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2736 RETURNS SETOF "delegation_chain_row"
2737 LANGUAGE 'plpgsql' STABLE AS $$
2738 DECLARE
2739 "scope_v" "delegation_scope";
2740 "unit_id_v" "unit"."id"%TYPE;
2741 "area_id_v" "area"."id"%TYPE;
2742 "issue_row" "issue"%ROWTYPE;
2743 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2744 "loop_member_id_v" "member"."id"%TYPE;
2745 "output_row" "delegation_chain_row";
2746 "output_rows" "delegation_chain_row"[];
2747 "simulate_v" BOOLEAN;
2748 "simulate_here_v" BOOLEAN;
2749 "delegation_row" "delegation"%ROWTYPE;
2750 "row_count" INT4;
2751 "i" INT4;
2752 "loop_v" BOOLEAN;
2753 BEGIN
2754 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
2755 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
2756 END IF;
2757 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
2758 "simulate_v" := TRUE;
2759 ELSE
2760 "simulate_v" := FALSE;
2761 END IF;
2762 IF
2763 "unit_id_p" NOTNULL AND
2764 "area_id_p" ISNULL AND
2765 "issue_id_p" ISNULL
2766 THEN
2767 "scope_v" := 'unit';
2768 "unit_id_v" := "unit_id_p";
2769 ELSIF
2770 "unit_id_p" ISNULL AND
2771 "area_id_p" NOTNULL AND
2772 "issue_id_p" ISNULL
2773 THEN
2774 "scope_v" := 'area';
2775 "area_id_v" := "area_id_p";
2776 SELECT "unit_id" INTO "unit_id_v"
2777 FROM "area" WHERE "id" = "area_id_v";
2778 ELSIF
2779 "unit_id_p" ISNULL AND
2780 "area_id_p" ISNULL AND
2781 "issue_id_p" NOTNULL
2782 THEN
2783 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
2784 IF "issue_row"."id" ISNULL THEN
2785 RETURN;
2786 END IF;
2787 IF "issue_row"."closed" NOTNULL THEN
2788 IF "simulate_v" THEN
2789 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
2790 END IF;
2791 FOR "output_row" IN
2792 SELECT * FROM
2793 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
2794 LOOP
2795 RETURN NEXT "output_row";
2796 END LOOP;
2797 RETURN;
2798 END IF;
2799 "scope_v" := 'issue';
2800 SELECT "area_id" INTO "area_id_v"
2801 FROM "issue" WHERE "id" = "issue_id_p";
2802 SELECT "unit_id" INTO "unit_id_v"
2803 FROM "area" WHERE "id" = "area_id_v";
2804 ELSE
2805 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2806 END IF;
2807 "visited_member_ids" := '{}';
2808 "loop_member_id_v" := NULL;
2809 "output_rows" := '{}';
2810 "output_row"."index" := 0;
2811 "output_row"."member_id" := "member_id_p";
2812 "output_row"."member_valid" := TRUE;
2813 "output_row"."participation" := FALSE;
2814 "output_row"."overridden" := FALSE;
2815 "output_row"."disabled_out" := FALSE;
2816 "output_row"."scope_out" := NULL;
2817 LOOP
2818 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2819 "loop_member_id_v" := "output_row"."member_id";
2820 ELSE
2821 "visited_member_ids" :=
2822 "visited_member_ids" || "output_row"."member_id";
2823 END IF;
2824 IF "output_row"."participation" ISNULL THEN
2825 "output_row"."overridden" := NULL;
2826 ELSIF "output_row"."participation" THEN
2827 "output_row"."overridden" := TRUE;
2828 END IF;
2829 "output_row"."scope_in" := "output_row"."scope_out";
2830 "output_row"."member_valid" := EXISTS (
2831 SELECT NULL FROM "member" JOIN "privilege"
2832 ON "privilege"."member_id" = "member"."id"
2833 AND "privilege"."unit_id" = "unit_id_v"
2834 WHERE "id" = "output_row"."member_id"
2835 AND "member"."active" AND "privilege"."voting_right"
2836 );
2837 "simulate_here_v" := (
2838 "simulate_v" AND
2839 "output_row"."member_id" = "member_id_p"
2840 );
2841 "delegation_row" := ROW(NULL);
2842 IF "output_row"."member_valid" OR "simulate_here_v" THEN
2843 IF "scope_v" = 'unit' THEN
2844 IF NOT "simulate_here_v" THEN
2845 SELECT * INTO "delegation_row" FROM "delegation"
2846 WHERE "truster_id" = "output_row"."member_id"
2847 AND "unit_id" = "unit_id_v";
2848 END IF;
2849 ELSIF "scope_v" = 'area' THEN
2850 "output_row"."participation" := EXISTS (
2851 SELECT NULL FROM "membership"
2852 WHERE "area_id" = "area_id_p"
2853 AND "member_id" = "output_row"."member_id"
2854 );
2855 IF "simulate_here_v" THEN
2856 IF "simulate_trustee_id_p" ISNULL THEN
2857 SELECT * INTO "delegation_row" FROM "delegation"
2858 WHERE "truster_id" = "output_row"."member_id"
2859 AND "unit_id" = "unit_id_v";
2860 END IF;
2861 ELSE
2862 SELECT * INTO "delegation_row" FROM "delegation"
2863 WHERE "truster_id" = "output_row"."member_id"
2864 AND (
2865 "unit_id" = "unit_id_v" OR
2866 "area_id" = "area_id_v"
2867 )
2868 ORDER BY "scope" DESC;
2869 END IF;
2870 ELSIF "scope_v" = 'issue' THEN
2871 IF "issue_row"."fully_frozen" ISNULL THEN
2872 "output_row"."participation" := EXISTS (
2873 SELECT NULL FROM "interest"
2874 WHERE "issue_id" = "issue_id_p"
2875 AND "member_id" = "output_row"."member_id"
2876 );
2877 ELSE
2878 IF "output_row"."member_id" = "member_id_p" THEN
2879 "output_row"."participation" := EXISTS (
2880 SELECT NULL FROM "direct_voter"
2881 WHERE "issue_id" = "issue_id_p"
2882 AND "member_id" = "output_row"."member_id"
2883 );
2884 ELSE
2885 "output_row"."participation" := NULL;
2886 END IF;
2887 END IF;
2888 IF "simulate_here_v" THEN
2889 IF "simulate_trustee_id_p" ISNULL THEN
2890 SELECT * INTO "delegation_row" FROM "delegation"
2891 WHERE "truster_id" = "output_row"."member_id"
2892 AND (
2893 "unit_id" = "unit_id_v" OR
2894 "area_id" = "area_id_v"
2895 )
2896 ORDER BY "scope" DESC;
2897 END IF;
2898 ELSE
2899 SELECT * INTO "delegation_row" FROM "delegation"
2900 WHERE "truster_id" = "output_row"."member_id"
2901 AND (
2902 "unit_id" = "unit_id_v" OR
2903 "area_id" = "area_id_v" OR
2904 "issue_id" = "issue_id_p"
2905 )
2906 ORDER BY "scope" DESC;
2907 END IF;
2908 END IF;
2909 ELSE
2910 "output_row"."participation" := FALSE;
2911 END IF;
2912 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
2913 "output_row"."scope_out" := "scope_v";
2914 "output_rows" := "output_rows" || "output_row";
2915 "output_row"."member_id" := "simulate_trustee_id_p";
2916 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2917 "output_row"."scope_out" := "delegation_row"."scope";
2918 "output_rows" := "output_rows" || "output_row";
2919 "output_row"."member_id" := "delegation_row"."trustee_id";
2920 ELSIF "delegation_row"."scope" NOTNULL THEN
2921 "output_row"."scope_out" := "delegation_row"."scope";
2922 "output_row"."disabled_out" := TRUE;
2923 "output_rows" := "output_rows" || "output_row";
2924 EXIT;
2925 ELSE
2926 "output_row"."scope_out" := NULL;
2927 "output_rows" := "output_rows" || "output_row";
2928 EXIT;
2929 END IF;
2930 EXIT WHEN "loop_member_id_v" NOTNULL;
2931 "output_row"."index" := "output_row"."index" + 1;
2932 END LOOP;
2933 "row_count" := array_upper("output_rows", 1);
2934 "i" := 1;
2935 "loop_v" := FALSE;
2936 LOOP
2937 "output_row" := "output_rows"["i"];
2938 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2939 IF "loop_v" THEN
2940 IF "i" + 1 = "row_count" THEN
2941 "output_row"."loop" := 'last';
2942 ELSIF "i" = "row_count" THEN
2943 "output_row"."loop" := 'repetition';
2944 ELSE
2945 "output_row"."loop" := 'intermediate';
2946 END IF;
2947 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2948 "output_row"."loop" := 'first';
2949 "loop_v" := TRUE;
2950 END IF;
2951 IF "scope_v" = 'unit' THEN
2952 "output_row"."participation" := NULL;
2953 END IF;
2954 RETURN NEXT "output_row";
2955 "i" := "i" + 1;
2956 END LOOP;
2957 RETURN;
2958 END;
2959 $$;
2961 COMMENT ON FUNCTION "delegation_chain"
2962 ( "member"."id"%TYPE,
2963 "unit"."id"%TYPE,
2964 "area"."id"%TYPE,
2965 "issue"."id"%TYPE,
2966 "member"."id"%TYPE,
2967 BOOLEAN )
2968 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
2972 ---------------------------------------------------------
2973 -- Single row returning function for delegation chains --
2974 ---------------------------------------------------------
2977 CREATE TYPE "delegation_info_loop_type" AS ENUM
2978 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
2980 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''';
2983 CREATE TYPE "delegation_info_type" AS (
2984 "own_participation" BOOLEAN,
2985 "own_delegation_scope" "delegation_scope",
2986 "first_trustee_id" INT4,
2987 "first_trustee_participation" BOOLEAN,
2988 "first_trustee_ellipsis" BOOLEAN,
2989 "other_trustee_id" INT4,
2990 "other_trustee_participation" BOOLEAN,
2991 "other_trustee_ellipsis" BOOLEAN,
2992 "delegation_loop" "delegation_info_loop_type",
2993 "participating_member_id" INT4 );
2995 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';
2997 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
2998 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
2999 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
3000 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
3001 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
3002 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
3003 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)';
3004 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
3005 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';
3006 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';
3009 CREATE FUNCTION "delegation_info"
3010 ( "member_id_p" "member"."id"%TYPE,
3011 "unit_id_p" "unit"."id"%TYPE,
3012 "area_id_p" "area"."id"%TYPE,
3013 "issue_id_p" "issue"."id"%TYPE,
3014 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
3015 "simulate_default_p" BOOLEAN DEFAULT FALSE )
3016 RETURNS "delegation_info_type"
3017 LANGUAGE 'plpgsql' STABLE AS $$
3018 DECLARE
3019 "current_row" "delegation_chain_row";
3020 "result" "delegation_info_type";
3021 BEGIN
3022 "result"."own_participation" := FALSE;
3023 FOR "current_row" IN
3024 SELECT * FROM "delegation_chain"(
3025 "member_id_p",
3026 "unit_id_p", "area_id_p", "issue_id_p",
3027 "simulate_trustee_id_p", "simulate_default_p")
3028 LOOP
3029 IF
3030 "result"."participating_member_id" ISNULL AND
3031 "current_row"."participation"
3032 THEN
3033 "result"."participating_member_id" := "current_row"."member_id";
3034 END IF;
3035 IF "current_row"."member_id" = "member_id_p" THEN
3036 "result"."own_participation" := "current_row"."participation";
3037 "result"."own_delegation_scope" := "current_row"."scope_out";
3038 IF "current_row"."loop" = 'first' THEN
3039 "result"."delegation_loop" := 'own';
3040 END IF;
3041 ELSIF
3042 "current_row"."member_valid" AND
3043 ( "current_row"."loop" ISNULL OR
3044 "current_row"."loop" != 'repetition' )
3045 THEN
3046 IF "result"."first_trustee_id" ISNULL THEN
3047 "result"."first_trustee_id" := "current_row"."member_id";
3048 "result"."first_trustee_participation" := "current_row"."participation";
3049 "result"."first_trustee_ellipsis" := FALSE;
3050 IF "current_row"."loop" = 'first' THEN
3051 "result"."delegation_loop" := 'first';
3052 END IF;
3053 ELSIF "result"."other_trustee_id" ISNULL THEN
3054 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
3055 "result"."other_trustee_id" := "current_row"."member_id";
3056 "result"."other_trustee_participation" := TRUE;
3057 "result"."other_trustee_ellipsis" := FALSE;
3058 IF "current_row"."loop" = 'first' THEN
3059 "result"."delegation_loop" := 'other';
3060 END IF;
3061 ELSE
3062 "result"."first_trustee_ellipsis" := TRUE;
3063 IF "current_row"."loop" = 'first' THEN
3064 "result"."delegation_loop" := 'first_ellipsis';
3065 END IF;
3066 END IF;
3067 ELSE
3068 "result"."other_trustee_ellipsis" := TRUE;
3069 IF "current_row"."loop" = 'first' THEN
3070 "result"."delegation_loop" := 'other_ellipsis';
3071 END IF;
3072 END IF;
3073 END IF;
3074 END LOOP;
3075 RETURN "result";
3076 END;
3077 $$;
3079 COMMENT ON FUNCTION "delegation_info"
3080 ( "member"."id"%TYPE,
3081 "unit"."id"%TYPE,
3082 "area"."id"%TYPE,
3083 "issue"."id"%TYPE,
3084 "member"."id"%TYPE,
3085 BOOLEAN )
3086 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
3090 ---------------------------
3091 -- Transaction isolation --
3092 ---------------------------
3095 CREATE FUNCTION "require_transaction_isolation"()
3096 RETURNS VOID
3097 LANGUAGE 'plpgsql' VOLATILE AS $$
3098 BEGIN
3099 IF
3100 current_setting('transaction_isolation') NOT IN
3101 ('repeatable read', 'serializable')
3102 THEN
3103 RAISE EXCEPTION 'Insufficient transaction isolation level' USING
3104 HINT = 'Consider using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ.';
3105 END IF;
3106 RETURN;
3107 END;
3108 $$;
3110 COMMENT ON FUNCTION "require_transaction_isolation"() IS 'Throws an exception, if transaction isolation level is too low to provide a consistent snapshot';
3113 CREATE FUNCTION "dont_require_transaction_isolation"()
3114 RETURNS VOID
3115 LANGUAGE 'plpgsql' VOLATILE AS $$
3116 BEGIN
3117 IF
3118 current_setting('transaction_isolation') IN
3119 ('repeatable read', 'serializable')
3120 THEN
3121 RAISE WARNING 'Unneccessary transaction isolation level: %',
3122 current_setting('transaction_isolation');
3123 END IF;
3124 RETURN;
3125 END;
3126 $$;
3128 COMMENT ON FUNCTION "dont_require_transaction_isolation"() IS 'Raises a warning, if transaction isolation level is higher than READ COMMITTED';
3132 -------------------------
3133 -- Notification system --
3134 -------------------------
3136 CREATE FUNCTION "get_initiatives_for_notification"
3137 ( "member_id_p" "member"."id"%TYPE )
3138 RETURNS SETOF "initiative_for_notification"
3139 LANGUAGE 'plpgsql' VOLATILE AS $$
3140 DECLARE
3141 "result_row" "initiative_for_notification"%ROWTYPE;
3142 "last_draft_id_v" "draft"."id"%TYPE;
3143 "last_suggestion_id_v" "suggestion"."id"%TYPE;
3144 BEGIN
3145 PERFORM "require_transaction_isolation"();
3146 PERFORM NULL FROM "member" WHERE "id" = "member_id_p" FOR UPDATE;
3147 FOR "result_row" IN
3148 SELECT * FROM "initiative_for_notification"
3149 WHERE "seen_by_member_id" = "member_id_p"
3150 LOOP
3151 SELECT "id" INTO "last_draft_id_v" FROM "draft"
3152 WHERE "draft"."initiative_id" = "result_row"."id"
3153 ORDER BY "id" DESC LIMIT 1;
3154 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
3155 WHERE "suggestion"."initiative_id" = "result_row"."id"
3156 ORDER BY "id" DESC LIMIT 1;
3157 INSERT INTO "initiative_notification_sent"
3158 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
3159 VALUES (
3160 "member_id_p",
3161 "result_row"."id",
3162 "last_draft_id_v",
3163 "last_suggestion_id_v" )
3164 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
3165 "last_draft_id" = CASE
3166 WHEN "initiative_notification_sent"."last_draft_id" > "last_draft_id_v"
3167 THEN "initiative_notification_sent"."last_draft_id"
3168 ELSE "last_draft_id_v"
3169 END,
3170 "last_suggestion_id" = CASE
3171 WHEN "initiative_notification_sent"."last_suggestion_id" > "last_suggestion_id_v"
3172 THEN "initiative_notification_sent"."last_suggestion_id"
3173 ELSE "last_suggestion_id_v"
3174 END;
3175 RETURN NEXT "result_row";
3176 END LOOP;
3177 DELETE FROM "initiative_notification_sent"
3178 USING "initiative", "issue"
3179 WHERE "initiative_notification_sent"."member_id" = "member_id_p"
3180 AND "initiative"."id" = "initiative_notification_sent"."initiative_id"
3181 AND "issue"."id" = "initiative"."issue_id"
3182 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
3183 UPDATE "member" SET "notification_counter" = "notification_counter" + 1
3184 WHERE "id" = "member_id_p";
3185 RETURN;
3186 END;
3187 $$;
3191 ------------------------------------------------------------------------
3192 -- Regular tasks, except calculcation of snapshots and voting results --
3193 ------------------------------------------------------------------------
3196 CREATE FUNCTION "check_activity"()
3197 RETURNS VOID
3198 LANGUAGE 'plpgsql' VOLATILE AS $$
3199 DECLARE
3200 "system_setting_row" "system_setting"%ROWTYPE;
3201 BEGIN
3202 PERFORM "dont_require_transaction_isolation"();
3203 SELECT * INTO "system_setting_row" FROM "system_setting";
3204 IF "system_setting_row"."member_ttl" NOTNULL THEN
3205 UPDATE "member" SET "active" = FALSE
3206 WHERE "active" = TRUE
3207 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
3208 END IF;
3209 RETURN;
3210 END;
3211 $$;
3213 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
3216 CREATE FUNCTION "calculate_member_counts"()
3217 RETURNS VOID
3218 LANGUAGE 'plpgsql' VOLATILE AS $$
3219 BEGIN
3220 PERFORM "require_transaction_isolation"();
3221 DELETE FROM "member_count";
3222 INSERT INTO "member_count" ("total_count")
3223 SELECT "total_count" FROM "member_count_view";
3224 UPDATE "unit" SET "member_count" = "view"."member_count"
3225 FROM "unit_member_count" AS "view"
3226 WHERE "view"."unit_id" = "unit"."id";
3227 UPDATE "area" SET
3228 "direct_member_count" = "view"."direct_member_count",
3229 "member_weight" = "view"."member_weight"
3230 FROM "area_member_count" AS "view"
3231 WHERE "view"."area_id" = "area"."id";
3232 RETURN;
3233 END;
3234 $$;
3236 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"';
3240 ------------------------------------
3241 -- Calculation of harmonic weight --
3242 ------------------------------------
3245 CREATE VIEW "remaining_harmonic_supporter_weight" AS
3246 SELECT
3247 "direct_interest_snapshot"."issue_id",
3248 "direct_interest_snapshot"."event",
3249 "direct_interest_snapshot"."member_id",
3250 "direct_interest_snapshot"."weight" AS "weight_num",
3251 count("initiative"."id") AS "weight_den"
3252 FROM "issue"
3253 JOIN "direct_interest_snapshot"
3254 ON "issue"."id" = "direct_interest_snapshot"."issue_id"
3255 AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
3256 JOIN "initiative"
3257 ON "issue"."id" = "initiative"."issue_id"
3258 AND "initiative"."harmonic_weight" ISNULL
3259 JOIN "direct_supporter_snapshot"
3260 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
3261 AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event"
3262 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
3263 AND (
3264 "direct_supporter_snapshot"."satisfied" = TRUE OR
3265 coalesce("initiative"."admitted", FALSE) = FALSE
3266 )
3267 GROUP BY
3268 "direct_interest_snapshot"."issue_id",
3269 "direct_interest_snapshot"."event",
3270 "direct_interest_snapshot"."member_id",
3271 "direct_interest_snapshot"."weight";
3273 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
3276 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
3277 SELECT
3278 "initiative"."issue_id",
3279 "initiative"."id" AS "initiative_id",
3280 "initiative"."admitted",
3281 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
3282 "remaining_harmonic_supporter_weight"."weight_den"
3283 FROM "remaining_harmonic_supporter_weight"
3284 JOIN "initiative"
3285 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
3286 AND "initiative"."harmonic_weight" ISNULL
3287 JOIN "direct_supporter_snapshot"
3288 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
3289 AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"
3290 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
3291 AND (
3292 "direct_supporter_snapshot"."satisfied" = TRUE OR
3293 coalesce("initiative"."admitted", FALSE) = FALSE
3294 )
3295 GROUP BY
3296 "initiative"."issue_id",
3297 "initiative"."id",
3298 "initiative"."admitted",
3299 "remaining_harmonic_supporter_weight"."weight_den";
3301 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
3304 CREATE VIEW "remaining_harmonic_initiative_weight_dummies" AS
3305 SELECT
3306 "issue_id",
3307 "id" AS "initiative_id",
3308 "admitted",
3309 0 AS "weight_num",
3310 1 AS "weight_den"
3311 FROM "initiative"
3312 WHERE "harmonic_weight" ISNULL;
3314 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';
3317 CREATE FUNCTION "set_harmonic_initiative_weights"
3318 ( "issue_id_p" "issue"."id"%TYPE )
3319 RETURNS VOID
3320 LANGUAGE 'plpgsql' VOLATILE AS $$
3321 DECLARE
3322 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
3323 "i" INT4;
3324 "count_v" INT4;
3325 "summand_v" FLOAT;
3326 "id_ary" INT4[];
3327 "weight_ary" FLOAT[];
3328 "min_weight_v" FLOAT;
3329 BEGIN
3330 PERFORM "require_transaction_isolation"();
3331 UPDATE "initiative" SET "harmonic_weight" = NULL
3332 WHERE "issue_id" = "issue_id_p";
3333 LOOP
3334 "min_weight_v" := NULL;
3335 "i" := 0;
3336 "count_v" := 0;
3337 FOR "weight_row" IN
3338 SELECT * FROM "remaining_harmonic_initiative_weight_summands"
3339 WHERE "issue_id" = "issue_id_p"
3340 AND (
3341 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
3342 SELECT NULL FROM "initiative"
3343 WHERE "issue_id" = "issue_id_p"
3344 AND "harmonic_weight" ISNULL
3345 AND coalesce("admitted", FALSE) = FALSE
3346 )
3347 )
3348 UNION ALL -- needed for corner cases
3349 SELECT * FROM "remaining_harmonic_initiative_weight_dummies"
3350 WHERE "issue_id" = "issue_id_p"
3351 AND (
3352 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
3353 SELECT NULL FROM "initiative"
3354 WHERE "issue_id" = "issue_id_p"
3355 AND "harmonic_weight" ISNULL
3356 AND coalesce("admitted", FALSE) = FALSE
3357 )
3358 )
3359 ORDER BY "initiative_id" DESC, "weight_den" DESC
3360 -- NOTE: non-admitted initiatives placed first (at last positions),
3361 -- latest initiatives treated worse in case of tie
3362 LOOP
3363 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
3364 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
3365 "i" := "i" + 1;
3366 "count_v" := "i";
3367 "id_ary"["i"] := "weight_row"."initiative_id";
3368 "weight_ary"["i"] := "summand_v";
3369 ELSE
3370 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
3371 END IF;
3372 END LOOP;
3373 EXIT WHEN "count_v" = 0;
3374 "i" := 1;
3375 LOOP
3376 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
3377 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
3378 "min_weight_v" := "weight_ary"["i"];
3379 END IF;
3380 "i" := "i" + 1;
3381 EXIT WHEN "i" > "count_v";
3382 END LOOP;
3383 "i" := 1;
3384 LOOP
3385 IF "weight_ary"["i"] = "min_weight_v" THEN
3386 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
3387 WHERE "id" = "id_ary"["i"];
3388 EXIT;
3389 END IF;
3390 "i" := "i" + 1;
3391 END LOOP;
3392 END LOOP;
3393 UPDATE "initiative" SET "harmonic_weight" = 0
3394 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
3395 END;
3396 $$;
3398 COMMENT ON FUNCTION "set_harmonic_initiative_weights"
3399 ( "issue"."id"%TYPE )
3400 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
3404 ------------------------------
3405 -- Calculation of snapshots --
3406 ------------------------------
3409 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
3410 ( "issue_id_p" "issue"."id"%TYPE,
3411 "member_id_p" "member"."id"%TYPE,
3412 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3413 RETURNS "direct_population_snapshot"."weight"%TYPE
3414 LANGUAGE 'plpgsql' VOLATILE AS $$
3415 DECLARE
3416 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3417 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
3418 "weight_v" INT4;
3419 "sub_weight_v" INT4;
3420 BEGIN
3421 PERFORM "require_transaction_isolation"();
3422 "weight_v" := 0;
3423 FOR "issue_delegation_row" IN
3424 SELECT * FROM "issue_delegation"
3425 WHERE "trustee_id" = "member_id_p"
3426 AND "issue_id" = "issue_id_p"
3427 LOOP
3428 IF NOT EXISTS (
3429 SELECT NULL FROM "direct_population_snapshot"
3430 WHERE "issue_id" = "issue_id_p"
3431 AND "event" = 'periodic'
3432 AND "member_id" = "issue_delegation_row"."truster_id"
3433 ) AND NOT EXISTS (
3434 SELECT NULL FROM "delegating_population_snapshot"
3435 WHERE "issue_id" = "issue_id_p"
3436 AND "event" = 'periodic'
3437 AND "member_id" = "issue_delegation_row"."truster_id"
3438 ) THEN
3439 "delegate_member_ids_v" :=
3440 "member_id_p" || "delegate_member_ids_p";
3441 INSERT INTO "delegating_population_snapshot" (
3442 "issue_id",
3443 "event",
3444 "member_id",
3445 "scope",
3446 "delegate_member_ids"
3447 ) VALUES (
3448 "issue_id_p",
3449 'periodic',
3450 "issue_delegation_row"."truster_id",
3451 "issue_delegation_row"."scope",
3452 "delegate_member_ids_v"
3453 );
3454 "sub_weight_v" := 1 +
3455 "weight_of_added_delegations_for_population_snapshot"(
3456 "issue_id_p",
3457 "issue_delegation_row"."truster_id",
3458 "delegate_member_ids_v"
3459 );
3460 UPDATE "delegating_population_snapshot"
3461 SET "weight" = "sub_weight_v"
3462 WHERE "issue_id" = "issue_id_p"
3463 AND "event" = 'periodic'
3464 AND "member_id" = "issue_delegation_row"."truster_id";
3465 "weight_v" := "weight_v" + "sub_weight_v";
3466 END IF;
3467 END LOOP;
3468 RETURN "weight_v";
3469 END;
3470 $$;
3472 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
3473 ( "issue"."id"%TYPE,
3474 "member"."id"%TYPE,
3475 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3476 IS 'Helper function for "create_population_snapshot" function';
3479 CREATE FUNCTION "create_population_snapshot"
3480 ( "issue_id_p" "issue"."id"%TYPE )
3481 RETURNS VOID
3482 LANGUAGE 'plpgsql' VOLATILE AS $$
3483 DECLARE
3484 "member_id_v" "member"."id"%TYPE;
3485 BEGIN
3486 PERFORM "require_transaction_isolation"();
3487 DELETE FROM "direct_population_snapshot"
3488 WHERE "issue_id" = "issue_id_p"
3489 AND "event" = 'periodic';
3490 DELETE FROM "delegating_population_snapshot"
3491 WHERE "issue_id" = "issue_id_p"
3492 AND "event" = 'periodic';
3493 INSERT INTO "direct_population_snapshot"
3494 ("issue_id", "event", "member_id")
3495 SELECT
3496 "issue_id_p" AS "issue_id",
3497 'periodic'::"snapshot_event" AS "event",
3498 "member"."id" AS "member_id"
3499 FROM "issue"
3500 JOIN "area" ON "issue"."area_id" = "area"."id"
3501 JOIN "membership" ON "area"."id" = "membership"."area_id"
3502 JOIN "member" ON "membership"."member_id" = "member"."id"
3503 JOIN "privilege"
3504 ON "privilege"."unit_id" = "area"."unit_id"
3505 AND "privilege"."member_id" = "member"."id"
3506 WHERE "issue"."id" = "issue_id_p"
3507 AND "member"."active" AND "privilege"."voting_right"
3508 UNION
3509 SELECT
3510 "issue_id_p" AS "issue_id",
3511 'periodic'::"snapshot_event" AS "event",
3512 "member"."id" AS "member_id"
3513 FROM "issue"
3514 JOIN "area" ON "issue"."area_id" = "area"."id"
3515 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3516 JOIN "member" ON "interest"."member_id" = "member"."id"
3517 JOIN "privilege"
3518 ON "privilege"."unit_id" = "area"."unit_id"
3519 AND "privilege"."member_id" = "member"."id"
3520 WHERE "issue"."id" = "issue_id_p"
3521 AND "member"."active" AND "privilege"."voting_right";
3522 FOR "member_id_v" IN
3523 SELECT "member_id" FROM "direct_population_snapshot"
3524 WHERE "issue_id" = "issue_id_p"
3525 AND "event" = 'periodic'
3526 LOOP
3527 UPDATE "direct_population_snapshot" SET
3528 "weight" = 1 +
3529 "weight_of_added_delegations_for_population_snapshot"(
3530 "issue_id_p",
3531 "member_id_v",
3532 '{}'
3533 )
3534 WHERE "issue_id" = "issue_id_p"
3535 AND "event" = 'periodic'
3536 AND "member_id" = "member_id_v";
3537 END LOOP;
3538 RETURN;
3539 END;
3540 $$;
3542 COMMENT ON FUNCTION "create_population_snapshot"
3543 ( "issue"."id"%TYPE )
3544 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.';
3547 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3548 ( "issue_id_p" "issue"."id"%TYPE,
3549 "member_id_p" "member"."id"%TYPE,
3550 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3551 RETURNS "direct_interest_snapshot"."weight"%TYPE
3552 LANGUAGE 'plpgsql' VOLATILE AS $$
3553 DECLARE
3554 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3555 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
3556 "weight_v" INT4;
3557 "sub_weight_v" INT4;
3558 BEGIN
3559 PERFORM "require_transaction_isolation"();
3560 "weight_v" := 0;
3561 FOR "issue_delegation_row" IN
3562 SELECT * FROM "issue_delegation"
3563 WHERE "trustee_id" = "member_id_p"
3564 AND "issue_id" = "issue_id_p"
3565 LOOP
3566 IF NOT EXISTS (
3567 SELECT NULL FROM "direct_interest_snapshot"
3568 WHERE "issue_id" = "issue_id_p"
3569 AND "event" = 'periodic'
3570 AND "member_id" = "issue_delegation_row"."truster_id"
3571 ) AND NOT EXISTS (
3572 SELECT NULL FROM "delegating_interest_snapshot"
3573 WHERE "issue_id" = "issue_id_p"
3574 AND "event" = 'periodic'
3575 AND "member_id" = "issue_delegation_row"."truster_id"
3576 ) THEN
3577 "delegate_member_ids_v" :=
3578 "member_id_p" || "delegate_member_ids_p";
3579 INSERT INTO "delegating_interest_snapshot" (
3580 "issue_id",
3581 "event",
3582 "member_id",
3583 "scope",
3584 "delegate_member_ids"
3585 ) VALUES (
3586 "issue_id_p",
3587 'periodic',
3588 "issue_delegation_row"."truster_id",
3589 "issue_delegation_row"."scope",
3590 "delegate_member_ids_v"
3591 );
3592 "sub_weight_v" := 1 +
3593 "weight_of_added_delegations_for_interest_snapshot"(
3594 "issue_id_p",
3595 "issue_delegation_row"."truster_id",
3596 "delegate_member_ids_v"
3597 );
3598 UPDATE "delegating_interest_snapshot"
3599 SET "weight" = "sub_weight_v"
3600 WHERE "issue_id" = "issue_id_p"
3601 AND "event" = 'periodic'
3602 AND "member_id" = "issue_delegation_row"."truster_id";
3603 "weight_v" := "weight_v" + "sub_weight_v";
3604 END IF;
3605 END LOOP;
3606 RETURN "weight_v";
3607 END;
3608 $$;
3610 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3611 ( "issue"."id"%TYPE,
3612 "member"."id"%TYPE,
3613 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3614 IS 'Helper function for "create_interest_snapshot" function';
3617 CREATE FUNCTION "create_interest_snapshot"
3618 ( "issue_id_p" "issue"."id"%TYPE )
3619 RETURNS VOID
3620 LANGUAGE 'plpgsql' VOLATILE AS $$
3621 DECLARE
3622 "member_id_v" "member"."id"%TYPE;
3623 BEGIN
3624 PERFORM "require_transaction_isolation"();
3625 DELETE FROM "direct_interest_snapshot"
3626 WHERE "issue_id" = "issue_id_p"
3627 AND "event" = 'periodic';
3628 DELETE FROM "delegating_interest_snapshot"
3629 WHERE "issue_id" = "issue_id_p"
3630 AND "event" = 'periodic';
3631 DELETE FROM "direct_supporter_snapshot"
3632 USING "initiative" -- NOTE: due to missing index on issue_id
3633 WHERE "initiative"."issue_id" = "issue_id_p"
3634 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
3635 AND "direct_supporter_snapshot"."event" = 'periodic';
3636 INSERT INTO "direct_interest_snapshot"
3637 ("issue_id", "event", "member_id")
3638 SELECT
3639 "issue_id_p" AS "issue_id",
3640 'periodic' AS "event",
3641 "member"."id" AS "member_id"
3642 FROM "issue"
3643 JOIN "area" ON "issue"."area_id" = "area"."id"
3644 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3645 JOIN "member" ON "interest"."member_id" = "member"."id"
3646 JOIN "privilege"
3647 ON "privilege"."unit_id" = "area"."unit_id"
3648 AND "privilege"."member_id" = "member"."id"
3649 WHERE "issue"."id" = "issue_id_p"
3650 AND "member"."active" AND "privilege"."voting_right";
3651 FOR "member_id_v" IN
3652 SELECT "member_id" FROM "direct_interest_snapshot"
3653 WHERE "issue_id" = "issue_id_p"
3654 AND "event" = 'periodic'
3655 LOOP
3656 UPDATE "direct_interest_snapshot" SET
3657 "weight" = 1 +
3658 "weight_of_added_delegations_for_interest_snapshot"(
3659 "issue_id_p",
3660 "member_id_v",
3661 '{}'
3662 )
3663 WHERE "issue_id" = "issue_id_p"
3664 AND "event" = 'periodic'
3665 AND "member_id" = "member_id_v";
3666 END LOOP;
3667 INSERT INTO "direct_supporter_snapshot"
3668 ( "issue_id", "initiative_id", "event", "member_id",
3669 "draft_id", "informed", "satisfied" )
3670 SELECT
3671 "issue_id_p" AS "issue_id",
3672 "initiative"."id" AS "initiative_id",
3673 'periodic' AS "event",
3674 "supporter"."member_id" AS "member_id",
3675 "supporter"."draft_id" AS "draft_id",
3676 "supporter"."draft_id" = "current_draft"."id" AS "informed",
3677 NOT EXISTS (
3678 SELECT NULL FROM "critical_opinion"
3679 WHERE "initiative_id" = "initiative"."id"
3680 AND "member_id" = "supporter"."member_id"
3681 ) AS "satisfied"
3682 FROM "initiative"
3683 JOIN "supporter"
3684 ON "supporter"."initiative_id" = "initiative"."id"
3685 JOIN "current_draft"
3686 ON "initiative"."id" = "current_draft"."initiative_id"
3687 JOIN "direct_interest_snapshot"
3688 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
3689 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
3690 AND "event" = 'periodic'
3691 WHERE "initiative"."issue_id" = "issue_id_p";
3692 RETURN;
3693 END;
3694 $$;
3696 COMMENT ON FUNCTION "create_interest_snapshot"
3697 ( "issue"."id"%TYPE )
3698 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.';
3701 CREATE FUNCTION "create_snapshot"
3702 ( "issue_id_p" "issue"."id"%TYPE )
3703 RETURNS VOID
3704 LANGUAGE 'plpgsql' VOLATILE AS $$
3705 DECLARE
3706 "initiative_id_v" "initiative"."id"%TYPE;
3707 "suggestion_id_v" "suggestion"."id"%TYPE;
3708 BEGIN
3709 PERFORM "require_transaction_isolation"();
3710 PERFORM "create_population_snapshot"("issue_id_p");
3711 PERFORM "create_interest_snapshot"("issue_id_p");
3712 UPDATE "issue" SET
3713 "snapshot" = coalesce("phase_finished", now()),
3714 "latest_snapshot_event" = 'periodic',
3715 "population" = (
3716 SELECT coalesce(sum("weight"), 0)
3717 FROM "direct_population_snapshot"
3718 WHERE "issue_id" = "issue_id_p"
3719 AND "event" = 'periodic'
3720 )
3721 WHERE "id" = "issue_id_p";
3722 FOR "initiative_id_v" IN
3723 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
3724 LOOP
3725 UPDATE "initiative" SET
3726 "supporter_count" = (
3727 SELECT coalesce(sum("di"."weight"), 0)
3728 FROM "direct_interest_snapshot" AS "di"
3729 JOIN "direct_supporter_snapshot" AS "ds"
3730 ON "di"."member_id" = "ds"."member_id"
3731 WHERE "di"."issue_id" = "issue_id_p"
3732 AND "di"."event" = 'periodic'
3733 AND "ds"."initiative_id" = "initiative_id_v"
3734 AND "ds"."event" = 'periodic'
3735 ),
3736 "informed_supporter_count" = (
3737 SELECT coalesce(sum("di"."weight"), 0)
3738 FROM "direct_interest_snapshot" AS "di"
3739 JOIN "direct_supporter_snapshot" AS "ds"
3740 ON "di"."member_id" = "ds"."member_id"
3741 WHERE "di"."issue_id" = "issue_id_p"
3742 AND "di"."event" = 'periodic'
3743 AND "ds"."initiative_id" = "initiative_id_v"
3744 AND "ds"."event" = 'periodic'
3745 AND "ds"."informed"
3746 ),
3747 "satisfied_supporter_count" = (
3748 SELECT coalesce(sum("di"."weight"), 0)
3749 FROM "direct_interest_snapshot" AS "di"
3750 JOIN "direct_supporter_snapshot" AS "ds"
3751 ON "di"."member_id" = "ds"."member_id"
3752 WHERE "di"."issue_id" = "issue_id_p"
3753 AND "di"."event" = 'periodic'
3754 AND "ds"."initiative_id" = "initiative_id_v"
3755 AND "ds"."event" = 'periodic'
3756 AND "ds"."satisfied"
3757 ),
3758 "satisfied_informed_supporter_count" = (
3759 SELECT coalesce(sum("di"."weight"), 0)
3760 FROM "direct_interest_snapshot" AS "di"
3761 JOIN "direct_supporter_snapshot" AS "ds"
3762 ON "di"."member_id" = "ds"."member_id"
3763 WHERE "di"."issue_id" = "issue_id_p"
3764 AND "di"."event" = 'periodic'
3765 AND "ds"."initiative_id" = "initiative_id_v"
3766 AND "ds"."event" = 'periodic'
3767 AND "ds"."informed"
3768 AND "ds"."satisfied"
3769 )
3770 WHERE "id" = "initiative_id_v";
3771 FOR "suggestion_id_v" IN
3772 SELECT "id" FROM "suggestion"
3773 WHERE "initiative_id" = "initiative_id_v"
3774 LOOP
3775 UPDATE "suggestion" SET
3776 "minus2_unfulfilled_count" = (
3777 SELECT coalesce(sum("snapshot"."weight"), 0)
3778 FROM "issue" CROSS JOIN "opinion"
3779 JOIN "direct_interest_snapshot" AS "snapshot"
3780 ON "snapshot"."issue_id" = "issue"."id"
3781 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3782 AND "snapshot"."member_id" = "opinion"."member_id"
3783 WHERE "issue"."id" = "issue_id_p"
3784 AND "opinion"."suggestion_id" = "suggestion_id_v"
3785 AND "opinion"."degree" = -2
3786 AND "opinion"."fulfilled" = FALSE
3787 ),
3788 "minus2_fulfilled_count" = (
3789 SELECT coalesce(sum("snapshot"."weight"), 0)
3790 FROM "issue" CROSS JOIN "opinion"
3791 JOIN "direct_interest_snapshot" AS "snapshot"
3792 ON "snapshot"."issue_id" = "issue"."id"
3793 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3794 AND "snapshot"."member_id" = "opinion"."member_id"
3795 WHERE "issue"."id" = "issue_id_p"
3796 AND "opinion"."suggestion_id" = "suggestion_id_v"
3797 AND "opinion"."degree" = -2
3798 AND "opinion"."fulfilled" = TRUE
3799 ),
3800 "minus1_unfulfilled_count" = (
3801 SELECT coalesce(sum("snapshot"."weight"), 0)
3802 FROM "issue" CROSS JOIN "opinion"
3803 JOIN "direct_interest_snapshot" AS "snapshot"
3804 ON "snapshot"."issue_id" = "issue"."id"
3805 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3806 AND "snapshot"."member_id" = "opinion"."member_id"
3807 WHERE "issue"."id" = "issue_id_p"
3808 AND "opinion"."suggestion_id" = "suggestion_id_v"
3809 AND "opinion"."degree" = -1
3810 AND "opinion"."fulfilled" = FALSE
3811 ),
3812 "minus1_fulfilled_count" = (
3813 SELECT coalesce(sum("snapshot"."weight"), 0)
3814 FROM "issue" CROSS JOIN "opinion"
3815 JOIN "direct_interest_snapshot" AS "snapshot"
3816 ON "snapshot"."issue_id" = "issue"."id"
3817 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3818 AND "snapshot"."member_id" = "opinion"."member_id"
3819 WHERE "issue"."id" = "issue_id_p"
3820 AND "opinion"."suggestion_id" = "suggestion_id_v"
3821 AND "opinion"."degree" = -1
3822 AND "opinion"."fulfilled" = TRUE
3823 ),
3824 "plus1_unfulfilled_count" = (
3825 SELECT coalesce(sum("snapshot"."weight"), 0)
3826 FROM "issue" CROSS JOIN "opinion"
3827 JOIN "direct_interest_snapshot" AS "snapshot"
3828 ON "snapshot"."issue_id" = "issue"."id"
3829 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3830 AND "snapshot"."member_id" = "opinion"."member_id"
3831 WHERE "issue"."id" = "issue_id_p"
3832 AND "opinion"."suggestion_id" = "suggestion_id_v"
3833 AND "opinion"."degree" = 1
3834 AND "opinion"."fulfilled" = FALSE
3835 ),
3836 "plus1_fulfilled_count" = (
3837 SELECT coalesce(sum("snapshot"."weight"), 0)
3838 FROM "issue" CROSS JOIN "opinion"
3839 JOIN "direct_interest_snapshot" AS "snapshot"
3840 ON "snapshot"."issue_id" = "issue"."id"
3841 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3842 AND "snapshot"."member_id" = "opinion"."member_id"
3843 WHERE "issue"."id" = "issue_id_p"
3844 AND "opinion"."suggestion_id" = "suggestion_id_v"
3845 AND "opinion"."degree" = 1
3846 AND "opinion"."fulfilled" = TRUE
3847 ),
3848 "plus2_unfulfilled_count" = (
3849 SELECT coalesce(sum("snapshot"."weight"), 0)
3850 FROM "issue" CROSS JOIN "opinion"
3851 JOIN "direct_interest_snapshot" AS "snapshot"
3852 ON "snapshot"."issue_id" = "issue"."id"
3853 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3854 AND "snapshot"."member_id" = "opinion"."member_id"
3855 WHERE "issue"."id" = "issue_id_p"
3856 AND "opinion"."suggestion_id" = "suggestion_id_v"
3857 AND "opinion"."degree" = 2
3858 AND "opinion"."fulfilled" = FALSE
3859 ),
3860 "plus2_fulfilled_count" = (
3861 SELECT coalesce(sum("snapshot"."weight"), 0)
3862 FROM "issue" CROSS JOIN "opinion"
3863 JOIN "direct_interest_snapshot" AS "snapshot"
3864 ON "snapshot"."issue_id" = "issue"."id"
3865 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3866 AND "snapshot"."member_id" = "opinion"."member_id"
3867 WHERE "issue"."id" = "issue_id_p"
3868 AND "opinion"."suggestion_id" = "suggestion_id_v"
3869 AND "opinion"."degree" = 2
3870 AND "opinion"."fulfilled" = TRUE
3871 )
3872 WHERE "suggestion"."id" = "suggestion_id_v";
3873 END LOOP;
3874 END LOOP;
3875 RETURN;
3876 END;
3877 $$;
3879 COMMENT ON FUNCTION "create_snapshot"
3880 ( "issue"."id"%TYPE )
3881 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.';
3884 CREATE FUNCTION "set_snapshot_event"
3885 ( "issue_id_p" "issue"."id"%TYPE,
3886 "event_p" "snapshot_event" )
3887 RETURNS VOID
3888 LANGUAGE 'plpgsql' VOLATILE AS $$
3889 DECLARE
3890 "event_v" "issue"."latest_snapshot_event"%TYPE;
3891 BEGIN
3892 PERFORM "require_transaction_isolation"();
3893 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
3894 WHERE "id" = "issue_id_p" FOR UPDATE;
3895 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
3896 WHERE "id" = "issue_id_p";
3897 UPDATE "direct_population_snapshot" SET "event" = "event_p"
3898 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3899 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
3900 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3901 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
3902 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3903 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
3904 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3905 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
3906 FROM "initiative" -- NOTE: due to missing index on issue_id
3907 WHERE "initiative"."issue_id" = "issue_id_p"
3908 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
3909 AND "direct_supporter_snapshot"."event" = "event_v";
3910 RETURN;
3911 END;
3912 $$;
3914 COMMENT ON FUNCTION "set_snapshot_event"
3915 ( "issue"."id"%TYPE,
3916 "snapshot_event" )
3917 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
3921 -----------------------
3922 -- Counting of votes --
3923 -----------------------
3926 CREATE FUNCTION "weight_of_added_vote_delegations"
3927 ( "issue_id_p" "issue"."id"%TYPE,
3928 "member_id_p" "member"."id"%TYPE,
3929 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
3930 RETURNS "direct_voter"."weight"%TYPE
3931 LANGUAGE 'plpgsql' VOLATILE AS $$
3932 DECLARE
3933 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3934 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
3935 "weight_v" INT4;
3936 "sub_weight_v" INT4;
3937 BEGIN
3938 PERFORM "require_transaction_isolation"();
3939 "weight_v" := 0;
3940 FOR "issue_delegation_row" IN
3941 SELECT * FROM "issue_delegation"
3942 WHERE "trustee_id" = "member_id_p"
3943 AND "issue_id" = "issue_id_p"
3944 LOOP
3945 IF NOT EXISTS (
3946 SELECT NULL FROM "direct_voter"
3947 WHERE "member_id" = "issue_delegation_row"."truster_id"
3948 AND "issue_id" = "issue_id_p"
3949 ) AND NOT EXISTS (
3950 SELECT NULL FROM "delegating_voter"
3951 WHERE "member_id" = "issue_delegation_row"."truster_id"
3952 AND "issue_id" = "issue_id_p"
3953 ) THEN
3954 "delegate_member_ids_v" :=
3955 "member_id_p" || "delegate_member_ids_p";
3956 INSERT INTO "delegating_voter" (
3957 "issue_id",
3958 "member_id",
3959 "scope",
3960 "delegate_member_ids"
3961 ) VALUES (
3962 "issue_id_p",
3963 "issue_delegation_row"."truster_id",
3964 "issue_delegation_row"."scope",
3965 "delegate_member_ids_v"
3966 );
3967 "sub_weight_v" := 1 +
3968 "weight_of_added_vote_delegations"(
3969 "issue_id_p",
3970 "issue_delegation_row"."truster_id",
3971 "delegate_member_ids_v"
3972 );
3973 UPDATE "delegating_voter"
3974 SET "weight" = "sub_weight_v"
3975 WHERE "issue_id" = "issue_id_p"
3976 AND "member_id" = "issue_delegation_row"."truster_id";
3977 "weight_v" := "weight_v" + "sub_weight_v";
3978 END IF;
3979 END LOOP;
3980 RETURN "weight_v";
3981 END;
3982 $$;
3984 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
3985 ( "issue"."id"%TYPE,
3986 "member"."id"%TYPE,
3987 "delegating_voter"."delegate_member_ids"%TYPE )
3988 IS 'Helper function for "add_vote_delegations" function';
3991 CREATE FUNCTION "add_vote_delegations"
3992 ( "issue_id_p" "issue"."id"%TYPE )
3993 RETURNS VOID
3994 LANGUAGE 'plpgsql' VOLATILE AS $$
3995 DECLARE
3996 "member_id_v" "member"."id"%TYPE;
3997 BEGIN
3998 PERFORM "require_transaction_isolation"();
3999 FOR "member_id_v" IN
4000 SELECT "member_id" FROM "direct_voter"
4001 WHERE "issue_id" = "issue_id_p"
4002 LOOP
4003 UPDATE "direct_voter" SET
4004 "weight" = "weight" + "weight_of_added_vote_delegations"(
4005 "issue_id_p",
4006 "member_id_v",
4007 '{}'
4008 )
4009 WHERE "member_id" = "member_id_v"
4010 AND "issue_id" = "issue_id_p";
4011 END LOOP;
4012 RETURN;
4013 END;
4014 $$;
4016 COMMENT ON FUNCTION "add_vote_delegations"
4017 ( "issue_id_p" "issue"."id"%TYPE )
4018 IS 'Helper function for "close_voting" function';
4021 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
4022 RETURNS VOID
4023 LANGUAGE 'plpgsql' VOLATILE AS $$
4024 DECLARE
4025 "area_id_v" "area"."id"%TYPE;
4026 "unit_id_v" "unit"."id"%TYPE;
4027 "member_id_v" "member"."id"%TYPE;
4028 BEGIN
4029 PERFORM "require_transaction_isolation"();
4030 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
4031 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
4032 -- override protection triggers:
4033 INSERT INTO "temporary_transaction_data" ("key", "value")
4034 VALUES ('override_protection_triggers', TRUE::TEXT);
4035 -- delete timestamp of voting comment:
4036 UPDATE "direct_voter" SET "comment_changed" = NULL
4037 WHERE "issue_id" = "issue_id_p";
4038 -- delete delegating votes (in cases of manual reset of issue state):
4039 DELETE FROM "delegating_voter"
4040 WHERE "issue_id" = "issue_id_p";
4041 -- delete votes from non-privileged voters:
4042 DELETE FROM "direct_voter"
4043 USING (
4044 SELECT
4045 "direct_voter"."member_id"
4046 FROM "direct_voter"
4047 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
4048 LEFT JOIN "privilege"
4049 ON "privilege"."unit_id" = "unit_id_v"
4050 AND "privilege"."member_id" = "direct_voter"."member_id"
4051 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
4052 "member"."active" = FALSE OR
4053 "privilege"."voting_right" ISNULL OR
4054 "privilege"."voting_right" = FALSE
4055 )
4056 ) AS "subquery"
4057 WHERE "direct_voter"."issue_id" = "issue_id_p"
4058 AND "direct_voter"."member_id" = "subquery"."member_id";
4059 -- consider delegations:
4060 UPDATE "direct_voter" SET "weight" = 1
4061 WHERE "issue_id" = "issue_id_p";
4062 PERFORM "add_vote_delegations"("issue_id_p");
4063 -- mark first preferences:
4064 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
4065 FROM (
4066 SELECT
4067 "vote"."initiative_id",
4068 "vote"."member_id",
4069 CASE WHEN "vote"."grade" > 0 THEN
4070 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
4071 ELSE NULL
4072 END AS "first_preference"
4073 FROM "vote"
4074 JOIN "initiative" -- NOTE: due to missing index on issue_id
4075 ON "vote"."issue_id" = "initiative"."issue_id"
4076 JOIN "vote" AS "agg"
4077 ON "initiative"."id" = "agg"."initiative_id"
4078 AND "vote"."member_id" = "agg"."member_id"
4079 GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
4080 ) AS "subquery"
4081 WHERE "vote"."issue_id" = "issue_id_p"
4082 AND "vote"."initiative_id" = "subquery"."initiative_id"
4083 AND "vote"."member_id" = "subquery"."member_id";
4084 -- finish overriding protection triggers (avoids garbage):
4085 DELETE FROM "temporary_transaction_data"
4086 WHERE "key" = 'override_protection_triggers';
4087 -- materialize battle_view:
4088 -- NOTE: "closed" column of issue must be set at this point
4089 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
4090 INSERT INTO "battle" (
4091 "issue_id",
4092 "winning_initiative_id", "losing_initiative_id",
4093 "count"
4094 ) SELECT
4095 "issue_id",
4096 "winning_initiative_id", "losing_initiative_id",
4097 "count"
4098 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
4099 -- set voter count:
4100 UPDATE "issue" SET
4101 "voter_count" = (
4102 SELECT coalesce(sum("weight"), 0)
4103 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
4104 )
4105 WHERE "id" = "issue_id_p";
4106 -- copy "positive_votes" and "negative_votes" from "battle" table:
4107 -- NOTE: "first_preference_votes" is set to a default of 0 at this step
4108 UPDATE "initiative" SET
4109 "first_preference_votes" = 0,
4110 "positive_votes" = "battle_win"."count",
4111 "negative_votes" = "battle_lose"."count"
4112 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
4113 WHERE
4114 "battle_win"."issue_id" = "issue_id_p" AND
4115 "battle_win"."winning_initiative_id" = "initiative"."id" AND
4116 "battle_win"."losing_initiative_id" ISNULL AND
4117 "battle_lose"."issue_id" = "issue_id_p" AND
4118 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
4119 "battle_lose"."winning_initiative_id" ISNULL;
4120 -- calculate "first_preference_votes":
4121 -- NOTE: will only set values not equal to zero
4122 UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum"
4123 FROM (
4124 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
4125 FROM "vote" JOIN "direct_voter"
4126 ON "vote"."issue_id" = "direct_voter"."issue_id"
4127 AND "vote"."member_id" = "direct_voter"."member_id"
4128 WHERE "vote"."first_preference"
4129 GROUP BY "vote"."initiative_id"
4130 ) AS "subquery"
4131 WHERE "initiative"."issue_id" = "issue_id_p"
4132 AND "initiative"."admitted"
4133 AND "initiative"."id" = "subquery"."initiative_id";
4134 END;
4135 $$;
4137 COMMENT ON FUNCTION "close_voting"
4138 ( "issue"."id"%TYPE )
4139 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.';
4142 CREATE FUNCTION "defeat_strength"
4143 ( "positive_votes_p" INT4,
4144 "negative_votes_p" INT4,
4145 "defeat_strength_p" "defeat_strength" )
4146 RETURNS INT8
4147 LANGUAGE 'plpgsql' IMMUTABLE AS $$
4148 BEGIN
4149 IF "defeat_strength_p" = 'simple'::"defeat_strength" THEN
4150 IF "positive_votes_p" > "negative_votes_p" THEN
4151 RETURN "positive_votes_p";
4152 ELSE
4153 RETURN 0;
4154 END IF;
4155 ELSE
4156 IF "positive_votes_p" > "negative_votes_p" THEN
4157 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
4158 ELSIF "positive_votes_p" = "negative_votes_p" THEN
4159 RETURN 0;
4160 ELSE
4161 RETURN -1;
4162 END IF;
4163 END IF;
4164 END;
4165 $$;
4167 COMMENT ON FUNCTION "defeat_strength"(INT4, INT4, "defeat_strength") IS 'Calculates defeat strength (INT8!) according to the "defeat_strength" option (see comment on type "defeat_strength")';
4170 CREATE FUNCTION "secondary_link_strength"
4171 ( "initiative1_ord_p" INT4,
4172 "initiative2_ord_p" INT4,
4173 "tie_breaking_p" "tie_breaking" )
4174 RETURNS INT8
4175 LANGUAGE 'plpgsql' IMMUTABLE AS $$
4176 BEGIN
4177 IF "initiative1_ord_p" = "initiative2_ord_p" THEN
4178 RAISE EXCEPTION 'Identical initiative ids passed to "secondary_link_strength" function (should not happen)';
4179 END IF;
4180 RETURN (
4181 CASE WHEN "tie_breaking_p" = 'simple'::"tie_breaking" THEN
4182 0
4183 ELSE
4184 CASE WHEN "initiative1_ord_p" < "initiative2_ord_p" THEN
4185 1::INT8 << 62
4186 ELSE 0 END
4187 +
4188 CASE WHEN "tie_breaking_p" = 'variant2'::"tie_breaking" THEN
4189 ("initiative2_ord_p"::INT8 << 31) - "initiative1_ord_p"::INT8
4190 ELSE
4191 "initiative2_ord_p"::INT8 - ("initiative1_ord_p"::INT8 << 31)
4192 END
4193 END
4194 );
4195 END;
4196 $$;
4198 COMMENT ON FUNCTION "secondary_link_strength"(INT4, INT4, "tie_breaking") IS 'Calculates a secondary criterion for the defeat strength (tie-breaking of the links)';
4201 CREATE TYPE "link_strength" AS (
4202 "primary" INT8,
4203 "secondary" INT8 );
4205 COMMENT ON TYPE "link_strength" IS 'Type to store the defeat strength of a link between two candidates plus a secondary criterion to create unique link strengths between the candidates (needed for tie-breaking ''variant1'' and ''variant2'')';
4208 CREATE FUNCTION "find_best_paths"("matrix_d" "link_strength"[][])
4209 RETURNS "link_strength"[][]
4210 LANGUAGE 'plpgsql' IMMUTABLE AS $$
4211 DECLARE
4212 "dimension_v" INT4;
4213 "matrix_p" "link_strength"[][];
4214 "i" INT4;
4215 "j" INT4;
4216 "k" INT4;
4217 BEGIN
4218 "dimension_v" := array_upper("matrix_d", 1);
4219 "matrix_p" := "matrix_d";
4220 "i" := 1;
4221 LOOP
4222 "j" := 1;
4223 LOOP
4224 IF "i" != "j" THEN
4225 "k" := 1;
4226 LOOP
4227 IF "i" != "k" AND "j" != "k" THEN
4228 IF "matrix_p"["j"]["i"] < "matrix_p"["i"]["k"] THEN
4229 IF "matrix_p"["j"]["i"] > "matrix_p"["j"]["k"] THEN
4230 "matrix_p"["j"]["k"] := "matrix_p"["j"]["i"];
4231 END IF;
4232 ELSE
4233 IF "matrix_p"["i"]["k"] > "matrix_p"["j"]["k"] THEN
4234 "matrix_p"["j"]["k"] := "matrix_p"["i"]["k"];
4235 END IF;
4236 END IF;
4237 END IF;
4238 EXIT WHEN "k" = "dimension_v";
4239 "k" := "k" + 1;
4240 END LOOP;
4241 END IF;
4242 EXIT WHEN "j" = "dimension_v";
4243 "j" := "j" + 1;
4244 END LOOP;
4245 EXIT WHEN "i" = "dimension_v";
4246 "i" := "i" + 1;
4247 END LOOP;
4248 RETURN "matrix_p";
4249 END;
4250 $$;
4252 COMMENT ON FUNCTION "find_best_paths"("link_strength"[][]) IS 'Computes the strengths of the best beat-paths from a square matrix';
4255 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
4256 RETURNS VOID
4257 LANGUAGE 'plpgsql' VOLATILE AS $$
4258 DECLARE
4259 "issue_row" "issue"%ROWTYPE;
4260 "policy_row" "policy"%ROWTYPE;
4261 "dimension_v" INT4;
4262 "matrix_a" INT4[][]; -- absolute votes
4263 "matrix_d" "link_strength"[][]; -- defeat strength (direct)
4264 "matrix_p" "link_strength"[][]; -- defeat strength (best path)
4265 "matrix_t" "link_strength"[][]; -- defeat strength (tie-breaking)
4266 "matrix_f" BOOLEAN[][]; -- forbidden link (tie-breaking)
4267 "matrix_b" BOOLEAN[][]; -- final order (who beats who)
4268 "i" INT4;
4269 "j" INT4;
4270 "m" INT4;
4271 "n" INT4;
4272 "battle_row" "battle"%ROWTYPE;
4273 "rank_ary" INT4[];
4274 "rank_v" INT4;
4275 "initiative_id_v" "initiative"."id"%TYPE;
4276 BEGIN
4277 PERFORM "require_transaction_isolation"();
4278 SELECT * INTO "issue_row"
4279 FROM "issue" WHERE "id" = "issue_id_p";
4280 SELECT * INTO "policy_row"
4281 FROM "policy" WHERE "id" = "issue_row"."policy_id";
4282 SELECT count(1) INTO "dimension_v"
4283 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
4284 -- create "matrix_a" with absolute number of votes in pairwise
4285 -- comparison:
4286 "matrix_a" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
4287 "i" := 1;
4288 "j" := 2;
4289 FOR "battle_row" IN
4290 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
4291 ORDER BY
4292 "winning_initiative_id" NULLS FIRST,
4293 "losing_initiative_id" NULLS FIRST
4294 LOOP
4295 "matrix_a"["i"]["j"] := "battle_row"."count";
4296 IF "j" = "dimension_v" THEN
4297 "i" := "i" + 1;
4298 "j" := 1;
4299 ELSE
4300 "j" := "j" + 1;
4301 IF "j" = "i" THEN
4302 "j" := "j" + 1;
4303 END IF;
4304 END IF;
4305 END LOOP;
4306 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
4307 RAISE EXCEPTION 'Wrong battle count (should not happen)';
4308 END IF;
4309 -- store direct defeat strengths in "matrix_d" using "defeat_strength"
4310 -- and "secondary_link_strength" functions:
4311 "matrix_d" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
4312 "i" := 1;
4313 LOOP
4314 "j" := 1;
4315 LOOP
4316 IF "i" != "j" THEN
4317 "matrix_d"["i"]["j"] := (
4318 "defeat_strength"(
4319 "matrix_a"["i"]["j"],
4320 "matrix_a"["j"]["i"],
4321 "policy_row"."defeat_strength"
4322 ),
4323 "secondary_link_strength"(
4324 "i",
4325 "j",
4326 "policy_row"."tie_breaking"
4327 )
4328 )::"link_strength";
4329 END IF;
4330 EXIT WHEN "j" = "dimension_v";
4331 "j" := "j" + 1;
4332 END LOOP;
4333 EXIT WHEN "i" = "dimension_v";
4334 "i" := "i" + 1;
4335 END LOOP;
4336 -- find best paths:
4337 "matrix_p" := "find_best_paths"("matrix_d");
4338 -- create partial order:
4339 "matrix_b" := array_fill(NULL::BOOLEAN, ARRAY["dimension_v", "dimension_v"]);
4340 "i" := 1;
4341 LOOP
4342 "j" := "i" + 1;
4343 LOOP
4344 IF "i" != "j" THEN
4345 IF "matrix_p"["i"]["j"] > "matrix_p"["j"]["i"] THEN
4346 "matrix_b"["i"]["j"] := TRUE;
4347 "matrix_b"["j"]["i"] := FALSE;
4348 ELSIF "matrix_p"["i"]["j"] < "matrix_p"["j"]["i"] THEN
4349 "matrix_b"["i"]["j"] := FALSE;
4350 "matrix_b"["j"]["i"] := TRUE;
4351 END IF;
4352 END IF;
4353 EXIT WHEN "j" = "dimension_v";
4354 "j" := "j" + 1;
4355 END LOOP;
4356 EXIT WHEN "i" = "dimension_v" - 1;
4357 "i" := "i" + 1;
4358 END LOOP;
4359 -- tie-breaking by forbidding shared weakest links in beat-paths
4360 -- (unless "tie_breaking" is set to 'simple', in which case tie-breaking
4361 -- is performed later by initiative id):
4362 IF "policy_row"."tie_breaking" != 'simple'::"tie_breaking" THEN
4363 "m" := 1;
4364 LOOP
4365 "n" := "m" + 1;
4366 LOOP
4367 -- only process those candidates m and n, which are tied:
4368 IF "matrix_b"["m"]["n"] ISNULL THEN
4369 -- start with beat-paths prior tie-breaking:
4370 "matrix_t" := "matrix_p";
4371 -- start with all links allowed:
4372 "matrix_f" := array_fill(FALSE, ARRAY["dimension_v", "dimension_v"]);
4373 LOOP
4374 -- determine (and forbid) that link that is the weakest link
4375 -- in both the best path from candidate m to candidate n and
4376 -- from candidate n to candidate m:
4377 "i" := 1;
4378 <<forbid_one_link>>
4379 LOOP
4380 "j" := 1;
4381 LOOP
4382 IF "i" != "j" THEN
4383 IF "matrix_d"["i"]["j"] = "matrix_t"["m"]["n"] THEN
4384 "matrix_f"["i"]["j"] := TRUE;
4385 -- exit for performance reasons,
4386 -- as exactly one link will be found:
4387 EXIT forbid_one_link;
4388 END IF;
4389 END IF;
4390 EXIT WHEN "j" = "dimension_v";
4391 "j" := "j" + 1;
4392 END LOOP;
4393 IF "i" = "dimension_v" THEN
4394 RAISE EXCEPTION 'Did not find shared weakest link for tie-breaking (should not happen)';
4395 END IF;
4396 "i" := "i" + 1;
4397 END LOOP;
4398 -- calculate best beat-paths while ignoring forbidden links:
4399 "i" := 1;
4400 LOOP
4401 "j" := 1;
4402 LOOP
4403 IF "i" != "j" THEN
4404 "matrix_t"["i"]["j"] := CASE
4405 WHEN "matrix_f"["i"]["j"]
4406 THEN ((-1::INT8) << 63, 0)::"link_strength" -- worst possible value
4407 ELSE "matrix_d"["i"]["j"] END;
4408 END IF;
4409 EXIT WHEN "j" = "dimension_v";
4410 "j" := "j" + 1;
4411 END LOOP;
4412 EXIT WHEN "i" = "dimension_v";
4413 "i" := "i" + 1;
4414 END LOOP;
4415 "matrix_t" := "find_best_paths"("matrix_t");
4416 -- extend partial order, if tie-breaking was successful:
4417 IF "matrix_t"["m"]["n"] > "matrix_t"["n"]["m"] THEN
4418 "matrix_b"["m"]["n"] := TRUE;
4419 "matrix_b"["n"]["m"] := FALSE;
4420 EXIT;
4421 ELSIF "matrix_t"["m"]["n"] < "matrix_t"["n"]["m"] THEN
4422 "matrix_b"["m"]["n"] := FALSE;
4423 "matrix_b"["n"]["m"] := TRUE;
4424 EXIT;
4425 END IF;
4426 END LOOP;
4427 END IF;
4428 EXIT WHEN "n" = "dimension_v";
4429 "n" := "n" + 1;
4430 END LOOP;
4431 EXIT WHEN "m" = "dimension_v" - 1;
4432 "m" := "m" + 1;
4433 END LOOP;
4434 END IF;
4435 -- store a unique ranking in "rank_ary":
4436 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
4437 "rank_v" := 1;
4438 LOOP
4439 "i" := 1;
4440 <<assign_next_rank>>
4441 LOOP
4442 IF "rank_ary"["i"] ISNULL THEN
4443 "j" := 1;
4444 LOOP
4445 IF
4446 "i" != "j" AND
4447 "rank_ary"["j"] ISNULL AND
4448 ( "matrix_b"["j"]["i"] OR
4449 -- tie-breaking by "id"
4450 ( "matrix_b"["j"]["i"] ISNULL AND
4451 "j" < "i" ) )
4452 THEN
4453 -- someone else is better
4454 EXIT;
4455 END IF;
4456 IF "j" = "dimension_v" THEN
4457 -- noone is better
4458 "rank_ary"["i"] := "rank_v";
4459 EXIT assign_next_rank;
4460 END IF;
4461 "j" := "j" + 1;
4462 END LOOP;
4463 END IF;
4464 "i" := "i" + 1;
4465 IF "i" > "dimension_v" THEN
4466 RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
4467 END IF;
4468 END LOOP;
4469 EXIT WHEN "rank_v" = "dimension_v";
4470 "rank_v" := "rank_v" + 1;
4471 END LOOP;
4472 -- write preliminary results:
4473 "i" := 2; -- omit status quo with "i" = 1
4474 FOR "initiative_id_v" IN
4475 SELECT "id" FROM "initiative"
4476 WHERE "issue_id" = "issue_id_p" AND "admitted"
4477 ORDER BY "id"
4478 LOOP
4479 UPDATE "initiative" SET
4480 "direct_majority" =
4481 CASE WHEN "policy_row"."direct_majority_strict" THEN
4482 "positive_votes" * "policy_row"."direct_majority_den" >
4483 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
4484 ELSE
4485 "positive_votes" * "policy_row"."direct_majority_den" >=
4486 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
4487 END
4488 AND "positive_votes" >= "policy_row"."direct_majority_positive"
4489 AND "issue_row"."voter_count"-"negative_votes" >=
4490 "policy_row"."direct_majority_non_negative",
4491 "indirect_majority" =
4492 CASE WHEN "policy_row"."indirect_majority_strict" THEN
4493 "positive_votes" * "policy_row"."indirect_majority_den" >
4494 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4495 ELSE
4496 "positive_votes" * "policy_row"."indirect_majority_den" >=
4497 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4498 END
4499 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
4500 AND "issue_row"."voter_count"-"negative_votes" >=
4501 "policy_row"."indirect_majority_non_negative",
4502 "schulze_rank" = "rank_ary"["i"],
4503 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
4504 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"[1],
4505 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"[1],
4506 "reverse_beat_path" = CASE WHEN "policy_row"."defeat_strength" = 'simple'::"defeat_strength"
4507 THEN NULL
4508 ELSE "matrix_p"[1]["i"]."primary" >= 0 END,
4509 "eligible" = FALSE,
4510 "winner" = FALSE,
4511 "rank" = NULL -- NOTE: in cases of manual reset of issue state
4512 WHERE "id" = "initiative_id_v";
4513 "i" := "i" + 1;
4514 END LOOP;
4515 IF "i" != "dimension_v" + 1 THEN
4516 RAISE EXCEPTION 'Wrong winner count (should not happen)';
4517 END IF;
4518 -- take indirect majorities into account:
4519 LOOP
4520 UPDATE "initiative" SET "indirect_majority" = TRUE
4521 FROM (
4522 SELECT "new_initiative"."id" AS "initiative_id"
4523 FROM "initiative" "old_initiative"
4524 JOIN "initiative" "new_initiative"
4525 ON "new_initiative"."issue_id" = "issue_id_p"
4526 AND "new_initiative"."indirect_majority" = FALSE
4527 JOIN "battle" "battle_win"
4528 ON "battle_win"."issue_id" = "issue_id_p"
4529 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
4530 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
4531 JOIN "battle" "battle_lose"
4532 ON "battle_lose"."issue_id" = "issue_id_p"
4533 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
4534 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
4535 WHERE "old_initiative"."issue_id" = "issue_id_p"
4536 AND "old_initiative"."indirect_majority" = TRUE
4537 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
4538 "battle_win"."count" * "policy_row"."indirect_majority_den" >
4539 "policy_row"."indirect_majority_num" *
4540 ("battle_win"."count"+"battle_lose"."count")
4541 ELSE
4542 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
4543 "policy_row"."indirect_majority_num" *
4544 ("battle_win"."count"+"battle_lose"."count")
4545 END
4546 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
4547 AND "issue_row"."voter_count"-"battle_lose"."count" >=
4548 "policy_row"."indirect_majority_non_negative"
4549 ) AS "subquery"
4550 WHERE "id" = "subquery"."initiative_id";
4551 EXIT WHEN NOT FOUND;
4552 END LOOP;
4553 -- set "multistage_majority" for remaining matching initiatives:
4554 UPDATE "initiative" SET "multistage_majority" = TRUE
4555 FROM (
4556 SELECT "losing_initiative"."id" AS "initiative_id"
4557 FROM "initiative" "losing_initiative"
4558 JOIN "initiative" "winning_initiative"
4559 ON "winning_initiative"."issue_id" = "issue_id_p"
4560 AND "winning_initiative"."admitted"
4561 JOIN "battle" "battle_win"
4562 ON "battle_win"."issue_id" = "issue_id_p"
4563 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
4564 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
4565 JOIN "battle" "battle_lose"
4566 ON "battle_lose"."issue_id" = "issue_id_p"
4567 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
4568 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
4569 WHERE "losing_initiative"."issue_id" = "issue_id_p"
4570 AND "losing_initiative"."admitted"
4571 AND "winning_initiative"."schulze_rank" <
4572 "losing_initiative"."schulze_rank"
4573 AND "battle_win"."count" > "battle_lose"."count"
4574 AND (
4575 "battle_win"."count" > "winning_initiative"."positive_votes" OR
4576 "battle_lose"."count" < "losing_initiative"."negative_votes" )
4577 ) AS "subquery"
4578 WHERE "id" = "subquery"."initiative_id";
4579 -- mark eligible initiatives:
4580 UPDATE "initiative" SET "eligible" = TRUE
4581 WHERE "issue_id" = "issue_id_p"
4582 AND "initiative"."direct_majority"
4583 AND "initiative"."indirect_majority"
4584 AND "initiative"."better_than_status_quo"
4585 AND (
4586 "policy_row"."no_multistage_majority" = FALSE OR
4587 "initiative"."multistage_majority" = FALSE )
4588 AND (
4589 "policy_row"."no_reverse_beat_path" = FALSE OR
4590 coalesce("initiative"."reverse_beat_path", FALSE) = FALSE );
4591 -- mark final winner:
4592 UPDATE "initiative" SET "winner" = TRUE
4593 FROM (
4594 SELECT "id" AS "initiative_id"
4595 FROM "initiative"
4596 WHERE "issue_id" = "issue_id_p" AND "eligible"
4597 ORDER BY
4598 "schulze_rank",
4599 "id"
4600 LIMIT 1
4601 ) AS "subquery"
4602 WHERE "id" = "subquery"."initiative_id";
4603 -- write (final) ranks:
4604 "rank_v" := 1;
4605 FOR "initiative_id_v" IN
4606 SELECT "id"
4607 FROM "initiative"
4608 WHERE "issue_id" = "issue_id_p" AND "admitted"
4609 ORDER BY
4610 "winner" DESC,
4611 "eligible" DESC,
4612 "schulze_rank",
4613 "id"
4614 LOOP
4615 UPDATE "initiative" SET "rank" = "rank_v"
4616 WHERE "id" = "initiative_id_v";
4617 "rank_v" := "rank_v" + 1;
4618 END LOOP;
4619 -- set schulze rank of status quo and mark issue as finished:
4620 UPDATE "issue" SET
4621 "status_quo_schulze_rank" = "rank_ary"[1],
4622 "state" =
4623 CASE WHEN EXISTS (
4624 SELECT NULL FROM "initiative"
4625 WHERE "issue_id" = "issue_id_p" AND "winner"
4626 ) THEN
4627 'finished_with_winner'::"issue_state"
4628 ELSE
4629 'finished_without_winner'::"issue_state"
4630 END,
4631 "closed" = "phase_finished",
4632 "phase_finished" = NULL
4633 WHERE "id" = "issue_id_p";
4634 RETURN;
4635 END;
4636 $$;
4638 COMMENT ON FUNCTION "calculate_ranks"
4639 ( "issue"."id"%TYPE )
4640 IS 'Determine ranking (Votes have to be counted first)';
4644 -----------------------------
4645 -- Automatic state changes --
4646 -----------------------------
4649 CREATE TYPE "check_issue_persistence" AS (
4650 "state" "issue_state",
4651 "phase_finished" BOOLEAN,
4652 "issue_revoked" BOOLEAN,
4653 "snapshot_created" BOOLEAN,
4654 "harmonic_weights_set" BOOLEAN,
4655 "closed_voting" BOOLEAN );
4657 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';
4660 CREATE FUNCTION "check_issue"
4661 ( "issue_id_p" "issue"."id"%TYPE,
4662 "persist" "check_issue_persistence" )
4663 RETURNS "check_issue_persistence"
4664 LANGUAGE 'plpgsql' VOLATILE AS $$
4665 DECLARE
4666 "issue_row" "issue"%ROWTYPE;
4667 "policy_row" "policy"%ROWTYPE;
4668 "initiative_row" "initiative"%ROWTYPE;
4669 "state_v" "issue_state";
4670 BEGIN
4671 PERFORM "require_transaction_isolation"();
4672 IF "persist" ISNULL THEN
4673 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4674 FOR UPDATE;
4675 IF "issue_row"."closed" NOTNULL THEN
4676 RETURN NULL;
4677 END IF;
4678 "persist"."state" := "issue_row"."state";
4679 IF
4680 ( "issue_row"."state" = 'admission' AND now() >=
4681 "issue_row"."created" + "issue_row"."max_admission_time" ) OR
4682 ( "issue_row"."state" = 'discussion' AND now() >=
4683 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
4684 ( "issue_row"."state" = 'verification' AND now() >=
4685 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
4686 ( "issue_row"."state" = 'voting' AND now() >=
4687 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
4688 THEN
4689 "persist"."phase_finished" := TRUE;
4690 ELSE
4691 "persist"."phase_finished" := FALSE;
4692 END IF;
4693 IF
4694 NOT EXISTS (
4695 -- all initiatives are revoked
4696 SELECT NULL FROM "initiative"
4697 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4698 ) AND (
4699 -- and issue has not been accepted yet
4700 "persist"."state" = 'admission' OR
4701 -- or verification time has elapsed
4702 ( "persist"."state" = 'verification' AND
4703 "persist"."phase_finished" ) OR
4704 -- or no initiatives have been revoked lately
4705 NOT EXISTS (
4706 SELECT NULL FROM "initiative"
4707 WHERE "issue_id" = "issue_id_p"
4708 AND now() < "revoked" + "issue_row"."verification_time"
4709 )
4710 )
4711 THEN
4712 "persist"."issue_revoked" := TRUE;
4713 ELSE
4714 "persist"."issue_revoked" := FALSE;
4715 END IF;
4716 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
4717 UPDATE "issue" SET "phase_finished" = now()
4718 WHERE "id" = "issue_row"."id";
4719 RETURN "persist";
4720 ELSIF
4721 "persist"."state" IN ('admission', 'discussion', 'verification')
4722 THEN
4723 RETURN "persist";
4724 ELSE
4725 RETURN NULL;
4726 END IF;
4727 END IF;
4728 IF
4729 "persist"."state" IN ('admission', 'discussion', 'verification') AND
4730 coalesce("persist"."snapshot_created", FALSE) = FALSE
4731 THEN
4732 PERFORM "create_snapshot"("issue_id_p");
4733 "persist"."snapshot_created" = TRUE;
4734 IF "persist"."phase_finished" THEN
4735 IF "persist"."state" = 'admission' THEN
4736 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
4737 ELSIF "persist"."state" = 'discussion' THEN
4738 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
4739 ELSIF "persist"."state" = 'verification' THEN
4740 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
4741 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4742 SELECT * INTO "policy_row" FROM "policy"
4743 WHERE "id" = "issue_row"."policy_id";
4744 FOR "initiative_row" IN
4745 SELECT * FROM "initiative"
4746 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4747 FOR UPDATE
4748 LOOP
4749 IF
4750 "initiative_row"."polling" OR (
4751 "initiative_row"."satisfied_supporter_count" > 0 AND
4752 "initiative_row"."satisfied_supporter_count" *
4753 "policy_row"."initiative_quorum_den" >=
4754 "issue_row"."population" * "policy_row"."initiative_quorum_num"
4755 )
4756 THEN
4757 UPDATE "initiative" SET "admitted" = TRUE
4758 WHERE "id" = "initiative_row"."id";
4759 ELSE
4760 UPDATE "initiative" SET "admitted" = FALSE
4761 WHERE "id" = "initiative_row"."id";
4762 END IF;
4763 END LOOP;
4764 END IF;
4765 END IF;
4766 RETURN "persist";
4767 END IF;
4768 IF
4769 "persist"."state" IN ('admission', 'discussion', 'verification') AND
4770 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
4771 THEN
4772 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
4773 "persist"."harmonic_weights_set" = TRUE;
4774 IF
4775 "persist"."phase_finished" OR
4776 "persist"."issue_revoked" OR
4777 "persist"."state" = 'admission'
4778 THEN
4779 RETURN "persist";
4780 ELSE
4781 RETURN NULL;
4782 END IF;
4783 END IF;
4784 IF "persist"."issue_revoked" THEN
4785 IF "persist"."state" = 'admission' THEN
4786 "state_v" := 'canceled_revoked_before_accepted';
4787 ELSIF "persist"."state" = 'discussion' THEN
4788 "state_v" := 'canceled_after_revocation_during_discussion';
4789 ELSIF "persist"."state" = 'verification' THEN
4790 "state_v" := 'canceled_after_revocation_during_verification';
4791 END IF;
4792 UPDATE "issue" SET
4793 "state" = "state_v",
4794 "closed" = "phase_finished",
4795 "phase_finished" = NULL
4796 WHERE "id" = "issue_id_p";
4797 RETURN NULL;
4798 END IF;
4799 IF "persist"."state" = 'admission' THEN
4800 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4801 FOR UPDATE;
4802 SELECT * INTO "policy_row"
4803 FROM "policy" WHERE "id" = "issue_row"."policy_id";
4804 IF
4805 ( now() >=
4806 "issue_row"."created" + "issue_row"."min_admission_time" ) AND
4807 EXISTS (
4808 SELECT NULL FROM "initiative"
4809 WHERE "issue_id" = "issue_id_p"
4810 AND "supporter_count" > 0
4811 AND "supporter_count" * "policy_row"."issue_quorum_den"
4812 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
4813 )
4814 THEN
4815 UPDATE "issue" SET
4816 "state" = 'discussion',
4817 "accepted" = coalesce("phase_finished", now()),
4818 "phase_finished" = NULL
4819 WHERE "id" = "issue_id_p";
4820 ELSIF "issue_row"."phase_finished" NOTNULL THEN
4821 UPDATE "issue" SET
4822 "state" = 'canceled_issue_not_accepted',
4823 "closed" = "phase_finished",
4824 "phase_finished" = NULL
4825 WHERE "id" = "issue_id_p";
4826 END IF;
4827 RETURN NULL;
4828 END IF;
4829 IF "persist"."phase_finished" THEN
4830 IF "persist"."state" = 'discussion' THEN
4831 UPDATE "issue" SET
4832 "state" = 'verification',
4833 "half_frozen" = "phase_finished",
4834 "phase_finished" = NULL
4835 WHERE "id" = "issue_id_p";
4836 RETURN NULL;
4837 END IF;
4838 IF "persist"."state" = 'verification' THEN
4839 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4840 FOR UPDATE;
4841 SELECT * INTO "policy_row" FROM "policy"
4842 WHERE "id" = "issue_row"."policy_id";
4843 IF EXISTS (
4844 SELECT NULL FROM "initiative"
4845 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
4846 ) THEN
4847 UPDATE "issue" SET
4848 "state" = 'voting',
4849 "fully_frozen" = "phase_finished",
4850 "phase_finished" = NULL
4851 WHERE "id" = "issue_id_p";
4852 ELSE
4853 UPDATE "issue" SET
4854 "state" = 'canceled_no_initiative_admitted',
4855 "fully_frozen" = "phase_finished",
4856 "closed" = "phase_finished",
4857 "phase_finished" = NULL
4858 WHERE "id" = "issue_id_p";
4859 -- NOTE: The following DELETE statements have effect only when
4860 -- issue state has been manipulated
4861 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
4862 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
4863 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
4864 END IF;
4865 RETURN NULL;
4866 END IF;
4867 IF "persist"."state" = 'voting' THEN
4868 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
4869 PERFORM "close_voting"("issue_id_p");
4870 "persist"."closed_voting" = TRUE;
4871 RETURN "persist";
4872 END IF;
4873 PERFORM "calculate_ranks"("issue_id_p");
4874 RETURN NULL;
4875 END IF;
4876 END IF;
4877 RAISE WARNING 'should not happen';
4878 RETURN NULL;
4879 END;
4880 $$;
4882 COMMENT ON FUNCTION "check_issue"
4883 ( "issue"."id"%TYPE,
4884 "check_issue_persistence" )
4885 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")';
4888 CREATE FUNCTION "check_everything"()
4889 RETURNS VOID
4890 LANGUAGE 'plpgsql' VOLATILE AS $$
4891 DECLARE
4892 "issue_id_v" "issue"."id"%TYPE;
4893 "persist_v" "check_issue_persistence";
4894 BEGIN
4895 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
4896 DELETE FROM "expired_session";
4897 PERFORM "check_activity"();
4898 PERFORM "calculate_member_counts"();
4899 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
4900 "persist_v" := NULL;
4901 LOOP
4902 "persist_v" := "check_issue"("issue_id_v", "persist_v");
4903 EXIT WHEN "persist_v" ISNULL;
4904 END LOOP;
4905 END LOOP;
4906 RETURN;
4907 END;
4908 $$;
4910 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.';
4914 ----------------------
4915 -- Deletion of data --
4916 ----------------------
4919 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
4920 RETURNS VOID
4921 LANGUAGE 'plpgsql' VOLATILE AS $$
4922 BEGIN
4923 IF EXISTS (
4924 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
4925 ) THEN
4926 -- override protection triggers:
4927 INSERT INTO "temporary_transaction_data" ("key", "value")
4928 VALUES ('override_protection_triggers', TRUE::TEXT);
4929 -- clean data:
4930 DELETE FROM "delegating_voter"
4931 WHERE "issue_id" = "issue_id_p";
4932 DELETE FROM "direct_voter"
4933 WHERE "issue_id" = "issue_id_p";
4934 DELETE FROM "delegating_interest_snapshot"
4935 WHERE "issue_id" = "issue_id_p";
4936 DELETE FROM "direct_interest_snapshot"
4937 WHERE "issue_id" = "issue_id_p";
4938 DELETE FROM "delegating_population_snapshot"
4939 WHERE "issue_id" = "issue_id_p";
4940 DELETE FROM "direct_population_snapshot"
4941 WHERE "issue_id" = "issue_id_p";
4942 DELETE FROM "non_voter"
4943 WHERE "issue_id" = "issue_id_p";
4944 DELETE FROM "delegation"
4945 WHERE "issue_id" = "issue_id_p";
4946 DELETE FROM "supporter"
4947 USING "initiative" -- NOTE: due to missing index on issue_id
4948 WHERE "initiative"."issue_id" = "issue_id_p"
4949 AND "supporter"."initiative_id" = "initiative_id";
4950 -- mark issue as cleaned:
4951 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
4952 -- finish overriding protection triggers (avoids garbage):
4953 DELETE FROM "temporary_transaction_data"
4954 WHERE "key" = 'override_protection_triggers';
4955 END IF;
4956 RETURN;
4957 END;
4958 $$;
4960 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
4963 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
4964 RETURNS VOID
4965 LANGUAGE 'plpgsql' VOLATILE AS $$
4966 BEGIN
4967 UPDATE "member" SET
4968 "last_login" = NULL,
4969 "last_delegation_check" = NULL,
4970 "login" = NULL,
4971 "password" = NULL,
4972 "authority" = NULL,
4973 "authority_uid" = NULL,
4974 "authority_login" = NULL,
4975 "locked" = TRUE,
4976 "active" = FALSE,
4977 "notify_email" = NULL,
4978 "notify_email_unconfirmed" = NULL,
4979 "notify_email_secret" = NULL,
4980 "notify_email_secret_expiry" = NULL,
4981 "notify_email_lock_expiry" = NULL,
4982 "login_recovery_expiry" = NULL,
4983 "password_reset_secret" = NULL,
4984 "password_reset_secret_expiry" = NULL,
4985 "organizational_unit" = NULL,
4986 "internal_posts" = NULL,
4987 "realname" = NULL,
4988 "birthday" = NULL,
4989 "address" = NULL,
4990 "email" = NULL,
4991 "xmpp_address" = NULL,
4992 "website" = NULL,
4993 "phone" = NULL,
4994 "mobile_phone" = NULL,
4995 "profession" = NULL,
4996 "external_memberships" = NULL,
4997 "external_posts" = NULL,
4998 "statement" = NULL
4999 WHERE "id" = "member_id_p";
5000 -- "text_search_data" is updated by triggers
5001 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
5002 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
5003 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
5004 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
5005 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
5006 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
5007 DELETE FROM "session" WHERE "member_id" = "member_id_p";
5008 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
5009 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
5010 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
5011 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
5012 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
5013 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
5014 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
5015 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
5016 DELETE FROM "direct_voter" USING "issue"
5017 WHERE "direct_voter"."issue_id" = "issue"."id"
5018 AND "issue"."closed" ISNULL
5019 AND "member_id" = "member_id_p";
5020 RETURN;
5021 END;
5022 $$;
5024 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)';
5027 CREATE FUNCTION "delete_private_data"()
5028 RETURNS VOID
5029 LANGUAGE 'plpgsql' VOLATILE AS $$
5030 BEGIN
5031 DELETE FROM "temporary_transaction_data";
5032 DELETE FROM "member" WHERE "activated" ISNULL;
5033 UPDATE "member" SET
5034 "invite_code" = NULL,
5035 "invite_code_expiry" = NULL,
5036 "admin_comment" = NULL,
5037 "last_login" = NULL,
5038 "last_delegation_check" = NULL,
5039 "login" = NULL,
5040 "password" = NULL,
5041 "authority" = NULL,
5042 "authority_uid" = NULL,
5043 "authority_login" = NULL,
5044 "lang" = NULL,
5045 "notify_email" = NULL,
5046 "notify_email_unconfirmed" = NULL,
5047 "notify_email_secret" = NULL,
5048 "notify_email_secret_expiry" = NULL,
5049 "notify_email_lock_expiry" = NULL,
5050 "notify_level" = NULL,
5051 "login_recovery_expiry" = NULL,
5052 "password_reset_secret" = NULL,
5053 "password_reset_secret_expiry" = NULL,
5054 "organizational_unit" = NULL,
5055 "internal_posts" = NULL,
5056 "realname" = NULL,
5057 "birthday" = NULL,
5058 "address" = NULL,
5059 "email" = NULL,
5060 "xmpp_address" = NULL,
5061 "website" = NULL,
5062 "phone" = NULL,
5063 "mobile_phone" = NULL,
5064 "profession" = NULL,
5065 "external_memberships" = NULL,
5066 "external_posts" = NULL,
5067 "formatting_engine" = NULL,
5068 "statement" = NULL;
5069 -- "text_search_data" is updated by triggers
5070 DELETE FROM "setting";
5071 DELETE FROM "setting_map";
5072 DELETE FROM "member_relation_setting";
5073 DELETE FROM "member_image";
5074 DELETE FROM "contact";
5075 DELETE FROM "ignored_member";
5076 DELETE FROM "session";
5077 DELETE FROM "area_setting";
5078 DELETE FROM "issue_setting";
5079 DELETE FROM "ignored_initiative";
5080 DELETE FROM "initiative_setting";
5081 DELETE FROM "suggestion_setting";
5082 DELETE FROM "non_voter";
5083 DELETE FROM "direct_voter" USING "issue"
5084 WHERE "direct_voter"."issue_id" = "issue"."id"
5085 AND "issue"."closed" ISNULL;
5086 RETURN;
5087 END;
5088 $$;
5090 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.';
5094 COMMIT;