matcher/prisma/migrations/20230823125530_init/migration.sql

69 lines
2.0 KiB
MySQL
Raw Permalink Normal View History

2023-08-24 12:08:49 +00:00
-- CreateExtension
CREATE EXTENSION IF NOT EXISTS "postgis";
-- Required to use postgis extension :
-- set the search_path to both public (where is postgis) AND the current schema
SET search_path TO matcher, public;
-- CreateEnum
CREATE TYPE "Frequency" AS ENUM ('PUNCTUAL', 'RECURRENT');
-- CreateTable
CREATE TABLE "ad" (
"uuid" UUID NOT NULL,
"driver" BOOLEAN NOT NULL,
"passenger" BOOLEAN NOT NULL,
"frequency" "Frequency" NOT NULL,
"fromDate" DATE NOT NULL,
"toDate" DATE NOT NULL,
"seatsProposed" SMALLINT NOT NULL,
"seatsRequested" SMALLINT NOT NULL,
"strict" BOOLEAN NOT NULL,
"driverDuration" INTEGER,
"driverDistance" INTEGER,
"passengerDuration" INTEGER,
"passengerDistance" INTEGER,
"waypoints" geography(LINESTRING),
"direction" geography(LINESTRING),
"fwdAzimuth" INTEGER NOT NULL,
"backAzimuth" INTEGER NOT NULL,
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT "ad_pkey" PRIMARY KEY ("uuid")
);
-- CreateTable
CREATE TABLE "schedule_item" (
"uuid" UUID NOT NULL,
"adUuid" UUID NOT NULL,
"day" INTEGER NOT NULL,
"time" TIME(4) NOT NULL,
"margin" INTEGER NOT NULL,
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT "schedule_item_pkey" PRIMARY KEY ("uuid")
);
-- CreateIndex
CREATE INDEX "ad_driver_idx" ON "ad"("driver");
-- CreateIndex
CREATE INDEX "ad_passenger_idx" ON "ad"("passenger");
-- CreateIndex
CREATE INDEX "ad_fromDate_idx" ON "ad"("fromDate");
-- CreateIndex
CREATE INDEX "ad_toDate_idx" ON "ad"("toDate");
-- CreateIndex
CREATE INDEX "ad_fwdAzimuth_idx" ON "ad"("fwdAzimuth");
-- CreateIndex
CREATE INDEX "direction_idx" ON "ad" USING GIST ("direction");
-- AddForeignKey
ALTER TABLE "schedule_item" ADD CONSTRAINT "schedule_item_adUuid_fkey" FOREIGN KEY ("adUuid") REFERENCES "ad"("uuid") ON DELETE CASCADE ON UPDATE CASCADE;