GDA Missing records in SQLite
mrj001 at shaw.ca
Sun Feb 24 22:24:26 EST 2008
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.
> 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.
More information about the gnucash-devel