liquid_feedback_core

changeset 131:284113a50c3b

Merged bugfix in function "close_voting"
author jbe
date Tue May 24 23:16:36 2011 +0200 (2011-05-24)
parents 5ea2f9c5ba9a 952200ce339b
children 3d5e38ea2fab
files core.sql
line diff
     1.1 --- a/.hgtags	Tue May 24 14:58:42 2011 +0200
     1.2 +++ b/.hgtags	Tue May 24 23:16:36 2011 +0200
     1.3 @@ -42,3 +42,4 @@
     1.4  fb9688f31740c4a95a9f2c63c114f64f812ddf05 v1.3.0
     1.5  ea4928ccc8eb303fdaf268f57c5572099b89d0ef v1.3.1
     1.6  0144b703b2610a4c93158427d5eff79cca3fca84 v1.4.0_rc1
     1.7 +07cfc7c1ce3f9c7191a028d3fabf27881edcc8c7 v1.4.0_rc2
     2.1 --- a/core.sql	Tue May 24 14:58:42 2011 +0200
     2.2 +++ b/core.sql	Tue May 24 23:16:36 2011 +0200
     2.3 @@ -7,7 +7,7 @@
     2.4  BEGIN;
     2.5  
     2.6  CREATE VIEW "liquid_feedback_version" AS
     2.7 -  SELECT * FROM (VALUES ('1.4.0_rc1', 1, 4, -1))
     2.8 +  SELECT * FROM (VALUES ('1.4.0_rc2', 1, 4, -1))
     2.9    AS "subquery"("string", "major", "minor", "revision");
    2.10  
    2.11  
    2.12 @@ -3438,8 +3438,8 @@
    2.13        "member_id_v" "member"."id"%TYPE;
    2.14      BEGIN
    2.15        PERFORM "lock_issue"("issue_id_p");
    2.16 -      SELECT "id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
    2.17 -      SELECT "id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
    2.18 +      SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
    2.19 +      SELECT "unit_id" INTO "unit_id_v" FROM "area"  WHERE "id" = "area_id_v";
    2.20        DELETE FROM "delegating_voter"
    2.21          WHERE "issue_id" = "issue_id_p";
    2.22        DELETE FROM "direct_voter"
     3.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     3.2 +++ b/update/core-update.v1.4.0_rc1-v1.4.0_rc2.sql	Tue May 24 23:16:36 2011 +0200
     3.3 @@ -0,0 +1,161 @@
     3.4 +BEGIN;
     3.5 +
     3.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     3.7 +  SELECT * FROM (VALUES ('1.4.0_rc2', 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 +          LEFT JOIN "direct_voter"
    3.52 +            ON "interest"."member_id" = "direct_voter"."member_id"
    3.53 +            AND "interest"."issue_id" = "direct_voter"."issue_id"
    3.54 +          LEFT JOIN "delegating_voter"
    3.55 +            ON "interest"."member_id" = "delegating_voter"."member_id"
    3.56 +            AND "interest"."issue_id" = "delegating_voter"."issue_id"
    3.57 +          WHERE "interest"."issue_id" = "issue_id_p"
    3.58 +          AND "interest"."autoreject" = TRUE
    3.59 +          AND "member"."active"
    3.60 +          AND "direct_voter"."member_id" ISNULL
    3.61 +          AND "delegating_voter"."member_id" ISNULL
    3.62 +        UNION SELECT "membership"."member_id"
    3.63 +          FROM "membership"
    3.64 +          JOIN "member"
    3.65 +            ON "membership"."member_id" = "member"."id"
    3.66 +          LEFT JOIN "interest"
    3.67 +            ON "membership"."member_id" = "interest"."member_id"
    3.68 +            AND "interest"."issue_id" = "issue_id_p"
    3.69 +          LEFT JOIN "direct_voter"
    3.70 +            ON "membership"."member_id" = "direct_voter"."member_id"
    3.71 +            AND "direct_voter"."issue_id" = "issue_id_p"
    3.72 +          LEFT JOIN "delegating_voter"
    3.73 +            ON "membership"."member_id" = "delegating_voter"."member_id"
    3.74 +            AND "delegating_voter"."issue_id" = "issue_id_p"
    3.75 +          WHERE "membership"."area_id" = "area_id_v"
    3.76 +          AND "membership"."autoreject" = TRUE
    3.77 +          AND "member"."active"
    3.78 +          AND "interest"."autoreject" ISNULL
    3.79 +          AND "direct_voter"."member_id" ISNULL
    3.80 +          AND "delegating_voter"."member_id" ISNULL
    3.81 +      LOOP
    3.82 +        INSERT INTO "direct_voter"
    3.83 +          ("member_id", "issue_id", "weight", "autoreject") VALUES
    3.84 +          ("member_id_v", "issue_id_p", 1, TRUE);
    3.85 +        INSERT INTO "vote" (
    3.86 +          "member_id",
    3.87 +          "issue_id",
    3.88 +          "initiative_id",
    3.89 +          "grade"
    3.90 +          ) SELECT
    3.91 +            "member_id_v" AS "member_id",
    3.92 +            "issue_id_p"  AS "issue_id",
    3.93 +            "id"          AS "initiative_id",
    3.94 +            -1            AS "grade"
    3.95 +          FROM "initiative" WHERE "issue_id" = "issue_id_p";
    3.96 +      END LOOP;
    3.97 +      PERFORM "add_vote_delegations"("issue_id_p");
    3.98 +      UPDATE "issue" SET
    3.99 +        "state"  = 'calculation',
   3.100 +        "closed" = now(),
   3.101 +        "voter_count" = (
   3.102 +          SELECT coalesce(sum("weight"), 0)
   3.103 +          FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
   3.104 +        )
   3.105 +        WHERE "id" = "issue_id_p";
   3.106 +      UPDATE "initiative" SET
   3.107 +        "positive_votes" = "vote_counts"."positive_votes",
   3.108 +        "negative_votes" = "vote_counts"."negative_votes",
   3.109 +        "agreed" = CASE WHEN "majority_strict" THEN
   3.110 +          "vote_counts"."positive_votes" * "majority_den" >
   3.111 +          "majority_num" *
   3.112 +          ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
   3.113 +        ELSE
   3.114 +          "vote_counts"."positive_votes" * "majority_den" >=
   3.115 +          "majority_num" *
   3.116 +          ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
   3.117 +        END
   3.118 +        FROM
   3.119 +          ( SELECT
   3.120 +              "initiative"."id" AS "initiative_id",
   3.121 +              coalesce(
   3.122 +                sum(
   3.123 +                  CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
   3.124 +                ),
   3.125 +                0
   3.126 +              ) AS "positive_votes",
   3.127 +              coalesce(
   3.128 +                sum(
   3.129 +                  CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
   3.130 +                ),
   3.131 +                0
   3.132 +              ) AS "negative_votes"
   3.133 +            FROM "initiative"
   3.134 +            JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
   3.135 +            JOIN "policy" ON "issue"."policy_id" = "policy"."id"
   3.136 +            LEFT JOIN "direct_voter"
   3.137 +              ON "direct_voter"."issue_id" = "initiative"."issue_id"
   3.138 +            LEFT JOIN "vote"
   3.139 +              ON "vote"."initiative_id" = "initiative"."id"
   3.140 +              AND "vote"."member_id" = "direct_voter"."member_id"
   3.141 +            WHERE "initiative"."issue_id" = "issue_id_p"
   3.142 +            AND "initiative"."admitted"  -- NOTE: NULL case is handled too
   3.143 +            GROUP BY "initiative"."id"
   3.144 +          ) AS "vote_counts",
   3.145 +          "issue",
   3.146 +          "policy"
   3.147 +        WHERE "vote_counts"."initiative_id" = "initiative"."id"
   3.148 +        AND "issue"."id" = "initiative"."issue_id"
   3.149 +        AND "policy"."id" = "issue"."policy_id";
   3.150 +      -- NOTE: "closed" column of issue must be set at this point
   3.151 +      DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
   3.152 +      INSERT INTO "battle" (
   3.153 +        "issue_id",
   3.154 +        "winning_initiative_id", "losing_initiative_id",
   3.155 +        "count"
   3.156 +      ) SELECT
   3.157 +        "issue_id",
   3.158 +        "winning_initiative_id", "losing_initiative_id",
   3.159 +        "count"
   3.160 +        FROM "battle_view" WHERE "issue_id" = "issue_id_p";
   3.161 +    END;
   3.162 +  $$;
   3.163 +
   3.164 +COMMIT;

Impressum / About Us