rev |
line source |
poelzi@79
|
1 BEGIN;
|
poelzi@79
|
2
|
poelzi@79
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
jbe@86
|
4 SELECT * FROM (VALUES ('1.3.0', 1, 3, 0))
|
poelzi@79
|
5 AS "subquery"("string", "major", "minor", "revision");
|
poelzi@79
|
6
|
jbe@86
|
7
|
jbe@86
|
8 -- update comment for column "fully_frozen" of table "issue"
|
jbe@86
|
9
|
jbe@86
|
10 COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed and voting has started; Frontends must ensure that for fully_frozen issues additionally to the restrictions for half_frozen issues a) initiatives are not created, b) no interest is created or removed, c) no supporters are added or removed, d) no opinions are created, changed or deleted.';
|
jbe@86
|
11
|
jbe@86
|
12
|
jbe@86
|
13 -- update comment for column "autoreject" of table "membership"
|
jbe@86
|
14
|
jbe@86
|
15 COMMENT ON COLUMN "membership"."autoreject" IS 'TRUE = member votes against all initiatives, if he is neither direct_ or delegating_voter; Entries in the "interest" table can override this setting.';
|
jbe@86
|
16
|
jbe@86
|
17
|
jbe@86
|
18 -- allow column "autoreject" of table "interest" to be NULL
|
jbe@86
|
19 -- (thus defaulting to "membership")
|
jbe@86
|
20
|
jbe@86
|
21 ALTER TABLE "interest" ALTER COLUMN "autoreject" DROP NOT NULL;
|
jbe@86
|
22
|
jbe@86
|
23
|
jbe@86
|
24 -- new table "ignored_issue" to allow members to ignore particular issues in certain states
|
jbe@86
|
25
|
jbe@86
|
26 CREATE TABLE "ignored_issue" (
|
jbe@86
|
27 PRIMARY KEY ("issue_id", "member_id"),
|
jbe@86
|
28 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@86
|
29 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@86
|
30 "new" BOOLEAN NOT NULL DEFAULT FALSE,
|
jbe@86
|
31 "accepted" BOOLEAN NOT NULL DEFAULT FALSE,
|
jbe@86
|
32 "half_frozen" BOOLEAN NOT NULL DEFAULT FALSE,
|
jbe@86
|
33 "fully_frozen" BOOLEAN NOT NULL DEFAULT FALSE );
|
jbe@86
|
34 CREATE INDEX "ignored_issue_member_id_idx" ON "ignored_issue" ("member_id");
|
jbe@86
|
35
|
jbe@86
|
36 COMMENT ON TABLE "ignored_issue" IS 'Table to store member specific options to ignore issues in selected states';
|
jbe@86
|
37
|
jbe@93
|
38 COMMENT ON COLUMN "ignored_issue"."new" IS 'Apply when issue is neither closed nor accepted';
|
jbe@93
|
39 COMMENT ON COLUMN "ignored_issue"."accepted" IS 'Apply when issue is accepted but not (half_)frozen or closed';
|
jbe@93
|
40 COMMENT ON COLUMN "ignored_issue"."half_frozen" IS 'Apply when issue is half_frozen but not fully_frozen or closed';
|
jbe@93
|
41 COMMENT ON COLUMN "ignored_issue"."fully_frozen" IS 'Apply when issue is fully_frozen (in voting) and not closed';
|
jbe@86
|
42
|
jbe@86
|
43
|
jbe@86
|
44 -- allow area and issue delegations with trustee_id set to NULL
|
jbe@86
|
45 -- (indicating that global or area delegation is void for that area or issue)
|
jbe@86
|
46
|
jbe@86
|
47 ALTER TABLE "delegation" ALTER COLUMN "trustee_id" DROP NOT NULL;
|
jbe@86
|
48
|
jbe@86
|
49 ALTER TABLE "delegation" ADD CONSTRAINT "no_global_delegation_to_null"
|
jbe@86
|
50 CHECK ("trustee_id" NOTNULL OR "scope" != 'global');
|
jbe@86
|
51
|
jbe@86
|
52
|
jbe@86
|
53 -- disable and delete "copy_autoreject" trigger on table "interest"
|
jbe@86
|
54
|
jbe@86
|
55 DROP TRIGGER "copy_autoreject" ON "interest";
|
jbe@86
|
56 DROP FUNCTION "copy_autoreject_trigger"();
|
jbe@86
|
57
|
jbe@86
|
58
|
jbe@86
|
59 -- update comments on delegation views
|
jbe@86
|
60
|
jbe@86
|
61 COMMENT ON VIEW "active_delegation" IS 'Helper view for views "global_delegation", "area_delegation" and "issue_delegation": Contains delegations where the truster_id refers to an active member and includes those delegations where trustee_id is NULL';
|
jbe@86
|
62
|
jbe@86
|
63 COMMENT ON VIEW "area_delegation" IS 'Resulting area delegations from active members; can include rows with trustee_id set to NULL';
|
jbe@86
|
64
|
jbe@86
|
65 COMMENT ON VIEW "issue_delegation" IS 'Resulting issue delegations from active members; can include rows with trustee_id set to NULL';
|
jbe@86
|
66
|
jbe@86
|
67
|
jbe@93
|
68 -- support for explicitly disabled delegations in "delegation_chain" functions
|
jbe@86
|
69
|
jbe@86
|
70 DROP FUNCTION "delegation_chain"
|
jbe@86
|
71 ( "member"."id"%TYPE,
|
jbe@86
|
72 "area"."id"%TYPE,
|
jbe@86
|
73 "issue"."id"%TYPE );
|
jbe@86
|
74
|
jbe@86
|
75 DROP FUNCTION "delegation_chain"
|
jbe@86
|
76 ( "member"."id"%TYPE,
|
jbe@86
|
77 "area"."id"%TYPE,
|
jbe@86
|
78 "issue"."id"%TYPE,
|
jbe@86
|
79 "member"."id"%TYPE );
|
jbe@86
|
80
|
jbe@86
|
81 DROP TYPE "delegation_chain_row";
|
jbe@86
|
82
|
jbe@86
|
83 CREATE TYPE "delegation_chain_row" AS (
|
jbe@86
|
84 "index" INT4,
|
jbe@86
|
85 "member_id" INT4,
|
jbe@86
|
86 "member_active" BOOLEAN,
|
jbe@86
|
87 "participation" BOOLEAN,
|
jbe@86
|
88 "overridden" BOOLEAN,
|
jbe@86
|
89 "scope_in" "delegation_scope",
|
jbe@86
|
90 "scope_out" "delegation_scope",
|
jbe@86
|
91 "disabled_out" BOOLEAN,
|
jbe@86
|
92 "loop" "delegation_chain_loop_tag" );
|
jbe@86
|
93
|
jbe@86
|
94 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
|
jbe@86
|
95
|
jbe@86
|
96 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
|
jbe@86
|
97 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest, for areas: membership, for global delegation chains: always null';
|
jbe@86
|
98 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
|
jbe@86
|
99 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
|
jbe@86
|
100 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
|
jbe@86
|
101 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
|
jbe@86
|
102 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
|
jbe@86
|
103
|
jbe@86
|
104
|
jbe@86
|
105 CREATE FUNCTION "delegation_chain"
|
jbe@86
|
106 ( "member_id_p" "member"."id"%TYPE,
|
jbe@86
|
107 "area_id_p" "area"."id"%TYPE,
|
jbe@86
|
108 "issue_id_p" "issue"."id"%TYPE,
|
jbe@86
|
109 "simulate_trustee_id_p" "member"."id"%TYPE )
|
jbe@86
|
110 RETURNS SETOF "delegation_chain_row"
|
jbe@86
|
111 LANGUAGE 'plpgsql' STABLE AS $$
|
jbe@86
|
112 DECLARE
|
jbe@86
|
113 "issue_row" "issue"%ROWTYPE;
|
jbe@86
|
114 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
|
jbe@86
|
115 "loop_member_id_v" "member"."id"%TYPE;
|
jbe@86
|
116 "output_row" "delegation_chain_row";
|
jbe@86
|
117 "output_rows" "delegation_chain_row"[];
|
jbe@86
|
118 "delegation_row" "delegation"%ROWTYPE;
|
jbe@86
|
119 "row_count" INT4;
|
jbe@86
|
120 "i" INT4;
|
jbe@86
|
121 "loop_v" BOOLEAN;
|
jbe@86
|
122 BEGIN
|
jbe@86
|
123 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@86
|
124 "visited_member_ids" := '{}';
|
jbe@86
|
125 "loop_member_id_v" := NULL;
|
jbe@86
|
126 "output_rows" := '{}';
|
jbe@86
|
127 "output_row"."index" := 0;
|
jbe@86
|
128 "output_row"."member_id" := "member_id_p";
|
jbe@86
|
129 "output_row"."member_active" := TRUE;
|
jbe@86
|
130 "output_row"."participation" := FALSE;
|
jbe@86
|
131 "output_row"."overridden" := FALSE;
|
jbe@86
|
132 "output_row"."disabled_out" := FALSE;
|
jbe@86
|
133 "output_row"."scope_out" := NULL;
|
jbe@86
|
134 LOOP
|
jbe@86
|
135 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
|
jbe@86
|
136 "loop_member_id_v" := "output_row"."member_id";
|
jbe@86
|
137 ELSE
|
jbe@86
|
138 "visited_member_ids" :=
|
jbe@86
|
139 "visited_member_ids" || "output_row"."member_id";
|
jbe@86
|
140 END IF;
|
jbe@86
|
141 IF "output_row"."participation" THEN
|
jbe@86
|
142 "output_row"."overridden" := TRUE;
|
jbe@86
|
143 END IF;
|
jbe@86
|
144 "output_row"."scope_in" := "output_row"."scope_out";
|
jbe@86
|
145 IF EXISTS (
|
jbe@86
|
146 SELECT NULL FROM "member"
|
jbe@86
|
147 WHERE "id" = "output_row"."member_id" AND "active"
|
jbe@86
|
148 ) THEN
|
jbe@86
|
149 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
|
jbe@86
|
150 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@86
|
151 WHERE "truster_id" = "output_row"."member_id"
|
jbe@86
|
152 AND "scope" = 'global';
|
jbe@86
|
153 ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN
|
jbe@86
|
154 "output_row"."participation" := EXISTS (
|
jbe@86
|
155 SELECT NULL FROM "membership"
|
jbe@86
|
156 WHERE "area_id" = "area_id_p"
|
jbe@86
|
157 AND "member_id" = "output_row"."member_id"
|
jbe@86
|
158 );
|
jbe@86
|
159 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@86
|
160 WHERE "truster_id" = "output_row"."member_id"
|
jbe@86
|
161 AND ("scope" = 'global' OR "area_id" = "area_id_p")
|
jbe@86
|
162 ORDER BY "scope" DESC;
|
jbe@86
|
163 ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN
|
jbe@86
|
164 "output_row"."participation" := EXISTS (
|
jbe@86
|
165 SELECT NULL FROM "interest"
|
jbe@86
|
166 WHERE "issue_id" = "issue_id_p"
|
jbe@86
|
167 AND "member_id" = "output_row"."member_id"
|
jbe@86
|
168 );
|
jbe@86
|
169 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@86
|
170 WHERE "truster_id" = "output_row"."member_id"
|
jbe@86
|
171 AND ("scope" = 'global' OR
|
jbe@86
|
172 "area_id" = "issue_row"."area_id" OR
|
jbe@86
|
173 "issue_id" = "issue_id_p"
|
jbe@86
|
174 )
|
jbe@86
|
175 ORDER BY "scope" DESC;
|
jbe@86
|
176 ELSE
|
jbe@86
|
177 RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';
|
jbe@86
|
178 END IF;
|
jbe@86
|
179 ELSE
|
jbe@86
|
180 "output_row"."member_active" := FALSE;
|
jbe@86
|
181 "output_row"."participation" := FALSE;
|
jbe@86
|
182 "output_row"."scope_out" := NULL;
|
jbe@86
|
183 "delegation_row" := ROW(NULL);
|
jbe@86
|
184 END IF;
|
jbe@86
|
185 IF
|
jbe@86
|
186 "output_row"."member_id" = "member_id_p" AND
|
jbe@86
|
187 "simulate_trustee_id_p" NOTNULL
|
jbe@86
|
188 THEN
|
jbe@86
|
189 "output_row"."scope_out" := CASE
|
jbe@86
|
190 WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global'
|
jbe@86
|
191 WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area'
|
jbe@86
|
192 WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue'
|
jbe@86
|
193 END;
|
jbe@86
|
194 "output_rows" := "output_rows" || "output_row";
|
jbe@86
|
195 "output_row"."member_id" := "simulate_trustee_id_p";
|
jbe@86
|
196 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
|
jbe@86
|
197 "output_row"."scope_out" := "delegation_row"."scope";
|
jbe@86
|
198 "output_rows" := "output_rows" || "output_row";
|
jbe@86
|
199 "output_row"."member_id" := "delegation_row"."trustee_id";
|
jbe@86
|
200 ELSIF "delegation_row"."scope" NOTNULL THEN
|
jbe@86
|
201 "output_row"."scope_out" := "delegation_row"."scope";
|
jbe@86
|
202 "output_row"."disabled_out" := TRUE;
|
jbe@86
|
203 "output_rows" := "output_rows" || "output_row";
|
jbe@86
|
204 EXIT;
|
jbe@86
|
205 ELSE
|
jbe@86
|
206 "output_row"."scope_out" := NULL;
|
jbe@86
|
207 "output_rows" := "output_rows" || "output_row";
|
jbe@86
|
208 EXIT;
|
jbe@86
|
209 END IF;
|
jbe@86
|
210 EXIT WHEN "loop_member_id_v" NOTNULL;
|
jbe@86
|
211 "output_row"."index" := "output_row"."index" + 1;
|
jbe@86
|
212 END LOOP;
|
jbe@86
|
213 "row_count" := array_upper("output_rows", 1);
|
jbe@86
|
214 "i" := 1;
|
jbe@86
|
215 "loop_v" := FALSE;
|
jbe@86
|
216 LOOP
|
jbe@86
|
217 "output_row" := "output_rows"["i"];
|
jbe@86
|
218 EXIT WHEN "output_row" ISNULL;
|
jbe@86
|
219 IF "loop_v" THEN
|
jbe@86
|
220 IF "i" + 1 = "row_count" THEN
|
jbe@86
|
221 "output_row"."loop" := 'last';
|
jbe@86
|
222 ELSIF "i" = "row_count" THEN
|
jbe@86
|
223 "output_row"."loop" := 'repetition';
|
jbe@86
|
224 ELSE
|
jbe@86
|
225 "output_row"."loop" := 'intermediate';
|
jbe@86
|
226 END IF;
|
jbe@86
|
227 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
|
jbe@86
|
228 "output_row"."loop" := 'first';
|
jbe@86
|
229 "loop_v" := TRUE;
|
jbe@86
|
230 END IF;
|
jbe@86
|
231 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
|
jbe@86
|
232 "output_row"."participation" := NULL;
|
jbe@86
|
233 END IF;
|
jbe@86
|
234 RETURN NEXT "output_row";
|
jbe@86
|
235 "i" := "i" + 1;
|
jbe@86
|
236 END LOOP;
|
jbe@86
|
237 RETURN;
|
jbe@86
|
238 END;
|
jbe@86
|
239 $$;
|
jbe@86
|
240
|
jbe@86
|
241 COMMENT ON FUNCTION "delegation_chain"
|
jbe@86
|
242 ( "member"."id"%TYPE,
|
jbe@86
|
243 "area"."id"%TYPE,
|
jbe@86
|
244 "issue"."id"%TYPE,
|
jbe@86
|
245 "member"."id"%TYPE )
|
jbe@86
|
246 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
|
jbe@86
|
247
|
jbe@86
|
248 CREATE FUNCTION "delegation_chain"
|
jbe@86
|
249 ( "member_id_p" "member"."id"%TYPE,
|
jbe@86
|
250 "area_id_p" "area"."id"%TYPE,
|
jbe@86
|
251 "issue_id_p" "issue"."id"%TYPE )
|
jbe@86
|
252 RETURNS SETOF "delegation_chain_row"
|
jbe@86
|
253 LANGUAGE 'plpgsql' STABLE AS $$
|
jbe@86
|
254 DECLARE
|
jbe@86
|
255 "result_row" "delegation_chain_row";
|
jbe@86
|
256 BEGIN
|
jbe@86
|
257 FOR "result_row" IN
|
jbe@86
|
258 SELECT * FROM "delegation_chain"(
|
jbe@86
|
259 "member_id_p", "area_id_p", "issue_id_p", NULL
|
jbe@86
|
260 )
|
jbe@86
|
261 LOOP
|
jbe@86
|
262 RETURN NEXT "result_row";
|
jbe@86
|
263 END LOOP;
|
jbe@86
|
264 RETURN;
|
jbe@86
|
265 END;
|
jbe@86
|
266 $$;
|
jbe@86
|
267
|
jbe@86
|
268 COMMENT ON FUNCTION "delegation_chain"
|
jbe@86
|
269 ( "member"."id"%TYPE,
|
jbe@86
|
270 "area"."id"%TYPE,
|
jbe@86
|
271 "issue"."id"%TYPE )
|
jbe@86
|
272 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
|
jbe@86
|
273
|
jbe@86
|
274
|
jbe@86
|
275 -- delete entries of "ignored_issue" table in "delete_member"(...) and "delete_private_data"() functions
|
jbe@86
|
276
|
jbe@94
|
277 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
|
jbe@94
|
278 RETURNS VOID
|
jbe@94
|
279 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@94
|
280 DECLARE
|
jbe@94
|
281 "issue_row" "issue"%ROWTYPE;
|
jbe@94
|
282 BEGIN
|
jbe@94
|
283 SELECT * INTO "issue_row"
|
jbe@94
|
284 FROM "issue" WHERE "id" = "issue_id_p"
|
jbe@94
|
285 FOR UPDATE;
|
jbe@94
|
286 IF "issue_row"."cleaned" ISNULL THEN
|
jbe@94
|
287 UPDATE "issue" SET
|
jbe@94
|
288 "closed" = NULL,
|
jbe@94
|
289 "ranks_available" = FALSE
|
jbe@94
|
290 WHERE "id" = "issue_id_p";
|
jbe@94
|
291 DELETE FROM "delegating_voter"
|
jbe@94
|
292 WHERE "issue_id" = "issue_id_p";
|
jbe@94
|
293 DELETE FROM "direct_voter"
|
jbe@94
|
294 WHERE "issue_id" = "issue_id_p";
|
jbe@94
|
295 DELETE FROM "delegating_interest_snapshot"
|
jbe@94
|
296 WHERE "issue_id" = "issue_id_p";
|
jbe@94
|
297 DELETE FROM "direct_interest_snapshot"
|
jbe@94
|
298 WHERE "issue_id" = "issue_id_p";
|
jbe@94
|
299 DELETE FROM "delegating_population_snapshot"
|
jbe@94
|
300 WHERE "issue_id" = "issue_id_p";
|
jbe@94
|
301 DELETE FROM "direct_population_snapshot"
|
jbe@94
|
302 WHERE "issue_id" = "issue_id_p";
|
jbe@94
|
303 DELETE FROM "ignored_issue"
|
jbe@94
|
304 WHERE "issue_id" = "issue_id_p";
|
jbe@94
|
305 DELETE FROM "delegation"
|
jbe@94
|
306 WHERE "issue_id" = "issue_id_p";
|
jbe@94
|
307 DELETE FROM "supporter"
|
jbe@94
|
308 WHERE "issue_id" = "issue_id_p";
|
jbe@94
|
309 UPDATE "issue" SET
|
jbe@94
|
310 "closed" = "issue_row"."closed",
|
jbe@94
|
311 "ranks_available" = "issue_row"."ranks_available",
|
jbe@94
|
312 "cleaned" = now()
|
jbe@94
|
313 WHERE "id" = "issue_id_p";
|
jbe@94
|
314 END IF;
|
jbe@94
|
315 RETURN;
|
jbe@94
|
316 END;
|
jbe@94
|
317 $$;
|
jbe@94
|
318
|
jbe@86
|
319 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
|
jbe@86
|
320 RETURNS VOID
|
jbe@86
|
321 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@86
|
322 BEGIN
|
jbe@86
|
323 UPDATE "member" SET
|
jbe@86
|
324 "last_login" = NULL,
|
jbe@86
|
325 "login" = NULL,
|
jbe@86
|
326 "password" = NULL,
|
jbe@86
|
327 "active" = FALSE,
|
jbe@86
|
328 "notify_email" = NULL,
|
jbe@86
|
329 "notify_email_unconfirmed" = NULL,
|
jbe@86
|
330 "notify_email_secret" = NULL,
|
jbe@86
|
331 "notify_email_secret_expiry" = NULL,
|
jbe@86
|
332 "notify_email_lock_expiry" = NULL,
|
jbe@86
|
333 "password_reset_secret" = NULL,
|
jbe@86
|
334 "password_reset_secret_expiry" = NULL,
|
jbe@86
|
335 "organizational_unit" = NULL,
|
jbe@86
|
336 "internal_posts" = NULL,
|
jbe@86
|
337 "realname" = NULL,
|
jbe@86
|
338 "birthday" = NULL,
|
jbe@86
|
339 "address" = NULL,
|
jbe@86
|
340 "email" = NULL,
|
jbe@86
|
341 "xmpp_address" = NULL,
|
jbe@86
|
342 "website" = NULL,
|
jbe@86
|
343 "phone" = NULL,
|
jbe@86
|
344 "mobile_phone" = NULL,
|
jbe@86
|
345 "profession" = NULL,
|
jbe@86
|
346 "external_memberships" = NULL,
|
jbe@86
|
347 "external_posts" = NULL,
|
jbe@86
|
348 "statement" = NULL
|
jbe@86
|
349 WHERE "id" = "member_id_p";
|
jbe@86
|
350 -- "text_search_data" is updated by triggers
|
jbe@86
|
351 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
|
jbe@86
|
352 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
|
jbe@86
|
353 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
|
jbe@86
|
354 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
|
jbe@86
|
355 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
|
jbe@86
|
356 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
|
jbe@86
|
357 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
|
jbe@86
|
358 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
|
jbe@86
|
359 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
|
jbe@86
|
360 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
|
jbe@94
|
361 DELETE FROM "ignored_issue" WHERE "member_id" = "member_id_p";
|
jbe@86
|
362 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
|
jbe@86
|
363 DELETE FROM "direct_voter" USING "issue"
|
jbe@86
|
364 WHERE "direct_voter"."issue_id" = "issue"."id"
|
jbe@86
|
365 AND "issue"."closed" ISNULL
|
jbe@86
|
366 AND "member_id" = "member_id_p";
|
jbe@86
|
367 RETURN;
|
jbe@86
|
368 END;
|
jbe@86
|
369 $$;
|
jbe@86
|
370
|
jbe@86
|
371 CREATE OR REPLACE FUNCTION "delete_private_data"()
|
jbe@86
|
372 RETURNS VOID
|
jbe@86
|
373 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@86
|
374 BEGIN
|
jbe@86
|
375 UPDATE "member" SET
|
jbe@86
|
376 "last_login" = NULL,
|
jbe@86
|
377 "login" = NULL,
|
jbe@86
|
378 "password" = NULL,
|
jbe@86
|
379 "notify_email" = NULL,
|
jbe@86
|
380 "notify_email_unconfirmed" = NULL,
|
jbe@86
|
381 "notify_email_secret" = NULL,
|
jbe@86
|
382 "notify_email_secret_expiry" = NULL,
|
jbe@86
|
383 "notify_email_lock_expiry" = NULL,
|
jbe@86
|
384 "password_reset_secret" = NULL,
|
jbe@86
|
385 "password_reset_secret_expiry" = NULL,
|
jbe@86
|
386 "organizational_unit" = NULL,
|
jbe@86
|
387 "internal_posts" = NULL,
|
jbe@86
|
388 "realname" = NULL,
|
jbe@86
|
389 "birthday" = NULL,
|
jbe@86
|
390 "address" = NULL,
|
jbe@86
|
391 "email" = NULL,
|
jbe@86
|
392 "xmpp_address" = NULL,
|
jbe@86
|
393 "website" = NULL,
|
jbe@86
|
394 "phone" = NULL,
|
jbe@86
|
395 "mobile_phone" = NULL,
|
jbe@86
|
396 "profession" = NULL,
|
jbe@86
|
397 "external_memberships" = NULL,
|
jbe@86
|
398 "external_posts" = NULL,
|
jbe@86
|
399 "statement" = NULL;
|
jbe@86
|
400 -- "text_search_data" is updated by triggers
|
jbe@86
|
401 DELETE FROM "invite_code";
|
jbe@86
|
402 DELETE FROM "setting";
|
jbe@86
|
403 DELETE FROM "setting_map";
|
jbe@86
|
404 DELETE FROM "member_relation_setting";
|
jbe@86
|
405 DELETE FROM "member_image";
|
jbe@86
|
406 DELETE FROM "contact";
|
jbe@86
|
407 DELETE FROM "session";
|
jbe@86
|
408 DELETE FROM "area_setting";
|
jbe@86
|
409 DELETE FROM "issue_setting";
|
jbe@86
|
410 DELETE FROM "initiative_setting";
|
jbe@86
|
411 DELETE FROM "suggestion_setting";
|
jbe@94
|
412 DELETE FROM "ignored_issue";
|
jbe@86
|
413 DELETE FROM "direct_voter" USING "issue"
|
jbe@86
|
414 WHERE "direct_voter"."issue_id" = "issue"."id"
|
jbe@86
|
415 AND "issue"."closed" ISNULL;
|
jbe@86
|
416 RETURN;
|
jbe@86
|
417 END;
|
jbe@86
|
418 $$;
|
jbe@86
|
419
|
jbe@86
|
420
|
poelzi@79
|
421 COMMIT;
|