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.
Blogger's formatting is horrendous!
ReplyDeletethanks for this info.. its true, blogger's formatting sucks.. but this info is hard to come across.. thanks!
ReplyDelete@Emi, if you write Perl code, you may also be interested in GnuCash::SQLite, a perl module created to provide a simpler interface than directly accessing the SQLite file. (See http://bit.ly/1v6OA0t)
Delete