liquid_feedback_core

view update/core-update.v3.2.2-v4.0.0.sql @ 593:e7f772ca0621

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

Impressum / About Us