83 lines
4.3 KiB
SQL
83 lines
4.3 KiB
SQL
-- 在权限库执行；先在同一会话显式设置以下两个变量，再执行本脚本：
|
|
-- SET @checkout_relation_tenant_id = 目标租户ID;
|
|
-- SET @checkout_relation_parent_id = 已存在的商户业务父菜单ID;
|
|
-- 不自动授予角色；执行后按需分配查看、新增、编辑、删除权限。
|
|
-- 沿用当前代码中菜单0、按钮1的持久化协议（历史表注释与实际代码不一致）。
|
|
|
|
DELIMITER $$
|
|
DROP PROCEDURE IF EXISTS `migrate_20261102_mch_checkout_relation_menu`$$
|
|
CREATE PROCEDURE `migrate_20261102_mch_checkout_relation_menu`()
|
|
BEGIN
|
|
DECLARE v_page_id BIGINT;
|
|
DECLARE v_count INT;
|
|
DECLARE EXIT HANDLER FOR SQLEXCEPTION
|
|
BEGIN
|
|
ROLLBACK;
|
|
RESIGNAL;
|
|
END;
|
|
|
|
IF @checkout_relation_tenant_id IS NULL OR @checkout_relation_tenant_id <= 0
|
|
OR @checkout_relation_parent_id IS NULL OR @checkout_relation_parent_id <= 0 THEN
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '请先显式设置目标租户和父菜单ID';
|
|
END IF;
|
|
|
|
START TRANSACTION;
|
|
-- 锁定同一父菜单，串行化重复或并发安装，不依赖sys_menu额外唯一索引。
|
|
SELECT menu_id INTO v_page_id FROM sys_menu
|
|
WHERE menu_id = @checkout_relation_parent_id AND tenant_id = @checkout_relation_tenant_id
|
|
AND del_flag = '0' AND menu_type = '0' FOR UPDATE;
|
|
IF v_page_id IS NULL THEN
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '父菜单不存在、租户不匹配或不是可用菜单';
|
|
END IF;
|
|
|
|
SELECT COUNT(*), MAX(menu_id) INTO v_count, v_page_id FROM sys_menu
|
|
WHERE tenant_id = @checkout_relation_tenant_id AND del_flag = '0'
|
|
AND path = '/mch/checkout/setting/index';
|
|
IF v_count > 1 THEN
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '已存在重复结账关系菜单，请先人工检查';
|
|
END IF;
|
|
IF v_count = 1 AND NOT EXISTS (
|
|
SELECT 1 FROM sys_menu WHERE menu_id = v_page_id AND parent_id = @checkout_relation_parent_id
|
|
AND menu_type = '0' AND (component IS NULL OR component = 'mch/checkout/setting/index')
|
|
) THEN
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '已存在菜单位置或组件不符，请先人工检查';
|
|
END IF;
|
|
|
|
IF v_count = 0 THEN
|
|
SET v_page_id = UUID_SHORT();
|
|
INSERT INTO sys_menu (menu_id, name, path, component, parent_id, icon, visible, sort_order,
|
|
keep_alive, menu_type, create_by, create_time, update_by, update_time, del_flag, tenant_id)
|
|
VALUES (v_page_id, '商户结账关系', '/mch/checkout/setting/index', 'mch/checkout/setting/index',
|
|
@checkout_relation_parent_id, 'ele-Connection', '1', 80, '1', '0',
|
|
'admin', NOW(), 'admin', NOW(), '0', @checkout_relation_tenant_id);
|
|
END IF;
|
|
|
|
IF EXISTS (
|
|
SELECT 1 FROM sys_menu WHERE tenant_id = @checkout_relation_tenant_id AND del_flag = '0'
|
|
AND permission IN ('wallet_mchCheckoutRelation_view', 'wallet_mchCheckoutRelation_add',
|
|
'wallet_mchCheckoutRelation_edit', 'wallet_mchCheckoutRelation_del')
|
|
AND (parent_id <> v_page_id OR menu_type <> '1')
|
|
) THEN
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '结账关系权限已被其他菜单占用，请先人工检查';
|
|
END IF;
|
|
|
|
INSERT INTO sys_menu (menu_id, name, permission, parent_id, visible, sort_order, keep_alive,
|
|
menu_type, create_by, create_time, update_by, update_time, del_flag, tenant_id)
|
|
SELECT UUID_SHORT(), p.name, p.permission, v_page_id, '1', p.sort_order, '0',
|
|
'1', 'admin', NOW(), 'admin', NOW(), '0', @checkout_relation_tenant_id
|
|
FROM (
|
|
SELECT '查看结账关系' name, 'wallet_mchCheckoutRelation_view' permission, 1 sort_order
|
|
UNION ALL SELECT '新增结账关系', 'wallet_mchCheckoutRelation_add', 2
|
|
UNION ALL SELECT '编辑结账关系', 'wallet_mchCheckoutRelation_edit', 3
|
|
UNION ALL SELECT '删除结账关系', 'wallet_mchCheckoutRelation_del', 4
|
|
) p
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM sys_menu m WHERE m.tenant_id = @checkout_relation_tenant_id
|
|
AND m.parent_id = v_page_id AND m.permission = p.permission AND m.del_flag = '0'
|
|
);
|
|
COMMIT;
|
|
END$$
|
|
CALL `migrate_20261102_mch_checkout_relation_menu`()$$
|
|
DROP PROCEDURE `migrate_20261102_mch_checkout_relation_menu`$$
|
|
DELIMITER ;
|