liquid_feedback_core
view update/core-update.v3.2.2-v4.0.0.sql @ 548:1adb0ad5900c
Added "discovery_baseurl" for system applications
| author | jbe |
|---|---|
| date | Sat Aug 05 16:10:43 2017 +0200 (2017-08-05) |
| parents | 3cde0bb68adf |
| children | 81a35235b450 |
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 BEGIN
1443 IF TG_OP = 'UPDATE' THEN
1444 IF
1445 OLD."initiative_id" = NEW."initiative_id" AND
1446 OLD."member_id" = NEW."member_id" AND
1447 coalesce(OLD."accepted", FALSE) = coalesce(NEW."accepted", FALSE)
1448 THEN
1449 RETURN NULL;
1450 END IF;
1451 END IF;
1452 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "accepted_v" THEN
1453 IF coalesce(OLD."accepted", FALSE) = TRUE THEN
1454 SELECT * INTO "initiative_row" FROM "initiative"
1455 WHERE "id" = OLD."initiative_id" FOR SHARE;
1456 IF "initiative_row"."id" NOTNULL THEN
1457 SELECT * INTO "issue_row" FROM "issue"
1458 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1459 SELECT * INTO "area_row" FROM "area"
1460 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1461 INSERT INTO "event" (
1462 "event", "member_id",
1463 "unit_id", "area_id", "policy_id", "issue_id", "state",
1464 "initiative_id", "boolean_value"
1465 ) VALUES (
1466 'initiator', OLD."member_id",
1467 "area_row"."unit_id", "issue_row"."area_id",
1468 "issue_row"."policy_id",
1469 "issue_row"."id", "issue_row"."state",
1470 OLD."initiative_id", FALSE
1471 );
1472 END IF;
1473 END IF;
1474 END IF;
1475 IF TG_OP = 'UPDATE' AND NOT "rejected_v" THEN
1476 IF coalesce(NEW."accepted", FALSE) = TRUE THEN
1477 SELECT * INTO "initiative_row" FROM "initiative"
1478 WHERE "id" = NEW."initiative_id" FOR SHARE;
1479 SELECT * INTO "issue_row" FROM "issue"
1480 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1481 SELECT * INTO "area_row" FROM "area"
1482 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1483 INSERT INTO "event" (
1484 "event", "member_id",
1485 "unit_id", "area_id", "policy_id", "issue_id", "state",
1486 "initiative_id", "boolean_value"
1487 ) VALUES (
1488 'initiator', NEW."member_id",
1489 "area_row"."unit_id", "issue_row"."area_id",
1490 "issue_row"."policy_id",
1491 "issue_row"."id", "issue_row"."state",
1492 NEW."initiative_id", TRUE
1493 );
1494 END IF;
1495 END IF;
1496 RETURN NULL;
1497 END;
1498 $$;
1500 CREATE TRIGGER "write_event_initiator"
1501 AFTER UPDATE OR DELETE ON "initiator" FOR EACH ROW EXECUTE PROCEDURE
1502 "write_event_initiator_trigger"();
1504 COMMENT ON FUNCTION "write_event_initiator_trigger"() IS 'Implementation of trigger "write_event_initiator" on table "initiator"';
1505 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)';
1508 CREATE FUNCTION "write_event_support_trigger"()
1509 RETURNS TRIGGER
1510 LANGUAGE 'plpgsql' VOLATILE AS $$
1511 DECLARE
1512 "issue_row" "issue"%ROWTYPE;
1513 "area_row" "area"%ROWTYPE;
1514 BEGIN
1515 IF TG_OP = 'UPDATE' THEN
1516 IF
1517 OLD."initiative_id" = NEW."initiative_id" AND
1518 OLD."member_id" = NEW."member_id"
1519 THEN
1520 IF OLD."draft_id" != NEW."draft_id" THEN
1521 SELECT * INTO "issue_row" FROM "issue"
1522 WHERE "id" = NEW."issue_id" FOR SHARE;
1523 SELECT * INTO "area_row" FROM "area"
1524 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1525 INSERT INTO "event" (
1526 "event", "member_id",
1527 "unit_id", "area_id", "policy_id", "issue_id", "state",
1528 "initiative_id", "draft_id"
1529 ) VALUES (
1530 'support_updated', NEW."member_id",
1531 "area_row"."unit_id", "issue_row"."area_id",
1532 "issue_row"."policy_id",
1533 "issue_row"."id", "issue_row"."state",
1534 NEW."initiative_id", NEW."draft_id"
1535 );
1536 END IF;
1537 RETURN NULL;
1538 END IF;
1539 END IF;
1540 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1541 IF EXISTS (
1542 SELECT NULL FROM "initiative" WHERE "id" = OLD."initiative_id"
1543 FOR SHARE
1544 ) THEN
1545 SELECT * INTO "issue_row" FROM "issue"
1546 WHERE "id" = OLD."issue_id" FOR SHARE;
1547 SELECT * INTO "area_row" FROM "area"
1548 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1549 INSERT INTO "event" (
1550 "event", "member_id",
1551 "unit_id", "area_id", "policy_id", "issue_id", "state",
1552 "initiative_id", "boolean_value"
1553 ) VALUES (
1554 'support', OLD."member_id",
1555 "area_row"."unit_id", "issue_row"."area_id",
1556 "issue_row"."policy_id",
1557 "issue_row"."id", "issue_row"."state",
1558 OLD."initiative_id", FALSE
1559 );
1560 END IF;
1561 END IF;
1562 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1563 SELECT * INTO "issue_row" FROM "issue"
1564 WHERE "id" = NEW."issue_id" FOR SHARE;
1565 SELECT * INTO "area_row" FROM "area"
1566 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1567 INSERT INTO "event" (
1568 "event", "member_id",
1569 "unit_id", "area_id", "policy_id", "issue_id", "state",
1570 "initiative_id", "draft_id", "boolean_value"
1571 ) VALUES (
1572 'support', NEW."member_id",
1573 "area_row"."unit_id", "issue_row"."area_id",
1574 "issue_row"."policy_id",
1575 "issue_row"."id", "issue_row"."state",
1576 NEW."initiative_id", NEW."draft_id", TRUE
1577 );
1578 END IF;
1579 RETURN NULL;
1580 END;
1581 $$;
1583 CREATE TRIGGER "write_event_support"
1584 AFTER INSERT OR UPDATE OR DELETE ON "supporter" FOR EACH ROW EXECUTE PROCEDURE
1585 "write_event_support_trigger"();
1587 COMMENT ON FUNCTION "write_event_support_trigger"() IS 'Implementation of trigger "write_event_support" on table "supporter"';
1588 COMMENT ON TRIGGER "write_event_support" ON "supporter" IS 'Create entry in "event" table when adding, updating, or removing support';
1591 CREATE FUNCTION "write_event_suggestion_rated_trigger"()
1592 RETURNS TRIGGER
1593 LANGUAGE 'plpgsql' VOLATILE AS $$
1594 DECLARE
1595 "same_pkey_v" BOOLEAN = FALSE;
1596 "initiative_row" "initiative"%ROWTYPE;
1597 "issue_row" "issue"%ROWTYPE;
1598 "area_row" "area"%ROWTYPE;
1599 BEGIN
1600 IF TG_OP = 'UPDATE' THEN
1601 IF
1602 OLD."suggestion_id" = NEW."suggestion_id" AND
1603 OLD."member_id" = NEW."member_id"
1604 THEN
1605 IF
1606 OLD."degree" = NEW."degree" AND
1607 OLD."fulfilled" = NEW."fulfilled"
1608 THEN
1609 RETURN NULL;
1610 END IF;
1611 "same_pkey_v" := TRUE;
1612 END IF;
1613 END IF;
1614 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "same_pkey_v" THEN
1615 IF EXISTS (
1616 SELECT NULL FROM "suggestion" WHERE "id" = OLD."suggestion_id"
1617 FOR SHARE
1618 ) THEN
1619 SELECT * INTO "initiative_row" FROM "initiative"
1620 WHERE "id" = OLD."initiative_id" FOR SHARE;
1621 SELECT * INTO "issue_row" FROM "issue"
1622 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1623 SELECT * INTO "area_row" FROM "area"
1624 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1625 INSERT INTO "event" (
1626 "event", "member_id",
1627 "unit_id", "area_id", "policy_id", "issue_id", "state",
1628 "initiative_id", "suggestion_id",
1629 "boolean_value", "numeric_value"
1630 ) VALUES (
1631 'suggestion_rated', OLD."member_id",
1632 "area_row"."unit_id", "issue_row"."area_id",
1633 "issue_row"."policy_id",
1634 "initiative_row"."issue_id", "issue_row"."state",
1635 OLD."initiative_id", OLD."suggestion_id",
1636 NULL, 0
1637 );
1638 END IF;
1639 END IF;
1640 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1641 SELECT * INTO "initiative_row" FROM "initiative"
1642 WHERE "id" = NEW."initiative_id" FOR SHARE;
1643 SELECT * INTO "issue_row" FROM "issue"
1644 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1645 SELECT * INTO "area_row" FROM "area"
1646 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1647 INSERT INTO "event" (
1648 "event", "member_id",
1649 "unit_id", "area_id", "policy_id", "issue_id", "state",
1650 "initiative_id", "suggestion_id",
1651 "boolean_value", "numeric_value"
1652 ) VALUES (
1653 'suggestion_rated', NEW."member_id",
1654 "area_row"."unit_id", "issue_row"."area_id",
1655 "issue_row"."policy_id",
1656 "initiative_row"."issue_id", "issue_row"."state",
1657 NEW."initiative_id", NEW."suggestion_id",
1658 NEW."fulfilled", NEW."degree"
1659 );
1660 END IF;
1661 RETURN NULL;
1662 END;
1663 $$;
1665 CREATE TRIGGER "write_event_suggestion_rated"
1666 AFTER INSERT OR UPDATE OR DELETE ON "opinion" FOR EACH ROW EXECUTE PROCEDURE
1667 "write_event_suggestion_rated_trigger"();
1669 COMMENT ON FUNCTION "write_event_suggestion_rated_trigger"() IS 'Implementation of trigger "write_event_suggestion_rated" on table "opinion"';
1670 COMMENT ON TRIGGER "write_event_suggestion_rated" ON "opinion" IS 'Create entry in "event" table when adding, updating, or removing support';
1673 CREATE FUNCTION "write_event_delegation_trigger"()
1674 RETURNS TRIGGER
1675 LANGUAGE 'plpgsql' VOLATILE AS $$
1676 DECLARE
1677 "issue_row" "issue"%ROWTYPE;
1678 "area_row" "area"%ROWTYPE;
1679 BEGIN
1680 IF TG_OP = 'DELETE' THEN
1681 IF EXISTS (
1682 SELECT NULL FROM "member" WHERE "id" = OLD."truster_id"
1683 ) AND (CASE OLD."scope"
1684 WHEN 'unit'::"delegation_scope" THEN EXISTS (
1685 SELECT NULL FROM "unit" WHERE "id" = OLD."unit_id"
1686 )
1687 WHEN 'area'::"delegation_scope" THEN EXISTS (
1688 SELECT NULL FROM "area" WHERE "id" = OLD."area_id"
1689 )
1690 WHEN 'issue'::"delegation_scope" THEN EXISTS (
1691 SELECT NULL FROM "issue" WHERE "id" = OLD."issue_id"
1692 )
1693 END) THEN
1694 SELECT * INTO "issue_row" FROM "issue"
1695 WHERE "id" = OLD."issue_id" FOR SHARE;
1696 SELECT * INTO "area_row" FROM "area"
1697 WHERE "id" = COALESCE(OLD."area_id", "issue_row"."area_id")
1698 FOR SHARE;
1699 INSERT INTO "event" (
1700 "event", "member_id", "scope",
1701 "unit_id", "area_id", "issue_id", "state",
1702 "boolean_value"
1703 ) VALUES (
1704 'delegation', OLD."truster_id", OLD."scope",
1705 COALESCE(OLD."unit_id", "area_row"."unit_id"), "area_row"."id",
1706 OLD."issue_id", "issue_row"."state",
1707 FALSE
1708 );
1709 END IF;
1710 ELSE
1711 SELECT * INTO "issue_row" FROM "issue"
1712 WHERE "id" = NEW."issue_id" FOR SHARE;
1713 SELECT * INTO "area_row" FROM "area"
1714 WHERE "id" = COALESCE(NEW."area_id", "issue_row"."area_id")
1715 FOR SHARE;
1716 INSERT INTO "event" (
1717 "event", "member_id", "other_member_id", "scope",
1718 "unit_id", "area_id", "issue_id", "state",
1719 "boolean_value"
1720 ) VALUES (
1721 'delegation', NEW."truster_id", NEW."trustee_id", NEW."scope",
1722 COALESCE(NEW."unit_id", "area_row"."unit_id"), "area_row"."id",
1723 NEW."issue_id", "issue_row"."state",
1724 TRUE
1725 );
1726 END IF;
1727 RETURN NULL;
1728 END;
1729 $$;
1731 CREATE TRIGGER "write_event_delegation"
1732 AFTER INSERT OR UPDATE OR DELETE ON "delegation" FOR EACH ROW EXECUTE PROCEDURE
1733 "write_event_delegation_trigger"();
1735 COMMENT ON FUNCTION "write_event_delegation_trigger"() IS 'Implementation of trigger "write_event_delegation" on table "delegation"';
1736 COMMENT ON TRIGGER "write_event_delegation" ON "delegation" IS 'Create entry in "event" table when adding, updating, or removing a delegation';
1739 CREATE FUNCTION "write_event_contact_trigger"()
1740 RETURNS TRIGGER
1741 LANGUAGE 'plpgsql' VOLATILE AS $$
1742 BEGIN
1743 IF TG_OP = 'UPDATE' THEN
1744 IF
1745 OLD."member_id" = NEW."member_id" AND
1746 OLD."other_member_id" = NEW."other_member_id" AND
1747 OLD."public" = NEW."public"
1748 THEN
1749 RETURN NULL;
1750 END IF;
1751 END IF;
1752 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1753 IF OLD."public" THEN
1754 IF EXISTS (
1755 SELECT NULL FROM "member" WHERE "id" = OLD."member_id"
1756 FOR SHARE
1757 ) AND EXISTS (
1758 SELECT NULL FROM "member" WHERE "id" = OLD."other_member_id"
1759 FOR SHARE
1760 ) THEN
1761 INSERT INTO "event" (
1762 "event", "member_id", "other_member_id", "boolean_value"
1763 ) VALUES (
1764 'contact', OLD."member_id", OLD."other_member_id", FALSE
1765 );
1766 END IF;
1767 END IF;
1768 END IF;
1769 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1770 IF NEW."public" THEN
1771 INSERT INTO "event" (
1772 "event", "member_id", "other_member_id", "boolean_value"
1773 ) VALUES (
1774 'contact', NEW."member_id", NEW."other_member_id", TRUE
1775 );
1776 END IF;
1777 END IF;
1778 RETURN NULL;
1779 END;
1780 $$;
1782 CREATE TRIGGER "write_event_contact"
1783 AFTER INSERT OR UPDATE OR DELETE ON "contact" FOR EACH ROW EXECUTE PROCEDURE
1784 "write_event_contact_trigger"();
1786 COMMENT ON FUNCTION "write_event_contact_trigger"() IS 'Implementation of trigger "write_event_contact" on table "contact"';
1787 COMMENT ON TRIGGER "write_event_contact" ON "contact" IS 'Create entry in "event" table when adding or removing public contacts';
1790 CREATE FUNCTION "send_event_notify_trigger"()
1791 RETURNS TRIGGER
1792 LANGUAGE 'plpgsql' VOLATILE AS $$
1793 BEGIN
1794 EXECUTE 'NOTIFY "event", ''' || NEW."event" || '''';
1795 RETURN NULL;
1796 END;
1797 $$;
1799 CREATE TRIGGER "send_notify"
1800 AFTER INSERT OR UPDATE ON "event" FOR EACH ROW EXECUTE PROCEDURE
1801 "send_event_notify_trigger"();
1804 CREATE FUNCTION "delete_extended_scope_tokens_trigger"()
1805 RETURNS TRIGGER
1806 LANGUAGE 'plpgsql' VOLATILE AS $$
1807 DECLARE
1808 "system_application_row" "system_application"%ROWTYPE;
1809 BEGIN
1810 IF OLD."system_application_id" NOTNULL THEN
1811 SELECT * FROM "system_application" INTO "system_application_row"
1812 WHERE "id" = OLD."system_application_id";
1813 DELETE FROM "token"
1814 WHERE "member_id" = OLD."member_id"
1815 AND "system_application_id" = OLD."system_application_id"
1816 AND NOT COALESCE(
1817 regexp_split_to_array("scope", E'\\s+') <@
1818 regexp_split_to_array(
1819 "system_application_row"."automatic_scope", E'\\s+'
1820 ),
1821 FALSE
1822 );
1823 END IF;
1824 RETURN OLD;
1825 END;
1826 $$;
1828 CREATE TRIGGER "delete_extended_scope_tokens"
1829 BEFORE DELETE ON "member_application" FOR EACH ROW EXECUTE PROCEDURE
1830 "delete_extended_scope_tokens_trigger"();
1833 CREATE FUNCTION "detach_token_from_session_trigger"()
1834 RETURNS TRIGGER
1835 LANGUAGE 'plpgsql' VOLATILE AS $$
1836 BEGIN
1837 UPDATE "token" SET "session_id" = NULL
1838 WHERE "session_id" = OLD."id";
1839 RETURN OLD;
1840 END;
1841 $$;
1843 CREATE TRIGGER "detach_token_from_session"
1844 BEFORE DELETE ON "session" FOR EACH ROW EXECUTE PROCEDURE
1845 "detach_token_from_session_trigger"();
1848 CREATE FUNCTION "delete_non_detached_scope_with_session_trigger"()
1849 RETURNS TRIGGER
1850 LANGUAGE 'plpgsql' VOLATILE AS $$
1851 BEGIN
1852 IF NEW."session_id" ISNULL THEN
1853 SELECT coalesce(string_agg("element", ' '), '') INTO NEW."scope"
1854 FROM unnest(regexp_split_to_array(NEW."scope", E'\\s+')) AS "element"
1855 WHERE "element" LIKE '%_detached';
1856 END IF;
1857 RETURN NEW;
1858 END;
1859 $$;
1861 CREATE TRIGGER "delete_non_detached_scope_with_session"
1862 BEFORE INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
1863 "delete_non_detached_scope_with_session_trigger"();
1866 CREATE FUNCTION "delete_token_with_empty_scope_trigger"()
1867 RETURNS TRIGGER
1868 LANGUAGE 'plpgsql' VOLATILE AS $$
1869 BEGIN
1870 IF NEW."scope" = '' THEN
1871 DELETE FROM "token" WHERE "id" = NEW."id";
1872 END IF;
1873 RETURN NULL;
1874 END;
1875 $$;
1877 CREATE TRIGGER "delete_token_with_empty_scope"
1878 AFTER INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
1879 "delete_token_with_empty_scope_trigger"();
1882 CREATE FUNCTION "delete_snapshot_on_partial_delete_trigger"()
1883 RETURNS TRIGGER
1884 LANGUAGE 'plpgsql' VOLATILE AS $$
1885 BEGIN
1886 IF TG_OP = 'UPDATE' THEN
1887 IF
1888 OLD."snapshot_id" = NEW."snapshot_id" AND
1889 OLD."issue_id" = NEW."issue_id"
1890 THEN
1891 RETURN NULL;
1892 END IF;
1893 END IF;
1894 DELETE FROM "snapshot" WHERE "id" = OLD."snapshot_id";
1895 RETURN NULL;
1896 END;
1897 $$;
1899 CREATE TRIGGER "delete_snapshot_on_partial_delete"
1900 AFTER UPDATE OR DELETE ON "snapshot_issue"
1901 FOR EACH ROW EXECUTE PROCEDURE
1902 "delete_snapshot_on_partial_delete_trigger"();
1904 COMMENT ON FUNCTION "delete_snapshot_on_partial_delete_trigger"() IS 'Implementation of trigger "delete_snapshot_on_partial_delete" on table "snapshot_issue"';
1905 COMMENT ON TRIGGER "delete_snapshot_on_partial_delete" ON "snapshot_issue" IS 'Deletes whole snapshot if one issue is deleted from the snapshot';
1908 CREATE FUNCTION "copy_current_draft_data"
1909 ("initiative_id_p" "initiative"."id"%TYPE )
1910 RETURNS VOID
1911 LANGUAGE 'plpgsql' VOLATILE AS $$
1912 BEGIN
1913 PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p"
1914 FOR UPDATE;
1915 UPDATE "initiative" SET
1916 "location" = "draft"."location",
1917 "draft_text_search_data" = "draft"."text_search_data"
1918 FROM "current_draft" AS "draft"
1919 WHERE "initiative"."id" = "initiative_id_p"
1920 AND "draft"."initiative_id" = "initiative_id_p";
1921 END;
1922 $$;
1924 COMMENT ON FUNCTION "copy_current_draft_data"
1925 ( "initiative"."id"%TYPE )
1926 IS 'Helper function for function "copy_current_draft_data_trigger"';
1929 CREATE FUNCTION "copy_current_draft_data_trigger"()
1930 RETURNS TRIGGER
1931 LANGUAGE 'plpgsql' VOLATILE AS $$
1932 BEGIN
1933 IF TG_OP='DELETE' THEN
1934 PERFORM "copy_current_draft_data"(OLD."initiative_id");
1935 ELSE
1936 IF TG_OP='UPDATE' THEN
1937 IF COALESCE(OLD."inititiave_id" != NEW."initiative_id", TRUE) THEN
1938 PERFORM "copy_current_draft_data"(OLD."initiative_id");
1939 END IF;
1940 END IF;
1941 PERFORM "copy_current_draft_data"(NEW."initiative_id");
1942 END IF;
1943 RETURN NULL;
1944 END;
1945 $$;
1947 CREATE TRIGGER "copy_current_draft_data"
1948 AFTER INSERT OR UPDATE OR DELETE ON "draft"
1949 FOR EACH ROW EXECUTE PROCEDURE
1950 "copy_current_draft_data_trigger"();
1952 COMMENT ON FUNCTION "copy_current_draft_data_trigger"() IS 'Implementation of trigger "copy_current_draft_data" on table "draft"';
1953 COMMENT ON TRIGGER "copy_current_draft_data" ON "draft" IS 'Copy certain fields from most recent "draft" to "initiative"';
1956 CREATE VIEW "area_quorum" AS
1957 SELECT
1958 "area"."id" AS "area_id",
1959 ceil(
1960 "area"."quorum_standard"::FLOAT8 * "quorum_factor"::FLOAT8 ^ (
1961 coalesce(
1962 ( SELECT sum(
1963 ( extract(epoch from "area"."quorum_time")::FLOAT8 /
1964 extract(epoch from
1965 ("issue"."accepted"-"issue"."created") +
1966 "issue"."discussion_time" +
1967 "issue"."verification_time" +
1968 "issue"."voting_time"
1969 )::FLOAT8
1970 ) ^ "area"."quorum_exponent"::FLOAT8
1971 )
1972 FROM "issue" JOIN "policy"
1973 ON "issue"."policy_id" = "policy"."id"
1974 WHERE "issue"."area_id" = "area"."id"
1975 AND "issue"."accepted" NOTNULL
1976 AND "issue"."closed" ISNULL
1977 AND "policy"."polling" = FALSE
1978 )::FLOAT8, 0::FLOAT8
1979 ) / "area"."quorum_issues"::FLOAT8 - 1::FLOAT8
1980 ) * CASE WHEN "area"."quorum_den" ISNULL THEN 1 ELSE (
1981 SELECT "snapshot"."population"
1982 FROM "snapshot"
1983 WHERE "snapshot"."area_id" = "area"."id"
1984 AND "snapshot"."issue_id" ISNULL
1985 ORDER BY "snapshot"."id" DESC
1986 LIMIT 1
1987 ) END / coalesce("area"."quorum_den", 1)
1989 )::INT4 AS "issue_quorum"
1990 FROM "area";
1992 COMMENT ON VIEW "area_quorum" IS 'Area-based quorum considering number of open (accepted) issues';
1995 CREATE VIEW "area_with_unaccepted_issues" AS
1996 SELECT DISTINCT ON ("area"."id") "area".*
1997 FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id"
1998 WHERE "issue"."state" = 'admission';
2000 COMMENT ON VIEW "area_with_unaccepted_issues" IS 'All areas with unaccepted open issues (needed for issue admission system)';
2003 DROP VIEW "area_member_count";
2006 DROP TABLE "membership";
2009 DROP FUNCTION "membership_weight"
2010 ( "area_id_p" "area"."id"%TYPE,
2011 "member_id_p" "member"."id"%TYPE );
2014 DROP FUNCTION "membership_weight_with_skipping"
2015 ( "area_id_p" "area"."id"%TYPE,
2016 "member_id_p" "member"."id"%TYPE,
2017 "skip_member_ids_p" INT4[] ); -- TODO: ordering/cascade
2020 CREATE OR REPLACE VIEW "issue_delegation" AS
2021 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
2022 "issue"."id" AS "issue_id",
2023 "delegation"."id",
2024 "delegation"."truster_id",
2025 "delegation"."trustee_id",
2026 "delegation"."scope"
2027 FROM "issue"
2028 JOIN "area"
2029 ON "area"."id" = "issue"."area_id"
2030 JOIN "delegation"
2031 ON "delegation"."unit_id" = "area"."unit_id"
2032 OR "delegation"."area_id" = "area"."id"
2033 OR "delegation"."issue_id" = "issue"."id"
2034 JOIN "member"
2035 ON "delegation"."truster_id" = "member"."id"
2036 JOIN "privilege"
2037 ON "area"."unit_id" = "privilege"."unit_id"
2038 AND "delegation"."truster_id" = "privilege"."member_id"
2039 WHERE "member"."active" AND "privilege"."voting_right"
2040 ORDER BY
2041 "issue"."id",
2042 "delegation"."truster_id",
2043 "delegation"."scope" DESC;
2046 CREATE VIEW "unit_member" AS
2047 SELECT
2048 "unit"."id" AS "unit_id",
2049 "member"."id" AS "member_id"
2050 FROM "privilege"
2051 JOIN "unit" ON "unit_id" = "privilege"."unit_id"
2052 JOIN "member" ON "member"."id" = "privilege"."member_id"
2053 WHERE "privilege"."voting_right" AND "member"."active";
2055 COMMENT ON VIEW "unit_member" IS 'Active members with voting right in a unit';
2058 CREATE OR REPLACE VIEW "unit_member_count" AS
2059 SELECT
2060 "unit"."id" AS "unit_id",
2061 count("unit_member"."member_id") AS "member_count"
2062 FROM "unit" LEFT JOIN "unit_member"
2063 ON "unit"."id" = "unit_member"."unit_id"
2064 GROUP BY "unit"."id";
2066 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
2069 CREATE OR REPLACE VIEW "opening_draft" AS
2070 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
2071 ORDER BY "initiative_id", "id";
2074 CREATE OR REPLACE VIEW "current_draft" AS
2075 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
2076 ORDER BY "initiative_id", "id" DESC;
2079 CREATE OR REPLACE VIEW "issue_supporter_in_admission_state" AS
2080 SELECT
2081 "area"."unit_id",
2082 "issue"."area_id",
2083 "issue"."id" AS "issue_id",
2084 "supporter"."member_id",
2085 "direct_interest_snapshot"."weight"
2086 FROM "issue"
2087 JOIN "area" ON "area"."id" = "issue"."area_id"
2088 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
2089 JOIN "direct_interest_snapshot"
2090 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
2091 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
2092 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
2093 WHERE "issue"."state" = 'admission'::"issue_state";
2096 CREATE OR REPLACE VIEW "individual_suggestion_ranking" AS
2097 SELECT
2098 "opinion"."initiative_id",
2099 "opinion"."member_id",
2100 "direct_interest_snapshot"."weight",
2101 CASE WHEN
2102 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
2103 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
2104 THEN 1 ELSE
2105 CASE WHEN
2106 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
2107 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
2108 THEN 2 ELSE
2109 CASE WHEN
2110 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
2111 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
2112 THEN 3 ELSE 4 END
2113 END
2114 END AS "preference",
2115 "opinion"."suggestion_id"
2116 FROM "opinion"
2117 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
2118 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2119 JOIN "direct_interest_snapshot"
2120 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
2121 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
2122 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
2125 CREATE VIEW "expired_session" AS
2126 SELECT * FROM "session" WHERE now() > "expiry";
2128 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
2129 DELETE FROM "session" WHERE "id" = OLD."id";
2131 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
2132 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
2135 CREATE VIEW "expired_token" AS
2136 SELECT * FROM "token" WHERE now() > "expiry" AND NOT (
2137 "token_type" = 'authorization' AND "used" AND EXISTS (
2138 SELECT NULL FROM "token" AS "other"
2139 WHERE "other"."authorization_token_id" = "id" ) );
2141 CREATE RULE "delete" AS ON DELETE TO "expired_token" DO INSTEAD
2142 DELETE FROM "token" WHERE "id" = OLD."id";
2144 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';
2147 CREATE VIEW "unused_snapshot" AS
2148 SELECT "snapshot".* FROM "snapshot"
2149 LEFT JOIN "issue"
2150 ON "snapshot"."id" = "issue"."latest_snapshot_id"
2151 OR "snapshot"."id" = "issue"."admission_snapshot_id"
2152 OR "snapshot"."id" = "issue"."half_freeze_snapshot_id"
2153 OR "snapshot"."id" = "issue"."full_freeze_snapshot_id"
2154 WHERE "issue"."id" ISNULL;
2156 CREATE RULE "delete" AS ON DELETE TO "unused_snapshot" DO INSTEAD
2157 DELETE FROM "snapshot" WHERE "id" = OLD."id";
2159 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)';
2162 CREATE VIEW "expired_snapshot" AS
2163 SELECT "unused_snapshot".* FROM "unused_snapshot" CROSS JOIN "system_setting"
2164 WHERE "unused_snapshot"."calculated" <
2165 now() - "system_setting"."snapshot_retention";
2167 CREATE RULE "delete" AS ON DELETE TO "expired_snapshot" DO INSTEAD
2168 DELETE FROM "snapshot" WHERE "id" = OLD."id";
2170 COMMENT ON VIEW "expired_snapshot" IS 'Contains "unused_snapshot"s that are older than "system_setting"."snapshot_retention" (for deletion)';
2173 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest; for area and global delegation chains: always null';
2176 CREATE OR REPLACE FUNCTION "delegation_chain"
2177 ( "member_id_p" "member"."id"%TYPE,
2178 "unit_id_p" "unit"."id"%TYPE,
2179 "area_id_p" "area"."id"%TYPE,
2180 "issue_id_p" "issue"."id"%TYPE,
2181 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2182 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2183 RETURNS SETOF "delegation_chain_row"
2184 LANGUAGE 'plpgsql' STABLE AS $$
2185 DECLARE
2186 "scope_v" "delegation_scope";
2187 "unit_id_v" "unit"."id"%TYPE;
2188 "area_id_v" "area"."id"%TYPE;
2189 "issue_row" "issue"%ROWTYPE;
2190 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2191 "loop_member_id_v" "member"."id"%TYPE;
2192 "output_row" "delegation_chain_row";
2193 "output_rows" "delegation_chain_row"[];
2194 "simulate_v" BOOLEAN;
2195 "simulate_here_v" BOOLEAN;
2196 "delegation_row" "delegation"%ROWTYPE;
2197 "row_count" INT4;
2198 "i" INT4;
2199 "loop_v" BOOLEAN;
2200 BEGIN
2201 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
2202 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
2203 END IF;
2204 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
2205 "simulate_v" := TRUE;
2206 ELSE
2207 "simulate_v" := FALSE;
2208 END IF;
2209 IF
2210 "unit_id_p" NOTNULL AND
2211 "area_id_p" ISNULL AND
2212 "issue_id_p" ISNULL
2213 THEN
2214 "scope_v" := 'unit';
2215 "unit_id_v" := "unit_id_p";
2216 ELSIF
2217 "unit_id_p" ISNULL AND
2218 "area_id_p" NOTNULL AND
2219 "issue_id_p" ISNULL
2220 THEN
2221 "scope_v" := 'area';
2222 "area_id_v" := "area_id_p";
2223 SELECT "unit_id" INTO "unit_id_v"
2224 FROM "area" WHERE "id" = "area_id_v";
2225 ELSIF
2226 "unit_id_p" ISNULL AND
2227 "area_id_p" ISNULL AND
2228 "issue_id_p" NOTNULL
2229 THEN
2230 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
2231 IF "issue_row"."id" ISNULL THEN
2232 RETURN;
2233 END IF;
2234 IF "issue_row"."closed" NOTNULL THEN
2235 IF "simulate_v" THEN
2236 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
2237 END IF;
2238 FOR "output_row" IN
2239 SELECT * FROM
2240 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
2241 LOOP
2242 RETURN NEXT "output_row";
2243 END LOOP;
2244 RETURN;
2245 END IF;
2246 "scope_v" := 'issue';
2247 SELECT "area_id" INTO "area_id_v"
2248 FROM "issue" WHERE "id" = "issue_id_p";
2249 SELECT "unit_id" INTO "unit_id_v"
2250 FROM "area" WHERE "id" = "area_id_v";
2251 ELSE
2252 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2253 END IF;
2254 "visited_member_ids" := '{}';
2255 "loop_member_id_v" := NULL;
2256 "output_rows" := '{}';
2257 "output_row"."index" := 0;
2258 "output_row"."member_id" := "member_id_p";
2259 "output_row"."member_valid" := TRUE;
2260 "output_row"."participation" := FALSE;
2261 "output_row"."overridden" := FALSE;
2262 "output_row"."disabled_out" := FALSE;
2263 "output_row"."scope_out" := NULL;
2264 LOOP
2265 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2266 "loop_member_id_v" := "output_row"."member_id";
2267 ELSE
2268 "visited_member_ids" :=
2269 "visited_member_ids" || "output_row"."member_id";
2270 END IF;
2271 IF "output_row"."participation" ISNULL THEN
2272 "output_row"."overridden" := NULL;
2273 ELSIF "output_row"."participation" THEN
2274 "output_row"."overridden" := TRUE;
2275 END IF;
2276 "output_row"."scope_in" := "output_row"."scope_out";
2277 "output_row"."member_valid" := EXISTS (
2278 SELECT NULL FROM "member" JOIN "privilege"
2279 ON "privilege"."member_id" = "member"."id"
2280 AND "privilege"."unit_id" = "unit_id_v"
2281 WHERE "id" = "output_row"."member_id"
2282 AND "member"."active" AND "privilege"."voting_right"
2283 );
2284 "simulate_here_v" := (
2285 "simulate_v" AND
2286 "output_row"."member_id" = "member_id_p"
2287 );
2288 "delegation_row" := ROW(NULL);
2289 IF "output_row"."member_valid" OR "simulate_here_v" THEN
2290 IF "scope_v" = 'unit' THEN
2291 IF NOT "simulate_here_v" THEN
2292 SELECT * INTO "delegation_row" FROM "delegation"
2293 WHERE "truster_id" = "output_row"."member_id"
2294 AND "unit_id" = "unit_id_v";
2295 END IF;
2296 ELSIF "scope_v" = 'area' THEN
2297 IF "simulate_here_v" THEN
2298 IF "simulate_trustee_id_p" ISNULL THEN
2299 SELECT * INTO "delegation_row" FROM "delegation"
2300 WHERE "truster_id" = "output_row"."member_id"
2301 AND "unit_id" = "unit_id_v";
2302 END IF;
2303 ELSE
2304 SELECT * INTO "delegation_row" FROM "delegation"
2305 WHERE "truster_id" = "output_row"."member_id"
2306 AND (
2307 "unit_id" = "unit_id_v" OR
2308 "area_id" = "area_id_v"
2309 )
2310 ORDER BY "scope" DESC;
2311 END IF;
2312 ELSIF "scope_v" = 'issue' THEN
2313 IF "issue_row"."fully_frozen" ISNULL THEN
2314 "output_row"."participation" := EXISTS (
2315 SELECT NULL FROM "interest"
2316 WHERE "issue_id" = "issue_id_p"
2317 AND "member_id" = "output_row"."member_id"
2318 );
2319 ELSE
2320 IF "output_row"."member_id" = "member_id_p" THEN
2321 "output_row"."participation" := EXISTS (
2322 SELECT NULL FROM "direct_voter"
2323 WHERE "issue_id" = "issue_id_p"
2324 AND "member_id" = "output_row"."member_id"
2325 );
2326 ELSE
2327 "output_row"."participation" := NULL;
2328 END IF;
2329 END IF;
2330 IF "simulate_here_v" THEN
2331 IF "simulate_trustee_id_p" ISNULL THEN
2332 SELECT * INTO "delegation_row" FROM "delegation"
2333 WHERE "truster_id" = "output_row"."member_id"
2334 AND (
2335 "unit_id" = "unit_id_v" OR
2336 "area_id" = "area_id_v"
2337 )
2338 ORDER BY "scope" DESC;
2339 END IF;
2340 ELSE
2341 SELECT * INTO "delegation_row" FROM "delegation"
2342 WHERE "truster_id" = "output_row"."member_id"
2343 AND (
2344 "unit_id" = "unit_id_v" OR
2345 "area_id" = "area_id_v" OR
2346 "issue_id" = "issue_id_p"
2347 )
2348 ORDER BY "scope" DESC;
2349 END IF;
2350 END IF;
2351 ELSE
2352 "output_row"."participation" := FALSE;
2353 END IF;
2354 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
2355 "output_row"."scope_out" := "scope_v";
2356 "output_rows" := "output_rows" || "output_row";
2357 "output_row"."member_id" := "simulate_trustee_id_p";
2358 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2359 "output_row"."scope_out" := "delegation_row"."scope";
2360 "output_rows" := "output_rows" || "output_row";
2361 "output_row"."member_id" := "delegation_row"."trustee_id";
2362 ELSIF "delegation_row"."scope" NOTNULL THEN
2363 "output_row"."scope_out" := "delegation_row"."scope";
2364 "output_row"."disabled_out" := TRUE;
2365 "output_rows" := "output_rows" || "output_row";
2366 EXIT;
2367 ELSE
2368 "output_row"."scope_out" := NULL;
2369 "output_rows" := "output_rows" || "output_row";
2370 EXIT;
2371 END IF;
2372 EXIT WHEN "loop_member_id_v" NOTNULL;
2373 "output_row"."index" := "output_row"."index" + 1;
2374 END LOOP;
2375 "row_count" := array_upper("output_rows", 1);
2376 "i" := 1;
2377 "loop_v" := FALSE;
2378 LOOP
2379 "output_row" := "output_rows"["i"];
2380 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2381 IF "loop_v" THEN
2382 IF "i" + 1 = "row_count" THEN
2383 "output_row"."loop" := 'last';
2384 ELSIF "i" = "row_count" THEN
2385 "output_row"."loop" := 'repetition';
2386 ELSE
2387 "output_row"."loop" := 'intermediate';
2388 END IF;
2389 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2390 "output_row"."loop" := 'first';
2391 "loop_v" := TRUE;
2392 END IF;
2393 IF "scope_v" = 'unit' THEN
2394 "output_row"."participation" := NULL;
2395 END IF;
2396 RETURN NEXT "output_row";
2397 "i" := "i" + 1;
2398 END LOOP;
2399 RETURN;
2400 END;
2401 $$;
2404 CREATE OR REPLACE FUNCTION "get_initiatives_for_notification"
2405 ( "recipient_id_p" "member"."id"%TYPE )
2406 RETURNS SETOF "initiative_for_notification"
2407 LANGUAGE 'plpgsql' VOLATILE AS $$
2408 DECLARE
2409 "result_row" "initiative_for_notification"%ROWTYPE;
2410 "last_draft_id_v" "draft"."id"%TYPE;
2411 "last_suggestion_id_v" "suggestion"."id"%TYPE;
2412 BEGIN
2413 PERFORM "require_transaction_isolation"();
2414 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
2415 FOR "result_row" IN
2416 SELECT * FROM "initiative_for_notification"
2417 WHERE "recipient_id" = "recipient_id_p"
2418 LOOP
2419 SELECT "id" INTO "last_draft_id_v" FROM "draft"
2420 WHERE "draft"."initiative_id" = "result_row"."initiative_id"
2421 ORDER BY "id" DESC LIMIT 1;
2422 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
2423 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
2424 ORDER BY "id" DESC LIMIT 1;
2425 INSERT INTO "notification_initiative_sent"
2426 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
2427 VALUES (
2428 "recipient_id_p",
2429 "result_row"."initiative_id",
2430 "last_draft_id_v",
2431 "last_suggestion_id_v" )
2432 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
2433 "last_draft_id" = "last_draft_id_v",
2434 "last_suggestion_id" = "last_suggestion_id_v";
2435 RETURN NEXT "result_row";
2436 END LOOP;
2437 DELETE FROM "notification_initiative_sent"
2438 USING "initiative", "issue"
2439 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
2440 AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
2441 AND "issue"."id" = "initiative"."issue_id"
2442 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
2443 UPDATE "member" SET
2444 "notification_counter" = "notification_counter" + 1,
2445 "notification_sent" = now()
2446 WHERE "id" = "recipient_id_p";
2447 RETURN;
2448 END;
2449 $$;
2452 CREATE OR REPLACE FUNCTION "calculate_member_counts"()
2453 RETURNS VOID
2454 LANGUAGE 'plpgsql' VOLATILE AS $$
2455 BEGIN
2456 PERFORM "require_transaction_isolation"();
2457 DELETE FROM "member_count";
2458 INSERT INTO "member_count" ("total_count")
2459 SELECT "total_count" FROM "member_count_view";
2460 UPDATE "unit" SET "member_count" = "view"."member_count"
2461 FROM "unit_member_count" AS "view"
2462 WHERE "view"."unit_id" = "unit"."id";
2463 RETURN;
2464 END;
2465 $$;
2467 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"';
2470 CREATE FUNCTION "calculate_area_quorum"()
2471 RETURNS VOID
2472 LANGUAGE 'plpgsql' VOLATILE AS $$
2473 BEGIN
2474 PERFORM "dont_require_transaction_isolation"();
2475 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
2476 FROM "area_quorum" AS "view"
2477 WHERE "view"."area_id" = "area"."id";
2478 RETURN;
2479 END;
2480 $$;
2482 COMMENT ON FUNCTION "calculate_area_quorum"() IS 'Calculate column "issue_quorum" in table "area" from view "area_quorum"';
2485 DROP VIEW "remaining_harmonic_initiative_weight_summands";
2486 DROP VIEW "remaining_harmonic_supporter_weight";
2489 CREATE VIEW "remaining_harmonic_supporter_weight" AS
2490 SELECT
2491 "direct_interest_snapshot"."snapshot_id",
2492 "direct_interest_snapshot"."issue_id",
2493 "direct_interest_snapshot"."member_id",
2494 "direct_interest_snapshot"."weight" AS "weight_num",
2495 count("initiative"."id") AS "weight_den"
2496 FROM "issue"
2497 JOIN "direct_interest_snapshot"
2498 ON "issue"."latest_snapshot_id" = "direct_interest_snapshot"."snapshot_id"
2499 AND "issue"."id" = "direct_interest_snapshot"."issue_id"
2500 JOIN "initiative"
2501 ON "issue"."id" = "initiative"."issue_id"
2502 AND "initiative"."harmonic_weight" ISNULL
2503 JOIN "direct_supporter_snapshot"
2504 ON "issue"."latest_snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
2505 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
2506 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
2507 AND (
2508 "direct_supporter_snapshot"."satisfied" = TRUE OR
2509 coalesce("initiative"."admitted", FALSE) = FALSE
2510 )
2511 GROUP BY
2512 "direct_interest_snapshot"."snapshot_id",
2513 "direct_interest_snapshot"."issue_id",
2514 "direct_interest_snapshot"."member_id",
2515 "direct_interest_snapshot"."weight";
2518 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
2519 SELECT
2520 "initiative"."issue_id",
2521 "initiative"."id" AS "initiative_id",
2522 "initiative"."admitted",
2523 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
2524 "remaining_harmonic_supporter_weight"."weight_den"
2525 FROM "remaining_harmonic_supporter_weight"
2526 JOIN "initiative"
2527 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
2528 AND "initiative"."harmonic_weight" ISNULL
2529 JOIN "direct_supporter_snapshot"
2530 ON "remaining_harmonic_supporter_weight"."snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
2531 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
2532 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
2533 AND (
2534 "direct_supporter_snapshot"."satisfied" = TRUE OR
2535 coalesce("initiative"."admitted", FALSE) = FALSE
2536 )
2537 GROUP BY
2538 "initiative"."issue_id",
2539 "initiative"."id",
2540 "initiative"."admitted",
2541 "remaining_harmonic_supporter_weight"."weight_den";
2544 DROP FUNCTION "create_population_snapshot"
2545 ( "issue_id_p" "issue"."id"%TYPE );
2548 DROP FUNCTION "weight_of_added_delegations_for_population_snapshot"
2549 ( "issue_id_p" "issue"."id"%TYPE,
2550 "member_id_p" "member"."id"%TYPE,
2551 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE );
2554 DROP FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2555 ( "issue_id_p" "issue"."id"%TYPE,
2556 "member_id_p" "member"."id"%TYPE,
2557 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE );
2560 CREATE FUNCTION "weight_of_added_delegations_for_snapshot"
2561 ( "snapshot_id_p" "snapshot"."id"%TYPE,
2562 "issue_id_p" "issue"."id"%TYPE,
2563 "member_id_p" "member"."id"%TYPE,
2564 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2565 RETURNS "direct_interest_snapshot"."weight"%TYPE
2566 LANGUAGE 'plpgsql' VOLATILE AS $$
2567 DECLARE
2568 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2569 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2570 "weight_v" INT4;
2571 "sub_weight_v" INT4;
2572 BEGIN
2573 PERFORM "require_transaction_isolation"();
2574 "weight_v" := 0;
2575 FOR "issue_delegation_row" IN
2576 SELECT * FROM "issue_delegation"
2577 WHERE "trustee_id" = "member_id_p"
2578 AND "issue_id" = "issue_id_p"
2579 LOOP
2580 IF NOT EXISTS (
2581 SELECT NULL FROM "direct_interest_snapshot"
2582 WHERE "snapshot_id" = "snapshot_id_p"
2583 AND "issue_id" = "issue_id_p"
2584 AND "member_id" = "issue_delegation_row"."truster_id"
2585 ) AND NOT EXISTS (
2586 SELECT NULL FROM "delegating_interest_snapshot"
2587 WHERE "snapshot_id" = "snapshot_id_p"
2588 AND "issue_id" = "issue_id_p"
2589 AND "member_id" = "issue_delegation_row"."truster_id"
2590 ) THEN
2591 "delegate_member_ids_v" :=
2592 "member_id_p" || "delegate_member_ids_p";
2593 INSERT INTO "delegating_interest_snapshot" (
2594 "snapshot_id",
2595 "issue_id",
2596 "member_id",
2597 "scope",
2598 "delegate_member_ids"
2599 ) VALUES (
2600 "snapshot_id_p",
2601 "issue_id_p",
2602 "issue_delegation_row"."truster_id",
2603 "issue_delegation_row"."scope",
2604 "delegate_member_ids_v"
2605 );
2606 "sub_weight_v" := 1 +
2607 "weight_of_added_delegations_for_snapshot"(
2608 "snapshot_id_p",
2609 "issue_id_p",
2610 "issue_delegation_row"."truster_id",
2611 "delegate_member_ids_v"
2612 );
2613 UPDATE "delegating_interest_snapshot"
2614 SET "weight" = "sub_weight_v"
2615 WHERE "snapshot_id" = "snapshot_id_p"
2616 AND "issue_id" = "issue_id_p"
2617 AND "member_id" = "issue_delegation_row"."truster_id";
2618 "weight_v" := "weight_v" + "sub_weight_v";
2619 END IF;
2620 END LOOP;
2621 RETURN "weight_v";
2622 END;
2623 $$;
2625 COMMENT ON FUNCTION "weight_of_added_delegations_for_snapshot"
2626 ( "snapshot"."id"%TYPE,
2627 "issue"."id"%TYPE,
2628 "member"."id"%TYPE,
2629 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2630 IS 'Helper function for "fill_snapshot" function';
2633 DROP FUNCTION "create_interest_snapshot"
2634 ( "issue_id_p" "issue"."id"%TYPE );
2637 DROP FUNCTION "create_snapshot"
2638 ( "issue_id_p" "issue"."id"%TYPE );
2641 CREATE FUNCTION "take_snapshot"
2642 ( "issue_id_p" "issue"."id"%TYPE,
2643 "area_id_p" "area"."id"%TYPE = NULL )
2644 RETURNS "snapshot"."id"%TYPE
2645 LANGUAGE 'plpgsql' VOLATILE AS $$
2646 DECLARE
2647 "area_id_v" "area"."id"%TYPE;
2648 "unit_id_v" "unit"."id"%TYPE;
2649 "snapshot_id_v" "snapshot"."id"%TYPE;
2650 "issue_id_v" "issue"."id"%TYPE;
2651 "member_id_v" "member"."id"%TYPE;
2652 BEGIN
2653 IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN
2654 RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL';
2655 END IF;
2656 PERFORM "require_transaction_isolation"();
2657 IF "issue_id_p" ISNULL THEN
2658 "area_id_v" := "area_id_p";
2659 ELSE
2660 SELECT "area_id" INTO "area_id_v"
2661 FROM "issue" WHERE "id" = "issue_id_p";
2662 END IF;
2663 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_p";
2664 INSERT INTO "snapshot" ("area_id", "issue_id")
2665 VALUES ("area_id_v", "issue_id_p")
2666 RETURNING "id" INTO "snapshot_id_v";
2667 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
2668 SELECT "snapshot_id_v", "member_id"
2669 FROM "unit_member" WHERE "unit_id" = "unit_id_v";
2670 UPDATE "snapshot" SET
2671 "population" = (
2672 SELECT count(1) FROM "snapshot_population"
2673 WHERE "snapshot_id" = "snapshot_id_v"
2674 ) WHERE "id" = "snapshot_id_v";
2675 FOR "issue_id_v" IN
2676 SELECT "id" FROM "issue"
2677 WHERE CASE WHEN "issue_id_p" ISNULL THEN
2678 "area_id" = "area_id_p" AND
2679 "state" = 'admission'
2680 ELSE
2681 "id" = "issue_id_p"
2682 END
2683 LOOP
2684 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
2685 VALUES ("snapshot_id_v", "issue_id_v");
2686 INSERT INTO "direct_interest_snapshot"
2687 ("snapshot_id", "issue_id", "member_id")
2688 SELECT
2689 "snapshot_id_v" AS "snapshot_id",
2690 "issue_id_v" AS "issue_id",
2691 "member"."id" AS "member_id"
2692 FROM "issue"
2693 JOIN "area" ON "issue"."area_id" = "area"."id"
2694 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2695 JOIN "member" ON "interest"."member_id" = "member"."id"
2696 JOIN "privilege"
2697 ON "privilege"."unit_id" = "area"."unit_id"
2698 AND "privilege"."member_id" = "member"."id"
2699 WHERE "issue"."id" = "issue_id_v"
2700 AND "member"."active" AND "privilege"."voting_right";
2701 FOR "member_id_v" IN
2702 SELECT "member_id" FROM "direct_interest_snapshot"
2703 WHERE "snapshot_id" = "snapshot_id_v"
2704 AND "issue_id" = "issue_id_v"
2705 LOOP
2706 UPDATE "direct_interest_snapshot" SET
2707 "weight" = 1 +
2708 "weight_of_added_delegations_for_snapshot"(
2709 "snapshot_id_v",
2710 "issue_id_v",
2711 "member_id_v",
2712 '{}'
2713 )
2714 WHERE "snapshot_id" = "snapshot_id_v"
2715 AND "issue_id" = "issue_id_v"
2716 AND "member_id" = "member_id_v";
2717 END LOOP;
2718 INSERT INTO "direct_supporter_snapshot"
2719 ( "snapshot_id", "issue_id", "initiative_id", "member_id",
2720 "draft_id", "informed", "satisfied" )
2721 SELECT
2722 "snapshot_id_v" AS "snapshot_id",
2723 "issue_id_v" AS "issue_id",
2724 "initiative"."id" AS "initiative_id",
2725 "supporter"."member_id" AS "member_id",
2726 "supporter"."draft_id" AS "draft_id",
2727 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2728 NOT EXISTS (
2729 SELECT NULL FROM "critical_opinion"
2730 WHERE "initiative_id" = "initiative"."id"
2731 AND "member_id" = "supporter"."member_id"
2732 ) AS "satisfied"
2733 FROM "initiative"
2734 JOIN "supporter"
2735 ON "supporter"."initiative_id" = "initiative"."id"
2736 JOIN "current_draft"
2737 ON "initiative"."id" = "current_draft"."initiative_id"
2738 JOIN "direct_interest_snapshot"
2739 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"
2740 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"
2741 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
2742 WHERE "initiative"."issue_id" = "issue_id_v";
2743 DELETE FROM "temporary_suggestion_counts";
2744 INSERT INTO "temporary_suggestion_counts"
2745 ( "id",
2746 "minus2_unfulfilled_count", "minus2_fulfilled_count",
2747 "minus1_unfulfilled_count", "minus1_fulfilled_count",
2748 "plus1_unfulfilled_count", "plus1_fulfilled_count",
2749 "plus2_unfulfilled_count", "plus2_fulfilled_count" )
2750 SELECT
2751 "suggestion"."id",
2752 ( SELECT coalesce(sum("di"."weight"), 0)
2753 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2754 ON "di"."snapshot_id" = "snapshot_id_v"
2755 AND "di"."issue_id" = "issue_id_v"
2756 AND "di"."member_id" = "opinion"."member_id"
2757 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2758 AND "opinion"."degree" = -2
2759 AND "opinion"."fulfilled" = FALSE
2760 ) AS "minus2_unfulfilled_count",
2761 ( SELECT coalesce(sum("di"."weight"), 0)
2762 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2763 ON "di"."snapshot_id" = "snapshot_id_v"
2764 AND "di"."issue_id" = "issue_id_v"
2765 AND "di"."member_id" = "opinion"."member_id"
2766 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2767 AND "opinion"."degree" = -2
2768 AND "opinion"."fulfilled" = TRUE
2769 ) AS "minus2_fulfilled_count",
2770 ( SELECT coalesce(sum("di"."weight"), 0)
2771 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2772 ON "di"."snapshot_id" = "snapshot_id_v"
2773 AND "di"."issue_id" = "issue_id_v"
2774 AND "di"."member_id" = "opinion"."member_id"
2775 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2776 AND "opinion"."degree" = -1
2777 AND "opinion"."fulfilled" = FALSE
2778 ) AS "minus1_unfulfilled_count",
2779 ( SELECT coalesce(sum("di"."weight"), 0)
2780 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2781 ON "di"."snapshot_id" = "snapshot_id_v"
2782 AND "di"."issue_id" = "issue_id_v"
2783 AND "di"."member_id" = "opinion"."member_id"
2784 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2785 AND "opinion"."degree" = -1
2786 AND "opinion"."fulfilled" = TRUE
2787 ) AS "minus1_fulfilled_count",
2788 ( SELECT coalesce(sum("di"."weight"), 0)
2789 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2790 ON "di"."snapshot_id" = "snapshot_id_v"
2791 AND "di"."issue_id" = "issue_id_v"
2792 AND "di"."member_id" = "opinion"."member_id"
2793 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2794 AND "opinion"."degree" = 1
2795 AND "opinion"."fulfilled" = FALSE
2796 ) AS "plus1_unfulfilled_count",
2797 ( SELECT coalesce(sum("di"."weight"), 0)
2798 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2799 ON "di"."snapshot_id" = "snapshot_id_v"
2800 AND "di"."issue_id" = "issue_id_v"
2801 AND "di"."member_id" = "opinion"."member_id"
2802 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2803 AND "opinion"."degree" = 1
2804 AND "opinion"."fulfilled" = TRUE
2805 ) AS "plus1_fulfilled_count",
2806 ( SELECT coalesce(sum("di"."weight"), 0)
2807 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2808 ON "di"."snapshot_id" = "snapshot_id_v"
2809 AND "di"."issue_id" = "issue_id_v"
2810 AND "di"."member_id" = "opinion"."member_id"
2811 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2812 AND "opinion"."degree" = 2
2813 AND "opinion"."fulfilled" = FALSE
2814 ) AS "plus2_unfulfilled_count",
2815 ( SELECT coalesce(sum("di"."weight"), 0)
2816 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2817 ON "di"."snapshot_id" = "snapshot_id_v"
2818 AND "di"."issue_id" = "issue_id_v"
2819 AND "di"."member_id" = "opinion"."member_id"
2820 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2821 AND "opinion"."degree" = 2
2822 AND "opinion"."fulfilled" = TRUE
2823 ) AS "plus2_fulfilled_count"
2824 FROM "suggestion" JOIN "initiative"
2825 ON "suggestion"."initiative_id" = "initiative"."id"
2826 WHERE "initiative"."issue_id" = "issue_id_v";
2827 END LOOP;
2828 RETURN "snapshot_id_v";
2829 END;
2830 $$;
2832 COMMENT ON FUNCTION "take_snapshot"
2833 ( "issue"."id"%TYPE,
2834 "area"."id"%TYPE )
2835 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.';
2838 DROP FUNCTION "set_snapshot_event"
2839 ( "issue_id_p" "issue"."id"%TYPE,
2840 "event_p" "snapshot_event" );
2843 CREATE FUNCTION "finish_snapshot"
2844 ( "issue_id_p" "issue"."id"%TYPE )
2845 RETURNS VOID
2846 LANGUAGE 'plpgsql' VOLATILE AS $$
2847 DECLARE
2848 "snapshot_id_v" "snapshot"."id"%TYPE;
2849 BEGIN
2850 -- NOTE: function does not require snapshot isolation but we don't call
2851 -- "dont_require_snapshot_isolation" here because this function is
2852 -- also invoked by "check_issue"
2853 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
2854 SELECT "id" INTO "snapshot_id_v" FROM "snapshot"
2855 ORDER BY "id" DESC LIMIT 1;
2856 UPDATE "issue" SET
2857 "calculated" = "snapshot"."calculated",
2858 "latest_snapshot_id" = "snapshot_id_v",
2859 "population" = "snapshot"."population"
2860 FROM "snapshot"
2861 WHERE "issue"."id" = "issue_id_p"
2862 AND "snapshot"."id" = "snapshot_id_v";
2863 UPDATE "initiative" SET
2864 "supporter_count" = (
2865 SELECT coalesce(sum("di"."weight"), 0)
2866 FROM "direct_interest_snapshot" AS "di"
2867 JOIN "direct_supporter_snapshot" AS "ds"
2868 ON "di"."member_id" = "ds"."member_id"
2869 WHERE "di"."snapshot_id" = "snapshot_id_v"
2870 AND "di"."issue_id" = "issue_id_p"
2871 AND "ds"."snapshot_id" = "snapshot_id_v"
2872 AND "ds"."initiative_id" = "initiative"."id"
2873 ),
2874 "informed_supporter_count" = (
2875 SELECT coalesce(sum("di"."weight"), 0)
2876 FROM "direct_interest_snapshot" AS "di"
2877 JOIN "direct_supporter_snapshot" AS "ds"
2878 ON "di"."member_id" = "ds"."member_id"
2879 WHERE "di"."snapshot_id" = "snapshot_id_v"
2880 AND "di"."issue_id" = "issue_id_p"
2881 AND "ds"."snapshot_id" = "snapshot_id_v"
2882 AND "ds"."initiative_id" = "initiative"."id"
2883 AND "ds"."informed"
2884 ),
2885 "satisfied_supporter_count" = (
2886 SELECT coalesce(sum("di"."weight"), 0)
2887 FROM "direct_interest_snapshot" AS "di"
2888 JOIN "direct_supporter_snapshot" AS "ds"
2889 ON "di"."member_id" = "ds"."member_id"
2890 WHERE "di"."snapshot_id" = "snapshot_id_v"
2891 AND "di"."issue_id" = "issue_id_p"
2892 AND "ds"."snapshot_id" = "snapshot_id_v"
2893 AND "ds"."initiative_id" = "initiative"."id"
2894 AND "ds"."satisfied"
2895 ),
2896 "satisfied_informed_supporter_count" = (
2897 SELECT coalesce(sum("di"."weight"), 0)
2898 FROM "direct_interest_snapshot" AS "di"
2899 JOIN "direct_supporter_snapshot" AS "ds"
2900 ON "di"."member_id" = "ds"."member_id"
2901 WHERE "di"."snapshot_id" = "snapshot_id_v"
2902 AND "di"."issue_id" = "issue_id_p"
2903 AND "ds"."snapshot_id" = "snapshot_id_v"
2904 AND "ds"."initiative_id" = "initiative"."id"
2905 AND "ds"."informed"
2906 AND "ds"."satisfied"
2907 )
2908 WHERE "issue_id" = "issue_id_p";
2909 UPDATE "suggestion" SET
2910 "minus2_unfulfilled_count" = "temp"."minus2_unfulfilled_count",
2911 "minus2_fulfilled_count" = "temp"."minus2_fulfilled_count",
2912 "minus1_unfulfilled_count" = "temp"."minus1_unfulfilled_count",
2913 "minus1_fulfilled_count" = "temp"."minus1_fulfilled_count",
2914 "plus1_unfulfilled_count" = "temp"."plus1_unfulfilled_count",
2915 "plus1_fulfilled_count" = "temp"."plus1_fulfilled_count",
2916 "plus2_unfulfilled_count" = "temp"."plus2_unfulfilled_count",
2917 "plus2_fulfilled_count" = "temp"."plus2_fulfilled_count"
2918 FROM "temporary_suggestion_counts" AS "temp", "initiative"
2919 WHERE "temp"."id" = "suggestion"."id"
2920 AND "initiative"."issue_id" = "issue_id_p"
2921 AND "suggestion"."initiative_id" = "initiative"."id";
2922 DELETE FROM "temporary_suggestion_counts";
2923 RETURN;
2924 END;
2925 $$;
2927 COMMENT ON FUNCTION "finish_snapshot"
2928 ( "issue"."id"%TYPE )
2929 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)';
2932 CREATE FUNCTION "issue_admission"
2933 ( "area_id_p" "area"."id"%TYPE )
2934 RETURNS BOOLEAN
2935 LANGUAGE 'plpgsql' VOLATILE AS $$
2936 DECLARE
2937 "issue_id_v" "issue"."id"%TYPE;
2938 BEGIN
2939 PERFORM "dont_require_transaction_isolation"();
2940 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
2941 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
2942 FROM "area_quorum" AS "view"
2943 WHERE "area"."id" = "view"."area_id"
2944 AND "area"."id" = "area_id_p";
2945 SELECT "id" INTO "issue_id_v" FROM "issue_for_admission"
2946 WHERE "area_id" = "area_id_p";
2947 IF "issue_id_v" ISNULL THEN RETURN FALSE; END IF;
2948 UPDATE "issue" SET
2949 "admission_snapshot_id" = "latest_snapshot_id",
2950 "state" = 'discussion',
2951 "accepted" = now(),
2952 "phase_finished" = NULL
2953 WHERE "id" = "issue_id_v";
2954 RETURN TRUE;
2955 END;
2956 $$;
2958 COMMENT ON FUNCTION "issue_admission"
2959 ( "area"."id"%TYPE )
2960 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';
2963 CREATE OR REPLACE FUNCTION "check_issue"
2964 ( "issue_id_p" "issue"."id"%TYPE,
2965 "persist" "check_issue_persistence" )
2966 RETURNS "check_issue_persistence"
2967 LANGUAGE 'plpgsql' VOLATILE AS $$
2968 DECLARE
2969 "issue_row" "issue"%ROWTYPE;
2970 "last_calculated_v" "snapshot"."calculated"%TYPE;
2971 "policy_row" "policy"%ROWTYPE;
2972 "initiative_row" "initiative"%ROWTYPE;
2973 "state_v" "issue_state";
2974 BEGIN
2975 PERFORM "require_transaction_isolation"();
2976 IF "persist" ISNULL THEN
2977 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
2978 FOR UPDATE;
2979 SELECT "calculated" INTO "last_calculated_v"
2980 FROM "snapshot" JOIN "snapshot_issue"
2981 ON "snapshot"."id" = "snapshot_issue"."snapshot_id"
2982 WHERE "snapshot_issue"."issue_id" = "issue_id_p";
2983 IF "issue_row"."closed" NOTNULL THEN
2984 RETURN NULL;
2985 END IF;
2986 "persist"."state" := "issue_row"."state";
2987 IF
2988 ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=
2989 "issue_row"."created" + "issue_row"."max_admission_time" ) OR
2990 ( "issue_row"."state" = 'discussion' AND now() >=
2991 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
2992 ( "issue_row"."state" = 'verification' AND now() >=
2993 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
2994 ( "issue_row"."state" = 'voting' AND now() >=
2995 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
2996 THEN
2997 "persist"."phase_finished" := TRUE;
2998 ELSE
2999 "persist"."phase_finished" := FALSE;
3000 END IF;
3001 IF
3002 NOT EXISTS (
3003 -- all initiatives are revoked
3004 SELECT NULL FROM "initiative"
3005 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3006 ) AND (
3007 -- and issue has not been accepted yet
3008 "persist"."state" = 'admission' OR
3009 -- or verification time has elapsed
3010 ( "persist"."state" = 'verification' AND
3011 "persist"."phase_finished" ) OR
3012 -- or no initiatives have been revoked lately
3013 NOT EXISTS (
3014 SELECT NULL FROM "initiative"
3015 WHERE "issue_id" = "issue_id_p"
3016 AND now() < "revoked" + "issue_row"."verification_time"
3017 )
3018 )
3019 THEN
3020 "persist"."issue_revoked" := TRUE;
3021 ELSE
3022 "persist"."issue_revoked" := FALSE;
3023 END IF;
3024 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
3025 UPDATE "issue" SET "phase_finished" = now()
3026 WHERE "id" = "issue_row"."id";
3027 RETURN "persist";
3028 ELSIF
3029 "persist"."state" IN ('admission', 'discussion', 'verification')
3030 THEN
3031 RETURN "persist";
3032 ELSE
3033 RETURN NULL;
3034 END IF;
3035 END IF;
3036 IF
3037 "persist"."state" IN ('admission', 'discussion', 'verification') AND
3038 coalesce("persist"."snapshot_created", FALSE) = FALSE
3039 THEN
3040 IF "persist"."state" != 'admission' THEN
3041 PERFORM "take_snapshot"("issue_id_p");
3042 PERFORM "finish_snapshot"("issue_id_p");
3043 END IF;
3044 "persist"."snapshot_created" = TRUE;
3045 IF "persist"."phase_finished" THEN
3046 IF "persist"."state" = 'admission' THEN
3047 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id";
3048 ELSIF "persist"."state" = 'discussion' THEN
3049 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id";
3050 ELSIF "persist"."state" = 'verification' THEN
3051 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id";
3052 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3053 SELECT * INTO "policy_row" FROM "policy"
3054 WHERE "id" = "issue_row"."policy_id";
3055 FOR "initiative_row" IN
3056 SELECT * FROM "initiative"
3057 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3058 FOR UPDATE
3059 LOOP
3060 IF
3061 "initiative_row"."polling" OR (
3062 "initiative_row"."satisfied_supporter_count" >
3063 "policy_row"."initiative_quorum" AND
3064 "initiative_row"."satisfied_supporter_count" *
3065 "policy_row"."initiative_quorum_den" >=
3066 "issue_row"."population" * "policy_row"."initiative_quorum_num"
3067 )
3068 THEN
3069 UPDATE "initiative" SET "admitted" = TRUE
3070 WHERE "id" = "initiative_row"."id";
3071 ELSE
3072 UPDATE "initiative" SET "admitted" = FALSE
3073 WHERE "id" = "initiative_row"."id";
3074 END IF;
3075 END LOOP;
3076 END IF;
3077 END IF;
3078 RETURN "persist";
3079 END IF;
3080 IF
3081 "persist"."state" IN ('admission', 'discussion', 'verification') AND
3082 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
3083 THEN
3084 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
3085 "persist"."harmonic_weights_set" = TRUE;
3086 IF
3087 "persist"."phase_finished" OR
3088 "persist"."issue_revoked" OR
3089 "persist"."state" = 'admission'
3090 THEN
3091 RETURN "persist";
3092 ELSE
3093 RETURN NULL;
3094 END IF;
3095 END IF;
3096 IF "persist"."issue_revoked" THEN
3097 IF "persist"."state" = 'admission' THEN
3098 "state_v" := 'canceled_revoked_before_accepted';
3099 ELSIF "persist"."state" = 'discussion' THEN
3100 "state_v" := 'canceled_after_revocation_during_discussion';
3101 ELSIF "persist"."state" = 'verification' THEN
3102 "state_v" := 'canceled_after_revocation_during_verification';
3103 END IF;
3104 UPDATE "issue" SET
3105 "state" = "state_v",
3106 "closed" = "phase_finished",
3107 "phase_finished" = NULL
3108 WHERE "id" = "issue_id_p";
3109 RETURN NULL;
3110 END IF;
3111 IF "persist"."state" = 'admission' THEN
3112 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
3113 FOR UPDATE;
3114 IF "issue_row"."phase_finished" NOTNULL THEN
3115 UPDATE "issue" SET
3116 "state" = 'canceled_issue_not_accepted',
3117 "closed" = "phase_finished",
3118 "phase_finished" = NULL
3119 WHERE "id" = "issue_id_p";
3120 END IF;
3121 RETURN NULL;
3122 END IF;
3123 IF "persist"."phase_finished" THEN
3124 IF "persist"."state" = 'discussion' THEN
3125 UPDATE "issue" SET
3126 "state" = 'verification',
3127 "half_frozen" = "phase_finished",
3128 "phase_finished" = NULL
3129 WHERE "id" = "issue_id_p";
3130 RETURN NULL;
3131 END IF;
3132 IF "persist"."state" = 'verification' THEN
3133 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
3134 FOR UPDATE;
3135 SELECT * INTO "policy_row" FROM "policy"
3136 WHERE "id" = "issue_row"."policy_id";
3137 IF EXISTS (
3138 SELECT NULL FROM "initiative"
3139 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
3140 ) THEN
3141 UPDATE "issue" SET
3142 "state" = 'voting',
3143 "fully_frozen" = "phase_finished",
3144 "phase_finished" = NULL
3145 WHERE "id" = "issue_id_p";
3146 ELSE
3147 UPDATE "issue" SET
3148 "state" = 'canceled_no_initiative_admitted',
3149 "fully_frozen" = "phase_finished",
3150 "closed" = "phase_finished",
3151 "phase_finished" = NULL
3152 WHERE "id" = "issue_id_p";
3153 -- NOTE: The following DELETE statements have effect only when
3154 -- issue state has been manipulated
3155 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
3156 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
3157 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3158 END IF;
3159 RETURN NULL;
3160 END IF;
3161 IF "persist"."state" = 'voting' THEN
3162 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
3163 PERFORM "close_voting"("issue_id_p");
3164 "persist"."closed_voting" = TRUE;
3165 RETURN "persist";
3166 END IF;
3167 PERFORM "calculate_ranks"("issue_id_p");
3168 RETURN NULL;
3169 END IF;
3170 END IF;
3171 RAISE WARNING 'should not happen';
3172 RETURN NULL;
3173 END;
3174 $$;
3177 CREATE OR REPLACE FUNCTION "check_everything"()
3178 RETURNS VOID
3179 LANGUAGE 'plpgsql' VOLATILE AS $$
3180 DECLARE
3181 "area_id_v" "area"."id"%TYPE;
3182 "snapshot_id_v" "snapshot"."id"%TYPE;
3183 "issue_id_v" "issue"."id"%TYPE;
3184 "persist_v" "check_issue_persistence";
3185 BEGIN
3186 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
3187 DELETE FROM "expired_session";
3188 DELETE FROM "expired_token";
3189 DELETE FROM "expired_snapshot";
3190 PERFORM "check_activity"();
3191 PERFORM "calculate_member_counts"();
3192 FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP
3193 SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v";
3194 PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"
3195 WHERE "snapshot_id" = "snapshot_id_v";
3196 LOOP
3197 EXIT WHEN "issue_admission"("area_id_v") = FALSE;
3198 END LOOP;
3199 END LOOP;
3200 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
3201 "persist_v" := NULL;
3202 LOOP
3203 "persist_v" := "check_issue"("issue_id_v", "persist_v");
3204 EXIT WHEN "persist_v" ISNULL;
3205 END LOOP;
3206 END LOOP;
3207 RETURN;
3208 END;
3209 $$;
3211 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';
3214 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
3215 RETURNS VOID
3216 LANGUAGE 'plpgsql' VOLATILE AS $$
3217 BEGIN
3218 IF EXISTS (
3219 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
3220 ) THEN
3221 -- override protection triggers:
3222 INSERT INTO "temporary_transaction_data" ("key", "value")
3223 VALUES ('override_protection_triggers', TRUE::TEXT);
3224 -- clean data:
3225 DELETE FROM "delegating_voter"
3226 WHERE "issue_id" = "issue_id_p";
3227 DELETE FROM "direct_voter"
3228 WHERE "issue_id" = "issue_id_p";
3229 DELETE FROM "delegating_interest_snapshot"
3230 WHERE "issue_id" = "issue_id_p";
3231 DELETE FROM "direct_interest_snapshot"
3232 WHERE "issue_id" = "issue_id_p";
3233 DELETE FROM "non_voter"
3234 WHERE "issue_id" = "issue_id_p";
3235 DELETE FROM "delegation"
3236 WHERE "issue_id" = "issue_id_p";
3237 DELETE FROM "supporter"
3238 USING "initiative" -- NOTE: due to missing index on issue_id
3239 WHERE "initiative"."issue_id" = "issue_id_p"
3240 AND "supporter"."initiative_id" = "initiative_id";
3241 -- mark issue as cleaned:
3242 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
3243 -- finish overriding protection triggers (avoids garbage):
3244 DELETE FROM "temporary_transaction_data"
3245 WHERE "key" = 'override_protection_triggers';
3246 END IF;
3247 RETURN;
3248 END;
3249 $$;
3252 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
3253 RETURNS VOID
3254 LANGUAGE 'plpgsql' VOLATILE AS $$
3255 BEGIN
3256 UPDATE "member" SET
3257 "last_login" = NULL,
3258 "last_delegation_check" = NULL,
3259 "login" = NULL,
3260 "password" = NULL,
3261 "authority" = NULL,
3262 "authority_uid" = NULL,
3263 "authority_login" = NULL,
3264 "locked" = TRUE,
3265 "active" = FALSE,
3266 "notify_email" = NULL,
3267 "notify_email_unconfirmed" = NULL,
3268 "notify_email_secret" = NULL,
3269 "notify_email_secret_expiry" = NULL,
3270 "notify_email_lock_expiry" = NULL,
3271 "disable_notifications" = TRUE,
3272 "notification_counter" = DEFAULT,
3273 "notification_sample_size" = 0,
3274 "notification_dow" = NULL,
3275 "notification_hour" = NULL,
3276 "notification_sent" = NULL,
3277 "login_recovery_expiry" = NULL,
3278 "password_reset_secret" = NULL,
3279 "password_reset_secret_expiry" = NULL,
3280 "location" = NULL
3281 WHERE "id" = "member_id_p";
3282 -- "text_search_data" is updated by triggers
3283 DELETE FROM "member_settings" WHERE "member_id" = "member_id_p";
3284 DELETE FROM "member_profile" WHERE "member_id" = "member_id_p";
3285 DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";
3286 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
3287 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
3288 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
3289 DELETE FROM "session" WHERE "member_id" = "member_id_p";
3290 DELETE FROM "member_application" WHERE "member_id" = "member_id_p";
3291 DELETE FROM "token" WHERE "member_id" = "member_id_p";
3292 DELETE FROM "subscription" WHERE "member_id" = "member_id_p";
3293 DELETE FROM "ignored_area" WHERE "member_id" = "member_id_p";
3294 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
3295 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
3296 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
3297 DELETE FROM "direct_voter" USING "issue"
3298 WHERE "direct_voter"."issue_id" = "issue"."id"
3299 AND "issue"."closed" ISNULL
3300 AND "member_id" = "member_id_p";
3301 DELETE FROM "notification_initiative_sent" WHERE "member_id" = "member_id_p";
3302 RETURN;
3303 END;
3304 $$;
3307 CREATE OR REPLACE FUNCTION "delete_private_data"()
3308 RETURNS VOID
3309 LANGUAGE 'plpgsql' VOLATILE AS $$
3310 BEGIN
3311 DELETE FROM "temporary_transaction_data";
3312 DELETE FROM "temporary_suggestion_counts";
3313 DELETE FROM "member" WHERE "activated" ISNULL;
3314 UPDATE "member" SET
3315 "invite_code" = NULL,
3316 "invite_code_expiry" = NULL,
3317 "admin_comment" = NULL,
3318 "last_login" = NULL,
3319 "last_delegation_check" = NULL,
3320 "login" = NULL,
3321 "password" = NULL,
3322 "authority" = NULL,
3323 "authority_uid" = NULL,
3324 "authority_login" = NULL,
3325 "lang" = NULL,
3326 "notify_email" = NULL,
3327 "notify_email_unconfirmed" = NULL,
3328 "notify_email_secret" = NULL,
3329 "notify_email_secret_expiry" = NULL,
3330 "notify_email_lock_expiry" = NULL,
3331 "disable_notifications" = TRUE,
3332 "notification_counter" = DEFAULT,
3333 "notification_sample_size" = 0,
3334 "notification_dow" = NULL,
3335 "notification_hour" = NULL,
3336 "notification_sent" = NULL,
3337 "login_recovery_expiry" = NULL,
3338 "password_reset_secret" = NULL,
3339 "password_reset_secret_expiry" = NULL,
3340 "location" = NULL;
3341 -- "text_search_data" is updated by triggers
3342 DELETE FROM "member_settings";
3343 DELETE FROM "member_useterms";
3344 DELETE FROM "member_profile";
3345 DELETE FROM "rendered_member_statement";
3346 DELETE FROM "member_image";
3347 DELETE FROM "contact";
3348 DELETE FROM "ignored_member";
3349 DELETE FROM "session";
3350 DELETE FROM "system_application";
3351 DELETE FROM "system_application_redirect_uri";
3352 DELETE FROM "dynamic_application_scope";
3353 DELETE FROM "member_application";
3354 DELETE FROM "token";
3355 DELETE FROM "subscription";
3356 DELETE FROM "ignored_area";
3357 DELETE FROM "ignored_initiative";
3358 DELETE FROM "non_voter";
3359 DELETE FROM "direct_voter" USING "issue"
3360 WHERE "direct_voter"."issue_id" = "issue"."id"
3361 AND "issue"."closed" ISNULL;
3362 DELETE FROM "event_processed";
3363 DELETE FROM "notification_initiative_sent";
3364 DELETE FROM "newsletter";
3365 RETURN;
3366 END;
3367 $$;
3370 CREATE TEMPORARY TABLE "old_snapshot" AS
3371 SELECT "ordered".*, row_number() OVER () AS "snapshot_id"
3372 FROM (
3373 SELECT * FROM (
3374 SELECT
3375 "id" AS "issue_id",
3376 'end_of_admission'::"snapshot_event" AS "event",
3377 "accepted" AS "calculated"
3378 FROM "issue" WHERE "accepted" NOTNULL
3379 UNION ALL
3380 SELECT
3381 "id" AS "issue_id",
3382 'half_freeze'::"snapshot_event" AS "event",
3383 "half_frozen" AS "calculated"
3384 FROM "issue" WHERE "half_frozen" NOTNULL
3385 UNION ALL
3386 SELECT
3387 "id" AS "issue_id",
3388 'full_freeze'::"snapshot_event" AS "event",
3389 "fully_frozen" AS "calculated"
3390 FROM "issue" WHERE "fully_frozen" NOTNULL
3391 ) AS "unordered"
3392 ORDER BY "calculated", "issue_id", "event"
3393 ) AS "ordered";
3396 INSERT INTO "snapshot" ("id", "calculated", "population", "area_id", "issue_id")
3397 SELECT
3398 "old_snapshot"."snapshot_id" AS "id",
3399 "old_snapshot"."calculated",
3400 ( SELECT COALESCE(sum("weight"), 0)
3401 FROM "direct_population_snapshot" "dps"
3402 WHERE "dps"."issue_id" = "old_snapshot"."issue_id"
3403 AND "dps"."event" = "old_snapshot"."event"
3404 ) AS "population",
3405 "issue"."area_id" AS "area_id",
3406 "issue"."id" AS "issue_id"
3407 FROM "old_snapshot" JOIN "issue"
3408 ON "old_snapshot"."issue_id" = "issue"."id";
3411 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
3412 SELECT "id" AS "snapshot_id", "issue_id" FROM "snapshot";
3415 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
3416 SELECT
3417 "old_snapshot"."snapshot_id",
3418 "direct_population_snapshot"."member_id"
3419 FROM "old_snapshot" JOIN "direct_population_snapshot"
3420 ON "old_snapshot"."issue_id" = "direct_population_snapshot"."issue_id"
3421 AND "old_snapshot"."event" = "direct_population_snapshot"."event";
3423 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
3424 SELECT
3425 "old_snapshot"."snapshot_id",
3426 "delegating_population_snapshot"."member_id"
3427 FROM "old_snapshot" JOIN "delegating_population_snapshot"
3428 ON "old_snapshot"."issue_id" = "delegating_population_snapshot"."issue_id"
3429 AND "old_snapshot"."event" = "delegating_population_snapshot"."event";
3432 INSERT INTO "direct_interest_snapshot"
3433 ("snapshot_id", "issue_id", "member_id", "weight")
3434 SELECT
3435 "old_snapshot"."snapshot_id",
3436 "old_snapshot"."issue_id",
3437 "direct_interest_snapshot_old"."member_id",
3438 "direct_interest_snapshot_old"."weight"
3439 FROM "old_snapshot" JOIN "direct_interest_snapshot_old"
3440 ON "old_snapshot"."issue_id" = "direct_interest_snapshot_old"."issue_id"
3441 AND "old_snapshot"."event" = "direct_interest_snapshot_old"."event";
3443 INSERT INTO "delegating_interest_snapshot"
3444 ( "snapshot_id", "issue_id",
3445 "member_id", "weight", "scope", "delegate_member_ids" )
3446 SELECT
3447 "old_snapshot"."snapshot_id",
3448 "old_snapshot"."issue_id",
3449 "delegating_interest_snapshot_old"."member_id",
3450 "delegating_interest_snapshot_old"."weight",
3451 "delegating_interest_snapshot_old"."scope",
3452 "delegating_interest_snapshot_old"."delegate_member_ids"
3453 FROM "old_snapshot" JOIN "delegating_interest_snapshot_old"
3454 ON "old_snapshot"."issue_id" = "delegating_interest_snapshot_old"."issue_id"
3455 AND "old_snapshot"."event" = "delegating_interest_snapshot_old"."event";
3457 INSERT INTO "direct_supporter_snapshot"
3458 ( "snapshot_id", "issue_id",
3459 "initiative_id", "member_id", "draft_id", "informed", "satisfied" )
3460 SELECT
3461 "old_snapshot"."snapshot_id",
3462 "old_snapshot"."issue_id",
3463 "direct_supporter_snapshot_old"."initiative_id",
3464 "direct_supporter_snapshot_old"."member_id",
3465 "direct_supporter_snapshot_old"."draft_id",
3466 "direct_supporter_snapshot_old"."informed",
3467 "direct_supporter_snapshot_old"."satisfied"
3468 FROM "old_snapshot" JOIN "direct_supporter_snapshot_old"
3469 ON "old_snapshot"."issue_id" = "direct_supporter_snapshot_old"."issue_id"
3470 AND "old_snapshot"."event" = "direct_supporter_snapshot_old"."event";
3473 ALTER TABLE "issue" DISABLE TRIGGER USER; -- NOTE: required to modify table later
3475 UPDATE "issue" SET "latest_snapshot_id" = "snapshot"."id"
3476 FROM (
3477 SELECT DISTINCT ON ("issue_id") "issue_id", "id"
3478 FROM "snapshot" ORDER BY "issue_id", "id" DESC
3479 ) AS "snapshot"
3480 WHERE "snapshot"."issue_id" = "issue"."id";
3482 UPDATE "issue" SET "admission_snapshot_id" = "old_snapshot"."snapshot_id"
3483 FROM "old_snapshot"
3484 WHERE "old_snapshot"."issue_id" = "issue"."id"
3485 AND "old_snapshot"."event" = 'end_of_admission';
3487 UPDATE "issue" SET "half_freeze_snapshot_id" = "old_snapshot"."snapshot_id"
3488 FROM "old_snapshot"
3489 WHERE "old_snapshot"."issue_id" = "issue"."id"
3490 AND "old_snapshot"."event" = 'half_freeze';
3492 UPDATE "issue" SET "full_freeze_snapshot_id" = "old_snapshot"."snapshot_id"
3493 FROM "old_snapshot"
3494 WHERE "old_snapshot"."issue_id" = "issue"."id"
3495 AND "old_snapshot"."event" = 'full_freeze';
3497 ALTER TABLE "issue" ENABLE TRIGGER USER;
3500 DROP TABLE "old_snapshot";
3502 DROP TABLE "direct_supporter_snapshot_old";
3503 DROP TABLE "delegating_interest_snapshot_old";
3504 DROP TABLE "direct_interest_snapshot_old";
3505 DROP TABLE "delegating_population_snapshot";
3506 DROP TABLE "direct_population_snapshot";
3509 DROP VIEW "open_issue";
3512 ALTER TABLE "issue" DROP COLUMN "latest_snapshot_event";
3515 CREATE VIEW "open_issue" AS
3516 SELECT * FROM "issue" WHERE "closed" ISNULL;
3518 COMMENT ON VIEW "open_issue" IS 'All open issues';
3521 -- NOTE: create "issue_for_admission" view after altering table "issue"
3522 CREATE VIEW "issue_for_admission" AS
3523 SELECT DISTINCT ON ("issue"."area_id")
3524 "issue".*,
3525 max("initiative"."supporter_count") AS "max_supporter_count"
3526 FROM "issue"
3527 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
3528 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
3529 JOIN "area" ON "issue"."area_id" = "area"."id"
3530 WHERE "issue"."state" = 'admission'::"issue_state"
3531 AND now() >= "issue"."created" + "issue"."min_admission_time"
3532 AND "initiative"."supporter_count" >= "policy"."issue_quorum"
3533 AND "initiative"."supporter_count" * "policy"."issue_quorum_den" >=
3534 "issue"."population" * "policy"."issue_quorum_num"
3535 AND "initiative"."supporter_count" >= "area"."issue_quorum"
3536 AND "initiative"."revoked" ISNULL
3537 GROUP BY "issue"."id"
3538 ORDER BY "issue"."area_id", "max_supporter_count" DESC, "issue"."id";
3540 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';
3543 DROP TYPE "snapshot_event";
3546 ALTER TABLE "issue" ADD CONSTRAINT "snapshot_required" CHECK (
3547 ("half_frozen" ISNULL OR "half_freeze_snapshot_id" NOTNULL) AND
3548 ("fully_frozen" ISNULL OR "full_freeze_snapshot_id" NOTNULL) );
3551 COMMIT;