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