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 = "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 = "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 = "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_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 = "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_0() throws Exception {
String sql = "SHOW FULL TABLES FROM sonar LIKE 'a'";
MySqlStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement stmt = statementList.get(0);
Assert.assertEquals(1, statementList.size());
MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
stmt.accept(visitor);
Assert.assertEquals(0, visitor.getTables().size());
Assert.assertEquals(0, visitor.getColumns().size());
Assert.assertEquals(0, visitor.getConditions().size());
Assert.assertEquals(0, visitor.getOrderByColumns().size());
// Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("mytable")));
}
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_0() throws Exception {
String sql =
"CREATE TABLE lookup"
+ //
" (id INT, INDEX USING BTREE (id))"
+ //
" AVG_ROW_LENGTH = 1024;"; //
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(1, visitor.getColumns().size());
Assert.assertEquals(0, visitor.getConditions().size());
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("lookup")));
String output = SQLUtils.toMySqlString(stmt);
Assert.assertEquals(
"CREATE TABLE lookup ("
+ //
"\n\tid INT, "
+ //
"\n\tINDEX USING BTREE(id)"
+ //
"\n) AVG_ROW_LENGTH = 1024",
output);
}
public void test_alter_first() throws Exception {
String sql = "alter table st_jklsxxb enable constraint FK_ST_xxx_REFERENCE_ST_xxx";
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 st_jklsxxb"
+ //
"\n\tENABLE CONSTRAINT FK_ST_xxx_REFERENCE_ST_xxx",
output);
Assert.assertEquals(1, visitor.getTables().size());
Assert.assertEquals(0, visitor.getColumns().size());
}
