Filler

So I’ve been pretty slack about posting dev stuff as of late. I can blame most of my silence on being busy writing code, but I won’t have that excuse pretty soon. With any luck we’ll be able to slow down a bit after July (watch for the alpha release!) and I’ll get to do some brain dumping here.

To get back into the swing of posting development updates I thought I’d put up something that we’re using in the Open-ILS database. Like all big projects that deal with user transactions we require an amount of accountability because of shared access to customer/patron data. The normal approach to this when using an RDBMS is to create an audit table for each production table you want to track. Normally one would have to create each audit table by hand, but since we’re using Postgres we get a little help. Postgres has a table creation option called LIKE that will create all columns from the named LIKE table in addition to any named in the CREATE list. Wrap that up in a UDF that creates the audit table and add a couple of triggers, and voila!

BEGIN;

-- Create a schema to contain all of our audit tables and the creator function
CREATE SCHEMA auditor;

-- The function that does the heavy lifting of creating audit tables
-- and the triggers that will populate them.
CREATE FUNCTION auditor.create_auditor ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
BEGIN

	-- Create the audit table : auditor.{schema}_{table}_history
	EXECUTE $$
		CREATE TABLE auditor.$$ || sch || $$_$$ || tbl || $$_history (
			audit_time	TIMESTAMP WITH TIME ZONE	NOT NULL,
			audit_action	CHAR(1)				NOT NULL,
			LIKE $$ || sch || $$.$$ || tbl || $$
		);
	$$;

	-- Create the function that will update the new table
	EXECUTE $$
		CREATE FUNCTION auditor.audit_$$ || sch || $$_$$ || tbl || $$_func ()
		RETURNS TRIGGER AS $func$
		BEGIN
			INSERT INTO auditor.$$ || sch || $$_$$ || tbl || $$_history
				SELECT now(), SUBSTR(TG_OP,1,1), OLD.*;
			RETURN NULL;
		END;
		$func$ LANGUAGE 'plpgsql';
	$$;

	-- Create the trigger to run the function after a
	-- successful update or delete to the table.
	EXECUTE $$
		CREATE TRIGGER audit_$$ || sch || $$_$$ || tbl || $$_update_trigger
			AFTER UPDATE OR DELETE ON $$ || sch || $$.$$ || tbl || $$ FOR EACH ROW
			EXECUTE PROCEDURE auditor.audit_$$ || sch || $$_$$ || tbl || $$_func ();
	$$;
	RETURN TRUE;
END;
$creator$ LANGUAGE 'plpgsql';

COMMIT;

Now it’s a simple matter of choosing the tables you’d like to audit and running the auditor.create_auditor() function with the schema and table names.

SELECT auditor.create_auditor( 'actor', 'usr' );
SELECT auditor.create_auditor( 'biblio', 'record_entry' );
SELECT auditor.create_auditor( 'asset', 'copy' );

Any changes made to actor.usr, biblio.record_entry or asset.copy will be recorded with the change type (‘U’ for update and ‘D’ for delete) and the time of the change. If you record the user id of the editor in the record, you then have accountability. You can also travel back in time to remove bad edits or resurrect deleted rows.