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 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")));
}
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 =
"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);
}
}
@Test
public void test_one() throws Exception {
String sql =
"create table hp_db.g20_relationship_communication_daily( "
+ " a_iden_string varchar,"
+ " b_iden_string varchar,"
+ " counter bigint,"
+ " durationtime bigint"
+ ") "
+ "\nPARTITION BY HASH KEY(a_iden_string) PARTITION NUM 100"
+ "\nSUBPARTITION BY LIST(bdt bigint)"
+ "\nSUBPARTITION OPTIONS(available_Partition_Num=90)"
+ "\nTABLEGROUP g20_test_group;";
MySqlStatementParser parser = new MySqlStatementParser(sql);
SQLStatement stmt = parser.parseCreateTable();
MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
stmt.accept(visitor);
{
String output = SQLUtils.toMySqlString(stmt);
Assert.assertEquals(
"CREATE TABLE hp_db.g20_relationship_communication_daily ("
+ "\n\ta_iden_string varchar, "
+ "\n\tb_iden_string varchar, "
+ "\n\tcounter bigint, "
+ "\n\tdurationtime bigint"
+ "\n)"
+ "\nPARTITION BY HASH KEY(a_iden_string) PARTITION NUM 100"
+ "\nSUBPARTITION BY LIST (bdt bigint)"
+ "\nSUBPARTITION OPTIONS (available_Partition_Num = 90)"
+ "\nTABLEGROUP g20_test_group",
output);
}
{
String output = SQLUtils.toMySqlString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION);
Assert.assertEquals(
"create table hp_db.g20_relationship_communication_daily ("
+ "\n\ta_iden_string varchar, "
+ "\n\tb_iden_string varchar, "
+ "\n\tcounter bigint, "
+ "\n\tdurationtime bigint"
+ "\n)"
+ "\npartition by hash key(a_iden_string) partition num 100"
+ "\nsubpartition by list (bdt bigint)"
+ "\nsubpartition options (available_Partition_Num = 90)"
+ "\ntablegroup g20_test_group",
output);
}
}
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 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_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() {
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 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 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 = "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 testReplace_7() throws Exception {
String sql = "ReplaCe LOW_PRIORITY t1 (t1.col1) valueS (123),('12''34')";
MySqlStatementParser parser = new MySqlStatementParser(sql);
SQLStatement stmt = parser.parseStatementList().get(0);
parser.match(Token.EOF);
String output = SQLUtils.toMySqlString(stmt);
Assert.assertEquals("REPLACE LOW_PRIORITY INTO t1 (t1.col1)\nVALUES (123), ('12''34')", output);
}
public void testReplace_2() throws Exception {
String sql = "ReplaCe t1 value (123,?) ";
MySqlStatementParser parser = new MySqlStatementParser(sql);
SQLStatement stmt = parser.parseStatementList().get(0);
parser.match(Token.EOF);
String output = SQLUtils.toMySqlString(stmt);
Assert.assertEquals("REPLACE INTO t1\nVALUES (123, ?)", output);
}
public void testReplace_1() throws Exception {
String sql = "ReplaCe test.t1 seT t1.id1:=? ";
MySqlStatementParser parser = new MySqlStatementParser(sql);
SQLStatement stmt = parser.parseStatementList().get(0);
parser.match(Token.EOF);
String output = SQLUtils.toMySqlString(stmt);
Assert.assertEquals("REPLACE INTO test.t1 (t1.id1)\nVALUES (?)", output);
}
public void test_distribute_by() throws Exception {
String sql =
"select total_day_cnt * EXP(-datediff(to_date('20150819', 'yyyymmdd'), last_time, 'dd') / 60) from dual"; //
Assert.assertEquals(
"SELECT total_day_cnt * EXP(-datediff(TO_DATE('20150819', 'yyyymmdd'), last_time, 'dd') / 60)"
+ "\nFROM dual",
SQLUtils.formatOdps(sql));
}
public void testReplace_6() throws Exception {
String sql = "ReplaCe LOW_PRIORITY t1 (select id from t1) ";
MySqlStatementParser parser = new MySqlStatementParser(sql);
SQLStatement stmt = parser.parseStatementList().get(0);
parser.match(Token.EOF);
String output = SQLUtils.toMySqlString(stmt);
Assert.assertEquals("REPLACE LOW_PRIORITY INTO t1\n\tSELECT id\n\tFROM t1", output);
}
public void testReplace_0() throws Exception {
String sql = "ReplaCe LOW_PRIORITY intO test.t1 seT t1.id1:=?, id2='123'";
MySqlStatementParser parser = new MySqlStatementParser(sql);
SQLStatement stmt = parser.parseStatementList().get(0);
parser.match(Token.EOF);
String output = SQLUtils.toMySqlString(stmt);
Assert.assertEquals(
"REPLACE LOW_PRIORITY INTO test.t1 (t1.id1, id2)\nVALUES (?, '123')", output);
}
public String evaluate(String sql, String dbType) {
List<SQLStatement> statementList = SQLUtils.parseStatements(sql, dbType);
SchemaStatVisitor visitor = SQLUtils.createSchemaStatVisitor(dbType);
for (SQLStatement stmt : statementList) {
stmt.accept(visitor);
}
StringBuffer buf = new StringBuffer();
for (TableStat.Column column : visitor.getColumns()) {
if (buf.length() != 0) {
buf.append(',');
}
buf.append(column.toString());
}
return buf.toString();
}
public void test_alter_first() throws Exception {
String sql = "ALTER TABLE `rules` DROP `enabled`";
MySqlStatementParser parser = new MySqlStatementParser(sql);
SQLStatement stmt = parser.parseStatementList().get(0);
parser.match(Token.EOF);
String output = SQLUtils.toMySqlString(stmt);
Assert.assertEquals(
"ALTER TABLE `rules`" //
+ "\n\tDROP COLUMN `enabled`",
output);
}
public void test_alter_first() throws Exception {
String sql = "alter table tabelname alter column operateVersion set default 0";
MySqlStatementParser parser = new MySqlStatementParser(sql);
SQLStatement stmt = parser.parseStatementList().get(0);
parser.match(Token.EOF);
String output = SQLUtils.toMySqlString(stmt);
Assert.assertEquals(
"ALTER TABLE tabelname"
+ //
"\n\tALTER COLUMN operateVersion SET DEFAULT 0",
output);
}
public void test_0() throws Exception {
String text = "CREATE TABLE customer (a INT, b CHAR (20), INDEX (a));";
Assert.assertEquals(
"CREATE TABLE customer (\n"
+ //
"\ta INT, \n"
+ //
"\tb CHAR(20), \n"
+ //
"\tINDEX(a)\n"
+ //
");",
SQLUtils.format(text, JdbcUtils.MYSQL));
}
public void test_0() throws Exception {
String sql = //
"CREATE TRIGGER \"AO_4AEACD_WEBHOOK_D367380484\" " //
+ "BEFORE INSERT" //
+ " ON \"AO_4AEACD_WEBHOOK_DAO\" FOR EACH ROW " //
+ "BEGIN" //
+ " SELECT \"AO_4AEACD_WEBHOOK_DAO_ID_SEQ\".NEXTVAL INTO :NEW.\"ID\" FROM DUAL;" //
+ "END;";
OracleStatementParser parser = new OracleStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement stmt = statementList.get(0);
print(statementList);
Assert.assertEquals(1, statementList.size());
Assert.assertEquals(
"CREATE TRIGGER \"AO_4AEACD_WEBHOOK_D367380484\"" //
+ "\n\tBEFORE INSERT" //
+ "\n\tON \"AO_4AEACD_WEBHOOK_DAO\"" //
+ "\n\tFOR EACH ROW" //
+ "\nBEGIN" //
+ "\n\tSELECT \"AO_4AEACD_WEBHOOK_DAO_ID_SEQ\".NEXTVAL" //
+ "\n\tINTO :NEW.\"ID\"" //
+ "\n\tFROM DUAL;"
+ "\nEND", //
SQLUtils.toSQLString(stmt, JdbcConstants.ORACLE));
OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
stmt.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() 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 static Object eval(
String dbType, SQLObject sqlObject, List<Object> parameters, boolean throwError) {
SQLEvalVisitor visitor = createEvalVisitor(dbType);
visitor.setParameters(parameters);
sqlObject.accept(visitor);
Object value = getValue(sqlObject);
if (value == null) {
if (throwError && !sqlObject.getAttributes().containsKey(EVAL_VALUE)) {
throw new DruidRuntimeException("eval error : " + SQLUtils.toSQLString(sqlObject, dbType));
}
}
return value;
}
@Test
public void test_two() throws Exception {
String sql =
"CREATE TABLE `t_cpi_driskconfig_bak` ("
+ "`Sequence` bigint(20) NOT NULL AUTO_INCREMENT,"
+ "`comcode` binary(20) NOT NULL,"
+ "`riskcode` varchar(10) binary NOT NULL,"
+ "`configcodehead` varchar(30) binary NOT NULL,"
+ "`configcodebody` varchar(100) binary,"
+ "`configvalue` varchar(200) binary ,"
+ "`inputdate` datetime NOT NULL,"
+ "`validstatus` char(1) NOT NULL,"
+ "`remark` varchar(3000),"
+ "`flag` varchar(10) ,"
+ "PRIMARY KEY (`Sequence`)"
+ ") ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8;";
MySqlStatementParser parser = new MySqlStatementParser(sql);
SQLStatement stmt = parser.parseCreateTable();
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());
Assert.assertEquals(1, visitor.getTables().size());
Assert.assertEquals(10, visitor.getColumns().size());
Assert.assertEquals(0, visitor.getConditions().size());
String output = SQLUtils.toMySqlString(stmt);
Assert.assertEquals(
"CREATE TABLE `t_cpi_driskconfig_bak` ("
+ "\n\t`Sequence` bigint(20) NOT NULL AUTO_INCREMENT, "
+ "\n\t`comcode` binary(20) NOT NULL, "
+ "\n\t`riskcode` varchar(10) BINARY NOT NULL, "
+ "\n\t`configcodehead` varchar(30) BINARY NOT NULL, "
+ "\n\t`configcodebody` varchar(100) BINARY , "
+ "\n\t`configvalue` varchar(200) BINARY , "
+ "\n\t`inputdate` datetime NOT NULL, "
+ "\n\t`validstatus` char(1) NOT NULL, "
+ "\n\t`remark` varchar(3000), "
+ "\n\t`flag` varchar(10), "
+ "\n\tPRIMARY KEY (`Sequence`)"
+ "\n) ENGINE = InnoDB AUTO_INCREMENT = 49 CHARSET = utf8",
output);
}
public void test_0() throws Exception {
String sql =
"CREATE TABLE t1 ("
+ //
"c1 INT STORAGE DISK,"
+ //
"c2 INT STORAGE MEMORY "
+ //
") TABLESPACE ts_1 ENGINE NDB;";
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(2, visitor.getColumns().size());
Assert.assertEquals(0, visitor.getConditions().size());
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t1")));
String output = SQLUtils.toMySqlString(stmt);
Assert.assertEquals(
"CREATE TABLE t1 ("
+ //
"\n\tc1 INT STORAGE DISK, "
+ //
"\n\tc2 INT STORAGE MEMORY"
+ //
"\n) TABLESPACE ts_1 ENGINE = NDB",
output);
}
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);
}