Saving with new mysql 5.7 triggers in db

John Ralls jralls at ceridwen.fremont.ca.us
Mon May 15 10:25:41 EDT 2017


> On May 14, 2017, at 9:22 AM, Ciarán <ciarandbrowne at gmail.com> wrote:
> 
> Hi all,
> 
> I'm using GNUCash for home accounts for several years and I love it! Perfect
> for an old time accountant! Anyway the reporting has always bothered me so
> I've built an OBIEE 11g solution with mysql 5.7
> 
> I have a simple solution which only uses three tables (accounts,splits &
> transactions) and it works like a charm. However building time hierarchies
> in OBI requires some additional db changes in GNUCash to normalize the data
> 
> Year, month, day
> 
> ALTER TABLE gnucash.transactions
> ADD post_year YEAR(4);
> 
> ALTER TABLE gnucash.transactions
> ADD post_day INT(2);
> 
> ALTER TABLE gnucash.transactions
> ADD post_month INT(2);
> 
> UPDATE gnucash.transactions
> SET post_year = year(post_date);
> 
> UPDATE gnucash.transactions
> SET post_month = month(post_date);
> 
> UPDATE gnucash.transactions
> SET post_day = day(post_date);
> 
> This works great and is robust throughout saves.
> 
> Next to automate the updates I created a trigger
> 
> DELIMITER $$
> CREATE TRIGGER ins_transactions
> AFTER INSERT ON gnucash.transactions 
> FOR EACH ROW
> BEGIN
> INSERT INTO gnucash.transactions
> SET ACTION = 'UPDATE',
> 	post_year = year(post_date),
>    post_month = month(post_date),
>    post_day = day(post_date);
> END$$
> DELIMITER ;
> 
> However from this point onwards GNUcash will not save when new transcations
> are created.
> 
> A msgbox appears with unable to save to database. 
> 
> To resolve, I have re-save the db file overwriting the additional columns in
> the transactions table.
> 
> and... back to square one.
> 
> Any ideas how to maintain new mysql triggers on save in gnucash? Is this
> possible?

GnuCash doesn't at present use the database as a database, it uses it as an object store. It reads the database exactly once per session, at session startup. GnuCash can't see the changes made by your trigger. Worse, you've altered the schema so GnuCash's INSERT queries no longer work. To make your triggers work create a new table TXN_POST_DMY with fields txn_guid (primary key, foreign key on transactions:guid), year, month, and day and fill *that* in with your trigger. You'll need another trigger to handle deletions so that you don't get foreign key errors when GnuCash does a DELETE query on a transaction.

While we do intend to eventually have GnuCash use the database as a database, we will probably never support using server-side computation with triggers because all of the data integrity code is in GnuCash itself and there's no way to apply that from a trigger and triggers are anyway not portable between different database providers (i.e. MySQL/MariaDB, Postgresql, and SQLite3).

Why on earth would you want separate redundant columns on month, day, and year for post_date anyway? You can use year(), month(), and day() in queries so there's no need to mess with the schema or add tables.

Regards,
John Ralls



More information about the gnucash-devel mailing list