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    $$;

Impressum / About Us