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