liquid_feedback_core
view core.sql @ 529:96ee2db56bec
New PostgreSQL extension "pgLatLon" for geospatial operations and indexing in LiquidFeedback
| author | jbe |
|---|---|
| date | Thu Aug 18 20:19:58 2016 +0200 (2016-08-18) |
| parents | 3e28fd842354 |
| children | 5855ff9e5c8f |
line source
2 -- NOTE: In PostgreSQL every UNIQUE constraint implies creation of an index
4 BEGIN;
6 CREATE EXTENSION latlon; -- load pgLatLon extenstion
8 CREATE VIEW "liquid_feedback_version" AS
9 SELECT * FROM (VALUES ('4.0.0', 4, 0, 0))
10 AS "subquery"("string", "major", "minor", "revision");
14 ----------------------
15 -- Full text search --
16 ----------------------
19 CREATE FUNCTION "text_search_query"("query_text_p" TEXT)
20 RETURNS TSQUERY
21 LANGUAGE 'plpgsql' IMMUTABLE AS $$
22 BEGIN
23 RETURN plainto_tsquery('pg_catalog.simple', "query_text_p");
24 END;
25 $$;
27 COMMENT ON FUNCTION "text_search_query"(TEXT) IS 'Usage: WHERE "text_search_data" @@ "text_search_query"(''<user query>'')';
30 CREATE FUNCTION "highlight"
31 ( "body_p" TEXT,
32 "query_text_p" TEXT )
33 RETURNS TEXT
34 LANGUAGE 'plpgsql' IMMUTABLE AS $$
35 BEGIN
36 RETURN ts_headline(
37 'pg_catalog.simple',
38 replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'),
39 "text_search_query"("query_text_p"),
40 'StartSel=* StopSel=* HighlightAll=TRUE' );
41 END;
42 $$;
44 COMMENT ON FUNCTION "highlight"
45 ( "body_p" TEXT,
46 "query_text_p" TEXT )
47 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.';
51 -------------------------
52 -- Tables and indicies --
53 -------------------------
56 CREATE TABLE "temporary_transaction_data" (
57 PRIMARY KEY ("txid", "key"),
58 "txid" INT8 DEFAULT txid_current(),
59 "key" TEXT,
60 "value" TEXT NOT NULL );
62 COMMENT ON TABLE "temporary_transaction_data" IS 'Table to store temporary transaction data; shall be emptied before a transaction is committed';
64 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';
67 CREATE TABLE "system_setting" (
68 "member_ttl" INTERVAL );
69 CREATE UNIQUE INDEX "system_setting_singleton_idx" ON "system_setting" ((1));
71 COMMENT ON TABLE "system_setting" IS 'This table contains only one row with different settings in each column.';
72 COMMENT ON INDEX "system_setting_singleton_idx" IS 'This index ensures that "system_setting" only contains one row maximum.';
74 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.';
77 CREATE TABLE "contingent" (
78 PRIMARY KEY ("polling", "time_frame"),
79 "polling" BOOLEAN,
80 "time_frame" INTERVAL,
81 "text_entry_limit" INT4,
82 "initiative_limit" INT4 );
84 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.';
86 COMMENT ON COLUMN "contingent"."polling" IS 'Determines if settings are for creating initiatives and new drafts of initiatives with "polling" flag set';
87 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';
88 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
91 CREATE TABLE "member" (
92 "id" SERIAL4 PRIMARY KEY,
93 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
94 "invite_code" TEXT UNIQUE,
95 "invite_code_expiry" TIMESTAMPTZ,
96 "admin_comment" TEXT,
97 "activated" TIMESTAMPTZ,
98 "last_activity" DATE,
99 "last_login" TIMESTAMPTZ,
100 "last_delegation_check" TIMESTAMPTZ,
101 "login" TEXT UNIQUE,
102 "password" TEXT,
103 "authority" TEXT,
104 "authority_uid" TEXT,
105 "authority_login" TEXT,
106 "locked" BOOLEAN NOT NULL DEFAULT FALSE,
107 "active" BOOLEAN NOT NULL DEFAULT FALSE,
108 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
109 "lang" TEXT,
110 "notify_email" TEXT,
111 "notify_email_unconfirmed" TEXT,
112 "notify_email_secret" TEXT UNIQUE,
113 "notify_email_secret_expiry" TIMESTAMPTZ,
114 "notify_email_lock_expiry" TIMESTAMPTZ,
115 "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE,
116 "notification_counter" INT4 NOT NULL DEFAULT 1,
117 "notification_sample_size" INT4 NOT NULL DEFAULT 3,
118 "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6),
119 "notification_hour" INT4 DEFAULT floor(random() * 24) CHECK ("notification_hour" BETWEEN 0 AND 23),
120 "notification_sent" TIMESTAMP,
121 "login_recovery_expiry" TIMESTAMPTZ,
122 "password_reset_secret" TEXT UNIQUE,
123 "password_reset_secret_expiry" TIMESTAMPTZ,
124 "name" TEXT UNIQUE,
125 "identification" TEXT UNIQUE,
126 "authentication" TEXT,
127 "organizational_unit" TEXT,
128 "internal_posts" TEXT,
129 "realname" TEXT,
130 "birthday" DATE,
131 "address" TEXT,
132 "email" TEXT,
133 "xmpp_address" TEXT,
134 "website" TEXT,
135 "phone" TEXT,
136 "mobile_phone" TEXT,
137 "profession" TEXT,
138 "external_memberships" TEXT,
139 "external_posts" TEXT,
140 "formatting_engine" TEXT,
141 "statement" TEXT,
142 "location" EPOINT,
143 "text_search_data" TSVECTOR,
144 CONSTRAINT "active_requires_activated_and_last_activity"
145 CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)),
146 CONSTRAINT "authority_requires_uid_and_vice_versa"
147 CHECK (("authority" NOTNULL) = ("authority_uid" NOTNULL)),
148 CONSTRAINT "authority_uid_unique_per_authority"
149 UNIQUE ("authority", "authority_uid"),
150 CONSTRAINT "authority_login_requires_authority"
151 CHECK ("authority" NOTNULL OR "authority_login" ISNULL),
152 CONSTRAINT "notification_dow_requires_notification_hour"
153 CHECK ("notification_dow" ISNULL OR "notification_hour" NOTNULL),
154 CONSTRAINT "name_not_null_if_activated"
155 CHECK ("activated" ISNULL OR "name" NOTNULL) );
156 CREATE INDEX "member_authority_login_idx" ON "member" ("authority_login");
157 CREATE INDEX "member_active_idx" ON "member" ("active");
158 CREATE INDEX "member_location_idx" ON "member" USING gist ("location");
159 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
160 CREATE TRIGGER "update_text_search_data"
161 BEFORE INSERT OR UPDATE ON "member"
162 FOR EACH ROW EXECUTE PROCEDURE
163 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
164 "name", "identification", "organizational_unit", "internal_posts",
165 "realname", "external_memberships", "external_posts", "statement" );
167 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
169 COMMENT ON COLUMN "member"."created" IS 'Creation of member record and/or invite code';
170 COMMENT ON COLUMN "member"."invite_code" IS 'Optional invite code, to allow a member to initialize his/her account the first time';
171 COMMENT ON COLUMN "member"."invite_code_expiry" IS 'Expiry data/time for "invite_code"';
172 COMMENT ON COLUMN "member"."admin_comment" IS 'Hidden comment for administrative purposes';
173 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';
174 COMMENT ON COLUMN "member"."last_activity" IS 'Date of last activity of member; required to be set for "active" members';
175 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';
176 COMMENT ON COLUMN "member"."last_delegation_check" IS 'Timestamp of last delegation check (i.e. confirmation of all unit and area delegations)';
177 COMMENT ON COLUMN "member"."login" IS 'Login name';
178 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
179 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)';
180 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)';
181 COMMENT ON COLUMN "member"."authority_login" IS 'Login name for external accounts (field is not unique!)';
182 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
183 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".';
184 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
185 COMMENT ON COLUMN "member"."lang" IS 'Language code of the preferred language of the member';
186 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
187 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
188 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
189 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
190 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
191 COMMENT ON COLUMN "member"."disable_notifications" IS 'TRUE if member does not want to receive notifications';
192 COMMENT ON COLUMN "member"."notification_counter" IS 'Sequential number of next scheduled notification message (used as a seed for pseudo-random initiative selection algorithm)';
193 COMMENT ON COLUMN "member"."notification_sample_size" IS 'Number of featured initiatives per issue in scheduled notification messages';
194 COMMENT ON COLUMN "member"."notification_dow" IS 'Day of week for scheduled notifications (NULL to receive a daily digest)';
195 COMMENT ON COLUMN "member"."notification_hour" IS 'Time of day when scheduled notifications are sent out';
196 COMMENT ON COLUMN "member"."notification_sent" IS 'Timestamp of last scheduled notification mail that has been sent out';
197 COMMENT ON COLUMN "member"."login_recovery_expiry" IS 'Date/time after which another login recovery attempt is allowed';
198 COMMENT ON COLUMN "member"."password_reset_secret" IS 'Secret string sent via e-mail for password recovery';
199 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';
200 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member, may be NULL if account has not been activated yet';
201 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
202 COMMENT ON COLUMN "member"."authentication" IS 'Information about how this member was authenticated';
203 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
204 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
205 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
206 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
207 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
208 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
209 COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"';
210 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his/her profile';
211 COMMENT ON COLUMN "member"."location" IS 'Geographic location on earth';
214 CREATE TABLE "member_history" (
215 "id" SERIAL8 PRIMARY KEY,
216 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
217 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
218 "active" BOOLEAN NOT NULL,
219 "name" TEXT NOT NULL );
220 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
222 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
224 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
225 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
228 CREATE TABLE "rendered_member_statement" (
229 PRIMARY KEY ("member_id", "format"),
230 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
231 "format" TEXT,
232 "content" TEXT NOT NULL );
234 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)';
237 CREATE TABLE "setting" (
238 PRIMARY KEY ("member_id", "key"),
239 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
240 "key" TEXT NOT NULL,
241 "value" TEXT NOT NULL );
242 CREATE INDEX "setting_key_idx" ON "setting" ("key");
244 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
246 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
249 CREATE TABLE "setting_map" (
250 PRIMARY KEY ("member_id", "key", "subkey"),
251 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
252 "key" TEXT NOT NULL,
253 "subkey" TEXT NOT NULL,
254 "value" TEXT NOT NULL );
255 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
257 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
259 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
260 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
261 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
264 CREATE TABLE "member_relation_setting" (
265 PRIMARY KEY ("member_id", "key", "other_member_id"),
266 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
267 "key" TEXT NOT NULL,
268 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
269 "value" TEXT NOT NULL );
271 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
274 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
276 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
279 CREATE TABLE "member_image" (
280 PRIMARY KEY ("member_id", "image_type", "scaled"),
281 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
282 "image_type" "member_image_type",
283 "scaled" BOOLEAN,
284 "content_type" TEXT,
285 "data" BYTEA NOT NULL );
287 COMMENT ON TABLE "member_image" IS 'Images of members';
289 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
292 CREATE TABLE "member_count" (
293 "calculated" TIMESTAMPTZ NOT NULL DEFAULT now(),
294 "total_count" INT4 NOT NULL );
296 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';
298 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
299 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
302 CREATE TABLE "contact" (
303 PRIMARY KEY ("member_id", "other_member_id"),
304 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
305 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
306 "public" BOOLEAN NOT NULL DEFAULT FALSE,
307 CONSTRAINT "cant_save_yourself_as_contact"
308 CHECK ("member_id" != "other_member_id") );
309 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
311 COMMENT ON TABLE "contact" IS 'Contact lists';
313 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
314 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
315 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
318 CREATE TABLE "ignored_member" (
319 PRIMARY KEY ("member_id", "other_member_id"),
320 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
321 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
322 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
324 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
326 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';
327 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
330 CREATE TABLE "session" (
331 "ident" TEXT PRIMARY KEY,
332 "additional_secret" TEXT,
333 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
334 "member_id" INT4 REFERENCES "member" ("id") ON DELETE SET NULL,
335 "authority" TEXT,
336 "authority_uid" TEXT,
337 "authority_login" TEXT,
338 "needs_delegation_check" BOOLEAN NOT NULL DEFAULT FALSE,
339 "lang" TEXT );
340 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
342 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
344 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
345 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
346 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
347 COMMENT ON COLUMN "session"."authority" IS 'Temporary store for "member"."authority" during member account creation';
348 COMMENT ON COLUMN "session"."authority_uid" IS 'Temporary store for "member"."authority_uid" during member account creation';
349 COMMENT ON COLUMN "session"."authority_login" IS 'Temporary store for "member"."authority_login" during member account creation';
350 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';
351 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
354 CREATE TYPE "defeat_strength" AS ENUM ('simple', 'tuple');
356 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';
359 CREATE TYPE "tie_breaking" AS ENUM ('simple', 'variant1', 'variant2');
361 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';
364 CREATE TABLE "policy" (
365 "id" SERIAL4 PRIMARY KEY,
366 "index" INT4 NOT NULL,
367 "active" BOOLEAN NOT NULL DEFAULT TRUE,
368 "name" TEXT NOT NULL UNIQUE,
369 "description" TEXT NOT NULL DEFAULT '',
370 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
371 "min_admission_time" INTERVAL,
372 "max_admission_time" INTERVAL,
373 "discussion_time" INTERVAL,
374 "verification_time" INTERVAL,
375 "voting_time" INTERVAL,
376 "issue_quorum" INT4 NOT NULL,
377 "initiative_quorum_num" INT4 NOT NULL,
378 "initiative_quorum_den" INT4 NOT NULL,
379 "defeat_strength" "defeat_strength" NOT NULL DEFAULT 'tuple',
380 "tie_breaking" "tie_breaking" NOT NULL DEFAULT 'variant1',
381 "direct_majority_num" INT4 NOT NULL DEFAULT 1,
382 "direct_majority_den" INT4 NOT NULL DEFAULT 2,
383 "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
384 "direct_majority_positive" INT4 NOT NULL DEFAULT 0,
385 "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0,
386 "indirect_majority_num" INT4 NOT NULL DEFAULT 1,
387 "indirect_majority_den" INT4 NOT NULL DEFAULT 2,
388 "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
389 "indirect_majority_positive" INT4 NOT NULL DEFAULT 0,
390 "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0,
391 "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT FALSE,
392 "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE,
393 CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK (
394 "polling" = ("issue_quorum" ISNULL) ),
395 CONSTRAINT "min_admission_time_smaller_than_max_admission_time" CHECK (
396 "min_admission_time" < "max_admission_time" ),
397 CONSTRAINT "timing_null_or_not_null_constraints" CHECK (
398 ( "polling" = FALSE AND
399 "min_admission_time" NOTNULL AND "max_admission_time" NOTNULL AND
400 "discussion_time" NOTNULL AND
401 "verification_time" NOTNULL AND
402 "voting_time" NOTNULL ) OR
403 ( "polling" = TRUE AND
404 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
405 "discussion_time" NOTNULL AND
406 "verification_time" NOTNULL AND
407 "voting_time" NOTNULL ) OR
408 ( "polling" = TRUE AND
409 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
410 "discussion_time" ISNULL AND
411 "verification_time" ISNULL AND
412 "voting_time" ISNULL ) ),
413 CONSTRAINT "no_reverse_beat_path_requires_tuple_defeat_strength" CHECK (
414 "defeat_strength" = 'tuple'::"defeat_strength" OR
415 "no_reverse_beat_path" = FALSE ) );
416 CREATE INDEX "policy_active_idx" ON "policy" ("active");
418 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
420 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
421 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
422 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';
423 COMMENT ON COLUMN "policy"."min_admission_time" IS 'Minimum duration of issue state ''admission''; Minimum time an issue stays open; Note: should be considerably smaller than "max_admission_time"';
424 COMMENT ON COLUMN "policy"."max_admission_time" IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';
425 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"';
426 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"';
427 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'')';
428 COMMENT ON COLUMN "policy"."issue_quorum" IS 'Minimum number of supporters needed for one initiative of an issue to allow the issue to pass from ''admission'' to ''discussion'' state (Note: further requirements apply, see tables "admission_rule" and "admission_rule_condition")';
429 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
430 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
431 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';
432 COMMENT ON COLUMN "policy"."tie_breaking" IS 'Tie-breaker for the Schulze method; see type "tie_breaking"; ''variant1'' or ''variant2'' are recommended';
433 COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner';
434 COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner';
435 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.';
436 COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner';
437 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';
438 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';
439 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';
440 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.';
441 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';
442 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';
443 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.';
444 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").';
447 CREATE TABLE "unit" (
448 "id" SERIAL4 PRIMARY KEY,
449 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
450 "active" BOOLEAN NOT NULL DEFAULT TRUE,
451 "name" TEXT NOT NULL,
452 "description" TEXT NOT NULL DEFAULT '',
453 "external_reference" TEXT,
454 "member_count" INT4,
455 "region" ECLUSTER,
456 "text_search_data" TSVECTOR );
457 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
458 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
459 CREATE INDEX "unit_active_idx" ON "unit" ("active");
460 CREATE INDEX "unit_region_idx" ON "unit" USING gist ("region");
461 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
462 CREATE TRIGGER "update_text_search_data"
463 BEFORE INSERT OR UPDATE ON "unit"
464 FOR EACH ROW EXECUTE PROCEDURE
465 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
466 "name", "description" );
468 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
470 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
471 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this unit';
472 COMMENT ON COLUMN "unit"."external_reference" IS 'Opaque data field to store an external reference';
473 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege" (only active members counted)';
474 COMMENT ON COLUMN "unit"."region" IS 'Scattered (or hollow) polygon represented as an array of polygons indicating valid coordinates for initiatives of issues with this policy';
477 CREATE TABLE "subscription" (
478 PRIMARY KEY ("member_id", "unit_id"),
479 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
480 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
481 CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id");
483 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';
486 CREATE TABLE "unit_setting" (
487 PRIMARY KEY ("member_id", "key", "unit_id"),
488 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
489 "key" TEXT NOT NULL,
490 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
491 "value" TEXT NOT NULL );
493 COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings';
496 CREATE TABLE "area" (
497 UNIQUE ("unit_id", "id"), -- index needed for foreign-key on table "admission_rule_condition"
498 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
499 "id" SERIAL4 PRIMARY KEY,
500 "active" BOOLEAN NOT NULL DEFAULT TRUE,
501 "name" TEXT NOT NULL,
502 "description" TEXT NOT NULL DEFAULT '',
503 "external_reference" TEXT,
504 "region" ECLUSTER,
505 "text_search_data" TSVECTOR );
506 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
507 CREATE INDEX "area_active_idx" ON "area" ("active");
508 CREATE INDEX "area_region_idx" ON "area" USING gist ("region");
509 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
510 CREATE TRIGGER "update_text_search_data"
511 BEFORE INSERT OR UPDATE ON "area"
512 FOR EACH ROW EXECUTE PROCEDURE
513 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
514 "name", "description" );
516 COMMENT ON TABLE "area" IS 'Subject areas';
518 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
519 COMMENT ON COLUMN "area"."external_reference" IS 'Opaque data field to store an external reference';
520 COMMENT ON COLUMN "area"."region" IS 'Scattered (or hollow) polygon represented as an array of polygons indicating valid coordinates for initiatives of issues with this policy';
523 CREATE TABLE "ignored_area" (
524 PRIMARY KEY ("member_id", "area_id"),
525 "member_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
526 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
527 CREATE INDEX "ignored_area_area_id_idx" ON "ignored_area" ("area_id");
529 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';
532 CREATE TABLE "area_setting" (
533 PRIMARY KEY ("member_id", "key", "area_id"),
534 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
535 "key" TEXT NOT NULL,
536 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
537 "value" TEXT NOT NULL );
539 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
542 CREATE TABLE "allowed_policy" (
543 PRIMARY KEY ("area_id", "policy_id"),
544 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
545 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
546 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
547 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
549 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
551 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
554 CREATE TABLE "admission_rule" (
555 UNIQUE ("unit_id", "id"), -- index needed for foreign-key on table "admission_rule_condition"
556 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
557 "id" SERIAL4 PRIMARY KEY,
558 "name" TEXT NOT NULL,
559 "description" TEXT NOT NULL DEFAULT '' );
561 COMMENT ON TABLE "admission_rule" IS 'Groups entries in "admission_rule_condition" to regulate how many issues may pass from ''admission'' to ''discussion'' state in a given time';
564 CREATE TABLE "admission_rule_condition" (
565 "unit_id" INT4 NOT NULL,
566 "admission_rule_id" INT4,
567 FOREIGN KEY ("unit_id", "admission_rule_id") REFERENCES "admission_rule" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
568 "policy_id" INT4 REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
569 "area_id" INT4,
570 FOREIGN KEY ("unit_id", "area_id") REFERENCES "area" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
571 "holdoff_time" INTERVAL NOT NULL );
572 CREATE UNIQUE INDEX "admission_rule_condition_unit_idx" ON "admission_rule_condition" ("admission_rule_id") WHERE "policy_id" ISNULL AND "area_id" ISNULL;
573 CREATE UNIQUE INDEX "admission_rule_condition_policy_idx" ON "admission_rule_condition" ("admission_rule_id", "policy_id") WHERE "area_id" ISNULL;
574 CREATE UNIQUE INDEX "admission_rule_condition_area_idx" ON "admission_rule_condition" ("admission_rule_id", "area_id") WHERE "policy_id" ISNULL;
575 CREATE UNIQUE INDEX "admission_rule_condition_policy_area_idx" ON "admission_rule_condition" ("admission_rule_id", "policy_id", "area_id");
577 COMMENT ON TABLE "admission_rule_condition" IS 'Regulates how many issues may pass from ''admission'' to ''discussion'' state in a given time; See definition of "issue_for_admission" view for details';
579 COMMENT ON COLUMN "admission_rule_condition"."unit_id" IS 'Grouped "admission_rule_condition" rows must have the same "unit_id"';
580 COMMENT ON COLUMN "admission_rule_condition"."admission_rule_id" IS 'Grouping several "admission_rule_condition" rows';
581 COMMENT ON COLUMN "admission_rule_condition"."policy_id" IS 'Set to link the condition with a given policy, NULL for any policy in the issue';
582 COMMENT ON COLUMN "admission_rule_condition"."area_id" IS 'Set to link the condition with a given policy, NULL for any area in the issue';
583 COMMENT ON COLUMN "admission_rule_condition"."holdoff_time" IS 'After an issue in the given unit, policy, and/or area has been admitted, the "admission_rule" is disabled for the selected "holdoff_time", e.g. a "holdoff_time" of ''6 hours'' causes four issues per day to be admitted';
586 CREATE TABLE "snapshot" (
587 "id" SERIAL8 PRIMARY KEY,
588 "calculated" TIMESTAMPTZ NOT NULL DEFAULT now() );
590 COMMENT ON TABLE "snapshot" IS 'Point in time when a snapshot of one or more issues (see table "snapshot_issue") and their supporter situation is taken';
593 CREATE TYPE "issue_state" AS ENUM (
594 'admission', 'discussion', 'verification', 'voting',
595 'canceled_by_admin',
596 'canceled_revoked_before_accepted',
597 'canceled_issue_not_accepted',
598 'canceled_after_revocation_during_discussion',
599 'canceled_after_revocation_during_verification',
600 'canceled_no_initiative_admitted',
601 'finished_without_winner', 'finished_with_winner');
603 COMMENT ON TYPE "issue_state" IS 'State of issues';
606 CREATE TABLE "issue" (
607 "id" SERIAL4 PRIMARY KEY,
608 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
609 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
610 "admin_notice" TEXT,
611 "external_reference" TEXT,
612 "state" "issue_state" NOT NULL DEFAULT 'admission',
613 "phase_finished" TIMESTAMPTZ,
614 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
615 "accepted" TIMESTAMPTZ,
616 "half_frozen" TIMESTAMPTZ,
617 "fully_frozen" TIMESTAMPTZ,
618 "closed" TIMESTAMPTZ,
619 "cleaned" TIMESTAMPTZ,
620 "min_admission_time" INTERVAL,
621 "max_admission_time" INTERVAL,
622 "discussion_time" INTERVAL NOT NULL,
623 "verification_time" INTERVAL NOT NULL,
624 "voting_time" INTERVAL NOT NULL,
625 "latest_snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
626 "admission_snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
627 "half_freeze_snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
628 "full_freeze_snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
629 "population" INT4,
630 "voter_count" INT4,
631 "status_quo_schulze_rank" INT4,
632 CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (
633 ("min_admission_time" NOTNULL) = ("max_admission_time" NOTNULL) AND
634 ("min_admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created")) ),
635 CONSTRAINT "valid_state" CHECK (
636 (
637 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
638 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
639 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL ) OR
640 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL)
641 ) AND (
642 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
643 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
644 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
645 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
646 ("state" = 'canceled_by_admin' AND "closed" NOTNULL) OR
647 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
648 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
649 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
650 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
651 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" = "fully_frozen") OR
652 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen") OR
653 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen")
654 )),
655 CONSTRAINT "phase_finished_only_when_not_closed" CHECK (
656 "phase_finished" ISNULL OR "closed" ISNULL ),
657 CONSTRAINT "state_change_order" CHECK (
658 "created" <= "accepted" AND
659 "accepted" <= "half_frozen" AND
660 "half_frozen" <= "fully_frozen" AND
661 "fully_frozen" <= "closed" ),
662 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
663 "cleaned" ISNULL OR "closed" NOTNULL ),
664 CONSTRAINT "snapshot_required" CHECK (
665 --("accepted" ISNULL OR "admission_snapshot_id" NOTNULL) AND
666 ("half_frozen" ISNULL OR "half_freeze_snapshot_id" NOTNULL) AND
667 ("fully_frozen" ISNULL OR "full_freeze_snapshot_id" NOTNULL) ) );
668 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
669 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
670 CREATE INDEX "issue_state_idx" ON "issue" ("state");
671 CREATE INDEX "issue_created_idx" ON "issue" ("created");
672 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
673 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
674 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
675 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
676 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
677 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
678 CREATE INDEX "issue_latest_snapshot_id" ON "issue" ("latest_snapshot_id");
679 CREATE INDEX "issue_admission_snapshot_id" ON "issue" ("admission_snapshot_id");
680 CREATE INDEX "issue_half_freeze_snapshot_id" ON "issue" ("half_freeze_snapshot_id");
681 CREATE INDEX "issue_full_freeze_snapshot_id" ON "issue" ("full_freeze_snapshot_id");
683 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
685 COMMENT ON COLUMN "issue"."admin_notice" IS 'Public notice by admin to explain manual interventions, or to announce corrections';
686 COMMENT ON COLUMN "issue"."external_reference" IS 'Opaque data field to store an external reference';
687 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';
688 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when the issue was accepted for further discussion (see table "admission_rule" and column "issue_quorum" of table "policy")';
689 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.';
690 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.';
691 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.';
692 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
693 COMMENT ON COLUMN "issue"."min_admission_time" IS 'Copied from "policy" table at creation of issue';
694 COMMENT ON COLUMN "issue"."max_admission_time" IS 'Copied from "policy" table at creation of issue';
695 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
696 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
697 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
698 COMMENT ON COLUMN "issue"."latest_snapshot_id" IS 'Snapshot id of most recent snapshot';
699 COMMENT ON COLUMN "issue"."admission_snapshot_id" IS 'Snapshot id when issue as accepted or canceled in admission phase';
700 COMMENT ON COLUMN "issue"."half_freeze_snapshot_id" IS 'Snapshot id at end of discussion phase';
701 COMMENT ON COLUMN "issue"."full_freeze_snapshot_id" IS 'Snapshot id at end of verification phase';
702 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
703 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';
704 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
707 CREATE TABLE "issue_order_in_admission_state" (
708 "id" INT8 PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
709 "order_in_area" INT4,
710 "order_in_unit" INT4 );
712 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"';
714 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';
715 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';
716 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';
719 CREATE TABLE "issue_setting" (
720 PRIMARY KEY ("member_id", "key", "issue_id"),
721 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
722 "key" TEXT NOT NULL,
723 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
724 "value" TEXT NOT NULL );
726 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
729 CREATE TABLE "initiative" (
730 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
731 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
732 "id" SERIAL4 PRIMARY KEY,
733 "name" TEXT NOT NULL,
734 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
735 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
736 "revoked" TIMESTAMPTZ,
737 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
738 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
739 "location1" EPOINT,
740 "location2" EPOINT,
741 "external_reference" TEXT,
742 "admitted" BOOLEAN,
743 "supporter_count" INT4,
744 "informed_supporter_count" INT4,
745 "satisfied_supporter_count" INT4,
746 "satisfied_informed_supporter_count" INT4,
747 "harmonic_weight" NUMERIC(12, 3),
748 "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE,
749 "first_preference_votes" INT4,
750 "positive_votes" INT4,
751 "negative_votes" INT4,
752 "direct_majority" BOOLEAN,
753 "indirect_majority" BOOLEAN,
754 "schulze_rank" INT4,
755 "better_than_status_quo" BOOLEAN,
756 "worse_than_status_quo" BOOLEAN,
757 "reverse_beat_path" BOOLEAN,
758 "multistage_majority" BOOLEAN,
759 "eligible" BOOLEAN,
760 "winner" BOOLEAN,
761 "rank" INT4,
762 "text_search_data" TSVECTOR,
763 "draft_text_search_data" TSVECTOR,
764 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
765 CHECK (("revoked" NOTNULL) = ("revoked_by_member_id" NOTNULL)),
766 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
767 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
768 CONSTRAINT "location2_requires_location1"
769 CHECK ("location2" ISNULL OR "location1" NOTNULL),
770 CONSTRAINT "revoked_initiatives_cant_be_admitted"
771 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
772 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
773 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
774 ( "first_preference_votes" ISNULL AND
775 "positive_votes" ISNULL AND "negative_votes" ISNULL AND
776 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
777 "schulze_rank" ISNULL AND
778 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
779 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
780 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ),
781 CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")),
782 CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (
783 "eligible" = FALSE OR
784 ("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ),
785 CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE),
786 CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1),
787 CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE),
788 CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") );
789 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
790 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
791 CREATE INDEX "initiative_location1_idx" ON "initiative" USING gist ("location1");
792 CREATE INDEX "initiative_location2_idx" ON "initiative" USING gist ("location2");
793 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
794 CREATE INDEX "initiative_draft_text_search_data_idx" ON "initiative" USING gin ("draft_text_search_data");
795 CREATE TRIGGER "update_text_search_data"
796 BEFORE INSERT OR UPDATE ON "initiative"
797 FOR EACH ROW EXECUTE PROCEDURE
798 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "name");
800 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.';
802 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative does not need to pass the initiative quorum (see "policy"."polling")';
803 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
804 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoke the initiative';
805 COMMENT ON COLUMN "initiative"."location1" IS 'Geographic location of initiative (automatically copied from most recent draft)';
806 COMMENT ON COLUMN "initiative"."location2" IS 'Geographic location of initiative''s second marker (automatically copied from most recent draft)';
807 COMMENT ON COLUMN "initiative"."external_reference" IS 'Opaque data field to store an external reference';
808 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
809 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
810 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
811 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
812 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
813 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';
814 COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time';
815 COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice';
816 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Number of direct and delegating voters who ranked this initiative better than the status quo';
817 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo';
818 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"';
819 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths';
820 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking';
821 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo';
822 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)';
823 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''';
824 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';
825 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"';
826 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank"';
827 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';
830 CREATE TABLE "battle" (
831 "issue_id" INT4 NOT NULL,
832 "winning_initiative_id" INT4,
833 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
834 "losing_initiative_id" INT4,
835 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
836 "count" INT4 NOT NULL,
837 CONSTRAINT "initiative_ids_not_equal" CHECK (
838 "winning_initiative_id" != "losing_initiative_id" OR
839 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
840 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) );
841 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
842 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
843 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
845 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';
848 CREATE TABLE "ignored_initiative" (
849 PRIMARY KEY ("member_id", "initiative_id"),
850 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
851 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
852 CREATE INDEX "ignored_initiative_initiative_id_idx" ON "ignored_initiative" ("initiative_id");
854 COMMENT ON TABLE "ignored_initiative" IS 'An entry in this table denotes that the member does not wish to receive notifications for the given initiative';
857 CREATE TABLE "initiative_setting" (
858 PRIMARY KEY ("member_id", "key", "initiative_id"),
859 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
860 "key" TEXT NOT NULL,
861 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
862 "value" TEXT NOT NULL );
864 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
867 CREATE TABLE "draft" (
868 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
869 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
870 "id" SERIAL8 PRIMARY KEY,
871 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
872 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
873 "formatting_engine" TEXT,
874 "content" TEXT NOT NULL,
875 "location1" EPOINT,
876 "location2" EPOINT,
877 "external_reference" TEXT,
878 "text_search_data" TSVECTOR,
879 CONSTRAINT "location2_requires_location1"
880 CHECK ("location2" ISNULL OR "location1" NOTNULL) );
881 CREATE INDEX "draft_created_idx" ON "draft" ("created");
882 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
883 CREATE INDEX "draft_location1_idx" ON "draft" USING gist ("location1");
884 CREATE INDEX "draft_location2_idx" ON "draft" USING gist ("location2");
885 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
886 CREATE TRIGGER "update_text_search_data"
887 BEFORE INSERT OR UPDATE ON "draft"
888 FOR EACH ROW EXECUTE PROCEDURE
889 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
891 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.';
893 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
894 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
895 COMMENT ON COLUMN "draft"."location1" IS 'Geographic location of initiative (automatically copied to "initiative" table if draft is most recent)';
896 COMMENT ON COLUMN "draft"."location2" IS 'Geographic location of initiative''s second marker (automatically copied to "initiative" table if draft is most recent)';
897 COMMENT ON COLUMN "draft"."external_reference" IS 'Opaque data field to store an external reference';
900 CREATE TABLE "rendered_draft" (
901 PRIMARY KEY ("draft_id", "format"),
902 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
903 "format" TEXT,
904 "content" TEXT NOT NULL );
906 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)';
909 CREATE TABLE "suggestion" (
910 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
911 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
912 "id" SERIAL8 PRIMARY KEY,
913 "draft_id" INT8 NOT NULL,
914 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
915 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
916 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
917 "name" TEXT NOT NULL,
918 "formatting_engine" TEXT,
919 "content" TEXT NOT NULL DEFAULT '',
920 "location1" EPOINT,
921 "location2" EPOINT,
922 "external_reference" TEXT,
923 "text_search_data" TSVECTOR,
924 "minus2_unfulfilled_count" INT4,
925 "minus2_fulfilled_count" INT4,
926 "minus1_unfulfilled_count" INT4,
927 "minus1_fulfilled_count" INT4,
928 "plus1_unfulfilled_count" INT4,
929 "plus1_fulfilled_count" INT4,
930 "plus2_unfulfilled_count" INT4,
931 "plus2_fulfilled_count" INT4,
932 "proportional_order" INT4,
933 CONSTRAINT "location2_requires_location1"
934 CHECK ("location2" ISNULL OR "location1" NOTNULL) );
935 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
936 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
937 CREATE INDEX "suggestion_location1_idx" ON "suggestion" USING gist ("location1");
938 CREATE INDEX "suggestion_location2_idx" ON "suggestion" USING gist ("location2");
939 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
940 CREATE TRIGGER "update_text_search_data"
941 BEFORE INSERT OR UPDATE ON "suggestion"
942 FOR EACH ROW EXECUTE PROCEDURE
943 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
944 "name", "content");
946 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';
948 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")';
949 COMMENT ON COLUMN "suggestion"."location1" IS 'Geographic location of suggestion';
950 COMMENT ON COLUMN "suggestion"."location2" IS 'Geographic location of suggestion''s second marker';
951 COMMENT ON COLUMN "suggestion"."external_reference" IS 'Opaque data field to store an external reference';
952 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
953 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
954 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
955 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
956 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
957 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
958 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
959 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
960 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"';
963 CREATE TABLE "rendered_suggestion" (
964 PRIMARY KEY ("suggestion_id", "format"),
965 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
966 "format" TEXT,
967 "content" TEXT NOT NULL );
969 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)';
972 CREATE TABLE "suggestion_setting" (
973 PRIMARY KEY ("member_id", "key", "suggestion_id"),
974 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
975 "key" TEXT NOT NULL,
976 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
977 "value" TEXT NOT NULL );
979 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
982 CREATE TABLE "temporary_suggestion_counts" (
983 "id" INT8 PRIMARY KEY, --REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
984 "minus2_unfulfilled_count" INT4 NOT NULL,
985 "minus2_fulfilled_count" INT4 NOT NULL,
986 "minus1_unfulfilled_count" INT4 NOT NULL,
987 "minus1_fulfilled_count" INT4 NOT NULL,
988 "plus1_unfulfilled_count" INT4 NOT NULL,
989 "plus1_fulfilled_count" INT4 NOT NULL,
990 "plus2_unfulfilled_count" INT4 NOT NULL,
991 "plus2_fulfilled_count" INT4 NOT NULL );
993 COMMENT ON TABLE "temporary_suggestion_counts" IS 'Holds certain calculated values (suggestion counts) temporarily until they can be copied into table "suggestion"';
995 COMMENT ON COLUMN "temporary_suggestion_counts"."id" IS 'References "suggestion" ("id") but has no referential integrity trigger associated, due to performance/locking issues';
998 CREATE TABLE "privilege" (
999 PRIMARY KEY ("unit_id", "member_id"),
1000 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1001 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1002 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
1003 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
1004 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
1005 "member_manager" BOOLEAN NOT NULL DEFAULT FALSE,
1006 "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE,
1007 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE,
1008 "polling_right" BOOLEAN NOT NULL DEFAULT FALSE );
1010 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
1012 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke any privileges to/from other members';
1013 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create and disable sub units';
1014 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create and disable areas and set area parameters';
1015 COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';
1016 COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';
1017 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote';
1018 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';
1021 CREATE TABLE "interest" (
1022 PRIMARY KEY ("issue_id", "member_id"),
1023 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1024 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
1025 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
1027 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.';
1030 CREATE TABLE "initiator" (
1031 PRIMARY KEY ("initiative_id", "member_id"),
1032 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1033 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1034 "accepted" BOOLEAN );
1035 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
1037 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.';
1039 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.';
1042 CREATE TABLE "supporter" (
1043 "issue_id" INT4 NOT NULL,
1044 PRIMARY KEY ("initiative_id", "member_id"),
1045 "initiative_id" INT4,
1046 "member_id" INT4,
1047 "draft_id" INT8 NOT NULL,
1048 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
1049 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE );
1050 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
1052 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.';
1054 COMMENT ON COLUMN "supporter"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
1055 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")';
1058 CREATE TABLE "opinion" (
1059 "initiative_id" INT4 NOT NULL,
1060 PRIMARY KEY ("suggestion_id", "member_id"),
1061 "suggestion_id" INT8,
1062 "member_id" INT4,
1063 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
1064 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
1065 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1066 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1067 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
1069 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.';
1071 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
1074 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
1076 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
1079 CREATE TABLE "delegation" (
1080 "id" SERIAL8 PRIMARY KEY,
1081 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1082 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1083 "scope" "delegation_scope" NOT NULL,
1084 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1085 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1086 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1087 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
1088 CONSTRAINT "no_unit_delegation_to_null"
1089 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
1090 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
1091 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
1092 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
1093 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
1094 UNIQUE ("unit_id", "truster_id"),
1095 UNIQUE ("area_id", "truster_id"),
1096 UNIQUE ("issue_id", "truster_id") );
1097 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
1098 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
1100 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
1102 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
1103 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
1104 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
1107 CREATE TABLE "snapshot_issue" (
1108 PRIMARY KEY ("snapshot_id", "issue_id"),
1109 "snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1110 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
1111 CREATE INDEX "snapshot_issue_issue_id_idx" ON "snapshot_issue" ("issue_id");
1113 COMMENT ON TABLE "snapshot_issue" IS 'List of issues included in a snapshot';
1116 CREATE TABLE "direct_interest_snapshot" (
1117 PRIMARY KEY ("snapshot_id", "issue_id", "member_id"),
1118 "snapshot_id" INT8,
1119 "issue_id" INT4,
1120 FOREIGN KEY ("snapshot_id", "issue_id")
1121 REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
1122 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1123 "weight" INT4 );
1124 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
1126 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';
1128 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
1131 CREATE TABLE "delegating_interest_snapshot" (
1132 PRIMARY KEY ("snapshot_id", "issue_id", "member_id"),
1133 "snapshot_id" INT8,
1134 "issue_id" INT4,
1135 FOREIGN KEY ("snapshot_id", "issue_id")
1136 REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
1137 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1138 "weight" INT4,
1139 "scope" "delegation_scope" NOT NULL,
1140 "delegate_member_ids" INT4[] NOT NULL );
1141 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
1143 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';
1145 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
1146 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
1147 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"';
1150 CREATE TABLE "direct_supporter_snapshot" (
1151 PRIMARY KEY ("snapshot_id", "initiative_id", "member_id"),
1152 "snapshot_id" INT8,
1153 "issue_id" INT4 NOT NULL,
1154 FOREIGN KEY ("snapshot_id", "issue_id")
1155 REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
1156 "initiative_id" INT4,
1157 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1158 "draft_id" INT8 NOT NULL,
1159 "informed" BOOLEAN NOT NULL,
1160 "satisfied" BOOLEAN NOT NULL,
1161 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1162 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
1163 FOREIGN KEY ("snapshot_id", "issue_id", "member_id") REFERENCES "direct_interest_snapshot" ("snapshot_id", "issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1164 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
1166 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';
1168 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';
1169 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
1170 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
1173 CREATE TABLE "non_voter" (
1174 PRIMARY KEY ("member_id", "issue_id"),
1175 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1176 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
1177 CREATE INDEX "non_voter_issue_id_idx" ON "non_voter" ("issue_id");
1179 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
1182 CREATE TABLE "direct_voter" (
1183 PRIMARY KEY ("issue_id", "member_id"),
1184 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1185 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1186 "weight" INT4,
1187 "comment_changed" TIMESTAMPTZ,
1188 "formatting_engine" TEXT,
1189 "comment" TEXT,
1190 "text_search_data" TSVECTOR );
1191 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
1192 CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data");
1193 CREATE TRIGGER "update_text_search_data"
1194 BEFORE INSERT OR UPDATE ON "direct_voter"
1195 FOR EACH ROW EXECUTE PROCEDURE
1196 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment");
1198 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';
1200 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
1201 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';
1202 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';
1203 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.';
1206 CREATE TABLE "rendered_voter_comment" (
1207 PRIMARY KEY ("issue_id", "member_id", "format"),
1208 FOREIGN KEY ("issue_id", "member_id")
1209 REFERENCES "direct_voter" ("issue_id", "member_id")
1210 ON DELETE CASCADE ON UPDATE CASCADE,
1211 "issue_id" INT4,
1212 "member_id" INT4,
1213 "format" TEXT,
1214 "content" TEXT NOT NULL );
1216 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)';
1219 CREATE TABLE "delegating_voter" (
1220 PRIMARY KEY ("issue_id", "member_id"),
1221 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1222 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1223 "weight" INT4,
1224 "scope" "delegation_scope" NOT NULL,
1225 "delegate_member_ids" INT4[] NOT NULL );
1226 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
1228 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';
1230 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
1231 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
1232 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"';
1235 CREATE TABLE "vote" (
1236 "issue_id" INT4 NOT NULL,
1237 PRIMARY KEY ("initiative_id", "member_id"),
1238 "initiative_id" INT4,
1239 "member_id" INT4,
1240 "grade" INT4 NOT NULL,
1241 "first_preference" BOOLEAN,
1242 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1243 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
1244 CONSTRAINT "first_preference_flag_only_set_on_positive_grades"
1245 CHECK ("grade" > 0 OR "first_preference" ISNULL) );
1246 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
1248 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';
1250 COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
1251 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.';
1252 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.';
1255 CREATE TYPE "event_type" AS ENUM (
1256 'issue_state_changed',
1257 'initiative_created_in_new_issue',
1258 'initiative_created_in_existing_issue',
1259 'initiative_revoked',
1260 'new_draft_created',
1261 'suggestion_created');
1263 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
1266 CREATE TABLE "event" (
1267 "id" SERIAL8 PRIMARY KEY,
1268 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
1269 "event" "event_type" NOT NULL,
1270 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1271 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1272 "state" "issue_state",
1273 "initiative_id" INT4,
1274 "draft_id" INT8,
1275 "suggestion_id" INT8,
1276 FOREIGN KEY ("issue_id", "initiative_id")
1277 REFERENCES "initiative" ("issue_id", "id")
1278 ON DELETE CASCADE ON UPDATE CASCADE,
1279 FOREIGN KEY ("initiative_id", "draft_id")
1280 REFERENCES "draft" ("initiative_id", "id")
1281 ON DELETE CASCADE ON UPDATE CASCADE,
1282 FOREIGN KEY ("initiative_id", "suggestion_id")
1283 REFERENCES "suggestion" ("initiative_id", "id")
1284 ON DELETE CASCADE ON UPDATE CASCADE,
1285 CONSTRAINT "null_constr_for_issue_state_changed" CHECK (
1286 "event" != 'issue_state_changed' OR (
1287 "member_id" ISNULL AND
1288 "issue_id" NOTNULL AND
1289 "state" NOTNULL AND
1290 "initiative_id" ISNULL AND
1291 "draft_id" ISNULL AND
1292 "suggestion_id" ISNULL )),
1293 CONSTRAINT "null_constr_for_initiative_creation_or_revocation_or_new_draft" CHECK (
1294 "event" NOT IN (
1295 'initiative_created_in_new_issue',
1296 'initiative_created_in_existing_issue',
1297 'initiative_revoked',
1298 'new_draft_created'
1299 ) OR (
1300 "member_id" NOTNULL AND
1301 "issue_id" NOTNULL AND
1302 "state" NOTNULL AND
1303 "initiative_id" NOTNULL AND
1304 "draft_id" NOTNULL AND
1305 "suggestion_id" ISNULL )),
1306 CONSTRAINT "null_constr_for_suggestion_creation" CHECK (
1307 "event" != 'suggestion_created' OR (
1308 "member_id" NOTNULL AND
1309 "issue_id" NOTNULL AND
1310 "state" NOTNULL AND
1311 "initiative_id" NOTNULL AND
1312 "draft_id" ISNULL AND
1313 "suggestion_id" NOTNULL )) );
1314 CREATE INDEX "event_occurrence_idx" ON "event" ("occurrence");
1316 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
1318 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
1319 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
1320 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
1321 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
1324 CREATE TABLE "notification_event_sent" (
1325 "event_id" INT8 NOT NULL );
1326 CREATE UNIQUE INDEX "notification_event_sent_singleton_idx" ON "notification_event_sent" ((1));
1328 COMMENT ON TABLE "notification_event_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';
1329 COMMENT ON INDEX "notification_event_sent_singleton_idx" IS 'This index ensures that "notification_event_sent" only contains one row maximum.';
1332 CREATE TABLE "notification_initiative_sent" (
1333 PRIMARY KEY ("member_id", "initiative_id"),
1334 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1335 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1336 "last_draft_id" INT8 NOT NULL,
1337 "last_suggestion_id" INT8 );
1338 CREATE INDEX "notification_initiative_sent_initiative_idx" ON "notification_initiative_sent" ("initiative_id");
1340 COMMENT ON TABLE "notification_initiative_sent" IS 'Information which initiatives have been promoted to a member in a scheduled notification mail';
1342 COMMENT ON COLUMN "notification_initiative_sent"."last_draft_id" IS 'Current (i.e. last) draft_id when initiative had been promoted';
1343 COMMENT ON COLUMN "notification_initiative_sent"."last_suggestion_id" IS 'Current (i.e. last) draft_id when initiative had been promoted';
1346 CREATE TABLE "newsletter" (
1347 "id" SERIAL4 PRIMARY KEY,
1348 "published" TIMESTAMPTZ NOT NULL,
1349 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1350 "include_all_members" BOOLEAN NOT NULL,
1351 "sent" TIMESTAMPTZ,
1352 "subject" TEXT NOT NULL,
1353 "content" TEXT NOT NULL );
1354 CREATE INDEX "newsletter_unit_id_idx" ON "newsletter" ("unit_id", "published");
1355 CREATE INDEX "newsletter_all_units_published_idx" ON "newsletter" ("published") WHERE "unit_id" ISNULL;
1356 CREATE INDEX "newsletter_published_idx" ON "newsletter" ("published");
1358 COMMENT ON TABLE "newsletter" IS 'Contains newsletters created by administrators to be sent out and for further reference';
1360 COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter is to be sent out (and made available in the frontend)';
1361 COMMENT ON COLUMN "newsletter"."unit_id" IS 'If set, only members with voting right in the given unit are considered to be recipients';
1362 COMMENT ON COLUMN "newsletter"."include_all_members" IS 'TRUE = include all members regardless of their ''disable_notifications'' setting';
1363 COMMENT ON COLUMN "newsletter"."sent" IS 'Timestamp when the newsletter has been mailed out';
1364 COMMENT ON COLUMN "newsletter"."subject" IS 'Subject line (e.g. to be used for the email)';
1365 COMMENT ON COLUMN "newsletter"."content" IS 'Plain text content of the newsletter';
1369 ----------------------------------------------
1370 -- Writing of history entries and event log --
1371 ----------------------------------------------
1374 CREATE FUNCTION "write_member_history_trigger"()
1375 RETURNS TRIGGER
1376 LANGUAGE 'plpgsql' VOLATILE AS $$
1377 BEGIN
1378 IF
1379 ( NEW."active" != OLD."active" OR
1380 NEW."name" != OLD."name" ) AND
1381 OLD."activated" NOTNULL
1382 THEN
1383 INSERT INTO "member_history"
1384 ("member_id", "active", "name")
1385 VALUES (NEW."id", OLD."active", OLD."name");
1386 END IF;
1387 RETURN NULL;
1388 END;
1389 $$;
1391 CREATE TRIGGER "write_member_history"
1392 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1393 "write_member_history_trigger"();
1395 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1396 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1399 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1400 RETURNS TRIGGER
1401 LANGUAGE 'plpgsql' VOLATILE AS $$
1402 BEGIN
1403 IF NEW."state" != OLD."state" THEN
1404 INSERT INTO "event" ("event", "issue_id", "state")
1405 VALUES ('issue_state_changed', NEW."id", NEW."state");
1406 END IF;
1407 RETURN NULL;
1408 END;
1409 $$;
1411 CREATE TRIGGER "write_event_issue_state_changed"
1412 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1413 "write_event_issue_state_changed_trigger"();
1415 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1416 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1419 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1420 RETURNS TRIGGER
1421 LANGUAGE 'plpgsql' VOLATILE AS $$
1422 DECLARE
1423 "initiative_row" "initiative"%ROWTYPE;
1424 "issue_row" "issue"%ROWTYPE;
1425 "event_v" "event_type";
1426 BEGIN
1427 SELECT * INTO "initiative_row" FROM "initiative"
1428 WHERE "id" = NEW."initiative_id";
1429 SELECT * INTO "issue_row" FROM "issue"
1430 WHERE "id" = "initiative_row"."issue_id";
1431 IF EXISTS (
1432 SELECT NULL FROM "draft"
1433 WHERE "initiative_id" = NEW."initiative_id"
1434 AND "id" != NEW."id"
1435 ) THEN
1436 "event_v" := 'new_draft_created';
1437 ELSE
1438 IF EXISTS (
1439 SELECT NULL FROM "initiative"
1440 WHERE "issue_id" = "initiative_row"."issue_id"
1441 AND "id" != "initiative_row"."id"
1442 ) THEN
1443 "event_v" := 'initiative_created_in_existing_issue';
1444 ELSE
1445 "event_v" := 'initiative_created_in_new_issue';
1446 END IF;
1447 END IF;
1448 INSERT INTO "event" (
1449 "event", "member_id",
1450 "issue_id", "state", "initiative_id", "draft_id"
1451 ) VALUES (
1452 "event_v",
1453 NEW."author_id",
1454 "initiative_row"."issue_id",
1455 "issue_row"."state",
1456 "initiative_row"."id",
1457 NEW."id" );
1458 RETURN NULL;
1459 END;
1460 $$;
1462 CREATE TRIGGER "write_event_initiative_or_draft_created"
1463 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1464 "write_event_initiative_or_draft_created_trigger"();
1466 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1467 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1470 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1471 RETURNS TRIGGER
1472 LANGUAGE 'plpgsql' VOLATILE AS $$
1473 DECLARE
1474 "issue_row" "issue"%ROWTYPE;
1475 "draft_id_v" "draft"."id"%TYPE;
1476 BEGIN
1477 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1478 SELECT * INTO "issue_row" FROM "issue"
1479 WHERE "id" = NEW."issue_id";
1480 SELECT "id" INTO "draft_id_v" FROM "current_draft"
1481 WHERE "initiative_id" = NEW."id";
1482 INSERT INTO "event" (
1483 "event", "member_id", "issue_id", "state", "initiative_id", "draft_id"
1484 ) VALUES (
1485 'initiative_revoked',
1486 NEW."revoked_by_member_id",
1487 NEW."issue_id",
1488 "issue_row"."state",
1489 NEW."id",
1490 "draft_id_v");
1491 END IF;
1492 RETURN NULL;
1493 END;
1494 $$;
1496 CREATE TRIGGER "write_event_initiative_revoked"
1497 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1498 "write_event_initiative_revoked_trigger"();
1500 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1501 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1504 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1505 RETURNS TRIGGER
1506 LANGUAGE 'plpgsql' VOLATILE AS $$
1507 DECLARE
1508 "initiative_row" "initiative"%ROWTYPE;
1509 "issue_row" "issue"%ROWTYPE;
1510 BEGIN
1511 SELECT * INTO "initiative_row" FROM "initiative"
1512 WHERE "id" = NEW."initiative_id";
1513 SELECT * INTO "issue_row" FROM "issue"
1514 WHERE "id" = "initiative_row"."issue_id";
1515 INSERT INTO "event" (
1516 "event", "member_id",
1517 "issue_id", "state", "initiative_id", "suggestion_id"
1518 ) VALUES (
1519 'suggestion_created',
1520 NEW."author_id",
1521 "initiative_row"."issue_id",
1522 "issue_row"."state",
1523 "initiative_row"."id",
1524 NEW."id" );
1525 RETURN NULL;
1526 END;
1527 $$;
1529 CREATE TRIGGER "write_event_suggestion_created"
1530 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1531 "write_event_suggestion_created_trigger"();
1533 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1534 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1538 ----------------------------
1539 -- Additional constraints --
1540 ----------------------------
1543 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1544 RETURNS TRIGGER
1545 LANGUAGE 'plpgsql' VOLATILE AS $$
1546 BEGIN
1547 IF NOT EXISTS (
1548 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1549 ) THEN
1550 RAISE EXCEPTION 'Cannot create issue without an initial initiative.' USING
1551 ERRCODE = 'integrity_constraint_violation',
1552 HINT = 'Create issue, initiative, and draft within the same transaction.';
1553 END IF;
1554 RETURN NULL;
1555 END;
1556 $$;
1558 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1559 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1560 FOR EACH ROW EXECUTE PROCEDURE
1561 "issue_requires_first_initiative_trigger"();
1563 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1564 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1567 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1568 RETURNS TRIGGER
1569 LANGUAGE 'plpgsql' VOLATILE AS $$
1570 DECLARE
1571 "reference_lost" BOOLEAN;
1572 BEGIN
1573 IF TG_OP = 'DELETE' THEN
1574 "reference_lost" := TRUE;
1575 ELSE
1576 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1577 END IF;
1578 IF
1579 "reference_lost" AND NOT EXISTS (
1580 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1581 )
1582 THEN
1583 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1584 END IF;
1585 RETURN NULL;
1586 END;
1587 $$;
1589 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1590 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1591 FOR EACH ROW EXECUTE PROCEDURE
1592 "last_initiative_deletes_issue_trigger"();
1594 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1595 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1598 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1599 RETURNS TRIGGER
1600 LANGUAGE 'plpgsql' VOLATILE AS $$
1601 BEGIN
1602 IF NOT EXISTS (
1603 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1604 ) THEN
1605 RAISE EXCEPTION 'Cannot create initiative without an initial draft.' USING
1606 ERRCODE = 'integrity_constraint_violation',
1607 HINT = 'Create issue, initiative and draft within the same transaction.';
1608 END IF;
1609 RETURN NULL;
1610 END;
1611 $$;
1613 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1614 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1615 FOR EACH ROW EXECUTE PROCEDURE
1616 "initiative_requires_first_draft_trigger"();
1618 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1619 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1622 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1623 RETURNS TRIGGER
1624 LANGUAGE 'plpgsql' VOLATILE AS $$
1625 DECLARE
1626 "reference_lost" BOOLEAN;
1627 BEGIN
1628 IF TG_OP = 'DELETE' THEN
1629 "reference_lost" := TRUE;
1630 ELSE
1631 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1632 END IF;
1633 IF
1634 "reference_lost" AND NOT EXISTS (
1635 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1636 )
1637 THEN
1638 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1639 END IF;
1640 RETURN NULL;
1641 END;
1642 $$;
1644 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1645 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1646 FOR EACH ROW EXECUTE PROCEDURE
1647 "last_draft_deletes_initiative_trigger"();
1649 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1650 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1653 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1654 RETURNS TRIGGER
1655 LANGUAGE 'plpgsql' VOLATILE AS $$
1656 BEGIN
1657 IF NOT EXISTS (
1658 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1659 ) THEN
1660 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.' USING
1661 ERRCODE = 'integrity_constraint_violation',
1662 HINT = 'Create suggestion and opinion within the same transaction.';
1663 END IF;
1664 RETURN NULL;
1665 END;
1666 $$;
1668 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1669 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1670 FOR EACH ROW EXECUTE PROCEDURE
1671 "suggestion_requires_first_opinion_trigger"();
1673 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1674 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1677 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1678 RETURNS TRIGGER
1679 LANGUAGE 'plpgsql' VOLATILE AS $$
1680 DECLARE
1681 "reference_lost" BOOLEAN;
1682 BEGIN
1683 IF TG_OP = 'DELETE' THEN
1684 "reference_lost" := TRUE;
1685 ELSE
1686 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1687 END IF;
1688 IF
1689 "reference_lost" AND NOT EXISTS (
1690 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1691 )
1692 THEN
1693 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1694 END IF;
1695 RETURN NULL;
1696 END;
1697 $$;
1699 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1700 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1701 FOR EACH ROW EXECUTE PROCEDURE
1702 "last_opinion_deletes_suggestion_trigger"();
1704 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1705 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1708 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()
1709 RETURNS TRIGGER
1710 LANGUAGE 'plpgsql' VOLATILE AS $$
1711 BEGIN
1712 DELETE FROM "direct_voter"
1713 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1714 RETURN NULL;
1715 END;
1716 $$;
1718 CREATE TRIGGER "non_voter_deletes_direct_voter"
1719 AFTER INSERT OR UPDATE ON "non_voter"
1720 FOR EACH ROW EXECUTE PROCEDURE
1721 "non_voter_deletes_direct_voter_trigger"();
1723 COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';
1724 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")';
1727 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()
1728 RETURNS TRIGGER
1729 LANGUAGE 'plpgsql' VOLATILE AS $$
1730 BEGIN
1731 DELETE FROM "non_voter"
1732 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1733 RETURN NULL;
1734 END;
1735 $$;
1737 CREATE TRIGGER "direct_voter_deletes_non_voter"
1738 AFTER INSERT OR UPDATE ON "direct_voter"
1739 FOR EACH ROW EXECUTE PROCEDURE
1740 "direct_voter_deletes_non_voter_trigger"();
1742 COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';
1743 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")';
1746 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()
1747 RETURNS TRIGGER
1748 LANGUAGE 'plpgsql' VOLATILE AS $$
1749 BEGIN
1750 IF NEW."comment" ISNULL THEN
1751 NEW."comment_changed" := NULL;
1752 NEW."formatting_engine" := NULL;
1753 END IF;
1754 RETURN NEW;
1755 END;
1756 $$;
1758 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"
1759 BEFORE INSERT OR UPDATE ON "direct_voter"
1760 FOR EACH ROW EXECUTE PROCEDURE
1761 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();
1763 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"';
1764 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.';
1768 ---------------------------------
1769 -- Delete incomplete snapshots --
1770 ---------------------------------
1773 CREATE FUNCTION "delete_snapshot_on_partial_delete_trigger"()
1774 RETURNS TRIGGER
1775 LANGUAGE 'plpgsql' VOLATILE AS $$
1776 BEGIN
1777 DELETE FROM "snapshot" WHERE "id" = OLD."snapshot_id";
1778 RETURN NULL;
1779 END;
1780 $$;
1782 CREATE TRIGGER "delete_snapshot_on_partial_delete"
1783 AFTER DELETE ON "snapshot_issue"
1784 FOR EACH ROW EXECUTE PROCEDURE
1785 "delete_snapshot_on_partial_delete_trigger"();
1787 COMMENT ON FUNCTION "delete_snapshot_on_partial_delete_trigger"() IS 'Implementation of trigger "delete_snapshot_on_partial_delete" on table "snapshot_issue"';
1788 COMMENT ON TRIGGER "delete_snapshot_on_partial_delete" ON "snapshot_issue" IS 'Deletes whole snapshot if one issue is deleted from the snapshot';
1792 ---------------------------------------------------------------
1793 -- Ensure that votes are not modified when issues are closed --
1794 ---------------------------------------------------------------
1796 -- NOTE: Frontends should ensure this anyway, but in case of programming
1797 -- errors the following triggers ensure data integrity.
1800 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1801 RETURNS TRIGGER
1802 LANGUAGE 'plpgsql' VOLATILE AS $$
1803 DECLARE
1804 "issue_id_v" "issue"."id"%TYPE;
1805 "issue_row" "issue"%ROWTYPE;
1806 BEGIN
1807 IF EXISTS (
1808 SELECT NULL FROM "temporary_transaction_data"
1809 WHERE "txid" = txid_current()
1810 AND "key" = 'override_protection_triggers'
1811 AND "value" = TRUE::TEXT
1812 ) THEN
1813 RETURN NULL;
1814 END IF;
1815 IF TG_OP = 'DELETE' THEN
1816 "issue_id_v" := OLD."issue_id";
1817 ELSE
1818 "issue_id_v" := NEW."issue_id";
1819 END IF;
1820 SELECT INTO "issue_row" * FROM "issue"
1821 WHERE "id" = "issue_id_v" FOR SHARE;
1822 IF (
1823 "issue_row"."closed" NOTNULL OR (
1824 "issue_row"."state" = 'voting' AND
1825 "issue_row"."phase_finished" NOTNULL
1826 )
1827 ) THEN
1828 IF
1829 TG_RELID = 'direct_voter'::regclass AND
1830 TG_OP = 'UPDATE'
1831 THEN
1832 IF
1833 OLD."issue_id" = NEW."issue_id" AND
1834 OLD."member_id" = NEW."member_id" AND
1835 OLD."weight" = NEW."weight"
1836 THEN
1837 RETURN NULL; -- allows changing of voter comment
1838 END IF;
1839 END IF;
1840 RAISE EXCEPTION 'Tried to modify data after voting has been closed.' USING
1841 ERRCODE = 'integrity_constraint_violation';
1842 END IF;
1843 RETURN NULL;
1844 END;
1845 $$;
1847 CREATE TRIGGER "forbid_changes_on_closed_issue"
1848 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1849 FOR EACH ROW EXECUTE PROCEDURE
1850 "forbid_changes_on_closed_issue_trigger"();
1852 CREATE TRIGGER "forbid_changes_on_closed_issue"
1853 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1854 FOR EACH ROW EXECUTE PROCEDURE
1855 "forbid_changes_on_closed_issue_trigger"();
1857 CREATE TRIGGER "forbid_changes_on_closed_issue"
1858 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1859 FOR EACH ROW EXECUTE PROCEDURE
1860 "forbid_changes_on_closed_issue_trigger"();
1862 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"';
1863 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';
1864 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';
1865 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';
1869 --------------------------------------------------------------------
1870 -- Auto-retrieval of fields only needed for referential integrity --
1871 --------------------------------------------------------------------
1874 CREATE FUNCTION "autofill_unit_id_from_admission_rule_trigger"()
1875 RETURNS TRIGGER
1876 LANGUAGE 'plpgsql' VOLATILE AS $$
1877 BEGIN
1878 IF NEW."unit_id" ISNULL THEN
1879 SELECT "unit_id" INTO NEW."unit_id"
1880 FROM "admission_rule" WHERE "id" = NEW."admission_rule_id";
1881 END IF;
1882 RETURN NEW;
1883 END;
1884 $$;
1886 CREATE TRIGGER "autofill_unit_id" BEFORE INSERT ON "admission_rule_condition"
1887 FOR EACH ROW EXECUTE PROCEDURE "autofill_unit_id_from_admission_rule_trigger"();
1889 COMMENT ON FUNCTION "autofill_unit_id_from_admission_rule_trigger"() IS 'Implementation of trigger "autofill_admission_rule_id" on table "admission_rule_entry"';
1890 COMMENT ON TRIGGER "autofill_unit_id" ON "admission_rule_condition" IS 'Set "unit_id" field automatically, if NULL';
1893 CREATE FUNCTION "autofill_issue_id_trigger"()
1894 RETURNS TRIGGER
1895 LANGUAGE 'plpgsql' VOLATILE AS $$
1896 BEGIN
1897 IF NEW."issue_id" ISNULL THEN
1898 SELECT "issue_id" INTO NEW."issue_id"
1899 FROM "initiative" WHERE "id" = NEW."initiative_id";
1900 END IF;
1901 RETURN NEW;
1902 END;
1903 $$;
1905 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1906 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1908 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1909 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1911 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1912 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1913 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1916 CREATE FUNCTION "autofill_initiative_id_trigger"()
1917 RETURNS TRIGGER
1918 LANGUAGE 'plpgsql' VOLATILE AS $$
1919 BEGIN
1920 IF NEW."initiative_id" ISNULL THEN
1921 SELECT "initiative_id" INTO NEW."initiative_id"
1922 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1923 END IF;
1924 RETURN NEW;
1925 END;
1926 $$;
1928 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1929 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1931 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1932 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1936 -------------------------------------------------------
1937 -- Automatic copying of values for indexing purposes --
1938 -------------------------------------------------------
1941 CREATE FUNCTION "copy_current_draft_data"
1942 ("initiative_id_p" "initiative"."id"%TYPE )
1943 RETURNS VOID
1944 LANGUAGE 'plpgsql' VOLATILE AS $$
1945 BEGIN
1946 PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p"
1947 FOR UPDATE;
1948 UPDATE "initiative" SET
1949 "location1" = "draft"."location1",
1950 "location2" = "draft"."location2",
1951 "draft_text_search_data" = "draft"."text_search_data"
1952 FROM "current_draft" AS "draft"
1953 WHERE "initiative"."id" = "initiative_id_p"
1954 AND "draft"."initiative_id" = "initiative_id_p";
1955 END;
1956 $$;
1958 COMMENT ON FUNCTION "copy_current_draft_data"
1959 ( "initiative"."id"%TYPE )
1960 IS 'Helper function for function "copy_current_draft_data_trigger"';
1963 CREATE FUNCTION "copy_current_draft_data_trigger"()
1964 RETURNS TRIGGER
1965 LANGUAGE 'plpgsql' VOLATILE AS $$
1966 BEGIN
1967 IF TG_OP='DELETE' THEN
1968 PERFORM "copy_current_draft_data"(OLD."initiative_id");
1969 ELSE
1970 IF TG_OP='UPDATE' THEN
1971 IF COALESCE(OLD."inititiave_id" != NEW."initiative_id", TRUE) THEN
1972 PERFORM "copy_current_draft_data"(OLD."initiative_id");
1973 END IF;
1974 END IF;
1975 PERFORM "copy_current_draft_data"(NEW."initiative_id");
1976 END IF;
1977 RETURN NULL;
1978 END;
1979 $$;
1981 CREATE TRIGGER "copy_current_draft_data"
1982 AFTER INSERT OR UPDATE OR DELETE ON "draft"
1983 FOR EACH ROW EXECUTE PROCEDURE
1984 "copy_current_draft_data_trigger"();
1986 COMMENT ON FUNCTION "copy_current_draft_data_trigger"() IS 'Implementation of trigger "copy_current_draft_data" on table "draft"';
1987 COMMENT ON TRIGGER "copy_current_draft_data" ON "draft" IS 'Copy certain fields from most recent "draft" to "initiative"';
1991 -----------------------------------------------------
1992 -- Automatic calculation of certain default values --
1993 -----------------------------------------------------
1996 CREATE FUNCTION "copy_timings_trigger"()
1997 RETURNS TRIGGER
1998 LANGUAGE 'plpgsql' VOLATILE AS $$
1999 DECLARE
2000 "policy_row" "policy"%ROWTYPE;
2001 BEGIN
2002 SELECT * INTO "policy_row" FROM "policy"
2003 WHERE "id" = NEW."policy_id";
2004 IF NEW."min_admission_time" ISNULL THEN
2005 NEW."min_admission_time" := "policy_row"."min_admission_time";
2006 END IF;
2007 IF NEW."max_admission_time" ISNULL THEN
2008 NEW."max_admission_time" := "policy_row"."max_admission_time";
2009 END IF;
2010 IF NEW."discussion_time" ISNULL THEN
2011 NEW."discussion_time" := "policy_row"."discussion_time";
2012 END IF;
2013 IF NEW."verification_time" ISNULL THEN
2014 NEW."verification_time" := "policy_row"."verification_time";
2015 END IF;
2016 IF NEW."voting_time" ISNULL THEN
2017 NEW."voting_time" := "policy_row"."voting_time";
2018 END IF;
2019 RETURN NEW;
2020 END;
2021 $$;
2023 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
2024 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
2026 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
2027 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
2030 CREATE FUNCTION "default_for_draft_id_trigger"()
2031 RETURNS TRIGGER
2032 LANGUAGE 'plpgsql' VOLATILE AS $$
2033 BEGIN
2034 IF NEW."draft_id" ISNULL THEN
2035 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
2036 WHERE "initiative_id" = NEW."initiative_id";
2037 END IF;
2038 RETURN NEW;
2039 END;
2040 $$;
2042 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
2043 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
2044 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
2045 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
2047 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
2048 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';
2049 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';
2053 ----------------------------------------
2054 -- Automatic creation of dependencies --
2055 ----------------------------------------
2058 CREATE FUNCTION "autocreate_interest_trigger"()
2059 RETURNS TRIGGER
2060 LANGUAGE 'plpgsql' VOLATILE AS $$
2061 BEGIN
2062 IF NOT EXISTS (
2063 SELECT NULL FROM "initiative" JOIN "interest"
2064 ON "initiative"."issue_id" = "interest"."issue_id"
2065 WHERE "initiative"."id" = NEW."initiative_id"
2066 AND "interest"."member_id" = NEW."member_id"
2067 ) THEN
2068 BEGIN
2069 INSERT INTO "interest" ("issue_id", "member_id")
2070 SELECT "issue_id", NEW."member_id"
2071 FROM "initiative" WHERE "id" = NEW."initiative_id";
2072 EXCEPTION WHEN unique_violation THEN END;
2073 END IF;
2074 RETURN NEW;
2075 END;
2076 $$;
2078 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
2079 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
2081 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
2082 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';
2085 CREATE FUNCTION "autocreate_supporter_trigger"()
2086 RETURNS TRIGGER
2087 LANGUAGE 'plpgsql' VOLATILE AS $$
2088 BEGIN
2089 IF NOT EXISTS (
2090 SELECT NULL FROM "suggestion" JOIN "supporter"
2091 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
2092 WHERE "suggestion"."id" = NEW."suggestion_id"
2093 AND "supporter"."member_id" = NEW."member_id"
2094 ) THEN
2095 BEGIN
2096 INSERT INTO "supporter" ("initiative_id", "member_id")
2097 SELECT "initiative_id", NEW."member_id"
2098 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
2099 EXCEPTION WHEN unique_violation THEN END;
2100 END IF;
2101 RETURN NEW;
2102 END;
2103 $$;
2105 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
2106 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
2108 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
2109 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.';
2113 ------------------------------------------
2114 -- Views and helper functions for views --
2115 ------------------------------------------
2118 CREATE VIEW "member_eligible_to_be_notified" AS
2119 SELECT * FROM "member"
2120 WHERE "activated" NOTNULL AND "locked" = FALSE;
2122 COMMENT ON VIEW "member_eligible_to_be_notified" IS 'Filtered "member" table containing only activated and non-locked members (used as helper view for "member_to_notify" and "newsletter_to_send")';
2125 CREATE VIEW "member_to_notify" AS
2126 SELECT * FROM "member_eligible_to_be_notified"
2127 WHERE "disable_notifications" = FALSE;
2129 COMMENT ON VIEW "member_to_notify" IS 'Filtered "member" table containing only members that are eligible to and wish to receive notifications; NOTE: "notify_email" may still be NULL and might need to be checked by frontend (this allows other means of messaging)';
2132 CREATE VIEW "matching_admission_rule_condition" AS
2133 SELECT DISTINCT ON ("issue_id", "admission_rule_condition"."admission_rule_id")
2134 "issue"."id" AS "issue_id",
2135 "admission_rule_condition".*
2136 FROM "admission_rule_condition"
2137 JOIN "area" ON "admission_rule_condition"."unit_id" = "area"."unit_id"
2138 JOIN "issue" ON "area"."id" = "issue"."area_id"
2139 WHERE (
2140 "admission_rule_condition"."policy_id" ISNULL OR
2141 "admission_rule_condition"."policy_id" = "issue"."policy_id"
2142 ) AND (
2143 "admission_rule_condition"."area_id" ISNULL OR
2144 "admission_rule_condition"."area_id" = "area"."id"
2145 )
2146 ORDER BY
2147 "issue_id",
2148 "admission_rule_condition"."admission_rule_id",
2149 "admission_rule_condition"."policy_id" ISNULL,
2150 "admission_rule_condition"."area_id" ISNULL;
2152 COMMENT ON VIEW "matching_admission_rule_condition" IS 'Selects the most fitting "admission_rule_condition" for a given pair of "issue" and "admission_rule"';
2155 CREATE VIEW "applicable_admission_rule" AS
2156 SELECT * FROM "admission_rule"
2157 WHERE NOT EXISTS (
2158 SELECT NULL FROM "issue"
2159 JOIN "matching_admission_rule_condition" AS "condition"
2160 ON "issue"."id" = "condition"."issue_id"
2161 WHERE "condition"."admission_rule_id" = "admission_rule"."id"
2162 AND "issue"."accepted" > now() - "condition"."holdoff_time"
2163 );
2165 COMMENT ON VIEW "applicable_admission_rule" IS 'Filters "admission_rule"s which are not blocked by a recently admitted issue';
2168 CREATE VIEW "issue_for_admission" AS
2169 SELECT
2170 "issue".*,
2171 max("initiative"."supporter_count") AS "max_supporter_count"
2172 FROM "issue"
2173 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
2174 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2175 JOIN "area" ON "issue"."area_id" = "area"."id"
2176 JOIN "admission_rule_condition"
2177 ON "admission_rule_condition"."unit_id" = "area"."unit_id"
2178 AND (
2179 "admission_rule_condition"."policy_id" ISNULL OR
2180 "admission_rule_condition"."policy_id" = "issue"."policy_id"
2181 )
2182 AND (
2183 "admission_rule_condition"."area_id" ISNULL OR
2184 "admission_rule_condition"."area_id" = "issue"."area_id"
2185 )
2186 JOIN "applicable_admission_rule"
2187 ON "admission_rule_condition"."admission_rule_id" = "applicable_admission_rule"."id"
2188 WHERE "issue"."state" = 'admission'::"issue_state"
2189 AND now() >= "issue"."created" + "issue"."min_admission_time"
2190 AND "initiative"."supporter_count" >= "policy"."issue_quorum"
2191 AND "initiative"."revoked" ISNULL
2192 GROUP BY "issue"."id"
2193 ORDER BY "max_supporter_count" DESC, "issue"."id"
2194 LIMIT 1;
2196 COMMENT ON VIEW "issue_for_admission" IS 'Contains up to 1 issue eligible to pass from ''admission'' to ''discussion'' state; needs to be recalculated after admitting the issue in this view';
2199 CREATE VIEW "unit_delegation" AS
2200 SELECT
2201 "unit"."id" AS "unit_id",
2202 "delegation"."id",
2203 "delegation"."truster_id",
2204 "delegation"."trustee_id",
2205 "delegation"."scope"
2206 FROM "unit"
2207 JOIN "delegation"
2208 ON "delegation"."unit_id" = "unit"."id"
2209 JOIN "member"
2210 ON "delegation"."truster_id" = "member"."id"
2211 JOIN "privilege"
2212 ON "delegation"."unit_id" = "privilege"."unit_id"
2213 AND "delegation"."truster_id" = "privilege"."member_id"
2214 WHERE "member"."active" AND "privilege"."voting_right";
2216 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
2219 CREATE VIEW "area_delegation" AS
2220 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
2221 "area"."id" AS "area_id",
2222 "delegation"."id",
2223 "delegation"."truster_id",
2224 "delegation"."trustee_id",
2225 "delegation"."scope"
2226 FROM "area"
2227 JOIN "delegation"
2228 ON "delegation"."unit_id" = "area"."unit_id"
2229 OR "delegation"."area_id" = "area"."id"
2230 JOIN "member"
2231 ON "delegation"."truster_id" = "member"."id"
2232 JOIN "privilege"
2233 ON "area"."unit_id" = "privilege"."unit_id"
2234 AND "delegation"."truster_id" = "privilege"."member_id"
2235 WHERE "member"."active" AND "privilege"."voting_right"
2236 ORDER BY
2237 "area"."id",
2238 "delegation"."truster_id",
2239 "delegation"."scope" DESC;
2241 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
2244 CREATE VIEW "issue_delegation" AS
2245 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
2246 "issue"."id" AS "issue_id",
2247 "delegation"."id",
2248 "delegation"."truster_id",
2249 "delegation"."trustee_id",
2250 "delegation"."scope"
2251 FROM "issue"
2252 JOIN "area"
2253 ON "area"."id" = "issue"."area_id"
2254 JOIN "delegation"
2255 ON "delegation"."unit_id" = "area"."unit_id"
2256 OR "delegation"."area_id" = "area"."id"
2257 OR "delegation"."issue_id" = "issue"."id"
2258 JOIN "member"
2259 ON "delegation"."truster_id" = "member"."id"
2260 JOIN "privilege"
2261 ON "area"."unit_id" = "privilege"."unit_id"
2262 AND "delegation"."truster_id" = "privilege"."member_id"
2263 WHERE "member"."active" AND "privilege"."voting_right"
2264 ORDER BY
2265 "issue"."id",
2266 "delegation"."truster_id",
2267 "delegation"."scope" DESC;
2269 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
2272 CREATE VIEW "member_count_view" AS
2273 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
2275 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
2278 CREATE VIEW "unit_member_count" AS
2279 SELECT
2280 "unit"."id" AS "unit_id",
2281 count("member"."id") AS "member_count"
2282 FROM "unit"
2283 LEFT JOIN "privilege"
2284 ON "privilege"."unit_id" = "unit"."id"
2285 AND "privilege"."voting_right"
2286 LEFT JOIN "member"
2287 ON "member"."id" = "privilege"."member_id"
2288 AND "member"."active"
2289 GROUP BY "unit"."id";
2291 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
2294 CREATE VIEW "opening_draft" AS
2295 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
2296 ORDER BY "initiative_id", "id";
2298 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
2301 CREATE VIEW "current_draft" AS
2302 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
2303 ORDER BY "initiative_id", "id" DESC;
2305 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
2308 CREATE VIEW "critical_opinion" AS
2309 SELECT * FROM "opinion"
2310 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
2311 OR ("degree" = -2 AND "fulfilled" = TRUE);
2313 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
2316 CREATE VIEW "issue_supporter_in_admission_state" AS
2317 SELECT
2318 "area"."unit_id",
2319 "issue"."area_id",
2320 "issue"."id" AS "issue_id",
2321 "supporter"."member_id",
2322 "direct_interest_snapshot"."weight"
2323 FROM "issue"
2324 JOIN "area" ON "area"."id" = "issue"."area_id"
2325 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
2326 JOIN "direct_interest_snapshot"
2327 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
2328 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
2329 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
2330 WHERE "issue"."state" = 'admission'::"issue_state";
2332 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';
2335 CREATE VIEW "initiative_suggestion_order_calculation" AS
2336 SELECT
2337 "initiative"."id" AS "initiative_id",
2338 ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final"
2339 FROM "initiative" JOIN "issue"
2340 ON "initiative"."issue_id" = "issue"."id"
2341 WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL)
2342 OR ("initiative"."final_suggestion_order_calculated" = FALSE);
2344 COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';
2346 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';
2349 CREATE VIEW "individual_suggestion_ranking" AS
2350 SELECT
2351 "opinion"."initiative_id",
2352 "opinion"."member_id",
2353 "direct_interest_snapshot"."weight",
2354 CASE WHEN
2355 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
2356 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
2357 THEN 1 ELSE
2358 CASE WHEN
2359 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
2360 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
2361 THEN 2 ELSE
2362 CASE WHEN
2363 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
2364 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
2365 THEN 3 ELSE 4 END
2366 END
2367 END AS "preference",
2368 "opinion"."suggestion_id"
2369 FROM "opinion"
2370 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
2371 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2372 JOIN "direct_interest_snapshot"
2373 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
2374 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
2375 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
2377 COMMENT ON VIEW "individual_suggestion_ranking" IS 'Helper view for "lf_update_suggestion_order" to allow a proportional ordering of suggestions within an initiative';
2380 CREATE VIEW "battle_participant" AS
2381 SELECT "initiative"."id", "initiative"."issue_id"
2382 FROM "issue" JOIN "initiative"
2383 ON "issue"."id" = "initiative"."issue_id"
2384 WHERE "initiative"."admitted"
2385 UNION ALL
2386 SELECT NULL, "id" AS "issue_id"
2387 FROM "issue";
2389 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
2392 CREATE VIEW "battle_view" AS
2393 SELECT
2394 "issue"."id" AS "issue_id",
2395 "winning_initiative"."id" AS "winning_initiative_id",
2396 "losing_initiative"."id" AS "losing_initiative_id",
2397 sum(
2398 CASE WHEN
2399 coalesce("better_vote"."grade", 0) >
2400 coalesce("worse_vote"."grade", 0)
2401 THEN "direct_voter"."weight" ELSE 0 END
2402 ) AS "count"
2403 FROM "issue"
2404 LEFT JOIN "direct_voter"
2405 ON "issue"."id" = "direct_voter"."issue_id"
2406 JOIN "battle_participant" AS "winning_initiative"
2407 ON "issue"."id" = "winning_initiative"."issue_id"
2408 JOIN "battle_participant" AS "losing_initiative"
2409 ON "issue"."id" = "losing_initiative"."issue_id"
2410 LEFT JOIN "vote" AS "better_vote"
2411 ON "direct_voter"."member_id" = "better_vote"."member_id"
2412 AND "winning_initiative"."id" = "better_vote"."initiative_id"
2413 LEFT JOIN "vote" AS "worse_vote"
2414 ON "direct_voter"."member_id" = "worse_vote"."member_id"
2415 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
2416 WHERE "issue"."state" = 'voting'
2417 AND "issue"."phase_finished" NOTNULL
2418 AND (
2419 "winning_initiative"."id" != "losing_initiative"."id" OR
2420 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
2421 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
2422 GROUP BY
2423 "issue"."id",
2424 "winning_initiative"."id",
2425 "losing_initiative"."id";
2427 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';
2430 CREATE VIEW "expired_session" AS
2431 SELECT * FROM "session" WHERE now() > "expiry";
2433 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
2434 DELETE FROM "session" WHERE "ident" = OLD."ident";
2436 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
2437 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
2440 CREATE VIEW "open_issue" AS
2441 SELECT * FROM "issue" WHERE "closed" ISNULL;
2443 COMMENT ON VIEW "open_issue" IS 'All open issues';
2446 CREATE VIEW "member_contingent" AS
2447 SELECT
2448 "member"."id" AS "member_id",
2449 "contingent"."polling",
2450 "contingent"."time_frame",
2451 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
2452 (
2453 SELECT count(1) FROM "draft"
2454 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
2455 WHERE "draft"."author_id" = "member"."id"
2456 AND "initiative"."polling" = "contingent"."polling"
2457 AND "draft"."created" > now() - "contingent"."time_frame"
2458 ) + (
2459 SELECT count(1) FROM "suggestion"
2460 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
2461 WHERE "suggestion"."author_id" = "member"."id"
2462 AND "contingent"."polling" = FALSE
2463 AND "suggestion"."created" > now() - "contingent"."time_frame"
2464 )
2465 ELSE NULL END AS "text_entry_count",
2466 "contingent"."text_entry_limit",
2467 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
2468 SELECT count(1) FROM "opening_draft" AS "draft"
2469 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
2470 WHERE "draft"."author_id" = "member"."id"
2471 AND "initiative"."polling" = "contingent"."polling"
2472 AND "draft"."created" > now() - "contingent"."time_frame"
2473 ) ELSE NULL END AS "initiative_count",
2474 "contingent"."initiative_limit"
2475 FROM "member" CROSS JOIN "contingent";
2477 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
2479 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
2480 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
2483 CREATE VIEW "member_contingent_left" AS
2484 SELECT
2485 "member_id",
2486 "polling",
2487 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
2488 max("initiative_limit" - "initiative_count") AS "initiatives_left"
2489 FROM "member_contingent" GROUP BY "member_id", "polling";
2491 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.';
2494 CREATE VIEW "event_for_notification" AS
2495 SELECT
2496 "member"."id" AS "recipient_id",
2497 "event".*
2498 FROM "member" CROSS JOIN "event"
2499 JOIN "issue" ON "issue"."id" = "event"."issue_id"
2500 JOIN "area" ON "area"."id" = "issue"."area_id"
2501 LEFT JOIN "privilege" ON
2502 "privilege"."member_id" = "member"."id" AND
2503 "privilege"."unit_id" = "area"."unit_id" AND
2504 "privilege"."voting_right" = TRUE
2505 LEFT JOIN "subscription" ON
2506 "subscription"."member_id" = "member"."id" AND
2507 "subscription"."unit_id" = "area"."unit_id"
2508 LEFT JOIN "ignored_area" ON
2509 "ignored_area"."member_id" = "member"."id" AND
2510 "ignored_area"."area_id" = "issue"."area_id"
2511 LEFT JOIN "interest" ON
2512 "interest"."member_id" = "member"."id" AND
2513 "interest"."issue_id" = "event"."issue_id"
2514 LEFT JOIN "supporter" ON
2515 "supporter"."member_id" = "member"."id" AND
2516 "supporter"."initiative_id" = "event"."initiative_id"
2517 WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL)
2518 AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
2519 AND (
2520 "event"."event" = 'issue_state_changed'::"event_type" OR
2521 ( "event"."event" = 'initiative_revoked'::"event_type" AND
2522 "supporter"."member_id" NOTNULL ) );
2524 COMMENT ON VIEW "event_for_notification" IS 'Entries of the "event" table which are of interest for a particular notification mail recipient';
2526 COMMENT ON COLUMN "event_for_notification"."recipient_id" IS 'member_id of the recipient of a notification mail';
2529 CREATE VIEW "updated_initiative" AS
2530 SELECT
2531 "supporter"."member_id" AS "recipient_id",
2532 FALSE AS "featured",
2533 "supporter"."initiative_id"
2534 FROM "supporter"
2535 JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id"
2536 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2537 LEFT JOIN "notification_initiative_sent" AS "sent" ON
2538 "sent"."member_id" = "supporter"."member_id" AND
2539 "sent"."initiative_id" = "supporter"."initiative_id"
2540 LEFT JOIN "ignored_initiative" ON
2541 "ignored_initiative"."member_id" = "supporter"."member_id" AND
2542 "ignored_initiative"."initiative_id" = "supporter"."initiative_id"
2543 WHERE "issue"."state" IN ('admission', 'discussion')
2544 AND "initiative"."revoked" ISNULL
2545 AND "ignored_initiative"."member_id" ISNULL
2546 AND (
2547 EXISTS (
2548 SELECT NULL FROM "draft"
2549 LEFT JOIN "ignored_member" ON
2550 "ignored_member"."member_id" = "supporter"."member_id" AND
2551 "ignored_member"."other_member_id" = "draft"."author_id"
2552 WHERE "draft"."initiative_id" = "supporter"."initiative_id"
2553 AND "draft"."id" > "supporter"."draft_id"
2554 AND "ignored_member"."member_id" ISNULL
2555 ) OR EXISTS (
2556 SELECT NULL FROM "suggestion"
2557 LEFT JOIN "opinion" ON
2558 "opinion"."member_id" = "supporter"."member_id" AND
2559 "opinion"."suggestion_id" = "suggestion"."id"
2560 LEFT JOIN "ignored_member" ON
2561 "ignored_member"."member_id" = "supporter"."member_id" AND
2562 "ignored_member"."other_member_id" = "suggestion"."author_id"
2563 WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"
2564 AND "opinion"."member_id" ISNULL
2565 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
2566 AND "ignored_member"."member_id" ISNULL
2567 )
2568 );
2570 COMMENT ON VIEW "updated_initiative" IS 'Helper view for view "updated_or_featured_initiative"';
2573 CREATE FUNCTION "featured_initiative"
2574 ( "recipient_id_p" "member"."id"%TYPE,
2575 "area_id_p" "area"."id"%TYPE )
2576 RETURNS SETOF "initiative"."id"%TYPE
2577 LANGUAGE 'plpgsql' STABLE AS $$
2578 DECLARE
2579 "counter_v" "member"."notification_counter"%TYPE;
2580 "sample_size_v" "member"."notification_sample_size"%TYPE;
2581 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
2582 "match_v" BOOLEAN;
2583 "member_id_v" "member"."id"%TYPE;
2584 "seed_v" TEXT;
2585 "initiative_id_v" "initiative"."id"%TYPE;
2586 BEGIN
2587 SELECT "notification_counter", "notification_sample_size"
2588 INTO "counter_v", "sample_size_v"
2589 FROM "member" WHERE "id" = "recipient_id_p";
2590 IF COALESCE("sample_size_v" <= 0, TRUE) THEN
2591 RETURN;
2592 END IF;
2593 "initiative_id_ary" := '{}';
2594 LOOP
2595 "match_v" := FALSE;
2596 FOR "member_id_v", "seed_v" IN
2597 SELECT * FROM (
2598 SELECT DISTINCT
2599 "supporter"."member_id",
2600 md5(
2601 "recipient_id_p" || '-' ||
2602 "counter_v" || '-' ||
2603 "area_id_p" || '-' ||
2604 "supporter"."member_id"
2605 ) AS "seed"
2606 FROM "supporter"
2607 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
2608 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2609 WHERE "supporter"."member_id" != "recipient_id_p"
2610 AND "issue"."area_id" = "area_id_p"
2611 AND "issue"."state" IN ('admission', 'discussion', 'verification')
2612 ) AS "subquery"
2613 ORDER BY "seed"
2614 LOOP
2615 SELECT "initiative"."id" INTO "initiative_id_v"
2616 FROM "initiative"
2617 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2618 JOIN "area" ON "area"."id" = "issue"."area_id"
2619 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
2620 LEFT JOIN "supporter" AS "self_support" ON
2621 "self_support"."initiative_id" = "initiative"."id" AND
2622 "self_support"."member_id" = "recipient_id_p"
2623 LEFT JOIN "privilege" ON
2624 "privilege"."member_id" = "recipient_id_p" AND
2625 "privilege"."unit_id" = "area"."unit_id" AND
2626 "privilege"."voting_right" = TRUE
2627 LEFT JOIN "subscription" ON
2628 "subscription"."member_id" = "recipient_id_p" AND
2629 "subscription"."unit_id" = "area"."unit_id"
2630 LEFT JOIN "ignored_initiative" ON
2631 "ignored_initiative"."member_id" = "recipient_id_p" AND
2632 "ignored_initiative"."initiative_id" = "initiative"."id"
2633 WHERE "supporter"."member_id" = "member_id_v"
2634 AND "issue"."area_id" = "area_id_p"
2635 AND "issue"."state" IN ('admission', 'discussion', 'verification')
2636 AND "initiative"."revoked" ISNULL
2637 AND "self_support"."member_id" ISNULL
2638 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
2639 AND (
2640 "privilege"."member_id" NOTNULL OR
2641 "subscription"."member_id" NOTNULL )
2642 AND "ignored_initiative"."member_id" ISNULL
2643 AND NOT EXISTS (
2644 SELECT NULL FROM "draft"
2645 JOIN "ignored_member" ON
2646 "ignored_member"."member_id" = "recipient_id_p" AND
2647 "ignored_member"."other_member_id" = "draft"."author_id"
2648 WHERE "draft"."initiative_id" = "initiative"."id"
2649 )
2650 ORDER BY md5("seed_v" || '-' || "initiative"."id")
2651 LIMIT 1;
2652 IF FOUND THEN
2653 "match_v" := TRUE;
2654 RETURN NEXT "initiative_id_v";
2655 IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
2656 RETURN;
2657 END IF;
2658 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
2659 END IF;
2660 END LOOP;
2661 EXIT WHEN NOT "match_v";
2662 END LOOP;
2663 RETURN;
2664 END;
2665 $$;
2667 COMMENT ON FUNCTION "featured_initiative"
2668 ( "recipient_id_p" "member"."id"%TYPE,
2669 "area_id_p" "area"."id"%TYPE )
2670 IS 'Helper function for view "updated_or_featured_initiative"';
2673 CREATE VIEW "updated_or_featured_initiative" AS
2674 SELECT
2675 "subquery".*,
2676 NOT EXISTS (
2677 SELECT NULL FROM "initiative" AS "better_initiative"
2678 WHERE "better_initiative"."issue_id" = "initiative"."issue_id"
2679 AND
2680 ( COALESCE("better_initiative"."supporter_count", -1),
2681 -"better_initiative"."id" ) >
2682 ( COALESCE("initiative"."supporter_count", -1),
2683 -"initiative"."id" )
2684 ) AS "leading"
2685 FROM (
2686 SELECT * FROM "updated_initiative"
2687 UNION ALL
2688 SELECT
2689 "member"."id" AS "recipient_id",
2690 TRUE AS "featured",
2691 "featured_initiative_id" AS "initiative_id"
2692 FROM "member" CROSS JOIN "area"
2693 CROSS JOIN LATERAL
2694 "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id"
2695 JOIN "initiative" ON "initiative"."id" = "featured_initiative_id"
2696 ) AS "subquery"
2697 JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id";
2699 COMMENT ON VIEW "updated_or_featured_initiative" IS 'Initiatives to be included in a scheduled notification mail because (a) they have been updated or (b) they are featured';
2701 COMMENT ON COLUMN "updated_or_featured_initiative"."recipient_id" IS '"id" of the member who receives the notification mail';
2702 COMMENT ON COLUMN "updated_or_featured_initiative"."featured" IS 'TRUE if the initiative has been included because it was selected by the "featured_initiative" algorithm (see source of function "featured_initiative")';
2703 COMMENT ON COLUMN "updated_or_featured_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
2704 COMMENT ON COLUMN "updated_or_featured_initiative"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue';
2707 CREATE VIEW "leading_complement_initiative" AS
2708 SELECT * FROM (
2709 SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id")
2710 "uf_initiative"."recipient_id",
2711 FALSE AS "featured",
2712 "uf_initiative"."initiative_id",
2713 TRUE AS "leading"
2714 FROM "updated_or_featured_initiative" AS "uf_initiative"
2715 JOIN "initiative" AS "uf_initiative_full" ON
2716 "uf_initiative_full"."id" = "uf_initiative"."initiative_id"
2717 JOIN "initiative" ON
2718 "initiative"."issue_id" = "uf_initiative_full"."issue_id"
2719 WHERE "initiative"."revoked" ISNULL
2720 ORDER BY
2721 "uf_initiative"."recipient_id",
2722 "initiative"."issue_id",
2723 "initiative"."supporter_count" DESC,
2724 "initiative"."id"
2725 ) AS "subquery"
2726 WHERE NOT EXISTS (
2727 SELECT NULL FROM "updated_or_featured_initiative" AS "other"
2728 WHERE "other"."recipient_id" = "subquery"."recipient_id"
2729 AND "other"."initiative_id" = "subquery"."initiative_id"
2730 );
2732 COMMENT ON VIEW "leading_complement_initiative" IS 'Helper view for view "unfiltered_initiative_for_notification" in order to always include the most supported initiative of an issue';
2733 COMMENT ON COLUMN "leading_complement_initiative"."featured" IS 'Always FALSE in this view';
2734 COMMENT ON COLUMN "leading_complement_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
2735 COMMENT ON COLUMN "leading_complement_initiative"."leading" IS 'Always TRUE in this view';
2738 CREATE VIEW "unfiltered_initiative_for_notification" AS
2739 SELECT
2740 "subquery".*,
2741 "supporter"."member_id" NOTNULL AS "supported",
2742 CASE WHEN "supporter"."member_id" NOTNULL THEN
2743 EXISTS (
2744 SELECT NULL FROM "draft"
2745 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
2746 AND "draft"."id" > "supporter"."draft_id"
2747 )
2748 ELSE
2749 EXISTS (
2750 SELECT NULL FROM "draft"
2751 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
2752 AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE)
2753 )
2754 END AS "new_draft",
2755 CASE WHEN "supporter"."member_id" NOTNULL THEN
2756 ( SELECT count(1) FROM "suggestion"
2757 LEFT JOIN "opinion" ON
2758 "opinion"."member_id" = "supporter"."member_id" AND
2759 "opinion"."suggestion_id" = "suggestion"."id"
2760 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
2761 AND "opinion"."member_id" ISNULL
2762 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
2763 )
2764 ELSE
2765 ( SELECT count(1) FROM "suggestion"
2766 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
2767 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
2768 )
2769 END AS "new_suggestion_count"
2770 FROM (
2771 SELECT * FROM "updated_or_featured_initiative"
2772 UNION ALL
2773 SELECT * FROM "leading_complement_initiative"
2774 ) AS "subquery"
2775 LEFT JOIN "supporter" ON
2776 "supporter"."member_id" = "subquery"."recipient_id" AND
2777 "supporter"."initiative_id" = "subquery"."initiative_id"
2778 LEFT JOIN "notification_initiative_sent" AS "sent" ON
2779 "sent"."member_id" = "subquery"."recipient_id" AND
2780 "sent"."initiative_id" = "subquery"."initiative_id";
2782 COMMENT ON VIEW "unfiltered_initiative_for_notification" IS 'Helper view which simply combines the views "updated_or_featured_initiative" and "leading_complement_initiative" and adds columns "supported", "new_draft", and "new_suggestion_count';
2784 COMMENT ON COLUMN "unfiltered_initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient';
2785 COMMENT ON COLUMN "unfiltered_initiative_for_notification"."new_draft" IS 'TRUE if a new draft exists (using the "draft_id" column of the "supporter" table in case of "supported" initiatives and the "last_draft_id" column of the "notification_initiative_sent" table in all other cases)';
2786 COMMENT ON COLUMN "unfiltered_initiative_for_notification"."new_suggestion_count" IS 'Number of new suggestions (using the "last_suggestion_id" column of the "notification_initiative_sent" table while ignoring suggestions with an "opinion")';
2789 CREATE VIEW "initiative_for_notification" AS
2790 SELECT "unfiltered1".*
2791 FROM "unfiltered_initiative_for_notification" "unfiltered1"
2792 JOIN "initiative" AS "initiative1" ON
2793 "initiative1"."id" = "unfiltered1"."initiative_id"
2794 JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id"
2795 WHERE EXISTS (
2796 SELECT NULL
2797 FROM "unfiltered_initiative_for_notification" "unfiltered2"
2798 JOIN "initiative" AS "initiative2" ON
2799 "initiative2"."id" = "unfiltered2"."initiative_id"
2800 JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id"
2801 WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"
2802 AND "issue1"."area_id" = "issue2"."area_id"
2803 AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 )
2804 );
2806 COMMENT ON VIEW "initiative_for_notification" IS 'Initiatives to be included in a scheduled notification mail';
2808 COMMENT ON COLUMN "initiative_for_notification"."recipient_id" IS '"id" of the member who receives the notification mail';
2809 COMMENT ON COLUMN "initiative_for_notification"."featured" IS 'TRUE if the initiative has been included because it was selected by the "featured_initiative" algorithm (see source of function "featured_initiative")';
2810 COMMENT ON COLUMN "initiative_for_notification"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
2811 COMMENT ON COLUMN "initiative_for_notification"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue';
2812 COMMENT ON COLUMN "initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient';
2813 COMMENT ON COLUMN "initiative_for_notification"."new_draft" IS 'TRUE if a new draft exists (using the "draft_id" column of the "supporter" table in case of "supported" initiatives and the "last_draft_id" column of the "notification_initiative_sent" table in all other cases)';
2814 COMMENT ON COLUMN "initiative_for_notification"."new_suggestion_count" IS 'Number of new suggestions (using the "last_suggestion_id" column of the "notification_initiative_sent" table while ignoring suggestions with an "opinion")';
2817 CREATE VIEW "scheduled_notification_to_send" AS
2818 SELECT * FROM (
2819 SELECT
2820 "id" AS "recipient_id",
2821 now() - CASE WHEN "notification_dow" ISNULL THEN
2822 ( "notification_sent"::DATE + CASE
2823 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
2824 THEN 0 ELSE 1 END
2825 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
2826 ELSE
2827 ( "notification_sent"::DATE +
2828 ( 7 + "notification_dow" -
2829 EXTRACT(DOW FROM
2830 ( "notification_sent"::DATE + CASE
2831 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
2832 THEN 0 ELSE 1 END
2833 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
2834 )::INTEGER
2835 ) % 7 +
2836 CASE
2837 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
2838 THEN 0 ELSE 1
2839 END
2840 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
2841 END AS "pending"
2842 FROM (
2843 SELECT
2844 "id",
2845 COALESCE("notification_sent", "activated") AS "notification_sent",
2846 "notification_dow",
2847 "notification_hour"
2848 FROM "member_to_notify"
2849 WHERE "notification_hour" NOTNULL
2850 ) AS "subquery1"
2851 ) AS "subquery2"
2852 WHERE "pending" > '0'::INTERVAL;
2854 COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending';
2856 COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail';
2857 COMMENT ON COLUMN "scheduled_notification_to_send"."pending" IS 'Duration for which the notification mail has already been pending';
2860 CREATE VIEW "newsletter_to_send" AS
2861 SELECT
2862 "member"."id" AS "recipient_id",
2863 "newsletter"."id" AS "newsletter_id",
2864 "newsletter"."published"
2865 FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"
2866 LEFT JOIN "privilege" ON
2867 "privilege"."member_id" = "member"."id" AND
2868 "privilege"."unit_id" = "newsletter"."unit_id" AND
2869 "privilege"."voting_right" = TRUE
2870 LEFT JOIN "subscription" ON
2871 "subscription"."member_id" = "member"."id" AND
2872 "subscription"."unit_id" = "newsletter"."unit_id"
2873 WHERE "newsletter"."published" <= now()
2874 AND "newsletter"."sent" ISNULL
2875 AND (
2876 "member"."disable_notifications" = FALSE OR
2877 "newsletter"."include_all_members" = TRUE )
2878 AND (
2879 "newsletter"."unit_id" ISNULL OR
2880 "privilege"."member_id" NOTNULL OR
2881 "subscription"."member_id" NOTNULL );
2883 COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';
2885 COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter was supposed to be sent out (can be used for ordering)';
2889 ------------------------------------------------------
2890 -- Row set returning function for delegation chains --
2891 ------------------------------------------------------
2894 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2895 ('first', 'intermediate', 'last', 'repetition');
2897 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2900 CREATE TYPE "delegation_chain_row" AS (
2901 "index" INT4,
2902 "member_id" INT4,
2903 "member_valid" BOOLEAN,
2904 "participation" BOOLEAN,
2905 "overridden" BOOLEAN,
2906 "scope_in" "delegation_scope",
2907 "scope_out" "delegation_scope",
2908 "disabled_out" BOOLEAN,
2909 "loop" "delegation_chain_loop_tag" );
2911 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
2913 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2914 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';
2915 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2916 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2917 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2918 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2919 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2922 CREATE FUNCTION "delegation_chain_for_closed_issue"
2923 ( "member_id_p" "member"."id"%TYPE,
2924 "issue_id_p" "issue"."id"%TYPE )
2925 RETURNS SETOF "delegation_chain_row"
2926 LANGUAGE 'plpgsql' STABLE AS $$
2927 DECLARE
2928 "output_row" "delegation_chain_row";
2929 "direct_voter_row" "direct_voter"%ROWTYPE;
2930 "delegating_voter_row" "delegating_voter"%ROWTYPE;
2931 BEGIN
2932 "output_row"."index" := 0;
2933 "output_row"."member_id" := "member_id_p";
2934 "output_row"."member_valid" := TRUE;
2935 "output_row"."participation" := FALSE;
2936 "output_row"."overridden" := FALSE;
2937 "output_row"."disabled_out" := FALSE;
2938 LOOP
2939 SELECT INTO "direct_voter_row" * FROM "direct_voter"
2940 WHERE "issue_id" = "issue_id_p"
2941 AND "member_id" = "output_row"."member_id";
2942 IF "direct_voter_row"."member_id" NOTNULL THEN
2943 "output_row"."participation" := TRUE;
2944 "output_row"."scope_out" := NULL;
2945 "output_row"."disabled_out" := NULL;
2946 RETURN NEXT "output_row";
2947 RETURN;
2948 END IF;
2949 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
2950 WHERE "issue_id" = "issue_id_p"
2951 AND "member_id" = "output_row"."member_id";
2952 IF "delegating_voter_row"."member_id" ISNULL THEN
2953 RETURN;
2954 END IF;
2955 "output_row"."scope_out" := "delegating_voter_row"."scope";
2956 RETURN NEXT "output_row";
2957 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
2958 "output_row"."scope_in" := "output_row"."scope_out";
2959 END LOOP;
2960 END;
2961 $$;
2963 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
2964 ( "member"."id"%TYPE,
2965 "member"."id"%TYPE )
2966 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
2969 CREATE FUNCTION "delegation_chain"
2970 ( "member_id_p" "member"."id"%TYPE,
2971 "unit_id_p" "unit"."id"%TYPE,
2972 "area_id_p" "area"."id"%TYPE,
2973 "issue_id_p" "issue"."id"%TYPE,
2974 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2975 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2976 RETURNS SETOF "delegation_chain_row"
2977 LANGUAGE 'plpgsql' STABLE AS $$
2978 DECLARE
2979 "scope_v" "delegation_scope";
2980 "unit_id_v" "unit"."id"%TYPE;
2981 "area_id_v" "area"."id"%TYPE;
2982 "issue_row" "issue"%ROWTYPE;
2983 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2984 "loop_member_id_v" "member"."id"%TYPE;
2985 "output_row" "delegation_chain_row";
2986 "output_rows" "delegation_chain_row"[];
2987 "simulate_v" BOOLEAN;
2988 "simulate_here_v" BOOLEAN;
2989 "delegation_row" "delegation"%ROWTYPE;
2990 "row_count" INT4;
2991 "i" INT4;
2992 "loop_v" BOOLEAN;
2993 BEGIN
2994 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
2995 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
2996 END IF;
2997 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
2998 "simulate_v" := TRUE;
2999 ELSE
3000 "simulate_v" := FALSE;
3001 END IF;
3002 IF
3003 "unit_id_p" NOTNULL AND
3004 "area_id_p" ISNULL AND
3005 "issue_id_p" ISNULL
3006 THEN
3007 "scope_v" := 'unit';
3008 "unit_id_v" := "unit_id_p";
3009 ELSIF
3010 "unit_id_p" ISNULL AND
3011 "area_id_p" NOTNULL AND
3012 "issue_id_p" ISNULL
3013 THEN
3014 "scope_v" := 'area';
3015 "area_id_v" := "area_id_p";
3016 SELECT "unit_id" INTO "unit_id_v"
3017 FROM "area" WHERE "id" = "area_id_v";
3018 ELSIF
3019 "unit_id_p" ISNULL AND
3020 "area_id_p" ISNULL AND
3021 "issue_id_p" NOTNULL
3022 THEN
3023 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
3024 IF "issue_row"."id" ISNULL THEN
3025 RETURN;
3026 END IF;
3027 IF "issue_row"."closed" NOTNULL THEN
3028 IF "simulate_v" THEN
3029 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
3030 END IF;
3031 FOR "output_row" IN
3032 SELECT * FROM
3033 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
3034 LOOP
3035 RETURN NEXT "output_row";
3036 END LOOP;
3037 RETURN;
3038 END IF;
3039 "scope_v" := 'issue';
3040 SELECT "area_id" INTO "area_id_v"
3041 FROM "issue" WHERE "id" = "issue_id_p";
3042 SELECT "unit_id" INTO "unit_id_v"
3043 FROM "area" WHERE "id" = "area_id_v";
3044 ELSE
3045 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
3046 END IF;
3047 "visited_member_ids" := '{}';
3048 "loop_member_id_v" := NULL;
3049 "output_rows" := '{}';
3050 "output_row"."index" := 0;
3051 "output_row"."member_id" := "member_id_p";
3052 "output_row"."member_valid" := TRUE;
3053 "output_row"."participation" := FALSE;
3054 "output_row"."overridden" := FALSE;
3055 "output_row"."disabled_out" := FALSE;
3056 "output_row"."scope_out" := NULL;
3057 LOOP
3058 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
3059 "loop_member_id_v" := "output_row"."member_id";
3060 ELSE
3061 "visited_member_ids" :=
3062 "visited_member_ids" || "output_row"."member_id";
3063 END IF;
3064 IF "output_row"."participation" ISNULL THEN
3065 "output_row"."overridden" := NULL;
3066 ELSIF "output_row"."participation" THEN
3067 "output_row"."overridden" := TRUE;
3068 END IF;
3069 "output_row"."scope_in" := "output_row"."scope_out";
3070 "output_row"."member_valid" := EXISTS (
3071 SELECT NULL FROM "member" JOIN "privilege"
3072 ON "privilege"."member_id" = "member"."id"
3073 AND "privilege"."unit_id" = "unit_id_v"
3074 WHERE "id" = "output_row"."member_id"
3075 AND "member"."active" AND "privilege"."voting_right"
3076 );
3077 "simulate_here_v" := (
3078 "simulate_v" AND
3079 "output_row"."member_id" = "member_id_p"
3080 );
3081 "delegation_row" := ROW(NULL);
3082 IF "output_row"."member_valid" OR "simulate_here_v" THEN
3083 IF "scope_v" = 'unit' THEN
3084 IF NOT "simulate_here_v" THEN
3085 SELECT * INTO "delegation_row" FROM "delegation"
3086 WHERE "truster_id" = "output_row"."member_id"
3087 AND "unit_id" = "unit_id_v";
3088 END IF;
3089 ELSIF "scope_v" = 'area' THEN
3090 "output_row"."participation" := EXISTS (
3091 SELECT NULL FROM "membership"
3092 WHERE "area_id" = "area_id_p"
3093 AND "member_id" = "output_row"."member_id"
3094 );
3095 IF "simulate_here_v" THEN
3096 IF "simulate_trustee_id_p" ISNULL THEN
3097 SELECT * INTO "delegation_row" FROM "delegation"
3098 WHERE "truster_id" = "output_row"."member_id"
3099 AND "unit_id" = "unit_id_v";
3100 END IF;
3101 ELSE
3102 SELECT * INTO "delegation_row" FROM "delegation"
3103 WHERE "truster_id" = "output_row"."member_id"
3104 AND (
3105 "unit_id" = "unit_id_v" OR
3106 "area_id" = "area_id_v"
3107 )
3108 ORDER BY "scope" DESC;
3109 END IF;
3110 ELSIF "scope_v" = 'issue' THEN
3111 IF "issue_row"."fully_frozen" ISNULL THEN
3112 "output_row"."participation" := EXISTS (
3113 SELECT NULL FROM "interest"
3114 WHERE "issue_id" = "issue_id_p"
3115 AND "member_id" = "output_row"."member_id"
3116 );
3117 ELSE
3118 IF "output_row"."member_id" = "member_id_p" THEN
3119 "output_row"."participation" := EXISTS (
3120 SELECT NULL FROM "direct_voter"
3121 WHERE "issue_id" = "issue_id_p"
3122 AND "member_id" = "output_row"."member_id"
3123 );
3124 ELSE
3125 "output_row"."participation" := NULL;
3126 END IF;
3127 END IF;
3128 IF "simulate_here_v" THEN
3129 IF "simulate_trustee_id_p" ISNULL THEN
3130 SELECT * INTO "delegation_row" FROM "delegation"
3131 WHERE "truster_id" = "output_row"."member_id"
3132 AND (
3133 "unit_id" = "unit_id_v" OR
3134 "area_id" = "area_id_v"
3135 )
3136 ORDER BY "scope" DESC;
3137 END IF;
3138 ELSE
3139 SELECT * INTO "delegation_row" FROM "delegation"
3140 WHERE "truster_id" = "output_row"."member_id"
3141 AND (
3142 "unit_id" = "unit_id_v" OR
3143 "area_id" = "area_id_v" OR
3144 "issue_id" = "issue_id_p"
3145 )
3146 ORDER BY "scope" DESC;
3147 END IF;
3148 END IF;
3149 ELSE
3150 "output_row"."participation" := FALSE;
3151 END IF;
3152 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
3153 "output_row"."scope_out" := "scope_v";
3154 "output_rows" := "output_rows" || "output_row";
3155 "output_row"."member_id" := "simulate_trustee_id_p";
3156 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
3157 "output_row"."scope_out" := "delegation_row"."scope";
3158 "output_rows" := "output_rows" || "output_row";
3159 "output_row"."member_id" := "delegation_row"."trustee_id";
3160 ELSIF "delegation_row"."scope" NOTNULL THEN
3161 "output_row"."scope_out" := "delegation_row"."scope";
3162 "output_row"."disabled_out" := TRUE;
3163 "output_rows" := "output_rows" || "output_row";
3164 EXIT;
3165 ELSE
3166 "output_row"."scope_out" := NULL;
3167 "output_rows" := "output_rows" || "output_row";
3168 EXIT;
3169 END IF;
3170 EXIT WHEN "loop_member_id_v" NOTNULL;
3171 "output_row"."index" := "output_row"."index" + 1;
3172 END LOOP;
3173 "row_count" := array_upper("output_rows", 1);
3174 "i" := 1;
3175 "loop_v" := FALSE;
3176 LOOP
3177 "output_row" := "output_rows"["i"];
3178 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
3179 IF "loop_v" THEN
3180 IF "i" + 1 = "row_count" THEN
3181 "output_row"."loop" := 'last';
3182 ELSIF "i" = "row_count" THEN
3183 "output_row"."loop" := 'repetition';
3184 ELSE
3185 "output_row"."loop" := 'intermediate';
3186 END IF;
3187 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
3188 "output_row"."loop" := 'first';
3189 "loop_v" := TRUE;
3190 END IF;
3191 IF "scope_v" = 'unit' THEN
3192 "output_row"."participation" := NULL;
3193 END IF;
3194 RETURN NEXT "output_row";
3195 "i" := "i" + 1;
3196 END LOOP;
3197 RETURN;
3198 END;
3199 $$;
3201 COMMENT ON FUNCTION "delegation_chain"
3202 ( "member"."id"%TYPE,
3203 "unit"."id"%TYPE,
3204 "area"."id"%TYPE,
3205 "issue"."id"%TYPE,
3206 "member"."id"%TYPE,
3207 BOOLEAN )
3208 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
3212 ---------------------------------------------------------
3213 -- Single row returning function for delegation chains --
3214 ---------------------------------------------------------
3217 CREATE TYPE "delegation_info_loop_type" AS ENUM
3218 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
3220 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''';
3223 CREATE TYPE "delegation_info_type" AS (
3224 "own_participation" BOOLEAN,
3225 "own_delegation_scope" "delegation_scope",
3226 "first_trustee_id" INT4,
3227 "first_trustee_participation" BOOLEAN,
3228 "first_trustee_ellipsis" BOOLEAN,
3229 "other_trustee_id" INT4,
3230 "other_trustee_participation" BOOLEAN,
3231 "other_trustee_ellipsis" BOOLEAN,
3232 "delegation_loop" "delegation_info_loop_type",
3233 "participating_member_id" INT4 );
3235 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';
3237 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
3238 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
3239 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
3240 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
3241 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
3242 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
3243 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)';
3244 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
3245 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';
3246 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';
3249 CREATE FUNCTION "delegation_info"
3250 ( "member_id_p" "member"."id"%TYPE,
3251 "unit_id_p" "unit"."id"%TYPE,
3252 "area_id_p" "area"."id"%TYPE,
3253 "issue_id_p" "issue"."id"%TYPE,
3254 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
3255 "simulate_default_p" BOOLEAN DEFAULT FALSE )
3256 RETURNS "delegation_info_type"
3257 LANGUAGE 'plpgsql' STABLE AS $$
3258 DECLARE
3259 "current_row" "delegation_chain_row";
3260 "result" "delegation_info_type";
3261 BEGIN
3262 "result"."own_participation" := FALSE;
3263 FOR "current_row" IN
3264 SELECT * FROM "delegation_chain"(
3265 "member_id_p",
3266 "unit_id_p", "area_id_p", "issue_id_p",
3267 "simulate_trustee_id_p", "simulate_default_p")
3268 LOOP
3269 IF
3270 "result"."participating_member_id" ISNULL AND
3271 "current_row"."participation"
3272 THEN
3273 "result"."participating_member_id" := "current_row"."member_id";
3274 END IF;
3275 IF "current_row"."member_id" = "member_id_p" THEN
3276 "result"."own_participation" := "current_row"."participation";
3277 "result"."own_delegation_scope" := "current_row"."scope_out";
3278 IF "current_row"."loop" = 'first' THEN
3279 "result"."delegation_loop" := 'own';
3280 END IF;
3281 ELSIF
3282 "current_row"."member_valid" AND
3283 ( "current_row"."loop" ISNULL OR
3284 "current_row"."loop" != 'repetition' )
3285 THEN
3286 IF "result"."first_trustee_id" ISNULL THEN
3287 "result"."first_trustee_id" := "current_row"."member_id";
3288 "result"."first_trustee_participation" := "current_row"."participation";
3289 "result"."first_trustee_ellipsis" := FALSE;
3290 IF "current_row"."loop" = 'first' THEN
3291 "result"."delegation_loop" := 'first';
3292 END IF;
3293 ELSIF "result"."other_trustee_id" ISNULL THEN
3294 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
3295 "result"."other_trustee_id" := "current_row"."member_id";
3296 "result"."other_trustee_participation" := TRUE;
3297 "result"."other_trustee_ellipsis" := FALSE;
3298 IF "current_row"."loop" = 'first' THEN
3299 "result"."delegation_loop" := 'other';
3300 END IF;
3301 ELSE
3302 "result"."first_trustee_ellipsis" := TRUE;
3303 IF "current_row"."loop" = 'first' THEN
3304 "result"."delegation_loop" := 'first_ellipsis';
3305 END IF;
3306 END IF;
3307 ELSE
3308 "result"."other_trustee_ellipsis" := TRUE;
3309 IF "current_row"."loop" = 'first' THEN
3310 "result"."delegation_loop" := 'other_ellipsis';
3311 END IF;
3312 END IF;
3313 END IF;
3314 END LOOP;
3315 RETURN "result";
3316 END;
3317 $$;
3319 COMMENT ON FUNCTION "delegation_info"
3320 ( "member"."id"%TYPE,
3321 "unit"."id"%TYPE,
3322 "area"."id"%TYPE,
3323 "issue"."id"%TYPE,
3324 "member"."id"%TYPE,
3325 BOOLEAN )
3326 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
3330 ---------------------------
3331 -- Transaction isolation --
3332 ---------------------------
3335 CREATE FUNCTION "require_transaction_isolation"()
3336 RETURNS VOID
3337 LANGUAGE 'plpgsql' VOLATILE AS $$
3338 BEGIN
3339 IF
3340 current_setting('transaction_isolation') NOT IN
3341 ('repeatable read', 'serializable')
3342 THEN
3343 RAISE EXCEPTION 'Insufficient transaction isolation level' USING
3344 HINT = 'Consider using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ.';
3345 END IF;
3346 RETURN;
3347 END;
3348 $$;
3350 COMMENT ON FUNCTION "require_transaction_isolation"() IS 'Throws an exception, if transaction isolation level is too low to provide a consistent snapshot';
3353 CREATE FUNCTION "dont_require_transaction_isolation"()
3354 RETURNS VOID
3355 LANGUAGE 'plpgsql' VOLATILE AS $$
3356 BEGIN
3357 IF
3358 current_setting('transaction_isolation') IN
3359 ('repeatable read', 'serializable')
3360 THEN
3361 RAISE WARNING 'Unneccessary transaction isolation level: %',
3362 current_setting('transaction_isolation');
3363 END IF;
3364 RETURN;
3365 END;
3366 $$;
3368 COMMENT ON FUNCTION "dont_require_transaction_isolation"() IS 'Raises a warning, if transaction isolation level is higher than READ COMMITTED';
3372 -------------------------
3373 -- Notification system --
3374 -------------------------
3376 CREATE FUNCTION "get_initiatives_for_notification"
3377 ( "recipient_id_p" "member"."id"%TYPE )
3378 RETURNS SETOF "initiative_for_notification"
3379 LANGUAGE 'plpgsql' VOLATILE AS $$
3380 DECLARE
3381 "result_row" "initiative_for_notification"%ROWTYPE;
3382 "last_draft_id_v" "draft"."id"%TYPE;
3383 "last_suggestion_id_v" "suggestion"."id"%TYPE;
3384 BEGIN
3385 PERFORM "require_transaction_isolation"();
3386 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
3387 FOR "result_row" IN
3388 SELECT * FROM "initiative_for_notification"
3389 WHERE "recipient_id" = "recipient_id_p"
3390 LOOP
3391 SELECT "id" INTO "last_draft_id_v" FROM "draft"
3392 WHERE "draft"."initiative_id" = "result_row"."initiative_id"
3393 ORDER BY "id" DESC LIMIT 1;
3394 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
3395 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
3396 ORDER BY "id" DESC LIMIT 1;
3397 /* compatibility with PostgreSQL 9.1 */
3398 DELETE FROM "notification_initiative_sent"
3399 WHERE "member_id" = "recipient_id_p"
3400 AND "initiative_id" = "result_row"."initiative_id";
3401 INSERT INTO "notification_initiative_sent"
3402 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
3403 VALUES (
3404 "recipient_id_p",
3405 "result_row"."initiative_id",
3406 "last_draft_id_v",
3407 "last_suggestion_id_v" );
3408 /* TODO: use alternative code below, requires PostgreSQL 9.5 or higher
3409 INSERT INTO "notification_initiative_sent"
3410 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
3411 VALUES (
3412 "recipient_id_p",
3413 "result_row"."initiative_id",
3414 "last_draft_id_v",
3415 "last_suggestion_id_v" )
3416 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
3417 "last_draft_id" = "last_draft_id_v",
3418 "last_suggestion_id" = "last_suggestion_id_v";
3419 */
3420 RETURN NEXT "result_row";
3421 END LOOP;
3422 DELETE FROM "notification_initiative_sent"
3423 USING "initiative", "issue"
3424 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
3425 AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
3426 AND "issue"."id" = "initiative"."issue_id"
3427 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
3428 UPDATE "member" SET
3429 "notification_counter" = "notification_counter" + 1,
3430 "notification_sent" = now()
3431 WHERE "id" = "recipient_id_p";
3432 RETURN;
3433 END;
3434 $$;
3436 COMMENT ON FUNCTION "get_initiatives_for_notification"
3437 ( "member"."id"%TYPE )
3438 IS 'Returns rows from view "initiative_for_notification" for a given recipient while updating table "notification_initiative_sent" and columns "notification_counter" and "notification_sent" of "member" table';
3442 ------------------------------------------------------------------------
3443 -- Regular tasks, except calculcation of snapshots and voting results --
3444 ------------------------------------------------------------------------
3447 CREATE FUNCTION "check_activity"()
3448 RETURNS VOID
3449 LANGUAGE 'plpgsql' VOLATILE AS $$
3450 DECLARE
3451 "system_setting_row" "system_setting"%ROWTYPE;
3452 BEGIN
3453 PERFORM "dont_require_transaction_isolation"();
3454 SELECT * INTO "system_setting_row" FROM "system_setting";
3455 IF "system_setting_row"."member_ttl" NOTNULL THEN
3456 UPDATE "member" SET "active" = FALSE
3457 WHERE "active" = TRUE
3458 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
3459 END IF;
3460 RETURN;
3461 END;
3462 $$;
3464 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
3467 CREATE FUNCTION "calculate_member_counts"()
3468 RETURNS VOID
3469 LANGUAGE 'plpgsql' VOLATILE AS $$
3470 BEGIN
3471 PERFORM "require_transaction_isolation"();
3472 DELETE FROM "member_count";
3473 INSERT INTO "member_count" ("total_count")
3474 SELECT "total_count" FROM "member_count_view";
3475 UPDATE "unit" SET "member_count" = "view"."member_count"
3476 FROM "unit_member_count" AS "view"
3477 WHERE "view"."unit_id" = "unit"."id";
3478 RETURN;
3479 END;
3480 $$;
3482 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"';
3486 ------------------------------------
3487 -- Calculation of harmonic weight --
3488 ------------------------------------
3491 CREATE VIEW "remaining_harmonic_supporter_weight" AS
3492 SELECT
3493 "direct_interest_snapshot"."snapshot_id",
3494 "direct_interest_snapshot"."issue_id",
3495 "direct_interest_snapshot"."member_id",
3496 "direct_interest_snapshot"."weight" AS "weight_num",
3497 count("initiative"."id") AS "weight_den"
3498 FROM "issue"
3499 JOIN "direct_interest_snapshot"
3500 ON "issue"."latest_snapshot_id" = "direct_interest_snapshot"."snapshot_id"
3501 AND "issue"."id" = "direct_interest_snapshot"."issue_id"
3502 JOIN "initiative"
3503 ON "issue"."id" = "initiative"."issue_id"
3504 AND "initiative"."harmonic_weight" ISNULL
3505 JOIN "direct_supporter_snapshot"
3506 ON "issue"."latest_snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
3507 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
3508 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
3509 AND (
3510 "direct_supporter_snapshot"."satisfied" = TRUE OR
3511 coalesce("initiative"."admitted", FALSE) = FALSE
3512 )
3513 GROUP BY
3514 "direct_interest_snapshot"."snapshot_id",
3515 "direct_interest_snapshot"."issue_id",
3516 "direct_interest_snapshot"."member_id",
3517 "direct_interest_snapshot"."weight";
3519 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
3522 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
3523 SELECT
3524 "initiative"."issue_id",
3525 "initiative"."id" AS "initiative_id",
3526 "initiative"."admitted",
3527 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
3528 "remaining_harmonic_supporter_weight"."weight_den"
3529 FROM "remaining_harmonic_supporter_weight"
3530 JOIN "initiative"
3531 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
3532 AND "initiative"."harmonic_weight" ISNULL
3533 JOIN "direct_supporter_snapshot"
3534 ON "remaining_harmonic_supporter_weight"."snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
3535 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
3536 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
3537 AND (
3538 "direct_supporter_snapshot"."satisfied" = TRUE OR
3539 coalesce("initiative"."admitted", FALSE) = FALSE
3540 )
3541 GROUP BY
3542 "initiative"."issue_id",
3543 "initiative"."id",
3544 "initiative"."admitted",
3545 "remaining_harmonic_supporter_weight"."weight_den";
3547 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
3550 CREATE VIEW "remaining_harmonic_initiative_weight_dummies" AS
3551 SELECT
3552 "issue_id",
3553 "id" AS "initiative_id",
3554 "admitted",
3555 0 AS "weight_num",
3556 1 AS "weight_den"
3557 FROM "initiative"
3558 WHERE "harmonic_weight" ISNULL;
3560 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';
3563 CREATE FUNCTION "set_harmonic_initiative_weights"
3564 ( "issue_id_p" "issue"."id"%TYPE )
3565 RETURNS VOID
3566 LANGUAGE 'plpgsql' VOLATILE AS $$
3567 DECLARE
3568 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
3569 "i" INT4;
3570 "count_v" INT4;
3571 "summand_v" FLOAT;
3572 "id_ary" INT4[];
3573 "weight_ary" FLOAT[];
3574 "min_weight_v" FLOAT;
3575 BEGIN
3576 PERFORM "require_transaction_isolation"();
3577 UPDATE "initiative" SET "harmonic_weight" = NULL
3578 WHERE "issue_id" = "issue_id_p";
3579 LOOP
3580 "min_weight_v" := NULL;
3581 "i" := 0;
3582 "count_v" := 0;
3583 FOR "weight_row" IN
3584 SELECT * FROM "remaining_harmonic_initiative_weight_summands"
3585 WHERE "issue_id" = "issue_id_p"
3586 AND (
3587 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
3588 SELECT NULL FROM "initiative"
3589 WHERE "issue_id" = "issue_id_p"
3590 AND "harmonic_weight" ISNULL
3591 AND coalesce("admitted", FALSE) = FALSE
3592 )
3593 )
3594 UNION ALL -- needed for corner cases
3595 SELECT * FROM "remaining_harmonic_initiative_weight_dummies"
3596 WHERE "issue_id" = "issue_id_p"
3597 AND (
3598 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
3599 SELECT NULL FROM "initiative"
3600 WHERE "issue_id" = "issue_id_p"
3601 AND "harmonic_weight" ISNULL
3602 AND coalesce("admitted", FALSE) = FALSE
3603 )
3604 )
3605 ORDER BY "initiative_id" DESC, "weight_den" DESC
3606 -- NOTE: non-admitted initiatives placed first (at last positions),
3607 -- latest initiatives treated worse in case of tie
3608 LOOP
3609 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
3610 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
3611 "i" := "i" + 1;
3612 "count_v" := "i";
3613 "id_ary"["i"] := "weight_row"."initiative_id";
3614 "weight_ary"["i"] := "summand_v";
3615 ELSE
3616 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
3617 END IF;
3618 END LOOP;
3619 EXIT WHEN "count_v" = 0;
3620 "i" := 1;
3621 LOOP
3622 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
3623 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
3624 "min_weight_v" := "weight_ary"["i"];
3625 END IF;
3626 "i" := "i" + 1;
3627 EXIT WHEN "i" > "count_v";
3628 END LOOP;
3629 "i" := 1;
3630 LOOP
3631 IF "weight_ary"["i"] = "min_weight_v" THEN
3632 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
3633 WHERE "id" = "id_ary"["i"];
3634 EXIT;
3635 END IF;
3636 "i" := "i" + 1;
3637 END LOOP;
3638 END LOOP;
3639 UPDATE "initiative" SET "harmonic_weight" = 0
3640 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
3641 END;
3642 $$;
3644 COMMENT ON FUNCTION "set_harmonic_initiative_weights"
3645 ( "issue"."id"%TYPE )
3646 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
3650 ------------------------------
3651 -- Calculation of snapshots --
3652 ------------------------------
3655 CREATE FUNCTION "weight_of_added_delegations_for_snapshot"
3656 ( "snapshot_id_p" "snapshot"."id"%TYPE,
3657 "issue_id_p" "issue"."id"%TYPE,
3658 "member_id_p" "member"."id"%TYPE,
3659 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3660 RETURNS "direct_interest_snapshot"."weight"%TYPE
3661 LANGUAGE 'plpgsql' VOLATILE AS $$
3662 DECLARE
3663 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3664 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
3665 "weight_v" INT4;
3666 "sub_weight_v" INT4;
3667 BEGIN
3668 PERFORM "require_transaction_isolation"();
3669 "weight_v" := 0;
3670 FOR "issue_delegation_row" IN
3671 SELECT * FROM "issue_delegation"
3672 WHERE "trustee_id" = "member_id_p"
3673 AND "issue_id" = "issue_id_p"
3674 LOOP
3675 IF NOT EXISTS (
3676 SELECT NULL FROM "direct_interest_snapshot"
3677 WHERE "snapshot_id" = "snapshot_id_p"
3678 AND "issue_id" = "issue_id_p"
3679 AND "member_id" = "issue_delegation_row"."truster_id"
3680 ) AND NOT EXISTS (
3681 SELECT NULL FROM "delegating_interest_snapshot"
3682 WHERE "snapshot_id" = "snapshot_id_p"
3683 AND "issue_id" = "issue_id_p"
3684 AND "member_id" = "issue_delegation_row"."truster_id"
3685 ) THEN
3686 "delegate_member_ids_v" :=
3687 "member_id_p" || "delegate_member_ids_p";
3688 INSERT INTO "delegating_interest_snapshot" (
3689 "snapshot_id",
3690 "issue_id",
3691 "member_id",
3692 "scope",
3693 "delegate_member_ids"
3694 ) VALUES (
3695 "snapshot_id_p",
3696 "issue_id_p",
3697 "issue_delegation_row"."truster_id",
3698 "issue_delegation_row"."scope",
3699 "delegate_member_ids_v"
3700 );
3701 "sub_weight_v" := 1 +
3702 "weight_of_added_delegations_for_snapshot"(
3703 "snapshot_id_p",
3704 "issue_id_p",
3705 "issue_delegation_row"."truster_id",
3706 "delegate_member_ids_v"
3707 );
3708 UPDATE "delegating_interest_snapshot"
3709 SET "weight" = "sub_weight_v"
3710 WHERE "snapshot_id" = "snapshot_id_p"
3711 AND "issue_id" = "issue_id_p"
3712 AND "member_id" = "issue_delegation_row"."truster_id";
3713 "weight_v" := "weight_v" + "sub_weight_v";
3714 END IF;
3715 END LOOP;
3716 RETURN "weight_v";
3717 END;
3718 $$;
3720 COMMENT ON FUNCTION "weight_of_added_delegations_for_snapshot"
3721 ( "snapshot"."id"%TYPE,
3722 "issue"."id"%TYPE,
3723 "member"."id"%TYPE,
3724 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3725 IS 'Helper function for "fill_snapshot" function';
3728 CREATE FUNCTION "take_snapshot"
3729 ( "issue_id_p" "issue"."id"%TYPE )
3730 RETURNS "snapshot"."id"%TYPE
3731 LANGUAGE 'plpgsql' VOLATILE AS $$
3732 DECLARE
3733 "snapshot_id_v" "snapshot"."id"%TYPE;
3734 "issue_id_v" "issue"."id"%TYPE;
3735 "member_id_v" "member"."id"%TYPE;
3736 BEGIN
3737 PERFORM "require_transaction_isolation"();
3738 INSERT INTO "snapshot" DEFAULT VALUES
3739 RETURNING "id" INTO "snapshot_id_v";
3740 FOR "issue_id_v" IN
3741 SELECT "id" FROM "issue"
3742 WHERE CASE WHEN "issue_id_p" ISNULL THEN
3743 "state" = 'admission'
3744 ELSE
3745 "id" = "issue_id_p"
3746 END
3747 LOOP
3748 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
3749 VALUES ("snapshot_id_v", "issue_id_v");
3750 INSERT INTO "direct_interest_snapshot"
3751 ("snapshot_id", "issue_id", "member_id")
3752 SELECT
3753 "snapshot_id_v" AS "snapshot_id",
3754 "issue_id_v" AS "issue_id",
3755 "member"."id" AS "member_id"
3756 FROM "issue"
3757 JOIN "area" ON "issue"."area_id" = "area"."id"
3758 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3759 JOIN "member" ON "interest"."member_id" = "member"."id"
3760 JOIN "privilege"
3761 ON "privilege"."unit_id" = "area"."unit_id"
3762 AND "privilege"."member_id" = "member"."id"
3763 WHERE "issue"."id" = "issue_id_v"
3764 AND "member"."active" AND "privilege"."voting_right";
3765 FOR "member_id_v" IN
3766 SELECT "member_id" FROM "direct_interest_snapshot"
3767 WHERE "snapshot_id" = "snapshot_id_v"
3768 AND "issue_id" = "issue_id_v"
3769 LOOP
3770 UPDATE "direct_interest_snapshot" SET
3771 "weight" = 1 +
3772 "weight_of_added_delegations_for_snapshot"(
3773 "snapshot_id_v",
3774 "issue_id_v",
3775 "member_id_v",
3776 '{}'
3777 )
3778 WHERE "snapshot_id" = "snapshot_id_v"
3779 AND "issue_id" = "issue_id_v"
3780 AND "member_id" = "member_id_v";
3781 END LOOP;
3782 INSERT INTO "direct_supporter_snapshot"
3783 ( "snapshot_id", "issue_id", "initiative_id", "member_id",
3784 "draft_id", "informed", "satisfied" )
3785 SELECT
3786 "snapshot_id_v" AS "snapshot_id",
3787 "issue_id_v" AS "issue_id",
3788 "initiative"."id" AS "initiative_id",
3789 "supporter"."member_id" AS "member_id",
3790 "supporter"."draft_id" AS "draft_id",
3791 "supporter"."draft_id" = "current_draft"."id" AS "informed",
3792 NOT EXISTS (
3793 SELECT NULL FROM "critical_opinion"
3794 WHERE "initiative_id" = "initiative"."id"
3795 AND "member_id" = "supporter"."member_id"
3796 ) AS "satisfied"
3797 FROM "initiative"
3798 JOIN "supporter"
3799 ON "supporter"."initiative_id" = "initiative"."id"
3800 JOIN "current_draft"
3801 ON "initiative"."id" = "current_draft"."initiative_id"
3802 JOIN "direct_interest_snapshot"
3803 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"
3804 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"
3805 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
3806 WHERE "initiative"."issue_id" = "issue_id_v";
3807 DELETE FROM "temporary_suggestion_counts";
3808 INSERT INTO "temporary_suggestion_counts"
3809 ( "id",
3810 "minus2_unfulfilled_count", "minus2_fulfilled_count",
3811 "minus1_unfulfilled_count", "minus1_fulfilled_count",
3812 "plus1_unfulfilled_count", "plus1_fulfilled_count",
3813 "plus2_unfulfilled_count", "plus2_fulfilled_count" )
3814 SELECT
3815 "suggestion"."id",
3816 ( SELECT coalesce(sum("di"."weight"), 0)
3817 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
3818 ON "di"."snapshot_id" = "snapshot_id_v"
3819 AND "di"."issue_id" = "issue_id_v"
3820 AND "di"."member_id" = "opinion"."member_id"
3821 WHERE "opinion"."suggestion_id" = "suggestion"."id"
3822 AND "opinion"."degree" = -2
3823 AND "opinion"."fulfilled" = FALSE
3824 ) AS "minus2_unfulfilled_count",
3825 ( SELECT coalesce(sum("di"."weight"), 0)
3826 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
3827 ON "di"."snapshot_id" = "snapshot_id_v"
3828 AND "di"."issue_id" = "issue_id_v"
3829 AND "di"."member_id" = "opinion"."member_id"
3830 WHERE "opinion"."suggestion_id" = "suggestion"."id"
3831 AND "opinion"."degree" = -2
3832 AND "opinion"."fulfilled" = TRUE
3833 ) AS "minus2_fulfilled_count",
3834 ( SELECT coalesce(sum("di"."weight"), 0)
3835 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
3836 ON "di"."snapshot_id" = "snapshot_id_v"
3837 AND "di"."issue_id" = "issue_id_v"
3838 AND "di"."member_id" = "opinion"."member_id"
3839 WHERE "opinion"."suggestion_id" = "suggestion"."id"
3840 AND "opinion"."degree" = -1
3841 AND "opinion"."fulfilled" = FALSE
3842 ) AS "minus1_unfulfilled_count",
3843 ( SELECT coalesce(sum("di"."weight"), 0)
3844 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
3845 ON "di"."snapshot_id" = "snapshot_id_v"
3846 AND "di"."issue_id" = "issue_id_v"
3847 AND "di"."member_id" = "opinion"."member_id"
3848 WHERE "opinion"."suggestion_id" = "suggestion"."id"
3849 AND "opinion"."degree" = -1
3850 AND "opinion"."fulfilled" = TRUE
3851 ) AS "minus1_fulfilled_count",
3852 ( SELECT coalesce(sum("di"."weight"), 0)
3853 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
3854 ON "di"."snapshot_id" = "snapshot_id_v"
3855 AND "di"."issue_id" = "issue_id_v"
3856 AND "di"."member_id" = "opinion"."member_id"
3857 WHERE "opinion"."suggestion_id" = "suggestion"."id"
3858 AND "opinion"."degree" = 1
3859 AND "opinion"."fulfilled" = FALSE
3860 ) AS "plus1_unfulfilled_count",
3861 ( SELECT coalesce(sum("di"."weight"), 0)
3862 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
3863 ON "di"."snapshot_id" = "snapshot_id_v"
3864 AND "di"."issue_id" = "issue_id_v"
3865 AND "di"."member_id" = "opinion"."member_id"
3866 WHERE "opinion"."suggestion_id" = "suggestion"."id"
3867 AND "opinion"."degree" = 1
3868 AND "opinion"."fulfilled" = TRUE
3869 ) AS "plus1_fulfilled_count",
3870 ( SELECT coalesce(sum("di"."weight"), 0)
3871 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
3872 ON "di"."snapshot_id" = "snapshot_id_v"
3873 AND "di"."issue_id" = "issue_id_v"
3874 AND "di"."member_id" = "opinion"."member_id"
3875 WHERE "opinion"."suggestion_id" = "suggestion"."id"
3876 AND "opinion"."degree" = 2
3877 AND "opinion"."fulfilled" = FALSE
3878 ) AS "plus2_unfulfilled_count",
3879 ( SELECT coalesce(sum("di"."weight"), 0)
3880 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
3881 ON "di"."snapshot_id" = "snapshot_id_v"
3882 AND "di"."issue_id" = "issue_id_v"
3883 AND "di"."member_id" = "opinion"."member_id"
3884 WHERE "opinion"."suggestion_id" = "suggestion"."id"
3885 AND "opinion"."degree" = 2
3886 AND "opinion"."fulfilled" = TRUE
3887 ) AS "plus2_fulfilled_count"
3888 FROM "suggestion" JOIN "initiative"
3889 ON "suggestion"."initiative_id" = "initiative"."id"
3890 WHERE "initiative"."issue_id" = "issue_id_v";
3891 END LOOP;
3892 RETURN "snapshot_id_v";
3893 END;
3894 $$;
3896 COMMENT ON FUNCTION "take_snapshot"
3897 ( "issue"."id"%TYPE )
3898 IS 'This function creates a new interest/supporter snapshot of a particular issue, or, if the argument is NULL, for all issues in ''admission'' phase. It must be executed with TRANSACTION ISOLATION LEVEL REPEATABLE READ. The snapshot must later be finished by calling "finish_snapshot" for every issue.';
3901 CREATE FUNCTION "finish_snapshot"
3902 ( "issue_id_p" "issue"."id"%TYPE )
3903 RETURNS VOID
3904 LANGUAGE 'plpgsql' VOLATILE AS $$
3905 DECLARE
3906 "snapshot_id_v" "snapshot"."id"%TYPE;
3907 BEGIN
3908 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
3909 SELECT "id" INTO "snapshot_id_v" FROM "snapshot"
3910 ORDER BY "id" DESC LIMIT 1;
3911 UPDATE "issue" SET
3912 "latest_snapshot_id" = "snapshot_id_v",
3913 "population" = (
3914 SELECT coalesce(sum("weight"), 0)
3915 FROM "direct_interest_snapshot"
3916 WHERE "snapshot_id" = "snapshot_id_v"
3917 AND "issue_id" = "issue_id_p"
3918 )
3919 WHERE "id" = "issue_id_p";
3920 UPDATE "initiative" SET
3921 "supporter_count" = (
3922 SELECT coalesce(sum("di"."weight"), 0)
3923 FROM "direct_interest_snapshot" AS "di"
3924 JOIN "direct_supporter_snapshot" AS "ds"
3925 ON "di"."member_id" = "ds"."member_id"
3926 WHERE "di"."snapshot_id" = "snapshot_id_v"
3927 AND "di"."issue_id" = "issue_id_p"
3928 AND "ds"."snapshot_id" = "snapshot_id_v"
3929 AND "ds"."initiative_id" = "initiative"."id"
3930 ),
3931 "informed_supporter_count" = (
3932 SELECT coalesce(sum("di"."weight"), 0)
3933 FROM "direct_interest_snapshot" AS "di"
3934 JOIN "direct_supporter_snapshot" AS "ds"
3935 ON "di"."member_id" = "ds"."member_id"
3936 WHERE "di"."snapshot_id" = "snapshot_id_v"
3937 AND "di"."issue_id" = "issue_id_p"
3938 AND "ds"."snapshot_id" = "snapshot_id_v"
3939 AND "ds"."initiative_id" = "initiative"."id"
3940 AND "ds"."informed"
3941 ),
3942 "satisfied_supporter_count" = (
3943 SELECT coalesce(sum("di"."weight"), 0)
3944 FROM "direct_interest_snapshot" AS "di"
3945 JOIN "direct_supporter_snapshot" AS "ds"
3946 ON "di"."member_id" = "ds"."member_id"
3947 WHERE "di"."snapshot_id" = "snapshot_id_v"
3948 AND "di"."issue_id" = "issue_id_p"
3949 AND "ds"."snapshot_id" = "snapshot_id_v"
3950 AND "ds"."initiative_id" = "initiative"."id"
3951 AND "ds"."satisfied"
3952 ),
3953 "satisfied_informed_supporter_count" = (
3954 SELECT coalesce(sum("di"."weight"), 0)
3955 FROM "direct_interest_snapshot" AS "di"
3956 JOIN "direct_supporter_snapshot" AS "ds"
3957 ON "di"."member_id" = "ds"."member_id"
3958 WHERE "di"."snapshot_id" = "snapshot_id_v"
3959 AND "di"."issue_id" = "issue_id_p"
3960 AND "ds"."snapshot_id" = "snapshot_id_v"
3961 AND "ds"."initiative_id" = "initiative"."id"
3962 AND "ds"."informed"
3963 AND "ds"."satisfied"
3964 )
3965 WHERE "issue_id" = "issue_id_p";
3966 UPDATE "suggestion" SET
3967 "minus2_unfulfilled_count" = "temp"."minus2_unfulfilled_count",
3968 "minus2_fulfilled_count" = "temp"."minus2_fulfilled_count",
3969 "minus1_unfulfilled_count" = "temp"."minus1_unfulfilled_count",
3970 "minus1_fulfilled_count" = "temp"."minus1_fulfilled_count",
3971 "plus1_unfulfilled_count" = "temp"."plus1_unfulfilled_count",
3972 "plus1_fulfilled_count" = "temp"."plus1_fulfilled_count",
3973 "plus2_unfulfilled_count" = "temp"."plus2_unfulfilled_count",
3974 "plus2_fulfilled_count" = "temp"."plus2_fulfilled_count"
3975 FROM "temporary_suggestion_counts" AS "temp", "initiative"
3976 WHERE "temp"."id" = "suggestion"."id"
3977 AND "initiative"."issue_id" = "issue_id_p"
3978 AND "suggestion"."initiative_id" = "initiative"."id";
3979 DELETE FROM "temporary_suggestion_counts";
3980 RETURN;
3981 END;
3982 $$;
3984 COMMENT ON FUNCTION "finish_snapshot"
3985 ( "issue"."id"%TYPE )
3986 IS 'After calling "take_snapshot", this function "finish_snapshot" needs to be called for every issue in the snapshot (separate function calls keep locking time minimal)';
3990 -----------------------
3991 -- Counting of votes --
3992 -----------------------
3995 CREATE FUNCTION "weight_of_added_vote_delegations"
3996 ( "issue_id_p" "issue"."id"%TYPE,
3997 "member_id_p" "member"."id"%TYPE,
3998 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
3999 RETURNS "direct_voter"."weight"%TYPE
4000 LANGUAGE 'plpgsql' VOLATILE AS $$
4001 DECLARE
4002 "issue_delegation_row" "issue_delegation"%ROWTYPE;
4003 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
4004 "weight_v" INT4;
4005 "sub_weight_v" INT4;
4006 BEGIN
4007 PERFORM "require_transaction_isolation"();
4008 "weight_v" := 0;
4009 FOR "issue_delegation_row" IN
4010 SELECT * FROM "issue_delegation"
4011 WHERE "trustee_id" = "member_id_p"
4012 AND "issue_id" = "issue_id_p"
4013 LOOP
4014 IF NOT EXISTS (
4015 SELECT NULL FROM "direct_voter"
4016 WHERE "member_id" = "issue_delegation_row"."truster_id"
4017 AND "issue_id" = "issue_id_p"
4018 ) AND NOT EXISTS (
4019 SELECT NULL FROM "delegating_voter"
4020 WHERE "member_id" = "issue_delegation_row"."truster_id"
4021 AND "issue_id" = "issue_id_p"
4022 ) THEN
4023 "delegate_member_ids_v" :=
4024 "member_id_p" || "delegate_member_ids_p";
4025 INSERT INTO "delegating_voter" (
4026 "issue_id",
4027 "member_id",
4028 "scope",
4029 "delegate_member_ids"
4030 ) VALUES (
4031 "issue_id_p",
4032 "issue_delegation_row"."truster_id",
4033 "issue_delegation_row"."scope",
4034 "delegate_member_ids_v"
4035 );
4036 "sub_weight_v" := 1 +
4037 "weight_of_added_vote_delegations"(
4038 "issue_id_p",
4039 "issue_delegation_row"."truster_id",
4040 "delegate_member_ids_v"
4041 );
4042 UPDATE "delegating_voter"
4043 SET "weight" = "sub_weight_v"
4044 WHERE "issue_id" = "issue_id_p"
4045 AND "member_id" = "issue_delegation_row"."truster_id";
4046 "weight_v" := "weight_v" + "sub_weight_v";
4047 END IF;
4048 END LOOP;
4049 RETURN "weight_v";
4050 END;
4051 $$;
4053 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
4054 ( "issue"."id"%TYPE,
4055 "member"."id"%TYPE,
4056 "delegating_voter"."delegate_member_ids"%TYPE )
4057 IS 'Helper function for "add_vote_delegations" function';
4060 CREATE FUNCTION "add_vote_delegations"
4061 ( "issue_id_p" "issue"."id"%TYPE )
4062 RETURNS VOID
4063 LANGUAGE 'plpgsql' VOLATILE AS $$
4064 DECLARE
4065 "member_id_v" "member"."id"%TYPE;
4066 BEGIN
4067 PERFORM "require_transaction_isolation"();
4068 FOR "member_id_v" IN
4069 SELECT "member_id" FROM "direct_voter"
4070 WHERE "issue_id" = "issue_id_p"
4071 LOOP
4072 UPDATE "direct_voter" SET
4073 "weight" = "weight" + "weight_of_added_vote_delegations"(
4074 "issue_id_p",
4075 "member_id_v",
4076 '{}'
4077 )
4078 WHERE "member_id" = "member_id_v"
4079 AND "issue_id" = "issue_id_p";
4080 END LOOP;
4081 RETURN;
4082 END;
4083 $$;
4085 COMMENT ON FUNCTION "add_vote_delegations"
4086 ( "issue_id_p" "issue"."id"%TYPE )
4087 IS 'Helper function for "close_voting" function';
4090 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
4091 RETURNS VOID
4092 LANGUAGE 'plpgsql' VOLATILE AS $$
4093 DECLARE
4094 "area_id_v" "area"."id"%TYPE;
4095 "unit_id_v" "unit"."id"%TYPE;
4096 "member_id_v" "member"."id"%TYPE;
4097 BEGIN
4098 PERFORM "require_transaction_isolation"();
4099 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
4100 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
4101 -- override protection triggers:
4102 INSERT INTO "temporary_transaction_data" ("key", "value")
4103 VALUES ('override_protection_triggers', TRUE::TEXT);
4104 -- delete timestamp of voting comment:
4105 UPDATE "direct_voter" SET "comment_changed" = NULL
4106 WHERE "issue_id" = "issue_id_p";
4107 -- delete delegating votes (in cases of manual reset of issue state):
4108 DELETE FROM "delegating_voter"
4109 WHERE "issue_id" = "issue_id_p";
4110 -- delete votes from non-privileged voters:
4111 DELETE FROM "direct_voter"
4112 USING (
4113 SELECT
4114 "direct_voter"."member_id"
4115 FROM "direct_voter"
4116 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
4117 LEFT JOIN "privilege"
4118 ON "privilege"."unit_id" = "unit_id_v"
4119 AND "privilege"."member_id" = "direct_voter"."member_id"
4120 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
4121 "member"."active" = FALSE OR
4122 "privilege"."voting_right" ISNULL OR
4123 "privilege"."voting_right" = FALSE
4124 )
4125 ) AS "subquery"
4126 WHERE "direct_voter"."issue_id" = "issue_id_p"
4127 AND "direct_voter"."member_id" = "subquery"."member_id";
4128 -- consider delegations:
4129 UPDATE "direct_voter" SET "weight" = 1
4130 WHERE "issue_id" = "issue_id_p";
4131 PERFORM "add_vote_delegations"("issue_id_p");
4132 -- mark first preferences:
4133 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
4134 FROM (
4135 SELECT
4136 "vote"."initiative_id",
4137 "vote"."member_id",
4138 CASE WHEN "vote"."grade" > 0 THEN
4139 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
4140 ELSE NULL
4141 END AS "first_preference"
4142 FROM "vote"
4143 JOIN "initiative" -- NOTE: due to missing index on issue_id
4144 ON "vote"."issue_id" = "initiative"."issue_id"
4145 JOIN "vote" AS "agg"
4146 ON "initiative"."id" = "agg"."initiative_id"
4147 AND "vote"."member_id" = "agg"."member_id"
4148 GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
4149 ) AS "subquery"
4150 WHERE "vote"."issue_id" = "issue_id_p"
4151 AND "vote"."initiative_id" = "subquery"."initiative_id"
4152 AND "vote"."member_id" = "subquery"."member_id";
4153 -- finish overriding protection triggers (avoids garbage):
4154 DELETE FROM "temporary_transaction_data"
4155 WHERE "key" = 'override_protection_triggers';
4156 -- materialize battle_view:
4157 -- NOTE: "closed" column of issue must be set at this point
4158 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
4159 INSERT INTO "battle" (
4160 "issue_id",
4161 "winning_initiative_id", "losing_initiative_id",
4162 "count"
4163 ) SELECT
4164 "issue_id",
4165 "winning_initiative_id", "losing_initiative_id",
4166 "count"
4167 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
4168 -- set voter count:
4169 UPDATE "issue" SET
4170 "voter_count" = (
4171 SELECT coalesce(sum("weight"), 0)
4172 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
4173 )
4174 WHERE "id" = "issue_id_p";
4175 -- copy "positive_votes" and "negative_votes" from "battle" table:
4176 -- NOTE: "first_preference_votes" is set to a default of 0 at this step
4177 UPDATE "initiative" SET
4178 "first_preference_votes" = 0,
4179 "positive_votes" = "battle_win"."count",
4180 "negative_votes" = "battle_lose"."count"
4181 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
4182 WHERE
4183 "battle_win"."issue_id" = "issue_id_p" AND
4184 "battle_win"."winning_initiative_id" = "initiative"."id" AND
4185 "battle_win"."losing_initiative_id" ISNULL AND
4186 "battle_lose"."issue_id" = "issue_id_p" AND
4187 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
4188 "battle_lose"."winning_initiative_id" ISNULL;
4189 -- calculate "first_preference_votes":
4190 -- NOTE: will only set values not equal to zero
4191 UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum"
4192 FROM (
4193 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
4194 FROM "vote" JOIN "direct_voter"
4195 ON "vote"."issue_id" = "direct_voter"."issue_id"
4196 AND "vote"."member_id" = "direct_voter"."member_id"
4197 WHERE "vote"."first_preference"
4198 GROUP BY "vote"."initiative_id"
4199 ) AS "subquery"
4200 WHERE "initiative"."issue_id" = "issue_id_p"
4201 AND "initiative"."admitted"
4202 AND "initiative"."id" = "subquery"."initiative_id";
4203 END;
4204 $$;
4206 COMMENT ON FUNCTION "close_voting"
4207 ( "issue"."id"%TYPE )
4208 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.';
4211 CREATE FUNCTION "defeat_strength"
4212 ( "positive_votes_p" INT4,
4213 "negative_votes_p" INT4,
4214 "defeat_strength_p" "defeat_strength" )
4215 RETURNS INT8
4216 LANGUAGE 'plpgsql' IMMUTABLE AS $$
4217 BEGIN
4218 IF "defeat_strength_p" = 'simple'::"defeat_strength" THEN
4219 IF "positive_votes_p" > "negative_votes_p" THEN
4220 RETURN "positive_votes_p";
4221 ELSE
4222 RETURN 0;
4223 END IF;
4224 ELSE
4225 IF "positive_votes_p" > "negative_votes_p" THEN
4226 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
4227 ELSIF "positive_votes_p" = "negative_votes_p" THEN
4228 RETURN 0;
4229 ELSE
4230 RETURN -1;
4231 END IF;
4232 END IF;
4233 END;
4234 $$;
4236 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")';
4239 CREATE FUNCTION "secondary_link_strength"
4240 ( "initiative1_ord_p" INT4,
4241 "initiative2_ord_p" INT4,
4242 "tie_breaking_p" "tie_breaking" )
4243 RETURNS INT8
4244 LANGUAGE 'plpgsql' IMMUTABLE AS $$
4245 BEGIN
4246 IF "initiative1_ord_p" = "initiative2_ord_p" THEN
4247 RAISE EXCEPTION 'Identical initiative ids passed to "secondary_link_strength" function (should not happen)';
4248 END IF;
4249 RETURN (
4250 CASE WHEN "tie_breaking_p" = 'simple'::"tie_breaking" THEN
4251 0
4252 ELSE
4253 CASE WHEN "initiative1_ord_p" < "initiative2_ord_p" THEN
4254 1::INT8 << 62
4255 ELSE 0 END
4256 +
4257 CASE WHEN "tie_breaking_p" = 'variant2'::"tie_breaking" THEN
4258 ("initiative2_ord_p"::INT8 << 31) - "initiative1_ord_p"::INT8
4259 ELSE
4260 "initiative2_ord_p"::INT8 - ("initiative1_ord_p"::INT8 << 31)
4261 END
4262 END
4263 );
4264 END;
4265 $$;
4267 COMMENT ON FUNCTION "secondary_link_strength"(INT4, INT4, "tie_breaking") IS 'Calculates a secondary criterion for the defeat strength (tie-breaking of the links)';
4270 CREATE TYPE "link_strength" AS (
4271 "primary" INT8,
4272 "secondary" INT8 );
4274 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'')';
4277 CREATE FUNCTION "find_best_paths"("matrix_d" "link_strength"[][])
4278 RETURNS "link_strength"[][]
4279 LANGUAGE 'plpgsql' IMMUTABLE AS $$
4280 DECLARE
4281 "dimension_v" INT4;
4282 "matrix_p" "link_strength"[][];
4283 "i" INT4;
4284 "j" INT4;
4285 "k" INT4;
4286 BEGIN
4287 "dimension_v" := array_upper("matrix_d", 1);
4288 "matrix_p" := "matrix_d";
4289 "i" := 1;
4290 LOOP
4291 "j" := 1;
4292 LOOP
4293 IF "i" != "j" THEN
4294 "k" := 1;
4295 LOOP
4296 IF "i" != "k" AND "j" != "k" THEN
4297 IF "matrix_p"["j"]["i"] < "matrix_p"["i"]["k"] THEN
4298 IF "matrix_p"["j"]["i"] > "matrix_p"["j"]["k"] THEN
4299 "matrix_p"["j"]["k"] := "matrix_p"["j"]["i"];
4300 END IF;
4301 ELSE
4302 IF "matrix_p"["i"]["k"] > "matrix_p"["j"]["k"] THEN
4303 "matrix_p"["j"]["k"] := "matrix_p"["i"]["k"];
4304 END IF;
4305 END IF;
4306 END IF;
4307 EXIT WHEN "k" = "dimension_v";
4308 "k" := "k" + 1;
4309 END LOOP;
4310 END IF;
4311 EXIT WHEN "j" = "dimension_v";
4312 "j" := "j" + 1;
4313 END LOOP;
4314 EXIT WHEN "i" = "dimension_v";
4315 "i" := "i" + 1;
4316 END LOOP;
4317 RETURN "matrix_p";
4318 END;
4319 $$;
4321 COMMENT ON FUNCTION "find_best_paths"("link_strength"[][]) IS 'Computes the strengths of the best beat-paths from a square matrix';
4324 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
4325 RETURNS VOID
4326 LANGUAGE 'plpgsql' VOLATILE AS $$
4327 DECLARE
4328 "issue_row" "issue"%ROWTYPE;
4329 "policy_row" "policy"%ROWTYPE;
4330 "dimension_v" INT4;
4331 "matrix_a" INT4[][]; -- absolute votes
4332 "matrix_d" "link_strength"[][]; -- defeat strength (direct)
4333 "matrix_p" "link_strength"[][]; -- defeat strength (best path)
4334 "matrix_t" "link_strength"[][]; -- defeat strength (tie-breaking)
4335 "matrix_f" BOOLEAN[][]; -- forbidden link (tie-breaking)
4336 "matrix_b" BOOLEAN[][]; -- final order (who beats who)
4337 "i" INT4;
4338 "j" INT4;
4339 "m" INT4;
4340 "n" INT4;
4341 "battle_row" "battle"%ROWTYPE;
4342 "rank_ary" INT4[];
4343 "rank_v" INT4;
4344 "initiative_id_v" "initiative"."id"%TYPE;
4345 BEGIN
4346 PERFORM "require_transaction_isolation"();
4347 SELECT * INTO "issue_row"
4348 FROM "issue" WHERE "id" = "issue_id_p";
4349 SELECT * INTO "policy_row"
4350 FROM "policy" WHERE "id" = "issue_row"."policy_id";
4351 SELECT count(1) INTO "dimension_v"
4352 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
4353 -- create "matrix_a" with absolute number of votes in pairwise
4354 -- comparison:
4355 "matrix_a" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
4356 "i" := 1;
4357 "j" := 2;
4358 FOR "battle_row" IN
4359 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
4360 ORDER BY
4361 "winning_initiative_id" NULLS FIRST,
4362 "losing_initiative_id" NULLS FIRST
4363 LOOP
4364 "matrix_a"["i"]["j"] := "battle_row"."count";
4365 IF "j" = "dimension_v" THEN
4366 "i" := "i" + 1;
4367 "j" := 1;
4368 ELSE
4369 "j" := "j" + 1;
4370 IF "j" = "i" THEN
4371 "j" := "j" + 1;
4372 END IF;
4373 END IF;
4374 END LOOP;
4375 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
4376 RAISE EXCEPTION 'Wrong battle count (should not happen)';
4377 END IF;
4378 -- store direct defeat strengths in "matrix_d" using "defeat_strength"
4379 -- and "secondary_link_strength" functions:
4380 "matrix_d" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
4381 "i" := 1;
4382 LOOP
4383 "j" := 1;
4384 LOOP
4385 IF "i" != "j" THEN
4386 "matrix_d"["i"]["j"] := (
4387 "defeat_strength"(
4388 "matrix_a"["i"]["j"],
4389 "matrix_a"["j"]["i"],
4390 "policy_row"."defeat_strength"
4391 ),
4392 "secondary_link_strength"(
4393 "i",
4394 "j",
4395 "policy_row"."tie_breaking"
4396 )
4397 )::"link_strength";
4398 END IF;
4399 EXIT WHEN "j" = "dimension_v";
4400 "j" := "j" + 1;
4401 END LOOP;
4402 EXIT WHEN "i" = "dimension_v";
4403 "i" := "i" + 1;
4404 END LOOP;
4405 -- find best paths:
4406 "matrix_p" := "find_best_paths"("matrix_d");
4407 -- create partial order:
4408 "matrix_b" := array_fill(NULL::BOOLEAN, ARRAY["dimension_v", "dimension_v"]);
4409 "i" := 1;
4410 LOOP
4411 "j" := "i" + 1;
4412 LOOP
4413 IF "i" != "j" THEN
4414 IF "matrix_p"["i"]["j"] > "matrix_p"["j"]["i"] THEN
4415 "matrix_b"["i"]["j"] := TRUE;
4416 "matrix_b"["j"]["i"] := FALSE;
4417 ELSIF "matrix_p"["i"]["j"] < "matrix_p"["j"]["i"] THEN
4418 "matrix_b"["i"]["j"] := FALSE;
4419 "matrix_b"["j"]["i"] := TRUE;
4420 END IF;
4421 END IF;
4422 EXIT WHEN "j" = "dimension_v";
4423 "j" := "j" + 1;
4424 END LOOP;
4425 EXIT WHEN "i" = "dimension_v" - 1;
4426 "i" := "i" + 1;
4427 END LOOP;
4428 -- tie-breaking by forbidding shared weakest links in beat-paths
4429 -- (unless "tie_breaking" is set to 'simple', in which case tie-breaking
4430 -- is performed later by initiative id):
4431 IF "policy_row"."tie_breaking" != 'simple'::"tie_breaking" THEN
4432 "m" := 1;
4433 LOOP
4434 "n" := "m" + 1;
4435 LOOP
4436 -- only process those candidates m and n, which are tied:
4437 IF "matrix_b"["m"]["n"] ISNULL THEN
4438 -- start with beat-paths prior tie-breaking:
4439 "matrix_t" := "matrix_p";
4440 -- start with all links allowed:
4441 "matrix_f" := array_fill(FALSE, ARRAY["dimension_v", "dimension_v"]);
4442 LOOP
4443 -- determine (and forbid) that link that is the weakest link
4444 -- in both the best path from candidate m to candidate n and
4445 -- from candidate n to candidate m:
4446 "i" := 1;
4447 <<forbid_one_link>>
4448 LOOP
4449 "j" := 1;
4450 LOOP
4451 IF "i" != "j" THEN
4452 IF "matrix_d"["i"]["j"] = "matrix_t"["m"]["n"] THEN
4453 "matrix_f"["i"]["j"] := TRUE;
4454 -- exit for performance reasons,
4455 -- as exactly one link will be found:
4456 EXIT forbid_one_link;
4457 END IF;
4458 END IF;
4459 EXIT WHEN "j" = "dimension_v";
4460 "j" := "j" + 1;
4461 END LOOP;
4462 IF "i" = "dimension_v" THEN
4463 RAISE EXCEPTION 'Did not find shared weakest link for tie-breaking (should not happen)';
4464 END IF;
4465 "i" := "i" + 1;
4466 END LOOP;
4467 -- calculate best beat-paths while ignoring forbidden links:
4468 "i" := 1;
4469 LOOP
4470 "j" := 1;
4471 LOOP
4472 IF "i" != "j" THEN
4473 "matrix_t"["i"]["j"] := CASE
4474 WHEN "matrix_f"["i"]["j"]
4475 THEN ((-1::INT8) << 63, 0)::"link_strength" -- worst possible value
4476 ELSE "matrix_d"["i"]["j"] END;
4477 END IF;
4478 EXIT WHEN "j" = "dimension_v";
4479 "j" := "j" + 1;
4480 END LOOP;
4481 EXIT WHEN "i" = "dimension_v";
4482 "i" := "i" + 1;
4483 END LOOP;
4484 "matrix_t" := "find_best_paths"("matrix_t");
4485 -- extend partial order, if tie-breaking was successful:
4486 IF "matrix_t"["m"]["n"] > "matrix_t"["n"]["m"] THEN
4487 "matrix_b"["m"]["n"] := TRUE;
4488 "matrix_b"["n"]["m"] := FALSE;
4489 EXIT;
4490 ELSIF "matrix_t"["m"]["n"] < "matrix_t"["n"]["m"] THEN
4491 "matrix_b"["m"]["n"] := FALSE;
4492 "matrix_b"["n"]["m"] := TRUE;
4493 EXIT;
4494 END IF;
4495 END LOOP;
4496 END IF;
4497 EXIT WHEN "n" = "dimension_v";
4498 "n" := "n" + 1;
4499 END LOOP;
4500 EXIT WHEN "m" = "dimension_v" - 1;
4501 "m" := "m" + 1;
4502 END LOOP;
4503 END IF;
4504 -- store a unique ranking in "rank_ary":
4505 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
4506 "rank_v" := 1;
4507 LOOP
4508 "i" := 1;
4509 <<assign_next_rank>>
4510 LOOP
4511 IF "rank_ary"["i"] ISNULL THEN
4512 "j" := 1;
4513 LOOP
4514 IF
4515 "i" != "j" AND
4516 "rank_ary"["j"] ISNULL AND
4517 ( "matrix_b"["j"]["i"] OR
4518 -- tie-breaking by "id"
4519 ( "matrix_b"["j"]["i"] ISNULL AND
4520 "j" < "i" ) )
4521 THEN
4522 -- someone else is better
4523 EXIT;
4524 END IF;
4525 IF "j" = "dimension_v" THEN
4526 -- noone is better
4527 "rank_ary"["i"] := "rank_v";
4528 EXIT assign_next_rank;
4529 END IF;
4530 "j" := "j" + 1;
4531 END LOOP;
4532 END IF;
4533 "i" := "i" + 1;
4534 IF "i" > "dimension_v" THEN
4535 RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
4536 END IF;
4537 END LOOP;
4538 EXIT WHEN "rank_v" = "dimension_v";
4539 "rank_v" := "rank_v" + 1;
4540 END LOOP;
4541 -- write preliminary results:
4542 "i" := 2; -- omit status quo with "i" = 1
4543 FOR "initiative_id_v" IN
4544 SELECT "id" FROM "initiative"
4545 WHERE "issue_id" = "issue_id_p" AND "admitted"
4546 ORDER BY "id"
4547 LOOP
4548 UPDATE "initiative" SET
4549 "direct_majority" =
4550 CASE WHEN "policy_row"."direct_majority_strict" THEN
4551 "positive_votes" * "policy_row"."direct_majority_den" >
4552 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
4553 ELSE
4554 "positive_votes" * "policy_row"."direct_majority_den" >=
4555 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
4556 END
4557 AND "positive_votes" >= "policy_row"."direct_majority_positive"
4558 AND "issue_row"."voter_count"-"negative_votes" >=
4559 "policy_row"."direct_majority_non_negative",
4560 "indirect_majority" =
4561 CASE WHEN "policy_row"."indirect_majority_strict" THEN
4562 "positive_votes" * "policy_row"."indirect_majority_den" >
4563 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4564 ELSE
4565 "positive_votes" * "policy_row"."indirect_majority_den" >=
4566 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4567 END
4568 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
4569 AND "issue_row"."voter_count"-"negative_votes" >=
4570 "policy_row"."indirect_majority_non_negative",
4571 "schulze_rank" = "rank_ary"["i"],
4572 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
4573 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"[1],
4574 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"[1],
4575 "reverse_beat_path" = CASE WHEN "policy_row"."defeat_strength" = 'simple'::"defeat_strength"
4576 THEN NULL
4577 ELSE "matrix_p"[1]["i"]."primary" >= 0 END,
4578 "eligible" = FALSE,
4579 "winner" = FALSE,
4580 "rank" = NULL -- NOTE: in cases of manual reset of issue state
4581 WHERE "id" = "initiative_id_v";
4582 "i" := "i" + 1;
4583 END LOOP;
4584 IF "i" != "dimension_v" + 1 THEN
4585 RAISE EXCEPTION 'Wrong winner count (should not happen)';
4586 END IF;
4587 -- take indirect majorities into account:
4588 LOOP
4589 UPDATE "initiative" SET "indirect_majority" = TRUE
4590 FROM (
4591 SELECT "new_initiative"."id" AS "initiative_id"
4592 FROM "initiative" "old_initiative"
4593 JOIN "initiative" "new_initiative"
4594 ON "new_initiative"."issue_id" = "issue_id_p"
4595 AND "new_initiative"."indirect_majority" = FALSE
4596 JOIN "battle" "battle_win"
4597 ON "battle_win"."issue_id" = "issue_id_p"
4598 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
4599 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
4600 JOIN "battle" "battle_lose"
4601 ON "battle_lose"."issue_id" = "issue_id_p"
4602 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
4603 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
4604 WHERE "old_initiative"."issue_id" = "issue_id_p"
4605 AND "old_initiative"."indirect_majority" = TRUE
4606 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
4607 "battle_win"."count" * "policy_row"."indirect_majority_den" >
4608 "policy_row"."indirect_majority_num" *
4609 ("battle_win"."count"+"battle_lose"."count")
4610 ELSE
4611 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
4612 "policy_row"."indirect_majority_num" *
4613 ("battle_win"."count"+"battle_lose"."count")
4614 END
4615 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
4616 AND "issue_row"."voter_count"-"battle_lose"."count" >=
4617 "policy_row"."indirect_majority_non_negative"
4618 ) AS "subquery"
4619 WHERE "id" = "subquery"."initiative_id";
4620 EXIT WHEN NOT FOUND;
4621 END LOOP;
4622 -- set "multistage_majority" for remaining matching initiatives:
4623 UPDATE "initiative" SET "multistage_majority" = TRUE
4624 FROM (
4625 SELECT "losing_initiative"."id" AS "initiative_id"
4626 FROM "initiative" "losing_initiative"
4627 JOIN "initiative" "winning_initiative"
4628 ON "winning_initiative"."issue_id" = "issue_id_p"
4629 AND "winning_initiative"."admitted"
4630 JOIN "battle" "battle_win"
4631 ON "battle_win"."issue_id" = "issue_id_p"
4632 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
4633 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
4634 JOIN "battle" "battle_lose"
4635 ON "battle_lose"."issue_id" = "issue_id_p"
4636 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
4637 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
4638 WHERE "losing_initiative"."issue_id" = "issue_id_p"
4639 AND "losing_initiative"."admitted"
4640 AND "winning_initiative"."schulze_rank" <
4641 "losing_initiative"."schulze_rank"
4642 AND "battle_win"."count" > "battle_lose"."count"
4643 AND (
4644 "battle_win"."count" > "winning_initiative"."positive_votes" OR
4645 "battle_lose"."count" < "losing_initiative"."negative_votes" )
4646 ) AS "subquery"
4647 WHERE "id" = "subquery"."initiative_id";
4648 -- mark eligible initiatives:
4649 UPDATE "initiative" SET "eligible" = TRUE
4650 WHERE "issue_id" = "issue_id_p"
4651 AND "initiative"."direct_majority"
4652 AND "initiative"."indirect_majority"
4653 AND "initiative"."better_than_status_quo"
4654 AND (
4655 "policy_row"."no_multistage_majority" = FALSE OR
4656 "initiative"."multistage_majority" = FALSE )
4657 AND (
4658 "policy_row"."no_reverse_beat_path" = FALSE OR
4659 coalesce("initiative"."reverse_beat_path", FALSE) = FALSE );
4660 -- mark final winner:
4661 UPDATE "initiative" SET "winner" = TRUE
4662 FROM (
4663 SELECT "id" AS "initiative_id"
4664 FROM "initiative"
4665 WHERE "issue_id" = "issue_id_p" AND "eligible"
4666 ORDER BY
4667 "schulze_rank",
4668 "id"
4669 LIMIT 1
4670 ) AS "subquery"
4671 WHERE "id" = "subquery"."initiative_id";
4672 -- write (final) ranks:
4673 "rank_v" := 1;
4674 FOR "initiative_id_v" IN
4675 SELECT "id"
4676 FROM "initiative"
4677 WHERE "issue_id" = "issue_id_p" AND "admitted"
4678 ORDER BY
4679 "winner" DESC,
4680 "eligible" DESC,
4681 "schulze_rank",
4682 "id"
4683 LOOP
4684 UPDATE "initiative" SET "rank" = "rank_v"
4685 WHERE "id" = "initiative_id_v";
4686 "rank_v" := "rank_v" + 1;
4687 END LOOP;
4688 -- set schulze rank of status quo and mark issue as finished:
4689 UPDATE "issue" SET
4690 "status_quo_schulze_rank" = "rank_ary"[1],
4691 "state" =
4692 CASE WHEN EXISTS (
4693 SELECT NULL FROM "initiative"
4694 WHERE "issue_id" = "issue_id_p" AND "winner"
4695 ) THEN
4696 'finished_with_winner'::"issue_state"
4697 ELSE
4698 'finished_without_winner'::"issue_state"
4699 END,
4700 "closed" = "phase_finished",
4701 "phase_finished" = NULL
4702 WHERE "id" = "issue_id_p";
4703 RETURN;
4704 END;
4705 $$;
4707 COMMENT ON FUNCTION "calculate_ranks"
4708 ( "issue"."id"%TYPE )
4709 IS 'Determine ranking (Votes have to be counted first)';
4713 -----------------------------
4714 -- Automatic state changes --
4715 -----------------------------
4718 CREATE FUNCTION "issue_admission"()
4719 RETURNS BOOLEAN
4720 LANGUAGE 'plpgsql' VOLATILE AS $$
4721 DECLARE
4722 "issue_id_v" "issue"."id"%TYPE;
4723 BEGIN
4724 PERFORM "dont_require_transaction_isolation"();
4725 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
4726 SELECT "id" INTO "issue_id_v" FROM "issue_for_admission" LIMIT 1;
4727 IF "issue_id_v" ISNULL THEN RETURN FALSE; END IF;
4728 UPDATE "issue" SET
4729 "admission_snapshot_id" = "latest_snapshot_id",
4730 "state" = 'discussion',
4731 "accepted" = now(),
4732 "phase_finished" = NULL
4733 WHERE "id" = "issue_id_v";
4734 RETURN TRUE;
4735 END;
4736 $$;
4738 COMMENT ON FUNCTION "issue_admission"() IS 'Checks if an issue can be admitted for further discussion; returns TRUE on success in which case the function must be called again until it returns FALSE';
4741 CREATE TYPE "check_issue_persistence" AS (
4742 "state" "issue_state",
4743 "phase_finished" BOOLEAN,
4744 "issue_revoked" BOOLEAN,
4745 "snapshot_created" BOOLEAN,
4746 "harmonic_weights_set" BOOLEAN,
4747 "closed_voting" BOOLEAN );
4749 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';
4752 CREATE FUNCTION "check_issue"
4753 ( "issue_id_p" "issue"."id"%TYPE,
4754 "persist" "check_issue_persistence" )
4755 RETURNS "check_issue_persistence"
4756 LANGUAGE 'plpgsql' VOLATILE AS $$
4757 DECLARE
4758 "issue_row" "issue"%ROWTYPE;
4759 "last_calculated_v" "snapshot"."calculated"%TYPE;
4760 "policy_row" "policy"%ROWTYPE;
4761 "initiative_row" "initiative"%ROWTYPE;
4762 "state_v" "issue_state";
4763 BEGIN
4764 PERFORM "require_transaction_isolation"();
4765 IF "persist" ISNULL THEN
4766 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4767 FOR UPDATE;
4768 SELECT "calculated" INTO "last_calculated_v"
4769 FROM "snapshot" JOIN "snapshot_issue"
4770 ON "snapshot"."id" = "snapshot_issue"."snapshot_id"
4771 WHERE "snapshot_issue"."issue_id" = "issue_id_p";
4772 IF "issue_row"."closed" NOTNULL THEN
4773 RETURN NULL;
4774 END IF;
4775 "persist"."state" := "issue_row"."state";
4776 IF
4777 ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=
4778 "issue_row"."created" + "issue_row"."max_admission_time" ) OR
4779 ( "issue_row"."state" = 'discussion' AND now() >=
4780 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
4781 ( "issue_row"."state" = 'verification' AND now() >=
4782 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
4783 ( "issue_row"."state" = 'voting' AND now() >=
4784 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
4785 THEN
4786 "persist"."phase_finished" := TRUE;
4787 ELSE
4788 "persist"."phase_finished" := FALSE;
4789 END IF;
4790 IF
4791 NOT EXISTS (
4792 -- all initiatives are revoked
4793 SELECT NULL FROM "initiative"
4794 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4795 ) AND (
4796 -- and issue has not been accepted yet
4797 "persist"."state" = 'admission' OR
4798 -- or verification time has elapsed
4799 ( "persist"."state" = 'verification' AND
4800 "persist"."phase_finished" ) OR
4801 -- or no initiatives have been revoked lately
4802 NOT EXISTS (
4803 SELECT NULL FROM "initiative"
4804 WHERE "issue_id" = "issue_id_p"
4805 AND now() < "revoked" + "issue_row"."verification_time"
4806 )
4807 )
4808 THEN
4809 "persist"."issue_revoked" := TRUE;
4810 ELSE
4811 "persist"."issue_revoked" := FALSE;
4812 END IF;
4813 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
4814 UPDATE "issue" SET "phase_finished" = now()
4815 WHERE "id" = "issue_row"."id";
4816 RETURN "persist";
4817 ELSIF
4818 "persist"."state" IN ('admission', 'discussion', 'verification')
4819 THEN
4820 RETURN "persist";
4821 ELSE
4822 RETURN NULL;
4823 END IF;
4824 END IF;
4825 IF
4826 "persist"."state" IN ('admission', 'discussion', 'verification') AND
4827 coalesce("persist"."snapshot_created", FALSE) = FALSE
4828 THEN
4829 IF "persist"."state" != 'admission' THEN
4830 PERFORM "take_snapshot"("issue_id_p");
4831 PERFORM "finish_snapshot"("issue_id_p");
4832 END IF;
4833 "persist"."snapshot_created" = TRUE;
4834 IF "persist"."phase_finished" THEN
4835 IF "persist"."state" = 'admission' THEN
4836 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id";
4837 ELSIF "persist"."state" = 'discussion' THEN
4838 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id";
4839 ELSIF "persist"."state" = 'verification' THEN
4840 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id";
4841 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4842 SELECT * INTO "policy_row" FROM "policy"
4843 WHERE "id" = "issue_row"."policy_id";
4844 FOR "initiative_row" IN
4845 SELECT * FROM "initiative"
4846 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4847 FOR UPDATE
4848 LOOP
4849 IF
4850 "initiative_row"."polling" OR (
4851 "initiative_row"."satisfied_supporter_count" > 0 AND
4852 "initiative_row"."satisfied_supporter_count" *
4853 "policy_row"."initiative_quorum_den" >=
4854 "issue_row"."population" * "policy_row"."initiative_quorum_num"
4855 )
4856 THEN
4857 UPDATE "initiative" SET "admitted" = TRUE
4858 WHERE "id" = "initiative_row"."id";
4859 ELSE
4860 UPDATE "initiative" SET "admitted" = FALSE
4861 WHERE "id" = "initiative_row"."id";
4862 END IF;
4863 END LOOP;
4864 END IF;
4865 END IF;
4866 RETURN "persist";
4867 END IF;
4868 IF
4869 "persist"."state" IN ('admission', 'discussion', 'verification') AND
4870 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
4871 THEN
4872 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
4873 "persist"."harmonic_weights_set" = TRUE;
4874 IF
4875 "persist"."phase_finished" OR
4876 "persist"."issue_revoked" OR
4877 "persist"."state" = 'admission'
4878 THEN
4879 RETURN "persist";
4880 ELSE
4881 RETURN NULL;
4882 END IF;
4883 END IF;
4884 IF "persist"."issue_revoked" THEN
4885 IF "persist"."state" = 'admission' THEN
4886 "state_v" := 'canceled_revoked_before_accepted';
4887 ELSIF "persist"."state" = 'discussion' THEN
4888 "state_v" := 'canceled_after_revocation_during_discussion';
4889 ELSIF "persist"."state" = 'verification' THEN
4890 "state_v" := 'canceled_after_revocation_during_verification';
4891 END IF;
4892 UPDATE "issue" SET
4893 "state" = "state_v",
4894 "closed" = "phase_finished",
4895 "phase_finished" = NULL
4896 WHERE "id" = "issue_id_p";
4897 RETURN NULL;
4898 END IF;
4899 IF "persist"."state" = 'admission' THEN
4900 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4901 FOR UPDATE;
4902 IF "issue_row"."phase_finished" NOTNULL THEN
4903 UPDATE "issue" SET
4904 "state" = 'canceled_issue_not_accepted',
4905 "closed" = "phase_finished",
4906 "phase_finished" = NULL
4907 WHERE "id" = "issue_id_p";
4908 END IF;
4909 RETURN NULL;
4910 END IF;
4911 IF "persist"."phase_finished" THEN
4912 IF "persist"."state" = 'discussion' THEN
4913 UPDATE "issue" SET
4914 "state" = 'verification',
4915 "half_frozen" = "phase_finished",
4916 "phase_finished" = NULL
4917 WHERE "id" = "issue_id_p";
4918 RETURN NULL;
4919 END IF;
4920 IF "persist"."state" = 'verification' THEN
4921 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4922 FOR UPDATE;
4923 SELECT * INTO "policy_row" FROM "policy"
4924 WHERE "id" = "issue_row"."policy_id";
4925 IF EXISTS (
4926 SELECT NULL FROM "initiative"
4927 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
4928 ) THEN
4929 UPDATE "issue" SET
4930 "state" = 'voting',
4931 "fully_frozen" = "phase_finished",
4932 "phase_finished" = NULL
4933 WHERE "id" = "issue_id_p";
4934 ELSE
4935 UPDATE "issue" SET
4936 "state" = 'canceled_no_initiative_admitted',
4937 "fully_frozen" = "phase_finished",
4938 "closed" = "phase_finished",
4939 "phase_finished" = NULL
4940 WHERE "id" = "issue_id_p";
4941 -- NOTE: The following DELETE statements have effect only when
4942 -- issue state has been manipulated
4943 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
4944 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
4945 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
4946 END IF;
4947 RETURN NULL;
4948 END IF;
4949 IF "persist"."state" = 'voting' THEN
4950 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
4951 PERFORM "close_voting"("issue_id_p");
4952 "persist"."closed_voting" = TRUE;
4953 RETURN "persist";
4954 END IF;
4955 PERFORM "calculate_ranks"("issue_id_p");
4956 RETURN NULL;
4957 END IF;
4958 END IF;
4959 RAISE WARNING 'should not happen';
4960 RETURN NULL;
4961 END;
4962 $$;
4964 COMMENT ON FUNCTION "check_issue"
4965 ( "issue"."id"%TYPE,
4966 "check_issue_persistence" )
4967 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")';
4970 CREATE FUNCTION "check_everything"()
4971 RETURNS VOID
4972 LANGUAGE 'plpgsql' VOLATILE AS $$
4973 DECLARE
4974 "snapshot_id_v" "snapshot"."id"%TYPE;
4975 "issue_id_v" "issue"."id"%TYPE;
4976 "persist_v" "check_issue_persistence";
4977 BEGIN
4978 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
4979 DELETE FROM "expired_session";
4980 PERFORM "check_activity"();
4981 PERFORM "calculate_member_counts"();
4982 SELECT "take_snapshot"(NULL) INTO "snapshot_id_v";
4983 PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"
4984 WHERE "snapshot_id" = "snapshot_id_v";
4985 LOOP
4986 EXIT WHEN "issue_admission"() = FALSE;
4987 END LOOP;
4988 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
4989 "persist_v" := NULL;
4990 LOOP
4991 "persist_v" := "check_issue"("issue_id_v", "persist_v");
4992 EXIT WHEN "persist_v" ISNULL;
4993 END LOOP;
4994 END LOOP;
4995 RETURN;
4996 END;
4997 $$;
4999 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.';
5003 ----------------------
5004 -- Deletion of data --
5005 ----------------------
5008 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
5009 RETURNS VOID
5010 LANGUAGE 'plpgsql' VOLATILE AS $$
5011 BEGIN
5012 IF EXISTS (
5013 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
5014 ) THEN
5015 -- override protection triggers:
5016 INSERT INTO "temporary_transaction_data" ("key", "value")
5017 VALUES ('override_protection_triggers', TRUE::TEXT);
5018 -- clean data:
5019 DELETE FROM "delegating_voter"
5020 WHERE "issue_id" = "issue_id_p";
5021 DELETE FROM "direct_voter"
5022 WHERE "issue_id" = "issue_id_p";
5023 DELETE FROM "delegating_interest_snapshot"
5024 WHERE "issue_id" = "issue_id_p";
5025 DELETE FROM "direct_interest_snapshot"
5026 WHERE "issue_id" = "issue_id_p";
5027 DELETE FROM "delegating_population_snapshot"
5028 WHERE "issue_id" = "issue_id_p";
5029 DELETE FROM "direct_population_snapshot"
5030 WHERE "issue_id" = "issue_id_p";
5031 DELETE FROM "non_voter"
5032 WHERE "issue_id" = "issue_id_p";
5033 DELETE FROM "delegation"
5034 WHERE "issue_id" = "issue_id_p";
5035 DELETE FROM "supporter"
5036 USING "initiative" -- NOTE: due to missing index on issue_id
5037 WHERE "initiative"."issue_id" = "issue_id_p"
5038 AND "supporter"."initiative_id" = "initiative_id";
5039 -- mark issue as cleaned:
5040 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
5041 -- finish overriding protection triggers (avoids garbage):
5042 DELETE FROM "temporary_transaction_data"
5043 WHERE "key" = 'override_protection_triggers';
5044 END IF;
5045 RETURN;
5046 END;
5047 $$;
5049 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
5052 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
5053 RETURNS VOID
5054 LANGUAGE 'plpgsql' VOLATILE AS $$
5055 BEGIN
5056 UPDATE "member" SET
5057 "last_login" = NULL,
5058 "last_delegation_check" = NULL,
5059 "login" = NULL,
5060 "password" = NULL,
5061 "authority" = NULL,
5062 "authority_uid" = NULL,
5063 "authority_login" = NULL,
5064 "locked" = TRUE,
5065 "active" = FALSE,
5066 "notify_email" = NULL,
5067 "notify_email_unconfirmed" = NULL,
5068 "notify_email_secret" = NULL,
5069 "notify_email_secret_expiry" = NULL,
5070 "notify_email_lock_expiry" = NULL,
5071 "disable_notifications" = TRUE,
5072 "notification_counter" = DEFAULT,
5073 "notification_sample_size" = 0,
5074 "notification_dow" = NULL,
5075 "notification_hour" = NULL,
5076 "login_recovery_expiry" = NULL,
5077 "password_reset_secret" = NULL,
5078 "password_reset_secret_expiry" = NULL,
5079 "organizational_unit" = NULL,
5080 "internal_posts" = NULL,
5081 "realname" = NULL,
5082 "birthday" = NULL,
5083 "address" = NULL,
5084 "email" = NULL,
5085 "xmpp_address" = NULL,
5086 "website" = NULL,
5087 "phone" = NULL,
5088 "mobile_phone" = NULL,
5089 "profession" = NULL,
5090 "external_memberships" = NULL,
5091 "external_posts" = NULL,
5092 "statement" = NULL
5093 WHERE "id" = "member_id_p";
5094 -- "text_search_data" is updated by triggers
5095 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
5096 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
5097 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
5098 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
5099 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
5100 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
5101 DELETE FROM "session" WHERE "member_id" = "member_id_p";
5102 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
5103 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
5104 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
5105 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
5106 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
5107 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
5108 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
5109 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
5110 DELETE FROM "direct_voter" USING "issue"
5111 WHERE "direct_voter"."issue_id" = "issue"."id"
5112 AND "issue"."closed" ISNULL
5113 AND "member_id" = "member_id_p";
5114 RETURN;
5115 END;
5116 $$;
5118 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)';
5121 CREATE FUNCTION "delete_private_data"()
5122 RETURNS VOID
5123 LANGUAGE 'plpgsql' VOLATILE AS $$
5124 BEGIN
5125 DELETE FROM "temporary_transaction_data";
5126 DELETE FROM "member" WHERE "activated" ISNULL;
5127 UPDATE "member" SET
5128 "invite_code" = NULL,
5129 "invite_code_expiry" = NULL,
5130 "admin_comment" = NULL,
5131 "last_login" = NULL,
5132 "last_delegation_check" = NULL,
5133 "login" = NULL,
5134 "password" = NULL,
5135 "authority" = NULL,
5136 "authority_uid" = NULL,
5137 "authority_login" = NULL,
5138 "lang" = NULL,
5139 "notify_email" = NULL,
5140 "notify_email_unconfirmed" = NULL,
5141 "notify_email_secret" = NULL,
5142 "notify_email_secret_expiry" = NULL,
5143 "notify_email_lock_expiry" = NULL,
5144 "disable_notifications" = TRUE,
5145 "notification_counter" = DEFAULT,
5146 "notification_sample_size" = 0,
5147 "notification_dow" = NULL,
5148 "notification_hour" = NULL,
5149 "login_recovery_expiry" = NULL,
5150 "password_reset_secret" = NULL,
5151 "password_reset_secret_expiry" = NULL,
5152 "organizational_unit" = NULL,
5153 "internal_posts" = NULL,
5154 "realname" = NULL,
5155 "birthday" = NULL,
5156 "address" = NULL,
5157 "email" = NULL,
5158 "xmpp_address" = NULL,
5159 "website" = NULL,
5160 "phone" = NULL,
5161 "mobile_phone" = NULL,
5162 "profession" = NULL,
5163 "external_memberships" = NULL,
5164 "external_posts" = NULL,
5165 "formatting_engine" = NULL,
5166 "statement" = NULL;
5167 -- "text_search_data" is updated by triggers
5168 DELETE FROM "setting";
5169 DELETE FROM "setting_map";
5170 DELETE FROM "member_relation_setting";
5171 DELETE FROM "member_image";
5172 DELETE FROM "contact";
5173 DELETE FROM "ignored_member";
5174 DELETE FROM "session";
5175 DELETE FROM "area_setting";
5176 DELETE FROM "issue_setting";
5177 DELETE FROM "ignored_initiative";
5178 DELETE FROM "initiative_setting";
5179 DELETE FROM "suggestion_setting";
5180 DELETE FROM "non_voter";
5181 DELETE FROM "direct_voter" USING "issue"
5182 WHERE "direct_voter"."issue_id" = "issue"."id"
5183 AND "issue"."closed" ISNULL;
5184 RETURN;
5185 END;
5186 $$;
5188 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.';
5192 COMMIT;