liquid_feedback_core
view core.sql @ 549:81a35235b450
Bugfix in function "write_event_initiator_trigger"
| author | jbe |
|---|---|
| date | Tue Sep 12 16:23:12 2017 +0200 (2017-09-12) |
| parents | 1adb0ad5900c |
| children | deabd90adae8 |
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 "accepted_v" BOOLEAN = FALSE;
2208 "rejected_v" BOOLEAN = FALSE;
2209 BEGIN
2210 IF TG_OP = 'UPDATE' THEN
2211 IF
2212 OLD."initiative_id" = NEW."initiative_id" AND
2213 OLD."member_id" = NEW."member_id"
2214 THEN
2215 IF
2216 coalesce(OLD."accepted", FALSE) = coalesce(NEW."accepted", FALSE)
2217 THEN
2218 RETURN NULL;
2219 END IF;
2220 IF coalesce(NEW."accepted", FALSE) = TRUE THEN
2221 "accepted_v" := TRUE;
2222 ELSE
2223 "rejected_v" := TRUE;
2224 END IF;
2225 END IF;
2226 END IF;
2227 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "accepted_v" THEN
2228 IF coalesce(OLD."accepted", FALSE) = TRUE THEN
2229 SELECT * INTO "initiative_row" FROM "initiative"
2230 WHERE "id" = OLD."initiative_id" FOR SHARE;
2231 IF "initiative_row"."id" NOTNULL THEN
2232 SELECT * INTO "issue_row" FROM "issue"
2233 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
2234 SELECT * INTO "area_row" FROM "area"
2235 WHERE "id" = "issue_row"."area_id" FOR SHARE;
2236 INSERT INTO "event" (
2237 "event", "member_id",
2238 "unit_id", "area_id", "policy_id", "issue_id", "state",
2239 "initiative_id", "boolean_value"
2240 ) VALUES (
2241 'initiator', OLD."member_id",
2242 "area_row"."unit_id", "issue_row"."area_id",
2243 "issue_row"."policy_id",
2244 "issue_row"."id", "issue_row"."state",
2245 OLD."initiative_id", FALSE
2246 );
2247 END IF;
2248 END IF;
2249 END IF;
2250 IF TG_OP = 'UPDATE' AND NOT "rejected_v" THEN
2251 IF coalesce(NEW."accepted", FALSE) = TRUE THEN
2252 SELECT * INTO "initiative_row" FROM "initiative"
2253 WHERE "id" = NEW."initiative_id" FOR SHARE;
2254 SELECT * INTO "issue_row" FROM "issue"
2255 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
2256 SELECT * INTO "area_row" FROM "area"
2257 WHERE "id" = "issue_row"."area_id" FOR SHARE;
2258 INSERT INTO "event" (
2259 "event", "member_id",
2260 "unit_id", "area_id", "policy_id", "issue_id", "state",
2261 "initiative_id", "boolean_value"
2262 ) VALUES (
2263 'initiator', NEW."member_id",
2264 "area_row"."unit_id", "issue_row"."area_id",
2265 "issue_row"."policy_id",
2266 "issue_row"."id", "issue_row"."state",
2267 NEW."initiative_id", TRUE
2268 );
2269 END IF;
2270 END IF;
2271 RETURN NULL;
2272 END;
2273 $$;
2275 CREATE TRIGGER "write_event_initiator"
2276 AFTER UPDATE OR DELETE ON "initiator" FOR EACH ROW EXECUTE PROCEDURE
2277 "write_event_initiator_trigger"();
2279 COMMENT ON FUNCTION "write_event_initiator_trigger"() IS 'Implementation of trigger "write_event_initiator" on table "initiator"';
2280 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)';
2283 CREATE FUNCTION "write_event_support_trigger"()
2284 RETURNS TRIGGER
2285 LANGUAGE 'plpgsql' VOLATILE AS $$
2286 DECLARE
2287 "issue_row" "issue"%ROWTYPE;
2288 "area_row" "area"%ROWTYPE;
2289 BEGIN
2290 IF TG_OP = 'UPDATE' THEN
2291 IF
2292 OLD."initiative_id" = NEW."initiative_id" AND
2293 OLD."member_id" = NEW."member_id"
2294 THEN
2295 IF OLD."draft_id" != NEW."draft_id" THEN
2296 SELECT * INTO "issue_row" FROM "issue"
2297 WHERE "id" = NEW."issue_id" FOR SHARE;
2298 SELECT * INTO "area_row" FROM "area"
2299 WHERE "id" = "issue_row"."area_id" FOR SHARE;
2300 INSERT INTO "event" (
2301 "event", "member_id",
2302 "unit_id", "area_id", "policy_id", "issue_id", "state",
2303 "initiative_id", "draft_id"
2304 ) VALUES (
2305 'support_updated', NEW."member_id",
2306 "area_row"."unit_id", "issue_row"."area_id",
2307 "issue_row"."policy_id",
2308 "issue_row"."id", "issue_row"."state",
2309 NEW."initiative_id", NEW."draft_id"
2310 );
2311 END IF;
2312 RETURN NULL;
2313 END IF;
2314 END IF;
2315 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
2316 IF EXISTS (
2317 SELECT NULL FROM "initiative" WHERE "id" = OLD."initiative_id"
2318 FOR SHARE
2319 ) THEN
2320 SELECT * INTO "issue_row" FROM "issue"
2321 WHERE "id" = OLD."issue_id" FOR SHARE;
2322 SELECT * INTO "area_row" FROM "area"
2323 WHERE "id" = "issue_row"."area_id" FOR SHARE;
2324 INSERT INTO "event" (
2325 "event", "member_id",
2326 "unit_id", "area_id", "policy_id", "issue_id", "state",
2327 "initiative_id", "boolean_value"
2328 ) VALUES (
2329 'support', OLD."member_id",
2330 "area_row"."unit_id", "issue_row"."area_id",
2331 "issue_row"."policy_id",
2332 "issue_row"."id", "issue_row"."state",
2333 OLD."initiative_id", FALSE
2334 );
2335 END IF;
2336 END IF;
2337 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2338 SELECT * INTO "issue_row" FROM "issue"
2339 WHERE "id" = NEW."issue_id" FOR SHARE;
2340 SELECT * INTO "area_row" FROM "area"
2341 WHERE "id" = "issue_row"."area_id" FOR SHARE;
2342 INSERT INTO "event" (
2343 "event", "member_id",
2344 "unit_id", "area_id", "policy_id", "issue_id", "state",
2345 "initiative_id", "draft_id", "boolean_value"
2346 ) VALUES (
2347 'support', NEW."member_id",
2348 "area_row"."unit_id", "issue_row"."area_id",
2349 "issue_row"."policy_id",
2350 "issue_row"."id", "issue_row"."state",
2351 NEW."initiative_id", NEW."draft_id", TRUE
2352 );
2353 END IF;
2354 RETURN NULL;
2355 END;
2356 $$;
2358 CREATE TRIGGER "write_event_support"
2359 AFTER INSERT OR UPDATE OR DELETE ON "supporter" FOR EACH ROW EXECUTE PROCEDURE
2360 "write_event_support_trigger"();
2362 COMMENT ON FUNCTION "write_event_support_trigger"() IS 'Implementation of trigger "write_event_support" on table "supporter"';
2363 COMMENT ON TRIGGER "write_event_support" ON "supporter" IS 'Create entry in "event" table when adding, updating, or removing support';
2366 CREATE FUNCTION "write_event_suggestion_rated_trigger"()
2367 RETURNS TRIGGER
2368 LANGUAGE 'plpgsql' VOLATILE AS $$
2369 DECLARE
2370 "same_pkey_v" BOOLEAN = FALSE;
2371 "initiative_row" "initiative"%ROWTYPE;
2372 "issue_row" "issue"%ROWTYPE;
2373 "area_row" "area"%ROWTYPE;
2374 BEGIN
2375 IF TG_OP = 'UPDATE' THEN
2376 IF
2377 OLD."suggestion_id" = NEW."suggestion_id" AND
2378 OLD."member_id" = NEW."member_id"
2379 THEN
2380 IF
2381 OLD."degree" = NEW."degree" AND
2382 OLD."fulfilled" = NEW."fulfilled"
2383 THEN
2384 RETURN NULL;
2385 END IF;
2386 "same_pkey_v" := TRUE;
2387 END IF;
2388 END IF;
2389 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "same_pkey_v" THEN
2390 IF EXISTS (
2391 SELECT NULL FROM "suggestion" WHERE "id" = OLD."suggestion_id"
2392 FOR SHARE
2393 ) THEN
2394 SELECT * INTO "initiative_row" FROM "initiative"
2395 WHERE "id" = OLD."initiative_id" FOR SHARE;
2396 SELECT * INTO "issue_row" FROM "issue"
2397 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
2398 SELECT * INTO "area_row" FROM "area"
2399 WHERE "id" = "issue_row"."area_id" FOR SHARE;
2400 INSERT INTO "event" (
2401 "event", "member_id",
2402 "unit_id", "area_id", "policy_id", "issue_id", "state",
2403 "initiative_id", "suggestion_id",
2404 "boolean_value", "numeric_value"
2405 ) VALUES (
2406 'suggestion_rated', OLD."member_id",
2407 "area_row"."unit_id", "issue_row"."area_id",
2408 "issue_row"."policy_id",
2409 "initiative_row"."issue_id", "issue_row"."state",
2410 OLD."initiative_id", OLD."suggestion_id",
2411 NULL, 0
2412 );
2413 END IF;
2414 END IF;
2415 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2416 SELECT * INTO "initiative_row" FROM "initiative"
2417 WHERE "id" = NEW."initiative_id" FOR SHARE;
2418 SELECT * INTO "issue_row" FROM "issue"
2419 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
2420 SELECT * INTO "area_row" FROM "area"
2421 WHERE "id" = "issue_row"."area_id" FOR SHARE;
2422 INSERT INTO "event" (
2423 "event", "member_id",
2424 "unit_id", "area_id", "policy_id", "issue_id", "state",
2425 "initiative_id", "suggestion_id",
2426 "boolean_value", "numeric_value"
2427 ) VALUES (
2428 'suggestion_rated', NEW."member_id",
2429 "area_row"."unit_id", "issue_row"."area_id",
2430 "issue_row"."policy_id",
2431 "initiative_row"."issue_id", "issue_row"."state",
2432 NEW."initiative_id", NEW."suggestion_id",
2433 NEW."fulfilled", NEW."degree"
2434 );
2435 END IF;
2436 RETURN NULL;
2437 END;
2438 $$;
2440 CREATE TRIGGER "write_event_suggestion_rated"
2441 AFTER INSERT OR UPDATE OR DELETE ON "opinion" FOR EACH ROW EXECUTE PROCEDURE
2442 "write_event_suggestion_rated_trigger"();
2444 COMMENT ON FUNCTION "write_event_suggestion_rated_trigger"() IS 'Implementation of trigger "write_event_suggestion_rated" on table "opinion"';
2445 COMMENT ON TRIGGER "write_event_suggestion_rated" ON "opinion" IS 'Create entry in "event" table when adding, updating, or removing support';
2448 CREATE FUNCTION "write_event_delegation_trigger"()
2449 RETURNS TRIGGER
2450 LANGUAGE 'plpgsql' VOLATILE AS $$
2451 DECLARE
2452 "issue_row" "issue"%ROWTYPE;
2453 "area_row" "area"%ROWTYPE;
2454 BEGIN
2455 IF TG_OP = 'DELETE' THEN
2456 IF EXISTS (
2457 SELECT NULL FROM "member" WHERE "id" = OLD."truster_id"
2458 ) AND (CASE OLD."scope"
2459 WHEN 'unit'::"delegation_scope" THEN EXISTS (
2460 SELECT NULL FROM "unit" WHERE "id" = OLD."unit_id"
2461 )
2462 WHEN 'area'::"delegation_scope" THEN EXISTS (
2463 SELECT NULL FROM "area" WHERE "id" = OLD."area_id"
2464 )
2465 WHEN 'issue'::"delegation_scope" THEN EXISTS (
2466 SELECT NULL FROM "issue" WHERE "id" = OLD."issue_id"
2467 )
2468 END) THEN
2469 SELECT * INTO "issue_row" FROM "issue"
2470 WHERE "id" = OLD."issue_id" FOR SHARE;
2471 SELECT * INTO "area_row" FROM "area"
2472 WHERE "id" = COALESCE(OLD."area_id", "issue_row"."area_id")
2473 FOR SHARE;
2474 INSERT INTO "event" (
2475 "event", "member_id", "scope",
2476 "unit_id", "area_id", "issue_id", "state",
2477 "boolean_value"
2478 ) VALUES (
2479 'delegation', OLD."truster_id", OLD."scope",
2480 COALESCE(OLD."unit_id", "area_row"."unit_id"), "area_row"."id",
2481 OLD."issue_id", "issue_row"."state",
2482 FALSE
2483 );
2484 END IF;
2485 ELSE
2486 SELECT * INTO "issue_row" FROM "issue"
2487 WHERE "id" = NEW."issue_id" FOR SHARE;
2488 SELECT * INTO "area_row" FROM "area"
2489 WHERE "id" = COALESCE(NEW."area_id", "issue_row"."area_id")
2490 FOR SHARE;
2491 INSERT INTO "event" (
2492 "event", "member_id", "other_member_id", "scope",
2493 "unit_id", "area_id", "issue_id", "state",
2494 "boolean_value"
2495 ) VALUES (
2496 'delegation', NEW."truster_id", NEW."trustee_id", NEW."scope",
2497 COALESCE(NEW."unit_id", "area_row"."unit_id"), "area_row"."id",
2498 NEW."issue_id", "issue_row"."state",
2499 TRUE
2500 );
2501 END IF;
2502 RETURN NULL;
2503 END;
2504 $$;
2506 CREATE TRIGGER "write_event_delegation"
2507 AFTER INSERT OR UPDATE OR DELETE ON "delegation" FOR EACH ROW EXECUTE PROCEDURE
2508 "write_event_delegation_trigger"();
2510 COMMENT ON FUNCTION "write_event_delegation_trigger"() IS 'Implementation of trigger "write_event_delegation" on table "delegation"';
2511 COMMENT ON TRIGGER "write_event_delegation" ON "delegation" IS 'Create entry in "event" table when adding, updating, or removing a delegation';
2514 CREATE FUNCTION "write_event_contact_trigger"()
2515 RETURNS TRIGGER
2516 LANGUAGE 'plpgsql' VOLATILE AS $$
2517 BEGIN
2518 IF TG_OP = 'UPDATE' THEN
2519 IF
2520 OLD."member_id" = NEW."member_id" AND
2521 OLD."other_member_id" = NEW."other_member_id" AND
2522 OLD."public" = NEW."public"
2523 THEN
2524 RETURN NULL;
2525 END IF;
2526 END IF;
2527 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
2528 IF OLD."public" THEN
2529 IF EXISTS (
2530 SELECT NULL FROM "member" WHERE "id" = OLD."member_id"
2531 FOR SHARE
2532 ) AND EXISTS (
2533 SELECT NULL FROM "member" WHERE "id" = OLD."other_member_id"
2534 FOR SHARE
2535 ) THEN
2536 INSERT INTO "event" (
2537 "event", "member_id", "other_member_id", "boolean_value"
2538 ) VALUES (
2539 'contact', OLD."member_id", OLD."other_member_id", FALSE
2540 );
2541 END IF;
2542 END IF;
2543 END IF;
2544 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2545 IF NEW."public" THEN
2546 INSERT INTO "event" (
2547 "event", "member_id", "other_member_id", "boolean_value"
2548 ) VALUES (
2549 'contact', NEW."member_id", NEW."other_member_id", TRUE
2550 );
2551 END IF;
2552 END IF;
2553 RETURN NULL;
2554 END;
2555 $$;
2557 CREATE TRIGGER "write_event_contact"
2558 AFTER INSERT OR UPDATE OR DELETE ON "contact" FOR EACH ROW EXECUTE PROCEDURE
2559 "write_event_contact_trigger"();
2561 COMMENT ON FUNCTION "write_event_contact_trigger"() IS 'Implementation of trigger "write_event_contact" on table "contact"';
2562 COMMENT ON TRIGGER "write_event_contact" ON "contact" IS 'Create entry in "event" table when adding or removing public contacts';
2565 CREATE FUNCTION "send_event_notify_trigger"()
2566 RETURNS TRIGGER
2567 LANGUAGE 'plpgsql' VOLATILE AS $$
2568 BEGIN
2569 EXECUTE 'NOTIFY "event", ''' || NEW."event" || '''';
2570 RETURN NULL;
2571 END;
2572 $$;
2574 CREATE TRIGGER "send_notify"
2575 AFTER INSERT OR UPDATE ON "event" FOR EACH ROW EXECUTE PROCEDURE
2576 "send_event_notify_trigger"();
2580 ----------------------------
2581 -- Additional constraints --
2582 ----------------------------
2585 CREATE FUNCTION "delete_extended_scope_tokens_trigger"()
2586 RETURNS TRIGGER
2587 LANGUAGE 'plpgsql' VOLATILE AS $$
2588 DECLARE
2589 "system_application_row" "system_application"%ROWTYPE;
2590 BEGIN
2591 IF OLD."system_application_id" NOTNULL THEN
2592 SELECT * FROM "system_application" INTO "system_application_row"
2593 WHERE "id" = OLD."system_application_id";
2594 DELETE FROM "token"
2595 WHERE "member_id" = OLD."member_id"
2596 AND "system_application_id" = OLD."system_application_id"
2597 AND NOT COALESCE(
2598 regexp_split_to_array("scope", E'\\s+') <@
2599 regexp_split_to_array(
2600 "system_application_row"."automatic_scope", E'\\s+'
2601 ),
2602 FALSE
2603 );
2604 END IF;
2605 RETURN OLD;
2606 END;
2607 $$;
2609 CREATE TRIGGER "delete_extended_scope_tokens"
2610 BEFORE DELETE ON "member_application" FOR EACH ROW EXECUTE PROCEDURE
2611 "delete_extended_scope_tokens_trigger"();
2614 CREATE FUNCTION "detach_token_from_session_trigger"()
2615 RETURNS TRIGGER
2616 LANGUAGE 'plpgsql' VOLATILE AS $$
2617 BEGIN
2618 UPDATE "token" SET "session_id" = NULL
2619 WHERE "session_id" = OLD."id";
2620 RETURN OLD;
2621 END;
2622 $$;
2624 CREATE TRIGGER "detach_token_from_session"
2625 BEFORE DELETE ON "session" FOR EACH ROW EXECUTE PROCEDURE
2626 "detach_token_from_session_trigger"();
2629 CREATE FUNCTION "delete_non_detached_scope_with_session_trigger"()
2630 RETURNS TRIGGER
2631 LANGUAGE 'plpgsql' VOLATILE AS $$
2632 BEGIN
2633 IF NEW."session_id" ISNULL THEN
2634 SELECT coalesce(string_agg("element", ' '), '') INTO NEW."scope"
2635 FROM unnest(regexp_split_to_array(NEW."scope", E'\\s+')) AS "element"
2636 WHERE "element" LIKE '%_detached';
2637 END IF;
2638 RETURN NEW;
2639 END;
2640 $$;
2642 CREATE TRIGGER "delete_non_detached_scope_with_session"
2643 BEFORE INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
2644 "delete_non_detached_scope_with_session_trigger"();
2647 CREATE FUNCTION "delete_token_with_empty_scope_trigger"()
2648 RETURNS TRIGGER
2649 LANGUAGE 'plpgsql' VOLATILE AS $$
2650 BEGIN
2651 IF NEW."scope" = '' THEN
2652 DELETE FROM "token" WHERE "id" = NEW."id";
2653 END IF;
2654 RETURN NULL;
2655 END;
2656 $$;
2658 CREATE TRIGGER "delete_token_with_empty_scope"
2659 AFTER INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
2660 "delete_token_with_empty_scope_trigger"();
2663 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
2664 RETURNS TRIGGER
2665 LANGUAGE 'plpgsql' VOLATILE AS $$
2666 BEGIN
2667 IF NOT EXISTS (
2668 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
2669 ) THEN
2670 RAISE EXCEPTION 'Cannot create issue without an initial initiative.' USING
2671 ERRCODE = 'integrity_constraint_violation',
2672 HINT = 'Create issue, initiative, and draft within the same transaction.';
2673 END IF;
2674 RETURN NULL;
2675 END;
2676 $$;
2678 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
2679 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
2680 FOR EACH ROW EXECUTE PROCEDURE
2681 "issue_requires_first_initiative_trigger"();
2683 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
2684 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
2687 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
2688 RETURNS TRIGGER
2689 LANGUAGE 'plpgsql' VOLATILE AS $$
2690 DECLARE
2691 "reference_lost" BOOLEAN;
2692 BEGIN
2693 IF TG_OP = 'DELETE' THEN
2694 "reference_lost" := TRUE;
2695 ELSE
2696 "reference_lost" := NEW."issue_id" != OLD."issue_id";
2697 END IF;
2698 IF
2699 "reference_lost" AND NOT EXISTS (
2700 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
2701 )
2702 THEN
2703 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
2704 END IF;
2705 RETURN NULL;
2706 END;
2707 $$;
2709 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
2710 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
2711 FOR EACH ROW EXECUTE PROCEDURE
2712 "last_initiative_deletes_issue_trigger"();
2714 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
2715 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
2718 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
2719 RETURNS TRIGGER
2720 LANGUAGE 'plpgsql' VOLATILE AS $$
2721 BEGIN
2722 IF NOT EXISTS (
2723 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
2724 ) THEN
2725 RAISE EXCEPTION 'Cannot create initiative without an initial draft.' USING
2726 ERRCODE = 'integrity_constraint_violation',
2727 HINT = 'Create issue, initiative and draft within the same transaction.';
2728 END IF;
2729 RETURN NULL;
2730 END;
2731 $$;
2733 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
2734 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
2735 FOR EACH ROW EXECUTE PROCEDURE
2736 "initiative_requires_first_draft_trigger"();
2738 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
2739 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
2742 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
2743 RETURNS TRIGGER
2744 LANGUAGE 'plpgsql' VOLATILE AS $$
2745 DECLARE
2746 "reference_lost" BOOLEAN;
2747 BEGIN
2748 IF TG_OP = 'DELETE' THEN
2749 "reference_lost" := TRUE;
2750 ELSE
2751 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
2752 END IF;
2753 IF
2754 "reference_lost" AND NOT EXISTS (
2755 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
2756 )
2757 THEN
2758 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
2759 END IF;
2760 RETURN NULL;
2761 END;
2762 $$;
2764 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
2765 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
2766 FOR EACH ROW EXECUTE PROCEDURE
2767 "last_draft_deletes_initiative_trigger"();
2769 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
2770 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
2773 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
2774 RETURNS TRIGGER
2775 LANGUAGE 'plpgsql' VOLATILE AS $$
2776 BEGIN
2777 IF NOT EXISTS (
2778 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
2779 ) THEN
2780 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.' USING
2781 ERRCODE = 'integrity_constraint_violation',
2782 HINT = 'Create suggestion and opinion within the same transaction.';
2783 END IF;
2784 RETURN NULL;
2785 END;
2786 $$;
2788 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
2789 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
2790 FOR EACH ROW EXECUTE PROCEDURE
2791 "suggestion_requires_first_opinion_trigger"();
2793 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
2794 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
2797 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
2798 RETURNS TRIGGER
2799 LANGUAGE 'plpgsql' VOLATILE AS $$
2800 DECLARE
2801 "reference_lost" BOOLEAN;
2802 BEGIN
2803 IF TG_OP = 'DELETE' THEN
2804 "reference_lost" := TRUE;
2805 ELSE
2806 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
2807 END IF;
2808 IF
2809 "reference_lost" AND NOT EXISTS (
2810 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
2811 )
2812 THEN
2813 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
2814 END IF;
2815 RETURN NULL;
2816 END;
2817 $$;
2819 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
2820 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
2821 FOR EACH ROW EXECUTE PROCEDURE
2822 "last_opinion_deletes_suggestion_trigger"();
2824 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
2825 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
2828 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()
2829 RETURNS TRIGGER
2830 LANGUAGE 'plpgsql' VOLATILE AS $$
2831 BEGIN
2832 DELETE FROM "direct_voter"
2833 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
2834 RETURN NULL;
2835 END;
2836 $$;
2838 CREATE TRIGGER "non_voter_deletes_direct_voter"
2839 AFTER INSERT OR UPDATE ON "non_voter"
2840 FOR EACH ROW EXECUTE PROCEDURE
2841 "non_voter_deletes_direct_voter_trigger"();
2843 COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';
2844 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")';
2847 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()
2848 RETURNS TRIGGER
2849 LANGUAGE 'plpgsql' VOLATILE AS $$
2850 BEGIN
2851 DELETE FROM "non_voter"
2852 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
2853 RETURN NULL;
2854 END;
2855 $$;
2857 CREATE TRIGGER "direct_voter_deletes_non_voter"
2858 AFTER INSERT OR UPDATE ON "direct_voter"
2859 FOR EACH ROW EXECUTE PROCEDURE
2860 "direct_voter_deletes_non_voter_trigger"();
2862 COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';
2863 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")';
2866 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()
2867 RETURNS TRIGGER
2868 LANGUAGE 'plpgsql' VOLATILE AS $$
2869 BEGIN
2870 IF NEW."comment" ISNULL THEN
2871 NEW."comment_changed" := NULL;
2872 NEW."formatting_engine" := NULL;
2873 END IF;
2874 RETURN NEW;
2875 END;
2876 $$;
2878 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"
2879 BEFORE INSERT OR UPDATE ON "direct_voter"
2880 FOR EACH ROW EXECUTE PROCEDURE
2881 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();
2883 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"';
2884 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.';
2888 ---------------------------------
2889 -- Delete incomplete snapshots --
2890 ---------------------------------
2893 CREATE FUNCTION "delete_snapshot_on_partial_delete_trigger"()
2894 RETURNS TRIGGER
2895 LANGUAGE 'plpgsql' VOLATILE AS $$
2896 BEGIN
2897 IF TG_OP = 'UPDATE' THEN
2898 IF
2899 OLD."snapshot_id" = NEW."snapshot_id" AND
2900 OLD."issue_id" = NEW."issue_id"
2901 THEN
2902 RETURN NULL;
2903 END IF;
2904 END IF;
2905 DELETE FROM "snapshot" WHERE "id" = OLD."snapshot_id";
2906 RETURN NULL;
2907 END;
2908 $$;
2910 CREATE TRIGGER "delete_snapshot_on_partial_delete"
2911 AFTER UPDATE OR DELETE ON "snapshot_issue"
2912 FOR EACH ROW EXECUTE PROCEDURE
2913 "delete_snapshot_on_partial_delete_trigger"();
2915 COMMENT ON FUNCTION "delete_snapshot_on_partial_delete_trigger"() IS 'Implementation of trigger "delete_snapshot_on_partial_delete" on table "snapshot_issue"';
2916 COMMENT ON TRIGGER "delete_snapshot_on_partial_delete" ON "snapshot_issue" IS 'Deletes whole snapshot if one issue is deleted from the snapshot';
2920 ---------------------------------------------------------------
2921 -- Ensure that votes are not modified when issues are closed --
2922 ---------------------------------------------------------------
2924 -- NOTE: Frontends should ensure this anyway, but in case of programming
2925 -- errors the following triggers ensure data integrity.
2928 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
2929 RETURNS TRIGGER
2930 LANGUAGE 'plpgsql' VOLATILE AS $$
2931 DECLARE
2932 "issue_id_v" "issue"."id"%TYPE;
2933 "issue_row" "issue"%ROWTYPE;
2934 BEGIN
2935 IF EXISTS (
2936 SELECT NULL FROM "temporary_transaction_data"
2937 WHERE "txid" = txid_current()
2938 AND "key" = 'override_protection_triggers'
2939 AND "value" = TRUE::TEXT
2940 ) THEN
2941 RETURN NULL;
2942 END IF;
2943 IF TG_OP = 'DELETE' THEN
2944 "issue_id_v" := OLD."issue_id";
2945 ELSE
2946 "issue_id_v" := NEW."issue_id";
2947 END IF;
2948 SELECT INTO "issue_row" * FROM "issue"
2949 WHERE "id" = "issue_id_v" FOR SHARE;
2950 IF (
2951 "issue_row"."closed" NOTNULL OR (
2952 "issue_row"."state" = 'voting' AND
2953 "issue_row"."phase_finished" NOTNULL
2954 )
2955 ) THEN
2956 IF
2957 TG_RELID = 'direct_voter'::regclass AND
2958 TG_OP = 'UPDATE'
2959 THEN
2960 IF
2961 OLD."issue_id" = NEW."issue_id" AND
2962 OLD."member_id" = NEW."member_id" AND
2963 OLD."weight" = NEW."weight"
2964 THEN
2965 RETURN NULL; -- allows changing of voter comment
2966 END IF;
2967 END IF;
2968 RAISE EXCEPTION 'Tried to modify data after voting has been closed.' USING
2969 ERRCODE = 'integrity_constraint_violation';
2970 END IF;
2971 RETURN NULL;
2972 END;
2973 $$;
2975 CREATE TRIGGER "forbid_changes_on_closed_issue"
2976 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
2977 FOR EACH ROW EXECUTE PROCEDURE
2978 "forbid_changes_on_closed_issue_trigger"();
2980 CREATE TRIGGER "forbid_changes_on_closed_issue"
2981 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
2982 FOR EACH ROW EXECUTE PROCEDURE
2983 "forbid_changes_on_closed_issue_trigger"();
2985 CREATE TRIGGER "forbid_changes_on_closed_issue"
2986 AFTER INSERT OR UPDATE OR DELETE ON "vote"
2987 FOR EACH ROW EXECUTE PROCEDURE
2988 "forbid_changes_on_closed_issue_trigger"();
2990 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"';
2991 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';
2992 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';
2993 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';
2997 --------------------------------------------------------------------
2998 -- Auto-retrieval of fields only needed for referential integrity --
2999 --------------------------------------------------------------------
3002 CREATE FUNCTION "autofill_issue_id_trigger"()
3003 RETURNS TRIGGER
3004 LANGUAGE 'plpgsql' VOLATILE AS $$
3005 BEGIN
3006 IF NEW."issue_id" ISNULL THEN
3007 SELECT "issue_id" INTO NEW."issue_id"
3008 FROM "initiative" WHERE "id" = NEW."initiative_id";
3009 END IF;
3010 RETURN NEW;
3011 END;
3012 $$;
3014 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
3015 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
3017 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
3018 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
3020 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
3021 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
3022 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
3025 CREATE FUNCTION "autofill_initiative_id_trigger"()
3026 RETURNS TRIGGER
3027 LANGUAGE 'plpgsql' VOLATILE AS $$
3028 BEGIN
3029 IF NEW."initiative_id" ISNULL THEN
3030 SELECT "initiative_id" INTO NEW."initiative_id"
3031 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
3032 END IF;
3033 RETURN NEW;
3034 END;
3035 $$;
3037 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
3038 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
3040 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
3041 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
3045 -------------------------------------------------------
3046 -- Automatic copying of values for indexing purposes --
3047 -------------------------------------------------------
3050 CREATE FUNCTION "copy_current_draft_data"
3051 ("initiative_id_p" "initiative"."id"%TYPE )
3052 RETURNS VOID
3053 LANGUAGE 'plpgsql' VOLATILE AS $$
3054 BEGIN
3055 PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p"
3056 FOR UPDATE;
3057 UPDATE "initiative" SET
3058 "location" = "draft"."location",
3059 "draft_text_search_data" = "draft"."text_search_data"
3060 FROM "current_draft" AS "draft"
3061 WHERE "initiative"."id" = "initiative_id_p"
3062 AND "draft"."initiative_id" = "initiative_id_p";
3063 END;
3064 $$;
3066 COMMENT ON FUNCTION "copy_current_draft_data"
3067 ( "initiative"."id"%TYPE )
3068 IS 'Helper function for function "copy_current_draft_data_trigger"';
3071 CREATE FUNCTION "copy_current_draft_data_trigger"()
3072 RETURNS TRIGGER
3073 LANGUAGE 'plpgsql' VOLATILE AS $$
3074 BEGIN
3075 IF TG_OP='DELETE' THEN
3076 PERFORM "copy_current_draft_data"(OLD."initiative_id");
3077 ELSE
3078 IF TG_OP='UPDATE' THEN
3079 IF COALESCE(OLD."inititiave_id" != NEW."initiative_id", TRUE) THEN
3080 PERFORM "copy_current_draft_data"(OLD."initiative_id");
3081 END IF;
3082 END IF;
3083 PERFORM "copy_current_draft_data"(NEW."initiative_id");
3084 END IF;
3085 RETURN NULL;
3086 END;
3087 $$;
3089 CREATE TRIGGER "copy_current_draft_data"
3090 AFTER INSERT OR UPDATE OR DELETE ON "draft"
3091 FOR EACH ROW EXECUTE PROCEDURE
3092 "copy_current_draft_data_trigger"();
3094 COMMENT ON FUNCTION "copy_current_draft_data_trigger"() IS 'Implementation of trigger "copy_current_draft_data" on table "draft"';
3095 COMMENT ON TRIGGER "copy_current_draft_data" ON "draft" IS 'Copy certain fields from most recent "draft" to "initiative"';
3099 -----------------------------------------------------
3100 -- Automatic calculation of certain default values --
3101 -----------------------------------------------------
3104 CREATE FUNCTION "copy_timings_trigger"()
3105 RETURNS TRIGGER
3106 LANGUAGE 'plpgsql' VOLATILE AS $$
3107 DECLARE
3108 "policy_row" "policy"%ROWTYPE;
3109 BEGIN
3110 SELECT * INTO "policy_row" FROM "policy"
3111 WHERE "id" = NEW."policy_id";
3112 IF NEW."min_admission_time" ISNULL THEN
3113 NEW."min_admission_time" := "policy_row"."min_admission_time";
3114 END IF;
3115 IF NEW."max_admission_time" ISNULL THEN
3116 NEW."max_admission_time" := "policy_row"."max_admission_time";
3117 END IF;
3118 IF NEW."discussion_time" ISNULL THEN
3119 NEW."discussion_time" := "policy_row"."discussion_time";
3120 END IF;
3121 IF NEW."verification_time" ISNULL THEN
3122 NEW."verification_time" := "policy_row"."verification_time";
3123 END IF;
3124 IF NEW."voting_time" ISNULL THEN
3125 NEW."voting_time" := "policy_row"."voting_time";
3126 END IF;
3127 RETURN NEW;
3128 END;
3129 $$;
3131 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
3132 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
3134 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
3135 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
3138 CREATE FUNCTION "default_for_draft_id_trigger"()
3139 RETURNS TRIGGER
3140 LANGUAGE 'plpgsql' VOLATILE AS $$
3141 BEGIN
3142 IF NEW."draft_id" ISNULL THEN
3143 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
3144 WHERE "initiative_id" = NEW."initiative_id";
3145 END IF;
3146 RETURN NEW;
3147 END;
3148 $$;
3150 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
3151 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
3152 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
3153 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
3155 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
3156 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';
3157 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';
3161 ----------------------------------------
3162 -- Automatic creation of dependencies --
3163 ----------------------------------------
3166 CREATE FUNCTION "autocreate_interest_trigger"()
3167 RETURNS TRIGGER
3168 LANGUAGE 'plpgsql' VOLATILE AS $$
3169 BEGIN
3170 IF NOT EXISTS (
3171 SELECT NULL FROM "initiative" JOIN "interest"
3172 ON "initiative"."issue_id" = "interest"."issue_id"
3173 WHERE "initiative"."id" = NEW."initiative_id"
3174 AND "interest"."member_id" = NEW."member_id"
3175 ) THEN
3176 BEGIN
3177 INSERT INTO "interest" ("issue_id", "member_id")
3178 SELECT "issue_id", NEW."member_id"
3179 FROM "initiative" WHERE "id" = NEW."initiative_id";
3180 EXCEPTION WHEN unique_violation THEN END;
3181 END IF;
3182 RETURN NEW;
3183 END;
3184 $$;
3186 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
3187 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
3189 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
3190 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';
3193 CREATE FUNCTION "autocreate_supporter_trigger"()
3194 RETURNS TRIGGER
3195 LANGUAGE 'plpgsql' VOLATILE AS $$
3196 BEGIN
3197 IF NOT EXISTS (
3198 SELECT NULL FROM "suggestion" JOIN "supporter"
3199 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
3200 WHERE "suggestion"."id" = NEW."suggestion_id"
3201 AND "supporter"."member_id" = NEW."member_id"
3202 ) THEN
3203 BEGIN
3204 INSERT INTO "supporter" ("initiative_id", "member_id")
3205 SELECT "initiative_id", NEW."member_id"
3206 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
3207 EXCEPTION WHEN unique_violation THEN END;
3208 END IF;
3209 RETURN NEW;
3210 END;
3211 $$;
3213 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
3214 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
3216 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
3217 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.';
3221 ------------------------------------------
3222 -- Views and helper functions for views --
3223 ------------------------------------------
3226 CREATE VIEW "member_eligible_to_be_notified" AS
3227 SELECT * FROM "member"
3228 WHERE "activated" NOTNULL AND "locked" = FALSE;
3230 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")';
3233 CREATE VIEW "member_to_notify" AS
3234 SELECT * FROM "member_eligible_to_be_notified"
3235 WHERE "disable_notifications" = FALSE;
3237 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)';
3240 CREATE VIEW "area_quorum" AS
3241 SELECT
3242 "area"."id" AS "area_id",
3243 ceil(
3244 "area"."quorum_standard"::FLOAT8 * "quorum_factor"::FLOAT8 ^ (
3245 coalesce(
3246 ( SELECT sum(
3247 ( extract(epoch from "area"."quorum_time")::FLOAT8 /
3248 extract(epoch from
3249 ("issue"."accepted"-"issue"."created") +
3250 "issue"."discussion_time" +
3251 "issue"."verification_time" +
3252 "issue"."voting_time"
3253 )::FLOAT8
3254 ) ^ "area"."quorum_exponent"::FLOAT8
3255 )
3256 FROM "issue" JOIN "policy"
3257 ON "issue"."policy_id" = "policy"."id"
3258 WHERE "issue"."area_id" = "area"."id"
3259 AND "issue"."accepted" NOTNULL
3260 AND "issue"."closed" ISNULL
3261 AND "policy"."polling" = FALSE
3262 )::FLOAT8, 0::FLOAT8
3263 ) / "area"."quorum_issues"::FLOAT8 - 1::FLOAT8
3264 ) * CASE WHEN "area"."quorum_den" ISNULL THEN 1 ELSE (
3265 SELECT "snapshot"."population"
3266 FROM "snapshot"
3267 WHERE "snapshot"."area_id" = "area"."id"
3268 AND "snapshot"."issue_id" ISNULL
3269 ORDER BY "snapshot"."id" DESC
3270 LIMIT 1
3271 ) END / coalesce("area"."quorum_den", 1)
3273 )::INT4 AS "issue_quorum"
3274 FROM "area";
3276 COMMENT ON VIEW "area_quorum" IS 'Area-based quorum considering number of open (accepted) issues';
3279 CREATE VIEW "area_with_unaccepted_issues" AS
3280 SELECT DISTINCT ON ("area"."id") "area".*
3281 FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id"
3282 WHERE "issue"."state" = 'admission';
3284 COMMENT ON VIEW "area_with_unaccepted_issues" IS 'All areas with unaccepted open issues (needed for issue admission system)';
3287 CREATE VIEW "issue_for_admission" AS
3288 SELECT DISTINCT ON ("issue"."area_id")
3289 "issue".*,
3290 max("initiative"."supporter_count") AS "max_supporter_count"
3291 FROM "issue"
3292 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
3293 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
3294 JOIN "area" ON "issue"."area_id" = "area"."id"
3295 WHERE "issue"."state" = 'admission'::"issue_state"
3296 AND now() >= "issue"."created" + "issue"."min_admission_time"
3297 AND "initiative"."supporter_count" >= "policy"."issue_quorum"
3298 AND "initiative"."supporter_count" * "policy"."issue_quorum_den" >=
3299 "issue"."population" * "policy"."issue_quorum_num"
3300 AND "initiative"."supporter_count" >= "area"."issue_quorum"
3301 AND "initiative"."revoked" ISNULL
3302 GROUP BY "issue"."id"
3303 ORDER BY "issue"."area_id", "max_supporter_count" DESC, "issue"."id";
3305 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';
3308 CREATE VIEW "unit_delegation" AS
3309 SELECT
3310 "unit"."id" AS "unit_id",
3311 "delegation"."id",
3312 "delegation"."truster_id",
3313 "delegation"."trustee_id",
3314 "delegation"."scope"
3315 FROM "unit"
3316 JOIN "delegation"
3317 ON "delegation"."unit_id" = "unit"."id"
3318 JOIN "member"
3319 ON "delegation"."truster_id" = "member"."id"
3320 JOIN "privilege"
3321 ON "delegation"."unit_id" = "privilege"."unit_id"
3322 AND "delegation"."truster_id" = "privilege"."member_id"
3323 WHERE "member"."active" AND "privilege"."voting_right";
3325 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
3328 CREATE VIEW "area_delegation" AS
3329 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
3330 "area"."id" AS "area_id",
3331 "delegation"."id",
3332 "delegation"."truster_id",
3333 "delegation"."trustee_id",
3334 "delegation"."scope"
3335 FROM "area"
3336 JOIN "delegation"
3337 ON "delegation"."unit_id" = "area"."unit_id"
3338 OR "delegation"."area_id" = "area"."id"
3339 JOIN "member"
3340 ON "delegation"."truster_id" = "member"."id"
3341 JOIN "privilege"
3342 ON "area"."unit_id" = "privilege"."unit_id"
3343 AND "delegation"."truster_id" = "privilege"."member_id"
3344 WHERE "member"."active" AND "privilege"."voting_right"
3345 ORDER BY
3346 "area"."id",
3347 "delegation"."truster_id",
3348 "delegation"."scope" DESC;
3350 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
3353 CREATE VIEW "issue_delegation" AS
3354 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
3355 "issue"."id" AS "issue_id",
3356 "delegation"."id",
3357 "delegation"."truster_id",
3358 "delegation"."trustee_id",
3359 "delegation"."scope"
3360 FROM "issue"
3361 JOIN "area"
3362 ON "area"."id" = "issue"."area_id"
3363 JOIN "delegation"
3364 ON "delegation"."unit_id" = "area"."unit_id"
3365 OR "delegation"."area_id" = "area"."id"
3366 OR "delegation"."issue_id" = "issue"."id"
3367 JOIN "member"
3368 ON "delegation"."truster_id" = "member"."id"
3369 JOIN "privilege"
3370 ON "area"."unit_id" = "privilege"."unit_id"
3371 AND "delegation"."truster_id" = "privilege"."member_id"
3372 WHERE "member"."active" AND "privilege"."voting_right"
3373 ORDER BY
3374 "issue"."id",
3375 "delegation"."truster_id",
3376 "delegation"."scope" DESC;
3378 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
3381 CREATE VIEW "member_count_view" AS
3382 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
3384 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
3387 CREATE VIEW "unit_member" AS
3388 SELECT
3389 "unit"."id" AS "unit_id",
3390 "member"."id" AS "member_id"
3391 FROM "privilege"
3392 JOIN "unit" ON "unit_id" = "privilege"."unit_id"
3393 JOIN "member" ON "member"."id" = "privilege"."member_id"
3394 WHERE "privilege"."voting_right" AND "member"."active";
3396 COMMENT ON VIEW "unit_member" IS 'Active members with voting right in a unit';
3399 CREATE VIEW "unit_member_count" AS
3400 SELECT
3401 "unit"."id" AS "unit_id",
3402 count("unit_member"."member_id") AS "member_count"
3403 FROM "unit" LEFT JOIN "unit_member"
3404 ON "unit"."id" = "unit_member"."unit_id"
3405 GROUP BY "unit"."id";
3407 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
3410 CREATE VIEW "opening_draft" AS
3411 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
3412 ORDER BY "initiative_id", "id";
3414 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
3417 CREATE VIEW "current_draft" AS
3418 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
3419 ORDER BY "initiative_id", "id" DESC;
3421 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
3424 CREATE VIEW "critical_opinion" AS
3425 SELECT * FROM "opinion"
3426 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
3427 OR ("degree" = -2 AND "fulfilled" = TRUE);
3429 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
3432 CREATE VIEW "issue_supporter_in_admission_state" AS
3433 SELECT
3434 "area"."unit_id",
3435 "issue"."area_id",
3436 "issue"."id" AS "issue_id",
3437 "supporter"."member_id",
3438 "direct_interest_snapshot"."weight"
3439 FROM "issue"
3440 JOIN "area" ON "area"."id" = "issue"."area_id"
3441 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
3442 JOIN "direct_interest_snapshot"
3443 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
3444 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
3445 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
3446 WHERE "issue"."state" = 'admission'::"issue_state";
3448 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';
3451 CREATE VIEW "initiative_suggestion_order_calculation" AS
3452 SELECT
3453 "initiative"."id" AS "initiative_id",
3454 ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final"
3455 FROM "initiative" JOIN "issue"
3456 ON "initiative"."issue_id" = "issue"."id"
3457 WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL)
3458 OR ("initiative"."final_suggestion_order_calculated" = FALSE);
3460 COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';
3462 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';
3465 CREATE VIEW "individual_suggestion_ranking" AS
3466 SELECT
3467 "opinion"."initiative_id",
3468 "opinion"."member_id",
3469 "direct_interest_snapshot"."weight",
3470 CASE WHEN
3471 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
3472 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
3473 THEN 1 ELSE
3474 CASE WHEN
3475 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
3476 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
3477 THEN 2 ELSE
3478 CASE WHEN
3479 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
3480 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
3481 THEN 3 ELSE 4 END
3482 END
3483 END AS "preference",
3484 "opinion"."suggestion_id"
3485 FROM "opinion"
3486 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
3487 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
3488 JOIN "direct_interest_snapshot"
3489 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
3490 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
3491 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
3493 COMMENT ON VIEW "individual_suggestion_ranking" IS 'Helper view for "lf_update_suggestion_order" to allow a proportional ordering of suggestions within an initiative';
3496 CREATE VIEW "battle_participant" AS
3497 SELECT "initiative"."id", "initiative"."issue_id"
3498 FROM "issue" JOIN "initiative"
3499 ON "issue"."id" = "initiative"."issue_id"
3500 WHERE "initiative"."admitted"
3501 UNION ALL
3502 SELECT NULL, "id" AS "issue_id"
3503 FROM "issue";
3505 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
3508 CREATE VIEW "battle_view" AS
3509 SELECT
3510 "issue"."id" AS "issue_id",
3511 "winning_initiative"."id" AS "winning_initiative_id",
3512 "losing_initiative"."id" AS "losing_initiative_id",
3513 sum(
3514 CASE WHEN
3515 coalesce("better_vote"."grade", 0) >
3516 coalesce("worse_vote"."grade", 0)
3517 THEN "direct_voter"."weight" ELSE 0 END
3518 ) AS "count"
3519 FROM "issue"
3520 LEFT JOIN "direct_voter"
3521 ON "issue"."id" = "direct_voter"."issue_id"
3522 JOIN "battle_participant" AS "winning_initiative"
3523 ON "issue"."id" = "winning_initiative"."issue_id"
3524 JOIN "battle_participant" AS "losing_initiative"
3525 ON "issue"."id" = "losing_initiative"."issue_id"
3526 LEFT JOIN "vote" AS "better_vote"
3527 ON "direct_voter"."member_id" = "better_vote"."member_id"
3528 AND "winning_initiative"."id" = "better_vote"."initiative_id"
3529 LEFT JOIN "vote" AS "worse_vote"
3530 ON "direct_voter"."member_id" = "worse_vote"."member_id"
3531 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
3532 WHERE "issue"."state" = 'voting'
3533 AND "issue"."phase_finished" NOTNULL
3534 AND (
3535 "winning_initiative"."id" != "losing_initiative"."id" OR
3536 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
3537 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
3538 GROUP BY
3539 "issue"."id",
3540 "winning_initiative"."id",
3541 "losing_initiative"."id";
3543 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';
3546 CREATE VIEW "expired_session" AS
3547 SELECT * FROM "session" WHERE now() > "expiry";
3549 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
3550 DELETE FROM "session" WHERE "id" = OLD."id";
3552 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
3553 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
3556 CREATE VIEW "expired_token" AS
3557 SELECT * FROM "token" WHERE now() > "expiry" AND NOT (
3558 "token_type" = 'authorization' AND "used" AND EXISTS (
3559 SELECT NULL FROM "token" AS "other"
3560 WHERE "other"."authorization_token_id" = "id" ) );
3562 CREATE RULE "delete" AS ON DELETE TO "expired_token" DO INSTEAD
3563 DELETE FROM "token" WHERE "id" = OLD."id";
3565 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';
3568 CREATE VIEW "unused_snapshot" AS
3569 SELECT "snapshot".* FROM "snapshot"
3570 LEFT JOIN "issue"
3571 ON "snapshot"."id" = "issue"."latest_snapshot_id"
3572 OR "snapshot"."id" = "issue"."admission_snapshot_id"
3573 OR "snapshot"."id" = "issue"."half_freeze_snapshot_id"
3574 OR "snapshot"."id" = "issue"."full_freeze_snapshot_id"
3575 WHERE "issue"."id" ISNULL;
3577 CREATE RULE "delete" AS ON DELETE TO "unused_snapshot" DO INSTEAD
3578 DELETE FROM "snapshot" WHERE "id" = OLD."id";
3580 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)';
3583 CREATE VIEW "expired_snapshot" AS
3584 SELECT "unused_snapshot".* FROM "unused_snapshot" CROSS JOIN "system_setting"
3585 WHERE "unused_snapshot"."calculated" <
3586 now() - "system_setting"."snapshot_retention";
3588 CREATE RULE "delete" AS ON DELETE TO "expired_snapshot" DO INSTEAD
3589 DELETE FROM "snapshot" WHERE "id" = OLD."id";
3591 COMMENT ON VIEW "expired_snapshot" IS 'Contains "unused_snapshot"s that are older than "system_setting"."snapshot_retention" (for deletion)';
3594 CREATE VIEW "open_issue" AS
3595 SELECT * FROM "issue" WHERE "closed" ISNULL;
3597 COMMENT ON VIEW "open_issue" IS 'All open issues';
3600 CREATE VIEW "member_contingent" AS
3601 SELECT
3602 "member"."id" AS "member_id",
3603 "contingent"."polling",
3604 "contingent"."time_frame",
3605 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
3606 (
3607 SELECT count(1) FROM "draft"
3608 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
3609 WHERE "draft"."author_id" = "member"."id"
3610 AND "initiative"."polling" = "contingent"."polling"
3611 AND "draft"."created" > now() - "contingent"."time_frame"
3612 ) + (
3613 SELECT count(1) FROM "suggestion"
3614 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
3615 WHERE "suggestion"."author_id" = "member"."id"
3616 AND "contingent"."polling" = FALSE
3617 AND "suggestion"."created" > now() - "contingent"."time_frame"
3618 )
3619 ELSE NULL END AS "text_entry_count",
3620 "contingent"."text_entry_limit",
3621 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
3622 SELECT count(1) FROM "opening_draft" AS "draft"
3623 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
3624 WHERE "draft"."author_id" = "member"."id"
3625 AND "initiative"."polling" = "contingent"."polling"
3626 AND "draft"."created" > now() - "contingent"."time_frame"
3627 ) ELSE NULL END AS "initiative_count",
3628 "contingent"."initiative_limit"
3629 FROM "member" CROSS JOIN "contingent";
3631 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
3633 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
3634 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
3637 CREATE VIEW "member_contingent_left" AS
3638 SELECT
3639 "member_id",
3640 "polling",
3641 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
3642 max("initiative_limit" - "initiative_count") AS "initiatives_left"
3643 FROM "member_contingent" GROUP BY "member_id", "polling";
3645 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.';
3648 CREATE VIEW "event_for_notification" AS
3649 SELECT
3650 "member"."id" AS "recipient_id",
3651 "event".*
3652 FROM "member" CROSS JOIN "event"
3653 JOIN "issue" ON "issue"."id" = "event"."issue_id"
3654 JOIN "area" ON "area"."id" = "issue"."area_id"
3655 LEFT JOIN "privilege" ON
3656 "privilege"."member_id" = "member"."id" AND
3657 "privilege"."unit_id" = "area"."unit_id" AND
3658 "privilege"."voting_right" = TRUE
3659 LEFT JOIN "subscription" ON
3660 "subscription"."member_id" = "member"."id" AND
3661 "subscription"."unit_id" = "area"."unit_id"
3662 LEFT JOIN "ignored_area" ON
3663 "ignored_area"."member_id" = "member"."id" AND
3664 "ignored_area"."area_id" = "issue"."area_id"
3665 LEFT JOIN "interest" ON
3666 "interest"."member_id" = "member"."id" AND
3667 "interest"."issue_id" = "event"."issue_id"
3668 LEFT JOIN "supporter" ON
3669 "supporter"."member_id" = "member"."id" AND
3670 "supporter"."initiative_id" = "event"."initiative_id"
3671 WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL)
3672 AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
3673 AND (
3674 "event"."event" = 'issue_state_changed'::"event_type" OR
3675 ( "event"."event" = 'initiative_revoked'::"event_type" AND
3676 "supporter"."member_id" NOTNULL ) );
3678 COMMENT ON VIEW "event_for_notification" IS 'Entries of the "event" table which are of interest for a particular notification mail recipient';
3680 COMMENT ON COLUMN "event_for_notification"."recipient_id" IS 'member_id of the recipient of a notification mail';
3683 CREATE VIEW "updated_initiative" AS
3684 SELECT
3685 "supporter"."member_id" AS "recipient_id",
3686 FALSE AS "featured",
3687 "supporter"."initiative_id"
3688 FROM "supporter"
3689 JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id"
3690 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
3691 LEFT JOIN "notification_initiative_sent" AS "sent" ON
3692 "sent"."member_id" = "supporter"."member_id" AND
3693 "sent"."initiative_id" = "supporter"."initiative_id"
3694 LEFT JOIN "ignored_initiative" ON
3695 "ignored_initiative"."member_id" = "supporter"."member_id" AND
3696 "ignored_initiative"."initiative_id" = "supporter"."initiative_id"
3697 WHERE "issue"."state" IN ('admission', 'discussion')
3698 AND "initiative"."revoked" ISNULL
3699 AND "ignored_initiative"."member_id" ISNULL
3700 AND (
3701 EXISTS (
3702 SELECT NULL FROM "draft"
3703 LEFT JOIN "ignored_member" ON
3704 "ignored_member"."member_id" = "supporter"."member_id" AND
3705 "ignored_member"."other_member_id" = "draft"."author_id"
3706 WHERE "draft"."initiative_id" = "supporter"."initiative_id"
3707 AND "draft"."id" > "supporter"."draft_id"
3708 AND "ignored_member"."member_id" ISNULL
3709 ) OR EXISTS (
3710 SELECT NULL FROM "suggestion"
3711 LEFT JOIN "opinion" ON
3712 "opinion"."member_id" = "supporter"."member_id" AND
3713 "opinion"."suggestion_id" = "suggestion"."id"
3714 LEFT JOIN "ignored_member" ON
3715 "ignored_member"."member_id" = "supporter"."member_id" AND
3716 "ignored_member"."other_member_id" = "suggestion"."author_id"
3717 WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"
3718 AND "opinion"."member_id" ISNULL
3719 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
3720 AND "ignored_member"."member_id" ISNULL
3721 )
3722 );
3724 COMMENT ON VIEW "updated_initiative" IS 'Helper view for view "updated_or_featured_initiative"';
3727 CREATE FUNCTION "featured_initiative"
3728 ( "recipient_id_p" "member"."id"%TYPE,
3729 "area_id_p" "area"."id"%TYPE )
3730 RETURNS SETOF "initiative"."id"%TYPE
3731 LANGUAGE 'plpgsql' STABLE AS $$
3732 DECLARE
3733 "counter_v" "member"."notification_counter"%TYPE;
3734 "sample_size_v" "member"."notification_sample_size"%TYPE;
3735 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
3736 "match_v" BOOLEAN;
3737 "member_id_v" "member"."id"%TYPE;
3738 "seed_v" TEXT;
3739 "initiative_id_v" "initiative"."id"%TYPE;
3740 BEGIN
3741 SELECT "notification_counter", "notification_sample_size"
3742 INTO "counter_v", "sample_size_v"
3743 FROM "member" WHERE "id" = "recipient_id_p";
3744 IF COALESCE("sample_size_v" <= 0, TRUE) THEN
3745 RETURN;
3746 END IF;
3747 "initiative_id_ary" := '{}';
3748 LOOP
3749 "match_v" := FALSE;
3750 FOR "member_id_v", "seed_v" IN
3751 SELECT * FROM (
3752 SELECT DISTINCT
3753 "supporter"."member_id",
3754 md5(
3755 "recipient_id_p" || '-' ||
3756 "counter_v" || '-' ||
3757 "area_id_p" || '-' ||
3758 "supporter"."member_id"
3759 ) AS "seed"
3760 FROM "supporter"
3761 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
3762 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
3763 WHERE "supporter"."member_id" != "recipient_id_p"
3764 AND "issue"."area_id" = "area_id_p"
3765 AND "issue"."state" IN ('admission', 'discussion', 'verification')
3766 ) AS "subquery"
3767 ORDER BY "seed"
3768 LOOP
3769 SELECT "initiative"."id" INTO "initiative_id_v"
3770 FROM "initiative"
3771 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
3772 JOIN "area" ON "area"."id" = "issue"."area_id"
3773 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
3774 LEFT JOIN "supporter" AS "self_support" ON
3775 "self_support"."initiative_id" = "initiative"."id" AND
3776 "self_support"."member_id" = "recipient_id_p"
3777 LEFT JOIN "privilege" ON
3778 "privilege"."member_id" = "recipient_id_p" AND
3779 "privilege"."unit_id" = "area"."unit_id" AND
3780 "privilege"."voting_right" = TRUE
3781 LEFT JOIN "subscription" ON
3782 "subscription"."member_id" = "recipient_id_p" AND
3783 "subscription"."unit_id" = "area"."unit_id"
3784 LEFT JOIN "ignored_initiative" ON
3785 "ignored_initiative"."member_id" = "recipient_id_p" AND
3786 "ignored_initiative"."initiative_id" = "initiative"."id"
3787 WHERE "supporter"."member_id" = "member_id_v"
3788 AND "issue"."area_id" = "area_id_p"
3789 AND "issue"."state" IN ('admission', 'discussion', 'verification')
3790 AND "initiative"."revoked" ISNULL
3791 AND "self_support"."member_id" ISNULL
3792 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
3793 AND (
3794 "privilege"."member_id" NOTNULL OR
3795 "subscription"."member_id" NOTNULL )
3796 AND "ignored_initiative"."member_id" ISNULL
3797 AND NOT EXISTS (
3798 SELECT NULL FROM "draft"
3799 JOIN "ignored_member" ON
3800 "ignored_member"."member_id" = "recipient_id_p" AND
3801 "ignored_member"."other_member_id" = "draft"."author_id"
3802 WHERE "draft"."initiative_id" = "initiative"."id"
3803 )
3804 ORDER BY md5("seed_v" || '-' || "initiative"."id")
3805 LIMIT 1;
3806 IF FOUND THEN
3807 "match_v" := TRUE;
3808 RETURN NEXT "initiative_id_v";
3809 IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
3810 RETURN;
3811 END IF;
3812 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
3813 END IF;
3814 END LOOP;
3815 EXIT WHEN NOT "match_v";
3816 END LOOP;
3817 RETURN;
3818 END;
3819 $$;
3821 COMMENT ON FUNCTION "featured_initiative"
3822 ( "recipient_id_p" "member"."id"%TYPE,
3823 "area_id_p" "area"."id"%TYPE )
3824 IS 'Helper function for view "updated_or_featured_initiative"';
3827 CREATE VIEW "updated_or_featured_initiative" AS
3828 SELECT
3829 "subquery".*,
3830 NOT EXISTS (
3831 SELECT NULL FROM "initiative" AS "better_initiative"
3832 WHERE "better_initiative"."issue_id" = "initiative"."issue_id"
3833 AND
3834 ( COALESCE("better_initiative"."supporter_count", -1),
3835 -"better_initiative"."id" ) >
3836 ( COALESCE("initiative"."supporter_count", -1),
3837 -"initiative"."id" )
3838 ) AS "leading"
3839 FROM (
3840 SELECT * FROM "updated_initiative"
3841 UNION ALL
3842 SELECT
3843 "member"."id" AS "recipient_id",
3844 TRUE AS "featured",
3845 "featured_initiative_id" AS "initiative_id"
3846 FROM "member" CROSS JOIN "area"
3847 CROSS JOIN LATERAL
3848 "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id"
3849 JOIN "initiative" ON "initiative"."id" = "featured_initiative_id"
3850 ) AS "subquery"
3851 JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id";
3853 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';
3855 COMMENT ON COLUMN "updated_or_featured_initiative"."recipient_id" IS '"id" of the member who receives the notification mail';
3856 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")';
3857 COMMENT ON COLUMN "updated_or_featured_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
3858 COMMENT ON COLUMN "updated_or_featured_initiative"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue';
3861 CREATE VIEW "leading_complement_initiative" AS
3862 SELECT * FROM (
3863 SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id")
3864 "uf_initiative"."recipient_id",
3865 FALSE AS "featured",
3866 "uf_initiative"."initiative_id",
3867 TRUE AS "leading"
3868 FROM "updated_or_featured_initiative" AS "uf_initiative"
3869 JOIN "initiative" AS "uf_initiative_full" ON
3870 "uf_initiative_full"."id" = "uf_initiative"."initiative_id"
3871 JOIN "initiative" ON
3872 "initiative"."issue_id" = "uf_initiative_full"."issue_id"
3873 WHERE "initiative"."revoked" ISNULL
3874 ORDER BY
3875 "uf_initiative"."recipient_id",
3876 "initiative"."issue_id",
3877 "initiative"."supporter_count" DESC,
3878 "initiative"."id"
3879 ) AS "subquery"
3880 WHERE NOT EXISTS (
3881 SELECT NULL FROM "updated_or_featured_initiative" AS "other"
3882 WHERE "other"."recipient_id" = "subquery"."recipient_id"
3883 AND "other"."initiative_id" = "subquery"."initiative_id"
3884 );
3886 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';
3887 COMMENT ON COLUMN "leading_complement_initiative"."featured" IS 'Always FALSE in this view';
3888 COMMENT ON COLUMN "leading_complement_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
3889 COMMENT ON COLUMN "leading_complement_initiative"."leading" IS 'Always TRUE in this view';
3892 CREATE VIEW "unfiltered_initiative_for_notification" AS
3893 SELECT
3894 "subquery".*,
3895 "supporter"."member_id" NOTNULL AS "supported",
3896 CASE WHEN "supporter"."member_id" NOTNULL THEN
3897 EXISTS (
3898 SELECT NULL FROM "draft"
3899 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
3900 AND "draft"."id" > "supporter"."draft_id"
3901 )
3902 ELSE
3903 EXISTS (
3904 SELECT NULL FROM "draft"
3905 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
3906 AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE)
3907 )
3908 END AS "new_draft",
3909 CASE WHEN "supporter"."member_id" NOTNULL THEN
3910 ( SELECT count(1) FROM "suggestion"
3911 LEFT JOIN "opinion" ON
3912 "opinion"."member_id" = "supporter"."member_id" AND
3913 "opinion"."suggestion_id" = "suggestion"."id"
3914 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
3915 AND "opinion"."member_id" ISNULL
3916 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
3917 )
3918 ELSE
3919 ( SELECT count(1) FROM "suggestion"
3920 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
3921 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
3922 )
3923 END AS "new_suggestion_count"
3924 FROM (
3925 SELECT * FROM "updated_or_featured_initiative"
3926 UNION ALL
3927 SELECT * FROM "leading_complement_initiative"
3928 ) AS "subquery"
3929 LEFT JOIN "supporter" ON
3930 "supporter"."member_id" = "subquery"."recipient_id" AND
3931 "supporter"."initiative_id" = "subquery"."initiative_id"
3932 LEFT JOIN "notification_initiative_sent" AS "sent" ON
3933 "sent"."member_id" = "subquery"."recipient_id" AND
3934 "sent"."initiative_id" = "subquery"."initiative_id";
3936 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';
3938 COMMENT ON COLUMN "unfiltered_initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient';
3939 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)';
3940 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")';
3943 CREATE VIEW "initiative_for_notification" AS
3944 SELECT "unfiltered1".*
3945 FROM "unfiltered_initiative_for_notification" "unfiltered1"
3946 JOIN "initiative" AS "initiative1" ON
3947 "initiative1"."id" = "unfiltered1"."initiative_id"
3948 JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id"
3949 WHERE EXISTS (
3950 SELECT NULL
3951 FROM "unfiltered_initiative_for_notification" "unfiltered2"
3952 JOIN "initiative" AS "initiative2" ON
3953 "initiative2"."id" = "unfiltered2"."initiative_id"
3954 JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id"
3955 WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"
3956 AND "issue1"."area_id" = "issue2"."area_id"
3957 AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 )
3958 );
3960 COMMENT ON VIEW "initiative_for_notification" IS 'Initiatives to be included in a scheduled notification mail';
3962 COMMENT ON COLUMN "initiative_for_notification"."recipient_id" IS '"id" of the member who receives the notification mail';
3963 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")';
3964 COMMENT ON COLUMN "initiative_for_notification"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
3965 COMMENT ON COLUMN "initiative_for_notification"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue';
3966 COMMENT ON COLUMN "initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient';
3967 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)';
3968 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")';
3971 CREATE VIEW "scheduled_notification_to_send" AS
3972 SELECT * FROM (
3973 SELECT
3974 "id" AS "recipient_id",
3975 now() - CASE WHEN "notification_dow" ISNULL THEN
3976 ( "notification_sent"::DATE + CASE
3977 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
3978 THEN 0 ELSE 1 END
3979 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
3980 ELSE
3981 ( "notification_sent"::DATE +
3982 ( 7 + "notification_dow" -
3983 EXTRACT(DOW FROM
3984 ( "notification_sent"::DATE + CASE
3985 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
3986 THEN 0 ELSE 1 END
3987 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
3988 )::INTEGER
3989 ) % 7 +
3990 CASE
3991 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
3992 THEN 0 ELSE 1
3993 END
3994 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
3995 END AS "pending"
3996 FROM (
3997 SELECT
3998 "id",
3999 COALESCE("notification_sent", "activated") AS "notification_sent",
4000 "notification_dow",
4001 "notification_hour"
4002 FROM "member_to_notify"
4003 WHERE "notification_hour" NOTNULL
4004 ) AS "subquery1"
4005 ) AS "subquery2"
4006 WHERE "pending" > '0'::INTERVAL;
4008 COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending';
4010 COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail';
4011 COMMENT ON COLUMN "scheduled_notification_to_send"."pending" IS 'Duration for which the notification mail has already been pending';
4014 CREATE VIEW "newsletter_to_send" AS
4015 SELECT
4016 "member"."id" AS "recipient_id",
4017 "newsletter"."id" AS "newsletter_id",
4018 "newsletter"."published"
4019 FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"
4020 LEFT JOIN "privilege" ON
4021 "privilege"."member_id" = "member"."id" AND
4022 "privilege"."unit_id" = "newsletter"."unit_id" AND
4023 "privilege"."voting_right" = TRUE
4024 LEFT JOIN "subscription" ON
4025 "subscription"."member_id" = "member"."id" AND
4026 "subscription"."unit_id" = "newsletter"."unit_id"
4027 WHERE "newsletter"."published" <= now()
4028 AND "newsletter"."sent" ISNULL
4029 AND (
4030 "member"."disable_notifications" = FALSE OR
4031 "newsletter"."include_all_members" = TRUE )
4032 AND (
4033 "newsletter"."unit_id" ISNULL OR
4034 "privilege"."member_id" NOTNULL OR
4035 "subscription"."member_id" NOTNULL );
4037 COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';
4039 COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter was supposed to be sent out (can be used for ordering)';
4043 ------------------------------------------------------
4044 -- Row set returning function for delegation chains --
4045 ------------------------------------------------------
4048 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
4049 ('first', 'intermediate', 'last', 'repetition');
4051 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
4054 CREATE TYPE "delegation_chain_row" AS (
4055 "index" INT4,
4056 "member_id" INT4,
4057 "member_valid" BOOLEAN,
4058 "participation" BOOLEAN,
4059 "overridden" BOOLEAN,
4060 "scope_in" "delegation_scope",
4061 "scope_out" "delegation_scope",
4062 "disabled_out" BOOLEAN,
4063 "loop" "delegation_chain_loop_tag" );
4065 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
4067 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
4068 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest; for area and global delegation chains: always null';
4069 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
4070 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
4071 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
4072 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
4073 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
4076 CREATE FUNCTION "delegation_chain_for_closed_issue"
4077 ( "member_id_p" "member"."id"%TYPE,
4078 "issue_id_p" "issue"."id"%TYPE )
4079 RETURNS SETOF "delegation_chain_row"
4080 LANGUAGE 'plpgsql' STABLE AS $$
4081 DECLARE
4082 "output_row" "delegation_chain_row";
4083 "direct_voter_row" "direct_voter"%ROWTYPE;
4084 "delegating_voter_row" "delegating_voter"%ROWTYPE;
4085 BEGIN
4086 "output_row"."index" := 0;
4087 "output_row"."member_id" := "member_id_p";
4088 "output_row"."member_valid" := TRUE;
4089 "output_row"."participation" := FALSE;
4090 "output_row"."overridden" := FALSE;
4091 "output_row"."disabled_out" := FALSE;
4092 LOOP
4093 SELECT INTO "direct_voter_row" * FROM "direct_voter"
4094 WHERE "issue_id" = "issue_id_p"
4095 AND "member_id" = "output_row"."member_id";
4096 IF "direct_voter_row"."member_id" NOTNULL THEN
4097 "output_row"."participation" := TRUE;
4098 "output_row"."scope_out" := NULL;
4099 "output_row"."disabled_out" := NULL;
4100 RETURN NEXT "output_row";
4101 RETURN;
4102 END IF;
4103 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
4104 WHERE "issue_id" = "issue_id_p"
4105 AND "member_id" = "output_row"."member_id";
4106 IF "delegating_voter_row"."member_id" ISNULL THEN
4107 RETURN;
4108 END IF;
4109 "output_row"."scope_out" := "delegating_voter_row"."scope";
4110 RETURN NEXT "output_row";
4111 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
4112 "output_row"."scope_in" := "output_row"."scope_out";
4113 END LOOP;
4114 END;
4115 $$;
4117 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
4118 ( "member"."id"%TYPE,
4119 "member"."id"%TYPE )
4120 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
4123 CREATE FUNCTION "delegation_chain"
4124 ( "member_id_p" "member"."id"%TYPE,
4125 "unit_id_p" "unit"."id"%TYPE,
4126 "area_id_p" "area"."id"%TYPE,
4127 "issue_id_p" "issue"."id"%TYPE,
4128 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
4129 "simulate_default_p" BOOLEAN DEFAULT FALSE )
4130 RETURNS SETOF "delegation_chain_row"
4131 LANGUAGE 'plpgsql' STABLE AS $$
4132 DECLARE
4133 "scope_v" "delegation_scope";
4134 "unit_id_v" "unit"."id"%TYPE;
4135 "area_id_v" "area"."id"%TYPE;
4136 "issue_row" "issue"%ROWTYPE;
4137 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
4138 "loop_member_id_v" "member"."id"%TYPE;
4139 "output_row" "delegation_chain_row";
4140 "output_rows" "delegation_chain_row"[];
4141 "simulate_v" BOOLEAN;
4142 "simulate_here_v" BOOLEAN;
4143 "delegation_row" "delegation"%ROWTYPE;
4144 "row_count" INT4;
4145 "i" INT4;
4146 "loop_v" BOOLEAN;
4147 BEGIN
4148 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
4149 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
4150 END IF;
4151 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
4152 "simulate_v" := TRUE;
4153 ELSE
4154 "simulate_v" := FALSE;
4155 END IF;
4156 IF
4157 "unit_id_p" NOTNULL AND
4158 "area_id_p" ISNULL AND
4159 "issue_id_p" ISNULL
4160 THEN
4161 "scope_v" := 'unit';
4162 "unit_id_v" := "unit_id_p";
4163 ELSIF
4164 "unit_id_p" ISNULL AND
4165 "area_id_p" NOTNULL AND
4166 "issue_id_p" ISNULL
4167 THEN
4168 "scope_v" := 'area';
4169 "area_id_v" := "area_id_p";
4170 SELECT "unit_id" INTO "unit_id_v"
4171 FROM "area" WHERE "id" = "area_id_v";
4172 ELSIF
4173 "unit_id_p" ISNULL AND
4174 "area_id_p" ISNULL AND
4175 "issue_id_p" NOTNULL
4176 THEN
4177 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
4178 IF "issue_row"."id" ISNULL THEN
4179 RETURN;
4180 END IF;
4181 IF "issue_row"."closed" NOTNULL THEN
4182 IF "simulate_v" THEN
4183 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
4184 END IF;
4185 FOR "output_row" IN
4186 SELECT * FROM
4187 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
4188 LOOP
4189 RETURN NEXT "output_row";
4190 END LOOP;
4191 RETURN;
4192 END IF;
4193 "scope_v" := 'issue';
4194 SELECT "area_id" INTO "area_id_v"
4195 FROM "issue" WHERE "id" = "issue_id_p";
4196 SELECT "unit_id" INTO "unit_id_v"
4197 FROM "area" WHERE "id" = "area_id_v";
4198 ELSE
4199 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
4200 END IF;
4201 "visited_member_ids" := '{}';
4202 "loop_member_id_v" := NULL;
4203 "output_rows" := '{}';
4204 "output_row"."index" := 0;
4205 "output_row"."member_id" := "member_id_p";
4206 "output_row"."member_valid" := TRUE;
4207 "output_row"."participation" := FALSE;
4208 "output_row"."overridden" := FALSE;
4209 "output_row"."disabled_out" := FALSE;
4210 "output_row"."scope_out" := NULL;
4211 LOOP
4212 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
4213 "loop_member_id_v" := "output_row"."member_id";
4214 ELSE
4215 "visited_member_ids" :=
4216 "visited_member_ids" || "output_row"."member_id";
4217 END IF;
4218 IF "output_row"."participation" ISNULL THEN
4219 "output_row"."overridden" := NULL;
4220 ELSIF "output_row"."participation" THEN
4221 "output_row"."overridden" := TRUE;
4222 END IF;
4223 "output_row"."scope_in" := "output_row"."scope_out";
4224 "output_row"."member_valid" := EXISTS (
4225 SELECT NULL FROM "member" JOIN "privilege"
4226 ON "privilege"."member_id" = "member"."id"
4227 AND "privilege"."unit_id" = "unit_id_v"
4228 WHERE "id" = "output_row"."member_id"
4229 AND "member"."active" AND "privilege"."voting_right"
4230 );
4231 "simulate_here_v" := (
4232 "simulate_v" AND
4233 "output_row"."member_id" = "member_id_p"
4234 );
4235 "delegation_row" := ROW(NULL);
4236 IF "output_row"."member_valid" OR "simulate_here_v" THEN
4237 IF "scope_v" = 'unit' THEN
4238 IF NOT "simulate_here_v" THEN
4239 SELECT * INTO "delegation_row" FROM "delegation"
4240 WHERE "truster_id" = "output_row"."member_id"
4241 AND "unit_id" = "unit_id_v";
4242 END IF;
4243 ELSIF "scope_v" = 'area' THEN
4244 IF "simulate_here_v" THEN
4245 IF "simulate_trustee_id_p" ISNULL THEN
4246 SELECT * INTO "delegation_row" FROM "delegation"
4247 WHERE "truster_id" = "output_row"."member_id"
4248 AND "unit_id" = "unit_id_v";
4249 END IF;
4250 ELSE
4251 SELECT * INTO "delegation_row" FROM "delegation"
4252 WHERE "truster_id" = "output_row"."member_id"
4253 AND (
4254 "unit_id" = "unit_id_v" OR
4255 "area_id" = "area_id_v"
4256 )
4257 ORDER BY "scope" DESC;
4258 END IF;
4259 ELSIF "scope_v" = 'issue' THEN
4260 IF "issue_row"."fully_frozen" ISNULL THEN
4261 "output_row"."participation" := EXISTS (
4262 SELECT NULL FROM "interest"
4263 WHERE "issue_id" = "issue_id_p"
4264 AND "member_id" = "output_row"."member_id"
4265 );
4266 ELSE
4267 IF "output_row"."member_id" = "member_id_p" THEN
4268 "output_row"."participation" := EXISTS (
4269 SELECT NULL FROM "direct_voter"
4270 WHERE "issue_id" = "issue_id_p"
4271 AND "member_id" = "output_row"."member_id"
4272 );
4273 ELSE
4274 "output_row"."participation" := NULL;
4275 END IF;
4276 END IF;
4277 IF "simulate_here_v" THEN
4278 IF "simulate_trustee_id_p" ISNULL THEN
4279 SELECT * INTO "delegation_row" FROM "delegation"
4280 WHERE "truster_id" = "output_row"."member_id"
4281 AND (
4282 "unit_id" = "unit_id_v" OR
4283 "area_id" = "area_id_v"
4284 )
4285 ORDER BY "scope" DESC;
4286 END IF;
4287 ELSE
4288 SELECT * INTO "delegation_row" FROM "delegation"
4289 WHERE "truster_id" = "output_row"."member_id"
4290 AND (
4291 "unit_id" = "unit_id_v" OR
4292 "area_id" = "area_id_v" OR
4293 "issue_id" = "issue_id_p"
4294 )
4295 ORDER BY "scope" DESC;
4296 END IF;
4297 END IF;
4298 ELSE
4299 "output_row"."participation" := FALSE;
4300 END IF;
4301 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
4302 "output_row"."scope_out" := "scope_v";
4303 "output_rows" := "output_rows" || "output_row";
4304 "output_row"."member_id" := "simulate_trustee_id_p";
4305 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
4306 "output_row"."scope_out" := "delegation_row"."scope";
4307 "output_rows" := "output_rows" || "output_row";
4308 "output_row"."member_id" := "delegation_row"."trustee_id";
4309 ELSIF "delegation_row"."scope" NOTNULL THEN
4310 "output_row"."scope_out" := "delegation_row"."scope";
4311 "output_row"."disabled_out" := TRUE;
4312 "output_rows" := "output_rows" || "output_row";
4313 EXIT;
4314 ELSE
4315 "output_row"."scope_out" := NULL;
4316 "output_rows" := "output_rows" || "output_row";
4317 EXIT;
4318 END IF;
4319 EXIT WHEN "loop_member_id_v" NOTNULL;
4320 "output_row"."index" := "output_row"."index" + 1;
4321 END LOOP;
4322 "row_count" := array_upper("output_rows", 1);
4323 "i" := 1;
4324 "loop_v" := FALSE;
4325 LOOP
4326 "output_row" := "output_rows"["i"];
4327 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
4328 IF "loop_v" THEN
4329 IF "i" + 1 = "row_count" THEN
4330 "output_row"."loop" := 'last';
4331 ELSIF "i" = "row_count" THEN
4332 "output_row"."loop" := 'repetition';
4333 ELSE
4334 "output_row"."loop" := 'intermediate';
4335 END IF;
4336 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
4337 "output_row"."loop" := 'first';
4338 "loop_v" := TRUE;
4339 END IF;
4340 IF "scope_v" = 'unit' THEN
4341 "output_row"."participation" := NULL;
4342 END IF;
4343 RETURN NEXT "output_row";
4344 "i" := "i" + 1;
4345 END LOOP;
4346 RETURN;
4347 END;
4348 $$;
4350 COMMENT ON FUNCTION "delegation_chain"
4351 ( "member"."id"%TYPE,
4352 "unit"."id"%TYPE,
4353 "area"."id"%TYPE,
4354 "issue"."id"%TYPE,
4355 "member"."id"%TYPE,
4356 BOOLEAN )
4357 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
4361 ---------------------------------------------------------
4362 -- Single row returning function for delegation chains --
4363 ---------------------------------------------------------
4366 CREATE TYPE "delegation_info_loop_type" AS ENUM
4367 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
4369 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''';
4372 CREATE TYPE "delegation_info_type" AS (
4373 "own_participation" BOOLEAN,
4374 "own_delegation_scope" "delegation_scope",
4375 "first_trustee_id" INT4,
4376 "first_trustee_participation" BOOLEAN,
4377 "first_trustee_ellipsis" BOOLEAN,
4378 "other_trustee_id" INT4,
4379 "other_trustee_participation" BOOLEAN,
4380 "other_trustee_ellipsis" BOOLEAN,
4381 "delegation_loop" "delegation_info_loop_type",
4382 "participating_member_id" INT4 );
4384 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';
4386 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
4387 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
4388 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
4389 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
4390 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
4391 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
4392 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)';
4393 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
4394 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';
4395 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';
4398 CREATE FUNCTION "delegation_info"
4399 ( "member_id_p" "member"."id"%TYPE,
4400 "unit_id_p" "unit"."id"%TYPE,
4401 "area_id_p" "area"."id"%TYPE,
4402 "issue_id_p" "issue"."id"%TYPE,
4403 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
4404 "simulate_default_p" BOOLEAN DEFAULT FALSE )
4405 RETURNS "delegation_info_type"
4406 LANGUAGE 'plpgsql' STABLE AS $$
4407 DECLARE
4408 "current_row" "delegation_chain_row";
4409 "result" "delegation_info_type";
4410 BEGIN
4411 "result"."own_participation" := FALSE;
4412 FOR "current_row" IN
4413 SELECT * FROM "delegation_chain"(
4414 "member_id_p",
4415 "unit_id_p", "area_id_p", "issue_id_p",
4416 "simulate_trustee_id_p", "simulate_default_p")
4417 LOOP
4418 IF
4419 "result"."participating_member_id" ISNULL AND
4420 "current_row"."participation"
4421 THEN
4422 "result"."participating_member_id" := "current_row"."member_id";
4423 END IF;
4424 IF "current_row"."member_id" = "member_id_p" THEN
4425 "result"."own_participation" := "current_row"."participation";
4426 "result"."own_delegation_scope" := "current_row"."scope_out";
4427 IF "current_row"."loop" = 'first' THEN
4428 "result"."delegation_loop" := 'own';
4429 END IF;
4430 ELSIF
4431 "current_row"."member_valid" AND
4432 ( "current_row"."loop" ISNULL OR
4433 "current_row"."loop" != 'repetition' )
4434 THEN
4435 IF "result"."first_trustee_id" ISNULL THEN
4436 "result"."first_trustee_id" := "current_row"."member_id";
4437 "result"."first_trustee_participation" := "current_row"."participation";
4438 "result"."first_trustee_ellipsis" := FALSE;
4439 IF "current_row"."loop" = 'first' THEN
4440 "result"."delegation_loop" := 'first';
4441 END IF;
4442 ELSIF "result"."other_trustee_id" ISNULL THEN
4443 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
4444 "result"."other_trustee_id" := "current_row"."member_id";
4445 "result"."other_trustee_participation" := TRUE;
4446 "result"."other_trustee_ellipsis" := FALSE;
4447 IF "current_row"."loop" = 'first' THEN
4448 "result"."delegation_loop" := 'other';
4449 END IF;
4450 ELSE
4451 "result"."first_trustee_ellipsis" := TRUE;
4452 IF "current_row"."loop" = 'first' THEN
4453 "result"."delegation_loop" := 'first_ellipsis';
4454 END IF;
4455 END IF;
4456 ELSE
4457 "result"."other_trustee_ellipsis" := TRUE;
4458 IF "current_row"."loop" = 'first' THEN
4459 "result"."delegation_loop" := 'other_ellipsis';
4460 END IF;
4461 END IF;
4462 END IF;
4463 END LOOP;
4464 RETURN "result";
4465 END;
4466 $$;
4468 COMMENT ON FUNCTION "delegation_info"
4469 ( "member"."id"%TYPE,
4470 "unit"."id"%TYPE,
4471 "area"."id"%TYPE,
4472 "issue"."id"%TYPE,
4473 "member"."id"%TYPE,
4474 BOOLEAN )
4475 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
4479 ---------------------------
4480 -- Transaction isolation --
4481 ---------------------------
4484 CREATE FUNCTION "require_transaction_isolation"()
4485 RETURNS VOID
4486 LANGUAGE 'plpgsql' VOLATILE AS $$
4487 BEGIN
4488 IF
4489 current_setting('transaction_isolation') NOT IN
4490 ('repeatable read', 'serializable')
4491 THEN
4492 RAISE EXCEPTION 'Insufficient transaction isolation level' USING
4493 HINT = 'Consider using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ.';
4494 END IF;
4495 RETURN;
4496 END;
4497 $$;
4499 COMMENT ON FUNCTION "require_transaction_isolation"() IS 'Throws an exception, if transaction isolation level is too low to provide a consistent snapshot';
4502 CREATE FUNCTION "dont_require_transaction_isolation"()
4503 RETURNS VOID
4504 LANGUAGE 'plpgsql' VOLATILE AS $$
4505 BEGIN
4506 IF
4507 current_setting('transaction_isolation') IN
4508 ('repeatable read', 'serializable')
4509 THEN
4510 RAISE WARNING 'Unneccessary transaction isolation level: %',
4511 current_setting('transaction_isolation');
4512 END IF;
4513 RETURN;
4514 END;
4515 $$;
4517 COMMENT ON FUNCTION "dont_require_transaction_isolation"() IS 'Raises a warning, if transaction isolation level is higher than READ COMMITTED';
4521 -------------------------
4522 -- Notification system --
4523 -------------------------
4525 CREATE FUNCTION "get_initiatives_for_notification"
4526 ( "recipient_id_p" "member"."id"%TYPE )
4527 RETURNS SETOF "initiative_for_notification"
4528 LANGUAGE 'plpgsql' VOLATILE AS $$
4529 DECLARE
4530 "result_row" "initiative_for_notification"%ROWTYPE;
4531 "last_draft_id_v" "draft"."id"%TYPE;
4532 "last_suggestion_id_v" "suggestion"."id"%TYPE;
4533 BEGIN
4534 PERFORM "require_transaction_isolation"();
4535 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
4536 FOR "result_row" IN
4537 SELECT * FROM "initiative_for_notification"
4538 WHERE "recipient_id" = "recipient_id_p"
4539 LOOP
4540 SELECT "id" INTO "last_draft_id_v" FROM "draft"
4541 WHERE "draft"."initiative_id" = "result_row"."initiative_id"
4542 ORDER BY "id" DESC LIMIT 1;
4543 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
4544 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
4545 ORDER BY "id" DESC LIMIT 1;
4546 INSERT INTO "notification_initiative_sent"
4547 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
4548 VALUES (
4549 "recipient_id_p",
4550 "result_row"."initiative_id",
4551 "last_draft_id_v",
4552 "last_suggestion_id_v" )
4553 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
4554 "last_draft_id" = "last_draft_id_v",
4555 "last_suggestion_id" = "last_suggestion_id_v";
4556 RETURN NEXT "result_row";
4557 END LOOP;
4558 DELETE FROM "notification_initiative_sent"
4559 USING "initiative", "issue"
4560 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
4561 AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
4562 AND "issue"."id" = "initiative"."issue_id"
4563 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
4564 UPDATE "member" SET
4565 "notification_counter" = "notification_counter" + 1,
4566 "notification_sent" = now()
4567 WHERE "id" = "recipient_id_p";
4568 RETURN;
4569 END;
4570 $$;
4572 COMMENT ON FUNCTION "get_initiatives_for_notification"
4573 ( "member"."id"%TYPE )
4574 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';
4578 ------------------------------------------------------------------------
4579 -- Regular tasks, except calculcation of snapshots and voting results --
4580 ------------------------------------------------------------------------
4583 CREATE FUNCTION "check_activity"()
4584 RETURNS VOID
4585 LANGUAGE 'plpgsql' VOLATILE AS $$
4586 DECLARE
4587 "system_setting_row" "system_setting"%ROWTYPE;
4588 BEGIN
4589 PERFORM "dont_require_transaction_isolation"();
4590 SELECT * INTO "system_setting_row" FROM "system_setting";
4591 IF "system_setting_row"."member_ttl" NOTNULL THEN
4592 UPDATE "member" SET "active" = FALSE
4593 WHERE "active" = TRUE
4594 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
4595 END IF;
4596 RETURN;
4597 END;
4598 $$;
4600 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
4603 CREATE FUNCTION "calculate_member_counts"()
4604 RETURNS VOID
4605 LANGUAGE 'plpgsql' VOLATILE AS $$
4606 BEGIN
4607 PERFORM "require_transaction_isolation"();
4608 DELETE FROM "member_count";
4609 INSERT INTO "member_count" ("total_count")
4610 SELECT "total_count" FROM "member_count_view";
4611 UPDATE "unit" SET "member_count" = "view"."member_count"
4612 FROM "unit_member_count" AS "view"
4613 WHERE "view"."unit_id" = "unit"."id";
4614 RETURN;
4615 END;
4616 $$;
4618 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"';
4621 CREATE FUNCTION "calculate_area_quorum"()
4622 RETURNS VOID
4623 LANGUAGE 'plpgsql' VOLATILE AS $$
4624 BEGIN
4625 PERFORM "dont_require_transaction_isolation"();
4626 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
4627 FROM "area_quorum" AS "view"
4628 WHERE "view"."area_id" = "area"."id";
4629 RETURN;
4630 END;
4631 $$;
4633 COMMENT ON FUNCTION "calculate_area_quorum"() IS 'Calculate column "issue_quorum" in table "area" from view "area_quorum"';
4637 ------------------------------------
4638 -- Calculation of harmonic weight --
4639 ------------------------------------
4642 CREATE VIEW "remaining_harmonic_supporter_weight" AS
4643 SELECT
4644 "direct_interest_snapshot"."snapshot_id",
4645 "direct_interest_snapshot"."issue_id",
4646 "direct_interest_snapshot"."member_id",
4647 "direct_interest_snapshot"."weight" AS "weight_num",
4648 count("initiative"."id") AS "weight_den"
4649 FROM "issue"
4650 JOIN "direct_interest_snapshot"
4651 ON "issue"."latest_snapshot_id" = "direct_interest_snapshot"."snapshot_id"
4652 AND "issue"."id" = "direct_interest_snapshot"."issue_id"
4653 JOIN "initiative"
4654 ON "issue"."id" = "initiative"."issue_id"
4655 AND "initiative"."harmonic_weight" ISNULL
4656 JOIN "direct_supporter_snapshot"
4657 ON "issue"."latest_snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
4658 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
4659 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
4660 AND (
4661 "direct_supporter_snapshot"."satisfied" = TRUE OR
4662 coalesce("initiative"."admitted", FALSE) = FALSE
4663 )
4664 GROUP BY
4665 "direct_interest_snapshot"."snapshot_id",
4666 "direct_interest_snapshot"."issue_id",
4667 "direct_interest_snapshot"."member_id",
4668 "direct_interest_snapshot"."weight";
4670 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
4673 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
4674 SELECT
4675 "initiative"."issue_id",
4676 "initiative"."id" AS "initiative_id",
4677 "initiative"."admitted",
4678 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
4679 "remaining_harmonic_supporter_weight"."weight_den"
4680 FROM "remaining_harmonic_supporter_weight"
4681 JOIN "initiative"
4682 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
4683 AND "initiative"."harmonic_weight" ISNULL
4684 JOIN "direct_supporter_snapshot"
4685 ON "remaining_harmonic_supporter_weight"."snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
4686 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
4687 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
4688 AND (
4689 "direct_supporter_snapshot"."satisfied" = TRUE OR
4690 coalesce("initiative"."admitted", FALSE) = FALSE
4691 )
4692 GROUP BY
4693 "initiative"."issue_id",
4694 "initiative"."id",
4695 "initiative"."admitted",
4696 "remaining_harmonic_supporter_weight"."weight_den";
4698 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
4701 CREATE VIEW "remaining_harmonic_initiative_weight_dummies" AS
4702 SELECT
4703 "issue_id",
4704 "id" AS "initiative_id",
4705 "admitted",
4706 0 AS "weight_num",
4707 1 AS "weight_den"
4708 FROM "initiative"
4709 WHERE "harmonic_weight" ISNULL;
4711 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';
4714 CREATE FUNCTION "set_harmonic_initiative_weights"
4715 ( "issue_id_p" "issue"."id"%TYPE )
4716 RETURNS VOID
4717 LANGUAGE 'plpgsql' VOLATILE AS $$
4718 DECLARE
4719 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
4720 "i" INT4;
4721 "count_v" INT4;
4722 "summand_v" FLOAT;
4723 "id_ary" INT4[];
4724 "weight_ary" FLOAT[];
4725 "min_weight_v" FLOAT;
4726 BEGIN
4727 PERFORM "require_transaction_isolation"();
4728 UPDATE "initiative" SET "harmonic_weight" = NULL
4729 WHERE "issue_id" = "issue_id_p";
4730 LOOP
4731 "min_weight_v" := NULL;
4732 "i" := 0;
4733 "count_v" := 0;
4734 FOR "weight_row" IN
4735 SELECT * FROM "remaining_harmonic_initiative_weight_summands"
4736 WHERE "issue_id" = "issue_id_p"
4737 AND (
4738 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
4739 SELECT NULL FROM "initiative"
4740 WHERE "issue_id" = "issue_id_p"
4741 AND "harmonic_weight" ISNULL
4742 AND coalesce("admitted", FALSE) = FALSE
4743 )
4744 )
4745 UNION ALL -- needed for corner cases
4746 SELECT * FROM "remaining_harmonic_initiative_weight_dummies"
4747 WHERE "issue_id" = "issue_id_p"
4748 AND (
4749 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
4750 SELECT NULL FROM "initiative"
4751 WHERE "issue_id" = "issue_id_p"
4752 AND "harmonic_weight" ISNULL
4753 AND coalesce("admitted", FALSE) = FALSE
4754 )
4755 )
4756 ORDER BY "initiative_id" DESC, "weight_den" DESC
4757 -- NOTE: non-admitted initiatives placed first (at last positions),
4758 -- latest initiatives treated worse in case of tie
4759 LOOP
4760 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
4761 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
4762 "i" := "i" + 1;
4763 "count_v" := "i";
4764 "id_ary"["i"] := "weight_row"."initiative_id";
4765 "weight_ary"["i"] := "summand_v";
4766 ELSE
4767 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
4768 END IF;
4769 END LOOP;
4770 EXIT WHEN "count_v" = 0;
4771 "i" := 1;
4772 LOOP
4773 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
4774 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
4775 "min_weight_v" := "weight_ary"["i"];
4776 END IF;
4777 "i" := "i" + 1;
4778 EXIT WHEN "i" > "count_v";
4779 END LOOP;
4780 "i" := 1;
4781 LOOP
4782 IF "weight_ary"["i"] = "min_weight_v" THEN
4783 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
4784 WHERE "id" = "id_ary"["i"];
4785 EXIT;
4786 END IF;
4787 "i" := "i" + 1;
4788 END LOOP;
4789 END LOOP;
4790 UPDATE "initiative" SET "harmonic_weight" = 0
4791 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
4792 END;
4793 $$;
4795 COMMENT ON FUNCTION "set_harmonic_initiative_weights"
4796 ( "issue"."id"%TYPE )
4797 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
4801 ------------------------------
4802 -- Calculation of snapshots --
4803 ------------------------------
4806 CREATE FUNCTION "weight_of_added_delegations_for_snapshot"
4807 ( "snapshot_id_p" "snapshot"."id"%TYPE,
4808 "issue_id_p" "issue"."id"%TYPE,
4809 "member_id_p" "member"."id"%TYPE,
4810 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
4811 RETURNS "direct_interest_snapshot"."weight"%TYPE
4812 LANGUAGE 'plpgsql' VOLATILE AS $$
4813 DECLARE
4814 "issue_delegation_row" "issue_delegation"%ROWTYPE;
4815 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
4816 "weight_v" INT4;
4817 "sub_weight_v" INT4;
4818 BEGIN
4819 PERFORM "require_transaction_isolation"();
4820 "weight_v" := 0;
4821 FOR "issue_delegation_row" IN
4822 SELECT * FROM "issue_delegation"
4823 WHERE "trustee_id" = "member_id_p"
4824 AND "issue_id" = "issue_id_p"
4825 LOOP
4826 IF NOT EXISTS (
4827 SELECT NULL FROM "direct_interest_snapshot"
4828 WHERE "snapshot_id" = "snapshot_id_p"
4829 AND "issue_id" = "issue_id_p"
4830 AND "member_id" = "issue_delegation_row"."truster_id"
4831 ) AND NOT EXISTS (
4832 SELECT NULL FROM "delegating_interest_snapshot"
4833 WHERE "snapshot_id" = "snapshot_id_p"
4834 AND "issue_id" = "issue_id_p"
4835 AND "member_id" = "issue_delegation_row"."truster_id"
4836 ) THEN
4837 "delegate_member_ids_v" :=
4838 "member_id_p" || "delegate_member_ids_p";
4839 INSERT INTO "delegating_interest_snapshot" (
4840 "snapshot_id",
4841 "issue_id",
4842 "member_id",
4843 "scope",
4844 "delegate_member_ids"
4845 ) VALUES (
4846 "snapshot_id_p",
4847 "issue_id_p",
4848 "issue_delegation_row"."truster_id",
4849 "issue_delegation_row"."scope",
4850 "delegate_member_ids_v"
4851 );
4852 "sub_weight_v" := 1 +
4853 "weight_of_added_delegations_for_snapshot"(
4854 "snapshot_id_p",
4855 "issue_id_p",
4856 "issue_delegation_row"."truster_id",
4857 "delegate_member_ids_v"
4858 );
4859 UPDATE "delegating_interest_snapshot"
4860 SET "weight" = "sub_weight_v"
4861 WHERE "snapshot_id" = "snapshot_id_p"
4862 AND "issue_id" = "issue_id_p"
4863 AND "member_id" = "issue_delegation_row"."truster_id";
4864 "weight_v" := "weight_v" + "sub_weight_v";
4865 END IF;
4866 END LOOP;
4867 RETURN "weight_v";
4868 END;
4869 $$;
4871 COMMENT ON FUNCTION "weight_of_added_delegations_for_snapshot"
4872 ( "snapshot"."id"%TYPE,
4873 "issue"."id"%TYPE,
4874 "member"."id"%TYPE,
4875 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
4876 IS 'Helper function for "fill_snapshot" function';
4879 CREATE FUNCTION "take_snapshot"
4880 ( "issue_id_p" "issue"."id"%TYPE,
4881 "area_id_p" "area"."id"%TYPE = NULL )
4882 RETURNS "snapshot"."id"%TYPE
4883 LANGUAGE 'plpgsql' VOLATILE AS $$
4884 DECLARE
4885 "area_id_v" "area"."id"%TYPE;
4886 "unit_id_v" "unit"."id"%TYPE;
4887 "snapshot_id_v" "snapshot"."id"%TYPE;
4888 "issue_id_v" "issue"."id"%TYPE;
4889 "member_id_v" "member"."id"%TYPE;
4890 BEGIN
4891 IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN
4892 RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL';
4893 END IF;
4894 PERFORM "require_transaction_isolation"();
4895 IF "issue_id_p" ISNULL THEN
4896 "area_id_v" := "area_id_p";
4897 ELSE
4898 SELECT "area_id" INTO "area_id_v"
4899 FROM "issue" WHERE "id" = "issue_id_p";
4900 END IF;
4901 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_p";
4902 INSERT INTO "snapshot" ("area_id", "issue_id")
4903 VALUES ("area_id_v", "issue_id_p")
4904 RETURNING "id" INTO "snapshot_id_v";
4905 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
4906 SELECT "snapshot_id_v", "member_id"
4907 FROM "unit_member" WHERE "unit_id" = "unit_id_v";
4908 UPDATE "snapshot" SET
4909 "population" = (
4910 SELECT count(1) FROM "snapshot_population"
4911 WHERE "snapshot_id" = "snapshot_id_v"
4912 ) WHERE "id" = "snapshot_id_v";
4913 FOR "issue_id_v" IN
4914 SELECT "id" FROM "issue"
4915 WHERE CASE WHEN "issue_id_p" ISNULL THEN
4916 "area_id" = "area_id_p" AND
4917 "state" = 'admission'
4918 ELSE
4919 "id" = "issue_id_p"
4920 END
4921 LOOP
4922 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
4923 VALUES ("snapshot_id_v", "issue_id_v");
4924 INSERT INTO "direct_interest_snapshot"
4925 ("snapshot_id", "issue_id", "member_id")
4926 SELECT
4927 "snapshot_id_v" AS "snapshot_id",
4928 "issue_id_v" AS "issue_id",
4929 "member"."id" AS "member_id"
4930 FROM "issue"
4931 JOIN "area" ON "issue"."area_id" = "area"."id"
4932 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
4933 JOIN "member" ON "interest"."member_id" = "member"."id"
4934 JOIN "privilege"
4935 ON "privilege"."unit_id" = "area"."unit_id"
4936 AND "privilege"."member_id" = "member"."id"
4937 WHERE "issue"."id" = "issue_id_v"
4938 AND "member"."active" AND "privilege"."voting_right";
4939 FOR "member_id_v" IN
4940 SELECT "member_id" FROM "direct_interest_snapshot"
4941 WHERE "snapshot_id" = "snapshot_id_v"
4942 AND "issue_id" = "issue_id_v"
4943 LOOP
4944 UPDATE "direct_interest_snapshot" SET
4945 "weight" = 1 +
4946 "weight_of_added_delegations_for_snapshot"(
4947 "snapshot_id_v",
4948 "issue_id_v",
4949 "member_id_v",
4950 '{}'
4951 )
4952 WHERE "snapshot_id" = "snapshot_id_v"
4953 AND "issue_id" = "issue_id_v"
4954 AND "member_id" = "member_id_v";
4955 END LOOP;
4956 INSERT INTO "direct_supporter_snapshot"
4957 ( "snapshot_id", "issue_id", "initiative_id", "member_id",
4958 "draft_id", "informed", "satisfied" )
4959 SELECT
4960 "snapshot_id_v" AS "snapshot_id",
4961 "issue_id_v" AS "issue_id",
4962 "initiative"."id" AS "initiative_id",
4963 "supporter"."member_id" AS "member_id",
4964 "supporter"."draft_id" AS "draft_id",
4965 "supporter"."draft_id" = "current_draft"."id" AS "informed",
4966 NOT EXISTS (
4967 SELECT NULL FROM "critical_opinion"
4968 WHERE "initiative_id" = "initiative"."id"
4969 AND "member_id" = "supporter"."member_id"
4970 ) AS "satisfied"
4971 FROM "initiative"
4972 JOIN "supporter"
4973 ON "supporter"."initiative_id" = "initiative"."id"
4974 JOIN "current_draft"
4975 ON "initiative"."id" = "current_draft"."initiative_id"
4976 JOIN "direct_interest_snapshot"
4977 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"
4978 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"
4979 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
4980 WHERE "initiative"."issue_id" = "issue_id_v";
4981 DELETE FROM "temporary_suggestion_counts";
4982 INSERT INTO "temporary_suggestion_counts"
4983 ( "id",
4984 "minus2_unfulfilled_count", "minus2_fulfilled_count",
4985 "minus1_unfulfilled_count", "minus1_fulfilled_count",
4986 "plus1_unfulfilled_count", "plus1_fulfilled_count",
4987 "plus2_unfulfilled_count", "plus2_fulfilled_count" )
4988 SELECT
4989 "suggestion"."id",
4990 ( SELECT coalesce(sum("di"."weight"), 0)
4991 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
4992 ON "di"."snapshot_id" = "snapshot_id_v"
4993 AND "di"."issue_id" = "issue_id_v"
4994 AND "di"."member_id" = "opinion"."member_id"
4995 WHERE "opinion"."suggestion_id" = "suggestion"."id"
4996 AND "opinion"."degree" = -2
4997 AND "opinion"."fulfilled" = FALSE
4998 ) AS "minus2_unfulfilled_count",
4999 ( SELECT coalesce(sum("di"."weight"), 0)
5000 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
5001 ON "di"."snapshot_id" = "snapshot_id_v"
5002 AND "di"."issue_id" = "issue_id_v"
5003 AND "di"."member_id" = "opinion"."member_id"
5004 WHERE "opinion"."suggestion_id" = "suggestion"."id"
5005 AND "opinion"."degree" = -2
5006 AND "opinion"."fulfilled" = TRUE
5007 ) AS "minus2_fulfilled_count",
5008 ( SELECT coalesce(sum("di"."weight"), 0)
5009 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
5010 ON "di"."snapshot_id" = "snapshot_id_v"
5011 AND "di"."issue_id" = "issue_id_v"
5012 AND "di"."member_id" = "opinion"."member_id"
5013 WHERE "opinion"."suggestion_id" = "suggestion"."id"
5014 AND "opinion"."degree" = -1
5015 AND "opinion"."fulfilled" = FALSE
5016 ) AS "minus1_unfulfilled_count",
5017 ( SELECT coalesce(sum("di"."weight"), 0)
5018 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
5019 ON "di"."snapshot_id" = "snapshot_id_v"
5020 AND "di"."issue_id" = "issue_id_v"
5021 AND "di"."member_id" = "opinion"."member_id"
5022 WHERE "opinion"."suggestion_id" = "suggestion"."id"
5023 AND "opinion"."degree" = -1
5024 AND "opinion"."fulfilled" = TRUE
5025 ) AS "minus1_fulfilled_count",
5026 ( SELECT coalesce(sum("di"."weight"), 0)
5027 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
5028 ON "di"."snapshot_id" = "snapshot_id_v"
5029 AND "di"."issue_id" = "issue_id_v"
5030 AND "di"."member_id" = "opinion"."member_id"
5031 WHERE "opinion"."suggestion_id" = "suggestion"."id"
5032 AND "opinion"."degree" = 1
5033 AND "opinion"."fulfilled" = FALSE
5034 ) AS "plus1_unfulfilled_count",
5035 ( SELECT coalesce(sum("di"."weight"), 0)
5036 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
5037 ON "di"."snapshot_id" = "snapshot_id_v"
5038 AND "di"."issue_id" = "issue_id_v"
5039 AND "di"."member_id" = "opinion"."member_id"
5040 WHERE "opinion"."suggestion_id" = "suggestion"."id"
5041 AND "opinion"."degree" = 1
5042 AND "opinion"."fulfilled" = TRUE
5043 ) AS "plus1_fulfilled_count",
5044 ( SELECT coalesce(sum("di"."weight"), 0)
5045 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
5046 ON "di"."snapshot_id" = "snapshot_id_v"
5047 AND "di"."issue_id" = "issue_id_v"
5048 AND "di"."member_id" = "opinion"."member_id"
5049 WHERE "opinion"."suggestion_id" = "suggestion"."id"
5050 AND "opinion"."degree" = 2
5051 AND "opinion"."fulfilled" = FALSE
5052 ) AS "plus2_unfulfilled_count",
5053 ( SELECT coalesce(sum("di"."weight"), 0)
5054 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
5055 ON "di"."snapshot_id" = "snapshot_id_v"
5056 AND "di"."issue_id" = "issue_id_v"
5057 AND "di"."member_id" = "opinion"."member_id"
5058 WHERE "opinion"."suggestion_id" = "suggestion"."id"
5059 AND "opinion"."degree" = 2
5060 AND "opinion"."fulfilled" = TRUE
5061 ) AS "plus2_fulfilled_count"
5062 FROM "suggestion" JOIN "initiative"
5063 ON "suggestion"."initiative_id" = "initiative"."id"
5064 WHERE "initiative"."issue_id" = "issue_id_v";
5065 END LOOP;
5066 RETURN "snapshot_id_v";
5067 END;
5068 $$;
5070 COMMENT ON FUNCTION "take_snapshot"
5071 ( "issue"."id"%TYPE,
5072 "area"."id"%TYPE )
5073 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.';
5076 CREATE FUNCTION "finish_snapshot"
5077 ( "issue_id_p" "issue"."id"%TYPE )
5078 RETURNS VOID
5079 LANGUAGE 'plpgsql' VOLATILE AS $$
5080 DECLARE
5081 "snapshot_id_v" "snapshot"."id"%TYPE;
5082 BEGIN
5083 -- NOTE: function does not require snapshot isolation but we don't call
5084 -- "dont_require_snapshot_isolation" here because this function is
5085 -- also invoked by "check_issue"
5086 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
5087 SELECT "id" INTO "snapshot_id_v" FROM "snapshot"
5088 ORDER BY "id" DESC LIMIT 1;
5089 UPDATE "issue" SET
5090 "calculated" = "snapshot"."calculated",
5091 "latest_snapshot_id" = "snapshot_id_v",
5092 "population" = "snapshot"."population"
5093 FROM "snapshot"
5094 WHERE "issue"."id" = "issue_id_p"
5095 AND "snapshot"."id" = "snapshot_id_v";
5096 UPDATE "initiative" SET
5097 "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 ),
5107 "informed_supporter_count" = (
5108 SELECT coalesce(sum("di"."weight"), 0)
5109 FROM "direct_interest_snapshot" AS "di"
5110 JOIN "direct_supporter_snapshot" AS "ds"
5111 ON "di"."member_id" = "ds"."member_id"
5112 WHERE "di"."snapshot_id" = "snapshot_id_v"
5113 AND "di"."issue_id" = "issue_id_p"
5114 AND "ds"."snapshot_id" = "snapshot_id_v"
5115 AND "ds"."initiative_id" = "initiative"."id"
5116 AND "ds"."informed"
5117 ),
5118 "satisfied_supporter_count" = (
5119 SELECT coalesce(sum("di"."weight"), 0)
5120 FROM "direct_interest_snapshot" AS "di"
5121 JOIN "direct_supporter_snapshot" AS "ds"
5122 ON "di"."member_id" = "ds"."member_id"
5123 WHERE "di"."snapshot_id" = "snapshot_id_v"
5124 AND "di"."issue_id" = "issue_id_p"
5125 AND "ds"."snapshot_id" = "snapshot_id_v"
5126 AND "ds"."initiative_id" = "initiative"."id"
5127 AND "ds"."satisfied"
5128 ),
5129 "satisfied_informed_supporter_count" = (
5130 SELECT coalesce(sum("di"."weight"), 0)
5131 FROM "direct_interest_snapshot" AS "di"
5132 JOIN "direct_supporter_snapshot" AS "ds"
5133 ON "di"."member_id" = "ds"."member_id"
5134 WHERE "di"."snapshot_id" = "snapshot_id_v"
5135 AND "di"."issue_id" = "issue_id_p"
5136 AND "ds"."snapshot_id" = "snapshot_id_v"
5137 AND "ds"."initiative_id" = "initiative"."id"
5138 AND "ds"."informed"
5139 AND "ds"."satisfied"
5140 )
5141 WHERE "issue_id" = "issue_id_p";
5142 UPDATE "suggestion" SET
5143 "minus2_unfulfilled_count" = "temp"."minus2_unfulfilled_count",
5144 "minus2_fulfilled_count" = "temp"."minus2_fulfilled_count",
5145 "minus1_unfulfilled_count" = "temp"."minus1_unfulfilled_count",
5146 "minus1_fulfilled_count" = "temp"."minus1_fulfilled_count",
5147 "plus1_unfulfilled_count" = "temp"."plus1_unfulfilled_count",
5148 "plus1_fulfilled_count" = "temp"."plus1_fulfilled_count",
5149 "plus2_unfulfilled_count" = "temp"."plus2_unfulfilled_count",
5150 "plus2_fulfilled_count" = "temp"."plus2_fulfilled_count"
5151 FROM "temporary_suggestion_counts" AS "temp", "initiative"
5152 WHERE "temp"."id" = "suggestion"."id"
5153 AND "initiative"."issue_id" = "issue_id_p"
5154 AND "suggestion"."initiative_id" = "initiative"."id";
5155 DELETE FROM "temporary_suggestion_counts";
5156 RETURN;
5157 END;
5158 $$;
5160 COMMENT ON FUNCTION "finish_snapshot"
5161 ( "issue"."id"%TYPE )
5162 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)';
5166 -----------------------
5167 -- Counting of votes --
5168 -----------------------
5171 CREATE FUNCTION "weight_of_added_vote_delegations"
5172 ( "issue_id_p" "issue"."id"%TYPE,
5173 "member_id_p" "member"."id"%TYPE,
5174 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
5175 RETURNS "direct_voter"."weight"%TYPE
5176 LANGUAGE 'plpgsql' VOLATILE AS $$
5177 DECLARE
5178 "issue_delegation_row" "issue_delegation"%ROWTYPE;
5179 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
5180 "weight_v" INT4;
5181 "sub_weight_v" INT4;
5182 BEGIN
5183 PERFORM "require_transaction_isolation"();
5184 "weight_v" := 0;
5185 FOR "issue_delegation_row" IN
5186 SELECT * FROM "issue_delegation"
5187 WHERE "trustee_id" = "member_id_p"
5188 AND "issue_id" = "issue_id_p"
5189 LOOP
5190 IF NOT EXISTS (
5191 SELECT NULL FROM "direct_voter"
5192 WHERE "member_id" = "issue_delegation_row"."truster_id"
5193 AND "issue_id" = "issue_id_p"
5194 ) AND NOT EXISTS (
5195 SELECT NULL FROM "delegating_voter"
5196 WHERE "member_id" = "issue_delegation_row"."truster_id"
5197 AND "issue_id" = "issue_id_p"
5198 ) THEN
5199 "delegate_member_ids_v" :=
5200 "member_id_p" || "delegate_member_ids_p";
5201 INSERT INTO "delegating_voter" (
5202 "issue_id",
5203 "member_id",
5204 "scope",
5205 "delegate_member_ids"
5206 ) VALUES (
5207 "issue_id_p",
5208 "issue_delegation_row"."truster_id",
5209 "issue_delegation_row"."scope",
5210 "delegate_member_ids_v"
5211 );
5212 "sub_weight_v" := 1 +
5213 "weight_of_added_vote_delegations"(
5214 "issue_id_p",
5215 "issue_delegation_row"."truster_id",
5216 "delegate_member_ids_v"
5217 );
5218 UPDATE "delegating_voter"
5219 SET "weight" = "sub_weight_v"
5220 WHERE "issue_id" = "issue_id_p"
5221 AND "member_id" = "issue_delegation_row"."truster_id";
5222 "weight_v" := "weight_v" + "sub_weight_v";
5223 END IF;
5224 END LOOP;
5225 RETURN "weight_v";
5226 END;
5227 $$;
5229 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
5230 ( "issue"."id"%TYPE,
5231 "member"."id"%TYPE,
5232 "delegating_voter"."delegate_member_ids"%TYPE )
5233 IS 'Helper function for "add_vote_delegations" function';
5236 CREATE FUNCTION "add_vote_delegations"
5237 ( "issue_id_p" "issue"."id"%TYPE )
5238 RETURNS VOID
5239 LANGUAGE 'plpgsql' VOLATILE AS $$
5240 DECLARE
5241 "member_id_v" "member"."id"%TYPE;
5242 BEGIN
5243 PERFORM "require_transaction_isolation"();
5244 FOR "member_id_v" IN
5245 SELECT "member_id" FROM "direct_voter"
5246 WHERE "issue_id" = "issue_id_p"
5247 LOOP
5248 UPDATE "direct_voter" SET
5249 "weight" = "weight" + "weight_of_added_vote_delegations"(
5250 "issue_id_p",
5251 "member_id_v",
5252 '{}'
5253 )
5254 WHERE "member_id" = "member_id_v"
5255 AND "issue_id" = "issue_id_p";
5256 END LOOP;
5257 RETURN;
5258 END;
5259 $$;
5261 COMMENT ON FUNCTION "add_vote_delegations"
5262 ( "issue_id_p" "issue"."id"%TYPE )
5263 IS 'Helper function for "close_voting" function';
5266 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
5267 RETURNS VOID
5268 LANGUAGE 'plpgsql' VOLATILE AS $$
5269 DECLARE
5270 "area_id_v" "area"."id"%TYPE;
5271 "unit_id_v" "unit"."id"%TYPE;
5272 "member_id_v" "member"."id"%TYPE;
5273 BEGIN
5274 PERFORM "require_transaction_isolation"();
5275 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
5276 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
5277 -- override protection triggers:
5278 INSERT INTO "temporary_transaction_data" ("key", "value")
5279 VALUES ('override_protection_triggers', TRUE::TEXT);
5280 -- delete timestamp of voting comment:
5281 UPDATE "direct_voter" SET "comment_changed" = NULL
5282 WHERE "issue_id" = "issue_id_p";
5283 -- delete delegating votes (in cases of manual reset of issue state):
5284 DELETE FROM "delegating_voter"
5285 WHERE "issue_id" = "issue_id_p";
5286 -- delete votes from non-privileged voters:
5287 DELETE FROM "direct_voter"
5288 USING (
5289 SELECT
5290 "direct_voter"."member_id"
5291 FROM "direct_voter"
5292 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
5293 LEFT JOIN "privilege"
5294 ON "privilege"."unit_id" = "unit_id_v"
5295 AND "privilege"."member_id" = "direct_voter"."member_id"
5296 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
5297 "member"."active" = FALSE OR
5298 "privilege"."voting_right" ISNULL OR
5299 "privilege"."voting_right" = FALSE
5300 )
5301 ) AS "subquery"
5302 WHERE "direct_voter"."issue_id" = "issue_id_p"
5303 AND "direct_voter"."member_id" = "subquery"."member_id";
5304 -- consider delegations:
5305 UPDATE "direct_voter" SET "weight" = 1
5306 WHERE "issue_id" = "issue_id_p";
5307 PERFORM "add_vote_delegations"("issue_id_p");
5308 -- mark first preferences:
5309 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
5310 FROM (
5311 SELECT
5312 "vote"."initiative_id",
5313 "vote"."member_id",
5314 CASE WHEN "vote"."grade" > 0 THEN
5315 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
5316 ELSE NULL
5317 END AS "first_preference"
5318 FROM "vote"
5319 JOIN "initiative" -- NOTE: due to missing index on issue_id
5320 ON "vote"."issue_id" = "initiative"."issue_id"
5321 JOIN "vote" AS "agg"
5322 ON "initiative"."id" = "agg"."initiative_id"
5323 AND "vote"."member_id" = "agg"."member_id"
5324 GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
5325 ) AS "subquery"
5326 WHERE "vote"."issue_id" = "issue_id_p"
5327 AND "vote"."initiative_id" = "subquery"."initiative_id"
5328 AND "vote"."member_id" = "subquery"."member_id";
5329 -- finish overriding protection triggers (avoids garbage):
5330 DELETE FROM "temporary_transaction_data"
5331 WHERE "key" = 'override_protection_triggers';
5332 -- materialize battle_view:
5333 -- NOTE: "closed" column of issue must be set at this point
5334 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
5335 INSERT INTO "battle" (
5336 "issue_id",
5337 "winning_initiative_id", "losing_initiative_id",
5338 "count"
5339 ) SELECT
5340 "issue_id",
5341 "winning_initiative_id", "losing_initiative_id",
5342 "count"
5343 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
5344 -- set voter count:
5345 UPDATE "issue" SET
5346 "voter_count" = (
5347 SELECT coalesce(sum("weight"), 0)
5348 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
5349 )
5350 WHERE "id" = "issue_id_p";
5351 -- copy "positive_votes" and "negative_votes" from "battle" table:
5352 -- NOTE: "first_preference_votes" is set to a default of 0 at this step
5353 UPDATE "initiative" SET
5354 "first_preference_votes" = 0,
5355 "positive_votes" = "battle_win"."count",
5356 "negative_votes" = "battle_lose"."count"
5357 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
5358 WHERE
5359 "battle_win"."issue_id" = "issue_id_p" AND
5360 "battle_win"."winning_initiative_id" = "initiative"."id" AND
5361 "battle_win"."losing_initiative_id" ISNULL AND
5362 "battle_lose"."issue_id" = "issue_id_p" AND
5363 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
5364 "battle_lose"."winning_initiative_id" ISNULL;
5365 -- calculate "first_preference_votes":
5366 -- NOTE: will only set values not equal to zero
5367 UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum"
5368 FROM (
5369 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
5370 FROM "vote" JOIN "direct_voter"
5371 ON "vote"."issue_id" = "direct_voter"."issue_id"
5372 AND "vote"."member_id" = "direct_voter"."member_id"
5373 WHERE "vote"."first_preference"
5374 GROUP BY "vote"."initiative_id"
5375 ) AS "subquery"
5376 WHERE "initiative"."issue_id" = "issue_id_p"
5377 AND "initiative"."admitted"
5378 AND "initiative"."id" = "subquery"."initiative_id";
5379 END;
5380 $$;
5382 COMMENT ON FUNCTION "close_voting"
5383 ( "issue"."id"%TYPE )
5384 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.';
5387 CREATE FUNCTION "defeat_strength"
5388 ( "positive_votes_p" INT4,
5389 "negative_votes_p" INT4,
5390 "defeat_strength_p" "defeat_strength" )
5391 RETURNS INT8
5392 LANGUAGE 'plpgsql' IMMUTABLE AS $$
5393 BEGIN
5394 IF "defeat_strength_p" = 'simple'::"defeat_strength" THEN
5395 IF "positive_votes_p" > "negative_votes_p" THEN
5396 RETURN "positive_votes_p";
5397 ELSE
5398 RETURN 0;
5399 END IF;
5400 ELSE
5401 IF "positive_votes_p" > "negative_votes_p" THEN
5402 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
5403 ELSIF "positive_votes_p" = "negative_votes_p" THEN
5404 RETURN 0;
5405 ELSE
5406 RETURN -1;
5407 END IF;
5408 END IF;
5409 END;
5410 $$;
5412 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")';
5415 CREATE FUNCTION "secondary_link_strength"
5416 ( "initiative1_ord_p" INT4,
5417 "initiative2_ord_p" INT4,
5418 "tie_breaking_p" "tie_breaking" )
5419 RETURNS INT8
5420 LANGUAGE 'plpgsql' IMMUTABLE AS $$
5421 BEGIN
5422 IF "initiative1_ord_p" = "initiative2_ord_p" THEN
5423 RAISE EXCEPTION 'Identical initiative ids passed to "secondary_link_strength" function (should not happen)';
5424 END IF;
5425 RETURN (
5426 CASE WHEN "tie_breaking_p" = 'simple'::"tie_breaking" THEN
5427 0
5428 ELSE
5429 CASE WHEN "initiative1_ord_p" < "initiative2_ord_p" THEN
5430 1::INT8 << 62
5431 ELSE 0 END
5432 +
5433 CASE WHEN "tie_breaking_p" = 'variant2'::"tie_breaking" THEN
5434 ("initiative2_ord_p"::INT8 << 31) - "initiative1_ord_p"::INT8
5435 ELSE
5436 "initiative2_ord_p"::INT8 - ("initiative1_ord_p"::INT8 << 31)
5437 END
5438 END
5439 );
5440 END;
5441 $$;
5443 COMMENT ON FUNCTION "secondary_link_strength"(INT4, INT4, "tie_breaking") IS 'Calculates a secondary criterion for the defeat strength (tie-breaking of the links)';
5446 CREATE TYPE "link_strength" AS (
5447 "primary" INT8,
5448 "secondary" INT8 );
5450 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'')';
5453 CREATE FUNCTION "find_best_paths"("matrix_d" "link_strength"[][])
5454 RETURNS "link_strength"[][]
5455 LANGUAGE 'plpgsql' IMMUTABLE AS $$
5456 DECLARE
5457 "dimension_v" INT4;
5458 "matrix_p" "link_strength"[][];
5459 "i" INT4;
5460 "j" INT4;
5461 "k" INT4;
5462 BEGIN
5463 "dimension_v" := array_upper("matrix_d", 1);
5464 "matrix_p" := "matrix_d";
5465 "i" := 1;
5466 LOOP
5467 "j" := 1;
5468 LOOP
5469 IF "i" != "j" THEN
5470 "k" := 1;
5471 LOOP
5472 IF "i" != "k" AND "j" != "k" THEN
5473 IF "matrix_p"["j"]["i"] < "matrix_p"["i"]["k"] THEN
5474 IF "matrix_p"["j"]["i"] > "matrix_p"["j"]["k"] THEN
5475 "matrix_p"["j"]["k"] := "matrix_p"["j"]["i"];
5476 END IF;
5477 ELSE
5478 IF "matrix_p"["i"]["k"] > "matrix_p"["j"]["k"] THEN
5479 "matrix_p"["j"]["k"] := "matrix_p"["i"]["k"];
5480 END IF;
5481 END IF;
5482 END IF;
5483 EXIT WHEN "k" = "dimension_v";
5484 "k" := "k" + 1;
5485 END LOOP;
5486 END IF;
5487 EXIT WHEN "j" = "dimension_v";
5488 "j" := "j" + 1;
5489 END LOOP;
5490 EXIT WHEN "i" = "dimension_v";
5491 "i" := "i" + 1;
5492 END LOOP;
5493 RETURN "matrix_p";
5494 END;
5495 $$;
5497 COMMENT ON FUNCTION "find_best_paths"("link_strength"[][]) IS 'Computes the strengths of the best beat-paths from a square matrix';
5500 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
5501 RETURNS VOID
5502 LANGUAGE 'plpgsql' VOLATILE AS $$
5503 DECLARE
5504 "issue_row" "issue"%ROWTYPE;
5505 "policy_row" "policy"%ROWTYPE;
5506 "dimension_v" INT4;
5507 "matrix_a" INT4[][]; -- absolute votes
5508 "matrix_d" "link_strength"[][]; -- defeat strength (direct)
5509 "matrix_p" "link_strength"[][]; -- defeat strength (best path)
5510 "matrix_t" "link_strength"[][]; -- defeat strength (tie-breaking)
5511 "matrix_f" BOOLEAN[][]; -- forbidden link (tie-breaking)
5512 "matrix_b" BOOLEAN[][]; -- final order (who beats who)
5513 "i" INT4;
5514 "j" INT4;
5515 "m" INT4;
5516 "n" INT4;
5517 "battle_row" "battle"%ROWTYPE;
5518 "rank_ary" INT4[];
5519 "rank_v" INT4;
5520 "initiative_id_v" "initiative"."id"%TYPE;
5521 BEGIN
5522 PERFORM "require_transaction_isolation"();
5523 SELECT * INTO "issue_row"
5524 FROM "issue" WHERE "id" = "issue_id_p";
5525 SELECT * INTO "policy_row"
5526 FROM "policy" WHERE "id" = "issue_row"."policy_id";
5527 SELECT count(1) INTO "dimension_v"
5528 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
5529 -- create "matrix_a" with absolute number of votes in pairwise
5530 -- comparison:
5531 "matrix_a" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
5532 "i" := 1;
5533 "j" := 2;
5534 FOR "battle_row" IN
5535 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
5536 ORDER BY
5537 "winning_initiative_id" NULLS FIRST,
5538 "losing_initiative_id" NULLS FIRST
5539 LOOP
5540 "matrix_a"["i"]["j"] := "battle_row"."count";
5541 IF "j" = "dimension_v" THEN
5542 "i" := "i" + 1;
5543 "j" := 1;
5544 ELSE
5545 "j" := "j" + 1;
5546 IF "j" = "i" THEN
5547 "j" := "j" + 1;
5548 END IF;
5549 END IF;
5550 END LOOP;
5551 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
5552 RAISE EXCEPTION 'Wrong battle count (should not happen)';
5553 END IF;
5554 -- store direct defeat strengths in "matrix_d" using "defeat_strength"
5555 -- and "secondary_link_strength" functions:
5556 "matrix_d" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
5557 "i" := 1;
5558 LOOP
5559 "j" := 1;
5560 LOOP
5561 IF "i" != "j" THEN
5562 "matrix_d"["i"]["j"] := (
5563 "defeat_strength"(
5564 "matrix_a"["i"]["j"],
5565 "matrix_a"["j"]["i"],
5566 "policy_row"."defeat_strength"
5567 ),
5568 "secondary_link_strength"(
5569 "i",
5570 "j",
5571 "policy_row"."tie_breaking"
5572 )
5573 )::"link_strength";
5574 END IF;
5575 EXIT WHEN "j" = "dimension_v";
5576 "j" := "j" + 1;
5577 END LOOP;
5578 EXIT WHEN "i" = "dimension_v";
5579 "i" := "i" + 1;
5580 END LOOP;
5581 -- find best paths:
5582 "matrix_p" := "find_best_paths"("matrix_d");
5583 -- create partial order:
5584 "matrix_b" := array_fill(NULL::BOOLEAN, ARRAY["dimension_v", "dimension_v"]);
5585 "i" := 1;
5586 LOOP
5587 "j" := "i" + 1;
5588 LOOP
5589 IF "i" != "j" THEN
5590 IF "matrix_p"["i"]["j"] > "matrix_p"["j"]["i"] THEN
5591 "matrix_b"["i"]["j"] := TRUE;
5592 "matrix_b"["j"]["i"] := FALSE;
5593 ELSIF "matrix_p"["i"]["j"] < "matrix_p"["j"]["i"] THEN
5594 "matrix_b"["i"]["j"] := FALSE;
5595 "matrix_b"["j"]["i"] := TRUE;
5596 END IF;
5597 END IF;
5598 EXIT WHEN "j" = "dimension_v";
5599 "j" := "j" + 1;
5600 END LOOP;
5601 EXIT WHEN "i" = "dimension_v" - 1;
5602 "i" := "i" + 1;
5603 END LOOP;
5604 -- tie-breaking by forbidding shared weakest links in beat-paths
5605 -- (unless "tie_breaking" is set to 'simple', in which case tie-breaking
5606 -- is performed later by initiative id):
5607 IF "policy_row"."tie_breaking" != 'simple'::"tie_breaking" THEN
5608 "m" := 1;
5609 LOOP
5610 "n" := "m" + 1;
5611 LOOP
5612 -- only process those candidates m and n, which are tied:
5613 IF "matrix_b"["m"]["n"] ISNULL THEN
5614 -- start with beat-paths prior tie-breaking:
5615 "matrix_t" := "matrix_p";
5616 -- start with all links allowed:
5617 "matrix_f" := array_fill(FALSE, ARRAY["dimension_v", "dimension_v"]);
5618 LOOP
5619 -- determine (and forbid) that link that is the weakest link
5620 -- in both the best path from candidate m to candidate n and
5621 -- from candidate n to candidate m:
5622 "i" := 1;
5623 <<forbid_one_link>>
5624 LOOP
5625 "j" := 1;
5626 LOOP
5627 IF "i" != "j" THEN
5628 IF "matrix_d"["i"]["j"] = "matrix_t"["m"]["n"] THEN
5629 "matrix_f"["i"]["j"] := TRUE;
5630 -- exit for performance reasons,
5631 -- as exactly one link will be found:
5632 EXIT forbid_one_link;
5633 END IF;
5634 END IF;
5635 EXIT WHEN "j" = "dimension_v";
5636 "j" := "j" + 1;
5637 END LOOP;
5638 IF "i" = "dimension_v" THEN
5639 RAISE EXCEPTION 'Did not find shared weakest link for tie-breaking (should not happen)';
5640 END IF;
5641 "i" := "i" + 1;
5642 END LOOP;
5643 -- calculate best beat-paths while ignoring forbidden links:
5644 "i" := 1;
5645 LOOP
5646 "j" := 1;
5647 LOOP
5648 IF "i" != "j" THEN
5649 "matrix_t"["i"]["j"] := CASE
5650 WHEN "matrix_f"["i"]["j"]
5651 THEN ((-1::INT8) << 63, 0)::"link_strength" -- worst possible value
5652 ELSE "matrix_d"["i"]["j"] END;
5653 END IF;
5654 EXIT WHEN "j" = "dimension_v";
5655 "j" := "j" + 1;
5656 END LOOP;
5657 EXIT WHEN "i" = "dimension_v";
5658 "i" := "i" + 1;
5659 END LOOP;
5660 "matrix_t" := "find_best_paths"("matrix_t");
5661 -- extend partial order, if tie-breaking was successful:
5662 IF "matrix_t"["m"]["n"] > "matrix_t"["n"]["m"] THEN
5663 "matrix_b"["m"]["n"] := TRUE;
5664 "matrix_b"["n"]["m"] := FALSE;
5665 EXIT;
5666 ELSIF "matrix_t"["m"]["n"] < "matrix_t"["n"]["m"] THEN
5667 "matrix_b"["m"]["n"] := FALSE;
5668 "matrix_b"["n"]["m"] := TRUE;
5669 EXIT;
5670 END IF;
5671 END LOOP;
5672 END IF;
5673 EXIT WHEN "n" = "dimension_v";
5674 "n" := "n" + 1;
5675 END LOOP;
5676 EXIT WHEN "m" = "dimension_v" - 1;
5677 "m" := "m" + 1;
5678 END LOOP;
5679 END IF;
5680 -- store a unique ranking in "rank_ary":
5681 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
5682 "rank_v" := 1;
5683 LOOP
5684 "i" := 1;
5685 <<assign_next_rank>>
5686 LOOP
5687 IF "rank_ary"["i"] ISNULL THEN
5688 "j" := 1;
5689 LOOP
5690 IF
5691 "i" != "j" AND
5692 "rank_ary"["j"] ISNULL AND
5693 ( "matrix_b"["j"]["i"] OR
5694 -- tie-breaking by "id"
5695 ( "matrix_b"["j"]["i"] ISNULL AND
5696 "j" < "i" ) )
5697 THEN
5698 -- someone else is better
5699 EXIT;
5700 END IF;
5701 IF "j" = "dimension_v" THEN
5702 -- noone is better
5703 "rank_ary"["i"] := "rank_v";
5704 EXIT assign_next_rank;
5705 END IF;
5706 "j" := "j" + 1;
5707 END LOOP;
5708 END IF;
5709 "i" := "i" + 1;
5710 IF "i" > "dimension_v" THEN
5711 RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
5712 END IF;
5713 END LOOP;
5714 EXIT WHEN "rank_v" = "dimension_v";
5715 "rank_v" := "rank_v" + 1;
5716 END LOOP;
5717 -- write preliminary results:
5718 "i" := 2; -- omit status quo with "i" = 1
5719 FOR "initiative_id_v" IN
5720 SELECT "id" FROM "initiative"
5721 WHERE "issue_id" = "issue_id_p" AND "admitted"
5722 ORDER BY "id"
5723 LOOP
5724 UPDATE "initiative" SET
5725 "direct_majority" =
5726 CASE WHEN "policy_row"."direct_majority_strict" THEN
5727 "positive_votes" * "policy_row"."direct_majority_den" >
5728 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
5729 ELSE
5730 "positive_votes" * "policy_row"."direct_majority_den" >=
5731 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
5732 END
5733 AND "positive_votes" >= "policy_row"."direct_majority_positive"
5734 AND "issue_row"."voter_count"-"negative_votes" >=
5735 "policy_row"."direct_majority_non_negative",
5736 "indirect_majority" =
5737 CASE WHEN "policy_row"."indirect_majority_strict" THEN
5738 "positive_votes" * "policy_row"."indirect_majority_den" >
5739 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
5740 ELSE
5741 "positive_votes" * "policy_row"."indirect_majority_den" >=
5742 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
5743 END
5744 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
5745 AND "issue_row"."voter_count"-"negative_votes" >=
5746 "policy_row"."indirect_majority_non_negative",
5747 "schulze_rank" = "rank_ary"["i"],
5748 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
5749 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"[1],
5750 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"[1],
5751 "reverse_beat_path" = CASE WHEN "policy_row"."defeat_strength" = 'simple'::"defeat_strength"
5752 THEN NULL
5753 ELSE "matrix_p"[1]["i"]."primary" >= 0 END,
5754 "eligible" = FALSE,
5755 "winner" = FALSE,
5756 "rank" = NULL -- NOTE: in cases of manual reset of issue state
5757 WHERE "id" = "initiative_id_v";
5758 "i" := "i" + 1;
5759 END LOOP;
5760 IF "i" != "dimension_v" + 1 THEN
5761 RAISE EXCEPTION 'Wrong winner count (should not happen)';
5762 END IF;
5763 -- take indirect majorities into account:
5764 LOOP
5765 UPDATE "initiative" SET "indirect_majority" = TRUE
5766 FROM (
5767 SELECT "new_initiative"."id" AS "initiative_id"
5768 FROM "initiative" "old_initiative"
5769 JOIN "initiative" "new_initiative"
5770 ON "new_initiative"."issue_id" = "issue_id_p"
5771 AND "new_initiative"."indirect_majority" = FALSE
5772 JOIN "battle" "battle_win"
5773 ON "battle_win"."issue_id" = "issue_id_p"
5774 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
5775 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
5776 JOIN "battle" "battle_lose"
5777 ON "battle_lose"."issue_id" = "issue_id_p"
5778 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
5779 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
5780 WHERE "old_initiative"."issue_id" = "issue_id_p"
5781 AND "old_initiative"."indirect_majority" = TRUE
5782 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
5783 "battle_win"."count" * "policy_row"."indirect_majority_den" >
5784 "policy_row"."indirect_majority_num" *
5785 ("battle_win"."count"+"battle_lose"."count")
5786 ELSE
5787 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
5788 "policy_row"."indirect_majority_num" *
5789 ("battle_win"."count"+"battle_lose"."count")
5790 END
5791 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
5792 AND "issue_row"."voter_count"-"battle_lose"."count" >=
5793 "policy_row"."indirect_majority_non_negative"
5794 ) AS "subquery"
5795 WHERE "id" = "subquery"."initiative_id";
5796 EXIT WHEN NOT FOUND;
5797 END LOOP;
5798 -- set "multistage_majority" for remaining matching initiatives:
5799 UPDATE "initiative" SET "multistage_majority" = TRUE
5800 FROM (
5801 SELECT "losing_initiative"."id" AS "initiative_id"
5802 FROM "initiative" "losing_initiative"
5803 JOIN "initiative" "winning_initiative"
5804 ON "winning_initiative"."issue_id" = "issue_id_p"
5805 AND "winning_initiative"."admitted"
5806 JOIN "battle" "battle_win"
5807 ON "battle_win"."issue_id" = "issue_id_p"
5808 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
5809 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
5810 JOIN "battle" "battle_lose"
5811 ON "battle_lose"."issue_id" = "issue_id_p"
5812 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
5813 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
5814 WHERE "losing_initiative"."issue_id" = "issue_id_p"
5815 AND "losing_initiative"."admitted"
5816 AND "winning_initiative"."schulze_rank" <
5817 "losing_initiative"."schulze_rank"
5818 AND "battle_win"."count" > "battle_lose"."count"
5819 AND (
5820 "battle_win"."count" > "winning_initiative"."positive_votes" OR
5821 "battle_lose"."count" < "losing_initiative"."negative_votes" )
5822 ) AS "subquery"
5823 WHERE "id" = "subquery"."initiative_id";
5824 -- mark eligible initiatives:
5825 UPDATE "initiative" SET "eligible" = TRUE
5826 WHERE "issue_id" = "issue_id_p"
5827 AND "initiative"."direct_majority"
5828 AND "initiative"."indirect_majority"
5829 AND "initiative"."better_than_status_quo"
5830 AND (
5831 "policy_row"."no_multistage_majority" = FALSE OR
5832 "initiative"."multistage_majority" = FALSE )
5833 AND (
5834 "policy_row"."no_reverse_beat_path" = FALSE OR
5835 coalesce("initiative"."reverse_beat_path", FALSE) = FALSE );
5836 -- mark final winner:
5837 UPDATE "initiative" SET "winner" = TRUE
5838 FROM (
5839 SELECT "id" AS "initiative_id"
5840 FROM "initiative"
5841 WHERE "issue_id" = "issue_id_p" AND "eligible"
5842 ORDER BY
5843 "schulze_rank",
5844 "id"
5845 LIMIT 1
5846 ) AS "subquery"
5847 WHERE "id" = "subquery"."initiative_id";
5848 -- write (final) ranks:
5849 "rank_v" := 1;
5850 FOR "initiative_id_v" IN
5851 SELECT "id"
5852 FROM "initiative"
5853 WHERE "issue_id" = "issue_id_p" AND "admitted"
5854 ORDER BY
5855 "winner" DESC,
5856 "eligible" DESC,
5857 "schulze_rank",
5858 "id"
5859 LOOP
5860 UPDATE "initiative" SET "rank" = "rank_v"
5861 WHERE "id" = "initiative_id_v";
5862 "rank_v" := "rank_v" + 1;
5863 END LOOP;
5864 -- set schulze rank of status quo and mark issue as finished:
5865 UPDATE "issue" SET
5866 "status_quo_schulze_rank" = "rank_ary"[1],
5867 "state" =
5868 CASE WHEN EXISTS (
5869 SELECT NULL FROM "initiative"
5870 WHERE "issue_id" = "issue_id_p" AND "winner"
5871 ) THEN
5872 'finished_with_winner'::"issue_state"
5873 ELSE
5874 'finished_without_winner'::"issue_state"
5875 END,
5876 "closed" = "phase_finished",
5877 "phase_finished" = NULL
5878 WHERE "id" = "issue_id_p";
5879 RETURN;
5880 END;
5881 $$;
5883 COMMENT ON FUNCTION "calculate_ranks"
5884 ( "issue"."id"%TYPE )
5885 IS 'Determine ranking (Votes have to be counted first)';
5889 -----------------------------
5890 -- Automatic state changes --
5891 -----------------------------
5894 CREATE FUNCTION "issue_admission"
5895 ( "area_id_p" "area"."id"%TYPE )
5896 RETURNS BOOLEAN
5897 LANGUAGE 'plpgsql' VOLATILE AS $$
5898 DECLARE
5899 "issue_id_v" "issue"."id"%TYPE;
5900 BEGIN
5901 PERFORM "dont_require_transaction_isolation"();
5902 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
5903 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
5904 FROM "area_quorum" AS "view"
5905 WHERE "area"."id" = "view"."area_id"
5906 AND "area"."id" = "area_id_p";
5907 SELECT "id" INTO "issue_id_v" FROM "issue_for_admission"
5908 WHERE "area_id" = "area_id_p";
5909 IF "issue_id_v" ISNULL THEN RETURN FALSE; END IF;
5910 UPDATE "issue" SET
5911 "admission_snapshot_id" = "latest_snapshot_id",
5912 "state" = 'discussion',
5913 "accepted" = now(),
5914 "phase_finished" = NULL
5915 WHERE "id" = "issue_id_v";
5916 RETURN TRUE;
5917 END;
5918 $$;
5920 COMMENT ON FUNCTION "issue_admission"
5921 ( "area"."id"%TYPE )
5922 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';
5925 CREATE TYPE "check_issue_persistence" AS (
5926 "state" "issue_state",
5927 "phase_finished" BOOLEAN,
5928 "issue_revoked" BOOLEAN,
5929 "snapshot_created" BOOLEAN,
5930 "harmonic_weights_set" BOOLEAN,
5931 "closed_voting" BOOLEAN );
5933 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';
5936 CREATE FUNCTION "check_issue"
5937 ( "issue_id_p" "issue"."id"%TYPE,
5938 "persist" "check_issue_persistence" )
5939 RETURNS "check_issue_persistence"
5940 LANGUAGE 'plpgsql' VOLATILE AS $$
5941 DECLARE
5942 "issue_row" "issue"%ROWTYPE;
5943 "last_calculated_v" "snapshot"."calculated"%TYPE;
5944 "policy_row" "policy"%ROWTYPE;
5945 "initiative_row" "initiative"%ROWTYPE;
5946 "state_v" "issue_state";
5947 BEGIN
5948 PERFORM "require_transaction_isolation"();
5949 IF "persist" ISNULL THEN
5950 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
5951 FOR UPDATE;
5952 SELECT "calculated" INTO "last_calculated_v"
5953 FROM "snapshot" JOIN "snapshot_issue"
5954 ON "snapshot"."id" = "snapshot_issue"."snapshot_id"
5955 WHERE "snapshot_issue"."issue_id" = "issue_id_p";
5956 IF "issue_row"."closed" NOTNULL THEN
5957 RETURN NULL;
5958 END IF;
5959 "persist"."state" := "issue_row"."state";
5960 IF
5961 ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=
5962 "issue_row"."created" + "issue_row"."max_admission_time" ) OR
5963 ( "issue_row"."state" = 'discussion' AND now() >=
5964 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
5965 ( "issue_row"."state" = 'verification' AND now() >=
5966 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
5967 ( "issue_row"."state" = 'voting' AND now() >=
5968 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
5969 THEN
5970 "persist"."phase_finished" := TRUE;
5971 ELSE
5972 "persist"."phase_finished" := FALSE;
5973 END IF;
5974 IF
5975 NOT EXISTS (
5976 -- all initiatives are revoked
5977 SELECT NULL FROM "initiative"
5978 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
5979 ) AND (
5980 -- and issue has not been accepted yet
5981 "persist"."state" = 'admission' OR
5982 -- or verification time has elapsed
5983 ( "persist"."state" = 'verification' AND
5984 "persist"."phase_finished" ) OR
5985 -- or no initiatives have been revoked lately
5986 NOT EXISTS (
5987 SELECT NULL FROM "initiative"
5988 WHERE "issue_id" = "issue_id_p"
5989 AND now() < "revoked" + "issue_row"."verification_time"
5990 )
5991 )
5992 THEN
5993 "persist"."issue_revoked" := TRUE;
5994 ELSE
5995 "persist"."issue_revoked" := FALSE;
5996 END IF;
5997 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
5998 UPDATE "issue" SET "phase_finished" = now()
5999 WHERE "id" = "issue_row"."id";
6000 RETURN "persist";
6001 ELSIF
6002 "persist"."state" IN ('admission', 'discussion', 'verification')
6003 THEN
6004 RETURN "persist";
6005 ELSE
6006 RETURN NULL;
6007 END IF;
6008 END IF;
6009 IF
6010 "persist"."state" IN ('admission', 'discussion', 'verification') AND
6011 coalesce("persist"."snapshot_created", FALSE) = FALSE
6012 THEN
6013 IF "persist"."state" != 'admission' THEN
6014 PERFORM "take_snapshot"("issue_id_p");
6015 PERFORM "finish_snapshot"("issue_id_p");
6016 END IF;
6017 "persist"."snapshot_created" = TRUE;
6018 IF "persist"."phase_finished" THEN
6019 IF "persist"."state" = 'admission' THEN
6020 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id";
6021 ELSIF "persist"."state" = 'discussion' THEN
6022 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id";
6023 ELSIF "persist"."state" = 'verification' THEN
6024 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id";
6025 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
6026 SELECT * INTO "policy_row" FROM "policy"
6027 WHERE "id" = "issue_row"."policy_id";
6028 FOR "initiative_row" IN
6029 SELECT * FROM "initiative"
6030 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
6031 FOR UPDATE
6032 LOOP
6033 IF
6034 "initiative_row"."polling" OR (
6035 "initiative_row"."satisfied_supporter_count" >
6036 "policy_row"."initiative_quorum" AND
6037 "initiative_row"."satisfied_supporter_count" *
6038 "policy_row"."initiative_quorum_den" >=
6039 "issue_row"."population" * "policy_row"."initiative_quorum_num"
6040 )
6041 THEN
6042 UPDATE "initiative" SET "admitted" = TRUE
6043 WHERE "id" = "initiative_row"."id";
6044 ELSE
6045 UPDATE "initiative" SET "admitted" = FALSE
6046 WHERE "id" = "initiative_row"."id";
6047 END IF;
6048 END LOOP;
6049 END IF;
6050 END IF;
6051 RETURN "persist";
6052 END IF;
6053 IF
6054 "persist"."state" IN ('admission', 'discussion', 'verification') AND
6055 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
6056 THEN
6057 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
6058 "persist"."harmonic_weights_set" = TRUE;
6059 IF
6060 "persist"."phase_finished" OR
6061 "persist"."issue_revoked" OR
6062 "persist"."state" = 'admission'
6063 THEN
6064 RETURN "persist";
6065 ELSE
6066 RETURN NULL;
6067 END IF;
6068 END IF;
6069 IF "persist"."issue_revoked" THEN
6070 IF "persist"."state" = 'admission' THEN
6071 "state_v" := 'canceled_revoked_before_accepted';
6072 ELSIF "persist"."state" = 'discussion' THEN
6073 "state_v" := 'canceled_after_revocation_during_discussion';
6074 ELSIF "persist"."state" = 'verification' THEN
6075 "state_v" := 'canceled_after_revocation_during_verification';
6076 END IF;
6077 UPDATE "issue" SET
6078 "state" = "state_v",
6079 "closed" = "phase_finished",
6080 "phase_finished" = NULL
6081 WHERE "id" = "issue_id_p";
6082 RETURN NULL;
6083 END IF;
6084 IF "persist"."state" = 'admission' THEN
6085 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
6086 FOR UPDATE;
6087 IF "issue_row"."phase_finished" NOTNULL THEN
6088 UPDATE "issue" SET
6089 "state" = 'canceled_issue_not_accepted',
6090 "closed" = "phase_finished",
6091 "phase_finished" = NULL
6092 WHERE "id" = "issue_id_p";
6093 END IF;
6094 RETURN NULL;
6095 END IF;
6096 IF "persist"."phase_finished" THEN
6097 IF "persist"."state" = 'discussion' THEN
6098 UPDATE "issue" SET
6099 "state" = 'verification',
6100 "half_frozen" = "phase_finished",
6101 "phase_finished" = NULL
6102 WHERE "id" = "issue_id_p";
6103 RETURN NULL;
6104 END IF;
6105 IF "persist"."state" = 'verification' THEN
6106 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
6107 FOR UPDATE;
6108 SELECT * INTO "policy_row" FROM "policy"
6109 WHERE "id" = "issue_row"."policy_id";
6110 IF EXISTS (
6111 SELECT NULL FROM "initiative"
6112 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
6113 ) THEN
6114 UPDATE "issue" SET
6115 "state" = 'voting',
6116 "fully_frozen" = "phase_finished",
6117 "phase_finished" = NULL
6118 WHERE "id" = "issue_id_p";
6119 ELSE
6120 UPDATE "issue" SET
6121 "state" = 'canceled_no_initiative_admitted',
6122 "fully_frozen" = "phase_finished",
6123 "closed" = "phase_finished",
6124 "phase_finished" = NULL
6125 WHERE "id" = "issue_id_p";
6126 -- NOTE: The following DELETE statements have effect only when
6127 -- issue state has been manipulated
6128 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
6129 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
6130 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
6131 END IF;
6132 RETURN NULL;
6133 END IF;
6134 IF "persist"."state" = 'voting' THEN
6135 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
6136 PERFORM "close_voting"("issue_id_p");
6137 "persist"."closed_voting" = TRUE;
6138 RETURN "persist";
6139 END IF;
6140 PERFORM "calculate_ranks"("issue_id_p");
6141 RETURN NULL;
6142 END IF;
6143 END IF;
6144 RAISE WARNING 'should not happen';
6145 RETURN NULL;
6146 END;
6147 $$;
6149 COMMENT ON FUNCTION "check_issue"
6150 ( "issue"."id"%TYPE,
6151 "check_issue_persistence" )
6152 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")';
6155 CREATE FUNCTION "check_everything"()
6156 RETURNS VOID
6157 LANGUAGE 'plpgsql' VOLATILE AS $$
6158 DECLARE
6159 "area_id_v" "area"."id"%TYPE;
6160 "snapshot_id_v" "snapshot"."id"%TYPE;
6161 "issue_id_v" "issue"."id"%TYPE;
6162 "persist_v" "check_issue_persistence";
6163 BEGIN
6164 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
6165 DELETE FROM "expired_session";
6166 DELETE FROM "expired_token";
6167 DELETE FROM "expired_snapshot";
6168 PERFORM "check_activity"();
6169 PERFORM "calculate_member_counts"();
6170 FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP
6171 SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v";
6172 PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"
6173 WHERE "snapshot_id" = "snapshot_id_v";
6174 LOOP
6175 EXIT WHEN "issue_admission"("area_id_v") = FALSE;
6176 END LOOP;
6177 END LOOP;
6178 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
6179 "persist_v" := NULL;
6180 LOOP
6181 "persist_v" := "check_issue"("issue_id_v", "persist_v");
6182 EXIT WHEN "persist_v" ISNULL;
6183 END LOOP;
6184 END LOOP;
6185 RETURN;
6186 END;
6187 $$;
6189 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';
6193 ----------------------
6194 -- Deletion of data --
6195 ----------------------
6198 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
6199 RETURNS VOID
6200 LANGUAGE 'plpgsql' VOLATILE AS $$
6201 BEGIN
6202 IF EXISTS (
6203 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
6204 ) THEN
6205 -- override protection triggers:
6206 INSERT INTO "temporary_transaction_data" ("key", "value")
6207 VALUES ('override_protection_triggers', TRUE::TEXT);
6208 -- clean data:
6209 DELETE FROM "delegating_voter"
6210 WHERE "issue_id" = "issue_id_p";
6211 DELETE FROM "direct_voter"
6212 WHERE "issue_id" = "issue_id_p";
6213 DELETE FROM "delegating_interest_snapshot"
6214 WHERE "issue_id" = "issue_id_p";
6215 DELETE FROM "direct_interest_snapshot"
6216 WHERE "issue_id" = "issue_id_p";
6217 DELETE FROM "non_voter"
6218 WHERE "issue_id" = "issue_id_p";
6219 DELETE FROM "delegation"
6220 WHERE "issue_id" = "issue_id_p";
6221 DELETE FROM "supporter"
6222 USING "initiative" -- NOTE: due to missing index on issue_id
6223 WHERE "initiative"."issue_id" = "issue_id_p"
6224 AND "supporter"."initiative_id" = "initiative_id";
6225 -- mark issue as cleaned:
6226 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
6227 -- finish overriding protection triggers (avoids garbage):
6228 DELETE FROM "temporary_transaction_data"
6229 WHERE "key" = 'override_protection_triggers';
6230 END IF;
6231 RETURN;
6232 END;
6233 $$;
6235 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
6238 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
6239 RETURNS VOID
6240 LANGUAGE 'plpgsql' VOLATILE AS $$
6241 BEGIN
6242 UPDATE "member" SET
6243 "last_login" = NULL,
6244 "last_delegation_check" = NULL,
6245 "login" = NULL,
6246 "password" = NULL,
6247 "authority" = NULL,
6248 "authority_uid" = NULL,
6249 "authority_login" = NULL,
6250 "locked" = TRUE,
6251 "active" = FALSE,
6252 "notify_email" = NULL,
6253 "notify_email_unconfirmed" = NULL,
6254 "notify_email_secret" = NULL,
6255 "notify_email_secret_expiry" = NULL,
6256 "notify_email_lock_expiry" = NULL,
6257 "disable_notifications" = TRUE,
6258 "notification_counter" = DEFAULT,
6259 "notification_sample_size" = 0,
6260 "notification_dow" = NULL,
6261 "notification_hour" = NULL,
6262 "notification_sent" = NULL,
6263 "login_recovery_expiry" = NULL,
6264 "password_reset_secret" = NULL,
6265 "password_reset_secret_expiry" = NULL,
6266 "location" = NULL
6267 WHERE "id" = "member_id_p";
6268 -- "text_search_data" is updated by triggers
6269 DELETE FROM "member_settings" WHERE "member_id" = "member_id_p";
6270 DELETE FROM "member_profile" WHERE "member_id" = "member_id_p";
6271 DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";
6272 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
6273 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
6274 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
6275 DELETE FROM "session" WHERE "member_id" = "member_id_p";
6276 DELETE FROM "member_application" WHERE "member_id" = "member_id_p";
6277 DELETE FROM "token" WHERE "member_id" = "member_id_p";
6278 DELETE FROM "subscription" WHERE "member_id" = "member_id_p";
6279 DELETE FROM "ignored_area" WHERE "member_id" = "member_id_p";
6280 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
6281 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
6282 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
6283 DELETE FROM "direct_voter" USING "issue"
6284 WHERE "direct_voter"."issue_id" = "issue"."id"
6285 AND "issue"."closed" ISNULL
6286 AND "member_id" = "member_id_p";
6287 DELETE FROM "notification_initiative_sent" WHERE "member_id" = "member_id_p";
6288 RETURN;
6289 END;
6290 $$;
6292 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)';
6295 CREATE FUNCTION "delete_private_data"()
6296 RETURNS VOID
6297 LANGUAGE 'plpgsql' VOLATILE AS $$
6298 BEGIN
6299 DELETE FROM "temporary_transaction_data";
6300 DELETE FROM "temporary_suggestion_counts";
6301 DELETE FROM "member" WHERE "activated" ISNULL;
6302 UPDATE "member" SET
6303 "invite_code" = NULL,
6304 "invite_code_expiry" = NULL,
6305 "admin_comment" = NULL,
6306 "last_login" = NULL,
6307 "last_delegation_check" = NULL,
6308 "login" = NULL,
6309 "password" = NULL,
6310 "authority" = NULL,
6311 "authority_uid" = NULL,
6312 "authority_login" = NULL,
6313 "lang" = NULL,
6314 "notify_email" = NULL,
6315 "notify_email_unconfirmed" = NULL,
6316 "notify_email_secret" = NULL,
6317 "notify_email_secret_expiry" = NULL,
6318 "notify_email_lock_expiry" = NULL,
6319 "disable_notifications" = TRUE,
6320 "notification_counter" = DEFAULT,
6321 "notification_sample_size" = 0,
6322 "notification_dow" = NULL,
6323 "notification_hour" = NULL,
6324 "notification_sent" = NULL,
6325 "login_recovery_expiry" = NULL,
6326 "password_reset_secret" = NULL,
6327 "password_reset_secret_expiry" = NULL,
6328 "location" = NULL;
6329 -- "text_search_data" is updated by triggers
6330 DELETE FROM "member_settings";
6331 DELETE FROM "member_useterms";
6332 DELETE FROM "member_profile";
6333 DELETE FROM "rendered_member_statement";
6334 DELETE FROM "member_image";
6335 DELETE FROM "contact";
6336 DELETE FROM "ignored_member";
6337 DELETE FROM "session";
6338 DELETE FROM "system_application";
6339 DELETE FROM "system_application_redirect_uri";
6340 DELETE FROM "dynamic_application_scope";
6341 DELETE FROM "member_application";
6342 DELETE FROM "token";
6343 DELETE FROM "subscription";
6344 DELETE FROM "ignored_area";
6345 DELETE FROM "ignored_initiative";
6346 DELETE FROM "non_voter";
6347 DELETE FROM "direct_voter" USING "issue"
6348 WHERE "direct_voter"."issue_id" = "issue"."id"
6349 AND "issue"."closed" ISNULL;
6350 DELETE FROM "event_processed";
6351 DELETE FROM "notification_initiative_sent";
6352 DELETE FROM "newsletter";
6353 RETURN;
6354 END;
6355 $$;
6357 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.';
6361 COMMIT;