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