liquid_feedback_core
view update/core-update.v1.2.9-v1.3.0.sql @ 93:20be30c73831
Adjusted file update/core-update.v1.2.9-v1.3.0.sql to match changes in core.sql
author | jbe |
---|---|
date | Sun Oct 31 02:14:18 2010 +0200 (2010-10-31) |
parents | d7eadecc7b05 |
children | fb9688f31740 |
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('1.3.0', 1, 3, 0))
5 AS "subquery"("string", "major", "minor", "revision");
8 -- update comment for column "fully_frozen" of table "issue"
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.';
13 -- update comment for column "autoreject" of table "membership"
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.';
18 -- allow column "autoreject" of table "interest" to be NULL
19 -- (thus defaulting to "membership")
21 ALTER TABLE "interest" ALTER COLUMN "autoreject" DROP NOT NULL;
24 -- new table "ignored_issue" to allow members to ignore particular issues in certain states
26 CREATE TABLE "ignored_issue" (
27 PRIMARY KEY ("issue_id", "member_id"),
28 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
29 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
30 "new" BOOLEAN NOT NULL DEFAULT FALSE,
31 "accepted" BOOLEAN NOT NULL DEFAULT FALSE,
32 "half_frozen" BOOLEAN NOT NULL DEFAULT FALSE,
33 "fully_frozen" BOOLEAN NOT NULL DEFAULT FALSE );
34 CREATE INDEX "ignored_issue_member_id_idx" ON "ignored_issue" ("member_id");
36 COMMENT ON TABLE "ignored_issue" IS 'Table to store member specific options to ignore issues in selected states';
38 COMMENT ON COLUMN "ignored_issue"."new" IS 'Apply when issue is neither closed nor accepted';
39 COMMENT ON COLUMN "ignored_issue"."accepted" IS 'Apply when issue is accepted but not (half_)frozen or closed';
40 COMMENT ON COLUMN "ignored_issue"."half_frozen" IS 'Apply when issue is half_frozen but not fully_frozen or closed';
41 COMMENT ON COLUMN "ignored_issue"."fully_frozen" IS 'Apply when issue is fully_frozen (in voting) and not closed';
44 -- allow area and issue delegations with trustee_id set to NULL
45 -- (indicating that global or area delegation is void for that area or issue)
47 ALTER TABLE "delegation" ALTER COLUMN "trustee_id" DROP NOT NULL;
49 ALTER TABLE "delegation" ADD CONSTRAINT "no_global_delegation_to_null"
50 CHECK ("trustee_id" NOTNULL OR "scope" != 'global');
53 -- disable and delete "copy_autoreject" trigger on table "interest"
55 DROP TRIGGER "copy_autoreject" ON "interest";
56 DROP FUNCTION "copy_autoreject_trigger"();
59 -- update comments on delegation views
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';
63 COMMENT ON VIEW "area_delegation" IS 'Resulting area delegations from active members; can include rows with trustee_id set to NULL';
65 COMMENT ON VIEW "issue_delegation" IS 'Resulting issue delegations from active members; can include rows with trustee_id set to NULL';
68 -- support for explicitly disabled delegations in "delegation_chain" functions
70 DROP FUNCTION "delegation_chain"
71 ( "member"."id"%TYPE,
72 "area"."id"%TYPE,
73 "issue"."id"%TYPE );
75 DROP FUNCTION "delegation_chain"
76 ( "member"."id"%TYPE,
77 "area"."id"%TYPE,
78 "issue"."id"%TYPE,
79 "member"."id"%TYPE );
81 DROP TYPE "delegation_chain_row";
83 CREATE TYPE "delegation_chain_row" AS (
84 "index" INT4,
85 "member_id" INT4,
86 "member_active" BOOLEAN,
87 "participation" BOOLEAN,
88 "overridden" BOOLEAN,
89 "scope_in" "delegation_scope",
90 "scope_out" "delegation_scope",
91 "disabled_out" BOOLEAN,
92 "loop" "delegation_chain_loop_tag" );
94 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
96 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
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';
98 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
99 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
100 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
101 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
102 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
105 CREATE FUNCTION "delegation_chain"
106 ( "member_id_p" "member"."id"%TYPE,
107 "area_id_p" "area"."id"%TYPE,
108 "issue_id_p" "issue"."id"%TYPE,
109 "simulate_trustee_id_p" "member"."id"%TYPE )
110 RETURNS SETOF "delegation_chain_row"
111 LANGUAGE 'plpgsql' STABLE AS $$
112 DECLARE
113 "issue_row" "issue"%ROWTYPE;
114 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
115 "loop_member_id_v" "member"."id"%TYPE;
116 "output_row" "delegation_chain_row";
117 "output_rows" "delegation_chain_row"[];
118 "delegation_row" "delegation"%ROWTYPE;
119 "row_count" INT4;
120 "i" INT4;
121 "loop_v" BOOLEAN;
122 BEGIN
123 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
124 "visited_member_ids" := '{}';
125 "loop_member_id_v" := NULL;
126 "output_rows" := '{}';
127 "output_row"."index" := 0;
128 "output_row"."member_id" := "member_id_p";
129 "output_row"."member_active" := TRUE;
130 "output_row"."participation" := FALSE;
131 "output_row"."overridden" := FALSE;
132 "output_row"."disabled_out" := FALSE;
133 "output_row"."scope_out" := NULL;
134 LOOP
135 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
136 "loop_member_id_v" := "output_row"."member_id";
137 ELSE
138 "visited_member_ids" :=
139 "visited_member_ids" || "output_row"."member_id";
140 END IF;
141 IF "output_row"."participation" THEN
142 "output_row"."overridden" := TRUE;
143 END IF;
144 "output_row"."scope_in" := "output_row"."scope_out";
145 IF EXISTS (
146 SELECT NULL FROM "member"
147 WHERE "id" = "output_row"."member_id" AND "active"
148 ) THEN
149 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
150 SELECT * INTO "delegation_row" FROM "delegation"
151 WHERE "truster_id" = "output_row"."member_id"
152 AND "scope" = 'global';
153 ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN
154 "output_row"."participation" := EXISTS (
155 SELECT NULL FROM "membership"
156 WHERE "area_id" = "area_id_p"
157 AND "member_id" = "output_row"."member_id"
158 );
159 SELECT * INTO "delegation_row" FROM "delegation"
160 WHERE "truster_id" = "output_row"."member_id"
161 AND ("scope" = 'global' OR "area_id" = "area_id_p")
162 ORDER BY "scope" DESC;
163 ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN
164 "output_row"."participation" := EXISTS (
165 SELECT NULL FROM "interest"
166 WHERE "issue_id" = "issue_id_p"
167 AND "member_id" = "output_row"."member_id"
168 );
169 SELECT * INTO "delegation_row" FROM "delegation"
170 WHERE "truster_id" = "output_row"."member_id"
171 AND ("scope" = 'global' OR
172 "area_id" = "issue_row"."area_id" OR
173 "issue_id" = "issue_id_p"
174 )
175 ORDER BY "scope" DESC;
176 ELSE
177 RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';
178 END IF;
179 ELSE
180 "output_row"."member_active" := FALSE;
181 "output_row"."participation" := FALSE;
182 "output_row"."scope_out" := NULL;
183 "delegation_row" := ROW(NULL);
184 END IF;
185 IF
186 "output_row"."member_id" = "member_id_p" AND
187 "simulate_trustee_id_p" NOTNULL
188 THEN
189 "output_row"."scope_out" := CASE
190 WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global'
191 WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area'
192 WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue'
193 END;
194 "output_rows" := "output_rows" || "output_row";
195 "output_row"."member_id" := "simulate_trustee_id_p";
196 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
197 "output_row"."scope_out" := "delegation_row"."scope";
198 "output_rows" := "output_rows" || "output_row";
199 "output_row"."member_id" := "delegation_row"."trustee_id";
200 ELSIF "delegation_row"."scope" NOTNULL THEN
201 "output_row"."scope_out" := "delegation_row"."scope";
202 "output_row"."disabled_out" := TRUE;
203 "output_rows" := "output_rows" || "output_row";
204 EXIT;
205 ELSE
206 "output_row"."scope_out" := NULL;
207 "output_rows" := "output_rows" || "output_row";
208 EXIT;
209 END IF;
210 EXIT WHEN "loop_member_id_v" NOTNULL;
211 "output_row"."index" := "output_row"."index" + 1;
212 END LOOP;
213 "row_count" := array_upper("output_rows", 1);
214 "i" := 1;
215 "loop_v" := FALSE;
216 LOOP
217 "output_row" := "output_rows"["i"];
218 EXIT WHEN "output_row" ISNULL;
219 IF "loop_v" THEN
220 IF "i" + 1 = "row_count" THEN
221 "output_row"."loop" := 'last';
222 ELSIF "i" = "row_count" THEN
223 "output_row"."loop" := 'repetition';
224 ELSE
225 "output_row"."loop" := 'intermediate';
226 END IF;
227 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
228 "output_row"."loop" := 'first';
229 "loop_v" := TRUE;
230 END IF;
231 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
232 "output_row"."participation" := NULL;
233 END IF;
234 RETURN NEXT "output_row";
235 "i" := "i" + 1;
236 END LOOP;
237 RETURN;
238 END;
239 $$;
241 COMMENT ON FUNCTION "delegation_chain"
242 ( "member"."id"%TYPE,
243 "area"."id"%TYPE,
244 "issue"."id"%TYPE,
245 "member"."id"%TYPE )
246 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
248 CREATE FUNCTION "delegation_chain"
249 ( "member_id_p" "member"."id"%TYPE,
250 "area_id_p" "area"."id"%TYPE,
251 "issue_id_p" "issue"."id"%TYPE )
252 RETURNS SETOF "delegation_chain_row"
253 LANGUAGE 'plpgsql' STABLE AS $$
254 DECLARE
255 "result_row" "delegation_chain_row";
256 BEGIN
257 FOR "result_row" IN
258 SELECT * FROM "delegation_chain"(
259 "member_id_p", "area_id_p", "issue_id_p", NULL
260 )
261 LOOP
262 RETURN NEXT "result_row";
263 END LOOP;
264 RETURN;
265 END;
266 $$;
268 COMMENT ON FUNCTION "delegation_chain"
269 ( "member"."id"%TYPE,
270 "area"."id"%TYPE,
271 "issue"."id"%TYPE )
272 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
275 -- delete entries of "ignored_issue" table in "delete_member"(...) and "delete_private_data"() functions
277 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
278 RETURNS VOID
279 LANGUAGE 'plpgsql' VOLATILE AS $$
280 BEGIN
281 UPDATE "member" SET
282 "last_login" = NULL,
283 "login" = NULL,
284 "password" = NULL,
285 "active" = FALSE,
286 "notify_email" = NULL,
287 "notify_email_unconfirmed" = NULL,
288 "notify_email_secret" = NULL,
289 "notify_email_secret_expiry" = NULL,
290 "notify_email_lock_expiry" = NULL,
291 "password_reset_secret" = NULL,
292 "password_reset_secret_expiry" = NULL,
293 "organizational_unit" = NULL,
294 "internal_posts" = NULL,
295 "realname" = NULL,
296 "birthday" = NULL,
297 "address" = NULL,
298 "email" = NULL,
299 "xmpp_address" = NULL,
300 "website" = NULL,
301 "phone" = NULL,
302 "mobile_phone" = NULL,
303 "profession" = NULL,
304 "external_memberships" = NULL,
305 "external_posts" = NULL,
306 "statement" = NULL
307 WHERE "id" = "member_id_p";
308 -- "text_search_data" is updated by triggers
309 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
310 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
311 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
312 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
313 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
314 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
315 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
316 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
317 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
318 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
319 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
320 DELETE FROM "ignored_voting" WHERE "member_id" = "member_id_p";
321 DELETE FROM "direct_voter" USING "issue"
322 WHERE "direct_voter"."issue_id" = "issue"."id"
323 AND "issue"."closed" ISNULL
324 AND "member_id" = "member_id_p";
325 RETURN;
326 END;
327 $$;
329 CREATE OR REPLACE FUNCTION "delete_private_data"()
330 RETURNS VOID
331 LANGUAGE 'plpgsql' VOLATILE AS $$
332 BEGIN
333 UPDATE "member" SET
334 "last_login" = NULL,
335 "login" = NULL,
336 "password" = NULL,
337 "notify_email" = NULL,
338 "notify_email_unconfirmed" = NULL,
339 "notify_email_secret" = NULL,
340 "notify_email_secret_expiry" = NULL,
341 "notify_email_lock_expiry" = NULL,
342 "password_reset_secret" = NULL,
343 "password_reset_secret_expiry" = NULL,
344 "organizational_unit" = NULL,
345 "internal_posts" = NULL,
346 "realname" = NULL,
347 "birthday" = NULL,
348 "address" = NULL,
349 "email" = NULL,
350 "xmpp_address" = NULL,
351 "website" = NULL,
352 "phone" = NULL,
353 "mobile_phone" = NULL,
354 "profession" = NULL,
355 "external_memberships" = NULL,
356 "external_posts" = NULL,
357 "statement" = NULL;
358 -- "text_search_data" is updated by triggers
359 DELETE FROM "invite_code";
360 DELETE FROM "setting";
361 DELETE FROM "setting_map";
362 DELETE FROM "member_relation_setting";
363 DELETE FROM "member_image";
364 DELETE FROM "contact";
365 DELETE FROM "session";
366 DELETE FROM "area_setting";
367 DELETE FROM "issue_setting";
368 DELETE FROM "initiative_setting";
369 DELETE FROM "suggestion_setting";
370 DELETE FROM "ignored_voting";
371 DELETE FROM "direct_voter" USING "issue"
372 WHERE "direct_voter"."issue_id" = "issue"."id"
373 AND "issue"."closed" ISNULL;
374 RETURN;
375 END;
376 $$;
379 COMMIT;