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