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