liquid_feedback_core
view update/core-update.v2.0.8-v2.0.9.sql @ 248:05eb08f56f09
Bugfix in view "unit_member_count"
author | jbe |
---|---|
date | Sat May 12 18:33:56 2012 +0200 (2012-05-12) |
parents | 4b8cc6fc2d00 |
children | d4c754c4aeaa |
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('2.0.9', 2, 0, 9))
5 AS "subquery"("string", "major", "minor", "revision");
7 CREATE OR REPLACE VIEW "unit_member_count" AS
8 SELECT
9 "unit"."id" AS "unit_id",
10 count("member"."id") AS "member_count"
11 FROM "unit"
12 LEFT JOIN "privilege"
13 ON "privilege"."unit_id" = "unit"."id"
14 AND "privilege"."voting_right"
15 LEFT JOIN "member"
16 ON "member"."id" = "privilege"."member_id"
17 AND "member"."active"
18 GROUP BY "unit"."id";
20 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
22 CREATE FUNCTION "delegation_chain_for_closed_issue"
23 ( "member_id_p" "member"."id"%TYPE,
24 "issue_id_p" "issue"."id"%TYPE )
25 RETURNS SETOF "delegation_chain_row"
26 LANGUAGE 'plpgsql' STABLE AS $$
27 DECLARE
28 "output_row" "delegation_chain_row";
29 "direct_voter_row" "direct_voter"%ROWTYPE;
30 "delegating_voter_row" "delegating_voter"%ROWTYPE;
31 BEGIN
32 "output_row"."index" := 0;
33 "output_row"."member_id" := "member_id_p";
34 "output_row"."member_valid" := TRUE;
35 "output_row"."participation" := FALSE;
36 "output_row"."overridden" := FALSE;
37 "output_row"."disabled_out" := FALSE;
38 LOOP
39 SELECT INTO "direct_voter_row" * FROM "direct_voter"
40 WHERE "issue_id" = "issue_id_p"
41 AND "member_id" = "output_row"."member_id";
42 IF "direct_voter_row"."member_id" NOTNULL THEN
43 "output_row"."participation" := TRUE;
44 "output_row"."scope_out" := NULL;
45 "output_row"."disabled_out" := NULL;
46 RETURN NEXT "output_row";
47 RETURN;
48 END IF;
49 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
50 WHERE "issue_id" = "issue_id_p"
51 AND "member_id" = "output_row"."member_id";
52 IF "delegating_voter_row"."member_id" ISNULL THEN
53 RETURN;
54 END IF;
55 "output_row"."scope_out" := "delegating_voter_row"."scope";
56 RETURN NEXT "output_row";
57 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
58 "output_row"."scope_in" := "output_row"."scope_out";
59 END LOOP;
60 END;
61 $$;
63 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
64 ( "member"."id"%TYPE,
65 "member"."id"%TYPE )
66 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
68 DROP FUNCTION "delegation_chain"
69 ( "member"."id"%TYPE,
70 "unit"."id"%TYPE,
71 "area"."id"%TYPE,
72 "issue"."id"%TYPE );
74 DROP FUNCTION "delegation_chain"
75 ( "member"."id"%TYPE,
76 "unit"."id"%TYPE,
77 "area"."id"%TYPE,
78 "issue"."id"%TYPE,
79 "member"."id"%TYPE );
81 CREATE FUNCTION "delegation_chain"
82 ( "member_id_p" "member"."id"%TYPE,
83 "unit_id_p" "unit"."id"%TYPE,
84 "area_id_p" "area"."id"%TYPE,
85 "issue_id_p" "issue"."id"%TYPE,
86 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL )
87 RETURNS SETOF "delegation_chain_row"
88 LANGUAGE 'plpgsql' STABLE AS $$
89 DECLARE
90 "scope_v" "delegation_scope";
91 "unit_id_v" "unit"."id"%TYPE;
92 "area_id_v" "area"."id"%TYPE;
93 "issue_row" "issue"%ROWTYPE;
94 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
95 "loop_member_id_v" "member"."id"%TYPE;
96 "output_row" "delegation_chain_row";
97 "output_rows" "delegation_chain_row"[];
98 "delegation_row" "delegation"%ROWTYPE;
99 "row_count" INT4;
100 "i" INT4;
101 "loop_v" BOOLEAN;
102 BEGIN
103 IF
104 "unit_id_p" NOTNULL AND
105 "area_id_p" ISNULL AND
106 "issue_id_p" ISNULL
107 THEN
108 "scope_v" := 'unit';
109 "unit_id_v" := "unit_id_p";
110 ELSIF
111 "unit_id_p" ISNULL AND
112 "area_id_p" NOTNULL AND
113 "issue_id_p" ISNULL
114 THEN
115 "scope_v" := 'area';
116 "area_id_v" := "area_id_p";
117 SELECT "unit_id" INTO "unit_id_v"
118 FROM "area" WHERE "id" = "area_id_v";
119 ELSIF
120 "unit_id_p" ISNULL AND
121 "area_id_p" ISNULL AND
122 "issue_id_p" NOTNULL
123 THEN
124 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
125 IF "issue_row"."id" ISNULL THEN
126 RETURN;
127 END IF;
128 IF "issue_row"."closed" NOTNULL THEN
129 IF "simulate_trustee_id_p" NOTNULL THEN
130 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
131 END IF;
132 FOR "output_row" IN
133 SELECT * FROM
134 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
135 LOOP
136 RETURN NEXT "output_row";
137 END LOOP;
138 RETURN;
139 END IF;
140 "scope_v" := 'issue';
141 SELECT "area_id" INTO "area_id_v"
142 FROM "issue" WHERE "id" = "issue_id_p";
143 SELECT "unit_id" INTO "unit_id_v"
144 FROM "area" WHERE "id" = "area_id_v";
145 ELSE
146 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
147 END IF;
148 "visited_member_ids" := '{}';
149 "loop_member_id_v" := NULL;
150 "output_rows" := '{}';
151 "output_row"."index" := 0;
152 "output_row"."member_id" := "member_id_p";
153 "output_row"."member_valid" := TRUE;
154 "output_row"."participation" := FALSE;
155 "output_row"."overridden" := FALSE;
156 "output_row"."disabled_out" := FALSE;
157 "output_row"."scope_out" := NULL;
158 LOOP
159 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
160 "loop_member_id_v" := "output_row"."member_id";
161 ELSE
162 "visited_member_ids" :=
163 "visited_member_ids" || "output_row"."member_id";
164 END IF;
165 IF "output_row"."participation" ISNULL THEN
166 "output_row"."overridden" := NULL;
167 ELSIF "output_row"."participation" THEN
168 "output_row"."overridden" := TRUE;
169 END IF;
170 "output_row"."scope_in" := "output_row"."scope_out";
171 IF EXISTS (
172 SELECT NULL FROM "member" JOIN "privilege"
173 ON "privilege"."member_id" = "member"."id"
174 AND "privilege"."unit_id" = "unit_id_v"
175 WHERE "id" = "output_row"."member_id"
176 AND "member"."active" AND "privilege"."voting_right"
177 ) THEN
178 IF "scope_v" = 'unit' THEN
179 SELECT * INTO "delegation_row" FROM "delegation"
180 WHERE "truster_id" = "output_row"."member_id"
181 AND "unit_id" = "unit_id_v";
182 ELSIF "scope_v" = 'area' THEN
183 "output_row"."participation" := EXISTS (
184 SELECT NULL FROM "membership"
185 WHERE "area_id" = "area_id_p"
186 AND "member_id" = "output_row"."member_id"
187 );
188 SELECT * INTO "delegation_row" FROM "delegation"
189 WHERE "truster_id" = "output_row"."member_id"
190 AND (
191 "unit_id" = "unit_id_v" OR
192 "area_id" = "area_id_v"
193 )
194 ORDER BY "scope" DESC;
195 ELSIF "scope_v" = 'issue' THEN
196 IF "issue_row"."fully_frozen" ISNULL THEN
197 "output_row"."participation" := EXISTS (
198 SELECT NULL FROM "interest"
199 WHERE "issue_id" = "issue_id_p"
200 AND "member_id" = "output_row"."member_id"
201 );
202 ELSE
203 IF "output_row"."member_id" = "member_id_p" THEN
204 "output_row"."participation" := EXISTS (
205 SELECT NULL FROM "direct_voter"
206 WHERE "issue_id" = "issue_id_p"
207 AND "member_id" = "output_row"."member_id"
208 );
209 ELSE
210 "output_row"."participation" := NULL;
211 END IF;
212 END IF;
213 SELECT * INTO "delegation_row" FROM "delegation"
214 WHERE "truster_id" = "output_row"."member_id"
215 AND (
216 "unit_id" = "unit_id_v" OR
217 "area_id" = "area_id_v" OR
218 "issue_id" = "issue_id_p"
219 )
220 ORDER BY "scope" DESC;
221 END IF;
222 ELSE
223 "output_row"."member_valid" := FALSE;
224 "output_row"."participation" := FALSE;
225 "output_row"."scope_out" := NULL;
226 "delegation_row" := ROW(NULL);
227 END IF;
228 IF
229 "output_row"."member_id" = "member_id_p" AND
230 "simulate_trustee_id_p" NOTNULL
231 THEN
232 "output_row"."scope_out" := "scope_v";
233 "output_rows" := "output_rows" || "output_row";
234 "output_row"."member_id" := "simulate_trustee_id_p";
235 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
236 "output_row"."scope_out" := "delegation_row"."scope";
237 "output_rows" := "output_rows" || "output_row";
238 "output_row"."member_id" := "delegation_row"."trustee_id";
239 ELSIF "delegation_row"."scope" NOTNULL THEN
240 "output_row"."scope_out" := "delegation_row"."scope";
241 "output_row"."disabled_out" := TRUE;
242 "output_rows" := "output_rows" || "output_row";
243 EXIT;
244 ELSE
245 "output_row"."scope_out" := NULL;
246 "output_rows" := "output_rows" || "output_row";
247 EXIT;
248 END IF;
249 EXIT WHEN "loop_member_id_v" NOTNULL;
250 "output_row"."index" := "output_row"."index" + 1;
251 END LOOP;
252 "row_count" := array_upper("output_rows", 1);
253 "i" := 1;
254 "loop_v" := FALSE;
255 LOOP
256 "output_row" := "output_rows"["i"];
257 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
258 IF "loop_v" THEN
259 IF "i" + 1 = "row_count" THEN
260 "output_row"."loop" := 'last';
261 ELSIF "i" = "row_count" THEN
262 "output_row"."loop" := 'repetition';
263 ELSE
264 "output_row"."loop" := 'intermediate';
265 END IF;
266 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
267 "output_row"."loop" := 'first';
268 "loop_v" := TRUE;
269 END IF;
270 IF "scope_v" = 'unit' THEN
271 "output_row"."participation" := NULL;
272 END IF;
273 RETURN NEXT "output_row";
274 "i" := "i" + 1;
275 END LOOP;
276 RETURN;
277 END;
278 $$;
280 COMMENT ON FUNCTION "delegation_chain"
281 ( "member"."id"%TYPE,
282 "unit"."id"%TYPE,
283 "area"."id"%TYPE,
284 "issue"."id"%TYPE,
285 "member"."id"%TYPE )
286 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
288 CREATE TYPE "delegation_info_loop_type" AS ENUM
289 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
291 COMMENT ON TYPE "delegation_info_loop_type" IS 'Type of "delegation_loop" in "delegation_info_type"; ''own'' means loop to self, ''first'' means loop to first trustee, ''first_ellipsis'' means loop to ellipsis after first trustee, ''other'' means loop to other trustee, ''other_ellipsis'' means loop to ellipsis after other trustee''';
293 CREATE TYPE "delegation_info_type" AS (
294 "own_participation" BOOLEAN,
295 "own_delegation_scope" "delegation_scope",
296 "first_trustee_id" INT4,
297 "first_trustee_participation" BOOLEAN,
298 "first_trustee_ellipsis" BOOLEAN,
299 "other_trustee_id" INT4,
300 "other_trustee_participation" BOOLEAN,
301 "other_trustee_ellipsis" BOOLEAN,
302 "delegation_loop" "delegation_info_loop_type");
304 COMMENT ON TYPE "delegation_info_type" IS 'Type of result returned by "delegation_info" function; For meaning of "participation" check comment on "delegation_chain_row" type';
306 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
307 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
308 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
309 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
310 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
311 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
312 COMMENT ON COLUMN "delegation_info_type"."other_trustee_participation" IS 'Another trustee is participating (redundant field: if "other_trustee_id" is set, then "other_trustee_participation" is always TRUE, else "other_trustee_participation" is NULL)';
313 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
314 COMMENT ON COLUMN "delegation_info_type"."delegation_loop" IS 'Non-NULL value, if delegation chain contains a circle; See comment on "delegation_info_loop_type" for details';
316 CREATE FUNCTION "delegation_info"
317 ( "member_id_p" "member"."id"%TYPE,
318 "unit_id_p" "unit"."id"%TYPE,
319 "area_id_p" "area"."id"%TYPE,
320 "issue_id_p" "issue"."id"%TYPE,
321 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL )
322 RETURNS "delegation_info_type"
323 LANGUAGE 'plpgsql' STABLE AS $$
324 DECLARE
325 "current_row" "delegation_chain_row";
326 "result" "delegation_info_type";
327 BEGIN
328 "result"."own_participation" := FALSE;
329 FOR "current_row" IN
330 SELECT * FROM "delegation_chain"(
331 "member_id_p",
332 "unit_id_p", "area_id_p", "issue_id_p",
333 "simulate_trustee_id_p")
334 LOOP
335 IF "current_row"."member_id" = "member_id_p" THEN
336 "result"."own_participation" := "current_row"."participation";
337 "result"."own_delegation_scope" := "current_row"."scope_out";
338 IF "current_row"."loop" = 'first' THEN
339 "result"."delegation_loop" := 'own';
340 END IF;
341 ELSIF
342 "current_row"."member_valid" AND
343 ( "current_row"."loop" ISNULL OR
344 "current_row"."loop" != 'repetition' )
345 THEN
346 IF "result"."first_trustee_id" ISNULL THEN
347 "result"."first_trustee_id" := "current_row"."member_id";
348 "result"."first_trustee_participation" := "current_row"."participation";
349 "result"."first_trustee_ellipsis" := FALSE;
350 IF "current_row"."loop" = 'first' THEN
351 "result"."delegation_loop" := 'first';
352 END IF;
353 ELSIF "result"."other_trustee_id" ISNULL THEN
354 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
355 "result"."other_trustee_id" := "current_row"."member_id";
356 "result"."other_trustee_participation" := TRUE;
357 "result"."other_trustee_ellipsis" := FALSE;
358 IF "current_row"."loop" = 'first' THEN
359 "result"."delegation_loop" := 'other';
360 END IF;
361 ELSE
362 "result"."first_trustee_ellipsis" := TRUE;
363 IF "current_row"."loop" = 'first' THEN
364 "result"."delegation_loop" := 'first_ellipsis';
365 END IF;
366 END IF;
367 ELSE
368 "result"."other_trustee_ellipsis" := TRUE;
369 IF "current_row"."loop" = 'first' THEN
370 "result"."delegation_loop" := 'other_ellipsis';
371 END IF;
372 END IF;
373 END IF;
374 END LOOP;
375 RETURN "result";
376 END;
377 $$;
379 COMMENT ON FUNCTION "delegation_info"
380 ( "member"."id"%TYPE,
381 "unit"."id"%TYPE,
382 "area"."id"%TYPE,
383 "issue"."id"%TYPE,
384 "member"."id"%TYPE )
385 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
387 COMMIT;