Difference between revisions of "SQL"

From GnuCash
Jump to: navigation, search
m (None)
(Version and backend info)
 
(14 intermediate revisions by 7 users not shown)
Line 1: Line 1:
This page is for information about a SQL backend for GnuCash.
+
[[Category:SQL]]
 
+
Since version 2.4 GnuCash supports SQL via the DBI backend. That again supports PostgreSQL, MySQL and SQLite3 databases.
 +
This page contains details about the SQL backend of GnuCash.
  
 +
==Entity Relationship Model==
 
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.
 
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 [[https://github.com/downloads/jralls/gnucash/gnucash_erd.png Entity-Relationship Diagram]] does.
+
This [[Media:gnucash_erd.png|Entity-Relationship Diagram]] does.
  
 +
=Tables=
 +
==Gnclock==
 
Locking table, should have zero or one record. Provides weak protection against simultaneous access.
 
Locking table, should have zero or one record. Provides weak protection against simultaneous access.
<pre>
+
<syntaxhighlight lang="SQL">
 
CREATE TABLE gnclock (
 
CREATE TABLE gnclock (
 
     Hostname varchar(255),  
 
     Hostname varchar(255),  
 
     PID int
 
     PID int
 
);
 
);
&lt;/pre>
+
</syntaxhighlight>
 +
 
 +
==Versions==
 
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.
 
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.
&lt;pre>
+
<syntaxhighlight lang="SQL">
 
CREATE TABLE versions (
 
CREATE TABLE versions (
 
     table_name      text(50),
 
     table_name      text(50),
 
     table_version  integer
 
     table_version  integer
 
);
 
);
&lt;/pre>
+
</syntaxhighlight>
 +
 
 +
==Books==
 
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.
 
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.
&lt;pre>
+
<syntaxhighlight lang="SQL">
 
CREATE TABLE books (
 
CREATE TABLE books (
 
     guid                CHAR(32) PRIMARY KEY NOT NULL,
 
     guid                CHAR(32) PRIMARY KEY NOT NULL,
Line 27: Line 35:
 
     root_template_guid  CHAR(32) NOT NULL
 
     root_template_guid  CHAR(32) NOT NULL
 
);
 
);
&lt;/pre>
+
</syntaxhighlight>
 +
 
 +
==Commodities==
 
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.
 
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.
&lt;pre>
+
<syntaxhighlight lang="SQL">
 
CREATE TABLE commodities (
 
CREATE TABLE commodities (
 
     guid            CHAR(32) PRIMARY KEY NOT NULL,
 
     guid            CHAR(32) PRIMARY KEY NOT NULL,
Line 41: Line 51:
 
     quote_tz        text(2048)
 
     quote_tz        text(2048)
 
);
 
);
&lt;/pre>
+
</syntaxhighlight>
 +
 
 +
==Prices==
 
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.
 
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.
&lt;pre>
+
<syntaxhighlight lang="SQL">
 
CREATE TABLE prices (
 
CREATE TABLE prices (
 
     guid                CHAR(32) PRIMARY KEY NOT NULL,
 
     guid                CHAR(32) PRIMARY KEY NOT NULL,
 
     commodity_guid      CHAR(32) NOT NULL,
 
     commodity_guid      CHAR(32) NOT NULL,
 
     currency_guid      CHAR(32) NOT NULL,
 
     currency_guid      CHAR(32) NOT NULL,
     date                CHAR(14) NOT NULL,
+
     date                timestamp NOT NULL,
 
     source              text(2048),
 
     source              text(2048),
 
     type                text(2048),
 
     type                text(2048),
Line 54: Line 66:
 
     value_denom        integer NOT NULL
 
     value_denom        integer NOT NULL
 
);
 
);
&lt;/pre>
+
</syntaxhighlight>
 +
 
 +
==Accounts==
 
Accounts are the containing entity for transactions
 
Accounts are the containing entity for transactions
&lt;pre>
+
<syntaxhighlight lang="SQL">
 
CREATE TABLE accounts (
 
CREATE TABLE accounts (
 
     guid            CHAR(32) PRIMARY KEY NOT NULL,
 
     guid            CHAR(32) PRIMARY KEY NOT NULL,
Line 66: Line 80:
 
     parent_guid    CHAR(32),
 
     parent_guid    CHAR(32),
 
     code            text(2048),
 
     code            text(2048),
     description    text(2048)
+
     description    text(2048),
 +
    hidden          integer NOT NULL,
 +
    placeholder    integer NOT NULL
 
);
 
);
&lt;/pre>
+
</syntaxhighlight>
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 <span class="plainlinks">[http://www.thepiggybackrider.com/ <span style="color:black;font-weight:normal; text-decoration:none!important; background:none!important; text-decoration:none;/*CITATION*/">kid carrier</span>]</span> 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.
+
 
 +
==Slots==
 +
Slots is a sort of catch all for a variety of data lacking a table. A top-level slot will have 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.  
 
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.  
&lt;pre>
+
<syntaxhighlight lang="SQL">
 
CREATE TABLE slots (
 
CREATE TABLE slots (
 
     id integer PRIMARY KEY AUTOINCREMENT NOT NULL,
 
     id integer PRIMARY KEY AUTOINCREMENT NOT NULL,
Line 84: Line 102:
 
     guid_val            CHAR(32),
 
     guid_val            CHAR(32),
 
     numeric_val_num    integer,
 
     numeric_val_num    integer,
     numeric_val_denom  integer
+
     numeric_val_denom  integer,
 +
    gdate_val          date
 
);
 
);
&lt;/pre>
+
</syntaxhighlight>
 +
 
 +
==Transactions==
 
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.
 
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.
&lt;pre>
+
<syntaxhighlight lang="SQL">
 
CREATE TABLE transactions (
 
CREATE TABLE transactions (
 
     guid            CHAR(32) PRIMARY KEY NOT NULL,
 
     guid            CHAR(32) PRIMARY KEY NOT NULL,
 
     currency_guid  CHAR(32) NOT NULL,
 
     currency_guid  CHAR(32) NOT NULL,
 
     num            text(2048) NOT NULL,
 
     num            text(2048) NOT NULL,
     post_date      CHAR(14) NOT NULL,
+
     post_date      timestamp NOT NULL,
     enter_date      CHAR(14) NOT NULL,
+
     enter_date      timestamp NOT NULL,
 
     description    text(2048)
 
     description    text(2048)
 
);
 
);
&lt;/pre>
+
</syntaxhighlight>
 +
 
 +
==Splits==
 
Splits tie an amount of a commodity to a single account in a transaction.
 
Splits tie an amount of a commodity to a single account in a transaction.
&lt;pre>
+
<syntaxhighlight lang="SQL">
 
CREATE TABLE splits (
 
CREATE TABLE splits (
 
     guid            CHAR(32) PRIMARY KEY NOT NULL,
 
     guid            CHAR(32) PRIMARY KEY NOT NULL,
Line 107: Line 130:
 
     action          text(2048) NOT NULL,
 
     action          text(2048) NOT NULL,
 
     reconcile_state text(1) NOT NULL,
 
     reconcile_state text(1) NOT NULL,
     reconcile_date  CHAR(14) NOT NULL,
+
     reconcile_date  timestamp NOT NULL,
 
     value_num      integer NOT NULL,
 
     value_num      integer NOT NULL,
 
     value_denom    integer NOT NULL,
 
     value_denom    integer NOT NULL,
Line 114: Line 137:
 
     lot_guid        CHAR(32)
 
     lot_guid        CHAR(32)
 
);
 
);
&lt;/pre>
+
</syntaxhighlight>
 +
 
 +
==Lots==
 
Lots are used to tie buy and sell transactions together (the transaction guids are contained in slots) to compute capital gains and losses.
 
Lots are used to tie buy and sell transactions together (the transaction guids are contained in slots) to compute capital gains and losses.
&lt;pre>
+
<syntaxhighlight lang="SQL">
 
CREATE TABLE lots (
 
CREATE TABLE lots (
 
     guid            CHAR(32) PRIMARY KEY NOT NULL,
 
     guid            CHAR(32) PRIMARY KEY NOT NULL,
Line 122: Line 147:
 
     is_closed      integer NOT NULL
 
     is_closed      integer NOT NULL
 
);
 
);
&lt;/pre>
+
</syntaxhighlight>
 +
 
 +
==Budgets==
 
Budgets are the top level entities for, well, budgeting.
 
Budgets are the top level entities for, well, budgeting.
&lt;pre>
+
<syntaxhighlight lang="SQL">
 
CREATE TABLE budgets (
 
CREATE TABLE budgets (
 
     guid            CHAR(32) PRIMARY KEY NOT NULL,
 
     guid            CHAR(32) PRIMARY KEY NOT NULL,
Line 131: Line 158:
 
     num_periods    integer NOT NULL
 
     num_periods    integer NOT NULL
 
);
 
);
 +
 +
==Budget_Amounts==
 
CREATE TABLE budget_amounts (
 
CREATE TABLE budget_amounts (
 
     id integer PRIMARY KEY AUTOINCREMENT NOT NULL,
 
     id integer PRIMARY KEY AUTOINCREMENT NOT NULL,
Line 139: Line 168:
 
     amount_denom bigint NOT NULL
 
     amount_denom bigint NOT NULL
 
);
 
);
 +
 +
==Recurrences==
 
CREATE TABLE recurrences (
 
CREATE TABLE recurrences (
 
     obj_guid                CHAR(32) NOT NULL,
 
     obj_guid                CHAR(32) NOT NULL,
Line 145: Line 176:
 
     recurrence_period_start CHAR(8) NOT NULL
 
     recurrence_period_start CHAR(8) NOT NULL
 
);
 
);
&lt;/pre>
+
</syntaxhighlight>
 +
==Scheduled transactions==
 
Scheduled transactions enable automatic creation of real transaction periodically in the future.
 
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.
 
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.
&lt;pre>
+
<syntaxhighlight lang="SQL">
 
CREATE TABLE schedxactions (
 
CREATE TABLE schedxactions (
 
     guid                CHAR(32) PRIMARY KEY NOT NULL,
 
     guid                CHAR(32) PRIMARY KEY NOT NULL,
 
     name                text(2048),
 
     name                text(2048),
 
     enabled            integer NOT NULL,
 
     enabled            integer NOT NULL,
     start_date          CHAR(8) NOT NULL,
+
     start_date          date NOT NULL,
     last_occur          CHAR(8),
+
    end_date            date,
 +
     last_occur          date,
 
     num_occur          integer NOT NULL,
 
     num_occur          integer NOT NULL,
 
     rem_occur          integer NOT NULL,
 
     rem_occur          integer NOT NULL,
Line 164: Line 197:
 
     template_act_guid  CHAR(32) NOT NULL
 
     template_act_guid  CHAR(32) NOT NULL
 
);
 
);
&lt;/pre>
+
</syntaxhighlight>
Business tables:
+
 
&lt;pre>
+
=Business tables=
 +
Many records have both an id and a guid: customers, vendors, invoices being examples.  The id is arbitrary text and (especially with bills) there is no guarantee they aren't duplicates.  These are user-facing, visible on the printed version of the invoice or bill, and used as search strings in the find functions.  The guid is unique at least within table, and is used internally as a serializable form of a pointer.
 +
 
 +
==Entries==
 +
Entries represent lines in invoices, bills or employee expense vouchers.  Invoice entries have all the expected fields; bill entries have all the corresponding fields for bills, as well as a complete set for an invoice.  The latter are used in a charge-back scenario.  Employee expense vouchers are essentially bills, with owners that are employees.
 +
<syntaxhighlight lang="SQL">
 
CREATE TABLE entries (
 
CREATE TABLE entries (
 
     guid text(32) PRIMARY KEY NOT NULL,
 
     guid text(32) PRIMARY KEY NOT NULL,
Line 200: Line 238:
 
     order_guid text(32)
 
     order_guid text(32)
 
);
 
);
 +
</syntaxhighlight>
 +
 +
==Customers==
 +
Most of the information in customer records is only stored and used in reports.  The guid is referenced as the owner of an invoice.
 +
<syntaxhighlight lang="SQL">
 
CREATE TABLE customers (
 
CREATE TABLE customers (
 
     guid text(32) PRIMARY KEY NOT NULL,
 
     guid text(32) PRIMARY KEY NOT NULL,
Line 232: Line 275:
 
     taxtable text(32)
 
     taxtable text(32)
 
);
 
);
 +
</syntaxhighlight>
 +
 +
==Employees==
 +
<syntaxhighlight lang="SQL">
 
CREATE TABLE employees (
 
CREATE TABLE employees (
 
     guid text(32) PRIMARY KEY NOT NULL,
 
     guid text(32) PRIMARY KEY NOT NULL,
Line 254: Line 301:
 
     addr_email text(256)
 
     addr_email text(256)
 
);
 
);
 +
</syntaxhighlight>
 +
 +
==Vendors==
 +
Vendors are to bills as customers are to invoices.
 +
<syntaxhighlight lang="SQL">
 
CREATE TABLE vendors (
 
CREATE TABLE vendors (
 
     guid text(32) PRIMARY KEY NOT NULL,
 
     guid text(32) PRIMARY KEY NOT NULL,
Line 274: Line 326:
 
     tax_table text(32)
 
     tax_table text(32)
 
);
 
);
 +
</syntaxhighlight>
 +
 +
==Orders==
 +
There doesn't appear to be any user documentation of features that use this?
 +
<syntaxhighlight lang="SQL">
 
CREATE TABLE orders (
 
CREATE TABLE orders (
 
     guid text(32) PRIMARY KEY NOT NULL,
 
     guid text(32) PRIMARY KEY NOT NULL,
Line 285: Line 342:
 
     owner_guid text(32) NOT NULL
 
     owner_guid text(32) NOT NULL
 
);
 
);
 +
</syntaxhighlight>
 +
 +
==Jobs==
 +
<syntaxhighlight lang="SQL">
 
CREATE TABLE jobs (
 
CREATE TABLE jobs (
 
     guid text(32) PRIMARY KEY NOT NULL,
 
     guid text(32) PRIMARY KEY NOT NULL,
Line 293: Line 354:
 
     owner_type integer, owner_guid text(32)
 
     owner_type integer, owner_guid text(32)
 
);
 
);
 +
</syntaxhighlight>
 +
==Invoices==
 +
Owner_type indicates whether this is a bill, an invoice or an expense voucher. owner_guid points to the vendor, customer or employee, respectively. 
 +
<syntaxhighlight lang="SQL">
 
CREATE TABLE invoices (
 
CREATE TABLE invoices (
 
     guid text(32) PRIMARY KEY NOT NULL,
 
     guid text(32) PRIMARY KEY NOT NULL,
 
     id text(2048) NOT NULL,
 
     id text(2048) NOT NULL,
     date_opened text(14),
+
     date_opened timestamp,
     date_posted text(14),
+
     date_posted timestamp,
 
     notes text(2048) NOT NULL,
 
     notes text(2048) NOT NULL,
 
     active integer NOT NULL,
 
     active integer NOT NULL,
Line 313: Line 378:
 
     charge_amt_denom bigint
 
     charge_amt_denom bigint
 
);
 
);
 +
</syntaxhighlight>
 +
 +
==Billing Terms==
 +
<syntaxhighlight lang="SQL">
 
CREATE TABLE billterms (
 
CREATE TABLE billterms (
 
     guid text(32) PRIMARY KEY NOT NULL,
 
     guid text(32) PRIMARY KEY NOT NULL,
Line 327: Line 396:
 
     cutoff integer
 
     cutoff integer
 
);
 
);
 +
</syntaxhighlight>
 +
 +
==Tax Tables==
 +
<syntaxhighlight lang="SQL">
 
CREATE TABLE taxtables (
 
CREATE TABLE taxtables (
 
     guid text(32) PRIMARY KEY NOT NULL,
 
     guid text(32) PRIMARY KEY NOT NULL,
Line 334: Line 407:
 
     parent text(32)
 
     parent text(32)
 
);
 
);
 +
</syntaxhighlight>
 +
 +
==Entries within tax tables==
 +
<syntaxhighlight lang="SQL">
 
CREATE TABLE taxtable_entries (
 
CREATE TABLE taxtable_entries (
 
     id integer PRIMARY KEY AUTOINCREMENT NOT NULL,
 
     id integer PRIMARY KEY AUTOINCREMENT NOT NULL,
Line 342: Line 419:
 
     type integer NOT NULL
 
     type integer NOT NULL
 
);
 
);
&lt;/pre>
+
</syntaxhighlight>
 +
 
 +
'''Note:''' SQLite3 doesn't support date or timestamp data types, so in SQLite3 databases those fields are represented as <tt>CHAR[8]</tt> and <tt>CHAR[14]</tt> respectively.

Latest revision as of 04:28, 20 April 2024

Since version 2.4 GnuCash supports SQL via the DBI backend. That again supports PostgreSQL, MySQL and SQLite3 databases. This page contains details about the SQL backend of GnuCash.

Entity Relationship Model

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.

Tables

Gnclock

Locking table, should have zero or one record. Provides weak protection against simultaneous access.

CREATE TABLE gnclock (
    Hostname varchar(255), 
    PID int
);

Versions

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
);

Books

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

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

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

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),
    hidden          integer NOT NULL,
    placeholder     integer NOT NULL
);

Slots

Slots is a sort of catch all for a variety of data lacking a table. A top-level slot will have 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,
    gdate_val           date
);

Transactions

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

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

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

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
);

==Budget_Amounts==
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
);

==Recurrences==
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

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,
    end_date            date,
    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

Many records have both an id and a guid: customers, vendors, invoices being examples. The id is arbitrary text and (especially with bills) there is no guarantee they aren't duplicates. These are user-facing, visible on the printed version of the invoice or bill, and used as search strings in the find functions. The guid is unique at least within table, and is used internally as a serializable form of a pointer.

Entries

Entries represent lines in invoices, bills or employee expense vouchers. Invoice entries have all the expected fields; bill entries have all the corresponding fields for bills, as well as a complete set for an invoice. The latter are used in a charge-back scenario. Employee expense vouchers are essentially bills, with owners that are employees.

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)
);

Customers

Most of the information in customer records is only stored and used in reports. The guid is referenced as the owner of an invoice.

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)
);

Employees

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)
);

Vendors

Vendors are to bills as customers are to invoices.

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)
);

Orders

There doesn't appear to be any user documentation of features that use this?

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
);

Jobs

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)
);

Invoices

Owner_type indicates whether this is a bill, an invoice or an expense voucher. owner_guid points to the vendor, customer or employee, respectively.

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
);

Billing Terms

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
);

Tax Tables

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)
);

Entries within tax tables

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.