Database Tables Transactions and Accounts

[Matt Riley]

Hello John,

Thanks again for your feedback. I'm more of a web software engineer and haven't 
looked at C in quite awhile but I looked anyway.

Is there any information on accessing libgnc-engine via QOF? I searched the 
docs, wiki and archives. I realize my use case is probally beyond the scope of 
the application.

I suppose in a worse case I can have my web application export the transaction 
data to a file format that GnuCash can import, assuming again that it won't 
cause problems for GnuCash.

Thanks,

Matt




----- Original Message ----
From: John Ralls <jralls at ceridwen.us>
To: Matt Riley <mrileyaz at yahoo.com>
Cc: gnucash-devel at gnucash.org
Sent: Mon, March 7, 2011 2:11:04 PM
Subject: Re: Database Tables Transactions and Accounts


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