1 package DBIx::Class::Journal;
3 use base qw/DBIx::Class/;
8 our $VERSION = '0.900001_06';
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 my $ret = $self->next::method(@_);
44 $self->journal_log_delete(@_);
48 sub journal_log_delete {
51 unless ($self->in_storage) {
52 my $j = $self->_journal_schema;
53 $j->journal_update_or_create_log_entry( $self, delete_id => $j->journal_create_change->id );
57 ## On update, copy row's new contents to AuditHistory
61 my $ret = $self->next::method(@_);
62 $self->journal_log_update(@_);
66 sub journal_log_update {
69 if ($self->in_storage) {
70 my $j = $self->_journal_schema;
71 my $change_id = $j->journal_create_change->id;
72 $j->journal_record_in_history( $self, audit_change_id => $change_id );
78 DBIx::Class::Journal - Auditing for tables managed by DBIx::Class
82 Load the module into your L<DBIx::Class> Schema Class:
85 use base 'DBIx::Class::Schema';
87 __PACKAGE__->load_components(qw/Schema::Journal/);
89 Optionally set where the journal is stored:
91 __PACKAGE__->journal_connection(['dbi:SQLite:t/var/Audit.db']);
93 And then call C<< $schema->bootstrap_journal >> (I<once only>) to create all
94 the tables necessary for the journal, in your database.
96 Later on, in your application, wrap operations in transactions, and optionally
97 associate a user with the changeset:
99 $schema->changeset_user($user->id);
100 my $new_artist = $schema->txn_do( sub {
101 return $schema->resultset('Artist')->create({ name => 'Fred' });
106 The purpose of this L<DBIx::Class> component module is to create an
107 audit-trail for all changes made to the data in your database (via a
108 DBIx::Class schema). It creates I<changesets> and assigns each
109 create/update/delete operation an I<id>. The creation and deletion date of
110 each row is stored, as well as the historical contents of any row that gets
113 All queries which need auditing B<must> be called using
114 L<DBIx::Class::Schema/txn_do>, which is used to create changesets for each
117 To track who did which changes, the C<user_id> (an integer) of the current
118 user can be set, and a C<session_id> can also be set; both are optional. To
119 access the auditing schema to look at the auditdata or revert a change, use
120 C<< $schema->_journal_schema >>.
124 Currently the module expects to be deployed alongside a new database schema,
125 and track all changes from first entry. To do that you need to create some
126 tables in which to store the journal, and you can opitonally configure which
127 data sources (tables) have their operations journalled by the module.
129 Connect to your schema and deploy the journal tables as below. The module
130 automatically scans your schema and sets up storage for journal entries.
132 # optional - defaults to all sources
133 My::Schema->journal_sources([qw/ table1 table2 /]);
135 $schema = My::Schema->connect(...);
136 $schema->journal_schema_deploy;
138 Note that if you are retrofitting journalling to an existing database, then as
139 well as creating the journal you will need to populate it with a history so
140 that when rows are deleted they can be mapped back to a (fake) creation.
142 If you ever update your original schema, remember that you must then also
143 update the journal's schema to match, so that the AuditHistory has the
144 corresponding new columns in which to save data.
148 The journal schema contains a number of tables. These track row creation,
149 update and deletion, and also are aware of multiple operations taking place
150 within one transaction.
156 Each changeset row has an auto-incremented C<ID>, optional C<user_id> and
157 C<session_id>, and a C<set_date> which defaults to the current datetime. This
158 is the authoritative log of one discrete change to your database, which may
159 possible consist of a number of ChangeLog operations within a single
164 Each operation done within the transaction is recorded as a row in the
165 ChangeLog table. It contains an auto-incrementing C<ID>, the C<changeset_id>
166 and an C<order> column to establish the order in which changes took place.
170 For every table in the original database that is to be audited, an AuditLog
171 table is created. When a row appears in the original database a corresponding
172 row is added here with a ChangeLog ID in the C<create_id> column, and when
173 that original row is deleted the AuditLog is updated to add another ChangeLog
174 ID this time into the C<delete_id> column. A third id column contains the
175 primary key of the original row, so you can find it in the AuditHistory.
177 Note that currently only integer-based single column primary keys are
178 supported in your original database tables.
182 For every table in the original database to be audited, an AuditHistory table
183 is created. This is where the actual field data from your original table rows
184 are stored on creation and on each update.
186 Each row in the AuditHistory has a C<change_id> field containing the ID of the
187 ChangeLog row. The other fields correspond to all the fields from the original
188 table (with any constraints removed). Each time a column value in the original
189 table is changed, the entire row contents after the change are added as a new
196 Call these in your Schema Class such as the C<My::Schema> package file, as in
201 =item journal_connection \@connect_info
203 Set the connection information for the database to save your audit information
206 Leaving this blank assumes you want to store the audit data into your current
207 database. The storage object will be shared by the regular schema and the
210 =item journal_components @components
212 If you want to add components to your journal
213 (L<DBIx::Class::Schema::Versioned> for example) pass them here.
215 =item journal_sources \@source_names
217 Set a list of source names you would like to audit. If unset, all sources are
220 NOTE: Currently only sources with a single-column integer PK are supported, so
221 use this method if you have sources which don't comply with that limitation.
223 =item journal_storage_type $type
225 Enter the special storage type of your journal schema if needed. See
226 L<DBIx::Class::Storage::DBI> for more information on storage types.
228 =item journal_user \@rel
230 The user_id column in the L</ChangeSet> will be linked to your user id with a
231 C<belongs_to> relation, if this is set with the appropriate arguments. For
234 __PACKAGE__->journal_user(['My::Schema::User', {'foreign.userid' => 'self.user_id'}]);
238 =head1 OBJECT METHODS
240 Once you have a connection to your database, call these methods to manage the
245 =item bootstrap_journal
247 This calls C<journal_schema_deploy> followed by C<prepopulate_journal> to
248 create your journal tables and if necessary populate them with a snapshot of
249 your current original schema data.
251 Do not run this method more than once on your database, as redeploying the
252 journal schema is not supported.
254 =item journal_schema_deploy
256 Will use L<DBIx::Class::Schema/deploy> to set up the tables for journalling in
257 your schema. Use this method to set up your journal.
259 Note that if you are retrofitting journalling to an existing database, then as
260 well as creating the journal you will need to populate it with a history so
261 that when rows are deleted they can be mapped back to a (fake) creation.
263 Do not run this method more than once on your database, as redeploying the
264 journal schema is not supported.
266 =item prepopulate_journal
268 Will load the current state of your original source tables into the audit
269 history as fake inserts in a single initial changeset. The advantage to this
270 is that later deletetions of the row will be consistent in the journal with an
273 Note that this can be an intensive and time consuming task, depending on how
274 much data you have in your original sources; all of it will be copied to the
275 journal history. However this step is essential if you are retrofitting
276 Journalling to a schema with existing data, otherwise when you delete a row
277 the Journal will die because it cannot relate that to an initial row insert.
279 =item changeset_user $user_id
281 Set the C<user_id> for the following changeset(s). This must be an integer.
283 =item changeset_session $session_id
285 Set the C<session_id> for the following changeset(s). This must be an integer.
289 Overloaded L<DBIx::Class::Schema/deploy> which will deploy your original
290 database schema and following that will deploy the journal schema.
292 =item txn_do $code_ref, @args
294 Overloaded L<DBIx::Class::Schema/txn_do>, this must be used to start a new
295 ChangeSet to cover a group of changes. Each subsequent change to an audited
296 table will use the C<changeset_id> created in the most recent C<txn_do> call.
298 Currently nested C<txn_do> calls cause a single ChangeSet object to be created.
302 =head2 Deprecated Methods
306 =item journal_deploy_on_connect $bool
308 If set to a true value will cause C<journal_schema_deploy> to be called on
309 C<connect>. Not recommended (because re-deploy of a schema is not supported),
310 but present for backwards compatibility.
314 =head1 TROUBLESHOOTING
316 For PostgreSQL databases you must enable quoting on SQL command generation by
317 passing C<< { quote_char => q{`}, name_sep => q{.} } >> when connecting to the
326 L<DBIx::Class> - You'll need it to use this.
336 Only single-column integer primary key'd tables are supported for auditing.
340 Rows changed as a result of C<CASCADE> settings on your database will not be
341 detected by the module and hence not journalled.
345 Updates made via L<DBIx::Class::ResultSet/update> are not yet supported.
349 No API for viewing or restoring changes yet.
353 Patches for the above are welcome ;-)
357 Jess Robinson <castaway@desert-island.me.uk>
359 Matt S. Trout <mst@shadowcatsystems.co.uk> (ideas and prodding)
363 You may distribute this code under the same terms as Perl itself.