liquid_feedback_core
changeset 240:1930aaf3cad5
New function "delegation_info"
author | jbe |
---|---|
date | Thu May 10 03:09:58 2012 +0200 (2012-05-10) |
parents | fc5d731ce23a |
children | 8d2d92e83605 |
files | core.sql |
line diff
1.1 --- a/core.sql Thu Mar 15 15:20:20 2012 +0100 1.2 +++ b/core.sql Thu May 10 03:09:58 2012 +0200 1.3 @@ -2596,6 +2596,132 @@ 1.4 1.5 1.6 ------------------------------ 1.7 +-- Delegation info function -- 1.8 +------------------------------ 1.9 + 1.10 + 1.11 +CREATE TYPE "delegation_info_type" AS ( 1.12 + "own_participation" BOOLEAN, 1.13 + "first_trustee_id" INT4, 1.14 + "first_trustee_participation" BOOLEAN, 1.15 + "first_trustee_ellipsis" BOOLEAN, 1.16 + "other_trustee_id" INT4, 1.17 + "other_trustee_participation" BOOLEAN, 1.18 + "other_trustee_ellipsis" BOOLEAN ); 1.19 + 1.20 +CREATE FUNCTION "delegation_info" 1.21 + ( "member_id_p" "member"."id"%TYPE, 1.22 + "issue_id_p" "issue"."id"%TYPE ) 1.23 + RETURNS "delegation_info_type" 1.24 + LANGUAGE 'plpgsql' STABLE AS $$ 1.25 + DECLARE 1.26 + "issue_row" "issue"%ROWTYPE; 1.27 + "current_row" "delegation_chain_row"; 1.28 + "direct_voter_row" "direct_voter"%ROWTYPE; 1.29 + "delegating_voter_row" "delegating_voter"%ROWTYPE; 1.30 + "result" "delegation_info_type"; 1.31 + BEGIN 1.32 + SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p"; 1.33 + IF 1.34 + "issue_row"."fully_frozen" ISNULL AND 1.35 + "issue_row"."closed" ISNULL 1.36 + THEN 1.37 + FOR "current_row" IN 1.38 + SELECT * 1.39 + FROM "delegation_chain"("member_id_p", NULL, NULL, "issue_id_p") 1.40 + LOOP 1.41 + IF 1.42 + "result"."own_participation" ISNULL 1.43 + THEN 1.44 + "result"."own_participation" := "current_row"."participation"; 1.45 + ELSIF 1.46 + "current_row"."member_valid" AND 1.47 + ( "current_row"."loop" ISNULL OR 1.48 + "current_row"."loop" != 'repetition' ) 1.49 + THEN 1.50 + IF "result"."first_trustee_id" ISNULL THEN 1.51 + "result"."first_trustee_id" := "current_row"."member_id"; 1.52 + "result"."first_trustee_participation" := 1.53 + "current_row"."participation" AND 1.54 + NOT "current_row"."overridden"; 1.55 + "result"."first_trustee_ellipsis" := FALSE; 1.56 + ELSIF "result"."other_trustee_id" ISNULL THEN 1.57 + IF 1.58 + "current_row"."participation" AND 1.59 + NOT "current_row"."overridden" 1.60 + THEN 1.61 + "result"."other_trustee_id" := "current_row"."member_id"; 1.62 + "result"."other_trustee_participation" := 1.63 + "current_row"."participation" AND 1.64 + NOT "current_row"."overridden"; 1.65 + "result"."other_trustee_ellipsis" := FALSE; 1.66 + ELSE 1.67 + "result"."first_trustee_ellipsis" := TRUE; 1.68 + END IF; 1.69 + ELSE 1.70 + "result"."other_trustee_ellipsis" := TRUE; 1.71 + END IF; 1.72 + END IF; 1.73 + END LOOP; 1.74 + ELSIF "issue_row"."closed" ISNULL THEN 1.75 + "result"."own_participation" := EXISTS ( 1.76 + SELECT NULL FROM "direct_voter" 1.77 + WHERE "issue_id" = "issue_id_p" AND "member_id" = "member_id_p" 1.78 + ); 1.79 + SELECT INTO "result"."first_trustee_id" "trustee_id" FROM "issue_delegation" 1.80 + WHERE "issue_id" = "issue_id_p" AND "truster_id" = "member_id_p"; 1.81 + IF "result"."first_trustee_id" NOTNULL THEN 1.82 + "result"."first_trustee_participation" := FALSE; 1.83 + "result"."first_trustee_ellipsis" := EXISTS ( 1.84 + SELECT NULL FROM "issue_delegation" 1.85 + WHERE "issue_id" = "issue_id_p" 1.86 + AND "truster_id" = "result"."first_trustee_id" 1.87 + AND "trustee_id" NOTNULL 1.88 + ); 1.89 + END IF; 1.90 + ELSE 1.91 + SELECT INTO "direct_voter_row" * FROM "direct_voter" 1.92 + WHERE "issue_id" = "issue_id_p" AND "member_id" = "member_id_p"; 1.93 + SELECT INTO "delegating_voter_row" * FROM "delegating_voter" 1.94 + WHERE "issue_id" = "issue_id_p" AND "member_id" = "member_id_p"; 1.95 + IF "direct_voter_row"."member_id" NOTNULL THEN 1.96 + "result"."own_participation" := TRUE; 1.97 + ELSIF "delegating_voter_row" NOTNULL THEN 1.98 + "result"."own_participation" := FALSE; 1.99 + IF 1.100 + array_upper("delegating_voter_row"."delegate_member_ids", 1) > 2 1.101 + THEN 1.102 + "result"."first_trustee_id" := "delegating_voter_row"."delegate_member_ids"[1]; 1.103 + "result"."first_trustee_participation" := FALSE; 1.104 + "result"."first_trustee_ellipsis" := TRUE; 1.105 + "result"."other_trustee_id" := "delegating_voter_row"."delegate_member_ids"[array_upper("delegating_voter_row"."delegate_member_ids", 1)]; 1.106 + "result"."other_trustee_participation" := TRUE; 1.107 + "result"."other_trustee_ellipsis" := FALSE; 1.108 + ELSIF 1.109 + array_upper("delegating_voter_row"."delegate_member_ids", 1) = 2 1.110 + THEN 1.111 + "result"."first_trustee_id" := "delegating_voter_row"."delegate_member_ids"[1]; 1.112 + "result"."first_trustee_participation" := FALSE; 1.113 + "result"."first_trustee_ellipsis" := FALSE; 1.114 + "result"."other_trustee_id" := "delegating_voter_row"."delegate_member_ids"[2]; 1.115 + "result"."other_trustee_participation" := TRUE; 1.116 + "result"."other_trustee_ellipsis" := FALSE; 1.117 + ELSE 1.118 + "result"."first_trustee_id" := "delegating_voter_row"."delegate_member_ids"[1]; 1.119 + "result"."first_trustee_participation" := TRUE; 1.120 + "result"."first_trustee_ellipsis" := FALSE; 1.121 + END IF; 1.122 + ELSE 1.123 + "result"."own_participation" := FALSE; 1.124 + END IF; 1.125 + END IF; 1.126 + RETURN "result"; 1.127 + END; 1.128 + $$; 1.129 + 1.130 + 1.131 + 1.132 +------------------------------ 1.133 -- Comparison by vote count -- 1.134 ------------------------------ 1.135