mirror of
https://github.com/chatfire-AI/huobao-drama.git
synced 2026-08-29 02:26:14 +08:00
Connor
4354472aaf
- 重新导出:17 张表(agent_configs 已移除)、种子 5 条 - 导出脚本适配 mysql-schema 精简(backfill/drop/cleanup 语句组已删除) Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
304 lines
12 KiB
SQL
304 lines
12 KiB
SQL
-- ============================================================================
|
|
-- Huobao Drama 初始化 SQL
|
|
-- 由 backend/scripts/export-init-sql.ts 从 backend/src/db/mysql-schema.ts 生成
|
|
-- 生成时间: 2026-08-08T15:10:25.386Z
|
|
--
|
|
-- 注意: 应用启动时会自动执行同等初始化(幂等),本文件不是部署必需,
|
|
-- 仅供 DBA 审核或在应用外预建表使用
|
|
-- ============================================================================
|
|
|
|
SET NAMES utf8mb4;
|
|
|
|
-- ----------------------------------------------------------------------------
|
|
-- 1. 建表(17 张)
|
|
-- ----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS dramas (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
genre TEXT,
|
|
style VARCHAR(64) DEFAULT '3d',
|
|
aspect_ratio VARCHAR(16) DEFAULT '16:9',
|
|
total_episodes INT DEFAULT 1,
|
|
total_duration INT DEFAULT 0,
|
|
status VARCHAR(64) NOT NULL DEFAULT 'draft',
|
|
thumbnail TEXT,
|
|
tags TEXT,
|
|
metadata TEXT,
|
|
created_at VARCHAR(64) NOT NULL,
|
|
updated_at VARCHAR(64) NOT NULL,
|
|
deleted_at VARCHAR(64)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS episodes (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
drama_id INT NOT NULL,
|
|
episode_number INT NOT NULL,
|
|
title TEXT NOT NULL,
|
|
content TEXT,
|
|
script_content TEXT,
|
|
description TEXT,
|
|
duration INT DEFAULT 0,
|
|
status VARCHAR(64) DEFAULT 'draft',
|
|
video_url TEXT,
|
|
thumbnail TEXT,
|
|
image_config_id INT,
|
|
video_config_id INT,
|
|
resolution VARCHAR(16) DEFAULT '720p',
|
|
created_at VARCHAR(64) NOT NULL,
|
|
updated_at VARCHAR(64) NOT NULL,
|
|
deleted_at VARCHAR(64)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS characters (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
drama_id INT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
role TEXT,
|
|
description TEXT,
|
|
appearance TEXT,
|
|
styling TEXT,
|
|
final_prompt TEXT,
|
|
personality TEXT,
|
|
image_url TEXT,
|
|
reference_images TEXT,
|
|
seed_value TEXT,
|
|
sort_order INT,
|
|
local_path TEXT,
|
|
created_at VARCHAR(64) NOT NULL,
|
|
updated_at VARCHAR(64) NOT NULL,
|
|
deleted_at VARCHAR(64)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS scenes (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
drama_id INT NOT NULL,
|
|
episode_id INT,
|
|
location TEXT NOT NULL,
|
|
time VARCHAR(64) NOT NULL,
|
|
prompt TEXT NOT NULL,
|
|
lighting TEXT,
|
|
final_prompt TEXT,
|
|
storyboard_count INT DEFAULT 1,
|
|
image_url TEXT,
|
|
status VARCHAR(64) DEFAULT 'pending',
|
|
local_path TEXT,
|
|
created_at VARCHAR(64) NOT NULL,
|
|
updated_at VARCHAR(64) NOT NULL,
|
|
deleted_at VARCHAR(64)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS storyboards (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
episode_id INT NOT NULL,
|
|
scene_id INT,
|
|
storyboard_number INT NOT NULL,
|
|
title TEXT,
|
|
location TEXT,
|
|
time VARCHAR(64),
|
|
shot_type TEXT,
|
|
angle TEXT,
|
|
movement TEXT,
|
|
result TEXT,
|
|
atmosphere TEXT,
|
|
image_prompt TEXT,
|
|
video_prompt TEXT,
|
|
bgm_prompt TEXT,
|
|
sound_effect TEXT,
|
|
description TEXT,
|
|
duration INT DEFAULT 0,
|
|
composed_image TEXT,
|
|
first_frame_image TEXT,
|
|
last_frame_image TEXT,
|
|
reference_images TEXT,
|
|
video_url TEXT,
|
|
subtitle_url TEXT,
|
|
composed_video_url TEXT,
|
|
status VARCHAR(64) DEFAULT 'pending',
|
|
created_at VARCHAR(64) NOT NULL,
|
|
updated_at VARCHAR(64) NOT NULL,
|
|
deleted_at VARCHAR(64)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS episode_characters (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
episode_id INT NOT NULL,
|
|
character_id INT NOT NULL,
|
|
created_at VARCHAR(64) NOT NULL,
|
|
INDEX idx_episode_characters_episode_id (episode_id),
|
|
INDEX idx_episode_characters_character_id (character_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS episode_scenes (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
episode_id INT NOT NULL,
|
|
scene_id INT NOT NULL,
|
|
created_at VARCHAR(64) NOT NULL,
|
|
INDEX idx_episode_scenes_episode_id (episode_id),
|
|
INDEX idx_episode_scenes_scene_id (scene_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS episode_props (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
episode_id INT NOT NULL,
|
|
prop_id INT NOT NULL,
|
|
created_at VARCHAR(64) NOT NULL,
|
|
INDEX idx_episode_props_episode_id (episode_id),
|
|
INDEX idx_episode_props_prop_id (prop_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS storyboard_characters (
|
|
storyboard_id INT NOT NULL,
|
|
character_id INT NOT NULL,
|
|
PRIMARY KEY (storyboard_id, character_id),
|
|
INDEX idx_storyboard_characters_character_id (character_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS storyboard_props (
|
|
storyboard_id INT NOT NULL,
|
|
prop_id INT NOT NULL,
|
|
PRIMARY KEY (storyboard_id, prop_id),
|
|
INDEX idx_storyboard_props_prop_id (prop_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS ai_service_configs (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
service_type VARCHAR(64) NOT NULL,
|
|
provider VARCHAR(64),
|
|
name TEXT NOT NULL,
|
|
base_url TEXT NOT NULL,
|
|
api_key TEXT NOT NULL,
|
|
model TEXT,
|
|
endpoint TEXT,
|
|
query_endpoint TEXT,
|
|
priority INT DEFAULT 0,
|
|
is_default TINYINT(1) DEFAULT 0,
|
|
is_active TINYINT(1) DEFAULT 1,
|
|
settings TEXT,
|
|
created_at VARCHAR(64) NOT NULL,
|
|
updated_at VARCHAR(64) NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS ai_service_providers (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
display_name TEXT,
|
|
service_type VARCHAR(64) NOT NULL,
|
|
provider VARCHAR(64) NOT NULL,
|
|
default_url TEXT,
|
|
preset_models TEXT,
|
|
description TEXT,
|
|
is_active TINYINT(1) DEFAULT 1,
|
|
created_at VARCHAR(64) NOT NULL,
|
|
updated_at VARCHAR(64) NOT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS style_presets (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(64) NOT NULL,
|
|
value VARCHAR(64) NOT NULL,
|
|
prompt TEXT NOT NULL,
|
|
description TEXT,
|
|
sort_order INT DEFAULT 0,
|
|
is_active TINYINT(1) DEFAULT 1,
|
|
created_at VARCHAR(64) NOT NULL,
|
|
updated_at VARCHAR(64) NOT NULL,
|
|
UNIQUE KEY uk_style_presets_value (value)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS sys_task (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
type VARCHAR(16) NOT NULL,
|
|
storyboard_id INT,
|
|
drama_id INT,
|
|
scene_id INT,
|
|
character_id INT,
|
|
prop_id INT,
|
|
provider VARCHAR(64),
|
|
prompt TEXT,
|
|
model TEXT,
|
|
params TEXT,
|
|
task_id TEXT,
|
|
result_url TEXT,
|
|
local_path TEXT,
|
|
status VARCHAR(64) DEFAULT 'processing',
|
|
error_msg TEXT,
|
|
created_at VARCHAR(64) NOT NULL,
|
|
updated_at VARCHAR(64) NOT NULL,
|
|
completed_at VARCHAR(64),
|
|
INDEX idx_sys_task_type (type),
|
|
INDEX idx_sys_task_drama_id (drama_id),
|
|
INDEX idx_sys_task_storyboard_id (storyboard_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS video_merges (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
episode_id INT,
|
|
drama_id INT,
|
|
title TEXT,
|
|
provider VARCHAR(64) NOT NULL,
|
|
model TEXT NOT NULL,
|
|
status VARCHAR(64) DEFAULT 'pending',
|
|
scenes TEXT,
|
|
merged_url TEXT,
|
|
duration INT,
|
|
task_id TEXT,
|
|
error_msg TEXT,
|
|
created_at VARCHAR(64) NOT NULL,
|
|
completed_at VARCHAR(64),
|
|
deleted_at VARCHAR(64)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS props (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
drama_id INT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
type TEXT,
|
|
description TEXT,
|
|
prompt TEXT,
|
|
final_prompt TEXT,
|
|
image_url TEXT,
|
|
reference_images TEXT,
|
|
local_path TEXT,
|
|
created_at VARCHAR(64) NOT NULL,
|
|
updated_at VARCHAR(64) NOT NULL,
|
|
deleted_at VARCHAR(64)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS assets (
|
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
drama_id INT,
|
|
episode_id INT,
|
|
storyboard_id INT,
|
|
storyboard_num INT,
|
|
name TEXT,
|
|
description TEXT,
|
|
type TEXT,
|
|
category TEXT,
|
|
url TEXT,
|
|
thumbnail_url TEXT,
|
|
local_path TEXT,
|
|
file_size INT,
|
|
mime_type TEXT,
|
|
width INT,
|
|
height INT,
|
|
duration INT,
|
|
format TEXT,
|
|
image_gen_id INT,
|
|
video_gen_id INT,
|
|
is_favorite TINYINT(1) DEFAULT 0,
|
|
view_count INT DEFAULT 0,
|
|
created_at VARCHAR(64) NOT NULL,
|
|
updated_at VARCHAR(64) NOT NULL,
|
|
deleted_at VARCHAR(64)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- ----------------------------------------------------------------------------
|
|
-- 2. 种子数据: 风格预设(幂等,只补缺失行)
|
|
-- ----------------------------------------------------------------------------
|
|
INSERT INTO `style_presets` (`name`, `value`, `prompt`, `description`, `sort_order`, `is_active`, `created_at`, `updated_at`) SELECT '3D 漫剧', '3d', '3D CG animation style, game-engine quality render, semi-realistic stylized characters, refined facial features, detailed materials and textures, cinematic lighting, high detail', '游戏引擎级 3D 渲染，半写实角色，当前短剧主流的 3D 漫剧质感', 1, 1, '2026-08-08T15:10:25.385Z', '2026-08-08T15:10:25.386Z' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `style_presets` WHERE `value` = '3d');
|
|
INSERT INTO `style_presets` (`name`, `value`, `prompt`, `description`, `sort_order`, `is_active`, `created_at`, `updated_at`) SELECT '日漫赛璐璐', 'anime', 'Japanese anime style, cel shading, clean crisp line art, vivid saturated colors, expressive character designs, detailed painted backgrounds', '日式赛璐璐动画风格', 2, 1, '2026-08-08T15:10:25.386Z', '2026-08-08T15:10:25.386Z' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `style_presets` WHERE `value` = 'anime');
|
|
INSERT INTO `style_presets` (`name`, `value`, `prompt`, `description`, `sort_order`, `is_active`, `created_at`, `updated_at`) SELECT '吉卜力手绘', 'ghibli', 'Studio Ghibli style, hand-drawn animation, soft watercolor painted backgrounds, warm nostalgic lighting, gentle natural palette, whimsical cozy atmosphere', '吉卜力手绘治愈风', 3, 1, '2026-08-08T15:10:25.386Z', '2026-08-08T15:10:25.386Z' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `style_presets` WHERE `value` = 'ghibli');
|
|
INSERT INTO `style_presets` (`name`, `value`, `prompt`, `description`, `sort_order`, `is_active`, `created_at`, `updated_at`) SELECT '水彩绘本', 'watercolor', 'watercolor illustration style, soft translucent washes, visible paper texture, delicate fluid brushwork, light airy atmosphere, hand-painted storybook feel', '水彩插画质感', 4, 1, '2026-08-08T15:10:25.386Z', '2026-08-08T15:10:25.386Z' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `style_presets` WHERE `value` = 'watercolor');
|
|
INSERT INTO `style_presets` (`name`, `value`, `prompt`, `description`, `sort_order`, `is_active`, `created_at`, `updated_at`) SELECT '美式漫画', 'comic', 'Western comic book style, bold black ink outlines, halftone dot shading, dynamic saturated colors, dramatic contrast lighting, flat graphic novel look', '美式漫画粗线条风格', 5, 1, '2026-08-08T15:10:25.386Z', '2026-08-08T15:10:25.386Z' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `style_presets` WHERE `value` = 'comic');
|