rev |
line source |
jbe@244
|
1 BEGIN;
|
jbe@244
|
2
|
jbe@244
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
jbe@244
|
4 SELECT * FROM (VALUES ('2.0.9', 2, 0, 9))
|
jbe@244
|
5 AS "subquery"("string", "major", "minor", "revision");
|
jbe@244
|
6
|
jbe@249
|
7 -- Bugfix of error in update script to v2.0.0:
|
jbe@249
|
8 ALTER TABLE "battle" ALTER COLUMN "winning_initiative_id" DROP NOT NULL;
|
jbe@249
|
9 ALTER TABLE "battle" ALTER COLUMN "losing_initiative_id" DROP NOT NULL;
|
jbe@249
|
10
|
jbe@248
|
11 CREATE OR REPLACE VIEW "unit_member_count" AS
|
jbe@248
|
12 SELECT
|
jbe@248
|
13 "unit"."id" AS "unit_id",
|
jbe@248
|
14 count("member"."id") AS "member_count"
|
jbe@248
|
15 FROM "unit"
|
jbe@248
|
16 LEFT JOIN "privilege"
|
jbe@248
|
17 ON "privilege"."unit_id" = "unit"."id"
|
jbe@248
|
18 AND "privilege"."voting_right"
|
jbe@248
|
19 LEFT JOIN "member"
|
jbe@248
|
20 ON "member"."id" = "privilege"."member_id"
|
jbe@248
|
21 AND "member"."active"
|
jbe@248
|
22 GROUP BY "unit"."id";
|
jbe@248
|
23
|
jbe@244
|
24 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
|
jbe@244
|
25
|
jbe@244
|
26 CREATE FUNCTION "delegation_chain_for_closed_issue"
|
jbe@244
|
27 ( "member_id_p" "member"."id"%TYPE,
|
jbe@244
|
28 "issue_id_p" "issue"."id"%TYPE )
|
jbe@244
|
29 RETURNS SETOF "delegation_chain_row"
|
jbe@244
|
30 LANGUAGE 'plpgsql' STABLE AS $$
|
jbe@244
|
31 DECLARE
|
jbe@244
|
32 "output_row" "delegation_chain_row";
|
jbe@244
|
33 "direct_voter_row" "direct_voter"%ROWTYPE;
|
jbe@244
|
34 "delegating_voter_row" "delegating_voter"%ROWTYPE;
|
jbe@244
|
35 BEGIN
|
jbe@244
|
36 "output_row"."index" := 0;
|
jbe@244
|
37 "output_row"."member_id" := "member_id_p";
|
jbe@244
|
38 "output_row"."member_valid" := TRUE;
|
jbe@244
|
39 "output_row"."participation" := FALSE;
|
jbe@244
|
40 "output_row"."overridden" := FALSE;
|
jbe@244
|
41 "output_row"."disabled_out" := FALSE;
|
jbe@244
|
42 LOOP
|
jbe@244
|
43 SELECT INTO "direct_voter_row" * FROM "direct_voter"
|
jbe@244
|
44 WHERE "issue_id" = "issue_id_p"
|
jbe@244
|
45 AND "member_id" = "output_row"."member_id";
|
jbe@244
|
46 IF "direct_voter_row"."member_id" NOTNULL THEN
|
jbe@244
|
47 "output_row"."participation" := TRUE;
|
jbe@244
|
48 "output_row"."scope_out" := NULL;
|
jbe@244
|
49 "output_row"."disabled_out" := NULL;
|
jbe@244
|
50 RETURN NEXT "output_row";
|
jbe@244
|
51 RETURN;
|
jbe@244
|
52 END IF;
|
jbe@244
|
53 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
|
jbe@244
|
54 WHERE "issue_id" = "issue_id_p"
|
jbe@244
|
55 AND "member_id" = "output_row"."member_id";
|
jbe@244
|
56 IF "delegating_voter_row"."member_id" ISNULL THEN
|
jbe@244
|
57 RETURN;
|
jbe@244
|
58 END IF;
|
jbe@244
|
59 "output_row"."scope_out" := "delegating_voter_row"."scope";
|
jbe@244
|
60 RETURN NEXT "output_row";
|
jbe@244
|
61 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
|
jbe@244
|
62 "output_row"."scope_in" := "output_row"."scope_out";
|
jbe@244
|
63 END LOOP;
|
jbe@244
|
64 END;
|
jbe@244
|
65 $$;
|
jbe@244
|
66
|
jbe@244
|
67 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
|
jbe@244
|
68 ( "member"."id"%TYPE,
|
jbe@244
|
69 "member"."id"%TYPE )
|
jbe@244
|
70 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
|
jbe@244
|
71
|
jbe@244
|
72 DROP FUNCTION "delegation_chain"
|
jbe@244
|
73 ( "member"."id"%TYPE,
|
jbe@244
|
74 "unit"."id"%TYPE,
|
jbe@244
|
75 "area"."id"%TYPE,
|
jbe@244
|
76 "issue"."id"%TYPE );
|
jbe@244
|
77
|
jbe@244
|
78 DROP FUNCTION "delegation_chain"
|
jbe@244
|
79 ( "member"."id"%TYPE,
|
jbe@244
|
80 "unit"."id"%TYPE,
|
jbe@244
|
81 "area"."id"%TYPE,
|
jbe@244
|
82 "issue"."id"%TYPE,
|
jbe@244
|
83 "member"."id"%TYPE );
|
jbe@244
|
84
|
jbe@244
|
85 CREATE FUNCTION "delegation_chain"
|
jbe@244
|
86 ( "member_id_p" "member"."id"%TYPE,
|
jbe@244
|
87 "unit_id_p" "unit"."id"%TYPE,
|
jbe@244
|
88 "area_id_p" "area"."id"%TYPE,
|
jbe@244
|
89 "issue_id_p" "issue"."id"%TYPE,
|
jbe@244
|
90 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL )
|
jbe@244
|
91 RETURNS SETOF "delegation_chain_row"
|
jbe@244
|
92 LANGUAGE 'plpgsql' STABLE AS $$
|
jbe@244
|
93 DECLARE
|
jbe@244
|
94 "scope_v" "delegation_scope";
|
jbe@244
|
95 "unit_id_v" "unit"."id"%TYPE;
|
jbe@244
|
96 "area_id_v" "area"."id"%TYPE;
|
jbe@244
|
97 "issue_row" "issue"%ROWTYPE;
|
jbe@244
|
98 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
|
jbe@244
|
99 "loop_member_id_v" "member"."id"%TYPE;
|
jbe@244
|
100 "output_row" "delegation_chain_row";
|
jbe@244
|
101 "output_rows" "delegation_chain_row"[];
|
jbe@244
|
102 "delegation_row" "delegation"%ROWTYPE;
|
jbe@244
|
103 "row_count" INT4;
|
jbe@244
|
104 "i" INT4;
|
jbe@244
|
105 "loop_v" BOOLEAN;
|
jbe@244
|
106 BEGIN
|
jbe@244
|
107 IF
|
jbe@244
|
108 "unit_id_p" NOTNULL AND
|
jbe@244
|
109 "area_id_p" ISNULL AND
|
jbe@244
|
110 "issue_id_p" ISNULL
|
jbe@244
|
111 THEN
|
jbe@244
|
112 "scope_v" := 'unit';
|
jbe@244
|
113 "unit_id_v" := "unit_id_p";
|
jbe@244
|
114 ELSIF
|
jbe@244
|
115 "unit_id_p" ISNULL AND
|
jbe@244
|
116 "area_id_p" NOTNULL AND
|
jbe@244
|
117 "issue_id_p" ISNULL
|
jbe@244
|
118 THEN
|
jbe@244
|
119 "scope_v" := 'area';
|
jbe@244
|
120 "area_id_v" := "area_id_p";
|
jbe@244
|
121 SELECT "unit_id" INTO "unit_id_v"
|
jbe@244
|
122 FROM "area" WHERE "id" = "area_id_v";
|
jbe@244
|
123 ELSIF
|
jbe@244
|
124 "unit_id_p" ISNULL AND
|
jbe@244
|
125 "area_id_p" ISNULL AND
|
jbe@244
|
126 "issue_id_p" NOTNULL
|
jbe@244
|
127 THEN
|
jbe@244
|
128 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@244
|
129 IF "issue_row"."id" ISNULL THEN
|
jbe@244
|
130 RETURN;
|
jbe@244
|
131 END IF;
|
jbe@244
|
132 IF "issue_row"."closed" NOTNULL THEN
|
jbe@244
|
133 IF "simulate_trustee_id_p" NOTNULL THEN
|
jbe@244
|
134 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
|
jbe@244
|
135 END IF;
|
jbe@244
|
136 FOR "output_row" IN
|
jbe@244
|
137 SELECT * FROM
|
jbe@244
|
138 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
|
jbe@244
|
139 LOOP
|
jbe@244
|
140 RETURN NEXT "output_row";
|
jbe@244
|
141 END LOOP;
|
jbe@244
|
142 RETURN;
|
jbe@244
|
143 END IF;
|
jbe@244
|
144 "scope_v" := 'issue';
|
jbe@244
|
145 SELECT "area_id" INTO "area_id_v"
|
jbe@244
|
146 FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@244
|
147 SELECT "unit_id" INTO "unit_id_v"
|
jbe@244
|
148 FROM "area" WHERE "id" = "area_id_v";
|
jbe@244
|
149 ELSE
|
jbe@244
|
150 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
|
jbe@244
|
151 END IF;
|
jbe@244
|
152 "visited_member_ids" := '{}';
|
jbe@244
|
153 "loop_member_id_v" := NULL;
|
jbe@244
|
154 "output_rows" := '{}';
|
jbe@244
|
155 "output_row"."index" := 0;
|
jbe@244
|
156 "output_row"."member_id" := "member_id_p";
|
jbe@244
|
157 "output_row"."member_valid" := TRUE;
|
jbe@244
|
158 "output_row"."participation" := FALSE;
|
jbe@244
|
159 "output_row"."overridden" := FALSE;
|
jbe@244
|
160 "output_row"."disabled_out" := FALSE;
|
jbe@244
|
161 "output_row"."scope_out" := NULL;
|
jbe@244
|
162 LOOP
|
jbe@244
|
163 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
|
jbe@244
|
164 "loop_member_id_v" := "output_row"."member_id";
|
jbe@244
|
165 ELSE
|
jbe@244
|
166 "visited_member_ids" :=
|
jbe@244
|
167 "visited_member_ids" || "output_row"."member_id";
|
jbe@244
|
168 END IF;
|
jbe@244
|
169 IF "output_row"."participation" ISNULL THEN
|
jbe@244
|
170 "output_row"."overridden" := NULL;
|
jbe@244
|
171 ELSIF "output_row"."participation" THEN
|
jbe@244
|
172 "output_row"."overridden" := TRUE;
|
jbe@244
|
173 END IF;
|
jbe@244
|
174 "output_row"."scope_in" := "output_row"."scope_out";
|
jbe@244
|
175 IF EXISTS (
|
jbe@244
|
176 SELECT NULL FROM "member" JOIN "privilege"
|
jbe@244
|
177 ON "privilege"."member_id" = "member"."id"
|
jbe@244
|
178 AND "privilege"."unit_id" = "unit_id_v"
|
jbe@244
|
179 WHERE "id" = "output_row"."member_id"
|
jbe@244
|
180 AND "member"."active" AND "privilege"."voting_right"
|
jbe@244
|
181 ) THEN
|
jbe@244
|
182 IF "scope_v" = 'unit' THEN
|
jbe@244
|
183 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@244
|
184 WHERE "truster_id" = "output_row"."member_id"
|
jbe@244
|
185 AND "unit_id" = "unit_id_v";
|
jbe@244
|
186 ELSIF "scope_v" = 'area' THEN
|
jbe@244
|
187 "output_row"."participation" := EXISTS (
|
jbe@244
|
188 SELECT NULL FROM "membership"
|
jbe@244
|
189 WHERE "area_id" = "area_id_p"
|
jbe@244
|
190 AND "member_id" = "output_row"."member_id"
|
jbe@244
|
191 );
|
jbe@244
|
192 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@244
|
193 WHERE "truster_id" = "output_row"."member_id"
|
jbe@244
|
194 AND (
|
jbe@244
|
195 "unit_id" = "unit_id_v" OR
|
jbe@244
|
196 "area_id" = "area_id_v"
|
jbe@244
|
197 )
|
jbe@244
|
198 ORDER BY "scope" DESC;
|
jbe@244
|
199 ELSIF "scope_v" = 'issue' THEN
|
jbe@244
|
200 IF "issue_row"."fully_frozen" ISNULL THEN
|
jbe@244
|
201 "output_row"."participation" := EXISTS (
|
jbe@244
|
202 SELECT NULL FROM "interest"
|
jbe@244
|
203 WHERE "issue_id" = "issue_id_p"
|
jbe@244
|
204 AND "member_id" = "output_row"."member_id"
|
jbe@244
|
205 );
|
jbe@244
|
206 ELSE
|
jbe@244
|
207 IF "output_row"."member_id" = "member_id_p" THEN
|
jbe@244
|
208 "output_row"."participation" := EXISTS (
|
jbe@244
|
209 SELECT NULL FROM "direct_voter"
|
jbe@244
|
210 WHERE "issue_id" = "issue_id_p"
|
jbe@244
|
211 AND "member_id" = "output_row"."member_id"
|
jbe@244
|
212 );
|
jbe@244
|
213 ELSE
|
jbe@244
|
214 "output_row"."participation" := NULL;
|
jbe@244
|
215 END IF;
|
jbe@244
|
216 END IF;
|
jbe@244
|
217 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@244
|
218 WHERE "truster_id" = "output_row"."member_id"
|
jbe@244
|
219 AND (
|
jbe@244
|
220 "unit_id" = "unit_id_v" OR
|
jbe@244
|
221 "area_id" = "area_id_v" OR
|
jbe@244
|
222 "issue_id" = "issue_id_p"
|
jbe@244
|
223 )
|
jbe@244
|
224 ORDER BY "scope" DESC;
|
jbe@244
|
225 END IF;
|
jbe@244
|
226 ELSE
|
jbe@244
|
227 "output_row"."member_valid" := FALSE;
|
jbe@244
|
228 "output_row"."participation" := FALSE;
|
jbe@244
|
229 "output_row"."scope_out" := NULL;
|
jbe@244
|
230 "delegation_row" := ROW(NULL);
|
jbe@244
|
231 END IF;
|
jbe@244
|
232 IF
|
jbe@244
|
233 "output_row"."member_id" = "member_id_p" AND
|
jbe@244
|
234 "simulate_trustee_id_p" NOTNULL
|
jbe@244
|
235 THEN
|
jbe@244
|
236 "output_row"."scope_out" := "scope_v";
|
jbe@244
|
237 "output_rows" := "output_rows" || "output_row";
|
jbe@244
|
238 "output_row"."member_id" := "simulate_trustee_id_p";
|
jbe@244
|
239 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
|
jbe@244
|
240 "output_row"."scope_out" := "delegation_row"."scope";
|
jbe@244
|
241 "output_rows" := "output_rows" || "output_row";
|
jbe@244
|
242 "output_row"."member_id" := "delegation_row"."trustee_id";
|
jbe@244
|
243 ELSIF "delegation_row"."scope" NOTNULL THEN
|
jbe@244
|
244 "output_row"."scope_out" := "delegation_row"."scope";
|
jbe@244
|
245 "output_row"."disabled_out" := TRUE;
|
jbe@244
|
246 "output_rows" := "output_rows" || "output_row";
|
jbe@244
|
247 EXIT;
|
jbe@244
|
248 ELSE
|
jbe@244
|
249 "output_row"."scope_out" := NULL;
|
jbe@244
|
250 "output_rows" := "output_rows" || "output_row";
|
jbe@244
|
251 EXIT;
|
jbe@244
|
252 END IF;
|
jbe@244
|
253 EXIT WHEN "loop_member_id_v" NOTNULL;
|
jbe@244
|
254 "output_row"."index" := "output_row"."index" + 1;
|
jbe@244
|
255 END LOOP;
|
jbe@244
|
256 "row_count" := array_upper("output_rows", 1);
|
jbe@244
|
257 "i" := 1;
|
jbe@244
|
258 "loop_v" := FALSE;
|
jbe@244
|
259 LOOP
|
jbe@244
|
260 "output_row" := "output_rows"["i"];
|
jbe@244
|
261 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
|
jbe@244
|
262 IF "loop_v" THEN
|
jbe@244
|
263 IF "i" + 1 = "row_count" THEN
|
jbe@244
|
264 "output_row"."loop" := 'last';
|
jbe@244
|
265 ELSIF "i" = "row_count" THEN
|
jbe@244
|
266 "output_row"."loop" := 'repetition';
|
jbe@244
|
267 ELSE
|
jbe@244
|
268 "output_row"."loop" := 'intermediate';
|
jbe@244
|
269 END IF;
|
jbe@244
|
270 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
|
jbe@244
|
271 "output_row"."loop" := 'first';
|
jbe@244
|
272 "loop_v" := TRUE;
|
jbe@244
|
273 END IF;
|
jbe@244
|
274 IF "scope_v" = 'unit' THEN
|
jbe@244
|
275 "output_row"."participation" := NULL;
|
jbe@244
|
276 END IF;
|
jbe@244
|
277 RETURN NEXT "output_row";
|
jbe@244
|
278 "i" := "i" + 1;
|
jbe@244
|
279 END LOOP;
|
jbe@244
|
280 RETURN;
|
jbe@244
|
281 END;
|
jbe@244
|
282 $$;
|
jbe@244
|
283
|
jbe@244
|
284 COMMENT ON FUNCTION "delegation_chain"
|
jbe@244
|
285 ( "member"."id"%TYPE,
|
jbe@244
|
286 "unit"."id"%TYPE,
|
jbe@244
|
287 "area"."id"%TYPE,
|
jbe@244
|
288 "issue"."id"%TYPE,
|
jbe@244
|
289 "member"."id"%TYPE )
|
jbe@244
|
290 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
|
jbe@244
|
291
|
jbe@244
|
292 CREATE TYPE "delegation_info_loop_type" AS ENUM
|
jbe@244
|
293 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
|
jbe@244
|
294
|
jbe@244
|
295 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''';
|
jbe@244
|
296
|
jbe@244
|
297 CREATE TYPE "delegation_info_type" AS (
|
jbe@244
|
298 "own_participation" BOOLEAN,
|
jbe@244
|
299 "own_delegation_scope" "delegation_scope",
|
jbe@244
|
300 "first_trustee_id" INT4,
|
jbe@244
|
301 "first_trustee_participation" BOOLEAN,
|
jbe@244
|
302 "first_trustee_ellipsis" BOOLEAN,
|
jbe@244
|
303 "other_trustee_id" INT4,
|
jbe@244
|
304 "other_trustee_participation" BOOLEAN,
|
jbe@244
|
305 "other_trustee_ellipsis" BOOLEAN,
|
jbe@244
|
306 "delegation_loop" "delegation_info_loop_type");
|
jbe@244
|
307
|
jbe@244
|
308 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';
|
jbe@244
|
309
|
jbe@244
|
310 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
|
jbe@244
|
311 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
|
jbe@244
|
312 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
|
jbe@244
|
313 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
|
jbe@244
|
314 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
|
jbe@244
|
315 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
|
jbe@244
|
316 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)';
|
jbe@244
|
317 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
|
jbe@244
|
318 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';
|
jbe@244
|
319
|
jbe@244
|
320 CREATE FUNCTION "delegation_info"
|
jbe@244
|
321 ( "member_id_p" "member"."id"%TYPE,
|
jbe@244
|
322 "unit_id_p" "unit"."id"%TYPE,
|
jbe@244
|
323 "area_id_p" "area"."id"%TYPE,
|
jbe@244
|
324 "issue_id_p" "issue"."id"%TYPE,
|
jbe@244
|
325 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL )
|
jbe@244
|
326 RETURNS "delegation_info_type"
|
jbe@244
|
327 LANGUAGE 'plpgsql' STABLE AS $$
|
jbe@244
|
328 DECLARE
|
jbe@244
|
329 "current_row" "delegation_chain_row";
|
jbe@244
|
330 "result" "delegation_info_type";
|
jbe@244
|
331 BEGIN
|
jbe@244
|
332 "result"."own_participation" := FALSE;
|
jbe@244
|
333 FOR "current_row" IN
|
jbe@244
|
334 SELECT * FROM "delegation_chain"(
|
jbe@244
|
335 "member_id_p",
|
jbe@244
|
336 "unit_id_p", "area_id_p", "issue_id_p",
|
jbe@244
|
337 "simulate_trustee_id_p")
|
jbe@244
|
338 LOOP
|
jbe@244
|
339 IF "current_row"."member_id" = "member_id_p" THEN
|
jbe@244
|
340 "result"."own_participation" := "current_row"."participation";
|
jbe@244
|
341 "result"."own_delegation_scope" := "current_row"."scope_out";
|
jbe@244
|
342 IF "current_row"."loop" = 'first' THEN
|
jbe@244
|
343 "result"."delegation_loop" := 'own';
|
jbe@244
|
344 END IF;
|
jbe@244
|
345 ELSIF
|
jbe@244
|
346 "current_row"."member_valid" AND
|
jbe@244
|
347 ( "current_row"."loop" ISNULL OR
|
jbe@244
|
348 "current_row"."loop" != 'repetition' )
|
jbe@244
|
349 THEN
|
jbe@244
|
350 IF "result"."first_trustee_id" ISNULL THEN
|
jbe@244
|
351 "result"."first_trustee_id" := "current_row"."member_id";
|
jbe@244
|
352 "result"."first_trustee_participation" := "current_row"."participation";
|
jbe@244
|
353 "result"."first_trustee_ellipsis" := FALSE;
|
jbe@244
|
354 IF "current_row"."loop" = 'first' THEN
|
jbe@244
|
355 "result"."delegation_loop" := 'first';
|
jbe@244
|
356 END IF;
|
jbe@244
|
357 ELSIF "result"."other_trustee_id" ISNULL THEN
|
jbe@247
|
358 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
|
jbe@244
|
359 "result"."other_trustee_id" := "current_row"."member_id";
|
jbe@244
|
360 "result"."other_trustee_participation" := TRUE;
|
jbe@244
|
361 "result"."other_trustee_ellipsis" := FALSE;
|
jbe@244
|
362 IF "current_row"."loop" = 'first' THEN
|
jbe@244
|
363 "result"."delegation_loop" := 'other';
|
jbe@244
|
364 END IF;
|
jbe@244
|
365 ELSE
|
jbe@244
|
366 "result"."first_trustee_ellipsis" := TRUE;
|
jbe@244
|
367 IF "current_row"."loop" = 'first' THEN
|
jbe@244
|
368 "result"."delegation_loop" := 'first_ellipsis';
|
jbe@244
|
369 END IF;
|
jbe@244
|
370 END IF;
|
jbe@244
|
371 ELSE
|
jbe@244
|
372 "result"."other_trustee_ellipsis" := TRUE;
|
jbe@244
|
373 IF "current_row"."loop" = 'first' THEN
|
jbe@244
|
374 "result"."delegation_loop" := 'other_ellipsis';
|
jbe@244
|
375 END IF;
|
jbe@244
|
376 END IF;
|
jbe@244
|
377 END IF;
|
jbe@244
|
378 END LOOP;
|
jbe@244
|
379 RETURN "result";
|
jbe@244
|
380 END;
|
jbe@244
|
381 $$;
|
jbe@244
|
382
|
jbe@244
|
383 COMMENT ON FUNCTION "delegation_info"
|
jbe@244
|
384 ( "member"."id"%TYPE,
|
jbe@244
|
385 "unit"."id"%TYPE,
|
jbe@244
|
386 "area"."id"%TYPE,
|
jbe@244
|
387 "issue"."id"%TYPE,
|
jbe@244
|
388 "member"."id"%TYPE )
|
jbe@244
|
389 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
|
jbe@244
|
390
|
jbe@250
|
391 CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
|
jbe@250
|
392 RETURNS VOID
|
jbe@250
|
393 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@250
|
394 DECLARE
|
jbe@250
|
395 "issue_row" "issue"%ROWTYPE;
|
jbe@250
|
396 "policy_row" "policy"%ROWTYPE;
|
jbe@250
|
397 "dimension_v" INTEGER;
|
jbe@250
|
398 "vote_matrix" INT4[][]; -- absolute votes
|
jbe@250
|
399 "matrix" INT8[][]; -- defeat strength / best paths
|
jbe@250
|
400 "i" INTEGER;
|
jbe@250
|
401 "j" INTEGER;
|
jbe@250
|
402 "k" INTEGER;
|
jbe@250
|
403 "battle_row" "battle"%ROWTYPE;
|
jbe@250
|
404 "rank_ary" INT4[];
|
jbe@250
|
405 "rank_v" INT4;
|
jbe@250
|
406 "done_v" INTEGER;
|
jbe@250
|
407 "winners_ary" INTEGER[];
|
jbe@250
|
408 "initiative_id_v" "initiative"."id"%TYPE;
|
jbe@250
|
409 BEGIN
|
jbe@250
|
410 SELECT * INTO "issue_row"
|
jbe@250
|
411 FROM "issue" WHERE "id" = "issue_id_p"
|
jbe@250
|
412 FOR UPDATE;
|
jbe@250
|
413 SELECT * INTO "policy_row"
|
jbe@250
|
414 FROM "policy" WHERE "id" = "issue_row"."policy_id";
|
jbe@250
|
415 SELECT count(1) INTO "dimension_v"
|
jbe@250
|
416 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
|
jbe@250
|
417 -- Create "vote_matrix" with absolute number of votes in pairwise
|
jbe@250
|
418 -- comparison:
|
jbe@250
|
419 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
|
jbe@250
|
420 "i" := 1;
|
jbe@250
|
421 "j" := 2;
|
jbe@250
|
422 FOR "battle_row" IN
|
jbe@250
|
423 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
|
jbe@250
|
424 ORDER BY
|
jbe@250
|
425 "winning_initiative_id" NULLS LAST,
|
jbe@250
|
426 "losing_initiative_id" NULLS LAST
|
jbe@250
|
427 LOOP
|
jbe@250
|
428 "vote_matrix"["i"]["j"] := "battle_row"."count";
|
jbe@250
|
429 IF "j" = "dimension_v" THEN
|
jbe@250
|
430 "i" := "i" + 1;
|
jbe@250
|
431 "j" := 1;
|
jbe@250
|
432 ELSE
|
jbe@250
|
433 "j" := "j" + 1;
|
jbe@250
|
434 IF "j" = "i" THEN
|
jbe@250
|
435 "j" := "j" + 1;
|
jbe@250
|
436 END IF;
|
jbe@250
|
437 END IF;
|
jbe@250
|
438 END LOOP;
|
jbe@250
|
439 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
|
jbe@250
|
440 RAISE EXCEPTION 'Wrong battle count (should not happen)';
|
jbe@250
|
441 END IF;
|
jbe@250
|
442 -- Store defeat strengths in "matrix" using "defeat_strength"
|
jbe@250
|
443 -- function:
|
jbe@250
|
444 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
|
jbe@250
|
445 "i" := 1;
|
jbe@250
|
446 LOOP
|
jbe@250
|
447 "j" := 1;
|
jbe@250
|
448 LOOP
|
jbe@250
|
449 IF "i" != "j" THEN
|
jbe@250
|
450 "matrix"["i"]["j"] := "defeat_strength"(
|
jbe@250
|
451 "vote_matrix"["i"]["j"],
|
jbe@250
|
452 "vote_matrix"["j"]["i"]
|
jbe@250
|
453 );
|
jbe@250
|
454 END IF;
|
jbe@250
|
455 EXIT WHEN "j" = "dimension_v";
|
jbe@250
|
456 "j" := "j" + 1;
|
jbe@250
|
457 END LOOP;
|
jbe@250
|
458 EXIT WHEN "i" = "dimension_v";
|
jbe@250
|
459 "i" := "i" + 1;
|
jbe@250
|
460 END LOOP;
|
jbe@250
|
461 -- Find best paths:
|
jbe@250
|
462 "i" := 1;
|
jbe@250
|
463 LOOP
|
jbe@250
|
464 "j" := 1;
|
jbe@250
|
465 LOOP
|
jbe@250
|
466 IF "i" != "j" THEN
|
jbe@250
|
467 "k" := 1;
|
jbe@250
|
468 LOOP
|
jbe@250
|
469 IF "i" != "k" AND "j" != "k" THEN
|
jbe@250
|
470 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
|
jbe@250
|
471 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
|
jbe@250
|
472 "matrix"["j"]["k"] := "matrix"["j"]["i"];
|
jbe@250
|
473 END IF;
|
jbe@250
|
474 ELSE
|
jbe@250
|
475 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
|
jbe@250
|
476 "matrix"["j"]["k"] := "matrix"["i"]["k"];
|
jbe@250
|
477 END IF;
|
jbe@250
|
478 END IF;
|
jbe@250
|
479 END IF;
|
jbe@250
|
480 EXIT WHEN "k" = "dimension_v";
|
jbe@250
|
481 "k" := "k" + 1;
|
jbe@250
|
482 END LOOP;
|
jbe@250
|
483 END IF;
|
jbe@250
|
484 EXIT WHEN "j" = "dimension_v";
|
jbe@250
|
485 "j" := "j" + 1;
|
jbe@250
|
486 END LOOP;
|
jbe@250
|
487 EXIT WHEN "i" = "dimension_v";
|
jbe@250
|
488 "i" := "i" + 1;
|
jbe@250
|
489 END LOOP;
|
jbe@250
|
490 -- Determine order of winners:
|
jbe@250
|
491 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
|
jbe@250
|
492 "rank_v" := 1;
|
jbe@250
|
493 "done_v" := 0;
|
jbe@250
|
494 LOOP
|
jbe@250
|
495 "winners_ary" := '{}';
|
jbe@250
|
496 "i" := 1;
|
jbe@250
|
497 LOOP
|
jbe@250
|
498 IF "rank_ary"["i"] ISNULL THEN
|
jbe@250
|
499 "j" := 1;
|
jbe@250
|
500 LOOP
|
jbe@250
|
501 IF
|
jbe@250
|
502 "i" != "j" AND
|
jbe@250
|
503 "rank_ary"["j"] ISNULL AND
|
jbe@250
|
504 "matrix"["j"]["i"] > "matrix"["i"]["j"]
|
jbe@250
|
505 THEN
|
jbe@250
|
506 -- someone else is better
|
jbe@250
|
507 EXIT;
|
jbe@250
|
508 END IF;
|
jbe@250
|
509 IF "j" = "dimension_v" THEN
|
jbe@250
|
510 -- noone is better
|
jbe@250
|
511 "winners_ary" := "winners_ary" || "i";
|
jbe@250
|
512 EXIT;
|
jbe@250
|
513 END IF;
|
jbe@250
|
514 "j" := "j" + 1;
|
jbe@250
|
515 END LOOP;
|
jbe@250
|
516 END IF;
|
jbe@250
|
517 EXIT WHEN "i" = "dimension_v";
|
jbe@250
|
518 "i" := "i" + 1;
|
jbe@250
|
519 END LOOP;
|
jbe@250
|
520 "i" := 1;
|
jbe@250
|
521 LOOP
|
jbe@250
|
522 "rank_ary"["winners_ary"["i"]] := "rank_v";
|
jbe@250
|
523 "done_v" := "done_v" + 1;
|
jbe@250
|
524 EXIT WHEN "i" = array_upper("winners_ary", 1);
|
jbe@250
|
525 "i" := "i" + 1;
|
jbe@250
|
526 END LOOP;
|
jbe@250
|
527 EXIT WHEN "done_v" = "dimension_v";
|
jbe@250
|
528 "rank_v" := "rank_v" + 1;
|
jbe@250
|
529 END LOOP;
|
jbe@250
|
530 -- write preliminary results:
|
jbe@250
|
531 "i" := 1;
|
jbe@250
|
532 FOR "initiative_id_v" IN
|
jbe@250
|
533 SELECT "id" FROM "initiative"
|
jbe@250
|
534 WHERE "issue_id" = "issue_id_p" AND "admitted"
|
jbe@250
|
535 ORDER BY "id"
|
jbe@250
|
536 LOOP
|
jbe@250
|
537 UPDATE "initiative" SET
|
jbe@250
|
538 "direct_majority" =
|
jbe@250
|
539 CASE WHEN "policy_row"."direct_majority_strict" THEN
|
jbe@250
|
540 "positive_votes" * "policy_row"."direct_majority_den" >
|
jbe@250
|
541 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
|
jbe@250
|
542 ELSE
|
jbe@250
|
543 "positive_votes" * "policy_row"."direct_majority_den" >=
|
jbe@250
|
544 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
|
jbe@250
|
545 END
|
jbe@250
|
546 AND "positive_votes" >= "policy_row"."direct_majority_positive"
|
jbe@250
|
547 AND "issue_row"."voter_count"-"negative_votes" >=
|
jbe@250
|
548 "policy_row"."direct_majority_non_negative",
|
jbe@250
|
549 "indirect_majority" =
|
jbe@250
|
550 CASE WHEN "policy_row"."indirect_majority_strict" THEN
|
jbe@250
|
551 "positive_votes" * "policy_row"."indirect_majority_den" >
|
jbe@250
|
552 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
|
jbe@250
|
553 ELSE
|
jbe@250
|
554 "positive_votes" * "policy_row"."indirect_majority_den" >=
|
jbe@250
|
555 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
|
jbe@250
|
556 END
|
jbe@250
|
557 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
|
jbe@250
|
558 AND "issue_row"."voter_count"-"negative_votes" >=
|
jbe@250
|
559 "policy_row"."indirect_majority_non_negative",
|
jbe@250
|
560 "schulze_rank" = "rank_ary"["i"],
|
jbe@250
|
561 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
|
jbe@250
|
562 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
|
jbe@250
|
563 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
|
jbe@250
|
564 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,
|
jbe@250
|
565 "eligible" = FALSE,
|
jbe@250
|
566 "winner" = FALSE,
|
jbe@250
|
567 "rank" = NULL -- NOTE: in cases of manual reset of issue state
|
jbe@250
|
568 WHERE "id" = "initiative_id_v";
|
jbe@250
|
569 "i" := "i" + 1;
|
jbe@250
|
570 END LOOP;
|
jbe@250
|
571 IF "i" != "dimension_v" THEN
|
jbe@250
|
572 RAISE EXCEPTION 'Wrong winner count (should not happen)';
|
jbe@250
|
573 END IF;
|
jbe@250
|
574 -- take indirect majorities into account:
|
jbe@250
|
575 LOOP
|
jbe@250
|
576 UPDATE "initiative" SET "indirect_majority" = TRUE
|
jbe@250
|
577 FROM (
|
jbe@250
|
578 SELECT "new_initiative"."id" AS "initiative_id"
|
jbe@250
|
579 FROM "initiative" "old_initiative"
|
jbe@250
|
580 JOIN "initiative" "new_initiative"
|
jbe@250
|
581 ON "new_initiative"."issue_id" = "issue_id_p"
|
jbe@250
|
582 AND "new_initiative"."indirect_majority" = FALSE
|
jbe@250
|
583 JOIN "battle" "battle_win"
|
jbe@250
|
584 ON "battle_win"."issue_id" = "issue_id_p"
|
jbe@250
|
585 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
|
jbe@250
|
586 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
|
jbe@250
|
587 JOIN "battle" "battle_lose"
|
jbe@250
|
588 ON "battle_lose"."issue_id" = "issue_id_p"
|
jbe@250
|
589 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
|
jbe@250
|
590 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
|
jbe@250
|
591 WHERE "old_initiative"."issue_id" = "issue_id_p"
|
jbe@250
|
592 AND "old_initiative"."indirect_majority" = TRUE
|
jbe@250
|
593 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
|
jbe@250
|
594 "battle_win"."count" * "policy_row"."indirect_majority_den" >
|
jbe@250
|
595 "policy_row"."indirect_majority_num" *
|
jbe@250
|
596 ("battle_win"."count"+"battle_lose"."count")
|
jbe@250
|
597 ELSE
|
jbe@250
|
598 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
|
jbe@250
|
599 "policy_row"."indirect_majority_num" *
|
jbe@250
|
600 ("battle_win"."count"+"battle_lose"."count")
|
jbe@250
|
601 END
|
jbe@250
|
602 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
|
jbe@250
|
603 AND "issue_row"."voter_count"-"battle_lose"."count" >=
|
jbe@250
|
604 "policy_row"."indirect_majority_non_negative"
|
jbe@250
|
605 ) AS "subquery"
|
jbe@250
|
606 WHERE "id" = "subquery"."initiative_id";
|
jbe@250
|
607 EXIT WHEN NOT FOUND;
|
jbe@250
|
608 END LOOP;
|
jbe@250
|
609 -- set "multistage_majority" for remaining matching initiatives:
|
jbe@250
|
610 UPDATE "initiative" SET "multistage_majority" = TRUE
|
jbe@250
|
611 FROM (
|
jbe@250
|
612 SELECT "losing_initiative"."id" AS "initiative_id"
|
jbe@250
|
613 FROM "initiative" "losing_initiative"
|
jbe@250
|
614 JOIN "initiative" "winning_initiative"
|
jbe@250
|
615 ON "winning_initiative"."issue_id" = "issue_id_p"
|
jbe@250
|
616 AND "winning_initiative"."admitted"
|
jbe@250
|
617 JOIN "battle" "battle_win"
|
jbe@250
|
618 ON "battle_win"."issue_id" = "issue_id_p"
|
jbe@250
|
619 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
|
jbe@250
|
620 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
|
jbe@250
|
621 JOIN "battle" "battle_lose"
|
jbe@250
|
622 ON "battle_lose"."issue_id" = "issue_id_p"
|
jbe@250
|
623 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
|
jbe@250
|
624 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
|
jbe@250
|
625 WHERE "losing_initiative"."issue_id" = "issue_id_p"
|
jbe@250
|
626 AND "losing_initiative"."admitted"
|
jbe@250
|
627 AND "winning_initiative"."schulze_rank" <
|
jbe@250
|
628 "losing_initiative"."schulze_rank"
|
jbe@250
|
629 AND "battle_win"."count" > "battle_lose"."count"
|
jbe@250
|
630 AND (
|
jbe@250
|
631 "battle_win"."count" > "winning_initiative"."positive_votes" OR
|
jbe@250
|
632 "battle_lose"."count" < "losing_initiative"."negative_votes" )
|
jbe@250
|
633 ) AS "subquery"
|
jbe@250
|
634 WHERE "id" = "subquery"."initiative_id";
|
jbe@250
|
635 -- mark eligible initiatives:
|
jbe@250
|
636 UPDATE "initiative" SET "eligible" = TRUE
|
jbe@250
|
637 WHERE "issue_id" = "issue_id_p"
|
jbe@250
|
638 AND "initiative"."direct_majority"
|
jbe@250
|
639 AND "initiative"."indirect_majority"
|
jbe@250
|
640 AND "initiative"."better_than_status_quo"
|
jbe@250
|
641 AND (
|
jbe@250
|
642 "policy_row"."no_multistage_majority" = FALSE OR
|
jbe@250
|
643 "initiative"."multistage_majority" = FALSE )
|
jbe@250
|
644 AND (
|
jbe@250
|
645 "policy_row"."no_reverse_beat_path" = FALSE OR
|
jbe@250
|
646 "initiative"."reverse_beat_path" = FALSE );
|
jbe@250
|
647 -- mark final winner:
|
jbe@250
|
648 UPDATE "initiative" SET "winner" = TRUE
|
jbe@250
|
649 FROM (
|
jbe@250
|
650 SELECT "id" AS "initiative_id"
|
jbe@250
|
651 FROM "initiative"
|
jbe@250
|
652 WHERE "issue_id" = "issue_id_p" AND "eligible"
|
jbe@250
|
653 ORDER BY
|
jbe@250
|
654 "schulze_rank",
|
jbe@250
|
655 "vote_ratio"("positive_votes", "negative_votes"),
|
jbe@250
|
656 "id"
|
jbe@250
|
657 LIMIT 1
|
jbe@250
|
658 ) AS "subquery"
|
jbe@250
|
659 WHERE "id" = "subquery"."initiative_id";
|
jbe@250
|
660 -- write (final) ranks:
|
jbe@250
|
661 "rank_v" := 1;
|
jbe@250
|
662 FOR "initiative_id_v" IN
|
jbe@250
|
663 SELECT "id"
|
jbe@250
|
664 FROM "initiative"
|
jbe@250
|
665 WHERE "issue_id" = "issue_id_p" AND "admitted"
|
jbe@250
|
666 ORDER BY
|
jbe@250
|
667 "winner" DESC,
|
jbe@250
|
668 "eligible" DESC,
|
jbe@250
|
669 "schulze_rank",
|
jbe@250
|
670 "vote_ratio"("positive_votes", "negative_votes"),
|
jbe@250
|
671 "id"
|
jbe@250
|
672 LOOP
|
jbe@250
|
673 UPDATE "initiative" SET "rank" = "rank_v"
|
jbe@250
|
674 WHERE "id" = "initiative_id_v";
|
jbe@250
|
675 "rank_v" := "rank_v" + 1;
|
jbe@250
|
676 END LOOP;
|
jbe@250
|
677 -- set schulze rank of status quo and mark issue as finished:
|
jbe@250
|
678 UPDATE "issue" SET
|
jbe@250
|
679 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
|
jbe@250
|
680 "state" =
|
jbe@250
|
681 CASE WHEN EXISTS (
|
jbe@250
|
682 SELECT NULL FROM "initiative"
|
jbe@250
|
683 WHERE "issue_id" = "issue_id_p" AND "winner"
|
jbe@250
|
684 ) THEN
|
jbe@250
|
685 'finished_with_winner'::"issue_state"
|
jbe@250
|
686 ELSE
|
jbe@250
|
687 'finished_without_winner'::"issue_state"
|
jbe@250
|
688 END,
|
jbe@250
|
689 "ranks_available" = TRUE
|
jbe@250
|
690 WHERE "id" = "issue_id_p";
|
jbe@250
|
691 RETURN;
|
jbe@250
|
692 END;
|
jbe@250
|
693 $$;
|
jbe@250
|
694
|
jbe@244
|
695 COMMIT;
|