liquid_feedback_core
changeset 244:d09c17b01c5d
Update script to v2.0.9
author | jbe |
---|---|
date | Thu May 10 18:32:43 2012 +0200 (2012-05-10) |
parents | d2642db42ad0 |
children | 8423373fbadd |
files | core.sql update/core-update.v2.0.8-v2.0.9.sql |
line diff
1.1 --- a/core.sql Thu May 10 18:24:36 2012 +0200 1.2 +++ b/core.sql Thu May 10 18:32:43 2012 +0200 1.3 @@ -7,7 +7,7 @@ 1.4 BEGIN; 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('2.0.8', 2, 0, 8)) 1.8 + SELECT * FROM (VALUES ('2.0.9', 2, 0, 9)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 2.2 +++ b/update/core-update.v2.0.8-v2.0.9.sql Thu May 10 18:32:43 2012 +0200 2.3 @@ -0,0 +1,374 @@ 2.4 +BEGIN; 2.5 + 2.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 2.7 + SELECT * FROM (VALUES ('2.0.9', 2, 0, 9)) 2.8 + AS "subquery"("string", "major", "minor", "revision"); 2.9 + 2.10 +COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function'; 2.11 + 2.12 +CREATE FUNCTION "delegation_chain_for_closed_issue" 2.13 + ( "member_id_p" "member"."id"%TYPE, 2.14 + "issue_id_p" "issue"."id"%TYPE ) 2.15 + RETURNS SETOF "delegation_chain_row" 2.16 + LANGUAGE 'plpgsql' STABLE AS $$ 2.17 + DECLARE 2.18 + "output_row" "delegation_chain_row"; 2.19 + "direct_voter_row" "direct_voter"%ROWTYPE; 2.20 + "delegating_voter_row" "delegating_voter"%ROWTYPE; 2.21 + BEGIN 2.22 + "output_row"."index" := 0; 2.23 + "output_row"."member_id" := "member_id_p"; 2.24 + "output_row"."member_valid" := TRUE; 2.25 + "output_row"."participation" := FALSE; 2.26 + "output_row"."overridden" := FALSE; 2.27 + "output_row"."disabled_out" := FALSE; 2.28 + LOOP 2.29 + SELECT INTO "direct_voter_row" * FROM "direct_voter" 2.30 + WHERE "issue_id" = "issue_id_p" 2.31 + AND "member_id" = "output_row"."member_id"; 2.32 + IF "direct_voter_row"."member_id" NOTNULL THEN 2.33 + "output_row"."participation" := TRUE; 2.34 + "output_row"."scope_out" := NULL; 2.35 + "output_row"."disabled_out" := NULL; 2.36 + RETURN NEXT "output_row"; 2.37 + RETURN; 2.38 + END IF; 2.39 + SELECT INTO "delegating_voter_row" * FROM "delegating_voter" 2.40 + WHERE "issue_id" = "issue_id_p" 2.41 + AND "member_id" = "output_row"."member_id"; 2.42 + IF "delegating_voter_row"."member_id" ISNULL THEN 2.43 + RETURN; 2.44 + END IF; 2.45 + "output_row"."scope_out" := "delegating_voter_row"."scope"; 2.46 + RETURN NEXT "output_row"; 2.47 + "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1]; 2.48 + "output_row"."scope_in" := "output_row"."scope_out"; 2.49 + END LOOP; 2.50 + END; 2.51 + $$; 2.52 + 2.53 +COMMENT ON FUNCTION "delegation_chain_for_closed_issue" 2.54 + ( "member"."id"%TYPE, 2.55 + "member"."id"%TYPE ) 2.56 + IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting'; 2.57 + 2.58 +DROP FUNCTION "delegation_chain" 2.59 + ( "member"."id"%TYPE, 2.60 + "unit"."id"%TYPE, 2.61 + "area"."id"%TYPE, 2.62 + "issue"."id"%TYPE ); 2.63 + 2.64 +DROP FUNCTION "delegation_chain" 2.65 + ( "member"."id"%TYPE, 2.66 + "unit"."id"%TYPE, 2.67 + "area"."id"%TYPE, 2.68 + "issue"."id"%TYPE, 2.69 + "member"."id"%TYPE ); 2.70 + 2.71 +CREATE FUNCTION "delegation_chain" 2.72 + ( "member_id_p" "member"."id"%TYPE, 2.73 + "unit_id_p" "unit"."id"%TYPE, 2.74 + "area_id_p" "area"."id"%TYPE, 2.75 + "issue_id_p" "issue"."id"%TYPE, 2.76 + "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL ) 2.77 + RETURNS SETOF "delegation_chain_row" 2.78 + LANGUAGE 'plpgsql' STABLE AS $$ 2.79 + DECLARE 2.80 + "scope_v" "delegation_scope"; 2.81 + "unit_id_v" "unit"."id"%TYPE; 2.82 + "area_id_v" "area"."id"%TYPE; 2.83 + "issue_row" "issue"%ROWTYPE; 2.84 + "visited_member_ids" INT4[]; -- "member"."id"%TYPE[] 2.85 + "loop_member_id_v" "member"."id"%TYPE; 2.86 + "output_row" "delegation_chain_row"; 2.87 + "output_rows" "delegation_chain_row"[]; 2.88 + "delegation_row" "delegation"%ROWTYPE; 2.89 + "row_count" INT4; 2.90 + "i" INT4; 2.91 + "loop_v" BOOLEAN; 2.92 + BEGIN 2.93 + IF 2.94 + "unit_id_p" NOTNULL AND 2.95 + "area_id_p" ISNULL AND 2.96 + "issue_id_p" ISNULL 2.97 + THEN 2.98 + "scope_v" := 'unit'; 2.99 + "unit_id_v" := "unit_id_p"; 2.100 + ELSIF 2.101 + "unit_id_p" ISNULL AND 2.102 + "area_id_p" NOTNULL AND 2.103 + "issue_id_p" ISNULL 2.104 + THEN 2.105 + "scope_v" := 'area'; 2.106 + "area_id_v" := "area_id_p"; 2.107 + SELECT "unit_id" INTO "unit_id_v" 2.108 + FROM "area" WHERE "id" = "area_id_v"; 2.109 + ELSIF 2.110 + "unit_id_p" ISNULL AND 2.111 + "area_id_p" ISNULL AND 2.112 + "issue_id_p" NOTNULL 2.113 + THEN 2.114 + SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p"; 2.115 + IF "issue_row"."id" ISNULL THEN 2.116 + RETURN; 2.117 + END IF; 2.118 + IF "issue_row"."closed" NOTNULL THEN 2.119 + IF "simulate_trustee_id_p" NOTNULL THEN 2.120 + RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.'; 2.121 + END IF; 2.122 + FOR "output_row" IN 2.123 + SELECT * FROM 2.124 + "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p") 2.125 + LOOP 2.126 + RETURN NEXT "output_row"; 2.127 + END LOOP; 2.128 + RETURN; 2.129 + END IF; 2.130 + "scope_v" := 'issue'; 2.131 + SELECT "area_id" INTO "area_id_v" 2.132 + FROM "issue" WHERE "id" = "issue_id_p"; 2.133 + SELECT "unit_id" INTO "unit_id_v" 2.134 + FROM "area" WHERE "id" = "area_id_v"; 2.135 + ELSE 2.136 + RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.'; 2.137 + END IF; 2.138 + "visited_member_ids" := '{}'; 2.139 + "loop_member_id_v" := NULL; 2.140 + "output_rows" := '{}'; 2.141 + "output_row"."index" := 0; 2.142 + "output_row"."member_id" := "member_id_p"; 2.143 + "output_row"."member_valid" := TRUE; 2.144 + "output_row"."participation" := FALSE; 2.145 + "output_row"."overridden" := FALSE; 2.146 + "output_row"."disabled_out" := FALSE; 2.147 + "output_row"."scope_out" := NULL; 2.148 + LOOP 2.149 + IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN 2.150 + "loop_member_id_v" := "output_row"."member_id"; 2.151 + ELSE 2.152 + "visited_member_ids" := 2.153 + "visited_member_ids" || "output_row"."member_id"; 2.154 + END IF; 2.155 + IF "output_row"."participation" ISNULL THEN 2.156 + "output_row"."overridden" := NULL; 2.157 + ELSIF "output_row"."participation" THEN 2.158 + "output_row"."overridden" := TRUE; 2.159 + END IF; 2.160 + "output_row"."scope_in" := "output_row"."scope_out"; 2.161 + IF EXISTS ( 2.162 + SELECT NULL FROM "member" JOIN "privilege" 2.163 + ON "privilege"."member_id" = "member"."id" 2.164 + AND "privilege"."unit_id" = "unit_id_v" 2.165 + WHERE "id" = "output_row"."member_id" 2.166 + AND "member"."active" AND "privilege"."voting_right" 2.167 + ) THEN 2.168 + IF "scope_v" = 'unit' THEN 2.169 + SELECT * INTO "delegation_row" FROM "delegation" 2.170 + WHERE "truster_id" = "output_row"."member_id" 2.171 + AND "unit_id" = "unit_id_v"; 2.172 + ELSIF "scope_v" = 'area' THEN 2.173 + "output_row"."participation" := EXISTS ( 2.174 + SELECT NULL FROM "membership" 2.175 + WHERE "area_id" = "area_id_p" 2.176 + AND "member_id" = "output_row"."member_id" 2.177 + ); 2.178 + SELECT * INTO "delegation_row" FROM "delegation" 2.179 + WHERE "truster_id" = "output_row"."member_id" 2.180 + AND ( 2.181 + "unit_id" = "unit_id_v" OR 2.182 + "area_id" = "area_id_v" 2.183 + ) 2.184 + ORDER BY "scope" DESC; 2.185 + ELSIF "scope_v" = 'issue' THEN 2.186 + IF "issue_row"."fully_frozen" ISNULL THEN 2.187 + "output_row"."participation" := EXISTS ( 2.188 + SELECT NULL FROM "interest" 2.189 + WHERE "issue_id" = "issue_id_p" 2.190 + AND "member_id" = "output_row"."member_id" 2.191 + ); 2.192 + ELSE 2.193 + IF "output_row"."member_id" = "member_id_p" THEN 2.194 + "output_row"."participation" := EXISTS ( 2.195 + SELECT NULL FROM "direct_voter" 2.196 + WHERE "issue_id" = "issue_id_p" 2.197 + AND "member_id" = "output_row"."member_id" 2.198 + ); 2.199 + ELSE 2.200 + "output_row"."participation" := NULL; 2.201 + END IF; 2.202 + END IF; 2.203 + SELECT * INTO "delegation_row" FROM "delegation" 2.204 + WHERE "truster_id" = "output_row"."member_id" 2.205 + AND ( 2.206 + "unit_id" = "unit_id_v" OR 2.207 + "area_id" = "area_id_v" OR 2.208 + "issue_id" = "issue_id_p" 2.209 + ) 2.210 + ORDER BY "scope" DESC; 2.211 + END IF; 2.212 + ELSE 2.213 + "output_row"."member_valid" := FALSE; 2.214 + "output_row"."participation" := FALSE; 2.215 + "output_row"."scope_out" := NULL; 2.216 + "delegation_row" := ROW(NULL); 2.217 + END IF; 2.218 + IF 2.219 + "output_row"."member_id" = "member_id_p" AND 2.220 + "simulate_trustee_id_p" NOTNULL 2.221 + THEN 2.222 + "output_row"."scope_out" := "scope_v"; 2.223 + "output_rows" := "output_rows" || "output_row"; 2.224 + "output_row"."member_id" := "simulate_trustee_id_p"; 2.225 + ELSIF "delegation_row"."trustee_id" NOTNULL THEN 2.226 + "output_row"."scope_out" := "delegation_row"."scope"; 2.227 + "output_rows" := "output_rows" || "output_row"; 2.228 + "output_row"."member_id" := "delegation_row"."trustee_id"; 2.229 + ELSIF "delegation_row"."scope" NOTNULL THEN 2.230 + "output_row"."scope_out" := "delegation_row"."scope"; 2.231 + "output_row"."disabled_out" := TRUE; 2.232 + "output_rows" := "output_rows" || "output_row"; 2.233 + EXIT; 2.234 + ELSE 2.235 + "output_row"."scope_out" := NULL; 2.236 + "output_rows" := "output_rows" || "output_row"; 2.237 + EXIT; 2.238 + END IF; 2.239 + EXIT WHEN "loop_member_id_v" NOTNULL; 2.240 + "output_row"."index" := "output_row"."index" + 1; 2.241 + END LOOP; 2.242 + "row_count" := array_upper("output_rows", 1); 2.243 + "i" := 1; 2.244 + "loop_v" := FALSE; 2.245 + LOOP 2.246 + "output_row" := "output_rows"["i"]; 2.247 + EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results! 2.248 + IF "loop_v" THEN 2.249 + IF "i" + 1 = "row_count" THEN 2.250 + "output_row"."loop" := 'last'; 2.251 + ELSIF "i" = "row_count" THEN 2.252 + "output_row"."loop" := 'repetition'; 2.253 + ELSE 2.254 + "output_row"."loop" := 'intermediate'; 2.255 + END IF; 2.256 + ELSIF "output_row"."member_id" = "loop_member_id_v" THEN 2.257 + "output_row"."loop" := 'first'; 2.258 + "loop_v" := TRUE; 2.259 + END IF; 2.260 + IF "scope_v" = 'unit' THEN 2.261 + "output_row"."participation" := NULL; 2.262 + END IF; 2.263 + RETURN NEXT "output_row"; 2.264 + "i" := "i" + 1; 2.265 + END LOOP; 2.266 + RETURN; 2.267 + END; 2.268 + $$; 2.269 + 2.270 +COMMENT ON FUNCTION "delegation_chain" 2.271 + ( "member"."id"%TYPE, 2.272 + "unit"."id"%TYPE, 2.273 + "area"."id"%TYPE, 2.274 + "issue"."id"%TYPE, 2.275 + "member"."id"%TYPE ) 2.276 + IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information'; 2.277 + 2.278 +CREATE TYPE "delegation_info_loop_type" AS ENUM 2.279 + ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis'); 2.280 + 2.281 +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'''; 2.282 + 2.283 +CREATE TYPE "delegation_info_type" AS ( 2.284 + "own_participation" BOOLEAN, 2.285 + "own_delegation_scope" "delegation_scope", 2.286 + "first_trustee_id" INT4, 2.287 + "first_trustee_participation" BOOLEAN, 2.288 + "first_trustee_ellipsis" BOOLEAN, 2.289 + "other_trustee_id" INT4, 2.290 + "other_trustee_participation" BOOLEAN, 2.291 + "other_trustee_ellipsis" BOOLEAN, 2.292 + "delegation_loop" "delegation_info_loop_type"); 2.293 + 2.294 +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'; 2.295 + 2.296 +COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating'; 2.297 +COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member'; 2.298 +COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member'; 2.299 +COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating'; 2.300 +COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"'; 2.301 +COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)'; 2.302 +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)'; 2.303 +COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"'; 2.304 +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'; 2.305 + 2.306 +CREATE FUNCTION "delegation_info" 2.307 + ( "member_id_p" "member"."id"%TYPE, 2.308 + "unit_id_p" "unit"."id"%TYPE, 2.309 + "area_id_p" "area"."id"%TYPE, 2.310 + "issue_id_p" "issue"."id"%TYPE, 2.311 + "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL ) 2.312 + RETURNS "delegation_info_type" 2.313 + LANGUAGE 'plpgsql' STABLE AS $$ 2.314 + DECLARE 2.315 + "current_row" "delegation_chain_row"; 2.316 + "result" "delegation_info_type"; 2.317 + BEGIN 2.318 + "result"."own_participation" := FALSE; 2.319 + FOR "current_row" IN 2.320 + SELECT * FROM "delegation_chain"( 2.321 + "member_id_p", 2.322 + "unit_id_p", "area_id_p", "issue_id_p", 2.323 + "simulate_trustee_id_p") 2.324 + LOOP 2.325 + IF "current_row"."member_id" = "member_id_p" THEN 2.326 + "result"."own_participation" := "current_row"."participation"; 2.327 + "result"."own_delegation_scope" := "current_row"."scope_out"; 2.328 + IF "current_row"."loop" = 'first' THEN 2.329 + "result"."delegation_loop" := 'own'; 2.330 + END IF; 2.331 + ELSIF 2.332 + "current_row"."member_valid" AND 2.333 + ( "current_row"."loop" ISNULL OR 2.334 + "current_row"."loop" != 'repetition' ) 2.335 + THEN 2.336 + IF "result"."first_trustee_id" ISNULL THEN 2.337 + "result"."first_trustee_id" := "current_row"."member_id"; 2.338 + "result"."first_trustee_participation" := "current_row"."participation"; 2.339 + "result"."first_trustee_ellipsis" := FALSE; 2.340 + IF "current_row"."loop" = 'first' THEN 2.341 + "result"."delegation_loop" := 'first'; 2.342 + END IF; 2.343 + ELSIF "result"."other_trustee_id" ISNULL THEN 2.344 + IF "current_row"."participation" THEN 2.345 + "result"."other_trustee_id" := "current_row"."member_id"; 2.346 + "result"."other_trustee_participation" := TRUE; 2.347 + "result"."other_trustee_ellipsis" := FALSE; 2.348 + IF "current_row"."loop" = 'first' THEN 2.349 + "result"."delegation_loop" := 'other'; 2.350 + END IF; 2.351 + ELSE 2.352 + "result"."first_trustee_ellipsis" := TRUE; 2.353 + IF "current_row"."loop" = 'first' THEN 2.354 + "result"."delegation_loop" := 'first_ellipsis'; 2.355 + END IF; 2.356 + END IF; 2.357 + ELSE 2.358 + "result"."other_trustee_ellipsis" := TRUE; 2.359 + IF "current_row"."loop" = 'first' THEN 2.360 + "result"."delegation_loop" := 'other_ellipsis'; 2.361 + END IF; 2.362 + END IF; 2.363 + END IF; 2.364 + END LOOP; 2.365 + RETURN "result"; 2.366 + END; 2.367 + $$; 2.368 + 2.369 +COMMENT ON FUNCTION "delegation_info" 2.370 + ( "member"."id"%TYPE, 2.371 + "unit"."id"%TYPE, 2.372 + "area"."id"%TYPE, 2.373 + "issue"."id"%TYPE, 2.374 + "member"."id"%TYPE ) 2.375 + IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information'; 2.376 + 2.377 +COMMIT;