41 lines
2.3 KiB
SQL
41 lines
2.3 KiB
SQL
-- W2-2 심사위원 역할 권한(잼 스코프). 멱등. db/apply-local-ddl.sh 로 실행 DB 비파괴 적용.
|
|
-- 선행: docs/jam-ddl.sql(jams — 알파벳 글롭 순 jam-ddl 먼저 적용).
|
|
-- 전역 user_permissions(RBAC) 변경 없음 — 잼 회차별 역할은 잼 스코프 조인이 정석.
|
|
-- 추가만, 파괴 없음.
|
|
|
|
-- ===========================================================================
|
|
-- 1) jam_judges (잼별 심사위원. 잼 스코프 역할. 전역 권한과 별도 축)
|
|
-- ===========================================================================
|
|
CREATE SEQUENCE IF NOT EXISTS "jam_judges_id_seq";
|
|
CREATE TABLE IF NOT EXISTS "jam_judges" (
|
|
"id" bigint DEFAULT nextval('jam_judges_id_seq'::regclass) NOT NULL,
|
|
"jam_id" bigint NOT NULL, -- 잼 회차(FK jams)
|
|
"user_id" bigint NOT NULL, -- 심사위원(FK users; 누구나 가능)
|
|
"assigned_by" bigint, -- 지정 관리자(FK users; 감사 보조, nullable)
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
PRIMARY KEY ("id")
|
|
);
|
|
ALTER SEQUENCE "jam_judges_id_seq" OWNED BY "jam_judges"."id";
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'jam_judges_jam_fkey') THEN
|
|
ALTER TABLE "jam_judges" ADD CONSTRAINT "jam_judges_jam_fkey"
|
|
FOREIGN KEY ("jam_id") REFERENCES "jams" ("id");
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'jam_judges_user_fkey') THEN
|
|
ALTER TABLE "jam_judges" ADD CONSTRAINT "jam_judges_user_fkey"
|
|
FOREIGN KEY ("user_id") REFERENCES "users" ("id");
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'jam_judges_assigned_by_fkey') THEN
|
|
ALTER TABLE "jam_judges" ADD CONSTRAINT "jam_judges_assigned_by_fkey"
|
|
FOREIGN KEY ("assigned_by") REFERENCES "users" ("id");
|
|
END IF;
|
|
END
|
|
$$;
|
|
-- 같은 잼에 같은 유저 중복 지정 방지(멱등 지정). 잼 종료 후 잔존(J5) — soft delete 없음(이력=행 존재).
|
|
-- 해제는 hard DELETE(역할 회수). 재지정은 다시 INSERT.
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "ux_jam_judges_jam_user"
|
|
ON "jam_judges" ("jam_id", "user_id");
|
|
CREATE INDEX IF NOT EXISTS "idx_jam_judges_jam"
|
|
ON "jam_judges" ("jam_id");
|