After reading the sqitch-rework documentation and the Altering Table with Sqitch Rework command stackoverflow question, I am aware that sqitch-rework was intended for idempotent changes.
But why?
It seems that my use of sqitch-rework works perfectly fine with a non-idempotent change.
Here is how I used it with PostgreSQL:
original deploy:
-- Deploy myproject:database/facility/schema/public/table/mytable to pg
-- requires: database/facility/schema/public
SET ROLE :"owner_role";
BEGIN;
CREATE TABLE public."MyTable"(
"Date" timestamp without time zone NOT NULL
);
COMMIT;
original revert:
-- Revert myproject:database/facility/schema/public/table/mytable from pg
SET ROLE :"owner_role";
BEGIN;
DROP TABLE public."MyTable";
COMMIT;
reworked deploy:
-- Deploy myproject:database/facility/schema/public/table/mytable to pg
-- requires: database/facility/schema/public
SET ROLE :"owner_role";
BEGIN;
LOCK TABLE public."MyTable" IN ACCESS EXCLUSIVE MODE;
ALTER TABLE public."MyTable" RENAME COLUMN "Date" TO "Complete time";
ALTER TABLE public."MyTable" ADD COLUMN "Start time" timestamp without time zone;
UPDATE public."MyTable" SET "Start time" = "Complete time";
ALTER TABLE public."MyTable" ALTER COLUMN "Start time" SET NOT NULL;
COMMIT;
reworked revert:
-- Revert myproject:database/facility/schema/public/table/mytable from pg
SET ROLE :"owner_role";
BEGIN;
LOCK TABLE public."MyTable" IN ACCESS EXCLUSIVE MODE;
ALTER TABLE public."MyTable" DROP COLUMN "Start time";
ALTER TABLE public."MyTable" RENAME COLUMN "Complete time" TO "Date";
COMMIT;
Am I not understanding something? Isn't this a valid use of sqitch-rework?
