149 lines
4.2 KiB
SQL
149 lines
4.2 KiB
SQL
-- CreateTable
|
|
CREATE TABLE "Project" (
|
|
"id" SERIAL NOT NULL,
|
|
"slug" VARCHAR(255) NOT NULL,
|
|
"title" VARCHAR(255) NOT NULL,
|
|
"subtitle" VARCHAR(255),
|
|
"description" TEXT,
|
|
"year" INTEGER NOT NULL,
|
|
"client" VARCHAR(255),
|
|
"role" VARCHAR(255),
|
|
"technologies" JSONB,
|
|
"featuredImage" VARCHAR(500),
|
|
"gallery" JSONB,
|
|
"externalUrl" VARCHAR(500),
|
|
"caseStudyContent" JSONB,
|
|
"displayOrder" INTEGER NOT NULL DEFAULT 0,
|
|
"status" VARCHAR(50) NOT NULL DEFAULT 'draft',
|
|
"publishedAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Project_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Post" (
|
|
"id" SERIAL NOT NULL,
|
|
"slug" VARCHAR(255) NOT NULL,
|
|
"postType" VARCHAR(50) NOT NULL,
|
|
"title" VARCHAR(255),
|
|
"content" JSONB,
|
|
"excerpt" TEXT,
|
|
"linkUrl" VARCHAR(500),
|
|
"linkDescription" TEXT,
|
|
"photoId" INTEGER,
|
|
"albumId" INTEGER,
|
|
"featuredImage" VARCHAR(500),
|
|
"tags" JSONB,
|
|
"status" VARCHAR(50) NOT NULL DEFAULT 'draft',
|
|
"publishedAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Post_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Album" (
|
|
"id" SERIAL NOT NULL,
|
|
"slug" VARCHAR(255) NOT NULL,
|
|
"title" VARCHAR(255) NOT NULL,
|
|
"description" TEXT,
|
|
"date" TIMESTAMP(3),
|
|
"location" VARCHAR(255),
|
|
"coverPhotoId" INTEGER,
|
|
"status" VARCHAR(50) NOT NULL DEFAULT 'draft',
|
|
"showInUniverse" BOOLEAN NOT NULL DEFAULT false,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Album_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Photo" (
|
|
"id" SERIAL NOT NULL,
|
|
"albumId" INTEGER,
|
|
"filename" VARCHAR(255) NOT NULL,
|
|
"url" VARCHAR(500) NOT NULL,
|
|
"thumbnailUrl" VARCHAR(500),
|
|
"width" INTEGER,
|
|
"height" INTEGER,
|
|
"exifData" JSONB,
|
|
"caption" TEXT,
|
|
"displayOrder" INTEGER NOT NULL DEFAULT 0,
|
|
"slug" VARCHAR(255),
|
|
"title" VARCHAR(255),
|
|
"description" TEXT,
|
|
"status" VARCHAR(50) NOT NULL DEFAULT 'draft',
|
|
"publishedAt" TIMESTAMP(3),
|
|
"showInPhotos" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Photo_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Media" (
|
|
"id" SERIAL NOT NULL,
|
|
"filename" VARCHAR(255) NOT NULL,
|
|
"mimeType" VARCHAR(100) NOT NULL,
|
|
"size" INTEGER NOT NULL,
|
|
"url" TEXT NOT NULL,
|
|
"thumbnailUrl" TEXT,
|
|
"width" INTEGER,
|
|
"height" INTEGER,
|
|
"usedIn" JSONB NOT NULL DEFAULT '[]',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Media_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Project_slug_key" ON "Project"("slug");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Project_slug_idx" ON "Project"("slug");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Project_status_idx" ON "Project"("status");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Post_slug_key" ON "Post"("slug");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Post_slug_idx" ON "Post"("slug");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Post_status_idx" ON "Post"("status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Post_postType_idx" ON "Post"("postType");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Album_slug_key" ON "Album"("slug");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Album_slug_idx" ON "Album"("slug");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Album_status_idx" ON "Album"("status");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Photo_slug_key" ON "Photo"("slug");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Photo_slug_idx" ON "Photo"("slug");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Photo_status_idx" ON "Photo"("status");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Post" ADD CONSTRAINT "Post_photoId_fkey" FOREIGN KEY ("photoId") REFERENCES "Photo"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Post" ADD CONSTRAINT "Post_albumId_fkey" FOREIGN KEY ("albumId") REFERENCES "Album"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Photo" ADD CONSTRAINT "Photo_albumId_fkey" FOREIGN KEY ("albumId") REFERENCES "Album"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|