ioncache / dbix-class-auditlog Goto Github PK

ExtUtils::MakeMaker has an issue with .pod files in the root directory of a distribution: that file will be installed and converted as a man page. See EUMM#19.

Hi,

One day, I started getting the following error.

DBIx::Class::Storage::DBI::select_single(): Query returned more than one row.  SQL that returns multiple rows is DEPRECATED for ->find and ->single

I tried s/carp/cluck/ on the line below.

https://github.com/Perl5/DBIx-Class/blob/03776361fc4081ae8413ff8d259f549c33569db5/lib/DBIx/Class/Storage/DBI.pm#L2765

DBIx::Class::Storage::DBI::select_single(DBIx::Class::Storage::DBI::Pg=HASH(0x805c11438), ARRAY(0x807373c90), ARRAY(0x8072d28b8), HASH(0x80704e9c0), HASH(0x8072c6090)) called at /usr/local/lib/perl5/site_perl/DBIx/Class/ResultSet.pm line 1101
DBIx::Class::ResultSet::single(DBIx::Class::ResultSet=HASH(0x8073736d8)) called at /usr/local/lib/perl5/site_perl/DBIx/Class/ResultSet.pm line 910
DBIx::Class::ResultSet::find(DBIx::Class::ResultSet=HASH(0x807373768), HASH(0x807395408)) called at /usr/local/lib/perl5/site_perl/DBIx/Class/Relationship/Base.pm line 731
DBIx::Class::Relationship::Base::find_related(DBIx::Class::Schema::AuditLog::Structure::AuditedTable=HASH(0x8072d2e10), "Field", HASH(0x807395408)) called at /usr/local/lib/perl5/site_perl/DBIx/Class/Relationship/Base.pm line 772
DBIx::Class::Relationship::Base::find_or_create_related(DBIx::Class::Schema::AuditLog::Structure::AuditedTable=HASH(0x8072d2e10), "Field", HASH(0x807395408)) called at /usr/local/lib/perl5/site_perl/DBIx/Class/AuditLog.pm line 131
DBIx::Class::AuditLog::_store_changes(MyApp::Schema::Result::Table1=HASH(0x8073959d8), DBIx::Class::Schema::AuditLog::Structure::Action=HASH(0x807398cd8), DBIx::Class::Schema::AuditLog::Structure::AuditedTable=HASH(0x8072d2e10), HASH(0x805a1e7c8), HASH(0x805a1e678)) called at /usr/local/lib/perl5/site_perl/DBIx/Class/AuditLog.pm line 74
DBIx::Class::AuditLog::update(MyApp::Schema::Result::Table1=HASH(0x8073959d8)) called at /usr/local/lib/perl5/site_perl/DBIx/Class/DynamicDefault.pm line 127
DBIx::Class::DynamicDefault::update(MyApp::Schema::Result::Table1=HASH(0x8073959d8)) called at /usr/local/lib/perl5/site_perl/DBIx/Class/TimeStamp/TimeMomentX_BOKUTIN.pm line 46
DBIx::Class::TimeStamp::TimeMomentX_BOKUTIN::update(MyApp::Schema::Result::Table1=HASH(0x8073959d8)) called at /usr/local/lib/perl5/site_perl/DBIx/Class/Row.pm line 1345
DBIx::Class::Row::update_or_insert(MyApp::Schema::Result::Table1=HASH(0x8073959d8)) called at /root/MyApp/script/../lib/MyApp/Cmd/Command/command1.pm line 113
MyApp::Cmd::Command::command1::__ANON__() called at /root/MyApp/script/../lib/MyApp/Schema.pm line 65
MyApp::Schema::__ANON__() called at /usr/local/lib/perl5/site_perl/Context/Preserve.pm line 23
Context::Preserve::preserve_context(CODE(0x807395fd8), "after", CODE(0x80508f930)) called at /root/MyApp/script/../lib/MyApp/Schema.pm line 68
MyApp::Schema::__ANON__(HASH(0x806fbc720)) called at /usr/local/lib/perl5/site_perl/DBIx/Class/Schema/AuditLog.pm line 66
DBIx::Class::Schema::AuditLog::__ANON__(HASH(0x806fbc720)) called at /usr/local/lib/perl5/site_perl/DBIx/Class/Storage/BlockRunner.pm line 130
DBIx::Class::Storage::BlockRunner::try {...} () called at /usr/local/lib/perl5/site_perl/Try/Tiny.pm line 102
eval {...} called at /usr/local/lib/perl5/site_perl/Try/Tiny.pm line 93
Try::Tiny::try(CODE(0x8070bc588), Try::Tiny::Catch=REF(0x8072d2d80)) called at /usr/local/lib/perl5/site_perl/DBIx/Class/Storage/BlockRunner.pm line 134
DBIx::Class::Storage::BlockRunner::__ANON__() called at /usr/local/lib/perl5/site_perl/Context/Preserve.pm line 23
Context::Preserve::preserve_context(CODE(0x80736ac90), "replace", CODE(0x807397060)) called at /usr/local/lib/perl5/site_perl/DBIx/Class/Storage/BlockRunner.pm line 213
DBIx::Class::Storage::BlockRunner::_run(undef, undef, HASH(0x806fbc720)) called at /usr/local/lib/perl5/site_perl/DBIx/Class/Storage/BlockRunner.pm line 105
DBIx::Class::Storage::BlockRunner::run(DBIx::Class::Storage::BlockRunner=HASH(0x80736aba0), CODE(0x806fccc60), HASH(0x806fbc720)) called at /usr/local/lib/perl5/site_perl/DBIx/Class/Storage.pm line 187
DBIx::Class::Storage::txn_do(DBIx::Class::Storage::DBI::Pg=HASH(0x805c11438), CODE(0x806fccc60), HASH(0x806fbc720)) called at /usr/local/lib/perl5/site_perl/DBIx/Class/Storage/DBI.pm line 861
DBIx::Class::Storage::DBI::txn_do(DBIx::Class::Storage::DBI::Pg=HASH(0x805c11438), CODE(0x806fccc60), HASH(0x806fbc720)) called at /usr/local/lib/perl5/site_perl/DBIx/Class/Schema.pm line 654
DBIx::Class::Schema::txn_do(MyApp::Schema=HASH(0x804e313a8), CODE(0x806fccc60), HASH(0x806fbc720)) called at /usr/local/lib/perl5/site_perl/DBIx/Class/Schema/AuditLog.pm line 75
DBIx::Class::Schema::AuditLog::txn_do(MyApp::Schema=HASH(0x804e313a8), CODE(0x80736a8d0), HASH(0x806fbc720)) called at /root/MyApp/script/../lib/MyApp/Schema.pm line 72
my application code $table1_row->update_or_insert ...

There seems to be a problem with the bottom line.

It seems to be as expected.

% psql db1
db1=# select array_agg(audited_table_id), array_agg(name) from audit_log_field group by audited_table_id, name having count(*) >= 2;
 array_agg  |            array_agg
------------+---------------------------------
 {13,13,13} | {col1,col1,col1}
(1 row)

db1=#

I think I need a bottom line.

diff --git a/lib/DBIx/Class/Schema/AuditLog/Structure/Field.pm b/lib/DBIx/Class/Schema/AuditLog/Structure/Field.pm
index 0aec51b..1777d84 100644
--- a/lib/DBIx/Class/Schema/AuditLog/Structure/Field.pm
+++ b/lib/DBIx/Class/Schema/AuditLog/Structure/Field.pm
@@ -26,6 +26,8 @@ __PACKAGE__->add_columns(

__PACKAGE__->set_primary_key('id');

+__PACKAGE__->add_unique_constraint( [qw/audited_table_id name/] );
+
__PACKAGE__->belongs_to(
    'AuditedTable',
    'DBIx::Class::Schema::AuditLog::Structure::AuditedTable',

DBIx-Class-AuditLog has not been updated, but it has been working fine for many years and I find it useful compared to the other DBIC-related Audit modules in CPAN.
I would like to continue to use it and would appreciate an update.

Thanks,

I was using the module under mysql and it works great, but I had to switch to Oracle. It seems that "user" is a reserved word in Oracle.
When I try to change a row which is auditable, I get:
[error] DBI Exception: DBD::Oracle::st execute failed: ORA-01747: invalid user.table.column, table.column, or columns specification (DBD ERROR: error possibly near <> indicator at char 58 in 'INSERT INTO audit_log_changeset ( description, timestamp, <>user) VALUES ( :p1, :p2, :p3 ) RETURNING id INTO :p4')

Is there a way to change the column name?

Cheers,
Dimitar

Opening as TODO. test case and patches to follow.

When running the deployment script column values are apparently shifted: the view below was produced. This was win7 mysql 5.5.

/View: audit_log_view/

/Column Information/

Field Type Collation Null Key Default Extra Privileges Comment

change_id int(11) (NULL) NO 0 select,insert,update,references
changeset_id varchar(255) latin1_swedish_ci YES (NULL) select,insert,update,references
old_value varchar(255) latin1_swedish_ci YES (NULL) select,insert,update,references
new_value varchar(10) latin1_swedish_ci NO (NULL) select,insert,update,references
action_type varchar(255) latin1_swedish_ci NO (NULL) select,insert,update,references
audited_row int(11) (NULL) NO 0 select,insert,update,references
table_name varchar(255) latin1_swedish_ci YES (NULL) select,insert,update,references
field_name timestamp (NULL) NO 0000-00-00 00:00:00 select,insert,update,references
description varchar(40) latin1_swedish_ci NO (NULL) select,insert,update,references
created_on varchar(40) latin1_swedish_ci NO (NULL) select,insert,update,references
user_name varchar(100) latin1_swedish_ci YES (NULL) select,insert,update,references

/DDL Information/

CREATE ALGORITHM=UNDEFINED DEFINER=ogtool_admin@% SQL SECURITY DEFINER VIEW audit_log_view AS
select
c.id AS change_id,
c.old_value AS changeset_id,
c.new_value AS old_value,
a.action_type AS new_value,
a.audited_row AS action_type,
s.id AS audited_row,
s.description AS table_name,
s.created_on AS field_name,
t.name AS description,
f.name AS created_on,
u.name AS user_name
from (((((audit_log_action a
join audit_log_change c
on ((c.action_id = a.id)))
join audit_log_field f
on ((f.id = c.field_id)))
join audit_log_table t
on ((t.id = a.audited_table_id)))
join audit_log_changeset s
on ((s.id = a.changeset_id)))
left join audit_log_user u
on ((s.user_id = u.id)))

Looks like a relict of pre-Dist::Zilla times.

I thought AuditLog removed the single int PK limitation from Journal, but it looks like it's still there. However, it appears that only a few simple changes would fix that. For one, I found:

https://metacpan.org/source/IONCACHE/DBIx-Class-AuditLog-0.3.1/lib/DBIx/Class/AuditLog.pm#L102

This will blindly accept that id is only one column. However, it may be multiple ones and that would cause an Odd number of elements in anonymous hash error. This could be turned into a join, and audited_row could be a simple varchar. One case of mine is a simple id+id grouping table.

When I am trying to retrieve a change using the $schema->get_changes, I got an error:

DBIx::Class::ResultSet::all(): DBI Exception: DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Change ON Change.action = Action.id WHERE ( ( field = '-1' AND ( audited_row = '' at line 1 [for Statement "SELECT Change.id, Change.action, Change.field, Change.old_value, Change.new_value FROM audit_log_changeset me JOIN audit_log_action Action ON Action.changeset = me.id JOIN audit_log_change Change ON Change.action = Action.id WHERE ( ( field = ? AND ( audited_row = ? AND audited_table = ? AND ( type = ? OR type = ? OR type = ? ) ) ) ) ORDER BY me.id desc" with ParamValues: 0=-1, 1="6", 2='1', 3='insert', 4='update', 5='delete']

I think Change might be stopword for MySQL? Also I don't get why get_changes specify field = ? when my query looks like:

my $schema  = $c->model('DB')->schema->audit_log_schema;
my $changes = $schema->get_changes({ id => $id, table => $table });

Any hints would be appreciated.

PS: Is there a method to create manually auditlog trail?

I'd like to be able to use this to audit a table, but to be able to omit certain fields from the auditing to be a bit more space efficient with fields that i know i don't actually care about the value history of.

DBIx::Class::AuditLog::Structure::ChangeSet seems to use DBIx::Class::TimeStamp to set the created_on timestamp. This way the timestamp is rounded to whole seconds instead of the micosecond precision PostgreSQL's timestamps can deliver. Also it records a different time than other timestamps in the transaction. The now() SQL function always reports the time at the start of the transaction. So the AuditLog's created_on timestamp would record the exact (to the microsecond) same time as other timestamps allowing better correlation and reduced confusion.

Please change it to actually let the database do its job.

Passing a user in the txn information hash does not work anymore. The field seems to be called "user_id" now despite the documentation still using "user":
user => 'username_or_id', # optional

user_id and description are optional and thus NULL is allowed. But when using the AuditLog schema in our application for a simple changelog viewer, the Changeset's User relationship is joined using an inner join causing it to omit all transactions where user information is missing. Specifying a join_type => 'left' would fix this.

From DBIx::Class::Relationship documentation:

   If the relationship is optional -- i.e. the column containing the foreign key can be NULL -- then the belongs_to relationship does the right thing. Thus, in the example above
   "$obj->author" would return "undef".  However in this case you would probably want to set the join_type attribute so that a "LEFT JOIN" is done, which makes complex resultsets involving
   "join" or "prefetch" operations work correctly.  The modified declaration is shown below:

     # in a Book class (where Author has_many Books)
     __PACKAGE__->belongs_to(
       author =>
       'My::DBIC::Schema::Author',
       'author',
       { join_type => 'left' }
     );

The utils folder and therefore the update_reserved_columns.pl script the documentation talks about is missing from the distribution.

This is for purely cosmetic reasons, but I'd like to be able to change the 'audit_log' prefix to something else of my choosing.