liquid_feedback_core

changeset 5:1cbdd3975a61 beta6

Version beta6

Added function for printing delegation chains

Replaced column member_count of table area by 3 new columns:
- direct_member_count
- member_weight (regarding delegations)
- autoreject_weight

Added calculated field to member_count table, storing the time of computation of the total member count and area member counts

Added view liquid_feedback_version

Code cleanup
author jbe
date Tue Nov 17 12:00:00 2009 +0100 (2009-11-17)
parents 6133c0a62378
children 3ea7a72ed7e7
files core.sql
line diff
     1.1 --- a/core.sql	Sun Nov 15 12:00:00 2009 +0100
     1.2 +++ b/core.sql	Tue Nov 17 12:00:00 2009 +0100
     1.3 @@ -5,6 +5,10 @@
     1.4  
     1.5  BEGIN;
     1.6  
     1.7 +CREATE VIEW "liquid_feedback_version" AS
     1.8 +  SELECT * FROM (VALUES ('beta6', NULL, NULL, NULL))
     1.9 +  AS "subquery"("string", "major", "minor", "revision");
    1.10 +
    1.11  
    1.12  
    1.13  -------------------------
    1.14 @@ -31,11 +35,13 @@
    1.15  
    1.16  
    1.17  CREATE TABLE "member_count" (
    1.18 -        "count"                 INT4            NOT NULL );
    1.19 +        "calculated"            TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    1.20 +        "total_count"           INT4            NOT NULL );
    1.21  
    1.22 -COMMENT ON TABLE "member_count" IS 'Holds a single value (single row, single column) of the total count of active(!) members, as calculated from "member_count_view"';
    1.23 +COMMENT ON TABLE "member_count" IS 'Contains one row which contains the total count of active(!) members and a timestamp indicating when the total member count and area member counts were calculated';
    1.24  
    1.25 -COMMENT ON COLUMN "member_count"."count" IS 'Total count of active(!) members';
    1.26 +COMMENT ON COLUMN "member_count"."calculated"  IS 'timestamp indicating when the total member count and area member counts were calculated';
    1.27 +COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
    1.28  
    1.29  
    1.30  CREATE TABLE "contact" (
    1.31 @@ -100,13 +106,17 @@
    1.32          "active"                BOOLEAN         NOT NULL DEFAULT TRUE,
    1.33          "name"                  TEXT            NOT NULL,
    1.34          "description"           TEXT            NOT NULL DEFAULT '',
    1.35 -        "member_count"          INT4 );
    1.36 +        "direct_member_count"   INT4,
    1.37 +        "member_weight"         INT4,
    1.38 +        "autoreject_weight"     INT4 );
    1.39  CREATE INDEX "area_active_idx" ON "area" ("active");
    1.40  
    1.41  COMMENT ON TABLE "area" IS 'Subject areas';
    1.42  
    1.43 -COMMENT ON COLUMN "area"."active"       IS 'TRUE means new issues can be created in this area';
    1.44 -COMMENT ON COLUMN "area"."member_count" IS 'Number of active members of that area, as calculated from view "area_member_count"';
    1.45 +COMMENT ON COLUMN "area"."active"              IS 'TRUE means new issues can be created in this area';
    1.46 +COMMENT ON COLUMN "area"."direct_member_count" IS 'Number of active members of that area (ignoring their weight), as calculated from view "area_member_count"';
    1.47 +COMMENT ON COLUMN "area"."member_weight"       IS 'Same as "direct_member_count" but respecting delegations';
    1.48 +COMMENT ON COLUMN "area"."autoreject_weight"   IS 'Sum of weight of members using the autoreject feature';
    1.49  
    1.50  
    1.51  CREATE TABLE "issue" (
    1.52 @@ -151,9 +161,9 @@
    1.53  COMMENT ON COLUMN "issue"."closed"          IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active';
    1.54  COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
    1.55  COMMENT ON COLUMN "issue"."snapshot"        IS 'Point in time, when snapshot tables have been updated and "population", "vote_now", "vote_later" and *_count values were precalculated';
    1.56 -COMMENT ON COLUMN "issue"."population"      IS 'Calculated from table "direct_population_snapshot"';
    1.57 -COMMENT ON COLUMN "issue"."vote_now"        IS 'Calculated from table "direct_interest_snapshot"';
    1.58 -COMMENT ON COLUMN "issue"."vote_later"      IS 'Calculated from table "direct_interest_snapshot"';
    1.59 +COMMENT ON COLUMN "issue"."population"      IS 'Sum of "weight" column in table "direct_population_snapshot"';
    1.60 +COMMENT ON COLUMN "issue"."vote_now"        IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
    1.61 +COMMENT ON COLUMN "issue"."vote_later"      IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
    1.62  COMMENT ON COLUMN "issue"."voter_count"     IS 'Total number of direct and delegating voters; This value is related to the final voting, while "population" is related to snapshots before the final voting';
    1.63  
    1.64  
    1.65 @@ -780,14 +790,163 @@
    1.66  -- Views and helper functions for views --
    1.67  ------------------------------------------
    1.68  
    1.69 +
    1.70 +CREATE TYPE "delegation_scope" AS ENUM
    1.71 +  ('global', 'area', 'issue');
    1.72 +
    1.73 +COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''global'', ''area'', or ''issue''';
    1.74 +
    1.75 +
    1.76 +CREATE VIEW "global_delegation" AS
    1.77 +  SELECT
    1.78 +    "delegation"."id",
    1.79 +    "delegation"."truster_id",
    1.80 +    "delegation"."trustee_id"
    1.81 +  FROM "delegation" JOIN "member"
    1.82 +  ON "delegation"."trustee_id" = "member"."id"
    1.83 +  WHERE "delegation"."area_id" ISNULL
    1.84 +  AND "delegation"."issue_id" ISNULL
    1.85 +  AND "member"."active";
    1.86 +
    1.87 +COMMENT ON VIEW "global_delegation" IS 'Global delegations to active members';
    1.88 +
    1.89 +
    1.90 +CREATE VIEW "area_delegation" AS
    1.91 +  SELECT "subquery".* FROM (
    1.92 +    SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
    1.93 +      "area"."id"               AS "area_id",
    1.94 +      "delegation"."id"         AS "id",
    1.95 +      "delegation"."truster_id" AS "truster_id",
    1.96 +      "delegation"."trustee_id" AS "trustee_id",
    1.97 +      CASE WHEN "delegation"."area_id" ISNULL THEN
    1.98 +        'global'::"delegation_scope"
    1.99 +      ELSE
   1.100 +        'area'::"delegation_scope"
   1.101 +      END AS "scope"
   1.102 +    FROM "area" JOIN "delegation"
   1.103 +    ON ("delegation"."area_id" ISNULL OR "delegation"."area_id" = "area"."id")
   1.104 +    AND "delegation"."issue_id" ISNULL
   1.105 +    ORDER BY
   1.106 +      "area"."id",
   1.107 +      "delegation"."truster_id",
   1.108 +      "delegation"."area_id" NULLS LAST
   1.109 +  ) AS "subquery"
   1.110 +  JOIN "member" ON "subquery"."trustee_id" = "member"."id"
   1.111 +  WHERE "member"."active";
   1.112 +
   1.113 +COMMENT ON VIEW "area_delegation" IS 'Active delegations for areas';
   1.114 +
   1.115 +
   1.116 +CREATE VIEW "issue_delegation" AS
   1.117 +  SELECT "subquery".* FROM (
   1.118 +    SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
   1.119 +      "issue"."id"              AS "issue_id",
   1.120 +      "delegation"."id"         AS "id",
   1.121 +      "delegation"."truster_id" AS "truster_id",
   1.122 +      "delegation"."trustee_id" AS "trustee_id",
   1.123 +      CASE
   1.124 +        WHEN
   1.125 +          "delegation"."area_id" ISNULL AND
   1.126 +          "delegation"."issue_id" ISNULL
   1.127 +        THEN 'global'::"delegation_scope"
   1.128 +        WHEN
   1.129 +          "delegation"."area_id" NOTNULL
   1.130 +        THEN 'area'::"delegation_scope"
   1.131 +        ELSE 'issue'::"delegation_scope"
   1.132 +      END AS "scope"
   1.133 +    FROM "issue" JOIN "delegation"
   1.134 +    ON (
   1.135 +      "delegation"."area_id" ISNULL OR
   1.136 +      "delegation"."area_id" = "issue"."area_id"
   1.137 +    ) AND (
   1.138 +      "delegation"."issue_id" ISNULL OR
   1.139 +      "delegation"."issue_id" = "issue"."id"
   1.140 +    )
   1.141 +    ORDER BY
   1.142 +      "issue"."id",
   1.143 +      "delegation"."truster_id",
   1.144 +      "delegation"."issue_id" NULLS LAST,
   1.145 +      "delegation"."area_id" NULLS LAST
   1.146 +  ) AS "subquery"
   1.147 +  JOIN "member" ON "subquery"."trustee_id" = "member"."id"
   1.148 +  WHERE "member"."active";
   1.149 +
   1.150 +COMMENT ON VIEW "issue_delegation" IS 'Active delegations for issues';
   1.151 +
   1.152 +
   1.153 +CREATE FUNCTION "membership_weight_with_skipping"
   1.154 +  ( "area_id_p"         "area"."id"%TYPE,
   1.155 +    "member_id_p"       "member"."id"%TYPE,
   1.156 +    "skip_member_ids_p" INT4[] )  -- "member"."id"%TYPE[]
   1.157 +  RETURNS INT4
   1.158 +  LANGUAGE 'plpgsql' STABLE AS $$
   1.159 +    DECLARE
   1.160 +      "sum_v"          INT4;
   1.161 +      "delegation_row" "area_delegation"%ROWTYPE;
   1.162 +    BEGIN
   1.163 +      "sum_v" := 1;
   1.164 +      FOR "delegation_row" IN
   1.165 +        SELECT "area_delegation".*
   1.166 +        FROM "area_delegation" LEFT JOIN "membership"
   1.167 +        ON "membership"."area_id" = "area_id_p"
   1.168 +        AND "membership"."member_id" = "area_delegation"."truster_id"
   1.169 +        WHERE "area_delegation"."area_id" = "area_id_p"
   1.170 +        AND "area_delegation"."trustee_id" = "member_id_p"
   1.171 +        AND "membership"."member_id" ISNULL
   1.172 +      LOOP
   1.173 +        IF NOT
   1.174 +          "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
   1.175 +        THEN
   1.176 +          "sum_v" := "sum_v" + "membership_weight_with_skipping"(
   1.177 +            "area_id_p",
   1.178 +            "delegation_row"."truster_id",
   1.179 +            "skip_member_ids_p" || "delegation_row"."truster_id"
   1.180 +          );
   1.181 +        END IF;
   1.182 +      END LOOP;
   1.183 +      RETURN "sum_v";
   1.184 +    END;
   1.185 +  $$;
   1.186 +
   1.187 +CREATE FUNCTION "membership_weight"
   1.188 +  ( "area_id_p"         "area"."id"%TYPE,
   1.189 +    "member_id_p"       "member"."id"%TYPE )  -- "member"."id"%TYPE[]
   1.190 +  RETURNS INT4
   1.191 +  LANGUAGE 'plpgsql' STABLE AS $$
   1.192 +    BEGIN
   1.193 +      RETURN "membership_weight_with_skipping"(
   1.194 +        "area_id_p",
   1.195 +        "member_id_p",
   1.196 +        ARRAY["member_id_p"]
   1.197 +      );
   1.198 +    END;
   1.199 +  $$;
   1.200 +
   1.201 +
   1.202  CREATE VIEW "member_count_view" AS
   1.203 -  SELECT count(1) FROM "member" WHERE "active";
   1.204 +  SELECT count(1) AS "total_count" FROM "member" WHERE "active";
   1.205  
   1.206  COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
   1.207  
   1.208  
   1.209  CREATE VIEW "area_member_count" AS
   1.210 -  SELECT "area"."id" AS "area_id", count("member"."id")
   1.211 +  SELECT
   1.212 +    "area"."id" AS "area_id",
   1.213 +    count("member"."id") AS "direct_member_count",
   1.214 +    coalesce(
   1.215 +      sum(
   1.216 +        CASE WHEN "member"."id" NOTNULL THEN
   1.217 +          "membership_weight"("area"."id", "member"."id")
   1.218 +        ELSE 0 END
   1.219 +      )
   1.220 +    ) AS "member_weight",
   1.221 +    coalesce(
   1.222 +      sum(
   1.223 +        CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
   1.224 +          "membership_weight"("area"."id", "member"."id")
   1.225 +        ELSE 0 END
   1.226 +      )
   1.227 +    ) AS "autoreject_weight"
   1.228    FROM "area"
   1.229    LEFT JOIN "membership"
   1.230    ON "area"."id" = "membership"."area_id"
   1.231 @@ -795,43 +954,11 @@
   1.232    ON "membership"."member_id" = "member"."id"
   1.233    AND "member"."active"
   1.234    GROUP BY "area"."id";
   1.235 +-- TODO: count delegations
   1.236  
   1.237  COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
   1.238  
   1.239  
   1.240 -CREATE VIEW "issue_delegation_with_overridden_and_inactive" AS
   1.241 -  SELECT "delegation".*, "issue"."id" AS "resulting_issue_id"
   1.242 -  FROM "delegation"
   1.243 -  JOIN "issue" ON
   1.244 -    ("delegation"."area_id" ISNULL AND "delegation"."issue_id" ISNULL) OR
   1.245 -    "delegation"."area_id" = "issue"."area_id" OR
   1.246 -    "delegation"."issue_id" = "issue"."id";
   1.247 -
   1.248 -COMMENT ON VIEW "issue_delegation_with_overridden_and_inactive" IS 'Helper view for "issue_delegation"';
   1.249 -
   1.250 -
   1.251 -CREATE VIEW "issue_delegation" AS
   1.252 -  SELECT
   1.253 -    "entry"."id"                 AS "id",
   1.254 -    "entry"."truster_id"         AS "truster_id",
   1.255 -    "entry"."trustee_id"         AS "trustee_id",
   1.256 -    "entry"."resulting_issue_id" AS "issue_id"
   1.257 -  FROM "issue_delegation_with_overridden_and_inactive" AS "entry"
   1.258 -  JOIN "member" AS "truster" ON "entry"."truster_id" = "truster"."id"
   1.259 -  JOIN "member" AS "trustee" ON "entry"."trustee_id" = "trustee"."id"
   1.260 -  LEFT JOIN "issue_delegation_with_overridden_and_inactive" AS "override"
   1.261 -    ON "entry"."truster_id" = "override"."truster_id"
   1.262 -    AND "entry"."id" != "override"."id"
   1.263 -    AND (
   1.264 -      ("entry"."area_id" ISNULL AND "entry"."issue_id" ISNULL) OR
   1.265 -      "override"."issue_id" NOTNULL
   1.266 -    )
   1.267 -  WHERE "truster"."active" AND "trustee"."active"
   1.268 -  AND "override"."truster_id" ISNULL;
   1.269 -
   1.270 -COMMENT ON VIEW "issue_delegation" IS 'Resulting delegations for issues, without those involving inactive members';
   1.271 -
   1.272 -
   1.273  CREATE VIEW "current_draft" AS
   1.274    SELECT "draft".* FROM (
   1.275      SELECT
   1.276 @@ -923,6 +1050,214 @@
   1.277  
   1.278  
   1.279  
   1.280 +--------------------------------------------------
   1.281 +-- Set returning function for delegation chains --
   1.282 +--------------------------------------------------
   1.283 +
   1.284 +
   1.285 +CREATE TYPE "delegation_chain_loop_tag" AS ENUM
   1.286 +  ('first', 'intermediate', 'last', 'repetition');
   1.287 +
   1.288 +COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
   1.289 +
   1.290 +
   1.291 +CREATE TYPE "delegation_chain_row" AS (
   1.292 +        "index"                 INT4,
   1.293 +        "member_id"             INT4,
   1.294 +        "member_active"         BOOLEAN,
   1.295 +        "participation"         BOOLEAN,
   1.296 +        "overridden"            BOOLEAN,
   1.297 +        "scope_in"              "delegation_scope",
   1.298 +        "scope_out"             "delegation_scope",
   1.299 +        "loop"                  "delegation_chain_loop_tag" );
   1.300 +
   1.301 +COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
   1.302 +
   1.303 +COMMENT ON COLUMN "delegation_chain_row"."index"         IS 'Index starting with 0 and counting up';
   1.304 +COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest, for areas: membership, for global delegation chains: always null';
   1.305 +COMMENT ON COLUMN "delegation_chain_row"."overridden"    IS 'True, if an entry with lower index has "participation" set to true';
   1.306 +COMMENT ON COLUMN "delegation_chain_row"."scope_in"      IS 'Scope of used incoming delegation';
   1.307 +COMMENT ON COLUMN "delegation_chain_row"."scope_out"     IS 'Scope of used outgoing delegation';
   1.308 +COMMENT ON COLUMN "delegation_chain_row"."loop"          IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
   1.309 +
   1.310 +
   1.311 +CREATE FUNCTION "delegation_chain"
   1.312 +  ( "member_id_p"           "member"."id"%TYPE,
   1.313 +    "area_id_p"             "area"."id"%TYPE,
   1.314 +    "issue_id_p"            "issue"."id"%TYPE,
   1.315 +    "simulate_trustee_id_p" "member"."id"%TYPE )
   1.316 +  RETURNS SETOF "delegation_chain_row"
   1.317 +  LANGUAGE 'plpgsql' STABLE AS $$
   1.318 +    DECLARE
   1.319 +      "issue_row"          "issue"%ROWTYPE;
   1.320 +      "visited_member_ids" INT4[];  -- "member"."id"%TYPE[]
   1.321 +      "loop_member_id_v"   "member"."id"%TYPE;
   1.322 +      "output_row"         "delegation_chain_row";
   1.323 +      "output_rows"        "delegation_chain_row"[];
   1.324 +      "delegation_row"     "delegation"%ROWTYPE;
   1.325 +      "row_count"          INT4;
   1.326 +      "i"                  INT4;
   1.327 +      "loop_v"             BOOLEAN;
   1.328 +    BEGIN
   1.329 +      SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
   1.330 +      "visited_member_ids" := '{}';
   1.331 +      "loop_member_id_v"   := NULL;
   1.332 +      "output_rows"        := '{}';
   1.333 +      "output_row"."index"         := 0;
   1.334 +      "output_row"."member_id"     := "member_id_p";
   1.335 +      "output_row"."member_active" := TRUE;
   1.336 +      "output_row"."participation" := FALSE;
   1.337 +      "output_row"."overridden"    := FALSE;
   1.338 +      "output_row"."scope_out"     := NULL;
   1.339 +      LOOP
   1.340 +        IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
   1.341 +          "loop_member_id_v" := "output_row"."member_id";
   1.342 +        ELSE
   1.343 +          "visited_member_ids" :=
   1.344 +            "visited_member_ids" || "output_row"."member_id";
   1.345 +        END IF;
   1.346 +        IF "output_row"."participation" THEN
   1.347 +          "output_row"."overridden" := TRUE;
   1.348 +        END IF;
   1.349 +        "output_row"."scope_in" := "output_row"."scope_out";
   1.350 +        IF EXISTS (
   1.351 +          SELECT NULL FROM "member" 
   1.352 +          WHERE "id" = "output_row"."member_id" AND "active"
   1.353 +        ) THEN
   1.354 +          IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
   1.355 +            SELECT * INTO "delegation_row" FROM "delegation"
   1.356 +              WHERE "truster_id" = "output_row"."member_id"
   1.357 +              AND "area_id" ISNULL AND "issue_id" ISNULL;
   1.358 +          ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN
   1.359 +            "output_row"."participation" := EXISTS (
   1.360 +              SELECT NULL FROM "membership"
   1.361 +              WHERE "area_id" = "area_id_p"
   1.362 +              AND "member_id" = "output_row"."member_id"
   1.363 +            );
   1.364 +            SELECT * INTO "delegation_row" FROM "delegation"
   1.365 +              WHERE "truster_id" = "output_row"."member_id"
   1.366 +              AND ("area_id" ISNULL OR "area_id" = "area_id_p")
   1.367 +              AND "issue_id" ISNULL
   1.368 +              ORDER BY "area_id" NULLS LAST;
   1.369 +          ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN
   1.370 +            "output_row"."participation" := EXISTS (
   1.371 +              SELECT NULL FROM "interest"
   1.372 +              WHERE "issue_id" = "issue_id_p"
   1.373 +              AND "member_id" = "output_row"."member_id"
   1.374 +            );
   1.375 +            SELECT * INTO "delegation_row" FROM "delegation"
   1.376 +              WHERE "truster_id" = "output_row"."member_id"
   1.377 +              AND ("area_id" ISNULL OR "area_id" = "issue_row"."area_id")
   1.378 +              AND ("issue_id" ISNULL OR "issue_id" = "issue_id_p")
   1.379 +              ORDER BY "issue_id" NULLS LAST, "area_id" NULLS LAST;
   1.380 +          ELSE
   1.381 +            RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';
   1.382 +          END IF;
   1.383 +        ELSE
   1.384 +          "output_row"."member_active" := FALSE;
   1.385 +          "output_row"."participation" := FALSE;
   1.386 +          "output_row"."scope_out"     := NULL;
   1.387 +          "delegation_row" := ROW(NULL);
   1.388 +        END IF;
   1.389 +        IF
   1.390 +          "output_row"."member_id" = "member_id_p" AND
   1.391 +          "simulate_trustee_id_p" NOTNULL
   1.392 +        THEN
   1.393 +          "output_row"."scope_out" := CASE
   1.394 +            WHEN "area_id_p" ISNULL  AND "issue_id_p" ISNULL  THEN 'global'
   1.395 +            WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL  THEN 'area'
   1.396 +            WHEN "area_id_p" ISNULL  AND "issue_id_p" NOTNULL THEN 'issue'
   1.397 +          END;
   1.398 +          "output_rows" := "output_rows" || "output_row";
   1.399 +          "output_row"."member_id" := "simulate_trustee_id_p";
   1.400 +        ELSIF "delegation_row"."trustee_id" NOTNULL THEN
   1.401 +          "output_row"."scope_out" := CASE
   1.402 +            WHEN
   1.403 +              "delegation_row"."area_id" ISNULL AND
   1.404 +              "delegation_row"."issue_id" ISNULL
   1.405 +            THEN 'global'
   1.406 +            WHEN
   1.407 +              "delegation_row"."area_id" NOTNULL AND
   1.408 +              "delegation_row"."issue_id" ISNULL
   1.409 +            THEN 'area'
   1.410 +            WHEN
   1.411 +              "delegation_row"."area_id" ISNULL AND
   1.412 +              "delegation_row"."issue_id" NOTNULL
   1.413 +            THEN 'issue'
   1.414 +          END;
   1.415 +          "output_rows" := "output_rows" || "output_row";
   1.416 +          "output_row"."member_id" := "delegation_row"."trustee_id";
   1.417 +        ELSE
   1.418 +          "output_row"."scope_out" := NULL;
   1.419 +          "output_rows" := "output_rows" || "output_row";
   1.420 +          EXIT;
   1.421 +        END IF;
   1.422 +        EXIT WHEN "loop_member_id_v" NOTNULL;
   1.423 +        "output_row"."index" := "output_row"."index" + 1;
   1.424 +      END LOOP;
   1.425 +      "row_count" := array_upper("output_rows", 1);
   1.426 +      "i"      := 1;
   1.427 +      "loop_v" := FALSE;
   1.428 +      LOOP
   1.429 +        "output_row" := "output_rows"["i"];
   1.430 +        EXIT WHEN "output_row"."member_id" ISNULL;
   1.431 +        IF "loop_v" THEN
   1.432 +          IF "i" + 1 = "row_count" THEN
   1.433 +            "output_row"."loop" := 'last';
   1.434 +          ELSIF "i" = "row_count" THEN
   1.435 +            "output_row"."loop" := 'repetition';
   1.436 +          ELSE
   1.437 +            "output_row"."loop" := 'intermediate';
   1.438 +          END IF;
   1.439 +        ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
   1.440 +          "output_row"."loop" := 'first';
   1.441 +          "loop_v" := TRUE;
   1.442 +        END IF;
   1.443 +        IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
   1.444 +          "output_row"."participation" := NULL;
   1.445 +        END IF;
   1.446 +        RETURN NEXT "output_row";
   1.447 +        "i" := "i" + 1;
   1.448 +      END LOOP;
   1.449 +      RETURN;
   1.450 +    END;
   1.451 +  $$;
   1.452 +
   1.453 +COMMENT ON FUNCTION "delegation_chain"
   1.454 +  ( "member"."id"%TYPE,
   1.455 +    "area"."id"%TYPE,
   1.456 +    "issue"."id"%TYPE,
   1.457 +    "member"."id"%TYPE )
   1.458 +  IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
   1.459 +
   1.460 +CREATE FUNCTION "delegation_chain"
   1.461 +  ( "member_id_p" "member"."id"%TYPE,
   1.462 +    "area_id_p"   "area"."id"%TYPE,
   1.463 +    "issue_id_p"  "issue"."id"%TYPE )
   1.464 +  RETURNS SETOF "delegation_chain_row"
   1.465 +  LANGUAGE 'plpgsql' STABLE AS $$
   1.466 +    DECLARE
   1.467 +      "result_row" "delegation_chain_row";
   1.468 +    BEGIN
   1.469 +      FOR "result_row" IN
   1.470 +        SELECT * FROM "delegation_chain"(
   1.471 +          "member_id_p", "area_id_p", "issue_id_p", NULL
   1.472 +        )
   1.473 +      LOOP
   1.474 +        RETURN NEXT "result_row";
   1.475 +      END LOOP;
   1.476 +      RETURN;
   1.477 +    END;
   1.478 +  $$;
   1.479 +
   1.480 +COMMENT ON FUNCTION "delegation_chain"
   1.481 +  ( "member"."id"%TYPE,
   1.482 +    "area"."id"%TYPE,
   1.483 +    "issue"."id"%TYPE )
   1.484 +  IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
   1.485 +
   1.486 +
   1.487 +
   1.488  ------------------------------
   1.489  -- Comparison by vote count --
   1.490  ------------------------------
   1.491 @@ -1003,11 +1338,14 @@
   1.492        LOCK TABLE "area"       IN EXCLUSIVE MODE;
   1.493        LOCK TABLE "membership" IN EXCLUSIVE MODE;
   1.494        DELETE FROM "member_count";
   1.495 -      INSERT INTO "member_count" ("count")
   1.496 -        SELECT "count" FROM "member_count_view";
   1.497 -      UPDATE "area" SET "member_count" = "area_member_count"."count"
   1.498 -        FROM "area_member_count"
   1.499 -        WHERE "area_member_count"."area_id" = "area"."id";
   1.500 +      INSERT INTO "member_count" ("total_count")
   1.501 +        SELECT "total_count" FROM "member_count_view";
   1.502 +      UPDATE "area" SET
   1.503 +        "direct_member_count" = "view"."direct_member_count",
   1.504 +        "member_weight"       = "view"."member_weight",
   1.505 +        "autoreject_weight"   = "view"."autoreject_weight"
   1.506 +        FROM "area_member_count" AS "view"
   1.507 +        WHERE "view"."area_id" = "area"."id";
   1.508        RETURN;
   1.509      END;
   1.510    $$;
   1.511 @@ -1550,7 +1888,7 @@
   1.512  -----------------------
   1.513  
   1.514  
   1.515 -CREATE FUNCTION "weight_of_added_delegations"
   1.516 +CREATE FUNCTION "weight_of_added_vote_delegations"
   1.517    ( "issue_id_p"            "issue"."id"%TYPE,
   1.518      "member_id_p"           "member"."id"%TYPE,
   1.519      "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
   1.520 @@ -1585,18 +1923,19 @@
   1.521                "issue_id_p",
   1.522                "delegate_member_ids_v"
   1.523              );
   1.524 -          "weight_v" := "weight_v" + 1 + "weight_of_added_delegations"(
   1.525 -            "issue_id_p",
   1.526 -            "issue_delegation_row"."truster_id",
   1.527 -            "delegate_member_ids_v"
   1.528 -          );
   1.529 +          "weight_v" := "weight_v" +
   1.530 +            1 + "weight_of_added_vote_delegations"(
   1.531 +              "issue_id_p",
   1.532 +              "issue_delegation_row"."truster_id",
   1.533 +              "delegate_member_ids_v"
   1.534 +            );
   1.535          END IF;
   1.536        END LOOP;
   1.537        RETURN "weight_v";
   1.538      END;
   1.539    $$;
   1.540  
   1.541 -COMMENT ON FUNCTION "weight_of_added_delegations"
   1.542 +COMMENT ON FUNCTION "weight_of_added_vote_delegations"
   1.543    ( "issue"."id"%TYPE,
   1.544      "member"."id"%TYPE,
   1.545      "delegating_voter"."delegate_member_ids"%TYPE )
   1.546 @@ -1615,7 +1954,7 @@
   1.547          WHERE "issue_id" = "issue_id_p"
   1.548        LOOP
   1.549          UPDATE "direct_voter" SET
   1.550 -          "weight" = "weight" + "weight_of_added_delegations"(
   1.551 +          "weight" = "weight" + "weight_of_added_vote_delegations"(
   1.552              "issue_id_p",
   1.553              "member_id_v",
   1.554              '{}'

Impressum / About Us