liquid_feedback_core

annotate update/core-update.v3.1.0-v3.2.0.sql @ 498:10b90162e982

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

Impressum / About Us