liquid_feedback_core

changeset 244:d09c17b01c5d

Update script to v2.0.9
author jbe
date Thu May 10 18:32:43 2012 +0200 (2012-05-10)
parents d2642db42ad0
children 8423373fbadd
files core.sql update/core-update.v2.0.8-v2.0.9.sql
line diff
     1.1 --- a/core.sql	Thu May 10 18:24:36 2012 +0200
     1.2 +++ b/core.sql	Thu May 10 18:32:43 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.8', 2, 0, 8))
     1.8 +  SELECT * FROM (VALUES ('2.0.9', 2, 0, 9))
     1.9    AS "subquery"("string", "major", "minor", "revision");
    1.10  
    1.11  
     2.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     2.2 +++ b/update/core-update.v2.0.8-v2.0.9.sql	Thu May 10 18:32:43 2012 +0200
     2.3 @@ -0,0 +1,374 @@
     2.4 +BEGIN;
     2.5 +
     2.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     2.7 +  SELECT * FROM (VALUES ('2.0.9', 2, 0, 9))
     2.8 +  AS "subquery"("string", "major", "minor", "revision");
     2.9 +
    2.10 +COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
    2.11 +
    2.12 +CREATE FUNCTION "delegation_chain_for_closed_issue"
    2.13 +  ( "member_id_p"           "member"."id"%TYPE,
    2.14 +    "issue_id_p"            "issue"."id"%TYPE )
    2.15 +  RETURNS SETOF "delegation_chain_row"
    2.16 +  LANGUAGE 'plpgsql' STABLE AS $$
    2.17 +    DECLARE
    2.18 +      "output_row"           "delegation_chain_row";
    2.19 +      "direct_voter_row"     "direct_voter"%ROWTYPE;
    2.20 +      "delegating_voter_row" "delegating_voter"%ROWTYPE;
    2.21 +    BEGIN
    2.22 +      "output_row"."index"         := 0;
    2.23 +      "output_row"."member_id"     := "member_id_p";
    2.24 +      "output_row"."member_valid"  := TRUE;
    2.25 +      "output_row"."participation" := FALSE;
    2.26 +      "output_row"."overridden"    := FALSE;
    2.27 +      "output_row"."disabled_out"  := FALSE;
    2.28 +      LOOP
    2.29 +        SELECT INTO "direct_voter_row" * FROM "direct_voter"
    2.30 +          WHERE "issue_id" = "issue_id_p"
    2.31 +          AND "member_id" = "output_row"."member_id";
    2.32 +        IF "direct_voter_row"."member_id" NOTNULL THEN
    2.33 +          "output_row"."participation" := TRUE;
    2.34 +          "output_row"."scope_out"     := NULL;
    2.35 +          "output_row"."disabled_out"  := NULL;
    2.36 +          RETURN NEXT "output_row";
    2.37 +          RETURN;
    2.38 +        END IF;
    2.39 +        SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
    2.40 +          WHERE "issue_id" = "issue_id_p"
    2.41 +          AND "member_id" = "output_row"."member_id";
    2.42 +        IF "delegating_voter_row"."member_id" ISNULL THEN
    2.43 +          RETURN;
    2.44 +        END IF;
    2.45 +        "output_row"."scope_out" := "delegating_voter_row"."scope";
    2.46 +        RETURN NEXT "output_row";
    2.47 +        "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
    2.48 +        "output_row"."scope_in"  := "output_row"."scope_out";
    2.49 +      END LOOP;
    2.50 +    END;
    2.51 +  $$;
    2.52 +
    2.53 +COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
    2.54 +  ( "member"."id"%TYPE,
    2.55 +    "member"."id"%TYPE )
    2.56 +  IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
    2.57 +
    2.58 +DROP FUNCTION "delegation_chain"
    2.59 +  ( "member"."id"%TYPE,
    2.60 +    "unit"."id"%TYPE,
    2.61 +    "area"."id"%TYPE,
    2.62 +    "issue"."id"%TYPE );
    2.63 +
    2.64 +DROP FUNCTION "delegation_chain"
    2.65 +  ( "member"."id"%TYPE,
    2.66 +    "unit"."id"%TYPE,
    2.67 +    "area"."id"%TYPE,
    2.68 +    "issue"."id"%TYPE,
    2.69 +    "member"."id"%TYPE );
    2.70 +
    2.71 +CREATE FUNCTION "delegation_chain"
    2.72 +  ( "member_id_p"           "member"."id"%TYPE,
    2.73 +    "unit_id_p"             "unit"."id"%TYPE,
    2.74 +    "area_id_p"             "area"."id"%TYPE,
    2.75 +    "issue_id_p"            "issue"."id"%TYPE,
    2.76 +    "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL )
    2.77 +  RETURNS SETOF "delegation_chain_row"
    2.78 +  LANGUAGE 'plpgsql' STABLE AS $$
    2.79 +    DECLARE
    2.80 +      "scope_v"            "delegation_scope";
    2.81 +      "unit_id_v"          "unit"."id"%TYPE;
    2.82 +      "area_id_v"          "area"."id"%TYPE;
    2.83 +      "issue_row"          "issue"%ROWTYPE;
    2.84 +      "visited_member_ids" INT4[];  -- "member"."id"%TYPE[]
    2.85 +      "loop_member_id_v"   "member"."id"%TYPE;
    2.86 +      "output_row"         "delegation_chain_row";
    2.87 +      "output_rows"        "delegation_chain_row"[];
    2.88 +      "delegation_row"     "delegation"%ROWTYPE;
    2.89 +      "row_count"          INT4;
    2.90 +      "i"                  INT4;
    2.91 +      "loop_v"             BOOLEAN;
    2.92 +    BEGIN
    2.93 +      IF
    2.94 +        "unit_id_p" NOTNULL AND
    2.95 +        "area_id_p" ISNULL AND
    2.96 +        "issue_id_p" ISNULL
    2.97 +      THEN
    2.98 +        "scope_v" := 'unit';
    2.99 +        "unit_id_v" := "unit_id_p";
   2.100 +      ELSIF
   2.101 +        "unit_id_p" ISNULL AND
   2.102 +        "area_id_p" NOTNULL AND
   2.103 +        "issue_id_p" ISNULL
   2.104 +      THEN
   2.105 +        "scope_v" := 'area';
   2.106 +        "area_id_v" := "area_id_p";
   2.107 +        SELECT "unit_id" INTO "unit_id_v"
   2.108 +          FROM "area" WHERE "id" = "area_id_v";
   2.109 +      ELSIF
   2.110 +        "unit_id_p" ISNULL AND
   2.111 +        "area_id_p" ISNULL AND
   2.112 +        "issue_id_p" NOTNULL
   2.113 +      THEN
   2.114 +        SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
   2.115 +        IF "issue_row"."id" ISNULL THEN
   2.116 +          RETURN;
   2.117 +        END IF;
   2.118 +        IF "issue_row"."closed" NOTNULL THEN
   2.119 +          IF "simulate_trustee_id_p" NOTNULL THEN
   2.120 +            RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
   2.121 +          END IF;
   2.122 +          FOR "output_row" IN
   2.123 +            SELECT * FROM
   2.124 +            "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
   2.125 +          LOOP
   2.126 +            RETURN NEXT "output_row";
   2.127 +          END LOOP;
   2.128 +          RETURN;
   2.129 +        END IF;
   2.130 +        "scope_v" := 'issue';
   2.131 +        SELECT "area_id" INTO "area_id_v"
   2.132 +          FROM "issue" WHERE "id" = "issue_id_p";
   2.133 +        SELECT "unit_id" INTO "unit_id_v"
   2.134 +          FROM "area"  WHERE "id" = "area_id_v";
   2.135 +      ELSE
   2.136 +        RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
   2.137 +      END IF;
   2.138 +      "visited_member_ids" := '{}';
   2.139 +      "loop_member_id_v"   := NULL;
   2.140 +      "output_rows"        := '{}';
   2.141 +      "output_row"."index"         := 0;
   2.142 +      "output_row"."member_id"     := "member_id_p";
   2.143 +      "output_row"."member_valid"  := TRUE;
   2.144 +      "output_row"."participation" := FALSE;
   2.145 +      "output_row"."overridden"    := FALSE;
   2.146 +      "output_row"."disabled_out"  := FALSE;
   2.147 +      "output_row"."scope_out"     := NULL;
   2.148 +      LOOP
   2.149 +        IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
   2.150 +          "loop_member_id_v" := "output_row"."member_id";
   2.151 +        ELSE
   2.152 +          "visited_member_ids" :=
   2.153 +            "visited_member_ids" || "output_row"."member_id";
   2.154 +        END IF;
   2.155 +        IF "output_row"."participation" ISNULL THEN
   2.156 +          "output_row"."overridden" := NULL;
   2.157 +        ELSIF "output_row"."participation" THEN
   2.158 +          "output_row"."overridden" := TRUE;
   2.159 +        END IF;
   2.160 +        "output_row"."scope_in" := "output_row"."scope_out";
   2.161 +        IF EXISTS (
   2.162 +          SELECT NULL FROM "member" JOIN "privilege"
   2.163 +          ON "privilege"."member_id" = "member"."id"
   2.164 +          AND "privilege"."unit_id" = "unit_id_v"
   2.165 +          WHERE "id" = "output_row"."member_id"
   2.166 +          AND "member"."active" AND "privilege"."voting_right"
   2.167 +        ) THEN
   2.168 +          IF "scope_v" = 'unit' THEN
   2.169 +            SELECT * INTO "delegation_row" FROM "delegation"
   2.170 +              WHERE "truster_id" = "output_row"."member_id"
   2.171 +              AND "unit_id" = "unit_id_v";
   2.172 +          ELSIF "scope_v" = 'area' THEN
   2.173 +            "output_row"."participation" := EXISTS (
   2.174 +              SELECT NULL FROM "membership"
   2.175 +              WHERE "area_id" = "area_id_p"
   2.176 +              AND "member_id" = "output_row"."member_id"
   2.177 +            );
   2.178 +            SELECT * INTO "delegation_row" FROM "delegation"
   2.179 +              WHERE "truster_id" = "output_row"."member_id"
   2.180 +              AND (
   2.181 +                "unit_id" = "unit_id_v" OR
   2.182 +                "area_id" = "area_id_v"
   2.183 +              )
   2.184 +              ORDER BY "scope" DESC;
   2.185 +          ELSIF "scope_v" = 'issue' THEN
   2.186 +            IF "issue_row"."fully_frozen" ISNULL THEN
   2.187 +              "output_row"."participation" := EXISTS (
   2.188 +                SELECT NULL FROM "interest"
   2.189 +                WHERE "issue_id" = "issue_id_p"
   2.190 +                AND "member_id" = "output_row"."member_id"
   2.191 +              );
   2.192 +            ELSE
   2.193 +              IF "output_row"."member_id" = "member_id_p" THEN
   2.194 +                "output_row"."participation" := EXISTS (
   2.195 +                  SELECT NULL FROM "direct_voter"
   2.196 +                  WHERE "issue_id" = "issue_id_p"
   2.197 +                  AND "member_id" = "output_row"."member_id"
   2.198 +                );
   2.199 +              ELSE
   2.200 +                "output_row"."participation" := NULL;
   2.201 +              END IF;
   2.202 +            END IF;
   2.203 +            SELECT * INTO "delegation_row" FROM "delegation"
   2.204 +              WHERE "truster_id" = "output_row"."member_id"
   2.205 +              AND (
   2.206 +                "unit_id" = "unit_id_v" OR
   2.207 +                "area_id" = "area_id_v" OR
   2.208 +                "issue_id" = "issue_id_p"
   2.209 +              )
   2.210 +              ORDER BY "scope" DESC;
   2.211 +          END IF;
   2.212 +        ELSE
   2.213 +          "output_row"."member_valid"  := FALSE;
   2.214 +          "output_row"."participation" := FALSE;
   2.215 +          "output_row"."scope_out"     := NULL;
   2.216 +          "delegation_row" := ROW(NULL);
   2.217 +        END IF;
   2.218 +        IF
   2.219 +          "output_row"."member_id" = "member_id_p" AND
   2.220 +          "simulate_trustee_id_p" NOTNULL
   2.221 +        THEN
   2.222 +          "output_row"."scope_out" := "scope_v";
   2.223 +          "output_rows" := "output_rows" || "output_row";
   2.224 +          "output_row"."member_id" := "simulate_trustee_id_p";
   2.225 +        ELSIF "delegation_row"."trustee_id" NOTNULL THEN
   2.226 +          "output_row"."scope_out" := "delegation_row"."scope";
   2.227 +          "output_rows" := "output_rows" || "output_row";
   2.228 +          "output_row"."member_id" := "delegation_row"."trustee_id";
   2.229 +        ELSIF "delegation_row"."scope" NOTNULL THEN
   2.230 +          "output_row"."scope_out" := "delegation_row"."scope";
   2.231 +          "output_row"."disabled_out" := TRUE;
   2.232 +          "output_rows" := "output_rows" || "output_row";
   2.233 +          EXIT;
   2.234 +        ELSE
   2.235 +          "output_row"."scope_out" := NULL;
   2.236 +          "output_rows" := "output_rows" || "output_row";
   2.237 +          EXIT;
   2.238 +        END IF;
   2.239 +        EXIT WHEN "loop_member_id_v" NOTNULL;
   2.240 +        "output_row"."index" := "output_row"."index" + 1;
   2.241 +      END LOOP;
   2.242 +      "row_count" := array_upper("output_rows", 1);
   2.243 +      "i"      := 1;
   2.244 +      "loop_v" := FALSE;
   2.245 +      LOOP
   2.246 +        "output_row" := "output_rows"["i"];
   2.247 +        EXIT WHEN "output_row" ISNULL;  -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
   2.248 +        IF "loop_v" THEN
   2.249 +          IF "i" + 1 = "row_count" THEN
   2.250 +            "output_row"."loop" := 'last';
   2.251 +          ELSIF "i" = "row_count" THEN
   2.252 +            "output_row"."loop" := 'repetition';
   2.253 +          ELSE
   2.254 +            "output_row"."loop" := 'intermediate';
   2.255 +          END IF;
   2.256 +        ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
   2.257 +          "output_row"."loop" := 'first';
   2.258 +          "loop_v" := TRUE;
   2.259 +        END IF;
   2.260 +        IF "scope_v" = 'unit' THEN
   2.261 +          "output_row"."participation" := NULL;
   2.262 +        END IF;
   2.263 +        RETURN NEXT "output_row";
   2.264 +        "i" := "i" + 1;
   2.265 +      END LOOP;
   2.266 +      RETURN;
   2.267 +    END;
   2.268 +  $$;
   2.269 +
   2.270 +COMMENT ON FUNCTION "delegation_chain"
   2.271 +  ( "member"."id"%TYPE,
   2.272 +    "unit"."id"%TYPE,
   2.273 +    "area"."id"%TYPE,
   2.274 +    "issue"."id"%TYPE,
   2.275 +    "member"."id"%TYPE )
   2.276 +  IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
   2.277 +
   2.278 +CREATE TYPE "delegation_info_loop_type" AS ENUM
   2.279 +  ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
   2.280 +
   2.281 +COMMENT ON TYPE "delegation_info_loop_type" IS 'Type of "delegation_loop" in "delegation_info_type"; ''own'' means loop to self, ''first'' means loop to first trustee, ''first_ellipsis'' means loop to ellipsis after first trustee, ''other'' means loop to other trustee, ''other_ellipsis'' means loop to ellipsis after other trustee''';
   2.282 +
   2.283 +CREATE TYPE "delegation_info_type" AS (
   2.284 +        "own_participation"           BOOLEAN,
   2.285 +        "own_delegation_scope"        "delegation_scope",
   2.286 +        "first_trustee_id"            INT4,
   2.287 +        "first_trustee_participation" BOOLEAN,
   2.288 +        "first_trustee_ellipsis"      BOOLEAN,
   2.289 +        "other_trustee_id"            INT4,
   2.290 +        "other_trustee_participation" BOOLEAN,
   2.291 +        "other_trustee_ellipsis"      BOOLEAN,
   2.292 +        "delegation_loop"             "delegation_info_loop_type");
   2.293 +
   2.294 +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.295 +
   2.296 +COMMENT ON COLUMN "delegation_info_type"."own_participation"           IS 'Member is directly participating';
   2.297 +COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope"        IS 'Delegation scope of member';
   2.298 +COMMENT ON COLUMN "delegation_info_type"."first_trustee_id"            IS 'Direct trustee of member';
   2.299 +COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
   2.300 +COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis"      IS 'Ellipsis in delegation chain after "first_trustee"';
   2.301 +COMMENT ON COLUMN "delegation_info_type"."other_trustee_id"            IS 'Another relevant trustee (due to participation)';
   2.302 +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.303 +COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis"      IS 'Ellipsis in delegation chain after "other_trustee"';
   2.304 +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.305 +
   2.306 +CREATE FUNCTION "delegation_info"
   2.307 +  ( "member_id_p"           "member"."id"%TYPE,
   2.308 +    "unit_id_p"             "unit"."id"%TYPE,
   2.309 +    "area_id_p"             "area"."id"%TYPE,
   2.310 +    "issue_id_p"            "issue"."id"%TYPE,
   2.311 +    "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL )
   2.312 +  RETURNS "delegation_info_type"
   2.313 +  LANGUAGE 'plpgsql' STABLE AS $$
   2.314 +    DECLARE
   2.315 +      "current_row" "delegation_chain_row";
   2.316 +      "result"      "delegation_info_type";
   2.317 +    BEGIN
   2.318 +      "result"."own_participation" := FALSE;
   2.319 +      FOR "current_row" IN
   2.320 +        SELECT * FROM "delegation_chain"(
   2.321 +          "member_id_p",
   2.322 +          "unit_id_p", "area_id_p", "issue_id_p",
   2.323 +          "simulate_trustee_id_p")
   2.324 +      LOOP
   2.325 +        IF "current_row"."member_id" = "member_id_p" THEN
   2.326 +          "result"."own_participation"    := "current_row"."participation";
   2.327 +          "result"."own_delegation_scope" := "current_row"."scope_out";
   2.328 +          IF "current_row"."loop" = 'first' THEN
   2.329 +            "result"."delegation_loop" := 'own';
   2.330 +          END IF;
   2.331 +        ELSIF
   2.332 +          "current_row"."member_valid" AND
   2.333 +          ( "current_row"."loop" ISNULL OR
   2.334 +            "current_row"."loop" != 'repetition' )
   2.335 +        THEN
   2.336 +          IF "result"."first_trustee_id" ISNULL THEN
   2.337 +            "result"."first_trustee_id"            := "current_row"."member_id";
   2.338 +            "result"."first_trustee_participation" := "current_row"."participation";
   2.339 +            "result"."first_trustee_ellipsis"      := FALSE;
   2.340 +            IF "current_row"."loop" = 'first' THEN
   2.341 +              "result"."delegation_loop" := 'first';
   2.342 +            END IF;
   2.343 +          ELSIF "result"."other_trustee_id" ISNULL THEN
   2.344 +            IF "current_row"."participation" THEN
   2.345 +              "result"."other_trustee_id"            := "current_row"."member_id";
   2.346 +              "result"."other_trustee_participation" := TRUE;
   2.347 +              "result"."other_trustee_ellipsis"      := FALSE;
   2.348 +              IF "current_row"."loop" = 'first' THEN
   2.349 +                "result"."delegation_loop" := 'other';
   2.350 +              END IF;
   2.351 +            ELSE
   2.352 +              "result"."first_trustee_ellipsis" := TRUE;
   2.353 +              IF "current_row"."loop" = 'first' THEN
   2.354 +                "result"."delegation_loop" := 'first_ellipsis';
   2.355 +              END IF;
   2.356 +            END IF;
   2.357 +          ELSE
   2.358 +            "result"."other_trustee_ellipsis" := TRUE;
   2.359 +            IF "current_row"."loop" = 'first' THEN
   2.360 +              "result"."delegation_loop" := 'other_ellipsis';
   2.361 +            END IF;
   2.362 +          END IF;
   2.363 +        END IF;
   2.364 +      END LOOP;
   2.365 +      RETURN "result";
   2.366 +    END;
   2.367 +  $$;
   2.368 +
   2.369 +COMMENT ON FUNCTION "delegation_info"
   2.370 +  ( "member"."id"%TYPE,
   2.371 +    "unit"."id"%TYPE,
   2.372 +    "area"."id"%TYPE,
   2.373 +    "issue"."id"%TYPE,
   2.374 +    "member"."id"%TYPE )
   2.375 +  IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
   2.376 +
   2.377 +COMMIT;

Impressum / About Us