liquid_feedback_core: 986d8f3a6895 update/core-update.v3.1.0-v3.2.0.sql

liquid_feedback_core

view update/core-update.v3.1.0-v3.2.0.sql @ 503:986d8f3a6895

Exclude revoked initiatives from updated/featured/leading initiatives in notification system

author	jbe
date	Tue Apr 05 00:19:33 2016 +0200 (2016-04-05)
parents	f39c673420cb
children	d07e6a046d41

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

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

17 DROP VIEW "selected_event_seen_by_member";

18 DROP VIEW "event_seen_by_member";

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

20 DROP TYPE "notify_level";

22 CREATE TABLE "subscription" (

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

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

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

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

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

30 CREATE TABLE "ignored_area" (

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

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

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

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

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

38 CREATE TABLE "initiative_notification_sent" (

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

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

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

42 "last_draft_id" INT8 NOT NULL,

43 "last_suggestion_id" INT8 );

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

46 CREATE TABLE "newsletter" (

47 "id" SERIAL4 PRIMARY KEY,

48 "published" TIMESTAMPTZ NOT NULL,

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

50 "include_all_members" BOOLEAN NOT NULL,

51 "sent" TIMESTAMPTZ,

52 "subject" TEXT NOT NULL,

53 "content" TEXT NOT NULL );

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

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

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

58 CREATE VIEW "event_for_notification" AS

59 SELECT

60 "member"."id" AS "recipient_id",

61 "event".*

62 FROM "member" CROSS JOIN "event"

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

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

65 LEFT JOIN "privilege" ON

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

67 "privilege"."unit_id" = "area"."unit_id" AND

68 "privilege"."voting_right" = TRUE

69 LEFT JOIN "subscription" ON

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

71 "subscription"."unit_id" = "area"."unit_id"

72 LEFT JOIN "ignored_area" ON

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

74 "ignored_area"."area_id" = "issue"."area_id"

75 LEFT JOIN "interest" ON

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

77 "interest"."issue_id" = "event"."issue_id"

78 LEFT JOIN "supporter" ON

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

80 "supporter"."initiative_id" = "event"."initiative_id"

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

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

83 AND (

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

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

86 "supporter"."member_id" NOTNULL ) );

88 CREATE VIEW "updated_initiative" AS

89 SELECT

90 "supporter"."member_id" AS "recipient_id",

91 FALSE AS "featured",

92 "supporter"."initiative_id"

93 FROM "supporter"

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

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

96 LEFT JOIN "initiative_notification_sent" AS "sent" ON

97 "sent"."member_id" = "supporter"."member_id" AND

98 "sent"."initiative_id" = "supporter"."initiative_id"

99 LEFT JOIN "ignored_initiative" ON

100 "ignored_initiative"."member_id" = "supporter"."member_id" AND

101 "ignored_initiative"."initiative_id" = "supporter"."initiative_id"

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

103 AND "initiative"."revoked" ISNULL

104 AND "ignored_initiative"."member_id" ISNULL

105 AND (

106 EXISTS (

107 SELECT NULL FROM "draft"

108 LEFT JOIN "ignored_member" ON

109 "ignored_member"."member_id" = "supporter"."member_id" AND

110 "ignored_member"."other_member_id" = "draft"."author_id"

111 WHERE "draft"."initiative_id" = "supporter"."initiative_id"

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

113 AND "ignored_member"."member_id" ISNULL

114 ) OR EXISTS (

115 SELECT NULL FROM "suggestion"

116 LEFT JOIN "opinion" ON

117 "opinion"."member_id" = "supporter"."member_id" AND

118 "opinion"."suggestion_id" = "suggestion"."id"

119 LEFT JOIN "ignored_member" ON

120 "ignored_member"."member_id" = "supporter"."member_id" AND

121 "ignored_member"."other_member_id" = "suggestion"."author_id"

122 WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"

123 AND "opinion"."member_id" ISNULL

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

125 AND "ignored_member"."member_id" ISNULL

126 )

127 );

128

129 CREATE FUNCTION "featured_initiative"

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

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

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

133 LANGUAGE 'plpgsql' STABLE AS $$

134 DECLARE

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

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

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

138 "match_v" BOOLEAN;

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

140 "seed_v" TEXT;

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

142 BEGIN

143 SELECT "notification_counter", "notification_sample_size"

144 INTO "counter_v", "sample_size_v"

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

146 "initiative_id_ary" := '{}';

147 LOOP

148 "match_v" := FALSE;

149 FOR "member_id_v", "seed_v" IN

150 SELECT * FROM (

151 SELECT DISTINCT

152 "supporter"."member_id",

153 md5(

154 "recipient_id_p" || '-' ||

155 "counter_v" || '-' ||

156 "area_id_p" || '-' ||

157 "supporter"."member_id"

158 ) AS "seed"

159 FROM "supporter"

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

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

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

163 AND "issue"."area_id" = "area_id_p"

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

165 ) AS "subquery"

166 ORDER BY "seed"

167 LOOP

168 SELECT "initiative"."id" INTO "initiative_id_v"

169 FROM "initiative"

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

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

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

173 LEFT JOIN "supporter" AS "self_support" ON

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

175 "self_support"."member_id" = "recipient_id_p"

176 LEFT JOIN "privilege" ON

177 "privilege"."member_id" = "recipient_id_p" AND

178 "privilege"."unit_id" = "area"."unit_id" AND

179 "privilege"."voting_right" = TRUE

180 LEFT JOIN "subscription" ON

181 "subscription"."member_id" = "recipient_id_p" AND

182 "subscription"."unit_id" = "area"."unit_id"

183 LEFT JOIN "ignored_initiative" ON

184 "ignored_initiative"."member_id" = "recipient_id_p" AND

185 "ignored_initiative"."initiative_id" = "initiative"."id"

186 WHERE "supporter"."member_id" = "member_id_v"

187 AND "issue"."area_id" = "area_id_p"

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

189 AND "initiative"."revoked" ISNULL

190 AND "self_support"."member_id" ISNULL

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

192 AND (

193 "privilege"."member_id" NOTNULL OR

194 "subscription"."member_id" NOTNULL )

195 AND "ignored_initiative"."member_id" ISNULL

196 AND NOT EXISTS (

197 SELECT NULL FROM "draft"

198 JOIN "ignored_member" ON

199 "ignored_member"."member_id" = "recipient_id_p" AND

200 "ignored_member"."other_member_id" = "draft"."author_id"

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

202 )

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

204 LIMIT 1;

205 IF FOUND THEN

206 "match_v" := TRUE;

207 RETURN NEXT "initiative_id_v";

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

209 RETURN;

210 END IF;

211 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";

212 END IF;

213 END LOOP;

214 EXIT WHEN NOT "match_v";

215 END LOOP;

216 RETURN;

217 END;

218 $$;

219

220 CREATE VIEW "updated_or_featured_initiative" AS

221 SELECT

222 "subquery".*,

223 NOT EXISTS (

224 SELECT NULL FROM "initiative" AS "better_initiative"

225 WHERE "better_initiative"."issue_id" = "initiative"."issue_id"

226 AND

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

228 -"better_initiative"."id" ) >

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

230 -"initiative"."id" )

231 ) AS "leading"

232 FROM (

233 SELECT * FROM "updated_initiative"

234 UNION ALL

235 SELECT

236 "member"."id" AS "recipient_id",

237 TRUE AS "featured",

238 "featured_initiative_id" AS "initiative_id"

239 FROM "member" CROSS JOIN "area"

240 CROSS JOIN LATERAL

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

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

243 ) AS "subquery"

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

245

246 CREATE VIEW "leading_complement_initiative" AS

247 SELECT * FROM (

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

249 "uf_initiative"."recipient_id",

250 FALSE AS "featured",

251 "uf_initiative"."initiative_id",

252 TRUE AS "leading"

253 FROM "updated_or_featured_initiative" AS "uf_initiative"

254 JOIN "initiative" AS "uf_initiative_full" ON

255 "uf_initiative_full"."id" = "uf_initiative"."initiative_id"

256 JOIN "initiative" ON

257 "initiative"."issue_id" = "uf_initiative_full"."issue_id"

258 WHERE "initiative"."revoked" ISNULL

259 ORDER BY

260 "uf_initiative"."recipient_id",

261 "initiative"."issue_id",

262 "initiative"."supporter_count" DESC,

263 "initiative"."id"

264 ) AS "subquery"

265 WHERE NOT EXISTS (

266 SELECT NULL FROM "updated_or_featured_initiative" AS "other"

267 WHERE "other"."recipient_id" = "subquery"."recipient_id"

268 AND "other"."initiative_id" = "subquery"."initiative_id"

269 );

270

271 CREATE VIEW "unfiltered_initiative_for_notification" AS

272 SELECT

273 "subquery".*,

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

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

276 EXISTS (

277 SELECT NULL FROM "draft"

278 WHERE "draft"."initiative_id" = "subquery"."initiative_id"

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

280 )

281 ELSE

282 EXISTS (

283 SELECT NULL FROM "draft"

284 WHERE "draft"."initiative_id" = "subquery"."initiative_id"

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

286 )

287 END AS "new_draft",

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

289 ( SELECT count(1) FROM "suggestion"

290 LEFT JOIN "opinion" ON

291 "opinion"."member_id" = "supporter"."member_id" AND

292 "opinion"."suggestion_id" = "suggestion"."id"

293 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"

294 AND "opinion"."member_id" ISNULL

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

296 )

297 ELSE

298 ( SELECT count(1) FROM "suggestion"

299 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"

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

301 )

302 END AS "new_suggestion_count"

303 FROM (

304 SELECT * FROM "updated_or_featured_initiative"

305 UNION ALL

306 SELECT * FROM "leading_complement_initiative"

307 ) AS "subquery"

308 LEFT JOIN "supporter" ON

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

310 "supporter"."initiative_id" = "subquery"."initiative_id"

311 LEFT JOIN "initiative_notification_sent" AS "sent" ON

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

313 "sent"."initiative_id" = "subquery"."initiative_id";

314

315 CREATE VIEW "initiative_for_notification" AS

316 SELECT "unfiltered1".*

317 FROM "unfiltered_initiative_for_notification" "unfiltered1"

318 JOIN "initiative" AS "initiative1" ON

319 "initiative1"."id" = "unfiltered1"."initiative_id"

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

321 WHERE EXISTS (

322 SELECT NULL

323 FROM "unfiltered_initiative_for_notification" "unfiltered2"

324 JOIN "initiative" AS "initiative2" ON

325 "initiative2"."id" = "unfiltered2"."initiative_id"

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

327 WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"

328 AND "issue1"."area_id" = "issue2"."area_id"

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

330 );

331

332 CREATE VIEW "newsletter_to_send" AS

333 SELECT

334 "member"."id" AS "recipient_id",

335 "newsletter"."id" AS "newsletter_id"

336 FROM "newsletter" CROSS JOIN "member"

337 LEFT JOIN "privilege" ON

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

339 "privilege"."unit_id" = "newsletter"."unit_id" AND

340 "privilege"."voting_right" = TRUE

341 LEFT JOIN "subscription" ON

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

343 "subscription"."unit_id" = "newsletter"."unit_id"

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

345 AND "newsletter"."sent" ISNULL

346 AND "member"."locked" = FALSE

347 AND (

348 "member"."disable_notifications" = FALSE OR

349 "newsletter"."include_all_members" = TRUE )

350 AND (

351 "newsletter"."unit_id" ISNULL OR

352 "privilege"."member_id" NOTNULL OR

353 "subscription"."member_id" NOTNULL );

354

355 CREATE FUNCTION "get_initiatives_for_notification"

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

357 RETURNS SETOF "initiative_for_notification"

358 LANGUAGE 'plpgsql' VOLATILE AS $$

359 DECLARE

360 "result_row" "initiative_for_notification"%ROWTYPE;

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

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

363 BEGIN

364 PERFORM "require_transaction_isolation"();

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

366 FOR "result_row" IN

367 SELECT * FROM "initiative_for_notification"

368 WHERE "recipient_id" = "recipient_id_p"

369 LOOP

370 SELECT "id" INTO "last_draft_id_v" FROM "draft"

371 WHERE "draft"."initiative_id" = "result_row"."initiative_id"

372 ORDER BY "id" DESC LIMIT 1;

373 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"

374 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"

375 ORDER BY "id" DESC LIMIT 1;

376 INSERT INTO "initiative_notification_sent"

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

378 VALUES (

379 "recipient_id_p",

380 "result_row"."initiative_id",

381 "last_draft_id_v",

382 "last_suggestion_id_v" )

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

384 "last_draft_id" = CASE

385 WHEN "initiative_notification_sent"."last_draft_id" > "last_draft_id_v"

386 THEN "initiative_notification_sent"."last_draft_id"

387 ELSE "last_draft_id_v"

388 END,

389 "last_suggestion_id" = CASE

390 WHEN "initiative_notification_sent"."last_suggestion_id" > "last_suggestion_id_v"

391 THEN "initiative_notification_sent"."last_suggestion_id"

392 ELSE "last_suggestion_id_v"

393 END;

394 RETURN NEXT "result_row";

395 END LOOP;

396 DELETE FROM "initiative_notification_sent"

397 USING "initiative", "issue"

398 WHERE "initiative_notification_sent"."member_id" = "recipient_id_p"

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

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

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

402 UPDATE "member" SET "notification_counter" = "notification_counter" + 1

403 WHERE "id" = "recipient_id_p";

404 RETURN;

405 END;

406 $$;

407

408 COMMIT;