liquid_feedback_core
changeset 253:389200fd973d v2.0.10
Added field "participating_member_id" to result of "delegation_info" function
author | jbe |
---|---|
date | Sun May 20 17:20:50 2012 +0200 (2012-05-20) |
parents | 878718ffa8f7 |
children | 5be836255919 |
files | core.sql update/core-update.v2.0.9-v2.0.10.sql |
line diff
1.1 --- a/core.sql Tue May 15 23:02:30 2012 +0200 1.2 +++ b/core.sql Sun May 20 17:20:50 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.9', 2, 0, 9)) 1.8 + SELECT * FROM (VALUES ('2.0.10', 2, 0, 10)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11 1.12 @@ -2664,7 +2664,8 @@ 1.13 "other_trustee_id" INT4, 1.14 "other_trustee_participation" BOOLEAN, 1.15 "other_trustee_ellipsis" BOOLEAN, 1.16 - "delegation_loop" "delegation_info_loop_type"); 1.17 + "delegation_loop" "delegation_info_loop_type", 1.18 + "participating_member_id" INT4 ); 1.19 1.20 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.21 1.22 @@ -2677,6 +2678,7 @@ 1.23 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.24 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"'; 1.25 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.26 +COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain'; 1.27 1.28 1.29 CREATE FUNCTION "delegation_info" 1.30 @@ -2698,6 +2700,12 @@ 1.31 "unit_id_p", "area_id_p", "issue_id_p", 1.32 "simulate_trustee_id_p") 1.33 LOOP 1.34 + IF 1.35 + "result"."participating_member_id" ISNULL AND 1.36 + "current_row"."participation" 1.37 + THEN 1.38 + "result"."participating_member_id" := "current_row"."member_id"; 1.39 + END IF; 1.40 IF "current_row"."member_id" = "member_id_p" THEN 1.41 "result"."own_participation" := "current_row"."participation"; 1.42 "result"."own_delegation_scope" := "current_row"."scope_out";
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 2.2 +++ b/update/core-update.v2.0.9-v2.0.10.sql Sun May 20 17:20:50 2012 +0200 2.3 @@ -0,0 +1,121 @@ 2.4 +BEGIN; 2.5 + 2.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 2.7 + SELECT * FROM (VALUES ('2.0.10', 2, 0, 10)) 2.8 + AS "subquery"("string", "major", "minor", "revision"); 2.9 + 2.10 +DROP FUNCTION "delegation_info" 2.11 + ( "member"."id"%TYPE, 2.12 + "unit"."id"%TYPE, 2.13 + "area"."id"%TYPE, 2.14 + "issue"."id"%TYPE, 2.15 + "member"."id"%TYPE ); 2.16 + 2.17 +DROP TYPE "delegation_info_type"; 2.18 + 2.19 + 2.20 +CREATE TYPE "delegation_info_type" AS ( 2.21 + "own_participation" BOOLEAN, 2.22 + "own_delegation_scope" "delegation_scope", 2.23 + "first_trustee_id" INT4, 2.24 + "first_trustee_participation" BOOLEAN, 2.25 + "first_trustee_ellipsis" BOOLEAN, 2.26 + "other_trustee_id" INT4, 2.27 + "other_trustee_participation" BOOLEAN, 2.28 + "other_trustee_ellipsis" BOOLEAN, 2.29 + "delegation_loop" "delegation_info_loop_type", 2.30 + "participating_member_id" INT4 ); 2.31 + 2.32 +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.33 + 2.34 +COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating'; 2.35 +COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member'; 2.36 +COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member'; 2.37 +COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating'; 2.38 +COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"'; 2.39 +COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)'; 2.40 +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.41 +COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"'; 2.42 +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.43 +COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain'; 2.44 + 2.45 + 2.46 +CREATE FUNCTION "delegation_info" 2.47 + ( "member_id_p" "member"."id"%TYPE, 2.48 + "unit_id_p" "unit"."id"%TYPE, 2.49 + "area_id_p" "area"."id"%TYPE, 2.50 + "issue_id_p" "issue"."id"%TYPE, 2.51 + "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL ) 2.52 + RETURNS "delegation_info_type" 2.53 + LANGUAGE 'plpgsql' STABLE AS $$ 2.54 + DECLARE 2.55 + "current_row" "delegation_chain_row"; 2.56 + "result" "delegation_info_type"; 2.57 + BEGIN 2.58 + "result"."own_participation" := FALSE; 2.59 + FOR "current_row" IN 2.60 + SELECT * FROM "delegation_chain"( 2.61 + "member_id_p", 2.62 + "unit_id_p", "area_id_p", "issue_id_p", 2.63 + "simulate_trustee_id_p") 2.64 + LOOP 2.65 + IF 2.66 + "result"."participating_member_id" ISNULL AND 2.67 + "current_row"."participation" 2.68 + THEN 2.69 + "result"."participating_member_id" := "current_row"."member_id"; 2.70 + END IF; 2.71 + IF "current_row"."member_id" = "member_id_p" THEN 2.72 + "result"."own_participation" := "current_row"."participation"; 2.73 + "result"."own_delegation_scope" := "current_row"."scope_out"; 2.74 + IF "current_row"."loop" = 'first' THEN 2.75 + "result"."delegation_loop" := 'own'; 2.76 + END IF; 2.77 + ELSIF 2.78 + "current_row"."member_valid" AND 2.79 + ( "current_row"."loop" ISNULL OR 2.80 + "current_row"."loop" != 'repetition' ) 2.81 + THEN 2.82 + IF "result"."first_trustee_id" ISNULL THEN 2.83 + "result"."first_trustee_id" := "current_row"."member_id"; 2.84 + "result"."first_trustee_participation" := "current_row"."participation"; 2.85 + "result"."first_trustee_ellipsis" := FALSE; 2.86 + IF "current_row"."loop" = 'first' THEN 2.87 + "result"."delegation_loop" := 'first'; 2.88 + END IF; 2.89 + ELSIF "result"."other_trustee_id" ISNULL THEN 2.90 + IF "current_row"."participation" AND NOT "current_row"."overridden" THEN 2.91 + "result"."other_trustee_id" := "current_row"."member_id"; 2.92 + "result"."other_trustee_participation" := TRUE; 2.93 + "result"."other_trustee_ellipsis" := FALSE; 2.94 + IF "current_row"."loop" = 'first' THEN 2.95 + "result"."delegation_loop" := 'other'; 2.96 + END IF; 2.97 + ELSE 2.98 + "result"."first_trustee_ellipsis" := TRUE; 2.99 + IF "current_row"."loop" = 'first' THEN 2.100 + "result"."delegation_loop" := 'first_ellipsis'; 2.101 + END IF; 2.102 + END IF; 2.103 + ELSE 2.104 + "result"."other_trustee_ellipsis" := TRUE; 2.105 + IF "current_row"."loop" = 'first' THEN 2.106 + "result"."delegation_loop" := 'other_ellipsis'; 2.107 + END IF; 2.108 + END IF; 2.109 + END IF; 2.110 + END LOOP; 2.111 + RETURN "result"; 2.112 + END; 2.113 + $$; 2.114 + 2.115 +COMMENT ON FUNCTION "delegation_info" 2.116 + ( "member"."id"%TYPE, 2.117 + "unit"."id"%TYPE, 2.118 + "area"."id"%TYPE, 2.119 + "issue"."id"%TYPE, 2.120 + "member"."id"%TYPE ) 2.121 + IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information'; 2.122 + 2.123 + 2.124 +COMMIT;