100 lines
4.2 KiB
SQL
100 lines
4.2 KiB
SQL
-- Indie game team recruitment posts.
|
|
-- PostgreSQL DDL aligned with the existing bibimbap table style.
|
|
|
|
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";
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_constraint WHERE conname = 'recruit_posts_user_id_fkey'
|
|
) THEN
|
|
ALTER TABLE "recruit_posts"
|
|
ADD CONSTRAINT "recruit_posts_user_id_fkey"
|
|
FOREIGN KEY ("user_id") REFERENCES "users" ("id");
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_constraint WHERE conname = 'recruit_posts_role_check'
|
|
) THEN
|
|
ALTER TABLE "recruit_posts"
|
|
ADD CONSTRAINT "recruit_posts_role_check"
|
|
CHECK ("role" IN ('기획', '아트', '프로그래머'));
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_constraint WHERE conname = 'recruit_posts_participation_type_check'
|
|
) THEN
|
|
ALTER TABLE "recruit_posts"
|
|
ADD CONSTRAINT "recruit_posts_participation_type_check"
|
|
CHECK ("participation_type" IN ('취미', '수익쉐어', '유급', '게임잼'));
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
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;
|
|
|
|
COMMENT ON TABLE "recruit_posts" IS '인디게임 팀원 모집글';
|
|
COMMENT ON COLUMN "recruit_posts"."id" IS '모집글 고유 ID';
|
|
COMMENT ON COLUMN "recruit_posts"."user_id" IS '모집글 작성자 users.id';
|
|
COMMENT ON COLUMN "recruit_posts"."project_name" IS '프로젝트 이름';
|
|
COMMENT ON COLUMN "recruit_posts"."genre" IS '게임 장르';
|
|
COMMENT ON COLUMN "recruit_posts"."summary" IS '한 줄 소개';
|
|
COMMENT ON COLUMN "recruit_posts"."role" IS '모집 역할. 기획, 아트, 프로그래머';
|
|
COMMENT ON COLUMN "recruit_posts"."project_status" IS '프로젝트 진행 상태';
|
|
COMMENT ON COLUMN "recruit_posts"."participation_type" IS '참여 방식. 취미, 수익쉐어, 유급, 게임잼';
|
|
COMMENT ON COLUMN "recruit_posts"."expected_period" IS '예상 작업 기간';
|
|
COMMENT ON COLUMN "recruit_posts"."team_members" IS '현재 팀 구성';
|
|
COMMENT ON COLUMN "recruit_posts"."contact" IS '연락 방법';
|
|
COMMENT ON COLUMN "recruit_posts"."description" IS '상세 설명';
|
|
COMMENT ON COLUMN "recruit_posts"."reference_url" IS '참고 링크';
|
|
COMMENT ON COLUMN "recruit_posts"."deadline_at" IS '모집 마감 시각';
|
|
COMMENT ON COLUMN "recruit_posts"."is_visible" IS '목록 공개 여부';
|
|
COMMENT ON COLUMN "recruit_posts"."sort_order" IS '정렬 우선순위';
|
|
COMMENT ON COLUMN "recruit_posts"."created_at" IS '모집글 생성 시각';
|
|
COMMENT ON COLUMN "recruit_posts"."updated_at" IS '모집글 마지막 수정 시각';
|
|
COMMENT ON COLUMN "recruit_posts"."deleted_at" IS '모집글 삭제 시각';
|
|
COMMENT ON COLUMN "recruit_posts"."is_delete" IS '소프트 삭제 여부';
|