209 lines
7.2 KiB
SQL
209 lines
7.2 KiB
SQL
-- CreateTable
|
|
CREATE TABLE "sites" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "sites_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "type_machines" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"category" TEXT,
|
|
"maintenanceFrequency" TEXT,
|
|
"components" JSONB,
|
|
"criticalParts" JSONB,
|
|
"specifications" JSONB,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "type_machines_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "type_composants" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "type_composants_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "type_pieces" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "type_pieces_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "machines" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"reference" TEXT,
|
|
"prestataire" TEXT,
|
|
"prix" DECIMAL(10,2),
|
|
"emplacement" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
"siteId" TEXT NOT NULL,
|
|
"typeMachineId" TEXT,
|
|
|
|
CONSTRAINT "machines_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "composants" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"reference" TEXT,
|
|
"prestataire" TEXT,
|
|
"prix" DECIMAL(10,2),
|
|
"emplacement" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
"machineId" TEXT,
|
|
"parentComposantId" TEXT,
|
|
"typeComposantId" TEXT,
|
|
|
|
CONSTRAINT "composants_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "pieces" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"reference" TEXT,
|
|
"prestataire" TEXT,
|
|
"prix" DECIMAL(10,2),
|
|
"emplacement" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
"machineId" TEXT,
|
|
"composantId" TEXT,
|
|
"typePieceId" TEXT,
|
|
|
|
CONSTRAINT "pieces_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "documents" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"filename" TEXT NOT NULL,
|
|
"path" TEXT NOT NULL,
|
|
"mimeType" TEXT NOT NULL,
|
|
"size" INTEGER NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
"machineId" TEXT,
|
|
"composantId" TEXT,
|
|
"pieceId" TEXT,
|
|
|
|
CONSTRAINT "documents_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "custom_fields" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"type" TEXT NOT NULL,
|
|
"required" BOOLEAN NOT NULL DEFAULT false,
|
|
"defaultValue" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
"typeMachineId" TEXT,
|
|
"typeComposantId" TEXT,
|
|
"typePieceId" TEXT,
|
|
|
|
CONSTRAINT "custom_fields_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "custom_field_values" (
|
|
"id" TEXT NOT NULL,
|
|
"value" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
"customFieldId" TEXT NOT NULL,
|
|
"machineId" TEXT,
|
|
"composantId" TEXT,
|
|
"pieceId" TEXT,
|
|
|
|
CONSTRAINT "custom_field_values_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "type_machines_name_key" ON "type_machines"("name");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "type_composants_name_key" ON "type_composants"("name");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "type_pieces_name_key" ON "type_pieces"("name");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "machines" ADD CONSTRAINT "machines_siteId_fkey" FOREIGN KEY ("siteId") REFERENCES "sites"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "machines" ADD CONSTRAINT "machines_typeMachineId_fkey" FOREIGN KEY ("typeMachineId") REFERENCES "type_machines"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "composants" ADD CONSTRAINT "composants_machineId_fkey" FOREIGN KEY ("machineId") REFERENCES "machines"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "composants" ADD CONSTRAINT "composants_parentComposantId_fkey" FOREIGN KEY ("parentComposantId") REFERENCES "composants"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "composants" ADD CONSTRAINT "composants_typeComposantId_fkey" FOREIGN KEY ("typeComposantId") REFERENCES "type_composants"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "pieces" ADD CONSTRAINT "pieces_machineId_fkey" FOREIGN KEY ("machineId") REFERENCES "machines"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "pieces" ADD CONSTRAINT "pieces_composantId_fkey" FOREIGN KEY ("composantId") REFERENCES "composants"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "pieces" ADD CONSTRAINT "pieces_typePieceId_fkey" FOREIGN KEY ("typePieceId") REFERENCES "type_pieces"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "documents" ADD CONSTRAINT "documents_machineId_fkey" FOREIGN KEY ("machineId") REFERENCES "machines"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "documents" ADD CONSTRAINT "documents_composantId_fkey" FOREIGN KEY ("composantId") REFERENCES "composants"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "documents" ADD CONSTRAINT "documents_pieceId_fkey" FOREIGN KEY ("pieceId") REFERENCES "pieces"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "custom_fields" ADD CONSTRAINT "custom_fields_typeMachineId_fkey" FOREIGN KEY ("typeMachineId") REFERENCES "type_machines"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "custom_fields" ADD CONSTRAINT "custom_fields_typeComposantId_fkey" FOREIGN KEY ("typeComposantId") REFERENCES "type_composants"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "custom_fields" ADD CONSTRAINT "custom_fields_typePieceId_fkey" FOREIGN KEY ("typePieceId") REFERENCES "type_pieces"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "custom_field_values" ADD CONSTRAINT "custom_field_values_customFieldId_fkey" FOREIGN KEY ("customFieldId") REFERENCES "custom_fields"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "custom_field_values" ADD CONSTRAINT "custom_field_values_machineId_fkey" FOREIGN KEY ("machineId") REFERENCES "machines"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "custom_field_values" ADD CONSTRAINT "custom_field_values_composantId_fkey" FOREIGN KEY ("composantId") REFERENCES "composants"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "custom_field_values" ADD CONSTRAINT "custom_field_values_pieceId_fkey" FOREIGN KEY ("pieceId") REFERENCES "pieces"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|