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