Skip Menu |

This queue is for tickets about the Log-Dispatch-DBI CPAN distribution.

Report information
The Basics
Id: 35077
Status: open
Priority: 0/
Queue: Log-Dispatch-DBI

People
Owner: Nobody in particular
Requestors: dmuey [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: Wishlist
Broken in: (no value)
Fixed in: (no value)



Subject: support additional column param
Hello, great module, thnaks! WHat would be nie is to have a way to specify additional column handlingg. For example there is no way to set a DEFAULT of NOW() for a DATETIME column in MySQL (and timestamp types are really bad to use for actual date and time needs) I'd be nice to say that if this key exists modify the QUERY and execute() call/ For example if I did: # values must be suitable for ? bind data in execute(), # or a code reference return a suitable value additional_query_columns => { 'id' => 'NULL', 'created_on' => 'NOW()', }, The QUERY would be: INSERT INTO $self->{table} (level, message, id, created_on) VALUES (?, ?, ?, ?) and the execute would be: execute(@params{qw(level message), 'NULL','NOW()'}) This would alleviate the need to create a subclass for every Db nuance (and allow me to be explicit since I like to avoid ambiguity when possible) If you're interested I'd be happy to make a patch for you, otherwise I'll make a subclass thanks!
Show quoted text
> 'id' => 'NULL', > 'created_on' => 'NOW()',
I should note that it'd need to be 'id' => undef, or even better, support not using placeholders since you can't: 'created_on' => 'NOW()', in other words it'd need to be: INSERT INTO $self->{table} (level, message, id, created_on) VALUES (?, ?, NULL, NOW()) and then the execute call does not change...
The attached patch has code changes and POD that do not change its default behavior but add more flexibility if its needed. If you're interested in it I'd be happy to make the tests and Changelog entries for it as well. Thanks!
The attached patch has code changes and POD that do not change its default behavior but add more flexibility if its needed. If you're interested in it I'd be happy to make the tests and Changelog entries for it as well. Thanks!
--- DBI.pm.orig 2008-04-16 18:50:13.000000000 -0500 +++ DBI.pm 2008-04-16 19:44:31.000000000 -0500 @@ -2,7 +2,7 @@ use strict; use vars qw($VERSION); -$VERSION = 0.02; +$VERSION = 0.03; use Log::Dispatch 2.00; use base qw(Log::Dispatch::Output); @@ -34,13 +34,19 @@ } $self->{table} = $params{table} || 'log'; + $self->{statement_columns} = $params{statement_columns} || 'level, message'; + $self->{statement_values} = $params{statement_values} || '?, ?'; + if (exists $params{append_to_execute_args}) { + $self->{append_to_execute_args} = $params{append_to_execute_args}; + } + $self->{execute_level_as_number} = $params{execute_level_as_number} : 0; $self->{sth} = $self->create_statement; } sub create_statement { my $self = shift; return $self->{dbh}->prepare(<<"SQL"); -INSERT INTO $self->{table} (level, message) VALUES (?, ?) +INSERT INTO $self->{table} ($self->{statement_columns}) VALUES ($self->{statement_columns}) SQL ; } @@ -48,7 +54,26 @@ sub log_message { my $self = shift; my %params = @_; - $self->{sth}->execute(@params{qw(level message)}); + my @execute = $self->{execute_level_as_number} ? ( $self->_level_as_number($params{level}) , $params{message}) : (@params{qw(level message)}); + + my @append; + if (exists $self->{append_to_execute_args}) { + if ( ref $self->{append_to_execute_args} eq 'CODE' ) { + @append = $self->{append_to_execute_args}->(); + } + elsif ( ref $self->{append_to_execute_args} eq 'ARRAY' ) { + @append = @{ $self->{append_to_execute_args} }; + } + elsif ( my $cr = $self->{append_to_execute_args}->can('get_log_dispatch_dbi_append_to_execute_args') ) { + @append = $cr->( $self->{append_to_execute_args} ); + } + else { + @append = ($self->{append_to_execute_args}); + } + push @execute, @append if @append; + } + + $self->{sth}->execute( @execute ); } sub DESTROY { @@ -122,6 +147,41 @@ Table name for logging. default is B<log>. +=item -- statement_columns + +SQL list of columns to insert into. Default is 'level, message' + + +=item -- statement_values + +SQL list of items for VALUES() including placeholders. Default is '?, ?' + +=item -- append_to_execute_args + +If increasing the number of SQL placeholders with 'statement_values' you will probably also want to pass more to execute() than 'level' and 'message' + +You can do that with 'append_to_execute_args' + +It can be an array ref of items, a code ref that returns an array of items (empty array results in no addtional values), an object with a method called 'get_log_dispatch_dbi_append_to_execute_args' that returns an array of items(empty array results in no addtional values), or a single item (undef becomes NULL usually) + +Default is [] if you do not specify it. + +Be carefull as it's mere existance regardless of if the value is undef or '' will get an addition argument to execute! + +=item -- execute_level_as_number + +If set to true, DBI's execute() will get the level in numeric form instead of textual. + +For example instead of 'debug' it will insert '0', 'info' will be 1, etc + +This is useful to facilitate the use of simple math in queries like this: 'WHERE level >= 2 AND < 6' + +Default is 0 so as not to change the default behavior. + +In L<TABLE SCHEMA> below you'd change the 'level' column like so: + + level int(1) NOT NULL, + =item log_message inherited from Log::Dispatch::Output. @@ -151,6 +211,36 @@ Log::Dispatch::DBI and override C<create_statement> and C<log_message> method. +=head1 Advanced SCHEMA Example + +Maybe something like this for MySQL. + + CREATE TABLE log ( + id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, + datetime datetime, + level int(1) NOT NULL, + message text NOT NULL, + ); + +Then in the object: + + $log->add( + Log::Dispatch::DBI->new( + ... + 'statement_columns' => 'id, when, level, message', + 'statement_values' => 'NULL, NOW(), ?, ?', + 'execute_level_as_number' => 1, + ) + ); + + ... + + $log->log(level => 'info', message => 'too bad'); + +will execute the following SQL: + + INSERT INTO log (id, when, level, message) VALUES(NULL, NOW(), 1, 'too bad'); + =head1 AUTHOR Tatsuhiko Miyagawa E<lt>miyagawa@bulknews.netE<gt>
On Wed Apr 16 20:52:22 2008, DMUEY wrote: Show quoted text
> The attached patch has code changes and POD that do not change its > default behavior but add > more flexibility if its needed.
This fixes a POD mistake.
--- DBI.pm.orig 2008-04-16 18:50:13.000000000 -0500 +++ DBI.pm 2008-04-16 19:44:31.000000000 -0500 @@ -2,7 +2,7 @@ use strict; use vars qw($VERSION); -$VERSION = 0.02; +$VERSION = 0.03; use Log::Dispatch 2.00; use base qw(Log::Dispatch::Output); @@ -34,13 +34,19 @@ } $self->{table} = $params{table} || 'log'; + $self->{statement_columns} = $params{statement_columns} || 'level, message'; + $self->{statement_values} = $params{statement_values} || '?, ?'; + if (exists $params{append_to_execute_args}) { + $self->{append_to_execute_args} = $params{append_to_execute_args}; + } + $self->{execute_level_as_number} = $params{execute_level_as_number} : 0; $self->{sth} = $self->create_statement; } sub create_statement { my $self = shift; return $self->{dbh}->prepare(<<"SQL"); -INSERT INTO $self->{table} (level, message) VALUES (?, ?) +INSERT INTO $self->{table} ($self->{statement_columns}) VALUES ($self->{statement_columns}) SQL ; } @@ -48,7 +54,26 @@ sub log_message { my $self = shift; my %params = @_; - $self->{sth}->execute(@params{qw(level message)}); + my @execute = $self->{execute_level_as_number} ? ( $self->_level_as_number($params{level}) , $params{message}) : (@params{qw(level message)}); + + my @append; + if (exists $self->{append_to_execute_args}) { + if ( ref $self->{append_to_execute_args} eq 'CODE' ) { + @append = $self->{append_to_execute_args}->(); + } + elsif ( ref $self->{append_to_execute_args} eq 'ARRAY' ) { + @append = @{ $self->{append_to_execute_args} }; + } + elsif ( my $cr = $self->{append_to_execute_args}->can('get_log_dispatch_dbi_append_to_execute_args') ) { + @append = $cr->( $self->{append_to_execute_args} ); + } + else { + @append = ($self->{append_to_execute_args}); + } + push @execute, @append if @append; + } + + $self->{sth}->execute( @execute ); } sub DESTROY { @@ -122,6 +147,41 @@ Table name for logging. default is B<log>. +=item -- statement_columns + +SQL list of columns to insert into. Default is 'level, message' + + +=item -- statement_values + +SQL list of items for VALUES() including placeholders. Default is '?, ?' + +=item -- append_to_execute_args + +If increasing the number of SQL placeholders with 'statement_values' you will probably also want to pass more to execute() than 'level' and 'message' + +You can do that with 'append_to_execute_args' + +It can be an array ref of items, a code ref that returns an array of items (empty array results in no addtional values), an object with a method called 'get_log_dispatch_dbi_append_to_execute_args' that returns an array of items(empty array results in no addtional values), or a single item (undef becomes NULL usually) + +Default is [] if you do not specify it. + +Be carefull as it's mere existance regardless of if the value is undef or '' will get an addition argument to execute! + +=item -- execute_level_as_number + +If set to true, DBI's execute() will get the level in numeric form instead of textual. + +For example instead of 'debug' it will insert '0', 'info' will be 1, etc + +This is useful to facilitate the use of simple math in queries like this: 'WHERE level >= 2 AND < 6' + +Default is 0 so as not to change the default behavior. + +In L<TABLE SCHEMA> below you'd change the 'level' column like so: + + level int(1) NOT NULL, + =item log_message inherited from Log::Dispatch::Output. @@ -151,6 +211,36 @@ Log::Dispatch::DBI and override C<create_statement> and C<log_message> method. +=head1 Advanced SCHEMA Example + +Maybe something like this for MySQL. + + CREATE TABLE log ( + id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, + datetime datetime, + level int(1) NOT NULL, + message text NOT NULL, + ); + +Then in the object: + + $log->add( + Log::Dispatch::DBI->new( + ... + 'statement_columns' => 'id, datetime, level, message', + 'statement_values' => 'NULL, NOW(), ?, ?', + 'execute_level_as_number' => 1, + ) + ); + + ... + + $log->log(level => 'info', message => 'too bad'); + +will execute the following SQL: + + INSERT INTO log (id, when, level, message) VALUES(NULL, NOW(), 1, 'too bad'); + =head1 AUTHOR Tatsuhiko Miyagawa E<lt>miyagawa@bulknews.netE<gt>
Subject: Re: [rt.cpan.org #35077] support additional column param
Date: Wed, 16 Apr 2008 18:39:58 -0700
To: bug-Log-Dispatch-DBI [...] rt.cpan.org
From: "Tatsuhiko Miyagawa" <miyagawa [...] gmail.com>
Do you want to take over this module? If you have a PAUSE ID let me know. I'll give you a maint bit. On Wed, Apr 16, 2008 at 5:54 PM, Daniel Muey via RT <bug-Log-Dispatch-DBI@rt.cpan.org> wrote: Show quoted text
> > Queue: Log-Dispatch-DBI > Ticket <URL: http://rt.cpan.org/Ticket/Display.html?id=35077 > > > > On Wed Apr 16 20:52:22 2008, DMUEY wrote:
> > The attached patch has code changes and POD that do not change its > > default behavior but add > > more flexibility if its needed.
> > This fixes a POD mistake. > > > > > --- DBI.pm.orig 2008-04-16 18:50:13.000000000 -0500 > +++ DBI.pm 2008-04-16 19:44:31.000000000 -0500 > @@ -2,7 +2,7 @@ > > use strict; > use vars qw($VERSION); > -$VERSION = 0.02; > +$VERSION = 0.03; > > use Log::Dispatch 2.00; > use base qw(Log::Dispatch::Output); > @@ -34,13 +34,19 @@ > } > > $self->{table} = $params{table} || 'log'; > + $self->{statement_columns} = $params{statement_columns} || 'level, message'; > + $self->{statement_values} = $params{statement_values} || '?, ?'; > + if (exists $params{append_to_execute_args}) { > + $self->{append_to_execute_args} = $params{append_to_execute_args}; > + } > + $self->{execute_level_as_number} = $params{execute_level_as_number} : 0; > $self->{sth} = $self->create_statement; > } > > sub create_statement { > my $self = shift; > return $self->{dbh}->prepare(<<"SQL"); > -INSERT INTO $self->{table} (level, message) VALUES (?, ?) > +INSERT INTO $self->{table} ($self->{statement_columns}) VALUES ($self->{statement_columns}) > SQL > ; > } > @@ -48,7 +54,26 @@ > sub log_message { > my $self = shift; > my %params = @_; > - $self->{sth}->execute(@params{qw(level message)}); > + my @execute = $self->{execute_level_as_number} ? ( $self->_level_as_number($params{level}) , $params{message}) : (@params{qw(level message)}); > + > + my @append; > + if (exists $self->{append_to_execute_args}) { > + if ( ref $self->{append_to_execute_args} eq 'CODE' ) { > + @append = $self->{append_to_execute_args}->(); > + } > + elsif ( ref $self->{append_to_execute_args} eq 'ARRAY' ) { > + @append = @{ $self->{append_to_execute_args} }; > + } > + elsif ( my $cr = $self->{append_to_execute_args}->can('get_log_dispatch_dbi_append_to_execute_args') ) { > + @append = $cr->( $self->{append_to_execute_args} ); > + } > + else { > + @append = ($self->{append_to_execute_args}); > + } > + push @execute, @append if @append; > + } > + > + $self->{sth}->execute( @execute ); > } > > sub DESTROY { > @@ -122,6 +147,41 @@ > > Table name for logging. default is B<log>. > > +=item -- statement_columns > + > +SQL list of columns to insert into. Default is 'level, message' > + > + > +=item -- statement_values > + > +SQL list of items for VALUES() including placeholders. Default is '?, ?' > + > +=item -- append_to_execute_args > + > +If increasing the number of SQL placeholders with 'statement_values' you will probably also want to pass more to execute() than 'level' and 'message' > + > +You can do that with 'append_to_execute_args' > + > +It can be an array ref of items, a code ref that returns an array of items (empty array results in no addtional values), an object with a method called 'get_log_dispatch_dbi_append_to_execute_args' that returns an array of items(empty array results in no addtional values), or a single item (undef becomes NULL usually) > + > +Default is [] if you do not specify it. > + > +Be carefull as it's mere existance regardless of if the value is undef or '' will get an addition argument to execute! > + > +=item -- execute_level_as_number > + > +If set to true, DBI's execute() will get the level in numeric form instead of textual. > + > +For example instead of 'debug' it will insert '0', 'info' will be 1, etc > + > +This is useful to facilitate the use of simple math in queries like this: 'WHERE level >= 2 AND < 6' > + > +Default is 0 so as not to change the default behavior. > + > +In L<TABLE SCHEMA> below you'd change the 'level' column like so: > + > + level int(1) NOT NULL, > + > =item log_message > > inherited from Log::Dispatch::Output. > @@ -151,6 +211,36 @@ > Log::Dispatch::DBI and override C<create_statement> and C<log_message> > method. > > +=head1 Advanced SCHEMA Example > + > +Maybe something like this for MySQL. > + > + CREATE TABLE log ( > + id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, > + datetime datetime, > + level int(1) NOT NULL, > + message text NOT NULL, > + ); > + > +Then in the object: > + > + $log->add( > + Log::Dispatch::DBI->new( > + ... > + 'statement_columns' => 'id, datetime, level, message', > + 'statement_values' => 'NULL, NOW(), ?, ?', > + 'execute_level_as_number' => 1, > + ) > + ); > + > + ... > + > + $log->log(level => 'info', message => 'too bad'); > + > +will execute the following SQL: > + > + INSERT INTO log (id, when, level, message) VALUES(NULL, NOW(), 1, 'too bad'); > + > =head1 AUTHOR > > Tatsuhiko Miyagawa E<lt>miyagawa@bulknews.netE<gt> > >
-- Tatsuhiko Miyagawa
On Wed Apr 16 21:46:32 2008, miyagawa@gmail.com wrote: Show quoted text
> Do you want to take over this module? > > If you have a PAUSE ID let me know. I'll give you a maint bit. >
I sure do: DMUEY. I'd be happy to co maintain it! thanks
Subject: Re: [rt.cpan.org #35077] support additional column param
Date: Wed, 16 Apr 2008 19:01:15 -0700
To: bug-Log-Dispatch-DBI [...] rt.cpan.org
From: "Tatsuhiko Miyagawa" <miyagawa [...] gmail.com>
Okay, added to the co-maintainer list. Upload on your own ;) On Wed, Apr 16, 2008 at 7:00 PM, Daniel Muey via RT <bug-Log-Dispatch-DBI@rt.cpan.org> wrote: Show quoted text
> > Queue: Log-Dispatch-DBI > Ticket <URL: http://rt.cpan.org/Ticket/Display.html?id=35077 > > > > On Wed Apr 16 21:46:32 2008, miyagawa@gmail.com wrote:
> > Do you want to take over this module? > > > > If you have a PAUSE ID let me know. I'll give you a maint bit. > >
> > I sure do: DMUEY. I'd be happy to co maintain it! > > thanks >
-- Tatsuhiko Miyagawa
On Wed Apr 16 22:01:31 2008, miyagawa@gmail.com wrote: Show quoted text
> Okay, added to the co-maintainer list. Upload on your own ;)
Wow, your are lightning quick :) thanks
On Wed Apr 16 22:04:28 2008, DMUEY wrote: Show quoted text
> On Wed Apr 16 22:01:31 2008, miyagawa@gmail.com wrote:
> > Okay, added to the co-maintainer list. Upload on your own ;)
> > Wow, your are lightning quick :) thanks
I take it you didn't release anything or are they better logging distros now for logging to a DB?
Show quoted text
> I take it you didn't release anything or
I don't believe I did, life went nuts right about that time :) Show quoted text
> are they better logging distros now for logging to a DB?
Log-Dispatch-DBI should still OK, it just doesn't have the patches here that support an additional column param AFAIK, it was updated in 2014 so maybe it does now?