public void test_0() {
String sql = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
SQLServerStatementParser parser = new SQLServerStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
SQLStatement stmt = stmtList.get(0);
Assert.assertEquals(1, stmtList.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(0, visitor.getTables().size());
Assert.assertEquals(0, visitor.getColumns().size());
Assert.assertEquals(0, visitor.getConditions().size());
String text = SQLUtils.toSQLString(stmtList, JdbcUtils.SQL_SERVER);
Assert.assertEquals("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED", text);
}
public void test() throws Exception {
String sql =
"INSERT [dbo].[SurveyAnswer]([CustomerId], [QuestionId], [OptionId], [CreateTime], [LastUpdateTime]) VALUES (@0, @1, @2, @3, @4)";
SQLServerStatementParser parser = new SQLServerStatementParser(sql);
parser.setParseCompleteValues(false);
parser.setParseValuesSize(3);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement stmt = statementList.get(0);
SQLServerInsertStatement insertStmt = (SQLServerInsertStatement) stmt;
Assert.assertEquals(1, insertStmt.getValuesList().size());
Assert.assertEquals(5, insertStmt.getValues().getValues().size());
Assert.assertEquals(5, insertStmt.getColumns().size());
Assert.assertEquals(1, statementList.size());
SQLServerSchemaStatVisitor visitor = new SQLServerSchemaStatVisitor();
stmt.accept(visitor);
String formatSql =
"INSERT INTO [dbo].[SurveyAnswer]" //
+ "\n\t([CustomerId], [QuestionId], [OptionId], [CreateTime], [LastUpdateTime])" //
+ "\nVALUES" //
+ "\n(@0, @1, @2, @3, @4)";
Assert.assertEquals(formatSql, SQLUtils.toSQLServerString(insertStmt));
}
public void test_isEmpty() throws Exception {
String sql = "USE AdventureWorks2008R2;";
String expect = "USE AdventureWorks2008R2";
SQLServerStatementParser parser = new SQLServerStatementParser(sql);
SQLStatement stmt = parser.parseStatementList().get(0);
String text = TestUtils.outputSqlServer(stmt);
Assert.assertEquals(expect, text);
System.out.println(text);
}
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")));
}
public void test_isEmpty() throws Exception {
String sql =
"SELECT name + ‘-’ + master.sys.fn_varbintohexstr(password_hash) from master.sys.sql_logins";
String expect =
"SELECT name + ‘ - ’ + master.sys.fn_varbintohexstr(password_hash)\n"
+ //
"FROM master.sys.sql_logins";
SQLServerStatementParser parser = new SQLServerStatementParser(sql);
SQLStatement stmt = parser.parseStatementList().get(0);
String text = TestUtils.outputSqlServer(stmt);
Assert.assertEquals(expect, text);
System.out.println(text);
}
public void test_3() {
String sql = "SET @state = N'Oregon'";
SQLServerStatementParser parser = new SQLServerStatementParser(sql);
List<SQLStatement> stmtList = parser.parseStatementList();
SQLStatement stmt = stmtList.get(0);
Assert.assertEquals(1, stmtList.size());
SQLServerSchemaStatVisitor visitor = new SQLServerSchemaStatVisitor();
stmt.accept(visitor);
Assert.assertEquals(0, visitor.getTables().size());
Assert.assertEquals(0, visitor.getColumns().size());
Assert.assertEquals(0, visitor.getConditions().size());
String text = SQLUtils.toSQLString(stmtList, JdbcUtils.SQL_SERVER);
Assert.assertEquals("SET @state = N'Oregon'", text);
}
public void test_alter_first() throws Exception {
String sql = "ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2) ;";
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_exy" //
+ "\n\tALTER COLUMN column_a DECIMAL(5, 2)",
output);
Assert.assertEquals(1, visitor.getTables().size());
Assert.assertEquals(1, visitor.getColumns().size());
}
