liquid_feedback_core
view update/core-update.v3.2.2-v4.0.0.sql @ 553:85f25c413238
Bugfix in update script to version 4.0.0: set new "policy_id" column in "event" table
| author | jbe |
|---|---|
| date | Sat Sep 16 21:16:16 2017 +0200 (2017-09-16) |
| parents | a676d305502f |
| children | 3e7ad7233404 |
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
724 "unit_id" = "area"."unit_id",
725 "area_id" = "issue"."area_id",
726 "policy_id" = "issue"."policy_id"
727 FROM "issue", "area"
728 WHERE "issue"."id" = "event"."issue_id" AND "area"."id" = "issue"."area_id";
730 ALTER TABLE "event" ADD CONSTRAINT "constr_for_issue_state_changed" CHECK (
731 "event" != 'issue_state_changed' OR (
732 "member_id" ISNULL AND
733 "other_member_id" ISNULL AND
734 "scope" ISNULL AND
735 "unit_id" NOTNULL AND
736 "area_id" NOTNULL AND
737 "policy_id" NOTNULL AND
738 "issue_id" NOTNULL AND
739 "state" NOTNULL AND
740 "initiative_id" ISNULL AND
741 "draft_id" ISNULL AND
742 "suggestion_id" ISNULL AND
743 "boolean_value" ISNULL AND
744 "numeric_value" ISNULL AND
745 "text_value" ISNULL AND
746 "old_text_value" ISNULL ));
747 ALTER TABLE "event" ADD CONSTRAINT "constr_for_initiative_creation_or_revocation_or_new_draft" CHECK (
748 "event" NOT IN (
749 'initiative_created_in_new_issue',
750 'initiative_created_in_existing_issue',
751 'initiative_revoked',
752 'new_draft_created'
753 ) OR (
754 "member_id" NOTNULL AND
755 "other_member_id" ISNULL AND
756 "scope" ISNULL AND
757 "unit_id" NOTNULL AND
758 "area_id" NOTNULL AND
759 "policy_id" NOTNULL AND
760 "issue_id" NOTNULL AND
761 "state" NOTNULL AND
762 "initiative_id" NOTNULL AND
763 "draft_id" NOTNULL AND
764 "suggestion_id" ISNULL AND
765 "boolean_value" ISNULL AND
766 "numeric_value" ISNULL AND
767 "text_value" ISNULL AND
768 "old_text_value" ISNULL ));
769 ALTER TABLE "event" ADD CONSTRAINT "constr_for_suggestion_creation" CHECK (
770 "event" != 'suggestion_created' OR (
771 "member_id" NOTNULL AND
772 "other_member_id" ISNULL AND
773 "scope" ISNULL AND
774 "unit_id" NOTNULL AND
775 "area_id" NOTNULL AND
776 "policy_id" NOTNULL AND
777 "issue_id" NOTNULL AND
778 "state" NOTNULL AND
779 "initiative_id" NOTNULL AND
780 "draft_id" ISNULL AND
781 "suggestion_id" NOTNULL AND
782 "boolean_value" ISNULL AND
783 "numeric_value" ISNULL AND
784 "text_value" ISNULL AND
785 "old_text_value" ISNULL ));
786 ALTER TABLE "event" ADD CONSTRAINT "constr_for_suggestion_removal" CHECK (
787 "event" != 'suggestion_removed' OR (
788 "member_id" ISNULL AND
789 "other_member_id" ISNULL AND
790 "scope" ISNULL AND
791 "unit_id" NOTNULL AND
792 "area_id" NOTNULL AND
793 "policy_id" NOTNULL AND
794 "issue_id" NOTNULL AND
795 "state" NOTNULL AND
796 "initiative_id" NOTNULL AND
797 "draft_id" ISNULL AND
798 "suggestion_id" NOTNULL AND
799 "boolean_value" ISNULL AND
800 "numeric_value" ISNULL AND
801 "text_value" ISNULL AND
802 "old_text_value" ISNULL ));
803 ALTER TABLE "event" ADD CONSTRAINT "constr_for_value_less_member_event" CHECK (
804 "event" NOT IN (
805 'member_activated',
806 'member_removed',
807 'member_profile_updated',
808 'member_image_updated'
809 ) OR (
810 "member_id" NOTNULL AND
811 "other_member_id" ISNULL AND
812 "scope" ISNULL AND
813 "unit_id" ISNULL AND
814 "area_id" ISNULL AND
815 "policy_id" ISNULL AND
816 "issue_id" ISNULL AND
817 "state" ISNULL AND
818 "initiative_id" ISNULL AND
819 "draft_id" ISNULL AND
820 "suggestion_id" ISNULL AND
821 "boolean_value" ISNULL AND
822 "numeric_value" ISNULL AND
823 "text_value" ISNULL AND
824 "old_text_value" ISNULL ));
825 ALTER TABLE "event" ADD CONSTRAINT "constr_for_member_active" CHECK (
826 "event" != 'member_active' OR (
827 "member_id" NOTNULL AND
828 "other_member_id" ISNULL AND
829 "scope" ISNULL AND
830 "unit_id" ISNULL AND
831 "area_id" ISNULL AND
832 "policy_id" ISNULL AND
833 "issue_id" ISNULL AND
834 "state" ISNULL AND
835 "initiative_id" ISNULL AND
836 "draft_id" ISNULL AND
837 "suggestion_id" ISNULL AND
838 "boolean_value" NOTNULL AND
839 "numeric_value" ISNULL AND
840 "text_value" ISNULL AND
841 "old_text_value" ISNULL ));
842 ALTER TABLE "event" ADD CONSTRAINT "constr_for_member_name_updated" CHECK (
843 "event" != 'member_name_updated' OR (
844 "member_id" NOTNULL AND
845 "other_member_id" ISNULL AND
846 "scope" ISNULL AND
847 "unit_id" ISNULL AND
848 "area_id" ISNULL AND
849 "policy_id" ISNULL AND
850 "issue_id" ISNULL AND
851 "state" ISNULL AND
852 "initiative_id" ISNULL AND
853 "draft_id" ISNULL AND
854 "suggestion_id" ISNULL AND
855 "boolean_value" ISNULL AND
856 "numeric_value" ISNULL AND
857 "text_value" NOTNULL AND
858 "old_text_value" NOTNULL ));
859 ALTER TABLE "event" ADD CONSTRAINT "constr_for_interest" CHECK (
860 "event" != 'interest' OR (
861 "member_id" NOTNULL AND
862 "other_member_id" ISNULL AND
863 "scope" ISNULL AND
864 "unit_id" NOTNULL AND
865 "area_id" NOTNULL AND
866 "policy_id" NOTNULL AND
867 "issue_id" NOTNULL AND
868 "state" NOTNULL AND
869 "initiative_id" ISNULL AND
870 "draft_id" ISNULL AND
871 "suggestion_id" ISNULL AND
872 "boolean_value" NOTNULL AND
873 "numeric_value" ISNULL AND
874 "text_value" ISNULL AND
875 "old_text_value" ISNULL ));
876 ALTER TABLE "event" ADD CONSTRAINT "constr_for_initiator" CHECK (
877 "event" != 'initiator' OR (
878 "member_id" NOTNULL AND
879 "other_member_id" ISNULL AND
880 "scope" ISNULL AND
881 "unit_id" NOTNULL AND
882 "area_id" NOTNULL AND
883 "policy_id" NOTNULL AND
884 "issue_id" NOTNULL AND
885 "state" NOTNULL AND
886 "initiative_id" NOTNULL AND
887 "draft_id" ISNULL AND
888 "suggestion_id" ISNULL AND
889 "boolean_value" NOTNULL AND
890 "numeric_value" ISNULL AND
891 "text_value" ISNULL AND
892 "old_text_value" ISNULL ));
893 ALTER TABLE "event" ADD CONSTRAINT "constr_for_support" CHECK (
894 "event" != 'support' OR (
895 "member_id" NOTNULL AND
896 "other_member_id" ISNULL AND
897 "scope" ISNULL AND
898 "unit_id" NOTNULL AND
899 "area_id" NOTNULL AND
900 "policy_id" NOTNULL AND
901 "issue_id" NOTNULL AND
902 "state" NOTNULL AND
903 "initiative_id" NOTNULL AND
904 ("draft_id" NOTNULL) = ("boolean_value" = TRUE) AND
905 "suggestion_id" ISNULL AND
906 "boolean_value" NOTNULL AND
907 "numeric_value" ISNULL AND
908 "text_value" ISNULL AND
909 "old_text_value" ISNULL ));
910 ALTER TABLE "event" ADD CONSTRAINT "constr_for_support_updated" CHECK (
911 "event" != 'support_updated' OR (
912 "member_id" NOTNULL AND
913 "other_member_id" ISNULL AND
914 "scope" ISNULL AND
915 "unit_id" NOTNULL AND
916 "area_id" NOTNULL AND
917 "policy_id" NOTNULL AND
918 "issue_id" NOTNULL AND
919 "state" NOTNULL AND
920 "initiative_id" NOTNULL AND
921 "draft_id" NOTNULL AND
922 "suggestion_id" ISNULL AND
923 "boolean_value" ISNULL AND
924 "numeric_value" ISNULL AND
925 "text_value" ISNULL AND
926 "old_text_value" ISNULL ));
927 ALTER TABLE "event" ADD CONSTRAINT "constr_for_suggestion_rated" CHECK (
928 "event" != 'suggestion_rated' OR (
929 "member_id" NOTNULL AND
930 "other_member_id" ISNULL AND
931 "scope" ISNULL AND
932 "unit_id" NOTNULL AND
933 "area_id" NOTNULL AND
934 "policy_id" NOTNULL AND
935 "issue_id" NOTNULL AND
936 "state" NOTNULL AND
937 "initiative_id" NOTNULL AND
938 "draft_id" ISNULL AND
939 "suggestion_id" NOTNULL AND
940 ("boolean_value" NOTNULL) = ("numeric_value" != 0) AND
941 "numeric_value" NOTNULL AND
942 "numeric_value" IN (-2, -1, 0, 1, 2) AND
943 "text_value" ISNULL AND
944 "old_text_value" ISNULL ));
945 ALTER TABLE "event" ADD CONSTRAINT "constr_for_delegation" CHECK (
946 "event" != 'delegation' OR (
947 "member_id" NOTNULL AND
948 ("other_member_id" NOTNULL) OR ("boolean_value" = FALSE) AND
949 "scope" NOTNULL AND
950 "unit_id" NOTNULL AND
951 ("area_id" NOTNULL) = ("scope" != 'unit'::"delegation_scope") AND
952 "policy_id" ISNULL AND
953 ("issue_id" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
954 ("state" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
955 "initiative_id" ISNULL AND
956 "draft_id" ISNULL AND
957 "suggestion_id" ISNULL AND
958 "boolean_value" NOTNULL AND
959 "numeric_value" ISNULL AND
960 "text_value" ISNULL AND
961 "old_text_value" ISNULL ));
962 ALTER TABLE "event" ADD CONSTRAINT "constr_for_contact" CHECK (
963 "event" != 'contact' OR (
964 "member_id" NOTNULL AND
965 "other_member_id" NOTNULL AND
966 "scope" ISNULL AND
967 "unit_id" ISNULL AND
968 "area_id" ISNULL AND
969 "policy_id" ISNULL AND
970 "issue_id" ISNULL AND
971 "state" ISNULL AND
972 "initiative_id" ISNULL AND
973 "draft_id" ISNULL AND
974 "suggestion_id" ISNULL AND
975 "boolean_value" NOTNULL AND
976 "numeric_value" ISNULL AND
977 "text_value" ISNULL AND
978 "old_text_value" ISNULL ));
981 ALTER TABLE "notification_event_sent" RENAME TO "event_processed";
982 ALTER INDEX "notification_event_sent_singleton_idx" RENAME TO "event_processed_singleton_idx";
984 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)';
985 COMMENT ON INDEX "event_processed_singleton_idx" IS 'This index ensures that "event_processed" only contains one row maximum.';
988 CREATE FUNCTION "write_event_unit_trigger"()
989 RETURNS TRIGGER
990 LANGUAGE 'plpgsql' VOLATILE AS $$
991 DECLARE
992 "event_v" "event_type";
993 BEGIN
994 IF TG_OP = 'UPDATE' THEN
995 IF OLD."active" = FALSE AND NEW."active" = FALSE THEN
996 RETURN NULL;
997 --ELSIF OLD."active" = FALSE AND NEW."active" = TRUE THEN
998 -- "event_v" := 'unit_created';
999 --ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN
1000 -- "event_v" := 'unit_removed';
1001 ELSIF OLD != NEW THEN
1002 "event_v" := 'unit_updated';
1003 ELSE
1004 RETURN NULL;
1005 END IF;
1006 ELSE
1007 "event_v" := 'unit_created';
1008 END IF;
1009 INSERT INTO "event" ("event", "unit_id") VALUES ("event_v", NEW."id");
1010 RETURN NULL;
1011 END;
1012 $$;
1014 CREATE TRIGGER "write_event_unit" AFTER INSERT OR UPDATE ON "unit"
1015 FOR EACH ROW EXECUTE PROCEDURE "write_event_unit_trigger"();
1017 COMMENT ON FUNCTION "write_event_unit_trigger"() IS 'Implementation of trigger "write_event_unit" on table "unit"';
1018 COMMENT ON TRIGGER "write_event_unit" ON "unit" IS 'Create entry in "event" table on new or changed/disabled units';
1021 CREATE FUNCTION "write_event_area_trigger"()
1022 RETURNS TRIGGER
1023 LANGUAGE 'plpgsql' VOLATILE AS $$
1024 DECLARE
1025 "event_v" "event_type";
1026 BEGIN
1027 IF TG_OP = 'UPDATE' THEN
1028 IF OLD."active" = FALSE AND NEW."active" = FALSE THEN
1029 RETURN NULL;
1030 --ELSIF OLD."active" = FALSE AND NEW."active" = TRUE THEN
1031 -- "event_v" := 'area_created';
1032 --ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN
1033 -- "event_v" := 'area_removed';
1034 ELSIF OLD != NEW THEN
1035 "event_v" := 'area_updated';
1036 ELSE
1037 RETURN NULL;
1038 END IF;
1039 ELSE
1040 "event_v" := 'area_created';
1041 END IF;
1042 INSERT INTO "event" ("event", "area_id") VALUES ("event_v", NEW."id");
1043 RETURN NULL;
1044 END;
1045 $$;
1047 CREATE TRIGGER "write_event_area" AFTER INSERT OR UPDATE ON "area"
1048 FOR EACH ROW EXECUTE PROCEDURE "write_event_area_trigger"();
1050 COMMENT ON FUNCTION "write_event_area_trigger"() IS 'Implementation of trigger "write_event_area" on table "area"';
1051 COMMENT ON TRIGGER "write_event_area" ON "area" IS 'Create entry in "event" table on new or changed/disabled areas';
1054 CREATE FUNCTION "write_event_policy_trigger"()
1055 RETURNS TRIGGER
1056 LANGUAGE 'plpgsql' VOLATILE AS $$
1057 DECLARE
1058 "event_v" "event_type";
1059 BEGIN
1060 IF TG_OP = 'UPDATE' THEN
1061 IF OLD."active" = FALSE AND NEW."active" = FALSE THEN
1062 RETURN NULL;
1063 --ELSIF OLD."active" = FALSE AND NEW."active" = TRUE THEN
1064 -- "event_v" := 'policy_created';
1065 --ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN
1066 -- "event_v" := 'policy_removed';
1067 ELSIF OLD != NEW THEN
1068 "event_v" := 'policy_updated';
1069 ELSE
1070 RETURN NULL;
1071 END IF;
1072 ELSE
1073 "event_v" := 'policy_created';
1074 END IF;
1075 INSERT INTO "event" ("event", "policy_id") VALUES ("event_v", NEW."id");
1076 RETURN NULL;
1077 END;
1078 $$;
1080 CREATE TRIGGER "write_event_policy" AFTER INSERT OR UPDATE ON "policy"
1081 FOR EACH ROW EXECUTE PROCEDURE "write_event_policy_trigger"();
1083 COMMENT ON FUNCTION "write_event_policy_trigger"() IS 'Implementation of trigger "write_event_policy" on table "policy"';
1084 COMMENT ON TRIGGER "write_event_policy" ON "policy" IS 'Create entry in "event" table on new or changed/disabled policies';
1087 CREATE OR REPLACE FUNCTION "write_event_issue_state_changed_trigger"()
1088 RETURNS TRIGGER
1089 LANGUAGE 'plpgsql' VOLATILE AS $$
1090 DECLARE
1091 "area_row" "area"%ROWTYPE;
1092 BEGIN
1093 IF NEW."state" != OLD."state" THEN
1094 SELECT * INTO "area_row" FROM "area" WHERE "id" = NEW."area_id"
1095 FOR SHARE;
1096 INSERT INTO "event" (
1097 "event",
1098 "unit_id", "area_id", "policy_id", "issue_id", "state"
1099 ) VALUES (
1100 'issue_state_changed',
1101 "area_row"."unit_id", NEW."area_id", NEW."policy_id",
1102 NEW."id", NEW."state"
1103 );
1104 END IF;
1105 RETURN NULL;
1106 END;
1107 $$;
1110 CREATE OR REPLACE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1111 RETURNS TRIGGER
1112 LANGUAGE 'plpgsql' VOLATILE AS $$
1113 DECLARE
1114 "initiative_row" "initiative"%ROWTYPE;
1115 "issue_row" "issue"%ROWTYPE;
1116 "area_row" "area"%ROWTYPE;
1117 "event_v" "event_type";
1118 BEGIN
1119 SELECT * INTO "initiative_row" FROM "initiative"
1120 WHERE "id" = NEW."initiative_id" FOR SHARE;
1121 SELECT * INTO "issue_row" FROM "issue"
1122 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1123 SELECT * INTO "area_row" FROM "area"
1124 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1125 IF EXISTS (
1126 SELECT NULL FROM "draft"
1127 WHERE "initiative_id" = NEW."initiative_id" AND "id" != NEW."id"
1128 FOR SHARE
1129 ) THEN
1130 "event_v" := 'new_draft_created';
1131 ELSE
1132 IF EXISTS (
1133 SELECT NULL FROM "initiative"
1134 WHERE "issue_id" = "initiative_row"."issue_id"
1135 AND "id" != "initiative_row"."id"
1136 FOR SHARE
1137 ) THEN
1138 "event_v" := 'initiative_created_in_existing_issue';
1139 ELSE
1140 "event_v" := 'initiative_created_in_new_issue';
1141 END IF;
1142 END IF;
1143 INSERT INTO "event" (
1144 "event", "member_id",
1145 "unit_id", "area_id", "policy_id", "issue_id", "state",
1146 "initiative_id", "draft_id"
1147 ) VALUES (
1148 "event_v", NEW."author_id",
1149 "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",
1150 "initiative_row"."issue_id", "issue_row"."state",
1151 NEW."initiative_id", NEW."id"
1152 );
1153 RETURN NULL;
1154 END;
1155 $$;
1158 CREATE OR REPLACE FUNCTION "write_event_initiative_revoked_trigger"()
1159 RETURNS TRIGGER
1160 LANGUAGE 'plpgsql' VOLATILE AS $$
1161 DECLARE
1162 "issue_row" "issue"%ROWTYPE;
1163 "area_row" "area"%ROWTYPE;
1164 "draft_id_v" "draft"."id"%TYPE;
1165 BEGIN
1166 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1167 SELECT * INTO "issue_row" FROM "issue"
1168 WHERE "id" = NEW."issue_id" FOR SHARE;
1169 SELECT * INTO "area_row" FROM "area"
1170 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1171 SELECT "id" INTO "draft_id_v" FROM "current_draft"
1172 WHERE "initiative_id" = NEW."id" FOR SHARE;
1173 INSERT INTO "event" (
1174 "event", "member_id",
1175 "unit_id", "area_id", "policy_id", "issue_id", "state",
1176 "initiative_id", "draft_id"
1177 ) VALUES (
1178 'initiative_revoked', NEW."revoked_by_member_id",
1179 "area_row"."unit_id", "issue_row"."area_id",
1180 "issue_row"."policy_id",
1181 NEW."issue_id", "issue_row"."state",
1182 NEW."id", "draft_id_v"
1183 );
1184 END IF;
1185 RETURN NULL;
1186 END;
1187 $$;
1190 CREATE OR REPLACE FUNCTION "write_event_suggestion_created_trigger"()
1191 RETURNS TRIGGER
1192 LANGUAGE 'plpgsql' VOLATILE AS $$
1193 DECLARE
1194 "initiative_row" "initiative"%ROWTYPE;
1195 "issue_row" "issue"%ROWTYPE;
1196 "area_row" "area"%ROWTYPE;
1197 BEGIN
1198 SELECT * INTO "initiative_row" FROM "initiative"
1199 WHERE "id" = NEW."initiative_id" FOR SHARE;
1200 SELECT * INTO "issue_row" FROM "issue"
1201 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1202 SELECT * INTO "area_row" FROM "area"
1203 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1204 INSERT INTO "event" (
1205 "event", "member_id",
1206 "unit_id", "area_id", "policy_id", "issue_id", "state",
1207 "initiative_id", "suggestion_id"
1208 ) VALUES (
1209 'suggestion_created', NEW."author_id",
1210 "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",
1211 "initiative_row"."issue_id", "issue_row"."state",
1212 NEW."initiative_id", NEW."id"
1213 );
1214 RETURN NULL;
1215 END;
1216 $$;
1219 CREATE FUNCTION "write_event_suggestion_removed_trigger"()
1220 RETURNS TRIGGER
1221 LANGUAGE 'plpgsql' VOLATILE AS $$
1222 DECLARE
1223 "initiative_row" "initiative"%ROWTYPE;
1224 "issue_row" "issue"%ROWTYPE;
1225 "area_row" "area"%ROWTYPE;
1226 BEGIN
1227 SELECT * INTO "initiative_row" FROM "initiative"
1228 WHERE "id" = OLD."initiative_id" FOR SHARE;
1229 IF "initiative_row"."id" NOTNULL THEN
1230 SELECT * INTO "issue_row" FROM "issue"
1231 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1232 SELECT * INTO "area_row" FROM "area"
1233 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1234 INSERT INTO "event" (
1235 "event",
1236 "unit_id", "area_id", "policy_id", "issue_id", "state",
1237 "initiative_id", "suggestion_id"
1238 ) VALUES (
1239 'suggestion_removed',
1240 "area_row"."unit_id", "issue_row"."area_id",
1241 "issue_row"."policy_id",
1242 "initiative_row"."issue_id", "issue_row"."state",
1243 OLD."initiative_id", OLD."id"
1244 );
1245 END IF;
1246 RETURN NULL;
1247 END;
1248 $$;
1250 CREATE TRIGGER "write_event_suggestion_removed"
1251 AFTER DELETE ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1252 "write_event_suggestion_removed_trigger"();
1254 COMMENT ON FUNCTION "write_event_suggestion_removed_trigger"() IS 'Implementation of trigger "write_event_suggestion_removed" on table "issue"';
1255 COMMENT ON TRIGGER "write_event_suggestion_removed" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1258 CREATE FUNCTION "write_event_member_trigger"()
1259 RETURNS TRIGGER
1260 LANGUAGE 'plpgsql' VOLATILE AS $$
1261 BEGIN
1262 IF TG_OP = 'INSERT' THEN
1263 IF NEW."activated" NOTNULL AND NEW."deleted" ISNULL THEN
1264 INSERT INTO "event" ("event", "member_id")
1265 VALUES ('member_activated', NEW."id");
1266 END IF;
1267 IF NEW."active" THEN
1268 INSERT INTO "event" ("event", "member_id", "boolean_value")
1269 VALUES ('member_active', NEW."id", TRUE);
1270 END IF;
1271 ELSIF TG_OP = 'UPDATE' THEN
1272 IF OLD."id" != NEW."id" THEN
1273 RAISE EXCEPTION 'Cannot change member ID';
1274 END IF;
1275 IF
1276 (OLD."activated" ISNULL OR OLD."deleted" NOTNULL) AND
1277 NEW."activated" NOTNULL AND NEW."deleted" ISNULL
1278 THEN
1279 INSERT INTO "event" ("event", "member_id")
1280 VALUES ('member_activated', NEW."id");
1281 END IF;
1282 IF OLD."active" != NEW."active" THEN
1283 INSERT INTO "event" ("event", "member_id", "boolean_value") VALUES (
1284 'member_active', NEW."id", NEW."active"
1285 );
1286 END IF;
1287 IF OLD."name" != NEW."name" THEN
1288 INSERT INTO "event" (
1289 "event", "member_id", "text_value", "old_text_value"
1290 ) VALUES (
1291 'member_name_updated', NEW."id", NEW."name", OLD."name"
1292 );
1293 END IF;
1294 IF
1295 OLD."activated" NOTNULL AND OLD."deleted" ISNULL AND
1296 (NEW."activated" ISNULL OR NEW."deleted" NOTNULL)
1297 THEN
1298 INSERT INTO "event" ("event", "member_id")
1299 VALUES ('member_removed', NEW."id");
1300 END IF;
1301 END IF;
1302 RETURN NULL;
1303 END;
1304 $$;
1306 CREATE TRIGGER "write_event_member"
1307 AFTER INSERT OR UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1308 "write_event_member_trigger"();
1310 COMMENT ON FUNCTION "write_event_member_trigger"() IS 'Implementation of trigger "write_event_member" on table "member"';
1311 COMMENT ON TRIGGER "write_event_member" ON "member" IS 'Create entries in "event" table on insertion to member table';
1314 CREATE FUNCTION "write_event_member_profile_updated_trigger"()
1315 RETURNS TRIGGER
1316 LANGUAGE 'plpgsql' VOLATILE AS $$
1317 BEGIN
1318 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1319 IF EXISTS (SELECT NULL FROM "member" WHERE "id" = OLD."member_id") THEN
1320 INSERT INTO "event" ("event", "member_id") VALUES (
1321 'member_profile_updated', OLD."member_id"
1322 );
1323 END IF;
1324 END IF;
1325 IF TG_OP = 'UPDATE' THEN
1326 IF OLD."member_id" = NEW."member_id" THEN
1327 RETURN NULL;
1328 END IF;
1329 END IF;
1330 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1331 INSERT INTO "event" ("event", "member_id") VALUES (
1332 'member_profile_updated', NEW."member_id"
1333 );
1334 END IF;
1335 RETURN NULL;
1336 END;
1337 $$;
1339 CREATE TRIGGER "write_event_member_profile_updated"
1340 AFTER INSERT OR UPDATE OR DELETE ON "member_profile"
1341 FOR EACH ROW EXECUTE PROCEDURE
1342 "write_event_member_profile_updated_trigger"();
1344 COMMENT ON FUNCTION "write_event_member_profile_updated_trigger"() IS 'Implementation of trigger "write_event_member_profile_updated" on table "member_profile"';
1345 COMMENT ON TRIGGER "write_event_member_profile_updated" ON "member_profile" IS 'Creates entries in "event" table on member profile update';
1348 CREATE FUNCTION "write_event_member_image_updated_trigger"()
1349 RETURNS TRIGGER
1350 LANGUAGE 'plpgsql' VOLATILE AS $$
1351 BEGIN
1352 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1353 IF NOT OLD."scaled" THEN
1354 IF EXISTS (SELECT NULL FROM "member" WHERE "id" = OLD."member_id") THEN
1355 INSERT INTO "event" ("event", "member_id") VALUES (
1356 'member_image_updated', OLD."member_id"
1357 );
1358 END IF;
1359 END IF;
1360 END IF;
1361 IF TG_OP = 'UPDATE' THEN
1362 IF
1363 OLD."member_id" = NEW."member_id" AND
1364 OLD."scaled" = NEW."scaled"
1365 THEN
1366 RETURN NULL;
1367 END IF;
1368 END IF;
1369 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1370 IF NOT NEW."scaled" THEN
1371 INSERT INTO "event" ("event", "member_id") VALUES (
1372 'member_image_updated', NEW."member_id"
1373 );
1374 END IF;
1375 END IF;
1376 RETURN NULL;
1377 END;
1378 $$;
1380 CREATE TRIGGER "write_event_member_image_updated"
1381 AFTER INSERT OR UPDATE OR DELETE ON "member_image"
1382 FOR EACH ROW EXECUTE PROCEDURE
1383 "write_event_member_image_updated_trigger"();
1385 COMMENT ON FUNCTION "write_event_member_image_updated_trigger"() IS 'Implementation of trigger "write_event_member_image_updated" on table "member_image"';
1386 COMMENT ON TRIGGER "write_event_member_image_updated" ON "member_image" IS 'Creates entries in "event" table on member image update';
1389 CREATE FUNCTION "write_event_interest_trigger"()
1390 RETURNS TRIGGER
1391 LANGUAGE 'plpgsql' VOLATILE AS $$
1392 DECLARE
1393 "issue_row" "issue"%ROWTYPE;
1394 "area_row" "area"%ROWTYPE;
1395 BEGIN
1396 IF TG_OP = 'UPDATE' THEN
1397 IF OLD = NEW THEN
1398 RETURN NULL;
1399 END IF;
1400 END IF;
1401 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1402 SELECT * INTO "issue_row" FROM "issue"
1403 WHERE "id" = OLD."issue_id" FOR SHARE;
1404 SELECT * INTO "area_row" FROM "area"
1405 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1406 IF "issue_row"."id" NOTNULL THEN
1407 INSERT INTO "event" (
1408 "event", "member_id",
1409 "unit_id", "area_id", "policy_id", "issue_id", "state",
1410 "boolean_value"
1411 ) VALUES (
1412 'interest', OLD."member_id",
1413 "area_row"."unit_id", "issue_row"."area_id",
1414 "issue_row"."policy_id",
1415 OLD."issue_id", "issue_row"."state",
1416 FALSE
1417 );
1418 END IF;
1419 END IF;
1420 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1421 SELECT * INTO "issue_row" FROM "issue"
1422 WHERE "id" = NEW."issue_id" FOR SHARE;
1423 SELECT * INTO "area_row" FROM "area"
1424 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1425 INSERT INTO "event" (
1426 "event", "member_id",
1427 "unit_id", "area_id", "policy_id", "issue_id", "state",
1428 "boolean_value"
1429 ) VALUES (
1430 'interest', NEW."member_id",
1431 "area_row"."unit_id", "issue_row"."area_id",
1432 "issue_row"."policy_id",
1433 NEW."issue_id", "issue_row"."state",
1434 TRUE
1435 );
1436 END IF;
1437 RETURN NULL;
1438 END;
1439 $$;
1441 CREATE TRIGGER "write_event_interest"
1442 AFTER INSERT OR UPDATE OR DELETE ON "interest" FOR EACH ROW EXECUTE PROCEDURE
1443 "write_event_interest_trigger"();
1445 COMMENT ON FUNCTION "write_event_interest_trigger"() IS 'Implementation of trigger "write_event_interest_inserted" on table "interest"';
1446 COMMENT ON TRIGGER "write_event_interest" ON "interest" IS 'Create entry in "event" table on adding or removing interest';
1449 CREATE FUNCTION "write_event_initiator_trigger"()
1450 RETURNS TRIGGER
1451 LANGUAGE 'plpgsql' VOLATILE AS $$
1452 DECLARE
1453 "initiative_row" "initiative"%ROWTYPE;
1454 "issue_row" "issue"%ROWTYPE;
1455 "area_row" "area"%ROWTYPE;
1456 "accepted_v" BOOLEAN = FALSE;
1457 "rejected_v" BOOLEAN = FALSE;
1458 BEGIN
1459 IF TG_OP = 'UPDATE' THEN
1460 IF
1461 OLD."initiative_id" = NEW."initiative_id" AND
1462 OLD."member_id" = NEW."member_id"
1463 THEN
1464 IF
1465 coalesce(OLD."accepted", FALSE) = coalesce(NEW."accepted", FALSE)
1466 THEN
1467 RETURN NULL;
1468 END IF;
1469 IF coalesce(NEW."accepted", FALSE) = TRUE THEN
1470 "accepted_v" := TRUE;
1471 ELSE
1472 "rejected_v" := TRUE;
1473 END IF;
1474 END IF;
1475 END IF;
1476 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "accepted_v" THEN
1477 IF coalesce(OLD."accepted", FALSE) = TRUE THEN
1478 SELECT * INTO "initiative_row" FROM "initiative"
1479 WHERE "id" = OLD."initiative_id" FOR SHARE;
1480 IF "initiative_row"."id" NOTNULL THEN
1481 SELECT * INTO "issue_row" FROM "issue"
1482 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1483 SELECT * INTO "area_row" FROM "area"
1484 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1485 INSERT INTO "event" (
1486 "event", "member_id",
1487 "unit_id", "area_id", "policy_id", "issue_id", "state",
1488 "initiative_id", "boolean_value"
1489 ) VALUES (
1490 'initiator', OLD."member_id",
1491 "area_row"."unit_id", "issue_row"."area_id",
1492 "issue_row"."policy_id",
1493 "issue_row"."id", "issue_row"."state",
1494 OLD."initiative_id", FALSE
1495 );
1496 END IF;
1497 END IF;
1498 END IF;
1499 IF TG_OP = 'UPDATE' AND NOT "rejected_v" THEN
1500 IF coalesce(NEW."accepted", FALSE) = TRUE THEN
1501 SELECT * INTO "initiative_row" FROM "initiative"
1502 WHERE "id" = NEW."initiative_id" FOR SHARE;
1503 SELECT * INTO "issue_row" FROM "issue"
1504 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1505 SELECT * INTO "area_row" FROM "area"
1506 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1507 INSERT INTO "event" (
1508 "event", "member_id",
1509 "unit_id", "area_id", "policy_id", "issue_id", "state",
1510 "initiative_id", "boolean_value"
1511 ) VALUES (
1512 'initiator', NEW."member_id",
1513 "area_row"."unit_id", "issue_row"."area_id",
1514 "issue_row"."policy_id",
1515 "issue_row"."id", "issue_row"."state",
1516 NEW."initiative_id", TRUE
1517 );
1518 END IF;
1519 END IF;
1520 RETURN NULL;
1521 END;
1522 $$;
1524 CREATE TRIGGER "write_event_initiator"
1525 AFTER UPDATE OR DELETE ON "initiator" FOR EACH ROW EXECUTE PROCEDURE
1526 "write_event_initiator_trigger"();
1528 COMMENT ON FUNCTION "write_event_initiator_trigger"() IS 'Implementation of trigger "write_event_initiator" on table "initiator"';
1529 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)';
1532 CREATE FUNCTION "write_event_support_trigger"()
1533 RETURNS TRIGGER
1534 LANGUAGE 'plpgsql' VOLATILE AS $$
1535 DECLARE
1536 "issue_row" "issue"%ROWTYPE;
1537 "area_row" "area"%ROWTYPE;
1538 BEGIN
1539 IF TG_OP = 'UPDATE' THEN
1540 IF
1541 OLD."initiative_id" = NEW."initiative_id" AND
1542 OLD."member_id" = NEW."member_id"
1543 THEN
1544 IF OLD."draft_id" != NEW."draft_id" THEN
1545 SELECT * INTO "issue_row" FROM "issue"
1546 WHERE "id" = NEW."issue_id" FOR SHARE;
1547 SELECT * INTO "area_row" FROM "area"
1548 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1549 INSERT INTO "event" (
1550 "event", "member_id",
1551 "unit_id", "area_id", "policy_id", "issue_id", "state",
1552 "initiative_id", "draft_id"
1553 ) VALUES (
1554 'support_updated', NEW."member_id",
1555 "area_row"."unit_id", "issue_row"."area_id",
1556 "issue_row"."policy_id",
1557 "issue_row"."id", "issue_row"."state",
1558 NEW."initiative_id", NEW."draft_id"
1559 );
1560 END IF;
1561 RETURN NULL;
1562 END IF;
1563 END IF;
1564 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1565 IF EXISTS (
1566 SELECT NULL FROM "initiative" WHERE "id" = OLD."initiative_id"
1567 FOR SHARE
1568 ) THEN
1569 SELECT * INTO "issue_row" FROM "issue"
1570 WHERE "id" = OLD."issue_id" FOR SHARE;
1571 SELECT * INTO "area_row" FROM "area"
1572 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1573 INSERT INTO "event" (
1574 "event", "member_id",
1575 "unit_id", "area_id", "policy_id", "issue_id", "state",
1576 "initiative_id", "boolean_value"
1577 ) VALUES (
1578 'support', OLD."member_id",
1579 "area_row"."unit_id", "issue_row"."area_id",
1580 "issue_row"."policy_id",
1581 "issue_row"."id", "issue_row"."state",
1582 OLD."initiative_id", FALSE
1583 );
1584 END IF;
1585 END IF;
1586 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1587 SELECT * INTO "issue_row" FROM "issue"
1588 WHERE "id" = NEW."issue_id" FOR SHARE;
1589 SELECT * INTO "area_row" FROM "area"
1590 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1591 INSERT INTO "event" (
1592 "event", "member_id",
1593 "unit_id", "area_id", "policy_id", "issue_id", "state",
1594 "initiative_id", "draft_id", "boolean_value"
1595 ) VALUES (
1596 'support', NEW."member_id",
1597 "area_row"."unit_id", "issue_row"."area_id",
1598 "issue_row"."policy_id",
1599 "issue_row"."id", "issue_row"."state",
1600 NEW."initiative_id", NEW."draft_id", TRUE
1601 );
1602 END IF;
1603 RETURN NULL;
1604 END;
1605 $$;
1607 CREATE TRIGGER "write_event_support"
1608 AFTER INSERT OR UPDATE OR DELETE ON "supporter" FOR EACH ROW EXECUTE PROCEDURE
1609 "write_event_support_trigger"();
1611 COMMENT ON FUNCTION "write_event_support_trigger"() IS 'Implementation of trigger "write_event_support" on table "supporter"';
1612 COMMENT ON TRIGGER "write_event_support" ON "supporter" IS 'Create entry in "event" table when adding, updating, or removing support';
1615 CREATE FUNCTION "write_event_suggestion_rated_trigger"()
1616 RETURNS TRIGGER
1617 LANGUAGE 'plpgsql' VOLATILE AS $$
1618 DECLARE
1619 "same_pkey_v" BOOLEAN = FALSE;
1620 "initiative_row" "initiative"%ROWTYPE;
1621 "issue_row" "issue"%ROWTYPE;
1622 "area_row" "area"%ROWTYPE;
1623 BEGIN
1624 IF TG_OP = 'UPDATE' THEN
1625 IF
1626 OLD."suggestion_id" = NEW."suggestion_id" AND
1627 OLD."member_id" = NEW."member_id"
1628 THEN
1629 IF
1630 OLD."degree" = NEW."degree" AND
1631 OLD."fulfilled" = NEW."fulfilled"
1632 THEN
1633 RETURN NULL;
1634 END IF;
1635 "same_pkey_v" := TRUE;
1636 END IF;
1637 END IF;
1638 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "same_pkey_v" THEN
1639 IF EXISTS (
1640 SELECT NULL FROM "suggestion" WHERE "id" = OLD."suggestion_id"
1641 FOR SHARE
1642 ) THEN
1643 SELECT * INTO "initiative_row" FROM "initiative"
1644 WHERE "id" = OLD."initiative_id" FOR SHARE;
1645 SELECT * INTO "issue_row" FROM "issue"
1646 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1647 SELECT * INTO "area_row" FROM "area"
1648 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1649 INSERT INTO "event" (
1650 "event", "member_id",
1651 "unit_id", "area_id", "policy_id", "issue_id", "state",
1652 "initiative_id", "suggestion_id",
1653 "boolean_value", "numeric_value"
1654 ) VALUES (
1655 'suggestion_rated', OLD."member_id",
1656 "area_row"."unit_id", "issue_row"."area_id",
1657 "issue_row"."policy_id",
1658 "initiative_row"."issue_id", "issue_row"."state",
1659 OLD."initiative_id", OLD."suggestion_id",
1660 NULL, 0
1661 );
1662 END IF;
1663 END IF;
1664 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1665 SELECT * INTO "initiative_row" FROM "initiative"
1666 WHERE "id" = NEW."initiative_id" FOR SHARE;
1667 SELECT * INTO "issue_row" FROM "issue"
1668 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1669 SELECT * INTO "area_row" FROM "area"
1670 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1671 INSERT INTO "event" (
1672 "event", "member_id",
1673 "unit_id", "area_id", "policy_id", "issue_id", "state",
1674 "initiative_id", "suggestion_id",
1675 "boolean_value", "numeric_value"
1676 ) VALUES (
1677 'suggestion_rated', NEW."member_id",
1678 "area_row"."unit_id", "issue_row"."area_id",
1679 "issue_row"."policy_id",
1680 "initiative_row"."issue_id", "issue_row"."state",
1681 NEW."initiative_id", NEW."suggestion_id",
1682 NEW."fulfilled", NEW."degree"
1683 );
1684 END IF;
1685 RETURN NULL;
1686 END;
1687 $$;
1689 CREATE TRIGGER "write_event_suggestion_rated"
1690 AFTER INSERT OR UPDATE OR DELETE ON "opinion" FOR EACH ROW EXECUTE PROCEDURE
1691 "write_event_suggestion_rated_trigger"();
1693 COMMENT ON FUNCTION "write_event_suggestion_rated_trigger"() IS 'Implementation of trigger "write_event_suggestion_rated" on table "opinion"';
1694 COMMENT ON TRIGGER "write_event_suggestion_rated" ON "opinion" IS 'Create entry in "event" table when adding, updating, or removing support';
1697 CREATE FUNCTION "write_event_delegation_trigger"()
1698 RETURNS TRIGGER
1699 LANGUAGE 'plpgsql' VOLATILE AS $$
1700 DECLARE
1701 "issue_row" "issue"%ROWTYPE;
1702 "area_row" "area"%ROWTYPE;
1703 BEGIN
1704 IF TG_OP = 'DELETE' THEN
1705 IF EXISTS (
1706 SELECT NULL FROM "member" WHERE "id" = OLD."truster_id"
1707 ) AND (CASE OLD."scope"
1708 WHEN 'unit'::"delegation_scope" THEN EXISTS (
1709 SELECT NULL FROM "unit" WHERE "id" = OLD."unit_id"
1710 )
1711 WHEN 'area'::"delegation_scope" THEN EXISTS (
1712 SELECT NULL FROM "area" WHERE "id" = OLD."area_id"
1713 )
1714 WHEN 'issue'::"delegation_scope" THEN EXISTS (
1715 SELECT NULL FROM "issue" WHERE "id" = OLD."issue_id"
1716 )
1717 END) THEN
1718 SELECT * INTO "issue_row" FROM "issue"
1719 WHERE "id" = OLD."issue_id" FOR SHARE;
1720 SELECT * INTO "area_row" FROM "area"
1721 WHERE "id" = COALESCE(OLD."area_id", "issue_row"."area_id")
1722 FOR SHARE;
1723 INSERT INTO "event" (
1724 "event", "member_id", "scope",
1725 "unit_id", "area_id", "issue_id", "state",
1726 "boolean_value"
1727 ) VALUES (
1728 'delegation', OLD."truster_id", OLD."scope",
1729 COALESCE(OLD."unit_id", "area_row"."unit_id"), "area_row"."id",
1730 OLD."issue_id", "issue_row"."state",
1731 FALSE
1732 );
1733 END IF;
1734 ELSE
1735 SELECT * INTO "issue_row" FROM "issue"
1736 WHERE "id" = NEW."issue_id" FOR SHARE;
1737 SELECT * INTO "area_row" FROM "area"
1738 WHERE "id" = COALESCE(NEW."area_id", "issue_row"."area_id")
1739 FOR SHARE;
1740 INSERT INTO "event" (
1741 "event", "member_id", "other_member_id", "scope",
1742 "unit_id", "area_id", "issue_id", "state",
1743 "boolean_value"
1744 ) VALUES (
1745 'delegation', NEW."truster_id", NEW."trustee_id", NEW."scope",
1746 COALESCE(NEW."unit_id", "area_row"."unit_id"), "area_row"."id",
1747 NEW."issue_id", "issue_row"."state",
1748 TRUE
1749 );
1750 END IF;
1751 RETURN NULL;
1752 END;
1753 $$;
1755 CREATE TRIGGER "write_event_delegation"
1756 AFTER INSERT OR UPDATE OR DELETE ON "delegation" FOR EACH ROW EXECUTE PROCEDURE
1757 "write_event_delegation_trigger"();
1759 COMMENT ON FUNCTION "write_event_delegation_trigger"() IS 'Implementation of trigger "write_event_delegation" on table "delegation"';
1760 COMMENT ON TRIGGER "write_event_delegation" ON "delegation" IS 'Create entry in "event" table when adding, updating, or removing a delegation';
1763 CREATE FUNCTION "write_event_contact_trigger"()
1764 RETURNS TRIGGER
1765 LANGUAGE 'plpgsql' VOLATILE AS $$
1766 BEGIN
1767 IF TG_OP = 'UPDATE' THEN
1768 IF
1769 OLD."member_id" = NEW."member_id" AND
1770 OLD."other_member_id" = NEW."other_member_id" AND
1771 OLD."public" = NEW."public"
1772 THEN
1773 RETURN NULL;
1774 END IF;
1775 END IF;
1776 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1777 IF OLD."public" THEN
1778 IF EXISTS (
1779 SELECT NULL FROM "member" WHERE "id" = OLD."member_id"
1780 FOR SHARE
1781 ) AND EXISTS (
1782 SELECT NULL FROM "member" WHERE "id" = OLD."other_member_id"
1783 FOR SHARE
1784 ) THEN
1785 INSERT INTO "event" (
1786 "event", "member_id", "other_member_id", "boolean_value"
1787 ) VALUES (
1788 'contact', OLD."member_id", OLD."other_member_id", FALSE
1789 );
1790 END IF;
1791 END IF;
1792 END IF;
1793 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1794 IF NEW."public" THEN
1795 INSERT INTO "event" (
1796 "event", "member_id", "other_member_id", "boolean_value"
1797 ) VALUES (
1798 'contact', NEW."member_id", NEW."other_member_id", TRUE
1799 );
1800 END IF;
1801 END IF;
1802 RETURN NULL;
1803 END;
1804 $$;
1806 CREATE TRIGGER "write_event_contact"
1807 AFTER INSERT OR UPDATE OR DELETE ON "contact" FOR EACH ROW EXECUTE PROCEDURE
1808 "write_event_contact_trigger"();
1810 COMMENT ON FUNCTION "write_event_contact_trigger"() IS 'Implementation of trigger "write_event_contact" on table "contact"';
1811 COMMENT ON TRIGGER "write_event_contact" ON "contact" IS 'Create entry in "event" table when adding or removing public contacts';
1814 CREATE FUNCTION "send_event_notify_trigger"()
1815 RETURNS TRIGGER
1816 LANGUAGE 'plpgsql' VOLATILE AS $$
1817 BEGIN
1818 EXECUTE 'NOTIFY "event", ''' || NEW."event" || '''';
1819 RETURN NULL;
1820 END;
1821 $$;
1823 CREATE TRIGGER "send_notify"
1824 AFTER INSERT OR UPDATE ON "event" FOR EACH ROW EXECUTE PROCEDURE
1825 "send_event_notify_trigger"();
1828 CREATE FUNCTION "delete_extended_scope_tokens_trigger"()
1829 RETURNS TRIGGER
1830 LANGUAGE 'plpgsql' VOLATILE AS $$
1831 DECLARE
1832 "system_application_row" "system_application"%ROWTYPE;
1833 BEGIN
1834 IF OLD."system_application_id" NOTNULL THEN
1835 SELECT * FROM "system_application" INTO "system_application_row"
1836 WHERE "id" = OLD."system_application_id";
1837 DELETE FROM "token"
1838 WHERE "member_id" = OLD."member_id"
1839 AND "system_application_id" = OLD."system_application_id"
1840 AND NOT COALESCE(
1841 regexp_split_to_array("scope", E'\\s+') <@
1842 regexp_split_to_array(
1843 "system_application_row"."automatic_scope", E'\\s+'
1844 ),
1845 FALSE
1846 );
1847 END IF;
1848 RETURN OLD;
1849 END;
1850 $$;
1852 CREATE TRIGGER "delete_extended_scope_tokens"
1853 BEFORE DELETE ON "member_application" FOR EACH ROW EXECUTE PROCEDURE
1854 "delete_extended_scope_tokens_trigger"();
1857 CREATE FUNCTION "detach_token_from_session_trigger"()
1858 RETURNS TRIGGER
1859 LANGUAGE 'plpgsql' VOLATILE AS $$
1860 BEGIN
1861 UPDATE "token" SET "session_id" = NULL
1862 WHERE "session_id" = OLD."id";
1863 RETURN OLD;
1864 END;
1865 $$;
1867 CREATE TRIGGER "detach_token_from_session"
1868 BEFORE DELETE ON "session" FOR EACH ROW EXECUTE PROCEDURE
1869 "detach_token_from_session_trigger"();
1872 CREATE FUNCTION "delete_non_detached_scope_with_session_trigger"()
1873 RETURNS TRIGGER
1874 LANGUAGE 'plpgsql' VOLATILE AS $$
1875 BEGIN
1876 IF NEW."session_id" ISNULL THEN
1877 SELECT coalesce(string_agg("element", ' '), '') INTO NEW."scope"
1878 FROM unnest(regexp_split_to_array(NEW."scope", E'\\s+')) AS "element"
1879 WHERE "element" LIKE '%_detached';
1880 END IF;
1881 RETURN NEW;
1882 END;
1883 $$;
1885 CREATE TRIGGER "delete_non_detached_scope_with_session"
1886 BEFORE INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
1887 "delete_non_detached_scope_with_session_trigger"();
1890 CREATE FUNCTION "delete_token_with_empty_scope_trigger"()
1891 RETURNS TRIGGER
1892 LANGUAGE 'plpgsql' VOLATILE AS $$
1893 BEGIN
1894 IF NEW."scope" = '' THEN
1895 DELETE FROM "token" WHERE "id" = NEW."id";
1896 END IF;
1897 RETURN NULL;
1898 END;
1899 $$;
1901 CREATE TRIGGER "delete_token_with_empty_scope"
1902 AFTER INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
1903 "delete_token_with_empty_scope_trigger"();
1906 CREATE FUNCTION "delete_snapshot_on_partial_delete_trigger"()
1907 RETURNS TRIGGER
1908 LANGUAGE 'plpgsql' VOLATILE AS $$
1909 BEGIN
1910 IF TG_OP = 'UPDATE' THEN
1911 IF
1912 OLD."snapshot_id" = NEW."snapshot_id" AND
1913 OLD."issue_id" = NEW."issue_id"
1914 THEN
1915 RETURN NULL;
1916 END IF;
1917 END IF;
1918 DELETE FROM "snapshot" WHERE "id" = OLD."snapshot_id";
1919 RETURN NULL;
1920 END;
1921 $$;
1923 CREATE TRIGGER "delete_snapshot_on_partial_delete"
1924 AFTER UPDATE OR DELETE ON "snapshot_issue"
1925 FOR EACH ROW EXECUTE PROCEDURE
1926 "delete_snapshot_on_partial_delete_trigger"();
1928 COMMENT ON FUNCTION "delete_snapshot_on_partial_delete_trigger"() IS 'Implementation of trigger "delete_snapshot_on_partial_delete" on table "snapshot_issue"';
1929 COMMENT ON TRIGGER "delete_snapshot_on_partial_delete" ON "snapshot_issue" IS 'Deletes whole snapshot if one issue is deleted from the snapshot';
1932 CREATE FUNCTION "copy_current_draft_data"
1933 ("initiative_id_p" "initiative"."id"%TYPE )
1934 RETURNS VOID
1935 LANGUAGE 'plpgsql' VOLATILE AS $$
1936 BEGIN
1937 PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p"
1938 FOR UPDATE;
1939 UPDATE "initiative" SET
1940 "location" = "draft"."location",
1941 "draft_text_search_data" = "draft"."text_search_data"
1942 FROM "current_draft" AS "draft"
1943 WHERE "initiative"."id" = "initiative_id_p"
1944 AND "draft"."initiative_id" = "initiative_id_p";
1945 END;
1946 $$;
1948 COMMENT ON FUNCTION "copy_current_draft_data"
1949 ( "initiative"."id"%TYPE )
1950 IS 'Helper function for function "copy_current_draft_data_trigger"';
1953 CREATE FUNCTION "copy_current_draft_data_trigger"()
1954 RETURNS TRIGGER
1955 LANGUAGE 'plpgsql' VOLATILE AS $$
1956 BEGIN
1957 IF TG_OP='DELETE' THEN
1958 PERFORM "copy_current_draft_data"(OLD."initiative_id");
1959 ELSE
1960 IF TG_OP='UPDATE' THEN
1961 IF COALESCE(OLD."inititiave_id" != NEW."initiative_id", TRUE) THEN
1962 PERFORM "copy_current_draft_data"(OLD."initiative_id");
1963 END IF;
1964 END IF;
1965 PERFORM "copy_current_draft_data"(NEW."initiative_id");
1966 END IF;
1967 RETURN NULL;
1968 END;
1969 $$;
1971 CREATE TRIGGER "copy_current_draft_data"
1972 AFTER INSERT OR UPDATE OR DELETE ON "draft"
1973 FOR EACH ROW EXECUTE PROCEDURE
1974 "copy_current_draft_data_trigger"();
1976 COMMENT ON FUNCTION "copy_current_draft_data_trigger"() IS 'Implementation of trigger "copy_current_draft_data" on table "draft"';
1977 COMMENT ON TRIGGER "copy_current_draft_data" ON "draft" IS 'Copy certain fields from most recent "draft" to "initiative"';
1980 CREATE VIEW "area_quorum" AS
1981 SELECT
1982 "area"."id" AS "area_id",
1983 ceil(
1984 "area"."quorum_standard"::FLOAT8 * "quorum_factor"::FLOAT8 ^ (
1985 coalesce(
1986 ( SELECT sum(
1987 ( extract(epoch from "area"."quorum_time")::FLOAT8 /
1988 extract(epoch from
1989 ("issue"."accepted"-"issue"."created") +
1990 "issue"."discussion_time" +
1991 "issue"."verification_time" +
1992 "issue"."voting_time"
1993 )::FLOAT8
1994 ) ^ "area"."quorum_exponent"::FLOAT8
1995 )
1996 FROM "issue" JOIN "policy"
1997 ON "issue"."policy_id" = "policy"."id"
1998 WHERE "issue"."area_id" = "area"."id"
1999 AND "issue"."accepted" NOTNULL
2000 AND "issue"."closed" ISNULL
2001 AND "policy"."polling" = FALSE
2002 )::FLOAT8, 0::FLOAT8
2003 ) / "area"."quorum_issues"::FLOAT8 - 1::FLOAT8
2004 ) * CASE WHEN "area"."quorum_den" ISNULL THEN 1 ELSE (
2005 SELECT "snapshot"."population"
2006 FROM "snapshot"
2007 WHERE "snapshot"."area_id" = "area"."id"
2008 AND "snapshot"."issue_id" ISNULL
2009 ORDER BY "snapshot"."id" DESC
2010 LIMIT 1
2011 ) END / coalesce("area"."quorum_den", 1)
2013 )::INT4 AS "issue_quorum"
2014 FROM "area";
2016 COMMENT ON VIEW "area_quorum" IS 'Area-based quorum considering number of open (accepted) issues';
2019 CREATE VIEW "area_with_unaccepted_issues" AS
2020 SELECT DISTINCT ON ("area"."id") "area".*
2021 FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id"
2022 WHERE "issue"."state" = 'admission';
2024 COMMENT ON VIEW "area_with_unaccepted_issues" IS 'All areas with unaccepted open issues (needed for issue admission system)';
2027 DROP VIEW "area_member_count";
2030 DROP TABLE "membership";
2033 DROP FUNCTION "membership_weight"
2034 ( "area_id_p" "area"."id"%TYPE,
2035 "member_id_p" "member"."id"%TYPE );
2038 DROP FUNCTION "membership_weight_with_skipping"
2039 ( "area_id_p" "area"."id"%TYPE,
2040 "member_id_p" "member"."id"%TYPE,
2041 "skip_member_ids_p" INT4[] ); -- TODO: ordering/cascade
2044 CREATE OR REPLACE VIEW "issue_delegation" AS
2045 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
2046 "issue"."id" AS "issue_id",
2047 "delegation"."id",
2048 "delegation"."truster_id",
2049 "delegation"."trustee_id",
2050 "delegation"."scope"
2051 FROM "issue"
2052 JOIN "area"
2053 ON "area"."id" = "issue"."area_id"
2054 JOIN "delegation"
2055 ON "delegation"."unit_id" = "area"."unit_id"
2056 OR "delegation"."area_id" = "area"."id"
2057 OR "delegation"."issue_id" = "issue"."id"
2058 JOIN "member"
2059 ON "delegation"."truster_id" = "member"."id"
2060 JOIN "privilege"
2061 ON "area"."unit_id" = "privilege"."unit_id"
2062 AND "delegation"."truster_id" = "privilege"."member_id"
2063 WHERE "member"."active" AND "privilege"."voting_right"
2064 ORDER BY
2065 "issue"."id",
2066 "delegation"."truster_id",
2067 "delegation"."scope" DESC;
2070 CREATE VIEW "unit_member" AS
2071 SELECT
2072 "unit"."id" AS "unit_id",
2073 "member"."id" AS "member_id"
2074 FROM "privilege"
2075 JOIN "unit" ON "unit_id" = "privilege"."unit_id"
2076 JOIN "member" ON "member"."id" = "privilege"."member_id"
2077 WHERE "privilege"."voting_right" AND "member"."active";
2079 COMMENT ON VIEW "unit_member" IS 'Active members with voting right in a unit';
2082 CREATE OR REPLACE VIEW "unit_member_count" AS
2083 SELECT
2084 "unit"."id" AS "unit_id",
2085 count("unit_member"."member_id") AS "member_count"
2086 FROM "unit" LEFT JOIN "unit_member"
2087 ON "unit"."id" = "unit_member"."unit_id"
2088 GROUP BY "unit"."id";
2090 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
2093 CREATE OR REPLACE VIEW "opening_draft" AS
2094 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
2095 ORDER BY "initiative_id", "id";
2098 CREATE OR REPLACE VIEW "current_draft" AS
2099 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
2100 ORDER BY "initiative_id", "id" DESC;
2103 CREATE OR REPLACE VIEW "issue_supporter_in_admission_state" AS
2104 SELECT
2105 "area"."unit_id",
2106 "issue"."area_id",
2107 "issue"."id" AS "issue_id",
2108 "supporter"."member_id",
2109 "direct_interest_snapshot"."weight"
2110 FROM "issue"
2111 JOIN "area" ON "area"."id" = "issue"."area_id"
2112 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
2113 JOIN "direct_interest_snapshot"
2114 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
2115 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
2116 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
2117 WHERE "issue"."state" = 'admission'::"issue_state";
2120 CREATE OR REPLACE VIEW "individual_suggestion_ranking" AS
2121 SELECT
2122 "opinion"."initiative_id",
2123 "opinion"."member_id",
2124 "direct_interest_snapshot"."weight",
2125 CASE WHEN
2126 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
2127 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
2128 THEN 1 ELSE
2129 CASE WHEN
2130 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
2131 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
2132 THEN 2 ELSE
2133 CASE WHEN
2134 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
2135 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
2136 THEN 3 ELSE 4 END
2137 END
2138 END AS "preference",
2139 "opinion"."suggestion_id"
2140 FROM "opinion"
2141 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
2142 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2143 JOIN "direct_interest_snapshot"
2144 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
2145 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
2146 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
2149 CREATE VIEW "expired_session" AS
2150 SELECT * FROM "session" WHERE now() > "expiry";
2152 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
2153 DELETE FROM "session" WHERE "id" = OLD."id";
2155 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
2156 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
2159 CREATE VIEW "expired_token" AS
2160 SELECT * FROM "token" WHERE now() > "expiry" AND NOT (
2161 "token_type" = 'authorization' AND "used" AND EXISTS (
2162 SELECT NULL FROM "token" AS "other"
2163 WHERE "other"."authorization_token_id" = "id" ) );
2165 CREATE RULE "delete" AS ON DELETE TO "expired_token" DO INSTEAD
2166 DELETE FROM "token" WHERE "id" = OLD."id";
2168 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';
2171 CREATE VIEW "unused_snapshot" AS
2172 SELECT "snapshot".* FROM "snapshot"
2173 LEFT JOIN "issue"
2174 ON "snapshot"."id" = "issue"."latest_snapshot_id"
2175 OR "snapshot"."id" = "issue"."admission_snapshot_id"
2176 OR "snapshot"."id" = "issue"."half_freeze_snapshot_id"
2177 OR "snapshot"."id" = "issue"."full_freeze_snapshot_id"
2178 WHERE "issue"."id" ISNULL;
2180 CREATE RULE "delete" AS ON DELETE TO "unused_snapshot" DO INSTEAD
2181 DELETE FROM "snapshot" WHERE "id" = OLD."id";
2183 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)';
2186 CREATE VIEW "expired_snapshot" AS
2187 SELECT "unused_snapshot".* FROM "unused_snapshot" CROSS JOIN "system_setting"
2188 WHERE "unused_snapshot"."calculated" <
2189 now() - "system_setting"."snapshot_retention";
2191 CREATE RULE "delete" AS ON DELETE TO "expired_snapshot" DO INSTEAD
2192 DELETE FROM "snapshot" WHERE "id" = OLD."id";
2194 COMMENT ON VIEW "expired_snapshot" IS 'Contains "unused_snapshot"s that are older than "system_setting"."snapshot_retention" (for deletion)';
2197 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest; for area and global delegation chains: always null';
2200 CREATE OR REPLACE FUNCTION "delegation_chain"
2201 ( "member_id_p" "member"."id"%TYPE,
2202 "unit_id_p" "unit"."id"%TYPE,
2203 "area_id_p" "area"."id"%TYPE,
2204 "issue_id_p" "issue"."id"%TYPE,
2205 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2206 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2207 RETURNS SETOF "delegation_chain_row"
2208 LANGUAGE 'plpgsql' STABLE AS $$
2209 DECLARE
2210 "scope_v" "delegation_scope";
2211 "unit_id_v" "unit"."id"%TYPE;
2212 "area_id_v" "area"."id"%TYPE;
2213 "issue_row" "issue"%ROWTYPE;
2214 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2215 "loop_member_id_v" "member"."id"%TYPE;
2216 "output_row" "delegation_chain_row";
2217 "output_rows" "delegation_chain_row"[];
2218 "simulate_v" BOOLEAN;
2219 "simulate_here_v" BOOLEAN;
2220 "delegation_row" "delegation"%ROWTYPE;
2221 "row_count" INT4;
2222 "i" INT4;
2223 "loop_v" BOOLEAN;
2224 BEGIN
2225 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
2226 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
2227 END IF;
2228 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
2229 "simulate_v" := TRUE;
2230 ELSE
2231 "simulate_v" := FALSE;
2232 END IF;
2233 IF
2234 "unit_id_p" NOTNULL AND
2235 "area_id_p" ISNULL AND
2236 "issue_id_p" ISNULL
2237 THEN
2238 "scope_v" := 'unit';
2239 "unit_id_v" := "unit_id_p";
2240 ELSIF
2241 "unit_id_p" ISNULL AND
2242 "area_id_p" NOTNULL AND
2243 "issue_id_p" ISNULL
2244 THEN
2245 "scope_v" := 'area';
2246 "area_id_v" := "area_id_p";
2247 SELECT "unit_id" INTO "unit_id_v"
2248 FROM "area" WHERE "id" = "area_id_v";
2249 ELSIF
2250 "unit_id_p" ISNULL AND
2251 "area_id_p" ISNULL AND
2252 "issue_id_p" NOTNULL
2253 THEN
2254 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
2255 IF "issue_row"."id" ISNULL THEN
2256 RETURN;
2257 END IF;
2258 IF "issue_row"."closed" NOTNULL THEN
2259 IF "simulate_v" THEN
2260 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
2261 END IF;
2262 FOR "output_row" IN
2263 SELECT * FROM
2264 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
2265 LOOP
2266 RETURN NEXT "output_row";
2267 END LOOP;
2268 RETURN;
2269 END IF;
2270 "scope_v" := 'issue';
2271 SELECT "area_id" INTO "area_id_v"
2272 FROM "issue" WHERE "id" = "issue_id_p";
2273 SELECT "unit_id" INTO "unit_id_v"
2274 FROM "area" WHERE "id" = "area_id_v";
2275 ELSE
2276 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2277 END IF;
2278 "visited_member_ids" := '{}';
2279 "loop_member_id_v" := NULL;
2280 "output_rows" := '{}';
2281 "output_row"."index" := 0;
2282 "output_row"."member_id" := "member_id_p";
2283 "output_row"."member_valid" := TRUE;
2284 "output_row"."participation" := FALSE;
2285 "output_row"."overridden" := FALSE;
2286 "output_row"."disabled_out" := FALSE;
2287 "output_row"."scope_out" := NULL;
2288 LOOP
2289 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2290 "loop_member_id_v" := "output_row"."member_id";
2291 ELSE
2292 "visited_member_ids" :=
2293 "visited_member_ids" || "output_row"."member_id";
2294 END IF;
2295 IF "output_row"."participation" ISNULL THEN
2296 "output_row"."overridden" := NULL;
2297 ELSIF "output_row"."participation" THEN
2298 "output_row"."overridden" := TRUE;
2299 END IF;
2300 "output_row"."scope_in" := "output_row"."scope_out";
2301 "output_row"."member_valid" := EXISTS (
2302 SELECT NULL FROM "member" JOIN "privilege"
2303 ON "privilege"."member_id" = "member"."id"
2304 AND "privilege"."unit_id" = "unit_id_v"
2305 WHERE "id" = "output_row"."member_id"
2306 AND "member"."active" AND "privilege"."voting_right"
2307 );
2308 "simulate_here_v" := (
2309 "simulate_v" AND
2310 "output_row"."member_id" = "member_id_p"
2311 );
2312 "delegation_row" := ROW(NULL);
2313 IF "output_row"."member_valid" OR "simulate_here_v" THEN
2314 IF "scope_v" = 'unit' THEN
2315 IF NOT "simulate_here_v" THEN
2316 SELECT * INTO "delegation_row" FROM "delegation"
2317 WHERE "truster_id" = "output_row"."member_id"
2318 AND "unit_id" = "unit_id_v";
2319 END IF;
2320 ELSIF "scope_v" = 'area' THEN
2321 IF "simulate_here_v" THEN
2322 IF "simulate_trustee_id_p" ISNULL THEN
2323 SELECT * INTO "delegation_row" FROM "delegation"
2324 WHERE "truster_id" = "output_row"."member_id"
2325 AND "unit_id" = "unit_id_v";
2326 END IF;
2327 ELSE
2328 SELECT * INTO "delegation_row" FROM "delegation"
2329 WHERE "truster_id" = "output_row"."member_id"
2330 AND (
2331 "unit_id" = "unit_id_v" OR
2332 "area_id" = "area_id_v"
2333 )
2334 ORDER BY "scope" DESC;
2335 END IF;
2336 ELSIF "scope_v" = 'issue' THEN
2337 IF "issue_row"."fully_frozen" ISNULL THEN
2338 "output_row"."participation" := EXISTS (
2339 SELECT NULL FROM "interest"
2340 WHERE "issue_id" = "issue_id_p"
2341 AND "member_id" = "output_row"."member_id"
2342 );
2343 ELSE
2344 IF "output_row"."member_id" = "member_id_p" THEN
2345 "output_row"."participation" := EXISTS (
2346 SELECT NULL FROM "direct_voter"
2347 WHERE "issue_id" = "issue_id_p"
2348 AND "member_id" = "output_row"."member_id"
2349 );
2350 ELSE
2351 "output_row"."participation" := NULL;
2352 END IF;
2353 END IF;
2354 IF "simulate_here_v" THEN
2355 IF "simulate_trustee_id_p" ISNULL THEN
2356 SELECT * INTO "delegation_row" FROM "delegation"
2357 WHERE "truster_id" = "output_row"."member_id"
2358 AND (
2359 "unit_id" = "unit_id_v" OR
2360 "area_id" = "area_id_v"
2361 )
2362 ORDER BY "scope" DESC;
2363 END IF;
2364 ELSE
2365 SELECT * INTO "delegation_row" FROM "delegation"
2366 WHERE "truster_id" = "output_row"."member_id"
2367 AND (
2368 "unit_id" = "unit_id_v" OR
2369 "area_id" = "area_id_v" OR
2370 "issue_id" = "issue_id_p"
2371 )
2372 ORDER BY "scope" DESC;
2373 END IF;
2374 END IF;
2375 ELSE
2376 "output_row"."participation" := FALSE;
2377 END IF;
2378 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
2379 "output_row"."scope_out" := "scope_v";
2380 "output_rows" := "output_rows" || "output_row";
2381 "output_row"."member_id" := "simulate_trustee_id_p";
2382 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2383 "output_row"."scope_out" := "delegation_row"."scope";
2384 "output_rows" := "output_rows" || "output_row";
2385 "output_row"."member_id" := "delegation_row"."trustee_id";
2386 ELSIF "delegation_row"."scope" NOTNULL THEN
2387 "output_row"."scope_out" := "delegation_row"."scope";
2388 "output_row"."disabled_out" := TRUE;
2389 "output_rows" := "output_rows" || "output_row";
2390 EXIT;
2391 ELSE
2392 "output_row"."scope_out" := NULL;
2393 "output_rows" := "output_rows" || "output_row";
2394 EXIT;
2395 END IF;
2396 EXIT WHEN "loop_member_id_v" NOTNULL;
2397 "output_row"."index" := "output_row"."index" + 1;
2398 END LOOP;
2399 "row_count" := array_upper("output_rows", 1);
2400 "i" := 1;
2401 "loop_v" := FALSE;
2402 LOOP
2403 "output_row" := "output_rows"["i"];
2404 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2405 IF "loop_v" THEN
2406 IF "i" + 1 = "row_count" THEN
2407 "output_row"."loop" := 'last';
2408 ELSIF "i" = "row_count" THEN
2409 "output_row"."loop" := 'repetition';
2410 ELSE
2411 "output_row"."loop" := 'intermediate';
2412 END IF;
2413 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2414 "output_row"."loop" := 'first';
2415 "loop_v" := TRUE;
2416 END IF;
2417 IF "scope_v" = 'unit' THEN
2418 "output_row"."participation" := NULL;
2419 END IF;
2420 RETURN NEXT "output_row";
2421 "i" := "i" + 1;
2422 END LOOP;
2423 RETURN;
2424 END;
2425 $$;
2428 CREATE OR REPLACE FUNCTION "get_initiatives_for_notification"
2429 ( "recipient_id_p" "member"."id"%TYPE )
2430 RETURNS SETOF "initiative_for_notification"
2431 LANGUAGE 'plpgsql' VOLATILE AS $$
2432 DECLARE
2433 "result_row" "initiative_for_notification"%ROWTYPE;
2434 "last_draft_id_v" "draft"."id"%TYPE;
2435 "last_suggestion_id_v" "suggestion"."id"%TYPE;
2436 BEGIN
2437 PERFORM "require_transaction_isolation"();
2438 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
2439 FOR "result_row" IN
2440 SELECT * FROM "initiative_for_notification"
2441 WHERE "recipient_id" = "recipient_id_p"
2442 LOOP
2443 SELECT "id" INTO "last_draft_id_v" FROM "draft"
2444 WHERE "draft"."initiative_id" = "result_row"."initiative_id"
2445 ORDER BY "id" DESC LIMIT 1;
2446 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
2447 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
2448 ORDER BY "id" DESC LIMIT 1;
2449 INSERT INTO "notification_initiative_sent"
2450 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
2451 VALUES (
2452 "recipient_id_p",
2453 "result_row"."initiative_id",
2454 "last_draft_id_v",
2455 "last_suggestion_id_v" )
2456 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
2457 "last_draft_id" = "last_draft_id_v",
2458 "last_suggestion_id" = "last_suggestion_id_v";
2459 RETURN NEXT "result_row";
2460 END LOOP;
2461 DELETE FROM "notification_initiative_sent"
2462 USING "initiative", "issue"
2463 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
2464 AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
2465 AND "issue"."id" = "initiative"."issue_id"
2466 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
2467 UPDATE "member" SET
2468 "notification_counter" = "notification_counter" + 1,
2469 "notification_sent" = now()
2470 WHERE "id" = "recipient_id_p";
2471 RETURN;
2472 END;
2473 $$;
2476 CREATE OR REPLACE FUNCTION "calculate_member_counts"()
2477 RETURNS VOID
2478 LANGUAGE 'plpgsql' VOLATILE AS $$
2479 BEGIN
2480 PERFORM "require_transaction_isolation"();
2481 DELETE FROM "member_count";
2482 INSERT INTO "member_count" ("total_count")
2483 SELECT "total_count" FROM "member_count_view";
2484 UPDATE "unit" SET "member_count" = "view"."member_count"
2485 FROM "unit_member_count" AS "view"
2486 WHERE "view"."unit_id" = "unit"."id";
2487 RETURN;
2488 END;
2489 $$;
2491 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"';
2494 CREATE FUNCTION "calculate_area_quorum"()
2495 RETURNS VOID
2496 LANGUAGE 'plpgsql' VOLATILE AS $$
2497 BEGIN
2498 PERFORM "dont_require_transaction_isolation"();
2499 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
2500 FROM "area_quorum" AS "view"
2501 WHERE "view"."area_id" = "area"."id";
2502 RETURN;
2503 END;
2504 $$;
2506 COMMENT ON FUNCTION "calculate_area_quorum"() IS 'Calculate column "issue_quorum" in table "area" from view "area_quorum"';
2509 DROP VIEW "remaining_harmonic_initiative_weight_summands";
2510 DROP VIEW "remaining_harmonic_supporter_weight";
2513 CREATE VIEW "remaining_harmonic_supporter_weight" AS
2514 SELECT
2515 "direct_interest_snapshot"."snapshot_id",
2516 "direct_interest_snapshot"."issue_id",
2517 "direct_interest_snapshot"."member_id",
2518 "direct_interest_snapshot"."weight" AS "weight_num",
2519 count("initiative"."id") AS "weight_den"
2520 FROM "issue"
2521 JOIN "direct_interest_snapshot"
2522 ON "issue"."latest_snapshot_id" = "direct_interest_snapshot"."snapshot_id"
2523 AND "issue"."id" = "direct_interest_snapshot"."issue_id"
2524 JOIN "initiative"
2525 ON "issue"."id" = "initiative"."issue_id"
2526 AND "initiative"."harmonic_weight" ISNULL
2527 JOIN "direct_supporter_snapshot"
2528 ON "issue"."latest_snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
2529 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
2530 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
2531 AND (
2532 "direct_supporter_snapshot"."satisfied" = TRUE OR
2533 coalesce("initiative"."admitted", FALSE) = FALSE
2534 )
2535 GROUP BY
2536 "direct_interest_snapshot"."snapshot_id",
2537 "direct_interest_snapshot"."issue_id",
2538 "direct_interest_snapshot"."member_id",
2539 "direct_interest_snapshot"."weight";
2542 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
2543 SELECT
2544 "initiative"."issue_id",
2545 "initiative"."id" AS "initiative_id",
2546 "initiative"."admitted",
2547 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
2548 "remaining_harmonic_supporter_weight"."weight_den"
2549 FROM "remaining_harmonic_supporter_weight"
2550 JOIN "initiative"
2551 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
2552 AND "initiative"."harmonic_weight" ISNULL
2553 JOIN "direct_supporter_snapshot"
2554 ON "remaining_harmonic_supporter_weight"."snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
2555 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
2556 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
2557 AND (
2558 "direct_supporter_snapshot"."satisfied" = TRUE OR
2559 coalesce("initiative"."admitted", FALSE) = FALSE
2560 )
2561 GROUP BY
2562 "initiative"."issue_id",
2563 "initiative"."id",
2564 "initiative"."admitted",
2565 "remaining_harmonic_supporter_weight"."weight_den";
2568 DROP FUNCTION "create_population_snapshot"
2569 ( "issue_id_p" "issue"."id"%TYPE );
2572 DROP FUNCTION "weight_of_added_delegations_for_population_snapshot"
2573 ( "issue_id_p" "issue"."id"%TYPE,
2574 "member_id_p" "member"."id"%TYPE,
2575 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE );
2578 DROP FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2579 ( "issue_id_p" "issue"."id"%TYPE,
2580 "member_id_p" "member"."id"%TYPE,
2581 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE );
2584 CREATE FUNCTION "weight_of_added_delegations_for_snapshot"
2585 ( "snapshot_id_p" "snapshot"."id"%TYPE,
2586 "issue_id_p" "issue"."id"%TYPE,
2587 "member_id_p" "member"."id"%TYPE,
2588 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2589 RETURNS "direct_interest_snapshot"."weight"%TYPE
2590 LANGUAGE 'plpgsql' VOLATILE AS $$
2591 DECLARE
2592 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2593 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2594 "weight_v" INT4;
2595 "sub_weight_v" INT4;
2596 BEGIN
2597 PERFORM "require_transaction_isolation"();
2598 "weight_v" := 0;
2599 FOR "issue_delegation_row" IN
2600 SELECT * FROM "issue_delegation"
2601 WHERE "trustee_id" = "member_id_p"
2602 AND "issue_id" = "issue_id_p"
2603 LOOP
2604 IF NOT EXISTS (
2605 SELECT NULL FROM "direct_interest_snapshot"
2606 WHERE "snapshot_id" = "snapshot_id_p"
2607 AND "issue_id" = "issue_id_p"
2608 AND "member_id" = "issue_delegation_row"."truster_id"
2609 ) AND NOT EXISTS (
2610 SELECT NULL FROM "delegating_interest_snapshot"
2611 WHERE "snapshot_id" = "snapshot_id_p"
2612 AND "issue_id" = "issue_id_p"
2613 AND "member_id" = "issue_delegation_row"."truster_id"
2614 ) THEN
2615 "delegate_member_ids_v" :=
2616 "member_id_p" || "delegate_member_ids_p";
2617 INSERT INTO "delegating_interest_snapshot" (
2618 "snapshot_id",
2619 "issue_id",
2620 "member_id",
2621 "scope",
2622 "delegate_member_ids"
2623 ) VALUES (
2624 "snapshot_id_p",
2625 "issue_id_p",
2626 "issue_delegation_row"."truster_id",
2627 "issue_delegation_row"."scope",
2628 "delegate_member_ids_v"
2629 );
2630 "sub_weight_v" := 1 +
2631 "weight_of_added_delegations_for_snapshot"(
2632 "snapshot_id_p",
2633 "issue_id_p",
2634 "issue_delegation_row"."truster_id",
2635 "delegate_member_ids_v"
2636 );
2637 UPDATE "delegating_interest_snapshot"
2638 SET "weight" = "sub_weight_v"
2639 WHERE "snapshot_id" = "snapshot_id_p"
2640 AND "issue_id" = "issue_id_p"
2641 AND "member_id" = "issue_delegation_row"."truster_id";
2642 "weight_v" := "weight_v" + "sub_weight_v";
2643 END IF;
2644 END LOOP;
2645 RETURN "weight_v";
2646 END;
2647 $$;
2649 COMMENT ON FUNCTION "weight_of_added_delegations_for_snapshot"
2650 ( "snapshot"."id"%TYPE,
2651 "issue"."id"%TYPE,
2652 "member"."id"%TYPE,
2653 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2654 IS 'Helper function for "fill_snapshot" function';
2657 DROP FUNCTION "create_interest_snapshot"
2658 ( "issue_id_p" "issue"."id"%TYPE );
2661 DROP FUNCTION "create_snapshot"
2662 ( "issue_id_p" "issue"."id"%TYPE );
2665 CREATE FUNCTION "take_snapshot"
2666 ( "issue_id_p" "issue"."id"%TYPE,
2667 "area_id_p" "area"."id"%TYPE = NULL )
2668 RETURNS "snapshot"."id"%TYPE
2669 LANGUAGE 'plpgsql' VOLATILE AS $$
2670 DECLARE
2671 "area_id_v" "area"."id"%TYPE;
2672 "unit_id_v" "unit"."id"%TYPE;
2673 "snapshot_id_v" "snapshot"."id"%TYPE;
2674 "issue_id_v" "issue"."id"%TYPE;
2675 "member_id_v" "member"."id"%TYPE;
2676 BEGIN
2677 IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN
2678 RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL';
2679 END IF;
2680 PERFORM "require_transaction_isolation"();
2681 IF "issue_id_p" ISNULL THEN
2682 "area_id_v" := "area_id_p";
2683 ELSE
2684 SELECT "area_id" INTO "area_id_v"
2685 FROM "issue" WHERE "id" = "issue_id_p";
2686 END IF;
2687 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_p";
2688 INSERT INTO "snapshot" ("area_id", "issue_id")
2689 VALUES ("area_id_v", "issue_id_p")
2690 RETURNING "id" INTO "snapshot_id_v";
2691 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
2692 SELECT "snapshot_id_v", "member_id"
2693 FROM "unit_member" WHERE "unit_id" = "unit_id_v";
2694 UPDATE "snapshot" SET
2695 "population" = (
2696 SELECT count(1) FROM "snapshot_population"
2697 WHERE "snapshot_id" = "snapshot_id_v"
2698 ) WHERE "id" = "snapshot_id_v";
2699 FOR "issue_id_v" IN
2700 SELECT "id" FROM "issue"
2701 WHERE CASE WHEN "issue_id_p" ISNULL THEN
2702 "area_id" = "area_id_p" AND
2703 "state" = 'admission'
2704 ELSE
2705 "id" = "issue_id_p"
2706 END
2707 LOOP
2708 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
2709 VALUES ("snapshot_id_v", "issue_id_v");
2710 INSERT INTO "direct_interest_snapshot"
2711 ("snapshot_id", "issue_id", "member_id")
2712 SELECT
2713 "snapshot_id_v" AS "snapshot_id",
2714 "issue_id_v" AS "issue_id",
2715 "member"."id" AS "member_id"
2716 FROM "issue"
2717 JOIN "area" ON "issue"."area_id" = "area"."id"
2718 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2719 JOIN "member" ON "interest"."member_id" = "member"."id"
2720 JOIN "privilege"
2721 ON "privilege"."unit_id" = "area"."unit_id"
2722 AND "privilege"."member_id" = "member"."id"
2723 WHERE "issue"."id" = "issue_id_v"
2724 AND "member"."active" AND "privilege"."voting_right";
2725 FOR "member_id_v" IN
2726 SELECT "member_id" FROM "direct_interest_snapshot"
2727 WHERE "snapshot_id" = "snapshot_id_v"
2728 AND "issue_id" = "issue_id_v"
2729 LOOP
2730 UPDATE "direct_interest_snapshot" SET
2731 "weight" = 1 +
2732 "weight_of_added_delegations_for_snapshot"(
2733 "snapshot_id_v",
2734 "issue_id_v",
2735 "member_id_v",
2736 '{}'
2737 )
2738 WHERE "snapshot_id" = "snapshot_id_v"
2739 AND "issue_id" = "issue_id_v"
2740 AND "member_id" = "member_id_v";
2741 END LOOP;
2742 INSERT INTO "direct_supporter_snapshot"
2743 ( "snapshot_id", "issue_id", "initiative_id", "member_id",
2744 "draft_id", "informed", "satisfied" )
2745 SELECT
2746 "snapshot_id_v" AS "snapshot_id",
2747 "issue_id_v" AS "issue_id",
2748 "initiative"."id" AS "initiative_id",
2749 "supporter"."member_id" AS "member_id",
2750 "supporter"."draft_id" AS "draft_id",
2751 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2752 NOT EXISTS (
2753 SELECT NULL FROM "critical_opinion"
2754 WHERE "initiative_id" = "initiative"."id"
2755 AND "member_id" = "supporter"."member_id"
2756 ) AS "satisfied"
2757 FROM "initiative"
2758 JOIN "supporter"
2759 ON "supporter"."initiative_id" = "initiative"."id"
2760 JOIN "current_draft"
2761 ON "initiative"."id" = "current_draft"."initiative_id"
2762 JOIN "direct_interest_snapshot"
2763 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"
2764 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"
2765 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
2766 WHERE "initiative"."issue_id" = "issue_id_v";
2767 DELETE FROM "temporary_suggestion_counts";
2768 INSERT INTO "temporary_suggestion_counts"
2769 ( "id",
2770 "minus2_unfulfilled_count", "minus2_fulfilled_count",
2771 "minus1_unfulfilled_count", "minus1_fulfilled_count",
2772 "plus1_unfulfilled_count", "plus1_fulfilled_count",
2773 "plus2_unfulfilled_count", "plus2_fulfilled_count" )
2774 SELECT
2775 "suggestion"."id",
2776 ( SELECT coalesce(sum("di"."weight"), 0)
2777 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2778 ON "di"."snapshot_id" = "snapshot_id_v"
2779 AND "di"."issue_id" = "issue_id_v"
2780 AND "di"."member_id" = "opinion"."member_id"
2781 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2782 AND "opinion"."degree" = -2
2783 AND "opinion"."fulfilled" = FALSE
2784 ) AS "minus2_unfulfilled_count",
2785 ( SELECT coalesce(sum("di"."weight"), 0)
2786 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2787 ON "di"."snapshot_id" = "snapshot_id_v"
2788 AND "di"."issue_id" = "issue_id_v"
2789 AND "di"."member_id" = "opinion"."member_id"
2790 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2791 AND "opinion"."degree" = -2
2792 AND "opinion"."fulfilled" = TRUE
2793 ) AS "minus2_fulfilled_count",
2794 ( SELECT coalesce(sum("di"."weight"), 0)
2795 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2796 ON "di"."snapshot_id" = "snapshot_id_v"
2797 AND "di"."issue_id" = "issue_id_v"
2798 AND "di"."member_id" = "opinion"."member_id"
2799 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2800 AND "opinion"."degree" = -1
2801 AND "opinion"."fulfilled" = FALSE
2802 ) AS "minus1_unfulfilled_count",
2803 ( SELECT coalesce(sum("di"."weight"), 0)
2804 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2805 ON "di"."snapshot_id" = "snapshot_id_v"
2806 AND "di"."issue_id" = "issue_id_v"
2807 AND "di"."member_id" = "opinion"."member_id"
2808 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2809 AND "opinion"."degree" = -1
2810 AND "opinion"."fulfilled" = TRUE
2811 ) AS "minus1_fulfilled_count",
2812 ( SELECT coalesce(sum("di"."weight"), 0)
2813 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2814 ON "di"."snapshot_id" = "snapshot_id_v"
2815 AND "di"."issue_id" = "issue_id_v"
2816 AND "di"."member_id" = "opinion"."member_id"
2817 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2818 AND "opinion"."degree" = 1
2819 AND "opinion"."fulfilled" = FALSE
2820 ) AS "plus1_unfulfilled_count",
2821 ( SELECT coalesce(sum("di"."weight"), 0)
2822 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2823 ON "di"."snapshot_id" = "snapshot_id_v"
2824 AND "di"."issue_id" = "issue_id_v"
2825 AND "di"."member_id" = "opinion"."member_id"
2826 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2827 AND "opinion"."degree" = 1
2828 AND "opinion"."fulfilled" = TRUE
2829 ) AS "plus1_fulfilled_count",
2830 ( SELECT coalesce(sum("di"."weight"), 0)
2831 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2832 ON "di"."snapshot_id" = "snapshot_id_v"
2833 AND "di"."issue_id" = "issue_id_v"
2834 AND "di"."member_id" = "opinion"."member_id"
2835 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2836 AND "opinion"."degree" = 2
2837 AND "opinion"."fulfilled" = FALSE
2838 ) AS "plus2_unfulfilled_count",
2839 ( SELECT coalesce(sum("di"."weight"), 0)
2840 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2841 ON "di"."snapshot_id" = "snapshot_id_v"
2842 AND "di"."issue_id" = "issue_id_v"
2843 AND "di"."member_id" = "opinion"."member_id"
2844 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2845 AND "opinion"."degree" = 2
2846 AND "opinion"."fulfilled" = TRUE
2847 ) AS "plus2_fulfilled_count"
2848 FROM "suggestion" JOIN "initiative"
2849 ON "suggestion"."initiative_id" = "initiative"."id"
2850 WHERE "initiative"."issue_id" = "issue_id_v";
2851 END LOOP;
2852 RETURN "snapshot_id_v";
2853 END;
2854 $$;
2856 COMMENT ON FUNCTION "take_snapshot"
2857 ( "issue"."id"%TYPE,
2858 "area"."id"%TYPE )
2859 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.';
2862 DROP FUNCTION "set_snapshot_event"
2863 ( "issue_id_p" "issue"."id"%TYPE,
2864 "event_p" "snapshot_event" );
2867 CREATE FUNCTION "finish_snapshot"
2868 ( "issue_id_p" "issue"."id"%TYPE )
2869 RETURNS VOID
2870 LANGUAGE 'plpgsql' VOLATILE AS $$
2871 DECLARE
2872 "snapshot_id_v" "snapshot"."id"%TYPE;
2873 BEGIN
2874 -- NOTE: function does not require snapshot isolation but we don't call
2875 -- "dont_require_snapshot_isolation" here because this function is
2876 -- also invoked by "check_issue"
2877 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
2878 SELECT "id" INTO "snapshot_id_v" FROM "snapshot"
2879 ORDER BY "id" DESC LIMIT 1;
2880 UPDATE "issue" SET
2881 "calculated" = "snapshot"."calculated",
2882 "latest_snapshot_id" = "snapshot_id_v",
2883 "population" = "snapshot"."population"
2884 FROM "snapshot"
2885 WHERE "issue"."id" = "issue_id_p"
2886 AND "snapshot"."id" = "snapshot_id_v";
2887 UPDATE "initiative" SET
2888 "supporter_count" = (
2889 SELECT coalesce(sum("di"."weight"), 0)
2890 FROM "direct_interest_snapshot" AS "di"
2891 JOIN "direct_supporter_snapshot" AS "ds"
2892 ON "di"."member_id" = "ds"."member_id"
2893 WHERE "di"."snapshot_id" = "snapshot_id_v"
2894 AND "di"."issue_id" = "issue_id_p"
2895 AND "ds"."snapshot_id" = "snapshot_id_v"
2896 AND "ds"."initiative_id" = "initiative"."id"
2897 ),
2898 "informed_supporter_count" = (
2899 SELECT coalesce(sum("di"."weight"), 0)
2900 FROM "direct_interest_snapshot" AS "di"
2901 JOIN "direct_supporter_snapshot" AS "ds"
2902 ON "di"."member_id" = "ds"."member_id"
2903 WHERE "di"."snapshot_id" = "snapshot_id_v"
2904 AND "di"."issue_id" = "issue_id_p"
2905 AND "ds"."snapshot_id" = "snapshot_id_v"
2906 AND "ds"."initiative_id" = "initiative"."id"
2907 AND "ds"."informed"
2908 ),
2909 "satisfied_supporter_count" = (
2910 SELECT coalesce(sum("di"."weight"), 0)
2911 FROM "direct_interest_snapshot" AS "di"
2912 JOIN "direct_supporter_snapshot" AS "ds"
2913 ON "di"."member_id" = "ds"."member_id"
2914 WHERE "di"."snapshot_id" = "snapshot_id_v"
2915 AND "di"."issue_id" = "issue_id_p"
2916 AND "ds"."snapshot_id" = "snapshot_id_v"
2917 AND "ds"."initiative_id" = "initiative"."id"
2918 AND "ds"."satisfied"
2919 ),
2920 "satisfied_informed_supporter_count" = (
2921 SELECT coalesce(sum("di"."weight"), 0)
2922 FROM "direct_interest_snapshot" AS "di"
2923 JOIN "direct_supporter_snapshot" AS "ds"
2924 ON "di"."member_id" = "ds"."member_id"
2925 WHERE "di"."snapshot_id" = "snapshot_id_v"
2926 AND "di"."issue_id" = "issue_id_p"
2927 AND "ds"."snapshot_id" = "snapshot_id_v"
2928 AND "ds"."initiative_id" = "initiative"."id"
2929 AND "ds"."informed"
2930 AND "ds"."satisfied"
2931 )
2932 WHERE "issue_id" = "issue_id_p";
2933 UPDATE "suggestion" SET
2934 "minus2_unfulfilled_count" = "temp"."minus2_unfulfilled_count",
2935 "minus2_fulfilled_count" = "temp"."minus2_fulfilled_count",
2936 "minus1_unfulfilled_count" = "temp"."minus1_unfulfilled_count",
2937 "minus1_fulfilled_count" = "temp"."minus1_fulfilled_count",
2938 "plus1_unfulfilled_count" = "temp"."plus1_unfulfilled_count",
2939 "plus1_fulfilled_count" = "temp"."plus1_fulfilled_count",
2940 "plus2_unfulfilled_count" = "temp"."plus2_unfulfilled_count",
2941 "plus2_fulfilled_count" = "temp"."plus2_fulfilled_count"
2942 FROM "temporary_suggestion_counts" AS "temp", "initiative"
2943 WHERE "temp"."id" = "suggestion"."id"
2944 AND "initiative"."issue_id" = "issue_id_p"
2945 AND "suggestion"."initiative_id" = "initiative"."id";
2946 DELETE FROM "temporary_suggestion_counts";
2947 RETURN;
2948 END;
2949 $$;
2951 COMMENT ON FUNCTION "finish_snapshot"
2952 ( "issue"."id"%TYPE )
2953 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)';
2956 CREATE FUNCTION "issue_admission"
2957 ( "area_id_p" "area"."id"%TYPE )
2958 RETURNS BOOLEAN
2959 LANGUAGE 'plpgsql' VOLATILE AS $$
2960 DECLARE
2961 "issue_id_v" "issue"."id"%TYPE;
2962 BEGIN
2963 PERFORM "dont_require_transaction_isolation"();
2964 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
2965 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
2966 FROM "area_quorum" AS "view"
2967 WHERE "area"."id" = "view"."area_id"
2968 AND "area"."id" = "area_id_p";
2969 SELECT "id" INTO "issue_id_v" FROM "issue_for_admission"
2970 WHERE "area_id" = "area_id_p";
2971 IF "issue_id_v" ISNULL THEN RETURN FALSE; END IF;
2972 UPDATE "issue" SET
2973 "admission_snapshot_id" = "latest_snapshot_id",
2974 "state" = 'discussion',
2975 "accepted" = now(),
2976 "phase_finished" = NULL
2977 WHERE "id" = "issue_id_v";
2978 RETURN TRUE;
2979 END;
2980 $$;
2982 COMMENT ON FUNCTION "issue_admission"
2983 ( "area"."id"%TYPE )
2984 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';
2987 CREATE OR REPLACE FUNCTION "check_issue"
2988 ( "issue_id_p" "issue"."id"%TYPE,
2989 "persist" "check_issue_persistence" )
2990 RETURNS "check_issue_persistence"
2991 LANGUAGE 'plpgsql' VOLATILE AS $$
2992 DECLARE
2993 "issue_row" "issue"%ROWTYPE;
2994 "last_calculated_v" "snapshot"."calculated"%TYPE;
2995 "policy_row" "policy"%ROWTYPE;
2996 "initiative_row" "initiative"%ROWTYPE;
2997 "state_v" "issue_state";
2998 BEGIN
2999 PERFORM "require_transaction_isolation"();
3000 IF "persist" ISNULL THEN
3001 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
3002 FOR UPDATE;
3003 SELECT "calculated" INTO "last_calculated_v"
3004 FROM "snapshot" JOIN "snapshot_issue"
3005 ON "snapshot"."id" = "snapshot_issue"."snapshot_id"
3006 WHERE "snapshot_issue"."issue_id" = "issue_id_p";
3007 IF "issue_row"."closed" NOTNULL THEN
3008 RETURN NULL;
3009 END IF;
3010 "persist"."state" := "issue_row"."state";
3011 IF
3012 ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=
3013 "issue_row"."created" + "issue_row"."max_admission_time" ) OR
3014 ( "issue_row"."state" = 'discussion' AND now() >=
3015 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
3016 ( "issue_row"."state" = 'verification' AND now() >=
3017 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
3018 ( "issue_row"."state" = 'voting' AND now() >=
3019 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
3020 THEN
3021 "persist"."phase_finished" := TRUE;
3022 ELSE
3023 "persist"."phase_finished" := FALSE;
3024 END IF;
3025 IF
3026 NOT EXISTS (
3027 -- all initiatives are revoked
3028 SELECT NULL FROM "initiative"
3029 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3030 ) AND (
3031 -- and issue has not been accepted yet
3032 "persist"."state" = 'admission' OR
3033 -- or verification time has elapsed
3034 ( "persist"."state" = 'verification' AND
3035 "persist"."phase_finished" ) OR
3036 -- or no initiatives have been revoked lately
3037 NOT EXISTS (
3038 SELECT NULL FROM "initiative"
3039 WHERE "issue_id" = "issue_id_p"
3040 AND now() < "revoked" + "issue_row"."verification_time"
3041 )
3042 )
3043 THEN
3044 "persist"."issue_revoked" := TRUE;
3045 ELSE
3046 "persist"."issue_revoked" := FALSE;
3047 END IF;
3048 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
3049 UPDATE "issue" SET "phase_finished" = now()
3050 WHERE "id" = "issue_row"."id";
3051 RETURN "persist";
3052 ELSIF
3053 "persist"."state" IN ('admission', 'discussion', 'verification')
3054 THEN
3055 RETURN "persist";
3056 ELSE
3057 RETURN NULL;
3058 END IF;
3059 END IF;
3060 IF
3061 "persist"."state" IN ('admission', 'discussion', 'verification') AND
3062 coalesce("persist"."snapshot_created", FALSE) = FALSE
3063 THEN
3064 IF "persist"."state" != 'admission' THEN
3065 PERFORM "take_snapshot"("issue_id_p");
3066 PERFORM "finish_snapshot"("issue_id_p");
3067 END IF;
3068 "persist"."snapshot_created" = TRUE;
3069 IF "persist"."phase_finished" THEN
3070 IF "persist"."state" = 'admission' THEN
3071 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id";
3072 ELSIF "persist"."state" = 'discussion' THEN
3073 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id";
3074 ELSIF "persist"."state" = 'verification' THEN
3075 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id";
3076 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3077 SELECT * INTO "policy_row" FROM "policy"
3078 WHERE "id" = "issue_row"."policy_id";
3079 FOR "initiative_row" IN
3080 SELECT * FROM "initiative"
3081 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3082 FOR UPDATE
3083 LOOP
3084 IF
3085 "initiative_row"."polling" OR (
3086 "initiative_row"."satisfied_supporter_count" >
3087 "policy_row"."initiative_quorum" AND
3088 "initiative_row"."satisfied_supporter_count" *
3089 "policy_row"."initiative_quorum_den" >=
3090 "issue_row"."population" * "policy_row"."initiative_quorum_num"
3091 )
3092 THEN
3093 UPDATE "initiative" SET "admitted" = TRUE
3094 WHERE "id" = "initiative_row"."id";
3095 ELSE
3096 UPDATE "initiative" SET "admitted" = FALSE
3097 WHERE "id" = "initiative_row"."id";
3098 END IF;
3099 END LOOP;
3100 END IF;
3101 END IF;
3102 RETURN "persist";
3103 END IF;
3104 IF
3105 "persist"."state" IN ('admission', 'discussion', 'verification') AND
3106 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
3107 THEN
3108 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
3109 "persist"."harmonic_weights_set" = TRUE;
3110 IF
3111 "persist"."phase_finished" OR
3112 "persist"."issue_revoked" OR
3113 "persist"."state" = 'admission'
3114 THEN
3115 RETURN "persist";
3116 ELSE
3117 RETURN NULL;
3118 END IF;
3119 END IF;
3120 IF "persist"."issue_revoked" THEN
3121 IF "persist"."state" = 'admission' THEN
3122 "state_v" := 'canceled_revoked_before_accepted';
3123 ELSIF "persist"."state" = 'discussion' THEN
3124 "state_v" := 'canceled_after_revocation_during_discussion';
3125 ELSIF "persist"."state" = 'verification' THEN
3126 "state_v" := 'canceled_after_revocation_during_verification';
3127 END IF;
3128 UPDATE "issue" SET
3129 "state" = "state_v",
3130 "closed" = "phase_finished",
3131 "phase_finished" = NULL
3132 WHERE "id" = "issue_id_p";
3133 RETURN NULL;
3134 END IF;
3135 IF "persist"."state" = 'admission' THEN
3136 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
3137 FOR UPDATE;
3138 IF "issue_row"."phase_finished" NOTNULL THEN
3139 UPDATE "issue" SET
3140 "state" = 'canceled_issue_not_accepted',
3141 "closed" = "phase_finished",
3142 "phase_finished" = NULL
3143 WHERE "id" = "issue_id_p";
3144 END IF;
3145 RETURN NULL;
3146 END IF;
3147 IF "persist"."phase_finished" THEN
3148 IF "persist"."state" = 'discussion' THEN
3149 UPDATE "issue" SET
3150 "state" = 'verification',
3151 "half_frozen" = "phase_finished",
3152 "phase_finished" = NULL
3153 WHERE "id" = "issue_id_p";
3154 RETURN NULL;
3155 END IF;
3156 IF "persist"."state" = 'verification' THEN
3157 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
3158 FOR UPDATE;
3159 SELECT * INTO "policy_row" FROM "policy"
3160 WHERE "id" = "issue_row"."policy_id";
3161 IF EXISTS (
3162 SELECT NULL FROM "initiative"
3163 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
3164 ) THEN
3165 UPDATE "issue" SET
3166 "state" = 'voting',
3167 "fully_frozen" = "phase_finished",
3168 "phase_finished" = NULL
3169 WHERE "id" = "issue_id_p";
3170 ELSE
3171 UPDATE "issue" SET
3172 "state" = 'canceled_no_initiative_admitted',
3173 "fully_frozen" = "phase_finished",
3174 "closed" = "phase_finished",
3175 "phase_finished" = NULL
3176 WHERE "id" = "issue_id_p";
3177 -- NOTE: The following DELETE statements have effect only when
3178 -- issue state has been manipulated
3179 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
3180 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
3181 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3182 END IF;
3183 RETURN NULL;
3184 END IF;
3185 IF "persist"."state" = 'voting' THEN
3186 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
3187 PERFORM "close_voting"("issue_id_p");
3188 "persist"."closed_voting" = TRUE;
3189 RETURN "persist";
3190 END IF;
3191 PERFORM "calculate_ranks"("issue_id_p");
3192 RETURN NULL;
3193 END IF;
3194 END IF;
3195 RAISE WARNING 'should not happen';
3196 RETURN NULL;
3197 END;
3198 $$;
3201 CREATE OR REPLACE FUNCTION "check_everything"()
3202 RETURNS VOID
3203 LANGUAGE 'plpgsql' VOLATILE AS $$
3204 DECLARE
3205 "area_id_v" "area"."id"%TYPE;
3206 "snapshot_id_v" "snapshot"."id"%TYPE;
3207 "issue_id_v" "issue"."id"%TYPE;
3208 "persist_v" "check_issue_persistence";
3209 BEGIN
3210 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
3211 DELETE FROM "expired_session";
3212 DELETE FROM "expired_token";
3213 DELETE FROM "expired_snapshot";
3214 PERFORM "check_activity"();
3215 PERFORM "calculate_member_counts"();
3216 FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP
3217 SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v";
3218 PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"
3219 WHERE "snapshot_id" = "snapshot_id_v";
3220 LOOP
3221 EXIT WHEN "issue_admission"("area_id_v") = FALSE;
3222 END LOOP;
3223 END LOOP;
3224 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
3225 "persist_v" := NULL;
3226 LOOP
3227 "persist_v" := "check_issue"("issue_id_v", "persist_v");
3228 EXIT WHEN "persist_v" ISNULL;
3229 END LOOP;
3230 END LOOP;
3231 RETURN;
3232 END;
3233 $$;
3235 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';
3238 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
3239 RETURNS VOID
3240 LANGUAGE 'plpgsql' VOLATILE AS $$
3241 BEGIN
3242 IF EXISTS (
3243 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
3244 ) THEN
3245 -- override protection triggers:
3246 INSERT INTO "temporary_transaction_data" ("key", "value")
3247 VALUES ('override_protection_triggers', TRUE::TEXT);
3248 -- clean data:
3249 DELETE FROM "delegating_voter"
3250 WHERE "issue_id" = "issue_id_p";
3251 DELETE FROM "direct_voter"
3252 WHERE "issue_id" = "issue_id_p";
3253 DELETE FROM "delegating_interest_snapshot"
3254 WHERE "issue_id" = "issue_id_p";
3255 DELETE FROM "direct_interest_snapshot"
3256 WHERE "issue_id" = "issue_id_p";
3257 DELETE FROM "non_voter"
3258 WHERE "issue_id" = "issue_id_p";
3259 DELETE FROM "delegation"
3260 WHERE "issue_id" = "issue_id_p";
3261 DELETE FROM "supporter"
3262 USING "initiative" -- NOTE: due to missing index on issue_id
3263 WHERE "initiative"."issue_id" = "issue_id_p"
3264 AND "supporter"."initiative_id" = "initiative_id";
3265 -- mark issue as cleaned:
3266 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
3267 -- finish overriding protection triggers (avoids garbage):
3268 DELETE FROM "temporary_transaction_data"
3269 WHERE "key" = 'override_protection_triggers';
3270 END IF;
3271 RETURN;
3272 END;
3273 $$;
3276 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
3277 RETURNS VOID
3278 LANGUAGE 'plpgsql' VOLATILE AS $$
3279 BEGIN
3280 UPDATE "member" SET
3281 "last_login" = NULL,
3282 "last_delegation_check" = NULL,
3283 "login" = NULL,
3284 "password" = NULL,
3285 "authority" = NULL,
3286 "authority_uid" = NULL,
3287 "authority_login" = NULL,
3288 "deleted" = coalesce("deleted", now()),
3289 "locked" = TRUE,
3290 "active" = FALSE,
3291 "notify_email" = NULL,
3292 "notify_email_unconfirmed" = NULL,
3293 "notify_email_secret" = NULL,
3294 "notify_email_secret_expiry" = NULL,
3295 "notify_email_lock_expiry" = NULL,
3296 "disable_notifications" = TRUE,
3297 "notification_counter" = DEFAULT,
3298 "notification_sample_size" = 0,
3299 "notification_dow" = NULL,
3300 "notification_hour" = NULL,
3301 "notification_sent" = NULL,
3302 "login_recovery_expiry" = NULL,
3303 "password_reset_secret" = NULL,
3304 "password_reset_secret_expiry" = NULL,
3305 "location" = NULL
3306 WHERE "id" = "member_id_p";
3307 -- "text_search_data" is updated by triggers
3308 DELETE FROM "member_settings" WHERE "member_id" = "member_id_p";
3309 DELETE FROM "member_profile" WHERE "member_id" = "member_id_p";
3310 DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";
3311 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
3312 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
3313 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
3314 DELETE FROM "session" WHERE "member_id" = "member_id_p";
3315 DELETE FROM "member_application" WHERE "member_id" = "member_id_p";
3316 DELETE FROM "token" WHERE "member_id" = "member_id_p";
3317 DELETE FROM "subscription" WHERE "member_id" = "member_id_p";
3318 DELETE FROM "ignored_area" WHERE "member_id" = "member_id_p";
3319 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
3320 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
3321 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
3322 DELETE FROM "direct_voter" USING "issue"
3323 WHERE "direct_voter"."issue_id" = "issue"."id"
3324 AND "issue"."closed" ISNULL
3325 AND "member_id" = "member_id_p";
3326 DELETE FROM "notification_initiative_sent" WHERE "member_id" = "member_id_p";
3327 RETURN;
3328 END;
3329 $$;
3332 CREATE OR REPLACE FUNCTION "delete_private_data"()
3333 RETURNS VOID
3334 LANGUAGE 'plpgsql' VOLATILE AS $$
3335 BEGIN
3336 DELETE FROM "temporary_transaction_data";
3337 DELETE FROM "temporary_suggestion_counts";
3338 DELETE FROM "member" WHERE "activated" ISNULL;
3339 UPDATE "member" SET
3340 "invite_code" = NULL,
3341 "invite_code_expiry" = NULL,
3342 "admin_comment" = NULL,
3343 "last_login" = NULL,
3344 "last_delegation_check" = NULL,
3345 "login" = NULL,
3346 "password" = NULL,
3347 "authority" = NULL,
3348 "authority_uid" = NULL,
3349 "authority_login" = NULL,
3350 "lang" = NULL,
3351 "notify_email" = NULL,
3352 "notify_email_unconfirmed" = NULL,
3353 "notify_email_secret" = NULL,
3354 "notify_email_secret_expiry" = NULL,
3355 "notify_email_lock_expiry" = NULL,
3356 "disable_notifications" = TRUE,
3357 "notification_counter" = DEFAULT,
3358 "notification_sample_size" = 0,
3359 "notification_dow" = NULL,
3360 "notification_hour" = NULL,
3361 "notification_sent" = NULL,
3362 "login_recovery_expiry" = NULL,
3363 "password_reset_secret" = NULL,
3364 "password_reset_secret_expiry" = NULL,
3365 "location" = NULL;
3366 -- "text_search_data" is updated by triggers
3367 DELETE FROM "member_settings";
3368 DELETE FROM "member_useterms";
3369 DELETE FROM "member_profile";
3370 DELETE FROM "rendered_member_statement";
3371 DELETE FROM "member_image";
3372 DELETE FROM "contact";
3373 DELETE FROM "ignored_member";
3374 DELETE FROM "session";
3375 DELETE FROM "system_application";
3376 DELETE FROM "system_application_redirect_uri";
3377 DELETE FROM "dynamic_application_scope";
3378 DELETE FROM "member_application";
3379 DELETE FROM "token";
3380 DELETE FROM "subscription";
3381 DELETE FROM "ignored_area";
3382 DELETE FROM "ignored_initiative";
3383 DELETE FROM "non_voter";
3384 DELETE FROM "direct_voter" USING "issue"
3385 WHERE "direct_voter"."issue_id" = "issue"."id"
3386 AND "issue"."closed" ISNULL;
3387 DELETE FROM "event_processed";
3388 DELETE FROM "notification_initiative_sent";
3389 DELETE FROM "newsletter";
3390 RETURN;
3391 END;
3392 $$;
3395 CREATE TEMPORARY TABLE "old_snapshot" AS
3396 SELECT "ordered".*, row_number() OVER () AS "snapshot_id"
3397 FROM (
3398 SELECT * FROM (
3399 SELECT
3400 "id" AS "issue_id",
3401 'end_of_admission'::"snapshot_event" AS "event",
3402 "accepted" AS "calculated"
3403 FROM "issue" WHERE "accepted" NOTNULL
3404 UNION ALL
3405 SELECT
3406 "id" AS "issue_id",
3407 'half_freeze'::"snapshot_event" AS "event",
3408 "half_frozen" AS "calculated"
3409 FROM "issue" WHERE "half_frozen" NOTNULL
3410 UNION ALL
3411 SELECT
3412 "id" AS "issue_id",
3413 'full_freeze'::"snapshot_event" AS "event",
3414 "fully_frozen" AS "calculated"
3415 FROM "issue" WHERE "fully_frozen" NOTNULL
3416 ) AS "unordered"
3417 ORDER BY "calculated", "issue_id", "event"
3418 ) AS "ordered";
3421 INSERT INTO "snapshot" ("id", "calculated", "population", "area_id", "issue_id")
3422 SELECT
3423 "old_snapshot"."snapshot_id" AS "id",
3424 "old_snapshot"."calculated",
3425 ( SELECT COALESCE(sum("weight"), 0)
3426 FROM "direct_population_snapshot" "dps"
3427 WHERE "dps"."issue_id" = "old_snapshot"."issue_id"
3428 AND "dps"."event" = "old_snapshot"."event"
3429 ) AS "population",
3430 "issue"."area_id" AS "area_id",
3431 "issue"."id" AS "issue_id"
3432 FROM "old_snapshot" JOIN "issue"
3433 ON "old_snapshot"."issue_id" = "issue"."id";
3436 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
3437 SELECT "id" AS "snapshot_id", "issue_id" FROM "snapshot";
3440 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
3441 SELECT
3442 "old_snapshot"."snapshot_id",
3443 "direct_population_snapshot"."member_id"
3444 FROM "old_snapshot" JOIN "direct_population_snapshot"
3445 ON "old_snapshot"."issue_id" = "direct_population_snapshot"."issue_id"
3446 AND "old_snapshot"."event" = "direct_population_snapshot"."event";
3448 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
3449 SELECT
3450 "old_snapshot"."snapshot_id",
3451 "delegating_population_snapshot"."member_id"
3452 FROM "old_snapshot" JOIN "delegating_population_snapshot"
3453 ON "old_snapshot"."issue_id" = "delegating_population_snapshot"."issue_id"
3454 AND "old_snapshot"."event" = "delegating_population_snapshot"."event";
3457 INSERT INTO "direct_interest_snapshot"
3458 ("snapshot_id", "issue_id", "member_id", "weight")
3459 SELECT
3460 "old_snapshot"."snapshot_id",
3461 "old_snapshot"."issue_id",
3462 "direct_interest_snapshot_old"."member_id",
3463 "direct_interest_snapshot_old"."weight"
3464 FROM "old_snapshot" JOIN "direct_interest_snapshot_old"
3465 ON "old_snapshot"."issue_id" = "direct_interest_snapshot_old"."issue_id"
3466 AND "old_snapshot"."event" = "direct_interest_snapshot_old"."event";
3468 INSERT INTO "delegating_interest_snapshot"
3469 ( "snapshot_id", "issue_id",
3470 "member_id", "weight", "scope", "delegate_member_ids" )
3471 SELECT
3472 "old_snapshot"."snapshot_id",
3473 "old_snapshot"."issue_id",
3474 "delegating_interest_snapshot_old"."member_id",
3475 "delegating_interest_snapshot_old"."weight",
3476 "delegating_interest_snapshot_old"."scope",
3477 "delegating_interest_snapshot_old"."delegate_member_ids"
3478 FROM "old_snapshot" JOIN "delegating_interest_snapshot_old"
3479 ON "old_snapshot"."issue_id" = "delegating_interest_snapshot_old"."issue_id"
3480 AND "old_snapshot"."event" = "delegating_interest_snapshot_old"."event";
3482 INSERT INTO "direct_supporter_snapshot"
3483 ( "snapshot_id", "issue_id",
3484 "initiative_id", "member_id", "draft_id", "informed", "satisfied" )
3485 SELECT
3486 "old_snapshot"."snapshot_id",
3487 "old_snapshot"."issue_id",
3488 "direct_supporter_snapshot_old"."initiative_id",
3489 "direct_supporter_snapshot_old"."member_id",
3490 "direct_supporter_snapshot_old"."draft_id",
3491 "direct_supporter_snapshot_old"."informed",
3492 "direct_supporter_snapshot_old"."satisfied"
3493 FROM "old_snapshot" JOIN "direct_supporter_snapshot_old"
3494 ON "old_snapshot"."issue_id" = "direct_supporter_snapshot_old"."issue_id"
3495 AND "old_snapshot"."event" = "direct_supporter_snapshot_old"."event";
3498 ALTER TABLE "issue" DISABLE TRIGGER USER; -- NOTE: required to modify table later
3500 UPDATE "issue" SET "latest_snapshot_id" = "snapshot"."id"
3501 FROM (
3502 SELECT DISTINCT ON ("issue_id") "issue_id", "id"
3503 FROM "snapshot" ORDER BY "issue_id", "id" DESC
3504 ) AS "snapshot"
3505 WHERE "snapshot"."issue_id" = "issue"."id";
3507 UPDATE "issue" SET "admission_snapshot_id" = "old_snapshot"."snapshot_id"
3508 FROM "old_snapshot"
3509 WHERE "old_snapshot"."issue_id" = "issue"."id"
3510 AND "old_snapshot"."event" = 'end_of_admission';
3512 UPDATE "issue" SET "half_freeze_snapshot_id" = "old_snapshot"."snapshot_id"
3513 FROM "old_snapshot"
3514 WHERE "old_snapshot"."issue_id" = "issue"."id"
3515 AND "old_snapshot"."event" = 'half_freeze';
3517 UPDATE "issue" SET "full_freeze_snapshot_id" = "old_snapshot"."snapshot_id"
3518 FROM "old_snapshot"
3519 WHERE "old_snapshot"."issue_id" = "issue"."id"
3520 AND "old_snapshot"."event" = 'full_freeze';
3522 ALTER TABLE "issue" ENABLE TRIGGER USER;
3525 DROP TABLE "old_snapshot";
3527 DROP TABLE "direct_supporter_snapshot_old";
3528 DROP TABLE "delegating_interest_snapshot_old";
3529 DROP TABLE "direct_interest_snapshot_old";
3530 DROP TABLE "delegating_population_snapshot";
3531 DROP TABLE "direct_population_snapshot";
3534 DROP VIEW "open_issue";
3537 ALTER TABLE "issue" DROP COLUMN "latest_snapshot_event";
3540 CREATE VIEW "open_issue" AS
3541 SELECT * FROM "issue" WHERE "closed" ISNULL;
3543 COMMENT ON VIEW "open_issue" IS 'All open issues';
3546 -- NOTE: create "issue_for_admission" view after altering table "issue"
3547 CREATE VIEW "issue_for_admission" AS
3548 SELECT DISTINCT ON ("issue"."area_id")
3549 "issue".*,
3550 max("initiative"."supporter_count") AS "max_supporter_count"
3551 FROM "issue"
3552 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
3553 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
3554 JOIN "area" ON "issue"."area_id" = "area"."id"
3555 WHERE "issue"."state" = 'admission'::"issue_state"
3556 AND now() >= "issue"."created" + "issue"."min_admission_time"
3557 AND "initiative"."supporter_count" >= "policy"."issue_quorum"
3558 AND "initiative"."supporter_count" * "policy"."issue_quorum_den" >=
3559 "issue"."population" * "policy"."issue_quorum_num"
3560 AND "initiative"."supporter_count" >= "area"."issue_quorum"
3561 AND "initiative"."revoked" ISNULL
3562 GROUP BY "issue"."id"
3563 ORDER BY "issue"."area_id", "max_supporter_count" DESC, "issue"."id";
3565 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';
3568 DROP TYPE "snapshot_event";
3571 ALTER TABLE "issue" ADD CONSTRAINT "snapshot_required" CHECK (
3572 ("half_frozen" ISNULL OR "half_freeze_snapshot_id" NOTNULL) AND
3573 ("fully_frozen" ISNULL OR "full_freeze_snapshot_id" NOTNULL) );
3576 COMMIT;