liquid_feedback_core
changeset 166:7f2011c7b95e
merge
author | jbe |
---|---|
date | Sun Jun 05 01:12:37 2011 +0200 (2011-06-05) |
parents | 7c8b812740ac 662d714de82e |
children | de59f6b1daf3 |
files | .hgtags core.sql |
line diff
1.1 --- a/.hgtags Sat Jun 04 22:52:50 2011 +0200 1.2 +++ b/.hgtags Sun Jun 05 01:12:37 2011 +0200 1.3 @@ -44,3 +44,4 @@ 1.4 0144b703b2610a4c93158427d5eff79cca3fca84 v1.4.0_rc1 1.5 07cfc7c1ce3f9c7191a028d3fabf27881edcc8c7 v1.4.0_rc2 1.6 0fa3c4739ca75a0dd55c073903e274ec20fce137 v1.4.0_rc3 1.7 +1be788313b84e0b27f396f34be3838ccb66287a4 v1.4.0_rc4
3.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 3.2 +++ b/update/core-update.v1.4.0_rc2-v1.4.0_rc3.sql Sun Jun 05 01:12:37 2011 +0200 3.3 @@ -0,0 +1,169 @@ 3.4 +BEGIN; 3.5 + 3.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 3.7 + SELECT * FROM (VALUES ('1.4.0_rc3', 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 + JOIN "privilege" 3.52 + ON "privilege"."unit_id" = "unit_id_v" 3.53 + AND "privilege"."member_id" = "member"."id" 3.54 + LEFT JOIN "direct_voter" 3.55 + ON "interest"."member_id" = "direct_voter"."member_id" 3.56 + AND "interest"."issue_id" = "direct_voter"."issue_id" 3.57 + LEFT JOIN "delegating_voter" 3.58 + ON "interest"."member_id" = "delegating_voter"."member_id" 3.59 + AND "interest"."issue_id" = "delegating_voter"."issue_id" 3.60 + WHERE "interest"."issue_id" = "issue_id_p" 3.61 + AND "interest"."autoreject" = TRUE 3.62 + AND "member"."active" 3.63 + AND "privilege"."voting_right" 3.64 + AND "direct_voter"."member_id" ISNULL 3.65 + AND "delegating_voter"."member_id" ISNULL 3.66 + UNION SELECT "membership"."member_id" 3.67 + FROM "membership" 3.68 + JOIN "member" 3.69 + ON "membership"."member_id" = "member"."id" 3.70 + JOIN "privilege" 3.71 + ON "privilege"."unit_id" = "unit_id_v" 3.72 + AND "privilege"."member_id" = "member"."id" 3.73 + LEFT JOIN "interest" 3.74 + ON "membership"."member_id" = "interest"."member_id" 3.75 + AND "interest"."issue_id" = "issue_id_p" 3.76 + LEFT JOIN "direct_voter" 3.77 + ON "membership"."member_id" = "direct_voter"."member_id" 3.78 + AND "direct_voter"."issue_id" = "issue_id_p" 3.79 + LEFT JOIN "delegating_voter" 3.80 + ON "membership"."member_id" = "delegating_voter"."member_id" 3.81 + AND "delegating_voter"."issue_id" = "issue_id_p" 3.82 + WHERE "membership"."area_id" = "area_id_v" 3.83 + AND "membership"."autoreject" = TRUE 3.84 + AND "member"."active" 3.85 + AND "privilege"."voting_right" 3.86 + AND "interest"."autoreject" ISNULL 3.87 + AND "direct_voter"."member_id" ISNULL 3.88 + AND "delegating_voter"."member_id" ISNULL 3.89 + LOOP 3.90 + INSERT INTO "direct_voter" 3.91 + ("member_id", "issue_id", "weight", "autoreject") VALUES 3.92 + ("member_id_v", "issue_id_p", 1, TRUE); 3.93 + INSERT INTO "vote" ( 3.94 + "member_id", 3.95 + "issue_id", 3.96 + "initiative_id", 3.97 + "grade" 3.98 + ) SELECT 3.99 + "member_id_v" AS "member_id", 3.100 + "issue_id_p" AS "issue_id", 3.101 + "id" AS "initiative_id", 3.102 + -1 AS "grade" 3.103 + FROM "initiative" WHERE "issue_id" = "issue_id_p"; 3.104 + END LOOP; 3.105 + PERFORM "add_vote_delegations"("issue_id_p"); 3.106 + UPDATE "issue" SET 3.107 + "state" = 'calculation', 3.108 + "closed" = now(), 3.109 + "voter_count" = ( 3.110 + SELECT coalesce(sum("weight"), 0) 3.111 + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 3.112 + ) 3.113 + WHERE "id" = "issue_id_p"; 3.114 + UPDATE "initiative" SET 3.115 + "positive_votes" = "vote_counts"."positive_votes", 3.116 + "negative_votes" = "vote_counts"."negative_votes", 3.117 + "agreed" = CASE WHEN "majority_strict" THEN 3.118 + "vote_counts"."positive_votes" * "majority_den" > 3.119 + "majority_num" * 3.120 + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 3.121 + ELSE 3.122 + "vote_counts"."positive_votes" * "majority_den" >= 3.123 + "majority_num" * 3.124 + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 3.125 + END 3.126 + FROM 3.127 + ( SELECT 3.128 + "initiative"."id" AS "initiative_id", 3.129 + coalesce( 3.130 + sum( 3.131 + CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END 3.132 + ), 3.133 + 0 3.134 + ) AS "positive_votes", 3.135 + coalesce( 3.136 + sum( 3.137 + CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END 3.138 + ), 3.139 + 0 3.140 + ) AS "negative_votes" 3.141 + FROM "initiative" 3.142 + JOIN "issue" ON "initiative"."issue_id" = "issue"."id" 3.143 + JOIN "policy" ON "issue"."policy_id" = "policy"."id" 3.144 + LEFT JOIN "direct_voter" 3.145 + ON "direct_voter"."issue_id" = "initiative"."issue_id" 3.146 + LEFT JOIN "vote" 3.147 + ON "vote"."initiative_id" = "initiative"."id" 3.148 + AND "vote"."member_id" = "direct_voter"."member_id" 3.149 + WHERE "initiative"."issue_id" = "issue_id_p" 3.150 + AND "initiative"."admitted" -- NOTE: NULL case is handled too 3.151 + GROUP BY "initiative"."id" 3.152 + ) AS "vote_counts", 3.153 + "issue", 3.154 + "policy" 3.155 + WHERE "vote_counts"."initiative_id" = "initiative"."id" 3.156 + AND "issue"."id" = "initiative"."issue_id" 3.157 + AND "policy"."id" = "issue"."policy_id"; 3.158 + -- NOTE: "closed" column of issue must be set at this point 3.159 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 3.160 + INSERT INTO "battle" ( 3.161 + "issue_id", 3.162 + "winning_initiative_id", "losing_initiative_id", 3.163 + "count" 3.164 + ) SELECT 3.165 + "issue_id", 3.166 + "winning_initiative_id", "losing_initiative_id", 3.167 + "count" 3.168 + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 3.169 + END; 3.170 + $$; 3.171 + 3.172 +COMMIT;
4.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 4.2 +++ b/update/core-update.v1.4.0_rc3-v1.4.0_rc4.sql Sun Jun 05 01:12:37 2011 +0200 4.3 @@ -0,0 +1,218 @@ 4.4 +BEGIN; 4.5 + 4.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 4.7 + SELECT * FROM (VALUES ('1.4.0_rc4', 1, 4, -1)) 4.8 + AS "subquery"("string", "major", "minor", "revision"); 4.9 + 4.10 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 4.11 + RETURNS VOID 4.12 + LANGUAGE 'plpgsql' VOLATILE AS $$ 4.13 + DECLARE 4.14 + "area_id_v" "area"."id"%TYPE; 4.15 + "unit_id_v" "unit"."id"%TYPE; 4.16 + "member_id_v" "member"."id"%TYPE; 4.17 + BEGIN 4.18 + PERFORM "lock_issue"("issue_id_p"); 4.19 + SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 4.20 + SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 4.21 + DELETE FROM "delegating_voter" 4.22 + WHERE "issue_id" = "issue_id_p"; 4.23 + DELETE FROM "direct_voter" 4.24 + WHERE "issue_id" = "issue_id_p" 4.25 + AND "autoreject" = TRUE; 4.26 + DELETE FROM "direct_voter" 4.27 + USING ( 4.28 + SELECT 4.29 + "direct_voter"."member_id" 4.30 + FROM "direct_voter" 4.31 + JOIN "member" ON "direct_voter"."member_id" = "member"."id" 4.32 + LEFT JOIN "privilege" 4.33 + ON "privilege"."unit_id" = "unit_id_v" 4.34 + AND "privilege"."member_id" = "direct_voter"."member_id" 4.35 + WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 4.36 + "member"."active" = FALSE OR 4.37 + "privilege"."voting_right" ISNULL OR 4.38 + "privilege"."voting_right" = FALSE 4.39 + ) 4.40 + ) AS "subquery" 4.41 + WHERE "direct_voter"."issue_id" = "issue_id_p" 4.42 + AND "direct_voter"."member_id" = "subquery"."member_id"; 4.43 + UPDATE "direct_voter" SET "weight" = 1 4.44 + WHERE "issue_id" = "issue_id_p"; 4.45 + PERFORM "add_vote_delegations"("issue_id_p"); 4.46 + FOR "member_id_v" IN 4.47 + SELECT "interest"."member_id" 4.48 + FROM "interest" 4.49 + JOIN "member" 4.50 + ON "interest"."member_id" = "member"."id" 4.51 + JOIN "privilege" 4.52 + ON "privilege"."unit_id" = "unit_id_v" 4.53 + AND "privilege"."member_id" = "member"."id" 4.54 + LEFT JOIN "direct_voter" 4.55 + ON "interest"."member_id" = "direct_voter"."member_id" 4.56 + AND "interest"."issue_id" = "direct_voter"."issue_id" 4.57 + LEFT JOIN "delegating_voter" 4.58 + ON "interest"."member_id" = "delegating_voter"."member_id" 4.59 + AND "interest"."issue_id" = "delegating_voter"."issue_id" 4.60 + WHERE "interest"."issue_id" = "issue_id_p" 4.61 + AND "interest"."autoreject" = TRUE 4.62 + AND "member"."active" 4.63 + AND "privilege"."voting_right" 4.64 + AND "direct_voter"."member_id" ISNULL 4.65 + AND "delegating_voter"."member_id" ISNULL 4.66 + UNION SELECT "membership"."member_id" 4.67 + FROM "membership" 4.68 + JOIN "member" 4.69 + ON "membership"."member_id" = "member"."id" 4.70 + JOIN "privilege" 4.71 + ON "privilege"."unit_id" = "unit_id_v" 4.72 + AND "privilege"."member_id" = "member"."id" 4.73 + LEFT JOIN "interest" 4.74 + ON "membership"."member_id" = "interest"."member_id" 4.75 + AND "interest"."issue_id" = "issue_id_p" 4.76 + LEFT JOIN "direct_voter" 4.77 + ON "membership"."member_id" = "direct_voter"."member_id" 4.78 + AND "direct_voter"."issue_id" = "issue_id_p" 4.79 + LEFT JOIN "delegating_voter" 4.80 + ON "membership"."member_id" = "delegating_voter"."member_id" 4.81 + AND "delegating_voter"."issue_id" = "issue_id_p" 4.82 + WHERE "membership"."area_id" = "area_id_v" 4.83 + AND "membership"."autoreject" = TRUE 4.84 + AND "member"."active" 4.85 + AND "privilege"."voting_right" 4.86 + AND "interest"."autoreject" ISNULL 4.87 + AND "direct_voter"."member_id" ISNULL 4.88 + AND "delegating_voter"."member_id" ISNULL 4.89 + LOOP 4.90 + INSERT INTO "direct_voter" 4.91 + ("member_id", "issue_id", "weight", "autoreject") VALUES 4.92 + ("member_id_v", "issue_id_p", 1, TRUE); 4.93 + INSERT INTO "vote" ( 4.94 + "member_id", 4.95 + "issue_id", 4.96 + "initiative_id", 4.97 + "grade" 4.98 + ) SELECT 4.99 + "member_id_v" AS "member_id", 4.100 + "issue_id_p" AS "issue_id", 4.101 + "id" AS "initiative_id", 4.102 + -1 AS "grade" 4.103 + FROM "initiative" 4.104 + WHERE "issue_id" = "issue_id_p" AND "admitted"; 4.105 + END LOOP; 4.106 + PERFORM "add_vote_delegations"("issue_id_p"); 4.107 + UPDATE "issue" SET 4.108 + "state" = 'calculation', 4.109 + "closed" = now(), 4.110 + "voter_count" = ( 4.111 + SELECT coalesce(sum("weight"), 0) 4.112 + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 4.113 + ) 4.114 + WHERE "id" = "issue_id_p"; 4.115 + UPDATE "initiative" SET 4.116 + "positive_votes" = "vote_counts"."positive_votes", 4.117 + "negative_votes" = "vote_counts"."negative_votes", 4.118 + "agreed" = CASE WHEN "majority_strict" THEN 4.119 + "vote_counts"."positive_votes" * "majority_den" > 4.120 + "majority_num" * 4.121 + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 4.122 + ELSE 4.123 + "vote_counts"."positive_votes" * "majority_den" >= 4.124 + "majority_num" * 4.125 + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 4.126 + END 4.127 + FROM 4.128 + ( SELECT 4.129 + "initiative"."id" AS "initiative_id", 4.130 + coalesce( 4.131 + sum( 4.132 + CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END 4.133 + ), 4.134 + 0 4.135 + ) AS "positive_votes", 4.136 + coalesce( 4.137 + sum( 4.138 + CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END 4.139 + ), 4.140 + 0 4.141 + ) AS "negative_votes" 4.142 + FROM "initiative" 4.143 + JOIN "issue" ON "initiative"."issue_id" = "issue"."id" 4.144 + JOIN "policy" ON "issue"."policy_id" = "policy"."id" 4.145 + LEFT JOIN "direct_voter" 4.146 + ON "direct_voter"."issue_id" = "initiative"."issue_id" 4.147 + LEFT JOIN "vote" 4.148 + ON "vote"."initiative_id" = "initiative"."id" 4.149 + AND "vote"."member_id" = "direct_voter"."member_id" 4.150 + WHERE "initiative"."issue_id" = "issue_id_p" 4.151 + AND "initiative"."admitted" -- NOTE: NULL case is handled too 4.152 + GROUP BY "initiative"."id" 4.153 + ) AS "vote_counts", 4.154 + "issue", 4.155 + "policy" 4.156 + WHERE "vote_counts"."initiative_id" = "initiative"."id" 4.157 + AND "issue"."id" = "initiative"."issue_id" 4.158 + AND "policy"."id" = "issue"."policy_id"; 4.159 + -- NOTE: "closed" column of issue must be set at this point 4.160 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 4.161 + INSERT INTO "battle" ( 4.162 + "issue_id", 4.163 + "winning_initiative_id", "losing_initiative_id", 4.164 + "count" 4.165 + ) SELECT 4.166 + "issue_id", 4.167 + "winning_initiative_id", "losing_initiative_id", 4.168 + "count" 4.169 + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 4.170 + END; 4.171 + $$; 4.172 + 4.173 +CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE) 4.174 + RETURNS VOID 4.175 + LANGUAGE 'plpgsql' VOLATILE AS $$ 4.176 + DECLARE 4.177 + "issue_row" "issue"%ROWTYPE; 4.178 + BEGIN 4.179 + SELECT * INTO "issue_row" 4.180 + FROM "issue" WHERE "id" = "issue_id_p" 4.181 + FOR UPDATE; 4.182 + IF "issue_row"."cleaned" ISNULL THEN 4.183 + UPDATE "issue" SET 4.184 + "state" = 'voting', 4.185 + "closed" = NULL, 4.186 + "ranks_available" = FALSE 4.187 + WHERE "id" = "issue_id_p"; 4.188 + DELETE FROM "issue_comment" 4.189 + WHERE "issue_id" = "issue_id_p"; 4.190 + DELETE FROM "voting_comment" 4.191 + WHERE "issue_id" = "issue_id_p"; 4.192 + DELETE FROM "delegating_voter" 4.193 + WHERE "issue_id" = "issue_id_p"; 4.194 + DELETE FROM "direct_voter" 4.195 + WHERE "issue_id" = "issue_id_p"; 4.196 + DELETE FROM "delegating_interest_snapshot" 4.197 + WHERE "issue_id" = "issue_id_p"; 4.198 + DELETE FROM "direct_interest_snapshot" 4.199 + WHERE "issue_id" = "issue_id_p"; 4.200 + DELETE FROM "delegating_population_snapshot" 4.201 + WHERE "issue_id" = "issue_id_p"; 4.202 + DELETE FROM "direct_population_snapshot" 4.203 + WHERE "issue_id" = "issue_id_p"; 4.204 + DELETE FROM "non_voter" 4.205 + WHERE "issue_id" = "issue_id_p"; 4.206 + DELETE FROM "delegation" 4.207 + WHERE "issue_id" = "issue_id_p"; 4.208 + DELETE FROM "supporter" 4.209 + WHERE "issue_id" = "issue_id_p"; 4.210 + UPDATE "issue" SET 4.211 + "state" = "issue_row"."state", 4.212 + "closed" = "issue_row"."closed", 4.213 + "ranks_available" = "issue_row"."ranks_available", 4.214 + "cleaned" = now() 4.215 + WHERE "id" = "issue_id_p"; 4.216 + END IF; 4.217 + RETURN; 4.218 + END; 4.219 + $$; 4.220 + 4.221 +COMMIT;