liquid_feedback_core
view update/core-update.v3.2.2-v4.0.0.sql @ 555:5d098bcc631a
Member verification through organizational units
author | jbe |
---|---|
date | Sun Sep 17 01:48:45 2017 +0200 (2017-09-17) |
parents | 3e7ad7233404 |
children | 3f21631a7f6d |
line source
1 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'unit_created';
2 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'unit_updated';
3 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'area_created';
4 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'area_updated';
5 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'policy_created';
6 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'policy_updated';
7 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'suggestion_deleted';
8 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_activated';
9 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_deleted';
10 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_active';
11 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_name_updated';
12 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_profile_updated';
13 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'member_image_updated';
14 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'interest';
15 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'initiator';
16 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'support';
17 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'support_updated';
18 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'suggestion_rated';
19 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'delegation';
20 ALTER TYPE "event_type" ADD VALUE IF NOT EXISTS 'contact';
23 BEGIN;
26 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
27 SELECT * FROM (VALUES ('4.0-dev', 4, 0, -1))
28 AS "subquery"("string", "major", "minor", "revision");
31 ALTER TABLE "system_setting" ADD COLUMN "snapshot_retention" INTERVAL;
33 COMMENT ON COLUMN "system_setting"."snapshot_retention" IS 'Unreferenced snapshots are retained for the given period of time after creation; set to NULL for infinite retention.';
36 ALTER TABLE "member" ADD COLUMN "deleted" TIMESTAMPTZ;
37 ALTER TABLE "member" ADD CONSTRAINT "deleted_requires_locked"
38 CHECK ("deleted" ISNULL OR "locked" = TRUE);
40 COMMENT ON COLUMN "member"."deleted" IS 'Timestamp of deletion (set by "delete_member" function)';
43 CREATE TABLE "member_settings" (
44 "member_id" INT4 PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
45 "settings" JSONB NOT NULL CHECK (jsonb_typeof("settings") = 'object') );
47 COMMENT ON TABLE "member_settings" IS 'Stores a JSON document for each member containing optional (additional) settings for the respective member';
50 CREATE TABLE "member_useterms" (
51 "member_id" INT4 PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
52 "accepted" TIMESTAMPTZ NOT NULL,
53 "contract_identifier" TEXT NOT NULL );
55 COMMENT ON TABLE "member_useterms" IS 'Keeps record of accepted terms of use; may contain multiple rows per member';
57 COMMENT ON COLUMN "member_useterms"."accepted" IS 'Point in time when user accepted the terms of use';
58 COMMENT ON COLUMN "member_useterms"."contract_identifier" IS 'String identifier to denote the accepted terms of use, including their version or revision';
61 CREATE TABLE "member_profile" (
62 "member_id" INT4 PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
63 "formatting_engine" TEXT,
64 "statement" TEXT,
65 "profile" JSONB NOT NULL DEFAULT '{}' CHECK (jsonb_typeof("profile") = 'object'),
66 "profile_text_data" TEXT,
67 "text_search_data" TSVECTOR );
68 CREATE INDEX "member_profile_text_search_data_idx" ON "member_profile" USING gin ("text_search_data");
69 CREATE TRIGGER "update_text_search_data"
70 BEFORE INSERT OR UPDATE ON "member_profile"
71 FOR EACH ROW EXECUTE PROCEDURE
72 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
73 'statement', 'profile_text_data');
75 COMMENT ON COLUMN "member_profile"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member_profile"."statement"';
76 COMMENT ON COLUMN "member_profile"."statement" IS 'Freely chosen text of the member for his/her profile';
77 COMMENT ON COLUMN "member_profile"."profile" IS 'Additional profile data as JSON document';
78 COMMENT ON COLUMN "member_profile"."profile_text_data" IS 'Text data from "profile" field for full text search';
81 INSERT INTO "member_profile"
82 ( "member_id", "formatting_engine", "statement", "profile")
83 SELECT
84 "id" AS "member_id",
85 "formatting_engine",
86 "statement",
87 json_build_object(
88 'organizational_unit', "organizational_unit",
89 'internal_posts', "internal_posts",
90 'realname', "realname",
91 'birthday', to_char("birthday", 'YYYY-MM-DD'),
92 'address', "address",
93 'email', "email",
94 'xmpp_address', "xmpp_address",
95 'website', "website",
96 'phone', "phone",
97 'mobile_phone', "mobile_phone",
98 'profession', "profession",
99 'external_memberships', "external_memberships",
100 'external_posts', "external_posts"
101 ) AS "profile"
102 FROM "member";
104 UPDATE "member_profile" SET "profile_text_data" =
105 coalesce(("profile"->>'organizational_unit') || ' ', '') ||
106 coalesce(("profile"->>'internal_posts') || ' ', '') ||
107 coalesce(("profile"->>'realname') || ' ', '') ||
108 coalesce(("profile"->>'birthday') || ' ', '') ||
109 coalesce(("profile"->>'address') || ' ', '') ||
110 coalesce(("profile"->>'email') || ' ', '') ||
111 coalesce(("profile"->>'xmpp_address') || ' ', '') ||
112 coalesce(("profile"->>'website') || ' ', '') ||
113 coalesce(("profile"->>'phone') || ' ', '') ||
114 coalesce(("profile"->>'mobile_phone') || ' ', '') ||
115 coalesce(("profile"->>'profession') || ' ', '') ||
116 coalesce(("profile"->>'external_memberships') || ' ', '') ||
117 coalesce(("profile"->>'external_posts') || ' ', '');
120 DROP VIEW "newsletter_to_send";
121 DROP VIEW "scheduled_notification_to_send";
122 DROP VIEW "member_to_notify";
123 DROP VIEW "member_eligible_to_be_notified";
126 ALTER TABLE "member" DROP COLUMN "organizational_unit";
127 ALTER TABLE "member" DROP COLUMN "internal_posts";
128 ALTER TABLE "member" DROP COLUMN "realname";
129 ALTER TABLE "member" DROP COLUMN "birthday";
130 ALTER TABLE "member" DROP COLUMN "address";
131 ALTER TABLE "member" DROP COLUMN "email";
132 ALTER TABLE "member" DROP COLUMN "xmpp_address";
133 ALTER TABLE "member" DROP COLUMN "website";
134 ALTER TABLE "member" DROP COLUMN "phone";
135 ALTER TABLE "member" DROP COLUMN "mobile_phone";
136 ALTER TABLE "member" DROP COLUMN "profession";
137 ALTER TABLE "member" DROP COLUMN "external_memberships";
138 ALTER TABLE "member" DROP COLUMN "external_posts";
139 ALTER TABLE "member" DROP COLUMN "formatting_engine";
140 ALTER TABLE "member" DROP COLUMN "statement";
142 ALTER TABLE "member" ADD COLUMN "location" JSONB;
143 COMMENT ON COLUMN "member"."location" IS 'Geographic location on earth as GeoJSON object';
144 CREATE INDEX "member_location_idx" ON "member" USING gist ((GeoJSON_to_ecluster("location")));
146 DROP TRIGGER "update_text_search_data" ON "member";
147 CREATE TRIGGER "update_text_search_data"
148 BEFORE INSERT OR UPDATE ON "member"
149 FOR EACH ROW EXECUTE PROCEDURE
150 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
151 "name", "identification");
154 CREATE TABLE "cross_unit_verification" (
155 PRIMARY KEY ("unit_id", "trusted_unit_id"),
156 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
157 "trusted_unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
159 COMMENT ON TABLE "cross_unit_verification" IS 'Enables member verifications in a unit to count for other units as well (non-transitively)';
161 COMMENT ON COLUMN "cross_unit_verification"."unit_id" IS 'Unit for which verification in "trusted_unit_id" is also taken into account';
162 COMMENT ON COLUMN "cross_unit_verification"."trusted_unit_id" IS 'Unit where verification exists';
165 CREATE TABLE "verification" (
166 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
167 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
168 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
169 --"expiry" TIMESTAMPTZ, -- TODO
170 "comment" TEXT );
171 CREATE INDEX "verification_unit_id_idx" ON "verification" ("unit_id");
172 CREATE INDEX "verification_member_id_idx" ON "verification" ("member_id");
174 COMMENT ON TABLE "verification" IS 'Member verification status';
177 CREATE VIEW "verified_privilege" AS
178 SELECT DISTINCT "privilege".* FROM "privilege"
179 LEFT JOIN "cross_unit_verification" AS "cross"
180 ON "privilege"."unit_id" = "cross"."unit_id"
181 LEFT JOIN "verification"
182 ON (
183 "privilege"."unit_id" = "verification"."unit_id" OR
184 "cross"."trusted_unit_id" = "verification"."unit_id"
185 )
186 AND "privilege"."member_id" = "verification"."member_id";
188 COMMENT ON VIEW "verified_privilege" IS 'View on privilege table containing only entries where verification of member in unit has been done';
191 CREATE VIEW "member_eligible_to_be_notified" AS
192 SELECT * FROM "member"
193 WHERE "activated" NOTNULL AND "locked" = FALSE;
195 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")';
198 CREATE VIEW "member_to_notify" AS
199 SELECT * FROM "member_eligible_to_be_notified"
200 WHERE "disable_notifications" = FALSE;
202 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)';
205 CREATE VIEW "scheduled_notification_to_send" AS
206 SELECT * FROM (
207 SELECT
208 "id" AS "recipient_id",
209 now() - CASE WHEN "notification_dow" ISNULL THEN
210 ( "notification_sent"::DATE + CASE
211 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
212 THEN 0 ELSE 1 END
213 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
214 ELSE
215 ( "notification_sent"::DATE +
216 ( 7 + "notification_dow" -
217 EXTRACT(DOW FROM
218 ( "notification_sent"::DATE + CASE
219 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
220 THEN 0 ELSE 1 END
221 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
222 )::INTEGER
223 ) % 7 +
224 CASE
225 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
226 THEN 0 ELSE 1
227 END
228 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
229 END AS "pending"
230 FROM (
231 SELECT
232 "id",
233 COALESCE("notification_sent", "activated") AS "notification_sent",
234 "notification_dow",
235 "notification_hour"
236 FROM "member_to_notify"
237 WHERE "notification_hour" NOTNULL
238 ) AS "subquery1"
239 ) AS "subquery2"
240 WHERE "pending" > '0'::INTERVAL;
242 COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending';
244 COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail';
245 COMMENT ON COLUMN "scheduled_notification_to_send"."pending" IS 'Duration for which the notification mail has already been pending';
248 CREATE VIEW "newsletter_to_send" AS
249 SELECT
250 "member"."id" AS "recipient_id",
251 "newsletter"."id" AS "newsletter_id",
252 "newsletter"."published"
253 FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"
254 LEFT JOIN "verified_privilege" ON
255 "verified_privilege"."member_id" = "member"."id" AND
256 "verified_privilege"."unit_id" = "newsletter"."unit_id" AND
257 "verified_privilege"."voting_right" = TRUE
258 LEFT JOIN "subscription" ON
259 "subscription"."member_id" = "member"."id" AND
260 "subscription"."unit_id" = "newsletter"."unit_id"
261 WHERE "newsletter"."published" <= now()
262 AND "newsletter"."sent" ISNULL
263 AND (
264 "member"."disable_notifications" = FALSE OR
265 "newsletter"."include_all_members" = TRUE )
266 AND (
267 "newsletter"."unit_id" ISNULL OR
268 "verified_privilege"."member_id" NOTNULL OR
269 "subscription"."member_id" NOTNULL );
271 COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';
273 COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter was supposed to be sent out (can be used for ordering)';
276 DROP VIEW "expired_session";
277 DROP TABLE "session";
280 CREATE TABLE "session" (
281 UNIQUE ("member_id", "id"), -- index needed for foreign-key on table "token"
282 "id" SERIAL8 PRIMARY KEY,
283 "ident" TEXT NOT NULL UNIQUE,
284 "additional_secret" TEXT,
285 "logout_token" TEXT,
286 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
287 "member_id" INT4 REFERENCES "member" ("id") ON DELETE SET NULL,
288 "authority" TEXT,
289 "authority_uid" TEXT,
290 "authority_login" TEXT,
291 "needs_delegation_check" BOOLEAN NOT NULL DEFAULT FALSE,
292 "lang" TEXT );
293 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
295 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
297 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
298 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
299 COMMENT ON COLUMN "session"."logout_token" IS 'Optional token to authorize logout through external component';
300 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
301 COMMENT ON COLUMN "session"."authority" IS 'Temporary store for "member"."authority" during member account creation';
302 COMMENT ON COLUMN "session"."authority_uid" IS 'Temporary store for "member"."authority_uid" during member account creation';
303 COMMENT ON COLUMN "session"."authority_login" IS 'Temporary store for "member"."authority_login" during member account creation';
304 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';
305 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
308 CREATE TYPE "authflow" AS ENUM ('code', 'token');
310 COMMENT ON TYPE "authflow" IS 'OAuth 2.0 flows: ''code'' = Authorization Code flow, ''token'' = Implicit flow';
313 CREATE TABLE "system_application" (
314 "id" SERIAL4 PRIMARY KEY,
315 "name" TEXT NOT NULL,
316 "discovery_baseurl" TEXT,
317 "client_id" TEXT NOT NULL UNIQUE,
318 "default_redirect_uri" TEXT NOT NULL,
319 "cert_common_name" TEXT,
320 "client_cred_scope" TEXT,
321 "flow" "authflow",
322 "automatic_scope" TEXT,
323 "permitted_scope" TEXT,
324 "forbidden_scope" TEXT );
326 COMMENT ON TABLE "system_application" IS 'OAuth 2.0 clients that are registered by the system administrator';
328 COMMENT ON COLUMN "system_application"."name" IS 'Human readable name of application';
329 COMMENT ON COLUMN "system_application"."discovery_baseurl" IS 'Base URL for application discovery; NULL for hidden application';
330 COMMENT ON COLUMN "system_application"."client_id" IS 'OAuth 2.0 "client_id"';
331 COMMENT ON COLUMN "system_application"."cert_common_name" IS 'Value for CN field of TLS client certificate';
332 COMMENT ON COLUMN "system_application"."client_cred_scope" IS 'Space-separated list of scopes; If set, Client Credentials Grant is allowed; value determines scope';
333 COMMENT ON COLUMN "system_application"."flow" IS 'If set to ''code'' or ''token'', then Authorization Code or Implicit flow is allowed respectively';
334 COMMENT ON COLUMN "system_application"."automatic_scope" IS 'Space-separated list of scopes; Automatically granted scope for Authorization Code or Implicit flow';
335 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';
336 COMMENT ON COLUMN "system_application"."forbidden_scope" IS 'Space-separated list of scopes that may not be granted to the application by a member';
339 CREATE TABLE "system_application_redirect_uri" (
340 PRIMARY KEY ("system_application_id", "redirect_uri"),
341 "system_application_id" INT4 REFERENCES "system_application" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
342 "redirect_uri" TEXT );
344 COMMENT ON TABLE "system_application_redirect_uri" IS 'Additional OAuth 2.0 redirection endpoints, which may be selected through the "redirect_uri" GET parameter';
347 CREATE TABLE "dynamic_application_scope" (
348 PRIMARY KEY ("redirect_uri", "flow", "scope"),
349 "redirect_uri" TEXT,
350 "flow" TEXT,
351 "scope" TEXT,
352 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours' );
353 CREATE INDEX "dynamic_application_scope_redirect_uri_scope_idx" ON "dynamic_application_scope" ("redirect_uri", "flow", "scope");
354 CREATE INDEX "dynamic_application_scope_expiry_idx" ON "dynamic_application_scope" ("expiry");
356 COMMENT ON TABLE "dynamic_application_scope" IS 'Dynamic OAuth 2.0 client registration data';
358 COMMENT ON COLUMN "dynamic_application_scope"."redirect_uri" IS 'Redirection endpoint for which the registration has been done';
359 COMMENT ON COLUMN "dynamic_application_scope"."flow" IS 'OAuth 2.0 flow for which the registration has been done (see also "system_application"."flow")';
360 COMMENT ON COLUMN "dynamic_application_scope"."scope" IS 'Single scope without space characters (use multiple rows for more scopes)';
361 COMMENT ON COLUMN "dynamic_application_scope"."expiry" IS 'Expiry unless renewed';
364 CREATE TABLE "member_application" (
365 "id" SERIAL4 PRIMARY KEY,
366 UNIQUE ("system_application_id", "member_id"),
367 UNIQUE ("domain", "member_id"),
368 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
369 "system_application_id" INT4 REFERENCES "system_application" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
370 "domain" TEXT,
371 "session_id" INT8,
372 FOREIGN KEY ("member_id", "session_id") REFERENCES "session" ("member_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
373 "scope" TEXT NOT NULL,
374 CONSTRAINT "system_application_or_domain_but_not_both" CHECK (
375 ("system_application_id" NOTNULL AND "domain" ISNULL) OR
376 ("system_application_id" ISNULL AND "domain" NOTNULL) ) );
377 CREATE INDEX "member_application_member_id_idx" ON "member_application" ("member_id");
379 COMMENT ON TABLE "member_application" IS 'Application authorized by a member';
381 COMMENT ON COLUMN "member_application"."system_application_id" IS 'If set, then application is a system application';
382 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';
383 COMMENT ON COLUMN "member_application"."session_id" IS 'If set, registration ends with session';
384 COMMENT ON COLUMN "member_application"."scope" IS 'Granted scope as space-separated list of strings';
387 CREATE TYPE "token_type" AS ENUM ('authorization', 'refresh', 'access');
389 COMMENT ON TYPE "token_type" IS 'Types for entries in "token" table';
392 CREATE TABLE "token" (
393 "id" SERIAL8 PRIMARY KEY,
394 "token" TEXT NOT NULL UNIQUE,
395 "token_type" "token_type" NOT NULL,
396 "authorization_token_id" INT8 REFERENCES "token" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
397 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
398 "system_application_id" INT4 REFERENCES "system_application" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
399 "domain" TEXT,
400 FOREIGN KEY ("member_id", "domain") REFERENCES "member_application" ("member_id", "domain") ON DELETE CASCADE ON UPDATE CASCADE,
401 "session_id" INT8,
402 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"
403 "redirect_uri" TEXT,
404 "redirect_uri_explicit" BOOLEAN,
405 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
406 "expiry" TIMESTAMPTZ DEFAULT now() + '1 hour',
407 "used" BOOLEAN NOT NULL DEFAULT FALSE,
408 "scope" TEXT NOT NULL,
409 CONSTRAINT "access_token_needs_expiry"
410 CHECK ("token_type" != 'access'::"token_type" OR "expiry" NOTNULL),
411 CONSTRAINT "authorization_token_needs_redirect_uri"
412 CHECK ("token_type" != 'authorization'::"token_type" OR ("redirect_uri" NOTNULL AND "redirect_uri_explicit" NOTNULL) ) );
413 CREATE INDEX "token_member_id_idx" ON "token" ("member_id");
414 CREATE INDEX "token_authorization_token_id_idx" ON "token" ("authorization_token_id");
415 CREATE INDEX "token_expiry_idx" ON "token" ("expiry");
417 COMMENT ON TABLE "token" IS 'Issued OAuth 2.0 authorization codes and access/refresh tokens';
419 COMMENT ON COLUMN "token"."token" IS 'String secret (the actual token)';
420 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)';
421 COMMENT ON COLUMN "token"."system_application_id" IS 'If set, then application is a system application';
422 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';
423 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''';
424 COMMENT ON COLUMN "token"."redirect_uri" IS 'Authorization codes must be bound to a specific redirect URI';
425 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)';
426 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';
427 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)';
428 COMMENT ON COLUMN "token"."scope" IS 'Scope as space-separated list of strings (detached scopes are marked with ''_detached'' suffix)';
431 CREATE TABLE "token_scope" (
432 PRIMARY KEY ("token_id", "index"),
433 "token_id" INT8 REFERENCES "token" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
434 "index" INT4,
435 "scope" TEXT NOT NULL );
437 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';
440 ALTER TABLE "policy" ADD COLUMN "issue_quorum" INT4 CHECK ("issue_quorum" >= 1);
441 ALTER TABLE "policy" ADD COLUMN "initiative_quorum" INT4 CHECK ("initiative_quorum" >= 1);
443 UPDATE "policy" SET "issue_quorum" = 1 WHERE "issue_quorum_num" NOTNULL;
444 UPDATE "policy" SET "initiative_quorum" = 1;
446 ALTER TABLE "policy" ALTER COLUMN "initiative_quorum" SET NOT NULL;
448 ALTER TABLE "policy" DROP CONSTRAINT "timing";
449 ALTER TABLE "policy" DROP CONSTRAINT "issue_quorum_if_and_only_if_not_polling";
450 ALTER TABLE "policy" ADD CONSTRAINT
451 "issue_quorum_if_and_only_if_not_polling" CHECK (
452 "polling" = ("issue_quorum" ISNULL) AND
453 "polling" = ("issue_quorum_num" ISNULL) AND
454 "polling" = ("issue_quorum_den" ISNULL)
455 );
456 ALTER TABLE "policy" ADD CONSTRAINT
457 "min_admission_time_smaller_than_max_admission_time" CHECK (
458 "min_admission_time" < "max_admission_time"
459 );
460 ALTER TABLE "policy" ADD CONSTRAINT
461 "timing_null_or_not_null_constraints" CHECK (
462 ( "polling" = FALSE AND
463 "min_admission_time" NOTNULL AND "max_admission_time" NOTNULL AND
464 "discussion_time" NOTNULL AND
465 "verification_time" NOTNULL AND
466 "voting_time" NOTNULL ) OR
467 ( "polling" = TRUE AND
468 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
469 "discussion_time" NOTNULL AND
470 "verification_time" NOTNULL AND
471 "voting_time" NOTNULL ) OR
472 ( "polling" = TRUE AND
473 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
474 "discussion_time" ISNULL AND
475 "verification_time" ISNULL AND
476 "voting_time" ISNULL )
477 );
479 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"';
480 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';
481 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)';
482 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)';
483 COMMENT ON COLUMN "policy"."initiative_quorum" IS 'Absolute number of satisfied supporters to be reached by an initiative to be "admitted" for voting';
484 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
485 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
488 ALTER TABLE "unit" ADD COLUMN "location" JSONB;
490 CREATE INDEX "unit_location_idx" ON "unit" USING gist ((GeoJSON_to_ecluster("location")));
492 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in view "verified_privilege" (only active members counted)';
493 COMMENT ON COLUMN "unit"."location" IS 'Geographic location on earth as GeoJSON object indicating valid coordinates for initiatives of issues with this policy';
496 DROP INDEX "area_unit_id_idx";
497 ALTER TABLE "area" ADD UNIQUE ("unit_id", "id");
499 ALTER TABLE "area" ADD COLUMN "quorum_standard" NUMERIC NOT NULL DEFAULT 2 CHECK ("quorum_standard" >= 0);
500 ALTER TABLE "area" ADD COLUMN "quorum_issues" NUMERIC NOT NULL DEFAULT 1 CHECK ("quorum_issues" > 0);
501 ALTER TABLE "area" ADD COLUMN "quorum_time" INTERVAL NOT NULL DEFAULT '1 day' CHECK ("quorum_time" > '0'::INTERVAL);
502 ALTER TABLE "area" ADD COLUMN "quorum_exponent" NUMERIC NOT NULL DEFAULT 0.5 CHECK ("quorum_exponent" BETWEEN 0 AND 1);
503 ALTER TABLE "area" ADD COLUMN "quorum_factor" NUMERIC NOT NULL DEFAULT 2 CHECK ("quorum_factor" >= 1);
504 ALTER TABLE "area" ADD COLUMN "quorum_den" INT4 CHECK ("quorum_den" > 0);
505 ALTER TABLE "area" ADD COLUMN "issue_quorum" INT4;
506 ALTER TABLE "area" ADD COLUMN "location" JSONB;
508 ALTER TABLE "area" DROP COLUMN "direct_member_count";
509 ALTER TABLE "area" DROP COLUMN "member_weight";
511 CREATE INDEX "area_location_idx" ON "area" USING gist ((GeoJSON_to_ecluster("location")));
513 COMMENT ON COLUMN "area"."quorum_standard" IS 'Parameter for dynamic issue quorum: default quorum';
514 COMMENT ON COLUMN "area"."quorum_issues" IS 'Parameter for dynamic issue quorum: number of open issues for default quorum';
515 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)';
516 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';
517 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';
518 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)';
519 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"';
520 COMMENT ON COLUMN "area"."external_reference" IS 'Opaque data field to store an external reference';
521 COMMENT ON COLUMN "area"."location" IS 'Geographic location on earth as GeoJSON object indicating valid coordinates for initiatives of issues with this policy';
524 CREATE TABLE "snapshot" (
525 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "issue"
526 "id" SERIAL8 PRIMARY KEY,
527 "calculated" TIMESTAMPTZ NOT NULL DEFAULT now(),
528 "population" INT4,
529 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
530 "issue_id" INT4 ); -- NOTE: following (cyclic) reference is added later through ALTER command: REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE
532 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';
535 CREATE TABLE "snapshot_population" (
536 PRIMARY KEY ("snapshot_id", "member_id"),
537 "snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
538 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE );
540 COMMENT ON TABLE "snapshot_population" IS 'Members with voting right relevant for a snapshot';
543 ALTER TABLE "issue" ADD UNIQUE ("area_id", "id");
544 DROP INDEX "issue_area_id_idx";
545 ALTER TABLE "issue" ADD UNIQUE ("policy_id", "id");
546 DROP INDEX "issue_policy_id_idx";
548 ALTER TABLE "issue" RENAME COLUMN "snapshot" TO "calculated";
550 ALTER TABLE "issue" ADD COLUMN "latest_snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
551 ALTER TABLE "issue" ADD COLUMN "admission_snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE SET NULL ON UPDATE CASCADE;
552 ALTER TABLE "issue" ADD COLUMN "half_freeze_snapshot_id" INT8;
553 ALTER TABLE "issue" ADD COLUMN "full_freeze_snapshot_id" INT8;
555 ALTER TABLE "issue" ADD FOREIGN KEY ("id", "half_freeze_snapshot_id")
556 REFERENCES "snapshot" ("issue_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE;
557 ALTER TABLE "issue" ADD FOREIGN KEY ("id", "full_freeze_snapshot_id")
558 REFERENCES "snapshot" ("issue_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE;
560 ALTER TABLE "issue" DROP CONSTRAINT "last_snapshot_on_full_freeze";
561 ALTER TABLE "issue" DROP CONSTRAINT "freeze_requires_snapshot";
562 ALTER TABLE "issue" DROP CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event";
564 CREATE INDEX "issue_state_idx" ON "issue" ("state");
565 CREATE INDEX "issue_latest_snapshot_id" ON "issue" ("latest_snapshot_id");
566 CREATE INDEX "issue_admission_snapshot_id" ON "issue" ("admission_snapshot_id");
567 CREATE INDEX "issue_half_freeze_snapshot_id" ON "issue" ("half_freeze_snapshot_id");
568 CREATE INDEX "issue_full_freeze_snapshot_id" ON "issue" ("full_freeze_snapshot_id");
570 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")';
571 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")';
572 COMMENT ON COLUMN "issue"."latest_snapshot_id" IS 'Snapshot id of most recent snapshot';
573 COMMENT ON COLUMN "issue"."admission_snapshot_id" IS 'Snapshot id when issue as accepted or canceled in admission phase';
574 COMMENT ON COLUMN "issue"."half_freeze_snapshot_id" IS 'Snapshot id at end of discussion phase';
575 COMMENT ON COLUMN "issue"."full_freeze_snapshot_id" IS 'Snapshot id at end of verification phase';
576 COMMENT ON COLUMN "issue"."population" IS 'Count of members in "snapshot_population" table with "snapshot_id" equal to "issue"."latest_snapshot_id"';
579 ALTER TABLE "snapshot" ADD FOREIGN KEY ("issue_id") REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
582 ALTER TABLE "initiative" DROP CONSTRAINT "initiative_suggested_initiative_id_fkey";
583 ALTER TABLE "initiative" ADD FOREIGN KEY ("suggested_initiative_id") REFERENCES "initiative" ("id") ON DELETE SET NULL ON UPDATE CASCADE;
585 ALTER TABLE "initiative" ADD COLUMN "location" JSONB;
586 ALTER TABLE "initiative" ADD COLUMN "draft_text_search_data" TSVECTOR;
588 CREATE INDEX "initiative_location_idx" ON "initiative" USING gist ((GeoJSON_to_ecluster("location")));
589 CREATE INDEX "initiative_draft_text_search_data_idx" ON "initiative" USING gin ("draft_text_search_data");
591 COMMENT ON COLUMN "initiative"."location" IS 'Geographic location of initiative as GeoJSON object (automatically copied from most recent draft)';
594 ALTER TABLE "draft" ADD COLUMN "location" JSONB;
596 CREATE INDEX "draft_location_idx" ON "draft" USING gist ((GeoJSON_to_ecluster("location")));
598 COMMENT ON COLUMN "draft"."location" IS 'Geographic location of initiative as GeoJSON object (automatically copied to "initiative" table if draft is most recent)';
601 ALTER TABLE "suggestion" ADD COLUMN "location" JSONB;
603 CREATE INDEX "suggestion_location_idx" ON "suggestion" USING gist ((GeoJSON_to_ecluster("location")));
605 COMMENT ON COLUMN "suggestion"."location" IS 'Geographic location of suggestion as GeoJSON object';
608 CREATE TABLE "temporary_suggestion_counts" (
609 "id" INT8 PRIMARY KEY, -- NOTE: no referential integrity due to performance/locking issues; REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
610 "minus2_unfulfilled_count" INT4 NOT NULL,
611 "minus2_fulfilled_count" INT4 NOT NULL,
612 "minus1_unfulfilled_count" INT4 NOT NULL,
613 "minus1_fulfilled_count" INT4 NOT NULL,
614 "plus1_unfulfilled_count" INT4 NOT NULL,
615 "plus1_fulfilled_count" INT4 NOT NULL,
616 "plus2_unfulfilled_count" INT4 NOT NULL,
617 "plus2_fulfilled_count" INT4 NOT NULL );
619 COMMENT ON TABLE "temporary_suggestion_counts" IS 'Holds certain calculated values (suggestion counts) temporarily until they can be copied into table "suggestion"';
621 COMMENT ON COLUMN "temporary_suggestion_counts"."id" IS 'References "suggestion" ("id") but has no referential integrity trigger associated, due to performance/locking issues';
624 ALTER TABLE "interest" DROP CONSTRAINT "interest_member_id_fkey";
625 ALTER TABLE "interest" ADD FOREIGN KEY ("member_id") REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
628 ALTER TABLE "initiator" DROP CONSTRAINT "initiator_member_id_fkey";
629 ALTER TABLE "initiator" ADD FOREIGN KEY ("member_id") REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
632 ALTER TABLE "delegation" DROP CONSTRAINT "delegation_trustee_id_fkey";
633 ALTER TABLE "delegation" ADD FOREIGN KEY ("trustee_id") REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
636 CREATE TABLE "snapshot_issue" (
637 PRIMARY KEY ("snapshot_id", "issue_id"),
638 "snapshot_id" INT8 REFERENCES "snapshot" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
639 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
640 CREATE INDEX "snapshot_issue_issue_id_idx" ON "snapshot_issue" ("issue_id");
642 COMMENT ON TABLE "snapshot_issue" IS 'List of issues included in a snapshot';
644 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.';
647 ALTER TABLE "direct_interest_snapshot" RENAME TO "direct_interest_snapshot_old"; -- TODO!
648 ALTER INDEX "direct_interest_snapshot_pkey" RENAME TO "direct_interest_snapshot_old_pkey";
649 ALTER INDEX "direct_interest_snapshot_member_id_idx" RENAME TO "direct_interest_snapshot_old_member_id_idx";
651 ALTER TABLE "delegating_interest_snapshot" RENAME TO "delegating_interest_snapshot_old"; -- TODO!
652 ALTER INDEX "delegating_interest_snapshot_pkey" RENAME TO "delegating_interest_snapshot_old_pkey";
653 ALTER INDEX "delegating_interest_snapshot_member_id_idx" RENAME TO "delegating_interest_snapshot_old_member_id_idx";
655 ALTER TABLE "direct_supporter_snapshot" RENAME TO "direct_supporter_snapshot_old"; -- TODO!
656 ALTER INDEX "direct_supporter_snapshot_pkey" RENAME TO "direct_supporter_snapshot_old_pkey";
657 ALTER INDEX "direct_supporter_snapshot_member_id_idx" RENAME TO "direct_supporter_snapshot_old_member_id_idx";
660 CREATE TABLE "direct_interest_snapshot" (
661 PRIMARY KEY ("snapshot_id", "issue_id", "member_id"),
662 "snapshot_id" INT8,
663 "issue_id" INT4,
664 FOREIGN KEY ("snapshot_id", "issue_id")
665 REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
666 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
667 "weight" INT4 );
668 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
670 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';
672 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
675 CREATE TABLE "delegating_interest_snapshot" (
676 PRIMARY KEY ("snapshot_id", "issue_id", "member_id"),
677 "snapshot_id" INT8,
678 "issue_id" INT4,
679 FOREIGN KEY ("snapshot_id", "issue_id")
680 REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
681 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
682 "weight" INT4,
683 "scope" "delegation_scope" NOT NULL,
684 "delegate_member_ids" INT4[] NOT NULL );
685 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
687 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';
689 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
690 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
691 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"';
694 CREATE TABLE "direct_supporter_snapshot" (
695 PRIMARY KEY ("snapshot_id", "initiative_id", "member_id"),
696 "snapshot_id" INT8,
697 "issue_id" INT4 NOT NULL,
698 FOREIGN KEY ("snapshot_id", "issue_id")
699 REFERENCES "snapshot_issue" ("snapshot_id", "issue_id") ON DELETE CASCADE ON UPDATE CASCADE,
700 "initiative_id" INT4,
701 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
702 "draft_id" INT8 NOT NULL,
703 "informed" BOOLEAN NOT NULL,
704 "satisfied" BOOLEAN NOT NULL,
705 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
706 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
707 FOREIGN KEY ("snapshot_id", "issue_id", "member_id") REFERENCES "direct_interest_snapshot" ("snapshot_id", "issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
708 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
710 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';
712 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';
713 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
714 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
717 ALTER TABLE "non_voter" DROP CONSTRAINT "non_voter_pkey";
718 DROP INDEX "non_voter_member_id_idx";
720 ALTER TABLE "non_voter" ADD PRIMARY KEY ("member_id", "issue_id");
721 CREATE INDEX "non_voter_issue_id_idx" ON "non_voter" ("issue_id");
724 INSERT INTO "member_useterms" ("member_id", "accepted", "contract_identifier")
725 SELECT
726 "member_id",
727 regexp_replace("value", '^accepted at ', '')::TIMESTAMPTZ AS "accepted",
728 regexp_replace("key", '^use_terms_checkbox_', '') AS "contract_identifier"
729 FROM "setting" WHERE "key" LIKE 'use_terms_checkbox_%';
732 DROP TABLE "setting";
733 DROP TABLE "setting_map";
734 DROP TABLE "member_relation_setting";
735 DROP TABLE "unit_setting";
736 DROP TABLE "area_setting";
737 DROP TABLE "initiative_setting";
738 DROP TABLE "suggestion_setting";
741 ALTER TABLE "event" ADD COLUMN "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE;
742 ALTER TABLE "event" ADD COLUMN "scope" "delegation_scope";
743 ALTER TABLE "event" ADD COLUMN "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
744 ALTER TABLE "event" ADD COLUMN "area_id" INT4;
745 ALTER TABLE "event" ADD COLUMN "policy_id" INT4 REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
746 ALTER TABLE "event" ADD COLUMN "boolean_value" BOOLEAN;
747 ALTER TABLE "event" ADD COLUMN "numeric_value" INT4;
748 ALTER TABLE "event" ADD COLUMN "text_value" TEXT;
749 ALTER TABLE "event" ADD COLUMN "old_text_value" TEXT;
751 ALTER TABLE "event" ADD FOREIGN KEY ("unit_id", "area_id") REFERENCES "area" ("unit_id", "id") ON DELETE CASCADE ON UPDATE CASCADE;
752 ALTER TABLE "event" ADD FOREIGN KEY ("area_id", "issue_id") REFERENCES "issue" ("area_id", "id") ON DELETE CASCADE ON UPDATE CASCADE;
753 ALTER TABLE "event" ADD FOREIGN KEY ("policy_id", "issue_id") REFERENCES "issue" ("policy_id", "id") ON DELETE CASCADE ON UPDATE CASCADE;
755 ALTER TABLE "event" DROP CONSTRAINT "event_initiative_id_fkey1";
756 ALTER TABLE "event" DROP CONSTRAINT "null_constr_for_issue_state_changed";
757 ALTER TABLE "event" DROP CONSTRAINT "null_constr_for_initiative_creation_or_revocation_or_new_draft";
758 ALTER TABLE "event" DROP CONSTRAINT "null_constr_for_suggestion_creation";
760 UPDATE "event" SET
761 "unit_id" = "area"."unit_id",
762 "area_id" = "issue"."area_id",
763 "policy_id" = "issue"."policy_id"
764 FROM "issue", "area"
765 WHERE "issue"."id" = "event"."issue_id" AND "area"."id" = "issue"."area_id";
767 ALTER TABLE "event" ADD CONSTRAINT "constr_for_issue_state_changed" CHECK (
768 "event" != 'issue_state_changed' OR (
769 "member_id" ISNULL AND
770 "other_member_id" ISNULL AND
771 "scope" ISNULL AND
772 "unit_id" NOTNULL AND
773 "area_id" NOTNULL AND
774 "policy_id" NOTNULL AND
775 "issue_id" NOTNULL AND
776 "state" NOTNULL AND
777 "initiative_id" ISNULL AND
778 "draft_id" ISNULL AND
779 "suggestion_id" ISNULL AND
780 "boolean_value" ISNULL AND
781 "numeric_value" ISNULL AND
782 "text_value" ISNULL AND
783 "old_text_value" ISNULL ));
784 ALTER TABLE "event" ADD CONSTRAINT "constr_for_initiative_creation_or_revocation_or_new_draft" CHECK (
785 "event" NOT IN (
786 'initiative_created_in_new_issue',
787 'initiative_created_in_existing_issue',
788 'initiative_revoked',
789 'new_draft_created'
790 ) OR (
791 "member_id" NOTNULL AND
792 "other_member_id" ISNULL AND
793 "scope" ISNULL AND
794 "unit_id" NOTNULL AND
795 "area_id" NOTNULL AND
796 "policy_id" NOTNULL AND
797 "issue_id" NOTNULL AND
798 "state" NOTNULL AND
799 "initiative_id" NOTNULL AND
800 "draft_id" NOTNULL AND
801 "suggestion_id" ISNULL AND
802 "boolean_value" ISNULL AND
803 "numeric_value" ISNULL AND
804 "text_value" ISNULL AND
805 "old_text_value" ISNULL ));
806 ALTER TABLE "event" ADD CONSTRAINT "constr_for_suggestion_creation" CHECK (
807 "event" != 'suggestion_created' OR (
808 "member_id" NOTNULL AND
809 "other_member_id" ISNULL AND
810 "scope" ISNULL AND
811 "unit_id" NOTNULL AND
812 "area_id" NOTNULL AND
813 "policy_id" NOTNULL AND
814 "issue_id" NOTNULL AND
815 "state" NOTNULL AND
816 "initiative_id" NOTNULL AND
817 "draft_id" ISNULL AND
818 "suggestion_id" NOTNULL AND
819 "boolean_value" ISNULL AND
820 "numeric_value" ISNULL AND
821 "text_value" ISNULL AND
822 "old_text_value" ISNULL ));
823 ALTER TABLE "event" ADD CONSTRAINT "constr_for_suggestion_removal" CHECK (
824 "event" != 'suggestion_deleted' OR (
825 "member_id" ISNULL AND
826 "other_member_id" ISNULL AND
827 "scope" ISNULL AND
828 "unit_id" NOTNULL AND
829 "area_id" NOTNULL AND
830 "policy_id" NOTNULL AND
831 "issue_id" NOTNULL AND
832 "state" NOTNULL AND
833 "initiative_id" NOTNULL AND
834 "draft_id" ISNULL AND
835 "suggestion_id" NOTNULL AND
836 "boolean_value" ISNULL AND
837 "numeric_value" ISNULL AND
838 "text_value" ISNULL AND
839 "old_text_value" ISNULL ));
840 ALTER TABLE "event" ADD CONSTRAINT "constr_for_value_less_member_event" CHECK (
841 "event" NOT IN (
842 'member_activated',
843 'member_deleted',
844 'member_profile_updated',
845 'member_image_updated'
846 ) OR (
847 "member_id" NOTNULL AND
848 "other_member_id" ISNULL AND
849 "scope" ISNULL AND
850 "unit_id" ISNULL AND
851 "area_id" ISNULL AND
852 "policy_id" ISNULL AND
853 "issue_id" ISNULL AND
854 "state" ISNULL AND
855 "initiative_id" ISNULL AND
856 "draft_id" ISNULL AND
857 "suggestion_id" ISNULL AND
858 "boolean_value" ISNULL AND
859 "numeric_value" ISNULL AND
860 "text_value" ISNULL AND
861 "old_text_value" ISNULL ));
862 ALTER TABLE "event" ADD CONSTRAINT "constr_for_member_active" CHECK (
863 "event" != 'member_active' OR (
864 "member_id" NOTNULL AND
865 "other_member_id" ISNULL AND
866 "scope" ISNULL AND
867 "unit_id" ISNULL AND
868 "area_id" ISNULL AND
869 "policy_id" ISNULL AND
870 "issue_id" ISNULL AND
871 "state" ISNULL AND
872 "initiative_id" ISNULL AND
873 "draft_id" ISNULL AND
874 "suggestion_id" ISNULL AND
875 "boolean_value" NOTNULL AND
876 "numeric_value" ISNULL AND
877 "text_value" ISNULL AND
878 "old_text_value" ISNULL ));
879 ALTER TABLE "event" ADD CONSTRAINT "constr_for_member_name_updated" CHECK (
880 "event" != 'member_name_updated' OR (
881 "member_id" NOTNULL AND
882 "other_member_id" ISNULL AND
883 "scope" ISNULL AND
884 "unit_id" ISNULL AND
885 "area_id" ISNULL AND
886 "policy_id" ISNULL AND
887 "issue_id" ISNULL AND
888 "state" ISNULL AND
889 "initiative_id" ISNULL AND
890 "draft_id" ISNULL AND
891 "suggestion_id" ISNULL AND
892 "boolean_value" ISNULL AND
893 "numeric_value" ISNULL AND
894 "text_value" NOTNULL AND
895 "old_text_value" NOTNULL ));
896 ALTER TABLE "event" ADD CONSTRAINT "constr_for_interest" CHECK (
897 "event" != 'interest' OR (
898 "member_id" NOTNULL AND
899 "other_member_id" ISNULL AND
900 "scope" ISNULL AND
901 "unit_id" NOTNULL AND
902 "area_id" NOTNULL AND
903 "policy_id" NOTNULL AND
904 "issue_id" NOTNULL AND
905 "state" NOTNULL AND
906 "initiative_id" ISNULL AND
907 "draft_id" ISNULL AND
908 "suggestion_id" ISNULL AND
909 "boolean_value" NOTNULL AND
910 "numeric_value" ISNULL AND
911 "text_value" ISNULL AND
912 "old_text_value" ISNULL ));
913 ALTER TABLE "event" ADD CONSTRAINT "constr_for_initiator" CHECK (
914 "event" != 'initiator' OR (
915 "member_id" NOTNULL AND
916 "other_member_id" ISNULL AND
917 "scope" ISNULL AND
918 "unit_id" NOTNULL AND
919 "area_id" NOTNULL AND
920 "policy_id" NOTNULL AND
921 "issue_id" NOTNULL AND
922 "state" NOTNULL AND
923 "initiative_id" NOTNULL AND
924 "draft_id" ISNULL AND
925 "suggestion_id" ISNULL AND
926 "boolean_value" NOTNULL AND
927 "numeric_value" ISNULL AND
928 "text_value" ISNULL AND
929 "old_text_value" ISNULL ));
930 ALTER TABLE "event" ADD CONSTRAINT "constr_for_support" CHECK (
931 "event" != 'support' OR (
932 "member_id" NOTNULL AND
933 "other_member_id" ISNULL AND
934 "scope" ISNULL AND
935 "unit_id" NOTNULL AND
936 "area_id" NOTNULL AND
937 "policy_id" NOTNULL AND
938 "issue_id" NOTNULL AND
939 "state" NOTNULL AND
940 "initiative_id" NOTNULL AND
941 ("draft_id" NOTNULL) = ("boolean_value" = TRUE) AND
942 "suggestion_id" ISNULL AND
943 "boolean_value" NOTNULL AND
944 "numeric_value" ISNULL AND
945 "text_value" ISNULL AND
946 "old_text_value" ISNULL ));
947 ALTER TABLE "event" ADD CONSTRAINT "constr_for_support_updated" CHECK (
948 "event" != 'support_updated' OR (
949 "member_id" NOTNULL AND
950 "other_member_id" ISNULL AND
951 "scope" ISNULL AND
952 "unit_id" NOTNULL AND
953 "area_id" NOTNULL AND
954 "policy_id" NOTNULL AND
955 "issue_id" NOTNULL AND
956 "state" NOTNULL AND
957 "initiative_id" NOTNULL AND
958 "draft_id" NOTNULL AND
959 "suggestion_id" ISNULL AND
960 "boolean_value" ISNULL AND
961 "numeric_value" ISNULL AND
962 "text_value" ISNULL AND
963 "old_text_value" ISNULL ));
964 ALTER TABLE "event" ADD CONSTRAINT "constr_for_suggestion_rated" CHECK (
965 "event" != 'suggestion_rated' OR (
966 "member_id" NOTNULL AND
967 "other_member_id" ISNULL AND
968 "scope" ISNULL AND
969 "unit_id" NOTNULL AND
970 "area_id" NOTNULL AND
971 "policy_id" NOTNULL AND
972 "issue_id" NOTNULL AND
973 "state" NOTNULL AND
974 "initiative_id" NOTNULL AND
975 "draft_id" ISNULL AND
976 "suggestion_id" NOTNULL AND
977 ("boolean_value" NOTNULL) = ("numeric_value" != 0) AND
978 "numeric_value" NOTNULL AND
979 "numeric_value" IN (-2, -1, 0, 1, 2) AND
980 "text_value" ISNULL AND
981 "old_text_value" ISNULL ));
982 ALTER TABLE "event" ADD CONSTRAINT "constr_for_delegation" CHECK (
983 "event" != 'delegation' OR (
984 "member_id" NOTNULL AND
985 ("other_member_id" NOTNULL) OR ("boolean_value" = FALSE) AND
986 "scope" NOTNULL AND
987 "unit_id" NOTNULL AND
988 ("area_id" NOTNULL) = ("scope" != 'unit'::"delegation_scope") AND
989 "policy_id" ISNULL AND
990 ("issue_id" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
991 ("state" NOTNULL) = ("scope" = 'issue'::"delegation_scope") AND
992 "initiative_id" ISNULL AND
993 "draft_id" ISNULL AND
994 "suggestion_id" ISNULL AND
995 "boolean_value" NOTNULL AND
996 "numeric_value" ISNULL AND
997 "text_value" ISNULL AND
998 "old_text_value" ISNULL ));
999 ALTER TABLE "event" ADD CONSTRAINT "constr_for_contact" CHECK (
1000 "event" != 'contact' OR (
1001 "member_id" NOTNULL AND
1002 "other_member_id" NOTNULL AND
1003 "scope" ISNULL AND
1004 "unit_id" ISNULL AND
1005 "area_id" ISNULL AND
1006 "policy_id" ISNULL AND
1007 "issue_id" ISNULL AND
1008 "state" ISNULL AND
1009 "initiative_id" ISNULL AND
1010 "draft_id" ISNULL AND
1011 "suggestion_id" ISNULL AND
1012 "boolean_value" NOTNULL AND
1013 "numeric_value" ISNULL AND
1014 "text_value" ISNULL AND
1015 "old_text_value" ISNULL ));
1018 ALTER TABLE "notification_event_sent" RENAME TO "event_processed";
1019 ALTER INDEX "notification_event_sent_singleton_idx" RENAME TO "event_processed_singleton_idx";
1021 COMMENT ON TABLE "event_processed" IS 'This table stores one row with the last event_id, for which event handlers have been executed (e.g. notifications having been sent out)';
1022 COMMENT ON INDEX "event_processed_singleton_idx" IS 'This index ensures that "event_processed" only contains one row maximum.';
1025 CREATE FUNCTION "write_event_unit_trigger"()
1026 RETURNS TRIGGER
1027 LANGUAGE 'plpgsql' VOLATILE AS $$
1028 DECLARE
1029 "event_v" "event_type";
1030 BEGIN
1031 IF TG_OP = 'UPDATE' THEN
1032 IF OLD."active" = FALSE AND NEW."active" = FALSE THEN
1033 RETURN NULL;
1034 --ELSIF OLD."active" = FALSE AND NEW."active" = TRUE THEN
1035 -- "event_v" := 'unit_created';
1036 --ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN
1037 -- "event_v" := 'unit_deleted';
1038 ELSIF OLD != NEW THEN
1039 "event_v" := 'unit_updated';
1040 ELSE
1041 RETURN NULL;
1042 END IF;
1043 ELSE
1044 "event_v" := 'unit_created';
1045 END IF;
1046 INSERT INTO "event" ("event", "unit_id") VALUES ("event_v", NEW."id");
1047 RETURN NULL;
1048 END;
1049 $$;
1051 CREATE TRIGGER "write_event_unit" AFTER INSERT OR UPDATE ON "unit"
1052 FOR EACH ROW EXECUTE PROCEDURE "write_event_unit_trigger"();
1054 COMMENT ON FUNCTION "write_event_unit_trigger"() IS 'Implementation of trigger "write_event_unit" on table "unit"';
1055 COMMENT ON TRIGGER "write_event_unit" ON "unit" IS 'Create entry in "event" table on new or changed/disabled units';
1058 CREATE FUNCTION "write_event_area_trigger"()
1059 RETURNS TRIGGER
1060 LANGUAGE 'plpgsql' VOLATILE AS $$
1061 DECLARE
1062 "event_v" "event_type";
1063 BEGIN
1064 IF TG_OP = 'UPDATE' THEN
1065 IF OLD."active" = FALSE AND NEW."active" = FALSE THEN
1066 RETURN NULL;
1067 --ELSIF OLD."active" = FALSE AND NEW."active" = TRUE THEN
1068 -- "event_v" := 'area_created';
1069 --ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN
1070 -- "event_v" := 'area_deleted';
1071 ELSIF OLD != NEW THEN
1072 "event_v" := 'area_updated';
1073 ELSE
1074 RETURN NULL;
1075 END IF;
1076 ELSE
1077 "event_v" := 'area_created';
1078 END IF;
1079 INSERT INTO "event" ("event", "area_id") VALUES ("event_v", NEW."id");
1080 RETURN NULL;
1081 END;
1082 $$;
1084 CREATE TRIGGER "write_event_area" AFTER INSERT OR UPDATE ON "area"
1085 FOR EACH ROW EXECUTE PROCEDURE "write_event_area_trigger"();
1087 COMMENT ON FUNCTION "write_event_area_trigger"() IS 'Implementation of trigger "write_event_area" on table "area"';
1088 COMMENT ON TRIGGER "write_event_area" ON "area" IS 'Create entry in "event" table on new or changed/disabled areas';
1091 CREATE FUNCTION "write_event_policy_trigger"()
1092 RETURNS TRIGGER
1093 LANGUAGE 'plpgsql' VOLATILE AS $$
1094 DECLARE
1095 "event_v" "event_type";
1096 BEGIN
1097 IF TG_OP = 'UPDATE' THEN
1098 IF OLD."active" = FALSE AND NEW."active" = FALSE THEN
1099 RETURN NULL;
1100 --ELSIF OLD."active" = FALSE AND NEW."active" = TRUE THEN
1101 -- "event_v" := 'policy_created';
1102 --ELSIF OLD."active" = TRUE AND NEW."active" = FALSE THEN
1103 -- "event_v" := 'policy_deleted';
1104 ELSIF OLD != NEW THEN
1105 "event_v" := 'policy_updated';
1106 ELSE
1107 RETURN NULL;
1108 END IF;
1109 ELSE
1110 "event_v" := 'policy_created';
1111 END IF;
1112 INSERT INTO "event" ("event", "policy_id") VALUES ("event_v", NEW."id");
1113 RETURN NULL;
1114 END;
1115 $$;
1117 CREATE TRIGGER "write_event_policy" AFTER INSERT OR UPDATE ON "policy"
1118 FOR EACH ROW EXECUTE PROCEDURE "write_event_policy_trigger"();
1120 COMMENT ON FUNCTION "write_event_policy_trigger"() IS 'Implementation of trigger "write_event_policy" on table "policy"';
1121 COMMENT ON TRIGGER "write_event_policy" ON "policy" IS 'Create entry in "event" table on new or changed/disabled policies';
1124 CREATE OR REPLACE FUNCTION "write_event_issue_state_changed_trigger"()
1125 RETURNS TRIGGER
1126 LANGUAGE 'plpgsql' VOLATILE AS $$
1127 DECLARE
1128 "area_row" "area"%ROWTYPE;
1129 BEGIN
1130 IF NEW."state" != OLD."state" THEN
1131 SELECT * INTO "area_row" FROM "area" WHERE "id" = NEW."area_id"
1132 FOR SHARE;
1133 INSERT INTO "event" (
1134 "event",
1135 "unit_id", "area_id", "policy_id", "issue_id", "state"
1136 ) VALUES (
1137 'issue_state_changed',
1138 "area_row"."unit_id", NEW."area_id", NEW."policy_id",
1139 NEW."id", NEW."state"
1140 );
1141 END IF;
1142 RETURN NULL;
1143 END;
1144 $$;
1147 CREATE OR REPLACE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1148 RETURNS TRIGGER
1149 LANGUAGE 'plpgsql' VOLATILE AS $$
1150 DECLARE
1151 "initiative_row" "initiative"%ROWTYPE;
1152 "issue_row" "issue"%ROWTYPE;
1153 "area_row" "area"%ROWTYPE;
1154 "event_v" "event_type";
1155 BEGIN
1156 SELECT * INTO "initiative_row" FROM "initiative"
1157 WHERE "id" = NEW."initiative_id" FOR SHARE;
1158 SELECT * INTO "issue_row" FROM "issue"
1159 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1160 SELECT * INTO "area_row" FROM "area"
1161 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1162 IF EXISTS (
1163 SELECT NULL FROM "draft"
1164 WHERE "initiative_id" = NEW."initiative_id" AND "id" != NEW."id"
1165 FOR SHARE
1166 ) THEN
1167 "event_v" := 'new_draft_created';
1168 ELSE
1169 IF EXISTS (
1170 SELECT NULL FROM "initiative"
1171 WHERE "issue_id" = "initiative_row"."issue_id"
1172 AND "id" != "initiative_row"."id"
1173 FOR SHARE
1174 ) THEN
1175 "event_v" := 'initiative_created_in_existing_issue';
1176 ELSE
1177 "event_v" := 'initiative_created_in_new_issue';
1178 END IF;
1179 END IF;
1180 INSERT INTO "event" (
1181 "event", "member_id",
1182 "unit_id", "area_id", "policy_id", "issue_id", "state",
1183 "initiative_id", "draft_id"
1184 ) VALUES (
1185 "event_v", NEW."author_id",
1186 "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",
1187 "initiative_row"."issue_id", "issue_row"."state",
1188 NEW."initiative_id", NEW."id"
1189 );
1190 RETURN NULL;
1191 END;
1192 $$;
1195 CREATE OR REPLACE FUNCTION "write_event_initiative_revoked_trigger"()
1196 RETURNS TRIGGER
1197 LANGUAGE 'plpgsql' VOLATILE AS $$
1198 DECLARE
1199 "issue_row" "issue"%ROWTYPE;
1200 "area_row" "area"%ROWTYPE;
1201 "draft_id_v" "draft"."id"%TYPE;
1202 BEGIN
1203 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1204 SELECT * INTO "issue_row" FROM "issue"
1205 WHERE "id" = NEW."issue_id" FOR SHARE;
1206 SELECT * INTO "area_row" FROM "area"
1207 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1208 SELECT "id" INTO "draft_id_v" FROM "current_draft"
1209 WHERE "initiative_id" = NEW."id" FOR SHARE;
1210 INSERT INTO "event" (
1211 "event", "member_id",
1212 "unit_id", "area_id", "policy_id", "issue_id", "state",
1213 "initiative_id", "draft_id"
1214 ) VALUES (
1215 'initiative_revoked', NEW."revoked_by_member_id",
1216 "area_row"."unit_id", "issue_row"."area_id",
1217 "issue_row"."policy_id",
1218 NEW."issue_id", "issue_row"."state",
1219 NEW."id", "draft_id_v"
1220 );
1221 END IF;
1222 RETURN NULL;
1223 END;
1224 $$;
1227 CREATE OR REPLACE FUNCTION "write_event_suggestion_created_trigger"()
1228 RETURNS TRIGGER
1229 LANGUAGE 'plpgsql' VOLATILE AS $$
1230 DECLARE
1231 "initiative_row" "initiative"%ROWTYPE;
1232 "issue_row" "issue"%ROWTYPE;
1233 "area_row" "area"%ROWTYPE;
1234 BEGIN
1235 SELECT * INTO "initiative_row" FROM "initiative"
1236 WHERE "id" = NEW."initiative_id" FOR SHARE;
1237 SELECT * INTO "issue_row" FROM "issue"
1238 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1239 SELECT * INTO "area_row" FROM "area"
1240 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1241 INSERT INTO "event" (
1242 "event", "member_id",
1243 "unit_id", "area_id", "policy_id", "issue_id", "state",
1244 "initiative_id", "suggestion_id"
1245 ) VALUES (
1246 'suggestion_created', NEW."author_id",
1247 "area_row"."unit_id", "issue_row"."area_id", "issue_row"."policy_id",
1248 "initiative_row"."issue_id", "issue_row"."state",
1249 NEW."initiative_id", NEW."id"
1250 );
1251 RETURN NULL;
1252 END;
1253 $$;
1256 CREATE FUNCTION "write_event_suggestion_removed_trigger"()
1257 RETURNS TRIGGER
1258 LANGUAGE 'plpgsql' VOLATILE AS $$
1259 DECLARE
1260 "initiative_row" "initiative"%ROWTYPE;
1261 "issue_row" "issue"%ROWTYPE;
1262 "area_row" "area"%ROWTYPE;
1263 BEGIN
1264 SELECT * INTO "initiative_row" FROM "initiative"
1265 WHERE "id" = OLD."initiative_id" FOR SHARE;
1266 IF "initiative_row"."id" NOTNULL THEN
1267 SELECT * INTO "issue_row" FROM "issue"
1268 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1269 SELECT * INTO "area_row" FROM "area"
1270 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1271 INSERT INTO "event" (
1272 "event",
1273 "unit_id", "area_id", "policy_id", "issue_id", "state",
1274 "initiative_id", "suggestion_id"
1275 ) VALUES (
1276 'suggestion_deleted',
1277 "area_row"."unit_id", "issue_row"."area_id",
1278 "issue_row"."policy_id",
1279 "initiative_row"."issue_id", "issue_row"."state",
1280 OLD."initiative_id", OLD."id"
1281 );
1282 END IF;
1283 RETURN NULL;
1284 END;
1285 $$;
1287 CREATE TRIGGER "write_event_suggestion_removed"
1288 AFTER DELETE ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1289 "write_event_suggestion_removed_trigger"();
1291 COMMENT ON FUNCTION "write_event_suggestion_removed_trigger"() IS 'Implementation of trigger "write_event_suggestion_removed" on table "issue"';
1292 COMMENT ON TRIGGER "write_event_suggestion_removed" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1295 CREATE FUNCTION "write_event_member_trigger"()
1296 RETURNS TRIGGER
1297 LANGUAGE 'plpgsql' VOLATILE AS $$
1298 BEGIN
1299 IF TG_OP = 'INSERT' THEN
1300 IF NEW."activated" NOTNULL AND NEW."deleted" ISNULL THEN
1301 INSERT INTO "event" ("event", "member_id")
1302 VALUES ('member_activated', NEW."id");
1303 END IF;
1304 IF NEW."active" THEN
1305 INSERT INTO "event" ("event", "member_id", "boolean_value")
1306 VALUES ('member_active', NEW."id", TRUE);
1307 END IF;
1308 ELSIF TG_OP = 'UPDATE' THEN
1309 IF OLD."id" != NEW."id" THEN
1310 RAISE EXCEPTION 'Cannot change member ID';
1311 END IF;
1312 IF
1313 (OLD."activated" ISNULL OR OLD."deleted" NOTNULL) AND
1314 NEW."activated" NOTNULL AND NEW."deleted" ISNULL
1315 THEN
1316 INSERT INTO "event" ("event", "member_id")
1317 VALUES ('member_activated', NEW."id");
1318 END IF;
1319 IF OLD."active" != NEW."active" THEN
1320 INSERT INTO "event" ("event", "member_id", "boolean_value") VALUES (
1321 'member_active', NEW."id", NEW."active"
1322 );
1323 END IF;
1324 IF OLD."name" != NEW."name" THEN
1325 INSERT INTO "event" (
1326 "event", "member_id", "text_value", "old_text_value"
1327 ) VALUES (
1328 'member_name_updated', NEW."id", NEW."name", OLD."name"
1329 );
1330 END IF;
1331 IF
1332 OLD."activated" NOTNULL AND OLD."deleted" ISNULL AND
1333 (NEW."activated" ISNULL OR NEW."deleted" NOTNULL)
1334 THEN
1335 INSERT INTO "event" ("event", "member_id")
1336 VALUES ('member_deleted', NEW."id");
1337 END IF;
1338 END IF;
1339 RETURN NULL;
1340 END;
1341 $$;
1343 CREATE TRIGGER "write_event_member"
1344 AFTER INSERT OR UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1345 "write_event_member_trigger"();
1347 COMMENT ON FUNCTION "write_event_member_trigger"() IS 'Implementation of trigger "write_event_member" on table "member"';
1348 COMMENT ON TRIGGER "write_event_member" ON "member" IS 'Create entries in "event" table on insertion to member table';
1351 CREATE FUNCTION "write_event_member_profile_updated_trigger"()
1352 RETURNS TRIGGER
1353 LANGUAGE 'plpgsql' VOLATILE AS $$
1354 BEGIN
1355 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1356 IF EXISTS (SELECT NULL FROM "member" WHERE "id" = OLD."member_id") THEN
1357 INSERT INTO "event" ("event", "member_id") VALUES (
1358 'member_profile_updated', OLD."member_id"
1359 );
1360 END IF;
1361 END IF;
1362 IF TG_OP = 'UPDATE' THEN
1363 IF OLD."member_id" = NEW."member_id" THEN
1364 RETURN NULL;
1365 END IF;
1366 END IF;
1367 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1368 INSERT INTO "event" ("event", "member_id") VALUES (
1369 'member_profile_updated', NEW."member_id"
1370 );
1371 END IF;
1372 RETURN NULL;
1373 END;
1374 $$;
1376 CREATE TRIGGER "write_event_member_profile_updated"
1377 AFTER INSERT OR UPDATE OR DELETE ON "member_profile"
1378 FOR EACH ROW EXECUTE PROCEDURE
1379 "write_event_member_profile_updated_trigger"();
1381 COMMENT ON FUNCTION "write_event_member_profile_updated_trigger"() IS 'Implementation of trigger "write_event_member_profile_updated" on table "member_profile"';
1382 COMMENT ON TRIGGER "write_event_member_profile_updated" ON "member_profile" IS 'Creates entries in "event" table on member profile update';
1385 CREATE FUNCTION "write_event_member_image_updated_trigger"()
1386 RETURNS TRIGGER
1387 LANGUAGE 'plpgsql' VOLATILE AS $$
1388 BEGIN
1389 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1390 IF NOT OLD."scaled" THEN
1391 IF EXISTS (SELECT NULL FROM "member" WHERE "id" = OLD."member_id") THEN
1392 INSERT INTO "event" ("event", "member_id") VALUES (
1393 'member_image_updated', OLD."member_id"
1394 );
1395 END IF;
1396 END IF;
1397 END IF;
1398 IF TG_OP = 'UPDATE' THEN
1399 IF
1400 OLD."member_id" = NEW."member_id" AND
1401 OLD."scaled" = NEW."scaled"
1402 THEN
1403 RETURN NULL;
1404 END IF;
1405 END IF;
1406 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1407 IF NOT NEW."scaled" THEN
1408 INSERT INTO "event" ("event", "member_id") VALUES (
1409 'member_image_updated', NEW."member_id"
1410 );
1411 END IF;
1412 END IF;
1413 RETURN NULL;
1414 END;
1415 $$;
1417 CREATE TRIGGER "write_event_member_image_updated"
1418 AFTER INSERT OR UPDATE OR DELETE ON "member_image"
1419 FOR EACH ROW EXECUTE PROCEDURE
1420 "write_event_member_image_updated_trigger"();
1422 COMMENT ON FUNCTION "write_event_member_image_updated_trigger"() IS 'Implementation of trigger "write_event_member_image_updated" on table "member_image"';
1423 COMMENT ON TRIGGER "write_event_member_image_updated" ON "member_image" IS 'Creates entries in "event" table on member image update';
1426 CREATE FUNCTION "write_event_interest_trigger"()
1427 RETURNS TRIGGER
1428 LANGUAGE 'plpgsql' VOLATILE AS $$
1429 DECLARE
1430 "issue_row" "issue"%ROWTYPE;
1431 "area_row" "area"%ROWTYPE;
1432 BEGIN
1433 IF TG_OP = 'UPDATE' THEN
1434 IF OLD = NEW THEN
1435 RETURN NULL;
1436 END IF;
1437 END IF;
1438 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1439 SELECT * INTO "issue_row" FROM "issue"
1440 WHERE "id" = OLD."issue_id" FOR SHARE;
1441 SELECT * INTO "area_row" FROM "area"
1442 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1443 IF "issue_row"."id" NOTNULL THEN
1444 INSERT INTO "event" (
1445 "event", "member_id",
1446 "unit_id", "area_id", "policy_id", "issue_id", "state",
1447 "boolean_value"
1448 ) VALUES (
1449 'interest', OLD."member_id",
1450 "area_row"."unit_id", "issue_row"."area_id",
1451 "issue_row"."policy_id",
1452 OLD."issue_id", "issue_row"."state",
1453 FALSE
1454 );
1455 END IF;
1456 END IF;
1457 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1458 SELECT * INTO "issue_row" FROM "issue"
1459 WHERE "id" = NEW."issue_id" FOR SHARE;
1460 SELECT * INTO "area_row" FROM "area"
1461 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1462 INSERT INTO "event" (
1463 "event", "member_id",
1464 "unit_id", "area_id", "policy_id", "issue_id", "state",
1465 "boolean_value"
1466 ) VALUES (
1467 'interest', NEW."member_id",
1468 "area_row"."unit_id", "issue_row"."area_id",
1469 "issue_row"."policy_id",
1470 NEW."issue_id", "issue_row"."state",
1471 TRUE
1472 );
1473 END IF;
1474 RETURN NULL;
1475 END;
1476 $$;
1478 CREATE TRIGGER "write_event_interest"
1479 AFTER INSERT OR UPDATE OR DELETE ON "interest" FOR EACH ROW EXECUTE PROCEDURE
1480 "write_event_interest_trigger"();
1482 COMMENT ON FUNCTION "write_event_interest_trigger"() IS 'Implementation of trigger "write_event_interest_inserted" on table "interest"';
1483 COMMENT ON TRIGGER "write_event_interest" ON "interest" IS 'Create entry in "event" table on adding or removing interest';
1486 CREATE FUNCTION "write_event_initiator_trigger"()
1487 RETURNS TRIGGER
1488 LANGUAGE 'plpgsql' VOLATILE AS $$
1489 DECLARE
1490 "initiative_row" "initiative"%ROWTYPE;
1491 "issue_row" "issue"%ROWTYPE;
1492 "area_row" "area"%ROWTYPE;
1493 "accepted_v" BOOLEAN = FALSE;
1494 "rejected_v" BOOLEAN = FALSE;
1495 BEGIN
1496 IF TG_OP = 'UPDATE' THEN
1497 IF
1498 OLD."initiative_id" = NEW."initiative_id" AND
1499 OLD."member_id" = NEW."member_id"
1500 THEN
1501 IF
1502 coalesce(OLD."accepted", FALSE) = coalesce(NEW."accepted", FALSE)
1503 THEN
1504 RETURN NULL;
1505 END IF;
1506 IF coalesce(NEW."accepted", FALSE) = TRUE THEN
1507 "accepted_v" := TRUE;
1508 ELSE
1509 "rejected_v" := TRUE;
1510 END IF;
1511 END IF;
1512 END IF;
1513 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "accepted_v" THEN
1514 IF coalesce(OLD."accepted", FALSE) = TRUE THEN
1515 SELECT * INTO "initiative_row" FROM "initiative"
1516 WHERE "id" = OLD."initiative_id" FOR SHARE;
1517 IF "initiative_row"."id" NOTNULL THEN
1518 SELECT * INTO "issue_row" FROM "issue"
1519 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1520 SELECT * INTO "area_row" FROM "area"
1521 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1522 INSERT INTO "event" (
1523 "event", "member_id",
1524 "unit_id", "area_id", "policy_id", "issue_id", "state",
1525 "initiative_id", "boolean_value"
1526 ) VALUES (
1527 'initiator', OLD."member_id",
1528 "area_row"."unit_id", "issue_row"."area_id",
1529 "issue_row"."policy_id",
1530 "issue_row"."id", "issue_row"."state",
1531 OLD."initiative_id", FALSE
1532 );
1533 END IF;
1534 END IF;
1535 END IF;
1536 IF TG_OP = 'UPDATE' AND NOT "rejected_v" THEN
1537 IF coalesce(NEW."accepted", FALSE) = TRUE THEN
1538 SELECT * INTO "initiative_row" FROM "initiative"
1539 WHERE "id" = NEW."initiative_id" FOR SHARE;
1540 SELECT * INTO "issue_row" FROM "issue"
1541 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1542 SELECT * INTO "area_row" FROM "area"
1543 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1544 INSERT INTO "event" (
1545 "event", "member_id",
1546 "unit_id", "area_id", "policy_id", "issue_id", "state",
1547 "initiative_id", "boolean_value"
1548 ) VALUES (
1549 'initiator', NEW."member_id",
1550 "area_row"."unit_id", "issue_row"."area_id",
1551 "issue_row"."policy_id",
1552 "issue_row"."id", "issue_row"."state",
1553 NEW."initiative_id", TRUE
1554 );
1555 END IF;
1556 END IF;
1557 RETURN NULL;
1558 END;
1559 $$;
1561 CREATE TRIGGER "write_event_initiator"
1562 AFTER UPDATE OR DELETE ON "initiator" FOR EACH ROW EXECUTE PROCEDURE
1563 "write_event_initiator_trigger"();
1565 COMMENT ON FUNCTION "write_event_initiator_trigger"() IS 'Implementation of trigger "write_event_initiator" on table "initiator"';
1566 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)';
1569 CREATE FUNCTION "write_event_support_trigger"()
1570 RETURNS TRIGGER
1571 LANGUAGE 'plpgsql' VOLATILE AS $$
1572 DECLARE
1573 "issue_row" "issue"%ROWTYPE;
1574 "area_row" "area"%ROWTYPE;
1575 BEGIN
1576 IF TG_OP = 'UPDATE' THEN
1577 IF
1578 OLD."initiative_id" = NEW."initiative_id" AND
1579 OLD."member_id" = NEW."member_id"
1580 THEN
1581 IF OLD."draft_id" != NEW."draft_id" THEN
1582 SELECT * INTO "issue_row" FROM "issue"
1583 WHERE "id" = NEW."issue_id" FOR SHARE;
1584 SELECT * INTO "area_row" FROM "area"
1585 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1586 INSERT INTO "event" (
1587 "event", "member_id",
1588 "unit_id", "area_id", "policy_id", "issue_id", "state",
1589 "initiative_id", "draft_id"
1590 ) VALUES (
1591 'support_updated', NEW."member_id",
1592 "area_row"."unit_id", "issue_row"."area_id",
1593 "issue_row"."policy_id",
1594 "issue_row"."id", "issue_row"."state",
1595 NEW."initiative_id", NEW."draft_id"
1596 );
1597 END IF;
1598 RETURN NULL;
1599 END IF;
1600 END IF;
1601 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1602 IF EXISTS (
1603 SELECT NULL FROM "initiative" WHERE "id" = OLD."initiative_id"
1604 FOR SHARE
1605 ) THEN
1606 SELECT * INTO "issue_row" FROM "issue"
1607 WHERE "id" = OLD."issue_id" FOR SHARE;
1608 SELECT * INTO "area_row" FROM "area"
1609 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1610 INSERT INTO "event" (
1611 "event", "member_id",
1612 "unit_id", "area_id", "policy_id", "issue_id", "state",
1613 "initiative_id", "boolean_value"
1614 ) VALUES (
1615 'support', OLD."member_id",
1616 "area_row"."unit_id", "issue_row"."area_id",
1617 "issue_row"."policy_id",
1618 "issue_row"."id", "issue_row"."state",
1619 OLD."initiative_id", FALSE
1620 );
1621 END IF;
1622 END IF;
1623 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1624 SELECT * INTO "issue_row" FROM "issue"
1625 WHERE "id" = NEW."issue_id" FOR SHARE;
1626 SELECT * INTO "area_row" FROM "area"
1627 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1628 INSERT INTO "event" (
1629 "event", "member_id",
1630 "unit_id", "area_id", "policy_id", "issue_id", "state",
1631 "initiative_id", "draft_id", "boolean_value"
1632 ) VALUES (
1633 'support', NEW."member_id",
1634 "area_row"."unit_id", "issue_row"."area_id",
1635 "issue_row"."policy_id",
1636 "issue_row"."id", "issue_row"."state",
1637 NEW."initiative_id", NEW."draft_id", TRUE
1638 );
1639 END IF;
1640 RETURN NULL;
1641 END;
1642 $$;
1644 CREATE TRIGGER "write_event_support"
1645 AFTER INSERT OR UPDATE OR DELETE ON "supporter" FOR EACH ROW EXECUTE PROCEDURE
1646 "write_event_support_trigger"();
1648 COMMENT ON FUNCTION "write_event_support_trigger"() IS 'Implementation of trigger "write_event_support" on table "supporter"';
1649 COMMENT ON TRIGGER "write_event_support" ON "supporter" IS 'Create entry in "event" table when adding, updating, or removing support';
1652 CREATE FUNCTION "write_event_suggestion_rated_trigger"()
1653 RETURNS TRIGGER
1654 LANGUAGE 'plpgsql' VOLATILE AS $$
1655 DECLARE
1656 "same_pkey_v" BOOLEAN = FALSE;
1657 "initiative_row" "initiative"%ROWTYPE;
1658 "issue_row" "issue"%ROWTYPE;
1659 "area_row" "area"%ROWTYPE;
1660 BEGIN
1661 IF TG_OP = 'UPDATE' THEN
1662 IF
1663 OLD."suggestion_id" = NEW."suggestion_id" AND
1664 OLD."member_id" = NEW."member_id"
1665 THEN
1666 IF
1667 OLD."degree" = NEW."degree" AND
1668 OLD."fulfilled" = NEW."fulfilled"
1669 THEN
1670 RETURN NULL;
1671 END IF;
1672 "same_pkey_v" := TRUE;
1673 END IF;
1674 END IF;
1675 IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND NOT "same_pkey_v" THEN
1676 IF EXISTS (
1677 SELECT NULL FROM "suggestion" WHERE "id" = OLD."suggestion_id"
1678 FOR SHARE
1679 ) THEN
1680 SELECT * INTO "initiative_row" FROM "initiative"
1681 WHERE "id" = OLD."initiative_id" FOR SHARE;
1682 SELECT * INTO "issue_row" FROM "issue"
1683 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1684 SELECT * INTO "area_row" FROM "area"
1685 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1686 INSERT INTO "event" (
1687 "event", "member_id",
1688 "unit_id", "area_id", "policy_id", "issue_id", "state",
1689 "initiative_id", "suggestion_id",
1690 "boolean_value", "numeric_value"
1691 ) VALUES (
1692 'suggestion_rated', OLD."member_id",
1693 "area_row"."unit_id", "issue_row"."area_id",
1694 "issue_row"."policy_id",
1695 "initiative_row"."issue_id", "issue_row"."state",
1696 OLD."initiative_id", OLD."suggestion_id",
1697 NULL, 0
1698 );
1699 END IF;
1700 END IF;
1701 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1702 SELECT * INTO "initiative_row" FROM "initiative"
1703 WHERE "id" = NEW."initiative_id" FOR SHARE;
1704 SELECT * INTO "issue_row" FROM "issue"
1705 WHERE "id" = "initiative_row"."issue_id" FOR SHARE;
1706 SELECT * INTO "area_row" FROM "area"
1707 WHERE "id" = "issue_row"."area_id" FOR SHARE;
1708 INSERT INTO "event" (
1709 "event", "member_id",
1710 "unit_id", "area_id", "policy_id", "issue_id", "state",
1711 "initiative_id", "suggestion_id",
1712 "boolean_value", "numeric_value"
1713 ) VALUES (
1714 'suggestion_rated', NEW."member_id",
1715 "area_row"."unit_id", "issue_row"."area_id",
1716 "issue_row"."policy_id",
1717 "initiative_row"."issue_id", "issue_row"."state",
1718 NEW."initiative_id", NEW."suggestion_id",
1719 NEW."fulfilled", NEW."degree"
1720 );
1721 END IF;
1722 RETURN NULL;
1723 END;
1724 $$;
1726 CREATE TRIGGER "write_event_suggestion_rated"
1727 AFTER INSERT OR UPDATE OR DELETE ON "opinion" FOR EACH ROW EXECUTE PROCEDURE
1728 "write_event_suggestion_rated_trigger"();
1730 COMMENT ON FUNCTION "write_event_suggestion_rated_trigger"() IS 'Implementation of trigger "write_event_suggestion_rated" on table "opinion"';
1731 COMMENT ON TRIGGER "write_event_suggestion_rated" ON "opinion" IS 'Create entry in "event" table when adding, updating, or removing support';
1734 CREATE FUNCTION "write_event_delegation_trigger"()
1735 RETURNS TRIGGER
1736 LANGUAGE 'plpgsql' VOLATILE AS $$
1737 DECLARE
1738 "issue_row" "issue"%ROWTYPE;
1739 "area_row" "area"%ROWTYPE;
1740 BEGIN
1741 IF TG_OP = 'DELETE' THEN
1742 IF EXISTS (
1743 SELECT NULL FROM "member" WHERE "id" = OLD."truster_id"
1744 ) AND (CASE OLD."scope"
1745 WHEN 'unit'::"delegation_scope" THEN EXISTS (
1746 SELECT NULL FROM "unit" WHERE "id" = OLD."unit_id"
1747 )
1748 WHEN 'area'::"delegation_scope" THEN EXISTS (
1749 SELECT NULL FROM "area" WHERE "id" = OLD."area_id"
1750 )
1751 WHEN 'issue'::"delegation_scope" THEN EXISTS (
1752 SELECT NULL FROM "issue" WHERE "id" = OLD."issue_id"
1753 )
1754 END) THEN
1755 SELECT * INTO "issue_row" FROM "issue"
1756 WHERE "id" = OLD."issue_id" FOR SHARE;
1757 SELECT * INTO "area_row" FROM "area"
1758 WHERE "id" = COALESCE(OLD."area_id", "issue_row"."area_id")
1759 FOR SHARE;
1760 INSERT INTO "event" (
1761 "event", "member_id", "scope",
1762 "unit_id", "area_id", "issue_id", "state",
1763 "boolean_value"
1764 ) VALUES (
1765 'delegation', OLD."truster_id", OLD."scope",
1766 COALESCE(OLD."unit_id", "area_row"."unit_id"), "area_row"."id",
1767 OLD."issue_id", "issue_row"."state",
1768 FALSE
1769 );
1770 END IF;
1771 ELSE
1772 SELECT * INTO "issue_row" FROM "issue"
1773 WHERE "id" = NEW."issue_id" FOR SHARE;
1774 SELECT * INTO "area_row" FROM "area"
1775 WHERE "id" = COALESCE(NEW."area_id", "issue_row"."area_id")
1776 FOR SHARE;
1777 INSERT INTO "event" (
1778 "event", "member_id", "other_member_id", "scope",
1779 "unit_id", "area_id", "issue_id", "state",
1780 "boolean_value"
1781 ) VALUES (
1782 'delegation', NEW."truster_id", NEW."trustee_id", NEW."scope",
1783 COALESCE(NEW."unit_id", "area_row"."unit_id"), "area_row"."id",
1784 NEW."issue_id", "issue_row"."state",
1785 TRUE
1786 );
1787 END IF;
1788 RETURN NULL;
1789 END;
1790 $$;
1792 CREATE TRIGGER "write_event_delegation"
1793 AFTER INSERT OR UPDATE OR DELETE ON "delegation" FOR EACH ROW EXECUTE PROCEDURE
1794 "write_event_delegation_trigger"();
1796 COMMENT ON FUNCTION "write_event_delegation_trigger"() IS 'Implementation of trigger "write_event_delegation" on table "delegation"';
1797 COMMENT ON TRIGGER "write_event_delegation" ON "delegation" IS 'Create entry in "event" table when adding, updating, or removing a delegation';
1800 CREATE FUNCTION "write_event_contact_trigger"()
1801 RETURNS TRIGGER
1802 LANGUAGE 'plpgsql' VOLATILE AS $$
1803 BEGIN
1804 IF TG_OP = 'UPDATE' THEN
1805 IF
1806 OLD."member_id" = NEW."member_id" AND
1807 OLD."other_member_id" = NEW."other_member_id" AND
1808 OLD."public" = NEW."public"
1809 THEN
1810 RETURN NULL;
1811 END IF;
1812 END IF;
1813 IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
1814 IF OLD."public" THEN
1815 IF EXISTS (
1816 SELECT NULL FROM "member" WHERE "id" = OLD."member_id"
1817 FOR SHARE
1818 ) AND EXISTS (
1819 SELECT NULL FROM "member" WHERE "id" = OLD."other_member_id"
1820 FOR SHARE
1821 ) THEN
1822 INSERT INTO "event" (
1823 "event", "member_id", "other_member_id", "boolean_value"
1824 ) VALUES (
1825 'contact', OLD."member_id", OLD."other_member_id", FALSE
1826 );
1827 END IF;
1828 END IF;
1829 END IF;
1830 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1831 IF NEW."public" THEN
1832 INSERT INTO "event" (
1833 "event", "member_id", "other_member_id", "boolean_value"
1834 ) VALUES (
1835 'contact', NEW."member_id", NEW."other_member_id", TRUE
1836 );
1837 END IF;
1838 END IF;
1839 RETURN NULL;
1840 END;
1841 $$;
1843 CREATE TRIGGER "write_event_contact"
1844 AFTER INSERT OR UPDATE OR DELETE ON "contact" FOR EACH ROW EXECUTE PROCEDURE
1845 "write_event_contact_trigger"();
1847 COMMENT ON FUNCTION "write_event_contact_trigger"() IS 'Implementation of trigger "write_event_contact" on table "contact"';
1848 COMMENT ON TRIGGER "write_event_contact" ON "contact" IS 'Create entry in "event" table when adding or removing public contacts';
1851 CREATE FUNCTION "send_event_notify_trigger"()
1852 RETURNS TRIGGER
1853 LANGUAGE 'plpgsql' VOLATILE AS $$
1854 BEGIN
1855 EXECUTE 'NOTIFY "event", ''' || NEW."event" || '''';
1856 RETURN NULL;
1857 END;
1858 $$;
1860 CREATE TRIGGER "send_notify"
1861 AFTER INSERT OR UPDATE ON "event" FOR EACH ROW EXECUTE PROCEDURE
1862 "send_event_notify_trigger"();
1865 CREATE FUNCTION "delete_extended_scope_tokens_trigger"()
1866 RETURNS TRIGGER
1867 LANGUAGE 'plpgsql' VOLATILE AS $$
1868 DECLARE
1869 "system_application_row" "system_application"%ROWTYPE;
1870 BEGIN
1871 IF OLD."system_application_id" NOTNULL THEN
1872 SELECT * FROM "system_application" INTO "system_application_row"
1873 WHERE "id" = OLD."system_application_id";
1874 DELETE FROM "token"
1875 WHERE "member_id" = OLD."member_id"
1876 AND "system_application_id" = OLD."system_application_id"
1877 AND NOT COALESCE(
1878 regexp_split_to_array("scope", E'\\s+') <@
1879 regexp_split_to_array(
1880 "system_application_row"."automatic_scope", E'\\s+'
1881 ),
1882 FALSE
1883 );
1884 END IF;
1885 RETURN OLD;
1886 END;
1887 $$;
1889 CREATE TRIGGER "delete_extended_scope_tokens"
1890 BEFORE DELETE ON "member_application" FOR EACH ROW EXECUTE PROCEDURE
1891 "delete_extended_scope_tokens_trigger"();
1894 CREATE FUNCTION "detach_token_from_session_trigger"()
1895 RETURNS TRIGGER
1896 LANGUAGE 'plpgsql' VOLATILE AS $$
1897 BEGIN
1898 UPDATE "token" SET "session_id" = NULL
1899 WHERE "session_id" = OLD."id";
1900 RETURN OLD;
1901 END;
1902 $$;
1904 CREATE TRIGGER "detach_token_from_session"
1905 BEFORE DELETE ON "session" FOR EACH ROW EXECUTE PROCEDURE
1906 "detach_token_from_session_trigger"();
1909 CREATE FUNCTION "delete_non_detached_scope_with_session_trigger"()
1910 RETURNS TRIGGER
1911 LANGUAGE 'plpgsql' VOLATILE AS $$
1912 BEGIN
1913 IF NEW."session_id" ISNULL THEN
1914 SELECT coalesce(string_agg("element", ' '), '') INTO NEW."scope"
1915 FROM unnest(regexp_split_to_array(NEW."scope", E'\\s+')) AS "element"
1916 WHERE "element" LIKE '%_detached';
1917 END IF;
1918 RETURN NEW;
1919 END;
1920 $$;
1922 CREATE TRIGGER "delete_non_detached_scope_with_session"
1923 BEFORE INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
1924 "delete_non_detached_scope_with_session_trigger"();
1927 CREATE FUNCTION "delete_token_with_empty_scope_trigger"()
1928 RETURNS TRIGGER
1929 LANGUAGE 'plpgsql' VOLATILE AS $$
1930 BEGIN
1931 IF NEW."scope" = '' THEN
1932 DELETE FROM "token" WHERE "id" = NEW."id";
1933 END IF;
1934 RETURN NULL;
1935 END;
1936 $$;
1938 CREATE TRIGGER "delete_token_with_empty_scope"
1939 AFTER INSERT OR UPDATE ON "token" FOR EACH ROW EXECUTE PROCEDURE
1940 "delete_token_with_empty_scope_trigger"();
1943 CREATE FUNCTION "delete_snapshot_on_partial_delete_trigger"()
1944 RETURNS TRIGGER
1945 LANGUAGE 'plpgsql' VOLATILE AS $$
1946 BEGIN
1947 IF TG_OP = 'UPDATE' THEN
1948 IF
1949 OLD."snapshot_id" = NEW."snapshot_id" AND
1950 OLD."issue_id" = NEW."issue_id"
1951 THEN
1952 RETURN NULL;
1953 END IF;
1954 END IF;
1955 DELETE FROM "snapshot" WHERE "id" = OLD."snapshot_id";
1956 RETURN NULL;
1957 END;
1958 $$;
1960 CREATE TRIGGER "delete_snapshot_on_partial_delete"
1961 AFTER UPDATE OR DELETE ON "snapshot_issue"
1962 FOR EACH ROW EXECUTE PROCEDURE
1963 "delete_snapshot_on_partial_delete_trigger"();
1965 COMMENT ON FUNCTION "delete_snapshot_on_partial_delete_trigger"() IS 'Implementation of trigger "delete_snapshot_on_partial_delete" on table "snapshot_issue"';
1966 COMMENT ON TRIGGER "delete_snapshot_on_partial_delete" ON "snapshot_issue" IS 'Deletes whole snapshot if one issue is deleted from the snapshot';
1969 CREATE FUNCTION "copy_current_draft_data"
1970 ("initiative_id_p" "initiative"."id"%TYPE )
1971 RETURNS VOID
1972 LANGUAGE 'plpgsql' VOLATILE AS $$
1973 BEGIN
1974 PERFORM NULL FROM "initiative" WHERE "id" = "initiative_id_p"
1975 FOR UPDATE;
1976 UPDATE "initiative" SET
1977 "location" = "draft"."location",
1978 "draft_text_search_data" = "draft"."text_search_data"
1979 FROM "current_draft" AS "draft"
1980 WHERE "initiative"."id" = "initiative_id_p"
1981 AND "draft"."initiative_id" = "initiative_id_p";
1982 END;
1983 $$;
1985 COMMENT ON FUNCTION "copy_current_draft_data"
1986 ( "initiative"."id"%TYPE )
1987 IS 'Helper function for function "copy_current_draft_data_trigger"';
1990 CREATE FUNCTION "copy_current_draft_data_trigger"()
1991 RETURNS TRIGGER
1992 LANGUAGE 'plpgsql' VOLATILE AS $$
1993 BEGIN
1994 IF TG_OP='DELETE' THEN
1995 PERFORM "copy_current_draft_data"(OLD."initiative_id");
1996 ELSE
1997 IF TG_OP='UPDATE' THEN
1998 IF COALESCE(OLD."inititiave_id" != NEW."initiative_id", TRUE) THEN
1999 PERFORM "copy_current_draft_data"(OLD."initiative_id");
2000 END IF;
2001 END IF;
2002 PERFORM "copy_current_draft_data"(NEW."initiative_id");
2003 END IF;
2004 RETURN NULL;
2005 END;
2006 $$;
2008 CREATE TRIGGER "copy_current_draft_data"
2009 AFTER INSERT OR UPDATE OR DELETE ON "draft"
2010 FOR EACH ROW EXECUTE PROCEDURE
2011 "copy_current_draft_data_trigger"();
2013 COMMENT ON FUNCTION "copy_current_draft_data_trigger"() IS 'Implementation of trigger "copy_current_draft_data" on table "draft"';
2014 COMMENT ON TRIGGER "copy_current_draft_data" ON "draft" IS 'Copy certain fields from most recent "draft" to "initiative"';
2017 CREATE VIEW "area_quorum" AS
2018 SELECT
2019 "area"."id" AS "area_id",
2020 ceil(
2021 "area"."quorum_standard"::FLOAT8 * "quorum_factor"::FLOAT8 ^ (
2022 coalesce(
2023 ( SELECT sum(
2024 ( extract(epoch from "area"."quorum_time")::FLOAT8 /
2025 extract(epoch from
2026 ("issue"."accepted"-"issue"."created") +
2027 "issue"."discussion_time" +
2028 "issue"."verification_time" +
2029 "issue"."voting_time"
2030 )::FLOAT8
2031 ) ^ "area"."quorum_exponent"::FLOAT8
2032 )
2033 FROM "issue" JOIN "policy"
2034 ON "issue"."policy_id" = "policy"."id"
2035 WHERE "issue"."area_id" = "area"."id"
2036 AND "issue"."accepted" NOTNULL
2037 AND "issue"."closed" ISNULL
2038 AND "policy"."polling" = FALSE
2039 )::FLOAT8, 0::FLOAT8
2040 ) / "area"."quorum_issues"::FLOAT8 - 1::FLOAT8
2041 ) * CASE WHEN "area"."quorum_den" ISNULL THEN 1 ELSE (
2042 SELECT "snapshot"."population"
2043 FROM "snapshot"
2044 WHERE "snapshot"."area_id" = "area"."id"
2045 AND "snapshot"."issue_id" ISNULL
2046 ORDER BY "snapshot"."id" DESC
2047 LIMIT 1
2048 ) END / coalesce("area"."quorum_den", 1)
2050 )::INT4 AS "issue_quorum"
2051 FROM "area";
2053 COMMENT ON VIEW "area_quorum" IS 'Area-based quorum considering number of open (accepted) issues';
2056 CREATE VIEW "area_with_unaccepted_issues" AS
2057 SELECT DISTINCT ON ("area"."id") "area".*
2058 FROM "area" JOIN "issue" ON "area"."id" = "issue"."area_id"
2059 WHERE "issue"."state" = 'admission';
2061 COMMENT ON VIEW "area_with_unaccepted_issues" IS 'All areas with unaccepted open issues (needed for issue admission system)';
2064 DROP VIEW "area_member_count";
2067 DROP TABLE "membership";
2070 DROP FUNCTION "membership_weight"
2071 ( "area_id_p" "area"."id"%TYPE,
2072 "member_id_p" "member"."id"%TYPE );
2075 DROP FUNCTION "membership_weight_with_skipping"
2076 ( "area_id_p" "area"."id"%TYPE,
2077 "member_id_p" "member"."id"%TYPE,
2078 "skip_member_ids_p" INT4[] ); -- TODO: ordering/cascade
2081 CREATE OR REPLACE VIEW "issue_delegation" AS
2082 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
2083 "issue"."id" AS "issue_id",
2084 "delegation"."id",
2085 "delegation"."truster_id",
2086 "delegation"."trustee_id",
2087 "delegation"."scope"
2088 FROM "issue"
2089 JOIN "area"
2090 ON "area"."id" = "issue"."area_id"
2091 JOIN "delegation"
2092 ON "delegation"."unit_id" = "area"."unit_id"
2093 OR "delegation"."area_id" = "area"."id"
2094 OR "delegation"."issue_id" = "issue"."id"
2095 JOIN "member"
2096 ON "delegation"."truster_id" = "member"."id"
2097 JOIN "verified_privilege"
2098 ON "area"."unit_id" = "verified_privilege"."unit_id"
2099 AND "delegation"."truster_id" = "verified_privilege"."member_id"
2100 WHERE "member"."active" AND "verified_privilege"."voting_right"
2101 ORDER BY
2102 "issue"."id",
2103 "delegation"."truster_id",
2104 "delegation"."scope" DESC;
2107 CREATE VIEW "unit_member" AS
2108 SELECT
2109 "unit"."id" AS "unit_id",
2110 "member"."id" AS "member_id"
2111 FROM "verified_privilege"
2112 JOIN "unit" ON "unit_id" = "verified_privilege"."unit_id"
2113 JOIN "member" ON "member"."id" = "verified_privilege"."member_id"
2114 WHERE "verified_privilege"."voting_right" AND "member"."active";
2116 COMMENT ON VIEW "unit_member" IS 'Active members with voting right in a unit';
2119 CREATE OR REPLACE VIEW "unit_member_count" AS
2120 SELECT
2121 "unit"."id" AS "unit_id",
2122 count("unit_member"."member_id") AS "member_count"
2123 FROM "unit" LEFT JOIN "unit_member"
2124 ON "unit"."id" = "unit_member"."unit_id"
2125 GROUP BY "unit"."id";
2127 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
2130 CREATE OR REPLACE VIEW "opening_draft" AS
2131 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
2132 ORDER BY "initiative_id", "id";
2135 CREATE OR REPLACE VIEW "current_draft" AS
2136 SELECT DISTINCT ON ("initiative_id") * FROM "draft"
2137 ORDER BY "initiative_id", "id" DESC;
2140 CREATE OR REPLACE VIEW "issue_supporter_in_admission_state" AS
2141 SELECT
2142 "area"."unit_id",
2143 "issue"."area_id",
2144 "issue"."id" AS "issue_id",
2145 "supporter"."member_id",
2146 "direct_interest_snapshot"."weight"
2147 FROM "issue"
2148 JOIN "area" ON "area"."id" = "issue"."area_id"
2149 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
2150 JOIN "direct_interest_snapshot"
2151 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
2152 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
2153 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
2154 WHERE "issue"."state" = 'admission'::"issue_state";
2157 CREATE OR REPLACE VIEW "individual_suggestion_ranking" AS
2158 SELECT
2159 "opinion"."initiative_id",
2160 "opinion"."member_id",
2161 "direct_interest_snapshot"."weight",
2162 CASE WHEN
2163 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
2164 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
2165 THEN 1 ELSE
2166 CASE WHEN
2167 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
2168 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
2169 THEN 2 ELSE
2170 CASE WHEN
2171 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
2172 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
2173 THEN 3 ELSE 4 END
2174 END
2175 END AS "preference",
2176 "opinion"."suggestion_id"
2177 FROM "opinion"
2178 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
2179 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2180 JOIN "direct_interest_snapshot"
2181 ON "direct_interest_snapshot"."snapshot_id" = "issue"."latest_snapshot_id"
2182 AND "direct_interest_snapshot"."issue_id" = "issue"."id"
2183 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
2186 CREATE VIEW "expired_session" AS
2187 SELECT * FROM "session" WHERE now() > "expiry";
2189 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
2190 DELETE FROM "session" WHERE "id" = OLD."id";
2192 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
2193 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
2196 CREATE VIEW "expired_token" AS
2197 SELECT * FROM "token" WHERE now() > "expiry" AND NOT (
2198 "token_type" = 'authorization' AND "used" AND EXISTS (
2199 SELECT NULL FROM "token" AS "other"
2200 WHERE "other"."authorization_token_id" = "id" ) );
2202 CREATE RULE "delete" AS ON DELETE TO "expired_token" DO INSTEAD
2203 DELETE FROM "token" WHERE "id" = OLD."id";
2205 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';
2208 CREATE VIEW "unused_snapshot" AS
2209 SELECT "snapshot".* FROM "snapshot"
2210 LEFT JOIN "issue"
2211 ON "snapshot"."id" = "issue"."latest_snapshot_id"
2212 OR "snapshot"."id" = "issue"."admission_snapshot_id"
2213 OR "snapshot"."id" = "issue"."half_freeze_snapshot_id"
2214 OR "snapshot"."id" = "issue"."full_freeze_snapshot_id"
2215 WHERE "issue"."id" ISNULL;
2217 CREATE RULE "delete" AS ON DELETE TO "unused_snapshot" DO INSTEAD
2218 DELETE FROM "snapshot" WHERE "id" = OLD."id";
2220 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)';
2223 CREATE VIEW "expired_snapshot" AS
2224 SELECT "unused_snapshot".* FROM "unused_snapshot" CROSS JOIN "system_setting"
2225 WHERE "unused_snapshot"."calculated" <
2226 now() - "system_setting"."snapshot_retention";
2228 CREATE RULE "delete" AS ON DELETE TO "expired_snapshot" DO INSTEAD
2229 DELETE FROM "snapshot" WHERE "id" = OLD."id";
2231 COMMENT ON VIEW "expired_snapshot" IS 'Contains "unused_snapshot"s that are older than "system_setting"."snapshot_retention" (for deletion)';
2234 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest; for area and global delegation chains: always null';
2237 CREATE OR REPLACE FUNCTION "delegation_chain"
2238 ( "member_id_p" "member"."id"%TYPE,
2239 "unit_id_p" "unit"."id"%TYPE,
2240 "area_id_p" "area"."id"%TYPE,
2241 "issue_id_p" "issue"."id"%TYPE,
2242 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2243 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2244 RETURNS SETOF "delegation_chain_row"
2245 LANGUAGE 'plpgsql' STABLE AS $$
2246 DECLARE
2247 "scope_v" "delegation_scope";
2248 "unit_id_v" "unit"."id"%TYPE;
2249 "area_id_v" "area"."id"%TYPE;
2250 "issue_row" "issue"%ROWTYPE;
2251 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2252 "loop_member_id_v" "member"."id"%TYPE;
2253 "output_row" "delegation_chain_row";
2254 "output_rows" "delegation_chain_row"[];
2255 "simulate_v" BOOLEAN;
2256 "simulate_here_v" BOOLEAN;
2257 "delegation_row" "delegation"%ROWTYPE;
2258 "row_count" INT4;
2259 "i" INT4;
2260 "loop_v" BOOLEAN;
2261 BEGIN
2262 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
2263 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
2264 END IF;
2265 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
2266 "simulate_v" := TRUE;
2267 ELSE
2268 "simulate_v" := FALSE;
2269 END IF;
2270 IF
2271 "unit_id_p" NOTNULL AND
2272 "area_id_p" ISNULL AND
2273 "issue_id_p" ISNULL
2274 THEN
2275 "scope_v" := 'unit';
2276 "unit_id_v" := "unit_id_p";
2277 ELSIF
2278 "unit_id_p" ISNULL AND
2279 "area_id_p" NOTNULL AND
2280 "issue_id_p" ISNULL
2281 THEN
2282 "scope_v" := 'area';
2283 "area_id_v" := "area_id_p";
2284 SELECT "unit_id" INTO "unit_id_v"
2285 FROM "area" WHERE "id" = "area_id_v";
2286 ELSIF
2287 "unit_id_p" ISNULL AND
2288 "area_id_p" ISNULL AND
2289 "issue_id_p" NOTNULL
2290 THEN
2291 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
2292 IF "issue_row"."id" ISNULL THEN
2293 RETURN;
2294 END IF;
2295 IF "issue_row"."closed" NOTNULL THEN
2296 IF "simulate_v" THEN
2297 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
2298 END IF;
2299 FOR "output_row" IN
2300 SELECT * FROM
2301 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
2302 LOOP
2303 RETURN NEXT "output_row";
2304 END LOOP;
2305 RETURN;
2306 END IF;
2307 "scope_v" := 'issue';
2308 SELECT "area_id" INTO "area_id_v"
2309 FROM "issue" WHERE "id" = "issue_id_p";
2310 SELECT "unit_id" INTO "unit_id_v"
2311 FROM "area" WHERE "id" = "area_id_v";
2312 ELSE
2313 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2314 END IF;
2315 "visited_member_ids" := '{}';
2316 "loop_member_id_v" := NULL;
2317 "output_rows" := '{}';
2318 "output_row"."index" := 0;
2319 "output_row"."member_id" := "member_id_p";
2320 "output_row"."member_valid" := TRUE;
2321 "output_row"."participation" := FALSE;
2322 "output_row"."overridden" := FALSE;
2323 "output_row"."disabled_out" := FALSE;
2324 "output_row"."scope_out" := NULL;
2325 LOOP
2326 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2327 "loop_member_id_v" := "output_row"."member_id";
2328 ELSE
2329 "visited_member_ids" :=
2330 "visited_member_ids" || "output_row"."member_id";
2331 END IF;
2332 IF "output_row"."participation" ISNULL THEN
2333 "output_row"."overridden" := NULL;
2334 ELSIF "output_row"."participation" THEN
2335 "output_row"."overridden" := TRUE;
2336 END IF;
2337 "output_row"."scope_in" := "output_row"."scope_out";
2338 "output_row"."member_valid" := EXISTS (
2339 SELECT NULL FROM "member" JOIN "verified_privilege"
2340 ON "verified_privilege"."member_id" = "member"."id"
2341 AND "verified_privilege"."unit_id" = "unit_id_v"
2342 WHERE "id" = "output_row"."member_id"
2343 AND "member"."active" AND "verified_privilege"."voting_right"
2344 );
2345 "simulate_here_v" := (
2346 "simulate_v" AND
2347 "output_row"."member_id" = "member_id_p"
2348 );
2349 "delegation_row" := ROW(NULL);
2350 IF "output_row"."member_valid" OR "simulate_here_v" THEN
2351 IF "scope_v" = 'unit' THEN
2352 IF NOT "simulate_here_v" THEN
2353 SELECT * INTO "delegation_row" FROM "delegation"
2354 WHERE "truster_id" = "output_row"."member_id"
2355 AND "unit_id" = "unit_id_v";
2356 END IF;
2357 ELSIF "scope_v" = 'area' THEN
2358 IF "simulate_here_v" THEN
2359 IF "simulate_trustee_id_p" ISNULL THEN
2360 SELECT * INTO "delegation_row" FROM "delegation"
2361 WHERE "truster_id" = "output_row"."member_id"
2362 AND "unit_id" = "unit_id_v";
2363 END IF;
2364 ELSE
2365 SELECT * INTO "delegation_row" FROM "delegation"
2366 WHERE "truster_id" = "output_row"."member_id"
2367 AND (
2368 "unit_id" = "unit_id_v" OR
2369 "area_id" = "area_id_v"
2370 )
2371 ORDER BY "scope" DESC;
2372 END IF;
2373 ELSIF "scope_v" = 'issue' THEN
2374 IF "issue_row"."fully_frozen" ISNULL THEN
2375 "output_row"."participation" := EXISTS (
2376 SELECT NULL FROM "interest"
2377 WHERE "issue_id" = "issue_id_p"
2378 AND "member_id" = "output_row"."member_id"
2379 );
2380 ELSE
2381 IF "output_row"."member_id" = "member_id_p" THEN
2382 "output_row"."participation" := EXISTS (
2383 SELECT NULL FROM "direct_voter"
2384 WHERE "issue_id" = "issue_id_p"
2385 AND "member_id" = "output_row"."member_id"
2386 );
2387 ELSE
2388 "output_row"."participation" := NULL;
2389 END IF;
2390 END IF;
2391 IF "simulate_here_v" THEN
2392 IF "simulate_trustee_id_p" ISNULL THEN
2393 SELECT * INTO "delegation_row" FROM "delegation"
2394 WHERE "truster_id" = "output_row"."member_id"
2395 AND (
2396 "unit_id" = "unit_id_v" OR
2397 "area_id" = "area_id_v"
2398 )
2399 ORDER BY "scope" DESC;
2400 END IF;
2401 ELSE
2402 SELECT * INTO "delegation_row" FROM "delegation"
2403 WHERE "truster_id" = "output_row"."member_id"
2404 AND (
2405 "unit_id" = "unit_id_v" OR
2406 "area_id" = "area_id_v" OR
2407 "issue_id" = "issue_id_p"
2408 )
2409 ORDER BY "scope" DESC;
2410 END IF;
2411 END IF;
2412 ELSE
2413 "output_row"."participation" := FALSE;
2414 END IF;
2415 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
2416 "output_row"."scope_out" := "scope_v";
2417 "output_rows" := "output_rows" || "output_row";
2418 "output_row"."member_id" := "simulate_trustee_id_p";
2419 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2420 "output_row"."scope_out" := "delegation_row"."scope";
2421 "output_rows" := "output_rows" || "output_row";
2422 "output_row"."member_id" := "delegation_row"."trustee_id";
2423 ELSIF "delegation_row"."scope" NOTNULL THEN
2424 "output_row"."scope_out" := "delegation_row"."scope";
2425 "output_row"."disabled_out" := TRUE;
2426 "output_rows" := "output_rows" || "output_row";
2427 EXIT;
2428 ELSE
2429 "output_row"."scope_out" := NULL;
2430 "output_rows" := "output_rows" || "output_row";
2431 EXIT;
2432 END IF;
2433 EXIT WHEN "loop_member_id_v" NOTNULL;
2434 "output_row"."index" := "output_row"."index" + 1;
2435 END LOOP;
2436 "row_count" := array_upper("output_rows", 1);
2437 "i" := 1;
2438 "loop_v" := FALSE;
2439 LOOP
2440 "output_row" := "output_rows"["i"];
2441 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2442 IF "loop_v" THEN
2443 IF "i" + 1 = "row_count" THEN
2444 "output_row"."loop" := 'last';
2445 ELSIF "i" = "row_count" THEN
2446 "output_row"."loop" := 'repetition';
2447 ELSE
2448 "output_row"."loop" := 'intermediate';
2449 END IF;
2450 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2451 "output_row"."loop" := 'first';
2452 "loop_v" := TRUE;
2453 END IF;
2454 IF "scope_v" = 'unit' THEN
2455 "output_row"."participation" := NULL;
2456 END IF;
2457 RETURN NEXT "output_row";
2458 "i" := "i" + 1;
2459 END LOOP;
2460 RETURN;
2461 END;
2462 $$;
2465 CREATE OR REPLACE FUNCTION "get_initiatives_for_notification"
2466 ( "recipient_id_p" "member"."id"%TYPE )
2467 RETURNS SETOF "initiative_for_notification"
2468 LANGUAGE 'plpgsql' VOLATILE AS $$
2469 DECLARE
2470 "result_row" "initiative_for_notification"%ROWTYPE;
2471 "last_draft_id_v" "draft"."id"%TYPE;
2472 "last_suggestion_id_v" "suggestion"."id"%TYPE;
2473 BEGIN
2474 PERFORM "require_transaction_isolation"();
2475 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
2476 FOR "result_row" IN
2477 SELECT * FROM "initiative_for_notification"
2478 WHERE "recipient_id" = "recipient_id_p"
2479 LOOP
2480 SELECT "id" INTO "last_draft_id_v" FROM "draft"
2481 WHERE "draft"."initiative_id" = "result_row"."initiative_id"
2482 ORDER BY "id" DESC LIMIT 1;
2483 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
2484 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
2485 ORDER BY "id" DESC LIMIT 1;
2486 INSERT INTO "notification_initiative_sent"
2487 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
2488 VALUES (
2489 "recipient_id_p",
2490 "result_row"."initiative_id",
2491 "last_draft_id_v",
2492 "last_suggestion_id_v" )
2493 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
2494 "last_draft_id" = "last_draft_id_v",
2495 "last_suggestion_id" = "last_suggestion_id_v";
2496 RETURN NEXT "result_row";
2497 END LOOP;
2498 DELETE FROM "notification_initiative_sent"
2499 USING "initiative", "issue"
2500 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
2501 AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
2502 AND "issue"."id" = "initiative"."issue_id"
2503 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
2504 UPDATE "member" SET
2505 "notification_counter" = "notification_counter" + 1,
2506 "notification_sent" = now()
2507 WHERE "id" = "recipient_id_p";
2508 RETURN;
2509 END;
2510 $$;
2513 CREATE OR REPLACE FUNCTION "calculate_member_counts"()
2514 RETURNS VOID
2515 LANGUAGE 'plpgsql' VOLATILE AS $$
2516 BEGIN
2517 PERFORM "require_transaction_isolation"();
2518 DELETE FROM "member_count";
2519 INSERT INTO "member_count" ("total_count")
2520 SELECT "total_count" FROM "member_count_view";
2521 UPDATE "unit" SET "member_count" = "view"."member_count"
2522 FROM "unit_member_count" AS "view"
2523 WHERE "view"."unit_id" = "unit"."id";
2524 RETURN;
2525 END;
2526 $$;
2528 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"';
2531 CREATE FUNCTION "calculate_area_quorum"()
2532 RETURNS VOID
2533 LANGUAGE 'plpgsql' VOLATILE AS $$
2534 BEGIN
2535 PERFORM "dont_require_transaction_isolation"();
2536 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
2537 FROM "area_quorum" AS "view"
2538 WHERE "view"."area_id" = "area"."id";
2539 RETURN;
2540 END;
2541 $$;
2543 COMMENT ON FUNCTION "calculate_area_quorum"() IS 'Calculate column "issue_quorum" in table "area" from view "area_quorum"';
2546 DROP VIEW "remaining_harmonic_initiative_weight_summands";
2547 DROP VIEW "remaining_harmonic_supporter_weight";
2550 CREATE VIEW "remaining_harmonic_supporter_weight" AS
2551 SELECT
2552 "direct_interest_snapshot"."snapshot_id",
2553 "direct_interest_snapshot"."issue_id",
2554 "direct_interest_snapshot"."member_id",
2555 "direct_interest_snapshot"."weight" AS "weight_num",
2556 count("initiative"."id") AS "weight_den"
2557 FROM "issue"
2558 JOIN "direct_interest_snapshot"
2559 ON "issue"."latest_snapshot_id" = "direct_interest_snapshot"."snapshot_id"
2560 AND "issue"."id" = "direct_interest_snapshot"."issue_id"
2561 JOIN "initiative"
2562 ON "issue"."id" = "initiative"."issue_id"
2563 AND "initiative"."harmonic_weight" ISNULL
2564 JOIN "direct_supporter_snapshot"
2565 ON "issue"."latest_snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
2566 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
2567 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
2568 AND (
2569 "direct_supporter_snapshot"."satisfied" = TRUE OR
2570 coalesce("initiative"."admitted", FALSE) = FALSE
2571 )
2572 GROUP BY
2573 "direct_interest_snapshot"."snapshot_id",
2574 "direct_interest_snapshot"."issue_id",
2575 "direct_interest_snapshot"."member_id",
2576 "direct_interest_snapshot"."weight";
2579 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
2580 SELECT
2581 "initiative"."issue_id",
2582 "initiative"."id" AS "initiative_id",
2583 "initiative"."admitted",
2584 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
2585 "remaining_harmonic_supporter_weight"."weight_den"
2586 FROM "remaining_harmonic_supporter_weight"
2587 JOIN "initiative"
2588 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
2589 AND "initiative"."harmonic_weight" ISNULL
2590 JOIN "direct_supporter_snapshot"
2591 ON "remaining_harmonic_supporter_weight"."snapshot_id" = "direct_supporter_snapshot"."snapshot_id"
2592 AND "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
2593 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
2594 AND (
2595 "direct_supporter_snapshot"."satisfied" = TRUE OR
2596 coalesce("initiative"."admitted", FALSE) = FALSE
2597 )
2598 GROUP BY
2599 "initiative"."issue_id",
2600 "initiative"."id",
2601 "initiative"."admitted",
2602 "remaining_harmonic_supporter_weight"."weight_den";
2605 DROP FUNCTION "create_population_snapshot"
2606 ( "issue_id_p" "issue"."id"%TYPE );
2609 DROP FUNCTION "weight_of_added_delegations_for_population_snapshot"
2610 ( "issue_id_p" "issue"."id"%TYPE,
2611 "member_id_p" "member"."id"%TYPE,
2612 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE );
2615 DROP FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2616 ( "issue_id_p" "issue"."id"%TYPE,
2617 "member_id_p" "member"."id"%TYPE,
2618 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE );
2621 CREATE FUNCTION "weight_of_added_delegations_for_snapshot"
2622 ( "snapshot_id_p" "snapshot"."id"%TYPE,
2623 "issue_id_p" "issue"."id"%TYPE,
2624 "member_id_p" "member"."id"%TYPE,
2625 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2626 RETURNS "direct_interest_snapshot"."weight"%TYPE
2627 LANGUAGE 'plpgsql' VOLATILE AS $$
2628 DECLARE
2629 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2630 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2631 "weight_v" INT4;
2632 "sub_weight_v" INT4;
2633 BEGIN
2634 PERFORM "require_transaction_isolation"();
2635 "weight_v" := 0;
2636 FOR "issue_delegation_row" IN
2637 SELECT * FROM "issue_delegation"
2638 WHERE "trustee_id" = "member_id_p"
2639 AND "issue_id" = "issue_id_p"
2640 LOOP
2641 IF NOT EXISTS (
2642 SELECT NULL FROM "direct_interest_snapshot"
2643 WHERE "snapshot_id" = "snapshot_id_p"
2644 AND "issue_id" = "issue_id_p"
2645 AND "member_id" = "issue_delegation_row"."truster_id"
2646 ) AND NOT EXISTS (
2647 SELECT NULL FROM "delegating_interest_snapshot"
2648 WHERE "snapshot_id" = "snapshot_id_p"
2649 AND "issue_id" = "issue_id_p"
2650 AND "member_id" = "issue_delegation_row"."truster_id"
2651 ) THEN
2652 "delegate_member_ids_v" :=
2653 "member_id_p" || "delegate_member_ids_p";
2654 INSERT INTO "delegating_interest_snapshot" (
2655 "snapshot_id",
2656 "issue_id",
2657 "member_id",
2658 "scope",
2659 "delegate_member_ids"
2660 ) VALUES (
2661 "snapshot_id_p",
2662 "issue_id_p",
2663 "issue_delegation_row"."truster_id",
2664 "issue_delegation_row"."scope",
2665 "delegate_member_ids_v"
2666 );
2667 "sub_weight_v" := 1 +
2668 "weight_of_added_delegations_for_snapshot"(
2669 "snapshot_id_p",
2670 "issue_id_p",
2671 "issue_delegation_row"."truster_id",
2672 "delegate_member_ids_v"
2673 );
2674 UPDATE "delegating_interest_snapshot"
2675 SET "weight" = "sub_weight_v"
2676 WHERE "snapshot_id" = "snapshot_id_p"
2677 AND "issue_id" = "issue_id_p"
2678 AND "member_id" = "issue_delegation_row"."truster_id";
2679 "weight_v" := "weight_v" + "sub_weight_v";
2680 END IF;
2681 END LOOP;
2682 RETURN "weight_v";
2683 END;
2684 $$;
2686 COMMENT ON FUNCTION "weight_of_added_delegations_for_snapshot"
2687 ( "snapshot"."id"%TYPE,
2688 "issue"."id"%TYPE,
2689 "member"."id"%TYPE,
2690 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2691 IS 'Helper function for "fill_snapshot" function';
2694 DROP FUNCTION "create_interest_snapshot"
2695 ( "issue_id_p" "issue"."id"%TYPE );
2698 DROP FUNCTION "create_snapshot"
2699 ( "issue_id_p" "issue"."id"%TYPE );
2702 CREATE FUNCTION "take_snapshot"
2703 ( "issue_id_p" "issue"."id"%TYPE,
2704 "area_id_p" "area"."id"%TYPE = NULL )
2705 RETURNS "snapshot"."id"%TYPE
2706 LANGUAGE 'plpgsql' VOLATILE AS $$
2707 DECLARE
2708 "area_id_v" "area"."id"%TYPE;
2709 "unit_id_v" "unit"."id"%TYPE;
2710 "snapshot_id_v" "snapshot"."id"%TYPE;
2711 "issue_id_v" "issue"."id"%TYPE;
2712 "member_id_v" "member"."id"%TYPE;
2713 BEGIN
2714 IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN
2715 RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL';
2716 END IF;
2717 PERFORM "require_transaction_isolation"();
2718 IF "issue_id_p" ISNULL THEN
2719 "area_id_v" := "area_id_p";
2720 ELSE
2721 SELECT "area_id" INTO "area_id_v"
2722 FROM "issue" WHERE "id" = "issue_id_p";
2723 END IF;
2724 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_p";
2725 INSERT INTO "snapshot" ("area_id", "issue_id")
2726 VALUES ("area_id_v", "issue_id_p")
2727 RETURNING "id" INTO "snapshot_id_v";
2728 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
2729 SELECT "snapshot_id_v", "member_id"
2730 FROM "unit_member" WHERE "unit_id" = "unit_id_v";
2731 UPDATE "snapshot" SET
2732 "population" = (
2733 SELECT count(1) FROM "snapshot_population"
2734 WHERE "snapshot_id" = "snapshot_id_v"
2735 ) WHERE "id" = "snapshot_id_v";
2736 FOR "issue_id_v" IN
2737 SELECT "id" FROM "issue"
2738 WHERE CASE WHEN "issue_id_p" ISNULL THEN
2739 "area_id" = "area_id_p" AND
2740 "state" = 'admission'
2741 ELSE
2742 "id" = "issue_id_p"
2743 END
2744 LOOP
2745 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
2746 VALUES ("snapshot_id_v", "issue_id_v");
2747 INSERT INTO "direct_interest_snapshot"
2748 ("snapshot_id", "issue_id", "member_id")
2749 SELECT
2750 "snapshot_id_v" AS "snapshot_id",
2751 "issue_id_v" AS "issue_id",
2752 "member"."id" AS "member_id"
2753 FROM "issue"
2754 JOIN "area" ON "issue"."area_id" = "area"."id"
2755 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2756 JOIN "member" ON "interest"."member_id" = "member"."id"
2757 JOIN "verified_privilege"
2758 ON "verified_privilege"."unit_id" = "area"."unit_id"
2759 AND "verified_privilege"."member_id" = "member"."id"
2760 WHERE "issue"."id" = "issue_id_v"
2761 AND "member"."active" AND "verified_privilege"."voting_right";
2762 FOR "member_id_v" IN
2763 SELECT "member_id" FROM "direct_interest_snapshot"
2764 WHERE "snapshot_id" = "snapshot_id_v"
2765 AND "issue_id" = "issue_id_v"
2766 LOOP
2767 UPDATE "direct_interest_snapshot" SET
2768 "weight" = 1 +
2769 "weight_of_added_delegations_for_snapshot"(
2770 "snapshot_id_v",
2771 "issue_id_v",
2772 "member_id_v",
2773 '{}'
2774 )
2775 WHERE "snapshot_id" = "snapshot_id_v"
2776 AND "issue_id" = "issue_id_v"
2777 AND "member_id" = "member_id_v";
2778 END LOOP;
2779 INSERT INTO "direct_supporter_snapshot"
2780 ( "snapshot_id", "issue_id", "initiative_id", "member_id",
2781 "draft_id", "informed", "satisfied" )
2782 SELECT
2783 "snapshot_id_v" AS "snapshot_id",
2784 "issue_id_v" AS "issue_id",
2785 "initiative"."id" AS "initiative_id",
2786 "supporter"."member_id" AS "member_id",
2787 "supporter"."draft_id" AS "draft_id",
2788 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2789 NOT EXISTS (
2790 SELECT NULL FROM "critical_opinion"
2791 WHERE "initiative_id" = "initiative"."id"
2792 AND "member_id" = "supporter"."member_id"
2793 ) AS "satisfied"
2794 FROM "initiative"
2795 JOIN "supporter"
2796 ON "supporter"."initiative_id" = "initiative"."id"
2797 JOIN "current_draft"
2798 ON "initiative"."id" = "current_draft"."initiative_id"
2799 JOIN "direct_interest_snapshot"
2800 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"
2801 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"
2802 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
2803 WHERE "initiative"."issue_id" = "issue_id_v";
2804 DELETE FROM "temporary_suggestion_counts";
2805 INSERT INTO "temporary_suggestion_counts"
2806 ( "id",
2807 "minus2_unfulfilled_count", "minus2_fulfilled_count",
2808 "minus1_unfulfilled_count", "minus1_fulfilled_count",
2809 "plus1_unfulfilled_count", "plus1_fulfilled_count",
2810 "plus2_unfulfilled_count", "plus2_fulfilled_count" )
2811 SELECT
2812 "suggestion"."id",
2813 ( SELECT coalesce(sum("di"."weight"), 0)
2814 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2815 ON "di"."snapshot_id" = "snapshot_id_v"
2816 AND "di"."issue_id" = "issue_id_v"
2817 AND "di"."member_id" = "opinion"."member_id"
2818 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2819 AND "opinion"."degree" = -2
2820 AND "opinion"."fulfilled" = FALSE
2821 ) AS "minus2_unfulfilled_count",
2822 ( SELECT coalesce(sum("di"."weight"), 0)
2823 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2824 ON "di"."snapshot_id" = "snapshot_id_v"
2825 AND "di"."issue_id" = "issue_id_v"
2826 AND "di"."member_id" = "opinion"."member_id"
2827 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2828 AND "opinion"."degree" = -2
2829 AND "opinion"."fulfilled" = TRUE
2830 ) AS "minus2_fulfilled_count",
2831 ( SELECT coalesce(sum("di"."weight"), 0)
2832 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2833 ON "di"."snapshot_id" = "snapshot_id_v"
2834 AND "di"."issue_id" = "issue_id_v"
2835 AND "di"."member_id" = "opinion"."member_id"
2836 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2837 AND "opinion"."degree" = -1
2838 AND "opinion"."fulfilled" = FALSE
2839 ) AS "minus1_unfulfilled_count",
2840 ( SELECT coalesce(sum("di"."weight"), 0)
2841 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2842 ON "di"."snapshot_id" = "snapshot_id_v"
2843 AND "di"."issue_id" = "issue_id_v"
2844 AND "di"."member_id" = "opinion"."member_id"
2845 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2846 AND "opinion"."degree" = -1
2847 AND "opinion"."fulfilled" = TRUE
2848 ) AS "minus1_fulfilled_count",
2849 ( SELECT coalesce(sum("di"."weight"), 0)
2850 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2851 ON "di"."snapshot_id" = "snapshot_id_v"
2852 AND "di"."issue_id" = "issue_id_v"
2853 AND "di"."member_id" = "opinion"."member_id"
2854 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2855 AND "opinion"."degree" = 1
2856 AND "opinion"."fulfilled" = FALSE
2857 ) AS "plus1_unfulfilled_count",
2858 ( SELECT coalesce(sum("di"."weight"), 0)
2859 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2860 ON "di"."snapshot_id" = "snapshot_id_v"
2861 AND "di"."issue_id" = "issue_id_v"
2862 AND "di"."member_id" = "opinion"."member_id"
2863 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2864 AND "opinion"."degree" = 1
2865 AND "opinion"."fulfilled" = TRUE
2866 ) AS "plus1_fulfilled_count",
2867 ( SELECT coalesce(sum("di"."weight"), 0)
2868 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2869 ON "di"."snapshot_id" = "snapshot_id_v"
2870 AND "di"."issue_id" = "issue_id_v"
2871 AND "di"."member_id" = "opinion"."member_id"
2872 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2873 AND "opinion"."degree" = 2
2874 AND "opinion"."fulfilled" = FALSE
2875 ) AS "plus2_unfulfilled_count",
2876 ( SELECT coalesce(sum("di"."weight"), 0)
2877 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
2878 ON "di"."snapshot_id" = "snapshot_id_v"
2879 AND "di"."issue_id" = "issue_id_v"
2880 AND "di"."member_id" = "opinion"."member_id"
2881 WHERE "opinion"."suggestion_id" = "suggestion"."id"
2882 AND "opinion"."degree" = 2
2883 AND "opinion"."fulfilled" = TRUE
2884 ) AS "plus2_fulfilled_count"
2885 FROM "suggestion" JOIN "initiative"
2886 ON "suggestion"."initiative_id" = "initiative"."id"
2887 WHERE "initiative"."issue_id" = "issue_id_v";
2888 END LOOP;
2889 RETURN "snapshot_id_v";
2890 END;
2891 $$;
2893 COMMENT ON FUNCTION "take_snapshot"
2894 ( "issue"."id"%TYPE,
2895 "area"."id"%TYPE )
2896 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.';
2899 DROP FUNCTION "set_snapshot_event"
2900 ( "issue_id_p" "issue"."id"%TYPE,
2901 "event_p" "snapshot_event" );
2904 CREATE FUNCTION "finish_snapshot"
2905 ( "issue_id_p" "issue"."id"%TYPE )
2906 RETURNS VOID
2907 LANGUAGE 'plpgsql' VOLATILE AS $$
2908 DECLARE
2909 "snapshot_id_v" "snapshot"."id"%TYPE;
2910 BEGIN
2911 -- NOTE: function does not require snapshot isolation but we don't call
2912 -- "dont_require_snapshot_isolation" here because this function is
2913 -- also invoked by "check_issue"
2914 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
2915 SELECT "id" INTO "snapshot_id_v" FROM "snapshot"
2916 ORDER BY "id" DESC LIMIT 1;
2917 UPDATE "issue" SET
2918 "calculated" = "snapshot"."calculated",
2919 "latest_snapshot_id" = "snapshot_id_v",
2920 "population" = "snapshot"."population"
2921 FROM "snapshot"
2922 WHERE "issue"."id" = "issue_id_p"
2923 AND "snapshot"."id" = "snapshot_id_v";
2924 UPDATE "initiative" SET
2925 "supporter_count" = (
2926 SELECT coalesce(sum("di"."weight"), 0)
2927 FROM "direct_interest_snapshot" AS "di"
2928 JOIN "direct_supporter_snapshot" AS "ds"
2929 ON "di"."member_id" = "ds"."member_id"
2930 WHERE "di"."snapshot_id" = "snapshot_id_v"
2931 AND "di"."issue_id" = "issue_id_p"
2932 AND "ds"."snapshot_id" = "snapshot_id_v"
2933 AND "ds"."initiative_id" = "initiative"."id"
2934 ),
2935 "informed_supporter_count" = (
2936 SELECT coalesce(sum("di"."weight"), 0)
2937 FROM "direct_interest_snapshot" AS "di"
2938 JOIN "direct_supporter_snapshot" AS "ds"
2939 ON "di"."member_id" = "ds"."member_id"
2940 WHERE "di"."snapshot_id" = "snapshot_id_v"
2941 AND "di"."issue_id" = "issue_id_p"
2942 AND "ds"."snapshot_id" = "snapshot_id_v"
2943 AND "ds"."initiative_id" = "initiative"."id"
2944 AND "ds"."informed"
2945 ),
2946 "satisfied_supporter_count" = (
2947 SELECT coalesce(sum("di"."weight"), 0)
2948 FROM "direct_interest_snapshot" AS "di"
2949 JOIN "direct_supporter_snapshot" AS "ds"
2950 ON "di"."member_id" = "ds"."member_id"
2951 WHERE "di"."snapshot_id" = "snapshot_id_v"
2952 AND "di"."issue_id" = "issue_id_p"
2953 AND "ds"."snapshot_id" = "snapshot_id_v"
2954 AND "ds"."initiative_id" = "initiative"."id"
2955 AND "ds"."satisfied"
2956 ),
2957 "satisfied_informed_supporter_count" = (
2958 SELECT coalesce(sum("di"."weight"), 0)
2959 FROM "direct_interest_snapshot" AS "di"
2960 JOIN "direct_supporter_snapshot" AS "ds"
2961 ON "di"."member_id" = "ds"."member_id"
2962 WHERE "di"."snapshot_id" = "snapshot_id_v"
2963 AND "di"."issue_id" = "issue_id_p"
2964 AND "ds"."snapshot_id" = "snapshot_id_v"
2965 AND "ds"."initiative_id" = "initiative"."id"
2966 AND "ds"."informed"
2967 AND "ds"."satisfied"
2968 )
2969 WHERE "issue_id" = "issue_id_p";
2970 UPDATE "suggestion" SET
2971 "minus2_unfulfilled_count" = "temp"."minus2_unfulfilled_count",
2972 "minus2_fulfilled_count" = "temp"."minus2_fulfilled_count",
2973 "minus1_unfulfilled_count" = "temp"."minus1_unfulfilled_count",
2974 "minus1_fulfilled_count" = "temp"."minus1_fulfilled_count",
2975 "plus1_unfulfilled_count" = "temp"."plus1_unfulfilled_count",
2976 "plus1_fulfilled_count" = "temp"."plus1_fulfilled_count",
2977 "plus2_unfulfilled_count" = "temp"."plus2_unfulfilled_count",
2978 "plus2_fulfilled_count" = "temp"."plus2_fulfilled_count"
2979 FROM "temporary_suggestion_counts" AS "temp", "initiative"
2980 WHERE "temp"."id" = "suggestion"."id"
2981 AND "initiative"."issue_id" = "issue_id_p"
2982 AND "suggestion"."initiative_id" = "initiative"."id";
2983 DELETE FROM "temporary_suggestion_counts";
2984 RETURN;
2985 END;
2986 $$;
2988 COMMENT ON FUNCTION "finish_snapshot"
2989 ( "issue"."id"%TYPE )
2990 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)';
2993 CREATE FUNCTION "issue_admission"
2994 ( "area_id_p" "area"."id"%TYPE )
2995 RETURNS BOOLEAN
2996 LANGUAGE 'plpgsql' VOLATILE AS $$
2997 DECLARE
2998 "issue_id_v" "issue"."id"%TYPE;
2999 BEGIN
3000 PERFORM "dont_require_transaction_isolation"();
3001 LOCK TABLE "snapshot" IN EXCLUSIVE MODE;
3002 UPDATE "area" SET "issue_quorum" = "view"."issue_quorum"
3003 FROM "area_quorum" AS "view"
3004 WHERE "area"."id" = "view"."area_id"
3005 AND "area"."id" = "area_id_p";
3006 SELECT "id" INTO "issue_id_v" FROM "issue_for_admission"
3007 WHERE "area_id" = "area_id_p";
3008 IF "issue_id_v" ISNULL THEN RETURN FALSE; END IF;
3009 UPDATE "issue" SET
3010 "admission_snapshot_id" = "latest_snapshot_id",
3011 "state" = 'discussion',
3012 "accepted" = now(),
3013 "phase_finished" = NULL
3014 WHERE "id" = "issue_id_v";
3015 RETURN TRUE;
3016 END;
3017 $$;
3019 COMMENT ON FUNCTION "issue_admission"
3020 ( "area"."id"%TYPE )
3021 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';
3024 CREATE OR REPLACE FUNCTION "check_issue"
3025 ( "issue_id_p" "issue"."id"%TYPE,
3026 "persist" "check_issue_persistence" )
3027 RETURNS "check_issue_persistence"
3028 LANGUAGE 'plpgsql' VOLATILE AS $$
3029 DECLARE
3030 "issue_row" "issue"%ROWTYPE;
3031 "last_calculated_v" "snapshot"."calculated"%TYPE;
3032 "policy_row" "policy"%ROWTYPE;
3033 "initiative_row" "initiative"%ROWTYPE;
3034 "state_v" "issue_state";
3035 BEGIN
3036 PERFORM "require_transaction_isolation"();
3037 IF "persist" ISNULL THEN
3038 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
3039 FOR UPDATE;
3040 SELECT "calculated" INTO "last_calculated_v"
3041 FROM "snapshot" JOIN "snapshot_issue"
3042 ON "snapshot"."id" = "snapshot_issue"."snapshot_id"
3043 WHERE "snapshot_issue"."issue_id" = "issue_id_p";
3044 IF "issue_row"."closed" NOTNULL THEN
3045 RETURN NULL;
3046 END IF;
3047 "persist"."state" := "issue_row"."state";
3048 IF
3049 ( "issue_row"."state" = 'admission' AND "last_calculated_v" >=
3050 "issue_row"."created" + "issue_row"."max_admission_time" ) OR
3051 ( "issue_row"."state" = 'discussion' AND now() >=
3052 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
3053 ( "issue_row"."state" = 'verification' AND now() >=
3054 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
3055 ( "issue_row"."state" = 'voting' AND now() >=
3056 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
3057 THEN
3058 "persist"."phase_finished" := TRUE;
3059 ELSE
3060 "persist"."phase_finished" := FALSE;
3061 END IF;
3062 IF
3063 NOT EXISTS (
3064 -- all initiatives are revoked
3065 SELECT NULL FROM "initiative"
3066 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3067 ) AND (
3068 -- and issue has not been accepted yet
3069 "persist"."state" = 'admission' OR
3070 -- or verification time has elapsed
3071 ( "persist"."state" = 'verification' AND
3072 "persist"."phase_finished" ) OR
3073 -- or no initiatives have been revoked lately
3074 NOT EXISTS (
3075 SELECT NULL FROM "initiative"
3076 WHERE "issue_id" = "issue_id_p"
3077 AND now() < "revoked" + "issue_row"."verification_time"
3078 )
3079 )
3080 THEN
3081 "persist"."issue_revoked" := TRUE;
3082 ELSE
3083 "persist"."issue_revoked" := FALSE;
3084 END IF;
3085 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
3086 UPDATE "issue" SET "phase_finished" = now()
3087 WHERE "id" = "issue_row"."id";
3088 RETURN "persist";
3089 ELSIF
3090 "persist"."state" IN ('admission', 'discussion', 'verification')
3091 THEN
3092 RETURN "persist";
3093 ELSE
3094 RETURN NULL;
3095 END IF;
3096 END IF;
3097 IF
3098 "persist"."state" IN ('admission', 'discussion', 'verification') AND
3099 coalesce("persist"."snapshot_created", FALSE) = FALSE
3100 THEN
3101 IF "persist"."state" != 'admission' THEN
3102 PERFORM "take_snapshot"("issue_id_p");
3103 PERFORM "finish_snapshot"("issue_id_p");
3104 END IF;
3105 "persist"."snapshot_created" = TRUE;
3106 IF "persist"."phase_finished" THEN
3107 IF "persist"."state" = 'admission' THEN
3108 UPDATE "issue" SET "admission_snapshot_id" = "latest_snapshot_id";
3109 ELSIF "persist"."state" = 'discussion' THEN
3110 UPDATE "issue" SET "half_freeze_snapshot_id" = "latest_snapshot_id";
3111 ELSIF "persist"."state" = 'verification' THEN
3112 UPDATE "issue" SET "full_freeze_snapshot_id" = "latest_snapshot_id";
3113 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3114 SELECT * INTO "policy_row" FROM "policy"
3115 WHERE "id" = "issue_row"."policy_id";
3116 FOR "initiative_row" IN
3117 SELECT * FROM "initiative"
3118 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3119 FOR UPDATE
3120 LOOP
3121 IF
3122 "initiative_row"."polling" OR (
3123 "initiative_row"."satisfied_supporter_count" >
3124 "policy_row"."initiative_quorum" AND
3125 "initiative_row"."satisfied_supporter_count" *
3126 "policy_row"."initiative_quorum_den" >=
3127 "issue_row"."population" * "policy_row"."initiative_quorum_num"
3128 )
3129 THEN
3130 UPDATE "initiative" SET "admitted" = TRUE
3131 WHERE "id" = "initiative_row"."id";
3132 ELSE
3133 UPDATE "initiative" SET "admitted" = FALSE
3134 WHERE "id" = "initiative_row"."id";
3135 END IF;
3136 END LOOP;
3137 END IF;
3138 END IF;
3139 RETURN "persist";
3140 END IF;
3141 IF
3142 "persist"."state" IN ('admission', 'discussion', 'verification') AND
3143 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
3144 THEN
3145 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
3146 "persist"."harmonic_weights_set" = TRUE;
3147 IF
3148 "persist"."phase_finished" OR
3149 "persist"."issue_revoked" OR
3150 "persist"."state" = 'admission'
3151 THEN
3152 RETURN "persist";
3153 ELSE
3154 RETURN NULL;
3155 END IF;
3156 END IF;
3157 IF "persist"."issue_revoked" THEN
3158 IF "persist"."state" = 'admission' THEN
3159 "state_v" := 'canceled_revoked_before_accepted';
3160 ELSIF "persist"."state" = 'discussion' THEN
3161 "state_v" := 'canceled_after_revocation_during_discussion';
3162 ELSIF "persist"."state" = 'verification' THEN
3163 "state_v" := 'canceled_after_revocation_during_verification';
3164 END IF;
3165 UPDATE "issue" SET
3166 "state" = "state_v",
3167 "closed" = "phase_finished",
3168 "phase_finished" = NULL
3169 WHERE "id" = "issue_id_p";
3170 RETURN NULL;
3171 END IF;
3172 IF "persist"."state" = 'admission' THEN
3173 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
3174 FOR UPDATE;
3175 IF "issue_row"."phase_finished" NOTNULL THEN
3176 UPDATE "issue" SET
3177 "state" = 'canceled_issue_not_accepted',
3178 "closed" = "phase_finished",
3179 "phase_finished" = NULL
3180 WHERE "id" = "issue_id_p";
3181 END IF;
3182 RETURN NULL;
3183 END IF;
3184 IF "persist"."phase_finished" THEN
3185 IF "persist"."state" = 'discussion' THEN
3186 UPDATE "issue" SET
3187 "state" = 'verification',
3188 "half_frozen" = "phase_finished",
3189 "phase_finished" = NULL
3190 WHERE "id" = "issue_id_p";
3191 RETURN NULL;
3192 END IF;
3193 IF "persist"."state" = 'verification' THEN
3194 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
3195 FOR UPDATE;
3196 SELECT * INTO "policy_row" FROM "policy"
3197 WHERE "id" = "issue_row"."policy_id";
3198 IF EXISTS (
3199 SELECT NULL FROM "initiative"
3200 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
3201 ) THEN
3202 UPDATE "issue" SET
3203 "state" = 'voting',
3204 "fully_frozen" = "phase_finished",
3205 "phase_finished" = NULL
3206 WHERE "id" = "issue_id_p";
3207 ELSE
3208 UPDATE "issue" SET
3209 "state" = 'canceled_no_initiative_admitted',
3210 "fully_frozen" = "phase_finished",
3211 "closed" = "phase_finished",
3212 "phase_finished" = NULL
3213 WHERE "id" = "issue_id_p";
3214 -- NOTE: The following DELETE statements have effect only when
3215 -- issue state has been manipulated
3216 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
3217 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
3218 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3219 END IF;
3220 RETURN NULL;
3221 END IF;
3222 IF "persist"."state" = 'voting' THEN
3223 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
3224 PERFORM "close_voting"("issue_id_p");
3225 "persist"."closed_voting" = TRUE;
3226 RETURN "persist";
3227 END IF;
3228 PERFORM "calculate_ranks"("issue_id_p");
3229 RETURN NULL;
3230 END IF;
3231 END IF;
3232 RAISE WARNING 'should not happen';
3233 RETURN NULL;
3234 END;
3235 $$;
3238 CREATE OR REPLACE FUNCTION "check_everything"()
3239 RETURNS VOID
3240 LANGUAGE 'plpgsql' VOLATILE AS $$
3241 DECLARE
3242 "area_id_v" "area"."id"%TYPE;
3243 "snapshot_id_v" "snapshot"."id"%TYPE;
3244 "issue_id_v" "issue"."id"%TYPE;
3245 "persist_v" "check_issue_persistence";
3246 BEGIN
3247 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
3248 DELETE FROM "expired_session";
3249 DELETE FROM "expired_token";
3250 DELETE FROM "expired_snapshot";
3251 PERFORM "check_activity"();
3252 PERFORM "calculate_member_counts"();
3253 FOR "area_id_v" IN SELECT "id" FROM "area_with_unaccepted_issues" LOOP
3254 SELECT "take_snapshot"(NULL, "area_id_v") INTO "snapshot_id_v";
3255 PERFORM "finish_snapshot"("issue_id") FROM "snapshot_issue"
3256 WHERE "snapshot_id" = "snapshot_id_v";
3257 LOOP
3258 EXIT WHEN "issue_admission"("area_id_v") = FALSE;
3259 END LOOP;
3260 END LOOP;
3261 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
3262 "persist_v" := NULL;
3263 LOOP
3264 "persist_v" := "check_issue"("issue_id_v", "persist_v");
3265 EXIT WHEN "persist_v" ISNULL;
3266 END LOOP;
3267 END LOOP;
3268 RETURN;
3269 END;
3270 $$;
3272 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';
3275 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
3276 RETURNS VOID
3277 LANGUAGE 'plpgsql' VOLATILE AS $$
3278 BEGIN
3279 IF EXISTS (
3280 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
3281 ) THEN
3282 -- override protection triggers:
3283 INSERT INTO "temporary_transaction_data" ("key", "value")
3284 VALUES ('override_protection_triggers', TRUE::TEXT);
3285 -- clean data:
3286 DELETE FROM "delegating_voter"
3287 WHERE "issue_id" = "issue_id_p";
3288 DELETE FROM "direct_voter"
3289 WHERE "issue_id" = "issue_id_p";
3290 DELETE FROM "delegating_interest_snapshot"
3291 WHERE "issue_id" = "issue_id_p";
3292 DELETE FROM "direct_interest_snapshot"
3293 WHERE "issue_id" = "issue_id_p";
3294 DELETE FROM "non_voter"
3295 WHERE "issue_id" = "issue_id_p";
3296 DELETE FROM "delegation"
3297 WHERE "issue_id" = "issue_id_p";
3298 DELETE FROM "supporter"
3299 USING "initiative" -- NOTE: due to missing index on issue_id
3300 WHERE "initiative"."issue_id" = "issue_id_p"
3301 AND "supporter"."initiative_id" = "initiative_id";
3302 -- mark issue as cleaned:
3303 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
3304 -- finish overriding protection triggers (avoids garbage):
3305 DELETE FROM "temporary_transaction_data"
3306 WHERE "key" = 'override_protection_triggers';
3307 END IF;
3308 RETURN;
3309 END;
3310 $$;
3313 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
3314 RETURNS VOID
3315 LANGUAGE 'plpgsql' VOLATILE AS $$
3316 BEGIN
3317 UPDATE "member" SET
3318 "last_login" = NULL,
3319 "last_delegation_check" = NULL,
3320 "login" = NULL,
3321 "password" = NULL,
3322 "authority" = NULL,
3323 "authority_uid" = NULL,
3324 "authority_login" = NULL,
3325 "deleted" = coalesce("deleted", now()),
3326 "locked" = TRUE,
3327 "active" = FALSE,
3328 "notify_email" = NULL,
3329 "notify_email_unconfirmed" = NULL,
3330 "notify_email_secret" = NULL,
3331 "notify_email_secret_expiry" = NULL,
3332 "notify_email_lock_expiry" = NULL,
3333 "disable_notifications" = TRUE,
3334 "notification_counter" = DEFAULT,
3335 "notification_sample_size" = 0,
3336 "notification_dow" = NULL,
3337 "notification_hour" = NULL,
3338 "notification_sent" = NULL,
3339 "login_recovery_expiry" = NULL,
3340 "password_reset_secret" = NULL,
3341 "password_reset_secret_expiry" = NULL,
3342 "location" = NULL
3343 WHERE "id" = "member_id_p";
3344 -- "text_search_data" is updated by triggers
3345 DELETE FROM "member_settings" WHERE "member_id" = "member_id_p";
3346 DELETE FROM "member_profile" WHERE "member_id" = "member_id_p";
3347 DELETE FROM "rendered_member_statement" WHERE "member_id" = "member_id_p";
3348 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
3349 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
3350 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
3351 DELETE FROM "session" WHERE "member_id" = "member_id_p";
3352 DELETE FROM "member_application" WHERE "member_id" = "member_id_p";
3353 DELETE FROM "token" WHERE "member_id" = "member_id_p";
3354 DELETE FROM "subscription" WHERE "member_id" = "member_id_p";
3355 DELETE FROM "ignored_area" WHERE "member_id" = "member_id_p";
3356 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
3357 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
3358 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
3359 DELETE FROM "direct_voter" USING "issue"
3360 WHERE "direct_voter"."issue_id" = "issue"."id"
3361 AND "issue"."closed" ISNULL
3362 AND "member_id" = "member_id_p";
3363 DELETE FROM "notification_initiative_sent" WHERE "member_id" = "member_id_p";
3364 RETURN;
3365 END;
3366 $$;
3369 CREATE OR REPLACE FUNCTION "delete_private_data"()
3370 RETURNS VOID
3371 LANGUAGE 'plpgsql' VOLATILE AS $$
3372 BEGIN
3373 DELETE FROM "temporary_transaction_data";
3374 DELETE FROM "temporary_suggestion_counts";
3375 DELETE FROM "member" WHERE "activated" ISNULL;
3376 UPDATE "member" SET
3377 "invite_code" = NULL,
3378 "invite_code_expiry" = NULL,
3379 "admin_comment" = NULL,
3380 "last_login" = NULL,
3381 "last_delegation_check" = NULL,
3382 "login" = NULL,
3383 "password" = NULL,
3384 "authority" = NULL,
3385 "authority_uid" = NULL,
3386 "authority_login" = NULL,
3387 "lang" = NULL,
3388 "notify_email" = NULL,
3389 "notify_email_unconfirmed" = NULL,
3390 "notify_email_secret" = NULL,
3391 "notify_email_secret_expiry" = NULL,
3392 "notify_email_lock_expiry" = NULL,
3393 "disable_notifications" = TRUE,
3394 "notification_counter" = DEFAULT,
3395 "notification_sample_size" = 0,
3396 "notification_dow" = NULL,
3397 "notification_hour" = NULL,
3398 "notification_sent" = NULL,
3399 "login_recovery_expiry" = NULL,
3400 "password_reset_secret" = NULL,
3401 "password_reset_secret_expiry" = NULL,
3402 "location" = NULL;
3403 -- "text_search_data" is updated by triggers
3404 DELETE FROM "member_settings";
3405 DELETE FROM "member_useterms";
3406 DELETE FROM "member_profile";
3407 DELETE FROM "rendered_member_statement";
3408 DELETE FROM "member_image";
3409 DELETE FROM "contact";
3410 DELETE FROM "ignored_member";
3411 DELETE FROM "session";
3412 DELETE FROM "system_application";
3413 DELETE FROM "system_application_redirect_uri";
3414 DELETE FROM "dynamic_application_scope";
3415 DELETE FROM "member_application";
3416 DELETE FROM "token";
3417 DELETE FROM "subscription";
3418 DELETE FROM "ignored_area";
3419 DELETE FROM "ignored_initiative";
3420 DELETE FROM "non_voter";
3421 DELETE FROM "direct_voter" USING "issue"
3422 WHERE "direct_voter"."issue_id" = "issue"."id"
3423 AND "issue"."closed" ISNULL;
3424 DELETE FROM "event_processed";
3425 DELETE FROM "notification_initiative_sent";
3426 DELETE FROM "newsletter";
3427 RETURN;
3428 END;
3429 $$;
3432 CREATE TEMPORARY TABLE "old_snapshot" AS
3433 SELECT "ordered".*, row_number() OVER () AS "snapshot_id"
3434 FROM (
3435 SELECT * FROM (
3436 SELECT
3437 "id" AS "issue_id",
3438 'end_of_admission'::"snapshot_event" AS "event",
3439 "accepted" AS "calculated"
3440 FROM "issue" WHERE "accepted" NOTNULL
3441 UNION ALL
3442 SELECT
3443 "id" AS "issue_id",
3444 'half_freeze'::"snapshot_event" AS "event",
3445 "half_frozen" AS "calculated"
3446 FROM "issue" WHERE "half_frozen" NOTNULL
3447 UNION ALL
3448 SELECT
3449 "id" AS "issue_id",
3450 'full_freeze'::"snapshot_event" AS "event",
3451 "fully_frozen" AS "calculated"
3452 FROM "issue" WHERE "fully_frozen" NOTNULL
3453 ) AS "unordered"
3454 ORDER BY "calculated", "issue_id", "event"
3455 ) AS "ordered";
3458 INSERT INTO "snapshot" ("id", "calculated", "population", "area_id", "issue_id")
3459 SELECT
3460 "old_snapshot"."snapshot_id" AS "id",
3461 "old_snapshot"."calculated",
3462 ( SELECT COALESCE(sum("weight"), 0)
3463 FROM "direct_population_snapshot" "dps"
3464 WHERE "dps"."issue_id" = "old_snapshot"."issue_id"
3465 AND "dps"."event" = "old_snapshot"."event"
3466 ) AS "population",
3467 "issue"."area_id" AS "area_id",
3468 "issue"."id" AS "issue_id"
3469 FROM "old_snapshot" JOIN "issue"
3470 ON "old_snapshot"."issue_id" = "issue"."id";
3473 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
3474 SELECT "id" AS "snapshot_id", "issue_id" FROM "snapshot";
3477 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
3478 SELECT
3479 "old_snapshot"."snapshot_id",
3480 "direct_population_snapshot"."member_id"
3481 FROM "old_snapshot" JOIN "direct_population_snapshot"
3482 ON "old_snapshot"."issue_id" = "direct_population_snapshot"."issue_id"
3483 AND "old_snapshot"."event" = "direct_population_snapshot"."event";
3485 INSERT INTO "snapshot_population" ("snapshot_id", "member_id")
3486 SELECT
3487 "old_snapshot"."snapshot_id",
3488 "delegating_population_snapshot"."member_id"
3489 FROM "old_snapshot" JOIN "delegating_population_snapshot"
3490 ON "old_snapshot"."issue_id" = "delegating_population_snapshot"."issue_id"
3491 AND "old_snapshot"."event" = "delegating_population_snapshot"."event";
3494 INSERT INTO "direct_interest_snapshot"
3495 ("snapshot_id", "issue_id", "member_id", "weight")
3496 SELECT
3497 "old_snapshot"."snapshot_id",
3498 "old_snapshot"."issue_id",
3499 "direct_interest_snapshot_old"."member_id",
3500 "direct_interest_snapshot_old"."weight"
3501 FROM "old_snapshot" JOIN "direct_interest_snapshot_old"
3502 ON "old_snapshot"."issue_id" = "direct_interest_snapshot_old"."issue_id"
3503 AND "old_snapshot"."event" = "direct_interest_snapshot_old"."event";
3505 INSERT INTO "delegating_interest_snapshot"
3506 ( "snapshot_id", "issue_id",
3507 "member_id", "weight", "scope", "delegate_member_ids" )
3508 SELECT
3509 "old_snapshot"."snapshot_id",
3510 "old_snapshot"."issue_id",
3511 "delegating_interest_snapshot_old"."member_id",
3512 "delegating_interest_snapshot_old"."weight",
3513 "delegating_interest_snapshot_old"."scope",
3514 "delegating_interest_snapshot_old"."delegate_member_ids"
3515 FROM "old_snapshot" JOIN "delegating_interest_snapshot_old"
3516 ON "old_snapshot"."issue_id" = "delegating_interest_snapshot_old"."issue_id"
3517 AND "old_snapshot"."event" = "delegating_interest_snapshot_old"."event";
3519 INSERT INTO "direct_supporter_snapshot"
3520 ( "snapshot_id", "issue_id",
3521 "initiative_id", "member_id", "draft_id", "informed", "satisfied" )
3522 SELECT
3523 "old_snapshot"."snapshot_id",
3524 "old_snapshot"."issue_id",
3525 "direct_supporter_snapshot_old"."initiative_id",
3526 "direct_supporter_snapshot_old"."member_id",
3527 "direct_supporter_snapshot_old"."draft_id",
3528 "direct_supporter_snapshot_old"."informed",
3529 "direct_supporter_snapshot_old"."satisfied"
3530 FROM "old_snapshot" JOIN "direct_supporter_snapshot_old"
3531 ON "old_snapshot"."issue_id" = "direct_supporter_snapshot_old"."issue_id"
3532 AND "old_snapshot"."event" = "direct_supporter_snapshot_old"."event";
3535 ALTER TABLE "issue" DISABLE TRIGGER USER; -- NOTE: required to modify table later
3537 UPDATE "issue" SET "latest_snapshot_id" = "snapshot"."id"
3538 FROM (
3539 SELECT DISTINCT ON ("issue_id") "issue_id", "id"
3540 FROM "snapshot" ORDER BY "issue_id", "id" DESC
3541 ) AS "snapshot"
3542 WHERE "snapshot"."issue_id" = "issue"."id";
3544 UPDATE "issue" SET "admission_snapshot_id" = "old_snapshot"."snapshot_id"
3545 FROM "old_snapshot"
3546 WHERE "old_snapshot"."issue_id" = "issue"."id"
3547 AND "old_snapshot"."event" = 'end_of_admission';
3549 UPDATE "issue" SET "half_freeze_snapshot_id" = "old_snapshot"."snapshot_id"
3550 FROM "old_snapshot"
3551 WHERE "old_snapshot"."issue_id" = "issue"."id"
3552 AND "old_snapshot"."event" = 'half_freeze';
3554 UPDATE "issue" SET "full_freeze_snapshot_id" = "old_snapshot"."snapshot_id"
3555 FROM "old_snapshot"
3556 WHERE "old_snapshot"."issue_id" = "issue"."id"
3557 AND "old_snapshot"."event" = 'full_freeze';
3559 ALTER TABLE "issue" ENABLE TRIGGER USER;
3562 DROP TABLE "old_snapshot";
3564 DROP TABLE "direct_supporter_snapshot_old";
3565 DROP TABLE "delegating_interest_snapshot_old";
3566 DROP TABLE "direct_interest_snapshot_old";
3567 DROP TABLE "delegating_population_snapshot";
3568 DROP TABLE "direct_population_snapshot";
3571 DROP VIEW "open_issue";
3574 ALTER TABLE "issue" DROP COLUMN "latest_snapshot_event";
3577 CREATE VIEW "open_issue" AS
3578 SELECT * FROM "issue" WHERE "closed" ISNULL;
3580 COMMENT ON VIEW "open_issue" IS 'All open issues';
3583 -- NOTE: create "issue_for_admission" view after altering table "issue"
3584 CREATE VIEW "issue_for_admission" AS
3585 SELECT DISTINCT ON ("issue"."area_id")
3586 "issue".*,
3587 max("initiative"."supporter_count") AS "max_supporter_count"
3588 FROM "issue"
3589 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
3590 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
3591 JOIN "area" ON "issue"."area_id" = "area"."id"
3592 WHERE "issue"."state" = 'admission'::"issue_state"
3593 AND now() >= "issue"."created" + "issue"."min_admission_time"
3594 AND "initiative"."supporter_count" >= "policy"."issue_quorum"
3595 AND "initiative"."supporter_count" * "policy"."issue_quorum_den" >=
3596 "issue"."population" * "policy"."issue_quorum_num"
3597 AND "initiative"."supporter_count" >= "area"."issue_quorum"
3598 AND "initiative"."revoked" ISNULL
3599 GROUP BY "issue"."id"
3600 ORDER BY "issue"."area_id", "max_supporter_count" DESC, "issue"."id";
3602 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';
3605 DROP TYPE "snapshot_event";
3608 ALTER TABLE "issue" ADD CONSTRAINT "snapshot_required" CHECK (
3609 ("half_frozen" ISNULL OR "half_freeze_snapshot_id" NOTNULL) AND
3610 ("fully_frozen" ISNULL OR "full_freeze_snapshot_id" NOTNULL) );
3613 CREATE OR REPLACE VIEW "unit_delegation" AS
3614 SELECT
3615 "unit"."id" AS "unit_id",
3616 "delegation"."id",
3617 "delegation"."truster_id",
3618 "delegation"."trustee_id",
3619 "delegation"."scope"
3620 FROM "unit"
3621 JOIN "delegation"
3622 ON "delegation"."unit_id" = "unit"."id"
3623 JOIN "member"
3624 ON "delegation"."truster_id" = "member"."id"
3625 JOIN "verified_privilege"
3626 ON "delegation"."unit_id" = "verified_privilege"."unit_id"
3627 AND "delegation"."truster_id" = "verified_privilege"."member_id"
3628 WHERE "member"."active" AND "verified_privilege"."voting_right";
3630 CREATE OR REPLACE VIEW "area_delegation" AS
3631 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
3632 "area"."id" AS "area_id",
3633 "delegation"."id",
3634 "delegation"."truster_id",
3635 "delegation"."trustee_id",
3636 "delegation"."scope"
3637 FROM "area"
3638 JOIN "delegation"
3639 ON "delegation"."unit_id" = "area"."unit_id"
3640 OR "delegation"."area_id" = "area"."id"
3641 JOIN "member"
3642 ON "delegation"."truster_id" = "member"."id"
3643 JOIN "verified_privilege"
3644 ON "area"."unit_id" = "verified_privilege"."unit_id"
3645 AND "delegation"."truster_id" = "verified_privilege"."member_id"
3646 WHERE "member"."active" AND "verified_privilege"."voting_right"
3647 ORDER BY
3648 "area"."id",
3649 "delegation"."truster_id",
3650 "delegation"."scope" DESC;
3652 CREATE OR REPLACE VIEW "event_for_notification" AS
3653 SELECT
3654 "member"."id" AS "recipient_id",
3655 "event".*
3656 FROM "member" CROSS JOIN "event"
3657 JOIN "issue" ON "issue"."id" = "event"."issue_id"
3658 JOIN "area" ON "area"."id" = "issue"."area_id"
3659 LEFT JOIN "verified_privilege" ON
3660 "verified_privilege"."member_id" = "member"."id" AND
3661 "verified_privilege"."unit_id" = "area"."unit_id" AND
3662 "verified_privilege"."voting_right" = TRUE
3663 LEFT JOIN "subscription" ON
3664 "subscription"."member_id" = "member"."id" AND
3665 "subscription"."unit_id" = "area"."unit_id"
3666 LEFT JOIN "ignored_area" ON
3667 "ignored_area"."member_id" = "member"."id" AND
3668 "ignored_area"."area_id" = "issue"."area_id"
3669 LEFT JOIN "interest" ON
3670 "interest"."member_id" = "member"."id" AND
3671 "interest"."issue_id" = "event"."issue_id"
3672 LEFT JOIN "supporter" ON
3673 "supporter"."member_id" = "member"."id" AND
3674 "supporter"."initiative_id" = "event"."initiative_id"
3675 WHERE (
3676 "verified_privilege"."member_id" NOTNULL OR
3677 "subscription"."member_id" NOTNULL )
3678 AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
3679 AND (
3680 "event"."event" = 'issue_state_changed'::"event_type" OR
3681 ( "event"."event" = 'initiative_revoked'::"event_type" AND
3682 "supporter"."member_id" NOTNULL ) );
3684 CREATE OR REPLACE FUNCTION "featured_initiative"
3685 ( "recipient_id_p" "member"."id"%TYPE,
3686 "area_id_p" "area"."id"%TYPE )
3687 RETURNS SETOF "initiative"."id"%TYPE
3688 LANGUAGE 'plpgsql' STABLE AS $$
3689 DECLARE
3690 "counter_v" "member"."notification_counter"%TYPE;
3691 "sample_size_v" "member"."notification_sample_size"%TYPE;
3692 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
3693 "match_v" BOOLEAN;
3694 "member_id_v" "member"."id"%TYPE;
3695 "seed_v" TEXT;
3696 "initiative_id_v" "initiative"."id"%TYPE;
3697 BEGIN
3698 SELECT "notification_counter", "notification_sample_size"
3699 INTO "counter_v", "sample_size_v"
3700 FROM "member" WHERE "id" = "recipient_id_p";
3701 IF COALESCE("sample_size_v" <= 0, TRUE) THEN
3702 RETURN;
3703 END IF;
3704 "initiative_id_ary" := '{}';
3705 LOOP
3706 "match_v" := FALSE;
3707 FOR "member_id_v", "seed_v" IN
3708 SELECT * FROM (
3709 SELECT DISTINCT
3710 "supporter"."member_id",
3711 md5(
3712 "recipient_id_p" || '-' ||
3713 "counter_v" || '-' ||
3714 "area_id_p" || '-' ||
3715 "supporter"."member_id"
3716 ) AS "seed"
3717 FROM "supporter"
3718 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
3719 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
3720 WHERE "supporter"."member_id" != "recipient_id_p"
3721 AND "issue"."area_id" = "area_id_p"
3722 AND "issue"."state" IN ('admission', 'discussion', 'verification')
3723 ) AS "subquery"
3724 ORDER BY "seed"
3725 LOOP
3726 SELECT "initiative"."id" INTO "initiative_id_v"
3727 FROM "initiative"
3728 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
3729 JOIN "area" ON "area"."id" = "issue"."area_id"
3730 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
3731 LEFT JOIN "supporter" AS "self_support" ON
3732 "self_support"."initiative_id" = "initiative"."id" AND
3733 "self_support"."member_id" = "recipient_id_p"
3734 LEFT JOIN "verified_privilege" ON
3735 "verified_privilege"."member_id" = "recipient_id_p" AND
3736 "verified_privilege"."unit_id" = "area"."unit_id" AND
3737 "verified_privilege"."voting_right" = TRUE
3738 LEFT JOIN "subscription" ON
3739 "subscription"."member_id" = "recipient_id_p" AND
3740 "subscription"."unit_id" = "area"."unit_id"
3741 LEFT JOIN "ignored_initiative" ON
3742 "ignored_initiative"."member_id" = "recipient_id_p" AND
3743 "ignored_initiative"."initiative_id" = "initiative"."id"
3744 WHERE "supporter"."member_id" = "member_id_v"
3745 AND "issue"."area_id" = "area_id_p"
3746 AND "issue"."state" IN ('admission', 'discussion', 'verification')
3747 AND "initiative"."revoked" ISNULL
3748 AND "self_support"."member_id" ISNULL
3749 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
3750 AND (
3751 "verified_privilege"."member_id" NOTNULL OR
3752 "subscription"."member_id" NOTNULL )
3753 AND "ignored_initiative"."member_id" ISNULL
3754 AND NOT EXISTS (
3755 SELECT NULL FROM "draft"
3756 JOIN "ignored_member" ON
3757 "ignored_member"."member_id" = "recipient_id_p" AND
3758 "ignored_member"."other_member_id" = "draft"."author_id"
3759 WHERE "draft"."initiative_id" = "initiative"."id"
3760 )
3761 ORDER BY md5("seed_v" || '-' || "initiative"."id")
3762 LIMIT 1;
3763 IF FOUND THEN
3764 "match_v" := TRUE;
3765 RETURN NEXT "initiative_id_v";
3766 IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
3767 RETURN;
3768 END IF;
3769 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
3770 END IF;
3771 END LOOP;
3772 EXIT WHEN NOT "match_v";
3773 END LOOP;
3774 RETURN;
3775 END;
3776 $$;
3778 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
3779 RETURNS VOID
3780 LANGUAGE 'plpgsql' VOLATILE AS $$
3781 DECLARE
3782 "area_id_v" "area"."id"%TYPE;
3783 "unit_id_v" "unit"."id"%TYPE;
3784 "member_id_v" "member"."id"%TYPE;
3785 BEGIN
3786 PERFORM "require_transaction_isolation"();
3787 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
3788 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
3789 -- override protection triggers:
3790 INSERT INTO "temporary_transaction_data" ("key", "value")
3791 VALUES ('override_protection_triggers', TRUE::TEXT);
3792 -- delete timestamp of voting comment:
3793 UPDATE "direct_voter" SET "comment_changed" = NULL
3794 WHERE "issue_id" = "issue_id_p";
3795 -- delete delegating votes (in cases of manual reset of issue state):
3796 DELETE FROM "delegating_voter"
3797 WHERE "issue_id" = "issue_id_p";
3798 -- delete votes from non-privileged voters:
3799 DELETE FROM "direct_voter"
3800 USING (
3801 SELECT
3802 "direct_voter"."member_id"
3803 FROM "direct_voter"
3804 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
3805 LEFT JOIN "verified_privilege"
3806 ON "verified_privilege"."unit_id" = "unit_id_v"
3807 AND "verified_privilege"."member_id" = "direct_voter"."member_id"
3808 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
3809 "member"."active" = FALSE OR
3810 "verified_privilege"."voting_right" ISNULL OR
3811 "verified_privilege"."voting_right" = FALSE
3812 )
3813 ) AS "subquery"
3814 WHERE "direct_voter"."issue_id" = "issue_id_p"
3815 AND "direct_voter"."member_id" = "subquery"."member_id";
3816 -- consider delegations:
3817 UPDATE "direct_voter" SET "weight" = 1
3818 WHERE "issue_id" = "issue_id_p";
3819 PERFORM "add_vote_delegations"("issue_id_p");
3820 -- mark first preferences:
3821 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
3822 FROM (
3823 SELECT
3824 "vote"."initiative_id",
3825 "vote"."member_id",
3826 CASE WHEN "vote"."grade" > 0 THEN
3827 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
3828 ELSE NULL
3829 END AS "first_preference"
3830 FROM "vote"
3831 JOIN "initiative" -- NOTE: due to missing index on issue_id
3832 ON "vote"."issue_id" = "initiative"."issue_id"
3833 JOIN "vote" AS "agg"
3834 ON "initiative"."id" = "agg"."initiative_id"
3835 AND "vote"."member_id" = "agg"."member_id"
3836 GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
3837 ) AS "subquery"
3838 WHERE "vote"."issue_id" = "issue_id_p"
3839 AND "vote"."initiative_id" = "subquery"."initiative_id"
3840 AND "vote"."member_id" = "subquery"."member_id";
3841 -- finish overriding protection triggers (avoids garbage):
3842 DELETE FROM "temporary_transaction_data"
3843 WHERE "key" = 'override_protection_triggers';
3844 -- materialize battle_view:
3845 -- NOTE: "closed" column of issue must be set at this point
3846 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3847 INSERT INTO "battle" (
3848 "issue_id",
3849 "winning_initiative_id", "losing_initiative_id",
3850 "count"
3851 ) SELECT
3852 "issue_id",
3853 "winning_initiative_id", "losing_initiative_id",
3854 "count"
3855 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
3856 -- set voter count:
3857 UPDATE "issue" SET
3858 "voter_count" = (
3859 SELECT coalesce(sum("weight"), 0)
3860 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
3861 )
3862 WHERE "id" = "issue_id_p";
3863 -- copy "positive_votes" and "negative_votes" from "battle" table:
3864 -- NOTE: "first_preference_votes" is set to a default of 0 at this step
3865 UPDATE "initiative" SET
3866 "first_preference_votes" = 0,
3867 "positive_votes" = "battle_win"."count",
3868 "negative_votes" = "battle_lose"."count"
3869 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
3870 WHERE
3871 "battle_win"."issue_id" = "issue_id_p" AND
3872 "battle_win"."winning_initiative_id" = "initiative"."id" AND
3873 "battle_win"."losing_initiative_id" ISNULL AND
3874 "battle_lose"."issue_id" = "issue_id_p" AND
3875 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
3876 "battle_lose"."winning_initiative_id" ISNULL;
3877 -- calculate "first_preference_votes":
3878 -- NOTE: will only set values not equal to zero
3879 UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum"
3880 FROM (
3881 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
3882 FROM "vote" JOIN "direct_voter"
3883 ON "vote"."issue_id" = "direct_voter"."issue_id"
3884 AND "vote"."member_id" = "direct_voter"."member_id"
3885 WHERE "vote"."first_preference"
3886 GROUP BY "vote"."initiative_id"
3887 ) AS "subquery"
3888 WHERE "initiative"."issue_id" = "issue_id_p"
3889 AND "initiative"."admitted"
3890 AND "initiative"."id" = "subquery"."initiative_id";
3891 END;
3892 $$;
3895 COMMIT;