# HG changeset patch # User jbe # Date 1279580010 -7200 # Node ID 598af132a6f9f0e63871728cbcc4477c6f12048b # Parent e83ff2e2e051de6fddd49271db78ec4be5f4cf76 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 diff -r e83ff2e2e051 -r 598af132a6f9 core.sql --- a/core.sql Mon Jul 19 22:52:21 2010 +0200 +++ b/core.sql Tue Jul 20 00:53:30 2010 +0200 @@ -6,7 +6,7 @@ BEGIN; CREATE VIEW "liquid_feedback_version" AS - SELECT * FROM (VALUES ('1.2.1', 1, 2, 1)) + SELECT * FROM (VALUES ('1.2.2', 1, 2, 2)) AS "subquery"("string", "major", "minor", "revision"); @@ -367,12 +367,8 @@ "accepted" <= "half_frozen" AND "half_frozen" <= "fully_frozen" AND "fully_frozen" <= "closed" ), - CONSTRAINT "clean_restriction" CHECK ( - "cleaned" ISNULL OR ( - "closed" NOTNULL AND ( - "fully_frozen" ISNULL OR "ranks_available" - ) - ) ), + CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK ( + "cleaned" ISNULL OR "closed" NOTNULL ), CONSTRAINT "last_snapshot_on_full_freeze" CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet CONSTRAINT "freeze_requires_snapshot" @@ -472,6 +468,18 @@ COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"'; +CREATE TABLE "battle" ( + PRIMARY KEY ("issue_id", "winning_initiative_id", "losing_initiative_id"), + "issue_id" INT4, + "winning_initiative_id" INT4, + FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, + "losing_initiative_id" INT4, + FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, + "count" INT4 NOT NULL); + +COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue'; + + CREATE TABLE "initiative_setting" ( PRIMARY KEY ("member_id", "key", "initiative_id"), "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, @@ -1428,7 +1436,7 @@ COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction'; -CREATE VIEW "battle" AS +CREATE VIEW "battle_view" AS SELECT "issue"."id" AS "issue_id", "winning_initiative"."id" AS "winning_initiative_id", @@ -1454,14 +1462,15 @@ LEFT JOIN "vote" AS "worse_vote" ON "direct_voter"."member_id" = "worse_vote"."member_id" AND "losing_initiative"."id" = "worse_vote"."initiative_id" - WHERE - "winning_initiative"."id" != "losing_initiative"."id" + WHERE "issue"."closed" NOTNULL + AND "issue"."cleaned" ISNULL + AND "winning_initiative"."id" != "losing_initiative"."id" GROUP BY "issue"."id", "winning_initiative"."id", "losing_initiative"."id"; -COMMENT ON VIEW "battle" IS 'Number of members preferring one initiative over another'; +COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative to another; Used to fill "battle" table'; CREATE VIEW "expired_session" AS @@ -2711,6 +2720,7 @@ END LOOP; PERFORM "add_vote_delegations"("issue_id_p"); UPDATE "issue" SET + "closed" = now(), "voter_count" = ( SELECT coalesce(sum("weight"), 0) FROM "direct_voter" WHERE "issue_id" = "issue_id_p" @@ -2760,7 +2770,17 @@ WHERE "vote_counts"."initiative_id" = "initiative"."id" AND "issue"."id" = "initiative"."issue_id" AND "policy"."id" = "issue"."policy_id"; - UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p"; + -- NOTE: "closed" column of issue must be set at this point + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; + INSERT INTO "battle" ( + "issue_id", + "winning_initiative_id", "losing_initiative_id", + "count" + ) SELECT + "issue_id", + "winning_initiative_id", "losing_initiative_id", + "count" + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; END; $$; diff -r e83ff2e2e051 -r 598af132a6f9 update/core-update.v1.2.1-v1.2.2.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/update/core-update.v1.2.1-v1.2.2.sql Tue Jul 20 00:53:30 2010 +0200 @@ -0,0 +1,204 @@ +BEGIN; + +CREATE OR REPLACE VIEW "liquid_feedback_version" AS + SELECT * FROM (VALUES ('1.2.2', 1, 2, 2)) + AS "subquery"("string", "major", "minor", "revision"); + +ALTER TABLE "issue" DROP CONSTRAINT "clean_restriction"; + +ALTER TABLE "issue" ADD CONSTRAINT "only_closed_issues_may_be_cleaned" + CHECK ("cleaned" ISNULL OR "closed" NOTNULL); + +ALTER VIEW "battle" RENAME TO "battle_view"; + +CREATE TABLE "battle" ( + PRIMARY KEY ("issue_id", "winning_initiative_id", "losing_initiative_id"), + "issue_id" INT4, + "winning_initiative_id" INT4, + FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, + "losing_initiative_id" INT4, + FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE, + "count" INT4 NOT NULL); + +COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue'; + +CREATE OR REPLACE VIEW "battle_view" AS + SELECT + "issue"."id" AS "issue_id", + "winning_initiative"."id" AS "winning_initiative_id", + "losing_initiative"."id" AS "losing_initiative_id", + sum( + CASE WHEN + coalesce("better_vote"."grade", 0) > + coalesce("worse_vote"."grade", 0) + THEN "direct_voter"."weight" ELSE 0 END + ) AS "count" + FROM "issue" + LEFT JOIN "direct_voter" + ON "issue"."id" = "direct_voter"."issue_id" + JOIN "initiative" AS "winning_initiative" + ON "issue"."id" = "winning_initiative"."issue_id" + AND "winning_initiative"."agreed" + JOIN "initiative" AS "losing_initiative" + ON "issue"."id" = "losing_initiative"."issue_id" + AND "losing_initiative"."agreed" + LEFT JOIN "vote" AS "better_vote" + ON "direct_voter"."member_id" = "better_vote"."member_id" + AND "winning_initiative"."id" = "better_vote"."initiative_id" + LEFT JOIN "vote" AS "worse_vote" + ON "direct_voter"."member_id" = "worse_vote"."member_id" + AND "losing_initiative"."id" = "worse_vote"."initiative_id" + WHERE "issue"."closed" NOTNULL + AND "issue"."cleaned" ISNULL + AND "winning_initiative"."id" != "losing_initiative"."id" + GROUP BY + "issue"."id", + "winning_initiative"."id", + "losing_initiative"."id"; + +COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative to another; Used to fill "battle" table'; + +INSERT INTO "battle" ( + "issue_id", + "winning_initiative_id", + "losing_initiative_id", + "count" +) SELECT + "issue_id", + "winning_initiative_id", "losing_initiative_id", + "count" + FROM "battle_view"; + +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + DECLARE + "issue_row" "issue"%ROWTYPE; + "member_id_v" "member"."id"%TYPE; + BEGIN + PERFORM "global_lock"(); + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; + DELETE FROM "delegating_voter" + WHERE "issue_id" = "issue_id_p"; + DELETE FROM "direct_voter" + WHERE "issue_id" = "issue_id_p" + AND "autoreject" = TRUE; + DELETE FROM "direct_voter" USING "member" + WHERE "direct_voter"."member_id" = "member"."id" + AND "direct_voter"."issue_id" = "issue_id_p" + AND "member"."active" = FALSE; + UPDATE "direct_voter" SET "weight" = 1 + WHERE "issue_id" = "issue_id_p"; + PERFORM "add_vote_delegations"("issue_id_p"); + FOR "member_id_v" IN + SELECT "interest"."member_id" + FROM "interest" + LEFT JOIN "direct_voter" + ON "interest"."member_id" = "direct_voter"."member_id" + AND "interest"."issue_id" = "direct_voter"."issue_id" + LEFT JOIN "delegating_voter" + ON "interest"."member_id" = "delegating_voter"."member_id" + AND "interest"."issue_id" = "delegating_voter"."issue_id" + WHERE "interest"."issue_id" = "issue_id_p" + AND "interest"."autoreject" = TRUE + AND "direct_voter"."member_id" ISNULL + AND "delegating_voter"."member_id" ISNULL + UNION SELECT "membership"."member_id" + FROM "membership" + LEFT JOIN "interest" + ON "membership"."member_id" = "interest"."member_id" + AND "interest"."issue_id" = "issue_id_p" + LEFT JOIN "direct_voter" + ON "membership"."member_id" = "direct_voter"."member_id" + AND "direct_voter"."issue_id" = "issue_id_p" + LEFT JOIN "delegating_voter" + ON "membership"."member_id" = "delegating_voter"."member_id" + AND "delegating_voter"."issue_id" = "issue_id_p" + WHERE "membership"."area_id" = "issue_row"."area_id" + AND "membership"."autoreject" = TRUE + AND "interest"."autoreject" ISNULL + AND "direct_voter"."member_id" ISNULL + AND "delegating_voter"."member_id" ISNULL + LOOP + INSERT INTO "direct_voter" + ("member_id", "issue_id", "weight", "autoreject") VALUES + ("member_id_v", "issue_id_p", 1, TRUE); + INSERT INTO "vote" ( + "member_id", + "issue_id", + "initiative_id", + "grade" + ) SELECT + "member_id_v" AS "member_id", + "issue_id_p" AS "issue_id", + "id" AS "initiative_id", + -1 AS "grade" + FROM "initiative" WHERE "issue_id" = "issue_id_p"; + END LOOP; + PERFORM "add_vote_delegations"("issue_id_p"); + UPDATE "issue" SET + "closed" = now(), + "voter_count" = ( + SELECT coalesce(sum("weight"), 0) + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" + ) + WHERE "id" = "issue_id_p"; + UPDATE "initiative" SET + "positive_votes" = "vote_counts"."positive_votes", + "negative_votes" = "vote_counts"."negative_votes", + "agreed" = CASE WHEN "majority_strict" THEN + "vote_counts"."positive_votes" * "majority_den" > + "majority_num" * + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") + ELSE + "vote_counts"."positive_votes" * "majority_den" >= + "majority_num" * + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") + END + FROM + ( SELECT + "initiative"."id" AS "initiative_id", + coalesce( + sum( + CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END + ), + 0 + ) AS "positive_votes", + coalesce( + sum( + CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END + ), + 0 + ) AS "negative_votes" + FROM "initiative" + JOIN "issue" ON "initiative"."issue_id" = "issue"."id" + JOIN "policy" ON "issue"."policy_id" = "policy"."id" + LEFT JOIN "direct_voter" + ON "direct_voter"."issue_id" = "initiative"."issue_id" + LEFT JOIN "vote" + ON "vote"."initiative_id" = "initiative"."id" + AND "vote"."member_id" = "direct_voter"."member_id" + WHERE "initiative"."issue_id" = "issue_id_p" + AND "initiative"."admitted" -- NOTE: NULL case is handled too + GROUP BY "initiative"."id" + ) AS "vote_counts", + "issue", + "policy" + WHERE "vote_counts"."initiative_id" = "initiative"."id" + AND "issue"."id" = "initiative"."issue_id" + AND "policy"."id" = "issue"."policy_id"; + -- NOTE: "closed" column of issue must be set at this point + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; + INSERT INTO "battle" ( + "issue_id", + "winning_initiative_id", "losing_initiative_id", + "count" + ) SELECT + "issue_id", + "winning_initiative_id", "losing_initiative_id", + "count" + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; + END; + $$; + +COMMIT;