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