db backend observations

[Phil Longstaff]

On December 26, 2008 05:21:07 pm Boyd Kelly wrote:
> Hi Phil,
>
> I have a 21M xml file with transactions in both CAD and USD back to 99,
> so lots (maybe too much) stuff to work with.
>
> I can open the xml file and saveas to a new sqlite file just fine.  It
> appears that I can continue to work with the sqlite file with no
> apparent issues.  I have imported a couple of ofx files, closed and
> reopened the file several times.  Ok for now.
>
> However with either sqlite or mysql I cannot save back to xml without
> major problems to the data file.
>
> If I work with a mysql database, once I close the db and then restart
> gnucash --nofile, choose the most recent 'file', my balances are all 0.
> On the mysql command line:  select count(*) from transactions returns
> 6494 the same as the sqlite file.
>
> So I will work with sqlite for the time being....
>
> When I open the 'sqlite_to_xml' file back in gnucash 2.2.7, I notice the
> following:
>
> Of all my accounts, only two liability credit card accounts(a visa and
> mc) have correct balances.  The only transactions left in any other
> equity, income or expense accounts are the 'other side' of the visa or
> mc account transactions.

I  know about the problem that saving back to an xml file can result in missing 
transactions.  The reason is that when an sqlite file is opened, the 
transactions are not loaded immediately, but only when required.  Opening an 
account register will fetch the transactions in that register.  Producing a 
report will fetch all required transactions.  However, when you "save as", it 
only saves the transactions which are currently loaded.  I need to modify the 
code to load all transactions before the "save as" is performed.

> At this point are you preferring to work with mysql or sqlite or
> indifferent?

I'm doing most of my testing with sqlite, though I do also have a mysql db set 
up.  I'm eating my own dog food by using sqlite for my home finances.  However, 
I am indifferent which you use, because there are minor differences in the gc 
code for one db vs the other.  However, the db library backend driver is 
completely different, so using mysql does exercise different code.

Phil