liquid_feedback_core
view core.sql @ 536:750b0be5acb6
Work on unit/area/policy events: added column "policy_id" to "event" table, added new "event_type"s
| author | jbe |
|---|---|
| date | Mon Jun 26 15:54:26 2017 +0200 (2017-06-26) |
| parents | 8b6433096a58 |
| children | aa261389c993 |
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_issue_state_changed_trigger"()
1786 RETURNS TRIGGER
1787 LANGUAGE 'plpgsql' VOLATILE AS $$
1788 DECLARE
1789 "area_row" "area"%ROWTYPE;
1790 BEGIN
1791 IF NEW."state" != OLD."state" THEN
1792 SELECT * INTO "area_row" FROM "area" WHERE "id" = NEW."area_id"
1793 FOR SHARE;
1794 INSERT INTO "event" (
1795 "event",
1796 "unit_id", "area_id", "policy_id", "issue_id", "state"
1797 ) VALUES (
1798 'issue_state_changed',
1799 "area_row"."unit_id", NEW."area_id", NEW."policy_id",
1800 NEW."id", NEW."state"
1801 );
1802 END IF;
1803 RETURN NULL;
1804 END;
1805 $$;
1807 CREATE TRIGGER "write_event_issue_state_changed"
1808 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1809 "write_event_issue_state_changed_trigger"();
1811 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1812 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1815 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1816 RETURNS TRIGGER
1817 LANGUAGE 'plpgsql' VOLATILE AS $$
1818 DECLARE
1819 "initiative_row" "initiative"%ROWTYPE;
1820 "issue_row" "issue"%ROWTYPE;
1821 "area_row" "area"%ROWTYPE;
1822 "event_v" "event_type";
1823 BEGIN
1824 SELECT * INTO "initiative_row" FROM "initiative"
1825 WHERE "id" = NEW."initiative_id" FOR SHARE;
1826 SELECT * INTO "issue_row" FROM "issue"
1827 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1828 SELECT * INTO "area_row" FROM "area"
1829 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1830 IF EXISTS (
1831 SELECT NULL FROM "draft"
1832 WHERE "initiative_id" = NEW."initiative_id" AND "id" != NEW."id"
1833 FOR SHARE
1834 ) THEN
1835 "event_v" := 'new_draft_created';
1836 ELSE
1837 IF EXISTS (
1838 SELECT NULL FROM "initiative"
1839 WHERE "issue_id" = "initiative_row"."issue_id"
1840 AND "id" != "initiative_row"."id"
1841 FOR SHARE
1842 ) THEN
1843 "event_v" := 'initiative_created_in_existing_issue';
1844 ELSE
1845 "event_v" := 'initiative_created_in_new_issue';
1846 END IF;
1847 END IF;
1848 INSERT INTO "event" (
1849 "event", "member_id",
1850 "unit_id", "area_id", "policy_id", "issue_id", "state",
1851 "initiative_id", "draft_id"
1852 ) VALUES (
1853 "event_v", NEW."author_id",
1854 "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",
1855 "initiative_row"."issue_id", "issue_row"."state",
1856 NEW."initiative_id", NEW."id"
1857 );
1858 RETURN NULL;
1859 END;
1860 $$;
1862 CREATE TRIGGER "write_event_initiative_or_draft_created"
1863 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1864 "write_event_initiative_or_draft_created_trigger"();
1866 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1867 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1870 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1871 RETURNS TRIGGER
1872 LANGUAGE 'plpgsql' VOLATILE AS $$
1873 DECLARE
1874 "issue_row" "issue"%ROWTYPE;
1875 "area_row" "area"%ROWTYPE;
1876 "draft_id_v" "draft"."id"%TYPE;
1877 BEGIN
1878 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1879 SELECT * INTO "issue_row" FROM "issue"
1880 WHERE "id" = NEW."issue_id" FOR SHARE;
1881 SELECT * INTO "area_row" FROM "area"
1882 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1883 SELECT "id" INTO "draft_id_v" FROM "current_draft"
1884 WHERE "initiative_id" = NEW."id" FOR SHARE;
1885 INSERT INTO "event" (
1886 "event", "member_id",
1887 "unit_id", "area_id", "policy_id", "issue_id", "state",
1888 "initiative_id", "draft_id"
1889 ) VALUES (
1890 'initiative_revoked', NEW."revoked_by_member_id",
1891 "area_row"."unit_id", "issue_row"."area_id",
1892 "issue_row"."policy_id",
1893 NEW."issue_id", "issue_row"."state",
1894 NEW."id", "draft_id_v"
1895 );
1896 END IF;
1897 RETURN NULL;
1898 END;
1899 $$;
1901 CREATE TRIGGER "write_event_initiative_revoked"
1902 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1903 "write_event_initiative_revoked_trigger"();
1905 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1906 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1909 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1910 RETURNS TRIGGER
1911 LANGUAGE 'plpgsql' VOLATILE AS $$
1912 DECLARE
1913 "initiative_row" "initiative"%ROWTYPE;
1914 "issue_row" "issue"%ROWTYPE;
1915 "area_row" "area"%ROWTYPE;
1916 BEGIN
1917 SELECT * INTO "initiative_row" FROM "initiative"
1918 WHERE "id" = NEW."initiative_id" FOR SHARE;
1919 SELECT * INTO "issue_row" FROM "issue"
1920 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1921 SELECT * INTO "area_row" FROM "area"
1922 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1923 INSERT INTO "event" (
1924 "event", "member_id",
1925 "unit_id", "area_id", "policy_id", "issue_id", "state",
1926 "initiative_id", "suggestion_id"
1927 ) VALUES (
1928 'suggestion_created', NEW."author_id",
1929 "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",
1930 "initiative_row"."issue_id", "issue_row"."state",
1931 NEW."initiative_id", NEW."id"
1932 );
1933 RETURN NULL;
1934 END;
1935 $$;
1937 CREATE TRIGGER "write_event_suggestion_created"
1938 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1939 "write_event_suggestion_created_trigger"();
1941 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1942 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1945 CREATE FUNCTION "write_event_suggestion_removed_trigger"()
1946 RETURNS TRIGGER
1947 LANGUAGE 'plpgsql' VOLATILE AS $$
1948 DECLARE
1949 "initiative_row" "initiative"%ROWTYPE;
1950 "issue_row" "issue"%ROWTYPE;
1951 "area_row" "area"%ROWTYPE;
1952 BEGIN
1953 SELECT * INTO "initiative_row" FROM "initiative"
1954 WHERE "id" = OLD."initiative_id" FOR SHARE;
1955 IF "initiative_row"."id" NOTNULL THEN
1956 SELECT * INTO "issue_row" FROM "issue"
1957 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1958 SELECT * INTO "area_row" FROM "area"
1959 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1960 INSERT INTO "event" (
1961 "event",
1962 "unit_id", "area_id", "policy_id", "issue_id", "state",
1963 "initiative_id", "suggestion_id"
1964 ) VALUES (
1965 'suggestion_removed',
1966 "area_row"."unit_id", "issue_row"."area_id",
1967 "issue_row"."policy_id",
1968 "initiative_row"."issue_id", "issue_row"."state",
1969 OLD."initiative_id", OLD."id"
1970 );
1971 END IF;
1972 RETURN NULL;
1973 END;
1974 $$;
1976 CREATE TRIGGER "write_event_suggestion_removed"
1977 AFTER DELETE ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1978 "write_event_suggestion_removed_trigger"();
1980 COMMENT ON FUNCTION "write_event_suggestion_removed_trigger"() IS 'Implementation of trigger "write_event_suggestion_removed" on table "issue"';
1981 COMMENT ON TRIGGER "write_event_suggestion_removed" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1984 CREATE FUNCTION "write_event_member_trigger"()
1985 RETURNS TRIGGER
1986 LANGUAGE 'plpgsql' VOLATILE AS $$
1987 BEGIN
1988 IF TG_OP = 'INSERT' THEN
1989 IF NEW."activated" NOTNULL THEN
1990 INSERT INTO "event" ("event", "member_id")
1991 VALUES ('member_activated', NEW."id");
1992 END IF;
1993 IF NEW."active" THEN
1994 INSERT INTO "event" ("event", "member_id", "boolean_value")
1995 VALUES ('member_active', NEW."id", TRUE);
1996 END IF;
1997 ELSIF TG_OP = 'UPDATE' THEN
1998 IF OLD."id" != NEW."id" THEN
1999 RAISE EXCEPTION 'Cannot change member ID';
2000 END IF;
2001 IF OLD."name" != NEW."name" THEN
2002 INSERT INTO "event" (
2003 "event", "member_id", "text_value", "old_text_value"
2004 ) VALUES (
2005 'member_name_updated', NEW."id", NEW."name", OLD."name"
2006 );
2007 END IF;
2008 IF OLD."active" != NEW."active" THEN
2009 INSERT INTO "event" ("event", "member_id", "boolean_value") VALUES (
2010 'member_active', NEW."id", NEW."active"
2011 );
2012 END IF;
2013 IF
2014 OLD."activated" NOTNULL AND
2015 -- TODO: NEW."activated" ISNULL? OLD."login" NOTNULL?
2016 NEW."last_login" ISNULL AND
2017 NEW."login" ISNULL AND
2018 NEW."authority_login" ISNULL AND
2019 NEW."locked" = TRUE
2020 THEN
2021 INSERT INTO "event" ("event", "member_id")
2022 VALUES ('member_removed', NEW."id");
2023 END IF;
2024 END IF;
2025 RETURN NULL;
2026 END;
2027 $$;
2029 CREATE TRIGGER "write_event_member"
2030 AFTER INSERT OR UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
2031 "write_event_member_trigger"();
2033 COMMENT ON FUNCTION "write_event_member_trigger"() IS 'Implementation of trigger "write_event_member" on table "member"';
2034 COMMENT ON TRIGGER "write_event_member" ON "member" IS 'Create entries in "event" table on insertion to member table';
2037 CREATE FUNCTION "write_event_member_profile_updated_trigger"()
2038 RETURNS TRIGGER
2039 LANGUAGE 'plpgsql' VOLATILE AS $$
2040 BEGIN
2041 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
2042 IF EXISTS (SELECT NULL FROM "member" WHERE "id" = OLD."member_id") THEN
2043 INSERT INTO "event" ("event", "member_id") VALUES (
2044 'member_profile_updated', OLD."member_id"
2045 );
2046 END IF;
2047 END IF;
2048 IF TG_OP = 'UPDATE' THEN
2049 IF OLD."member_id" = NEW."member_id" THEN
2050 RETURN NULL;
2051 END IF;
2052 END IF;
2053 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2054 INSERT INTO "event" ("event", "member_id") VALUES (
2055 'member_profile_updated', NEW."member_id"
2056 );
2057 END IF;
2058 RETURN NULL;
2059 END;
2060 $$;
2062 CREATE TRIGGER "write_event_member_profile_updated"
2063 AFTER INSERT OR UPDATE OR DELETE ON "member_profile"
2064 FOR EACH ROW EXECUTE PROCEDURE
2065 "write_event_member_profile_updated_trigger"();
2067 COMMENT ON FUNCTION "write_event_member_profile_updated_trigger"() IS 'Implementation of trigger "write_event_member_profile_updated" on table "member_profile"';
2068 COMMENT ON TRIGGER "write_event_member_profile_updated" ON "member_profile" IS 'Creates entries in "event" table on member profile update';
2071 CREATE FUNCTION "write_event_member_image_updated_trigger"()
2072 RETURNS TRIGGER
2073 LANGUAGE 'plpgsql' VOLATILE AS $$
2074 BEGIN
2075 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
2076 IF NOT OLD."scaled" THEN
2077 IF EXISTS (SELECT NULL FROM "member" WHERE "id" = OLD."member_id") THEN
2078 INSERT INTO "event" ("event", "member_id") VALUES (
2079 'member_image_updated', OLD."member_id"
2080 );
2081 END IF;
2082 END IF;
2083 END IF;
2084 IF TG_OP = 'UPDATE' THEN
2085 IF
2086 OLD."member_id" = NEW."member_id" AND
2087 OLD."scaled" = NEW."scaled"
2088 THEN
2089 RETURN NULL;
2090 END IF;
2091 END IF;
2092 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2093 IF NOT NEW."scaled" THEN
2094 INSERT INTO "event" ("event", "member_id") VALUES (
2095 'member_image_updated', NEW."member_id"
2096 );
2097 END IF;
2098 END IF;
2099 RETURN NULL;
2100 END;
2101 $$;
2103 CREATE TRIGGER "write_event_member_image_updated"
2104 AFTER INSERT OR UPDATE OR DELETE ON "member_image"
2105 FOR EACH ROW EXECUTE PROCEDURE
2106 "write_event_member_image_updated_trigger"();
2108 COMMENT ON FUNCTION "write_event_member_image_updated_trigger"() IS 'Implementation of trigger "write_event_member_image_updated" on table "member_image"';
2109 COMMENT ON TRIGGER "write_event_member_image_updated" ON "member_image" IS 'Creates entries in "event" table on member image update';
2112 CREATE FUNCTION "write_event_interest_trigger"()
2113 RETURNS TRIGGER
2114 LANGUAGE 'plpgsql' VOLATILE AS $$
2115 DECLARE
2116 "issue_row" "issue"%ROWTYPE;
2117 "area_row" "area"%ROWTYPE;
2118 BEGIN
2119 IF TG_OP = 'UPDATE' THEN
2120 IF OLD = NEW THEN
2121 RETURN NULL;
2122 END IF;
2123 END IF;
2124 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
2125 SELECT * INTO "issue_row" FROM "issue"
2126 WHERE "id" = OLD."issue_id" FOR SHARE;
2127 SELECT * INTO "area_row" FROM "area"
2128 WHERE "id" = "issue_row"."area_id" FOR SHARE;
2129 IF "issue_row"."id" NOTNULL THEN
2130 INSERT INTO "event" (
2131 "event", "member_id",
2132 "unit_id", "area_id", "policy_id", "issue_id", "state",
2133 "boolean_value"
2134 ) VALUES (
2135 'interest', OLD."member_id",
2136 "area_row"."unit_id", "issue_row"."area_id",
2137 "issue_row"."policy_id",
2138 OLD."issue_id", "issue_row"."state",
2139 FALSE
2140 );
2141 END IF;
2142 END IF;
2143 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2144 SELECT * INTO "issue_row" FROM "issue"
2145 WHERE "id" = NEW."issue_id" FOR SHARE;
2146 SELECT * INTO "area_row" FROM "area"
2147 WHERE "id" = "issue_row"."area_id" FOR SHARE;
2148 INSERT INTO "event" (
2149 "event", "member_id",
2150 "unit_id", "area_id", "policy_id", "issue_id", "state",
2151 "boolean_value"
2152 ) VALUES (
2153 'interest', NEW."member_id",
2154 "area_row"."unit_id", "issue_row"."area_id",
2155 "issue_row"."policy_id",
2156 NEW."issue_id", "issue_row"."state",
2157 TRUE
2158 );
2159 END IF;
2160 RETURN NULL;
2161 END;
2162 $$;
2164 CREATE TRIGGER "write_event_interest"
2165 AFTER INSERT OR UPDATE OR DELETE ON "interest" FOR EACH ROW EXECUTE PROCEDURE
2166 "write_event_interest_trigger"();
2168 COMMENT ON FUNCTION "write_event_interest_trigger"() IS 'Implementation of trigger "write_event_interest_inserted" on table "interest"';
2169 COMMENT ON TRIGGER "write_event_interest" ON "interest" IS 'Create entry in "event" table on adding or removing interest';
2172 CREATE FUNCTION "write_event_initiator_trigger"()
2173 RETURNS TRIGGER
2174 LANGUAGE 'plpgsql' VOLATILE AS $$
2175 DECLARE
2176 "initiative_row" "initiative"%ROWTYPE;
2177 "issue_row" "issue"%ROWTYPE;
2178 "area_row" "area"%ROWTYPE;
2179 BEGIN
2180 IF TG_OP = 'UPDATE' THEN
2181 IF
2182 OLD."initiative_id" = NEW."initiative_id" AND
2183 OLD."member_id" = NEW."member_id" AND
2184 coalesce(OLD."accepted", FALSE) = coalesce(NEW."accepted", FALSE)
2185 THEN
2186 RETURN NULL;
2187 END IF;
2188 END IF;
2189 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "accepted_v" THEN
2190 IF coalesce(OLD."accepted", FALSE) = TRUE THEN
2191 SELECT * INTO "initiative_row" FROM "initiative"
2192 WHERE "id" = OLD."initiative_id" FOR SHARE;
2193 IF "initiative_row"."id" NOTNULL THEN
2194 SELECT * INTO "issue_row" FROM "issue"
2195 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
2196 SELECT * INTO "area_row" FROM "area"
2197 WHERE "id" = "issue_row"."area_id" FOR SHARE;
2198 INSERT INTO "event" (
2199 "event", "member_id",
2200 "unit_id", "area_id", "policy_id", "issue_id", "state",
2201 "initiative_id", "boolean_value"
2202 ) VALUES (
2203 'initiator', OLD."member_id",
2204 "area_row"."unit_id", "issue_row"."area_id",
2205 "issue_row"."policy_id",
2206 "issue_row"."id", "issue_row"."state",
2207 OLD."initiative_id", FALSE
2208 );
2209 END IF;
2210 END IF;
2211 END IF;
2212 IF TG_OP = 'UPDATE' AND NOT "rejected_v" THEN
2213 IF coalesce(NEW."accepted", FALSE) = TRUE THEN
2214 SELECT * INTO "initiative_row" FROM "initiative"
2215 WHERE "id" = NEW."initiative_id" FOR SHARE;
2216 SELECT * INTO "issue_row" FROM "issue"
2217 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
2218 SELECT * INTO "area_row" FROM "area"
2219 WHERE "id" = "issue_row"."area_id" FOR SHARE;
2220 INSERT INTO "event" (
2221 "event", "member_id",
2222 "unit_id", "area_id", "policy_id", "issue_id", "state",
2223 "initiative_id", "boolean_value"
2224 ) VALUES (
2225 'initiator', NEW."member_id",
2226 "area_row"."unit_id", "issue_row"."area_id",
2227 "issue_row"."policy_id",
2228 "issue_row"."id", "issue_row"."state",
2229 NEW."initiative_id", TRUE
2230 );
2231 END IF;
2232 END IF;
2233 RETURN NULL;
2234 END;
2235 $$;
2237 CREATE TRIGGER "write_event_initiator"
2238 AFTER UPDATE OR DELETE ON "initiator" FOR EACH ROW EXECUTE PROCEDURE
2239 "write_event_initiator_trigger"();
2241 COMMENT ON FUNCTION "write_event_initiator_trigger"() IS 'Implementation of trigger "write_event_initiator" on table "initiator"';
2242 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)';
2245 CREATE FUNCTION "write_event_support_trigger"()
2246 RETURNS TRIGGER
2247 LANGUAGE 'plpgsql' VOLATILE AS $$
2248 DECLARE
2249 "issue_row" "issue"%ROWTYPE;
2250 "area_row" "area"%ROWTYPE;
2251 BEGIN
2252 IF TG_OP = 'UPDATE' THEN
2253 IF
2254 OLD."initiative_id" = NEW."initiative_id" AND
2255 OLD."member_id" = NEW."member_id"
2256 THEN
2257 IF OLD."draft_id" != NEW."draft_id" THEN
2258 SELECT * INTO "issue_row" FROM "issue"
2259 WHERE "id" = NEW."issue_id" FOR SHARE;
2260 SELECT * INTO "area_row" FROM "area"
2261 WHERE "id" = "issue_row"."area_id" FOR SHARE;
2262 INSERT INTO "event" (
2263 "event", "member_id",
2264 "unit_id", "area_id", "policy_id", "issue_id", "state",
2265 "initiative_id", "draft_id"
2266 ) VALUES (
2267 'support_updated', NEW."member_id",
2268 "area_row"."unit_id", "issue_row"."area_id",
2269 "issue_row"."policy_id",
2270 "issue_row"."id", "issue_row"."state",
2271 NEW."initiative_id", NEW."draft_id"
2272 );
2273 END IF;
2274 RETURN NULL;
2275 END IF;
2276 END IF;
2277 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
2278 IF EXISTS (
2279 SELECT NULL FROM "initiative" WHERE "id" = OLD."initiative_id"
2280 FOR SHARE
2281 ) THEN
2282 SELECT * INTO "issue_row" FROM "issue"
2283 WHERE "id" = OLD."issue_id" FOR SHARE;
2284 SELECT * INTO "area_row" FROM "area"
2285 WHERE "id" = "issue_row"."area_id" FOR SHARE;
2286 INSERT INTO "event" (
2287 "event", "member_id",
2288 "unit_id", "area_id", "policy_id", "issue_id", "state",
2289 "initiative_id", "boolean_value"
2290 ) VALUES (
2291 'support', OLD."member_id",
2292 "area_row"."unit_id", "issue_row"."area_id",
2293 "issue_row"."policy_id",
2294 "issue_row"."id", "issue_row"."state",
2295 OLD."initiative_id", FALSE
2296 );
2297 END IF;
2298 END IF;
2299 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2300 SELECT * INTO "issue_row" FROM "issue"
2301 WHERE "id" = NEW."issue_id" FOR SHARE;
2302 SELECT * INTO "area_row" FROM "area"
2303 WHERE "id" = "issue_row"."area_id" FOR SHARE;
2304 INSERT INTO "event" (
2305 "event", "member_id",
2306 "unit_id", "area_id", "policy_id", "issue_id", "state",
2307 "initiative_id", "draft_id", "boolean_value"
2308 ) VALUES (
2309 'support', NEW."member_id",
2310 "area_row"."unit_id", "issue_row"."area_id",
2311 "issue_row"."policy_id",
2312 "issue_row"."id", "issue_row"."state",
2313 NEW."initiative_id", NEW."draft_id", TRUE
2314 );
2315 END IF;
2316 RETURN NULL;
2317 END;
2318 $$;
2320 CREATE TRIGGER "write_event_support"
2321 AFTER INSERT OR UPDATE OR DELETE ON "supporter" FOR EACH ROW EXECUTE PROCEDURE
2322 "write_event_support_trigger"();
2324 COMMENT ON FUNCTION "write_event_support_trigger"() IS 'Implementation of trigger "write_event_support" on table "supporter"';
2325 COMMENT ON TRIGGER "write_event_support" ON "supporter" IS 'Create entry in "event" table when adding, updating, or removing support';
2328 CREATE FUNCTION "write_event_suggestion_rated_trigger"()
2329 RETURNS TRIGGER
2330 LANGUAGE 'plpgsql' VOLATILE AS $$
2331 DECLARE
2332 "same_pkey_v" BOOLEAN = FALSE;
2333 "initiative_row" "initiative"%ROWTYPE;
2334 "issue_row" "issue"%ROWTYPE;
2335 "area_row" "area"%ROWTYPE;
2336 BEGIN
2337 IF TG_OP = 'UPDATE' THEN
2338 IF
2339 OLD."suggestion_id" = NEW."suggestion_id" AND
2340 OLD."member_id" = NEW."member_id"
2341 THEN
2342 IF
2343 OLD."degree" = NEW."degree" AND
2344 OLD."fulfilled" = NEW."fulfilled"
2345 THEN
2346 RETURN NULL;
2347 END IF;
2348 "same_pkey_v" := TRUE;
2349 END IF;
2350 END IF;
2351 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "same_pkey_v" THEN
2352 IF EXISTS (
2353 SELECT NULL FROM "suggestion" WHERE "id" = OLD."suggestion_id"
2354 FOR SHARE
2355 ) THEN
2356 SELECT * INTO "initiative_row" FROM "initiative"
2357 WHERE "id" = OLD."initiative_id" FOR SHARE;
2358 SELECT * INTO "issue_row" FROM "issue"
2359 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
2360 SELECT * INTO "area_row" FROM "area"
2361 WHERE "id" = "issue_row"."area_id" FOR SHARE;
2362 INSERT INTO "event" (
2363 "event", "member_id",
2364 "unit_id", "area_id", "policy_id", "issue_id", "state",
2365 "initiative_id", "suggestion_id",
2366 "boolean_value", "numeric_value"
2367 ) VALUES (
2368 'suggestion_rated', OLD."member_id",
2369 "area_row"."unit_id", "issue_row"."area_id",
2370 "issue_row"."policy_id",
2371 "initiative_row"."issue_id", "issue_row"."state",
2372 OLD."initiative_id", OLD."suggestion_id",
2373 NULL, 0
2374 );
2375 END IF;
2376 END IF;
2377 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2378 SELECT * INTO "initiative_row" FROM "initiative"
2379 WHERE "id" = NEW."initiative_id" FOR SHARE;
2380 SELECT * INTO "issue_row" FROM "issue"
2381 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
2382 SELECT * INTO "area_row" FROM "area"
2383 WHERE "id" = "issue_row"."area_id" FOR SHARE;
2384 INSERT INTO "event" (
2385 "event", "member_id",
2386 "unit_id", "area_id", "policy_id", "issue_id", "state",
2387 "initiative_id", "suggestion_id",
2388 "boolean_value", "numeric_value"
2389 ) VALUES (
2390 'suggestion_rated', NEW."member_id",
2391 "area_row"."unit_id", "issue_row"."area_id",
2392 "issue_row"."policy_id",
2393 "initiative_row"."issue_id", "issue_row"."state",
2394 NEW."initiative_id", NEW."suggestion_id",
2395 NEW."fulfilled", NEW."degree"
2396 );
2397 END IF;
2398 RETURN NULL;
2399 END;
2400 $$;
2402 CREATE TRIGGER "write_event_suggestion_rated"
2403 AFTER INSERT OR UPDATE OR DELETE ON "opinion" FOR EACH ROW EXECUTE PROCEDURE
2404 "write_event_suggestion_rated_trigger"();
2406 COMMENT ON FUNCTION "write_event_suggestion_rated_trigger"() IS 'Implementation of trigger "write_event_suggestion_rated" on table "opinion"';
2407 COMMENT ON TRIGGER "write_event_suggestion_rated" ON "opinion" IS 'Create entry in "event" table when adding, updating, or removing support';
2410 CREATE FUNCTION "write_event_delegation_trigger"()
2411 RETURNS TRIGGER
2412 LANGUAGE 'plpgsql' VOLATILE AS $$
2413 DECLARE
2414 "issue_row" "issue"%ROWTYPE;
2415 "area_row" "area"%ROWTYPE;
2416 BEGIN
2417 IF TG_OP = 'DELETE' THEN
2418 IF EXISTS (
2419 SELECT NULL FROM "member" WHERE "id" = OLD."truster_id"
2420 ) AND (CASE OLD."scope"
2421 WHEN 'unit'::"delegation_scope" THEN EXISTS (
2422 SELECT NULL FROM "unit" WHERE "id" = OLD."unit_id"
2423 )
2424 WHEN 'area'::"delegation_scope" THEN EXISTS (
2425 SELECT NULL FROM "area" WHERE "id" = OLD."area_id"
2426 )
2427 WHEN 'issue'::"delegation_scope" THEN EXISTS (
2428 SELECT NULL FROM "issue" WHERE "id" = OLD."issue_id"
2429 )
2430 END) THEN
2431 SELECT * INTO "issue_row" FROM "issue"
2432 WHERE "id" = OLD."issue_id" FOR SHARE;
2433 SELECT * INTO "area_row" FROM "area"
2434 WHERE "id" = COALESCE(OLD."area_id", "issue_row"."area_id")
2435 FOR SHARE;
2436 INSERT INTO "event" (
2437 "event", "member_id", "scope",
2438 "unit_id", "area_id", "issue_id", "state",
2439 "boolean_value"
2440 ) VALUES (
2441 'delegation', OLD."truster_id", OLD."scope",
2442 COALESCE(OLD."unit_id", "area_row"."unit_id"), "area_row"."id",
2443 OLD."issue_id", "issue_row"."state",
2444 FALSE
2445 );
2446 END IF;
2447 ELSE
2448 SELECT * INTO "issue_row" FROM "issue"
2449 WHERE "id" = NEW."issue_id" FOR SHARE;
2450 SELECT * INTO "area_row" FROM "area"
2451 WHERE "id" = COALESCE(NEW."area_id", "issue_row"."area_id")
2452 FOR SHARE;
2453 INSERT INTO "event" (
2454 "event", "member_id", "other_member_id", "scope",
2455 "unit_id", "area_id", "issue_id", "state",
2456 "boolean_value"
2457 ) VALUES (
2458 'delegation', NEW."truster_id", NEW."trustee_id", NEW."scope",
2459 COALESCE(NEW."unit_id", "area_row"."unit_id"), "area_row"."id",
2460 NEW."issue_id", "issue_row"."state",
2461 TRUE
2462 );
2463 END IF;
2464 RETURN NULL;
2465 END;
2466 $$;
2468 CREATE TRIGGER "write_event_delegation"
2469 AFTER INSERT OR UPDATE OR DELETE ON "delegation" FOR EACH ROW EXECUTE PROCEDURE
2470 "write_event_delegation_trigger"();
2472 COMMENT ON FUNCTION "write_event_delegation_trigger"() IS 'Implementation of trigger "write_event_delegation" on table "delegation"';
2473 COMMENT ON TRIGGER "write_event_delegation" ON "delegation" IS 'Create entry in "event" table when adding, updating, or removing a delegation';
2476 CREATE FUNCTION "write_event_contact_trigger"()
2477 RETURNS TRIGGER
2478 LANGUAGE 'plpgsql' VOLATILE AS $$
2479 BEGIN
2480 IF TG_OP = 'UPDATE' THEN
2481 IF
2482 OLD."member_id" = NEW."member_id" AND
2483 OLD."other_member_id" = NEW."other_member_id" AND
2484 OLD."public" = NEW."public"
2485 THEN
2486 RETURN NULL;
2487 END IF;
2488 END IF;
2489 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
2490 IF OLD."public" THEN
2491 IF EXISTS (
2492 SELECT NULL FROM "member" WHERE "id" = OLD."member_id"
2493 FOR SHARE
2494 ) AND EXISTS (
2495 SELECT NULL FROM "member" WHERE "id" = OLD."other_member_id"
2496 FOR SHARE
2497 ) THEN
2498 INSERT INTO "event" (
2499 "event", "member_id", "other_member_id", "boolean_value"
2500 ) VALUES (
2501 'contact', OLD."member_id", OLD."other_member_id", FALSE
2502 );
2503 END IF;
2504 END IF;
2505 END IF;
2506 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2507 IF NEW."public" THEN
2508 INSERT INTO "event" (
2509 "event", "member_id", "other_member_id", "boolean_value"
2510 ) VALUES (
2511 'contact', NEW."member_id", NEW."other_member_id", TRUE
2512 );
2513 END IF;
2514 END IF;
2515 RETURN NULL;
2516 END;
2517 $$;
2519 CREATE TRIGGER "write_event_contact"
2520 AFTER INSERT OR UPDATE OR DELETE ON "contact" FOR EACH ROW EXECUTE PROCEDURE
2521 "write_event_contact_trigger"();
2523 COMMENT ON FUNCTION "write_event_contact_trigger"() IS 'Implementation of trigger "write_event_contact" on table "contact"';
2524 COMMENT ON TRIGGER "write_event_contact" ON "contact" IS 'Create entry in "event" table when adding or removing public contacts';
2527 CREATE FUNCTION "send_event_notify_trigger"()
2528 RETURNS TRIGGER
2529 LANGUAGE 'plpgsql' VOLATILE AS $$
2530 BEGIN
2531 EXECUTE 'NOTIFY "event", ''' || NEW."event" || '''';
2532 RETURN NULL;
2533 END;
2534 $$;
2536 CREATE TRIGGER "send_notify"
2537 AFTER INSERT OR UPDATE ON "event" FOR EACH ROW EXECUTE PROCEDURE
2538 "send_event_notify_trigger"();
2542 ----------------------------
2543 -- Additional constraints --
2544 ----------------------------
2547 CREATE FUNCTION "delete_extended_scope_tokens_trigger"()
2548 RETURNS TRIGGER
2549 LANGUAGE 'plpgsql' VOLATILE AS $$
2550 DECLARE
2551 "system_application_row" "system_application"%ROWTYPE;
2552 BEGIN
2553 IF OLD."system_application_id" NOTNULL THEN
2554 SELECT * FROM "system_application" INTO "system_application_row"
2555 WHERE "id" = OLD."system_application_id";
2556 DELETE FROM "token"
2557 WHERE "member_id" = OLD."member_id"
2558 AND "system_application_id" = OLD."system_application_id"
2559 AND NOT COALESCE(
2560 regexp_split_to_array("scope", E'\\s+') <@
2561 regexp_split_to_array(
2562 "system_application_row"."automatic_scope", E'\\s+'
2563 ),
2564 FALSE
2565 );
2566 END IF;
2567 RETURN OLD;
2568 END;
2569 $$;
2571 CREATE TRIGGER "delete_extended_scope_tokens"
2572 BEFORE DELETE ON "member_application" FOR EACH ROW EXECUTE PROCEDURE
2573 "delete_extended_scope_tokens_trigger"();
2576 CREATE FUNCTION "detach_token_from_session_trigger"()
2577 RETURNS TRIGGER
2578 LANGUAGE 'plpgsql' VOLATILE AS $$
2579 BEGIN
2580 UPDATE "token" SET "session_id" = NULL
2581 WHERE "session_id" = OLD."id";
2582 RETURN OLD;
2583 END;
2584 $$;
2586 CREATE TRIGGER "detach_token_from_session"
2587 BEFORE DELETE ON "session" FOR EACH ROW EXECUTE PROCEDURE
2588 "detach_token_from_session_trigger"();
2591 CREATE FUNCTION "delete_non_detached_scope_with_session_trigger"()
2592 RETURNS TRIGGER
2593 LANGUAGE 'plpgsql' VOLATILE AS $$
2594 BEGIN
2595 IF NEW."session_id" ISNULL THEN
2596 SELECT coalesce(string_agg("element", ' '), '') INTO NEW."scope"
2597 FROM unnest(regexp_split_to_array(NEW."scope", E'\\s+')) AS "element"
2598 WHERE "element" LIKE '%_detached';
2599 END IF;
2600 RETURN NEW;
2601 END;
2602 $$;
2604 CREATE TRIGGER "delete_non_detached_scope_with_session"
2605 BEFORE INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
2606 "delete_non_detached_scope_with_session_trigger"();
2609 CREATE FUNCTION "delete_token_with_empty_scope_trigger"()
2610 RETURNS TRIGGER
2611 LANGUAGE 'plpgsql' VOLATILE AS $$
2612 BEGIN
2613 IF NEW."scope" = '' THEN
2614 DELETE FROM "token" WHERE "id" = NEW."id";
2615 END IF;
2616 RETURN NULL;
2617 END;
2618 $$;
2620 CREATE TRIGGER "delete_token_with_empty_scope"
2621 AFTER INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
2622 "delete_token_with_empty_scope_trigger"();
2625 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
2626 RETURNS TRIGGER
2627 LANGUAGE 'plpgsql' VOLATILE AS $$
2628 BEGIN
2629 IF NOT EXISTS (
2630 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
2631 ) THEN
2632 RAISE EXCEPTION 'Cannot create issue without an initial initiative.' USING
2633 ERRCODE = 'integrity_constraint_violation',
2634 HINT = 'Create issue, initiative, and draft within the same transaction.';
2635 END IF;
2636 RETURN NULL;
2637 END;
2638 $$;
2640 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
2641 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
2642 FOR EACH ROW EXECUTE PROCEDURE
2643 "issue_requires_first_initiative_trigger"();
2645 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
2646 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
2649 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
2650 RETURNS TRIGGER
2651 LANGUAGE 'plpgsql' VOLATILE AS $$
2652 DECLARE
2653 "reference_lost" BOOLEAN;
2654 BEGIN
2655 IF TG_OP = 'DELETE' THEN
2656 "reference_lost" := TRUE;
2657 ELSE
2658 "reference_lost" := NEW."issue_id" != OLD."issue_id";
2659 END IF;
2660 IF
2661 "reference_lost" AND NOT EXISTS (
2662 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
2663 )
2664 THEN
2665 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
2666 END IF;
2667 RETURN NULL;
2668 END;
2669 $$;
2671 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
2672 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
2673 FOR EACH ROW EXECUTE PROCEDURE
2674 "last_initiative_deletes_issue_trigger"();
2676 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
2677 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
2680 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
2681 RETURNS TRIGGER
2682 LANGUAGE 'plpgsql' VOLATILE AS $$
2683 BEGIN
2684 IF NOT EXISTS (
2685 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
2686 ) THEN
2687 RAISE EXCEPTION 'Cannot create initiative without an initial draft.' USING
2688 ERRCODE = 'integrity_constraint_violation',
2689 HINT = 'Create issue, initiative and draft within the same transaction.';
2690 END IF;
2691 RETURN NULL;
2692 END;
2693 $$;
2695 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
2696 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
2697 FOR EACH ROW EXECUTE PROCEDURE
2698 "initiative_requires_first_draft_trigger"();
2700 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
2701 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
2704 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
2705 RETURNS TRIGGER
2706 LANGUAGE 'plpgsql' VOLATILE AS $$
2707 DECLARE
2708 "reference_lost" BOOLEAN;
2709 BEGIN
2710 IF TG_OP = 'DELETE' THEN
2711 "reference_lost" := TRUE;
2712 ELSE
2713 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
2714 END IF;
2715 IF
2716 "reference_lost" AND NOT EXISTS (
2717 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
2718 )
2719 THEN
2720 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
2721 END IF;
2722 RETURN NULL;
2723 END;
2724 $$;
2726 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
2727 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
2728 FOR EACH ROW EXECUTE PROCEDURE
2729 "last_draft_deletes_initiative_trigger"();
2731 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
2732 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
2735 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
2736 RETURNS TRIGGER
2737 LANGUAGE 'plpgsql' VOLATILE AS $$
2738 BEGIN
2739 IF NOT EXISTS (
2740 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
2741 ) THEN
2742 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.' USING
2743 ERRCODE = 'integrity_constraint_violation',
2744 HINT = 'Create suggestion and opinion within the same transaction.';
2745 END IF;
2746 RETURN NULL;
2747 END;
2748 $$;
2750 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
2751 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
2752 FOR EACH ROW EXECUTE PROCEDURE
2753 "suggestion_requires_first_opinion_trigger"();
2755 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
2756 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
2759 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
2760 RETURNS TRIGGER
2761 LANGUAGE 'plpgsql' VOLATILE AS $$
2762 DECLARE
2763 "reference_lost" BOOLEAN;
2764 BEGIN
2765 IF TG_OP = 'DELETE' THEN
2766 "reference_lost" := TRUE;
2767 ELSE
2768 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
2769 END IF;
2770 IF
2771 "reference_lost" AND NOT EXISTS (
2772 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
2773 )
2774 THEN
2775 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
2776 END IF;
2777 RETURN NULL;
2778 END;
2779 $$;
2781 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
2782 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
2783 FOR EACH ROW EXECUTE PROCEDURE
2784 "last_opinion_deletes_suggestion_trigger"();
2786 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
2787 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
2790 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()
2791 RETURNS TRIGGER
2792 LANGUAGE 'plpgsql' VOLATILE AS $$
2793 BEGIN
2794 DELETE FROM "direct_voter"
2795 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
2796 RETURN NULL;
2797 END;
2798 $$;
2800 CREATE TRIGGER "non_voter_deletes_direct_voter"
2801 AFTER INSERT OR UPDATE ON "non_voter"
2802 FOR EACH ROW EXECUTE PROCEDURE
2803 "non_voter_deletes_direct_voter_trigger"();
2805 COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';
2806 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")';
2809 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()
2810 RETURNS TRIGGER
2811 LANGUAGE 'plpgsql' VOLATILE AS $$
2812 BEGIN
2813 DELETE FROM "non_voter"
2814 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
2815 RETURN NULL;
2816 END;
2817 $$;
2819 CREATE TRIGGER "direct_voter_deletes_non_voter"
2820 AFTER INSERT OR UPDATE ON "direct_voter"
2821 FOR EACH ROW EXECUTE PROCEDURE
2822 "direct_voter_deletes_non_voter_trigger"();
2824 COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';
2825 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")';
2828 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()
2829 RETURNS TRIGGER
2830 LANGUAGE 'plpgsql' VOLATILE AS $$
2831 BEGIN
2832 IF NEW."comment" ISNULL THEN
2833 NEW."comment_changed" := NULL;
2834 NEW."formatting_engine" := NULL;
2835 END IF;
2836 RETURN NEW;
2837 END;
2838 $$;
2840 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"
2841 BEFORE INSERT OR UPDATE ON "direct_voter"
2842 FOR EACH ROW EXECUTE PROCEDURE
2843 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();
2845 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"';
2846 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.';
2850 ---------------------------------
2851 -- Delete incomplete snapshots --
2852 ---------------------------------
2855 CREATE FUNCTION "delete_snapshot_on_partial_delete_trigger"()
2856 RETURNS TRIGGER
2857 LANGUAGE 'plpgsql' VOLATILE AS $$
2858 BEGIN
2859 IF TG_OP = 'UPDATE' THEN
2860 IF
2861 OLD."snapshot_id" = NEW."snapshot_id" AND
2862 OLD."issue_id" = NEW."issue_id"
2863 THEN
2864 RETURN NULL;
2865 END IF;
2866 END IF;
2867 DELETE FROM "snapshot" WHERE "id" = OLD."snapshot_id";
2868 RETURN NULL;
2869 END;
2870 $$;
2872 CREATE TRIGGER "delete_snapshot_on_partial_delete"
2873 AFTER UPDATE OR DELETE ON "snapshot_issue"
2874 FOR EACH ROW EXECUTE PROCEDURE
2875 "delete_snapshot_on_partial_delete_trigger"();
2877 COMMENT ON FUNCTION "delete_snapshot_on_partial_delete_trigger"() IS 'Implementation of trigger "delete_snapshot_on_partial_delete" on table "snapshot_issue"';
2878 COMMENT ON TRIGGER "delete_snapshot_on_partial_delete" ON "snapshot_issue" IS 'Deletes whole snapshot if one issue is deleted from the snapshot';
2882 ---------------------------------------------------------------
2883 -- Ensure that votes are not modified when issues are closed --
2884 ---------------------------------------------------------------
2886 -- NOTE: Frontends should ensure this anyway, but in case of programming
2887 -- errors the following triggers ensure data integrity.
2890 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
2891 RETURNS TRIGGER
2892 LANGUAGE 'plpgsql' VOLATILE AS $$
2893 DECLARE
2894 "issue_id_v" "issue"."id"%TYPE;
2895 "issue_row" "issue"%ROWTYPE;
2896 BEGIN
2897 IF EXISTS (
2898 SELECT NULL FROM "temporary_transaction_data"
2899 WHERE "txid" = txid_current()
2900 AND "key" = 'override_protection_triggers'
2901 AND "value" = TRUE::TEXT
2902 ) THEN
2903 RETURN NULL;
2904 END IF;
2905 IF TG_OP = 'DELETE' THEN
2906 "issue_id_v" := OLD."issue_id";
2907 ELSE
2908 "issue_id_v" := NEW."issue_id";
2909 END IF;
2910 SELECT INTO "issue_row" * FROM "issue"
2911 WHERE "id" = "issue_id_v" FOR SHARE;
2912 IF (
2913 "issue_row"."closed" NOTNULL OR (
2914 "issue_row"."state" = 'voting' AND
2915 "issue_row"."phase_finished" NOTNULL
2916 )
2917 ) THEN
2918 IF
2919 TG_RELID = 'direct_voter'::regclass AND
2920 TG_OP = 'UPDATE'
2921 THEN
2922 IF
2923 OLD."issue_id" = NEW."issue_id" AND
2924 OLD."member_id" = NEW."member_id" AND
2925 OLD."weight" = NEW."weight"
2926 THEN
2927 RETURN NULL; -- allows changing of voter comment
2928 END IF;
2929 END IF;
2930 RAISE EXCEPTION 'Tried to modify data after voting has been closed.' USING
2931 ERRCODE = 'integrity_constraint_violation';
2932 END IF;
2933 RETURN NULL;
2934 END;
2935 $$;
2937 CREATE TRIGGER "forbid_changes_on_closed_issue"
2938 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
2939 FOR EACH ROW EXECUTE PROCEDURE
2940 "forbid_changes_on_closed_issue_trigger"();
2942 CREATE TRIGGER "forbid_changes_on_closed_issue"
2943 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
2944 FOR EACH ROW EXECUTE PROCEDURE
2945 "forbid_changes_on_closed_issue_trigger"();
2947 CREATE TRIGGER "forbid_changes_on_closed_issue"
2948 AFTER INSERT OR UPDATE OR DELETE ON "vote"
2949 FOR EACH ROW EXECUTE PROCEDURE
2950 "forbid_changes_on_closed_issue_trigger"();
2952 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"';
2953 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';
2954 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';
2955 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';
2959 --------------------------------------------------------------------
2960 -- Auto-retrieval of fields only needed for referential integrity --
2961 --------------------------------------------------------------------
2964 CREATE FUNCTION "autofill_issue_id_trigger"()
2965 RETURNS TRIGGER
2966 LANGUAGE 'plpgsql' VOLATILE AS $$
2967 BEGIN
2968 IF NEW."issue_id" ISNULL THEN
2969 SELECT "issue_id" INTO NEW."issue_id"
2970 FROM "initiative" WHERE "id" = NEW."initiative_id";
2971 END IF;
2972 RETURN NEW;
2973 END;
2974 $$;
2976 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
2977 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
2979 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
2980 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
2982 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
2983 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
2984 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
2987 CREATE FUNCTION "autofill_initiative_id_trigger"()
2988 RETURNS TRIGGER
2989 LANGUAGE 'plpgsql' VOLATILE AS $$
2990 BEGIN
2991 IF NEW."initiative_id" ISNULL THEN
2992 SELECT "initiative_id" INTO NEW."initiative_id"
2993 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
2994 END IF;
2995 RETURN NEW;
2996 END;
2997 $$;
2999 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
3000 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
3002 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
3003 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
3007 -------------------------------------------------------
3008 -- Automatic copying of values for indexing purposes --
3009 -------------------------------------------------------
3012 CREATE FUNCTION "copy_current_draft_data"
3013 ("initiative_id_p" "initiative"."id"%TYPE )
3014 RETURNS VOID
3015 LANGUAGE 'plpgsql' VOLATILE AS $$
3016 BEGIN
3017 PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p"
3018 FOR UPDATE;
3019 UPDATE "initiative" SET
3020 "location" = "draft"."location",
3021 "draft_text_search_data" = "draft"."text_search_data"
3022 FROM "current_draft" AS "draft"
3023 WHERE "initiative"."id" = "initiative_id_p"
3024 AND "draft"."initiative_id" = "initiative_id_p";
3025 END;
3026 $$;
3028 COMMENT ON FUNCTION "copy_current_draft_data"
3029 ( "initiative"."id"%TYPE )
3030 IS 'Helper function for function "copy_current_draft_data_trigger"';
3033 CREATE FUNCTION "copy_current_draft_data_trigger"()
3034 RETURNS TRIGGER
3035 LANGUAGE 'plpgsql' VOLATILE AS $$
3036 BEGIN
3037 IF TG_OP='DELETE' THEN
3038 PERFORM "copy_current_draft_data"(OLD."initiative_id");
3039 ELSE
3040 IF TG_OP='UPDATE' THEN
3041 IF COALESCE(OLD."inititiave_id" != NEW."initiative_id", TRUE) THEN
3042 PERFORM "copy_current_draft_data"(OLD."initiative_id");
3043 END IF;
3044 END IF;
3045 PERFORM "copy_current_draft_data"(NEW."initiative_id");
3046 END IF;
3047 RETURN NULL;
3048 END;
3049 $$;
3051 CREATE TRIGGER "copy_current_draft_data"
3052 AFTER INSERT OR UPDATE OR DELETE ON "draft"
3053 FOR EACH ROW EXECUTE PROCEDURE
3054 "copy_current_draft_data_trigger"();
3056 COMMENT ON FUNCTION "copy_current_draft_data_trigger"() IS 'Implementation of trigger "copy_current_draft_data" on table "draft"';
3057 COMMENT ON TRIGGER "copy_current_draft_data" ON "draft" IS 'Copy certain fields from most recent "draft" to "initiative"';
3061 -----------------------------------------------------
3062 -- Automatic calculation of certain default values --
3063 -----------------------------------------------------
3066 CREATE FUNCTION "copy_timings_trigger"()
3067 RETURNS TRIGGER
3068 LANGUAGE 'plpgsql' VOLATILE AS $$
3069 DECLARE
3070 "policy_row" "policy"%ROWTYPE;
3071 BEGIN
3072 SELECT * INTO "policy_row" FROM "policy"
3073 WHERE "id" = NEW."policy_id";
3074 IF NEW."min_admission_time" ISNULL THEN
3075 NEW."min_admission_time" := "policy_row"."min_admission_time";
3076 END IF;
3077 IF NEW."max_admission_time" ISNULL THEN
3078 NEW."max_admission_time" := "policy_row"."max_admission_time";
3079 END IF;
3080 IF NEW."discussion_time" ISNULL THEN
3081 NEW."discussion_time" := "policy_row"."discussion_time";
3082 END IF;
3083 IF NEW."verification_time" ISNULL THEN
3084 NEW."verification_time" := "policy_row"."verification_time";
3085 END IF;
3086 IF NEW."voting_time" ISNULL THEN
3087 NEW."voting_time" := "policy_row"."voting_time";
3088 END IF;
3089 RETURN NEW;
3090 END;
3091 $$;
3093 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
3094 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
3096 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
3097 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
3100 CREATE FUNCTION "default_for_draft_id_trigger"()
3101 RETURNS TRIGGER
3102 LANGUAGE 'plpgsql' VOLATILE AS $$
3103 BEGIN
3104 IF NEW."draft_id" ISNULL THEN
3105 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
3106 WHERE "initiative_id" = NEW."initiative_id";
3107 END IF;
3108 RETURN NEW;
3109 END;
3110 $$;
3112 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
3113 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
3114 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
3115 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
3117 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
3118 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';
3119 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';
3123 ----------------------------------------
3124 -- Automatic creation of dependencies --
3125 ----------------------------------------
3128 CREATE FUNCTION "autocreate_interest_trigger"()
3129 RETURNS TRIGGER
3130 LANGUAGE 'plpgsql' VOLATILE AS $$
3131 BEGIN
3132 IF NOT EXISTS (
3133 SELECT NULL FROM "initiative" JOIN "interest"
3134 ON "initiative"."issue_id" = "interest"."issue_id"
3135 WHERE "initiative"."id" = NEW."initiative_id"
3136 AND "interest"."member_id" = NEW."member_id"
3137 ) THEN
3138 BEGIN
3139 INSERT INTO "interest" ("issue_id", "member_id")
3140 SELECT "issue_id", NEW."member_id"
3141 FROM "initiative" WHERE "id" = NEW."initiative_id";
3142 EXCEPTION WHEN unique_violation THEN END;
3143 END IF;
3144 RETURN NEW;
3145 END;
3146 $$;
3148 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
3149 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
3151 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
3152 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';
3155 CREATE FUNCTION "autocreate_supporter_trigger"()
3156 RETURNS TRIGGER
3157 LANGUAGE 'plpgsql' VOLATILE AS $$
3158 BEGIN
3159 IF NOT EXISTS (
3160 SELECT NULL FROM "suggestion" JOIN "supporter"
3161 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
3162 WHERE "suggestion"."id" = NEW."suggestion_id"
3163 AND "supporter"."member_id" = NEW."member_id"
3164 ) THEN
3165 BEGIN
3166 INSERT INTO "supporter" ("initiative_id", "member_id")
3167 SELECT "initiative_id", NEW."member_id"
3168 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
3169 EXCEPTION WHEN unique_violation THEN END;
3170 END IF;
3171 RETURN NEW;
3172 END;
3173 $$;
3175 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
3176 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
3178 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
3179 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.';
3183 ------------------------------------------
3184 -- Views and helper functions for views --
3185 ------------------------------------------
3188 CREATE VIEW "member_eligible_to_be_notified" AS
3189 SELECT * FROM "member"
3190 WHERE "activated" NOTNULL AND "locked" = FALSE;
3192 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")';
3195 CREATE VIEW "member_to_notify" AS
3196 SELECT * FROM "member_eligible_to_be_notified"
3197 WHERE "disable_notifications" = FALSE;
3199 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)';
3202 CREATE VIEW "area_quorum" AS
3203 SELECT
3204 "area"."id" AS "area_id",
3205 ceil(
3206 "area"."quorum_standard"::FLOAT8 * "quorum_factor"::FLOAT8 ^ (
3207 coalesce(
3208 ( SELECT sum(
3209 ( extract(epoch from "area"."quorum_time")::FLOAT8 /
3210 extract(epoch from
3211 ("issue"."accepted"-"issue"."created") +
3212 "issue"."discussion_time" +
3213 "issue"."verification_time" +
3214 "issue"."voting_time"
3215 )::FLOAT8
3216 ) ^ "area"."quorum_exponent"::FLOAT8
3217 )
3218 FROM "issue" JOIN "policy"
3219 ON "issue"."policy_id" = "policy"."id"
3220 WHERE "issue"."area_id" = "area"."id"
3221 AND "issue"."accepted" NOTNULL
3222 AND "issue"."closed" ISNULL
3223 AND "policy"."polling" = FALSE
3224 )::FLOAT8, 0::FLOAT8
3225 ) / "area"."quorum_issues"::FLOAT8 - 1::FLOAT8
3226 ) * CASE WHEN "area"."quorum_den" ISNULL THEN 1 ELSE (
3227 SELECT "snapshot"."population"
3228 FROM "snapshot"
3229 WHERE "snapshot"."area_id" = "area"."id"
3230 AND "snapshot"."issue_id" ISNULL
3231 ORDER BY "snapshot"."id" DESC
3232 LIMIT 1
3233 ) END / coalesce("area"."quorum_den", 1)
3235 )::INT4 AS "issue_quorum"
3236 FROM "area";
3238 COMMENT ON VIEW "area_quorum" IS 'Area-based quorum considering number of open (accepted) issues';
3241 CREATE VIEW "area_with_unaccepted_issues" AS
3242 SELECT DISTINCT ON ("area"."id") "area".*
3243 FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id"
3244 WHERE "issue"."state" = 'admission';
3246 COMMENT ON VIEW "area_with_unaccepted_issues" IS 'All areas with unaccepted open issues (needed for issue admission system)';
3249 CREATE VIEW "issue_for_admission" AS
3250 SELECT DISTINCT ON ("issue"."area_id")
3251 "issue".*,
3252 max("initiative"."supporter_count") AS "max_supporter_count"
3253 FROM "issue"
3254 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
3255 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
3256 JOIN "area" ON "issue"."area_id" = "area"."id"
3257 WHERE "issue"."state" = 'admission'::"issue_state"
3258 AND now() >= "issue"."created" + "issue"."min_admission_time"
3259 AND "initiative"."supporter_count" >= "policy"."issue_quorum"
3260 AND "initiative"."supporter_count" * "policy"."issue_quorum_den" >=
3261 "issue"."population" * "policy"."issue_quorum_num"
3262 AND "initiative"."supporter_count" >= "area"."issue_quorum"
3263 AND "initiative"."revoked" ISNULL
3264 GROUP BY "issue"."id"
3265 ORDER BY "issue"."area_id", "max_supporter_count" DESC, "issue"."id";
3267 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';
3270 CREATE VIEW "unit_delegation" AS
3271 SELECT
3272 "unit"."id" AS "unit_id",
3273 "delegation"."id",
3274 "delegation"."truster_id",
3275 "delegation"."trustee_id",
3276 "delegation"."scope"
3277 FROM "unit"
3278 JOIN "delegation"
3279 ON "delegation"."unit_id" = "unit"."id"
3280 JOIN "member"
3281 ON "delegation"."truster_id" = "member"."id"
3282 JOIN "privilege"
3283 ON "delegation"."unit_id" = "privilege"."unit_id"
3284 AND "delegation"."truster_id" = "privilege"."member_id"
3285 WHERE "member"."active" AND "privilege"."voting_right";
3287 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
3290 CREATE VIEW "area_delegation" AS
3291 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
3292 "area"."id" AS "area_id",
3293 "delegation"."id",
3294 "delegation"."truster_id",
3295 "delegation"."trustee_id",
3296 "delegation"."scope"
3297 FROM "area"
3298 JOIN "delegation"
3299 ON "delegation"."unit_id" = "area"."unit_id"
3300 OR "delegation"."area_id" = "area"."id"
3301 JOIN "member"
3302 ON "delegation"."truster_id" = "member"."id"
3303 JOIN "privilege"
3304 ON "area"."unit_id" = "privilege"."unit_id"
3305 AND "delegation"."truster_id" = "privilege"."member_id"
3306 WHERE "member"."active" AND "privilege"."voting_right"
3307 ORDER BY
3308 "area"."id",
3309 "delegation"."truster_id",
3310 "delegation"."scope" DESC;
3312 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
3315 CREATE VIEW "issue_delegation" AS
3316 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
3317 "issue"."id" AS "issue_id",
3318 "delegation"."id",
3319 "delegation"."truster_id",
3320 "delegation"."trustee_id",
3321 "delegation"."scope"
3322 FROM "issue"
3323 JOIN "area"
3324 ON "area"."id" = "issue"."area_id"
3325 JOIN "delegation"
3326 ON "delegation"."unit_id" = "area"."unit_id"
3327 OR "delegation"."area_id" = "area"."id"
3328 OR "delegation"."issue_id" = "issue"."id"
3329 JOIN "member"
3330 ON "delegation"."truster_id" = "member"."id"
3331 JOIN "privilege"
3332 ON "area"."unit_id" = "privilege"."unit_id"
3333 AND "delegation"."truster_id" = "privilege"."member_id"
3334 WHERE "member"."active" AND "privilege"."voting_right"
3335 ORDER BY
3336 "issue"."id",
3337 "delegation"."truster_id",
3338 "delegation"."scope" DESC;
3340 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
3343 CREATE VIEW "member_count_view" AS
3344 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
3346 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
3349 CREATE VIEW "unit_member" AS
3350 SELECT
3351 "unit"."id" AS "unit_id",
3352 "member"."id" AS "member_id"
3353 FROM "privilege"
3354 JOIN "unit" ON "unit_id" = "privilege"."unit_id"
3355 JOIN "member" ON "member"."id" = "privilege"."member_id"
3356 WHERE "privilege"."voting_right" AND "member"."active";
3358 COMMENT ON VIEW "unit_member" IS 'Active members with voting right in a unit';
3361 CREATE VIEW "unit_member_count" AS
3362 SELECT
3363 "unit"."id" AS "unit_id",
3364 count("unit_member"."member_id") AS "member_count"
3365 FROM "unit" LEFT JOIN "unit_member"
3366 ON "unit"."id" = "unit_member"."unit_id"
3367 GROUP BY "unit"."id";
3369 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
3372 CREATE VIEW "opening_draft" AS
3373 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
3374 ORDER BY "initiative_id", "id";
3376 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
3379 CREATE VIEW "current_draft" AS
3380 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
3381 ORDER BY "initiative_id", "id" DESC;
3383 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
3386 CREATE VIEW "critical_opinion" AS
3387 SELECT * FROM "opinion"
3388 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
3389 OR ("degree" = -2 AND "fulfilled" = TRUE);
3391 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
3394 CREATE VIEW "issue_supporter_in_admission_state" AS
3395 SELECT
3396 "area"."unit_id",
3397 "issue"."area_id",
3398 "issue"."id" AS "issue_id",
3399 "supporter"."member_id",
3400 "direct_interest_snapshot"."weight"
3401 FROM "issue"
3402 JOIN "area" ON "area"."id" = "issue"."area_id"
3403 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
3404 JOIN "direct_interest_snapshot"
3405 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
3406 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
3407 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
3408 WHERE "issue"."state" = 'admission'::"issue_state";
3410 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';
3413 CREATE VIEW "initiative_suggestion_order_calculation" AS
3414 SELECT
3415 "initiative"."id" AS "initiative_id",
3416 ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final"
3417 FROM "initiative" JOIN "issue"
3418 ON "initiative"."issue_id" = "issue"."id"
3419 WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL)
3420 OR ("initiative"."final_suggestion_order_calculated" = FALSE);
3422 COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';
3424 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';
3427 CREATE VIEW "individual_suggestion_ranking" AS
3428 SELECT
3429 "opinion"."initiative_id",
3430 "opinion"."member_id",
3431 "direct_interest_snapshot"."weight",
3432 CASE WHEN
3433 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
3434 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
3435 THEN 1 ELSE
3436 CASE WHEN
3437 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
3438 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
3439 THEN 2 ELSE
3440 CASE WHEN
3441 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
3442 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
3443 THEN 3 ELSE 4 END
3444 END
3445 END AS "preference",
3446 "opinion"."suggestion_id"
3447 FROM "opinion"
3448 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
3449 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
3450 JOIN "direct_interest_snapshot"
3451 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
3452 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
3453 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
3455 COMMENT ON VIEW "individual_suggestion_ranking" IS 'Helper view for "lf_update_suggestion_order" to allow a proportional ordering of suggestions within an initiative';
3458 CREATE VIEW "battle_participant" AS
3459 SELECT "initiative"."id", "initiative"."issue_id"
3460 FROM "issue" JOIN "initiative"
3461 ON "issue"."id" = "initiative"."issue_id"
3462 WHERE "initiative"."admitted"
3463 UNION ALL
3464 SELECT NULL, "id" AS "issue_id"
3465 FROM "issue";
3467 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
3470 CREATE VIEW "battle_view" AS
3471 SELECT
3472 "issue"."id" AS "issue_id",
3473 "winning_initiative"."id" AS "winning_initiative_id",
3474 "losing_initiative"."id" AS "losing_initiative_id",
3475 sum(
3476 CASE WHEN
3477 coalesce("better_vote"."grade", 0) >
3478 coalesce("worse_vote"."grade", 0)
3479 THEN "direct_voter"."weight" ELSE 0 END
3480 ) AS "count"
3481 FROM "issue"
3482 LEFT JOIN "direct_voter"
3483 ON "issue"."id" = "direct_voter"."issue_id"
3484 JOIN "battle_participant" AS "winning_initiative"
3485 ON "issue"."id" = "winning_initiative"."issue_id"
3486 JOIN "battle_participant" AS "losing_initiative"
3487 ON "issue"."id" = "losing_initiative"."issue_id"
3488 LEFT JOIN "vote" AS "better_vote"
3489 ON "direct_voter"."member_id" = "better_vote"."member_id"
3490 AND "winning_initiative"."id" = "better_vote"."initiative_id"
3491 LEFT JOIN "vote" AS "worse_vote"
3492 ON "direct_voter"."member_id" = "worse_vote"."member_id"
3493 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
3494 WHERE "issue"."state" = 'voting'
3495 AND "issue"."phase_finished" NOTNULL
3496 AND (
3497 "winning_initiative"."id" != "losing_initiative"."id" OR
3498 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
3499 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
3500 GROUP BY
3501 "issue"."id",
3502 "winning_initiative"."id",
3503 "losing_initiative"."id";
3505 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';
3508 CREATE VIEW "expired_session" AS
3509 SELECT * FROM "session" WHERE now() > "expiry";
3511 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
3512 DELETE FROM "session" WHERE "id" = OLD."id";
3514 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
3515 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
3518 CREATE VIEW "expired_token" AS
3519 SELECT * FROM "token" WHERE now() > "expiry" AND NOT (
3520 "token_type" = 'authorization' AND "used" AND EXISTS (
3521 SELECT NULL FROM "token" AS "other"
3522 WHERE "other"."authorization_token_id" = "id" ) );
3524 CREATE RULE "delete" AS ON DELETE TO "expired_token" DO INSTEAD
3525 DELETE FROM "token" WHERE "id" = OLD."id";
3527 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';
3530 CREATE VIEW "unused_snapshot" AS
3531 SELECT "snapshot".* FROM "snapshot"
3532 LEFT JOIN "issue"
3533 ON "snapshot"."id" = "issue"."latest_snapshot_id"
3534 OR "snapshot"."id" = "issue"."admission_snapshot_id"
3535 OR "snapshot"."id" = "issue"."half_freeze_snapshot_id"
3536 OR "snapshot"."id" = "issue"."full_freeze_snapshot_id"
3537 WHERE "issue"."id" ISNULL;
3539 CREATE RULE "delete" AS ON DELETE TO "unused_snapshot" DO INSTEAD
3540 DELETE FROM "snapshot" WHERE "id" = OLD."id";
3542 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)';
3545 CREATE VIEW "expired_snapshot" AS
3546 SELECT "unused_snapshot".* FROM "unused_snapshot" CROSS JOIN "system_setting"
3547 WHERE "unused_snapshot"."calculated" <
3548 now() - "system_setting"."snapshot_retention";
3550 CREATE RULE "delete" AS ON DELETE TO "expired_snapshot" DO INSTEAD
3551 DELETE FROM "snapshot" WHERE "id" = OLD."id";
3553 COMMENT ON VIEW "expired_snapshot" IS 'Contains "unused_snapshot"s that are older than "system_setting"."snapshot_retention" (for deletion)';
3556 CREATE VIEW "open_issue" AS
3557 SELECT * FROM "issue" WHERE "closed" ISNULL;
3559 COMMENT ON VIEW "open_issue" IS 'All open issues';
3562 CREATE VIEW "member_contingent" AS
3563 SELECT
3564 "member"."id" AS "member_id",
3565 "contingent"."polling",
3566 "contingent"."time_frame",
3567 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
3568 (
3569 SELECT count(1) FROM "draft"
3570 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
3571 WHERE "draft"."author_id" = "member"."id"
3572 AND "initiative"."polling" = "contingent"."polling"
3573 AND "draft"."created" > now() - "contingent"."time_frame"
3574 ) + (
3575 SELECT count(1) FROM "suggestion"
3576 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
3577 WHERE "suggestion"."author_id" = "member"."id"
3578 AND "contingent"."polling" = FALSE
3579 AND "suggestion"."created" > now() - "contingent"."time_frame"
3580 )
3581 ELSE NULL END AS "text_entry_count",
3582 "contingent"."text_entry_limit",
3583 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
3584 SELECT count(1) FROM "opening_draft" AS "draft"
3585 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
3586 WHERE "draft"."author_id" = "member"."id"
3587 AND "initiative"."polling" = "contingent"."polling"
3588 AND "draft"."created" > now() - "contingent"."time_frame"
3589 ) ELSE NULL END AS "initiative_count",
3590 "contingent"."initiative_limit"
3591 FROM "member" CROSS JOIN "contingent";
3593 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
3595 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
3596 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
3599 CREATE VIEW "member_contingent_left" AS
3600 SELECT
3601 "member_id",
3602 "polling",
3603 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
3604 max("initiative_limit" - "initiative_count") AS "initiatives_left"
3605 FROM "member_contingent" GROUP BY "member_id", "polling";
3607 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.';
3610 CREATE VIEW "event_for_notification" AS
3611 SELECT
3612 "member"."id" AS "recipient_id",
3613 "event".*
3614 FROM "member" CROSS JOIN "event"
3615 JOIN "issue" ON "issue"."id" = "event"."issue_id"
3616 JOIN "area" ON "area"."id" = "issue"."area_id"
3617 LEFT JOIN "privilege" ON
3618 "privilege"."member_id" = "member"."id" AND
3619 "privilege"."unit_id" = "area"."unit_id" AND
3620 "privilege"."voting_right" = TRUE
3621 LEFT JOIN "subscription" ON
3622 "subscription"."member_id" = "member"."id" AND
3623 "subscription"."unit_id" = "area"."unit_id"
3624 LEFT JOIN "ignored_area" ON
3625 "ignored_area"."member_id" = "member"."id" AND
3626 "ignored_area"."area_id" = "issue"."area_id"
3627 LEFT JOIN "interest" ON
3628 "interest"."member_id" = "member"."id" AND
3629 "interest"."issue_id" = "event"."issue_id"
3630 LEFT JOIN "supporter" ON
3631 "supporter"."member_id" = "member"."id" AND
3632 "supporter"."initiative_id" = "event"."initiative_id"
3633 WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL)
3634 AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
3635 AND (
3636 "event"."event" = 'issue_state_changed'::"event_type" OR
3637 ( "event"."event" = 'initiative_revoked'::"event_type" AND
3638 "supporter"."member_id" NOTNULL ) );
3640 COMMENT ON VIEW "event_for_notification" IS 'Entries of the "event" table which are of interest for a particular notification mail recipient';
3642 COMMENT ON COLUMN "event_for_notification"."recipient_id" IS 'member_id of the recipient of a notification mail';
3645 CREATE VIEW "updated_initiative" AS
3646 SELECT
3647 "supporter"."member_id" AS "recipient_id",
3648 FALSE AS "featured",
3649 "supporter"."initiative_id"
3650 FROM "supporter"
3651 JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id"
3652 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
3653 LEFT JOIN "notification_initiative_sent" AS "sent" ON
3654 "sent"."member_id" = "supporter"."member_id" AND
3655 "sent"."initiative_id" = "supporter"."initiative_id"
3656 LEFT JOIN "ignored_initiative" ON
3657 "ignored_initiative"."member_id" = "supporter"."member_id" AND
3658 "ignored_initiative"."initiative_id" = "supporter"."initiative_id"
3659 WHERE "issue"."state" IN ('admission', 'discussion')
3660 AND "initiative"."revoked" ISNULL
3661 AND "ignored_initiative"."member_id" ISNULL
3662 AND (
3663 EXISTS (
3664 SELECT NULL FROM "draft"
3665 LEFT JOIN "ignored_member" ON
3666 "ignored_member"."member_id" = "supporter"."member_id" AND
3667 "ignored_member"."other_member_id" = "draft"."author_id"
3668 WHERE "draft"."initiative_id" = "supporter"."initiative_id"
3669 AND "draft"."id" > "supporter"."draft_id"
3670 AND "ignored_member"."member_id" ISNULL
3671 ) OR EXISTS (
3672 SELECT NULL FROM "suggestion"
3673 LEFT JOIN "opinion" ON
3674 "opinion"."member_id" = "supporter"."member_id" AND
3675 "opinion"."suggestion_id" = "suggestion"."id"
3676 LEFT JOIN "ignored_member" ON
3677 "ignored_member"."member_id" = "supporter"."member_id" AND
3678 "ignored_member"."other_member_id" = "suggestion"."author_id"
3679 WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"
3680 AND "opinion"."member_id" ISNULL
3681 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
3682 AND "ignored_member"."member_id" ISNULL
3683 )
3684 );
3686 COMMENT ON VIEW "updated_initiative" IS 'Helper view for view "updated_or_featured_initiative"';
3689 CREATE FUNCTION "featured_initiative"
3690 ( "recipient_id_p" "member"."id"%TYPE,
3691 "area_id_p" "area"."id"%TYPE )
3692 RETURNS SETOF "initiative"."id"%TYPE
3693 LANGUAGE 'plpgsql' STABLE AS $$
3694 DECLARE
3695 "counter_v" "member"."notification_counter"%TYPE;
3696 "sample_size_v" "member"."notification_sample_size"%TYPE;
3697 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
3698 "match_v" BOOLEAN;
3699 "member_id_v" "member"."id"%TYPE;
3700 "seed_v" TEXT;
3701 "initiative_id_v" "initiative"."id"%TYPE;
3702 BEGIN
3703 SELECT "notification_counter", "notification_sample_size"
3704 INTO "counter_v", "sample_size_v"
3705 FROM "member" WHERE "id" = "recipient_id_p";
3706 IF COALESCE("sample_size_v" <= 0, TRUE) THEN
3707 RETURN;
3708 END IF;
3709 "initiative_id_ary" := '{}';
3710 LOOP
3711 "match_v" := FALSE;
3712 FOR "member_id_v", "seed_v" IN
3713 SELECT * FROM (
3714 SELECT DISTINCT
3715 "supporter"."member_id",
3716 md5(
3717 "recipient_id_p" || '-' ||
3718 "counter_v" || '-' ||
3719 "area_id_p" || '-' ||
3720 "supporter"."member_id"
3721 ) AS "seed"
3722 FROM "supporter"
3723 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
3724 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
3725 WHERE "supporter"."member_id" != "recipient_id_p"
3726 AND "issue"."area_id" = "area_id_p"
3727 AND "issue"."state" IN ('admission', 'discussion', 'verification')
3728 ) AS "subquery"
3729 ORDER BY "seed"
3730 LOOP
3731 SELECT "initiative"."id" INTO "initiative_id_v"
3732 FROM "initiative"
3733 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
3734 JOIN "area" ON "area"."id" = "issue"."area_id"
3735 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
3736 LEFT JOIN "supporter" AS "self_support" ON
3737 "self_support"."initiative_id" = "initiative"."id" AND
3738 "self_support"."member_id" = "recipient_id_p"
3739 LEFT JOIN "privilege" ON
3740 "privilege"."member_id" = "recipient_id_p" AND
3741 "privilege"."unit_id" = "area"."unit_id" AND
3742 "privilege"."voting_right" = TRUE
3743 LEFT JOIN "subscription" ON
3744 "subscription"."member_id" = "recipient_id_p" AND
3745 "subscription"."unit_id" = "area"."unit_id"
3746 LEFT JOIN "ignored_initiative" ON
3747 "ignored_initiative"."member_id" = "recipient_id_p" AND
3748 "ignored_initiative"."initiative_id" = "initiative"."id"
3749 WHERE "supporter"."member_id" = "member_id_v"
3750 AND "issue"."area_id" = "area_id_p"
3751 AND "issue"."state" IN ('admission', 'discussion', 'verification')
3752 AND "initiative"."revoked" ISNULL
3753 AND "self_support"."member_id" ISNULL
3754 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
3755 AND (
3756 "privilege"."member_id" NOTNULL OR
3757 "subscription"."member_id" NOTNULL )
3758 AND "ignored_initiative"."member_id" ISNULL
3759 AND NOT EXISTS (
3760 SELECT NULL FROM "draft"
3761 JOIN "ignored_member" ON
3762 "ignored_member"."member_id" = "recipient_id_p" AND
3763 "ignored_member"."other_member_id" = "draft"."author_id"
3764 WHERE "draft"."initiative_id" = "initiative"."id"
3765 )
3766 ORDER BY md5("seed_v" || '-' || "initiative"."id")
3767 LIMIT 1;
3768 IF FOUND THEN
3769 "match_v" := TRUE;
3770 RETURN NEXT "initiative_id_v";
3771 IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
3772 RETURN;
3773 END IF;
3774 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
3775 END IF;
3776 END LOOP;
3777 EXIT WHEN NOT "match_v";
3778 END LOOP;
3779 RETURN;
3780 END;
3781 $$;
3783 COMMENT ON FUNCTION "featured_initiative"
3784 ( "recipient_id_p" "member"."id"%TYPE,
3785 "area_id_p" "area"."id"%TYPE )
3786 IS 'Helper function for view "updated_or_featured_initiative"';
3789 CREATE VIEW "updated_or_featured_initiative" AS
3790 SELECT
3791 "subquery".*,
3792 NOT EXISTS (
3793 SELECT NULL FROM "initiative" AS "better_initiative"
3794 WHERE "better_initiative"."issue_id" = "initiative"."issue_id"
3795 AND
3796 ( COALESCE("better_initiative"."supporter_count", -1),
3797 -"better_initiative"."id" ) >
3798 ( COALESCE("initiative"."supporter_count", -1),
3799 -"initiative"."id" )
3800 ) AS "leading"
3801 FROM (
3802 SELECT * FROM "updated_initiative"
3803 UNION ALL
3804 SELECT
3805 "member"."id" AS "recipient_id",
3806 TRUE AS "featured",
3807 "featured_initiative_id" AS "initiative_id"
3808 FROM "member" CROSS JOIN "area"
3809 CROSS JOIN LATERAL
3810 "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id"
3811 JOIN "initiative" ON "initiative"."id" = "featured_initiative_id"
3812 ) AS "subquery"
3813 JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id";
3815 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';
3817 COMMENT ON COLUMN "updated_or_featured_initiative"."recipient_id" IS '"id" of the member who receives the notification mail';
3818 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")';
3819 COMMENT ON COLUMN "updated_or_featured_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
3820 COMMENT ON COLUMN "updated_or_featured_initiative"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue';
3823 CREATE VIEW "leading_complement_initiative" AS
3824 SELECT * FROM (
3825 SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id")
3826 "uf_initiative"."recipient_id",
3827 FALSE AS "featured",
3828 "uf_initiative"."initiative_id",
3829 TRUE AS "leading"
3830 FROM "updated_or_featured_initiative" AS "uf_initiative"
3831 JOIN "initiative" AS "uf_initiative_full" ON
3832 "uf_initiative_full"."id" = "uf_initiative"."initiative_id"
3833 JOIN "initiative" ON
3834 "initiative"."issue_id" = "uf_initiative_full"."issue_id"
3835 WHERE "initiative"."revoked" ISNULL
3836 ORDER BY
3837 "uf_initiative"."recipient_id",
3838 "initiative"."issue_id",
3839 "initiative"."supporter_count" DESC,
3840 "initiative"."id"
3841 ) AS "subquery"
3842 WHERE NOT EXISTS (
3843 SELECT NULL FROM "updated_or_featured_initiative" AS "other"
3844 WHERE "other"."recipient_id" = "subquery"."recipient_id"
3845 AND "other"."initiative_id" = "subquery"."initiative_id"
3846 );
3848 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';
3849 COMMENT ON COLUMN "leading_complement_initiative"."featured" IS 'Always FALSE in this view';
3850 COMMENT ON COLUMN "leading_complement_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
3851 COMMENT ON COLUMN "leading_complement_initiative"."leading" IS 'Always TRUE in this view';
3854 CREATE VIEW "unfiltered_initiative_for_notification" AS
3855 SELECT
3856 "subquery".*,
3857 "supporter"."member_id" NOTNULL AS "supported",
3858 CASE WHEN "supporter"."member_id" NOTNULL THEN
3859 EXISTS (
3860 SELECT NULL FROM "draft"
3861 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
3862 AND "draft"."id" > "supporter"."draft_id"
3863 )
3864 ELSE
3865 EXISTS (
3866 SELECT NULL FROM "draft"
3867 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
3868 AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE)
3869 )
3870 END AS "new_draft",
3871 CASE WHEN "supporter"."member_id" NOTNULL THEN
3872 ( SELECT count(1) FROM "suggestion"
3873 LEFT JOIN "opinion" ON
3874 "opinion"."member_id" = "supporter"."member_id" AND
3875 "opinion"."suggestion_id" = "suggestion"."id"
3876 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
3877 AND "opinion"."member_id" ISNULL
3878 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
3879 )
3880 ELSE
3881 ( SELECT count(1) FROM "suggestion"
3882 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
3883 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
3884 )
3885 END AS "new_suggestion_count"
3886 FROM (
3887 SELECT * FROM "updated_or_featured_initiative"
3888 UNION ALL
3889 SELECT * FROM "leading_complement_initiative"
3890 ) AS "subquery"
3891 LEFT JOIN "supporter" ON
3892 "supporter"."member_id" = "subquery"."recipient_id" AND
3893 "supporter"."initiative_id" = "subquery"."initiative_id"
3894 LEFT JOIN "notification_initiative_sent" AS "sent" ON
3895 "sent"."member_id" = "subquery"."recipient_id" AND
3896 "sent"."initiative_id" = "subquery"."initiative_id";
3898 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';
3900 COMMENT ON COLUMN "unfiltered_initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient';
3901 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)';
3902 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")';
3905 CREATE VIEW "initiative_for_notification" AS
3906 SELECT "unfiltered1".*
3907 FROM "unfiltered_initiative_for_notification" "unfiltered1"
3908 JOIN "initiative" AS "initiative1" ON
3909 "initiative1"."id" = "unfiltered1"."initiative_id"
3910 JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id"
3911 WHERE EXISTS (
3912 SELECT NULL
3913 FROM "unfiltered_initiative_for_notification" "unfiltered2"
3914 JOIN "initiative" AS "initiative2" ON
3915 "initiative2"."id" = "unfiltered2"."initiative_id"
3916 JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id"
3917 WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"
3918 AND "issue1"."area_id" = "issue2"."area_id"
3919 AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 )
3920 );
3922 COMMENT ON VIEW "initiative_for_notification" IS 'Initiatives to be included in a scheduled notification mail';
3924 COMMENT ON COLUMN "initiative_for_notification"."recipient_id" IS '"id" of the member who receives the notification mail';
3925 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")';
3926 COMMENT ON COLUMN "initiative_for_notification"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
3927 COMMENT ON COLUMN "initiative_for_notification"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue';
3928 COMMENT ON COLUMN "initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient';
3929 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)';
3930 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")';
3933 CREATE VIEW "scheduled_notification_to_send" AS
3934 SELECT * FROM (
3935 SELECT
3936 "id" AS "recipient_id",
3937 now() - CASE WHEN "notification_dow" ISNULL THEN
3938 ( "notification_sent"::DATE + CASE
3939 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
3940 THEN 0 ELSE 1 END
3941 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
3942 ELSE
3943 ( "notification_sent"::DATE +
3944 ( 7 + "notification_dow" -
3945 EXTRACT(DOW FROM
3946 ( "notification_sent"::DATE + CASE
3947 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
3948 THEN 0 ELSE 1 END
3949 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
3950 )::INTEGER
3951 ) % 7 +
3952 CASE
3953 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
3954 THEN 0 ELSE 1
3955 END
3956 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
3957 END AS "pending"
3958 FROM (
3959 SELECT
3960 "id",
3961 COALESCE("notification_sent", "activated") AS "notification_sent",
3962 "notification_dow",
3963 "notification_hour"
3964 FROM "member_to_notify"
3965 WHERE "notification_hour" NOTNULL
3966 ) AS "subquery1"
3967 ) AS "subquery2"
3968 WHERE "pending" > '0'::INTERVAL;
3970 COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending';
3972 COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail';
3973 COMMENT ON COLUMN "scheduled_notification_to_send"."pending" IS 'Duration for which the notification mail has already been pending';
3976 CREATE VIEW "newsletter_to_send" AS
3977 SELECT
3978 "member"."id" AS "recipient_id",
3979 "newsletter"."id" AS "newsletter_id",
3980 "newsletter"."published"
3981 FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"
3982 LEFT JOIN "privilege" ON
3983 "privilege"."member_id" = "member"."id" AND
3984 "privilege"."unit_id" = "newsletter"."unit_id" AND
3985 "privilege"."voting_right" = TRUE
3986 LEFT JOIN "subscription" ON
3987 "subscription"."member_id" = "member"."id" AND
3988 "subscription"."unit_id" = "newsletter"."unit_id"
3989 WHERE "newsletter"."published" <= now()
3990 AND "newsletter"."sent" ISNULL
3991 AND (
3992 "member"."disable_notifications" = FALSE OR
3993 "newsletter"."include_all_members" = TRUE )
3994 AND (
3995 "newsletter"."unit_id" ISNULL OR
3996 "privilege"."member_id" NOTNULL OR
3997 "subscription"."member_id" NOTNULL );
3999 COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';
4001 COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter was supposed to be sent out (can be used for ordering)';
4005 ------------------------------------------------------
4006 -- Row set returning function for delegation chains --
4007 ------------------------------------------------------
4010 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
4011 ('first', 'intermediate', 'last', 'repetition');
4013 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
4016 CREATE TYPE "delegation_chain_row" AS (
4017 "index" INT4,
4018 "member_id" INT4,
4019 "member_valid" BOOLEAN,
4020 "participation" BOOLEAN,
4021 "overridden" BOOLEAN,
4022 "scope_in" "delegation_scope",
4023 "scope_out" "delegation_scope",
4024 "disabled_out" BOOLEAN,
4025 "loop" "delegation_chain_loop_tag" );
4027 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
4029 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
4030 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest; for area and global delegation chains: always null';
4031 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
4032 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
4033 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
4034 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
4035 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
4038 CREATE FUNCTION "delegation_chain_for_closed_issue"
4039 ( "member_id_p" "member"."id"%TYPE,
4040 "issue_id_p" "issue"."id"%TYPE )
4041 RETURNS SETOF "delegation_chain_row"
4042 LANGUAGE 'plpgsql' STABLE AS $$
4043 DECLARE
4044 "output_row" "delegation_chain_row";
4045 "direct_voter_row" "direct_voter"%ROWTYPE;
4046 "delegating_voter_row" "delegating_voter"%ROWTYPE;
4047 BEGIN
4048 "output_row"."index" := 0;
4049 "output_row"."member_id" := "member_id_p";
4050 "output_row"."member_valid" := TRUE;
4051 "output_row"."participation" := FALSE;
4052 "output_row"."overridden" := FALSE;
4053 "output_row"."disabled_out" := FALSE;
4054 LOOP
4055 SELECT INTO "direct_voter_row" * FROM "direct_voter"
4056 WHERE "issue_id" = "issue_id_p"
4057 AND "member_id" = "output_row"."member_id";
4058 IF "direct_voter_row"."member_id" NOTNULL THEN
4059 "output_row"."participation" := TRUE;
4060 "output_row"."scope_out" := NULL;
4061 "output_row"."disabled_out" := NULL;
4062 RETURN NEXT "output_row";
4063 RETURN;
4064 END IF;
4065 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
4066 WHERE "issue_id" = "issue_id_p"
4067 AND "member_id" = "output_row"."member_id";
4068 IF "delegating_voter_row"."member_id" ISNULL THEN
4069 RETURN;
4070 END IF;
4071 "output_row"."scope_out" := "delegating_voter_row"."scope";
4072 RETURN NEXT "output_row";
4073 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
4074 "output_row"."scope_in" := "output_row"."scope_out";
4075 END LOOP;
4076 END;
4077 $$;
4079 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
4080 ( "member"."id"%TYPE,
4081 "member"."id"%TYPE )
4082 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
4085 CREATE FUNCTION "delegation_chain"
4086 ( "member_id_p" "member"."id"%TYPE,
4087 "unit_id_p" "unit"."id"%TYPE,
4088 "area_id_p" "area"."id"%TYPE,
4089 "issue_id_p" "issue"."id"%TYPE,
4090 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
4091 "simulate_default_p" BOOLEAN DEFAULT FALSE )
4092 RETURNS SETOF "delegation_chain_row"
4093 LANGUAGE 'plpgsql' STABLE AS $$
4094 DECLARE
4095 "scope_v" "delegation_scope";
4096 "unit_id_v" "unit"."id"%TYPE;
4097 "area_id_v" "area"."id"%TYPE;
4098 "issue_row" "issue"%ROWTYPE;
4099 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
4100 "loop_member_id_v" "member"."id"%TYPE;
4101 "output_row" "delegation_chain_row";
4102 "output_rows" "delegation_chain_row"[];
4103 "simulate_v" BOOLEAN;
4104 "simulate_here_v" BOOLEAN;
4105 "delegation_row" "delegation"%ROWTYPE;
4106 "row_count" INT4;
4107 "i" INT4;
4108 "loop_v" BOOLEAN;
4109 BEGIN
4110 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
4111 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
4112 END IF;
4113 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
4114 "simulate_v" := TRUE;
4115 ELSE
4116 "simulate_v" := FALSE;
4117 END IF;
4118 IF
4119 "unit_id_p" NOTNULL AND
4120 "area_id_p" ISNULL AND
4121 "issue_id_p" ISNULL
4122 THEN
4123 "scope_v" := 'unit';
4124 "unit_id_v" := "unit_id_p";
4125 ELSIF
4126 "unit_id_p" ISNULL AND
4127 "area_id_p" NOTNULL AND
4128 "issue_id_p" ISNULL
4129 THEN
4130 "scope_v" := 'area';
4131 "area_id_v" := "area_id_p";
4132 SELECT "unit_id" INTO "unit_id_v"
4133 FROM "area" WHERE "id" = "area_id_v";
4134 ELSIF
4135 "unit_id_p" ISNULL AND
4136 "area_id_p" ISNULL AND
4137 "issue_id_p" NOTNULL
4138 THEN
4139 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
4140 IF "issue_row"."id" ISNULL THEN
4141 RETURN;
4142 END IF;
4143 IF "issue_row"."closed" NOTNULL THEN
4144 IF "simulate_v" THEN
4145 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
4146 END IF;
4147 FOR "output_row" IN
4148 SELECT * FROM
4149 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
4150 LOOP
4151 RETURN NEXT "output_row";
4152 END LOOP;
4153 RETURN;
4154 END IF;
4155 "scope_v" := 'issue';
4156 SELECT "area_id" INTO "area_id_v"
4157 FROM "issue" WHERE "id" = "issue_id_p";
4158 SELECT "unit_id" INTO "unit_id_v"
4159 FROM "area" WHERE "id" = "area_id_v";
4160 ELSE
4161 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
4162 END IF;
4163 "visited_member_ids" := '{}';
4164 "loop_member_id_v" := NULL;
4165 "output_rows" := '{}';
4166 "output_row"."index" := 0;
4167 "output_row"."member_id" := "member_id_p";
4168 "output_row"."member_valid" := TRUE;
4169 "output_row"."participation" := FALSE;
4170 "output_row"."overridden" := FALSE;
4171 "output_row"."disabled_out" := FALSE;
4172 "output_row"."scope_out" := NULL;
4173 LOOP
4174 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
4175 "loop_member_id_v" := "output_row"."member_id";
4176 ELSE
4177 "visited_member_ids" :=
4178 "visited_member_ids" || "output_row"."member_id";
4179 END IF;
4180 IF "output_row"."participation" ISNULL THEN
4181 "output_row"."overridden" := NULL;
4182 ELSIF "output_row"."participation" THEN
4183 "output_row"."overridden" := TRUE;
4184 END IF;
4185 "output_row"."scope_in" := "output_row"."scope_out";
4186 "output_row"."member_valid" := EXISTS (
4187 SELECT NULL FROM "member" JOIN "privilege"
4188 ON "privilege"."member_id" = "member"."id"
4189 AND "privilege"."unit_id" = "unit_id_v"
4190 WHERE "id" = "output_row"."member_id"
4191 AND "member"."active" AND "privilege"."voting_right"
4192 );
4193 "simulate_here_v" := (
4194 "simulate_v" AND
4195 "output_row"."member_id" = "member_id_p"
4196 );
4197 "delegation_row" := ROW(NULL);
4198 IF "output_row"."member_valid" OR "simulate_here_v" THEN
4199 IF "scope_v" = 'unit' THEN
4200 IF NOT "simulate_here_v" THEN
4201 SELECT * INTO "delegation_row" FROM "delegation"
4202 WHERE "truster_id" = "output_row"."member_id"
4203 AND "unit_id" = "unit_id_v";
4204 END IF;
4205 ELSIF "scope_v" = 'area' THEN
4206 IF "simulate_here_v" THEN
4207 IF "simulate_trustee_id_p" ISNULL THEN
4208 SELECT * INTO "delegation_row" FROM "delegation"
4209 WHERE "truster_id" = "output_row"."member_id"
4210 AND "unit_id" = "unit_id_v";
4211 END IF;
4212 ELSE
4213 SELECT * INTO "delegation_row" FROM "delegation"
4214 WHERE "truster_id" = "output_row"."member_id"
4215 AND (
4216 "unit_id" = "unit_id_v" OR
4217 "area_id" = "area_id_v"
4218 )
4219 ORDER BY "scope" DESC;
4220 END IF;
4221 ELSIF "scope_v" = 'issue' THEN
4222 IF "issue_row"."fully_frozen" ISNULL THEN
4223 "output_row"."participation" := EXISTS (
4224 SELECT NULL FROM "interest"
4225 WHERE "issue_id" = "issue_id_p"
4226 AND "member_id" = "output_row"."member_id"
4227 );
4228 ELSE
4229 IF "output_row"."member_id" = "member_id_p" THEN
4230 "output_row"."participation" := EXISTS (
4231 SELECT NULL FROM "direct_voter"
4232 WHERE "issue_id" = "issue_id_p"
4233 AND "member_id" = "output_row"."member_id"
4234 );
4235 ELSE
4236 "output_row"."participation" := NULL;
4237 END IF;
4238 END IF;
4239 IF "simulate_here_v" THEN
4240 IF "simulate_trustee_id_p" ISNULL THEN
4241 SELECT * INTO "delegation_row" FROM "delegation"
4242 WHERE "truster_id" = "output_row"."member_id"
4243 AND (
4244 "unit_id" = "unit_id_v" OR
4245 "area_id" = "area_id_v"
4246 )
4247 ORDER BY "scope" DESC;
4248 END IF;
4249 ELSE
4250 SELECT * INTO "delegation_row" FROM "delegation"
4251 WHERE "truster_id" = "output_row"."member_id"
4252 AND (
4253 "unit_id" = "unit_id_v" OR
4254 "area_id" = "area_id_v" OR
4255 "issue_id" = "issue_id_p"
4256 )
4257 ORDER BY "scope" DESC;
4258 END IF;
4259 END IF;
4260 ELSE
4261 "output_row"."participation" := FALSE;
4262 END IF;
4263 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
4264 "output_row"."scope_out" := "scope_v";
4265 "output_rows" := "output_rows" || "output_row";
4266 "output_row"."member_id" := "simulate_trustee_id_p";
4267 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
4268 "output_row"."scope_out" := "delegation_row"."scope";
4269 "output_rows" := "output_rows" || "output_row";
4270 "output_row"."member_id" := "delegation_row"."trustee_id";
4271 ELSIF "delegation_row"."scope" NOTNULL THEN
4272 "output_row"."scope_out" := "delegation_row"."scope";
4273 "output_row"."disabled_out" := TRUE;
4274 "output_rows" := "output_rows" || "output_row";
4275 EXIT;
4276 ELSE
4277 "output_row"."scope_out" := NULL;
4278 "output_rows" := "output_rows" || "output_row";
4279 EXIT;
4280 END IF;
4281 EXIT WHEN "loop_member_id_v" NOTNULL;
4282 "output_row"."index" := "output_row"."index" + 1;
4283 END LOOP;
4284 "row_count" := array_upper("output_rows", 1);
4285 "i" := 1;
4286 "loop_v" := FALSE;
4287 LOOP
4288 "output_row" := "output_rows"["i"];
4289 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
4290 IF "loop_v" THEN
4291 IF "i" + 1 = "row_count" THEN
4292 "output_row"."loop" := 'last';
4293 ELSIF "i" = "row_count" THEN
4294 "output_row"."loop" := 'repetition';
4295 ELSE
4296 "output_row"."loop" := 'intermediate';
4297 END IF;
4298 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
4299 "output_row"."loop" := 'first';
4300 "loop_v" := TRUE;
4301 END IF;
4302 IF "scope_v" = 'unit' THEN
4303 "output_row"."participation" := NULL;
4304 END IF;
4305 RETURN NEXT "output_row";
4306 "i" := "i" + 1;
4307 END LOOP;
4308 RETURN;
4309 END;
4310 $$;
4312 COMMENT ON FUNCTION "delegation_chain"
4313 ( "member"."id"%TYPE,
4314 "unit"."id"%TYPE,
4315 "area"."id"%TYPE,
4316 "issue"."id"%TYPE,
4317 "member"."id"%TYPE,
4318 BOOLEAN )
4319 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
4323 ---------------------------------------------------------
4324 -- Single row returning function for delegation chains --
4325 ---------------------------------------------------------
4328 CREATE TYPE "delegation_info_loop_type" AS ENUM
4329 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
4331 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''';
4334 CREATE TYPE "delegation_info_type" AS (
4335 "own_participation" BOOLEAN,
4336 "own_delegation_scope" "delegation_scope",
4337 "first_trustee_id" INT4,
4338 "first_trustee_participation" BOOLEAN,
4339 "first_trustee_ellipsis" BOOLEAN,
4340 "other_trustee_id" INT4,
4341 "other_trustee_participation" BOOLEAN,
4342 "other_trustee_ellipsis" BOOLEAN,
4343 "delegation_loop" "delegation_info_loop_type",
4344 "participating_member_id" INT4 );
4346 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';
4348 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
4349 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
4350 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
4351 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
4352 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
4353 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
4354 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)';
4355 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
4356 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';
4357 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';
4360 CREATE FUNCTION "delegation_info"
4361 ( "member_id_p" "member"."id"%TYPE,
4362 "unit_id_p" "unit"."id"%TYPE,
4363 "area_id_p" "area"."id"%TYPE,
4364 "issue_id_p" "issue"."id"%TYPE,
4365 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
4366 "simulate_default_p" BOOLEAN DEFAULT FALSE )
4367 RETURNS "delegation_info_type"
4368 LANGUAGE 'plpgsql' STABLE AS $$
4369 DECLARE
4370 "current_row" "delegation_chain_row";
4371 "result" "delegation_info_type";
4372 BEGIN
4373 "result"."own_participation" := FALSE;
4374 FOR "current_row" IN
4375 SELECT * FROM "delegation_chain"(
4376 "member_id_p",
4377 "unit_id_p", "area_id_p", "issue_id_p",
4378 "simulate_trustee_id_p", "simulate_default_p")
4379 LOOP
4380 IF
4381 "result"."participating_member_id" ISNULL AND
4382 "current_row"."participation"
4383 THEN
4384 "result"."participating_member_id" := "current_row"."member_id";
4385 END IF;
4386 IF "current_row"."member_id" = "member_id_p" THEN
4387 "result"."own_participation" := "current_row"."participation";
4388 "result"."own_delegation_scope" := "current_row"."scope_out";
4389 IF "current_row"."loop" = 'first' THEN
4390 "result"."delegation_loop" := 'own';
4391 END IF;
4392 ELSIF
4393 "current_row"."member_valid" AND
4394 ( "current_row"."loop" ISNULL OR
4395 "current_row"."loop" != 'repetition' )
4396 THEN
4397 IF "result"."first_trustee_id" ISNULL THEN
4398 "result"."first_trustee_id" := "current_row"."member_id";
4399 "result"."first_trustee_participation" := "current_row"."participation";
4400 "result"."first_trustee_ellipsis" := FALSE;
4401 IF "current_row"."loop" = 'first' THEN
4402 "result"."delegation_loop" := 'first';
4403 END IF;
4404 ELSIF "result"."other_trustee_id" ISNULL THEN
4405 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
4406 "result"."other_trustee_id" := "current_row"."member_id";
4407 "result"."other_trustee_participation" := TRUE;
4408 "result"."other_trustee_ellipsis" := FALSE;
4409 IF "current_row"."loop" = 'first' THEN
4410 "result"."delegation_loop" := 'other';
4411 END IF;
4412 ELSE
4413 "result"."first_trustee_ellipsis" := TRUE;
4414 IF "current_row"."loop" = 'first' THEN
4415 "result"."delegation_loop" := 'first_ellipsis';
4416 END IF;
4417 END IF;
4418 ELSE
4419 "result"."other_trustee_ellipsis" := TRUE;
4420 IF "current_row"."loop" = 'first' THEN
4421 "result"."delegation_loop" := 'other_ellipsis';
4422 END IF;
4423 END IF;
4424 END IF;
4425 END LOOP;
4426 RETURN "result";
4427 END;
4428 $$;
4430 COMMENT ON FUNCTION "delegation_info"
4431 ( "member"."id"%TYPE,
4432 "unit"."id"%TYPE,
4433 "area"."id"%TYPE,
4434 "issue"."id"%TYPE,
4435 "member"."id"%TYPE,
4436 BOOLEAN )
4437 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
4441 ---------------------------
4442 -- Transaction isolation --
4443 ---------------------------
4446 CREATE FUNCTION "require_transaction_isolation"()
4447 RETURNS VOID
4448 LANGUAGE 'plpgsql' VOLATILE AS $$
4449 BEGIN
4450 IF
4451 current_setting('transaction_isolation') NOT IN
4452 ('repeatable read', 'serializable')
4453 THEN
4454 RAISE EXCEPTION 'Insufficient transaction isolation level' USING
4455 HINT = 'Consider using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ.';
4456 END IF;
4457 RETURN;
4458 END;
4459 $$;
4461 COMMENT ON FUNCTION "require_transaction_isolation"() IS 'Throws an exception, if transaction isolation level is too low to provide a consistent snapshot';
4464 CREATE FUNCTION "dont_require_transaction_isolation"()
4465 RETURNS VOID
4466 LANGUAGE 'plpgsql' VOLATILE AS $$
4467 BEGIN
4468 IF
4469 current_setting('transaction_isolation') IN
4470 ('repeatable read', 'serializable')
4471 THEN
4472 RAISE WARNING 'Unneccessary transaction isolation level: %',
4473 current_setting('transaction_isolation');
4474 END IF;
4475 RETURN;
4476 END;
4477 $$;
4479 COMMENT ON FUNCTION "dont_require_transaction_isolation"() IS 'Raises a warning, if transaction isolation level is higher than READ COMMITTED';
4483 -------------------------
4484 -- Notification system --
4485 -------------------------
4487 CREATE FUNCTION "get_initiatives_for_notification"
4488 ( "recipient_id_p" "member"."id"%TYPE )
4489 RETURNS SETOF "initiative_for_notification"
4490 LANGUAGE 'plpgsql' VOLATILE AS $$
4491 DECLARE
4492 "result_row" "initiative_for_notification"%ROWTYPE;
4493 "last_draft_id_v" "draft"."id"%TYPE;
4494 "last_suggestion_id_v" "suggestion"."id"%TYPE;
4495 BEGIN
4496 PERFORM "require_transaction_isolation"();
4497 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
4498 FOR "result_row" IN
4499 SELECT * FROM "initiative_for_notification"
4500 WHERE "recipient_id" = "recipient_id_p"
4501 LOOP
4502 SELECT "id" INTO "last_draft_id_v" FROM "draft"
4503 WHERE "draft"."initiative_id" = "result_row"."initiative_id"
4504 ORDER BY "id" DESC LIMIT 1;
4505 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
4506 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
4507 ORDER BY "id" DESC LIMIT 1;
4508 INSERT INTO "notification_initiative_sent"
4509 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
4510 VALUES (
4511 "recipient_id_p",
4512 "result_row"."initiative_id",
4513 "last_draft_id_v",
4514 "last_suggestion_id_v" )
4515 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
4516 "last_draft_id" = "last_draft_id_v",
4517 "last_suggestion_id" = "last_suggestion_id_v";
4518 RETURN NEXT "result_row";
4519 END LOOP;
4520 DELETE FROM "notification_initiative_sent"
4521 USING "initiative", "issue"
4522 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
4523 AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
4524 AND "issue"."id" = "initiative"."issue_id"
4525 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
4526 UPDATE "member" SET
4527 "notification_counter" = "notification_counter" + 1,
4528 "notification_sent" = now()
4529 WHERE "id" = "recipient_id_p";
4530 RETURN;
4531 END;
4532 $$;
4534 COMMENT ON FUNCTION "get_initiatives_for_notification"
4535 ( "member"."id"%TYPE )
4536 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';
4540 ------------------------------------------------------------------------
4541 -- Regular tasks, except calculcation of snapshots and voting results --
4542 ------------------------------------------------------------------------
4545 CREATE FUNCTION "check_activity"()
4546 RETURNS VOID
4547 LANGUAGE 'plpgsql' VOLATILE AS $$
4548 DECLARE
4549 "system_setting_row" "system_setting"%ROWTYPE;
4550 BEGIN
4551 PERFORM "dont_require_transaction_isolation"();
4552 SELECT * INTO "system_setting_row" FROM "system_setting";
4553 IF "system_setting_row"."member_ttl" NOTNULL THEN
4554 UPDATE "member" SET "active" = FALSE
4555 WHERE "active" = TRUE
4556 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
4557 END IF;
4558 RETURN;
4559 END;
4560 $$;
4562 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
4565 CREATE FUNCTION "calculate_member_counts"()
4566 RETURNS VOID
4567 LANGUAGE 'plpgsql' VOLATILE AS $$
4568 BEGIN
4569 PERFORM "require_transaction_isolation"();
4570 DELETE FROM "member_count";
4571 INSERT INTO "member_count" ("total_count")
4572 SELECT "total_count" FROM "member_count_view";
4573 UPDATE "unit" SET "member_count" = "view"."member_count"
4574 FROM "unit_member_count" AS "view"
4575 WHERE "view"."unit_id" = "unit"."id";
4576 RETURN;
4577 END;
4578 $$;
4580 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"';
4583 CREATE FUNCTION "calculate_area_quorum"()
4584 RETURNS VOID
4585 LANGUAGE 'plpgsql' VOLATILE AS $$
4586 BEGIN
4587 PERFORM "dont_require_transaction_isolation"();
4588 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
4589 FROM "area_quorum" AS "view"
4590 WHERE "view"."area_id" = "area"."id";
4591 RETURN;
4592 END;
4593 $$;
4595 COMMENT ON FUNCTION "calculate_area_quorum"() IS 'Calculate column "issue_quorum" in table "area" from view "area_quorum"';
4599 ------------------------------------
4600 -- Calculation of harmonic weight --
4601 ------------------------------------
4604 CREATE VIEW "remaining_harmonic_supporter_weight" AS
4605 SELECT
4606 "direct_interest_snapshot"."snapshot_id",
4607 "direct_interest_snapshot"."issue_id",
4608 "direct_interest_snapshot"."member_id",
4609 "direct_interest_snapshot"."weight" AS "weight_num",
4610 count("initiative"."id") AS "weight_den"
4611 FROM "issue"
4612 JOIN "direct_interest_snapshot"
4613 ON "issue"."latest_snapshot_id" = "direct_interest_snapshot"."snapshot_id"
4614 AND "issue"."id" = "direct_interest_snapshot"."issue_id"
4615 JOIN "initiative"
4616 ON "issue"."id" = "initiative"."issue_id"
4617 AND "initiative"."harmonic_weight" ISNULL
4618 JOIN "direct_supporter_snapshot"
4619 ON "issue"."latest_snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
4620 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
4621 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
4622 AND (
4623 "direct_supporter_snapshot"."satisfied" = TRUE OR
4624 coalesce("initiative"."admitted", FALSE) = FALSE
4625 )
4626 GROUP BY
4627 "direct_interest_snapshot"."snapshot_id",
4628 "direct_interest_snapshot"."issue_id",
4629 "direct_interest_snapshot"."member_id",
4630 "direct_interest_snapshot"."weight";
4632 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
4635 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
4636 SELECT
4637 "initiative"."issue_id",
4638 "initiative"."id" AS "initiative_id",
4639 "initiative"."admitted",
4640 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
4641 "remaining_harmonic_supporter_weight"."weight_den"
4642 FROM "remaining_harmonic_supporter_weight"
4643 JOIN "initiative"
4644 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
4645 AND "initiative"."harmonic_weight" ISNULL
4646 JOIN "direct_supporter_snapshot"
4647 ON "remaining_harmonic_supporter_weight"."snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
4648 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
4649 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
4650 AND (
4651 "direct_supporter_snapshot"."satisfied" = TRUE OR
4652 coalesce("initiative"."admitted", FALSE) = FALSE
4653 )
4654 GROUP BY
4655 "initiative"."issue_id",
4656 "initiative"."id",
4657 "initiative"."admitted",
4658 "remaining_harmonic_supporter_weight"."weight_den";
4660 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
4663 CREATE VIEW "remaining_harmonic_initiative_weight_dummies" AS
4664 SELECT
4665 "issue_id",
4666 "id" AS "initiative_id",
4667 "admitted",
4668 0 AS "weight_num",
4669 1 AS "weight_den"
4670 FROM "initiative"
4671 WHERE "harmonic_weight" ISNULL;
4673 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';
4676 CREATE FUNCTION "set_harmonic_initiative_weights"
4677 ( "issue_id_p" "issue"."id"%TYPE )
4678 RETURNS VOID
4679 LANGUAGE 'plpgsql' VOLATILE AS $$
4680 DECLARE
4681 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
4682 "i" INT4;
4683 "count_v" INT4;
4684 "summand_v" FLOAT;
4685 "id_ary" INT4[];
4686 "weight_ary" FLOAT[];
4687 "min_weight_v" FLOAT;
4688 BEGIN
4689 PERFORM "require_transaction_isolation"();
4690 UPDATE "initiative" SET "harmonic_weight" = NULL
4691 WHERE "issue_id" = "issue_id_p";
4692 LOOP
4693 "min_weight_v" := NULL;
4694 "i" := 0;
4695 "count_v" := 0;
4696 FOR "weight_row" IN
4697 SELECT * FROM "remaining_harmonic_initiative_weight_summands"
4698 WHERE "issue_id" = "issue_id_p"
4699 AND (
4700 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
4701 SELECT NULL FROM "initiative"
4702 WHERE "issue_id" = "issue_id_p"
4703 AND "harmonic_weight" ISNULL
4704 AND coalesce("admitted", FALSE) = FALSE
4705 )
4706 )
4707 UNION ALL -- needed for corner cases
4708 SELECT * FROM "remaining_harmonic_initiative_weight_dummies"
4709 WHERE "issue_id" = "issue_id_p"
4710 AND (
4711 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
4712 SELECT NULL FROM "initiative"
4713 WHERE "issue_id" = "issue_id_p"
4714 AND "harmonic_weight" ISNULL
4715 AND coalesce("admitted", FALSE) = FALSE
4716 )
4717 )
4718 ORDER BY "initiative_id" DESC, "weight_den" DESC
4719 -- NOTE: non-admitted initiatives placed first (at last positions),
4720 -- latest initiatives treated worse in case of tie
4721 LOOP
4722 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
4723 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
4724 "i" := "i" + 1;
4725 "count_v" := "i";
4726 "id_ary"["i"] := "weight_row"."initiative_id";
4727 "weight_ary"["i"] := "summand_v";
4728 ELSE
4729 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
4730 END IF;
4731 END LOOP;
4732 EXIT WHEN "count_v" = 0;
4733 "i" := 1;
4734 LOOP
4735 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
4736 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
4737 "min_weight_v" := "weight_ary"["i"];
4738 END IF;
4739 "i" := "i" + 1;
4740 EXIT WHEN "i" > "count_v";
4741 END LOOP;
4742 "i" := 1;
4743 LOOP
4744 IF "weight_ary"["i"] = "min_weight_v" THEN
4745 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
4746 WHERE "id" = "id_ary"["i"];
4747 EXIT;
4748 END IF;
4749 "i" := "i" + 1;
4750 END LOOP;
4751 END LOOP;
4752 UPDATE "initiative" SET "harmonic_weight" = 0
4753 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
4754 END;
4755 $$;
4757 COMMENT ON FUNCTION "set_harmonic_initiative_weights"
4758 ( "issue"."id"%TYPE )
4759 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
4763 ------------------------------
4764 -- Calculation of snapshots --
4765 ------------------------------
4768 CREATE FUNCTION "weight_of_added_delegations_for_snapshot"
4769 ( "snapshot_id_p" "snapshot"."id"%TYPE,
4770 "issue_id_p" "issue"."id"%TYPE,
4771 "member_id_p" "member"."id"%TYPE,
4772 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
4773 RETURNS "direct_interest_snapshot"."weight"%TYPE
4774 LANGUAGE 'plpgsql' VOLATILE AS $$
4775 DECLARE
4776 "issue_delegation_row" "issue_delegation"%ROWTYPE;
4777 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
4778 "weight_v" INT4;
4779 "sub_weight_v" INT4;
4780 BEGIN
4781 PERFORM "require_transaction_isolation"();
4782 "weight_v" := 0;
4783 FOR "issue_delegation_row" IN
4784 SELECT * FROM "issue_delegation"
4785 WHERE "trustee_id" = "member_id_p"
4786 AND "issue_id" = "issue_id_p"
4787 LOOP
4788 IF NOT EXISTS (
4789 SELECT NULL FROM "direct_interest_snapshot"
4790 WHERE "snapshot_id" = "snapshot_id_p"
4791 AND "issue_id" = "issue_id_p"
4792 AND "member_id" = "issue_delegation_row"."truster_id"
4793 ) AND NOT EXISTS (
4794 SELECT NULL FROM "delegating_interest_snapshot"
4795 WHERE "snapshot_id" = "snapshot_id_p"
4796 AND "issue_id" = "issue_id_p"
4797 AND "member_id" = "issue_delegation_row"."truster_id"
4798 ) THEN
4799 "delegate_member_ids_v" :=
4800 "member_id_p" || "delegate_member_ids_p";
4801 INSERT INTO "delegating_interest_snapshot" (
4802 "snapshot_id",
4803 "issue_id",
4804 "member_id",
4805 "scope",
4806 "delegate_member_ids"
4807 ) VALUES (
4808 "snapshot_id_p",
4809 "issue_id_p",
4810 "issue_delegation_row"."truster_id",
4811 "issue_delegation_row"."scope",
4812 "delegate_member_ids_v"
4813 );
4814 "sub_weight_v" := 1 +
4815 "weight_of_added_delegations_for_snapshot"(
4816 "snapshot_id_p",
4817 "issue_id_p",
4818 "issue_delegation_row"."truster_id",
4819 "delegate_member_ids_v"
4820 );
4821 UPDATE "delegating_interest_snapshot"
4822 SET "weight" = "sub_weight_v"
4823 WHERE "snapshot_id" = "snapshot_id_p"
4824 AND "issue_id" = "issue_id_p"
4825 AND "member_id" = "issue_delegation_row"."truster_id";
4826 "weight_v" := "weight_v" + "sub_weight_v";
4827 END IF;
4828 END LOOP;
4829 RETURN "weight_v";
4830 END;
4831 $$;
4833 COMMENT ON FUNCTION "weight_of_added_delegations_for_snapshot"
4834 ( "snapshot"."id"%TYPE,
4835 "issue"."id"%TYPE,
4836 "member"."id"%TYPE,
4837 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
4838 IS 'Helper function for "fill_snapshot" function';
4841 CREATE FUNCTION "take_snapshot"
4842 ( "issue_id_p" "issue"."id"%TYPE,
4843 "area_id_p" "area"."id"%TYPE = NULL )
4844 RETURNS "snapshot"."id"%TYPE
4845 LANGUAGE 'plpgsql' VOLATILE AS $$
4846 DECLARE
4847 "area_id_v" "area"."id"%TYPE;
4848 "unit_id_v" "unit"."id"%TYPE;
4849 "snapshot_id_v" "snapshot"."id"%TYPE;
4850 "issue_id_v" "issue"."id"%TYPE;
4851 "member_id_v" "member"."id"%TYPE;
4852 BEGIN
4853 IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN
4854 RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL';
4855 END IF;
4856 PERFORM "require_transaction_isolation"();
4857 IF "issue_id_p" ISNULL THEN
4858 "area_id_v" := "area_id_p";
4859 ELSE
4860 SELECT "area_id" INTO "area_id_v"
4861 FROM "issue" WHERE "id" = "issue_id_p";
4862 END IF;
4863 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_p";
4864 INSERT INTO "snapshot" ("area_id", "issue_id")
4865 VALUES ("area_id_v", "issue_id_p")
4866 RETURNING "id" INTO "snapshot_id_v";
4867 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
4868 SELECT "snapshot_id_v", "member_id"
4869 FROM "unit_member" WHERE "unit_id" = "unit_id_v";
4870 UPDATE "snapshot" SET
4871 "population" = (
4872 SELECT count(1) FROM "snapshot_population"
4873 WHERE "snapshot_id" = "snapshot_id_v"
4874 ) WHERE "id" = "snapshot_id_v";
4875 FOR "issue_id_v" IN
4876 SELECT "id" FROM "issue"
4877 WHERE CASE WHEN "issue_id_p" ISNULL THEN
4878 "area_id" = "area_id_p" AND
4879 "state" = 'admission'
4880 ELSE
4881 "id" = "issue_id_p"
4882 END
4883 LOOP
4884 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
4885 VALUES ("snapshot_id_v", "issue_id_v");
4886 INSERT INTO "direct_interest_snapshot"
4887 ("snapshot_id", "issue_id", "member_id")
4888 SELECT
4889 "snapshot_id_v" AS "snapshot_id",
4890 "issue_id_v" AS "issue_id",
4891 "member"."id" AS "member_id"
4892 FROM "issue"
4893 JOIN "area" ON "issue"."area_id" = "area"."id"
4894 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
4895 JOIN "member" ON "interest"."member_id" = "member"."id"
4896 JOIN "privilege"
4897 ON "privilege"."unit_id" = "area"."unit_id"
4898 AND "privilege"."member_id" = "member"."id"
4899 WHERE "issue"."id" = "issue_id_v"
4900 AND "member"."active" AND "privilege"."voting_right";
4901 FOR "member_id_v" IN
4902 SELECT "member_id" FROM "direct_interest_snapshot"
4903 WHERE "snapshot_id" = "snapshot_id_v"
4904 AND "issue_id" = "issue_id_v"
4905 LOOP
4906 UPDATE "direct_interest_snapshot" SET
4907 "weight" = 1 +
4908 "weight_of_added_delegations_for_snapshot"(
4909 "snapshot_id_v",
4910 "issue_id_v",
4911 "member_id_v",
4912 '{}'
4913 )
4914 WHERE "snapshot_id" = "snapshot_id_v"
4915 AND "issue_id" = "issue_id_v"
4916 AND "member_id" = "member_id_v";
4917 END LOOP;
4918 INSERT INTO "direct_supporter_snapshot"
4919 ( "snapshot_id", "issue_id", "initiative_id", "member_id",
4920 "draft_id", "informed", "satisfied" )
4921 SELECT
4922 "snapshot_id_v" AS "snapshot_id",
4923 "issue_id_v" AS "issue_id",
4924 "initiative"."id" AS "initiative_id",
4925 "supporter"."member_id" AS "member_id",
4926 "supporter"."draft_id" AS "draft_id",
4927 "supporter"."draft_id" = "current_draft"."id" AS "informed",
4928 NOT EXISTS (
4929 SELECT NULL FROM "critical_opinion"
4930 WHERE "initiative_id" = "initiative"."id"
4931 AND "member_id" = "supporter"."member_id"
4932 ) AS "satisfied"
4933 FROM "initiative"
4934 JOIN "supporter"
4935 ON "supporter"."initiative_id" = "initiative"."id"
4936 JOIN "current_draft"
4937 ON "initiative"."id" = "current_draft"."initiative_id"
4938 JOIN "direct_interest_snapshot"
4939 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"
4940 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"
4941 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
4942 WHERE "initiative"."issue_id" = "issue_id_v";
4943 DELETE FROM "temporary_suggestion_counts";
4944 INSERT INTO "temporary_suggestion_counts"
4945 ( "id",
4946 "minus2_unfulfilled_count", "minus2_fulfilled_count",
4947 "minus1_unfulfilled_count", "minus1_fulfilled_count",
4948 "plus1_unfulfilled_count", "plus1_fulfilled_count",
4949 "plus2_unfulfilled_count", "plus2_fulfilled_count" )
4950 SELECT
4951 "suggestion"."id",
4952 ( SELECT coalesce(sum("di"."weight"), 0)
4953 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
4954 ON "di"."snapshot_id" = "snapshot_id_v"
4955 AND "di"."issue_id" = "issue_id_v"
4956 AND "di"."member_id" = "opinion"."member_id"
4957 WHERE "opinion"."suggestion_id" = "suggestion"."id"
4958 AND "opinion"."degree" = -2
4959 AND "opinion"."fulfilled" = FALSE
4960 ) AS "minus2_unfulfilled_count",
4961 ( SELECT coalesce(sum("di"."weight"), 0)
4962 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
4963 ON "di"."snapshot_id" = "snapshot_id_v"
4964 AND "di"."issue_id" = "issue_id_v"
4965 AND "di"."member_id" = "opinion"."member_id"
4966 WHERE "opinion"."suggestion_id" = "suggestion"."id"
4967 AND "opinion"."degree" = -2
4968 AND "opinion"."fulfilled" = TRUE
4969 ) AS "minus2_fulfilled_count",
4970 ( SELECT coalesce(sum("di"."weight"), 0)
4971 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
4972 ON "di"."snapshot_id" = "snapshot_id_v"
4973 AND "di"."issue_id" = "issue_id_v"
4974 AND "di"."member_id" = "opinion"."member_id"
4975 WHERE "opinion"."suggestion_id" = "suggestion"."id"
4976 AND "opinion"."degree" = -1
4977 AND "opinion"."fulfilled" = FALSE
4978 ) AS "minus1_unfulfilled_count",
4979 ( SELECT coalesce(sum("di"."weight"), 0)
4980 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
4981 ON "di"."snapshot_id" = "snapshot_id_v"
4982 AND "di"."issue_id" = "issue_id_v"
4983 AND "di"."member_id" = "opinion"."member_id"
4984 WHERE "opinion"."suggestion_id" = "suggestion"."id"
4985 AND "opinion"."degree" = -1
4986 AND "opinion"."fulfilled" = TRUE
4987 ) AS "minus1_fulfilled_count",
4988 ( SELECT coalesce(sum("di"."weight"), 0)
4989 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
4990 ON "di"."snapshot_id" = "snapshot_id_v"
4991 AND "di"."issue_id" = "issue_id_v"
4992 AND "di"."member_id" = "opinion"."member_id"
4993 WHERE "opinion"."suggestion_id" = "suggestion"."id"
4994 AND "opinion"."degree" = 1
4995 AND "opinion"."fulfilled" = FALSE
4996 ) AS "plus1_unfulfilled_count",
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" = 1
5004 AND "opinion"."fulfilled" = TRUE
5005 ) AS "plus1_fulfilled_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" = FALSE
5014 ) AS "plus2_unfulfilled_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" = 2
5022 AND "opinion"."fulfilled" = TRUE
5023 ) AS "plus2_fulfilled_count"
5024 FROM "suggestion" JOIN "initiative"
5025 ON "suggestion"."initiative_id" = "initiative"."id"
5026 WHERE "initiative"."issue_id" = "issue_id_v";
5027 END LOOP;
5028 RETURN "snapshot_id_v";
5029 END;
5030 $$;
5032 COMMENT ON FUNCTION "take_snapshot"
5033 ( "issue"."id"%TYPE,
5034 "area"."id"%TYPE )
5035 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.';
5038 CREATE FUNCTION "finish_snapshot"
5039 ( "issue_id_p" "issue"."id"%TYPE )
5040 RETURNS VOID
5041 LANGUAGE 'plpgsql' VOLATILE AS $$
5042 DECLARE
5043 "snapshot_id_v" "snapshot"."id"%TYPE;
5044 BEGIN
5045 -- NOTE: function does not require snapshot isolation but we don't call
5046 -- "dont_require_snapshot_isolation" here because this function is
5047 -- also invoked by "check_issue"
5048 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
5049 SELECT "id" INTO "snapshot_id_v" FROM "snapshot"
5050 ORDER BY "id" DESC LIMIT 1;
5051 UPDATE "issue" SET
5052 "calculated" = "snapshot"."calculated",
5053 "latest_snapshot_id" = "snapshot_id_v",
5054 "population" = "snapshot"."population"
5055 FROM "snapshot"
5056 WHERE "issue"."id" = "issue_id_p"
5057 AND "snapshot"."id" = "snapshot_id_v";
5058 UPDATE "initiative" SET
5059 "supporter_count" = (
5060 SELECT coalesce(sum("di"."weight"), 0)
5061 FROM "direct_interest_snapshot" AS "di"
5062 JOIN "direct_supporter_snapshot" AS "ds"
5063 ON "di"."member_id" = "ds"."member_id"
5064 WHERE "di"."snapshot_id" = "snapshot_id_v"
5065 AND "di"."issue_id" = "issue_id_p"
5066 AND "ds"."snapshot_id" = "snapshot_id_v"
5067 AND "ds"."initiative_id" = "initiative"."id"
5068 ),
5069 "informed_supporter_count" = (
5070 SELECT coalesce(sum("di"."weight"), 0)
5071 FROM "direct_interest_snapshot" AS "di"
5072 JOIN "direct_supporter_snapshot" AS "ds"
5073 ON "di"."member_id" = "ds"."member_id"
5074 WHERE "di"."snapshot_id" = "snapshot_id_v"
5075 AND "di"."issue_id" = "issue_id_p"
5076 AND "ds"."snapshot_id" = "snapshot_id_v"
5077 AND "ds"."initiative_id" = "initiative"."id"
5078 AND "ds"."informed"
5079 ),
5080 "satisfied_supporter_count" = (
5081 SELECT coalesce(sum("di"."weight"), 0)
5082 FROM "direct_interest_snapshot" AS "di"
5083 JOIN "direct_supporter_snapshot" AS "ds"
5084 ON "di"."member_id" = "ds"."member_id"
5085 WHERE "di"."snapshot_id" = "snapshot_id_v"
5086 AND "di"."issue_id" = "issue_id_p"
5087 AND "ds"."snapshot_id" = "snapshot_id_v"
5088 AND "ds"."initiative_id" = "initiative"."id"
5089 AND "ds"."satisfied"
5090 ),
5091 "satisfied_informed_supporter_count" = (
5092 SELECT coalesce(sum("di"."weight"), 0)
5093 FROM "direct_interest_snapshot" AS "di"
5094 JOIN "direct_supporter_snapshot" AS "ds"
5095 ON "di"."member_id" = "ds"."member_id"
5096 WHERE "di"."snapshot_id" = "snapshot_id_v"
5097 AND "di"."issue_id" = "issue_id_p"
5098 AND "ds"."snapshot_id" = "snapshot_id_v"
5099 AND "ds"."initiative_id" = "initiative"."id"
5100 AND "ds"."informed"
5101 AND "ds"."satisfied"
5102 )
5103 WHERE "issue_id" = "issue_id_p";
5104 UPDATE "suggestion" SET
5105 "minus2_unfulfilled_count" = "temp"."minus2_unfulfilled_count",
5106 "minus2_fulfilled_count" = "temp"."minus2_fulfilled_count",
5107 "minus1_unfulfilled_count" = "temp"."minus1_unfulfilled_count",
5108 "minus1_fulfilled_count" = "temp"."minus1_fulfilled_count",
5109 "plus1_unfulfilled_count" = "temp"."plus1_unfulfilled_count",
5110 "plus1_fulfilled_count" = "temp"."plus1_fulfilled_count",
5111 "plus2_unfulfilled_count" = "temp"."plus2_unfulfilled_count",
5112 "plus2_fulfilled_count" = "temp"."plus2_fulfilled_count"
5113 FROM "temporary_suggestion_counts" AS "temp", "initiative"
5114 WHERE "temp"."id" = "suggestion"."id"
5115 AND "initiative"."issue_id" = "issue_id_p"
5116 AND "suggestion"."initiative_id" = "initiative"."id";
5117 DELETE FROM "temporary_suggestion_counts";
5118 RETURN;
5119 END;
5120 $$;
5122 COMMENT ON FUNCTION "finish_snapshot"
5123 ( "issue"."id"%TYPE )
5124 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)';
5128 -----------------------
5129 -- Counting of votes --
5130 -----------------------
5133 CREATE FUNCTION "weight_of_added_vote_delegations"
5134 ( "issue_id_p" "issue"."id"%TYPE,
5135 "member_id_p" "member"."id"%TYPE,
5136 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
5137 RETURNS "direct_voter"."weight"%TYPE
5138 LANGUAGE 'plpgsql' VOLATILE AS $$
5139 DECLARE
5140 "issue_delegation_row" "issue_delegation"%ROWTYPE;
5141 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
5142 "weight_v" INT4;
5143 "sub_weight_v" INT4;
5144 BEGIN
5145 PERFORM "require_transaction_isolation"();
5146 "weight_v" := 0;
5147 FOR "issue_delegation_row" IN
5148 SELECT * FROM "issue_delegation"
5149 WHERE "trustee_id" = "member_id_p"
5150 AND "issue_id" = "issue_id_p"
5151 LOOP
5152 IF NOT EXISTS (
5153 SELECT NULL FROM "direct_voter"
5154 WHERE "member_id" = "issue_delegation_row"."truster_id"
5155 AND "issue_id" = "issue_id_p"
5156 ) AND NOT EXISTS (
5157 SELECT NULL FROM "delegating_voter"
5158 WHERE "member_id" = "issue_delegation_row"."truster_id"
5159 AND "issue_id" = "issue_id_p"
5160 ) THEN
5161 "delegate_member_ids_v" :=
5162 "member_id_p" || "delegate_member_ids_p";
5163 INSERT INTO "delegating_voter" (
5164 "issue_id",
5165 "member_id",
5166 "scope",
5167 "delegate_member_ids"
5168 ) VALUES (
5169 "issue_id_p",
5170 "issue_delegation_row"."truster_id",
5171 "issue_delegation_row"."scope",
5172 "delegate_member_ids_v"
5173 );
5174 "sub_weight_v" := 1 +
5175 "weight_of_added_vote_delegations"(
5176 "issue_id_p",
5177 "issue_delegation_row"."truster_id",
5178 "delegate_member_ids_v"
5179 );
5180 UPDATE "delegating_voter"
5181 SET "weight" = "sub_weight_v"
5182 WHERE "issue_id" = "issue_id_p"
5183 AND "member_id" = "issue_delegation_row"."truster_id";
5184 "weight_v" := "weight_v" + "sub_weight_v";
5185 END IF;
5186 END LOOP;
5187 RETURN "weight_v";
5188 END;
5189 $$;
5191 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
5192 ( "issue"."id"%TYPE,
5193 "member"."id"%TYPE,
5194 "delegating_voter"."delegate_member_ids"%TYPE )
5195 IS 'Helper function for "add_vote_delegations" function';
5198 CREATE FUNCTION "add_vote_delegations"
5199 ( "issue_id_p" "issue"."id"%TYPE )
5200 RETURNS VOID
5201 LANGUAGE 'plpgsql' VOLATILE AS $$
5202 DECLARE
5203 "member_id_v" "member"."id"%TYPE;
5204 BEGIN
5205 PERFORM "require_transaction_isolation"();
5206 FOR "member_id_v" IN
5207 SELECT "member_id" FROM "direct_voter"
5208 WHERE "issue_id" = "issue_id_p"
5209 LOOP
5210 UPDATE "direct_voter" SET
5211 "weight" = "weight" + "weight_of_added_vote_delegations"(
5212 "issue_id_p",
5213 "member_id_v",
5214 '{}'
5215 )
5216 WHERE "member_id" = "member_id_v"
5217 AND "issue_id" = "issue_id_p";
5218 END LOOP;
5219 RETURN;
5220 END;
5221 $$;
5223 COMMENT ON FUNCTION "add_vote_delegations"
5224 ( "issue_id_p" "issue"."id"%TYPE )
5225 IS 'Helper function for "close_voting" function';
5228 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
5229 RETURNS VOID
5230 LANGUAGE 'plpgsql' VOLATILE AS $$
5231 DECLARE
5232 "area_id_v" "area"."id"%TYPE;
5233 "unit_id_v" "unit"."id"%TYPE;
5234 "member_id_v" "member"."id"%TYPE;
5235 BEGIN
5236 PERFORM "require_transaction_isolation"();
5237 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
5238 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
5239 -- override protection triggers:
5240 INSERT INTO "temporary_transaction_data" ("key", "value")
5241 VALUES ('override_protection_triggers', TRUE::TEXT);
5242 -- delete timestamp of voting comment:
5243 UPDATE "direct_voter" SET "comment_changed" = NULL
5244 WHERE "issue_id" = "issue_id_p";
5245 -- delete delegating votes (in cases of manual reset of issue state):
5246 DELETE FROM "delegating_voter"
5247 WHERE "issue_id" = "issue_id_p";
5248 -- delete votes from non-privileged voters:
5249 DELETE FROM "direct_voter"
5250 USING (
5251 SELECT
5252 "direct_voter"."member_id"
5253 FROM "direct_voter"
5254 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
5255 LEFT JOIN "privilege"
5256 ON "privilege"."unit_id" = "unit_id_v"
5257 AND "privilege"."member_id" = "direct_voter"."member_id"
5258 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
5259 "member"."active" = FALSE OR
5260 "privilege"."voting_right" ISNULL OR
5261 "privilege"."voting_right" = FALSE
5262 )
5263 ) AS "subquery"
5264 WHERE "direct_voter"."issue_id" = "issue_id_p"
5265 AND "direct_voter"."member_id" = "subquery"."member_id";
5266 -- consider delegations:
5267 UPDATE "direct_voter" SET "weight" = 1
5268 WHERE "issue_id" = "issue_id_p";
5269 PERFORM "add_vote_delegations"("issue_id_p");
5270 -- mark first preferences:
5271 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
5272 FROM (
5273 SELECT
5274 "vote"."initiative_id",
5275 "vote"."member_id",
5276 CASE WHEN "vote"."grade" > 0 THEN
5277 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
5278 ELSE NULL
5279 END AS "first_preference"
5280 FROM "vote"
5281 JOIN "initiative" -- NOTE: due to missing index on issue_id
5282 ON "vote"."issue_id" = "initiative"."issue_id"
5283 JOIN "vote" AS "agg"
5284 ON "initiative"."id" = "agg"."initiative_id"
5285 AND "vote"."member_id" = "agg"."member_id"
5286 GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
5287 ) AS "subquery"
5288 WHERE "vote"."issue_id" = "issue_id_p"
5289 AND "vote"."initiative_id" = "subquery"."initiative_id"
5290 AND "vote"."member_id" = "subquery"."member_id";
5291 -- finish overriding protection triggers (avoids garbage):
5292 DELETE FROM "temporary_transaction_data"
5293 WHERE "key" = 'override_protection_triggers';
5294 -- materialize battle_view:
5295 -- NOTE: "closed" column of issue must be set at this point
5296 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
5297 INSERT INTO "battle" (
5298 "issue_id",
5299 "winning_initiative_id", "losing_initiative_id",
5300 "count"
5301 ) SELECT
5302 "issue_id",
5303 "winning_initiative_id", "losing_initiative_id",
5304 "count"
5305 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
5306 -- set voter count:
5307 UPDATE "issue" SET
5308 "voter_count" = (
5309 SELECT coalesce(sum("weight"), 0)
5310 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
5311 )
5312 WHERE "id" = "issue_id_p";
5313 -- copy "positive_votes" and "negative_votes" from "battle" table:
5314 -- NOTE: "first_preference_votes" is set to a default of 0 at this step
5315 UPDATE "initiative" SET
5316 "first_preference_votes" = 0,
5317 "positive_votes" = "battle_win"."count",
5318 "negative_votes" = "battle_lose"."count"
5319 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
5320 WHERE
5321 "battle_win"."issue_id" = "issue_id_p" AND
5322 "battle_win"."winning_initiative_id" = "initiative"."id" AND
5323 "battle_win"."losing_initiative_id" ISNULL AND
5324 "battle_lose"."issue_id" = "issue_id_p" AND
5325 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
5326 "battle_lose"."winning_initiative_id" ISNULL;
5327 -- calculate "first_preference_votes":
5328 -- NOTE: will only set values not equal to zero
5329 UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum"
5330 FROM (
5331 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
5332 FROM "vote" JOIN "direct_voter"
5333 ON "vote"."issue_id" = "direct_voter"."issue_id"
5334 AND "vote"."member_id" = "direct_voter"."member_id"
5335 WHERE "vote"."first_preference"
5336 GROUP BY "vote"."initiative_id"
5337 ) AS "subquery"
5338 WHERE "initiative"."issue_id" = "issue_id_p"
5339 AND "initiative"."admitted"
5340 AND "initiative"."id" = "subquery"."initiative_id";
5341 END;
5342 $$;
5344 COMMENT ON FUNCTION "close_voting"
5345 ( "issue"."id"%TYPE )
5346 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.';
5349 CREATE FUNCTION "defeat_strength"
5350 ( "positive_votes_p" INT4,
5351 "negative_votes_p" INT4,
5352 "defeat_strength_p" "defeat_strength" )
5353 RETURNS INT8
5354 LANGUAGE 'plpgsql' IMMUTABLE AS $$
5355 BEGIN
5356 IF "defeat_strength_p" = 'simple'::"defeat_strength" THEN
5357 IF "positive_votes_p" > "negative_votes_p" THEN
5358 RETURN "positive_votes_p";
5359 ELSE
5360 RETURN 0;
5361 END IF;
5362 ELSE
5363 IF "positive_votes_p" > "negative_votes_p" THEN
5364 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
5365 ELSIF "positive_votes_p" = "negative_votes_p" THEN
5366 RETURN 0;
5367 ELSE
5368 RETURN -1;
5369 END IF;
5370 END IF;
5371 END;
5372 $$;
5374 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")';
5377 CREATE FUNCTION "secondary_link_strength"
5378 ( "initiative1_ord_p" INT4,
5379 "initiative2_ord_p" INT4,
5380 "tie_breaking_p" "tie_breaking" )
5381 RETURNS INT8
5382 LANGUAGE 'plpgsql' IMMUTABLE AS $$
5383 BEGIN
5384 IF "initiative1_ord_p" = "initiative2_ord_p" THEN
5385 RAISE EXCEPTION 'Identical initiative ids passed to "secondary_link_strength" function (should not happen)';
5386 END IF;
5387 RETURN (
5388 CASE WHEN "tie_breaking_p" = 'simple'::"tie_breaking" THEN
5389 0
5390 ELSE
5391 CASE WHEN "initiative1_ord_p" < "initiative2_ord_p" THEN
5392 1::INT8 << 62
5393 ELSE 0 END
5394 +
5395 CASE WHEN "tie_breaking_p" = 'variant2'::"tie_breaking" THEN
5396 ("initiative2_ord_p"::INT8 << 31) - "initiative1_ord_p"::INT8
5397 ELSE
5398 "initiative2_ord_p"::INT8 - ("initiative1_ord_p"::INT8 << 31)
5399 END
5400 END
5401 );
5402 END;
5403 $$;
5405 COMMENT ON FUNCTION "secondary_link_strength"(INT4, INT4, "tie_breaking") IS 'Calculates a secondary criterion for the defeat strength (tie-breaking of the links)';
5408 CREATE TYPE "link_strength" AS (
5409 "primary" INT8,
5410 "secondary" INT8 );
5412 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'')';
5415 CREATE FUNCTION "find_best_paths"("matrix_d" "link_strength"[][])
5416 RETURNS "link_strength"[][]
5417 LANGUAGE 'plpgsql' IMMUTABLE AS $$
5418 DECLARE
5419 "dimension_v" INT4;
5420 "matrix_p" "link_strength"[][];
5421 "i" INT4;
5422 "j" INT4;
5423 "k" INT4;
5424 BEGIN
5425 "dimension_v" := array_upper("matrix_d", 1);
5426 "matrix_p" := "matrix_d";
5427 "i" := 1;
5428 LOOP
5429 "j" := 1;
5430 LOOP
5431 IF "i" != "j" THEN
5432 "k" := 1;
5433 LOOP
5434 IF "i" != "k" AND "j" != "k" THEN
5435 IF "matrix_p"["j"]["i"] < "matrix_p"["i"]["k"] THEN
5436 IF "matrix_p"["j"]["i"] > "matrix_p"["j"]["k"] THEN
5437 "matrix_p"["j"]["k"] := "matrix_p"["j"]["i"];
5438 END IF;
5439 ELSE
5440 IF "matrix_p"["i"]["k"] > "matrix_p"["j"]["k"] THEN
5441 "matrix_p"["j"]["k"] := "matrix_p"["i"]["k"];
5442 END IF;
5443 END IF;
5444 END IF;
5445 EXIT WHEN "k" = "dimension_v";
5446 "k" := "k" + 1;
5447 END LOOP;
5448 END IF;
5449 EXIT WHEN "j" = "dimension_v";
5450 "j" := "j" + 1;
5451 END LOOP;
5452 EXIT WHEN "i" = "dimension_v";
5453 "i" := "i" + 1;
5454 END LOOP;
5455 RETURN "matrix_p";
5456 END;
5457 $$;
5459 COMMENT ON FUNCTION "find_best_paths"("link_strength"[][]) IS 'Computes the strengths of the best beat-paths from a square matrix';
5462 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
5463 RETURNS VOID
5464 LANGUAGE 'plpgsql' VOLATILE AS $$
5465 DECLARE
5466 "issue_row" "issue"%ROWTYPE;
5467 "policy_row" "policy"%ROWTYPE;
5468 "dimension_v" INT4;
5469 "matrix_a" INT4[][]; -- absolute votes
5470 "matrix_d" "link_strength"[][]; -- defeat strength (direct)
5471 "matrix_p" "link_strength"[][]; -- defeat strength (best path)
5472 "matrix_t" "link_strength"[][]; -- defeat strength (tie-breaking)
5473 "matrix_f" BOOLEAN[][]; -- forbidden link (tie-breaking)
5474 "matrix_b" BOOLEAN[][]; -- final order (who beats who)
5475 "i" INT4;
5476 "j" INT4;
5477 "m" INT4;
5478 "n" INT4;
5479 "battle_row" "battle"%ROWTYPE;
5480 "rank_ary" INT4[];
5481 "rank_v" INT4;
5482 "initiative_id_v" "initiative"."id"%TYPE;
5483 BEGIN
5484 PERFORM "require_transaction_isolation"();
5485 SELECT * INTO "issue_row"
5486 FROM "issue" WHERE "id" = "issue_id_p";
5487 SELECT * INTO "policy_row"
5488 FROM "policy" WHERE "id" = "issue_row"."policy_id";
5489 SELECT count(1) INTO "dimension_v"
5490 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
5491 -- create "matrix_a" with absolute number of votes in pairwise
5492 -- comparison:
5493 "matrix_a" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
5494 "i" := 1;
5495 "j" := 2;
5496 FOR "battle_row" IN
5497 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
5498 ORDER BY
5499 "winning_initiative_id" NULLS FIRST,
5500 "losing_initiative_id" NULLS FIRST
5501 LOOP
5502 "matrix_a"["i"]["j"] := "battle_row"."count";
5503 IF "j" = "dimension_v" THEN
5504 "i" := "i" + 1;
5505 "j" := 1;
5506 ELSE
5507 "j" := "j" + 1;
5508 IF "j" = "i" THEN
5509 "j" := "j" + 1;
5510 END IF;
5511 END IF;
5512 END LOOP;
5513 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
5514 RAISE EXCEPTION 'Wrong battle count (should not happen)';
5515 END IF;
5516 -- store direct defeat strengths in "matrix_d" using "defeat_strength"
5517 -- and "secondary_link_strength" functions:
5518 "matrix_d" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
5519 "i" := 1;
5520 LOOP
5521 "j" := 1;
5522 LOOP
5523 IF "i" != "j" THEN
5524 "matrix_d"["i"]["j"] := (
5525 "defeat_strength"(
5526 "matrix_a"["i"]["j"],
5527 "matrix_a"["j"]["i"],
5528 "policy_row"."defeat_strength"
5529 ),
5530 "secondary_link_strength"(
5531 "i",
5532 "j",
5533 "policy_row"."tie_breaking"
5534 )
5535 )::"link_strength";
5536 END IF;
5537 EXIT WHEN "j" = "dimension_v";
5538 "j" := "j" + 1;
5539 END LOOP;
5540 EXIT WHEN "i" = "dimension_v";
5541 "i" := "i" + 1;
5542 END LOOP;
5543 -- find best paths:
5544 "matrix_p" := "find_best_paths"("matrix_d");
5545 -- create partial order:
5546 "matrix_b" := array_fill(NULL::BOOLEAN, ARRAY["dimension_v", "dimension_v"]);
5547 "i" := 1;
5548 LOOP
5549 "j" := "i" + 1;
5550 LOOP
5551 IF "i" != "j" THEN
5552 IF "matrix_p"["i"]["j"] > "matrix_p"["j"]["i"] THEN
5553 "matrix_b"["i"]["j"] := TRUE;
5554 "matrix_b"["j"]["i"] := FALSE;
5555 ELSIF "matrix_p"["i"]["j"] < "matrix_p"["j"]["i"] THEN
5556 "matrix_b"["i"]["j"] := FALSE;
5557 "matrix_b"["j"]["i"] := TRUE;
5558 END IF;
5559 END IF;
5560 EXIT WHEN "j" = "dimension_v";
5561 "j" := "j" + 1;
5562 END LOOP;
5563 EXIT WHEN "i" = "dimension_v" - 1;
5564 "i" := "i" + 1;
5565 END LOOP;
5566 -- tie-breaking by forbidding shared weakest links in beat-paths
5567 -- (unless "tie_breaking" is set to 'simple', in which case tie-breaking
5568 -- is performed later by initiative id):
5569 IF "policy_row"."tie_breaking" != 'simple'::"tie_breaking" THEN
5570 "m" := 1;
5571 LOOP
5572 "n" := "m" + 1;
5573 LOOP
5574 -- only process those candidates m and n, which are tied:
5575 IF "matrix_b"["m"]["n"] ISNULL THEN
5576 -- start with beat-paths prior tie-breaking:
5577 "matrix_t" := "matrix_p";
5578 -- start with all links allowed:
5579 "matrix_f" := array_fill(FALSE, ARRAY["dimension_v", "dimension_v"]);
5580 LOOP
5581 -- determine (and forbid) that link that is the weakest link
5582 -- in both the best path from candidate m to candidate n and
5583 -- from candidate n to candidate m:
5584 "i" := 1;
5585 <<forbid_one_link>>
5586 LOOP
5587 "j" := 1;
5588 LOOP
5589 IF "i" != "j" THEN
5590 IF "matrix_d"["i"]["j"] = "matrix_t"["m"]["n"] THEN
5591 "matrix_f"["i"]["j"] := TRUE;
5592 -- exit for performance reasons,
5593 -- as exactly one link will be found:
5594 EXIT forbid_one_link;
5595 END IF;
5596 END IF;
5597 EXIT WHEN "j" = "dimension_v";
5598 "j" := "j" + 1;
5599 END LOOP;
5600 IF "i" = "dimension_v" THEN
5601 RAISE EXCEPTION 'Did not find shared weakest link for tie-breaking (should not happen)';
5602 END IF;
5603 "i" := "i" + 1;
5604 END LOOP;
5605 -- calculate best beat-paths while ignoring forbidden links:
5606 "i" := 1;
5607 LOOP
5608 "j" := 1;
5609 LOOP
5610 IF "i" != "j" THEN
5611 "matrix_t"["i"]["j"] := CASE
5612 WHEN "matrix_f"["i"]["j"]
5613 THEN ((-1::INT8) << 63, 0)::"link_strength" -- worst possible value
5614 ELSE "matrix_d"["i"]["j"] END;
5615 END IF;
5616 EXIT WHEN "j" = "dimension_v";
5617 "j" := "j" + 1;
5618 END LOOP;
5619 EXIT WHEN "i" = "dimension_v";
5620 "i" := "i" + 1;
5621 END LOOP;
5622 "matrix_t" := "find_best_paths"("matrix_t");
5623 -- extend partial order, if tie-breaking was successful:
5624 IF "matrix_t"["m"]["n"] > "matrix_t"["n"]["m"] THEN
5625 "matrix_b"["m"]["n"] := TRUE;
5626 "matrix_b"["n"]["m"] := FALSE;
5627 EXIT;
5628 ELSIF "matrix_t"["m"]["n"] < "matrix_t"["n"]["m"] THEN
5629 "matrix_b"["m"]["n"] := FALSE;
5630 "matrix_b"["n"]["m"] := TRUE;
5631 EXIT;
5632 END IF;
5633 END LOOP;
5634 END IF;
5635 EXIT WHEN "n" = "dimension_v";
5636 "n" := "n" + 1;
5637 END LOOP;
5638 EXIT WHEN "m" = "dimension_v" - 1;
5639 "m" := "m" + 1;
5640 END LOOP;
5641 END IF;
5642 -- store a unique ranking in "rank_ary":
5643 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
5644 "rank_v" := 1;
5645 LOOP
5646 "i" := 1;
5647 <<assign_next_rank>>
5648 LOOP
5649 IF "rank_ary"["i"] ISNULL THEN
5650 "j" := 1;
5651 LOOP
5652 IF
5653 "i" != "j" AND
5654 "rank_ary"["j"] ISNULL AND
5655 ( "matrix_b"["j"]["i"] OR
5656 -- tie-breaking by "id"
5657 ( "matrix_b"["j"]["i"] ISNULL AND
5658 "j" < "i" ) )
5659 THEN
5660 -- someone else is better
5661 EXIT;
5662 END IF;
5663 IF "j" = "dimension_v" THEN
5664 -- noone is better
5665 "rank_ary"["i"] := "rank_v";
5666 EXIT assign_next_rank;
5667 END IF;
5668 "j" := "j" + 1;
5669 END LOOP;
5670 END IF;
5671 "i" := "i" + 1;
5672 IF "i" > "dimension_v" THEN
5673 RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
5674 END IF;
5675 END LOOP;
5676 EXIT WHEN "rank_v" = "dimension_v";
5677 "rank_v" := "rank_v" + 1;
5678 END LOOP;
5679 -- write preliminary results:
5680 "i" := 2; -- omit status quo with "i" = 1
5681 FOR "initiative_id_v" IN
5682 SELECT "id" FROM "initiative"
5683 WHERE "issue_id" = "issue_id_p" AND "admitted"
5684 ORDER BY "id"
5685 LOOP
5686 UPDATE "initiative" SET
5687 "direct_majority" =
5688 CASE WHEN "policy_row"."direct_majority_strict" THEN
5689 "positive_votes" * "policy_row"."direct_majority_den" >
5690 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
5691 ELSE
5692 "positive_votes" * "policy_row"."direct_majority_den" >=
5693 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
5694 END
5695 AND "positive_votes" >= "policy_row"."direct_majority_positive"
5696 AND "issue_row"."voter_count"-"negative_votes" >=
5697 "policy_row"."direct_majority_non_negative",
5698 "indirect_majority" =
5699 CASE WHEN "policy_row"."indirect_majority_strict" THEN
5700 "positive_votes" * "policy_row"."indirect_majority_den" >
5701 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
5702 ELSE
5703 "positive_votes" * "policy_row"."indirect_majority_den" >=
5704 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
5705 END
5706 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
5707 AND "issue_row"."voter_count"-"negative_votes" >=
5708 "policy_row"."indirect_majority_non_negative",
5709 "schulze_rank" = "rank_ary"["i"],
5710 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
5711 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"[1],
5712 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"[1],
5713 "reverse_beat_path" = CASE WHEN "policy_row"."defeat_strength" = 'simple'::"defeat_strength"
5714 THEN NULL
5715 ELSE "matrix_p"[1]["i"]."primary" >= 0 END,
5716 "eligible" = FALSE,
5717 "winner" = FALSE,
5718 "rank" = NULL -- NOTE: in cases of manual reset of issue state
5719 WHERE "id" = "initiative_id_v";
5720 "i" := "i" + 1;
5721 END LOOP;
5722 IF "i" != "dimension_v" + 1 THEN
5723 RAISE EXCEPTION 'Wrong winner count (should not happen)';
5724 END IF;
5725 -- take indirect majorities into account:
5726 LOOP
5727 UPDATE "initiative" SET "indirect_majority" = TRUE
5728 FROM (
5729 SELECT "new_initiative"."id" AS "initiative_id"
5730 FROM "initiative" "old_initiative"
5731 JOIN "initiative" "new_initiative"
5732 ON "new_initiative"."issue_id" = "issue_id_p"
5733 AND "new_initiative"."indirect_majority" = FALSE
5734 JOIN "battle" "battle_win"
5735 ON "battle_win"."issue_id" = "issue_id_p"
5736 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
5737 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
5738 JOIN "battle" "battle_lose"
5739 ON "battle_lose"."issue_id" = "issue_id_p"
5740 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
5741 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
5742 WHERE "old_initiative"."issue_id" = "issue_id_p"
5743 AND "old_initiative"."indirect_majority" = TRUE
5744 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
5745 "battle_win"."count" * "policy_row"."indirect_majority_den" >
5746 "policy_row"."indirect_majority_num" *
5747 ("battle_win"."count"+"battle_lose"."count")
5748 ELSE
5749 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
5750 "policy_row"."indirect_majority_num" *
5751 ("battle_win"."count"+"battle_lose"."count")
5752 END
5753 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
5754 AND "issue_row"."voter_count"-"battle_lose"."count" >=
5755 "policy_row"."indirect_majority_non_negative"
5756 ) AS "subquery"
5757 WHERE "id" = "subquery"."initiative_id";
5758 EXIT WHEN NOT FOUND;
5759 END LOOP;
5760 -- set "multistage_majority" for remaining matching initiatives:
5761 UPDATE "initiative" SET "multistage_majority" = TRUE
5762 FROM (
5763 SELECT "losing_initiative"."id" AS "initiative_id"
5764 FROM "initiative" "losing_initiative"
5765 JOIN "initiative" "winning_initiative"
5766 ON "winning_initiative"."issue_id" = "issue_id_p"
5767 AND "winning_initiative"."admitted"
5768 JOIN "battle" "battle_win"
5769 ON "battle_win"."issue_id" = "issue_id_p"
5770 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
5771 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
5772 JOIN "battle" "battle_lose"
5773 ON "battle_lose"."issue_id" = "issue_id_p"
5774 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
5775 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
5776 WHERE "losing_initiative"."issue_id" = "issue_id_p"
5777 AND "losing_initiative"."admitted"
5778 AND "winning_initiative"."schulze_rank" <
5779 "losing_initiative"."schulze_rank"
5780 AND "battle_win"."count" > "battle_lose"."count"
5781 AND (
5782 "battle_win"."count" > "winning_initiative"."positive_votes" OR
5783 "battle_lose"."count" < "losing_initiative"."negative_votes" )
5784 ) AS "subquery"
5785 WHERE "id" = "subquery"."initiative_id";
5786 -- mark eligible initiatives:
5787 UPDATE "initiative" SET "eligible" = TRUE
5788 WHERE "issue_id" = "issue_id_p"
5789 AND "initiative"."direct_majority"
5790 AND "initiative"."indirect_majority"
5791 AND "initiative"."better_than_status_quo"
5792 AND (
5793 "policy_row"."no_multistage_majority" = FALSE OR
5794 "initiative"."multistage_majority" = FALSE )
5795 AND (
5796 "policy_row"."no_reverse_beat_path" = FALSE OR
5797 coalesce("initiative"."reverse_beat_path", FALSE) = FALSE );
5798 -- mark final winner:
5799 UPDATE "initiative" SET "winner" = TRUE
5800 FROM (
5801 SELECT "id" AS "initiative_id"
5802 FROM "initiative"
5803 WHERE "issue_id" = "issue_id_p" AND "eligible"
5804 ORDER BY
5805 "schulze_rank",
5806 "id"
5807 LIMIT 1
5808 ) AS "subquery"
5809 WHERE "id" = "subquery"."initiative_id";
5810 -- write (final) ranks:
5811 "rank_v" := 1;
5812 FOR "initiative_id_v" IN
5813 SELECT "id"
5814 FROM "initiative"
5815 WHERE "issue_id" = "issue_id_p" AND "admitted"
5816 ORDER BY
5817 "winner" DESC,
5818 "eligible" DESC,
5819 "schulze_rank",
5820 "id"
5821 LOOP
5822 UPDATE "initiative" SET "rank" = "rank_v"
5823 WHERE "id" = "initiative_id_v";
5824 "rank_v" := "rank_v" + 1;
5825 END LOOP;
5826 -- set schulze rank of status quo and mark issue as finished:
5827 UPDATE "issue" SET
5828 "status_quo_schulze_rank" = "rank_ary"[1],
5829 "state" =
5830 CASE WHEN EXISTS (
5831 SELECT NULL FROM "initiative"
5832 WHERE "issue_id" = "issue_id_p" AND "winner"
5833 ) THEN
5834 'finished_with_winner'::"issue_state"
5835 ELSE
5836 'finished_without_winner'::"issue_state"
5837 END,
5838 "closed" = "phase_finished",
5839 "phase_finished" = NULL
5840 WHERE "id" = "issue_id_p";
5841 RETURN;
5842 END;
5843 $$;
5845 COMMENT ON FUNCTION "calculate_ranks"
5846 ( "issue"."id"%TYPE )
5847 IS 'Determine ranking (Votes have to be counted first)';
5851 -----------------------------
5852 -- Automatic state changes --
5853 -----------------------------
5856 CREATE FUNCTION "issue_admission"
5857 ( "area_id_p" "area"."id"%TYPE )
5858 RETURNS BOOLEAN
5859 LANGUAGE 'plpgsql' VOLATILE AS $$
5860 DECLARE
5861 "issue_id_v" "issue"."id"%TYPE;
5862 BEGIN
5863 PERFORM "dont_require_transaction_isolation"();
5864 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
5865 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
5866 FROM "area_quorum" AS "view"
5867 WHERE "area"."id" = "view"."area_id"
5868 AND "area"."id" = "area_id_p";
5869 SELECT "id" INTO "issue_id_v" FROM "issue_for_admission"
5870 WHERE "area_id" = "area_id_p";
5871 IF "issue_id_v" ISNULL THEN RETURN FALSE; END IF;
5872 UPDATE "issue" SET
5873 "admission_snapshot_id" = "latest_snapshot_id",
5874 "state" = 'discussion',
5875 "accepted" = now(),
5876 "phase_finished" = NULL
5877 WHERE "id" = "issue_id_v";
5878 RETURN TRUE;
5879 END;
5880 $$;
5882 COMMENT ON FUNCTION "issue_admission"
5883 ( "area"."id"%TYPE )
5884 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';
5887 CREATE TYPE "check_issue_persistence" AS (
5888 "state" "issue_state",
5889 "phase_finished" BOOLEAN,
5890 "issue_revoked" BOOLEAN,
5891 "snapshot_created" BOOLEAN,
5892 "harmonic_weights_set" BOOLEAN,
5893 "closed_voting" BOOLEAN );
5895 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';
5898 CREATE FUNCTION "check_issue"
5899 ( "issue_id_p" "issue"."id"%TYPE,
5900 "persist" "check_issue_persistence" )
5901 RETURNS "check_issue_persistence"
5902 LANGUAGE 'plpgsql' VOLATILE AS $$
5903 DECLARE
5904 "issue_row" "issue"%ROWTYPE;
5905 "last_calculated_v" "snapshot"."calculated"%TYPE;
5906 "policy_row" "policy"%ROWTYPE;
5907 "initiative_row" "initiative"%ROWTYPE;
5908 "state_v" "issue_state";
5909 BEGIN
5910 PERFORM "require_transaction_isolation"();
5911 IF "persist" ISNULL THEN
5912 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
5913 FOR UPDATE;
5914 SELECT "calculated" INTO "last_calculated_v"
5915 FROM "snapshot" JOIN "snapshot_issue"
5916 ON "snapshot"."id" = "snapshot_issue"."snapshot_id"
5917 WHERE "snapshot_issue"."issue_id" = "issue_id_p";
5918 IF "issue_row"."closed" NOTNULL THEN
5919 RETURN NULL;
5920 END IF;
5921 "persist"."state" := "issue_row"."state";
5922 IF
5923 ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=
5924 "issue_row"."created" + "issue_row"."max_admission_time" ) OR
5925 ( "issue_row"."state" = 'discussion' AND now() >=
5926 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
5927 ( "issue_row"."state" = 'verification' AND now() >=
5928 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
5929 ( "issue_row"."state" = 'voting' AND now() >=
5930 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
5931 THEN
5932 "persist"."phase_finished" := TRUE;
5933 ELSE
5934 "persist"."phase_finished" := FALSE;
5935 END IF;
5936 IF
5937 NOT EXISTS (
5938 -- all initiatives are revoked
5939 SELECT NULL FROM "initiative"
5940 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
5941 ) AND (
5942 -- and issue has not been accepted yet
5943 "persist"."state" = 'admission' OR
5944 -- or verification time has elapsed
5945 ( "persist"."state" = 'verification' AND
5946 "persist"."phase_finished" ) OR
5947 -- or no initiatives have been revoked lately
5948 NOT EXISTS (
5949 SELECT NULL FROM "initiative"
5950 WHERE "issue_id" = "issue_id_p"
5951 AND now() < "revoked" + "issue_row"."verification_time"
5952 )
5953 )
5954 THEN
5955 "persist"."issue_revoked" := TRUE;
5956 ELSE
5957 "persist"."issue_revoked" := FALSE;
5958 END IF;
5959 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
5960 UPDATE "issue" SET "phase_finished" = now()
5961 WHERE "id" = "issue_row"."id";
5962 RETURN "persist";
5963 ELSIF
5964 "persist"."state" IN ('admission', 'discussion', 'verification')
5965 THEN
5966 RETURN "persist";
5967 ELSE
5968 RETURN NULL;
5969 END IF;
5970 END IF;
5971 IF
5972 "persist"."state" IN ('admission', 'discussion', 'verification') AND
5973 coalesce("persist"."snapshot_created", FALSE) = FALSE
5974 THEN
5975 IF "persist"."state" != 'admission' THEN
5976 PERFORM "take_snapshot"("issue_id_p");
5977 PERFORM "finish_snapshot"("issue_id_p");
5978 END IF;
5979 "persist"."snapshot_created" = TRUE;
5980 IF "persist"."phase_finished" THEN
5981 IF "persist"."state" = 'admission' THEN
5982 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id";
5983 ELSIF "persist"."state" = 'discussion' THEN
5984 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id";
5985 ELSIF "persist"."state" = 'verification' THEN
5986 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id";
5987 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
5988 SELECT * INTO "policy_row" FROM "policy"
5989 WHERE "id" = "issue_row"."policy_id";
5990 FOR "initiative_row" IN
5991 SELECT * FROM "initiative"
5992 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
5993 FOR UPDATE
5994 LOOP
5995 IF
5996 "initiative_row"."polling" OR (
5997 "initiative_row"."satisfied_supporter_count" >
5998 "policy_row"."initiative_quorum" AND
5999 "initiative_row"."satisfied_supporter_count" *
6000 "policy_row"."initiative_quorum_den" >=
6001 "issue_row"."population" * "policy_row"."initiative_quorum_num"
6002 )
6003 THEN
6004 UPDATE "initiative" SET "admitted" = TRUE
6005 WHERE "id" = "initiative_row"."id";
6006 ELSE
6007 UPDATE "initiative" SET "admitted" = FALSE
6008 WHERE "id" = "initiative_row"."id";
6009 END IF;
6010 END LOOP;
6011 END IF;
6012 END IF;
6013 RETURN "persist";
6014 END IF;
6015 IF
6016 "persist"."state" IN ('admission', 'discussion', 'verification') AND
6017 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
6018 THEN
6019 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
6020 "persist"."harmonic_weights_set" = TRUE;
6021 IF
6022 "persist"."phase_finished" OR
6023 "persist"."issue_revoked" OR
6024 "persist"."state" = 'admission'
6025 THEN
6026 RETURN "persist";
6027 ELSE
6028 RETURN NULL;
6029 END IF;
6030 END IF;
6031 IF "persist"."issue_revoked" THEN
6032 IF "persist"."state" = 'admission' THEN
6033 "state_v" := 'canceled_revoked_before_accepted';
6034 ELSIF "persist"."state" = 'discussion' THEN
6035 "state_v" := 'canceled_after_revocation_during_discussion';
6036 ELSIF "persist"."state" = 'verification' THEN
6037 "state_v" := 'canceled_after_revocation_during_verification';
6038 END IF;
6039 UPDATE "issue" SET
6040 "state" = "state_v",
6041 "closed" = "phase_finished",
6042 "phase_finished" = NULL
6043 WHERE "id" = "issue_id_p";
6044 RETURN NULL;
6045 END IF;
6046 IF "persist"."state" = 'admission' THEN
6047 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
6048 FOR UPDATE;
6049 IF "issue_row"."phase_finished" NOTNULL THEN
6050 UPDATE "issue" SET
6051 "state" = 'canceled_issue_not_accepted',
6052 "closed" = "phase_finished",
6053 "phase_finished" = NULL
6054 WHERE "id" = "issue_id_p";
6055 END IF;
6056 RETURN NULL;
6057 END IF;
6058 IF "persist"."phase_finished" THEN
6059 IF "persist"."state" = 'discussion' THEN
6060 UPDATE "issue" SET
6061 "state" = 'verification',
6062 "half_frozen" = "phase_finished",
6063 "phase_finished" = NULL
6064 WHERE "id" = "issue_id_p";
6065 RETURN NULL;
6066 END IF;
6067 IF "persist"."state" = 'verification' THEN
6068 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
6069 FOR UPDATE;
6070 SELECT * INTO "policy_row" FROM "policy"
6071 WHERE "id" = "issue_row"."policy_id";
6072 IF EXISTS (
6073 SELECT NULL FROM "initiative"
6074 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
6075 ) THEN
6076 UPDATE "issue" SET
6077 "state" = 'voting',
6078 "fully_frozen" = "phase_finished",
6079 "phase_finished" = NULL
6080 WHERE "id" = "issue_id_p";
6081 ELSE
6082 UPDATE "issue" SET
6083 "state" = 'canceled_no_initiative_admitted',
6084 "fully_frozen" = "phase_finished",
6085 "closed" = "phase_finished",
6086 "phase_finished" = NULL
6087 WHERE "id" = "issue_id_p";
6088 -- NOTE: The following DELETE statements have effect only when
6089 -- issue state has been manipulated
6090 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
6091 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
6092 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
6093 END IF;
6094 RETURN NULL;
6095 END IF;
6096 IF "persist"."state" = 'voting' THEN
6097 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
6098 PERFORM "close_voting"("issue_id_p");
6099 "persist"."closed_voting" = TRUE;
6100 RETURN "persist";
6101 END IF;
6102 PERFORM "calculate_ranks"("issue_id_p");
6103 RETURN NULL;
6104 END IF;
6105 END IF;
6106 RAISE WARNING 'should not happen';
6107 RETURN NULL;
6108 END;
6109 $$;
6111 COMMENT ON FUNCTION "check_issue"
6112 ( "issue"."id"%TYPE,
6113 "check_issue_persistence" )
6114 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")';
6117 CREATE FUNCTION "check_everything"()
6118 RETURNS VOID
6119 LANGUAGE 'plpgsql' VOLATILE AS $$
6120 DECLARE
6121 "area_id_v" "area"."id"%TYPE;
6122 "snapshot_id_v" "snapshot"."id"%TYPE;
6123 "issue_id_v" "issue"."id"%TYPE;
6124 "persist_v" "check_issue_persistence";
6125 BEGIN
6126 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
6127 DELETE FROM "expired_session";
6128 DELETE FROM "expired_token";
6129 DELETE FROM "expired_snapshot";
6130 PERFORM "check_activity"();
6131 PERFORM "calculate_member_counts"();
6132 FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP
6133 SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v";
6134 PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"
6135 WHERE "snapshot_id" = "snapshot_id_v";
6136 LOOP
6137 EXIT WHEN "issue_admission"("area_id_v") = FALSE;
6138 END LOOP;
6139 END LOOP;
6140 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
6141 "persist_v" := NULL;
6142 LOOP
6143 "persist_v" := "check_issue"("issue_id_v", "persist_v");
6144 EXIT WHEN "persist_v" ISNULL;
6145 END LOOP;
6146 END LOOP;
6147 RETURN;
6148 END;
6149 $$;
6151 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';
6155 ----------------------
6156 -- Deletion of data --
6157 ----------------------
6160 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
6161 RETURNS VOID
6162 LANGUAGE 'plpgsql' VOLATILE AS $$
6163 BEGIN
6164 IF EXISTS (
6165 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
6166 ) THEN
6167 -- override protection triggers:
6168 INSERT INTO "temporary_transaction_data" ("key", "value")
6169 VALUES ('override_protection_triggers', TRUE::TEXT);
6170 -- clean data:
6171 DELETE FROM "delegating_voter"
6172 WHERE "issue_id" = "issue_id_p";
6173 DELETE FROM "direct_voter"
6174 WHERE "issue_id" = "issue_id_p";
6175 DELETE FROM "delegating_interest_snapshot"
6176 WHERE "issue_id" = "issue_id_p";
6177 DELETE FROM "direct_interest_snapshot"
6178 WHERE "issue_id" = "issue_id_p";
6179 DELETE FROM "non_voter"
6180 WHERE "issue_id" = "issue_id_p";
6181 DELETE FROM "delegation"
6182 WHERE "issue_id" = "issue_id_p";
6183 DELETE FROM "supporter"
6184 USING "initiative" -- NOTE: due to missing index on issue_id
6185 WHERE "initiative"."issue_id" = "issue_id_p"
6186 AND "supporter"."initiative_id" = "initiative_id";
6187 -- mark issue as cleaned:
6188 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
6189 -- finish overriding protection triggers (avoids garbage):
6190 DELETE FROM "temporary_transaction_data"
6191 WHERE "key" = 'override_protection_triggers';
6192 END IF;
6193 RETURN;
6194 END;
6195 $$;
6197 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
6200 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
6201 RETURNS VOID
6202 LANGUAGE 'plpgsql' VOLATILE AS $$
6203 BEGIN
6204 UPDATE "member" SET
6205 "last_login" = NULL,
6206 "last_delegation_check" = NULL,
6207 "login" = NULL,
6208 "password" = NULL,
6209 "authority" = NULL,
6210 "authority_uid" = NULL,
6211 "authority_login" = NULL,
6212 "locked" = TRUE,
6213 "active" = FALSE,
6214 "notify_email" = NULL,
6215 "notify_email_unconfirmed" = NULL,
6216 "notify_email_secret" = NULL,
6217 "notify_email_secret_expiry" = NULL,
6218 "notify_email_lock_expiry" = NULL,
6219 "disable_notifications" = TRUE,
6220 "notification_counter" = DEFAULT,
6221 "notification_sample_size" = 0,
6222 "notification_dow" = NULL,
6223 "notification_hour" = NULL,
6224 "login_recovery_expiry" = NULL,
6225 "password_reset_secret" = NULL,
6226 "password_reset_secret_expiry" = NULL,
6227 "location" = NULL
6228 WHERE "id" = "member_id_p";
6229 -- "text_search_data" is updated by triggers
6230 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
6231 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
6232 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
6233 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
6234 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
6235 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
6236 DELETE FROM "session" WHERE "member_id" = "member_id_p";
6237 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
6238 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
6239 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
6240 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
6241 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
6242 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
6243 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
6244 DELETE FROM "direct_voter" USING "issue"
6245 WHERE "direct_voter"."issue_id" = "issue"."id"
6246 AND "issue"."closed" ISNULL
6247 AND "member_id" = "member_id_p";
6248 RETURN;
6249 END;
6250 $$;
6252 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)';
6255 CREATE FUNCTION "delete_private_data"()
6256 RETURNS VOID
6257 LANGUAGE 'plpgsql' VOLATILE AS $$
6258 BEGIN
6259 DELETE FROM "temporary_transaction_data";
6260 DELETE FROM "member" WHERE "activated" ISNULL;
6261 UPDATE "member" SET
6262 "invite_code" = NULL,
6263 "invite_code_expiry" = NULL,
6264 "admin_comment" = NULL,
6265 "last_login" = NULL,
6266 "last_delegation_check" = NULL,
6267 "login" = NULL,
6268 "password" = NULL,
6269 "authority" = NULL,
6270 "authority_uid" = NULL,
6271 "authority_login" = NULL,
6272 "lang" = NULL,
6273 "notify_email" = NULL,
6274 "notify_email_unconfirmed" = NULL,
6275 "notify_email_secret" = NULL,
6276 "notify_email_secret_expiry" = NULL,
6277 "notify_email_lock_expiry" = NULL,
6278 "disable_notifications" = TRUE,
6279 "notification_counter" = DEFAULT,
6280 "notification_sample_size" = 0,
6281 "notification_dow" = NULL,
6282 "notification_hour" = NULL,
6283 "login_recovery_expiry" = NULL,
6284 "password_reset_secret" = NULL,
6285 "password_reset_secret_expiry" = NULL,
6286 "location" = NULL;
6287 -- "text_search_data" is updated by triggers
6288 DELETE FROM "setting";
6289 DELETE FROM "setting_map";
6290 DELETE FROM "member_relation_setting";
6291 DELETE FROM "member_image";
6292 DELETE FROM "contact";
6293 DELETE FROM "ignored_member";
6294 DELETE FROM "session";
6295 DELETE FROM "area_setting";
6296 DELETE FROM "issue_setting";
6297 DELETE FROM "ignored_initiative";
6298 DELETE FROM "initiative_setting";
6299 DELETE FROM "suggestion_setting";
6300 DELETE FROM "non_voter";
6301 DELETE FROM "direct_voter" USING "issue"
6302 WHERE "direct_voter"."issue_id" = "issue"."id"
6303 AND "issue"."closed" ISNULL;
6304 RETURN;
6305 END;
6306 $$;
6308 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.';
6312 COMMIT;