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