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);
}
public void route(
SystemConfig sysConfig,
SchemaConfig schema,
int sqlType,
String realSQL,
String charset,
ServerConnection sc,
LayerCachePool cachePool) {
int rs = ServerParse.parse(realSQL);
this.sqltype = rs & 0xff;
this.sysConfig = sysConfig;
this.schema = schema;
this.charset = charset;
this.sc = sc;
this.cachePool = cachePool;
try {
// RouteStrategy routes=RouteStrategyFactory.getRouteStrategy();
// rrs =RouteStrategyFactory.getRouteStrategy().route(sysConfig, schema, sqlType2,
// realSQL,charset, sc, cachePool);
MySqlStatementParser parser = new MySqlStatementParser(realSQL);
SQLStatement statement = parser.parseStatement();
if (statement instanceof SQLSelectStatement) {
SQLSelectStatement st = (SQLSelectStatement) statement;
SQLSelectQuery sqlSelectQuery = st.getSelect().getQuery();
if (sqlSelectQuery instanceof MySqlSelectQueryBlock) {
MySqlSelectQueryBlock mysqlSelectQuery =
(MySqlSelectQueryBlock) st.getSelect().getQuery();
joinParser = new JoinParser(mysqlSelectQuery, realSQL);
joinParser.parser();
}
}
/*
if (routes instanceof DruidMysqlRouteStrategy) {
SQLSelectStatement st=((DruidMysqlRouteStrategy) routes).getSQLStatement();
SQLSelectQuery sqlSelectQuery =st.getSelect().getQuery();
if(sqlSelectQuery instanceof MySqlSelectQueryBlock) {
MySqlSelectQueryBlock mysqlSelectQuery = (MySqlSelectQueryBlock)st.getSelect().getQuery();
joinParser=new JoinParser(mysqlSelectQuery,realSQL);
joinParser.parser();
}
}
*/
} catch (Exception e) {
}
}
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() + " ------");
}