40 lines
1.3 KiB
SQL
40 lines
1.3 KiB
SQL
-- CreateTable
|
|
CREATE TABLE "Site" (
|
|
"id" SERIAL NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"location" TEXT NOT NULL,
|
|
"inverterProvider" TEXT NOT NULL,
|
|
"emergencyContact" TEXT NOT NULL,
|
|
"lastSyncTimestamp" TIMESTAMP(3) NOT NULL,
|
|
"theoreticalMaxGeneration_kWh" DOUBLE PRECISION NOT NULL,
|
|
|
|
CONSTRAINT "Site_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "EnergyData" (
|
|
"id" SERIAL NOT NULL,
|
|
"type" TEXT NOT NULL,
|
|
"timestamp" TIMESTAMP(3) NOT NULL,
|
|
"value" DOUBLE PRECISION NOT NULL,
|
|
"consumptionSiteId" INTEGER,
|
|
"generationSiteId" INTEGER,
|
|
|
|
CONSTRAINT "EnergyData_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Site_name_key" ON "Site"("name");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "EnergyData_consumptionSiteId_idx" ON "EnergyData"("consumptionSiteId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "EnergyData_generationSiteId_idx" ON "EnergyData"("generationSiteId");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "EnergyData" ADD CONSTRAINT "EnergyData_consumptionSiteId_fkey" FOREIGN KEY ("consumptionSiteId") REFERENCES "Site"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "EnergyData" ADD CONSTRAINT "EnergyData_generationSiteId_fkey" FOREIGN KEY ("generationSiteId") REFERENCES "Site"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|