78 lines
2.3 KiB
SQL
78 lines
2.3 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "StageType" AS ENUM ('ROUND_ROBIN', 'SINGLE_ELIM');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Player" (
|
|
"id" SERIAL NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"registeredAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Player_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "AdminUser" (
|
|
"id" SERIAL NOT NULL,
|
|
"username" TEXT NOT NULL,
|
|
"passwordHash" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "AdminUser_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "TournamentStage" (
|
|
"id" SERIAL NOT NULL,
|
|
"type" "StageType" NOT NULL,
|
|
"startedAt" TIMESTAMP(3),
|
|
"endedAt" TIMESTAMP(3),
|
|
|
|
CONSTRAINT "TournamentStage_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Match" (
|
|
"id" SERIAL NOT NULL,
|
|
"stageId" INTEGER NOT NULL,
|
|
"player1Id" INTEGER NOT NULL,
|
|
"player2Id" INTEGER NOT NULL,
|
|
"scheduledAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Match_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Result" (
|
|
"id" SERIAL NOT NULL,
|
|
"matchId" INTEGER NOT NULL,
|
|
"player1Score" INTEGER NOT NULL,
|
|
"player2Score" INTEGER NOT NULL,
|
|
"winnerId" INTEGER NOT NULL,
|
|
|
|
CONSTRAINT "Result_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Player_email_key" ON "Player"("email");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "AdminUser_username_key" ON "AdminUser"("username");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Result_matchId_key" ON "Result"("matchId");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Match" ADD CONSTRAINT "Match_stageId_fkey" FOREIGN KEY ("stageId") REFERENCES "TournamentStage"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Match" ADD CONSTRAINT "Match_player1Id_fkey" FOREIGN KEY ("player1Id") REFERENCES "Player"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Match" ADD CONSTRAINT "Match_player2Id_fkey" FOREIGN KEY ("player2Id") REFERENCES "Player"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Result" ADD CONSTRAINT "Result_matchId_fkey" FOREIGN KEY ("matchId") REFERENCES "Match"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Result" ADD CONSTRAINT "Result_winnerId_fkey" FOREIGN KEY ("winnerId") REFERENCES "Player"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|