# HG changeset patch # User jbe # Date 1363988279 -3600 # Node ID 1c991490f07570ea457c206a9ec44c80516d3726 # Parent eda6362598465042609452aae9ce60745234dd28 Fixed an error, which occurred when deleting votes from voters, which lost their voting right during voting phase diff -r eda636259846 -r 1c991490f075 core.sql --- a/core.sql Mon Mar 18 14:48:41 2013 +0100 +++ b/core.sql Fri Mar 22 22:37:59 2013 +0100 @@ -7,7 +7,7 @@ BEGIN; CREATE VIEW "liquid_feedback_version" AS - SELECT * FROM (VALUES ('2.2.1', 2, 2, 1)) + SELECT * FROM (VALUES ('2.2.2', 2, 2, 2)) AS "subquery"("string", "major", "minor", "revision"); @@ -54,6 +54,17 @@ ------------------------- +CREATE TABLE "internal_session_store" ( + PRIMARY KEY ("backend_pid", "key"), + "backend_pid" INT4, + "key" TEXT, + "value" TEXT NOT NULL ); + +COMMENT ON TABLE "internal_session_store" IS 'Table to store session variables; shall be emptied before a transaction is committed'; + +COMMENT ON COLUMN "internal_session_store"."backend_pid" IS 'Value returned by function pg_backend_pid()'; + + CREATE TABLE "system_setting" ( "member_ttl" INTERVAL ); CREATE UNIQUE INDEX "system_setting_singleton_idx" ON "system_setting" ((1)); @@ -1587,6 +1598,14 @@ "issue_id_v" "issue"."id"%TYPE; "issue_row" "issue"%ROWTYPE; BEGIN + IF EXISTS ( + SELECT NULL FROM "internal_session_store" + WHERE "backend_pid" = pg_backend_pid() + AND "key" = 'override_protection_triggers' + AND "value" = TRUE::TEXT + ) THEN + RETURN NULL; + END IF; IF TG_OP = 'DELETE' THEN "issue_id_v" := OLD."issue_id"; ELSE @@ -1594,7 +1613,12 @@ END IF; SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_v" FOR SHARE; - IF "issue_row"."closed" NOTNULL THEN + IF ( + "issue_row"."closed" NOTNULL OR ( + "issue_row"."state" = 'voting' AND + "issue_row"."phase_finished" NOTNULL + ) + ) THEN IF TG_RELID = 'direct_voter'::regclass AND TG_OP = 'UPDATE' @@ -1607,14 +1631,7 @@ RETURN NULL; -- allows changing of voter comment END IF; END IF; - RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.'; - ELSIF - "issue_row"."state" = 'voting' AND - "issue_row"."phase_finished" NOTNULL - THEN - IF TG_RELID = 'vote'::regclass THEN - RAISE EXCEPTION 'Tried to modify data after voting has been closed.'; - END IF; + RAISE EXCEPTION 'Tried to modify data after voting has been closed.'; END IF; RETURN NULL; END; @@ -3653,6 +3670,10 @@ PERFORM "require_transaction_isolation"(); SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; + -- override protection triggers: + DELETE FROM "internal_session_store"; + INSERT INTO "internal_session_store" ("backend_pid", "key", "value") + VALUES (pg_backend_pid(), 'override_protection_triggers', TRUE::TEXT); -- delete timestamp of voting comment: UPDATE "direct_voter" SET "comment_changed" = NULL WHERE "issue_id" = "issue_id_p"; @@ -3681,6 +3702,8 @@ UPDATE "direct_voter" SET "weight" = 1 WHERE "issue_id" = "issue_id_p"; PERFORM "add_vote_delegations"("issue_id_p"); + -- finish overriding protection triggers (mandatory, as pids may be reused): + DELETE FROM "internal_session_store"; -- materialize battle_view: -- NOTE: "closed" column of issue must be set at this point DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; diff -r eda636259846 -r 1c991490f075 update/core-update.v2.2.1-v2.2.2.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/update/core-update.v2.2.1-v2.2.2.sql Fri Mar 22 22:37:59 2013 +0100 @@ -0,0 +1,142 @@ +BEGIN; + +CREATE OR REPLACE VIEW "liquid_feedback_version" AS + SELECT * FROM (VALUES ('2.2.2', 2, 2, 2)) + AS "subquery"("string", "major", "minor", "revision"); + +CREATE TABLE "internal_session_store" ( + PRIMARY KEY ("backend_pid", "key"), + "backend_pid" INT4, + "key" TEXT, + "value" TEXT NOT NULL ); + +COMMENT ON TABLE "internal_session_store" IS 'Table to store session variables; shall be emptied before a transaction is committed'; + +COMMENT ON COLUMN "internal_session_store"."backend_pid" IS 'Value returned by function pg_backend_pid()'; + +CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"() + RETURNS TRIGGER + LANGUAGE 'plpgsql' VOLATILE AS $$ + DECLARE + "issue_id_v" "issue"."id"%TYPE; + "issue_row" "issue"%ROWTYPE; + BEGIN + IF EXISTS ( + SELECT NULL FROM "internal_session_store" + WHERE "backend_pid" = pg_backend_pid() + AND "key" = 'override_protection_triggers' + AND "value" = TRUE::TEXT + ) THEN + RETURN NULL; + END IF; + IF TG_OP = 'DELETE' THEN + "issue_id_v" := OLD."issue_id"; + ELSE + "issue_id_v" := NEW."issue_id"; + END IF; + SELECT INTO "issue_row" * FROM "issue" + WHERE "id" = "issue_id_v" FOR SHARE; + IF ( + "issue_row"."closed" NOTNULL OR ( + "issue_row"."state" = 'voting' AND + "issue_row"."phase_finished" NOTNULL + ) + ) THEN + IF + TG_RELID = 'direct_voter'::regclass AND + TG_OP = 'UPDATE' + THEN + IF + OLD."issue_id" = NEW."issue_id" AND + OLD."member_id" = NEW."member_id" AND + OLD."weight" = NEW."weight" + THEN + RETURN NULL; -- allows changing of voter comment + END IF; + END IF; + RAISE EXCEPTION 'Tried to modify data after voting has been closed.'; + END IF; + RETURN NULL; + END; + $$; + +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) + RETURNS VOID + LANGUAGE 'plpgsql' VOLATILE AS $$ + DECLARE + "area_id_v" "area"."id"%TYPE; + "unit_id_v" "unit"."id"%TYPE; + "member_id_v" "member"."id"%TYPE; + BEGIN + PERFORM "require_transaction_isolation"(); + SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; + SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; + -- override protection triggers: + DELETE FROM "internal_session_store"; + INSERT INTO "internal_session_store" ("backend_pid", "key", "value") + VALUES (pg_backend_pid(), 'override_protection_triggers', TRUE::TEXT); + -- delete timestamp of voting comment: + UPDATE "direct_voter" SET "comment_changed" = NULL + WHERE "issue_id" = "issue_id_p"; + -- delete delegating votes (in cases of manual reset of issue state): + DELETE FROM "delegating_voter" + WHERE "issue_id" = "issue_id_p"; + -- delete votes from non-privileged voters: + DELETE FROM "direct_voter" + USING ( + SELECT + "direct_voter"."member_id" + FROM "direct_voter" + JOIN "member" ON "direct_voter"."member_id" = "member"."id" + LEFT JOIN "privilege" + ON "privilege"."unit_id" = "unit_id_v" + AND "privilege"."member_id" = "direct_voter"."member_id" + WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( + "member"."active" = FALSE OR + "privilege"."voting_right" ISNULL OR + "privilege"."voting_right" = FALSE + ) + ) AS "subquery" + WHERE "direct_voter"."issue_id" = "issue_id_p" + AND "direct_voter"."member_id" = "subquery"."member_id"; + -- consider delegations: + UPDATE "direct_voter" SET "weight" = 1 + WHERE "issue_id" = "issue_id_p"; + PERFORM "add_vote_delegations"("issue_id_p"); + -- finish overriding protection triggers (mandatory, as pids may be reused): + DELETE FROM "internal_session_store"; + -- materialize battle_view: + -- NOTE: "closed" column of issue must be set at this point + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; + INSERT INTO "battle" ( + "issue_id", + "winning_initiative_id", "losing_initiative_id", + "count" + ) SELECT + "issue_id", + "winning_initiative_id", "losing_initiative_id", + "count" + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; + -- set voter count: + UPDATE "issue" SET + "voter_count" = ( + SELECT coalesce(sum("weight"), 0) + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" + ) + WHERE "id" = "issue_id_p"; + -- copy "positive_votes" and "negative_votes" from "battle" table: + UPDATE "initiative" SET + "positive_votes" = "battle_win"."count", + "negative_votes" = "battle_lose"."count" + FROM "battle" AS "battle_win", "battle" AS "battle_lose" + WHERE + "battle_win"."issue_id" = "issue_id_p" AND + "battle_win"."winning_initiative_id" = "initiative"."id" AND + "battle_win"."losing_initiative_id" ISNULL AND + "battle_lose"."issue_id" = "issue_id_p" AND + "battle_lose"."losing_initiative_id" = "initiative"."id" AND + "battle_lose"."winning_initiative_id" ISNULL; + END; + $$; + +COMMIT;