SQL

This page is for information about a SQL backend for GnuCash.

Here is the data model created by SQLite3:

CREATE TABLE versions (table_name text(50), table_version integer);
CREATE TABLE transactions (guid CHAR(32) PRIMARY KEY NOT NULL, currency_guid CHAR(32) NOT NULL, num text(2048) NOT NULL, post_date CHAR(14) NOT NULL, enter_date CHAR(14) NOT NULL, description text(2048));
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 CHAR(14) 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));
CREATE TABLE lots (guid CHAR(32) PRIMARY KEY NOT NULL, account_guid CHAR(32), is_closed integer NOT NULL);
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 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);
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));
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));
CREATE TABLE slots (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);
CREATE TABLE schedxactions (guid CHAR(32) PRIMARY KEY NOT NULL, name text(2048), enabled integer NOT NULL, start_date CHAR(8) NOT NULL, last_occur CHAR(8), 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);
CREATE TABLE prices (guid CHAR(32) PRIMARY KEY NOT NULL, commodity_guid CHAR(32) NOT NULL, currency_guid CHAR(32) NOT NULL, date CHAR(14) NOT NULL, source text(2048), type text(2048), value_num integer NOT NULL, value_denom integer NOT NULL);
CREATE TABLE books (guid CHAR(32) PRIMARY KEY NOT NULL, root_account_guid CHAR(32) NOT NULL, root_template_guid CHAR(32) NOT NULL);