171 lines
8.8 KiB
SQL
171 lines
8.8 KiB
SQL
-- =============================================================================
|
|
-- bibimbap local-dev schema bootstrap
|
|
-- =============================================================================
|
|
-- 출처/권위 수준 (반드시 읽을 것):
|
|
-- * recruit_posts : 권위 DDL. docs/recruit-posts-ddl.sql 와 동일 스펙.
|
|
-- * user_auth_identities : security-hardening 의 active-unique index 포함(docs/security-hardening-ddl.sql).
|
|
-- * users / games /
|
|
-- game_comments / game_likes : **비권위 복원본**. 운영 DB 의 pg_dump 가 아니라
|
|
-- MyBatis 매퍼(@Insert/@Select)의 컬럼 목록 + data POJO 의 Java 타입에서 역추출.
|
|
-- 컬럼 "이름/유무" 는 매퍼와 일치(확인됨)하나, **타입·길이·기본값·제약은 추론값**이다.
|
|
-- 실제 운영 스키마와 대조(pg_dump) 전까지 권위 자료로 신뢰하지 말 것.
|
|
--
|
|
-- 타입 매핑 근거: Long→bigint, Integer→integer, Boolean→boolean,
|
|
-- String→varchar(길이추정)/text, OffsetDateTime→timestamptz.
|
|
--
|
|
-- 적용 범위: 이 스크립트는 dev 스키마를 채운다(기본 프로필). live 스키마는 빈 상태로
|
|
-- 생성만 한다. live 도 채우려면 아래 'SET search_path TO live;' 로 바꿔 동일 블록을 재실행.
|
|
-- PostgreSQL 컨테이너의 docker-entrypoint-initdb.d 에서 최초 1회 자동 실행된다.
|
|
-- =============================================================================
|
|
|
|
CREATE SCHEMA IF NOT EXISTS dev;
|
|
CREATE SCHEMA IF NOT EXISTS live;
|
|
|
|
SET search_path TO dev;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- users (비권위 복원본)
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE SEQUENCE IF NOT EXISTS "users_id_seq";
|
|
CREATE TABLE IF NOT EXISTS "users" (
|
|
"id" bigint DEFAULT nextval('users_id_seq'::regclass) NOT NULL,
|
|
"display_name" character varying(200),
|
|
"canonical_email" character varying(320),
|
|
"avatar_url" character varying(500),
|
|
"role" character varying(30) DEFAULT 'USER' NOT NULL,
|
|
"status" character varying(30),
|
|
"last_login_at" timestamp with time zone,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"is_delete" boolean DEFAULT false NOT NULL,
|
|
PRIMARY KEY ("id")
|
|
);
|
|
ALTER SEQUENCE "users_id_seq" OWNED BY "users"."id";
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- user_auth_identities (비권위 복원본 + security-hardening active-unique index)
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE SEQUENCE IF NOT EXISTS "user_auth_identities_id_seq";
|
|
CREATE TABLE IF NOT EXISTS "user_auth_identities" (
|
|
"id" bigint DEFAULT nextval('user_auth_identities_id_seq'::regclass) NOT NULL,
|
|
"user_id" bigint NOT NULL REFERENCES "users" ("id"),
|
|
"provider" character varying(30) NOT NULL,
|
|
"provider_user_id" character varying(200) NOT NULL,
|
|
"email" character varying(320),
|
|
"password_hash" character varying(500),
|
|
"display_name" character varying(200),
|
|
"avatar_url" character varying(500),
|
|
"last_login_at" timestamp with time zone,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"is_delete" boolean DEFAULT false NOT NULL,
|
|
PRIMARY KEY ("id")
|
|
);
|
|
ALTER SEQUENCE "user_auth_identities_id_seq" OWNED BY "user_auth_identities"."id";
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "ux_user_auth_identities_provider_user_id_active"
|
|
ON "user_auth_identities" ("provider", "provider_user_id")
|
|
WHERE "is_delete" IS NOT TRUE;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- games (비권위 복원본)
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE SEQUENCE IF NOT EXISTS "games_id_seq";
|
|
CREATE TABLE IF NOT EXISTS "games" (
|
|
"id" bigint DEFAULT nextval('games_id_seq'::regclass) NOT NULL,
|
|
"user_id" bigint NOT NULL REFERENCES "users" ("id"),
|
|
"name" character varying(200) NOT NULL,
|
|
"creator_note" text,
|
|
"git_url" character varying(500),
|
|
"webgl_path" character varying(500),
|
|
"thumbnail_url" character varying(500),
|
|
"like_count" integer DEFAULT 0 NOT NULL,
|
|
"is_visible" boolean DEFAULT true NOT NULL,
|
|
"sort_order" integer DEFAULT 0 NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"is_delete" boolean DEFAULT false NOT NULL,
|
|
PRIMARY KEY ("id")
|
|
);
|
|
ALTER SEQUENCE "games_id_seq" OWNED BY "games"."id";
|
|
|
|
CREATE INDEX IF NOT EXISTS "idx_games_visible_order"
|
|
ON "games" ("is_visible", "is_delete", "sort_order", "created_at" DESC, "id" DESC);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- game_comments (비권위 복원본 — 매퍼 존재하나 컨트롤러 미연결)
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE SEQUENCE IF NOT EXISTS "game_comments_id_seq";
|
|
CREATE TABLE IF NOT EXISTS "game_comments" (
|
|
"id" bigint DEFAULT nextval('game_comments_id_seq'::regclass) NOT NULL,
|
|
"game_id" bigint NOT NULL REFERENCES "games" ("id"),
|
|
"nickname" character varying(100),
|
|
"content" text,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"deleted_at" timestamp with time zone,
|
|
"is_delete" boolean DEFAULT false NOT NULL,
|
|
PRIMARY KEY ("id")
|
|
);
|
|
ALTER SEQUENCE "game_comments_id_seq" OWNED BY "game_comments"."id";
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- game_likes (비권위 복원본 — 매퍼는 hard delete 사용, is_delete 컬럼 없음)
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE SEQUENCE IF NOT EXISTS "game_likes_id_seq";
|
|
CREATE TABLE IF NOT EXISTS "game_likes" (
|
|
"id" bigint DEFAULT nextval('game_likes_id_seq'::regclass) NOT NULL,
|
|
"game_id" bigint NOT NULL REFERENCES "games" ("id"),
|
|
"user_key" character varying(200) NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
PRIMARY KEY ("id")
|
|
);
|
|
ALTER SEQUENCE "game_likes_id_seq" OWNED BY "game_likes"."id";
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- recruit_posts (권위 DDL — docs/recruit-posts-ddl.sql 와 동일)
|
|
-- ---------------------------------------------------------------------------
|
|
CREATE SEQUENCE IF NOT EXISTS "recruit_posts_id_seq";
|
|
CREATE TABLE IF NOT EXISTS "recruit_posts" (
|
|
"id" bigint DEFAULT nextval('recruit_posts_id_seq'::regclass) NOT NULL,
|
|
"user_id" bigint NOT NULL,
|
|
"project_name" character varying(200) NOT NULL,
|
|
"genre" character varying(80),
|
|
"summary" character varying(200) NOT NULL,
|
|
"role" character varying(30) NOT NULL,
|
|
"project_status" character varying(50) NOT NULL,
|
|
"participation_type" character varying(30) NOT NULL,
|
|
"expected_period" character varying(80),
|
|
"team_members" character varying(200),
|
|
"contact" character varying(200) NOT NULL,
|
|
"description" text,
|
|
"reference_url" character varying(500),
|
|
"deadline_at" timestamp with time zone,
|
|
"is_visible" boolean DEFAULT true NOT NULL,
|
|
"sort_order" integer DEFAULT 0 NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"deleted_at" timestamp with time zone,
|
|
"is_delete" boolean DEFAULT false NOT NULL,
|
|
PRIMARY KEY ("id")
|
|
);
|
|
ALTER SEQUENCE "recruit_posts_id_seq" OWNED BY "recruit_posts"."id";
|
|
|
|
ALTER TABLE "recruit_posts"
|
|
ADD CONSTRAINT "recruit_posts_user_id_fkey"
|
|
FOREIGN KEY ("user_id") REFERENCES "users" ("id");
|
|
ALTER TABLE "recruit_posts"
|
|
ADD CONSTRAINT "recruit_posts_role_check"
|
|
CHECK ("role" IN ('기획', '아트', '프로그래머'));
|
|
ALTER TABLE "recruit_posts"
|
|
ADD CONSTRAINT "recruit_posts_participation_type_check"
|
|
CHECK ("participation_type" IN ('취미', '수익쉐어', '유급', '게임잼'));
|
|
|
|
CREATE INDEX IF NOT EXISTS "idx_recruit_posts_visible_order"
|
|
ON "recruit_posts" ("is_visible", "is_delete", "sort_order", "created_at" DESC, "id" DESC);
|
|
CREATE INDEX IF NOT EXISTS "idx_recruit_posts_role"
|
|
ON "recruit_posts" ("role")
|
|
WHERE "is_delete" = false AND "is_visible" = true;
|
|
CREATE INDEX IF NOT EXISTS "idx_recruit_posts_participation_type"
|
|
ON "recruit_posts" ("participation_type")
|
|
WHERE "is_delete" = false AND "is_visible" = true;
|