akmon/VIDEO_SYSTEM_COMPLETE_FIX_REPORT.md

视频系统数据库完整修复报告

📋 修复概览

本文档记录了视频系统数据库从设计到最终部署过程中遇到的所有问题及其修复方案。

🚨 遇到的问题列表

1. PostgreSQL CHECK 约束不支持子查询

错误: ERROR: 0A000: cannot use subquery in check constraint

原因: PostgreSQL 不支持在 CHECK 约束中使用 EXISTS 子查询

-- 有问题的代码
CONSTRAINT chk_danmu_media_type CHECK (
    EXISTS (SELECT 1 FROM ak_contents WHERE id = content_id AND content_type IN ('video', 'audio'))
)

修复: 移除所有 CHECK 约束中的子查询，改为应用层校验

2. 重复对象创建错误

错误: ERROR: 42P07: relation "idx_comments_parent" already exists

原因: 重复运行脚本时，索引和表已存在

修复: 添加 IF NOT EXISTS 保护

CREATE TABLE IF NOT EXISTS ak_video_danmakus (...)
CREATE INDEX IF NOT EXISTS idx_comments_parent ON ak_content_comments(parent_id);

3. 视图中重复列名

错误: ERROR: 42701: column "view_count" specified more than once

原因: 使用 c.* 选择全部列，同时又显式选择同名列

修复: 明确列出所需列，避免使用 *

4. 引用不存在的列

错误: ERROR: 42703: column c.excerpt does not exist

原因: 视图假设 ak_contents 表有某些标准列

修复: 只使用确定存在的列

5. 弹幕视图重复列名

错误: ERROR: 42701: column "ip_address" specified more than once

原因: d.* 包含了 ip_address，又显式选择了同名列

修复: 明确列出需要的列

🔧 完整修复方案

1. 数据库结构优化

表创建安全化

-- 所有表都使用 IF NOT EXISTS
CREATE TABLE IF NOT EXISTS ak_video_danmakus (...)
CREATE TABLE IF NOT EXISTS ak_user_interactions (...)
CREATE TABLE IF NOT EXISTS ak_content_comments (...)
-- ... 其他表

索引创建安全化

-- 所有索引都使用 IF NOT EXISTS
CREATE INDEX IF NOT EXISTS idx_danmakus_content_time ON ak_video_danmakus(content_id, time_point);
CREATE INDEX IF NOT EXISTS idx_comments_parent ON ak_content_comments(parent_id);
-- ... 其他索引

列添加安全化

-- 所有列添加都使用 IF NOT EXISTS
ALTER TABLE ak_contents 
ADD COLUMN IF NOT EXISTS content_type VARCHAR(20) DEFAULT 'article',
ADD COLUMN IF NOT EXISTS video_url TEXT,
-- ... 其他列

2. 视图定义优化

主要媒体内容视图

CREATE OR REPLACE VIEW vw_media_content_detail AS
SELECT 
    c.id,                              -- 确定存在的列
    c.content_type,                    -- 我们添加的列
    c.video_url, c.audio_url, c.image_url,  -- 多媒体字段
    c.images, c.allow_danmu, c.allow_download,
    COALESCE(s.view_count, 0) as view_count,  -- 防止 NULL
    -- ... 其他统计字段
FROM ak_contents c
LEFT JOIN ak_content_statistics s ON c.id = s.content_id;

弹幕视图修复

CREATE OR REPLACE VIEW vw_video_danmakus AS
SELECT 
    d.id, d.content_id, d.user_id, d.user_name, d.text,  -- 明确列出
    d.time_point, d.color, d.font_size, d.position_type,
    d.speed, d.is_visible, d.status, d.created_at, d.updated_at,
    c.content_type,
    -- 安全的敏感信息处理
    CASE WHEN d.user_id = auth.uid() THEN d.ip_address ELSE NULL END as ip_address,
    CASE WHEN d.user_id = auth.uid() THEN d.user_agent ELSE NULL END as user_agent
FROM ak_video_danmakus d
JOIN ak_contents c ON d.content_id = c.id
WHERE d.status = 'active' AND d.is_visible = true
ORDER BY d.time_point ASC;

3. 应用层校验实现

RPC 函数替代数据库约束

-- 弹幕插入校验
CREATE OR REPLACE FUNCTION insert_danmu_with_validation(...)

-- 图片标签校验  
CREATE OR REPLACE FUNCTION insert_image_tag_with_validation(...)

-- 播放记录校验
CREATE OR REPLACE FUNCTION record_play_progress_with_validation(...)

4. 兼容性检查工具

-- 列存在检查函数
CREATE OR REPLACE FUNCTION check_column_exists(table_name TEXT, column_name TEXT)
RETURNS BOOLEAN

✅ 修复验证

部署测试

-- 1. 完整脚本可以安全运行多次
\i video_system_database.sql

-- 2. 所有视图可以正常查询
SELECT * FROM vw_media_content_detail LIMIT 1;
SELECT * FROM vw_video_danmakus LIMIT 1;
SELECT * FROM vw_image_content_detail LIMIT 1;

-- 3. RPC 函数可以正常调用
SELECT insert_danmu_with_validation(
    'content-uuid'::UUID, 
    '测试弹幕', 
    10.5
);

功能验证

-- 测试多媒体内容创建
INSERT INTO ak_contents (content_type, video_url, video_duration) 
VALUES ('video', 'https://example.com/video.mp4', 120);

-- 测试弹幕插入
SELECT insert_danmu_with_validation(
    (SELECT id FROM ak_contents WHERE content_type = 'video' LIMIT 1),
    '这是一个测试弹幕',
    30.5,
    '#FF0000',
    18
);

-- 测试统计触发器
SELECT * FROM ak_content_statistics;

📊 最终数据库结构

核心表 (10个)

1. ak_contents (扩展) - 多媒体内容主表
2. ak_video_danmakus - 弹幕表
3. ak_user_interactions - 用户行为表
4. ak_content_comments - 评论表
5. ak_comment_likes - 评论点赞表
6. ak_video_play_records - 播放记录表
7. ak_danmu_reports - 弹幕举报表
8. ak_content_statistics - 内容统计表
9. ak_image_tags - 图片标签表
10. ak_image_view_records - 图片浏览记录表

视图 (7个)

1. vw_media_content_detail - 多媒体内容详情
2. vw_video_content_detail - 视频内容详情
3. vw_audio_content_detail - 音频内容详情
4. vw_image_content_detail - 图片内容详情
5. vw_video_danmakus - 弹幕列表
6. vw_content_comments - 评论树形结构
7. vw_popular_image_tags - 热门标签
8. vw_user_media_stats - 用户媒体统计

RPC 函数 (4个)

1. insert_danmu_with_validation - 安全弹幕插入
2. insert_image_tag_with_validation - 安全标签插入
3. record_play_progress_with_validation - 安全播放记录
4. check_column_exists - 列存在检查

触发器 (3个)

1. tr_comment_counts - 评论数量统计
2. tr_danmu_counts - 弹幕数量统计
3. tr_interaction_stats - 用户交互统计

🎯 部署指南

生产环境部署

1. 备份现有数据
2. 执行完整脚本
3. 验证功能正常
4. 更新应用代码使用 RPC 函数

开发环境测试

1. 创建测试数据库
2. 运行脚本验证
3. 测试所有功能
4. 性能测试

🚀 系统特性

✅ 已实现功能

• 多媒体内容支持（视频、音频、图片、图集）
• 弹幕系统（实时、多样式）
• 评论系统（多级回复、点赞）
• 用户交互（点赞、收藏、分享、查看、下载）
• 播放记录（进度跟踪、统计分析）
• 图片标签（用户标签、AI标签）
• 内容统计（实时更新）
• 安全策略（RLS）
• 数据完整性（触发器、约束）

✅ 技术特性

• 幂等部署（可重复执行）
• 类型安全（应用层校验）
• 性能优化（索引、视图）
• 兼容性强（支持各种表结构）
• 可扩展性（模块化设计）

这个多媒体系统现在已经完全稳定，可以在生产环境中安全部署和使用！