liquid_feedback_core
view update/core-update.v3.2.2-v4.0.0.sql @ 546:f46ebb677898
Fixed unit/area/policy event triggers
| author | jbe |
|---|---|
| date | Tue Jul 18 01:14:45 2017 +0200 (2017-07-18) |
| parents | 9c433d24ed00 |
| children | 3cde0bb68adf |
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" = FALSE AND NEW."active" = TRUE THEN
989 "event_v" := 'unit_created';
990 ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN
991 "event_v" := 'unit_removed';
992 ELSIF OLD != NEW THEN
993 "event_v" := 'unit_updated';
994 ELSE
995 RETURN NULL;
996 END IF;
997 ELSE
998 "event_v" := 'unit_created';
999 END IF;
1000 INSERT INTO "event" ("event", "unit_id") VALUES ("event_v", NEW."id");
1001 RETURN NULL;
1002 END;
1003 $$;
1005 CREATE TRIGGER "write_event_unit" AFTER INSERT OR UPDATE ON "unit"
1006 FOR EACH ROW EXECUTE PROCEDURE "write_event_unit_trigger"();
1008 COMMENT ON FUNCTION "write_event_unit_trigger"() IS 'Implementation of trigger "write_event_unit" on table "unit"';
1009 COMMENT ON TRIGGER "write_event_unit" ON "unit" IS 'Create entry in "event" table on new or changed/disabled units';
1012 CREATE FUNCTION "write_event_area_trigger"()
1013 RETURNS TRIGGER
1014 LANGUAGE 'plpgsql' VOLATILE AS $$
1015 DECLARE
1016 "event_v" "event_type";
1017 BEGIN
1018 IF TG_OP = 'UPDATE' THEN
1019 IF OLD."active" = FALSE AND NEW."active" = FALSE THEN
1020 RETURN NULL;
1021 ELSIF OLD."active" = FALSE AND NEW."active" = TRUE THEN
1022 "event_v" := 'area_created';
1023 ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN
1024 "event_v" := 'area_removed';
1025 ELSIF OLD != NEW THEN
1026 "event_v" := 'area_updated';
1027 ELSE
1028 RETURN NULL;
1029 END IF;
1030 ELSE
1031 "event_v" := 'area_created';
1032 END IF;
1033 INSERT INTO "event" ("event", "area_id") VALUES ("event_v", NEW."id");
1034 RETURN NULL;
1035 END;
1036 $$;
1038 CREATE TRIGGER "write_event_area" AFTER INSERT OR UPDATE ON "area"
1039 FOR EACH ROW EXECUTE PROCEDURE "write_event_area_trigger"();
1041 COMMENT ON FUNCTION "write_event_area_trigger"() IS 'Implementation of trigger "write_event_area" on table "area"';
1042 COMMENT ON TRIGGER "write_event_area" ON "area" IS 'Create entry in "event" table on new or changed/disabled areas';
1045 CREATE FUNCTION "write_event_policy_trigger"()
1046 RETURNS TRIGGER
1047 LANGUAGE 'plpgsql' VOLATILE AS $$
1048 DECLARE
1049 "event_v" "event_type";
1050 BEGIN
1051 IF TG_OP = 'UPDATE' THEN
1052 IF OLD."active" = FALSE AND NEW."active" = FALSE THEN
1053 RETURN NULL;
1054 ELSIF OLD."active" = FALSE AND NEW."active" = TRUE THEN
1055 "event_v" := 'policy_created';
1056 ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN
1057 "event_v" := 'policy_removed';
1058 ELSIF OLD != NEW THEN
1059 "event_v" := 'policy_updated';
1060 ELSE
1061 RETURN NULL;
1062 END IF;
1063 ELSE
1064 "event_v" := 'policy_created';
1065 END IF;
1066 INSERT INTO "event" ("event", "policy_id") VALUES ("event_v", NEW."id");
1067 RETURN NULL;
1068 END;
1069 $$;
1071 CREATE TRIGGER "write_event_policy" AFTER INSERT OR UPDATE ON "policy"
1072 FOR EACH ROW EXECUTE PROCEDURE "write_event_policy_trigger"();
1074 COMMENT ON FUNCTION "write_event_policy_trigger"() IS 'Implementation of trigger "write_event_policy" on table "policy"';
1075 COMMENT ON TRIGGER "write_event_policy" ON "policy" IS 'Create entry in "event" table on new or changed/disabled policies';
1078 CREATE OR REPLACE FUNCTION "write_event_issue_state_changed_trigger"()
1079 RETURNS TRIGGER
1080 LANGUAGE 'plpgsql' VOLATILE AS $$
1081 DECLARE
1082 "area_row" "area"%ROWTYPE;
1083 BEGIN
1084 IF NEW."state" != OLD."state" THEN
1085 SELECT * INTO "area_row" FROM "area" WHERE "id" = NEW."area_id"
1086 FOR SHARE;
1087 INSERT INTO "event" (
1088 "event",
1089 "unit_id", "area_id", "policy_id", "issue_id", "state"
1090 ) VALUES (
1091 'issue_state_changed',
1092 "area_row"."unit_id", NEW."area_id", NEW."policy_id",
1093 NEW."id", NEW."state"
1094 );
1095 END IF;
1096 RETURN NULL;
1097 END;
1098 $$;
1101 CREATE OR REPLACE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1102 RETURNS TRIGGER
1103 LANGUAGE 'plpgsql' VOLATILE AS $$
1104 DECLARE
1105 "initiative_row" "initiative"%ROWTYPE;
1106 "issue_row" "issue"%ROWTYPE;
1107 "area_row" "area"%ROWTYPE;
1108 "event_v" "event_type";
1109 BEGIN
1110 SELECT * INTO "initiative_row" FROM "initiative"
1111 WHERE "id" = NEW."initiative_id" FOR SHARE;
1112 SELECT * INTO "issue_row" FROM "issue"
1113 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1114 SELECT * INTO "area_row" FROM "area"
1115 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1116 IF EXISTS (
1117 SELECT NULL FROM "draft"
1118 WHERE "initiative_id" = NEW."initiative_id" AND "id" != NEW."id"
1119 FOR SHARE
1120 ) THEN
1121 "event_v" := 'new_draft_created';
1122 ELSE
1123 IF EXISTS (
1124 SELECT NULL FROM "initiative"
1125 WHERE "issue_id" = "initiative_row"."issue_id"
1126 AND "id" != "initiative_row"."id"
1127 FOR SHARE
1128 ) THEN
1129 "event_v" := 'initiative_created_in_existing_issue';
1130 ELSE
1131 "event_v" := 'initiative_created_in_new_issue';
1132 END IF;
1133 END IF;
1134 INSERT INTO "event" (
1135 "event", "member_id",
1136 "unit_id", "area_id", "policy_id", "issue_id", "state",
1137 "initiative_id", "draft_id"
1138 ) VALUES (
1139 "event_v", NEW."author_id",
1140 "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",
1141 "initiative_row"."issue_id", "issue_row"."state",
1142 NEW."initiative_id", NEW."id"
1143 );
1144 RETURN NULL;
1145 END;
1146 $$;
1149 CREATE OR REPLACE FUNCTION "write_event_initiative_revoked_trigger"()
1150 RETURNS TRIGGER
1151 LANGUAGE 'plpgsql' VOLATILE AS $$
1152 DECLARE
1153 "issue_row" "issue"%ROWTYPE;
1154 "area_row" "area"%ROWTYPE;
1155 "draft_id_v" "draft"."id"%TYPE;
1156 BEGIN
1157 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1158 SELECT * INTO "issue_row" FROM "issue"
1159 WHERE "id" = NEW."issue_id" FOR SHARE;
1160 SELECT * INTO "area_row" FROM "area"
1161 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1162 SELECT "id" INTO "draft_id_v" FROM "current_draft"
1163 WHERE "initiative_id" = NEW."id" FOR SHARE;
1164 INSERT INTO "event" (
1165 "event", "member_id",
1166 "unit_id", "area_id", "policy_id", "issue_id", "state",
1167 "initiative_id", "draft_id"
1168 ) VALUES (
1169 'initiative_revoked', NEW."revoked_by_member_id",
1170 "area_row"."unit_id", "issue_row"."area_id",
1171 "issue_row"."policy_id",
1172 NEW."issue_id", "issue_row"."state",
1173 NEW."id", "draft_id_v"
1174 );
1175 END IF;
1176 RETURN NULL;
1177 END;
1178 $$;
1181 CREATE OR REPLACE FUNCTION "write_event_suggestion_created_trigger"()
1182 RETURNS TRIGGER
1183 LANGUAGE 'plpgsql' VOLATILE AS $$
1184 DECLARE
1185 "initiative_row" "initiative"%ROWTYPE;
1186 "issue_row" "issue"%ROWTYPE;
1187 "area_row" "area"%ROWTYPE;
1188 BEGIN
1189 SELECT * INTO "initiative_row" FROM "initiative"
1190 WHERE "id" = NEW."initiative_id" FOR SHARE;
1191 SELECT * INTO "issue_row" FROM "issue"
1192 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1193 SELECT * INTO "area_row" FROM "area"
1194 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1195 INSERT INTO "event" (
1196 "event", "member_id",
1197 "unit_id", "area_id", "policy_id", "issue_id", "state",
1198 "initiative_id", "suggestion_id"
1199 ) VALUES (
1200 'suggestion_created', NEW."author_id",
1201 "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",
1202 "initiative_row"."issue_id", "issue_row"."state",
1203 NEW."initiative_id", NEW."id"
1204 );
1205 RETURN NULL;
1206 END;
1207 $$;
1210 CREATE FUNCTION "write_event_suggestion_removed_trigger"()
1211 RETURNS TRIGGER
1212 LANGUAGE 'plpgsql' VOLATILE AS $$
1213 DECLARE
1214 "initiative_row" "initiative"%ROWTYPE;
1215 "issue_row" "issue"%ROWTYPE;
1216 "area_row" "area"%ROWTYPE;
1217 BEGIN
1218 SELECT * INTO "initiative_row" FROM "initiative"
1219 WHERE "id" = OLD."initiative_id" FOR SHARE;
1220 IF "initiative_row"."id" NOTNULL THEN
1221 SELECT * INTO "issue_row" FROM "issue"
1222 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1223 SELECT * INTO "area_row" FROM "area"
1224 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1225 INSERT INTO "event" (
1226 "event",
1227 "unit_id", "area_id", "policy_id", "issue_id", "state",
1228 "initiative_id", "suggestion_id"
1229 ) VALUES (
1230 'suggestion_removed',
1231 "area_row"."unit_id", "issue_row"."area_id",
1232 "issue_row"."policy_id",
1233 "initiative_row"."issue_id", "issue_row"."state",
1234 OLD."initiative_id", OLD."id"
1235 );
1236 END IF;
1237 RETURN NULL;
1238 END;
1239 $$;
1241 CREATE TRIGGER "write_event_suggestion_removed"
1242 AFTER DELETE ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1243 "write_event_suggestion_removed_trigger"();
1245 COMMENT ON FUNCTION "write_event_suggestion_removed_trigger"() IS 'Implementation of trigger "write_event_suggestion_removed" on table "issue"';
1246 COMMENT ON TRIGGER "write_event_suggestion_removed" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1249 CREATE FUNCTION "write_event_member_trigger"()
1250 RETURNS TRIGGER
1251 LANGUAGE 'plpgsql' VOLATILE AS $$
1252 BEGIN
1253 IF TG_OP = 'INSERT' THEN
1254 IF NEW."activated" NOTNULL THEN
1255 INSERT INTO "event" ("event", "member_id")
1256 VALUES ('member_activated', NEW."id");
1257 END IF;
1258 IF NEW."active" THEN
1259 INSERT INTO "event" ("event", "member_id", "boolean_value")
1260 VALUES ('member_active', NEW."id", TRUE);
1261 END IF;
1262 ELSIF TG_OP = 'UPDATE' THEN
1263 IF OLD."id" != NEW."id" THEN
1264 RAISE EXCEPTION 'Cannot change member ID';
1265 END IF;
1266 IF OLD."name" != NEW."name" THEN
1267 INSERT INTO "event" (
1268 "event", "member_id", "text_value", "old_text_value"
1269 ) VALUES (
1270 'member_name_updated', NEW."id", NEW."name", OLD."name"
1271 );
1272 END IF;
1273 IF OLD."active" != NEW."active" THEN
1274 INSERT INTO "event" ("event", "member_id", "boolean_value") VALUES (
1275 'member_active', NEW."id", NEW."active"
1276 );
1277 END IF;
1278 IF
1279 OLD."activated" NOTNULL AND
1280 (OLD."login" NOTNULL OR OLD."authority_login" NOTNULL) AND
1281 NEW."login" ISNULL AND
1282 NEW."authority_login" ISNULL AND
1283 NEW."locked" = TRUE
1284 THEN
1285 INSERT INTO "event" ("event", "member_id")
1286 VALUES ('member_removed', NEW."id");
1287 END IF;
1288 END IF;
1289 RETURN NULL;
1290 END;
1291 $$;
1293 CREATE TRIGGER "write_event_member"
1294 AFTER INSERT OR UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1295 "write_event_member_trigger"();
1297 COMMENT ON FUNCTION "write_event_member_trigger"() IS 'Implementation of trigger "write_event_member" on table "member"';
1298 COMMENT ON TRIGGER "write_event_member" ON "member" IS 'Create entries in "event" table on insertion to member table';
1301 CREATE FUNCTION "write_event_member_profile_updated_trigger"()
1302 RETURNS TRIGGER
1303 LANGUAGE 'plpgsql' VOLATILE AS $$
1304 BEGIN
1305 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1306 IF EXISTS (SELECT NULL FROM "member" WHERE "id" = OLD."member_id") THEN
1307 INSERT INTO "event" ("event", "member_id") VALUES (
1308 'member_profile_updated', OLD."member_id"
1309 );
1310 END IF;
1311 END IF;
1312 IF TG_OP = 'UPDATE' THEN
1313 IF OLD."member_id" = NEW."member_id" THEN
1314 RETURN NULL;
1315 END IF;
1316 END IF;
1317 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1318 INSERT INTO "event" ("event", "member_id") VALUES (
1319 'member_profile_updated', NEW."member_id"
1320 );
1321 END IF;
1322 RETURN NULL;
1323 END;
1324 $$;
1326 CREATE TRIGGER "write_event_member_profile_updated"
1327 AFTER INSERT OR UPDATE OR DELETE ON "member_profile"
1328 FOR EACH ROW EXECUTE PROCEDURE
1329 "write_event_member_profile_updated_trigger"();
1331 COMMENT ON FUNCTION "write_event_member_profile_updated_trigger"() IS 'Implementation of trigger "write_event_member_profile_updated" on table "member_profile"';
1332 COMMENT ON TRIGGER "write_event_member_profile_updated" ON "member_profile" IS 'Creates entries in "event" table on member profile update';
1335 CREATE FUNCTION "write_event_member_image_updated_trigger"()
1336 RETURNS TRIGGER
1337 LANGUAGE 'plpgsql' VOLATILE AS $$
1338 BEGIN
1339 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1340 IF NOT OLD."scaled" THEN
1341 IF EXISTS (SELECT NULL FROM "member" WHERE "id" = OLD."member_id") THEN
1342 INSERT INTO "event" ("event", "member_id") VALUES (
1343 'member_image_updated', OLD."member_id"
1344 );
1345 END IF;
1346 END IF;
1347 END IF;
1348 IF TG_OP = 'UPDATE' THEN
1349 IF
1350 OLD."member_id" = NEW."member_id" AND
1351 OLD."scaled" = NEW."scaled"
1352 THEN
1353 RETURN NULL;
1354 END IF;
1355 END IF;
1356 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1357 IF NOT NEW."scaled" THEN
1358 INSERT INTO "event" ("event", "member_id") VALUES (
1359 'member_image_updated', NEW."member_id"
1360 );
1361 END IF;
1362 END IF;
1363 RETURN NULL;
1364 END;
1365 $$;
1367 CREATE TRIGGER "write_event_member_image_updated"
1368 AFTER INSERT OR UPDATE OR DELETE ON "member_image"
1369 FOR EACH ROW EXECUTE PROCEDURE
1370 "write_event_member_image_updated_trigger"();
1372 COMMENT ON FUNCTION "write_event_member_image_updated_trigger"() IS 'Implementation of trigger "write_event_member_image_updated" on table "member_image"';
1373 COMMENT ON TRIGGER "write_event_member_image_updated" ON "member_image" IS 'Creates entries in "event" table on member image update';
1376 CREATE FUNCTION "write_event_interest_trigger"()
1377 RETURNS TRIGGER
1378 LANGUAGE 'plpgsql' VOLATILE AS $$
1379 DECLARE
1380 "issue_row" "issue"%ROWTYPE;
1381 "area_row" "area"%ROWTYPE;
1382 BEGIN
1383 IF TG_OP = 'UPDATE' THEN
1384 IF OLD = NEW THEN
1385 RETURN NULL;
1386 END IF;
1387 END IF;
1388 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1389 SELECT * INTO "issue_row" FROM "issue"
1390 WHERE "id" = OLD."issue_id" FOR SHARE;
1391 SELECT * INTO "area_row" FROM "area"
1392 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1393 IF "issue_row"."id" NOTNULL THEN
1394 INSERT INTO "event" (
1395 "event", "member_id",
1396 "unit_id", "area_id", "policy_id", "issue_id", "state",
1397 "boolean_value"
1398 ) VALUES (
1399 'interest', OLD."member_id",
1400 "area_row"."unit_id", "issue_row"."area_id",
1401 "issue_row"."policy_id",
1402 OLD."issue_id", "issue_row"."state",
1403 FALSE
1404 );
1405 END IF;
1406 END IF;
1407 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1408 SELECT * INTO "issue_row" FROM "issue"
1409 WHERE "id" = NEW."issue_id" FOR SHARE;
1410 SELECT * INTO "area_row" FROM "area"
1411 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1412 INSERT INTO "event" (
1413 "event", "member_id",
1414 "unit_id", "area_id", "policy_id", "issue_id", "state",
1415 "boolean_value"
1416 ) VALUES (
1417 'interest', NEW."member_id",
1418 "area_row"."unit_id", "issue_row"."area_id",
1419 "issue_row"."policy_id",
1420 NEW."issue_id", "issue_row"."state",
1421 TRUE
1422 );
1423 END IF;
1424 RETURN NULL;
1425 END;
1426 $$;
1428 CREATE TRIGGER "write_event_interest"
1429 AFTER INSERT OR UPDATE OR DELETE ON "interest" FOR EACH ROW EXECUTE PROCEDURE
1430 "write_event_interest_trigger"();
1432 COMMENT ON FUNCTION "write_event_interest_trigger"() IS 'Implementation of trigger "write_event_interest_inserted" on table "interest"';
1433 COMMENT ON TRIGGER "write_event_interest" ON "interest" IS 'Create entry in "event" table on adding or removing interest';
1436 CREATE FUNCTION "write_event_initiator_trigger"()
1437 RETURNS TRIGGER
1438 LANGUAGE 'plpgsql' VOLATILE AS $$
1439 DECLARE
1440 "initiative_row" "initiative"%ROWTYPE;
1441 "issue_row" "issue"%ROWTYPE;
1442 "area_row" "area"%ROWTYPE;
1443 BEGIN
1444 IF TG_OP = 'UPDATE' THEN
1445 IF
1446 OLD."initiative_id" = NEW."initiative_id" AND
1447 OLD."member_id" = NEW."member_id" AND
1448 coalesce(OLD."accepted", FALSE) = coalesce(NEW."accepted", FALSE)
1449 THEN
1450 RETURN NULL;
1451 END IF;
1452 END IF;
1453 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "accepted_v" THEN
1454 IF coalesce(OLD."accepted", FALSE) = TRUE THEN
1455 SELECT * INTO "initiative_row" FROM "initiative"
1456 WHERE "id" = OLD."initiative_id" FOR SHARE;
1457 IF "initiative_row"."id" NOTNULL THEN
1458 SELECT * INTO "issue_row" FROM "issue"
1459 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1460 SELECT * INTO "area_row" FROM "area"
1461 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1462 INSERT INTO "event" (
1463 "event", "member_id",
1464 "unit_id", "area_id", "policy_id", "issue_id", "state",
1465 "initiative_id", "boolean_value"
1466 ) VALUES (
1467 'initiator', OLD."member_id",
1468 "area_row"."unit_id", "issue_row"."area_id",
1469 "issue_row"."policy_id",
1470 "issue_row"."id", "issue_row"."state",
1471 OLD."initiative_id", FALSE
1472 );
1473 END IF;
1474 END IF;
1475 END IF;
1476 IF TG_OP = 'UPDATE' AND NOT "rejected_v" THEN
1477 IF coalesce(NEW."accepted", FALSE) = TRUE THEN
1478 SELECT * INTO "initiative_row" FROM "initiative"
1479 WHERE "id" = NEW."initiative_id" FOR SHARE;
1480 SELECT * INTO "issue_row" FROM "issue"
1481 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1482 SELECT * INTO "area_row" FROM "area"
1483 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1484 INSERT INTO "event" (
1485 "event", "member_id",
1486 "unit_id", "area_id", "policy_id", "issue_id", "state",
1487 "initiative_id", "boolean_value"
1488 ) VALUES (
1489 'initiator', NEW."member_id",
1490 "area_row"."unit_id", "issue_row"."area_id",
1491 "issue_row"."policy_id",
1492 "issue_row"."id", "issue_row"."state",
1493 NEW."initiative_id", TRUE
1494 );
1495 END IF;
1496 END IF;
1497 RETURN NULL;
1498 END;
1499 $$;
1501 CREATE TRIGGER "write_event_initiator"
1502 AFTER UPDATE OR DELETE ON "initiator" FOR EACH ROW EXECUTE PROCEDURE
1503 "write_event_initiator_trigger"();
1505 COMMENT ON FUNCTION "write_event_initiator_trigger"() IS 'Implementation of trigger "write_event_initiator" on table "initiator"';
1506 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)';
1509 CREATE FUNCTION "write_event_support_trigger"()
1510 RETURNS TRIGGER
1511 LANGUAGE 'plpgsql' VOLATILE AS $$
1512 DECLARE
1513 "issue_row" "issue"%ROWTYPE;
1514 "area_row" "area"%ROWTYPE;
1515 BEGIN
1516 IF TG_OP = 'UPDATE' THEN
1517 IF
1518 OLD."initiative_id" = NEW."initiative_id" AND
1519 OLD."member_id" = NEW."member_id"
1520 THEN
1521 IF OLD."draft_id" != NEW."draft_id" THEN
1522 SELECT * INTO "issue_row" FROM "issue"
1523 WHERE "id" = NEW."issue_id" FOR SHARE;
1524 SELECT * INTO "area_row" FROM "area"
1525 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1526 INSERT INTO "event" (
1527 "event", "member_id",
1528 "unit_id", "area_id", "policy_id", "issue_id", "state",
1529 "initiative_id", "draft_id"
1530 ) VALUES (
1531 'support_updated', NEW."member_id",
1532 "area_row"."unit_id", "issue_row"."area_id",
1533 "issue_row"."policy_id",
1534 "issue_row"."id", "issue_row"."state",
1535 NEW."initiative_id", NEW."draft_id"
1536 );
1537 END IF;
1538 RETURN NULL;
1539 END IF;
1540 END IF;
1541 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1542 IF EXISTS (
1543 SELECT NULL FROM "initiative" WHERE "id" = OLD."initiative_id"
1544 FOR SHARE
1545 ) THEN
1546 SELECT * INTO "issue_row" FROM "issue"
1547 WHERE "id" = OLD."issue_id" FOR SHARE;
1548 SELECT * INTO "area_row" FROM "area"
1549 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1550 INSERT INTO "event" (
1551 "event", "member_id",
1552 "unit_id", "area_id", "policy_id", "issue_id", "state",
1553 "initiative_id", "boolean_value"
1554 ) VALUES (
1555 'support', OLD."member_id",
1556 "area_row"."unit_id", "issue_row"."area_id",
1557 "issue_row"."policy_id",
1558 "issue_row"."id", "issue_row"."state",
1559 OLD."initiative_id", FALSE
1560 );
1561 END IF;
1562 END IF;
1563 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1564 SELECT * INTO "issue_row" FROM "issue"
1565 WHERE "id" = NEW."issue_id" FOR SHARE;
1566 SELECT * INTO "area_row" FROM "area"
1567 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1568 INSERT INTO "event" (
1569 "event", "member_id",
1570 "unit_id", "area_id", "policy_id", "issue_id", "state",
1571 "initiative_id", "draft_id", "boolean_value"
1572 ) VALUES (
1573 'support', NEW."member_id",
1574 "area_row"."unit_id", "issue_row"."area_id",
1575 "issue_row"."policy_id",
1576 "issue_row"."id", "issue_row"."state",
1577 NEW."initiative_id", NEW."draft_id", TRUE
1578 );
1579 END IF;
1580 RETURN NULL;
1581 END;
1582 $$;
1584 CREATE TRIGGER "write_event_support"
1585 AFTER INSERT OR UPDATE OR DELETE ON "supporter" FOR EACH ROW EXECUTE PROCEDURE
1586 "write_event_support_trigger"();
1588 COMMENT ON FUNCTION "write_event_support_trigger"() IS 'Implementation of trigger "write_event_support" on table "supporter"';
1589 COMMENT ON TRIGGER "write_event_support" ON "supporter" IS 'Create entry in "event" table when adding, updating, or removing support';
1592 CREATE FUNCTION "write_event_suggestion_rated_trigger"()
1593 RETURNS TRIGGER
1594 LANGUAGE 'plpgsql' VOLATILE AS $$
1595 DECLARE
1596 "same_pkey_v" BOOLEAN = FALSE;
1597 "initiative_row" "initiative"%ROWTYPE;
1598 "issue_row" "issue"%ROWTYPE;
1599 "area_row" "area"%ROWTYPE;
1600 BEGIN
1601 IF TG_OP = 'UPDATE' THEN
1602 IF
1603 OLD."suggestion_id" = NEW."suggestion_id" AND
1604 OLD."member_id" = NEW."member_id"
1605 THEN
1606 IF
1607 OLD."degree" = NEW."degree" AND
1608 OLD."fulfilled" = NEW."fulfilled"
1609 THEN
1610 RETURN NULL;
1611 END IF;
1612 "same_pkey_v" := TRUE;
1613 END IF;
1614 END IF;
1615 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "same_pkey_v" THEN
1616 IF EXISTS (
1617 SELECT NULL FROM "suggestion" WHERE "id" = OLD."suggestion_id"
1618 FOR SHARE
1619 ) THEN
1620 SELECT * INTO "initiative_row" FROM "initiative"
1621 WHERE "id" = OLD."initiative_id" FOR SHARE;
1622 SELECT * INTO "issue_row" FROM "issue"
1623 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1624 SELECT * INTO "area_row" FROM "area"
1625 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1626 INSERT INTO "event" (
1627 "event", "member_id",
1628 "unit_id", "area_id", "policy_id", "issue_id", "state",
1629 "initiative_id", "suggestion_id",
1630 "boolean_value", "numeric_value"
1631 ) VALUES (
1632 'suggestion_rated', OLD."member_id",
1633 "area_row"."unit_id", "issue_row"."area_id",
1634 "issue_row"."policy_id",
1635 "initiative_row"."issue_id", "issue_row"."state",
1636 OLD."initiative_id", OLD."suggestion_id",
1637 NULL, 0
1638 );
1639 END IF;
1640 END IF;
1641 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1642 SELECT * INTO "initiative_row" FROM "initiative"
1643 WHERE "id" = NEW."initiative_id" FOR SHARE;
1644 SELECT * INTO "issue_row" FROM "issue"
1645 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1646 SELECT * INTO "area_row" FROM "area"
1647 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1648 INSERT INTO "event" (
1649 "event", "member_id",
1650 "unit_id", "area_id", "policy_id", "issue_id", "state",
1651 "initiative_id", "suggestion_id",
1652 "boolean_value", "numeric_value"
1653 ) VALUES (
1654 'suggestion_rated', NEW."member_id",
1655 "area_row"."unit_id", "issue_row"."area_id",
1656 "issue_row"."policy_id",
1657 "initiative_row"."issue_id", "issue_row"."state",
1658 NEW."initiative_id", NEW."suggestion_id",
1659 NEW."fulfilled", NEW."degree"
1660 );
1661 END IF;
1662 RETURN NULL;
1663 END;
1664 $$;
1666 CREATE TRIGGER "write_event_suggestion_rated"
1667 AFTER INSERT OR UPDATE OR DELETE ON "opinion" FOR EACH ROW EXECUTE PROCEDURE
1668 "write_event_suggestion_rated_trigger"();
1670 COMMENT ON FUNCTION "write_event_suggestion_rated_trigger"() IS 'Implementation of trigger "write_event_suggestion_rated" on table "opinion"';
1671 COMMENT ON TRIGGER "write_event_suggestion_rated" ON "opinion" IS 'Create entry in "event" table when adding, updating, or removing support';
1674 CREATE FUNCTION "write_event_delegation_trigger"()
1675 RETURNS TRIGGER
1676 LANGUAGE 'plpgsql' VOLATILE AS $$
1677 DECLARE
1678 "issue_row" "issue"%ROWTYPE;
1679 "area_row" "area"%ROWTYPE;
1680 BEGIN
1681 IF TG_OP = 'DELETE' THEN
1682 IF EXISTS (
1683 SELECT NULL FROM "member" WHERE "id" = OLD."truster_id"
1684 ) AND (CASE OLD."scope"
1685 WHEN 'unit'::"delegation_scope" THEN EXISTS (
1686 SELECT NULL FROM "unit" WHERE "id" = OLD."unit_id"
1687 )
1688 WHEN 'area'::"delegation_scope" THEN EXISTS (
1689 SELECT NULL FROM "area" WHERE "id" = OLD."area_id"
1690 )
1691 WHEN 'issue'::"delegation_scope" THEN EXISTS (
1692 SELECT NULL FROM "issue" WHERE "id" = OLD."issue_id"
1693 )
1694 END) THEN
1695 SELECT * INTO "issue_row" FROM "issue"
1696 WHERE "id" = OLD."issue_id" FOR SHARE;
1697 SELECT * INTO "area_row" FROM "area"
1698 WHERE "id" = COALESCE(OLD."area_id", "issue_row"."area_id")
1699 FOR SHARE;
1700 INSERT INTO "event" (
1701 "event", "member_id", "scope",
1702 "unit_id", "area_id", "issue_id", "state",
1703 "boolean_value"
1704 ) VALUES (
1705 'delegation', OLD."truster_id", OLD."scope",
1706 COALESCE(OLD."unit_id", "area_row"."unit_id"), "area_row"."id",
1707 OLD."issue_id", "issue_row"."state",
1708 FALSE
1709 );
1710 END IF;
1711 ELSE
1712 SELECT * INTO "issue_row" FROM "issue"
1713 WHERE "id" = NEW."issue_id" FOR SHARE;
1714 SELECT * INTO "area_row" FROM "area"
1715 WHERE "id" = COALESCE(NEW."area_id", "issue_row"."area_id")
1716 FOR SHARE;
1717 INSERT INTO "event" (
1718 "event", "member_id", "other_member_id", "scope",
1719 "unit_id", "area_id", "issue_id", "state",
1720 "boolean_value"
1721 ) VALUES (
1722 'delegation', NEW."truster_id", NEW."trustee_id", NEW."scope",
1723 COALESCE(NEW."unit_id", "area_row"."unit_id"), "area_row"."id",
1724 NEW."issue_id", "issue_row"."state",
1725 TRUE
1726 );
1727 END IF;
1728 RETURN NULL;
1729 END;
1730 $$;
1732 CREATE TRIGGER "write_event_delegation"
1733 AFTER INSERT OR UPDATE OR DELETE ON "delegation" FOR EACH ROW EXECUTE PROCEDURE
1734 "write_event_delegation_trigger"();
1736 COMMENT ON FUNCTION "write_event_delegation_trigger"() IS 'Implementation of trigger "write_event_delegation" on table "delegation"';
1737 COMMENT ON TRIGGER "write_event_delegation" ON "delegation" IS 'Create entry in "event" table when adding, updating, or removing a delegation';
1740 CREATE FUNCTION "write_event_contact_trigger"()
1741 RETURNS TRIGGER
1742 LANGUAGE 'plpgsql' VOLATILE AS $$
1743 BEGIN
1744 IF TG_OP = 'UPDATE' THEN
1745 IF
1746 OLD."member_id" = NEW."member_id" AND
1747 OLD."other_member_id" = NEW."other_member_id" AND
1748 OLD."public" = NEW."public"
1749 THEN
1750 RETURN NULL;
1751 END IF;
1752 END IF;
1753 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1754 IF OLD."public" THEN
1755 IF EXISTS (
1756 SELECT NULL FROM "member" WHERE "id" = OLD."member_id"
1757 FOR SHARE
1758 ) AND EXISTS (
1759 SELECT NULL FROM "member" WHERE "id" = OLD."other_member_id"
1760 FOR SHARE
1761 ) THEN
1762 INSERT INTO "event" (
1763 "event", "member_id", "other_member_id", "boolean_value"
1764 ) VALUES (
1765 'contact', OLD."member_id", OLD."other_member_id", FALSE
1766 );
1767 END IF;
1768 END IF;
1769 END IF;
1770 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1771 IF NEW."public" THEN
1772 INSERT INTO "event" (
1773 "event", "member_id", "other_member_id", "boolean_value"
1774 ) VALUES (
1775 'contact', NEW."member_id", NEW."other_member_id", TRUE
1776 );
1777 END IF;
1778 END IF;
1779 RETURN NULL;
1780 END;
1781 $$;
1783 CREATE TRIGGER "write_event_contact"
1784 AFTER INSERT OR UPDATE OR DELETE ON "contact" FOR EACH ROW EXECUTE PROCEDURE
1785 "write_event_contact_trigger"();
1787 COMMENT ON FUNCTION "write_event_contact_trigger"() IS 'Implementation of trigger "write_event_contact" on table "contact"';
1788 COMMENT ON TRIGGER "write_event_contact" ON "contact" IS 'Create entry in "event" table when adding or removing public contacts';
1791 CREATE FUNCTION "send_event_notify_trigger"()
1792 RETURNS TRIGGER
1793 LANGUAGE 'plpgsql' VOLATILE AS $$
1794 BEGIN
1795 EXECUTE 'NOTIFY "event", ''' || NEW."event" || '''';
1796 RETURN NULL;
1797 END;
1798 $$;
1800 CREATE TRIGGER "send_notify"
1801 AFTER INSERT OR UPDATE ON "event" FOR EACH ROW EXECUTE PROCEDURE
1802 "send_event_notify_trigger"();
1805 CREATE FUNCTION "delete_extended_scope_tokens_trigger"()
1806 RETURNS TRIGGER
1807 LANGUAGE 'plpgsql' VOLATILE AS $$
1808 DECLARE
1809 "system_application_row" "system_application"%ROWTYPE;
1810 BEGIN
1811 IF OLD."system_application_id" NOTNULL THEN
1812 SELECT * FROM "system_application" INTO "system_application_row"
1813 WHERE "id" = OLD."system_application_id";
1814 DELETE FROM "token"
1815 WHERE "member_id" = OLD."member_id"
1816 AND "system_application_id" = OLD."system_application_id"
1817 AND NOT COALESCE(
1818 regexp_split_to_array("scope", E'\\s+') <@
1819 regexp_split_to_array(
1820 "system_application_row"."automatic_scope", E'\\s+'
1821 ),
1822 FALSE
1823 );
1824 END IF;
1825 RETURN OLD;
1826 END;
1827 $$;
1829 CREATE TRIGGER "delete_extended_scope_tokens"
1830 BEFORE DELETE ON "member_application" FOR EACH ROW EXECUTE PROCEDURE
1831 "delete_extended_scope_tokens_trigger"();
1834 CREATE FUNCTION "detach_token_from_session_trigger"()
1835 RETURNS TRIGGER
1836 LANGUAGE 'plpgsql' VOLATILE AS $$
1837 BEGIN
1838 UPDATE "token" SET "session_id" = NULL
1839 WHERE "session_id" = OLD."id";
1840 RETURN OLD;
1841 END;
1842 $$;
1844 CREATE TRIGGER "detach_token_from_session"
1845 BEFORE DELETE ON "session" FOR EACH ROW EXECUTE PROCEDURE
1846 "detach_token_from_session_trigger"();
1849 CREATE FUNCTION "delete_non_detached_scope_with_session_trigger"()
1850 RETURNS TRIGGER
1851 LANGUAGE 'plpgsql' VOLATILE AS $$
1852 BEGIN
1853 IF NEW."session_id" ISNULL THEN
1854 SELECT coalesce(string_agg("element", ' '), '') INTO NEW."scope"
1855 FROM unnest(regexp_split_to_array(NEW."scope", E'\\s+')) AS "element"
1856 WHERE "element" LIKE '%_detached';
1857 END IF;
1858 RETURN NEW;
1859 END;
1860 $$;
1862 CREATE TRIGGER "delete_non_detached_scope_with_session"
1863 BEFORE INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
1864 "delete_non_detached_scope_with_session_trigger"();
1867 CREATE FUNCTION "delete_token_with_empty_scope_trigger"()
1868 RETURNS TRIGGER
1869 LANGUAGE 'plpgsql' VOLATILE AS $$
1870 BEGIN
1871 IF NEW."scope" = '' THEN
1872 DELETE FROM "token" WHERE "id" = NEW."id";
1873 END IF;
1874 RETURN NULL;
1875 END;
1876 $$;
1878 CREATE TRIGGER "delete_token_with_empty_scope"
1879 AFTER INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
1880 "delete_token_with_empty_scope_trigger"();
1883 CREATE FUNCTION "delete_snapshot_on_partial_delete_trigger"()
1884 RETURNS TRIGGER
1885 LANGUAGE 'plpgsql' VOLATILE AS $$
1886 BEGIN
1887 IF TG_OP = 'UPDATE' THEN
1888 IF
1889 OLD."snapshot_id" = NEW."snapshot_id" AND
1890 OLD."issue_id" = NEW."issue_id"
1891 THEN
1892 RETURN NULL;
1893 END IF;
1894 END IF;
1895 DELETE FROM "snapshot" WHERE "id" = OLD."snapshot_id";
1896 RETURN NULL;
1897 END;
1898 $$;
1900 CREATE TRIGGER "delete_snapshot_on_partial_delete"
1901 AFTER UPDATE OR DELETE ON "snapshot_issue"
1902 FOR EACH ROW EXECUTE PROCEDURE
1903 "delete_snapshot_on_partial_delete_trigger"();
1905 COMMENT ON FUNCTION "delete_snapshot_on_partial_delete_trigger"() IS 'Implementation of trigger "delete_snapshot_on_partial_delete" on table "snapshot_issue"';
1906 COMMENT ON TRIGGER "delete_snapshot_on_partial_delete" ON "snapshot_issue" IS 'Deletes whole snapshot if one issue is deleted from the snapshot';
1909 CREATE FUNCTION "copy_current_draft_data"
1910 ("initiative_id_p" "initiative"."id"%TYPE )
1911 RETURNS VOID
1912 LANGUAGE 'plpgsql' VOLATILE AS $$
1913 BEGIN
1914 PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p"
1915 FOR UPDATE;
1916 UPDATE "initiative" SET
1917 "location" = "draft"."location",
1918 "draft_text_search_data" = "draft"."text_search_data"
1919 FROM "current_draft" AS "draft"
1920 WHERE "initiative"."id" = "initiative_id_p"
1921 AND "draft"."initiative_id" = "initiative_id_p";
1922 END;
1923 $$;
1925 COMMENT ON FUNCTION "copy_current_draft_data"
1926 ( "initiative"."id"%TYPE )
1927 IS 'Helper function for function "copy_current_draft_data_trigger"';
1930 CREATE FUNCTION "copy_current_draft_data_trigger"()
1931 RETURNS TRIGGER
1932 LANGUAGE 'plpgsql' VOLATILE AS $$
1933 BEGIN
1934 IF TG_OP='DELETE' THEN
1935 PERFORM "copy_current_draft_data"(OLD."initiative_id");
1936 ELSE
1937 IF TG_OP='UPDATE' THEN
1938 IF COALESCE(OLD."inititiave_id" != NEW."initiative_id", TRUE) THEN
1939 PERFORM "copy_current_draft_data"(OLD."initiative_id");
1940 END IF;
1941 END IF;
1942 PERFORM "copy_current_draft_data"(NEW."initiative_id");
1943 END IF;
1944 RETURN NULL;
1945 END;
1946 $$;
1948 CREATE TRIGGER "copy_current_draft_data"
1949 AFTER INSERT OR UPDATE OR DELETE ON "draft"
1950 FOR EACH ROW EXECUTE PROCEDURE
1951 "copy_current_draft_data_trigger"();
1953 COMMENT ON FUNCTION "copy_current_draft_data_trigger"() IS 'Implementation of trigger "copy_current_draft_data" on table "draft"';
1954 COMMENT ON TRIGGER "copy_current_draft_data" ON "draft" IS 'Copy certain fields from most recent "draft" to "initiative"';
1957 CREATE VIEW "area_quorum" AS
1958 SELECT
1959 "area"."id" AS "area_id",
1960 ceil(
1961 "area"."quorum_standard"::FLOAT8 * "quorum_factor"::FLOAT8 ^ (
1962 coalesce(
1963 ( SELECT sum(
1964 ( extract(epoch from "area"."quorum_time")::FLOAT8 /
1965 extract(epoch from
1966 ("issue"."accepted"-"issue"."created") +
1967 "issue"."discussion_time" +
1968 "issue"."verification_time" +
1969 "issue"."voting_time"
1970 )::FLOAT8
1971 ) ^ "area"."quorum_exponent"::FLOAT8
1972 )
1973 FROM "issue" JOIN "policy"
1974 ON "issue"."policy_id" = "policy"."id"
1975 WHERE "issue"."area_id" = "area"."id"
1976 AND "issue"."accepted" NOTNULL
1977 AND "issue"."closed" ISNULL
1978 AND "policy"."polling" = FALSE
1979 )::FLOAT8, 0::FLOAT8
1980 ) / "area"."quorum_issues"::FLOAT8 - 1::FLOAT8
1981 ) * CASE WHEN "area"."quorum_den" ISNULL THEN 1 ELSE (
1982 SELECT "snapshot"."population"
1983 FROM "snapshot"
1984 WHERE "snapshot"."area_id" = "area"."id"
1985 AND "snapshot"."issue_id" ISNULL
1986 ORDER BY "snapshot"."id" DESC
1987 LIMIT 1
1988 ) END / coalesce("area"."quorum_den", 1)
1990 )::INT4 AS "issue_quorum"
1991 FROM "area";
1993 COMMENT ON VIEW "area_quorum" IS 'Area-based quorum considering number of open (accepted) issues';
1996 CREATE VIEW "area_with_unaccepted_issues" AS
1997 SELECT DISTINCT ON ("area"."id") "area".*
1998 FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id"
1999 WHERE "issue"."state" = 'admission';
2001 COMMENT ON VIEW "area_with_unaccepted_issues" IS 'All areas with unaccepted open issues (needed for issue admission system)';
2004 DROP VIEW "area_member_count";
2007 DROP TABLE "membership";
2010 DROP FUNCTION "membership_weight"
2011 ( "area_id_p" "area"."id"%TYPE,
2012 "member_id_p" "member"."id"%TYPE );
2015 DROP FUNCTION "membership_weight_with_skipping"
2016 ( "area_id_p" "area"."id"%TYPE,
2017 "member_id_p" "member"."id"%TYPE,
2018 "skip_member_ids_p" INT4[] ); -- TODO: ordering/cascade
2021 CREATE OR REPLACE VIEW "issue_delegation" AS
2022 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
2023 "issue"."id" AS "issue_id",
2024 "delegation"."id",
2025 "delegation"."truster_id",
2026 "delegation"."trustee_id",
2027 "delegation"."scope"
2028 FROM "issue"
2029 JOIN "area"
2030 ON "area"."id" = "issue"."area_id"
2031 JOIN "delegation"
2032 ON "delegation"."unit_id" = "area"."unit_id"
2033 OR "delegation"."area_id" = "area"."id"
2034 OR "delegation"."issue_id" = "issue"."id"
2035 JOIN "member"
2036 ON "delegation"."truster_id" = "member"."id"
2037 JOIN "privilege"
2038 ON "area"."unit_id" = "privilege"."unit_id"
2039 AND "delegation"."truster_id" = "privilege"."member_id"
2040 WHERE "member"."active" AND "privilege"."voting_right"
2041 ORDER BY
2042 "issue"."id",
2043 "delegation"."truster_id",
2044 "delegation"."scope" DESC;
2047 CREATE VIEW "unit_member" AS
2048 SELECT
2049 "unit"."id" AS "unit_id",
2050 "member"."id" AS "member_id"
2051 FROM "privilege"
2052 JOIN "unit" ON "unit_id" = "privilege"."unit_id"
2053 JOIN "member" ON "member"."id" = "privilege"."member_id"
2054 WHERE "privilege"."voting_right" AND "member"."active";
2056 COMMENT ON VIEW "unit_member" IS 'Active members with voting right in a unit';
2059 CREATE OR REPLACE VIEW "unit_member_count" AS
2060 SELECT
2061 "unit"."id" AS "unit_id",
2062 count("unit_member"."member_id") AS "member_count"
2063 FROM "unit" LEFT JOIN "unit_member"
2064 ON "unit"."id" = "unit_member"."unit_id"
2065 GROUP BY "unit"."id";
2067 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
2070 CREATE OR REPLACE VIEW "opening_draft" AS
2071 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
2072 ORDER BY "initiative_id", "id";
2075 CREATE OR REPLACE VIEW "current_draft" AS
2076 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
2077 ORDER BY "initiative_id", "id" DESC;
2080 CREATE OR REPLACE VIEW "issue_supporter_in_admission_state" AS
2081 SELECT
2082 "area"."unit_id",
2083 "issue"."area_id",
2084 "issue"."id" AS "issue_id",
2085 "supporter"."member_id",
2086 "direct_interest_snapshot"."weight"
2087 FROM "issue"
2088 JOIN "area" ON "area"."id" = "issue"."area_id"
2089 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
2090 JOIN "direct_interest_snapshot"
2091 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
2092 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
2093 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
2094 WHERE "issue"."state" = 'admission'::"issue_state";
2097 CREATE OR REPLACE VIEW "individual_suggestion_ranking" AS
2098 SELECT
2099 "opinion"."initiative_id",
2100 "opinion"."member_id",
2101 "direct_interest_snapshot"."weight",
2102 CASE WHEN
2103 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
2104 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
2105 THEN 1 ELSE
2106 CASE WHEN
2107 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
2108 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
2109 THEN 2 ELSE
2110 CASE WHEN
2111 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
2112 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
2113 THEN 3 ELSE 4 END
2114 END
2115 END AS "preference",
2116 "opinion"."suggestion_id"
2117 FROM "opinion"
2118 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
2119 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2120 JOIN "direct_interest_snapshot"
2121 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
2122 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
2123 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
2126 CREATE VIEW "expired_session" AS
2127 SELECT * FROM "session" WHERE now() > "expiry";
2129 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
2130 DELETE FROM "session" WHERE "id" = OLD."id";
2132 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
2133 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
2136 CREATE VIEW "expired_token" AS
2137 SELECT * FROM "token" WHERE now() > "expiry" AND NOT (
2138 "token_type" = 'authorization' AND "used" AND EXISTS (
2139 SELECT NULL FROM "token" AS "other"
2140 WHERE "other"."authorization_token_id" = "id" ) );
2142 CREATE RULE "delete" AS ON DELETE TO "expired_token" DO INSTEAD
2143 DELETE FROM "token" WHERE "id" = OLD."id";
2145 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';
2148 CREATE VIEW "unused_snapshot" AS
2149 SELECT "snapshot".* FROM "snapshot"
2150 LEFT JOIN "issue"
2151 ON "snapshot"."id" = "issue"."latest_snapshot_id"
2152 OR "snapshot"."id" = "issue"."admission_snapshot_id"
2153 OR "snapshot"."id" = "issue"."half_freeze_snapshot_id"
2154 OR "snapshot"."id" = "issue"."full_freeze_snapshot_id"
2155 WHERE "issue"."id" ISNULL;
2157 CREATE RULE "delete" AS ON DELETE TO "unused_snapshot" DO INSTEAD
2158 DELETE FROM "snapshot" WHERE "id" = OLD."id";
2160 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)';
2163 CREATE VIEW "expired_snapshot" AS
2164 SELECT "unused_snapshot".* FROM "unused_snapshot" CROSS JOIN "system_setting"
2165 WHERE "unused_snapshot"."calculated" <
2166 now() - "system_setting"."snapshot_retention";
2168 CREATE RULE "delete" AS ON DELETE TO "expired_snapshot" DO INSTEAD
2169 DELETE FROM "snapshot" WHERE "id" = OLD."id";
2171 COMMENT ON VIEW "expired_snapshot" IS 'Contains "unused_snapshot"s that are older than "system_setting"."snapshot_retention" (for deletion)';
2174 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest; for area and global delegation chains: always null';
2177 CREATE OR REPLACE FUNCTION "delegation_chain"
2178 ( "member_id_p" "member"."id"%TYPE,
2179 "unit_id_p" "unit"."id"%TYPE,
2180 "area_id_p" "area"."id"%TYPE,
2181 "issue_id_p" "issue"."id"%TYPE,
2182 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2183 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2184 RETURNS SETOF "delegation_chain_row"
2185 LANGUAGE 'plpgsql' STABLE AS $$
2186 DECLARE
2187 "scope_v" "delegation_scope";
2188 "unit_id_v" "unit"."id"%TYPE;
2189 "area_id_v" "area"."id"%TYPE;
2190 "issue_row" "issue"%ROWTYPE;
2191 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2192 "loop_member_id_v" "member"."id"%TYPE;
2193 "output_row" "delegation_chain_row";
2194 "output_rows" "delegation_chain_row"[];
2195 "simulate_v" BOOLEAN;
2196 "simulate_here_v" BOOLEAN;
2197 "delegation_row" "delegation"%ROWTYPE;
2198 "row_count" INT4;
2199 "i" INT4;
2200 "loop_v" BOOLEAN;
2201 BEGIN
2202 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
2203 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
2204 END IF;
2205 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
2206 "simulate_v" := TRUE;
2207 ELSE
2208 "simulate_v" := FALSE;
2209 END IF;
2210 IF
2211 "unit_id_p" NOTNULL AND
2212 "area_id_p" ISNULL AND
2213 "issue_id_p" ISNULL
2214 THEN
2215 "scope_v" := 'unit';
2216 "unit_id_v" := "unit_id_p";
2217 ELSIF
2218 "unit_id_p" ISNULL AND
2219 "area_id_p" NOTNULL AND
2220 "issue_id_p" ISNULL
2221 THEN
2222 "scope_v" := 'area';
2223 "area_id_v" := "area_id_p";
2224 SELECT "unit_id" INTO "unit_id_v"
2225 FROM "area" WHERE "id" = "area_id_v";
2226 ELSIF
2227 "unit_id_p" ISNULL AND
2228 "area_id_p" ISNULL AND
2229 "issue_id_p" NOTNULL
2230 THEN
2231 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
2232 IF "issue_row"."id" ISNULL THEN
2233 RETURN;
2234 END IF;
2235 IF "issue_row"."closed" NOTNULL THEN
2236 IF "simulate_v" THEN
2237 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
2238 END IF;
2239 FOR "output_row" IN
2240 SELECT * FROM
2241 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
2242 LOOP
2243 RETURN NEXT "output_row";
2244 END LOOP;
2245 RETURN;
2246 END IF;
2247 "scope_v" := 'issue';
2248 SELECT "area_id" INTO "area_id_v"
2249 FROM "issue" WHERE "id" = "issue_id_p";
2250 SELECT "unit_id" INTO "unit_id_v"
2251 FROM "area" WHERE "id" = "area_id_v";
2252 ELSE
2253 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2254 END IF;
2255 "visited_member_ids" := '{}';
2256 "loop_member_id_v" := NULL;
2257 "output_rows" := '{}';
2258 "output_row"."index" := 0;
2259 "output_row"."member_id" := "member_id_p";
2260 "output_row"."member_valid" := TRUE;
2261 "output_row"."participation" := FALSE;
2262 "output_row"."overridden" := FALSE;
2263 "output_row"."disabled_out" := FALSE;
2264 "output_row"."scope_out" := NULL;
2265 LOOP
2266 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2267 "loop_member_id_v" := "output_row"."member_id";
2268 ELSE
2269 "visited_member_ids" :=
2270 "visited_member_ids" || "output_row"."member_id";
2271 END IF;
2272 IF "output_row"."participation" ISNULL THEN
2273 "output_row"."overridden" := NULL;
2274 ELSIF "output_row"."participation" THEN
2275 "output_row"."overridden" := TRUE;
2276 END IF;
2277 "output_row"."scope_in" := "output_row"."scope_out";
2278 "output_row"."member_valid" := EXISTS (
2279 SELECT NULL FROM "member" JOIN "privilege"
2280 ON "privilege"."member_id" = "member"."id"
2281 AND "privilege"."unit_id" = "unit_id_v"
2282 WHERE "id" = "output_row"."member_id"
2283 AND "member"."active" AND "privilege"."voting_right"
2284 );
2285 "simulate_here_v" := (
2286 "simulate_v" AND
2287 "output_row"."member_id" = "member_id_p"
2288 );
2289 "delegation_row" := ROW(NULL);
2290 IF "output_row"."member_valid" OR "simulate_here_v" THEN
2291 IF "scope_v" = 'unit' THEN
2292 IF NOT "simulate_here_v" THEN
2293 SELECT * INTO "delegation_row" FROM "delegation"
2294 WHERE "truster_id" = "output_row"."member_id"
2295 AND "unit_id" = "unit_id_v";
2296 END IF;
2297 ELSIF "scope_v" = 'area' THEN
2298 IF "simulate_here_v" THEN
2299 IF "simulate_trustee_id_p" ISNULL THEN
2300 SELECT * INTO "delegation_row" FROM "delegation"
2301 WHERE "truster_id" = "output_row"."member_id"
2302 AND "unit_id" = "unit_id_v";
2303 END IF;
2304 ELSE
2305 SELECT * INTO "delegation_row" FROM "delegation"
2306 WHERE "truster_id" = "output_row"."member_id"
2307 AND (
2308 "unit_id" = "unit_id_v" OR
2309 "area_id" = "area_id_v"
2310 )
2311 ORDER BY "scope" DESC;
2312 END IF;
2313 ELSIF "scope_v" = 'issue' THEN
2314 IF "issue_row"."fully_frozen" ISNULL THEN
2315 "output_row"."participation" := EXISTS (
2316 SELECT NULL FROM "interest"
2317 WHERE "issue_id" = "issue_id_p"
2318 AND "member_id" = "output_row"."member_id"
2319 );
2320 ELSE
2321 IF "output_row"."member_id" = "member_id_p" THEN
2322 "output_row"."participation" := EXISTS (
2323 SELECT NULL FROM "direct_voter"
2324 WHERE "issue_id" = "issue_id_p"
2325 AND "member_id" = "output_row"."member_id"
2326 );
2327 ELSE
2328 "output_row"."participation" := NULL;
2329 END IF;
2330 END IF;
2331 IF "simulate_here_v" THEN
2332 IF "simulate_trustee_id_p" ISNULL THEN
2333 SELECT * INTO "delegation_row" FROM "delegation"
2334 WHERE "truster_id" = "output_row"."member_id"
2335 AND (
2336 "unit_id" = "unit_id_v" OR
2337 "area_id" = "area_id_v"
2338 )
2339 ORDER BY "scope" DESC;
2340 END IF;
2341 ELSE
2342 SELECT * INTO "delegation_row" FROM "delegation"
2343 WHERE "truster_id" = "output_row"."member_id"
2344 AND (
2345 "unit_id" = "unit_id_v" OR
2346 "area_id" = "area_id_v" OR
2347 "issue_id" = "issue_id_p"
2348 )
2349 ORDER BY "scope" DESC;
2350 END IF;
2351 END IF;
2352 ELSE
2353 "output_row"."participation" := FALSE;
2354 END IF;
2355 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
2356 "output_row"."scope_out" := "scope_v";
2357 "output_rows" := "output_rows" || "output_row";
2358 "output_row"."member_id" := "simulate_trustee_id_p";
2359 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2360 "output_row"."scope_out" := "delegation_row"."scope";
2361 "output_rows" := "output_rows" || "output_row";
2362 "output_row"."member_id" := "delegation_row"."trustee_id";
2363 ELSIF "delegation_row"."scope" NOTNULL THEN
2364 "output_row"."scope_out" := "delegation_row"."scope";
2365 "output_row"."disabled_out" := TRUE;
2366 "output_rows" := "output_rows" || "output_row";
2367 EXIT;
2368 ELSE
2369 "output_row"."scope_out" := NULL;
2370 "output_rows" := "output_rows" || "output_row";
2371 EXIT;
2372 END IF;
2373 EXIT WHEN "loop_member_id_v" NOTNULL;
2374 "output_row"."index" := "output_row"."index" + 1;
2375 END LOOP;
2376 "row_count" := array_upper("output_rows", 1);
2377 "i" := 1;
2378 "loop_v" := FALSE;
2379 LOOP
2380 "output_row" := "output_rows"["i"];
2381 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2382 IF "loop_v" THEN
2383 IF "i" + 1 = "row_count" THEN
2384 "output_row"."loop" := 'last';
2385 ELSIF "i" = "row_count" THEN
2386 "output_row"."loop" := 'repetition';
2387 ELSE
2388 "output_row"."loop" := 'intermediate';
2389 END IF;
2390 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2391 "output_row"."loop" := 'first';
2392 "loop_v" := TRUE;
2393 END IF;
2394 IF "scope_v" = 'unit' THEN
2395 "output_row"."participation" := NULL;
2396 END IF;
2397 RETURN NEXT "output_row";
2398 "i" := "i" + 1;
2399 END LOOP;
2400 RETURN;
2401 END;
2402 $$;
2405 CREATE OR REPLACE FUNCTION "get_initiatives_for_notification"
2406 ( "recipient_id_p" "member"."id"%TYPE )
2407 RETURNS SETOF "initiative_for_notification"
2408 LANGUAGE 'plpgsql' VOLATILE AS $$
2409 DECLARE
2410 "result_row" "initiative_for_notification"%ROWTYPE;
2411 "last_draft_id_v" "draft"."id"%TYPE;
2412 "last_suggestion_id_v" "suggestion"."id"%TYPE;
2413 BEGIN
2414 PERFORM "require_transaction_isolation"();
2415 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
2416 FOR "result_row" IN
2417 SELECT * FROM "initiative_for_notification"
2418 WHERE "recipient_id" = "recipient_id_p"
2419 LOOP
2420 SELECT "id" INTO "last_draft_id_v" FROM "draft"
2421 WHERE "draft"."initiative_id" = "result_row"."initiative_id"
2422 ORDER BY "id" DESC LIMIT 1;
2423 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
2424 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
2425 ORDER BY "id" DESC LIMIT 1;
2426 INSERT INTO "notification_initiative_sent"
2427 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
2428 VALUES (
2429 "recipient_id_p",
2430 "result_row"."initiative_id",
2431 "last_draft_id_v",
2432 "last_suggestion_id_v" )
2433 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
2434 "last_draft_id" = "last_draft_id_v",
2435 "last_suggestion_id" = "last_suggestion_id_v";
2436 RETURN NEXT "result_row";
2437 END LOOP;
2438 DELETE FROM "notification_initiative_sent"
2439 USING "initiative", "issue"
2440 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
2441 AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
2442 AND "issue"."id" = "initiative"."issue_id"
2443 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
2444 UPDATE "member" SET
2445 "notification_counter" = "notification_counter" + 1,
2446 "notification_sent" = now()
2447 WHERE "id" = "recipient_id_p";
2448 RETURN;
2449 END;
2450 $$;
2453 CREATE OR REPLACE FUNCTION "calculate_member_counts"()
2454 RETURNS VOID
2455 LANGUAGE 'plpgsql' VOLATILE AS $$
2456 BEGIN
2457 PERFORM "require_transaction_isolation"();
2458 DELETE FROM "member_count";
2459 INSERT INTO "member_count" ("total_count")
2460 SELECT "total_count" FROM "member_count_view";
2461 UPDATE "unit" SET "member_count" = "view"."member_count"
2462 FROM "unit_member_count" AS "view"
2463 WHERE "view"."unit_id" = "unit"."id";
2464 RETURN;
2465 END;
2466 $$;
2468 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"';
2471 CREATE FUNCTION "calculate_area_quorum"()
2472 RETURNS VOID
2473 LANGUAGE 'plpgsql' VOLATILE AS $$
2474 BEGIN
2475 PERFORM "dont_require_transaction_isolation"();
2476 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
2477 FROM "area_quorum" AS "view"
2478 WHERE "view"."area_id" = "area"."id";
2479 RETURN;
2480 END;
2481 $$;
2483 COMMENT ON FUNCTION "calculate_area_quorum"() IS 'Calculate column "issue_quorum" in table "area" from view "area_quorum"';
2486 DROP VIEW "remaining_harmonic_initiative_weight_summands";
2487 DROP VIEW "remaining_harmonic_supporter_weight";
2490 CREATE VIEW "remaining_harmonic_supporter_weight" AS
2491 SELECT
2492 "direct_interest_snapshot"."snapshot_id",
2493 "direct_interest_snapshot"."issue_id",
2494 "direct_interest_snapshot"."member_id",
2495 "direct_interest_snapshot"."weight" AS "weight_num",
2496 count("initiative"."id") AS "weight_den"
2497 FROM "issue"
2498 JOIN "direct_interest_snapshot"
2499 ON "issue"."latest_snapshot_id" = "direct_interest_snapshot"."snapshot_id"
2500 AND "issue"."id" = "direct_interest_snapshot"."issue_id"
2501 JOIN "initiative"
2502 ON "issue"."id" = "initiative"."issue_id"
2503 AND "initiative"."harmonic_weight" ISNULL
2504 JOIN "direct_supporter_snapshot"
2505 ON "issue"."latest_snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
2506 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
2507 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
2508 AND (
2509 "direct_supporter_snapshot"."satisfied" = TRUE OR
2510 coalesce("initiative"."admitted", FALSE) = FALSE
2511 )
2512 GROUP BY
2513 "direct_interest_snapshot"."snapshot_id",
2514 "direct_interest_snapshot"."issue_id",
2515 "direct_interest_snapshot"."member_id",
2516 "direct_interest_snapshot"."weight";
2519 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
2520 SELECT
2521 "initiative"."issue_id",
2522 "initiative"."id" AS "initiative_id",
2523 "initiative"."admitted",
2524 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
2525 "remaining_harmonic_supporter_weight"."weight_den"
2526 FROM "remaining_harmonic_supporter_weight"
2527 JOIN "initiative"
2528 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
2529 AND "initiative"."harmonic_weight" ISNULL
2530 JOIN "direct_supporter_snapshot"
2531 ON "remaining_harmonic_supporter_weight"."snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
2532 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
2533 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
2534 AND (
2535 "direct_supporter_snapshot"."satisfied" = TRUE OR
2536 coalesce("initiative"."admitted", FALSE) = FALSE
2537 )
2538 GROUP BY
2539 "initiative"."issue_id",
2540 "initiative"."id",
2541 "initiative"."admitted",
2542 "remaining_harmonic_supporter_weight"."weight_den";
2545 DROP FUNCTION "create_population_snapshot"
2546 ( "issue_id_p" "issue"."id"%TYPE );
2549 DROP FUNCTION "weight_of_added_delegations_for_population_snapshot"
2550 ( "issue_id_p" "issue"."id"%TYPE,
2551 "member_id_p" "member"."id"%TYPE,
2552 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE );
2555 DROP FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2556 ( "issue_id_p" "issue"."id"%TYPE,
2557 "member_id_p" "member"."id"%TYPE,
2558 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE );
2561 CREATE FUNCTION "weight_of_added_delegations_for_snapshot"
2562 ( "snapshot_id_p" "snapshot"."id"%TYPE,
2563 "issue_id_p" "issue"."id"%TYPE,
2564 "member_id_p" "member"."id"%TYPE,
2565 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2566 RETURNS "direct_interest_snapshot"."weight"%TYPE
2567 LANGUAGE 'plpgsql' VOLATILE AS $$
2568 DECLARE
2569 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2570 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2571 "weight_v" INT4;
2572 "sub_weight_v" INT4;
2573 BEGIN
2574 PERFORM "require_transaction_isolation"();
2575 "weight_v" := 0;
2576 FOR "issue_delegation_row" IN
2577 SELECT * FROM "issue_delegation"
2578 WHERE "trustee_id" = "member_id_p"
2579 AND "issue_id" = "issue_id_p"
2580 LOOP
2581 IF NOT EXISTS (
2582 SELECT NULL FROM "direct_interest_snapshot"
2583 WHERE "snapshot_id" = "snapshot_id_p"
2584 AND "issue_id" = "issue_id_p"
2585 AND "member_id" = "issue_delegation_row"."truster_id"
2586 ) AND NOT EXISTS (
2587 SELECT NULL FROM "delegating_interest_snapshot"
2588 WHERE "snapshot_id" = "snapshot_id_p"
2589 AND "issue_id" = "issue_id_p"
2590 AND "member_id" = "issue_delegation_row"."truster_id"
2591 ) THEN
2592 "delegate_member_ids_v" :=
2593 "member_id_p" || "delegate_member_ids_p";
2594 INSERT INTO "delegating_interest_snapshot" (
2595 "snapshot_id",
2596 "issue_id",
2597 "member_id",
2598 "scope",
2599 "delegate_member_ids"
2600 ) VALUES (
2601 "snapshot_id_p",
2602 "issue_id_p",
2603 "issue_delegation_row"."truster_id",
2604 "issue_delegation_row"."scope",
2605 "delegate_member_ids_v"
2606 );
2607 "sub_weight_v" := 1 +
2608 "weight_of_added_delegations_for_snapshot"(
2609 "snapshot_id_p",
2610 "issue_id_p",
2611 "issue_delegation_row"."truster_id",
2612 "delegate_member_ids_v"
2613 );
2614 UPDATE "delegating_interest_snapshot"
2615 SET "weight" = "sub_weight_v"
2616 WHERE "snapshot_id" = "snapshot_id_p"
2617 AND "issue_id" = "issue_id_p"
2618 AND "member_id" = "issue_delegation_row"."truster_id";
2619 "weight_v" := "weight_v" + "sub_weight_v";
2620 END IF;
2621 END LOOP;
2622 RETURN "weight_v";
2623 END;
2624 $$;
2626 COMMENT ON FUNCTION "weight_of_added_delegations_for_snapshot"
2627 ( "snapshot"."id"%TYPE,
2628 "issue"."id"%TYPE,
2629 "member"."id"%TYPE,
2630 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2631 IS 'Helper function for "fill_snapshot" function';
2634 DROP FUNCTION "create_interest_snapshot"
2635 ( "issue_id_p" "issue"."id"%TYPE );
2638 DROP FUNCTION "create_snapshot"
2639 ( "issue_id_p" "issue"."id"%TYPE );
2642 CREATE FUNCTION "take_snapshot"
2643 ( "issue_id_p" "issue"."id"%TYPE,
2644 "area_id_p" "area"."id"%TYPE = NULL )
2645 RETURNS "snapshot"."id"%TYPE
2646 LANGUAGE 'plpgsql' VOLATILE AS $$
2647 DECLARE
2648 "area_id_v" "area"."id"%TYPE;
2649 "unit_id_v" "unit"."id"%TYPE;
2650 "snapshot_id_v" "snapshot"."id"%TYPE;
2651 "issue_id_v" "issue"."id"%TYPE;
2652 "member_id_v" "member"."id"%TYPE;
2653 BEGIN
2654 IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN
2655 RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL';
2656 END IF;
2657 PERFORM "require_transaction_isolation"();
2658 IF "issue_id_p" ISNULL THEN
2659 "area_id_v" := "area_id_p";
2660 ELSE
2661 SELECT "area_id" INTO "area_id_v"
2662 FROM "issue" WHERE "id" = "issue_id_p";
2663 END IF;
2664 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_p";
2665 INSERT INTO "snapshot" ("area_id", "issue_id")
2666 VALUES ("area_id_v", "issue_id_p")
2667 RETURNING "id" INTO "snapshot_id_v";
2668 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
2669 SELECT "snapshot_id_v", "member_id"
2670 FROM "unit_member" WHERE "unit_id" = "unit_id_v";
2671 UPDATE "snapshot" SET
2672 "population" = (
2673 SELECT count(1) FROM "snapshot_population"
2674 WHERE "snapshot_id" = "snapshot_id_v"
2675 ) WHERE "id" = "snapshot_id_v";
2676 FOR "issue_id_v" IN
2677 SELECT "id" FROM "issue"
2678 WHERE CASE WHEN "issue_id_p" ISNULL THEN
2679 "area_id" = "area_id_p" AND
2680 "state" = 'admission'
2681 ELSE
2682 "id" = "issue_id_p"
2683 END
2684 LOOP
2685 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
2686 VALUES ("snapshot_id_v", "issue_id_v");
2687 INSERT INTO "direct_interest_snapshot"
2688 ("snapshot_id", "issue_id", "member_id")
2689 SELECT
2690 "snapshot_id_v" AS "snapshot_id",
2691 "issue_id_v" AS "issue_id",
2692 "member"."id" AS "member_id"
2693 FROM "issue"
2694 JOIN "area" ON "issue"."area_id" = "area"."id"
2695 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2696 JOIN "member" ON "interest"."member_id" = "member"."id"
2697 JOIN "privilege"
2698 ON "privilege"."unit_id" = "area"."unit_id"
2699 AND "privilege"."member_id" = "member"."id"
2700 WHERE "issue"."id" = "issue_id_v"
2701 AND "member"."active" AND "privilege"."voting_right";
2702 FOR "member_id_v" IN
2703 SELECT "member_id" FROM "direct_interest_snapshot"
2704 WHERE "snapshot_id" = "snapshot_id_v"
2705 AND "issue_id" = "issue_id_v"
2706 LOOP
2707 UPDATE "direct_interest_snapshot" SET
2708 "weight" = 1 +
2709 "weight_of_added_delegations_for_snapshot"(
2710 "snapshot_id_v",
2711 "issue_id_v",
2712 "member_id_v",
2713 '{}'
2714 )
2715 WHERE "snapshot_id" = "snapshot_id_v"
2716 AND "issue_id" = "issue_id_v"
2717 AND "member_id" = "member_id_v";
2718 END LOOP;
2719 INSERT INTO "direct_supporter_snapshot"
2720 ( "snapshot_id", "issue_id", "initiative_id", "member_id",
2721 "draft_id", "informed", "satisfied" )
2722 SELECT
2723 "snapshot_id_v" AS "snapshot_id",
2724 "issue_id_v" AS "issue_id",
2725 "initiative"."id" AS "initiative_id",
2726 "supporter"."member_id" AS "member_id",
2727 "supporter"."draft_id" AS "draft_id",
2728 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2729 NOT EXISTS (
2730 SELECT NULL FROM "critical_opinion"
2731 WHERE "initiative_id" = "initiative"."id"
2732 AND "member_id" = "supporter"."member_id"
2733 ) AS "satisfied"
2734 FROM "initiative"
2735 JOIN "supporter"
2736 ON "supporter"."initiative_id" = "initiative"."id"
2737 JOIN "current_draft"
2738 ON "initiative"."id" = "current_draft"."initiative_id"
2739 JOIN "direct_interest_snapshot"
2740 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"
2741 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"
2742 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
2743 WHERE "initiative"."issue_id" = "issue_id_v";
2744 DELETE FROM "temporary_suggestion_counts";
2745 INSERT INTO "temporary_suggestion_counts"
2746 ( "id",
2747 "minus2_unfulfilled_count", "minus2_fulfilled_count",
2748 "minus1_unfulfilled_count", "minus1_fulfilled_count",
2749 "plus1_unfulfilled_count", "plus1_fulfilled_count",
2750 "plus2_unfulfilled_count", "plus2_fulfilled_count" )
2751 SELECT
2752 "suggestion"."id",
2753 ( SELECT coalesce(sum("di"."weight"), 0)
2754 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2755 ON "di"."snapshot_id" = "snapshot_id_v"
2756 AND "di"."issue_id" = "issue_id_v"
2757 AND "di"."member_id" = "opinion"."member_id"
2758 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2759 AND "opinion"."degree" = -2
2760 AND "opinion"."fulfilled" = FALSE
2761 ) AS "minus2_unfulfilled_count",
2762 ( SELECT coalesce(sum("di"."weight"), 0)
2763 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2764 ON "di"."snapshot_id" = "snapshot_id_v"
2765 AND "di"."issue_id" = "issue_id_v"
2766 AND "di"."member_id" = "opinion"."member_id"
2767 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2768 AND "opinion"."degree" = -2
2769 AND "opinion"."fulfilled" = TRUE
2770 ) AS "minus2_fulfilled_count",
2771 ( SELECT coalesce(sum("di"."weight"), 0)
2772 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2773 ON "di"."snapshot_id" = "snapshot_id_v"
2774 AND "di"."issue_id" = "issue_id_v"
2775 AND "di"."member_id" = "opinion"."member_id"
2776 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2777 AND "opinion"."degree" = -1
2778 AND "opinion"."fulfilled" = FALSE
2779 ) AS "minus1_unfulfilled_count",
2780 ( SELECT coalesce(sum("di"."weight"), 0)
2781 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2782 ON "di"."snapshot_id" = "snapshot_id_v"
2783 AND "di"."issue_id" = "issue_id_v"
2784 AND "di"."member_id" = "opinion"."member_id"
2785 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2786 AND "opinion"."degree" = -1
2787 AND "opinion"."fulfilled" = TRUE
2788 ) AS "minus1_fulfilled_count",
2789 ( SELECT coalesce(sum("di"."weight"), 0)
2790 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2791 ON "di"."snapshot_id" = "snapshot_id_v"
2792 AND "di"."issue_id" = "issue_id_v"
2793 AND "di"."member_id" = "opinion"."member_id"
2794 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2795 AND "opinion"."degree" = 1
2796 AND "opinion"."fulfilled" = FALSE
2797 ) AS "plus1_unfulfilled_count",
2798 ( SELECT coalesce(sum("di"."weight"), 0)
2799 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2800 ON "di"."snapshot_id" = "snapshot_id_v"
2801 AND "di"."issue_id" = "issue_id_v"
2802 AND "di"."member_id" = "opinion"."member_id"
2803 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2804 AND "opinion"."degree" = 1
2805 AND "opinion"."fulfilled" = TRUE
2806 ) AS "plus1_fulfilled_count",
2807 ( SELECT coalesce(sum("di"."weight"), 0)
2808 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2809 ON "di"."snapshot_id" = "snapshot_id_v"
2810 AND "di"."issue_id" = "issue_id_v"
2811 AND "di"."member_id" = "opinion"."member_id"
2812 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2813 AND "opinion"."degree" = 2
2814 AND "opinion"."fulfilled" = FALSE
2815 ) AS "plus2_unfulfilled_count",
2816 ( SELECT coalesce(sum("di"."weight"), 0)
2817 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2818 ON "di"."snapshot_id" = "snapshot_id_v"
2819 AND "di"."issue_id" = "issue_id_v"
2820 AND "di"."member_id" = "opinion"."member_id"
2821 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2822 AND "opinion"."degree" = 2
2823 AND "opinion"."fulfilled" = TRUE
2824 ) AS "plus2_fulfilled_count"
2825 FROM "suggestion" JOIN "initiative"
2826 ON "suggestion"."initiative_id" = "initiative"."id"
2827 WHERE "initiative"."issue_id" = "issue_id_v";
2828 END LOOP;
2829 RETURN "snapshot_id_v";
2830 END;
2831 $$;
2833 COMMENT ON FUNCTION "take_snapshot"
2834 ( "issue"."id"%TYPE,
2835 "area"."id"%TYPE )
2836 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.';
2839 DROP FUNCTION "set_snapshot_event"
2840 ( "issue_id_p" "issue"."id"%TYPE,
2841 "event_p" "snapshot_event" );
2844 CREATE FUNCTION "finish_snapshot"
2845 ( "issue_id_p" "issue"."id"%TYPE )
2846 RETURNS VOID
2847 LANGUAGE 'plpgsql' VOLATILE AS $$
2848 DECLARE
2849 "snapshot_id_v" "snapshot"."id"%TYPE;
2850 BEGIN
2851 -- NOTE: function does not require snapshot isolation but we don't call
2852 -- "dont_require_snapshot_isolation" here because this function is
2853 -- also invoked by "check_issue"
2854 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
2855 SELECT "id" INTO "snapshot_id_v" FROM "snapshot"
2856 ORDER BY "id" DESC LIMIT 1;
2857 UPDATE "issue" SET
2858 "calculated" = "snapshot"."calculated",
2859 "latest_snapshot_id" = "snapshot_id_v",
2860 "population" = "snapshot"."population"
2861 FROM "snapshot"
2862 WHERE "issue"."id" = "issue_id_p"
2863 AND "snapshot"."id" = "snapshot_id_v";
2864 UPDATE "initiative" SET
2865 "supporter_count" = (
2866 SELECT coalesce(sum("di"."weight"), 0)
2867 FROM "direct_interest_snapshot" AS "di"
2868 JOIN "direct_supporter_snapshot" AS "ds"
2869 ON "di"."member_id" = "ds"."member_id"
2870 WHERE "di"."snapshot_id" = "snapshot_id_v"
2871 AND "di"."issue_id" = "issue_id_p"
2872 AND "ds"."snapshot_id" = "snapshot_id_v"
2873 AND "ds"."initiative_id" = "initiative"."id"
2874 ),
2875 "informed_supporter_count" = (
2876 SELECT coalesce(sum("di"."weight"), 0)
2877 FROM "direct_interest_snapshot" AS "di"
2878 JOIN "direct_supporter_snapshot" AS "ds"
2879 ON "di"."member_id" = "ds"."member_id"
2880 WHERE "di"."snapshot_id" = "snapshot_id_v"
2881 AND "di"."issue_id" = "issue_id_p"
2882 AND "ds"."snapshot_id" = "snapshot_id_v"
2883 AND "ds"."initiative_id" = "initiative"."id"
2884 AND "ds"."informed"
2885 ),
2886 "satisfied_supporter_count" = (
2887 SELECT coalesce(sum("di"."weight"), 0)
2888 FROM "direct_interest_snapshot" AS "di"
2889 JOIN "direct_supporter_snapshot" AS "ds"
2890 ON "di"."member_id" = "ds"."member_id"
2891 WHERE "di"."snapshot_id" = "snapshot_id_v"
2892 AND "di"."issue_id" = "issue_id_p"
2893 AND "ds"."snapshot_id" = "snapshot_id_v"
2894 AND "ds"."initiative_id" = "initiative"."id"
2895 AND "ds"."satisfied"
2896 ),
2897 "satisfied_informed_supporter_count" = (
2898 SELECT coalesce(sum("di"."weight"), 0)
2899 FROM "direct_interest_snapshot" AS "di"
2900 JOIN "direct_supporter_snapshot" AS "ds"
2901 ON "di"."member_id" = "ds"."member_id"
2902 WHERE "di"."snapshot_id" = "snapshot_id_v"
2903 AND "di"."issue_id" = "issue_id_p"
2904 AND "ds"."snapshot_id" = "snapshot_id_v"
2905 AND "ds"."initiative_id" = "initiative"."id"
2906 AND "ds"."informed"
2907 AND "ds"."satisfied"
2908 )
2909 WHERE "issue_id" = "issue_id_p";
2910 UPDATE "suggestion" SET
2911 "minus2_unfulfilled_count" = "temp"."minus2_unfulfilled_count",
2912 "minus2_fulfilled_count" = "temp"."minus2_fulfilled_count",
2913 "minus1_unfulfilled_count" = "temp"."minus1_unfulfilled_count",
2914 "minus1_fulfilled_count" = "temp"."minus1_fulfilled_count",
2915 "plus1_unfulfilled_count" = "temp"."plus1_unfulfilled_count",
2916 "plus1_fulfilled_count" = "temp"."plus1_fulfilled_count",
2917 "plus2_unfulfilled_count" = "temp"."plus2_unfulfilled_count",
2918 "plus2_fulfilled_count" = "temp"."plus2_fulfilled_count"
2919 FROM "temporary_suggestion_counts" AS "temp", "initiative"
2920 WHERE "temp"."id" = "suggestion"."id"
2921 AND "initiative"."issue_id" = "issue_id_p"
2922 AND "suggestion"."initiative_id" = "initiative"."id";
2923 DELETE FROM "temporary_suggestion_counts";
2924 RETURN;
2925 END;
2926 $$;
2928 COMMENT ON FUNCTION "finish_snapshot"
2929 ( "issue"."id"%TYPE )
2930 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)';
2933 CREATE FUNCTION "issue_admission"
2934 ( "area_id_p" "area"."id"%TYPE )
2935 RETURNS BOOLEAN
2936 LANGUAGE 'plpgsql' VOLATILE AS $$
2937 DECLARE
2938 "issue_id_v" "issue"."id"%TYPE;
2939 BEGIN
2940 PERFORM "dont_require_transaction_isolation"();
2941 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
2942 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
2943 FROM "area_quorum" AS "view"
2944 WHERE "area"."id" = "view"."area_id"
2945 AND "area"."id" = "area_id_p";
2946 SELECT "id" INTO "issue_id_v" FROM "issue_for_admission"
2947 WHERE "area_id" = "area_id_p";
2948 IF "issue_id_v" ISNULL THEN RETURN FALSE; END IF;
2949 UPDATE "issue" SET
2950 "admission_snapshot_id" = "latest_snapshot_id",
2951 "state" = 'discussion',
2952 "accepted" = now(),
2953 "phase_finished" = NULL
2954 WHERE "id" = "issue_id_v";
2955 RETURN TRUE;
2956 END;
2957 $$;
2959 COMMENT ON FUNCTION "issue_admission"
2960 ( "area"."id"%TYPE )
2961 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';
2964 CREATE OR REPLACE FUNCTION "check_issue"
2965 ( "issue_id_p" "issue"."id"%TYPE,
2966 "persist" "check_issue_persistence" )
2967 RETURNS "check_issue_persistence"
2968 LANGUAGE 'plpgsql' VOLATILE AS $$
2969 DECLARE
2970 "issue_row" "issue"%ROWTYPE;
2971 "last_calculated_v" "snapshot"."calculated"%TYPE;
2972 "policy_row" "policy"%ROWTYPE;
2973 "initiative_row" "initiative"%ROWTYPE;
2974 "state_v" "issue_state";
2975 BEGIN
2976 PERFORM "require_transaction_isolation"();
2977 IF "persist" ISNULL THEN
2978 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
2979 FOR UPDATE;
2980 SELECT "calculated" INTO "last_calculated_v"
2981 FROM "snapshot" JOIN "snapshot_issue"
2982 ON "snapshot"."id" = "snapshot_issue"."snapshot_id"
2983 WHERE "snapshot_issue"."issue_id" = "issue_id_p";
2984 IF "issue_row"."closed" NOTNULL THEN
2985 RETURN NULL;
2986 END IF;
2987 "persist"."state" := "issue_row"."state";
2988 IF
2989 ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=
2990 "issue_row"."created" + "issue_row"."max_admission_time" ) OR
2991 ( "issue_row"."state" = 'discussion' AND now() >=
2992 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
2993 ( "issue_row"."state" = 'verification' AND now() >=
2994 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
2995 ( "issue_row"."state" = 'voting' AND now() >=
2996 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
2997 THEN
2998 "persist"."phase_finished" := TRUE;
2999 ELSE
3000 "persist"."phase_finished" := FALSE;
3001 END IF;
3002 IF
3003 NOT EXISTS (
3004 -- all initiatives are revoked
3005 SELECT NULL FROM "initiative"
3006 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3007 ) AND (
3008 -- and issue has not been accepted yet
3009 "persist"."state" = 'admission' OR
3010 -- or verification time has elapsed
3011 ( "persist"."state" = 'verification' AND
3012 "persist"."phase_finished" ) OR
3013 -- or no initiatives have been revoked lately
3014 NOT EXISTS (
3015 SELECT NULL FROM "initiative"
3016 WHERE "issue_id" = "issue_id_p"
3017 AND now() < "revoked" + "issue_row"."verification_time"
3018 )
3019 )
3020 THEN
3021 "persist"."issue_revoked" := TRUE;
3022 ELSE
3023 "persist"."issue_revoked" := FALSE;
3024 END IF;
3025 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
3026 UPDATE "issue" SET "phase_finished" = now()
3027 WHERE "id" = "issue_row"."id";
3028 RETURN "persist";
3029 ELSIF
3030 "persist"."state" IN ('admission', 'discussion', 'verification')
3031 THEN
3032 RETURN "persist";
3033 ELSE
3034 RETURN NULL;
3035 END IF;
3036 END IF;
3037 IF
3038 "persist"."state" IN ('admission', 'discussion', 'verification') AND
3039 coalesce("persist"."snapshot_created", FALSE) = FALSE
3040 THEN
3041 IF "persist"."state" != 'admission' THEN
3042 PERFORM "take_snapshot"("issue_id_p");
3043 PERFORM "finish_snapshot"("issue_id_p");
3044 END IF;
3045 "persist"."snapshot_created" = TRUE;
3046 IF "persist"."phase_finished" THEN
3047 IF "persist"."state" = 'admission' THEN
3048 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id";
3049 ELSIF "persist"."state" = 'discussion' THEN
3050 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id";
3051 ELSIF "persist"."state" = 'verification' THEN
3052 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id";
3053 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3054 SELECT * INTO "policy_row" FROM "policy"
3055 WHERE "id" = "issue_row"."policy_id";
3056 FOR "initiative_row" IN
3057 SELECT * FROM "initiative"
3058 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3059 FOR UPDATE
3060 LOOP
3061 IF
3062 "initiative_row"."polling" OR (
3063 "initiative_row"."satisfied_supporter_count" >
3064 "policy_row"."initiative_quorum" AND
3065 "initiative_row"."satisfied_supporter_count" *
3066 "policy_row"."initiative_quorum_den" >=
3067 "issue_row"."population" * "policy_row"."initiative_quorum_num"
3068 )
3069 THEN
3070 UPDATE "initiative" SET "admitted" = TRUE
3071 WHERE "id" = "initiative_row"."id";
3072 ELSE
3073 UPDATE "initiative" SET "admitted" = FALSE
3074 WHERE "id" = "initiative_row"."id";
3075 END IF;
3076 END LOOP;
3077 END IF;
3078 END IF;
3079 RETURN "persist";
3080 END IF;
3081 IF
3082 "persist"."state" IN ('admission', 'discussion', 'verification') AND
3083 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
3084 THEN
3085 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
3086 "persist"."harmonic_weights_set" = TRUE;
3087 IF
3088 "persist"."phase_finished" OR
3089 "persist"."issue_revoked" OR
3090 "persist"."state" = 'admission'
3091 THEN
3092 RETURN "persist";
3093 ELSE
3094 RETURN NULL;
3095 END IF;
3096 END IF;
3097 IF "persist"."issue_revoked" THEN
3098 IF "persist"."state" = 'admission' THEN
3099 "state_v" := 'canceled_revoked_before_accepted';
3100 ELSIF "persist"."state" = 'discussion' THEN
3101 "state_v" := 'canceled_after_revocation_during_discussion';
3102 ELSIF "persist"."state" = 'verification' THEN
3103 "state_v" := 'canceled_after_revocation_during_verification';
3104 END IF;
3105 UPDATE "issue" SET
3106 "state" = "state_v",
3107 "closed" = "phase_finished",
3108 "phase_finished" = NULL
3109 WHERE "id" = "issue_id_p";
3110 RETURN NULL;
3111 END IF;
3112 IF "persist"."state" = 'admission' THEN
3113 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
3114 FOR UPDATE;
3115 IF "issue_row"."phase_finished" NOTNULL THEN
3116 UPDATE "issue" SET
3117 "state" = 'canceled_issue_not_accepted',
3118 "closed" = "phase_finished",
3119 "phase_finished" = NULL
3120 WHERE "id" = "issue_id_p";
3121 END IF;
3122 RETURN NULL;
3123 END IF;
3124 IF "persist"."phase_finished" THEN
3125 IF "persist"."state" = 'discussion' THEN
3126 UPDATE "issue" SET
3127 "state" = 'verification',
3128 "half_frozen" = "phase_finished",
3129 "phase_finished" = NULL
3130 WHERE "id" = "issue_id_p";
3131 RETURN NULL;
3132 END IF;
3133 IF "persist"."state" = 'verification' THEN
3134 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
3135 FOR UPDATE;
3136 SELECT * INTO "policy_row" FROM "policy"
3137 WHERE "id" = "issue_row"."policy_id";
3138 IF EXISTS (
3139 SELECT NULL FROM "initiative"
3140 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
3141 ) THEN
3142 UPDATE "issue" SET
3143 "state" = 'voting',
3144 "fully_frozen" = "phase_finished",
3145 "phase_finished" = NULL
3146 WHERE "id" = "issue_id_p";
3147 ELSE
3148 UPDATE "issue" SET
3149 "state" = 'canceled_no_initiative_admitted',
3150 "fully_frozen" = "phase_finished",
3151 "closed" = "phase_finished",
3152 "phase_finished" = NULL
3153 WHERE "id" = "issue_id_p";
3154 -- NOTE: The following DELETE statements have effect only when
3155 -- issue state has been manipulated
3156 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
3157 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
3158 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3159 END IF;
3160 RETURN NULL;
3161 END IF;
3162 IF "persist"."state" = 'voting' THEN
3163 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
3164 PERFORM "close_voting"("issue_id_p");
3165 "persist"."closed_voting" = TRUE;
3166 RETURN "persist";
3167 END IF;
3168 PERFORM "calculate_ranks"("issue_id_p");
3169 RETURN NULL;
3170 END IF;
3171 END IF;
3172 RAISE WARNING 'should not happen';
3173 RETURN NULL;
3174 END;
3175 $$;
3178 CREATE OR REPLACE FUNCTION "check_everything"()
3179 RETURNS VOID
3180 LANGUAGE 'plpgsql' VOLATILE AS $$
3181 DECLARE
3182 "area_id_v" "area"."id"%TYPE;
3183 "snapshot_id_v" "snapshot"."id"%TYPE;
3184 "issue_id_v" "issue"."id"%TYPE;
3185 "persist_v" "check_issue_persistence";
3186 BEGIN
3187 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
3188 DELETE FROM "expired_session";
3189 DELETE FROM "expired_token";
3190 DELETE FROM "expired_snapshot";
3191 PERFORM "check_activity"();
3192 PERFORM "calculate_member_counts"();
3193 FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP
3194 SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v";
3195 PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"
3196 WHERE "snapshot_id" = "snapshot_id_v";
3197 LOOP
3198 EXIT WHEN "issue_admission"("area_id_v") = FALSE;
3199 END LOOP;
3200 END LOOP;
3201 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
3202 "persist_v" := NULL;
3203 LOOP
3204 "persist_v" := "check_issue"("issue_id_v", "persist_v");
3205 EXIT WHEN "persist_v" ISNULL;
3206 END LOOP;
3207 END LOOP;
3208 RETURN;
3209 END;
3210 $$;
3212 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';
3215 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
3216 RETURNS VOID
3217 LANGUAGE 'plpgsql' VOLATILE AS $$
3218 BEGIN
3219 IF EXISTS (
3220 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
3221 ) THEN
3222 -- override protection triggers:
3223 INSERT INTO "temporary_transaction_data" ("key", "value")
3224 VALUES ('override_protection_triggers', TRUE::TEXT);
3225 -- clean data:
3226 DELETE FROM "delegating_voter"
3227 WHERE "issue_id" = "issue_id_p";
3228 DELETE FROM "direct_voter"
3229 WHERE "issue_id" = "issue_id_p";
3230 DELETE FROM "delegating_interest_snapshot"
3231 WHERE "issue_id" = "issue_id_p";
3232 DELETE FROM "direct_interest_snapshot"
3233 WHERE "issue_id" = "issue_id_p";
3234 DELETE FROM "non_voter"
3235 WHERE "issue_id" = "issue_id_p";
3236 DELETE FROM "delegation"
3237 WHERE "issue_id" = "issue_id_p";
3238 DELETE FROM "supporter"
3239 USING "initiative" -- NOTE: due to missing index on issue_id
3240 WHERE "initiative"."issue_id" = "issue_id_p"
3241 AND "supporter"."initiative_id" = "initiative_id";
3242 -- mark issue as cleaned:
3243 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
3244 -- finish overriding protection triggers (avoids garbage):
3245 DELETE FROM "temporary_transaction_data"
3246 WHERE "key" = 'override_protection_triggers';
3247 END IF;
3248 RETURN;
3249 END;
3250 $$;
3253 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
3254 RETURNS VOID
3255 LANGUAGE 'plpgsql' VOLATILE AS $$
3256 BEGIN
3257 UPDATE "member" SET
3258 "last_login" = NULL,
3259 "last_delegation_check" = NULL,
3260 "login" = NULL,
3261 "password" = NULL,
3262 "authority" = NULL,
3263 "authority_uid" = NULL,
3264 "authority_login" = NULL,
3265 "locked" = TRUE,
3266 "active" = FALSE,
3267 "notify_email" = NULL,
3268 "notify_email_unconfirmed" = NULL,
3269 "notify_email_secret" = NULL,
3270 "notify_email_secret_expiry" = NULL,
3271 "notify_email_lock_expiry" = NULL,
3272 "disable_notifications" = TRUE,
3273 "notification_counter" = DEFAULT,
3274 "notification_sample_size" = 0,
3275 "notification_dow" = NULL,
3276 "notification_hour" = NULL,
3277 "notification_sent" = NULL,
3278 "login_recovery_expiry" = NULL,
3279 "password_reset_secret" = NULL,
3280 "password_reset_secret_expiry" = NULL,
3281 "location" = NULL
3282 WHERE "id" = "member_id_p";
3283 -- "text_search_data" is updated by triggers
3284 DELETE FROM "member_settings" WHERE "member_id" = "member_id_p";
3285 DELETE FROM "member_profile" WHERE "member_id" = "member_id_p";
3286 DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";
3287 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
3288 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
3289 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
3290 DELETE FROM "session" WHERE "member_id" = "member_id_p";
3291 DELETE FROM "member_application" WHERE "member_id" = "member_id_p";
3292 DELETE FROM "token" WHERE "member_id" = "member_id_p";
3293 DELETE FROM "subscription" WHERE "member_id" = "member_id_p";
3294 DELETE FROM "ignored_area" WHERE "member_id" = "member_id_p";
3295 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
3296 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
3297 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
3298 DELETE FROM "direct_voter" USING "issue"
3299 WHERE "direct_voter"."issue_id" = "issue"."id"
3300 AND "issue"."closed" ISNULL
3301 AND "member_id" = "member_id_p";
3302 DELETE FROM "notification_initiative_sent" WHERE "member_id" = "member_id_p";
3303 RETURN;
3304 END;
3305 $$;
3308 CREATE OR REPLACE FUNCTION "delete_private_data"()
3309 RETURNS VOID
3310 LANGUAGE 'plpgsql' VOLATILE AS $$
3311 BEGIN
3312 DELETE FROM "temporary_transaction_data";
3313 DELETE FROM "temporary_suggestion_counts";
3314 DELETE FROM "member" WHERE "activated" ISNULL;
3315 UPDATE "member" SET
3316 "invite_code" = NULL,
3317 "invite_code_expiry" = NULL,
3318 "admin_comment" = NULL,
3319 "last_login" = NULL,
3320 "last_delegation_check" = NULL,
3321 "login" = NULL,
3322 "password" = NULL,
3323 "authority" = NULL,
3324 "authority_uid" = NULL,
3325 "authority_login" = NULL,
3326 "lang" = NULL,
3327 "notify_email" = NULL,
3328 "notify_email_unconfirmed" = NULL,
3329 "notify_email_secret" = NULL,
3330 "notify_email_secret_expiry" = NULL,
3331 "notify_email_lock_expiry" = NULL,
3332 "disable_notifications" = TRUE,
3333 "notification_counter" = DEFAULT,
3334 "notification_sample_size" = 0,
3335 "notification_dow" = NULL,
3336 "notification_hour" = NULL,
3337 "notification_sent" = NULL,
3338 "login_recovery_expiry" = NULL,
3339 "password_reset_secret" = NULL,
3340 "password_reset_secret_expiry" = NULL,
3341 "location" = NULL;
3342 -- "text_search_data" is updated by triggers
3343 DELETE FROM "member_settings";
3344 DELETE FROM "member_useterms";
3345 DELETE FROM "member_profile";
3346 DELETE FROM "rendered_member_statement";
3347 DELETE FROM "member_image";
3348 DELETE FROM "contact";
3349 DELETE FROM "ignored_member";
3350 DELETE FROM "session";
3351 DELETE FROM "system_application";
3352 DELETE FROM "system_application_redirect_uri";
3353 DELETE FROM "dynamic_application_scope";
3354 DELETE FROM "member_application";
3355 DELETE FROM "token";
3356 DELETE FROM "subscription";
3357 DELETE FROM "ignored_area";
3358 DELETE FROM "ignored_initiative";
3359 DELETE FROM "non_voter";
3360 DELETE FROM "direct_voter" USING "issue"
3361 WHERE "direct_voter"."issue_id" = "issue"."id"
3362 AND "issue"."closed" ISNULL;
3363 DELETE FROM "event_processed";
3364 DELETE FROM "notification_initiative_sent";
3365 DELETE FROM "newsletter";
3366 RETURN;
3367 END;
3368 $$;
3371 CREATE TEMPORARY TABLE "old_snapshot" AS
3372 SELECT "ordered".*, row_number() OVER () AS "snapshot_id"
3373 FROM (
3374 SELECT * FROM (
3375 SELECT
3376 "id" AS "issue_id",
3377 'end_of_admission'::"snapshot_event" AS "event",
3378 "accepted" AS "calculated"
3379 FROM "issue" WHERE "accepted" NOTNULL
3380 UNION ALL
3381 SELECT
3382 "id" AS "issue_id",
3383 'half_freeze'::"snapshot_event" AS "event",
3384 "half_frozen" AS "calculated"
3385 FROM "issue" WHERE "half_frozen" NOTNULL
3386 UNION ALL
3387 SELECT
3388 "id" AS "issue_id",
3389 'full_freeze'::"snapshot_event" AS "event",
3390 "fully_frozen" AS "calculated"
3391 FROM "issue" WHERE "fully_frozen" NOTNULL
3392 ) AS "unordered"
3393 ORDER BY "calculated", "issue_id", "event"
3394 ) AS "ordered";
3397 INSERT INTO "snapshot" ("id", "calculated", "population", "area_id", "issue_id")
3398 SELECT
3399 "old_snapshot"."snapshot_id" AS "id",
3400 "old_snapshot"."calculated",
3401 ( SELECT COALESCE(sum("weight"), 0)
3402 FROM "direct_population_snapshot" "dps"
3403 WHERE "dps"."issue_id" = "old_snapshot"."issue_id"
3404 AND "dps"."event" = "old_snapshot"."event"
3405 ) AS "population",
3406 "issue"."area_id" AS "area_id",
3407 "issue"."id" AS "issue_id"
3408 FROM "old_snapshot" JOIN "issue"
3409 ON "old_snapshot"."issue_id" = "issue"."id";
3412 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
3413 SELECT "id" AS "snapshot_id", "issue_id" FROM "snapshot";
3416 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
3417 SELECT
3418 "old_snapshot"."snapshot_id",
3419 "direct_population_snapshot"."member_id"
3420 FROM "old_snapshot" JOIN "direct_population_snapshot"
3421 ON "old_snapshot"."issue_id" = "direct_population_snapshot"."issue_id"
3422 AND "old_snapshot"."event" = "direct_population_snapshot"."event";
3424 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
3425 SELECT
3426 "old_snapshot"."snapshot_id",
3427 "delegating_population_snapshot"."member_id"
3428 FROM "old_snapshot" JOIN "delegating_population_snapshot"
3429 ON "old_snapshot"."issue_id" = "delegating_population_snapshot"."issue_id"
3430 AND "old_snapshot"."event" = "delegating_population_snapshot"."event";
3433 INSERT INTO "direct_interest_snapshot"
3434 ("snapshot_id", "issue_id", "member_id", "weight")
3435 SELECT
3436 "old_snapshot"."snapshot_id",
3437 "old_snapshot"."issue_id",
3438 "direct_interest_snapshot_old"."member_id",
3439 "direct_interest_snapshot_old"."weight"
3440 FROM "old_snapshot" JOIN "direct_interest_snapshot_old"
3441 ON "old_snapshot"."issue_id" = "direct_interest_snapshot_old"."issue_id"
3442 AND "old_snapshot"."event" = "direct_interest_snapshot_old"."event";
3444 INSERT INTO "delegating_interest_snapshot"
3445 ( "snapshot_id", "issue_id",
3446 "member_id", "weight", "scope", "delegate_member_ids" )
3447 SELECT
3448 "old_snapshot"."snapshot_id",
3449 "old_snapshot"."issue_id",
3450 "delegating_interest_snapshot_old"."member_id",
3451 "delegating_interest_snapshot_old"."weight",
3452 "delegating_interest_snapshot_old"."scope",
3453 "delegating_interest_snapshot_old"."delegate_member_ids"
3454 FROM "old_snapshot" JOIN "delegating_interest_snapshot_old"
3455 ON "old_snapshot"."issue_id" = "delegating_interest_snapshot_old"."issue_id"
3456 AND "old_snapshot"."event" = "delegating_interest_snapshot_old"."event";
3458 INSERT INTO "direct_supporter_snapshot"
3459 ( "snapshot_id", "issue_id",
3460 "initiative_id", "member_id", "draft_id", "informed", "satisfied" )
3461 SELECT
3462 "old_snapshot"."snapshot_id",
3463 "old_snapshot"."issue_id",
3464 "direct_supporter_snapshot_old"."initiative_id",
3465 "direct_supporter_snapshot_old"."member_id",
3466 "direct_supporter_snapshot_old"."draft_id",
3467 "direct_supporter_snapshot_old"."informed",
3468 "direct_supporter_snapshot_old"."satisfied"
3469 FROM "old_snapshot" JOIN "direct_supporter_snapshot_old"
3470 ON "old_snapshot"."issue_id" = "direct_supporter_snapshot_old"."issue_id"
3471 AND "old_snapshot"."event" = "direct_supporter_snapshot_old"."event";
3474 ALTER TABLE "issue" DISABLE TRIGGER USER; -- NOTE: required to modify table later
3476 UPDATE "issue" SET "latest_snapshot_id" = "snapshot"."id"
3477 FROM (
3478 SELECT DISTINCT ON ("issue_id") "issue_id", "id"
3479 FROM "snapshot" ORDER BY "issue_id", "id" DESC
3480 ) AS "snapshot"
3481 WHERE "snapshot"."issue_id" = "issue"."id";
3483 UPDATE "issue" SET "admission_snapshot_id" = "old_snapshot"."snapshot_id"
3484 FROM "old_snapshot"
3485 WHERE "old_snapshot"."issue_id" = "issue"."id"
3486 AND "old_snapshot"."event" = 'end_of_admission';
3488 UPDATE "issue" SET "half_freeze_snapshot_id" = "old_snapshot"."snapshot_id"
3489 FROM "old_snapshot"
3490 WHERE "old_snapshot"."issue_id" = "issue"."id"
3491 AND "old_snapshot"."event" = 'half_freeze';
3493 UPDATE "issue" SET "full_freeze_snapshot_id" = "old_snapshot"."snapshot_id"
3494 FROM "old_snapshot"
3495 WHERE "old_snapshot"."issue_id" = "issue"."id"
3496 AND "old_snapshot"."event" = 'full_freeze';
3498 ALTER TABLE "issue" ENABLE TRIGGER USER;
3501 DROP TABLE "old_snapshot";
3503 DROP TABLE "direct_supporter_snapshot_old";
3504 DROP TABLE "delegating_interest_snapshot_old";
3505 DROP TABLE "direct_interest_snapshot_old";
3506 DROP TABLE "delegating_population_snapshot";
3507 DROP TABLE "direct_population_snapshot";
3510 DROP VIEW "open_issue";
3513 ALTER TABLE "issue" DROP COLUMN "latest_snapshot_event";
3516 CREATE VIEW "open_issue" AS
3517 SELECT * FROM "issue" WHERE "closed" ISNULL;
3519 COMMENT ON VIEW "open_issue" IS 'All open issues';
3522 -- NOTE: create "issue_for_admission" view after altering table "issue"
3523 CREATE VIEW "issue_for_admission" AS
3524 SELECT DISTINCT ON ("issue"."area_id")
3525 "issue".*,
3526 max("initiative"."supporter_count") AS "max_supporter_count"
3527 FROM "issue"
3528 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
3529 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
3530 JOIN "area" ON "issue"."area_id" = "area"."id"
3531 WHERE "issue"."state" = 'admission'::"issue_state"
3532 AND now() >= "issue"."created" + "issue"."min_admission_time"
3533 AND "initiative"."supporter_count" >= "policy"."issue_quorum"
3534 AND "initiative"."supporter_count" * "policy"."issue_quorum_den" >=
3535 "issue"."population" * "policy"."issue_quorum_num"
3536 AND "initiative"."supporter_count" >= "area"."issue_quorum"
3537 AND "initiative"."revoked" ISNULL
3538 GROUP BY "issue"."id"
3539 ORDER BY "issue"."area_id", "max_supporter_count" DESC, "issue"."id";
3541 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';
3544 DROP TYPE "snapshot_event";
3547 ALTER TABLE "issue" ADD CONSTRAINT "snapshot_required" CHECK (
3548 ("half_frozen" ISNULL OR "half_freeze_snapshot_id" NOTNULL) AND
3549 ("fully_frozen" ISNULL OR "full_freeze_snapshot_id" NOTNULL) );
3552 COMMIT;