Obviously I have not yet thought of anything.
ioncache / dbix-class-auditlog Goto Github PK
View Code? Open in Web Editor NEWSimple activity audit logging for DBIx::Class
Home Page: https://metacpan.org/module/DBIx::Class::AuditLog
Simple activity audit logging for DBIx::Class
Home Page: https://metacpan.org/module/DBIx::Class::AuditLog
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.
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.
DBIx-Class-AuditLog/lib/DBIx/Class/AuditLog.pm
Lines 131 to 132 in b5239c2
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.
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
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.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.