SQL
This page is for information about a SQL backend for GnuCash.
The following CREATE TABLE commands show the SQL code from Gnucash which creates a new database. It doesn't show the relations, nor does it show the vast amount of data saved without structure in the Slots table.
This Entity-Relationship Diagram does.
Locking table, should have zero or one record. Provides weak protection against simultaneous access.
CREATE TABLE gnclock (
Hostname varchar(255),
PID int
);
Versions table lists all of the other tables with their schema versions (as long as the developer who changes a schema remembers to also change the versions table). Entries should be hard-coded in gnucash.
CREATE TABLE versions (
table_name text(50),
table_version integer
);
A book is the root container for all other entities in the Query Object Framework. At present, because of the way the load logic and user interface are written, there can be only one book in a database.
CREATE TABLE books (
guid CHAR(32) PRIMARY KEY NOT NULL,
root_account_guid CHAR(32) NOT NULL,
root_template_guid CHAR(32) NOT NULL
);
Commodities contains descriptions of all currencies, securities, and tangible asset types accounted for in a book. Currencies are preloaded by Gnucash; other commodities are created as required by the user.
CREATE TABLE commodities (
guid CHAR(32) PRIMARY KEY NOT NULL,
namespace text(2048) NOT NULL,
mnemonic text(2048) NOT NULL,
fullname text(2048),
cusip text(2048),
fraction integer NOT NULL,
quote_flag integer NOT NULL,
quote_source text(2048),
quote_tz text(2048)
);
Prices contains conversion factors (prices/exchange rates) between two commodities (currencies, securities) at a particular time. Many prices may exist per commodity pair for historical reports.
CREATE TABLE prices (
guid CHAR(32) PRIMARY KEY NOT NULL,
commodity_guid CHAR(32) NOT NULL,
currency_guid CHAR(32) NOT NULL,
date timestamp NOT NULL,
source text(2048),
type text(2048),
value_num integer NOT NULL,
value_denom integer NOT NULL
);
Accounts are the containing entity for transactions
CREATE TABLE accounts (
guid CHAR(32) PRIMARY KEY NOT NULL,
name text(2048) NOT NULL,
account_type text(2048) NOT NULL,
commodity_guid CHAR(32) NOT NULL,
commodity_scu integer NOT NULL,
non_std_scu integer NOT NULL,
parent_guid CHAR(32),
code text(2048),
description text(2048)
);
Slots is a sort of catch all for a variety of data lacking a table. A top-level slot will have a have as it's obj_guid the guid of some row in another table. Container slots (which may be of type KVP_TYPE_FRAME or KVP_TYPE_GLIST) will have a giud_val, and child slots will use this value for their obj_guid. (Yes, id's would be much faster but would require a schema change). These guid's are discarded at load, and an outside entity's slots are recursively deleted and rewritten when it is edited. A KVP can be one of several types, so only one of the foo_val fields will be non-null.
For anyone looking to query a Gnucash db without using the Gnucash API, that means that in order to get all of ths KVP information you'll have to examine the slot type and requery for the children of any FRAME or LIST slots. But since most of the deeply-nested KVPs are online banking setup and import Bayesian matching data, neither of which is of much use outside of Gnucash, you can probably get away with a simple second inner join on slots2.obj_guid = slots1.guid_val.
CREATE TABLE slots (
id integer PRIMARY KEY AUTOINCREMENT NOT NULL,
obj_guid CHAR(32) NOT NULL,
name text(4096) NOT NULL,
slot_type integer NOT NULL,
int64_val integer,
string_val text(4096),
double_val real,
timespec_val CHAR(14),
guid_val CHAR(32),
numeric_val_num integer,
numeric_val_denom integer
);
Transactions are transfers of commodities between accounts; if the accounts are for different commodities, then a price is created as the ratio of the quantities in each. Transactions must contain at least two splits, and the credits and debits in the splits must sum to the same amount -- they must balance.
CREATE TABLE transactions (
guid CHAR(32) PRIMARY KEY NOT NULL,
currency_guid CHAR(32) NOT NULL,
num text(2048) NOT NULL,
post_date timestamp NOT NULL,
enter_date timestamp NOT NULL,
description text(2048)
);
Splits tie an amount of a commodity to a single account in a transaction.
CREATE TABLE splits (
guid CHAR(32) PRIMARY KEY NOT NULL,
tx_guid CHAR(32) NOT NULL,
account_guid CHAR(32) NOT NULL,
memo text(2048) NOT NULL,
action text(2048) NOT NULL,
reconcile_state text(1) NOT NULL,
reconcile_date timestamp NOT NULL,
value_num integer NOT NULL,
value_denom integer NOT NULL,
quantity_num integer NOT NULL,
quantity_denom integer NOT NULL,
lot_guid CHAR(32)
);
Lots are used to tie buy and sell transactions together (the transaction guids are contained in slots) to compute capital gains and losses.
CREATE TABLE lots (
guid CHAR(32) PRIMARY KEY NOT NULL,
account_guid CHAR(32),
is_closed integer NOT NULL
);
Budgets are the top level entities for, well, budgeting.
CREATE TABLE budgets (
guid CHAR(32) PRIMARY KEY NOT NULL,
name text(2048) NOT NULL,
description text(2048),
num_periods integer NOT NULL
);
CREATE TABLE budget_amounts (
id integer PRIMARY KEY AUTOINCREMENT NOT NULL,
budget_guid text(32) NOT NULL,
account_guid text(32) NOT NULL,
period_num integer NOT NULL,
amount_num bigint NOT NULL,
amount_denom bigint NOT NULL
);
CREATE TABLE recurrences (
obj_guid CHAR(32) NOT NULL,
recurrence_mult integer NOT NULL,
recurrence_period_type text(2048) NOT NULL,
recurrence_period_start CHAR(8) NOT NULL
);
Scheduled transactions enable automatic creation of real transaction periodically in the future. There is a hidden set of accounts with template transactions having template splits (which store the actual account guids and amounts in slots). The hidden account related to the schedxaction entry is pointed to by template_act_guid.
CREATE TABLE schedxactions (
guid CHAR(32) PRIMARY KEY NOT NULL,
name text(2048),
enabled integer NOT NULL,
start_date date NOT NULL,
last_occur date,
num_occur integer NOT NULL,
rem_occur integer NOT NULL,
auto_create integer NOT NULL,
auto_notify integer NOT NULL,
adv_creation integer NOT NULL,
adv_notify integer NOT NULL,
instance_count integer NOT NULL,
template_act_guid CHAR(32) NOT NULL
);
Business tables:
CREATE TABLE entries (
guid text(32) PRIMARY KEY NOT NULL,
date text(14) NOT NULL,
date_entered text(14),
description text(2048),
action text(2048),
notes text(2048),
quantity_num bigint,
quantity_denom bigint,
i_acct text(32),
i_price_num bigint,
i_price_denom bigint,
i_discount_num bigint,
i_discount_denom bigint,
invoice text(32),
i_disc_type text(2048),
i_disc_how text(2048),
i_taxable integer,
i_taxincluded integer,
i_taxtable text(32),
b_acct text(32),
b_price_num bigint,
b_price_denom bigint,
bill text(32),
b_taxable integer,
b_taxincluded integer,
b_taxtable text(32),
b_paytype integer,
billable integer,
billto_type integer,
billto_guid text(32),
order_guid text(32)
);
CREATE TABLE customers (
guid text(32) PRIMARY KEY NOT NULL,
name text(2048) NOT NULL,
id text(2048) NOT NULL,
notes text(2048) NOT NULL,
active integer NOT NULL,
discount_num bigint NOT NULL,
discount_denom bigint NOT NULL,
credit_num bigint NOT NULL,
credit_denom bigint NOT NULL,
currency text(32) NOT NULL,
tax_override integer NOT NULL,
addr_name text(1024),
addr_addr1 text(1024),
addr_addr2 text(1024),
addr_addr3 text(1024),
addr_addr4 text(1024),
addr_phone text(128),
addr_fax text(128),
addr_email text(256),
shipaddr_name text(1024),
shipaddr_addr1 text(1024),
shipaddr_addr2 text(1024),
shipaddr_addr3 text(1024),
shipaddr_addr4 text(1024),
shipaddr_phone text(128),
shipaddr_fax text(128),
shipaddr_email text(256),
terms text(32),
tax_included integer,
taxtable text(32)
);
CREATE TABLE employees (
guid text(32) PRIMARY KEY NOT NULL,
username text(2048) NOT NULL,
id text(2048) NOT NULL,
language text(2048) NOT NULL,
acl text(2048) NOT NULL,
active integer NOT NULL,
currency text(32) NOT NULL,
ccard_guid text(32),
workday_num bigint NOT NULL,
workday_denom bigint NOT NULL,
rate_num bigint NOT NULL,
rate_denom bigint NOT NULL,
addr_name text(1024),
addr_addr1 text(1024),
addr_addr2 text(1024),
addr_addr3 text(1024),
addr_addr4 text(1024),
addr_phone text(128),
addr_fax text(128),
addr_email text(256)
);
CREATE TABLE vendors (
guid text(32) PRIMARY KEY NOT NULL,
name text(2048) NOT NULL,
id text(2048) NOT NULL,
notes text(2048) NOT NULL,
currency text(32) NOT NULL,
active integer NOT NULL,
tax_override integer NOT NULL,
addr_name text(1024),
addr_addr1 text(1024),
addr_addr2 text(1024),
addr_addr3 text(1024),
addr_addr4 text(1024),
addr_phone text(128),
addr_fax text(128),
addr_email text(256),
terms text(32),
tax_inc text(2048),
tax_table text(32)
);
CREATE TABLE orders (
guid text(32) PRIMARY KEY NOT NULL,
id text(2048) NOT NULL,
notes text(2048) NOT NULL,
reference text(2048) NOT NULL,
active integer NOT NULL,
date_opened text(14) NOT NULL,
date_closed text(14) NOT NULL,
owner_type integer NOT NULL,
owner_guid text(32) NOT NULL
);
CREATE TABLE jobs (
guid text(32) PRIMARY KEY NOT NULL,
id text(2048) NOT NULL,
name text(2048) NOT NULL,
reference text(2048) NOT NULL,
active integer NOT NULL,
owner_type integer, owner_guid text(32)
);
CREATE TABLE invoices (
guid text(32) PRIMARY KEY NOT NULL,
id text(2048) NOT NULL,
date_opened timestamp,
date_posted timestamp,
notes text(2048) NOT NULL,
active integer NOT NULL,
currency text(32) NOT NULL,
owner_type integer,
owner_guid text(32),
terms text(32),
billing_id text(2048),
post_txn text(32),
post_lot text(32),
post_acc text(32),
billto_type integer,
billto_guid text(32),
charge_amt_num bigint,
charge_amt_denom bigint
);
CREATE TABLE billterms (
guid text(32) PRIMARY KEY NOT NULL,
name text(2048) NOT NULL,
description text(2048) NOT NULL,
refcount integer NOT NULL,
invisible integer NOT NULL,
parent text(32),
type text(2048) NOT NULL,
duedays integer,
discountdays integer,
discount_num bigint,
discount_denom bigint,
cutoff integer
);
CREATE TABLE taxtables (
guid text(32) PRIMARY KEY NOT NULL,
name text(50) NOT NULL,
refcount bigint NOT NULL,
invisible integer NOT NULL,
parent text(32)
);
CREATE TABLE taxtable_entries (
id integer PRIMARY KEY AUTOINCREMENT NOT NULL,
taxtable text(32) NOT NULL,
account text(32) NOT NULL,
amount_num bigint NOT NULL,
amount_denom bigint NOT NULL,
type integer NOT NULL
);
Note: SQLite3 doesn't support date or timestamp data types, so in SQLite3 databases those fields are represented as CHAR[8] and CHAR[14] respectively.
