liquid_feedback_core
view update/core-update.v3.2.2-v4.0.0.sql @ 559:71f431fb78d4
Bugfix in "member_useterms" table: allow multiple entries per member
| author | jbe |
|---|---|
| date | Wed Sep 20 18:07:42 2017 +0200 (2017-09-20) |
| parents | 25b551e53da2 |
| children | 318827ecd041 |
line source
1 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'unit_created';
2 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'unit_updated';
3 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'area_created';
4 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'area_updated';
5 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'policy_created';
6 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'policy_updated';
7 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'suggestion_deleted';
8 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_activated';
9 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_deleted';
10 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_active';
11 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_name_updated';
12 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_profile_updated';
13 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_image_updated';
14 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'interest';
15 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'initiator';
16 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'support';
17 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'support_updated';
18 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'suggestion_rated';
19 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'delegation';
20 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'contact';
23 BEGIN;
26 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
27 SELECT * FROM (VALUES ('4.0-dev', 4, 0, -1))
28 AS "subquery"("string", "major", "minor", "revision");
31 ALTER TABLE "system_setting" ADD COLUMN "snapshot_retention" INTERVAL;
33 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.';
36 CREATE TABLE "verification" (
37 "id" SERIAL8 PRIMARY KEY,
38 "requested" TIMESTAMPTZ,
39 "request_origin" JSONB,
40 "request_data" JSONB,
41 "verified" TIMESTAMPTZ,
42 "verification_origin" JSONB,
43 "verification_data" JSONB,
44 "denied" TIMESTAMPTZ,
45 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
46 "comment" TEXT,
47 CONSTRAINT "verified_and_denied_conflict" CHECK (
48 "verified" ISNULL OR "denied" ISNULL ) );
49 CREATE INDEX "verification_requested_idx" ON "verification" ("requested");
50 CREATE INDEX "verification_open_request_idx" ON "verification" ("requested") WHERE "verified" ISNULL AND "denied" ISNULL;
51 CREATE INDEX "verification_verified_idx" ON "verification" ("verified");
52 CREATE INDEX "verification_denied_idx" ON "verification" ("denied");
53 CREATE INDEX "verification_member_id_idx" ON "verification" ("member_id");
55 COMMENT ON TABLE "verification" IS 'Request to verify a participant';
57 COMMENT ON COLUMN "verification"."requested" IS 'Timestamp when request for verification has been submitted';
58 COMMENT ON COLUMN "verification"."request_origin" IS 'JSON data containing information about the origin of the request (e.g. IP address or hostname)';
59 COMMENT ON COLUMN "verification"."request_data" IS 'JSON data containing information about the entity to be verified (e.g. real name, address, etc.)';
60 COMMENT ON COLUMN "verification"."verified" IS 'Timestamp when request for verification has been accepted by authority';
61 COMMENT ON COLUMN "verification"."verification_origin" IS 'JSON data containing information about the authority or operator who accepted or denied the request';
62 COMMENT ON COLUMN "verification"."verification_data" IS 'JSON data containing additional verified data, but all public information shall be copied to "member"."identification", "member"."verification" and/or "member"."name" if applicable for setup';
63 COMMENT ON COLUMN "verification"."denied" IS 'Timestamp when request for verification has been denied by authority';
64 COMMENT ON COLUMN "verification"."member_id" IS 'Timestamp when request for verification has been denied by authority';
65 COMMENT ON COLUMN "verification"."comment" IS 'Administrative comment';
68 ALTER TABLE "member" ADD COLUMN "deleted" TIMESTAMPTZ;
69 ALTER TABLE "member" ADD CONSTRAINT "deleted_requires_locked"
70 CHECK ("deleted" ISNULL OR "locked" = TRUE);
72 COMMENT ON COLUMN "member"."deleted" IS 'Timestamp of deletion (set by "delete_member" function)';
75 CREATE TABLE "member_settings" (
76 "member_id" INT4 PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
77 "settings" JSONB NOT NULL CHECK (jsonb_typeof("settings") = 'object') );
79 COMMENT ON TABLE "member_settings" IS 'Stores a JSON document for each member containing optional (additional) settings for the respective member';
82 CREATE TABLE "member_useterms" (
83 "id" SERIAL8 PRIMARY KEY,
84 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
85 "accepted" TIMESTAMPTZ NOT NULL DEFAULT now(),
86 "contract_identifier" TEXT NOT NULL );
88 COMMENT ON TABLE "member_useterms" IS 'Keeps record of accepted terms of use; may contain multiple rows per member';
90 COMMENT ON COLUMN "member_useterms"."accepted" IS 'Point in time when user accepted the terms of use';
91 COMMENT ON COLUMN "member_useterms"."contract_identifier" IS 'String identifier to denote the accepted terms of use, including their version or revision';
94 CREATE TABLE "member_profile" (
95 "member_id" INT4 PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
96 "formatting_engine" TEXT,
97 "statement" TEXT,
98 "profile" JSONB NOT NULL DEFAULT '{}' CHECK (jsonb_typeof("profile") = 'object'),
99 "profile_text_data" TEXT,
100 "text_search_data" TSVECTOR );
101 CREATE INDEX "member_profile_text_search_data_idx" ON "member_profile" USING gin ("text_search_data");
102 CREATE TRIGGER "update_text_search_data"
103 BEFORE INSERT OR UPDATE ON "member_profile"
104 FOR EACH ROW EXECUTE PROCEDURE
105 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
106 'statement', 'profile_text_data');
108 COMMENT ON COLUMN "member_profile"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member_profile"."statement"';
109 COMMENT ON COLUMN "member_profile"."statement" IS 'Freely chosen text of the member for his/her profile';
110 COMMENT ON COLUMN "member_profile"."profile" IS 'Additional profile data as JSON document';
111 COMMENT ON COLUMN "member_profile"."profile_text_data" IS 'Text data from "profile" field for full text search';
114 INSERT INTO "member_profile"
115 ( "member_id", "formatting_engine", "statement", "profile")
116 SELECT
117 "id" AS "member_id",
118 "formatting_engine",
119 "statement",
120 json_build_object(
121 'organizational_unit', "organizational_unit",
122 'internal_posts', "internal_posts",
123 'realname', "realname",
124 'birthday', to_char("birthday", 'YYYY-MM-DD'),
125 'address', "address",
126 'email', "email",
127 'xmpp_address', "xmpp_address",
128 'website', "website",
129 'phone', "phone",
130 'mobile_phone', "mobile_phone",
131 'profession', "profession",
132 'external_memberships', "external_memberships",
133 'external_posts', "external_posts"
134 ) AS "profile"
135 FROM "member";
137 UPDATE "member_profile" SET "profile_text_data" =
138 coalesce(("profile"->>'organizational_unit') || ' ', '') ||
139 coalesce(("profile"->>'internal_posts') || ' ', '') ||
140 coalesce(("profile"->>'realname') || ' ', '') ||
141 coalesce(("profile"->>'birthday') || ' ', '') ||
142 coalesce(("profile"->>'address') || ' ', '') ||
143 coalesce(("profile"->>'email') || ' ', '') ||
144 coalesce(("profile"->>'xmpp_address') || ' ', '') ||
145 coalesce(("profile"->>'website') || ' ', '') ||
146 coalesce(("profile"->>'phone') || ' ', '') ||
147 coalesce(("profile"->>'mobile_phone') || ' ', '') ||
148 coalesce(("profile"->>'profession') || ' ', '') ||
149 coalesce(("profile"->>'external_memberships') || ' ', '') ||
150 coalesce(("profile"->>'external_posts') || ' ', '');
153 DROP VIEW "newsletter_to_send";
154 DROP VIEW "scheduled_notification_to_send";
155 DROP VIEW "member_to_notify";
156 DROP VIEW "member_eligible_to_be_notified";
159 ALTER TABLE "member" DROP COLUMN "organizational_unit";
160 ALTER TABLE "member" DROP COLUMN "internal_posts";
161 ALTER TABLE "member" DROP COLUMN "realname";
162 ALTER TABLE "member" DROP COLUMN "birthday";
163 ALTER TABLE "member" DROP COLUMN "address";
164 ALTER TABLE "member" DROP COLUMN "email";
165 ALTER TABLE "member" DROP COLUMN "xmpp_address";
166 ALTER TABLE "member" DROP COLUMN "website";
167 ALTER TABLE "member" DROP COLUMN "phone";
168 ALTER TABLE "member" DROP COLUMN "mobile_phone";
169 ALTER TABLE "member" DROP COLUMN "profession";
170 ALTER TABLE "member" DROP COLUMN "external_memberships";
171 ALTER TABLE "member" DROP COLUMN "external_posts";
172 ALTER TABLE "member" DROP COLUMN "formatting_engine";
173 ALTER TABLE "member" DROP COLUMN "statement";
175 ALTER TABLE "member" ADD COLUMN "location" JSONB;
176 COMMENT ON COLUMN "member"."location" IS 'Geographic location on earth as GeoJSON object';
177 CREATE INDEX "member_location_idx" ON "member" USING gist ((GeoJSON_to_ecluster("location")));
179 DROP TRIGGER "update_text_search_data" ON "member";
180 CREATE TRIGGER "update_text_search_data"
181 BEFORE INSERT OR UPDATE ON "member"
182 FOR EACH ROW EXECUTE PROCEDURE
183 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
184 "name", "identification");
187 CREATE VIEW "member_eligible_to_be_notified" AS
188 SELECT * FROM "member"
189 WHERE "activated" NOTNULL AND "locked" = FALSE;
191 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")';
194 CREATE VIEW "member_to_notify" AS
195 SELECT * FROM "member_eligible_to_be_notified"
196 WHERE "disable_notifications" = FALSE;
198 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)';
201 CREATE VIEW "scheduled_notification_to_send" AS
202 SELECT * FROM (
203 SELECT
204 "id" AS "recipient_id",
205 now() - CASE WHEN "notification_dow" ISNULL THEN
206 ( "notification_sent"::DATE + CASE
207 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
208 THEN 0 ELSE 1 END
209 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
210 ELSE
211 ( "notification_sent"::DATE +
212 ( 7 + "notification_dow" -
213 EXTRACT(DOW FROM
214 ( "notification_sent"::DATE + CASE
215 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
216 THEN 0 ELSE 1 END
217 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
218 )::INTEGER
219 ) % 7 +
220 CASE
221 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
222 THEN 0 ELSE 1
223 END
224 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
225 END AS "pending"
226 FROM (
227 SELECT
228 "id",
229 COALESCE("notification_sent", "activated") AS "notification_sent",
230 "notification_dow",
231 "notification_hour"
232 FROM "member_to_notify"
233 WHERE "notification_hour" NOTNULL
234 ) AS "subquery1"
235 ) AS "subquery2"
236 WHERE "pending" > '0'::INTERVAL;
238 COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending';
240 COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail';
241 COMMENT ON COLUMN "scheduled_notification_to_send"."pending" IS 'Duration for which the notification mail has already been pending';
244 CREATE VIEW "newsletter_to_send" AS
245 SELECT
246 "member"."id" AS "recipient_id",
247 "newsletter"."id" AS "newsletter_id",
248 "newsletter"."published"
249 FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"
250 LEFT JOIN "privilege" ON
251 "privilege"."member_id" = "member"."id" AND
252 "privilege"."unit_id" = "newsletter"."unit_id" AND
253 "privilege"."voting_right" = TRUE
254 LEFT JOIN "subscription" ON
255 "subscription"."member_id" = "member"."id" AND
256 "subscription"."unit_id" = "newsletter"."unit_id"
257 WHERE "newsletter"."published" <= now()
258 AND "newsletter"."sent" ISNULL
259 AND (
260 "member"."disable_notifications" = FALSE OR
261 "newsletter"."include_all_members" = TRUE )
262 AND (
263 "newsletter"."unit_id" ISNULL OR
264 "privilege"."member_id" NOTNULL OR
265 "subscription"."member_id" NOTNULL );
267 COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';
269 COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter was supposed to be sent out (can be used for ordering)';
272 DROP VIEW "expired_session";
273 DROP TABLE "session";
276 CREATE TABLE "session" (
277 UNIQUE ("member_id", "id"), -- index needed for foreign-key on table "token"
278 "id" SERIAL8 PRIMARY KEY,
279 "ident" TEXT NOT NULL UNIQUE,
280 "additional_secret" TEXT,
281 "logout_token" TEXT,
282 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
283 "member_id" INT4 REFERENCES "member" ("id") ON DELETE SET NULL,
284 "authority" TEXT,
285 "authority_uid" TEXT,
286 "authority_login" TEXT,
287 "needs_delegation_check" BOOLEAN NOT NULL DEFAULT FALSE,
288 "lang" TEXT );
289 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
291 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
293 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
294 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
295 COMMENT ON COLUMN "session"."logout_token" IS 'Optional token to authorize logout through external component';
296 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
297 COMMENT ON COLUMN "session"."authority" IS 'Temporary store for "member"."authority" during member account creation';
298 COMMENT ON COLUMN "session"."authority_uid" IS 'Temporary store for "member"."authority_uid" during member account creation';
299 COMMENT ON COLUMN "session"."authority_login" IS 'Temporary store for "member"."authority_login" during member account creation';
300 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';
301 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
304 CREATE TYPE "authflow" AS ENUM ('code', 'token');
306 COMMENT ON TYPE "authflow" IS 'OAuth 2.0 flows: ''code'' = Authorization Code flow, ''token'' = Implicit flow';
309 CREATE TABLE "system_application" (
310 "id" SERIAL4 PRIMARY KEY,
311 "name" TEXT NOT NULL,
312 "discovery_baseurl" TEXT,
313 "client_id" TEXT NOT NULL UNIQUE,
314 "default_redirect_uri" TEXT NOT NULL,
315 "cert_common_name" TEXT,
316 "client_cred_scope" TEXT,
317 "flow" "authflow",
318 "automatic_scope" TEXT,
319 "permitted_scope" TEXT,
320 "forbidden_scope" TEXT );
322 COMMENT ON TABLE "system_application" IS 'OAuth 2.0 clients that are registered by the system administrator';
324 COMMENT ON COLUMN "system_application"."name" IS 'Human readable name of application';
325 COMMENT ON COLUMN "system_application"."discovery_baseurl" IS 'Base URL for application discovery; NULL for hidden application';
326 COMMENT ON COLUMN "system_application"."client_id" IS 'OAuth 2.0 "client_id"';
327 COMMENT ON COLUMN "system_application"."cert_common_name" IS 'Value for CN field of TLS client certificate';
328 COMMENT ON COLUMN "system_application"."client_cred_scope" IS 'Space-separated list of scopes; If set, Client Credentials Grant is allowed; value determines scope';
329 COMMENT ON COLUMN "system_application"."flow" IS 'If set to ''code'' or ''token'', then Authorization Code or Implicit flow is allowed respectively';
330 COMMENT ON COLUMN "system_application"."automatic_scope" IS 'Space-separated list of scopes; Automatically granted scope for Authorization Code or Implicit flow';
331 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';
332 COMMENT ON COLUMN "system_application"."forbidden_scope" IS 'Space-separated list of scopes that may not be granted to the application by a member';
335 CREATE TABLE "system_application_redirect_uri" (
336 PRIMARY KEY ("system_application_id", "redirect_uri"),
337 "system_application_id" INT4 REFERENCES "system_application" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
338 "redirect_uri" TEXT );
340 COMMENT ON TABLE "system_application_redirect_uri" IS 'Additional OAuth 2.0 redirection endpoints, which may be selected through the "redirect_uri" GET parameter';
343 CREATE TABLE "dynamic_application_scope" (
344 PRIMARY KEY ("redirect_uri", "flow", "scope"),
345 "redirect_uri" TEXT,
346 "flow" TEXT,
347 "scope" TEXT,
348 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours' );
349 CREATE INDEX "dynamic_application_scope_redirect_uri_scope_idx" ON "dynamic_application_scope" ("redirect_uri", "flow", "scope");
350 CREATE INDEX "dynamic_application_scope_expiry_idx" ON "dynamic_application_scope" ("expiry");
352 COMMENT ON TABLE "dynamic_application_scope" IS 'Dynamic OAuth 2.0 client registration data';
354 COMMENT ON COLUMN "dynamic_application_scope"."redirect_uri" IS 'Redirection endpoint for which the registration has been done';
355 COMMENT ON COLUMN "dynamic_application_scope"."flow" IS 'OAuth 2.0 flow for which the registration has been done (see also "system_application"."flow")';
356 COMMENT ON COLUMN "dynamic_application_scope"."scope" IS 'Single scope without space characters (use multiple rows for more scopes)';
357 COMMENT ON COLUMN "dynamic_application_scope"."expiry" IS 'Expiry unless renewed';
360 CREATE TABLE "member_application" (
361 "id" SERIAL4 PRIMARY KEY,
362 UNIQUE ("system_application_id", "member_id"),
363 UNIQUE ("domain", "member_id"),
364 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
365 "system_application_id" INT4 REFERENCES "system_application" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
366 "domain" TEXT,
367 "session_id" INT8,
368 FOREIGN KEY ("member_id", "session_id") REFERENCES "session" ("member_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
369 "scope" TEXT NOT NULL,
370 CONSTRAINT "system_application_or_domain_but_not_both" CHECK (
371 ("system_application_id" NOTNULL AND "domain" ISNULL) OR
372 ("system_application_id" ISNULL AND "domain" NOTNULL) ) );
373 CREATE INDEX "member_application_member_id_idx" ON "member_application" ("member_id");
375 COMMENT ON TABLE "member_application" IS 'Application authorized by a member';
377 COMMENT ON COLUMN "member_application"."system_application_id" IS 'If set, then application is a system application';
378 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';
379 COMMENT ON COLUMN "member_application"."session_id" IS 'If set, registration ends with session';
380 COMMENT ON COLUMN "member_application"."scope" IS 'Granted scope as space-separated list of strings';
383 CREATE TYPE "token_type" AS ENUM ('authorization', 'refresh', 'access');
385 COMMENT ON TYPE "token_type" IS 'Types for entries in "token" table';
388 CREATE TABLE "token" (
389 "id" SERIAL8 PRIMARY KEY,
390 "token" TEXT NOT NULL UNIQUE,
391 "token_type" "token_type" NOT NULL,
392 "authorization_token_id" INT8 REFERENCES "token" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
393 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
394 "system_application_id" INT4 REFERENCES "system_application" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
395 "domain" TEXT,
396 FOREIGN KEY ("member_id", "domain") REFERENCES "member_application" ("member_id", "domain") ON DELETE CASCADE ON UPDATE CASCADE,
397 "session_id" INT8,
398 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"
399 "redirect_uri" TEXT,
400 "redirect_uri_explicit" BOOLEAN,
401 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
402 "expiry" TIMESTAMPTZ DEFAULT now() + '1 hour',
403 "used" BOOLEAN NOT NULL DEFAULT FALSE,
404 "scope" TEXT NOT NULL,
405 CONSTRAINT "access_token_needs_expiry"
406 CHECK ("token_type" != 'access'::"token_type" OR "expiry" NOTNULL),
407 CONSTRAINT "authorization_token_needs_redirect_uri"
408 CHECK ("token_type" != 'authorization'::"token_type" OR ("redirect_uri" NOTNULL AND "redirect_uri_explicit" NOTNULL) ) );
409 CREATE INDEX "token_member_id_idx" ON "token" ("member_id");
410 CREATE INDEX "token_authorization_token_id_idx" ON "token" ("authorization_token_id");
411 CREATE INDEX "token_expiry_idx" ON "token" ("expiry");
413 COMMENT ON TABLE "token" IS 'Issued OAuth 2.0 authorization codes and access/refresh tokens';
415 COMMENT ON COLUMN "token"."token" IS 'String secret (the actual token)';
416 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)';
417 COMMENT ON COLUMN "token"."system_application_id" IS 'If set, then application is a system application';
418 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';
419 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''';
420 COMMENT ON COLUMN "token"."redirect_uri" IS 'Authorization codes must be bound to a specific redirect URI';
421 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)';
422 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';
423 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)';
424 COMMENT ON COLUMN "token"."scope" IS 'Scope as space-separated list of strings (detached scopes are marked with ''_detached'' suffix)';
427 CREATE TABLE "token_scope" (
428 PRIMARY KEY ("token_id", "index"),
429 "token_id" INT8 REFERENCES "token" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
430 "index" INT4,
431 "scope" TEXT NOT NULL );
433 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';
436 ALTER TABLE "policy" ADD COLUMN "issue_quorum" INT4 CHECK ("issue_quorum" >= 1);
437 ALTER TABLE "policy" ADD COLUMN "initiative_quorum" INT4 CHECK ("initiative_quorum" >= 1);
439 UPDATE "policy" SET "issue_quorum" = 1 WHERE "issue_quorum_num" NOTNULL;
440 UPDATE "policy" SET "initiative_quorum" = 1;
442 ALTER TABLE "policy" ALTER COLUMN "initiative_quorum" SET NOT NULL;
444 ALTER TABLE "policy" DROP CONSTRAINT "timing";
445 ALTER TABLE "policy" DROP CONSTRAINT "issue_quorum_if_and_only_if_not_polling";
446 ALTER TABLE "policy" ADD CONSTRAINT
447 "issue_quorum_if_and_only_if_not_polling" CHECK (
448 "polling" = ("issue_quorum" ISNULL) AND
449 "polling" = ("issue_quorum_num" ISNULL) AND
450 "polling" = ("issue_quorum_den" ISNULL)
451 );
452 ALTER TABLE "policy" ADD CONSTRAINT
453 "min_admission_time_smaller_than_max_admission_time" CHECK (
454 "min_admission_time" < "max_admission_time"
455 );
456 ALTER TABLE "policy" ADD CONSTRAINT
457 "timing_null_or_not_null_constraints" CHECK (
458 ( "polling" = FALSE AND
459 "min_admission_time" NOTNULL AND "max_admission_time" NOTNULL AND
460 "discussion_time" NOTNULL AND
461 "verification_time" NOTNULL AND
462 "voting_time" NOTNULL ) OR
463 ( "polling" = TRUE AND
464 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
465 "discussion_time" NOTNULL AND
466 "verification_time" NOTNULL AND
467 "voting_time" NOTNULL ) OR
468 ( "polling" = TRUE AND
469 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
470 "discussion_time" ISNULL AND
471 "verification_time" ISNULL AND
472 "voting_time" ISNULL )
473 );
475 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"';
476 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';
477 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)';
478 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)';
479 COMMENT ON COLUMN "policy"."initiative_quorum" IS 'Absolute number of satisfied supporters to be reached by an initiative to be "admitted" for voting';
480 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
481 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
484 ALTER TABLE "unit" ADD COLUMN "location" JSONB;
486 CREATE INDEX "unit_location_idx" ON "unit" USING gist ((GeoJSON_to_ecluster("location")));
488 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege" (only active members counted)';
489 COMMENT ON COLUMN "unit"."location" IS 'Geographic location on earth as GeoJSON object indicating valid coordinates for initiatives of issues with this policy';
492 DROP INDEX "area_unit_id_idx";
493 ALTER TABLE "area" ADD UNIQUE ("unit_id", "id");
495 ALTER TABLE "area" ADD COLUMN "quorum_standard" NUMERIC NOT NULL DEFAULT 2 CHECK ("quorum_standard" >= 0);
496 ALTER TABLE "area" ADD COLUMN "quorum_issues" NUMERIC NOT NULL DEFAULT 1 CHECK ("quorum_issues" > 0);
497 ALTER TABLE "area" ADD COLUMN "quorum_time" INTERVAL NOT NULL DEFAULT '1 day' CHECK ("quorum_time" > '0'::INTERVAL);
498 ALTER TABLE "area" ADD COLUMN "quorum_exponent" NUMERIC NOT NULL DEFAULT 0.5 CHECK ("quorum_exponent" BETWEEN 0 AND 1);
499 ALTER TABLE "area" ADD COLUMN "quorum_factor" NUMERIC NOT NULL DEFAULT 2 CHECK ("quorum_factor" >= 1);
500 ALTER TABLE "area" ADD COLUMN "quorum_den" INT4 CHECK ("quorum_den" > 0);
501 ALTER TABLE "area" ADD COLUMN "issue_quorum" INT4;
502 ALTER TABLE "area" ADD COLUMN "location" JSONB;
504 ALTER TABLE "area" DROP COLUMN "direct_member_count";
505 ALTER TABLE "area" DROP COLUMN "member_weight";
507 CREATE INDEX "area_location_idx" ON "area" USING gist ((GeoJSON_to_ecluster("location")));
509 COMMENT ON COLUMN "area"."quorum_standard" IS 'Parameter for dynamic issue quorum: default quorum';
510 COMMENT ON COLUMN "area"."quorum_issues" IS 'Parameter for dynamic issue quorum: number of open issues for default quorum';
511 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)';
512 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';
513 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';
514 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)';
515 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"';
516 COMMENT ON COLUMN "area"."external_reference" IS 'Opaque data field to store an external reference';
517 COMMENT ON COLUMN "area"."location" IS 'Geographic location on earth as GeoJSON object indicating valid coordinates for initiatives of issues with this policy';
520 CREATE TABLE "snapshot" (
521 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "issue"
522 "id" SERIAL8 PRIMARY KEY,
523 "calculated" TIMESTAMPTZ NOT NULL DEFAULT now(),
524 "population" INT4,
525 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
526 "issue_id" INT4 ); -- NOTE: following (cyclic) reference is added later through ALTER command: REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE
528 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';
531 CREATE TABLE "snapshot_population" (
532 PRIMARY KEY ("snapshot_id", "member_id"),
533 "snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
534 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE );
536 COMMENT ON TABLE "snapshot_population" IS 'Members with voting right relevant for a snapshot';
539 ALTER TABLE "issue" ADD UNIQUE ("area_id", "id");
540 DROP INDEX "issue_area_id_idx";
541 ALTER TABLE "issue" ADD UNIQUE ("policy_id", "id");
542 DROP INDEX "issue_policy_id_idx";
544 ALTER TABLE "issue" RENAME COLUMN "snapshot" TO "calculated";
546 ALTER TABLE "issue" ADD COLUMN "latest_snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
547 ALTER TABLE "issue" ADD COLUMN "admission_snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE SET NULL ON UPDATE CASCADE;
548 ALTER TABLE "issue" ADD COLUMN "half_freeze_snapshot_id" INT8;
549 ALTER TABLE "issue" ADD COLUMN "full_freeze_snapshot_id" INT8;
551 ALTER TABLE "issue" ADD FOREIGN KEY ("id", "half_freeze_snapshot_id")
552 REFERENCES "snapshot" ("issue_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE;
553 ALTER TABLE "issue" ADD FOREIGN KEY ("id", "full_freeze_snapshot_id")
554 REFERENCES "snapshot" ("issue_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE;
556 ALTER TABLE "issue" DROP CONSTRAINT "last_snapshot_on_full_freeze";
557 ALTER TABLE "issue" DROP CONSTRAINT "freeze_requires_snapshot";
558 ALTER TABLE "issue" DROP CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event";
560 CREATE INDEX "issue_state_idx" ON "issue" ("state");
561 CREATE INDEX "issue_latest_snapshot_id" ON "issue" ("latest_snapshot_id");
562 CREATE INDEX "issue_admission_snapshot_id" ON "issue" ("admission_snapshot_id");
563 CREATE INDEX "issue_half_freeze_snapshot_id" ON "issue" ("half_freeze_snapshot_id");
564 CREATE INDEX "issue_full_freeze_snapshot_id" ON "issue" ("full_freeze_snapshot_id");
566 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")';
567 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")';
568 COMMENT ON COLUMN "issue"."latest_snapshot_id" IS 'Snapshot id of most recent snapshot';
569 COMMENT ON COLUMN "issue"."admission_snapshot_id" IS 'Snapshot id when issue as accepted or canceled in admission phase';
570 COMMENT ON COLUMN "issue"."half_freeze_snapshot_id" IS 'Snapshot id at end of discussion phase';
571 COMMENT ON COLUMN "issue"."full_freeze_snapshot_id" IS 'Snapshot id at end of verification phase';
572 COMMENT ON COLUMN "issue"."population" IS 'Count of members in "snapshot_population" table with "snapshot_id" equal to "issue"."latest_snapshot_id"';
575 ALTER TABLE "snapshot" ADD FOREIGN KEY ("issue_id") REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
578 ALTER TABLE "initiative" DROP CONSTRAINT "initiative_suggested_initiative_id_fkey";
579 ALTER TABLE "initiative" ADD FOREIGN KEY ("suggested_initiative_id") REFERENCES "initiative" ("id") ON DELETE SET NULL ON UPDATE CASCADE;
581 ALTER TABLE "initiative" ADD COLUMN "location" JSONB;
582 ALTER TABLE "initiative" ADD COLUMN "draft_text_search_data" TSVECTOR;
584 CREATE INDEX "initiative_location_idx" ON "initiative" USING gist ((GeoJSON_to_ecluster("location")));
585 CREATE INDEX "initiative_draft_text_search_data_idx" ON "initiative" USING gin ("draft_text_search_data");
587 COMMENT ON COLUMN "initiative"."location" IS 'Geographic location of initiative as GeoJSON object (automatically copied from most recent draft)';
590 ALTER TABLE "draft" ADD COLUMN "location" JSONB;
592 CREATE INDEX "draft_location_idx" ON "draft" USING gist ((GeoJSON_to_ecluster("location")));
594 COMMENT ON COLUMN "draft"."location" IS 'Geographic location of initiative as GeoJSON object (automatically copied to "initiative" table if draft is most recent)';
597 ALTER TABLE "suggestion" ADD COLUMN "location" JSONB;
599 CREATE INDEX "suggestion_location_idx" ON "suggestion" USING gist ((GeoJSON_to_ecluster("location")));
601 COMMENT ON COLUMN "suggestion"."location" IS 'Geographic location of suggestion as GeoJSON object';
604 CREATE TABLE "temporary_suggestion_counts" (
605 "id" INT8 PRIMARY KEY, -- NOTE: no referential integrity due to performance/locking issues; REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
606 "minus2_unfulfilled_count" INT4 NOT NULL,
607 "minus2_fulfilled_count" INT4 NOT NULL,
608 "minus1_unfulfilled_count" INT4 NOT NULL,
609 "minus1_fulfilled_count" INT4 NOT NULL,
610 "plus1_unfulfilled_count" INT4 NOT NULL,
611 "plus1_fulfilled_count" INT4 NOT NULL,
612 "plus2_unfulfilled_count" INT4 NOT NULL,
613 "plus2_fulfilled_count" INT4 NOT NULL );
615 COMMENT ON TABLE "temporary_suggestion_counts" IS 'Holds certain calculated values (suggestion counts) temporarily until they can be copied into table "suggestion"';
617 COMMENT ON COLUMN "temporary_suggestion_counts"."id" IS 'References "suggestion" ("id") but has no referential integrity trigger associated, due to performance/locking issues';
620 ALTER TABLE "interest" DROP CONSTRAINT "interest_member_id_fkey";
621 ALTER TABLE "interest" ADD FOREIGN KEY ("member_id") REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
624 ALTER TABLE "initiator" DROP CONSTRAINT "initiator_member_id_fkey";
625 ALTER TABLE "initiator" ADD FOREIGN KEY ("member_id") REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
628 ALTER TABLE "delegation" DROP CONSTRAINT "delegation_trustee_id_fkey";
629 ALTER TABLE "delegation" ADD FOREIGN KEY ("trustee_id") REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
632 CREATE TABLE "snapshot_issue" (
633 PRIMARY KEY ("snapshot_id", "issue_id"),
634 "snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
635 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
636 CREATE INDEX "snapshot_issue_issue_id_idx" ON "snapshot_issue" ("issue_id");
638 COMMENT ON TABLE "snapshot_issue" IS 'List of issues included in a snapshot';
640 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.';
643 ALTER TABLE "direct_interest_snapshot" RENAME TO "direct_interest_snapshot_old"; -- TODO!
644 ALTER INDEX "direct_interest_snapshot_pkey" RENAME TO "direct_interest_snapshot_old_pkey";
645 ALTER INDEX "direct_interest_snapshot_member_id_idx" RENAME TO "direct_interest_snapshot_old_member_id_idx";
647 ALTER TABLE "delegating_interest_snapshot" RENAME TO "delegating_interest_snapshot_old"; -- TODO!
648 ALTER INDEX "delegating_interest_snapshot_pkey" RENAME TO "delegating_interest_snapshot_old_pkey";
649 ALTER INDEX "delegating_interest_snapshot_member_id_idx" RENAME TO "delegating_interest_snapshot_old_member_id_idx";
651 ALTER TABLE "direct_supporter_snapshot" RENAME TO "direct_supporter_snapshot_old"; -- TODO!
652 ALTER INDEX "direct_supporter_snapshot_pkey" RENAME TO "direct_supporter_snapshot_old_pkey";
653 ALTER INDEX "direct_supporter_snapshot_member_id_idx" RENAME TO "direct_supporter_snapshot_old_member_id_idx";
656 CREATE TABLE "direct_interest_snapshot" (
657 PRIMARY KEY ("snapshot_id", "issue_id", "member_id"),
658 "snapshot_id" INT8,
659 "issue_id" INT4,
660 FOREIGN KEY ("snapshot_id", "issue_id")
661 REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
662 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
663 "weight" INT4 );
664 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
666 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';
668 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
671 CREATE TABLE "delegating_interest_snapshot" (
672 PRIMARY KEY ("snapshot_id", "issue_id", "member_id"),
673 "snapshot_id" INT8,
674 "issue_id" INT4,
675 FOREIGN KEY ("snapshot_id", "issue_id")
676 REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
677 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
678 "weight" INT4,
679 "scope" "delegation_scope" NOT NULL,
680 "delegate_member_ids" INT4[] NOT NULL );
681 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
683 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';
685 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
686 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
687 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"';
690 CREATE TABLE "direct_supporter_snapshot" (
691 PRIMARY KEY ("snapshot_id", "initiative_id", "member_id"),
692 "snapshot_id" INT8,
693 "issue_id" INT4 NOT NULL,
694 FOREIGN KEY ("snapshot_id", "issue_id")
695 REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
696 "initiative_id" INT4,
697 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
698 "draft_id" INT8 NOT NULL,
699 "informed" BOOLEAN NOT NULL,
700 "satisfied" BOOLEAN NOT NULL,
701 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
702 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
703 FOREIGN KEY ("snapshot_id", "issue_id", "member_id") REFERENCES "direct_interest_snapshot" ("snapshot_id", "issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
704 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
706 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';
708 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';
709 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
710 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
713 ALTER TABLE "non_voter" DROP CONSTRAINT "non_voter_pkey";
714 DROP INDEX "non_voter_member_id_idx";
716 ALTER TABLE "non_voter" ADD PRIMARY KEY ("member_id", "issue_id");
717 CREATE INDEX "non_voter_issue_id_idx" ON "non_voter" ("issue_id");
720 INSERT INTO "member_useterms" ("member_id", "accepted", "contract_identifier")
721 SELECT
722 "member_id",
723 regexp_replace("value", '^accepted at ', '')::TIMESTAMPTZ AS "accepted",
724 regexp_replace("key", '^use_terms_checkbox_', '') AS "contract_identifier"
725 FROM "setting" WHERE "key" LIKE 'use_terms_checkbox_%';
728 DROP TABLE "setting";
729 DROP TABLE "setting_map";
730 DROP TABLE "member_relation_setting";
731 DROP TABLE "unit_setting";
732 DROP TABLE "area_setting";
733 DROP TABLE "initiative_setting";
734 DROP TABLE "suggestion_setting";
737 ALTER TABLE "event" ADD COLUMN "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
738 ALTER TABLE "event" ADD COLUMN "scope" "delegation_scope";
739 ALTER TABLE "event" ADD COLUMN "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
740 ALTER TABLE "event" ADD COLUMN "area_id" INT4;
741 ALTER TABLE "event" ADD COLUMN "policy_id" INT4 REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
742 ALTER TABLE "event" ADD COLUMN "boolean_value" BOOLEAN;
743 ALTER TABLE "event" ADD COLUMN "numeric_value" INT4;
744 ALTER TABLE "event" ADD COLUMN "text_value" TEXT;
745 ALTER TABLE "event" ADD COLUMN "old_text_value" TEXT;
747 ALTER TABLE "event" ADD FOREIGN KEY ("unit_id", "area_id") REFERENCES "area" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE;
748 ALTER TABLE "event" ADD FOREIGN KEY ("area_id", "issue_id") REFERENCES "issue" ("area_id", "id") ON DELETE CASCADE ON UPDATE CASCADE;
749 ALTER TABLE "event" ADD FOREIGN KEY ("policy_id", "issue_id") REFERENCES "issue" ("policy_id", "id") ON DELETE CASCADE ON UPDATE CASCADE;
751 ALTER TABLE "event" DROP CONSTRAINT "event_initiative_id_fkey1";
752 ALTER TABLE "event" DROP CONSTRAINT "null_constr_for_issue_state_changed";
753 ALTER TABLE "event" DROP CONSTRAINT "null_constr_for_initiative_creation_or_revocation_or_new_draft";
754 ALTER TABLE "event" DROP CONSTRAINT "null_constr_for_suggestion_creation";
756 UPDATE "event" SET
757 "unit_id" = "area"."unit_id",
758 "area_id" = "issue"."area_id",
759 "policy_id" = "issue"."policy_id"
760 FROM "issue", "area"
761 WHERE "issue"."id" = "event"."issue_id" AND "area"."id" = "issue"."area_id";
763 ALTER TABLE "event" ADD CONSTRAINT "constr_for_issue_state_changed" CHECK (
764 "event" != 'issue_state_changed' OR (
765 "member_id" ISNULL AND
766 "other_member_id" ISNULL AND
767 "scope" ISNULL AND
768 "unit_id" NOTNULL AND
769 "area_id" NOTNULL AND
770 "policy_id" NOTNULL AND
771 "issue_id" NOTNULL AND
772 "state" NOTNULL AND
773 "initiative_id" ISNULL AND
774 "draft_id" ISNULL AND
775 "suggestion_id" ISNULL AND
776 "boolean_value" ISNULL AND
777 "numeric_value" ISNULL AND
778 "text_value" ISNULL AND
779 "old_text_value" ISNULL ));
780 ALTER TABLE "event" ADD CONSTRAINT "constr_for_initiative_creation_or_revocation_or_new_draft" CHECK (
781 "event" NOT IN (
782 'initiative_created_in_new_issue',
783 'initiative_created_in_existing_issue',
784 'initiative_revoked',
785 'new_draft_created'
786 ) OR (
787 "member_id" NOTNULL AND
788 "other_member_id" ISNULL AND
789 "scope" ISNULL AND
790 "unit_id" NOTNULL AND
791 "area_id" NOTNULL AND
792 "policy_id" NOTNULL AND
793 "issue_id" NOTNULL AND
794 "state" NOTNULL AND
795 "initiative_id" NOTNULL AND
796 "draft_id" NOTNULL AND
797 "suggestion_id" ISNULL AND
798 "boolean_value" ISNULL AND
799 "numeric_value" ISNULL AND
800 "text_value" ISNULL AND
801 "old_text_value" ISNULL ));
802 ALTER TABLE "event" ADD CONSTRAINT "constr_for_suggestion_creation" CHECK (
803 "event" != 'suggestion_created' OR (
804 "member_id" NOTNULL AND
805 "other_member_id" ISNULL AND
806 "scope" ISNULL AND
807 "unit_id" NOTNULL AND
808 "area_id" NOTNULL AND
809 "policy_id" NOTNULL AND
810 "issue_id" NOTNULL AND
811 "state" NOTNULL AND
812 "initiative_id" NOTNULL AND
813 "draft_id" ISNULL AND
814 "suggestion_id" NOTNULL AND
815 "boolean_value" ISNULL AND
816 "numeric_value" ISNULL AND
817 "text_value" ISNULL AND
818 "old_text_value" ISNULL ));
819 ALTER TABLE "event" ADD CONSTRAINT "constr_for_suggestion_removal" CHECK (
820 "event" != 'suggestion_deleted' OR (
821 "member_id" ISNULL AND
822 "other_member_id" ISNULL AND
823 "scope" ISNULL AND
824 "unit_id" NOTNULL AND
825 "area_id" NOTNULL AND
826 "policy_id" NOTNULL AND
827 "issue_id" NOTNULL AND
828 "state" NOTNULL AND
829 "initiative_id" NOTNULL AND
830 "draft_id" ISNULL AND
831 "suggestion_id" NOTNULL AND
832 "boolean_value" ISNULL AND
833 "numeric_value" ISNULL AND
834 "text_value" ISNULL AND
835 "old_text_value" ISNULL ));
836 ALTER TABLE "event" ADD CONSTRAINT "constr_for_value_less_member_event" CHECK (
837 "event" NOT IN (
838 'member_activated',
839 'member_deleted',
840 'member_profile_updated',
841 'member_image_updated'
842 ) OR (
843 "member_id" NOTNULL AND
844 "other_member_id" ISNULL AND
845 "scope" ISNULL AND
846 "unit_id" ISNULL AND
847 "area_id" ISNULL AND
848 "policy_id" ISNULL AND
849 "issue_id" ISNULL AND
850 "state" ISNULL AND
851 "initiative_id" ISNULL AND
852 "draft_id" ISNULL AND
853 "suggestion_id" ISNULL AND
854 "boolean_value" ISNULL AND
855 "numeric_value" ISNULL AND
856 "text_value" ISNULL AND
857 "old_text_value" ISNULL ));
858 ALTER TABLE "event" ADD CONSTRAINT "constr_for_member_active" CHECK (
859 "event" != 'member_active' OR (
860 "member_id" NOTNULL AND
861 "other_member_id" ISNULL AND
862 "scope" ISNULL AND
863 "unit_id" ISNULL AND
864 "area_id" ISNULL AND
865 "policy_id" ISNULL AND
866 "issue_id" ISNULL AND
867 "state" ISNULL AND
868 "initiative_id" ISNULL AND
869 "draft_id" ISNULL AND
870 "suggestion_id" ISNULL AND
871 "boolean_value" NOTNULL AND
872 "numeric_value" ISNULL AND
873 "text_value" ISNULL AND
874 "old_text_value" ISNULL ));
875 ALTER TABLE "event" ADD CONSTRAINT "constr_for_member_name_updated" CHECK (
876 "event" != 'member_name_updated' OR (
877 "member_id" NOTNULL AND
878 "other_member_id" ISNULL AND
879 "scope" ISNULL AND
880 "unit_id" ISNULL AND
881 "area_id" ISNULL AND
882 "policy_id" ISNULL AND
883 "issue_id" ISNULL AND
884 "state" ISNULL AND
885 "initiative_id" ISNULL AND
886 "draft_id" ISNULL AND
887 "suggestion_id" ISNULL AND
888 "boolean_value" ISNULL AND
889 "numeric_value" ISNULL AND
890 "text_value" NOTNULL AND
891 "old_text_value" NOTNULL ));
892 ALTER TABLE "event" ADD CONSTRAINT "constr_for_interest" CHECK (
893 "event" != 'interest' OR (
894 "member_id" NOTNULL AND
895 "other_member_id" ISNULL AND
896 "scope" ISNULL AND
897 "unit_id" NOTNULL AND
898 "area_id" NOTNULL AND
899 "policy_id" NOTNULL AND
900 "issue_id" NOTNULL AND
901 "state" NOTNULL AND
902 "initiative_id" ISNULL AND
903 "draft_id" ISNULL AND
904 "suggestion_id" ISNULL AND
905 "boolean_value" NOTNULL AND
906 "numeric_value" ISNULL AND
907 "text_value" ISNULL AND
908 "old_text_value" ISNULL ));
909 ALTER TABLE "event" ADD CONSTRAINT "constr_for_initiator" CHECK (
910 "event" != 'initiator' OR (
911 "member_id" NOTNULL AND
912 "other_member_id" ISNULL AND
913 "scope" ISNULL AND
914 "unit_id" NOTNULL AND
915 "area_id" NOTNULL AND
916 "policy_id" NOTNULL AND
917 "issue_id" NOTNULL AND
918 "state" NOTNULL AND
919 "initiative_id" NOTNULL AND
920 "draft_id" ISNULL AND
921 "suggestion_id" ISNULL AND
922 "boolean_value" NOTNULL AND
923 "numeric_value" ISNULL AND
924 "text_value" ISNULL AND
925 "old_text_value" ISNULL ));
926 ALTER TABLE "event" ADD CONSTRAINT "constr_for_support" CHECK (
927 "event" != 'support' OR (
928 "member_id" NOTNULL AND
929 "other_member_id" ISNULL AND
930 "scope" ISNULL AND
931 "unit_id" NOTNULL AND
932 "area_id" NOTNULL AND
933 "policy_id" NOTNULL AND
934 "issue_id" NOTNULL AND
935 "state" NOTNULL AND
936 "initiative_id" NOTNULL AND
937 ("draft_id" NOTNULL) = ("boolean_value" = TRUE) AND
938 "suggestion_id" ISNULL AND
939 "boolean_value" NOTNULL AND
940 "numeric_value" ISNULL AND
941 "text_value" ISNULL AND
942 "old_text_value" ISNULL ));
943 ALTER TABLE "event" ADD CONSTRAINT "constr_for_support_updated" CHECK (
944 "event" != 'support_updated' OR (
945 "member_id" NOTNULL AND
946 "other_member_id" ISNULL AND
947 "scope" ISNULL AND
948 "unit_id" NOTNULL AND
949 "area_id" NOTNULL AND
950 "policy_id" NOTNULL AND
951 "issue_id" NOTNULL AND
952 "state" NOTNULL AND
953 "initiative_id" NOTNULL AND
954 "draft_id" NOTNULL AND
955 "suggestion_id" ISNULL AND
956 "boolean_value" ISNULL AND
957 "numeric_value" ISNULL AND
958 "text_value" ISNULL AND
959 "old_text_value" ISNULL ));
960 ALTER TABLE "event" ADD CONSTRAINT "constr_for_suggestion_rated" CHECK (
961 "event" != 'suggestion_rated' OR (
962 "member_id" NOTNULL AND
963 "other_member_id" ISNULL AND
964 "scope" ISNULL AND
965 "unit_id" NOTNULL AND
966 "area_id" NOTNULL AND
967 "policy_id" NOTNULL AND
968 "issue_id" NOTNULL AND
969 "state" NOTNULL AND
970 "initiative_id" NOTNULL AND
971 "draft_id" ISNULL AND
972 "suggestion_id" NOTNULL AND
973 ("boolean_value" NOTNULL) = ("numeric_value" != 0) AND
974 "numeric_value" NOTNULL AND
975 "numeric_value" IN (-2, -1, 0, 1, 2) AND
976 "text_value" ISNULL AND
977 "old_text_value" ISNULL ));
978 ALTER TABLE "event" ADD CONSTRAINT "constr_for_delegation" CHECK (
979 "event" != 'delegation' OR (
980 "member_id" NOTNULL AND
981 ("other_member_id" NOTNULL) OR ("boolean_value" = FALSE) AND
982 "scope" NOTNULL AND
983 "unit_id" NOTNULL AND
984 ("area_id" NOTNULL) = ("scope" != 'unit'::"delegation_scope") AND
985 "policy_id" ISNULL AND
986 ("issue_id" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
987 ("state" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
988 "initiative_id" ISNULL AND
989 "draft_id" ISNULL AND
990 "suggestion_id" ISNULL AND
991 "boolean_value" NOTNULL AND
992 "numeric_value" ISNULL AND
993 "text_value" ISNULL AND
994 "old_text_value" ISNULL ));
995 ALTER TABLE "event" ADD CONSTRAINT "constr_for_contact" CHECK (
996 "event" != 'contact' OR (
997 "member_id" NOTNULL AND
998 "other_member_id" NOTNULL AND
999 "scope" ISNULL AND
1000 "unit_id" ISNULL AND
1001 "area_id" ISNULL AND
1002 "policy_id" ISNULL AND
1003 "issue_id" ISNULL AND
1004 "state" ISNULL AND
1005 "initiative_id" ISNULL AND
1006 "draft_id" ISNULL AND
1007 "suggestion_id" ISNULL AND
1008 "boolean_value" NOTNULL AND
1009 "numeric_value" ISNULL AND
1010 "text_value" ISNULL AND
1011 "old_text_value" ISNULL ));
1014 ALTER TABLE "notification_event_sent" RENAME TO "event_processed";
1015 ALTER INDEX "notification_event_sent_singleton_idx" RENAME TO "event_processed_singleton_idx";
1017 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)';
1018 COMMENT ON INDEX "event_processed_singleton_idx" IS 'This index ensures that "event_processed" only contains one row maximum.';
1021 CREATE FUNCTION "write_event_unit_trigger"()
1022 RETURNS TRIGGER
1023 LANGUAGE 'plpgsql' VOLATILE AS $$
1024 DECLARE
1025 "event_v" "event_type";
1026 BEGIN
1027 IF TG_OP = 'UPDATE' THEN
1028 IF OLD."active" = FALSE AND NEW."active" = FALSE THEN
1029 RETURN NULL;
1030 --ELSIF OLD."active" = FALSE AND NEW."active" = TRUE THEN
1031 -- "event_v" := 'unit_created';
1032 --ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN
1033 -- "event_v" := 'unit_deleted';
1034 ELSIF OLD != NEW THEN
1035 "event_v" := 'unit_updated';
1036 ELSE
1037 RETURN NULL;
1038 END IF;
1039 ELSE
1040 "event_v" := 'unit_created';
1041 END IF;
1042 INSERT INTO "event" ("event", "unit_id") VALUES ("event_v", NEW."id");
1043 RETURN NULL;
1044 END;
1045 $$;
1047 CREATE TRIGGER "write_event_unit" AFTER INSERT OR UPDATE ON "unit"
1048 FOR EACH ROW EXECUTE PROCEDURE "write_event_unit_trigger"();
1050 COMMENT ON FUNCTION "write_event_unit_trigger"() IS 'Implementation of trigger "write_event_unit" on table "unit"';
1051 COMMENT ON TRIGGER "write_event_unit" ON "unit" IS 'Create entry in "event" table on new or changed/disabled units';
1054 CREATE FUNCTION "write_event_area_trigger"()
1055 RETURNS TRIGGER
1056 LANGUAGE 'plpgsql' VOLATILE AS $$
1057 DECLARE
1058 "event_v" "event_type";
1059 BEGIN
1060 IF TG_OP = 'UPDATE' THEN
1061 IF OLD."active" = FALSE AND NEW."active" = FALSE THEN
1062 RETURN NULL;
1063 --ELSIF OLD."active" = FALSE AND NEW."active" = TRUE THEN
1064 -- "event_v" := 'area_created';
1065 --ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN
1066 -- "event_v" := 'area_deleted';
1067 ELSIF OLD != NEW THEN
1068 "event_v" := 'area_updated';
1069 ELSE
1070 RETURN NULL;
1071 END IF;
1072 ELSE
1073 "event_v" := 'area_created';
1074 END IF;
1075 INSERT INTO "event" ("event", "area_id") VALUES ("event_v", NEW."id");
1076 RETURN NULL;
1077 END;
1078 $$;
1080 CREATE TRIGGER "write_event_area" AFTER INSERT OR UPDATE ON "area"
1081 FOR EACH ROW EXECUTE PROCEDURE "write_event_area_trigger"();
1083 COMMENT ON FUNCTION "write_event_area_trigger"() IS 'Implementation of trigger "write_event_area" on table "area"';
1084 COMMENT ON TRIGGER "write_event_area" ON "area" IS 'Create entry in "event" table on new or changed/disabled areas';
1087 CREATE FUNCTION "write_event_policy_trigger"()
1088 RETURNS TRIGGER
1089 LANGUAGE 'plpgsql' VOLATILE AS $$
1090 DECLARE
1091 "event_v" "event_type";
1092 BEGIN
1093 IF TG_OP = 'UPDATE' THEN
1094 IF OLD."active" = FALSE AND NEW."active" = FALSE THEN
1095 RETURN NULL;
1096 --ELSIF OLD."active" = FALSE AND NEW."active" = TRUE THEN
1097 -- "event_v" := 'policy_created';
1098 --ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN
1099 -- "event_v" := 'policy_deleted';
1100 ELSIF OLD != NEW THEN
1101 "event_v" := 'policy_updated';
1102 ELSE
1103 RETURN NULL;
1104 END IF;
1105 ELSE
1106 "event_v" := 'policy_created';
1107 END IF;
1108 INSERT INTO "event" ("event", "policy_id") VALUES ("event_v", NEW."id");
1109 RETURN NULL;
1110 END;
1111 $$;
1113 CREATE TRIGGER "write_event_policy" AFTER INSERT OR UPDATE ON "policy"
1114 FOR EACH ROW EXECUTE PROCEDURE "write_event_policy_trigger"();
1116 COMMENT ON FUNCTION "write_event_policy_trigger"() IS 'Implementation of trigger "write_event_policy" on table "policy"';
1117 COMMENT ON TRIGGER "write_event_policy" ON "policy" IS 'Create entry in "event" table on new or changed/disabled policies';
1120 CREATE OR REPLACE FUNCTION "write_event_issue_state_changed_trigger"()
1121 RETURNS TRIGGER
1122 LANGUAGE 'plpgsql' VOLATILE AS $$
1123 DECLARE
1124 "area_row" "area"%ROWTYPE;
1125 BEGIN
1126 IF NEW."state" != OLD."state" THEN
1127 SELECT * INTO "area_row" FROM "area" WHERE "id" = NEW."area_id"
1128 FOR SHARE;
1129 INSERT INTO "event" (
1130 "event",
1131 "unit_id", "area_id", "policy_id", "issue_id", "state"
1132 ) VALUES (
1133 'issue_state_changed',
1134 "area_row"."unit_id", NEW."area_id", NEW."policy_id",
1135 NEW."id", NEW."state"
1136 );
1137 END IF;
1138 RETURN NULL;
1139 END;
1140 $$;
1143 CREATE OR REPLACE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1144 RETURNS TRIGGER
1145 LANGUAGE 'plpgsql' VOLATILE AS $$
1146 DECLARE
1147 "initiative_row" "initiative"%ROWTYPE;
1148 "issue_row" "issue"%ROWTYPE;
1149 "area_row" "area"%ROWTYPE;
1150 "event_v" "event_type";
1151 BEGIN
1152 SELECT * INTO "initiative_row" FROM "initiative"
1153 WHERE "id" = NEW."initiative_id" FOR SHARE;
1154 SELECT * INTO "issue_row" FROM "issue"
1155 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1156 SELECT * INTO "area_row" FROM "area"
1157 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1158 IF EXISTS (
1159 SELECT NULL FROM "draft"
1160 WHERE "initiative_id" = NEW."initiative_id" AND "id" != NEW."id"
1161 FOR SHARE
1162 ) THEN
1163 "event_v" := 'new_draft_created';
1164 ELSE
1165 IF EXISTS (
1166 SELECT NULL FROM "initiative"
1167 WHERE "issue_id" = "initiative_row"."issue_id"
1168 AND "id" != "initiative_row"."id"
1169 FOR SHARE
1170 ) THEN
1171 "event_v" := 'initiative_created_in_existing_issue';
1172 ELSE
1173 "event_v" := 'initiative_created_in_new_issue';
1174 END IF;
1175 END IF;
1176 INSERT INTO "event" (
1177 "event", "member_id",
1178 "unit_id", "area_id", "policy_id", "issue_id", "state",
1179 "initiative_id", "draft_id"
1180 ) VALUES (
1181 "event_v", NEW."author_id",
1182 "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",
1183 "initiative_row"."issue_id", "issue_row"."state",
1184 NEW."initiative_id", NEW."id"
1185 );
1186 RETURN NULL;
1187 END;
1188 $$;
1191 CREATE OR REPLACE FUNCTION "write_event_initiative_revoked_trigger"()
1192 RETURNS TRIGGER
1193 LANGUAGE 'plpgsql' VOLATILE AS $$
1194 DECLARE
1195 "issue_row" "issue"%ROWTYPE;
1196 "area_row" "area"%ROWTYPE;
1197 "draft_id_v" "draft"."id"%TYPE;
1198 BEGIN
1199 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1200 SELECT * INTO "issue_row" FROM "issue"
1201 WHERE "id" = NEW."issue_id" FOR SHARE;
1202 SELECT * INTO "area_row" FROM "area"
1203 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1204 SELECT "id" INTO "draft_id_v" FROM "current_draft"
1205 WHERE "initiative_id" = NEW."id" FOR SHARE;
1206 INSERT INTO "event" (
1207 "event", "member_id",
1208 "unit_id", "area_id", "policy_id", "issue_id", "state",
1209 "initiative_id", "draft_id"
1210 ) VALUES (
1211 'initiative_revoked', NEW."revoked_by_member_id",
1212 "area_row"."unit_id", "issue_row"."area_id",
1213 "issue_row"."policy_id",
1214 NEW."issue_id", "issue_row"."state",
1215 NEW."id", "draft_id_v"
1216 );
1217 END IF;
1218 RETURN NULL;
1219 END;
1220 $$;
1223 CREATE OR REPLACE FUNCTION "write_event_suggestion_created_trigger"()
1224 RETURNS TRIGGER
1225 LANGUAGE 'plpgsql' VOLATILE AS $$
1226 DECLARE
1227 "initiative_row" "initiative"%ROWTYPE;
1228 "issue_row" "issue"%ROWTYPE;
1229 "area_row" "area"%ROWTYPE;
1230 BEGIN
1231 SELECT * INTO "initiative_row" FROM "initiative"
1232 WHERE "id" = NEW."initiative_id" FOR SHARE;
1233 SELECT * INTO "issue_row" FROM "issue"
1234 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1235 SELECT * INTO "area_row" FROM "area"
1236 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1237 INSERT INTO "event" (
1238 "event", "member_id",
1239 "unit_id", "area_id", "policy_id", "issue_id", "state",
1240 "initiative_id", "suggestion_id"
1241 ) VALUES (
1242 'suggestion_created', NEW."author_id",
1243 "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",
1244 "initiative_row"."issue_id", "issue_row"."state",
1245 NEW."initiative_id", NEW."id"
1246 );
1247 RETURN NULL;
1248 END;
1249 $$;
1252 CREATE FUNCTION "write_event_suggestion_removed_trigger"()
1253 RETURNS TRIGGER
1254 LANGUAGE 'plpgsql' VOLATILE AS $$
1255 DECLARE
1256 "initiative_row" "initiative"%ROWTYPE;
1257 "issue_row" "issue"%ROWTYPE;
1258 "area_row" "area"%ROWTYPE;
1259 BEGIN
1260 SELECT * INTO "initiative_row" FROM "initiative"
1261 WHERE "id" = OLD."initiative_id" FOR SHARE;
1262 IF "initiative_row"."id" NOTNULL THEN
1263 SELECT * INTO "issue_row" FROM "issue"
1264 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1265 SELECT * INTO "area_row" FROM "area"
1266 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1267 INSERT INTO "event" (
1268 "event",
1269 "unit_id", "area_id", "policy_id", "issue_id", "state",
1270 "initiative_id", "suggestion_id"
1271 ) VALUES (
1272 'suggestion_deleted',
1273 "area_row"."unit_id", "issue_row"."area_id",
1274 "issue_row"."policy_id",
1275 "initiative_row"."issue_id", "issue_row"."state",
1276 OLD."initiative_id", OLD."id"
1277 );
1278 END IF;
1279 RETURN NULL;
1280 END;
1281 $$;
1283 CREATE TRIGGER "write_event_suggestion_removed"
1284 AFTER DELETE ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1285 "write_event_suggestion_removed_trigger"();
1287 COMMENT ON FUNCTION "write_event_suggestion_removed_trigger"() IS 'Implementation of trigger "write_event_suggestion_removed" on table "issue"';
1288 COMMENT ON TRIGGER "write_event_suggestion_removed" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1291 CREATE FUNCTION "write_event_member_trigger"()
1292 RETURNS TRIGGER
1293 LANGUAGE 'plpgsql' VOLATILE AS $$
1294 BEGIN
1295 IF TG_OP = 'INSERT' THEN
1296 IF NEW."activated" NOTNULL AND NEW."deleted" ISNULL THEN
1297 INSERT INTO "event" ("event", "member_id")
1298 VALUES ('member_activated', NEW."id");
1299 END IF;
1300 IF NEW."active" THEN
1301 INSERT INTO "event" ("event", "member_id", "boolean_value")
1302 VALUES ('member_active', NEW."id", TRUE);
1303 END IF;
1304 ELSIF TG_OP = 'UPDATE' THEN
1305 IF OLD."id" != NEW."id" THEN
1306 RAISE EXCEPTION 'Cannot change member ID';
1307 END IF;
1308 IF
1309 (OLD."activated" ISNULL OR OLD."deleted" NOTNULL) AND
1310 NEW."activated" NOTNULL AND NEW."deleted" ISNULL
1311 THEN
1312 INSERT INTO "event" ("event", "member_id")
1313 VALUES ('member_activated', NEW."id");
1314 END IF;
1315 IF OLD."active" != NEW."active" THEN
1316 INSERT INTO "event" ("event", "member_id", "boolean_value") VALUES (
1317 'member_active', NEW."id", NEW."active"
1318 );
1319 END IF;
1320 IF OLD."name" != NEW."name" THEN
1321 INSERT INTO "event" (
1322 "event", "member_id", "text_value", "old_text_value"
1323 ) VALUES (
1324 'member_name_updated', NEW."id", NEW."name", OLD."name"
1325 );
1326 END IF;
1327 IF
1328 OLD."activated" NOTNULL AND OLD."deleted" ISNULL AND
1329 (NEW."activated" ISNULL OR NEW."deleted" NOTNULL)
1330 THEN
1331 INSERT INTO "event" ("event", "member_id")
1332 VALUES ('member_deleted', NEW."id");
1333 END IF;
1334 END IF;
1335 RETURN NULL;
1336 END;
1337 $$;
1339 CREATE TRIGGER "write_event_member"
1340 AFTER INSERT OR UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1341 "write_event_member_trigger"();
1343 COMMENT ON FUNCTION "write_event_member_trigger"() IS 'Implementation of trigger "write_event_member" on table "member"';
1344 COMMENT ON TRIGGER "write_event_member" ON "member" IS 'Create entries in "event" table on insertion to member table';
1347 CREATE FUNCTION "write_event_member_profile_updated_trigger"()
1348 RETURNS TRIGGER
1349 LANGUAGE 'plpgsql' VOLATILE AS $$
1350 BEGIN
1351 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1352 IF EXISTS (SELECT NULL FROM "member" WHERE "id" = OLD."member_id") THEN
1353 INSERT INTO "event" ("event", "member_id") VALUES (
1354 'member_profile_updated', OLD."member_id"
1355 );
1356 END IF;
1357 END IF;
1358 IF TG_OP = 'UPDATE' THEN
1359 IF OLD."member_id" = NEW."member_id" THEN
1360 RETURN NULL;
1361 END IF;
1362 END IF;
1363 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1364 INSERT INTO "event" ("event", "member_id") VALUES (
1365 'member_profile_updated', NEW."member_id"
1366 );
1367 END IF;
1368 RETURN NULL;
1369 END;
1370 $$;
1372 CREATE TRIGGER "write_event_member_profile_updated"
1373 AFTER INSERT OR UPDATE OR DELETE ON "member_profile"
1374 FOR EACH ROW EXECUTE PROCEDURE
1375 "write_event_member_profile_updated_trigger"();
1377 COMMENT ON FUNCTION "write_event_member_profile_updated_trigger"() IS 'Implementation of trigger "write_event_member_profile_updated" on table "member_profile"';
1378 COMMENT ON TRIGGER "write_event_member_profile_updated" ON "member_profile" IS 'Creates entries in "event" table on member profile update';
1381 CREATE FUNCTION "write_event_member_image_updated_trigger"()
1382 RETURNS TRIGGER
1383 LANGUAGE 'plpgsql' VOLATILE AS $$
1384 BEGIN
1385 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1386 IF NOT OLD."scaled" THEN
1387 IF EXISTS (SELECT NULL FROM "member" WHERE "id" = OLD."member_id") THEN
1388 INSERT INTO "event" ("event", "member_id") VALUES (
1389 'member_image_updated', OLD."member_id"
1390 );
1391 END IF;
1392 END IF;
1393 END IF;
1394 IF TG_OP = 'UPDATE' THEN
1395 IF
1396 OLD."member_id" = NEW."member_id" AND
1397 OLD."scaled" = NEW."scaled"
1398 THEN
1399 RETURN NULL;
1400 END IF;
1401 END IF;
1402 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1403 IF NOT NEW."scaled" THEN
1404 INSERT INTO "event" ("event", "member_id") VALUES (
1405 'member_image_updated', NEW."member_id"
1406 );
1407 END IF;
1408 END IF;
1409 RETURN NULL;
1410 END;
1411 $$;
1413 CREATE TRIGGER "write_event_member_image_updated"
1414 AFTER INSERT OR UPDATE OR DELETE ON "member_image"
1415 FOR EACH ROW EXECUTE PROCEDURE
1416 "write_event_member_image_updated_trigger"();
1418 COMMENT ON FUNCTION "write_event_member_image_updated_trigger"() IS 'Implementation of trigger "write_event_member_image_updated" on table "member_image"';
1419 COMMENT ON TRIGGER "write_event_member_image_updated" ON "member_image" IS 'Creates entries in "event" table on member image update';
1422 CREATE FUNCTION "write_event_interest_trigger"()
1423 RETURNS TRIGGER
1424 LANGUAGE 'plpgsql' VOLATILE AS $$
1425 DECLARE
1426 "issue_row" "issue"%ROWTYPE;
1427 "area_row" "area"%ROWTYPE;
1428 BEGIN
1429 IF TG_OP = 'UPDATE' THEN
1430 IF OLD = NEW THEN
1431 RETURN NULL;
1432 END IF;
1433 END IF;
1434 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1435 SELECT * INTO "issue_row" FROM "issue"
1436 WHERE "id" = OLD."issue_id" FOR SHARE;
1437 SELECT * INTO "area_row" FROM "area"
1438 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1439 IF "issue_row"."id" NOTNULL THEN
1440 INSERT INTO "event" (
1441 "event", "member_id",
1442 "unit_id", "area_id", "policy_id", "issue_id", "state",
1443 "boolean_value"
1444 ) VALUES (
1445 'interest', OLD."member_id",
1446 "area_row"."unit_id", "issue_row"."area_id",
1447 "issue_row"."policy_id",
1448 OLD."issue_id", "issue_row"."state",
1449 FALSE
1450 );
1451 END IF;
1452 END IF;
1453 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1454 SELECT * INTO "issue_row" FROM "issue"
1455 WHERE "id" = NEW."issue_id" FOR SHARE;
1456 SELECT * INTO "area_row" FROM "area"
1457 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1458 INSERT INTO "event" (
1459 "event", "member_id",
1460 "unit_id", "area_id", "policy_id", "issue_id", "state",
1461 "boolean_value"
1462 ) VALUES (
1463 'interest', NEW."member_id",
1464 "area_row"."unit_id", "issue_row"."area_id",
1465 "issue_row"."policy_id",
1466 NEW."issue_id", "issue_row"."state",
1467 TRUE
1468 );
1469 END IF;
1470 RETURN NULL;
1471 END;
1472 $$;
1474 CREATE TRIGGER "write_event_interest"
1475 AFTER INSERT OR UPDATE OR DELETE ON "interest" FOR EACH ROW EXECUTE PROCEDURE
1476 "write_event_interest_trigger"();
1478 COMMENT ON FUNCTION "write_event_interest_trigger"() IS 'Implementation of trigger "write_event_interest_inserted" on table "interest"';
1479 COMMENT ON TRIGGER "write_event_interest" ON "interest" IS 'Create entry in "event" table on adding or removing interest';
1482 CREATE FUNCTION "write_event_initiator_trigger"()
1483 RETURNS TRIGGER
1484 LANGUAGE 'plpgsql' VOLATILE AS $$
1485 DECLARE
1486 "initiative_row" "initiative"%ROWTYPE;
1487 "issue_row" "issue"%ROWTYPE;
1488 "area_row" "area"%ROWTYPE;
1489 "accepted_v" BOOLEAN = FALSE;
1490 "rejected_v" BOOLEAN = FALSE;
1491 BEGIN
1492 IF TG_OP = 'UPDATE' THEN
1493 IF
1494 OLD."initiative_id" = NEW."initiative_id" AND
1495 OLD."member_id" = NEW."member_id"
1496 THEN
1497 IF
1498 coalesce(OLD."accepted", FALSE) = coalesce(NEW."accepted", FALSE)
1499 THEN
1500 RETURN NULL;
1501 END IF;
1502 IF coalesce(NEW."accepted", FALSE) = TRUE THEN
1503 "accepted_v" := TRUE;
1504 ELSE
1505 "rejected_v" := TRUE;
1506 END IF;
1507 END IF;
1508 END IF;
1509 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "accepted_v" THEN
1510 IF coalesce(OLD."accepted", FALSE) = TRUE THEN
1511 SELECT * INTO "initiative_row" FROM "initiative"
1512 WHERE "id" = OLD."initiative_id" FOR SHARE;
1513 IF "initiative_row"."id" NOTNULL THEN
1514 SELECT * INTO "issue_row" FROM "issue"
1515 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1516 SELECT * INTO "area_row" FROM "area"
1517 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1518 INSERT INTO "event" (
1519 "event", "member_id",
1520 "unit_id", "area_id", "policy_id", "issue_id", "state",
1521 "initiative_id", "boolean_value"
1522 ) VALUES (
1523 'initiator', OLD."member_id",
1524 "area_row"."unit_id", "issue_row"."area_id",
1525 "issue_row"."policy_id",
1526 "issue_row"."id", "issue_row"."state",
1527 OLD."initiative_id", FALSE
1528 );
1529 END IF;
1530 END IF;
1531 END IF;
1532 IF TG_OP = 'UPDATE' AND NOT "rejected_v" THEN
1533 IF coalesce(NEW."accepted", FALSE) = TRUE THEN
1534 SELECT * INTO "initiative_row" FROM "initiative"
1535 WHERE "id" = NEW."initiative_id" FOR SHARE;
1536 SELECT * INTO "issue_row" FROM "issue"
1537 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1538 SELECT * INTO "area_row" FROM "area"
1539 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1540 INSERT INTO "event" (
1541 "event", "member_id",
1542 "unit_id", "area_id", "policy_id", "issue_id", "state",
1543 "initiative_id", "boolean_value"
1544 ) VALUES (
1545 'initiator', NEW."member_id",
1546 "area_row"."unit_id", "issue_row"."area_id",
1547 "issue_row"."policy_id",
1548 "issue_row"."id", "issue_row"."state",
1549 NEW."initiative_id", TRUE
1550 );
1551 END IF;
1552 END IF;
1553 RETURN NULL;
1554 END;
1555 $$;
1557 CREATE TRIGGER "write_event_initiator"
1558 AFTER UPDATE OR DELETE ON "initiator" FOR EACH ROW EXECUTE PROCEDURE
1559 "write_event_initiator_trigger"();
1561 COMMENT ON FUNCTION "write_event_initiator_trigger"() IS 'Implementation of trigger "write_event_initiator" on table "initiator"';
1562 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)';
1565 CREATE FUNCTION "write_event_support_trigger"()
1566 RETURNS TRIGGER
1567 LANGUAGE 'plpgsql' VOLATILE AS $$
1568 DECLARE
1569 "issue_row" "issue"%ROWTYPE;
1570 "area_row" "area"%ROWTYPE;
1571 BEGIN
1572 IF TG_OP = 'UPDATE' THEN
1573 IF
1574 OLD."initiative_id" = NEW."initiative_id" AND
1575 OLD."member_id" = NEW."member_id"
1576 THEN
1577 IF OLD."draft_id" != NEW."draft_id" THEN
1578 SELECT * INTO "issue_row" FROM "issue"
1579 WHERE "id" = NEW."issue_id" FOR SHARE;
1580 SELECT * INTO "area_row" FROM "area"
1581 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1582 INSERT INTO "event" (
1583 "event", "member_id",
1584 "unit_id", "area_id", "policy_id", "issue_id", "state",
1585 "initiative_id", "draft_id"
1586 ) VALUES (
1587 'support_updated', NEW."member_id",
1588 "area_row"."unit_id", "issue_row"."area_id",
1589 "issue_row"."policy_id",
1590 "issue_row"."id", "issue_row"."state",
1591 NEW."initiative_id", NEW."draft_id"
1592 );
1593 END IF;
1594 RETURN NULL;
1595 END IF;
1596 END IF;
1597 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1598 IF EXISTS (
1599 SELECT NULL FROM "initiative" WHERE "id" = OLD."initiative_id"
1600 FOR SHARE
1601 ) THEN
1602 SELECT * INTO "issue_row" FROM "issue"
1603 WHERE "id" = OLD."issue_id" FOR SHARE;
1604 SELECT * INTO "area_row" FROM "area"
1605 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1606 INSERT INTO "event" (
1607 "event", "member_id",
1608 "unit_id", "area_id", "policy_id", "issue_id", "state",
1609 "initiative_id", "boolean_value"
1610 ) VALUES (
1611 'support', OLD."member_id",
1612 "area_row"."unit_id", "issue_row"."area_id",
1613 "issue_row"."policy_id",
1614 "issue_row"."id", "issue_row"."state",
1615 OLD."initiative_id", FALSE
1616 );
1617 END IF;
1618 END IF;
1619 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1620 SELECT * INTO "issue_row" FROM "issue"
1621 WHERE "id" = NEW."issue_id" FOR SHARE;
1622 SELECT * INTO "area_row" FROM "area"
1623 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1624 INSERT INTO "event" (
1625 "event", "member_id",
1626 "unit_id", "area_id", "policy_id", "issue_id", "state",
1627 "initiative_id", "draft_id", "boolean_value"
1628 ) VALUES (
1629 'support', NEW."member_id",
1630 "area_row"."unit_id", "issue_row"."area_id",
1631 "issue_row"."policy_id",
1632 "issue_row"."id", "issue_row"."state",
1633 NEW."initiative_id", NEW."draft_id", TRUE
1634 );
1635 END IF;
1636 RETURN NULL;
1637 END;
1638 $$;
1640 CREATE TRIGGER "write_event_support"
1641 AFTER INSERT OR UPDATE OR DELETE ON "supporter" FOR EACH ROW EXECUTE PROCEDURE
1642 "write_event_support_trigger"();
1644 COMMENT ON FUNCTION "write_event_support_trigger"() IS 'Implementation of trigger "write_event_support" on table "supporter"';
1645 COMMENT ON TRIGGER "write_event_support" ON "supporter" IS 'Create entry in "event" table when adding, updating, or removing support';
1648 CREATE FUNCTION "write_event_suggestion_rated_trigger"()
1649 RETURNS TRIGGER
1650 LANGUAGE 'plpgsql' VOLATILE AS $$
1651 DECLARE
1652 "same_pkey_v" BOOLEAN = FALSE;
1653 "initiative_row" "initiative"%ROWTYPE;
1654 "issue_row" "issue"%ROWTYPE;
1655 "area_row" "area"%ROWTYPE;
1656 BEGIN
1657 IF TG_OP = 'UPDATE' THEN
1658 IF
1659 OLD."suggestion_id" = NEW."suggestion_id" AND
1660 OLD."member_id" = NEW."member_id"
1661 THEN
1662 IF
1663 OLD."degree" = NEW."degree" AND
1664 OLD."fulfilled" = NEW."fulfilled"
1665 THEN
1666 RETURN NULL;
1667 END IF;
1668 "same_pkey_v" := TRUE;
1669 END IF;
1670 END IF;
1671 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "same_pkey_v" THEN
1672 IF EXISTS (
1673 SELECT NULL FROM "suggestion" WHERE "id" = OLD."suggestion_id"
1674 FOR SHARE
1675 ) THEN
1676 SELECT * INTO "initiative_row" FROM "initiative"
1677 WHERE "id" = OLD."initiative_id" FOR SHARE;
1678 SELECT * INTO "issue_row" FROM "issue"
1679 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1680 SELECT * INTO "area_row" FROM "area"
1681 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1682 INSERT INTO "event" (
1683 "event", "member_id",
1684 "unit_id", "area_id", "policy_id", "issue_id", "state",
1685 "initiative_id", "suggestion_id",
1686 "boolean_value", "numeric_value"
1687 ) VALUES (
1688 'suggestion_rated', OLD."member_id",
1689 "area_row"."unit_id", "issue_row"."area_id",
1690 "issue_row"."policy_id",
1691 "initiative_row"."issue_id", "issue_row"."state",
1692 OLD."initiative_id", OLD."suggestion_id",
1693 NULL, 0
1694 );
1695 END IF;
1696 END IF;
1697 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1698 SELECT * INTO "initiative_row" FROM "initiative"
1699 WHERE "id" = NEW."initiative_id" FOR SHARE;
1700 SELECT * INTO "issue_row" FROM "issue"
1701 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1702 SELECT * INTO "area_row" FROM "area"
1703 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1704 INSERT INTO "event" (
1705 "event", "member_id",
1706 "unit_id", "area_id", "policy_id", "issue_id", "state",
1707 "initiative_id", "suggestion_id",
1708 "boolean_value", "numeric_value"
1709 ) VALUES (
1710 'suggestion_rated', NEW."member_id",
1711 "area_row"."unit_id", "issue_row"."area_id",
1712 "issue_row"."policy_id",
1713 "initiative_row"."issue_id", "issue_row"."state",
1714 NEW."initiative_id", NEW."suggestion_id",
1715 NEW."fulfilled", NEW."degree"
1716 );
1717 END IF;
1718 RETURN NULL;
1719 END;
1720 $$;
1722 CREATE TRIGGER "write_event_suggestion_rated"
1723 AFTER INSERT OR UPDATE OR DELETE ON "opinion" FOR EACH ROW EXECUTE PROCEDURE
1724 "write_event_suggestion_rated_trigger"();
1726 COMMENT ON FUNCTION "write_event_suggestion_rated_trigger"() IS 'Implementation of trigger "write_event_suggestion_rated" on table "opinion"';
1727 COMMENT ON TRIGGER "write_event_suggestion_rated" ON "opinion" IS 'Create entry in "event" table when adding, updating, or removing support';
1730 CREATE FUNCTION "write_event_delegation_trigger"()
1731 RETURNS TRIGGER
1732 LANGUAGE 'plpgsql' VOLATILE AS $$
1733 DECLARE
1734 "issue_row" "issue"%ROWTYPE;
1735 "area_row" "area"%ROWTYPE;
1736 BEGIN
1737 IF TG_OP = 'DELETE' THEN
1738 IF EXISTS (
1739 SELECT NULL FROM "member" WHERE "id" = OLD."truster_id"
1740 ) AND (CASE OLD."scope"
1741 WHEN 'unit'::"delegation_scope" THEN EXISTS (
1742 SELECT NULL FROM "unit" WHERE "id" = OLD."unit_id"
1743 )
1744 WHEN 'area'::"delegation_scope" THEN EXISTS (
1745 SELECT NULL FROM "area" WHERE "id" = OLD."area_id"
1746 )
1747 WHEN 'issue'::"delegation_scope" THEN EXISTS (
1748 SELECT NULL FROM "issue" WHERE "id" = OLD."issue_id"
1749 )
1750 END) THEN
1751 SELECT * INTO "issue_row" FROM "issue"
1752 WHERE "id" = OLD."issue_id" FOR SHARE;
1753 SELECT * INTO "area_row" FROM "area"
1754 WHERE "id" = COALESCE(OLD."area_id", "issue_row"."area_id")
1755 FOR SHARE;
1756 INSERT INTO "event" (
1757 "event", "member_id", "scope",
1758 "unit_id", "area_id", "issue_id", "state",
1759 "boolean_value"
1760 ) VALUES (
1761 'delegation', OLD."truster_id", OLD."scope",
1762 COALESCE(OLD."unit_id", "area_row"."unit_id"), "area_row"."id",
1763 OLD."issue_id", "issue_row"."state",
1764 FALSE
1765 );
1766 END IF;
1767 ELSE
1768 SELECT * INTO "issue_row" FROM "issue"
1769 WHERE "id" = NEW."issue_id" FOR SHARE;
1770 SELECT * INTO "area_row" FROM "area"
1771 WHERE "id" = COALESCE(NEW."area_id", "issue_row"."area_id")
1772 FOR SHARE;
1773 INSERT INTO "event" (
1774 "event", "member_id", "other_member_id", "scope",
1775 "unit_id", "area_id", "issue_id", "state",
1776 "boolean_value"
1777 ) VALUES (
1778 'delegation', NEW."truster_id", NEW."trustee_id", NEW."scope",
1779 COALESCE(NEW."unit_id", "area_row"."unit_id"), "area_row"."id",
1780 NEW."issue_id", "issue_row"."state",
1781 TRUE
1782 );
1783 END IF;
1784 RETURN NULL;
1785 END;
1786 $$;
1788 CREATE TRIGGER "write_event_delegation"
1789 AFTER INSERT OR UPDATE OR DELETE ON "delegation" FOR EACH ROW EXECUTE PROCEDURE
1790 "write_event_delegation_trigger"();
1792 COMMENT ON FUNCTION "write_event_delegation_trigger"() IS 'Implementation of trigger "write_event_delegation" on table "delegation"';
1793 COMMENT ON TRIGGER "write_event_delegation" ON "delegation" IS 'Create entry in "event" table when adding, updating, or removing a delegation';
1796 CREATE FUNCTION "write_event_contact_trigger"()
1797 RETURNS TRIGGER
1798 LANGUAGE 'plpgsql' VOLATILE AS $$
1799 BEGIN
1800 IF TG_OP = 'UPDATE' THEN
1801 IF
1802 OLD."member_id" = NEW."member_id" AND
1803 OLD."other_member_id" = NEW."other_member_id" AND
1804 OLD."public" = NEW."public"
1805 THEN
1806 RETURN NULL;
1807 END IF;
1808 END IF;
1809 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1810 IF OLD."public" THEN
1811 IF EXISTS (
1812 SELECT NULL FROM "member" WHERE "id" = OLD."member_id"
1813 FOR SHARE
1814 ) AND EXISTS (
1815 SELECT NULL FROM "member" WHERE "id" = OLD."other_member_id"
1816 FOR SHARE
1817 ) THEN
1818 INSERT INTO "event" (
1819 "event", "member_id", "other_member_id", "boolean_value"
1820 ) VALUES (
1821 'contact', OLD."member_id", OLD."other_member_id", FALSE
1822 );
1823 END IF;
1824 END IF;
1825 END IF;
1826 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1827 IF NEW."public" THEN
1828 INSERT INTO "event" (
1829 "event", "member_id", "other_member_id", "boolean_value"
1830 ) VALUES (
1831 'contact', NEW."member_id", NEW."other_member_id", TRUE
1832 );
1833 END IF;
1834 END IF;
1835 RETURN NULL;
1836 END;
1837 $$;
1839 CREATE TRIGGER "write_event_contact"
1840 AFTER INSERT OR UPDATE OR DELETE ON "contact" FOR EACH ROW EXECUTE PROCEDURE
1841 "write_event_contact_trigger"();
1843 COMMENT ON FUNCTION "write_event_contact_trigger"() IS 'Implementation of trigger "write_event_contact" on table "contact"';
1844 COMMENT ON TRIGGER "write_event_contact" ON "contact" IS 'Create entry in "event" table when adding or removing public contacts';
1847 CREATE FUNCTION "send_event_notify_trigger"()
1848 RETURNS TRIGGER
1849 LANGUAGE 'plpgsql' VOLATILE AS $$
1850 BEGIN
1851 EXECUTE 'NOTIFY "event", ''' || NEW."event" || '''';
1852 RETURN NULL;
1853 END;
1854 $$;
1856 CREATE TRIGGER "send_notify"
1857 AFTER INSERT OR UPDATE ON "event" FOR EACH ROW EXECUTE PROCEDURE
1858 "send_event_notify_trigger"();
1861 CREATE FUNCTION "delete_extended_scope_tokens_trigger"()
1862 RETURNS TRIGGER
1863 LANGUAGE 'plpgsql' VOLATILE AS $$
1864 DECLARE
1865 "system_application_row" "system_application"%ROWTYPE;
1866 BEGIN
1867 IF OLD."system_application_id" NOTNULL THEN
1868 SELECT * FROM "system_application" INTO "system_application_row"
1869 WHERE "id" = OLD."system_application_id";
1870 DELETE FROM "token"
1871 WHERE "member_id" = OLD."member_id"
1872 AND "system_application_id" = OLD."system_application_id"
1873 AND NOT COALESCE(
1874 regexp_split_to_array("scope", E'\\s+') <@
1875 regexp_split_to_array(
1876 "system_application_row"."automatic_scope", E'\\s+'
1877 ),
1878 FALSE
1879 );
1880 END IF;
1881 RETURN OLD;
1882 END;
1883 $$;
1885 CREATE TRIGGER "delete_extended_scope_tokens"
1886 BEFORE DELETE ON "member_application" FOR EACH ROW EXECUTE PROCEDURE
1887 "delete_extended_scope_tokens_trigger"();
1890 CREATE FUNCTION "detach_token_from_session_trigger"()
1891 RETURNS TRIGGER
1892 LANGUAGE 'plpgsql' VOLATILE AS $$
1893 BEGIN
1894 UPDATE "token" SET "session_id" = NULL
1895 WHERE "session_id" = OLD."id";
1896 RETURN OLD;
1897 END;
1898 $$;
1900 CREATE TRIGGER "detach_token_from_session"
1901 BEFORE DELETE ON "session" FOR EACH ROW EXECUTE PROCEDURE
1902 "detach_token_from_session_trigger"();
1905 CREATE FUNCTION "delete_non_detached_scope_with_session_trigger"()
1906 RETURNS TRIGGER
1907 LANGUAGE 'plpgsql' VOLATILE AS $$
1908 BEGIN
1909 IF NEW."session_id" ISNULL THEN
1910 SELECT coalesce(string_agg("element", ' '), '') INTO NEW."scope"
1911 FROM unnest(regexp_split_to_array(NEW."scope", E'\\s+')) AS "element"
1912 WHERE "element" LIKE '%_detached';
1913 END IF;
1914 RETURN NEW;
1915 END;
1916 $$;
1918 CREATE TRIGGER "delete_non_detached_scope_with_session"
1919 BEFORE INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
1920 "delete_non_detached_scope_with_session_trigger"();
1923 CREATE FUNCTION "delete_token_with_empty_scope_trigger"()
1924 RETURNS TRIGGER
1925 LANGUAGE 'plpgsql' VOLATILE AS $$
1926 BEGIN
1927 IF NEW."scope" = '' THEN
1928 DELETE FROM "token" WHERE "id" = NEW."id";
1929 END IF;
1930 RETURN NULL;
1931 END;
1932 $$;
1934 CREATE TRIGGER "delete_token_with_empty_scope"
1935 AFTER INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
1936 "delete_token_with_empty_scope_trigger"();
1939 CREATE FUNCTION "delete_snapshot_on_partial_delete_trigger"()
1940 RETURNS TRIGGER
1941 LANGUAGE 'plpgsql' VOLATILE AS $$
1942 BEGIN
1943 IF TG_OP = 'UPDATE' THEN
1944 IF
1945 OLD."snapshot_id" = NEW."snapshot_id" AND
1946 OLD."issue_id" = NEW."issue_id"
1947 THEN
1948 RETURN NULL;
1949 END IF;
1950 END IF;
1951 DELETE FROM "snapshot" WHERE "id" = OLD."snapshot_id";
1952 RETURN NULL;
1953 END;
1954 $$;
1956 CREATE TRIGGER "delete_snapshot_on_partial_delete"
1957 AFTER UPDATE OR DELETE ON "snapshot_issue"
1958 FOR EACH ROW EXECUTE PROCEDURE
1959 "delete_snapshot_on_partial_delete_trigger"();
1961 COMMENT ON FUNCTION "delete_snapshot_on_partial_delete_trigger"() IS 'Implementation of trigger "delete_snapshot_on_partial_delete" on table "snapshot_issue"';
1962 COMMENT ON TRIGGER "delete_snapshot_on_partial_delete" ON "snapshot_issue" IS 'Deletes whole snapshot if one issue is deleted from the snapshot';
1965 CREATE FUNCTION "copy_current_draft_data"
1966 ("initiative_id_p" "initiative"."id"%TYPE )
1967 RETURNS VOID
1968 LANGUAGE 'plpgsql' VOLATILE AS $$
1969 BEGIN
1970 PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p"
1971 FOR UPDATE;
1972 UPDATE "initiative" SET
1973 "location" = "draft"."location",
1974 "draft_text_search_data" = "draft"."text_search_data"
1975 FROM "current_draft" AS "draft"
1976 WHERE "initiative"."id" = "initiative_id_p"
1977 AND "draft"."initiative_id" = "initiative_id_p";
1978 END;
1979 $$;
1981 COMMENT ON FUNCTION "copy_current_draft_data"
1982 ( "initiative"."id"%TYPE )
1983 IS 'Helper function for function "copy_current_draft_data_trigger"';
1986 CREATE FUNCTION "copy_current_draft_data_trigger"()
1987 RETURNS TRIGGER
1988 LANGUAGE 'plpgsql' VOLATILE AS $$
1989 BEGIN
1990 IF TG_OP='DELETE' THEN
1991 PERFORM "copy_current_draft_data"(OLD."initiative_id");
1992 ELSE
1993 IF TG_OP='UPDATE' THEN
1994 IF COALESCE(OLD."inititiave_id" != NEW."initiative_id", TRUE) THEN
1995 PERFORM "copy_current_draft_data"(OLD."initiative_id");
1996 END IF;
1997 END IF;
1998 PERFORM "copy_current_draft_data"(NEW."initiative_id");
1999 END IF;
2000 RETURN NULL;
2001 END;
2002 $$;
2004 CREATE TRIGGER "copy_current_draft_data"
2005 AFTER INSERT OR UPDATE OR DELETE ON "draft"
2006 FOR EACH ROW EXECUTE PROCEDURE
2007 "copy_current_draft_data_trigger"();
2009 COMMENT ON FUNCTION "copy_current_draft_data_trigger"() IS 'Implementation of trigger "copy_current_draft_data" on table "draft"';
2010 COMMENT ON TRIGGER "copy_current_draft_data" ON "draft" IS 'Copy certain fields from most recent "draft" to "initiative"';
2013 CREATE VIEW "area_quorum" AS
2014 SELECT
2015 "area"."id" AS "area_id",
2016 ceil(
2017 "area"."quorum_standard"::FLOAT8 * "quorum_factor"::FLOAT8 ^ (
2018 coalesce(
2019 ( SELECT sum(
2020 ( extract(epoch from "area"."quorum_time")::FLOAT8 /
2021 extract(epoch from
2022 ("issue"."accepted"-"issue"."created") +
2023 "issue"."discussion_time" +
2024 "issue"."verification_time" +
2025 "issue"."voting_time"
2026 )::FLOAT8
2027 ) ^ "area"."quorum_exponent"::FLOAT8
2028 )
2029 FROM "issue" JOIN "policy"
2030 ON "issue"."policy_id" = "policy"."id"
2031 WHERE "issue"."area_id" = "area"."id"
2032 AND "issue"."accepted" NOTNULL
2033 AND "issue"."closed" ISNULL
2034 AND "policy"."polling" = FALSE
2035 )::FLOAT8, 0::FLOAT8
2036 ) / "area"."quorum_issues"::FLOAT8 - 1::FLOAT8
2037 ) * CASE WHEN "area"."quorum_den" ISNULL THEN 1 ELSE (
2038 SELECT "snapshot"."population"
2039 FROM "snapshot"
2040 WHERE "snapshot"."area_id" = "area"."id"
2041 AND "snapshot"."issue_id" ISNULL
2042 ORDER BY "snapshot"."id" DESC
2043 LIMIT 1
2044 ) END / coalesce("area"."quorum_den", 1)
2046 )::INT4 AS "issue_quorum"
2047 FROM "area";
2049 COMMENT ON VIEW "area_quorum" IS 'Area-based quorum considering number of open (accepted) issues';
2052 CREATE VIEW "area_with_unaccepted_issues" AS
2053 SELECT DISTINCT ON ("area"."id") "area".*
2054 FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id"
2055 WHERE "issue"."state" = 'admission';
2057 COMMENT ON VIEW "area_with_unaccepted_issues" IS 'All areas with unaccepted open issues (needed for issue admission system)';
2060 DROP VIEW "area_member_count";
2063 DROP TABLE "membership";
2066 DROP FUNCTION "membership_weight"
2067 ( "area_id_p" "area"."id"%TYPE,
2068 "member_id_p" "member"."id"%TYPE );
2071 DROP FUNCTION "membership_weight_with_skipping"
2072 ( "area_id_p" "area"."id"%TYPE,
2073 "member_id_p" "member"."id"%TYPE,
2074 "skip_member_ids_p" INT4[] ); -- TODO: ordering/cascade
2077 CREATE OR REPLACE VIEW "issue_delegation" AS
2078 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
2079 "issue"."id" AS "issue_id",
2080 "delegation"."id",
2081 "delegation"."truster_id",
2082 "delegation"."trustee_id",
2083 "delegation"."scope"
2084 FROM "issue"
2085 JOIN "area"
2086 ON "area"."id" = "issue"."area_id"
2087 JOIN "delegation"
2088 ON "delegation"."unit_id" = "area"."unit_id"
2089 OR "delegation"."area_id" = "area"."id"
2090 OR "delegation"."issue_id" = "issue"."id"
2091 JOIN "member"
2092 ON "delegation"."truster_id" = "member"."id"
2093 JOIN "privilege"
2094 ON "area"."unit_id" = "privilege"."unit_id"
2095 AND "delegation"."truster_id" = "privilege"."member_id"
2096 WHERE "member"."active" AND "privilege"."voting_right"
2097 ORDER BY
2098 "issue"."id",
2099 "delegation"."truster_id",
2100 "delegation"."scope" DESC;
2103 CREATE VIEW "unit_member" AS
2104 SELECT
2105 "unit"."id" AS "unit_id",
2106 "member"."id" AS "member_id"
2107 FROM "privilege"
2108 JOIN "unit" ON "unit_id" = "privilege"."unit_id"
2109 JOIN "member" ON "member"."id" = "privilege"."member_id"
2110 WHERE "privilege"."voting_right" AND "member"."active";
2112 COMMENT ON VIEW "unit_member" IS 'Active members with voting right in a unit';
2115 CREATE OR REPLACE VIEW "unit_member_count" AS
2116 SELECT
2117 "unit"."id" AS "unit_id",
2118 count("unit_member"."member_id") AS "member_count"
2119 FROM "unit" LEFT JOIN "unit_member"
2120 ON "unit"."id" = "unit_member"."unit_id"
2121 GROUP BY "unit"."id";
2123 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
2126 CREATE OR REPLACE VIEW "opening_draft" AS
2127 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
2128 ORDER BY "initiative_id", "id";
2131 CREATE OR REPLACE VIEW "current_draft" AS
2132 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
2133 ORDER BY "initiative_id", "id" DESC;
2136 CREATE OR REPLACE VIEW "issue_supporter_in_admission_state" AS
2137 SELECT
2138 "area"."unit_id",
2139 "issue"."area_id",
2140 "issue"."id" AS "issue_id",
2141 "supporter"."member_id",
2142 "direct_interest_snapshot"."weight"
2143 FROM "issue"
2144 JOIN "area" ON "area"."id" = "issue"."area_id"
2145 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
2146 JOIN "direct_interest_snapshot"
2147 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
2148 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
2149 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
2150 WHERE "issue"."state" = 'admission'::"issue_state";
2153 CREATE OR REPLACE VIEW "individual_suggestion_ranking" AS
2154 SELECT
2155 "opinion"."initiative_id",
2156 "opinion"."member_id",
2157 "direct_interest_snapshot"."weight",
2158 CASE WHEN
2159 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
2160 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
2161 THEN 1 ELSE
2162 CASE WHEN
2163 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
2164 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
2165 THEN 2 ELSE
2166 CASE WHEN
2167 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
2168 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
2169 THEN 3 ELSE 4 END
2170 END
2171 END AS "preference",
2172 "opinion"."suggestion_id"
2173 FROM "opinion"
2174 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
2175 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2176 JOIN "direct_interest_snapshot"
2177 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
2178 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
2179 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
2182 CREATE VIEW "expired_session" AS
2183 SELECT * FROM "session" WHERE now() > "expiry";
2185 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
2186 DELETE FROM "session" WHERE "id" = OLD."id";
2188 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
2189 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
2192 CREATE VIEW "expired_token" AS
2193 SELECT * FROM "token" WHERE now() > "expiry" AND NOT (
2194 "token_type" = 'authorization' AND "used" AND EXISTS (
2195 SELECT NULL FROM "token" AS "other"
2196 WHERE "other"."authorization_token_id" = "id" ) );
2198 CREATE RULE "delete" AS ON DELETE TO "expired_token" DO INSTEAD
2199 DELETE FROM "token" WHERE "id" = OLD."id";
2201 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';
2204 CREATE VIEW "unused_snapshot" AS
2205 SELECT "snapshot".* FROM "snapshot"
2206 LEFT JOIN "issue"
2207 ON "snapshot"."id" = "issue"."latest_snapshot_id"
2208 OR "snapshot"."id" = "issue"."admission_snapshot_id"
2209 OR "snapshot"."id" = "issue"."half_freeze_snapshot_id"
2210 OR "snapshot"."id" = "issue"."full_freeze_snapshot_id"
2211 WHERE "issue"."id" ISNULL;
2213 CREATE RULE "delete" AS ON DELETE TO "unused_snapshot" DO INSTEAD
2214 DELETE FROM "snapshot" WHERE "id" = OLD."id";
2216 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)';
2219 CREATE VIEW "expired_snapshot" AS
2220 SELECT "unused_snapshot".* FROM "unused_snapshot" CROSS JOIN "system_setting"
2221 WHERE "unused_snapshot"."calculated" <
2222 now() - "system_setting"."snapshot_retention";
2224 CREATE RULE "delete" AS ON DELETE TO "expired_snapshot" DO INSTEAD
2225 DELETE FROM "snapshot" WHERE "id" = OLD."id";
2227 COMMENT ON VIEW "expired_snapshot" IS 'Contains "unused_snapshot"s that are older than "system_setting"."snapshot_retention" (for deletion)';
2230 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest; for area and global delegation chains: always null';
2233 CREATE OR REPLACE FUNCTION "delegation_chain"
2234 ( "member_id_p" "member"."id"%TYPE,
2235 "unit_id_p" "unit"."id"%TYPE,
2236 "area_id_p" "area"."id"%TYPE,
2237 "issue_id_p" "issue"."id"%TYPE,
2238 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2239 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2240 RETURNS SETOF "delegation_chain_row"
2241 LANGUAGE 'plpgsql' STABLE AS $$
2242 DECLARE
2243 "scope_v" "delegation_scope";
2244 "unit_id_v" "unit"."id"%TYPE;
2245 "area_id_v" "area"."id"%TYPE;
2246 "issue_row" "issue"%ROWTYPE;
2247 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2248 "loop_member_id_v" "member"."id"%TYPE;
2249 "output_row" "delegation_chain_row";
2250 "output_rows" "delegation_chain_row"[];
2251 "simulate_v" BOOLEAN;
2252 "simulate_here_v" BOOLEAN;
2253 "delegation_row" "delegation"%ROWTYPE;
2254 "row_count" INT4;
2255 "i" INT4;
2256 "loop_v" BOOLEAN;
2257 BEGIN
2258 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
2259 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
2260 END IF;
2261 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
2262 "simulate_v" := TRUE;
2263 ELSE
2264 "simulate_v" := FALSE;
2265 END IF;
2266 IF
2267 "unit_id_p" NOTNULL AND
2268 "area_id_p" ISNULL AND
2269 "issue_id_p" ISNULL
2270 THEN
2271 "scope_v" := 'unit';
2272 "unit_id_v" := "unit_id_p";
2273 ELSIF
2274 "unit_id_p" ISNULL AND
2275 "area_id_p" NOTNULL AND
2276 "issue_id_p" ISNULL
2277 THEN
2278 "scope_v" := 'area';
2279 "area_id_v" := "area_id_p";
2280 SELECT "unit_id" INTO "unit_id_v"
2281 FROM "area" WHERE "id" = "area_id_v";
2282 ELSIF
2283 "unit_id_p" ISNULL AND
2284 "area_id_p" ISNULL AND
2285 "issue_id_p" NOTNULL
2286 THEN
2287 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
2288 IF "issue_row"."id" ISNULL THEN
2289 RETURN;
2290 END IF;
2291 IF "issue_row"."closed" NOTNULL THEN
2292 IF "simulate_v" THEN
2293 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
2294 END IF;
2295 FOR "output_row" IN
2296 SELECT * FROM
2297 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
2298 LOOP
2299 RETURN NEXT "output_row";
2300 END LOOP;
2301 RETURN;
2302 END IF;
2303 "scope_v" := 'issue';
2304 SELECT "area_id" INTO "area_id_v"
2305 FROM "issue" WHERE "id" = "issue_id_p";
2306 SELECT "unit_id" INTO "unit_id_v"
2307 FROM "area" WHERE "id" = "area_id_v";
2308 ELSE
2309 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2310 END IF;
2311 "visited_member_ids" := '{}';
2312 "loop_member_id_v" := NULL;
2313 "output_rows" := '{}';
2314 "output_row"."index" := 0;
2315 "output_row"."member_id" := "member_id_p";
2316 "output_row"."member_valid" := TRUE;
2317 "output_row"."participation" := FALSE;
2318 "output_row"."overridden" := FALSE;
2319 "output_row"."disabled_out" := FALSE;
2320 "output_row"."scope_out" := NULL;
2321 LOOP
2322 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2323 "loop_member_id_v" := "output_row"."member_id";
2324 ELSE
2325 "visited_member_ids" :=
2326 "visited_member_ids" || "output_row"."member_id";
2327 END IF;
2328 IF "output_row"."participation" ISNULL THEN
2329 "output_row"."overridden" := NULL;
2330 ELSIF "output_row"."participation" THEN
2331 "output_row"."overridden" := TRUE;
2332 END IF;
2333 "output_row"."scope_in" := "output_row"."scope_out";
2334 "output_row"."member_valid" := EXISTS (
2335 SELECT NULL FROM "member" JOIN "privilege"
2336 ON "privilege"."member_id" = "member"."id"
2337 AND "privilege"."unit_id" = "unit_id_v"
2338 WHERE "id" = "output_row"."member_id"
2339 AND "member"."active" AND "privilege"."voting_right"
2340 );
2341 "simulate_here_v" := (
2342 "simulate_v" AND
2343 "output_row"."member_id" = "member_id_p"
2344 );
2345 "delegation_row" := ROW(NULL);
2346 IF "output_row"."member_valid" OR "simulate_here_v" THEN
2347 IF "scope_v" = 'unit' THEN
2348 IF NOT "simulate_here_v" THEN
2349 SELECT * INTO "delegation_row" FROM "delegation"
2350 WHERE "truster_id" = "output_row"."member_id"
2351 AND "unit_id" = "unit_id_v";
2352 END IF;
2353 ELSIF "scope_v" = 'area' THEN
2354 IF "simulate_here_v" THEN
2355 IF "simulate_trustee_id_p" ISNULL THEN
2356 SELECT * INTO "delegation_row" FROM "delegation"
2357 WHERE "truster_id" = "output_row"."member_id"
2358 AND "unit_id" = "unit_id_v";
2359 END IF;
2360 ELSE
2361 SELECT * INTO "delegation_row" FROM "delegation"
2362 WHERE "truster_id" = "output_row"."member_id"
2363 AND (
2364 "unit_id" = "unit_id_v" OR
2365 "area_id" = "area_id_v"
2366 )
2367 ORDER BY "scope" DESC;
2368 END IF;
2369 ELSIF "scope_v" = 'issue' THEN
2370 IF "issue_row"."fully_frozen" ISNULL THEN
2371 "output_row"."participation" := EXISTS (
2372 SELECT NULL FROM "interest"
2373 WHERE "issue_id" = "issue_id_p"
2374 AND "member_id" = "output_row"."member_id"
2375 );
2376 ELSE
2377 IF "output_row"."member_id" = "member_id_p" THEN
2378 "output_row"."participation" := EXISTS (
2379 SELECT NULL FROM "direct_voter"
2380 WHERE "issue_id" = "issue_id_p"
2381 AND "member_id" = "output_row"."member_id"
2382 );
2383 ELSE
2384 "output_row"."participation" := NULL;
2385 END IF;
2386 END IF;
2387 IF "simulate_here_v" THEN
2388 IF "simulate_trustee_id_p" ISNULL THEN
2389 SELECT * INTO "delegation_row" FROM "delegation"
2390 WHERE "truster_id" = "output_row"."member_id"
2391 AND (
2392 "unit_id" = "unit_id_v" OR
2393 "area_id" = "area_id_v"
2394 )
2395 ORDER BY "scope" DESC;
2396 END IF;
2397 ELSE
2398 SELECT * INTO "delegation_row" FROM "delegation"
2399 WHERE "truster_id" = "output_row"."member_id"
2400 AND (
2401 "unit_id" = "unit_id_v" OR
2402 "area_id" = "area_id_v" OR
2403 "issue_id" = "issue_id_p"
2404 )
2405 ORDER BY "scope" DESC;
2406 END IF;
2407 END IF;
2408 ELSE
2409 "output_row"."participation" := FALSE;
2410 END IF;
2411 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
2412 "output_row"."scope_out" := "scope_v";
2413 "output_rows" := "output_rows" || "output_row";
2414 "output_row"."member_id" := "simulate_trustee_id_p";
2415 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2416 "output_row"."scope_out" := "delegation_row"."scope";
2417 "output_rows" := "output_rows" || "output_row";
2418 "output_row"."member_id" := "delegation_row"."trustee_id";
2419 ELSIF "delegation_row"."scope" NOTNULL THEN
2420 "output_row"."scope_out" := "delegation_row"."scope";
2421 "output_row"."disabled_out" := TRUE;
2422 "output_rows" := "output_rows" || "output_row";
2423 EXIT;
2424 ELSE
2425 "output_row"."scope_out" := NULL;
2426 "output_rows" := "output_rows" || "output_row";
2427 EXIT;
2428 END IF;
2429 EXIT WHEN "loop_member_id_v" NOTNULL;
2430 "output_row"."index" := "output_row"."index" + 1;
2431 END LOOP;
2432 "row_count" := array_upper("output_rows", 1);
2433 "i" := 1;
2434 "loop_v" := FALSE;
2435 LOOP
2436 "output_row" := "output_rows"["i"];
2437 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2438 IF "loop_v" THEN
2439 IF "i" + 1 = "row_count" THEN
2440 "output_row"."loop" := 'last';
2441 ELSIF "i" = "row_count" THEN
2442 "output_row"."loop" := 'repetition';
2443 ELSE
2444 "output_row"."loop" := 'intermediate';
2445 END IF;
2446 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2447 "output_row"."loop" := 'first';
2448 "loop_v" := TRUE;
2449 END IF;
2450 IF "scope_v" = 'unit' THEN
2451 "output_row"."participation" := NULL;
2452 END IF;
2453 RETURN NEXT "output_row";
2454 "i" := "i" + 1;
2455 END LOOP;
2456 RETURN;
2457 END;
2458 $$;
2461 CREATE OR REPLACE FUNCTION "get_initiatives_for_notification"
2462 ( "recipient_id_p" "member"."id"%TYPE )
2463 RETURNS SETOF "initiative_for_notification"
2464 LANGUAGE 'plpgsql' VOLATILE AS $$
2465 DECLARE
2466 "result_row" "initiative_for_notification"%ROWTYPE;
2467 "last_draft_id_v" "draft"."id"%TYPE;
2468 "last_suggestion_id_v" "suggestion"."id"%TYPE;
2469 BEGIN
2470 PERFORM "require_transaction_isolation"();
2471 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
2472 FOR "result_row" IN
2473 SELECT * FROM "initiative_for_notification"
2474 WHERE "recipient_id" = "recipient_id_p"
2475 LOOP
2476 SELECT "id" INTO "last_draft_id_v" FROM "draft"
2477 WHERE "draft"."initiative_id" = "result_row"."initiative_id"
2478 ORDER BY "id" DESC LIMIT 1;
2479 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
2480 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
2481 ORDER BY "id" DESC LIMIT 1;
2482 INSERT INTO "notification_initiative_sent"
2483 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
2484 VALUES (
2485 "recipient_id_p",
2486 "result_row"."initiative_id",
2487 "last_draft_id_v",
2488 "last_suggestion_id_v" )
2489 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
2490 "last_draft_id" = "last_draft_id_v",
2491 "last_suggestion_id" = "last_suggestion_id_v";
2492 RETURN NEXT "result_row";
2493 END LOOP;
2494 DELETE FROM "notification_initiative_sent"
2495 USING "initiative", "issue"
2496 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
2497 AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
2498 AND "issue"."id" = "initiative"."issue_id"
2499 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
2500 UPDATE "member" SET
2501 "notification_counter" = "notification_counter" + 1,
2502 "notification_sent" = now()
2503 WHERE "id" = "recipient_id_p";
2504 RETURN;
2505 END;
2506 $$;
2509 CREATE OR REPLACE FUNCTION "calculate_member_counts"()
2510 RETURNS VOID
2511 LANGUAGE 'plpgsql' VOLATILE AS $$
2512 BEGIN
2513 PERFORM "require_transaction_isolation"();
2514 DELETE FROM "member_count";
2515 INSERT INTO "member_count" ("total_count")
2516 SELECT "total_count" FROM "member_count_view";
2517 UPDATE "unit" SET "member_count" = "view"."member_count"
2518 FROM "unit_member_count" AS "view"
2519 WHERE "view"."unit_id" = "unit"."id";
2520 RETURN;
2521 END;
2522 $$;
2524 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"';
2527 CREATE FUNCTION "calculate_area_quorum"()
2528 RETURNS VOID
2529 LANGUAGE 'plpgsql' VOLATILE AS $$
2530 BEGIN
2531 PERFORM "dont_require_transaction_isolation"();
2532 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
2533 FROM "area_quorum" AS "view"
2534 WHERE "view"."area_id" = "area"."id";
2535 RETURN;
2536 END;
2537 $$;
2539 COMMENT ON FUNCTION "calculate_area_quorum"() IS 'Calculate column "issue_quorum" in table "area" from view "area_quorum"';
2542 DROP VIEW "remaining_harmonic_initiative_weight_summands";
2543 DROP VIEW "remaining_harmonic_supporter_weight";
2546 CREATE VIEW "remaining_harmonic_supporter_weight" AS
2547 SELECT
2548 "direct_interest_snapshot"."snapshot_id",
2549 "direct_interest_snapshot"."issue_id",
2550 "direct_interest_snapshot"."member_id",
2551 "direct_interest_snapshot"."weight" AS "weight_num",
2552 count("initiative"."id") AS "weight_den"
2553 FROM "issue"
2554 JOIN "direct_interest_snapshot"
2555 ON "issue"."latest_snapshot_id" = "direct_interest_snapshot"."snapshot_id"
2556 AND "issue"."id" = "direct_interest_snapshot"."issue_id"
2557 JOIN "initiative"
2558 ON "issue"."id" = "initiative"."issue_id"
2559 AND "initiative"."harmonic_weight" ISNULL
2560 JOIN "direct_supporter_snapshot"
2561 ON "issue"."latest_snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
2562 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
2563 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
2564 AND (
2565 "direct_supporter_snapshot"."satisfied" = TRUE OR
2566 coalesce("initiative"."admitted", FALSE) = FALSE
2567 )
2568 GROUP BY
2569 "direct_interest_snapshot"."snapshot_id",
2570 "direct_interest_snapshot"."issue_id",
2571 "direct_interest_snapshot"."member_id",
2572 "direct_interest_snapshot"."weight";
2575 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
2576 SELECT
2577 "initiative"."issue_id",
2578 "initiative"."id" AS "initiative_id",
2579 "initiative"."admitted",
2580 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
2581 "remaining_harmonic_supporter_weight"."weight_den"
2582 FROM "remaining_harmonic_supporter_weight"
2583 JOIN "initiative"
2584 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
2585 AND "initiative"."harmonic_weight" ISNULL
2586 JOIN "direct_supporter_snapshot"
2587 ON "remaining_harmonic_supporter_weight"."snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
2588 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
2589 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
2590 AND (
2591 "direct_supporter_snapshot"."satisfied" = TRUE OR
2592 coalesce("initiative"."admitted", FALSE) = FALSE
2593 )
2594 GROUP BY
2595 "initiative"."issue_id",
2596 "initiative"."id",
2597 "initiative"."admitted",
2598 "remaining_harmonic_supporter_weight"."weight_den";
2601 DROP FUNCTION "create_population_snapshot"
2602 ( "issue_id_p" "issue"."id"%TYPE );
2605 DROP FUNCTION "weight_of_added_delegations_for_population_snapshot"
2606 ( "issue_id_p" "issue"."id"%TYPE,
2607 "member_id_p" "member"."id"%TYPE,
2608 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE );
2611 DROP FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2612 ( "issue_id_p" "issue"."id"%TYPE,
2613 "member_id_p" "member"."id"%TYPE,
2614 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE );
2617 CREATE FUNCTION "weight_of_added_delegations_for_snapshot"
2618 ( "snapshot_id_p" "snapshot"."id"%TYPE,
2619 "issue_id_p" "issue"."id"%TYPE,
2620 "member_id_p" "member"."id"%TYPE,
2621 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2622 RETURNS "direct_interest_snapshot"."weight"%TYPE
2623 LANGUAGE 'plpgsql' VOLATILE AS $$
2624 DECLARE
2625 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2626 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2627 "weight_v" INT4;
2628 "sub_weight_v" INT4;
2629 BEGIN
2630 PERFORM "require_transaction_isolation"();
2631 "weight_v" := 0;
2632 FOR "issue_delegation_row" IN
2633 SELECT * FROM "issue_delegation"
2634 WHERE "trustee_id" = "member_id_p"
2635 AND "issue_id" = "issue_id_p"
2636 LOOP
2637 IF NOT EXISTS (
2638 SELECT NULL FROM "direct_interest_snapshot"
2639 WHERE "snapshot_id" = "snapshot_id_p"
2640 AND "issue_id" = "issue_id_p"
2641 AND "member_id" = "issue_delegation_row"."truster_id"
2642 ) AND NOT EXISTS (
2643 SELECT NULL FROM "delegating_interest_snapshot"
2644 WHERE "snapshot_id" = "snapshot_id_p"
2645 AND "issue_id" = "issue_id_p"
2646 AND "member_id" = "issue_delegation_row"."truster_id"
2647 ) THEN
2648 "delegate_member_ids_v" :=
2649 "member_id_p" || "delegate_member_ids_p";
2650 INSERT INTO "delegating_interest_snapshot" (
2651 "snapshot_id",
2652 "issue_id",
2653 "member_id",
2654 "scope",
2655 "delegate_member_ids"
2656 ) VALUES (
2657 "snapshot_id_p",
2658 "issue_id_p",
2659 "issue_delegation_row"."truster_id",
2660 "issue_delegation_row"."scope",
2661 "delegate_member_ids_v"
2662 );
2663 "sub_weight_v" := 1 +
2664 "weight_of_added_delegations_for_snapshot"(
2665 "snapshot_id_p",
2666 "issue_id_p",
2667 "issue_delegation_row"."truster_id",
2668 "delegate_member_ids_v"
2669 );
2670 UPDATE "delegating_interest_snapshot"
2671 SET "weight" = "sub_weight_v"
2672 WHERE "snapshot_id" = "snapshot_id_p"
2673 AND "issue_id" = "issue_id_p"
2674 AND "member_id" = "issue_delegation_row"."truster_id";
2675 "weight_v" := "weight_v" + "sub_weight_v";
2676 END IF;
2677 END LOOP;
2678 RETURN "weight_v";
2679 END;
2680 $$;
2682 COMMENT ON FUNCTION "weight_of_added_delegations_for_snapshot"
2683 ( "snapshot"."id"%TYPE,
2684 "issue"."id"%TYPE,
2685 "member"."id"%TYPE,
2686 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2687 IS 'Helper function for "fill_snapshot" function';
2690 DROP FUNCTION "create_interest_snapshot"
2691 ( "issue_id_p" "issue"."id"%TYPE );
2694 DROP FUNCTION "create_snapshot"
2695 ( "issue_id_p" "issue"."id"%TYPE );
2698 CREATE FUNCTION "take_snapshot"
2699 ( "issue_id_p" "issue"."id"%TYPE,
2700 "area_id_p" "area"."id"%TYPE = NULL )
2701 RETURNS "snapshot"."id"%TYPE
2702 LANGUAGE 'plpgsql' VOLATILE AS $$
2703 DECLARE
2704 "area_id_v" "area"."id"%TYPE;
2705 "unit_id_v" "unit"."id"%TYPE;
2706 "snapshot_id_v" "snapshot"."id"%TYPE;
2707 "issue_id_v" "issue"."id"%TYPE;
2708 "member_id_v" "member"."id"%TYPE;
2709 BEGIN
2710 IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN
2711 RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL';
2712 END IF;
2713 PERFORM "require_transaction_isolation"();
2714 IF "issue_id_p" ISNULL THEN
2715 "area_id_v" := "area_id_p";
2716 ELSE
2717 SELECT "area_id" INTO "area_id_v"
2718 FROM "issue" WHERE "id" = "issue_id_p";
2719 END IF;
2720 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_p";
2721 INSERT INTO "snapshot" ("area_id", "issue_id")
2722 VALUES ("area_id_v", "issue_id_p")
2723 RETURNING "id" INTO "snapshot_id_v";
2724 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
2725 SELECT "snapshot_id_v", "member_id"
2726 FROM "unit_member" WHERE "unit_id" = "unit_id_v";
2727 UPDATE "snapshot" SET
2728 "population" = (
2729 SELECT count(1) FROM "snapshot_population"
2730 WHERE "snapshot_id" = "snapshot_id_v"
2731 ) WHERE "id" = "snapshot_id_v";
2732 FOR "issue_id_v" IN
2733 SELECT "id" FROM "issue"
2734 WHERE CASE WHEN "issue_id_p" ISNULL THEN
2735 "area_id" = "area_id_p" AND
2736 "state" = 'admission'
2737 ELSE
2738 "id" = "issue_id_p"
2739 END
2740 LOOP
2741 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
2742 VALUES ("snapshot_id_v", "issue_id_v");
2743 INSERT INTO "direct_interest_snapshot"
2744 ("snapshot_id", "issue_id", "member_id")
2745 SELECT
2746 "snapshot_id_v" AS "snapshot_id",
2747 "issue_id_v" AS "issue_id",
2748 "member"."id" AS "member_id"
2749 FROM "issue"
2750 JOIN "area" ON "issue"."area_id" = "area"."id"
2751 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2752 JOIN "member" ON "interest"."member_id" = "member"."id"
2753 JOIN "privilege"
2754 ON "privilege"."unit_id" = "area"."unit_id"
2755 AND "privilege"."member_id" = "member"."id"
2756 WHERE "issue"."id" = "issue_id_v"
2757 AND "member"."active" AND "privilege"."voting_right";
2758 FOR "member_id_v" IN
2759 SELECT "member_id" FROM "direct_interest_snapshot"
2760 WHERE "snapshot_id" = "snapshot_id_v"
2761 AND "issue_id" = "issue_id_v"
2762 LOOP
2763 UPDATE "direct_interest_snapshot" SET
2764 "weight" = 1 +
2765 "weight_of_added_delegations_for_snapshot"(
2766 "snapshot_id_v",
2767 "issue_id_v",
2768 "member_id_v",
2769 '{}'
2770 )
2771 WHERE "snapshot_id" = "snapshot_id_v"
2772 AND "issue_id" = "issue_id_v"
2773 AND "member_id" = "member_id_v";
2774 END LOOP;
2775 INSERT INTO "direct_supporter_snapshot"
2776 ( "snapshot_id", "issue_id", "initiative_id", "member_id",
2777 "draft_id", "informed", "satisfied" )
2778 SELECT
2779 "snapshot_id_v" AS "snapshot_id",
2780 "issue_id_v" AS "issue_id",
2781 "initiative"."id" AS "initiative_id",
2782 "supporter"."member_id" AS "member_id",
2783 "supporter"."draft_id" AS "draft_id",
2784 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2785 NOT EXISTS (
2786 SELECT NULL FROM "critical_opinion"
2787 WHERE "initiative_id" = "initiative"."id"
2788 AND "member_id" = "supporter"."member_id"
2789 ) AS "satisfied"
2790 FROM "initiative"
2791 JOIN "supporter"
2792 ON "supporter"."initiative_id" = "initiative"."id"
2793 JOIN "current_draft"
2794 ON "initiative"."id" = "current_draft"."initiative_id"
2795 JOIN "direct_interest_snapshot"
2796 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"
2797 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"
2798 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
2799 WHERE "initiative"."issue_id" = "issue_id_v";
2800 DELETE FROM "temporary_suggestion_counts";
2801 INSERT INTO "temporary_suggestion_counts"
2802 ( "id",
2803 "minus2_unfulfilled_count", "minus2_fulfilled_count",
2804 "minus1_unfulfilled_count", "minus1_fulfilled_count",
2805 "plus1_unfulfilled_count", "plus1_fulfilled_count",
2806 "plus2_unfulfilled_count", "plus2_fulfilled_count" )
2807 SELECT
2808 "suggestion"."id",
2809 ( SELECT coalesce(sum("di"."weight"), 0)
2810 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2811 ON "di"."snapshot_id" = "snapshot_id_v"
2812 AND "di"."issue_id" = "issue_id_v"
2813 AND "di"."member_id" = "opinion"."member_id"
2814 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2815 AND "opinion"."degree" = -2
2816 AND "opinion"."fulfilled" = FALSE
2817 ) AS "minus2_unfulfilled_count",
2818 ( SELECT coalesce(sum("di"."weight"), 0)
2819 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2820 ON "di"."snapshot_id" = "snapshot_id_v"
2821 AND "di"."issue_id" = "issue_id_v"
2822 AND "di"."member_id" = "opinion"."member_id"
2823 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2824 AND "opinion"."degree" = -2
2825 AND "opinion"."fulfilled" = TRUE
2826 ) AS "minus2_fulfilled_count",
2827 ( SELECT coalesce(sum("di"."weight"), 0)
2828 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2829 ON "di"."snapshot_id" = "snapshot_id_v"
2830 AND "di"."issue_id" = "issue_id_v"
2831 AND "di"."member_id" = "opinion"."member_id"
2832 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2833 AND "opinion"."degree" = -1
2834 AND "opinion"."fulfilled" = FALSE
2835 ) AS "minus1_unfulfilled_count",
2836 ( SELECT coalesce(sum("di"."weight"), 0)
2837 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2838 ON "di"."snapshot_id" = "snapshot_id_v"
2839 AND "di"."issue_id" = "issue_id_v"
2840 AND "di"."member_id" = "opinion"."member_id"
2841 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2842 AND "opinion"."degree" = -1
2843 AND "opinion"."fulfilled" = TRUE
2844 ) AS "minus1_fulfilled_count",
2845 ( SELECT coalesce(sum("di"."weight"), 0)
2846 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2847 ON "di"."snapshot_id" = "snapshot_id_v"
2848 AND "di"."issue_id" = "issue_id_v"
2849 AND "di"."member_id" = "opinion"."member_id"
2850 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2851 AND "opinion"."degree" = 1
2852 AND "opinion"."fulfilled" = FALSE
2853 ) AS "plus1_unfulfilled_count",
2854 ( SELECT coalesce(sum("di"."weight"), 0)
2855 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2856 ON "di"."snapshot_id" = "snapshot_id_v"
2857 AND "di"."issue_id" = "issue_id_v"
2858 AND "di"."member_id" = "opinion"."member_id"
2859 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2860 AND "opinion"."degree" = 1
2861 AND "opinion"."fulfilled" = TRUE
2862 ) AS "plus1_fulfilled_count",
2863 ( SELECT coalesce(sum("di"."weight"), 0)
2864 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2865 ON "di"."snapshot_id" = "snapshot_id_v"
2866 AND "di"."issue_id" = "issue_id_v"
2867 AND "di"."member_id" = "opinion"."member_id"
2868 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2869 AND "opinion"."degree" = 2
2870 AND "opinion"."fulfilled" = FALSE
2871 ) AS "plus2_unfulfilled_count",
2872 ( SELECT coalesce(sum("di"."weight"), 0)
2873 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2874 ON "di"."snapshot_id" = "snapshot_id_v"
2875 AND "di"."issue_id" = "issue_id_v"
2876 AND "di"."member_id" = "opinion"."member_id"
2877 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2878 AND "opinion"."degree" = 2
2879 AND "opinion"."fulfilled" = TRUE
2880 ) AS "plus2_fulfilled_count"
2881 FROM "suggestion" JOIN "initiative"
2882 ON "suggestion"."initiative_id" = "initiative"."id"
2883 WHERE "initiative"."issue_id" = "issue_id_v";
2884 END LOOP;
2885 RETURN "snapshot_id_v";
2886 END;
2887 $$;
2889 COMMENT ON FUNCTION "take_snapshot"
2890 ( "issue"."id"%TYPE,
2891 "area"."id"%TYPE )
2892 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.';
2895 DROP FUNCTION "set_snapshot_event"
2896 ( "issue_id_p" "issue"."id"%TYPE,
2897 "event_p" "snapshot_event" );
2900 CREATE FUNCTION "finish_snapshot"
2901 ( "issue_id_p" "issue"."id"%TYPE )
2902 RETURNS VOID
2903 LANGUAGE 'plpgsql' VOLATILE AS $$
2904 DECLARE
2905 "snapshot_id_v" "snapshot"."id"%TYPE;
2906 BEGIN
2907 -- NOTE: function does not require snapshot isolation but we don't call
2908 -- "dont_require_snapshot_isolation" here because this function is
2909 -- also invoked by "check_issue"
2910 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
2911 SELECT "id" INTO "snapshot_id_v" FROM "snapshot"
2912 ORDER BY "id" DESC LIMIT 1;
2913 UPDATE "issue" SET
2914 "calculated" = "snapshot"."calculated",
2915 "latest_snapshot_id" = "snapshot_id_v",
2916 "population" = "snapshot"."population"
2917 FROM "snapshot"
2918 WHERE "issue"."id" = "issue_id_p"
2919 AND "snapshot"."id" = "snapshot_id_v";
2920 UPDATE "initiative" SET
2921 "supporter_count" = (
2922 SELECT coalesce(sum("di"."weight"), 0)
2923 FROM "direct_interest_snapshot" AS "di"
2924 JOIN "direct_supporter_snapshot" AS "ds"
2925 ON "di"."member_id" = "ds"."member_id"
2926 WHERE "di"."snapshot_id" = "snapshot_id_v"
2927 AND "di"."issue_id" = "issue_id_p"
2928 AND "ds"."snapshot_id" = "snapshot_id_v"
2929 AND "ds"."initiative_id" = "initiative"."id"
2930 ),
2931 "informed_supporter_count" = (
2932 SELECT coalesce(sum("di"."weight"), 0)
2933 FROM "direct_interest_snapshot" AS "di"
2934 JOIN "direct_supporter_snapshot" AS "ds"
2935 ON "di"."member_id" = "ds"."member_id"
2936 WHERE "di"."snapshot_id" = "snapshot_id_v"
2937 AND "di"."issue_id" = "issue_id_p"
2938 AND "ds"."snapshot_id" = "snapshot_id_v"
2939 AND "ds"."initiative_id" = "initiative"."id"
2940 AND "ds"."informed"
2941 ),
2942 "satisfied_supporter_count" = (
2943 SELECT coalesce(sum("di"."weight"), 0)
2944 FROM "direct_interest_snapshot" AS "di"
2945 JOIN "direct_supporter_snapshot" AS "ds"
2946 ON "di"."member_id" = "ds"."member_id"
2947 WHERE "di"."snapshot_id" = "snapshot_id_v"
2948 AND "di"."issue_id" = "issue_id_p"
2949 AND "ds"."snapshot_id" = "snapshot_id_v"
2950 AND "ds"."initiative_id" = "initiative"."id"
2951 AND "ds"."satisfied"
2952 ),
2953 "satisfied_informed_supporter_count" = (
2954 SELECT coalesce(sum("di"."weight"), 0)
2955 FROM "direct_interest_snapshot" AS "di"
2956 JOIN "direct_supporter_snapshot" AS "ds"
2957 ON "di"."member_id" = "ds"."member_id"
2958 WHERE "di"."snapshot_id" = "snapshot_id_v"
2959 AND "di"."issue_id" = "issue_id_p"
2960 AND "ds"."snapshot_id" = "snapshot_id_v"
2961 AND "ds"."initiative_id" = "initiative"."id"
2962 AND "ds"."informed"
2963 AND "ds"."satisfied"
2964 )
2965 WHERE "issue_id" = "issue_id_p";
2966 UPDATE "suggestion" SET
2967 "minus2_unfulfilled_count" = "temp"."minus2_unfulfilled_count",
2968 "minus2_fulfilled_count" = "temp"."minus2_fulfilled_count",
2969 "minus1_unfulfilled_count" = "temp"."minus1_unfulfilled_count",
2970 "minus1_fulfilled_count" = "temp"."minus1_fulfilled_count",
2971 "plus1_unfulfilled_count" = "temp"."plus1_unfulfilled_count",
2972 "plus1_fulfilled_count" = "temp"."plus1_fulfilled_count",
2973 "plus2_unfulfilled_count" = "temp"."plus2_unfulfilled_count",
2974 "plus2_fulfilled_count" = "temp"."plus2_fulfilled_count"
2975 FROM "temporary_suggestion_counts" AS "temp", "initiative"
2976 WHERE "temp"."id" = "suggestion"."id"
2977 AND "initiative"."issue_id" = "issue_id_p"
2978 AND "suggestion"."initiative_id" = "initiative"."id";
2979 DELETE FROM "temporary_suggestion_counts";
2980 RETURN;
2981 END;
2982 $$;
2984 COMMENT ON FUNCTION "finish_snapshot"
2985 ( "issue"."id"%TYPE )
2986 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)';
2989 CREATE FUNCTION "issue_admission"
2990 ( "area_id_p" "area"."id"%TYPE )
2991 RETURNS BOOLEAN
2992 LANGUAGE 'plpgsql' VOLATILE AS $$
2993 DECLARE
2994 "issue_id_v" "issue"."id"%TYPE;
2995 BEGIN
2996 PERFORM "dont_require_transaction_isolation"();
2997 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
2998 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
2999 FROM "area_quorum" AS "view"
3000 WHERE "area"."id" = "view"."area_id"
3001 AND "area"."id" = "area_id_p";
3002 SELECT "id" INTO "issue_id_v" FROM "issue_for_admission"
3003 WHERE "area_id" = "area_id_p";
3004 IF "issue_id_v" ISNULL THEN RETURN FALSE; END IF;
3005 UPDATE "issue" SET
3006 "admission_snapshot_id" = "latest_snapshot_id",
3007 "state" = 'discussion',
3008 "accepted" = now(),
3009 "phase_finished" = NULL
3010 WHERE "id" = "issue_id_v";
3011 RETURN TRUE;
3012 END;
3013 $$;
3015 COMMENT ON FUNCTION "issue_admission"
3016 ( "area"."id"%TYPE )
3017 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';
3020 CREATE OR REPLACE FUNCTION "check_issue"
3021 ( "issue_id_p" "issue"."id"%TYPE,
3022 "persist" "check_issue_persistence" )
3023 RETURNS "check_issue_persistence"
3024 LANGUAGE 'plpgsql' VOLATILE AS $$
3025 DECLARE
3026 "issue_row" "issue"%ROWTYPE;
3027 "last_calculated_v" "snapshot"."calculated"%TYPE;
3028 "policy_row" "policy"%ROWTYPE;
3029 "initiative_row" "initiative"%ROWTYPE;
3030 "state_v" "issue_state";
3031 BEGIN
3032 PERFORM "require_transaction_isolation"();
3033 IF "persist" ISNULL THEN
3034 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
3035 FOR UPDATE;
3036 SELECT "calculated" INTO "last_calculated_v"
3037 FROM "snapshot" JOIN "snapshot_issue"
3038 ON "snapshot"."id" = "snapshot_issue"."snapshot_id"
3039 WHERE "snapshot_issue"."issue_id" = "issue_id_p";
3040 IF "issue_row"."closed" NOTNULL THEN
3041 RETURN NULL;
3042 END IF;
3043 "persist"."state" := "issue_row"."state";
3044 IF
3045 ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=
3046 "issue_row"."created" + "issue_row"."max_admission_time" ) OR
3047 ( "issue_row"."state" = 'discussion' AND now() >=
3048 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
3049 ( "issue_row"."state" = 'verification' AND now() >=
3050 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
3051 ( "issue_row"."state" = 'voting' AND now() >=
3052 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
3053 THEN
3054 "persist"."phase_finished" := TRUE;
3055 ELSE
3056 "persist"."phase_finished" := FALSE;
3057 END IF;
3058 IF
3059 NOT EXISTS (
3060 -- all initiatives are revoked
3061 SELECT NULL FROM "initiative"
3062 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3063 ) AND (
3064 -- and issue has not been accepted yet
3065 "persist"."state" = 'admission' OR
3066 -- or verification time has elapsed
3067 ( "persist"."state" = 'verification' AND
3068 "persist"."phase_finished" ) OR
3069 -- or no initiatives have been revoked lately
3070 NOT EXISTS (
3071 SELECT NULL FROM "initiative"
3072 WHERE "issue_id" = "issue_id_p"
3073 AND now() < "revoked" + "issue_row"."verification_time"
3074 )
3075 )
3076 THEN
3077 "persist"."issue_revoked" := TRUE;
3078 ELSE
3079 "persist"."issue_revoked" := FALSE;
3080 END IF;
3081 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
3082 UPDATE "issue" SET "phase_finished" = now()
3083 WHERE "id" = "issue_row"."id";
3084 RETURN "persist";
3085 ELSIF
3086 "persist"."state" IN ('admission', 'discussion', 'verification')
3087 THEN
3088 RETURN "persist";
3089 ELSE
3090 RETURN NULL;
3091 END IF;
3092 END IF;
3093 IF
3094 "persist"."state" IN ('admission', 'discussion', 'verification') AND
3095 coalesce("persist"."snapshot_created", FALSE) = FALSE
3096 THEN
3097 IF "persist"."state" != 'admission' THEN
3098 PERFORM "take_snapshot"("issue_id_p");
3099 PERFORM "finish_snapshot"("issue_id_p");
3100 END IF;
3101 "persist"."snapshot_created" = TRUE;
3102 IF "persist"."phase_finished" THEN
3103 IF "persist"."state" = 'admission' THEN
3104 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id";
3105 ELSIF "persist"."state" = 'discussion' THEN
3106 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id";
3107 ELSIF "persist"."state" = 'verification' THEN
3108 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id";
3109 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3110 SELECT * INTO "policy_row" FROM "policy"
3111 WHERE "id" = "issue_row"."policy_id";
3112 FOR "initiative_row" IN
3113 SELECT * FROM "initiative"
3114 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3115 FOR UPDATE
3116 LOOP
3117 IF
3118 "initiative_row"."polling" OR (
3119 "initiative_row"."satisfied_supporter_count" >
3120 "policy_row"."initiative_quorum" AND
3121 "initiative_row"."satisfied_supporter_count" *
3122 "policy_row"."initiative_quorum_den" >=
3123 "issue_row"."population" * "policy_row"."initiative_quorum_num"
3124 )
3125 THEN
3126 UPDATE "initiative" SET "admitted" = TRUE
3127 WHERE "id" = "initiative_row"."id";
3128 ELSE
3129 UPDATE "initiative" SET "admitted" = FALSE
3130 WHERE "id" = "initiative_row"."id";
3131 END IF;
3132 END LOOP;
3133 END IF;
3134 END IF;
3135 RETURN "persist";
3136 END IF;
3137 IF
3138 "persist"."state" IN ('admission', 'discussion', 'verification') AND
3139 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
3140 THEN
3141 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
3142 "persist"."harmonic_weights_set" = TRUE;
3143 IF
3144 "persist"."phase_finished" OR
3145 "persist"."issue_revoked" OR
3146 "persist"."state" = 'admission'
3147 THEN
3148 RETURN "persist";
3149 ELSE
3150 RETURN NULL;
3151 END IF;
3152 END IF;
3153 IF "persist"."issue_revoked" THEN
3154 IF "persist"."state" = 'admission' THEN
3155 "state_v" := 'canceled_revoked_before_accepted';
3156 ELSIF "persist"."state" = 'discussion' THEN
3157 "state_v" := 'canceled_after_revocation_during_discussion';
3158 ELSIF "persist"."state" = 'verification' THEN
3159 "state_v" := 'canceled_after_revocation_during_verification';
3160 END IF;
3161 UPDATE "issue" SET
3162 "state" = "state_v",
3163 "closed" = "phase_finished",
3164 "phase_finished" = NULL
3165 WHERE "id" = "issue_id_p";
3166 RETURN NULL;
3167 END IF;
3168 IF "persist"."state" = 'admission' THEN
3169 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
3170 FOR UPDATE;
3171 IF "issue_row"."phase_finished" NOTNULL THEN
3172 UPDATE "issue" SET
3173 "state" = 'canceled_issue_not_accepted',
3174 "closed" = "phase_finished",
3175 "phase_finished" = NULL
3176 WHERE "id" = "issue_id_p";
3177 END IF;
3178 RETURN NULL;
3179 END IF;
3180 IF "persist"."phase_finished" THEN
3181 IF "persist"."state" = 'discussion' THEN
3182 UPDATE "issue" SET
3183 "state" = 'verification',
3184 "half_frozen" = "phase_finished",
3185 "phase_finished" = NULL
3186 WHERE "id" = "issue_id_p";
3187 RETURN NULL;
3188 END IF;
3189 IF "persist"."state" = 'verification' THEN
3190 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
3191 FOR UPDATE;
3192 SELECT * INTO "policy_row" FROM "policy"
3193 WHERE "id" = "issue_row"."policy_id";
3194 IF EXISTS (
3195 SELECT NULL FROM "initiative"
3196 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
3197 ) THEN
3198 UPDATE "issue" SET
3199 "state" = 'voting',
3200 "fully_frozen" = "phase_finished",
3201 "phase_finished" = NULL
3202 WHERE "id" = "issue_id_p";
3203 ELSE
3204 UPDATE "issue" SET
3205 "state" = 'canceled_no_initiative_admitted',
3206 "fully_frozen" = "phase_finished",
3207 "closed" = "phase_finished",
3208 "phase_finished" = NULL
3209 WHERE "id" = "issue_id_p";
3210 -- NOTE: The following DELETE statements have effect only when
3211 -- issue state has been manipulated
3212 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
3213 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
3214 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3215 END IF;
3216 RETURN NULL;
3217 END IF;
3218 IF "persist"."state" = 'voting' THEN
3219 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
3220 PERFORM "close_voting"("issue_id_p");
3221 "persist"."closed_voting" = TRUE;
3222 RETURN "persist";
3223 END IF;
3224 PERFORM "calculate_ranks"("issue_id_p");
3225 RETURN NULL;
3226 END IF;
3227 END IF;
3228 RAISE WARNING 'should not happen';
3229 RETURN NULL;
3230 END;
3231 $$;
3234 CREATE OR REPLACE FUNCTION "check_everything"()
3235 RETURNS VOID
3236 LANGUAGE 'plpgsql' VOLATILE AS $$
3237 DECLARE
3238 "area_id_v" "area"."id"%TYPE;
3239 "snapshot_id_v" "snapshot"."id"%TYPE;
3240 "issue_id_v" "issue"."id"%TYPE;
3241 "persist_v" "check_issue_persistence";
3242 BEGIN
3243 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
3244 DELETE FROM "expired_session";
3245 DELETE FROM "expired_token";
3246 DELETE FROM "expired_snapshot";
3247 PERFORM "check_activity"();
3248 PERFORM "calculate_member_counts"();
3249 FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP
3250 SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v";
3251 PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"
3252 WHERE "snapshot_id" = "snapshot_id_v";
3253 LOOP
3254 EXIT WHEN "issue_admission"("area_id_v") = FALSE;
3255 END LOOP;
3256 END LOOP;
3257 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
3258 "persist_v" := NULL;
3259 LOOP
3260 "persist_v" := "check_issue"("issue_id_v", "persist_v");
3261 EXIT WHEN "persist_v" ISNULL;
3262 END LOOP;
3263 END LOOP;
3264 RETURN;
3265 END;
3266 $$;
3268 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';
3271 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
3272 RETURNS VOID
3273 LANGUAGE 'plpgsql' VOLATILE AS $$
3274 BEGIN
3275 IF EXISTS (
3276 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
3277 ) THEN
3278 -- override protection triggers:
3279 INSERT INTO "temporary_transaction_data" ("key", "value")
3280 VALUES ('override_protection_triggers', TRUE::TEXT);
3281 -- clean data:
3282 DELETE FROM "delegating_voter"
3283 WHERE "issue_id" = "issue_id_p";
3284 DELETE FROM "direct_voter"
3285 WHERE "issue_id" = "issue_id_p";
3286 DELETE FROM "delegating_interest_snapshot"
3287 WHERE "issue_id" = "issue_id_p";
3288 DELETE FROM "direct_interest_snapshot"
3289 WHERE "issue_id" = "issue_id_p";
3290 DELETE FROM "non_voter"
3291 WHERE "issue_id" = "issue_id_p";
3292 DELETE FROM "delegation"
3293 WHERE "issue_id" = "issue_id_p";
3294 DELETE FROM "supporter"
3295 USING "initiative" -- NOTE: due to missing index on issue_id
3296 WHERE "initiative"."issue_id" = "issue_id_p"
3297 AND "supporter"."initiative_id" = "initiative_id";
3298 -- mark issue as cleaned:
3299 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
3300 -- finish overriding protection triggers (avoids garbage):
3301 DELETE FROM "temporary_transaction_data"
3302 WHERE "key" = 'override_protection_triggers';
3303 END IF;
3304 RETURN;
3305 END;
3306 $$;
3309 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
3310 RETURNS VOID
3311 LANGUAGE 'plpgsql' VOLATILE AS $$
3312 BEGIN
3313 UPDATE "member" SET
3314 "last_login" = NULL,
3315 "last_delegation_check" = NULL,
3316 "login" = NULL,
3317 "password" = NULL,
3318 "authority" = NULL,
3319 "authority_uid" = NULL,
3320 "authority_login" = NULL,
3321 "deleted" = coalesce("deleted", now()),
3322 "locked" = TRUE,
3323 "active" = FALSE,
3324 "notify_email" = NULL,
3325 "notify_email_unconfirmed" = NULL,
3326 "notify_email_secret" = NULL,
3327 "notify_email_secret_expiry" = NULL,
3328 "notify_email_lock_expiry" = NULL,
3329 "disable_notifications" = TRUE,
3330 "notification_counter" = DEFAULT,
3331 "notification_sample_size" = 0,
3332 "notification_dow" = NULL,
3333 "notification_hour" = NULL,
3334 "notification_sent" = NULL,
3335 "login_recovery_expiry" = NULL,
3336 "password_reset_secret" = NULL,
3337 "password_reset_secret_expiry" = NULL,
3338 "location" = NULL
3339 WHERE "id" = "member_id_p";
3340 -- "text_search_data" is updated by triggers
3341 DELETE FROM "member_settings" WHERE "member_id" = "member_id_p";
3342 DELETE FROM "member_profile" WHERE "member_id" = "member_id_p";
3343 DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";
3344 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
3345 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
3346 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
3347 DELETE FROM "session" WHERE "member_id" = "member_id_p";
3348 DELETE FROM "member_application" WHERE "member_id" = "member_id_p";
3349 DELETE FROM "token" WHERE "member_id" = "member_id_p";
3350 DELETE FROM "subscription" WHERE "member_id" = "member_id_p";
3351 DELETE FROM "ignored_area" WHERE "member_id" = "member_id_p";
3352 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
3353 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
3354 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
3355 DELETE FROM "direct_voter" USING "issue"
3356 WHERE "direct_voter"."issue_id" = "issue"."id"
3357 AND "issue"."closed" ISNULL
3358 AND "member_id" = "member_id_p";
3359 DELETE FROM "notification_initiative_sent" WHERE "member_id" = "member_id_p";
3360 RETURN;
3361 END;
3362 $$;
3365 CREATE OR REPLACE FUNCTION "delete_private_data"()
3366 RETURNS VOID
3367 LANGUAGE 'plpgsql' VOLATILE AS $$
3368 BEGIN
3369 DELETE FROM "temporary_transaction_data";
3370 DELETE FROM "temporary_suggestion_counts";
3371 DELETE FROM "member" WHERE "activated" ISNULL;
3372 UPDATE "member" SET
3373 "invite_code" = NULL,
3374 "invite_code_expiry" = NULL,
3375 "admin_comment" = NULL,
3376 "last_login" = NULL,
3377 "last_delegation_check" = NULL,
3378 "login" = NULL,
3379 "password" = NULL,
3380 "authority" = NULL,
3381 "authority_uid" = NULL,
3382 "authority_login" = NULL,
3383 "lang" = NULL,
3384 "notify_email" = NULL,
3385 "notify_email_unconfirmed" = NULL,
3386 "notify_email_secret" = NULL,
3387 "notify_email_secret_expiry" = NULL,
3388 "notify_email_lock_expiry" = NULL,
3389 "disable_notifications" = TRUE,
3390 "notification_counter" = DEFAULT,
3391 "notification_sample_size" = 0,
3392 "notification_dow" = NULL,
3393 "notification_hour" = NULL,
3394 "notification_sent" = NULL,
3395 "login_recovery_expiry" = NULL,
3396 "password_reset_secret" = NULL,
3397 "password_reset_secret_expiry" = NULL,
3398 "location" = NULL;
3399 -- "text_search_data" is updated by triggers
3400 DELETE FROM "verification";
3401 DELETE FROM "member_settings";
3402 DELETE FROM "member_useterms";
3403 DELETE FROM "member_profile";
3404 DELETE FROM "rendered_member_statement";
3405 DELETE FROM "member_image";
3406 DELETE FROM "contact";
3407 DELETE FROM "ignored_member";
3408 DELETE FROM "session";
3409 DELETE FROM "system_application";
3410 DELETE FROM "system_application_redirect_uri";
3411 DELETE FROM "dynamic_application_scope";
3412 DELETE FROM "member_application";
3413 DELETE FROM "token";
3414 DELETE FROM "subscription";
3415 DELETE FROM "ignored_area";
3416 DELETE FROM "ignored_initiative";
3417 DELETE FROM "non_voter";
3418 DELETE FROM "direct_voter" USING "issue"
3419 WHERE "direct_voter"."issue_id" = "issue"."id"
3420 AND "issue"."closed" ISNULL;
3421 DELETE FROM "event_processed";
3422 DELETE FROM "notification_initiative_sent";
3423 DELETE FROM "newsletter";
3424 RETURN;
3425 END;
3426 $$;
3429 CREATE TEMPORARY TABLE "old_snapshot" AS
3430 SELECT "ordered".*, row_number() OVER () AS "snapshot_id"
3431 FROM (
3432 SELECT * FROM (
3433 SELECT
3434 "id" AS "issue_id",
3435 'end_of_admission'::"snapshot_event" AS "event",
3436 "accepted" AS "calculated"
3437 FROM "issue" WHERE "accepted" NOTNULL
3438 UNION ALL
3439 SELECT
3440 "id" AS "issue_id",
3441 'half_freeze'::"snapshot_event" AS "event",
3442 "half_frozen" AS "calculated"
3443 FROM "issue" WHERE "half_frozen" NOTNULL
3444 UNION ALL
3445 SELECT
3446 "id" AS "issue_id",
3447 'full_freeze'::"snapshot_event" AS "event",
3448 "fully_frozen" AS "calculated"
3449 FROM "issue" WHERE "fully_frozen" NOTNULL
3450 ) AS "unordered"
3451 ORDER BY "calculated", "issue_id", "event"
3452 ) AS "ordered";
3455 INSERT INTO "snapshot" ("id", "calculated", "population", "area_id", "issue_id")
3456 SELECT
3457 "old_snapshot"."snapshot_id" AS "id",
3458 "old_snapshot"."calculated",
3459 ( SELECT COALESCE(sum("weight"), 0)
3460 FROM "direct_population_snapshot" "dps"
3461 WHERE "dps"."issue_id" = "old_snapshot"."issue_id"
3462 AND "dps"."event" = "old_snapshot"."event"
3463 ) AS "population",
3464 "issue"."area_id" AS "area_id",
3465 "issue"."id" AS "issue_id"
3466 FROM "old_snapshot" JOIN "issue"
3467 ON "old_snapshot"."issue_id" = "issue"."id";
3470 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
3471 SELECT "id" AS "snapshot_id", "issue_id" FROM "snapshot";
3474 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
3475 SELECT
3476 "old_snapshot"."snapshot_id",
3477 "direct_population_snapshot"."member_id"
3478 FROM "old_snapshot" JOIN "direct_population_snapshot"
3479 ON "old_snapshot"."issue_id" = "direct_population_snapshot"."issue_id"
3480 AND "old_snapshot"."event" = "direct_population_snapshot"."event";
3482 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
3483 SELECT
3484 "old_snapshot"."snapshot_id",
3485 "delegating_population_snapshot"."member_id"
3486 FROM "old_snapshot" JOIN "delegating_population_snapshot"
3487 ON "old_snapshot"."issue_id" = "delegating_population_snapshot"."issue_id"
3488 AND "old_snapshot"."event" = "delegating_population_snapshot"."event";
3491 INSERT INTO "direct_interest_snapshot"
3492 ("snapshot_id", "issue_id", "member_id", "weight")
3493 SELECT
3494 "old_snapshot"."snapshot_id",
3495 "old_snapshot"."issue_id",
3496 "direct_interest_snapshot_old"."member_id",
3497 "direct_interest_snapshot_old"."weight"
3498 FROM "old_snapshot" JOIN "direct_interest_snapshot_old"
3499 ON "old_snapshot"."issue_id" = "direct_interest_snapshot_old"."issue_id"
3500 AND "old_snapshot"."event" = "direct_interest_snapshot_old"."event";
3502 INSERT INTO "delegating_interest_snapshot"
3503 ( "snapshot_id", "issue_id",
3504 "member_id", "weight", "scope", "delegate_member_ids" )
3505 SELECT
3506 "old_snapshot"."snapshot_id",
3507 "old_snapshot"."issue_id",
3508 "delegating_interest_snapshot_old"."member_id",
3509 "delegating_interest_snapshot_old"."weight",
3510 "delegating_interest_snapshot_old"."scope",
3511 "delegating_interest_snapshot_old"."delegate_member_ids"
3512 FROM "old_snapshot" JOIN "delegating_interest_snapshot_old"
3513 ON "old_snapshot"."issue_id" = "delegating_interest_snapshot_old"."issue_id"
3514 AND "old_snapshot"."event" = "delegating_interest_snapshot_old"."event";
3516 INSERT INTO "direct_supporter_snapshot"
3517 ( "snapshot_id", "issue_id",
3518 "initiative_id", "member_id", "draft_id", "informed", "satisfied" )
3519 SELECT
3520 "old_snapshot"."snapshot_id",
3521 "old_snapshot"."issue_id",
3522 "direct_supporter_snapshot_old"."initiative_id",
3523 "direct_supporter_snapshot_old"."member_id",
3524 "direct_supporter_snapshot_old"."draft_id",
3525 "direct_supporter_snapshot_old"."informed",
3526 "direct_supporter_snapshot_old"."satisfied"
3527 FROM "old_snapshot" JOIN "direct_supporter_snapshot_old"
3528 ON "old_snapshot"."issue_id" = "direct_supporter_snapshot_old"."issue_id"
3529 AND "old_snapshot"."event" = "direct_supporter_snapshot_old"."event";
3532 ALTER TABLE "issue" DISABLE TRIGGER USER; -- NOTE: required to modify table later
3534 UPDATE "issue" SET "latest_snapshot_id" = "snapshot"."id"
3535 FROM (
3536 SELECT DISTINCT ON ("issue_id") "issue_id", "id"
3537 FROM "snapshot" ORDER BY "issue_id", "id" DESC
3538 ) AS "snapshot"
3539 WHERE "snapshot"."issue_id" = "issue"."id";
3541 UPDATE "issue" SET "admission_snapshot_id" = "old_snapshot"."snapshot_id"
3542 FROM "old_snapshot"
3543 WHERE "old_snapshot"."issue_id" = "issue"."id"
3544 AND "old_snapshot"."event" = 'end_of_admission';
3546 UPDATE "issue" SET "half_freeze_snapshot_id" = "old_snapshot"."snapshot_id"
3547 FROM "old_snapshot"
3548 WHERE "old_snapshot"."issue_id" = "issue"."id"
3549 AND "old_snapshot"."event" = 'half_freeze';
3551 UPDATE "issue" SET "full_freeze_snapshot_id" = "old_snapshot"."snapshot_id"
3552 FROM "old_snapshot"
3553 WHERE "old_snapshot"."issue_id" = "issue"."id"
3554 AND "old_snapshot"."event" = 'full_freeze';
3556 ALTER TABLE "issue" ENABLE TRIGGER USER;
3559 DROP TABLE "old_snapshot";
3561 DROP TABLE "direct_supporter_snapshot_old";
3562 DROP TABLE "delegating_interest_snapshot_old";
3563 DROP TABLE "direct_interest_snapshot_old";
3564 DROP TABLE "delegating_population_snapshot";
3565 DROP TABLE "direct_population_snapshot";
3568 DROP VIEW "open_issue";
3571 ALTER TABLE "issue" DROP COLUMN "latest_snapshot_event";
3574 CREATE VIEW "open_issue" AS
3575 SELECT * FROM "issue" WHERE "closed" ISNULL;
3577 COMMENT ON VIEW "open_issue" IS 'All open issues';
3580 -- NOTE: create "issue_for_admission" view after altering table "issue"
3581 CREATE VIEW "issue_for_admission" AS
3582 SELECT DISTINCT ON ("issue"."area_id")
3583 "issue".*,
3584 max("initiative"."supporter_count") AS "max_supporter_count"
3585 FROM "issue"
3586 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
3587 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
3588 JOIN "area" ON "issue"."area_id" = "area"."id"
3589 WHERE "issue"."state" = 'admission'::"issue_state"
3590 AND now() >= "issue"."created" + "issue"."min_admission_time"
3591 AND "initiative"."supporter_count" >= "policy"."issue_quorum"
3592 AND "initiative"."supporter_count" * "policy"."issue_quorum_den" >=
3593 "issue"."population" * "policy"."issue_quorum_num"
3594 AND "initiative"."supporter_count" >= "area"."issue_quorum"
3595 AND "initiative"."revoked" ISNULL
3596 GROUP BY "issue"."id"
3597 ORDER BY "issue"."area_id", "max_supporter_count" DESC, "issue"."id";
3599 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';
3602 DROP TYPE "snapshot_event";
3605 ALTER TABLE "issue" ADD CONSTRAINT "snapshot_required" CHECK (
3606 ("half_frozen" ISNULL OR "half_freeze_snapshot_id" NOTNULL) AND
3607 ("fully_frozen" ISNULL OR "full_freeze_snapshot_id" NOTNULL) );
3610 COMMIT;