liquid_feedback_core
view update/core-update.v3.2.2-v4.0.0.sql @ 532:5855ff9e5c8f
Several changes/additions for upcoming major release
- OAuth 2.0 support
- storing profiles as JSON document
- removed subject area membership
- revised snapshot system
- additional issue limiter (dynamic quorum in subject area)
- extended event logging in "event" table
- OAuth 2.0 support
- storing profiles as JSON document
- removed subject area membership
- revised snapshot system
- additional issue limiter (dynamic quorum in subject area)
- extended event logging in "event" table
| author | jbe |
|---|---|
| date | Thu Mar 30 19:42:38 2017 +0200 (2017-03-30) |
| parents | |
| children | b341544beb75 |
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 CREATE OR REPLACE FUNCTION "write_event_issue_state_changed_trigger"()
911 RETURNS TRIGGER
912 LANGUAGE 'plpgsql' VOLATILE AS $$
913 DECLARE
914 "area_row" "area"%ROWTYPE;
915 BEGIN
916 IF NEW."state" != OLD."state" THEN
917 SELECT * INTO "area_row" FROM "area" WHERE "id" = NEW."area_id"
918 FOR SHARE;
919 INSERT INTO "event" (
920 "event",
921 "unit_id", "area_id", "issue_id", "state"
922 ) VALUES (
923 'issue_state_changed',
924 "area_row"."unit_id", NEW."area_id", NEW."id", NEW."state"
925 );
926 END IF;
927 RETURN NULL;
928 END;
929 $$;
932 CREATE OR REPLACE FUNCTION "write_event_initiative_or_draft_created_trigger"()
933 RETURNS TRIGGER
934 LANGUAGE 'plpgsql' VOLATILE AS $$
935 DECLARE
936 "initiative_row" "initiative"%ROWTYPE;
937 "issue_row" "issue"%ROWTYPE;
938 "area_row" "area"%ROWTYPE;
939 "event_v" "event_type";
940 BEGIN
941 SELECT * INTO "initiative_row" FROM "initiative"
942 WHERE "id" = NEW."initiative_id" FOR SHARE;
943 SELECT * INTO "issue_row" FROM "issue"
944 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
945 SELECT * INTO "area_row" FROM "area"
946 WHERE "id" = "issue_row"."area_id" FOR SHARE;
947 IF EXISTS (
948 SELECT NULL FROM "draft"
949 WHERE "initiative_id" = NEW."initiative_id" AND "id" != NEW."id"
950 FOR SHARE
951 ) THEN
952 "event_v" := 'new_draft_created';
953 ELSE
954 IF EXISTS (
955 SELECT NULL FROM "initiative"
956 WHERE "issue_id" = "initiative_row"."issue_id"
957 AND "id" != "initiative_row"."id"
958 FOR SHARE
959 ) THEN
960 "event_v" := 'initiative_created_in_existing_issue';
961 ELSE
962 "event_v" := 'initiative_created_in_new_issue';
963 END IF;
964 END IF;
965 INSERT INTO "event" (
966 "event", "member_id",
967 "unit_id", "area_id", "issue_id", "state",
968 "initiative_id", "draft_id"
969 ) VALUES (
970 "event_v", NEW."author_id",
971 "area_row"."unit_id", "issue_row"."area_id",
972 "initiative_row"."issue_id", "issue_row"."state",
973 NEW."initiative_id", NEW."id"
974 );
975 RETURN NULL;
976 END;
977 $$;
980 CREATE OR REPLACE FUNCTION "write_event_initiative_revoked_trigger"()
981 RETURNS TRIGGER
982 LANGUAGE 'plpgsql' VOLATILE AS $$
983 DECLARE
984 "issue_row" "issue"%ROWTYPE;
985 "area_row" "area"%ROWTYPE;
986 "draft_id_v" "draft"."id"%TYPE;
987 BEGIN
988 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
989 SELECT * INTO "issue_row" FROM "issue"
990 WHERE "id" = NEW."issue_id" FOR SHARE;
991 SELECT * INTO "area_row" FROM "area"
992 WHERE "id" = "issue_row"."area_id" FOR SHARE;
993 SELECT "id" INTO "draft_id_v" FROM "current_draft"
994 WHERE "initiative_id" = NEW."id" FOR SHARE;
995 INSERT INTO "event" (
996 "event", "member_id",
997 "unit_id", "area_id", "issue_id", "state",
998 "initiative_id", "draft_id"
999 ) VALUES (
1000 'initiative_revoked', NEW."revoked_by_member_id",
1001 "area_row"."unit_id", "issue_row"."area_id",
1002 NEW."issue_id", "issue_row"."state",
1003 NEW."id", "draft_id_v"
1004 );
1005 END IF;
1006 RETURN NULL;
1007 END;
1008 $$;
1011 CREATE OR REPLACE FUNCTION "write_event_suggestion_created_trigger"()
1012 RETURNS TRIGGER
1013 LANGUAGE 'plpgsql' VOLATILE AS $$
1014 DECLARE
1015 "initiative_row" "initiative"%ROWTYPE;
1016 "issue_row" "issue"%ROWTYPE;
1017 "area_row" "area"%ROWTYPE;
1018 BEGIN
1019 SELECT * INTO "initiative_row" FROM "initiative"
1020 WHERE "id" = NEW."initiative_id" FOR SHARE;
1021 SELECT * INTO "issue_row" FROM "issue"
1022 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1023 SELECT * INTO "area_row" FROM "area"
1024 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1025 INSERT INTO "event" (
1026 "event", "member_id",
1027 "unit_id", "area_id", "issue_id", "state",
1028 "initiative_id", "suggestion_id"
1029 ) VALUES (
1030 'suggestion_created', NEW."author_id",
1031 "area_row"."unit_id", "issue_row"."area_id",
1032 "initiative_row"."issue_id", "issue_row"."state",
1033 NEW."initiative_id", NEW."id"
1034 );
1035 RETURN NULL;
1036 END;
1037 $$;
1040 CREATE FUNCTION "write_event_suggestion_removed_trigger"()
1041 RETURNS TRIGGER
1042 LANGUAGE 'plpgsql' VOLATILE AS $$
1043 DECLARE
1044 "initiative_row" "initiative"%ROWTYPE;
1045 "issue_row" "issue"%ROWTYPE;
1046 "area_row" "area"%ROWTYPE;
1047 BEGIN
1048 SELECT * INTO "initiative_row" FROM "initiative"
1049 WHERE "id" = OLD."initiative_id" FOR SHARE;
1050 IF "initiative_row"."id" NOTNULL THEN
1051 SELECT * INTO "issue_row" FROM "issue"
1052 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1053 SELECT * INTO "area_row" FROM "area"
1054 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1055 INSERT INTO "event" (
1056 "event",
1057 "unit_id", "area_id", "issue_id", "state",
1058 "initiative_id", "suggestion_id"
1059 ) VALUES (
1060 'suggestion_removed',
1061 "area_row"."unit_id", "issue_row"."area_id",
1062 "initiative_row"."issue_id", "issue_row"."state",
1063 OLD."initiative_id", OLD."id"
1064 );
1065 END IF;
1066 RETURN NULL;
1067 END;
1068 $$;
1070 CREATE TRIGGER "write_event_suggestion_removed"
1071 AFTER DELETE ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1072 "write_event_suggestion_removed_trigger"();
1074 COMMENT ON FUNCTION "write_event_suggestion_removed_trigger"() IS 'Implementation of trigger "write_event_suggestion_removed" on table "issue"';
1075 COMMENT ON TRIGGER "write_event_suggestion_removed" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1078 CREATE FUNCTION "write_event_member_trigger"()
1079 RETURNS TRIGGER
1080 LANGUAGE 'plpgsql' VOLATILE AS $$
1081 BEGIN
1082 IF TG_OP = 'INSERT' THEN
1083 IF NEW."activated" NOTNULL THEN
1084 INSERT INTO "event" ("event", "member_id")
1085 VALUES ('member_activated', NEW."id");
1086 END IF;
1087 IF NEW."active" THEN
1088 INSERT INTO "event" ("event", "member_id", "boolean_value")
1089 VALUES ('member_active', NEW."id", TRUE);
1090 END IF;
1091 ELSIF TG_OP = 'UPDATE' THEN
1092 IF OLD."id" != NEW."id" THEN
1093 RAISE EXCEPTION 'Cannot change member ID';
1094 END IF;
1095 IF OLD."name" != NEW."name" THEN
1096 INSERT INTO "event" (
1097 "event", "member_id", "text_value", "old_text_value"
1098 ) VALUES (
1099 'member_name_updated', NEW."id", NEW."name", OLD."name"
1100 );
1101 END IF;
1102 IF OLD."active" != NEW."active" THEN
1103 INSERT INTO "event" ("event", "member_id", "boolean_value") VALUES (
1104 'member_active', NEW."id", NEW."active"
1105 );
1106 END IF;
1107 IF
1108 OLD."activated" NOTNULL AND
1109 NEW."last_login" ISNULL AND
1110 NEW."login" ISNULL AND
1111 NEW."authority_login" ISNULL AND
1112 NEW."locked" = TRUE
1113 THEN
1114 INSERT INTO "event" ("event", "member_id")
1115 VALUES ('member_removed', NEW."id");
1116 END IF;
1117 END IF;
1118 RETURN NULL;
1119 END;
1120 $$;
1122 CREATE TRIGGER "write_event_member"
1123 AFTER INSERT OR UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1124 "write_event_member_trigger"();
1126 COMMENT ON FUNCTION "write_event_member_trigger"() IS 'Implementation of trigger "write_event_member" on table "member"';
1127 COMMENT ON TRIGGER "write_event_member" ON "member" IS 'Create entries in "event" table on insertion to member table';
1130 CREATE FUNCTION "write_event_member_profile_updated_trigger"()
1131 RETURNS TRIGGER
1132 LANGUAGE 'plpgsql' VOLATILE AS $$
1133 BEGIN
1134 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1135 IF EXISTS (SELECT NULL FROM "member" WHERE "id" = OLD."member_id") THEN
1136 INSERT INTO "event" ("event", "member_id") VALUES (
1137 'member_profile_updated', OLD."member_id"
1138 );
1139 END IF;
1140 END IF;
1141 IF TG_OP = 'UPDATE' THEN
1142 IF OLD."member_id" = NEW."member_id" THEN
1143 RETURN NULL;
1144 END IF;
1145 END IF;
1146 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1147 INSERT INTO "event" ("event", "member_id") VALUES (
1148 'member_profile_updated', NEW."member_id"
1149 );
1150 END IF;
1151 RETURN NULL;
1152 END;
1153 $$;
1155 CREATE TRIGGER "write_event_member_profile_updated"
1156 AFTER INSERT OR UPDATE OR DELETE ON "member_profile"
1157 FOR EACH ROW EXECUTE PROCEDURE
1158 "write_event_member_profile_updated_trigger"();
1160 COMMENT ON FUNCTION "write_event_member_profile_updated_trigger"() IS 'Implementation of trigger "write_event_member_profile_updated" on table "member_profile"';
1161 COMMENT ON TRIGGER "write_event_member_profile_updated" ON "member_profile" IS 'Creates entries in "event" table on member profile update';
1164 CREATE FUNCTION "write_event_member_image_updated_trigger"()
1165 RETURNS TRIGGER
1166 LANGUAGE 'plpgsql' VOLATILE AS $$
1167 BEGIN
1168 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1169 IF NOT OLD."scaled" THEN
1170 IF EXISTS (SELECT NULL FROM "member" WHERE "id" = OLD."member_id") THEN
1171 INSERT INTO "event" ("event", "member_id") VALUES (
1172 'member_image_updated', OLD."member_id"
1173 );
1174 END IF;
1175 END IF;
1176 END IF;
1177 IF TG_OP = 'UPDATE' THEN
1178 IF
1179 OLD."member_id" = NEW."member_id" AND
1180 OLD."scaled" = NEW."scaled"
1181 THEN
1182 RETURN NULL;
1183 END IF;
1184 END IF;
1185 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1186 IF NOT NEW."scaled" THEN
1187 INSERT INTO "event" ("event", "member_id") VALUES (
1188 'member_image_updated', NEW."member_id"
1189 );
1190 END IF;
1191 END IF;
1192 RETURN NULL;
1193 END;
1194 $$;
1196 CREATE TRIGGER "write_event_member_image_updated"
1197 AFTER INSERT OR UPDATE OR DELETE ON "member_image"
1198 FOR EACH ROW EXECUTE PROCEDURE
1199 "write_event_member_image_updated_trigger"();
1201 COMMENT ON FUNCTION "write_event_member_image_updated_trigger"() IS 'Implementation of trigger "write_event_member_image_updated" on table "member_image"';
1202 COMMENT ON TRIGGER "write_event_member_image_updated" ON "member_image" IS 'Creates entries in "event" table on member image update';
1205 CREATE FUNCTION "write_event_interest_trigger"()
1206 RETURNS TRIGGER
1207 LANGUAGE 'plpgsql' VOLATILE AS $$
1208 DECLARE
1209 "issue_row" "issue"%ROWTYPE;
1210 "area_row" "area"%ROWTYPE;
1211 BEGIN
1212 IF TG_OP = 'UPDATE' THEN
1213 IF OLD = NEW THEN
1214 RETURN NULL;
1215 END IF;
1216 END IF;
1217 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1218 SELECT * INTO "issue_row" FROM "issue"
1219 WHERE "id" = OLD."issue_id" FOR SHARE;
1220 SELECT * INTO "area_row" FROM "area"
1221 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1222 IF "issue_row"."id" NOTNULL THEN
1223 INSERT INTO "event" (
1224 "event", "member_id",
1225 "unit_id", "area_id", "issue_id", "state",
1226 "boolean_value"
1227 ) VALUES (
1228 'interest', OLD."member_id",
1229 "area_row"."unit_id", "issue_row"."area_id",
1230 OLD."issue_id", "issue_row"."state",
1231 FALSE
1232 );
1233 END IF;
1234 END IF;
1235 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1236 SELECT * INTO "issue_row" FROM "issue"
1237 WHERE "id" = NEW."issue_id" FOR SHARE;
1238 SELECT * INTO "area_row" FROM "area"
1239 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1240 INSERT INTO "event" (
1241 "event", "member_id",
1242 "unit_id", "area_id", "issue_id", "state",
1243 "boolean_value"
1244 ) VALUES (
1245 'interest', NEW."member_id",
1246 "area_row"."unit_id", "issue_row"."area_id",
1247 NEW."issue_id", "issue_row"."state",
1248 TRUE
1249 );
1250 END IF;
1251 RETURN NULL;
1252 END;
1253 $$;
1255 CREATE TRIGGER "write_event_interest"
1256 AFTER INSERT OR UPDATE OR DELETE ON "interest" FOR EACH ROW EXECUTE PROCEDURE
1257 "write_event_interest_trigger"();
1259 COMMENT ON FUNCTION "write_event_interest_trigger"() IS 'Implementation of trigger "write_event_interest_inserted" on table "interest"';
1260 COMMENT ON TRIGGER "write_event_interest" ON "interest" IS 'Create entry in "event" table on adding or removing interest';
1263 CREATE FUNCTION "write_event_initiator_trigger"()
1264 RETURNS TRIGGER
1265 LANGUAGE 'plpgsql' VOLATILE AS $$
1266 DECLARE
1267 "initiative_row" "initiative"%ROWTYPE;
1268 "issue_row" "issue"%ROWTYPE;
1269 "area_row" "area"%ROWTYPE;
1270 BEGIN
1271 IF TG_OP = 'UPDATE' THEN
1272 IF
1273 OLD."initiative_id" = NEW."initiative_id" AND
1274 OLD."member_id" = NEW."member_id" AND
1275 coalesce(OLD."accepted", FALSE) = coalesce(NEW."accepted", FALSE)
1276 THEN
1277 RETURN NULL;
1278 END IF;
1279 END IF;
1280 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "accepted_v" THEN
1281 IF coalesce(OLD."accepted", FALSE) = TRUE THEN
1282 SELECT * INTO "initiative_row" FROM "initiative"
1283 WHERE "id" = OLD."initiative_id" FOR SHARE;
1284 IF "initiative_row"."id" NOTNULL THEN
1285 SELECT * INTO "issue_row" FROM "issue"
1286 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1287 SELECT * INTO "area_row" FROM "area"
1288 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1289 INSERT INTO "event" (
1290 "event", "member_id",
1291 "unit_id", "area_id", "issue_id", "state",
1292 "initiative_id", "boolean_value"
1293 ) VALUES (
1294 'initiator', OLD."member_id",
1295 "area_row"."unit_id", "issue_row"."area_id",
1296 "issue_row"."id", "issue_row"."state",
1297 OLD."initiative_id", FALSE
1298 );
1299 END IF;
1300 END IF;
1301 END IF;
1302 IF TG_OP = 'UPDATE' AND NOT "rejected_v" THEN
1303 IF coalesce(NEW."accepted", FALSE) = TRUE THEN
1304 SELECT * INTO "initiative_row" FROM "initiative"
1305 WHERE "id" = NEW."initiative_id" FOR SHARE;
1306 SELECT * INTO "issue_row" FROM "issue"
1307 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1308 SELECT * INTO "area_row" FROM "area"
1309 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1310 INSERT INTO "event" (
1311 "event", "member_id",
1312 "unit_id", "area_id", "issue_id", "state",
1313 "initiative_id", "boolean_value"
1314 ) VALUES (
1315 'initiator', NEW."member_id",
1316 "area_row"."unit_id", "issue_row"."area_id",
1317 "issue_row"."id", "issue_row"."state",
1318 NEW."initiative_id", TRUE
1319 );
1320 END IF;
1321 END IF;
1322 RETURN NULL;
1323 END;
1324 $$;
1326 CREATE TRIGGER "write_event_initiator"
1327 AFTER UPDATE OR DELETE ON "initiator" FOR EACH ROW EXECUTE PROCEDURE
1328 "write_event_initiator_trigger"();
1330 COMMENT ON FUNCTION "write_event_initiator_trigger"() IS 'Implementation of trigger "write_event_initiator" on table "initiator"';
1331 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)';
1334 CREATE FUNCTION "write_event_support_trigger"()
1335 RETURNS TRIGGER
1336 LANGUAGE 'plpgsql' VOLATILE AS $$
1337 DECLARE
1338 "issue_row" "issue"%ROWTYPE;
1339 "area_row" "area"%ROWTYPE;
1340 BEGIN
1341 IF TG_OP = 'UPDATE' THEN
1342 IF
1343 OLD."initiative_id" = NEW."initiative_id" AND
1344 OLD."member_id" = NEW."member_id"
1345 THEN
1346 IF OLD."draft_id" != NEW."draft_id" THEN
1347 SELECT * INTO "issue_row" FROM "issue"
1348 WHERE "id" = NEW."issue_id" FOR SHARE;
1349 SELECT * INTO "area_row" FROM "area"
1350 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1351 INSERT INTO "event" (
1352 "event", "member_id",
1353 "unit_id", "area_id", "issue_id", "state",
1354 "initiative_id", "draft_id"
1355 ) VALUES (
1356 'support_updated', NEW."member_id",
1357 "area_row"."unit_id", "issue_row"."area_id",
1358 "issue_row"."id", "issue_row"."state",
1359 NEW."initiative_id", NEW."draft_id"
1360 );
1361 END IF;
1362 RETURN NULL;
1363 END IF;
1364 END IF;
1365 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1366 IF EXISTS (
1367 SELECT NULL FROM "initiative" WHERE "id" = OLD."initiative_id"
1368 FOR SHARE
1369 ) THEN
1370 SELECT * INTO "issue_row" FROM "issue"
1371 WHERE "id" = OLD."issue_id" FOR SHARE;
1372 SELECT * INTO "area_row" FROM "area"
1373 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1374 INSERT INTO "event" (
1375 "event", "member_id",
1376 "unit_id", "area_id", "issue_id", "state",
1377 "initiative_id", "draft_id", "boolean_value"
1378 ) VALUES (
1379 'support', OLD."member_id",
1380 "area_row"."unit_id", "issue_row"."area_id",
1381 "issue_row"."id", "issue_row"."state",
1382 OLD."initiative_id", OLD."draft_id", FALSE
1383 );
1384 END IF;
1385 END IF;
1386 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1387 SELECT * INTO "issue_row" FROM "issue"
1388 WHERE "id" = NEW."issue_id" FOR SHARE;
1389 SELECT * INTO "area_row" FROM "area"
1390 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1391 INSERT INTO "event" (
1392 "event", "member_id",
1393 "unit_id", "area_id", "issue_id", "state",
1394 "initiative_id", "draft_id", "boolean_value"
1395 ) VALUES (
1396 'support', NEW."member_id",
1397 "area_row"."unit_id", "issue_row"."area_id",
1398 "issue_row"."id", "issue_row"."state",
1399 NEW."initiative_id", NEW."draft_id", TRUE
1400 );
1401 END IF;
1402 RETURN NULL;
1403 END;
1404 $$;
1406 CREATE TRIGGER "write_event_support"
1407 AFTER INSERT OR UPDATE OR DELETE ON "supporter" FOR EACH ROW EXECUTE PROCEDURE
1408 "write_event_support_trigger"();
1410 COMMENT ON FUNCTION "write_event_support_trigger"() IS 'Implementation of trigger "write_event_support" on table "supporter"';
1411 COMMENT ON TRIGGER "write_event_support" ON "supporter" IS 'Create entry in "event" table when adding, updating, or removing support';
1414 CREATE FUNCTION "write_event_suggestion_rated_trigger"()
1415 RETURNS TRIGGER
1416 LANGUAGE 'plpgsql' VOLATILE AS $$
1417 DECLARE
1418 "same_pkey_v" BOOLEAN = FALSE;
1419 "initiative_row" "initiative"%ROWTYPE;
1420 "issue_row" "issue"%ROWTYPE;
1421 "area_row" "area"%ROWTYPE;
1422 BEGIN
1423 IF TG_OP = 'UPDATE' THEN
1424 IF
1425 OLD."suggestion_id" = NEW."suggestion_id" AND
1426 OLD."member_id" = NEW."member_id"
1427 THEN
1428 IF
1429 OLD."degree" = NEW."degree" AND
1430 OLD."fulfilled" = NEW."fulfilled"
1431 THEN
1432 RETURN NULL;
1433 END IF;
1434 "same_pkey_v" := TRUE;
1435 END IF;
1436 END IF;
1437 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "same_pkey_v" THEN
1438 IF EXISTS (
1439 SELECT NULL FROM "suggestion" WHERE "id" = OLD."suggestion_id"
1440 FOR SHARE
1441 ) THEN
1442 SELECT * INTO "initiative_row" FROM "initiative"
1443 WHERE "id" = OLD."initiative_id" FOR SHARE;
1444 SELECT * INTO "issue_row" FROM "issue"
1445 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1446 SELECT * INTO "area_row" FROM "area"
1447 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1448 INSERT INTO "event" (
1449 "event", "member_id",
1450 "unit_id", "area_id", "issue_id", "state",
1451 "initiative_id", "suggestion_id",
1452 "boolean_value", "numeric_value"
1453 ) VALUES (
1454 'suggestion_rated', OLD."member_id",
1455 "area_row"."unit_id", "issue_row"."area_id",
1456 "initiative_row"."issue_id", "issue_row"."state",
1457 OLD."initiative_id", OLD."suggestion_id",
1458 NULL, 0
1459 );
1460 END IF;
1461 END IF;
1462 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1463 SELECT * INTO "initiative_row" FROM "initiative"
1464 WHERE "id" = NEW."initiative_id" FOR SHARE;
1465 SELECT * INTO "issue_row" FROM "issue"
1466 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1467 SELECT * INTO "area_row" FROM "area"
1468 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1469 INSERT INTO "event" (
1470 "event", "member_id",
1471 "unit_id", "area_id", "issue_id", "state",
1472 "initiative_id", "suggestion_id",
1473 "boolean_value", "numeric_value"
1474 ) VALUES (
1475 'suggestion_rated', NEW."member_id",
1476 "area_row"."unit_id", "issue_row"."area_id",
1477 "initiative_row"."issue_id", "issue_row"."state",
1478 NEW."initiative_id", NEW."suggestion_id",
1479 NEW."fulfilled", NEW."degree"
1480 );
1481 END IF;
1482 RETURN NULL;
1483 END;
1484 $$;
1486 CREATE TRIGGER "write_event_suggestion_rated"
1487 AFTER INSERT OR UPDATE OR DELETE ON "opinion" FOR EACH ROW EXECUTE PROCEDURE
1488 "write_event_suggestion_rated_trigger"();
1490 COMMENT ON FUNCTION "write_event_suggestion_rated_trigger"() IS 'Implementation of trigger "write_event_suggestion_rated" on table "opinion"';
1491 COMMENT ON TRIGGER "write_event_suggestion_rated" ON "opinion" IS 'Create entry in "event" table when adding, updating, or removing support';
1494 CREATE FUNCTION "write_event_delegation_trigger"()
1495 RETURNS TRIGGER
1496 LANGUAGE 'plpgsql' VOLATILE AS $$
1497 DECLARE
1498 "issue_row" "issue"%ROWTYPE;
1499 "area_row" "area"%ROWTYPE;
1500 BEGIN
1501 IF TG_OP = 'DELETE' THEN
1502 IF EXISTS (
1503 SELECT NULL FROM "member" WHERE "id" = OLD."truster_id"
1504 ) AND (CASE OLD."scope"
1505 WHEN 'unit'::"delegation_scope" THEN EXISTS (
1506 SELECT NULL FROM "unit" WHERE "id" = OLD."unit_id"
1507 )
1508 WHEN 'area'::"delegation_scope" THEN EXISTS (
1509 SELECT NULL FROM "area" WHERE "id" = OLD."area_id"
1510 )
1511 WHEN 'issue'::"delegation_scope" THEN EXISTS (
1512 SELECT NULL FROM "issue" WHERE "id" = OLD."issue_id"
1513 )
1514 END) THEN
1515 SELECT * INTO "issue_row" FROM "issue"
1516 WHERE "id" = OLD."issue_id" FOR SHARE;
1517 SELECT * INTO "area_row" FROM "area"
1518 WHERE "id" = COALESCE(OLD."area_id", "issue_row"."area_id")
1519 FOR SHARE;
1520 INSERT INTO "event" (
1521 "event", "member_id", "scope",
1522 "unit_id", "area_id", "issue_id", "state",
1523 "boolean_value"
1524 ) VALUES (
1525 'delegation', OLD."truster_id", OLD."scope",
1526 COALESCE(OLD."unit_id", "area_row"."unit_id"), "area_row"."id",
1527 OLD."issue_id", "issue_row"."state",
1528 FALSE
1529 );
1530 END IF;
1531 ELSE
1532 SELECT * INTO "issue_row" FROM "issue"
1533 WHERE "id" = NEW."issue_id" FOR SHARE;
1534 SELECT * INTO "area_row" FROM "area"
1535 WHERE "id" = COALESCE(NEW."area_id", "issue_row"."area_id")
1536 FOR SHARE;
1537 INSERT INTO "event" (
1538 "event", "member_id", "other_member_id", "scope",
1539 "unit_id", "area_id", "issue_id", "state",
1540 "boolean_value"
1541 ) VALUES (
1542 'delegation', NEW."truster_id", NEW."trustee_id", NEW."scope",
1543 COALESCE(NEW."unit_id", "area_row"."unit_id"), "area_row"."id",
1544 NEW."issue_id", "issue_row"."state",
1545 TRUE
1546 );
1547 END IF;
1548 RETURN NULL;
1549 END;
1550 $$;
1552 CREATE TRIGGER "write_event_delegation"
1553 AFTER INSERT OR UPDATE OR DELETE ON "delegation" FOR EACH ROW EXECUTE PROCEDURE
1554 "write_event_delegation_trigger"();
1556 COMMENT ON FUNCTION "write_event_delegation_trigger"() IS 'Implementation of trigger "write_event_delegation" on table "delegation"';
1557 COMMENT ON TRIGGER "write_event_delegation" ON "delegation" IS 'Create entry in "event" table when adding, updating, or removing a delegation';
1560 CREATE FUNCTION "write_event_contact_trigger"()
1561 RETURNS TRIGGER
1562 LANGUAGE 'plpgsql' VOLATILE AS $$
1563 BEGIN
1564 IF TG_OP = 'UPDATE' THEN
1565 IF
1566 OLD."member_id" = NEW."member_id" AND
1567 OLD."other_member_id" = NEW."other_member_id" AND
1568 OLD."public" = NEW."public"
1569 THEN
1570 RETURN NULL;
1571 END IF;
1572 END IF;
1573 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1574 IF OLD."public" THEN
1575 IF EXISTS (
1576 SELECT NULL FROM "member" WHERE "id" = OLD."member_id"
1577 FOR SHARE
1578 ) AND EXISTS (
1579 SELECT NULL FROM "member" WHERE "id" = OLD."other_member_id"
1580 FOR SHARE
1581 ) THEN
1582 INSERT INTO "event" (
1583 "event", "member_id", "other_member_id", "boolean_value"
1584 ) VALUES (
1585 'contact', OLD."member_id", OLD."other_member_id", FALSE
1586 );
1587 END IF;
1588 END IF;
1589 END IF;
1590 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1591 IF NEW."public" THEN
1592 INSERT INTO "event" (
1593 "event", "member_id", "other_member_id", "boolean_value"
1594 ) VALUES (
1595 'contact', NEW."member_id", NEW."other_member_id", TRUE
1596 );
1597 END IF;
1598 END IF;
1599 RETURN NULL;
1600 END;
1601 $$;
1603 CREATE TRIGGER "write_event_contact"
1604 AFTER INSERT OR UPDATE OR DELETE ON "contact" FOR EACH ROW EXECUTE PROCEDURE
1605 "write_event_contact_trigger"();
1607 COMMENT ON FUNCTION "write_event_contact_trigger"() IS 'Implementation of trigger "write_event_contact" on table "contact"';
1608 COMMENT ON TRIGGER "write_event_contact" ON "contact" IS 'Create entry in "event" table when adding or removing public contacts';
1611 CREATE FUNCTION "send_event_notify_trigger"()
1612 RETURNS TRIGGER
1613 LANGUAGE 'plpgsql' VOLATILE AS $$
1614 BEGIN
1615 EXECUTE 'NOTIFY "event", ''' || NEW."event" || '''';
1616 RETURN NULL;
1617 END;
1618 $$;
1620 CREATE TRIGGER "send_notify"
1621 AFTER INSERT OR UPDATE ON "event" FOR EACH ROW EXECUTE PROCEDURE
1622 "send_event_notify_trigger"();
1625 CREATE FUNCTION "delete_extended_scope_tokens_trigger"()
1626 RETURNS TRIGGER
1627 LANGUAGE 'plpgsql' VOLATILE AS $$
1628 DECLARE
1629 "system_application_row" "system_application"%ROWTYPE;
1630 BEGIN
1631 IF OLD."system_application_id" NOTNULL THEN
1632 SELECT * FROM "system_application" INTO "system_application_row"
1633 WHERE "id" = OLD."system_application_id";
1634 DELETE FROM "token"
1635 WHERE "member_id" = OLD."member_id"
1636 AND "system_application_id" = OLD."system_application_id"
1637 AND NOT COALESCE(
1638 regexp_split_to_array("scope", E'\\s+') <@
1639 regexp_split_to_array(
1640 "system_application_row"."automatic_scope", E'\\s+'
1641 ),
1642 FALSE
1643 );
1644 END IF;
1645 RETURN OLD;
1646 END;
1647 $$;
1649 CREATE TRIGGER "delete_extended_scope_tokens"
1650 BEFORE DELETE ON "member_application" FOR EACH ROW EXECUTE PROCEDURE
1651 "delete_extended_scope_tokens_trigger"();
1654 CREATE FUNCTION "detach_token_from_session_trigger"()
1655 RETURNS TRIGGER
1656 LANGUAGE 'plpgsql' VOLATILE AS $$
1657 BEGIN
1658 UPDATE "token" SET "session_id" = NULL
1659 WHERE "session_id" = OLD."id";
1660 RETURN OLD;
1661 END;
1662 $$;
1664 CREATE TRIGGER "detach_token_from_session"
1665 BEFORE DELETE ON "session" FOR EACH ROW EXECUTE PROCEDURE
1666 "detach_token_from_session_trigger"();
1669 CREATE FUNCTION "delete_non_detached_scope_with_session_trigger"()
1670 RETURNS TRIGGER
1671 LANGUAGE 'plpgsql' VOLATILE AS $$
1672 BEGIN
1673 IF NEW."session_id" ISNULL THEN
1674 SELECT coalesce(string_agg("element", ' '), '') INTO NEW."scope"
1675 FROM unnest(regexp_split_to_array(NEW."scope", E'\\s+')) AS "element"
1676 WHERE "element" LIKE '%_detached';
1677 END IF;
1678 RETURN NEW;
1679 END;
1680 $$;
1682 CREATE TRIGGER "delete_non_detached_scope_with_session"
1683 BEFORE INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
1684 "delete_non_detached_scope_with_session_trigger"();
1687 CREATE FUNCTION "delete_token_with_empty_scope_trigger"()
1688 RETURNS TRIGGER
1689 LANGUAGE 'plpgsql' VOLATILE AS $$
1690 BEGIN
1691 IF NEW."scope" = '' THEN
1692 DELETE FROM "token" WHERE "id" = NEW."id";
1693 END IF;
1694 RETURN NULL;
1695 END;
1696 $$;
1698 CREATE TRIGGER "delete_token_with_empty_scope"
1699 AFTER INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
1700 "delete_token_with_empty_scope_trigger"();
1703 CREATE FUNCTION "delete_snapshot_on_partial_delete_trigger"()
1704 RETURNS TRIGGER
1705 LANGUAGE 'plpgsql' VOLATILE AS $$
1706 BEGIN
1707 IF TG_OP = 'UPDATE' THEN
1708 IF
1709 OLD."snapshot_id" = NEW."snapshot_id" AND
1710 OLD."issue_id" = NEW."issue_id"
1711 THEN
1712 RETURN NULL;
1713 END IF;
1714 END IF;
1715 DELETE FROM "snapshot" WHERE "id" = OLD."snapshot_id";
1716 RETURN NULL;
1717 END;
1718 $$;
1720 CREATE TRIGGER "delete_snapshot_on_partial_delete"
1721 AFTER UPDATE OR DELETE ON "snapshot_issue"
1722 FOR EACH ROW EXECUTE PROCEDURE
1723 "delete_snapshot_on_partial_delete_trigger"();
1725 COMMENT ON FUNCTION "delete_snapshot_on_partial_delete_trigger"() IS 'Implementation of trigger "delete_snapshot_on_partial_delete" on table "snapshot_issue"';
1726 COMMENT ON TRIGGER "delete_snapshot_on_partial_delete" ON "snapshot_issue" IS 'Deletes whole snapshot if one issue is deleted from the snapshot';
1729 CREATE FUNCTION "copy_current_draft_data"
1730 ("initiative_id_p" "initiative"."id"%TYPE )
1731 RETURNS VOID
1732 LANGUAGE 'plpgsql' VOLATILE AS $$
1733 BEGIN
1734 PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p"
1735 FOR UPDATE;
1736 UPDATE "initiative" SET
1737 "location" = "draft"."location",
1738 "draft_text_search_data" = "draft"."text_search_data"
1739 FROM "current_draft" AS "draft"
1740 WHERE "initiative"."id" = "initiative_id_p"
1741 AND "draft"."initiative_id" = "initiative_id_p";
1742 END;
1743 $$;
1745 COMMENT ON FUNCTION "copy_current_draft_data"
1746 ( "initiative"."id"%TYPE )
1747 IS 'Helper function for function "copy_current_draft_data_trigger"';
1750 CREATE FUNCTION "copy_current_draft_data_trigger"()
1751 RETURNS TRIGGER
1752 LANGUAGE 'plpgsql' VOLATILE AS $$
1753 BEGIN
1754 IF TG_OP='DELETE' THEN
1755 PERFORM "copy_current_draft_data"(OLD."initiative_id");
1756 ELSE
1757 IF TG_OP='UPDATE' THEN
1758 IF COALESCE(OLD."inititiave_id" != NEW."initiative_id", TRUE) THEN
1759 PERFORM "copy_current_draft_data"(OLD."initiative_id");
1760 END IF;
1761 END IF;
1762 PERFORM "copy_current_draft_data"(NEW."initiative_id");
1763 END IF;
1764 RETURN NULL;
1765 END;
1766 $$;
1768 CREATE TRIGGER "copy_current_draft_data"
1769 AFTER INSERT OR UPDATE OR DELETE ON "draft"
1770 FOR EACH ROW EXECUTE PROCEDURE
1771 "copy_current_draft_data_trigger"();
1773 COMMENT ON FUNCTION "copy_current_draft_data_trigger"() IS 'Implementation of trigger "copy_current_draft_data" on table "draft"';
1774 COMMENT ON TRIGGER "copy_current_draft_data" ON "draft" IS 'Copy certain fields from most recent "draft" to "initiative"';
1777 CREATE VIEW "area_quorum" AS
1778 SELECT
1779 "area"."id" AS "area_id",
1780 ceil(
1781 "area"."quorum_standard"::FLOAT8 * "quorum_factor"::FLOAT8 ^ (
1782 coalesce(
1783 ( SELECT sum(
1784 ( extract(epoch from "area"."quorum_time")::FLOAT8 /
1785 extract(epoch from
1786 ("issue"."accepted"-"issue"."created") +
1787 "issue"."discussion_time" +
1788 "issue"."verification_time" +
1789 "issue"."voting_time"
1790 )::FLOAT8
1791 ) ^ "area"."quorum_exponent"::FLOAT8
1792 )
1793 FROM "issue" JOIN "policy"
1794 ON "issue"."policy_id" = "policy"."id"
1795 WHERE "issue"."area_id" = "area"."id"
1796 AND "issue"."accepted" NOTNULL
1797 AND "issue"."closed" ISNULL
1798 AND "policy"."polling" = FALSE
1799 )::FLOAT8, 0::FLOAT8
1800 ) / "area"."quorum_issues"::FLOAT8 - 1::FLOAT8
1801 ) * CASE WHEN "area"."quorum_den" ISNULL THEN 1 ELSE (
1802 SELECT "snapshot"."population"
1803 FROM "snapshot"
1804 WHERE "snapshot"."area_id" = "area"."id"
1805 AND "snapshot"."issue_id" ISNULL
1806 ORDER BY "snapshot"."id" DESC
1807 LIMIT 1
1808 ) END / coalesce("area"."quorum_den", 1)
1810 )::INT4 AS "issue_quorum"
1811 FROM "area";
1813 COMMENT ON VIEW "area_quorum" IS 'Area-based quorum considering number of open (accepted) issues';
1816 CREATE VIEW "area_with_unaccepted_issues" AS
1817 SELECT DISTINCT ON ("area"."id") "area".*
1818 FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id"
1819 WHERE "issue"."state" = 'admission';
1821 COMMENT ON VIEW "area_with_unaccepted_issues" IS 'All areas with unaccepted open issues (needed for issue admission system)';
1824 DROP VIEW "area_member_count";
1827 DROP TABLE "membership";
1830 DROP FUNCTION "membership_weight"
1831 ( "area_id_p" "area"."id"%TYPE,
1832 "member_id_p" "member"."id"%TYPE );
1835 DROP FUNCTION "membership_weight_with_skipping"
1836 ( "area_id_p" "area"."id"%TYPE,
1837 "member_id_p" "member"."id"%TYPE,
1838 "skip_member_ids_p" INT4[] ); -- TODO: ordering/cascade
1841 CREATE OR REPLACE VIEW "issue_delegation" AS
1842 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1843 "issue"."id" AS "issue_id",
1844 "delegation"."id",
1845 "delegation"."truster_id",
1846 "delegation"."trustee_id",
1847 "delegation"."scope"
1848 FROM "issue"
1849 JOIN "area"
1850 ON "area"."id" = "issue"."area_id"
1851 JOIN "delegation"
1852 ON "delegation"."unit_id" = "area"."unit_id"
1853 OR "delegation"."area_id" = "area"."id"
1854 OR "delegation"."issue_id" = "issue"."id"
1855 JOIN "member"
1856 ON "delegation"."truster_id" = "member"."id"
1857 JOIN "privilege"
1858 ON "area"."unit_id" = "privilege"."unit_id"
1859 AND "delegation"."truster_id" = "privilege"."member_id"
1860 WHERE "member"."active" AND "privilege"."voting_right"
1861 ORDER BY
1862 "issue"."id",
1863 "delegation"."truster_id",
1864 "delegation"."scope" DESC;
1867 CREATE VIEW "unit_member" AS
1868 SELECT
1869 "unit"."id" AS "unit_id",
1870 "member"."id" AS "member_id"
1871 FROM "privilege"
1872 JOIN "unit" ON "unit_id" = "privilege"."unit_id"
1873 JOIN "member" ON "member"."id" = "privilege"."member_id"
1874 WHERE "privilege"."voting_right" AND "member"."active";
1876 COMMENT ON VIEW "unit_member" IS 'Active members with voting right in a unit';
1879 CREATE OR REPLACE VIEW "unit_member_count" AS
1880 SELECT
1881 "unit"."id" AS "unit_id",
1882 count("unit_member"."member_id") AS "member_count"
1883 FROM "unit" LEFT JOIN "unit_member"
1884 ON "unit"."id" = "unit_member"."unit_id"
1885 GROUP BY "unit"."id";
1887 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
1890 CREATE OR REPLACE VIEW "opening_draft" AS
1891 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
1892 ORDER BY "initiative_id", "id";
1895 CREATE OR REPLACE VIEW "current_draft" AS
1896 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
1897 ORDER BY "initiative_id", "id" DESC;
1900 CREATE OR REPLACE VIEW "issue_supporter_in_admission_state" AS
1901 SELECT
1902 "area"."unit_id",
1903 "issue"."area_id",
1904 "issue"."id" AS "issue_id",
1905 "supporter"."member_id",
1906 "direct_interest_snapshot"."weight"
1907 FROM "issue"
1908 JOIN "area" ON "area"."id" = "issue"."area_id"
1909 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
1910 JOIN "direct_interest_snapshot"
1911 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
1912 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
1913 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
1914 WHERE "issue"."state" = 'admission'::"issue_state";
1917 CREATE OR REPLACE VIEW "individual_suggestion_ranking" AS
1918 SELECT
1919 "opinion"."initiative_id",
1920 "opinion"."member_id",
1921 "direct_interest_snapshot"."weight",
1922 CASE WHEN
1923 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
1924 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
1925 THEN 1 ELSE
1926 CASE WHEN
1927 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
1928 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
1929 THEN 2 ELSE
1930 CASE WHEN
1931 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
1932 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
1933 THEN 3 ELSE 4 END
1934 END
1935 END AS "preference",
1936 "opinion"."suggestion_id"
1937 FROM "opinion"
1938 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
1939 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
1940 JOIN "direct_interest_snapshot"
1941 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
1942 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
1943 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
1946 CREATE VIEW "expired_session" AS
1947 SELECT * FROM "session" WHERE now() > "expiry";
1949 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
1950 DELETE FROM "session" WHERE "id" = OLD."id";
1952 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
1953 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
1956 CREATE VIEW "expired_token" AS
1957 SELECT * FROM "token" WHERE now() > "expiry" AND NOT (
1958 "token_type" = 'authorization' AND "used" AND EXISTS (
1959 SELECT NULL FROM "token" AS "other"
1960 WHERE "other"."authorization_token_id" = "id" ) );
1962 CREATE RULE "delete" AS ON DELETE TO "expired_token" DO INSTEAD
1963 DELETE FROM "token" WHERE "id" = OLD."id";
1965 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';
1968 CREATE VIEW "unused_snapshot" AS
1969 SELECT "snapshot".* FROM "snapshot"
1970 LEFT JOIN "issue"
1971 ON "snapshot"."id" = "issue"."latest_snapshot_id"
1972 OR "snapshot"."id" = "issue"."admission_snapshot_id"
1973 OR "snapshot"."id" = "issue"."half_freeze_snapshot_id"
1974 OR "snapshot"."id" = "issue"."full_freeze_snapshot_id"
1975 WHERE "issue"."id" ISNULL;
1977 CREATE RULE "delete" AS ON DELETE TO "unused_snapshot" DO INSTEAD
1978 DELETE FROM "snapshot" WHERE "id" = OLD."id";
1980 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)';
1983 CREATE VIEW "expired_snapshot" AS
1984 SELECT "unused_snapshot".* FROM "unused_snapshot" CROSS JOIN "system_setting"
1985 WHERE "unused_snapshot"."calculated" <
1986 now() - "system_setting"."snapshot_retention";
1988 CREATE RULE "delete" AS ON DELETE TO "expired_snapshot" DO INSTEAD
1989 DELETE FROM "snapshot" WHERE "id" = OLD."id";
1991 COMMENT ON VIEW "expired_snapshot" IS 'Contains "unused_snapshot"s that are older than "system_setting"."snapshot_retention" (for deletion)';
1994 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest; for area and global delegation chains: always null';
1997 CREATE OR REPLACE FUNCTION "delegation_chain"
1998 ( "member_id_p" "member"."id"%TYPE,
1999 "unit_id_p" "unit"."id"%TYPE,
2000 "area_id_p" "area"."id"%TYPE,
2001 "issue_id_p" "issue"."id"%TYPE,
2002 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2003 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2004 RETURNS SETOF "delegation_chain_row"
2005 LANGUAGE 'plpgsql' STABLE AS $$
2006 DECLARE
2007 "scope_v" "delegation_scope";
2008 "unit_id_v" "unit"."id"%TYPE;
2009 "area_id_v" "area"."id"%TYPE;
2010 "issue_row" "issue"%ROWTYPE;
2011 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2012 "loop_member_id_v" "member"."id"%TYPE;
2013 "output_row" "delegation_chain_row";
2014 "output_rows" "delegation_chain_row"[];
2015 "simulate_v" BOOLEAN;
2016 "simulate_here_v" BOOLEAN;
2017 "delegation_row" "delegation"%ROWTYPE;
2018 "row_count" INT4;
2019 "i" INT4;
2020 "loop_v" BOOLEAN;
2021 BEGIN
2022 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
2023 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
2024 END IF;
2025 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
2026 "simulate_v" := TRUE;
2027 ELSE
2028 "simulate_v" := FALSE;
2029 END IF;
2030 IF
2031 "unit_id_p" NOTNULL AND
2032 "area_id_p" ISNULL AND
2033 "issue_id_p" ISNULL
2034 THEN
2035 "scope_v" := 'unit';
2036 "unit_id_v" := "unit_id_p";
2037 ELSIF
2038 "unit_id_p" ISNULL AND
2039 "area_id_p" NOTNULL AND
2040 "issue_id_p" ISNULL
2041 THEN
2042 "scope_v" := 'area';
2043 "area_id_v" := "area_id_p";
2044 SELECT "unit_id" INTO "unit_id_v"
2045 FROM "area" WHERE "id" = "area_id_v";
2046 ELSIF
2047 "unit_id_p" ISNULL AND
2048 "area_id_p" ISNULL AND
2049 "issue_id_p" NOTNULL
2050 THEN
2051 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
2052 IF "issue_row"."id" ISNULL THEN
2053 RETURN;
2054 END IF;
2055 IF "issue_row"."closed" NOTNULL THEN
2056 IF "simulate_v" THEN
2057 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
2058 END IF;
2059 FOR "output_row" IN
2060 SELECT * FROM
2061 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
2062 LOOP
2063 RETURN NEXT "output_row";
2064 END LOOP;
2065 RETURN;
2066 END IF;
2067 "scope_v" := 'issue';
2068 SELECT "area_id" INTO "area_id_v"
2069 FROM "issue" WHERE "id" = "issue_id_p";
2070 SELECT "unit_id" INTO "unit_id_v"
2071 FROM "area" WHERE "id" = "area_id_v";
2072 ELSE
2073 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2074 END IF;
2075 "visited_member_ids" := '{}';
2076 "loop_member_id_v" := NULL;
2077 "output_rows" := '{}';
2078 "output_row"."index" := 0;
2079 "output_row"."member_id" := "member_id_p";
2080 "output_row"."member_valid" := TRUE;
2081 "output_row"."participation" := FALSE;
2082 "output_row"."overridden" := FALSE;
2083 "output_row"."disabled_out" := FALSE;
2084 "output_row"."scope_out" := NULL;
2085 LOOP
2086 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2087 "loop_member_id_v" := "output_row"."member_id";
2088 ELSE
2089 "visited_member_ids" :=
2090 "visited_member_ids" || "output_row"."member_id";
2091 END IF;
2092 IF "output_row"."participation" ISNULL THEN
2093 "output_row"."overridden" := NULL;
2094 ELSIF "output_row"."participation" THEN
2095 "output_row"."overridden" := TRUE;
2096 END IF;
2097 "output_row"."scope_in" := "output_row"."scope_out";
2098 "output_row"."member_valid" := EXISTS (
2099 SELECT NULL FROM "member" JOIN "privilege"
2100 ON "privilege"."member_id" = "member"."id"
2101 AND "privilege"."unit_id" = "unit_id_v"
2102 WHERE "id" = "output_row"."member_id"
2103 AND "member"."active" AND "privilege"."voting_right"
2104 );
2105 "simulate_here_v" := (
2106 "simulate_v" AND
2107 "output_row"."member_id" = "member_id_p"
2108 );
2109 "delegation_row" := ROW(NULL);
2110 IF "output_row"."member_valid" OR "simulate_here_v" THEN
2111 IF "scope_v" = 'unit' THEN
2112 IF NOT "simulate_here_v" THEN
2113 SELECT * INTO "delegation_row" FROM "delegation"
2114 WHERE "truster_id" = "output_row"."member_id"
2115 AND "unit_id" = "unit_id_v";
2116 END IF;
2117 ELSIF "scope_v" = 'area' THEN
2118 IF "simulate_here_v" THEN
2119 IF "simulate_trustee_id_p" ISNULL 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 ELSE
2125 SELECT * INTO "delegation_row" FROM "delegation"
2126 WHERE "truster_id" = "output_row"."member_id"
2127 AND (
2128 "unit_id" = "unit_id_v" OR
2129 "area_id" = "area_id_v"
2130 )
2131 ORDER BY "scope" DESC;
2132 END IF;
2133 ELSIF "scope_v" = 'issue' THEN
2134 IF "issue_row"."fully_frozen" ISNULL THEN
2135 "output_row"."participation" := EXISTS (
2136 SELECT NULL FROM "interest"
2137 WHERE "issue_id" = "issue_id_p"
2138 AND "member_id" = "output_row"."member_id"
2139 );
2140 ELSE
2141 IF "output_row"."member_id" = "member_id_p" THEN
2142 "output_row"."participation" := EXISTS (
2143 SELECT NULL FROM "direct_voter"
2144 WHERE "issue_id" = "issue_id_p"
2145 AND "member_id" = "output_row"."member_id"
2146 );
2147 ELSE
2148 "output_row"."participation" := NULL;
2149 END IF;
2150 END IF;
2151 IF "simulate_here_v" THEN
2152 IF "simulate_trustee_id_p" ISNULL THEN
2153 SELECT * INTO "delegation_row" FROM "delegation"
2154 WHERE "truster_id" = "output_row"."member_id"
2155 AND (
2156 "unit_id" = "unit_id_v" OR
2157 "area_id" = "area_id_v"
2158 )
2159 ORDER BY "scope" DESC;
2160 END IF;
2161 ELSE
2162 SELECT * INTO "delegation_row" FROM "delegation"
2163 WHERE "truster_id" = "output_row"."member_id"
2164 AND (
2165 "unit_id" = "unit_id_v" OR
2166 "area_id" = "area_id_v" OR
2167 "issue_id" = "issue_id_p"
2168 )
2169 ORDER BY "scope" DESC;
2170 END IF;
2171 END IF;
2172 ELSE
2173 "output_row"."participation" := FALSE;
2174 END IF;
2175 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
2176 "output_row"."scope_out" := "scope_v";
2177 "output_rows" := "output_rows" || "output_row";
2178 "output_row"."member_id" := "simulate_trustee_id_p";
2179 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2180 "output_row"."scope_out" := "delegation_row"."scope";
2181 "output_rows" := "output_rows" || "output_row";
2182 "output_row"."member_id" := "delegation_row"."trustee_id";
2183 ELSIF "delegation_row"."scope" NOTNULL THEN
2184 "output_row"."scope_out" := "delegation_row"."scope";
2185 "output_row"."disabled_out" := TRUE;
2186 "output_rows" := "output_rows" || "output_row";
2187 EXIT;
2188 ELSE
2189 "output_row"."scope_out" := NULL;
2190 "output_rows" := "output_rows" || "output_row";
2191 EXIT;
2192 END IF;
2193 EXIT WHEN "loop_member_id_v" NOTNULL;
2194 "output_row"."index" := "output_row"."index" + 1;
2195 END LOOP;
2196 "row_count" := array_upper("output_rows", 1);
2197 "i" := 1;
2198 "loop_v" := FALSE;
2199 LOOP
2200 "output_row" := "output_rows"["i"];
2201 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2202 IF "loop_v" THEN
2203 IF "i" + 1 = "row_count" THEN
2204 "output_row"."loop" := 'last';
2205 ELSIF "i" = "row_count" THEN
2206 "output_row"."loop" := 'repetition';
2207 ELSE
2208 "output_row"."loop" := 'intermediate';
2209 END IF;
2210 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2211 "output_row"."loop" := 'first';
2212 "loop_v" := TRUE;
2213 END IF;
2214 IF "scope_v" = 'unit' THEN
2215 "output_row"."participation" := NULL;
2216 END IF;
2217 RETURN NEXT "output_row";
2218 "i" := "i" + 1;
2219 END LOOP;
2220 RETURN;
2221 END;
2222 $$;
2225 CREATE OR REPLACE FUNCTION "get_initiatives_for_notification"
2226 ( "recipient_id_p" "member"."id"%TYPE )
2227 RETURNS SETOF "initiative_for_notification"
2228 LANGUAGE 'plpgsql' VOLATILE AS $$
2229 DECLARE
2230 "result_row" "initiative_for_notification"%ROWTYPE;
2231 "last_draft_id_v" "draft"."id"%TYPE;
2232 "last_suggestion_id_v" "suggestion"."id"%TYPE;
2233 BEGIN
2234 PERFORM "require_transaction_isolation"();
2235 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
2236 FOR "result_row" IN
2237 SELECT * FROM "initiative_for_notification"
2238 WHERE "recipient_id" = "recipient_id_p"
2239 LOOP
2240 SELECT "id" INTO "last_draft_id_v" FROM "draft"
2241 WHERE "draft"."initiative_id" = "result_row"."initiative_id"
2242 ORDER BY "id" DESC LIMIT 1;
2243 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
2244 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
2245 ORDER BY "id" DESC LIMIT 1;
2246 INSERT INTO "notification_initiative_sent"
2247 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
2248 VALUES (
2249 "recipient_id_p",
2250 "result_row"."initiative_id",
2251 "last_draft_id_v",
2252 "last_suggestion_id_v" )
2253 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
2254 "last_draft_id" = "last_draft_id_v",
2255 "last_suggestion_id" = "last_suggestion_id_v";
2256 RETURN NEXT "result_row";
2257 END LOOP;
2258 DELETE FROM "notification_initiative_sent"
2259 USING "initiative", "issue"
2260 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
2261 AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
2262 AND "issue"."id" = "initiative"."issue_id"
2263 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
2264 UPDATE "member" SET
2265 "notification_counter" = "notification_counter" + 1,
2266 "notification_sent" = now()
2267 WHERE "id" = "recipient_id_p";
2268 RETURN;
2269 END;
2270 $$;
2273 CREATE OR REPLACE FUNCTION "calculate_member_counts"()
2274 RETURNS VOID
2275 LANGUAGE 'plpgsql' VOLATILE AS $$
2276 BEGIN
2277 PERFORM "require_transaction_isolation"();
2278 DELETE FROM "member_count";
2279 INSERT INTO "member_count" ("total_count")
2280 SELECT "total_count" FROM "member_count_view";
2281 UPDATE "unit" SET "member_count" = "view"."member_count"
2282 FROM "unit_member_count" AS "view"
2283 WHERE "view"."unit_id" = "unit"."id";
2284 RETURN;
2285 END;
2286 $$;
2288 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"';
2291 CREATE FUNCTION "calculate_area_quorum"()
2292 RETURNS VOID
2293 LANGUAGE 'plpgsql' VOLATILE AS $$
2294 BEGIN
2295 PERFORM "dont_require_transaction_isolation"();
2296 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
2297 FROM "area_quorum" AS "view"
2298 WHERE "view"."area_id" = "area"."id";
2299 RETURN;
2300 END;
2301 $$;
2303 COMMENT ON FUNCTION "calculate_area_quorum"() IS 'Calculate column "issue_quorum" in table "area" from view "area_quorum"';
2306 DROP VIEW "remaining_harmonic_initiative_weight_summands";
2307 DROP VIEW "remaining_harmonic_supporter_weight";
2310 CREATE VIEW "remaining_harmonic_supporter_weight" AS
2311 SELECT
2312 "direct_interest_snapshot"."snapshot_id",
2313 "direct_interest_snapshot"."issue_id",
2314 "direct_interest_snapshot"."member_id",
2315 "direct_interest_snapshot"."weight" AS "weight_num",
2316 count("initiative"."id") AS "weight_den"
2317 FROM "issue"
2318 JOIN "direct_interest_snapshot"
2319 ON "issue"."latest_snapshot_id" = "direct_interest_snapshot"."snapshot_id"
2320 AND "issue"."id" = "direct_interest_snapshot"."issue_id"
2321 JOIN "initiative"
2322 ON "issue"."id" = "initiative"."issue_id"
2323 AND "initiative"."harmonic_weight" ISNULL
2324 JOIN "direct_supporter_snapshot"
2325 ON "issue"."latest_snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
2326 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
2327 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
2328 AND (
2329 "direct_supporter_snapshot"."satisfied" = TRUE OR
2330 coalesce("initiative"."admitted", FALSE) = FALSE
2331 )
2332 GROUP BY
2333 "direct_interest_snapshot"."snapshot_id",
2334 "direct_interest_snapshot"."issue_id",
2335 "direct_interest_snapshot"."member_id",
2336 "direct_interest_snapshot"."weight";
2339 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
2340 SELECT
2341 "initiative"."issue_id",
2342 "initiative"."id" AS "initiative_id",
2343 "initiative"."admitted",
2344 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
2345 "remaining_harmonic_supporter_weight"."weight_den"
2346 FROM "remaining_harmonic_supporter_weight"
2347 JOIN "initiative"
2348 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
2349 AND "initiative"."harmonic_weight" ISNULL
2350 JOIN "direct_supporter_snapshot"
2351 ON "remaining_harmonic_supporter_weight"."snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
2352 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
2353 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
2354 AND (
2355 "direct_supporter_snapshot"."satisfied" = TRUE OR
2356 coalesce("initiative"."admitted", FALSE) = FALSE
2357 )
2358 GROUP BY
2359 "initiative"."issue_id",
2360 "initiative"."id",
2361 "initiative"."admitted",
2362 "remaining_harmonic_supporter_weight"."weight_den";
2365 DROP FUNCTION "create_population_snapshot"
2366 ( "issue_id_p" "issue"."id"%TYPE );
2369 DROP FUNCTION "weight_of_added_delegations_for_population_snapshot"
2370 ( "issue_id_p" "issue"."id"%TYPE,
2371 "member_id_p" "member"."id"%TYPE,
2372 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE );
2375 DROP FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2376 ( "issue_id_p" "issue"."id"%TYPE,
2377 "member_id_p" "member"."id"%TYPE,
2378 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE );
2381 CREATE FUNCTION "weight_of_added_delegations_for_snapshot"
2382 ( "snapshot_id_p" "snapshot"."id"%TYPE,
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 )
2386 RETURNS "direct_interest_snapshot"."weight"%TYPE
2387 LANGUAGE 'plpgsql' VOLATILE AS $$
2388 DECLARE
2389 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2390 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2391 "weight_v" INT4;
2392 "sub_weight_v" INT4;
2393 BEGIN
2394 PERFORM "require_transaction_isolation"();
2395 "weight_v" := 0;
2396 FOR "issue_delegation_row" IN
2397 SELECT * FROM "issue_delegation"
2398 WHERE "trustee_id" = "member_id_p"
2399 AND "issue_id" = "issue_id_p"
2400 LOOP
2401 IF NOT EXISTS (
2402 SELECT NULL FROM "direct_interest_snapshot"
2403 WHERE "snapshot_id" = "snapshot_id_p"
2404 AND "issue_id" = "issue_id_p"
2405 AND "member_id" = "issue_delegation_row"."truster_id"
2406 ) AND NOT EXISTS (
2407 SELECT NULL FROM "delegating_interest_snapshot"
2408 WHERE "snapshot_id" = "snapshot_id_p"
2409 AND "issue_id" = "issue_id_p"
2410 AND "member_id" = "issue_delegation_row"."truster_id"
2411 ) THEN
2412 "delegate_member_ids_v" :=
2413 "member_id_p" || "delegate_member_ids_p";
2414 INSERT INTO "delegating_interest_snapshot" (
2415 "snapshot_id",
2416 "issue_id",
2417 "member_id",
2418 "scope",
2419 "delegate_member_ids"
2420 ) VALUES (
2421 "snapshot_id_p",
2422 "issue_id_p",
2423 "issue_delegation_row"."truster_id",
2424 "issue_delegation_row"."scope",
2425 "delegate_member_ids_v"
2426 );
2427 "sub_weight_v" := 1 +
2428 "weight_of_added_delegations_for_snapshot"(
2429 "snapshot_id_p",
2430 "issue_id_p",
2431 "issue_delegation_row"."truster_id",
2432 "delegate_member_ids_v"
2433 );
2434 UPDATE "delegating_interest_snapshot"
2435 SET "weight" = "sub_weight_v"
2436 WHERE "snapshot_id" = "snapshot_id_p"
2437 AND "issue_id" = "issue_id_p"
2438 AND "member_id" = "issue_delegation_row"."truster_id";
2439 "weight_v" := "weight_v" + "sub_weight_v";
2440 END IF;
2441 END LOOP;
2442 RETURN "weight_v";
2443 END;
2444 $$;
2446 COMMENT ON FUNCTION "weight_of_added_delegations_for_snapshot"
2447 ( "snapshot"."id"%TYPE,
2448 "issue"."id"%TYPE,
2449 "member"."id"%TYPE,
2450 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2451 IS 'Helper function for "fill_snapshot" function';
2454 DROP FUNCTION "create_interest_snapshot"
2455 ( "issue_id_p" "issue"."id"%TYPE );
2458 DROP FUNCTION "create_snapshot"
2459 ( "issue_id_p" "issue"."id"%TYPE );
2462 CREATE FUNCTION "take_snapshot"
2463 ( "issue_id_p" "issue"."id"%TYPE,
2464 "area_id_p" "area"."id"%TYPE = NULL )
2465 RETURNS "snapshot"."id"%TYPE
2466 LANGUAGE 'plpgsql' VOLATILE AS $$
2467 DECLARE
2468 "area_id_v" "area"."id"%TYPE;
2469 "unit_id_v" "unit"."id"%TYPE;
2470 "snapshot_id_v" "snapshot"."id"%TYPE;
2471 "issue_id_v" "issue"."id"%TYPE;
2472 "member_id_v" "member"."id"%TYPE;
2473 BEGIN
2474 IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN
2475 RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL';
2476 END IF;
2477 PERFORM "require_transaction_isolation"();
2478 IF "issue_id_p" ISNULL THEN
2479 "area_id_v" := "area_id_p";
2480 ELSE
2481 SELECT "area_id" INTO "area_id_v"
2482 FROM "issue" WHERE "id" = "issue_id_p";
2483 END IF;
2484 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_p";
2485 INSERT INTO "snapshot" ("area_id", "issue_id")
2486 VALUES ("area_id_v", "issue_id_p")
2487 RETURNING "id" INTO "snapshot_id_v";
2488 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
2489 SELECT "snapshot_id_v", "member_id"
2490 FROM "unit_member" WHERE "unit_id" = "unit_id_v";
2491 UPDATE "snapshot" SET
2492 "population" = (
2493 SELECT count(1) FROM "snapshot_population"
2494 WHERE "snapshot_id" = "snapshot_id_v"
2495 ) WHERE "id" = "snapshot_id_v";
2496 FOR "issue_id_v" IN
2497 SELECT "id" FROM "issue"
2498 WHERE CASE WHEN "issue_id_p" ISNULL THEN
2499 "area_id" = "area_id_p" AND
2500 "state" = 'admission'
2501 ELSE
2502 "id" = "issue_id_p"
2503 END
2504 LOOP
2505 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
2506 VALUES ("snapshot_id_v", "issue_id_v");
2507 INSERT INTO "direct_interest_snapshot"
2508 ("snapshot_id", "issue_id", "member_id")
2509 SELECT
2510 "snapshot_id_v" AS "snapshot_id",
2511 "issue_id_v" AS "issue_id",
2512 "member"."id" AS "member_id"
2513 FROM "issue"
2514 JOIN "area" ON "issue"."area_id" = "area"."id"
2515 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2516 JOIN "member" ON "interest"."member_id" = "member"."id"
2517 JOIN "privilege"
2518 ON "privilege"."unit_id" = "area"."unit_id"
2519 AND "privilege"."member_id" = "member"."id"
2520 WHERE "issue"."id" = "issue_id_v"
2521 AND "member"."active" AND "privilege"."voting_right";
2522 FOR "member_id_v" IN
2523 SELECT "member_id" FROM "direct_interest_snapshot"
2524 WHERE "snapshot_id" = "snapshot_id_v"
2525 AND "issue_id" = "issue_id_v"
2526 LOOP
2527 UPDATE "direct_interest_snapshot" SET
2528 "weight" = 1 +
2529 "weight_of_added_delegations_for_snapshot"(
2530 "snapshot_id_v",
2531 "issue_id_v",
2532 "member_id_v",
2533 '{}'
2534 )
2535 WHERE "snapshot_id" = "snapshot_id_v"
2536 AND "issue_id" = "issue_id_v"
2537 AND "member_id" = "member_id_v";
2538 END LOOP;
2539 INSERT INTO "direct_supporter_snapshot"
2540 ( "snapshot_id", "issue_id", "initiative_id", "member_id",
2541 "draft_id", "informed", "satisfied" )
2542 SELECT
2543 "snapshot_id_v" AS "snapshot_id",
2544 "issue_id_v" AS "issue_id",
2545 "initiative"."id" AS "initiative_id",
2546 "supporter"."member_id" AS "member_id",
2547 "supporter"."draft_id" AS "draft_id",
2548 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2549 NOT EXISTS (
2550 SELECT NULL FROM "critical_opinion"
2551 WHERE "initiative_id" = "initiative"."id"
2552 AND "member_id" = "supporter"."member_id"
2553 ) AS "satisfied"
2554 FROM "initiative"
2555 JOIN "supporter"
2556 ON "supporter"."initiative_id" = "initiative"."id"
2557 JOIN "current_draft"
2558 ON "initiative"."id" = "current_draft"."initiative_id"
2559 JOIN "direct_interest_snapshot"
2560 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"
2561 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"
2562 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
2563 WHERE "initiative"."issue_id" = "issue_id_v";
2564 DELETE FROM "temporary_suggestion_counts";
2565 INSERT INTO "temporary_suggestion_counts"
2566 ( "id",
2567 "minus2_unfulfilled_count", "minus2_fulfilled_count",
2568 "minus1_unfulfilled_count", "minus1_fulfilled_count",
2569 "plus1_unfulfilled_count", "plus1_fulfilled_count",
2570 "plus2_unfulfilled_count", "plus2_fulfilled_count" )
2571 SELECT
2572 "suggestion"."id",
2573 ( SELECT coalesce(sum("di"."weight"), 0)
2574 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2575 ON "di"."snapshot_id" = "snapshot_id_v"
2576 AND "di"."issue_id" = "issue_id_v"
2577 AND "di"."member_id" = "opinion"."member_id"
2578 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2579 AND "opinion"."degree" = -2
2580 AND "opinion"."fulfilled" = FALSE
2581 ) AS "minus2_unfulfilled_count",
2582 ( SELECT coalesce(sum("di"."weight"), 0)
2583 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2584 ON "di"."snapshot_id" = "snapshot_id_v"
2585 AND "di"."issue_id" = "issue_id_v"
2586 AND "di"."member_id" = "opinion"."member_id"
2587 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2588 AND "opinion"."degree" = -2
2589 AND "opinion"."fulfilled" = TRUE
2590 ) AS "minus2_fulfilled_count",
2591 ( SELECT coalesce(sum("di"."weight"), 0)
2592 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2593 ON "di"."snapshot_id" = "snapshot_id_v"
2594 AND "di"."issue_id" = "issue_id_v"
2595 AND "di"."member_id" = "opinion"."member_id"
2596 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2597 AND "opinion"."degree" = -1
2598 AND "opinion"."fulfilled" = FALSE
2599 ) AS "minus1_unfulfilled_count",
2600 ( SELECT coalesce(sum("di"."weight"), 0)
2601 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2602 ON "di"."snapshot_id" = "snapshot_id_v"
2603 AND "di"."issue_id" = "issue_id_v"
2604 AND "di"."member_id" = "opinion"."member_id"
2605 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2606 AND "opinion"."degree" = -1
2607 AND "opinion"."fulfilled" = TRUE
2608 ) AS "minus1_fulfilled_count",
2609 ( SELECT coalesce(sum("di"."weight"), 0)
2610 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2611 ON "di"."snapshot_id" = "snapshot_id_v"
2612 AND "di"."issue_id" = "issue_id_v"
2613 AND "di"."member_id" = "opinion"."member_id"
2614 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2615 AND "opinion"."degree" = 1
2616 AND "opinion"."fulfilled" = FALSE
2617 ) AS "plus1_unfulfilled_count",
2618 ( SELECT coalesce(sum("di"."weight"), 0)
2619 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2620 ON "di"."snapshot_id" = "snapshot_id_v"
2621 AND "di"."issue_id" = "issue_id_v"
2622 AND "di"."member_id" = "opinion"."member_id"
2623 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2624 AND "opinion"."degree" = 1
2625 AND "opinion"."fulfilled" = TRUE
2626 ) AS "plus1_fulfilled_count",
2627 ( SELECT coalesce(sum("di"."weight"), 0)
2628 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2629 ON "di"."snapshot_id" = "snapshot_id_v"
2630 AND "di"."issue_id" = "issue_id_v"
2631 AND "di"."member_id" = "opinion"."member_id"
2632 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2633 AND "opinion"."degree" = 2
2634 AND "opinion"."fulfilled" = FALSE
2635 ) AS "plus2_unfulfilled_count",
2636 ( SELECT coalesce(sum("di"."weight"), 0)
2637 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2638 ON "di"."snapshot_id" = "snapshot_id_v"
2639 AND "di"."issue_id" = "issue_id_v"
2640 AND "di"."member_id" = "opinion"."member_id"
2641 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2642 AND "opinion"."degree" = 2
2643 AND "opinion"."fulfilled" = TRUE
2644 ) AS "plus2_fulfilled_count"
2645 FROM "suggestion" JOIN "initiative"
2646 ON "suggestion"."initiative_id" = "initiative"."id"
2647 WHERE "initiative"."issue_id" = "issue_id_v";
2648 END LOOP;
2649 RETURN "snapshot_id_v";
2650 END;
2651 $$;
2653 COMMENT ON FUNCTION "take_snapshot"
2654 ( "issue"."id"%TYPE,
2655 "area"."id"%TYPE )
2656 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.';
2659 DROP FUNCTION "set_snapshot_event"
2660 ( "issue_id_p" "issue"."id"%TYPE,
2661 "event_p" "snapshot_event" );
2664 CREATE FUNCTION "finish_snapshot"
2665 ( "issue_id_p" "issue"."id"%TYPE )
2666 RETURNS VOID
2667 LANGUAGE 'plpgsql' VOLATILE AS $$
2668 DECLARE
2669 "snapshot_id_v" "snapshot"."id"%TYPE;
2670 BEGIN
2671 -- NOTE: function does not require snapshot isolation but we don't call
2672 -- "dont_require_snapshot_isolation" here because this function is
2673 -- also invoked by "check_issue"
2674 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
2675 SELECT "id" INTO "snapshot_id_v" FROM "snapshot"
2676 ORDER BY "id" DESC LIMIT 1;
2677 UPDATE "issue" SET
2678 "calculated" = "snapshot"."calculated",
2679 "latest_snapshot_id" = "snapshot_id_v",
2680 "population" = "snapshot"."population"
2681 FROM "snapshot"
2682 WHERE "issue"."id" = "issue_id_p"
2683 AND "snapshot"."id" = "snapshot_id_v";
2684 UPDATE "initiative" SET
2685 "supporter_count" = (
2686 SELECT coalesce(sum("di"."weight"), 0)
2687 FROM "direct_interest_snapshot" AS "di"
2688 JOIN "direct_supporter_snapshot" AS "ds"
2689 ON "di"."member_id" = "ds"."member_id"
2690 WHERE "di"."snapshot_id" = "snapshot_id_v"
2691 AND "di"."issue_id" = "issue_id_p"
2692 AND "ds"."snapshot_id" = "snapshot_id_v"
2693 AND "ds"."initiative_id" = "initiative"."id"
2694 ),
2695 "informed_supporter_count" = (
2696 SELECT coalesce(sum("di"."weight"), 0)
2697 FROM "direct_interest_snapshot" AS "di"
2698 JOIN "direct_supporter_snapshot" AS "ds"
2699 ON "di"."member_id" = "ds"."member_id"
2700 WHERE "di"."snapshot_id" = "snapshot_id_v"
2701 AND "di"."issue_id" = "issue_id_p"
2702 AND "ds"."snapshot_id" = "snapshot_id_v"
2703 AND "ds"."initiative_id" = "initiative"."id"
2704 AND "ds"."informed"
2705 ),
2706 "satisfied_supporter_count" = (
2707 SELECT coalesce(sum("di"."weight"), 0)
2708 FROM "direct_interest_snapshot" AS "di"
2709 JOIN "direct_supporter_snapshot" AS "ds"
2710 ON "di"."member_id" = "ds"."member_id"
2711 WHERE "di"."snapshot_id" = "snapshot_id_v"
2712 AND "di"."issue_id" = "issue_id_p"
2713 AND "ds"."snapshot_id" = "snapshot_id_v"
2714 AND "ds"."initiative_id" = "initiative"."id"
2715 AND "ds"."satisfied"
2716 ),
2717 "satisfied_informed_supporter_count" = (
2718 SELECT coalesce(sum("di"."weight"), 0)
2719 FROM "direct_interest_snapshot" AS "di"
2720 JOIN "direct_supporter_snapshot" AS "ds"
2721 ON "di"."member_id" = "ds"."member_id"
2722 WHERE "di"."snapshot_id" = "snapshot_id_v"
2723 AND "di"."issue_id" = "issue_id_p"
2724 AND "ds"."snapshot_id" = "snapshot_id_v"
2725 AND "ds"."initiative_id" = "initiative"."id"
2726 AND "ds"."informed"
2727 AND "ds"."satisfied"
2728 )
2729 WHERE "issue_id" = "issue_id_p";
2730 UPDATE "suggestion" SET
2731 "minus2_unfulfilled_count" = "temp"."minus2_unfulfilled_count",
2732 "minus2_fulfilled_count" = "temp"."minus2_fulfilled_count",
2733 "minus1_unfulfilled_count" = "temp"."minus1_unfulfilled_count",
2734 "minus1_fulfilled_count" = "temp"."minus1_fulfilled_count",
2735 "plus1_unfulfilled_count" = "temp"."plus1_unfulfilled_count",
2736 "plus1_fulfilled_count" = "temp"."plus1_fulfilled_count",
2737 "plus2_unfulfilled_count" = "temp"."plus2_unfulfilled_count",
2738 "plus2_fulfilled_count" = "temp"."plus2_fulfilled_count"
2739 FROM "temporary_suggestion_counts" AS "temp", "initiative"
2740 WHERE "temp"."id" = "suggestion"."id"
2741 AND "initiative"."issue_id" = "issue_id_p"
2742 AND "suggestion"."initiative_id" = "initiative"."id";
2743 DELETE FROM "temporary_suggestion_counts";
2744 RETURN;
2745 END;
2746 $$;
2748 COMMENT ON FUNCTION "finish_snapshot"
2749 ( "issue"."id"%TYPE )
2750 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)';
2753 CREATE FUNCTION "issue_admission"
2754 ( "area_id_p" "area"."id"%TYPE )
2755 RETURNS BOOLEAN
2756 LANGUAGE 'plpgsql' VOLATILE AS $$
2757 DECLARE
2758 "issue_id_v" "issue"."id"%TYPE;
2759 BEGIN
2760 PERFORM "dont_require_transaction_isolation"();
2761 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
2762 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
2763 FROM "area_quorum" AS "view"
2764 WHERE "area"."id" = "view"."area_id"
2765 AND "area"."id" = "area_id_p";
2766 SELECT "id" INTO "issue_id_v" FROM "issue_for_admission"
2767 WHERE "area_id" = "area_id_p";
2768 IF "issue_id_v" ISNULL THEN RETURN FALSE; END IF;
2769 UPDATE "issue" SET
2770 "admission_snapshot_id" = "latest_snapshot_id",
2771 "state" = 'discussion',
2772 "accepted" = now(),
2773 "phase_finished" = NULL
2774 WHERE "id" = "issue_id_v";
2775 RETURN TRUE;
2776 END;
2777 $$;
2779 COMMENT ON FUNCTION "issue_admission"
2780 ( "area"."id"%TYPE )
2781 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';
2784 CREATE OR REPLACE FUNCTION "check_issue"
2785 ( "issue_id_p" "issue"."id"%TYPE,
2786 "persist" "check_issue_persistence" )
2787 RETURNS "check_issue_persistence"
2788 LANGUAGE 'plpgsql' VOLATILE AS $$
2789 DECLARE
2790 "issue_row" "issue"%ROWTYPE;
2791 "last_calculated_v" "snapshot"."calculated"%TYPE;
2792 "policy_row" "policy"%ROWTYPE;
2793 "initiative_row" "initiative"%ROWTYPE;
2794 "state_v" "issue_state";
2795 BEGIN
2796 PERFORM "require_transaction_isolation"();
2797 IF "persist" ISNULL THEN
2798 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
2799 FOR UPDATE;
2800 SELECT "calculated" INTO "last_calculated_v"
2801 FROM "snapshot" JOIN "snapshot_issue"
2802 ON "snapshot"."id" = "snapshot_issue"."snapshot_id"
2803 WHERE "snapshot_issue"."issue_id" = "issue_id_p";
2804 IF "issue_row"."closed" NOTNULL THEN
2805 RETURN NULL;
2806 END IF;
2807 "persist"."state" := "issue_row"."state";
2808 IF
2809 ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=
2810 "issue_row"."created" + "issue_row"."max_admission_time" ) OR
2811 ( "issue_row"."state" = 'discussion' AND now() >=
2812 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
2813 ( "issue_row"."state" = 'verification' AND now() >=
2814 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
2815 ( "issue_row"."state" = 'voting' AND now() >=
2816 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
2817 THEN
2818 "persist"."phase_finished" := TRUE;
2819 ELSE
2820 "persist"."phase_finished" := FALSE;
2821 END IF;
2822 IF
2823 NOT EXISTS (
2824 -- all initiatives are revoked
2825 SELECT NULL FROM "initiative"
2826 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
2827 ) AND (
2828 -- and issue has not been accepted yet
2829 "persist"."state" = 'admission' OR
2830 -- or verification time has elapsed
2831 ( "persist"."state" = 'verification' AND
2832 "persist"."phase_finished" ) OR
2833 -- or no initiatives have been revoked lately
2834 NOT EXISTS (
2835 SELECT NULL FROM "initiative"
2836 WHERE "issue_id" = "issue_id_p"
2837 AND now() < "revoked" + "issue_row"."verification_time"
2838 )
2839 )
2840 THEN
2841 "persist"."issue_revoked" := TRUE;
2842 ELSE
2843 "persist"."issue_revoked" := FALSE;
2844 END IF;
2845 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
2846 UPDATE "issue" SET "phase_finished" = now()
2847 WHERE "id" = "issue_row"."id";
2848 RETURN "persist";
2849 ELSIF
2850 "persist"."state" IN ('admission', 'discussion', 'verification')
2851 THEN
2852 RETURN "persist";
2853 ELSE
2854 RETURN NULL;
2855 END IF;
2856 END IF;
2857 IF
2858 "persist"."state" IN ('admission', 'discussion', 'verification') AND
2859 coalesce("persist"."snapshot_created", FALSE) = FALSE
2860 THEN
2861 IF "persist"."state" != 'admission' THEN
2862 PERFORM "take_snapshot"("issue_id_p");
2863 PERFORM "finish_snapshot"("issue_id_p");
2864 END IF;
2865 "persist"."snapshot_created" = TRUE;
2866 IF "persist"."phase_finished" THEN
2867 IF "persist"."state" = 'admission' THEN
2868 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id";
2869 ELSIF "persist"."state" = 'discussion' THEN
2870 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id";
2871 ELSIF "persist"."state" = 'verification' THEN
2872 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id";
2873 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2874 SELECT * INTO "policy_row" FROM "policy"
2875 WHERE "id" = "issue_row"."policy_id";
2876 FOR "initiative_row" IN
2877 SELECT * FROM "initiative"
2878 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
2879 FOR UPDATE
2880 LOOP
2881 IF
2882 "initiative_row"."polling" OR (
2883 "initiative_row"."satisfied_supporter_count" >
2884 "policy_row"."initiative_quorum" AND
2885 "initiative_row"."satisfied_supporter_count" *
2886 "policy_row"."initiative_quorum_den" >=
2887 "issue_row"."population" * "policy_row"."initiative_quorum_num"
2888 )
2889 THEN
2890 UPDATE "initiative" SET "admitted" = TRUE
2891 WHERE "id" = "initiative_row"."id";
2892 ELSE
2893 UPDATE "initiative" SET "admitted" = FALSE
2894 WHERE "id" = "initiative_row"."id";
2895 END IF;
2896 END LOOP;
2897 END IF;
2898 END IF;
2899 RETURN "persist";
2900 END IF;
2901 IF
2902 "persist"."state" IN ('admission', 'discussion', 'verification') AND
2903 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
2904 THEN
2905 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
2906 "persist"."harmonic_weights_set" = TRUE;
2907 IF
2908 "persist"."phase_finished" OR
2909 "persist"."issue_revoked" OR
2910 "persist"."state" = 'admission'
2911 THEN
2912 RETURN "persist";
2913 ELSE
2914 RETURN NULL;
2915 END IF;
2916 END IF;
2917 IF "persist"."issue_revoked" THEN
2918 IF "persist"."state" = 'admission' THEN
2919 "state_v" := 'canceled_revoked_before_accepted';
2920 ELSIF "persist"."state" = 'discussion' THEN
2921 "state_v" := 'canceled_after_revocation_during_discussion';
2922 ELSIF "persist"."state" = 'verification' THEN
2923 "state_v" := 'canceled_after_revocation_during_verification';
2924 END IF;
2925 UPDATE "issue" SET
2926 "state" = "state_v",
2927 "closed" = "phase_finished",
2928 "phase_finished" = NULL
2929 WHERE "id" = "issue_id_p";
2930 RETURN NULL;
2931 END IF;
2932 IF "persist"."state" = 'admission' THEN
2933 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
2934 FOR UPDATE;
2935 IF "issue_row"."phase_finished" NOTNULL THEN
2936 UPDATE "issue" SET
2937 "state" = 'canceled_issue_not_accepted',
2938 "closed" = "phase_finished",
2939 "phase_finished" = NULL
2940 WHERE "id" = "issue_id_p";
2941 END IF;
2942 RETURN NULL;
2943 END IF;
2944 IF "persist"."phase_finished" THEN
2945 IF "persist"."state" = 'discussion' THEN
2946 UPDATE "issue" SET
2947 "state" = 'verification',
2948 "half_frozen" = "phase_finished",
2949 "phase_finished" = NULL
2950 WHERE "id" = "issue_id_p";
2951 RETURN NULL;
2952 END IF;
2953 IF "persist"."state" = 'verification' THEN
2954 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
2955 FOR UPDATE;
2956 SELECT * INTO "policy_row" FROM "policy"
2957 WHERE "id" = "issue_row"."policy_id";
2958 IF EXISTS (
2959 SELECT NULL FROM "initiative"
2960 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
2961 ) THEN
2962 UPDATE "issue" SET
2963 "state" = 'voting',
2964 "fully_frozen" = "phase_finished",
2965 "phase_finished" = NULL
2966 WHERE "id" = "issue_id_p";
2967 ELSE
2968 UPDATE "issue" SET
2969 "state" = 'canceled_no_initiative_admitted',
2970 "fully_frozen" = "phase_finished",
2971 "closed" = "phase_finished",
2972 "phase_finished" = NULL
2973 WHERE "id" = "issue_id_p";
2974 -- NOTE: The following DELETE statements have effect only when
2975 -- issue state has been manipulated
2976 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
2977 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
2978 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
2979 END IF;
2980 RETURN NULL;
2981 END IF;
2982 IF "persist"."state" = 'voting' THEN
2983 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
2984 PERFORM "close_voting"("issue_id_p");
2985 "persist"."closed_voting" = TRUE;
2986 RETURN "persist";
2987 END IF;
2988 PERFORM "calculate_ranks"("issue_id_p");
2989 RETURN NULL;
2990 END IF;
2991 END IF;
2992 RAISE WARNING 'should not happen';
2993 RETURN NULL;
2994 END;
2995 $$;
2998 CREATE OR REPLACE FUNCTION "check_everything"()
2999 RETURNS VOID
3000 LANGUAGE 'plpgsql' VOLATILE AS $$
3001 DECLARE
3002 "area_id_v" "area"."id"%TYPE;
3003 "snapshot_id_v" "snapshot"."id"%TYPE;
3004 "issue_id_v" "issue"."id"%TYPE;
3005 "persist_v" "check_issue_persistence";
3006 BEGIN
3007 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
3008 DELETE FROM "expired_session";
3009 DELETE FROM "expired_token";
3010 DELETE FROM "expired_snapshot";
3011 PERFORM "check_activity"();
3012 PERFORM "calculate_member_counts"();
3013 FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP
3014 SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v";
3015 PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"
3016 WHERE "snapshot_id" = "snapshot_id_v";
3017 LOOP
3018 EXIT WHEN "issue_admission"("area_id_v") = FALSE;
3019 END LOOP;
3020 END LOOP;
3021 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
3022 "persist_v" := NULL;
3023 LOOP
3024 "persist_v" := "check_issue"("issue_id_v", "persist_v");
3025 EXIT WHEN "persist_v" ISNULL;
3026 END LOOP;
3027 END LOOP;
3028 RETURN;
3029 END;
3030 $$;
3032 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';
3035 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
3036 RETURNS VOID
3037 LANGUAGE 'plpgsql' VOLATILE AS $$
3038 BEGIN
3039 IF EXISTS (
3040 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
3041 ) THEN
3042 -- override protection triggers:
3043 INSERT INTO "temporary_transaction_data" ("key", "value")
3044 VALUES ('override_protection_triggers', TRUE::TEXT);
3045 -- clean data:
3046 DELETE FROM "delegating_voter"
3047 WHERE "issue_id" = "issue_id_p";
3048 DELETE FROM "direct_voter"
3049 WHERE "issue_id" = "issue_id_p";
3050 DELETE FROM "delegating_interest_snapshot"
3051 WHERE "issue_id" = "issue_id_p";
3052 DELETE FROM "direct_interest_snapshot"
3053 WHERE "issue_id" = "issue_id_p";
3054 DELETE FROM "non_voter"
3055 WHERE "issue_id" = "issue_id_p";
3056 DELETE FROM "delegation"
3057 WHERE "issue_id" = "issue_id_p";
3058 DELETE FROM "supporter"
3059 USING "initiative" -- NOTE: due to missing index on issue_id
3060 WHERE "initiative"."issue_id" = "issue_id_p"
3061 AND "supporter"."initiative_id" = "initiative_id";
3062 -- mark issue as cleaned:
3063 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
3064 -- finish overriding protection triggers (avoids garbage):
3065 DELETE FROM "temporary_transaction_data"
3066 WHERE "key" = 'override_protection_triggers';
3067 END IF;
3068 RETURN;
3069 END;
3070 $$;
3073 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
3074 RETURNS VOID
3075 LANGUAGE 'plpgsql' VOLATILE AS $$
3076 BEGIN
3077 UPDATE "member" SET
3078 "last_login" = NULL,
3079 "last_delegation_check" = NULL,
3080 "login" = NULL,
3081 "password" = NULL,
3082 "authority" = NULL,
3083 "authority_uid" = NULL,
3084 "authority_login" = NULL,
3085 "locked" = TRUE,
3086 "active" = FALSE,
3087 "notify_email" = NULL,
3088 "notify_email_unconfirmed" = NULL,
3089 "notify_email_secret" = NULL,
3090 "notify_email_secret_expiry" = NULL,
3091 "notify_email_lock_expiry" = NULL,
3092 "disable_notifications" = TRUE,
3093 "notification_counter" = DEFAULT,
3094 "notification_sample_size" = 0,
3095 "notification_dow" = NULL,
3096 "notification_hour" = NULL,
3097 "login_recovery_expiry" = NULL,
3098 "password_reset_secret" = NULL,
3099 "password_reset_secret_expiry" = NULL,
3100 "location" = NULL
3101 WHERE "id" = "member_id_p";
3102 -- "text_search_data" is updated by triggers
3103 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
3104 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
3105 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
3106 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
3107 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
3108 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
3109 DELETE FROM "session" WHERE "member_id" = "member_id_p";
3110 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
3111 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
3112 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
3113 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
3114 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
3115 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
3116 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
3117 DELETE FROM "direct_voter" USING "issue"
3118 WHERE "direct_voter"."issue_id" = "issue"."id"
3119 AND "issue"."closed" ISNULL
3120 AND "member_id" = "member_id_p";
3121 RETURN;
3122 END;
3123 $$;
3126 CREATE OR REPLACE FUNCTION "delete_private_data"()
3127 RETURNS VOID
3128 LANGUAGE 'plpgsql' VOLATILE AS $$
3129 BEGIN
3130 DELETE FROM "temporary_transaction_data";
3131 DELETE FROM "member" WHERE "activated" ISNULL;
3132 UPDATE "member" SET
3133 "invite_code" = NULL,
3134 "invite_code_expiry" = NULL,
3135 "admin_comment" = NULL,
3136 "last_login" = NULL,
3137 "last_delegation_check" = NULL,
3138 "login" = NULL,
3139 "password" = NULL,
3140 "authority" = NULL,
3141 "authority_uid" = NULL,
3142 "authority_login" = NULL,
3143 "lang" = NULL,
3144 "notify_email" = NULL,
3145 "notify_email_unconfirmed" = NULL,
3146 "notify_email_secret" = NULL,
3147 "notify_email_secret_expiry" = NULL,
3148 "notify_email_lock_expiry" = NULL,
3149 "disable_notifications" = TRUE,
3150 "notification_counter" = DEFAULT,
3151 "notification_sample_size" = 0,
3152 "notification_dow" = NULL,
3153 "notification_hour" = NULL,
3154 "login_recovery_expiry" = NULL,
3155 "password_reset_secret" = NULL,
3156 "password_reset_secret_expiry" = NULL,
3157 "location" = NULL;
3158 -- "text_search_data" is updated by triggers
3159 DELETE FROM "setting";
3160 DELETE FROM "setting_map";
3161 DELETE FROM "member_relation_setting";
3162 DELETE FROM "member_image";
3163 DELETE FROM "contact";
3164 DELETE FROM "ignored_member";
3165 DELETE FROM "session";
3166 DELETE FROM "area_setting";
3167 DELETE FROM "issue_setting";
3168 DELETE FROM "ignored_initiative";
3169 DELETE FROM "initiative_setting";
3170 DELETE FROM "suggestion_setting";
3171 DELETE FROM "non_voter";
3172 DELETE FROM "direct_voter" USING "issue"
3173 WHERE "direct_voter"."issue_id" = "issue"."id"
3174 AND "issue"."closed" ISNULL;
3175 RETURN;
3176 END;
3177 $$;
3180 CREATE TEMPORARY TABLE "old_snapshot" AS
3181 SELECT "ordered".*, row_number() OVER () AS "snapshot_id"
3182 FROM (
3183 SELECT * FROM (
3184 SELECT
3185 "id" AS "issue_id",
3186 'end_of_admission'::"snapshot_event" AS "event",
3187 "accepted" AS "calculated"
3188 FROM "issue" WHERE "accepted" NOTNULL
3189 UNION ALL
3190 SELECT
3191 "id" AS "issue_id",
3192 'half_freeze'::"snapshot_event" AS "event",
3193 "half_frozen" AS "calculated"
3194 FROM "issue" WHERE "half_frozen" NOTNULL
3195 UNION ALL
3196 SELECT
3197 "id" AS "issue_id",
3198 'full_freeze'::"snapshot_event" AS "event",
3199 "fully_frozen" AS "calculated"
3200 FROM "issue" WHERE "fully_frozen" NOTNULL
3201 ) AS "unordered"
3202 ORDER BY "calculated", "issue_id", "event"
3203 ) AS "ordered";
3206 INSERT INTO "snapshot" ("id", "calculated", "population", "area_id", "issue_id")
3207 SELECT
3208 "old_snapshot"."snapshot_id" AS "id",
3209 "old_snapshot"."calculated",
3210 ( SELECT COALESCE(sum("weight"), 0)
3211 FROM "direct_population_snapshot" "dps"
3212 WHERE "dps"."issue_id" = "old_snapshot"."issue_id"
3213 AND "dps"."event" = "old_snapshot"."event"
3214 ) AS "population",
3215 "issue"."area_id" AS "area_id",
3216 "issue"."id" AS "issue_id"
3217 FROM "old_snapshot" JOIN "issue"
3218 ON "old_snapshot"."issue_id" = "issue"."id";
3221 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
3222 SELECT "id" AS "snapshot_id", "issue_id" FROM "snapshot";
3225 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
3226 SELECT
3227 "old_snapshot"."snapshot_id",
3228 "direct_population_snapshot"."member_id"
3229 FROM "old_snapshot" JOIN "direct_population_snapshot"
3230 ON "old_snapshot"."issue_id" = "direct_population_snapshot"."issue_id"
3231 AND "old_snapshot"."event" = "direct_population_snapshot"."event";
3233 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
3234 SELECT
3235 "old_snapshot"."snapshot_id",
3236 "delegating_population_snapshot"."member_id"
3237 FROM "old_snapshot" JOIN "delegating_population_snapshot"
3238 ON "old_snapshot"."issue_id" = "delegating_population_snapshot"."issue_id"
3239 AND "old_snapshot"."event" = "delegating_population_snapshot"."event";
3242 INSERT INTO "direct_interest_snapshot"
3243 ("snapshot_id", "issue_id", "member_id", "weight")
3244 SELECT
3245 "old_snapshot"."snapshot_id",
3246 "old_snapshot"."issue_id",
3247 "direct_interest_snapshot_old"."member_id",
3248 "direct_interest_snapshot_old"."weight"
3249 FROM "old_snapshot" JOIN "direct_interest_snapshot_old"
3250 ON "old_snapshot"."issue_id" = "direct_interest_snapshot_old"."issue_id"
3251 AND "old_snapshot"."event" = "direct_interest_snapshot_old"."event";
3253 INSERT INTO "delegating_interest_snapshot"
3254 ( "snapshot_id", "issue_id",
3255 "member_id", "weight", "scope", "delegate_member_ids" )
3256 SELECT
3257 "old_snapshot"."snapshot_id",
3258 "old_snapshot"."issue_id",
3259 "delegating_interest_snapshot_old"."member_id",
3260 "delegating_interest_snapshot_old"."weight",
3261 "delegating_interest_snapshot_old"."scope",
3262 "delegating_interest_snapshot_old"."delegate_member_ids"
3263 FROM "old_snapshot" JOIN "delegating_interest_snapshot_old"
3264 ON "old_snapshot"."issue_id" = "delegating_interest_snapshot_old"."issue_id"
3265 AND "old_snapshot"."event" = "delegating_interest_snapshot_old"."event";
3267 INSERT INTO "direct_supporter_snapshot"
3268 ( "snapshot_id", "issue_id",
3269 "initiative_id", "member_id", "draft_id", "informed", "satisfied" )
3270 SELECT
3271 "old_snapshot"."snapshot_id",
3272 "old_snapshot"."issue_id",
3273 "direct_supporter_snapshot_old"."initiative_id",
3274 "direct_supporter_snapshot_old"."member_id",
3275 "direct_supporter_snapshot_old"."draft_id",
3276 "direct_supporter_snapshot_old"."informed",
3277 "direct_supporter_snapshot_old"."satisfied"
3278 FROM "old_snapshot" JOIN "direct_supporter_snapshot_old"
3279 ON "old_snapshot"."issue_id" = "direct_supporter_snapshot_old"."issue_id"
3280 AND "old_snapshot"."event" = "direct_supporter_snapshot_old"."event";
3283 ALTER TABLE "issue" DISABLE TRIGGER USER; -- NOTE: required to modify table later
3285 UPDATE "issue" SET "latest_snapshot_id" = "snapshot"."id"
3286 FROM (
3287 SELECT DISTINCT ON ("issue_id") "issue_id", "id"
3288 FROM "snapshot" ORDER BY "issue_id", "id" DESC
3289 ) AS "snapshot"
3290 WHERE "snapshot"."issue_id" = "issue"."id";
3292 UPDATE "issue" SET "admission_snapshot_id" = "old_snapshot"."snapshot_id"
3293 FROM "old_snapshot"
3294 WHERE "old_snapshot"."issue_id" = "issue"."id"
3295 AND "old_snapshot"."event" = 'end_of_admission';
3297 UPDATE "issue" SET "half_freeze_snapshot_id" = "old_snapshot"."snapshot_id"
3298 FROM "old_snapshot"
3299 WHERE "old_snapshot"."issue_id" = "issue"."id"
3300 AND "old_snapshot"."event" = 'half_freeze';
3302 UPDATE "issue" SET "full_freeze_snapshot_id" = "old_snapshot"."snapshot_id"
3303 FROM "old_snapshot"
3304 WHERE "old_snapshot"."issue_id" = "issue"."id"
3305 AND "old_snapshot"."event" = 'full_freeze';
3307 ALTER TABLE "issue" ENABLE TRIGGER USER;
3310 DROP TABLE "old_snapshot";
3312 DROP TABLE "direct_supporter_snapshot_old";
3313 DROP TABLE "delegating_interest_snapshot_old";
3314 DROP TABLE "direct_interest_snapshot_old";
3315 DROP TABLE "delegating_population_snapshot";
3316 DROP TABLE "direct_population_snapshot";
3319 DROP VIEW "open_issue";
3322 ALTER TABLE "issue" DROP COLUMN "latest_snapshot_event";
3325 CREATE VIEW "open_issue" AS
3326 SELECT * FROM "issue" WHERE "closed" ISNULL;
3328 COMMENT ON VIEW "open_issue" IS 'All open issues';
3331 -- NOTE: create "issue_for_admission" view after altering table "issue"
3332 CREATE VIEW "issue_for_admission" AS
3333 SELECT DISTINCT ON ("issue"."area_id")
3334 "issue".*,
3335 max("initiative"."supporter_count") AS "max_supporter_count"
3336 FROM "issue"
3337 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
3338 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
3339 JOIN "area" ON "issue"."area_id" = "area"."id"
3340 WHERE "issue"."state" = 'admission'::"issue_state"
3341 AND now() >= "issue"."created" + "issue"."min_admission_time"
3342 AND "initiative"."supporter_count" >= "policy"."issue_quorum"
3343 AND "initiative"."supporter_count" * "policy"."issue_quorum_den" >=
3344 "issue"."population" * "policy"."issue_quorum_num"
3345 AND "initiative"."supporter_count" >= "area"."issue_quorum"
3346 AND "initiative"."revoked" ISNULL
3347 GROUP BY "issue"."id"
3348 ORDER BY "issue"."area_id", "max_supporter_count" DESC, "issue"."id";
3350 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';
3353 DROP TYPE "snapshot_event";
3356 ALTER TABLE "issue" ADD CONSTRAINT "snapshot_required" CHECK (
3357 ("half_frozen" ISNULL OR "half_freeze_snapshot_id" NOTNULL) AND
3358 ("fully_frozen" ISNULL OR "full_freeze_snapshot_id" NOTNULL) );
3361 COMMIT;