liquid_feedback_core

changeset 61:598af132a6f9 v1.2.2

Save battle data from calls of "clean_issue"("issue"."id")

Details:
- Calling "clean_issue" is also allowed before ranks are calculated
- View "battle" was renamed to "battle_view"
- "battle_view" only contains entries for closed issues
- New table "battle", which is filled when an issue is closed
- Version change to v1.2.2
- Update script to v1.2.2 materializes battles of existent (closed) issues
author jbe
date Tue Jul 20 00:53:30 2010 +0200 (2010-07-20)
parents e83ff2e2e051
children bc01495a4afa
files core.sql update/core-update.v1.2.1-v1.2.2.sql
line diff
     1.1 --- a/core.sql	Mon Jul 19 22:52:21 2010 +0200
     1.2 +++ b/core.sql	Tue Jul 20 00:53:30 2010 +0200
     1.3 @@ -6,7 +6,7 @@
     1.4  BEGIN;
     1.5  
     1.6  CREATE VIEW "liquid_feedback_version" AS
     1.7 -  SELECT * FROM (VALUES ('1.2.1', 1, 2, 1))
     1.8 +  SELECT * FROM (VALUES ('1.2.2', 1, 2, 2))
     1.9    AS "subquery"("string", "major", "minor", "revision");
    1.10  
    1.11  
    1.12 @@ -367,12 +367,8 @@
    1.13            "accepted"     <= "half_frozen" AND
    1.14            "half_frozen"  <= "fully_frozen" AND
    1.15            "fully_frozen" <= "closed" ),
    1.16 -        CONSTRAINT "clean_restriction" CHECK (
    1.17 -          "cleaned" ISNULL OR (
    1.18 -            "closed" NOTNULL AND (
    1.19 -              "fully_frozen" ISNULL OR "ranks_available"
    1.20 -            )
    1.21 -          ) ),
    1.22 +        CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
    1.23 +          "cleaned" ISNULL OR "closed" NOTNULL ),
    1.24          CONSTRAINT "last_snapshot_on_full_freeze"
    1.25            CHECK ("snapshot" = "fully_frozen"),  -- NOTE: snapshot can be set, while frozen is NULL yet
    1.26          CONSTRAINT "freeze_requires_snapshot"
    1.27 @@ -472,6 +468,18 @@
    1.28  COMMENT ON COLUMN "initiative"."rank"           IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
    1.29  
    1.30  
    1.31 +CREATE TABLE "battle" (
    1.32 +        PRIMARY KEY ("issue_id", "winning_initiative_id", "losing_initiative_id"),
    1.33 +        "issue_id"              INT4,
    1.34 +        "winning_initiative_id" INT4,
    1.35 +        FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.36 +        "losing_initiative_id"  INT4,
    1.37 +        FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.38 +        "count"                 INT4            NOT NULL);
    1.39 +
    1.40 +COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue';
    1.41 +
    1.42 +
    1.43  CREATE TABLE "initiative_setting" (
    1.44          PRIMARY KEY ("member_id", "key", "initiative_id"),
    1.45          "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.46 @@ -1428,7 +1436,7 @@
    1.47  COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
    1.48  
    1.49  
    1.50 -CREATE VIEW "battle" AS
    1.51 +CREATE VIEW "battle_view" AS
    1.52    SELECT
    1.53      "issue"."id" AS "issue_id",
    1.54      "winning_initiative"."id" AS "winning_initiative_id",
    1.55 @@ -1454,14 +1462,15 @@
    1.56    LEFT JOIN "vote" AS "worse_vote"
    1.57      ON "direct_voter"."member_id" = "worse_vote"."member_id"
    1.58      AND "losing_initiative"."id" = "worse_vote"."initiative_id"
    1.59 -  WHERE
    1.60 -    "winning_initiative"."id" != "losing_initiative"."id"
    1.61 +  WHERE "issue"."closed" NOTNULL
    1.62 +  AND "issue"."cleaned" ISNULL
    1.63 +  AND "winning_initiative"."id" != "losing_initiative"."id"
    1.64    GROUP BY
    1.65      "issue"."id",
    1.66      "winning_initiative"."id",
    1.67      "losing_initiative"."id";
    1.68  
    1.69 -COMMENT ON VIEW "battle" IS 'Number of members preferring one initiative over another';
    1.70 +COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative to another; Used to fill "battle" table';
    1.71  
    1.72  
    1.73  CREATE VIEW "expired_session" AS
    1.74 @@ -2711,6 +2720,7 @@
    1.75        END LOOP;
    1.76        PERFORM "add_vote_delegations"("issue_id_p");
    1.77        UPDATE "issue" SET
    1.78 +        "closed" = now(),
    1.79          "voter_count" = (
    1.80            SELECT coalesce(sum("weight"), 0)
    1.81            FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
    1.82 @@ -2760,7 +2770,17 @@
    1.83          WHERE "vote_counts"."initiative_id" = "initiative"."id"
    1.84          AND "issue"."id" = "initiative"."issue_id"
    1.85          AND "policy"."id" = "issue"."policy_id";
    1.86 -      UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";
    1.87 +      -- NOTE: "closed" column of issue must be set at this point
    1.88 +      DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
    1.89 +      INSERT INTO "battle" (
    1.90 +        "issue_id",
    1.91 +        "winning_initiative_id", "losing_initiative_id",
    1.92 +        "count"
    1.93 +      ) SELECT
    1.94 +        "issue_id",
    1.95 +        "winning_initiative_id", "losing_initiative_id",
    1.96 +        "count"
    1.97 +        FROM "battle_view" WHERE "issue_id" = "issue_id_p";
    1.98      END;
    1.99    $$;
   1.100  
     2.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     2.2 +++ b/update/core-update.v1.2.1-v1.2.2.sql	Tue Jul 20 00:53:30 2010 +0200
     2.3 @@ -0,0 +1,204 @@
     2.4 +BEGIN;
     2.5 +
     2.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS
     2.7 +  SELECT * FROM (VALUES ('1.2.2', 1, 2, 2))
     2.8 +  AS "subquery"("string", "major", "minor", "revision");
     2.9 +
    2.10 +ALTER TABLE "issue" DROP CONSTRAINT "clean_restriction";
    2.11 +
    2.12 +ALTER TABLE "issue" ADD CONSTRAINT "only_closed_issues_may_be_cleaned"
    2.13 +  CHECK ("cleaned" ISNULL OR "closed" NOTNULL);
    2.14 +
    2.15 +ALTER VIEW "battle" RENAME TO "battle_view";
    2.16 +
    2.17 +CREATE TABLE "battle" (
    2.18 +        PRIMARY KEY ("issue_id", "winning_initiative_id", "losing_initiative_id"),
    2.19 +        "issue_id"              INT4,
    2.20 +        "winning_initiative_id" INT4,
    2.21 +        FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
    2.22 +        "losing_initiative_id"  INT4,
    2.23 +        FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
    2.24 +        "count"                 INT4            NOT NULL);
    2.25 +
    2.26 +COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue';
    2.27 +
    2.28 +CREATE OR REPLACE VIEW "battle_view" AS
    2.29 +  SELECT
    2.30 +    "issue"."id" AS "issue_id",
    2.31 +    "winning_initiative"."id" AS "winning_initiative_id",
    2.32 +    "losing_initiative"."id" AS "losing_initiative_id",
    2.33 +    sum(
    2.34 +      CASE WHEN
    2.35 +        coalesce("better_vote"."grade", 0) >
    2.36 +        coalesce("worse_vote"."grade", 0)
    2.37 +      THEN "direct_voter"."weight" ELSE 0 END
    2.38 +    ) AS "count"
    2.39 +  FROM "issue"
    2.40 +  LEFT JOIN "direct_voter"
    2.41 +  ON "issue"."id" = "direct_voter"."issue_id"
    2.42 +  JOIN "initiative" AS "winning_initiative"
    2.43 +    ON "issue"."id" = "winning_initiative"."issue_id"
    2.44 +    AND "winning_initiative"."agreed"
    2.45 +  JOIN "initiative" AS "losing_initiative"
    2.46 +    ON "issue"."id" = "losing_initiative"."issue_id"
    2.47 +    AND "losing_initiative"."agreed"
    2.48 +  LEFT JOIN "vote" AS "better_vote"
    2.49 +    ON "direct_voter"."member_id" = "better_vote"."member_id"
    2.50 +    AND "winning_initiative"."id" = "better_vote"."initiative_id"
    2.51 +  LEFT JOIN "vote" AS "worse_vote"
    2.52 +    ON "direct_voter"."member_id" = "worse_vote"."member_id"
    2.53 +    AND "losing_initiative"."id" = "worse_vote"."initiative_id"
    2.54 +  WHERE "issue"."closed" NOTNULL
    2.55 +  AND "issue"."cleaned" ISNULL
    2.56 +  AND "winning_initiative"."id" != "losing_initiative"."id"
    2.57 +  GROUP BY
    2.58 +    "issue"."id",
    2.59 +    "winning_initiative"."id",
    2.60 +    "losing_initiative"."id";
    2.61 +
    2.62 +COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative to another; Used to fill "battle" table';
    2.63 +
    2.64 +INSERT INTO "battle" (
    2.65 +  "issue_id",
    2.66 +  "winning_initiative_id",
    2.67 +  "losing_initiative_id",
    2.68 +  "count"
    2.69 +) SELECT
    2.70 +  "issue_id",
    2.71 +  "winning_initiative_id", "losing_initiative_id",
    2.72 +  "count"
    2.73 +  FROM "battle_view";
    2.74 +
    2.75 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
    2.76 +  RETURNS VOID
    2.77 +  LANGUAGE 'plpgsql' VOLATILE AS $$
    2.78 +    DECLARE
    2.79 +      "issue_row"   "issue"%ROWTYPE;
    2.80 +      "member_id_v" "member"."id"%TYPE;
    2.81 +    BEGIN
    2.82 +      PERFORM "global_lock"();
    2.83 +      SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
    2.84 +      DELETE FROM "delegating_voter"
    2.85 +        WHERE "issue_id" = "issue_id_p";
    2.86 +      DELETE FROM "direct_voter"
    2.87 +        WHERE "issue_id" = "issue_id_p"
    2.88 +        AND "autoreject" = TRUE;
    2.89 +      DELETE FROM "direct_voter" USING "member"
    2.90 +        WHERE "direct_voter"."member_id" = "member"."id"
    2.91 +        AND "direct_voter"."issue_id" = "issue_id_p"
    2.92 +        AND "member"."active" = FALSE;
    2.93 +      UPDATE "direct_voter" SET "weight" = 1
    2.94 +        WHERE "issue_id" = "issue_id_p";
    2.95 +      PERFORM "add_vote_delegations"("issue_id_p");
    2.96 +      FOR "member_id_v" IN
    2.97 +        SELECT "interest"."member_id"
    2.98 +          FROM "interest"
    2.99 +          LEFT JOIN "direct_voter"
   2.100 +            ON "interest"."member_id" = "direct_voter"."member_id"
   2.101 +            AND "interest"."issue_id" = "direct_voter"."issue_id"
   2.102 +          LEFT JOIN "delegating_voter"
   2.103 +            ON "interest"."member_id" = "delegating_voter"."member_id"
   2.104 +            AND "interest"."issue_id" = "delegating_voter"."issue_id"
   2.105 +          WHERE "interest"."issue_id" = "issue_id_p"
   2.106 +          AND "interest"."autoreject" = TRUE
   2.107 +          AND "direct_voter"."member_id" ISNULL
   2.108 +          AND "delegating_voter"."member_id" ISNULL
   2.109 +        UNION SELECT "membership"."member_id"
   2.110 +          FROM "membership"
   2.111 +          LEFT JOIN "interest"
   2.112 +            ON "membership"."member_id" = "interest"."member_id"
   2.113 +            AND "interest"."issue_id" = "issue_id_p"
   2.114 +          LEFT JOIN "direct_voter"
   2.115 +            ON "membership"."member_id" = "direct_voter"."member_id"
   2.116 +            AND "direct_voter"."issue_id" = "issue_id_p"
   2.117 +          LEFT JOIN "delegating_voter"
   2.118 +            ON "membership"."member_id" = "delegating_voter"."member_id"
   2.119 +            AND "delegating_voter"."issue_id" = "issue_id_p"
   2.120 +          WHERE "membership"."area_id" = "issue_row"."area_id"
   2.121 +          AND "membership"."autoreject" = TRUE
   2.122 +          AND "interest"."autoreject" ISNULL
   2.123 +          AND "direct_voter"."member_id" ISNULL
   2.124 +          AND "delegating_voter"."member_id" ISNULL
   2.125 +      LOOP
   2.126 +        INSERT INTO "direct_voter"
   2.127 +          ("member_id", "issue_id", "weight", "autoreject") VALUES
   2.128 +          ("member_id_v", "issue_id_p", 1, TRUE);
   2.129 +        INSERT INTO "vote" (
   2.130 +          "member_id",
   2.131 +          "issue_id",
   2.132 +          "initiative_id",
   2.133 +          "grade"
   2.134 +          ) SELECT
   2.135 +            "member_id_v" AS "member_id",
   2.136 +            "issue_id_p"  AS "issue_id",
   2.137 +            "id"          AS "initiative_id",
   2.138 +            -1            AS "grade"
   2.139 +          FROM "initiative" WHERE "issue_id" = "issue_id_p";
   2.140 +      END LOOP;
   2.141 +      PERFORM "add_vote_delegations"("issue_id_p");
   2.142 +      UPDATE "issue" SET
   2.143 +        "closed" = now(),
   2.144 +        "voter_count" = (
   2.145 +          SELECT coalesce(sum("weight"), 0)
   2.146 +          FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
   2.147 +        )
   2.148 +        WHERE "id" = "issue_id_p";
   2.149 +      UPDATE "initiative" SET
   2.150 +        "positive_votes" = "vote_counts"."positive_votes",
   2.151 +        "negative_votes" = "vote_counts"."negative_votes",
   2.152 +        "agreed" = CASE WHEN "majority_strict" THEN
   2.153 +          "vote_counts"."positive_votes" * "majority_den" >
   2.154 +          "majority_num" *
   2.155 +          ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
   2.156 +        ELSE
   2.157 +          "vote_counts"."positive_votes" * "majority_den" >=
   2.158 +          "majority_num" *
   2.159 +          ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
   2.160 +        END
   2.161 +        FROM
   2.162 +          ( SELECT
   2.163 +              "initiative"."id" AS "initiative_id",
   2.164 +              coalesce(
   2.165 +                sum(
   2.166 +                  CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
   2.167 +                ),
   2.168 +                0
   2.169 +              ) AS "positive_votes",
   2.170 +              coalesce(
   2.171 +                sum(
   2.172 +                  CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
   2.173 +                ),
   2.174 +                0
   2.175 +              ) AS "negative_votes"
   2.176 +            FROM "initiative"
   2.177 +            JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
   2.178 +            JOIN "policy" ON "issue"."policy_id" = "policy"."id"
   2.179 +            LEFT JOIN "direct_voter"
   2.180 +              ON "direct_voter"."issue_id" = "initiative"."issue_id"
   2.181 +            LEFT JOIN "vote"
   2.182 +              ON "vote"."initiative_id" = "initiative"."id"
   2.183 +              AND "vote"."member_id" = "direct_voter"."member_id"
   2.184 +            WHERE "initiative"."issue_id" = "issue_id_p"
   2.185 +            AND "initiative"."admitted"  -- NOTE: NULL case is handled too
   2.186 +            GROUP BY "initiative"."id"
   2.187 +          ) AS "vote_counts",
   2.188 +          "issue",
   2.189 +          "policy"
   2.190 +        WHERE "vote_counts"."initiative_id" = "initiative"."id"
   2.191 +        AND "issue"."id" = "initiative"."issue_id"
   2.192 +        AND "policy"."id" = "issue"."policy_id";
   2.193 +      -- NOTE: "closed" column of issue must be set at this point
   2.194 +      DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
   2.195 +      INSERT INTO "battle" (
   2.196 +        "issue_id",
   2.197 +        "winning_initiative_id", "losing_initiative_id",
   2.198 +        "count"
   2.199 +      ) SELECT
   2.200 +        "issue_id",
   2.201 +        "winning_initiative_id", "losing_initiative_id",
   2.202 +        "count"
   2.203 +        FROM "battle_view" WHERE "issue_id" = "issue_id_p";
   2.204 +    END;
   2.205 +  $$;
   2.206 +
   2.207 +COMMIT;

Impressum / About Us