liquid_feedback_core

changeset 436:34cc98defa8b

2 bugfixes: Error in core-update.v2.2.6-v3.0.1.sql fixed; Always set "initiative"."first_preference_votes" (also if no votes are found)
author jbe
date Wed Jul 16 16:28:35 2014 +0200 (2014-07-16)
parents e3dd2c3629db
children eb12a069063c
files core.sql update/core-update.v2.2.6-v3.0.1.sql update/core-update.v3.0.2-v3.0.3.sql
line diff
     1.1 --- a/core.sql	Fri Jul 11 01:44:25 2014 +0200
     1.2 +++ b/core.sql	Wed Jul 16 16:28:35 2014 +0200
     1.3 @@ -7,7 +7,7 @@
     1.4  BEGIN;
     1.5  
     1.6  CREATE VIEW "liquid_feedback_version" AS
     1.7 -  SELECT * FROM (VALUES ('3.0.2', 3, 0, 2))
     1.8 +  SELECT * FROM (VALUES ('3.0.3', 3, 0, 3))
     1.9    AS "subquery"("string", "major", "minor", "revision");
    1.10  
    1.11  
    1.12 @@ -3806,7 +3806,7 @@
    1.13          WHERE "id" = "issue_id_p";
    1.14        -- calculate "first_preference_votes":
    1.15        UPDATE "initiative"
    1.16 -        SET "first_preference_votes" = coalesce("subquery"."sum", 0)
    1.17 +        SET "first_preference_votes" = "subquery"."sum"
    1.18          FROM (
    1.19            SELECT "vote"."initiative_id", sum("direct_voter"."weight")
    1.20            FROM "vote" JOIN "direct_voter"
    1.21 @@ -3818,6 +3818,10 @@
    1.22          WHERE "initiative"."issue_id" = "issue_id_p"
    1.23          AND "initiative"."admitted"
    1.24          AND "initiative"."id" = "subquery"."initiative_id";
    1.25 +      UPDATE "initiative" SET "first_preference_votes" = 0
    1.26 +        WHERE "issue_id" = "issue_id_p"
    1.27 +        AND "initiative"."admitted"
    1.28 +        AND "first_preference_votes" ISNULL;
    1.29        -- copy "positive_votes" and "negative_votes" from "battle" table:
    1.30        UPDATE "initiative" SET
    1.31          "positive_votes" = "battle_win"."count",
     2.1 --- a/update/core-update.v2.2.6-v3.0.1.sql	Fri Jul 11 01:44:25 2014 +0200
     2.2 +++ b/update/core-update.v2.2.6-v3.0.1.sql	Wed Jul 16 16:28:35 2014 +0200
     2.3 @@ -336,4 +336,235 @@
     2.4      END;
     2.5    $$;
     2.6  
     2.7 +ALTER TABLE "initiative" ADD COLUMN "first_preference_votes" INT4;
     2.8 +
     2.9 +ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results";
    2.10 +ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
    2.11 +          ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
    2.12 +          ( "first_preference_votes" ISNULL AND
    2.13 +            "positive_votes" ISNULL AND "negative_votes" ISNULL AND
    2.14 +            "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
    2.15 +            "schulze_rank" ISNULL AND
    2.16 +            "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
    2.17 +            "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
    2.18 +            "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) );
    2.19 +
    2.20 +COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice';
    2.21 +COMMENT ON COLUMN "initiative"."positive_votes"         IS 'Number of direct and delegating voters who ranked this initiative better than the status quo';
    2.22 +COMMENT ON COLUMN "initiative"."negative_votes"         IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo';
    2.23 +
    2.24 +-- UPDATE TABLE "vote" SET "grade" = 0 WHERE "grade" ISNULL;  -- should not be necessary
    2.25 +ALTER TABLE "vote" ALTER COLUMN "grade" SET NOT NULL;
    2.26 +
    2.27 +ALTER TABLE "vote" ADD COLUMN "first_preference" BOOLEAN;
    2.28 +
    2.29 +ALTER TABLE "vote" ADD
    2.30 +        CONSTRAINT "first_preference_flag_only_set_on_positive_grades"
    2.31 +        CHECK ("grade" > 0 OR "first_preference" ISNULL);
    2.32 +
    2.33 +COMMENT ON COLUMN "vote"."first_preference" IS 'Value is automatically set after voting is finished. For positive grades, this value is set to true for the highest (i.e. best) grade.';
    2.34 + 
    2.35 +INSERT INTO "temporary_transaction_data" ("key", "value")
    2.36 +  VALUES ('override_protection_triggers', TRUE::TEXT);
    2.37 +
    2.38 +UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
    2.39 +  FROM (
    2.40 +    SELECT
    2.41 +      "vote"."initiative_id",
    2.42 +      "vote"."member_id",
    2.43 +      CASE WHEN "vote"."grade" > 0 THEN
    2.44 +        CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
    2.45 +      ELSE NULL
    2.46 +      END AS "first_preference"
    2.47 +    FROM "vote"
    2.48 +    JOIN "initiative"  -- NOTE: due to missing index on issue_id
    2.49 +    ON "vote"."issue_id" = "initiative"."issue_id"
    2.50 +    JOIN "vote" AS "agg"
    2.51 +    ON "initiative"."id" = "agg"."initiative_id"
    2.52 +    AND "vote"."member_id" = "agg"."member_id"
    2.53 +    GROUP BY "vote"."initiative_id", "vote"."member_id"
    2.54 +  ) AS "subquery"
    2.55 +  WHERE "vote"."initiative_id" = "subquery"."initiative_id"
    2.56 +  AND "vote"."member_id" = "subquery"."member_id";
    2.57 +
    2.58 +DELETE FROM "temporary_transaction_data"
    2.59 +  WHERE "key" = 'override_protection_triggers';
    2.60 +
    2.61 +UPDATE "initiative"
    2.62 +  SET "first_preference_votes" = coalesce("subquery"."sum", 0)
    2.63 +  FROM (
    2.64 +    SELECT "vote"."initiative_id", sum("direct_voter"."weight")
    2.65 +    FROM "vote" JOIN "direct_voter"
    2.66 +    ON "vote"."issue_id" = "direct_voter"."issue_id"
    2.67 +    AND "vote"."member_id" = "direct_voter"."member_id"
    2.68 +    WHERE "vote"."first_preference"
    2.69 +    GROUP BY "vote"."initiative_id"
    2.70 +  ) AS "subquery"
    2.71 +  WHERE "initiative"."admitted"
    2.72 +  AND "initiative"."id" = "subquery"."initiative_id";
    2.73 +
    2.74 +-- reconstruct battle data (originating from LiquidFeedback Core before v2.0.0)
    2.75 +-- to avoid future data loss when executing "clean_issue" to delete voting data:
    2.76 +INSERT INTO "battle" (
    2.77 +    "issue_id",
    2.78 +    "winning_initiative_id",
    2.79 +    "losing_initiative_id",
    2.80 +    "count"
    2.81 +  ) SELECT
    2.82 +    "battle_view"."issue_id",
    2.83 +    "battle_view"."winning_initiative_id",
    2.84 +    "battle_view"."losing_initiative_id",
    2.85 +    "battle_view"."count"
    2.86 +  FROM (
    2.87 +    SELECT
    2.88 +      "issue"."id" AS "issue_id",
    2.89 +      "winning_initiative"."id" AS "winning_initiative_id",
    2.90 +      "losing_initiative"."id" AS "losing_initiative_id",
    2.91 +      sum(
    2.92 +        CASE WHEN
    2.93 +          coalesce("better_vote"."grade", 0) >
    2.94 +          coalesce("worse_vote"."grade", 0)
    2.95 +        THEN "direct_voter"."weight" ELSE 0 END
    2.96 +      ) AS "count"
    2.97 +    FROM "issue"
    2.98 +    LEFT JOIN "direct_voter"
    2.99 +    ON "issue"."id" = "direct_voter"."issue_id"
   2.100 +    JOIN "battle_participant" AS "winning_initiative"
   2.101 +      ON "issue"."id" = "winning_initiative"."issue_id"
   2.102 +    JOIN "battle_participant" AS "losing_initiative"
   2.103 +      ON "issue"."id" = "losing_initiative"."issue_id"
   2.104 +    LEFT JOIN "vote" AS "better_vote"
   2.105 +      ON "direct_voter"."member_id" = "better_vote"."member_id"
   2.106 +      AND "winning_initiative"."id" = "better_vote"."initiative_id"
   2.107 +    LEFT JOIN "vote" AS "worse_vote"
   2.108 +      ON "direct_voter"."member_id" = "worse_vote"."member_id"
   2.109 +      AND "losing_initiative"."id" = "worse_vote"."initiative_id"
   2.110 +    WHERE "issue"."state" IN ('finished_with_winner', 'finished_without_winner')
   2.111 +    AND "winning_initiative"."id" != "losing_initiative"."id"
   2.112 +    -- NOTE: comparisons with status-quo are intentionally omitted to mark
   2.113 +    --       issues that were counted prior LiquidFeedback Core v2.0.0
   2.114 +    GROUP BY
   2.115 +      "issue"."id",
   2.116 +      "winning_initiative"."id",
   2.117 +      "losing_initiative"."id"
   2.118 +  ) AS "battle_view"
   2.119 +  LEFT JOIN "battle"
   2.120 +  ON "battle_view"."winning_initiative_id" = "battle"."winning_initiative_id"
   2.121 +  AND "battle_view"."losing_initiative_id" = "battle"."losing_initiative_id"
   2.122 +  WHERE "battle" ISNULL;
   2.123 +
   2.124 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
   2.125 +  RETURNS VOID
   2.126 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   2.127 +    DECLARE
   2.128 +      "area_id_v"   "area"."id"%TYPE;
   2.129 +      "unit_id_v"   "unit"."id"%TYPE;
   2.130 +      "member_id_v" "member"."id"%TYPE;
   2.131 +    BEGIN
   2.132 +      PERFORM "require_transaction_isolation"();
   2.133 +      SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
   2.134 +      SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
   2.135 +      -- override protection triggers:
   2.136 +      INSERT INTO "temporary_transaction_data" ("key", "value")
   2.137 +        VALUES ('override_protection_triggers', TRUE::TEXT);
   2.138 +      -- delete timestamp of voting comment:
   2.139 +      UPDATE "direct_voter" SET "comment_changed" = NULL
   2.140 +        WHERE "issue_id" = "issue_id_p";
   2.141 +      -- delete delegating votes (in cases of manual reset of issue state):
   2.142 +      DELETE FROM "delegating_voter"
   2.143 +        WHERE "issue_id" = "issue_id_p";
   2.144 +      -- delete votes from non-privileged voters:
   2.145 +      DELETE FROM "direct_voter"
   2.146 +        USING (
   2.147 +          SELECT
   2.148 +            "direct_voter"."member_id"
   2.149 +          FROM "direct_voter"
   2.150 +          JOIN "member" ON "direct_voter"."member_id" = "member"."id"
   2.151 +          LEFT JOIN "privilege"
   2.152 +          ON "privilege"."unit_id" = "unit_id_v"
   2.153 +          AND "privilege"."member_id" = "direct_voter"."member_id"
   2.154 +          WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
   2.155 +            "member"."active" = FALSE OR
   2.156 +            "privilege"."voting_right" ISNULL OR
   2.157 +            "privilege"."voting_right" = FALSE
   2.158 +          )
   2.159 +        ) AS "subquery"
   2.160 +        WHERE "direct_voter"."issue_id" = "issue_id_p"
   2.161 +        AND "direct_voter"."member_id" = "subquery"."member_id";
   2.162 +      -- consider delegations:
   2.163 +      UPDATE "direct_voter" SET "weight" = 1
   2.164 +        WHERE "issue_id" = "issue_id_p";
   2.165 +      PERFORM "add_vote_delegations"("issue_id_p");
   2.166 +      -- mark first preferences:
   2.167 +      UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
   2.168 +        FROM (
   2.169 +          SELECT
   2.170 +            "vote"."initiative_id",
   2.171 +            "vote"."member_id",
   2.172 +            CASE WHEN "vote"."grade" > 0 THEN
   2.173 +              CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
   2.174 +            ELSE NULL
   2.175 +            END AS "first_preference"
   2.176 +          FROM "vote"
   2.177 +          JOIN "initiative"  -- NOTE: due to missing index on issue_id
   2.178 +          ON "vote"."issue_id" = "initiative"."issue_id"
   2.179 +          JOIN "vote" AS "agg"
   2.180 +          ON "initiative"."id" = "agg"."initiative_id"
   2.181 +          AND "vote"."member_id" = "agg"."member_id"
   2.182 +          GROUP BY "vote"."initiative_id", "vote"."member_id"
   2.183 +        ) AS "subquery"
   2.184 +        WHERE "vote"."issue_id" = "issue_id_p"
   2.185 +        AND "vote"."initiative_id" = "subquery"."initiative_id"
   2.186 +        AND "vote"."member_id" = "subquery"."member_id";
   2.187 +      -- finish overriding protection triggers (avoids garbage):
   2.188 +      DELETE FROM "temporary_transaction_data"
   2.189 +        WHERE "key" = 'override_protection_triggers';
   2.190 +      -- materialize battle_view:
   2.191 +      -- NOTE: "closed" column of issue must be set at this point
   2.192 +      DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
   2.193 +      INSERT INTO "battle" (
   2.194 +        "issue_id",
   2.195 +        "winning_initiative_id", "losing_initiative_id",
   2.196 +        "count"
   2.197 +      ) SELECT
   2.198 +        "issue_id",
   2.199 +        "winning_initiative_id", "losing_initiative_id",
   2.200 +        "count"
   2.201 +        FROM "battle_view" WHERE "issue_id" = "issue_id_p";
   2.202 +      -- set voter count:
   2.203 +      UPDATE "issue" SET
   2.204 +        "voter_count" = (
   2.205 +          SELECT coalesce(sum("weight"), 0)
   2.206 +          FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
   2.207 +        )
   2.208 +        WHERE "id" = "issue_id_p";
   2.209 +      -- calculate "first_preference_votes":
   2.210 +      UPDATE "initiative"
   2.211 +        SET "first_preference_votes" = coalesce("subquery"."sum", 0)
   2.212 +        FROM (
   2.213 +          SELECT "vote"."initiative_id", sum("direct_voter"."weight")
   2.214 +          FROM "vote" JOIN "direct_voter"
   2.215 +          ON "vote"."issue_id" = "direct_voter"."issue_id"
   2.216 +          AND "vote"."member_id" = "direct_voter"."member_id"
   2.217 +          WHERE "vote"."first_preference"
   2.218 +          GROUP BY "vote"."initiative_id"
   2.219 +        ) AS "subquery"
   2.220 +        WHERE "initiative"."issue_id" = "issue_id_p"
   2.221 +        AND "initiative"."admitted"
   2.222 +        AND "initiative"."id" = "subquery"."initiative_id";
   2.223 +      -- copy "positive_votes" and "negative_votes" from "battle" table:
   2.224 +      UPDATE "initiative" SET
   2.225 +        "positive_votes" = "battle_win"."count",
   2.226 +        "negative_votes" = "battle_lose"."count"
   2.227 +        FROM "battle" AS "battle_win", "battle" AS "battle_lose"
   2.228 +        WHERE
   2.229 +          "battle_win"."issue_id" = "issue_id_p" AND
   2.230 +          "battle_win"."winning_initiative_id" = "initiative"."id" AND
   2.231 +          "battle_win"."losing_initiative_id" ISNULL AND
   2.232 +          "battle_lose"."issue_id" = "issue_id_p" AND
   2.233 +          "battle_lose"."losing_initiative_id" = "initiative"."id" AND
   2.234 +          "battle_lose"."winning_initiative_id" ISNULL;
   2.235 +    END;
   2.236 +  $$;
   2.237 +
   2.238  COMMIT;
     3.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     3.2 +++ b/update/core-update.v3.0.2-v3.0.3.sql	Wed Jul 16 16:28:35 2014 +0200
     3.3 @@ -0,0 +1,275 @@
     3.4 +-- NOTICE: This update script disables the "no_reserve_beat_path" setting for
     3.5 +--         all policies. If this is not intended, please edit this script
     3.6 +--         before applying it to your database.
     3.7 +
     3.8 +BEGIN;
     3.9 +
    3.10 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
    3.11 +  SELECT * FROM (VALUES ('3.0.3', 3, 0, 3))
    3.12 +  AS "subquery"("string", "major", "minor", "revision");
    3.13 +
    3.14 +CREATE FUNCTION "update3_0_3_add_columns_if_missing"()
    3.15 +  RETURNS VOID
    3.16 +  LANGUAGE 'plpgsql' AS $$
    3.17 +    BEGIN
    3.18 +      BEGIN
    3.19 +        ALTER TABLE "initiative" ADD COLUMN "first_preference_votes" INT4;
    3.20 +      EXCEPTION
    3.21 +        WHEN duplicate_column THEN
    3.22 +          RAISE NOTICE 'column "first_preference_votes" of relation "initiative" already exists, skipping';
    3.23 +      END;
    3.24 +      BEGIN
    3.25 +        ALTER TABLE "vote" ADD COLUMN "first_preference" BOOLEAN;
    3.26 +      EXCEPTION
    3.27 +        WHEN duplicate_column THEN
    3.28 +          RAISE NOTICE 'column "first_preference" of relation "vote" already exists, skipping';
    3.29 +      END;
    3.30 +      RETURN;
    3.31 +    END;
    3.32 +  $$;
    3.33 +
    3.34 +SELECT "update3_0_3_add_columns_if_missing"();
    3.35 +
    3.36 +DROP FUNCTION "update3_0_3_add_columns_if_missing"();
    3.37 +
    3.38 +ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results";
    3.39 +ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
    3.40 +          ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
    3.41 +          ( "first_preference_votes" ISNULL AND
    3.42 +            "positive_votes" ISNULL AND "negative_votes" ISNULL AND
    3.43 +            "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
    3.44 +            "schulze_rank" ISNULL AND
    3.45 +            "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
    3.46 +            "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
    3.47 +            "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) );
    3.48 +
    3.49 +COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice';
    3.50 +COMMENT ON COLUMN "initiative"."positive_votes"         IS 'Number of direct and delegating voters who ranked this initiative better than the status quo';
    3.51 +COMMENT ON COLUMN "initiative"."negative_votes"         IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo';
    3.52 +
    3.53 +-- UPDATE TABLE "vote" SET "grade" = 0 WHERE "grade" ISNULL;  -- should not be necessary
    3.54 +ALTER TABLE "vote" ALTER COLUMN "grade" SET NOT NULL;
    3.55 +
    3.56 +ALTER TABLE "vote" DROP CONSTRAINT IF EXISTS "first_preference_flag_only_set_on_positive_grades";
    3.57 +ALTER TABLE "vote" ADD
    3.58 +        CONSTRAINT "first_preference_flag_only_set_on_positive_grades"
    3.59 +        CHECK ("grade" > 0 OR "first_preference" ISNULL);
    3.60 +
    3.61 +COMMENT ON COLUMN "vote"."first_preference" IS 'Value is automatically set after voting is finished. For positive grades, this value is set to true for the highest (i.e. best) grade.';
    3.62 + 
    3.63 +INSERT INTO "temporary_transaction_data" ("key", "value")
    3.64 +  VALUES ('override_protection_triggers', TRUE::TEXT);
    3.65 +
    3.66 +UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
    3.67 +  FROM (
    3.68 +    SELECT
    3.69 +      "vote"."initiative_id",
    3.70 +      "vote"."member_id",
    3.71 +      CASE WHEN "vote"."grade" > 0 THEN
    3.72 +        CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
    3.73 +      ELSE NULL
    3.74 +      END AS "first_preference"
    3.75 +    FROM "vote"
    3.76 +    JOIN "initiative"  -- NOTE: due to missing index on issue_id
    3.77 +    ON "vote"."issue_id" = "initiative"."issue_id"
    3.78 +    JOIN "vote" AS "agg"
    3.79 +    ON "initiative"."id" = "agg"."initiative_id"
    3.80 +    AND "vote"."member_id" = "agg"."member_id"
    3.81 +    GROUP BY "vote"."initiative_id", "vote"."member_id"
    3.82 +  ) AS "subquery"
    3.83 +  WHERE "vote"."initiative_id" = "subquery"."initiative_id"
    3.84 +  AND "vote"."member_id" = "subquery"."member_id";
    3.85 +
    3.86 +DELETE FROM "temporary_transaction_data"
    3.87 +  WHERE "key" = 'override_protection_triggers';
    3.88 +
    3.89 +UPDATE "initiative" SET "first_preference_votes" = NULL
    3.90 +  WHERE "first_preference_votes" = 0;
    3.91 +
    3.92 +UPDATE "initiative"
    3.93 +  SET "first_preference_votes" = "subquery"."sum"
    3.94 +  FROM (
    3.95 +    SELECT "vote"."initiative_id", sum("direct_voter"."weight")
    3.96 +    FROM "vote" JOIN "direct_voter"
    3.97 +    ON "vote"."issue_id" = "direct_voter"."issue_id"
    3.98 +    AND "vote"."member_id" = "direct_voter"."member_id"
    3.99 +    WHERE "vote"."first_preference"
   3.100 +    GROUP BY "vote"."initiative_id"
   3.101 +  ) AS "subquery"
   3.102 +  WHERE "initiative"."admitted"
   3.103 +  AND "initiative"."id" = "subquery"."initiative_id"
   3.104 +  AND "initiative"."first_preference_votes" ISNULL;
   3.105 +
   3.106 +UPDATE "initiative" SET "first_preference_votes" = 0
   3.107 +  WHERE "positive_votes" NOTNULL
   3.108 +  AND "first_preference_votes" ISNULL;
   3.109 +
   3.110 +-- reconstruct battle data (originating from LiquidFeedback Core before v2.0.0)
   3.111 +-- to avoid future data loss when executing "clean_issue" to delete voting data:
   3.112 +INSERT INTO "battle" (
   3.113 +    "issue_id",
   3.114 +    "winning_initiative_id",
   3.115 +    "losing_initiative_id",
   3.116 +    "count"
   3.117 +  ) SELECT
   3.118 +    "battle_view"."issue_id",
   3.119 +    "battle_view"."winning_initiative_id",
   3.120 +    "battle_view"."losing_initiative_id",
   3.121 +    "battle_view"."count"
   3.122 +  FROM (
   3.123 +    SELECT
   3.124 +      "issue"."id" AS "issue_id",
   3.125 +      "winning_initiative"."id" AS "winning_initiative_id",
   3.126 +      "losing_initiative"."id" AS "losing_initiative_id",
   3.127 +      sum(
   3.128 +        CASE WHEN
   3.129 +          coalesce("better_vote"."grade", 0) >
   3.130 +          coalesce("worse_vote"."grade", 0)
   3.131 +        THEN "direct_voter"."weight" ELSE 0 END
   3.132 +      ) AS "count"
   3.133 +    FROM "issue"
   3.134 +    LEFT JOIN "direct_voter"
   3.135 +    ON "issue"."id" = "direct_voter"."issue_id"
   3.136 +    JOIN "battle_participant" AS "winning_initiative"
   3.137 +      ON "issue"."id" = "winning_initiative"."issue_id"
   3.138 +    JOIN "battle_participant" AS "losing_initiative"
   3.139 +      ON "issue"."id" = "losing_initiative"."issue_id"
   3.140 +    LEFT JOIN "vote" AS "better_vote"
   3.141 +      ON "direct_voter"."member_id" = "better_vote"."member_id"
   3.142 +      AND "winning_initiative"."id" = "better_vote"."initiative_id"
   3.143 +    LEFT JOIN "vote" AS "worse_vote"
   3.144 +      ON "direct_voter"."member_id" = "worse_vote"."member_id"
   3.145 +      AND "losing_initiative"."id" = "worse_vote"."initiative_id"
   3.146 +    WHERE "issue"."state" IN ('finished_with_winner', 'finished_without_winner')
   3.147 +    AND "winning_initiative"."id" != "losing_initiative"."id"
   3.148 +    -- NOTE: comparisons with status-quo are intentionally omitted to mark
   3.149 +    --       issues that were counted prior LiquidFeedback Core v2.0.0
   3.150 +    GROUP BY
   3.151 +      "issue"."id",
   3.152 +      "winning_initiative"."id",
   3.153 +      "losing_initiative"."id"
   3.154 +  ) AS "battle_view"
   3.155 +  LEFT JOIN "battle"
   3.156 +  ON "battle_view"."winning_initiative_id" = "battle"."winning_initiative_id"
   3.157 +  AND "battle_view"."losing_initiative_id" = "battle"."losing_initiative_id"
   3.158 +  WHERE "battle" ISNULL;
   3.159 +
   3.160 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
   3.161 +  RETURNS VOID
   3.162 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   3.163 +    DECLARE
   3.164 +      "area_id_v"   "area"."id"%TYPE;
   3.165 +      "unit_id_v"   "unit"."id"%TYPE;
   3.166 +      "member_id_v" "member"."id"%TYPE;
   3.167 +    BEGIN
   3.168 +      PERFORM "require_transaction_isolation"();
   3.169 +      SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
   3.170 +      SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
   3.171 +      -- override protection triggers:
   3.172 +      INSERT INTO "temporary_transaction_data" ("key", "value")
   3.173 +        VALUES ('override_protection_triggers', TRUE::TEXT);
   3.174 +      -- delete timestamp of voting comment:
   3.175 +      UPDATE "direct_voter" SET "comment_changed" = NULL
   3.176 +        WHERE "issue_id" = "issue_id_p";
   3.177 +      -- delete delegating votes (in cases of manual reset of issue state):
   3.178 +      DELETE FROM "delegating_voter"
   3.179 +        WHERE "issue_id" = "issue_id_p";
   3.180 +      -- delete votes from non-privileged voters:
   3.181 +      DELETE FROM "direct_voter"
   3.182 +        USING (
   3.183 +          SELECT
   3.184 +            "direct_voter"."member_id"
   3.185 +          FROM "direct_voter"
   3.186 +          JOIN "member" ON "direct_voter"."member_id" = "member"."id"
   3.187 +          LEFT JOIN "privilege"
   3.188 +          ON "privilege"."unit_id" = "unit_id_v"
   3.189 +          AND "privilege"."member_id" = "direct_voter"."member_id"
   3.190 +          WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
   3.191 +            "member"."active" = FALSE OR
   3.192 +            "privilege"."voting_right" ISNULL OR
   3.193 +            "privilege"."voting_right" = FALSE
   3.194 +          )
   3.195 +        ) AS "subquery"
   3.196 +        WHERE "direct_voter"."issue_id" = "issue_id_p"
   3.197 +        AND "direct_voter"."member_id" = "subquery"."member_id";
   3.198 +      -- consider delegations:
   3.199 +      UPDATE "direct_voter" SET "weight" = 1
   3.200 +        WHERE "issue_id" = "issue_id_p";
   3.201 +      PERFORM "add_vote_delegations"("issue_id_p");
   3.202 +      -- mark first preferences:
   3.203 +      UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
   3.204 +        FROM (
   3.205 +          SELECT
   3.206 +            "vote"."initiative_id",
   3.207 +            "vote"."member_id",
   3.208 +            CASE WHEN "vote"."grade" > 0 THEN
   3.209 +              CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
   3.210 +            ELSE NULL
   3.211 +            END AS "first_preference"
   3.212 +          FROM "vote"
   3.213 +          JOIN "initiative"  -- NOTE: due to missing index on issue_id
   3.214 +          ON "vote"."issue_id" = "initiative"."issue_id"
   3.215 +          JOIN "vote" AS "agg"
   3.216 +          ON "initiative"."id" = "agg"."initiative_id"
   3.217 +          AND "vote"."member_id" = "agg"."member_id"
   3.218 +          GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
   3.219 +        ) AS "subquery"
   3.220 +        WHERE "vote"."issue_id" = "issue_id_p"
   3.221 +        AND "vote"."initiative_id" = "subquery"."initiative_id"
   3.222 +        AND "vote"."member_id" = "subquery"."member_id";
   3.223 +      -- finish overriding protection triggers (avoids garbage):
   3.224 +      DELETE FROM "temporary_transaction_data"
   3.225 +        WHERE "key" = 'override_protection_triggers';
   3.226 +      -- materialize battle_view:
   3.227 +      -- NOTE: "closed" column of issue must be set at this point
   3.228 +      DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
   3.229 +      INSERT INTO "battle" (
   3.230 +        "issue_id",
   3.231 +        "winning_initiative_id", "losing_initiative_id",
   3.232 +        "count"
   3.233 +      ) SELECT
   3.234 +        "issue_id",
   3.235 +        "winning_initiative_id", "losing_initiative_id",
   3.236 +        "count"
   3.237 +        FROM "battle_view" WHERE "issue_id" = "issue_id_p";
   3.238 +      -- set voter count:
   3.239 +      UPDATE "issue" SET
   3.240 +        "voter_count" = (
   3.241 +          SELECT coalesce(sum("weight"), 0)
   3.242 +          FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
   3.243 +        )
   3.244 +        WHERE "id" = "issue_id_p";
   3.245 +      -- calculate "first_preference_votes":
   3.246 +      UPDATE "initiative"
   3.247 +        SET "first_preference_votes" = "subquery"."sum"
   3.248 +        FROM (
   3.249 +          SELECT "vote"."initiative_id", sum("direct_voter"."weight")
   3.250 +          FROM "vote" JOIN "direct_voter"
   3.251 +          ON "vote"."issue_id" = "direct_voter"."issue_id"
   3.252 +          AND "vote"."member_id" = "direct_voter"."member_id"
   3.253 +          WHERE "vote"."first_preference"
   3.254 +          GROUP BY "vote"."initiative_id"
   3.255 +        ) AS "subquery"
   3.256 +        WHERE "initiative"."issue_id" = "issue_id_p"
   3.257 +        AND "initiative"."admitted"
   3.258 +        AND "initiative"."id" = "subquery"."initiative_id";
   3.259 +      UPDATE "initiative" SET "first_preference_votes" = 0
   3.260 +        WHERE "issue_id" = "issue_id_p"
   3.261 +        AND "initiative"."admitted"
   3.262 +        AND "first_preference_votes" ISNULL;
   3.263 +      -- copy "positive_votes" and "negative_votes" from "battle" table:
   3.264 +      UPDATE "initiative" SET
   3.265 +        "positive_votes" = "battle_win"."count",
   3.266 +        "negative_votes" = "battle_lose"."count"
   3.267 +        FROM "battle" AS "battle_win", "battle" AS "battle_lose"
   3.268 +        WHERE
   3.269 +          "battle_win"."issue_id" = "issue_id_p" AND
   3.270 +          "battle_win"."winning_initiative_id" = "initiative"."id" AND
   3.271 +          "battle_win"."losing_initiative_id" ISNULL AND
   3.272 +          "battle_lose"."issue_id" = "issue_id_p" AND
   3.273 +          "battle_lose"."losing_initiative_id" = "initiative"."id" AND
   3.274 +          "battle_lose"."winning_initiative_id" ISNULL;
   3.275 +    END;
   3.276 +  $$;
   3.277 +
   3.278 +COMMIT;

Impressum / About Us