51工具盒子1 前言 {#1-%E5%89%8D%E8%A8%80}
DataEase 系统数据库 5.7 迁移至 8.0 出现报错：Illegal mix of collations 初步判断，数据库链接的编码和数据库 sever 的编码不同导致，可能是函数与视图所使用的的字符集与表的不一致导致的。
2 原因 {#2-%E5%8E%9F%E5%9B%A0}
问题原因是函数和视图编码使用 MySQL8 默认的 utf8mb4_0900_ai_ci。
查询函数的字符集，会发现 collation_connection 使用的是默认 utf8mb4_0900_ai_ci
mysql> SHOW CREATE FUNCTION CHECK_TREE_NO_MANAGE_PRIVILEGE\G
图中已经被修改成 utf8mb4_general_ci。
3 解决方法 {#3-%E8%A7%A3%E5%86%B3%E6%96%B9%E6%B3%95}
3.1 编辑 my.cnf {#3.1-%E7%BC%96%E8%BE%91-my.cnf}
vi /etc/my.cnf
#增加如下：
[mysqld]
character_set_server=utf8mb4
collation-server=utf8mb4_general_ci
[client]
default-character-set=utf8mb4
3.2 重启数据库 {#3.2-%E9%87%8D%E5%90%AF%E6%95%B0%E6%8D%AE%E5%BA%93}
3.3 查询数据字符集 {#3.3-%E6%9F%A5%E8%AF%A2%E6%95%B0%E6%8D%AE%E5%AD%97%E7%AC%A6%E9%9B%86}
# show VARIABLES LIKE '%collation%';
mysql> show VARIABLES LIKE '%collation%';
+-------------------------------+--------------------+
| Variable_name | Value |
+-------------------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
#对字符集进行设置
set collation_database=utf8mb4_general_ci;
set collation_connection=utf8mb4_general_ci;
set collation_server=utf8mb4_general_ci;
set default_collation_for_utf8mb4=utf8mb4_general_ci;
3.4 命令窗口不关，执行创建视图和函数的 SQL，对视图和函数进行重建 {#3.4-%E5%91%BD%E4%BB%A4%E7%AA%97%E5%8F%A3%E4%B8%8D%E5%85%B3%EF%BC%8C%E6%89%A7%E8%A1%8C%E5%88%9B%E5%BB%BA%E8%A7%86%E5%9B%BE%E5%92%8C%E5%87%BD%E6%95%B0%E7%9A%84-sql%EF%BC%8C%E5%AF%B9%E8%A7%86%E5%9B%BE%E5%92%8C%E5%87%BD%E6%95%B0%E8%BF%9B%E8%A1%8C%E9%87%8D%E5%BB%BA}
-- ----------------------------
-- View structure for v_auth_model
-- ----------------------------
DROP VIEW IF EXISTS v_auth_model;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW v_auth_model AS select sys_user.user_id AS id,sys_user.username AS name,sys_user.username AS label,'0' AS pid,'leaf' AS node_type,'user' AS model_type,'user' AS model_inner_type,'target' AS auth_type,sys_user.create_by AS create_by,0 AS level,0 AS mode,'0' AS data_source_id from sys_user where (sys_user.is_admin <> 1) union all select sys_role.role_id AS id,sys_role.name AS name,sys_role.name AS label,'0' AS pid,'leaf' AS node_type,'role' AS model_type,'role' AS model_inner_type,'target' AS auth_type,sys_role.create_by AS create_by,0 AS level,0 AS mode,'0' AS data_source_id from sys_role union all select sys_dept.dept_id AS id,sys_dept.name AS name,sys_dept.name AS lable,(cast(sys_dept.pid as char charset utf8mb4) collate utf8mb4_general_ci) AS pid,if((sys_dept.sub_count = 0),'leaf','spine') AS node_type,'dept' AS model_type,'dept' AS model_inner_type,'target' AS auth_type,sys_dept.create_by AS create_by,0 AS level,0 AS mode,'0' AS data_source_id from sys_dept union all select datasource.id AS id,datasource.name AS NAME,datasource.name AS label,'0' AS pid,'leaf' AS node_type,'link' AS model_type,datasource.type AS model_inner_type,'source' AS auth_type,datasource.create_by AS create_by,0 AS level,0 AS mode,'0' AS data_source_id from datasource union all select dataset_group.id AS id,dataset_group.name AS NAME,dataset_group.name AS lable,if((dataset_group.pid is null),'0',dataset_group.pid) AS pid,'spine' AS node_type,'dataset' AS model_type,dataset_group.type AS model_inner_type,'source' AS auth_type,dataset_group.create_by AS create_by,dataset_group.level AS level,0 AS mode,'0' AS data_source_id from dataset_group union all select dataset_table.id AS id,dataset_table.name AS NAME,dataset_table.name AS lable,dataset_table.scene_id AS pid,'leaf' AS node_type,'dataset' AS model_type,dataset_table.type AS model_inner_type,'source' AS auth_type,dataset_table.create_by AS create_by,0 AS level,dataset_table.mode AS mode,dataset_table.data_source_id AS data_source_id from dataset_table union all select panel_group.id AS id,panel_group.name AS NAME,panel_group.name AS label,(case panel_group.id when 'panel_list' then '0' when 'default_panel' then '0' else panel_group.pid end) AS pid,if((panel_group.node_type = 'folder'),'spine','leaf') AS node_type,'panel' AS model_type,panel_group.panel_type AS model_inner_type,'source' AS auth_type,panel_group.create_by AS create_by,0 AS level,0 AS mode,'0' AS data_source_id from panel_group union all select sys_menu.menu_id AS menu_id,sys_menu.title AS name,sys_menu.title AS label,sys_menu.pid AS pid,if((sys_menu.sub_count > 0),'spine','leaf') AS node_type,'menu' AS model_type,(case sys_menu.type when 0 then 'folder' when 1 then 'menu' when 2 then 'button' end) AS model_inner_type,'source' AS auth_type,sys_menu.create_by AS create_by,0 AS level,0 AS mode,'0' AS data_source_id from sys_menu where ((sys_menu.i_frame <> 1) or (sys_menu.i_frame is null)) union all select plugin_sys_menu.menu_id AS menu_id,plugin_sys_menu.title AS name,plugin_sys_menu.title AS label,plugin_sys_menu.pid AS pid,if((plugin_sys_menu.sub_count > 0),'spine','leaf') AS node_type,'menu' AS model_type,(case plugin_sys_menu.type when 0 then 'folder' when 1 then 'menu' when 2 then 'button' end) AS model_inner_type,'source' AS auth_type,plugin_sys_menu.create_by AS create_by,0 AS level,0 AS mode,'0' AS data_source_id from plugin_sys_menu where ((plugin_sys_menu.i_frame <> 1) or (plugin_sys_menu.i_frame is null));
-- View structure for v_auth_privilege
DROP VIEW IF EXISTS v_auth_privilege;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW v_auth_privilege AS select sys_auth.auth_source AS auth_source,sys_auth.auth_source_type AS auth_source_type,group_concat(sys_auth_detail.privilege_extend separator ',') AS privileges from (sys_auth left join sys_auth_detail on((sys_auth.id = sys_auth_detail.auth_id))) where ((sys_auth_detail.privilege_value = 1) and (((sys_auth.auth_target_type = 'dept') and (sys_auth.auth_target = (select sys_user.dept_id from sys_user where (sys_user.user_id = '4')))) or ((sys_auth.auth_target_type = 'user') and (sys_auth.auth_target = '4')) or ((sys_auth.auth_target_type = 'role') and (sys_auth.auth_target = (select sys_users_roles.role_id from sys_users_roles where (sys_users_roles.user_id = '4')))))) group by sys_auth.auth_source,sys_auth.auth_source_type;
-- View structure for v_history_chart_view
DROP VIEW IF EXISTS v_history_chart_view;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW v_history_chart_view AS select chart_group.id AS id,chart_group.id AS inner_id,chart_group.name AS NAME,chart_group.name AS label,chart_group.pid AS pid,chart_group.type AS model_inner_type,'spine' AS node_type,'view' AS model_type,1 AS mode from chart_group union all select distinct chart_view.id AS id,chart_view.id AS inner_id,chart_view.name AS NAME,chart_view.name AS label,chart_view.scene_id AS pid,chart_view.type AS model_inner_type,'leaf' AS node_type,'view' AS model_type,1 AS mode from chart_view where (chart_view.chart_type = 'public');
-- Function structure for CHECK_TREE_NO_MANAGE_PRIVILEGE
DROP FUNCTION IF EXISTS CHECK_TREE_NO_MANAGE_PRIVILEGE;
delimiter ;;
CREATE FUNCTION CHECK_TREE_NO_MANAGE_PRIVILEGE(userId varchar(255),modelType varchar(255),nodeId varchar(255))
RETURNS int
READS SQL DATA
BEGIN
DECLARE privilegeType INTEGER;
DECLARE allTreeIds longtext;
DECLARE allPrivilegeTreeIds longtext;
DECLARE result INTEGER;
select privilege_type into privilegeType from sys_auth_detail where auth_id =modelType and privilege_extend ='manage';
select GET_V_AUTH_MODEL_WITH_CHILDREN( nodeId ,modelType) into allTreeIds;
select GET_V_AUTH_MODEL_WITH_PRIVILEGE(userId,modelType,privilegeType) into allPrivilegeTreeIds;
select count(id) into result from v_auth_model where v_auth_model.model_type=modelType and FIND_IN_SET(v_auth_model.id,allTreeIds) and (!FIND_IN_SET(v_auth_model.id,allPrivilegeTreeIds) or ISNULL(allPrivilegeTreeIds));
RETURN result;
END
;;
delimiter ;
-- Function structure for copy_auth
DROP FUNCTION IF EXISTS copy_auth;
delimiter ;;
CREATE FUNCTION copy_auth(authSource varchar(255),authSourceType varchar(255),authUser varchar(255))
RETURNS varchar(255) CHARSET utf8mb4
READS SQL DATA
BEGIN
DECLARE authId varchar(255);
DECLARE userId varchar(255);
DECLARE copyId varchar(255);
DECLARE selectPid varchar(255);
select uuid() into authId;
select uuid() into copyId;
select max(sys_user.user_id) into userId from sys_user where username= authUser;
SELECT pid into selectPid FROM v_auth_model WHERE id = authSource AND model_type = authSourceType;
delete from sys_auth_detail where auth_id in (
select id from sys_auth where sys_auth.auth_source=authSource and sys_auth.auth_source_type=authSourceType
);
delete from sys_auth where sys_auth.auth_source=authSource and sys_auth.auth_source_type=authSourceType;
INSERT INTO sys_auth (
id,
auth_source,
auth_source_type,
auth_target,
auth_target_type,
auth_time,
auth_user
)
VALUES
(
authId,
authSource,
authSourceType,
userId,
'user',
unix_timestamp(
now())* 1000,'auto');
INSERT INTO sys_auth_detail (
id,
auth_id,
privilege_name,
privilege_type,
privilege_value,
privilege_extend,
remark,
create_user,
create_time
) SELECT
uuid() AS id,
authId AS auth_id,
sys_auth_detail.privilege_name,
sys_auth_detail.privilege_type,
1,
sys_auth_detail.privilege_extend,
sys_auth_detail.remark,
'auto' AS create_user,
unix_timestamp(now())* 1000 AS create_time
FROM
sys_auth_detail where auth_id =authSourceType;
/继承第一父级权限/
insert into sys_auth(
id,
auth_source,
auth_source_type,
auth_target,
auth_target_type,
auth_time,
auth_user,
copy_from,
copy_id
)
SELECT
uuid() as id,
authSource as auth_source,
authSourceType as auth_source_type,
auth_target,
auth_target_type,
NOW()* 1000 as auth_time,
'auto' as auth_user,
id as copy_from,
copyId as copy_id
FROM
sys_auth
WHERE
auth_source =selectPid
AND auth_source_type = authSourceType
and concat(auth_target,'-',auth_target_type) !=CONCAT(userId,'-','user');
INSERT INTO sys_auth_detail (
id,
auth_id,
privilege_name,
privilege_type,
privilege_value,
privilege_extend,
remark,
create_user,
create_time,
copy_from,
copy_id
) SELECT
uuid() AS id,
sa_copy.t_id AS auth_id,
sys_auth_detail.privilege_name,
sys_auth_detail.privilege_type,
sys_auth_detail.privilege_value,
sys_auth_detail.privilege_extend,
sys_auth_detail.remark,
'auto' AS create_user,
unix_timestamp(
now())* 1000 AS create_time,
id AS copy_from,
copyId AS copy_id
FROM
sys_auth_detail
INNER JOIN (
SELECT
id AS t_id,
copy_from AS s_id
FROM
sys_auth
WHERE
copy_id = copyId
) sa_copy ON sys_auth_detail.auth_id = sa_copy.s_id;
RETURN 'success';
END
;;
delimiter ;
-- Function structure for delete_auth_source
DROP FUNCTION IF EXISTS delete_auth_source;
delimiter ;;
CREATE FUNCTION delete_auth_source(authSource varchar(255),authSourceType varchar(255))
RETURNS varchar(255) CHARSET utf8mb4
READS SQL DATA
BEGIN
delete from sys_auth_detail where auth_id in (
select id from sys_auth where sys_auth.auth_source=authSource and sys_auth.auth_source_type=authSourceType
);
delete from sys_auth where sys_auth.auth_source=authSource and sys_auth.auth_source_type=authSourceType;
RETURN 'success';
END
;;
delimiter ;
-- Function structure for delete_auth_target
DROP FUNCTION IF EXISTS delete_auth_target;
delimiter ;;
CREATE FUNCTION delete_auth_target(authTarget varchar(255),authTargetType varchar(255))
RETURNS varchar(255) CHARSET utf8mb4
READS SQL DATA
BEGIN
delete from sys_auth_detail where auth_id in (
select id from sys_auth where sys_auth.auth_target=authTarget and sys_auth.auth_target_type=authTargetType
);
delete from sys_auth where sys_auth.auth_target=authTarget and sys_auth.auth_target_type=authTargetType;
RETURN 'sucess';
END
;;
delimiter ;
-- Function structure for get_auths
DROP FUNCTION IF EXISTS get_auths;
delimiter ;;
CREATE FUNCTION get_auths(authSource varchar(255),modelType varchar(255),userId varchar(255))
RETURNS longtext CHARSET utf8mb4
READS SQL DATA
BEGIN
DECLARE oTemp longtext;
SELECT
group_concat( DISTINCT sys_auth_detail.privilege_extend) into oTemp
FROM
(
sys_auth
LEFT JOIN sys_auth_detail ON ((
sys_auth.id = sys_auth_detail.auth_id
)))
where sys_auth_detail.privilege_value =1
and sys_auth.auth_source=authSource
AND (
(
sys_auth.auth_target_type = 'dept'
AND sys_auth.auth_target in ( SELECT dept_id FROM sys_user WHERE user_id = userId )
)
OR (
sys_auth.auth_target_type = 'user'
AND sys_auth.auth_target = userId
)
OR (
sys_auth.auth_target_type = 'role'
AND sys_auth.auth_target in ( SELECT role_id FROM sys_users_roles WHERE user_id = userId )
)
)
GROUP BY
sys_auth.auth_source,
sys_auth.auth_source_type;
RETURN oTemp;
END
;;
delimiter ;
-- Function structure for get_auth_children_count
DROP FUNCTION IF EXISTS get_auth_children_count;
delimiter ;;
CREATE FUNCTION get_auth_children_count(pidInfo varchar(255),modelType varchar(255),userName varchar(255))
RETURNS varchar(255) CHARSET utf8mb4
READS SQL DATA
BEGIN
DECLARE childrenCount INTEGER;
select count(1)-1 into childrenCount from v_auth_model where FIND_IN_SET(
v_auth_model.id,
GET_V_AUTH_MODEL_WITH_CHILDREN ( pidInfo, modelType ))
AND create_by = userName
AND v_auth_model.node_type = 'leaf';
RETURN childrenCount;
END
;;
delimiter ;
-- Function structure for GET_CHART_GROUP_WITH_CHILDREN
DROP FUNCTION IF EXISTS GET_CHART_GROUP_WITH_CHILDREN;
delimiter ;;
CREATE FUNCTION GET_CHART_GROUP_WITH_CHILDREN(parentId varchar(8000))
RETURNS longtext CHARSET utf8mb3
READS SQL DATA
BEGIN
DECLARE oTemp LONGTEXT;
DECLARE oTempChild LONGTEXT;
SET oTemp = '';
SET oTempChild = CAST(parentId AS CHAR);
WHILE oTempChild IS NOT NULL
DO
SET oTemp = CONCAT(oTemp,',',oTempChild);
SELECT GROUP_CONCAT(id) INTO oTempChild FROM v_history_chart_view WHERE FIND_IN_SET(pid,oTempChild) > 0;
END WHILE;
RETURN oTemp;
END
;;
delimiter ;
-- Function structure for GET_CHART_VIEW_COPY_NAME
DROP FUNCTION IF EXISTS GET_CHART_VIEW_COPY_NAME;
delimiter ;;
CREATE FUNCTION GET_CHART_VIEW_COPY_NAME(chartId varchar(255),pid varchar(255))
RETURNS varchar(255) CHARSET utf8mb4
READS SQL DATA
BEGIN
DECLARE chartName varchar(255);
DECLARE regexpInfo varchar(255);
DECLARE chartNameCount INTEGER;
select name into chartName from chart_view where id =chartId;
/**
因为名称存在（）等特殊字符，所以不能直接用REGEXP进行查找，qrtz_locks
1.用like 'chartName%' 过滤可能的数据项
2.REPLACE(name,chartName,'') REGEXP '-copy\(([0-9])+\)$' 过滤去掉chartName后的字符以 -copy(/d) 结尾的数据
3.(LENGTH(REPLACE(name,chartName,''))-LENGTH(replace(REPLACE(name,chartName,''),'-',''))=1) 确定只出现一次 '-' 防止多次copy
**/
select (count(1)+1) into chartNameCount from chart_view
where (LENGTH(REPLACE(name,chartName,''))-LENGTH(replace(REPLACE(name,chartName,''),'-',''))=1)
and REPLACE(name,chartName,'') REGEXP '-copy\(([0-9])+\)$' and name like CONCAT(chartName,'%') and chart_view.scene_id=pid ;
RETURN concat(chartName,'-copy(',chartNameCount,')');
END
;;
delimiter ;
-- Function structure for get_grant_auths
DROP FUNCTION IF EXISTS get_grant_auths;
delimiter ;;
CREATE FUNCTION get_grant_auths(modelType VARCHAR ( 255 ),
userId VARCHAR ( 255 ))
RETURNS longtext CHARSET utf8mb4
READS SQL DATA
BEGIN
DECLARE
oTemp LONGTEXT;
SELECT
GROUP_CONCAT( DISTINCT v_auth_model.id ) into oTemp
FROM
v_auth_model
LEFT JOIN sys_auth ON v_auth_model.id = sys_auth.auth_source
AND v_auth_model.model_type = sys_auth.auth_source_type
LEFT JOIN sys_auth_detail ON sys_auth.id = sys_auth_detail.auth_id
WHERE
privilege_type = 15
AND privilege_value = 1
AND v_auth_model.model_type = modelType
AND (
(
sys_auth.auth_target_type = 'dept'
AND sys_auth.auth_target IN ( SELECT dept_id FROM sys_user WHERE user_id = userId )
)
OR (
sys_auth.auth_target_type = 'user'
AND sys_auth.auth_target = userId
)
OR (
sys_auth.auth_target_type = 'role'
AND sys_auth.auth_target IN ( SELECT role_id FROM sys_users_roles WHERE user_id = userId )
)
);
RETURN oTemp;
END
;;
delimiter ;
-- Function structure for GET_PANEL_GROUP_WITH_CHILDREN
DROP FUNCTION IF EXISTS GET_PANEL_GROUP_WITH_CHILDREN;
delimiter ;;
CREATE FUNCTION GET_PANEL_GROUP_WITH_CHILDREN(parentId varchar(8000))
RETURNS varchar(8000) CHARSET utf8mb4
READS SQL DATA
BEGIN
DECLARE oTemp VARCHAR(8000);
DECLARE oTempChild VARCHAR(8000);
SET oTemp = '';
SET oTempChild = CAST(parentId AS CHAR CHARACTER set utf8mb4) COLLATE utf8mb4_general_ci;
WHILE oTempChild IS NOT NULL
DO
SET oTemp = CONCAT(oTemp,',',oTempChild);
SELECT GROUP_CONCAT(id) INTO oTempChild FROM panel_group WHERE FIND_IN_SET(pid,oTempChild) > 0;
END WHILE;
RETURN oTemp;
END
;;
delimiter ;
-- Function structure for GET_PANEL_TEMPLATE_WITH_CHILDREN
DROP FUNCTION IF EXISTS GET_PANEL_TEMPLATE_WITH_CHILDREN;
delimiter ;;
CREATE FUNCTION GET_PANEL_TEMPLATE_WITH_CHILDREN(parentId varchar(8000))
RETURNS varchar(8000) CHARSET utf8mb4
READS SQL DATA
BEGIN
DECLARE oTemp VARCHAR(8000);
DECLARE oTempChild VARCHAR(8000);
SET oTemp = '';
SET oTempChild = CAST(parentId AS CHAR CHARACTER set utf8mb4) COLLATE utf8mb4_general_ci;
WHILE oTempChild IS NOT NULL
DO
SET oTemp = CONCAT(oTemp,',',oTempChild);
SELECT GROUP_CONCAT(id) INTO oTempChild FROM panel_template WHERE FIND_IN_SET(pid,oTempChild) > 0;
END WHILE;
RETURN oTemp;
END
;;
delimiter ;
-- Function structure for GET_PANEL_WITH_PRIVILEGE_AND_MOBILE
DROP FUNCTION IF EXISTS GET_PANEL_WITH_PRIVILEGE_AND_MOBILE;
delimiter ;;
CREATE FUNCTION GET_PANEL_WITH_PRIVILEGE_AND_MOBILE(userId longtext,modelType varchar(255),privilegeType varchar(255))
RETURNS longtext CHARSET utf8mb3
READS SQL DATA
BEGIN
DECLARE oTempLeafIds longtext;
select GROUP_CONCAT(auth_source) into oTempLeafIds from (
SELECT
sys_auth.auth_source_type,
sys_auth.auth_source
FROM
sys_auth
LEFT JOIN sys_auth_detail ON sys_auth.id = sys_auth_detail.auth_id
WHERE
sys_auth_detail.privilege_type = privilegeType
and sys_auth.auth_source_type = modelType
AND (
(
sys_auth.auth_target_type = 'dept'
AND sys_auth.auth_target in ( SELECT dept_id FROM sys_user WHERE user_id = userId )
)
OR (
sys_auth.auth_target_type = 'user'
AND sys_auth.auth_target = userId
)
OR (
sys_auth.auth_target_type = 'role'
AND sys_auth.auth_target in ( SELECT role_id FROM sys_users_roles WHERE user_id = userId )
)
OR (1 = ( SELECT is_admin FROM sys_user WHERE user_id = userId ))
)
and sys_auth.auth_source in (select id from panel_group where mobile_layout='1')
GROUP BY
sys_auth.auth_source_type,
sys_auth.auth_source
having (sum( sys_auth_detail.privilege_value )> 0 or 1 = ( SELECT is_admin FROM sys_user WHERE user_id = userId ))) temp;
RETURN oTempLeafIds;
END
;;
delimiter ;
-- Function structure for GET_V_AUTH_MODEL_ID_P_USE
DROP FUNCTION IF EXISTS GET_V_AUTH_MODEL_ID_P_USE;
delimiter ;;
CREATE FUNCTION GET_V_AUTH_MODEL_ID_P_USE(userId longtext,modelType varchar(255))
RETURNS longtext CHARSET utf8mb4
READS SQL DATA
BEGIN
DECLARE oTempLeafIds longtext;
DECLARE oTempAllIds longtext;
select GET_V_AUTH_MODEL_WITH_PRIVILEGE(userId,modelType,1) into oTempLeafIds;
select GROUP_CONCAT(id) into oTempAllIds from (select GET_V_AUTH_MODEL_WITH_PARENT ( oTempLeafIds ,modelType) cids) t, v_auth_model where v_auth_model.model_type=modelType and FIND_IN_SET(v_auth_model.id,cids) order by id asc;
RETURN oTempAllIds;
END
;;
delimiter ;
-- Function structure for GET_V_AUTH_MODEL_ID_P_USE_MOBILE
DROP FUNCTION IF EXISTS GET_V_AUTH_MODEL_ID_P_USE_MOBILE;
delimiter ;;
CREATE FUNCTION GET_V_AUTH_MODEL_ID_P_USE_MOBILE(userId longtext,modelType varchar(255))
RETURNS longtext CHARSET utf8mb3
READS SQL DATA
BEGIN
DECLARE oTempLeafIds longtext;
DECLARE oTempAllIds longtext;
select GET_PANEL_WITH_PRIVILEGE_AND_MOBILE(userId,modelType,1) into oTempLeafIds;
select GROUP_CONCAT(id) into oTempAllIds from (select GET_V_AUTH_MODEL_WITH_PARENT ( oTempLeafIds ,modelType) cids) t, v_auth_model where v_auth_model.model_type=modelType and FIND_IN_SET(v_auth_model.id,cids) order by id asc;
RETURN oTempAllIds;
END
;;
delimiter ;
-- Function structure for GET_V_AUTH_MODEL_WITH_CHILDREN
DROP FUNCTION IF EXISTS GET_V_AUTH_MODEL_WITH_CHILDREN;
delimiter ;;
CREATE FUNCTION GET_V_AUTH_MODEL_WITH_CHILDREN(parentId longtext,modelType varchar(255))
RETURNS longtext CHARSET utf8mb4
READS SQL DATA
BEGIN
DECLARE oTemp longtext;
DECLARE oTempChild longtext;
SET oTemp = '';
SET oTempChild = CAST(parentId AS CHAR CHARACTER set utf8mb4) COLLATE utf8mb4_general_ci;
WHILE oTempChild IS NOT NULL
DO
SET oTemp = CONCAT(oTemp,',',oTempChild);
SELECT GROUP_CONCAT(id) INTO oTempChild FROM V_AUTH_MODEL WHERE FIND_IN_SET(pid,oTempChild) > 0 and V_AUTH_MODEL.model_type=modelType order by id asc;
END WHILE;
RETURN oTemp;
END
;;
delimiter ;
-- Function structure for GET_V_AUTH_MODEL_WITH_PARENT
DROP FUNCTION IF EXISTS GET_V_AUTH_MODEL_WITH_PARENT;
delimiter ;;
CREATE FUNCTION GET_V_AUTH_MODEL_WITH_PARENT(childrenId longtext,modelType varchar(255))
RETURNS longtext CHARSET utf8mb4
READS SQL DATA
BEGIN
DECLARE oTemp longtext;
DECLARE oTempParent longtext;
SET oTemp = '';
SET oTempParent = CAST(childrenId AS CHAR CHARACTER set utf8mb4) COLLATE utf8mb4_general_ci;
WHILE oTempParent IS NOT NULL
DO
SET oTemp = CONCAT(oTemp,',',oTempParent);
SELECT GROUP_CONCAT(distinct pid) INTO oTempParent FROM V_AUTH_MODEL WHERE FIND_IN_SET(id,oTempParent) > 0 and V_AUTH_MODEL.model_type=modelType order by pid asc;
END WHILE;
RETURN oTemp;
END
;;
delimiter ;
-- Function structure for GET_V_AUTH_MODEL_WITH_PRIVILEGE
DROP FUNCTION IF EXISTS GET_V_AUTH_MODEL_WITH_PRIVILEGE;
delimiter ;;
CREATE FUNCTION GET_V_AUTH_MODEL_WITH_PRIVILEGE(userId longtext,modelType varchar(255),privilegeType varchar(255))
RETURNS longtext CHARSET utf8mb4
READS SQL DATA
BEGIN
DECLARE oTempLeafIds longtext;
select GROUP_CONCAT(auth_source) into oTempLeafIds from (
SELECT
sys_auth.auth_source_type,
sys_auth.auth_source
FROM
sys_auth
LEFT JOIN sys_auth_detail ON sys_auth.id = sys_auth_detail.auth_id
WHERE
sys_auth_detail.privilege_type = privilegeType
and sys_auth.auth_source_type = modelType
AND (
(
sys_auth.auth_target_type = 'dept'
AND sys_auth.auth_target in ( SELECT dept_id FROM sys_user WHERE user_id = userId )
)
OR (
sys_auth.auth_target_type = 'user'
AND sys_auth.auth_target = userId
)
OR (
sys_auth.auth_target_type = 'role'
AND sys_auth.auth_target in ( SELECT role_id FROM sys_users_roles WHERE user_id = userId )
)
OR (1 = ( SELECT is_admin FROM sys_user WHERE user_id = userId ))
)
GROUP BY
sys_auth.auth_source_type,
sys_auth.auth_source
having (sum( sys_auth_detail.privilege_value )> 0 or 1 = ( SELECT is_admin FROM sys_user WHERE user_id = userId ))) temp;
RETURN oTempLeafIds;
END
;;
delimiter ;
-- Triggers structure for table chart_group
DROP TRIGGER IF EXISTS new_auth_chart_group;
delimiter ;;
CREATE TRIGGER new_auth_chart_group AFTER INSERT ON chart_group FOR EACH ROW select copy_auth(NEW.id,'chart',NEW.create_by) into @ee
;;
delimiter ;
-- Triggers structure for table chart_group
DROP TRIGGER IF EXISTS delete_auth_chart_group;
delimiter ;;
CREATE TRIGGER delete_auth_chart_group AFTER DELETE ON chart_group FOR EACH ROW select delete_auth_source(OLD.id,'chart') into @ee
;;
delimiter ;
-- Triggers structure for table dataset_group
DROP TRIGGER IF EXISTS delete_auth_dataset_group;
delimiter ;;
CREATE TRIGGER delete_auth_dataset_group AFTER DELETE ON dataset_group FOR EACH ROW select delete_auth_source(OLD.id,'dataset') into @ee
;;
delimiter ;
-- Triggers structure for table dataset_table
DROP TRIGGER IF EXISTS delete_auth_dataset_table;
delimiter ;;
CREATE TRIGGER delete_auth_dataset_table AFTER DELETE ON dataset_table FOR EACH ROW select delete_auth_source(OLD.id,'dataset') into @ee
;;
delimiter ;
-- Triggers structure for table datasource
DROP TRIGGER IF EXISTS delete_auth_link;
delimiter ;;
CREATE TRIGGER delete_auth_link AFTER DELETE ON datasource FOR EACH ROW select delete_auth_source(OLD.id,'link') into @ee
;;
delimiter ;
-- Triggers structure for table panel_group
DROP TRIGGER IF EXISTS delete_auth_panel;
delimiter ;;
CREATE TRIGGER delete_auth_panel AFTER DELETE ON panel_group FOR EACH ROW select delete_auth_source(OLD.id,'panel') into @ee
;;
delimiter ;
-- Triggers structure for table sys_dept
DROP TRIGGER IF EXISTS delete_auth_dept_target;
delimiter ;;
CREATE TRIGGER delete_auth_dept_target AFTER DELETE ON sys_dept FOR EACH ROW select delete_auth_target(OLD.dept_id,'dept') into @ee
;;
delimiter ;
-- Triggers structure for table sys_role
DROP TRIGGER IF EXISTS delete_auth_role_target;
delimiter ;;
CREATE TRIGGER delete_auth_role_target AFTER DELETE ON sys_role FOR EACH ROW select delete_auth_target(OLD.role_id,'role') into @ee
;;
delimiter ;
-- Triggers structure for table sys_user
DROP TRIGGER IF EXISTS delete_auth_user_target;
delimiter ;;
CREATE TRIGGER delete_auth_user_target AFTER DELETE ON sys_user FOR EACH ROW select delete_auth_target(OLD.user_id,'user') into @ee
;;
delimiter ;
SET FOREIGN_KEY_CHECKS = 1;
