Commit | Line | Data |
f0f14c64 |
1 | package DBIx::Class::Journal; |
2 | |
3 | use base qw/DBIx::Class/; |
f0f14c64 |
4 | |
ec16e73a |
5 | use strict; |
6 | use warnings; |
7 | |
5a837c69 |
8 | our $VERSION = '0.900100'; |
22f043c8 |
9 | $VERSION = eval $VERSION; # no errors in dev versions |
ec16e73a |
10 | |
f2188d3f |
11 | ## On create/insert, add new entry to AuditLog and new content to AuditHistory |
74f04ccc |
12 | |
7adb876c |
13 | sub _journal_schema { |
14 | my $self = shift; |
15 | $self->result_source->schema->_journal_schema; |
6bfb7a1d |
16 | } |
17 | |
548cc9f7 |
18 | sub insert { |
6bfb7a1d |
19 | my ($self, @args) = @_; |
548cc9f7 |
20 | return if $self->in_storage; |
6bfb7a1d |
21 | |
22 | my $res = $self->next::method(@args); |
548cc9f7 |
23 | $self->journal_log_insert; |
6bfb7a1d |
24 | |
c5fba518 |
25 | return $res; |
74f04ccc |
26 | } |
27 | |
548cc9f7 |
28 | sub journal_log_insert { |
6bfb7a1d |
29 | my ($self) = @_; |
30 | |
7adb876c |
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 ); |
36 | } |
6bfb7a1d |
37 | } |
38 | |
74f04ccc |
39 | ## On delete, update delete_id of AuditLog |
40 | |
548cc9f7 |
41 | sub delete { |
42 | my $self = shift; |
2ffcef12 |
43 | my $ret = $self->next::method(@_); |
548cc9f7 |
44 | $self->journal_log_delete(@_); |
2ffcef12 |
45 | return $ret |
6bfb7a1d |
46 | } |
1e996809 |
47 | |
548cc9f7 |
48 | sub journal_log_delete { |
6bfb7a1d |
49 | my ($self) = @_; |
50 | |
7adb876c |
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 ); |
54 | } |
1e996809 |
55 | } |
56 | |
f2188d3f |
57 | ## On update, copy row's new contents to AuditHistory |
74f04ccc |
58 | |
548cc9f7 |
59 | sub update { |
60 | my $self = shift; |
2ffcef12 |
61 | my $ret = $self->next::method(@_); |
f2188d3f |
62 | $self->journal_log_update(@_); |
2ffcef12 |
63 | return $ret |
6bfb7a1d |
64 | } |
65 | |
548cc9f7 |
66 | sub journal_log_update { |
67 | my $self = shift; |
1e996809 |
68 | |
3ef10e31 |
69 | if ($self->in_storage) { |
7adb876c |
70 | my $j = $self->_journal_schema; |
e0a0e192 |
71 | my $change_id = $j->journal_create_change->id; |
3ef10e31 |
72 | $j->journal_record_in_history( $self, audit_change_id => $change_id ); |
1e996809 |
73 | } |
1e996809 |
74 | } |
75 | |
ec16e73a |
76 | =head1 NAME |
77 | |
86e977a3 |
78 | DBIx::Class::Journal - Auditing for tables managed by DBIx::Class |
ec16e73a |
79 | |
80 | =head1 SYNOPSIS |
81 | |
172e6afe |
82 | Load the module into your L<DBIx::Class> Schema Class: |
83 | |
22f043c8 |
84 | package My::Schema; |
85 | use base 'DBIx::Class::Schema'; |
ec16e73a |
86 | |
22f043c8 |
87 | __PACKAGE__->load_components(qw/Schema::Journal/); |
ec16e73a |
88 | |
b468e28b |
89 | Optionally set where the journal is stored: |
86e977a3 |
90 | |
22f043c8 |
91 | __PACKAGE__->journal_connection(['dbi:SQLite:t/var/Audit.db']); |
ec16e73a |
92 | |
d57ee845 |
93 | And then call C<< $schema->bootstrap_journal >> (I<once only>) to create all |
94 | the tables necessary for the journal, in your database. |
95 | |
b468e28b |
96 | Later on, in your application, wrap operations in transactions, and optionally |
97 | associate a user with the changeset: |
ec16e73a |
98 | |
22f043c8 |
99 | $schema->changeset_user($user->id); |
100 | my $new_artist = $schema->txn_do( sub { |
101 | return $schema->resultset('Artist')->create({ name => 'Fred' }); |
102 | }); |
ec16e73a |
103 | |
ec16e73a |
104 | =head1 DESCRIPTION |
105 | |
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 |
b468e28b |
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 |
111 | changed. |
ec16e73a |
112 | |
172e6afe |
113 | All queries which need auditing B<must> be called using |
b468e28b |
114 | L<DBIx::Class::Schema/txn_do>, which is used to create changesets for each |
115 | transaction. |
ec16e73a |
116 | |
b468e28b |
117 | To track who did which changes, the C<user_id> (an integer) of the current |
172e6afe |
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 |
b468e28b |
120 | C<< $schema->_journal_schema >>. |
ec16e73a |
121 | |
b468e28b |
122 | =head1 DEPLOYMENT |
ec16e73a |
123 | |
b468e28b |
124 | Currently the module expects to be deployed alongside a new database schema, |
172e6afe |
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. |
ec16e73a |
128 | |
b468e28b |
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. |
ec16e73a |
131 | |
172e6afe |
132 | # optional - defaults to all sources |
b468e28b |
133 | My::Schema->journal_sources([qw/ table1 table2 /]); |
4bd0bd90 |
134 | |
b468e28b |
135 | $schema = My::Schema->connect(...); |
136 | $schema->journal_schema_deploy; |
ec16e73a |
137 | |
b468e28b |
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. |
ec16e73a |
141 | |
172e6afe |
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. |
145 | |
b468e28b |
146 | =head1 TABLES |
147 | |
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. |
151 | |
152 | =over 4 |
153 | |
154 | =item ChangeSet |
155 | |
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 |
160 | transaction. |
ec16e73a |
161 | |
41daf590 |
162 | =item ChangeLog |
ec16e73a |
163 | |
b468e28b |
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. |
ec16e73a |
167 | |
168 | =item AuditLog |
169 | |
b468e28b |
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. |
176 | |
177 | Note that currently only integer-based single column primary keys are |
178 | supported in your original database tables. |
ec16e73a |
179 | |
180 | =item AuditHistory |
181 | |
b468e28b |
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. |
185 | |
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 |
190 | row in this table. |
ec16e73a |
191 | |
192 | =back |
193 | |
b468e28b |
194 | =head1 CLASS METHODS |
195 | |
196 | Call these in your Schema Class such as the C<My::Schema> package file, as in |
197 | the SYNOPSIS, above. |
ec16e73a |
198 | |
b468e28b |
199 | =over 4 |
ec16e73a |
200 | |
c1c87879 |
201 | =item journal_connection \@connect_info |
ec16e73a |
202 | |
b468e28b |
203 | Set the connection information for the database to save your audit information |
204 | to. |
ec16e73a |
205 | |
c1c87879 |
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 |
208 | journalling schema. |
ec16e73a |
209 | |
27e45f70 |
210 | =item journal_components @components |
211 | |
212 | If you want to add components to your journal |
86e977a3 |
213 | (L<DBIx::Class::Schema::Versioned> for example) pass them here. |
27e45f70 |
214 | |
c1c87879 |
215 | =item journal_sources \@source_names |
ec16e73a |
216 | |
b468e28b |
217 | Set a list of source names you would like to audit. If unset, all sources are |
218 | used. |
ec16e73a |
219 | |
b468e28b |
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. |
ec16e73a |
222 | |
c1c87879 |
223 | =item journal_storage_type $type |
ec16e73a |
224 | |
ec16e73a |
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. |
227 | |
c1c87879 |
228 | =item journal_user \@rel |
ec16e73a |
229 | |
b468e28b |
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 |
232 | example: |
233 | |
234 | __PACKAGE__->journal_user(['My::Schema::User', {'foreign.userid' => 'self.user_id'}]); |
235 | |
236 | =back |
237 | |
238 | =head1 OBJECT METHODS |
239 | |
240 | Once you have a connection to your database, call these methods to manage the |
241 | journalling. |
242 | |
243 | =over 4 |
ec16e73a |
244 | |
462d8e70 |
245 | =item bootstrap_journal |
246 | |
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. |
250 | |
ac1354cf |
251 | Do not run this method more than once on your database, as redeploying the |
252 | journal schema is not supported. |
253 | |
86e977a3 |
254 | =item journal_schema_deploy |
255 | |
b468e28b |
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. |
258 | |
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. |
86e977a3 |
262 | |
ac1354cf |
263 | Do not run this method more than once on your database, as redeploying the |
264 | journal schema is not supported. |
ec16e73a |
265 | |
1d09727d |
266 | =item prepopulate_journal |
267 | |
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 |
271 | initial state. |
272 | |
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. |
278 | |
c1c87879 |
279 | =item changeset_user $user_id |
ec16e73a |
280 | |
b468e28b |
281 | Set the C<user_id> for the following changeset(s). This must be an integer. |
ec16e73a |
282 | |
c1c87879 |
283 | =item changeset_session $session_id |
ec16e73a |
284 | |
b468e28b |
285 | Set the C<session_id> for the following changeset(s). This must be an integer. |
ec16e73a |
286 | |
86e977a3 |
287 | =item deploy |
288 | |
b468e28b |
289 | Overloaded L<DBIx::Class::Schema/deploy> which will deploy your original |
86e977a3 |
290 | database schema and following that will deploy the journal schema. |
291 | |
c1c87879 |
292 | =item txn_do $code_ref, @args |
ec16e73a |
293 | |
b468e28b |
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. |
ec16e73a |
297 | |
c1c87879 |
298 | Currently nested C<txn_do> calls cause a single ChangeSet object to be created. |
299 | |
90dae731 |
300 | =back |
301 | |
ac1354cf |
302 | =head2 Deprecated Methods |
303 | |
304 | =over 4 |
305 | |
306 | =item journal_deploy_on_connect $bool |
307 | |
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. |
311 | |
312 | =back |
313 | |
314 | =head1 TROUBLESHOOTING |
315 | |
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 |
318 | database. |
319 | |
ec16e73a |
320 | =head1 SEE ALSO |
321 | |
b468e28b |
322 | =over 4 |
323 | |
324 | =item * |
325 | |
ec16e73a |
326 | L<DBIx::Class> - You'll need it to use this. |
327 | |
b468e28b |
328 | =back |
ec16e73a |
329 | |
b468e28b |
330 | =head1 LIMITATIONS |
331 | |
332 | =over 4 |
333 | |
334 | =item * |
335 | |
336 | Only single-column integer primary key'd tables are supported for auditing. |
337 | |
338 | =item * |
ec16e73a |
339 | |
d57ee845 |
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. |
342 | |
343 | =item * |
344 | |
ec16e73a |
345 | Updates made via L<DBIx::Class::ResultSet/update> are not yet supported. |
346 | |
b468e28b |
347 | =item * |
348 | |
ec16e73a |
349 | No API for viewing or restoring changes yet. |
350 | |
b468e28b |
351 | =back |
352 | |
353 | Patches for the above are welcome ;-) |
ec16e73a |
354 | |
355 | =head1 AUTHOR |
356 | |
357 | Jess Robinson <castaway@desert-island.me.uk> |
358 | |
359 | Matt S. Trout <mst@shadowcatsystems.co.uk> (ideas and prodding) |
360 | |
361 | =head1 LICENCE |
362 | |
363 | You may distribute this code under the same terms as Perl itself. |
f0f14c64 |
364 | |
ec16e73a |
365 | =cut |
f0f14c64 |
366 | |
367 | 1; |