GDA Missing records in SQLite

[Mark Johnson]

Graham Leggett wrote:
> Mark Johnson wrote:
>
>> By examining which transactions, accounts, and splits were missing 
>> from SQLite (as compared to MySQL), I was able to determine that 
>> anything which had a single quote in a string (description, name, & 
>> memo fields) failed to be inserted into SQLite.  This is a one-to-one 
>> correspondence.  i.e. anything that had a single quote failed.  These 
>> were the only records missing as compared to MySql.
>>
>> At the moment, I am guessing that the SQLite provider does not 
>> properly escape such strings passed to it.  If so, the SQL INSERT 
>> statements would be illegal and fail.  (I've built just such a bug 
>> myself once using MySql.)
>>
>> More study of the SQLite provider is required.  I did not find any 
>> existing bug reports regarding single quotes and the SQLite 
>> provider.  If  I can confirm my guess, I'll file the bug report.
>
> This sounds more like a symptom of not using prepared statements in 
> sqlite. This could be a GDA problem, or it could be sqlite not 
> supporting prepared statements.
Looking in SQLite's documentation on the SQL it supports, I do not see 
anything on prepared statements.

The GdaQuery object does allow you to set parameters.  So then, it would 
be up to the provider to use or not use them.  In the case of SQLite, it 
would have to render SQL and send it to the server.  This brings us back 
to my original guess (and, so far, it is just an educated guess). 

Aside: In the case of the PostgreSQL provider, it does something horrid 
with prepared statements.  For each query executed, it creates a 
prepared statement, executes it, and deallocates it.  This triples the 
interprocess communications as compared to simply rendering a SQL 
statement and sending it.  It also does nothing to preserve the parsing 
of the query, generation of the plan, optimization of the plan, etc.

The MySql provider does not export the function which GdaQuery uses to 
execute queries.  This causes libgda to use "fallback" code, which looks 
like it simply renders and sends a SQL statement.  This makes the MySql 
provider more efficient than the PostgreSQL provider.

In any case, the code I have looked at in gnucash-gda builds a new query 
each time.  Thus, it would not derive any benefit from prepared statements.

(I am using libgda version 3.1.2.)
>
> What is more worrying is that rows are being dropped and no error is 
> being thrown. That looks to me like a more serious bug at this point, 
> it means that strange data corruption errors are likely and the end 
> user will never know.
I agree completely.
>
> Regards,
> Graham
> -- 
Mark