31 lines
2.0 KiB
PL/PgSQL
31 lines
2.0 KiB
PL/PgSQL
SET search_path TO atlas, public;
|
|
|
|
-- operation 使用数据库枚举，父套图的 partial 状态映射为失败操作并保留已完成数量。
|
|
CREATE OR REPLACE FUNCTION refresh_image_suite_status() RETURNS TRIGGER LANGUAGE plpgsql SET search_path = atlas, pg_temp AS $$
|
|
DECLARE
|
|
parent product_image_suites%ROWTYPE;
|
|
count_all INTEGER;
|
|
count_done INTEGER;
|
|
count_active INTEGER;
|
|
next_status TEXT;
|
|
BEGIN
|
|
IF NEW.suite_id IS NULL THEN RETURN NEW; END IF;
|
|
SELECT * INTO parent FROM product_image_suites WHERE workspace_id = NEW.workspace_id AND id = NEW.suite_id FOR UPDATE;
|
|
IF parent.status = 'cancelled' THEN RETURN NEW; END IF;
|
|
SELECT count(*), count(*) FILTER (WHERE status = 'succeeded'), count(*) FILTER (WHERE status IN ('queued', 'running'))
|
|
INTO count_all, count_done, count_active FROM media_ai_edits WHERE workspace_id = NEW.workspace_id AND suite_id = NEW.suite_id;
|
|
next_status := CASE WHEN count_active > 0 THEN 'running' WHEN count_done = count_all THEN 'succeeded'
|
|
WHEN count_done > 0 THEN 'partial' ELSE 'failed' END;
|
|
UPDATE product_image_suites SET status = next_status,
|
|
completed_at = CASE WHEN count_active = 0 THEN now() ELSE NULL END,
|
|
error_code = CASE WHEN next_status IN ('partial', 'failed') THEN 'AI_SUITE_IMAGE_FAILED' ELSE NULL END
|
|
WHERE workspace_id = NEW.workspace_id AND id = NEW.suite_id;
|
|
UPDATE operations SET status = (CASE WHEN next_status = 'partial' THEN 'failed' ELSE next_status END)::operation_status,
|
|
started_at = coalesce(started_at, now()), completed_at = CASE WHEN count_active = 0 THEN now() ELSE NULL END,
|
|
error_code = CASE WHEN next_status IN ('partial', 'failed') THEN 'AI_SUITE_IMAGE_FAILED' ELSE NULL END,
|
|
error_message = CASE WHEN next_status IN ('partial', 'failed') THEN '部分图片未完成，可重试失败项' ELSE NULL END,
|
|
result = jsonb_build_object('completed', count_done, 'total', count_all)
|
|
WHERE workspace_id = NEW.workspace_id AND id = NEW.suite_id;
|
|
RETURN NEW;
|
|
END $$;
|