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