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