JJTree/Javacc - SQL Grammar Parser to expose "sql parts"

[Dan Rollo]

I'm a new user of javacc and jjtree, so please let me know if I'm doing things wrong.

I'm using a modified version of the PLSql Grammar (made it into a .jjt jjtree file).
My goal is to have the parse accept some very complex "select" statements, and after parsing, allow me to get the various "parts" of the sql. For example, getSelectPart(), getFromPart(), getWherePart(), etc.

I have the parsing successfully generating an object graph of SimpleNodes, but I can't figure out the cleanest way to preserve the "value" (the matched string) of each Node. My first attempt used a bunch of:
{ jjtThis.setText(token.image); } statements to the .jjt file (but as the dump below shows, I don't think that's the correct approach).

I also looked at calling the *TokenManager directly (output from this at end of email), but again, I don't think this gets me to an implementation of getSelect/From/Where...Part().

Any suggestions?

In case it helps, here's the output of  a dump using the sql:
"select * from \n"
            + "(SELECT CHAIR FROM FURNITURE WHERE LEGS = 4)\n"
            + " where dog = 'cat'"

Object Graph:

 SequenceOfStatements ['cat']
  PLSQLStatement ['cat']
   SQLStatement ['cat']
    QueryStatement ['cat']
     SelectStatement ['cat']
      SelectWithoutOrder ['cat']
       SelectSet ['cat']
        Select ['cat']
         SelectList [*]
         FromClause [)]
          TableReference [)]
           QueryTableExpression [)]
            SubQuery [4]
             SelectStatement [4]
              SelectWithoutOrder [4]
               SelectSet [4]
                Select [4]
                 SelectList [CHAIR]
                  SelectItem [CHAIR]
                   SQLSimpleExpression [CHAIR]
                    SQLMultiplicativeExpression [CHAIR]
                     SQLExponentExpression [CHAIR]
                      SQLUnaryExpression [CHAIR]
                       SQLPrimaryExpression [null]
                        TableColumn [CHAIR]
                         ObjectReference [CHAIR]
                          OracleObjectName [CHAIR]
                 FromClause [FURNITURE]
                  TableReference [FURNITURE]
                   QueryTableExpression [FURNITURE]
                    TableName [FURNITURE]
                     TableObjectName [FURNITURE]
                 WhereClause [4]
                  SQLExpression [4]
                   SQLAndExpression [4]
                    SQLUnaryLogicalExpression [4]
                     SQLRelationalExpression [4]
                      SQLSimpleExpression [LEGS]
                       SQLMultiplicativeExpression [LEGS]
                        SQLExponentExpression [LEGS]
                         SQLUnaryExpression [LEGS]
                          SQLPrimaryExpression [null]
                           TableColumn [LEGS]
                            ObjectReference [LEGS]
                             OracleObjectName [LEGS]
                      SQLRelationalOperatorExpression [4]
                       Relop [=]
                       SQLSimpleExpression [4]
                        SQLMultiplicativeExpression [4]
                         SQLExponentExpression [4]
                          SQLUnaryExpression [4]
                           SQLPrimaryExpression [4]
         WhereClause ['cat']
          SQLExpression ['cat']
           SQLAndExpression ['cat']
            SQLUnaryLogicalExpression ['cat']
             SQLRelationalExpression ['cat']
              SQLSimpleExpression [dog]
               SQLMultiplicativeExpression [dog]
                SQLExponentExpression [dog]
                 SQLUnaryExpression [dog]
                  SQLPrimaryExpression [null]
                   TableColumn [dog]
                    ObjectReference [dog]
                     OracleObjectName [dog]
              SQLRelationalOperatorExpression ['cat']
               Relop [=]
               SQLSimpleExpression ['cat']
                SQLMultiplicativeExpression ['cat']
                 SQLExponentExpression ['cat']
                  SQLUnaryExpression ['cat']
                   SQLPrimaryExpression ['cat']
Select ['cat']
 SelectList [*]
 FromClause [)]
  TableReference [)]
   QueryTableExpression [)]
    SubQuery [4]
     SelectStatement [4]
      SelectWithoutOrder [4]
       SelectSet [4]
        Select [4]
         SelectList [CHAIR]
          SelectItem [CHAIR]
           SQLSimpleExpression [CHAIR]
            SQLMultiplicativeExpression [CHAIR]
             SQLExponentExpression [CHAIR]
              SQLUnaryExpression [CHAIR]
               SQLPrimaryExpression [null]
                TableColumn [CHAIR]
                 ObjectReference [CHAIR]
                  OracleObjectName [CHAIR]
         FromClause [FURNITURE]
          TableReference [FURNITURE]
           QueryTableExpression [FURNITURE]
            TableName [FURNITURE]
             TableObjectName [FURNITURE]
         WhereClause [4]
          SQLExpression [4]
           SQLAndExpression [4]
            SQLUnaryLogicalExpression [4]
             SQLRelationalExpression [4]
              SQLSimpleExpression [LEGS]
               SQLMultiplicativeExpression [LEGS]
                SQLExponentExpression [LEGS]
                 SQLUnaryExpression [LEGS]
                  SQLPrimaryExpression [null]
                   TableColumn [LEGS]
                    ObjectReference [LEGS]
                     OracleObjectName [LEGS]
              SQLRelationalOperatorExpression [4]
               Relop [=]
               SQLSimpleExpression [4]
                SQLMultiplicativeExpression [4]
                 SQLExponentExpression [4]
                  SQLUnaryExpression [4]
                   SQLPrimaryExpression [4]
 WhereClause ['cat']
  SQLExpression ['cat']
   SQLAndExpression ['cat']
    SQLUnaryLogicalExpression ['cat']
     SQLRelationalExpression ['cat']
      SQLSimpleExpression [dog]
       SQLMultiplicativeExpression [dog]
        SQLExponentExpression [dog]
         SQLUnaryExpression [dog]
          SQLPrimaryExpression [null]
           TableColumn [dog]
            ObjectReference [dog]
             OracleObjectName [dog]
      SQLRelationalOperatorExpression ['cat']
       Relop [=]
       SQLSimpleExpression ['cat']
        SQLMultiplicativeExpression ['cat']
         SQLExponentExpression ['cat']
          SQLUnaryExpression ['cat']
           SQLPrimaryExpression ['cat']


TokenManager output:

token 00 (kind: 110): select
token 01 (kind: 167): *
token 02 (kind: 052): from
token 03 (kind: 155): (
token 04 (kind: 110): SELECT
token 05 (kind: 145): CHAIR
token 06 (kind: 052): FROM
token 07 (kind: 145): FURNITURE
token 08 (kind: 134): WHERE
token 09 (kind: 145): LEGS
token 10 (kind: 153): =
token 11 (kind: 139): 4
token 12 (kind: 156): )
token 13 (kind: 134): where
token 14 (kind: 145): dog
token 15 (kind: 153): =
token 16 (kind: 149): 'cat'


Thanks,
Dan Rollo

[J.Chris Findlay]

A common approach is to have each node "remember" it's first and last
tokens, and have it's "text" be drawn from the token-stream between
(and including) those two endpoints.
One thing to note is that if you just keep pointers to the tokens, you
end up keeping the entire token-stream in memory.  It might suit you
better to extract the concatenated strings as you collect the
end-token instead.  Also, you may only want to do that for certain
node types anyway.
I recall somewhere in the jjtree examples there was an example of
something like this...

On Thu, Sep 4, 2008 at 10:04 AM, Dan Rollo <bha@...> wrote:

> I'm a new user of javacc and jjtree, so please let me know if I'm doing
> things wrong.
>
> I'm using a modified version of the PLSql Grammar (made it into a .jjt
> jjtree file).
> My goal is to have the parse accept some very complex "select" statements,
> and after parsing, allow me to get the various "parts" of the sql. For
> example, getSelectPart(), getFromPart(), getWherePart(), etc.
>
> I have the parsing successfully generating an object graph of SimpleNodes,
> but I can't figure out the cleanest way to preserve the "value" (the matched
> string) of each Node. My first attempt used a bunch of:
> { jjtThis.setText(token.image); } statements to the .jjt file (but as the
> dump below shows, I don't think that's the correct approach).
>
> I also looked at calling the *TokenManager directly (output from this at end
> of email), but again, I don't think this gets me to an implementation of
> getSelect/From/Where...Part().
>
> Any suggestions?
>
> In case it helps, here's the output of  a dump using the sql:
> "select * from \n"
>             + "(SELECT CHAIR FROM FURNITURE WHERE LEGS = 4)\n"
>             + " where dog = 'cat'"
>
> Object Graph:
>
>  SequenceOfStatements ['cat']
>   PLSQLStatement ['cat']
>    SQLStatement ['cat']
>     QueryStatement ['cat']
>      SelectStatement ['cat']
>       SelectWithoutOrder ['cat']
>        SelectSet ['cat']
>         Select ['cat']
>          SelectList [*]
>          FromClause [)]
>           TableReference [)]
>            QueryTableExpression [)]
>             SubQuery [4]
>              SelectStatement [4]
>               SelectWithoutOrder [4]
>                SelectSet [4]
>                 Select [4]
>                  SelectList [CHAIR]
>                   SelectItem [CHAIR]
>                    SQLSimpleExpression [CHAIR]
>                     SQLMultiplicativeExpression [CHAIR]
>                      SQLExponentExpression [CHAIR]
>                       SQLUnaryExpression [CHAIR]
>                        SQLPrimaryExpression [null]
>                         TableColumn [CHAIR]
>                          ObjectReference [CHAIR]
>                           OracleObjectName [CHAIR]
>                  FromClause [FURNITURE]
>                   TableReference [FURNITURE]
>                    QueryTableExpression [FURNITURE]
>                     TableName [FURNITURE]
>                      TableObjectName [FURNITURE]
>                  WhereClause [4]
>                   SQLExpression [4]
>                    SQLAndExpression [4]
>                     SQLUnaryLogicalExpression [4]
>                      SQLRelationalExpression [4]
>                       SQLSimpleExpression [LEGS]
>                        SQLMultiplicativeExpression [LEGS]
>                         SQLExponentExpression [LEGS]
>                          SQLUnaryExpression [LEGS]
>                           SQLPrimaryExpression [null]
>                            TableColumn [LEGS]
>                             ObjectReference [LEGS]
>                              OracleObjectName [LEGS]
>                       SQLRelationalOperatorExpression [4]
>                        Relop [=]
>                        SQLSimpleExpression [4]
>                         SQLMultiplicativeExpression [4]
>                          SQLExponentExpression [4]
>                           SQLUnaryExpression [4]
>                            SQLPrimaryExpression [4]
>          WhereClause ['cat']
>           SQLExpression ['cat']
>            SQLAndExpression ['cat']
>             SQLUnaryLogicalExpression ['cat']
>              SQLRelationalExpression ['cat']
>               SQLSimpleExpression [dog]
>                SQLMultiplicativeExpression [dog]
>                 SQLExponentExpression [dog]
>                  SQLUnaryExpression [dog]
>                   SQLPrimaryExpression [null]
>                    TableColumn [dog]
>                     ObjectReference [dog]
>                      OracleObjectName [dog]
>               SQLRelationalOperatorExpression ['cat']
>                Relop [=]
>                SQLSimpleExpression ['cat']
>                 SQLMultiplicativeExpression ['cat']
>                  SQLExponentExpression ['cat']
>                   SQLUnaryExpression ['cat']
>                    SQLPrimaryExpression ['cat']
> Select ['cat']
>  SelectList [*]
>  FromClause [)]
>   TableReference [)]
>    QueryTableExpression [)]
>     SubQuery [4]
>      SelectStatement [4]
>       SelectWithoutOrder [4]
>        SelectSet [4]
>         Select [4]
>          SelectList [CHAIR]
>           SelectItem [CHAIR]
>            SQLSimpleExpression [CHAIR]
>             SQLMultiplicativeExpression [CHAIR]
>              SQLExponentExpression [CHAIR]
>               SQLUnaryExpression [CHAIR]
>                SQLPrimaryExpression [null]
>                 TableColumn [CHAIR]
>                  ObjectReference [CHAIR]
>                   OracleObjectName [CHAIR]
>          FromClause [FURNITURE]
>           TableReference [FURNITURE]
>            QueryTableExpression [FURNITURE]
>             TableName [FURNITURE]
>              TableObjectName [FURNITURE]
>          WhereClause [4]
>           SQLExpression [4]
>            SQLAndExpression [4]
>             SQLUnaryLogicalExpression [4]
>              SQLRelationalExpression [4]
>               SQLSimpleExpression [LEGS]
>                SQLMultiplicativeExpression [LEGS]
>                 SQLExponentExpression [LEGS]
>                  SQLUnaryExpression [LEGS]
>                   SQLPrimaryExpression [null]
>                    TableColumn [LEGS]
>                     ObjectReference [LEGS]
>                      OracleObjectName [LEGS]
>               SQLRelationalOperatorExpression [4]
>                Relop [=]
>                SQLSimpleExpression [4]
>                 SQLMultiplicativeExpression [4]
>                  SQLExponentExpression [4]
>                   SQLUnaryExpression [4]
>                    SQLPrimaryExpression [4]
>  WhereClause ['cat']
>   SQLExpression ['cat']
>    SQLAndExpression ['cat']
>     SQLUnaryLogicalExpression ['cat']
>      SQLRelationalExpression ['cat']
>       SQLSimpleExpression [dog]
>        SQLMultiplicativeExpression [dog]
>         SQLExponentExpression [dog]
>          SQLUnaryExpression [dog]
>           SQLPrimaryExpression [null]
>            TableColumn [dog]
>             ObjectReference [dog]
>              OracleObjectName [dog]
>       SQLRelationalOperatorExpression ['cat']
>        Relop [=]
>        SQLSimpleExpression ['cat']
>         SQLMultiplicativeExpression ['cat']
>          SQLExponentExpression ['cat']
>           SQLUnaryExpression ['cat']
>            SQLPrimaryExpression ['cat']
>
>
> TokenManager output:
>
> token 00 (kind: 110): select
> token 01 (kind: 167): *
> token 02 (kind: 052): from
> token 03 (kind: 155): (
> token 04 (kind: 110): SELECT
> token 05 (kind: 145): CHAIR
> token 06 (kind: 052): FROM
> token 07 (kind: 145): FURNITURE
> token 08 (kind: 134): WHERE
> token 09 (kind: 145): LEGS
> token 10 (kind: 153): =
> token 11 (kind: 139): 4
> token 12 (kind: 156): )
> token 13 (kind: 134): where
> token 14 (kind: 145): dog
> token 15 (kind: 153): =
> token 16 (kind: 149): 'cat'
>
>
> Thanks,
> Dan Rollo
>

--
 - J.Chris Findlay
 (c:

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Dan Rollo]

That sounds like what I'm after. So far, I have not been holding on to the actual "token", but just stuffing token.image into a custom "text" (via jjtThis.setText(token.image)), so I think I'm avoiding holding the whole token-stream. For example (from my PlSql.jjt):

void SQLSimpleExpression():
{}
{
    SQLMultiplicativeExpression() ( ("+" | "-" | "||") SQLMultiplicativeExpression())*

    { jjtThis.setText(token.image); } // support getText
}

Unfortunatley, the above is not grabing the text I'm after.
That said, I'll try to find what you described in the examples. (Please holler if anyone knows exactly where to look). Hopefully I can avoid holding actual token objects, and figure out how to grab the string betwixt first and last token (assuming I figure out how to determine the first and last token. ;)

Thanks!

 

-------------- Original message from "J.Chris Findlay" <j.chris.findlay@...>: --------------

> A common approach is to have each node "remember" it's first and last
> tokens, and have it's "text" be drawn from the token-stream between
> (and including) those two endpoints.
> One thing to note is that if you just keep pointers to the tokens, you
> end up keeping the entire token-stream in memory. It might suit you
> better to extract the concatenated strings as you collect the
> end-token instead. Also, you may only want to do that for certain
> node types anyway.
> I recall somewhere in the jjtree examples there was an example of
> something like this...
>
> On Thu, Sep 4, 2008 at 10:04 AM, Dan Rollo wrote:
> > I'm a new user of javacc and jjtree, so please let me know if I'm doing
> > things wrong.
> >
> > I'm using a modified version of the PLSql Grammar (made it into a .jjt
> > jjtree file).
> > My goal is to have the parse accept some very complex "select" statements,
> > and after parsing, allow me to get the various "parts" of the sql. For
> > example, getSelectPart(), getFromPart(), getWherePart(), etc.
> >
> > I have the parsing successfully generating an object graph of SimpleNodes,
> > but I can't figure out the cleanest way to preserve the "value" (the matched
> > string) of each Node. My first attempt used a bunch of:
> > { jjtThis.setText(token.image); } statements to the .jjt file (but as the
> > dump below shows, I don't think that's the correct approach).
> >
> > I also looked at calling the *TokenManager directly (output from this at end
> > of email), but again, I don't think this gets me to an implementation of
> > getSelect/From/Where...Part().
> >
> > Any suggestions?
> >
> > In case it helps, here's the output of a dump using the sql:
> > "select * from \n"
> > + "(SELECT CHAIR FROM FURNITURE WHERE LEGS = 4)\n"
> > + " where dog = 'cat'"
> >
> > Object Graph:
> >
> > SequenceOfStatements ['cat']
> > PLSQLStatement ['cat']
> > SQLStatement ['cat']
> > QueryStatement ['cat']
> > SelectStatement ['cat']
> > SelectWithoutOrder ['cat']
> > SelectSet ['cat']
> > Select ['cat']
> > SelectList [*]
> > FromClause [)]
> > TableReference [)]
> > QueryTableExpression [)]
> > SubQuery [4]
> > SelectStatement [4]
> > SelectWithoutOrder [4]
> > SelectSet [4]
> > Select [4]
> > SelectList [CHAIR]
> > SelectItem [CHAIR]
> > SQLSimpleExpression [CHAIR]
> > SQLMultiplicativeExpression [CHAIR]
> > SQLExponentExpression [CHAIR]
> > SQLUnaryExpression [CHAIR]
> > SQLPrimaryExpression [null]
> > TableColumn [CHAIR]
> > ObjectReference [CHAIR]
> > OracleObjectName [CHAIR]
> > FromClause [FURNITURE]
> > TableReference [FURNITURE]
> > QueryTableExpression [FURNITURE]
> > TableName [FURNITURE]
> > TableObjectName [FURNITURE]
> > WhereClause [4]
> > SQLExpression [4]
> > SQLAndExpression [4]
> > SQLUnaryLogicalExpression [4]
> > SQLRelationalExpression [4]
> > SQLSimpleExpression [LEGS]
> > SQLMultiplicativeExpression [LEGS]
> > SQLExponentExpression [LEGS]
> > SQLUnaryExpression [LEGS]
> > SQLPrimaryExpression [null]
> > TableColumn [LEGS]
> > ObjectReference [LEGS]
> > OracleObjectName [LEGS]
> > SQLRelationalOperatorExpression [4]
> > Relop [=]
> > SQLSimpleExpression [4]
> > SQLMultiplicativeExpression [4]
> > SQLExponentExpression [4]
> > SQLUnaryExpression [4]
> > SQLPrimaryExpression [4]
> > WhereClause ['cat']
> > SQLExpression ['cat']
> > SQLAndExpression ['cat']
> > SQLUnaryLogicalExpression ['cat']
> > SQLRelationalExpression ['cat']
> > SQLSimpleExpression [dog]
> > SQLMultiplicativeExpression [dog]
> > SQLExponentExpression [dog]
> > SQLUnaryExpression [dog]
> > SQLPrimaryExpression [null]
> > TableColumn [dog]
> > ObjectReference [dog]
> > OracleObjectName [dog]
> > SQLRelationalOperatorExpression ['cat']
> > Relop [=]
> > SQLSimpleExpression ['cat']
> > SQLMultiplicativeExpression ['cat']
> > SQLExponentExpression ['cat']
> > SQLUnaryExpression ['cat']
> > SQLPrimaryExpression ['cat']
> > Select ['cat']
> > SelectList [*]
> > FromClause [)]
> > TableReference [)]
> > QueryTableExpression [)]
> > SubQuery [4]
> > SelectStatement [4]
> > SelectWithoutOrder [4]
> > SelectSet [4]
> > Select [4]
> > SelectList [CHAIR]
> > SelectItem [CHAIR]
> > SQLSimpleExpression [CHAIR]
> > SQLMultiplicativeExpression [CHAIR]
> > SQLExponentExpression [CHAIR]
> > SQLUnaryExpression [CHAIR]
> > SQLPrimaryExpression [null]
> > TableColumn [CHAIR]
> > ObjectReference [CHAIR]
> > OracleObjectName [CHAIR]
> > FromClause [FURNITURE]
> > TableReference [FURNITURE]
> > QueryTableExpression [FURNITURE]
> > TableName [FURNITURE]
> > TableObjectName [FURNITURE]
> > WhereClause [4]
> > SQLExpression [4]
> > SQLAndExpression [4]
> > SQLUnaryLogicalExpression [4]
> > SQLRelationalExpression [4]
> > SQLSimpleExpression [LEGS]
> > SQLMultiplicativeExpression [LEGS]
> > SQLExponentExpression [LEGS]
> > SQLUnaryExpression [LEGS]
> > SQLPrimaryExpression [null]
> > TableColumn [LEGS]
> > ObjectReference [LEGS]
> > OracleObjectName [LEGS]
> > SQLRelationalOperatorExpression [4]
> > Relop [=]
> > SQLSimpleExpression [4]
> > SQLMultiplicativeExpression [4]
> > SQLExponentExpression [4]
> > SQLUnaryExpression [4]
> > SQLPrimaryExpression [4]
> > WhereClause ['cat']
> > SQLExpression ['cat']
> > SQLAndExpression ['cat']
> > SQLUnaryLogicalExpression ['cat']
> > SQLRelationalExpression ['cat']
> > SQLSimpleExpression [dog]
> > SQLMultiplicativeExpression [dog]
> > SQLExponentExpression [dog]
> > SQLUnaryExpression [dog]
> > SQLPrimaryExpression [null]
> > TableColumn [dog]
> > ObjectReference [dog]
> > OracleObjectName [dog]
> > SQLRelationalOperatorExpression ['cat']
> > Relop [=]
> > SQLSimpleExpression ['cat']
> > SQLMultiplicativeExpression ['cat']
> > SQLExponentExpression ['cat']
> > SQLUnaryExpression ['cat']
> > SQLPrimaryExpression ['cat']
> >
> >
> > TokenManager output:
> >
> > token 00 (kind: 110): select
> > token 01 (kind: 167): *
> > token 02 (kind: 052): from
> > token 03 (kind: 155): (
> > token 04 (kind: 110): SELECT
> > token 05 (kind: 145): CHAIR
> > token 06 (kind: 052): FROM
> > token 07 (kind: 145): FURNITURE
> > token 08 (kind: 134): WHERE
> > token 09 (kind: 145): LEGS
> > token 10 (kind: 153): =
> > token 11 (kind: 139): 4
> > token 12 (kind: 156): )
> > token 13 (kind: 134): where
> > token 14 (kind: 145): dog
> > token 15 (kind: 153): =
> > token 16 (kind: 149): 'cat'
> >
> >
> > Thanks,
> > Dan Rollo
> >
>
>
>
> --
> - J.Chris Findlay
> (c:
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...
>