dev/常用SQL语句/权限相关/生成模块权限的语句.sql

/*为权限生成layercode*/
UPDATE auth_privilege SET privilege_layer_code=NULL WHERE application_id=2;

UPDATE auth_privilege SET privilege_layer_code=CONCAT('0,',privilege_id) WHERE application_id=2 AND privilege_key = "ITIM.PRIVS";

/* 反复执行，直到为0 */
UPDATE auth_privilege a SET privilege_layer_code=CONCAT((SELECT c.privilege_layer_code FROM (SELECT * FROM auth_privilege) c WHERE a.PRIVILEGE_parent_ID = c. PRIVILEGE_ID  ),',',privilege_id) WHERE application_id=2 AND privilege_layer_code IS NULL AND EXISTS(
SELECT * FROM (SELECT * FROM auth_privilege) b WHERE a.PRIVILEGE_parent_ID = b. PRIVILEGE_ID AND b.privilege_layer_code IS NOT NULL
);

/* 查看排好序的权限 */
SELECT * FROM auth_privilege WHERE application_id=2 ORDER BY privilege_layer_code;

/*         以下是迁移发版的时候使用的语句               */
/* 将父key更新到PRIVILEGE_LAYER_CODE_DESCRIPTION */
UPDATE auth_privilege a SET a.PRIVILEGE_LAYER_CODE_DESCRIPTION=(SELECT c.PRIVILEGE_KEY FROM (SELECT * FROM auth_privilege) c WHERE a.PRIVILEGE_parent_ID = c. PRIVILEGE_ID ) WHERE a.application_id=2;


/* 生成权限迁移的sql语句，需要借助 PRIVILEGE_LAYER_CODE_DESCRIPTION 的值*/

SELECT
CONCAT("INSERT INTO auth_privilege ( APPLICATION_ID, PRIVILEGE_KEY, PRIVILEGE_NAME, PRIVILEGE_TYPE_DICT, CREATE_USER_ID, CREATE_TIME, UPDATE_USER_ID, UPDATE_TIME,PRIVILEGE_DISPLAY_ORDER,PRIVILEGE_PARENT_ID,PRIVILEGE_ICON,PRIVILEGE_URL,PRIVILEGE_STATUS ) (SELECT "
,APPLICATION_ID,",'",PRIVILEGE_KEY,"','",PRIVILEGE_NAME,"','",PRIVILEGE_TYPE_DICT,"','1',now(),'1',now(),",PRIVILEGE_DISPLAY_ORDER,",(SELECT max(PRIVILEGE_ID) FROM auth_privilege WHERE PRIVILEGE_KEY='",PRIVILEGE_LAYER_CODE_DESCRIPTION,"'),'",PRIVILEGE_ICON,"','",PRIVILEGE_URL,"','",PRIVILEGE_STATUS,"' FROM DUAL);" ) AS psql
 FROM auth_privilege
WHERE application_id=2 AND privilege_key LIKE 'ITIM.PERSONNEL%'
ORDER BY privilege_layer_code;