liquid_feedback_core: be7942edfd1e update/core-update.v3.1.0-v3.2.0.sql

liquid_feedback_core

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

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

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 -- TODO: preliminary script

9 ALTER TABLE "member" ADD COLUMN "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE;

10 ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 1;

11 ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4 NOT NULL DEFAULT 3;

12 ALTER TABLE "member" ADD COLUMN "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6);

13 ALTER TABLE "member" ADD COLUMN "notification_hour" INT4 CHECK ("notification_hour" BETWEEN 0 AND 23);

14 ALTER TABLE "member" ADD COLUMN "notification_sent" TIMESTAMP;

16 UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none';

18 DROP VIEW "selected_event_seen_by_member";

19 DROP VIEW "event_seen_by_member";

20 ALTER TABLE "member" DROP COLUMN "notify_level";

21 DROP TYPE "notify_level";

23 CREATE TABLE "subscription" (

24 PRIMARY KEY ("member_id", "unit_id"),

25 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

26 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );

27 CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id");

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';

31 CREATE TABLE "ignored_area" (

32 PRIMARY KEY ("member_id", "area_id"),

33 "member_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

34 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE );

35 CREATE INDEX "ignored_area_area_id_idx" ON "ignored_area" ("area_id");

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';

39 CREATE TABLE "initiative_notification_sent" (

40 PRIMARY KEY ("member_id", "initiative_id"),

41 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

42 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

43 "last_draft_id" INT8 NOT NULL,

44 "last_suggestion_id" INT8 );

45 CREATE INDEX "initiative_notification_sent_initiative_idx" ON "initiative_notification_sent" ("initiative_id");

47 CREATE TABLE "newsletter" (

48 "id" SERIAL4 PRIMARY KEY,

49 "published" TIMESTAMPTZ NOT NULL,

50 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,

51 "include_all_members" BOOLEAN NOT NULL,

52 "sent" TIMESTAMPTZ,

53 "subject" TEXT NOT NULL,

54 "content" TEXT NOT NULL );

55 CREATE INDEX "newsletter_unit_id_idx" ON "newsletter" ("unit_id", "published");

56 CREATE INDEX "newsletter_all_units_published_idx" ON "newsletter" ("published") WHERE "unit_id" ISNULL;

57 CREATE INDEX "newsletter_published_idx" ON "newsletter" ("published");

59 CREATE VIEW "event_for_notification" AS

60 SELECT

61 "member"."id" AS "recipient_id",

62 "event".*

63 FROM "member" CROSS JOIN "event"

64 JOIN "issue" ON "issue"."id" = "event"."issue_id"

65 JOIN "area" ON "area"."id" = "issue"."area_id"

66 LEFT JOIN "privilege" ON

67 "privilege"."member_id" = "member"."id" AND

68 "privilege"."unit_id" = "area"."unit_id" AND

69 "privilege"."voting_right" = TRUE

70 LEFT JOIN "subscription" ON

71 "subscription"."member_id" = "member"."id" AND

72 "subscription"."unit_id" = "area"."unit_id"

73 LEFT JOIN "ignored_area" ON

74 "ignored_area"."member_id" = "member"."id" AND

75 "ignored_area"."area_id" = "issue"."area_id"

76 LEFT JOIN "interest" ON

77 "interest"."member_id" = "member"."id" AND

78 "interest"."issue_id" = "event"."issue_id"

79 LEFT JOIN "supporter" ON

80 "supporter"."member_id" = "member"."id" AND

81 "supporter"."initiative_id" = "event"."initiative_id"

82 WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL)

83 AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)

84 AND (

85 "event"."event" = 'issue_state_changed'::"event_type" OR

86 ( "event"."event" = 'initiative_revoked'::"event_type" AND

87 "supporter"."member_id" NOTNULL ) );

89 CREATE VIEW "updated_initiative" AS

90 SELECT

91 "supporter"."member_id" AS "recipient_id",

92 FALSE AS "featured",

93 "supporter"."initiative_id"

94 FROM "supporter"

95 JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id"

96 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"

97 LEFT JOIN "initiative_notification_sent" AS "sent" ON

98 "sent"."member_id" = "supporter"."member_id" AND

99 "sent"."initiative_id" = "supporter"."initiative_id"

100 LEFT JOIN "ignored_initiative" ON

101 "ignored_initiative"."member_id" = "supporter"."member_id" AND

102 "ignored_initiative"."initiative_id" = "supporter"."initiative_id"

103 WHERE "issue"."state" IN ('admission', 'discussion')

104 AND "initiative"."revoked" ISNULL

105 AND "ignored_initiative"."member_id" ISNULL

106 AND (

107 EXISTS (

108 SELECT NULL FROM "draft"

109 LEFT JOIN "ignored_member" ON

110 "ignored_member"."member_id" = "supporter"."member_id" AND

111 "ignored_member"."other_member_id" = "draft"."author_id"

112 WHERE "draft"."initiative_id" = "supporter"."initiative_id"

113 AND "draft"."id" > "supporter"."draft_id"

114 AND "ignored_member"."member_id" ISNULL

115 ) OR EXISTS (

116 SELECT NULL FROM "suggestion"

117 LEFT JOIN "opinion" ON

118 "opinion"."member_id" = "supporter"."member_id" AND

119 "opinion"."suggestion_id" = "suggestion"."id"

120 LEFT JOIN "ignored_member" ON

121 "ignored_member"."member_id" = "supporter"."member_id" AND

122 "ignored_member"."other_member_id" = "suggestion"."author_id"

123 WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"

124 AND "opinion"."member_id" ISNULL

125 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)

126 AND "ignored_member"."member_id" ISNULL

127 )

128 );

129

130 CREATE FUNCTION "featured_initiative"

131 ( "recipient_id_p" "member"."id"%TYPE,

132 "area_id_p" "area"."id"%TYPE )

133 RETURNS SETOF "initiative"."id"%TYPE

134 LANGUAGE 'plpgsql' STABLE AS $$

135 DECLARE

136 "counter_v" "member"."notification_counter"%TYPE;

137 "sample_size_v" "member"."notification_sample_size"%TYPE;

138 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]

139 "match_v" BOOLEAN;

140 "member_id_v" "member"."id"%TYPE;

141 "seed_v" TEXT;

142 "initiative_id_v" "initiative"."id"%TYPE;

143 BEGIN

144 SELECT "notification_counter", "notification_sample_size"

145 INTO "counter_v", "sample_size_v"

146 FROM "member" WHERE "id" = "recipient_id_p";

147 "initiative_id_ary" := '{}';

148 LOOP

149 "match_v" := FALSE;

150 FOR "member_id_v", "seed_v" IN

151 SELECT * FROM (

152 SELECT DISTINCT

153 "supporter"."member_id",

154 md5(

155 "recipient_id_p" || '-' ||

156 "counter_v" || '-' ||

157 "area_id_p" || '-' ||

158 "supporter"."member_id"

159 ) AS "seed"

160 FROM "supporter"

161 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"

162 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"

163 WHERE "supporter"."member_id" != "recipient_id_p"

164 AND "issue"."area_id" = "area_id_p"

165 AND "issue"."state" IN ('admission', 'discussion', 'verification')

166 ) AS "subquery"

167 ORDER BY "seed"

168 LOOP

169 SELECT "initiative"."id" INTO "initiative_id_v"

170 FROM "initiative"

171 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"

172 JOIN "area" ON "area"."id" = "issue"."area_id"

173 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"

174 LEFT JOIN "supporter" AS "self_support" ON

175 "self_support"."initiative_id" = "initiative"."id" AND

176 "self_support"."member_id" = "recipient_id_p"

177 LEFT JOIN "privilege" ON

178 "privilege"."member_id" = "recipient_id_p" AND

179 "privilege"."unit_id" = "area"."unit_id" AND

180 "privilege"."voting_right" = TRUE

181 LEFT JOIN "subscription" ON

182 "subscription"."member_id" = "recipient_id_p" AND

183 "subscription"."unit_id" = "area"."unit_id"

184 LEFT JOIN "ignored_initiative" ON

185 "ignored_initiative"."member_id" = "recipient_id_p" AND

186 "ignored_initiative"."initiative_id" = "initiative"."id"

187 WHERE "supporter"."member_id" = "member_id_v"

188 AND "issue"."area_id" = "area_id_p"

189 AND "issue"."state" IN ('admission', 'discussion', 'verification')

190 AND "initiative"."revoked" ISNULL

191 AND "self_support"."member_id" ISNULL

192 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]

193 AND (

194 "privilege"."member_id" NOTNULL OR

195 "subscription"."member_id" NOTNULL )

196 AND "ignored_initiative"."member_id" ISNULL

197 AND NOT EXISTS (

198 SELECT NULL FROM "draft"

199 JOIN "ignored_member" ON

200 "ignored_member"."member_id" = "recipient_id_p" AND

201 "ignored_member"."other_member_id" = "draft"."author_id"

202 WHERE "draft"."initiative_id" = "initiative"."id"

203 )

204 ORDER BY md5("seed_v" || '-' || "initiative"."id")

205 LIMIT 1;

206 IF FOUND THEN

207 "match_v" := TRUE;

208 RETURN NEXT "initiative_id_v";

209 IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN

210 RETURN;

211 END IF;

212 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";

213 END IF;

214 END LOOP;

215 EXIT WHEN NOT "match_v";

216 END LOOP;

217 RETURN;

218 END;

219 $$;

220

221 CREATE VIEW "updated_or_featured_initiative" AS

222 SELECT

223 "subquery".*,

224 NOT EXISTS (

225 SELECT NULL FROM "initiative" AS "better_initiative"

226 WHERE "better_initiative"."issue_id" = "initiative"."issue_id"

227 AND

228 ( COALESCE("better_initiative"."supporter_count", -1),

229 -"better_initiative"."id" ) >

230 ( COALESCE("initiative"."supporter_count", -1),

231 -"initiative"."id" )

232 ) AS "leading"

233 FROM (

234 SELECT * FROM "updated_initiative"

235 UNION ALL

236 SELECT

237 "member"."id" AS "recipient_id",

238 TRUE AS "featured",

239 "featured_initiative_id" AS "initiative_id"

240 FROM "member" CROSS JOIN "area"

241 CROSS JOIN LATERAL

242 "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id"

243 JOIN "initiative" ON "initiative"."id" = "featured_initiative_id"

244 ) AS "subquery"

245 JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id";

246

247 CREATE VIEW "leading_complement_initiative" AS

248 SELECT * FROM (

249 SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id")

250 "uf_initiative"."recipient_id",

251 FALSE AS "featured",

252 "uf_initiative"."initiative_id",

253 TRUE AS "leading"

254 FROM "updated_or_featured_initiative" AS "uf_initiative"

255 JOIN "initiative" AS "uf_initiative_full" ON

256 "uf_initiative_full"."id" = "uf_initiative"."initiative_id"

257 JOIN "initiative" ON

258 "initiative"."issue_id" = "uf_initiative_full"."issue_id"

259 WHERE "initiative"."revoked" ISNULL

260 ORDER BY

261 "uf_initiative"."recipient_id",

262 "initiative"."issue_id",

263 "initiative"."supporter_count" DESC,

264 "initiative"."id"

265 ) AS "subquery"

266 WHERE NOT EXISTS (

267 SELECT NULL FROM "updated_or_featured_initiative" AS "other"

268 WHERE "other"."recipient_id" = "subquery"."recipient_id"

269 AND "other"."initiative_id" = "subquery"."initiative_id"

270 );

271

272 CREATE VIEW "unfiltered_initiative_for_notification" AS

273 SELECT

274 "subquery".*,

275 "supporter"."member_id" NOTNULL AS "supported",

276 CASE WHEN "supporter"."member_id" NOTNULL THEN

277 EXISTS (

278 SELECT NULL FROM "draft"

279 WHERE "draft"."initiative_id" = "subquery"."initiative_id"

280 AND "draft"."id" > "supporter"."draft_id"

281 )

282 ELSE

283 EXISTS (

284 SELECT NULL FROM "draft"

285 WHERE "draft"."initiative_id" = "subquery"."initiative_id"

286 AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE)

287 )

288 END AS "new_draft",

289 CASE WHEN "supporter"."member_id" NOTNULL THEN

290 ( SELECT count(1) FROM "suggestion"

291 LEFT JOIN "opinion" ON

292 "opinion"."member_id" = "supporter"."member_id" AND

293 "opinion"."suggestion_id" = "suggestion"."id"

294 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"

295 AND "opinion"."member_id" ISNULL

296 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)

297 )

298 ELSE

299 ( SELECT count(1) FROM "suggestion"

300 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"

301 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)

302 )

303 END AS "new_suggestion_count"

304 FROM (

305 SELECT * FROM "updated_or_featured_initiative"

306 UNION ALL

307 SELECT * FROM "leading_complement_initiative"

308 ) AS "subquery"

309 LEFT JOIN "supporter" ON

310 "supporter"."member_id" = "subquery"."recipient_id" AND

311 "supporter"."initiative_id" = "subquery"."initiative_id"

312 LEFT JOIN "initiative_notification_sent" AS "sent" ON

313 "sent"."member_id" = "subquery"."recipient_id" AND

314 "sent"."initiative_id" = "subquery"."initiative_id";

315

316 CREATE VIEW "initiative_for_notification" AS

317 SELECT "unfiltered1".*

318 FROM "unfiltered_initiative_for_notification" "unfiltered1"

319 JOIN "initiative" AS "initiative1" ON

320 "initiative1"."id" = "unfiltered1"."initiative_id"

321 JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id"

322 WHERE EXISTS (

323 SELECT NULL

324 FROM "unfiltered_initiative_for_notification" "unfiltered2"

325 JOIN "initiative" AS "initiative2" ON

326 "initiative2"."id" = "unfiltered2"."initiative_id"

327 JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id"

328 WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"

329 AND "issue1"."area_id" = "issue2"."area_id"

330 AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 )

331 );

332

333 CREATE VIEW "scheduled_notification_to_send" AS

334 SELECT * FROM (

335 SELECT

336 "id" AS "recipient_id",

337 now() - CASE WHEN "notification_dow" ISNULL THEN

338 ( "notification_sent"::DATE + CASE

339 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"

340 THEN 0 ELSE 1 END

341 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"

342 ELSE

343 ( "notification_sent"::DATE +

344 ( 7 + "notification_dow" -

345 EXTRACT(DOW FROM

346 ( "notification_sent"::DATE + CASE

347 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"

348 THEN 0 ELSE 1 END

349 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"

350 )::INTEGER

351 ) % 7 +

352 CASE

353 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"

354 THEN 0 ELSE 1

355 END

356 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"

357 END AS "pending"

358 FROM (

359 SELECT

360 "id",

361 COALESCE("notification_sent", "activated") AS "notification_sent",

362 "notification_dow",

363 "notification_hour"

364 FROM "member"

365 WHERE "disable_notifications" = FALSE

366 AND "notification_hour" NOTNULL

367 ) AS "subquery1"

368 ) AS "subquery2"

369 WHERE "pending" > '0'::INTERVAL;

370

371 CREATE VIEW "newsletter_to_send" AS

372 SELECT

373 "member"."id" AS "recipient_id",

374 "newsletter"."id" AS "newsletter_id"

375 FROM "newsletter" CROSS JOIN "member"

376 LEFT JOIN "privilege" ON

377 "privilege"."member_id" = "member"."id" AND

378 "privilege"."unit_id" = "newsletter"."unit_id" AND

379 "privilege"."voting_right" = TRUE

380 LEFT JOIN "subscription" ON

381 "subscription"."member_id" = "member"."id" AND

382 "subscription"."unit_id" = "newsletter"."unit_id"

383 WHERE "newsletter"."published" <= now()

384 AND "newsletter"."sent" ISNULL

385 AND "member"."locked" = FALSE

386 AND (

387 "member"."disable_notifications" = FALSE OR

388 "newsletter"."include_all_members" = TRUE )

389 AND (

390 "newsletter"."unit_id" ISNULL OR

391 "privilege"."member_id" NOTNULL OR

392 "subscription"."member_id" NOTNULL );

393

394 CREATE FUNCTION "get_initiatives_for_notification"

395 ( "recipient_id_p" "member"."id"%TYPE )

396 RETURNS SETOF "initiative_for_notification"

397 LANGUAGE 'plpgsql' VOLATILE AS $$

398 DECLARE

399 "result_row" "initiative_for_notification"%ROWTYPE;

400 "last_draft_id_v" "draft"."id"%TYPE;

401 "last_suggestion_id_v" "suggestion"."id"%TYPE;

402 BEGIN

403 PERFORM "require_transaction_isolation"();

404 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;

405 FOR "result_row" IN

406 SELECT * FROM "initiative_for_notification"

407 WHERE "recipient_id" = "recipient_id_p"

408 LOOP

409 SELECT "id" INTO "last_draft_id_v" FROM "draft"

410 WHERE "draft"."initiative_id" = "result_row"."initiative_id"

411 ORDER BY "id" DESC LIMIT 1;

412 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"

413 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"

414 ORDER BY "id" DESC LIMIT 1;

415 INSERT INTO "initiative_notification_sent"

416 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")

417 VALUES (

418 "recipient_id_p",

419 "result_row"."initiative_id",

420 "last_draft_id_v",

421 "last_suggestion_id_v" )

422 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET

423 "last_draft_id" = CASE

424 WHEN "initiative_notification_sent"."last_draft_id" > "last_draft_id_v"

425 THEN "initiative_notification_sent"."last_draft_id"

426 ELSE "last_draft_id_v"

427 END,

428 "last_suggestion_id" = CASE

429 WHEN "initiative_notification_sent"."last_suggestion_id" > "last_suggestion_id_v"

430 THEN "initiative_notification_sent"."last_suggestion_id"

431 ELSE "last_suggestion_id_v"

432 END;

433 RETURN NEXT "result_row";

434 END LOOP;

435 DELETE FROM "initiative_notification_sent"

436 USING "initiative", "issue"

437 WHERE "initiative_notification_sent"."member_id" = "recipient_id_p"

438 AND "initiative"."id" = "initiative_notification_sent"."initiative_id"

439 AND "issue"."id" = "initiative"."issue_id"

440 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );

441 UPDATE "member" SET

442 "notification_counter" = "notification_counter" + 1 AND

443 "notification_sent" = now()

444 WHERE "id" = "recipient_id_p";

445 RETURN;

446 END;

447 $$;

448

449 COMMIT;