SQL

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

Here is the data model created by src/backends/SQL:

CREATE TABLE gnclock (
    Hostname varchar(255), 
    PID int
);
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 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 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 books (
    guid                CHAR(32) PRIMARY KEY NOT NULL,
    root_account_guid   CHAR(32) NOT NULL,
    root_template_guid  CHAR(32) NOT NULL
);