Gnucash PostgreSQL audit trails

Matthew Vanecek mevanecek at yahoo.com
Wed Aug 13 21:09:32 CDT 2003


On Wed, 2003-08-13 at 17:55, Linas Vepstas wrote:
> Hi,
> 
> On Tue, Aug 12, 2003 at 11:51:36AM -0700, Drake Diedrich was heard to remark:
> > Hi Linas,
> >    I've been toying with gnucash for a little while, and had some ideas
> > on simplifying Gnucash's interface to the postgreSQL backend.  Using
> > triggers and rules in the SQL backend, all of the auditing code could be
> > removed from Gnucash.  The production of the SQL auditing code can be
> > automated (I've done this for other applications).
> 
> Three remarks:
> 1) All conversation must take place on gnucash-devel at gnucash.org.
>    That's where all the developers are.

Indeed. :)

>  
> 2) The current 'audit trail' serves a dual purpose:  it is used as a 
>    way of communicating changes between multiple users.  So any changes
>    must not break that.
> 
>    You can test this by starting two copies of gnucash, attached to the
>    same DB.  Open the same account.   Edit in one account, and magically
>    watch changes appear in the other account (actually, you have to
>    force some activity to make the changes appear, but no matter.)
>    Even delete works ! Audti trails are particularly critical for 
>    delettion, otherwise there is no way to tell aprt a deleted trans
>    from a brand-new trans.

I've also been toying with the idea of somehow tying the audit trail to
Undo, somehow.  Haven't thought through the whole multi-user aspect of
it, though, so it's still in the toying stage.

>    
> 3) Triggers are OK, I guess, but the mysql contingent may scream, 
>    as mysql doesn't support triggers .. and this would make porting 
>    that much harder ... So I'm not sure what the benefit really is.
> 

Triggers would rock.
<rant level="my personal opinion">I'm not sure I really care about the
MySQL contingent--that excuse for a DBMS has so many non-compliancies
it's going to be a royal pain to code an ANSI SQL-compliant generic
backend that supports MySQL.  Postgresql, at least, has fairly complete
support for SQL92/99, although it does provide some non-standard
extensions that make life a bit easier.</rant>

However, triggers may still be non-portable to another DBMS (e.g.,
SQLite) that is not so feature-rich but is still SQL standards
compliant.

Besides which, there are already a good number of automagic auditing
tools out there that are very, very capable, if we were going to devote
ourselves exclusively to Postgresql...I wouldn't want to reinvent the
wheel.

One point about the code that I cut below. It would be convenient, as
you say, to have the perl, et al., to be able to import records directly
to the DB, and have the triggered updates.  However, Gnucash does some
pretty specific things with its data; therefore, it would be better to
use the Gnucash engine to do your work.  There may even be some Perl
bindings that still work.  Otherwise, you risk corrupting your data.

One thing to keep in mind in your explorations:  We are aiming to be as
close to DBMS-agnostic as possible in the future, so features you
envision would be more viable if they follow that precept.   You're
right, though: the more work we can slough off onto the DBMS engine, the
better...

-- 
Matthew Vanecek
perl -e 'print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10);'
********************************************************************************
For 93 million miles, there is nothing between the sun and my shadow except me.
I'm always getting in the way of something...



More information about the gnucash-devel mailing list