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