Examples with AstStart com.developmentontheedge.sql.model.AstStart used on opensource projects

Example 21 with AstStart

use of com.developmentontheedge.sql.model.AstStart in project be5 by DevelopmentOnTheEdge.

the class ContextApplierApplyParametersTest method typeSupport.

@Test
public void typeSupport() {
    AstStart start = SqlQuery.parse("SELECT * FROM table WHERE date = <parameter:date type=\"java.sql.Date\"/>");
    ContextApplier contextApplier = new ContextApplier(new BasicQueryContext.Builder().parameter("date", "1900-01-01").build());
    contextApplier.applyContext(start);
    assertEquals("SELECT * FROM table WHERE date = '1900-01-01'", start.getQuery().toString());
}

Example 22 with AstStart

use of com.developmentontheedge.sql.model.AstStart in project be5 by DevelopmentOnTheEdge.

the class FilterApplierTest method testSetFilterApplierUnion.

@Test
public void testSetFilterApplierUnion() {
    AstStart query = SqlQuery.parse("SELECT name FROM bbc WHERE name LIKE 'Z%' UNION SELECT name FROM actor WHERE name LIKE 'Z%'");
    Map<ColumnRef, Object> conditions = Collections.singletonMap(ColumnRef.resolve(query, "name"), "name");
    new FilterApplier().setFilter(query, conditions);
    assertEquals("SELECT * FROM (SELECT name FROM bbc UNION SELECT name FROM actor) tmp WHERE name ='name'", new Formatter().format(query, new Context(Dbms.POSTGRESQL), new DefaultParserContext()));
// conditions = Collections.singletonMap( ColumnRef.resolve( query, "name1" ), "name1" );
// new FilterApplier().setFilter( query, conditions );
// assertEquals( "SELECT * FROM (SELECT name FROM bbc UNION SELECT name FROM actor) tmp WHERE name1 ='name1'",
// new Formatter().format( query, new Context( Dbms.POSTGRESQL ), new DefaultParserContext() ) );
}

Example 23 with AstStart

use of com.developmentontheedge.sql.model.AstStart in project be5 by DevelopmentOnTheEdge.

the class FilterApplierTest method testSetFilterApplier.

@Test
public void testSetFilterApplier() {
    AstStart query = SqlQuery.parse("SELECT * FROM games g, city WHERE g.city = city.name");
    Map<ColumnRef, Object> conditions = EntryStream.<String, Object>of("city.country", "UK", "games.yr", 2012).mapKeys(key -> ColumnRef.resolve(query, key)).toCustomMap(LinkedHashMap::new);
    new FilterApplier().setFilter(query, conditions);
    assertEquals("SELECT * FROM games g, city WHERE city.country ='UK' AND g.yr = 2012", new Formatter().format(query, new Context(Dbms.POSTGRESQL), new DefaultParserContext()));
    AstStart query2 = SqlQuery.parse("SELECT city.name, g.* FROM city INNER JOIN games g ON (g.city = city.name)");
    new FilterApplier().setFilter(query2, conditions);
    assertEquals("SELECT city.name, g.* FROM city INNER JOIN games g WHERE city.country ='UK' AND g.yr = 2012", new Formatter().format(query2, new Context(Dbms.POSTGRESQL), new DefaultParserContext()));
    AstStart query3 = SqlQuery.parse("SELECT * FROM city JOIN games g ON (g.city = city.name) JOIN games gm ON city.country ='UK'");
    new FilterApplier().setFilter(query3, conditions);
    assertEquals("SELECT * FROM city INNER JOIN games g INNER JOIN games gm WHERE city.country ='UK' AND g.yr = 2012", new Formatter().format(query3, new Context(Dbms.POSTGRESQL), new DefaultParserContext()));
}

Example 24 with AstStart

use of com.developmentontheedge.sql.model.AstStart 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()));
}

Example 25 with AstStart

use of com.developmentontheedge.sql.model.AstStart in project be5 by DevelopmentOnTheEdge.

the class MacroTest method testMacro.

@Test
public void testMacro() {
    SqlParser parser = new SqlParser();
    String input = "MACRO A(arg1=default, arg2=NOW()) \'<!--\' || CAST((arg2) AS CHAR) || \'-->\' || \'<a href=\"...\">\' || arg1 || \'</a>\' END";
    parser.parse(input);
    if (!parser.getMessages().isEmpty()) {
        throw new IllegalArgumentException(String.join("\n", parser.getMessages()));
    }
    ParserContext context = parser.getContext();
    AstStart start = SqlQuery.parse("SELECT A(a, b) FROM table t", context);
    new MacroExpander().expandMacros(start);
    assertEquals("SELECT  \'<!--\' || TO_CHAR(( b))|| \'-->\' || \'<a href=\"...\">\' || a || \'</a>\'  FROM table t", new Formatter().format(start, new Context(Dbms.ORACLE), context));
    start = SqlQuery.parse("SELECT A(a) FROM table t", context);
    new MacroExpander().expandMacros(start);
    assertEquals("SELECT  \'<!--\' || TO_CHAR((SYSDATE))|| \'-->\' || \'<a href=\"...\">\' || a || \'</a>\'  FROM table t", new Formatter().format(start, new Context(Dbms.ORACLE), context));
    SqlParser newParser = new SqlParser();
    input = "MACRO B(arg1, arg2, arg3) arg1 || arg2 || A(arg3) END";
    newParser.setContext(context);
    newParser.parse(input);
    if (!newParser.getMessages().isEmpty()) {
        throw new IllegalArgumentException(String.join("\n", newParser.getMessages()));
    }
    start = SqlQuery.parse("SELECT B(a, b, c) FROM table t", context);
    new MacroExpander().expandMacros(start);
    assertEquals("SELECT  a ||  b || '<!--' || TO_CHAR((SYSDATE))|| \'-->\' || '<a href=\"...\">\' ||  c || \'</a>\'   FROM table t", new Formatter().format(start, new Context(Dbms.ORACLE), context));
}