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

Example 66 with SQLServerStatementParser

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

the class SQLServerSelectTest2 method test_isEmpty.

public void test_isEmpty() throws Exception {
    String sql = "SELECT TOP 1 name FROM (SELECT TOP 9 name FROM master..syslogins ORDER BY name ASC) sq ORDER BY name DESC ";
    String expect = "SELECT TOP 1 name\n" + "FROM (\n" + "\tSELECT TOP 9 name\n" + "\tFROM master..syslogins\n" + "\tORDER BY name ASC\n" + ") sq\n" + "ORDER BY name DESC";
    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 67 with SQLServerStatementParser

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

the class SQLServerSelectTest21 method test_simple.

public void test_simple() throws Exception {
    // 
    String sql = "SELECT First_Name + ' ' + Last Name FROM Employees ORDER BY First_Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;";
    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    SQLStatement stmt = parser.parseStatementList().get(0);
    {
        String text = SQLUtils.toSQLServerString(stmt);
        Assert.assertEquals(// 
        "SELECT First_Name + ' ' + Last AS Name" + // 
        "\nFROM Employees" + // 
        "\nORDER BY First_Name" + "\nOFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;", text);
    }
    {
        String text = SQLUtils.toSQLServerString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION);
        Assert.assertEquals(// 
        "select First_Name + ' ' + Last as Name" + // 
        "\nfrom Employees" + // 
        "\norder by First_Name" + "\noffset 10 rows fetch next 5 rows only;", text);
    }
}

Example 68 with SQLServerStatementParser

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

the class SQLServerCreateTableTest_1 method test_0.

public void test_0() throws Exception {
    String sql = //
    "CREATE TABLE [projects] (" + //
    "[id] int NOT NULL IDENTITY(1, 1) PRIMARY KEY, " + //
    "[name] NVARCHAR(256), [description] NVARCHAR(2000), " + //
    "[enabled] bit DEFAULT 1 NOT NULL, " + //
    "[scope] NVARCHAR(3), " + //
    "[qualifier] NVARCHAR(3), " + //
    "[kee] NVARCHAR(400), " + //
    "[root_id] int, " + //
    "[profile_id] int, " + //
    "[language] NVARCHAR(5), " + //
    "[copy_resource_id] int, " + "[long_name] NVARCHAR(256)) ";
    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLCreateTableStatement stmt = (SQLCreateTableStatement) statementList.get(0);
    Assert.assertEquals(1, statementList.size());
    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(12, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getConditions().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("projects")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("projects", "id")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("projects", "name")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("projects", "enabled")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("projects", "scope")));
}

Example 69 with SQLServerStatementParser

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

the class SQLServerCreateTableTest_3 method test_0.

public void test_0() throws Exception {
    String sql = //
    "CREATE TABLE dbo.cwd_directory (" + //
    "ID NUMERIC NOT NULL, directory_name NVARCHAR(255), " + //
    "lower_directory_name NVARCHAR(255), created_date DATETIME, " + //
    "updated_date DATETIME, active int, description NVARCHAR(255), " + //
    "impl_class NVARCHAR(255), lower_impl_class NVARCHAR(255), " + //
    "directory_type NVARCHAR(60), directory_position NUMERIC, " + "CONSTRAINT PK_cwd_directory PRIMARY KEY (ID))";
    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLCreateTableStatement stmt = (SQLCreateTableStatement) statementList.get(0);
    Assert.assertEquals(1, statementList.size());
    Assert.assertEquals(//
    "CREATE TABLE dbo.cwd_directory (" + //
    "\n\tID NUMERIC NOT NULL," + //
    "\n\tdirectory_name NVARCHAR(255)," + //
    "\n\tlower_directory_name NVARCHAR(255)," + //
    "\n\tcreated_date DATETIME," + //
    "\n\tupdated_date DATETIME," + //
    "\n\tactive int," + //
    "\n\tdescription NVARCHAR(255)," + //
    "\n\timpl_class NVARCHAR(255)," + //
    "\n\tlower_impl_class NVARCHAR(255)," + //
    "\n\tdirectory_type NVARCHAR(60)," + //
    "\n\tdirectory_position NUMERIC," + //
    "\n\tCONSTRAINT PK_cwd_directory PRIMARY KEY (ID)" + "\n)", SQLUtils.toSQLString(stmt, JdbcConstants.SQL_SERVER));
    Assert.assertEquals(//
    "create table dbo.cwd_directory (" + //
    "\n\tID NUMERIC not null," + //
    "\n\tdirectory_name NVARCHAR(255)," + //
    "\n\tlower_directory_name NVARCHAR(255)," + //
    "\n\tcreated_date DATETIME," + //
    "\n\tupdated_date DATETIME," + //
    "\n\tactive int," + //
    "\n\tdescription NVARCHAR(255)," + //
    "\n\timpl_class NVARCHAR(255)," + //
    "\n\tlower_impl_class NVARCHAR(255)," + //
    "\n\tdirectory_type NVARCHAR(60)," + //
    "\n\tdirectory_position NUMERIC," + //
    "\n\tconstraint PK_cwd_directory primary key (ID)" + "\n)", SQLUtils.toSQLString(stmt, JdbcConstants.SQL_SERVER, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));
    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(12, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getConditions().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("dbo.cwd_directory")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("dbo.cwd_directory", "ID")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("dbo.cwd_directory", "directory_name")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("dbo.cwd_directory", "lower_directory_name")));
}

Example 70 with SQLServerStatementParser

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

the class SQLServerCreateTableTest_7 method test_0.

public void test_0() throws Exception {
    String sql = "create table leave_jpa (" + " id bigint identity not null, " + " user_id varchar(255), " + " 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 leave_jpa (" + "\n\tid bigint DEFAULT NULL IDENTITY," + "\n\tuser_id varchar(255)," + "\n\tPRIMARY 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(2, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getConditions().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("leave_jpa")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("leave_jpa", "id")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("leave_jpa", "user_id")));
}