NAME
DBIx::Class::AuditLog - Simple activity audit logging for DBIx::Class
SYNOPSIS
Enable the AuditLog schema component in your DBIx::Class::Schema class
file:
package My::Schema;
use base qw/DBIx::Class::Schema/;
__PACKAGE__->load_components(qw/Schema::AuditLog/);
Enable the AuditLog component in your the individual DBIx::Class table
class files that you want to enable logging on:
package My::Schema::Result::Table
use base qw/DBIx::Class::Core/;
__PACKAGE__->load_components(qw/AuditLog/);
In your application wrap any insert/update/delete in a transaction to
have audit logging activated:
$my_schema->txn_do(
sub {
$my_row->update({ ... });
}
);
Optionally pass an extra hashref to the txn_do method to indicate a user
and/or a description for the transaction:
$my_schema->txn_do(
sub {
$my_row->update({ ... });
},
{
user => 'username_or_id', # optional
description => 'description of transaction' # optional
}
);
DESCRIPTION
DBIx::Class::AuditLog is meant for tracking changes made to specific
tables in your database.
Any insert/update/delete that requires auditing must be wrapped in a
"txn_do" statement.
Transactions are saved as Changesets. Each Changeset can have many
Actions. An Action can be of type insert/update/delete. Actions can have
many Changes. Changes show the data that was changed during an action.
By default all columns will be audited in any table where the AuditLog
component is loaded.
On an individual column basis you can disable auditing by setting
'audit_log_column' to 0:
__PACKAGE__->add_columns(
"admin_id",
{ data_type => "integer", is_auto_increment => 1, is_nullable => 0, audit_log_column => 0 },
"admin_name",
{ data_type => "varchar", is_nullable => 0, size => 20 },
"admin_pasword",
{ data_type => "varchar", is_nullable => 0, size => 20 },
);
If you are using a DBIx::Class generated schema, and don't want to
modify the column defintions directly, you can add the following to the
editable portion of the Result Class file:
__PACKAGE__->add_columns(
"+admin_id",
{ audit_log_column => 0, }
);
TABLE STRUCTURE
The AuditLog schema is a self-contained schema that keeps no
relationships to your main schema.
The AuditLog schema consists of 6 tables:
+-------------------------+
| Tables_audit_log_schema |
+-------------------------+
| audit_log_action |
| audit_log_change |
| audit_log_changeset |
| audit_log_field |
| audit_log_table |
| audit_log_user |
+-------------------------+
User
This table contains a unique list of users that have executed
transactions. The 'name' field is used to store the User identifier.
This could be used for a user's name or id or other unique
identifier as needed.
This table does not get a relationship to any table in your main
schema.
mysql> select * from audit_log_user order by id;
+----+---------------------+
| id | name |
+----+---------------------+
| 1 | TestAdminUser |
| 2 | YetAnotherAdminUser |
| 3 | ioncache |
| 4 | markj |
+----+---------------------+
4 rows in set (0.01 sec)
Changeset
An AuditLogChangeset is an identifier for a transaction. It can have
many Actions. It can optionally be owned by a User. And optionally
have a description.
mysql> select * from audit_log_changeset order by id;
+----+----------------------------------------------+---------------------+------+
| id | description | timestamp | user |
+----+----------------------------------------------+---------------------+------+
| 1 | adding new user: JohnSample | 2012-06-22 17:16:25 | 1 |
| 2 | updating username: JaneSample | 2012-06-22 17:16:25 | 1 |
| 3 | delete user: JohnSample | 2012-06-22 17:16:25 | 2 |
| 4 | adding new user: TehPwnerer -- no admin user | 2012-06-22 17:16:25 | NULL |
| 5 | multi-action changeset | 2012-06-22 17:16:25 | 3 |
| 6 | NULL | 2012-06-22 17:16:25 | 4 |
+----+----------------------------------------------+---------------------+------+
6 rows in set (0.00 sec)
Action
An AuditLogAction is an insert/update/delete belonging to a
Changeset. It is also owned by Table.
mysql> select * from audit_log_action order by id;
+----+-----------+---------------+-------------+--------+
| id | changeset | audited_table | audited_row | type |
+----+-----------+---------------+-------------+--------+
| 1 | 1 | 1 | 8 | insert |
| 2 | 2 | 1 | 7 | update |
| 3 | 3 | 1 | 7 | delete |
| 4 | 4 | 1 | 9 | insert |
| 5 | 5 | 1 | 10 | insert |
| 6 | 5 | 1 | 11 | insert |
| 7 | 5 | 1 | 11 | update |
| 8 | 5 | 1 | 9 | update |
| 9 | 6 | 1 | 13 | insert |
| 10 | 6 | 1 | 13 | update |
+----+-----------+---------------+-------------+--------+
10 rows in set (0.00 sec)
Change
An AuditLogChange contains changed data for one AuditLogField in an
AuditLogAction. The original value is stored in the old_value field
and the new value is stored in the new_value field.
mysql> select * from audit_log_change order by id;
+----+--------+-------+--------------+--------------+
| id | action | field | old_value | new_value |
+----+--------+-------+--------------+--------------+
| 1 | 1 | 1 | NULL | JohnSample |
| 2 | 1 | 2 | NULL | 8 |
| 3 | 1 | 3 | NULL | 999-888-7777 |
| 4 | 3 | 1 | JohnSample | NULL |
| 5 | 3 | 2 | 7 | NULL |
| 6 | 3 | 3 | 999-888-7777 | NULL |
| 7 | 4 | 1 | NULL | TehPnwerer |
| 8 | 4 | 2 | NULL | 9 |
| 9 | 4 | 3 | NULL | 999-888-7777 |
| 10 | 5 | 1 | NULL | Superman |
| 11 | 5 | 2 | NULL | 10 |
| 12 | 5 | 3 | NULL | 123-456-7890 |
| 13 | 6 | 1 | NULL | Spiderman |
| 14 | 6 | 2 | NULL | 11 |
| 15 | 6 | 3 | NULL | 987-654-3210 |
| 16 | 8 | 1 | TehPnwerer | TehPwnerer |
| 17 | 8 | 3 | 999-888-7777 | 416-123-4567 |
| 18 | 9 | 1 | NULL | Drunk Hulk |
| 19 | 9 | 2 | NULL | 13 |
| 20 | 9 | 3 | NULL | 123-456-7890 |
+----+--------+-------+--------------+--------------+
20 rows in set (0.00 sec)
AuditedTable
An AuditLogAuditedTable contains a unique list of tables that are
being tracked. The name field contains the name of a table from your
database. AuditLogAuditedTable rows are added as needed whenever an
insert/update/date occurs.
Note: at least for DB2, the table name will have the schema name
added as well, eg., myschema.foo for the foo table in schema
myschema.
mysql> select * from audit_log_table order by id;
+----+------+
| id | name |
+----+------+
| 1 | user |
+----+------+
1 row in set (0.00 sec)
Field
An AuditLogField contains a unique list of fields for each table
that is being audited. The name field contains the name of a field
from a specific table inyour database. AuditLogField rows are added
as needed whenever an insert/update/date occurs.
mysql> select * from audit_log_field order by id;
+----+---------------+-------+
| id | audited_table | name |
+----+---------------+-------+
| 1 | 1 | name |
| 2 | 1 | id |
| 3 | 1 | phone |
+----+---------------+-------+
3 rows in set (0.00 sec)
DEPLOYMENT
To deploy an AuditLog schema, load your main schema, and then run the
deploy command on the audit_log_schema:
my $schema = AuditTest::Schema->connect( "DBI:mysql:database=audit_test",
"root", "somepassword", { RaiseError => 1, PrintError => 0 } );
$schema->audit_log_schema->deploy;
The db user that is deploying the schema must have the correct create
table permissions.
Note: this should only be run once.
METHODS
audit_log_schema
Returns: DBIC schema
The Audit Log schema can be accessed from your main schema by calling
the audit_log_schema method.
my $al_schema = $schema->audit_log_schema;
get_changes
Required: id, table
Optional: action_type, change_order, field, timestamp
Returns: DBIC resultset
A convenience method for reading changes from the Audit Log.
my $al_schema = $schema->audit_log_schema;
my $changes = $al_schema->get_changes({
id => 1,
table => 'foo',
field => 'bar', # optional
action_type => 'insert', # optional, 1 of insert, update, delete
timestamp => { '>=', $time }, # optional, $time must be a DateTime object,
change_order => 'asc', # optional, 1 of asc, desc
});
TODO
* add auto-deploy of schema if it doesn't exist
* add more convenience method(s) for retrieving changes from the Audit
Log schema
SEE ALSO
* DBIx::Class
* DBIx::Class::Journal
ACKNOWLEDGEMENTS
Development time supported by OANDA www.oanda.com
<http://www.oanda.com>.
Many ideas and code borrowed from DBIx::Class::Journal.
AUTHOR
Mark Jubenville (ioncache <mailto:ioncache@gmail.com>)
COPYRIGHT AND LICENCE
This software is copyright (c) 2012 by Mark Jubenville.
This is free software; you can redistribute it and/or modify it under
the same terms as the Perl 5 programming language system itself.