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