Database Tables Transactions and Accounts

[Matt Riley]

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.

Matt




----- 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:

> Hello,
> 
> 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
something like 

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.)

Regards,
John Ralls