Examples with SQLASTOutputVisitor com.alibaba.druid.sql.visitor.SQLASTOutputVisitor used on opensource projects

Example 1 with SQLASTOutputVisitor

use of com.alibaba.druid.sql.visitor.SQLASTOutputVisitor in project druid by alibaba.

the class SQLUtils method toSQLString.

public static String toSQLString(SQLObject sqlObject, String dbType, FormatOption option) {
    StringBuilder out = new StringBuilder();
    SQLASTOutputVisitor visitor = createOutputVisitor(out, dbType);
    if (option == null) {
        option = DEFAULT_FORMAT_OPTION;
    }
    visitor.setUppCase(option.isUppCase());
    visitor.setPrettyFormat(option.isPrettyFormat());
    visitor.setParameterized(option.isParameterized());
    sqlObject.accept(visitor);
    String sql = out.toString();
    return sql;
}

Example 2 with SQLASTOutputVisitor

use of com.alibaba.druid.sql.visitor.SQLASTOutputVisitor in project Mycat-Server by MyCATApache.

the class DruidSelectParser method parseAggGroupCommon.

protected Map<String, String> parseAggGroupCommon(SchemaConfig schema, SQLStatement stmt, RouteResultset rrs, SQLSelectQueryBlock mysqlSelectQuery) {
    Map<String, String> aliaColumns = new HashMap<String, String>();
    Map<String, Integer> aggrColumns = new HashMap<String, Integer>();
    // Added by winbill, 20160314, for having clause, Begin ==>
    List<String> havingColsName = new ArrayList<String>();
    // Added by winbill, 20160314, for having clause, End  <==
    List<SQLSelectItem> selectList = mysqlSelectQuery.getSelectList();
    boolean isNeedChangeSql = false;
    int size = selectList.size();
    boolean isDistinct = mysqlSelectQuery.getDistionOption() == 2;
    for (int i = 0; i < size; i++) {
        SQLSelectItem item = selectList.get(i);
        if (item.getExpr() instanceof SQLAggregateExpr) {
            SQLAggregateExpr expr = (SQLAggregateExpr) item.getExpr();
            String method = expr.getMethodName();
            boolean isHasArgument = !expr.getArguments().isEmpty();
            if (isHasArgument) {
                // Added by winbill, 20160314, for having clause
                String aggrColName = method + "(" + expr.getArguments().get(0) + ")";
                // Added by winbill, 20160314, for having clause
                havingColsName.add(aggrColName);
            }
            // 只处理有别名的情况，无别名添加别名，否则某些数据库会得不到正确结果处理
            int mergeType = MergeCol.getMergeType(method);
            if (MergeCol.MERGE_AVG == mergeType && isRoutMultiNode(schema, rrs)) {
                // 跨分片avg需要特殊处理，直接avg结果是不对的
                String colName = item.getAlias() != null ? item.getAlias() : method + i;
                SQLSelectItem sum = new SQLSelectItem();
                String sumColName = colName + "SUM";
                sum.setAlias(sumColName);
                SQLAggregateExpr sumExp = new SQLAggregateExpr("SUM");
                ObjectUtil.copyProperties(expr, sumExp);
                sumExp.getArguments().addAll(expr.getArguments());
                sumExp.setMethodName("SUM");
                sum.setExpr(sumExp);
                selectList.set(i, sum);
                aggrColumns.put(sumColName, MergeCol.MERGE_SUM);
                // Added by winbill, 20160314, for having clause
                havingColsName.add(sumColName);
                // Added by winbill, 20160314, two aliases for AVG
                havingColsName.add(item.getAlias() != null ? item.getAlias() : "");
                SQLSelectItem count = new SQLSelectItem();
                String countColName = colName + "COUNT";
                count.setAlias(countColName);
                SQLAggregateExpr countExp = new SQLAggregateExpr("COUNT");
                ObjectUtil.copyProperties(expr, countExp);
                countExp.getArguments().addAll(expr.getArguments());
                countExp.setMethodName("COUNT");
                count.setExpr(countExp);
                selectList.add(count);
                aggrColumns.put(countColName, MergeCol.MERGE_COUNT);
                isNeedChangeSql = true;
                aggrColumns.put(colName, mergeType);
                rrs.setHasAggrColumn(true);
            } else if (MergeCol.MERGE_UNSUPPORT != mergeType) {
                String aggColName = null;
                StringBuilder sb = new StringBuilder();
                if (mysqlSelectQuery instanceof MySqlSelectQueryBlock) {
                    expr.accept(new MySqlOutputVisitor(sb));
                } else if (mysqlSelectQuery instanceof OracleSelectQueryBlock) {
                    expr.accept(new OracleOutputVisitor(sb));
                } else if (mysqlSelectQuery instanceof PGSelectQueryBlock) {
                    expr.accept(new PGOutputVisitor(sb));
                } else if (mysqlSelectQuery instanceof SQLServerSelectQueryBlock) {
                    expr.accept(new SQLASTOutputVisitor(sb));
                } else if (mysqlSelectQuery instanceof DB2SelectQueryBlock) {
                    expr.accept(new DB2OutputVisitor(sb));
                }
                aggColName = sb.toString();
                if (item.getAlias() != null && item.getAlias().length() > 0) {
                    aggrColumns.put(item.getAlias(), mergeType);
                    aliaColumns.put(aggColName, item.getAlias());
                } else {
                    // 如果不加，jdbc方式时取不到正确结果   ;修改添加别名
                    item.setAlias(method + i);
                    aggrColumns.put(method + i, mergeType);
                    aliaColumns.put(aggColName, method + i);
                    isNeedChangeSql = true;
                }
                rrs.setHasAggrColumn(true);
                // Added by winbill, 20160314, for having clause
                havingColsName.add(item.getAlias());
                // Added by winbill, 20160314, one alias for non-AVG
                havingColsName.add("");
            }
        } else {
            if (!(item.getExpr() instanceof SQLAllColumnExpr)) {
                String alia = item.getAlias();
                String field = getFieldName(item);
                if (alia == null) {
                    alia = field;
                }
                aliaColumns.put(field, alia);
            }
        }
    }
    if (aggrColumns.size() > 0) {
        rrs.setMergeCols(aggrColumns);
    }
    // 通过优化转换成group by来实现
    if (isDistinct) {
        mysqlSelectQuery.setDistionOption(0);
        SQLSelectGroupByClause groupBy = new SQLSelectGroupByClause();
        for (String fieldName : aliaColumns.keySet()) {
            groupBy.addItem(new SQLIdentifierExpr(fieldName));
        }
        mysqlSelectQuery.setGroupBy(groupBy);
        isNeedChangeSql = true;
    }
    // setGroupByCols
    if (mysqlSelectQuery.getGroupBy() != null) {
        List<SQLExpr> groupByItems = mysqlSelectQuery.getGroupBy().getItems();
        String[] groupByCols = buildGroupByCols(groupByItems, aliaColumns);
        rrs.setGroupByCols(groupByCols);
        rrs.setHavings(buildGroupByHaving(mysqlSelectQuery.getGroupBy().getHaving(), aliaColumns));
        rrs.setHasAggrColumn(true);
        // Added by winbill, 20160314, for having clause
        rrs.setHavingColsName(havingColsName.toArray());
    }
    if (isNeedChangeSql) {
        String sql = stmt.toString();
        rrs.changeNodeSqlAfterAddLimit(schema, getCurentDbType(), sql, 0, -1, false);
        getCtx().setSql(sql);
    }
    return aliaColumns;
}

Example 3 with SQLASTOutputVisitor

use of com.alibaba.druid.sql.visitor.SQLASTOutputVisitor in project druid by alibaba.

the class MySqlStatementParser method parseInsert.

public SQLInsertStatement parseInsert() {
    MySqlInsertStatement stmt = new MySqlInsertStatement();
    SQLName tableName = null;
    if (lexer.token() == Token.INSERT) {
        lexer.nextToken();
        for (; ; ) {
            if (lexer.token() == Token.IDENTIFIER) {
                long hash = lexer.hash_lower();
                if (hash == FnvHash.Constants.LOW_PRIORITY) {
                    stmt.setLowPriority(true);
                    lexer.nextToken();
                    continue;
                }
                if (hash == FnvHash.Constants.DELAYED) {
                    stmt.setDelayed(true);
                    lexer.nextToken();
                    continue;
                }
                if (hash == FnvHash.Constants.HIGH_PRIORITY) {
                    stmt.setHighPriority(true);
                    lexer.nextToken();
                    continue;
                }
                if (hash == FnvHash.Constants.IGNORE) {
                    stmt.setIgnore(true);
                    lexer.nextToken();
                    continue;
                }
                if (hash == FnvHash.Constants.ROLLBACK_ON_FAIL) {
                    stmt.setRollbackOnFail(true);
                    lexer.nextToken();
                    continue;
                }
            }
            break;
        }
        if (lexer.token() == Token.HINT) {
            List<SQLCommentHint> hints = this.exprParser.parseHints();
            stmt.setHints(hints);
        }
        if (lexer.token() == Token.INTO) {
            lexer.nextToken();
            if (lexer.token() == Token.TABLE) {
                lexer.nextToken();
            }
        } else if (lexer.identifierEquals(FnvHash.Constants.OVERWRITE)) {
            lexer.nextToken();
            stmt.setOverwrite(true);
            if (lexer.token() == Token.TABLE) {
                lexer.nextToken();
            } else if (lexer.token() == Token.INTO) {
                lexer.nextToken();
            }
        }
        if (lexer.token() == Token.LINE_COMMENT) {
            lexer.nextToken();
        }
        if (lexer.token() == Token.FULLTEXT) {
            lexer.nextToken();
            if (lexer.identifierEquals(FnvHash.Constants.DICTIONARY)) {
                lexer.nextToken();
                stmt.setFulltextDictionary(true);
            }
        }
        tableName = this.exprParser.name();
        stmt.setTableName(tableName);
        if (lexer.token() == Token.HINT) {
            String comment = "/*" + lexer.stringVal() + "*/";
            lexer.nextToken();
            stmt.getTableSource().addAfterComment(comment);
        }
        if (lexer.token() == Token.IDENTIFIER && !lexer.identifierEquals(FnvHash.Constants.VALUE)) {
            stmt.setAlias(lexer.stringVal());
            lexer.nextToken();
        }
        if (lexer.token() == Token.WITH) {
            SQLSelectStatement withStmt = (SQLSelectStatement) parseWith();
            stmt.setQuery(withStmt.getSelect());
        }
        if (lexer.token() == Token.PARTITION) {
            lexer.nextToken();
            accept(Token.LPAREN);
            for (; ; ) {
                SQLAssignItem ptExpr = new SQLAssignItem();
                ptExpr.setTarget(this.exprParser.name());
                if (lexer.token() == Token.EQ) {
                    lexer.nextToken();
                    SQLExpr ptValue = this.exprParser.expr();
                    ptExpr.setValue(ptValue);
                }
                stmt.addPartition(ptExpr);
                if (lexer.token() != Token.COMMA) {
                    break;
                } else {
                    lexer.nextToken();
                }
            }
            accept(Token.RPAREN);
            if (lexer.token() == Token.IF) {
                lexer.nextToken();
                accept(Token.NOT);
                accept(Token.EXISTS);
                stmt.setIfNotExists(true);
            }
        }
    }
    int columnSize = 0;
    List<SQLColumnDefinition> columnDefinitionList = null;
    if (lexer.token() == Token.LPAREN) {
        boolean useInsertColumnsCache = lexer.isEnabled(SQLParserFeature.UseInsertColumnsCache);
        InsertColumnsCache insertColumnsCache = null;
        long tableNameHash = 0;
        InsertColumnsCache.Entry cachedColumns = null;
        if (useInsertColumnsCache) {
            insertColumnsCache = this.insertColumnsCache;
            if (insertColumnsCache == null) {
                insertColumnsCache = InsertColumnsCache.global;
            }
            if (tableName != null) {
                tableNameHash = tableName.nameHashCode64();
                cachedColumns = insertColumnsCache.get(tableNameHash);
            }
        }
        SchemaObject tableObject = null;
        int pos = lexer.pos();
        if (cachedColumns != null && lexer.text.startsWith(cachedColumns.columnsString, pos)) {
            if (!lexer.isEnabled(SQLParserFeature.OptimizedForParameterized)) {
                List<SQLExpr> columns = stmt.getColumns();
                List<SQLExpr> cachedColumns2 = cachedColumns.columns;
                for (int i = 0, size = cachedColumns2.size(); i < size; i++) {
                    columns.add(cachedColumns2.get(i).clone());
                }
            }
            stmt.setColumnsString(cachedColumns.columnsFormattedString, cachedColumns.columnsFormattedStringHash);
            int p2 = pos + cachedColumns.columnsString.length();
            lexer.reset(p2);
            lexer.nextToken();
        } else {
            Lexer.SavePoint mark = lexer.mark();
            lexer.nextToken();
            if (lexer.token() == Token.SELECT) {
                lexer.reset(mark);
                SQLSelect select = this.exprParser.createSelectParser().select();
                select.setParent(stmt);
                stmt.setQuery(select);
            } else {
                if (repository != null && lexer.isEnabled(SQLParserFeature.InsertValueCheckType)) {
                    tableObject = repository.findTable(tableName.nameHashCode64());
                }
                if (tableObject != null) {
                    columnDefinitionList = new ArrayList<SQLColumnDefinition>();
                }
                List<SQLExpr> columns = stmt.getColumns();
                if (lexer.token() != Token.RPAREN) {
                    for (; ; ) {
                        String identName;
                        long hash;
                        Token token = lexer.token();
                        if (token == Token.IDENTIFIER) {
                            identName = lexer.stringVal();
                            hash = lexer.hash_lower();
                        } else if (token == Token.LITERAL_CHARS) {
                            if (lexer.isEnabled(SQLParserFeature.IgnoreNameQuotes)) {
                                identName = lexer.stringVal();
                            } else {
                                identName = '\'' + lexer.stringVal() + '\'';
                            }
                            hash = 0;
                        } else if (token == Token.LITERAL_ALIAS) {
                            identName = lexer.stringVal();
                            if (lexer.isEnabled(SQLParserFeature.IgnoreNameQuotes)) {
                                identName = SQLUtils.normalize(identName, dbType);
                            }
                            hash = 0;
                        } else {
                            identName = lexer.stringVal();
                            hash = 0;
                        }
                        lexer.nextTokenComma();
                        SQLExpr expr = new SQLIdentifierExpr(identName, hash);
                        while (lexer.token() == Token.DOT) {
                            lexer.nextToken();
                            String propertyName = lexer.stringVal();
                            lexer.nextToken();
                            expr = new SQLPropertyExpr(expr, propertyName);
                        }
                        expr.setParent(stmt);
                        columns.add(expr);
                        columnSize++;
                        if (tableObject != null) {
                            SQLColumnDefinition columnDefinition = tableObject.findColumn(hash);
                            columnDefinitionList.add(columnDefinition);
                        }
                        if (lexer.token() == Token.COMMA) {
                            lexer.nextTokenIdent();
                            continue;
                        }
                        break;
                    }
                    columnSize = stmt.getColumns().size();
                    if (insertColumnsCache != null && tableName != null) {
                        String columnsString = lexer.subString(pos, lexer.pos() - pos);
                        List<SQLExpr> clonedColumns = new ArrayList<SQLExpr>(columnSize);
                        for (int i = 0; i < columns.size(); i++) {
                            clonedColumns.add(columns.get(i).clone());
                        }
                        StringBuilder buf = new StringBuilder();
                        SQLASTOutputVisitor outputVisitor = SQLUtils.createOutputVisitor(buf, dbType);
                        outputVisitor.printInsertColumns(columns);
                        String formattedColumnsString = buf.toString();
                        long columnsFormattedStringHash = FnvHash.fnv1a_64_lower(formattedColumnsString);
                        insertColumnsCache.put(tableName.hashCode64(), columnsString, formattedColumnsString, clonedColumns);
                        stmt.setColumnsString(formattedColumnsString, columnsFormattedStringHash);
                    }
                }
                accept(Token.RPAREN);
            }
        }
    }
    List<SQLCommentHint> commentHints = null;
    if (lexer.token() == Token.HINT) {
        commentHints = this.exprParser.parseHints();
    } else if (lexer.token() == Token.LINE_COMMENT) {
        lexer.nextToken();
    }
    if (lexer.token() == Token.VALUES || lexer.identifierEquals(FnvHash.Constants.VALUE)) {
        lexer.nextTokenLParen();
        if (lexer.isEnabled(SQLParserFeature.InsertReader)) {
            return stmt;
        }
        if (lexer.isEnabled(SQLParserFeature.InsertValueNative)) {
            parseValueClauseNative(stmt.getValuesList(), columnDefinitionList, columnSize, stmt);
        } else {
            parseValueClause(stmt.getValuesList(), columnDefinitionList, columnSize, stmt);
        }
    } else if (lexer.token() == Token.SET) {
        lexer.nextToken();
        SQLInsertStatement.ValuesClause values = new SQLInsertStatement.ValuesClause();
        stmt.addValueCause(values);
        for (; ; ) {
            SQLName name = this.exprParser.name();
            stmt.addColumn(name);
            if (lexer.token() == Token.EQ) {
                lexer.nextToken();
            } else {
                accept(Token.COLONEQ);
            }
            values.addValue(this.exprParser.expr());
            if (lexer.token() == Token.COMMA) {
                lexer.nextToken();
                continue;
            }
            break;
        }
    } else if (lexer.token() == (Token.SELECT)) {
        SQLSelect select = this.exprParser.createSelectParser().select();
        if (commentHints != null && !commentHints.isEmpty()) {
            select.setHeadHint(commentHints.get(0));
        }
        select.setParent(stmt);
        stmt.setQuery(select);
    } else if (lexer.token() == (Token.LPAREN)) {
        lexer.nextToken();
        SQLSelect select = this.exprParser.createSelectParser().select();
        select.setParent(stmt);
        stmt.setQuery(select);
        accept(Token.RPAREN);
    } else if (lexer.token() == WITH) {
        SQLSelect query = this.exprParser.createSelectParser().select();
        stmt.setQuery(query);
    }
    if (lexer.token() == Token.ON) {
        lexer.nextToken();
        acceptIdentifier("DUPLICATE");
        accept(Token.KEY);
        accept(Token.UPDATE);
        List<SQLExpr> duplicateKeyUpdate = stmt.getDuplicateKeyUpdate();
        for (; ; ) {
            SQLName name = this.exprParser.name();
            accept(Token.EQ);
            SQLExpr value;
            try {
                value = this.exprParser.expr();
            } catch (EOFParserException e) {
                throw new ParserException("EOF, " + name + "=", e);
            }
            SQLBinaryOpExpr assignment = new SQLBinaryOpExpr(name, SQLBinaryOperator.Equality, value);
            assignment.setParent(stmt);
            duplicateKeyUpdate.add(assignment);
            if (lexer.token() == Token.COMMA) {
                lexer.nextTokenIdent();
                continue;
            }
            break;
        }
    }
    return stmt;
}

Example 4 with SQLASTOutputVisitor

use of com.alibaba.druid.sql.visitor.SQLASTOutputVisitor in project druid by alibaba.

the class ExportParameterShardingTest method test_exportParameter.

public void test_exportParameter() throws Exception {
    String sql = "select * from t_user_0000 where oid = 1001";
    List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, dbType);
    assertEquals(1, stmtList.size());
    SQLStatement stmt = stmtList.get(0);
    StringBuilder out = new StringBuilder();
    SQLASTOutputVisitor visitor = SQLUtils.createOutputVisitor(out, dbType);
    visitor.setParameterized(true);
    visitor.setParameterizedMergeInList(true);
    List<Object> parameters = visitor.getParameters();
    // visitor.setParameters(parameters);
    stmt.accept(visitor);
    System.out.println(out);
    System.out.println(JSON.toJSONString(parameters));
    String restoredSql = restore(out.toString(), parameters);
    assertEquals("SELECT *\n" + "FROM t_user_0000\n" + "WHERE oid = 1001", restoredSql);
}

Example 5 with SQLASTOutputVisitor

use of com.alibaba.druid.sql.visitor.SQLASTOutputVisitor in project druid by alibaba.

the class ExportParameterInTest method test_exportParameter.

public void test_exportParameter() throws Exception {
    String sql = "select * from t_user where oid = '102' and uid in (1, 2, 3)";
    List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, dbType);
    assertEquals(1, stmtList.size());
    SQLStatement stmt = stmtList.get(0);
    StringBuilder out = new StringBuilder();
    List<Object> parameters = new ArrayList<Object>();
    SQLASTOutputVisitor visitor = SQLUtils.createOutputVisitor(out, dbType);
    visitor.setParameterized(true);
    visitor.setParameterizedMergeInList(true);
    visitor.setParameters(parameters);
    stmt.accept(visitor);
    System.out.println(out);
    System.out.println(JSON.toJSONString(parameters));
    restore(out.toString(), parameters);
}