liquid_feedback_core
diff update/core-update.v2.0.8-v2.0.9.sql @ 244:d09c17b01c5d
Update script to v2.0.9
| author | jbe |
|---|---|
| date | Thu May 10 18:32:43 2012 +0200 (2012-05-10) |
| parents | |
| children | 4b8cc6fc2d00 |
line diff
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/update/core-update.v2.0.8-v2.0.9.sql Thu May 10 18:32:43 2012 +0200 1.3 @@ -0,0 +1,374 @@ 1.4 +BEGIN; 1.5 + 1.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.7 + SELECT * FROM (VALUES ('2.0.9', 2, 0, 9)) 1.8 + AS "subquery"("string", "major", "minor", "revision"); 1.9 + 1.10 +COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function'; 1.11 + 1.12 +CREATE FUNCTION "delegation_chain_for_closed_issue" 1.13 + ( "member_id_p" "member"."id"%TYPE, 1.14 + "issue_id_p" "issue"."id"%TYPE ) 1.15 + RETURNS SETOF "delegation_chain_row" 1.16 + LANGUAGE 'plpgsql' STABLE AS $$ 1.17 + DECLARE 1.18 + "output_row" "delegation_chain_row"; 1.19 + "direct_voter_row" "direct_voter"%ROWTYPE; 1.20 + "delegating_voter_row" "delegating_voter"%ROWTYPE; 1.21 + BEGIN 1.22 + "output_row"."index" := 0; 1.23 + "output_row"."member_id" := "member_id_p"; 1.24 + "output_row"."member_valid" := TRUE; 1.25 + "output_row"."participation" := FALSE; 1.26 + "output_row"."overridden" := FALSE; 1.27 + "output_row"."disabled_out" := FALSE; 1.28 + LOOP 1.29 + SELECT INTO "direct_voter_row" * FROM "direct_voter" 1.30 + WHERE "issue_id" = "issue_id_p" 1.31 + AND "member_id" = "output_row"."member_id"; 1.32 + IF "direct_voter_row"."member_id" NOTNULL THEN 1.33 + "output_row"."participation" := TRUE; 1.34 + "output_row"."scope_out" := NULL; 1.35 + "output_row"."disabled_out" := NULL; 1.36 + RETURN NEXT "output_row"; 1.37 + RETURN; 1.38 + END IF; 1.39 + SELECT INTO "delegating_voter_row" * FROM "delegating_voter" 1.40 + WHERE "issue_id" = "issue_id_p" 1.41 + AND "member_id" = "output_row"."member_id"; 1.42 + IF "delegating_voter_row"."member_id" ISNULL THEN 1.43 + RETURN; 1.44 + END IF; 1.45 + "output_row"."scope_out" := "delegating_voter_row"."scope"; 1.46 + RETURN NEXT "output_row"; 1.47 + "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1]; 1.48 + "output_row"."scope_in" := "output_row"."scope_out"; 1.49 + END LOOP; 1.50 + END; 1.51 + $$; 1.52 + 1.53 +COMMENT ON FUNCTION "delegation_chain_for_closed_issue" 1.54 + ( "member"."id"%TYPE, 1.55 + "member"."id"%TYPE ) 1.56 + IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting'; 1.57 + 1.58 +DROP FUNCTION "delegation_chain" 1.59 + ( "member"."id"%TYPE, 1.60 + "unit"."id"%TYPE, 1.61 + "area"."id"%TYPE, 1.62 + "issue"."id"%TYPE ); 1.63 + 1.64 +DROP FUNCTION "delegation_chain" 1.65 + ( "member"."id"%TYPE, 1.66 + "unit"."id"%TYPE, 1.67 + "area"."id"%TYPE, 1.68 + "issue"."id"%TYPE, 1.69 + "member"."id"%TYPE ); 1.70 + 1.71 +CREATE FUNCTION "delegation_chain" 1.72 + ( "member_id_p" "member"."id"%TYPE, 1.73 + "unit_id_p" "unit"."id"%TYPE, 1.74 + "area_id_p" "area"."id"%TYPE, 1.75 + "issue_id_p" "issue"."id"%TYPE, 1.76 + "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL ) 1.77 + RETURNS SETOF "delegation_chain_row" 1.78 + LANGUAGE 'plpgsql' STABLE AS $$ 1.79 + DECLARE 1.80 + "scope_v" "delegation_scope"; 1.81 + "unit_id_v" "unit"."id"%TYPE; 1.82 + "area_id_v" "area"."id"%TYPE; 1.83 + "issue_row" "issue"%ROWTYPE; 1.84 + "visited_member_ids" INT4[]; -- "member"."id"%TYPE[] 1.85 + "loop_member_id_v" "member"."id"%TYPE; 1.86 + "output_row" "delegation_chain_row"; 1.87 + "output_rows" "delegation_chain_row"[]; 1.88 + "delegation_row" "delegation"%ROWTYPE; 1.89 + "row_count" INT4; 1.90 + "i" INT4; 1.91 + "loop_v" BOOLEAN; 1.92 + BEGIN 1.93 + IF 1.94 + "unit_id_p" NOTNULL AND 1.95 + "area_id_p" ISNULL AND 1.96 + "issue_id_p" ISNULL 1.97 + THEN 1.98 + "scope_v" := 'unit'; 1.99 + "unit_id_v" := "unit_id_p"; 1.100 + ELSIF 1.101 + "unit_id_p" ISNULL AND 1.102 + "area_id_p" NOTNULL AND 1.103 + "issue_id_p" ISNULL 1.104 + THEN 1.105 + "scope_v" := 'area'; 1.106 + "area_id_v" := "area_id_p"; 1.107 + SELECT "unit_id" INTO "unit_id_v" 1.108 + FROM "area" WHERE "id" = "area_id_v"; 1.109 + ELSIF 1.110 + "unit_id_p" ISNULL AND 1.111 + "area_id_p" ISNULL AND 1.112 + "issue_id_p" NOTNULL 1.113 + THEN 1.114 + SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p"; 1.115 + IF "issue_row"."id" ISNULL THEN 1.116 + RETURN; 1.117 + END IF; 1.118 + IF "issue_row"."closed" NOTNULL THEN 1.119 + IF "simulate_trustee_id_p" NOTNULL THEN 1.120 + RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.'; 1.121 + END IF; 1.122 + FOR "output_row" IN 1.123 + SELECT * FROM 1.124 + "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p") 1.125 + LOOP 1.126 + RETURN NEXT "output_row"; 1.127 + END LOOP; 1.128 + RETURN; 1.129 + END IF; 1.130 + "scope_v" := 'issue'; 1.131 + SELECT "area_id" INTO "area_id_v" 1.132 + FROM "issue" WHERE "id" = "issue_id_p"; 1.133 + SELECT "unit_id" INTO "unit_id_v" 1.134 + FROM "area" WHERE "id" = "area_id_v"; 1.135 + ELSE 1.136 + RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.'; 1.137 + END IF; 1.138 + "visited_member_ids" := '{}'; 1.139 + "loop_member_id_v" := NULL; 1.140 + "output_rows" := '{}'; 1.141 + "output_row"."index" := 0; 1.142 + "output_row"."member_id" := "member_id_p"; 1.143 + "output_row"."member_valid" := TRUE; 1.144 + "output_row"."participation" := FALSE; 1.145 + "output_row"."overridden" := FALSE; 1.146 + "output_row"."disabled_out" := FALSE; 1.147 + "output_row"."scope_out" := NULL; 1.148 + LOOP 1.149 + IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN 1.150 + "loop_member_id_v" := "output_row"."member_id"; 1.151 + ELSE 1.152 + "visited_member_ids" := 1.153 + "visited_member_ids" || "output_row"."member_id"; 1.154 + END IF; 1.155 + IF "output_row"."participation" ISNULL THEN 1.156 + "output_row"."overridden" := NULL; 1.157 + ELSIF "output_row"."participation" THEN 1.158 + "output_row"."overridden" := TRUE; 1.159 + END IF; 1.160 + "output_row"."scope_in" := "output_row"."scope_out"; 1.161 + IF EXISTS ( 1.162 + SELECT NULL FROM "member" JOIN "privilege" 1.163 + ON "privilege"."member_id" = "member"."id" 1.164 + AND "privilege"."unit_id" = "unit_id_v" 1.165 + WHERE "id" = "output_row"."member_id" 1.166 + AND "member"."active" AND "privilege"."voting_right" 1.167 + ) THEN 1.168 + IF "scope_v" = 'unit' THEN 1.169 + SELECT * INTO "delegation_row" FROM "delegation" 1.170 + WHERE "truster_id" = "output_row"."member_id" 1.171 + AND "unit_id" = "unit_id_v"; 1.172 + ELSIF "scope_v" = 'area' THEN 1.173 + "output_row"."participation" := EXISTS ( 1.174 + SELECT NULL FROM "membership" 1.175 + WHERE "area_id" = "area_id_p" 1.176 + AND "member_id" = "output_row"."member_id" 1.177 + ); 1.178 + SELECT * INTO "delegation_row" FROM "delegation" 1.179 + WHERE "truster_id" = "output_row"."member_id" 1.180 + AND ( 1.181 + "unit_id" = "unit_id_v" OR 1.182 + "area_id" = "area_id_v" 1.183 + ) 1.184 + ORDER BY "scope" DESC; 1.185 + ELSIF "scope_v" = 'issue' THEN 1.186 + IF "issue_row"."fully_frozen" ISNULL THEN 1.187 + "output_row"."participation" := EXISTS ( 1.188 + SELECT NULL FROM "interest" 1.189 + WHERE "issue_id" = "issue_id_p" 1.190 + AND "member_id" = "output_row"."member_id" 1.191 + ); 1.192 + ELSE 1.193 + IF "output_row"."member_id" = "member_id_p" THEN 1.194 + "output_row"."participation" := EXISTS ( 1.195 + SELECT NULL FROM "direct_voter" 1.196 + WHERE "issue_id" = "issue_id_p" 1.197 + AND "member_id" = "output_row"."member_id" 1.198 + ); 1.199 + ELSE 1.200 + "output_row"."participation" := NULL; 1.201 + END IF; 1.202 + END IF; 1.203 + SELECT * INTO "delegation_row" FROM "delegation" 1.204 + WHERE "truster_id" = "output_row"."member_id" 1.205 + AND ( 1.206 + "unit_id" = "unit_id_v" OR 1.207 + "area_id" = "area_id_v" OR 1.208 + "issue_id" = "issue_id_p" 1.209 + ) 1.210 + ORDER BY "scope" DESC; 1.211 + END IF; 1.212 + ELSE 1.213 + "output_row"."member_valid" := FALSE; 1.214 + "output_row"."participation" := FALSE; 1.215 + "output_row"."scope_out" := NULL; 1.216 + "delegation_row" := ROW(NULL); 1.217 + END IF; 1.218 + IF 1.219 + "output_row"."member_id" = "member_id_p" AND 1.220 + "simulate_trustee_id_p" NOTNULL 1.221 + THEN 1.222 + "output_row"."scope_out" := "scope_v"; 1.223 + "output_rows" := "output_rows" || "output_row"; 1.224 + "output_row"."member_id" := "simulate_trustee_id_p"; 1.225 + ELSIF "delegation_row"."trustee_id" NOTNULL THEN 1.226 + "output_row"."scope_out" := "delegation_row"."scope"; 1.227 + "output_rows" := "output_rows" || "output_row"; 1.228 + "output_row"."member_id" := "delegation_row"."trustee_id"; 1.229 + ELSIF "delegation_row"."scope" NOTNULL THEN 1.230 + "output_row"."scope_out" := "delegation_row"."scope"; 1.231 + "output_row"."disabled_out" := TRUE; 1.232 + "output_rows" := "output_rows" || "output_row"; 1.233 + EXIT; 1.234 + ELSE 1.235 + "output_row"."scope_out" := NULL; 1.236 + "output_rows" := "output_rows" || "output_row"; 1.237 + EXIT; 1.238 + END IF; 1.239 + EXIT WHEN "loop_member_id_v" NOTNULL; 1.240 + "output_row"."index" := "output_row"."index" + 1; 1.241 + END LOOP; 1.242 + "row_count" := array_upper("output_rows", 1); 1.243 + "i" := 1; 1.244 + "loop_v" := FALSE; 1.245 + LOOP 1.246 + "output_row" := "output_rows"["i"]; 1.247 + EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results! 1.248 + IF "loop_v" THEN 1.249 + IF "i" + 1 = "row_count" THEN 1.250 + "output_row"."loop" := 'last'; 1.251 + ELSIF "i" = "row_count" THEN 1.252 + "output_row"."loop" := 'repetition'; 1.253 + ELSE 1.254 + "output_row"."loop" := 'intermediate'; 1.255 + END IF; 1.256 + ELSIF "output_row"."member_id" = "loop_member_id_v" THEN 1.257 + "output_row"."loop" := 'first'; 1.258 + "loop_v" := TRUE; 1.259 + END IF; 1.260 + IF "scope_v" = 'unit' THEN 1.261 + "output_row"."participation" := NULL; 1.262 + END IF; 1.263 + RETURN NEXT "output_row"; 1.264 + "i" := "i" + 1; 1.265 + END LOOP; 1.266 + RETURN; 1.267 + END; 1.268 + $$; 1.269 + 1.270 +COMMENT ON FUNCTION "delegation_chain" 1.271 + ( "member"."id"%TYPE, 1.272 + "unit"."id"%TYPE, 1.273 + "area"."id"%TYPE, 1.274 + "issue"."id"%TYPE, 1.275 + "member"."id"%TYPE ) 1.276 + IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information'; 1.277 + 1.278 +CREATE TYPE "delegation_info_loop_type" AS ENUM 1.279 + ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis'); 1.280 + 1.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'''; 1.282 + 1.283 +CREATE TYPE "delegation_info_type" AS ( 1.284 + "own_participation" BOOLEAN, 1.285 + "own_delegation_scope" "delegation_scope", 1.286 + "first_trustee_id" INT4, 1.287 + "first_trustee_participation" BOOLEAN, 1.288 + "first_trustee_ellipsis" BOOLEAN, 1.289 + "other_trustee_id" INT4, 1.290 + "other_trustee_participation" BOOLEAN, 1.291 + "other_trustee_ellipsis" BOOLEAN, 1.292 + "delegation_loop" "delegation_info_loop_type"); 1.293 + 1.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'; 1.295 + 1.296 +COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating'; 1.297 +COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member'; 1.298 +COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member'; 1.299 +COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating'; 1.300 +COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"'; 1.301 +COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)'; 1.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)'; 1.303 +COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"'; 1.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'; 1.305 + 1.306 +CREATE FUNCTION "delegation_info" 1.307 + ( "member_id_p" "member"."id"%TYPE, 1.308 + "unit_id_p" "unit"."id"%TYPE, 1.309 + "area_id_p" "area"."id"%TYPE, 1.310 + "issue_id_p" "issue"."id"%TYPE, 1.311 + "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL ) 1.312 + RETURNS "delegation_info_type" 1.313 + LANGUAGE 'plpgsql' STABLE AS $$ 1.314 + DECLARE 1.315 + "current_row" "delegation_chain_row"; 1.316 + "result" "delegation_info_type"; 1.317 + BEGIN 1.318 + "result"."own_participation" := FALSE; 1.319 + FOR "current_row" IN 1.320 + SELECT * FROM "delegation_chain"( 1.321 + "member_id_p", 1.322 + "unit_id_p", "area_id_p", "issue_id_p", 1.323 + "simulate_trustee_id_p") 1.324 + LOOP 1.325 + IF "current_row"."member_id" = "member_id_p" THEN 1.326 + "result"."own_participation" := "current_row"."participation"; 1.327 + "result"."own_delegation_scope" := "current_row"."scope_out"; 1.328 + IF "current_row"."loop" = 'first' THEN 1.329 + "result"."delegation_loop" := 'own'; 1.330 + END IF; 1.331 + ELSIF 1.332 + "current_row"."member_valid" AND 1.333 + ( "current_row"."loop" ISNULL OR 1.334 + "current_row"."loop" != 'repetition' ) 1.335 + THEN 1.336 + IF "result"."first_trustee_id" ISNULL THEN 1.337 + "result"."first_trustee_id" := "current_row"."member_id"; 1.338 + "result"."first_trustee_participation" := "current_row"."participation"; 1.339 + "result"."first_trustee_ellipsis" := FALSE; 1.340 + IF "current_row"."loop" = 'first' THEN 1.341 + "result"."delegation_loop" := 'first'; 1.342 + END IF; 1.343 + ELSIF "result"."other_trustee_id" ISNULL THEN 1.344 + IF "current_row"."participation" THEN 1.345 + "result"."other_trustee_id" := "current_row"."member_id"; 1.346 + "result"."other_trustee_participation" := TRUE; 1.347 + "result"."other_trustee_ellipsis" := FALSE; 1.348 + IF "current_row"."loop" = 'first' THEN 1.349 + "result"."delegation_loop" := 'other'; 1.350 + END IF; 1.351 + ELSE 1.352 + "result"."first_trustee_ellipsis" := TRUE; 1.353 + IF "current_row"."loop" = 'first' THEN 1.354 + "result"."delegation_loop" := 'first_ellipsis'; 1.355 + END IF; 1.356 + END IF; 1.357 + ELSE 1.358 + "result"."other_trustee_ellipsis" := TRUE; 1.359 + IF "current_row"."loop" = 'first' THEN 1.360 + "result"."delegation_loop" := 'other_ellipsis'; 1.361 + END IF; 1.362 + END IF; 1.363 + END IF; 1.364 + END LOOP; 1.365 + RETURN "result"; 1.366 + END; 1.367 + $$; 1.368 + 1.369 +COMMENT ON FUNCTION "delegation_info" 1.370 + ( "member"."id"%TYPE, 1.371 + "unit"."id"%TYPE, 1.372 + "area"."id"%TYPE, 1.373 + "issue"."id"%TYPE, 1.374 + "member"."id"%TYPE ) 1.375 + IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information'; 1.376 + 1.377 +COMMIT;