liquid_feedback_core
view update/core-update.v2.0.10-v2.0.11.sql @ 378:e88d0606891f
Bugfix regarding "proportional_order" of suggestions:
Use NULL values explicitly to be sorted last
(includes new suggestions as well as suggestions without any individual rankings)
Use NULL values explicitly to be sorted last
(includes new suggestions as well as suggestions without any individual rankings)
author | jbe |
---|---|
date | Mon Mar 18 09:36:21 2013 +0100 (2013-03-18) |
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;