Gnucash 2.3.0 - Database schema and third party tools

[Phil Longstaff]

On June 24, 2009 09:08:12 am marcus.wolschon at googlemail.com wrote:
> On Wed, 20 May 2009 15:30:59 +0200, <marcus.wolschon at googlemail.com> wrote:
> > On Wed, 20 May 2009 08:25:26 -0400, Phil Longstaff
>
> <plongstaff at rogers.com>
>
> > wrote:
> >> On May 20, 2009 04:32:50 am marcus.wolschon at googlemail.com wrote:
> >>> Hello,
> >>>
> >>> is there some documentation on the database-schema
> >>> Gnucash 2.3 introduces, the foreign keys used, if
> >>> transactions are used, where future extensions are
> >>> supposed to be added,...?
> >>> I'd like to give it a close look to see how much
> >>> I need to change any of my existing database-backend
> >>> of jGnucashLib to handle gnucash's own new scheme.
> >>
> >> There's no documentation yet.  I will provide some.
> >
> > Thank you. I'm looking forward to it.
> > And don't hurry, there's no need to. :)
>
> Hello Phil,
>
> any progress on documenting the database-schema yet?

Here's a first cut at the database schema.  It only covers the main tables and 
types (not business tables).  I will put this information into the header files 
so that it gets put into the doxygen developers documentation.

Although there are some fields which contain guids which reference other 
tables, they are not defined as foreign fields (yet).  2 reasons: 1) sqlite3 
doesn't enforce foreign fields (although it is apparently possible to use 
triggers to do some checking) and 2) gnucash is not a database app and doesn't 
necessarily enforce the checking (2 known instances - the "add price" dialog 
creates (and therefore stores) a new price before the commodity has been set, 
and lots in the LOTS table sometimes have the account_guid set to NULL).

Phil
-------------- next part --------------
Types:
Name             Interpretation
-------------------------------
ACCOUNTREF       GUID of an entry in the ACCOUNTS table
BOOLEAN          true/false value - stored as a (hopefully small) integer
COMMODITYREF     GUID of an entry in the COMMODITIES table
DOUBLE           floating double-precision value
GDATE            8-char string - format YYYYMMDD
GUID             32 character GUID - hex-ascii conversion of 16 byte GUID
INT              32-bit integer
INT64            64-bit integer
LOTREF           GUID of an entry in the LOTS table
NUMERIC          stored as 2 fields (both int64).  For field "f", the two fields are "f_num" and
                 "f_denom" storing the numerator and denominator for a gnc_numeric, respectively
STRING           text field
TIMESPEC         14-char string - format YYYYMMDDHHMMSS
TXREF            GUID of an entry in the TRANSACTIONS table

ACCOUNTS
Name            Type          Max Length (string)
-------------------------------------------------
guid            GUID
name       	    STRING        2048
account_type    STRING        2048
commodity_guid  COMMODITYREF
commodity_scu   INT
non_std_scu     BOOLEAN
parent_guid     GUID
code            STRING        2048
description     STRING        2048

BOOKS
Name                Type          Max Length (string)
-----------------------------------------------------
guid                GUID
root_account_guid   GUID
root_template_guid  GUID

BUDGETS
Name        Type    Max Length (string)
----------------------------------------
guid        GUID
name        STRING  2048
description STRING  2048
num_periods INT

COMMODITIES
Name            Type    Max Length (string)
-------------------------------------------
guid            GUID
namespace       STRING  2048
mnemonic        STRING  2048
fullname        STRING  2048
cusip           STRING  2048
fraction        INT
quote_flag      BOOLEAN
quote_source    STRING  2048
quote_tz        STRING  2048

LOTS
Name            Type    Max Length (string)
-------------------------------------------
guid            GUID
account_guid    GUID
is_closed       BOOLEAN

PRICES
Name            Type    Max Length (string)
-------------------------------------------
guid            GUID
commodity_guid  COMMODITYREF
currency_guid   COMMODITYREF
date            TIMESPEC
source          STRING  2048
type            STRING  2048
value           NUMERIC

RECURRENCES
Name                    Type      Max Length (string)
-----------------------------------------------------
obj_guid                GUID
recurrence_mult         INT
recurrence_period_type  STRING    2048
recurrence_period_start GDATE

SCHEDULED TRANSACTIONS
Name                Type      Max Length (string)
-----------------------------------------------------
guid                GUID
name                STRING    2048
enabled             BOOLEAN
start_date          GDATE
last_occur          GDATE
num_occur           INT
rem_occur           INT
auto_create         BOOLEAN
auto_notify         BOOLEAN
adv_creation        INT
adv_notify          INT
instance_count      INT
template_act_guid   GUID

SLOTS
Name         Type      Max Length (string)
------------------------------------------
obj_guid     GUID
name         STRING    2048
slot_type    INT
int64_val    INT64
string_val   STRING    2048
double_val   DOUBLE
timespec_val TIMESPEC
guid_val     GUID
numeric_val  NUMERIC

TRANSACTIONS
Name          Type            Max Length (string)
-------------------------------------------------
guid          GUID
currency_guid COMMODITYREF
num           STRING          2048
post_date     TIMESPEC
enter_date    TIMESPEC
description   STRING          2048

SPLITS
Name              Type          Max Length (string)
---------------------------------------------------
guid              GUID
tx_guid           TXREF
account_guid      ACCOUNTREF
memo              STRING        2048
action            STRING        2048
reconcile_state   STRING        1
reconcile_date    TIMESPEC
value             NUMERIC
quantity          NUMERIC
lot_guid          LOTREF