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;