News for hackers. Stuff that matters.
Re:SQLite Internals: How the Most Used Database Works by specialist on Monday December 19, 2022 @08:38AM (#34051856)
> ...than it would be to learn the exact syntax and quirks and possibly bugs of someone else's implementation...

Yup. Also, having deep knowledge of the language is required.

SQLite's grammar is neat. Creating a compatible parser would make a fun project. Here's a pretty good example: https://github.com/bkiers/sqlite-parser (Actual ANTLR 4 grammar: https://github.com/bkiers/sqlite-parser/blob/master/src/main... )

Postgres, which tries to be compliant with the latest standards, however...

SQL-2016 is a beast. Not to mention all the dialects.

I'm updating my personal (soon to be FOSS) grammar from ANTLR 3 LL(k) to ANTLR 4 ALL(Star).

I've long had a working knowledge of SQL-92, with some SQL-1999 (eg common table expressions).

But the new structures and extensions are a bit overwhelming.

Fortunately, ANTLR project has ~dozen FOSS grammars to learn from. https://github.com/antlr/grammars-v4/tree/master/sql

They mostly mechanically translate BNFs to LL(k) with some ALL(Star). Meaning few take advantage of left-recursion. https://github.com/antlr/antlr4/blob/master/doc/left-recursi...

Honestly, I struggled to understand these grammars. Plus, not being conversant with the SQL-2016 was a huge impediment. Just finding a succinct corbis of test cases was a huge hurdle for me.

Fortunately, the H2 Database project is a great resource. https://github.com/h2database/h2database/tree/master/h2/src/...

Now for the exciting conclusion...

My ANTLR grammar which passes all of H2's tests diverges significantly any of the official or product specific BNFs.

Further, I found discrepancy between product specific BNFs and their implementations.

So a lot of trial & error is required for a "real world" parser. Which would explain why the professional SQL parsing tools ask for money.

I still think creating a parser for SQLite is a great project.

Re:SQLite Internals: How the Most Used Database Works by osdotsystem on Monday December 19, 2022 @09:58PM (#34060989)
Thank you for the resources and insights. H2 is a great implementation!