270 lines
9.1 KiB
SQL
270 lines
9.1 KiB
SQL
-- AI 캐릭터 크리에이터 기능 최소 연결 운영 DB 반영 SQL
|
|
-- MySQL 기준. 운영 반영 전 백업과 트랜잭션/락 영향을 점검한다.
|
|
|
|
-- 1. member.member_kind 추가
|
|
SET @member_kind_column_exists := (
|
|
SELECT COUNT(*)
|
|
FROM information_schema.columns
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'member'
|
|
AND column_name = 'member_kind'
|
|
);
|
|
|
|
SET @add_member_kind_sql := IF(
|
|
@member_kind_column_exists = 0,
|
|
'ALTER TABLE member ADD COLUMN member_kind VARCHAR(30) NOT NULL DEFAULT ''HUMAN'' COMMENT ''Member 주체 종류: HUMAN, AI_CHARACTER'' AFTER role',
|
|
'SELECT ''member.member_kind already exists'' AS message'
|
|
);
|
|
|
|
PREPARE add_member_kind_stmt FROM @add_member_kind_sql;
|
|
EXECUTE add_member_kind_stmt;
|
|
DEALLOCATE PREPARE add_member_kind_stmt;
|
|
|
|
-- 1번 결과 확인: varchar(30), NOT NULL, DEFAULT 'HUMAN'
|
|
SELECT column_name, column_type, is_nullable, column_default, column_comment
|
|
FROM information_schema.columns
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'member'
|
|
AND column_name = 'member_kind';
|
|
|
|
-- 2. chat_character.creator_member_id nullable 컬럼 추가
|
|
SET @creator_member_column_exists := (
|
|
SELECT COUNT(*)
|
|
FROM information_schema.columns
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'chat_character'
|
|
AND column_name = 'creator_member_id'
|
|
);
|
|
|
|
SET @add_creator_member_sql := IF(
|
|
@creator_member_column_exists = 0,
|
|
'ALTER TABLE chat_character ADD COLUMN creator_member_id BIGINT NULL COMMENT ''크리에이터 기능 주체 Member ID'' AFTER character_type',
|
|
'SELECT ''chat_character.creator_member_id already exists'' AS message'
|
|
);
|
|
|
|
PREPARE add_creator_member_stmt FROM @add_creator_member_sql;
|
|
EXECUTE add_creator_member_stmt;
|
|
DEALLOCATE PREPARE add_creator_member_stmt;
|
|
|
|
-- 2번 결과 확인: bigint, NULL 허용
|
|
SELECT column_name, column_type, is_nullable, column_comment
|
|
FROM information_schema.columns
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'chat_character'
|
|
AND column_name = 'creator_member_id';
|
|
|
|
-- 3. 기존 chat_character별 AI 캐릭터용 Member 생성 및 매핑
|
|
DROP TEMPORARY TABLE IF EXISTS tmp_chat_character_creator_member;
|
|
CREATE TEMPORARY TABLE tmp_chat_character_creator_member (
|
|
chat_character_id BIGINT NOT NULL PRIMARY KEY,
|
|
migration_email VARCHAR(255) NOT NULL UNIQUE,
|
|
creator_member_id BIGINT NULL
|
|
) COMMENT 'chat_character와 backfill member.id 임시 매핑';
|
|
|
|
INSERT INTO tmp_chat_character_creator_member (chat_character_id, migration_email)
|
|
SELECT
|
|
c.id,
|
|
CONCAT('__ai_character_creator_', c.id, '@migration.local')
|
|
FROM chat_character c
|
|
WHERE c.creator_member_id IS NULL;
|
|
|
|
-- member.email은 nullable이므로 backfill 중에만 임시 식별자로 사용하고, 매핑 후 NULL로 되돌린다.
|
|
INSERT INTO member (
|
|
email,
|
|
password,
|
|
nickname,
|
|
profile_image,
|
|
provider,
|
|
gender,
|
|
role,
|
|
member_kind,
|
|
is_visible_donation_rank,
|
|
donation_ranking_period,
|
|
is_active,
|
|
container,
|
|
introduce,
|
|
instagram_url,
|
|
fancimm_url,
|
|
x_url,
|
|
youtube_url,
|
|
website_url,
|
|
blog_url,
|
|
pg_charge_can,
|
|
pg_reward_can,
|
|
google_charge_can,
|
|
google_reward_can,
|
|
apple_charge_can,
|
|
apple_reward_can,
|
|
created_at,
|
|
updated_at
|
|
)
|
|
SELECT
|
|
m.migration_email,
|
|
'',
|
|
c.name,
|
|
c.image_path,
|
|
'EMAIL',
|
|
'NONE',
|
|
'CREATOR',
|
|
'AI_CHARACTER',
|
|
TRUE,
|
|
'CUMULATIVE',
|
|
c.is_active,
|
|
'web',
|
|
COALESCE(c.description, ''),
|
|
'',
|
|
'',
|
|
'',
|
|
'',
|
|
'',
|
|
'',
|
|
0,
|
|
0,
|
|
0,
|
|
0,
|
|
0,
|
|
0,
|
|
CURRENT_TIMESTAMP,
|
|
CURRENT_TIMESTAMP
|
|
FROM tmp_chat_character_creator_member m
|
|
INNER JOIN chat_character c
|
|
ON c.id = m.chat_character_id
|
|
LEFT JOIN member existing_member
|
|
ON existing_member.email = m.migration_email
|
|
WHERE existing_member.id IS NULL;
|
|
|
|
UPDATE tmp_chat_character_creator_member m
|
|
INNER JOIN member mb
|
|
ON mb.email = m.migration_email
|
|
SET m.creator_member_id = mb.id
|
|
WHERE m.creator_member_id IS NULL
|
|
AND m.chat_character_id IS NOT NULL;
|
|
|
|
UPDATE chat_character c
|
|
INNER JOIN tmp_chat_character_creator_member m
|
|
ON m.chat_character_id = c.id
|
|
SET c.creator_member_id = m.creator_member_id
|
|
WHERE c.creator_member_id IS NULL
|
|
AND m.creator_member_id IS NOT NULL;
|
|
|
|
UPDATE member mb
|
|
INNER JOIN tmp_chat_character_creator_member m
|
|
ON m.creator_member_id = mb.id
|
|
SET mb.email = NULL
|
|
WHERE mb.email = m.migration_email;
|
|
|
|
-- 4. unique index 추가
|
|
SET @creator_member_unique_exists := (
|
|
SELECT COUNT(*)
|
|
FROM information_schema.statistics
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'chat_character'
|
|
AND index_name = 'uk_chat_character_creator_member'
|
|
);
|
|
|
|
SET @add_creator_member_unique_sql := IF(
|
|
@creator_member_unique_exists = 0,
|
|
'ALTER TABLE chat_character ADD UNIQUE INDEX uk_chat_character_creator_member (creator_member_id)',
|
|
'SELECT ''uk_chat_character_creator_member already exists'' AS message'
|
|
);
|
|
|
|
PREPARE add_creator_member_unique_stmt FROM @add_creator_member_unique_sql;
|
|
EXECUTE add_creator_member_unique_stmt;
|
|
DEALLOCATE PREPARE add_creator_member_unique_stmt;
|
|
|
|
-- 5. foreign key 추가
|
|
SET @creator_member_fk_exists := (
|
|
SELECT COUNT(*)
|
|
FROM information_schema.table_constraints
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'chat_character'
|
|
AND constraint_name = 'fk_chat_character_creator_member'
|
|
AND constraint_type = 'FOREIGN KEY'
|
|
);
|
|
|
|
SET @add_creator_member_fk_sql := IF(
|
|
@creator_member_fk_exists = 0,
|
|
'ALTER TABLE chat_character ADD CONSTRAINT fk_chat_character_creator_member FOREIGN KEY (creator_member_id) REFERENCES member (id)',
|
|
'SELECT ''fk_chat_character_creator_member already exists'' AS message'
|
|
);
|
|
|
|
PREPARE add_creator_member_fk_stmt FROM @add_creator_member_fk_sql;
|
|
EXECUTE add_creator_member_fk_stmt;
|
|
DEALLOCATE PREPARE add_creator_member_fk_stmt;
|
|
|
|
-- 6. 운영 반영 전 필수 검증. 두 결과 모두 0이어야 한다.
|
|
SELECT COUNT(*) AS invalid_ai_character_member_count
|
|
FROM member
|
|
WHERE member_kind = 'AI_CHARACTER'
|
|
AND role <> 'CREATOR';
|
|
|
|
SELECT COUNT(*) AS missing_creator_member_count
|
|
FROM chat_character
|
|
WHERE creator_member_id IS NULL;
|
|
|
|
SELECT COUNT(*) AS remaining_migration_email_count
|
|
FROM member
|
|
WHERE email LIKE '__ai_character_creator_%@migration.local';
|
|
|
|
-- 7. 검증 완료 후 creator_member_id NOT NULL 전환
|
|
SET @missing_creator_member_count := (
|
|
SELECT COUNT(*)
|
|
FROM chat_character
|
|
WHERE creator_member_id IS NULL
|
|
);
|
|
|
|
SET @creator_member_nullable := (
|
|
SELECT is_nullable
|
|
FROM information_schema.columns
|
|
WHERE table_schema = DATABASE()
|
|
AND table_name = 'chat_character'
|
|
AND column_name = 'creator_member_id'
|
|
);
|
|
|
|
SET @modify_creator_member_not_null_sql := IF(
|
|
@missing_creator_member_count = 0 AND @creator_member_nullable = 'YES',
|
|
'ALTER TABLE chat_character MODIFY COLUMN creator_member_id BIGINT NOT NULL COMMENT ''크리에이터 기능 주체 Member ID''',
|
|
'SELECT ''chat_character.creator_member_id not modified; verify missing_creator_member_count is 0 and column is nullable'' AS message'
|
|
);
|
|
|
|
PREPARE modify_creator_member_not_null_stmt FROM @modify_creator_member_not_null_sql;
|
|
EXECUTE modify_creator_member_not_null_stmt;
|
|
DEALLOCATE PREPARE modify_creator_member_not_null_stmt;
|
|
|
|
DROP TEMPORARY TABLE IF EXISTS tmp_chat_character_creator_member;
|
|
|
|
-- Rollback 참고용. 운영 반영 후 문제가 있으면 백업 복구를 우선 검토한다.
|
|
-- 아래 SQL은 이 마이그레이션으로 연결된 AI_CHARACTER Member와 제약/컬럼을 되돌리는 전체 롤백 예시다.
|
|
-- 신규 기능을 이미 운영에서 사용한 뒤에는 후속 데이터 의존성이 생길 수 있으므로 실행 전 영향 범위를 재확인한다.
|
|
-- 1) FK 제거
|
|
-- ALTER TABLE chat_character DROP FOREIGN KEY fk_chat_character_creator_member;
|
|
-- 2) unique index 제거
|
|
-- ALTER TABLE chat_character DROP INDEX uk_chat_character_creator_member;
|
|
-- 3) creator_member_id를 NULL 허용으로 복구
|
|
-- ALTER TABLE chat_character MODIFY COLUMN creator_member_id BIGINT NULL COMMENT '크리에이터 기능 주체 Member ID';
|
|
-- 4) backfill로 연결된 AI 캐릭터용 Member 삭제 준비
|
|
-- DROP TEMPORARY TABLE IF EXISTS tmp_rollback_ai_character_member;
|
|
-- CREATE TEMPORARY TABLE tmp_rollback_ai_character_member (
|
|
-- member_id BIGINT NOT NULL PRIMARY KEY
|
|
-- ) COMMENT 'AI 캐릭터 크리에이터 backfill 롤백 대상 Member';
|
|
-- INSERT INTO tmp_rollback_ai_character_member (member_id)
|
|
-- SELECT DISTINCT mb.id
|
|
-- FROM member mb
|
|
-- INNER JOIN chat_character c
|
|
-- ON c.creator_member_id = mb.id
|
|
-- WHERE mb.member_kind = 'AI_CHARACTER'
|
|
-- AND mb.role = 'CREATOR';
|
|
-- 5) chat_character 연결 해제 후 Member 삭제
|
|
-- UPDATE chat_character c
|
|
-- INNER JOIN tmp_rollback_ai_character_member r
|
|
-- ON r.member_id = c.creator_member_id
|
|
-- SET c.creator_member_id = NULL;
|
|
-- DELETE mb
|
|
-- FROM member mb
|
|
-- INNER JOIN tmp_rollback_ai_character_member r
|
|
-- ON r.member_id = mb.id;
|
|
-- DROP TEMPORARY TABLE IF EXISTS tmp_rollback_ai_character_member;
|
|
-- 6) 컬럼 제거가 필요한 전체 스키마 롤백인 경우에만 실행
|
|
-- ALTER TABLE chat_character DROP COLUMN creator_member_id;
|
|
-- ALTER TABLE member DROP COLUMN member_kind;
|