liquid_feedback_core: 10b90162e982 update/core-update.v3.1.0-v3.2.0.sql

liquid_feedback_core

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

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

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 ALTER TABLE "member" ADD COLUMN "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE;

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

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

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

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

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

15 CREATE TABLE "subscription" (

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

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

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

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

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

23 CREATE TABLE "ignored_area" (

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

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

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

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

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

31 CREATE TABLE "initiative_notification_sent" (

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

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

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

35 "last_draft_id" INT8 NOT NULL,

36 "last_suggestion_id" INT8 );

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

39 CREATE TABLE "newsletter" (

40 "id" SERIAL4 PRIMARY KEY,

41 "published" TIMESTAMPTZ NOT NULL,

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

43 "include_all_members" BOOLEAN NOT NULL,

44 "sent" TIMESTAMPTZ,

45 "subject" TEXT NOT NULL,

46 "content" TEXT NOT NULL );

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

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

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

51 CREATE VIEW "updated_initiative" AS

52 SELECT

53 "supporter"."member_id" AS "seen_by_member_id",

54 TRUE AS "supported",

55 EXISTS (

56 SELECT NULL FROM "draft"

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

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

59 ) AS "new_draft",

60 ( SELECT count(1) FROM "suggestion"

61 LEFT JOIN "opinion" ON

62 "opinion"."member_id" = "supporter"."member_id" AND

63 "opinion"."suggestion_id" = "suggestion"."id"

64 WHERE "suggestion"."initiative_id" = "initiative"."id"

65 AND "opinion"."member_id" ISNULL

66 AND COALESCE(

67 "suggestion"."id" > "sent"."last_suggestion_id",

68 TRUE

69 )

70 ) AS "new_suggestion_count",

71 FALSE AS "featured",

72 NOT EXISTS (

73 SELECT NULL FROM "initiative" AS "better_initiative"

74 WHERE

75 "better_initiative"."issue_id" = "initiative"."issue_id"

76 AND

77 ( COALESCE("better_initiative"."harmonic_weight", -1),

78 -"better_initiative"."id" ) >

79 ( COALESCE("initiative"."harmonic_weight", -1),

80 -"initiative"."id" )

81 ) AS "leading",

82 "initiative".*

83 FROM "supporter" JOIN "initiative"

84 ON "supporter"."initiative_id" = "initiative"."id"

85 LEFT JOIN "initiative_notification_sent" AS "sent"

86 ON "sent"."member_id" = "supporter"."member_id"

87 AND "sent"."initiative_id" = "initiative"."id"

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

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

90 AND (

91 EXISTS (

92 SELECT NULL FROM "draft"

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

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

95 ) OR EXISTS (

96 SELECT NULL FROM "suggestion"

97 LEFT JOIN "opinion" ON

98 "opinion"."member_id" = "supporter"."member_id" AND

99 "opinion"."suggestion_id" = "suggestion"."id"

100 WHERE "suggestion"."initiative_id" = "initiative"."id"

101 AND "opinion"."member_id" ISNULL

102 AND COALESCE(

103 "suggestion"."id" > "sent"."last_suggestion_id",

104 TRUE

105 )

106 )

107 );

108

109 CREATE FUNCTION "featured_initiative"

110 ( "member_id_p" "member"."id"%TYPE,

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

112 RETURNS SETOF "initiative"

113 LANGUAGE 'plpgsql' STABLE AS $$

114 DECLARE

115 "member_row" "member"%ROWTYPE;

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

117 "seed_v" TEXT;

118 "result_row" "initiative"%ROWTYPE;

119 "match_v" BOOLEAN;

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

121 BEGIN

122 SELECT INTO "member_row" * FROM "member" WHERE "id" = "member_id_p";

123 "initiative_id_ary" := '{}';

124 LOOP

125 "match_v" := FALSE;

126 FOR "member_id_v", "seed_v" IN

127 SELECT * FROM (

128 SELECT DISTINCT

129 "supporter"."member_id",

130 md5("member_id_p" || '-' || "member_row"."notification_counter" || '-' || "area_id_p" || '-' || "supporter"."member_id") AS "seed"

131 FROM "supporter"

132 JOIN "member" ON "member"."id" = "supporter"."member_id"

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

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

135 WHERE "supporter"."member_id" != "member_id_p"

136 AND "issue"."area_id" = "area_id_p"

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

138 ) AS "subquery"

139 ORDER BY "seed"

140 LOOP

141 SELECT "initiative".* INTO "result_row"

142 FROM "initiative"

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

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

145 LEFT JOIN "supporter" AS "self_support" ON

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

147 "self_support"."member_id" = "member_id_p"

148 WHERE "supporter"."member_id" = "member_id_v"

149 AND "issue"."area_id" = "area_id_p"

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

151 AND "self_support"."member_id" ISNULL

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

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

154 LIMIT 1;

155 IF FOUND THEN

156 "match_v" := TRUE;

157 "initiative_id_ary" := "initiative_id_ary" || "result_row"."id";

158 RETURN NEXT "result_row";

159 IF array_length("initiative_id_ary", 1) >= "member_row"."notification_sample_size" THEN

160 RETURN;

161 END IF;

162 END IF;

163 END LOOP;

164 EXIT WHEN NOT "match_v";

165 END LOOP;

166 RETURN;

167 END;

168 $$;

169

170 CREATE VIEW "updated_or_featured_initiative" AS

171 SELECT * FROM "updated_initiative"

172 UNION ALL

173 SELECT

174 "member"."id" AS "seen_by_member_id",

175 FALSE AS "supported",

176 EXISTS (

177 SELECT NULL FROM "draft"

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

179 AND COALESCE(

180 "draft"."id" > "sent"."last_draft_id",

181 TRUE

182 )

183 ) AS "new_draft",

184 ( SELECT count(1) FROM "suggestion"

185 WHERE "suggestion"."initiative_id" = "initiative"."id"

186 AND COALESCE(

187 "suggestion"."id" > "sent"."last_suggestion_id",

188 TRUE

189 )

190 ) AS "new_suggestion_count",

191 TRUE AS "featured",

192 NOT EXISTS (

193 SELECT NULL FROM "initiative" AS "better_initiative"

194 WHERE

195 "better_initiative"."issue_id" = "initiative"."issue_id"

196 AND

197 ( COALESCE("better_initiative"."harmonic_weight", -1),

198 -"better_initiative"."id" ) >

199 ( COALESCE("initiative"."harmonic_weight", -1),

200 -"initiative"."id" )

201 ) AS "leading",

202 "initiative".*

203 FROM "member" CROSS JOIN "area"

204 CROSS JOIN LATERAL

205 "featured_initiative"("member"."id", "area"."id") AS "initiative"

206 LEFT JOIN "initiative_notification_sent" AS "sent"

207 ON "sent"."member_id" = "member"."id"

208 AND "sent"."initiative_id" = "initiative"."id";

209

210 CREATE VIEW "leading_complement_initiative" AS

211 SELECT * FROM (

212 SELECT DISTINCT ON ("seen_by_member_id", "initiative"."issue_id")

213 "uf_initiative"."seen_by_member_id",

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

215 CASE WHEN "supporter"."member_id" NOTNULL THEN FALSE ELSE

216 EXISTS (

217 SELECT NULL FROM "draft"

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

219 AND COALESCE(

220 "draft"."id" > "sent"."last_draft_id",

221 TRUE

222 )

223 )

224 END AS "new_draft",

225 CASE WHEN "supporter"."member_id" NOTNULL THEN 0 ELSE

226 ( SELECT count(1) FROM "suggestion"

227 WHERE "suggestion"."initiative_id" = "initiative"."id"

228 AND COALESCE(

229 "suggestion"."id" > "sent"."last_suggestion_id",

230 TRUE

231 )

232 )

233 END AS "new_suggestion_count",

234 FALSE AS "featured",

235 TRUE AS "leading",

236 "initiative".*

237 FROM "updated_or_featured_initiative" AS "uf_initiative"

238 JOIN "initiative" ON

239 "uf_initiative"."issue_id" = "initiative"."issue_id"

240 LEFT JOIN "supporter" ON

241 "supporter"."member_id" = "uf_initiative"."seen_by_member_id" AND

242 "supporter"."initiative_id" = "initiative"."id"

243 LEFT JOIN "initiative_notification_sent" AS "sent"

244 ON "sent"."member_id" = "uf_initiative"."seen_by_member_id"

245 AND "sent"."initiative_id" = "initiative"."id"

246 ORDER BY

247 "seen_by_member_id",

248 "initiative"."issue_id",

249 "initiative"."harmonic_weight" DESC,

250 "initiative"."id"

251 ) AS "subquery"

252 WHERE NOT EXISTS (

253 SELECT NULL FROM "updated_or_featured_initiative" AS "other"

254 WHERE "other"."seen_by_member_id" = "subquery"."seen_by_member_id"

255 AND "other"."id" = "subquery"."id"

256 );

257

258 CREATE VIEW "unfiltered_initiative_for_notification" AS

259 SELECT * FROM "updated_or_featured_initiative"

260 UNION ALL

261 SELECT * FROM "leading_complement_initiative";

262

263 CREATE VIEW "initiative_for_notification" AS

264 SELECT "initiative1".*

265 FROM "unfiltered_initiative_for_notification" "initiative1"

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

267 WHERE EXISTS (

268 SELECT NULL

269 FROM "unfiltered_initiative_for_notification" "initiative2"

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

271 WHERE "initiative1"."seen_by_member_id" = "initiative2"."seen_by_member_id"

272 AND "issue1"."area_id" = "issue2"."area_id"

273 AND ( "initiative2"."new_draft" OR "initiative2"."new_suggestion_count" > 0 )

274 );

275

276 CREATE VIEW "newsletter_to_send" AS

277 SELECT

278 "newsletter"."id" AS "newsletter_id",

279 "member"."id" AS "member_id"

280 FROM "newsletter" CROSS JOIN "member"

281 LEFT JOIN "privilege" ON

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

283 "privilege"."unit_id" = "newsletter"."unit_id" AND

284 "privilege"."voting_right" = TRUE

285 LEFT JOIN "subscription" ON

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

287 "subscription"."unit_id" = "newsletter"."unit_id"

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

289 AND "newsletter"."sent" ISNULL

290 AND "member"."locked" = FALSE

291 AND (

292 "member"."disable_notifications" = FALSE OR

293 "newsletter"."include_all_members" = TRUE )

294 AND (

295 "newsletter"."unit_id" ISNULL OR

296 "privilege"."member_id" NOTNULL OR

297 "subscription"."member_id" NOTNULL );

298

299 CREATE FUNCTION "get_initiatives_for_notification"

300 ( "member_id_p" "member"."id"%TYPE )

301 RETURNS SETOF "initiative_for_notification"

302 LANGUAGE 'plpgsql' VOLATILE AS $$

303 DECLARE

304 "result_row" "initiative_for_notification"%ROWTYPE;

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

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

307 BEGIN

308 PERFORM "require_transaction_isolation"();

309 PERFORM NULL FROM "member" WHERE "id" = "member_id_p" FOR UPDATE;

310 FOR "result_row" IN

311 SELECT * FROM "initiative_for_notification"

312 WHERE "seen_by_member_id" = "member_id_p"

313 LOOP

314 SELECT "id" INTO "last_draft_id_v" FROM "draft"

315 WHERE "draft"."initiative_id" = "result_row"."id"

316 ORDER BY "id" DESC LIMIT 1;

317 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"

318 WHERE "suggestion"."initiative_id" = "result_row"."id"

319 ORDER BY "id" DESC LIMIT 1;

320 INSERT INTO "initiative_notification_sent"

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

322 VALUES (

323 "member_id_p",

324 "result_row"."id",

325 "last_draft_id_v",

326 "last_suggestion_id_v" )

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

328 "last_draft_id" = CASE

329 WHEN "initiative_notification_sent"."last_draft_id" > "last_draft_id_v"

330 THEN "initiative_notification_sent"."last_draft_id"

331 ELSE "last_draft_id_v"

332 END,

333 "last_suggestion_id" = CASE

334 WHEN "initiative_notification_sent"."last_suggestion_id" > "last_suggestion_id_v"

335 THEN "initiative_notification_sent"."last_suggestion_id"

336 ELSE "last_suggestion_id_v"

337 END;

338 RETURN NEXT "result_row";

339 END LOOP;

340 DELETE FROM "initiative_notification_sent"

341 USING "initiative", "issue"

342 WHERE "initiative_notification_sent"."member_id" = "member_id_p"

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

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

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

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

347 WHERE "id" = "member_id_p";

348 RETURN;

349 END;

350 $$;

351

352 COMMIT;