liquid_feedback_core: 917a65357efb update/core-update.v3.1.0-v3.2.0.sql

liquid_feedback_core

view update/core-update.v3.1.0-v3.2.0.sql @ 492:917a65357efb

Updated preliminary update script for new notification system

author	jbe
date	Sun Apr 03 19:48:32 2016 +0200 (2016-04-03)
parents	e431f1b73c35
children	d932363da4ee

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

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

39 CREATE VIEW "updated_initiative" AS

40 SELECT

41 "supporter"."member_id" AS "seen_by_member_id",

42 TRUE AS "supported",

43 EXISTS (

44 SELECT NULL FROM "draft"

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

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

47 ) AS "new_draft",

48 ( SELECT count(1) FROM "suggestion"

49 LEFT JOIN "opinion" ON

50 "opinion"."member_id" = "supporter"."member_id" AND

51 "opinion"."suggestion_id" = "suggestion"."id"

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

53 AND "opinion"."member_id" ISNULL

54 AND COALESCE(

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

56 TRUE

57 )

58 ) AS "new_suggestion_count",

59 FALSE AS "featured",

60 NOT EXISTS (

61 SELECT NULL FROM "initiative" AS "better_initiative"

62 WHERE

63 "better_initiative"."issue_id" = "initiative"."issue_id"

64 AND

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

66 -"better_initiative"."id" ) >

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

68 -"initiative"."id" )

69 ) AS "leading",

70 "initiative".*

71 FROM "supporter" JOIN "initiative"

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

73 LEFT JOIN "initiative_notification_sent" AS "sent"

74 ON "sent"."member_id" = "supporter"."member_id"

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

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

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

78 AND (

79 EXISTS (

80 SELECT NULL FROM "draft"

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

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

83 ) OR EXISTS (

84 SELECT NULL FROM "suggestion"

85 LEFT JOIN "opinion" ON

86 "opinion"."member_id" = "supporter"."member_id" AND

87 "opinion"."suggestion_id" = "suggestion"."id"

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

89 AND "opinion"."member_id" ISNULL

90 AND COALESCE(

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

92 TRUE

93 )

94 )

95 );

97 CREATE FUNCTION "featured_initiative"

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

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

100 RETURNS SETOF "initiative"

101 LANGUAGE 'plpgsql' STABLE AS $$

102 DECLARE

103 "member_row" "member"%ROWTYPE;

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

105 "seed_v" TEXT;

106 "result_row" "initiative"%ROWTYPE;

107 "match_v" BOOLEAN;

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

109 BEGIN

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

111 "initiative_id_ary" := '{}';

112 LOOP

113 "match_v" := FALSE;

114 FOR "member_id_v", "seed_v" IN

115 SELECT * FROM (

116 SELECT DISTINCT

117 "supporter"."member_id",

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

119 FROM "supporter"

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

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

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

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

124 AND "issue"."area_id" = "area_id_p"

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

126 ) AS "subquery"

127 ORDER BY "seed"

128 LOOP

129 SELECT "initiative".* INTO "result_row"

130 FROM "initiative"

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

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

133 LEFT JOIN "supporter" AS "self_support" ON

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

135 "self_support"."member_id" = "member_id_p"

136 WHERE "supporter"."member_id" = "member_id_v"

137 AND "issue"."area_id" = "area_id_p"

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

139 AND "self_support"."member_id" ISNULL

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

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

142 LIMIT 1;

143 IF FOUND THEN

144 "match_v" := TRUE;

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

146 RETURN NEXT "result_row";

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

148 RETURN;

149 END IF;

150 END IF;

151 END LOOP;

152 EXIT WHEN NOT "match_v";

153 END LOOP;

154 RETURN;

155 END;

156 $$;

157

158 CREATE VIEW "updated_or_featured_initiative" AS

159 SELECT * FROM "updated_initiative"

160 UNION ALL

161 SELECT

162 "member"."id" AS "seen_by_member_id",

163 FALSE AS "supported",

164 EXISTS (

165 SELECT NULL FROM "draft"

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

167 AND COALESCE(

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

169 TRUE

170 )

171 ) AS "new_draft",

172 ( SELECT count(1) FROM "suggestion"

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

174 AND COALESCE(

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

176 TRUE

177 )

178 ) AS "new_suggestion_count",

179 TRUE AS "featured",

180 NOT EXISTS (

181 SELECT NULL FROM "initiative" AS "better_initiative"

182 WHERE

183 "better_initiative"."issue_id" = "initiative"."issue_id"

184 AND

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

186 -"better_initiative"."id" ) >

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

188 -"initiative"."id" )

189 ) AS "leading",

190 "initiative".*

191 FROM "member" CROSS JOIN "area"

192 CROSS JOIN LATERAL

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

194 LEFT JOIN "initiative_notification_sent" AS "sent"

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

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

197

198 CREATE VIEW "leading_complement_initiative" AS

199 SELECT * FROM (

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

201 "uf_initiative"."seen_by_member_id",

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

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

204 EXISTS (

205 SELECT NULL FROM "draft"

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

207 AND COALESCE(

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

209 TRUE

210 )

211 )

212 END AS "new_draft",

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

214 ( SELECT count(1) FROM "suggestion"

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

216 AND COALESCE(

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

218 TRUE

219 )

220 )

221 END AS "new_suggestion_count",

222 FALSE AS "featured",

223 TRUE AS "leading",

224 "initiative".*

225 FROM "updated_or_featured_initiative" AS "uf_initiative"

226 JOIN "initiative" ON

227 "uf_initiative"."issue_id" = "initiative"."issue_id"

228 LEFT JOIN "supporter" ON

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

230 "supporter"."initiative_id" = "initiative"."id"

231 LEFT JOIN "initiative_notification_sent" AS "sent"

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

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

234 ORDER BY

235 "seen_by_member_id",

236 "initiative"."issue_id",

237 "initiative"."harmonic_weight" DESC,

238 "initiative"."id"

239 ) AS "subquery"

240 WHERE NOT EXISTS (

241 SELECT NULL FROM "updated_or_featured_initiative" AS "other"

242 WHERE "other"."seen_by_member_id" = "subquery"."seen_by_member_id"

243 AND "other"."id" = "subquery"."id"

244 );

245

246 CREATE VIEW "unfiltered_initiative_for_notification" AS

247 SELECT * FROM "updated_or_featured_initiative"

248 UNION ALL

249 SELECT * FROM "leading_complement_initiative";

250

251 CREATE VIEW "initiative_for_notification" AS

252 SELECT "initiative1".*

253 FROM "unfiltered_initiative_for_notification" "initiative1"

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

255 WHERE EXISTS (

256 SELECT NULL

257 FROM "unfiltered_initiative_for_notification" "initiative2"

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

259 WHERE "initiative1"."seen_by_member_id" = "initiative2"."seen_by_member_id"

260 AND "issue1"."area_id" = "issue2"."area_id"

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

262 );

263

264 CREATE FUNCTION "get_initiatives_for_notification"

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

266 RETURNS SETOF "initiative_for_notification"

267 LANGUAGE 'plpgsql' VOLATILE AS $$

268 DECLARE

269 "result_row" "initiative_for_notification"%ROWTYPE;

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

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

272 BEGIN

273 PERFORM "require_transaction_isolation"();

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

275 FOR "result_row" IN

276 SELECT * FROM "initiative_for_notification"

277 WHERE "seen_by_member_id" = "member_id_p"

278 LOOP

279 SELECT "id" INTO "last_draft_id_v" FROM "draft"

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

281 ORDER BY "id" DESC LIMIT 1;

282 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"

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

284 ORDER BY "id" DESC LIMIT 1;

285 INSERT INTO "initiative_notification_sent"

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

287 VALUES (

288 "member_id_p",

289 "result_row"."id",

290 "last_draft_id",

291 "last_suggestion_id" )

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

293 "last_draft_id" = CASE

294 WHEN "last_draft_id" > "last_draft_id_v"

295 THEN "last_draft_id"

296 ELSE "last_draft_id_v"

297 END,

298 "last_suggestion_id" = CASE

299 WHEN "last_suggestion_id" > "last_suggestion_id_v"

300 THEN "last_suggestion_id"

301 ELSE "last_suggestion_id_v"

302 END;

303 RETURN NEXT "result_row";

304 END LOOP;

305 DELETE FROM "initiative_notification_sent"

306 USING "initiative", "issue"

307 WHERE "initiative_notification_sent"."member_id" = "member_id_p"

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

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

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

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

312 WHERE "id" = "member_id_p";

313 RETURN;

314 END;

315 $$;

316

317 COMMIT;