| rev |
line source |
|
jbe@478
|
1 BEGIN;
|
|
jbe@478
|
2
|
|
jbe@478
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
|
jbe@478
|
4 SELECT * FROM (VALUES ('3.2.0', 3, 2, 0))
|
|
jbe@478
|
5 AS "subquery"("string", "major", "minor", "revision");
|
|
jbe@478
|
6
|
|
jbe@499
|
7 -- TODO: preliminary script
|
|
jbe@499
|
8
|
|
jbe@478
|
9 ALTER TABLE "member" ADD COLUMN "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE;
|
|
jbe@492
|
10 ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 1;
|
|
jbe@492
|
11 ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4 NOT NULL DEFAULT 3;
|
|
jbe@492
|
12 ALTER TABLE "member" ADD COLUMN "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6);
|
|
jbe@492
|
13 ALTER TABLE "member" ADD COLUMN "notification_hour" INT4 CHECK ("notification_hour" BETWEEN 0 AND 23);
|
|
jbe@478
|
14
|
|
jbe@478
|
15 UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none';
|
|
jbe@499
|
16
|
|
jbe@500
|
17 DROP VIEW "selected_event_seen_by_member";
|
|
jbe@500
|
18 DROP VIEW "event_seen_by_member";
|
|
jbe@499
|
19 ALTER TABLE "member" DROP COLUMN "notify_level";
|
|
jbe@499
|
20 DROP TYPE "notify_level";
|
|
jbe@478
|
21
|
|
jbe@478
|
22 CREATE TABLE "subscription" (
|
|
jbe@478
|
23 PRIMARY KEY ("member_id", "unit_id"),
|
|
jbe@478
|
24 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@478
|
25 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
|
|
jbe@478
|
26 CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id");
|
|
jbe@492
|
27
|
|
jbe@492
|
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';
|
|
jbe@492
|
29
|
|
jbe@492
|
30 CREATE TABLE "ignored_area" (
|
|
jbe@492
|
31 PRIMARY KEY ("member_id", "area_id"),
|
|
jbe@492
|
32 "member_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@492
|
33 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
|
|
jbe@492
|
34 CREATE INDEX "ignored_area_area_id_idx" ON "ignored_area" ("area_id");
|
|
jbe@492
|
35
|
|
jbe@492
|
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';
|
|
jbe@492
|
37
|
|
jbe@492
|
38 CREATE TABLE "initiative_notification_sent" (
|
|
jbe@492
|
39 PRIMARY KEY ("member_id", "initiative_id"),
|
|
jbe@492
|
40 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@492
|
41 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@492
|
42 "last_draft_id" INT8 NOT NULL,
|
|
jbe@495
|
43 "last_suggestion_id" INT8 );
|
|
jbe@492
|
44 CREATE INDEX "initiative_notification_sent_initiative_idx" ON "initiative_notification_sent" ("initiative_id");
|
|
jbe@478
|
45
|
|
jbe@496
|
46 CREATE TABLE "newsletter" (
|
|
jbe@496
|
47 "id" SERIAL4 PRIMARY KEY,
|
|
jbe@496
|
48 "published" TIMESTAMPTZ NOT NULL,
|
|
jbe@496
|
49 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@496
|
50 "include_all_members" BOOLEAN NOT NULL,
|
|
jbe@496
|
51 "sent" TIMESTAMPTZ,
|
|
jbe@496
|
52 "subject" TEXT NOT NULL,
|
|
jbe@496
|
53 "content" TEXT NOT NULL );
|
|
jbe@496
|
54 CREATE INDEX "newsletter_unit_id_idx" ON "newsletter" ("unit_id", "published");
|
|
jbe@496
|
55 CREATE INDEX "newsletter_all_units_published_idx" ON "newsletter" ("published") WHERE "unit_id" ISNULL;
|
|
jbe@496
|
56 CREATE INDEX "newsletter_published_idx" ON "newsletter" ("published");
|
|
jbe@496
|
57
|
|
jbe@499
|
58 CREATE VIEW "event_for_notification" AS
|
|
jbe@499
|
59 SELECT
|
|
jbe@499
|
60 "member"."id" AS "recipient_id",
|
|
jbe@499
|
61 "event".*
|
|
jbe@499
|
62 FROM "member" CROSS JOIN "event"
|
|
jbe@499
|
63 JOIN "issue" ON "issue"."id" = "event"."issue_id"
|
|
jbe@499
|
64 JOIN "area" ON "area"."id" = "issue"."area_id"
|
|
jbe@499
|
65 LEFT JOIN "privilege" ON
|
|
jbe@499
|
66 "privilege"."member_id" = "member"."id" AND
|
|
jbe@499
|
67 "privilege"."unit_id" = "area"."unit_id" AND
|
|
jbe@499
|
68 "privilege"."voting_right" = TRUE
|
|
jbe@499
|
69 LEFT JOIN "subscription" ON
|
|
jbe@499
|
70 "subscription"."member_id" = "member"."id" AND
|
|
jbe@499
|
71 "subscription"."unit_id" = "area"."unit_id"
|
|
jbe@499
|
72 LEFT JOIN "ignored_area" ON
|
|
jbe@499
|
73 "ignored_area"."member_id" = "member"."id" AND
|
|
jbe@499
|
74 "ignored_area"."area_id" = "issue"."area_id"
|
|
jbe@499
|
75 LEFT JOIN "interest" ON
|
|
jbe@499
|
76 "interest"."member_id" = "member"."id" AND
|
|
jbe@499
|
77 "interest"."issue_id" = "event"."issue_id"
|
|
jbe@499
|
78 LEFT JOIN "supporter" ON
|
|
jbe@499
|
79 "supporter"."member_id" = "member"."id" AND
|
|
jbe@499
|
80 "supporter"."initiative_id" = "event"."initiative_id"
|
|
jbe@499
|
81 WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL)
|
|
jbe@499
|
82 AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
|
|
jbe@499
|
83 AND (
|
|
jbe@499
|
84 "event"."event" = 'issue_state_changed'::"event_type" OR
|
|
jbe@499
|
85 ( "event"."event" = 'initiative_revoked'::"event_type" AND
|
|
jbe@499
|
86 "supporter"."member_id" NOTNULL ) );
|
|
jbe@499
|
87
|
|
jbe@478
|
88 CREATE VIEW "updated_initiative" AS
|
|
jbe@478
|
89 SELECT
|
|
jbe@499
|
90 "supporter"."member_id" AS "recipient_id",
|
|
jbe@478
|
91 FALSE AS "featured",
|
|
jbe@499
|
92 "supporter"."initiative_id"
|
|
jbe@499
|
93 FROM "supporter"
|
|
jbe@499
|
94 JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id"
|
|
jbe@478
|
95 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
|
jbe@499
|
96 LEFT JOIN "initiative_notification_sent" AS "sent" ON
|
|
jbe@499
|
97 "sent"."member_id" = "supporter"."member_id" AND
|
|
jbe@499
|
98 "sent"."initiative_id" = "supporter"."initiative_id"
|
|
jbe@499
|
99 LEFT JOIN "ignored_initiative" ON
|
|
jbe@499
|
100 "ignored_initiative"."member_id" = "supporter"."member_id" AND
|
|
jbe@499
|
101 "ignored_initiative"."initiative_id" = "supporter"."initiative_id"
|
|
jbe@480
|
102 WHERE "issue"."state" IN ('admission', 'discussion')
|
|
jbe@499
|
103 AND "ignored_initiative"."member_id" ISNULL
|
|
jbe@478
|
104 AND (
|
|
jbe@478
|
105 EXISTS (
|
|
jbe@478
|
106 SELECT NULL FROM "draft"
|
|
jbe@499
|
107 LEFT JOIN "ignored_member" ON
|
|
jbe@499
|
108 "ignored_member"."member_id" = "supporter"."member_id" AND
|
|
jbe@499
|
109 "ignored_member"."other_member_id" = "draft"."author_id"
|
|
jbe@499
|
110 WHERE "draft"."initiative_id" = "supporter"."initiative_id"
|
|
jbe@478
|
111 AND "draft"."id" > "supporter"."draft_id"
|
|
jbe@499
|
112 AND "ignored_member"."member_id" ISNULL
|
|
jbe@478
|
113 ) OR EXISTS (
|
|
jbe@478
|
114 SELECT NULL FROM "suggestion"
|
|
jbe@492
|
115 LEFT JOIN "opinion" ON
|
|
jbe@492
|
116 "opinion"."member_id" = "supporter"."member_id" AND
|
|
jbe@492
|
117 "opinion"."suggestion_id" = "suggestion"."id"
|
|
jbe@499
|
118 LEFT JOIN "ignored_member" ON
|
|
jbe@499
|
119 "ignored_member"."member_id" = "supporter"."member_id" AND
|
|
jbe@499
|
120 "ignored_member"."other_member_id" = "suggestion"."author_id"
|
|
jbe@499
|
121 WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"
|
|
jbe@492
|
122 AND "opinion"."member_id" ISNULL
|
|
jbe@499
|
123 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
|
|
jbe@499
|
124 AND "ignored_member"."member_id" ISNULL
|
|
jbe@478
|
125 )
|
|
jbe@478
|
126 );
|
|
jbe@478
|
127
|
|
jbe@478
|
128 CREATE FUNCTION "featured_initiative"
|
|
jbe@499
|
129 ( "recipient_id_p" "member"."id"%TYPE,
|
|
jbe@499
|
130 "area_id_p" "area"."id"%TYPE )
|
|
jbe@499
|
131 RETURNS SETOF "initiative"."id"%TYPE
|
|
jbe@478
|
132 LANGUAGE 'plpgsql' STABLE AS $$
|
|
jbe@478
|
133 DECLARE
|
|
jbe@499
|
134 "counter_v" "member"."notification_counter"%TYPE;
|
|
jbe@499
|
135 "sample_size_v" "member"."notification_sample_size"%TYPE;
|
|
jbe@499
|
136 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
|
|
jbe@499
|
137 "match_v" BOOLEAN;
|
|
jbe@478
|
138 "member_id_v" "member"."id"%TYPE;
|
|
jbe@478
|
139 "seed_v" TEXT;
|
|
jbe@499
|
140 "initiative_id_v" "initiative"."id"%TYPE;
|
|
jbe@478
|
141 BEGIN
|
|
jbe@499
|
142 SELECT "notification_counter", "notification_sample_size"
|
|
jbe@499
|
143 INTO "counter_v", "sample_size_v"
|
|
jbe@499
|
144 FROM "member" WHERE "id" = "recipient_id_p";
|
|
jbe@478
|
145 "initiative_id_ary" := '{}';
|
|
jbe@478
|
146 LOOP
|
|
jbe@478
|
147 "match_v" := FALSE;
|
|
jbe@478
|
148 FOR "member_id_v", "seed_v" IN
|
|
jbe@478
|
149 SELECT * FROM (
|
|
jbe@478
|
150 SELECT DISTINCT
|
|
jbe@478
|
151 "supporter"."member_id",
|
|
jbe@499
|
152 md5(
|
|
jbe@499
|
153 "recipient_id_p" || '-' ||
|
|
jbe@499
|
154 "counter_v" || '-' ||
|
|
jbe@499
|
155 "area_id_p" || '-' ||
|
|
jbe@499
|
156 "supporter"."member_id"
|
|
jbe@499
|
157 ) AS "seed"
|
|
jbe@478
|
158 FROM "supporter"
|
|
jbe@478
|
159 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
|
|
jbe@478
|
160 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
|
jbe@499
|
161 WHERE "supporter"."member_id" != "recipient_id_p"
|
|
jbe@478
|
162 AND "issue"."area_id" = "area_id_p"
|
|
jbe@478
|
163 AND "issue"."state" IN ('admission', 'discussion', 'verification')
|
|
jbe@478
|
164 ) AS "subquery"
|
|
jbe@478
|
165 ORDER BY "seed"
|
|
jbe@478
|
166 LOOP
|
|
jbe@499
|
167 SELECT "initiative"."id" INTO "initiative_id_v"
|
|
jbe@478
|
168 FROM "initiative"
|
|
jbe@478
|
169 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
|
jbe@499
|
170 JOIN "area" ON "area"."id" = "issue"."area_id"
|
|
jbe@478
|
171 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
|
|
jbe@478
|
172 LEFT JOIN "supporter" AS "self_support" ON
|
|
jbe@478
|
173 "self_support"."initiative_id" = "initiative"."id" AND
|
|
jbe@499
|
174 "self_support"."member_id" = "recipient_id_p"
|
|
jbe@499
|
175 LEFT JOIN "privilege" ON
|
|
jbe@499
|
176 "privilege"."member_id" = "recipient_id_p" AND
|
|
jbe@499
|
177 "privilege"."unit_id" = "area"."unit_id" AND
|
|
jbe@499
|
178 "privilege"."voting_right" = TRUE
|
|
jbe@499
|
179 LEFT JOIN "subscription" ON
|
|
jbe@499
|
180 "subscription"."member_id" = "recipient_id_p" AND
|
|
jbe@499
|
181 "subscription"."unit_id" = "area"."unit_id"
|
|
jbe@499
|
182 LEFT JOIN "ignored_initiative" ON
|
|
jbe@499
|
183 "ignored_initiative"."member_id" = "recipient_id_p" AND
|
|
jbe@499
|
184 "ignored_initiative"."initiative_id" = "initiative"."id"
|
|
jbe@478
|
185 WHERE "supporter"."member_id" = "member_id_v"
|
|
jbe@478
|
186 AND "issue"."area_id" = "area_id_p"
|
|
jbe@478
|
187 AND "issue"."state" IN ('admission', 'discussion', 'verification')
|
|
jbe@478
|
188 AND "self_support"."member_id" ISNULL
|
|
jbe@478
|
189 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
|
|
jbe@499
|
190 AND (
|
|
jbe@499
|
191 "privilege"."member_id" NOTNULL OR
|
|
jbe@499
|
192 "subscription"."member_id" NOTNULL )
|
|
jbe@499
|
193 AND "ignored_initiative"."member_id" ISNULL
|
|
jbe@499
|
194 AND NOT EXISTS (
|
|
jbe@499
|
195 SELECT NULL FROM "draft"
|
|
jbe@499
|
196 JOIN "ignored_member" ON
|
|
jbe@499
|
197 "ignored_member"."member_id" = "recipient_id_p" AND
|
|
jbe@499
|
198 "ignored_member"."other_member_id" = "draft"."author_id"
|
|
jbe@499
|
199 WHERE "draft"."initiative_id" = "initiative"."id"
|
|
jbe@499
|
200 )
|
|
jbe@478
|
201 ORDER BY md5("seed_v" || '-' || "initiative"."id")
|
|
jbe@478
|
202 LIMIT 1;
|
|
jbe@478
|
203 IF FOUND THEN
|
|
jbe@478
|
204 "match_v" := TRUE;
|
|
jbe@499
|
205 RETURN NEXT "initiative_id_v";
|
|
jbe@499
|
206 IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
|
|
jbe@478
|
207 RETURN;
|
|
jbe@478
|
208 END IF;
|
|
jbe@499
|
209 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
|
|
jbe@478
|
210 END IF;
|
|
jbe@478
|
211 END LOOP;
|
|
jbe@478
|
212 EXIT WHEN NOT "match_v";
|
|
jbe@478
|
213 END LOOP;
|
|
jbe@478
|
214 RETURN;
|
|
jbe@478
|
215 END;
|
|
jbe@478
|
216 $$;
|
|
jbe@478
|
217
|
|
jbe@478
|
218 CREATE VIEW "updated_or_featured_initiative" AS
|
|
jbe@478
|
219 SELECT
|
|
jbe@499
|
220 "subquery".*,
|
|
jbe@478
|
221 NOT EXISTS (
|
|
jbe@478
|
222 SELECT NULL FROM "initiative" AS "better_initiative"
|
|
jbe@499
|
223 WHERE "better_initiative"."issue_id" = "initiative"."issue_id"
|
|
jbe@484
|
224 AND
|
|
jbe@484
|
225 ( COALESCE("better_initiative"."harmonic_weight", -1),
|
|
jbe@484
|
226 -"better_initiative"."id" ) >
|
|
jbe@484
|
227 ( COALESCE("initiative"."harmonic_weight", -1),
|
|
jbe@485
|
228 -"initiative"."id" )
|
|
jbe@499
|
229 ) AS "leading"
|
|
jbe@499
|
230 FROM (
|
|
jbe@499
|
231 SELECT * FROM "updated_initiative"
|
|
jbe@499
|
232 UNION ALL
|
|
jbe@499
|
233 SELECT
|
|
jbe@499
|
234 "member"."id" AS "recipient_id",
|
|
jbe@499
|
235 TRUE AS "featured",
|
|
jbe@499
|
236 "featured_initiative_id" AS "initiative_id"
|
|
jbe@499
|
237 FROM "member" CROSS JOIN "area"
|
|
jbe@499
|
238 CROSS JOIN LATERAL
|
|
jbe@499
|
239 "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id"
|
|
jbe@499
|
240 JOIN "initiative" ON "initiative"."id" = "featured_initiative_id"
|
|
jbe@499
|
241 ) AS "subquery"
|
|
jbe@499
|
242 JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id";
|
|
jbe@478
|
243
|
|
jbe@478
|
244 CREATE VIEW "leading_complement_initiative" AS
|
|
jbe@478
|
245 SELECT * FROM (
|
|
jbe@499
|
246 SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id")
|
|
jbe@499
|
247 "uf_initiative"."recipient_id",
|
|
jbe@478
|
248 FALSE AS "featured",
|
|
jbe@499
|
249 "uf_initiative"."initiative_id",
|
|
jbe@499
|
250 TRUE AS "leading"
|
|
jbe@492
|
251 FROM "updated_or_featured_initiative" AS "uf_initiative"
|
|
jbe@499
|
252 JOIN "initiative" AS "uf_initiative_full" ON
|
|
jbe@499
|
253 "uf_initiative_full"."id" = "uf_initiative"."initiative_id"
|
|
jbe@492
|
254 JOIN "initiative" ON
|
|
jbe@499
|
255 "initiative"."issue_id" = "uf_initiative_full"."issue_id"
|
|
jbe@478
|
256 ORDER BY
|
|
jbe@499
|
257 "uf_initiative"."recipient_id",
|
|
jbe@478
|
258 "initiative"."issue_id",
|
|
jbe@478
|
259 "initiative"."harmonic_weight" DESC,
|
|
jbe@478
|
260 "initiative"."id"
|
|
jbe@478
|
261 ) AS "subquery"
|
|
jbe@478
|
262 WHERE NOT EXISTS (
|
|
jbe@478
|
263 SELECT NULL FROM "updated_or_featured_initiative" AS "other"
|
|
jbe@499
|
264 WHERE "other"."recipient_id" = "subquery"."recipient_id"
|
|
jbe@499
|
265 AND "other"."initiative_id" = "subquery"."initiative_id"
|
|
jbe@478
|
266 );
|
|
jbe@478
|
267
|
|
jbe@492
|
268 CREATE VIEW "unfiltered_initiative_for_notification" AS
|
|
jbe@499
|
269 SELECT
|
|
jbe@499
|
270 "subquery".*,
|
|
jbe@499
|
271 "supporter"."member_id" NOTNULL AS "supported",
|
|
jbe@499
|
272 CASE WHEN "supporter"."member_id" NOTNULL THEN
|
|
jbe@499
|
273 EXISTS (
|
|
jbe@499
|
274 SELECT NULL FROM "draft"
|
|
jbe@499
|
275 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
|
|
jbe@499
|
276 AND "draft"."id" > "supporter"."draft_id"
|
|
jbe@499
|
277 )
|
|
jbe@499
|
278 ELSE
|
|
jbe@499
|
279 EXISTS (
|
|
jbe@499
|
280 SELECT NULL FROM "draft"
|
|
jbe@499
|
281 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
|
|
jbe@499
|
282 AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE)
|
|
jbe@499
|
283 )
|
|
jbe@499
|
284 END AS "new_draft",
|
|
jbe@499
|
285 CASE WHEN "supporter"."member_id" NOTNULL THEN
|
|
jbe@499
|
286 ( SELECT count(1) FROM "suggestion"
|
|
jbe@499
|
287 LEFT JOIN "opinion" ON
|
|
jbe@499
|
288 "opinion"."member_id" = "supporter"."member_id" AND
|
|
jbe@499
|
289 "opinion"."suggestion_id" = "suggestion"."id"
|
|
jbe@499
|
290 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
|
|
jbe@499
|
291 AND "opinion"."member_id" ISNULL
|
|
jbe@499
|
292 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
|
|
jbe@499
|
293 )
|
|
jbe@499
|
294 ELSE
|
|
jbe@499
|
295 ( SELECT count(1) FROM "suggestion"
|
|
jbe@499
|
296 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
|
|
jbe@499
|
297 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
|
|
jbe@499
|
298 )
|
|
jbe@499
|
299 END AS "new_suggestion_count"
|
|
jbe@499
|
300 FROM (
|
|
jbe@499
|
301 SELECT * FROM "updated_or_featured_initiative"
|
|
jbe@499
|
302 UNION ALL
|
|
jbe@499
|
303 SELECT * FROM "leading_complement_initiative"
|
|
jbe@499
|
304 ) AS "subquery"
|
|
jbe@499
|
305 LEFT JOIN "supporter" ON
|
|
jbe@499
|
306 "supporter"."member_id" = "subquery"."recipient_id" AND
|
|
jbe@499
|
307 "supporter"."initiative_id" = "subquery"."initiative_id"
|
|
jbe@499
|
308 LEFT JOIN "initiative_notification_sent" AS "sent" ON
|
|
jbe@499
|
309 "sent"."member_id" = "subquery"."recipient_id" AND
|
|
jbe@499
|
310 "sent"."initiative_id" = "subquery"."initiative_id";
|
|
jbe@478
|
311
|
|
jbe@492
|
312 CREATE VIEW "initiative_for_notification" AS
|
|
jbe@499
|
313 SELECT "unfiltered1".*
|
|
jbe@499
|
314 FROM "unfiltered_initiative_for_notification" "unfiltered1"
|
|
jbe@499
|
315 JOIN "initiative" AS "initiative1" ON
|
|
jbe@499
|
316 "initiative1"."id" = "unfiltered1"."initiative_id"
|
|
jbe@499
|
317 JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id"
|
|
jbe@492
|
318 WHERE EXISTS (
|
|
jbe@492
|
319 SELECT NULL
|
|
jbe@499
|
320 FROM "unfiltered_initiative_for_notification" "unfiltered2"
|
|
jbe@499
|
321 JOIN "initiative" AS "initiative2" ON
|
|
jbe@499
|
322 "initiative2"."id" = "unfiltered2"."initiative_id"
|
|
jbe@499
|
323 JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id"
|
|
jbe@499
|
324 WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"
|
|
jbe@492
|
325 AND "issue1"."area_id" = "issue2"."area_id"
|
|
jbe@499
|
326 AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 )
|
|
jbe@492
|
327 );
|
|
jbe@492
|
328
|
|
jbe@497
|
329 CREATE VIEW "newsletter_to_send" AS
|
|
jbe@497
|
330 SELECT
|
|
jbe@499
|
331 "member"."id" AS "recipient_id",
|
|
jbe@499
|
332 "newsletter"."id" AS "newsletter_id"
|
|
jbe@497
|
333 FROM "newsletter" CROSS JOIN "member"
|
|
jbe@497
|
334 LEFT JOIN "privilege" ON
|
|
jbe@497
|
335 "privilege"."member_id" = "member"."id" AND
|
|
jbe@497
|
336 "privilege"."unit_id" = "newsletter"."unit_id" AND
|
|
jbe@497
|
337 "privilege"."voting_right" = TRUE
|
|
jbe@497
|
338 LEFT JOIN "subscription" ON
|
|
jbe@497
|
339 "subscription"."member_id" = "member"."id" AND
|
|
jbe@497
|
340 "subscription"."unit_id" = "newsletter"."unit_id"
|
|
jbe@498
|
341 WHERE "newsletter"."published" <= now()
|
|
jbe@497
|
342 AND "newsletter"."sent" ISNULL
|
|
jbe@497
|
343 AND "member"."locked" = FALSE
|
|
jbe@497
|
344 AND (
|
|
jbe@497
|
345 "member"."disable_notifications" = FALSE OR
|
|
jbe@497
|
346 "newsletter"."include_all_members" = TRUE )
|
|
jbe@497
|
347 AND (
|
|
jbe@497
|
348 "newsletter"."unit_id" ISNULL OR
|
|
jbe@497
|
349 "privilege"."member_id" NOTNULL OR
|
|
jbe@497
|
350 "subscription"."member_id" NOTNULL );
|
|
jbe@497
|
351
|
|
jbe@492
|
352 CREATE FUNCTION "get_initiatives_for_notification"
|
|
jbe@501
|
353 ( "recipient_id_p" "member"."id"%TYPE )
|
|
jbe@492
|
354 RETURNS SETOF "initiative_for_notification"
|
|
jbe@492
|
355 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@492
|
356 DECLARE
|
|
jbe@492
|
357 "result_row" "initiative_for_notification"%ROWTYPE;
|
|
jbe@492
|
358 "last_draft_id_v" "draft"."id"%TYPE;
|
|
jbe@492
|
359 "last_suggestion_id_v" "suggestion"."id"%TYPE;
|
|
jbe@492
|
360 BEGIN
|
|
jbe@492
|
361 PERFORM "require_transaction_isolation"();
|
|
jbe@501
|
362 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
|
|
jbe@492
|
363 FOR "result_row" IN
|
|
jbe@492
|
364 SELECT * FROM "initiative_for_notification"
|
|
jbe@501
|
365 WHERE "recipient_id" = "recipient_id_p"
|
|
jbe@492
|
366 LOOP
|
|
jbe@492
|
367 SELECT "id" INTO "last_draft_id_v" FROM "draft"
|
|
jbe@499
|
368 WHERE "draft"."initiative_id" = "result_row"."initiative_id"
|
|
jbe@492
|
369 ORDER BY "id" DESC LIMIT 1;
|
|
jbe@492
|
370 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
|
|
jbe@499
|
371 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
|
|
jbe@492
|
372 ORDER BY "id" DESC LIMIT 1;
|
|
jbe@492
|
373 INSERT INTO "initiative_notification_sent"
|
|
jbe@492
|
374 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
|
|
jbe@492
|
375 VALUES (
|
|
jbe@501
|
376 "recipient_id_p",
|
|
jbe@499
|
377 "result_row"."initiative_id",
|
|
jbe@493
|
378 "last_draft_id_v",
|
|
jbe@493
|
379 "last_suggestion_id_v" )
|
|
jbe@492
|
380 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
|
|
jbe@492
|
381 "last_draft_id" = CASE
|
|
jbe@494
|
382 WHEN "initiative_notification_sent"."last_draft_id" > "last_draft_id_v"
|
|
jbe@494
|
383 THEN "initiative_notification_sent"."last_draft_id"
|
|
jbe@492
|
384 ELSE "last_draft_id_v"
|
|
jbe@492
|
385 END,
|
|
jbe@492
|
386 "last_suggestion_id" = CASE
|
|
jbe@494
|
387 WHEN "initiative_notification_sent"."last_suggestion_id" > "last_suggestion_id_v"
|
|
jbe@494
|
388 THEN "initiative_notification_sent"."last_suggestion_id"
|
|
jbe@492
|
389 ELSE "last_suggestion_id_v"
|
|
jbe@492
|
390 END;
|
|
jbe@492
|
391 RETURN NEXT "result_row";
|
|
jbe@492
|
392 END LOOP;
|
|
jbe@492
|
393 DELETE FROM "initiative_notification_sent"
|
|
jbe@492
|
394 USING "initiative", "issue"
|
|
jbe@501
|
395 WHERE "initiative_notification_sent"."member_id" = "recipient_id_p"
|
|
jbe@492
|
396 AND "initiative"."id" = "initiative_notification_sent"."initiative_id"
|
|
jbe@492
|
397 AND "issue"."id" = "initiative"."issue_id"
|
|
jbe@492
|
398 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
|
|
jbe@492
|
399 UPDATE "member" SET "notification_counter" = "notification_counter" + 1
|
|
jbe@501
|
400 WHERE "id" = "recipient_id_p";
|
|
jbe@492
|
401 RETURN;
|
|
jbe@492
|
402 END;
|
|
jbe@492
|
403 $$;
|
|
jbe@492
|
404
|
|
jbe@478
|
405 COMMIT;
|