How to access the H2 parser and AST?

[Andreas Reichel]

Dear All,

just a quick one: We use JSQLParser as a simple parser in order to get information about a query. Works, but of course it is not perfect. Now I wonder two things:

1. can we access the H2 internal Parse (which should know best) and retrieve the Query AST? I did not find anything in the API documentation.

2. is there an ANTLR 4 grammar for H2? (I did not find any)

Cheers
Andreas

[Noel Grandin]

On 2020/10/07 2:37 pm, Andreas Reichel wrote:
>
> can we access the H2 internal Parse (which should know best) and retrieve the Query AST? I did not find anything in
> the API documentation.

No, the internal parser doesn't generate a real AST, it produces something that roughly half-way between an AST and a
query-plan.

> is there an ANTLR 4 grammar for H2? (I did not find any)

No, although the documentation does have a fairly complete set of grammar-like productions.

[Andreas Reichel]

Thank you Noel,

but please let me ask further: How exactly is the SQL syntax and grammar defined/specified for H2? Is there a BNF file somewhere? Or is it all hard-coded in Java only?

I mean, somehow you create your excellent online documentation with the BNF diagrams. 

Cheers

Andreas

[Noel Grandin]

On 2020/10/07 3:54 pm, Andreas Reichel wrote:
> but please let me ask further: How exactly is the SQL syntax and grammar defined/specified for H2? Is there a BNF file
> somewhere? Or is it all hard-coded in Java only?

We use a top-down recursive descent parser, and we have separate documentation files which we pass through a build-time
tool to generate BNF-type diagrams.

[Andreas Reichel]

On Wed, 2020-10-07 at 15:57 +0200, Noel Grandin wrote:

We use a top-down recursive descent parser, and we have separate documentation files which we pass through a build-time 

tool to generate BNF-type diagrams.

This one, right?

          public static Bnf getInstance(Reader csv) throws SQLException, IOException {

        Bnf bnf = new Bnf();

        if (csv == null) {

            byte[] data = Utils.getResource("/org/h2/res/help.csv");

            csv = new InputStreamReader(new ByteArrayInputStream(data));

        }

        bnf.parse(csv);

        return bnf;

    }

Thanks a lot, I appreciate!

Andreas

[Andreas Reichel]

Dear Noel and Team,

compliments! I would like to ask for your kind help please.

My objective is to write an ANTLR V4. grammar for H2 and of course lazy as I am I would like to use the existing BNF from the CSV and because I am very lazy I do not want to parse that myself, but use the H2 BNF parser.

I managed already to extract the H2 commands, which will go into the ANTLR parser definition, e. g.:

SELECT [TOP term [PERCENT] [WITH TIES]] [DISTINCT [ON ( expression [, ...] )] | ALL] selectExpression [, ...] [FROM tableExpression [, ...]] [WHERE expression] [GROUP BY groupingElement [, ...]] [HAVING expression] [WINDOW windowName AS windowSpecification [, ...]] [QUALIFY expression] [UNION [ALL] | EXCEPT | MINUS | INTERSECT query] [ORDER BY selectOrder [, ...]] [OFFSET expression ROW | ROWS] [FETCH FIRST | NEXT [expression [PERCENT]] ROW | ROWS ONLY | WITH TIES] [LIMIT expression [OFFSET expression]] [FOR UPDATE] ;

After some changes to the various Rule Classes, I am also able to extract the fragments for the Lexer, e. g.:

term
expression
selectExpression
tableExpression
groupingElement
windowName
windowSpecification
query
selectOrder

However, I am stuck in getting the definition of these fragments from the BNF classes.

For example, in the Spreadsheet, you have defined TERM as

{ value
    | column
    | ?[ int ]
    | sequenceValueExpression
    | function
    | { - | + } term
    | ( expression )
    | arrayElementReference
    | fieldReference
    | query
    | caseExpression
    | castSpecification
    | userDefinedFunctionName }
[ timeZone | intervalQualifier ]

But it seems like I can't get that definition from BNF directly, but also not from the RuleElement term itself.

So how to get that red definition please?

Also, most of the properties in RuleList, RuleFixed, RuleOptional and RuleElement are private and there are no public getters.

Would you like to consider/accept a patch with getters for these properties? Especially list in RuleList is crucial.

Thank you as always and best regards

Andreas

[Evgenij Ryazanov]

Hello.

1. I suggest you not to use any internals of H2 for your own purposes. But if you have no other choice, you need to write an own implementation of org.h2.bnf.BnfVisitor, see org.h2.build.doc.BnfRailroad and org.h2.build.doc.BnfSyntax for examples.

2. BNF of H2 isn't really usable for machine usage. There are many incorrect links (for example, the most of links to literals should actually be links to expressions of that or any compatible data type, we have an issue about this problem somewhere), BNF of literals itself needs some quirks, some commands may have incomplete or incorrect documentation. Some commands and functions are intentionally not documented, but it doesn't matter, usually they are for compatibility only. Code completion in H2 Console uses that grammar with its own quirks, but it isn't very usable due to various issues, such as missing support for quoted identifiers.