liquid_feedback_core
changeset 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 | e3dd2c3629db |
children | eb12a069063c |
files | core.sql update/core-update.v2.2.6-v3.0.1.sql update/core-update.v3.0.2-v3.0.3.sql |
line diff
1.1 --- a/core.sql Fri Jul 11 01:44:25 2014 +0200 1.2 +++ b/core.sql Wed Jul 16 16:28:35 2014 +0200 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.2', 3, 0, 2)) 1.8 + SELECT * FROM (VALUES ('3.0.3', 3, 0, 3)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11 1.12 @@ -3806,7 +3806,7 @@ 1.13 WHERE "id" = "issue_id_p"; 1.14 -- calculate "first_preference_votes": 1.15 UPDATE "initiative" 1.16 - SET "first_preference_votes" = coalesce("subquery"."sum", 0) 1.17 + SET "first_preference_votes" = "subquery"."sum" 1.18 FROM ( 1.19 SELECT "vote"."initiative_id", sum("direct_voter"."weight") 1.20 FROM "vote" JOIN "direct_voter" 1.21 @@ -3818,6 +3818,10 @@ 1.22 WHERE "initiative"."issue_id" = "issue_id_p" 1.23 AND "initiative"."admitted" 1.24 AND "initiative"."id" = "subquery"."initiative_id"; 1.25 + UPDATE "initiative" SET "first_preference_votes" = 0 1.26 + WHERE "issue_id" = "issue_id_p" 1.27 + AND "initiative"."admitted" 1.28 + AND "first_preference_votes" ISNULL; 1.29 -- copy "positive_votes" and "negative_votes" from "battle" table: 1.30 UPDATE "initiative" SET 1.31 "positive_votes" = "battle_win"."count",
2.1 --- a/update/core-update.v2.2.6-v3.0.1.sql Fri Jul 11 01:44:25 2014 +0200 2.2 +++ b/update/core-update.v2.2.6-v3.0.1.sql Wed Jul 16 16:28:35 2014 +0200 2.3 @@ -336,4 +336,235 @@ 2.4 END; 2.5 $$; 2.6 2.7 +ALTER TABLE "initiative" ADD COLUMN "first_preference_votes" INT4; 2.8 + 2.9 +ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"; 2.10 +ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK ( 2.11 + ( "admitted" NOTNULL AND "admitted" = TRUE ) OR 2.12 + ( "first_preference_votes" ISNULL AND 2.13 + "positive_votes" ISNULL AND "negative_votes" ISNULL AND 2.14 + "direct_majority" ISNULL AND "indirect_majority" ISNULL AND 2.15 + "schulze_rank" ISNULL AND 2.16 + "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND 2.17 + "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND 2.18 + "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ); 2.19 + 2.20 +COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice'; 2.21 +COMMENT ON COLUMN "initiative"."positive_votes" IS 'Number of direct and delegating voters who ranked this initiative better than the status quo'; 2.22 +COMMENT ON COLUMN "initiative"."negative_votes" IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo'; 2.23 + 2.24 +-- UPDATE TABLE "vote" SET "grade" = 0 WHERE "grade" ISNULL; -- should not be necessary 2.25 +ALTER TABLE "vote" ALTER COLUMN "grade" SET NOT NULL; 2.26 + 2.27 +ALTER TABLE "vote" ADD COLUMN "first_preference" BOOLEAN; 2.28 + 2.29 +ALTER TABLE "vote" ADD 2.30 + CONSTRAINT "first_preference_flag_only_set_on_positive_grades" 2.31 + CHECK ("grade" > 0 OR "first_preference" ISNULL); 2.32 + 2.33 +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.34 + 2.35 +INSERT INTO "temporary_transaction_data" ("key", "value") 2.36 + VALUES ('override_protection_triggers', TRUE::TEXT); 2.37 + 2.38 +UPDATE "vote" SET "first_preference" = "subquery"."first_preference" 2.39 + FROM ( 2.40 + SELECT 2.41 + "vote"."initiative_id", 2.42 + "vote"."member_id", 2.43 + CASE WHEN "vote"."grade" > 0 THEN 2.44 + CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END 2.45 + ELSE NULL 2.46 + END AS "first_preference" 2.47 + FROM "vote" 2.48 + JOIN "initiative" -- NOTE: due to missing index on issue_id 2.49 + ON "vote"."issue_id" = "initiative"."issue_id" 2.50 + JOIN "vote" AS "agg" 2.51 + ON "initiative"."id" = "agg"."initiative_id" 2.52 + AND "vote"."member_id" = "agg"."member_id" 2.53 + GROUP BY "vote"."initiative_id", "vote"."member_id" 2.54 + ) AS "subquery" 2.55 + WHERE "vote"."initiative_id" = "subquery"."initiative_id" 2.56 + AND "vote"."member_id" = "subquery"."member_id"; 2.57 + 2.58 +DELETE FROM "temporary_transaction_data" 2.59 + WHERE "key" = 'override_protection_triggers'; 2.60 + 2.61 +UPDATE "initiative" 2.62 + SET "first_preference_votes" = coalesce("subquery"."sum", 0) 2.63 + FROM ( 2.64 + SELECT "vote"."initiative_id", sum("direct_voter"."weight") 2.65 + FROM "vote" JOIN "direct_voter" 2.66 + ON "vote"."issue_id" = "direct_voter"."issue_id" 2.67 + AND "vote"."member_id" = "direct_voter"."member_id" 2.68 + WHERE "vote"."first_preference" 2.69 + GROUP BY "vote"."initiative_id" 2.70 + ) AS "subquery" 2.71 + WHERE "initiative"."admitted" 2.72 + AND "initiative"."id" = "subquery"."initiative_id"; 2.73 + 2.74 +-- reconstruct battle data (originating from LiquidFeedback Core before v2.0.0) 2.75 +-- to avoid future data loss when executing "clean_issue" to delete voting data: 2.76 +INSERT INTO "battle" ( 2.77 + "issue_id", 2.78 + "winning_initiative_id", 2.79 + "losing_initiative_id", 2.80 + "count" 2.81 + ) SELECT 2.82 + "battle_view"."issue_id", 2.83 + "battle_view"."winning_initiative_id", 2.84 + "battle_view"."losing_initiative_id", 2.85 + "battle_view"."count" 2.86 + FROM ( 2.87 + SELECT 2.88 + "issue"."id" AS "issue_id", 2.89 + "winning_initiative"."id" AS "winning_initiative_id", 2.90 + "losing_initiative"."id" AS "losing_initiative_id", 2.91 + sum( 2.92 + CASE WHEN 2.93 + coalesce("better_vote"."grade", 0) > 2.94 + coalesce("worse_vote"."grade", 0) 2.95 + THEN "direct_voter"."weight" ELSE 0 END 2.96 + ) AS "count" 2.97 + FROM "issue" 2.98 + LEFT JOIN "direct_voter" 2.99 + ON "issue"."id" = "direct_voter"."issue_id" 2.100 + JOIN "battle_participant" AS "winning_initiative" 2.101 + ON "issue"."id" = "winning_initiative"."issue_id" 2.102 + JOIN "battle_participant" AS "losing_initiative" 2.103 + ON "issue"."id" = "losing_initiative"."issue_id" 2.104 + LEFT JOIN "vote" AS "better_vote" 2.105 + ON "direct_voter"."member_id" = "better_vote"."member_id" 2.106 + AND "winning_initiative"."id" = "better_vote"."initiative_id" 2.107 + LEFT JOIN "vote" AS "worse_vote" 2.108 + ON "direct_voter"."member_id" = "worse_vote"."member_id" 2.109 + AND "losing_initiative"."id" = "worse_vote"."initiative_id" 2.110 + WHERE "issue"."state" IN ('finished_with_winner', 'finished_without_winner') 2.111 + AND "winning_initiative"."id" != "losing_initiative"."id" 2.112 + -- NOTE: comparisons with status-quo are intentionally omitted to mark 2.113 + -- issues that were counted prior LiquidFeedback Core v2.0.0 2.114 + GROUP BY 2.115 + "issue"."id", 2.116 + "winning_initiative"."id", 2.117 + "losing_initiative"."id" 2.118 + ) AS "battle_view" 2.119 + LEFT JOIN "battle" 2.120 + ON "battle_view"."winning_initiative_id" = "battle"."winning_initiative_id" 2.121 + AND "battle_view"."losing_initiative_id" = "battle"."losing_initiative_id" 2.122 + WHERE "battle" ISNULL; 2.123 + 2.124 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 2.125 + RETURNS VOID 2.126 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.127 + DECLARE 2.128 + "area_id_v" "area"."id"%TYPE; 2.129 + "unit_id_v" "unit"."id"%TYPE; 2.130 + "member_id_v" "member"."id"%TYPE; 2.131 + BEGIN 2.132 + PERFORM "require_transaction_isolation"(); 2.133 + SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 2.134 + SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 2.135 + -- override protection triggers: 2.136 + INSERT INTO "temporary_transaction_data" ("key", "value") 2.137 + VALUES ('override_protection_triggers', TRUE::TEXT); 2.138 + -- delete timestamp of voting comment: 2.139 + UPDATE "direct_voter" SET "comment_changed" = NULL 2.140 + WHERE "issue_id" = "issue_id_p"; 2.141 + -- delete delegating votes (in cases of manual reset of issue state): 2.142 + DELETE FROM "delegating_voter" 2.143 + WHERE "issue_id" = "issue_id_p"; 2.144 + -- delete votes from non-privileged voters: 2.145 + DELETE FROM "direct_voter" 2.146 + USING ( 2.147 + SELECT 2.148 + "direct_voter"."member_id" 2.149 + FROM "direct_voter" 2.150 + JOIN "member" ON "direct_voter"."member_id" = "member"."id" 2.151 + LEFT JOIN "privilege" 2.152 + ON "privilege"."unit_id" = "unit_id_v" 2.153 + AND "privilege"."member_id" = "direct_voter"."member_id" 2.154 + WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 2.155 + "member"."active" = FALSE OR 2.156 + "privilege"."voting_right" ISNULL OR 2.157 + "privilege"."voting_right" = FALSE 2.158 + ) 2.159 + ) AS "subquery" 2.160 + WHERE "direct_voter"."issue_id" = "issue_id_p" 2.161 + AND "direct_voter"."member_id" = "subquery"."member_id"; 2.162 + -- consider delegations: 2.163 + UPDATE "direct_voter" SET "weight" = 1 2.164 + WHERE "issue_id" = "issue_id_p"; 2.165 + PERFORM "add_vote_delegations"("issue_id_p"); 2.166 + -- mark first preferences: 2.167 + UPDATE "vote" SET "first_preference" = "subquery"."first_preference" 2.168 + FROM ( 2.169 + SELECT 2.170 + "vote"."initiative_id", 2.171 + "vote"."member_id", 2.172 + CASE WHEN "vote"."grade" > 0 THEN 2.173 + CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END 2.174 + ELSE NULL 2.175 + END AS "first_preference" 2.176 + FROM "vote" 2.177 + JOIN "initiative" -- NOTE: due to missing index on issue_id 2.178 + ON "vote"."issue_id" = "initiative"."issue_id" 2.179 + JOIN "vote" AS "agg" 2.180 + ON "initiative"."id" = "agg"."initiative_id" 2.181 + AND "vote"."member_id" = "agg"."member_id" 2.182 + GROUP BY "vote"."initiative_id", "vote"."member_id" 2.183 + ) AS "subquery" 2.184 + WHERE "vote"."issue_id" = "issue_id_p" 2.185 + AND "vote"."initiative_id" = "subquery"."initiative_id" 2.186 + AND "vote"."member_id" = "subquery"."member_id"; 2.187 + -- finish overriding protection triggers (avoids garbage): 2.188 + DELETE FROM "temporary_transaction_data" 2.189 + WHERE "key" = 'override_protection_triggers'; 2.190 + -- materialize battle_view: 2.191 + -- NOTE: "closed" column of issue must be set at this point 2.192 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 2.193 + INSERT INTO "battle" ( 2.194 + "issue_id", 2.195 + "winning_initiative_id", "losing_initiative_id", 2.196 + "count" 2.197 + ) SELECT 2.198 + "issue_id", 2.199 + "winning_initiative_id", "losing_initiative_id", 2.200 + "count" 2.201 + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 2.202 + -- set voter count: 2.203 + UPDATE "issue" SET 2.204 + "voter_count" = ( 2.205 + SELECT coalesce(sum("weight"), 0) 2.206 + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 2.207 + ) 2.208 + WHERE "id" = "issue_id_p"; 2.209 + -- calculate "first_preference_votes": 2.210 + UPDATE "initiative" 2.211 + SET "first_preference_votes" = coalesce("subquery"."sum", 0) 2.212 + FROM ( 2.213 + SELECT "vote"."initiative_id", sum("direct_voter"."weight") 2.214 + FROM "vote" JOIN "direct_voter" 2.215 + ON "vote"."issue_id" = "direct_voter"."issue_id" 2.216 + AND "vote"."member_id" = "direct_voter"."member_id" 2.217 + WHERE "vote"."first_preference" 2.218 + GROUP BY "vote"."initiative_id" 2.219 + ) AS "subquery" 2.220 + WHERE "initiative"."issue_id" = "issue_id_p" 2.221 + AND "initiative"."admitted" 2.222 + AND "initiative"."id" = "subquery"."initiative_id"; 2.223 + -- copy "positive_votes" and "negative_votes" from "battle" table: 2.224 + UPDATE "initiative" SET 2.225 + "positive_votes" = "battle_win"."count", 2.226 + "negative_votes" = "battle_lose"."count" 2.227 + FROM "battle" AS "battle_win", "battle" AS "battle_lose" 2.228 + WHERE 2.229 + "battle_win"."issue_id" = "issue_id_p" AND 2.230 + "battle_win"."winning_initiative_id" = "initiative"."id" AND 2.231 + "battle_win"."losing_initiative_id" ISNULL AND 2.232 + "battle_lose"."issue_id" = "issue_id_p" AND 2.233 + "battle_lose"."losing_initiative_id" = "initiative"."id" AND 2.234 + "battle_lose"."winning_initiative_id" ISNULL; 2.235 + END; 2.236 + $$; 2.237 + 2.238 COMMIT;
3.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 3.2 +++ b/update/core-update.v3.0.2-v3.0.3.sql Wed Jul 16 16:28:35 2014 +0200 3.3 @@ -0,0 +1,275 @@ 3.4 +-- NOTICE: This update script disables the "no_reserve_beat_path" setting for 3.5 +-- all policies. If this is not intended, please edit this script 3.6 +-- before applying it to your database. 3.7 + 3.8 +BEGIN; 3.9 + 3.10 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 3.11 + SELECT * FROM (VALUES ('3.0.3', 3, 0, 3)) 3.12 + AS "subquery"("string", "major", "minor", "revision"); 3.13 + 3.14 +CREATE FUNCTION "update3_0_3_add_columns_if_missing"() 3.15 + RETURNS VOID 3.16 + LANGUAGE 'plpgsql' AS $$ 3.17 + BEGIN 3.18 + BEGIN 3.19 + ALTER TABLE "initiative" ADD COLUMN "first_preference_votes" INT4; 3.20 + EXCEPTION 3.21 + WHEN duplicate_column THEN 3.22 + RAISE NOTICE 'column "first_preference_votes" of relation "initiative" already exists, skipping'; 3.23 + END; 3.24 + BEGIN 3.25 + ALTER TABLE "vote" ADD COLUMN "first_preference" BOOLEAN; 3.26 + EXCEPTION 3.27 + WHEN duplicate_column THEN 3.28 + RAISE NOTICE 'column "first_preference" of relation "vote" already exists, skipping'; 3.29 + END; 3.30 + RETURN; 3.31 + END; 3.32 + $$; 3.33 + 3.34 +SELECT "update3_0_3_add_columns_if_missing"(); 3.35 + 3.36 +DROP FUNCTION "update3_0_3_add_columns_if_missing"(); 3.37 + 3.38 +ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"; 3.39 +ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK ( 3.40 + ( "admitted" NOTNULL AND "admitted" = TRUE ) OR 3.41 + ( "first_preference_votes" ISNULL AND 3.42 + "positive_votes" ISNULL AND "negative_votes" ISNULL AND 3.43 + "direct_majority" ISNULL AND "indirect_majority" ISNULL AND 3.44 + "schulze_rank" ISNULL AND 3.45 + "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND 3.46 + "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND 3.47 + "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ); 3.48 + 3.49 +COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice'; 3.50 +COMMENT ON COLUMN "initiative"."positive_votes" IS 'Number of direct and delegating voters who ranked this initiative better than the status quo'; 3.51 +COMMENT ON COLUMN "initiative"."negative_votes" IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo'; 3.52 + 3.53 +-- UPDATE TABLE "vote" SET "grade" = 0 WHERE "grade" ISNULL; -- should not be necessary 3.54 +ALTER TABLE "vote" ALTER COLUMN "grade" SET NOT NULL; 3.55 + 3.56 +ALTER TABLE "vote" DROP CONSTRAINT IF EXISTS "first_preference_flag_only_set_on_positive_grades"; 3.57 +ALTER TABLE "vote" ADD 3.58 + CONSTRAINT "first_preference_flag_only_set_on_positive_grades" 3.59 + CHECK ("grade" > 0 OR "first_preference" ISNULL); 3.60 + 3.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.'; 3.62 + 3.63 +INSERT INTO "temporary_transaction_data" ("key", "value") 3.64 + VALUES ('override_protection_triggers', TRUE::TEXT); 3.65 + 3.66 +UPDATE "vote" SET "first_preference" = "subquery"."first_preference" 3.67 + FROM ( 3.68 + SELECT 3.69 + "vote"."initiative_id", 3.70 + "vote"."member_id", 3.71 + CASE WHEN "vote"."grade" > 0 THEN 3.72 + CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END 3.73 + ELSE NULL 3.74 + END AS "first_preference" 3.75 + FROM "vote" 3.76 + JOIN "initiative" -- NOTE: due to missing index on issue_id 3.77 + ON "vote"."issue_id" = "initiative"."issue_id" 3.78 + JOIN "vote" AS "agg" 3.79 + ON "initiative"."id" = "agg"."initiative_id" 3.80 + AND "vote"."member_id" = "agg"."member_id" 3.81 + GROUP BY "vote"."initiative_id", "vote"."member_id" 3.82 + ) AS "subquery" 3.83 + WHERE "vote"."initiative_id" = "subquery"."initiative_id" 3.84 + AND "vote"."member_id" = "subquery"."member_id"; 3.85 + 3.86 +DELETE FROM "temporary_transaction_data" 3.87 + WHERE "key" = 'override_protection_triggers'; 3.88 + 3.89 +UPDATE "initiative" SET "first_preference_votes" = NULL 3.90 + WHERE "first_preference_votes" = 0; 3.91 + 3.92 +UPDATE "initiative" 3.93 + SET "first_preference_votes" = "subquery"."sum" 3.94 + FROM ( 3.95 + SELECT "vote"."initiative_id", sum("direct_voter"."weight") 3.96 + FROM "vote" JOIN "direct_voter" 3.97 + ON "vote"."issue_id" = "direct_voter"."issue_id" 3.98 + AND "vote"."member_id" = "direct_voter"."member_id" 3.99 + WHERE "vote"."first_preference" 3.100 + GROUP BY "vote"."initiative_id" 3.101 + ) AS "subquery" 3.102 + WHERE "initiative"."admitted" 3.103 + AND "initiative"."id" = "subquery"."initiative_id" 3.104 + AND "initiative"."first_preference_votes" ISNULL; 3.105 + 3.106 +UPDATE "initiative" SET "first_preference_votes" = 0 3.107 + WHERE "positive_votes" NOTNULL 3.108 + AND "first_preference_votes" ISNULL; 3.109 + 3.110 +-- reconstruct battle data (originating from LiquidFeedback Core before v2.0.0) 3.111 +-- to avoid future data loss when executing "clean_issue" to delete voting data: 3.112 +INSERT INTO "battle" ( 3.113 + "issue_id", 3.114 + "winning_initiative_id", 3.115 + "losing_initiative_id", 3.116 + "count" 3.117 + ) SELECT 3.118 + "battle_view"."issue_id", 3.119 + "battle_view"."winning_initiative_id", 3.120 + "battle_view"."losing_initiative_id", 3.121 + "battle_view"."count" 3.122 + FROM ( 3.123 + SELECT 3.124 + "issue"."id" AS "issue_id", 3.125 + "winning_initiative"."id" AS "winning_initiative_id", 3.126 + "losing_initiative"."id" AS "losing_initiative_id", 3.127 + sum( 3.128 + CASE WHEN 3.129 + coalesce("better_vote"."grade", 0) > 3.130 + coalesce("worse_vote"."grade", 0) 3.131 + THEN "direct_voter"."weight" ELSE 0 END 3.132 + ) AS "count" 3.133 + FROM "issue" 3.134 + LEFT JOIN "direct_voter" 3.135 + ON "issue"."id" = "direct_voter"."issue_id" 3.136 + JOIN "battle_participant" AS "winning_initiative" 3.137 + ON "issue"."id" = "winning_initiative"."issue_id" 3.138 + JOIN "battle_participant" AS "losing_initiative" 3.139 + ON "issue"."id" = "losing_initiative"."issue_id" 3.140 + LEFT JOIN "vote" AS "better_vote" 3.141 + ON "direct_voter"."member_id" = "better_vote"."member_id" 3.142 + AND "winning_initiative"."id" = "better_vote"."initiative_id" 3.143 + LEFT JOIN "vote" AS "worse_vote" 3.144 + ON "direct_voter"."member_id" = "worse_vote"."member_id" 3.145 + AND "losing_initiative"."id" = "worse_vote"."initiative_id" 3.146 + WHERE "issue"."state" IN ('finished_with_winner', 'finished_without_winner') 3.147 + AND "winning_initiative"."id" != "losing_initiative"."id" 3.148 + -- NOTE: comparisons with status-quo are intentionally omitted to mark 3.149 + -- issues that were counted prior LiquidFeedback Core v2.0.0 3.150 + GROUP BY 3.151 + "issue"."id", 3.152 + "winning_initiative"."id", 3.153 + "losing_initiative"."id" 3.154 + ) AS "battle_view" 3.155 + LEFT JOIN "battle" 3.156 + ON "battle_view"."winning_initiative_id" = "battle"."winning_initiative_id" 3.157 + AND "battle_view"."losing_initiative_id" = "battle"."losing_initiative_id" 3.158 + WHERE "battle" ISNULL; 3.159 + 3.160 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 3.161 + RETURNS VOID 3.162 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.163 + DECLARE 3.164 + "area_id_v" "area"."id"%TYPE; 3.165 + "unit_id_v" "unit"."id"%TYPE; 3.166 + "member_id_v" "member"."id"%TYPE; 3.167 + BEGIN 3.168 + PERFORM "require_transaction_isolation"(); 3.169 + SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 3.170 + SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 3.171 + -- override protection triggers: 3.172 + INSERT INTO "temporary_transaction_data" ("key", "value") 3.173 + VALUES ('override_protection_triggers', TRUE::TEXT); 3.174 + -- delete timestamp of voting comment: 3.175 + UPDATE "direct_voter" SET "comment_changed" = NULL 3.176 + WHERE "issue_id" = "issue_id_p"; 3.177 + -- delete delegating votes (in cases of manual reset of issue state): 3.178 + DELETE FROM "delegating_voter" 3.179 + WHERE "issue_id" = "issue_id_p"; 3.180 + -- delete votes from non-privileged voters: 3.181 + DELETE FROM "direct_voter" 3.182 + USING ( 3.183 + SELECT 3.184 + "direct_voter"."member_id" 3.185 + FROM "direct_voter" 3.186 + JOIN "member" ON "direct_voter"."member_id" = "member"."id" 3.187 + LEFT JOIN "privilege" 3.188 + ON "privilege"."unit_id" = "unit_id_v" 3.189 + AND "privilege"."member_id" = "direct_voter"."member_id" 3.190 + WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 3.191 + "member"."active" = FALSE OR 3.192 + "privilege"."voting_right" ISNULL OR 3.193 + "privilege"."voting_right" = FALSE 3.194 + ) 3.195 + ) AS "subquery" 3.196 + WHERE "direct_voter"."issue_id" = "issue_id_p" 3.197 + AND "direct_voter"."member_id" = "subquery"."member_id"; 3.198 + -- consider delegations: 3.199 + UPDATE "direct_voter" SET "weight" = 1 3.200 + WHERE "issue_id" = "issue_id_p"; 3.201 + PERFORM "add_vote_delegations"("issue_id_p"); 3.202 + -- mark first preferences: 3.203 + UPDATE "vote" SET "first_preference" = "subquery"."first_preference" 3.204 + FROM ( 3.205 + SELECT 3.206 + "vote"."initiative_id", 3.207 + "vote"."member_id", 3.208 + CASE WHEN "vote"."grade" > 0 THEN 3.209 + CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END 3.210 + ELSE NULL 3.211 + END AS "first_preference" 3.212 + FROM "vote" 3.213 + JOIN "initiative" -- NOTE: due to missing index on issue_id 3.214 + ON "vote"."issue_id" = "initiative"."issue_id" 3.215 + JOIN "vote" AS "agg" 3.216 + ON "initiative"."id" = "agg"."initiative_id" 3.217 + AND "vote"."member_id" = "agg"."member_id" 3.218 + GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade" 3.219 + ) AS "subquery" 3.220 + WHERE "vote"."issue_id" = "issue_id_p" 3.221 + AND "vote"."initiative_id" = "subquery"."initiative_id" 3.222 + AND "vote"."member_id" = "subquery"."member_id"; 3.223 + -- finish overriding protection triggers (avoids garbage): 3.224 + DELETE FROM "temporary_transaction_data" 3.225 + WHERE "key" = 'override_protection_triggers'; 3.226 + -- materialize battle_view: 3.227 + -- NOTE: "closed" column of issue must be set at this point 3.228 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 3.229 + INSERT INTO "battle" ( 3.230 + "issue_id", 3.231 + "winning_initiative_id", "losing_initiative_id", 3.232 + "count" 3.233 + ) SELECT 3.234 + "issue_id", 3.235 + "winning_initiative_id", "losing_initiative_id", 3.236 + "count" 3.237 + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 3.238 + -- set voter count: 3.239 + UPDATE "issue" SET 3.240 + "voter_count" = ( 3.241 + SELECT coalesce(sum("weight"), 0) 3.242 + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 3.243 + ) 3.244 + WHERE "id" = "issue_id_p"; 3.245 + -- calculate "first_preference_votes": 3.246 + UPDATE "initiative" 3.247 + SET "first_preference_votes" = "subquery"."sum" 3.248 + FROM ( 3.249 + SELECT "vote"."initiative_id", sum("direct_voter"."weight") 3.250 + FROM "vote" JOIN "direct_voter" 3.251 + ON "vote"."issue_id" = "direct_voter"."issue_id" 3.252 + AND "vote"."member_id" = "direct_voter"."member_id" 3.253 + WHERE "vote"."first_preference" 3.254 + GROUP BY "vote"."initiative_id" 3.255 + ) AS "subquery" 3.256 + WHERE "initiative"."issue_id" = "issue_id_p" 3.257 + AND "initiative"."admitted" 3.258 + AND "initiative"."id" = "subquery"."initiative_id"; 3.259 + UPDATE "initiative" SET "first_preference_votes" = 0 3.260 + WHERE "issue_id" = "issue_id_p" 3.261 + AND "initiative"."admitted" 3.262 + AND "first_preference_votes" ISNULL; 3.263 + -- copy "positive_votes" and "negative_votes" from "battle" table: 3.264 + UPDATE "initiative" SET 3.265 + "positive_votes" = "battle_win"."count", 3.266 + "negative_votes" = "battle_lose"."count" 3.267 + FROM "battle" AS "battle_win", "battle" AS "battle_lose" 3.268 + WHERE 3.269 + "battle_win"."issue_id" = "issue_id_p" AND 3.270 + "battle_win"."winning_initiative_id" = "initiative"."id" AND 3.271 + "battle_win"."losing_initiative_id" ISNULL AND 3.272 + "battle_lose"."issue_id" = "issue_id_p" AND 3.273 + "battle_lose"."losing_initiative_id" = "initiative"."id" AND 3.274 + "battle_lose"."winning_initiative_id" ISNULL; 3.275 + END; 3.276 + $$; 3.277 + 3.278 +COMMIT;