liquid_feedback_core
changeset 383:1c991490f075 v2.2.2
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 | eda636259846 |
children | 1a5c4cc28d09 |
files | core.sql update/core-update.v2.2.1-v2.2.2.sql |
line diff
1.1 --- a/core.sql Mon Mar 18 14:48:41 2013 +0100 1.2 +++ b/core.sql Fri Mar 22 22:37:59 2013 +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 ('2.2.1', 2, 2, 1)) 1.8 + SELECT * FROM (VALUES ('2.2.2', 2, 2, 2)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11 1.12 @@ -54,6 +54,17 @@ 1.13 ------------------------- 1.14 1.15 1.16 +CREATE TABLE "internal_session_store" ( 1.17 + PRIMARY KEY ("backend_pid", "key"), 1.18 + "backend_pid" INT4, 1.19 + "key" TEXT, 1.20 + "value" TEXT NOT NULL ); 1.21 + 1.22 +COMMENT ON TABLE "internal_session_store" IS 'Table to store session variables; shall be emptied before a transaction is committed'; 1.23 + 1.24 +COMMENT ON COLUMN "internal_session_store"."backend_pid" IS 'Value returned by function pg_backend_pid()'; 1.25 + 1.26 + 1.27 CREATE TABLE "system_setting" ( 1.28 "member_ttl" INTERVAL ); 1.29 CREATE UNIQUE INDEX "system_setting_singleton_idx" ON "system_setting" ((1)); 1.30 @@ -1587,6 +1598,14 @@ 1.31 "issue_id_v" "issue"."id"%TYPE; 1.32 "issue_row" "issue"%ROWTYPE; 1.33 BEGIN 1.34 + IF EXISTS ( 1.35 + SELECT NULL FROM "internal_session_store" 1.36 + WHERE "backend_pid" = pg_backend_pid() 1.37 + AND "key" = 'override_protection_triggers' 1.38 + AND "value" = TRUE::TEXT 1.39 + ) THEN 1.40 + RETURN NULL; 1.41 + END IF; 1.42 IF TG_OP = 'DELETE' THEN 1.43 "issue_id_v" := OLD."issue_id"; 1.44 ELSE 1.45 @@ -1594,7 +1613,12 @@ 1.46 END IF; 1.47 SELECT INTO "issue_row" * FROM "issue" 1.48 WHERE "id" = "issue_id_v" FOR SHARE; 1.49 - IF "issue_row"."closed" NOTNULL THEN 1.50 + IF ( 1.51 + "issue_row"."closed" NOTNULL OR ( 1.52 + "issue_row"."state" = 'voting' AND 1.53 + "issue_row"."phase_finished" NOTNULL 1.54 + ) 1.55 + ) THEN 1.56 IF 1.57 TG_RELID = 'direct_voter'::regclass AND 1.58 TG_OP = 'UPDATE' 1.59 @@ -1607,14 +1631,7 @@ 1.60 RETURN NULL; -- allows changing of voter comment 1.61 END IF; 1.62 END IF; 1.63 - RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.'; 1.64 - ELSIF 1.65 - "issue_row"."state" = 'voting' AND 1.66 - "issue_row"."phase_finished" NOTNULL 1.67 - THEN 1.68 - IF TG_RELID = 'vote'::regclass THEN 1.69 - RAISE EXCEPTION 'Tried to modify data after voting has been closed.'; 1.70 - END IF; 1.71 + RAISE EXCEPTION 'Tried to modify data after voting has been closed.'; 1.72 END IF; 1.73 RETURN NULL; 1.74 END; 1.75 @@ -3653,6 +3670,10 @@ 1.76 PERFORM "require_transaction_isolation"(); 1.77 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 1.78 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 1.79 + -- override protection triggers: 1.80 + DELETE FROM "internal_session_store"; 1.81 + INSERT INTO "internal_session_store" ("backend_pid", "key", "value") 1.82 + VALUES (pg_backend_pid(), 'override_protection_triggers', TRUE::TEXT); 1.83 -- delete timestamp of voting comment: 1.84 UPDATE "direct_voter" SET "comment_changed" = NULL 1.85 WHERE "issue_id" = "issue_id_p"; 1.86 @@ -3681,6 +3702,8 @@ 1.87 UPDATE "direct_voter" SET "weight" = 1 1.88 WHERE "issue_id" = "issue_id_p"; 1.89 PERFORM "add_vote_delegations"("issue_id_p"); 1.90 + -- finish overriding protection triggers (mandatory, as pids may be reused): 1.91 + DELETE FROM "internal_session_store"; 1.92 -- materialize battle_view: 1.93 -- NOTE: "closed" column of issue must be set at this point 1.94 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 2.2 +++ b/update/core-update.v2.2.1-v2.2.2.sql Fri Mar 22 22:37:59 2013 +0100 2.3 @@ -0,0 +1,142 @@ 2.4 +BEGIN; 2.5 + 2.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 2.7 + SELECT * FROM (VALUES ('2.2.2', 2, 2, 2)) 2.8 + AS "subquery"("string", "major", "minor", "revision"); 2.9 + 2.10 +CREATE TABLE "internal_session_store" ( 2.11 + PRIMARY KEY ("backend_pid", "key"), 2.12 + "backend_pid" INT4, 2.13 + "key" TEXT, 2.14 + "value" TEXT NOT NULL ); 2.15 + 2.16 +COMMENT ON TABLE "internal_session_store" IS 'Table to store session variables; shall be emptied before a transaction is committed'; 2.17 + 2.18 +COMMENT ON COLUMN "internal_session_store"."backend_pid" IS 'Value returned by function pg_backend_pid()'; 2.19 + 2.20 +CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"() 2.21 + RETURNS TRIGGER 2.22 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.23 + DECLARE 2.24 + "issue_id_v" "issue"."id"%TYPE; 2.25 + "issue_row" "issue"%ROWTYPE; 2.26 + BEGIN 2.27 + IF EXISTS ( 2.28 + SELECT NULL FROM "internal_session_store" 2.29 + WHERE "backend_pid" = pg_backend_pid() 2.30 + AND "key" = 'override_protection_triggers' 2.31 + AND "value" = TRUE::TEXT 2.32 + ) THEN 2.33 + RETURN NULL; 2.34 + END IF; 2.35 + IF TG_OP = 'DELETE' THEN 2.36 + "issue_id_v" := OLD."issue_id"; 2.37 + ELSE 2.38 + "issue_id_v" := NEW."issue_id"; 2.39 + END IF; 2.40 + SELECT INTO "issue_row" * FROM "issue" 2.41 + WHERE "id" = "issue_id_v" FOR SHARE; 2.42 + IF ( 2.43 + "issue_row"."closed" NOTNULL OR ( 2.44 + "issue_row"."state" = 'voting' AND 2.45 + "issue_row"."phase_finished" NOTNULL 2.46 + ) 2.47 + ) THEN 2.48 + IF 2.49 + TG_RELID = 'direct_voter'::regclass AND 2.50 + TG_OP = 'UPDATE' 2.51 + THEN 2.52 + IF 2.53 + OLD."issue_id" = NEW."issue_id" AND 2.54 + OLD."member_id" = NEW."member_id" AND 2.55 + OLD."weight" = NEW."weight" 2.56 + THEN 2.57 + RETURN NULL; -- allows changing of voter comment 2.58 + END IF; 2.59 + END IF; 2.60 + RAISE EXCEPTION 'Tried to modify data after voting has been closed.'; 2.61 + END IF; 2.62 + RETURN NULL; 2.63 + END; 2.64 + $$; 2.65 + 2.66 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 2.67 + RETURNS VOID 2.68 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.69 + DECLARE 2.70 + "area_id_v" "area"."id"%TYPE; 2.71 + "unit_id_v" "unit"."id"%TYPE; 2.72 + "member_id_v" "member"."id"%TYPE; 2.73 + BEGIN 2.74 + PERFORM "require_transaction_isolation"(); 2.75 + SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 2.76 + SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 2.77 + -- override protection triggers: 2.78 + DELETE FROM "internal_session_store"; 2.79 + INSERT INTO "internal_session_store" ("backend_pid", "key", "value") 2.80 + VALUES (pg_backend_pid(), 'override_protection_triggers', TRUE::TEXT); 2.81 + -- delete timestamp of voting comment: 2.82 + UPDATE "direct_voter" SET "comment_changed" = NULL 2.83 + WHERE "issue_id" = "issue_id_p"; 2.84 + -- delete delegating votes (in cases of manual reset of issue state): 2.85 + DELETE FROM "delegating_voter" 2.86 + WHERE "issue_id" = "issue_id_p"; 2.87 + -- delete votes from non-privileged voters: 2.88 + DELETE FROM "direct_voter" 2.89 + USING ( 2.90 + SELECT 2.91 + "direct_voter"."member_id" 2.92 + FROM "direct_voter" 2.93 + JOIN "member" ON "direct_voter"."member_id" = "member"."id" 2.94 + LEFT JOIN "privilege" 2.95 + ON "privilege"."unit_id" = "unit_id_v" 2.96 + AND "privilege"."member_id" = "direct_voter"."member_id" 2.97 + WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 2.98 + "member"."active" = FALSE OR 2.99 + "privilege"."voting_right" ISNULL OR 2.100 + "privilege"."voting_right" = FALSE 2.101 + ) 2.102 + ) AS "subquery" 2.103 + WHERE "direct_voter"."issue_id" = "issue_id_p" 2.104 + AND "direct_voter"."member_id" = "subquery"."member_id"; 2.105 + -- consider delegations: 2.106 + UPDATE "direct_voter" SET "weight" = 1 2.107 + WHERE "issue_id" = "issue_id_p"; 2.108 + PERFORM "add_vote_delegations"("issue_id_p"); 2.109 + -- finish overriding protection triggers (mandatory, as pids may be reused): 2.110 + DELETE FROM "internal_session_store"; 2.111 + -- materialize battle_view: 2.112 + -- NOTE: "closed" column of issue must be set at this point 2.113 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 2.114 + INSERT INTO "battle" ( 2.115 + "issue_id", 2.116 + "winning_initiative_id", "losing_initiative_id", 2.117 + "count" 2.118 + ) SELECT 2.119 + "issue_id", 2.120 + "winning_initiative_id", "losing_initiative_id", 2.121 + "count" 2.122 + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 2.123 + -- set voter count: 2.124 + UPDATE "issue" SET 2.125 + "voter_count" = ( 2.126 + SELECT coalesce(sum("weight"), 0) 2.127 + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 2.128 + ) 2.129 + WHERE "id" = "issue_id_p"; 2.130 + -- copy "positive_votes" and "negative_votes" from "battle" table: 2.131 + UPDATE "initiative" SET 2.132 + "positive_votes" = "battle_win"."count", 2.133 + "negative_votes" = "battle_lose"."count" 2.134 + FROM "battle" AS "battle_win", "battle" AS "battle_lose" 2.135 + WHERE 2.136 + "battle_win"."issue_id" = "issue_id_p" AND 2.137 + "battle_win"."winning_initiative_id" = "initiative"."id" AND 2.138 + "battle_win"."losing_initiative_id" ISNULL AND 2.139 + "battle_lose"."issue_id" = "issue_id_p" AND 2.140 + "battle_lose"."losing_initiative_id" = "initiative"."id" AND 2.141 + "battle_lose"."winning_initiative_id" ISNULL; 2.142 + END; 2.143 + $$; 2.144 + 2.145 +COMMIT;