GDA referential integrity

[Phil Longstaff]

Mark Johnson wrote:
> Having successfully loaded my xml file into gnucash-gda, I saved it to a 
> new postgresql database.  As Phil mentioned, the performance was poor.  
> I have several years of data, and it took hours to save.  The save 
> completed sometime during the night, so I don't know exactly how long it 
> took.
>   

I think the basic performance problem is that on the initial save, I am 
saving each object individually rather than batched.  During regular 
gnucash use, if you create a new account and then commit it, the gda 
backend needs to commit the commodity, because it might not have been 
committed.  Then, a SELECT is done to see if the account already exists 
and is followed by an INSERT if it does not, or an UPDATE if it does.  I 
know some databases allow INSERT OR UPDATE i.e. INSERT if it doesn't 
exist and UPDATE if it does, but GDA does not support that, and not all 
GDA backends do, so I can't count on it.  In addition, when saving 
(creating or modifying) an account, the old set of saved slots needs to 
be deleted and then the new slots need to be saved.  However, on an 
initial save from XML into a GDA backend, 1) those checks and deletes 
are unnecessary because the tables are empty and 2) a batched mode 
INSERT INTO accounts VALUES 
(...account1...),(...account2...),(...account3...),... could be used.  I 
think GDA supports this, and if not I can fall back to SQL, but I 
haven't implemented it yet.

> I thought I'd check the referential integrity of the results.
>
> FIRST:
> I checked that for every split, a transaction existed.  I found the 
> following:
> gnucash_db=# select distinct tx_guid from splits except select guid from 
> transactions;
>              tx_guid
> ----------------------------------
>  16097c3be93a538e66e1de61ad743b0a
> (1 row)
>
> gnucash_db=# select * from transactions where guid = 
> '16097c3be93a538e66e1de61ad743b0a';
>  guid | currency_guid | num | post_date | enter_date | description
> ------+---------------+-----+-----------+------------+-------------
> (0 rows)
>
> So I decompressed the xml file, and searched for the transaction.  I 
> found the following transaction.  It looks like a scheduled transaction 
> template may have found its way into the splits table.  I have many 
> scheduled transactions for each month which enter estimates of my 
> various bills in advance.  Neither my checking account register nor my 
> telephone expense account register show this transaction.
>
> I did find a <gnc:schedxaction version="2.0.0"> tag for the real Telus 
> scheduled transaction.  Is it possible that this is some corruption in 
> my xml file due to a problem with scheduled transactions, perhaps in a 
> past version of gnucash?  Is this something that gnucash should have 
> eliminated from my xml file?  Or is it something I should manually 
> eliminate?
>
> [scheduled tx info deleted]
>   

OK.  Thanks.  I'll take a look at this.

Phil