Saving with new mysql 5.7 triggers in db

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Saving with new mysql 5.7 triggers in db

Ciarán
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?


Reply | Threaded
Open this post in threaded view
|

Re: Saving with new mysql 5.7 triggers in db

Geert Janssens-4
On zondag 14 mei 2017 18:22:24 CEST Ciarán 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?

I don't have much experience with mysql triggers. However it looks to me your
trigger is a bit odd. You specify that after the new row is inserted the
trigger should insert something again, but don't specify a full row. I don't
think you want to insert another row in the same table. You want to alter the
row being inserted.

Rather than a trigger AFTER INSERT, I'd use a BEFORE INSERT and in your BEGIN/
END block simply set the fields as you want. Something like this (not tested):

DELIMITER $$
CREATE TRIGGER ins_transactions
BEFORE INSERT ON gnucash.transactions
FOR EACH ROW
BEGIN
   SET NEW.post_year = year(NEW.post_date);
   SET NEW.post_month = month(NEW.post_date);
   SET NEW.post_day = day(NEW.post_date);
END$$
DELIMITER ;

Geert
_______________________________________________
gnucash-devel mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Reply | Threaded
Open this post in threaded view
|

Re: Saving with new mysql 5.7 triggers in db

Ciarán
Hi Geert,

I don't have much experience with MySQL triggers either ;) But your solution works!

Thanks a million!

Ciarán
Reply | Threaded
Open this post in threaded view
|

Re: Saving with new mysql 5.7 triggers in db

John Ralls
In reply to this post by Ciarán

> On May 14, 2017, at 9:22 AM, Ciarán <[hidden email]> 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

_______________________________________________
gnucash-devel mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Reply | Threaded
Open this post in threaded view
|

Re: Saving with new mysql 5.7 triggers in db

Derek Atkins
In reply to this post by Geert Janssens-4
Geert Janssens <[hidden email]> writes:

> On zondag 14 mei 2017 18:22:24 CEST Ciarán wrote:
>> 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?
>
> I don't have much experience with mysql triggers. However it looks to me your
> trigger is a bit odd. You specify that after the new row is inserted the
> trigger should insert something again, but don't specify a full row. I don't
> think you want to insert another row in the same table. You want to alter the
> row being inserted.

Is it possible that GnuCash just isn't being happy with the "modified"
tables?

-derek
--
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       [hidden email]                        PGP key available
_______________________________________________
gnucash-devel mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Reply | Threaded
Open this post in threaded view
|

Re: Saving with new mysql 5.7 triggers in db

Geert Janssens-4
On maandag 15 mei 2017 17:24:45 CEST Derek Atkins wrote:

> Geert Janssens <[hidden email]> writes:
> > On zondag 14 mei 2017 18:22:24 CEST Ciarán wrote:
> >> 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?
> >
> > I don't have much experience with mysql triggers. However it looks to me
> > your trigger is a bit odd. You specify that after the new row is inserted
> > the trigger should insert something again, but don't specify a full row.
> > I don't think you want to insert another row in the same table. You want
> > to alter the row being inserted.
>
> Is it possible that GnuCash just isn't being happy with the "modified"
> tables?
>
Possibly and that's what John also suggests. I don't know the exact details of
how the sql backend interacts with the tables. I just know there are ways in
general that you can have extra columns in an sql table than there are columns
used in an insert/update query. When columns are omitted they are normally set
to their default value on insert or ignored on an update. I used that idea to
suggest my alternative trigger.

On the other hand if the sql backend for some reason sets restrictions on the
available columns this may be an issue. Only a real test can tell but I'm not
interested enough right now to spend the effort. On the other hand if someone
else is, I would appreciate to hear the result.

And John's other remark was even more to the point. Why not use the year(),
month() and day() function directly in the queries instead of duplicating the
info in the table ?

Geert
_______________________________________________
gnucash-devel mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Reply | Threaded
Open this post in threaded view
|

Re: Saving with new mysql 5.7 triggers in db

Derek Atkins
Geert Janssens <[hidden email]> writes:

>> Is it possible that GnuCash just isn't being happy with the "modified"
>> tables?
>>
> Possibly and that's what John also suggests. I don't know the exact details of
> how the sql backend interacts with the tables. I just know there are ways in
> general that you can have extra columns in an sql table than there are columns
> used in an insert/update query. When columns are omitted they are normally set
> to their default value on insert or ignored on an update. I used that idea to
> suggest my alternative trigger.
>
> On the other hand if the sql backend for some reason sets restrictions on the
> available columns this may be an issue. Only a real test can tell but I'm not
> interested enough right now to spend the effort. On the other hand if someone
> else is, I would appreciate to hear the result.
>
> And John's other remark was even more to the point. Why not use the year(),
> month() and day() function directly in the queries instead of duplicating the
> info in the table ?

I haven't looked at the code but inserts can be done implicitly or
explicitly.  If they are done implicitly then any change to the table
could cause problems on future inserts.  I don't know if GnuCash uses
implicit or explicit insertions.

> Geert

-derek

--
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       [hidden email]                        PGP key available
_______________________________________________
gnucash-devel mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Reply | Threaded
Open this post in threaded view
|

Re: Saving with new mysql 5.7 triggers in db

John Ralls

> On May 15, 2017, at 8:49 AM, Derek Atkins <[hidden email]> wrote:
>
> Geert Janssens <[hidden email] <mailto:[hidden email]>> writes:
>
>>> Is it possible that GnuCash just isn't being happy with the "modified"
>>> tables?
>>>
>> Possibly and that's what John also suggests. I don't know the exact details of
>> how the sql backend interacts with the tables. I just know there are ways in
>> general that you can have extra columns in an sql table than there are columns
>> used in an insert/update query. When columns are omitted they are normally set
>> to their default value on insert or ignored on an update. I used that idea to
>> suggest my alternative trigger.
>>
>> On the other hand if the sql backend for some reason sets restrictions on the
>> available columns this may be an issue. Only a real test can tell but I'm not
>> interested enough right now to spend the effort. On the other hand if someone
>> else is, I would appreciate to hear the result.
>>
>> And John's other remark was even more to the point. Why not use the year(),
>> month() and day() function directly in the queries instead of duplicating the
>> info in the table ?
>
> I haven't looked at the code but inserts can be done implicitly or
> explicitly.  If they are done implicitly then any change to the table
> could cause problems on future inserts.  I don't know if GnuCash uses
> implicit or explicit insertions.

If "implicit" means "whole record with fields in order and not tagged with field names" that's what GnuCash does.

Regards,
John Ralls

_______________________________________________
gnucash-devel mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Reply | Threaded
Open this post in threaded view
|

Re: Saving with new mysql 5.7 triggers in db

Ciarán
In reply to this post by John Ralls
Separate columns are required for building a Level based hierarchy in the Presentation layer in OBIEE. This is the middleware I use for reporting from GNUCash.

Reply | Threaded
Open this post in threaded view
|

Re: Saving with new mysql 5.7 triggers in db

Derek Atkins-3
In reply to this post by John Ralls
   Hi,
   On May 15, 2017 12:20 PM, John Ralls <[hidden email]>
   wrote:

   On May 15, 2017, at 8:49 AM, Derek Atkins <[1][hidden email]> wrote:

   Geert Janssens <[2][hidden email]> writes:

     Is it possible that GnuCash just isn't being happy with the
     "modified"
     tables?

     Possibly and that's what John also suggests. I don't know the exact
     details of
     how the sql backend interacts with the tables. I just know there are
     ways in
     general that you can have extra columns in an sql table than there
     are columns
     used in an insert/update query. When columns are omitted they are
     normally set
     to their default value on insert or ignored on an update. I used
     that idea to
     suggest my alternative trigger.
     On the other hand if the sql backend for some reason sets
     restrictions on the
     available columns this may be an issue. Only a real test can tell
     but I'm not
     interested enough right now to spend the effort. On the other hand
     if someone
     else is, I would appreciate to hear the result.
     And John's other remark was even more to the point. Why not use the
     year(),
     month() and day() function directly in the queries instead of
     duplicating the
     info in the table ?

   I haven't looked at the code but inserts can be done implicitly or
   explicitly.  If they are done implicitly then any change to the table
   could cause problems on future inserts.  I don't know if GnuCash uses
   implicit or explicit insertions.

   If "implicit" means "whole record with fields in order and not tagged
   with field names" that's what GnuCash does.

   Yes, that is what I mean..

   Regards,
   John Ralls

   -derek

References

   1. mailto:[hidden email]
   2. mailto:[hidden email]
_______________________________________________
gnucash-devel mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-devel