Search in sources :

Example 1 with SQLServerSelect

use of com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelect in project Mycat-Server by MyCATApache.

the class DruidSelectSqlServerParser method parseOrderAggGroupSqlServer.

private void parseOrderAggGroupSqlServer(SchemaConfig schema, SQLStatement stmt, RouteResultset rrs, SQLServerSelectQueryBlock mysqlSelectQuery) {
    Map<String, String> aliaColumns = parseAggGroupCommon(schema, stmt, rrs, mysqlSelectQuery);
    SQLServerSelect oracleSelect = (SQLServerSelect) mysqlSelectQuery.getParent();
    if (oracleSelect.getOrderBy() != null) {
        List<SQLSelectOrderByItem> orderByItems = oracleSelect.getOrderBy().getItems();
        rrs.setOrderByCols(buildOrderByCols(orderByItems, aliaColumns));
    }
}
Also used : SQLServerSelect(com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelect) SQLSelectOrderByItem(com.alibaba.druid.sql.ast.statement.SQLSelectOrderByItem)

Example 2 with SQLServerSelect

use of com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelect 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;
                    }
                }
            }
        }
    }
}
Also used : SQLSubqueryTableSource(com.alibaba.druid.sql.ast.statement.SQLSubqueryTableSource) SQLOrderBy(com.alibaba.druid.sql.ast.SQLOrderBy) SQLBinaryOperator(com.alibaba.druid.sql.ast.expr.SQLBinaryOperator) SQLServerSelect(com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelect) SQLSelectQuery(com.alibaba.druid.sql.ast.statement.SQLSelectQuery) SQLExpr(com.alibaba.druid.sql.ast.SQLExpr) SQLTableSource(com.alibaba.druid.sql.ast.statement.SQLTableSource) SQLSelectItem(com.alibaba.druid.sql.ast.statement.SQLSelectItem) SQLServerTop(com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerTop) SQLServerSelectQueryBlock(com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelectQueryBlock) SQLIntegerExpr(com.alibaba.druid.sql.ast.expr.SQLIntegerExpr) SQLBinaryOpExpr(com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr) List(java.util.List) SQLAggregateExpr(com.alibaba.druid.sql.ast.expr.SQLAggregateExpr)

Example 3 with SQLServerSelect

use of com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelect in project druid by alibaba.

the class SQLServerSelectParser method select.

public SQLSelect select() {
    SQLServerSelect select = new SQLServerSelect();
    withSubquery(select);
    select.setQuery(query());
    select.setOrderBy(parseOrderBy());
    if (select.getOrderBy() == null) {
        select.setOrderBy(parseOrderBy());
    }
    if (lexer.token() == Token.FOR) {
        lexer.nextToken();
        if (identifierEquals("BROWSE")) {
            lexer.nextToken();
            select.setForBrowse(true);
        } else if (identifierEquals("XML")) {
            lexer.nextToken();
            for (; ; ) {
                if (//
                identifierEquals("AUTO") || //
                identifierEquals("TYPE") || //
                identifierEquals("XMLSCHEMA")) {
                    select.getForXmlOptions().add(lexer.stringVal());
                    lexer.nextToken();
                } else if (identifierEquals("ELEMENTS")) {
                    lexer.nextToken();
                    if (identifierEquals("XSINIL")) {
                        lexer.nextToken();
                        select.getForXmlOptions().add("ELEMENTS XSINIL");
                    } else {
                        select.getForXmlOptions().add("ELEMENTS");
                    }
                } else if (identifierEquals("PATH")) {
                    SQLExpr xmlPath = this.exprParser.expr();
                    select.setXmlPath(xmlPath);
                } else {
                    break;
                }
                if (lexer.token() == Token.COMMA) {
                    lexer.nextToken();
                    continue;
                } else {
                    break;
                }
            }
        } else {
            throw new ParserException("syntax error, not support option : " + lexer.token());
        }
    }
    if (identifierEquals("OFFSET")) {
        lexer.nextToken();
        SQLExpr offset = this.expr();
        acceptIdentifier("ROWS");
        select.setOffset(offset);
        if (lexer.token() == Token.FETCH) {
            lexer.nextToken();
            acceptIdentifier("NEXT");
            SQLExpr rowCount = expr();
            acceptIdentifier("ROWS");
            acceptIdentifier("ONLY");
            select.setRowCount(rowCount);
        }
    }
    return select;
}
Also used : ParserException(com.alibaba.druid.sql.parser.ParserException) SQLServerSelect(com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelect) SQLExpr(com.alibaba.druid.sql.ast.SQLExpr)

Aggregations

SQLServerSelect (com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelect)3 SQLExpr (com.alibaba.druid.sql.ast.SQLExpr)2 SQLOrderBy (com.alibaba.druid.sql.ast.SQLOrderBy)1 SQLAggregateExpr (com.alibaba.druid.sql.ast.expr.SQLAggregateExpr)1 SQLBinaryOpExpr (com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr)1 SQLBinaryOperator (com.alibaba.druid.sql.ast.expr.SQLBinaryOperator)1 SQLIntegerExpr (com.alibaba.druid.sql.ast.expr.SQLIntegerExpr)1 SQLSelectItem (com.alibaba.druid.sql.ast.statement.SQLSelectItem)1 SQLSelectOrderByItem (com.alibaba.druid.sql.ast.statement.SQLSelectOrderByItem)1 SQLSelectQuery (com.alibaba.druid.sql.ast.statement.SQLSelectQuery)1 SQLSubqueryTableSource (com.alibaba.druid.sql.ast.statement.SQLSubqueryTableSource)1 SQLTableSource (com.alibaba.druid.sql.ast.statement.SQLTableSource)1 SQLServerSelectQueryBlock (com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerSelectQueryBlock)1 SQLServerTop (com.alibaba.druid.sql.dialect.sqlserver.ast.SQLServerTop)1 ParserException (com.alibaba.druid.sql.parser.ParserException)1 List (java.util.List)1