liquid_feedback_core

changeset 615:5ae68278492f

Added update script core-update.v4.2.0-v4.2.1.sql
author jbe
date Sun May 17 13:56:43 2020 +0200 (2020-05-17)
parents 7f564126cffb
children ae53fc96c953
files update/core-update.v4.2.0-v4.2.1.sql
line diff
     1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/update/core-update.v4.2.0-v4.2.1.sql	Sun May 17 13:56:43 2020 +0200
     1.3 @@ -0,0 +1,968 @@
     1.4 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     1.5 +  SELECT * FROM (VALUES ('4.2.1-incomplete-update', 4, 2, -1))
     1.6 +  AS "subquery"("string", "major", "minor", "revision");
     1.7 +
     1.8 +BEGIN;
     1.9 +
    1.10 +ALTER TABLE "unit" ADD COLUMN "member_weight" INT4;
    1.11 +COMMENT ON COLUMN "unit"."member_weight" IS 'Sum of active members'' voting weight';
    1.12 +
    1.13 +ALTER TABLE "snapshot_population" ADD COLUMN "weight" INT4 NOT NULL DEFAULT 1;
    1.14 +ALTER TABLE "snapshot_population" ALTER COLUMN "weight" DROP DEFAULT;
    1.15 + 
    1.16 +ALTER TABLE "privilege" ADD COLUMN "weight" INT4 NOT NULL DEFAULT 1 CHECK ("weight" >= 0);
    1.17 +COMMENT ON COLUMN "privilege"."weight"           IS 'Voting weight of member in unit';
    1.18 +
    1.19 +CREATE TABLE "issue_privilege" (
    1.20 +        PRIMARY KEY ("issue_id", "member_id"),
    1.21 +        "issue_id"              INT4            REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.22 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.23 +        "initiative_right"      BOOLEAN,
    1.24 +        "voting_right"          BOOLEAN,
    1.25 +        "polling_right"         BOOLEAN,
    1.26 +        "weight"                INT4            CHECK ("weight" >= 0) );
    1.27 +CREATE INDEX "issue_privilege_idx" ON "issue_privilege" ("member_id");
    1.28 +COMMENT ON TABLE "issue_privilege" IS 'Override of "privilege" table for rights of members in certain issues';
    1.29 + 
    1.30 +ALTER TABLE "direct_interest_snapshot" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1;
    1.31 +ALTER TABLE "direct_interest_snapshot" ALTER COLUMN "ownweight" DROP DEFAULT;
    1.32 +COMMENT ON COLUMN "direct_interest_snapshot"."ownweight" IS 'Own voting weight of member, disregading delegations';
    1.33 +COMMENT ON COLUMN "direct_interest_snapshot"."weight"    IS 'Voting weight of member according to own weight and "delegating_interest_snapshot"';
    1.34 + 
    1.35 +ALTER TABLE "delegating_interest_snapshot" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1;
    1.36 +ALTER TABLE "delegating_interest_snapshot" ALTER COLUMN "ownweight" DROP DEFAULT;
    1.37 +COMMENT ON COLUMN "delegating_interest_snapshot"."ownweight" IS 'Own voting weight of member, disregading delegations';
    1.38 +COMMENT ON COLUMN "delegating_interest_snapshot"."weight"    IS 'Intermediate voting weight considering incoming delegations';
    1.39 +
    1.40 +ALTER TABLE "direct_voter" ADD COLUMN "ownweight" INT4 DEFAULT 1;
    1.41 +ALTER TABLE "direct_voter" ALTER COLUMN "ownweight" DROP DEFAULT;
    1.42 +COMMENT ON COLUMN "direct_voter"."ownweight" IS 'Own voting weight of member, disregarding delegations';
    1.43 +COMMENT ON COLUMN "direct_voter"."weight"    IS 'Voting weight of member according to own weight and "delegating_interest_snapshot"';
    1.44 +
    1.45 +ALTER TABLE "delegating_voter" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1;
    1.46 +ALTER TABLE "delegating_voter" ALTER COLUMN "ownweight" DROP DEFAULT;
    1.47 +COMMENT ON COLUMN "delegating_voter"."ownweight" IS 'Own voting weight of member, disregarding delegations';
    1.48 +COMMENT ON COLUMN "delegating_voter"."weight"    IS 'Intermediate voting weight considering incoming delegations';
    1.49 +
    1.50 +DROP VIEW "issue_delegation";
    1.51 +CREATE VIEW "issue_delegation" AS
    1.52 +  SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
    1.53 +    "issue"."id" AS "issue_id",
    1.54 +    "delegation"."id",
    1.55 +    "delegation"."truster_id",
    1.56 +    "delegation"."trustee_id",
    1.57 +    COALESCE("issue_privilege"."weight", "privilege"."weight") AS "weight",
    1.58 +    "delegation"."scope"
    1.59 +  FROM "issue"
    1.60 +  JOIN "area"
    1.61 +    ON "area"."id" = "issue"."area_id"
    1.62 +  JOIN "delegation"
    1.63 +    ON "delegation"."unit_id" = "area"."unit_id"
    1.64 +    OR "delegation"."area_id" = "area"."id"
    1.65 +    OR "delegation"."issue_id" = "issue"."id"
    1.66 +  JOIN "member"
    1.67 +    ON "delegation"."truster_id" = "member"."id"
    1.68 +  LEFT JOIN "privilege"
    1.69 +    ON "area"."unit_id" = "privilege"."unit_id"
    1.70 +    AND "delegation"."truster_id" = "privilege"."member_id"
    1.71 +  LEFT JOIN "issue_privilege"
    1.72 +    ON "issue"."id" = "issue_privilege"."issue_id"
    1.73 +    AND "delegation"."truster_id" = "issue_privilege"."member_id"
    1.74 +  WHERE "member"."active"
    1.75 +  AND COALESCE("issue_privilege"."voting_right", "privilege"."voting_right")
    1.76 +  ORDER BY
    1.77 +    "issue"."id",
    1.78 +    "delegation"."truster_id",
    1.79 +    "delegation"."scope" DESC;
    1.80 +COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
    1.81 +
    1.82 +CREATE OR REPLACE VIEW "unit_member" AS
    1.83 +  SELECT
    1.84 +    "privilege"."unit_id" AS "unit_id",
    1.85 +    "member"."id"         AS "member_id",
    1.86 +    "privilege"."weight"
    1.87 +  FROM "privilege" JOIN "member" ON "member"."id" = "privilege"."member_id"
    1.88 +  WHERE "privilege"."voting_right" AND "member"."active";
    1.89 +
    1.90 +CREATE OR REPLACE VIEW "unit_member_count" AS
    1.91 +  SELECT
    1.92 +    "unit"."id" AS "unit_id",
    1.93 +    count("unit_member"."member_id") AS "member_count",
    1.94 +    sum("unit_member"."weight") AS "member_weight"
    1.95 +  FROM "unit" LEFT JOIN "unit_member"
    1.96 +  ON "unit"."id" = "unit_member"."unit_id"
    1.97 +  GROUP BY "unit"."id";
    1.98 +
    1.99 +CREATE OR REPLACE VIEW "event_for_notification" AS
   1.100 +  SELECT
   1.101 +    "member"."id" AS "recipient_id",
   1.102 +    "event".*
   1.103 +  FROM "member" CROSS JOIN "event"
   1.104 +  JOIN "issue" ON "issue"."id" = "event"."issue_id"
   1.105 +  JOIN "area" ON "area"."id" = "issue"."area_id"
   1.106 +  LEFT JOIN "privilege" ON
   1.107 +    "privilege"."member_id" = "member"."id" AND
   1.108 +    "privilege"."unit_id" = "area"."unit_id"
   1.109 +  LEFT JOIN "issue_privilege" ON
   1.110 +    "issue_privilege"."member_id" = "member"."id" AND
   1.111 +    "issue_privilege"."issue_id" = "event"."issue_id"
   1.112 +  LEFT JOIN "subscription" ON
   1.113 +    "subscription"."member_id" = "member"."id" AND
   1.114 +    "subscription"."unit_id" = "area"."unit_id"
   1.115 +  LEFT JOIN "ignored_area" ON
   1.116 +    "ignored_area"."member_id" = "member"."id" AND
   1.117 +    "ignored_area"."area_id" = "issue"."area_id"
   1.118 +  LEFT JOIN "interest" ON
   1.119 +    "interest"."member_id" = "member"."id" AND
   1.120 +    "interest"."issue_id" = "event"."issue_id"
   1.121 +  LEFT JOIN "supporter" ON
   1.122 +    "supporter"."member_id" = "member"."id" AND
   1.123 +    "supporter"."initiative_id" = "event"."initiative_id"
   1.124 +  WHERE (
   1.125 +    COALESCE("issue_privilege"."voting_right", "privilege"."voting_right") OR
   1.126 +    "subscription"."member_id" NOTNULL
   1.127 +  ) AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
   1.128 +  AND (
   1.129 +    "event"."event" = 'issue_state_changed'::"event_type" OR
   1.130 +    ( "event"."event" = 'initiative_revoked'::"event_type" AND
   1.131 +      "supporter"."member_id" NOTNULL ) );
   1.132 +
   1.133 +CREATE OR REPLACE FUNCTION "featured_initiative"
   1.134 +  ( "recipient_id_p" "member"."id"%TYPE,
   1.135 +    "area_id_p"      "area"."id"%TYPE )
   1.136 +  RETURNS SETOF "initiative"."id"%TYPE
   1.137 +  LANGUAGE 'plpgsql' STABLE AS $$
   1.138 +    DECLARE
   1.139 +      "counter_v"         "member"."notification_counter"%TYPE;
   1.140 +      "sample_size_v"     "member"."notification_sample_size"%TYPE;
   1.141 +      "initiative_id_ary" INT4[];  --"initiative"."id"%TYPE[]
   1.142 +      "match_v"           BOOLEAN;
   1.143 +      "member_id_v"       "member"."id"%TYPE;
   1.144 +      "seed_v"            TEXT;
   1.145 +      "initiative_id_v"   "initiative"."id"%TYPE;
   1.146 +    BEGIN
   1.147 +      SELECT "notification_counter", "notification_sample_size"
   1.148 +        INTO "counter_v", "sample_size_v"
   1.149 +        FROM "member" WHERE "id" = "recipient_id_p";
   1.150 +      IF COALESCE("sample_size_v" <= 0, TRUE) THEN
   1.151 +        RETURN;
   1.152 +      END IF;
   1.153 +      "initiative_id_ary" := '{}';
   1.154 +      LOOP
   1.155 +        "match_v" := FALSE;
   1.156 +        FOR "member_id_v", "seed_v" IN
   1.157 +          SELECT * FROM (
   1.158 +            SELECT DISTINCT
   1.159 +              "supporter"."member_id",
   1.160 +              md5(
   1.161 +                "recipient_id_p" || '-' ||
   1.162 +                "counter_v"      || '-' ||
   1.163 +                "area_id_p"      || '-' ||
   1.164 +                "supporter"."member_id"
   1.165 +              ) AS "seed"
   1.166 +            FROM "supporter"
   1.167 +            JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
   1.168 +            JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   1.169 +            WHERE "supporter"."member_id" != "recipient_id_p"
   1.170 +            AND "issue"."area_id" = "area_id_p"
   1.171 +            AND "issue"."state" IN ('admission', 'discussion', 'verification')
   1.172 +          ) AS "subquery"
   1.173 +          ORDER BY "seed"
   1.174 +        LOOP
   1.175 +          SELECT "initiative"."id" INTO "initiative_id_v"
   1.176 +            FROM "initiative"
   1.177 +            JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   1.178 +            JOIN "area" ON "area"."id" = "issue"."area_id"
   1.179 +            JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
   1.180 +            LEFT JOIN "supporter" AS "self_support" ON
   1.181 +              "self_support"."initiative_id" = "initiative"."id" AND
   1.182 +              "self_support"."member_id" = "recipient_id_p"
   1.183 +            LEFT JOIN "privilege" ON
   1.184 +              "privilege"."member_id" = "recipient_id_p" AND
   1.185 +              "privilege"."unit_id" = "area"."unit_id"
   1.186 +            LEFT JOIN "issue_privilege" ON
   1.187 +              "privilege"."member_id" = "recipient_id_p" AND
   1.188 +              "privilege"."issue_id" = "initiative"."issue_id"
   1.189 +            LEFT JOIN "subscription" ON
   1.190 +              "subscription"."member_id" = "recipient_id_p" AND
   1.191 +              "subscription"."unit_id" = "area"."unit_id"
   1.192 +            LEFT JOIN "ignored_initiative" ON
   1.193 +              "ignored_initiative"."member_id" = "recipient_id_p" AND
   1.194 +              "ignored_initiative"."initiative_id" = "initiative"."id"
   1.195 +            WHERE "supporter"."member_id" = "member_id_v"
   1.196 +            AND "issue"."area_id" = "area_id_p"
   1.197 +            AND "issue"."state" IN ('admission', 'discussion', 'verification')
   1.198 +            AND "initiative"."revoked" ISNULL
   1.199 +            AND "self_support"."member_id" ISNULL
   1.200 +            AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
   1.201 +            AND (
   1.202 +              COALESCE(
   1.203 +                "issue_privilege"."voting_right", "privilege"."voting_right"
   1.204 +              ) OR "subscription"."member_id" NOTNULL )
   1.205 +            AND "ignored_initiative"."member_id" ISNULL
   1.206 +            AND NOT EXISTS (
   1.207 +              SELECT NULL FROM "draft"
   1.208 +              JOIN "ignored_member" ON
   1.209 +                "ignored_member"."member_id" = "recipient_id_p" AND
   1.210 +                "ignored_member"."other_member_id" = "draft"."author_id"
   1.211 +              WHERE "draft"."initiative_id" = "initiative"."id"
   1.212 +            )
   1.213 +            ORDER BY md5("seed_v" || '-' || "initiative"."id")
   1.214 +            LIMIT 1;
   1.215 +          IF FOUND THEN
   1.216 +            "match_v" := TRUE;
   1.217 +            RETURN NEXT "initiative_id_v";
   1.218 +            IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
   1.219 +              RETURN;
   1.220 +            END IF;
   1.221 +            "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
   1.222 +          END IF;
   1.223 +        END LOOP;
   1.224 +        EXIT WHEN NOT "match_v";
   1.225 +      END LOOP;
   1.226 +      RETURN;
   1.227 +    END;
   1.228 +  $$;
   1.229 +
   1.230 +CREATE OR REPLACE FUNCTION "delegation_chain"
   1.231 +  ( "member_id_p"           "member"."id"%TYPE,
   1.232 +    "unit_id_p"             "unit"."id"%TYPE,
   1.233 +    "area_id_p"             "area"."id"%TYPE,
   1.234 +    "issue_id_p"            "issue"."id"%TYPE,
   1.235 +    "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
   1.236 +    "simulate_default_p"    BOOLEAN            DEFAULT FALSE )
   1.237 +  RETURNS SETOF "delegation_chain_row"
   1.238 +  LANGUAGE 'plpgsql' STABLE AS $$
   1.239 +    DECLARE
   1.240 +      "scope_v"            "delegation_scope";
   1.241 +      "unit_id_v"          "unit"."id"%TYPE;
   1.242 +      "area_id_v"          "area"."id"%TYPE;
   1.243 +      "issue_row"          "issue"%ROWTYPE;
   1.244 +      "visited_member_ids" INT4[];  -- "member"."id"%TYPE[]
   1.245 +      "loop_member_id_v"   "member"."id"%TYPE;
   1.246 +      "output_row"         "delegation_chain_row";
   1.247 +      "output_rows"        "delegation_chain_row"[];
   1.248 +      "simulate_v"         BOOLEAN;
   1.249 +      "simulate_here_v"    BOOLEAN;
   1.250 +      "delegation_row"     "delegation"%ROWTYPE;
   1.251 +      "row_count"          INT4;
   1.252 +      "i"                  INT4;
   1.253 +      "loop_v"             BOOLEAN;
   1.254 +    BEGIN
   1.255 +      IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
   1.256 +        RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
   1.257 +      END IF;
   1.258 +      IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
   1.259 +        "simulate_v" := TRUE;
   1.260 +      ELSE
   1.261 +        "simulate_v" := FALSE;
   1.262 +      END IF;
   1.263 +      IF
   1.264 +        "unit_id_p" NOTNULL AND
   1.265 +        "area_id_p" ISNULL AND
   1.266 +        "issue_id_p" ISNULL
   1.267 +      THEN
   1.268 +        "scope_v" := 'unit';
   1.269 +        "unit_id_v" := "unit_id_p";
   1.270 +      ELSIF
   1.271 +        "unit_id_p" ISNULL AND
   1.272 +        "area_id_p" NOTNULL AND
   1.273 +        "issue_id_p" ISNULL
   1.274 +      THEN
   1.275 +        "scope_v" := 'area';
   1.276 +        "area_id_v" := "area_id_p";
   1.277 +        SELECT "unit_id" INTO "unit_id_v"
   1.278 +          FROM "area" WHERE "id" = "area_id_v";
   1.279 +      ELSIF
   1.280 +        "unit_id_p" ISNULL AND
   1.281 +        "area_id_p" ISNULL AND
   1.282 +        "issue_id_p" NOTNULL
   1.283 +      THEN
   1.284 +        SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
   1.285 +        IF "issue_row"."id" ISNULL THEN
   1.286 +          RETURN;
   1.287 +        END IF;
   1.288 +        IF "issue_row"."closed" NOTNULL THEN
   1.289 +          IF "simulate_v" THEN
   1.290 +            RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
   1.291 +          END IF;
   1.292 +          FOR "output_row" IN
   1.293 +            SELECT * FROM
   1.294 +            "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
   1.295 +          LOOP
   1.296 +            RETURN NEXT "output_row";
   1.297 +          END LOOP;
   1.298 +          RETURN;
   1.299 +        END IF;
   1.300 +        "scope_v" := 'issue';
   1.301 +        SELECT "area_id" INTO "area_id_v"
   1.302 +          FROM "issue" WHERE "id" = "issue_id_p";
   1.303 +        SELECT "unit_id" INTO "unit_id_v"
   1.304 +          FROM "area"  WHERE "id" = "area_id_v";
   1.305 +      ELSE
   1.306 +        RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
   1.307 +      END IF;
   1.308 +      "visited_member_ids" := '{}';
   1.309 +      "loop_member_id_v"   := NULL;
   1.310 +      "output_rows"        := '{}';
   1.311 +      "output_row"."index"         := 0;
   1.312 +      "output_row"."member_id"     := "member_id_p";
   1.313 +      "output_row"."member_valid"  := TRUE;
   1.314 +      "output_row"."participation" := FALSE;
   1.315 +      "output_row"."overridden"    := FALSE;
   1.316 +      "output_row"."disabled_out"  := FALSE;
   1.317 +      "output_row"."scope_out"     := NULL;
   1.318 +      LOOP
   1.319 +        IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
   1.320 +          "loop_member_id_v" := "output_row"."member_id";
   1.321 +        ELSE
   1.322 +          "visited_member_ids" :=
   1.323 +            "visited_member_ids" || "output_row"."member_id";
   1.324 +        END IF;
   1.325 +        IF "output_row"."participation" ISNULL THEN
   1.326 +          "output_row"."overridden" := NULL;
   1.327 +        ELSIF "output_row"."participation" THEN
   1.328 +          "output_row"."overridden" := TRUE;
   1.329 +        END IF;
   1.330 +        "output_row"."scope_in" := "output_row"."scope_out";
   1.331 +        "output_row"."member_valid" := EXISTS (
   1.332 +          SELECT NULL FROM "member"
   1.333 +          LEFT JOIN "privilege"
   1.334 +          ON "privilege"."member_id" = "member"."id"
   1.335 +          AND "privilege"."unit_id" = "unit_id_v"
   1.336 +          LEFT JOIN "issue_privilege"
   1.337 +          ON "issue_privilege"."member_id" = "member"."id"
   1.338 +          AND "issue_privilege"."issue_id" = "issue_id_p"
   1.339 +          WHERE "id" = "output_row"."member_id"
   1.340 +          AND "member"."active"
   1.341 +          AND COALESCE(
   1.342 +            "issue_privilege"."voting_right", "privilege"."voting_right")
   1.343 +        );
   1.344 +        "simulate_here_v" := (
   1.345 +          "simulate_v" AND
   1.346 +          "output_row"."member_id" = "member_id_p"
   1.347 +        );
   1.348 +        "delegation_row" := ROW(NULL);
   1.349 +        IF "output_row"."member_valid" OR "simulate_here_v" THEN
   1.350 +          IF "scope_v" = 'unit' THEN
   1.351 +            IF NOT "simulate_here_v" THEN
   1.352 +              SELECT * INTO "delegation_row" FROM "delegation"
   1.353 +                WHERE "truster_id" = "output_row"."member_id"
   1.354 +                AND "unit_id" = "unit_id_v";
   1.355 +            END IF;
   1.356 +          ELSIF "scope_v" = 'area' THEN
   1.357 +            IF "simulate_here_v" THEN
   1.358 +              IF "simulate_trustee_id_p" ISNULL THEN
   1.359 +                SELECT * INTO "delegation_row" FROM "delegation"
   1.360 +                  WHERE "truster_id" = "output_row"."member_id"
   1.361 +                  AND "unit_id" = "unit_id_v";
   1.362 +              END IF;
   1.363 +            ELSE
   1.364 +              SELECT * INTO "delegation_row" FROM "delegation"
   1.365 +                WHERE "truster_id" = "output_row"."member_id"
   1.366 +                AND (
   1.367 +                  "unit_id" = "unit_id_v" OR
   1.368 +                  "area_id" = "area_id_v"
   1.369 +                )
   1.370 +                ORDER BY "scope" DESC;
   1.371 +            END IF;
   1.372 +          ELSIF "scope_v" = 'issue' THEN
   1.373 +            IF "issue_row"."fully_frozen" ISNULL THEN
   1.374 +              "output_row"."participation" := EXISTS (
   1.375 +                SELECT NULL FROM "interest"
   1.376 +                WHERE "issue_id" = "issue_id_p"
   1.377 +                AND "member_id" = "output_row"."member_id"
   1.378 +              );
   1.379 +            ELSE
   1.380 +              IF "output_row"."member_id" = "member_id_p" THEN
   1.381 +                "output_row"."participation" := EXISTS (
   1.382 +                  SELECT NULL FROM "direct_voter"
   1.383 +                  WHERE "issue_id" = "issue_id_p"
   1.384 +                  AND "member_id" = "output_row"."member_id"
   1.385 +                );
   1.386 +              ELSE
   1.387 +                "output_row"."participation" := NULL;
   1.388 +              END IF;
   1.389 +            END IF;
   1.390 +            IF "simulate_here_v" THEN
   1.391 +              IF "simulate_trustee_id_p" ISNULL THEN
   1.392 +                SELECT * INTO "delegation_row" FROM "delegation"
   1.393 +                  WHERE "truster_id" = "output_row"."member_id"
   1.394 +                  AND (
   1.395 +                    "unit_id" = "unit_id_v" OR
   1.396 +                    "area_id" = "area_id_v"
   1.397 +                  )
   1.398 +                  ORDER BY "scope" DESC;
   1.399 +              END IF;
   1.400 +            ELSE
   1.401 +              SELECT * INTO "delegation_row" FROM "delegation"
   1.402 +                WHERE "truster_id" = "output_row"."member_id"
   1.403 +                AND (
   1.404 +                  "unit_id" = "unit_id_v" OR
   1.405 +                  "area_id" = "area_id_v" OR
   1.406 +                  "issue_id" = "issue_id_p"
   1.407 +                )
   1.408 +                ORDER BY "scope" DESC;
   1.409 +            END IF;
   1.410 +          END IF;
   1.411 +        ELSE
   1.412 +          "output_row"."participation" := FALSE;
   1.413 +        END IF;
   1.414 +        IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
   1.415 +          "output_row"."scope_out" := "scope_v";
   1.416 +          "output_rows" := "output_rows" || "output_row";
   1.417 +          "output_row"."member_id" := "simulate_trustee_id_p";
   1.418 +        ELSIF "delegation_row"."trustee_id" NOTNULL THEN
   1.419 +          "output_row"."scope_out" := "delegation_row"."scope";
   1.420 +          "output_rows" := "output_rows" || "output_row";
   1.421 +          "output_row"."member_id" := "delegation_row"."trustee_id";
   1.422 +        ELSIF "delegation_row"."scope" NOTNULL THEN
   1.423 +          "output_row"."scope_out" := "delegation_row"."scope";
   1.424 +          "output_row"."disabled_out" := TRUE;
   1.425 +          "output_rows" := "output_rows" || "output_row";
   1.426 +          EXIT;
   1.427 +        ELSE
   1.428 +          "output_row"."scope_out" := NULL;
   1.429 +          "output_rows" := "output_rows" || "output_row";
   1.430 +          EXIT;
   1.431 +        END IF;
   1.432 +        EXIT WHEN "loop_member_id_v" NOTNULL;
   1.433 +        "output_row"."index" := "output_row"."index" + 1;
   1.434 +      END LOOP;
   1.435 +      "row_count" := array_upper("output_rows", 1);
   1.436 +      "i"      := 1;
   1.437 +      "loop_v" := FALSE;
   1.438 +      LOOP
   1.439 +        "output_row" := "output_rows"["i"];
   1.440 +        EXIT WHEN "output_row" ISNULL;  -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
   1.441 +        IF "loop_v" THEN
   1.442 +          IF "i" + 1 = "row_count" THEN
   1.443 +            "output_row"."loop" := 'last';
   1.444 +          ELSIF "i" = "row_count" THEN
   1.445 +            "output_row"."loop" := 'repetition';
   1.446 +          ELSE
   1.447 +            "output_row"."loop" := 'intermediate';
   1.448 +          END IF;
   1.449 +        ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
   1.450 +          "output_row"."loop" := 'first';
   1.451 +          "loop_v" := TRUE;
   1.452 +        END IF;
   1.453 +        IF "scope_v" = 'unit' THEN
   1.454 +          "output_row"."participation" := NULL;
   1.455 +        END IF;
   1.456 +        RETURN NEXT "output_row";
   1.457 +        "i" := "i" + 1;
   1.458 +      END LOOP;
   1.459 +      RETURN;
   1.460 +    END;
   1.461 +  $$;
   1.462 +
   1.463 +CREATE OR REPLACE FUNCTION "calculate_member_counts"()
   1.464 +  RETURNS VOID
   1.465 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.466 +    BEGIN
   1.467 +      PERFORM "require_transaction_isolation"();
   1.468 +      DELETE FROM "member_count";
   1.469 +      INSERT INTO "member_count" ("total_count")
   1.470 +        SELECT "total_count" FROM "member_count_view";
   1.471 +      UPDATE "unit" SET
   1.472 +        "member_count" = "view"."member_count",
   1.473 +        "member_weight" = "view"."member_weight"
   1.474 +        FROM "unit_member_count" AS "view"
   1.475 +        WHERE "view"."unit_id" = "unit"."id";
   1.476 +      RETURN;
   1.477 +    END;
   1.478 +  $$;
   1.479 +COMMENT ON FUNCTION "calculate_member_counts"() IS 'Updates "member_count" table and "member_count" and "member_weight" columns of table "area" by materializing data from views "member_count_view" and "unit_member_count"';
   1.480 + 
   1.481 +CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_snapshot"
   1.482 +  ( "snapshot_id_p"         "snapshot"."id"%TYPE,
   1.483 +    "issue_id_p"            "issue"."id"%TYPE,
   1.484 +    "member_id_p"           "member"."id"%TYPE,
   1.485 +    "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
   1.486 +  RETURNS "direct_interest_snapshot"."weight"%TYPE
   1.487 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.488 +    DECLARE
   1.489 +      "issue_delegation_row"  "issue_delegation"%ROWTYPE;
   1.490 +      "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
   1.491 +      "weight_v"              INT4;
   1.492 +      "sub_weight_v"          INT4;
   1.493 +    BEGIN
   1.494 +      PERFORM "require_transaction_isolation"();
   1.495 +      "weight_v" := 0;
   1.496 +      FOR "issue_delegation_row" IN
   1.497 +        SELECT * FROM "issue_delegation"
   1.498 +        WHERE "trustee_id" = "member_id_p"
   1.499 +        AND "issue_id" = "issue_id_p"
   1.500 +      LOOP
   1.501 +        IF NOT EXISTS (
   1.502 +          SELECT NULL FROM "direct_interest_snapshot"
   1.503 +          WHERE "snapshot_id" = "snapshot_id_p"
   1.504 +          AND "issue_id" = "issue_id_p"
   1.505 +          AND "member_id" = "issue_delegation_row"."truster_id"
   1.506 +        ) AND NOT EXISTS (
   1.507 +          SELECT NULL FROM "delegating_interest_snapshot"
   1.508 +          WHERE "snapshot_id" = "snapshot_id_p"
   1.509 +          AND "issue_id" = "issue_id_p"
   1.510 +          AND "member_id" = "issue_delegation_row"."truster_id"
   1.511 +        ) THEN
   1.512 +          "delegate_member_ids_v" :=
   1.513 +            "member_id_p" || "delegate_member_ids_p";
   1.514 +          INSERT INTO "delegating_interest_snapshot" (
   1.515 +              "snapshot_id",
   1.516 +              "issue_id",
   1.517 +              "member_id",
   1.518 +              "ownweight",
   1.519 +              "scope",
   1.520 +              "delegate_member_ids"
   1.521 +            ) VALUES (
   1.522 +              "snapshot_id_p",
   1.523 +              "issue_id_p",
   1.524 +              "issue_delegation_row"."truster_id",
   1.525 +              "issue_delegation_row"."weight",
   1.526 +              "issue_delegation_row"."scope",
   1.527 +              "delegate_member_ids_v"
   1.528 +            );
   1.529 +          "sub_weight_v" := "issue_delegation_row"."weight" +
   1.530 +            "weight_of_added_delegations_for_snapshot"(
   1.531 +              "snapshot_id_p",
   1.532 +              "issue_id_p",
   1.533 +              "issue_delegation_row"."truster_id",
   1.534 +              "delegate_member_ids_v"
   1.535 +            );
   1.536 +          UPDATE "delegating_interest_snapshot"
   1.537 +            SET "weight" = "sub_weight_v"
   1.538 +            WHERE "snapshot_id" = "snapshot_id_p"
   1.539 +            AND "issue_id" = "issue_id_p"
   1.540 +            AND "member_id" = "issue_delegation_row"."truster_id";
   1.541 +          "weight_v" := "weight_v" + "sub_weight_v";
   1.542 +        END IF;
   1.543 +      END LOOP;
   1.544 +      RETURN "weight_v";
   1.545 +    END;
   1.546 +  $$;
   1.547 +
   1.548 +CREATE OR REPLACE FUNCTION "take_snapshot"
   1.549 +  ( "issue_id_p" "issue"."id"%TYPE,
   1.550 +    "area_id_p"  "area"."id"%TYPE = NULL )
   1.551 +  RETURNS "snapshot"."id"%TYPE
   1.552 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.553 +    DECLARE
   1.554 +      "area_id_v"     "area"."id"%TYPE;
   1.555 +      "unit_id_v"     "unit"."id"%TYPE;
   1.556 +      "snapshot_id_v" "snapshot"."id"%TYPE;
   1.557 +      "issue_id_v"    "issue"."id"%TYPE;
   1.558 +      "member_id_v"   "member"."id"%TYPE;
   1.559 +    BEGIN
   1.560 +      IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN
   1.561 +        RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL';
   1.562 +      END IF;
   1.563 +      PERFORM "require_transaction_isolation"();
   1.564 +      IF "issue_id_p" ISNULL THEN
   1.565 +        "area_id_v" := "area_id_p";
   1.566 +      ELSE
   1.567 +        SELECT "area_id" INTO "area_id_v"
   1.568 +          FROM "issue" WHERE "id" = "issue_id_p";
   1.569 +      END IF;
   1.570 +      SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
   1.571 +      INSERT INTO "snapshot" ("area_id", "issue_id")
   1.572 +        VALUES ("area_id_v", "issue_id_p")
   1.573 +        RETURNING "id" INTO "snapshot_id_v";
   1.574 +      INSERT INTO "snapshot_population" ("snapshot_id", "member_id", "weight")
   1.575 +        SELECT
   1.576 +          "snapshot_id_v",
   1.577 +          "member"."id",
   1.578 +          COALESCE("issue_privilege"."weight", "privilege"."weight")
   1.579 +        FROM "member"
   1.580 +        LEFT JOIN "privilege"
   1.581 +        ON "privilege"."unit_id" = "unit_id_v"
   1.582 +        AND "privilege"."member_id" = "member"."id"
   1.583 +        LEFT JOIN "issue_privilege"
   1.584 +        ON "issue_privilege"."issue_id" = "issue_id_p"
   1.585 +        AND "issue_privilege"."member_id" = "member"."id"
   1.586 +        WHERE "member"."active" AND COALESCE(
   1.587 +          "issue_privilege"."voting_right", "privilege"."voting_right");
   1.588 +      UPDATE "snapshot" SET
   1.589 +        "population" = (
   1.590 +          SELECT sum("weight") FROM "snapshot_population"
   1.591 +          WHERE "snapshot_id" = "snapshot_id_v"
   1.592 +        ) WHERE "id" = "snapshot_id_v";
   1.593 +      FOR "issue_id_v" IN
   1.594 +        SELECT "id" FROM "issue"
   1.595 +        WHERE CASE WHEN "issue_id_p" ISNULL THEN
   1.596 +          "area_id" = "area_id_p" AND
   1.597 +          "state" = 'admission'
   1.598 +        ELSE
   1.599 +          "id" = "issue_id_p"
   1.600 +        END
   1.601 +      LOOP
   1.602 +        INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
   1.603 +          VALUES ("snapshot_id_v", "issue_id_v");
   1.604 +        INSERT INTO "direct_interest_snapshot"
   1.605 +          ("snapshot_id", "issue_id", "member_id", "ownweight")
   1.606 +          SELECT
   1.607 +            "snapshot_id_v" AS "snapshot_id",
   1.608 +            "issue_id_v"    AS "issue_id",
   1.609 +            "member"."id"   AS "member_id",
   1.610 +            COALESCE(
   1.611 +              "issue_privilege"."weight", "privilege"."weight"
   1.612 +            ) AS "ownweight"
   1.613 +          FROM "issue"
   1.614 +          JOIN "area" ON "issue"."area_id" = "area"."id"
   1.615 +          JOIN "interest" ON "issue"."id" = "interest"."issue_id"
   1.616 +          JOIN "member" ON "interest"."member_id" = "member"."id"
   1.617 +          LEFT JOIN "privilege"
   1.618 +            ON "privilege"."unit_id" = "area"."unit_id"
   1.619 +            AND "privilege"."member_id" = "member"."id"
   1.620 +          LEFT JOIN "issue_privilege"
   1.621 +            ON "issue_privilege"."issue_id" = "issue_id_v"
   1.622 +            AND "issue_privilege"."member_id" = "member"."id"
   1.623 +          WHERE "issue"."id" = "issue_id_v"
   1.624 +          AND "member"."active" AND COALESCE(
   1.625 +            "issue_privilege"."voting_right", "privilege"."voting_right");
   1.626 +        FOR "member_id_v" IN
   1.627 +          SELECT "member_id" FROM "direct_interest_snapshot"
   1.628 +          WHERE "snapshot_id" = "snapshot_id_v"
   1.629 +          AND "issue_id" = "issue_id_v"
   1.630 +        LOOP
   1.631 +          UPDATE "direct_interest_snapshot" SET
   1.632 +            "weight" = "ownweight" +
   1.633 +              "weight_of_added_delegations_for_snapshot"(
   1.634 +                "snapshot_id_v",
   1.635 +                "issue_id_v",
   1.636 +                "member_id_v",
   1.637 +                '{}'
   1.638 +              )
   1.639 +            WHERE "snapshot_id" = "snapshot_id_v"
   1.640 +            AND "issue_id" = "issue_id_v"
   1.641 +            AND "member_id" = "member_id_v";
   1.642 +        END LOOP;
   1.643 +        INSERT INTO "direct_supporter_snapshot"
   1.644 +          ( "snapshot_id", "issue_id", "initiative_id", "member_id",
   1.645 +            "draft_id", "informed", "satisfied" )
   1.646 +          SELECT
   1.647 +            "snapshot_id_v"         AS "snapshot_id",
   1.648 +            "issue_id_v"            AS "issue_id",
   1.649 +            "initiative"."id"       AS "initiative_id",
   1.650 +            "supporter"."member_id" AS "member_id",
   1.651 +            "supporter"."draft_id"  AS "draft_id",
   1.652 +            "supporter"."draft_id" = "current_draft"."id" AS "informed",
   1.653 +            NOT EXISTS (
   1.654 +              SELECT NULL FROM "critical_opinion"
   1.655 +              WHERE "initiative_id" = "initiative"."id"
   1.656 +              AND "member_id" = "supporter"."member_id"
   1.657 +            ) AS "satisfied"
   1.658 +          FROM "initiative"
   1.659 +          JOIN "supporter"
   1.660 +          ON "supporter"."initiative_id" = "initiative"."id"
   1.661 +          JOIN "current_draft"
   1.662 +          ON "initiative"."id" = "current_draft"."initiative_id"
   1.663 +          JOIN "direct_interest_snapshot"
   1.664 +          ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"
   1.665 +          AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"
   1.666 +          AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
   1.667 +          WHERE "initiative"."issue_id" = "issue_id_v";
   1.668 +        DELETE FROM "temporary_suggestion_counts";
   1.669 +        INSERT INTO "temporary_suggestion_counts"
   1.670 +          ( "id",
   1.671 +            "minus2_unfulfilled_count", "minus2_fulfilled_count",
   1.672 +            "minus1_unfulfilled_count", "minus1_fulfilled_count",
   1.673 +            "plus1_unfulfilled_count", "plus1_fulfilled_count",
   1.674 +            "plus2_unfulfilled_count", "plus2_fulfilled_count" )
   1.675 +          SELECT
   1.676 +            "suggestion"."id",
   1.677 +            ( SELECT coalesce(sum("di"."weight"), 0)
   1.678 +              FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
   1.679 +              ON "di"."snapshot_id" = "snapshot_id_v"
   1.680 +              AND "di"."issue_id" = "issue_id_v"
   1.681 +              AND "di"."member_id" = "opinion"."member_id"
   1.682 +              WHERE "opinion"."suggestion_id" = "suggestion"."id"
   1.683 +              AND "opinion"."degree" = -2
   1.684 +              AND "opinion"."fulfilled" = FALSE
   1.685 +            ) AS "minus2_unfulfilled_count",
   1.686 +            ( SELECT coalesce(sum("di"."weight"), 0)
   1.687 +              FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
   1.688 +              ON "di"."snapshot_id" = "snapshot_id_v"
   1.689 +              AND "di"."issue_id" = "issue_id_v"
   1.690 +              AND "di"."member_id" = "opinion"."member_id"
   1.691 +              WHERE "opinion"."suggestion_id" = "suggestion"."id"
   1.692 +              AND "opinion"."degree" = -2
   1.693 +              AND "opinion"."fulfilled" = TRUE
   1.694 +            ) AS "minus2_fulfilled_count",
   1.695 +            ( SELECT coalesce(sum("di"."weight"), 0)
   1.696 +              FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
   1.697 +              ON "di"."snapshot_id" = "snapshot_id_v"
   1.698 +              AND "di"."issue_id" = "issue_id_v"
   1.699 +              AND "di"."member_id" = "opinion"."member_id"
   1.700 +              WHERE "opinion"."suggestion_id" = "suggestion"."id"
   1.701 +              AND "opinion"."degree" = -1
   1.702 +              AND "opinion"."fulfilled" = FALSE
   1.703 +            ) AS "minus1_unfulfilled_count",
   1.704 +            ( SELECT coalesce(sum("di"."weight"), 0)
   1.705 +              FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
   1.706 +              ON "di"."snapshot_id" = "snapshot_id_v"
   1.707 +              AND "di"."issue_id" = "issue_id_v"
   1.708 +              AND "di"."member_id" = "opinion"."member_id"
   1.709 +              WHERE "opinion"."suggestion_id" = "suggestion"."id"
   1.710 +              AND "opinion"."degree" = -1
   1.711 +              AND "opinion"."fulfilled" = TRUE
   1.712 +            ) AS "minus1_fulfilled_count",
   1.713 +            ( SELECT coalesce(sum("di"."weight"), 0)
   1.714 +              FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
   1.715 +              ON "di"."snapshot_id" = "snapshot_id_v"
   1.716 +              AND "di"."issue_id" = "issue_id_v"
   1.717 +              AND "di"."member_id" = "opinion"."member_id"
   1.718 +              WHERE "opinion"."suggestion_id" = "suggestion"."id"
   1.719 +              AND "opinion"."degree" = 1
   1.720 +              AND "opinion"."fulfilled" = FALSE
   1.721 +            ) AS "plus1_unfulfilled_count",
   1.722 +            ( SELECT coalesce(sum("di"."weight"), 0)
   1.723 +              FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
   1.724 +              ON "di"."snapshot_id" = "snapshot_id_v"
   1.725 +              AND "di"."issue_id" = "issue_id_v"
   1.726 +              AND "di"."member_id" = "opinion"."member_id"
   1.727 +              WHERE "opinion"."suggestion_id" = "suggestion"."id"
   1.728 +              AND "opinion"."degree" = 1
   1.729 +              AND "opinion"."fulfilled" = TRUE
   1.730 +            ) AS "plus1_fulfilled_count",
   1.731 +            ( SELECT coalesce(sum("di"."weight"), 0)
   1.732 +              FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
   1.733 +              ON "di"."snapshot_id" = "snapshot_id_v"
   1.734 +              AND "di"."issue_id" = "issue_id_v"
   1.735 +              AND "di"."member_id" = "opinion"."member_id"
   1.736 +              WHERE "opinion"."suggestion_id" = "suggestion"."id"
   1.737 +              AND "opinion"."degree" = 2
   1.738 +              AND "opinion"."fulfilled" = FALSE
   1.739 +            ) AS "plus2_unfulfilled_count",
   1.740 +            ( SELECT coalesce(sum("di"."weight"), 0)
   1.741 +              FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
   1.742 +              ON "di"."snapshot_id" = "snapshot_id_v"
   1.743 +              AND "di"."issue_id" = "issue_id_v"
   1.744 +              AND "di"."member_id" = "opinion"."member_id"
   1.745 +              WHERE "opinion"."suggestion_id" = "suggestion"."id"
   1.746 +              AND "opinion"."degree" = 2
   1.747 +              AND "opinion"."fulfilled" = TRUE
   1.748 +            ) AS "plus2_fulfilled_count"
   1.749 +            FROM "suggestion" JOIN "initiative"
   1.750 +            ON "suggestion"."initiative_id" = "initiative"."id"
   1.751 +            WHERE "initiative"."issue_id" = "issue_id_v";
   1.752 +      END LOOP;
   1.753 +      RETURN "snapshot_id_v";
   1.754 +    END;
   1.755 +  $$;
   1.756 +
   1.757 +CREATE OR REPLACE FUNCTION "weight_of_added_vote_delegations"
   1.758 +  ( "issue_id_p"            "issue"."id"%TYPE,
   1.759 +    "member_id_p"           "member"."id"%TYPE,
   1.760 +    "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
   1.761 +  RETURNS "direct_voter"."weight"%TYPE
   1.762 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.763 +    DECLARE
   1.764 +      "issue_delegation_row"  "issue_delegation"%ROWTYPE;
   1.765 +      "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
   1.766 +      "weight_v"              INT4;
   1.767 +      "sub_weight_v"          INT4;
   1.768 +    BEGIN
   1.769 +      PERFORM "require_transaction_isolation"();
   1.770 +      "weight_v" := 0;
   1.771 +      FOR "issue_delegation_row" IN
   1.772 +        SELECT * FROM "issue_delegation"
   1.773 +        WHERE "trustee_id" = "member_id_p"
   1.774 +        AND "issue_id" = "issue_id_p"
   1.775 +      LOOP
   1.776 +        IF NOT EXISTS (
   1.777 +          SELECT NULL FROM "direct_voter"
   1.778 +          WHERE "member_id" = "issue_delegation_row"."truster_id"
   1.779 +          AND "issue_id" = "issue_id_p"
   1.780 +        ) AND NOT EXISTS (
   1.781 +          SELECT NULL FROM "delegating_voter"
   1.782 +          WHERE "member_id" = "issue_delegation_row"."truster_id"
   1.783 +          AND "issue_id" = "issue_id_p"
   1.784 +        ) THEN
   1.785 +          "delegate_member_ids_v" :=
   1.786 +            "member_id_p" || "delegate_member_ids_p";
   1.787 +          INSERT INTO "delegating_voter" (
   1.788 +              "issue_id",
   1.789 +              "member_id",
   1.790 +              "ownweight",
   1.791 +              "scope",
   1.792 +              "delegate_member_ids"
   1.793 +            ) VALUES (
   1.794 +              "issue_id_p",
   1.795 +              "issue_delegation_row"."truster_id",
   1.796 +              "issue_delegation_row"."weight",
   1.797 +              "issue_delegation_row"."scope",
   1.798 +              "delegate_member_ids_v"
   1.799 +            );
   1.800 +          "sub_weight_v" := "issue_delegation_row"."weight" +
   1.801 +            "weight_of_added_vote_delegations"(
   1.802 +              "issue_id_p",
   1.803 +              "issue_delegation_row"."truster_id",
   1.804 +              "delegate_member_ids_v"
   1.805 +            );
   1.806 +          UPDATE "delegating_voter"
   1.807 +            SET "weight" = "sub_weight_v"
   1.808 +            WHERE "issue_id" = "issue_id_p"
   1.809 +            AND "member_id" = "issue_delegation_row"."truster_id";
   1.810 +          "weight_v" := "weight_v" + "sub_weight_v";
   1.811 +        END IF;
   1.812 +      END LOOP;
   1.813 +      RETURN "weight_v";
   1.814 +    END;
   1.815 +  $$;
   1.816 +
   1.817 +CREATE OR REPLACE FUNCTION "add_vote_delegations"
   1.818 +  ( "issue_id_p" "issue"."id"%TYPE )
   1.819 +  RETURNS VOID
   1.820 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.821 +    DECLARE
   1.822 +      "member_id_v" "member"."id"%TYPE;
   1.823 +    BEGIN
   1.824 +      PERFORM "require_transaction_isolation"();
   1.825 +      FOR "member_id_v" IN
   1.826 +        SELECT "member_id" FROM "direct_voter"
   1.827 +        WHERE "issue_id" = "issue_id_p"
   1.828 +      LOOP
   1.829 +        UPDATE "direct_voter" SET
   1.830 +          "weight" = "ownweight" + "weight_of_added_vote_delegations"(
   1.831 +            "issue_id_p",
   1.832 +            "member_id_v",
   1.833 +            '{}'
   1.834 +          )
   1.835 +          WHERE "member_id" = "member_id_v"
   1.836 +          AND "issue_id" = "issue_id_p";
   1.837 +      END LOOP;
   1.838 +      RETURN;
   1.839 +    END;
   1.840 +  $$;
   1.841 +
   1.842 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
   1.843 +  RETURNS VOID
   1.844 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.845 +    DECLARE
   1.846 +      "area_id_v"   "area"."id"%TYPE;
   1.847 +      "unit_id_v"   "unit"."id"%TYPE;
   1.848 +      "member_id_v" "member"."id"%TYPE;
   1.849 +    BEGIN
   1.850 +      PERFORM "require_transaction_isolation"();
   1.851 +      SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
   1.852 +      SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
   1.853 +      -- override protection triggers:
   1.854 +      INSERT INTO "temporary_transaction_data" ("key", "value")
   1.855 +        VALUES ('override_protection_triggers', TRUE::TEXT);
   1.856 +      -- delete timestamp of voting comment:
   1.857 +      UPDATE "direct_voter" SET "comment_changed" = NULL
   1.858 +        WHERE "issue_id" = "issue_id_p";
   1.859 +      -- delete delegating votes (in cases of manual reset of issue state):
   1.860 +      DELETE FROM "delegating_voter"
   1.861 +        WHERE "issue_id" = "issue_id_p";
   1.862 +      -- delete votes from non-privileged voters:
   1.863 +      DELETE FROM "direct_voter"
   1.864 +        USING (
   1.865 +          SELECT "direct_voter"."member_id"
   1.866 +          FROM "direct_voter"
   1.867 +          JOIN "member" ON "direct_voter"."member_id" = "member"."id"
   1.868 +          LEFT JOIN "privilege"
   1.869 +          ON "privilege"."unit_id" = "unit_id_v"
   1.870 +          AND "privilege"."member_id" = "direct_voter"."member_id"
   1.871 +          LEFT JOIN "issue_privilege"
   1.872 +          ON "issue_privilege"."issue_id" = "issue_id_p"
   1.873 +          AND "issue_privilege"."member_id" = "direct_voter"."member_id"
   1.874 +          WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
   1.875 +            "member"."active" = FALSE OR
   1.876 +            COALESCE(
   1.877 +              "issue_privilege"."voting_right",
   1.878 +              "privilege"."voting_right",
   1.879 +              FALSE
   1.880 +            ) = FALSE
   1.881 +          )
   1.882 +        ) AS "subquery"
   1.883 +        WHERE "direct_voter"."issue_id" = "issue_id_p"
   1.884 +        AND "direct_voter"."member_id" = "subquery"."member_id";
   1.885 +      -- consider voting weight and delegations:
   1.886 +      UPDATE "direct_voter" SET "ownweight" = "privilege"."weight"
   1.887 +        FROM "privilege"
   1.888 +        WHERE "issue_id" = "issue_id_p"
   1.889 +        AND "privilege"."unit_id" = "unit_id_v"
   1.890 +        AND "privilege"."member_id" = "direct_voter"."member_id";
   1.891 +      UPDATE "direct_voter" SET "ownweight" = "issue_privilege"."weight"
   1.892 +        FROM "issue_privilege"
   1.893 +        WHERE "direct_voter"."issue_id" = "issue_id_p"
   1.894 +        AND "issue_privilege"."issue_id" = "issue_id_p"
   1.895 +        AND "issue_privilege"."member_id" = "direct_voter"."member_id";
   1.896 +      PERFORM "add_vote_delegations"("issue_id_p");
   1.897 +      -- mark first preferences:
   1.898 +      UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
   1.899 +        FROM (
   1.900 +          SELECT
   1.901 +            "vote"."initiative_id",
   1.902 +            "vote"."member_id",
   1.903 +            CASE WHEN "vote"."grade" > 0 THEN
   1.904 +              CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
   1.905 +            ELSE NULL
   1.906 +            END AS "first_preference"
   1.907 +          FROM "vote"
   1.908 +          JOIN "initiative"  -- NOTE: due to missing index on issue_id
   1.909 +          ON "vote"."issue_id" = "initiative"."issue_id"
   1.910 +          JOIN "vote" AS "agg"
   1.911 +          ON "initiative"."id" = "agg"."initiative_id"
   1.912 +          AND "vote"."member_id" = "agg"."member_id"
   1.913 +          GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
   1.914 +        ) AS "subquery"
   1.915 +        WHERE "vote"."issue_id" = "issue_id_p"
   1.916 +        AND "vote"."initiative_id" = "subquery"."initiative_id"
   1.917 +        AND "vote"."member_id" = "subquery"."member_id";
   1.918 +      -- finish overriding protection triggers (avoids garbage):
   1.919 +      DELETE FROM "temporary_transaction_data"
   1.920 +        WHERE "key" = 'override_protection_triggers';
   1.921 +      -- materialize battle_view:
   1.922 +      -- NOTE: "closed" column of issue must be set at this point
   1.923 +      DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
   1.924 +      INSERT INTO "battle" (
   1.925 +        "issue_id",
   1.926 +        "winning_initiative_id", "losing_initiative_id",
   1.927 +        "count"
   1.928 +      ) SELECT
   1.929 +        "issue_id",
   1.930 +        "winning_initiative_id", "losing_initiative_id",
   1.931 +        "count"
   1.932 +        FROM "battle_view" WHERE "issue_id" = "issue_id_p";
   1.933 +      -- set voter count:
   1.934 +      UPDATE "issue" SET
   1.935 +        "voter_count" = (
   1.936 +          SELECT coalesce(sum("weight"), 0)
   1.937 +          FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
   1.938 +        )
   1.939 +        WHERE "id" = "issue_id_p";
   1.940 +      -- copy "positive_votes" and "negative_votes" from "battle" table:
   1.941 +      -- NOTE: "first_preference_votes" is set to a default of 0 at this step
   1.942 +      UPDATE "initiative" SET
   1.943 +        "first_preference_votes" = 0,
   1.944 +        "positive_votes" = "battle_win"."count",
   1.945 +        "negative_votes" = "battle_lose"."count"
   1.946 +        FROM "battle" AS "battle_win", "battle" AS "battle_lose"
   1.947 +        WHERE
   1.948 +          "battle_win"."issue_id" = "issue_id_p" AND
   1.949 +          "battle_win"."winning_initiative_id" = "initiative"."id" AND
   1.950 +          "battle_win"."losing_initiative_id" ISNULL AND
   1.951 +          "battle_lose"."issue_id" = "issue_id_p" AND
   1.952 +          "battle_lose"."losing_initiative_id" = "initiative"."id" AND
   1.953 +          "battle_lose"."winning_initiative_id" ISNULL;
   1.954 +      -- calculate "first_preference_votes":
   1.955 +      -- NOTE: will only set values not equal to zero
   1.956 +      UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum"
   1.957 +        FROM (
   1.958 +          SELECT "vote"."initiative_id", sum("direct_voter"."weight")
   1.959 +          FROM "vote" JOIN "direct_voter"
   1.960 +          ON "vote"."issue_id" = "direct_voter"."issue_id"
   1.961 +          AND "vote"."member_id" = "direct_voter"."member_id"
   1.962 +          WHERE "vote"."first_preference"
   1.963 +          GROUP BY "vote"."initiative_id"
   1.964 +        ) AS "subquery"
   1.965 +        WHERE "initiative"."issue_id" = "issue_id_p"
   1.966 +        AND "initiative"."admitted"
   1.967 +        AND "initiative"."id" = "subquery"."initiative_id";
   1.968 +    END;
   1.969 +  $$;
   1.970 +
   1.971 +COMMIT;

Impressum / About Us