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