liquid_feedback_core

changeset 256:e818f83e133b v2.0.11

Added update script from v2.0.10 to v2.0.11
author jbe
date Wed Jun 20 21:22:02 2012 +0200 (2012-06-20)
parents a1db85ce10ea
children 29538d1ea21e 69d6fba0f84c a839e7efde9f
files update/core-update.v2.0.10-v2.0.11.sql
line diff
     1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/update/core-update.v2.0.10-v2.0.11.sql	Wed Jun 20 21:22:02 2012 +0200
     1.3 @@ -0,0 +1,341 @@
     1.4 +BEGIN;
     1.5 +
     1.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     1.7 +  SELECT * FROM (VALUES ('2.0.11', 2, 0, 11))
     1.8 +  AS "subquery"("string", "major", "minor", "revision");
     1.9 +
    1.10 +DROP FUNCTION "delegation_info"
    1.11 +  ( "member"."id"%TYPE,
    1.12 +    "unit"."id"%TYPE,
    1.13 +    "area"."id"%TYPE,
    1.14 +    "issue"."id"%TYPE,
    1.15 +    "member"."id"%TYPE );
    1.16 +
    1.17 +DROP FUNCTION "delegation_chain"
    1.18 +  ( "member"."id"%TYPE,
    1.19 +    "unit"."id"%TYPE,
    1.20 +    "area"."id"%TYPE,
    1.21 +    "issue"."id"%TYPE,
    1.22 +    "member"."id"%TYPE );
    1.23 +
    1.24 +CREATE FUNCTION "delegation_chain"
    1.25 +  ( "member_id_p"           "member"."id"%TYPE,
    1.26 +    "unit_id_p"             "unit"."id"%TYPE,
    1.27 +    "area_id_p"             "area"."id"%TYPE,
    1.28 +    "issue_id_p"            "issue"."id"%TYPE,
    1.29 +    "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
    1.30 +    "simulate_default_p"    BOOLEAN            DEFAULT FALSE )
    1.31 +  RETURNS SETOF "delegation_chain_row"
    1.32 +  LANGUAGE 'plpgsql' STABLE AS $$
    1.33 +    DECLARE
    1.34 +      "scope_v"            "delegation_scope";
    1.35 +      "unit_id_v"          "unit"."id"%TYPE;
    1.36 +      "area_id_v"          "area"."id"%TYPE;
    1.37 +      "issue_row"          "issue"%ROWTYPE;
    1.38 +      "visited_member_ids" INT4[];  -- "member"."id"%TYPE[]
    1.39 +      "loop_member_id_v"   "member"."id"%TYPE;
    1.40 +      "output_row"         "delegation_chain_row";
    1.41 +      "output_rows"        "delegation_chain_row"[];
    1.42 +      "simulate_v"         BOOLEAN;
    1.43 +      "simulate_here_v"    BOOLEAN;
    1.44 +      "delegation_row"     "delegation"%ROWTYPE;
    1.45 +      "row_count"          INT4;
    1.46 +      "i"                  INT4;
    1.47 +      "loop_v"             BOOLEAN;
    1.48 +    BEGIN
    1.49 +      IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
    1.50 +        RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
    1.51 +      END IF;
    1.52 +      IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
    1.53 +        "simulate_v" := TRUE;
    1.54 +      ELSE
    1.55 +        "simulate_v" := FALSE;
    1.56 +      END IF;
    1.57 +      IF
    1.58 +        "unit_id_p" NOTNULL AND
    1.59 +        "area_id_p" ISNULL AND
    1.60 +        "issue_id_p" ISNULL
    1.61 +      THEN
    1.62 +        "scope_v" := 'unit';
    1.63 +        "unit_id_v" := "unit_id_p";
    1.64 +      ELSIF
    1.65 +        "unit_id_p" ISNULL AND
    1.66 +        "area_id_p" NOTNULL AND
    1.67 +        "issue_id_p" ISNULL
    1.68 +      THEN
    1.69 +        "scope_v" := 'area';
    1.70 +        "area_id_v" := "area_id_p";
    1.71 +        SELECT "unit_id" INTO "unit_id_v"
    1.72 +          FROM "area" WHERE "id" = "area_id_v";
    1.73 +      ELSIF
    1.74 +        "unit_id_p" ISNULL AND
    1.75 +        "area_id_p" ISNULL AND
    1.76 +        "issue_id_p" NOTNULL
    1.77 +      THEN
    1.78 +        SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
    1.79 +        IF "issue_row"."id" ISNULL THEN
    1.80 +          RETURN;
    1.81 +        END IF;
    1.82 +        IF "issue_row"."closed" NOTNULL THEN
    1.83 +          IF "simulate_v" THEN
    1.84 +            RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
    1.85 +          END IF;
    1.86 +          FOR "output_row" IN
    1.87 +            SELECT * FROM
    1.88 +            "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
    1.89 +          LOOP
    1.90 +            RETURN NEXT "output_row";
    1.91 +          END LOOP;
    1.92 +          RETURN;
    1.93 +        END IF;
    1.94 +        "scope_v" := 'issue';
    1.95 +        SELECT "area_id" INTO "area_id_v"
    1.96 +          FROM "issue" WHERE "id" = "issue_id_p";
    1.97 +        SELECT "unit_id" INTO "unit_id_v"
    1.98 +          FROM "area"  WHERE "id" = "area_id_v";
    1.99 +      ELSE
   1.100 +        RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
   1.101 +      END IF;
   1.102 +      "visited_member_ids" := '{}';
   1.103 +      "loop_member_id_v"   := NULL;
   1.104 +      "output_rows"        := '{}';
   1.105 +      "output_row"."index"         := 0;
   1.106 +      "output_row"."member_id"     := "member_id_p";
   1.107 +      "output_row"."member_valid"  := TRUE;
   1.108 +      "output_row"."participation" := FALSE;
   1.109 +      "output_row"."overridden"    := FALSE;
   1.110 +      "output_row"."disabled_out"  := FALSE;
   1.111 +      "output_row"."scope_out"     := NULL;
   1.112 +      LOOP
   1.113 +        IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
   1.114 +          "loop_member_id_v" := "output_row"."member_id";
   1.115 +        ELSE
   1.116 +          "visited_member_ids" :=
   1.117 +            "visited_member_ids" || "output_row"."member_id";
   1.118 +        END IF;
   1.119 +        IF "output_row"."participation" ISNULL THEN
   1.120 +          "output_row"."overridden" := NULL;
   1.121 +        ELSIF "output_row"."participation" THEN
   1.122 +          "output_row"."overridden" := TRUE;
   1.123 +        END IF;
   1.124 +        "output_row"."scope_in" := "output_row"."scope_out";
   1.125 +        "output_row"."member_valid" := EXISTS (
   1.126 +          SELECT NULL FROM "member" JOIN "privilege"
   1.127 +          ON "privilege"."member_id" = "member"."id"
   1.128 +          AND "privilege"."unit_id" = "unit_id_v"
   1.129 +          WHERE "id" = "output_row"."member_id"
   1.130 +          AND "member"."active" AND "privilege"."voting_right"
   1.131 +        );
   1.132 +        "simulate_here_v" := (
   1.133 +          "simulate_v" AND
   1.134 +          "output_row"."member_id" = "member_id_p"
   1.135 +        );
   1.136 +        "delegation_row" := ROW(NULL);
   1.137 +        IF "output_row"."member_valid" OR "simulate_here_v" THEN
   1.138 +          IF "scope_v" = 'unit' THEN
   1.139 +            IF NOT "simulate_here_v" THEN
   1.140 +              SELECT * INTO "delegation_row" FROM "delegation"
   1.141 +                WHERE "truster_id" = "output_row"."member_id"
   1.142 +                AND "unit_id" = "unit_id_v";
   1.143 +            END IF;
   1.144 +          ELSIF "scope_v" = 'area' THEN
   1.145 +            "output_row"."participation" := EXISTS (
   1.146 +              SELECT NULL FROM "membership"
   1.147 +              WHERE "area_id" = "area_id_p"
   1.148 +              AND "member_id" = "output_row"."member_id"
   1.149 +            );
   1.150 +            IF "simulate_here_v" THEN
   1.151 +              IF "simulate_trustee_id_p" ISNULL THEN
   1.152 +                SELECT * INTO "delegation_row" FROM "delegation"
   1.153 +                  WHERE "truster_id" = "output_row"."member_id"
   1.154 +                  AND "unit_id" = "unit_id_v";
   1.155 +              END IF;
   1.156 +            ELSE
   1.157 +              SELECT * INTO "delegation_row" FROM "delegation"
   1.158 +                WHERE "truster_id" = "output_row"."member_id"
   1.159 +                AND (
   1.160 +                  "unit_id" = "unit_id_v" OR
   1.161 +                  "area_id" = "area_id_v"
   1.162 +                )
   1.163 +                ORDER BY "scope" DESC;
   1.164 +            END IF;
   1.165 +          ELSIF "scope_v" = 'issue' THEN
   1.166 +            IF "issue_row"."fully_frozen" ISNULL THEN
   1.167 +              "output_row"."participation" := EXISTS (
   1.168 +                SELECT NULL FROM "interest"
   1.169 +                WHERE "issue_id" = "issue_id_p"
   1.170 +                AND "member_id" = "output_row"."member_id"
   1.171 +              );
   1.172 +            ELSE
   1.173 +              IF "output_row"."member_id" = "member_id_p" THEN
   1.174 +                "output_row"."participation" := EXISTS (
   1.175 +                  SELECT NULL FROM "direct_voter"
   1.176 +                  WHERE "issue_id" = "issue_id_p"
   1.177 +                  AND "member_id" = "output_row"."member_id"
   1.178 +                );
   1.179 +              ELSE
   1.180 +                "output_row"."participation" := NULL;
   1.181 +              END IF;
   1.182 +            END IF;
   1.183 +            IF "simulate_here_v" THEN
   1.184 +              IF "simulate_trustee_id_p" ISNULL THEN
   1.185 +                SELECT * INTO "delegation_row" FROM "delegation"
   1.186 +                  WHERE "truster_id" = "output_row"."member_id"
   1.187 +                  AND (
   1.188 +                    "unit_id" = "unit_id_v" OR
   1.189 +                    "area_id" = "area_id_v"
   1.190 +                  )
   1.191 +                  ORDER BY "scope" DESC;
   1.192 +              END IF;
   1.193 +            ELSE
   1.194 +              SELECT * INTO "delegation_row" FROM "delegation"
   1.195 +                WHERE "truster_id" = "output_row"."member_id"
   1.196 +                AND (
   1.197 +                  "unit_id" = "unit_id_v" OR
   1.198 +                  "area_id" = "area_id_v" OR
   1.199 +                  "issue_id" = "issue_id_p"
   1.200 +                )
   1.201 +                ORDER BY "scope" DESC;
   1.202 +            END IF;
   1.203 +          END IF;
   1.204 +        ELSE
   1.205 +          "output_row"."participation" := FALSE;
   1.206 +        END IF;
   1.207 +        IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
   1.208 +          "output_row"."scope_out" := "scope_v";
   1.209 +          "output_rows" := "output_rows" || "output_row";
   1.210 +          "output_row"."member_id" := "simulate_trustee_id_p";
   1.211 +        ELSIF "delegation_row"."trustee_id" NOTNULL THEN
   1.212 +          "output_row"."scope_out" := "delegation_row"."scope";
   1.213 +          "output_rows" := "output_rows" || "output_row";
   1.214 +          "output_row"."member_id" := "delegation_row"."trustee_id";
   1.215 +        ELSIF "delegation_row"."scope" NOTNULL THEN
   1.216 +          "output_row"."scope_out" := "delegation_row"."scope";
   1.217 +          "output_row"."disabled_out" := TRUE;
   1.218 +          "output_rows" := "output_rows" || "output_row";
   1.219 +          EXIT;
   1.220 +        ELSE
   1.221 +          "output_row"."scope_out" := NULL;
   1.222 +          "output_rows" := "output_rows" || "output_row";
   1.223 +          EXIT;
   1.224 +        END IF;
   1.225 +        EXIT WHEN "loop_member_id_v" NOTNULL;
   1.226 +        "output_row"."index" := "output_row"."index" + 1;
   1.227 +      END LOOP;
   1.228 +      "row_count" := array_upper("output_rows", 1);
   1.229 +      "i"      := 1;
   1.230 +      "loop_v" := FALSE;
   1.231 +      LOOP
   1.232 +        "output_row" := "output_rows"["i"];
   1.233 +        EXIT WHEN "output_row" ISNULL;  -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
   1.234 +        IF "loop_v" THEN
   1.235 +          IF "i" + 1 = "row_count" THEN
   1.236 +            "output_row"."loop" := 'last';
   1.237 +          ELSIF "i" = "row_count" THEN
   1.238 +            "output_row"."loop" := 'repetition';
   1.239 +          ELSE
   1.240 +            "output_row"."loop" := 'intermediate';
   1.241 +          END IF;
   1.242 +        ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
   1.243 +          "output_row"."loop" := 'first';
   1.244 +          "loop_v" := TRUE;
   1.245 +        END IF;
   1.246 +        IF "scope_v" = 'unit' THEN
   1.247 +          "output_row"."participation" := NULL;
   1.248 +        END IF;
   1.249 +        RETURN NEXT "output_row";
   1.250 +        "i" := "i" + 1;
   1.251 +      END LOOP;
   1.252 +      RETURN;
   1.253 +    END;
   1.254 +  $$;
   1.255 +
   1.256 +COMMENT ON FUNCTION "delegation_chain"
   1.257 +  ( "member"."id"%TYPE,
   1.258 +    "unit"."id"%TYPE,
   1.259 +    "area"."id"%TYPE,
   1.260 +    "issue"."id"%TYPE,
   1.261 +    "member"."id"%TYPE,
   1.262 +    BOOLEAN )
   1.263 +  IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
   1.264 +
   1.265 +CREATE FUNCTION "delegation_info"
   1.266 +  ( "member_id_p"           "member"."id"%TYPE,
   1.267 +    "unit_id_p"             "unit"."id"%TYPE,
   1.268 +    "area_id_p"             "area"."id"%TYPE,
   1.269 +    "issue_id_p"            "issue"."id"%TYPE,
   1.270 +    "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
   1.271 +    "simulate_default_p"    BOOLEAN            DEFAULT FALSE )
   1.272 +  RETURNS "delegation_info_type"
   1.273 +  LANGUAGE 'plpgsql' STABLE AS $$
   1.274 +    DECLARE
   1.275 +      "current_row" "delegation_chain_row";
   1.276 +      "result"      "delegation_info_type";
   1.277 +    BEGIN
   1.278 +      "result"."own_participation" := FALSE;
   1.279 +      FOR "current_row" IN
   1.280 +        SELECT * FROM "delegation_chain"(
   1.281 +          "member_id_p",
   1.282 +          "unit_id_p", "area_id_p", "issue_id_p",
   1.283 +          "simulate_trustee_id_p", "simulate_default_p")
   1.284 +      LOOP
   1.285 +        IF
   1.286 +          "result"."participating_member_id" ISNULL AND
   1.287 +          "current_row"."participation"
   1.288 +        THEN
   1.289 +          "result"."participating_member_id" := "current_row"."member_id";
   1.290 +        END IF;
   1.291 +        IF "current_row"."member_id" = "member_id_p" THEN
   1.292 +          "result"."own_participation"    := "current_row"."participation";
   1.293 +          "result"."own_delegation_scope" := "current_row"."scope_out";
   1.294 +          IF "current_row"."loop" = 'first' THEN
   1.295 +            "result"."delegation_loop" := 'own';
   1.296 +          END IF;
   1.297 +        ELSIF
   1.298 +          "current_row"."member_valid" AND
   1.299 +          ( "current_row"."loop" ISNULL OR
   1.300 +            "current_row"."loop" != 'repetition' )
   1.301 +        THEN
   1.302 +          IF "result"."first_trustee_id" ISNULL THEN
   1.303 +            "result"."first_trustee_id"            := "current_row"."member_id";
   1.304 +            "result"."first_trustee_participation" := "current_row"."participation";
   1.305 +            "result"."first_trustee_ellipsis"      := FALSE;
   1.306 +            IF "current_row"."loop" = 'first' THEN
   1.307 +              "result"."delegation_loop" := 'first';
   1.308 +            END IF;
   1.309 +          ELSIF "result"."other_trustee_id" ISNULL THEN
   1.310 +            IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
   1.311 +              "result"."other_trustee_id"            := "current_row"."member_id";
   1.312 +              "result"."other_trustee_participation" := TRUE;
   1.313 +              "result"."other_trustee_ellipsis"      := FALSE;
   1.314 +              IF "current_row"."loop" = 'first' THEN
   1.315 +                "result"."delegation_loop" := 'other';
   1.316 +              END IF;
   1.317 +            ELSE
   1.318 +              "result"."first_trustee_ellipsis" := TRUE;
   1.319 +              IF "current_row"."loop" = 'first' THEN
   1.320 +                "result"."delegation_loop" := 'first_ellipsis';
   1.321 +              END IF;
   1.322 +            END IF;
   1.323 +          ELSE
   1.324 +            "result"."other_trustee_ellipsis" := TRUE;
   1.325 +            IF "current_row"."loop" = 'first' THEN
   1.326 +              "result"."delegation_loop" := 'other_ellipsis';
   1.327 +            END IF;
   1.328 +          END IF;
   1.329 +        END IF;
   1.330 +      END LOOP;
   1.331 +      RETURN "result";
   1.332 +    END;
   1.333 +  $$;
   1.334 +
   1.335 +COMMENT ON FUNCTION "delegation_info"
   1.336 +  ( "member"."id"%TYPE,
   1.337 +    "unit"."id"%TYPE,
   1.338 +    "area"."id"%TYPE,
   1.339 +    "issue"."id"%TYPE,
   1.340 +    "member"."id"%TYPE,
   1.341 +    BOOLEAN )
   1.342 +  IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
   1.343 +
   1.344 +COMMIT;

Impressum / About Us