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; }