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