liquid_feedback_core

annotate update/core-update.v3.1.0-v3.2.0.sql @ 492:917a65357efb

find changesets by author, revision, files, or words in the commit message
Updated preliminary update script for new notification system
author jbe
date Sun Apr 03 19:48:32 2016 +0200 (2016-04-03)
parents e431f1b73c35
children d932363da4ee
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@478 7 ALTER TABLE "member" ADD COLUMN "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE;
jbe@492 8 ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 1;
jbe@492 9 ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4 NOT NULL DEFAULT 3;
jbe@492 10 ALTER TABLE "member" ADD COLUMN "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6);
jbe@492 11 ALTER TABLE "member" ADD COLUMN "notification_hour" INT4 CHECK ("notification_hour" BETWEEN 0 AND 23);
jbe@478 12
jbe@478 13 UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none';
jbe@478 14
jbe@478 15 CREATE TABLE "subscription" (
jbe@478 16 PRIMARY KEY ("member_id", "unit_id"),
jbe@478 17 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@478 18 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@478 19 CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id");
jbe@492 20
jbe@492 21 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 22
jbe@492 23 CREATE TABLE "ignored_area" (
jbe@492 24 PRIMARY KEY ("member_id", "area_id"),
jbe@492 25 "member_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@492 26 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
jbe@492 27 CREATE INDEX "ignored_area_area_id_idx" ON "ignored_area" ("area_id");
jbe@492 28
jbe@492 29 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 30
jbe@492 31 CREATE TABLE "initiative_notification_sent" (
jbe@492 32 PRIMARY KEY ("member_id", "initiative_id"),
jbe@492 33 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@492 34 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@492 35 "last_draft_id" INT8 NOT NULL,
jbe@492 36 "last_suggestion_id" INT8 NOT NULL );
jbe@492 37 CREATE INDEX "initiative_notification_sent_initiative_idx" ON "initiative_notification_sent" ("initiative_id");
jbe@478 38
jbe@478 39 CREATE VIEW "updated_initiative" AS
jbe@478 40 SELECT
jbe@492 41 "supporter"."member_id" AS "seen_by_member_id",
jbe@478 42 TRUE AS "supported",
jbe@478 43 EXISTS (
jbe@478 44 SELECT NULL FROM "draft"
jbe@478 45 WHERE "draft"."initiative_id" = "initiative"."id"
jbe@478 46 AND "draft"."id" > "supporter"."draft_id"
jbe@478 47 ) AS "new_draft",
jbe@478 48 ( SELECT count(1) FROM "suggestion"
jbe@492 49 LEFT JOIN "opinion" ON
jbe@492 50 "opinion"."member_id" = "supporter"."member_id" AND
jbe@492 51 "opinion"."suggestion_id" = "suggestion"."id"
jbe@478 52 WHERE "suggestion"."initiative_id" = "initiative"."id"
jbe@492 53 AND "opinion"."member_id" ISNULL
jbe@478 54 AND COALESCE(
jbe@492 55 "suggestion"."id" > "sent"."last_suggestion_id",
jbe@478 56 TRUE
jbe@478 57 )
jbe@478 58 ) AS "new_suggestion_count",
jbe@478 59 FALSE AS "featured",
jbe@478 60 NOT EXISTS (
jbe@478 61 SELECT NULL FROM "initiative" AS "better_initiative"
jbe@478 62 WHERE
jbe@484 63 "better_initiative"."issue_id" = "initiative"."issue_id"
jbe@484 64 AND
jbe@484 65 ( COALESCE("better_initiative"."harmonic_weight", -1),
jbe@484 66 -"better_initiative"."id" ) >
jbe@484 67 ( COALESCE("initiative"."harmonic_weight", -1),
jbe@485 68 -"initiative"."id" )
jbe@478 69 ) AS "leading",
jbe@478 70 "initiative".*
jbe@492 71 FROM "supporter" JOIN "initiative"
jbe@492 72 ON "supporter"."initiative_id" = "initiative"."id"
jbe@492 73 LEFT JOIN "initiative_notification_sent" AS "sent"
jbe@492 74 ON "sent"."member_id" = "supporter"."member_id"
jbe@492 75 AND "sent"."initiative_id" = "initiative"."id"
jbe@478 76 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@480 77 WHERE "issue"."state" IN ('admission', 'discussion')
jbe@478 78 AND (
jbe@478 79 EXISTS (
jbe@478 80 SELECT NULL FROM "draft"
jbe@478 81 WHERE "draft"."initiative_id" = "initiative"."id"
jbe@478 82 AND "draft"."id" > "supporter"."draft_id"
jbe@478 83 ) OR EXISTS (
jbe@478 84 SELECT NULL FROM "suggestion"
jbe@492 85 LEFT JOIN "opinion" ON
jbe@492 86 "opinion"."member_id" = "supporter"."member_id" AND
jbe@492 87 "opinion"."suggestion_id" = "suggestion"."id"
jbe@478 88 WHERE "suggestion"."initiative_id" = "initiative"."id"
jbe@492 89 AND "opinion"."member_id" ISNULL
jbe@478 90 AND COALESCE(
jbe@492 91 "suggestion"."id" > "sent"."last_suggestion_id",
jbe@478 92 TRUE
jbe@478 93 )
jbe@478 94 )
jbe@478 95 );
jbe@478 96
jbe@478 97 CREATE FUNCTION "featured_initiative"
jbe@478 98 ( "member_id_p" "member"."id"%TYPE,
jbe@478 99 "area_id_p" "area"."id"%TYPE )
jbe@478 100 RETURNS SETOF "initiative"
jbe@478 101 LANGUAGE 'plpgsql' STABLE AS $$
jbe@478 102 DECLARE
jbe@482 103 "member_row" "member"%ROWTYPE;
jbe@478 104 "member_id_v" "member"."id"%TYPE;
jbe@478 105 "seed_v" TEXT;
jbe@478 106 "result_row" "initiative"%ROWTYPE;
jbe@478 107 "match_v" BOOLEAN;
jbe@478 108 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
jbe@478 109 BEGIN
jbe@482 110 SELECT INTO "member_row" * FROM "member" WHERE "id" = "member_id_p";
jbe@478 111 "initiative_id_ary" := '{}';
jbe@478 112 LOOP
jbe@478 113 "match_v" := FALSE;
jbe@478 114 FOR "member_id_v", "seed_v" IN
jbe@478 115 SELECT * FROM (
jbe@478 116 SELECT DISTINCT
jbe@478 117 "supporter"."member_id",
jbe@482 118 md5("member_id_p" || '-' || "member_row"."notification_counter" || '-' || "area_id_p" || '-' || "supporter"."member_id") AS "seed"
jbe@478 119 FROM "supporter"
jbe@478 120 JOIN "member" ON "member"."id" = "supporter"."member_id"
jbe@478 121 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
jbe@478 122 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@478 123 WHERE "supporter"."member_id" != "member_id_p"
jbe@478 124 AND "issue"."area_id" = "area_id_p"
jbe@478 125 AND "issue"."state" IN ('admission', 'discussion', 'verification')
jbe@478 126 ) AS "subquery"
jbe@478 127 ORDER BY "seed"
jbe@478 128 LOOP
jbe@478 129 SELECT "initiative".* INTO "result_row"
jbe@478 130 FROM "initiative"
jbe@478 131 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@478 132 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
jbe@478 133 LEFT JOIN "supporter" AS "self_support" ON
jbe@478 134 "self_support"."initiative_id" = "initiative"."id" AND
jbe@478 135 "self_support"."member_id" = "member_id_p"
jbe@478 136 WHERE "supporter"."member_id" = "member_id_v"
jbe@478 137 AND "issue"."area_id" = "area_id_p"
jbe@478 138 AND "issue"."state" IN ('admission', 'discussion', 'verification')
jbe@478 139 AND "self_support"."member_id" ISNULL
jbe@478 140 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
jbe@478 141 ORDER BY md5("seed_v" || '-' || "initiative"."id")
jbe@478 142 LIMIT 1;
jbe@478 143 IF FOUND THEN
jbe@478 144 "match_v" := TRUE;
jbe@478 145 "initiative_id_ary" := "initiative_id_ary" || "result_row"."id";
jbe@478 146 RETURN NEXT "result_row";
jbe@492 147 IF array_length("initiative_id_ary", 1) >= "member_row"."notification_sample_size" THEN
jbe@478 148 RETURN;
jbe@478 149 END IF;
jbe@478 150 END IF;
jbe@478 151 END LOOP;
jbe@478 152 EXIT WHEN NOT "match_v";
jbe@478 153 END LOOP;
jbe@478 154 RETURN;
jbe@478 155 END;
jbe@478 156 $$;
jbe@478 157
jbe@478 158 CREATE VIEW "updated_or_featured_initiative" AS
jbe@478 159 SELECT * FROM "updated_initiative"
jbe@478 160 UNION ALL
jbe@478 161 SELECT
jbe@478 162 "member"."id" AS "seen_by_member_id",
jbe@478 163 FALSE AS "supported",
jbe@492 164 EXISTS (
jbe@492 165 SELECT NULL FROM "draft"
jbe@492 166 WHERE "draft"."initiative_id" = "initiative"."id"
jbe@492 167 AND COALESCE(
jbe@492 168 "draft"."id" > "sent"."last_draft_id",
jbe@492 169 TRUE
jbe@492 170 )
jbe@492 171 ) AS "new_draft",
jbe@492 172 ( SELECT count(1) FROM "suggestion"
jbe@492 173 WHERE "suggestion"."initiative_id" = "initiative"."id"
jbe@492 174 AND COALESCE(
jbe@492 175 "suggestion"."id" > "sent"."last_suggestion_id",
jbe@492 176 TRUE
jbe@492 177 )
jbe@492 178 ) AS "new_suggestion_count",
jbe@478 179 TRUE AS "featured",
jbe@478 180 NOT EXISTS (
jbe@478 181 SELECT NULL FROM "initiative" AS "better_initiative"
jbe@478 182 WHERE
jbe@484 183 "better_initiative"."issue_id" = "initiative"."issue_id"
jbe@484 184 AND
jbe@484 185 ( COALESCE("better_initiative"."harmonic_weight", -1),
jbe@484 186 -"better_initiative"."id" ) >
jbe@484 187 ( COALESCE("initiative"."harmonic_weight", -1),
jbe@485 188 -"initiative"."id" )
jbe@478 189 ) AS "leading",
jbe@478 190 "initiative".*
jbe@478 191 FROM "member" CROSS JOIN "area"
jbe@478 192 CROSS JOIN LATERAL
jbe@492 193 "featured_initiative"("member"."id", "area"."id") AS "initiative"
jbe@492 194 LEFT JOIN "initiative_notification_sent" AS "sent"
jbe@492 195 ON "sent"."member_id" = "member"."id"
jbe@492 196 AND "sent"."initiative_id" = "initiative"."id";
jbe@478 197
jbe@478 198 CREATE VIEW "leading_complement_initiative" AS
jbe@478 199 SELECT * FROM (
jbe@478 200 SELECT DISTINCT ON ("seen_by_member_id", "initiative"."issue_id")
jbe@492 201 "uf_initiative"."seen_by_member_id",
jbe@492 202 "supporter"."member_id" NOTNULL AS "supported",
jbe@492 203 CASE WHEN "supporter"."member_id" NOTNULL THEN FALSE ELSE
jbe@492 204 EXISTS (
jbe@492 205 SELECT NULL FROM "draft"
jbe@492 206 WHERE "draft"."initiative_id" = "initiative"."id"
jbe@492 207 AND COALESCE(
jbe@492 208 "draft"."id" > "sent"."last_draft_id",
jbe@492 209 TRUE
jbe@492 210 )
jbe@492 211 )
jbe@492 212 END AS "new_draft",
jbe@492 213 CASE WHEN "supporter"."member_id" NOTNULL THEN 0 ELSE
jbe@492 214 ( SELECT count(1) FROM "suggestion"
jbe@492 215 WHERE "suggestion"."initiative_id" = "initiative"."id"
jbe@492 216 AND COALESCE(
jbe@492 217 "suggestion"."id" > "sent"."last_suggestion_id",
jbe@492 218 TRUE
jbe@492 219 )
jbe@492 220 )
jbe@492 221 END AS "new_suggestion_count",
jbe@478 222 FALSE AS "featured",
jbe@478 223 TRUE AS "leading",
jbe@478 224 "initiative".*
jbe@492 225 FROM "updated_or_featured_initiative" AS "uf_initiative"
jbe@492 226 JOIN "initiative" ON
jbe@492 227 "uf_initiative"."issue_id" = "initiative"."issue_id"
jbe@492 228 LEFT JOIN "supporter" ON
jbe@492 229 "supporter"."member_id" = "uf_initiative"."seen_by_member_id" AND
jbe@492 230 "supporter"."initiative_id" = "initiative"."id"
jbe@492 231 LEFT JOIN "initiative_notification_sent" AS "sent"
jbe@492 232 ON "sent"."member_id" = "uf_initiative"."seen_by_member_id"
jbe@492 233 AND "sent"."initiative_id" = "initiative"."id"
jbe@478 234 ORDER BY
jbe@478 235 "seen_by_member_id",
jbe@478 236 "initiative"."issue_id",
jbe@478 237 "initiative"."harmonic_weight" DESC,
jbe@478 238 "initiative"."id"
jbe@478 239 ) AS "subquery"
jbe@478 240 WHERE NOT EXISTS (
jbe@478 241 SELECT NULL FROM "updated_or_featured_initiative" AS "other"
jbe@478 242 WHERE "other"."seen_by_member_id" = "subquery"."seen_by_member_id"
jbe@478 243 AND "other"."id" = "subquery"."id"
jbe@478 244 );
jbe@478 245
jbe@492 246 CREATE VIEW "unfiltered_initiative_for_notification" AS
jbe@478 247 SELECT * FROM "updated_or_featured_initiative"
jbe@478 248 UNION ALL
jbe@478 249 SELECT * FROM "leading_complement_initiative";
jbe@478 250
jbe@492 251 CREATE VIEW "initiative_for_notification" AS
jbe@492 252 SELECT "initiative1".*
jbe@492 253 FROM "unfiltered_initiative_for_notification" "initiative1"
jbe@492 254 JOIN "issue" AS "issue1" ON "initiative1"."issue_id" = "issue1"."id"
jbe@492 255 WHERE EXISTS (
jbe@492 256 SELECT NULL
jbe@492 257 FROM "unfiltered_initiative_for_notification" "initiative2"
jbe@492 258 JOIN "issue" AS "issue2" ON "initiative2"."issue_id" = "issue2"."id"
jbe@492 259 WHERE "initiative1"."seen_by_member_id" = "initiative2"."seen_by_member_id"
jbe@492 260 AND "issue1"."area_id" = "issue2"."area_id"
jbe@492 261 AND ( "initiative2"."new_draft" OR "initiative2"."new_suggestion_count" > 0 )
jbe@492 262 );
jbe@492 263
jbe@492 264 CREATE FUNCTION "get_initiatives_for_notification"
jbe@492 265 ( "member_id_p" "member"."id"%TYPE )
jbe@492 266 RETURNS SETOF "initiative_for_notification"
jbe@492 267 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@492 268 DECLARE
jbe@492 269 "result_row" "initiative_for_notification"%ROWTYPE;
jbe@492 270 "last_draft_id_v" "draft"."id"%TYPE;
jbe@492 271 "last_suggestion_id_v" "suggestion"."id"%TYPE;
jbe@492 272 BEGIN
jbe@492 273 PERFORM "require_transaction_isolation"();
jbe@492 274 PERFORM NULL FROM "member" WHERE "id" = "member_id_p" FOR UPDATE;
jbe@492 275 FOR "result_row" IN
jbe@492 276 SELECT * FROM "initiative_for_notification"
jbe@492 277 WHERE "seen_by_member_id" = "member_id_p"
jbe@492 278 LOOP
jbe@492 279 SELECT "id" INTO "last_draft_id_v" FROM "draft"
jbe@492 280 WHERE "draft"."initiative_id" = "result_row"."id"
jbe@492 281 ORDER BY "id" DESC LIMIT 1;
jbe@492 282 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
jbe@492 283 WHERE "suggestion"."initiative_id" = "result_row"."id"
jbe@492 284 ORDER BY "id" DESC LIMIT 1;
jbe@492 285 INSERT INTO "initiative_notification_sent"
jbe@492 286 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
jbe@492 287 VALUES (
jbe@492 288 "member_id_p",
jbe@492 289 "result_row"."id",
jbe@492 290 "last_draft_id",
jbe@492 291 "last_suggestion_id" )
jbe@492 292 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
jbe@492 293 "last_draft_id" = CASE
jbe@492 294 WHEN "last_draft_id" > "last_draft_id_v"
jbe@492 295 THEN "last_draft_id"
jbe@492 296 ELSE "last_draft_id_v"
jbe@492 297 END,
jbe@492 298 "last_suggestion_id" = CASE
jbe@492 299 WHEN "last_suggestion_id" > "last_suggestion_id_v"
jbe@492 300 THEN "last_suggestion_id"
jbe@492 301 ELSE "last_suggestion_id_v"
jbe@492 302 END;
jbe@492 303 RETURN NEXT "result_row";
jbe@492 304 END LOOP;
jbe@492 305 DELETE FROM "initiative_notification_sent"
jbe@492 306 USING "initiative", "issue"
jbe@492 307 WHERE "initiative_notification_sent"."member_id" = "member_id_p"
jbe@492 308 AND "initiative"."id" = "initiative_notification_sent"."initiative_id"
jbe@492 309 AND "issue"."id" = "initiative"."issue_id"
jbe@492 310 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
jbe@492 311 UPDATE "member" SET "notification_counter" = "notification_counter" + 1
jbe@492 312 WHERE "id" = "member_id_p";
jbe@492 313 RETURN;
jbe@492 314 END;
jbe@492 315 $$;
jbe@492 316
jbe@478 317 COMMIT;

Impressum / About Us