About GnuCash 2.3.x / 2.4

[Phil Longstaff]

The basic tables are:

- books table - 1 row - has book guid, root account guid, root template account guid (used for scheduled transactions)
- account table - one per account - has guid of parent account, has guid of commodity.  Top level accounts (e.g. Assets) have parent account guid = root account guid from books table.
- commodities table - one per commodity
- transactions table - contains description and some dates
- splits table - contains tx_guid to refer to its transaction, account_guid to refer to account it belongs to.  Has "value" and "amount" ("quantity"?).  For a stock purchase of 10 shares costing $100.00, one of these has the value 10 and one has the value 10000, but I can't remember which is which.
- slots table - has a unique id (I think) and an object_guid that the slot belongs to, a string path, a type (boolean/int/double/string/...) and some value fields (use the correct one based on type).  Used for "extra information" to tack on to an object.  Note that some of this extra information has actually become standard e.g. for accounts, the "hidden" flag is stored here.  For transactions created from scheduled transaction, contains info about the scheduled transaction.  For budgets, contains budget info.

Note: all gnc_numeric values (used for amounts and quantities) are
stored as numerator/denominator pair, both of which are 64-bit integers
i.e. "value" will have "value_num" and "value_denom".

The balance for account 'ABC' on date 'D' can be determined by:

SELECT SUM(amount_num),amount_denom FROM splits WHERE tx_guid IN (SELECT * FROM transactions WHERE post_date <= D) AND account_guid = (SELECT guid FROM accounts WHERE name = 'ABC') GROUP BY amount_denom

This could *easily* not be the most efficient join, but it gives you the idea.  If you don't change the number of decimal digits for an account, I *think* that all splits for that account will have the same denominator, so the query will give you 1 row.  If you have changed the number of decimal digits, you might get more than 1 row, one for each denominator (100, 1000, 10000, ...) and will need to add them.  The SQL backend to gnucash actually uses:

SELECT account_guid,reconcile_state,SUM(amount_num),amount_denom FROM splits GROUP BY account_guid,reconcile_state,amount_denom

when it starts.  This generates 1 row per account_guid/reconcile_state/amount_denom triplet.  Scanning and addition gives 1 row per account_guid/reconcile_state pair, and these values provide the account balances.

There is *ABSOLUTELY NO GUARANTEE* of what will happen if you try to modify the data using anything other than gnucash.

At some point, this information will be put into the gnucash development wiki.

Phil



________________________________
From: AmigaPhil <AmigaPhil at ping.be>
To: gnucash-user at gnucash.org
Sent: Tuesday, August 4, 2009 4:49:02 PM
Subject: About GnuCash 2.3.x / 2.4

Now that GnuCash 2.3.x / 2.4 will also store the data in a SQL
database, I guess this will allow users to perform several
operations without loading the GnuCash interface, like creating
and interacting with a HTML (and PHP/MySQL queries) report from
a web browser.  Am I right ?

What will be needed to be able to access the database from a local
web form ?

- a web browser (almost every computer already has one installed)
- PHP5
- MySQL or PostgreSQL
  (MySQL is already installed on my Linux distribution.  But I read
  some years ago discussions about how superior/safer PostgreSQL is.
  Is it worth to install PostgreSQL too ?  Which one is better ?)

Anything else ?



AmigaPhil

_______________________________________________
gnucash-user mailing list
gnucash-user at gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.