Examples with LimitsApplier com.developmentontheedge.sql.format.LimitsApplier used on opensource projects

Example 1 with LimitsApplier

use of com.developmentontheedge.sql.format.LimitsApplier in project be5 by DevelopmentOnTheEdge.

the class ParserTarget method limit.

protected void limit(Element element) {
    checkQuery(TAG_LIMIT);
    int count = Integer.parseInt(element.getAttribute("count"));
    astStart = astStart.clone();
    new LimitsApplier(0, count).transform(astStart);
}

Example 2 with LimitsApplier

use of com.developmentontheedge.sql.format.LimitsApplier in project be5 by DevelopmentOnTheEdge.

the class Be5QueryExecutor method getFinalSql.

@Override
public String getFinalSql() {
    DebugQueryLogger dql = new DebugQueryLogger();
    dql.log("Orig", query.getQuery());
    String queryText = meta.getQueryCode(query, UserInfoHolder.getCurrentRoles());
    dql.log("After FreeMarker", queryText);
    if (queryText.isEmpty())
        return "";
    AstStart ast;
    try {
        ast = SqlQuery.parse(queryText);
    } catch (RuntimeException e) {
        log.log(Level.SEVERE, "SqlQuery.parse error: ", e);
        throw Be5Exception.internalInQuery(e, query);
    // ast = SqlQuery.parse("select 'error'");
    }
    dql.log("Compiled", ast);
    resolveTypeOfRefColumn(ast);
    // CONTEXT
    // FILTERS
    filterHelper.applyFilters(ast, query.getEntity().getName(), new HashMap<>(parametersMap));
    // CATEGORY
    // applyCategory( dql, ast );
    contextApplier.applyContext(ast);
    subQueryKeys = contextApplier.subQueryKeys().toSet();
    dql.log("With context", ast);
    // ID COLUMN
    if (query.getType() == QueryType.D1 && query.getEntity().findTableDefinition() != null && !hasColumnWithLabel(ast, DatabaseConstants.ID_COLUMN_LABEL)) {
        new ColumnAdder().addColumn(ast, query.getEntity().getName(), query.getEntity().getPrimaryKey(), DatabaseConstants.ID_COLUMN_LABEL);
        dql.log("With ID column", ast);
    } else {
        dql.log("Without ID column", ast);
    }
    // SIMPLIFY
    Simplifier.simplify(ast);
    dql.log("Simplified", ast);
    if (extraQuery == ExtraQuery.COUNT) {
        countFromQuery(ast.getQuery());
        dql.log("Count(1) from query", ast);
    }
    if (extraQuery == ExtraQuery.DEFAULT) {
        // SORT ORDER
        applySort(dql, ast);
        // LIMITS
        new LimitsApplier(offset, limit).transform(ast);
        dql.log("With limits", ast);
    }
    return new Formatter().format(ast, context, parserContext);
}

Example 3 with LimitsApplier

use of com.developmentontheedge.sql.format.LimitsApplier in project be5 by DevelopmentOnTheEdge.

the class LimitsApplierTest method testLimitsApplier.

@Test
public void testLimitsApplier() {
    AstStart start = SqlQuery.parse("SELECT * FROM products p ORDER BY buyprice DESC");
    new LimitsApplier(10, 20).transformQuery(start.getQuery());
    assertEquals("SELECT * FROM products p ORDER BY buyprice DESC LIMIT 10, 20", new Formatter().format(start, new Context(Dbms.MYSQL), new DefaultParserContext()));
    assertEquals("SELECT * FROM products p ORDER BY buyprice DESC LIMIT 20 OFFSET 10", new Formatter().format(start, new Context(Dbms.POSTGRESQL), new DefaultParserContext()));
    assertEquals("SELECT * FROM (SELECT p.*, ROW_NUMBER() OVER( ORDER BY buyprice DESC) AS rn FROM products p) AS tmp WHERE tmp.rn BETWEEN 10 AND 30", new Formatter().format(start, new Context(Dbms.DB2), new DefaultParserContext()));
    assertEquals("SELECT * FROM (SELECT p.*, ROW_NUMBER() OVER( ORDER BY buyprice DESC) AS rn FROM products p) AS tmp WHERE tmp.rn BETWEEN 10 AND 30", new Formatter().format(start, new Context(Dbms.SQLSERVER), new DefaultParserContext()));
    assertEquals("SELECT * FROM (SELECT tmp.*, ROWNUM rn FROM (SELECT * FROM products p ORDER BY buyprice DESC) tmp WHERE ROWNUM <= 30) WHERE ROWNUM > 10", new Formatter().format(start, new Context(Dbms.ORACLE), new DefaultParserContext()));
    start = SqlQuery.parse("SELECT name, address FROM products ORDER BY buyprice DESC");
    new LimitsApplier(10, 20).transformQuery(start.getQuery());
    assertEquals("SELECT name, address FROM products ORDER BY buyprice DESC LIMIT 10, 20", new Formatter().format(start, new Context(Dbms.MYSQL), new DefaultParserContext()));
    assertEquals("SELECT name, address FROM products ORDER BY buyprice DESC LIMIT 20 OFFSET 10", new Formatter().format(start, new Context(Dbms.POSTGRESQL), new DefaultParserContext()));
    assertEquals("SELECT name, address FROM (SELECT name, address, ROW_NUMBER() OVER( ORDER BY buyprice DESC) AS rn FROM products) AS tmp WHERE tmp.rn BETWEEN 10 AND 30", new Formatter().format(start, new Context(Dbms.DB2), new DefaultParserContext()));
    assertEquals("SELECT name, address FROM (SELECT name, address, ROW_NUMBER() OVER( ORDER BY buyprice DESC) AS rn FROM products) AS tmp WHERE tmp.rn BETWEEN 10 AND 30", new Formatter().format(start, new Context(Dbms.SQLSERVER), new DefaultParserContext()));
    assertEquals("SELECT name, address FROM (SELECT tmp.*, ROWNUM rn FROM (SELECT name, address FROM products ORDER BY buyprice DESC) tmp WHERE ROWNUM <= 30) WHERE ROWNUM > 10", new Formatter().format(start, new Context(Dbms.ORACLE), new DefaultParserContext()));
}