liquid_feedback_core
view update/core-update.v3.2.2-v4.0.0.sql @ 567:797282760db4
Removed unused function "calculate_area_quorum" (done by function "issue_admission")
| author | jbe |
|---|---|
| date | Thu Sep 28 20:12:23 2017 +0200 (2017-09-28) |
| parents | 74f98b2196ef |
| children | b1326d149247 |
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 DROP VIEW "remaining_harmonic_initiative_weight_summands";
2535 DROP VIEW "remaining_harmonic_supporter_weight";
2538 CREATE VIEW "remaining_harmonic_supporter_weight" AS
2539 SELECT
2540 "direct_interest_snapshot"."snapshot_id",
2541 "direct_interest_snapshot"."issue_id",
2542 "direct_interest_snapshot"."member_id",
2543 "direct_interest_snapshot"."weight" AS "weight_num",
2544 count("initiative"."id") AS "weight_den"
2545 FROM "issue"
2546 JOIN "direct_interest_snapshot"
2547 ON "issue"."latest_snapshot_id" = "direct_interest_snapshot"."snapshot_id"
2548 AND "issue"."id" = "direct_interest_snapshot"."issue_id"
2549 JOIN "initiative"
2550 ON "issue"."id" = "initiative"."issue_id"
2551 AND "initiative"."harmonic_weight" ISNULL
2552 JOIN "direct_supporter_snapshot"
2553 ON "issue"."latest_snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
2554 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
2555 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
2556 AND (
2557 "direct_supporter_snapshot"."satisfied" = TRUE OR
2558 coalesce("initiative"."admitted", FALSE) = FALSE
2559 )
2560 GROUP BY
2561 "direct_interest_snapshot"."snapshot_id",
2562 "direct_interest_snapshot"."issue_id",
2563 "direct_interest_snapshot"."member_id",
2564 "direct_interest_snapshot"."weight";
2567 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
2568 SELECT
2569 "initiative"."issue_id",
2570 "initiative"."id" AS "initiative_id",
2571 "initiative"."admitted",
2572 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
2573 "remaining_harmonic_supporter_weight"."weight_den"
2574 FROM "remaining_harmonic_supporter_weight"
2575 JOIN "initiative"
2576 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
2577 AND "initiative"."harmonic_weight" ISNULL
2578 JOIN "direct_supporter_snapshot"
2579 ON "remaining_harmonic_supporter_weight"."snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
2580 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
2581 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
2582 AND (
2583 "direct_supporter_snapshot"."satisfied" = TRUE OR
2584 coalesce("initiative"."admitted", FALSE) = FALSE
2585 )
2586 GROUP BY
2587 "initiative"."issue_id",
2588 "initiative"."id",
2589 "initiative"."admitted",
2590 "remaining_harmonic_supporter_weight"."weight_den";
2593 DROP FUNCTION "create_population_snapshot"
2594 ( "issue_id_p" "issue"."id"%TYPE );
2597 DROP FUNCTION "weight_of_added_delegations_for_population_snapshot"
2598 ( "issue_id_p" "issue"."id"%TYPE,
2599 "member_id_p" "member"."id"%TYPE,
2600 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE );
2603 DROP FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2604 ( "issue_id_p" "issue"."id"%TYPE,
2605 "member_id_p" "member"."id"%TYPE,
2606 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE );
2609 CREATE FUNCTION "weight_of_added_delegations_for_snapshot"
2610 ( "snapshot_id_p" "snapshot"."id"%TYPE,
2611 "issue_id_p" "issue"."id"%TYPE,
2612 "member_id_p" "member"."id"%TYPE,
2613 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2614 RETURNS "direct_interest_snapshot"."weight"%TYPE
2615 LANGUAGE 'plpgsql' VOLATILE AS $$
2616 DECLARE
2617 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2618 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2619 "weight_v" INT4;
2620 "sub_weight_v" INT4;
2621 BEGIN
2622 PERFORM "require_transaction_isolation"();
2623 "weight_v" := 0;
2624 FOR "issue_delegation_row" IN
2625 SELECT * FROM "issue_delegation"
2626 WHERE "trustee_id" = "member_id_p"
2627 AND "issue_id" = "issue_id_p"
2628 LOOP
2629 IF NOT EXISTS (
2630 SELECT NULL FROM "direct_interest_snapshot"
2631 WHERE "snapshot_id" = "snapshot_id_p"
2632 AND "issue_id" = "issue_id_p"
2633 AND "member_id" = "issue_delegation_row"."truster_id"
2634 ) AND NOT EXISTS (
2635 SELECT NULL FROM "delegating_interest_snapshot"
2636 WHERE "snapshot_id" = "snapshot_id_p"
2637 AND "issue_id" = "issue_id_p"
2638 AND "member_id" = "issue_delegation_row"."truster_id"
2639 ) THEN
2640 "delegate_member_ids_v" :=
2641 "member_id_p" || "delegate_member_ids_p";
2642 INSERT INTO "delegating_interest_snapshot" (
2643 "snapshot_id",
2644 "issue_id",
2645 "member_id",
2646 "scope",
2647 "delegate_member_ids"
2648 ) VALUES (
2649 "snapshot_id_p",
2650 "issue_id_p",
2651 "issue_delegation_row"."truster_id",
2652 "issue_delegation_row"."scope",
2653 "delegate_member_ids_v"
2654 );
2655 "sub_weight_v" := 1 +
2656 "weight_of_added_delegations_for_snapshot"(
2657 "snapshot_id_p",
2658 "issue_id_p",
2659 "issue_delegation_row"."truster_id",
2660 "delegate_member_ids_v"
2661 );
2662 UPDATE "delegating_interest_snapshot"
2663 SET "weight" = "sub_weight_v"
2664 WHERE "snapshot_id" = "snapshot_id_p"
2665 AND "issue_id" = "issue_id_p"
2666 AND "member_id" = "issue_delegation_row"."truster_id";
2667 "weight_v" := "weight_v" + "sub_weight_v";
2668 END IF;
2669 END LOOP;
2670 RETURN "weight_v";
2671 END;
2672 $$;
2674 COMMENT ON FUNCTION "weight_of_added_delegations_for_snapshot"
2675 ( "snapshot"."id"%TYPE,
2676 "issue"."id"%TYPE,
2677 "member"."id"%TYPE,
2678 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2679 IS 'Helper function for "fill_snapshot" function';
2682 DROP FUNCTION "create_interest_snapshot"
2683 ( "issue_id_p" "issue"."id"%TYPE );
2686 DROP FUNCTION "create_snapshot"
2687 ( "issue_id_p" "issue"."id"%TYPE );
2690 CREATE FUNCTION "take_snapshot"
2691 ( "issue_id_p" "issue"."id"%TYPE,
2692 "area_id_p" "area"."id"%TYPE = NULL )
2693 RETURNS "snapshot"."id"%TYPE
2694 LANGUAGE 'plpgsql' VOLATILE AS $$
2695 DECLARE
2696 "area_id_v" "area"."id"%TYPE;
2697 "unit_id_v" "unit"."id"%TYPE;
2698 "snapshot_id_v" "snapshot"."id"%TYPE;
2699 "issue_id_v" "issue"."id"%TYPE;
2700 "member_id_v" "member"."id"%TYPE;
2701 BEGIN
2702 IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN
2703 RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL';
2704 END IF;
2705 PERFORM "require_transaction_isolation"();
2706 IF "issue_id_p" ISNULL THEN
2707 "area_id_v" := "area_id_p";
2708 ELSE
2709 SELECT "area_id" INTO "area_id_v"
2710 FROM "issue" WHERE "id" = "issue_id_p";
2711 END IF;
2712 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
2713 INSERT INTO "snapshot" ("area_id", "issue_id")
2714 VALUES ("area_id_v", "issue_id_p")
2715 RETURNING "id" INTO "snapshot_id_v";
2716 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
2717 SELECT "snapshot_id_v", "member_id"
2718 FROM "unit_member" WHERE "unit_id" = "unit_id_v";
2719 UPDATE "snapshot" SET
2720 "population" = (
2721 SELECT count(1) FROM "snapshot_population"
2722 WHERE "snapshot_id" = "snapshot_id_v"
2723 ) WHERE "id" = "snapshot_id_v";
2724 FOR "issue_id_v" IN
2725 SELECT "id" FROM "issue"
2726 WHERE CASE WHEN "issue_id_p" ISNULL THEN
2727 "area_id" = "area_id_p" AND
2728 "state" = 'admission'
2729 ELSE
2730 "id" = "issue_id_p"
2731 END
2732 LOOP
2733 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
2734 VALUES ("snapshot_id_v", "issue_id_v");
2735 INSERT INTO "direct_interest_snapshot"
2736 ("snapshot_id", "issue_id", "member_id")
2737 SELECT
2738 "snapshot_id_v" AS "snapshot_id",
2739 "issue_id_v" AS "issue_id",
2740 "member"."id" AS "member_id"
2741 FROM "issue"
2742 JOIN "area" ON "issue"."area_id" = "area"."id"
2743 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2744 JOIN "member" ON "interest"."member_id" = "member"."id"
2745 JOIN "privilege"
2746 ON "privilege"."unit_id" = "area"."unit_id"
2747 AND "privilege"."member_id" = "member"."id"
2748 WHERE "issue"."id" = "issue_id_v"
2749 AND "member"."active" AND "privilege"."voting_right";
2750 FOR "member_id_v" IN
2751 SELECT "member_id" FROM "direct_interest_snapshot"
2752 WHERE "snapshot_id" = "snapshot_id_v"
2753 AND "issue_id" = "issue_id_v"
2754 LOOP
2755 UPDATE "direct_interest_snapshot" SET
2756 "weight" = 1 +
2757 "weight_of_added_delegations_for_snapshot"(
2758 "snapshot_id_v",
2759 "issue_id_v",
2760 "member_id_v",
2761 '{}'
2762 )
2763 WHERE "snapshot_id" = "snapshot_id_v"
2764 AND "issue_id" = "issue_id_v"
2765 AND "member_id" = "member_id_v";
2766 END LOOP;
2767 INSERT INTO "direct_supporter_snapshot"
2768 ( "snapshot_id", "issue_id", "initiative_id", "member_id",
2769 "draft_id", "informed", "satisfied" )
2770 SELECT
2771 "snapshot_id_v" AS "snapshot_id",
2772 "issue_id_v" AS "issue_id",
2773 "initiative"."id" AS "initiative_id",
2774 "supporter"."member_id" AS "member_id",
2775 "supporter"."draft_id" AS "draft_id",
2776 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2777 NOT EXISTS (
2778 SELECT NULL FROM "critical_opinion"
2779 WHERE "initiative_id" = "initiative"."id"
2780 AND "member_id" = "supporter"."member_id"
2781 ) AS "satisfied"
2782 FROM "initiative"
2783 JOIN "supporter"
2784 ON "supporter"."initiative_id" = "initiative"."id"
2785 JOIN "current_draft"
2786 ON "initiative"."id" = "current_draft"."initiative_id"
2787 JOIN "direct_interest_snapshot"
2788 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"
2789 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"
2790 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
2791 WHERE "initiative"."issue_id" = "issue_id_v";
2792 DELETE FROM "temporary_suggestion_counts";
2793 INSERT INTO "temporary_suggestion_counts"
2794 ( "id",
2795 "minus2_unfulfilled_count", "minus2_fulfilled_count",
2796 "minus1_unfulfilled_count", "minus1_fulfilled_count",
2797 "plus1_unfulfilled_count", "plus1_fulfilled_count",
2798 "plus2_unfulfilled_count", "plus2_fulfilled_count" )
2799 SELECT
2800 "suggestion"."id",
2801 ( SELECT coalesce(sum("di"."weight"), 0)
2802 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2803 ON "di"."snapshot_id" = "snapshot_id_v"
2804 AND "di"."issue_id" = "issue_id_v"
2805 AND "di"."member_id" = "opinion"."member_id"
2806 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2807 AND "opinion"."degree" = -2
2808 AND "opinion"."fulfilled" = FALSE
2809 ) AS "minus2_unfulfilled_count",
2810 ( SELECT coalesce(sum("di"."weight"), 0)
2811 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2812 ON "di"."snapshot_id" = "snapshot_id_v"
2813 AND "di"."issue_id" = "issue_id_v"
2814 AND "di"."member_id" = "opinion"."member_id"
2815 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2816 AND "opinion"."degree" = -2
2817 AND "opinion"."fulfilled" = TRUE
2818 ) AS "minus2_fulfilled_count",
2819 ( SELECT coalesce(sum("di"."weight"), 0)
2820 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2821 ON "di"."snapshot_id" = "snapshot_id_v"
2822 AND "di"."issue_id" = "issue_id_v"
2823 AND "di"."member_id" = "opinion"."member_id"
2824 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2825 AND "opinion"."degree" = -1
2826 AND "opinion"."fulfilled" = FALSE
2827 ) AS "minus1_unfulfilled_count",
2828 ( SELECT coalesce(sum("di"."weight"), 0)
2829 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2830 ON "di"."snapshot_id" = "snapshot_id_v"
2831 AND "di"."issue_id" = "issue_id_v"
2832 AND "di"."member_id" = "opinion"."member_id"
2833 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2834 AND "opinion"."degree" = -1
2835 AND "opinion"."fulfilled" = TRUE
2836 ) AS "minus1_fulfilled_count",
2837 ( SELECT coalesce(sum("di"."weight"), 0)
2838 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2839 ON "di"."snapshot_id" = "snapshot_id_v"
2840 AND "di"."issue_id" = "issue_id_v"
2841 AND "di"."member_id" = "opinion"."member_id"
2842 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2843 AND "opinion"."degree" = 1
2844 AND "opinion"."fulfilled" = FALSE
2845 ) AS "plus1_unfulfilled_count",
2846 ( SELECT coalesce(sum("di"."weight"), 0)
2847 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2848 ON "di"."snapshot_id" = "snapshot_id_v"
2849 AND "di"."issue_id" = "issue_id_v"
2850 AND "di"."member_id" = "opinion"."member_id"
2851 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2852 AND "opinion"."degree" = 1
2853 AND "opinion"."fulfilled" = TRUE
2854 ) AS "plus1_fulfilled_count",
2855 ( SELECT coalesce(sum("di"."weight"), 0)
2856 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2857 ON "di"."snapshot_id" = "snapshot_id_v"
2858 AND "di"."issue_id" = "issue_id_v"
2859 AND "di"."member_id" = "opinion"."member_id"
2860 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2861 AND "opinion"."degree" = 2
2862 AND "opinion"."fulfilled" = FALSE
2863 ) AS "plus2_unfulfilled_count",
2864 ( SELECT coalesce(sum("di"."weight"), 0)
2865 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2866 ON "di"."snapshot_id" = "snapshot_id_v"
2867 AND "di"."issue_id" = "issue_id_v"
2868 AND "di"."member_id" = "opinion"."member_id"
2869 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2870 AND "opinion"."degree" = 2
2871 AND "opinion"."fulfilled" = TRUE
2872 ) AS "plus2_fulfilled_count"
2873 FROM "suggestion" JOIN "initiative"
2874 ON "suggestion"."initiative_id" = "initiative"."id"
2875 WHERE "initiative"."issue_id" = "issue_id_v";
2876 END LOOP;
2877 RETURN "snapshot_id_v";
2878 END;
2879 $$;
2881 COMMENT ON FUNCTION "take_snapshot"
2882 ( "issue"."id"%TYPE,
2883 "area"."id"%TYPE )
2884 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.';
2887 DROP FUNCTION "set_snapshot_event"
2888 ( "issue_id_p" "issue"."id"%TYPE,
2889 "event_p" "snapshot_event" );
2892 CREATE FUNCTION "finish_snapshot"
2893 ( "issue_id_p" "issue"."id"%TYPE )
2894 RETURNS VOID
2895 LANGUAGE 'plpgsql' VOLATILE AS $$
2896 DECLARE
2897 "snapshot_id_v" "snapshot"."id"%TYPE;
2898 BEGIN
2899 -- NOTE: function does not require snapshot isolation but we don't call
2900 -- "dont_require_snapshot_isolation" here because this function is
2901 -- also invoked by "check_issue"
2902 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
2903 SELECT "id" INTO "snapshot_id_v" FROM "snapshot"
2904 ORDER BY "id" DESC LIMIT 1;
2905 UPDATE "issue" SET
2906 "calculated" = "snapshot"."calculated",
2907 "latest_snapshot_id" = "snapshot_id_v",
2908 "population" = "snapshot"."population"
2909 FROM "snapshot"
2910 WHERE "issue"."id" = "issue_id_p"
2911 AND "snapshot"."id" = "snapshot_id_v";
2912 UPDATE "initiative" SET
2913 "supporter_count" = (
2914 SELECT coalesce(sum("di"."weight"), 0)
2915 FROM "direct_interest_snapshot" AS "di"
2916 JOIN "direct_supporter_snapshot" AS "ds"
2917 ON "di"."member_id" = "ds"."member_id"
2918 WHERE "di"."snapshot_id" = "snapshot_id_v"
2919 AND "di"."issue_id" = "issue_id_p"
2920 AND "ds"."snapshot_id" = "snapshot_id_v"
2921 AND "ds"."initiative_id" = "initiative"."id"
2922 ),
2923 "informed_supporter_count" = (
2924 SELECT coalesce(sum("di"."weight"), 0)
2925 FROM "direct_interest_snapshot" AS "di"
2926 JOIN "direct_supporter_snapshot" AS "ds"
2927 ON "di"."member_id" = "ds"."member_id"
2928 WHERE "di"."snapshot_id" = "snapshot_id_v"
2929 AND "di"."issue_id" = "issue_id_p"
2930 AND "ds"."snapshot_id" = "snapshot_id_v"
2931 AND "ds"."initiative_id" = "initiative"."id"
2932 AND "ds"."informed"
2933 ),
2934 "satisfied_supporter_count" = (
2935 SELECT coalesce(sum("di"."weight"), 0)
2936 FROM "direct_interest_snapshot" AS "di"
2937 JOIN "direct_supporter_snapshot" AS "ds"
2938 ON "di"."member_id" = "ds"."member_id"
2939 WHERE "di"."snapshot_id" = "snapshot_id_v"
2940 AND "di"."issue_id" = "issue_id_p"
2941 AND "ds"."snapshot_id" = "snapshot_id_v"
2942 AND "ds"."initiative_id" = "initiative"."id"
2943 AND "ds"."satisfied"
2944 ),
2945 "satisfied_informed_supporter_count" = (
2946 SELECT coalesce(sum("di"."weight"), 0)
2947 FROM "direct_interest_snapshot" AS "di"
2948 JOIN "direct_supporter_snapshot" AS "ds"
2949 ON "di"."member_id" = "ds"."member_id"
2950 WHERE "di"."snapshot_id" = "snapshot_id_v"
2951 AND "di"."issue_id" = "issue_id_p"
2952 AND "ds"."snapshot_id" = "snapshot_id_v"
2953 AND "ds"."initiative_id" = "initiative"."id"
2954 AND "ds"."informed"
2955 AND "ds"."satisfied"
2956 )
2957 WHERE "issue_id" = "issue_id_p";
2958 UPDATE "suggestion" SET
2959 "minus2_unfulfilled_count" = "temp"."minus2_unfulfilled_count",
2960 "minus2_fulfilled_count" = "temp"."minus2_fulfilled_count",
2961 "minus1_unfulfilled_count" = "temp"."minus1_unfulfilled_count",
2962 "minus1_fulfilled_count" = "temp"."minus1_fulfilled_count",
2963 "plus1_unfulfilled_count" = "temp"."plus1_unfulfilled_count",
2964 "plus1_fulfilled_count" = "temp"."plus1_fulfilled_count",
2965 "plus2_unfulfilled_count" = "temp"."plus2_unfulfilled_count",
2966 "plus2_fulfilled_count" = "temp"."plus2_fulfilled_count"
2967 FROM "temporary_suggestion_counts" AS "temp", "initiative"
2968 WHERE "temp"."id" = "suggestion"."id"
2969 AND "initiative"."issue_id" = "issue_id_p"
2970 AND "suggestion"."initiative_id" = "initiative"."id";
2971 DELETE FROM "temporary_suggestion_counts";
2972 RETURN;
2973 END;
2974 $$;
2976 COMMENT ON FUNCTION "finish_snapshot"
2977 ( "issue"."id"%TYPE )
2978 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)';
2981 CREATE FUNCTION "issue_admission"
2982 ( "area_id_p" "area"."id"%TYPE )
2983 RETURNS BOOLEAN
2984 LANGUAGE 'plpgsql' VOLATILE AS $$
2985 DECLARE
2986 "issue_id_v" "issue"."id"%TYPE;
2987 BEGIN
2988 PERFORM "dont_require_transaction_isolation"();
2989 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
2990 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
2991 FROM "area_quorum" AS "view"
2992 WHERE "area"."id" = "view"."area_id"
2993 AND "area"."id" = "area_id_p";
2994 SELECT "id" INTO "issue_id_v" FROM "issue_for_admission"
2995 WHERE "area_id" = "area_id_p";
2996 IF "issue_id_v" ISNULL THEN RETURN FALSE; END IF;
2997 UPDATE "issue" SET
2998 "admission_snapshot_id" = "latest_snapshot_id",
2999 "state" = 'discussion',
3000 "accepted" = now(),
3001 "phase_finished" = NULL
3002 WHERE "id" = "issue_id_v";
3003 RETURN TRUE;
3004 END;
3005 $$;
3007 COMMENT ON FUNCTION "issue_admission"
3008 ( "area"."id"%TYPE )
3009 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';
3012 CREATE OR REPLACE FUNCTION "check_issue"
3013 ( "issue_id_p" "issue"."id"%TYPE,
3014 "persist" "check_issue_persistence" )
3015 RETURNS "check_issue_persistence"
3016 LANGUAGE 'plpgsql' VOLATILE AS $$
3017 DECLARE
3018 "issue_row" "issue"%ROWTYPE;
3019 "last_calculated_v" "snapshot"."calculated"%TYPE;
3020 "policy_row" "policy"%ROWTYPE;
3021 "initiative_row" "initiative"%ROWTYPE;
3022 "state_v" "issue_state";
3023 BEGIN
3024 PERFORM "require_transaction_isolation"();
3025 IF "persist" ISNULL THEN
3026 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
3027 FOR UPDATE;
3028 SELECT "calculated" INTO "last_calculated_v"
3029 FROM "snapshot" JOIN "snapshot_issue"
3030 ON "snapshot"."id" = "snapshot_issue"."snapshot_id"
3031 WHERE "snapshot_issue"."issue_id" = "issue_id_p";
3032 IF "issue_row"."closed" NOTNULL THEN
3033 RETURN NULL;
3034 END IF;
3035 "persist"."state" := "issue_row"."state";
3036 IF
3037 ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=
3038 "issue_row"."created" + "issue_row"."max_admission_time" ) OR
3039 ( "issue_row"."state" = 'discussion' AND now() >=
3040 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
3041 ( "issue_row"."state" = 'verification' AND now() >=
3042 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
3043 ( "issue_row"."state" = 'voting' AND now() >=
3044 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
3045 THEN
3046 "persist"."phase_finished" := TRUE;
3047 ELSE
3048 "persist"."phase_finished" := FALSE;
3049 END IF;
3050 IF
3051 NOT EXISTS (
3052 -- all initiatives are revoked
3053 SELECT NULL FROM "initiative"
3054 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3055 ) AND (
3056 -- and issue has not been accepted yet
3057 "persist"."state" = 'admission' OR
3058 -- or verification time has elapsed
3059 ( "persist"."state" = 'verification' AND
3060 "persist"."phase_finished" ) OR
3061 -- or no initiatives have been revoked lately
3062 NOT EXISTS (
3063 SELECT NULL FROM "initiative"
3064 WHERE "issue_id" = "issue_id_p"
3065 AND now() < "revoked" + "issue_row"."verification_time"
3066 )
3067 )
3068 THEN
3069 "persist"."issue_revoked" := TRUE;
3070 ELSE
3071 "persist"."issue_revoked" := FALSE;
3072 END IF;
3073 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
3074 UPDATE "issue" SET "phase_finished" = now()
3075 WHERE "id" = "issue_row"."id";
3076 RETURN "persist";
3077 ELSIF
3078 "persist"."state" IN ('admission', 'discussion', 'verification')
3079 THEN
3080 RETURN "persist";
3081 ELSE
3082 RETURN NULL;
3083 END IF;
3084 END IF;
3085 IF
3086 "persist"."state" IN ('admission', 'discussion', 'verification') AND
3087 coalesce("persist"."snapshot_created", FALSE) = FALSE
3088 THEN
3089 IF "persist"."state" != 'admission' THEN
3090 PERFORM "take_snapshot"("issue_id_p");
3091 PERFORM "finish_snapshot"("issue_id_p");
3092 END IF;
3093 "persist"."snapshot_created" = TRUE;
3094 IF "persist"."phase_finished" THEN
3095 IF "persist"."state" = 'admission' THEN
3096 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id"
3097 WHERE "id" = "issue_id_p";
3098 ELSIF "persist"."state" = 'discussion' THEN
3099 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id"
3100 WHERE "id" = "issue_id_p";
3101 ELSIF "persist"."state" = 'verification' THEN
3102 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id"
3103 WHERE "id" = "issue_id_p";
3104 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3105 SELECT * INTO "policy_row" FROM "policy"
3106 WHERE "id" = "issue_row"."policy_id";
3107 FOR "initiative_row" IN
3108 SELECT * FROM "initiative"
3109 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3110 FOR UPDATE
3111 LOOP
3112 IF
3113 "initiative_row"."polling" OR (
3114 "initiative_row"."satisfied_supporter_count" >=
3115 "policy_row"."initiative_quorum" AND
3116 "initiative_row"."satisfied_supporter_count" *
3117 "policy_row"."initiative_quorum_den" >=
3118 "issue_row"."population" * "policy_row"."initiative_quorum_num"
3119 )
3120 THEN
3121 UPDATE "initiative" SET "admitted" = TRUE
3122 WHERE "id" = "initiative_row"."id";
3123 ELSE
3124 UPDATE "initiative" SET "admitted" = FALSE
3125 WHERE "id" = "initiative_row"."id";
3126 END IF;
3127 END LOOP;
3128 END IF;
3129 END IF;
3130 RETURN "persist";
3131 END IF;
3132 IF
3133 "persist"."state" IN ('admission', 'discussion', 'verification') AND
3134 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
3135 THEN
3136 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
3137 "persist"."harmonic_weights_set" = TRUE;
3138 IF
3139 "persist"."phase_finished" OR
3140 "persist"."issue_revoked" OR
3141 "persist"."state" = 'admission'
3142 THEN
3143 RETURN "persist";
3144 ELSE
3145 RETURN NULL;
3146 END IF;
3147 END IF;
3148 IF "persist"."issue_revoked" THEN
3149 IF "persist"."state" = 'admission' THEN
3150 "state_v" := 'canceled_revoked_before_accepted';
3151 ELSIF "persist"."state" = 'discussion' THEN
3152 "state_v" := 'canceled_after_revocation_during_discussion';
3153 ELSIF "persist"."state" = 'verification' THEN
3154 "state_v" := 'canceled_after_revocation_during_verification';
3155 END IF;
3156 UPDATE "issue" SET
3157 "state" = "state_v",
3158 "closed" = "phase_finished",
3159 "phase_finished" = NULL
3160 WHERE "id" = "issue_id_p";
3161 RETURN NULL;
3162 END IF;
3163 IF "persist"."state" = 'admission' THEN
3164 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
3165 FOR UPDATE;
3166 IF "issue_row"."phase_finished" NOTNULL THEN
3167 UPDATE "issue" SET
3168 "state" = 'canceled_issue_not_accepted',
3169 "closed" = "phase_finished",
3170 "phase_finished" = NULL
3171 WHERE "id" = "issue_id_p";
3172 END IF;
3173 RETURN NULL;
3174 END IF;
3175 IF "persist"."phase_finished" THEN
3176 IF "persist"."state" = 'discussion' THEN
3177 UPDATE "issue" SET
3178 "state" = 'verification',
3179 "half_frozen" = "phase_finished",
3180 "phase_finished" = NULL
3181 WHERE "id" = "issue_id_p";
3182 RETURN NULL;
3183 END IF;
3184 IF "persist"."state" = 'verification' THEN
3185 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
3186 FOR UPDATE;
3187 SELECT * INTO "policy_row" FROM "policy"
3188 WHERE "id" = "issue_row"."policy_id";
3189 IF EXISTS (
3190 SELECT NULL FROM "initiative"
3191 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
3192 ) THEN
3193 UPDATE "issue" SET
3194 "state" = 'voting',
3195 "fully_frozen" = "phase_finished",
3196 "phase_finished" = NULL
3197 WHERE "id" = "issue_id_p";
3198 ELSE
3199 UPDATE "issue" SET
3200 "state" = 'canceled_no_initiative_admitted',
3201 "fully_frozen" = "phase_finished",
3202 "closed" = "phase_finished",
3203 "phase_finished" = NULL
3204 WHERE "id" = "issue_id_p";
3205 -- NOTE: The following DELETE statements have effect only when
3206 -- issue state has been manipulated
3207 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
3208 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
3209 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3210 END IF;
3211 RETURN NULL;
3212 END IF;
3213 IF "persist"."state" = 'voting' THEN
3214 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
3215 PERFORM "close_voting"("issue_id_p");
3216 "persist"."closed_voting" = TRUE;
3217 RETURN "persist";
3218 END IF;
3219 PERFORM "calculate_ranks"("issue_id_p");
3220 RETURN NULL;
3221 END IF;
3222 END IF;
3223 RAISE WARNING 'should not happen';
3224 RETURN NULL;
3225 END;
3226 $$;
3229 CREATE OR REPLACE FUNCTION "check_everything"()
3230 RETURNS VOID
3231 LANGUAGE 'plpgsql' VOLATILE AS $$
3232 DECLARE
3233 "area_id_v" "area"."id"%TYPE;
3234 "snapshot_id_v" "snapshot"."id"%TYPE;
3235 "issue_id_v" "issue"."id"%TYPE;
3236 "persist_v" "check_issue_persistence";
3237 BEGIN
3238 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
3239 DELETE FROM "expired_session";
3240 DELETE FROM "expired_token";
3241 DELETE FROM "expired_snapshot";
3242 PERFORM "check_activity"();
3243 PERFORM "calculate_member_counts"();
3244 FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP
3245 SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v";
3246 PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"
3247 WHERE "snapshot_id" = "snapshot_id_v";
3248 LOOP
3249 EXIT WHEN "issue_admission"("area_id_v") = FALSE;
3250 END LOOP;
3251 END LOOP;
3252 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
3253 "persist_v" := NULL;
3254 LOOP
3255 "persist_v" := "check_issue"("issue_id_v", "persist_v");
3256 EXIT WHEN "persist_v" ISNULL;
3257 END LOOP;
3258 END LOOP;
3259 RETURN;
3260 END;
3261 $$;
3263 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';
3266 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
3267 RETURNS VOID
3268 LANGUAGE 'plpgsql' VOLATILE AS $$
3269 BEGIN
3270 IF EXISTS (
3271 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
3272 ) THEN
3273 -- override protection triggers:
3274 INSERT INTO "temporary_transaction_data" ("key", "value")
3275 VALUES ('override_protection_triggers', TRUE::TEXT);
3276 -- clean data:
3277 DELETE FROM "delegating_voter"
3278 WHERE "issue_id" = "issue_id_p";
3279 DELETE FROM "direct_voter"
3280 WHERE "issue_id" = "issue_id_p";
3281 DELETE FROM "delegating_interest_snapshot"
3282 WHERE "issue_id" = "issue_id_p";
3283 DELETE FROM "direct_interest_snapshot"
3284 WHERE "issue_id" = "issue_id_p";
3285 DELETE FROM "non_voter"
3286 WHERE "issue_id" = "issue_id_p";
3287 DELETE FROM "delegation"
3288 WHERE "issue_id" = "issue_id_p";
3289 DELETE FROM "supporter"
3290 USING "initiative" -- NOTE: due to missing index on issue_id
3291 WHERE "initiative"."issue_id" = "issue_id_p"
3292 AND "supporter"."initiative_id" = "initiative_id";
3293 -- mark issue as cleaned:
3294 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
3295 -- finish overriding protection triggers (avoids garbage):
3296 DELETE FROM "temporary_transaction_data"
3297 WHERE "key" = 'override_protection_triggers';
3298 END IF;
3299 RETURN;
3300 END;
3301 $$;
3304 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
3305 RETURNS VOID
3306 LANGUAGE 'plpgsql' VOLATILE AS $$
3307 BEGIN
3308 UPDATE "member" SET
3309 "last_login" = NULL,
3310 "last_delegation_check" = NULL,
3311 "login" = NULL,
3312 "password" = NULL,
3313 "authority" = NULL,
3314 "authority_uid" = NULL,
3315 "authority_login" = NULL,
3316 "deleted" = coalesce("deleted", now()),
3317 "locked" = TRUE,
3318 "active" = FALSE,
3319 "notify_email" = NULL,
3320 "notify_email_unconfirmed" = NULL,
3321 "notify_email_secret" = NULL,
3322 "notify_email_secret_expiry" = NULL,
3323 "notify_email_lock_expiry" = NULL,
3324 "disable_notifications" = TRUE,
3325 "notification_counter" = DEFAULT,
3326 "notification_sample_size" = 0,
3327 "notification_dow" = NULL,
3328 "notification_hour" = NULL,
3329 "notification_sent" = NULL,
3330 "login_recovery_expiry" = NULL,
3331 "password_reset_secret" = NULL,
3332 "password_reset_secret_expiry" = NULL,
3333 "location" = NULL
3334 WHERE "id" = "member_id_p";
3335 -- "text_search_data" is updated by triggers
3336 DELETE FROM "member_settings" WHERE "member_id" = "member_id_p";
3337 DELETE FROM "member_profile" WHERE "member_id" = "member_id_p";
3338 DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";
3339 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
3340 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
3341 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
3342 DELETE FROM "session" WHERE "member_id" = "member_id_p";
3343 DELETE FROM "member_application" WHERE "member_id" = "member_id_p";
3344 DELETE FROM "token" WHERE "member_id" = "member_id_p";
3345 DELETE FROM "subscription" WHERE "member_id" = "member_id_p";
3346 DELETE FROM "ignored_area" WHERE "member_id" = "member_id_p";
3347 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
3348 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
3349 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
3350 DELETE FROM "direct_voter" USING "issue"
3351 WHERE "direct_voter"."issue_id" = "issue"."id"
3352 AND "issue"."closed" ISNULL
3353 AND "member_id" = "member_id_p";
3354 DELETE FROM "notification_initiative_sent" WHERE "member_id" = "member_id_p";
3355 RETURN;
3356 END;
3357 $$;
3360 CREATE OR REPLACE FUNCTION "delete_private_data"()
3361 RETURNS VOID
3362 LANGUAGE 'plpgsql' VOLATILE AS $$
3363 BEGIN
3364 DELETE FROM "temporary_transaction_data";
3365 DELETE FROM "temporary_suggestion_counts";
3366 DELETE FROM "member" WHERE "activated" ISNULL;
3367 UPDATE "member" SET
3368 "invite_code" = NULL,
3369 "invite_code_expiry" = NULL,
3370 "admin_comment" = NULL,
3371 "last_login" = NULL,
3372 "last_delegation_check" = NULL,
3373 "login" = NULL,
3374 "password" = NULL,
3375 "authority" = NULL,
3376 "authority_uid" = NULL,
3377 "authority_login" = NULL,
3378 "lang" = NULL,
3379 "notify_email" = NULL,
3380 "notify_email_unconfirmed" = NULL,
3381 "notify_email_secret" = NULL,
3382 "notify_email_secret_expiry" = NULL,
3383 "notify_email_lock_expiry" = NULL,
3384 "disable_notifications" = TRUE,
3385 "notification_counter" = DEFAULT,
3386 "notification_sample_size" = 0,
3387 "notification_dow" = NULL,
3388 "notification_hour" = NULL,
3389 "notification_sent" = NULL,
3390 "login_recovery_expiry" = NULL,
3391 "password_reset_secret" = NULL,
3392 "password_reset_secret_expiry" = NULL,
3393 "location" = NULL;
3394 -- "text_search_data" is updated by triggers
3395 DELETE FROM "verification";
3396 DELETE FROM "member_settings";
3397 DELETE FROM "member_useterms";
3398 DELETE FROM "member_profile";
3399 DELETE FROM "rendered_member_statement";
3400 DELETE FROM "member_image";
3401 DELETE FROM "contact";
3402 DELETE FROM "ignored_member";
3403 DELETE FROM "session";
3404 DELETE FROM "system_application";
3405 DELETE FROM "system_application_redirect_uri";
3406 DELETE FROM "dynamic_application_scope";
3407 DELETE FROM "member_application";
3408 DELETE FROM "token";
3409 DELETE FROM "subscription";
3410 DELETE FROM "ignored_area";
3411 DELETE FROM "ignored_initiative";
3412 DELETE FROM "non_voter";
3413 DELETE FROM "direct_voter" USING "issue"
3414 WHERE "direct_voter"."issue_id" = "issue"."id"
3415 AND "issue"."closed" ISNULL;
3416 DELETE FROM "event_processed";
3417 DELETE FROM "notification_initiative_sent";
3418 DELETE FROM "newsletter";
3419 RETURN;
3420 END;
3421 $$;
3424 CREATE TEMPORARY TABLE "old_snapshot" AS
3425 SELECT "ordered".*, row_number() OVER () AS "snapshot_id"
3426 FROM (
3427 SELECT * FROM (
3428 SELECT
3429 "id" AS "issue_id",
3430 'end_of_admission'::"snapshot_event" AS "event",
3431 "accepted" AS "calculated"
3432 FROM "issue" WHERE "accepted" NOTNULL
3433 UNION ALL
3434 SELECT
3435 "id" AS "issue_id",
3436 'half_freeze'::"snapshot_event" AS "event",
3437 "half_frozen" AS "calculated"
3438 FROM "issue" WHERE "half_frozen" NOTNULL
3439 UNION ALL
3440 SELECT
3441 "id" AS "issue_id",
3442 'full_freeze'::"snapshot_event" AS "event",
3443 "fully_frozen" AS "calculated"
3444 FROM "issue" WHERE "fully_frozen" NOTNULL
3445 ) AS "unordered"
3446 ORDER BY "calculated", "issue_id", "event"
3447 ) AS "ordered";
3450 INSERT INTO "snapshot" ("id", "calculated", "population", "area_id", "issue_id")
3451 SELECT
3452 "old_snapshot"."snapshot_id" AS "id",
3453 "old_snapshot"."calculated",
3454 ( SELECT COALESCE(sum("weight"), 0)
3455 FROM "direct_population_snapshot" "dps"
3456 WHERE "dps"."issue_id" = "old_snapshot"."issue_id"
3457 AND "dps"."event" = "old_snapshot"."event"
3458 ) AS "population",
3459 "issue"."area_id" AS "area_id",
3460 "issue"."id" AS "issue_id"
3461 FROM "old_snapshot" JOIN "issue"
3462 ON "old_snapshot"."issue_id" = "issue"."id";
3465 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
3466 SELECT "id" AS "snapshot_id", "issue_id" FROM "snapshot";
3469 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
3470 SELECT
3471 "old_snapshot"."snapshot_id",
3472 "direct_population_snapshot"."member_id"
3473 FROM "old_snapshot" JOIN "direct_population_snapshot"
3474 ON "old_snapshot"."issue_id" = "direct_population_snapshot"."issue_id"
3475 AND "old_snapshot"."event" = "direct_population_snapshot"."event";
3477 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
3478 SELECT
3479 "old_snapshot"."snapshot_id",
3480 "delegating_population_snapshot"."member_id"
3481 FROM "old_snapshot" JOIN "delegating_population_snapshot"
3482 ON "old_snapshot"."issue_id" = "delegating_population_snapshot"."issue_id"
3483 AND "old_snapshot"."event" = "delegating_population_snapshot"."event";
3486 INSERT INTO "direct_interest_snapshot"
3487 ("snapshot_id", "issue_id", "member_id", "weight")
3488 SELECT
3489 "old_snapshot"."snapshot_id",
3490 "old_snapshot"."issue_id",
3491 "direct_interest_snapshot_old"."member_id",
3492 "direct_interest_snapshot_old"."weight"
3493 FROM "old_snapshot" JOIN "direct_interest_snapshot_old"
3494 ON "old_snapshot"."issue_id" = "direct_interest_snapshot_old"."issue_id"
3495 AND "old_snapshot"."event" = "direct_interest_snapshot_old"."event";
3497 INSERT INTO "delegating_interest_snapshot"
3498 ( "snapshot_id", "issue_id",
3499 "member_id", "weight", "scope", "delegate_member_ids" )
3500 SELECT
3501 "old_snapshot"."snapshot_id",
3502 "old_snapshot"."issue_id",
3503 "delegating_interest_snapshot_old"."member_id",
3504 "delegating_interest_snapshot_old"."weight",
3505 "delegating_interest_snapshot_old"."scope",
3506 "delegating_interest_snapshot_old"."delegate_member_ids"
3507 FROM "old_snapshot" JOIN "delegating_interest_snapshot_old"
3508 ON "old_snapshot"."issue_id" = "delegating_interest_snapshot_old"."issue_id"
3509 AND "old_snapshot"."event" = "delegating_interest_snapshot_old"."event";
3511 INSERT INTO "direct_supporter_snapshot"
3512 ( "snapshot_id", "issue_id",
3513 "initiative_id", "member_id", "draft_id", "informed", "satisfied" )
3514 SELECT
3515 "old_snapshot"."snapshot_id",
3516 "old_snapshot"."issue_id",
3517 "direct_supporter_snapshot_old"."initiative_id",
3518 "direct_supporter_snapshot_old"."member_id",
3519 "direct_supporter_snapshot_old"."draft_id",
3520 "direct_supporter_snapshot_old"."informed",
3521 "direct_supporter_snapshot_old"."satisfied"
3522 FROM "old_snapshot" JOIN "direct_supporter_snapshot_old"
3523 ON "old_snapshot"."issue_id" = "direct_supporter_snapshot_old"."issue_id"
3524 AND "old_snapshot"."event" = "direct_supporter_snapshot_old"."event";
3527 ALTER TABLE "issue" DISABLE TRIGGER USER; -- NOTE: required to modify table later
3529 UPDATE "issue" SET "latest_snapshot_id" = "snapshot"."id"
3530 FROM (
3531 SELECT DISTINCT ON ("issue_id") "issue_id", "id"
3532 FROM "snapshot" ORDER BY "issue_id", "id" DESC
3533 ) AS "snapshot"
3534 WHERE "snapshot"."issue_id" = "issue"."id";
3536 UPDATE "issue" SET "admission_snapshot_id" = "old_snapshot"."snapshot_id"
3537 FROM "old_snapshot"
3538 WHERE "old_snapshot"."issue_id" = "issue"."id"
3539 AND "old_snapshot"."event" = 'end_of_admission';
3541 UPDATE "issue" SET "half_freeze_snapshot_id" = "old_snapshot"."snapshot_id"
3542 FROM "old_snapshot"
3543 WHERE "old_snapshot"."issue_id" = "issue"."id"
3544 AND "old_snapshot"."event" = 'half_freeze';
3546 UPDATE "issue" SET "full_freeze_snapshot_id" = "old_snapshot"."snapshot_id"
3547 FROM "old_snapshot"
3548 WHERE "old_snapshot"."issue_id" = "issue"."id"
3549 AND "old_snapshot"."event" = 'full_freeze';
3551 ALTER TABLE "issue" ENABLE TRIGGER USER;
3554 DROP TABLE "old_snapshot";
3556 DROP TABLE "direct_supporter_snapshot_old";
3557 DROP TABLE "delegating_interest_snapshot_old";
3558 DROP TABLE "direct_interest_snapshot_old";
3559 DROP TABLE "delegating_population_snapshot";
3560 DROP TABLE "direct_population_snapshot";
3563 DROP VIEW "open_issue";
3566 ALTER TABLE "issue" DROP COLUMN "latest_snapshot_event";
3569 CREATE VIEW "open_issue" AS
3570 SELECT * FROM "issue" WHERE "closed" ISNULL;
3572 COMMENT ON VIEW "open_issue" IS 'All open issues';
3575 -- NOTE: create "issue_for_admission" view after altering table "issue"
3576 CREATE VIEW "issue_for_admission" AS
3577 SELECT DISTINCT ON ("issue"."area_id")
3578 "issue".*,
3579 max("initiative"."supporter_count") AS "max_supporter_count"
3580 FROM "issue"
3581 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
3582 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
3583 JOIN "area" ON "issue"."area_id" = "area"."id"
3584 WHERE "issue"."state" = 'admission'::"issue_state"
3585 AND now() >= "issue"."created" + "issue"."min_admission_time"
3586 AND "initiative"."supporter_count" >= "policy"."issue_quorum"
3587 AND "initiative"."supporter_count" * "policy"."issue_quorum_den" >=
3588 "issue"."population" * "policy"."issue_quorum_num"
3589 AND "initiative"."supporter_count" >= "area"."issue_quorum"
3590 AND "initiative"."revoked" ISNULL
3591 GROUP BY "issue"."id"
3592 ORDER BY "issue"."area_id", "max_supporter_count" DESC, "issue"."id";
3594 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';
3597 DROP TYPE "snapshot_event";
3600 ALTER TABLE "issue" ADD CONSTRAINT "snapshot_required" CHECK (
3601 ("half_frozen" ISNULL OR "half_freeze_snapshot_id" NOTNULL) AND
3602 ("fully_frozen" ISNULL OR "full_freeze_snapshot_id" NOTNULL) );
3605 COMMIT;