How to add a transaction programmatically to GnuCash - SQLs

This post describes the SQLs needed to add a transaction that captures the transfer of money from one asset account (Cash) to another asset account (aBank).

Refer to this post and this post for background details that feeds into this post.

Update: I've created a perl module, GnuCash::SQLite to simplify the process of adding transactions to Gnucash.

Caveats:

1. SQLs below are for transferring money from one Asset account to another Asset account. For transactions to/from Income or Expense accounts, it may be different, so beware.
2. Assumes single currency.

Input data

Input data structure as follows: {
    tx_date        : 20140102
    tx_description : Transfer
    tx_from_acct   : Cash
    tx_to_acct     : Assets:aBank
    tx_amt         : 1000
    tx_num         : (typically blank, as in this case)
}

Then add derived information as follows: {
    tx_guid        : gen_guid(32),
    tx_currency    : lookup_currency_guid(tx_from_acct),
    tx_post_date   : gen_post_date(tx_date),
    tx_enter_date  : system_timestamp(),
    tx_from_guid   : lookup_account_guid(tx_from_acct),

    tx_to_guid     : lookup_account_guid(tx_to_acct),
    tx_from_numer  : tx_amt * -100,
    tx_to_numer    : tx_amt * 100
}

SQL #1: Insert into Transactions table

INSERT INTO transactions VALUES ( 
    tx_guid,
    tx_currency,
    tx_num,
    tx_post_date,
    tx_enter_date,
    tx_description
);

SQL #2: Insert 2 rows into splits table

For splits, create additional derived data: {
    splt_guid_1 : gen_guid(32),
    splt_guid_2 : gen_guid(32)

}

INSERT INTO splits VALUES (
    splt_guid_1,
    tx_guid,
    tx_from_guid,
    '',
    '',
    'n',
    '',
    tx_from_numer,
    100,
    tx_from_numer,
    100,
    null
);

INSERT INTO splits VALUES (
    splt_guid_2,
    tx_guid,
    tx_to_guid,
    '',
    '',
    'n',
    '',
    tx_to_numer,
    100,
    tx_to_numer,
    100,
    null
);

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

SQL #3: Insert 1 row into slots table

INSERT INTO slots (
    obj_guid,
    name,
    slot_type,
    int64_val,
    string_val,
    double_val,
    timespec_val,
    guid_val,
    numeric_val_num,
    numeric_val_denom,
    gdate_val
) VALUES (
    tx_guid,
    'date-posted',
    '10',
    '0',
    '',
    '0.0',
    '',
    '',
    '0',
    '1',
    tx_date
)

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           text(8) 

);

----
Sample data below. See the GnuCash series on what the data below means.

** slots **
7|ded5efa287a34e862794b4277f8e0508|date-posted|10|0||0.0|||0|1|20140102 

** splits ** 
47df2567833d70d7771c99b7dc47154e|ded5efa287a34e862794b4277f8e0508|6a86047e3b12a6c4748fbf8fde76c0c0|||n||-100000|100|-100000|100| 

f8f99cf7d8974bcba4e07d4cb9cb9d3e|ded5efa287a34e862794b4277f8e0508|6b870a6ef2c3fbbff0ec6df32108ac34|||n||100000|100|100000|100| 

** transactions ** 
ded5efa287a34e862794b4277f8e0508|be2788c5c017bb63c859430612e64093||20140101170000|20141111071603|Transfer