libdbi-users

[libdbi-users] PATCH: sqlite3 parser bugs

[Vikram A. <noe...@gm...>]

I'm working on a patch for the sqlite3 driver to resolve some of the 
issues I've been having.

The first issue I had was that the parser was unable to find Tables in 
the SQL statement. This was because the parser assumed that the only 
possible white space around keywords was ' ' or 0x20;

eg. strstr(statement," FROM ");

If you had SQL formatted like;

SELECT foo\nFROM bar;

or

SELECT foo\n\tFROM bar;

The parser would fail to find 'bar' because there isn't a ' ' character 
before FROM. This was a problem for all keywords. I think using strtok 
would have been a better approach than going char by char. But since the 
parser works in general, I didn't want to rewrite it all.

The second bug was that the parser would always read past the first 
"endword" if there was a JOIN just before it. Resulting in tables being 
added to the list that don't exist. (eg. Artist.name, not a table but a 
field). Thus in order to fix this you need to check for endwords right 
away if you're parsing through JOINs.

I'm still having all sorts of issues with the sqlite3 driver but these 
two things have at least got my application running :)

I also removed some magic references to nottables[] and added some 
comments to denote the end of large statements.

I ran make check with the patch and 380 tests passed, 0 failed.

Let me know what you think of the patch.

On a side note, write performance to sqlite3 db is incredibly poor (>1s 
for each INSERT regardless of content or no. of fields). But read 
performance is great. Any tips on how to tune sqlite3?


Vikram.

Re: [libdbi-users] PATCH: sqlite3 parser bugs

[Vikram A. <noe...@gm...>]

On 12/25/2010 08:34 PM, Vikram Ambrose wrote:
> The first issue I had was that the parser was unable to find Tables in 
> the SQL statement. This was because the parser assumed that the only 
> possible white space around keywords was ' ' or 0x20;
>
> [...]
>
> The second bug was that the parser would always read past the first 
> "endword" if there was a JOIN just before it. Resulting in tables 
> being added to the list that don't exist. (eg. Artist.name, not a 
> table but a field). Thus in order to fix this you need to check for 
> endwords right away if you're parsing through JOINs.
>
>

And here is an update to the patch for a third bug.

The third bug is where the driver is unable to find functions in the SQL 
query.

[...]
if ( function_flag == 1 ) {
    free(statement_copy);
    strcpy(curr_field,itemstore);
[...]

"itemstore" is a pointer into "statement_copy", and as such you cannot 
free it before the strcpy().

The patch moves the free() after the strcpy() and the parser is now able 
to correctly identify function return types.

Again, 380 tests pass, 0 fail.

By the way, Merry Christmas and a Happy New Year to everyone.


Vikram.

Re: [libdbi-users] PATCH: sqlite3 parser bugs

[<mar...@mh...>]

Vikram Ambrose writes:
 > And here is an update to the patch for a third bug.

Better late than never ... I've applied your patch, and things seem to
work ok. Thanks for being this patient.

regards,
Markus

-- 
Markus Hoenicka
http://www.mhoenicka.de
AQ score 38