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