rev |
line source |
jbe@520
|
1 BEGIN;
|
jbe@520
|
2
|
jbe@520
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
jbe@520
|
4 SELECT * FROM (VALUES ('3.2.2', 3, 2, 2))
|
jbe@520
|
5 AS "subquery"("string", "major", "minor", "revision");
|
jbe@520
|
6
|
jbe@523
|
7 UPDATE "member" SET
|
jbe@523
|
8 "disable_notifications" = TRUE,
|
jbe@523
|
9 "notification_counter" = DEFAULT,
|
jbe@523
|
10 "notification_sample_size" = 0,
|
jbe@523
|
11 "notification_dow" = NULL,
|
jbe@523
|
12 "notification_hour" = NULL
|
jbe@523
|
13 WHERE "last_login" ISNULL
|
jbe@523
|
14 AND "login" ISNULL
|
jbe@523
|
15 AND "authority_login" ISNULL
|
jbe@523
|
16 AND "locked" = TRUE
|
jbe@523
|
17 AND "active" = FALSE;
|
jbe@523
|
18
|
jbe@524
|
19 CREATE VIEW "member_eligible_to_be_notified" AS
|
jbe@524
|
20 SELECT * FROM "member"
|
jbe@524
|
21 WHERE "activated" NOTNULL AND "locked" = FALSE;
|
jbe@524
|
22
|
jbe@524
|
23 COMMENT ON VIEW "member_eligible_to_be_notified" IS 'Filtered "member" table containing only activated and non-locked members (used as helper view for "member_to_notify" and "newsletter_to_send")';
|
jbe@524
|
24
|
jbe@524
|
25 CREATE VIEW "member_to_notify" AS
|
jbe@524
|
26 SELECT * FROM "member_eligible_to_be_notified"
|
jbe@524
|
27 WHERE "disable_notifications" = FALSE;
|
jbe@524
|
28
|
jbe@524
|
29 COMMENT ON VIEW "member_to_notify" IS 'Filtered "member" table containing only members that are eligible to and wish to receive notifications; NOTE: "notify_email" may still be NULL and might need to be checked by frontend (this allows other means of messaging)';
|
jbe@524
|
30
|
jbe@520
|
31 CREATE OR REPLACE FUNCTION "featured_initiative"
|
jbe@520
|
32 ( "recipient_id_p" "member"."id"%TYPE,
|
jbe@520
|
33 "area_id_p" "area"."id"%TYPE )
|
jbe@520
|
34 RETURNS SETOF "initiative"."id"%TYPE
|
jbe@520
|
35 LANGUAGE 'plpgsql' STABLE AS $$
|
jbe@520
|
36 DECLARE
|
jbe@520
|
37 "counter_v" "member"."notification_counter"%TYPE;
|
jbe@520
|
38 "sample_size_v" "member"."notification_sample_size"%TYPE;
|
jbe@520
|
39 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
|
jbe@520
|
40 "match_v" BOOLEAN;
|
jbe@520
|
41 "member_id_v" "member"."id"%TYPE;
|
jbe@520
|
42 "seed_v" TEXT;
|
jbe@520
|
43 "initiative_id_v" "initiative"."id"%TYPE;
|
jbe@520
|
44 BEGIN
|
jbe@520
|
45 SELECT "notification_counter", "notification_sample_size"
|
jbe@520
|
46 INTO "counter_v", "sample_size_v"
|
jbe@520
|
47 FROM "member" WHERE "id" = "recipient_id_p";
|
jbe@520
|
48 IF COALESCE("sample_size_v" <= 0, TRUE) THEN
|
jbe@520
|
49 RETURN;
|
jbe@520
|
50 END IF;
|
jbe@520
|
51 "initiative_id_ary" := '{}';
|
jbe@520
|
52 LOOP
|
jbe@520
|
53 "match_v" := FALSE;
|
jbe@520
|
54 FOR "member_id_v", "seed_v" IN
|
jbe@520
|
55 SELECT * FROM (
|
jbe@520
|
56 SELECT DISTINCT
|
jbe@520
|
57 "supporter"."member_id",
|
jbe@520
|
58 md5(
|
jbe@520
|
59 "recipient_id_p" || '-' ||
|
jbe@520
|
60 "counter_v" || '-' ||
|
jbe@520
|
61 "area_id_p" || '-' ||
|
jbe@520
|
62 "supporter"."member_id"
|
jbe@520
|
63 ) AS "seed"
|
jbe@520
|
64 FROM "supporter"
|
jbe@520
|
65 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
|
jbe@520
|
66 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
jbe@520
|
67 WHERE "supporter"."member_id" != "recipient_id_p"
|
jbe@520
|
68 AND "issue"."area_id" = "area_id_p"
|
jbe@520
|
69 AND "issue"."state" IN ('admission', 'discussion', 'verification')
|
jbe@520
|
70 ) AS "subquery"
|
jbe@520
|
71 ORDER BY "seed"
|
jbe@520
|
72 LOOP
|
jbe@520
|
73 SELECT "initiative"."id" INTO "initiative_id_v"
|
jbe@520
|
74 FROM "initiative"
|
jbe@520
|
75 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
jbe@520
|
76 JOIN "area" ON "area"."id" = "issue"."area_id"
|
jbe@520
|
77 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
|
jbe@520
|
78 LEFT JOIN "supporter" AS "self_support" ON
|
jbe@520
|
79 "self_support"."initiative_id" = "initiative"."id" AND
|
jbe@520
|
80 "self_support"."member_id" = "recipient_id_p"
|
jbe@520
|
81 LEFT JOIN "privilege" ON
|
jbe@520
|
82 "privilege"."member_id" = "recipient_id_p" AND
|
jbe@520
|
83 "privilege"."unit_id" = "area"."unit_id" AND
|
jbe@520
|
84 "privilege"."voting_right" = TRUE
|
jbe@520
|
85 LEFT JOIN "subscription" ON
|
jbe@520
|
86 "subscription"."member_id" = "recipient_id_p" AND
|
jbe@520
|
87 "subscription"."unit_id" = "area"."unit_id"
|
jbe@520
|
88 LEFT JOIN "ignored_initiative" ON
|
jbe@520
|
89 "ignored_initiative"."member_id" = "recipient_id_p" AND
|
jbe@520
|
90 "ignored_initiative"."initiative_id" = "initiative"."id"
|
jbe@520
|
91 WHERE "supporter"."member_id" = "member_id_v"
|
jbe@520
|
92 AND "issue"."area_id" = "area_id_p"
|
jbe@520
|
93 AND "issue"."state" IN ('admission', 'discussion', 'verification')
|
jbe@520
|
94 AND "initiative"."revoked" ISNULL
|
jbe@520
|
95 AND "self_support"."member_id" ISNULL
|
jbe@520
|
96 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
|
jbe@520
|
97 AND (
|
jbe@520
|
98 "privilege"."member_id" NOTNULL OR
|
jbe@520
|
99 "subscription"."member_id" NOTNULL )
|
jbe@520
|
100 AND "ignored_initiative"."member_id" ISNULL
|
jbe@520
|
101 AND NOT EXISTS (
|
jbe@520
|
102 SELECT NULL FROM "draft"
|
jbe@520
|
103 JOIN "ignored_member" ON
|
jbe@520
|
104 "ignored_member"."member_id" = "recipient_id_p" AND
|
jbe@520
|
105 "ignored_member"."other_member_id" = "draft"."author_id"
|
jbe@520
|
106 WHERE "draft"."initiative_id" = "initiative"."id"
|
jbe@520
|
107 )
|
jbe@520
|
108 ORDER BY md5("seed_v" || '-' || "initiative"."id")
|
jbe@520
|
109 LIMIT 1;
|
jbe@520
|
110 IF FOUND THEN
|
jbe@520
|
111 "match_v" := TRUE;
|
jbe@520
|
112 RETURN NEXT "initiative_id_v";
|
jbe@520
|
113 IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
|
jbe@520
|
114 RETURN;
|
jbe@520
|
115 END IF;
|
jbe@520
|
116 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
|
jbe@520
|
117 END IF;
|
jbe@520
|
118 END LOOP;
|
jbe@520
|
119 EXIT WHEN NOT "match_v";
|
jbe@520
|
120 END LOOP;
|
jbe@520
|
121 RETURN;
|
jbe@520
|
122 END;
|
jbe@520
|
123 $$;
|
jbe@520
|
124
|
jbe@521
|
125 CREATE OR REPLACE VIEW "scheduled_notification_to_send" AS
|
jbe@521
|
126 SELECT * FROM (
|
jbe@521
|
127 SELECT
|
jbe@521
|
128 "id" AS "recipient_id",
|
jbe@521
|
129 now() - CASE WHEN "notification_dow" ISNULL THEN
|
jbe@521
|
130 ( "notification_sent"::DATE + CASE
|
jbe@521
|
131 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
|
jbe@521
|
132 THEN 0 ELSE 1 END
|
jbe@521
|
133 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
|
jbe@521
|
134 ELSE
|
jbe@521
|
135 ( "notification_sent"::DATE +
|
jbe@521
|
136 ( 7 + "notification_dow" -
|
jbe@521
|
137 EXTRACT(DOW FROM
|
jbe@521
|
138 ( "notification_sent"::DATE + CASE
|
jbe@521
|
139 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
|
jbe@521
|
140 THEN 0 ELSE 1 END
|
jbe@521
|
141 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
|
jbe@521
|
142 )::INTEGER
|
jbe@521
|
143 ) % 7 +
|
jbe@521
|
144 CASE
|
jbe@521
|
145 WHEN EXTRACT(HOUR FROM "notification_sent") < "notification_hour"
|
jbe@521
|
146 THEN 0 ELSE 1
|
jbe@521
|
147 END
|
jbe@521
|
148 )::TIMESTAMP + '1 hour'::INTERVAL * "notification_hour"
|
jbe@521
|
149 END AS "pending"
|
jbe@521
|
150 FROM (
|
jbe@521
|
151 SELECT
|
jbe@521
|
152 "id",
|
jbe@521
|
153 COALESCE("notification_sent", "activated") AS "notification_sent",
|
jbe@521
|
154 "notification_dow",
|
jbe@521
|
155 "notification_hour"
|
jbe@524
|
156 FROM "member_to_notify"
|
jbe@524
|
157 WHERE "notification_hour" NOTNULL
|
jbe@521
|
158 ) AS "subquery1"
|
jbe@521
|
159 ) AS "subquery2"
|
jbe@521
|
160 WHERE "pending" > '0'::INTERVAL;
|
jbe@521
|
161
|
jbe@524
|
162 CREATE OR REPLACE VIEW "newsletter_to_send" AS
|
jbe@524
|
163 SELECT
|
jbe@524
|
164 "member"."id" AS "recipient_id",
|
jbe@524
|
165 "newsletter"."id" AS "newsletter_id",
|
jbe@524
|
166 "newsletter"."published"
|
jbe@524
|
167 FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"
|
jbe@524
|
168 LEFT JOIN "privilege" ON
|
jbe@524
|
169 "privilege"."member_id" = "member"."id" AND
|
jbe@524
|
170 "privilege"."unit_id" = "newsletter"."unit_id" AND
|
jbe@524
|
171 "privilege"."voting_right" = TRUE
|
jbe@524
|
172 LEFT JOIN "subscription" ON
|
jbe@524
|
173 "subscription"."member_id" = "member"."id" AND
|
jbe@524
|
174 "subscription"."unit_id" = "newsletter"."unit_id"
|
jbe@524
|
175 WHERE "newsletter"."published" <= now()
|
jbe@524
|
176 AND "newsletter"."sent" ISNULL
|
jbe@524
|
177 AND (
|
jbe@524
|
178 "member"."disable_notifications" = FALSE OR
|
jbe@524
|
179 "newsletter"."include_all_members" = TRUE )
|
jbe@524
|
180 AND (
|
jbe@524
|
181 "newsletter"."unit_id" ISNULL OR
|
jbe@524
|
182 "privilege"."member_id" NOTNULL OR
|
jbe@524
|
183 "subscription"."member_id" NOTNULL );
|
jbe@524
|
184
|
jbe@522
|
185 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
|
jbe@522
|
186 RETURNS VOID
|
jbe@522
|
187 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@522
|
188 BEGIN
|
jbe@522
|
189 UPDATE "member" SET
|
jbe@522
|
190 "last_login" = NULL,
|
jbe@522
|
191 "last_delegation_check" = NULL,
|
jbe@522
|
192 "login" = NULL,
|
jbe@522
|
193 "password" = NULL,
|
jbe@522
|
194 "authority" = NULL,
|
jbe@522
|
195 "authority_uid" = NULL,
|
jbe@522
|
196 "authority_login" = NULL,
|
jbe@522
|
197 "locked" = TRUE,
|
jbe@522
|
198 "active" = FALSE,
|
jbe@522
|
199 "notify_email" = NULL,
|
jbe@522
|
200 "notify_email_unconfirmed" = NULL,
|
jbe@522
|
201 "notify_email_secret" = NULL,
|
jbe@522
|
202 "notify_email_secret_expiry" = NULL,
|
jbe@522
|
203 "notify_email_lock_expiry" = NULL,
|
jbe@522
|
204 "disable_notifications" = TRUE,
|
jbe@522
|
205 "notification_counter" = DEFAULT,
|
jbe@522
|
206 "notification_sample_size" = 0,
|
jbe@522
|
207 "notification_dow" = NULL,
|
jbe@522
|
208 "notification_hour" = NULL,
|
jbe@522
|
209 "login_recovery_expiry" = NULL,
|
jbe@522
|
210 "password_reset_secret" = NULL,
|
jbe@522
|
211 "password_reset_secret_expiry" = NULL,
|
jbe@522
|
212 "organizational_unit" = NULL,
|
jbe@522
|
213 "internal_posts" = NULL,
|
jbe@522
|
214 "realname" = NULL,
|
jbe@522
|
215 "birthday" = NULL,
|
jbe@522
|
216 "address" = NULL,
|
jbe@522
|
217 "email" = NULL,
|
jbe@522
|
218 "xmpp_address" = NULL,
|
jbe@522
|
219 "website" = NULL,
|
jbe@522
|
220 "phone" = NULL,
|
jbe@522
|
221 "mobile_phone" = NULL,
|
jbe@522
|
222 "profession" = NULL,
|
jbe@522
|
223 "external_memberships" = NULL,
|
jbe@522
|
224 "external_posts" = NULL,
|
jbe@522
|
225 "statement" = NULL
|
jbe@522
|
226 WHERE "id" = "member_id_p";
|
jbe@522
|
227 -- "text_search_data" is updated by triggers
|
jbe@522
|
228 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
|
jbe@522
|
229 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
|
jbe@522
|
230 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
|
jbe@522
|
231 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
|
jbe@522
|
232 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
|
jbe@522
|
233 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
|
jbe@522
|
234 DELETE FROM "session" WHERE "member_id" = "member_id_p";
|
jbe@522
|
235 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
|
jbe@522
|
236 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
|
jbe@522
|
237 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
|
jbe@522
|
238 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
|
jbe@522
|
239 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
|
jbe@522
|
240 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
|
jbe@522
|
241 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
|
jbe@522
|
242 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
|
jbe@522
|
243 DELETE FROM "direct_voter" USING "issue"
|
jbe@522
|
244 WHERE "direct_voter"."issue_id" = "issue"."id"
|
jbe@522
|
245 AND "issue"."closed" ISNULL
|
jbe@522
|
246 AND "member_id" = "member_id_p";
|
jbe@522
|
247 RETURN;
|
jbe@522
|
248 END;
|
jbe@522
|
249 $$;
|
jbe@522
|
250
|
jbe@522
|
251 CREATE OR REPLACE FUNCTION "delete_private_data"()
|
jbe@522
|
252 RETURNS VOID
|
jbe@522
|
253 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@522
|
254 BEGIN
|
jbe@522
|
255 DELETE FROM "temporary_transaction_data";
|
jbe@522
|
256 DELETE FROM "member" WHERE "activated" ISNULL;
|
jbe@522
|
257 UPDATE "member" SET
|
jbe@522
|
258 "invite_code" = NULL,
|
jbe@522
|
259 "invite_code_expiry" = NULL,
|
jbe@522
|
260 "admin_comment" = NULL,
|
jbe@522
|
261 "last_login" = NULL,
|
jbe@522
|
262 "last_delegation_check" = NULL,
|
jbe@522
|
263 "login" = NULL,
|
jbe@522
|
264 "password" = NULL,
|
jbe@522
|
265 "authority" = NULL,
|
jbe@522
|
266 "authority_uid" = NULL,
|
jbe@522
|
267 "authority_login" = NULL,
|
jbe@522
|
268 "lang" = NULL,
|
jbe@522
|
269 "notify_email" = NULL,
|
jbe@522
|
270 "notify_email_unconfirmed" = NULL,
|
jbe@522
|
271 "notify_email_secret" = NULL,
|
jbe@522
|
272 "notify_email_secret_expiry" = NULL,
|
jbe@522
|
273 "notify_email_lock_expiry" = NULL,
|
jbe@522
|
274 "disable_notifications" = TRUE,
|
jbe@522
|
275 "notification_counter" = DEFAULT,
|
jbe@522
|
276 "notification_sample_size" = 0,
|
jbe@522
|
277 "notification_dow" = NULL,
|
jbe@522
|
278 "notification_hour" = NULL,
|
jbe@522
|
279 "login_recovery_expiry" = NULL,
|
jbe@522
|
280 "password_reset_secret" = NULL,
|
jbe@522
|
281 "password_reset_secret_expiry" = NULL,
|
jbe@522
|
282 "organizational_unit" = NULL,
|
jbe@522
|
283 "internal_posts" = NULL,
|
jbe@522
|
284 "realname" = NULL,
|
jbe@522
|
285 "birthday" = NULL,
|
jbe@522
|
286 "address" = NULL,
|
jbe@522
|
287 "email" = NULL,
|
jbe@522
|
288 "xmpp_address" = NULL,
|
jbe@522
|
289 "website" = NULL,
|
jbe@522
|
290 "phone" = NULL,
|
jbe@522
|
291 "mobile_phone" = NULL,
|
jbe@522
|
292 "profession" = NULL,
|
jbe@522
|
293 "external_memberships" = NULL,
|
jbe@522
|
294 "external_posts" = NULL,
|
jbe@522
|
295 "formatting_engine" = NULL,
|
jbe@522
|
296 "statement" = NULL;
|
jbe@522
|
297 -- "text_search_data" is updated by triggers
|
jbe@522
|
298 DELETE FROM "setting";
|
jbe@522
|
299 DELETE FROM "setting_map";
|
jbe@522
|
300 DELETE FROM "member_relation_setting";
|
jbe@522
|
301 DELETE FROM "member_image";
|
jbe@522
|
302 DELETE FROM "contact";
|
jbe@522
|
303 DELETE FROM "ignored_member";
|
jbe@522
|
304 DELETE FROM "session";
|
jbe@522
|
305 DELETE FROM "area_setting";
|
jbe@522
|
306 DELETE FROM "issue_setting";
|
jbe@522
|
307 DELETE FROM "ignored_initiative";
|
jbe@522
|
308 DELETE FROM "initiative_setting";
|
jbe@522
|
309 DELETE FROM "suggestion_setting";
|
jbe@522
|
310 DELETE FROM "non_voter";
|
jbe@522
|
311 DELETE FROM "direct_voter" USING "issue"
|
jbe@522
|
312 WHERE "direct_voter"."issue_id" = "issue"."id"
|
jbe@522
|
313 AND "issue"."closed" ISNULL;
|
jbe@522
|
314 RETURN;
|
jbe@522
|
315 END;
|
jbe@522
|
316 $$;
|
jbe@522
|
317
|
jbe@520
|
318 COMMIT;
|