liquid_feedback_core
changeset 242:0c5ddf9b4b20
Support closed issues in "delegation_chain" function; Work on "delegation_info" function
author | jbe |
---|---|
date | Thu May 10 15:07:00 2012 +0200 (2012-05-10) |
parents | 8d2d92e83605 |
children | d2642db42ad0 |
files | core.sql |
line diff
1.1 --- a/core.sql Thu May 10 13:55:35 2012 +0200 1.2 +++ b/core.sql Thu May 10 15:07:00 2012 +0200 1.3 @@ -2355,9 +2355,9 @@ 1.4 1.5 1.6 1.7 --------------------------------------------------- 1.8 --- Set returning function for delegation chains -- 1.9 --------------------------------------------------- 1.10 +------------------------------------------------------ 1.11 +-- Row set returning function for delegation chains -- 1.12 +------------------------------------------------------ 1.13 1.14 1.15 CREATE TYPE "delegation_chain_loop_tag" AS ENUM 1.16 @@ -2388,12 +2388,59 @@ 1.17 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type'; 1.18 1.19 1.20 +CREATE FUNCTION "delegation_chain_for_closed_issue" 1.21 + ( "member_id_p" "member"."id"%TYPE, 1.22 + "issue_id_p" "issue"."id"%TYPE ) 1.23 + RETURNS SETOF "delegation_chain_row" 1.24 + LANGUAGE 'plpgsql' STABLE AS $$ 1.25 + DECLARE 1.26 + "output_row" "delegation_chain_row"; 1.27 + "direct_voter_row" "direct_voter"%ROWTYPE; 1.28 + "delegating_voter_row" "delegating_voter"%ROWTYPE; 1.29 + BEGIN 1.30 + "output_row"."index" := 0; 1.31 + "output_row"."member_id" := "member_id_p"; 1.32 + "output_row"."member_valid" := TRUE; 1.33 + "output_row"."participation" := FALSE; 1.34 + "output_row"."overridden" := FALSE; 1.35 + "output_row"."disabled_out" := FALSE; 1.36 + LOOP 1.37 + SELECT INTO "direct_voter_row" * FROM "direct_voter" 1.38 + WHERE "issue_id" = "issue_id_p" 1.39 + AND "member_id" = "output_row"."member_id"; 1.40 + IF "direct_voter_row"."member_id" NOTNULL THEN 1.41 + "output_row"."participation" := TRUE; 1.42 + "output_row"."scope_out" := NULL; 1.43 + "output_row"."disabled_out" := NULL; 1.44 + RETURN NEXT "output_row"; 1.45 + RETURN; 1.46 + END IF; 1.47 + SELECT INTO "delegating_voter_row" * FROM "delegating_voter" 1.48 + WHERE "issue_id" = "issue_id_p" 1.49 + AND "member_id" = "output_row"."member_id"; 1.50 + IF "delegating_voter_row"."member_id" ISNULL THEN 1.51 + RETURN; 1.52 + END IF; 1.53 + "output_row"."scope_out" := "delegating_voter_row"."scope"; 1.54 + RETURN NEXT "output_row"; 1.55 + "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1]; 1.56 + "output_row"."scope_in" := "output_row"."scope_out"; 1.57 + END LOOP; 1.58 + END; 1.59 + $$; 1.60 + 1.61 +COMMENT ON FUNCTION "delegation_chain_for_closed_issue" 1.62 + ( "member"."id"%TYPE, 1.63 + "member"."id"%TYPE ) 1.64 + IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting'; 1.65 + 1.66 + 1.67 CREATE FUNCTION "delegation_chain" 1.68 ( "member_id_p" "member"."id"%TYPE, 1.69 "unit_id_p" "unit"."id"%TYPE, 1.70 "area_id_p" "area"."id"%TYPE, 1.71 "issue_id_p" "issue"."id"%TYPE, 1.72 - "simulate_trustee_id_p" "member"."id"%TYPE ) 1.73 + "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL ) 1.74 RETURNS SETOF "delegation_chain_row" 1.75 LANGUAGE 'plpgsql' STABLE AS $$ 1.76 DECLARE 1.77 @@ -2431,9 +2478,23 @@ 1.78 "area_id_p" ISNULL AND 1.79 "issue_id_p" NOTNULL 1.80 THEN 1.81 + SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p"; 1.82 + IF "issue_row"."id" ISNULL THEN 1.83 + RETURN; 1.84 + END IF; 1.85 + IF "issue_row"."closed" NOTNULL THEN 1.86 + IF "simulate_trustee_id_p" NOTNULL THEN 1.87 + RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.'; 1.88 + END IF; 1.89 + FOR "output_row" IN 1.90 + SELECT * FROM 1.91 + "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p") 1.92 + LOOP 1.93 + RETURN NEXT "output_row"; 1.94 + END LOOP; 1.95 + RETURN; 1.96 + END IF; 1.97 "scope_v" := 'issue'; 1.98 - SELECT INTO "issue_row" * FROM "issue" 1.99 - WHERE "id" = "issue_id_p"; 1.100 SELECT "area_id" INTO "area_id_v" 1.101 FROM "issue" WHERE "id" = "issue_id_p"; 1.102 SELECT "unit_id" INTO "unit_id_v" 1.103 @@ -2579,159 +2640,88 @@ 1.104 "area"."id"%TYPE, 1.105 "issue"."id"%TYPE, 1.106 "member"."id"%TYPE ) 1.107 - IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic'; 1.108 - 1.109 - 1.110 -CREATE FUNCTION "delegation_chain" 1.111 - ( "member_id_p" "member"."id"%TYPE, 1.112 - "unit_id_p" "unit"."id"%TYPE, 1.113 - "area_id_p" "area"."id"%TYPE, 1.114 - "issue_id_p" "issue"."id"%TYPE ) 1.115 - RETURNS SETOF "delegation_chain_row" 1.116 - LANGUAGE 'plpgsql' STABLE AS $$ 1.117 - DECLARE 1.118 - "result_row" "delegation_chain_row"; 1.119 - BEGIN 1.120 - FOR "result_row" IN 1.121 - SELECT * FROM "delegation_chain"( 1.122 - "member_id_p", "unit_id_p", "area_id_p", "issue_id_p", NULL 1.123 - ) 1.124 - LOOP 1.125 - RETURN NEXT "result_row"; 1.126 - END LOOP; 1.127 - RETURN; 1.128 - END; 1.129 - $$; 1.130 - 1.131 -COMMENT ON FUNCTION "delegation_chain" 1.132 - ( "member"."id"%TYPE, 1.133 - "unit"."id"%TYPE, 1.134 - "area"."id"%TYPE, 1.135 - "issue"."id"%TYPE ) 1.136 - IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null'; 1.137 - 1.138 - 1.139 - 1.140 ------------------------------- 1.141 --- Delegation info function -- 1.142 ------------------------------- 1.143 - 1.144 + IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information'; 1.145 + 1.146 + 1.147 + 1.148 +--------------------------------------------------------- 1.149 +-- Single row returning function for delegation chains -- 1.150 +--------------------------------------------------------- 1.151 + 1.152 + 1.153 +CREATE TYPE "delegation_info_loop_type" AS ENUM 1.154 + ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis'); 1.155 1.156 CREATE TYPE "delegation_info_type" AS ( 1.157 - "own_participation" BOOLEAN, 1.158 - "first_trustee_id" INT4, 1.159 + "own_participation" BOOLEAN, 1.160 + "own_delegation_scope" "delegation_scope", 1.161 + "first_trustee_id" INT4, 1.162 "first_trustee_participation" BOOLEAN, 1.163 - "first_trustee_ellipsis" BOOLEAN, 1.164 - "other_trustee_id" INT4, 1.165 + "first_trustee_ellipsis" BOOLEAN, 1.166 + "other_trustee_id" INT4, 1.167 "other_trustee_participation" BOOLEAN, 1.168 - "other_trustee_ellipsis" BOOLEAN ); 1.169 + "other_trustee_ellipsis" BOOLEAN, 1.170 + "delegation_loop" "delegation_info_loop_type"); 1.171 1.172 CREATE FUNCTION "delegation_info" 1.173 - ( "member_id_p" "member"."id"%TYPE, 1.174 - "issue_id_p" "issue"."id"%TYPE ) 1.175 + ( "member_id_p" "member"."id"%TYPE, 1.176 + "unit_id_p" "unit"."id"%TYPE, 1.177 + "area_id_p" "area"."id"%TYPE, 1.178 + "issue_id_p" "issue"."id"%TYPE, 1.179 + "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL ) 1.180 RETURNS "delegation_info_type" 1.181 LANGUAGE 'plpgsql' STABLE AS $$ 1.182 DECLARE 1.183 - "issue_row" "issue"%ROWTYPE; 1.184 - "current_row" "delegation_chain_row"; 1.185 - "direct_voter_row" "direct_voter"%ROWTYPE; 1.186 - "delegating_voter_row" "delegating_voter"%ROWTYPE; 1.187 - "result" "delegation_info_type"; 1.188 + "current_row" "delegation_chain_row"; 1.189 + "result" "delegation_info_type"; 1.190 BEGIN 1.191 - SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p"; 1.192 - IF 1.193 - "issue_row"."fully_frozen" ISNULL AND 1.194 - "issue_row"."closed" ISNULL 1.195 - THEN 1.196 - FOR "current_row" IN 1.197 - SELECT * 1.198 - FROM "delegation_chain"("member_id_p", NULL, NULL, "issue_id_p") 1.199 - LOOP 1.200 - IF 1.201 - "result"."own_participation" ISNULL 1.202 - THEN 1.203 - "result"."own_participation" := "current_row"."participation"; 1.204 - ELSIF 1.205 - "current_row"."member_valid" AND 1.206 - ( "current_row"."loop" ISNULL OR 1.207 - "current_row"."loop" != 'repetition' ) 1.208 - THEN 1.209 - IF "result"."first_trustee_id" ISNULL THEN 1.210 - "result"."first_trustee_id" := "current_row"."member_id"; 1.211 - "result"."first_trustee_participation" := 1.212 - "current_row"."participation" AND 1.213 - NOT "current_row"."overridden"; 1.214 - "result"."first_trustee_ellipsis" := FALSE; 1.215 - ELSIF "result"."other_trustee_id" ISNULL THEN 1.216 - IF 1.217 - "current_row"."participation" AND 1.218 - NOT "current_row"."overridden" 1.219 - THEN 1.220 - "result"."other_trustee_id" := "current_row"."member_id"; 1.221 - "result"."other_trustee_participation" := 1.222 - "current_row"."participation" AND 1.223 - NOT "current_row"."overridden"; 1.224 - "result"."other_trustee_ellipsis" := FALSE; 1.225 - ELSE 1.226 - "result"."first_trustee_ellipsis" := TRUE; 1.227 + "result"."own_participation" := FALSE; 1.228 + FOR "current_row" IN 1.229 + SELECT * FROM "delegation_chain"( 1.230 + "member_id_p", 1.231 + "unit_id_p", "area_id_p", "issue_id_p", 1.232 + "simulate_trustee_id_p") 1.233 + LOOP 1.234 + IF "current_row"."member_id" = "member_id_p" THEN 1.235 + "result"."own_participation" := "current_row"."participation"; 1.236 + "result"."own_delegation_scope" := "current_row"."scope_out"; 1.237 + IF "current_row"."loop" = 'first' THEN 1.238 + "result"."delegation_loop" := 'own'; 1.239 + END IF; 1.240 + ELSIF 1.241 + "current_row"."member_valid" AND 1.242 + ( "current_row"."loop" ISNULL OR 1.243 + "current_row"."loop" != 'repetition' ) 1.244 + THEN 1.245 + IF "result"."first_trustee_id" ISNULL THEN 1.246 + "result"."first_trustee_id" := "current_row"."member_id"; 1.247 + "result"."first_trustee_participation" := "current_row"."participation"; 1.248 + "result"."first_trustee_ellipsis" := FALSE; 1.249 + IF "current_row"."loop" = 'first' THEN 1.250 + "result"."delegation_loop" := 'first'; 1.251 + END IF; 1.252 + ELSIF "result"."other_trustee_id" ISNULL THEN 1.253 + IF "current_row"."participation" THEN 1.254 + "result"."other_trustee_id" := "current_row"."member_id"; 1.255 + "result"."other_trustee_participation" := TRUE; 1.256 + "result"."other_trustee_ellipsis" := FALSE; 1.257 + IF "current_row"."loop" = 'first' THEN 1.258 + "result"."delegation_loop" := 'other'; 1.259 END IF; 1.260 ELSE 1.261 - "result"."other_trustee_ellipsis" := TRUE; 1.262 + "result"."first_trustee_ellipsis" := TRUE; 1.263 + IF "current_row"."loop" = 'first' THEN 1.264 + "result"."delegation_loop" := 'first_ellipsis'; 1.265 + END IF; 1.266 + END IF; 1.267 + ELSE 1.268 + "result"."other_trustee_ellipsis" := TRUE; 1.269 + IF "current_row"."loop" = 'first' THEN 1.270 + "result"."delegation_loop" := 'other_ellipsis'; 1.271 END IF; 1.272 END IF; 1.273 - END LOOP; 1.274 - ELSIF "issue_row"."closed" ISNULL THEN 1.275 - "result"."own_participation" := EXISTS ( 1.276 - SELECT NULL FROM "direct_voter" 1.277 - WHERE "issue_id" = "issue_id_p" AND "member_id" = "member_id_p" 1.278 - ); 1.279 - SELECT INTO "result"."first_trustee_id" "trustee_id" FROM "issue_delegation" 1.280 - WHERE "issue_id" = "issue_id_p" AND "truster_id" = "member_id_p"; 1.281 - IF "result"."first_trustee_id" NOTNULL THEN 1.282 - "result"."first_trustee_participation" := FALSE; 1.283 - "result"."first_trustee_ellipsis" := EXISTS ( 1.284 - SELECT NULL FROM "issue_delegation" 1.285 - WHERE "issue_id" = "issue_id_p" 1.286 - AND "truster_id" = "result"."first_trustee_id" 1.287 - AND "trustee_id" NOTNULL 1.288 - ); 1.289 END IF; 1.290 - ELSE 1.291 - SELECT INTO "direct_voter_row" * FROM "direct_voter" 1.292 - WHERE "issue_id" = "issue_id_p" AND "member_id" = "member_id_p"; 1.293 - SELECT INTO "delegating_voter_row" * FROM "delegating_voter" 1.294 - WHERE "issue_id" = "issue_id_p" AND "member_id" = "member_id_p"; 1.295 - IF "direct_voter_row"."member_id" NOTNULL THEN 1.296 - "result"."own_participation" := TRUE; 1.297 - ELSIF "delegating_voter_row" NOTNULL THEN 1.298 - "result"."own_participation" := FALSE; 1.299 - IF 1.300 - array_upper("delegating_voter_row"."delegate_member_ids", 1) > 2 1.301 - THEN 1.302 - "result"."first_trustee_id" := "delegating_voter_row"."delegate_member_ids"[1]; 1.303 - "result"."first_trustee_participation" := FALSE; 1.304 - "result"."first_trustee_ellipsis" := TRUE; 1.305 - "result"."other_trustee_id" := "delegating_voter_row"."delegate_member_ids"[array_upper("delegating_voter_row"."delegate_member_ids", 1)]; 1.306 - "result"."other_trustee_participation" := TRUE; 1.307 - "result"."other_trustee_ellipsis" := FALSE; 1.308 - ELSIF 1.309 - array_upper("delegating_voter_row"."delegate_member_ids", 1) = 2 1.310 - THEN 1.311 - "result"."first_trustee_id" := "delegating_voter_row"."delegate_member_ids"[1]; 1.312 - "result"."first_trustee_participation" := FALSE; 1.313 - "result"."first_trustee_ellipsis" := FALSE; 1.314 - "result"."other_trustee_id" := "delegating_voter_row"."delegate_member_ids"[2]; 1.315 - "result"."other_trustee_participation" := TRUE; 1.316 - "result"."other_trustee_ellipsis" := FALSE; 1.317 - ELSE 1.318 - "result"."first_trustee_id" := "delegating_voter_row"."delegate_member_ids"[1]; 1.319 - "result"."first_trustee_participation" := TRUE; 1.320 - "result"."first_trustee_ellipsis" := FALSE; 1.321 - END IF; 1.322 - ELSE 1.323 - "result"."own_participation" := FALSE; 1.324 - END IF; 1.325 - END IF; 1.326 + END LOOP; 1.327 RETURN "result"; 1.328 END; 1.329 $$;