Re: [log4perl-devel] implementing a DBI appender, RFC

[Erik W. S. <er...@se...>]

(yes, I'm back from the dead :)

I'll toss in one... it'd be nice if at least the password (if not the 
username) could be obtained from environment variables. What we do 
around here is we have a reasonably secure script that sets certain 
environment variables and give permission to use said script via sudo. 
This lets the DBAs keep their passwords under control.

Now, granted, I'm from the school whereby username / password security 
is just dumb for applications because (a) you gotta bake 'em in 
somewhere, which means when that hacker roots your box, they can find 
it, and (b) you can never change both at the same time and therefore you 
end up just screwing yourself, but that's just me. :)

-e

Kevin Goess wrote:

> I needed to add logging to a database, here's a proposed solution.
>
> Log4j has a JDBCAppender with the warning: "This version of 
> JDBCAppender is very likely to be completely replaced in the future."  
> The API is kind of different, you call append() until the buffer is 
> full, at which point the an sth is created and executed on the 
> buffer.  So the API isn't interchangeable with other appenders.
>
> So I've implemented a DBI appender that's completely controllable from 
> the config file, looking something like this:
>
>     log4j.category = WARN, DBAppndr
>     log4j.appender.DBAppndr             = Log::Log4perl::Appender::DBI
>     log4j.appender.DBAppndr.datasource = DBI:CSV:f_dir=t/tmp
>     log4j.appender.DBAppndr.username  = bobjones
>     log4j.appender.DBAppndr.password = 12345
>     log4j.appender.DBAppndr.sqlStatement = \
>        insert into log4perltest \
>        (level, message, shortmessage) \
>        values (?,?,?)
>     log4j.appender.DBAppndr.params.1 = %p
>     log4j.appender.DBAppndr.params.2 = %m
>     log4j.appender.DBAppndr.params.3 = %5.5m
>     ...
>
> The code is based on Log::Dispatch::DBI by Tatsuhiko Miyagawa.  His 
> module gets behavior changes via subclassing, but by the time I was 
> done I had overridden everything but DESTROY, so I just dropped the 
> dependency on his module.
>
> Below is the only change to existing code, the module and a unit test 
> are attached. Any comments or suggestions?
>
>
> ============================================================
> --- lib/Log/Log4perl/Appender.pm        18 Nov 2002 20:03:39 
> -0000      1.17
> +++ lib/Log/Log4perl/Appender.pm        9 Dec 2002 19:51:31 -0000
> @@ -120,7 +120,10 @@
>                                              $level,
>                                              3 + 
> $Log::Log4perl::caller_depth,
>                                              );
> -    $self->{appender}->log(%$p);
> +    $self->{appender}->log(%$p,
> +                            #these are used by our Appender::DBI
> +                            log4p_category => $category,
> +                            log4p_level  => $level,);
>
>      return 1;
>  }
>
>
>
>------------------------------------------------------------------------
>
>package Log::Log4perl::Appender::DBI;
>
>use Carp;
>
>use strict;
>use Log::Log4perl::Layout::PatternLayout;
>
>use base qw(Log::Dispatch::Output);
>
>
>sub new {
>    my($proto, %params) = @_;
>    my $class = ref $proto || $proto;
>
>    my $self = bless {}, $class;
>
>    $self->_basic_init(%params);
>    $self->_init(%params);
>
>    #e.g.
>    #log4j.appender.DBAppndr.params.1 = %p    
>    #log4j.appender.DBAppndr.params.2 = %5.5m
>    foreach my $pnum (keys %{$params{params}}){
>        $self->{bind_value_layouts}{$pnum} = 
>                Log::Log4perl::Layout::PatternLayout->new(
>                    {ConversionPattern => {value  => $params{params}->{$pnum}}});
>
>    }
>
>    return $self;
>}
>
>
>sub _init {
>    my $self = shift;
>    my %params = @_;
>
>    # set parameters
>    if ($params{dbh}) {
>        $self->{dbh} = $params{dbh};
>    } else {
>        $self->{dbh} = DBI->connect(@params{qw(datasource username password)})
>            or die $DBI::errstr;
>        $self->{_mine} = 1;
>    }
>    $self->{sth} = $self->create_statement($params{sqlStatement});
>}
>
>sub create_statement {
>    my ($self, $stmt) = @_;
>
>    $stmt || die "sqlStatement not set in Log4perl::Appender::DBI";
>
>    return $self->{dbh}->prepare($stmt);
>}
>
>
>sub log_message {
>    my $self = shift;
>    my %p = @_;
>
>    #params is
>    #    { name    => \$appender_name,
>    #      level   => \$Log::Log4perl::Level::L4P_TO_LD{\$level},   
>    #      message => \$message,
>    #      log4p_category => $category,
>    #      log4p_level  => $level,);
>    #    },
>
>    my @qmarks = ();
>
>    foreach my $pnum (keys %{$self->{bind_value_layouts}}){
>        my $msg = $self->{bind_value_layouts}{$pnum}->render(
>                $p{message},
>                $p{log4p_category},
>                $p{log4p_level},
>                5 + $Log::Log4perl::caller_depth,  
>            );
>
>        push @qmarks, $msg;
>    }
>
>    $self->{sth}->execute(@qmarks);
>}
>
>sub DESTROY {
>    my $self = shift;
>    if ($self->{_mine} && $self->{dbh}) {
>        $self->{dbh}->disconnect;
>    }
>}
>
>
>1;
>
>__END__
>
>=head1 NAME
>
>Log::Log4perl::Appender::DBI - implements appending to a DB
>
>=head1 SYNOPSIS
>
>    $dbh->do('CREATE TABLE log4perltest (level char(9), message char(128));
>
>    my $config = <<'EOT';
>    log4j.category = WARN, DBAppndr
>    log4j.appender.DBAppndr             = Log::Log4perl::Appender::DBI
>    log4j.appender.DBAppndr.datasource = DBI:CSV:f_dir=t/tmp
>    log4j.appender.DBAppndr.username  = bobjones
>    log4j.appender.DBAppndr.password = 12345
>    log4j.appender.DBAppndr.sqlStatement = \
>       insert into log4perltest \
>       (level, message, shortmessage, category, millis) \
>       values (?,?,?,?,?)
>    log4j.appender.DBAppndr.params.1 = %p    
>    log4j.appender.DBAppndr.params.2 = %m
>    log4j.appender.DBAppndr.params.3 = %5.5m
>    log4j.appender.DBAppndr.params.4 = %c
>    log4j.appender.DBAppndr.params.5 = %C
>        
>    #want the pattern layout so we can avoid the \n    
>    log4j.appender.DBAppndr.layout    = Log::Log4perl::Layout::PatternLayout
>    log4j.appender.DBAppndr.layout.ConversionPattern    = %m
>
>=head1 DESCRIPTION
>
>This is a specialized Log::Dispatch object customized to work with
>log4perl and its abilities.
>
>The code is based on Log::Dispatch::DBI by Tatsuhiko Miyagawa.  I was
>going to subclass it, but after I was done overriding, there
>wasn't anything left in the superclass!
>
>This is an attempted compromise between what Log::Dispatch::DBI was 
>doing and what log4j's JDBCAppender does.  Since the JDBCAppender
>docs say it "is very likely to be completely replaced in the future"
>and since it looks like it does stuff like prepares the sth each time 
>around (bad performance?) I didn't feel too bad about straying 
>from its example.  And the Log::Dispatch::DBI required subclassing
>to change performance, where log4perl is inspired by the power
>of the config file!
>
>=head1 CHANGING DBH CONNECTIONS (POOLING)
>
>If you want to get your dbh from some place in particular, like
>maybe a pool, subclass and override _init() and/or create_statement(), 
>q.v.
>
>=head1 LIFE OF CONNECTIONS
>
>This module creates an sth when it starts and keeps it for the life
>of the program.  For long-running processes (e.g. mod_perl) this
>may be a problem, your connections may go stale.
>
>It also holds one connection open for every appender, which might
>be too many.
>
>
>=head1 AUTHOR
>
>Kevin Goess <cp...@go...> December, 2002
>
>=head1 SEE ALSO
>
>L<Log::Dispatch::DBI>
>
>L<Log::Log4perl::JavaMap>
>
>L<Log::Log4perl::JavaMap::JDBCAppender>.
>
>=cut
>
>  
>