|
马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
mysql的原来开发者又开发了MariaDB,MariaDB适合用来替代mysql吗</p>文由网友whl供稿,特此感激!
/**
*Desc:取栏目树,过滤用户权限和有效栏目
*Author:WHL
*Date:2009-05-3115:17
*/
/**1.取某用户有权限(np_cms_column_security表有纪录且t.action_1=‘1′)的栏目标树**/01.createorreplaceviewV_NP_CTREE_BSas02.selectB.*from(03.selectA.*,lag(A.column_id)over(partitionbyA.column_idorderby0)RK04.from(select/*+choose*/05.t.*06.fromnp_cms_columnt07.wheret.is_active=108.connectbypriort.column_id=t.parent_id09.startwitht.column_idin(selectt.column_id10.fromnp_cms_column_securityt11.wheret.subject_id=mazj12./*这里增加脚色过滤*/13.andt.action_1=1))A)B14.wherenotexists15.(select016.from(selectdistinctd.column_id17.fromnp_cms_columnd18.connectbypriord.column_id=d.parent_id19.startwithd.column_idin20.(selectt.column_id21.fromnp_cms_column_securityt22.wheret.subject_id=mazj23./*这里增加脚色过滤*/24.andt.action_1=025./*扫除有权限树下的非受权ID,既Action_1=0的*/26.andexists27.(select028.from(selectdistinctd.column_id29.fromnp_cms_columnd30.connectbypriord.column_id=31.d.parent_id32.startwithd.column_idin33.(selectt.column_id34.fromnp_cms_column_securityt35.wheret.subject_id=36.mazj37./*这里增加脚色过滤*/38.andt.action_1=1))C139.whereC1.column_id=t.column_id))40.andd.is_active=1)C41.whereC.column_id=B.column_idandB.RKisnull)andB.RKisnull42.unionall43.selectc.*,0RKfromnp_cms_columncwherec.parent_id=0;————————————————————————
/**2.失掉栏目标假造父亲ID(思索到把断层的节点接起来)**/
01.createorreplaceviewV_NP_CTREE_PAas02.selectB.*,03.(caseB.column_id04.when1then0elsenvl(B.father,1)end)VFA05.from(selectv.*,06.(selectvv.column_id07.fromV_NP_CTREE_BSvv08.wherevv.column_id=v.parent_id)FATHER09.fromV_NP_CTREE_BSv)B;————————————————————————
/**3.掏出流派必要的栏目树**/
1.--createorreplaceviewV_NP_CTREE_RSas2.select3.D.*,LPAD(,2*level-1)SYS_CONNECT_BY_PATH(D.COLUMN_NAME,/)"Path"4.from(selectc.*5.fromV_NP_CTREE_PAc6.orderbyc.VFA,c.disorderdesc,c.column_iddesc)D7.connectbypriorD.column_id=D.VFA8.startwithD.column_id=1;目前的方案是用mysqlbinlog工具,增加一个Flashback参数,输出结果为一个新的binlog文件――姑且叫做flashbacklog,这个flashbacklog顺序执行,可制定某张表和执行到哪个pos,来实现数据库的闪回。 |
|