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