public void test_0() throws Exception {
String sql = "SELECT 1--1 from t where id = 1";
MySqlStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement stmt = statementList.get(0);
SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
SQLSelect select = selectStmt.getSelect();
Assert.assertNotNull(select.getQuery());
MySqlSelectQueryBlock queryBlock = (MySqlSelectQueryBlock) select.getQuery();
Assert.assertNull(queryBlock.getOrderBy());
// print(statementList);
Assert.assertEquals(1, statementList.size());
MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
stmt.accept(visitor);
Assert.assertEquals(1, visitor.getTables().size());
Assert.assertEquals(1, visitor.getColumns().size());
Assert.assertEquals(1, visitor.getConditions().size());
Assert.assertEquals(0, visitor.getOrderByColumns().size());
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t")));
String output = SQLUtils.toMySqlString(stmt);
Assert.assertEquals(
"SELECT 1 - -1" //
+ "\nFROM t" //
+ "\nWHERE id = 1", //
output);
}
public void test_0() throws Exception {
String sql = "SELECT * FROM t_department WHERE name IN ('0000','4444') ORDER BY name ASC";
MySqlStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement stmt = statementList.get(0);
SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
SQLSelect select = selectStmt.getSelect();
Assert.assertNotNull(select.getQuery());
MySqlSelectQueryBlock queryBlock = (MySqlSelectQueryBlock) select.getQuery();
Assert.assertNotNull(queryBlock.getOrderBy());
// print(statementList);
Assert.assertEquals(1, statementList.size());
MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
stmt.accept(visitor);
Assert.assertEquals(1, visitor.getTables().size());
Assert.assertEquals(2, visitor.getColumns().size());
Assert.assertEquals(1, visitor.getConditions().size());
Assert.assertEquals(1, visitor.getOrderByColumns().size());
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t_department")));
}
public void test_0() throws Exception {
String sql = "SELECT `group`.* FROM `group` WHERE (group.group_id=159754)";
MySqlStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement stmt = statementList.get(0);
SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
SQLSelect select = selectStmt.getSelect();
Assert.assertNotNull(select.getQuery());
MySqlSelectQueryBlock queryBlock = (MySqlSelectQueryBlock) select.getQuery();
Assert.assertNull(queryBlock.getOrderBy());
print(statementList);
Assert.assertEquals(1, statementList.size());
MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
stmt.accept(visitor);
Assert.assertEquals(1, visitor.getTables().size());
Assert.assertEquals(2, visitor.getColumns().size());
Assert.assertEquals(1, visitor.getConditions().size());
Assert.assertEquals(0, visitor.getOrderByColumns().size());
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("group")));
}
public void test_leftJoin() throws Exception {
String sql =
"SELECT host.id as id" //
+ ", host.item_id as itemId" //
+ ", host.node_id as nodeId" //
+ ", host.node_type as nodeType" //
+ ", host.begin_time as beginTime" //
+ ", host.end_time as endTime" //
+ ", host.gmt_create as gmtCreate" //
+ ", host.gmt_modify as gmtModify" //
+ ", host.reason as reason" //
+ ", host.creator_id as creatorId" //
+ ", host.modifier_id as modifierId" //
+ ", user.name as creator" //
+ ", user.name as modifier" //
+ ", user.nick_name as nickName " //
+ " FROM notice_close_node host left join sys_user user on user.id = host.modifier_id";
TeradataStatementParser parser = new TeradataStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement statemen = statementList.get(0);
print(statementList);
Assert.assertEquals(1, statementList.size());
{
SQLSelectStatement selectStmt = (SQLSelectStatement) statemen;
SQLSelect selectSQL = selectStmt.getSelect();
TeradataSelectQueryBlock queryBlock = (TeradataSelectQueryBlock) selectSQL.getQuery();
Assert.assertTrue(queryBlock.getFrom() instanceof SQLJoinTableSource);
SQLJoinTableSource joinSource = (SQLJoinTableSource) queryBlock.getFrom();
Assert.assertEquals(joinSource.getJoinType().toString(), "LEFT_OUTER_JOIN");
Assert.assertEquals(joinSource.getLeft().toString(), "notice_close_node");
Assert.assertEquals(joinSource.getRight().toString(), "sys_user");
Assert.assertNotNull(joinSource.getCondition());
}
TeradataSchemaStatVisitor visitor = new TeradataSchemaStatVisitor();
statemen.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(2, visitor.getTables().size());
Assert.assertEquals(15, visitor.getColumns().size());
Assert.assertEquals(2, visitor.getConditions().size());
Assert.assertTrue(visitor.getColumns().contains(new Column("sys_user", "id")));
Assert.assertTrue(
visitor.getColumns().contains(new Column("notice_close_node", "modifier_id")));
System.out.println(
"------ end of " + new Object() {}.getClass().getEnclosingMethod().getName() + " ------");
}
public void test_0() throws Exception {
String sql = "SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);";
MySqlStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement stmt = statementList.get(0);
SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
SQLSelect select = selectStmt.getSelect();
Assert.assertNotNull(select.getQuery());
MySqlSelectQueryBlock queryBlock = (MySqlSelectQueryBlock) select.getQuery();
Assert.assertNull(queryBlock.getOrderBy());
// print(statementList);
Assert.assertEquals(1, statementList.size());
MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
stmt.accept(visitor);
Assert.assertEquals(2, visitor.getTables().size());
Assert.assertEquals(3, visitor.getColumns().size());
Assert.assertEquals(1, visitor.getConditions().size());
Assert.assertEquals(0, visitor.getOrderByColumns().size());
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t1")));
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t2")));
String output = SQLUtils.toMySqlString(stmt);
Assert.assertEquals(
"SELECT *" //
+ "\nFROM t1" //
+ "\nWHERE column1 = (" //
+ "\n\tSELECT column1" //
+ "\n\tFROM t2" //
+ "\n\t)", //
output);
String output_lcase = SQLUtils.toMySqlString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION);
Assert.assertEquals(
"select *" //
+ "\nfrom t1" //
+ "\nwhere column1 = (" //
+ "\n\tselect column1" //
+ "\n\tfrom t2" //
+ "\n\t)", //
output_lcase);
}
@Override
public boolean equals(Object obj) {
if (this == obj) {
return true;
}
if (obj == null) {
return false;
}
if (getClass() != obj.getClass()) {
return false;
}
SQLInSubQueryExpr other = (SQLInSubQueryExpr) obj;
if (expr == null) {
if (other.expr != null) {
return false;
}
} else if (!expr.equals(other.expr)) {
return false;
}
if (not != other.not) {
return false;
}
if (subQuery == null) {
if (other.subQuery != null) {
return false;
}
} else if (!subQuery.equals(other.subQuery)) {
return false;
}
return true;
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((expr == null) ? 0 : expr.hashCode());
result = prime * result + (not ? 1231 : 1237);
result = prime * result + ((subQuery == null) ? 0 : subQuery.hashCode());
return result;
}
public void test_crossJoin() throws Exception {
String sql = "SELECT * FROM t1 CROSS JOIN t2 ON t1.id = t2.id;";
TeradataStatementParser parser = new TeradataStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement statement = statementList.get(0);
print(statementList);
Assert.assertEquals(1, statementList.size());
{
SQLSelectStatement selectStmt = (SQLSelectStatement) statement;
SQLSelect selectSQL = selectStmt.getSelect();
TeradataSelectQueryBlock queryBlock = (TeradataSelectQueryBlock) selectSQL.getQuery();
Assert.assertTrue(queryBlock.getFrom() instanceof SQLJoinTableSource);
SQLJoinTableSource joinSource = (SQLJoinTableSource) queryBlock.getFrom();
Assert.assertEquals(joinSource.getJoinType().toString(), "CROSS_JOIN");
Assert.assertEquals(joinSource.getLeft().toString(), "t1");
Assert.assertEquals(joinSource.getRight().toString(), "t2");
Assert.assertNotNull(joinSource.getCondition());
}
TeradataSchemaStatVisitor visitor = new TeradataSchemaStatVisitor();
statement.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(2, visitor.getTables().size());
System.out.println(
"------ end of " + new Object() {}.getClass().getEnclosingMethod().getName() + " ------");
}
public boolean visit(SQLSelect x) {
setCurrentTable(x);
if (x.getOrderBy() != null) {
x.getOrderBy().setParent(x);
}
accept(x.getQuery());
String originalTable = getCurrentTable();
setCurrentTable((String) x.getQuery().getAttribute("table"));
x.putAttribute("_old_local_", originalTable);
accept(x.getOrderBy());
setCurrentTable(originalTable);
return false;
}