liquid_feedback_core
view update/core-update.v2.0.10-v2.0.11.sql @ 258:8809f1202ee0
Updated LICENSE file
| author | jbe |
|---|---|
| date | Wed Jun 20 23:08:12 2012 +0200 (2012-06-20) |
| parents | e818f83e133b |
| children |
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('2.0.11', 2, 0, 11))
5 AS "subquery"("string", "major", "minor", "revision");
7 DROP FUNCTION "delegation_info"
8 ( "member"."id"%TYPE,
9 "unit"."id"%TYPE,
10 "area"."id"%TYPE,
11 "issue"."id"%TYPE,
12 "member"."id"%TYPE );
14 DROP FUNCTION "delegation_chain"
15 ( "member"."id"%TYPE,
16 "unit"."id"%TYPE,
17 "area"."id"%TYPE,
18 "issue"."id"%TYPE,
19 "member"."id"%TYPE );
21 CREATE FUNCTION "delegation_chain"
22 ( "member_id_p" "member"."id"%TYPE,
23 "unit_id_p" "unit"."id"%TYPE,
24 "area_id_p" "area"."id"%TYPE,
25 "issue_id_p" "issue"."id"%TYPE,
26 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
27 "simulate_default_p" BOOLEAN DEFAULT FALSE )
28 RETURNS SETOF "delegation_chain_row"
29 LANGUAGE 'plpgsql' STABLE AS $$
30 DECLARE
31 "scope_v" "delegation_scope";
32 "unit_id_v" "unit"."id"%TYPE;
33 "area_id_v" "area"."id"%TYPE;
34 "issue_row" "issue"%ROWTYPE;
35 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
36 "loop_member_id_v" "member"."id"%TYPE;
37 "output_row" "delegation_chain_row";
38 "output_rows" "delegation_chain_row"[];
39 "simulate_v" BOOLEAN;
40 "simulate_here_v" BOOLEAN;
41 "delegation_row" "delegation"%ROWTYPE;
42 "row_count" INT4;
43 "i" INT4;
44 "loop_v" BOOLEAN;
45 BEGIN
46 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
47 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
48 END IF;
49 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
50 "simulate_v" := TRUE;
51 ELSE
52 "simulate_v" := FALSE;
53 END IF;
54 IF
55 "unit_id_p" NOTNULL AND
56 "area_id_p" ISNULL AND
57 "issue_id_p" ISNULL
58 THEN
59 "scope_v" := 'unit';
60 "unit_id_v" := "unit_id_p";
61 ELSIF
62 "unit_id_p" ISNULL AND
63 "area_id_p" NOTNULL AND
64 "issue_id_p" ISNULL
65 THEN
66 "scope_v" := 'area';
67 "area_id_v" := "area_id_p";
68 SELECT "unit_id" INTO "unit_id_v"
69 FROM "area" WHERE "id" = "area_id_v";
70 ELSIF
71 "unit_id_p" ISNULL AND
72 "area_id_p" ISNULL AND
73 "issue_id_p" NOTNULL
74 THEN
75 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
76 IF "issue_row"."id" ISNULL THEN
77 RETURN;
78 END IF;
79 IF "issue_row"."closed" NOTNULL THEN
80 IF "simulate_v" THEN
81 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
82 END IF;
83 FOR "output_row" IN
84 SELECT * FROM
85 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
86 LOOP
87 RETURN NEXT "output_row";
88 END LOOP;
89 RETURN;
90 END IF;
91 "scope_v" := 'issue';
92 SELECT "area_id" INTO "area_id_v"
93 FROM "issue" WHERE "id" = "issue_id_p";
94 SELECT "unit_id" INTO "unit_id_v"
95 FROM "area" WHERE "id" = "area_id_v";
96 ELSE
97 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
98 END IF;
99 "visited_member_ids" := '{}';
100 "loop_member_id_v" := NULL;
101 "output_rows" := '{}';
102 "output_row"."index" := 0;
103 "output_row"."member_id" := "member_id_p";
104 "output_row"."member_valid" := TRUE;
105 "output_row"."participation" := FALSE;
106 "output_row"."overridden" := FALSE;
107 "output_row"."disabled_out" := FALSE;
108 "output_row"."scope_out" := NULL;
109 LOOP
110 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
111 "loop_member_id_v" := "output_row"."member_id";
112 ELSE
113 "visited_member_ids" :=
114 "visited_member_ids" || "output_row"."member_id";
115 END IF;
116 IF "output_row"."participation" ISNULL THEN
117 "output_row"."overridden" := NULL;
118 ELSIF "output_row"."participation" THEN
119 "output_row"."overridden" := TRUE;
120 END IF;
121 "output_row"."scope_in" := "output_row"."scope_out";
122 "output_row"."member_valid" := EXISTS (
123 SELECT NULL FROM "member" JOIN "privilege"
124 ON "privilege"."member_id" = "member"."id"
125 AND "privilege"."unit_id" = "unit_id_v"
126 WHERE "id" = "output_row"."member_id"
127 AND "member"."active" AND "privilege"."voting_right"
128 );
129 "simulate_here_v" := (
130 "simulate_v" AND
131 "output_row"."member_id" = "member_id_p"
132 );
133 "delegation_row" := ROW(NULL);
134 IF "output_row"."member_valid" OR "simulate_here_v" THEN
135 IF "scope_v" = 'unit' THEN
136 IF NOT "simulate_here_v" THEN
137 SELECT * INTO "delegation_row" FROM "delegation"
138 WHERE "truster_id" = "output_row"."member_id"
139 AND "unit_id" = "unit_id_v";
140 END IF;
141 ELSIF "scope_v" = 'area' THEN
142 "output_row"."participation" := EXISTS (
143 SELECT NULL FROM "membership"
144 WHERE "area_id" = "area_id_p"
145 AND "member_id" = "output_row"."member_id"
146 );
147 IF "simulate_here_v" THEN
148 IF "simulate_trustee_id_p" ISNULL THEN
149 SELECT * INTO "delegation_row" FROM "delegation"
150 WHERE "truster_id" = "output_row"."member_id"
151 AND "unit_id" = "unit_id_v";
152 END IF;
153 ELSE
154 SELECT * INTO "delegation_row" FROM "delegation"
155 WHERE "truster_id" = "output_row"."member_id"
156 AND (
157 "unit_id" = "unit_id_v" OR
158 "area_id" = "area_id_v"
159 )
160 ORDER BY "scope" DESC;
161 END IF;
162 ELSIF "scope_v" = 'issue' THEN
163 IF "issue_row"."fully_frozen" ISNULL 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 ELSE
170 IF "output_row"."member_id" = "member_id_p" THEN
171 "output_row"."participation" := EXISTS (
172 SELECT NULL FROM "direct_voter"
173 WHERE "issue_id" = "issue_id_p"
174 AND "member_id" = "output_row"."member_id"
175 );
176 ELSE
177 "output_row"."participation" := NULL;
178 END IF;
179 END IF;
180 IF "simulate_here_v" THEN
181 IF "simulate_trustee_id_p" ISNULL THEN
182 SELECT * INTO "delegation_row" FROM "delegation"
183 WHERE "truster_id" = "output_row"."member_id"
184 AND (
185 "unit_id" = "unit_id_v" OR
186 "area_id" = "area_id_v"
187 )
188 ORDER BY "scope" DESC;
189 END IF;
190 ELSE
191 SELECT * INTO "delegation_row" FROM "delegation"
192 WHERE "truster_id" = "output_row"."member_id"
193 AND (
194 "unit_id" = "unit_id_v" OR
195 "area_id" = "area_id_v" OR
196 "issue_id" = "issue_id_p"
197 )
198 ORDER BY "scope" DESC;
199 END IF;
200 END IF;
201 ELSE
202 "output_row"."participation" := FALSE;
203 END IF;
204 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
205 "output_row"."scope_out" := "scope_v";
206 "output_rows" := "output_rows" || "output_row";
207 "output_row"."member_id" := "simulate_trustee_id_p";
208 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
209 "output_row"."scope_out" := "delegation_row"."scope";
210 "output_rows" := "output_rows" || "output_row";
211 "output_row"."member_id" := "delegation_row"."trustee_id";
212 ELSIF "delegation_row"."scope" NOTNULL THEN
213 "output_row"."scope_out" := "delegation_row"."scope";
214 "output_row"."disabled_out" := TRUE;
215 "output_rows" := "output_rows" || "output_row";
216 EXIT;
217 ELSE
218 "output_row"."scope_out" := NULL;
219 "output_rows" := "output_rows" || "output_row";
220 EXIT;
221 END IF;
222 EXIT WHEN "loop_member_id_v" NOTNULL;
223 "output_row"."index" := "output_row"."index" + 1;
224 END LOOP;
225 "row_count" := array_upper("output_rows", 1);
226 "i" := 1;
227 "loop_v" := FALSE;
228 LOOP
229 "output_row" := "output_rows"["i"];
230 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
231 IF "loop_v" THEN
232 IF "i" + 1 = "row_count" THEN
233 "output_row"."loop" := 'last';
234 ELSIF "i" = "row_count" THEN
235 "output_row"."loop" := 'repetition';
236 ELSE
237 "output_row"."loop" := 'intermediate';
238 END IF;
239 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
240 "output_row"."loop" := 'first';
241 "loop_v" := TRUE;
242 END IF;
243 IF "scope_v" = 'unit' THEN
244 "output_row"."participation" := NULL;
245 END IF;
246 RETURN NEXT "output_row";
247 "i" := "i" + 1;
248 END LOOP;
249 RETURN;
250 END;
251 $$;
253 COMMENT ON FUNCTION "delegation_chain"
254 ( "member"."id"%TYPE,
255 "unit"."id"%TYPE,
256 "area"."id"%TYPE,
257 "issue"."id"%TYPE,
258 "member"."id"%TYPE,
259 BOOLEAN )
260 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
262 CREATE FUNCTION "delegation_info"
263 ( "member_id_p" "member"."id"%TYPE,
264 "unit_id_p" "unit"."id"%TYPE,
265 "area_id_p" "area"."id"%TYPE,
266 "issue_id_p" "issue"."id"%TYPE,
267 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
268 "simulate_default_p" BOOLEAN DEFAULT FALSE )
269 RETURNS "delegation_info_type"
270 LANGUAGE 'plpgsql' STABLE AS $$
271 DECLARE
272 "current_row" "delegation_chain_row";
273 "result" "delegation_info_type";
274 BEGIN
275 "result"."own_participation" := FALSE;
276 FOR "current_row" IN
277 SELECT * FROM "delegation_chain"(
278 "member_id_p",
279 "unit_id_p", "area_id_p", "issue_id_p",
280 "simulate_trustee_id_p", "simulate_default_p")
281 LOOP
282 IF
283 "result"."participating_member_id" ISNULL AND
284 "current_row"."participation"
285 THEN
286 "result"."participating_member_id" := "current_row"."member_id";
287 END IF;
288 IF "current_row"."member_id" = "member_id_p" THEN
289 "result"."own_participation" := "current_row"."participation";
290 "result"."own_delegation_scope" := "current_row"."scope_out";
291 IF "current_row"."loop" = 'first' THEN
292 "result"."delegation_loop" := 'own';
293 END IF;
294 ELSIF
295 "current_row"."member_valid" AND
296 ( "current_row"."loop" ISNULL OR
297 "current_row"."loop" != 'repetition' )
298 THEN
299 IF "result"."first_trustee_id" ISNULL THEN
300 "result"."first_trustee_id" := "current_row"."member_id";
301 "result"."first_trustee_participation" := "current_row"."participation";
302 "result"."first_trustee_ellipsis" := FALSE;
303 IF "current_row"."loop" = 'first' THEN
304 "result"."delegation_loop" := 'first';
305 END IF;
306 ELSIF "result"."other_trustee_id" ISNULL THEN
307 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
308 "result"."other_trustee_id" := "current_row"."member_id";
309 "result"."other_trustee_participation" := TRUE;
310 "result"."other_trustee_ellipsis" := FALSE;
311 IF "current_row"."loop" = 'first' THEN
312 "result"."delegation_loop" := 'other';
313 END IF;
314 ELSE
315 "result"."first_trustee_ellipsis" := TRUE;
316 IF "current_row"."loop" = 'first' THEN
317 "result"."delegation_loop" := 'first_ellipsis';
318 END IF;
319 END IF;
320 ELSE
321 "result"."other_trustee_ellipsis" := TRUE;
322 IF "current_row"."loop" = 'first' THEN
323 "result"."delegation_loop" := 'other_ellipsis';
324 END IF;
325 END IF;
326 END IF;
327 END LOOP;
328 RETURN "result";
329 END;
330 $$;
332 COMMENT ON FUNCTION "delegation_info"
333 ( "member"."id"%TYPE,
334 "unit"."id"%TYPE,
335 "area"."id"%TYPE,
336 "issue"."id"%TYPE,
337 "member"."id"%TYPE,
338 BOOLEAN )
339 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
341 COMMIT;