SQLServerSelectTest7.java

/*
 * Copyright 1999-2017 Alibaba Group Holding Ltd.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.alibaba.druid.bvt.sql.sqlserver;

import junit.framework.TestCase;

import org.junit.Assert;

import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser;
import com.alibaba.druid.sql.test.TestUtils;

public class SQLServerSelectTest7 extends TestCase {

    public void test_isEmpty() throws Exception {
        String sql = "with menu_view as(" +
        		"\n            select t.*,1 level from sec_portal_menu t where t.parent_id = ?" +
        		"\n            union all" +
        		"\n            select t.*,level + 1 from sec_portal_menu t , menu_view x where t.parent_id = x.menu_id" +
        		"\n        )" +
        		"\n        select t.menu_id \"id\"," +
        		"\n               t.menu_name \"name\"," +
        		"\n               t.parent_id \"pId\"," +
        		"\n               case t.level when 1 then 'true' else 'false' end \"open\"," +
        		"\n               t.link_type \"linkType\"" +
        		"\n                from menu_view t" +
        		"\n                where 1=1" +
        		"\n                and t.deleted = 0" +
        		"\n                --菜单权限控制" +
        		"\n                AND t.link_type in ('simple','link')" +
        		"\n                AND (" +
        		"\n                     EXISTS (" +
        		"\n                        select p.entity_code from sec_role_auth p where p.entity_code = t.menu_id" +
        		"\n                        and p.entity_type = 'menu'" +
        		"\n                        and p.role_id in (" +
        		"\n                            select r.role_code from sec_role_member rm ,sec_role r where rm.entity_type = 'user'" +
        		"\n                            and entity_code = ? --用户ID" +
        		"\n                            and r.role_id = rm.role_id" +
        		"\n                            and r.enabled = 1" +
        		"\n                            and r.deleted = 0" +
        		"\n                        )" +
        		"\n                     )" +
        		"\n                     or '1'= ? --超级管理员账户id" +
        		"\n                     or t.need_control = 0" +
        		"\n                )" +
        		"\n                AND (" +
        		"\n                    t.enabled = 1 or '1'= ? --超级管理员账户id" +
        		"\n                    or t.need_control = 0" +
        		"\n                )" +
        		"\n        order by t.sort_order";

        String expect = "WITH" +
        		"\n\tmenu_view" +
        		"\n\tAS" +
        		"\n\t(" +
        		"\n\t\tSELECT t.*, 1 AS level" +
        		"\n\t\tFROM sec_portal_menu t" +
        		"\n\t\tWHERE t.parent_id = ?" +
        		"\n\t\tUNION ALL" +
        		"\n\t\tSELECT t.*, level + 1" +
        		"\n\t\tFROM sec_portal_menu t, menu_view x" +
        		"\n\t\tWHERE t.parent_id = x.menu_id" +
        		"\n\t)" +
        		"\nSELECT t.menu_id AS \"id\", t.menu_name AS \"name\", t.parent_id AS \"pId\", CASE t.level WHEN 1 THEN 'true' ELSE 'false' END AS \"open\", t.link_type AS \"linkType\"" +
        		"\nFROM menu_view t" +
				"\nWHERE 1 = 1" +
				"\n\tAND t.deleted = 0" +
				"\n\tAND t.link_type IN ('simple', 'link')" +
				"\n\tAND (EXISTS (" + 
				"\n\t\t\tSELECT p.entity_code" +
				"\n\t\t\tFROM sec_role_auth p" +
				"\n\t\t\tWHERE p.entity_code = t.menu_id" +
				"\n\t\t\t\tAND p.entity_type = 'menu'" +
				"\n\t\t\t\tAND p.role_id IN (SELECT r.role_code" +
				"\n\t\t\t\t\tFROM sec_role_member rm, sec_role r" +
				"\n\t\t\t\t\tWHERE rm.entity_type = 'user'" +
				"\n\t\t\t\t\t\tAND entity_code = ?" +
				"\n\t\t\t\t\t\tAND r.role_id = rm.role_id" +
				"\n\t\t\t\t\t\tAND r.enabled = 1" +
				"\n\t\t\t\t\t\tAND r.deleted = 0)"+ 
				"\n\t\t\t)" +
				"\n\t\tOR '1' = ?" +
				"\n\t\tOR t.need_control = 0)" +
				"\n\tAND (t.enabled = 1" +
				"\n\t\tOR '1' = ?" +
				"\n\t\tOR t.need_control = 0)" +
				"\nORDER BY t.sort_order";

        SQLServerStatementParser parser = new SQLServerStatementParser(sql);
        SQLStatement stmt = parser.parseStatementList().get(0);

        String text = TestUtils.outputSqlServer(stmt);

        Assert.assertEquals(expect, text);

//        System.out.println(text);
    }
}