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

Example 1 with SQLSubqueryTableSource

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

the class PagerUtils method limitDB2.

private static String limitDB2(SQLSelect select, String dbType, int offset, int count) {
    SQLSelectQuery query = select.getQuery();
    SQLBinaryOpExpr gt = new //
    SQLBinaryOpExpr(//
    new SQLIdentifierExpr("ROWNUM"), //
    SQLBinaryOperator.GreaterThan, //
    new SQLNumberExpr(offset), JdbcConstants.DB2);
    SQLBinaryOpExpr lteq = new //
    SQLBinaryOpExpr(//
    new SQLIdentifierExpr("ROWNUM"), //
    SQLBinaryOperator.LessThanOrEqual, //
    new SQLNumberExpr(count + offset), JdbcConstants.DB2);
    SQLBinaryOpExpr pageCondition = new SQLBinaryOpExpr(gt, SQLBinaryOperator.BooleanAnd, lteq, JdbcConstants.DB2);
    if (query instanceof SQLSelectQueryBlock) {
        DB2SelectQueryBlock queryBlock = (DB2SelectQueryBlock) query;
        if (offset <= 0) {
            queryBlock.setFirst(new SQLNumberExpr(count));
            return SQLUtils.toSQLString(select, dbType);
        }
        SQLAggregateExpr aggregateExpr = new SQLAggregateExpr("ROW_NUMBER");
        SQLOrderBy orderBy = select.getOrderBy();
        if (orderBy == null && select.getQuery() instanceof SQLSelectQueryBlock) {
            SQLSelectQueryBlock selectQueryBlcok = (SQLSelectQueryBlock) select.getQuery();
            orderBy = selectQueryBlcok.getOrderBy();
            selectQueryBlcok.setOrderBy(null);
        } else {
            select.setOrderBy(null);
        }
        aggregateExpr.setOver(new SQLOver(orderBy));
        queryBlock.getSelectList().add(new SQLSelectItem(aggregateExpr, "ROWNUM"));
        DB2SelectQueryBlock countQueryBlock = new DB2SelectQueryBlock();
        countQueryBlock.getSelectList().add(new SQLSelectItem(new SQLAllColumnExpr()));
        countQueryBlock.setFrom(new SQLSubqueryTableSource(select, "XX"));
        countQueryBlock.setWhere(pageCondition);
        return SQLUtils.toSQLString(countQueryBlock, dbType);
    }
    DB2SelectQueryBlock countQueryBlock = new DB2SelectQueryBlock();
    countQueryBlock.getSelectList().add(new SQLSelectItem(new SQLPropertyExpr(new SQLIdentifierExpr("XX"), "*")));
    SQLAggregateExpr aggregateExpr = new SQLAggregateExpr("ROW_NUMBER");
    SQLOrderBy orderBy = select.getOrderBy();
    aggregateExpr.setOver(new SQLOver(orderBy));
    select.setOrderBy(null);
    countQueryBlock.getSelectList().add(new SQLSelectItem(aggregateExpr, "ROWNUM"));
    countQueryBlock.setFrom(new SQLSubqueryTableSource(select, "XX"));
    if (offset <= 0) {
        return SQLUtils.toSQLString(countQueryBlock, dbType);
    }
    DB2SelectQueryBlock offsetQueryBlock = new DB2SelectQueryBlock();
    offsetQueryBlock.getSelectList().add(new SQLSelectItem(new SQLAllColumnExpr()));
    offsetQueryBlock.setFrom(new SQLSubqueryTableSource(new SQLSelect(countQueryBlock), "XXX"));
    offsetQueryBlock.setWhere(pageCondition);
    return SQLUtils.toSQLString(offsetQueryBlock, dbType);
}

Example 2 with SQLSubqueryTableSource

use of com.alibaba.druid.sql.ast.statement.SQLSubqueryTableSource in project Mycat_plus by coderczp.

the class DruidSelectOracleParser method parseThreeLevelPageSql.

private void parseThreeLevelPageSql(SQLStatement stmt, RouteResultset rrs, SchemaConfig schema, SQLSubqueryTableSource from, SQLBinaryOpExpr one, SQLBinaryOperator operator) {
    SQLIntegerExpr right = (SQLIntegerExpr) one.getRight();
    int firstrownum = right.getNumber().intValue();
    if (operator == SQLBinaryOperator.GreaterThanOrEqual && firstrownum != 0) {
        firstrownum = firstrownum - 1;
    }
    SQLSelectQuery subSelect = from.getSelect().getQuery();
    if (subSelect instanceof OracleSelectQueryBlock) {
        // 第二层子查询
        OracleSelectQueryBlock twoSubSelect = (OracleSelectQueryBlock) subSelect;
        if (twoSubSelect.getWhere() instanceof SQLBinaryOpExpr && twoSubSelect.getFrom() instanceof SQLSubqueryTableSource) {
            SQLBinaryOpExpr twoWhere = (SQLBinaryOpExpr) twoSubSelect.getWhere();
            boolean isRowNum = "rownum".equalsIgnoreCase(twoWhere.getLeft().toString());
            boolean isLess = twoWhere.getOperator() == SQLBinaryOperator.LessThanOrEqual || twoWhere.getOperator() == SQLBinaryOperator.LessThan;
            if (isRowNum && twoWhere.getRight() instanceof SQLIntegerExpr && isLess) {
                int lastrownum = ((SQLIntegerExpr) twoWhere.getRight()).getNumber().intValue();
                if (operator == SQLBinaryOperator.LessThan && lastrownum != 0) {
                    lastrownum = lastrownum - 1;
                }
                SQLSelectQuery finalQuery = ((SQLSubqueryTableSource) twoSubSelect.getFrom()).getSelect().getQuery();
                if (finalQuery instanceof OracleSelectQueryBlock) {
                    setLimitIFChange(stmt, rrs, schema, one, firstrownum, lastrownum);
                    parseOrderAggGroupOracle(stmt, rrs, (OracleSelectQueryBlock) finalQuery, schema);
                    isNeedParseOrderAgg = false;
                }
            }
        }
    }
}

Example 3 with SQLSubqueryTableSource

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

the class DruidSelectOracleParser method parseThreeLevelPageSql.

private void parseThreeLevelPageSql(SQLStatement stmt, RouteResultset rrs, SchemaConfig schema, SQLSubqueryTableSource from, SQLBinaryOpExpr one, SQLBinaryOperator operator) {
    SQLIntegerExpr right = (SQLIntegerExpr) one.getRight();
    int firstrownum = right.getNumber().intValue();
    if (operator == SQLBinaryOperator.GreaterThanOrEqual && firstrownum != 0) {
        firstrownum = firstrownum - 1;
    }
    SQLSelectQuery subSelect = from.getSelect().getQuery();
    if (subSelect instanceof OracleSelectQueryBlock) {
        // 第二层子查询
        OracleSelectQueryBlock twoSubSelect = (OracleSelectQueryBlock) subSelect;
        if (twoSubSelect.getWhere() instanceof SQLBinaryOpExpr && twoSubSelect.getFrom() instanceof SQLSubqueryTableSource) {
            SQLBinaryOpExpr twoWhere = (SQLBinaryOpExpr) twoSubSelect.getWhere();
            boolean isRowNum = "rownum".equalsIgnoreCase(twoWhere.getLeft().toString());
            boolean isLess = twoWhere.getOperator() == SQLBinaryOperator.LessThanOrEqual || twoWhere.getOperator() == SQLBinaryOperator.LessThan;
            if (isRowNum && twoWhere.getRight() instanceof SQLIntegerExpr && isLess) {
                int lastrownum = ((SQLIntegerExpr) twoWhere.getRight()).getNumber().intValue();
                if (operator == SQLBinaryOperator.LessThan && lastrownum != 0) {
                    lastrownum = lastrownum - 1;
                }
                SQLSelectQuery finalQuery = ((SQLSubqueryTableSource) twoSubSelect.getFrom()).getSelect().getQuery();
                if (finalQuery instanceof OracleSelectQueryBlock) {
                    setLimitIFChange(stmt, rrs, schema, one, firstrownum, lastrownum);
                    parseOrderAggGroupOracle(stmt, rrs, (OracleSelectQueryBlock) finalQuery, schema);
                    isNeedParseOrderAgg = false;
                }
            }
        }
    }
}

Example 4 with SQLSubqueryTableSource

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

the class SQLTransformUtils method transformOracleToPostgresql.

public static SQLTableSource transformOracleToPostgresql(SQLTableSource x) {
    if (x instanceof OracleSelectTableReference) {
        OracleSelectTableReference xx = (OracleSelectTableReference) x;
        SQLExprTableSource y = new SQLExprTableSource();
        xx.cloneTo(y);
        y.setParent(x.getParent());
        return y;
    }
    if (x instanceof OracleSelectJoin) {
        OracleSelectJoin xx = (OracleSelectJoin) x;
        SQLJoinTableSource y = new SQLJoinTableSource();
        xx.cloneTo(y);
        y.setLeft(transformOracleToPostgresql(y.getLeft()));
        y.setRight(transformOracleToPostgresql(y.getRight()));
        y.setParent(x.getParent());
        return y;
    }
    if (x instanceof OracleSelectSubqueryTableSource) {
        OracleSelectSubqueryTableSource xx = (OracleSelectSubqueryTableSource) x;
        SQLSubqueryTableSource y = new SQLSubqueryTableSource();
        xx.cloneTo(y);
        y.setParent(x.getParent());
        return y;
    }
    if (x instanceof OracleWithSubqueryEntry) {
        SQLWithSubqueryClause.Entry entry = new SQLWithSubqueryClause.Entry();
        ((OracleWithSubqueryEntry) x).cloneTo(entry);
        entry.setParent(x.getParent());
        return entry;
    }
    return x;
}

Example 5 with SQLSubqueryTableSource

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

the class NameResolveVisitor method isAliasColumn.

/**
 * 是否是 select item 字段的别名
 *
 * @param x      x 是否是 select item 字段的别名
 * @param source 从 source 数据中查找 and 判断
 * @return true：是、false：不是
 */
public boolean isAliasColumn(SQLExpr x, SQLSelectQueryBlock source) {
    if (x instanceof SQLIdentifierExpr) {
        SQLIdentifierExpr identifierExpr = (SQLIdentifierExpr) x;
        long nameHashCode64 = identifierExpr.nameHashCode64();
        SQLSelectQueryBlock queryBlock = source;
        SQLSelectItem selectItem = queryBlock.findSelectItem(nameHashCode64);
        if (selectItem != null) {
            return true;
        }
        if (queryBlock.getFrom() instanceof SQLSubqueryTableSource && ((SQLSubqueryTableSource) queryBlock.getFrom()).getSelect().getQuery() instanceof SQLSelectQueryBlock) {
            SQLSelectQueryBlock subQueryBlock = ((SQLSubqueryTableSource) queryBlock.getFrom()).getSelect().getQueryBlock();
            if (isAliasColumn(x, subQueryBlock)) {
                return true;
            }
        }
    }
    return false;
}