65 lines
3.5 KiB
SQL
65 lines
3.5 KiB
SQL
-- 管理员屏蔽私聊消息权限，MySQL 8；交付后在后台业务库手动执行。
|
|
-- 不修改消息数据。给现有聊天记录/聊天列表页面的角色补充同一操作权限。
|
|
-- SQL 不会触发服务端角色权限缓存失效；执行后请在角色管理中重新保存受影响角色的菜单权限，
|
|
-- 再重新登录刷新前端权限。应用发布需同时更新 API 与 IM，uni-app 无需更新。
|
|
-- 复用现有权限按钮，不固定菜单/角色/租户 ID，不删除已有菜单或授权。
|
|
-- 此脚本含存储过程 DDL，请单独执行，不要包在其它迁移事务中。
|
|
DELIMITER $$
|
|
DROP PROCEDURE IF EXISTS migrate_chat_message_moderation_permission_20261103$$
|
|
CREATE PROCEDURE migrate_chat_message_moderation_permission_20261103()
|
|
BEGIN
|
|
DECLARE source_id BIGINT;
|
|
DECLARE target_id BIGINT;
|
|
DECLARE migration_lock INT DEFAULT 0;
|
|
DECLARE EXIT HANDLER FOR SQLEXCEPTION
|
|
BEGIN
|
|
ROLLBACK;
|
|
IF migration_lock = 1 THEN
|
|
DO RELEASE_LOCK('chat_message_moderation_permission_20261103');
|
|
END IF;
|
|
RESIGNAL;
|
|
END;
|
|
SELECT GET_LOCK('chat_message_moderation_permission_20261103', 10) INTO migration_lock;
|
|
IF COALESCE(migration_lock, 0) <> 1 THEN
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot acquire chat moderation permission migration lock';
|
|
END IF;
|
|
START TRANSACTION;
|
|
SELECT id INTO source_id FROM system_menu
|
|
WHERE deleted = b'0' AND type = 2
|
|
AND TRIM(LEADING '/' FROM component) IN
|
|
('chat/private-msg/index', 'chat/private-msg/index.vue', 'chat/list/index', 'chat/list/index.vue')
|
|
ORDER BY CASE WHEN TRIM(LEADING '/' FROM component) LIKE 'chat/private-msg/%' THEN 0 ELSE 1 END, id
|
|
LIMIT 1;
|
|
IF source_id IS NULL THEN
|
|
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No active private-message or chat-list menu found';
|
|
END IF;
|
|
|
|
SELECT MIN(id) INTO target_id FROM system_menu
|
|
WHERE deleted = b'0' AND type = 3 AND permission = 'chat:private-msg:moderate';
|
|
IF target_id IS NULL THEN
|
|
INSERT INTO system_menu
|
|
(name, permission, type, sort, parent_id, path, icon, component, component_name,
|
|
status, visible, keep_alive, always_show, creator, updater, deleted)
|
|
VALUES ('屏蔽私聊消息', 'chat:private-msg:moderate', 3, 10, source_id, '', '#', NULL, NULL,
|
|
0, b'1', b'1', b'1', 'migration', 'migration', b'0');
|
|
SET target_id = LAST_INSERT_ID();
|
|
END IF;
|
|
|
|
INSERT INTO system_role_menu (role_id, menu_id, creator, updater, deleted, tenant_id)
|
|
SELECT DISTINCT source.role_id, target_id, 'migration', 'migration', b'0', source.tenant_id
|
|
FROM system_role_menu source
|
|
JOIN system_menu page ON page.id = source.menu_id AND page.deleted = b'0' AND page.type = 2
|
|
WHERE source.deleted = b'0'
|
|
AND TRIM(LEADING '/' FROM page.component) IN
|
|
('chat/private-msg/index', 'chat/private-msg/index.vue', 'chat/list/index', 'chat/list/index.vue')
|
|
AND NOT EXISTS (SELECT 1 FROM system_role_menu target
|
|
WHERE target.role_id = source.role_id AND target.tenant_id = source.tenant_id
|
|
AND target.menu_id = target_id AND target.deleted = b'0');
|
|
COMMIT;
|
|
DO RELEASE_LOCK('chat_message_moderation_permission_20261103');
|
|
SELECT target_id AS permission_menu_id, 'chat:private-msg:moderate' AS permission;
|
|
END$$
|
|
CALL migrate_chat_message_moderation_permission_20261103()$$
|
|
DROP PROCEDURE migrate_chat_message_moderation_permission_20261103$$
|
|
DELIMITER ;
|