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