27 lines
691 B
PL/PgSQL
27 lines
691 B
PL/PgSQL
-- Author: fruit.com.cn
|
|
-- 将已有零绑定转码任务按分集视频 URL 回填（幂等，仅处理未完成任务）
|
|
|
|
BEGIN;
|
|
|
|
UPDATE transcode_task t
|
|
SET drama_id = e.drama_id,
|
|
episode_id = e.id
|
|
FROM drama_episode e
|
|
WHERE t.deleted = 0
|
|
AND e.deleted = 0
|
|
AND t.status IN ('QUEUED', 'RUNNING')
|
|
AND t.episode_id = 0
|
|
AND e.drama_id > 0
|
|
AND split_part(e.video_url, '?', 1) = split_part(t.source_url, '?', 1);
|
|
|
|
UPDATE drama_episode e
|
|
SET video_asset_id = t.media_asset_id
|
|
FROM transcode_task t
|
|
WHERE t.deleted = 0
|
|
AND t.episode_id = e.id
|
|
AND t.media_asset_id > 0
|
|
AND e.video_asset_id = 0
|
|
AND split_part(e.video_url, '?', 1) = split_part(t.source_url, '?', 1);
|
|
|
|
COMMIT;
|