47 lines
1.8 KiB
SQL
47 lines
1.8 KiB
SQL
-- AlterTable
|
|
ALTER TABLE "public"."Hunt" ADD COLUMN "trackDistance" BOOLEAN NOT NULL DEFAULT false;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public"."TrackPoint" (
|
|
"id" SERIAL NOT NULL,
|
|
"lat" DECIMAL(10,8) NOT NULL,
|
|
"lon" DECIMAL(10,8) NOT NULL,
|
|
"accuracy" DOUBLE PRECISION,
|
|
"recordedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
"createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"userId" INTEGER NOT NULL,
|
|
"huntId" INTEGER NOT NULL,
|
|
|
|
CONSTRAINT "TrackPoint_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public"."UserTrack" (
|
|
"id" SERIAL NOT NULL,
|
|
"distanceMeters" INTEGER NOT NULL DEFAULT 0,
|
|
"lastFixAt" TIMESTAMPTZ(3),
|
|
"updatedAt" TIMESTAMPTZ(3) NOT NULL,
|
|
"userId" INTEGER NOT NULL,
|
|
"huntId" INTEGER NOT NULL,
|
|
|
|
CONSTRAINT "UserTrack_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "TrackPoint_userId_huntId_recordedAt_idx" ON "public"."TrackPoint"("userId", "huntId", "recordedAt");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "UserTrack_userId_huntId_key" ON "public"."UserTrack"("userId", "huntId");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."TrackPoint" ADD CONSTRAINT "TrackPoint_userId_fkey" FOREIGN KEY ("userId") REFERENCES "public"."User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."TrackPoint" ADD CONSTRAINT "TrackPoint_huntId_fkey" FOREIGN KEY ("huntId") REFERENCES "public"."Hunt"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."UserTrack" ADD CONSTRAINT "UserTrack_userId_fkey" FOREIGN KEY ("userId") REFERENCES "public"."User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."UserTrack" ADD CONSTRAINT "UserTrack_huntId_fkey" FOREIGN KEY ("huntId") REFERENCES "public"."Hunt"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|