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