liquid_feedback_core

changeset 166:7f2011c7b95e

merge
author jbe
date Sun Jun 05 01:12:37 2011 +0200 (2011-06-05)
parents 7c8b812740ac 662d714de82e
children de59f6b1daf3
files .hgtags core.sql
line diff
     1.1 --- a/.hgtags	Sat Jun 04 22:52:50 2011 +0200
     1.2 +++ b/.hgtags	Sun Jun 05 01:12:37 2011 +0200
     1.3 @@ -44,3 +44,4 @@
     1.4  0144b703b2610a4c93158427d5eff79cca3fca84 v1.4.0_rc1
     1.5  07cfc7c1ce3f9c7191a028d3fabf27881edcc8c7 v1.4.0_rc2
     1.6  0fa3c4739ca75a0dd55c073903e274ec20fce137 v1.4.0_rc3
     1.7 +1be788313b84e0b27f396f34be3838ccb66287a4 v1.4.0_rc4
     3.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     3.2 +++ b/update/core-update.v1.4.0_rc2-v1.4.0_rc3.sql	Sun Jun 05 01:12:37 2011 +0200
     3.3 @@ -0,0 +1,169 @@
     3.4 +BEGIN;
     3.5 +
     3.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     3.7 +  SELECT * FROM (VALUES ('1.4.0_rc3', 1, 4, -1))
     3.8 +  AS "subquery"("string", "major", "minor", "revision");
     3.9 +
    3.10 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
    3.11 +  RETURNS VOID
    3.12 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    3.13 +    DECLARE
    3.14 +      "area_id_v"   "area"."id"%TYPE;
    3.15 +      "unit_id_v"   "unit"."id"%TYPE;
    3.16 +      "member_id_v" "member"."id"%TYPE;
    3.17 +    BEGIN
    3.18 +      PERFORM "lock_issue"("issue_id_p");
    3.19 +      SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
    3.20 +      SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
    3.21 +      DELETE FROM "delegating_voter"
    3.22 +        WHERE "issue_id" = "issue_id_p";
    3.23 +      DELETE FROM "direct_voter"
    3.24 +        WHERE "issue_id" = "issue_id_p"
    3.25 +        AND "autoreject" = TRUE;
    3.26 +      DELETE FROM "direct_voter"
    3.27 +        USING (
    3.28 +          SELECT
    3.29 +            "direct_voter"."member_id"
    3.30 +          FROM "direct_voter"
    3.31 +          JOIN "member" ON "direct_voter"."member_id" = "member"."id"
    3.32 +          LEFT JOIN "privilege"
    3.33 +          ON "privilege"."unit_id" = "unit_id_v"
    3.34 +          AND "privilege"."member_id" = "direct_voter"."member_id"
    3.35 +          WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
    3.36 +            "member"."active" = FALSE OR
    3.37 +            "privilege"."voting_right" ISNULL OR
    3.38 +            "privilege"."voting_right" = FALSE
    3.39 +          )
    3.40 +        ) AS "subquery"
    3.41 +        WHERE "direct_voter"."issue_id" = "issue_id_p"
    3.42 +        AND "direct_voter"."member_id" = "subquery"."member_id";
    3.43 +      UPDATE "direct_voter" SET "weight" = 1
    3.44 +        WHERE "issue_id" = "issue_id_p";
    3.45 +      PERFORM "add_vote_delegations"("issue_id_p");
    3.46 +      FOR "member_id_v" IN
    3.47 +        SELECT "interest"."member_id"
    3.48 +          FROM "interest"
    3.49 +          JOIN "member"
    3.50 +            ON "interest"."member_id" = "member"."id"
    3.51 +          JOIN "privilege"
    3.52 +            ON "privilege"."unit_id" = "unit_id_v"
    3.53 +            AND "privilege"."member_id" = "member"."id"
    3.54 +          LEFT JOIN "direct_voter"
    3.55 +            ON "interest"."member_id" = "direct_voter"."member_id"
    3.56 +            AND "interest"."issue_id" = "direct_voter"."issue_id"
    3.57 +          LEFT JOIN "delegating_voter"
    3.58 +            ON "interest"."member_id" = "delegating_voter"."member_id"
    3.59 +            AND "interest"."issue_id" = "delegating_voter"."issue_id"
    3.60 +          WHERE "interest"."issue_id" = "issue_id_p"
    3.61 +          AND "interest"."autoreject" = TRUE
    3.62 +          AND "member"."active"
    3.63 +          AND "privilege"."voting_right"
    3.64 +          AND "direct_voter"."member_id" ISNULL
    3.65 +          AND "delegating_voter"."member_id" ISNULL
    3.66 +        UNION SELECT "membership"."member_id"
    3.67 +          FROM "membership"
    3.68 +          JOIN "member"
    3.69 +            ON "membership"."member_id" = "member"."id"
    3.70 +          JOIN "privilege"
    3.71 +            ON "privilege"."unit_id" = "unit_id_v"
    3.72 +            AND "privilege"."member_id" = "member"."id"
    3.73 +          LEFT JOIN "interest"
    3.74 +            ON "membership"."member_id" = "interest"."member_id"
    3.75 +            AND "interest"."issue_id" = "issue_id_p"
    3.76 +          LEFT JOIN "direct_voter"
    3.77 +            ON "membership"."member_id" = "direct_voter"."member_id"
    3.78 +            AND "direct_voter"."issue_id" = "issue_id_p"
    3.79 +          LEFT JOIN "delegating_voter"
    3.80 +            ON "membership"."member_id" = "delegating_voter"."member_id"
    3.81 +            AND "delegating_voter"."issue_id" = "issue_id_p"
    3.82 +          WHERE "membership"."area_id" = "area_id_v"
    3.83 +          AND "membership"."autoreject" = TRUE
    3.84 +          AND "member"."active"
    3.85 +          AND "privilege"."voting_right"
    3.86 +          AND "interest"."autoreject" ISNULL
    3.87 +          AND "direct_voter"."member_id" ISNULL
    3.88 +          AND "delegating_voter"."member_id" ISNULL
    3.89 +      LOOP
    3.90 +        INSERT INTO "direct_voter"
    3.91 +          ("member_id", "issue_id", "weight", "autoreject") VALUES
    3.92 +          ("member_id_v", "issue_id_p", 1, TRUE);
    3.93 +        INSERT INTO "vote" (
    3.94 +          "member_id",
    3.95 +          "issue_id",
    3.96 +          "initiative_id",
    3.97 +          "grade"
    3.98 +          ) SELECT
    3.99 +            "member_id_v" AS "member_id",
   3.100 +            "issue_id_p"  AS "issue_id",
   3.101 +            "id"          AS "initiative_id",
   3.102 +            -1            AS "grade"
   3.103 +          FROM "initiative" WHERE "issue_id" = "issue_id_p";
   3.104 +      END LOOP;
   3.105 +      PERFORM "add_vote_delegations"("issue_id_p");
   3.106 +      UPDATE "issue" SET
   3.107 +        "state"  = 'calculation',
   3.108 +        "closed" = now(),
   3.109 +        "voter_count" = (
   3.110 +          SELECT coalesce(sum("weight"), 0)
   3.111 +          FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
   3.112 +        )
   3.113 +        WHERE "id" = "issue_id_p";
   3.114 +      UPDATE "initiative" SET
   3.115 +        "positive_votes" = "vote_counts"."positive_votes",
   3.116 +        "negative_votes" = "vote_counts"."negative_votes",
   3.117 +        "agreed" = CASE WHEN "majority_strict" THEN
   3.118 +          "vote_counts"."positive_votes" * "majority_den" >
   3.119 +          "majority_num" *
   3.120 +          ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
   3.121 +        ELSE
   3.122 +          "vote_counts"."positive_votes" * "majority_den" >=
   3.123 +          "majority_num" *
   3.124 +          ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
   3.125 +        END
   3.126 +        FROM
   3.127 +          ( SELECT
   3.128 +              "initiative"."id" AS "initiative_id",
   3.129 +              coalesce(
   3.130 +                sum(
   3.131 +                  CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
   3.132 +                ),
   3.133 +                0
   3.134 +              ) AS "positive_votes",
   3.135 +              coalesce(
   3.136 +                sum(
   3.137 +                  CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
   3.138 +                ),
   3.139 +                0
   3.140 +              ) AS "negative_votes"
   3.141 +            FROM "initiative"
   3.142 +            JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
   3.143 +            JOIN "policy" ON "issue"."policy_id" = "policy"."id"
   3.144 +            LEFT JOIN "direct_voter"
   3.145 +              ON "direct_voter"."issue_id" = "initiative"."issue_id"
   3.146 +            LEFT JOIN "vote"
   3.147 +              ON "vote"."initiative_id" = "initiative"."id"
   3.148 +              AND "vote"."member_id" = "direct_voter"."member_id"
   3.149 +            WHERE "initiative"."issue_id" = "issue_id_p"
   3.150 +            AND "initiative"."admitted"  -- NOTE: NULL case is handled too
   3.151 +            GROUP BY "initiative"."id"
   3.152 +          ) AS "vote_counts",
   3.153 +          "issue",
   3.154 +          "policy"
   3.155 +        WHERE "vote_counts"."initiative_id" = "initiative"."id"
   3.156 +        AND "issue"."id" = "initiative"."issue_id"
   3.157 +        AND "policy"."id" = "issue"."policy_id";
   3.158 +      -- NOTE: "closed" column of issue must be set at this point
   3.159 +      DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
   3.160 +      INSERT INTO "battle" (
   3.161 +        "issue_id",
   3.162 +        "winning_initiative_id", "losing_initiative_id",
   3.163 +        "count"
   3.164 +      ) SELECT
   3.165 +        "issue_id",
   3.166 +        "winning_initiative_id", "losing_initiative_id",
   3.167 +        "count"
   3.168 +        FROM "battle_view" WHERE "issue_id" = "issue_id_p";
   3.169 +    END;
   3.170 +  $$;
   3.171 +
   3.172 +COMMIT;
     4.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     4.2 +++ b/update/core-update.v1.4.0_rc3-v1.4.0_rc4.sql	Sun Jun 05 01:12:37 2011 +0200
     4.3 @@ -0,0 +1,218 @@
     4.4 +BEGIN;
     4.5 +
     4.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     4.7 +  SELECT * FROM (VALUES ('1.4.0_rc4', 1, 4, -1))
     4.8 +  AS "subquery"("string", "major", "minor", "revision");
     4.9 +
    4.10 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
    4.11 +  RETURNS VOID
    4.12 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    4.13 +    DECLARE
    4.14 +      "area_id_v"   "area"."id"%TYPE;
    4.15 +      "unit_id_v"   "unit"."id"%TYPE;
    4.16 +      "member_id_v" "member"."id"%TYPE;
    4.17 +    BEGIN
    4.18 +      PERFORM "lock_issue"("issue_id_p");
    4.19 +      SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
    4.20 +      SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
    4.21 +      DELETE FROM "delegating_voter"
    4.22 +        WHERE "issue_id" = "issue_id_p";
    4.23 +      DELETE FROM "direct_voter"
    4.24 +        WHERE "issue_id" = "issue_id_p"
    4.25 +        AND "autoreject" = TRUE;
    4.26 +      DELETE FROM "direct_voter"
    4.27 +        USING (
    4.28 +          SELECT
    4.29 +            "direct_voter"."member_id"
    4.30 +          FROM "direct_voter"
    4.31 +          JOIN "member" ON "direct_voter"."member_id" = "member"."id"
    4.32 +          LEFT JOIN "privilege"
    4.33 +          ON "privilege"."unit_id" = "unit_id_v"
    4.34 +          AND "privilege"."member_id" = "direct_voter"."member_id"
    4.35 +          WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
    4.36 +            "member"."active" = FALSE OR
    4.37 +            "privilege"."voting_right" ISNULL OR
    4.38 +            "privilege"."voting_right" = FALSE
    4.39 +          )
    4.40 +        ) AS "subquery"
    4.41 +        WHERE "direct_voter"."issue_id" = "issue_id_p"
    4.42 +        AND "direct_voter"."member_id" = "subquery"."member_id";
    4.43 +      UPDATE "direct_voter" SET "weight" = 1
    4.44 +        WHERE "issue_id" = "issue_id_p";
    4.45 +      PERFORM "add_vote_delegations"("issue_id_p");
    4.46 +      FOR "member_id_v" IN
    4.47 +        SELECT "interest"."member_id"
    4.48 +          FROM "interest"
    4.49 +          JOIN "member"
    4.50 +            ON "interest"."member_id" = "member"."id"
    4.51 +          JOIN "privilege"
    4.52 +            ON "privilege"."unit_id" = "unit_id_v"
    4.53 +            AND "privilege"."member_id" = "member"."id"
    4.54 +          LEFT JOIN "direct_voter"
    4.55 +            ON "interest"."member_id" = "direct_voter"."member_id"
    4.56 +            AND "interest"."issue_id" = "direct_voter"."issue_id"
    4.57 +          LEFT JOIN "delegating_voter"
    4.58 +            ON "interest"."member_id" = "delegating_voter"."member_id"
    4.59 +            AND "interest"."issue_id" = "delegating_voter"."issue_id"
    4.60 +          WHERE "interest"."issue_id" = "issue_id_p"
    4.61 +          AND "interest"."autoreject" = TRUE
    4.62 +          AND "member"."active"
    4.63 +          AND "privilege"."voting_right"
    4.64 +          AND "direct_voter"."member_id" ISNULL
    4.65 +          AND "delegating_voter"."member_id" ISNULL
    4.66 +        UNION SELECT "membership"."member_id"
    4.67 +          FROM "membership"
    4.68 +          JOIN "member"
    4.69 +            ON "membership"."member_id" = "member"."id"
    4.70 +          JOIN "privilege"
    4.71 +            ON "privilege"."unit_id" = "unit_id_v"
    4.72 +            AND "privilege"."member_id" = "member"."id"
    4.73 +          LEFT JOIN "interest"
    4.74 +            ON "membership"."member_id" = "interest"."member_id"
    4.75 +            AND "interest"."issue_id" = "issue_id_p"
    4.76 +          LEFT JOIN "direct_voter"
    4.77 +            ON "membership"."member_id" = "direct_voter"."member_id"
    4.78 +            AND "direct_voter"."issue_id" = "issue_id_p"
    4.79 +          LEFT JOIN "delegating_voter"
    4.80 +            ON "membership"."member_id" = "delegating_voter"."member_id"
    4.81 +            AND "delegating_voter"."issue_id" = "issue_id_p"
    4.82 +          WHERE "membership"."area_id" = "area_id_v"
    4.83 +          AND "membership"."autoreject" = TRUE
    4.84 +          AND "member"."active"
    4.85 +          AND "privilege"."voting_right"
    4.86 +          AND "interest"."autoreject" ISNULL
    4.87 +          AND "direct_voter"."member_id" ISNULL
    4.88 +          AND "delegating_voter"."member_id" ISNULL
    4.89 +      LOOP
    4.90 +        INSERT INTO "direct_voter"
    4.91 +          ("member_id", "issue_id", "weight", "autoreject") VALUES
    4.92 +          ("member_id_v", "issue_id_p", 1, TRUE);
    4.93 +        INSERT INTO "vote" (
    4.94 +          "member_id",
    4.95 +          "issue_id",
    4.96 +          "initiative_id",
    4.97 +          "grade"
    4.98 +          ) SELECT
    4.99 +            "member_id_v" AS "member_id",
   4.100 +            "issue_id_p"  AS "issue_id",
   4.101 +            "id"          AS "initiative_id",
   4.102 +            -1            AS "grade"
   4.103 +          FROM "initiative"
   4.104 +          WHERE "issue_id" = "issue_id_p" AND "admitted";
   4.105 +      END LOOP;
   4.106 +      PERFORM "add_vote_delegations"("issue_id_p");
   4.107 +      UPDATE "issue" SET
   4.108 +        "state"  = 'calculation',
   4.109 +        "closed" = now(),
   4.110 +        "voter_count" = (
   4.111 +          SELECT coalesce(sum("weight"), 0)
   4.112 +          FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
   4.113 +        )
   4.114 +        WHERE "id" = "issue_id_p";
   4.115 +      UPDATE "initiative" SET
   4.116 +        "positive_votes" = "vote_counts"."positive_votes",
   4.117 +        "negative_votes" = "vote_counts"."negative_votes",
   4.118 +        "agreed" = CASE WHEN "majority_strict" THEN
   4.119 +          "vote_counts"."positive_votes" * "majority_den" >
   4.120 +          "majority_num" *
   4.121 +          ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
   4.122 +        ELSE
   4.123 +          "vote_counts"."positive_votes" * "majority_den" >=
   4.124 +          "majority_num" *
   4.125 +          ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
   4.126 +        END
   4.127 +        FROM
   4.128 +          ( SELECT
   4.129 +              "initiative"."id" AS "initiative_id",
   4.130 +              coalesce(
   4.131 +                sum(
   4.132 +                  CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
   4.133 +                ),
   4.134 +                0
   4.135 +              ) AS "positive_votes",
   4.136 +              coalesce(
   4.137 +                sum(
   4.138 +                  CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
   4.139 +                ),
   4.140 +                0
   4.141 +              ) AS "negative_votes"
   4.142 +            FROM "initiative"
   4.143 +            JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
   4.144 +            JOIN "policy" ON "issue"."policy_id" = "policy"."id"
   4.145 +            LEFT JOIN "direct_voter"
   4.146 +              ON "direct_voter"."issue_id" = "initiative"."issue_id"
   4.147 +            LEFT JOIN "vote"
   4.148 +              ON "vote"."initiative_id" = "initiative"."id"
   4.149 +              AND "vote"."member_id" = "direct_voter"."member_id"
   4.150 +            WHERE "initiative"."issue_id" = "issue_id_p"
   4.151 +            AND "initiative"."admitted"  -- NOTE: NULL case is handled too
   4.152 +            GROUP BY "initiative"."id"
   4.153 +          ) AS "vote_counts",
   4.154 +          "issue",
   4.155 +          "policy"
   4.156 +        WHERE "vote_counts"."initiative_id" = "initiative"."id"
   4.157 +        AND "issue"."id" = "initiative"."issue_id"
   4.158 +        AND "policy"."id" = "issue"."policy_id";
   4.159 +      -- NOTE: "closed" column of issue must be set at this point
   4.160 +      DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
   4.161 +      INSERT INTO "battle" (
   4.162 +        "issue_id",
   4.163 +        "winning_initiative_id", "losing_initiative_id",
   4.164 +        "count"
   4.165 +      ) SELECT
   4.166 +        "issue_id",
   4.167 +        "winning_initiative_id", "losing_initiative_id",
   4.168 +        "count"
   4.169 +        FROM "battle_view" WHERE "issue_id" = "issue_id_p";
   4.170 +    END;
   4.171 +  $$;
   4.172 +
   4.173 +CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
   4.174 +  RETURNS VOID
   4.175 +  LANGUAGE 'plpgsql' VOLATILE AS $$
   4.176 +    DECLARE
   4.177 +      "issue_row" "issue"%ROWTYPE;
   4.178 +    BEGIN
   4.179 +      SELECT * INTO "issue_row"
   4.180 +        FROM "issue" WHERE "id" = "issue_id_p"
   4.181 +        FOR UPDATE;
   4.182 +      IF "issue_row"."cleaned" ISNULL THEN
   4.183 +        UPDATE "issue" SET
   4.184 +          "state"           = 'voting',
   4.185 +          "closed"          = NULL,
   4.186 +          "ranks_available" = FALSE
   4.187 +          WHERE "id" = "issue_id_p";
   4.188 +        DELETE FROM "issue_comment"
   4.189 +          WHERE "issue_id" = "issue_id_p";
   4.190 +        DELETE FROM "voting_comment"
   4.191 +          WHERE "issue_id" = "issue_id_p";
   4.192 +        DELETE FROM "delegating_voter"
   4.193 +          WHERE "issue_id" = "issue_id_p";
   4.194 +        DELETE FROM "direct_voter"
   4.195 +          WHERE "issue_id" = "issue_id_p";
   4.196 +        DELETE FROM "delegating_interest_snapshot"
   4.197 +          WHERE "issue_id" = "issue_id_p";
   4.198 +        DELETE FROM "direct_interest_snapshot"
   4.199 +          WHERE "issue_id" = "issue_id_p";
   4.200 +        DELETE FROM "delegating_population_snapshot"
   4.201 +          WHERE "issue_id" = "issue_id_p";
   4.202 +        DELETE FROM "direct_population_snapshot"
   4.203 +          WHERE "issue_id" = "issue_id_p";
   4.204 +        DELETE FROM "non_voter"
   4.205 +          WHERE "issue_id" = "issue_id_p";
   4.206 +        DELETE FROM "delegation"
   4.207 +          WHERE "issue_id" = "issue_id_p";
   4.208 +        DELETE FROM "supporter"
   4.209 +          WHERE "issue_id" = "issue_id_p";
   4.210 +        UPDATE "issue" SET
   4.211 +          "state"           = "issue_row"."state",
   4.212 +          "closed"          = "issue_row"."closed",
   4.213 +          "ranks_available" = "issue_row"."ranks_available",
   4.214 +          "cleaned"         = now()
   4.215 +          WHERE "id" = "issue_id_p";
   4.216 +      END IF;
   4.217 +      RETURN;
   4.218 +    END;
   4.219 +  $$;
   4.220 +
   4.221 +COMMIT;

Impressum / About Us