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