| 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@510
|
7 ALTER TABLE "member" ADD COLUMN "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE;
|
|
jbe@510
|
8 ALTER TABLE "member" ADD COLUMN "notification_counter" INT4 NOT NULL DEFAULT 1;
|
|
jbe@510
|
9 ALTER TABLE "member" ADD COLUMN "notification_sample_size" INT4 NOT NULL DEFAULT 3;
|
|
jbe@510
|
10 ALTER TABLE "member" ADD COLUMN "notification_dow" INT4 CHECK ("notification_dow" BETWEEN 0 AND 6);
|
|
jbe@510
|
11 ALTER TABLE "member" ADD COLUMN "notification_hour" INT4 CHECK ("notification_hour" BETWEEN 0 AND 23);
|
|
jbe@510
|
12 ALTER TABLE "member" ADD COLUMN "notification_sent" TIMESTAMP;
|
|
jbe@510
|
13 ALTER TABLE "member" ADD
|
|
jbe@510
|
14 CONSTRAINT "notification_dow_requires_notification_hour"
|
|
jbe@510
|
15 CHECK ("notification_dow" ISNULL OR "notification_hour" NOTNULL);
|
|
jbe@499
|
16
|
|
jbe@510
|
17 UPDATE "member" SET "disable_notifications" = TRUE WHERE "notify_level" = 'none'::"notify_level";
|
|
jbe@478
|
18
|
|
jbe@510
|
19 ALTER TABLE "member" DROP COLUMN "notify_level";
|
|
jbe@499
|
20
|
|
jbe@500
|
21 DROP VIEW "selected_event_seen_by_member";
|
|
jbe@500
|
22 DROP VIEW "event_seen_by_member";
|
|
jbe@499
|
23 DROP TYPE "notify_level";
|
|
jbe@510
|
24
|
|
jbe@510
|
25 COMMENT ON COLUMN "member"."disable_notifications" IS 'TRUE if member does not want to receive notifications';
|
|
jbe@510
|
26 COMMENT ON COLUMN "member"."notification_counter" IS 'Sequential number of next scheduled notification message (used as a seed for pseudo-random initiative selection algorithm)';
|
|
jbe@510
|
27 COMMENT ON COLUMN "member"."notification_sample_size" IS 'Number of featured initiatives per issue in scheduled notification messages';
|
|
jbe@510
|
28 COMMENT ON COLUMN "member"."notification_dow" IS 'Day of week for scheduled notifications (NULL to receive a daily digest)';
|
|
jbe@510
|
29 COMMENT ON COLUMN "member"."notification_hour" IS 'Time of day when scheduled notifications are sent out';
|
|
jbe@510
|
30 COMMENT ON COLUMN "member"."notification_sent" IS 'Timestamp of last scheduled notification mail that has been sent out';
|
|
jbe@510
|
31
|
|
jbe@510
|
32 ALTER TABLE "rendered_member_statement" ALTER COLUMN "member_id" SET DATA TYPE INT4;
|
|
jbe@510
|
33 ALTER TABLE "session" ALTER COLUMN "member_id" SET DATA TYPE INT4;
|
|
jbe@510
|
34
|
|
jbe@478
|
35 CREATE TABLE "subscription" (
|
|
jbe@478
|
36 PRIMARY KEY ("member_id", "unit_id"),
|
|
jbe@478
|
37 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@478
|
38 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
|
|
jbe@478
|
39 CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id");
|
|
jbe@492
|
40
|
|
jbe@492
|
41 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
|
42
|
|
jbe@492
|
43 CREATE TABLE "ignored_area" (
|
|
jbe@492
|
44 PRIMARY KEY ("member_id", "area_id"),
|
|
jbe@492
|
45 "member_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@492
|
46 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
|
|
jbe@492
|
47 CREATE INDEX "ignored_area_area_id_idx" ON "ignored_area" ("area_id");
|
|
jbe@492
|
48
|
|
jbe@492
|
49 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
|
50
|
|
jbe@510
|
51 ALTER TABLE "ignored_initiative" DROP CONSTRAINT "ignored_initiative_pkey";
|
|
jbe@510
|
52 DROP INDEX "ignored_initiative_member_id_idx";
|
|
jbe@510
|
53
|
|
jbe@510
|
54 ALTER TABLE "ignored_initiative" ADD PRIMARY KEY ("member_id", "initiative_id");
|
|
jbe@510
|
55 CREATE INDEX "ignored_initiative_initiative_id_idx" ON "ignored_initiative" ("initiative_id");
|
|
jbe@510
|
56
|
|
jbe@510
|
57 COMMENT ON TABLE "ignored_initiative" IS 'An entry in this table denotes that the member does not wish to receive notifications for the given initiative';
|
|
jbe@510
|
58
|
|
jbe@510
|
59 ALTER TABLE "notification_sent" RENAME TO "notification_event_sent";
|
|
jbe@510
|
60 ALTER INDEX "notification_sent_singleton_idx" RENAME TO "notification_event_sent_singleton_idx";
|
|
jbe@510
|
61
|
|
jbe@510
|
62 CREATE TABLE "notification_initiative_sent" (
|
|
jbe@492
|
63 PRIMARY KEY ("member_id", "initiative_id"),
|
|
jbe@492
|
64 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@492
|
65 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@492
|
66 "last_draft_id" INT8 NOT NULL,
|
|
jbe@495
|
67 "last_suggestion_id" INT8 );
|
|
jbe@510
|
68 CREATE INDEX "notification_initiative_sent_initiative_idx" ON "notification_initiative_sent" ("initiative_id");
|
|
jbe@510
|
69
|
|
jbe@510
|
70 COMMENT ON TABLE "notification_initiative_sent" IS 'Information which initiatives have been promoted to a member in a scheduled notification mail';
|
|
jbe@510
|
71
|
|
jbe@510
|
72 COMMENT ON COLUMN "notification_initiative_sent"."last_draft_id" IS 'Current (i.e. last) draft_id when initiative had been promoted';
|
|
jbe@510
|
73 COMMENT ON COLUMN "notification_initiative_sent"."last_suggestion_id" IS 'Current (i.e. last) draft_id when initiative had been promoted';
|
|
jbe@478
|
74
|
|
jbe@496
|
75 CREATE TABLE "newsletter" (
|
|
jbe@496
|
76 "id" SERIAL4 PRIMARY KEY,
|
|
jbe@496
|
77 "published" TIMESTAMPTZ NOT NULL,
|
|
jbe@496
|
78 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
jbe@496
|
79 "include_all_members" BOOLEAN NOT NULL,
|
|
jbe@496
|
80 "sent" TIMESTAMPTZ,
|
|
jbe@496
|
81 "subject" TEXT NOT NULL,
|
|
jbe@496
|
82 "content" TEXT NOT NULL );
|
|
jbe@496
|
83 CREATE INDEX "newsletter_unit_id_idx" ON "newsletter" ("unit_id", "published");
|
|
jbe@496
|
84 CREATE INDEX "newsletter_all_units_published_idx" ON "newsletter" ("published") WHERE "unit_id" ISNULL;
|
|
jbe@496
|
85 CREATE INDEX "newsletter_published_idx" ON "newsletter" ("published");
|
|
jbe@496
|
86
|
|
jbe@510
|
87 COMMENT ON TABLE "newsletter" IS 'Contains newsletters created by administrators to be sent out and for further reference';
|
|
jbe@510
|
88
|
|
jbe@510
|
89 COMMENT ON COLUMN "newsletter"."published" IS 'Timestamp when the newsletter is to be sent out (and made available in the frontend)';
|
|
jbe@510
|
90 COMMENT ON COLUMN "newsletter"."unit_id" IS 'If set, only members with voting right in the given unit are considered to be recipients';
|
|
jbe@510
|
91 COMMENT ON COLUMN "newsletter"."include_all_members" IS 'TRUE = include all members regardless of their ''disable_notifications'' setting';
|
|
jbe@510
|
92 COMMENT ON COLUMN "newsletter"."sent" IS 'Timestamp when the newsletter has been mailed out';
|
|
jbe@510
|
93 COMMENT ON COLUMN "newsletter"."subject" IS 'Subject line (e.g. to be used for the email)';
|
|
jbe@510
|
94 COMMENT ON COLUMN "newsletter"."content" IS 'Plain text content of the newsletter';
|
|
jbe@510
|
95
|
|
jbe@510
|
96 CREATE OR REPLACE FUNCTION "issue_requires_first_initiative_trigger"()
|
|
jbe@510
|
97 RETURNS TRIGGER
|
|
jbe@510
|
98 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@510
|
99 BEGIN
|
|
jbe@510
|
100 IF NOT EXISTS (
|
|
jbe@510
|
101 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
|
|
jbe@510
|
102 ) THEN
|
|
jbe@510
|
103 RAISE EXCEPTION 'Cannot create issue without an initial initiative.' USING
|
|
jbe@510
|
104 ERRCODE = 'integrity_constraint_violation',
|
|
jbe@510
|
105 HINT = 'Create issue, initiative, and draft within the same transaction.';
|
|
jbe@510
|
106 END IF;
|
|
jbe@510
|
107 RETURN NULL;
|
|
jbe@510
|
108 END;
|
|
jbe@510
|
109 $$;
|
|
jbe@510
|
110
|
|
jbe@510
|
111 CREATE OR REPLACE FUNCTION "initiative_requires_first_draft_trigger"()
|
|
jbe@510
|
112 RETURNS TRIGGER
|
|
jbe@510
|
113 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@510
|
114 BEGIN
|
|
jbe@510
|
115 IF NOT EXISTS (
|
|
jbe@510
|
116 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
|
|
jbe@510
|
117 ) THEN
|
|
jbe@510
|
118 RAISE EXCEPTION 'Cannot create initiative without an initial draft.' USING
|
|
jbe@510
|
119 ERRCODE = 'integrity_constraint_violation',
|
|
jbe@510
|
120 HINT = 'Create issue, initiative and draft within the same transaction.';
|
|
jbe@510
|
121 END IF;
|
|
jbe@510
|
122 RETURN NULL;
|
|
jbe@510
|
123 END;
|
|
jbe@510
|
124 $$;
|
|
jbe@510
|
125
|
|
jbe@510
|
126 CREATE OR REPLACE FUNCTION "suggestion_requires_first_opinion_trigger"()
|
|
jbe@510
|
127 RETURNS TRIGGER
|
|
jbe@510
|
128 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@510
|
129 BEGIN
|
|
jbe@510
|
130 IF NOT EXISTS (
|
|
jbe@510
|
131 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
|
|
jbe@510
|
132 ) THEN
|
|
jbe@510
|
133 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.' USING
|
|
jbe@510
|
134 ERRCODE = 'integrity_constraint_violation',
|
|
jbe@510
|
135 HINT = 'Create suggestion and opinion within the same transaction.';
|
|
jbe@510
|
136 END IF;
|
|
jbe@510
|
137 RETURN NULL;
|
|
jbe@510
|
138 END;
|
|
jbe@510
|
139 $$;
|
|
jbe@510
|
140
|
|
jbe@510
|
141 CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"()
|
|
jbe@510
|
142 RETURNS TRIGGER
|
|
jbe@510
|
143 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@510
|
144 DECLARE
|
|
jbe@510
|
145 "issue_id_v" "issue"."id"%TYPE;
|
|
jbe@510
|
146 "issue_row" "issue"%ROWTYPE;
|
|
jbe@510
|
147 BEGIN
|
|
jbe@510
|
148 IF EXISTS (
|
|
jbe@510
|
149 SELECT NULL FROM "temporary_transaction_data"
|
|
jbe@510
|
150 WHERE "txid" = txid_current()
|
|
jbe@510
|
151 AND "key" = 'override_protection_triggers'
|
|
jbe@510
|
152 AND "value" = TRUE::TEXT
|
|
jbe@510
|
153 ) THEN
|
|
jbe@510
|
154 RETURN NULL;
|
|
jbe@510
|
155 END IF;
|
|
jbe@510
|
156 IF TG_OP = 'DELETE' THEN
|
|
jbe@510
|
157 "issue_id_v" := OLD."issue_id";
|
|
jbe@510
|
158 ELSE
|
|
jbe@510
|
159 "issue_id_v" := NEW."issue_id";
|
|
jbe@510
|
160 END IF;
|
|
jbe@510
|
161 SELECT INTO "issue_row" * FROM "issue"
|
|
jbe@510
|
162 WHERE "id" = "issue_id_v" FOR SHARE;
|
|
jbe@510
|
163 IF (
|
|
jbe@510
|
164 "issue_row"."closed" NOTNULL OR (
|
|
jbe@510
|
165 "issue_row"."state" = 'voting' AND
|
|
jbe@510
|
166 "issue_row"."phase_finished" NOTNULL
|
|
jbe@510
|
167 )
|
|
jbe@510
|
168 ) THEN
|
|
jbe@510
|
169 IF
|
|
jbe@510
|
170 TG_RELID = 'direct_voter'::regclass AND
|
|
jbe@510
|
171 TG_OP = 'UPDATE'
|
|
jbe@510
|
172 THEN
|
|
jbe@510
|
173 IF
|
|
jbe@510
|
174 OLD."issue_id" = NEW."issue_id" AND
|
|
jbe@510
|
175 OLD."member_id" = NEW."member_id" AND
|
|
jbe@510
|
176 OLD."weight" = NEW."weight"
|
|
jbe@510
|
177 THEN
|
|
jbe@510
|
178 RETURN NULL; -- allows changing of voter comment
|
|
jbe@510
|
179 END IF;
|
|
jbe@510
|
180 END IF;
|
|
jbe@510
|
181 RAISE EXCEPTION 'Tried to modify data after voting has been closed.' USING
|
|
jbe@510
|
182 ERRCODE = 'integrity_constraint_violation';
|
|
jbe@510
|
183 END IF;
|
|
jbe@510
|
184 RETURN NULL;
|
|
jbe@510
|
185 END;
|
|
jbe@510
|
186 $$;
|
|
jbe@510
|
187
|
|
jbe@499
|
188 CREATE VIEW "event_for_notification" AS
|
|
jbe@499
|
189 SELECT
|
|
jbe@499
|
190 "member"."id" AS "recipient_id",
|
|
jbe@499
|
191 "event".*
|
|
jbe@499
|
192 FROM "member" CROSS JOIN "event"
|
|
jbe@499
|
193 JOIN "issue" ON "issue"."id" = "event"."issue_id"
|
|
jbe@499
|
194 JOIN "area" ON "area"."id" = "issue"."area_id"
|
|
jbe@499
|
195 LEFT JOIN "privilege" ON
|
|
jbe@499
|
196 "privilege"."member_id" = "member"."id" AND
|
|
jbe@499
|
197 "privilege"."unit_id" = "area"."unit_id" AND
|
|
jbe@499
|
198 "privilege"."voting_right" = TRUE
|
|
jbe@499
|
199 LEFT JOIN "subscription" ON
|
|
jbe@499
|
200 "subscription"."member_id" = "member"."id" AND
|
|
jbe@499
|
201 "subscription"."unit_id" = "area"."unit_id"
|
|
jbe@499
|
202 LEFT JOIN "ignored_area" ON
|
|
jbe@499
|
203 "ignored_area"."member_id" = "member"."id" AND
|
|
jbe@499
|
204 "ignored_area"."area_id" = "issue"."area_id"
|
|
jbe@499
|
205 LEFT JOIN "interest" ON
|
|
jbe@499
|
206 "interest"."member_id" = "member"."id" AND
|
|
jbe@499
|
207 "interest"."issue_id" = "event"."issue_id"
|
|
jbe@499
|
208 LEFT JOIN "supporter" ON
|
|
jbe@499
|
209 "supporter"."member_id" = "member"."id" AND
|
|
jbe@499
|
210 "supporter"."initiative_id" = "event"."initiative_id"
|
|
jbe@499
|
211 WHERE ("privilege"."member_id" NOTNULL OR "subscription"."member_id" NOTNULL)
|
|
jbe@499
|
212 AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
|
|
jbe@499
|
213 AND (
|
|
jbe@499
|
214 "event"."event" = 'issue_state_changed'::"event_type" OR
|
|
jbe@499
|
215 ( "event"."event" = 'initiative_revoked'::"event_type" AND
|
|
jbe@499
|
216 "supporter"."member_id" NOTNULL ) );
|
|
jbe@499
|
217
|
|
jbe@510
|
218 COMMENT ON VIEW "event_for_notification" IS 'Entries of the "event" table which are of interest for a particular notification mail recipient';
|
|
jbe@510
|
219
|
|
jbe@510
|
220 COMMENT ON COLUMN "event_for_notification"."recipient_id" IS 'member_id of the recipient of a notification mail';
|
|
jbe@510
|
221
|
|
jbe@478
|
222 CREATE VIEW "updated_initiative" AS
|
|
jbe@478
|
223 SELECT
|
|
jbe@499
|
224 "supporter"."member_id" AS "recipient_id",
|
|
jbe@478
|
225 FALSE AS "featured",
|
|
jbe@499
|
226 "supporter"."initiative_id"
|
|
jbe@499
|
227 FROM "supporter"
|
|
jbe@499
|
228 JOIN "initiative" ON "supporter"."initiative_id" = "initiative"."id"
|
|
jbe@478
|
229 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
|
jbe@510
|
230 LEFT JOIN "notification_initiative_sent" AS "sent" ON
|
|
jbe@499
|
231 "sent"."member_id" = "supporter"."member_id" AND
|
|
jbe@499
|
232 "sent"."initiative_id" = "supporter"."initiative_id"
|
|
jbe@499
|
233 LEFT JOIN "ignored_initiative" ON
|
|
jbe@499
|
234 "ignored_initiative"."member_id" = "supporter"."member_id" AND
|
|
jbe@499
|
235 "ignored_initiative"."initiative_id" = "supporter"."initiative_id"
|
|
jbe@480
|
236 WHERE "issue"."state" IN ('admission', 'discussion')
|
|
jbe@503
|
237 AND "initiative"."revoked" ISNULL
|
|
jbe@499
|
238 AND "ignored_initiative"."member_id" ISNULL
|
|
jbe@478
|
239 AND (
|
|
jbe@478
|
240 EXISTS (
|
|
jbe@478
|
241 SELECT NULL FROM "draft"
|
|
jbe@499
|
242 LEFT JOIN "ignored_member" ON
|
|
jbe@499
|
243 "ignored_member"."member_id" = "supporter"."member_id" AND
|
|
jbe@499
|
244 "ignored_member"."other_member_id" = "draft"."author_id"
|
|
jbe@499
|
245 WHERE "draft"."initiative_id" = "supporter"."initiative_id"
|
|
jbe@478
|
246 AND "draft"."id" > "supporter"."draft_id"
|
|
jbe@499
|
247 AND "ignored_member"."member_id" ISNULL
|
|
jbe@478
|
248 ) OR EXISTS (
|
|
jbe@478
|
249 SELECT NULL FROM "suggestion"
|
|
jbe@492
|
250 LEFT JOIN "opinion" ON
|
|
jbe@492
|
251 "opinion"."member_id" = "supporter"."member_id" AND
|
|
jbe@492
|
252 "opinion"."suggestion_id" = "suggestion"."id"
|
|
jbe@499
|
253 LEFT JOIN "ignored_member" ON
|
|
jbe@499
|
254 "ignored_member"."member_id" = "supporter"."member_id" AND
|
|
jbe@499
|
255 "ignored_member"."other_member_id" = "suggestion"."author_id"
|
|
jbe@499
|
256 WHERE "suggestion"."initiative_id" = "supporter"."initiative_id"
|
|
jbe@492
|
257 AND "opinion"."member_id" ISNULL
|
|
jbe@499
|
258 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
|
|
jbe@499
|
259 AND "ignored_member"."member_id" ISNULL
|
|
jbe@478
|
260 )
|
|
jbe@478
|
261 );
|
|
jbe@478
|
262
|
|
jbe@510
|
263 COMMENT ON VIEW "updated_initiative" IS 'Helper view for view "updated_or_featured_initiative"';
|
|
jbe@510
|
264
|
|
jbe@478
|
265 CREATE FUNCTION "featured_initiative"
|
|
jbe@499
|
266 ( "recipient_id_p" "member"."id"%TYPE,
|
|
jbe@499
|
267 "area_id_p" "area"."id"%TYPE )
|
|
jbe@499
|
268 RETURNS SETOF "initiative"."id"%TYPE
|
|
jbe@478
|
269 LANGUAGE 'plpgsql' STABLE AS $$
|
|
jbe@478
|
270 DECLARE
|
|
jbe@499
|
271 "counter_v" "member"."notification_counter"%TYPE;
|
|
jbe@499
|
272 "sample_size_v" "member"."notification_sample_size"%TYPE;
|
|
jbe@499
|
273 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
|
|
jbe@499
|
274 "match_v" BOOLEAN;
|
|
jbe@478
|
275 "member_id_v" "member"."id"%TYPE;
|
|
jbe@478
|
276 "seed_v" TEXT;
|
|
jbe@499
|
277 "initiative_id_v" "initiative"."id"%TYPE;
|
|
jbe@478
|
278 BEGIN
|
|
jbe@499
|
279 SELECT "notification_counter", "notification_sample_size"
|
|
jbe@499
|
280 INTO "counter_v", "sample_size_v"
|
|
jbe@499
|
281 FROM "member" WHERE "id" = "recipient_id_p";
|
|
jbe@478
|
282 "initiative_id_ary" := '{}';
|
|
jbe@478
|
283 LOOP
|
|
jbe@478
|
284 "match_v" := FALSE;
|
|
jbe@478
|
285 FOR "member_id_v", "seed_v" IN
|
|
jbe@478
|
286 SELECT * FROM (
|
|
jbe@478
|
287 SELECT DISTINCT
|
|
jbe@478
|
288 "supporter"."member_id",
|
|
jbe@499
|
289 md5(
|
|
jbe@499
|
290 "recipient_id_p" || '-' ||
|
|
jbe@499
|
291 "counter_v" || '-' ||
|
|
jbe@499
|
292 "area_id_p" || '-' ||
|
|
jbe@499
|
293 "supporter"."member_id"
|
|
jbe@499
|
294 ) AS "seed"
|
|
jbe@478
|
295 FROM "supporter"
|
|
jbe@478
|
296 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
|
|
jbe@478
|
297 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
|
jbe@499
|
298 WHERE "supporter"."member_id" != "recipient_id_p"
|
|
jbe@478
|
299 AND "issue"."area_id" = "area_id_p"
|
|
jbe@478
|
300 AND "issue"."state" IN ('admission', 'discussion', 'verification')
|
|
jbe@478
|
301 ) AS "subquery"
|
|
jbe@478
|
302 ORDER BY "seed"
|
|
jbe@478
|
303 LOOP
|
|
jbe@499
|
304 SELECT "initiative"."id" INTO "initiative_id_v"
|
|
jbe@478
|
305 FROM "initiative"
|
|
jbe@478
|
306 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
|
jbe@499
|
307 JOIN "area" ON "area"."id" = "issue"."area_id"
|
|
jbe@478
|
308 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
|
|
jbe@478
|
309 LEFT JOIN "supporter" AS "self_support" ON
|
|
jbe@478
|
310 "self_support"."initiative_id" = "initiative"."id" AND
|
|
jbe@499
|
311 "self_support"."member_id" = "recipient_id_p"
|
|
jbe@499
|
312 LEFT JOIN "privilege" ON
|
|
jbe@499
|
313 "privilege"."member_id" = "recipient_id_p" AND
|
|
jbe@499
|
314 "privilege"."unit_id" = "area"."unit_id" AND
|
|
jbe@499
|
315 "privilege"."voting_right" = TRUE
|
|
jbe@499
|
316 LEFT JOIN "subscription" ON
|
|
jbe@499
|
317 "subscription"."member_id" = "recipient_id_p" AND
|
|
jbe@499
|
318 "subscription"."unit_id" = "area"."unit_id"
|
|
jbe@499
|
319 LEFT JOIN "ignored_initiative" ON
|
|
jbe@499
|
320 "ignored_initiative"."member_id" = "recipient_id_p" AND
|
|
jbe@499
|
321 "ignored_initiative"."initiative_id" = "initiative"."id"
|
|
jbe@478
|
322 WHERE "supporter"."member_id" = "member_id_v"
|
|
jbe@478
|
323 AND "issue"."area_id" = "area_id_p"
|
|
jbe@478
|
324 AND "issue"."state" IN ('admission', 'discussion', 'verification')
|
|
jbe@503
|
325 AND "initiative"."revoked" ISNULL
|
|
jbe@478
|
326 AND "self_support"."member_id" ISNULL
|
|
jbe@478
|
327 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
|
|
jbe@499
|
328 AND (
|
|
jbe@499
|
329 "privilege"."member_id" NOTNULL OR
|
|
jbe@499
|
330 "subscription"."member_id" NOTNULL )
|
|
jbe@499
|
331 AND "ignored_initiative"."member_id" ISNULL
|
|
jbe@499
|
332 AND NOT EXISTS (
|
|
jbe@499
|
333 SELECT NULL FROM "draft"
|
|
jbe@499
|
334 JOIN "ignored_member" ON
|
|
jbe@499
|
335 "ignored_member"."member_id" = "recipient_id_p" AND
|
|
jbe@499
|
336 "ignored_member"."other_member_id" = "draft"."author_id"
|
|
jbe@499
|
337 WHERE "draft"."initiative_id" = "initiative"."id"
|
|
jbe@499
|
338 )
|
|
jbe@478
|
339 ORDER BY md5("seed_v" || '-' || "initiative"."id")
|
|
jbe@478
|
340 LIMIT 1;
|
|
jbe@478
|
341 IF FOUND THEN
|
|
jbe@478
|
342 "match_v" := TRUE;
|
|
jbe@499
|
343 RETURN NEXT "initiative_id_v";
|
|
jbe@499
|
344 IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
|
|
jbe@478
|
345 RETURN;
|
|
jbe@478
|
346 END IF;
|
|
jbe@499
|
347 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
|
|
jbe@478
|
348 END IF;
|
|
jbe@478
|
349 END LOOP;
|
|
jbe@478
|
350 EXIT WHEN NOT "match_v";
|
|
jbe@478
|
351 END LOOP;
|
|
jbe@478
|
352 RETURN;
|
|
jbe@478
|
353 END;
|
|
jbe@478
|
354 $$;
|
|
jbe@478
|
355
|
|
jbe@510
|
356 COMMENT ON FUNCTION "featured_initiative"
|
|
jbe@510
|
357 ( "recipient_id_p" "member"."id"%TYPE,
|
|
jbe@510
|
358 "area_id_p" "area"."id"%TYPE )
|
|
jbe@510
|
359 IS 'Helper function for view "updated_or_featured_initiative"';
|
|
jbe@510
|
360
|
|
jbe@478
|
361 CREATE VIEW "updated_or_featured_initiative" AS
|
|
jbe@478
|
362 SELECT
|
|
jbe@499
|
363 "subquery".*,
|
|
jbe@478
|
364 NOT EXISTS (
|
|
jbe@478
|
365 SELECT NULL FROM "initiative" AS "better_initiative"
|
|
jbe@499
|
366 WHERE "better_initiative"."issue_id" = "initiative"."issue_id"
|
|
jbe@484
|
367 AND
|
|
jbe@502
|
368 ( COALESCE("better_initiative"."supporter_count", -1),
|
|
jbe@484
|
369 -"better_initiative"."id" ) >
|
|
jbe@502
|
370 ( COALESCE("initiative"."supporter_count", -1),
|
|
jbe@485
|
371 -"initiative"."id" )
|
|
jbe@499
|
372 ) AS "leading"
|
|
jbe@499
|
373 FROM (
|
|
jbe@499
|
374 SELECT * FROM "updated_initiative"
|
|
jbe@499
|
375 UNION ALL
|
|
jbe@499
|
376 SELECT
|
|
jbe@499
|
377 "member"."id" AS "recipient_id",
|
|
jbe@499
|
378 TRUE AS "featured",
|
|
jbe@499
|
379 "featured_initiative_id" AS "initiative_id"
|
|
jbe@499
|
380 FROM "member" CROSS JOIN "area"
|
|
jbe@499
|
381 CROSS JOIN LATERAL
|
|
jbe@499
|
382 "featured_initiative"("member"."id", "area"."id") AS "featured_initiative_id"
|
|
jbe@499
|
383 JOIN "initiative" ON "initiative"."id" = "featured_initiative_id"
|
|
jbe@499
|
384 ) AS "subquery"
|
|
jbe@499
|
385 JOIN "initiative" ON "initiative"."id" = "subquery"."initiative_id";
|
|
jbe@478
|
386
|
|
jbe@510
|
387 COMMENT ON VIEW "updated_or_featured_initiative" IS 'Initiatives to be included in a scheduled notification mail because (a) they have been updated or (b) they are featured';
|
|
jbe@510
|
388
|
|
jbe@510
|
389 COMMENT ON COLUMN "updated_or_featured_initiative"."recipient_id" IS '"id" of the member who receives the notification mail';
|
|
jbe@510
|
390 COMMENT ON COLUMN "updated_or_featured_initiative"."featured" IS 'TRUE if the initiative has been included because it was selected by the "featured_initiative" algorithm (see source of function "featured_initiative")';
|
|
jbe@510
|
391 COMMENT ON COLUMN "updated_or_featured_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
|
|
jbe@510
|
392 COMMENT ON COLUMN "updated_or_featured_initiative"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue';
|
|
jbe@510
|
393
|
|
jbe@478
|
394 CREATE VIEW "leading_complement_initiative" AS
|
|
jbe@478
|
395 SELECT * FROM (
|
|
jbe@499
|
396 SELECT DISTINCT ON ("uf_initiative"."recipient_id", "initiative"."issue_id")
|
|
jbe@499
|
397 "uf_initiative"."recipient_id",
|
|
jbe@478
|
398 FALSE AS "featured",
|
|
jbe@499
|
399 "uf_initiative"."initiative_id",
|
|
jbe@499
|
400 TRUE AS "leading"
|
|
jbe@492
|
401 FROM "updated_or_featured_initiative" AS "uf_initiative"
|
|
jbe@499
|
402 JOIN "initiative" AS "uf_initiative_full" ON
|
|
jbe@499
|
403 "uf_initiative_full"."id" = "uf_initiative"."initiative_id"
|
|
jbe@492
|
404 JOIN "initiative" ON
|
|
jbe@499
|
405 "initiative"."issue_id" = "uf_initiative_full"."issue_id"
|
|
jbe@503
|
406 WHERE "initiative"."revoked" ISNULL
|
|
jbe@478
|
407 ORDER BY
|
|
jbe@499
|
408 "uf_initiative"."recipient_id",
|
|
jbe@478
|
409 "initiative"."issue_id",
|
|
jbe@502
|
410 "initiative"."supporter_count" DESC,
|
|
jbe@478
|
411 "initiative"."id"
|
|
jbe@478
|
412 ) AS "subquery"
|
|
jbe@478
|
413 WHERE NOT EXISTS (
|
|
jbe@478
|
414 SELECT NULL FROM "updated_or_featured_initiative" AS "other"
|
|
jbe@499
|
415 WHERE "other"."recipient_id" = "subquery"."recipient_id"
|
|
jbe@499
|
416 AND "other"."initiative_id" = "subquery"."initiative_id"
|
|
jbe@478
|
417 );
|
|
jbe@478
|
418
|
|
jbe@510
|
419 COMMENT ON VIEW "leading_complement_initiative" IS 'Helper view for view "unfiltered_initiative_for_notification" in order to always include the most supported initiative of an issue';
|
|
jbe@510
|
420 COMMENT ON COLUMN "leading_complement_initiative"."featured" IS 'Always FALSE in this view';
|
|
jbe@510
|
421 COMMENT ON COLUMN "leading_complement_initiative"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
|
|
jbe@510
|
422 COMMENT ON COLUMN "leading_complement_initiative"."leading" IS 'Always TRUE in this view';
|
|
jbe@510
|
423
|
|
jbe@492
|
424 CREATE VIEW "unfiltered_initiative_for_notification" AS
|
|
jbe@499
|
425 SELECT
|
|
jbe@499
|
426 "subquery".*,
|
|
jbe@499
|
427 "supporter"."member_id" NOTNULL AS "supported",
|
|
jbe@499
|
428 CASE WHEN "supporter"."member_id" NOTNULL THEN
|
|
jbe@499
|
429 EXISTS (
|
|
jbe@499
|
430 SELECT NULL FROM "draft"
|
|
jbe@499
|
431 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
|
|
jbe@499
|
432 AND "draft"."id" > "supporter"."draft_id"
|
|
jbe@499
|
433 )
|
|
jbe@499
|
434 ELSE
|
|
jbe@499
|
435 EXISTS (
|
|
jbe@499
|
436 SELECT NULL FROM "draft"
|
|
jbe@499
|
437 WHERE "draft"."initiative_id" = "subquery"."initiative_id"
|
|
jbe@499
|
438 AND COALESCE("draft"."id" > "sent"."last_draft_id", TRUE)
|
|
jbe@499
|
439 )
|
|
jbe@499
|
440 END AS "new_draft",
|
|
jbe@499
|
441 CASE WHEN "supporter"."member_id" NOTNULL THEN
|
|
jbe@499
|
442 ( SELECT count(1) FROM "suggestion"
|
|
jbe@499
|
443 LEFT JOIN "opinion" ON
|
|
jbe@499
|
444 "opinion"."member_id" = "supporter"."member_id" AND
|
|
jbe@499
|
445 "opinion"."suggestion_id" = "suggestion"."id"
|
|
jbe@499
|
446 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
|
|
jbe@499
|
447 AND "opinion"."member_id" ISNULL
|
|
jbe@499
|
448 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
|
|
jbe@499
|
449 )
|
|
jbe@499
|
450 ELSE
|
|
jbe@499
|
451 ( SELECT count(1) FROM "suggestion"
|
|
jbe@499
|
452 WHERE "suggestion"."initiative_id" = "subquery"."initiative_id"
|
|
jbe@499
|
453 AND COALESCE("suggestion"."id" > "sent"."last_suggestion_id", TRUE)
|
|
jbe@499
|
454 )
|
|
jbe@499
|
455 END AS "new_suggestion_count"
|
|
jbe@499
|
456 FROM (
|
|
jbe@499
|
457 SELECT * FROM "updated_or_featured_initiative"
|
|
jbe@499
|
458 UNION ALL
|
|
jbe@499
|
459 SELECT * FROM "leading_complement_initiative"
|
|
jbe@499
|
460 ) AS "subquery"
|
|
jbe@499
|
461 LEFT JOIN "supporter" ON
|
|
jbe@499
|
462 "supporter"."member_id" = "subquery"."recipient_id" AND
|
|
jbe@499
|
463 "supporter"."initiative_id" = "subquery"."initiative_id"
|
|
jbe@510
|
464 LEFT JOIN "notification_initiative_sent" AS "sent" ON
|
|
jbe@499
|
465 "sent"."member_id" = "subquery"."recipient_id" AND
|
|
jbe@499
|
466 "sent"."initiative_id" = "subquery"."initiative_id";
|
|
jbe@478
|
467
|
|
jbe@510
|
468 COMMENT ON VIEW "unfiltered_initiative_for_notification" IS 'Helper view which simply combines the views "updated_or_featured_initiative" and "leading_complement_initiative" and adds columns "supported", "new_draft", and "new_suggestion_count';
|
|
jbe@510
|
469
|
|
jbe@510
|
470 COMMENT ON COLUMN "unfiltered_initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient';
|
|
jbe@510
|
471 COMMENT ON COLUMN "unfiltered_initiative_for_notification"."new_draft" IS 'TRUE if a new draft exists (using the "draft_id" column of the "supporter" table in case of "supported" initiatives and the "last_draft_id" column of the "notification_initiative_sent" table in all other cases)';
|
|
jbe@510
|
472 COMMENT ON COLUMN "unfiltered_initiative_for_notification"."new_suggestion_count" IS 'Number of new suggestions (using the "last_suggestion_id" column of the "notification_initiative_sent" table while ignoring suggestions with an "opinion")';
|
|
jbe@510
|
473
|
|
jbe@492
|
474 CREATE VIEW "initiative_for_notification" AS
|
|
jbe@499
|
475 SELECT "unfiltered1".*
|
|
jbe@499
|
476 FROM "unfiltered_initiative_for_notification" "unfiltered1"
|
|
jbe@499
|
477 JOIN "initiative" AS "initiative1" ON
|
|
jbe@499
|
478 "initiative1"."id" = "unfiltered1"."initiative_id"
|
|
jbe@499
|
479 JOIN "issue" AS "issue1" ON "issue1"."id" = "initiative1"."issue_id"
|
|
jbe@492
|
480 WHERE EXISTS (
|
|
jbe@492
|
481 SELECT NULL
|
|
jbe@499
|
482 FROM "unfiltered_initiative_for_notification" "unfiltered2"
|
|
jbe@499
|
483 JOIN "initiative" AS "initiative2" ON
|
|
jbe@499
|
484 "initiative2"."id" = "unfiltered2"."initiative_id"
|
|
jbe@499
|
485 JOIN "issue" AS "issue2" ON "issue2"."id" = "initiative2"."issue_id"
|
|
jbe@499
|
486 WHERE "unfiltered1"."recipient_id" = "unfiltered2"."recipient_id"
|
|
jbe@492
|
487 AND "issue1"."area_id" = "issue2"."area_id"
|
|
jbe@499
|
488 AND ("unfiltered2"."new_draft" OR "unfiltered2"."new_suggestion_count" > 0 )
|
|
jbe@492
|
489 );
|
|
jbe@492
|
490
|
|
jbe@510
|
491 COMMENT ON VIEW "initiative_for_notification" IS 'Initiatives to be included in a scheduled notification mail';
|
|
jbe@510
|
492
|
|
jbe@510
|
493 COMMENT ON COLUMN "initiative_for_notification"."recipient_id" IS '"id" of the member who receives the notification mail';
|
|
jbe@510
|
494 COMMENT ON COLUMN "initiative_for_notification"."featured" IS 'TRUE if the initiative has been included because it was selected by the "featured_initiative" algorithm (see source of function "featured_initiative")';
|
|
jbe@510
|
495 COMMENT ON COLUMN "initiative_for_notification"."initiative_id" IS '"id" of the initiative to be included in the notification mail';
|
|
jbe@510
|
496 COMMENT ON COLUMN "initiative_for_notification"."leading" IS 'TRUE if the initiative has the highest "supporter_count" in the issue';
|
|
jbe@510
|
497 COMMENT ON COLUMN "initiative_for_notification"."supported" IS 'TRUE if initiative is supported by the recipient';
|
|
jbe@510
|
498 COMMENT ON COLUMN "initiative_for_notification"."new_draft" IS 'TRUE if a new draft exists (using the "draft_id" column of the "supporter" table in case of "supported" initiatives and the "last_draft_id" column of the "notification_initiative_sent" table in all other cases)';
|
|
jbe@510
|
499 COMMENT ON COLUMN "initiative_for_notification"."new_suggestion_count" IS 'Number of new suggestions (using the "last_suggestion_id" column of the "notification_initiative_sent" table while ignoring suggestions with an "opinion")';
|
|
jbe@510
|
500
|
|
jbe@504
|
501 CREATE VIEW "scheduled_notification_to_send" AS
|
|
jbe@505
|
502 SELECT * FROM (
|
|
jbe@505
|
503 SELECT
|
|
jbe@505
|
504 "id" AS "recipient_id",
|
|
jbe@505
|
505 now() - CASE WHEN "notification_dow" ISNULL THEN
|
|
jbe@505
|
506 ( "notification_sent"::DATE + CASE
|
|
jbe@505
|
507 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
|
|
jbe@505
|
508 THEN 0 ELSE 1 END
|
|
jbe@505
|
509 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
|
|
jbe@505
|
510 ELSE
|
|
jbe@505
|
511 ( "notification_sent"::DATE +
|
|
jbe@505
|
512 ( 7 + "notification_dow" -
|
|
jbe@505
|
513 EXTRACT(DOW FROM
|
|
jbe@505
|
514 ( "notification_sent"::DATE + CASE
|
|
jbe@505
|
515 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
|
|
jbe@505
|
516 THEN 0 ELSE 1 END
|
|
jbe@505
|
517 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
|
|
jbe@505
|
518 )::INTEGER
|
|
jbe@505
|
519 ) % 7 +
|
|
jbe@505
|
520 CASE
|
|
jbe@505
|
521 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
|
|
jbe@505
|
522 THEN 0 ELSE 1
|
|
jbe@505
|
523 END
|
|
jbe@505
|
524 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
|
|
jbe@505
|
525 END AS "pending"
|
|
jbe@505
|
526 FROM (
|
|
jbe@505
|
527 SELECT
|
|
jbe@505
|
528 "id",
|
|
jbe@505
|
529 COALESCE("notification_sent", "activated") AS "notification_sent",
|
|
jbe@505
|
530 "notification_dow",
|
|
jbe@505
|
531 "notification_hour"
|
|
jbe@505
|
532 FROM "member"
|
|
jbe@505
|
533 WHERE "disable_notifications" = FALSE
|
|
jbe@505
|
534 AND "notification_hour" NOTNULL
|
|
jbe@505
|
535 ) AS "subquery1"
|
|
jbe@505
|
536 ) AS "subquery2"
|
|
jbe@505
|
537 WHERE "pending" > '0'::INTERVAL;
|
|
jbe@504
|
538
|
|
jbe@510
|
539 COMMENT ON VIEW "scheduled_notification_to_send" IS 'Set of members where a scheduled notification mail is pending';
|
|
jbe@510
|
540
|
|
jbe@510
|
541 COMMENT ON COLUMN "scheduled_notification_to_send"."recipient_id" IS '"id" of the member who needs to receive a notification mail';
|
|
jbe@510
|
542 COMMENT ON COLUMN "scheduled_notification_to_send"."pending" IS 'Duration for which the notification mail has already been pending';
|
|
jbe@510
|
543
|
|
jbe@497
|
544 CREATE VIEW "newsletter_to_send" AS
|
|
jbe@497
|
545 SELECT
|
|
jbe@499
|
546 "member"."id" AS "recipient_id",
|
|
jbe@499
|
547 "newsletter"."id" AS "newsletter_id"
|
|
jbe@497
|
548 FROM "newsletter" CROSS JOIN "member"
|
|
jbe@497
|
549 LEFT JOIN "privilege" ON
|
|
jbe@497
|
550 "privilege"."member_id" = "member"."id" AND
|
|
jbe@497
|
551 "privilege"."unit_id" = "newsletter"."unit_id" AND
|
|
jbe@497
|
552 "privilege"."voting_right" = TRUE
|
|
jbe@497
|
553 LEFT JOIN "subscription" ON
|
|
jbe@497
|
554 "subscription"."member_id" = "member"."id" AND
|
|
jbe@497
|
555 "subscription"."unit_id" = "newsletter"."unit_id"
|
|
jbe@498
|
556 WHERE "newsletter"."published" <= now()
|
|
jbe@497
|
557 AND "newsletter"."sent" ISNULL
|
|
jbe@497
|
558 AND "member"."locked" = FALSE
|
|
jbe@497
|
559 AND (
|
|
jbe@497
|
560 "member"."disable_notifications" = FALSE OR
|
|
jbe@497
|
561 "newsletter"."include_all_members" = TRUE )
|
|
jbe@497
|
562 AND (
|
|
jbe@497
|
563 "newsletter"."unit_id" ISNULL OR
|
|
jbe@497
|
564 "privilege"."member_id" NOTNULL OR
|
|
jbe@497
|
565 "subscription"."member_id" NOTNULL );
|
|
jbe@497
|
566
|
|
jbe@510
|
567 COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';
|
|
jbe@510
|
568
|
|
jbe@510
|
569 CREATE OR REPLACE FUNCTION "require_transaction_isolation"()
|
|
jbe@510
|
570 RETURNS VOID
|
|
jbe@510
|
571 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@510
|
572 BEGIN
|
|
jbe@510
|
573 IF
|
|
jbe@510
|
574 current_setting('transaction_isolation') NOT IN
|
|
jbe@510
|
575 ('repeatable read', 'serializable')
|
|
jbe@510
|
576 THEN
|
|
jbe@510
|
577 RAISE EXCEPTION 'Insufficient transaction isolation level' USING
|
|
jbe@510
|
578 HINT = 'Consider using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ.';
|
|
jbe@510
|
579 END IF;
|
|
jbe@510
|
580 RETURN;
|
|
jbe@510
|
581 END;
|
|
jbe@510
|
582 $$;
|
|
jbe@510
|
583
|
|
jbe@492
|
584 CREATE FUNCTION "get_initiatives_for_notification"
|
|
jbe@501
|
585 ( "recipient_id_p" "member"."id"%TYPE )
|
|
jbe@492
|
586 RETURNS SETOF "initiative_for_notification"
|
|
jbe@492
|
587 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@492
|
588 DECLARE
|
|
jbe@492
|
589 "result_row" "initiative_for_notification"%ROWTYPE;
|
|
jbe@492
|
590 "last_draft_id_v" "draft"."id"%TYPE;
|
|
jbe@492
|
591 "last_suggestion_id_v" "suggestion"."id"%TYPE;
|
|
jbe@492
|
592 BEGIN
|
|
jbe@492
|
593 PERFORM "require_transaction_isolation"();
|
|
jbe@501
|
594 PERFORM NULL FROM "member" WHERE "id" = "recipient_id_p" FOR UPDATE;
|
|
jbe@492
|
595 FOR "result_row" IN
|
|
jbe@492
|
596 SELECT * FROM "initiative_for_notification"
|
|
jbe@501
|
597 WHERE "recipient_id" = "recipient_id_p"
|
|
jbe@492
|
598 LOOP
|
|
jbe@492
|
599 SELECT "id" INTO "last_draft_id_v" FROM "draft"
|
|
jbe@499
|
600 WHERE "draft"."initiative_id" = "result_row"."initiative_id"
|
|
jbe@492
|
601 ORDER BY "id" DESC LIMIT 1;
|
|
jbe@492
|
602 SELECT "id" INTO "last_suggestion_id_v" FROM "suggestion"
|
|
jbe@499
|
603 WHERE "suggestion"."initiative_id" = "result_row"."initiative_id"
|
|
jbe@492
|
604 ORDER BY "id" DESC LIMIT 1;
|
|
jbe@510
|
605 INSERT INTO "notification_initiative_sent"
|
|
jbe@492
|
606 ("member_id", "initiative_id", "last_draft_id", "last_suggestion_id")
|
|
jbe@492
|
607 VALUES (
|
|
jbe@501
|
608 "recipient_id_p",
|
|
jbe@499
|
609 "result_row"."initiative_id",
|
|
jbe@493
|
610 "last_draft_id_v",
|
|
jbe@493
|
611 "last_suggestion_id_v" )
|
|
jbe@492
|
612 ON CONFLICT ("member_id", "initiative_id") DO UPDATE SET
|
|
jbe@492
|
613 "last_draft_id" = CASE
|
|
jbe@510
|
614 WHEN "notification_initiative_sent"."last_draft_id" > "last_draft_id_v"
|
|
jbe@510
|
615 THEN "notification_initiative_sent"."last_draft_id"
|
|
jbe@492
|
616 ELSE "last_draft_id_v"
|
|
jbe@492
|
617 END,
|
|
jbe@492
|
618 "last_suggestion_id" = CASE
|
|
jbe@510
|
619 WHEN "notification_initiative_sent"."last_suggestion_id" > "last_suggestion_id_v"
|
|
jbe@510
|
620 THEN "notification_initiative_sent"."last_suggestion_id"
|
|
jbe@492
|
621 ELSE "last_suggestion_id_v"
|
|
jbe@492
|
622 END;
|
|
jbe@492
|
623 RETURN NEXT "result_row";
|
|
jbe@492
|
624 END LOOP;
|
|
jbe@510
|
625 DELETE FROM "notification_initiative_sent"
|
|
jbe@492
|
626 USING "initiative", "issue"
|
|
jbe@510
|
627 WHERE "notification_initiative_sent"."member_id" = "recipient_id_p"
|
|
jbe@510
|
628 AND "initiative"."id" = "notification_initiative_sent"."initiative_id"
|
|
jbe@492
|
629 AND "issue"."id" = "initiative"."issue_id"
|
|
jbe@492
|
630 AND ( "issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL );
|
|
jbe@505
|
631 UPDATE "member" SET
|
|
jbe@506
|
632 "notification_counter" = "notification_counter" + 1,
|
|
jbe@505
|
633 "notification_sent" = now()
|
|
jbe@501
|
634 WHERE "id" = "recipient_id_p";
|
|
jbe@492
|
635 RETURN;
|
|
jbe@492
|
636 END;
|
|
jbe@492
|
637 $$;
|
|
jbe@492
|
638
|
|
jbe@511
|
639 COMMENT ON FUNCTION "get_initiatives_for_notification"
|
|
jbe@511
|
640 ( "member"."id"%TYPE )
|
|
jbe@511
|
641 IS 'Returns rows from view "initiative_for_notification" for a given recipient while updating table "notification_initiative_sent" and columns "notification_counter" and "notification_sent" of "member" table';
|
|
jbe@510
|
642
|
|
jbe@510
|
643 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
|
|
jbe@510
|
644 RETURNS VOID
|
|
jbe@510
|
645 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@510
|
646 BEGIN
|
|
jbe@510
|
647 UPDATE "member" SET
|
|
jbe@510
|
648 "last_login" = NULL,
|
|
jbe@510
|
649 "last_delegation_check" = NULL,
|
|
jbe@510
|
650 "login" = NULL,
|
|
jbe@510
|
651 "password" = NULL,
|
|
jbe@510
|
652 "authority" = NULL,
|
|
jbe@510
|
653 "authority_uid" = NULL,
|
|
jbe@510
|
654 "authority_login" = NULL,
|
|
jbe@510
|
655 "locked" = TRUE,
|
|
jbe@510
|
656 "active" = FALSE,
|
|
jbe@510
|
657 "notify_email" = NULL,
|
|
jbe@510
|
658 "notify_email_unconfirmed" = NULL,
|
|
jbe@510
|
659 "notify_email_secret" = NULL,
|
|
jbe@510
|
660 "notify_email_secret_expiry" = NULL,
|
|
jbe@510
|
661 "notify_email_lock_expiry" = NULL,
|
|
jbe@510
|
662 "disable_notifications" = NULL,
|
|
jbe@510
|
663 "notification_counter" = NULL,
|
|
jbe@510
|
664 "notification_sample_size" = NULL,
|
|
jbe@510
|
665 "notification_dow" = NULL,
|
|
jbe@510
|
666 "notification_hour" = NULL,
|
|
jbe@510
|
667 "login_recovery_expiry" = NULL,
|
|
jbe@510
|
668 "password_reset_secret" = NULL,
|
|
jbe@510
|
669 "password_reset_secret_expiry" = NULL,
|
|
jbe@510
|
670 "organizational_unit" = NULL,
|
|
jbe@510
|
671 "internal_posts" = NULL,
|
|
jbe@510
|
672 "realname" = NULL,
|
|
jbe@510
|
673 "birthday" = NULL,
|
|
jbe@510
|
674 "address" = NULL,
|
|
jbe@510
|
675 "email" = NULL,
|
|
jbe@510
|
676 "xmpp_address" = NULL,
|
|
jbe@510
|
677 "website" = NULL,
|
|
jbe@510
|
678 "phone" = NULL,
|
|
jbe@510
|
679 "mobile_phone" = NULL,
|
|
jbe@510
|
680 "profession" = NULL,
|
|
jbe@510
|
681 "external_memberships" = NULL,
|
|
jbe@510
|
682 "external_posts" = NULL,
|
|
jbe@510
|
683 "statement" = NULL
|
|
jbe@510
|
684 WHERE "id" = "member_id_p";
|
|
jbe@510
|
685 -- "text_search_data" is updated by triggers
|
|
jbe@510
|
686 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
|
|
jbe@510
|
687 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
|
|
jbe@510
|
688 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
|
|
jbe@510
|
689 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
|
|
jbe@510
|
690 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
|
|
jbe@510
|
691 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
|
|
jbe@510
|
692 DELETE FROM "session" WHERE "member_id" = "member_id_p";
|
|
jbe@510
|
693 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
|
|
jbe@510
|
694 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
|
|
jbe@510
|
695 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
|
|
jbe@510
|
696 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
|
|
jbe@510
|
697 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
|
|
jbe@510
|
698 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
|
|
jbe@510
|
699 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
|
|
jbe@510
|
700 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
|
|
jbe@510
|
701 DELETE FROM "direct_voter" USING "issue"
|
|
jbe@510
|
702 WHERE "direct_voter"."issue_id" = "issue"."id"
|
|
jbe@510
|
703 AND "issue"."closed" ISNULL
|
|
jbe@510
|
704 AND "member_id" = "member_id_p";
|
|
jbe@510
|
705 RETURN;
|
|
jbe@510
|
706 END;
|
|
jbe@510
|
707 $$;
|
|
jbe@510
|
708
|
|
jbe@510
|
709 CREATE OR REPLACE FUNCTION "delete_private_data"()
|
|
jbe@510
|
710 RETURNS VOID
|
|
jbe@510
|
711 LANGUAGE 'plpgsql' VOLATILE AS $$
|
|
jbe@510
|
712 BEGIN
|
|
jbe@510
|
713 DELETE FROM "temporary_transaction_data";
|
|
jbe@510
|
714 DELETE FROM "member" WHERE "activated" ISNULL;
|
|
jbe@510
|
715 UPDATE "member" SET
|
|
jbe@510
|
716 "invite_code" = NULL,
|
|
jbe@510
|
717 "invite_code_expiry" = NULL,
|
|
jbe@510
|
718 "admin_comment" = NULL,
|
|
jbe@510
|
719 "last_login" = NULL,
|
|
jbe@510
|
720 "last_delegation_check" = NULL,
|
|
jbe@510
|
721 "login" = NULL,
|
|
jbe@510
|
722 "password" = NULL,
|
|
jbe@510
|
723 "authority" = NULL,
|
|
jbe@510
|
724 "authority_uid" = NULL,
|
|
jbe@510
|
725 "authority_login" = NULL,
|
|
jbe@510
|
726 "lang" = NULL,
|
|
jbe@510
|
727 "notify_email" = NULL,
|
|
jbe@510
|
728 "notify_email_unconfirmed" = NULL,
|
|
jbe@510
|
729 "notify_email_secret" = NULL,
|
|
jbe@510
|
730 "notify_email_secret_expiry" = NULL,
|
|
jbe@510
|
731 "notify_email_lock_expiry" = NULL,
|
|
jbe@510
|
732 "disable_notifications" = NULL,
|
|
jbe@510
|
733 "notification_counter" = NULL,
|
|
jbe@510
|
734 "notification_sample_size" = NULL,
|
|
jbe@510
|
735 "notification_dow" = NULL,
|
|
jbe@510
|
736 "notification_hour" = NULL,
|
|
jbe@510
|
737 "login_recovery_expiry" = NULL,
|
|
jbe@510
|
738 "password_reset_secret" = NULL,
|
|
jbe@510
|
739 "password_reset_secret_expiry" = NULL,
|
|
jbe@510
|
740 "organizational_unit" = NULL,
|
|
jbe@510
|
741 "internal_posts" = NULL,
|
|
jbe@510
|
742 "realname" = NULL,
|
|
jbe@510
|
743 "birthday" = NULL,
|
|
jbe@510
|
744 "address" = NULL,
|
|
jbe@510
|
745 "email" = NULL,
|
|
jbe@510
|
746 "xmpp_address" = NULL,
|
|
jbe@510
|
747 "website" = NULL,
|
|
jbe@510
|
748 "phone" = NULL,
|
|
jbe@510
|
749 "mobile_phone" = NULL,
|
|
jbe@510
|
750 "profession" = NULL,
|
|
jbe@510
|
751 "external_memberships" = NULL,
|
|
jbe@510
|
752 "external_posts" = NULL,
|
|
jbe@510
|
753 "formatting_engine" = NULL,
|
|
jbe@510
|
754 "statement" = NULL;
|
|
jbe@510
|
755 -- "text_search_data" is updated by triggers
|
|
jbe@510
|
756 DELETE FROM "setting";
|
|
jbe@510
|
757 DELETE FROM "setting_map";
|
|
jbe@510
|
758 DELETE FROM "member_relation_setting";
|
|
jbe@510
|
759 DELETE FROM "member_image";
|
|
jbe@510
|
760 DELETE FROM "contact";
|
|
jbe@510
|
761 DELETE FROM "ignored_member";
|
|
jbe@510
|
762 DELETE FROM "session";
|
|
jbe@510
|
763 DELETE FROM "area_setting";
|
|
jbe@510
|
764 DELETE FROM "issue_setting";
|
|
jbe@510
|
765 DELETE FROM "ignored_initiative";
|
|
jbe@510
|
766 DELETE FROM "initiative_setting";
|
|
jbe@510
|
767 DELETE FROM "suggestion_setting";
|
|
jbe@510
|
768 DELETE FROM "non_voter";
|
|
jbe@510
|
769 DELETE FROM "direct_voter" USING "issue"
|
|
jbe@510
|
770 WHERE "direct_voter"."issue_id" = "issue"."id"
|
|
jbe@510
|
771 AND "issue"."closed" ISNULL;
|
|
jbe@510
|
772 RETURN;
|
|
jbe@510
|
773 END;
|
|
jbe@510
|
774 $$;
|
|
jbe@510
|
775
|
|
jbe@478
|
776 COMMIT;
|