SQLite Backend Data

[John Ralls]

On Feb 2, 2011, at 5:21 PM, Mark wrote:

> Thank you all for the clarification, and your cautions are all well taken. I am working only with an expendable copy of Gnucash's SQLite file, and am interested solely in creating custom views and reports of my data.
> 
> The data I want are mainly budget lines and register transactions. The questions I have below all have to do with the db design. Is that viewable anywhere?
> 
> Questions:
> 
> I'm not sure what "key-value pairs" (in slots table) are. Under what circumstances, if any, would I need them?
> 
> How is the 'transactions' table related to the 'splits' table? Is the
> 
> Am I right that dollar amounts are stored absolutely with no decimal point -- e.g. $123.90 appears as 12390?

Key-value-pairs (KVP for short) are a mechanism that's been used to store a variety of metadata. It has allowed developers to add features to Gnucash without changing the way the database is stored, which keeps the database backwards-compatible: Earlier versions just ignore the extra KVP values because they don't know to look for them. What KVP data is associated with what tables is not documented, but it needs to be. The only way to figure out at present is to study the code in the core modules like  engine and business.

Transactions are a container of two or more splits. The splits have the account and amount of each part of a transaction. Join splits.tx_guid = transactions.guid. (For the split to make sense you'll also need to join splits.account_guid = accounts.guid).

Yes, amounts are always integers in Gnucash to avoid rounding errors. Each number has two fields, foo_num and foo_denom. Divide the former by the latter. The result isn't always representable as a decimal (1/3 for example).

Regards,
John Ralls