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