GDA missing records retest (long)
Phil Longstaff
plongstaff at rogers.com
Mon Mar 10 09:59:07 EDT 2008
Mark Johnson wrote:
> Now that I've managed to get the data saved to the DBs and the gnucash's
> exitted, it is time to check the data in the DBs.
>
> As a check on the DB data, I created an awk script to count the numbers
> of accounts, transactions, and splits in the xml data file. Here is the
> awk script:
>
> BEGIN {
> nAccounts = 0;
> nTransactions = 0;
> nSplits = 0;
> }
> /^<gnc:account/ { nAccounts ++ }
> /^<gnc:transaction/ { nTransactions ++ }
> /<trn:split>/ { nSplits ++ }
> END {
> printf("Number of Accounts %d\n", nAccounts);
> printf("Number of Transactions %d\n", nTransactions);
> printf("Number of Splits %d\n", nSplits);
> }
>
> Here is the output from the script:
> Number of Accounts 523
> Number of Transactions 3663
> Number of Splits 11487
>
> The following are the results for each DB:
> (editted for clarity)
>
> SQLITE:
> sqlite> select count(*) from accounts;
> 522
> sqlite> select count(*) from transactions;
> 3665
> sqlite> select count(*) from splits;
> 11494
> sqlite> select count(*) from slots;
> 1637
>
> MySQL:
> mysql> select count(*) from accounts;
> | 522 |
> mysql> select count(*) from transactions;
> | 3665 |
> mysql> select count(*) from splits;
> | 11494 |
> mysql> select count(*) from slots;
> | 1637 |
>
> PostgreSQL:
> gnucash_db=# select count(*) from accounts;
> 522
> gnucash_db=# select count(*) from transactions;
> 3665
> gnucash_db=# select count(*) from splits;
> 11494
> gnucash_db=# select count(*) from slots;
> 1637
>
> The good news is that now all three DBs have the same number of records.
>
> However, it does not agree with the output of my awk script.
>
> Since I have one extra account showing in the XML file, I checked to see
> if the DBs have the ROOT account in them. They do not.
> QUESTION: Is this a problem?
The ROOT account was added somewhere in the 2.x series in GC. It is not
stored as an explicit account in the db. Instead, the ROOT account's
GUID is stored in the books table. When the db is opened and the books
table record is read, the ROOT account's GUID is set to the value in the
books table record.
>
> The next important question I had to look at is: why do I have 2 extra
> transactions and 7 extra splits in the DBs? (Most likely those scheduled
> transactions from when I opened the XML files.)
Yes, I think that's it.
>
> Comparison of the guids for transactions and splits in each DB revealed
> 3 transactions guids were different in each DB. The ones from MySql
> were not present in the XML. Similarly there were 7 split guids that
> did not match in the different DBs. I did not check for them in the
> XML. This is consistent with 3 scheduled transactions being created
> with 7 splits. Now, I wish I'd looked closer at that scheduled
> transaction dialog when I first opened the XML yesterday.... Despite
> this, I believe that these three are scheduled transactions.
> QUESTION: Why do two of them have a entered_date in 2005? Checking one
> against the XML shows that the last couple of entered_dates were in
> 2005, but before that they looked normal.
I assume you opened 3 copies of GC with your XML data, each of which
prompted you about scheduled transactions. If so, it makes sense that
each copy would have had different guids for the new transactions and
splits. Each transaction has an entered date and a posted date. I
believe the posted date is the one shown in the register. Usually, the
entered date is the date that you type in the transaction. For a
transaction resulting from a scheduled transaction, I don't know if the
entered date is the date it is created or the date of the original sx.
>
> However, there was an excess of TWO transactions in the DBs, but THREE
> differing guids. I was able to track this to a missing template
> transaction for a scheduled transaction. Here is the corresponding
> entry in the PostgreSQL error log:
> ERROR: null value in column "currency_guid" violates not-null constraint
> STATEMENT: INSERT INTO transactions (guid, currency_guid, num,
> post_date, enter_date, description) VALUES
> ('16097c3be93a538e66e1de61ad743b0a', NULL, '', '2005-04-27',
> '2005-04-27', 'Telus')
>
> Here is the first portion of the XML:
> <gnc:transaction version="2.0.0">
> <trn:id type="guid">16097c3be93a538e66e1de61ad743b0a</trn:id>
> <trn:date-posted>
> <ts:date>2005-04-27 00:00:00 -0600</ts:date>
> </trn:date-posted>
> <trn:date-entered>
> <ts:date>2005-04-27 12:26:51 -0600</ts:date>
> </trn:date-entered>
> <trn:description>Telus</trn:description>
> <trn:splits>
>
> Here is the first portion of another template transaction:
> <gnc:transaction version="2.0.0">
> <trn:id type="guid">df0c8dbca68109b354d2b5cb035bf623</trn:id>
> <trn:currency>
> <cmdty:space>ISO4217</cmdty:space>
> <cmdty:id>CAD</cmdty:id>
> </trn:currency>
> <trn:date-posted>
> <ts:date>2005-09-08 00:00:00 -0600</ts:date>
> </trn:date-posted>
> <trn:date-entered>
> <ts:date>2005-09-08 12:10:41 -0600</ts:date>
> </trn:date-entered>
> <trn:description>Primerica</trn:description>
> <trn:splits>
>
> NOTE the missing <trn:currency> tag in the former.
> PROBLEM: a missing XML tag has led to a failed data insert.
Hmmm. I have no clue why there was no currency.
>
> While looking at the PostgreSQL error log, I also noted the following error:
> ERROR: relation "books" does not exist
> STATEMENT: INSERT INTO books (guid, root_account_guid,
> root_template_guid) VALUES ('be5d5b80c1a8f36ec33139cb27440ca7',
> '8f436420bffa2ba45515192c895b083b', '2c54584a7995ce449f2fd371771ef562')
> PROBLEM: the books table is not created. I checked all 3 DBs.
OK. I'll check this.
>
> Also, from the PostgreSQL error log:
> ERROR: null value in column "fullname" violates not-null constraint
> STATEMENT: INSERT INTO commodities (guid, namespace, mnemonic,
> fullname, cusip, fraction, quote_flag, quote_source, quote_tz) VALUES
> ('d6d52116cd6e092ea4f525ede20f38d0', 'template', 'template', NULL, NULL,
> 0, 0, NULL, '')
> There were many similar entries. I'm not sure what this means. The XML
> data does not match this guid. All three DBs have 30 commodities. The
> XML file has the following near the beginning:
> <gnc:count-data cd:type="commodity">29</gnc:count-data>
> PROBLEM: spurious commodities inserts.
I've seen this "template" commodity before. Most commodities have a
fullname. I guess, for this one, I need to allow NULL values.
>
> I found the following in the XML file:
> <gnc:account version="2.0.0">
> <act:name/>
> <act:id type="guid">6b516dc592018e5b3968bcbd5625c7cf</act:id>
> <act:type>ROOT</act:type>
> <act:commodity>
> <cmdty:space>template</cmdty:space>
> <cmdty:id>template</cmdty:id>
> </act:commodity>
> <act:commodity-scu>1</act:commodity-scu>
> <act:parent type="guid">dd5fa0a84ddbdfc6fde7b6bcb810a5b7</act:parent>
> </gnc:account>
>
> This is inside the <gnc:template-transactions> tag, so I suppose it is
> related to scheduled transactions. I guess it is the source of the
> spurious commodities inserts.
>
> I found a commodity in the DBs with namespace, mnemonic and fullname all
> set to "template". This appears to the the reason for the differing
> counts between the XML and the DBs.
> QUESTION: should this be a commodity? If so, why does gnucash-gda
> attempt to insert a bunch more copies of it?
There is a template root account as well as the regular root account,
and the guid for that template root account is also stored in the books
table. Since the rest of GC uses the template currency as a real
currency, it is much easier for the gda backend to handle it as a real
commodity and not try to filter it out. I'll look into why a bunch are
inserted.
>
> The above is quite long. I've highlight "PROBLEM"s and "QUESTION"s with
> those strings respectively. This is now a tremendous improvement over
> the previous situation.
>
> Mark
>
> _______________________________________________
> gnucash-devel mailing list
> gnucash-devel at gnucash.org
> https://lists.gnucash.org/mailman/listinfo/gnucash-devel
>
More information about the gnucash-devel
mailing list