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