liquid_feedback_core
view core.sql @ 544:ff2c21f883ce
New tables "member_settings" and "member_useterms"; Make column "profile" of "member_profile" a NOT NULL column (with DEFAULT)
| author | jbe |
|---|---|
| date | Fri Jul 14 21:02:54 2017 +0200 (2017-07-14) |
| parents | 4e0ee8416bbc |
| children | f46ebb677898 |
line source
2 -- NOTE: In PostgreSQL every UNIQUE constraint implies creation of an index
4 BEGIN;
6 CREATE EXTENSION IF NOT EXISTS latlon; -- load pgLatLon extenstion
8 CREATE VIEW "liquid_feedback_version" AS
9 SELECT * FROM (VALUES ('4.0-dev', 4, 0, -1))
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 "snapshot_retention" INTERVAL );
70 CREATE UNIQUE INDEX "system_setting_singleton_idx" ON "system_setting" ((1));
72 COMMENT ON TABLE "system_setting" IS 'This table contains only one row with different settings in each column.';
73 COMMENT ON INDEX "system_setting_singleton_idx" IS 'This index ensures that "system_setting" only contains one row maximum.';
75 COMMENT ON COLUMN "system_setting"."member_ttl" IS 'Time after members get their "active" flag set to FALSE, if they do not show any activity.';
76 COMMENT ON COLUMN "system_setting"."snapshot_retention" IS 'Unreferenced snapshots are retained for the given period of time after creation; set to NULL for infinite retention.';
79 CREATE TABLE "contingent" (
80 PRIMARY KEY ("polling", "time_frame"),
81 "polling" BOOLEAN,
82 "time_frame" INTERVAL,
83 "text_entry_limit" INT4,
84 "initiative_limit" INT4 );
86 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.';
88 COMMENT ON COLUMN "contingent"."polling" IS 'Determines if settings are for creating initiatives and new drafts of initiatives with "polling" flag set';
89 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';
90 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
93 CREATE TABLE "member" (
94 "id" SERIAL4 PRIMARY KEY,
95 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
96 "invite_code" TEXT UNIQUE,
97 "invite_code_expiry" TIMESTAMPTZ,
98 "admin_comment" TEXT,
99 "activated" TIMESTAMPTZ,
100 "last_activity" DATE,
101 "last_login" TIMESTAMPTZ,
102 "last_delegation_check" TIMESTAMPTZ,
103 "login" TEXT UNIQUE,
104 "password" TEXT,
105 "authority" TEXT,
106 "authority_uid" TEXT,
107 "authority_login" TEXT,
108 "locked" BOOLEAN NOT NULL DEFAULT FALSE,
109 "active" BOOLEAN NOT NULL DEFAULT FALSE,
110 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
111 "lang" TEXT,
112 "notify_email" TEXT,
113 "notify_email_unconfirmed" TEXT,
114 "notify_email_secret" TEXT UNIQUE,
115 "notify_email_secret_expiry" TIMESTAMPTZ,
116 "notify_email_lock_expiry" TIMESTAMPTZ,
117 "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE,
118 "notification_counter" INT4 NOT NULL DEFAULT 1,
119 "notification_sample_size" INT4 NOT NULL DEFAULT 3,
120 "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6),
121 "notification_hour" INT4 DEFAULT floor(random() * 24) CHECK ("notification_hour" BETWEEN 0 AND 23),
122 "notification_sent" TIMESTAMP,
123 "login_recovery_expiry" TIMESTAMPTZ,
124 "password_reset_secret" TEXT UNIQUE,
125 "password_reset_secret_expiry" TIMESTAMPTZ,
126 "name" TEXT UNIQUE,
127 "identification" TEXT UNIQUE,
128 "authentication" TEXT,
129 "location" JSONB,
130 "text_search_data" TSVECTOR,
131 CONSTRAINT "active_requires_activated_and_last_activity"
132 CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)),
133 CONSTRAINT "authority_requires_uid_and_vice_versa"
134 CHECK (("authority" NOTNULL) = ("authority_uid" NOTNULL)),
135 CONSTRAINT "authority_uid_unique_per_authority"
136 UNIQUE ("authority", "authority_uid"),
137 CONSTRAINT "authority_login_requires_authority"
138 CHECK ("authority" NOTNULL OR "authority_login" ISNULL),
139 CONSTRAINT "notification_dow_requires_notification_hour"
140 CHECK ("notification_dow" ISNULL OR "notification_hour" NOTNULL),
141 CONSTRAINT "name_not_null_if_activated"
142 CHECK ("activated" ISNULL OR "name" NOTNULL) );
143 CREATE INDEX "member_authority_login_idx" ON "member" ("authority_login");
144 CREATE INDEX "member_active_idx" ON "member" ("active");
145 CREATE INDEX "member_location_idx" ON "member" USING gist ((GeoJSON_to_ecluster("location")));
146 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
147 CREATE TRIGGER "update_text_search_data"
148 BEFORE INSERT OR UPDATE ON "member"
149 FOR EACH ROW EXECUTE PROCEDURE
150 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
151 "name", "identification");
153 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
155 COMMENT ON COLUMN "member"."created" IS 'Creation of member record and/or invite code';
156 COMMENT ON COLUMN "member"."invite_code" IS 'Optional invite code, to allow a member to initialize his/her account the first time';
157 COMMENT ON COLUMN "member"."invite_code_expiry" IS 'Expiry data/time for "invite_code"';
158 COMMENT ON COLUMN "member"."admin_comment" IS 'Hidden comment for administrative purposes';
159 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';
160 COMMENT ON COLUMN "member"."last_activity" IS 'Date of last activity of member; required to be set for "active" members';
161 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';
162 COMMENT ON COLUMN "member"."last_delegation_check" IS 'Timestamp of last delegation check (i.e. confirmation of all unit and area delegations)';
163 COMMENT ON COLUMN "member"."login" IS 'Login name';
164 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
165 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)';
166 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)';
167 COMMENT ON COLUMN "member"."authority_login" IS 'Login name for external accounts (field is not unique!)';
168 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
169 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".';
170 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
171 COMMENT ON COLUMN "member"."lang" IS 'Language code of the preferred language of the member';
172 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
173 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
174 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
175 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
176 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
177 COMMENT ON COLUMN "member"."disable_notifications" IS 'TRUE if member does not want to receive notifications';
178 COMMENT ON COLUMN "member"."notification_counter" IS 'Sequential number of next scheduled notification message (used as a seed for pseudo-random initiative selection algorithm)';
179 COMMENT ON COLUMN "member"."notification_sample_size" IS 'Number of featured initiatives per issue in scheduled notification messages';
180 COMMENT ON COLUMN "member"."notification_dow" IS 'Day of week for scheduled notifications (NULL to receive a daily digest)';
181 COMMENT ON COLUMN "member"."notification_hour" IS 'Time of day when scheduled notifications are sent out';
182 COMMENT ON COLUMN "member"."notification_sent" IS 'Timestamp of last scheduled notification mail that has been sent out';
183 COMMENT ON COLUMN "member"."login_recovery_expiry" IS 'Date/time after which another login recovery attempt is allowed';
184 COMMENT ON COLUMN "member"."password_reset_secret" IS 'Secret string sent via e-mail for password recovery';
185 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';
186 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member, may be NULL if account has not been activated yet';
187 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
188 COMMENT ON COLUMN "member"."authentication" IS 'Information about how this member was authenticated';
189 COMMENT ON COLUMN "member"."location" IS 'Geographic location on earth as GeoJSON object';
192 CREATE TABLE "member_history" ( -- TODO: redundancy with new "event" table
193 "id" SERIAL8 PRIMARY KEY,
194 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
195 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
196 "active" BOOLEAN NOT NULL,
197 "name" TEXT NOT NULL );
198 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
200 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
202 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
203 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
206 CREATE TABLE "member_settings" (
207 "member_id" INT4 PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
208 "settings" JSONB NOT NULL CHECK (jsonb_typeof("settings") = 'object') );
210 COMMENT ON TABLE "member_settings" IS 'Stores a JSON document for each member containing optional (additional) settings for the respective member';
213 CREATE TABLE "member_useterms" (
214 "member_id" INT4 PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
215 "accepted" TIMESTAMPTZ NOT NULL,
216 "contract_identifier" TEXT NOT NULL );
218 COMMENT ON TABLE "member_useterms" IS 'Keeps record of accepted terms of use; may contain multiple rows per member';
220 COMMENT ON COLUMN "member_useterms"."accepted" IS 'Point in time when user accepted the terms of use';
221 COMMENT ON COLUMN "member_useterms"."contract_identifier" IS 'String identifier to denote the accepted terms of use, including their version or revision';
224 CREATE TABLE "member_profile" (
225 "member_id" INT4 PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
226 "formatting_engine" TEXT,
227 "statement" TEXT,
228 "profile" JSONB NOT NULL DEFAULT '{}' CHECK (jsonb_typeof("profile") = 'object'),
229 "profile_text_data" TEXT,
230 "text_search_data" TSVECTOR );
231 CREATE INDEX "member_profile_text_search_data_idx" ON "member_profile" USING gin ("text_search_data");
232 CREATE TRIGGER "update_text_search_data"
233 BEFORE INSERT OR UPDATE ON "member_profile"
234 FOR EACH ROW EXECUTE PROCEDURE
235 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
236 'statement', 'profile_text_data');
238 COMMENT ON COLUMN "member_profile"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member_profile"."statement"';
239 COMMENT ON COLUMN "member_profile"."statement" IS 'Freely chosen text of the member for his/her profile';
240 COMMENT ON COLUMN "member_profile"."profile" IS 'Additional profile data as JSON document';
241 COMMENT ON COLUMN "member_profile"."profile_text_data" IS 'Text data from "profile" field for full text search';
244 CREATE TABLE "rendered_member_statement" (
245 PRIMARY KEY ("member_id", "format"),
246 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
247 "format" TEXT,
248 "content" TEXT NOT NULL );
250 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)';
253 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
255 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
258 CREATE TABLE "member_image" (
259 PRIMARY KEY ("member_id", "image_type", "scaled"),
260 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
261 "image_type" "member_image_type",
262 "scaled" BOOLEAN,
263 "content_type" TEXT,
264 "data" BYTEA NOT NULL );
266 COMMENT ON TABLE "member_image" IS 'Images of members';
268 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
271 CREATE TABLE "member_count" (
272 "calculated" TIMESTAMPTZ NOT NULL DEFAULT now(),
273 "total_count" INT4 NOT NULL );
275 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';
277 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
278 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
281 CREATE TABLE "contact" (
282 PRIMARY KEY ("member_id", "other_member_id"),
283 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
284 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
285 "public" BOOLEAN NOT NULL DEFAULT FALSE,
286 CONSTRAINT "cant_save_yourself_as_contact"
287 CHECK ("member_id" != "other_member_id") );
288 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
290 COMMENT ON TABLE "contact" IS 'Contact lists';
292 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
293 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
294 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
297 CREATE TABLE "ignored_member" (
298 PRIMARY KEY ("member_id", "other_member_id"),
299 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
300 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
301 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
303 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
305 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';
306 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
309 CREATE TABLE "session" (
310 UNIQUE ("member_id", "id"), -- index needed for foreign-key on table "token"
311 "id" SERIAL8 PRIMARY KEY,
312 "ident" TEXT NOT NULL UNIQUE,
313 "additional_secret" TEXT,
314 "logout_token" TEXT,
315 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
316 "member_id" INT4 REFERENCES "member" ("id") ON DELETE SET NULL,
317 "authority" TEXT,
318 "authority_uid" TEXT,
319 "authority_login" TEXT,
320 "needs_delegation_check" BOOLEAN NOT NULL DEFAULT FALSE,
321 "lang" TEXT );
322 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
324 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
326 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
327 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
328 COMMENT ON COLUMN "session"."logout_token" IS 'Optional token to authorize logout through external component';
329 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
330 COMMENT ON COLUMN "session"."authority" IS 'Temporary store for "member"."authority" during member account creation';
331 COMMENT ON COLUMN "session"."authority_uid" IS 'Temporary store for "member"."authority_uid" during member account creation';
332 COMMENT ON COLUMN "session"."authority_login" IS 'Temporary store for "member"."authority_login" during member account creation';
333 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';
334 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
337 CREATE TYPE "authflow" AS ENUM ('code', 'token');
339 COMMENT ON TYPE "authflow" IS 'OAuth 2.0 flows: ''code'' = Authorization Code flow, ''token'' = Implicit flow';
342 CREATE TABLE "system_application" (
343 "id" SERIAL4 PRIMARY KEY,
344 "name" TEXT NOT NULL,
345 "client_id" TEXT NOT NULL UNIQUE,
346 "default_redirect_uri" TEXT NOT NULL,
347 "cert_common_name" TEXT,
348 "client_cred_scope" TEXT,
349 "flow" "authflow",
350 "automatic_scope" TEXT,
351 "permitted_scope" TEXT,
352 "forbidden_scope" TEXT );
354 COMMENT ON TABLE "system_application" IS 'OAuth 2.0 clients that are registered by the system administrator';
356 COMMENT ON COLUMN "system_application"."name" IS 'Human readable name of application';
357 COMMENT ON COLUMN "system_application"."client_id" IS 'OAuth 2.0 "client_id"';
358 COMMENT ON COLUMN "system_application"."cert_common_name" IS 'Value for CN field of TLS client certificate';
359 COMMENT ON COLUMN "system_application"."client_cred_scope" IS 'Space-separated list of scopes; If set, Client Credentials Grant is allowed; value determines scope';
360 COMMENT ON COLUMN "system_application"."flow" IS 'If set to ''code'' or ''token'', then Authorization Code or Implicit flow is allowed respectively';
361 COMMENT ON COLUMN "system_application"."automatic_scope" IS 'Space-separated list of scopes; Automatically granted scope for Authorization Code or Implicit flow';
362 COMMENT ON COLUMN "system_application"."permitted_scope" IS 'Space-separated list of scopes; If set, scope that members may grant to the application is limited to the given value';
363 COMMENT ON COLUMN "system_application"."forbidden_scope" IS 'Space-separated list of scopes that may not be granted to the application by a member';
366 CREATE TABLE "system_application_redirect_uri" (
367 PRIMARY KEY ("system_application_id", "redirect_uri"),
368 "system_application_id" INT4 REFERENCES "system_application" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
369 "redirect_uri" TEXT );
371 COMMENT ON TABLE "system_application_redirect_uri" IS 'Additional OAuth 2.0 redirection endpoints, which may be selected through the "redirect_uri" GET parameter';
374 CREATE TABLE "dynamic_application_scope" (
375 PRIMARY KEY ("redirect_uri", "flow", "scope"),
376 "redirect_uri" TEXT,
377 "flow" TEXT,
378 "scope" TEXT,
379 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours' );
380 CREATE INDEX "dynamic_application_scope_redirect_uri_scope_idx" ON "dynamic_application_scope" ("redirect_uri", "flow", "scope");
381 CREATE INDEX "dynamic_application_scope_expiry_idx" ON "dynamic_application_scope" ("expiry");
383 COMMENT ON TABLE "dynamic_application_scope" IS 'Dynamic OAuth 2.0 client registration data';
385 COMMENT ON COLUMN "dynamic_application_scope"."redirect_uri" IS 'Redirection endpoint for which the registration has been done';
386 COMMENT ON COLUMN "dynamic_application_scope"."flow" IS 'OAuth 2.0 flow for which the registration has been done (see also "system_application"."flow")';
387 COMMENT ON COLUMN "dynamic_application_scope"."scope" IS 'Single scope without space characters (use multiple rows for more scopes)';
388 COMMENT ON COLUMN "dynamic_application_scope"."expiry" IS 'Expiry unless renewed';
391 CREATE TABLE "member_application" (
392 "id" SERIAL4 PRIMARY KEY,
393 UNIQUE ("system_application_id", "member_id"),
394 UNIQUE ("domain", "member_id"),
395 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
396 "system_application_id" INT4 REFERENCES "system_application" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
397 "domain" TEXT,
398 "session_id" INT8,
399 FOREIGN KEY ("member_id", "session_id") REFERENCES "session" ("member_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
400 "scope" TEXT NOT NULL,
401 CONSTRAINT "system_application_or_domain_but_not_both" CHECK (
402 ("system_application_id" NOTNULL AND "domain" ISNULL) OR
403 ("system_application_id" ISNULL AND "domain" NOTNULL) ) );
404 CREATE INDEX "member_application_member_id_idx" ON "member_application" ("member_id");
406 COMMENT ON TABLE "member_application" IS 'Application authorized by a member';
408 COMMENT ON COLUMN "member_application"."system_application_id" IS 'If set, then application is a system application';
409 COMMENT ON COLUMN "member_application"."domain" IS 'If set, then application is a dynamically registered OAuth 2.0 client; value is set to client''s domain';
410 COMMENT ON COLUMN "member_application"."session_id" IS 'If set, registration ends with session';
411 COMMENT ON COLUMN "member_application"."scope" IS 'Granted scope as space-separated list of strings';
414 CREATE TYPE "token_type" AS ENUM ('authorization', 'refresh', 'access');
416 COMMENT ON TYPE "token_type" IS 'Types for entries in "token" table';
419 CREATE TABLE "token" (
420 "id" SERIAL8 PRIMARY KEY,
421 "token" TEXT NOT NULL UNIQUE,
422 "token_type" "token_type" NOT NULL,
423 "authorization_token_id" INT8 REFERENCES "token" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
424 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
425 "system_application_id" INT4 REFERENCES "system_application" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
426 "domain" TEXT,
427 FOREIGN KEY ("member_id", "domain") REFERENCES "member_application" ("member_id", "domain") ON DELETE CASCADE ON UPDATE CASCADE,
428 "session_id" INT8,
429 FOREIGN KEY ("member_id", "session_id") REFERENCES "session" ("member_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE, -- NOTE: deletion through "detach_token_from_session" trigger on table "session"
430 "redirect_uri" TEXT,
431 "redirect_uri_explicit" BOOLEAN,
432 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
433 "expiry" TIMESTAMPTZ DEFAULT now() + '1 hour',
434 "used" BOOLEAN NOT NULL DEFAULT FALSE,
435 "scope" TEXT NOT NULL,
436 CONSTRAINT "access_token_needs_expiry"
437 CHECK ("token_type" != 'access'::"token_type" OR "expiry" NOTNULL),
438 CONSTRAINT "authorization_token_needs_redirect_uri"
439 CHECK ("token_type" != 'authorization'::"token_type" OR ("redirect_uri" NOTNULL AND "redirect_uri_explicit" NOTNULL) ) );
440 CREATE INDEX "token_member_id_idx" ON "token" ("member_id");
441 CREATE INDEX "token_authorization_token_id_idx" ON "token" ("authorization_token_id");
442 CREATE INDEX "token_expiry_idx" ON "token" ("expiry");
444 COMMENT ON TABLE "token" IS 'Issued OAuth 2.0 authorization codes and access/refresh tokens';
446 COMMENT ON COLUMN "token"."token" IS 'String secret (the actual token)';
447 COMMENT ON COLUMN "token"."authorization_token_id" IS 'Reference to authorization token if tokens were originally created by Authorization Code flow (allows deletion if code is used twice)';
448 COMMENT ON COLUMN "token"."system_application_id" IS 'If set, then application is a system application';
449 COMMENT ON COLUMN "token"."domain" IS 'If set, then application is a dynamically registered OAuth 2.0 client; value is set to client''s domain';
450 COMMENT ON COLUMN "token"."session_id" IS 'If set, then token is tied to a session; Deletion of session sets value to NULL (via trigger) and removes all scopes without suffix ''_detached''';
451 COMMENT ON COLUMN "token"."redirect_uri" IS 'Authorization codes must be bound to a specific redirect URI';
452 COMMENT ON COLUMN "token"."redirect_uri_explicit" IS 'True if ''redirect_uri'' parameter was explicitly specified during authorization request of the Authorization Code flow (since RFC 6749 requires it to be included in the access token request in this case)';
453 COMMENT ON COLUMN "token"."expiry" IS 'Point in time when code or token expired; In case of "used" authorization codes, authorization code must not be deleted as long as tokens exist which refer to the authorization code';
454 COMMENT ON COLUMN "token"."used" IS 'Can be set to TRUE for authorization codes that have been used (enables deletion of authorization codes that were used twice)';
455 COMMENT ON COLUMN "token"."scope" IS 'Scope as space-separated list of strings (detached scopes are marked with ''_detached'' suffix)';
458 CREATE TABLE "token_scope" (
459 PRIMARY KEY ("token_id", "index"),
460 "token_id" INT8 REFERENCES "token" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
461 "index" INT4,
462 "scope" TEXT NOT NULL );
464 COMMENT ON TABLE "token_scope" IS 'Additional scopes for an authorization code if ''scope1'', ''scope2'', etc. parameters were used during Authorization Code flow to request several access and refresh tokens at once';
467 CREATE TYPE "defeat_strength" AS ENUM ('simple', 'tuple');
469 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';
472 CREATE TYPE "tie_breaking" AS ENUM ('simple', 'variant1', 'variant2');
474 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';
477 CREATE TABLE "policy" (
478 "id" SERIAL4 PRIMARY KEY,
479 "index" INT4 NOT NULL,
480 "active" BOOLEAN NOT NULL DEFAULT TRUE,
481 "name" TEXT NOT NULL UNIQUE,
482 "description" TEXT NOT NULL DEFAULT '',
483 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
484 "min_admission_time" INTERVAL,
485 "max_admission_time" INTERVAL,
486 "discussion_time" INTERVAL,
487 "verification_time" INTERVAL,
488 "voting_time" INTERVAL,
489 "issue_quorum" INT4 CHECK ("issue_quorum" >= 1),
490 "issue_quorum_num" INT4,
491 "issue_quorum_den" INT4,
492 "initiative_quorum" INT4 NOT NULL CHECK ("initiative_quorum" >= 1),
493 "initiative_quorum_num" INT4 NOT NULL,
494 "initiative_quorum_den" INT4 NOT NULL,
495 "defeat_strength" "defeat_strength" NOT NULL DEFAULT 'tuple',
496 "tie_breaking" "tie_breaking" NOT NULL DEFAULT 'variant1',
497 "direct_majority_num" INT4 NOT NULL DEFAULT 1,
498 "direct_majority_den" INT4 NOT NULL DEFAULT 2,
499 "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
500 "direct_majority_positive" INT4 NOT NULL DEFAULT 0,
501 "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0,
502 "indirect_majority_num" INT4 NOT NULL DEFAULT 1,
503 "indirect_majority_den" INT4 NOT NULL DEFAULT 2,
504 "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
505 "indirect_majority_positive" INT4 NOT NULL DEFAULT 0,
506 "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0,
507 "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT FALSE,
508 "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE,
509 CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK (
510 "polling" = ("issue_quorum" ISNULL) AND
511 "polling" = ("issue_quorum_num" ISNULL) AND
512 "polling" = ("issue_quorum_den" ISNULL) ),
513 CONSTRAINT "min_admission_time_smaller_than_max_admission_time" CHECK (
514 "min_admission_time" < "max_admission_time" ),
515 CONSTRAINT "timing_null_or_not_null_constraints" CHECK (
516 ( "polling" = FALSE AND
517 "min_admission_time" NOTNULL AND "max_admission_time" NOTNULL AND
518 "discussion_time" NOTNULL AND
519 "verification_time" NOTNULL AND
520 "voting_time" NOTNULL ) OR
521 ( "polling" = TRUE AND
522 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
523 "discussion_time" NOTNULL AND
524 "verification_time" NOTNULL AND
525 "voting_time" NOTNULL ) OR
526 ( "polling" = TRUE AND
527 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
528 "discussion_time" ISNULL AND
529 "verification_time" ISNULL AND
530 "voting_time" ISNULL ) ),
531 CONSTRAINT "no_reverse_beat_path_requires_tuple_defeat_strength" CHECK (
532 "defeat_strength" = 'tuple'::"defeat_strength" OR
533 "no_reverse_beat_path" = FALSE ) );
534 CREATE INDEX "policy_active_idx" ON "policy" ("active");
536 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
538 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
539 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
540 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';
541 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"';
542 COMMENT ON COLUMN "policy"."max_admission_time" IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';
543 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"';
544 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"';
545 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'')';
546 COMMENT ON COLUMN "policy"."issue_quorum" IS 'Absolute number of supporters needed by an initiative to be "accepted", i.e. pass from ''admission'' to ''discussion'' state';
547 COMMENT ON COLUMN "policy"."issue_quorum_num" IS 'Numerator of supporter quorum to be reached by an initiative to be "accepted", i.e. pass from ''admission'' to ''discussion'' state (Note: further requirements apply, see quorum columns of "area" table)';
548 COMMENT ON COLUMN "policy"."issue_quorum_den" IS 'Denominator of supporter quorum to be reached by an initiative to be "accepted", i.e. pass from ''admission'' to ''discussion'' state (Note: further requirements apply, see quorum columns of "area" table)';
549 COMMENT ON COLUMN "policy"."initiative_quorum" IS 'Absolute number of satisfied supporters to be reached by an initiative to be "admitted" for voting';
550 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
551 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
552 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';
553 COMMENT ON COLUMN "policy"."tie_breaking" IS 'Tie-breaker for the Schulze method; see type "tie_breaking"; ''variant1'' or ''variant2'' are recommended';
554 COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner';
555 COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner';
556 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.';
557 COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner';
558 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';
559 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';
560 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';
561 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.';
562 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';
563 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';
564 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.';
565 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").';
568 CREATE TABLE "unit" (
569 "id" SERIAL4 PRIMARY KEY,
570 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
571 "active" BOOLEAN NOT NULL DEFAULT TRUE,
572 "name" TEXT NOT NULL,
573 "description" TEXT NOT NULL DEFAULT '',
574 "external_reference" TEXT,
575 "member_count" INT4,
576 "region" JSONB,
577 "text_search_data" TSVECTOR );
578 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
579 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
580 CREATE INDEX "unit_active_idx" ON "unit" ("active");
581 CREATE INDEX "unit_region_idx" ON "unit" USING gist ((GeoJSON_to_ecluster("region")));
582 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
583 CREATE TRIGGER "update_text_search_data"
584 BEFORE INSERT OR UPDATE ON "unit"
585 FOR EACH ROW EXECUTE PROCEDURE
586 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
587 "name", "description" );
589 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
591 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
592 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this unit';
593 COMMENT ON COLUMN "unit"."external_reference" IS 'Opaque data field to store an external reference';
594 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege" (only active members counted)';
595 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';
598 CREATE TABLE "subscription" (
599 PRIMARY KEY ("member_id", "unit_id"),
600 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
601 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
602 CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id");
604 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';
607 CREATE TABLE "area" (
608 UNIQUE ("unit_id", "id"), -- index needed for foreign-key on table "event"
609 "id" SERIAL4 PRIMARY KEY,
610 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
611 "active" BOOLEAN NOT NULL DEFAULT TRUE,
612 "name" TEXT NOT NULL,
613 "description" TEXT NOT NULL DEFAULT '',
614 "quorum_standard" NUMERIC NOT NULL DEFAULT 2 CHECK ("quorum_standard" >= 0),
615 "quorum_issues" NUMERIC NOT NULL DEFAULT 1 CHECK ("quorum_issues" > 0),
616 "quorum_time" INTERVAL NOT NULL DEFAULT '1 day' CHECK ("quorum_time" > '0'::INTERVAL),
617 "quorum_exponent" NUMERIC NOT NULL DEFAULT 0.5 CHECK ("quorum_exponent" BETWEEN 0 AND 1),
618 "quorum_factor" NUMERIC NOT NULL DEFAULT 2 CHECK ("quorum_factor" >= 1),
619 "quorum_den" INT4 CHECK ("quorum_den" > 0),
620 "issue_quorum" INT4,
621 "external_reference" TEXT,
622 "region" JSONB,
623 "text_search_data" TSVECTOR );
624 CREATE INDEX "area_active_idx" ON "area" ("active");
625 CREATE INDEX "area_region_idx" ON "area" USING gist ((GeoJSON_to_ecluster("region")));
626 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
627 CREATE TRIGGER "update_text_search_data"
628 BEFORE INSERT OR UPDATE ON "area"
629 FOR EACH ROW EXECUTE PROCEDURE
630 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
631 "name", "description" );
633 COMMENT ON TABLE "area" IS 'Subject areas';
635 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
636 COMMENT ON COLUMN "area"."quorum_standard" IS 'Parameter for dynamic issue quorum: default quorum';
637 COMMENT ON COLUMN "area"."quorum_issues" IS 'Parameter for dynamic issue quorum: number of open issues for default quorum';
638 COMMENT ON COLUMN "area"."quorum_time" IS 'Parameter for dynamic issue quorum: discussion, verification, and voting time of open issues to result in the given default quorum (open issues with shorter time will increase quorum and open issues with longer time will reduce quorum if "quorum_exponent" is greater than zero)';
639 COMMENT ON COLUMN "area"."quorum_exponent" IS 'Parameter for dynamic issue quorum: set to zero to ignore duration of open issues, set to one to fully take duration of open issues into account; defaults to 0.5';
640 COMMENT ON COLUMN "area"."quorum_factor" IS 'Parameter for dynamic issue quorum: factor to increase dynamic quorum when a number of "quorum_issues" issues with "quorum_time" duration of discussion, verification, and voting phase are added to the number of open admitted issues';
641 COMMENT ON COLUMN "area"."quorum_den" IS 'Parameter for dynamic issue quorum: when set, dynamic quorum is multiplied with "issue"."population" and divided by "quorum_den" (and then rounded up)';
642 COMMENT ON COLUMN "area"."issue_quorum" IS 'Additional dynamic issue quorum based on the number of open accepted issues; automatically calculated by function "issue_admission"';
643 COMMENT ON COLUMN "area"."external_reference" IS 'Opaque data field to store an external reference';
644 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';
647 CREATE TABLE "ignored_area" (
648 PRIMARY KEY ("member_id", "area_id"),
649 "member_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
650 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
651 CREATE INDEX "ignored_area_area_id_idx" ON "ignored_area" ("area_id");
653 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';
656 CREATE TABLE "allowed_policy" (
657 PRIMARY KEY ("area_id", "policy_id"),
658 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
659 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
660 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
661 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
663 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
665 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
668 CREATE TABLE "snapshot" (
669 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "issue"
670 "id" SERIAL8 PRIMARY KEY,
671 "calculated" TIMESTAMPTZ NOT NULL DEFAULT now(),
672 "population" INT4,
673 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
674 "issue_id" INT4 ); -- NOTE: following (cyclic) reference is added later through ALTER command: REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE
676 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';
679 CREATE TABLE "snapshot_population" (
680 PRIMARY KEY ("snapshot_id", "member_id"),
681 "snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
682 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE );
684 COMMENT ON TABLE "snapshot_population" IS 'Members with voting right relevant for a snapshot';
687 CREATE TYPE "issue_state" AS ENUM (
688 'admission', 'discussion', 'verification', 'voting',
689 'canceled_by_admin',
690 'canceled_revoked_before_accepted',
691 'canceled_issue_not_accepted',
692 'canceled_after_revocation_during_discussion',
693 'canceled_after_revocation_during_verification',
694 'canceled_no_initiative_admitted',
695 'finished_without_winner', 'finished_with_winner');
697 COMMENT ON TYPE "issue_state" IS 'State of issues';
700 CREATE TABLE "issue" (
701 UNIQUE ("area_id", "id"), -- index needed for foreign-key on table "event"
702 UNIQUE ("policy_id", "id"), -- index needed for foreign-key on table "event"
703 "id" SERIAL4 PRIMARY KEY,
704 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
705 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
706 "admin_notice" TEXT,
707 "external_reference" TEXT,
708 "state" "issue_state" NOT NULL DEFAULT 'admission',
709 "phase_finished" TIMESTAMPTZ,
710 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
711 "accepted" TIMESTAMPTZ,
712 "half_frozen" TIMESTAMPTZ,
713 "fully_frozen" TIMESTAMPTZ,
714 "closed" TIMESTAMPTZ,
715 "cleaned" TIMESTAMPTZ,
716 "min_admission_time" INTERVAL,
717 "max_admission_time" INTERVAL,
718 "discussion_time" INTERVAL NOT NULL,
719 "verification_time" INTERVAL NOT NULL,
720 "voting_time" INTERVAL NOT NULL,
721 "calculated" TIMESTAMPTZ, -- NOTE: copy of "calculated" column of latest snapshot, but no referential integrity to avoid overhead
722 "latest_snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
723 "admission_snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
724 "half_freeze_snapshot_id" INT8,
725 FOREIGN KEY ("id", "half_freeze_snapshot_id")
726 REFERENCES "snapshot" ("issue_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE,
727 "full_freeze_snapshot_id" INT8,
728 FOREIGN KEY ("id", "full_freeze_snapshot_id")
729 REFERENCES "snapshot" ("issue_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE,
730 "population" INT4,
731 "voter_count" INT4,
732 "status_quo_schulze_rank" INT4,
733 CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (
734 ("min_admission_time" NOTNULL) = ("max_admission_time" NOTNULL) AND
735 ("min_admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created")) ),
736 CONSTRAINT "valid_state" CHECK (
737 (
738 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
739 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
740 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL ) OR
741 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL)
742 ) AND (
743 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
744 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
745 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
746 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
747 ("state" = 'canceled_by_admin' AND "closed" NOTNULL) OR
748 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
749 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
750 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
751 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
752 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" = "fully_frozen") OR
753 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen") OR
754 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen")
755 )),
756 CONSTRAINT "phase_finished_only_when_not_closed" CHECK (
757 "phase_finished" ISNULL OR "closed" ISNULL ),
758 CONSTRAINT "state_change_order" CHECK (
759 "created" <= "accepted" AND
760 "accepted" <= "half_frozen" AND
761 "half_frozen" <= "fully_frozen" AND
762 "fully_frozen" <= "closed" ),
763 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
764 "cleaned" ISNULL OR "closed" NOTNULL ),
765 CONSTRAINT "snapshot_required" CHECK (
766 --("accepted" ISNULL OR "admission_snapshot_id" NOTNULL) AND
767 ("half_frozen" ISNULL OR "half_freeze_snapshot_id" NOTNULL) AND
768 ("fully_frozen" ISNULL OR "full_freeze_snapshot_id" NOTNULL) ) );
769 CREATE INDEX "issue_state_idx" ON "issue" ("state");
770 CREATE INDEX "issue_created_idx" ON "issue" ("created");
771 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
772 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
773 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
774 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
775 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
776 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
777 CREATE INDEX "issue_latest_snapshot_id" ON "issue" ("latest_snapshot_id");
778 CREATE INDEX "issue_admission_snapshot_id" ON "issue" ("admission_snapshot_id");
779 CREATE INDEX "issue_half_freeze_snapshot_id" ON "issue" ("half_freeze_snapshot_id");
780 CREATE INDEX "issue_full_freeze_snapshot_id" ON "issue" ("full_freeze_snapshot_id");
782 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
784 COMMENT ON COLUMN "issue"."admin_notice" IS 'Public notice by admin to explain manual interventions, or to announce corrections';
785 COMMENT ON COLUMN "issue"."external_reference" IS 'Opaque data field to store an external reference';
786 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';
787 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when the issue was accepted for further discussion (see columns "issue_quorum_num" and "issue_quorum_den" of table "policy" and quorum columns of table "area")';
788 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.';
789 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.';
790 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.';
791 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
792 COMMENT ON COLUMN "issue"."min_admission_time" IS 'Copied from "policy" table at creation of issue';
793 COMMENT ON COLUMN "issue"."max_admission_time" IS 'Copied from "policy" table at creation of issue';
794 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
795 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
796 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
797 COMMENT ON COLUMN "issue"."calculated" IS 'Point in time, when most recent snapshot and "population" and *_count values were calculated (NOTE: value is equal to "snapshot"."calculated" of snapshot with "id"="issue"."latest_snapshot_id")';
798 COMMENT ON COLUMN "issue"."latest_snapshot_id" IS 'Snapshot id of most recent snapshot';
799 COMMENT ON COLUMN "issue"."admission_snapshot_id" IS 'Snapshot id when issue as accepted or canceled in admission phase';
800 COMMENT ON COLUMN "issue"."half_freeze_snapshot_id" IS 'Snapshot id at end of discussion phase';
801 COMMENT ON COLUMN "issue"."full_freeze_snapshot_id" IS 'Snapshot id at end of verification phase';
802 COMMENT ON COLUMN "issue"."population" IS 'Count of members in "snapshot_population" table with "snapshot_id" equal to "issue"."latest_snapshot_id"';
803 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';
804 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
807 ALTER TABLE "snapshot" ADD FOREIGN KEY ("issue_id") REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
810 CREATE TABLE "issue_order_in_admission_state" (
811 "id" INT8 PRIMARY KEY, -- NOTE: no referential integrity due to performans/locking issues; REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
812 "order_in_area" INT4,
813 "order_in_unit" INT4 );
815 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"';
817 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';
818 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';
819 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';
822 CREATE TABLE "initiative" (
823 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
824 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
825 "id" SERIAL4 PRIMARY KEY,
826 "name" TEXT NOT NULL,
827 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
828 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
829 "revoked" TIMESTAMPTZ,
830 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
831 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
832 "location" JSONB,
833 "external_reference" TEXT,
834 "admitted" BOOLEAN,
835 "supporter_count" INT4,
836 "informed_supporter_count" INT4,
837 "satisfied_supporter_count" INT4,
838 "satisfied_informed_supporter_count" INT4,
839 "harmonic_weight" NUMERIC(12, 3),
840 "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE,
841 "first_preference_votes" INT4,
842 "positive_votes" INT4,
843 "negative_votes" INT4,
844 "direct_majority" BOOLEAN,
845 "indirect_majority" BOOLEAN,
846 "schulze_rank" INT4,
847 "better_than_status_quo" BOOLEAN,
848 "worse_than_status_quo" BOOLEAN,
849 "reverse_beat_path" BOOLEAN,
850 "multistage_majority" BOOLEAN,
851 "eligible" BOOLEAN,
852 "winner" BOOLEAN,
853 "rank" INT4,
854 "text_search_data" TSVECTOR,
855 "draft_text_search_data" TSVECTOR,
856 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
857 CHECK (("revoked" NOTNULL) = ("revoked_by_member_id" NOTNULL)),
858 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
859 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
860 CONSTRAINT "revoked_initiatives_cant_be_admitted"
861 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
862 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
863 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
864 ( "first_preference_votes" ISNULL AND
865 "positive_votes" ISNULL AND "negative_votes" ISNULL AND
866 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
867 "schulze_rank" ISNULL AND
868 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
869 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
870 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ),
871 CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")),
872 CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (
873 "eligible" = FALSE OR
874 ("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ),
875 CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE),
876 CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1),
877 CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE),
878 CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") );
879 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
880 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
881 CREATE INDEX "initiative_location_idx" ON "initiative" USING gist ((GeoJSON_to_ecluster("location")));
882 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
883 CREATE INDEX "initiative_draft_text_search_data_idx" ON "initiative" USING gin ("draft_text_search_data");
884 CREATE TRIGGER "update_text_search_data"
885 BEFORE INSERT OR UPDATE ON "initiative"
886 FOR EACH ROW EXECUTE PROCEDURE
887 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "name");
889 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.';
891 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative does not need to pass the initiative quorum (see "policy"."polling")';
892 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
893 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoke the initiative';
894 COMMENT ON COLUMN "initiative"."location" IS 'Geographic location of initiative as GeoJSON object (automatically copied from most recent draft)';
895 COMMENT ON COLUMN "initiative"."external_reference" IS 'Opaque data field to store an external reference';
896 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
897 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
898 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
899 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
900 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
901 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';
902 COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time';
903 COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice';
904 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Number of direct and delegating voters who ranked this initiative better than the status quo';
905 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo';
906 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"';
907 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths';
908 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking';
909 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo';
910 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)';
911 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''';
912 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';
913 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"';
914 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank"';
915 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';
918 CREATE TABLE "battle" (
919 "issue_id" INT4 NOT NULL,
920 "winning_initiative_id" INT4,
921 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
922 "losing_initiative_id" INT4,
923 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
924 "count" INT4 NOT NULL,
925 CONSTRAINT "initiative_ids_not_equal" CHECK (
926 "winning_initiative_id" != "losing_initiative_id" OR
927 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
928 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) );
929 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
930 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
931 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
933 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';
936 CREATE TABLE "ignored_initiative" (
937 PRIMARY KEY ("member_id", "initiative_id"),
938 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
939 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
940 CREATE INDEX "ignored_initiative_initiative_id_idx" ON "ignored_initiative" ("initiative_id");
942 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';
945 CREATE TABLE "draft" (
946 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
947 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
948 "id" SERIAL8 PRIMARY KEY,
949 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
950 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
951 "formatting_engine" TEXT,
952 "content" TEXT NOT NULL,
953 "location" JSONB,
954 "external_reference" TEXT,
955 "text_search_data" TSVECTOR );
956 CREATE INDEX "draft_created_idx" ON "draft" ("created");
957 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
958 CREATE INDEX "draft_location_idx" ON "draft" USING gist ((GeoJSON_to_ecluster("location")));
959 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
960 CREATE TRIGGER "update_text_search_data"
961 BEFORE INSERT OR UPDATE ON "draft"
962 FOR EACH ROW EXECUTE PROCEDURE
963 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
965 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.';
967 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
968 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
969 COMMENT ON COLUMN "draft"."location" IS 'Geographic location of initiative as GeoJSON object (automatically copied to "initiative" table if draft is most recent)';
970 COMMENT ON COLUMN "draft"."external_reference" IS 'Opaque data field to store an external reference';
973 CREATE TABLE "rendered_draft" (
974 PRIMARY KEY ("draft_id", "format"),
975 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
976 "format" TEXT,
977 "content" TEXT NOT NULL );
979 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)';
982 CREATE TABLE "suggestion" (
983 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
984 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
985 "id" SERIAL8 PRIMARY KEY,
986 "draft_id" INT8 NOT NULL,
987 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
988 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
989 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
990 "name" TEXT NOT NULL,
991 "formatting_engine" TEXT,
992 "content" TEXT NOT NULL DEFAULT '',
993 "location" JSONB,
994 "external_reference" TEXT,
995 "text_search_data" TSVECTOR,
996 "minus2_unfulfilled_count" INT4,
997 "minus2_fulfilled_count" INT4,
998 "minus1_unfulfilled_count" INT4,
999 "minus1_fulfilled_count" INT4,
1000 "plus1_unfulfilled_count" INT4,
1001 "plus1_fulfilled_count" INT4,
1002 "plus2_unfulfilled_count" INT4,
1003 "plus2_fulfilled_count" INT4,
1004 "proportional_order" INT4 );
1005 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
1006 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
1007 CREATE INDEX "suggestion_location_idx" ON "suggestion" USING gist ((GeoJSON_to_ecluster("location")));
1008 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
1009 CREATE TRIGGER "update_text_search_data"
1010 BEFORE INSERT OR UPDATE ON "suggestion"
1011 FOR EACH ROW EXECUTE PROCEDURE
1012 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
1013 "name", "content");
1015 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';
1017 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")';
1018 COMMENT ON COLUMN "suggestion"."location" IS 'Geographic location of suggestion as GeoJSON object';
1019 COMMENT ON COLUMN "suggestion"."external_reference" IS 'Opaque data field to store an external reference';
1020 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
1021 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
1022 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
1023 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
1024 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
1025 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
1026 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
1027 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
1028 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"';
1031 CREATE TABLE "rendered_suggestion" (
1032 PRIMARY KEY ("suggestion_id", "format"),
1033 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1034 "format" TEXT,
1035 "content" TEXT NOT NULL );
1037 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)';
1040 CREATE TABLE "temporary_suggestion_counts" (
1041 "id" INT8 PRIMARY KEY, -- NOTE: no referential integrity due to performance/locking issues; REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1042 "minus2_unfulfilled_count" INT4 NOT NULL,
1043 "minus2_fulfilled_count" INT4 NOT NULL,
1044 "minus1_unfulfilled_count" INT4 NOT NULL,
1045 "minus1_fulfilled_count" INT4 NOT NULL,
1046 "plus1_unfulfilled_count" INT4 NOT NULL,
1047 "plus1_fulfilled_count" INT4 NOT NULL,
1048 "plus2_unfulfilled_count" INT4 NOT NULL,
1049 "plus2_fulfilled_count" INT4 NOT NULL );
1051 COMMENT ON TABLE "temporary_suggestion_counts" IS 'Holds certain calculated values (suggestion counts) temporarily until they can be copied into table "suggestion"';
1053 COMMENT ON COLUMN "temporary_suggestion_counts"."id" IS 'References "suggestion" ("id") but has no referential integrity trigger associated, due to performance/locking issues';
1056 CREATE TABLE "privilege" (
1057 PRIMARY KEY ("unit_id", "member_id"),
1058 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1059 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1060 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
1061 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
1062 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
1063 "member_manager" BOOLEAN NOT NULL DEFAULT FALSE,
1064 "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE,
1065 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE,
1066 "polling_right" BOOLEAN NOT NULL DEFAULT FALSE );
1068 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
1070 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke any privileges to/from other members';
1071 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create and disable sub units';
1072 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create and disable areas and set area parameters';
1073 COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';
1074 COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';
1075 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote';
1076 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';
1079 CREATE TABLE "interest" (
1080 PRIMARY KEY ("issue_id", "member_id"),
1081 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1082 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE );
1083 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
1085 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.';
1088 CREATE TABLE "initiator" (
1089 PRIMARY KEY ("initiative_id", "member_id"),
1090 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1091 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1092 "accepted" BOOLEAN );
1093 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
1095 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.';
1097 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.';
1100 CREATE TABLE "supporter" (
1101 "issue_id" INT4 NOT NULL,
1102 PRIMARY KEY ("initiative_id", "member_id"),
1103 "initiative_id" INT4,
1104 "member_id" INT4,
1105 "draft_id" INT8 NOT NULL,
1106 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
1107 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE );
1108 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
1110 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.';
1112 COMMENT ON COLUMN "supporter"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
1113 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")';
1116 CREATE TABLE "opinion" (
1117 "initiative_id" INT4 NOT NULL,
1118 PRIMARY KEY ("suggestion_id", "member_id"),
1119 "suggestion_id" INT8,
1120 "member_id" INT4,
1121 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
1122 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
1123 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1124 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1125 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
1127 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.';
1129 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
1132 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
1134 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
1137 CREATE TABLE "delegation" (
1138 "id" SERIAL8 PRIMARY KEY,
1139 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1140 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1141 "scope" "delegation_scope" NOT NULL,
1142 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1143 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1144 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1145 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
1146 CONSTRAINT "no_unit_delegation_to_null"
1147 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
1148 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
1149 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
1150 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
1151 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
1152 UNIQUE ("unit_id", "truster_id"),
1153 UNIQUE ("area_id", "truster_id"),
1154 UNIQUE ("issue_id", "truster_id") );
1155 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
1156 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
1158 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
1160 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
1161 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
1162 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
1165 CREATE TABLE "snapshot_issue" (
1166 PRIMARY KEY ("snapshot_id", "issue_id"),
1167 "snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1168 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE ); -- NOTE: trigger "delete_snapshot_on_partial_delete" will delete whole "snapshot"
1169 CREATE INDEX "snapshot_issue_issue_id_idx" ON "snapshot_issue" ("issue_id");
1171 COMMENT ON TABLE "snapshot_issue" IS 'List of issues included in a snapshot';
1173 COMMENT ON COLUMN "snapshot_issue"."issue_id" IS 'Issue being part of the snapshot; Trigger "delete_snapshot_on_partial_delete" on "snapshot_issue" table will delete snapshot if an issue of the snapshot is deleted.';
1176 CREATE TABLE "direct_interest_snapshot" (
1177 PRIMARY KEY ("snapshot_id", "issue_id", "member_id"),
1178 "snapshot_id" INT8,
1179 "issue_id" INT4,
1180 FOREIGN KEY ("snapshot_id", "issue_id")
1181 REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
1182 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1183 "weight" INT4 );
1184 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
1186 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';
1188 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
1191 CREATE TABLE "delegating_interest_snapshot" (
1192 PRIMARY KEY ("snapshot_id", "issue_id", "member_id"),
1193 "snapshot_id" INT8,
1194 "issue_id" INT4,
1195 FOREIGN KEY ("snapshot_id", "issue_id")
1196 REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
1197 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1198 "weight" INT4,
1199 "scope" "delegation_scope" NOT NULL,
1200 "delegate_member_ids" INT4[] NOT NULL );
1201 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
1203 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';
1205 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
1206 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
1207 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"';
1210 CREATE TABLE "direct_supporter_snapshot" (
1211 PRIMARY KEY ("snapshot_id", "initiative_id", "member_id"),
1212 "snapshot_id" INT8,
1213 "issue_id" INT4 NOT NULL,
1214 FOREIGN KEY ("snapshot_id", "issue_id")
1215 REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
1216 "initiative_id" INT4,
1217 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1218 "draft_id" INT8 NOT NULL,
1219 "informed" BOOLEAN NOT NULL,
1220 "satisfied" BOOLEAN NOT NULL,
1221 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1222 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
1223 FOREIGN KEY ("snapshot_id", "issue_id", "member_id") REFERENCES "direct_interest_snapshot" ("snapshot_id", "issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1224 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
1226 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';
1228 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';
1229 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
1230 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
1233 CREATE TABLE "non_voter" (
1234 PRIMARY KEY ("member_id", "issue_id"),
1235 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1236 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
1237 CREATE INDEX "non_voter_issue_id_idx" ON "non_voter" ("issue_id");
1239 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
1242 CREATE TABLE "direct_voter" (
1243 PRIMARY KEY ("issue_id", "member_id"),
1244 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1245 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1246 "weight" INT4,
1247 "comment_changed" TIMESTAMPTZ,
1248 "formatting_engine" TEXT,
1249 "comment" TEXT,
1250 "text_search_data" TSVECTOR );
1251 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
1252 CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data");
1253 CREATE TRIGGER "update_text_search_data"
1254 BEFORE INSERT OR UPDATE ON "direct_voter"
1255 FOR EACH ROW EXECUTE PROCEDURE
1256 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment");
1258 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';
1260 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
1261 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';
1262 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';
1263 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.';
1266 CREATE TABLE "rendered_voter_comment" (
1267 PRIMARY KEY ("issue_id", "member_id", "format"),
1268 FOREIGN KEY ("issue_id", "member_id")
1269 REFERENCES "direct_voter" ("issue_id", "member_id")
1270 ON DELETE CASCADE ON UPDATE CASCADE,
1271 "issue_id" INT4,
1272 "member_id" INT4,
1273 "format" TEXT,
1274 "content" TEXT NOT NULL );
1276 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)';
1279 CREATE TABLE "delegating_voter" (
1280 PRIMARY KEY ("issue_id", "member_id"),
1281 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1282 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1283 "weight" INT4,
1284 "scope" "delegation_scope" NOT NULL,
1285 "delegate_member_ids" INT4[] NOT NULL );
1286 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
1288 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';
1290 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
1291 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
1292 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"';
1295 CREATE TABLE "vote" (
1296 "issue_id" INT4 NOT NULL,
1297 PRIMARY KEY ("initiative_id", "member_id"),
1298 "initiative_id" INT4,
1299 "member_id" INT4,
1300 "grade" INT4 NOT NULL,
1301 "first_preference" BOOLEAN,
1302 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1303 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
1304 CONSTRAINT "first_preference_flag_only_set_on_positive_grades"
1305 CHECK ("grade" > 0 OR "first_preference" ISNULL) );
1306 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
1308 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';
1310 COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
1311 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.';
1312 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.';
1315 CREATE TYPE "event_type" AS ENUM (
1316 'unit_created',
1317 'unit_updated',
1318 'unit_removed',
1319 'area_created',
1320 'area_updated',
1321 'area_removed',
1322 'policy_created',
1323 'policy_updated',
1324 'policy_removed',
1325 'issue_state_changed',
1326 'initiative_created_in_new_issue',
1327 'initiative_created_in_existing_issue',
1328 'initiative_revoked',
1329 'new_draft_created',
1330 'suggestion_created',
1331 'suggestion_removed',
1332 'member_activated',
1333 'member_removed',
1334 'member_active',
1335 'member_name_updated',
1336 'member_profile_updated',
1337 'member_image_updated',
1338 'interest',
1339 'initiator',
1340 'support',
1341 'support_updated',
1342 'suggestion_rated',
1343 'delegation',
1344 'contact' );
1346 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
1349 CREATE TABLE "event" (
1350 "id" SERIAL8 PRIMARY KEY,
1351 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
1352 "event" "event_type" NOT NULL,
1353 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1354 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1355 "scope" "delegation_scope",
1356 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1357 "area_id" INT4,
1358 FOREIGN KEY ("unit_id", "area_id") REFERENCES "area" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1359 "policy_id" INT4 REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1360 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1361 FOREIGN KEY ("area_id", "issue_id") REFERENCES "issue" ("area_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1362 FOREIGN KEY ("policy_id", "issue_id") REFERENCES "issue" ("policy_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1363 "state" "issue_state",
1364 "initiative_id" INT4,
1365 "draft_id" INT8,
1366 "suggestion_id" INT8,
1367 "boolean_value" BOOLEAN,
1368 "numeric_value" INT4,
1369 "text_value" TEXT,
1370 "old_text_value" TEXT,
1371 FOREIGN KEY ("issue_id", "initiative_id")
1372 REFERENCES "initiative" ("issue_id", "id")
1373 ON DELETE CASCADE ON UPDATE CASCADE,
1374 FOREIGN KEY ("initiative_id", "draft_id")
1375 REFERENCES "draft" ("initiative_id", "id")
1376 ON DELETE CASCADE ON UPDATE CASCADE,
1377 -- NOTE: no referential integrity for suggestions because those are
1378 -- actually deleted
1379 -- FOREIGN KEY ("initiative_id", "suggestion_id")
1380 -- REFERENCES "suggestion" ("initiative_id", "id")
1381 -- ON DELETE CASCADE ON UPDATE CASCADE,
1382 CONSTRAINT "constr_for_issue_state_changed" CHECK (
1383 "event" != 'issue_state_changed' OR (
1384 "member_id" ISNULL AND
1385 "other_member_id" ISNULL AND
1386 "scope" ISNULL AND
1387 "unit_id" NOTNULL AND
1388 "area_id" NOTNULL AND
1389 "policy_id" NOTNULL AND
1390 "issue_id" NOTNULL AND
1391 "state" NOTNULL AND
1392 "initiative_id" ISNULL AND
1393 "draft_id" ISNULL AND
1394 "suggestion_id" ISNULL AND
1395 "boolean_value" ISNULL AND
1396 "numeric_value" ISNULL AND
1397 "text_value" ISNULL AND
1398 "old_text_value" ISNULL )),
1399 CONSTRAINT "constr_for_initiative_creation_or_revocation_or_new_draft" CHECK (
1400 "event" NOT IN (
1401 'initiative_created_in_new_issue',
1402 'initiative_created_in_existing_issue',
1403 'initiative_revoked',
1404 'new_draft_created'
1405 ) OR (
1406 "member_id" NOTNULL AND
1407 "other_member_id" ISNULL AND
1408 "scope" ISNULL AND
1409 "unit_id" NOTNULL AND
1410 "area_id" NOTNULL AND
1411 "policy_id" NOTNULL AND
1412 "issue_id" NOTNULL AND
1413 "state" NOTNULL AND
1414 "initiative_id" NOTNULL AND
1415 "draft_id" NOTNULL AND
1416 "suggestion_id" ISNULL AND
1417 "boolean_value" ISNULL AND
1418 "numeric_value" ISNULL AND
1419 "text_value" ISNULL AND
1420 "old_text_value" ISNULL )),
1421 CONSTRAINT "constr_for_suggestion_creation" CHECK (
1422 "event" != 'suggestion_created' OR (
1423 "member_id" NOTNULL AND
1424 "other_member_id" ISNULL AND
1425 "scope" ISNULL AND
1426 "unit_id" NOTNULL AND
1427 "area_id" NOTNULL AND
1428 "policy_id" NOTNULL AND
1429 "issue_id" NOTNULL AND
1430 "state" NOTNULL AND
1431 "initiative_id" NOTNULL AND
1432 "draft_id" ISNULL AND
1433 "suggestion_id" NOTNULL AND
1434 "boolean_value" ISNULL AND
1435 "numeric_value" ISNULL AND
1436 "text_value" ISNULL AND
1437 "old_text_value" ISNULL )),
1438 CONSTRAINT "constr_for_suggestion_removal" CHECK (
1439 "event" != 'suggestion_removed' OR (
1440 "member_id" ISNULL AND
1441 "other_member_id" ISNULL AND
1442 "scope" ISNULL AND
1443 "unit_id" NOTNULL AND
1444 "area_id" NOTNULL AND
1445 "policy_id" NOTNULL AND
1446 "issue_id" NOTNULL AND
1447 "state" NOTNULL AND
1448 "initiative_id" NOTNULL AND
1449 "draft_id" ISNULL AND
1450 "suggestion_id" NOTNULL AND
1451 "boolean_value" ISNULL AND
1452 "numeric_value" ISNULL AND
1453 "text_value" ISNULL AND
1454 "old_text_value" ISNULL )),
1455 CONSTRAINT "constr_for_value_less_member_event" CHECK (
1456 "event" NOT IN (
1457 'member_activated',
1458 'member_removed',
1459 'member_profile_updated',
1460 'member_image_updated'
1461 ) OR (
1462 "member_id" NOTNULL AND
1463 "other_member_id" ISNULL AND
1464 "scope" ISNULL AND
1465 "unit_id" ISNULL AND
1466 "area_id" ISNULL AND
1467 "policy_id" ISNULL AND
1468 "issue_id" ISNULL AND
1469 "state" ISNULL AND
1470 "initiative_id" ISNULL AND
1471 "draft_id" ISNULL AND
1472 "suggestion_id" ISNULL AND
1473 "boolean_value" ISNULL AND
1474 "numeric_value" ISNULL AND
1475 "text_value" ISNULL AND
1476 "old_text_value" ISNULL )),
1477 CONSTRAINT "constr_for_member_active" CHECK (
1478 "event" != 'member_active' OR (
1479 "member_id" NOTNULL AND
1480 "other_member_id" ISNULL AND
1481 "scope" ISNULL AND
1482 "unit_id" ISNULL AND
1483 "area_id" ISNULL AND
1484 "policy_id" ISNULL AND
1485 "issue_id" ISNULL AND
1486 "state" ISNULL AND
1487 "initiative_id" ISNULL AND
1488 "draft_id" ISNULL AND
1489 "suggestion_id" ISNULL AND
1490 "boolean_value" NOTNULL AND
1491 "numeric_value" ISNULL AND
1492 "text_value" ISNULL AND
1493 "old_text_value" ISNULL )),
1494 CONSTRAINT "constr_for_member_name_updated" CHECK (
1495 "event" != 'member_name_updated' OR (
1496 "member_id" NOTNULL AND
1497 "other_member_id" ISNULL AND
1498 "scope" ISNULL AND
1499 "unit_id" ISNULL AND
1500 "area_id" ISNULL AND
1501 "policy_id" ISNULL AND
1502 "issue_id" ISNULL AND
1503 "state" ISNULL AND
1504 "initiative_id" ISNULL AND
1505 "draft_id" ISNULL AND
1506 "suggestion_id" ISNULL AND
1507 "boolean_value" ISNULL AND
1508 "numeric_value" ISNULL AND
1509 "text_value" NOTNULL AND
1510 "old_text_value" NOTNULL )),
1511 CONSTRAINT "constr_for_interest" CHECK (
1512 "event" != 'interest' OR (
1513 "member_id" NOTNULL AND
1514 "other_member_id" ISNULL AND
1515 "scope" ISNULL AND
1516 "unit_id" NOTNULL AND
1517 "area_id" NOTNULL AND
1518 "policy_id" NOTNULL AND
1519 "issue_id" NOTNULL AND
1520 "state" NOTNULL AND
1521 "initiative_id" ISNULL AND
1522 "draft_id" ISNULL AND
1523 "suggestion_id" ISNULL AND
1524 "boolean_value" NOTNULL AND
1525 "numeric_value" ISNULL AND
1526 "text_value" ISNULL AND
1527 "old_text_value" ISNULL )),
1528 CONSTRAINT "constr_for_initiator" CHECK (
1529 "event" != 'initiator' OR (
1530 "member_id" NOTNULL AND
1531 "other_member_id" ISNULL AND
1532 "scope" ISNULL AND
1533 "unit_id" NOTNULL AND
1534 "area_id" NOTNULL AND
1535 "policy_id" NOTNULL AND
1536 "issue_id" NOTNULL AND
1537 "state" NOTNULL AND
1538 "initiative_id" NOTNULL AND
1539 "draft_id" ISNULL AND
1540 "suggestion_id" ISNULL AND
1541 "boolean_value" NOTNULL AND
1542 "numeric_value" ISNULL AND
1543 "text_value" ISNULL AND
1544 "old_text_value" ISNULL )),
1545 CONSTRAINT "constr_for_support" CHECK (
1546 "event" != 'support' OR (
1547 "member_id" NOTNULL AND
1548 "other_member_id" ISNULL AND
1549 "scope" ISNULL AND
1550 "unit_id" NOTNULL AND
1551 "area_id" NOTNULL AND
1552 "policy_id" NOTNULL AND
1553 "issue_id" NOTNULL AND
1554 "state" NOTNULL AND
1555 "initiative_id" NOTNULL AND
1556 ("draft_id" NOTNULL) = ("boolean_value" = TRUE) AND
1557 "suggestion_id" ISNULL AND
1558 "boolean_value" NOTNULL AND
1559 "numeric_value" ISNULL AND
1560 "text_value" ISNULL AND
1561 "old_text_value" ISNULL )),
1562 CONSTRAINT "constr_for_support_updated" CHECK (
1563 "event" != 'support_updated' OR (
1564 "member_id" NOTNULL AND
1565 "other_member_id" ISNULL AND
1566 "scope" ISNULL AND
1567 "unit_id" NOTNULL AND
1568 "area_id" NOTNULL AND
1569 "policy_id" NOTNULL AND
1570 "issue_id" NOTNULL AND
1571 "state" NOTNULL AND
1572 "initiative_id" NOTNULL AND
1573 "draft_id" NOTNULL AND
1574 "suggestion_id" ISNULL AND
1575 "boolean_value" ISNULL AND
1576 "numeric_value" ISNULL AND
1577 "text_value" ISNULL AND
1578 "old_text_value" ISNULL )),
1579 CONSTRAINT "constr_for_suggestion_rated" CHECK (
1580 "event" != 'suggestion_rated' OR (
1581 "member_id" NOTNULL AND
1582 "other_member_id" ISNULL AND
1583 "scope" ISNULL AND
1584 "unit_id" NOTNULL AND
1585 "area_id" NOTNULL AND
1586 "policy_id" NOTNULL AND
1587 "issue_id" NOTNULL AND
1588 "state" NOTNULL AND
1589 "initiative_id" NOTNULL AND
1590 "draft_id" ISNULL AND
1591 "suggestion_id" NOTNULL AND
1592 ("boolean_value" NOTNULL) = ("numeric_value" != 0) AND
1593 "numeric_value" NOTNULL AND
1594 "numeric_value" IN (-2, -1, 0, 1, 2) AND
1595 "text_value" ISNULL AND
1596 "old_text_value" ISNULL )),
1597 CONSTRAINT "constr_for_delegation" CHECK (
1598 "event" != 'delegation' OR (
1599 "member_id" NOTNULL AND
1600 ("other_member_id" NOTNULL) OR ("boolean_value" = FALSE) AND
1601 "scope" NOTNULL AND
1602 "unit_id" NOTNULL AND
1603 ("area_id" NOTNULL) = ("scope" != 'unit'::"delegation_scope") AND
1604 "policy_id" ISNULL AND
1605 ("issue_id" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
1606 ("state" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
1607 "initiative_id" ISNULL AND
1608 "draft_id" ISNULL AND
1609 "suggestion_id" ISNULL AND
1610 "boolean_value" NOTNULL AND
1611 "numeric_value" ISNULL AND
1612 "text_value" ISNULL AND
1613 "old_text_value" ISNULL )),
1614 CONSTRAINT "constr_for_contact" CHECK (
1615 "event" != 'contact' OR (
1616 "member_id" NOTNULL AND
1617 "other_member_id" NOTNULL AND
1618 "scope" ISNULL AND
1619 "unit_id" ISNULL AND
1620 "area_id" ISNULL AND
1621 "policy_id" ISNULL AND
1622 "issue_id" ISNULL AND
1623 "state" ISNULL AND
1624 "initiative_id" ISNULL AND
1625 "draft_id" ISNULL AND
1626 "suggestion_id" ISNULL AND
1627 "boolean_value" NOTNULL AND
1628 "numeric_value" ISNULL AND
1629 "text_value" ISNULL AND
1630 "old_text_value" ISNULL )) );
1631 CREATE INDEX "event_occurrence_idx" ON "event" ("occurrence");
1633 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
1635 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
1636 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
1637 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
1638 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
1641 CREATE TABLE "event_processed" (
1642 "event_id" INT8 NOT NULL );
1643 CREATE UNIQUE INDEX "event_processed_singleton_idx" ON "event_processed" ((1));
1645 COMMENT ON TABLE "event_processed" IS 'This table stores one row with the last event_id, for which event handlers have been executed (e.g. notifications having been sent out)';
1646 COMMENT ON INDEX "event_processed_singleton_idx" IS 'This index ensures that "event_processed" only contains one row maximum.';
1649 CREATE TABLE "notification_initiative_sent" (
1650 PRIMARY KEY ("member_id", "initiative_id"),
1651 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1652 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1653 "last_draft_id" INT8 NOT NULL,
1654 "last_suggestion_id" INT8 );
1655 CREATE INDEX "notification_initiative_sent_initiative_idx" ON "notification_initiative_sent" ("initiative_id");
1657 COMMENT ON TABLE "notification_initiative_sent" IS 'Information which initiatives have been promoted to a member in a scheduled notification mail';
1659 COMMENT ON COLUMN "notification_initiative_sent"."last_draft_id" IS 'Current (i.e. last) draft_id when initiative had been promoted';
1660 COMMENT ON COLUMN "notification_initiative_sent"."last_suggestion_id" IS 'Current (i.e. last) draft_id when initiative had been promoted';
1663 CREATE TABLE "newsletter" (
1664 "id" SERIAL4 PRIMARY KEY,
1665 "published" TIMESTAMPTZ NOT NULL,
1666 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1667 "include_all_members" BOOLEAN NOT NULL,
1668 "sent" TIMESTAMPTZ,
1669 "subject" TEXT NOT NULL,
1670 "content" TEXT NOT NULL );
1671 CREATE INDEX "newsletter_unit_id_idx" ON "newsletter" ("unit_id", "published");
1672 CREATE INDEX "newsletter_all_units_published_idx" ON "newsletter" ("published") WHERE "unit_id" ISNULL;
1673 CREATE INDEX "newsletter_published_idx" ON "newsletter" ("published");
1675 COMMENT ON TABLE "newsletter" IS 'Contains newsletters created by administrators to be sent out and for further reference';
1677 COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter is to be sent out (and made available in the frontend)';
1678 COMMENT ON COLUMN "newsletter"."unit_id" IS 'If set, only members with voting right in the given unit are considered to be recipients';
1679 COMMENT ON COLUMN "newsletter"."include_all_members" IS 'TRUE = include all members regardless of their ''disable_notifications'' setting';
1680 COMMENT ON COLUMN "newsletter"."sent" IS 'Timestamp when the newsletter has been mailed out';
1681 COMMENT ON COLUMN "newsletter"."subject" IS 'Subject line (e.g. to be used for the email)';
1682 COMMENT ON COLUMN "newsletter"."content" IS 'Plain text content of the newsletter';
1686 ----------------------------------------------
1687 -- Writing of history entries and event log --
1688 ----------------------------------------------
1691 CREATE FUNCTION "write_member_history_trigger"()
1692 RETURNS TRIGGER
1693 LANGUAGE 'plpgsql' VOLATILE AS $$
1694 BEGIN
1695 IF
1696 ( NEW."active" != OLD."active" OR
1697 NEW."name" != OLD."name" ) AND
1698 OLD."activated" NOTNULL
1699 THEN
1700 INSERT INTO "member_history"
1701 ("member_id", "active", "name")
1702 VALUES (NEW."id", OLD."active", OLD."name");
1703 END IF;
1704 RETURN NULL;
1705 END;
1706 $$;
1708 CREATE TRIGGER "write_member_history"
1709 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1710 "write_member_history_trigger"();
1712 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1713 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1716 CREATE FUNCTION "write_event_unit_trigger"()
1717 RETURNS TRIGGER
1718 LANGUAGE 'plpgsql' VOLATILE AS $$
1719 DECLARE
1720 "event_v" "event_type";
1721 BEGIN
1722 IF TG_OP = 'UPDATE' THEN
1723 IF OLD."active" = FALSE AND NEW."active" = FALSE THEN
1724 RETURN NULL;
1725 ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN
1726 "event_v" := 'unit_removed';
1727 ELSE
1728 "event_v" := 'unit_updated';
1729 END IF;
1730 ELSE
1731 "event_v" := 'unit_created';
1732 END IF;
1733 INSERT INTO "event" ("event", "unit_id") VALUES ("event_v", NEW."id");
1734 RETURN NULL;
1735 END;
1736 $$;
1738 CREATE TRIGGER "write_event_unit" AFTER INSERT OR UPDATE ON "unit"
1739 FOR EACH ROW EXECUTE PROCEDURE "write_event_unit_trigger"();
1741 COMMENT ON FUNCTION "write_event_unit_trigger"() IS 'Implementation of trigger "write_event_unit" on table "unit"';
1742 COMMENT ON TRIGGER "write_event_unit" ON "unit" IS 'Create entry in "event" table on new or changed/disabled units';
1745 CREATE FUNCTION "write_event_area_trigger"()
1746 RETURNS TRIGGER
1747 LANGUAGE 'plpgsql' VOLATILE AS $$
1748 DECLARE
1749 "event_v" "event_type";
1750 BEGIN
1751 IF TG_OP = 'UPDATE' THEN
1752 IF OLD."active" = FALSE AND NEW."active" = FALSE THEN
1753 RETURN NULL;
1754 ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN
1755 "event_v" := 'area_removed';
1756 ELSE
1757 "event_v" := 'area_updated';
1758 END IF;
1759 ELSE
1760 "event_v" := 'area_created';
1761 END IF;
1762 INSERT INTO "event" ("event", "area_id") VALUES ("event_v", NEW."id");
1763 RETURN NULL;
1764 END;
1765 $$;
1767 CREATE TRIGGER "write_event_area" AFTER INSERT OR UPDATE ON "area"
1768 FOR EACH ROW EXECUTE PROCEDURE "write_event_area_trigger"();
1770 COMMENT ON FUNCTION "write_event_area_trigger"() IS 'Implementation of trigger "write_event_area" on table "area"';
1771 COMMENT ON TRIGGER "write_event_area" ON "area" IS 'Create entry in "event" table on new or changed/disabled areas';
1774 CREATE FUNCTION "write_event_policy_trigger"()
1775 RETURNS TRIGGER
1776 LANGUAGE 'plpgsql' VOLATILE AS $$
1777 DECLARE
1778 "event_v" "event_type";
1779 BEGIN
1780 IF TG_OP = 'UPDATE' THEN
1781 IF OLD."active" = FALSE AND NEW."active" = FALSE THEN
1782 RETURN NULL;
1783 ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN
1784 "event_v" := 'policy_removed';
1785 ELSE
1786 "event_v" := 'policy_updated';
1787 END IF;
1788 ELSE
1789 "event_v" := 'policy_created';
1790 END IF;
1791 INSERT INTO "event" ("event", "policy_id") VALUES ("event_v", NEW."id");
1792 RETURN NULL;
1793 END;
1794 $$;
1796 CREATE TRIGGER "write_event_policy" AFTER INSERT OR UPDATE ON "policy"
1797 FOR EACH ROW EXECUTE PROCEDURE "write_event_policy_trigger"();
1799 COMMENT ON FUNCTION "write_event_policy_trigger"() IS 'Implementation of trigger "write_event_policy" on table "policy"';
1800 COMMENT ON TRIGGER "write_event_policy" ON "policy" IS 'Create entry in "event" table on new or changed/disabled policies';
1803 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1804 RETURNS TRIGGER
1805 LANGUAGE 'plpgsql' VOLATILE AS $$
1806 DECLARE
1807 "area_row" "area"%ROWTYPE;
1808 BEGIN
1809 IF NEW."state" != OLD."state" THEN
1810 SELECT * INTO "area_row" FROM "area" WHERE "id" = NEW."area_id"
1811 FOR SHARE;
1812 INSERT INTO "event" (
1813 "event",
1814 "unit_id", "area_id", "policy_id", "issue_id", "state"
1815 ) VALUES (
1816 'issue_state_changed',
1817 "area_row"."unit_id", NEW."area_id", NEW."policy_id",
1818 NEW."id", NEW."state"
1819 );
1820 END IF;
1821 RETURN NULL;
1822 END;
1823 $$;
1825 CREATE TRIGGER "write_event_issue_state_changed"
1826 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1827 "write_event_issue_state_changed_trigger"();
1829 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1830 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1833 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1834 RETURNS TRIGGER
1835 LANGUAGE 'plpgsql' VOLATILE AS $$
1836 DECLARE
1837 "initiative_row" "initiative"%ROWTYPE;
1838 "issue_row" "issue"%ROWTYPE;
1839 "area_row" "area"%ROWTYPE;
1840 "event_v" "event_type";
1841 BEGIN
1842 SELECT * INTO "initiative_row" FROM "initiative"
1843 WHERE "id" = NEW."initiative_id" FOR SHARE;
1844 SELECT * INTO "issue_row" FROM "issue"
1845 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1846 SELECT * INTO "area_row" FROM "area"
1847 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1848 IF EXISTS (
1849 SELECT NULL FROM "draft"
1850 WHERE "initiative_id" = NEW."initiative_id" AND "id" != NEW."id"
1851 FOR SHARE
1852 ) THEN
1853 "event_v" := 'new_draft_created';
1854 ELSE
1855 IF EXISTS (
1856 SELECT NULL FROM "initiative"
1857 WHERE "issue_id" = "initiative_row"."issue_id"
1858 AND "id" != "initiative_row"."id"
1859 FOR SHARE
1860 ) THEN
1861 "event_v" := 'initiative_created_in_existing_issue';
1862 ELSE
1863 "event_v" := 'initiative_created_in_new_issue';
1864 END IF;
1865 END IF;
1866 INSERT INTO "event" (
1867 "event", "member_id",
1868 "unit_id", "area_id", "policy_id", "issue_id", "state",
1869 "initiative_id", "draft_id"
1870 ) VALUES (
1871 "event_v", NEW."author_id",
1872 "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",
1873 "initiative_row"."issue_id", "issue_row"."state",
1874 NEW."initiative_id", NEW."id"
1875 );
1876 RETURN NULL;
1877 END;
1878 $$;
1880 CREATE TRIGGER "write_event_initiative_or_draft_created"
1881 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1882 "write_event_initiative_or_draft_created_trigger"();
1884 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1885 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1888 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1889 RETURNS TRIGGER
1890 LANGUAGE 'plpgsql' VOLATILE AS $$
1891 DECLARE
1892 "issue_row" "issue"%ROWTYPE;
1893 "area_row" "area"%ROWTYPE;
1894 "draft_id_v" "draft"."id"%TYPE;
1895 BEGIN
1896 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1897 SELECT * INTO "issue_row" FROM "issue"
1898 WHERE "id" = NEW."issue_id" FOR SHARE;
1899 SELECT * INTO "area_row" FROM "area"
1900 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1901 SELECT "id" INTO "draft_id_v" FROM "current_draft"
1902 WHERE "initiative_id" = NEW."id" FOR SHARE;
1903 INSERT INTO "event" (
1904 "event", "member_id",
1905 "unit_id", "area_id", "policy_id", "issue_id", "state",
1906 "initiative_id", "draft_id"
1907 ) VALUES (
1908 'initiative_revoked', NEW."revoked_by_member_id",
1909 "area_row"."unit_id", "issue_row"."area_id",
1910 "issue_row"."policy_id",
1911 NEW."issue_id", "issue_row"."state",
1912 NEW."id", "draft_id_v"
1913 );
1914 END IF;
1915 RETURN NULL;
1916 END;
1917 $$;
1919 CREATE TRIGGER "write_event_initiative_revoked"
1920 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1921 "write_event_initiative_revoked_trigger"();
1923 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1924 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1927 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1928 RETURNS TRIGGER
1929 LANGUAGE 'plpgsql' VOLATILE AS $$
1930 DECLARE
1931 "initiative_row" "initiative"%ROWTYPE;
1932 "issue_row" "issue"%ROWTYPE;
1933 "area_row" "area"%ROWTYPE;
1934 BEGIN
1935 SELECT * INTO "initiative_row" FROM "initiative"
1936 WHERE "id" = NEW."initiative_id" FOR SHARE;
1937 SELECT * INTO "issue_row" FROM "issue"
1938 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1939 SELECT * INTO "area_row" FROM "area"
1940 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1941 INSERT INTO "event" (
1942 "event", "member_id",
1943 "unit_id", "area_id", "policy_id", "issue_id", "state",
1944 "initiative_id", "suggestion_id"
1945 ) VALUES (
1946 'suggestion_created', NEW."author_id",
1947 "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",
1948 "initiative_row"."issue_id", "issue_row"."state",
1949 NEW."initiative_id", NEW."id"
1950 );
1951 RETURN NULL;
1952 END;
1953 $$;
1955 CREATE TRIGGER "write_event_suggestion_created"
1956 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1957 "write_event_suggestion_created_trigger"();
1959 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1960 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1963 CREATE FUNCTION "write_event_suggestion_removed_trigger"()
1964 RETURNS TRIGGER
1965 LANGUAGE 'plpgsql' VOLATILE AS $$
1966 DECLARE
1967 "initiative_row" "initiative"%ROWTYPE;
1968 "issue_row" "issue"%ROWTYPE;
1969 "area_row" "area"%ROWTYPE;
1970 BEGIN
1971 SELECT * INTO "initiative_row" FROM "initiative"
1972 WHERE "id" = OLD."initiative_id" FOR SHARE;
1973 IF "initiative_row"."id" NOTNULL THEN
1974 SELECT * INTO "issue_row" FROM "issue"
1975 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1976 SELECT * INTO "area_row" FROM "area"
1977 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1978 INSERT INTO "event" (
1979 "event",
1980 "unit_id", "area_id", "policy_id", "issue_id", "state",
1981 "initiative_id", "suggestion_id"
1982 ) VALUES (
1983 'suggestion_removed',
1984 "area_row"."unit_id", "issue_row"."area_id",
1985 "issue_row"."policy_id",
1986 "initiative_row"."issue_id", "issue_row"."state",
1987 OLD."initiative_id", OLD."id"
1988 );
1989 END IF;
1990 RETURN NULL;
1991 END;
1992 $$;
1994 CREATE TRIGGER "write_event_suggestion_removed"
1995 AFTER DELETE ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1996 "write_event_suggestion_removed_trigger"();
1998 COMMENT ON FUNCTION "write_event_suggestion_removed_trigger"() IS 'Implementation of trigger "write_event_suggestion_removed" on table "issue"';
1999 COMMENT ON TRIGGER "write_event_suggestion_removed" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
2002 CREATE FUNCTION "write_event_member_trigger"()
2003 RETURNS TRIGGER
2004 LANGUAGE 'plpgsql' VOLATILE AS $$
2005 BEGIN
2006 IF TG_OP = 'INSERT' THEN
2007 IF NEW."activated" NOTNULL THEN
2008 INSERT INTO "event" ("event", "member_id")
2009 VALUES ('member_activated', NEW."id");
2010 END IF;
2011 IF NEW."active" THEN
2012 INSERT INTO "event" ("event", "member_id", "boolean_value")
2013 VALUES ('member_active', NEW."id", TRUE);
2014 END IF;
2015 ELSIF TG_OP = 'UPDATE' THEN
2016 IF OLD."id" != NEW."id" THEN
2017 RAISE EXCEPTION 'Cannot change member ID';
2018 END IF;
2019 IF OLD."name" != NEW."name" THEN
2020 INSERT INTO "event" (
2021 "event", "member_id", "text_value", "old_text_value"
2022 ) VALUES (
2023 'member_name_updated', NEW."id", NEW."name", OLD."name"
2024 );
2025 END IF;
2026 IF OLD."active" != NEW."active" THEN
2027 INSERT INTO "event" ("event", "member_id", "boolean_value") VALUES (
2028 'member_active', NEW."id", NEW."active"
2029 );
2030 END IF;
2031 IF
2032 OLD."activated" NOTNULL AND
2033 (OLD."login" NOTNULL OR OLD."authority_login" NOTNULL) AND
2034 NEW."login" ISNULL AND
2035 NEW."authority_login" ISNULL AND
2036 NEW."locked" = TRUE
2037 THEN
2038 INSERT INTO "event" ("event", "member_id")
2039 VALUES ('member_removed', NEW."id");
2040 END IF;
2041 END IF;
2042 RETURN NULL;
2043 END;
2044 $$;
2046 CREATE TRIGGER "write_event_member"
2047 AFTER INSERT OR UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
2048 "write_event_member_trigger"();
2050 COMMENT ON FUNCTION "write_event_member_trigger"() IS 'Implementation of trigger "write_event_member" on table "member"';
2051 COMMENT ON TRIGGER "write_event_member" ON "member" IS 'Create entries in "event" table on insertion to member table';
2054 CREATE FUNCTION "write_event_member_profile_updated_trigger"()
2055 RETURNS TRIGGER
2056 LANGUAGE 'plpgsql' VOLATILE AS $$
2057 BEGIN
2058 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
2059 IF EXISTS (SELECT NULL FROM "member" WHERE "id" = OLD."member_id") THEN
2060 INSERT INTO "event" ("event", "member_id") VALUES (
2061 'member_profile_updated', OLD."member_id"
2062 );
2063 END IF;
2064 END IF;
2065 IF TG_OP = 'UPDATE' THEN
2066 IF OLD."member_id" = NEW."member_id" THEN
2067 RETURN NULL;
2068 END IF;
2069 END IF;
2070 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2071 INSERT INTO "event" ("event", "member_id") VALUES (
2072 'member_profile_updated', NEW."member_id"
2073 );
2074 END IF;
2075 RETURN NULL;
2076 END;
2077 $$;
2079 CREATE TRIGGER "write_event_member_profile_updated"
2080 AFTER INSERT OR UPDATE OR DELETE ON "member_profile"
2081 FOR EACH ROW EXECUTE PROCEDURE
2082 "write_event_member_profile_updated_trigger"();
2084 COMMENT ON FUNCTION "write_event_member_profile_updated_trigger"() IS 'Implementation of trigger "write_event_member_profile_updated" on table "member_profile"';
2085 COMMENT ON TRIGGER "write_event_member_profile_updated" ON "member_profile" IS 'Creates entries in "event" table on member profile update';
2088 CREATE FUNCTION "write_event_member_image_updated_trigger"()
2089 RETURNS TRIGGER
2090 LANGUAGE 'plpgsql' VOLATILE AS $$
2091 BEGIN
2092 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
2093 IF NOT OLD."scaled" THEN
2094 IF EXISTS (SELECT NULL FROM "member" WHERE "id" = OLD."member_id") THEN
2095 INSERT INTO "event" ("event", "member_id") VALUES (
2096 'member_image_updated', OLD."member_id"
2097 );
2098 END IF;
2099 END IF;
2100 END IF;
2101 IF TG_OP = 'UPDATE' THEN
2102 IF
2103 OLD."member_id" = NEW."member_id" AND
2104 OLD."scaled" = NEW."scaled"
2105 THEN
2106 RETURN NULL;
2107 END IF;
2108 END IF;
2109 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2110 IF NOT NEW."scaled" THEN
2111 INSERT INTO "event" ("event", "member_id") VALUES (
2112 'member_image_updated', NEW."member_id"
2113 );
2114 END IF;
2115 END IF;
2116 RETURN NULL;
2117 END;
2118 $$;
2120 CREATE TRIGGER "write_event_member_image_updated"
2121 AFTER INSERT OR UPDATE OR DELETE ON "member_image"
2122 FOR EACH ROW EXECUTE PROCEDURE
2123 "write_event_member_image_updated_trigger"();
2125 COMMENT ON FUNCTION "write_event_member_image_updated_trigger"() IS 'Implementation of trigger "write_event_member_image_updated" on table "member_image"';
2126 COMMENT ON TRIGGER "write_event_member_image_updated" ON "member_image" IS 'Creates entries in "event" table on member image update';
2129 CREATE FUNCTION "write_event_interest_trigger"()
2130 RETURNS TRIGGER
2131 LANGUAGE 'plpgsql' VOLATILE AS $$
2132 DECLARE
2133 "issue_row" "issue"%ROWTYPE;
2134 "area_row" "area"%ROWTYPE;
2135 BEGIN
2136 IF TG_OP = 'UPDATE' THEN
2137 IF OLD = NEW THEN
2138 RETURN NULL;
2139 END IF;
2140 END IF;
2141 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
2142 SELECT * INTO "issue_row" FROM "issue"
2143 WHERE "id" = OLD."issue_id" FOR SHARE;
2144 SELECT * INTO "area_row" FROM "area"
2145 WHERE "id" = "issue_row"."area_id" FOR SHARE;
2146 IF "issue_row"."id" NOTNULL THEN
2147 INSERT INTO "event" (
2148 "event", "member_id",
2149 "unit_id", "area_id", "policy_id", "issue_id", "state",
2150 "boolean_value"
2151 ) VALUES (
2152 'interest', OLD."member_id",
2153 "area_row"."unit_id", "issue_row"."area_id",
2154 "issue_row"."policy_id",
2155 OLD."issue_id", "issue_row"."state",
2156 FALSE
2157 );
2158 END IF;
2159 END IF;
2160 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2161 SELECT * INTO "issue_row" FROM "issue"
2162 WHERE "id" = NEW."issue_id" FOR SHARE;
2163 SELECT * INTO "area_row" FROM "area"
2164 WHERE "id" = "issue_row"."area_id" FOR SHARE;
2165 INSERT INTO "event" (
2166 "event", "member_id",
2167 "unit_id", "area_id", "policy_id", "issue_id", "state",
2168 "boolean_value"
2169 ) VALUES (
2170 'interest', NEW."member_id",
2171 "area_row"."unit_id", "issue_row"."area_id",
2172 "issue_row"."policy_id",
2173 NEW."issue_id", "issue_row"."state",
2174 TRUE
2175 );
2176 END IF;
2177 RETURN NULL;
2178 END;
2179 $$;
2181 CREATE TRIGGER "write_event_interest"
2182 AFTER INSERT OR UPDATE OR DELETE ON "interest" FOR EACH ROW EXECUTE PROCEDURE
2183 "write_event_interest_trigger"();
2185 COMMENT ON FUNCTION "write_event_interest_trigger"() IS 'Implementation of trigger "write_event_interest_inserted" on table "interest"';
2186 COMMENT ON TRIGGER "write_event_interest" ON "interest" IS 'Create entry in "event" table on adding or removing interest';
2189 CREATE FUNCTION "write_event_initiator_trigger"()
2190 RETURNS TRIGGER
2191 LANGUAGE 'plpgsql' VOLATILE AS $$
2192 DECLARE
2193 "initiative_row" "initiative"%ROWTYPE;
2194 "issue_row" "issue"%ROWTYPE;
2195 "area_row" "area"%ROWTYPE;
2196 BEGIN
2197 IF TG_OP = 'UPDATE' THEN
2198 IF
2199 OLD."initiative_id" = NEW."initiative_id" AND
2200 OLD."member_id" = NEW."member_id" AND
2201 coalesce(OLD."accepted", FALSE) = coalesce(NEW."accepted", FALSE)
2202 THEN
2203 RETURN NULL;
2204 END IF;
2205 END IF;
2206 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "accepted_v" THEN
2207 IF coalesce(OLD."accepted", FALSE) = TRUE THEN
2208 SELECT * INTO "initiative_row" FROM "initiative"
2209 WHERE "id" = OLD."initiative_id" FOR SHARE;
2210 IF "initiative_row"."id" NOTNULL THEN
2211 SELECT * INTO "issue_row" FROM "issue"
2212 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
2213 SELECT * INTO "area_row" FROM "area"
2214 WHERE "id" = "issue_row"."area_id" FOR SHARE;
2215 INSERT INTO "event" (
2216 "event", "member_id",
2217 "unit_id", "area_id", "policy_id", "issue_id", "state",
2218 "initiative_id", "boolean_value"
2219 ) VALUES (
2220 'initiator', OLD."member_id",
2221 "area_row"."unit_id", "issue_row"."area_id",
2222 "issue_row"."policy_id",
2223 "issue_row"."id", "issue_row"."state",
2224 OLD."initiative_id", FALSE
2225 );
2226 END IF;
2227 END IF;
2228 END IF;
2229 IF TG_OP = 'UPDATE' AND NOT "rejected_v" THEN
2230 IF coalesce(NEW."accepted", FALSE) = TRUE THEN
2231 SELECT * INTO "initiative_row" FROM "initiative"
2232 WHERE "id" = NEW."initiative_id" FOR SHARE;
2233 SELECT * INTO "issue_row" FROM "issue"
2234 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
2235 SELECT * INTO "area_row" FROM "area"
2236 WHERE "id" = "issue_row"."area_id" FOR SHARE;
2237 INSERT INTO "event" (
2238 "event", "member_id",
2239 "unit_id", "area_id", "policy_id", "issue_id", "state",
2240 "initiative_id", "boolean_value"
2241 ) VALUES (
2242 'initiator', NEW."member_id",
2243 "area_row"."unit_id", "issue_row"."area_id",
2244 "issue_row"."policy_id",
2245 "issue_row"."id", "issue_row"."state",
2246 NEW."initiative_id", TRUE
2247 );
2248 END IF;
2249 END IF;
2250 RETURN NULL;
2251 END;
2252 $$;
2254 CREATE TRIGGER "write_event_initiator"
2255 AFTER UPDATE OR DELETE ON "initiator" FOR EACH ROW EXECUTE PROCEDURE
2256 "write_event_initiator_trigger"();
2258 COMMENT ON FUNCTION "write_event_initiator_trigger"() IS 'Implementation of trigger "write_event_initiator" on table "initiator"';
2259 COMMENT ON TRIGGER "write_event_initiator" ON "initiator" IS 'Create entry in "event" table when accepting or removing initiatorship (NOTE: trigger does not fire on INSERT to avoid events on initiative creation)';
2262 CREATE FUNCTION "write_event_support_trigger"()
2263 RETURNS TRIGGER
2264 LANGUAGE 'plpgsql' VOLATILE AS $$
2265 DECLARE
2266 "issue_row" "issue"%ROWTYPE;
2267 "area_row" "area"%ROWTYPE;
2268 BEGIN
2269 IF TG_OP = 'UPDATE' THEN
2270 IF
2271 OLD."initiative_id" = NEW."initiative_id" AND
2272 OLD."member_id" = NEW."member_id"
2273 THEN
2274 IF OLD."draft_id" != NEW."draft_id" THEN
2275 SELECT * INTO "issue_row" FROM "issue"
2276 WHERE "id" = NEW."issue_id" FOR SHARE;
2277 SELECT * INTO "area_row" FROM "area"
2278 WHERE "id" = "issue_row"."area_id" FOR SHARE;
2279 INSERT INTO "event" (
2280 "event", "member_id",
2281 "unit_id", "area_id", "policy_id", "issue_id", "state",
2282 "initiative_id", "draft_id"
2283 ) VALUES (
2284 'support_updated', NEW."member_id",
2285 "area_row"."unit_id", "issue_row"."area_id",
2286 "issue_row"."policy_id",
2287 "issue_row"."id", "issue_row"."state",
2288 NEW."initiative_id", NEW."draft_id"
2289 );
2290 END IF;
2291 RETURN NULL;
2292 END IF;
2293 END IF;
2294 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
2295 IF EXISTS (
2296 SELECT NULL FROM "initiative" WHERE "id" = OLD."initiative_id"
2297 FOR SHARE
2298 ) THEN
2299 SELECT * INTO "issue_row" FROM "issue"
2300 WHERE "id" = OLD."issue_id" FOR SHARE;
2301 SELECT * INTO "area_row" FROM "area"
2302 WHERE "id" = "issue_row"."area_id" FOR SHARE;
2303 INSERT INTO "event" (
2304 "event", "member_id",
2305 "unit_id", "area_id", "policy_id", "issue_id", "state",
2306 "initiative_id", "boolean_value"
2307 ) VALUES (
2308 'support', OLD."member_id",
2309 "area_row"."unit_id", "issue_row"."area_id",
2310 "issue_row"."policy_id",
2311 "issue_row"."id", "issue_row"."state",
2312 OLD."initiative_id", FALSE
2313 );
2314 END IF;
2315 END IF;
2316 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2317 SELECT * INTO "issue_row" FROM "issue"
2318 WHERE "id" = NEW."issue_id" FOR SHARE;
2319 SELECT * INTO "area_row" FROM "area"
2320 WHERE "id" = "issue_row"."area_id" FOR SHARE;
2321 INSERT INTO "event" (
2322 "event", "member_id",
2323 "unit_id", "area_id", "policy_id", "issue_id", "state",
2324 "initiative_id", "draft_id", "boolean_value"
2325 ) VALUES (
2326 'support', NEW."member_id",
2327 "area_row"."unit_id", "issue_row"."area_id",
2328 "issue_row"."policy_id",
2329 "issue_row"."id", "issue_row"."state",
2330 NEW."initiative_id", NEW."draft_id", TRUE
2331 );
2332 END IF;
2333 RETURN NULL;
2334 END;
2335 $$;
2337 CREATE TRIGGER "write_event_support"
2338 AFTER INSERT OR UPDATE OR DELETE ON "supporter" FOR EACH ROW EXECUTE PROCEDURE
2339 "write_event_support_trigger"();
2341 COMMENT ON FUNCTION "write_event_support_trigger"() IS 'Implementation of trigger "write_event_support" on table "supporter"';
2342 COMMENT ON TRIGGER "write_event_support" ON "supporter" IS 'Create entry in "event" table when adding, updating, or removing support';
2345 CREATE FUNCTION "write_event_suggestion_rated_trigger"()
2346 RETURNS TRIGGER
2347 LANGUAGE 'plpgsql' VOLATILE AS $$
2348 DECLARE
2349 "same_pkey_v" BOOLEAN = FALSE;
2350 "initiative_row" "initiative"%ROWTYPE;
2351 "issue_row" "issue"%ROWTYPE;
2352 "area_row" "area"%ROWTYPE;
2353 BEGIN
2354 IF TG_OP = 'UPDATE' THEN
2355 IF
2356 OLD."suggestion_id" = NEW."suggestion_id" AND
2357 OLD."member_id" = NEW."member_id"
2358 THEN
2359 IF
2360 OLD."degree" = NEW."degree" AND
2361 OLD."fulfilled" = NEW."fulfilled"
2362 THEN
2363 RETURN NULL;
2364 END IF;
2365 "same_pkey_v" := TRUE;
2366 END IF;
2367 END IF;
2368 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "same_pkey_v" THEN
2369 IF EXISTS (
2370 SELECT NULL FROM "suggestion" WHERE "id" = OLD."suggestion_id"
2371 FOR SHARE
2372 ) THEN
2373 SELECT * INTO "initiative_row" FROM "initiative"
2374 WHERE "id" = OLD."initiative_id" FOR SHARE;
2375 SELECT * INTO "issue_row" FROM "issue"
2376 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
2377 SELECT * INTO "area_row" FROM "area"
2378 WHERE "id" = "issue_row"."area_id" FOR SHARE;
2379 INSERT INTO "event" (
2380 "event", "member_id",
2381 "unit_id", "area_id", "policy_id", "issue_id", "state",
2382 "initiative_id", "suggestion_id",
2383 "boolean_value", "numeric_value"
2384 ) VALUES (
2385 'suggestion_rated', OLD."member_id",
2386 "area_row"."unit_id", "issue_row"."area_id",
2387 "issue_row"."policy_id",
2388 "initiative_row"."issue_id", "issue_row"."state",
2389 OLD."initiative_id", OLD."suggestion_id",
2390 NULL, 0
2391 );
2392 END IF;
2393 END IF;
2394 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2395 SELECT * INTO "initiative_row" FROM "initiative"
2396 WHERE "id" = NEW."initiative_id" FOR SHARE;
2397 SELECT * INTO "issue_row" FROM "issue"
2398 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
2399 SELECT * INTO "area_row" FROM "area"
2400 WHERE "id" = "issue_row"."area_id" FOR SHARE;
2401 INSERT INTO "event" (
2402 "event", "member_id",
2403 "unit_id", "area_id", "policy_id", "issue_id", "state",
2404 "initiative_id", "suggestion_id",
2405 "boolean_value", "numeric_value"
2406 ) VALUES (
2407 'suggestion_rated', NEW."member_id",
2408 "area_row"."unit_id", "issue_row"."area_id",
2409 "issue_row"."policy_id",
2410 "initiative_row"."issue_id", "issue_row"."state",
2411 NEW."initiative_id", NEW."suggestion_id",
2412 NEW."fulfilled", NEW."degree"
2413 );
2414 END IF;
2415 RETURN NULL;
2416 END;
2417 $$;
2419 CREATE TRIGGER "write_event_suggestion_rated"
2420 AFTER INSERT OR UPDATE OR DELETE ON "opinion" FOR EACH ROW EXECUTE PROCEDURE
2421 "write_event_suggestion_rated_trigger"();
2423 COMMENT ON FUNCTION "write_event_suggestion_rated_trigger"() IS 'Implementation of trigger "write_event_suggestion_rated" on table "opinion"';
2424 COMMENT ON TRIGGER "write_event_suggestion_rated" ON "opinion" IS 'Create entry in "event" table when adding, updating, or removing support';
2427 CREATE FUNCTION "write_event_delegation_trigger"()
2428 RETURNS TRIGGER
2429 LANGUAGE 'plpgsql' VOLATILE AS $$
2430 DECLARE
2431 "issue_row" "issue"%ROWTYPE;
2432 "area_row" "area"%ROWTYPE;
2433 BEGIN
2434 IF TG_OP = 'DELETE' THEN
2435 IF EXISTS (
2436 SELECT NULL FROM "member" WHERE "id" = OLD."truster_id"
2437 ) AND (CASE OLD."scope"
2438 WHEN 'unit'::"delegation_scope" THEN EXISTS (
2439 SELECT NULL FROM "unit" WHERE "id" = OLD."unit_id"
2440 )
2441 WHEN 'area'::"delegation_scope" THEN EXISTS (
2442 SELECT NULL FROM "area" WHERE "id" = OLD."area_id"
2443 )
2444 WHEN 'issue'::"delegation_scope" THEN EXISTS (
2445 SELECT NULL FROM "issue" WHERE "id" = OLD."issue_id"
2446 )
2447 END) THEN
2448 SELECT * INTO "issue_row" FROM "issue"
2449 WHERE "id" = OLD."issue_id" FOR SHARE;
2450 SELECT * INTO "area_row" FROM "area"
2451 WHERE "id" = COALESCE(OLD."area_id", "issue_row"."area_id")
2452 FOR SHARE;
2453 INSERT INTO "event" (
2454 "event", "member_id", "scope",
2455 "unit_id", "area_id", "issue_id", "state",
2456 "boolean_value"
2457 ) VALUES (
2458 'delegation', OLD."truster_id", OLD."scope",
2459 COALESCE(OLD."unit_id", "area_row"."unit_id"), "area_row"."id",
2460 OLD."issue_id", "issue_row"."state",
2461 FALSE
2462 );
2463 END IF;
2464 ELSE
2465 SELECT * INTO "issue_row" FROM "issue"
2466 WHERE "id" = NEW."issue_id" FOR SHARE;
2467 SELECT * INTO "area_row" FROM "area"
2468 WHERE "id" = COALESCE(NEW."area_id", "issue_row"."area_id")
2469 FOR SHARE;
2470 INSERT INTO "event" (
2471 "event", "member_id", "other_member_id", "scope",
2472 "unit_id", "area_id", "issue_id", "state",
2473 "boolean_value"
2474 ) VALUES (
2475 'delegation', NEW."truster_id", NEW."trustee_id", NEW."scope",
2476 COALESCE(NEW."unit_id", "area_row"."unit_id"), "area_row"."id",
2477 NEW."issue_id", "issue_row"."state",
2478 TRUE
2479 );
2480 END IF;
2481 RETURN NULL;
2482 END;
2483 $$;
2485 CREATE TRIGGER "write_event_delegation"
2486 AFTER INSERT OR UPDATE OR DELETE ON "delegation" FOR EACH ROW EXECUTE PROCEDURE
2487 "write_event_delegation_trigger"();
2489 COMMENT ON FUNCTION "write_event_delegation_trigger"() IS 'Implementation of trigger "write_event_delegation" on table "delegation"';
2490 COMMENT ON TRIGGER "write_event_delegation" ON "delegation" IS 'Create entry in "event" table when adding, updating, or removing a delegation';
2493 CREATE FUNCTION "write_event_contact_trigger"()
2494 RETURNS TRIGGER
2495 LANGUAGE 'plpgsql' VOLATILE AS $$
2496 BEGIN
2497 IF TG_OP = 'UPDATE' THEN
2498 IF
2499 OLD."member_id" = NEW."member_id" AND
2500 OLD."other_member_id" = NEW."other_member_id" AND
2501 OLD."public" = NEW."public"
2502 THEN
2503 RETURN NULL;
2504 END IF;
2505 END IF;
2506 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
2507 IF OLD."public" THEN
2508 IF EXISTS (
2509 SELECT NULL FROM "member" WHERE "id" = OLD."member_id"
2510 FOR SHARE
2511 ) AND EXISTS (
2512 SELECT NULL FROM "member" WHERE "id" = OLD."other_member_id"
2513 FOR SHARE
2514 ) THEN
2515 INSERT INTO "event" (
2516 "event", "member_id", "other_member_id", "boolean_value"
2517 ) VALUES (
2518 'contact', OLD."member_id", OLD."other_member_id", FALSE
2519 );
2520 END IF;
2521 END IF;
2522 END IF;
2523 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2524 IF NEW."public" THEN
2525 INSERT INTO "event" (
2526 "event", "member_id", "other_member_id", "boolean_value"
2527 ) VALUES (
2528 'contact', NEW."member_id", NEW."other_member_id", TRUE
2529 );
2530 END IF;
2531 END IF;
2532 RETURN NULL;
2533 END;
2534 $$;
2536 CREATE TRIGGER "write_event_contact"
2537 AFTER INSERT OR UPDATE OR DELETE ON "contact" FOR EACH ROW EXECUTE PROCEDURE
2538 "write_event_contact_trigger"();
2540 COMMENT ON FUNCTION "write_event_contact_trigger"() IS 'Implementation of trigger "write_event_contact" on table "contact"';
2541 COMMENT ON TRIGGER "write_event_contact" ON "contact" IS 'Create entry in "event" table when adding or removing public contacts';
2544 CREATE FUNCTION "send_event_notify_trigger"()
2545 RETURNS TRIGGER
2546 LANGUAGE 'plpgsql' VOLATILE AS $$
2547 BEGIN
2548 EXECUTE 'NOTIFY "event", ''' || NEW."event" || '''';
2549 RETURN NULL;
2550 END;
2551 $$;
2553 CREATE TRIGGER "send_notify"
2554 AFTER INSERT OR UPDATE ON "event" FOR EACH ROW EXECUTE PROCEDURE
2555 "send_event_notify_trigger"();
2559 ----------------------------
2560 -- Additional constraints --
2561 ----------------------------
2564 CREATE FUNCTION "delete_extended_scope_tokens_trigger"()
2565 RETURNS TRIGGER
2566 LANGUAGE 'plpgsql' VOLATILE AS $$
2567 DECLARE
2568 "system_application_row" "system_application"%ROWTYPE;
2569 BEGIN
2570 IF OLD."system_application_id" NOTNULL THEN
2571 SELECT * FROM "system_application" INTO "system_application_row"
2572 WHERE "id" = OLD."system_application_id";
2573 DELETE FROM "token"
2574 WHERE "member_id" = OLD."member_id"
2575 AND "system_application_id" = OLD."system_application_id"
2576 AND NOT COALESCE(
2577 regexp_split_to_array("scope", E'\\s+') <@
2578 regexp_split_to_array(
2579 "system_application_row"."automatic_scope", E'\\s+'
2580 ),
2581 FALSE
2582 );
2583 END IF;
2584 RETURN OLD;
2585 END;
2586 $$;
2588 CREATE TRIGGER "delete_extended_scope_tokens"
2589 BEFORE DELETE ON "member_application" FOR EACH ROW EXECUTE PROCEDURE
2590 "delete_extended_scope_tokens_trigger"();
2593 CREATE FUNCTION "detach_token_from_session_trigger"()
2594 RETURNS TRIGGER
2595 LANGUAGE 'plpgsql' VOLATILE AS $$
2596 BEGIN
2597 UPDATE "token" SET "session_id" = NULL
2598 WHERE "session_id" = OLD."id";
2599 RETURN OLD;
2600 END;
2601 $$;
2603 CREATE TRIGGER "detach_token_from_session"
2604 BEFORE DELETE ON "session" FOR EACH ROW EXECUTE PROCEDURE
2605 "detach_token_from_session_trigger"();
2608 CREATE FUNCTION "delete_non_detached_scope_with_session_trigger"()
2609 RETURNS TRIGGER
2610 LANGUAGE 'plpgsql' VOLATILE AS $$
2611 BEGIN
2612 IF NEW."session_id" ISNULL THEN
2613 SELECT coalesce(string_agg("element", ' '), '') INTO NEW."scope"
2614 FROM unnest(regexp_split_to_array(NEW."scope", E'\\s+')) AS "element"
2615 WHERE "element" LIKE '%_detached';
2616 END IF;
2617 RETURN NEW;
2618 END;
2619 $$;
2621 CREATE TRIGGER "delete_non_detached_scope_with_session"
2622 BEFORE INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
2623 "delete_non_detached_scope_with_session_trigger"();
2626 CREATE FUNCTION "delete_token_with_empty_scope_trigger"()
2627 RETURNS TRIGGER
2628 LANGUAGE 'plpgsql' VOLATILE AS $$
2629 BEGIN
2630 IF NEW."scope" = '' THEN
2631 DELETE FROM "token" WHERE "id" = NEW."id";
2632 END IF;
2633 RETURN NULL;
2634 END;
2635 $$;
2637 CREATE TRIGGER "delete_token_with_empty_scope"
2638 AFTER INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
2639 "delete_token_with_empty_scope_trigger"();
2642 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
2643 RETURNS TRIGGER
2644 LANGUAGE 'plpgsql' VOLATILE AS $$
2645 BEGIN
2646 IF NOT EXISTS (
2647 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
2648 ) THEN
2649 RAISE EXCEPTION 'Cannot create issue without an initial initiative.' USING
2650 ERRCODE = 'integrity_constraint_violation',
2651 HINT = 'Create issue, initiative, and draft within the same transaction.';
2652 END IF;
2653 RETURN NULL;
2654 END;
2655 $$;
2657 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
2658 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
2659 FOR EACH ROW EXECUTE PROCEDURE
2660 "issue_requires_first_initiative_trigger"();
2662 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
2663 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
2666 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
2667 RETURNS TRIGGER
2668 LANGUAGE 'plpgsql' VOLATILE AS $$
2669 DECLARE
2670 "reference_lost" BOOLEAN;
2671 BEGIN
2672 IF TG_OP = 'DELETE' THEN
2673 "reference_lost" := TRUE;
2674 ELSE
2675 "reference_lost" := NEW."issue_id" != OLD."issue_id";
2676 END IF;
2677 IF
2678 "reference_lost" AND NOT EXISTS (
2679 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
2680 )
2681 THEN
2682 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
2683 END IF;
2684 RETURN NULL;
2685 END;
2686 $$;
2688 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
2689 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
2690 FOR EACH ROW EXECUTE PROCEDURE
2691 "last_initiative_deletes_issue_trigger"();
2693 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
2694 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
2697 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
2698 RETURNS TRIGGER
2699 LANGUAGE 'plpgsql' VOLATILE AS $$
2700 BEGIN
2701 IF NOT EXISTS (
2702 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
2703 ) THEN
2704 RAISE EXCEPTION 'Cannot create initiative without an initial draft.' USING
2705 ERRCODE = 'integrity_constraint_violation',
2706 HINT = 'Create issue, initiative and draft within the same transaction.';
2707 END IF;
2708 RETURN NULL;
2709 END;
2710 $$;
2712 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
2713 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
2714 FOR EACH ROW EXECUTE PROCEDURE
2715 "initiative_requires_first_draft_trigger"();
2717 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
2718 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
2721 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
2722 RETURNS TRIGGER
2723 LANGUAGE 'plpgsql' VOLATILE AS $$
2724 DECLARE
2725 "reference_lost" BOOLEAN;
2726 BEGIN
2727 IF TG_OP = 'DELETE' THEN
2728 "reference_lost" := TRUE;
2729 ELSE
2730 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
2731 END IF;
2732 IF
2733 "reference_lost" AND NOT EXISTS (
2734 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
2735 )
2736 THEN
2737 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
2738 END IF;
2739 RETURN NULL;
2740 END;
2741 $$;
2743 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
2744 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
2745 FOR EACH ROW EXECUTE PROCEDURE
2746 "last_draft_deletes_initiative_trigger"();
2748 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
2749 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
2752 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
2753 RETURNS TRIGGER
2754 LANGUAGE 'plpgsql' VOLATILE AS $$
2755 BEGIN
2756 IF NOT EXISTS (
2757 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
2758 ) THEN
2759 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.' USING
2760 ERRCODE = 'integrity_constraint_violation',
2761 HINT = 'Create suggestion and opinion within the same transaction.';
2762 END IF;
2763 RETURN NULL;
2764 END;
2765 $$;
2767 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
2768 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
2769 FOR EACH ROW EXECUTE PROCEDURE
2770 "suggestion_requires_first_opinion_trigger"();
2772 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
2773 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
2776 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
2777 RETURNS TRIGGER
2778 LANGUAGE 'plpgsql' VOLATILE AS $$
2779 DECLARE
2780 "reference_lost" BOOLEAN;
2781 BEGIN
2782 IF TG_OP = 'DELETE' THEN
2783 "reference_lost" := TRUE;
2784 ELSE
2785 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
2786 END IF;
2787 IF
2788 "reference_lost" AND NOT EXISTS (
2789 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
2790 )
2791 THEN
2792 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
2793 END IF;
2794 RETURN NULL;
2795 END;
2796 $$;
2798 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
2799 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
2800 FOR EACH ROW EXECUTE PROCEDURE
2801 "last_opinion_deletes_suggestion_trigger"();
2803 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
2804 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
2807 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()
2808 RETURNS TRIGGER
2809 LANGUAGE 'plpgsql' VOLATILE AS $$
2810 BEGIN
2811 DELETE FROM "direct_voter"
2812 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
2813 RETURN NULL;
2814 END;
2815 $$;
2817 CREATE TRIGGER "non_voter_deletes_direct_voter"
2818 AFTER INSERT OR UPDATE ON "non_voter"
2819 FOR EACH ROW EXECUTE PROCEDURE
2820 "non_voter_deletes_direct_voter_trigger"();
2822 COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';
2823 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")';
2826 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()
2827 RETURNS TRIGGER
2828 LANGUAGE 'plpgsql' VOLATILE AS $$
2829 BEGIN
2830 DELETE FROM "non_voter"
2831 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
2832 RETURN NULL;
2833 END;
2834 $$;
2836 CREATE TRIGGER "direct_voter_deletes_non_voter"
2837 AFTER INSERT OR UPDATE ON "direct_voter"
2838 FOR EACH ROW EXECUTE PROCEDURE
2839 "direct_voter_deletes_non_voter_trigger"();
2841 COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';
2842 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")';
2845 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()
2846 RETURNS TRIGGER
2847 LANGUAGE 'plpgsql' VOLATILE AS $$
2848 BEGIN
2849 IF NEW."comment" ISNULL THEN
2850 NEW."comment_changed" := NULL;
2851 NEW."formatting_engine" := NULL;
2852 END IF;
2853 RETURN NEW;
2854 END;
2855 $$;
2857 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"
2858 BEFORE INSERT OR UPDATE ON "direct_voter"
2859 FOR EACH ROW EXECUTE PROCEDURE
2860 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();
2862 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"';
2863 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.';
2867 ---------------------------------
2868 -- Delete incomplete snapshots --
2869 ---------------------------------
2872 CREATE FUNCTION "delete_snapshot_on_partial_delete_trigger"()
2873 RETURNS TRIGGER
2874 LANGUAGE 'plpgsql' VOLATILE AS $$
2875 BEGIN
2876 IF TG_OP = 'UPDATE' THEN
2877 IF
2878 OLD."snapshot_id" = NEW."snapshot_id" AND
2879 OLD."issue_id" = NEW."issue_id"
2880 THEN
2881 RETURN NULL;
2882 END IF;
2883 END IF;
2884 DELETE FROM "snapshot" WHERE "id" = OLD."snapshot_id";
2885 RETURN NULL;
2886 END;
2887 $$;
2889 CREATE TRIGGER "delete_snapshot_on_partial_delete"
2890 AFTER UPDATE OR DELETE ON "snapshot_issue"
2891 FOR EACH ROW EXECUTE PROCEDURE
2892 "delete_snapshot_on_partial_delete_trigger"();
2894 COMMENT ON FUNCTION "delete_snapshot_on_partial_delete_trigger"() IS 'Implementation of trigger "delete_snapshot_on_partial_delete" on table "snapshot_issue"';
2895 COMMENT ON TRIGGER "delete_snapshot_on_partial_delete" ON "snapshot_issue" IS 'Deletes whole snapshot if one issue is deleted from the snapshot';
2899 ---------------------------------------------------------------
2900 -- Ensure that votes are not modified when issues are closed --
2901 ---------------------------------------------------------------
2903 -- NOTE: Frontends should ensure this anyway, but in case of programming
2904 -- errors the following triggers ensure data integrity.
2907 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
2908 RETURNS TRIGGER
2909 LANGUAGE 'plpgsql' VOLATILE AS $$
2910 DECLARE
2911 "issue_id_v" "issue"."id"%TYPE;
2912 "issue_row" "issue"%ROWTYPE;
2913 BEGIN
2914 IF EXISTS (
2915 SELECT NULL FROM "temporary_transaction_data"
2916 WHERE "txid" = txid_current()
2917 AND "key" = 'override_protection_triggers'
2918 AND "value" = TRUE::TEXT
2919 ) THEN
2920 RETURN NULL;
2921 END IF;
2922 IF TG_OP = 'DELETE' THEN
2923 "issue_id_v" := OLD."issue_id";
2924 ELSE
2925 "issue_id_v" := NEW."issue_id";
2926 END IF;
2927 SELECT INTO "issue_row" * FROM "issue"
2928 WHERE "id" = "issue_id_v" FOR SHARE;
2929 IF (
2930 "issue_row"."closed" NOTNULL OR (
2931 "issue_row"."state" = 'voting' AND
2932 "issue_row"."phase_finished" NOTNULL
2933 )
2934 ) THEN
2935 IF
2936 TG_RELID = 'direct_voter'::regclass AND
2937 TG_OP = 'UPDATE'
2938 THEN
2939 IF
2940 OLD."issue_id" = NEW."issue_id" AND
2941 OLD."member_id" = NEW."member_id" AND
2942 OLD."weight" = NEW."weight"
2943 THEN
2944 RETURN NULL; -- allows changing of voter comment
2945 END IF;
2946 END IF;
2947 RAISE EXCEPTION 'Tried to modify data after voting has been closed.' USING
2948 ERRCODE = 'integrity_constraint_violation';
2949 END IF;
2950 RETURN NULL;
2951 END;
2952 $$;
2954 CREATE TRIGGER "forbid_changes_on_closed_issue"
2955 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
2956 FOR EACH ROW EXECUTE PROCEDURE
2957 "forbid_changes_on_closed_issue_trigger"();
2959 CREATE TRIGGER "forbid_changes_on_closed_issue"
2960 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
2961 FOR EACH ROW EXECUTE PROCEDURE
2962 "forbid_changes_on_closed_issue_trigger"();
2964 CREATE TRIGGER "forbid_changes_on_closed_issue"
2965 AFTER INSERT OR UPDATE OR DELETE ON "vote"
2966 FOR EACH ROW EXECUTE PROCEDURE
2967 "forbid_changes_on_closed_issue_trigger"();
2969 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"';
2970 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';
2971 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';
2972 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';
2976 --------------------------------------------------------------------
2977 -- Auto-retrieval of fields only needed for referential integrity --
2978 --------------------------------------------------------------------
2981 CREATE FUNCTION "autofill_issue_id_trigger"()
2982 RETURNS TRIGGER
2983 LANGUAGE 'plpgsql' VOLATILE AS $$
2984 BEGIN
2985 IF NEW."issue_id" ISNULL THEN
2986 SELECT "issue_id" INTO NEW."issue_id"
2987 FROM "initiative" WHERE "id" = NEW."initiative_id";
2988 END IF;
2989 RETURN NEW;
2990 END;
2991 $$;
2993 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
2994 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
2996 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
2997 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
2999 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
3000 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
3001 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
3004 CREATE FUNCTION "autofill_initiative_id_trigger"()
3005 RETURNS TRIGGER
3006 LANGUAGE 'plpgsql' VOLATILE AS $$
3007 BEGIN
3008 IF NEW."initiative_id" ISNULL THEN
3009 SELECT "initiative_id" INTO NEW."initiative_id"
3010 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
3011 END IF;
3012 RETURN NEW;
3013 END;
3014 $$;
3016 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
3017 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
3019 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
3020 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
3024 -------------------------------------------------------
3025 -- Automatic copying of values for indexing purposes --
3026 -------------------------------------------------------
3029 CREATE FUNCTION "copy_current_draft_data"
3030 ("initiative_id_p" "initiative"."id"%TYPE )
3031 RETURNS VOID
3032 LANGUAGE 'plpgsql' VOLATILE AS $$
3033 BEGIN
3034 PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p"
3035 FOR UPDATE;
3036 UPDATE "initiative" SET
3037 "location" = "draft"."location",
3038 "draft_text_search_data" = "draft"."text_search_data"
3039 FROM "current_draft" AS "draft"
3040 WHERE "initiative"."id" = "initiative_id_p"
3041 AND "draft"."initiative_id" = "initiative_id_p";
3042 END;
3043 $$;
3045 COMMENT ON FUNCTION "copy_current_draft_data"
3046 ( "initiative"."id"%TYPE )
3047 IS 'Helper function for function "copy_current_draft_data_trigger"';
3050 CREATE FUNCTION "copy_current_draft_data_trigger"()
3051 RETURNS TRIGGER
3052 LANGUAGE 'plpgsql' VOLATILE AS $$
3053 BEGIN
3054 IF TG_OP='DELETE' THEN
3055 PERFORM "copy_current_draft_data"(OLD."initiative_id");
3056 ELSE
3057 IF TG_OP='UPDATE' THEN
3058 IF COALESCE(OLD."inititiave_id" != NEW."initiative_id", TRUE) THEN
3059 PERFORM "copy_current_draft_data"(OLD."initiative_id");
3060 END IF;
3061 END IF;
3062 PERFORM "copy_current_draft_data"(NEW."initiative_id");
3063 END IF;
3064 RETURN NULL;
3065 END;
3066 $$;
3068 CREATE TRIGGER "copy_current_draft_data"
3069 AFTER INSERT OR UPDATE OR DELETE ON "draft"
3070 FOR EACH ROW EXECUTE PROCEDURE
3071 "copy_current_draft_data_trigger"();
3073 COMMENT ON FUNCTION "copy_current_draft_data_trigger"() IS 'Implementation of trigger "copy_current_draft_data" on table "draft"';
3074 COMMENT ON TRIGGER "copy_current_draft_data" ON "draft" IS 'Copy certain fields from most recent "draft" to "initiative"';
3078 -----------------------------------------------------
3079 -- Automatic calculation of certain default values --
3080 -----------------------------------------------------
3083 CREATE FUNCTION "copy_timings_trigger"()
3084 RETURNS TRIGGER
3085 LANGUAGE 'plpgsql' VOLATILE AS $$
3086 DECLARE
3087 "policy_row" "policy"%ROWTYPE;
3088 BEGIN
3089 SELECT * INTO "policy_row" FROM "policy"
3090 WHERE "id" = NEW."policy_id";
3091 IF NEW."min_admission_time" ISNULL THEN
3092 NEW."min_admission_time" := "policy_row"."min_admission_time";
3093 END IF;
3094 IF NEW."max_admission_time" ISNULL THEN
3095 NEW."max_admission_time" := "policy_row"."max_admission_time";
3096 END IF;
3097 IF NEW."discussion_time" ISNULL THEN
3098 NEW."discussion_time" := "policy_row"."discussion_time";
3099 END IF;
3100 IF NEW."verification_time" ISNULL THEN
3101 NEW."verification_time" := "policy_row"."verification_time";
3102 END IF;
3103 IF NEW."voting_time" ISNULL THEN
3104 NEW."voting_time" := "policy_row"."voting_time";
3105 END IF;
3106 RETURN NEW;
3107 END;
3108 $$;
3110 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
3111 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
3113 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
3114 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
3117 CREATE FUNCTION "default_for_draft_id_trigger"()
3118 RETURNS TRIGGER
3119 LANGUAGE 'plpgsql' VOLATILE AS $$
3120 BEGIN
3121 IF NEW."draft_id" ISNULL THEN
3122 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
3123 WHERE "initiative_id" = NEW."initiative_id";
3124 END IF;
3125 RETURN NEW;
3126 END;
3127 $$;
3129 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
3130 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
3131 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
3132 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
3134 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
3135 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';
3136 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';
3140 ----------------------------------------
3141 -- Automatic creation of dependencies --
3142 ----------------------------------------
3145 CREATE FUNCTION "autocreate_interest_trigger"()
3146 RETURNS TRIGGER
3147 LANGUAGE 'plpgsql' VOLATILE AS $$
3148 BEGIN
3149 IF NOT EXISTS (
3150 SELECT NULL FROM "initiative" JOIN "interest"
3151 ON "initiative"."issue_id" = "interest"."issue_id"
3152 WHERE "initiative"."id" = NEW."initiative_id"
3153 AND "interest"."member_id" = NEW."member_id"
3154 ) THEN
3155 BEGIN
3156 INSERT INTO "interest" ("issue_id", "member_id")
3157 SELECT "issue_id", NEW."member_id"
3158 FROM "initiative" WHERE "id" = NEW."initiative_id";
3159 EXCEPTION WHEN unique_violation THEN END;
3160 END IF;
3161 RETURN NEW;
3162 END;
3163 $$;
3165 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
3166 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
3168 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
3169 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';
3172 CREATE FUNCTION "autocreate_supporter_trigger"()
3173 RETURNS TRIGGER
3174 LANGUAGE 'plpgsql' VOLATILE AS $$
3175 BEGIN
3176 IF NOT EXISTS (
3177 SELECT NULL FROM "suggestion" JOIN "supporter"
3178 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
3179 WHERE "suggestion"."id" = NEW."suggestion_id"
3180 AND "supporter"."member_id" = NEW."member_id"
3181 ) THEN
3182 BEGIN
3183 INSERT INTO "supporter" ("initiative_id", "member_id")
3184 SELECT "initiative_id", NEW."member_id"
3185 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
3186 EXCEPTION WHEN unique_violation THEN END;
3187 END IF;
3188 RETURN NEW;
3189 END;
3190 $$;
3192 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
3193 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
3195 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
3196 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.';
3200 ------------------------------------------
3201 -- Views and helper functions for views --
3202 ------------------------------------------
3205 CREATE VIEW "member_eligible_to_be_notified" AS
3206 SELECT * FROM "member"
3207 WHERE "activated" NOTNULL AND "locked" = FALSE;
3209 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")';
3212 CREATE VIEW "member_to_notify" AS
3213 SELECT * FROM "member_eligible_to_be_notified"
3214 WHERE "disable_notifications" = FALSE;
3216 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)';
3219 CREATE VIEW "area_quorum" AS
3220 SELECT
3221 "area"."id" AS "area_id",
3222 ceil(
3223 "area"."quorum_standard"::FLOAT8 * "quorum_factor"::FLOAT8 ^ (
3224 coalesce(
3225 ( SELECT sum(
3226 ( extract(epoch from "area"."quorum_time")::FLOAT8 /
3227 extract(epoch from
3228 ("issue"."accepted"-"issue"."created") +
3229 "issue"."discussion_time" +
3230 "issue"."verification_time" +
3231 "issue"."voting_time"
3232 )::FLOAT8
3233 ) ^ "area"."quorum_exponent"::FLOAT8
3234 )
3235 FROM "issue" JOIN "policy"
3236 ON "issue"."policy_id" = "policy"."id"
3237 WHERE "issue"."area_id" = "area"."id"
3238 AND "issue"."accepted" NOTNULL
3239 AND "issue"."closed" ISNULL
3240 AND "policy"."polling" = FALSE
3241 )::FLOAT8, 0::FLOAT8
3242 ) / "area"."quorum_issues"::FLOAT8 - 1::FLOAT8
3243 ) * CASE WHEN "area"."quorum_den" ISNULL THEN 1 ELSE (
3244 SELECT "snapshot"."population"
3245 FROM "snapshot"
3246 WHERE "snapshot"."area_id" = "area"."id"
3247 AND "snapshot"."issue_id" ISNULL
3248 ORDER BY "snapshot"."id" DESC
3249 LIMIT 1
3250 ) END / coalesce("area"."quorum_den", 1)
3252 )::INT4 AS "issue_quorum"
3253 FROM "area";
3255 COMMENT ON VIEW "area_quorum" IS 'Area-based quorum considering number of open (accepted) issues';
3258 CREATE VIEW "area_with_unaccepted_issues" AS
3259 SELECT DISTINCT ON ("area"."id") "area".*
3260 FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id"
3261 WHERE "issue"."state" = 'admission';
3263 COMMENT ON VIEW "area_with_unaccepted_issues" IS 'All areas with unaccepted open issues (needed for issue admission system)';
3266 CREATE VIEW "issue_for_admission" AS
3267 SELECT DISTINCT ON ("issue"."area_id")
3268 "issue".*,
3269 max("initiative"."supporter_count") AS "max_supporter_count"
3270 FROM "issue"
3271 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
3272 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
3273 JOIN "area" ON "issue"."area_id" = "area"."id"
3274 WHERE "issue"."state" = 'admission'::"issue_state"
3275 AND now() >= "issue"."created" + "issue"."min_admission_time"
3276 AND "initiative"."supporter_count" >= "policy"."issue_quorum"
3277 AND "initiative"."supporter_count" * "policy"."issue_quorum_den" >=
3278 "issue"."population" * "policy"."issue_quorum_num"
3279 AND "initiative"."supporter_count" >= "area"."issue_quorum"
3280 AND "initiative"."revoked" ISNULL
3281 GROUP BY "issue"."id"
3282 ORDER BY "issue"."area_id", "max_supporter_count" DESC, "issue"."id";
3284 COMMENT ON VIEW "issue_for_admission" IS 'Contains up to 1 issue per area eligible to pass from ''admission'' to ''discussion'' state; needs to be recalculated after admitting the issue in this view';
3287 CREATE VIEW "unit_delegation" AS
3288 SELECT
3289 "unit"."id" AS "unit_id",
3290 "delegation"."id",
3291 "delegation"."truster_id",
3292 "delegation"."trustee_id",
3293 "delegation"."scope"
3294 FROM "unit"
3295 JOIN "delegation"
3296 ON "delegation"."unit_id" = "unit"."id"
3297 JOIN "member"
3298 ON "delegation"."truster_id" = "member"."id"
3299 JOIN "privilege"
3300 ON "delegation"."unit_id" = "privilege"."unit_id"
3301 AND "delegation"."truster_id" = "privilege"."member_id"
3302 WHERE "member"."active" AND "privilege"."voting_right";
3304 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
3307 CREATE VIEW "area_delegation" AS
3308 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
3309 "area"."id" AS "area_id",
3310 "delegation"."id",
3311 "delegation"."truster_id",
3312 "delegation"."trustee_id",
3313 "delegation"."scope"
3314 FROM "area"
3315 JOIN "delegation"
3316 ON "delegation"."unit_id" = "area"."unit_id"
3317 OR "delegation"."area_id" = "area"."id"
3318 JOIN "member"
3319 ON "delegation"."truster_id" = "member"."id"
3320 JOIN "privilege"
3321 ON "area"."unit_id" = "privilege"."unit_id"
3322 AND "delegation"."truster_id" = "privilege"."member_id"
3323 WHERE "member"."active" AND "privilege"."voting_right"
3324 ORDER BY
3325 "area"."id",
3326 "delegation"."truster_id",
3327 "delegation"."scope" DESC;
3329 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
3332 CREATE VIEW "issue_delegation" AS
3333 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
3334 "issue"."id" AS "issue_id",
3335 "delegation"."id",
3336 "delegation"."truster_id",
3337 "delegation"."trustee_id",
3338 "delegation"."scope"
3339 FROM "issue"
3340 JOIN "area"
3341 ON "area"."id" = "issue"."area_id"
3342 JOIN "delegation"
3343 ON "delegation"."unit_id" = "area"."unit_id"
3344 OR "delegation"."area_id" = "area"."id"
3345 OR "delegation"."issue_id" = "issue"."id"
3346 JOIN "member"
3347 ON "delegation"."truster_id" = "member"."id"
3348 JOIN "privilege"
3349 ON "area"."unit_id" = "privilege"."unit_id"
3350 AND "delegation"."truster_id" = "privilege"."member_id"
3351 WHERE "member"."active" AND "privilege"."voting_right"
3352 ORDER BY
3353 "issue"."id",
3354 "delegation"."truster_id",
3355 "delegation"."scope" DESC;
3357 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
3360 CREATE VIEW "member_count_view" AS
3361 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
3363 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
3366 CREATE VIEW "unit_member" AS
3367 SELECT
3368 "unit"."id" AS "unit_id",
3369 "member"."id" AS "member_id"
3370 FROM "privilege"
3371 JOIN "unit" ON "unit_id" = "privilege"."unit_id"
3372 JOIN "member" ON "member"."id" = "privilege"."member_id"
3373 WHERE "privilege"."voting_right" AND "member"."active";
3375 COMMENT ON VIEW "unit_member" IS 'Active members with voting right in a unit';
3378 CREATE VIEW "unit_member_count" AS
3379 SELECT
3380 "unit"."id" AS "unit_id",
3381 count("unit_member"."member_id") AS "member_count"
3382 FROM "unit" LEFT JOIN "unit_member"
3383 ON "unit"."id" = "unit_member"."unit_id"
3384 GROUP BY "unit"."id";
3386 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
3389 CREATE VIEW "opening_draft" AS
3390 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
3391 ORDER BY "initiative_id", "id";
3393 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
3396 CREATE VIEW "current_draft" AS
3397 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
3398 ORDER BY "initiative_id", "id" DESC;
3400 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
3403 CREATE VIEW "critical_opinion" AS
3404 SELECT * FROM "opinion"
3405 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
3406 OR ("degree" = -2 AND "fulfilled" = TRUE);
3408 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
3411 CREATE VIEW "issue_supporter_in_admission_state" AS
3412 SELECT
3413 "area"."unit_id",
3414 "issue"."area_id",
3415 "issue"."id" AS "issue_id",
3416 "supporter"."member_id",
3417 "direct_interest_snapshot"."weight"
3418 FROM "issue"
3419 JOIN "area" ON "area"."id" = "issue"."area_id"
3420 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
3421 JOIN "direct_interest_snapshot"
3422 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
3423 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
3424 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
3425 WHERE "issue"."state" = 'admission'::"issue_state";
3427 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';
3430 CREATE VIEW "initiative_suggestion_order_calculation" AS
3431 SELECT
3432 "initiative"."id" AS "initiative_id",
3433 ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final"
3434 FROM "initiative" JOIN "issue"
3435 ON "initiative"."issue_id" = "issue"."id"
3436 WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL)
3437 OR ("initiative"."final_suggestion_order_calculated" = FALSE);
3439 COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';
3441 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';
3444 CREATE VIEW "individual_suggestion_ranking" AS
3445 SELECT
3446 "opinion"."initiative_id",
3447 "opinion"."member_id",
3448 "direct_interest_snapshot"."weight",
3449 CASE WHEN
3450 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
3451 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
3452 THEN 1 ELSE
3453 CASE WHEN
3454 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
3455 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
3456 THEN 2 ELSE
3457 CASE WHEN
3458 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
3459 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
3460 THEN 3 ELSE 4 END
3461 END
3462 END AS "preference",
3463 "opinion"."suggestion_id"
3464 FROM "opinion"
3465 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
3466 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
3467 JOIN "direct_interest_snapshot"
3468 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
3469 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
3470 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
3472 COMMENT ON VIEW "individual_suggestion_ranking" IS 'Helper view for "lf_update_suggestion_order" to allow a proportional ordering of suggestions within an initiative';
3475 CREATE VIEW "battle_participant" AS
3476 SELECT "initiative"."id", "initiative"."issue_id"
3477 FROM "issue" JOIN "initiative"
3478 ON "issue"."id" = "initiative"."issue_id"
3479 WHERE "initiative"."admitted"
3480 UNION ALL
3481 SELECT NULL, "id" AS "issue_id"
3482 FROM "issue";
3484 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
3487 CREATE VIEW "battle_view" AS
3488 SELECT
3489 "issue"."id" AS "issue_id",
3490 "winning_initiative"."id" AS "winning_initiative_id",
3491 "losing_initiative"."id" AS "losing_initiative_id",
3492 sum(
3493 CASE WHEN
3494 coalesce("better_vote"."grade", 0) >
3495 coalesce("worse_vote"."grade", 0)
3496 THEN "direct_voter"."weight" ELSE 0 END
3497 ) AS "count"
3498 FROM "issue"
3499 LEFT JOIN "direct_voter"
3500 ON "issue"."id" = "direct_voter"."issue_id"
3501 JOIN "battle_participant" AS "winning_initiative"
3502 ON "issue"."id" = "winning_initiative"."issue_id"
3503 JOIN "battle_participant" AS "losing_initiative"
3504 ON "issue"."id" = "losing_initiative"."issue_id"
3505 LEFT JOIN "vote" AS "better_vote"
3506 ON "direct_voter"."member_id" = "better_vote"."member_id"
3507 AND "winning_initiative"."id" = "better_vote"."initiative_id"
3508 LEFT JOIN "vote" AS "worse_vote"
3509 ON "direct_voter"."member_id" = "worse_vote"."member_id"
3510 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
3511 WHERE "issue"."state" = 'voting'
3512 AND "issue"."phase_finished" NOTNULL
3513 AND (
3514 "winning_initiative"."id" != "losing_initiative"."id" OR
3515 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
3516 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
3517 GROUP BY
3518 "issue"."id",
3519 "winning_initiative"."id",
3520 "losing_initiative"."id";
3522 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';
3525 CREATE VIEW "expired_session" AS
3526 SELECT * FROM "session" WHERE now() > "expiry";
3528 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
3529 DELETE FROM "session" WHERE "id" = OLD."id";
3531 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
3532 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
3535 CREATE VIEW "expired_token" AS
3536 SELECT * FROM "token" WHERE now() > "expiry" AND NOT (
3537 "token_type" = 'authorization' AND "used" AND EXISTS (
3538 SELECT NULL FROM "token" AS "other"
3539 WHERE "other"."authorization_token_id" = "id" ) );
3541 CREATE RULE "delete" AS ON DELETE TO "expired_token" DO INSTEAD
3542 DELETE FROM "token" WHERE "id" = OLD."id";
3544 COMMENT ON VIEW "expired_token" IS 'View containing all expired tokens where DELETE is possible; Note that used authorization codes must not be deleted if still referred to by other tokens';
3547 CREATE VIEW "unused_snapshot" AS
3548 SELECT "snapshot".* FROM "snapshot"
3549 LEFT JOIN "issue"
3550 ON "snapshot"."id" = "issue"."latest_snapshot_id"
3551 OR "snapshot"."id" = "issue"."admission_snapshot_id"
3552 OR "snapshot"."id" = "issue"."half_freeze_snapshot_id"
3553 OR "snapshot"."id" = "issue"."full_freeze_snapshot_id"
3554 WHERE "issue"."id" ISNULL;
3556 CREATE RULE "delete" AS ON DELETE TO "unused_snapshot" DO INSTEAD
3557 DELETE FROM "snapshot" WHERE "id" = OLD."id";
3559 COMMENT ON VIEW "unused_snapshot" IS 'Snapshots that are not referenced by any issue (either as latest snapshot or as snapshot at phase/state change)';
3562 CREATE VIEW "expired_snapshot" AS
3563 SELECT "unused_snapshot".* FROM "unused_snapshot" CROSS JOIN "system_setting"
3564 WHERE "unused_snapshot"."calculated" <
3565 now() - "system_setting"."snapshot_retention";
3567 CREATE RULE "delete" AS ON DELETE TO "expired_snapshot" DO INSTEAD
3568 DELETE FROM "snapshot" WHERE "id" = OLD."id";
3570 COMMENT ON VIEW "expired_snapshot" IS 'Contains "unused_snapshot"s that are older than "system_setting"."snapshot_retention" (for deletion)';
3573 CREATE VIEW "open_issue" AS
3574 SELECT * FROM "issue" WHERE "closed" ISNULL;
3576 COMMENT ON VIEW "open_issue" IS 'All open issues';
3579 CREATE VIEW "member_contingent" AS
3580 SELECT
3581 "member"."id" AS "member_id",
3582 "contingent"."polling",
3583 "contingent"."time_frame",
3584 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
3585 (
3586 SELECT count(1) FROM "draft"
3587 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
3588 WHERE "draft"."author_id" = "member"."id"
3589 AND "initiative"."polling" = "contingent"."polling"
3590 AND "draft"."created" > now() - "contingent"."time_frame"
3591 ) + (
3592 SELECT count(1) FROM "suggestion"
3593 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
3594 WHERE "suggestion"."author_id" = "member"."id"
3595 AND "contingent"."polling" = FALSE
3596 AND "suggestion"."created" > now() - "contingent"."time_frame"
3597 )
3598 ELSE NULL END AS "text_entry_count",
3599 "contingent"."text_entry_limit",
3600 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
3601 SELECT count(1) FROM "opening_draft" AS "draft"
3602 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
3603 WHERE "draft"."author_id" = "member"."id"
3604 AND "initiative"."polling" = "contingent"."polling"
3605 AND "draft"."created" > now() - "contingent"."time_frame"
3606 ) ELSE NULL END AS "initiative_count",
3607 "contingent"."initiative_limit"
3608 FROM "member" CROSS JOIN "contingent";
3610 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
3612 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
3613 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
3616 CREATE VIEW "member_contingent_left" AS
3617 SELECT
3618 "member_id",
3619 "polling",
3620 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
3621 max("initiative_limit" - "initiative_count") AS "initiatives_left"
3622 FROM "member_contingent" GROUP BY "member_id", "polling";
3624 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.';
3627 CREATE VIEW "event_for_notification" AS
3628 SELECT
3629 "member"."id" AS "recipient_id",
3630 "event".*
3631 FROM "member" CROSS JOIN "event"
3632 JOIN "issue" ON "issue"."id" = "event"."issue_id"
3633 JOIN "area" ON "area"."id" = "issue"."area_id"
3634 LEFT JOIN "privilege" ON
3635 "privilege"."member_id" = "member"."id" AND
3636 "privilege"."unit_id" = "area"."unit_id" AND
3637 "privilege"."voting_right" = TRUE
3638 LEFT JOIN "subscription" ON
3639 "subscription"."member_id" = "member"."id" AND
3640 "subscription"."unit_id" = "area"."unit_id"
3641 LEFT JOIN "ignored_area" ON
3642 "ignored_area"."member_id" = "member"."id" AND
3643 "ignored_area"."area_id" = "issue"."area_id"
3644 LEFT JOIN "interest" ON
3645 "interest"."member_id" = "member"."id" AND
3646 "interest"."issue_id" = "event"."issue_id"
3647 LEFT JOIN "supporter" ON
3648 "supporter"."member_id" = "member"."id" AND
3649 "supporter"."initiative_id" = "event"."initiative_id"
3650 WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL)
3651 AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
3652 AND (
3653 "event"."event" = 'issue_state_changed'::"event_type" OR
3654 ( "event"."event" = 'initiative_revoked'::"event_type" AND
3655 "supporter"."member_id" NOTNULL ) );
3657 COMMENT ON VIEW "event_for_notification" IS 'Entries of the "event" table which are of interest for a particular notification mail recipient';
3659 COMMENT ON COLUMN "event_for_notification"."recipient_id" IS 'member_id of the recipient of a notification mail';
3662 CREATE VIEW "updated_initiative" AS
3663 SELECT
3664 "supporter"."member_id" AS "recipient_id",
3665 FALSE AS "featured",
3666 "supporter"."initiative_id"
3667 FROM "supporter"
3668 JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id"
3669 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
3670 LEFT JOIN "notification_initiative_sent" AS "sent" ON
3671 "sent"."member_id" = "supporter"."member_id" AND
3672 "sent"."initiative_id" = "supporter"."initiative_id"
3673 LEFT JOIN "ignored_initiative" ON
3674 "ignored_initiative"."member_id" = "supporter"."member_id" AND
3675 "ignored_initiative"."initiative_id" = "supporter"."initiative_id"
3676 WHERE "issue"."state" IN ('admission', 'discussion')
3677 AND "initiative"."revoked" ISNULL
3678 AND "ignored_initiative"."member_id" ISNULL
3679 AND (
3680 EXISTS (
3681 SELECT NULL FROM "draft"
3682 LEFT JOIN "ignored_member" ON
3683 "ignored_member"."member_id" = "supporter"."member_id" AND
3684 "ignored_member"."other_member_id" = "draft"."author_id"
3685 WHERE "draft"."initiative_id" = "supporter"."initiative_id"
3686 AND "draft"."id" > "supporter"."draft_id"
3687 AND "ignored_member"."member_id" ISNULL
3688 ) OR EXISTS (
3689 SELECT NULL FROM "suggestion"
3690 LEFT JOIN "opinion" ON
3691 "opinion"."member_id" = "supporter"."member_id" AND
3692 "opinion"."suggestion_id" = "suggestion"."id"
3693 LEFT JOIN "ignored_member" ON
3694 "ignored_member"."member_id" = "supporter"."member_id" AND
3695 "ignored_member"."other_member_id" = "suggestion"."author_id"
3696 WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"
3697 AND "opinion"."member_id" ISNULL
3698 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
3699 AND "ignored_member"."member_id" ISNULL
3700 )
3701 );
3703 COMMENT ON VIEW "updated_initiative" IS 'Helper view for view "updated_or_featured_initiative"';
3706 CREATE FUNCTION "featured_initiative"
3707 ( "recipient_id_p" "member"."id"%TYPE,
3708 "area_id_p" "area"."id"%TYPE )
3709 RETURNS SETOF "initiative"."id"%TYPE
3710 LANGUAGE 'plpgsql' STABLE AS $$
3711 DECLARE
3712 "counter_v" "member"."notification_counter"%TYPE;
3713 "sample_size_v" "member"."notification_sample_size"%TYPE;
3714 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
3715 "match_v" BOOLEAN;
3716 "member_id_v" "member"."id"%TYPE;
3717 "seed_v" TEXT;
3718 "initiative_id_v" "initiative"."id"%TYPE;
3719 BEGIN
3720 SELECT "notification_counter", "notification_sample_size"
3721 INTO "counter_v", "sample_size_v"
3722 FROM "member" WHERE "id" = "recipient_id_p";
3723 IF COALESCE("sample_size_v" <= 0, TRUE) THEN
3724 RETURN;
3725 END IF;
3726 "initiative_id_ary" := '{}';
3727 LOOP
3728 "match_v" := FALSE;
3729 FOR "member_id_v", "seed_v" IN
3730 SELECT * FROM (
3731 SELECT DISTINCT
3732 "supporter"."member_id",
3733 md5(
3734 "recipient_id_p" || '-' ||
3735 "counter_v" || '-' ||
3736 "area_id_p" || '-' ||
3737 "supporter"."member_id"
3738 ) AS "seed"
3739 FROM "supporter"
3740 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
3741 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
3742 WHERE "supporter"."member_id" != "recipient_id_p"
3743 AND "issue"."area_id" = "area_id_p"
3744 AND "issue"."state" IN ('admission', 'discussion', 'verification')
3745 ) AS "subquery"
3746 ORDER BY "seed"
3747 LOOP
3748 SELECT "initiative"."id" INTO "initiative_id_v"
3749 FROM "initiative"
3750 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
3751 JOIN "area" ON "area"."id" = "issue"."area_id"
3752 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
3753 LEFT JOIN "supporter" AS "self_support" ON
3754 "self_support"."initiative_id" = "initiative"."id" AND
3755 "self_support"."member_id" = "recipient_id_p"
3756 LEFT JOIN "privilege" ON
3757 "privilege"."member_id" = "recipient_id_p" AND
3758 "privilege"."unit_id" = "area"."unit_id" AND
3759 "privilege"."voting_right" = TRUE
3760 LEFT JOIN "subscription" ON
3761 "subscription"."member_id" = "recipient_id_p" AND
3762 "subscription"."unit_id" = "area"."unit_id"
3763 LEFT JOIN "ignored_initiative" ON
3764 "ignored_initiative"."member_id" = "recipient_id_p" AND
3765 "ignored_initiative"."initiative_id" = "initiative"."id"
3766 WHERE "supporter"."member_id" = "member_id_v"
3767 AND "issue"."area_id" = "area_id_p"
3768 AND "issue"."state" IN ('admission', 'discussion', 'verification')
3769 AND "initiative"."revoked" ISNULL
3770 AND "self_support"."member_id" ISNULL
3771 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
3772 AND (
3773 "privilege"."member_id" NOTNULL OR
3774 "subscription"."member_id" NOTNULL )
3775 AND "ignored_initiative"."member_id" ISNULL
3776 AND NOT EXISTS (
3777 SELECT NULL FROM "draft"
3778 JOIN "ignored_member" ON
3779 "ignored_member"."member_id" = "recipient_id_p" AND
3780 "ignored_member"."other_member_id" = "draft"."author_id"
3781 WHERE "draft"."initiative_id" = "initiative"."id"
3782 )
3783 ORDER BY md5("seed_v" || '-' || "initiative"."id")
3784 LIMIT 1;
3785 IF FOUND THEN
3786 "match_v" := TRUE;
3787 RETURN NEXT "initiative_id_v";
3788 IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
3789 RETURN;
3790 END IF;
3791 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
3792 END IF;
3793 END LOOP;
3794 EXIT WHEN NOT "match_v";
3795 END LOOP;
3796 RETURN;
3797 END;
3798 $$;
3800 COMMENT ON FUNCTION "featured_initiative"
3801 ( "recipient_id_p" "member"."id"%TYPE,
3802 "area_id_p" "area"."id"%TYPE )
3803 IS 'Helper function for view "updated_or_featured_initiative"';
3806 CREATE VIEW "updated_or_featured_initiative" AS
3807 SELECT
3808 "subquery".*,
3809 NOT EXISTS (
3810 SELECT NULL FROM "initiative" AS "better_initiative"
3811 WHERE "better_initiative"."issue_id" = "initiative"."issue_id"
3812 AND
3813 ( COALESCE("better_initiative"."supporter_count", -1),
3814 -"better_initiative"."id" ) >
3815 ( COALESCE("initiative"."supporter_count", -1),
3816 -"initiative"."id" )
3817 ) AS "leading"
3818 FROM (
3819 SELECT * FROM "updated_initiative"
3820 UNION ALL
3821 SELECT
3822 "member"."id" AS "recipient_id",
3823 TRUE AS "featured",
3824 "featured_initiative_id" AS "initiative_id"
3825 FROM "member" CROSS JOIN "area"
3826 CROSS JOIN LATERAL
3827 "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id"
3828 JOIN "initiative" ON "initiative"."id" = "featured_initiative_id"
3829 ) AS "subquery"
3830 JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id";
3832 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';
3834 COMMENT ON COLUMN "updated_or_featured_initiative"."recipient_id" IS '"id" of the member who receives the notification mail';
3835 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")';
3836 COMMENT ON COLUMN "updated_or_featured_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
3837 COMMENT ON COLUMN "updated_or_featured_initiative"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue';
3840 CREATE VIEW "leading_complement_initiative" AS
3841 SELECT * FROM (
3842 SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id")
3843 "uf_initiative"."recipient_id",
3844 FALSE AS "featured",
3845 "uf_initiative"."initiative_id",
3846 TRUE AS "leading"
3847 FROM "updated_or_featured_initiative" AS "uf_initiative"
3848 JOIN "initiative" AS "uf_initiative_full" ON
3849 "uf_initiative_full"."id" = "uf_initiative"."initiative_id"
3850 JOIN "initiative" ON
3851 "initiative"."issue_id" = "uf_initiative_full"."issue_id"
3852 WHERE "initiative"."revoked" ISNULL
3853 ORDER BY
3854 "uf_initiative"."recipient_id",
3855 "initiative"."issue_id",
3856 "initiative"."supporter_count" DESC,
3857 "initiative"."id"
3858 ) AS "subquery"
3859 WHERE NOT EXISTS (
3860 SELECT NULL FROM "updated_or_featured_initiative" AS "other"
3861 WHERE "other"."recipient_id" = "subquery"."recipient_id"
3862 AND "other"."initiative_id" = "subquery"."initiative_id"
3863 );
3865 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';
3866 COMMENT ON COLUMN "leading_complement_initiative"."featured" IS 'Always FALSE in this view';
3867 COMMENT ON COLUMN "leading_complement_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
3868 COMMENT ON COLUMN "leading_complement_initiative"."leading" IS 'Always TRUE in this view';
3871 CREATE VIEW "unfiltered_initiative_for_notification" AS
3872 SELECT
3873 "subquery".*,
3874 "supporter"."member_id" NOTNULL AS "supported",
3875 CASE WHEN "supporter"."member_id" NOTNULL THEN
3876 EXISTS (
3877 SELECT NULL FROM "draft"
3878 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
3879 AND "draft"."id" > "supporter"."draft_id"
3880 )
3881 ELSE
3882 EXISTS (
3883 SELECT NULL FROM "draft"
3884 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
3885 AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE)
3886 )
3887 END AS "new_draft",
3888 CASE WHEN "supporter"."member_id" NOTNULL THEN
3889 ( SELECT count(1) FROM "suggestion"
3890 LEFT JOIN "opinion" ON
3891 "opinion"."member_id" = "supporter"."member_id" AND
3892 "opinion"."suggestion_id" = "suggestion"."id"
3893 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
3894 AND "opinion"."member_id" ISNULL
3895 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
3896 )
3897 ELSE
3898 ( SELECT count(1) FROM "suggestion"
3899 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
3900 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
3901 )
3902 END AS "new_suggestion_count"
3903 FROM (
3904 SELECT * FROM "updated_or_featured_initiative"
3905 UNION ALL
3906 SELECT * FROM "leading_complement_initiative"
3907 ) AS "subquery"
3908 LEFT JOIN "supporter" ON
3909 "supporter"."member_id" = "subquery"."recipient_id" AND
3910 "supporter"."initiative_id" = "subquery"."initiative_id"
3911 LEFT JOIN "notification_initiative_sent" AS "sent" ON
3912 "sent"."member_id" = "subquery"."recipient_id" AND
3913 "sent"."initiative_id" = "subquery"."initiative_id";
3915 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';
3917 COMMENT ON COLUMN "unfiltered_initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient';
3918 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)';
3919 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")';
3922 CREATE VIEW "initiative_for_notification" AS
3923 SELECT "unfiltered1".*
3924 FROM "unfiltered_initiative_for_notification" "unfiltered1"
3925 JOIN "initiative" AS "initiative1" ON
3926 "initiative1"."id" = "unfiltered1"."initiative_id"
3927 JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id"
3928 WHERE EXISTS (
3929 SELECT NULL
3930 FROM "unfiltered_initiative_for_notification" "unfiltered2"
3931 JOIN "initiative" AS "initiative2" ON
3932 "initiative2"."id" = "unfiltered2"."initiative_id"
3933 JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id"
3934 WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"
3935 AND "issue1"."area_id" = "issue2"."area_id"
3936 AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 )
3937 );
3939 COMMENT ON VIEW "initiative_for_notification" IS 'Initiatives to be included in a scheduled notification mail';
3941 COMMENT ON COLUMN "initiative_for_notification"."recipient_id" IS '"id" of the member who receives the notification mail';
3942 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")';
3943 COMMENT ON COLUMN "initiative_for_notification"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
3944 COMMENT ON COLUMN "initiative_for_notification"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue';
3945 COMMENT ON COLUMN "initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient';
3946 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)';
3947 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")';
3950 CREATE VIEW "scheduled_notification_to_send" AS
3951 SELECT * FROM (
3952 SELECT
3953 "id" AS "recipient_id",
3954 now() - CASE WHEN "notification_dow" ISNULL THEN
3955 ( "notification_sent"::DATE + CASE
3956 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
3957 THEN 0 ELSE 1 END
3958 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
3959 ELSE
3960 ( "notification_sent"::DATE +
3961 ( 7 + "notification_dow" -
3962 EXTRACT(DOW FROM
3963 ( "notification_sent"::DATE + CASE
3964 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
3965 THEN 0 ELSE 1 END
3966 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
3967 )::INTEGER
3968 ) % 7 +
3969 CASE
3970 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
3971 THEN 0 ELSE 1
3972 END
3973 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
3974 END AS "pending"
3975 FROM (
3976 SELECT
3977 "id",
3978 COALESCE("notification_sent", "activated") AS "notification_sent",
3979 "notification_dow",
3980 "notification_hour"
3981 FROM "member_to_notify"
3982 WHERE "notification_hour" NOTNULL
3983 ) AS "subquery1"
3984 ) AS "subquery2"
3985 WHERE "pending" > '0'::INTERVAL;
3987 COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending';
3989 COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail';
3990 COMMENT ON COLUMN "scheduled_notification_to_send"."pending" IS 'Duration for which the notification mail has already been pending';
3993 CREATE VIEW "newsletter_to_send" AS
3994 SELECT
3995 "member"."id" AS "recipient_id",
3996 "newsletter"."id" AS "newsletter_id",
3997 "newsletter"."published"
3998 FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"
3999 LEFT JOIN "privilege" ON
4000 "privilege"."member_id" = "member"."id" AND
4001 "privilege"."unit_id" = "newsletter"."unit_id" AND
4002 "privilege"."voting_right" = TRUE
4003 LEFT JOIN "subscription" ON
4004 "subscription"."member_id" = "member"."id" AND
4005 "subscription"."unit_id" = "newsletter"."unit_id"
4006 WHERE "newsletter"."published" <= now()
4007 AND "newsletter"."sent" ISNULL
4008 AND (
4009 "member"."disable_notifications" = FALSE OR
4010 "newsletter"."include_all_members" = TRUE )
4011 AND (
4012 "newsletter"."unit_id" ISNULL OR
4013 "privilege"."member_id" NOTNULL OR
4014 "subscription"."member_id" NOTNULL );
4016 COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';
4018 COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter was supposed to be sent out (can be used for ordering)';
4022 ------------------------------------------------------
4023 -- Row set returning function for delegation chains --
4024 ------------------------------------------------------
4027 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
4028 ('first', 'intermediate', 'last', 'repetition');
4030 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
4033 CREATE TYPE "delegation_chain_row" AS (
4034 "index" INT4,
4035 "member_id" INT4,
4036 "member_valid" BOOLEAN,
4037 "participation" BOOLEAN,
4038 "overridden" BOOLEAN,
4039 "scope_in" "delegation_scope",
4040 "scope_out" "delegation_scope",
4041 "disabled_out" BOOLEAN,
4042 "loop" "delegation_chain_loop_tag" );
4044 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
4046 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
4047 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest; for area and global delegation chains: always null';
4048 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
4049 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
4050 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
4051 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
4052 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
4055 CREATE FUNCTION "delegation_chain_for_closed_issue"
4056 ( "member_id_p" "member"."id"%TYPE,
4057 "issue_id_p" "issue"."id"%TYPE )
4058 RETURNS SETOF "delegation_chain_row"
4059 LANGUAGE 'plpgsql' STABLE AS $$
4060 DECLARE
4061 "output_row" "delegation_chain_row";
4062 "direct_voter_row" "direct_voter"%ROWTYPE;
4063 "delegating_voter_row" "delegating_voter"%ROWTYPE;
4064 BEGIN
4065 "output_row"."index" := 0;
4066 "output_row"."member_id" := "member_id_p";
4067 "output_row"."member_valid" := TRUE;
4068 "output_row"."participation" := FALSE;
4069 "output_row"."overridden" := FALSE;
4070 "output_row"."disabled_out" := FALSE;
4071 LOOP
4072 SELECT INTO "direct_voter_row" * FROM "direct_voter"
4073 WHERE "issue_id" = "issue_id_p"
4074 AND "member_id" = "output_row"."member_id";
4075 IF "direct_voter_row"."member_id" NOTNULL THEN
4076 "output_row"."participation" := TRUE;
4077 "output_row"."scope_out" := NULL;
4078 "output_row"."disabled_out" := NULL;
4079 RETURN NEXT "output_row";
4080 RETURN;
4081 END IF;
4082 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
4083 WHERE "issue_id" = "issue_id_p"
4084 AND "member_id" = "output_row"."member_id";
4085 IF "delegating_voter_row"."member_id" ISNULL THEN
4086 RETURN;
4087 END IF;
4088 "output_row"."scope_out" := "delegating_voter_row"."scope";
4089 RETURN NEXT "output_row";
4090 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
4091 "output_row"."scope_in" := "output_row"."scope_out";
4092 END LOOP;
4093 END;
4094 $$;
4096 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
4097 ( "member"."id"%TYPE,
4098 "member"."id"%TYPE )
4099 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
4102 CREATE FUNCTION "delegation_chain"
4103 ( "member_id_p" "member"."id"%TYPE,
4104 "unit_id_p" "unit"."id"%TYPE,
4105 "area_id_p" "area"."id"%TYPE,
4106 "issue_id_p" "issue"."id"%TYPE,
4107 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
4108 "simulate_default_p" BOOLEAN DEFAULT FALSE )
4109 RETURNS SETOF "delegation_chain_row"
4110 LANGUAGE 'plpgsql' STABLE AS $$
4111 DECLARE
4112 "scope_v" "delegation_scope";
4113 "unit_id_v" "unit"."id"%TYPE;
4114 "area_id_v" "area"."id"%TYPE;
4115 "issue_row" "issue"%ROWTYPE;
4116 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
4117 "loop_member_id_v" "member"."id"%TYPE;
4118 "output_row" "delegation_chain_row";
4119 "output_rows" "delegation_chain_row"[];
4120 "simulate_v" BOOLEAN;
4121 "simulate_here_v" BOOLEAN;
4122 "delegation_row" "delegation"%ROWTYPE;
4123 "row_count" INT4;
4124 "i" INT4;
4125 "loop_v" BOOLEAN;
4126 BEGIN
4127 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
4128 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
4129 END IF;
4130 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
4131 "simulate_v" := TRUE;
4132 ELSE
4133 "simulate_v" := FALSE;
4134 END IF;
4135 IF
4136 "unit_id_p" NOTNULL AND
4137 "area_id_p" ISNULL AND
4138 "issue_id_p" ISNULL
4139 THEN
4140 "scope_v" := 'unit';
4141 "unit_id_v" := "unit_id_p";
4142 ELSIF
4143 "unit_id_p" ISNULL AND
4144 "area_id_p" NOTNULL AND
4145 "issue_id_p" ISNULL
4146 THEN
4147 "scope_v" := 'area';
4148 "area_id_v" := "area_id_p";
4149 SELECT "unit_id" INTO "unit_id_v"
4150 FROM "area" WHERE "id" = "area_id_v";
4151 ELSIF
4152 "unit_id_p" ISNULL AND
4153 "area_id_p" ISNULL AND
4154 "issue_id_p" NOTNULL
4155 THEN
4156 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
4157 IF "issue_row"."id" ISNULL THEN
4158 RETURN;
4159 END IF;
4160 IF "issue_row"."closed" NOTNULL THEN
4161 IF "simulate_v" THEN
4162 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
4163 END IF;
4164 FOR "output_row" IN
4165 SELECT * FROM
4166 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
4167 LOOP
4168 RETURN NEXT "output_row";
4169 END LOOP;
4170 RETURN;
4171 END IF;
4172 "scope_v" := 'issue';
4173 SELECT "area_id" INTO "area_id_v"
4174 FROM "issue" WHERE "id" = "issue_id_p";
4175 SELECT "unit_id" INTO "unit_id_v"
4176 FROM "area" WHERE "id" = "area_id_v";
4177 ELSE
4178 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
4179 END IF;
4180 "visited_member_ids" := '{}';
4181 "loop_member_id_v" := NULL;
4182 "output_rows" := '{}';
4183 "output_row"."index" := 0;
4184 "output_row"."member_id" := "member_id_p";
4185 "output_row"."member_valid" := TRUE;
4186 "output_row"."participation" := FALSE;
4187 "output_row"."overridden" := FALSE;
4188 "output_row"."disabled_out" := FALSE;
4189 "output_row"."scope_out" := NULL;
4190 LOOP
4191 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
4192 "loop_member_id_v" := "output_row"."member_id";
4193 ELSE
4194 "visited_member_ids" :=
4195 "visited_member_ids" || "output_row"."member_id";
4196 END IF;
4197 IF "output_row"."participation" ISNULL THEN
4198 "output_row"."overridden" := NULL;
4199 ELSIF "output_row"."participation" THEN
4200 "output_row"."overridden" := TRUE;
4201 END IF;
4202 "output_row"."scope_in" := "output_row"."scope_out";
4203 "output_row"."member_valid" := EXISTS (
4204 SELECT NULL FROM "member" JOIN "privilege"
4205 ON "privilege"."member_id" = "member"."id"
4206 AND "privilege"."unit_id" = "unit_id_v"
4207 WHERE "id" = "output_row"."member_id"
4208 AND "member"."active" AND "privilege"."voting_right"
4209 );
4210 "simulate_here_v" := (
4211 "simulate_v" AND
4212 "output_row"."member_id" = "member_id_p"
4213 );
4214 "delegation_row" := ROW(NULL);
4215 IF "output_row"."member_valid" OR "simulate_here_v" THEN
4216 IF "scope_v" = 'unit' THEN
4217 IF NOT "simulate_here_v" THEN
4218 SELECT * INTO "delegation_row" FROM "delegation"
4219 WHERE "truster_id" = "output_row"."member_id"
4220 AND "unit_id" = "unit_id_v";
4221 END IF;
4222 ELSIF "scope_v" = 'area' THEN
4223 IF "simulate_here_v" THEN
4224 IF "simulate_trustee_id_p" ISNULL THEN
4225 SELECT * INTO "delegation_row" FROM "delegation"
4226 WHERE "truster_id" = "output_row"."member_id"
4227 AND "unit_id" = "unit_id_v";
4228 END IF;
4229 ELSE
4230 SELECT * INTO "delegation_row" FROM "delegation"
4231 WHERE "truster_id" = "output_row"."member_id"
4232 AND (
4233 "unit_id" = "unit_id_v" OR
4234 "area_id" = "area_id_v"
4235 )
4236 ORDER BY "scope" DESC;
4237 END IF;
4238 ELSIF "scope_v" = 'issue' THEN
4239 IF "issue_row"."fully_frozen" ISNULL THEN
4240 "output_row"."participation" := EXISTS (
4241 SELECT NULL FROM "interest"
4242 WHERE "issue_id" = "issue_id_p"
4243 AND "member_id" = "output_row"."member_id"
4244 );
4245 ELSE
4246 IF "output_row"."member_id" = "member_id_p" THEN
4247 "output_row"."participation" := EXISTS (
4248 SELECT NULL FROM "direct_voter"
4249 WHERE "issue_id" = "issue_id_p"
4250 AND "member_id" = "output_row"."member_id"
4251 );
4252 ELSE
4253 "output_row"."participation" := NULL;
4254 END IF;
4255 END IF;
4256 IF "simulate_here_v" THEN
4257 IF "simulate_trustee_id_p" ISNULL THEN
4258 SELECT * INTO "delegation_row" FROM "delegation"
4259 WHERE "truster_id" = "output_row"."member_id"
4260 AND (
4261 "unit_id" = "unit_id_v" OR
4262 "area_id" = "area_id_v"
4263 )
4264 ORDER BY "scope" DESC;
4265 END IF;
4266 ELSE
4267 SELECT * INTO "delegation_row" FROM "delegation"
4268 WHERE "truster_id" = "output_row"."member_id"
4269 AND (
4270 "unit_id" = "unit_id_v" OR
4271 "area_id" = "area_id_v" OR
4272 "issue_id" = "issue_id_p"
4273 )
4274 ORDER BY "scope" DESC;
4275 END IF;
4276 END IF;
4277 ELSE
4278 "output_row"."participation" := FALSE;
4279 END IF;
4280 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
4281 "output_row"."scope_out" := "scope_v";
4282 "output_rows" := "output_rows" || "output_row";
4283 "output_row"."member_id" := "simulate_trustee_id_p";
4284 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
4285 "output_row"."scope_out" := "delegation_row"."scope";
4286 "output_rows" := "output_rows" || "output_row";
4287 "output_row"."member_id" := "delegation_row"."trustee_id";
4288 ELSIF "delegation_row"."scope" NOTNULL THEN
4289 "output_row"."scope_out" := "delegation_row"."scope";
4290 "output_row"."disabled_out" := TRUE;
4291 "output_rows" := "output_rows" || "output_row";
4292 EXIT;
4293 ELSE
4294 "output_row"."scope_out" := NULL;
4295 "output_rows" := "output_rows" || "output_row";
4296 EXIT;
4297 END IF;
4298 EXIT WHEN "loop_member_id_v" NOTNULL;
4299 "output_row"."index" := "output_row"."index" + 1;
4300 END LOOP;
4301 "row_count" := array_upper("output_rows", 1);
4302 "i" := 1;
4303 "loop_v" := FALSE;
4304 LOOP
4305 "output_row" := "output_rows"["i"];
4306 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
4307 IF "loop_v" THEN
4308 IF "i" + 1 = "row_count" THEN
4309 "output_row"."loop" := 'last';
4310 ELSIF "i" = "row_count" THEN
4311 "output_row"."loop" := 'repetition';
4312 ELSE
4313 "output_row"."loop" := 'intermediate';
4314 END IF;
4315 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
4316 "output_row"."loop" := 'first';
4317 "loop_v" := TRUE;
4318 END IF;
4319 IF "scope_v" = 'unit' THEN
4320 "output_row"."participation" := NULL;
4321 END IF;
4322 RETURN NEXT "output_row";
4323 "i" := "i" + 1;
4324 END LOOP;
4325 RETURN;
4326 END;
4327 $$;
4329 COMMENT ON FUNCTION "delegation_chain"
4330 ( "member"."id"%TYPE,
4331 "unit"."id"%TYPE,
4332 "area"."id"%TYPE,
4333 "issue"."id"%TYPE,
4334 "member"."id"%TYPE,
4335 BOOLEAN )
4336 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
4340 ---------------------------------------------------------
4341 -- Single row returning function for delegation chains --
4342 ---------------------------------------------------------
4345 CREATE TYPE "delegation_info_loop_type" AS ENUM
4346 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
4348 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''';
4351 CREATE TYPE "delegation_info_type" AS (
4352 "own_participation" BOOLEAN,
4353 "own_delegation_scope" "delegation_scope",
4354 "first_trustee_id" INT4,
4355 "first_trustee_participation" BOOLEAN,
4356 "first_trustee_ellipsis" BOOLEAN,
4357 "other_trustee_id" INT4,
4358 "other_trustee_participation" BOOLEAN,
4359 "other_trustee_ellipsis" BOOLEAN,
4360 "delegation_loop" "delegation_info_loop_type",
4361 "participating_member_id" INT4 );
4363 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';
4365 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
4366 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
4367 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
4368 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
4369 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
4370 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
4371 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)';
4372 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
4373 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';
4374 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';
4377 CREATE FUNCTION "delegation_info"
4378 ( "member_id_p" "member"."id"%TYPE,
4379 "unit_id_p" "unit"."id"%TYPE,
4380 "area_id_p" "area"."id"%TYPE,
4381 "issue_id_p" "issue"."id"%TYPE,
4382 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
4383 "simulate_default_p" BOOLEAN DEFAULT FALSE )
4384 RETURNS "delegation_info_type"
4385 LANGUAGE 'plpgsql' STABLE AS $$
4386 DECLARE
4387 "current_row" "delegation_chain_row";
4388 "result" "delegation_info_type";
4389 BEGIN
4390 "result"."own_participation" := FALSE;
4391 FOR "current_row" IN
4392 SELECT * FROM "delegation_chain"(
4393 "member_id_p",
4394 "unit_id_p", "area_id_p", "issue_id_p",
4395 "simulate_trustee_id_p", "simulate_default_p")
4396 LOOP
4397 IF
4398 "result"."participating_member_id" ISNULL AND
4399 "current_row"."participation"
4400 THEN
4401 "result"."participating_member_id" := "current_row"."member_id";
4402 END IF;
4403 IF "current_row"."member_id" = "member_id_p" THEN
4404 "result"."own_participation" := "current_row"."participation";
4405 "result"."own_delegation_scope" := "current_row"."scope_out";
4406 IF "current_row"."loop" = 'first' THEN
4407 "result"."delegation_loop" := 'own';
4408 END IF;
4409 ELSIF
4410 "current_row"."member_valid" AND
4411 ( "current_row"."loop" ISNULL OR
4412 "current_row"."loop" != 'repetition' )
4413 THEN
4414 IF "result"."first_trustee_id" ISNULL THEN
4415 "result"."first_trustee_id" := "current_row"."member_id";
4416 "result"."first_trustee_participation" := "current_row"."participation";
4417 "result"."first_trustee_ellipsis" := FALSE;
4418 IF "current_row"."loop" = 'first' THEN
4419 "result"."delegation_loop" := 'first';
4420 END IF;
4421 ELSIF "result"."other_trustee_id" ISNULL THEN
4422 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
4423 "result"."other_trustee_id" := "current_row"."member_id";
4424 "result"."other_trustee_participation" := TRUE;
4425 "result"."other_trustee_ellipsis" := FALSE;
4426 IF "current_row"."loop" = 'first' THEN
4427 "result"."delegation_loop" := 'other';
4428 END IF;
4429 ELSE
4430 "result"."first_trustee_ellipsis" := TRUE;
4431 IF "current_row"."loop" = 'first' THEN
4432 "result"."delegation_loop" := 'first_ellipsis';
4433 END IF;
4434 END IF;
4435 ELSE
4436 "result"."other_trustee_ellipsis" := TRUE;
4437 IF "current_row"."loop" = 'first' THEN
4438 "result"."delegation_loop" := 'other_ellipsis';
4439 END IF;
4440 END IF;
4441 END IF;
4442 END LOOP;
4443 RETURN "result";
4444 END;
4445 $$;
4447 COMMENT ON FUNCTION "delegation_info"
4448 ( "member"."id"%TYPE,
4449 "unit"."id"%TYPE,
4450 "area"."id"%TYPE,
4451 "issue"."id"%TYPE,
4452 "member"."id"%TYPE,
4453 BOOLEAN )
4454 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
4458 ---------------------------
4459 -- Transaction isolation --
4460 ---------------------------
4463 CREATE FUNCTION "require_transaction_isolation"()
4464 RETURNS VOID
4465 LANGUAGE 'plpgsql' VOLATILE AS $$
4466 BEGIN
4467 IF
4468 current_setting('transaction_isolation') NOT IN
4469 ('repeatable read', 'serializable')
4470 THEN
4471 RAISE EXCEPTION 'Insufficient transaction isolation level' USING
4472 HINT = 'Consider using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ.';
4473 END IF;
4474 RETURN;
4475 END;
4476 $$;
4478 COMMENT ON FUNCTION "require_transaction_isolation"() IS 'Throws an exception, if transaction isolation level is too low to provide a consistent snapshot';
4481 CREATE FUNCTION "dont_require_transaction_isolation"()
4482 RETURNS VOID
4483 LANGUAGE 'plpgsql' VOLATILE AS $$
4484 BEGIN
4485 IF
4486 current_setting('transaction_isolation') IN
4487 ('repeatable read', 'serializable')
4488 THEN
4489 RAISE WARNING 'Unneccessary transaction isolation level: %',
4490 current_setting('transaction_isolation');
4491 END IF;
4492 RETURN;
4493 END;
4494 $$;
4496 COMMENT ON FUNCTION "dont_require_transaction_isolation"() IS 'Raises a warning, if transaction isolation level is higher than READ COMMITTED';
4500 -------------------------
4501 -- Notification system --
4502 -------------------------
4504 CREATE FUNCTION "get_initiatives_for_notification"
4505 ( "recipient_id_p" "member"."id"%TYPE )
4506 RETURNS SETOF "initiative_for_notification"
4507 LANGUAGE 'plpgsql' VOLATILE AS $$
4508 DECLARE
4509 "result_row" "initiative_for_notification"%ROWTYPE;
4510 "last_draft_id_v" "draft"."id"%TYPE;
4511 "last_suggestion_id_v" "suggestion"."id"%TYPE;
4512 BEGIN
4513 PERFORM "require_transaction_isolation"();
4514 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
4515 FOR "result_row" IN
4516 SELECT * FROM "initiative_for_notification"
4517 WHERE "recipient_id" = "recipient_id_p"
4518 LOOP
4519 SELECT "id" INTO "last_draft_id_v" FROM "draft"
4520 WHERE "draft"."initiative_id" = "result_row"."initiative_id"
4521 ORDER BY "id" DESC LIMIT 1;
4522 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
4523 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
4524 ORDER BY "id" DESC LIMIT 1;
4525 INSERT INTO "notification_initiative_sent"
4526 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
4527 VALUES (
4528 "recipient_id_p",
4529 "result_row"."initiative_id",
4530 "last_draft_id_v",
4531 "last_suggestion_id_v" )
4532 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
4533 "last_draft_id" = "last_draft_id_v",
4534 "last_suggestion_id" = "last_suggestion_id_v";
4535 RETURN NEXT "result_row";
4536 END LOOP;
4537 DELETE FROM "notification_initiative_sent"
4538 USING "initiative", "issue"
4539 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
4540 AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
4541 AND "issue"."id" = "initiative"."issue_id"
4542 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
4543 UPDATE "member" SET
4544 "notification_counter" = "notification_counter" + 1,
4545 "notification_sent" = now()
4546 WHERE "id" = "recipient_id_p";
4547 RETURN;
4548 END;
4549 $$;
4551 COMMENT ON FUNCTION "get_initiatives_for_notification"
4552 ( "member"."id"%TYPE )
4553 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';
4557 ------------------------------------------------------------------------
4558 -- Regular tasks, except calculcation of snapshots and voting results --
4559 ------------------------------------------------------------------------
4562 CREATE FUNCTION "check_activity"()
4563 RETURNS VOID
4564 LANGUAGE 'plpgsql' VOLATILE AS $$
4565 DECLARE
4566 "system_setting_row" "system_setting"%ROWTYPE;
4567 BEGIN
4568 PERFORM "dont_require_transaction_isolation"();
4569 SELECT * INTO "system_setting_row" FROM "system_setting";
4570 IF "system_setting_row"."member_ttl" NOTNULL THEN
4571 UPDATE "member" SET "active" = FALSE
4572 WHERE "active" = TRUE
4573 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
4574 END IF;
4575 RETURN;
4576 END;
4577 $$;
4579 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
4582 CREATE FUNCTION "calculate_member_counts"()
4583 RETURNS VOID
4584 LANGUAGE 'plpgsql' VOLATILE AS $$
4585 BEGIN
4586 PERFORM "require_transaction_isolation"();
4587 DELETE FROM "member_count";
4588 INSERT INTO "member_count" ("total_count")
4589 SELECT "total_count" FROM "member_count_view";
4590 UPDATE "unit" SET "member_count" = "view"."member_count"
4591 FROM "unit_member_count" AS "view"
4592 WHERE "view"."unit_id" = "unit"."id";
4593 RETURN;
4594 END;
4595 $$;
4597 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 "unit_member_count"';
4600 CREATE FUNCTION "calculate_area_quorum"()
4601 RETURNS VOID
4602 LANGUAGE 'plpgsql' VOLATILE AS $$
4603 BEGIN
4604 PERFORM "dont_require_transaction_isolation"();
4605 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
4606 FROM "area_quorum" AS "view"
4607 WHERE "view"."area_id" = "area"."id";
4608 RETURN;
4609 END;
4610 $$;
4612 COMMENT ON FUNCTION "calculate_area_quorum"() IS 'Calculate column "issue_quorum" in table "area" from view "area_quorum"';
4616 ------------------------------------
4617 -- Calculation of harmonic weight --
4618 ------------------------------------
4621 CREATE VIEW "remaining_harmonic_supporter_weight" AS
4622 SELECT
4623 "direct_interest_snapshot"."snapshot_id",
4624 "direct_interest_snapshot"."issue_id",
4625 "direct_interest_snapshot"."member_id",
4626 "direct_interest_snapshot"."weight" AS "weight_num",
4627 count("initiative"."id") AS "weight_den"
4628 FROM "issue"
4629 JOIN "direct_interest_snapshot"
4630 ON "issue"."latest_snapshot_id" = "direct_interest_snapshot"."snapshot_id"
4631 AND "issue"."id" = "direct_interest_snapshot"."issue_id"
4632 JOIN "initiative"
4633 ON "issue"."id" = "initiative"."issue_id"
4634 AND "initiative"."harmonic_weight" ISNULL
4635 JOIN "direct_supporter_snapshot"
4636 ON "issue"."latest_snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
4637 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
4638 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
4639 AND (
4640 "direct_supporter_snapshot"."satisfied" = TRUE OR
4641 coalesce("initiative"."admitted", FALSE) = FALSE
4642 )
4643 GROUP BY
4644 "direct_interest_snapshot"."snapshot_id",
4645 "direct_interest_snapshot"."issue_id",
4646 "direct_interest_snapshot"."member_id",
4647 "direct_interest_snapshot"."weight";
4649 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
4652 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
4653 SELECT
4654 "initiative"."issue_id",
4655 "initiative"."id" AS "initiative_id",
4656 "initiative"."admitted",
4657 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
4658 "remaining_harmonic_supporter_weight"."weight_den"
4659 FROM "remaining_harmonic_supporter_weight"
4660 JOIN "initiative"
4661 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
4662 AND "initiative"."harmonic_weight" ISNULL
4663 JOIN "direct_supporter_snapshot"
4664 ON "remaining_harmonic_supporter_weight"."snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
4665 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
4666 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
4667 AND (
4668 "direct_supporter_snapshot"."satisfied" = TRUE OR
4669 coalesce("initiative"."admitted", FALSE) = FALSE
4670 )
4671 GROUP BY
4672 "initiative"."issue_id",
4673 "initiative"."id",
4674 "initiative"."admitted",
4675 "remaining_harmonic_supporter_weight"."weight_den";
4677 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
4680 CREATE VIEW "remaining_harmonic_initiative_weight_dummies" AS
4681 SELECT
4682 "issue_id",
4683 "id" AS "initiative_id",
4684 "admitted",
4685 0 AS "weight_num",
4686 1 AS "weight_den"
4687 FROM "initiative"
4688 WHERE "harmonic_weight" ISNULL;
4690 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';
4693 CREATE FUNCTION "set_harmonic_initiative_weights"
4694 ( "issue_id_p" "issue"."id"%TYPE )
4695 RETURNS VOID
4696 LANGUAGE 'plpgsql' VOLATILE AS $$
4697 DECLARE
4698 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
4699 "i" INT4;
4700 "count_v" INT4;
4701 "summand_v" FLOAT;
4702 "id_ary" INT4[];
4703 "weight_ary" FLOAT[];
4704 "min_weight_v" FLOAT;
4705 BEGIN
4706 PERFORM "require_transaction_isolation"();
4707 UPDATE "initiative" SET "harmonic_weight" = NULL
4708 WHERE "issue_id" = "issue_id_p";
4709 LOOP
4710 "min_weight_v" := NULL;
4711 "i" := 0;
4712 "count_v" := 0;
4713 FOR "weight_row" IN
4714 SELECT * FROM "remaining_harmonic_initiative_weight_summands"
4715 WHERE "issue_id" = "issue_id_p"
4716 AND (
4717 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
4718 SELECT NULL FROM "initiative"
4719 WHERE "issue_id" = "issue_id_p"
4720 AND "harmonic_weight" ISNULL
4721 AND coalesce("admitted", FALSE) = FALSE
4722 )
4723 )
4724 UNION ALL -- needed for corner cases
4725 SELECT * FROM "remaining_harmonic_initiative_weight_dummies"
4726 WHERE "issue_id" = "issue_id_p"
4727 AND (
4728 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
4729 SELECT NULL FROM "initiative"
4730 WHERE "issue_id" = "issue_id_p"
4731 AND "harmonic_weight" ISNULL
4732 AND coalesce("admitted", FALSE) = FALSE
4733 )
4734 )
4735 ORDER BY "initiative_id" DESC, "weight_den" DESC
4736 -- NOTE: non-admitted initiatives placed first (at last positions),
4737 -- latest initiatives treated worse in case of tie
4738 LOOP
4739 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
4740 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
4741 "i" := "i" + 1;
4742 "count_v" := "i";
4743 "id_ary"["i"] := "weight_row"."initiative_id";
4744 "weight_ary"["i"] := "summand_v";
4745 ELSE
4746 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
4747 END IF;
4748 END LOOP;
4749 EXIT WHEN "count_v" = 0;
4750 "i" := 1;
4751 LOOP
4752 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
4753 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
4754 "min_weight_v" := "weight_ary"["i"];
4755 END IF;
4756 "i" := "i" + 1;
4757 EXIT WHEN "i" > "count_v";
4758 END LOOP;
4759 "i" := 1;
4760 LOOP
4761 IF "weight_ary"["i"] = "min_weight_v" THEN
4762 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
4763 WHERE "id" = "id_ary"["i"];
4764 EXIT;
4765 END IF;
4766 "i" := "i" + 1;
4767 END LOOP;
4768 END LOOP;
4769 UPDATE "initiative" SET "harmonic_weight" = 0
4770 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
4771 END;
4772 $$;
4774 COMMENT ON FUNCTION "set_harmonic_initiative_weights"
4775 ( "issue"."id"%TYPE )
4776 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
4780 ------------------------------
4781 -- Calculation of snapshots --
4782 ------------------------------
4785 CREATE FUNCTION "weight_of_added_delegations_for_snapshot"
4786 ( "snapshot_id_p" "snapshot"."id"%TYPE,
4787 "issue_id_p" "issue"."id"%TYPE,
4788 "member_id_p" "member"."id"%TYPE,
4789 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
4790 RETURNS "direct_interest_snapshot"."weight"%TYPE
4791 LANGUAGE 'plpgsql' VOLATILE AS $$
4792 DECLARE
4793 "issue_delegation_row" "issue_delegation"%ROWTYPE;
4794 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
4795 "weight_v" INT4;
4796 "sub_weight_v" INT4;
4797 BEGIN
4798 PERFORM "require_transaction_isolation"();
4799 "weight_v" := 0;
4800 FOR "issue_delegation_row" IN
4801 SELECT * FROM "issue_delegation"
4802 WHERE "trustee_id" = "member_id_p"
4803 AND "issue_id" = "issue_id_p"
4804 LOOP
4805 IF NOT EXISTS (
4806 SELECT NULL FROM "direct_interest_snapshot"
4807 WHERE "snapshot_id" = "snapshot_id_p"
4808 AND "issue_id" = "issue_id_p"
4809 AND "member_id" = "issue_delegation_row"."truster_id"
4810 ) AND NOT EXISTS (
4811 SELECT NULL FROM "delegating_interest_snapshot"
4812 WHERE "snapshot_id" = "snapshot_id_p"
4813 AND "issue_id" = "issue_id_p"
4814 AND "member_id" = "issue_delegation_row"."truster_id"
4815 ) THEN
4816 "delegate_member_ids_v" :=
4817 "member_id_p" || "delegate_member_ids_p";
4818 INSERT INTO "delegating_interest_snapshot" (
4819 "snapshot_id",
4820 "issue_id",
4821 "member_id",
4822 "scope",
4823 "delegate_member_ids"
4824 ) VALUES (
4825 "snapshot_id_p",
4826 "issue_id_p",
4827 "issue_delegation_row"."truster_id",
4828 "issue_delegation_row"."scope",
4829 "delegate_member_ids_v"
4830 );
4831 "sub_weight_v" := 1 +
4832 "weight_of_added_delegations_for_snapshot"(
4833 "snapshot_id_p",
4834 "issue_id_p",
4835 "issue_delegation_row"."truster_id",
4836 "delegate_member_ids_v"
4837 );
4838 UPDATE "delegating_interest_snapshot"
4839 SET "weight" = "sub_weight_v"
4840 WHERE "snapshot_id" = "snapshot_id_p"
4841 AND "issue_id" = "issue_id_p"
4842 AND "member_id" = "issue_delegation_row"."truster_id";
4843 "weight_v" := "weight_v" + "sub_weight_v";
4844 END IF;
4845 END LOOP;
4846 RETURN "weight_v";
4847 END;
4848 $$;
4850 COMMENT ON FUNCTION "weight_of_added_delegations_for_snapshot"
4851 ( "snapshot"."id"%TYPE,
4852 "issue"."id"%TYPE,
4853 "member"."id"%TYPE,
4854 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
4855 IS 'Helper function for "fill_snapshot" function';
4858 CREATE FUNCTION "take_snapshot"
4859 ( "issue_id_p" "issue"."id"%TYPE,
4860 "area_id_p" "area"."id"%TYPE = NULL )
4861 RETURNS "snapshot"."id"%TYPE
4862 LANGUAGE 'plpgsql' VOLATILE AS $$
4863 DECLARE
4864 "area_id_v" "area"."id"%TYPE;
4865 "unit_id_v" "unit"."id"%TYPE;
4866 "snapshot_id_v" "snapshot"."id"%TYPE;
4867 "issue_id_v" "issue"."id"%TYPE;
4868 "member_id_v" "member"."id"%TYPE;
4869 BEGIN
4870 IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN
4871 RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL';
4872 END IF;
4873 PERFORM "require_transaction_isolation"();
4874 IF "issue_id_p" ISNULL THEN
4875 "area_id_v" := "area_id_p";
4876 ELSE
4877 SELECT "area_id" INTO "area_id_v"
4878 FROM "issue" WHERE "id" = "issue_id_p";
4879 END IF;
4880 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_p";
4881 INSERT INTO "snapshot" ("area_id", "issue_id")
4882 VALUES ("area_id_v", "issue_id_p")
4883 RETURNING "id" INTO "snapshot_id_v";
4884 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
4885 SELECT "snapshot_id_v", "member_id"
4886 FROM "unit_member" WHERE "unit_id" = "unit_id_v";
4887 UPDATE "snapshot" SET
4888 "population" = (
4889 SELECT count(1) FROM "snapshot_population"
4890 WHERE "snapshot_id" = "snapshot_id_v"
4891 ) WHERE "id" = "snapshot_id_v";
4892 FOR "issue_id_v" IN
4893 SELECT "id" FROM "issue"
4894 WHERE CASE WHEN "issue_id_p" ISNULL THEN
4895 "area_id" = "area_id_p" AND
4896 "state" = 'admission'
4897 ELSE
4898 "id" = "issue_id_p"
4899 END
4900 LOOP
4901 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
4902 VALUES ("snapshot_id_v", "issue_id_v");
4903 INSERT INTO "direct_interest_snapshot"
4904 ("snapshot_id", "issue_id", "member_id")
4905 SELECT
4906 "snapshot_id_v" AS "snapshot_id",
4907 "issue_id_v" AS "issue_id",
4908 "member"."id" AS "member_id"
4909 FROM "issue"
4910 JOIN "area" ON "issue"."area_id" = "area"."id"
4911 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
4912 JOIN "member" ON "interest"."member_id" = "member"."id"
4913 JOIN "privilege"
4914 ON "privilege"."unit_id" = "area"."unit_id"
4915 AND "privilege"."member_id" = "member"."id"
4916 WHERE "issue"."id" = "issue_id_v"
4917 AND "member"."active" AND "privilege"."voting_right";
4918 FOR "member_id_v" IN
4919 SELECT "member_id" FROM "direct_interest_snapshot"
4920 WHERE "snapshot_id" = "snapshot_id_v"
4921 AND "issue_id" = "issue_id_v"
4922 LOOP
4923 UPDATE "direct_interest_snapshot" SET
4924 "weight" = 1 +
4925 "weight_of_added_delegations_for_snapshot"(
4926 "snapshot_id_v",
4927 "issue_id_v",
4928 "member_id_v",
4929 '{}'
4930 )
4931 WHERE "snapshot_id" = "snapshot_id_v"
4932 AND "issue_id" = "issue_id_v"
4933 AND "member_id" = "member_id_v";
4934 END LOOP;
4935 INSERT INTO "direct_supporter_snapshot"
4936 ( "snapshot_id", "issue_id", "initiative_id", "member_id",
4937 "draft_id", "informed", "satisfied" )
4938 SELECT
4939 "snapshot_id_v" AS "snapshot_id",
4940 "issue_id_v" AS "issue_id",
4941 "initiative"."id" AS "initiative_id",
4942 "supporter"."member_id" AS "member_id",
4943 "supporter"."draft_id" AS "draft_id",
4944 "supporter"."draft_id" = "current_draft"."id" AS "informed",
4945 NOT EXISTS (
4946 SELECT NULL FROM "critical_opinion"
4947 WHERE "initiative_id" = "initiative"."id"
4948 AND "member_id" = "supporter"."member_id"
4949 ) AS "satisfied"
4950 FROM "initiative"
4951 JOIN "supporter"
4952 ON "supporter"."initiative_id" = "initiative"."id"
4953 JOIN "current_draft"
4954 ON "initiative"."id" = "current_draft"."initiative_id"
4955 JOIN "direct_interest_snapshot"
4956 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"
4957 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"
4958 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
4959 WHERE "initiative"."issue_id" = "issue_id_v";
4960 DELETE FROM "temporary_suggestion_counts";
4961 INSERT INTO "temporary_suggestion_counts"
4962 ( "id",
4963 "minus2_unfulfilled_count", "minus2_fulfilled_count",
4964 "minus1_unfulfilled_count", "minus1_fulfilled_count",
4965 "plus1_unfulfilled_count", "plus1_fulfilled_count",
4966 "plus2_unfulfilled_count", "plus2_fulfilled_count" )
4967 SELECT
4968 "suggestion"."id",
4969 ( SELECT coalesce(sum("di"."weight"), 0)
4970 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
4971 ON "di"."snapshot_id" = "snapshot_id_v"
4972 AND "di"."issue_id" = "issue_id_v"
4973 AND "di"."member_id" = "opinion"."member_id"
4974 WHERE "opinion"."suggestion_id" = "suggestion"."id"
4975 AND "opinion"."degree" = -2
4976 AND "opinion"."fulfilled" = FALSE
4977 ) AS "minus2_unfulfilled_count",
4978 ( SELECT coalesce(sum("di"."weight"), 0)
4979 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
4980 ON "di"."snapshot_id" = "snapshot_id_v"
4981 AND "di"."issue_id" = "issue_id_v"
4982 AND "di"."member_id" = "opinion"."member_id"
4983 WHERE "opinion"."suggestion_id" = "suggestion"."id"
4984 AND "opinion"."degree" = -2
4985 AND "opinion"."fulfilled" = TRUE
4986 ) AS "minus2_fulfilled_count",
4987 ( SELECT coalesce(sum("di"."weight"), 0)
4988 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
4989 ON "di"."snapshot_id" = "snapshot_id_v"
4990 AND "di"."issue_id" = "issue_id_v"
4991 AND "di"."member_id" = "opinion"."member_id"
4992 WHERE "opinion"."suggestion_id" = "suggestion"."id"
4993 AND "opinion"."degree" = -1
4994 AND "opinion"."fulfilled" = FALSE
4995 ) AS "minus1_unfulfilled_count",
4996 ( SELECT coalesce(sum("di"."weight"), 0)
4997 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
4998 ON "di"."snapshot_id" = "snapshot_id_v"
4999 AND "di"."issue_id" = "issue_id_v"
5000 AND "di"."member_id" = "opinion"."member_id"
5001 WHERE "opinion"."suggestion_id" = "suggestion"."id"
5002 AND "opinion"."degree" = -1
5003 AND "opinion"."fulfilled" = TRUE
5004 ) AS "minus1_fulfilled_count",
5005 ( SELECT coalesce(sum("di"."weight"), 0)
5006 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
5007 ON "di"."snapshot_id" = "snapshot_id_v"
5008 AND "di"."issue_id" = "issue_id_v"
5009 AND "di"."member_id" = "opinion"."member_id"
5010 WHERE "opinion"."suggestion_id" = "suggestion"."id"
5011 AND "opinion"."degree" = 1
5012 AND "opinion"."fulfilled" = FALSE
5013 ) AS "plus1_unfulfilled_count",
5014 ( SELECT coalesce(sum("di"."weight"), 0)
5015 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
5016 ON "di"."snapshot_id" = "snapshot_id_v"
5017 AND "di"."issue_id" = "issue_id_v"
5018 AND "di"."member_id" = "opinion"."member_id"
5019 WHERE "opinion"."suggestion_id" = "suggestion"."id"
5020 AND "opinion"."degree" = 1
5021 AND "opinion"."fulfilled" = TRUE
5022 ) AS "plus1_fulfilled_count",
5023 ( SELECT coalesce(sum("di"."weight"), 0)
5024 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
5025 ON "di"."snapshot_id" = "snapshot_id_v"
5026 AND "di"."issue_id" = "issue_id_v"
5027 AND "di"."member_id" = "opinion"."member_id"
5028 WHERE "opinion"."suggestion_id" = "suggestion"."id"
5029 AND "opinion"."degree" = 2
5030 AND "opinion"."fulfilled" = FALSE
5031 ) AS "plus2_unfulfilled_count",
5032 ( SELECT coalesce(sum("di"."weight"), 0)
5033 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
5034 ON "di"."snapshot_id" = "snapshot_id_v"
5035 AND "di"."issue_id" = "issue_id_v"
5036 AND "di"."member_id" = "opinion"."member_id"
5037 WHERE "opinion"."suggestion_id" = "suggestion"."id"
5038 AND "opinion"."degree" = 2
5039 AND "opinion"."fulfilled" = TRUE
5040 ) AS "plus2_fulfilled_count"
5041 FROM "suggestion" JOIN "initiative"
5042 ON "suggestion"."initiative_id" = "initiative"."id"
5043 WHERE "initiative"."issue_id" = "issue_id_v";
5044 END LOOP;
5045 RETURN "snapshot_id_v";
5046 END;
5047 $$;
5049 COMMENT ON FUNCTION "take_snapshot"
5050 ( "issue"."id"%TYPE,
5051 "area"."id"%TYPE )
5052 IS 'This function creates a new interest/supporter snapshot of a particular issue, or, if the first argument is NULL, for all issues in ''admission'' phase of the area given as second argument. It must be executed with TRANSACTION ISOLATION LEVEL REPEATABLE READ. The snapshot must later be finished by calling "finish_snapshot" for every issue.';
5055 CREATE FUNCTION "finish_snapshot"
5056 ( "issue_id_p" "issue"."id"%TYPE )
5057 RETURNS VOID
5058 LANGUAGE 'plpgsql' VOLATILE AS $$
5059 DECLARE
5060 "snapshot_id_v" "snapshot"."id"%TYPE;
5061 BEGIN
5062 -- NOTE: function does not require snapshot isolation but we don't call
5063 -- "dont_require_snapshot_isolation" here because this function is
5064 -- also invoked by "check_issue"
5065 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
5066 SELECT "id" INTO "snapshot_id_v" FROM "snapshot"
5067 ORDER BY "id" DESC LIMIT 1;
5068 UPDATE "issue" SET
5069 "calculated" = "snapshot"."calculated",
5070 "latest_snapshot_id" = "snapshot_id_v",
5071 "population" = "snapshot"."population"
5072 FROM "snapshot"
5073 WHERE "issue"."id" = "issue_id_p"
5074 AND "snapshot"."id" = "snapshot_id_v";
5075 UPDATE "initiative" SET
5076 "supporter_count" = (
5077 SELECT coalesce(sum("di"."weight"), 0)
5078 FROM "direct_interest_snapshot" AS "di"
5079 JOIN "direct_supporter_snapshot" AS "ds"
5080 ON "di"."member_id" = "ds"."member_id"
5081 WHERE "di"."snapshot_id" = "snapshot_id_v"
5082 AND "di"."issue_id" = "issue_id_p"
5083 AND "ds"."snapshot_id" = "snapshot_id_v"
5084 AND "ds"."initiative_id" = "initiative"."id"
5085 ),
5086 "informed_supporter_count" = (
5087 SELECT coalesce(sum("di"."weight"), 0)
5088 FROM "direct_interest_snapshot" AS "di"
5089 JOIN "direct_supporter_snapshot" AS "ds"
5090 ON "di"."member_id" = "ds"."member_id"
5091 WHERE "di"."snapshot_id" = "snapshot_id_v"
5092 AND "di"."issue_id" = "issue_id_p"
5093 AND "ds"."snapshot_id" = "snapshot_id_v"
5094 AND "ds"."initiative_id" = "initiative"."id"
5095 AND "ds"."informed"
5096 ),
5097 "satisfied_supporter_count" = (
5098 SELECT coalesce(sum("di"."weight"), 0)
5099 FROM "direct_interest_snapshot" AS "di"
5100 JOIN "direct_supporter_snapshot" AS "ds"
5101 ON "di"."member_id" = "ds"."member_id"
5102 WHERE "di"."snapshot_id" = "snapshot_id_v"
5103 AND "di"."issue_id" = "issue_id_p"
5104 AND "ds"."snapshot_id" = "snapshot_id_v"
5105 AND "ds"."initiative_id" = "initiative"."id"
5106 AND "ds"."satisfied"
5107 ),
5108 "satisfied_informed_supporter_count" = (
5109 SELECT coalesce(sum("di"."weight"), 0)
5110 FROM "direct_interest_snapshot" AS "di"
5111 JOIN "direct_supporter_snapshot" AS "ds"
5112 ON "di"."member_id" = "ds"."member_id"
5113 WHERE "di"."snapshot_id" = "snapshot_id_v"
5114 AND "di"."issue_id" = "issue_id_p"
5115 AND "ds"."snapshot_id" = "snapshot_id_v"
5116 AND "ds"."initiative_id" = "initiative"."id"
5117 AND "ds"."informed"
5118 AND "ds"."satisfied"
5119 )
5120 WHERE "issue_id" = "issue_id_p";
5121 UPDATE "suggestion" SET
5122 "minus2_unfulfilled_count" = "temp"."minus2_unfulfilled_count",
5123 "minus2_fulfilled_count" = "temp"."minus2_fulfilled_count",
5124 "minus1_unfulfilled_count" = "temp"."minus1_unfulfilled_count",
5125 "minus1_fulfilled_count" = "temp"."minus1_fulfilled_count",
5126 "plus1_unfulfilled_count" = "temp"."plus1_unfulfilled_count",
5127 "plus1_fulfilled_count" = "temp"."plus1_fulfilled_count",
5128 "plus2_unfulfilled_count" = "temp"."plus2_unfulfilled_count",
5129 "plus2_fulfilled_count" = "temp"."plus2_fulfilled_count"
5130 FROM "temporary_suggestion_counts" AS "temp", "initiative"
5131 WHERE "temp"."id" = "suggestion"."id"
5132 AND "initiative"."issue_id" = "issue_id_p"
5133 AND "suggestion"."initiative_id" = "initiative"."id";
5134 DELETE FROM "temporary_suggestion_counts";
5135 RETURN;
5136 END;
5137 $$;
5139 COMMENT ON FUNCTION "finish_snapshot"
5140 ( "issue"."id"%TYPE )
5141 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)';
5145 -----------------------
5146 -- Counting of votes --
5147 -----------------------
5150 CREATE FUNCTION "weight_of_added_vote_delegations"
5151 ( "issue_id_p" "issue"."id"%TYPE,
5152 "member_id_p" "member"."id"%TYPE,
5153 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
5154 RETURNS "direct_voter"."weight"%TYPE
5155 LANGUAGE 'plpgsql' VOLATILE AS $$
5156 DECLARE
5157 "issue_delegation_row" "issue_delegation"%ROWTYPE;
5158 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
5159 "weight_v" INT4;
5160 "sub_weight_v" INT4;
5161 BEGIN
5162 PERFORM "require_transaction_isolation"();
5163 "weight_v" := 0;
5164 FOR "issue_delegation_row" IN
5165 SELECT * FROM "issue_delegation"
5166 WHERE "trustee_id" = "member_id_p"
5167 AND "issue_id" = "issue_id_p"
5168 LOOP
5169 IF NOT EXISTS (
5170 SELECT NULL FROM "direct_voter"
5171 WHERE "member_id" = "issue_delegation_row"."truster_id"
5172 AND "issue_id" = "issue_id_p"
5173 ) AND NOT EXISTS (
5174 SELECT NULL FROM "delegating_voter"
5175 WHERE "member_id" = "issue_delegation_row"."truster_id"
5176 AND "issue_id" = "issue_id_p"
5177 ) THEN
5178 "delegate_member_ids_v" :=
5179 "member_id_p" || "delegate_member_ids_p";
5180 INSERT INTO "delegating_voter" (
5181 "issue_id",
5182 "member_id",
5183 "scope",
5184 "delegate_member_ids"
5185 ) VALUES (
5186 "issue_id_p",
5187 "issue_delegation_row"."truster_id",
5188 "issue_delegation_row"."scope",
5189 "delegate_member_ids_v"
5190 );
5191 "sub_weight_v" := 1 +
5192 "weight_of_added_vote_delegations"(
5193 "issue_id_p",
5194 "issue_delegation_row"."truster_id",
5195 "delegate_member_ids_v"
5196 );
5197 UPDATE "delegating_voter"
5198 SET "weight" = "sub_weight_v"
5199 WHERE "issue_id" = "issue_id_p"
5200 AND "member_id" = "issue_delegation_row"."truster_id";
5201 "weight_v" := "weight_v" + "sub_weight_v";
5202 END IF;
5203 END LOOP;
5204 RETURN "weight_v";
5205 END;
5206 $$;
5208 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
5209 ( "issue"."id"%TYPE,
5210 "member"."id"%TYPE,
5211 "delegating_voter"."delegate_member_ids"%TYPE )
5212 IS 'Helper function for "add_vote_delegations" function';
5215 CREATE FUNCTION "add_vote_delegations"
5216 ( "issue_id_p" "issue"."id"%TYPE )
5217 RETURNS VOID
5218 LANGUAGE 'plpgsql' VOLATILE AS $$
5219 DECLARE
5220 "member_id_v" "member"."id"%TYPE;
5221 BEGIN
5222 PERFORM "require_transaction_isolation"();
5223 FOR "member_id_v" IN
5224 SELECT "member_id" FROM "direct_voter"
5225 WHERE "issue_id" = "issue_id_p"
5226 LOOP
5227 UPDATE "direct_voter" SET
5228 "weight" = "weight" + "weight_of_added_vote_delegations"(
5229 "issue_id_p",
5230 "member_id_v",
5231 '{}'
5232 )
5233 WHERE "member_id" = "member_id_v"
5234 AND "issue_id" = "issue_id_p";
5235 END LOOP;
5236 RETURN;
5237 END;
5238 $$;
5240 COMMENT ON FUNCTION "add_vote_delegations"
5241 ( "issue_id_p" "issue"."id"%TYPE )
5242 IS 'Helper function for "close_voting" function';
5245 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
5246 RETURNS VOID
5247 LANGUAGE 'plpgsql' VOLATILE AS $$
5248 DECLARE
5249 "area_id_v" "area"."id"%TYPE;
5250 "unit_id_v" "unit"."id"%TYPE;
5251 "member_id_v" "member"."id"%TYPE;
5252 BEGIN
5253 PERFORM "require_transaction_isolation"();
5254 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
5255 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
5256 -- override protection triggers:
5257 INSERT INTO "temporary_transaction_data" ("key", "value")
5258 VALUES ('override_protection_triggers', TRUE::TEXT);
5259 -- delete timestamp of voting comment:
5260 UPDATE "direct_voter" SET "comment_changed" = NULL
5261 WHERE "issue_id" = "issue_id_p";
5262 -- delete delegating votes (in cases of manual reset of issue state):
5263 DELETE FROM "delegating_voter"
5264 WHERE "issue_id" = "issue_id_p";
5265 -- delete votes from non-privileged voters:
5266 DELETE FROM "direct_voter"
5267 USING (
5268 SELECT
5269 "direct_voter"."member_id"
5270 FROM "direct_voter"
5271 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
5272 LEFT JOIN "privilege"
5273 ON "privilege"."unit_id" = "unit_id_v"
5274 AND "privilege"."member_id" = "direct_voter"."member_id"
5275 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
5276 "member"."active" = FALSE OR
5277 "privilege"."voting_right" ISNULL OR
5278 "privilege"."voting_right" = FALSE
5279 )
5280 ) AS "subquery"
5281 WHERE "direct_voter"."issue_id" = "issue_id_p"
5282 AND "direct_voter"."member_id" = "subquery"."member_id";
5283 -- consider delegations:
5284 UPDATE "direct_voter" SET "weight" = 1
5285 WHERE "issue_id" = "issue_id_p";
5286 PERFORM "add_vote_delegations"("issue_id_p");
5287 -- mark first preferences:
5288 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
5289 FROM (
5290 SELECT
5291 "vote"."initiative_id",
5292 "vote"."member_id",
5293 CASE WHEN "vote"."grade" > 0 THEN
5294 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
5295 ELSE NULL
5296 END AS "first_preference"
5297 FROM "vote"
5298 JOIN "initiative" -- NOTE: due to missing index on issue_id
5299 ON "vote"."issue_id" = "initiative"."issue_id"
5300 JOIN "vote" AS "agg"
5301 ON "initiative"."id" = "agg"."initiative_id"
5302 AND "vote"."member_id" = "agg"."member_id"
5303 GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
5304 ) AS "subquery"
5305 WHERE "vote"."issue_id" = "issue_id_p"
5306 AND "vote"."initiative_id" = "subquery"."initiative_id"
5307 AND "vote"."member_id" = "subquery"."member_id";
5308 -- finish overriding protection triggers (avoids garbage):
5309 DELETE FROM "temporary_transaction_data"
5310 WHERE "key" = 'override_protection_triggers';
5311 -- materialize battle_view:
5312 -- NOTE: "closed" column of issue must be set at this point
5313 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
5314 INSERT INTO "battle" (
5315 "issue_id",
5316 "winning_initiative_id", "losing_initiative_id",
5317 "count"
5318 ) SELECT
5319 "issue_id",
5320 "winning_initiative_id", "losing_initiative_id",
5321 "count"
5322 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
5323 -- set voter count:
5324 UPDATE "issue" SET
5325 "voter_count" = (
5326 SELECT coalesce(sum("weight"), 0)
5327 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
5328 )
5329 WHERE "id" = "issue_id_p";
5330 -- copy "positive_votes" and "negative_votes" from "battle" table:
5331 -- NOTE: "first_preference_votes" is set to a default of 0 at this step
5332 UPDATE "initiative" SET
5333 "first_preference_votes" = 0,
5334 "positive_votes" = "battle_win"."count",
5335 "negative_votes" = "battle_lose"."count"
5336 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
5337 WHERE
5338 "battle_win"."issue_id" = "issue_id_p" AND
5339 "battle_win"."winning_initiative_id" = "initiative"."id" AND
5340 "battle_win"."losing_initiative_id" ISNULL AND
5341 "battle_lose"."issue_id" = "issue_id_p" AND
5342 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
5343 "battle_lose"."winning_initiative_id" ISNULL;
5344 -- calculate "first_preference_votes":
5345 -- NOTE: will only set values not equal to zero
5346 UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum"
5347 FROM (
5348 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
5349 FROM "vote" JOIN "direct_voter"
5350 ON "vote"."issue_id" = "direct_voter"."issue_id"
5351 AND "vote"."member_id" = "direct_voter"."member_id"
5352 WHERE "vote"."first_preference"
5353 GROUP BY "vote"."initiative_id"
5354 ) AS "subquery"
5355 WHERE "initiative"."issue_id" = "issue_id_p"
5356 AND "initiative"."admitted"
5357 AND "initiative"."id" = "subquery"."initiative_id";
5358 END;
5359 $$;
5361 COMMENT ON FUNCTION "close_voting"
5362 ( "issue"."id"%TYPE )
5363 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.';
5366 CREATE FUNCTION "defeat_strength"
5367 ( "positive_votes_p" INT4,
5368 "negative_votes_p" INT4,
5369 "defeat_strength_p" "defeat_strength" )
5370 RETURNS INT8
5371 LANGUAGE 'plpgsql' IMMUTABLE AS $$
5372 BEGIN
5373 IF "defeat_strength_p" = 'simple'::"defeat_strength" THEN
5374 IF "positive_votes_p" > "negative_votes_p" THEN
5375 RETURN "positive_votes_p";
5376 ELSE
5377 RETURN 0;
5378 END IF;
5379 ELSE
5380 IF "positive_votes_p" > "negative_votes_p" THEN
5381 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
5382 ELSIF "positive_votes_p" = "negative_votes_p" THEN
5383 RETURN 0;
5384 ELSE
5385 RETURN -1;
5386 END IF;
5387 END IF;
5388 END;
5389 $$;
5391 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")';
5394 CREATE FUNCTION "secondary_link_strength"
5395 ( "initiative1_ord_p" INT4,
5396 "initiative2_ord_p" INT4,
5397 "tie_breaking_p" "tie_breaking" )
5398 RETURNS INT8
5399 LANGUAGE 'plpgsql' IMMUTABLE AS $$
5400 BEGIN
5401 IF "initiative1_ord_p" = "initiative2_ord_p" THEN
5402 RAISE EXCEPTION 'Identical initiative ids passed to "secondary_link_strength" function (should not happen)';
5403 END IF;
5404 RETURN (
5405 CASE WHEN "tie_breaking_p" = 'simple'::"tie_breaking" THEN
5406 0
5407 ELSE
5408 CASE WHEN "initiative1_ord_p" < "initiative2_ord_p" THEN
5409 1::INT8 << 62
5410 ELSE 0 END
5411 +
5412 CASE WHEN "tie_breaking_p" = 'variant2'::"tie_breaking" THEN
5413 ("initiative2_ord_p"::INT8 << 31) - "initiative1_ord_p"::INT8
5414 ELSE
5415 "initiative2_ord_p"::INT8 - ("initiative1_ord_p"::INT8 << 31)
5416 END
5417 END
5418 );
5419 END;
5420 $$;
5422 COMMENT ON FUNCTION "secondary_link_strength"(INT4, INT4, "tie_breaking") IS 'Calculates a secondary criterion for the defeat strength (tie-breaking of the links)';
5425 CREATE TYPE "link_strength" AS (
5426 "primary" INT8,
5427 "secondary" INT8 );
5429 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'')';
5432 CREATE FUNCTION "find_best_paths"("matrix_d" "link_strength"[][])
5433 RETURNS "link_strength"[][]
5434 LANGUAGE 'plpgsql' IMMUTABLE AS $$
5435 DECLARE
5436 "dimension_v" INT4;
5437 "matrix_p" "link_strength"[][];
5438 "i" INT4;
5439 "j" INT4;
5440 "k" INT4;
5441 BEGIN
5442 "dimension_v" := array_upper("matrix_d", 1);
5443 "matrix_p" := "matrix_d";
5444 "i" := 1;
5445 LOOP
5446 "j" := 1;
5447 LOOP
5448 IF "i" != "j" THEN
5449 "k" := 1;
5450 LOOP
5451 IF "i" != "k" AND "j" != "k" THEN
5452 IF "matrix_p"["j"]["i"] < "matrix_p"["i"]["k"] THEN
5453 IF "matrix_p"["j"]["i"] > "matrix_p"["j"]["k"] THEN
5454 "matrix_p"["j"]["k"] := "matrix_p"["j"]["i"];
5455 END IF;
5456 ELSE
5457 IF "matrix_p"["i"]["k"] > "matrix_p"["j"]["k"] THEN
5458 "matrix_p"["j"]["k"] := "matrix_p"["i"]["k"];
5459 END IF;
5460 END IF;
5461 END IF;
5462 EXIT WHEN "k" = "dimension_v";
5463 "k" := "k" + 1;
5464 END LOOP;
5465 END IF;
5466 EXIT WHEN "j" = "dimension_v";
5467 "j" := "j" + 1;
5468 END LOOP;
5469 EXIT WHEN "i" = "dimension_v";
5470 "i" := "i" + 1;
5471 END LOOP;
5472 RETURN "matrix_p";
5473 END;
5474 $$;
5476 COMMENT ON FUNCTION "find_best_paths"("link_strength"[][]) IS 'Computes the strengths of the best beat-paths from a square matrix';
5479 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
5480 RETURNS VOID
5481 LANGUAGE 'plpgsql' VOLATILE AS $$
5482 DECLARE
5483 "issue_row" "issue"%ROWTYPE;
5484 "policy_row" "policy"%ROWTYPE;
5485 "dimension_v" INT4;
5486 "matrix_a" INT4[][]; -- absolute votes
5487 "matrix_d" "link_strength"[][]; -- defeat strength (direct)
5488 "matrix_p" "link_strength"[][]; -- defeat strength (best path)
5489 "matrix_t" "link_strength"[][]; -- defeat strength (tie-breaking)
5490 "matrix_f" BOOLEAN[][]; -- forbidden link (tie-breaking)
5491 "matrix_b" BOOLEAN[][]; -- final order (who beats who)
5492 "i" INT4;
5493 "j" INT4;
5494 "m" INT4;
5495 "n" INT4;
5496 "battle_row" "battle"%ROWTYPE;
5497 "rank_ary" INT4[];
5498 "rank_v" INT4;
5499 "initiative_id_v" "initiative"."id"%TYPE;
5500 BEGIN
5501 PERFORM "require_transaction_isolation"();
5502 SELECT * INTO "issue_row"
5503 FROM "issue" WHERE "id" = "issue_id_p";
5504 SELECT * INTO "policy_row"
5505 FROM "policy" WHERE "id" = "issue_row"."policy_id";
5506 SELECT count(1) INTO "dimension_v"
5507 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
5508 -- create "matrix_a" with absolute number of votes in pairwise
5509 -- comparison:
5510 "matrix_a" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
5511 "i" := 1;
5512 "j" := 2;
5513 FOR "battle_row" IN
5514 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
5515 ORDER BY
5516 "winning_initiative_id" NULLS FIRST,
5517 "losing_initiative_id" NULLS FIRST
5518 LOOP
5519 "matrix_a"["i"]["j"] := "battle_row"."count";
5520 IF "j" = "dimension_v" THEN
5521 "i" := "i" + 1;
5522 "j" := 1;
5523 ELSE
5524 "j" := "j" + 1;
5525 IF "j" = "i" THEN
5526 "j" := "j" + 1;
5527 END IF;
5528 END IF;
5529 END LOOP;
5530 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
5531 RAISE EXCEPTION 'Wrong battle count (should not happen)';
5532 END IF;
5533 -- store direct defeat strengths in "matrix_d" using "defeat_strength"
5534 -- and "secondary_link_strength" functions:
5535 "matrix_d" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
5536 "i" := 1;
5537 LOOP
5538 "j" := 1;
5539 LOOP
5540 IF "i" != "j" THEN
5541 "matrix_d"["i"]["j"] := (
5542 "defeat_strength"(
5543 "matrix_a"["i"]["j"],
5544 "matrix_a"["j"]["i"],
5545 "policy_row"."defeat_strength"
5546 ),
5547 "secondary_link_strength"(
5548 "i",
5549 "j",
5550 "policy_row"."tie_breaking"
5551 )
5552 )::"link_strength";
5553 END IF;
5554 EXIT WHEN "j" = "dimension_v";
5555 "j" := "j" + 1;
5556 END LOOP;
5557 EXIT WHEN "i" = "dimension_v";
5558 "i" := "i" + 1;
5559 END LOOP;
5560 -- find best paths:
5561 "matrix_p" := "find_best_paths"("matrix_d");
5562 -- create partial order:
5563 "matrix_b" := array_fill(NULL::BOOLEAN, ARRAY["dimension_v", "dimension_v"]);
5564 "i" := 1;
5565 LOOP
5566 "j" := "i" + 1;
5567 LOOP
5568 IF "i" != "j" THEN
5569 IF "matrix_p"["i"]["j"] > "matrix_p"["j"]["i"] THEN
5570 "matrix_b"["i"]["j"] := TRUE;
5571 "matrix_b"["j"]["i"] := FALSE;
5572 ELSIF "matrix_p"["i"]["j"] < "matrix_p"["j"]["i"] THEN
5573 "matrix_b"["i"]["j"] := FALSE;
5574 "matrix_b"["j"]["i"] := TRUE;
5575 END IF;
5576 END IF;
5577 EXIT WHEN "j" = "dimension_v";
5578 "j" := "j" + 1;
5579 END LOOP;
5580 EXIT WHEN "i" = "dimension_v" - 1;
5581 "i" := "i" + 1;
5582 END LOOP;
5583 -- tie-breaking by forbidding shared weakest links in beat-paths
5584 -- (unless "tie_breaking" is set to 'simple', in which case tie-breaking
5585 -- is performed later by initiative id):
5586 IF "policy_row"."tie_breaking" != 'simple'::"tie_breaking" THEN
5587 "m" := 1;
5588 LOOP
5589 "n" := "m" + 1;
5590 LOOP
5591 -- only process those candidates m and n, which are tied:
5592 IF "matrix_b"["m"]["n"] ISNULL THEN
5593 -- start with beat-paths prior tie-breaking:
5594 "matrix_t" := "matrix_p";
5595 -- start with all links allowed:
5596 "matrix_f" := array_fill(FALSE, ARRAY["dimension_v", "dimension_v"]);
5597 LOOP
5598 -- determine (and forbid) that link that is the weakest link
5599 -- in both the best path from candidate m to candidate n and
5600 -- from candidate n to candidate m:
5601 "i" := 1;
5602 <<forbid_one_link>>
5603 LOOP
5604 "j" := 1;
5605 LOOP
5606 IF "i" != "j" THEN
5607 IF "matrix_d"["i"]["j"] = "matrix_t"["m"]["n"] THEN
5608 "matrix_f"["i"]["j"] := TRUE;
5609 -- exit for performance reasons,
5610 -- as exactly one link will be found:
5611 EXIT forbid_one_link;
5612 END IF;
5613 END IF;
5614 EXIT WHEN "j" = "dimension_v";
5615 "j" := "j" + 1;
5616 END LOOP;
5617 IF "i" = "dimension_v" THEN
5618 RAISE EXCEPTION 'Did not find shared weakest link for tie-breaking (should not happen)';
5619 END IF;
5620 "i" := "i" + 1;
5621 END LOOP;
5622 -- calculate best beat-paths while ignoring forbidden links:
5623 "i" := 1;
5624 LOOP
5625 "j" := 1;
5626 LOOP
5627 IF "i" != "j" THEN
5628 "matrix_t"["i"]["j"] := CASE
5629 WHEN "matrix_f"["i"]["j"]
5630 THEN ((-1::INT8) << 63, 0)::"link_strength" -- worst possible value
5631 ELSE "matrix_d"["i"]["j"] END;
5632 END IF;
5633 EXIT WHEN "j" = "dimension_v";
5634 "j" := "j" + 1;
5635 END LOOP;
5636 EXIT WHEN "i" = "dimension_v";
5637 "i" := "i" + 1;
5638 END LOOP;
5639 "matrix_t" := "find_best_paths"("matrix_t");
5640 -- extend partial order, if tie-breaking was successful:
5641 IF "matrix_t"["m"]["n"] > "matrix_t"["n"]["m"] THEN
5642 "matrix_b"["m"]["n"] := TRUE;
5643 "matrix_b"["n"]["m"] := FALSE;
5644 EXIT;
5645 ELSIF "matrix_t"["m"]["n"] < "matrix_t"["n"]["m"] THEN
5646 "matrix_b"["m"]["n"] := FALSE;
5647 "matrix_b"["n"]["m"] := TRUE;
5648 EXIT;
5649 END IF;
5650 END LOOP;
5651 END IF;
5652 EXIT WHEN "n" = "dimension_v";
5653 "n" := "n" + 1;
5654 END LOOP;
5655 EXIT WHEN "m" = "dimension_v" - 1;
5656 "m" := "m" + 1;
5657 END LOOP;
5658 END IF;
5659 -- store a unique ranking in "rank_ary":
5660 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
5661 "rank_v" := 1;
5662 LOOP
5663 "i" := 1;
5664 <<assign_next_rank>>
5665 LOOP
5666 IF "rank_ary"["i"] ISNULL THEN
5667 "j" := 1;
5668 LOOP
5669 IF
5670 "i" != "j" AND
5671 "rank_ary"["j"] ISNULL AND
5672 ( "matrix_b"["j"]["i"] OR
5673 -- tie-breaking by "id"
5674 ( "matrix_b"["j"]["i"] ISNULL AND
5675 "j" < "i" ) )
5676 THEN
5677 -- someone else is better
5678 EXIT;
5679 END IF;
5680 IF "j" = "dimension_v" THEN
5681 -- noone is better
5682 "rank_ary"["i"] := "rank_v";
5683 EXIT assign_next_rank;
5684 END IF;
5685 "j" := "j" + 1;
5686 END LOOP;
5687 END IF;
5688 "i" := "i" + 1;
5689 IF "i" > "dimension_v" THEN
5690 RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
5691 END IF;
5692 END LOOP;
5693 EXIT WHEN "rank_v" = "dimension_v";
5694 "rank_v" := "rank_v" + 1;
5695 END LOOP;
5696 -- write preliminary results:
5697 "i" := 2; -- omit status quo with "i" = 1
5698 FOR "initiative_id_v" IN
5699 SELECT "id" FROM "initiative"
5700 WHERE "issue_id" = "issue_id_p" AND "admitted"
5701 ORDER BY "id"
5702 LOOP
5703 UPDATE "initiative" SET
5704 "direct_majority" =
5705 CASE WHEN "policy_row"."direct_majority_strict" THEN
5706 "positive_votes" * "policy_row"."direct_majority_den" >
5707 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
5708 ELSE
5709 "positive_votes" * "policy_row"."direct_majority_den" >=
5710 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
5711 END
5712 AND "positive_votes" >= "policy_row"."direct_majority_positive"
5713 AND "issue_row"."voter_count"-"negative_votes" >=
5714 "policy_row"."direct_majority_non_negative",
5715 "indirect_majority" =
5716 CASE WHEN "policy_row"."indirect_majority_strict" THEN
5717 "positive_votes" * "policy_row"."indirect_majority_den" >
5718 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
5719 ELSE
5720 "positive_votes" * "policy_row"."indirect_majority_den" >=
5721 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
5722 END
5723 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
5724 AND "issue_row"."voter_count"-"negative_votes" >=
5725 "policy_row"."indirect_majority_non_negative",
5726 "schulze_rank" = "rank_ary"["i"],
5727 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
5728 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"[1],
5729 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"[1],
5730 "reverse_beat_path" = CASE WHEN "policy_row"."defeat_strength" = 'simple'::"defeat_strength"
5731 THEN NULL
5732 ELSE "matrix_p"[1]["i"]."primary" >= 0 END,
5733 "eligible" = FALSE,
5734 "winner" = FALSE,
5735 "rank" = NULL -- NOTE: in cases of manual reset of issue state
5736 WHERE "id" = "initiative_id_v";
5737 "i" := "i" + 1;
5738 END LOOP;
5739 IF "i" != "dimension_v" + 1 THEN
5740 RAISE EXCEPTION 'Wrong winner count (should not happen)';
5741 END IF;
5742 -- take indirect majorities into account:
5743 LOOP
5744 UPDATE "initiative" SET "indirect_majority" = TRUE
5745 FROM (
5746 SELECT "new_initiative"."id" AS "initiative_id"
5747 FROM "initiative" "old_initiative"
5748 JOIN "initiative" "new_initiative"
5749 ON "new_initiative"."issue_id" = "issue_id_p"
5750 AND "new_initiative"."indirect_majority" = FALSE
5751 JOIN "battle" "battle_win"
5752 ON "battle_win"."issue_id" = "issue_id_p"
5753 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
5754 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
5755 JOIN "battle" "battle_lose"
5756 ON "battle_lose"."issue_id" = "issue_id_p"
5757 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
5758 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
5759 WHERE "old_initiative"."issue_id" = "issue_id_p"
5760 AND "old_initiative"."indirect_majority" = TRUE
5761 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
5762 "battle_win"."count" * "policy_row"."indirect_majority_den" >
5763 "policy_row"."indirect_majority_num" *
5764 ("battle_win"."count"+"battle_lose"."count")
5765 ELSE
5766 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
5767 "policy_row"."indirect_majority_num" *
5768 ("battle_win"."count"+"battle_lose"."count")
5769 END
5770 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
5771 AND "issue_row"."voter_count"-"battle_lose"."count" >=
5772 "policy_row"."indirect_majority_non_negative"
5773 ) AS "subquery"
5774 WHERE "id" = "subquery"."initiative_id";
5775 EXIT WHEN NOT FOUND;
5776 END LOOP;
5777 -- set "multistage_majority" for remaining matching initiatives:
5778 UPDATE "initiative" SET "multistage_majority" = TRUE
5779 FROM (
5780 SELECT "losing_initiative"."id" AS "initiative_id"
5781 FROM "initiative" "losing_initiative"
5782 JOIN "initiative" "winning_initiative"
5783 ON "winning_initiative"."issue_id" = "issue_id_p"
5784 AND "winning_initiative"."admitted"
5785 JOIN "battle" "battle_win"
5786 ON "battle_win"."issue_id" = "issue_id_p"
5787 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
5788 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
5789 JOIN "battle" "battle_lose"
5790 ON "battle_lose"."issue_id" = "issue_id_p"
5791 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
5792 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
5793 WHERE "losing_initiative"."issue_id" = "issue_id_p"
5794 AND "losing_initiative"."admitted"
5795 AND "winning_initiative"."schulze_rank" <
5796 "losing_initiative"."schulze_rank"
5797 AND "battle_win"."count" > "battle_lose"."count"
5798 AND (
5799 "battle_win"."count" > "winning_initiative"."positive_votes" OR
5800 "battle_lose"."count" < "losing_initiative"."negative_votes" )
5801 ) AS "subquery"
5802 WHERE "id" = "subquery"."initiative_id";
5803 -- mark eligible initiatives:
5804 UPDATE "initiative" SET "eligible" = TRUE
5805 WHERE "issue_id" = "issue_id_p"
5806 AND "initiative"."direct_majority"
5807 AND "initiative"."indirect_majority"
5808 AND "initiative"."better_than_status_quo"
5809 AND (
5810 "policy_row"."no_multistage_majority" = FALSE OR
5811 "initiative"."multistage_majority" = FALSE )
5812 AND (
5813 "policy_row"."no_reverse_beat_path" = FALSE OR
5814 coalesce("initiative"."reverse_beat_path", FALSE) = FALSE );
5815 -- mark final winner:
5816 UPDATE "initiative" SET "winner" = TRUE
5817 FROM (
5818 SELECT "id" AS "initiative_id"
5819 FROM "initiative"
5820 WHERE "issue_id" = "issue_id_p" AND "eligible"
5821 ORDER BY
5822 "schulze_rank",
5823 "id"
5824 LIMIT 1
5825 ) AS "subquery"
5826 WHERE "id" = "subquery"."initiative_id";
5827 -- write (final) ranks:
5828 "rank_v" := 1;
5829 FOR "initiative_id_v" IN
5830 SELECT "id"
5831 FROM "initiative"
5832 WHERE "issue_id" = "issue_id_p" AND "admitted"
5833 ORDER BY
5834 "winner" DESC,
5835 "eligible" DESC,
5836 "schulze_rank",
5837 "id"
5838 LOOP
5839 UPDATE "initiative" SET "rank" = "rank_v"
5840 WHERE "id" = "initiative_id_v";
5841 "rank_v" := "rank_v" + 1;
5842 END LOOP;
5843 -- set schulze rank of status quo and mark issue as finished:
5844 UPDATE "issue" SET
5845 "status_quo_schulze_rank" = "rank_ary"[1],
5846 "state" =
5847 CASE WHEN EXISTS (
5848 SELECT NULL FROM "initiative"
5849 WHERE "issue_id" = "issue_id_p" AND "winner"
5850 ) THEN
5851 'finished_with_winner'::"issue_state"
5852 ELSE
5853 'finished_without_winner'::"issue_state"
5854 END,
5855 "closed" = "phase_finished",
5856 "phase_finished" = NULL
5857 WHERE "id" = "issue_id_p";
5858 RETURN;
5859 END;
5860 $$;
5862 COMMENT ON FUNCTION "calculate_ranks"
5863 ( "issue"."id"%TYPE )
5864 IS 'Determine ranking (Votes have to be counted first)';
5868 -----------------------------
5869 -- Automatic state changes --
5870 -----------------------------
5873 CREATE FUNCTION "issue_admission"
5874 ( "area_id_p" "area"."id"%TYPE )
5875 RETURNS BOOLEAN
5876 LANGUAGE 'plpgsql' VOLATILE AS $$
5877 DECLARE
5878 "issue_id_v" "issue"."id"%TYPE;
5879 BEGIN
5880 PERFORM "dont_require_transaction_isolation"();
5881 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
5882 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
5883 FROM "area_quorum" AS "view"
5884 WHERE "area"."id" = "view"."area_id"
5885 AND "area"."id" = "area_id_p";
5886 SELECT "id" INTO "issue_id_v" FROM "issue_for_admission"
5887 WHERE "area_id" = "area_id_p";
5888 IF "issue_id_v" ISNULL THEN RETURN FALSE; END IF;
5889 UPDATE "issue" SET
5890 "admission_snapshot_id" = "latest_snapshot_id",
5891 "state" = 'discussion',
5892 "accepted" = now(),
5893 "phase_finished" = NULL
5894 WHERE "id" = "issue_id_v";
5895 RETURN TRUE;
5896 END;
5897 $$;
5899 COMMENT ON FUNCTION "issue_admission"
5900 ( "area"."id"%TYPE )
5901 IS 'Checks if an issue in the area can be admitted for further discussion; returns TRUE on success in which case the function must be called again until it returns FALSE';
5904 CREATE TYPE "check_issue_persistence" AS (
5905 "state" "issue_state",
5906 "phase_finished" BOOLEAN,
5907 "issue_revoked" BOOLEAN,
5908 "snapshot_created" BOOLEAN,
5909 "harmonic_weights_set" BOOLEAN,
5910 "closed_voting" BOOLEAN );
5912 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';
5915 CREATE FUNCTION "check_issue"
5916 ( "issue_id_p" "issue"."id"%TYPE,
5917 "persist" "check_issue_persistence" )
5918 RETURNS "check_issue_persistence"
5919 LANGUAGE 'plpgsql' VOLATILE AS $$
5920 DECLARE
5921 "issue_row" "issue"%ROWTYPE;
5922 "last_calculated_v" "snapshot"."calculated"%TYPE;
5923 "policy_row" "policy"%ROWTYPE;
5924 "initiative_row" "initiative"%ROWTYPE;
5925 "state_v" "issue_state";
5926 BEGIN
5927 PERFORM "require_transaction_isolation"();
5928 IF "persist" ISNULL THEN
5929 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
5930 FOR UPDATE;
5931 SELECT "calculated" INTO "last_calculated_v"
5932 FROM "snapshot" JOIN "snapshot_issue"
5933 ON "snapshot"."id" = "snapshot_issue"."snapshot_id"
5934 WHERE "snapshot_issue"."issue_id" = "issue_id_p";
5935 IF "issue_row"."closed" NOTNULL THEN
5936 RETURN NULL;
5937 END IF;
5938 "persist"."state" := "issue_row"."state";
5939 IF
5940 ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=
5941 "issue_row"."created" + "issue_row"."max_admission_time" ) OR
5942 ( "issue_row"."state" = 'discussion' AND now() >=
5943 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
5944 ( "issue_row"."state" = 'verification' AND now() >=
5945 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
5946 ( "issue_row"."state" = 'voting' AND now() >=
5947 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
5948 THEN
5949 "persist"."phase_finished" := TRUE;
5950 ELSE
5951 "persist"."phase_finished" := FALSE;
5952 END IF;
5953 IF
5954 NOT EXISTS (
5955 -- all initiatives are revoked
5956 SELECT NULL FROM "initiative"
5957 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
5958 ) AND (
5959 -- and issue has not been accepted yet
5960 "persist"."state" = 'admission' OR
5961 -- or verification time has elapsed
5962 ( "persist"."state" = 'verification' AND
5963 "persist"."phase_finished" ) OR
5964 -- or no initiatives have been revoked lately
5965 NOT EXISTS (
5966 SELECT NULL FROM "initiative"
5967 WHERE "issue_id" = "issue_id_p"
5968 AND now() < "revoked" + "issue_row"."verification_time"
5969 )
5970 )
5971 THEN
5972 "persist"."issue_revoked" := TRUE;
5973 ELSE
5974 "persist"."issue_revoked" := FALSE;
5975 END IF;
5976 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
5977 UPDATE "issue" SET "phase_finished" = now()
5978 WHERE "id" = "issue_row"."id";
5979 RETURN "persist";
5980 ELSIF
5981 "persist"."state" IN ('admission', 'discussion', 'verification')
5982 THEN
5983 RETURN "persist";
5984 ELSE
5985 RETURN NULL;
5986 END IF;
5987 END IF;
5988 IF
5989 "persist"."state" IN ('admission', 'discussion', 'verification') AND
5990 coalesce("persist"."snapshot_created", FALSE) = FALSE
5991 THEN
5992 IF "persist"."state" != 'admission' THEN
5993 PERFORM "take_snapshot"("issue_id_p");
5994 PERFORM "finish_snapshot"("issue_id_p");
5995 END IF;
5996 "persist"."snapshot_created" = TRUE;
5997 IF "persist"."phase_finished" THEN
5998 IF "persist"."state" = 'admission' THEN
5999 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id";
6000 ELSIF "persist"."state" = 'discussion' THEN
6001 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id";
6002 ELSIF "persist"."state" = 'verification' THEN
6003 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id";
6004 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
6005 SELECT * INTO "policy_row" FROM "policy"
6006 WHERE "id" = "issue_row"."policy_id";
6007 FOR "initiative_row" IN
6008 SELECT * FROM "initiative"
6009 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
6010 FOR UPDATE
6011 LOOP
6012 IF
6013 "initiative_row"."polling" OR (
6014 "initiative_row"."satisfied_supporter_count" >
6015 "policy_row"."initiative_quorum" AND
6016 "initiative_row"."satisfied_supporter_count" *
6017 "policy_row"."initiative_quorum_den" >=
6018 "issue_row"."population" * "policy_row"."initiative_quorum_num"
6019 )
6020 THEN
6021 UPDATE "initiative" SET "admitted" = TRUE
6022 WHERE "id" = "initiative_row"."id";
6023 ELSE
6024 UPDATE "initiative" SET "admitted" = FALSE
6025 WHERE "id" = "initiative_row"."id";
6026 END IF;
6027 END LOOP;
6028 END IF;
6029 END IF;
6030 RETURN "persist";
6031 END IF;
6032 IF
6033 "persist"."state" IN ('admission', 'discussion', 'verification') AND
6034 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
6035 THEN
6036 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
6037 "persist"."harmonic_weights_set" = TRUE;
6038 IF
6039 "persist"."phase_finished" OR
6040 "persist"."issue_revoked" OR
6041 "persist"."state" = 'admission'
6042 THEN
6043 RETURN "persist";
6044 ELSE
6045 RETURN NULL;
6046 END IF;
6047 END IF;
6048 IF "persist"."issue_revoked" THEN
6049 IF "persist"."state" = 'admission' THEN
6050 "state_v" := 'canceled_revoked_before_accepted';
6051 ELSIF "persist"."state" = 'discussion' THEN
6052 "state_v" := 'canceled_after_revocation_during_discussion';
6053 ELSIF "persist"."state" = 'verification' THEN
6054 "state_v" := 'canceled_after_revocation_during_verification';
6055 END IF;
6056 UPDATE "issue" SET
6057 "state" = "state_v",
6058 "closed" = "phase_finished",
6059 "phase_finished" = NULL
6060 WHERE "id" = "issue_id_p";
6061 RETURN NULL;
6062 END IF;
6063 IF "persist"."state" = 'admission' THEN
6064 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
6065 FOR UPDATE;
6066 IF "issue_row"."phase_finished" NOTNULL THEN
6067 UPDATE "issue" SET
6068 "state" = 'canceled_issue_not_accepted',
6069 "closed" = "phase_finished",
6070 "phase_finished" = NULL
6071 WHERE "id" = "issue_id_p";
6072 END IF;
6073 RETURN NULL;
6074 END IF;
6075 IF "persist"."phase_finished" THEN
6076 IF "persist"."state" = 'discussion' THEN
6077 UPDATE "issue" SET
6078 "state" = 'verification',
6079 "half_frozen" = "phase_finished",
6080 "phase_finished" = NULL
6081 WHERE "id" = "issue_id_p";
6082 RETURN NULL;
6083 END IF;
6084 IF "persist"."state" = 'verification' THEN
6085 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
6086 FOR UPDATE;
6087 SELECT * INTO "policy_row" FROM "policy"
6088 WHERE "id" = "issue_row"."policy_id";
6089 IF EXISTS (
6090 SELECT NULL FROM "initiative"
6091 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
6092 ) THEN
6093 UPDATE "issue" SET
6094 "state" = 'voting',
6095 "fully_frozen" = "phase_finished",
6096 "phase_finished" = NULL
6097 WHERE "id" = "issue_id_p";
6098 ELSE
6099 UPDATE "issue" SET
6100 "state" = 'canceled_no_initiative_admitted',
6101 "fully_frozen" = "phase_finished",
6102 "closed" = "phase_finished",
6103 "phase_finished" = NULL
6104 WHERE "id" = "issue_id_p";
6105 -- NOTE: The following DELETE statements have effect only when
6106 -- issue state has been manipulated
6107 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
6108 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
6109 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
6110 END IF;
6111 RETURN NULL;
6112 END IF;
6113 IF "persist"."state" = 'voting' THEN
6114 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
6115 PERFORM "close_voting"("issue_id_p");
6116 "persist"."closed_voting" = TRUE;
6117 RETURN "persist";
6118 END IF;
6119 PERFORM "calculate_ranks"("issue_id_p");
6120 RETURN NULL;
6121 END IF;
6122 END IF;
6123 RAISE WARNING 'should not happen';
6124 RETURN NULL;
6125 END;
6126 $$;
6128 COMMENT ON FUNCTION "check_issue"
6129 ( "issue"."id"%TYPE,
6130 "check_issue_persistence" )
6131 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")';
6134 CREATE FUNCTION "check_everything"()
6135 RETURNS VOID
6136 LANGUAGE 'plpgsql' VOLATILE AS $$
6137 DECLARE
6138 "area_id_v" "area"."id"%TYPE;
6139 "snapshot_id_v" "snapshot"."id"%TYPE;
6140 "issue_id_v" "issue"."id"%TYPE;
6141 "persist_v" "check_issue_persistence";
6142 BEGIN
6143 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
6144 DELETE FROM "expired_session";
6145 DELETE FROM "expired_token";
6146 DELETE FROM "expired_snapshot";
6147 PERFORM "check_activity"();
6148 PERFORM "calculate_member_counts"();
6149 FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP
6150 SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v";
6151 PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"
6152 WHERE "snapshot_id" = "snapshot_id_v";
6153 LOOP
6154 EXIT WHEN "issue_admission"("area_id_v") = FALSE;
6155 END LOOP;
6156 END LOOP;
6157 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
6158 "persist_v" := NULL;
6159 LOOP
6160 "persist_v" := "check_issue"("issue_id_v", "persist_v");
6161 EXIT WHEN "persist_v" ISNULL;
6162 END LOOP;
6163 END LOOP;
6164 RETURN;
6165 END;
6166 $$;
6168 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. For production, use lf_update binary instead';
6172 ----------------------
6173 -- Deletion of data --
6174 ----------------------
6177 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
6178 RETURNS VOID
6179 LANGUAGE 'plpgsql' VOLATILE AS $$
6180 BEGIN
6181 IF EXISTS (
6182 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
6183 ) THEN
6184 -- override protection triggers:
6185 INSERT INTO "temporary_transaction_data" ("key", "value")
6186 VALUES ('override_protection_triggers', TRUE::TEXT);
6187 -- clean data:
6188 DELETE FROM "delegating_voter"
6189 WHERE "issue_id" = "issue_id_p";
6190 DELETE FROM "direct_voter"
6191 WHERE "issue_id" = "issue_id_p";
6192 DELETE FROM "delegating_interest_snapshot"
6193 WHERE "issue_id" = "issue_id_p";
6194 DELETE FROM "direct_interest_snapshot"
6195 WHERE "issue_id" = "issue_id_p";
6196 DELETE FROM "non_voter"
6197 WHERE "issue_id" = "issue_id_p";
6198 DELETE FROM "delegation"
6199 WHERE "issue_id" = "issue_id_p";
6200 DELETE FROM "supporter"
6201 USING "initiative" -- NOTE: due to missing index on issue_id
6202 WHERE "initiative"."issue_id" = "issue_id_p"
6203 AND "supporter"."initiative_id" = "initiative_id";
6204 -- mark issue as cleaned:
6205 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
6206 -- finish overriding protection triggers (avoids garbage):
6207 DELETE FROM "temporary_transaction_data"
6208 WHERE "key" = 'override_protection_triggers';
6209 END IF;
6210 RETURN;
6211 END;
6212 $$;
6214 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
6217 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
6218 RETURNS VOID
6219 LANGUAGE 'plpgsql' VOLATILE AS $$
6220 BEGIN
6221 UPDATE "member" SET
6222 "last_login" = NULL,
6223 "last_delegation_check" = NULL,
6224 "login" = NULL,
6225 "password" = NULL,
6226 "authority" = NULL,
6227 "authority_uid" = NULL,
6228 "authority_login" = NULL,
6229 "locked" = TRUE,
6230 "active" = FALSE,
6231 "notify_email" = NULL,
6232 "notify_email_unconfirmed" = NULL,
6233 "notify_email_secret" = NULL,
6234 "notify_email_secret_expiry" = NULL,
6235 "notify_email_lock_expiry" = NULL,
6236 "disable_notifications" = TRUE,
6237 "notification_counter" = DEFAULT,
6238 "notification_sample_size" = 0,
6239 "notification_dow" = NULL,
6240 "notification_hour" = NULL,
6241 "notification_sent" = NULL,
6242 "login_recovery_expiry" = NULL,
6243 "password_reset_secret" = NULL,
6244 "password_reset_secret_expiry" = NULL,
6245 "location" = NULL
6246 WHERE "id" = "member_id_p";
6247 -- "text_search_data" is updated by triggers
6248 DELETE FROM "member_settings" WHERE "member_id" = "member_id_p";
6249 DELETE FROM "member_profile" WHERE "member_id" = "member_id_p";
6250 DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";
6251 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
6252 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
6253 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
6254 DELETE FROM "session" WHERE "member_id" = "member_id_p";
6255 DELETE FROM "member_application" WHERE "member_id" = "member_id_p";
6256 DELETE FROM "token" WHERE "member_id" = "member_id_p";
6257 DELETE FROM "subscription" WHERE "member_id" = "member_id_p";
6258 DELETE FROM "ignored_area" WHERE "member_id" = "member_id_p";
6259 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
6260 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
6261 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
6262 DELETE FROM "direct_voter" USING "issue"
6263 WHERE "direct_voter"."issue_id" = "issue"."id"
6264 AND "issue"."closed" ISNULL
6265 AND "member_id" = "member_id_p";
6266 DELETE FROM "notification_initiative_sent" WHERE "member_id" = "member_id_p";
6267 RETURN;
6268 END;
6269 $$;
6271 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)';
6274 CREATE FUNCTION "delete_private_data"()
6275 RETURNS VOID
6276 LANGUAGE 'plpgsql' VOLATILE AS $$
6277 BEGIN
6278 DELETE FROM "temporary_transaction_data";
6279 DELETE FROM "temporary_suggestion_counts";
6280 DELETE FROM "member" WHERE "activated" ISNULL;
6281 UPDATE "member" SET
6282 "invite_code" = NULL,
6283 "invite_code_expiry" = NULL,
6284 "admin_comment" = NULL,
6285 "last_login" = NULL,
6286 "last_delegation_check" = NULL,
6287 "login" = NULL,
6288 "password" = NULL,
6289 "authority" = NULL,
6290 "authority_uid" = NULL,
6291 "authority_login" = NULL,
6292 "lang" = NULL,
6293 "notify_email" = NULL,
6294 "notify_email_unconfirmed" = NULL,
6295 "notify_email_secret" = NULL,
6296 "notify_email_secret_expiry" = NULL,
6297 "notify_email_lock_expiry" = NULL,
6298 "disable_notifications" = TRUE,
6299 "notification_counter" = DEFAULT,
6300 "notification_sample_size" = 0,
6301 "notification_dow" = NULL,
6302 "notification_hour" = NULL,
6303 "notification_sent" = NULL,
6304 "login_recovery_expiry" = NULL,
6305 "password_reset_secret" = NULL,
6306 "password_reset_secret_expiry" = NULL,
6307 "location" = NULL;
6308 -- "text_search_data" is updated by triggers
6309 DELETE FROM "member_settings";
6310 DELETE FROM "member_useterms";
6311 DELETE FROM "member_profile";
6312 DELETE FROM "rendered_member_statement";
6313 DELETE FROM "member_image";
6314 DELETE FROM "contact";
6315 DELETE FROM "ignored_member";
6316 DELETE FROM "session";
6317 DELETE FROM "system_application";
6318 DELETE FROM "system_application_redirect_uri";
6319 DELETE FROM "dynamic_application_scope";
6320 DELETE FROM "member_application";
6321 DELETE FROM "token";
6322 DELETE FROM "subscription";
6323 DELETE FROM "ignored_area";
6324 DELETE FROM "ignored_initiative";
6325 DELETE FROM "non_voter";
6326 DELETE FROM "direct_voter" USING "issue"
6327 WHERE "direct_voter"."issue_id" = "issue"."id"
6328 AND "issue"."closed" ISNULL;
6329 DELETE FROM "event_processed";
6330 DELETE FROM "notification_initiative_sent";
6331 DELETE FROM "newsletter";
6332 RETURN;
6333 END;
6334 $$;
6336 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.';
6340 COMMIT;