[FEATURE] Add option to call parent of expression

[voidlol]

I'm using JsqlParser to parse SQL script, which is always select statement and is written in Spark SQL syntax.

My goal is to travel through all functions and replace them with correct analog to target database (for example postgres).

Using visitor is great feature. but I can only change existing Function object, change function name, change parameters and so on.
But in some cases I need to replace this Function with other Expression, for example with Subtraction.

Look at this example:
SELECT to_unix_timestamp("col1") from mytable; -> this is valid spark sql
SELECT ("col1" - to_date('1970-01-01', 'yyyy-mm-dd')) * 86400 -> this is valid oracle sql

Here i need to change to_unix_timestamp function with Multiplication.

I wish there was an option to get parentExpression on my Function in a visitor and call something like replaceWith(multiplication)

[manticore-projects]

Greetings.
All of that is very possible. What challenges exactly are you facing please?

Maybe this explanations will help: https://manticore-projects.com/JSQLParser/usage.html#build-a-sql-statement

[voidlol]

Im trying to make this:
`class ReplaceExpressionAdapter extends ExpressionVisitorAdapter {

    @Override
    public void visit(Function function) {
        super.visit(function);
        String functionName = function.getName().toUpperCase();
        Expression replacement = Optional.ofNullable(functions.get(functionName))
                .map(c -> c.apply(function))
                .orElse(function);
    }

}`

Goal here is to replace Function function with a Expression replacement.
Steps:

1. Get sql string
2. Parse it as Statemetns
3. For each statement set a statement visitor
4. Indise statement visitor set PlainSelect visitor
5. In plainselect visitor travel through all expressions (in select body, group by, order by, joins, unions, where, having) with ExpressionVisitor and make expression visitor replace function it visits with replacement.
6. Call toString() on parsed statements

[voidlol]

Actually I can make it setting new expressions by hand, but due to many inheritors of Expression interface I have to implement traveling through nested expressions for each inheritor, like this:
private Expression processExpression(Expression expression) { if (expression instanceof Function f) { return replaceSparkBigFunction(f) .orElseGet(replaceRegularFunction(expression, f)); } else if (expression instanceof BinaryExpression be) { be.setLeftExpression(processExpression(be.getLeftExpression())); be.setRightExpression(processExpression(be.getRightExpression())); } else if (expression instanceof MultipleExpression me) { me.getList().replaceAll(this::processExpression); } else if (expression instanceof WhenClause wc) { wc.setThenExpression(processExpression(wc.getThenExpression())); wc.setWhenExpression(processExpression(wc.getWhenExpression())); } else if (expression instanceof AnalyticExpression ae) { ae.setExpression(processExpression(ae.getExpression())); Optional.ofNullable(ae.getFuncOrderBy()).ifPresent(l -> l.replaceAll(this::processOrderByElement)); } else if (expression instanceof CastExpression ce) { ce.setLeftExpression(processExpression(ce.getLeftExpression())); return cast(ce); } else if (expression instanceof Parenthesis p) { p.setExpression(processExpression(p.getExpression())); } else if (expression instanceof IsNullExpression ine) { ine.setLeftExpression(processExpression(ine.getLeftExpression())); } return expression; }

[manticore-projects]

Ok, now I understand at least what you ask for: For the AST Node "Function" your want to access the parent branch and/or replace the AST Node.

I actually have done something similar here: http://jsqlformatter.manticore-projects.com/jsqlformatter/demo.html?args=-c%20MoUQMiDCAqAE0HsD6BVAdgSwB5OhgtgKYDOALgIb4AOAFLAEQDGCANgIz2wCUAUAGIAlAPIBZWPgCeFAEYtCPANw8eoCDFh0mrDrAC08ZABFypQnQDkbAJwB2AAy67bR23MAaWOYneJu-Pl0AE0DzbjCAKlgADgA2ABY7O1gWRSA

In this example, you can get the Node from a XSLT Path (and so can get the parent Object).

[voidlol]

Yeah, probably it is possible already, but how can I implement it in java code?
`class ReplaceExpressionAdapter extends ExpressionVisitorAdapter {

    @Override
    public void visit(Function function) {
        super.visit(function);
        String functionName = function.getName().toUpperCase();
        Expression replacement = Optional.ofNullable(functions.get(functionName))
                .map(c -> c.apply(function))
                .orElse(function);
    }
}`

What should I do? I can get ASTNode from function, can even call getParent() on it, but how to actually replace?
Can find it out :(

[manticore-projects]

Hi, sorry I have not too much time right now.
If I was in your shoes I would use Jackson xml (or anything similar) to serialize the AST, then apply XSLT to find the Node of interest -- which will give you its parent. When you have the parent node, then you can replace its properties with the normal java methods of the POJO.

[manticore-projects]

Greetings. This example may be useful, although it depends on a small, unpublished amendment of the Grammar, linking the PrimaryExpressions to the AST:

  @Test
  public void testXPathReplace() throws Exception {
    String sqlStr = "select func1( func2( func3( x ) ) ) from  tablename where a=b and b=c;";
    PlainSelect select = (PlainSelect) CCJSqlParserUtil.parse(sqlStr);
    Function func1 = (Function) select.getSelectItem(0).getExpression();
    
    // pretend knowing only `func2` and trying to get its parent
    Function func2 = (Function) func1.getParameters().get(0);
    SimpleNode node = (SimpleNode) func2.getASTNode().jjtGetParent();
    while (node.jjtGetValue()==null) {
      node =  (SimpleNode) node.jjtGetParent();
    }

    Function parentFunction = (Function) node.jjtGetValue();

    Assertions.assertEquals(func1, parentFunction);
  }