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