private String getSqlStat(Integer id) {
Map<String, Object> map = statManagerFacade.getSqlStatData(id);
if (map == null) {
return returnJSONResult(RESULT_CODE_ERROR, null);
}
String dbType = (String) map.get("DbType");
String sql = (String) map.get("SQL");
map.put("formattedSql", SQLUtils.format(sql, dbType));
List<SQLStatement> statementList = SQLUtils.parseStatements(sql, dbType);
if (!statementList.isEmpty()) {
SQLStatement sqlStmt = statementList.get(0);
SchemaStatVisitor visitor = SQLUtils.createSchemaStatVisitor(dbType);
sqlStmt.accept(visitor);
map.put("parsedTable", visitor.getTables().toString());
map.put("parsedFields", visitor.getColumns().toString());
map.put("parsedConditions", visitor.getConditions().toString());
map.put("parsedRelationships", visitor.getRelationships().toString());
map.put("parsedOrderbycolumns", visitor.getOrderByColumns().toString());
}
DateFormat format = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss:SSS");
Date maxTimespanOccurTime = (Date) map.get("MaxTimespanOccurTime");
if (maxTimespanOccurTime != null) {
map.put("MaxTimespanOccurTime", format.format(maxTimespanOccurTime));
}
return returnJSONResult(map == null ? RESULT_CODE_ERROR : RESULT_CODE_SUCCESS, map);
}
public void test_0() throws Exception {
String sql = "GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';";
MySqlStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement stmt = statementList.get(0);
print(statementList);
Assert.assertEquals(1, statementList.size());
MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
stmt.accept(visitor);
String output = SQLUtils.toMySqlString(stmt);
Assert.assertEquals(
"GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost'", //
output);
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());
// Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("City")));
// Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t2")));
// Assert.assertTrue(visitor.getColumns().contains(new Column("t2", "id")));
}
public void test_0() throws Exception {
String sql = //
"ALTER TABLE ws_i18n_translate_item ADD CONSTRAINT ws_i18n_translate_item_pk " //
+ "PRIMARY KEY (id) USING INDEX ws_i18n_translate_item_pk";
OracleStatementParser parser = new OracleStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement statemen = statementList.get(0);
print(statementList);
Assert.assertEquals(1, statementList.size());
OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
statemen.accept(visitor);
System.out.println("Tables : " + visitor.getTables());
System.out.println("fields : " + visitor.getColumns());
System.out.println("coditions : " + visitor.getConditions());
System.out.println("relationships : " + visitor.getRelationships());
System.out.println("orderBy : " + visitor.getOrderByColumns());
Assert.assertEquals(1, visitor.getTables().size());
Assert.assertTrue(
visitor.getTables().containsKey(new TableStat.Name("ws_i18n_translate_item")));
Assert.assertEquals(1, visitor.getColumns().size());
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("ws_i18n_translate_item", "id")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("pivot_table",
// "YEAR")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("pivot_table",
// "order_mode")));
}
public void test_0() throws Exception {
String sql = //
"alter index PRODUCT_ADDITION_IND1 monitoring usage";
OracleStatementParser parser = new OracleStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement statemen = statementList.get(0);
print(statementList);
Assert.assertEquals(1, statementList.size());
OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
statemen.accept(visitor);
System.out.println("Tables : " + visitor.getTables());
System.out.println("fields : " + visitor.getColumns());
System.out.println("coditions : " + visitor.getConditions());
System.out.println("relationships : " + visitor.getRelationships());
System.out.println("orderBy : " + visitor.getOrderByColumns());
Assert.assertEquals(0, visitor.getTables().size());
// Assert.assertTrue(visitor.getTables().containsKey(new
// TableStat.Name("cdc.en_complaint_ipr_stat_fdt0")));
Assert.assertEquals(0, visitor.getColumns().size());
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("pivot_table", "*")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("pivot_table",
// "YEAR")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("pivot_table",
// "order_mode")));
}
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_0() throws Exception {
String sql = "GRANT CREATE TEMPORARY TABLE ON mydb.* TO 'someuser'@'somehost';";
MySqlStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement stmt = statementList.get(0);
// print(statementList);
Assert.assertEquals(1, statementList.size());
MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
stmt.accept(visitor);
Assert.assertEquals(
"GRANT CREATE TEMPORARY TABLE ON mydb.* TO 'someuser'@'somehost'", //
SQLUtils.toMySqlString(stmt));
Assert.assertEquals(
"grant CREATE TEMPORARY TABLE on mydb.* to 'someuser'@'somehost'", //
SQLUtils.toMySqlString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));
// 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(0, visitor.getColumns().size());
Assert.assertEquals(0, visitor.getConditions().size());
// Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("City")));
// Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t2")));
// Assert.assertTrue(visitor.getColumns().contains(new Column("t2", "id")));
}
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_0() throws Exception {
String sql = //
"select (select decode(count(*),1,'YES','NO') FROM sys.obj$ o, sys.user$ u WHERE u.name = 'PERFSTAT' AND o.owner# = u.user# AND o.name = 'STATSPACK' AND o.type# = 11 AND o.status = 1) is_installed , (select nvl(INTERVAL,'') from dba_jobs where what like 'statspack.snap%' and SCHEMA_USER='******' and rownum =1) freq from dual"; //
OracleStatementParser parser = new OracleStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement statemen = statementList.get(0);
print(statementList);
Assert.assertEquals(1, statementList.size());
OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
statemen.accept(visitor);
System.out.println("Tables : " + visitor.getTables());
System.out.println("fields : " + visitor.getColumns());
System.out.println("coditions : " + visitor.getConditions());
System.out.println("relationships : " + visitor.getRelationships());
System.out.println("orderBy : " + visitor.getOrderByColumns());
Assert.assertEquals(3, visitor.getTables().size());
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("sys.obj$")));
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("sys.user$")));
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("dba_jobs")));
Assert.assertEquals(10, visitor.getColumns().size());
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("pivot_table", "*")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("pivot_table",
// "YEAR")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("pivot_table",
// "order_mode")));
}
public void test_0() throws Exception {
String sql =
"insert into \"OPS$ADMIN\".\"ORASTAT\" select /*+ rule */ :5 statid, 'C' type, :6 version, bitand(h.spare2,7) flags, ot.name c1, null c2, null c3, c.name c4, u.name c5, h.distcnt n1, h.density n2, h.spare1 n3, h.sample_size n4, h.null_cnt n5, h.minimum n6, h.maximum n7, h.avgcln n8, decode(h.cache_cnt,0,null,1) n9, hg.bucket n10, hg.endpoint n11, null n12, h.timestamp# d1, h.lowval r1, h.hival r2, hg.epvalue ch1 from sys.user$ u, sys.obj$ ot, sys.col$ c, sys.hist_head$ h, histgrm$ hg where :3 is null and u.name = :1 and ot.owner# = u.user# and ot.name = :2 and ot.type# = 2 and c.obj# = ot.obj# and (:4 is null or c.name = :4) and h.obj# = ot.obj# and h.intcol# = c.intcol# and hg.obj#(+) = h.obj# and hg.intcol#(+) = h.intcol# union all select :5 statid, 'C' type, :6 version, bitand(h.spare2,7) flags, ot.name c1, op.subname c2, null c3, c.name c4, u.name c5, h.distcnt n1, h.density n2, h.spare1 n3, h.sample_size n4, h.null_cnt n5, h.minimum n6, h.maximum n7, h.avgcln n8, decode(h.cache_cnt,0,null,1) n9, hg.bucket n10, hg.endpoint n11, null n12, h.timestamp# d1, h.lowval r1, h.hival r2, hg.epvalue ch1 from sys.user$ u, sys.obj$ ot, sys.col$ c, sys.tabpart$ tp, sys.obj$ op, sys.hist_head$ h, histgrm$ hg where u.name = :1 and ot.owner# = u.user# and ot.name = :2 and ot.type# = 2 and c.obj# = ot.obj# and (:4 is null or c.name = :4) and tp.bo# = ot.obj# and tp.obj# = op.obj# and ((:3 is null and :vc_cascade_parts is not null) or op.subname = :3) and h.obj# = op.obj# and h.intcol# = c.intcol# and hg.obj#(+) = h.obj# and hg.intcol#(+) = h.intcol# union all select :5 statid, 'C' type, :6 version, bitand(h.spare2,7) flags, op.name c1, op.subname c2, null c3, c.name c4, u.name c5, h.distcnt n1, h.density n2, h.spare1 n3, h.sample_size n4, h.null_cnt n5, h.minimum n6, h.maximum n7, h.avgcln n8, decode(h.cache_cnt,0,null,1) n9, hg.bucket n10, hg.endpoint n11, null n12, h.timestamp# d1, h.lowval r1, h.hival r2, hg.epvalue ch1 from sys.user$ u, sys.col$ c, sys.tabcompart$ tp, sys.obj$ op, sys.hist_head$ h, histgrm$ hg where u.name = :1 and op.owner# = u.user# and op.name = :2 and op.type# = 19 and ((:3 is null and :vc_cascade_parts is not null) or op.subname = :3) and tp.obj# = op.obj# and c.obj# = tp.bo# and (:4 is null or c.name = :4) and h.obj# = op.obj# and h.intcol# = c.intcol# and hg.obj#(+) = h.obj# and hg.intcol#(+) = h.intcol# union all select :5 statid, 'C' type, :6 version, bitand(h.spare2,7) flags, op.name c1, op.subname c2, os.subname c3, c.name c4, u.name c5, h.distcnt n1, h.density n2, h.spare1 n3, h.sample_size n4, h.null_cnt n5, h.minimum n6, h.maximum n7, h.avgcln n8, decode(h.cache_cnt,0,null,1) n9, hg.bucket n10, hg.endpoint n11, null n12, h.timestamp# d1, h.lowval r1, h.hival r2, hg.epvalue ch1 from sys.user$ u, sys.col$ c, sys.tabcompart$ tp, sys.obj$ op, sys.tabsubpart$ ts, sys.obj$ os, sys.hist_head$ h, histgrm$ hg where u.name = :1 and op.owner# = u.user# and op.name = :2 and op.type# = 19 and tp.obj# = op.obj# and c.obj# = tp.bo# and (:4 is null or c.name = :4) and ts.pobj# = tp.obj# and ts.obj# = os.obj# and ((:3 is null and :vc_cascade_parts is not null) or (op.subname = :3 and (:vc_cascade_parts is not null or os.subname is null)) or os.subname = :3) and h.obj# = os.obj# and h.intcol# = c.intcol# and hg.obj#(+) = h.obj# and hg.intcol#(+) = h.intcol# order by c5,c1,c2,c3,c4,n10";
OracleStatementParser parser = new OracleStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement statemen = statementList.get(0);
print(statementList);
Assert.assertEquals(1, statementList.size());
OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
statemen.accept(visitor);
System.out.println("Tables : " + visitor.getTables());
System.out.println("fields : " + visitor.getColumns());
System.out.println("coditions : " + visitor.getConditions());
System.out.println("relationships : " + visitor.getRelationships());
Assert.assertEquals(9, visitor.getTables().size());
Assert.assertEquals(42, visitor.getColumns().size());
// Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("raises")));
// Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("employees")));
//
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees",
// "employee_id")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees",
// "salary")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees",
// "commission_pct")));
}
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));
}
void validateOracle(int id, String sql) throws Exception {
sql = sql.trim();
boolean sqlFlag = false;
String lowerSql = sql.toLowerCase();
if (lowerSql.startsWith("insert")
|| lowerSql.startsWith("select")
|| lowerSql.startsWith("upate")
|| lowerSql.startsWith("delete")
|| lowerSql.startsWith("create")
|| lowerSql.startsWith("drop")) {
sqlFlag = true;
}
if (!sqlFlag) {
update(id, sql, 2);
return;
}
OracleStatementParser parser = new OracleStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement statemen = statementList.get(0);
Assert.assertEquals(1, statementList.size());
StringBuilder out = new StringBuilder();
OracleParameterizedOutputVisitor visitor = new OracleParameterizedOutputVisitor(out);
statemen.accept(visitor);
update(id, out.toString(), 1);
System.out.println(sql);
System.out.println(out.toString());
}
public void exec_test(String resource) throws Exception {
// System.out.println(resource);
InputStream is = null;
is = Thread.currentThread().getContextClassLoader().getResourceAsStream(resource);
Reader reader = new InputStreamReader(is, "UTF-8");
String input = Utils.read(reader);
JdbcUtils.close(reader);
String[] items = input.split("---------------------------");
String sql = items[0].trim();
String expect = items[1].trim();
OdpsStatementParser parser = new OdpsStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement stmt = statementList.get(0);
Assert.assertEquals(1, statementList.size());
SchemaStatVisitor visitor = new OdpsSchemaStatVisitor();
stmt.accept(visitor);
// System.out.println(sql);
// System.out.println("Tables : " + visitor.getTables());
// System.out.println("fields : " + visitor.getColumns());
//
// System.out.println();
// System.out.println("---------------------------");
// System.out.println(SQLUtils.toOdpsString(stmt));
}
public void test_alter_first() throws Exception {
String sql = "alter table test add dspcode char(200)";
MySqlStatementParser parser = new MySqlStatementParser(sql);
SQLStatement stmt = parser.parseStatementList().get(0);
parser.match(Token.EOF);
MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
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.toMySqlString(stmt);
Assert.assertEquals(
"ALTER TABLE test"
+ //
"\n\tADD COLUMN dspcode char(200)",
output);
Assert.assertEquals(1, visitor.getTables().size());
Assert.assertEquals(1, visitor.getColumns().size());
Assert.assertTrue(visitor.getColumns().contains(new Column("test", "dspcode")));
}
public void test_0() throws Exception {
String sql = "CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';";
MySqlStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement stmt = statementList.get(0);
print(statementList);
Assert.assertEquals(1, statementList.size());
MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
stmt.accept(visitor);
String output = SQLUtils.toMySqlString(stmt);
Assert.assertEquals(
"CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'", //
output);
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());
// Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("City")));
// Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t2")));
// Assert.assertTrue(visitor.getColumns().contains(new Column("t2", "id")));
}
public void test_0() throws Exception {
String sql =
"CREATE TABLE products ("
+ //
" product_no integer,"
+ //
" name text,"
+ //
" price numeric DEFAULT 9.99"
+ //
");";
PGSQLStatementParser parser = new PGSQLStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement statemen = statementList.get(0);
// print(statementList);
Assert.assertEquals(1, statementList.size());
PGSchemaStatVisitor visitor = new PGSchemaStatVisitor();
statemen.accept(visitor);
// System.out.println("Tables : " + visitor.getTables());
// System.out.println("fields : " + visitor.getColumns());
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("products")));
Assert.assertTrue(
visitor.getTables().get(new TableStat.Name("products")).getCreateCount() == 1);
Assert.assertTrue(visitor.getColumns().size() == 3);
}
public void test_0() throws Exception {
String sql =
"WITH RECURSIVE included_parts(sub_part, part, quantity) AS ("
+ " SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'"
+ " UNION ALL"
+ " SELECT p.sub_part, p.part, p.quantity"
+ " FROM included_parts pr, parts p"
+ " WHERE p.part = pr.sub_part"
+ " )\n"
+ "SELECT sub_part, SUM(quantity) as total_quantity\n"
+ "FROM included_parts\n"
+ "GROUP BY sub_part";
PGSQLStatementParser parser = new PGSQLStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement statemen = statementList.get(0);
// print(statementList);
Assert.assertEquals(1, statementList.size());
PGSchemaStatVisitor visitor = new PGSchemaStatVisitor();
statemen.accept(visitor);
// System.out.println("Tables : " + visitor.getTables());
// System.out.println("fields : " + visitor.getColumns());
// System.out.println("coditions : " + visitor.getConditions());
Assert.assertEquals(5, visitor.getColumns().size());
Assert.assertEquals(2, visitor.getTables().size());
}
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 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 DSN81010.EMP;";
DB2StatementParser parser = new DB2StatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement stmt = statementList.get(0);
print(statementList);
Assert.assertEquals(1, statementList.size());
DB2SchemaStatVisitor visitor = new DB2SchemaStatVisitor();
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(1, visitor.getColumns().size());
Assert.assertEquals(0, visitor.getConditions().size());
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("DSN81010.EMP")));
// Assert.assertTrue(visitor.getColumns().contains(new Column("mytable", "last_name")));
// Assert.assertTrue(visitor.getColumns().contains(new Column("mytable", "first_name")));
// Assert.assertTrue(visitor.getColumns().contains(new Column("mytable", "full_name")));
String output = SQLUtils.toSQLString(stmt, JdbcConstants.DB2);
Assert.assertEquals(
"SELECT *" //
+ "\nFROM DSN81010.EMP", //
output);
}
public void test_0() throws Exception {
String sql = //
"alter table WRH$_SERVICE_WAIT_CLASS drop partition WRH$_SERVIC_1870432296_13478";
OracleStatementParser parser = new OracleStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement statemen = statementList.get(0);
print(statementList);
Assert.assertEquals(1, statementList.size());
OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
statemen.accept(visitor);
System.out.println("Tables : " + visitor.getTables());
System.out.println("fields : " + visitor.getColumns());
System.out.println("coditions : " + visitor.getConditions());
System.out.println("relationships : " + visitor.getRelationships());
System.out.println("orderBy : " + visitor.getOrderByColumns());
Assert.assertEquals(1, visitor.getTables().size());
Assert.assertTrue(
visitor.getTables().containsKey(new TableStat.Name("WRH$_SERVICE_WAIT_CLASS")));
Assert.assertEquals(0, visitor.getColumns().size());
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("pivot_table", "*")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("pivot_table",
// "YEAR")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("pivot_table",
// "order_mode")));
}
public void test_0() throws Exception {
String sql = "INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';";
PGSQLStatementParser parser = new PGSQLStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement statemen = statementList.get(0);
print(statementList);
Assert.assertEquals(1, statementList.size());
PGSchemaStatVisitor visitor = new PGSchemaStatVisitor();
statemen.accept(visitor);
System.out.println("Tables : " + visitor.getTables());
System.out.println("fields : " + visitor.getColumns());
System.out.println("coditions : " + visitor.getConditions());
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("films")));
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("tmp_films")));
Assert.assertEquals(2, visitor.getColumns().size());
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("tmp_films", "date_prod")));
Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("tmp_films", "*")));
}
public void test_0() throws Exception {
String sql = "DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a1.id=a2.id;";
MySqlStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement stmt = statementList.get(0);
Assert.assertEquals(
"DELETE FROM a1, a2 USING (t1) AS a1" + "\n\tINNER JOIN t2 a2" + "\nWHERE a1.id = a2.id",
SQLUtils.toMySqlString(stmt));
Assert.assertEquals(
"delete from a1, a2 using (t1) as a1" + "\n\tinner join t2 a2" + "\nwhere a1.id = a2.id",
SQLUtils.toMySqlString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));
Assert.assertEquals(1, statementList.size());
MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
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(3, visitor.getTables().size());
Assert.assertEquals(2, visitor.getColumns().size());
Assert.assertEquals(2, visitor.getConditions().size());
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("a1")));
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("a2")));
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t2")));
Assert.assertTrue(visitor.getColumns().contains(new Column("a1", "id")));
Assert.assertTrue(visitor.getColumns().contains(new Column("a2", "id")));
}
public void test_0() throws Exception {
String sql = "GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90";
MySqlStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement stmt = statementList.get(0);
// print(statementList);
Assert.assertEquals(1, statementList.size());
MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
stmt.accept(visitor);
String output = SQLUtils.toMySqlString(stmt);
Assert.assertEquals(
"GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90", //
output);
// 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(0, visitor.getColumns().size());
Assert.assertEquals(0, visitor.getConditions().size());
// Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("City")));
// Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t2")));
// Assert.assertTrue(visitor.getColumns().contains(new Column("t2", "id")));
}
public void test_0() throws Exception {
String sql =
"select id, name, beanId, \"algLable.id\", \"algLable.name\""
+ //
", \"algLable.schemaName\", \"algLable.tableName\", \"algLable.fieldName\""
+ //
" from cnres.function_select_algmodule_rule() "
+ //
" as a( id text,name text,beanId text, \"algLable.id\" text,\"algLable.name\" text"
+ //
", \"algLable.schemaName\" text,\"algLable.tableName\" text, \"algLable.fieldName\" text)";
PGSQLStatementParser parser = new PGSQLStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement statemen = statementList.get(0);
print(statementList);
Assert.assertEquals(1, statementList.size());
PGSchemaStatVisitor visitor = new PGSchemaStatVisitor();
statemen.accept(visitor);
System.out.println("Tables : " + visitor.getTables());
System.out.println("fields : " + visitor.getColumns());
System.out.println("coditions : " + visitor.getConditions());
Assert.assertEquals(8, visitor.getColumns().size());
Assert.assertEquals(0, visitor.getTables().size());
}
public void test_0() throws Exception {
String sql = "UPDATE `z_code` SET `key`='INTRANT_NOTALLOWED_CATEGORY_C'";
MySqlStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement statemen = statementList.get(0);
print(statementList);
Assert.assertEquals(1, statementList.size());
MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
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(1, visitor.getTables().size());
Assert.assertEquals(1, visitor.getColumns().size());
Assert.assertEquals(0, visitor.getConditions().size());
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("z_code")));
Assert.assertTrue(visitor.getColumns().contains(new Column("z_code", "key")));
}
public void test_0() throws Exception {
String sql =
"UPDATE ROLLBACK_ON_FAIL TARGET_AFFECT_ROW 1 "
+ "`table_3966` AS `table_3966_11` SET `version` = `version` + 3, `gmt_modified` = NOW(), `optype` = ?, `feature` = ? "
+ "WHERE `sub_biz_order_id` = ? AND `biz_order_type` = ? AND `id` = ? AND `ti_id` = ? AND `optype` = ? AND `root_id` = ?";
MySqlStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement stmt = statementList.get(0);
print(statementList);
Assert.assertEquals(1, statementList.size());
MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
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(6, visitor.getConditions().size());
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("table_3966")));
Assert.assertTrue(visitor.getColumns().contains(new Column("table_3966", "sub_biz_order_id")));
Assert.assertTrue(visitor.getColumns().contains(new Column("table_3966", "feature")));
Assert.assertTrue(visitor.getColumns().contains(new Column("table_3966", "id")));
{
String output = SQLUtils.toMySqlString(stmt);
Assert.assertEquals(
"UPDATE ROLLBACK_ON_FAIL TARGET_AFFECT_ROW 1 `table_3966` `table_3966_11`"
+ "\nSET `version` = `version` + 3, `gmt_modified` = NOW(), `optype` = ?, `feature` = ?"
+ "\nWHERE `sub_biz_order_id` = ?"
+ "\n\tAND `biz_order_type` = ?"
+ "\n\tAND `id` = ?"
+ "\n\tAND `ti_id` = ?"
+ "\n\tAND `optype` = ?"
+ "\n\tAND `root_id` = ?", //
output);
}
{
String output = SQLUtils.toMySqlString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION);
Assert.assertEquals(
"update rollback_on_fail target_affect_row 1 `table_3966` `table_3966_11`"
+ "\nset `version` = `version` + 3, `gmt_modified` = NOW(), `optype` = ?, `feature` = ?"
+ "\nwhere `sub_biz_order_id` = ?"
+ "\n\tand `biz_order_type` = ?"
+ "\n\tand `id` = ?"
+ "\n\tand `ti_id` = ?"
+ "\n\tand `optype` = ?"
+ "\n\tand `root_id` = ?", //
output);
}
}
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() + " ------");
}
protected String output(List<SQLStatement> stmtList) {
StringBuilder out = new StringBuilder();
HiveOutputVisitor visitor = new HiveOutputVisitor(out);
for (SQLStatement stmt : stmtList) {
stmt.accept(visitor);
}
return out.toString();
}
private String output(List<SQLStatement> stmtList) {
StringBuilder out = new StringBuilder();
for (SQLStatement stmt : stmtList) {
stmt.accept(new MySqlOutputVisitor(out));
out.append(";");
}
return out.toString();
}
public void test_0() throws Exception {
String sql =
"SELECT e.employee_id, e.salary, e.commission_pct"
+ //
" FROM employees e JOIN departments d"
+ //
" USING (department_id)"
+ //
" WHERE job_id = 'SA_REP'"
+ //
" AND location_id = 2500"
+ //
" ORDER BY e.employee_id"
+ //
" FOR UPDATE OF e.salary;";
OracleStatementParser parser = new OracleStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement statemen = statementList.get(0);
print(statementList);
Assert.assertEquals(1, statementList.size());
OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
statemen.accept(visitor);
System.out.println("Tables : " + visitor.getTables());
System.out.println("fields : " + visitor.getColumns());
System.out.println("coditions : " + visitor.getConditions());
System.out.println("relationships : " + visitor.getRelationships());
System.out.println("orderBy : " + visitor.getOrderByColumns());
Assert.assertEquals(2, visitor.getTables().size());
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("departments")));
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("employees")));
Assert.assertEquals(7, visitor.getColumns().size());
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("departments", "department_id")));
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("employees", "department_id")));
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("employees", "employee_id")));
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("employees", "commission_pct")));
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("employees", "department_id")));
Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("UNKNOWN", "job_id")));
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("UNKNOWN", "location_id")));
}