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

Example 96 with SQLServerStatementParser

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

the class SQLServerSelectTest24 method test_simple.

public void test_simple() throws Exception {
    // 
    String sql = "SELECT TOP 100 *\n" + "FROM\n" + "(SELECT ROW_NUMBER()\n" + "OVER (ORDER BY DateUpdated DESC) PAGE_ROW_NUMBER, *\n" + "FROM\n" + "(SELECT x.*\n" + "FROM\n" + "(SELECT TOP 2000 a.*\n" + "FROM pk_Battle a\n" + "WHERE a.RequestUserId = 33460323\n" + "AND a.IsActive = 1\n" + "AND a.BattleType <> 2\n" + "AND a.Status NOT IN (0, 2)\n" + "AND EXISTS\n" + "(SELECT TOP 1 1\n" + "FROM pk_BattleExt\n" + "WHERE BattleId = a.Id\n" + "AND RequestIsViewResults = 0)\n" + "ORDER BY a.Id DESC\n" + "UNION\n" + "SELECT TOP 1000 a.*\n" + "FROM pk_Battle a\n" + "WHERE a.ResponseUserId = 33460323\n" + "AND a.IsActive = 1\n" + "AND a.BattleType = 1\n" + "AND a.Status NOT IN (0, 2)\n" + "AND EXISTS\n" + "(SELECT TOP 1 1\n" + "FROM pk_BattleExt\n" + "WHERE BattleId = a.Id\n" + "AND ResponseIsViewResults = 0)\n" + "ORDER BY a.Id DESC) x) AS PAGE_TABLE_ALIAS) AS PAGE_TABLE_ALIAS\n" + "WHERE PAGE_ROW_NUMBER > 0\n" + // 
    "ORDER BY PAGE_ROW_NUMBER";
    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    SQLStatement stmt = parser.parseStatementList().get(0);
    {
        String text = SQLUtils.toSQLServerString(stmt);
        Assert.assertEquals("SELECT TOP 100 *\n" + "FROM (\n" + "\tSELECT ROW_NUMBER() OVER (ORDER BY DateUpdated DESC) AS PAGE_ROW_NUMBER, *\n" + "\tFROM (\n" + "\t\tSELECT x.*\n" + "\t\tFROM (\n" + "\t\t\tSELECT TOP 2000 a.*\n" + "\t\t\tFROM pk_Battle a\n" + "\t\t\tWHERE a.RequestUserId = 33460323\n" + "\t\t\t\tAND a.IsActive = 1\n" + "\t\t\t\tAND a.BattleType <> 2\n" + "\t\t\t\tAND a.Status NOT IN (0, 2)\n" + "\t\t\t\tAND EXISTS (\n" + "\t\t\t\t\tSELECT TOP 1 1\n" + "\t\t\t\t\tFROM pk_BattleExt\n" + "\t\t\t\t\tWHERE BattleId = a.Id\n" + "\t\t\t\t\t\tAND RequestIsViewResults = 0\n" + "\t\t\t\t)\n" + "\t\t\tORDER BY a.Id DESC\n" + "\t\t\tUNION\n" + "\t\t\tSELECT TOP 1000 a.*\n" + "\t\t\tFROM pk_Battle a\n" + "\t\t\tWHERE a.ResponseUserId = 33460323\n" + "\t\t\t\tAND a.IsActive = 1\n" + "\t\t\t\tAND a.BattleType = 1\n" + "\t\t\t\tAND a.Status NOT IN (0, 2)\n" + "\t\t\t\tAND EXISTS (\n" + "\t\t\t\t\tSELECT TOP 1 1\n" + "\t\t\t\t\tFROM pk_BattleExt\n" + "\t\t\t\t\tWHERE BattleId = a.Id\n" + "\t\t\t\t\t\tAND ResponseIsViewResults = 0\n" + "\t\t\t\t)\n" + "\t\t\tORDER BY a.Id DESC\n" + "\t\t) x\n" + "\t) PAGE_TABLE_ALIAS\n" + ") PAGE_TABLE_ALIAS\n" + "WHERE PAGE_ROW_NUMBER > 0\n" + "ORDER BY PAGE_ROW_NUMBER", text);
    }
    {
        String text = SQLUtils.toSQLServerString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION);
        Assert.assertEquals("select top 100 *\n" + "from (\n" + "\tselect row_number() over (order by DateUpdated desc) as PAGE_ROW_NUMBER, *\n" + "\tfrom (\n" + "\t\tselect x.*\n" + "\t\tfrom (\n" + "\t\t\tselect top 2000 a.*\n" + "\t\t\tfrom pk_Battle a\n" + "\t\t\twhere a.RequestUserId = 33460323\n" + "\t\t\t\tand a.IsActive = 1\n" + "\t\t\t\tand a.BattleType <> 2\n" + "\t\t\t\tand a.Status not in (0, 2)\n" + "\t\t\t\tand exists (\n" + "\t\t\t\t\tselect top 1 1\n" + "\t\t\t\t\tfrom pk_BattleExt\n" + "\t\t\t\t\twhere BattleId = a.Id\n" + "\t\t\t\t\t\tand RequestIsViewResults = 0\n" + "\t\t\t\t)\n" + "\t\t\torder by a.Id desc\n" + "\t\t\tunion\n" + "\t\t\tselect top 1000 a.*\n" + "\t\t\tfrom pk_Battle a\n" + "\t\t\twhere a.ResponseUserId = 33460323\n" + "\t\t\t\tand a.IsActive = 1\n" + "\t\t\t\tand a.BattleType = 1\n" + "\t\t\t\tand a.Status not in (0, 2)\n" + "\t\t\t\tand exists (\n" + "\t\t\t\t\tselect top 1 1\n" + "\t\t\t\t\tfrom pk_BattleExt\n" + "\t\t\t\t\twhere BattleId = a.Id\n" + "\t\t\t\t\t\tand ResponseIsViewResults = 0\n" + "\t\t\t\t)\n" + "\t\t\torder by a.Id desc\n" + "\t\t) x\n" + "\t) PAGE_TABLE_ALIAS\n" + ") PAGE_TABLE_ALIAS\n" + "where PAGE_ROW_NUMBER > 0\n" + "order by PAGE_ROW_NUMBER", text);
    }
}

Example 97 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";
    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    SQLStatement stmt = parser.parseStatementList().get(0);
    String text = TestUtils.outputSqlServer(stmt);
    assertEquals("WITH menu_view AS (\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)\n" + "SELECT t.menu_id AS \"id\", t.menu_name AS \"name\", t.parent_id AS \"pId\"\n" + "\t, CASE t.level\n" + "\t\tWHEN 1 THEN 'true'\n" + "\t\tELSE 'false'\n" + "\tEND AS \"open\", t.link_type AS \"linkType\"\n" + "FROM menu_view t\n" + "WHERE 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 (\n" + "\t\t\t\t\tSELECT 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\t)\n" + "\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)\n" + "ORDER BY t.sort_order", text);
// System.out.println(text);
}

Example 98 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\"\n" + "FROM 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 99 with SQLServerStatementParser

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

the class SQLServerCreateTableTest_4 method test_0.

public void test_0() throws Exception {
    String sql = // 
    "CREATE TABLE dbo.customfieldoption (" + // 
    "ID NUMERIC NOT NULL, CUSTOMFIELD NUMERIC, CUSTOMFIELDCONFIG NUMERIC, " + // 
    "PARENTOPTIONID NUMERIC, SEQUENCE NUMERIC, customvalue NVARCHAR(255), " + // 
    "optiontype NVARCHAR(60), disabled NVARCHAR(60), " + "CONSTRAINT PK_customfieldoption PRIMARY KEY (ID))";
    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLCreateTableStatement stmt = (SQLCreateTableStatement) statementList.get(0);
    Assert.assertEquals(1, statementList.size());
    String output = SQLUtils.toSQLString(stmt, JdbcConstants.SQL_SERVER);
    Assert.assertEquals(// 
    "CREATE TABLE dbo.customfieldoption (" + // 
    "\n\tID NUMERIC NOT NULL," + // 
    "\n\tCUSTOMFIELD NUMERIC," + // 
    "\n\tCUSTOMFIELDCONFIG NUMERIC," + // 
    "\n\tPARENTOPTIONID NUMERIC," + // 
    "\n\tSEQUENCE NUMERIC," + // 
    "\n\tcustomvalue NVARCHAR(255)," + // 
    "\n\toptiontype NVARCHAR(60)," + // 
    "\n\tdisabled NVARCHAR(60)," + // 
    "\n\tCONSTRAINT PK_customfieldoption PRIMARY KEY (ID)" + "\n)", output);
    SQLServerSchemaStatVisitor visitor = new SQLServerSchemaStatVisitor();
    stmt.accept(visitor);
    // System.out.println("Tables : " + visitor.getTables());
    // System.out.println("fields : " + visitor.getColumns());
    // System.out.println("coditions : " + visitor.getConditions());
    // System.out.println("orderBy : " + visitor.getOrderByColumns());
    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(9, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getConditions().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("dbo.customfieldoption")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("dbo.customfieldoption", "ID")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("dbo.customfieldoption", "CUSTOMFIELD")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("dbo.customfieldoption", "CUSTOMFIELDCONFIG")));
}

Example 100 with SQLServerStatementParser

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

the class SQLServerAlterTableTest_10 method test_alter_first.

public void test_alter_first() throws Exception {
    String sql = // 
    "ALTER TABLE dbo.doc_exc ADD column_b VARCHAR(20) NULL " + "    CONSTRAINT exb_unique UNIQUE ;";
    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    SQLStatement stmt = parser.parseStatementList().get(0);
    parser.match(Token.EOF);
    SQLServerSchemaStatVisitor visitor = new SQLServerSchemaStatVisitor();
    stmt.accept(visitor);
    // System.out.println("Tables : " + visitor.getTables());
    // System.out.println("fields : " + visitor.getColumns());
    // System.out.println("coditions : " + visitor.getConditions());
    // System.out.println("orderBy : " + visitor.getOrderByColumns());
    String output = SQLUtils.toSQLString(stmt, JdbcConstants.SQL_SERVER);
    Assert.assertEquals("ALTER TABLE dbo.doc_exc" + "\n\tADD column_b VARCHAR(20) NULL CONSTRAINT exb_unique UNIQUE;", output);
    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(1, visitor.getColumns().size());
}