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