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