liquid_feedback_core
view update/core-update.v3.1.0-v3.2.0.sql @ 496:044af1eec28b
New table "newsletter"
| author | jbe |
|---|---|
| date | Sun Apr 03 20:46:10 2016 +0200 (2016-04-03) |
| parents | bb420abbc2fa |
| children | 91e3d31c1de2 |
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 );
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 $$;
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";
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 );
258 CREATE VIEW "unfiltered_initiative_for_notification" AS
259 SELECT * FROM "updated_or_featured_initiative"
260 UNION ALL
261 SELECT * FROM "leading_complement_initiative";
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 );
276 CREATE FUNCTION "get_initiatives_for_notification"
277 ( "member_id_p" "member"."id"%TYPE )
278 RETURNS SETOF "initiative_for_notification"
279 LANGUAGE 'plpgsql' VOLATILE AS $$
280 DECLARE
281 "result_row" "initiative_for_notification"%ROWTYPE;
282 "last_draft_id_v" "draft"."id"%TYPE;
283 "last_suggestion_id_v" "suggestion"."id"%TYPE;
284 BEGIN
285 PERFORM "require_transaction_isolation"();
286 PERFORM NULL FROM "member" WHERE "id" = "member_id_p" FOR UPDATE;
287 FOR "result_row" IN
288 SELECT * FROM "initiative_for_notification"
289 WHERE "seen_by_member_id" = "member_id_p"
290 LOOP
291 SELECT "id" INTO "last_draft_id_v" FROM "draft"
292 WHERE "draft"."initiative_id" = "result_row"."id"
293 ORDER BY "id" DESC LIMIT 1;
294 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
295 WHERE "suggestion"."initiative_id" = "result_row"."id"
296 ORDER BY "id" DESC LIMIT 1;
297 INSERT INTO "initiative_notification_sent"
298 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
299 VALUES (
300 "member_id_p",
301 "result_row"."id",
302 "last_draft_id_v",
303 "last_suggestion_id_v" )
304 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
305 "last_draft_id" = CASE
306 WHEN "initiative_notification_sent"."last_draft_id" > "last_draft_id_v"
307 THEN "initiative_notification_sent"."last_draft_id"
308 ELSE "last_draft_id_v"
309 END,
310 "last_suggestion_id" = CASE
311 WHEN "initiative_notification_sent"."last_suggestion_id" > "last_suggestion_id_v"
312 THEN "initiative_notification_sent"."last_suggestion_id"
313 ELSE "last_suggestion_id_v"
314 END;
315 RETURN NEXT "result_row";
316 END LOOP;
317 DELETE FROM "initiative_notification_sent"
318 USING "initiative", "issue"
319 WHERE "initiative_notification_sent"."member_id" = "member_id_p"
320 AND "initiative"."id" = "initiative_notification_sent"."initiative_id"
321 AND "issue"."id" = "initiative"."issue_id"
322 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
323 UPDATE "member" SET "notification_counter" = "notification_counter" + 1
324 WHERE "id" = "member_id_p";
325 RETURN;
326 END;
327 $$;
329 COMMIT;