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

Javascript - Unit Testing (with Jasmine) on a browser

This post is about unit testing Javascript using Jasmine using a browser.

1. Get Jasmine here.
2. Download and unpack -- mine is in ~/app/jasmine
3. Project directory should be something like:

4. Edit the SpecRunner.html file to include the source files and the test files:

5. The test file should not run the "require" code:

6. Run the tests by viewing on the browser:

Done.

To run tests on nodejs instead of via a browser, see this earlier post.

Javascript - Unit Testing (with Jasmine) using NodeJS

Here's how to implement unit testing (with Jasmine and Node) for a class defined earlier:

Source files in subfolder "src" and test files in subfolder "spec". All test files must end with ".spec.js". Execute the test like so:

Done.

To run tests via a browser instead, see this other post.

Perl - Unit Testing

Here's a screenshot how unit testing can be implemented in Perl for the class we defined earlier.

The testing file should be in the ./t subfolder. Execute the tests like so:

Done.

Perl - Inheritance

Here's how to do inheritance in Perl:

This uses the Moose object system which is should probably be used for all large Perl applications. Compare this against the Javascript version.

See this post on how to unit test the classes.

Javascript - Inheritance

Below is a diagram that summarizes how inheritance is implemented in Javascript:

This came from the Udacity course on OO Javascript by the way. Compare this against the Perl version of inheritance.

See this post on how to unit test the classes above.

How to add a transaction to GnuCash

From the last two posts, to add a new transaction to GnuCash, we need to:

1. Add 1 row to transactions table
2. Add 2 rows to splits table
3. Add 1 row to slots table

Let's tackle that one by one:

1. Add 1 row to transactions table

Here's the schema for accounts and transactions table:

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

Example data: 

guid            ded5efa287a34e862794b4277f8e0508

    System generated GUID.

    Use CPAN module DATA::GUID to generate this.

    Should check for uniqueness, or at least failure to insert.

    Save this GUID somewhere, it's needed to create rows for "splits" and "slots" tables.

currency_guid   be2788c5c017bb63c859430612e64093

    System obtains this a lookup on user provided "account name".

    For example, the account name is "Cash":

        SELECT commodity_guid FROM Accounts WHERE Accounts.name = "Cash"

num

    User provided value. If not provided, set to empty string '' (can't be null)

post_date       20140101170000

    User provided date in the form YYYYMMDD.

    IMPORTANT: 

    System takes T-1 from user date and appends "170000"

    Example, User provides 2014-01-01, System stores as 20131231170000

    ----

    Update: Turns out SQLite stores datetime in UTC timezone. 

            When provided a datetime of 2014-01-01 00:00:00 in local time of UTC +7:00.

            This is equivalent to UTC 2013-12-31 17:00:00

            which SQLite stores as 2013123117000

    ---- 

    A check on existing file shows this is valid:

        SELECT DISTINCT substr(post_date,9,6) FROM transactions;

    Use format YYYYMMDDhhmmss

enter_date      20141111071603

    System provided value.

    Timezone is GMT 00:00 (not local timezone)

    Use format YYYYMMDDhhmmss

description     Transfer

    User provided value. 

2. Add 2 rows to splits table
The schema for splits table:

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

Sample data: 

guid            47df2567833d70d7771c99b7dc47154e 

                f8f99cf7d8974bcba4e07d4cb9cb9d3e 

    System generate GUID.  

tx_guid         ded5efa287a34e862794b4277f8e0508 

                ded5efa287a34e862794b4277f8e0508  

    This is foreign key for the transaction GUID 

    Use the transaction GUID 

    Passed in as parameter to function that creates splits

    Implies sequence of processing that transaction must be created first 

account_guid   6a86047e3b12a6c4748fbf8fde76c0c0 

               6b870a6ef2c3fbbff0ec6df32108ac34  

    User provides account name.

    System looks up the account name. Example, account is Cash:

        SELECT guid from Accounts WHERE name = "Cash";

memo             

    Set as empty string ''  

action           

    Set as empty string '' 

reconcile_state n 

                n

     Default to "n"

reconcile_date  

    Set as empty string '' 

value_num       -100000 

                 100000

    User provides transaction amount up to 2 decimal places. Eg: 200.15

    System multiplies user value by 100. E.g. 200.15 becomes 20015

value_denom         100 

                    100  

    Default to 100.

quantity_num    -100000 

                 100000  

    Same as value_num field.

    Check using this SQL:

        SELECT count(*) FROM splits WHERE value_num <> quantity_num;

            Expect: 0 

        SELECT count(*) FROM splits WHERE value_num <> quantity_num;

            Expect: Same rowcount as rows in table 

quantity_denom      100 

                    100  

    Default to 100.

lot_guid         

    Set as empty string '' 

3. Add 1 row to slots table

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:

id integer          7 

    To insert into auto-incrementing fields see: http://bit.ly/1wOrWYE

    Basically, match columns and values:

       INSERT INTO participants(obj_guid, name,...) VALUES ('ded5..',"date-");

obj_guid            ded5efa287a34e862794b4277f8e0508 

    This is foreign key for the transaction GUID

    Use the transaction GUID for "transfer" transactions. 

    May be something else for other purposes.

    Passed in as parameter to function that creates splits

    Implies sequence of processing that transaction must be created first  

name                date-posted 

    Set as "date-posted" for transfer transactions.

slot_type           10 

    Set as "10" for "date-posted".

int64_val           0 

    Set as "10" for "date-posted".

string_val 

    Set as empty string '' for date-posted".  

double_val          0.0 

    Set as "0.0" for date-posted".  

timespec_val 

    Set as empty string '' for date-posted".  

guid_val 

    Set as empty string '' for date-posted".   

numeric_val_num     0 

    Set as "0" for date-posted".   

numeric_val_denom   1 

    Set as "1" for date-posted".  

gdate_val           20140102

    User provided transaction date.

    Note that the corresponding field transactions.post_date will be "gdate_val-1"

The way to check for default values to use for "date-posted" slot is: 

    SELECT DISTINCT name, slot_type, int64_val, string_val, double_val,

                    timespec_val, guid_val, numeric_val_num, numeric_val_denom

    FROM slots WHERE name in ("date-posted"); 

---------------- 

Update: There is now a Perl Module Gnucash::SQLite that provides a decent interface 

        to add transactions and check account balances.

What changes when we transfer funds between Asset Accounts?

The transfer is as follows:

With the following effect:

The database changes are:

** 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

Okay, the usual suspects:

1. Add 1 row to transactions table
2. Add 2 rows to splits table
3. Add 1 row to slots table

Initiate accounts with some balance - Adding an Equity account

Before we can do anything, there must be some cash in these accounts. To start, let's give ourselves some equity.

1. First create an "Equity" type account named "Equity"

Here's how things should look after completion:

2. Then add 10,000 to the Cash account, matched with entry in Equity on 01-Jan-2014.

And the following are our balances:

And here's the changes to the database:

** slots **
6|04a6f5e65289295457ba0b24d2de69fd|date-posted|10|0||0.0|||0|1|20140101

** splits **
1eb24bc760ee0c99a54c2a3cd36798f7|04a6f5e65289295457ba0b24d2de69fd|6a86047e3b12a6c4748fbf8fde76c0c0|||n||1000000|100|1000000|100|
6e87026b4db7f5ce5658e8ea402cae8d|04a6f5e65289295457ba0b24d2de69fd|dedeaf05873d7e92aa2a31dba73704d5|||n||-1000000|100|-1000000|100|

** transactions **
04a6f5e65289295457ba0b24d2de69fd|be2788c5c017bb63c859430612e64093||20131231170000|20141111064545|Initial Balance
Notes
1. Slots table gets inserted with a "date-posted" entry -- huh? See here, and here.
2. A row gets added to the transactions table
3. Two rows gets added to the splits table

What happens when a child account is added to the Asset?

Here's the screen that adds the account:

Changes to the tables as follows:

** accounts **
6a86047e3b12a6c4748fbf8fde76c0c0|Cash|ASSET|be2788c5c017bb63c859430612e64093|100|0|f81bd224897ba77e5998139d7cc50be5|1001|Cash Account|0|0

** slots **
3|6a86047e3b12a6c4748fbf8fde76c0c0|color|4|0|Not Set|0.0|||0|1|

Again an account is added together with a corresponding slot. It is also a THB-denominated account so the "commodities" table did not expand.

What happens when a top-level Asset account gets added?

The following rows are added in various tables:

** accounts **
f81bd224897ba77e5998139d7cc50be5|Assets|ASSET|be2788c5c017bb63c859430612e64093|100|0|1d61881fa764645df6f203834c0c3030|||0|0

** commodities **
be2788c5c017bb63c859430612e64093|CURRENCY|THB|Baht|764|100|1|currency|

** slots **
1|f81bd224897ba77e5998139d7cc50be5|color|4|0|Not Set|0.0|||0|1|

See http://wiki.gnucash.org/wiki/images/8/86/Gnucash_erd.png for the ER diagram for each table. Printing it out in color did much good for me.

1. No idea what "slots" table does.
2. Commodities table simply says that the account is of currency THB
3. We've added an Account named "Asset"

Dump the contents in a GnuCash file

Here's the perl code to dump the contents in a GnuCash file:

#!/usr/bin/env perl
# regc.pl - Reverse Engineer GnuCash

use strict;
use warnings;
use 5.10.0;
use DBI;
use Data::Dumper;

# Global Variables
my $gc_file = 'empty.gnucash';
my $dbh = DBI->connect("dbi:SQLite:dbname=$gc_file","","");

my $t = tables($gc_file);
say table_contents($t);

sub tables {
    my $file = shift;
    my $tables = [];

    my $res = `echo .schema | sqlite3 $file`;
    foreach my $t (split /;/, $res) {
        next unless $t =~ /CREATE TABLE (.*) \(/;
        push @{$tables}, $1;
    }
   
    return $tables;
}

# Given a list of tables, dump the contents of the tables to screen
sub table_contents {
    my $tables = shift;
    my $content = '';

    foreach my $t (sort @{$tables}) {
        $content .= "** $t **\n";
        my $sql = "SELECT * from $t;";
        my $res = `echo "$sql" | sqlite3 $gc_file`;
        $content .= $res . "\n";
    }
    return $content;
}

An empty GnuCash file has the following contents. Where double asterisks shows the table name:

** accounts **
1d61881fa764645df6f203834c0c3030|Root Account|ROOT||0|0||||0|0
d6b5387e81f169a5d4fbabca7130b9f4|Template Root|ROOT||0|0||||0|0

** billterms **

** books **
4e9e052c3dfc6c82d3f24f654ff80229|1d61881fa764645df6f203834c0c3030|d6b5387e81f169a5d4fbabca7130b9f4

** budget_amounts **

** budgets **

** commodities **

** customers **

** employees **

** entries **

** gnclock **
i3|6315

** invoices **

** jobs **

** lots **

** orders **

** prices **

** recurrences **

** schedxactions **

** slots **

** splits **

** taxtable_entries **

** taxtables **

** transactions **

** vendors **

** versions **
Gnucash|2041300
Gnucash-Resave|19920
accounts|1
books|1
budgets|1
budget_amounts|1
commodities|1
lots|2
prices|2
schedxactions|1
transactions|3
splits|4
billterms|2
customers|2
employees|2
entries|3
invoices|3
jobs|1
orders|1
taxtables|2
taxtable_entries|3
vendors|1
recurrences|2
slots|3

As you can see, this is Gnucash v2.4.13.

Getting a list of tables in GnuCash

Here's some perl code to show a list of tables in a GnuCash file:

#!/usr/bin/env perl
# regc.pl - Reverse Engineer GnuCash

use strict;
use warnings;
use 5.10.0;
use DBI;
use Data::Dumper;

# Global Variables
my $gc_file = 'empty.gnucash';
my $dbh = DBI->connect("dbi:SQLite:dbname=$gc_file","","");

say Dumper tables($gc_file);

sub tables {
    my $file = shift;
    my $tables = [];

    my $res = `echo .schema | sqlite3 $file`;
    foreach my $t (split /;/, $res) {
        next unless $t =~ /CREATE TABLE (.*) \(/;
        push @{$tables}, $1;
    }
   
    return $tables;
}

 The list of tables are:

          'gnclock',
          'versions',
          'accounts',
          'books',
          'budgets',
          'budget_amounts',
          'commodities',
          'lots',
          'prices',
          'schedxactions',
          'transactions',
          'splits',
          'billterms',
          'customers',
          'employees',
          'entries',
          'invoices',
          'jobs',
          'orders',
          'taxtables',
          'taxtable_entries',
          'vendors',
          'recurrences',
          'slots'

GnuCash E-R diagram

The ER diagram is located here:

http://wiki.gnucash.org/wiki/images/8/86/Gnucash_erd.png