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

Example 11 with Formatter

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

the class TestMain method testModule5DB.

public static StatContext testModule5DB(String name, List<String> okQueries) throws ProjectLoadException, ReadException {
    LoadContext ctx = new LoadContext();
    Project project = ModuleUtils.loadModule(name, ctx);
    ModuleUtils.addModuleScripts(project);
    ctx.check();
    SqlParser parser = new SqlParser();
    Formatter formatter = new Formatter();
    DefaultParserContext parserContext = new DefaultParserContext();
    parser.setContext(parserContext);
    Rdbms[] dbms = { Rdbms.MYSQL, Rdbms.DB2, Rdbms.ORACLE, Rdbms.SQLSERVER, Rdbms.POSTGRESQL };
    StatContext stat = new StatContext(name + "/5DB", StreamEx.of(dbms).map(Rdbms::name).prepend("Count", "PF").append("Success").toArray(String[]::new));
    for (String entityName : project.getEntityNames()) {
        Entity entity = project.getEntity(entityName);
        for (Query query : entity.getQueries()) {
            if (!allowedTypes.contains(query.getType()))
                continue;
            if (query.getName().equals("Table definition"))
                continue;
            if (query.getQuery().trim().isEmpty())
                continue;
            project.setDatabaseSystem(Rdbms.BESQL);
            String queryText;
            try {
                queryText = query.getQueryCompiled().validate();
            } catch (ProjectElementException e) {
                continue;
            }
            stat.inc("Count");
            parser.parse(queryText);
            if (!parser.getMessages().isEmpty()) {
                stat.inc("PF");
                continue;
            }
            try {
                formatter.format(parser.getStartNode(), new Context(Dbms.POSTGRESQL), parserContext);
            } catch (IllegalArgumentException e) {
                System.out.println(entityName + "." + query.getName() + ": " + e.getMessage());
                stat.inc("PF");
                continue;
            }
            boolean success = true;
            for (Rdbms db : dbms) {
                project.setDatabaseSystem(db);
                String expected;
                try {
                    expected = sanitizeValue(query.getQueryCompiled().validate());
                } catch (ProjectElementException e) {
                    continue;
                }
                String formatted;
                try {
                    formatted = formatter.format(parser.getStartNode(), new Context(Dbms.valueOf(db.name())), parserContext);
                } catch (IllegalArgumentException e) {
                    System.out.println(entityName + "." + query.getName() + ": " + e.getMessage());
                    stat.inc(db.name());
                    success = false;
                    continue;
                }
                String actual = sanitizeValue(formatted);
                if (!expected.equals(actual)) {
                    if (db == Rdbms.ORACLE) {
                        System.out.println(entityName + "." + query.getName());
                        System.out.println("BESQL: " + queryText);
                        System.out.println("Actual: " + actual);
                        System.out.println("Expected: " + expected);
                    }
                    stat.inc(db.name());
                    success = false;
                }
            }
            if (success) {
                stat.inc("Success");
            // okQueries.add( entityName+"."+query.getName() );
            }
        }
    }
    return stat;
}

Example 12 with Formatter

use of com.developmentontheedge.sql.format.Formatter 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 13 with Formatter

use of com.developmentontheedge.sql.format.Formatter 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 14 with Formatter

use of com.developmentontheedge.sql.format.Formatter 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 15 with Formatter

use of com.developmentontheedge.sql.format.Formatter 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));
}