liquid_feedback_core
diff update/core-update.v2.2.1-v2.2.2.sql @ 383:1c991490f075
Fixed an error, which occurred when deleting votes from voters, which lost their voting right during voting phase
author | jbe |
---|---|
date | Fri Mar 22 22:37:59 2013 +0100 (2013-03-22) |
parents | |
children |
line diff
1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 1.2 +++ b/update/core-update.v2.2.1-v2.2.2.sql Fri Mar 22 22:37:59 2013 +0100 1.3 @@ -0,0 +1,142 @@ 1.4 +BEGIN; 1.5 + 1.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 1.7 + SELECT * FROM (VALUES ('2.2.2', 2, 2, 2)) 1.8 + AS "subquery"("string", "major", "minor", "revision"); 1.9 + 1.10 +CREATE TABLE "internal_session_store" ( 1.11 + PRIMARY KEY ("backend_pid", "key"), 1.12 + "backend_pid" INT4, 1.13 + "key" TEXT, 1.14 + "value" TEXT NOT NULL ); 1.15 + 1.16 +COMMENT ON TABLE "internal_session_store" IS 'Table to store session variables; shall be emptied before a transaction is committed'; 1.17 + 1.18 +COMMENT ON COLUMN "internal_session_store"."backend_pid" IS 'Value returned by function pg_backend_pid()'; 1.19 + 1.20 +CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"() 1.21 + RETURNS TRIGGER 1.22 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.23 + DECLARE 1.24 + "issue_id_v" "issue"."id"%TYPE; 1.25 + "issue_row" "issue"%ROWTYPE; 1.26 + BEGIN 1.27 + IF EXISTS ( 1.28 + SELECT NULL FROM "internal_session_store" 1.29 + WHERE "backend_pid" = pg_backend_pid() 1.30 + AND "key" = 'override_protection_triggers' 1.31 + AND "value" = TRUE::TEXT 1.32 + ) THEN 1.33 + RETURN NULL; 1.34 + END IF; 1.35 + IF TG_OP = 'DELETE' THEN 1.36 + "issue_id_v" := OLD."issue_id"; 1.37 + ELSE 1.38 + "issue_id_v" := NEW."issue_id"; 1.39 + END IF; 1.40 + SELECT INTO "issue_row" * FROM "issue" 1.41 + WHERE "id" = "issue_id_v" FOR SHARE; 1.42 + IF ( 1.43 + "issue_row"."closed" NOTNULL OR ( 1.44 + "issue_row"."state" = 'voting' AND 1.45 + "issue_row"."phase_finished" NOTNULL 1.46 + ) 1.47 + ) THEN 1.48 + IF 1.49 + TG_RELID = 'direct_voter'::regclass AND 1.50 + TG_OP = 'UPDATE' 1.51 + THEN 1.52 + IF 1.53 + OLD."issue_id" = NEW."issue_id" AND 1.54 + OLD."member_id" = NEW."member_id" AND 1.55 + OLD."weight" = NEW."weight" 1.56 + THEN 1.57 + RETURN NULL; -- allows changing of voter comment 1.58 + END IF; 1.59 + END IF; 1.60 + RAISE EXCEPTION 'Tried to modify data after voting has been closed.'; 1.61 + END IF; 1.62 + RETURN NULL; 1.63 + END; 1.64 + $$; 1.65 + 1.66 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 1.67 + RETURNS VOID 1.68 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.69 + DECLARE 1.70 + "area_id_v" "area"."id"%TYPE; 1.71 + "unit_id_v" "unit"."id"%TYPE; 1.72 + "member_id_v" "member"."id"%TYPE; 1.73 + BEGIN 1.74 + PERFORM "require_transaction_isolation"(); 1.75 + SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 1.76 + SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 1.77 + -- override protection triggers: 1.78 + DELETE FROM "internal_session_store"; 1.79 + INSERT INTO "internal_session_store" ("backend_pid", "key", "value") 1.80 + VALUES (pg_backend_pid(), 'override_protection_triggers', TRUE::TEXT); 1.81 + -- delete timestamp of voting comment: 1.82 + UPDATE "direct_voter" SET "comment_changed" = NULL 1.83 + WHERE "issue_id" = "issue_id_p"; 1.84 + -- delete delegating votes (in cases of manual reset of issue state): 1.85 + DELETE FROM "delegating_voter" 1.86 + WHERE "issue_id" = "issue_id_p"; 1.87 + -- delete votes from non-privileged voters: 1.88 + DELETE FROM "direct_voter" 1.89 + USING ( 1.90 + SELECT 1.91 + "direct_voter"."member_id" 1.92 + FROM "direct_voter" 1.93 + JOIN "member" ON "direct_voter"."member_id" = "member"."id" 1.94 + LEFT JOIN "privilege" 1.95 + ON "privilege"."unit_id" = "unit_id_v" 1.96 + AND "privilege"."member_id" = "direct_voter"."member_id" 1.97 + WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 1.98 + "member"."active" = FALSE OR 1.99 + "privilege"."voting_right" ISNULL OR 1.100 + "privilege"."voting_right" = FALSE 1.101 + ) 1.102 + ) AS "subquery" 1.103 + WHERE "direct_voter"."issue_id" = "issue_id_p" 1.104 + AND "direct_voter"."member_id" = "subquery"."member_id"; 1.105 + -- consider delegations: 1.106 + UPDATE "direct_voter" SET "weight" = 1 1.107 + WHERE "issue_id" = "issue_id_p"; 1.108 + PERFORM "add_vote_delegations"("issue_id_p"); 1.109 + -- finish overriding protection triggers (mandatory, as pids may be reused): 1.110 + DELETE FROM "internal_session_store"; 1.111 + -- materialize battle_view: 1.112 + -- NOTE: "closed" column of issue must be set at this point 1.113 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 1.114 + INSERT INTO "battle" ( 1.115 + "issue_id", 1.116 + "winning_initiative_id", "losing_initiative_id", 1.117 + "count" 1.118 + ) SELECT 1.119 + "issue_id", 1.120 + "winning_initiative_id", "losing_initiative_id", 1.121 + "count" 1.122 + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 1.123 + -- set voter count: 1.124 + UPDATE "issue" SET 1.125 + "voter_count" = ( 1.126 + SELECT coalesce(sum("weight"), 0) 1.127 + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 1.128 + ) 1.129 + WHERE "id" = "issue_id_p"; 1.130 + -- copy "positive_votes" and "negative_votes" from "battle" table: 1.131 + UPDATE "initiative" SET 1.132 + "positive_votes" = "battle_win"."count", 1.133 + "negative_votes" = "battle_lose"."count" 1.134 + FROM "battle" AS "battle_win", "battle" AS "battle_lose" 1.135 + WHERE 1.136 + "battle_win"."issue_id" = "issue_id_p" AND 1.137 + "battle_win"."winning_initiative_id" = "initiative"."id" AND 1.138 + "battle_win"."losing_initiative_id" ISNULL AND 1.139 + "battle_lose"."issue_id" = "issue_id_p" AND 1.140 + "battle_lose"."losing_initiative_id" = "initiative"."id" AND 1.141 + "battle_lose"."winning_initiative_id" ISNULL; 1.142 + END; 1.143 + $$; 1.144 + 1.145 +COMMIT;