liquid_feedback_core
diff update/core-update.v3.0.2-v3.0.3.sql @ 436:34cc98defa8b
2 bugfixes: Error in core-update.v2.2.6-v3.0.1.sql fixed; Always set "initiative"."first_preference_votes" (also if no votes are found)
author | jbe |
---|---|
date | Wed Jul 16 16:28:35 2014 +0200 (2014-07-16) |
parents | |
children | eb12a069063c |
line diff
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/update/core-update.v3.0.2-v3.0.3.sql Wed Jul 16 16:28:35 2014 +0200 1.3 @@ -0,0 +1,275 @@ 1.4 +-- NOTICE: This update script disables the "no_reserve_beat_path" setting for 1.5 +-- all policies. If this is not intended, please edit this script 1.6 +-- before applying it to your database. 1.7 + 1.8 +BEGIN; 1.9 + 1.10 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.11 + SELECT * FROM (VALUES ('3.0.3', 3, 0, 3)) 1.12 + AS "subquery"("string", "major", "minor", "revision"); 1.13 + 1.14 +CREATE FUNCTION "update3_0_3_add_columns_if_missing"() 1.15 + RETURNS VOID 1.16 + LANGUAGE 'plpgsql' AS $$ 1.17 + BEGIN 1.18 + BEGIN 1.19 + ALTER TABLE "initiative" ADD COLUMN "first_preference_votes" INT4; 1.20 + EXCEPTION 1.21 + WHEN duplicate_column THEN 1.22 + RAISE NOTICE 'column "first_preference_votes" of relation "initiative" already exists, skipping'; 1.23 + END; 1.24 + BEGIN 1.25 + ALTER TABLE "vote" ADD COLUMN "first_preference" BOOLEAN; 1.26 + EXCEPTION 1.27 + WHEN duplicate_column THEN 1.28 + RAISE NOTICE 'column "first_preference" of relation "vote" already exists, skipping'; 1.29 + END; 1.30 + RETURN; 1.31 + END; 1.32 + $$; 1.33 + 1.34 +SELECT "update3_0_3_add_columns_if_missing"(); 1.35 + 1.36 +DROP FUNCTION "update3_0_3_add_columns_if_missing"(); 1.37 + 1.38 +ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"; 1.39 +ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK ( 1.40 + ( "admitted" NOTNULL AND "admitted" = TRUE ) OR 1.41 + ( "first_preference_votes" ISNULL AND 1.42 + "positive_votes" ISNULL AND "negative_votes" ISNULL AND 1.43 + "direct_majority" ISNULL AND "indirect_majority" ISNULL AND 1.44 + "schulze_rank" ISNULL AND 1.45 + "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND 1.46 + "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND 1.47 + "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ); 1.48 + 1.49 +COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice'; 1.50 +COMMENT ON COLUMN "initiative"."positive_votes" IS 'Number of direct and delegating voters who ranked this initiative better than the status quo'; 1.51 +COMMENT ON COLUMN "initiative"."negative_votes" IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo'; 1.52 + 1.53 +-- UPDATE TABLE "vote" SET "grade" = 0 WHERE "grade" ISNULL; -- should not be necessary 1.54 +ALTER TABLE "vote" ALTER COLUMN "grade" SET NOT NULL; 1.55 + 1.56 +ALTER TABLE "vote" DROP CONSTRAINT IF EXISTS "first_preference_flag_only_set_on_positive_grades"; 1.57 +ALTER TABLE "vote" ADD 1.58 + CONSTRAINT "first_preference_flag_only_set_on_positive_grades" 1.59 + CHECK ("grade" > 0 OR "first_preference" ISNULL); 1.60 + 1.61 +COMMENT ON COLUMN "vote"."first_preference" IS 'Value is automatically set after voting is finished. For positive grades, this value is set to true for the highest (i.e. best) grade.'; 1.62 + 1.63 +INSERT INTO "temporary_transaction_data" ("key", "value") 1.64 + VALUES ('override_protection_triggers', TRUE::TEXT); 1.65 + 1.66 +UPDATE "vote" SET "first_preference" = "subquery"."first_preference" 1.67 + FROM ( 1.68 + SELECT 1.69 + "vote"."initiative_id", 1.70 + "vote"."member_id", 1.71 + CASE WHEN "vote"."grade" > 0 THEN 1.72 + CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END 1.73 + ELSE NULL 1.74 + END AS "first_preference" 1.75 + FROM "vote" 1.76 + JOIN "initiative" -- NOTE: due to missing index on issue_id 1.77 + ON "vote"."issue_id" = "initiative"."issue_id" 1.78 + JOIN "vote" AS "agg" 1.79 + ON "initiative"."id" = "agg"."initiative_id" 1.80 + AND "vote"."member_id" = "agg"."member_id" 1.81 + GROUP BY "vote"."initiative_id", "vote"."member_id" 1.82 + ) AS "subquery" 1.83 + WHERE "vote"."initiative_id" = "subquery"."initiative_id" 1.84 + AND "vote"."member_id" = "subquery"."member_id"; 1.85 + 1.86 +DELETE FROM "temporary_transaction_data" 1.87 + WHERE "key" = 'override_protection_triggers'; 1.88 + 1.89 +UPDATE "initiative" SET "first_preference_votes" = NULL 1.90 + WHERE "first_preference_votes" = 0; 1.91 + 1.92 +UPDATE "initiative" 1.93 + SET "first_preference_votes" = "subquery"."sum" 1.94 + FROM ( 1.95 + SELECT "vote"."initiative_id", sum("direct_voter"."weight") 1.96 + FROM "vote" JOIN "direct_voter" 1.97 + ON "vote"."issue_id" = "direct_voter"."issue_id" 1.98 + AND "vote"."member_id" = "direct_voter"."member_id" 1.99 + WHERE "vote"."first_preference" 1.100 + GROUP BY "vote"."initiative_id" 1.101 + ) AS "subquery" 1.102 + WHERE "initiative"."admitted" 1.103 + AND "initiative"."id" = "subquery"."initiative_id" 1.104 + AND "initiative"."first_preference_votes" ISNULL; 1.105 + 1.106 +UPDATE "initiative" SET "first_preference_votes" = 0 1.107 + WHERE "positive_votes" NOTNULL 1.108 + AND "first_preference_votes" ISNULL; 1.109 + 1.110 +-- reconstruct battle data (originating from LiquidFeedback Core before v2.0.0) 1.111 +-- to avoid future data loss when executing "clean_issue" to delete voting data: 1.112 +INSERT INTO "battle" ( 1.113 + "issue_id", 1.114 + "winning_initiative_id", 1.115 + "losing_initiative_id", 1.116 + "count" 1.117 + ) SELECT 1.118 + "battle_view"."issue_id", 1.119 + "battle_view"."winning_initiative_id", 1.120 + "battle_view"."losing_initiative_id", 1.121 + "battle_view"."count" 1.122 + FROM ( 1.123 + SELECT 1.124 + "issue"."id" AS "issue_id", 1.125 + "winning_initiative"."id" AS "winning_initiative_id", 1.126 + "losing_initiative"."id" AS "losing_initiative_id", 1.127 + sum( 1.128 + CASE WHEN 1.129 + coalesce("better_vote"."grade", 0) > 1.130 + coalesce("worse_vote"."grade", 0) 1.131 + THEN "direct_voter"."weight" ELSE 0 END 1.132 + ) AS "count" 1.133 + FROM "issue" 1.134 + LEFT JOIN "direct_voter" 1.135 + ON "issue"."id" = "direct_voter"."issue_id" 1.136 + JOIN "battle_participant" AS "winning_initiative" 1.137 + ON "issue"."id" = "winning_initiative"."issue_id" 1.138 + JOIN "battle_participant" AS "losing_initiative" 1.139 + ON "issue"."id" = "losing_initiative"."issue_id" 1.140 + LEFT JOIN "vote" AS "better_vote" 1.141 + ON "direct_voter"."member_id" = "better_vote"."member_id" 1.142 + AND "winning_initiative"."id" = "better_vote"."initiative_id" 1.143 + LEFT JOIN "vote" AS "worse_vote" 1.144 + ON "direct_voter"."member_id" = "worse_vote"."member_id" 1.145 + AND "losing_initiative"."id" = "worse_vote"."initiative_id" 1.146 + WHERE "issue"."state" IN ('finished_with_winner', 'finished_without_winner') 1.147 + AND "winning_initiative"."id" != "losing_initiative"."id" 1.148 + -- NOTE: comparisons with status-quo are intentionally omitted to mark 1.149 + -- issues that were counted prior LiquidFeedback Core v2.0.0 1.150 + GROUP BY 1.151 + "issue"."id", 1.152 + "winning_initiative"."id", 1.153 + "losing_initiative"."id" 1.154 + ) AS "battle_view" 1.155 + LEFT JOIN "battle" 1.156 + ON "battle_view"."winning_initiative_id" = "battle"."winning_initiative_id" 1.157 + AND "battle_view"."losing_initiative_id" = "battle"."losing_initiative_id" 1.158 + WHERE "battle" ISNULL; 1.159 + 1.160 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 1.161 + RETURNS VOID 1.162 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.163 + DECLARE 1.164 + "area_id_v" "area"."id"%TYPE; 1.165 + "unit_id_v" "unit"."id"%TYPE; 1.166 + "member_id_v" "member"."id"%TYPE; 1.167 + BEGIN 1.168 + PERFORM "require_transaction_isolation"(); 1.169 + SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 1.170 + SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 1.171 + -- override protection triggers: 1.172 + INSERT INTO "temporary_transaction_data" ("key", "value") 1.173 + VALUES ('override_protection_triggers', TRUE::TEXT); 1.174 + -- delete timestamp of voting comment: 1.175 + UPDATE "direct_voter" SET "comment_changed" = NULL 1.176 + WHERE "issue_id" = "issue_id_p"; 1.177 + -- delete delegating votes (in cases of manual reset of issue state): 1.178 + DELETE FROM "delegating_voter" 1.179 + WHERE "issue_id" = "issue_id_p"; 1.180 + -- delete votes from non-privileged voters: 1.181 + DELETE FROM "direct_voter" 1.182 + USING ( 1.183 + SELECT 1.184 + "direct_voter"."member_id" 1.185 + FROM "direct_voter" 1.186 + JOIN "member" ON "direct_voter"."member_id" = "member"."id" 1.187 + LEFT JOIN "privilege" 1.188 + ON "privilege"."unit_id" = "unit_id_v" 1.189 + AND "privilege"."member_id" = "direct_voter"."member_id" 1.190 + WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 1.191 + "member"."active" = FALSE OR 1.192 + "privilege"."voting_right" ISNULL OR 1.193 + "privilege"."voting_right" = FALSE 1.194 + ) 1.195 + ) AS "subquery" 1.196 + WHERE "direct_voter"."issue_id" = "issue_id_p" 1.197 + AND "direct_voter"."member_id" = "subquery"."member_id"; 1.198 + -- consider delegations: 1.199 + UPDATE "direct_voter" SET "weight" = 1 1.200 + WHERE "issue_id" = "issue_id_p"; 1.201 + PERFORM "add_vote_delegations"("issue_id_p"); 1.202 + -- mark first preferences: 1.203 + UPDATE "vote" SET "first_preference" = "subquery"."first_preference" 1.204 + FROM ( 1.205 + SELECT 1.206 + "vote"."initiative_id", 1.207 + "vote"."member_id", 1.208 + CASE WHEN "vote"."grade" > 0 THEN 1.209 + CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END 1.210 + ELSE NULL 1.211 + END AS "first_preference" 1.212 + FROM "vote" 1.213 + JOIN "initiative" -- NOTE: due to missing index on issue_id 1.214 + ON "vote"."issue_id" = "initiative"."issue_id" 1.215 + JOIN "vote" AS "agg" 1.216 + ON "initiative"."id" = "agg"."initiative_id" 1.217 + AND "vote"."member_id" = "agg"."member_id" 1.218 + GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade" 1.219 + ) AS "subquery" 1.220 + WHERE "vote"."issue_id" = "issue_id_p" 1.221 + AND "vote"."initiative_id" = "subquery"."initiative_id" 1.222 + AND "vote"."member_id" = "subquery"."member_id"; 1.223 + -- finish overriding protection triggers (avoids garbage): 1.224 + DELETE FROM "temporary_transaction_data" 1.225 + WHERE "key" = 'override_protection_triggers'; 1.226 + -- materialize battle_view: 1.227 + -- NOTE: "closed" column of issue must be set at this point 1.228 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 1.229 + INSERT INTO "battle" ( 1.230 + "issue_id", 1.231 + "winning_initiative_id", "losing_initiative_id", 1.232 + "count" 1.233 + ) SELECT 1.234 + "issue_id", 1.235 + "winning_initiative_id", "losing_initiative_id", 1.236 + "count" 1.237 + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 1.238 + -- set voter count: 1.239 + UPDATE "issue" SET 1.240 + "voter_count" = ( 1.241 + SELECT coalesce(sum("weight"), 0) 1.242 + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 1.243 + ) 1.244 + WHERE "id" = "issue_id_p"; 1.245 + -- calculate "first_preference_votes": 1.246 + UPDATE "initiative" 1.247 + SET "first_preference_votes" = "subquery"."sum" 1.248 + FROM ( 1.249 + SELECT "vote"."initiative_id", sum("direct_voter"."weight") 1.250 + FROM "vote" JOIN "direct_voter" 1.251 + ON "vote"."issue_id" = "direct_voter"."issue_id" 1.252 + AND "vote"."member_id" = "direct_voter"."member_id" 1.253 + WHERE "vote"."first_preference" 1.254 + GROUP BY "vote"."initiative_id" 1.255 + ) AS "subquery" 1.256 + WHERE "initiative"."issue_id" = "issue_id_p" 1.257 + AND "initiative"."admitted" 1.258 + AND "initiative"."id" = "subquery"."initiative_id"; 1.259 + UPDATE "initiative" SET "first_preference_votes" = 0 1.260 + WHERE "issue_id" = "issue_id_p" 1.261 + AND "initiative"."admitted" 1.262 + AND "first_preference_votes" ISNULL; 1.263 + -- copy "positive_votes" and "negative_votes" from "battle" table: 1.264 + UPDATE "initiative" SET 1.265 + "positive_votes" = "battle_win"."count", 1.266 + "negative_votes" = "battle_lose"."count" 1.267 + FROM "battle" AS "battle_win", "battle" AS "battle_lose" 1.268 + WHERE 1.269 + "battle_win"."issue_id" = "issue_id_p" AND 1.270 + "battle_win"."winning_initiative_id" = "initiative"."id" AND 1.271 + "battle_win"."losing_initiative_id" ISNULL AND 1.272 + "battle_lose"."issue_id" = "issue_id_p" AND 1.273 + "battle_lose"."losing_initiative_id" = "initiative"."id" AND 1.274 + "battle_lose"."winning_initiative_id" ISNULL; 1.275 + END; 1.276 + $$; 1.277 + 1.278 +COMMIT;