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