162 lines
8.2 KiB
SQL
162 lines
8.2 KiB
SQL
-- Upgrade the managed Prompt releases used by app blueprint and flow generation.
|
||
-- Idempotent: a template is only versioned when its current published content
|
||
-- does not already contain the resource-detail binding rule.
|
||
|
||
DELIMITER $$
|
||
|
||
DROP PROCEDURE IF EXISTS upgrade_resource_detail_binding_prompts_20260715$$
|
||
|
||
CREATE PROCEDURE upgrade_resource_detail_binding_prompts_20260715()
|
||
BEGIN
|
||
DECLARE v_template_id BIGINT;
|
||
DECLARE v_current_version_id BIGINT;
|
||
DECLARE v_new_version_id BIGINT;
|
||
DECLARE v_version_no INT;
|
||
DECLARE v_version_label VARCHAR(50);
|
||
DECLARE v_generate_type VARCHAR(50);
|
||
DECLARE v_prompt_code VARCHAR(100);
|
||
DECLARE v_system_prompt LONGTEXT;
|
||
DECLARE v_user_prompt_contract VARCHAR(255);
|
||
DECLARE v_user_prompt_template LONGTEXT;
|
||
DECLARE v_target_prompt LONGTEXT;
|
||
DECLARE v_provider_code VARCHAR(50);
|
||
DECLARE v_model VARCHAR(100);
|
||
DECLARE v_pipeline_release_code VARCHAR(100);
|
||
DECLARE v_content_hash CHAR(64);
|
||
DECLARE v_anchor TEXT;
|
||
DECLARE v_rule TEXT;
|
||
|
||
DECLARE EXIT HANDLER FOR SQLEXCEPTION
|
||
BEGIN
|
||
ROLLBACK;
|
||
RESIGNAL;
|
||
END;
|
||
|
||
START TRANSACTION;
|
||
|
||
SELECT t.prompt_template_id, t.current_version_id, t.generate_type, t.prompt_code,
|
||
v.system_prompt, v.user_prompt_contract, v.user_prompt_template,
|
||
v.provider_code, v.model, v.pipeline_release_code
|
||
INTO v_template_id, v_current_version_id, v_generate_type, v_prompt_code,
|
||
v_system_prompt, v_user_prompt_contract, v_user_prompt_template,
|
||
v_provider_code, v_model, v_pipeline_release_code
|
||
FROM factory_prompt_template t
|
||
JOIN factory_prompt_version v ON v.prompt_version_id = t.current_version_id
|
||
WHERE t.prompt_code = 'front.app_blueprint'
|
||
FOR UPDATE;
|
||
|
||
SET v_rule = '需求中只要存在用户针对单条资源记录发起并创建业务记录的动作（例如预约、申请、借用、报名、下单），就必须为资源列表创建同 tableName 的 detail 子页面；parentPageCode 指向资源列表页，path 使用列表路径加 /:id。此类动作后续必须落在详情页主操作区，不能依赖列表行操作。';
|
||
IF LOCATE('同 tableName 的 detail 子页面', v_user_prompt_template) = 0 THEN
|
||
SET v_anchor = 'frontendMenus 顶层菜单应指向 pageCode；详情页作为子页面，不创建顶层详情菜单。';
|
||
IF LOCATE(v_anchor, v_user_prompt_template) > 0 THEN
|
||
SET v_target_prompt = REPLACE(v_user_prompt_template, v_anchor,
|
||
CONCAT(v_anchor, CHAR(10), v_rule));
|
||
ELSE
|
||
SET v_target_prompt = CONCAT(v_user_prompt_template, CHAR(10), v_rule);
|
||
END IF;
|
||
|
||
SELECT COALESCE(MAX(version_no), 0) + 1
|
||
INTO v_version_no
|
||
FROM factory_prompt_version
|
||
WHERE prompt_template_id = v_template_id
|
||
FOR UPDATE;
|
||
SET v_version_label = CONCAT('V', v_version_no);
|
||
SET v_content_hash = SHA2(CONCAT(
|
||
'{"generateType":', JSON_QUOTE(v_generate_type),
|
||
',"promptCode":', JSON_QUOTE(v_prompt_code),
|
||
',"systemPrompt":', JSON_QUOTE(v_system_prompt),
|
||
',"userPromptContract":', JSON_QUOTE(v_user_prompt_contract),
|
||
',"userPromptTemplate":', JSON_QUOTE(v_target_prompt),
|
||
',"version":', JSON_QUOTE(v_version_label), '}'), 256);
|
||
|
||
INSERT INTO factory_prompt_version
|
||
(prompt_template_id, version_no, version_label, system_prompt,
|
||
user_prompt_contract, user_prompt_template, provider_code, model,
|
||
content_hash, status, parent_version_id, pipeline_release_code,
|
||
published_by, published_at, create_by, create_time, remark)
|
||
VALUES
|
||
(v_template_id, v_version_no, v_version_label, v_system_prompt,
|
||
v_user_prompt_contract, v_target_prompt, v_provider_code, v_model,
|
||
v_content_hash, 'DRAFT', v_current_version_id, v_pipeline_release_code,
|
||
'', NULL, 'system-upgrade', NOW(),
|
||
'Resource initiating actions require a same-table detail page');
|
||
SET v_new_version_id = LAST_INSERT_ID();
|
||
|
||
UPDATE factory_prompt_version
|
||
SET status = 'RETIRED'
|
||
WHERE prompt_template_id = v_template_id
|
||
AND status = 'PUBLISHED'
|
||
AND prompt_version_id <> v_new_version_id;
|
||
UPDATE factory_prompt_version
|
||
SET status = 'PUBLISHED', published_by = 'system-upgrade', published_at = NOW()
|
||
WHERE prompt_version_id = v_new_version_id AND status = 'DRAFT';
|
||
UPDATE factory_prompt_template
|
||
SET current_version_id = v_new_version_id,
|
||
update_by = 'system-upgrade', update_time = NOW()
|
||
WHERE prompt_template_id = v_template_id;
|
||
END IF;
|
||
|
||
SELECT t.prompt_template_id, t.current_version_id, t.generate_type, t.prompt_code,
|
||
v.system_prompt, v.user_prompt_contract, v.user_prompt_template,
|
||
v.provider_code, v.model, v.pipeline_release_code
|
||
INTO v_template_id, v_current_version_id, v_generate_type, v_prompt_code,
|
||
v_system_prompt, v_user_prompt_contract, v_user_prompt_template,
|
||
v_provider_code, v_model, v_pipeline_release_code
|
||
FROM factory_prompt_template t
|
||
JOIN factory_prompt_version v ON v.prompt_version_id = t.current_version_id
|
||
WHERE t.prompt_code = 'front.flow_config'
|
||
FOR UPDATE;
|
||
|
||
SET v_rule = '12. 用户针对单条资源记录发起并创建主业务记录的动作（ownerTable 不等于 mainTable、无 from/to、create_record 到 mainTable）必须绑定同 ownerTable 的 detail 页面和 detail.primaryActions，例如预约、申请、借用、报名、下单。还书、取消、审核等已有业务记录上的状态流转动作仍可绑定业务记录列表的 list.rowActions。禁止把资源发起动作放在资源列表的 list.rowActions。';
|
||
IF LOCATE('禁止把资源发起动作放在资源列表的 list.rowActions', v_system_prompt) = 0 THEN
|
||
SET v_target_prompt = CONCAT(v_system_prompt, CHAR(10), v_rule);
|
||
|
||
SELECT COALESCE(MAX(version_no), 0) + 1
|
||
INTO v_version_no
|
||
FROM factory_prompt_version
|
||
WHERE prompt_template_id = v_template_id
|
||
FOR UPDATE;
|
||
SET v_version_label = CONCAT('V', v_version_no);
|
||
SET v_content_hash = SHA2(CONCAT(
|
||
'{"generateType":', JSON_QUOTE(v_generate_type),
|
||
',"promptCode":', JSON_QUOTE(v_prompt_code),
|
||
',"systemPrompt":', JSON_QUOTE(v_target_prompt),
|
||
',"userPromptContract":', JSON_QUOTE(v_user_prompt_contract),
|
||
',"userPromptTemplate":', JSON_QUOTE(v_user_prompt_template),
|
||
',"version":', JSON_QUOTE(v_version_label), '}'), 256);
|
||
|
||
INSERT INTO factory_prompt_version
|
||
(prompt_template_id, version_no, version_label, system_prompt,
|
||
user_prompt_contract, user_prompt_template, provider_code, model,
|
||
content_hash, status, parent_version_id, pipeline_release_code,
|
||
published_by, published_at, create_by, create_time, remark)
|
||
VALUES
|
||
(v_template_id, v_version_no, v_version_label, v_target_prompt,
|
||
v_user_prompt_contract, v_user_prompt_template, v_provider_code, v_model,
|
||
v_content_hash, 'DRAFT', v_current_version_id, v_pipeline_release_code,
|
||
'', NULL, 'system-upgrade', NOW(),
|
||
'Resource initiating actions bind to detail primary actions');
|
||
SET v_new_version_id = LAST_INSERT_ID();
|
||
|
||
UPDATE factory_prompt_version
|
||
SET status = 'RETIRED'
|
||
WHERE prompt_template_id = v_template_id
|
||
AND status = 'PUBLISHED'
|
||
AND prompt_version_id <> v_new_version_id;
|
||
UPDATE factory_prompt_version
|
||
SET status = 'PUBLISHED', published_by = 'system-upgrade', published_at = NOW()
|
||
WHERE prompt_version_id = v_new_version_id AND status = 'DRAFT';
|
||
UPDATE factory_prompt_template
|
||
SET current_version_id = v_new_version_id,
|
||
update_by = 'system-upgrade', update_time = NOW()
|
||
WHERE prompt_template_id = v_template_id;
|
||
END IF;
|
||
|
||
COMMIT;
|
||
END$$
|
||
|
||
CALL upgrade_resource_detail_binding_prompts_20260715()$$
|
||
DROP PROCEDURE upgrade_resource_detail_binding_prompts_20260715$$
|
||
|
||
DELIMITER ;
|