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