Database Tables Transactions and Accounts

[John Ralls]

On Mar 7, 2011, at 12:04 PM, Matt Riley wrote:

> Hello John,
> 
> Thanks for your help. Here's the final select query that worked for me:
> 
> SELECT *
> FROM gnucash.transactions AS t
> JOIN gnucash.splits AS s
>    ON s.tx_guid = t.guid
> JOIN gnucash.accounts AS a
>    ON s.account_guid = a.guid
> WHERE t.guid = '[my-guid]';
> 
> 
> My next task is to insert data into these tables while maintaining data 
> integrity. I'm building myself a mobile web application that will allow me to 
> enter transaction data. The new transaction data is queued online and my local 
> system will pickup the new data and insert it into my local MySQL into these 
> tables on a scheduled basis.
> 
> Based on my analysis of these tables, here's my thinking on how to insert the 
> data correctly. Please let me know if I'm missing anything obvious.
> 
> First insert a record in 'gnucash.transactions'
> Take the new 'gnucash.transactions.guid' value and insert two records into 
> 'gnucash.splits'
> The value for 'gnucash.splits.tx_guid' maps to new 'gnucash.transactions.guid' 
> value
> The value for 'gnucash.splits.account_guid' maps to an existing 
> 'gnucash.accounts.guid' value for each corresponding account
> Values for 'gnucash.splits.value_num' and 'gnucash.splits.quantity_num' need to 
> correspond to the *_denom value
> For example if *_denom = 100, then a real value of $1.25 will translate to '125'
> Also, while I'm inserting new records I'll need to create my own new guid values
> 
> Does this look good? Are there any other tables I should be concerned about when 
> adding new transactions?
> 
> Your help is greatly appreciated.

Won't work. It's a lot more complicated than that. ***You can't understand how it works from the tables!*** There's a lot of action going on in C; everything important, in fact. Spend a few dozen hours studying all of the code in gnucash/src/engine and you'll eventually see how it fits together.

If it's a web app, then the server can talk to libgnc-engine and do it safely through QOF. Anything else is very unlikely to make transaction that won't either be ignored or crash.

If gnucash interoperablity isn't a goal, then write your own data model. Gnucash's isn't designed correctly for transactional use with an RDB; fixing that is one of the major projects for the next year or three.

Regards,
John Ralls