liquid_feedback_core

annotate update/core-update.v3.1.0-v3.2.0.sql @ 505:be7942edfd1e

find changesets by author, revision, files, or words in the commit message
New algorithm for view "scheduled_notification_to_send"; Update "notification_sent" column in "get_initiatives_for_notification" function
author jbe
date Tue Apr 05 20:12:24 2016 +0200 (2016-04-05)
parents d07e6a046d41
children f5aba1fbb845
rev   line source
jbe@478 1 BEGIN;
jbe@478 2
jbe@478 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@478 4 SELECT * FROM (VALUES ('3.2.0', 3, 2, 0))
jbe@478 5 AS "subquery"("string", "major", "minor", "revision");
jbe@478 6
jbe@499 7 -- TODO: preliminary script
jbe@499 8
jbe@478 9 ALTER TABLE "member" ADD COLUMN "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE;
jbe@492 10 ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 1;
jbe@492 11 ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4 NOT NULL DEFAULT 3;
jbe@492 12 ALTER TABLE "member" ADD COLUMN "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6);
jbe@492 13 ALTER TABLE "member" ADD COLUMN "notification_hour" INT4 CHECK ("notification_hour" BETWEEN 0 AND 23);
jbe@504 14 ALTER TABLE "member" ADD COLUMN "notification_sent" TIMESTAMP;
jbe@478 15
jbe@478 16 UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none';
jbe@499 17
jbe@500 18 DROP VIEW "selected_event_seen_by_member";
jbe@500 19 DROP VIEW "event_seen_by_member";
jbe@499 20 ALTER TABLE "member" DROP COLUMN "notify_level";
jbe@499 21 DROP TYPE "notify_level";
jbe@478 22
jbe@478 23 CREATE TABLE "subscription" (
jbe@478 24 PRIMARY KEY ("member_id", "unit_id"),
jbe@478 25 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@478 26 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@478 27 CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id");
jbe@492 28
jbe@492 29 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';
jbe@492 30
jbe@492 31 CREATE TABLE "ignored_area" (
jbe@492 32 PRIMARY KEY ("member_id", "area_id"),
jbe@492 33 "member_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@492 34 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@492 35 CREATE INDEX "ignored_area_area_id_idx" ON "ignored_area" ("area_id");
jbe@492 36
jbe@492 37 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';
jbe@492 38
jbe@492 39 CREATE TABLE "initiative_notification_sent" (
jbe@492 40 PRIMARY KEY ("member_id", "initiative_id"),
jbe@492 41 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@492 42 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@492 43 "last_draft_id" INT8 NOT NULL,
jbe@495 44 "last_suggestion_id" INT8 );
jbe@492 45 CREATE INDEX "initiative_notification_sent_initiative_idx" ON "initiative_notification_sent" ("initiative_id");
jbe@478 46
jbe@496 47 CREATE TABLE "newsletter" (
jbe@496 48 "id" SERIAL4 PRIMARY KEY,
jbe@496 49 "published" TIMESTAMPTZ NOT NULL,
jbe@496 50 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@496 51 "include_all_members" BOOLEAN NOT NULL,
jbe@496 52 "sent" TIMESTAMPTZ,
jbe@496 53 "subject" TEXT NOT NULL,
jbe@496 54 "content" TEXT NOT NULL );
jbe@496 55 CREATE INDEX "newsletter_unit_id_idx" ON "newsletter" ("unit_id", "published");
jbe@496 56 CREATE INDEX "newsletter_all_units_published_idx" ON "newsletter" ("published") WHERE "unit_id" ISNULL;
jbe@496 57 CREATE INDEX "newsletter_published_idx" ON "newsletter" ("published");
jbe@496 58
jbe@499 59 CREATE VIEW "event_for_notification" AS
jbe@499 60 SELECT
jbe@499 61 "member"."id" AS "recipient_id",
jbe@499 62 "event".*
jbe@499 63 FROM "member" CROSS JOIN "event"
jbe@499 64 JOIN "issue" ON "issue"."id" = "event"."issue_id"
jbe@499 65 JOIN "area" ON "area"."id" = "issue"."area_id"
jbe@499 66 LEFT JOIN "privilege" ON
jbe@499 67 "privilege"."member_id" = "member"."id" AND
jbe@499 68 "privilege"."unit_id" = "area"."unit_id" AND
jbe@499 69 "privilege"."voting_right" = TRUE
jbe@499 70 LEFT JOIN "subscription" ON
jbe@499 71 "subscription"."member_id" = "member"."id" AND
jbe@499 72 "subscription"."unit_id" = "area"."unit_id"
jbe@499 73 LEFT JOIN "ignored_area" ON
jbe@499 74 "ignored_area"."member_id" = "member"."id" AND
jbe@499 75 "ignored_area"."area_id" = "issue"."area_id"
jbe@499 76 LEFT JOIN "interest" ON
jbe@499 77 "interest"."member_id" = "member"."id" AND
jbe@499 78 "interest"."issue_id" = "event"."issue_id"
jbe@499 79 LEFT JOIN "supporter" ON
jbe@499 80 "supporter"."member_id" = "member"."id" AND
jbe@499 81 "supporter"."initiative_id" = "event"."initiative_id"
jbe@499 82 WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL)
jbe@499 83 AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
jbe@499 84 AND (
jbe@499 85 "event"."event" = 'issue_state_changed'::"event_type" OR
jbe@499 86 ( "event"."event" = 'initiative_revoked'::"event_type" AND
jbe@499 87 "supporter"."member_id" NOTNULL ) );
jbe@499 88
jbe@478 89 CREATE VIEW "updated_initiative" AS
jbe@478 90 SELECT
jbe@499 91 "supporter"."member_id" AS "recipient_id",
jbe@478 92 FALSE AS "featured",
jbe@499 93 "supporter"."initiative_id"
jbe@499 94 FROM "supporter"
jbe@499 95 JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id"
jbe@478 96 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@499 97 LEFT JOIN "initiative_notification_sent" AS "sent" ON
jbe@499 98 "sent"."member_id" = "supporter"."member_id" AND
jbe@499 99 "sent"."initiative_id" = "supporter"."initiative_id"
jbe@499 100 LEFT JOIN "ignored_initiative" ON
jbe@499 101 "ignored_initiative"."member_id" = "supporter"."member_id" AND
jbe@499 102 "ignored_initiative"."initiative_id" = "supporter"."initiative_id"
jbe@480 103 WHERE "issue"."state" IN ('admission', 'discussion')
jbe@503 104 AND "initiative"."revoked" ISNULL
jbe@499 105 AND "ignored_initiative"."member_id" ISNULL
jbe@478 106 AND (
jbe@478 107 EXISTS (
jbe@478 108 SELECT NULL FROM "draft"
jbe@499 109 LEFT JOIN "ignored_member" ON
jbe@499 110 "ignored_member"."member_id" = "supporter"."member_id" AND
jbe@499 111 "ignored_member"."other_member_id" = "draft"."author_id"
jbe@499 112 WHERE "draft"."initiative_id" = "supporter"."initiative_id"
jbe@478 113 AND "draft"."id" > "supporter"."draft_id"
jbe@499 114 AND "ignored_member"."member_id" ISNULL
jbe@478 115 ) OR EXISTS (
jbe@478 116 SELECT NULL FROM "suggestion"
jbe@492 117 LEFT JOIN "opinion" ON
jbe@492 118 "opinion"."member_id" = "supporter"."member_id" AND
jbe@492 119 "opinion"."suggestion_id" = "suggestion"."id"
jbe@499 120 LEFT JOIN "ignored_member" ON
jbe@499 121 "ignored_member"."member_id" = "supporter"."member_id" AND
jbe@499 122 "ignored_member"."other_member_id" = "suggestion"."author_id"
jbe@499 123 WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"
jbe@492 124 AND "opinion"."member_id" ISNULL
jbe@499 125 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
jbe@499 126 AND "ignored_member"."member_id" ISNULL
jbe@478 127 )
jbe@478 128 );
jbe@478 129
jbe@478 130 CREATE FUNCTION "featured_initiative"
jbe@499 131 ( "recipient_id_p" "member"."id"%TYPE,
jbe@499 132 "area_id_p" "area"."id"%TYPE )
jbe@499 133 RETURNS SETOF "initiative"."id"%TYPE
jbe@478 134 LANGUAGE 'plpgsql' STABLE AS $$
jbe@478 135 DECLARE
jbe@499 136 "counter_v" "member"."notification_counter"%TYPE;
jbe@499 137 "sample_size_v" "member"."notification_sample_size"%TYPE;
jbe@499 138 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
jbe@499 139 "match_v" BOOLEAN;
jbe@478 140 "member_id_v" "member"."id"%TYPE;
jbe@478 141 "seed_v" TEXT;
jbe@499 142 "initiative_id_v" "initiative"."id"%TYPE;
jbe@478 143 BEGIN
jbe@499 144 SELECT "notification_counter", "notification_sample_size"
jbe@499 145 INTO "counter_v", "sample_size_v"
jbe@499 146 FROM "member" WHERE "id" = "recipient_id_p";
jbe@478 147 "initiative_id_ary" := '{}';
jbe@478 148 LOOP
jbe@478 149 "match_v" := FALSE;
jbe@478 150 FOR "member_id_v", "seed_v" IN
jbe@478 151 SELECT * FROM (
jbe@478 152 SELECT DISTINCT
jbe@478 153 "supporter"."member_id",
jbe@499 154 md5(
jbe@499 155 "recipient_id_p" || '-' ||
jbe@499 156 "counter_v" || '-' ||
jbe@499 157 "area_id_p" || '-' ||
jbe@499 158 "supporter"."member_id"
jbe@499 159 ) AS "seed"
jbe@478 160 FROM "supporter"
jbe@478 161 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
jbe@478 162 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@499 163 WHERE "supporter"."member_id" != "recipient_id_p"
jbe@478 164 AND "issue"."area_id" = "area_id_p"
jbe@478 165 AND "issue"."state" IN ('admission', 'discussion', 'verification')
jbe@478 166 ) AS "subquery"
jbe@478 167 ORDER BY "seed"
jbe@478 168 LOOP
jbe@499 169 SELECT "initiative"."id" INTO "initiative_id_v"
jbe@478 170 FROM "initiative"
jbe@478 171 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@499 172 JOIN "area" ON "area"."id" = "issue"."area_id"
jbe@478 173 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
jbe@478 174 LEFT JOIN "supporter" AS "self_support" ON
jbe@478 175 "self_support"."initiative_id" = "initiative"."id" AND
jbe@499 176 "self_support"."member_id" = "recipient_id_p"
jbe@499 177 LEFT JOIN "privilege" ON
jbe@499 178 "privilege"."member_id" = "recipient_id_p" AND
jbe@499 179 "privilege"."unit_id" = "area"."unit_id" AND
jbe@499 180 "privilege"."voting_right" = TRUE
jbe@499 181 LEFT JOIN "subscription" ON
jbe@499 182 "subscription"."member_id" = "recipient_id_p" AND
jbe@499 183 "subscription"."unit_id" = "area"."unit_id"
jbe@499 184 LEFT JOIN "ignored_initiative" ON
jbe@499 185 "ignored_initiative"."member_id" = "recipient_id_p" AND
jbe@499 186 "ignored_initiative"."initiative_id" = "initiative"."id"
jbe@478 187 WHERE "supporter"."member_id" = "member_id_v"
jbe@478 188 AND "issue"."area_id" = "area_id_p"
jbe@478 189 AND "issue"."state" IN ('admission', 'discussion', 'verification')
jbe@503 190 AND "initiative"."revoked" ISNULL
jbe@478 191 AND "self_support"."member_id" ISNULL
jbe@478 192 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
jbe@499 193 AND (
jbe@499 194 "privilege"."member_id" NOTNULL OR
jbe@499 195 "subscription"."member_id" NOTNULL )
jbe@499 196 AND "ignored_initiative"."member_id" ISNULL
jbe@499 197 AND NOT EXISTS (
jbe@499 198 SELECT NULL FROM "draft"
jbe@499 199 JOIN "ignored_member" ON
jbe@499 200 "ignored_member"."member_id" = "recipient_id_p" AND
jbe@499 201 "ignored_member"."other_member_id" = "draft"."author_id"
jbe@499 202 WHERE "draft"."initiative_id" = "initiative"."id"
jbe@499 203 )
jbe@478 204 ORDER BY md5("seed_v" || '-' || "initiative"."id")
jbe@478 205 LIMIT 1;
jbe@478 206 IF FOUND THEN
jbe@478 207 "match_v" := TRUE;
jbe@499 208 RETURN NEXT "initiative_id_v";
jbe@499 209 IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
jbe@478 210 RETURN;
jbe@478 211 END IF;
jbe@499 212 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
jbe@478 213 END IF;
jbe@478 214 END LOOP;
jbe@478 215 EXIT WHEN NOT "match_v";
jbe@478 216 END LOOP;
jbe@478 217 RETURN;
jbe@478 218 END;
jbe@478 219 $$;
jbe@478 220
jbe@478 221 CREATE VIEW "updated_or_featured_initiative" AS
jbe@478 222 SELECT
jbe@499 223 "subquery".*,
jbe@478 224 NOT EXISTS (
jbe@478 225 SELECT NULL FROM "initiative" AS "better_initiative"
jbe@499 226 WHERE "better_initiative"."issue_id" = "initiative"."issue_id"
jbe@484 227 AND
jbe@502 228 ( COALESCE("better_initiative"."supporter_count", -1),
jbe@484 229 -"better_initiative"."id" ) >
jbe@502 230 ( COALESCE("initiative"."supporter_count", -1),
jbe@485 231 -"initiative"."id" )
jbe@499 232 ) AS "leading"
jbe@499 233 FROM (
jbe@499 234 SELECT * FROM "updated_initiative"
jbe@499 235 UNION ALL
jbe@499 236 SELECT
jbe@499 237 "member"."id" AS "recipient_id",
jbe@499 238 TRUE AS "featured",
jbe@499 239 "featured_initiative_id" AS "initiative_id"
jbe@499 240 FROM "member" CROSS JOIN "area"
jbe@499 241 CROSS JOIN LATERAL
jbe@499 242 "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id"
jbe@499 243 JOIN "initiative" ON "initiative"."id" = "featured_initiative_id"
jbe@499 244 ) AS "subquery"
jbe@499 245 JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id";
jbe@478 246
jbe@478 247 CREATE VIEW "leading_complement_initiative" AS
jbe@478 248 SELECT * FROM (
jbe@499 249 SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id")
jbe@499 250 "uf_initiative"."recipient_id",
jbe@478 251 FALSE AS "featured",
jbe@499 252 "uf_initiative"."initiative_id",
jbe@499 253 TRUE AS "leading"
jbe@492 254 FROM "updated_or_featured_initiative" AS "uf_initiative"
jbe@499 255 JOIN "initiative" AS "uf_initiative_full" ON
jbe@499 256 "uf_initiative_full"."id" = "uf_initiative"."initiative_id"
jbe@492 257 JOIN "initiative" ON
jbe@499 258 "initiative"."issue_id" = "uf_initiative_full"."issue_id"
jbe@503 259 WHERE "initiative"."revoked" ISNULL
jbe@478 260 ORDER BY
jbe@499 261 "uf_initiative"."recipient_id",
jbe@478 262 "initiative"."issue_id",
jbe@502 263 "initiative"."supporter_count" DESC,
jbe@478 264 "initiative"."id"
jbe@478 265 ) AS "subquery"
jbe@478 266 WHERE NOT EXISTS (
jbe@478 267 SELECT NULL FROM "updated_or_featured_initiative" AS "other"
jbe@499 268 WHERE "other"."recipient_id" = "subquery"."recipient_id"
jbe@499 269 AND "other"."initiative_id" = "subquery"."initiative_id"
jbe@478 270 );
jbe@478 271
jbe@492 272 CREATE VIEW "unfiltered_initiative_for_notification" AS
jbe@499 273 SELECT
jbe@499 274 "subquery".*,
jbe@499 275 "supporter"."member_id" NOTNULL AS "supported",
jbe@499 276 CASE WHEN "supporter"."member_id" NOTNULL THEN
jbe@499 277 EXISTS (
jbe@499 278 SELECT NULL FROM "draft"
jbe@499 279 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
jbe@499 280 AND "draft"."id" > "supporter"."draft_id"
jbe@499 281 )
jbe@499 282 ELSE
jbe@499 283 EXISTS (
jbe@499 284 SELECT NULL FROM "draft"
jbe@499 285 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
jbe@499 286 AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE)
jbe@499 287 )
jbe@499 288 END AS "new_draft",
jbe@499 289 CASE WHEN "supporter"."member_id" NOTNULL THEN
jbe@499 290 ( SELECT count(1) FROM "suggestion"
jbe@499 291 LEFT JOIN "opinion" ON
jbe@499 292 "opinion"."member_id" = "supporter"."member_id" AND
jbe@499 293 "opinion"."suggestion_id" = "suggestion"."id"
jbe@499 294 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
jbe@499 295 AND "opinion"."member_id" ISNULL
jbe@499 296 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
jbe@499 297 )
jbe@499 298 ELSE
jbe@499 299 ( SELECT count(1) FROM "suggestion"
jbe@499 300 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
jbe@499 301 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
jbe@499 302 )
jbe@499 303 END AS "new_suggestion_count"
jbe@499 304 FROM (
jbe@499 305 SELECT * FROM "updated_or_featured_initiative"
jbe@499 306 UNION ALL
jbe@499 307 SELECT * FROM "leading_complement_initiative"
jbe@499 308 ) AS "subquery"
jbe@499 309 LEFT JOIN "supporter" ON
jbe@499 310 "supporter"."member_id" = "subquery"."recipient_id" AND
jbe@499 311 "supporter"."initiative_id" = "subquery"."initiative_id"
jbe@499 312 LEFT JOIN "initiative_notification_sent" AS "sent" ON
jbe@499 313 "sent"."member_id" = "subquery"."recipient_id" AND
jbe@499 314 "sent"."initiative_id" = "subquery"."initiative_id";
jbe@478 315
jbe@492 316 CREATE VIEW "initiative_for_notification" AS
jbe@499 317 SELECT "unfiltered1".*
jbe@499 318 FROM "unfiltered_initiative_for_notification" "unfiltered1"
jbe@499 319 JOIN "initiative" AS "initiative1" ON
jbe@499 320 "initiative1"."id" = "unfiltered1"."initiative_id"
jbe@499 321 JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id"
jbe@492 322 WHERE EXISTS (
jbe@492 323 SELECT NULL
jbe@499 324 FROM "unfiltered_initiative_for_notification" "unfiltered2"
jbe@499 325 JOIN "initiative" AS "initiative2" ON
jbe@499 326 "initiative2"."id" = "unfiltered2"."initiative_id"
jbe@499 327 JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id"
jbe@499 328 WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"
jbe@492 329 AND "issue1"."area_id" = "issue2"."area_id"
jbe@499 330 AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 )
jbe@492 331 );
jbe@492 332
jbe@504 333 CREATE VIEW "scheduled_notification_to_send" AS
jbe@505 334 SELECT * FROM (
jbe@505 335 SELECT
jbe@505 336 "id" AS "recipient_id",
jbe@505 337 now() - CASE WHEN "notification_dow" ISNULL THEN
jbe@505 338 ( "notification_sent"::DATE + CASE
jbe@505 339 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
jbe@505 340 THEN 0 ELSE 1 END
jbe@505 341 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
jbe@505 342 ELSE
jbe@505 343 ( "notification_sent"::DATE +
jbe@505 344 ( 7 + "notification_dow" -
jbe@505 345 EXTRACT(DOW FROM
jbe@505 346 ( "notification_sent"::DATE + CASE
jbe@505 347 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
jbe@505 348 THEN 0 ELSE 1 END
jbe@505 349 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
jbe@505 350 )::INTEGER
jbe@505 351 ) % 7 +
jbe@505 352 CASE
jbe@505 353 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
jbe@505 354 THEN 0 ELSE 1
jbe@505 355 END
jbe@505 356 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
jbe@505 357 END AS "pending"
jbe@505 358 FROM (
jbe@505 359 SELECT
jbe@505 360 "id",
jbe@505 361 COALESCE("notification_sent", "activated") AS "notification_sent",
jbe@505 362 "notification_dow",
jbe@505 363 "notification_hour"
jbe@505 364 FROM "member"
jbe@505 365 WHERE "disable_notifications" = FALSE
jbe@505 366 AND "notification_hour" NOTNULL
jbe@505 367 ) AS "subquery1"
jbe@505 368 ) AS "subquery2"
jbe@505 369 WHERE "pending" > '0'::INTERVAL;
jbe@504 370
jbe@497 371 CREATE VIEW "newsletter_to_send" AS
jbe@497 372 SELECT
jbe@499 373 "member"."id" AS "recipient_id",
jbe@499 374 "newsletter"."id" AS "newsletter_id"
jbe@497 375 FROM "newsletter" CROSS JOIN "member"
jbe@497 376 LEFT JOIN "privilege" ON
jbe@497 377 "privilege"."member_id" = "member"."id" AND
jbe@497 378 "privilege"."unit_id" = "newsletter"."unit_id" AND
jbe@497 379 "privilege"."voting_right" = TRUE
jbe@497 380 LEFT JOIN "subscription" ON
jbe@497 381 "subscription"."member_id" = "member"."id" AND
jbe@497 382 "subscription"."unit_id" = "newsletter"."unit_id"
jbe@498 383 WHERE "newsletter"."published" <= now()
jbe@497 384 AND "newsletter"."sent" ISNULL
jbe@497 385 AND "member"."locked" = FALSE
jbe@497 386 AND (
jbe@497 387 "member"."disable_notifications" = FALSE OR
jbe@497 388 "newsletter"."include_all_members" = TRUE )
jbe@497 389 AND (
jbe@497 390 "newsletter"."unit_id" ISNULL OR
jbe@497 391 "privilege"."member_id" NOTNULL OR
jbe@497 392 "subscription"."member_id" NOTNULL );
jbe@497 393
jbe@492 394 CREATE FUNCTION "get_initiatives_for_notification"
jbe@501 395 ( "recipient_id_p" "member"."id"%TYPE )
jbe@492 396 RETURNS SETOF "initiative_for_notification"
jbe@492 397 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@492 398 DECLARE
jbe@492 399 "result_row" "initiative_for_notification"%ROWTYPE;
jbe@492 400 "last_draft_id_v" "draft"."id"%TYPE;
jbe@492 401 "last_suggestion_id_v" "suggestion"."id"%TYPE;
jbe@492 402 BEGIN
jbe@492 403 PERFORM "require_transaction_isolation"();
jbe@501 404 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
jbe@492 405 FOR "result_row" IN
jbe@492 406 SELECT * FROM "initiative_for_notification"
jbe@501 407 WHERE "recipient_id" = "recipient_id_p"
jbe@492 408 LOOP
jbe@492 409 SELECT "id" INTO "last_draft_id_v" FROM "draft"
jbe@499 410 WHERE "draft"."initiative_id" = "result_row"."initiative_id"
jbe@492 411 ORDER BY "id" DESC LIMIT 1;
jbe@492 412 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
jbe@499 413 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
jbe@492 414 ORDER BY "id" DESC LIMIT 1;
jbe@492 415 INSERT INTO "initiative_notification_sent"
jbe@492 416 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
jbe@492 417 VALUES (
jbe@501 418 "recipient_id_p",
jbe@499 419 "result_row"."initiative_id",
jbe@493 420 "last_draft_id_v",
jbe@493 421 "last_suggestion_id_v" )
jbe@492 422 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
jbe@492 423 "last_draft_id" = CASE
jbe@494 424 WHEN "initiative_notification_sent"."last_draft_id" > "last_draft_id_v"
jbe@494 425 THEN "initiative_notification_sent"."last_draft_id"
jbe@492 426 ELSE "last_draft_id_v"
jbe@492 427 END,
jbe@492 428 "last_suggestion_id" = CASE
jbe@494 429 WHEN "initiative_notification_sent"."last_suggestion_id" > "last_suggestion_id_v"
jbe@494 430 THEN "initiative_notification_sent"."last_suggestion_id"
jbe@492 431 ELSE "last_suggestion_id_v"
jbe@492 432 END;
jbe@492 433 RETURN NEXT "result_row";
jbe@492 434 END LOOP;
jbe@492 435 DELETE FROM "initiative_notification_sent"
jbe@492 436 USING "initiative", "issue"
jbe@501 437 WHERE "initiative_notification_sent"."member_id" = "recipient_id_p"
jbe@492 438 AND "initiative"."id" = "initiative_notification_sent"."initiative_id"
jbe@492 439 AND "issue"."id" = "initiative"."issue_id"
jbe@492 440 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
jbe@505 441 UPDATE "member" SET
jbe@505 442 "notification_counter" = "notification_counter" + 1 AND
jbe@505 443 "notification_sent" = now()
jbe@501 444 WHERE "id" = "recipient_id_p";
jbe@492 445 RETURN;
jbe@492 446 END;
jbe@492 447 $$;
jbe@492 448
jbe@478 449 COMMIT;

Impressum / About Us