NAME

DBIx::Class::Journal - auditing for tables managed by DBIx::Class

SYNOPSIS

  package My::Schema;
  use base 'DBIx::Class::Schema';

  __PACKAGE__->load_components(qw/+DBIx::Class::Schema::Journal/);

  __PACKAGE__->journal_connection(['dbi:SQLite:t/var/Audit.db']);
  __PACKAGE__->journal_user(['My::Schema::User', {'foreign.userid' => 'self.user_id'}]);


 ########

  $schema->changeset_user($user->id);
  my $new_artist = $schema->txn_do( sub {
   return = $schema->resultset('Artist')->create({ name => 'Fred' });
  });

DESCRIPTION

The purpose of this DBIx::Class component module is to create an audit-trail for all changes made to the data in your database (via a DBIx::Class schema). It creates changesets and assigns each create/update/delete operation an id. The creation and deletion date of each row is stored, as well as the previous contents of any row that gets changed.

All queries which want auditing should be called using "txn_do" in DBIx::Class::Schema, which is used to create changesets for each transaction.

To track who did which changes, the user_id (an integer) of the current user can be set, a session_id can also be set, both are optional.

To access the auditing schema to look at the auditdata or revert a change, use $schema->_journal_schema.

TABLES

The journal schema contains a number of tables.

ChangeSet

Each changeset row has an auto-incremented ID, optional user_id and session_id, and a set_date which defaults to the current datetime.

A ChangeSet has_many Changes.

ChangeLog

Each change/operation done in the transaction is recorded as a row in the ChangeLog table. It contains an auto-incrementing ID, the changeset_id and an order column for the ordering of each change in the changeset.

AuditLog

For every table in the original database that is to be audited, an AuditLog table is created. Each auditlog row has an id which will contain the primary key of the table it is associated with. (NB: currently only supports integer-based single column PKs). The create_id and delete_id fields contain the IDs of the Changes that created or deleted this row.

AuditHistory

For every table in the original database to be audited, an AuditHistory table is created. Each row has a change_id field containing the ID of the ChangeLog row. The other fields correspond to all the fields from the original table. Each time a column value in the original table is changed, the entire row contents before the change are added as a new row in this table.

METHODS

journal_connection \@connect_info

Set the connection information for the database to save your audit information to.

Leaving this blank assumes you want to store the audit data into your current database. The storage object will be shared by the regular schema and the journalling schema.

journal_sources \@source_names

Set a list of source names you would like to audit, if unset, all sources are used.

NOTE: Currently only sources with a single-column PK are supported, so use this method if you have sources with multi-column PKs.

journal_storage_type $type

Enter the special storage type of your journal schema if needed. See DBIx::Class::Storage::DBI for more information on storage types.

journal_user \@rel

The user_id column in the "ChangeSet" will be linked to your user id with a belongs_to relation, if this is set with the appropriate arguments.

journal_deploy_on_connect $bool

If set to a true value will cause journal_schema_deploy to be called on connect.

Not reccomended, but present for backwards compatibility.

changeset_user $user_id

Set the user_id for the following changeset(s). This must be an integer.

changeset_session $session_id

Set the session_id for the following changeset(s). This must be an integer.

txn_do $code_ref, @args

Overloaded "txn_do" in DBIx::Class::Schema, this must be used to start a new changeset to cover a group of changes. Each subsequent change to an audited table will use the changeset_id created in the most recent txn_do call.

Currently nested txn_do calls cause a single ChangeSet object to be created.

SEE ALSO

DBIx::Class - You'll need it to use this.

NOTES

Only single-column integer primary key'd tables are supported for auditing so far.

Updates made via "update" in DBIx::Class::ResultSet are not yet supported.

No API for viewing or restoring changes yet.

Patches for the above welcome ;)

AUTHOR

Jess Robinson <castaway@desert-island.me.uk>

Matt S. Trout <mst@shadowcatsystems.co.uk> (ideas and prodding)

LICENCE

You may distribute this code under the same terms as Perl itself.