580 lines
16 KiB
Markdown
580 lines
16 KiB
Markdown
# MixVideo 数据库设计规范
|
||
|
||
## 数据库技术栈
|
||
|
||
### 核心技术
|
||
- **SQLite 3.40+**: 嵌入式数据库
|
||
- **WAL模式**: 提高并发性能
|
||
- **外键约束**: 保证数据一致性
|
||
- **连接池**: 支持高并发访问
|
||
- **读写分离**: 优化查询性能
|
||
|
||
### 数据库配置
|
||
```sql
|
||
-- 启用WAL模式
|
||
PRAGMA journal_mode = WAL;
|
||
|
||
-- 启用外键约束
|
||
PRAGMA foreign_keys = ON;
|
||
|
||
-- 设置同步模式
|
||
PRAGMA synchronous = NORMAL;
|
||
|
||
-- 设置缓存大小
|
||
PRAGMA cache_size = 10000;
|
||
|
||
-- 设置超时时间
|
||
PRAGMA busy_timeout = 5000;
|
||
|
||
-- 启用安全删除
|
||
PRAGMA secure_delete = ON;
|
||
|
||
-- 设置临时存储
|
||
PRAGMA temp_store = MEMORY;
|
||
```
|
||
|
||
## 表设计规范
|
||
|
||
### 命名规范
|
||
- **表名**: snake_case复数形式 (如 `materials`, `projects`)
|
||
- **字段名**: snake_case (如 `created_at`, `project_id`)
|
||
- **索引名**: `idx_表名_字段名` (如 `idx_materials_project_id`)
|
||
- **外键名**: `fk_表名_引用表名` (如 `fk_materials_projects`)
|
||
|
||
### 字段设计规范
|
||
```sql
|
||
-- 主键设计
|
||
id TEXT PRIMARY KEY NOT NULL, -- 使用UUID字符串
|
||
|
||
-- 时间戳字段
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
|
||
-- 软删除字段
|
||
is_active INTEGER NOT NULL DEFAULT 1,
|
||
|
||
-- 外键字段
|
||
project_id TEXT NOT NULL,
|
||
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
|
||
|
||
-- 枚举字段（使用TEXT存储JSON）
|
||
material_type TEXT NOT NULL CHECK (material_type IN ('video', 'audio', 'image')),
|
||
|
||
-- 可选字段
|
||
description TEXT,
|
||
duration INTEGER, -- 可为NULL
|
||
|
||
-- 文件路径字段
|
||
path TEXT NOT NULL,
|
||
size INTEGER NOT NULL CHECK (size > 0),
|
||
```
|
||
|
||
## 核心表结构
|
||
|
||
### 1. 项目表 (projects)
|
||
```sql
|
||
CREATE TABLE projects (
|
||
id TEXT PRIMARY KEY NOT NULL,
|
||
name TEXT NOT NULL,
|
||
description TEXT,
|
||
path TEXT NOT NULL UNIQUE,
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
is_active INTEGER NOT NULL DEFAULT 1
|
||
);
|
||
|
||
-- 索引
|
||
CREATE INDEX idx_projects_name ON projects(name);
|
||
CREATE INDEX idx_projects_path ON projects(path);
|
||
CREATE INDEX idx_projects_created_at ON projects(created_at);
|
||
```
|
||
|
||
### 2. 素材表 (materials)
|
||
```sql
|
||
CREATE TABLE materials (
|
||
id TEXT PRIMARY KEY NOT NULL,
|
||
name TEXT NOT NULL,
|
||
path TEXT NOT NULL,
|
||
material_type TEXT NOT NULL CHECK (material_type IN ('video', 'audio', 'image')),
|
||
size INTEGER NOT NULL CHECK (size > 0),
|
||
duration INTEGER, -- 视频/音频时长(秒)
|
||
width INTEGER, -- 图片/视频宽度
|
||
height INTEGER, -- 图片/视频高度
|
||
fps REAL, -- 视频帧率
|
||
project_id TEXT NOT NULL,
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
is_active INTEGER NOT NULL DEFAULT 1,
|
||
|
||
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
|
||
);
|
||
|
||
-- 索引
|
||
CREATE INDEX idx_materials_project_id ON materials(project_id);
|
||
CREATE INDEX idx_materials_type ON materials(material_type);
|
||
CREATE INDEX idx_materials_name ON materials(name);
|
||
CREATE INDEX idx_materials_created_at ON materials(created_at);
|
||
CREATE UNIQUE INDEX idx_materials_path_project ON materials(path, project_id);
|
||
```
|
||
|
||
### 3. 模特表 (models)
|
||
```sql
|
||
CREATE TABLE models (
|
||
id TEXT PRIMARY KEY NOT NULL,
|
||
name TEXT NOT NULL,
|
||
stage_name TEXT,
|
||
gender TEXT NOT NULL CHECK (gender IN ('male', 'female', 'other')),
|
||
age INTEGER CHECK (age > 0 AND age < 150),
|
||
height INTEGER CHECK (height > 0 AND height < 300), -- 厘米
|
||
weight INTEGER CHECK (weight > 0 AND weight < 500), -- 公斤
|
||
measurements TEXT, -- JSON格式存储三围
|
||
description TEXT,
|
||
tags TEXT, -- JSON数组格式存储标签
|
||
avatar_path TEXT,
|
||
contact_info TEXT, -- JSON格式存储联系信息
|
||
social_media TEXT, -- JSON格式存储社交媒体
|
||
status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'archived')),
|
||
rating REAL CHECK (rating >= 1.0 AND rating <= 5.0),
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
is_active INTEGER NOT NULL DEFAULT 1
|
||
);
|
||
|
||
-- 索引
|
||
CREATE INDEX idx_models_name ON models(name);
|
||
CREATE INDEX idx_models_gender ON models(gender);
|
||
CREATE INDEX idx_models_status ON models(status);
|
||
CREATE INDEX idx_models_rating ON models(rating);
|
||
```
|
||
|
||
### 4. 模特照片表 (model_photos)
|
||
```sql
|
||
CREATE TABLE model_photos (
|
||
id TEXT PRIMARY KEY NOT NULL,
|
||
model_id TEXT NOT NULL,
|
||
photo_type TEXT NOT NULL CHECK (photo_type IN ('portrait', 'full_body', 'casual', 'formal', 'swimwear', 'other')),
|
||
path TEXT NOT NULL,
|
||
description TEXT,
|
||
is_primary INTEGER NOT NULL DEFAULT 0, -- 是否为主要照片
|
||
sort_order INTEGER NOT NULL DEFAULT 0,
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
is_active INTEGER NOT NULL DEFAULT 1,
|
||
|
||
FOREIGN KEY (model_id) REFERENCES models(id) ON DELETE CASCADE
|
||
);
|
||
|
||
-- 索引
|
||
CREATE INDEX idx_model_photos_model_id ON model_photos(model_id);
|
||
CREATE INDEX idx_model_photos_type ON model_photos(photo_type);
|
||
CREATE INDEX idx_model_photos_primary ON model_photos(is_primary);
|
||
CREATE UNIQUE INDEX idx_model_photos_path ON model_photos(path);
|
||
```
|
||
|
||
### 5. 模板表 (templates)
|
||
```sql
|
||
CREATE TABLE templates (
|
||
id TEXT PRIMARY KEY NOT NULL,
|
||
name TEXT NOT NULL,
|
||
description TEXT,
|
||
canvas_config TEXT NOT NULL, -- JSON格式存储画布配置
|
||
duration INTEGER NOT NULL, -- 模板总时长(微秒)
|
||
fps REAL NOT NULL,
|
||
materials TEXT, -- JSON格式存储模板素材
|
||
tracks TEXT, -- JSON格式存储轨道信息
|
||
import_status TEXT NOT NULL DEFAULT 'pending' CHECK (import_status IN ('pending', 'processing', 'completed', 'failed')),
|
||
source_file_path TEXT,
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
is_active INTEGER NOT NULL DEFAULT 1
|
||
);
|
||
|
||
-- 索引
|
||
CREATE INDEX idx_templates_name ON templates(name);
|
||
CREATE INDEX idx_templates_status ON templates(import_status);
|
||
CREATE INDEX idx_templates_created_at ON templates(created_at);
|
||
```
|
||
|
||
### 6. AI分类记录表 (video_classifications)
|
||
```sql
|
||
CREATE TABLE video_classifications (
|
||
id TEXT PRIMARY KEY NOT NULL,
|
||
material_id TEXT NOT NULL,
|
||
classification_result TEXT NOT NULL, -- JSON格式存储分类结果
|
||
confidence_score REAL,
|
||
processing_time INTEGER, -- 处理时间(毫秒)
|
||
ai_model_version TEXT,
|
||
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'failed')),
|
||
error_message TEXT,
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
|
||
FOREIGN KEY (material_id) REFERENCES materials(id) ON DELETE CASCADE
|
||
);
|
||
|
||
-- 索引
|
||
CREATE INDEX idx_video_classifications_material_id ON video_classifications(material_id);
|
||
CREATE INDEX idx_video_classifications_status ON video_classifications(status);
|
||
CREATE INDEX idx_video_classifications_created_at ON video_classifications(created_at);
|
||
```
|
||
|
||
### 7. 穿搭图片表 (outfit_images)
|
||
```sql
|
||
CREATE TABLE outfit_images (
|
||
id TEXT PRIMARY KEY NOT NULL,
|
||
model_id TEXT NOT NULL,
|
||
image_path TEXT NOT NULL,
|
||
prompt TEXT,
|
||
negative_prompt TEXT,
|
||
generation_params TEXT, -- JSON格式存储生成参数
|
||
is_favorite INTEGER NOT NULL DEFAULT 0,
|
||
tags TEXT, -- JSON数组格式存储标签
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
is_active INTEGER NOT NULL DEFAULT 1,
|
||
|
||
FOREIGN KEY (model_id) REFERENCES models(id) ON DELETE CASCADE
|
||
);
|
||
|
||
-- 索引
|
||
CREATE INDEX idx_outfit_images_model_id ON outfit_images(model_id);
|
||
CREATE INDEX idx_outfit_images_favorite ON outfit_images(is_favorite);
|
||
CREATE INDEX idx_outfit_images_created_at ON outfit_images(created_at);
|
||
CREATE UNIQUE INDEX idx_outfit_images_path ON outfit_images(image_path);
|
||
```
|
||
|
||
### 8. 对话记录表 (conversations)
|
||
```sql
|
||
CREATE TABLE conversations (
|
||
id TEXT PRIMARY KEY NOT NULL,
|
||
session_id TEXT NOT NULL,
|
||
role TEXT NOT NULL CHECK (role IN ('user', 'assistant', 'system')),
|
||
content TEXT NOT NULL,
|
||
metadata TEXT, -- JSON格式存储元数据
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
is_active INTEGER NOT NULL DEFAULT 1
|
||
);
|
||
|
||
-- 索引
|
||
CREATE INDEX idx_conversations_session_id ON conversations(session_id);
|
||
CREATE INDEX idx_conversations_role ON conversations(role);
|
||
CREATE INDEX idx_conversations_created_at ON conversations(created_at);
|
||
```
|
||
|
||
## 数据库迁移规范
|
||
|
||
### 迁移文件结构
|
||
```
|
||
migrations/
|
||
├── 001_initial_schema.sql
|
||
├── 002_add_model_photos.sql
|
||
├── 003_add_video_classifications.sql
|
||
├── 004_add_outfit_images.sql
|
||
└── 005_add_conversations.sql
|
||
```
|
||
|
||
### 迁移脚本模板
|
||
```sql
|
||
-- Migration: 001_initial_schema.sql
|
||
-- Description: 创建初始数据库结构
|
||
-- Date: 2024-01-01
|
||
|
||
BEGIN TRANSACTION;
|
||
|
||
-- 创建项目表
|
||
CREATE TABLE projects (
|
||
-- 表结构定义
|
||
);
|
||
|
||
-- 创建索引
|
||
CREATE INDEX idx_projects_name ON projects(name);
|
||
|
||
-- 插入初始数据（如果需要）
|
||
INSERT INTO projects (id, name, path) VALUES
|
||
('default', 'Default Project', '/default/path');
|
||
|
||
COMMIT;
|
||
```
|
||
|
||
### 迁移管理
|
||
```rust
|
||
pub struct DatabaseMigration {
|
||
version: u32,
|
||
description: String,
|
||
sql: String,
|
||
}
|
||
|
||
impl Database {
|
||
pub async fn migrate(&self) -> Result<()> {
|
||
// 创建迁移记录表
|
||
self.create_migration_table().await?;
|
||
|
||
// 获取当前版本
|
||
let current_version = self.get_current_version().await?;
|
||
|
||
// 执行待处理的迁移
|
||
let migrations = self.get_pending_migrations(current_version)?;
|
||
|
||
for migration in migrations {
|
||
self.execute_migration(&migration).await?;
|
||
}
|
||
|
||
Ok(())
|
||
}
|
||
}
|
||
```
|
||
|
||
## 查询优化规范
|
||
|
||
### 索引策略
|
||
```sql
|
||
-- 单列索引
|
||
CREATE INDEX idx_materials_project_id ON materials(project_id);
|
||
|
||
-- 复合索引
|
||
CREATE INDEX idx_materials_project_type ON materials(project_id, material_type);
|
||
|
||
-- 唯一索引
|
||
CREATE UNIQUE INDEX idx_projects_path ON projects(path);
|
||
|
||
-- 部分索引
|
||
CREATE INDEX idx_active_materials ON materials(project_id) WHERE is_active = 1;
|
||
```
|
||
|
||
### 查询优化
|
||
```sql
|
||
-- ✅ 使用索引的查询
|
||
SELECT * FROM materials
|
||
WHERE project_id = ? AND material_type = 'video'
|
||
ORDER BY created_at DESC
|
||
LIMIT 20;
|
||
|
||
-- ✅ 使用EXISTS而不是IN
|
||
SELECT * FROM projects p
|
||
WHERE EXISTS (
|
||
SELECT 1 FROM materials m
|
||
WHERE m.project_id = p.id AND m.is_active = 1
|
||
);
|
||
|
||
-- ✅ 使用适当的JOIN
|
||
SELECT p.name, COUNT(m.id) as material_count
|
||
FROM projects p
|
||
LEFT JOIN materials m ON p.id = m.project_id AND m.is_active = 1
|
||
WHERE p.is_active = 1
|
||
GROUP BY p.id, p.name;
|
||
```
|
||
|
||
### 分页查询
|
||
```sql
|
||
-- 基于OFFSET的分页（适用于小数据集）
|
||
SELECT * FROM materials
|
||
WHERE project_id = ?
|
||
ORDER BY created_at DESC
|
||
LIMIT ? OFFSET ?;
|
||
|
||
-- 基于游标的分页（适用于大数据集）
|
||
SELECT * FROM materials
|
||
WHERE project_id = ? AND created_at < ?
|
||
ORDER BY created_at DESC
|
||
LIMIT ?;
|
||
```
|
||
|
||
## 数据完整性规范
|
||
|
||
### 外键约束
|
||
```sql
|
||
-- 级联删除
|
||
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
|
||
|
||
-- 限制删除
|
||
FOREIGN KEY (model_id) REFERENCES models(id) ON DELETE RESTRICT
|
||
|
||
-- 设置NULL
|
||
FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
|
||
```
|
||
|
||
### 检查约束
|
||
```sql
|
||
-- 数值范围检查
|
||
CHECK (age > 0 AND age < 150)
|
||
|
||
-- 枚举值检查
|
||
CHECK (status IN ('active', 'inactive', 'archived'))
|
||
|
||
-- 字符串长度检查
|
||
CHECK (length(name) > 0 AND length(name) <= 255)
|
||
```
|
||
|
||
### 触发器
|
||
```sql
|
||
-- 自动更新时间戳
|
||
CREATE TRIGGER update_materials_timestamp
|
||
AFTER UPDATE ON materials
|
||
FOR EACH ROW
|
||
BEGIN
|
||
UPDATE materials SET updated_at = datetime('now') WHERE id = NEW.id;
|
||
END;
|
||
|
||
-- 软删除触发器
|
||
CREATE TRIGGER soft_delete_materials
|
||
INSTEAD OF DELETE ON materials
|
||
FOR EACH ROW
|
||
BEGIN
|
||
UPDATE materials SET is_active = 0, updated_at = datetime('now') WHERE id = OLD.id;
|
||
END;
|
||
```
|
||
|
||
## 备份和恢复规范
|
||
|
||
### 备份策略
|
||
```rust
|
||
pub struct DatabaseBackup {
|
||
source_path: PathBuf,
|
||
backup_dir: PathBuf,
|
||
}
|
||
|
||
impl DatabaseBackup {
|
||
pub async fn create_backup(&self) -> Result<PathBuf> {
|
||
let timestamp = Utc::now().format("%Y%m%d_%H%M%S");
|
||
let backup_name = format!("mixvideo_backup_{}.db", timestamp);
|
||
let backup_path = self.backup_dir.join(backup_name);
|
||
|
||
// 使用SQLite的备份API
|
||
let source = Connection::open(&self.source_path)?;
|
||
let backup = Connection::open(&backup_path)?;
|
||
|
||
let backup_handle = rusqlite::backup::Backup::new(&source, &backup)?;
|
||
backup_handle.run_to_completion(5, Duration::from_millis(250), None)?;
|
||
|
||
Ok(backup_path)
|
||
}
|
||
|
||
pub async fn restore_backup(&self, backup_path: &Path) -> Result<()> {
|
||
// 验证备份文件
|
||
self.validate_backup(backup_path)?;
|
||
|
||
// 创建当前数据库的备份
|
||
let current_backup = self.create_backup().await?;
|
||
|
||
// 恢复数据库
|
||
std::fs::copy(backup_path, &self.source_path)?;
|
||
|
||
// 验证恢复的数据库
|
||
self.validate_database().await?;
|
||
|
||
Ok(())
|
||
}
|
||
}
|
||
```
|
||
|
||
### 数据验证
|
||
```sql
|
||
-- 检查数据完整性
|
||
PRAGMA integrity_check;
|
||
|
||
-- 检查外键约束
|
||
PRAGMA foreign_key_check;
|
||
|
||
-- 分析数据库统计信息
|
||
ANALYZE;
|
||
```
|
||
|
||
## 性能监控规范
|
||
|
||
### 查询性能分析
|
||
```sql
|
||
-- 启用查询计划分析
|
||
EXPLAIN QUERY PLAN SELECT * FROM materials WHERE project_id = ?;
|
||
|
||
-- 查看表统计信息
|
||
SELECT name, tbl_name, sql FROM sqlite_master WHERE type = 'index';
|
||
|
||
-- 检查表大小
|
||
SELECT
|
||
name,
|
||
COUNT(*) as row_count,
|
||
SUM(length(sql)) as size_estimate
|
||
FROM sqlite_master
|
||
WHERE type = 'table'
|
||
GROUP BY name;
|
||
```
|
||
|
||
### 性能监控
|
||
```rust
|
||
pub struct DatabaseMetrics {
|
||
query_times: HashMap<String, Vec<Duration>>,
|
||
connection_pool_stats: ConnectionPoolStats,
|
||
}
|
||
|
||
impl DatabaseMetrics {
|
||
pub fn record_query_time(&mut self, query: &str, duration: Duration) {
|
||
self.query_times
|
||
.entry(query.to_string())
|
||
.or_insert_with(Vec::new)
|
||
.push(duration);
|
||
}
|
||
|
||
pub fn get_slow_queries(&self, threshold: Duration) -> Vec<(String, Duration)> {
|
||
self.query_times
|
||
.iter()
|
||
.filter_map(|(query, times)| {
|
||
let avg_time = times.iter().sum::<Duration>() / times.len() as u32;
|
||
if avg_time > threshold {
|
||
Some((query.clone(), avg_time))
|
||
} else {
|
||
None
|
||
}
|
||
})
|
||
.collect()
|
||
}
|
||
}
|
||
```
|
||
|
||
## 安全规范
|
||
|
||
### 数据加密
|
||
```rust
|
||
// 敏感数据加密存储
|
||
pub fn encrypt_sensitive_data(data: &str, key: &[u8]) -> Result<String> {
|
||
// 使用AES加密
|
||
let cipher = Aes256Gcm::new(key.into());
|
||
let nonce = Aes256Gcm::generate_nonce(&mut OsRng);
|
||
let ciphertext = cipher.encrypt(&nonce, data.as_bytes())?;
|
||
|
||
// 组合nonce和密文
|
||
let mut result = nonce.to_vec();
|
||
result.extend_from_slice(&ciphertext);
|
||
|
||
Ok(base64::encode(result))
|
||
}
|
||
```
|
||
|
||
### 访问控制
|
||
```sql
|
||
-- 创建只读视图
|
||
CREATE VIEW materials_readonly AS
|
||
SELECT id, name, material_type, size, created_at
|
||
FROM materials
|
||
WHERE is_active = 1;
|
||
|
||
-- 限制敏感字段访问
|
||
CREATE VIEW models_public AS
|
||
SELECT id, name, stage_name, gender, description, tags, avatar_path, rating
|
||
FROM models
|
||
WHERE is_active = 1;
|
||
```
|
||
|
||
### 审计日志
|
||
```sql
|
||
CREATE TABLE audit_logs (
|
||
id TEXT PRIMARY KEY NOT NULL,
|
||
table_name TEXT NOT NULL,
|
||
operation TEXT NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
|
||
record_id TEXT NOT NULL,
|
||
old_values TEXT, -- JSON格式
|
||
new_values TEXT, -- JSON格式
|
||
user_id TEXT,
|
||
timestamp TEXT NOT NULL DEFAULT (datetime('now'))
|
||
);
|
||
```
|