1 package DBIx::Class::Journal;
3 use base qw/DBIx::Class/;
8 our $VERSION = '0.900001_04';
9 $VERSION = eval $VERSION; # no errors in dev versions
11 ## On create/insert, add new entry to AuditLog and new content to AuditHistory
15 $self->result_source->schema->_journal_schema;
19 my ($self, @args) = @_;
20 return if $self->in_storage;
22 my $res = $self->next::method(@args);
23 $self->journal_log_insert;
28 sub journal_log_insert {
31 if ( $self->in_storage ) {
32 my $j = $self->_journal_schema;
33 my $change_id = $j->journal_create_change()->id;
34 $j->journal_update_or_create_log_entry( $self, create_id => $change_id );
35 $j->journal_record_in_history( $self, audit_change_id => $change_id );
39 ## On delete, update delete_id of AuditLog
43 $self->next::method(@_);
44 $self->journal_log_delete(@_);
47 sub journal_log_delete {
50 unless ($self->in_storage) {
51 my $j = $self->_journal_schema;
52 $j->journal_update_or_create_log_entry( $self, delete_id => $j->journal_create_change->id );
56 ## On update, copy row's new contents to AuditHistory
60 $self->next::method(@_);
61 $self->journal_log_update(@_);
64 sub journal_log_update {
67 if ($self->in_storage) {
68 my $j = $self->_journal_schema;
69 my $change_id = $j->journal_create_change->id;
70 $j->journal_record_in_history( $self, audit_change_id => $change_id );
76 DBIx::Class::Journal - Auditing for tables managed by DBIx::Class
81 use base 'DBIx::Class::Schema';
83 __PACKAGE__->load_components(qw/Schema::Journal/);
85 And then call C<< $schema->journal_schema_deploy >> to create all the tables
86 necessary for the journal, in your database.
88 Optionally set where the journal is stored:
90 __PACKAGE__->journal_connection(['dbi:SQLite:t/var/Audit.db']);
92 Later on, in your application, wrap operations in transactions, and optionally
93 associate a user with the changeset:
95 $schema->changeset_user($user->id);
96 my $new_artist = $schema->txn_do( sub {
97 return $schema->resultset('Artist')->create({ name => 'Fred' });
102 The purpose of this L<DBIx::Class> component module is to create an
103 audit-trail for all changes made to the data in your database (via a
104 DBIx::Class schema). It creates I<changesets> and assigns each
105 create/update/delete operation an I<id>. The creation and deletion date of
106 each row is stored, as well as the historical contents of any row that gets
109 All queries which need auditing must be called using
110 L<DBIx::Class::Schema/txn_do>, which is used to create changesets for each
113 To track who did which changes, the C<user_id> (an integer) of the current
114 user can be set, and a C<session_id> can also be set; both are optional.
116 To access the auditing schema to look at the auditdata or revert a change, use
117 C<< $schema->_journal_schema >>.
121 Currently the module expects to be deployed alongside a new database schema,
122 and track all changes from first entry. Do do that you need to add some tables
123 for the journal, and you can configure which tables have their operations
124 journalled by the module.
126 Connect to your schema and deploy the journal tables as below. The module
127 automatically scans your schema and sets up storage for journal entries.
129 My::Schema->journal_sources([qw/ table1 table2 /]);
131 $schema = My::Schema->connect(...);
132 $schema->journal_schema_deploy;
134 Note that if you are retrofitting journalling to an existing database, then as
135 well as creating the journal you will need to populate it with a history so
136 that when rows are deleted they can be mapped back to a (fake) creation.
140 The journal schema contains a number of tables. These track row creation,
141 update and deletion, and also are aware of multiple operations taking place
142 within one transaction.
148 Each changeset row has an auto-incremented C<ID>, optional C<user_id> and
149 C<session_id>, and a C<set_date> which defaults to the current datetime. This
150 is the authoritative log of one discrete change to your database, which may
151 possible consist of a number of ChangeLog operations within a single
156 Each operation done within the transaction is recorded as a row in the
157 ChangeLog table. It contains an auto-incrementing C<ID>, the C<changeset_id>
158 and an C<order> column to establish the order in which changes took place.
162 For every table in the original database that is to be audited, an AuditLog
163 table is created. When a row appears in the original database a corresponding
164 row is added here with a ChangeLog ID in the C<create_id> column, and when
165 that original row is deleted the AuditLog is updated to add another ChangeLog
166 ID this time into the C<delete_id> column. A third id column contains the
167 primary key of the original row, so you can find it in the AuditHistory.
169 Note that currently only integer-based single column primary keys are
170 supported in your original database tables.
174 For every table in the original database to be audited, an AuditHistory table
175 is created. This is where the actual field data from your original table rows
176 are stored on creation and on each update.
178 Each row in the AuditHistory has a C<change_id> field containing the ID of the
179 ChangeLog row. The other fields correspond to all the fields from the original
180 table (with any constraints removed). Each time a column value in the original
181 table is changed, the entire row contents after the change are added as a new
188 Call these in your Schema Class such as the C<My::Schema> package file, as in
193 =item journal_connection \@connect_info
195 Set the connection information for the database to save your audit information
198 Leaving this blank assumes you want to store the audit data into your current
199 database. The storage object will be shared by the regular schema and the
202 =item journal_components @components
204 If you want to add components to your journal
205 (L<DBIx::Class::Schema::Versioned> for example) pass them here.
207 =item journal_sources \@source_names
209 Set a list of source names you would like to audit. If unset, all sources are
212 NOTE: Currently only sources with a single-column integer PK are supported, so
213 use this method if you have sources which don't comply with that limitation.
215 =item journal_storage_type $type
217 Enter the special storage type of your journal schema if needed. See
218 L<DBIx::Class::Storage::DBI> for more information on storage types.
220 =item journal_user \@rel
222 The user_id column in the L</ChangeSet> will be linked to your user id with a
223 C<belongs_to> relation, if this is set with the appropriate arguments. For
226 __PACKAGE__->journal_user(['My::Schema::User', {'foreign.userid' => 'self.user_id'}]);
230 =head1 OBJECT METHODS
232 Once you have a connection to your database, call these methods to manage the
237 =item journal_schema_deploy
239 Will use L<DBIx::Class::Schema/deploy> to set up the tables for journalling in
240 your schema. Use this method to set up your journal.
242 Note that if you are retrofitting journalling to an existing database, then as
243 well as creating the journal you will need to populate it with a history so
244 that when rows are deleted they can be mapped back to a (fake) creation.
246 =item journal_deploy_on_connect $bool
248 If set to a true value will cause C<journal_schema_deploy> to be called on
251 Not recommended, but present for backwards compatibility.
253 =item changeset_user $user_id
255 Set the C<user_id> for the following changeset(s). This must be an integer.
257 =item changeset_session $session_id
259 Set the C<session_id> for the following changeset(s). This must be an integer.
263 Overloaded L<DBIx::Class::Schema/deploy> which will deploy your original
264 database schema and following that will deploy the journal schema.
266 =item txn_do $code_ref, @args
268 Overloaded L<DBIx::Class::Schema/txn_do>, this must be used to start a new
269 ChangeSet to cover a group of changes. Each subsequent change to an audited
270 table will use the C<changeset_id> created in the most recent C<txn_do> call.
272 Currently nested C<txn_do> calls cause a single ChangeSet object to be created.
282 L<DBIx::Class> - You'll need it to use this.
292 Only single-column integer primary key'd tables are supported for auditing.
296 Updates made via L<DBIx::Class::ResultSet/update> are not yet supported.
300 No API for viewing or restoring changes yet.
304 Patches for the above are welcome ;-)
308 Jess Robinson <castaway@desert-island.me.uk>
310 Matt S. Trout <mst@shadowcatsystems.co.uk> (ideas and prodding)
314 You may distribute this code under the same terms as Perl itself.