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