Gnucash PostgreSQL audit trails

[Linas Vepstas]

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

I would really loike to see someone add support for Lots into the
backend.

--linas



> 
>    I've attached a little bit of code to automatically audit the gncentry
> table, and a completely independent test table generated automatically using
> a small Perl table munger.
> 
>    Another advantage of regularizing the SQL would be that other
> applications (thinking web and commandline) could be used to import/export
> data directly from the backend, without needing human interaction via the
> main Gnucash binary (my real incentive - importing my borken bank statements
> in a few lines of Perl and a web browser handler entry.  Right now it's a
> cleanup the grotty "XML", and then a bunch of file loading and clicking in
> Gnucash).
> 
>    I would also like to enforce zero-sum transactions, but without deferred
> triggers in Postgres yet this seems difficult.  Still, getting the database
> to ensure transaction integrity would make giving users direct database
> access and act as a check on the Gnucash transaction code as well.
> 
>    What do you think, would this be worthwhile continuing with?
> 
> -Drake Diedrich
> 

> CREATE FUNCTION record_stamp () RETURNS OPAQUE AS '
>         BEGIN
>             NEW.timestamp := current_timestamp; 
>             NEW.userstamp := current_user;
>             RETURN NEW;
>         END;
>     ' LANGUAGE 'plpgsql';
> 
> ALTER TABLE gncentry ADD COLUMN "userstamp" TEXT;
> ALTER TABLE gncentry ADD COLUMN "timestamp" TIMESTAMP;
> 
> CREATE TABLE gncentry_old (
>     entryguid character(32),
>     accountguid character(32),
>     transguid character(32),
>     memo text,
>     "action" text,
>     reconciled character(1),
>     date_reconciled timestamp without time zone,
>     amount bigint,
>     value bigint,
>     iguid integer,
>     userstamp TEXT,
>     timestamp TIMESTAMP
> );
> 
> CREATE RULE gncentry_update_rule AS ON UPDATE TO gncentry DO (
> 	INSERT INTO gncentry_old (entryguid,accountguid,transguid,memo,"action",
> 		reconciled,date_reconciled,amount,value,iguid,userstamp,timestamp)
> 	VALUES (OLD.entryguid,OLD.accountguid,OLD.transguid,OLD.memo,OLD."action",
> 		OLD.reconciled,OLD.date_reconciled,OLD.amount,OLD.value,OLD.iguid,OLD.userstamp,OLD.timestamp);
> );
> 
> CREATE RULE gncentry_delete_rule AS ON DELETE TO gncentry DO (
> 	INSERT INTO gncentry_old (entryguid,accountguid,transguid,memo,"action",
> 		reconciled,date_reconciled,amount,value,iguid,userstamp,timestamp)
> 	VALUES (OLD.entryguid,OLD.accountguid,OLD.transguid,OLD.memo,OLD."action",
> 		OLD.reconciled,OLD.date_reconciled,OLD.amount,OLD.value,OLD.iguid,OLD.userstamp,OLD.timestamp);
> 	INSERT INTO gncentry_old (entryguid,userstamp,timestamp)
> 	VALUES (OLD.entryguid,current_user,current_timestamp);
> );
> 
> CREATE TRIGGER gncentry_insert_stamp BEFORE INSERT ON gncentry
> 	FOR EACH ROW EXECUTE PROCEDURE record_stamp();
> CREATE TRIGGER gncentry_update_stamp BEFORE UPDATE ON gncentry
> 	FOR EACH ROW EXECUTE PROCEDURE record_stamp();

> CREATE SEQUENCE test_id_seq;
> CREATE SEQUENCE test_entry_seq;
> CREATE TABLE test (
> 	id		INTEGER PRIMARY KEY,
> 	note	TEXT,
> 	num	INTEGER,
> 	entry		INTEGER,
> 	userstamp		TEXT,
> 	timestamp	TIMESTAMP
> );
> 
> CREATE TABLE test_old (
> 	id		INTEGER,
> 	note	TEXT,
> 	num	INTEGER,
> 	deleted		BOOLEAN,
> 	entry		INTEGER PRIMARY KEY,
> 	userstamp	TEXT,
> 	timestamp	TIMESTAMP
> );
> 
> CREATE INDEX test_old_id_idx ON test_old(id);
> 
> CREATE RULE test_update_rule AS ON UPDATE TO test DO (
> 	INSERT INTO test_old (entry,id,timestamp,userstamp,note,num)
> 		VALUES(OLD.entry,OLD.id,OLD.timestamp,OLD.userstamp,OLD.note,OLD.num);
> );
> 
> CREATE RULE test_delete_rule AS ON DELETE TO test DO (
> 	INSERT INTO test_old (entry,id,timestamp,userstamp,note,num)
> 		VALUES(OLD.entry,OLD.id,OLD.timestamp,OLD.userstamp,OLD.note,OLD.num);
> 	INSERT INTO test_old(entry,id,timestamp,userstamp,deleted)
> 		VALUES(NEXTVAL('test_entry_seq'),OLD.id,current_timestamp,current_user,True);
> );
> 
> CREATE VIEW test_history AS
> 	SELECT id,note,num,deleted,entry,userstamp,timestamp
> 		FROM test_old
> 	UNION
> 	SELECT id,note,num,NULL,entry,userstamp,timestamp
> 	FROM test t;
> 
> CREATE VIEW test_temporal AS
> 	SELECT id,note,num,entry,userstamp,timestamp AS valid_from,
> 	COALESCE( (SELECT th2.timestamp FROM test_history th2
> 			WHERE th2.entry > th1.entry AND th2.id=th1.id
> 			ORDER BY th2.entry ASC LIMIT 1),
> 		'infinity'::timestamp) AS valid_to
> 	FROM test_history th1
> 	WHERE th1.deleted ISNULL;
> 
> CREATE TRIGGER test_insert_stamp BEFORE INSERT ON test
> 	FOR EACH ROW EXECUTE PROCEDURE record_stamp();
> 
> CREATE TRIGGER test_insert_id BEFORE INSERT ON test
> 	FOR EACH ROW EXECUTE PROCEDURE id_stamp();
> 
> CREATE TRIGGER test_update_stamp BEFORE UPDATE ON test
> 	FOR EACH ROW EXECUTE PROCEDURE record_stamp();
> 
> -- GRANT SELECT,INSERT,UPDATE,DELETE ON test TO GROUP groupname;
> -- GRANT SELECT ON test_history TO GROUP groupname;
> -- GRANT SELECT,UPDATE ON test_entry_seq TO GROUP groupname;
> -- GRANT SELECT,UPDATE ON test_id_seq TO GROUP groupname;


-- 
pub  1024D/01045933 2001-02-01 Linas Vepstas (Labas!) <linas at linas.org>
PGP Key fingerprint = 8305 2521 6000 0B5E 8984  3F54 64A9 9A82 0104 5933