Examples with MySqlSelectQueryBlock com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock used on opensource projects

Example 51 with MySqlSelectQueryBlock

use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock in project Mycat-Server by MyCATApache.

the class DruidSelectParser method changeSql.

/**
 * 改写sql：需要加limit的加上
 */
@Override
public void changeSql(SchemaConfig schema, RouteResultset rrs, SQLStatement stmt, LayerCachePool cachePool) throws SQLNonTransientException {
    tryRoute(schema, rrs, cachePool);
    rrs.copyLimitToNodes();
    SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
    SQLSelectQuery sqlSelectQuery = selectStmt.getSelect().getQuery();
    if (sqlSelectQuery instanceof MySqlSelectQueryBlock) {
        MySqlSelectQueryBlock mysqlSelectQuery = (MySqlSelectQueryBlock) selectStmt.getSelect().getQuery();
        int limitStart = 0;
        int limitSize = schema.getDefaultMaxLimit();
        // clear group having
        SQLSelectGroupByClause groupByClause = mysqlSelectQuery.getGroupBy();
        // Modified by winbill, 20160614, do NOT include having clause when routing to multiple nodes
        if (groupByClause != null && groupByClause.getHaving() != null && isRoutMultiNode(schema, rrs)) {
            groupByClause.setHaving(null);
        }
        Map<String, Map<String, Set<ColumnRoutePair>>> allConditions = getAllConditions();
        boolean isNeedAddLimit = isNeedAddLimit(schema, rrs, mysqlSelectQuery, allConditions);
        if (isNeedAddLimit) {
            Limit limit = new Limit();
            limit.setRowCount(new SQLIntegerExpr(limitSize));
            mysqlSelectQuery.setLimit(limit);
            rrs.setLimitSize(limitSize);
            String sql = getSql(rrs, stmt, isNeedAddLimit);
            rrs.changeNodeSqlAfterAddLimit(schema, getCurentDbType(), sql, 0, limitSize, true);
        }
        Limit limit = mysqlSelectQuery.getLimit();
        if (limit != null && !isNeedAddLimit) {
            SQLIntegerExpr offset = (SQLIntegerExpr) limit.getOffset();
            SQLIntegerExpr count = (SQLIntegerExpr) limit.getRowCount();
            if (offset != null) {
                limitStart = offset.getNumber().intValue();
                rrs.setLimitStart(limitStart);
            }
            if (count != null) {
                limitSize = count.getNumber().intValue();
                rrs.setLimitSize(limitSize);
            }
            if (isNeedChangeLimit(rrs)) {
                Limit changedLimit = new Limit();
                changedLimit.setRowCount(new SQLIntegerExpr(limitStart + limitSize));
                if (offset != null) {
                    if (limitStart < 0) {
                        String msg = "You have an error in your SQL syntax; check the manual that " + "corresponds to your MySQL server version for the right syntax to use near '" + limitStart + "'";
                        throw new SQLNonTransientException(ErrorCode.ER_PARSE_ERROR + " - " + msg);
                    } else {
                        changedLimit.setOffset(new SQLIntegerExpr(0));
                    }
                }
                mysqlSelectQuery.setLimit(changedLimit);
                String sql = getSql(rrs, stmt, isNeedAddLimit);
                rrs.changeNodeSqlAfterAddLimit(schema, getCurentDbType(), sql, 0, limitStart + limitSize, true);
                // 设置改写后的sql
                ctx.setSql(sql);
            } else {
                rrs.changeNodeSqlAfterAddLimit(schema, getCurentDbType(), getCtx().getSql(), rrs.getLimitStart(), rrs.getLimitSize(), true);
            // ctx.setSql(nativeSql);
            }
        }
        if (rrs.isDistTable()) {
            SQLTableSource from = mysqlSelectQuery.getFrom();
            for (RouteResultsetNode node : rrs.getNodes()) {
                SQLIdentifierExpr sqlIdentifierExpr = new SQLIdentifierExpr();
                sqlIdentifierExpr.setParent(from);
                sqlIdentifierExpr.setName(node.getSubTableName());
                SQLExprTableSource from2 = new SQLExprTableSource(sqlIdentifierExpr);
                from2.setAlias(from.getAlias());
                mysqlSelectQuery.setFrom(from2);
                node.setStatement(stmt.toString());
            }
        }
        rrs.setCacheAble(isNeedCache(schema, rrs, mysqlSelectQuery, allConditions));
    }
}

Example 52 with MySqlSelectQueryBlock

use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock in project Mycat-Server by MyCATApache.

the class DruidSelectSqlServerParser method statementParse.

@Override
public void statementParse(SchemaConfig schema, RouteResultset rrs, SQLStatement stmt) {
    SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
    SQLSelectQuery sqlSelectQuery = selectStmt.getSelect().getQuery();
    // 从mysql解析过来
    if (sqlSelectQuery instanceof MySqlSelectQueryBlock) {
        MySqlSelectQueryBlock mysqlSelectQuery = (MySqlSelectQueryBlock) selectStmt.getSelect().getQuery();
        MySqlSelectQueryBlock.Limit limit = mysqlSelectQuery.getLimit();
        if (limit == null) {
            sqlserverParse(schema, rrs);
        }
        if (isNeedParseOrderAgg) {
            parseOrderAggGroupMysql(schema, stmt, rrs, mysqlSelectQuery);
            // 更改canRunInReadDB属性
            if ((mysqlSelectQuery.isForUpdate() || mysqlSelectQuery.isLockInShareMode()) && rrs.isAutocommit() == false) {
                rrs.setCanRunInReadDB(false);
            }
        }
    }
}

Example 53 with MySqlSelectQueryBlock

use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock in project Mycat-Server by MyCATApache.

the class DruidSelectOracleParser method statementParse.

@Override
public void statementParse(SchemaConfig schema, RouteResultset rrs, SQLStatement stmt) {
    SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
    SQLSelectQuery sqlSelectQuery = selectStmt.getSelect().getQuery();
    // 从mysql解析过来
    if (sqlSelectQuery instanceof MySqlSelectQueryBlock) {
        MySqlSelectQueryBlock mysqlSelectQuery = (MySqlSelectQueryBlock) selectStmt.getSelect().getQuery();
        Limit limit = mysqlSelectQuery.getLimit();
        if (limit == null) {
            // 使用oracle的解析，否则会有部分oracle语法识别错误
            OracleStatementParser oracleParser = new OracleStatementParser(getCtx().getSql());
            SQLSelectStatement oracleStmt = (SQLSelectStatement) oracleParser.parseStatement();
            selectStmt = oracleStmt;
            SQLSelectQuery oracleSqlSelectQuery = oracleStmt.getSelect().getQuery();
            if (oracleSqlSelectQuery instanceof OracleSelectQueryBlock) {
                parseNativePageSql(oracleStmt, rrs, (OracleSelectQueryBlock) oracleSqlSelectQuery, schema);
            }
        }
        if (isNeedParseOrderAgg) {
            parseOrderAggGroupMysql(schema, selectStmt, rrs, mysqlSelectQuery);
            // 更改canRunInReadDB属性
            if ((mysqlSelectQuery.isForUpdate() || mysqlSelectQuery.isLockInShareMode()) && rrs.isAutocommit() == false) {
                rrs.setCanRunInReadDB(false);
            }
        }
    }
}

Example 54 with MySqlSelectQueryBlock

use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock in project Mycat-Server by MyCATApache.

the class MycatSchemaStatVisitor method visit.

/* 
     *  遇到 any 将子查询改写成  SELECT MIN(name) FROM subtest1
     *  例如:
     *    select * from subtest where id oper any (select name from subtest1);
     *    >/>= any ----> >/>= min
     *    </<= any ----> </<= max
     *    <>   any ----> not in
     *    =    some ----> in
     *    other  不改写
     */
@Override
public boolean visit(SQLAnyExpr x) {
    setSubQueryRelationOrFlag(x);
    List<SQLSelectItem> itemlist = ((SQLSelectQueryBlock) (x.getSubQuery().getQuery())).getSelectList();
    SQLExpr sexpr = itemlist.get(0).getExpr();
    if (x.getParent() instanceof SQLBinaryOpExpr) {
        SQLBinaryOpExpr parentExpr = (SQLBinaryOpExpr) x.getParent();
        SQLAggregateExpr saexpr = null;
        switch(parentExpr.getOperator()) {
            case GreaterThan:
            case GreaterThanOrEqual:
            case NotLessThan:
                this.hasChange = true;
                if (sexpr instanceof SQLIdentifierExpr || (sexpr instanceof SQLPropertyExpr && ((SQLPropertyExpr) sexpr).getOwner() instanceof SQLIdentifierExpr)) {
                    saexpr = new SQLAggregateExpr("MIN");
                    saexpr.getArguments().add(sexpr);
                    saexpr.setParent(itemlist.get(0));
                    itemlist.get(0).setExpr(saexpr);
                }
                SQLQueryExpr maxSubQuery = new SQLQueryExpr(x.getSubQuery());
                x.getSubQuery().setParent(maxSubQuery);
                // 生成新的SQLQueryExpr 替换当前 SQLAllExpr 节点
                if (x.getParent() instanceof SQLBinaryOpExpr) {
                    if (((SQLBinaryOpExpr) x.getParent()).getLeft().equals(x)) {
                        ((SQLBinaryOpExpr) x.getParent()).setLeft(maxSubQuery);
                    } else if (((SQLBinaryOpExpr) x.getParent()).getRight().equals(x)) {
                        ((SQLBinaryOpExpr) x.getParent()).setRight(maxSubQuery);
                    }
                }
                addSubQuerys(x.getSubQuery());
                return super.visit(x.getSubQuery());
            case LessThan:
            case LessThanOrEqual:
            case NotGreaterThan:
                this.hasChange = true;
                if (sexpr instanceof SQLIdentifierExpr || (sexpr instanceof SQLPropertyExpr && ((SQLPropertyExpr) sexpr).getOwner() instanceof SQLIdentifierExpr)) {
                    saexpr = new SQLAggregateExpr("MAX");
                    saexpr.getArguments().add(sexpr);
                    saexpr.setParent(itemlist.get(0));
                    itemlist.get(0).setExpr(saexpr);
                }
                // 生成新的SQLQueryExpr 替换当前 SQLAllExpr 节点
                SQLQueryExpr minSubQuery = new SQLQueryExpr(x.getSubQuery());
                x.subQuery.setParent(minSubQuery);
                if (x.getParent() instanceof SQLBinaryOpExpr) {
                    if (((SQLBinaryOpExpr) x.getParent()).getLeft().equals(x)) {
                        ((SQLBinaryOpExpr) x.getParent()).setLeft(minSubQuery);
                    } else if (((SQLBinaryOpExpr) x.getParent()).getRight().equals(x)) {
                        ((SQLBinaryOpExpr) x.getParent()).setRight(minSubQuery);
                    }
                }
                addSubQuerys(x.getSubQuery());
                return super.visit(x.getSubQuery());
            case LessThanOrGreater:
            case NotEqual:
                this.hasChange = true;
                SQLInSubQueryExpr notInSubQueryExpr = new SQLInSubQueryExpr(x.getSubQuery());
                x.getSubQuery().setParent(notInSubQueryExpr);
                notInSubQueryExpr.setNot(true);
                // 生成新的SQLQueryExpr 替换当前 SQLAllExpr 节点
                if (x.getParent() instanceof SQLBinaryOpExpr) {
                    SQLBinaryOpExpr xp = (SQLBinaryOpExpr) x.getParent();
                    if (xp.getLeft().equals(x)) {
                        notInSubQueryExpr.setExpr(xp.getRight());
                    } else if (xp.getRight().equals(x)) {
                        notInSubQueryExpr.setExpr(xp.getLeft());
                    }
                    if (xp.getParent() instanceof MySqlSelectQueryBlock) {
                        ((MySqlSelectQueryBlock) xp.getParent()).setWhere(notInSubQueryExpr);
                    } else if (xp.getParent() instanceof SQLBinaryOpExpr) {
                        SQLBinaryOpExpr pp = ((SQLBinaryOpExpr) xp.getParent());
                        if (pp.getLeft().equals(xp)) {
                            pp.setLeft(notInSubQueryExpr);
                        } else if (pp.getRight().equals(xp)) {
                            pp.setRight(notInSubQueryExpr);
                        }
                    }
                }
                addSubQuerys(x.getSubQuery());
                return super.visit(notInSubQueryExpr);
            case Equality:
                this.hasChange = true;
                SQLInSubQueryExpr inSubQueryExpr = new SQLInSubQueryExpr(x.getSubQuery());
                x.getSubQuery().setParent(inSubQueryExpr);
                inSubQueryExpr.setNot(false);
                // 生成新的SQLQueryExpr 替换当前 SQLAllExpr 节点
                if (x.getParent() instanceof SQLBinaryOpExpr) {
                    SQLBinaryOpExpr xp = (SQLBinaryOpExpr) x.getParent();
                    if (xp.getLeft().equals(x)) {
                        inSubQueryExpr.setExpr(xp.getRight());
                    } else if (xp.getRight().equals(x)) {
                        inSubQueryExpr.setExpr(xp.getLeft());
                    }
                    if (xp.getParent() instanceof MySqlSelectQueryBlock) {
                        ((MySqlSelectQueryBlock) xp.getParent()).setWhere(inSubQueryExpr);
                    } else if (xp.getParent() instanceof SQLBinaryOpExpr) {
                        SQLBinaryOpExpr pp = ((SQLBinaryOpExpr) xp.getParent());
                        if (pp.getLeft().equals(xp)) {
                            pp.setLeft(inSubQueryExpr);
                        } else if (pp.getRight().equals(xp)) {
                            pp.setRight(inSubQueryExpr);
                        }
                    }
                }
                addSubQuerys(x.getSubQuery());
                return super.visit(inSubQueryExpr);
            default:
                break;
        }
    }
    addSubQuerys(x.getSubQuery());
    return super.visit(x);
}

Example 55 with MySqlSelectQueryBlock

use of com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock in project Mycat-Server by MyCATApache.

the class DefaultDruidParser method visitorParse.

/**
 * 子类可覆盖（如果该方法解析得不到表名、字段等信息的，就覆盖该方法，覆盖成空方法，然后通过statementPparse去解析）
 * 通过visitor解析：有些类型的Statement通过visitor解析得不到表名、
 * @param stmt
 */
@Override
public void visitorParse(RouteResultset rrs, SQLStatement stmt, MycatSchemaStatVisitor visitor) throws SQLNonTransientException {
    stmt.accept(visitor);
    ctx.setVisitor(visitor);
    if (stmt instanceof SQLSelectStatement) {
        SQLSelectQuery query = ((SQLSelectStatement) stmt).getSelect().getQuery();
        if (query instanceof MySqlSelectQueryBlock) {
            if (((MySqlSelectQueryBlock) query).isForUpdate()) {
                rrs.setSelectForUpdate(true);
            }
        }
    }
    List<List<Condition>> mergedConditionList = new ArrayList<List<Condition>>();
    if (visitor.hasOrCondition()) {
        // 包含or语句
        // TODO
        // 根据or拆分
        mergedConditionList = visitor.splitConditions();
    } else {
        // 不包含OR语句
        mergedConditionList.add(visitor.getConditions());
    }
    if (visitor.isHasChange()) {
        // 在解析的过程中子查询被改写了.需要更新ctx.
        ctx.setSql(stmt.toString());
        rrs.setStatement(ctx.getSql());
    }
    if (visitor.getAliasMap() != null) {
        for (Map.Entry<String, String> entry : visitor.getAliasMap().entrySet()) {
            String key = entry.getKey();
            String value = entry.getValue();
            if (key != null && key.indexOf("`") >= 0) {
                key = key.replaceAll("`", "");
            }
            if (value != null && value.indexOf("`") >= 0) {
                value = value.replaceAll("`", "");
            }
            // 表名前面带database的，去掉
            if (key != null) {
                int pos = key.indexOf(".");
                if (pos > 0) {
                    key = key.substring(pos + 1);
                }
                tableAliasMap.put(key.toUpperCase(), value);
            }
        // else {
        // tableAliasMap.put(key, value);
        // }
        }
        ctx.addTables(visitor.getTables());
        visitor.getAliasMap().putAll(tableAliasMap);
        ctx.setTableAliasMap(tableAliasMap);
    }
    ctx.setRouteCalculateUnits(this.buildRouteCalculateUnits(visitor, mergedConditionList));
}