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