-
-
Notifications
You must be signed in to change notification settings - Fork 1.4k
-
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)
Beta Was this translation helpful? Give feedback.
All reactions
Replies: 3 comments 4 replies
-
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
Beta Was this translation helpful? Give feedback.
All reactions
-
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:
- Get sql string
- Parse it as Statemetns
- For each statement set a statement visitor
- Indise statement visitor set PlainSelect visitor
- 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.
- Call toString() on parsed statements
Beta Was this translation helpful? Give feedback.
All reactions
-
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; }
Beta Was this translation helpful? Give feedback.
All reactions
-
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).
Beta Was this translation helpful? Give feedback.
All reactions
-
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 :(
Beta Was this translation helpful? Give feedback.
All reactions
-
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.
Beta Was this translation helpful? Give feedback.
All reactions
-
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); }
Beta Was this translation helpful? Give feedback.