70 lines
2.4 KiB
SQL
70 lines
2.4 KiB
SQL
-- 将历史 shots.status='generating' 回算为静态状态。
|
|
-- 说明：
|
|
-- 1. 新逻辑下 shot.status 仅表示“信息提取确认状态”：pending / ready
|
|
-- 2. 运行中的生成任务应从 GenerationTask / GenerationTaskLink 动态聚合，不再写入 shots.status
|
|
-- 3. dialogue candidates 表在部分环境可能尚未创建，因此这里按“存在才参与回算”处理
|
|
|
|
SET @has_dialogue_candidates_table = (
|
|
SELECT COUNT(*)
|
|
FROM information_schema.TABLES
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'shot_extracted_dialogue_candidates'
|
|
);
|
|
|
|
SET @normalize_shot_status_sql = IF(
|
|
@has_dialogue_candidates_table > 0,
|
|
"
|
|
UPDATE shots AS s
|
|
LEFT JOIN (
|
|
SELECT
|
|
shot_id,
|
|
COUNT(*) AS total_count,
|
|
SUM(CASE WHEN candidate_status = 'pending' THEN 1 ELSE 0 END) AS pending_count
|
|
FROM shot_extracted_candidates
|
|
GROUP BY shot_id
|
|
) AS ac
|
|
ON ac.shot_id = s.id
|
|
LEFT JOIN (
|
|
SELECT
|
|
shot_id,
|
|
COUNT(*) AS total_count,
|
|
SUM(CASE WHEN candidate_status = 'pending' THEN 1 ELSE 0 END) AS pending_count
|
|
FROM shot_extracted_dialogue_candidates
|
|
GROUP BY shot_id
|
|
) AS dc
|
|
ON dc.shot_id = s.id
|
|
SET s.status = CASE
|
|
WHEN s.skip_extraction = 1 THEN 'ready'
|
|
WHEN s.last_extracted_at IS NULL THEN 'pending'
|
|
WHEN COALESCE(ac.total_count, 0) = 0 AND COALESCE(dc.total_count, 0) = 0 THEN 'ready'
|
|
WHEN COALESCE(ac.pending_count, 0) = 0 AND COALESCE(dc.pending_count, 0) = 0 THEN 'ready'
|
|
ELSE 'pending'
|
|
END
|
|
WHERE s.status = 'generating'
|
|
",
|
|
"
|
|
UPDATE shots AS s
|
|
LEFT JOIN (
|
|
SELECT
|
|
shot_id,
|
|
COUNT(*) AS total_count,
|
|
SUM(CASE WHEN candidate_status = 'pending' THEN 1 ELSE 0 END) AS pending_count
|
|
FROM shot_extracted_candidates
|
|
GROUP BY shot_id
|
|
) AS ac
|
|
ON ac.shot_id = s.id
|
|
SET s.status = CASE
|
|
WHEN s.skip_extraction = 1 THEN 'ready'
|
|
WHEN s.last_extracted_at IS NULL THEN 'pending'
|
|
WHEN COALESCE(ac.total_count, 0) = 0 THEN 'ready'
|
|
WHEN COALESCE(ac.pending_count, 0) = 0 THEN 'ready'
|
|
ELSE 'pending'
|
|
END
|
|
WHERE s.status = 'generating'
|
|
"
|
|
);
|
|
|
|
PREPARE normalize_shot_status_stmt FROM @normalize_shot_status_sql;
|
|
EXECUTE normalize_shot_status_stmt;
|
|
DEALLOCATE PREPARE normalize_shot_status_stmt;
|