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