112 lines
4.6 KiB
SQL
112 lines
4.6 KiB
SQL
-- Drop old foreign keys linking components and pieces directly to machines
|
|
ALTER TABLE "composants" DROP CONSTRAINT IF EXISTS "composants_machineId_fkey";
|
|
ALTER TABLE "composants" DROP CONSTRAINT IF EXISTS "composants_parentComposantId_fkey";
|
|
ALTER TABLE "composants" DROP CONSTRAINT IF EXISTS "composants_typeMachineComponentRequirementId_fkey";
|
|
ALTER TABLE "pieces" DROP CONSTRAINT IF EXISTS "pieces_machineId_fkey";
|
|
ALTER TABLE "pieces" DROP CONSTRAINT IF EXISTS "pieces_composantId_fkey";
|
|
ALTER TABLE "pieces" DROP CONSTRAINT IF EXISTS "pieces_typeMachinePieceRequirementId_fkey";
|
|
|
|
-- Create new link tables to associate machines with components and pieces
|
|
CREATE TABLE "machine_component_links" (
|
|
"id" TEXT NOT NULL,
|
|
"machineId" TEXT NOT NULL,
|
|
"composantId" TEXT NOT NULL,
|
|
"parentLinkId" TEXT,
|
|
"typeMachineComponentRequirementId" TEXT,
|
|
"nameOverride" TEXT,
|
|
"referenceOverride" TEXT,
|
|
"prixOverride" DECIMAL(10,2),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "machine_component_links_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "machine_piece_links" (
|
|
"id" TEXT NOT NULL,
|
|
"machineId" TEXT NOT NULL,
|
|
"pieceId" TEXT NOT NULL,
|
|
"parentLinkId" TEXT,
|
|
"typeMachinePieceRequirementId" TEXT,
|
|
"nameOverride" TEXT,
|
|
"referenceOverride" TEXT,
|
|
"prixOverride" DECIMAL(10,2),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "machine_piece_links_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- Seed the new link tables using the existing component and piece assignments
|
|
INSERT INTO "machine_component_links" (
|
|
"id",
|
|
"machineId",
|
|
"composantId",
|
|
"typeMachineComponentRequirementId",
|
|
"createdAt",
|
|
"updatedAt"
|
|
)
|
|
SELECT
|
|
"machineId" || '_' || "id" AS "id",
|
|
"machineId",
|
|
"id" AS "composantId",
|
|
"typeMachineComponentRequirementId",
|
|
"createdAt",
|
|
"updatedAt"
|
|
FROM "composants"
|
|
WHERE "machineId" IS NOT NULL;
|
|
|
|
UPDATE "machine_component_links" AS link
|
|
SET "parentLinkId" = link."machineId" || '_' || c."parentComposantId"
|
|
FROM "composants" AS c
|
|
WHERE link."composantId" = c."id"
|
|
AND c."parentComposantId" IS NOT NULL;
|
|
|
|
INSERT INTO "machine_piece_links" (
|
|
"id",
|
|
"machineId",
|
|
"pieceId",
|
|
"parentLinkId",
|
|
"typeMachinePieceRequirementId",
|
|
"createdAt",
|
|
"updatedAt"
|
|
)
|
|
SELECT
|
|
"machineId" || '_' || "id" AS "id",
|
|
"machineId",
|
|
"id" AS "pieceId",
|
|
CASE WHEN "composantId" IS NOT NULL THEN "machineId" || '_' || "composantId" ELSE NULL END,
|
|
"typeMachinePieceRequirementId",
|
|
"createdAt",
|
|
"updatedAt"
|
|
FROM "pieces"
|
|
WHERE "machineId" IS NOT NULL;
|
|
|
|
-- Remove the obsolete columns now that the data has been migrated
|
|
ALTER TABLE "composants" DROP COLUMN IF EXISTS "machineId";
|
|
ALTER TABLE "composants" DROP COLUMN IF EXISTS "parentComposantId";
|
|
ALTER TABLE "composants" DROP COLUMN IF EXISTS "typeMachineComponentRequirementId";
|
|
|
|
ALTER TABLE "pieces" DROP COLUMN IF EXISTS "machineId";
|
|
ALTER TABLE "pieces" DROP COLUMN IF EXISTS "composantId";
|
|
ALTER TABLE "pieces" DROP COLUMN IF EXISTS "typeMachinePieceRequirementId";
|
|
|
|
-- Add the new foreign key constraints for the link tables
|
|
ALTER TABLE "machine_component_links"
|
|
ADD CONSTRAINT "machine_component_links_machineId_fkey"
|
|
FOREIGN KEY ("machineId") REFERENCES "machines"("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
ADD CONSTRAINT "machine_component_links_composantId_fkey"
|
|
FOREIGN KEY ("composantId") REFERENCES "composants"("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
ADD CONSTRAINT "machine_component_links_parentLinkId_fkey"
|
|
FOREIGN KEY ("parentLinkId") REFERENCES "machine_component_links"("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
ADD CONSTRAINT "machine_component_links_typeMachineComponentRequirementId_fkey"
|
|
FOREIGN KEY ("typeMachineComponentRequirementId") REFERENCES "type_machine_component_requirements"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE "machine_piece_links"
|
|
ADD CONSTRAINT "machine_piece_links_machineId_fkey"
|
|
FOREIGN KEY ("machineId") REFERENCES "machines"("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
ADD CONSTRAINT "machine_piece_links_pieceId_fkey"
|
|
FOREIGN KEY ("pieceId") REFERENCES "pieces"("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
ADD CONSTRAINT "machine_piece_links_parentLinkId_fkey"
|
|
FOREIGN KEY ("parentLinkId") REFERENCES "machine_component_links"("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
ADD CONSTRAINT "machine_piece_links_typeMachinePieceRequirementId_fkey"
|
|
FOREIGN KEY ("typeMachinePieceRequirementId") REFERENCES "type_machine_piece_requirements"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|