Repository navigation
Help wanted: testing our grammar parser #741
Description
Here's a task for all who want to contribute but don't feel ready to start C++-coding.
Our grammar parser needs some more stress-testing, I think. The grammar parser is the part that tries to understand the CREATE TABLE ... statements and extract all the information on the table. So testing it is as simple as creating a new database, adding a table (via SQL), opening it in the Edit Table dialog and see the SQL code it prints. If there is something missing, that's a bug. Or if you have a database and notice that some existing table isn't fully parsed, it's a bug, too, of course.
If you are more of a systematic person, you can check out the diagrams at https://sqlite.org/lang_createtable.html and try out the various combinations and keywords mentioned in there.
There are some features which we know are missing. These are:
- Named constraints (names for unique/primary key/foreign key constraints)
- Sorted indices (ASC/DESC in primary key/unique constraints)
- The COLLATE keyword and all its uses
- Primary keys with multiple columns in it
But there are certainly more 😃 So I want to encourage everybody who stumbles upon another issue to open a new issue for it.
Also there are some parts that our parser understands (multiple columns in a unique constraint for example) but you aren't able to create or edit using the UI - only using SQL. Every one of these is worth opening an issue, too 😉 Just make it clear whether it's a problem with the parser or the dialog.
Thanks to all helping to make the grammar parsing as complete as possible for the next release 😃
Activity
MarqueIV commentedon Aug 30, 2016
Not sure if this is related, but when modifying an existing table, adding a primary key to it, even after confirming that the data should support a primary key on the selected fields (i.e. no nulls and when running a group-by with a count on the fields, I never get anything but a '1') it just silently fails and doesn't add the key. No warnings, no errors, nothing. The dialog just gets dismissed and nothing actually happens. Same thing when I tried marking those columns as 'not null'.
Since this is just a db of the public NJ Transit schedules (as imported from flat CSV files) I can send it along so you can try it yourself if interested. (It's 20+ mb so I figured I'd ask first. If so, lemme know how you want me to send it to you.)
justinclift commentedon Aug 31, 2016
@MarqueIV With the behaviour you're describing, how recent a build of DB4S is that?
Asking because @MKleusberg has been improving the constraints handling code over the last few days, including adding a warning message when our parser can't understand all of the constraints.
If you've the time/interest, trying the nightly build out (tomorrow) and seeing if that problem still happens would be useful. 😄
MarqueIV commentedon Aug 31, 2016
Definitely have both the time and the interest. Again, your product is an integral part of me being able to create mine so anything I can do to help I do so gladly! :) Just post the link here on where I need to go to get the daily to test and I'll let you know, just like I did with the black-on-black browsing issue.
MarqueIV commentedon Aug 31, 2016
Oh... and it's your just-released build... again, the one you linked to in the other question about preferences and colors.
justinclift commentedon Aug 31, 2016
Awesome. 😄
The nightly builds for OSX are here:
http://nightlies.sqlitebrowser.org/osx/
MKleusberg commentedon Sep 6, 2016
@MarqueIV Yes, this might be related. But either way it definitely sounds like a bug 😃 Unfortunately I can't reproduce this using one of my test database. So I'm afraid you have to send me that 20MB file; maybe you can zip it though or upload it somewhere.
chrisjlocke commentedon Sep 9, 2016
In the latest nightly (9 Sep) the parser seems broken for multiple lines...
This occurs if you click the 'Execute current line' button. Previous versions parsed the line to be up to the next semi colon.
This only seems a recent 'breakage'...
justinclift commentedon Sep 10, 2016
It might be from this commit by @revolter yesterday: 55d8905
chrisjlocke commentedon Sep 10, 2016
Oops, now I feel guilty...:wink:
revolter commentedon Sep 19, 2016
Ups, sorry. So should a line mean a text line or an SQL line (up until the first semi colon)?
Fixed SQL log containing incorrect lines when executing the current line
chrisjlocke commentedon Sep 19, 2016
A line should be up to a semi-colon, so lines can be split. Its more readable creating a table like:
create table blah (id text,dinDang real,piddlePop integer,diddleIDo text);but thats just me... 😉
bernardosulzbach commentedon Sep 19, 2016
Pretty much what was said above. Production SQLite code has many queries spanning multiple lines.
revolter commentedon Sep 19, 2016
I'm working now on a fix.
revolter commentedon Sep 19, 2016
Well, It didn't work ok in 3.8.0 too. I'm working of a proper fix.
grammar: Parse columns with explicit NULL (un-)constraint correctly
grammar: Support named foreign key constraints in the column definition
TWiStErRob commentedon Jan 6, 2017
Tip: https://github.com/search?l=SQL&q=_id&ref=searchresults&type=Code&utf8=%E2%9C%93 may give you a few examples to try.
grammar: Parse named primary keys in column constraints
MKleusberg commentedon Sep 4, 2019
Earlier today I noticed that no table and no index can be parsed which uses "/" for divisions in any expression. So even this simple table definition won't work:
We had this issue from day 1 using the grammar parser, that's for more than six years now. This probably confirms the long-held theory that division is some sort of seldom-used higher mathematics.
That made me curious. So I'll start collecting other missing bits and pieces in our grammar parser, especially in the expression rule, in this comment here 😉
DISTINCTkeyword is not recognised (see here in the function call part of the picture)CASTexpressions (see here in the cast part of the picture) pass the parser but are scrambled when running through the AST. In the Edit Table dialog they are completely broken.NOTNULLandISNULL(without spaces) don't work (see here in the ISNULL branch of the picture)The list is probably going to grow. Except for the Unicode problem, this can all be fixed rather easily but it makes me wonder once again whether it would be better to rewrite the whole thing.
grammar: Replace the Antlr parser for CREATE INDEX statements
grammar: Replace the Antlr parser for CREATE INDEX statements
grammar: Replace the Antlr parser for CREATE INDEX statements
MKleusberg commentedon Sep 12, 2019
I'm going to close this issue since #1990 is a more up-to-date now. All problems which are mentioned in this issue should be fixed in the new parser 😄