DB 코드와 애플리케이션 열거형

DROP TEMPORARY TABLE IF EXISTS release_enum_bad;

CREATE TEMPORARY TABLE release_enum_bad (
  code VARCHAR(40) NOT NULL PRIMARY KEY
);

INSERT INTO release_enum_bad (code)
VALUES ('DRAFT'), ('PUBLISHED'), ('HIDDEN'), ('PAUSED');

SELECT db.code AS db_only
FROM final_code AS db
LEFT JOIN release_enum_bad AS app ON app.code = db.code
WHERE db.group_code = 'POST_STATUS'
  AND db.is_active = TRUE
  AND app.code IS NULL;

SELECT app.code AS app_only
FROM release_enum_bad AS app
LEFT JOIN final_code AS db
  ON db.group_code = 'POST_STATUS'
 AND db.code = app.code
 AND db.is_active = TRUE
WHERE db.code IS NULL;

db_only
DELETED

app_only
PAUSED

release compatible: no

DROP TABLE IF EXISTS final_post_status_transition;
DROP TABLE IF EXISTS final_release_enum_code;

CREATE TABLE final_release_enum_code (
  release_id VARCHAR(40) NOT NULL,
  group_code VARCHAR(40) NOT NULL,
  code VARCHAR(40) NOT NULL,
  loaded_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (release_id, group_code, code),
  CONSTRAINT fk_release_enum_code FOREIGN KEY (group_code, code)
    REFERENCES final_code (group_code, code) ON DELETE RESTRICT,
  CONSTRAINT chk_release_enum_group
    CHECK (group_code = 'POST_STATUS')
) ENGINE=InnoDB;

CREATE TABLE final_post_status_transition (
  group_code VARCHAR(40) NOT NULL DEFAULT 'POST_STATUS',
  from_code VARCHAR(40) NOT NULL,
  to_code VARCHAR(40) NOT NULL,
  transition_name VARCHAR(100) NOT NULL,
  PRIMARY KEY (group_code, from_code, to_code),
  CONSTRAINT chk_transition_group
    CHECK (group_code = 'POST_STATUS'),
  CONSTRAINT chk_transition_self CHECK (from_code <> to_code),
  CONSTRAINT fk_transition_from FOREIGN KEY (group_code, from_code)
    REFERENCES final_code (group_code, code) ON DELETE RESTRICT,
  CONSTRAINT fk_transition_to FOREIGN KEY (group_code, to_code)
    REFERENCES final_code (group_code, code) ON DELETE RESTRICT
) ENGINE=InnoDB;

INSERT INTO final_release_enum_code
  (release_id, group_code, code)
VALUES
  ('2026.07', 'POST_STATUS', 'DRAFT'),
  ('2026.07', 'POST_STATUS', 'PUBLISHED'),
  ('2026.07', 'POST_STATUS', 'HIDDEN'),
  ('2026.07', 'POST_STATUS', 'DELETED');

INSERT INTO final_post_status_transition
  (from_code, to_code, transition_name)
VALUES
  ('DRAFT', 'PUBLISHED', '게시 시작'),
  ('DRAFT', 'DELETED', '초안 삭제'),
  ('PUBLISHED', 'HIDDEN', '게시 숨김'),
  ('PUBLISHED', 'DELETED', '게시글 삭제');

release_id: 2026.07
manifest codes: 4
active DB codes: 4
db-only rows: 0
app-only rows: 0
allowed transitions: 4

SELECT 'DB_ONLY' AS mismatch_type, db.code
FROM final_code AS db
LEFT JOIN final_release_enum_code AS app
  ON app.release_id = '2026.07'
 AND app.group_code = db.group_code
 AND app.code = db.code
WHERE db.group_code = 'POST_STATUS'
  AND db.is_active = TRUE
  AND app.code IS NULL

UNION ALL

SELECT 'APP_ONLY' AS mismatch_type, app.code
FROM final_release_enum_code AS app
LEFT JOIN final_code AS db
  ON db.group_code = app.group_code
 AND db.code = app.code
 AND db.is_active = TRUE
WHERE app.release_id = '2026.07'
  AND app.group_code = 'POST_STATUS'
  AND db.code IS NULL;

SELECT EXISTS (
  SELECT 1
  FROM final_post_status_transition
  WHERE from_code = 'DRAFT'
    AND to_code = 'HIDDEN'
) AS draft_hide_allowed;

SELECT EXISTS (
  SELECT 1
  FROM final_post_status_transition
  WHERE from_code = 'PUBLISHED'
    AND to_code = 'HIDDEN'
) AS published_hide_allowed;