liquid_feedback_core

view update/core-update.v3.1.0-v3.2.0.sql @ 593:e7f772ca0621

Removed an index on table "initiative"
author jbe
date Sat Dec 08 01:37:23 2018 +0100 (20 months ago)
parents ab05fb3bf974
children
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('3.2.0', 3, 2, 0))
5 AS "subquery"("string", "major", "minor", "revision");
7 ALTER TABLE "member" ADD COLUMN "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE;
8 ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 1;
9 ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4 NOT NULL DEFAULT 3;
10 ALTER TABLE "member" ADD COLUMN "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6);
11 ALTER TABLE "member" ADD COLUMN "notification_hour" INT4 DEFAULT floor(random() * 24) CHECK ("notification_hour" BETWEEN 0 AND 23);
12 ALTER TABLE "member" ADD COLUMN "notification_sent" TIMESTAMP;
13 ALTER TABLE "member" ADD
14 CONSTRAINT "notification_dow_requires_notification_hour"
15 CHECK ("notification_dow" ISNULL OR "notification_hour" NOTNULL);
17 UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none'::"notify_level";
19 DROP VIEW "selected_event_seen_by_member";
20 DROP VIEW "event_seen_by_member";
22 ALTER TABLE "member" DROP COLUMN "notify_level";
24 DROP TYPE "notify_level";
26 COMMENT ON COLUMN "member"."disable_notifications" IS 'TRUE if member does not want to receive notifications';
27 COMMENT ON COLUMN "member"."notification_counter" IS 'Sequential number of next scheduled notification message (used as a seed for pseudo-random initiative selection algorithm)';
28 COMMENT ON COLUMN "member"."notification_sample_size" IS 'Number of featured initiatives per issue in scheduled notification messages';
29 COMMENT ON COLUMN "member"."notification_dow" IS 'Day of week for scheduled notifications (NULL to receive a daily digest)';
30 COMMENT ON COLUMN "member"."notification_hour" IS 'Time of day when scheduled notifications are sent out';
31 COMMENT ON COLUMN "member"."notification_sent" IS 'Timestamp of last scheduled notification mail that has been sent out';
33 ALTER TABLE "rendered_member_statement" ALTER COLUMN "member_id" SET DATA TYPE INT4;
35 DROP VIEW "expired_session";
37 ALTER TABLE "session" ALTER COLUMN "member_id" SET DATA TYPE INT4;
39 CREATE VIEW "expired_session" AS
40 SELECT * FROM "session" WHERE now() > "expiry";
41 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
42 DELETE FROM "session" WHERE "ident" = OLD."ident";
44 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
45 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
47 CREATE TABLE "subscription" (
48 PRIMARY KEY ("member_id", "unit_id"),
49 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
50 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
51 CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id");
53 COMMENT ON TABLE "subscription" IS 'An entry in this table denotes that the member wishes to receive notifications regardless of his/her privileges in the given unit';
55 CREATE TABLE "ignored_area" (
56 PRIMARY KEY ("member_id", "area_id"),
57 "member_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
58 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
59 CREATE INDEX "ignored_area_area_id_idx" ON "ignored_area" ("area_id");
61 COMMENT ON TABLE "ignored_area" IS 'An entry in this table denotes that the member does not wish to receive notifications for the given subject area unless he/she declared interested in a particular issue';
63 ALTER TABLE "ignored_initiative" DROP CONSTRAINT "ignored_initiative_pkey";
64 DROP INDEX "ignored_initiative_member_id_idx";
66 ALTER TABLE "ignored_initiative" ADD PRIMARY KEY ("member_id", "initiative_id");
67 CREATE INDEX "ignored_initiative_initiative_id_idx" ON "ignored_initiative" ("initiative_id");
69 COMMENT ON TABLE "ignored_initiative" IS 'An entry in this table denotes that the member does not wish to receive notifications for the given initiative';
71 ALTER TABLE "notification_sent" RENAME TO "notification_event_sent";
72 ALTER INDEX "notification_sent_singleton_idx" RENAME TO "notification_event_sent_singleton_idx";
74 CREATE TABLE "notification_initiative_sent" (
75 PRIMARY KEY ("member_id", "initiative_id"),
76 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
77 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
78 "last_draft_id" INT8 NOT NULL,
79 "last_suggestion_id" INT8 );
80 CREATE INDEX "notification_initiative_sent_initiative_idx" ON "notification_initiative_sent" ("initiative_id");
82 COMMENT ON TABLE "notification_initiative_sent" IS 'Information which initiatives have been promoted to a member in a scheduled notification mail';
84 COMMENT ON COLUMN "notification_initiative_sent"."last_draft_id" IS 'Current (i.e. last) draft_id when initiative had been promoted';
85 COMMENT ON COLUMN "notification_initiative_sent"."last_suggestion_id" IS 'Current (i.e. last) draft_id when initiative had been promoted';
87 CREATE TABLE "newsletter" (
88 "id" SERIAL4 PRIMARY KEY,
89 "published" TIMESTAMPTZ NOT NULL,
90 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
91 "include_all_members" BOOLEAN NOT NULL,
92 "sent" TIMESTAMPTZ,
93 "subject" TEXT NOT NULL,
94 "content" TEXT NOT NULL );
95 CREATE INDEX "newsletter_unit_id_idx" ON "newsletter" ("unit_id", "published");
96 CREATE INDEX "newsletter_all_units_published_idx" ON "newsletter" ("published") WHERE "unit_id" ISNULL;
97 CREATE INDEX "newsletter_published_idx" ON "newsletter" ("published");
99 COMMENT ON TABLE "newsletter" IS 'Contains newsletters created by administrators to be sent out and for further reference';
101 COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter is to be sent out (and made available in the frontend)';
102 COMMENT ON COLUMN "newsletter"."unit_id" IS 'If set, only members with voting right in the given unit are considered to be recipients';
103 COMMENT ON COLUMN "newsletter"."include_all_members" IS 'TRUE = include all members regardless of their ''disable_notifications'' setting';
104 COMMENT ON COLUMN "newsletter"."sent" IS 'Timestamp when the newsletter has been mailed out';
105 COMMENT ON COLUMN "newsletter"."subject" IS 'Subject line (e.g. to be used for the email)';
106 COMMENT ON COLUMN "newsletter"."content" IS 'Plain text content of the newsletter';
108 CREATE OR REPLACE FUNCTION "issue_requires_first_initiative_trigger"()
109 RETURNS TRIGGER
110 LANGUAGE 'plpgsql' VOLATILE AS $$
111 BEGIN
112 IF NOT EXISTS (
113 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
114 ) THEN
115 RAISE EXCEPTION 'Cannot create issue without an initial initiative.' USING
116 ERRCODE = 'integrity_constraint_violation',
117 HINT = 'Create issue, initiative, and draft within the same transaction.';
118 END IF;
119 RETURN NULL;
120 END;
121 $$;
123 CREATE OR REPLACE FUNCTION "initiative_requires_first_draft_trigger"()
124 RETURNS TRIGGER
125 LANGUAGE 'plpgsql' VOLATILE AS $$
126 BEGIN
127 IF NOT EXISTS (
128 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
129 ) THEN
130 RAISE EXCEPTION 'Cannot create initiative without an initial draft.' USING
131 ERRCODE = 'integrity_constraint_violation',
132 HINT = 'Create issue, initiative and draft within the same transaction.';
133 END IF;
134 RETURN NULL;
135 END;
136 $$;
138 CREATE OR REPLACE FUNCTION "suggestion_requires_first_opinion_trigger"()
139 RETURNS TRIGGER
140 LANGUAGE 'plpgsql' VOLATILE AS $$
141 BEGIN
142 IF NOT EXISTS (
143 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
144 ) THEN
145 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.' USING
146 ERRCODE = 'integrity_constraint_violation',
147 HINT = 'Create suggestion and opinion within the same transaction.';
148 END IF;
149 RETURN NULL;
150 END;
151 $$;
153 CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"()
154 RETURNS TRIGGER
155 LANGUAGE 'plpgsql' VOLATILE AS $$
156 DECLARE
157 "issue_id_v" "issue"."id"%TYPE;
158 "issue_row" "issue"%ROWTYPE;
159 BEGIN
160 IF EXISTS (
161 SELECT NULL FROM "temporary_transaction_data"
162 WHERE "txid" = txid_current()
163 AND "key" = 'override_protection_triggers'
164 AND "value" = TRUE::TEXT
165 ) THEN
166 RETURN NULL;
167 END IF;
168 IF TG_OP = 'DELETE' THEN
169 "issue_id_v" := OLD."issue_id";
170 ELSE
171 "issue_id_v" := NEW."issue_id";
172 END IF;
173 SELECT INTO "issue_row" * FROM "issue"
174 WHERE "id" = "issue_id_v" FOR SHARE;
175 IF (
176 "issue_row"."closed" NOTNULL OR (
177 "issue_row"."state" = 'voting' AND
178 "issue_row"."phase_finished" NOTNULL
179 )
180 ) THEN
181 IF
182 TG_RELID = 'direct_voter'::regclass AND
183 TG_OP = 'UPDATE'
184 THEN
185 IF
186 OLD."issue_id" = NEW."issue_id" AND
187 OLD."member_id" = NEW."member_id" AND
188 OLD."weight" = NEW."weight"
189 THEN
190 RETURN NULL; -- allows changing of voter comment
191 END IF;
192 END IF;
193 RAISE EXCEPTION 'Tried to modify data after voting has been closed.' USING
194 ERRCODE = 'integrity_constraint_violation';
195 END IF;
196 RETURN NULL;
197 END;
198 $$;
200 CREATE VIEW "event_for_notification" AS
201 SELECT
202 "member"."id" AS "recipient_id",
203 "event".*
204 FROM "member" CROSS JOIN "event"
205 JOIN "issue" ON "issue"."id" = "event"."issue_id"
206 JOIN "area" ON "area"."id" = "issue"."area_id"
207 LEFT JOIN "privilege" ON
208 "privilege"."member_id" = "member"."id" AND
209 "privilege"."unit_id" = "area"."unit_id" AND
210 "privilege"."voting_right" = TRUE
211 LEFT JOIN "subscription" ON
212 "subscription"."member_id" = "member"."id" AND
213 "subscription"."unit_id" = "area"."unit_id"
214 LEFT JOIN "ignored_area" ON
215 "ignored_area"."member_id" = "member"."id" AND
216 "ignored_area"."area_id" = "issue"."area_id"
217 LEFT JOIN "interest" ON
218 "interest"."member_id" = "member"."id" AND
219 "interest"."issue_id" = "event"."issue_id"
220 LEFT JOIN "supporter" ON
221 "supporter"."member_id" = "member"."id" AND
222 "supporter"."initiative_id" = "event"."initiative_id"
223 WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL)
224 AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
225 AND (
226 "event"."event" = 'issue_state_changed'::"event_type" OR
227 ( "event"."event" = 'initiative_revoked'::"event_type" AND
228 "supporter"."member_id" NOTNULL ) );
230 COMMENT ON VIEW "event_for_notification" IS 'Entries of the "event" table which are of interest for a particular notification mail recipient';
232 COMMENT ON COLUMN "event_for_notification"."recipient_id" IS 'member_id of the recipient of a notification mail';
234 CREATE VIEW "updated_initiative" AS
235 SELECT
236 "supporter"."member_id" AS "recipient_id",
237 FALSE AS "featured",
238 "supporter"."initiative_id"
239 FROM "supporter"
240 JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id"
241 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
242 LEFT JOIN "notification_initiative_sent" AS "sent" ON
243 "sent"."member_id" = "supporter"."member_id" AND
244 "sent"."initiative_id" = "supporter"."initiative_id"
245 LEFT JOIN "ignored_initiative" ON
246 "ignored_initiative"."member_id" = "supporter"."member_id" AND
247 "ignored_initiative"."initiative_id" = "supporter"."initiative_id"
248 WHERE "issue"."state" IN ('admission', 'discussion')
249 AND "initiative"."revoked" ISNULL
250 AND "ignored_initiative"."member_id" ISNULL
251 AND (
252 EXISTS (
253 SELECT NULL FROM "draft"
254 LEFT JOIN "ignored_member" ON
255 "ignored_member"."member_id" = "supporter"."member_id" AND
256 "ignored_member"."other_member_id" = "draft"."author_id"
257 WHERE "draft"."initiative_id" = "supporter"."initiative_id"
258 AND "draft"."id" > "supporter"."draft_id"
259 AND "ignored_member"."member_id" ISNULL
260 ) OR EXISTS (
261 SELECT NULL FROM "suggestion"
262 LEFT JOIN "opinion" ON
263 "opinion"."member_id" = "supporter"."member_id" AND
264 "opinion"."suggestion_id" = "suggestion"."id"
265 LEFT JOIN "ignored_member" ON
266 "ignored_member"."member_id" = "supporter"."member_id" AND
267 "ignored_member"."other_member_id" = "suggestion"."author_id"
268 WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"
269 AND "opinion"."member_id" ISNULL
270 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
271 AND "ignored_member"."member_id" ISNULL
272 )
273 );
275 COMMENT ON VIEW "updated_initiative" IS 'Helper view for view "updated_or_featured_initiative"';
277 CREATE FUNCTION "featured_initiative"
278 ( "recipient_id_p" "member"."id"%TYPE,
279 "area_id_p" "area"."id"%TYPE )
280 RETURNS SETOF "initiative"."id"%TYPE
281 LANGUAGE 'plpgsql' STABLE AS $$
282 DECLARE
283 "counter_v" "member"."notification_counter"%TYPE;
284 "sample_size_v" "member"."notification_sample_size"%TYPE;
285 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
286 "match_v" BOOLEAN;
287 "member_id_v" "member"."id"%TYPE;
288 "seed_v" TEXT;
289 "initiative_id_v" "initiative"."id"%TYPE;
290 BEGIN
291 SELECT "notification_counter", "notification_sample_size"
292 INTO "counter_v", "sample_size_v"
293 FROM "member" WHERE "id" = "recipient_id_p";
294 "initiative_id_ary" := '{}';
295 LOOP
296 "match_v" := FALSE;
297 FOR "member_id_v", "seed_v" IN
298 SELECT * FROM (
299 SELECT DISTINCT
300 "supporter"."member_id",
301 md5(
302 "recipient_id_p" || '-' ||
303 "counter_v" || '-' ||
304 "area_id_p" || '-' ||
305 "supporter"."member_id"
306 ) AS "seed"
307 FROM "supporter"
308 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
309 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
310 WHERE "supporter"."member_id" != "recipient_id_p"
311 AND "issue"."area_id" = "area_id_p"
312 AND "issue"."state" IN ('admission', 'discussion', 'verification')
313 ) AS "subquery"
314 ORDER BY "seed"
315 LOOP
316 SELECT "initiative"."id" INTO "initiative_id_v"
317 FROM "initiative"
318 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
319 JOIN "area" ON "area"."id" = "issue"."area_id"
320 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
321 LEFT JOIN "supporter" AS "self_support" ON
322 "self_support"."initiative_id" = "initiative"."id" AND
323 "self_support"."member_id" = "recipient_id_p"
324 LEFT JOIN "privilege" ON
325 "privilege"."member_id" = "recipient_id_p" AND
326 "privilege"."unit_id" = "area"."unit_id" AND
327 "privilege"."voting_right" = TRUE
328 LEFT JOIN "subscription" ON
329 "subscription"."member_id" = "recipient_id_p" AND
330 "subscription"."unit_id" = "area"."unit_id"
331 LEFT JOIN "ignored_initiative" ON
332 "ignored_initiative"."member_id" = "recipient_id_p" AND
333 "ignored_initiative"."initiative_id" = "initiative"."id"
334 WHERE "supporter"."member_id" = "member_id_v"
335 AND "issue"."area_id" = "area_id_p"
336 AND "issue"."state" IN ('admission', 'discussion', 'verification')
337 AND "initiative"."revoked" ISNULL
338 AND "self_support"."member_id" ISNULL
339 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
340 AND (
341 "privilege"."member_id" NOTNULL OR
342 "subscription"."member_id" NOTNULL )
343 AND "ignored_initiative"."member_id" ISNULL
344 AND NOT EXISTS (
345 SELECT NULL FROM "draft"
346 JOIN "ignored_member" ON
347 "ignored_member"."member_id" = "recipient_id_p" AND
348 "ignored_member"."other_member_id" = "draft"."author_id"
349 WHERE "draft"."initiative_id" = "initiative"."id"
350 )
351 ORDER BY md5("seed_v" || '-' || "initiative"."id")
352 LIMIT 1;
353 IF FOUND THEN
354 "match_v" := TRUE;
355 RETURN NEXT "initiative_id_v";
356 IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
357 RETURN;
358 END IF;
359 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
360 END IF;
361 END LOOP;
362 EXIT WHEN NOT "match_v";
363 END LOOP;
364 RETURN;
365 END;
366 $$;
368 COMMENT ON FUNCTION "featured_initiative"
369 ( "recipient_id_p" "member"."id"%TYPE,
370 "area_id_p" "area"."id"%TYPE )
371 IS 'Helper function for view "updated_or_featured_initiative"';
373 CREATE VIEW "updated_or_featured_initiative" AS
374 SELECT
375 "subquery".*,
376 NOT EXISTS (
377 SELECT NULL FROM "initiative" AS "better_initiative"
378 WHERE "better_initiative"."issue_id" = "initiative"."issue_id"
379 AND
380 ( COALESCE("better_initiative"."supporter_count", -1),
381 -"better_initiative"."id" ) >
382 ( COALESCE("initiative"."supporter_count", -1),
383 -"initiative"."id" )
384 ) AS "leading"
385 FROM (
386 SELECT * FROM "updated_initiative"
387 UNION ALL
388 SELECT
389 "member"."id" AS "recipient_id",
390 TRUE AS "featured",
391 "featured_initiative_id" AS "initiative_id"
392 FROM "member" CROSS JOIN "area"
393 CROSS JOIN LATERAL
394 "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id"
395 JOIN "initiative" ON "initiative"."id" = "featured_initiative_id"
396 ) AS "subquery"
397 JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id";
399 COMMENT ON VIEW "updated_or_featured_initiative" IS 'Initiatives to be included in a scheduled notification mail because (a) they have been updated or (b) they are featured';
401 COMMENT ON COLUMN "updated_or_featured_initiative"."recipient_id" IS '"id" of the member who receives the notification mail';
402 COMMENT ON COLUMN "updated_or_featured_initiative"."featured" IS 'TRUE if the initiative has been included because it was selected by the "featured_initiative" algorithm (see source of function "featured_initiative")';
403 COMMENT ON COLUMN "updated_or_featured_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
404 COMMENT ON COLUMN "updated_or_featured_initiative"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue';
406 CREATE VIEW "leading_complement_initiative" AS
407 SELECT * FROM (
408 SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id")
409 "uf_initiative"."recipient_id",
410 FALSE AS "featured",
411 "uf_initiative"."initiative_id",
412 TRUE AS "leading"
413 FROM "updated_or_featured_initiative" AS "uf_initiative"
414 JOIN "initiative" AS "uf_initiative_full" ON
415 "uf_initiative_full"."id" = "uf_initiative"."initiative_id"
416 JOIN "initiative" ON
417 "initiative"."issue_id" = "uf_initiative_full"."issue_id"
418 WHERE "initiative"."revoked" ISNULL
419 ORDER BY
420 "uf_initiative"."recipient_id",
421 "initiative"."issue_id",
422 "initiative"."supporter_count" DESC,
423 "initiative"."id"
424 ) AS "subquery"
425 WHERE NOT EXISTS (
426 SELECT NULL FROM "updated_or_featured_initiative" AS "other"
427 WHERE "other"."recipient_id" = "subquery"."recipient_id"
428 AND "other"."initiative_id" = "subquery"."initiative_id"
429 );
431 COMMENT ON VIEW "leading_complement_initiative" IS 'Helper view for view "unfiltered_initiative_for_notification" in order to always include the most supported initiative of an issue';
432 COMMENT ON COLUMN "leading_complement_initiative"."featured" IS 'Always FALSE in this view';
433 COMMENT ON COLUMN "leading_complement_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
434 COMMENT ON COLUMN "leading_complement_initiative"."leading" IS 'Always TRUE in this view';
436 CREATE VIEW "unfiltered_initiative_for_notification" AS
437 SELECT
438 "subquery".*,
439 "supporter"."member_id" NOTNULL AS "supported",
440 CASE WHEN "supporter"."member_id" NOTNULL THEN
441 EXISTS (
442 SELECT NULL FROM "draft"
443 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
444 AND "draft"."id" > "supporter"."draft_id"
445 )
446 ELSE
447 EXISTS (
448 SELECT NULL FROM "draft"
449 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
450 AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE)
451 )
452 END AS "new_draft",
453 CASE WHEN "supporter"."member_id" NOTNULL THEN
454 ( SELECT count(1) FROM "suggestion"
455 LEFT JOIN "opinion" ON
456 "opinion"."member_id" = "supporter"."member_id" AND
457 "opinion"."suggestion_id" = "suggestion"."id"
458 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
459 AND "opinion"."member_id" ISNULL
460 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
461 )
462 ELSE
463 ( SELECT count(1) FROM "suggestion"
464 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
465 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
466 )
467 END AS "new_suggestion_count"
468 FROM (
469 SELECT * FROM "updated_or_featured_initiative"
470 UNION ALL
471 SELECT * FROM "leading_complement_initiative"
472 ) AS "subquery"
473 LEFT JOIN "supporter" ON
474 "supporter"."member_id" = "subquery"."recipient_id" AND
475 "supporter"."initiative_id" = "subquery"."initiative_id"
476 LEFT JOIN "notification_initiative_sent" AS "sent" ON
477 "sent"."member_id" = "subquery"."recipient_id" AND
478 "sent"."initiative_id" = "subquery"."initiative_id";
480 COMMENT ON VIEW "unfiltered_initiative_for_notification" IS 'Helper view which simply combines the views "updated_or_featured_initiative" and "leading_complement_initiative" and adds columns "supported", "new_draft", and "new_suggestion_count';
482 COMMENT ON COLUMN "unfiltered_initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient';
483 COMMENT ON COLUMN "unfiltered_initiative_for_notification"."new_draft" IS 'TRUE if a new draft exists (using the "draft_id" column of the "supporter" table in case of "supported" initiatives and the "last_draft_id" column of the "notification_initiative_sent" table in all other cases)';
484 COMMENT ON COLUMN "unfiltered_initiative_for_notification"."new_suggestion_count" IS 'Number of new suggestions (using the "last_suggestion_id" column of the "notification_initiative_sent" table while ignoring suggestions with an "opinion")';
486 CREATE VIEW "initiative_for_notification" AS
487 SELECT "unfiltered1".*
488 FROM "unfiltered_initiative_for_notification" "unfiltered1"
489 JOIN "initiative" AS "initiative1" ON
490 "initiative1"."id" = "unfiltered1"."initiative_id"
491 JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id"
492 WHERE EXISTS (
493 SELECT NULL
494 FROM "unfiltered_initiative_for_notification" "unfiltered2"
495 JOIN "initiative" AS "initiative2" ON
496 "initiative2"."id" = "unfiltered2"."initiative_id"
497 JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id"
498 WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"
499 AND "issue1"."area_id" = "issue2"."area_id"
500 AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 )
501 );
503 COMMENT ON VIEW "initiative_for_notification" IS 'Initiatives to be included in a scheduled notification mail';
505 COMMENT ON COLUMN "initiative_for_notification"."recipient_id" IS '"id" of the member who receives the notification mail';
506 COMMENT ON COLUMN "initiative_for_notification"."featured" IS 'TRUE if the initiative has been included because it was selected by the "featured_initiative" algorithm (see source of function "featured_initiative")';
507 COMMENT ON COLUMN "initiative_for_notification"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
508 COMMENT ON COLUMN "initiative_for_notification"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue';
509 COMMENT ON COLUMN "initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient';
510 COMMENT ON COLUMN "initiative_for_notification"."new_draft" IS 'TRUE if a new draft exists (using the "draft_id" column of the "supporter" table in case of "supported" initiatives and the "last_draft_id" column of the "notification_initiative_sent" table in all other cases)';
511 COMMENT ON COLUMN "initiative_for_notification"."new_suggestion_count" IS 'Number of new suggestions (using the "last_suggestion_id" column of the "notification_initiative_sent" table while ignoring suggestions with an "opinion")';
513 CREATE VIEW "scheduled_notification_to_send" AS
514 SELECT * FROM (
515 SELECT
516 "id" AS "recipient_id",
517 now() - CASE WHEN "notification_dow" ISNULL THEN
518 ( "notification_sent"::DATE + CASE
519 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
520 THEN 0 ELSE 1 END
521 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
522 ELSE
523 ( "notification_sent"::DATE +
524 ( 7 + "notification_dow" -
525 EXTRACT(DOW FROM
526 ( "notification_sent"::DATE + CASE
527 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
528 THEN 0 ELSE 1 END
529 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
530 )::INTEGER
531 ) % 7 +
532 CASE
533 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
534 THEN 0 ELSE 1
535 END
536 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
537 END AS "pending"
538 FROM (
539 SELECT
540 "id",
541 COALESCE("notification_sent", "activated") AS "notification_sent",
542 "notification_dow",
543 "notification_hour"
544 FROM "member"
545 WHERE "disable_notifications" = FALSE
546 AND "notification_hour" NOTNULL
547 ) AS "subquery1"
548 ) AS "subquery2"
549 WHERE "pending" > '0'::INTERVAL;
551 COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending';
553 COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail';
554 COMMENT ON COLUMN "scheduled_notification_to_send"."pending" IS 'Duration for which the notification mail has already been pending';
556 CREATE VIEW "newsletter_to_send" AS
557 SELECT
558 "member"."id" AS "recipient_id",
559 "newsletter"."id" AS "newsletter_id",
560 "newsletter"."published"
561 FROM "newsletter" CROSS JOIN "member"
562 LEFT JOIN "privilege" ON
563 "privilege"."member_id" = "member"."id" AND
564 "privilege"."unit_id" = "newsletter"."unit_id" AND
565 "privilege"."voting_right" = TRUE
566 LEFT JOIN "subscription" ON
567 "subscription"."member_id" = "member"."id" AND
568 "subscription"."unit_id" = "newsletter"."unit_id"
569 WHERE "newsletter"."published" <= now()
570 AND "newsletter"."sent" ISNULL
571 AND "member"."locked" = FALSE
572 AND (
573 "member"."disable_notifications" = FALSE OR
574 "newsletter"."include_all_members" = TRUE )
575 AND (
576 "newsletter"."unit_id" ISNULL OR
577 "privilege"."member_id" NOTNULL OR
578 "subscription"."member_id" NOTNULL );
580 COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';
581 COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter was supposed to be sent out (can be used for ordering)';
583 CREATE OR REPLACE FUNCTION "require_transaction_isolation"()
584 RETURNS VOID
585 LANGUAGE 'plpgsql' VOLATILE AS $$
586 BEGIN
587 IF
588 current_setting('transaction_isolation') NOT IN
589 ('repeatable read', 'serializable')
590 THEN
591 RAISE EXCEPTION 'Insufficient transaction isolation level' USING
592 HINT = 'Consider using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ.';
593 END IF;
594 RETURN;
595 END;
596 $$;
598 CREATE FUNCTION "get_initiatives_for_notification"
599 ( "recipient_id_p" "member"."id"%TYPE )
600 RETURNS SETOF "initiative_for_notification"
601 LANGUAGE 'plpgsql' VOLATILE AS $$
602 DECLARE
603 "result_row" "initiative_for_notification"%ROWTYPE;
604 "last_draft_id_v" "draft"."id"%TYPE;
605 "last_suggestion_id_v" "suggestion"."id"%TYPE;
606 BEGIN
607 PERFORM "require_transaction_isolation"();
608 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
609 FOR "result_row" IN
610 SELECT * FROM "initiative_for_notification"
611 WHERE "recipient_id" = "recipient_id_p"
612 LOOP
613 SELECT "id" INTO "last_draft_id_v" FROM "draft"
614 WHERE "draft"."initiative_id" = "result_row"."initiative_id"
615 ORDER BY "id" DESC LIMIT 1;
616 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
617 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
618 ORDER BY "id" DESC LIMIT 1;
619 INSERT INTO "notification_initiative_sent"
620 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
621 VALUES (
622 "recipient_id_p",
623 "result_row"."initiative_id",
624 "last_draft_id_v",
625 "last_suggestion_id_v" )
626 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
627 "last_draft_id" = CASE
628 WHEN "notification_initiative_sent"."last_draft_id" > "last_draft_id_v"
629 THEN "notification_initiative_sent"."last_draft_id"
630 ELSE "last_draft_id_v"
631 END,
632 "last_suggestion_id" = CASE
633 WHEN "notification_initiative_sent"."last_suggestion_id" > "last_suggestion_id_v"
634 THEN "notification_initiative_sent"."last_suggestion_id"
635 ELSE "last_suggestion_id_v"
636 END;
637 RETURN NEXT "result_row";
638 END LOOP;
639 DELETE FROM "notification_initiative_sent"
640 USING "initiative", "issue"
641 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
642 AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
643 AND "issue"."id" = "initiative"."issue_id"
644 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
645 UPDATE "member" SET
646 "notification_counter" = "notification_counter" + 1,
647 "notification_sent" = now()
648 WHERE "id" = "recipient_id_p";
649 RETURN;
650 END;
651 $$;
653 COMMENT ON FUNCTION "get_initiatives_for_notification"
654 ( "member"."id"%TYPE )
655 IS 'Returns rows from view "initiative_for_notification" for a given recipient while updating table "notification_initiative_sent" and columns "notification_counter" and "notification_sent" of "member" table';
657 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
658 RETURNS VOID
659 LANGUAGE 'plpgsql' VOLATILE AS $$
660 BEGIN
661 UPDATE "member" SET
662 "last_login" = NULL,
663 "last_delegation_check" = NULL,
664 "login" = NULL,
665 "password" = NULL,
666 "authority" = NULL,
667 "authority_uid" = NULL,
668 "authority_login" = NULL,
669 "locked" = TRUE,
670 "active" = FALSE,
671 "notify_email" = NULL,
672 "notify_email_unconfirmed" = NULL,
673 "notify_email_secret" = NULL,
674 "notify_email_secret_expiry" = NULL,
675 "notify_email_lock_expiry" = NULL,
676 "disable_notifications" = NULL,
677 "notification_counter" = NULL,
678 "notification_sample_size" = NULL,
679 "notification_dow" = NULL,
680 "notification_hour" = NULL,
681 "login_recovery_expiry" = NULL,
682 "password_reset_secret" = NULL,
683 "password_reset_secret_expiry" = NULL,
684 "organizational_unit" = NULL,
685 "internal_posts" = NULL,
686 "realname" = NULL,
687 "birthday" = NULL,
688 "address" = NULL,
689 "email" = NULL,
690 "xmpp_address" = NULL,
691 "website" = NULL,
692 "phone" = NULL,
693 "mobile_phone" = NULL,
694 "profession" = NULL,
695 "external_memberships" = NULL,
696 "external_posts" = NULL,
697 "statement" = NULL
698 WHERE "id" = "member_id_p";
699 -- "text_search_data" is updated by triggers
700 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
701 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
702 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
703 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
704 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
705 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
706 DELETE FROM "session" WHERE "member_id" = "member_id_p";
707 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
708 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
709 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
710 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
711 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
712 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
713 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
714 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
715 DELETE FROM "direct_voter" USING "issue"
716 WHERE "direct_voter"."issue_id" = "issue"."id"
717 AND "issue"."closed" ISNULL
718 AND "member_id" = "member_id_p";
719 RETURN;
720 END;
721 $$;
723 CREATE OR REPLACE FUNCTION "delete_private_data"()
724 RETURNS VOID
725 LANGUAGE 'plpgsql' VOLATILE AS $$
726 BEGIN
727 DELETE FROM "temporary_transaction_data";
728 DELETE FROM "member" WHERE "activated" ISNULL;
729 UPDATE "member" SET
730 "invite_code" = NULL,
731 "invite_code_expiry" = NULL,
732 "admin_comment" = NULL,
733 "last_login" = NULL,
734 "last_delegation_check" = NULL,
735 "login" = NULL,
736 "password" = NULL,
737 "authority" = NULL,
738 "authority_uid" = NULL,
739 "authority_login" = NULL,
740 "lang" = NULL,
741 "notify_email" = NULL,
742 "notify_email_unconfirmed" = NULL,
743 "notify_email_secret" = NULL,
744 "notify_email_secret_expiry" = NULL,
745 "notify_email_lock_expiry" = NULL,
746 "disable_notifications" = NULL,
747 "notification_counter" = NULL,
748 "notification_sample_size" = NULL,
749 "notification_dow" = NULL,
750 "notification_hour" = NULL,
751 "login_recovery_expiry" = NULL,
752 "password_reset_secret" = NULL,
753 "password_reset_secret_expiry" = NULL,
754 "organizational_unit" = NULL,
755 "internal_posts" = NULL,
756 "realname" = NULL,
757 "birthday" = NULL,
758 "address" = NULL,
759 "email" = NULL,
760 "xmpp_address" = NULL,
761 "website" = NULL,
762 "phone" = NULL,
763 "mobile_phone" = NULL,
764 "profession" = NULL,
765 "external_memberships" = NULL,
766 "external_posts" = NULL,
767 "formatting_engine" = NULL,
768 "statement" = NULL;
769 -- "text_search_data" is updated by triggers
770 DELETE FROM "setting";
771 DELETE FROM "setting_map";
772 DELETE FROM "member_relation_setting";
773 DELETE FROM "member_image";
774 DELETE FROM "contact";
775 DELETE FROM "ignored_member";
776 DELETE FROM "session";
777 DELETE FROM "area_setting";
778 DELETE FROM "issue_setting";
779 DELETE FROM "ignored_initiative";
780 DELETE FROM "initiative_setting";
781 DELETE FROM "suggestion_setting";
782 DELETE FROM "non_voter";
783 DELETE FROM "direct_voter" USING "issue"
784 WHERE "direct_voter"."issue_id" = "issue"."id"
785 AND "issue"."closed" ISNULL;
786 RETURN;
787 END;
788 $$;
790 COMMIT;

Impressum / About Us