Examples with SQLTableSource com.alibaba.druid.sql.ast.statement.SQLTableSource used on opensource projects

Example 6 with SQLTableSource

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

the class DruidSelectOracleParser method parseNativePageSql.

protected void parseNativePageSql(SQLStatement stmt, RouteResultset rrs, OracleSelectQueryBlock mysqlSelectQuery, SchemaConfig schema) {
    //第一层子查询
    SQLExpr where = mysqlSelectQuery.getWhere();
    SQLTableSource from = mysqlSelectQuery.getFrom();
    if (where instanceof SQLBinaryOpExpr && from instanceof SQLSubqueryTableSource) {
        SQLBinaryOpExpr one = (SQLBinaryOpExpr) where;
        SQLExpr left = one.getLeft();
        SQLBinaryOperator operator = one.getOperator();
        //解析只有一层rownum限制大小
        if (one.getRight() instanceof SQLIntegerExpr && "rownum".equalsIgnoreCase(left.toString()) && (operator == SQLBinaryOperator.LessThanOrEqual || operator == SQLBinaryOperator.LessThan)) {
            SQLIntegerExpr right = (SQLIntegerExpr) one.getRight();
            int firstrownum = right.getNumber().intValue();
            if (operator == SQLBinaryOperator.LessThan && firstrownum != 0) {
                firstrownum = firstrownum - 1;
            }
            SQLSelectQuery subSelect = ((SQLSubqueryTableSource) from).getSelect().getQuery();
            if (subSelect instanceof OracleSelectQueryBlock) {
                rrs.setLimitStart(0);
                rrs.setLimitSize(firstrownum);
                //为了继续解出order by 等
                mysqlSelectQuery = (OracleSelectQueryBlock) subSelect;
                parseOrderAggGroupOracle(stmt, rrs, mysqlSelectQuery, schema);
                isNeedParseOrderAgg = false;
            }
        } else //解析oracle三层嵌套分页
        if (one.getRight() instanceof SQLIntegerExpr && !"rownum".equalsIgnoreCase(left.toString()) && (operator == SQLBinaryOperator.GreaterThan || operator == SQLBinaryOperator.GreaterThanOrEqual)) {
            parseThreeLevelPageSql(stmt, rrs, schema, (SQLSubqueryTableSource) from, one, operator);
        } else //解析oracle rownumber over分页
        {
            SQLSelectQuery subSelect = ((SQLSubqueryTableSource) from).getSelect().getQuery();
            SQLOrderBy orderBy = null;
            if (subSelect instanceof OracleSelectQueryBlock) {
                boolean hasRowNumber = false;
                OracleSelectQueryBlock subSelectOracle = (OracleSelectQueryBlock) subSelect;
                List<SQLSelectItem> sqlSelectItems = subSelectOracle.getSelectList();
                for (SQLSelectItem sqlSelectItem : sqlSelectItems) {
                    SQLExpr sqlExpr = sqlSelectItem.getExpr();
                    if (sqlExpr instanceof SQLAggregateExpr) {
                        SQLAggregateExpr agg = (SQLAggregateExpr) sqlExpr;
                        if ("row_number".equalsIgnoreCase(agg.getMethodName()) && agg.getOver() != null) {
                            hasRowNumber = true;
                            orderBy = agg.getOver().getOrderBy();
                        }
                    }
                }
                if (hasRowNumber) {
                    if ((operator == SQLBinaryOperator.LessThan || operator == SQLBinaryOperator.LessThanOrEqual) && one.getRight() instanceof SQLIntegerExpr) {
                        SQLIntegerExpr right = (SQLIntegerExpr) one.getRight();
                        int firstrownum = right.getNumber().intValue();
                        if (operator == SQLBinaryOperator.LessThan && firstrownum != 0) {
                            firstrownum = firstrownum - 1;
                        }
                        if (subSelect instanceof OracleSelectQueryBlock) {
                            rrs.setLimitStart(0);
                            rrs.setLimitSize(firstrownum);
                            mysqlSelectQuery = (OracleSelectQueryBlock) subSelect;
                            if (orderBy != null) {
                                OracleSelect oracleSelect = (OracleSelect) subSelect.getParent();
                                oracleSelect.setOrderBy(orderBy);
                            }
                            parseOrderAggGroupOracle(stmt, rrs, mysqlSelectQuery, schema);
                            isNeedParseOrderAgg = false;
                        }
                    } else if (operator == SQLBinaryOperator.BooleanAnd && left instanceof SQLBinaryOpExpr && one.getRight() instanceof SQLBinaryOpExpr) {
                        SQLBinaryOpExpr leftE = (SQLBinaryOpExpr) left;
                        SQLBinaryOpExpr rightE = (SQLBinaryOpExpr) one.getRight();
                        SQLBinaryOpExpr small = null;
                        SQLBinaryOpExpr larger = null;
                        int firstrownum = 0;
                        int lastrownum = 0;
                        if (leftE.getRight() instanceof SQLIntegerExpr && (leftE.getOperator() == SQLBinaryOperator.GreaterThan || leftE.getOperator() == SQLBinaryOperator.GreaterThanOrEqual)) {
                            small = leftE;
                            firstrownum = ((SQLIntegerExpr) leftE.getRight()).getNumber().intValue();
                            if (leftE.getOperator() == SQLBinaryOperator.GreaterThanOrEqual && firstrownum != 0) {
                                firstrownum = firstrownum - 1;
                            }
                        } else if (leftE.getRight() instanceof SQLIntegerExpr && (leftE.getOperator() == SQLBinaryOperator.LessThan || leftE.getOperator() == SQLBinaryOperator.LessThanOrEqual)) {
                            larger = leftE;
                            lastrownum = ((SQLIntegerExpr) leftE.getRight()).getNumber().intValue();
                            if (leftE.getOperator() == SQLBinaryOperator.LessThan && lastrownum != 0) {
                                lastrownum = lastrownum - 1;
                            }
                        }
                        if (rightE.getRight() instanceof SQLIntegerExpr && (rightE.getOperator() == SQLBinaryOperator.GreaterThan || rightE.getOperator() == SQLBinaryOperator.GreaterThanOrEqual)) {
                            small = rightE;
                            firstrownum = ((SQLIntegerExpr) rightE.getRight()).getNumber().intValue();
                            if (rightE.getOperator() == SQLBinaryOperator.GreaterThanOrEqual && firstrownum != 0) {
                                firstrownum = firstrownum - 1;
                            }
                        } else if (rightE.getRight() instanceof SQLIntegerExpr && (rightE.getOperator() == SQLBinaryOperator.LessThan || rightE.getOperator() == SQLBinaryOperator.LessThanOrEqual)) {
                            larger = rightE;
                            lastrownum = ((SQLIntegerExpr) rightE.getRight()).getNumber().intValue();
                            if (rightE.getOperator() == SQLBinaryOperator.LessThan && lastrownum != 0) {
                                lastrownum = lastrownum - 1;
                            }
                        }
                        if (small != null && larger != null) {
                            setLimitIFChange(stmt, rrs, schema, small, firstrownum, lastrownum);
                            if (orderBy != null) {
                                OracleSelect oracleSelect = (OracleSelect) subSelect.getParent();
                                oracleSelect.setOrderBy(orderBy);
                            }
                            parseOrderAggGroupOracle(stmt, rrs, (OracleSelectQueryBlock) subSelect, schema);
                            isNeedParseOrderAgg = false;
                        }
                    }
                } else {
                    parseNativeSql(stmt, rrs, mysqlSelectQuery, schema);
                }
            }
        }
    } else {
        parseNativeSql(stmt, rrs, mysqlSelectQuery, schema);
    }
    if (isNeedParseOrderAgg) {
        parseOrderAggGroupOracle(stmt, rrs, mysqlSelectQuery, schema);
    }
}

Example 7 with SQLTableSource

use of com.alibaba.druid.sql.ast.statement.SQLTableSource 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);
                mysqlSelectQuery.setFrom(from2);
                node.setStatement(stmt.toString());
            }
        }
        rrs.setCacheAble(isNeedCache(schema, rrs, mysqlSelectQuery, allConditions));
    }
}

Example 8 with SQLTableSource

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

the class DruidSelectSqlServerParser method parseSqlServerPageSql.

private void parseSqlServerPageSql(SQLStatement stmt, RouteResultset rrs, SQLServerSelectQueryBlock sqlserverSelectQuery, SchemaConfig schema) {
    //第一层子查询
    SQLExpr where = sqlserverSelectQuery.getWhere();
    SQLTableSource from = sqlserverSelectQuery.getFrom();
    if (sqlserverSelectQuery.getTop() != null) {
        SQLServerTop top = sqlserverSelectQuery.getTop();
        SQLExpr sqlExpr = top.getExpr();
        if (sqlExpr instanceof SQLIntegerExpr) {
            int topValue = ((SQLIntegerExpr) sqlExpr).getNumber().intValue();
            rrs.setLimitStart(0);
            rrs.setLimitSize(topValue);
        }
    } else if (where instanceof SQLBinaryOpExpr && from instanceof SQLSubqueryTableSource) {
        SQLBinaryOpExpr one = (SQLBinaryOpExpr) where;
        SQLExpr left = one.getLeft();
        SQLBinaryOperator operator = one.getOperator();
        SQLSelectQuery subSelect = ((SQLSubqueryTableSource) from).getSelect().getQuery();
        SQLOrderBy orderBy = null;
        if (subSelect instanceof SQLServerSelectQueryBlock) {
            boolean hasRowNumber = false;
            boolean hasSubTop = false;
            int subTop = 0;
            SQLServerSelectQueryBlock subSelectOracle = (SQLServerSelectQueryBlock) subSelect;
            List<SQLSelectItem> sqlSelectItems = subSelectOracle.getSelectList();
            for (SQLSelectItem sqlSelectItem : sqlSelectItems) {
                SQLExpr sqlExpr = sqlSelectItem.getExpr();
                if (sqlExpr instanceof SQLAggregateExpr) {
                    SQLAggregateExpr agg = (SQLAggregateExpr) sqlExpr;
                    if ("row_number".equalsIgnoreCase(agg.getMethodName()) && agg.getOver() != null) {
                        hasRowNumber = true;
                        orderBy = agg.getOver().getOrderBy();
                    }
                }
            }
            if (subSelectOracle.getFrom() instanceof SQLSubqueryTableSource) {
                SQLSubqueryTableSource subFrom = (SQLSubqueryTableSource) subSelectOracle.getFrom();
                if (subFrom.getSelect().getQuery() instanceof SQLServerSelectQueryBlock) {
                    SQLServerSelectQueryBlock sqlSelectQuery = (SQLServerSelectQueryBlock) subFrom.getSelect().getQuery();
                    if (sqlSelectQuery.getTop() != null) {
                        SQLExpr sqlExpr = sqlSelectQuery.getTop().getExpr();
                        if (sqlExpr instanceof SQLIntegerExpr) {
                            hasSubTop = true;
                            subTop = ((SQLIntegerExpr) sqlExpr).getNumber().intValue();
                            orderBy = subFrom.getSelect().getOrderBy();
                        }
                    }
                }
            }
            if (hasRowNumber) {
                if (hasSubTop && (operator == SQLBinaryOperator.GreaterThan || operator == SQLBinaryOperator.GreaterThanOrEqual) && one.getRight() instanceof SQLIntegerExpr) {
                    SQLIntegerExpr right = (SQLIntegerExpr) one.getRight();
                    int firstrownum = right.getNumber().intValue();
                    if (operator == SQLBinaryOperator.GreaterThanOrEqual && firstrownum != 0) {
                        firstrownum = firstrownum - 1;
                    }
                    int lastrownum = subTop;
                    setLimitIFChange(stmt, rrs, schema, one, firstrownum, lastrownum);
                    if (orderBy != null) {
                        SQLServerSelect oracleSelect = (SQLServerSelect) subSelect.getParent();
                        oracleSelect.setOrderBy(orderBy);
                    }
                    parseOrderAggGroupSqlServer(schema, stmt, rrs, (SQLServerSelectQueryBlock) subSelect);
                    isNeedParseOrderAgg = false;
                } else if ((operator == SQLBinaryOperator.LessThan || operator == SQLBinaryOperator.LessThanOrEqual) && one.getRight() instanceof SQLIntegerExpr) {
                    SQLIntegerExpr right = (SQLIntegerExpr) one.getRight();
                    int firstrownum = right.getNumber().intValue();
                    if (operator == SQLBinaryOperator.LessThan && firstrownum != 0) {
                        firstrownum = firstrownum - 1;
                    }
                    if (subSelect instanceof SQLServerSelectQueryBlock) {
                        rrs.setLimitStart(0);
                        rrs.setLimitSize(firstrownum);
                        //为了继续解出order by 等
                        sqlserverSelectQuery = (SQLServerSelectQueryBlock) subSelect;
                        if (orderBy != null) {
                            SQLServerSelect oracleSelect = (SQLServerSelect) subSelect.getParent();
                            oracleSelect.setOrderBy(orderBy);
                        }
                        parseOrderAggGroupSqlServer(schema, stmt, rrs, sqlserverSelectQuery);
                        isNeedParseOrderAgg = false;
                    }
                } else if (operator == SQLBinaryOperator.BooleanAnd && left instanceof SQLBinaryOpExpr && one.getRight() instanceof SQLBinaryOpExpr) {
                    SQLBinaryOpExpr leftE = (SQLBinaryOpExpr) left;
                    SQLBinaryOpExpr rightE = (SQLBinaryOpExpr) one.getRight();
                    SQLBinaryOpExpr small = null;
                    SQLBinaryOpExpr larger = null;
                    int firstrownum = 0;
                    int lastrownum = 0;
                    if (leftE.getRight() instanceof SQLIntegerExpr && (leftE.getOperator() == SQLBinaryOperator.GreaterThan || leftE.getOperator() == SQLBinaryOperator.GreaterThanOrEqual)) {
                        small = leftE;
                        firstrownum = ((SQLIntegerExpr) leftE.getRight()).getNumber().intValue();
                        if (leftE.getOperator() == SQLBinaryOperator.GreaterThanOrEqual && firstrownum != 0) {
                            firstrownum = firstrownum - 1;
                        }
                    } else if (leftE.getRight() instanceof SQLIntegerExpr && (leftE.getOperator() == SQLBinaryOperator.LessThan || leftE.getOperator() == SQLBinaryOperator.LessThanOrEqual)) {
                        larger = leftE;
                        lastrownum = ((SQLIntegerExpr) leftE.getRight()).getNumber().intValue();
                        if (leftE.getOperator() == SQLBinaryOperator.LessThan && lastrownum != 0) {
                            lastrownum = lastrownum - 1;
                        }
                    }
                    if (rightE.getRight() instanceof SQLIntegerExpr && (rightE.getOperator() == SQLBinaryOperator.GreaterThan || rightE.getOperator() == SQLBinaryOperator.GreaterThanOrEqual)) {
                        small = rightE;
                        firstrownum = ((SQLIntegerExpr) rightE.getRight()).getNumber().intValue();
                        if (rightE.getOperator() == SQLBinaryOperator.GreaterThanOrEqual && firstrownum != 0) {
                            firstrownum = firstrownum - 1;
                        }
                    } else if (rightE.getRight() instanceof SQLIntegerExpr && (rightE.getOperator() == SQLBinaryOperator.LessThan || rightE.getOperator() == SQLBinaryOperator.LessThanOrEqual)) {
                        larger = rightE;
                        lastrownum = ((SQLIntegerExpr) rightE.getRight()).getNumber().intValue();
                        if (rightE.getOperator() == SQLBinaryOperator.LessThan && lastrownum != 0) {
                            lastrownum = lastrownum - 1;
                        }
                    }
                    if (small != null && larger != null) {
                        setLimitIFChange(stmt, rrs, schema, small, firstrownum, lastrownum);
                        if (orderBy != null) {
                            SQLServerSelect oracleSelect = (SQLServerSelect) subSelect.getParent();
                            oracleSelect.setOrderBy(orderBy);
                        }
                        parseOrderAggGroupSqlServer(schema, stmt, rrs, (SQLServerSelectQueryBlock) subSelect);
                        isNeedParseOrderAgg = false;
                    }
                }
            }
        }
    }
}

Example 9 with SQLTableSource

use of com.alibaba.druid.sql.ast.statement.SQLTableSource in project druid by alibaba.

the class OracleUpdateParser method parseUpdateStatement.

public OracleUpdateStatement parseUpdateStatement() {
    OracleUpdateStatement update = new OracleUpdateStatement();
    if (lexer.token() == Token.UPDATE) {
        lexer.nextToken();
        parseHints(update);
        if (identifierEquals("ONLY")) {
            update.setOnly(true);
        }
        SQLTableSource tableSource = this.exprParser.createSelectParser().parseTableSource();
        update.setTableSource(tableSource);
        if ((update.getAlias() == null) || (update.getAlias().length() == 0)) {
            update.setAlias(as());
        }
    }
    parseUpdateSet(update);
    parseWhere(update);
    parseReturn(update);
    parseErrorLoging(update);
    return update;
}

Example 10 with SQLTableSource

use of com.alibaba.druid.sql.ast.statement.SQLTableSource in project druid by alibaba.

the class PGSQLStatementParser method parseUpdateStatement.

public SQLUpdateStatement parseUpdateStatement() {
    accept(Token.UPDATE);
    PGUpdateStatement udpateStatement = new PGUpdateStatement();
    SQLSelectParser selectParser = this.exprParser.createSelectParser();
    SQLTableSource tableSource = selectParser.parseTableSource();
    udpateStatement.setTableSource(tableSource);
    parseUpdateSet(udpateStatement);
    if (lexer.token() == Token.FROM) {
        lexer.nextToken();
        SQLTableSource from = selectParser.parseTableSource();
        udpateStatement.setFrom(from);
    }
    if (lexer.token() == (Token.WHERE)) {
        lexer.nextToken();
        udpateStatement.setWhere(this.exprParser.expr());
    }
    if (lexer.token() == Token.RETURNING) {
        lexer.nextToken();
        for (; ; ) {
            udpateStatement.getReturning().add(this.exprParser.expr());
            if (lexer.token() == Token.COMMA) {
                lexer.nextToken();
                continue;
            }
            break;
        }
    }
    return udpateStatement;
}