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  

Impressum / About Us