On June 24, 2009 09:08:12 am marcus.wolsc...@googlemail.com wrote:
> On Wed, 20 May 2009 15:30:59 +0200, <marcus.wolsc...@googlemail.com> wrote:
> > On Wed, 20 May 2009 08:25:26 -0400, Phil Longstaff
>
> <plongst...@rogers.com>
>
> > wrote:
> >> On May 20, 2009 04:32:50 am marcus.wolsc...@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
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
_______________________________________________
gnucash-devel mailing list
gnucash-devel@gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
