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