akiban sql parser

587 views
Skip to first unread message

Mikhail Khodonov

unread,
Mar 11, 2013, 7:48:52 AM3/11/13
to akiba...@akiban.com
Hi,
I am a newbie in akiban.sql.parser, I try to use it in my project. Could you please explain, suppose I want to parse sql select:
1. in javadoc parseStatement returns StatementNode, but SelectNode inherited from ResultSetNode -> QueryTreeNode, while StatementNode -> QueryTreeNode.
What to do to get select node?
2. in javadoc for DMLStatementNode "A DMLStatementNode represents any type of DML statement". Do you mean "insert, update, delete" or "select, insert, update, delete"?
Further description points for the second "A SELECT statement sends its result set to the client". But DMLStatementNode has childs DeleteNode, InsertNode, UpdateNode and there is no SelectNode.
3. SelectNode doesn't have methods to get "order by" clause (while InsertNode has, by the way, what do you mean to add getOrderByList method for it?). How I get "order by" clause for select node?
4. a few words about javadocs, version 1.0.11 it's ok, but 1.0.13 has an unusual appearance (both in chrome and ff), and it is inconvenient to use, see attach

Best regards,
Mikhail

shotscreen2013-03-11 15:46:39.png

Nathan Williams

unread,
Mar 11, 2013, 8:09:12 PM3/11/13
to Mikhail Khodonov, akiba...@akiban.com
Hi Mikhail,

Glad you found the project. Answers to your questions in-line. Feel free to ask as many as needed :)

On Monday, March 11, 2013 at 7:48 AM, Mikhail Khodonov wrote:

1. in javadoc parseStatement returns StatementNode, but SelectNode inherited from ResultSetNode -> QueryTreeNode, while StatementNode -> QueryTreeNode.
What to do to get select node?
A good place to start might be this demo I put together. It has a simple visitor and dumps all the columns it encounters:

The README should get you up and running. You can then pipe a statement like this into it:
$ echo "SELECT * FROM t ORDER BY x" |java -jar target/ParserDemo.jar
Statement: CursorNode
  *, x

As you can see, that's often what we're interested in. The key bit here is that CursorNode is the concrete type you get back from a SELECT and visiting will take you to the order by list.

If you don't want to do a visitor and happy to assume (or check) that you have a CursorNode, there is a getOrderByList() available on it.

2. in javadoc for DMLStatementNode "A DMLStatementNode represents any type of DML statement". Do you mean "insert, update, delete" or "select, insert, update, delete"?
Further description points for the second "A SELECT statement sends its result set to the client". But DMLStatementNode has childs DeleteNode, InsertNode, UpdateNode and there is no SelectNode.
The SelectNode itself is only created for other places where a SELECT can be present. For example:
$ echo "DELETE FROM foo WHERE x IN (SELECT a FROM b)" |java -jar target/ParserDemo.jar
Statement: DeleteNode
  a, x

The DeleteNode has a getResultSetNode(), inherited from DMLStatementNode, which contains a SelectNode.

3. SelectNode doesn't have methods to get "order by" clause (while InsertNode has, by the way, what do you mean to add getOrderByList method for it?). How I get "order by" clause for select node?
First, something that will generate a SelectNode with an ORDER BY:
$ echo "DELETE FROM foo WHERE x IN (SELECT a FROM b ORDER BY c)" |java -jar target/ParserDemo.jar
Statement: DeleteNode
  a, x, c

If you wanted to tear it apart manually, something fragile like this could do the trick:
((SubqueryNode)((SelectNode)((DeleteNode)node).getResultSetNode()).getWhereClause()).getOrderByList()

That is the general form: SelectNode has a where clause which will have your order by list. The assumptions and casting would probably work fine for a quick code or script, but anything more complicated would want to use a visitor and track state as needed.

4. a few words about javadocs, version 1.0.11 it's ok, but 1.0.13 has an unusual appearance (both in chrome and ff), and it is inconvenient to use, see attach
Thanks for the catch! I see what you mean with the formatting. We'll be doing another release soon and double check that the JavaDoc is generated properly.


-Nathan

Mikhail Khodonov

unread,
Mar 12, 2013, 4:59:28 AM3/12/13
to Nathan Williams, akiba...@akiban.com
Hi, Nathan,
Thank you for your answers.
As I understand I should to use CursorNode for sql select. But the
comparison with SelectNode API shows that many of the necessary methods
(imho) are not available, e.g. getFromList(), getGroupByList(),
getHavingClause(), getWhereClause(), setGroupByList(),
setHavingClause(), setWhereClause()
I try to explain with my example. There is an object (databuffer) and
the associated sql query. Data are retrieved to the databuffer using
this query. User can set (via gui) additional filter conditions to sql
query. To do this I should:
a) parse sql query
b) get where clause
c) add new filter conditions to the where clause
d) assemble sql query with modified where clause
But it seems impossible because of there are no API methods
(getWhereClause(), setWhereClause()) or such node type (WhereNode),
please, see example source in attach

Best regards,
Mikhail


12.03.2013 04:09, Nathan Williams пишет:
QueryModifier.java

Nathan Williams

unread,
Mar 13, 2013, 5:22:54 PM3/13/13
to Mikhail Khodonov, akiba...@akiban.com
The CursorNode will generally have a SelectNode for you consumption from its getResultSetNode() method.

Here's a quick and dirty example to pull out the pieces you were interested in:
List<StatementNode> nodes = parser.parseStatements(sql);
for(StatementNode node : nodes) {
if(!(node instanceof CursorNode))
continue;
CursorNode cn = (CursorNode)node;
if(!(cn.getResultSetNode() instanceof SelectNode))
continue;
SelectNode sn = (SelectNode)cn.getResultSetNode();
System.out.println("From");
for(FromTable f : sn.getFromList())
System.out.println(" " + f.getTableName());
System.out.println("GroupBy");
for(GroupByColumn g : sn.getGroupByList())
System.out.println(" " + g.getColumnName());
System.out.println("OrderBy");
for(OrderByColumn o : cn.getOrderByList())
System.out.println(" " + o.getExpression().getColumnName());
}

When given this (syntactically valid) statement:
SELECT a,b FROM f WHERE a=5 GROUP BY a_g,b_g ORDER BY a_o,g_o

The output below is produced:
From
f
GroupBy
a_g
b_g
OrderBy
a_o
g_o

SelectNode has a getWhereClause() method as well, which can be any ValueNode (e.g. boolean, subquery, etc).

As far as modifying the query itself, there are a couple options. A very quick way would be to just put the query back together and string splice your modifications in. Using a visitor, you can modify the tree itself as your visiting it by returning new nodes or re-setting pieces of others. It all depends on what your application is doing and how much it needs to handle.

Good luck :)

-Nathan
> Attachments:
> - QueryModifier.java
>



Mikhail Khodonov

unread,
Mar 14, 2013, 2:13:15 AM3/14/13
to Nathan Williams, akiba...@akiban.com
Nathan, thank you very much

Mikhail

14.03.2013 01:22, Nathan Williams пишет:

Mikhail Khodonov

unread,
May 21, 2013, 11:29:02 PM5/21/13
to Nathan Williams, akiba...@akiban.com
Hi, Nathan,

Does parser support LIMIT clause?

Mikhail

Nathan Williams

unread,
May 24, 2013, 12:00:22 AM5/24/13
to Mikhail Khodonov, akiba...@akiban.com
On Tuesday, May 21, 2013 at 11:29 PM, Mikhail Khodonov wrote:
> Does parser support LIMIT clause?

It most certainly does!

A few variations should work as you might be familiar with:

SELECT x FROM t LIMIT 10; => Limit 10
SELECT x FROM t LIMIT 5,10; => Offset 5, limit 10
SELECT x FROM t LIMIT 10 OFFSET 5 => Offset 5, limit 10

Did you run into some trouble using such syntax?

-Nathan


Mikhail Khodonov

unread,
May 24, 2013, 12:25:51 AM5/24/13
to Nathan Williams, akiba...@akiban.com
Yes,
It seems something wrong with parser 1.0.45 to parse LIMIT clause.

My code:
String sQuery = (
"SELECT TRO_EVENT.TRE_ID, D_EVENT.EVT_CODE,
D_EVENT.EVT_NAME, D_EVENT.EVT_IS_SECURITY, " +
"TRO_OBJECT.TRO_UID, D_TRO_STATE.STA_NAME,
TRO_EVENT.TRE_DATE, TRO_EVENT.TRE_MSG FROM " +
"TRO_EVENT, D_TRO_STATE, TRO_OBJECT, D_EVENT WHERE " +
"D_TRO_STATE.STA_ID = TRO_OBJECT.STA_ID AND
TRO_OBJECT.TRO_ID = TRO_EVENT.TRO_ID AND " +
"D_EVENT.EVT_ID = TRO_EVENT.EVT_ID AND TRO_OBJECT.TRO_ID = ?
ORDER BY D_EVENT.EVT_ID LIMIT 100"
);
StatementNode node = _parser.parseStatement( sQuery );
node.treePrint( );
System.out.println( new NodeToString( ).toString( node ) );

Tree output, as you can see the LIMIT node disappeared:
com.akiban.sql.parser.CursorNode@4e8044a2
name: null
updateMode: UNSPECIFIED
statementType: SELECT
resultSet:
com.akiban.sql.parser.SelectNode@471a0ff1
isDistinct: false
resultColumns:
com.akiban.sql.parser.ResultColumnList@cbbaf

[0]:
com.akiban.sql.parser.ResultColumn@43f5de2f
exposedName: tre_id
name: tre_id
tableName: null
isDefaultColumn: false
type: null
expression:
com.akiban.sql.parser.ColumnReference@42ce0284
columnName: tre_id
tableName: tro_event
type: null
[1]:
com.akiban.sql.parser.ResultColumn@63074a5f
exposedName: evt_code
name: evt_code
tableName: null
isDefaultColumn: false
type: null
expression:
com.akiban.sql.parser.ColumnReference@73a3d5c3
columnName: evt_code
tableName: d_event
type: null
[2]:
com.akiban.sql.parser.ResultColumn@9273085
exposedName: evt_name
name: evt_name
tableName: null
isDefaultColumn: false
type: null
expression:
com.akiban.sql.parser.ColumnReference@6be27074
columnName: evt_name
tableName: d_event
type: null
[3]:
com.akiban.sql.parser.ResultColumn@5f450701
exposedName: evt_is_security
name: evt_is_security
tableName: null
isDefaultColumn: false
type: null
expression:
com.akiban.sql.parser.ColumnReference@2ccf0384
columnName: evt_is_security
tableName: d_event
type: null
[4]:
com.akiban.sql.parser.ResultColumn@4aefe617
exposedName: tro_uid
name: tro_uid
tableName: null
isDefaultColumn: false
type: null
expression:
com.akiban.sql.parser.ColumnReference@4beb1670
columnName: tro_uid
tableName: tro_object
type: null
[5]:
com.akiban.sql.parser.ResultColumn@370e3a00
exposedName: sta_name
name: sta_name
tableName: null
isDefaultColumn: false
type: null
expression:
com.akiban.sql.parser.ColumnReference@701f23d
columnName: sta_name
tableName: d_tro_state
type: null
[6]:
com.akiban.sql.parser.ResultColumn@10c68663
exposedName: tre_date
name: tre_date
tableName: null
isDefaultColumn: false
type: null
expression:
com.akiban.sql.parser.ColumnReference@59a0d62f
columnName: tre_date
tableName: tro_event
type: null
[7]:
com.akiban.sql.parser.ResultColumn@4e4dd5a1
exposedName: tre_msg
name: tre_msg
tableName: null
isDefaultColumn: false
type: null
expression:
com.akiban.sql.parser.ColumnReference@58046530
columnName: tre_msg
tableName: tro_event
type: null
fromList:
com.akiban.sql.parser.FromList@88f5f75

[0]:
com.akiban.sql.parser.FromBaseTable@7cc3feb6
tableName: tro_event
updateOrDelete: null
null
correlation Name: null
null
[1]:
com.akiban.sql.parser.FromBaseTable@27879eb8
tableName: d_tro_state
updateOrDelete: null
null
correlation Name: null
null
[2]:
com.akiban.sql.parser.FromBaseTable@38c5564e
tableName: tro_object
updateOrDelete: null
null
correlation Name: null
null
[3]:
com.akiban.sql.parser.FromBaseTable@23a53800
tableName: d_event
updateOrDelete: null
null
correlation Name: null
null
whereClause:
com.akiban.sql.parser.AndNode@33ff9a48
operator: and
methodName: and
type: null
leftOperand:
com.akiban.sql.parser.AndNode@51ea07a3
operator: and
methodName: and
type: null
leftOperand:
com.akiban.sql.parser.AndNode@5b9b8858
operator: and
methodName: and
type: null
leftOperand:

com.akiban.sql.parser.BinaryRelationalOperatorNode@40147864
operator: =
methodName: equals
type: null
leftOperand:
com.akiban.sql.parser.ColumnReference@7fec0e19
columnName: sta_id
tableName: d_tro_state
type: null
rightOperand:
com.akiban.sql.parser.ColumnReference@6291cceb
columnName: sta_id
tableName: tro_object
type: null
rightOperand:

com.akiban.sql.parser.BinaryRelationalOperatorNode@52248adb
operator: =
methodName: equals
type: null
leftOperand:
com.akiban.sql.parser.ColumnReference@5d1859fe
columnName: tro_id
tableName: tro_object
type: null
rightOperand:
com.akiban.sql.parser.ColumnReference@69bc6271
columnName: tro_id
tableName: tro_event
type: null
rightOperand:
com.akiban.sql.parser.BinaryRelationalOperatorNode@4ee31ef2
operator: =
methodName: equals
type: null
leftOperand:
com.akiban.sql.parser.ColumnReference@2104ca54
columnName: evt_id
tableName: d_event
type: null
rightOperand:
com.akiban.sql.parser.ColumnReference@417f61bb
columnName: evt_id
tableName: tro_event
type: null
rightOperand:
com.akiban.sql.parser.BinaryRelationalOperatorNode@11e95d95
operator: =
methodName: equals
type: null
leftOperand:
com.akiban.sql.parser.ColumnReference@6ffeea62
columnName: tro_id
tableName: tro_object
type: null
rightOperand:
com.akiban.sql.parser.ParameterNode@48ce015f
number: 0
type: null
orderByList:
com.akiban.sql.parser.OrderByList@4cbc2950
allAscending: true
[0]:
com.akiban.sql.parser.OrderByColumn@553c6a8b
columnPosition: -1
expression:
com.akiban.sql.parser.ColumnReference@7176f764
columnName: evt_id
tableName: d_event
type: null
fetchFirst:
com.akiban.sql.parser.NumericConstantNode@396c006e
value: 100
type: INTEGER NOT NULL

and finally I unparsed node to the sql query:
SELECT tro_event.tre_id AS tre_id, d_event.evt_code AS evt_code,
d_event.evt_name AS evt_name, d_event.evt_is_security AS
evt_is_security, tro_object.tro_uid AS tro_uid, d_tro_state.sta_name AS
sta_name, tro_event.tre_date AS tre_date, tro_event.tre_msg AS tre_msg
FROM tro_event, d_tro_state, tro_object, d_event WHERE
(((d_tro_state.sta_id = tro_object.sta_id) AND (tro_object.tro_id =
tro_event.tro_id)) AND (d_event.evt_id = tro_event.evt_id)) AND
(tro_object.tro_id = $1) ORDER BY d_event.evt_id

Mikhail

24.05.2013 13:00, Nathan Williams пишет:

Nathan Williams

unread,
May 24, 2013, 1:15:14 PM5/24/13
to akiba...@akiban.com, Mikhail Khodonov
On Friday, May 24, 2013 at 12:25 AM, Mikhail Khodonov wrote:
> It seems something wrong with parser 1.0.45 to parse LIMIT clause.

The latest release is 1.0.15, was 45 a typo or are you using a forked version?

> Tree output, as you can see the LIMIT node disappeared:
> com.akiban.sql.parser.CursorNode@4e8044a2
> name: null
> updateMode: UNSPECIFIED
> statementType: SELECT
> resultSet:
> com.akiban.sql.parser.SelectNode@471a0ff1
> isDistinct: false
> resultColumns:
> com.akiban.sql.parser.ResultColumnList@cbbaf

The root CursorNode has a 'ValueNode fetchFirst' data member, which is where the LIMIT clause ends up in the tree for this particularly query. That doesn't get printed when using NodeToString.

It is available from the getFetchFirstClause() method directly and is also encountered when using a Visitor through accept()/acceptChildren(). You could also see it if you using the treePrint() method, instead of NodeToString, as well.

Thanks for pointing this out. I've filed an enhancement bug to make sure the LIMIT value gets included in the NodeToString output if appropriate:
https://bugs.launchpad.net/akiban-sql-parser/+bug/1183917

-Nathan



Mikhail Khodonov

unread,
May 24, 2013, 9:46:53 PM5/24/13
to Nathan Williams, akiba...@akiban.com
well, if so, could you please suggest a workaround to unparsed node and
not to lose LIMIT clause?

I am trying to use sql parser for my task:
1. parse input sql query
2. find select node in tree
3. find where clause (value node) in select node
4. add additional condition to found value node
5. unparsed modified tree
how should I do unparse and not to lose limit clause?

Nathan, which sql standard sql parser supports?

Mikhail


25.05.2013 02:15, Nathan Williams пишет:

Nathan Williams

unread,
May 28, 2013, 4:51:25 PM5/28/13
to akiba...@akiban.com, Mikhail Khodonov
On Friday, May 24, 2013 at 9:46 PM, Mikhail Khodonov wrote:
well, if so, could you please suggest a workaround to unparsed node and
not to lose LIMIT clause?
There is no way to get it to into the NodeToString output at the moment. The bug I linked in the last message is tracking that enhancement. 
Nathan, which sql standard sql parser supports?
It is mostly SQL92, with a dashes of later standards and other system compatibility thrown in for good measure.

-Nathan

nik...@gmail.com

unread,
Mar 19, 2014, 9:17:51 AM3/19/14
to akiba...@akiban.com
Hello Sir,
Can anyone point me to the documentaion of this akiban sql parser

Nathan Williams

unread,
Mar 19, 2014, 3:00:25 PM3/19/14
to nik...@gmail.com, akiba...@akiban.com
On Wednesday, March 19, 2014 at 9:17 AM, nik...@gmail.com wrote:
Hello Sir,
Can anyone point me to the documentaion of this akiban sql parser

Hi There,

The project was renamed to FoundationDB SQL Parser and is hosted on GitHub:

There are instructions there about installing, usage, etc.

The Javadocs are also browsable online:

-Nathan

Nikesh shetty

unread,
Mar 19, 2014, 10:56:41 PM3/19/14
to Nathan Williams, akiba...@akiban.com
Thanks a lot

Nikesh shetty

unread,
Mar 19, 2014, 11:22:38 PM3/19/14
to Nathan Williams, akiba...@akiban.com
Reply all
Reply to author
Forward
0 new messages