liquid_feedback_core
changeset 416:db9ccf3c05f4
Added update script to introduce "first_preference_votes"
author | jbe |
---|---|
date | Wed Mar 26 14:45:49 2014 +0100 (2014-03-26) |
parents | ffc4c08cd835 |
children | bc94ea65757b |
files | core.sql update/core-update.v3.0.0-v3.0.1.sql |
line diff
1.1 --- a/core.sql Wed Mar 26 14:26:46 2014 +0100 1.2 +++ b/core.sql Wed Mar 26 14:45:49 2014 +0100 1.3 @@ -7,7 +7,7 @@ 1.4 BEGIN; 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('3.0.0', 3, 0, 0)) 1.8 + SELECT * FROM (VALUES ('3.0.1', 3, 0, 1)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 2.2 +++ b/update/core-update.v3.0.0-v3.0.1.sql Wed Mar 26 14:45:49 2014 +0100 2.3 @@ -0,0 +1,188 @@ 2.4 +BEGIN; 2.5 + 2.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 2.7 + SELECT * FROM (VALUES ('3.0.1', 3, 0, 1)) 2.8 + AS "subquery"("string", "major", "minor", "revision"); 2.9 + 2.10 +ALTER TABLE "initiative" ADD COLUMN "first_preference_votes" INT4; 2.11 + 2.12 +ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"; 2.13 +ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK ( 2.14 + ( "admitted" NOTNULL AND "admitted" = TRUE ) OR 2.15 + ( "first_preference_votes" ISNULL AND 2.16 + "positive_votes" ISNULL AND "negative_votes" ISNULL AND 2.17 + "direct_majority" ISNULL AND "indirect_majority" ISNULL AND 2.18 + "schulze_rank" ISNULL AND 2.19 + "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND 2.20 + "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND 2.21 + "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ); 2.22 + 2.23 +COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice'; 2.24 +COMMENT ON COLUMN "initiative"."positive_votes" IS 'Number of direct and delegating voters who ranked this initiative better than the status quo'; 2.25 +COMMENT ON COLUMN "initiative"."negative_votes" IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo'; 2.26 + 2.27 +-- UPDATE TABLE "vote" SET "grade" = 0 WHERE "grade" ISNULL; -- should not be necessary 2.28 +ALTER TABLE "vote" ALTER COLUMN "grade" SET NOT NULL; 2.29 + 2.30 +ALTER TABLE "vote" ADD COLUMN "first_preference" BOOLEAN; 2.31 + 2.32 +ALTER TABLE "vote" ADD 2.33 + CONSTRAINT "first_preference_flag_only_set_on_positive_grades" 2.34 + CHECK ("grade" > 0 OR "first_preference" ISNULL); 2.35 + 2.36 +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.'; 2.37 + 2.38 +INSERT INTO "temporary_transaction_data" ("key", "value") 2.39 + VALUES ('override_protection_triggers', TRUE::TEXT); 2.40 + 2.41 +UPDATE "vote" SET "first_preference" = "subquery"."first_preference" 2.42 + FROM ( 2.43 + SELECT 2.44 + "vote"."initiative_id", 2.45 + "vote"."member_id", 2.46 + CASE WHEN "vote"."grade" > 0 THEN 2.47 + CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END 2.48 + ELSE NULL 2.49 + END AS "first_preference" 2.50 + FROM "vote" 2.51 + JOIN "initiative" -- NOTE: due to missing index on issue_id 2.52 + ON "vote"."issue_id" = "initiative"."issue_id" 2.53 + JOIN "vote" AS "agg" 2.54 + ON "initiative"."id" = "agg"."initiative_id" 2.55 + AND "vote"."member_id" = "agg"."member_id" 2.56 + GROUP BY "vote"."initiative_id", "vote"."member_id" 2.57 + ) AS "subquery" 2.58 + WHERE "vote"."initiative_id" = "subquery"."initiative_id" 2.59 + AND "vote"."member_id" = "subquery"."member_id"; 2.60 + 2.61 +DELETE FROM "temporary_transaction_data" 2.62 + WHERE "key" = 'override_protection_triggers'; 2.63 + 2.64 +UPDATE "initiative" 2.65 + SET "first_preference_votes" = coalesce("subquery"."sum", 0) 2.66 + FROM ( 2.67 + SELECT "vote"."initiative_id", sum("direct_voter"."weight") 2.68 + FROM "vote" JOIN "direct_voter" 2.69 + ON "vote"."issue_id" = "direct_voter"."issue_id" 2.70 + AND "vote"."member_id" = "direct_voter"."member_id" 2.71 + WHERE "vote"."first_preference" 2.72 + GROUP BY "vote"."initiative_id" 2.73 + ) AS "subquery" 2.74 + WHERE "initiative"."admitted" 2.75 + AND "initiative"."id" = "subquery"."initiative_id"; 2.76 + 2.77 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 2.78 + RETURNS VOID 2.79 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.80 + DECLARE 2.81 + "area_id_v" "area"."id"%TYPE; 2.82 + "unit_id_v" "unit"."id"%TYPE; 2.83 + "member_id_v" "member"."id"%TYPE; 2.84 + BEGIN 2.85 + PERFORM "require_transaction_isolation"(); 2.86 + SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 2.87 + SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 2.88 + -- override protection triggers: 2.89 + INSERT INTO "temporary_transaction_data" ("key", "value") 2.90 + VALUES ('override_protection_triggers', TRUE::TEXT); 2.91 + -- delete timestamp of voting comment: 2.92 + UPDATE "direct_voter" SET "comment_changed" = NULL 2.93 + WHERE "issue_id" = "issue_id_p"; 2.94 + -- delete delegating votes (in cases of manual reset of issue state): 2.95 + DELETE FROM "delegating_voter" 2.96 + WHERE "issue_id" = "issue_id_p"; 2.97 + -- delete votes from non-privileged voters: 2.98 + DELETE FROM "direct_voter" 2.99 + USING ( 2.100 + SELECT 2.101 + "direct_voter"."member_id" 2.102 + FROM "direct_voter" 2.103 + JOIN "member" ON "direct_voter"."member_id" = "member"."id" 2.104 + LEFT JOIN "privilege" 2.105 + ON "privilege"."unit_id" = "unit_id_v" 2.106 + AND "privilege"."member_id" = "direct_voter"."member_id" 2.107 + WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 2.108 + "member"."active" = FALSE OR 2.109 + "privilege"."voting_right" ISNULL OR 2.110 + "privilege"."voting_right" = FALSE 2.111 + ) 2.112 + ) AS "subquery" 2.113 + WHERE "direct_voter"."issue_id" = "issue_id_p" 2.114 + AND "direct_voter"."member_id" = "subquery"."member_id"; 2.115 + -- consider delegations: 2.116 + UPDATE "direct_voter" SET "weight" = 1 2.117 + WHERE "issue_id" = "issue_id_p"; 2.118 + PERFORM "add_vote_delegations"("issue_id_p"); 2.119 + -- mark first preferences: 2.120 + UPDATE "vote" SET "first_preference" = "subquery"."first_preference" 2.121 + FROM ( 2.122 + SELECT 2.123 + "vote"."initiative_id", 2.124 + "vote"."member_id", 2.125 + CASE WHEN "vote"."grade" > 0 THEN 2.126 + CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END 2.127 + ELSE NULL 2.128 + END AS "first_preference" 2.129 + FROM "vote" 2.130 + JOIN "initiative" -- NOTE: due to missing index on issue_id 2.131 + ON "vote"."issue_id" = "initiative"."issue_id" 2.132 + JOIN "vote" AS "agg" 2.133 + ON "initiative"."id" = "agg"."initiative_id" 2.134 + AND "vote"."member_id" = "agg"."member_id" 2.135 + GROUP BY "vote"."initiative_id", "vote"."member_id" 2.136 + ) AS "subquery" 2.137 + WHERE "vote"."issue_id" = "issue_id_p" 2.138 + AND "vote"."initiative_id" = "subquery"."initiative_id" 2.139 + AND "vote"."member_id" = "subquery"."member_id"; 2.140 + -- finish overriding protection triggers (avoids garbage): 2.141 + DELETE FROM "temporary_transaction_data" 2.142 + WHERE "key" = 'override_protection_triggers'; 2.143 + -- materialize battle_view: 2.144 + -- NOTE: "closed" column of issue must be set at this point 2.145 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 2.146 + INSERT INTO "battle" ( 2.147 + "issue_id", 2.148 + "winning_initiative_id", "losing_initiative_id", 2.149 + "count" 2.150 + ) SELECT 2.151 + "issue_id", 2.152 + "winning_initiative_id", "losing_initiative_id", 2.153 + "count" 2.154 + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 2.155 + -- set voter count: 2.156 + UPDATE "issue" SET 2.157 + "voter_count" = ( 2.158 + SELECT coalesce(sum("weight"), 0) 2.159 + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 2.160 + ) 2.161 + WHERE "id" = "issue_id_p"; 2.162 + -- calculate "first_preference_votes": 2.163 + UPDATE "initiative" 2.164 + SET "first_preference_votes" = coalesce("subquery"."sum", 0) 2.165 + FROM ( 2.166 + SELECT "vote"."initiative_id", sum("direct_voter"."weight") 2.167 + FROM "vote" JOIN "direct_voter" 2.168 + ON "vote"."issue_id" = "direct_voter"."issue_id" 2.169 + AND "vote"."member_id" = "direct_voter"."member_id" 2.170 + WHERE "vote"."first_preference" 2.171 + GROUP BY "vote"."initiative_id" 2.172 + ) AS "subquery" 2.173 + WHERE "initiative"."issue_id" = "issue_id_p" 2.174 + AND "initiative"."admitted" 2.175 + AND "initiative"."id" = "subquery"."initiative_id"; 2.176 + -- copy "positive_votes" and "negative_votes" from "battle" table: 2.177 + UPDATE "initiative" SET 2.178 + "positive_votes" = "battle_win"."count", 2.179 + "negative_votes" = "battle_lose"."count" 2.180 + FROM "battle" AS "battle_win", "battle" AS "battle_lose" 2.181 + WHERE 2.182 + "battle_win"."issue_id" = "issue_id_p" AND 2.183 + "battle_win"."winning_initiative_id" = "initiative"."id" AND 2.184 + "battle_win"."losing_initiative_id" ISNULL AND 2.185 + "battle_lose"."issue_id" = "issue_id_p" AND 2.186 + "battle_lose"."losing_initiative_id" = "initiative"."id" AND 2.187 + "battle_lose"."winning_initiative_id" ISNULL; 2.188 + END; 2.189 + $$; 2.190 + 2.191 +COMMIT;