rev |
line source |
jbe@387
|
1 BEGIN;
|
jbe@387
|
2
|
jbe@387
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
jbe@387
|
4 SELECT * FROM (VALUES ('2.2.4', 2, 2, 4))
|
jbe@387
|
5 AS "subquery"("string", "major", "minor", "revision");
|
jbe@387
|
6
|
jbe@387
|
7
|
jbe@387
|
8 ALTER TABLE "member" ADD COLUMN "last_delegation_check" TIMESTAMPTZ;
|
jbe@387
|
9 ALTER TABLE "member" ADD COLUMN "login_recovery_expiry" TIMESTAMPTZ;
|
jbe@387
|
10
|
jbe@387
|
11 COMMENT ON COLUMN "member"."last_delegation_check" IS 'Timestamp of last delegation check (i.e. confirmation of all unit and area delegations)';
|
jbe@387
|
12 COMMENT ON COLUMN "member"."login_recovery_expiry" IS 'Date/time after which another login recovery attempt is allowed';
|
jbe@387
|
13 COMMENT ON COLUMN "member"."password_reset_secret" IS 'Secret string sent via e-mail for password recovery';
|
jbe@387
|
14 COMMENT ON COLUMN "member"."password_reset_secret_expiry" IS 'Date/time until the password recovery secret is valid, and date/time after which another password recovery attempt is allowed';
|
jbe@387
|
15
|
jbe@387
|
16 ALTER TABLE "session" ADD COLUMN "needs_delegation_check" BOOLEAN NOT NULL DEFAULT FALSE;
|
jbe@387
|
17 COMMENT ON COLUMN "session"."needs_delegation_check" IS 'Set to TRUE, if member must perform a delegation check to proceed with login; see column "last_delegation_check" in "member" table';
|
jbe@387
|
18
|
jbe@387
|
19 CREATE OR REPLACE VIEW "event_seen_by_member" AS
|
jbe@387
|
20 SELECT
|
jbe@387
|
21 "member"."id" AS "seen_by_member_id",
|
jbe@387
|
22 CASE WHEN "event"."state" IN (
|
jbe@387
|
23 'voting',
|
jbe@387
|
24 'finished_without_winner',
|
jbe@387
|
25 'finished_with_winner'
|
jbe@387
|
26 ) THEN
|
jbe@387
|
27 'voting'::"notify_level"
|
jbe@387
|
28 ELSE
|
jbe@387
|
29 CASE WHEN "event"."state" IN (
|
jbe@387
|
30 'verification',
|
jbe@387
|
31 'canceled_after_revocation_during_verification',
|
jbe@387
|
32 'canceled_no_initiative_admitted'
|
jbe@387
|
33 ) THEN
|
jbe@387
|
34 'verification'::"notify_level"
|
jbe@387
|
35 ELSE
|
jbe@387
|
36 CASE WHEN "event"."state" IN (
|
jbe@387
|
37 'discussion',
|
jbe@387
|
38 'canceled_after_revocation_during_discussion'
|
jbe@387
|
39 ) THEN
|
jbe@387
|
40 'discussion'::"notify_level"
|
jbe@387
|
41 ELSE
|
jbe@387
|
42 'all'::"notify_level"
|
jbe@387
|
43 END
|
jbe@387
|
44 END
|
jbe@387
|
45 END AS "notify_level",
|
jbe@387
|
46 "event".*
|
jbe@387
|
47 FROM "member" CROSS JOIN "event"
|
jbe@387
|
48 LEFT JOIN "issue"
|
jbe@387
|
49 ON "event"."issue_id" = "issue"."id"
|
jbe@387
|
50 LEFT JOIN "membership"
|
jbe@387
|
51 ON "member"."id" = "membership"."member_id"
|
jbe@387
|
52 AND "issue"."area_id" = "membership"."area_id"
|
jbe@387
|
53 LEFT JOIN "interest"
|
jbe@387
|
54 ON "member"."id" = "interest"."member_id"
|
jbe@387
|
55 AND "event"."issue_id" = "interest"."issue_id"
|
jbe@387
|
56 LEFT JOIN "ignored_member"
|
jbe@387
|
57 ON "member"."id" = "ignored_member"."member_id"
|
jbe@387
|
58 AND "event"."member_id" = "ignored_member"."other_member_id"
|
jbe@387
|
59 LEFT JOIN "ignored_initiative"
|
jbe@387
|
60 ON "member"."id" = "ignored_initiative"."member_id"
|
jbe@387
|
61 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
|
jbe@387
|
62 WHERE (
|
jbe@387
|
63 "interest"."member_id" NOTNULL OR
|
jbe@387
|
64 ( "membership"."member_id" NOTNULL AND
|
jbe@387
|
65 "event"."event" IN (
|
jbe@387
|
66 'issue_state_changed',
|
jbe@387
|
67 'initiative_created_in_new_issue',
|
jbe@387
|
68 'initiative_created_in_existing_issue',
|
jbe@387
|
69 'initiative_revoked' ) ) )
|
jbe@387
|
70 AND "ignored_member"."member_id" ISNULL
|
jbe@387
|
71 AND "ignored_initiative"."member_id" ISNULL;
|
jbe@387
|
72
|
jbe@387
|
73 CREATE OR REPLACE VIEW "selected_event_seen_by_member" AS
|
jbe@387
|
74 SELECT
|
jbe@387
|
75 "member"."id" AS "seen_by_member_id",
|
jbe@387
|
76 CASE WHEN "event"."state" IN (
|
jbe@387
|
77 'voting',
|
jbe@387
|
78 'finished_without_winner',
|
jbe@387
|
79 'finished_with_winner'
|
jbe@387
|
80 ) THEN
|
jbe@387
|
81 'voting'::"notify_level"
|
jbe@387
|
82 ELSE
|
jbe@387
|
83 CASE WHEN "event"."state" IN (
|
jbe@387
|
84 'verification',
|
jbe@387
|
85 'canceled_after_revocation_during_verification',
|
jbe@387
|
86 'canceled_no_initiative_admitted'
|
jbe@387
|
87 ) THEN
|
jbe@387
|
88 'verification'::"notify_level"
|
jbe@387
|
89 ELSE
|
jbe@387
|
90 CASE WHEN "event"."state" IN (
|
jbe@387
|
91 'discussion',
|
jbe@387
|
92 'canceled_after_revocation_during_discussion'
|
jbe@387
|
93 ) THEN
|
jbe@387
|
94 'discussion'::"notify_level"
|
jbe@387
|
95 ELSE
|
jbe@387
|
96 'all'::"notify_level"
|
jbe@387
|
97 END
|
jbe@387
|
98 END
|
jbe@387
|
99 END AS "notify_level",
|
jbe@387
|
100 "event".*
|
jbe@387
|
101 FROM "member" CROSS JOIN "event"
|
jbe@387
|
102 LEFT JOIN "issue"
|
jbe@387
|
103 ON "event"."issue_id" = "issue"."id"
|
jbe@387
|
104 LEFT JOIN "membership"
|
jbe@387
|
105 ON "member"."id" = "membership"."member_id"
|
jbe@387
|
106 AND "issue"."area_id" = "membership"."area_id"
|
jbe@387
|
107 LEFT JOIN "interest"
|
jbe@387
|
108 ON "member"."id" = "interest"."member_id"
|
jbe@387
|
109 AND "event"."issue_id" = "interest"."issue_id"
|
jbe@387
|
110 LEFT JOIN "ignored_member"
|
jbe@387
|
111 ON "member"."id" = "ignored_member"."member_id"
|
jbe@387
|
112 AND "event"."member_id" = "ignored_member"."other_member_id"
|
jbe@387
|
113 LEFT JOIN "ignored_initiative"
|
jbe@387
|
114 ON "member"."id" = "ignored_initiative"."member_id"
|
jbe@387
|
115 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
|
jbe@387
|
116 WHERE (
|
jbe@387
|
117 ( "member"."notify_level" >= 'all' ) OR
|
jbe@387
|
118 ( "member"."notify_level" >= 'voting' AND
|
jbe@387
|
119 "event"."state" IN (
|
jbe@387
|
120 'voting',
|
jbe@387
|
121 'finished_without_winner',
|
jbe@387
|
122 'finished_with_winner' ) ) OR
|
jbe@387
|
123 ( "member"."notify_level" >= 'verification' AND
|
jbe@387
|
124 "event"."state" IN (
|
jbe@387
|
125 'verification',
|
jbe@387
|
126 'canceled_after_revocation_during_verification',
|
jbe@387
|
127 'canceled_no_initiative_admitted' ) ) OR
|
jbe@387
|
128 ( "member"."notify_level" >= 'discussion' AND
|
jbe@387
|
129 "event"."state" IN (
|
jbe@387
|
130 'discussion',
|
jbe@387
|
131 'canceled_after_revocation_during_discussion' ) ) )
|
jbe@387
|
132 AND (
|
jbe@387
|
133 "interest"."member_id" NOTNULL OR
|
jbe@387
|
134 ( "membership"."member_id" NOTNULL AND
|
jbe@387
|
135 "event"."event" IN (
|
jbe@387
|
136 'issue_state_changed',
|
jbe@387
|
137 'initiative_created_in_new_issue',
|
jbe@387
|
138 'initiative_created_in_existing_issue',
|
jbe@387
|
139 'initiative_revoked' ) ) )
|
jbe@387
|
140 AND "ignored_member"."member_id" ISNULL
|
jbe@387
|
141 AND "ignored_initiative"."member_id" ISNULL;
|
jbe@387
|
142
|
jbe@387
|
143 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
|
jbe@387
|
144 RETURNS VOID
|
jbe@387
|
145 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@387
|
146 BEGIN
|
jbe@387
|
147 UPDATE "member" SET
|
jbe@387
|
148 "last_login" = NULL,
|
jbe@387
|
149 "last_delegation_check" = NULL,
|
jbe@387
|
150 "login" = NULL,
|
jbe@387
|
151 "password" = NULL,
|
jbe@387
|
152 "locked" = TRUE,
|
jbe@387
|
153 "active" = FALSE,
|
jbe@387
|
154 "notify_email" = NULL,
|
jbe@387
|
155 "notify_email_unconfirmed" = NULL,
|
jbe@387
|
156 "notify_email_secret" = NULL,
|
jbe@387
|
157 "notify_email_secret_expiry" = NULL,
|
jbe@387
|
158 "notify_email_lock_expiry" = NULL,
|
jbe@387
|
159 "login_recovery_expiry" = NULL,
|
jbe@387
|
160 "password_reset_secret" = NULL,
|
jbe@387
|
161 "password_reset_secret_expiry" = NULL,
|
jbe@387
|
162 "organizational_unit" = NULL,
|
jbe@387
|
163 "internal_posts" = NULL,
|
jbe@387
|
164 "realname" = NULL,
|
jbe@387
|
165 "birthday" = NULL,
|
jbe@387
|
166 "address" = NULL,
|
jbe@387
|
167 "email" = NULL,
|
jbe@387
|
168 "xmpp_address" = NULL,
|
jbe@387
|
169 "website" = NULL,
|
jbe@387
|
170 "phone" = NULL,
|
jbe@387
|
171 "mobile_phone" = NULL,
|
jbe@387
|
172 "profession" = NULL,
|
jbe@387
|
173 "external_memberships" = NULL,
|
jbe@387
|
174 "external_posts" = NULL,
|
jbe@387
|
175 "statement" = NULL
|
jbe@387
|
176 WHERE "id" = "member_id_p";
|
jbe@387
|
177 -- "text_search_data" is updated by triggers
|
jbe@387
|
178 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
|
jbe@387
|
179 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
|
jbe@387
|
180 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
|
jbe@387
|
181 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
|
jbe@387
|
182 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
|
jbe@387
|
183 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
|
jbe@387
|
184 DELETE FROM "session" WHERE "member_id" = "member_id_p";
|
jbe@387
|
185 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
|
jbe@387
|
186 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
|
jbe@387
|
187 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
|
jbe@387
|
188 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
|
jbe@387
|
189 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
|
jbe@387
|
190 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
|
jbe@387
|
191 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
|
jbe@387
|
192 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
|
jbe@387
|
193 DELETE FROM "direct_voter" USING "issue"
|
jbe@387
|
194 WHERE "direct_voter"."issue_id" = "issue"."id"
|
jbe@387
|
195 AND "issue"."closed" ISNULL
|
jbe@387
|
196 AND "member_id" = "member_id_p";
|
jbe@387
|
197 RETURN;
|
jbe@387
|
198 END;
|
jbe@387
|
199 $$;
|
jbe@387
|
200
|
jbe@387
|
201 CREATE OR REPLACE FUNCTION "delete_private_data"()
|
jbe@387
|
202 RETURNS VOID
|
jbe@387
|
203 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@387
|
204 BEGIN
|
jbe@387
|
205 DELETE FROM "temporary_transaction_data";
|
jbe@387
|
206 DELETE FROM "member" WHERE "activated" ISNULL;
|
jbe@387
|
207 UPDATE "member" SET
|
jbe@387
|
208 "invite_code" = NULL,
|
jbe@387
|
209 "invite_code_expiry" = NULL,
|
jbe@387
|
210 "admin_comment" = NULL,
|
jbe@387
|
211 "last_login" = NULL,
|
jbe@387
|
212 "last_delegation_check" = NULL,
|
jbe@387
|
213 "login" = NULL,
|
jbe@387
|
214 "password" = NULL,
|
jbe@387
|
215 "lang" = NULL,
|
jbe@387
|
216 "notify_email" = NULL,
|
jbe@387
|
217 "notify_email_unconfirmed" = NULL,
|
jbe@387
|
218 "notify_email_secret" = NULL,
|
jbe@387
|
219 "notify_email_secret_expiry" = NULL,
|
jbe@387
|
220 "notify_email_lock_expiry" = NULL,
|
jbe@387
|
221 "notify_level" = NULL,
|
jbe@387
|
222 "login_recovery_expiry" = NULL,
|
jbe@387
|
223 "password_reset_secret" = NULL,
|
jbe@387
|
224 "password_reset_secret_expiry" = NULL,
|
jbe@387
|
225 "organizational_unit" = NULL,
|
jbe@387
|
226 "internal_posts" = NULL,
|
jbe@387
|
227 "realname" = NULL,
|
jbe@387
|
228 "birthday" = NULL,
|
jbe@387
|
229 "address" = NULL,
|
jbe@387
|
230 "email" = NULL,
|
jbe@387
|
231 "xmpp_address" = NULL,
|
jbe@387
|
232 "website" = NULL,
|
jbe@387
|
233 "phone" = NULL,
|
jbe@387
|
234 "mobile_phone" = NULL,
|
jbe@387
|
235 "profession" = NULL,
|
jbe@387
|
236 "external_memberships" = NULL,
|
jbe@387
|
237 "external_posts" = NULL,
|
jbe@387
|
238 "formatting_engine" = NULL,
|
jbe@387
|
239 "statement" = NULL;
|
jbe@387
|
240 -- "text_search_data" is updated by triggers
|
jbe@387
|
241 DELETE FROM "setting";
|
jbe@387
|
242 DELETE FROM "setting_map";
|
jbe@387
|
243 DELETE FROM "member_relation_setting";
|
jbe@387
|
244 DELETE FROM "member_image";
|
jbe@387
|
245 DELETE FROM "contact";
|
jbe@387
|
246 DELETE FROM "ignored_member";
|
jbe@387
|
247 DELETE FROM "session";
|
jbe@387
|
248 DELETE FROM "area_setting";
|
jbe@387
|
249 DELETE FROM "issue_setting";
|
jbe@387
|
250 DELETE FROM "ignored_initiative";
|
jbe@387
|
251 DELETE FROM "initiative_setting";
|
jbe@387
|
252 DELETE FROM "suggestion_setting";
|
jbe@387
|
253 DELETE FROM "non_voter";
|
jbe@387
|
254 DELETE FROM "direct_voter" USING "issue"
|
jbe@387
|
255 WHERE "direct_voter"."issue_id" = "issue"."id"
|
jbe@387
|
256 AND "issue"."closed" ISNULL;
|
jbe@387
|
257 RETURN;
|
jbe@387
|
258 END;
|
jbe@387
|
259 $$;
|
jbe@387
|
260
|
jbe@387
|
261 COMMIT;
|