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