Database Tables Transactions and Accounts
mrileyaz at yahoo.com
Mon Mar 7 15:04:33 EST 2011
Thanks for your help. Here's the final select query that worked for me:
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.
----- Original Message ----
From: John Ralls <jralls at ceridwen.us>
To: Matt Riley <mrileyaz at yahoo.com>
Cc: gnucash-devel at gnucash.org
Sent: Sun, March 6, 2011 9:32:30 PM
Subject: Re: Database Tables Transactions and Accounts
On Mar 6, 2011, at 7:37 PM, Matt Riley wrote:
> I would like to know if there's a cross reference table or file that maps the
> 'guid' database column of the tables 'transactions' and 'accounts' together. I
> explored the other database tables and at first glance there does not appear to
> be one. I'm using MySQL as the local database.
> Any help is greatly appreciated.
Transactions aren't directly linked to accounts, splits are, so to put together
a transaction with its accounts you'll do
select * from t=transactions, s=splits, a=accounts join on s.tx_guid = t.guid
and s.account_guid = a.guid...
(The syntax isn't quite right, but you get the idea.)
More information about the gnucash-devel