Examples with PGSelectQueryBlock com.alibaba.druid.sql.dialect.postgresql.ast.stmt.PGSelectQueryBlock used on opensource projects

Example 1 with PGSelectQueryBlock

use of com.alibaba.druid.sql.dialect.postgresql.ast.stmt.PGSelectQueryBlock in project Mycat-Server by MyCATApache.

the class PageSQLUtil method convertLimitToNativePageSql.

public static String convertLimitToNativePageSql(String dbType, String sql, int offset, int count) {
    if (JdbcConstants.ORACLE.equalsIgnoreCase(dbType)) {
        OracleStatementParser oracleParser = new OracleStatementParser(sql);
        SQLSelectStatement oracleStmt = (SQLSelectStatement) oracleParser.parseStatement();
        return PagerUtils.limit(oracleStmt.getSelect(), JdbcConstants.ORACLE, offset, count);
    } else if (JdbcConstants.SQL_SERVER.equalsIgnoreCase(dbType)) {
        SQLServerStatementParser oracleParser = new SQLServerStatementParser(sql);
        SQLSelectStatement sqlserverStmt = (SQLSelectStatement) oracleParser.parseStatement();
        SQLSelect select = sqlserverStmt.getSelect();
        SQLOrderBy orderBy = select.getOrderBy();
        if (orderBy == null) {
            SQLSelectQuery sqlSelectQuery = select.getQuery();
            if (sqlSelectQuery instanceof SQLServerSelectQueryBlock) {
                SQLServerSelectQueryBlock sqlServerSelectQueryBlock = (SQLServerSelectQueryBlock) sqlSelectQuery;
                SQLTableSource from = sqlServerSelectQueryBlock.getFrom();
                if ("limit".equalsIgnoreCase(from.getAlias())) {
                    from.setAlias(null);
                }
            }
            SQLOrderBy newOrderBy = new SQLOrderBy(new SQLIdentifierExpr("(select 0)"));
            select.setOrderBy(newOrderBy);
        }
        return PagerUtils.limit(select, JdbcConstants.SQL_SERVER, offset, count);
    } else if (JdbcConstants.DB2.equalsIgnoreCase(dbType)) {
        DB2StatementParser db2Parser = new DB2StatementParser(sql);
        SQLSelectStatement db2Stmt = (SQLSelectStatement) db2Parser.parseStatement();
        return limitDB2(db2Stmt.getSelect(), JdbcConstants.DB2, offset, count);
    } else if (JdbcConstants.POSTGRESQL.equalsIgnoreCase(dbType)) {
        PGSQLStatementParser pgParser = new PGSQLStatementParser(sql);
        SQLSelectStatement pgStmt = (SQLSelectStatement) pgParser.parseStatement();
        SQLSelect select = pgStmt.getSelect();
        SQLSelectQuery query = select.getQuery();
        if (query instanceof PGSelectQueryBlock) {
            PGSelectQueryBlock pgSelectQueryBlock = (PGSelectQueryBlock) query;
            pgSelectQueryBlock.setOffset(null);
            pgSelectQueryBlock.setLimit(null);
        }
        return PagerUtils.limit(select, JdbcConstants.POSTGRESQL, offset, count);
    } else if (JdbcConstants.MYSQL.equalsIgnoreCase(dbType)) {
        MySqlStatementParser pgParser = new MySqlStatementParser(sql);
        SQLSelectStatement pgStmt = (SQLSelectStatement) pgParser.parseStatement();
        SQLSelect select = pgStmt.getSelect();
        SQLSelectQuery query = select.getQuery();
        if (query instanceof MySqlSelectQueryBlock) {
            MySqlSelectQueryBlock pgSelectQueryBlock = (MySqlSelectQueryBlock) query;
            pgSelectQueryBlock.setLimit(null);
        }
        return PagerUtils.limit(select, JdbcConstants.MYSQL, offset, count);
    }
    return sql;
}

Example 2 with PGSelectQueryBlock

use of com.alibaba.druid.sql.dialect.postgresql.ast.stmt.PGSelectQueryBlock in project druid by alibaba.

the class PagerUtils method clearOrderBy.

private static void clearOrderBy(SQLSelectQuery query) {
    if (query instanceof SQLSelectQueryBlock) {
        SQLSelectQueryBlock queryBlock = (SQLSelectQueryBlock) query;
        if (queryBlock instanceof MySqlSelectQueryBlock) {
            MySqlSelectQueryBlock mysqlQueryBlock = (MySqlSelectQueryBlock) queryBlock;
            if (mysqlQueryBlock.getOrderBy() != null) {
                mysqlQueryBlock.setOrderBy(null);
            }
        } else if (queryBlock instanceof PGSelectQueryBlock) {
            PGSelectQueryBlock pgQueryBlock = (PGSelectQueryBlock) queryBlock;
            if (pgQueryBlock.getOrderBy() != null) {
                pgQueryBlock.setOrderBy(null);
            }
        }
        return;
    }
    if (query instanceof SQLUnionQuery) {
        SQLUnionQuery union = (SQLUnionQuery) query;
        if (union.getOrderBy() != null) {
            union.setOrderBy(null);
        }
        clearOrderBy(union.getLeft());
        clearOrderBy(union.getRight());
    }
}

Example 3 with PGSelectQueryBlock

use of com.alibaba.druid.sql.dialect.postgresql.ast.stmt.PGSelectQueryBlock in project druid by alibaba.

the class SQLSelectBuilderImpl method limit.

@Override
public SQLSelectBuilderImpl limit(int rowCount, int offset) {
    SQLSelectQueryBlock queryBlock = getQueryBlock();
    if (queryBlock instanceof MySqlSelectQueryBlock) {
        MySqlSelectQueryBlock mySqlQueryBlock = (MySqlSelectQueryBlock) queryBlock;
        SQLLimit limit = new SQLLimit();
        limit.setRowCount(new SQLIntegerExpr(rowCount));
        if (offset > 0) {
            limit.setOffset(new SQLIntegerExpr(offset));
        }
        mySqlQueryBlock.setLimit(limit);
        return this;
    }
    if (queryBlock instanceof SQLServerSelectQueryBlock) {
        SQLServerSelectQueryBlock sqlserverQueryBlock = (SQLServerSelectQueryBlock) queryBlock;
        if (offset <= 0) {
            SQLServerTop top = new SQLServerTop();
            top.setExpr(new SQLIntegerExpr(rowCount));
            sqlserverQueryBlock.setTop(top);
        } else {
            throw new UnsupportedOperationException("not support offset");
        }
        return this;
    }
    if (queryBlock instanceof PGSelectQueryBlock) {
        PGSelectQueryBlock pgQueryBlock = (PGSelectQueryBlock) queryBlock;
        SQLLimit limit = new SQLLimit();
        if (offset > 0) {
            limit.setOffset(new SQLIntegerExpr(offset));
        }
        limit.setRowCount(new SQLIntegerExpr(rowCount));
        pgQueryBlock.setLimit(limit);
        return this;
    }
    if (queryBlock instanceof DB2SelectQueryBlock) {
        DB2SelectQueryBlock db2QueryBlock = (DB2SelectQueryBlock) queryBlock;
        if (offset <= 0) {
            SQLExpr rowCountExpr = new SQLIntegerExpr(rowCount);
            db2QueryBlock.setFirst(rowCountExpr);
        } else {
            throw new UnsupportedOperationException("not support offset");
        }
        return this;
    }
    if (queryBlock instanceof OracleSelectQueryBlock) {
        OracleSelectQueryBlock oracleQueryBlock = (OracleSelectQueryBlock) queryBlock;
        if (offset <= 0) {
            SQLExpr rowCountExpr = new SQLIntegerExpr(rowCount);
            SQLExpr newCondition = SQLUtils.buildCondition(SQLBinaryOperator.BooleanAnd, rowCountExpr, false, oracleQueryBlock.getWhere());
            queryBlock.setWhere(newCondition);
        } else {
            throw new UnsupportedOperationException("not support offset");
        }
        return this;
    }
    if (queryBlock instanceof OdpsSelectQueryBlock) {
        OdpsSelectQueryBlock odpsQueryBlock = (OdpsSelectQueryBlock) queryBlock;
        if (offset > 0) {
            throw new UnsupportedOperationException("not support offset");
        }
        odpsQueryBlock.setLimit(new SQLLimit(new SQLIntegerExpr(rowCount)));
        return this;
    }
    throw new UnsupportedOperationException();
}

Example 4 with PGSelectQueryBlock

use of com.alibaba.druid.sql.dialect.postgresql.ast.stmt.PGSelectQueryBlock in project druid by alibaba.

the class PGSelectParser method query.

@Override
public SQLSelectQuery query() {
    if (lexer.token() == Token.VALUES) {
        lexer.nextToken();
        accept(Token.LPAREN);
        PGValuesQuery valuesQuery = new PGValuesQuery();
        this.exprParser.exprList(valuesQuery.getValues(), valuesQuery);
        accept(Token.RPAREN);
        return queryRest(valuesQuery);
    }
    if (lexer.token() == Token.LPAREN) {
        lexer.nextToken();
        SQLSelectQuery select = query();
        if (select instanceof SQLSelectQueryBlock) {
            ((SQLSelectQueryBlock) select).setParenthesized(true);
        }
        accept(Token.RPAREN);
        return queryRest(select);
    }
    PGSelectQueryBlock queryBlock = new PGSelectQueryBlock();
    if (lexer.token() == Token.SELECT) {
        lexer.nextToken();
        if (lexer.token() == Token.COMMENT) {
            lexer.nextToken();
        }
        if (lexer.token() == Token.DISTINCT) {
            queryBlock.setDistionOption(SQLSetQuantifier.DISTINCT);
            lexer.nextToken();
            if (lexer.token() == Token.ON) {
                lexer.nextToken();
                for (; ; ) {
                    SQLExpr expr = this.createExprParser().expr();
                    queryBlock.getDistinctOn().add(expr);
                    if (lexer.token() == Token.COMMA) {
                        lexer.nextToken();
                        continue;
                    } else {
                        break;
                    }
                }
            }
        } else if (lexer.token() == Token.ALL) {
            queryBlock.setDistionOption(SQLSetQuantifier.ALL);
            lexer.nextToken();
        }
        parseSelectList(queryBlock);
        if (lexer.token() == Token.INTO) {
            lexer.nextToken();
            if (lexer.token() == Token.TEMPORARY) {
                lexer.nextToken();
                queryBlock.setIntoOption(IntoOption.TEMPORARY);
            } else if (lexer.token() == Token.TEMP) {
                lexer.nextToken();
                queryBlock.setIntoOption(IntoOption.TEMP);
            } else if (lexer.token() == Token.UNLOGGED) {
                lexer.nextToken();
                queryBlock.setIntoOption(IntoOption.UNLOGGED);
            }
            if (lexer.token() == Token.TABLE) {
                lexer.nextToken();
            }
            SQLExpr name = this.createExprParser().name();
            queryBlock.setInto(new SQLExprTableSource(name));
        }
    }
    parseFrom(queryBlock);
    parseWhere(queryBlock);
    parseGroupBy(queryBlock);
    if (lexer.token() == Token.WINDOW) {
        lexer.nextToken();
        PGSelectQueryBlock.WindowClause window = new PGSelectQueryBlock.WindowClause();
        window.setName(this.expr());
        accept(Token.AS);
        for (; ; ) {
            SQLExpr expr = this.createExprParser().expr();
            window.getDefinition().add(expr);
            if (lexer.token() == Token.COMMA) {
                lexer.nextToken();
                continue;
            } else {
                break;
            }
        }
        queryBlock.setWindow(window);
    }
    queryBlock.setOrderBy(this.createExprParser().parseOrderBy());
    for (; ; ) {
        if (lexer.token() == Token.LIMIT) {
            SQLLimit limit = new SQLLimit();
            lexer.nextToken();
            if (lexer.token() == Token.ALL) {
                limit.setRowCount(new SQLIdentifierExpr("ALL"));
                lexer.nextToken();
            } else {
                limit.setRowCount(expr());
            }
            queryBlock.setLimit(limit);
        } else if (lexer.token() == Token.OFFSET) {
            SQLLimit limit = queryBlock.getLimit();
            if (limit == null) {
                limit = new SQLLimit();
                queryBlock.setLimit(limit);
            }
            lexer.nextToken();
            SQLExpr offset = expr();
            limit.setOffset(offset);
            if (lexer.token() == Token.ROW || lexer.token() == Token.ROWS) {
                lexer.nextToken();
            }
        } else {
            break;
        }
    }
    if (lexer.token() == Token.FETCH) {
        lexer.nextToken();
        PGSelectQueryBlock.FetchClause fetch = new PGSelectQueryBlock.FetchClause();
        if (lexer.token() == Token.FIRST) {
            fetch.setOption(PGSelectQueryBlock.FetchClause.Option.FIRST);
        } else if (lexer.token() == Token.NEXT) {
            fetch.setOption(PGSelectQueryBlock.FetchClause.Option.NEXT);
        } else {
            throw new ParserException("expect 'FIRST' or 'NEXT'");
        }
        SQLExpr count = expr();
        fetch.setCount(count);
        if (lexer.token() == Token.ROW || lexer.token() == Token.ROWS) {
            lexer.nextToken();
        } else {
            throw new ParserException("expect 'ROW' or 'ROWS'");
        }
        if (lexer.token() == Token.ONLY) {
            lexer.nextToken();
        } else {
            throw new ParserException("expect 'ONLY'");
        }
        queryBlock.setFetch(fetch);
    }
    if (lexer.token() == Token.FOR) {
        lexer.nextToken();
        PGSelectQueryBlock.ForClause forClause = new PGSelectQueryBlock.ForClause();
        if (lexer.token() == Token.UPDATE) {
            forClause.setOption(PGSelectQueryBlock.ForClause.Option.UPDATE);
            lexer.nextToken();
        } else if (lexer.token() == Token.SHARE) {
            forClause.setOption(PGSelectQueryBlock.ForClause.Option.SHARE);
            lexer.nextToken();
        } else {
            throw new ParserException("expect 'FIRST' or 'NEXT'");
        }
        if (lexer.token() == Token.OF) {
            for (; ; ) {
                SQLExpr expr = this.createExprParser().expr();
                forClause.getOf().add(expr);
                if (lexer.token() == Token.COMMA) {
                    lexer.nextToken();
                    continue;
                } else {
                    break;
                }
            }
        }
        if (lexer.token() == Token.NOWAIT) {
            lexer.nextToken();
            forClause.setNoWait(true);
        }
        queryBlock.setForClause(forClause);
    }
    return queryRest(queryBlock);
}