Examples with SQLServerStatementParser com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser used on opensource projects

Example 81 with SQLServerStatementParser

use of com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser in project druid by alibaba.

the class SQLServerSelectTest7 method test_isEmpty.

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);
}

Example 82 with SQLServerStatementParser

use of com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser in project druid by alibaba.

the class SQLServerSelectTest8 method test_isEmpty.

public void test_isEmpty() throws Exception {
    String sql = "SELECT Location.Distance (@p)" + " FROM Cities;";
    String expect = //
    "SELECT Location.Distance(@p)" + "\nFROM Cities";
    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    SQLStatement stmt = parser.parseStatementList().get(0);
    String text = TestUtils.outputSqlServer(stmt);
    Assert.assertEquals(expect, text);
//        System.out.println(text);
}

Example 83 with SQLServerStatementParser

use of com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser in project druid by alibaba.

the class SQLServerSelectTest9 method test_isEmpty.

public void test_isEmpty() throws Exception {
    String sql = //
    "SELECT AVG(UnitPrice) AS 'Average Price' " + "FROM Sales.SalesOrderDetail;";
    String expect = //
    "SELECT AVG(UnitPrice) AS 'Average Price'" + "\nFROM Sales.SalesOrderDetail";
    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    SQLStatement stmt = parser.parseStatementList().get(0);
    String text = TestUtils.outputSqlServer(stmt);
    Assert.assertEquals(expect, text);
//        System.out.println(text);
}

Example 84 with SQLServerStatementParser

use of com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser in project Mycat-Server by MyCATApache.

the class DruidSelectSqlServerParser method sqlserverParse.

private void sqlserverParse(SchemaConfig schema, RouteResultset rrs) {
    // 使用sqlserver的解析，否则会有部分语法识别错误
    SQLServerStatementParser oracleParser = new SQLServerStatementParser(getCtx().getSql());
    SQLSelectStatement oracleStmt = (SQLSelectStatement) oracleParser.parseStatement();
    SQLSelectQuery oracleSqlSelectQuery = oracleStmt.getSelect().getQuery();
    if (oracleSqlSelectQuery instanceof SQLServerSelectQueryBlock) {
        parseSqlServerPageSql(oracleStmt, rrs, (SQLServerSelectQueryBlock) oracleSqlSelectQuery, schema);
        if (isNeedParseOrderAgg) {
            parseOrderAggGroupSqlServer(schema, oracleStmt, rrs, (SQLServerSelectQueryBlock) oracleSqlSelectQuery);
        }
    }
}

Example 85 with SQLServerStatementParser

use of com.alibaba.druid.sql.dialect.sqlserver.parser.SQLServerStatementParser in project druid by alibaba.

the class SqlserverGroupByTest method testGroupBy.

public void testGroupBy() throws Exception {
    String sql = "SELECT a.workflowid, COUNT(1) FROM workflow_base a GROUP BY a.workflowid";
    SQLStatementParser parser = new SQLServerStatementParser(sql);
    // 
    SQLSelectStatement stmt = (SQLSelectStatement) parser.parseStatement();
    try {
        stmt.toString();
    } catch (ClassCastException e) {
        Assert.fail(e.getMessage());
    }
}