1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/update/core-update.v1.4.0_rc2-v1.4.0_rc3.sql	Sun Jun 05 01:08:14 2011 +0200
     1.3 @@ -0,0 +1,169 @@
     1.4 +BEGIN;
     1.5 +
     1.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     1.7 +  SELECT * FROM (VALUES ('1.4.0_rc3', 1, 4, -1))
     1.8 +  AS "subquery"("string", "major", "minor", "revision");
     1.9 +
    1.10 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
    1.11 +  RETURNS VOID
    1.12 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    1.13 +    DECLARE
    1.14 +      "area_id_v"   "area"."id"%TYPE;
    1.15 +      "unit_id_v"   "unit"."id"%TYPE;
    1.16 +      "member_id_v" "member"."id"%TYPE;
    1.17 +    BEGIN
    1.18 +      PERFORM "lock_issue"("issue_id_p");
    1.19 +      SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
    1.20 +      SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
    1.21 +      DELETE FROM "delegating_voter"
    1.22 +        WHERE "issue_id" = "issue_id_p";
    1.23 +      DELETE FROM "direct_voter"
    1.24 +        WHERE "issue_id" = "issue_id_p"
    1.25 +        AND "autoreject" = TRUE;
    1.26 +      DELETE FROM "direct_voter"
    1.27 +        USING (
    1.28 +          SELECT
    1.29 +            "direct_voter"."member_id"
    1.30 +          FROM "direct_voter"
    1.31 +          JOIN "member" ON "direct_voter"."member_id" = "member"."id"
    1.32 +          LEFT JOIN "privilege"
    1.33 +          ON "privilege"."unit_id" = "unit_id_v"
    1.34 +          AND "privilege"."member_id" = "direct_voter"."member_id"
    1.35 +          WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
    1.36 +            "member"."active" = FALSE OR
    1.37 +            "privilege"."voting_right" ISNULL OR
    1.38 +            "privilege"."voting_right" = FALSE
    1.39 +          )
    1.40 +        ) AS "subquery"
    1.41 +        WHERE "direct_voter"."issue_id" = "issue_id_p"
    1.42 +        AND "direct_voter"."member_id" = "subquery"."member_id";
    1.43 +      UPDATE "direct_voter" SET "weight" = 1
    1.44 +        WHERE "issue_id" = "issue_id_p";
    1.45 +      PERFORM "add_vote_delegations"("issue_id_p");
    1.46 +      FOR "member_id_v" IN
    1.47 +        SELECT "interest"."member_id"
    1.48 +          FROM "interest"
    1.49 +          JOIN "member"
    1.50 +            ON "interest"."member_id" = "member"."id"
    1.51 +          JOIN "privilege"
    1.52 +            ON "privilege"."unit_id" = "unit_id_v"
    1.53 +            AND "privilege"."member_id" = "member"."id"
    1.54 +          LEFT JOIN "direct_voter"
    1.55 +            ON "interest"."member_id" = "direct_voter"."member_id"
    1.56 +            AND "interest"."issue_id" = "direct_voter"."issue_id"
    1.57 +          LEFT JOIN "delegating_voter"
    1.58 +            ON "interest"."member_id" = "delegating_voter"."member_id"
    1.59 +            AND "interest"."issue_id" = "delegating_voter"."issue_id"
    1.60 +          WHERE "interest"."issue_id" = "issue_id_p"
    1.61 +          AND "interest"."autoreject" = TRUE
    1.62 +          AND "member"."active"
    1.63 +          AND "privilege"."voting_right"
    1.64 +          AND "direct_voter"."member_id" ISNULL
    1.65 +          AND "delegating_voter"."member_id" ISNULL
    1.66 +        UNION SELECT "membership"."member_id"
    1.67 +          FROM "membership"
    1.68 +          JOIN "member"
    1.69 +            ON "membership"."member_id" = "member"."id"
    1.70 +          JOIN "privilege"
    1.71 +            ON "privilege"."unit_id" = "unit_id_v"
    1.72 +            AND "privilege"."member_id" = "member"."id"
    1.73 +          LEFT JOIN "interest"
    1.74 +            ON "membership"."member_id" = "interest"."member_id"
    1.75 +            AND "interest"."issue_id" = "issue_id_p"
    1.76 +          LEFT JOIN "direct_voter"
    1.77 +            ON "membership"."member_id" = "direct_voter"."member_id"
    1.78 +            AND "direct_voter"."issue_id" = "issue_id_p"
    1.79 +          LEFT JOIN "delegating_voter"
    1.80 +            ON "membership"."member_id" = "delegating_voter"."member_id"
    1.81 +            AND "delegating_voter"."issue_id" = "issue_id_p"
    1.82 +          WHERE "membership"."area_id" = "area_id_v"
    1.83 +          AND "membership"."autoreject" = TRUE
    1.84 +          AND "member"."active"
    1.85 +          AND "privilege"."voting_right"
    1.86 +          AND "interest"."autoreject" ISNULL
    1.87 +          AND "direct_voter"."member_id" ISNULL
    1.88 +          AND "delegating_voter"."member_id" ISNULL
    1.89 +      LOOP
    1.90 +        INSERT INTO "direct_voter"
    1.91 +          ("member_id", "issue_id", "weight", "autoreject") VALUES
    1.92 +          ("member_id_v", "issue_id_p", 1, TRUE);
    1.93 +        INSERT INTO "vote" (
    1.94 +          "member_id",
    1.95 +          "issue_id",
    1.96 +          "initiative_id",
    1.97 +          "grade"
    1.98 +          ) SELECT
    1.99 +            "member_id_v" AS "member_id",
   1.100 +            "issue_id_p"  AS "issue_id",
   1.101 +            "id"          AS "initiative_id",
   1.102 +            -1            AS "grade"
   1.103 +          FROM "initiative" WHERE "issue_id" = "issue_id_p";
   1.104 +      END LOOP;
   1.105 +      PERFORM "add_vote_delegations"("issue_id_p");
   1.106 +      UPDATE "issue" SET
   1.107 +        "state"  = 'calculation',
   1.108 +        "closed" = now(),
   1.109 +        "voter_count" = (
   1.110 +          SELECT coalesce(sum("weight"), 0)
   1.111 +          FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
   1.112 +        )
   1.113 +        WHERE "id" = "issue_id_p";
   1.114 +      UPDATE "initiative" SET
   1.115 +        "positive_votes" = "vote_counts"."positive_votes",
   1.116 +        "negative_votes" = "vote_counts"."negative_votes",
   1.117 +        "agreed" = CASE WHEN "majority_strict" THEN
   1.118 +          "vote_counts"."positive_votes" * "majority_den" >
   1.119 +          "majority_num" *
   1.120 +          ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
   1.121 +        ELSE
   1.122 +          "vote_counts"."positive_votes" * "majority_den" >=
   1.123 +          "majority_num" *
   1.124 +          ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
   1.125 +        END
   1.126 +        FROM
   1.127 +          ( SELECT
   1.128 +              "initiative"."id" AS "initiative_id",
   1.129 +              coalesce(
   1.130 +                sum(
   1.131 +                  CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
   1.132 +                ),
   1.133 +                0
   1.134 +              ) AS "positive_votes",
   1.135 +              coalesce(
   1.136 +                sum(
   1.137 +                  CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
   1.138 +                ),
   1.139 +                0
   1.140 +              ) AS "negative_votes"
   1.141 +            FROM "initiative"
   1.142 +            JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
   1.143 +            JOIN "policy" ON "issue"."policy_id" = "policy"."id"
   1.144 +            LEFT JOIN "direct_voter"
   1.145 +              ON "direct_voter"."issue_id" = "initiative"."issue_id"
   1.146 +            LEFT JOIN "vote"
   1.147 +              ON "vote"."initiative_id" = "initiative"."id"
   1.148 +              AND "vote"."member_id" = "direct_voter"."member_id"
   1.149 +            WHERE "initiative"."issue_id" = "issue_id_p"
   1.150 +            AND "initiative"."admitted"  -- NOTE: NULL case is handled too
   1.151 +            GROUP BY "initiative"."id"
   1.152 +          ) AS "vote_counts",
   1.153 +          "issue",
   1.154 +          "policy"
   1.155 +        WHERE "vote_counts"."initiative_id" = "initiative"."id"
   1.156 +        AND "issue"."id" = "initiative"."issue_id"
   1.157 +        AND "policy"."id" = "issue"."policy_id";
   1.158 +      -- NOTE: "closed" column of issue must be set at this point
   1.159 +      DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
   1.160 +      INSERT INTO "battle" (
   1.161 +        "issue_id",
   1.162 +        "winning_initiative_id", "losing_initiative_id",
   1.163 +        "count"
   1.164 +      ) SELECT
   1.165 +        "issue_id",
   1.166 +        "winning_initiative_id", "losing_initiative_id",
   1.167 +        "count"
   1.168 +        FROM "battle_view" WHERE "issue_id" = "issue_id_p";
   1.169 +    END;
   1.170 +  $$;
   1.171 +
   1.172 +COMMIT;

     2.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     2.2 +++ b/update/core-update.v1.4.0_rc3-v1.4.0_rc4.sql	Sun Jun 05 01:08:14 2011 +0200
     2.3 @@ -0,0 +1,218 @@
     2.4 +BEGIN;
     2.5 +
     2.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     2.7 +  SELECT * FROM (VALUES ('1.4.0_rc4', 1, 4, -1))
     2.8 +  AS "subquery"("string", "major", "minor", "revision");
     2.9 +
    2.10 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
    2.11 +  RETURNS VOID
    2.12 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    2.13 +    DECLARE
    2.14 +      "area_id_v"   "area"."id"%TYPE;
    2.15 +      "unit_id_v"   "unit"."id"%TYPE;
    2.16 +      "member_id_v" "member"."id"%TYPE;
    2.17 +    BEGIN
    2.18 +      PERFORM "lock_issue"("issue_id_p");
    2.19 +      SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
    2.20 +      SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
    2.21 +      DELETE FROM "delegating_voter"
    2.22 +        WHERE "issue_id" = "issue_id_p";
    2.23 +      DELETE FROM "direct_voter"
    2.24 +        WHERE "issue_id" = "issue_id_p"
    2.25 +        AND "autoreject" = TRUE;
    2.26 +      DELETE FROM "direct_voter"
    2.27 +        USING (
    2.28 +          SELECT
    2.29 +            "direct_voter"."member_id"
    2.30 +          FROM "direct_voter"
    2.31 +          JOIN "member" ON "direct_voter"."member_id" = "member"."id"
    2.32 +          LEFT JOIN "privilege"
    2.33 +          ON "privilege"."unit_id" = "unit_id_v"
    2.34 +          AND "privilege"."member_id" = "direct_voter"."member_id"
    2.35 +          WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
    2.36 +            "member"."active" = FALSE OR
    2.37 +            "privilege"."voting_right" ISNULL OR
    2.38 +            "privilege"."voting_right" = FALSE
    2.39 +          )
    2.40 +        ) AS "subquery"
    2.41 +        WHERE "direct_voter"."issue_id" = "issue_id_p"
    2.42 +        AND "direct_voter"."member_id" = "subquery"."member_id";
    2.43 +      UPDATE "direct_voter" SET "weight" = 1
    2.44 +        WHERE "issue_id" = "issue_id_p";
    2.45 +      PERFORM "add_vote_delegations"("issue_id_p");
    2.46 +      FOR "member_id_v" IN
    2.47 +        SELECT "interest"."member_id"
    2.48 +          FROM "interest"
    2.49 +          JOIN "member"
    2.50 +            ON "interest"."member_id" = "member"."id"
    2.51 +          JOIN "privilege"
    2.52 +            ON "privilege"."unit_id" = "unit_id_v"
    2.53 +            AND "privilege"."member_id" = "member"."id"
    2.54 +          LEFT JOIN "direct_voter"
    2.55 +            ON "interest"."member_id" = "direct_voter"."member_id"
    2.56 +            AND "interest"."issue_id" = "direct_voter"."issue_id"
    2.57 +          LEFT JOIN "delegating_voter"
    2.58 +            ON "interest"."member_id" = "delegating_voter"."member_id"
    2.59 +            AND "interest"."issue_id" = "delegating_voter"."issue_id"
    2.60 +          WHERE "interest"."issue_id" = "issue_id_p"
    2.61 +          AND "interest"."autoreject" = TRUE
    2.62 +          AND "member"."active"
    2.63 +          AND "privilege"."voting_right"
    2.64 +          AND "direct_voter"."member_id" ISNULL
    2.65 +          AND "delegating_voter"."member_id" ISNULL
    2.66 +        UNION SELECT "membership"."member_id"
    2.67 +          FROM "membership"
    2.68 +          JOIN "member"
    2.69 +            ON "membership"."member_id" = "member"."id"
    2.70 +          JOIN "privilege"
    2.71 +            ON "privilege"."unit_id" = "unit_id_v"
    2.72 +            AND "privilege"."member_id" = "member"."id"
    2.73 +          LEFT JOIN "interest"
    2.74 +            ON "membership"."member_id" = "interest"."member_id"
    2.75 +            AND "interest"."issue_id" = "issue_id_p"
    2.76 +          LEFT JOIN "direct_voter"
    2.77 +            ON "membership"."member_id" = "direct_voter"."member_id"
    2.78 +            AND "direct_voter"."issue_id" = "issue_id_p"
    2.79 +          LEFT JOIN "delegating_voter"
    2.80 +            ON "membership"."member_id" = "delegating_voter"."member_id"
    2.81 +            AND "delegating_voter"."issue_id" = "issue_id_p"
    2.82 +          WHERE "membership"."area_id" = "area_id_v"
    2.83 +          AND "membership"."autoreject" = TRUE
    2.84 +          AND "member"."active"
    2.85 +          AND "privilege"."voting_right"
    2.86 +          AND "interest"."autoreject" ISNULL
    2.87 +          AND "direct_voter"."member_id" ISNULL
    2.88 +          AND "delegating_voter"."member_id" ISNULL
    2.89 +      LOOP
    2.90 +        INSERT INTO "direct_voter"
    2.91 +          ("member_id", "issue_id", "weight", "autoreject") VALUES
    2.92 +          ("member_id_v", "issue_id_p", 1, TRUE);
    2.93 +        INSERT INTO "vote" (
    2.94 +          "member_id",
    2.95 +          "issue_id",
    2.96 +          "initiative_id",
    2.97 +          "grade"
    2.98 +          ) SELECT
    2.99 +            "member_id_v" AS "member_id",
   2.100 +            "issue_id_p"  AS "issue_id",
   2.101 +            "id"          AS "initiative_id",
   2.102 +            -1            AS "grade"
   2.103 +          FROM "initiative"
   2.104 +          WHERE "issue_id" = "issue_id_p" AND "admitted";
   2.105 +      END LOOP;
   2.106 +      PERFORM "add_vote_delegations"("issue_id_p");
   2.107 +      UPDATE "issue" SET
   2.108 +        "state"  = 'calculation',
   2.109 +        "closed" = now(),
   2.110 +        "voter_count" = (
   2.111 +          SELECT coalesce(sum("weight"), 0)
   2.112 +          FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
   2.113 +        )
   2.114 +        WHERE "id" = "issue_id_p";
   2.115 +      UPDATE "initiative" SET
   2.116 +        "positive_votes" = "vote_counts"."positive_votes",
   2.117 +        "negative_votes" = "vote_counts"."negative_votes",
   2.118 +        "agreed" = CASE WHEN "majority_strict" THEN
   2.119 +          "vote_counts"."positive_votes" * "majority_den" >
   2.120 +          "majority_num" *
   2.121 +          ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
   2.122 +        ELSE
   2.123 +          "vote_counts"."positive_votes" * "majority_den" >=
   2.124 +          "majority_num" *
   2.125 +          ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
   2.126 +        END
   2.127 +        FROM
   2.128 +          ( SELECT
   2.129 +              "initiative"."id" AS "initiative_id",
   2.130 +              coalesce(
   2.131 +                sum(
   2.132 +                  CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
   2.133 +                ),
   2.134 +                0
   2.135 +              ) AS "positive_votes",
   2.136 +              coalesce(
   2.137 +                sum(
   2.138 +                  CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
   2.139 +                ),
   2.140 +                0
   2.141 +              ) AS "negative_votes"
   2.142 +            FROM "initiative"
   2.143 +            JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
   2.144 +            JOIN "policy" ON "issue"."policy_id" = "policy"."id"
   2.145 +            LEFT JOIN "direct_voter"
   2.146 +              ON "direct_voter"."issue_id" = "initiative"."issue_id"
   2.147 +            LEFT JOIN "vote"
   2.148 +              ON "vote"."initiative_id" = "initiative"."id"
   2.149 +              AND "vote"."member_id" = "direct_voter"."member_id"
   2.150 +            WHERE "initiative"."issue_id" = "issue_id_p"
   2.151 +            AND "initiative"."admitted"  -- NOTE: NULL case is handled too
   2.152 +            GROUP BY "initiative"."id"
   2.153 +          ) AS "vote_counts",
   2.154 +          "issue",
   2.155 +          "policy"
   2.156 +        WHERE "vote_counts"."initiative_id" = "initiative"."id"
   2.157 +        AND "issue"."id" = "initiative"."issue_id"
   2.158 +        AND "policy"."id" = "issue"."policy_id";
   2.159 +      -- NOTE: "closed" column of issue must be set at this point
   2.160 +      DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
   2.161 +      INSERT INTO "battle" (
   2.162 +        "issue_id",
   2.163 +        "winning_initiative_id", "losing_initiative_id",
   2.164 +        "count"
   2.165 +      ) SELECT
   2.166 +        "issue_id",
   2.167 +        "winning_initiative_id", "losing_initiative_id",
   2.168 +        "count"
   2.169 +        FROM "battle_view" WHERE "issue_id" = "issue_id_p";
   2.170 +    END;
   2.171 +  $$;
   2.172 +
   2.173 +CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
   2.174 +  RETURNS VOID
   2.175 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   2.176 +    DECLARE
   2.177 +      "issue_row" "issue"%ROWTYPE;
   2.178 +    BEGIN
   2.179 +      SELECT * INTO "issue_row"
   2.180 +        FROM "issue" WHERE "id" = "issue_id_p"
   2.181 +        FOR UPDATE;
   2.182 +      IF "issue_row"."cleaned" ISNULL THEN
   2.183 +        UPDATE "issue" SET
   2.184 +          "state"           = 'voting',
   2.185 +          "closed"          = NULL,
   2.186 +          "ranks_available" = FALSE
   2.187 +          WHERE "id" = "issue_id_p";
   2.188 +        DELETE FROM "issue_comment"
   2.189 +          WHERE "issue_id" = "issue_id_p";
   2.190 +        DELETE FROM "voting_comment"
   2.191 +          WHERE "issue_id" = "issue_id_p";
   2.192 +        DELETE FROM "delegating_voter"
   2.193 +          WHERE "issue_id" = "issue_id_p";
   2.194 +        DELETE FROM "direct_voter"
   2.195 +          WHERE "issue_id" = "issue_id_p";
   2.196 +        DELETE FROM "delegating_interest_snapshot"
   2.197 +          WHERE "issue_id" = "issue_id_p";
   2.198 +        DELETE FROM "direct_interest_snapshot"
   2.199 +          WHERE "issue_id" = "issue_id_p";
   2.200 +        DELETE FROM "delegating_population_snapshot"
   2.201 +          WHERE "issue_id" = "issue_id_p";
   2.202 +        DELETE FROM "direct_population_snapshot"
   2.203 +          WHERE "issue_id" = "issue_id_p";
   2.204 +        DELETE FROM "non_voter"
   2.205 +          WHERE "issue_id" = "issue_id_p";
   2.206 +        DELETE FROM "delegation"
   2.207 +          WHERE "issue_id" = "issue_id_p";
   2.208 +        DELETE FROM "supporter"
   2.209 +          WHERE "issue_id" = "issue_id_p";
   2.210 +        UPDATE "issue" SET
   2.211 +          "state"           = "issue_row"."state",
   2.212 +          "closed"          = "issue_row"."closed",
   2.213 +          "ranks_available" = "issue_row"."ranks_available",
   2.214 +          "cleaned"         = now()
   2.215 +          WHERE "id" = "issue_id_p";
   2.216 +      END IF;
   2.217 +      RETURN;
   2.218 +    END;
   2.219 +  $$;
   2.220 +
   2.221 +COMMIT;