db.migration.postgres.V1_022__GenerateId.sql Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of nussknacker-designer_2.13 Show documentation
nussknacker-designer
There is a newer version: 1.17.0
Show newest version
-- get rid of inconsistencies
DELETE FROM "process_deployment_info"
 WHERE "process_id" NOT IN (SELECT "id" FROM "processes");

-- add PK-to-be column
ALTER TABLE "processes" ADD COLUMN "new_id" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1 INCREMENT BY 1);

-- add new columns in referenced tables
ALTER TABLE "tags" ADD COLUMN "new_id" INTEGER;
ALTER TABLE "process_attachments" ADD COLUMN "new_id" INTEGER;
ALTER TABLE "process_comments" ADD COLUMN "new_id" INTEGER;
ALTER TABLE "process_deployment_info" ADD COLUMN "new_id" INTEGER;
ALTER TABLE "process_versions" ADD COLUMN "new_id" INTEGER;

-- set corresponding values for key-to-be
UPDATE "tags" t SET "new_id" = (SELECT p."new_id" FROM "processes" p WHERE p."id" = t."process_id");
UPDATE "process_attachments" pa SET "new_id" = (SELECT p."new_id" FROM "processes" p WHERE p."id" = pa."process_id");
UPDATE "process_comments" pc SET "new_id" = (SELECT p."new_id" FROM "processes" p WHERE p."id" = pc."process_id");
UPDATE "process_deployment_info" pdi SET "new_id" = (SELECT p."new_id" FROM "processes" p WHERE p."id" = pdi."process_id");
UPDATE "process_versions" pv SET "new_id" = (SELECT p."new_id" FROM "processes" p WHERE p."id" = pv."process_id");

-- drop old constraints for a moment
ALTER TABLE "tags" DROP CONSTRAINT "tag-process-fk";
ALTER TABLE "process_attachments" DROP CONSTRAINT "proc_attach_proc_version_fk";
ALTER TABLE "process_comments" DROP CONSTRAINT "proc_comments_proc_version_fk";
ALTER TABLE "process_deployment_info" DROP CONSTRAINT "proc_ver_in_deployed_proc_fk";
ALTER TABLE "process_versions" DROP CONSTRAINT "version-process-fk";

ALTER TABLE "tags" DROP CONSTRAINT "pk_tag";
ALTER TABLE "processes" DROP CONSTRAINT "processes_pkey";
ALTER TABLE "process_versions" DROP CONSTRAINT "pk_process_version";
ALTER TABLE "process_deployment_info" DROP CONSTRAINT "pk_process_deployment_info";

-- drop old columns
ALTER TABLE "processes" DROP COLUMN "id";
ALTER TABLE "tags" DROP COLUMN "process_id";
ALTER TABLE "process_versions" DROP COLUMN "process_id";
ALTER TABLE "process_comments" DROP COLUMN "process_id";
ALTER TABLE "process_deployment_info" DROP COLUMN "process_id";
ALTER TABLE "process_attachments" DROP COLUMN "process_id";

-- rename new columns
ALTER TABLE "processes" RENAME COLUMN "new_id" TO "id";
ALTER TABLE "tags" RENAME COLUMN "new_id" TO "process_id";
ALTER TABLE "process_versions" RENAME COLUMN "new_id" TO "process_id";
ALTER TABLE "process_comments" RENAME COLUMN "new_id" TO "process_id";
ALTER TABLE "process_deployment_info" RENAME COLUMN "new_id" TO "process_id";
ALTER TABLE "process_attachments" RENAME COLUMN "new_id" TO "process_id";

-- restore constraints
ALTER TABLE "processes" ADD PRIMARY KEY ("id");
ALTER TABLE "tags" ADD CONSTRAINT "tag-process-fk" FOREIGN KEY ("process_id") REFERENCES "processes" ("id") ON DELETE CASCADE;
ALTER TABLE "tags" ADD CONSTRAINT "pk_tag" PRIMARY KEY ("name", "process_id");
ALTER TABLE "process_versions" ADD CONSTRAINT "version-process-fk" FOREIGN KEY ("process_id") REFERENCES "processes" ("id") ON DELETE CASCADE;
ALTER TABLE "process_versions" ADD CONSTRAINT "pk_process_version" PRIMARY KEY ("process_id", "id");
ALTER TABLE "process_comments" ADD CONSTRAINT "proc_comments_proc_version_fk" FOREIGN KEY ("process_id", "process_version_id") REFERENCES "process_versions" ("process_id", "id") ON DELETE CASCADE;
ALTER TABLE "process_deployment_info" ADD CONSTRAINT "pk_process_deployment_info" PRIMARY KEY ("process_id", "deployment_action", "environment", "deploy_at");
ALTER TABLE "process_deployment_info" ADD CONSTRAINT "proc_ver_in_deployed_proc_fk" FOREIGN KEY ("process_id", "process_version_id") REFERENCES "process_versions" ("process_id", "id") ON DELETE CASCADE;
ALTER TABLE "process_attachments" ADD CONSTRAINT "proc_attach_proc_version_fk" FOREIGN KEY ("process_id", "process_version_id") REFERENCES "process_versions" ("process_id", "id") ON DELETE CASCADE;