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
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;