1.1 --- a/update/core-update.v3.2.2-v4.0.0.sql	Sat Sep 16 21:20:38 2017 +0200
     1.2 +++ b/update/core-update.v3.2.2-v4.0.0.sql	Sun Sep 17 01:48:45 2017 +0200
     1.3 @@ -151,6 +151,43 @@
     1.4      "name", "identification");
     1.5   
     1.6  
     1.7 +CREATE TABLE "cross_unit_verification" (
     1.8 +        PRIMARY KEY ("unit_id", "trusted_unit_id"),
     1.9 +        "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.10 +        "trusted_unit_id"       INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
    1.11 +
    1.12 +COMMENT ON TABLE "cross_unit_verification" IS 'Enables member verifications in a unit to count for other units as well (non-transitively)';
    1.13 +
    1.14 +COMMENT ON COLUMN "cross_unit_verification"."unit_id"         IS 'Unit for which verification in "trusted_unit_id" is also taken into account';
    1.15 +COMMENT ON COLUMN "cross_unit_verification"."trusted_unit_id" IS 'Unit where verification exists';
    1.16 +
    1.17 +
    1.18 +CREATE TABLE "verification" (
    1.19 +        "unit_id"               INT4            REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.20 +        "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.21 +        "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
    1.22 +        --"expiry"                TIMESTAMPTZ,  -- TODO
    1.23 +        "comment"               TEXT );
    1.24 +CREATE INDEX "verification_unit_id_idx" ON "verification" ("unit_id");
    1.25 +CREATE INDEX "verification_member_id_idx" ON "verification" ("member_id");
    1.26 +
    1.27 +COMMENT ON TABLE "verification" IS 'Member verification status';
    1.28 +
    1.29 +
    1.30 +CREATE VIEW "verified_privilege" AS
    1.31 +  SELECT DISTINCT "privilege".* FROM "privilege"
    1.32 +  LEFT JOIN "cross_unit_verification" AS "cross"
    1.33 +    ON "privilege"."unit_id" = "cross"."unit_id"
    1.34 +  LEFT JOIN "verification"
    1.35 +    ON (
    1.36 +      "privilege"."unit_id" = "verification"."unit_id" OR
    1.37 +      "cross"."trusted_unit_id" = "verification"."unit_id"
    1.38 +    )
    1.39 +    AND "privilege"."member_id" = "verification"."member_id";
    1.40 +
    1.41 +COMMENT ON VIEW "verified_privilege" IS 'View on privilege table containing only entries where verification of member in unit has been done';
    1.42 +
    1.43 +
    1.44  CREATE VIEW "member_eligible_to_be_notified" AS
    1.45    SELECT * FROM "member"
    1.46    WHERE "activated" NOTNULL AND "locked" = FALSE;
    1.47 @@ -214,10 +251,10 @@
    1.48      "newsletter"."id" AS "newsletter_id",
    1.49      "newsletter"."published"
    1.50    FROM "newsletter" CROSS JOIN "member_eligible_to_be_notified" AS "member"
    1.51 -  LEFT JOIN "privilege" ON
    1.52 -    "privilege"."member_id" = "member"."id" AND
    1.53 -    "privilege"."unit_id" = "newsletter"."unit_id" AND
    1.54 -    "privilege"."voting_right" = TRUE
    1.55 +  LEFT JOIN "verified_privilege" ON
    1.56 +    "verified_privilege"."member_id" = "member"."id" AND
    1.57 +    "verified_privilege"."unit_id" = "newsletter"."unit_id" AND
    1.58 +    "verified_privilege"."voting_right" = TRUE
    1.59    LEFT JOIN "subscription" ON
    1.60      "subscription"."member_id" = "member"."id" AND
    1.61      "subscription"."unit_id" = "newsletter"."unit_id"
    1.62 @@ -228,7 +265,7 @@
    1.63      "newsletter"."include_all_members" = TRUE )
    1.64    AND (
    1.65      "newsletter"."unit_id" ISNULL OR
    1.66 -    "privilege"."member_id" NOTNULL OR
    1.67 +    "verified_privilege"."member_id" NOTNULL OR
    1.68      "subscription"."member_id" NOTNULL );
    1.69  
    1.70  COMMENT ON VIEW "newsletter_to_send" IS 'List of "newsletter_id"s for each member that are due to be sent out';
    1.71 @@ -452,7 +489,7 @@
    1.72  
    1.73  CREATE INDEX "unit_location_idx" ON "unit" USING gist ((GeoJSON_to_ecluster("location")));
    1.74  
    1.75 -COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege" (only active members counted)';
    1.76 +COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in view "verified_privilege" (only active members counted)';
    1.77  COMMENT ON COLUMN "unit"."location"     IS 'Geographic location on earth as GeoJSON object indicating valid coordinates for initiatives of issues with this policy';
    1.78   
    1.79  
    1.80 @@ -2057,10 +2094,10 @@
    1.81      OR "delegation"."issue_id" = "issue"."id"
    1.82    JOIN "member"
    1.83      ON "delegation"."truster_id" = "member"."id"
    1.84 -  JOIN "privilege"
    1.85 -    ON "area"."unit_id" = "privilege"."unit_id"
    1.86 -    AND "delegation"."truster_id" = "privilege"."member_id"
    1.87 -  WHERE "member"."active" AND "privilege"."voting_right"
    1.88 +  JOIN "verified_privilege"
    1.89 +    ON "area"."unit_id" = "verified_privilege"."unit_id"
    1.90 +    AND "delegation"."truster_id" = "verified_privilege"."member_id"
    1.91 +  WHERE "member"."active" AND "verified_privilege"."voting_right"
    1.92    ORDER BY
    1.93      "issue"."id",
    1.94      "delegation"."truster_id",
    1.95 @@ -2071,10 +2108,10 @@
    1.96    SELECT
    1.97      "unit"."id"   AS "unit_id",
    1.98      "member"."id" AS "member_id"
    1.99 -  FROM "privilege"
   1.100 -  JOIN "unit"   ON "unit_id"     = "privilege"."unit_id"
   1.101 -  JOIN "member" ON "member"."id" = "privilege"."member_id"
   1.102 -  WHERE "privilege"."voting_right" AND "member"."active";
   1.103 +  FROM "verified_privilege"
   1.104 +  JOIN "unit"   ON "unit_id"     = "verified_privilege"."unit_id"
   1.105 +  JOIN "member" ON "member"."id" = "verified_privilege"."member_id"
   1.106 +  WHERE "verified_privilege"."voting_right" AND "member"."active";
   1.107  
   1.108  COMMENT ON VIEW "unit_member" IS 'Active members with voting right in a unit';
   1.109   
   1.110 @@ -2299,11 +2336,11 @@
   1.111          END IF;
   1.112          "output_row"."scope_in" := "output_row"."scope_out";
   1.113          "output_row"."member_valid" := EXISTS (
   1.114 -          SELECT NULL FROM "member" JOIN "privilege"
   1.115 -          ON "privilege"."member_id" = "member"."id"
   1.116 -          AND "privilege"."unit_id" = "unit_id_v"
   1.117 +          SELECT NULL FROM "member" JOIN "verified_privilege"
   1.118 +          ON "verified_privilege"."member_id" = "member"."id"
   1.119 +          AND "verified_privilege"."unit_id" = "unit_id_v"
   1.120            WHERE "id" = "output_row"."member_id"
   1.121 -          AND "member"."active" AND "privilege"."voting_right"
   1.122 +          AND "member"."active" AND "verified_privilege"."voting_right"
   1.123          );
   1.124          "simulate_here_v" := (
   1.125            "simulate_v" AND
   1.126 @@ -2717,11 +2754,11 @@
   1.127            JOIN "area" ON "issue"."area_id" = "area"."id"
   1.128            JOIN "interest" ON "issue"."id" = "interest"."issue_id"
   1.129            JOIN "member" ON "interest"."member_id" = "member"."id"
   1.130 -          JOIN "privilege"
   1.131 -            ON "privilege"."unit_id" = "area"."unit_id"
   1.132 -            AND "privilege"."member_id" = "member"."id"
   1.133 +          JOIN "verified_privilege"
   1.134 +            ON "verified_privilege"."unit_id" = "area"."unit_id"
   1.135 +            AND "verified_privilege"."member_id" = "member"."id"
   1.136            WHERE "issue"."id" = "issue_id_v"
   1.137 -          AND "member"."active" AND "privilege"."voting_right";
   1.138 +          AND "member"."active" AND "verified_privilege"."voting_right";
   1.139          FOR "member_id_v" IN
   1.140            SELECT "member_id" FROM "direct_interest_snapshot"
   1.141            WHERE "snapshot_id" = "snapshot_id_v"
   1.142 @@ -3573,4 +3610,286 @@
   1.143    ("fully_frozen" ISNULL OR "full_freeze_snapshot_id" NOTNULL) );
   1.144  
   1.145  
   1.146 +CREATE OR REPLACE VIEW "unit_delegation" AS
   1.147 +  SELECT
   1.148 +    "unit"."id" AS "unit_id",
   1.149 +    "delegation"."id",
   1.150 +    "delegation"."truster_id",
   1.151 +    "delegation"."trustee_id",
   1.152 +    "delegation"."scope"
   1.153 +  FROM "unit"
   1.154 +  JOIN "delegation"
   1.155 +    ON "delegation"."unit_id" = "unit"."id"
   1.156 +  JOIN "member"
   1.157 +    ON "delegation"."truster_id" = "member"."id"
   1.158 +  JOIN "verified_privilege"
   1.159 +    ON "delegation"."unit_id" = "verified_privilege"."unit_id"
   1.160 +    AND "delegation"."truster_id" = "verified_privilege"."member_id"
   1.161 +  WHERE "member"."active" AND "verified_privilege"."voting_right";
   1.162 +
   1.163 +CREATE OR REPLACE VIEW "area_delegation" AS
   1.164 +  SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
   1.165 +    "area"."id" AS "area_id",
   1.166 +    "delegation"."id",
   1.167 +    "delegation"."truster_id",
   1.168 +    "delegation"."trustee_id",
   1.169 +    "delegation"."scope"
   1.170 +  FROM "area"
   1.171 +  JOIN "delegation"
   1.172 +    ON "delegation"."unit_id" = "area"."unit_id"
   1.173 +    OR "delegation"."area_id" = "area"."id"
   1.174 +  JOIN "member"
   1.175 +    ON "delegation"."truster_id" = "member"."id"
   1.176 +  JOIN "verified_privilege"
   1.177 +    ON "area"."unit_id" = "verified_privilege"."unit_id"
   1.178 +    AND "delegation"."truster_id" = "verified_privilege"."member_id"
   1.179 +  WHERE "member"."active" AND "verified_privilege"."voting_right"
   1.180 +  ORDER BY
   1.181 +    "area"."id",
   1.182 +    "delegation"."truster_id",
   1.183 +    "delegation"."scope" DESC;
   1.184 +
   1.185 +CREATE OR REPLACE VIEW "event_for_notification" AS
   1.186 +  SELECT
   1.187 +    "member"."id" AS "recipient_id",
   1.188 +    "event".*
   1.189 +  FROM "member" CROSS JOIN "event"
   1.190 +  JOIN "issue" ON "issue"."id" = "event"."issue_id"
   1.191 +  JOIN "area" ON "area"."id" = "issue"."area_id"
   1.192 +  LEFT JOIN "verified_privilege" ON
   1.193 +    "verified_privilege"."member_id" = "member"."id" AND
   1.194 +    "verified_privilege"."unit_id" = "area"."unit_id" AND
   1.195 +    "verified_privilege"."voting_right" = TRUE
   1.196 +  LEFT JOIN "subscription" ON
   1.197 +    "subscription"."member_id" = "member"."id" AND
   1.198 +    "subscription"."unit_id" = "area"."unit_id"
   1.199 +  LEFT JOIN "ignored_area" ON
   1.200 +    "ignored_area"."member_id" = "member"."id" AND
   1.201 +    "ignored_area"."area_id" = "issue"."area_id"
   1.202 +  LEFT JOIN "interest" ON
   1.203 +    "interest"."member_id" = "member"."id" AND
   1.204 +    "interest"."issue_id" = "event"."issue_id"
   1.205 +  LEFT JOIN "supporter" ON
   1.206 +    "supporter"."member_id" = "member"."id" AND
   1.207 +    "supporter"."initiative_id" = "event"."initiative_id"
   1.208 +  WHERE (
   1.209 +    "verified_privilege"."member_id" NOTNULL OR
   1.210 +    "subscription"."member_id" NOTNULL )
   1.211 +  AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
   1.212 +  AND (
   1.213 +    "event"."event" = 'issue_state_changed'::"event_type" OR
   1.214 +    ( "event"."event" = 'initiative_revoked'::"event_type" AND
   1.215 +      "supporter"."member_id" NOTNULL ) );
   1.216 +
   1.217 +CREATE OR REPLACE FUNCTION "featured_initiative"
   1.218 +  ( "recipient_id_p" "member"."id"%TYPE,
   1.219 +    "area_id_p"      "area"."id"%TYPE )
   1.220 +  RETURNS SETOF "initiative"."id"%TYPE
   1.221 +  LANGUAGE 'plpgsql' STABLE AS $$
   1.222 +    DECLARE
   1.223 +      "counter_v"         "member"."notification_counter"%TYPE;
   1.224 +      "sample_size_v"     "member"."notification_sample_size"%TYPE;
   1.225 +      "initiative_id_ary" INT4[];  --"initiative"."id"%TYPE[]
   1.226 +      "match_v"           BOOLEAN;
   1.227 +      "member_id_v"       "member"."id"%TYPE;
   1.228 +      "seed_v"            TEXT;
   1.229 +      "initiative_id_v"   "initiative"."id"%TYPE;
   1.230 +    BEGIN
   1.231 +      SELECT "notification_counter", "notification_sample_size"
   1.232 +        INTO "counter_v", "sample_size_v"
   1.233 +        FROM "member" WHERE "id" = "recipient_id_p";
   1.234 +      IF COALESCE("sample_size_v" <= 0, TRUE) THEN
   1.235 +        RETURN;
   1.236 +      END IF;
   1.237 +      "initiative_id_ary" := '{}';
   1.238 +      LOOP
   1.239 +        "match_v" := FALSE;
   1.240 +        FOR "member_id_v", "seed_v" IN
   1.241 +          SELECT * FROM (
   1.242 +            SELECT DISTINCT
   1.243 +              "supporter"."member_id",
   1.244 +              md5(
   1.245 +                "recipient_id_p" || '-' ||
   1.246 +                "counter_v"      || '-' ||
   1.247 +                "area_id_p"      || '-' ||
   1.248 +                "supporter"."member_id"
   1.249 +              ) AS "seed"
   1.250 +            FROM "supporter"
   1.251 +            JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
   1.252 +            JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   1.253 +            WHERE "supporter"."member_id" != "recipient_id_p"
   1.254 +            AND "issue"."area_id" = "area_id_p"
   1.255 +            AND "issue"."state" IN ('admission', 'discussion', 'verification')
   1.256 +          ) AS "subquery"
   1.257 +          ORDER BY "seed"
   1.258 +        LOOP
   1.259 +          SELECT "initiative"."id" INTO "initiative_id_v"
   1.260 +            FROM "initiative"
   1.261 +            JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
   1.262 +            JOIN "area" ON "area"."id" = "issue"."area_id"
   1.263 +            JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
   1.264 +            LEFT JOIN "supporter" AS "self_support" ON
   1.265 +              "self_support"."initiative_id" = "initiative"."id" AND
   1.266 +              "self_support"."member_id" = "recipient_id_p"
   1.267 +            LEFT JOIN "verified_privilege" ON
   1.268 +              "verified_privilege"."member_id" = "recipient_id_p" AND
   1.269 +              "verified_privilege"."unit_id" = "area"."unit_id" AND
   1.270 +              "verified_privilege"."voting_right" = TRUE
   1.271 +            LEFT JOIN "subscription" ON
   1.272 +              "subscription"."member_id" = "recipient_id_p" AND
   1.273 +              "subscription"."unit_id" = "area"."unit_id"
   1.274 +            LEFT JOIN "ignored_initiative" ON
   1.275 +              "ignored_initiative"."member_id" = "recipient_id_p" AND
   1.276 +              "ignored_initiative"."initiative_id" = "initiative"."id"
   1.277 +            WHERE "supporter"."member_id" = "member_id_v"
   1.278 +            AND "issue"."area_id" = "area_id_p"
   1.279 +            AND "issue"."state" IN ('admission', 'discussion', 'verification')
   1.280 +            AND "initiative"."revoked" ISNULL
   1.281 +            AND "self_support"."member_id" ISNULL
   1.282 +            AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
   1.283 +            AND (
   1.284 +              "verified_privilege"."member_id" NOTNULL OR
   1.285 +              "subscription"."member_id" NOTNULL )
   1.286 +            AND "ignored_initiative"."member_id" ISNULL
   1.287 +            AND NOT EXISTS (
   1.288 +              SELECT NULL FROM "draft"
   1.289 +              JOIN "ignored_member" ON
   1.290 +                "ignored_member"."member_id" = "recipient_id_p" AND
   1.291 +                "ignored_member"."other_member_id" = "draft"."author_id"
   1.292 +              WHERE "draft"."initiative_id" = "initiative"."id"
   1.293 +            )
   1.294 +            ORDER BY md5("seed_v" || '-' || "initiative"."id")
   1.295 +            LIMIT 1;
   1.296 +          IF FOUND THEN
   1.297 +            "match_v" := TRUE;
   1.298 +            RETURN NEXT "initiative_id_v";
   1.299 +            IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
   1.300 +              RETURN;
   1.301 +            END IF;
   1.302 +            "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
   1.303 +          END IF;
   1.304 +        END LOOP;
   1.305 +        EXIT WHEN NOT "match_v";
   1.306 +      END LOOP;
   1.307 +      RETURN;
   1.308 +    END;
   1.309 +  $$;
   1.310 +
   1.311 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
   1.312 +  RETURNS VOID
   1.313 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   1.314 +    DECLARE
   1.315 +      "area_id_v"   "area"."id"%TYPE;
   1.316 +      "unit_id_v"   "unit"."id"%TYPE;
   1.317 +      "member_id_v" "member"."id"%TYPE;
   1.318 +    BEGIN
   1.319 +      PERFORM "require_transaction_isolation"();
   1.320 +      SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
   1.321 +      SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
   1.322 +      -- override protection triggers:
   1.323 +      INSERT INTO "temporary_transaction_data" ("key", "value")
   1.324 +        VALUES ('override_protection_triggers', TRUE::TEXT);
   1.325 +      -- delete timestamp of voting comment:
   1.326 +      UPDATE "direct_voter" SET "comment_changed" = NULL
   1.327 +        WHERE "issue_id" = "issue_id_p";
   1.328 +      -- delete delegating votes (in cases of manual reset of issue state):
   1.329 +      DELETE FROM "delegating_voter"
   1.330 +        WHERE "issue_id" = "issue_id_p";
   1.331 +      -- delete votes from non-privileged voters:
   1.332 +      DELETE FROM "direct_voter"
   1.333 +        USING (
   1.334 +          SELECT
   1.335 +            "direct_voter"."member_id"
   1.336 +          FROM "direct_voter"
   1.337 +          JOIN "member" ON "direct_voter"."member_id" = "member"."id"
   1.338 +          LEFT JOIN "verified_privilege"
   1.339 +          ON "verified_privilege"."unit_id" = "unit_id_v"
   1.340 +          AND "verified_privilege"."member_id" = "direct_voter"."member_id"
   1.341 +          WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
   1.342 +            "member"."active" = FALSE OR
   1.343 +            "verified_privilege"."voting_right" ISNULL OR
   1.344 +            "verified_privilege"."voting_right" = FALSE
   1.345 +          )
   1.346 +        ) AS "subquery"
   1.347 +        WHERE "direct_voter"."issue_id" = "issue_id_p"
   1.348 +        AND "direct_voter"."member_id" = "subquery"."member_id";
   1.349 +      -- consider delegations:
   1.350 +      UPDATE "direct_voter" SET "weight" = 1
   1.351 +        WHERE "issue_id" = "issue_id_p";
   1.352 +      PERFORM "add_vote_delegations"("issue_id_p");
   1.353 +      -- mark first preferences:
   1.354 +      UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
   1.355 +        FROM (
   1.356 +          SELECT
   1.357 +            "vote"."initiative_id",
   1.358 +            "vote"."member_id",
   1.359 +            CASE WHEN "vote"."grade" > 0 THEN
   1.360 +              CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
   1.361 +            ELSE NULL
   1.362 +            END AS "first_preference"
   1.363 +          FROM "vote"
   1.364 +          JOIN "initiative"  -- NOTE: due to missing index on issue_id
   1.365 +          ON "vote"."issue_id" = "initiative"."issue_id"
   1.366 +          JOIN "vote" AS "agg"
   1.367 +          ON "initiative"."id" = "agg"."initiative_id"
   1.368 +          AND "vote"."member_id" = "agg"."member_id"
   1.369 +          GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
   1.370 +        ) AS "subquery"
   1.371 +        WHERE "vote"."issue_id" = "issue_id_p"
   1.372 +        AND "vote"."initiative_id" = "subquery"."initiative_id"
   1.373 +        AND "vote"."member_id" = "subquery"."member_id";
   1.374 +      -- finish overriding protection triggers (avoids garbage):
   1.375 +      DELETE FROM "temporary_transaction_data"
   1.376 +        WHERE "key" = 'override_protection_triggers';
   1.377 +      -- materialize battle_view:
   1.378 +      -- NOTE: "closed" column of issue must be set at this point
   1.379 +      DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
   1.380 +      INSERT INTO "battle" (
   1.381 +        "issue_id",
   1.382 +        "winning_initiative_id", "losing_initiative_id",
   1.383 +        "count"
   1.384 +      ) SELECT
   1.385 +        "issue_id",
   1.386 +        "winning_initiative_id", "losing_initiative_id",
   1.387 +        "count"
   1.388 +        FROM "battle_view" WHERE "issue_id" = "issue_id_p";
   1.389 +      -- set voter count:
   1.390 +      UPDATE "issue" SET
   1.391 +        "voter_count" = (
   1.392 +          SELECT coalesce(sum("weight"), 0)
   1.393 +          FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
   1.394 +        )
   1.395 +        WHERE "id" = "issue_id_p";
   1.396 +      -- copy "positive_votes" and "negative_votes" from "battle" table:
   1.397 +      -- NOTE: "first_preference_votes" is set to a default of 0 at this step
   1.398 +      UPDATE "initiative" SET
   1.399 +        "first_preference_votes" = 0,
   1.400 +        "positive_votes" = "battle_win"."count",
   1.401 +        "negative_votes" = "battle_lose"."count"
   1.402 +        FROM "battle" AS "battle_win", "battle" AS "battle_lose"
   1.403 +        WHERE
   1.404 +          "battle_win"."issue_id" = "issue_id_p" AND
   1.405 +          "battle_win"."winning_initiative_id" = "initiative"."id" AND
   1.406 +          "battle_win"."losing_initiative_id" ISNULL AND
   1.407 +          "battle_lose"."issue_id" = "issue_id_p" AND
   1.408 +          "battle_lose"."losing_initiative_id" = "initiative"."id" AND
   1.409 +          "battle_lose"."winning_initiative_id" ISNULL;
   1.410 +      -- calculate "first_preference_votes":
   1.411 +      -- NOTE: will only set values not equal to zero
   1.412 +      UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum"
   1.413 +        FROM (
   1.414 +          SELECT "vote"."initiative_id", sum("direct_voter"."weight")
   1.415 +          FROM "vote" JOIN "direct_voter"
   1.416 +          ON "vote"."issue_id" = "direct_voter"."issue_id"
   1.417 +          AND "vote"."member_id" = "direct_voter"."member_id"
   1.418 +          WHERE "vote"."first_preference"
   1.419 +          GROUP BY "vote"."initiative_id"
   1.420 +        ) AS "subquery"
   1.421 +        WHERE "initiative"."issue_id" = "issue_id_p"
   1.422 +        AND "initiative"."admitted"
   1.423 +        AND "initiative"."id" = "subquery"."initiative_id";
   1.424 +    END;
   1.425 +  $$;
   1.426 +
   1.427 +
   1.428  COMMIT;