GDA Missing records in SQLite

[Mark Johnson]

Graham Leggett wrote:
> Phil Longstaff wrote:
>
>> There is a function to query the backend to see what features it 
>> supports, but prepared statements is not in that list.  Also, if we 
>> require prepared statements, that might cut out the sqlite backend 
>> because a libgda modification to use it might not propagate out far 
>> enough.  I know Derek wants to see the xml gnucash file replaced by a 
>> sqlite db, so we need to be really sure it is unusable before we 
>> disqualify it.
>
> A quick Google search shows a number of references to prepared 
> statement use in sqlite, which suggests to me that prepared statements 
> are supported.
Looking through SQLite's C API confirms that.  However, the question is 
whether libgda uses that capability or, failing that, escapes strings 
correctly.  Note that it is libgda and/or its providers which would be 
doing the string escaping and not gnucash-gda.
>
> Two things worry me at this point about the current behaviour of libgda:
>
> - Row inserts are failing, but the error is not communicated back to 
> the caller. As a result, the database is corrupted in the process.
It is communicated back to gnucash-gda from libgda.  This is currently a 
problem with gnucash-gda.  gnucash-gda logs the error into 
/tmp/gnucash.trace, but does not inform the user.
>
> - libgda doesn't seem to (yet) guarantee that prepared statements are 
> used, and therefore that parameters do not need escaping.
I note that the MySql provider does not implement a function for 
gda_query_execute.  This causes libgda to use fallback code which does 
not use prepared statements and these strings are handled properly.  (I 
am not sure if the escaping is done in the fallback code or in a MySql 
provider implementation of another function used by that code.)

The PostgreSQL provider does use prepared statements. Unfortunately, it 
prepares the statement, executes it, and deallocates the statement for 
EVERY call to gda_query_execute.  This triples the inter-process 
communication as compared to simply rendering (properly escaped) SQL and 
sending it to the server.  NOTE the rendering of the query into SQL 
would be done by libgda and not gnucash-gda.

Regardless of whether or not prepared statements are used by a provider, 
it is the libgda backend which is doing the escaping and not gnucash-gda.
>
> The data being saved is financial data, and people are using this data 
> to file tax returns and various other mandatory stuff that could prove 
> expensive if done incorrectly. Gnucash should be treating this data 
> conservatively, and should only be using backends that can give some 
> kind of certainty that data won't be corrupted for any reason.
Agreed.
>
> If we have to temporarily disable a backend until that backend works 
> correctly, it is the safest thing to do, and offers an incentive to 
> get the backend fixed.
>
> Regards,
> Graham
Mark