You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by venu <VE...@GMAIL.COM> on 2013/07/01 06:09:16 UTC
Re: Using ASTParser and TreeWalker for parsing SQL query
Hi Rick,
Thanks for spending your valuable time on this issue.
Sorry if I ask the same question again.
Could you please help me on this ?
ex:
select colName from tableName where id=100 having COUNT(*) > 1
When I go through the nodes, I can see the where clause and having clause
both have defined in a same way (node tree structure).
Both doesn't have parent node. Both are using BinaryRelationalOperatorNode
as parent node if, both have the condition with some operator (like = in
where clause and > in having clause).
But, for differentiate these two we don't have any parent node.
For example if we take a look at from or groupby or orderby they have root
nodes like fromlist, groupbylist and orderbylist.
for where clause and having clause both doesn't have parent node.
-----------------
whereClause:
org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode@35b835b8
operator: =
methodName: equals
dataTypeServices: null
leftOperand:
org.apache.derby.impl.sql.compile.ColumnReference@33c433c4
columnName: ID
tableNumber: -1
columnNumber: 0
replacesAggregate: false
replacesWindowFunctionCall: false
tableName: null
nestingLevel: -1
sourceLevel: -1
dataTypeServices: null
rightOperand:
org.apache.derby.impl.sql.compile.NumericConstantNode@34a434a4
value: 100
dataTypeServices: INTEGER NOT NULL
havingClause:
org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode@24622462
operator: >
methodName: greaterThan
dataTypeServices: null
leftOperand:
org.apache.derby.impl.sql.compile.AggregateNode@73c273c2
aggregateName: COUNT(*)
distinct: false
operator: null
methodName: null
dataTypeServices: null
rightOperand:
org.apache.derby.impl.sql.compile.NumericConstantNode@23722372
value: 1
dataTypeServices: INTEGER NOT NULL
-----------------------------
If I provide any conditional operator like AND or OR to where clause or
having clause, then the structure is different for both of these two.
My problem is, I want to take having clause data and where clause data from
the query.
So, not able to get the data properly due to the structure is changing for
these two items every time.
Is there any way to differentiate these two elements ?
or can we add any parent node for these two like others?
If not possible could you please give some advice on 'where I need to change
or which way I need to move' for solving my issue ?
Thanks in advance,
Venu.
--
View this message in context: http://apache-database.10148.n7.nabble.com/Using-ASTParser-and-TreeWalker-for-parsing-SQL-query-tp131219p132167.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Using ASTParser and TreeWalker for parsing SQL query
Posted by Rick Hillegas <ri...@oracle.com>.
Hi Venu,
Glad to hear that this technique is working for you. A couple general
comments:
A) If you need code added to the Derby engine, the best approach would
be to file a JIRA and attach a patch file. That makes it easier for
committers to evaluate your improvements and track changes to the codebase.
B) I see that you want to introduce a new NodeFilterSingletone class,
whose purpose seems to be to mark the boundaries of SQL clauses. Keep an
eye on https://issues.apache.org/jira/browse/DERBY-6434. I expect to
propose a different solution to that problem in an upcoming patch. You
may be able to use it.
C) It's probably best to move this discussion to derby-dev. That's a
better forum for discussing changes to the Derby codebase.
One other comment inline...
On 1/9/14 6:47 PM, venu wrote:
> Hi Rick,
> I tried your solution (regarding AST parser) and its working perfectly.
> I have some problems could you please give me some advice on these issues.
>
> 1. We added TreeWalker.java file in
> java\engine\org\apache\derby\impl\sql\compile folder.
> While generating derby.jar the class file was generated but class file was
> not inserted into the jar file.
> How can I add TreeWalker.class into derby.jar file ?
> I added the class into DBMSnodes.properties file but still the class file
> not found in jar.
> Could you please tell me how to add this? or if possible add this file in
> the source then I can modify it.
Try adding the class to tools/jar/extraDBMSclasses.properties.
Hope this helps,
-Rick
> 2. I did some changes on these files for solving order by and group
> by issue. If you have some time, could you please look into these codes ?
> If you feel these codes are correct could you please add those into
> the official version or even development version? If not, advice me on
> 'where to change or strategy which I need to follow'.
>
> \java\engine\org\apache\derby\impl\sql\compile\ValueNodeList.java -->
> added below method
>
> public ArrayList getValueNodes(){
> ArrayList valueNode = new ArrayList();
> for (int index = 0; index< size(); index++) {
> valueNode.add((QueryTreeNode)elementAt(index));
> }
> return valueNode;
> }
>
> \java\engine\org\apache\derby\impl\sql\compile\CastNode.java --> added
> below method
>
> public ValueNode getCastOperand(){
> return castOperand;
> }
>
>
> \java\engine\org\apache\derby\impl\sql\compile\SelectNode.java -->
> modified below method
>
> void acceptChildren(Visitor v) throws StandardException
> {
> super.acceptChildren(v);
>
> if (fromList != null)
> {
> NodeFilterSingletone.getInstance().setFromListState(true);
> --> Added this line
> fromList = (FromList)fromList.accept(v);
> NodeFilterSingletone.getInstance().setFromListState(false);
> --> Added this line
>
> }
>
> if (whereClause != null)
> {
>
> NodeFilterSingletone.getInstance().setWhereClauseState(true); --> Added
> this line
> whereClause = (ValueNode)whereClause.accept(v);
>
> NodeFilterSingletone.getInstance().setWhereClauseState(false); --> Added
> this line
> }
>
> if (wherePredicates != null)
> {
> wherePredicates = (PredicateList)wherePredicates.accept(v);
> }
>
> if (havingClause != null) {
>
> NodeFilterSingletone.getInstance().setHavingClauseState(true); --> Added
> this line
> havingClause = (ValueNode)havingClause.accept(v);
>
> NodeFilterSingletone.getInstance().setHavingClauseState(false); --> Added
> this line
> }
>
> // visiting these clauses was added as part of DERBY-6263. a better
> fix might be to fix the
> // visitor rather than skip it.
> if ( !(v instanceof HasCorrelatedCRsVisitor) )
> {
> if (selectSubquerys != null)
> {
> selectSubquerys = (SubqueryList) selectSubquerys.accept( v
> );
> }
>
> if (whereSubquerys != null)
> {
> whereSubquerys = (SubqueryList) whereSubquerys.accept( v );
> }
>
> if (groupByList != null) {
> NodeFilterSingletone.getInstance().setGroupbyState(true);
> --> Added this line
> groupByList = (GroupByList) groupByList.accept( v );
> NodeFilterSingletone.getInstance().setGroupbyState(false);
> --> Added this line
> }
>
> if (orderByLists[0] != null) {
> for (int i = 0; i< orderByLists.length; i++) {
>
> NodeFilterSingletone.getInstance().setOrderbyState(true); --> Added this
> line
> orderByLists[i] = (OrderByList) orderByLists[ i
> ].accept( v );
>
> NodeFilterSingletone.getInstance().setOrderbyState(false); --> Added this
> line
> }
>
> }
>
> if (offset != null) {
> offset = (ValueNode) offset.accept( v );
> }
>
> if (fetchFirst != null) {
> fetchFirst = (ValueNode) fetchFirst.accept( v );
> }
>
> if (preJoinFL != null)
> {
> preJoinFL = (FromList) preJoinFL.accept( v );
> }
>
> if (windows != null)
> {
> windows = (WindowList) windows.accept( v );
> }
> }
> }
>
> \java\engine\org\apache\derby\impl\sql\compile\ NodeFilterSingletone.java
> --> Added this class along with TreeWalker.java class
>
> public class NodeFilterSingletone {
>
> private static NodeFilterSingletone instance = null;
>
> private boolean fromListState = false;
> private boolean whereClauseState = false;
> private boolean havingClauseState = false;
> private boolean groupbyState = false;
> private boolean orderbyState = false;
>
> protected NodeFilterSingletone() {
> }
>
> public static NodeFilterSingletone getInstance() {
> if (instance == null) {
> instance = new NodeFilterSingletone();
> }
> return instance;
> }
>
> public boolean isFromListState() {
> return fromListState;
> }
>
> public void setFromListState(boolean fromListState) {
> this.fromListState = fromListState;
> }
>
> public boolean isWhereClauseState() {
> return whereClauseState;
> }
>
> public void setWhereClauseState(boolean whereClauseState) {
> this.whereClauseState = whereClauseState;
> }
>
> public boolean isHavingClauseState() {
> return havingClauseState;
> }
>
> public void setHavingClauseState(boolean havingClauseState) {
> this.havingClauseState = havingClauseState;
> }
>
> public boolean isGroupbyState() {
> return groupbyState;
> }
>
> public void setGroupbyState(boolean groupbyState) {
> this.groupbyState = groupbyState;
> }
>
> public boolean isOrderbyState() {
> return orderbyState;
> }
>
> public void setOrderbyState(boolean orderbyState) {
> this.orderbyState = orderbyState;
> }
> }
>
>
> Thanks,
> Venu.
> (Sorry for adding so much code in the post)
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/Using-ASTParser-and-TreeWalker-for-parsing-SQL-query-tp131219p136426.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
Re: Using ASTParser and TreeWalker for parsing SQL query
Posted by venu <VE...@GMAIL.COM>.
Hi Rick,
I tried your solution (regarding AST parser) and its working perfectly.
I have some problems could you please give me some advice on these issues.
1. We added TreeWalker.java file in
java\engine\org\apache\derby\impl\sql\compile folder.
While generating derby.jar the class file was generated but class file was
not inserted into the jar file.
How can I add TreeWalker.class into derby.jar file ?
I added the class into DBMSnodes.properties file but still the class file
not found in jar.
Could you please tell me how to add this? or if possible add this file in
the source then I can modify it.
2. I did some changes on these files for solving order by and group
by issue. If you have some time, could you please look into these codes ?
If you feel these codes are correct could you please add those into
the official version or even development version? If not, advice me on
'where to change or strategy which I need to follow'.
\java\engine\org\apache\derby\impl\sql\compile\ValueNodeList.java -->
added below method
public ArrayList getValueNodes(){
ArrayList valueNode = new ArrayList();
for (int index = 0; index < size(); index++) {
valueNode.add((QueryTreeNode)elementAt(index));
}
return valueNode;
}
\java\engine\org\apache\derby\impl\sql\compile\CastNode.java --> added
below method
public ValueNode getCastOperand(){
return castOperand;
}
\java\engine\org\apache\derby\impl\sql\compile\SelectNode.java -->
modified below method
void acceptChildren(Visitor v) throws StandardException
{
super.acceptChildren(v);
if (fromList != null)
{
NodeFilterSingletone.getInstance().setFromListState(true);
--> Added this line
fromList = (FromList)fromList.accept(v);
NodeFilterSingletone.getInstance().setFromListState(false);
--> Added this line
}
if (whereClause != null)
{
NodeFilterSingletone.getInstance().setWhereClauseState(true); --> Added
this line
whereClause = (ValueNode)whereClause.accept(v);
NodeFilterSingletone.getInstance().setWhereClauseState(false); --> Added
this line
}
if (wherePredicates != null)
{
wherePredicates = (PredicateList)wherePredicates.accept(v);
}
if (havingClause != null) {
NodeFilterSingletone.getInstance().setHavingClauseState(true); --> Added
this line
havingClause = (ValueNode)havingClause.accept(v);
NodeFilterSingletone.getInstance().setHavingClauseState(false); --> Added
this line
}
// visiting these clauses was added as part of DERBY-6263. a better
fix might be to fix the
// visitor rather than skip it.
if ( !(v instanceof HasCorrelatedCRsVisitor) )
{
if (selectSubquerys != null)
{
selectSubquerys = (SubqueryList) selectSubquerys.accept( v
);
}
if (whereSubquerys != null)
{
whereSubquerys = (SubqueryList) whereSubquerys.accept( v );
}
if (groupByList != null) {
NodeFilterSingletone.getInstance().setGroupbyState(true);
--> Added this line
groupByList = (GroupByList) groupByList.accept( v );
NodeFilterSingletone.getInstance().setGroupbyState(false);
--> Added this line
}
if (orderByLists[0] != null) {
for (int i = 0; i < orderByLists.length; i++) {
NodeFilterSingletone.getInstance().setOrderbyState(true); --> Added this
line
orderByLists[i] = (OrderByList) orderByLists[ i
].accept( v );
NodeFilterSingletone.getInstance().setOrderbyState(false); --> Added this
line
}
}
if (offset != null) {
offset = (ValueNode) offset.accept( v );
}
if (fetchFirst != null) {
fetchFirst = (ValueNode) fetchFirst.accept( v );
}
if (preJoinFL != null)
{
preJoinFL = (FromList) preJoinFL.accept( v );
}
if (windows != null)
{
windows = (WindowList) windows.accept( v );
}
}
}
\java\engine\org\apache\derby\impl\sql\compile\ NodeFilterSingletone.java
--> Added this class along with TreeWalker.java class
public class NodeFilterSingletone {
private static NodeFilterSingletone instance = null;
private boolean fromListState = false;
private boolean whereClauseState = false;
private boolean havingClauseState = false;
private boolean groupbyState = false;
private boolean orderbyState = false;
protected NodeFilterSingletone() {
}
public static NodeFilterSingletone getInstance() {
if (instance == null) {
instance = new NodeFilterSingletone();
}
return instance;
}
public boolean isFromListState() {
return fromListState;
}
public void setFromListState(boolean fromListState) {
this.fromListState = fromListState;
}
public boolean isWhereClauseState() {
return whereClauseState;
}
public void setWhereClauseState(boolean whereClauseState) {
this.whereClauseState = whereClauseState;
}
public boolean isHavingClauseState() {
return havingClauseState;
}
public void setHavingClauseState(boolean havingClauseState) {
this.havingClauseState = havingClauseState;
}
public boolean isGroupbyState() {
return groupbyState;
}
public void setGroupbyState(boolean groupbyState) {
this.groupbyState = groupbyState;
}
public boolean isOrderbyState() {
return orderbyState;
}
public void setOrderbyState(boolean orderbyState) {
this.orderbyState = orderbyState;
}
}
Thanks,
Venu.
(Sorry for adding so much code in the post)
--
View this message in context: http://apache-database.10148.n7.nabble.com/Using-ASTParser-and-TreeWalker-for-parsing-SQL-query-tp131219p136426.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Using ASTParser and TreeWalker for parsing SQL query
Posted by Rick Hillegas <ri...@oracle.com>.
On 6/30/13 9:09 PM, venu wrote:
> Hi Rick,
> Thanks for spending your valuable time on this issue.
> Sorry if I ask the same question again.
>
> Could you please help me on this ?
> ex:
> select colName from tableName where id=100 having COUNT(*)> 1
> When I go through the nodes, I can see the where clause and having clause
> both have defined in a same way (node tree structure).
>
> Both doesn't have parent node. Both are using BinaryRelationalOperatorNode
> as parent node if, both have the condition with some operator (like = in
> where clause and> in having clause).
>
> But, for differentiate these two we don't have any parent node.
> For example if we take a look at from or groupby or orderby they have root
> nodes like fromlist, groupbylist and orderbylist.
>
> for where clause and having clause both doesn't have parent node.
> -----------------
> whereClause:
> org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode@35b835b8
> operator: =
> methodName: equals
> dataTypeServices: null
> leftOperand:
> org.apache.derby.impl.sql.compile.ColumnReference@33c433c4
> columnName: ID
> tableNumber: -1
> columnNumber: 0
> replacesAggregate: false
> replacesWindowFunctionCall: false
> tableName: null
> nestingLevel: -1
> sourceLevel: -1
> dataTypeServices: null
> rightOperand:
> org.apache.derby.impl.sql.compile.NumericConstantNode@34a434a4
> value: 100
> dataTypeServices: INTEGER NOT NULL
> havingClause:
> org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode@24622462
> operator:>
> methodName: greaterThan
> dataTypeServices: null
> leftOperand:
> org.apache.derby.impl.sql.compile.AggregateNode@73c273c2
> aggregateName: COUNT(*)
> distinct: false
> operator: null
> methodName: null
> dataTypeServices: null
> rightOperand:
> org.apache.derby.impl.sql.compile.NumericConstantNode@23722372
> value: 1
> dataTypeServices: INTEGER NOT NULL
> -----------------------------
>
> If I provide any conditional operator like AND or OR to where clause or
> having clause, then the structure is different for both of these two.
>
> My problem is, I want to take having clause data and where clause data from
> the query.
>
> So, not able to get the data properly due to the structure is changing for
> these two items every time.
>
> Is there any way to differentiate these two elements ?
> or can we add any parent node for these two like others?
> If not possible could you please give some advice on 'where I need to change
> or which way I need to move' for solving my issue ?
>
> Thanks in advance,
> Venu.
Hi Venu,
If I understand correctly, the problem you are facing is that your
Visitor can't tell the difference between a whereClause and a
havingClause. They are both ValueNodes rather some more refined types
specific to each kind of clause. And they don't contain backpointers to
their parent SelectNodes so you can't get more information out of the
parent node when processing the whereClause and havingClause.
I don't have any clever solution to this problem. If I were tackling
this problem, I would maintain some extra state in the Visitor, say a
HashSet of whereClause references and another HashSet of havingClause
references. I would fill in these HashSets as I processed the
SelectNodes. Then when the Visitor got to the actual whereClause and
havingClause, it could look them up in the HashSets in order to figure
out what it was handing at the moment. Because the clause fields are
package private, the Visitor would need to live in the same package as
SelectNode. So in order to not incur a sealing violation, the Visitor
would have to be injected into derby.jar.
That's a little complicated, but it might help.
Hope that's useful,
-Rick
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/Using-ASTParser-and-TreeWalker-for-parsing-SQL-query-tp131219p132167.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>