liquid_feedback_core
changeset 164:1be788313b84 v1.4.0_rc4
Added update scripts from v1.4.0_rc2 to _rc3 and from _rc3 to _rc4
author | jbe |
---|---|
date | Sun Jun 05 01:08:14 2011 +0200 (2011-06-05) |
parents | 2fd12a390f56 |
children | 662d714de82e d53e276f32ba |
files | update/core-update.v1.4.0_rc2-v1.4.0_rc3.sql update/core-update.v1.4.0_rc3-v1.4.0_rc4.sql |
line diff
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/update/core-update.v1.4.0_rc2-v1.4.0_rc3.sql Sun Jun 05 01:08:14 2011 +0200 1.3 @@ -0,0 +1,169 @@ 1.4 +BEGIN; 1.5 + 1.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.7 + SELECT * FROM (VALUES ('1.4.0_rc3', 1, 4, -1)) 1.8 + AS "subquery"("string", "major", "minor", "revision"); 1.9 + 1.10 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 1.11 + RETURNS VOID 1.12 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.13 + DECLARE 1.14 + "area_id_v" "area"."id"%TYPE; 1.15 + "unit_id_v" "unit"."id"%TYPE; 1.16 + "member_id_v" "member"."id"%TYPE; 1.17 + BEGIN 1.18 + PERFORM "lock_issue"("issue_id_p"); 1.19 + SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 1.20 + SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 1.21 + DELETE FROM "delegating_voter" 1.22 + WHERE "issue_id" = "issue_id_p"; 1.23 + DELETE FROM "direct_voter" 1.24 + WHERE "issue_id" = "issue_id_p" 1.25 + AND "autoreject" = TRUE; 1.26 + DELETE FROM "direct_voter" 1.27 + USING ( 1.28 + SELECT 1.29 + "direct_voter"."member_id" 1.30 + FROM "direct_voter" 1.31 + JOIN "member" ON "direct_voter"."member_id" = "member"."id" 1.32 + LEFT JOIN "privilege" 1.33 + ON "privilege"."unit_id" = "unit_id_v" 1.34 + AND "privilege"."member_id" = "direct_voter"."member_id" 1.35 + WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 1.36 + "member"."active" = FALSE OR 1.37 + "privilege"."voting_right" ISNULL OR 1.38 + "privilege"."voting_right" = FALSE 1.39 + ) 1.40 + ) AS "subquery" 1.41 + WHERE "direct_voter"."issue_id" = "issue_id_p" 1.42 + AND "direct_voter"."member_id" = "subquery"."member_id"; 1.43 + UPDATE "direct_voter" SET "weight" = 1 1.44 + WHERE "issue_id" = "issue_id_p"; 1.45 + PERFORM "add_vote_delegations"("issue_id_p"); 1.46 + FOR "member_id_v" IN 1.47 + SELECT "interest"."member_id" 1.48 + FROM "interest" 1.49 + JOIN "member" 1.50 + ON "interest"."member_id" = "member"."id" 1.51 + JOIN "privilege" 1.52 + ON "privilege"."unit_id" = "unit_id_v" 1.53 + AND "privilege"."member_id" = "member"."id" 1.54 + LEFT JOIN "direct_voter" 1.55 + ON "interest"."member_id" = "direct_voter"."member_id" 1.56 + AND "interest"."issue_id" = "direct_voter"."issue_id" 1.57 + LEFT JOIN "delegating_voter" 1.58 + ON "interest"."member_id" = "delegating_voter"."member_id" 1.59 + AND "interest"."issue_id" = "delegating_voter"."issue_id" 1.60 + WHERE "interest"."issue_id" = "issue_id_p" 1.61 + AND "interest"."autoreject" = TRUE 1.62 + AND "member"."active" 1.63 + AND "privilege"."voting_right" 1.64 + AND "direct_voter"."member_id" ISNULL 1.65 + AND "delegating_voter"."member_id" ISNULL 1.66 + UNION SELECT "membership"."member_id" 1.67 + FROM "membership" 1.68 + JOIN "member" 1.69 + ON "membership"."member_id" = "member"."id" 1.70 + JOIN "privilege" 1.71 + ON "privilege"."unit_id" = "unit_id_v" 1.72 + AND "privilege"."member_id" = "member"."id" 1.73 + LEFT JOIN "interest" 1.74 + ON "membership"."member_id" = "interest"."member_id" 1.75 + AND "interest"."issue_id" = "issue_id_p" 1.76 + LEFT JOIN "direct_voter" 1.77 + ON "membership"."member_id" = "direct_voter"."member_id" 1.78 + AND "direct_voter"."issue_id" = "issue_id_p" 1.79 + LEFT JOIN "delegating_voter" 1.80 + ON "membership"."member_id" = "delegating_voter"."member_id" 1.81 + AND "delegating_voter"."issue_id" = "issue_id_p" 1.82 + WHERE "membership"."area_id" = "area_id_v" 1.83 + AND "membership"."autoreject" = TRUE 1.84 + AND "member"."active" 1.85 + AND "privilege"."voting_right" 1.86 + AND "interest"."autoreject" ISNULL 1.87 + AND "direct_voter"."member_id" ISNULL 1.88 + AND "delegating_voter"."member_id" ISNULL 1.89 + LOOP 1.90 + INSERT INTO "direct_voter" 1.91 + ("member_id", "issue_id", "weight", "autoreject") VALUES 1.92 + ("member_id_v", "issue_id_p", 1, TRUE); 1.93 + INSERT INTO "vote" ( 1.94 + "member_id", 1.95 + "issue_id", 1.96 + "initiative_id", 1.97 + "grade" 1.98 + ) SELECT 1.99 + "member_id_v" AS "member_id", 1.100 + "issue_id_p" AS "issue_id", 1.101 + "id" AS "initiative_id", 1.102 + -1 AS "grade" 1.103 + FROM "initiative" WHERE "issue_id" = "issue_id_p"; 1.104 + END LOOP; 1.105 + PERFORM "add_vote_delegations"("issue_id_p"); 1.106 + UPDATE "issue" SET 1.107 + "state" = 'calculation', 1.108 + "closed" = now(), 1.109 + "voter_count" = ( 1.110 + SELECT coalesce(sum("weight"), 0) 1.111 + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 1.112 + ) 1.113 + WHERE "id" = "issue_id_p"; 1.114 + UPDATE "initiative" SET 1.115 + "positive_votes" = "vote_counts"."positive_votes", 1.116 + "negative_votes" = "vote_counts"."negative_votes", 1.117 + "agreed" = CASE WHEN "majority_strict" THEN 1.118 + "vote_counts"."positive_votes" * "majority_den" > 1.119 + "majority_num" * 1.120 + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 1.121 + ELSE 1.122 + "vote_counts"."positive_votes" * "majority_den" >= 1.123 + "majority_num" * 1.124 + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 1.125 + END 1.126 + FROM 1.127 + ( SELECT 1.128 + "initiative"."id" AS "initiative_id", 1.129 + coalesce( 1.130 + sum( 1.131 + CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END 1.132 + ), 1.133 + 0 1.134 + ) AS "positive_votes", 1.135 + coalesce( 1.136 + sum( 1.137 + CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END 1.138 + ), 1.139 + 0 1.140 + ) AS "negative_votes" 1.141 + FROM "initiative" 1.142 + JOIN "issue" ON "initiative"."issue_id" = "issue"."id" 1.143 + JOIN "policy" ON "issue"."policy_id" = "policy"."id" 1.144 + LEFT JOIN "direct_voter" 1.145 + ON "direct_voter"."issue_id" = "initiative"."issue_id" 1.146 + LEFT JOIN "vote" 1.147 + ON "vote"."initiative_id" = "initiative"."id" 1.148 + AND "vote"."member_id" = "direct_voter"."member_id" 1.149 + WHERE "initiative"."issue_id" = "issue_id_p" 1.150 + AND "initiative"."admitted" -- NOTE: NULL case is handled too 1.151 + GROUP BY "initiative"."id" 1.152 + ) AS "vote_counts", 1.153 + "issue", 1.154 + "policy" 1.155 + WHERE "vote_counts"."initiative_id" = "initiative"."id" 1.156 + AND "issue"."id" = "initiative"."issue_id" 1.157 + AND "policy"."id" = "issue"."policy_id"; 1.158 + -- NOTE: "closed" column of issue must be set at this point 1.159 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 1.160 + INSERT INTO "battle" ( 1.161 + "issue_id", 1.162 + "winning_initiative_id", "losing_initiative_id", 1.163 + "count" 1.164 + ) SELECT 1.165 + "issue_id", 1.166 + "winning_initiative_id", "losing_initiative_id", 1.167 + "count" 1.168 + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 1.169 + END; 1.170 + $$; 1.171 + 1.172 +COMMIT;
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 2.2 +++ b/update/core-update.v1.4.0_rc3-v1.4.0_rc4.sql Sun Jun 05 01:08:14 2011 +0200 2.3 @@ -0,0 +1,218 @@ 2.4 +BEGIN; 2.5 + 2.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 2.7 + SELECT * FROM (VALUES ('1.4.0_rc4', 1, 4, -1)) 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 + "area_id_v" "area"."id"%TYPE; 2.15 + "unit_id_v" "unit"."id"%TYPE; 2.16 + "member_id_v" "member"."id"%TYPE; 2.17 + BEGIN 2.18 + PERFORM "lock_issue"("issue_id_p"); 2.19 + SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 2.20 + SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 2.21 + DELETE FROM "delegating_voter" 2.22 + WHERE "issue_id" = "issue_id_p"; 2.23 + DELETE FROM "direct_voter" 2.24 + WHERE "issue_id" = "issue_id_p" 2.25 + AND "autoreject" = TRUE; 2.26 + DELETE FROM "direct_voter" 2.27 + USING ( 2.28 + SELECT 2.29 + "direct_voter"."member_id" 2.30 + FROM "direct_voter" 2.31 + JOIN "member" ON "direct_voter"."member_id" = "member"."id" 2.32 + LEFT JOIN "privilege" 2.33 + ON "privilege"."unit_id" = "unit_id_v" 2.34 + AND "privilege"."member_id" = "direct_voter"."member_id" 2.35 + WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 2.36 + "member"."active" = FALSE OR 2.37 + "privilege"."voting_right" ISNULL OR 2.38 + "privilege"."voting_right" = FALSE 2.39 + ) 2.40 + ) AS "subquery" 2.41 + WHERE "direct_voter"."issue_id" = "issue_id_p" 2.42 + AND "direct_voter"."member_id" = "subquery"."member_id"; 2.43 + UPDATE "direct_voter" SET "weight" = 1 2.44 + WHERE "issue_id" = "issue_id_p"; 2.45 + PERFORM "add_vote_delegations"("issue_id_p"); 2.46 + FOR "member_id_v" IN 2.47 + SELECT "interest"."member_id" 2.48 + FROM "interest" 2.49 + JOIN "member" 2.50 + ON "interest"."member_id" = "member"."id" 2.51 + JOIN "privilege" 2.52 + ON "privilege"."unit_id" = "unit_id_v" 2.53 + AND "privilege"."member_id" = "member"."id" 2.54 + LEFT JOIN "direct_voter" 2.55 + ON "interest"."member_id" = "direct_voter"."member_id" 2.56 + AND "interest"."issue_id" = "direct_voter"."issue_id" 2.57 + LEFT JOIN "delegating_voter" 2.58 + ON "interest"."member_id" = "delegating_voter"."member_id" 2.59 + AND "interest"."issue_id" = "delegating_voter"."issue_id" 2.60 + WHERE "interest"."issue_id" = "issue_id_p" 2.61 + AND "interest"."autoreject" = TRUE 2.62 + AND "member"."active" 2.63 + AND "privilege"."voting_right" 2.64 + AND "direct_voter"."member_id" ISNULL 2.65 + AND "delegating_voter"."member_id" ISNULL 2.66 + UNION SELECT "membership"."member_id" 2.67 + FROM "membership" 2.68 + JOIN "member" 2.69 + ON "membership"."member_id" = "member"."id" 2.70 + JOIN "privilege" 2.71 + ON "privilege"."unit_id" = "unit_id_v" 2.72 + AND "privilege"."member_id" = "member"."id" 2.73 + LEFT JOIN "interest" 2.74 + ON "membership"."member_id" = "interest"."member_id" 2.75 + AND "interest"."issue_id" = "issue_id_p" 2.76 + LEFT JOIN "direct_voter" 2.77 + ON "membership"."member_id" = "direct_voter"."member_id" 2.78 + AND "direct_voter"."issue_id" = "issue_id_p" 2.79 + LEFT JOIN "delegating_voter" 2.80 + ON "membership"."member_id" = "delegating_voter"."member_id" 2.81 + AND "delegating_voter"."issue_id" = "issue_id_p" 2.82 + WHERE "membership"."area_id" = "area_id_v" 2.83 + AND "membership"."autoreject" = TRUE 2.84 + AND "member"."active" 2.85 + AND "privilege"."voting_right" 2.86 + AND "interest"."autoreject" ISNULL 2.87 + AND "direct_voter"."member_id" ISNULL 2.88 + AND "delegating_voter"."member_id" ISNULL 2.89 + LOOP 2.90 + INSERT INTO "direct_voter" 2.91 + ("member_id", "issue_id", "weight", "autoreject") VALUES 2.92 + ("member_id_v", "issue_id_p", 1, TRUE); 2.93 + INSERT INTO "vote" ( 2.94 + "member_id", 2.95 + "issue_id", 2.96 + "initiative_id", 2.97 + "grade" 2.98 + ) SELECT 2.99 + "member_id_v" AS "member_id", 2.100 + "issue_id_p" AS "issue_id", 2.101 + "id" AS "initiative_id", 2.102 + -1 AS "grade" 2.103 + FROM "initiative" 2.104 + WHERE "issue_id" = "issue_id_p" AND "admitted"; 2.105 + END LOOP; 2.106 + PERFORM "add_vote_delegations"("issue_id_p"); 2.107 + UPDATE "issue" SET 2.108 + "state" = 'calculation', 2.109 + "closed" = now(), 2.110 + "voter_count" = ( 2.111 + SELECT coalesce(sum("weight"), 0) 2.112 + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 2.113 + ) 2.114 + WHERE "id" = "issue_id_p"; 2.115 + UPDATE "initiative" SET 2.116 + "positive_votes" = "vote_counts"."positive_votes", 2.117 + "negative_votes" = "vote_counts"."negative_votes", 2.118 + "agreed" = CASE WHEN "majority_strict" THEN 2.119 + "vote_counts"."positive_votes" * "majority_den" > 2.120 + "majority_num" * 2.121 + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 2.122 + ELSE 2.123 + "vote_counts"."positive_votes" * "majority_den" >= 2.124 + "majority_num" * 2.125 + ("vote_counts"."positive_votes"+"vote_counts"."negative_votes") 2.126 + END 2.127 + FROM 2.128 + ( SELECT 2.129 + "initiative"."id" AS "initiative_id", 2.130 + coalesce( 2.131 + sum( 2.132 + CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END 2.133 + ), 2.134 + 0 2.135 + ) AS "positive_votes", 2.136 + coalesce( 2.137 + sum( 2.138 + CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END 2.139 + ), 2.140 + 0 2.141 + ) AS "negative_votes" 2.142 + FROM "initiative" 2.143 + JOIN "issue" ON "initiative"."issue_id" = "issue"."id" 2.144 + JOIN "policy" ON "issue"."policy_id" = "policy"."id" 2.145 + LEFT JOIN "direct_voter" 2.146 + ON "direct_voter"."issue_id" = "initiative"."issue_id" 2.147 + LEFT JOIN "vote" 2.148 + ON "vote"."initiative_id" = "initiative"."id" 2.149 + AND "vote"."member_id" = "direct_voter"."member_id" 2.150 + WHERE "initiative"."issue_id" = "issue_id_p" 2.151 + AND "initiative"."admitted" -- NOTE: NULL case is handled too 2.152 + GROUP BY "initiative"."id" 2.153 + ) AS "vote_counts", 2.154 + "issue", 2.155 + "policy" 2.156 + WHERE "vote_counts"."initiative_id" = "initiative"."id" 2.157 + AND "issue"."id" = "initiative"."issue_id" 2.158 + AND "policy"."id" = "issue"."policy_id"; 2.159 + -- NOTE: "closed" column of issue must be set at this point 2.160 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 2.161 + INSERT INTO "battle" ( 2.162 + "issue_id", 2.163 + "winning_initiative_id", "losing_initiative_id", 2.164 + "count" 2.165 + ) SELECT 2.166 + "issue_id", 2.167 + "winning_initiative_id", "losing_initiative_id", 2.168 + "count" 2.169 + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 2.170 + END; 2.171 + $$; 2.172 + 2.173 +CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE) 2.174 + RETURNS VOID 2.175 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.176 + DECLARE 2.177 + "issue_row" "issue"%ROWTYPE; 2.178 + BEGIN 2.179 + SELECT * INTO "issue_row" 2.180 + FROM "issue" WHERE "id" = "issue_id_p" 2.181 + FOR UPDATE; 2.182 + IF "issue_row"."cleaned" ISNULL THEN 2.183 + UPDATE "issue" SET 2.184 + "state" = 'voting', 2.185 + "closed" = NULL, 2.186 + "ranks_available" = FALSE 2.187 + WHERE "id" = "issue_id_p"; 2.188 + DELETE FROM "issue_comment" 2.189 + WHERE "issue_id" = "issue_id_p"; 2.190 + DELETE FROM "voting_comment" 2.191 + WHERE "issue_id" = "issue_id_p"; 2.192 + DELETE FROM "delegating_voter" 2.193 + WHERE "issue_id" = "issue_id_p"; 2.194 + DELETE FROM "direct_voter" 2.195 + WHERE "issue_id" = "issue_id_p"; 2.196 + DELETE FROM "delegating_interest_snapshot" 2.197 + WHERE "issue_id" = "issue_id_p"; 2.198 + DELETE FROM "direct_interest_snapshot" 2.199 + WHERE "issue_id" = "issue_id_p"; 2.200 + DELETE FROM "delegating_population_snapshot" 2.201 + WHERE "issue_id" = "issue_id_p"; 2.202 + DELETE FROM "direct_population_snapshot" 2.203 + WHERE "issue_id" = "issue_id_p"; 2.204 + DELETE FROM "non_voter" 2.205 + WHERE "issue_id" = "issue_id_p"; 2.206 + DELETE FROM "delegation" 2.207 + WHERE "issue_id" = "issue_id_p"; 2.208 + DELETE FROM "supporter" 2.209 + WHERE "issue_id" = "issue_id_p"; 2.210 + UPDATE "issue" SET 2.211 + "state" = "issue_row"."state", 2.212 + "closed" = "issue_row"."closed", 2.213 + "ranks_available" = "issue_row"."ranks_available", 2.214 + "cleaned" = now() 2.215 + WHERE "id" = "issue_id_p"; 2.216 + END IF; 2.217 + RETURN; 2.218 + END; 2.219 + $$; 2.220 + 2.221 +COMMIT;