通向架构师的道路(第六天)之漫谈基于数据库的权限系统的设计.docx
通向架构师的道路（第六天）之漫谈基于数据库的权限系统的设计一、权限系统二、先来看客户的一个需求2.1 2.2 三、基于数据库的系统权限表设计3.1 ER(Entity Relationship)3.2 当然，还有更好的方法如：SELECT *FROM T_SYS_MENUSTARTWITH MENU_PID=0CONNECTBYPRIOR MENU_ID=MENU_PIDorderby MENU_IDSELECT *FROM T_SYS_MENUSTARTWITH MENU_ID='105'CONNECTBYPRIOR MENU_ID=MENU_PIDorderby MENU_IDselectdistinct m.menu_id,m.menu_descr,m.menu_url,m.menu_pid,p.privilege_id,p.privilege_type fromt_menu_privilege mp,t_sys_menu m,t_privilege p,t_user_role rwheremp.privilege_id=p.privilege_idand mp.role_id=r.role_idand mp.menu_id=m.menu_idand r.role_id='user'orderby m.menu_idselectdistinct m.menu_id,m.menu_descr,m.menu_url,m.menu_pid,p.privilege_id,p.privilege_type fromt_menu_privilege mp,t_sys_menu m,t_privilege p,t_user_role rwheremp.privilege_id=p.privilege_idand mp.role_id=r.role_idand mp.menu_id=m.menu_idand r.user_id='Danzel'orderby m.menu_id;3.3 selectdistinct m.menu_id, m.menu_descr, m.menu_url, m.menu_pidfromt_menu_privilege mp,t_sys_menu m,t_privilege p,t_user_role rwheremp.privilege_id=p.privilege_idand mp.role_id=r.role_idand mp.menu_id=m.menu_idand r.user_id='Danzel'STARTWITH MENU_PID='0'CONNECTBYPRIOR M.MENU_ID=M.MENU_PIDorderby M.MENU_IDselectdistinct m.menu_id,m.menu_descr,m.menu_url,m.menu_pid,p.privilege_id,p.privilege_type fromt_menu_privilege mp,t_sys_menu m,t_privilege p,t_user_role rwheremp.privilege_id=p.privilege_idand mp.role_id=r.role_idand mp.menu_id=m.menu_idand r.user_id='Danzel'orderby m.menu_id;一旦界面上该角色对某个系统菜单没有了查看权限后，它对这个菜单的其它权限也必须从T_MENU_PRIVILEGE这个表中删除。四、改进T_SYSTEM_MENU4.1 SELECT COUNT(parent.menu_id)-1) menuLevel, FROM t_sys_menu parentWHERE BETWEEN parent.lftAND parent.rgt AND node.menu_descr!='菜单'GROUPBY node.menu_id,node.menu_descr,node.lft,node.rgt,node.menu_url,node.menu_pidORDERBY node.lft4.2 SELECT lftFROM t_sys_menuwhere menu_id='101'UPDATE t_sys_menuSET rgt = rgt +2WHERE rgt >2;UPDATE t_sys_menuSET lft = lft +2WHERE lft >2;INSERTINTO t_sys_menu(menu_id, menu_descr, menu_url, lft, rgt)VALUES('113','周报','周报的url', (2+1), (2 +2);SELECT COUNT(parent.menu_id)-1) menuLevel, FROM t_sys_menu parentWHERE BETWEEN parent.lft AND parent.rgt AND node.menu_descr!='菜单'GROUPBY node.menu_id,node.menu_descr,node.lft,node.rgt,node.menu_url,node.menu_pidORDERBY node.lft1.3 如何插入一个新的节点第一步：选取要被插入新的节点左边节点的rgt的值rgtrgtrgtrgtSELECT rgtFROM t_sys_menuwhere menu_id='101'UPDATE t_sys_menuSET rgt = rgt +2WHERE rgt >11;UPDATE t_sys_menuSET lft = lft +2WHERE lft >11;INSERTINTO t_sys_menu(menu_id, menu_descr, menu_url, lft, rgt)VALUES('114','保单审核','', (11+1), (11 +2);SELECT COUNT(parent.menu_id)-1) menuLevel, FROM t_sys_menu parent WHERE BETWEEN parent.lftAND parent.rgt AND node.menu_descr!='菜单' GROUPBY node.menu_id,node.menu_descr,node.lft,node.rgt,node.menu_url,node.menu_pid ORDERBY node.lft1.3 如何删除一个节点SELECT lft, rgt, (rgt - lft +1) widthFROM t_sys_menuWHERE menu_id ='114'DELETEFROM t_sys_menuWHERE lftBETWEEN12AND15UPDATE t_sys_menuSET rgt = rgt -4WHERE rgt >15UPDATE t_sys_menuSET lft = lft -4WHERE lft >15SELECT COUNT(parent.menu_id)-1) menuLevel, FROM t_sys_menu parent WHERE BETWEEN parent.lftAND parent.rgt AND node.menu_descr!='菜单' GROUPBY node.menu_id,node.menu_descr,node.lft,node.rgt,node.menu_url,node.menu_pid ORDERBY node.lft
