liquid_feedback_core
changeset 87:e588fdf1676e v1.2.9
Bugfix: "autoreject" only taken into account, when member is "active"
author | jbe |
---|---|
date | Tue Oct 26 22:18:18 2010 +0200 (2010-10-26) |
parents | f77c0f3d443c |
children | dfa00eaa5081 |
files | core.sql update/core-update.v1.2.8-v1.2.9.sql |
line diff
1.1 --- a/core.sql Tue Sep 07 21:20:18 2010 +0200 1.2 +++ b/core.sql Tue Oct 26 22:18:18 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.8', 1, 2, 8)) 1.8 + SELECT * FROM (VALUES ('1.2.9', 1, 2, 9)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11 1.12 @@ -2765,6 +2765,8 @@ 1.13 FOR "member_id_v" IN 1.14 SELECT "interest"."member_id" 1.15 FROM "interest" 1.16 + JOIN "member" 1.17 + ON "interest"."member_id" = "member"."id" 1.18 LEFT JOIN "direct_voter" 1.19 ON "interest"."member_id" = "direct_voter"."member_id" 1.20 AND "interest"."issue_id" = "direct_voter"."issue_id" 1.21 @@ -2773,10 +2775,13 @@ 1.22 AND "interest"."issue_id" = "delegating_voter"."issue_id" 1.23 WHERE "interest"."issue_id" = "issue_id_p" 1.24 AND "interest"."autoreject" = TRUE 1.25 + AND "member"."active" 1.26 AND "direct_voter"."member_id" ISNULL 1.27 AND "delegating_voter"."member_id" ISNULL 1.28 UNION SELECT "membership"."member_id" 1.29 FROM "membership" 1.30 + JOIN "member" 1.31 + ON "membership"."member_id" = "member"."id" 1.32 LEFT JOIN "interest" 1.33 ON "membership"."member_id" = "interest"."member_id" 1.34 AND "interest"."issue_id" = "issue_id_p" 1.35 @@ -2788,6 +2793,7 @@ 1.36 AND "delegating_voter"."issue_id" = "issue_id_p" 1.37 WHERE "membership"."area_id" = "issue_row"."area_id" 1.38 AND "membership"."autoreject" = TRUE 1.39 + AND "member"."active" 1.40 AND "interest"."autoreject" ISNULL 1.41 AND "direct_voter"."member_id" ISNULL 1.42 AND "delegating_voter"."member_id" ISNULL
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 2.2 +++ b/update/core-update.v1.2.8-v1.2.9.sql Tue Oct 26 22:18:18 2010 +0200 2.3 @@ -0,0 +1,145 @@ 2.4 +BEGIN; 2.5 + 2.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 2.7 + SELECT * FROM (VALUES ('1.2.9', 1, 2, 9)) 2.8 + AS "subquery"("string", "major", "minor", "revision"); 2.9 + 2.10 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 2.11 + RETURNS VOID 2.12 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.13 + DECLARE 2.14 + "issue_row" "issue"%ROWTYPE; 2.15 + "member_id_v" "member"."id"%TYPE; 2.16 + BEGIN 2.17 + PERFORM "lock_issue"("issue_id_p"); 2.18 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 2.19 + DELETE FROM "delegating_voter" 2.20 + WHERE "issue_id" = "issue_id_p"; 2.21 + DELETE FROM "direct_voter" 2.22 + WHERE "issue_id" = "issue_id_p" 2.23 + AND "autoreject" = TRUE; 2.24 + DELETE FROM "direct_voter" USING "member" 2.25 + WHERE "direct_voter"."member_id" = "member"."id" 2.26 + AND "direct_voter"."issue_id" = "issue_id_p" 2.27 + AND "member"."active" = FALSE; 2.28 + UPDATE "direct_voter" SET "weight" = 1 2.29 + WHERE "issue_id" = "issue_id_p"; 2.30 + PERFORM "add_vote_delegations"("issue_id_p"); 2.31 + FOR "member_id_v" IN 2.32 + SELECT "interest"."member_id" 2.33 + FROM "interest" 2.34 + JOIN "member" 2.35 + ON "interest"."member_id" = "member"."id" 2.36 + LEFT JOIN "direct_voter" 2.37 + ON "interest"."member_id" = "direct_voter"."member_id" 2.38 + AND "interest"."issue_id" = "direct_voter"."issue_id" 2.39 + LEFT JOIN "delegating_voter" 2.40 + ON "interest"."member_id" = "delegating_voter"."member_id" 2.41 + AND "interest"."issue_id" = "delegating_voter"."issue_id" 2.42 + WHERE "interest"."issue_id" = "issue_id_p" 2.43 + AND "interest"."autoreject" = TRUE 2.44 + AND "member"."active" 2.45 + AND "direct_voter"."member_id" ISNULL 2.46 + AND "delegating_voter"."member_id" ISNULL 2.47 + UNION SELECT "membership"."member_id" 2.48 + FROM "membership" 2.49 + JOIN "member" 2.50 + ON "membership"."member_id" = "member"."id" 2.51 + LEFT JOIN "interest" 2.52 + ON "membership"."member_id" = "interest"."member_id" 2.53 + AND "interest"."issue_id" = "issue_id_p" 2.54 + LEFT JOIN "direct_voter" 2.55 + ON "membership"."member_id" = "direct_voter"."member_id" 2.56 + AND "direct_voter"."issue_id" = "issue_id_p" 2.57 + LEFT JOIN "delegating_voter" 2.58 + ON "membership"."member_id" = "delegating_voter"."member_id" 2.59 + AND "delegating_voter"."issue_id" = "issue_id_p" 2.60 + WHERE "membership"."area_id" = "issue_row"."area_id" 2.61 + AND "membership"."autoreject" = TRUE 2.62 + AND "member"."active" 2.63 + AND "interest"."autoreject" ISNULL 2.64 + AND "direct_voter"."member_id" ISNULL 2.65 + AND "delegating_voter"."member_id" ISNULL 2.66 + LOOP 2.67 + INSERT INTO "direct_voter" 2.68 + ("member_id", "issue_id", "weight", "autoreject") VALUES 2.69 + ("member_id_v", "issue_id_p", 1, TRUE); 2.70 + INSERT INTO "vote" ( 2.71 + "member_id", 2.72 + "issue_id", 2.73 + "initiative_id", 2.74 + "grade" 2.75 + ) SELECT 2.76 + "member_id_v" AS "member_id", 2.77 + "issue_id_p" AS "issue_id", 2.78 + "id" AS "initiative_id", 2.79 + -1 AS "grade" 2.80 + FROM "initiative" WHERE "issue_id" = "issue_id_p"; 2.81 + END LOOP; 2.82 + PERFORM "add_vote_delegations"("issue_id_p"); 2.83 + UPDATE "issue" SET 2.84 + "closed" = now(), 2.85 + "voter_count" = ( 2.86 + SELECT coalesce(sum("weight"), 0) 2.87 + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 2.88 + ) 2.89 + WHERE "id" = "issue_id_p"; 2.90 + UPDATE "initiative" SET 2.91 + "positive_votes" = "vote_counts"."positive_votes", 2.92 + "negative_votes" = "vote_counts"."negative_votes", 2.93 + "agreed" = CASE WHEN "majority_strict" THEN 2.94 + "vote_counts"."positive_votes" * "majority_den" > 2.95 + "majority_num" * 2.96 + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 2.97 + ELSE 2.98 + "vote_counts"."positive_votes" * "majority_den" >= 2.99 + "majority_num" * 2.100 + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 2.101 + END 2.102 + FROM 2.103 + ( SELECT 2.104 + "initiative"."id" AS "initiative_id", 2.105 + coalesce( 2.106 + sum( 2.107 + CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END 2.108 + ), 2.109 + 0 2.110 + ) AS "positive_votes", 2.111 + coalesce( 2.112 + sum( 2.113 + CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END 2.114 + ), 2.115 + 0 2.116 + ) AS "negative_votes" 2.117 + FROM "initiative" 2.118 + JOIN "issue" ON "initiative"."issue_id" = "issue"."id" 2.119 + JOIN "policy" ON "issue"."policy_id" = "policy"."id" 2.120 + LEFT JOIN "direct_voter" 2.121 + ON "direct_voter"."issue_id" = "initiative"."issue_id" 2.122 + LEFT JOIN "vote" 2.123 + ON "vote"."initiative_id" = "initiative"."id" 2.124 + AND "vote"."member_id" = "direct_voter"."member_id" 2.125 + WHERE "initiative"."issue_id" = "issue_id_p" 2.126 + AND "initiative"."admitted" -- NOTE: NULL case is handled too 2.127 + GROUP BY "initiative"."id" 2.128 + ) AS "vote_counts", 2.129 + "issue", 2.130 + "policy" 2.131 + WHERE "vote_counts"."initiative_id" = "initiative"."id" 2.132 + AND "issue"."id" = "initiative"."issue_id" 2.133 + AND "policy"."id" = "issue"."policy_id"; 2.134 + -- NOTE: "closed" column of issue must be set at this point 2.135 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 2.136 + INSERT INTO "battle" ( 2.137 + "issue_id", 2.138 + "winning_initiative_id", "losing_initiative_id", 2.139 + "count" 2.140 + ) SELECT 2.141 + "issue_id", 2.142 + "winning_initiative_id", "losing_initiative_id", 2.143 + "count" 2.144 + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 2.145 + END; 2.146 + $$; 2.147 + 2.148 +COMMIT;