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

liquid_feedback_core

view update/core-update.v3.1.0-v3.2.0.sql @ 502:f39c673420cb

Use "supporter_count" instead of "harmonic_weight" to determine leading initiative for notification system

author	jbe
date	Mon Apr 04 23:55:22 2016 +0200 (2016-04-04)
parents	4a8d9f00867b
children	986d8f3a6895

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 "ignored_initiative"."member_id" ISNULL

104 AND (

105 EXISTS (

106 SELECT NULL FROM "draft"

107 LEFT JOIN "ignored_member" ON

108 "ignored_member"."member_id" = "supporter"."member_id" AND

109 "ignored_member"."other_member_id" = "draft"."author_id"

110 WHERE "draft"."initiative_id" = "supporter"."initiative_id"

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

112 AND "ignored_member"."member_id" ISNULL

113 ) OR EXISTS (

114 SELECT NULL FROM "suggestion"

115 LEFT JOIN "opinion" ON

116 "opinion"."member_id" = "supporter"."member_id" AND

117 "opinion"."suggestion_id" = "suggestion"."id"

118 LEFT JOIN "ignored_member" ON

119 "ignored_member"."member_id" = "supporter"."member_id" AND

120 "ignored_member"."other_member_id" = "suggestion"."author_id"

121 WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"

122 AND "opinion"."member_id" ISNULL

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

124 AND "ignored_member"."member_id" ISNULL

125 )

126 );

127

128 CREATE FUNCTION "featured_initiative"

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

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

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

132 LANGUAGE 'plpgsql' STABLE AS $$

133 DECLARE

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

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

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

137 "match_v" BOOLEAN;

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

139 "seed_v" TEXT;

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

141 BEGIN

142 SELECT "notification_counter", "notification_sample_size"

143 INTO "counter_v", "sample_size_v"

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

145 "initiative_id_ary" := '{}';

146 LOOP

147 "match_v" := FALSE;

148 FOR "member_id_v", "seed_v" IN

149 SELECT * FROM (

150 SELECT DISTINCT

151 "supporter"."member_id",

152 md5(

153 "recipient_id_p" || '-' ||

154 "counter_v" || '-' ||

155 "area_id_p" || '-' ||

156 "supporter"."member_id"

157 ) AS "seed"

158 FROM "supporter"

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

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

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

162 AND "issue"."area_id" = "area_id_p"

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

164 ) AS "subquery"

165 ORDER BY "seed"

166 LOOP

167 SELECT "initiative"."id" INTO "initiative_id_v"

168 FROM "initiative"

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

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

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

172 LEFT JOIN "supporter" AS "self_support" ON

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

174 "self_support"."member_id" = "recipient_id_p"

175 LEFT JOIN "privilege" ON

176 "privilege"."member_id" = "recipient_id_p" AND

177 "privilege"."unit_id" = "area"."unit_id" AND

178 "privilege"."voting_right" = TRUE

179 LEFT JOIN "subscription" ON

180 "subscription"."member_id" = "recipient_id_p" AND

181 "subscription"."unit_id" = "area"."unit_id"

182 LEFT JOIN "ignored_initiative" ON

183 "ignored_initiative"."member_id" = "recipient_id_p" AND

184 "ignored_initiative"."initiative_id" = "initiative"."id"

185 WHERE "supporter"."member_id" = "member_id_v"

186 AND "issue"."area_id" = "area_id_p"

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

188 AND "self_support"."member_id" ISNULL

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

190 AND (

191 "privilege"."member_id" NOTNULL OR

192 "subscription"."member_id" NOTNULL )

193 AND "ignored_initiative"."member_id" ISNULL

194 AND NOT EXISTS (

195 SELECT NULL FROM "draft"

196 JOIN "ignored_member" ON

197 "ignored_member"."member_id" = "recipient_id_p" AND

198 "ignored_member"."other_member_id" = "draft"."author_id"

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

200 )

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

202 LIMIT 1;

203 IF FOUND THEN

204 "match_v" := TRUE;

205 RETURN NEXT "initiative_id_v";

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

207 RETURN;

208 END IF;

209 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";

210 END IF;

211 END LOOP;

212 EXIT WHEN NOT "match_v";

213 END LOOP;

214 RETURN;

215 END;

216 $$;

217

218 CREATE VIEW "updated_or_featured_initiative" AS

219 SELECT

220 "subquery".*,

221 NOT EXISTS (

222 SELECT NULL FROM "initiative" AS "better_initiative"

223 WHERE "better_initiative"."issue_id" = "initiative"."issue_id"

224 AND

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

226 -"better_initiative"."id" ) >

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

228 -"initiative"."id" )

229 ) AS "leading"

230 FROM (

231 SELECT * FROM "updated_initiative"

232 UNION ALL

233 SELECT

234 "member"."id" AS "recipient_id",

235 TRUE AS "featured",

236 "featured_initiative_id" AS "initiative_id"

237 FROM "member" CROSS JOIN "area"

238 CROSS JOIN LATERAL

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

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

241 ) AS "subquery"

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

243

244 CREATE VIEW "leading_complement_initiative" AS

245 SELECT * FROM (

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

247 "uf_initiative"."recipient_id",

248 FALSE AS "featured",

249 "uf_initiative"."initiative_id",

250 TRUE AS "leading"

251 FROM "updated_or_featured_initiative" AS "uf_initiative"

252 JOIN "initiative" AS "uf_initiative_full" ON

253 "uf_initiative_full"."id" = "uf_initiative"."initiative_id"

254 JOIN "initiative" ON

255 "initiative"."issue_id" = "uf_initiative_full"."issue_id"

256 ORDER BY

257 "uf_initiative"."recipient_id",

258 "initiative"."issue_id",

259 "initiative"."supporter_count" DESC,

260 "initiative"."id"

261 ) AS "subquery"

262 WHERE NOT EXISTS (

263 SELECT NULL FROM "updated_or_featured_initiative" AS "other"

264 WHERE "other"."recipient_id" = "subquery"."recipient_id"

265 AND "other"."initiative_id" = "subquery"."initiative_id"

266 );

267

268 CREATE VIEW "unfiltered_initiative_for_notification" AS

269 SELECT

270 "subquery".*,

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

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

273 EXISTS (

274 SELECT NULL FROM "draft"

275 WHERE "draft"."initiative_id" = "subquery"."initiative_id"

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

277 )

278 ELSE

279 EXISTS (

280 SELECT NULL FROM "draft"

281 WHERE "draft"."initiative_id" = "subquery"."initiative_id"

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

283 )

284 END AS "new_draft",

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

286 ( SELECT count(1) FROM "suggestion"

287 LEFT JOIN "opinion" ON

288 "opinion"."member_id" = "supporter"."member_id" AND

289 "opinion"."suggestion_id" = "suggestion"."id"

290 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"

291 AND "opinion"."member_id" ISNULL

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

293 )

294 ELSE

295 ( SELECT count(1) FROM "suggestion"

296 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"

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

298 )

299 END AS "new_suggestion_count"

300 FROM (

301 SELECT * FROM "updated_or_featured_initiative"

302 UNION ALL

303 SELECT * FROM "leading_complement_initiative"

304 ) AS "subquery"

305 LEFT JOIN "supporter" ON

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

307 "supporter"."initiative_id" = "subquery"."initiative_id"

308 LEFT JOIN "initiative_notification_sent" AS "sent" ON

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

310 "sent"."initiative_id" = "subquery"."initiative_id";

311

312 CREATE VIEW "initiative_for_notification" AS

313 SELECT "unfiltered1".*

314 FROM "unfiltered_initiative_for_notification" "unfiltered1"

315 JOIN "initiative" AS "initiative1" ON

316 "initiative1"."id" = "unfiltered1"."initiative_id"

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

318 WHERE EXISTS (

319 SELECT NULL

320 FROM "unfiltered_initiative_for_notification" "unfiltered2"

321 JOIN "initiative" AS "initiative2" ON

322 "initiative2"."id" = "unfiltered2"."initiative_id"

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

324 WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"

325 AND "issue1"."area_id" = "issue2"."area_id"

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

327 );

328

329 CREATE VIEW "newsletter_to_send" AS

330 SELECT

331 "member"."id" AS "recipient_id",

332 "newsletter"."id" AS "newsletter_id"

333 FROM "newsletter" CROSS JOIN "member"

334 LEFT JOIN "privilege" ON

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

336 "privilege"."unit_id" = "newsletter"."unit_id" AND

337 "privilege"."voting_right" = TRUE

338 LEFT JOIN "subscription" ON

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

340 "subscription"."unit_id" = "newsletter"."unit_id"

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

342 AND "newsletter"."sent" ISNULL

343 AND "member"."locked" = FALSE

344 AND (

345 "member"."disable_notifications" = FALSE OR

346 "newsletter"."include_all_members" = TRUE )

347 AND (

348 "newsletter"."unit_id" ISNULL OR

349 "privilege"."member_id" NOTNULL OR

350 "subscription"."member_id" NOTNULL );

351

352 CREATE FUNCTION "get_initiatives_for_notification"

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

354 RETURNS SETOF "initiative_for_notification"

355 LANGUAGE 'plpgsql' VOLATILE AS $$

356 DECLARE

357 "result_row" "initiative_for_notification"%ROWTYPE;

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

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

360 BEGIN

361 PERFORM "require_transaction_isolation"();

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

363 FOR "result_row" IN

364 SELECT * FROM "initiative_for_notification"

365 WHERE "recipient_id" = "recipient_id_p"

366 LOOP

367 SELECT "id" INTO "last_draft_id_v" FROM "draft"

368 WHERE "draft"."initiative_id" = "result_row"."initiative_id"

369 ORDER BY "id" DESC LIMIT 1;

370 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"

371 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"

372 ORDER BY "id" DESC LIMIT 1;

373 INSERT INTO "initiative_notification_sent"

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

375 VALUES (

376 "recipient_id_p",

377 "result_row"."initiative_id",

378 "last_draft_id_v",

379 "last_suggestion_id_v" )

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

381 "last_draft_id" = CASE

382 WHEN "initiative_notification_sent"."last_draft_id" > "last_draft_id_v"

383 THEN "initiative_notification_sent"."last_draft_id"

384 ELSE "last_draft_id_v"

385 END,

386 "last_suggestion_id" = CASE

387 WHEN "initiative_notification_sent"."last_suggestion_id" > "last_suggestion_id_v"

388 THEN "initiative_notification_sent"."last_suggestion_id"

389 ELSE "last_suggestion_id_v"

390 END;

391 RETURN NEXT "result_row";

392 END LOOP;

393 DELETE FROM "initiative_notification_sent"

394 USING "initiative", "issue"

395 WHERE "initiative_notification_sent"."member_id" = "recipient_id_p"

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

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

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

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

400 WHERE "id" = "recipient_id_p";

401 RETURN;

402 END;

403 $$;

404

405 COMMIT;