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_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);
}
private static boolean isJoin(SQLQueryExpr sqlExpr, String sql) {
MySqlSelectQueryBlock query = (MySqlSelectQueryBlock) sqlExpr.getSubQuery().getQuery();
return query.getFrom() instanceof SQLJoinTableSource && sql.toLowerCase().contains("join");
}
@Override
public SQLSelectQuery query() {
if (lexer.token() == (Token.LPAREN)) {
lexer.nextToken();
SQLSelectQuery select = query();
accept(Token.RPAREN);
return queryRest(select);
}
MySqlSelectQueryBlock queryBlock = new MySqlSelectQueryBlock();
if (lexer.token() == Token.SELECT) {
lexer.nextToken();
if (lexer.token() == Token.HINT) {
this.exprParser.parseHints(queryBlock.getHints());
}
if (lexer.token() == Token.COMMENT) {
lexer.nextToken();
}
if (lexer.token() == (Token.DISTINCT)) {
queryBlock.setDistionOption(SQLSetQuantifier.DISTINCT);
lexer.nextToken();
} else if (identifierEquals("DISTINCTROW")) {
queryBlock.setDistionOption(SQLSetQuantifier.DISTINCTROW);
lexer.nextToken();
} else if (lexer.token() == (Token.ALL)) {
queryBlock.setDistionOption(SQLSetQuantifier.ALL);
lexer.nextToken();
}
if (identifierEquals("HIGH_PRIORITY")) {
queryBlock.setHignPriority(true);
lexer.nextToken();
}
if (identifierEquals("STRAIGHT_JOIN")) {
queryBlock.setStraightJoin(true);
lexer.nextToken();
}
if (identifierEquals("SQL_SMALL_RESULT")) {
queryBlock.setSmallResult(true);
lexer.nextToken();
}
if (identifierEquals("SQL_BIG_RESULT")) {
queryBlock.setBigResult(true);
lexer.nextToken();
}
if (identifierEquals("SQL_BUFFER_RESULT")) {
queryBlock.setBufferResult(true);
lexer.nextToken();
}
if (identifierEquals("SQL_CACHE")) {
queryBlock.setCache(true);
lexer.nextToken();
}
if (identifierEquals("SQL_NO_CACHE")) {
queryBlock.setCache(false);
lexer.nextToken();
}
if (identifierEquals("SQL_CALC_FOUND_ROWS")) {
queryBlock.setCalcFoundRows(true);
lexer.nextToken();
}
parseSelectList(queryBlock);
argsList = parseIntoArgs();
}
parseFrom(queryBlock);
parseWhere(queryBlock);
parseGroupBy(queryBlock);
queryBlock.setOrderBy(this.exprParser.parseOrderBy());
if (lexer.token() == Token.LIMIT) {
queryBlock.setLimit(parseLimit());
}
if (lexer.token() == Token.PROCEDURE) {
lexer.nextToken();
throw new ParserException("TODO");
}
parseInto(queryBlock);
if (lexer.token() == Token.FOR) {
lexer.nextToken();
accept(Token.UPDATE);
queryBlock.setForUpdate(true);
}
if (lexer.token() == Token.LOCK) {
lexer.nextToken();
accept(Token.IN);
acceptIdentifier("SHARE");
acceptIdentifier("MODE");
queryBlock.setLockInShareMode(true);
}
return queryRest(queryBlock);
}
public boolean visit(MySqlSelectQueryBlock select) {
print("SELECT ");
if (SQLSetQuantifier.ALL == select.getDistionOption()) print("ALL ");
else if (SQLSetQuantifier.DISTINCT == select.getDistionOption()) print("DISTINCT ");
else if (SQLSetQuantifier.DISTINCTROW == select.getDistionOption()) {
print("DISTINCTROW ");
}
if (select.isHignPriority()) {
print("HIGH_PRIORITY ");
}
if (select.isSmallResult()) {
print("SQL_SMALL_RESULT ");
}
if (select.isBigResult()) {
print("SQL_BIG_RESULT ");
}
if (select.isBufferResult()) {
print("SQL_BUFFER_RESULT ");
}
if (select.getCache() != null) {
if (select.getCache().booleanValue()) {
print("SQL_CACHE ");
} else {
print("SQL_NO_CACHE ");
}
}
if (select.isCalcFoundRows()) {
print("SQL_CALC_FOUND_ROWS ");
}
printSelectList(select.getSelectList());
if (select.getOutFile() != null) {
println();
print("INTO OUTFILE ");
select.getOutFile().accept(this);
if (select.getOutFileCharset() != null) {
print(" CHARACTER SET ");
print(select.getOutFileCharset());
}
if (select.getOutFileColumnsTerminatedBy() != null
|| select.getOutFileColumnsEnclosedBy() != null
|| select.getOutFileColumnsEscaped() != null) {
print(" COLUMNS");
if (select.getOutFileColumnsTerminatedBy() != null) {
print(" TERMINATED BY ");
select.getOutFileColumnsTerminatedBy().accept(this);
}
if (select.getOutFileColumnsEnclosedBy() != null) {
if (select.isOutFileColumnsEnclosedOptionally()) {
print(" OPTIONALLY");
}
print(" ENCLOSED BY ");
select.getOutFileColumnsEnclosedBy().accept(this);
}
if (select.getOutFileColumnsEscaped() != null) {
print(" ESCAPED BY ");
select.getOutFileColumnsEscaped().accept(this);
}
}
if (select.getOutFileLinesStartingBy() != null
|| select.getOutFileLinesTerminatedBy() != null) {
print(" LINES");
if (select.getOutFileLinesStartingBy() != null) {
print(" STARTING BY ");
select.getOutFileLinesStartingBy().accept(this);
}
if (select.getOutFileLinesTerminatedBy() != null) {
print(" TERMINATED BY ");
select.getOutFileLinesTerminatedBy().accept(this);
}
}
}
if (select.getFrom() != null) {
println();
print("FROM ");
select.getFrom().accept(this);
}
if (select.getWhere() != null) {
println();
print("WHERE ");
select.getWhere().accept(this);
}
if (select.getGroupBy() != null) {
println();
select.getGroupBy().accept(this);
}
if (select.getOrderBy() != null) {
println();
select.getOrderBy().accept(this);
}
if (select.getLimit() != null) {
println();
select.getLimit().accept(this);
}
if (select.getProcedureName() != null) {
print(" PROCEDURE ");
select.getProcedureName().accept(this);
if (select.getProcedureArgumentList().size() > 0) {
print("(");
printAndAccept(select.getProcedureArgumentList(), ", ");
print(")");
}
}
if (select.isForUpdate()) {
println();
print("FOR UPDATE");
}
if (select.isLockInShareMode()) {
println();
print("LOCK IN SHARE MODE");
}
return false;
}