liquid_feedback_core

annotate update/core-update.v3.1.0-v3.2.0.sql @ 500:c1a2954078d7

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

Impressum / About Us