Database Tables Transactions and Accounts
jralls at ceridwen.us
Mon Mar 7 16:11:04 EST 2011
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
> The value for 'gnucash.splits.tx_guid' maps to new 'gnucash.transactions.guid'
> 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.
More information about the gnucash-devel