qwer-xyz
33 lines
2.0 KiB
SQL
33 lines
2.0 KiB
SQL
ALTER TYPE "PointsLogType" ADD VALUE IF NOT EXISTS 'badge_draw_spend';
|
|
ALTER TYPE "PointsLogType" ADD VALUE IF NOT EXISTS 'badge_select_spend';
|
|
|
|
CREATE TYPE "UserBadgeSource" AS ENUM ('draw', 'select', 'admin_grant');
|
|
CREATE TYPE "BadgeApplicationTarget" AS ENUM ('avatar', 'instance');
|
|
|
|
ALTER TABLE "users" ADD COLUMN "avatar_badge_id" TEXT;
|
|
ALTER TABLE "instances" ADD COLUMN "icon_badge_id" TEXT;
|
|
|
|
CREATE TABLE "user_badge_ownerships" (
|
|
"id" SERIAL NOT NULL,
|
|
"user_id" INTEGER NOT NULL,
|
|
"badge_id" TEXT NOT NULL,
|
|
"source" "UserBadgeSource" NOT NULL DEFAULT 'draw',
|
|
"application_target" "BadgeApplicationTarget",
|
|
"applied_instance_id" INTEGER,
|
|
"applied_at" TIMESTAMP(3),
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "user_badge_ownerships_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE INDEX "user_badge_ownerships_user_id_created_at_idx" ON "user_badge_ownerships"("user_id", "created_at" DESC);
|
|
CREATE INDEX "user_badge_ownerships_user_id_badge_id_idx" ON "user_badge_ownerships"("user_id", "badge_id");
|
|
CREATE INDEX "user_badge_ownerships_user_id_application_target_idx" ON "user_badge_ownerships"("user_id", "application_target");
|
|
CREATE INDEX "user_badge_ownerships_applied_instance_id_idx" ON "user_badge_ownerships"("applied_instance_id");
|
|
CREATE UNIQUE INDEX "user_badge_ownerships_one_avatar_per_user_idx" ON "user_badge_ownerships"("user_id") WHERE "application_target" = 'avatar';
|
|
CREATE UNIQUE INDEX "user_badge_ownerships_one_badge_per_instance_idx" ON "user_badge_ownerships"("applied_instance_id") WHERE "application_target" = 'instance' AND "applied_instance_id" IS NOT NULL;
|
|
|
|
ALTER TABLE "user_badge_ownerships" ADD CONSTRAINT "user_badge_ownerships_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "user_badge_ownerships" ADD CONSTRAINT "user_badge_ownerships_applied_instance_id_fkey" FOREIGN KEY ("applied_instance_id") REFERENCES "instances"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|