115 lines
5.1 KiB
SQL
115 lines
5.1 KiB
SQL
-- ============================================
|
||
-- 任务状态级联更新触发器
|
||
--
|
||
-- 功能：当 task_status 表状态更新时，自动同步到：
|
||
-- - task_queue
|
||
-- - user_works
|
||
-- - text_to_video_tasks
|
||
-- - image_to_video_tasks
|
||
-- - storyboard_video_tasks
|
||
--
|
||
-- 执行方式：在 MySQL 客户端中执行此脚本
|
||
-- ============================================
|
||
|
||
-- 选择数据库
|
||
USE aigc_platform;
|
||
|
||
-- 先删除已存在的触发器
|
||
DROP TRIGGER IF EXISTS trg_task_status_update;
|
||
|
||
-- 修改分隔符
|
||
DELIMITER //
|
||
|
||
-- 创建触发器
|
||
-- 状态枚举说明：
|
||
-- task_status/task_queue: PENDING, PROCESSING, COMPLETED, FAILED, CANCELLED, TIMEOUT
|
||
-- user_works: PROCESSING, COMPLETED, FAILED, DELETED (缺少 PENDING, CANCELLED, TIMEOUT)
|
||
-- 业务表: PENDING, PROCESSING, COMPLETED, FAILED, CANCELLED (缺少 TIMEOUT)
|
||
--
|
||
-- 注意：分镜图(_image后缀)在task_status表中没有独立记录，无需处理
|
||
CREATE TRIGGER trg_task_status_update
|
||
AFTER UPDATE ON task_status
|
||
FOR EACH ROW
|
||
BEGIN
|
||
IF NEW.status <> OLD.status THEN
|
||
|
||
-- 1. 更新 task_queue 表（状态枚举一致，直接同步）
|
||
UPDATE task_queue
|
||
SET status = NEW.status,
|
||
updated_at = NOW(),
|
||
error_message = CASE WHEN NEW.status IN ('FAILED', 'TIMEOUT') THEN NEW.error_message ELSE error_message END
|
||
WHERE task_id = NEW.task_id;
|
||
|
||
-- 2. 更新 user_works 表（需要状态映射）
|
||
-- PENDING -> PROCESSING, CANCELLED/TIMEOUT -> FAILED
|
||
UPDATE user_works
|
||
SET status = CASE
|
||
WHEN NEW.status IN ('PENDING', 'PROCESSING') THEN 'PROCESSING'
|
||
WHEN NEW.status = 'COMPLETED' THEN 'COMPLETED'
|
||
WHEN NEW.status IN ('FAILED', 'CANCELLED', 'TIMEOUT') THEN 'FAILED'
|
||
ELSE status
|
||
END,
|
||
updated_at = NOW(),
|
||
result_url = CASE WHEN NEW.status = 'COMPLETED' AND NEW.result_url IS NOT NULL THEN NEW.result_url ELSE result_url END
|
||
WHERE task_id = NEW.task_id;
|
||
|
||
-- 3. 更新业务任务表（TIMEOUT 映射为 FAILED）
|
||
|
||
-- 文生视频 (txt2vid_*)
|
||
IF NEW.task_id LIKE 'txt2vid_%' THEN
|
||
UPDATE text_to_video_tasks
|
||
SET status = CASE WHEN NEW.status = 'TIMEOUT' THEN 'FAILED' ELSE NEW.status END,
|
||
updated_at = NOW(),
|
||
progress = CASE WHEN NEW.status = 'COMPLETED' THEN 100 ELSE progress END,
|
||
completed_at = CASE WHEN NEW.status IN ('COMPLETED', 'FAILED', 'CANCELLED', 'TIMEOUT') THEN NOW() ELSE completed_at END,
|
||
error_message = CASE WHEN NEW.status IN ('FAILED', 'TIMEOUT') THEN COALESCE(NEW.error_message, '任务超时') ELSE error_message END,
|
||
result_url = CASE WHEN NEW.status = 'COMPLETED' AND NEW.result_url IS NOT NULL THEN NEW.result_url ELSE result_url END
|
||
WHERE task_id = NEW.task_id;
|
||
END IF;
|
||
|
||
-- 图生视频 (img2vid_*)
|
||
IF NEW.task_id LIKE 'img2vid_%' THEN
|
||
UPDATE image_to_video_tasks
|
||
SET status = CASE WHEN NEW.status = 'TIMEOUT' THEN 'FAILED' ELSE NEW.status END,
|
||
updated_at = NOW(),
|
||
progress = CASE WHEN NEW.status = 'COMPLETED' THEN 100 ELSE progress END,
|
||
completed_at = CASE WHEN NEW.status IN ('COMPLETED', 'FAILED', 'CANCELLED', 'TIMEOUT') THEN NOW() ELSE completed_at END,
|
||
error_message = CASE WHEN NEW.status IN ('FAILED', 'TIMEOUT') THEN COALESCE(NEW.error_message, '任务超时') ELSE error_message END,
|
||
result_url = CASE WHEN NEW.status = 'COMPLETED' AND NEW.result_url IS NOT NULL THEN NEW.result_url ELSE result_url END
|
||
WHERE task_id = NEW.task_id;
|
||
END IF;
|
||
|
||
-- 分镜视频 (storyboard_*)
|
||
IF NEW.task_id LIKE 'storyboard_%' THEN
|
||
UPDATE storyboard_video_tasks
|
||
SET status = CASE WHEN NEW.status = 'TIMEOUT' THEN 'FAILED' ELSE NEW.status END,
|
||
updated_at = NOW(),
|
||
progress = CASE WHEN NEW.status = 'COMPLETED' THEN 100 ELSE progress END,
|
||
completed_at = CASE WHEN NEW.status IN ('COMPLETED', 'FAILED', 'CANCELLED', 'TIMEOUT') THEN NOW() ELSE completed_at END,
|
||
error_message = CASE WHEN NEW.status IN ('FAILED', 'TIMEOUT') THEN COALESCE(NEW.error_message, '任务超时') ELSE error_message END,
|
||
result_url = CASE WHEN NEW.status = 'COMPLETED' AND NEW.result_url IS NOT NULL THEN NEW.result_url ELSE result_url END
|
||
WHERE task_id = NEW.task_id;
|
||
END IF;
|
||
|
||
END IF;
|
||
END//
|
||
|
||
-- 恢复分隔符
|
||
DELIMITER ;
|
||
|
||
-- 验证触发器已创建
|
||
SHOW TRIGGERS LIKE 'task_status';
|
||
|
||
-- ============================================
|
||
-- 使用说明：
|
||
--
|
||
-- 1. 连接到 MySQL 数据库
|
||
-- 2. 选择对应的数据库: USE your_database_name;
|
||
-- 3. 执行此脚本
|
||
--
|
||
-- 注意事项：
|
||
-- - 触发器会在 task_status 表的 status 字段更新时自动执行
|
||
-- - 确保所有表都有 task_id 字段作为关联键
|
||
-- - 状态值使用字符串存储（如 'COMPLETED', 'FAILED' 等）
|
||
-- ============================================
|