3 ms·

Where is the SQLite test suite, please? I'd be very interested. There are already SQL grammars, check https://github.com/antlr/grammars-v4 https://github.com/a

Where is the SQLite test suite, please? I'd be very interested. There are already SQL grammars, check https://github.com/antlr/grammars-v4 https://github.com/antlr/grammars-v4 specifically in here I think https://github.com/antlr/grammars-v4/tree/master/sql https://github.com/antlr/grammars-v4/tree/master/sql I contributed to one of them, and I wrote my own for some personal work. Be warned, it's very involved, very complex and MSSQL is rather ill-defined. Names bracket identifiers) in SQL are bloody awful. Sometimes square brackets are even compulsory, and why you can usually replace [...] with the SQL standard "..." , not always! Trust me, it gets worse. I don't find antlr grammars to be brittle, and while they can lose in performance (by how much I don't know, perhaps quite considerably) they are very easy to maintain and I am very fortunate to have antlr to work with. Edit: unless you are very familiar with the SQL and also a masochist, please don't try and write your own grammar. For someone with my background it was very time-consuming, without that background it will be a nightmare. Friendly warning.

specialist 3y ago
> Where is the SQLite test suite Here's the scrapped tests: https://github.com/bkiers/sqlite-parser/tree/master/src/test/resources https://github.com/bkiers/sqlite-parser/tree/master/src/test... I didn't find a scrapper. I was cobbling together my own scrapper when I stumbled onto these files. My scrapper is turrible. This SQLite grammar is included in grammars-v4. Looks like a copy vs a fork. I don't know why these tests weren't copied too. > please don't try and write your own grammar I must be a masochist. Aside: Ages ago, during an job interview, when asked to talk about some of my prior work, I mentioned my SQL grammar. The self-described "bar raiser" cut me off, dismissed my effort as trivial, because the grammar generator (ANTLR) does all the work. I had no response.
- _a_a_a_ 3y ago
  A, thanks for these tests! That will be really useful for my work. A couple of thoughts: what do you mean by a 'scrapper'? At first I thought you meant scraper, but I don't think it's that. I haven't come across the term before in this context, can you explain please. I'm a bit concerned about the times reported for your parsing. It's unfortunately well-known that the python output of antlr is agonisingly slow – what is your output language? (I don't think that's your problem, but if it is then switching to Java output will fix this immediately).
  - specialist 3y ago
    Oof. Yes, I meant scraper. > ...concerned about the times reported Agreed. FWIW... My wall time for this beast (randtest1) is ~55ms. SELECT coalesce((select max(11- -19-f-t1.b+a) from t1 where exists(select 1 from t1 where 11-~(d)-c*a*~t1.a-t1.e-t1.e+coalesce((select coalesce((select t1.c from t1 where case (c) when d then e else 11 end=t1.f),t1.d) from t1 where (t1.d)>b), -11) | f+t1.f not in (((c)),b,13))),f) FROM t1 WHERE case when 19+c>=t1.a then t1.c when not case when not exists(select 1 from t1 where +f | b*b*19+19*13-a | case when t1.e not in (t1.f,t1.c,b) then 11 when 17>t1.c then a else e end<>e) then b when 17=t1.e then b else e end<>t1.b then a else d end-t1.b=(13) Whereas simple statements like these are < 20ms each. select ((select 1) union (select 1)); select a between b and c and d; I need to add profiling metrics to the build, to (proactively) catch performance regressions. It's fairly easy for ambiguity to sneak in. Mostly because I'm noob. But also because I'm stubbornly using left-recursion and I'm still navigating ANTLR's magical rule rewriting. > ...python output of antlr is agonisingly slow Still just using Java. Edit: Now I'm curious. I'll try ANTLR's Python runtime asap. My tool is a language translation widget. Input SQL and output templates (vs something like an ORM). In the future, I'd like to support dynamic languages (stacks) like Python and nodejs/deno. One, just to be a good citizen ("when in Rome..."). But also to try some half-baked notions for supporting dynamic SQL at runtime. Which isn't (yet?) feasible with a compile-time stack like Java. Thanks for your interest. I put my email in my HN profile. I can send you updates on my work. And once I get to a "beta" release (usable by other people), I intend to have weekly "office hours" on Zoom (or some such).

specialist 3y ago

> Where is the SQLite test suite Here's the scrapped tests: https://github.com/bkiers/sqlite-parser/tree/master/src/test/resources https://github.com/bkiers/sqlite-parser/tree/master/src/test... I didn't find a scrapper. I was cobbling together my own scrapper when I stumbled onto these files. My scrapper is turrible. This SQLite grammar is included in grammars-v4. Looks like a copy vs a fork. I don't know why these tests weren't copied too. > please don't try and write your own grammar I must be a masochist. Aside: Ages ago, during an job interview, when asked to talk about some of my prior work, I mentioned my SQL grammar. The self-described "bar raiser" cut me off, dismissed my effort as trivial, because the grammar generator (ANTLR) does all the work. I had no response.

_a_a_a_ 3y ago
A, thanks for these tests! That will be really useful for my work. A couple of thoughts: what do you mean by a 'scrapper'? At first I thought you meant scraper, but I don't think it's that. I haven't come across the term before in this context, can you explain please. I'm a bit concerned about the times reported for your parsing. It's unfortunately well-known that the python output of antlr is agonisingly slow – what is your output language? (I don't think that's your problem, but if it is then switching to Java output will fix this immediately).
- specialist 3y ago
  Oof. Yes, I meant scraper. > ...concerned about the times reported Agreed. FWIW... My wall time for this beast (randtest1) is ~55ms. SELECT coalesce((select max(11- -19-f-t1.b+a) from t1 where exists(select 1 from t1 where 11-~(d)-c*a*~t1.a-t1.e-t1.e+coalesce((select coalesce((select t1.c from t1 where case (c) when d then e else 11 end=t1.f),t1.d) from t1 where (t1.d)>b), -11) | f+t1.f not in (((c)),b,13))),f) FROM t1 WHERE case when 19+c>=t1.a then t1.c when not case when not exists(select 1 from t1 where +f | b*b*19+19*13-a | case when t1.e not in (t1.f,t1.c,b) then 11 when 17>t1.c then a else e end<>e) then b when 17=t1.e then b else e end<>t1.b then a else d end-t1.b=(13) Whereas simple statements like these are < 20ms each. select ((select 1) union (select 1)); select a between b and c and d; I need to add profiling metrics to the build, to (proactively) catch performance regressions. It's fairly easy for ambiguity to sneak in. Mostly because I'm noob. But also because I'm stubbornly using left-recursion and I'm still navigating ANTLR's magical rule rewriting. > ...python output of antlr is agonisingly slow Still just using Java. Edit: Now I'm curious. I'll try ANTLR's Python runtime asap. My tool is a language translation widget. Input SQL and output templates (vs something like an ORM). In the future, I'd like to support dynamic languages (stacks) like Python and nodejs/deno. One, just to be a good citizen ("when in Rome..."). But also to try some half-baked notions for supporting dynamic SQL at runtime. Which isn't (yet?) feasible with a compile-time stack like Java. Thanks for your interest. I put my email in my HN profile. I can send you updates on my work. And once I get to a "beta" release (usable by other people), I intend to have weekly "office hours" on Zoom (or some such).