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