This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
Subject: New Version of Apache::DBI which uses 'reauthenticate' instead of caching all connections
From: "Jeff Horn" <jeff@intralect.com>
Date: Sun, 13 Aug 2000 01:34:41 -0500
This is a multi-part message in MIME format.
------=_NextPart_000_000D_01C004C6.A6906310
Content-Type: multipart/alternative;
boundary="----=_NextPart_001_000E_01C004C6.A6906310"
------=_NextPart_001_000E_01C004C6.A6906310
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
I've made some changes which allow this module to be used in situations =
where scripts mess with common and database handle attributes. Each =
connection can be made and get defaults for such attributes modulo any =
changes that are made in the connect call.
This module is very useful in situations where a web application wishes =
to use database security. In this situation, the current Apache::DBI on =
CPAN quickly runs into scaling problems as it attempts to maintain many, =
many connections.
I would like input on whether people think it would be better to =
distribute this in the future as Apache::DBIReauth or somehow meld this =
with the existing Apache::DBI with some configuration options as to =
which kind of caching to use.
Please let me know what you think!
-- Jeff Horn
------=_NextPart_001_000E_01C004C6.A6906310
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dwindows-1252">
<META content=3D"MSHTML 5.50.4134.600" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>I've made some changes which allow this =
module to=20
be used in situations where scripts mess with common and database handle =
attributes. Each connection can be made and get defaults for such=20
attributes modulo any changes that are made in the connect =
call.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>This module is very useful in =
situations where a=20
web application wishes to use database security. In this =
situation, the=20
current Apache::DBI on CPAN quickly runs into scaling problems as it =
attempts to=20
maintain many, many connections.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>I would like input on whether people =
think it would=20
be better to distribute this in the future as Apache::DBIReauth or =
somehow meld=20
this with the existing Apache::DBI with some configuration options as to =
which=20
kind of caching to use.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Please let me know what you =
think!</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>-- Jeff Horn</FONT></DIV></BODY></HTML>
------=_NextPart_001_000E_01C004C6.A6906310--
------=_NextPart_000_000D_01C004C6.A6906310
Content-Type: application/octet-stream;
name="DBI.pm"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="DBI.pm"
package Apache::DBI;
#########################################################################=
#
#
# $Header: /home/cvs/jobplanet/middleware/database/DBI.pm,v 1.3 =
2000/08/12 19:24:48 jeff Exp $
# Hacked Up Version of Apache::DBI
#
# Hacking by: Jeff Horn (jeff@intralect.com)
#
# Purpose: This version of Apache::DBI maintains a SINGLE persistent
# connection to each database. When the user changes on=20
# subsequent connect calls, the DBI->func( ., ., =
'reauthtenticate')
# is called to quickly authenticate the new user on the =
existing
# connection.
#
# Future Plans: Marry this with an LRU caching mechanism so that we can=20
# maintain N persistent connections per Apache child and use =
reauthenticate
# to quickly get a new connection authenticated from the outgoing (via =
LRU)
# one.
#
#########################################################################=
#####
use Apache ();
use DBI ();
use strict;
# $Id: DBI.pm,v 1.3 2000/08/12 19:24:48 jeff Exp $
require_version DBI 1.00;
$Apache::DBI::VERSION =3D '0.87';
# 1: report about new connect
# 2: full debug output
$Apache::DBI::DEBUG =3D 0;
#DBI->trace(2);
my %Connected; # cache for database handles
my @ChildConnect; # connections to be established when a new httpd child =
is created
my %Rollback; # keeps track of pushed PerlCleanupHandler which can =
do a rollback after the request has finished
my %PingTimeOut; # stores the timeout values per data_source, a =
negative value de-activates ping, default =3D 0
my %LastPingTime; # keeps track of last ping per data_source
my $Idx; # key of %Connected and %Rollback.
my $ReauthString; # Username/Password for current connection
# supposed to be called in a startup script.
# stores the data_source of all connections, which are supposed to be =
created upon
# server startup, and creates a PerlChildInitHandler, which initiates =
the connections.
sub connect_on_init {=20
# provide a handler which creates all connections during server =
startup
if(!@ChildConnect and Apache->can('push_handlers')) {
Apache->push_handlers(PerlChildInitHandler =3D> \&childinit);
}
# store connections
push @ChildConnect, [@_];
}
# supposed to be called in a startup script.
# stores the timeout per data_source for the ping function.
# use a DSN without attribute settings specified within !
sub setPingTimeOut {=20
my $class =3D shift;
my $data_source =3D shift;
my $timeout =3D shift;
# sanity check
if ($data_source =3D~ /dbi:\w+:.*/ and $timeout =3D~ /\-*\d+/) {
$PingTimeOut{$data_source} =3D $timeout;
}
}
# the connect method called from DBI::connect
sub connect {
my $class =3D shift;
unshift @_, $class if ref $class;
my $drh =3D shift;
my @args =3D map { defined $_ ? $_ : "" } @_;
my $dsn =3D "dbi:$drh->{Name}:$args[0]";
my $prefix =3D "$$ Apache::DBI ";
# $Idx =3D join $;, $args[0], $args[1], $args[2];
#
# # the hash-reference differs between calls even in the same
# # process, so de-reference the hash-reference=20
# if (3 =3D=3D $#args and ref $args[3] eq "HASH") {
# my ($key, $val);
# while (($key,$val) =3D each %{$args[3]}) {
# $Idx .=3D "$;$key=3D$val";
# }
# } elsif (3 =3D=3D $#args) {
# pop @args;
# }
# We create one Index for every database on every distinct child
$Idx =3D $args[0].":$$";
# don't cache connections created during server initialization; they
# won't be useful after ChildInit, since multiple processes trying =
to
# work over the same database connection simultaneously will receive
# unpredictable query results.
if ($Apache::ServerStarting =3D=3D 1) {
print STDERR "$prefix skipping connection during server startup, =
read the docu !!\n" if $Apache::DBI::DEBUG > 1;
return $drh->connect(@args);
}
# this PerlCleanupHandler is supposed to initiate a rollback after =
the script has finished if AutoCommit is off.
my $needCleanup =3D ($Idx =3D~ /AutoCommit[^\d]+0/) ? 1 : 0;
if(!$Rollback{$Idx} and $needCleanup and =
Apache->can('push_handlers')) {
print STDERR "$prefix push PerlCleanupHandler \n" if =
$Apache::DBI::DEBUG > 1;
Apache->push_handlers("PerlCleanupHandler", \&cleanup);
# make sure, that the rollback is called only once for every=20
# request, even if the script calls connect more than once
$Rollback{$Idx} =3D 1;
}
# do we need to ping the database ?
$PingTimeOut{$dsn} =3D 0 unless defined($PingTimeOut{$dsn});
$LastPingTime{$dsn} =3D 0 unless defined($LastPingTime{$dsn});
my $now =3D time;
my $needping =3D ($PingTimeOut{$dsn} >=3D 0 and $now - =
$LastPingTime{$dsn} > $PingTimeOut{$dsn}) ? 1 : 0;
print STDERR "$prefix need ping: ", $needping =3D=3D 1 ? "yes" : =
"no", "\n" if $Apache::DBI::DEBUG > 1;
$LastPingTime{$dsn} =3D $now;
# check first if there is already a database-handle cached
# if this is the case, possibly verify the database-handle=20
# using the ping-method. Use eval for checking the connection=20
# handle in order to avoid problems (dying inside ping) when=20
# RaiseError being on and the handle is invalid.
if ($Connected{$Idx} and (!$needping or =
eval{$Connected{$Idx}->ping})) {
print STDERR "$prefix already connected to '$Idx'\n"=20
if $Apache::DBI::DEBUG > 1;
my $tmp_reauth_string =3D uc("$args[1]/$args[2]");
if ($tmp_reauth_string eq $ReauthString) {
print STDERR "$prefix - Already connected as =
$ReauthString... ".
"simply returning.\n"
if $Apache::DBI::DEBUG > 1;
return (bless $Connected{$Idx}, 'Apache::DBI::db');
}
else {
print STDERR "$prefix - Using '$Idx'. Reauthenticating from =
".
"'$ReauthString' to '$tmp_reauth_string'\n"
if $Apache::DBI::DEBUG > 1;
if (eval{($Connected{$Idx})->func($tmp_reauth_string, '',=20
'reauthenticate')})
{
print STDERR "$prefix - reauthorization worked. Now ".
"connected to '$Idx' as '$tmp_reauth_string'.\n"
if $Apache::DBI::DEBUG;
$ReauthString =3D $tmp_reauth_string;
# Set Common and Database Handle Attributes for this
# reauthenticated connection
my %NewAttrs;
if (3 =3D=3D $#args and ref $args[3] eq "HASH") {
%NewAttrs=3D%{$args[3]};
}
$Connected{$Idx}->{PrintError}=3D
(defined($NewAttrs{PrintError}))?$NewAttrs{PrintError}:1;
$Connected{$Idx}->{RaiseError}=3D
(defined($NewAttrs{RaiseError}))?$NewAttrs{RaiseError}:'';
$Connected{$Idx}->{ChopBlanks}=3D
(defined($NewAttrs{ChopBlanks}))?$NewAttrs{ChopBlanks}:'';
$Connected{$Idx}->{LongReadLen}=3D
(defined($NewAttrs{LongReadLen}))?$NewAttrs{LongReadLen}:80;
$Connected{$Idx}->{LongTruncOk}=3D
(defined($NewAttrs{LongTruncOk}))?$NewAttrs{LongTruncOk}:'';
$Connected{$Idx}->{AutoCommit}=3D
(defined($NewAttrs{AutoCommit}))?$NewAttrs{AutoCommit}:1;
return (bless $Connected{$Idx}, 'Apache::DBI::db');
}
}
}
# either there is no database handle-cached or it is not valid,
# so get a new database-handle and store it in the cache
delete $Connected{$Idx};
$Connected{$Idx} =3D eval{$drh->connect(@args)};
if ($Connected{$Idx}) {
$ReauthString =3D uc("$args[1]/$args[2]");
# return the new database handle
print STDERR "$prefix new connect to '$Idx' using =
'$ReauthString'\n"=20
if $Apache::DBI::DEBUG;
return (bless $Connected{$Idx}, 'Apache::DBI::db');
}
else {
$ReauthString =3D undef;
return undef;
}
}
# The PerlChildInitHandler creates all connections during server =
startup.
# Note: this handler runs in every child server, but not in the main =
server.
sub childinit {
my $prefix =3D "$$ Apache::DBI ";
print STDERR "$prefix PerlChildInitHandler \n" if =
$Apache::DBI::DEBUG > 1;
if (defined @ChildConnect) {
for my $aref (@ChildConnect) {
shift @$aref;
DBI->connect(@$aref);
$LastPingTime{@$aref[0]} =3D time;
}
}
1;
}
# The PerlCleanupHandler is supposed to initiate a rollback after the =
script has finished if AutoCommit is off.
# Note: the PerlCleanupHandler runs after the response has been sent to =
the client
sub cleanup {
my $prefix =3D "$$ Apache::DBI ";
print STDERR "$prefix PerlCleanupHandler \n" if $Apache::DBI::DEBUG =
> 1;
my $dbh =3D $Connected{$Idx};
if ($Rollback{$Idx} and $dbh and $dbh->{Active} and =
!$dbh->{AutoCommit} and eval {$dbh->rollback}) {
print STDERR "$prefix PerlCleanupHandler rollback for $Idx \n" =
if $Apache::DBI::DEBUG > 1;
}
delete $Rollback{$Idx};
1;
}
# This function can be called from other handlers to perform tasks on =
all cached database handles.
sub all_handlers {
return \%Connected;
}
# patch from Tim Bunce: Apache::DBI will not return a DBD ref cursor
@Apache::DBI::st::ISA =3D ('DBI::st');
# overload disconnect
{ package Apache::DBI::db;
no strict;
@ISA=3Dqw(DBI::db);
use strict;
sub disconnect {
my $prefix =3D "$$ Apache::DBI ";
print STDERR "$prefix disconnect (overloaded) \n" if =
$Apache::DBI::DEBUG > 1;
1;
};
}
# prepare menu item for Apache::Status
Apache::Status->menu_item(
'DBI' =3D> 'DBI connections',
sub {
my($r, $q) =3D @_;
my(@s) =3D =
qw(<TABLE><TR><TD>Datasource</TD><TD>Username</TD></TR>);
for (keys %Connected) {
push @s, '<TR><TD>', join('</TD><TD>', (split($;, =
$_))[0,1]), "</TD></TR>\n";
}
push @s, '</TABLE>';
return \@s;
}
) if ($INC{'Apache.pm'} and Apache->module('Apache::Status'));
1;
__END__
=3Dhead1 NAME
Apache::DBI - Initiate a persistent database connection
=3Dhead1 SYNOPSIS
# Configuration in httpd.conf or startup.pl:
PerlModule Apache::DBI # this comes before all other modules using DBI
Do NOT change anything in your scripts. The usage of this module is=20
absolutely transparent !
=3Dhead1 DESCRIPTION
This module initiates a persistent database connection.=20
The database access uses Perl's DBI. For supported DBI drivers see:=20
http://www.symbolstone.org/technology/perl/DBI/
When loading the DBI module (do not confuse this with the Apache::DBI =
module)=20
it looks if the environment variable GATEWAY_INTERFACE starts with =
'CGI-Perl'=20
and if the module Apache::DBI has been loaded. In this case every =
connect=20
request will be forwarded to the Apache::DBI module. This looks if a =
database=20
handle from a previous connect request is already stored and if this =
handle is=20
still valid using the ping method. If these two conditions are fulfilled =
it=20
just returns the database handle. The parameters defining the connection =
have=20
to be exactly the same, including the connect attributes ! If there is =
no=20
appropriate database handle or if the ping method fails, a new =
connection is=20
established and the handle is stored for later re-use. There is no need =
to=20
remove the disconnect statements from your code. They won't do anything =
because=20
the Apache::DBI module overloads the disconnect method.=20
The Apache::DBI module still has a limitation: it keeps database =
connections=20
persistent on a per process basis. The problem is, if a user accesses =
several=20
times a database, the http requests will be handled very likely by =
different=20
servers. Every server needs to do its own connect. It would be nice, if =
all=20
servers could share the database handles. Currently this is not =
possible,=20
because of the distinct name-space of every process. Also it is not =
possible=20
to create a database handle upon startup of the httpd and then =
inheriting this=20
handle to every subsequent server. This will cause clashes when the =
handle is=20
used by two processes at the same time.=20
With this limitation in mind, there are scenarios, where the usage of=20
Apache::DBI is depreciated. Think about a heavy loaded Web-site where =
every=20
user connects to the database with a unique userid. Every server would =
create =20
many database handles each of which spawning a new backend process. In a =
short=20
time this would kill the web server.=20
Another problem are timeouts: some databases disconnect the client after =
a=20
certain time of inactivity. The module tries to validate the database =
handle=20
using the ping-method of the DBI-module. This method returns true as =
default.=20
If the database handle is not valid and the driver has no implementation =
for=20
the ping method, you will get an error when accessing the database. As a =
work-around you can try to replace the ping method by any database =
command,=20
which is cheap and safe or you can deactivate the usage of the ping =
method=20
(see CONFIGURATION below).=20
Here is generalized ping method, which can be added to the driver =
module:
{ package DBD::xxx::db; # =3D=3D=3D=3D=3D=3D DATABASE =
=3D=3D=3D=3D=3D=3D
use strict;
sub ping {
my($dbh) =3D @_;
my $ret =3D 0;
eval {
local $SIG{__DIE__} =3D sub { return (0); };
local $SIG{__WARN__} =3D sub { return (0); };
# adapt the select statement to your database:
my $sth =3D $dbh->prepare("SELECT 1");
$ret =3D $sth && ($sth->execute);
$sth->finish;
};
return ($@) ? 0 : $ret;
}
}
Transactions: a standard DBI script will automatically perform a =
rollback
whenever the script exits. In the case of persistent database =
connections,
the database handle will not be destroyed and hence no automatic =
rollback=20
occurs. At a first glance it seems even to be possible, to handle a =
transaction=20
over multiple requests. But this should be avoided, because different
requests are handled by different servers and a server does not know the =
state=20
of a specific transaction which has been started by another server. In =
general=20
it is good practice to perform an explicit commit or rollback at the end =
of=20
every script. In order to avoid inconsistencies in the database in case=20
AutoCommit is off and the script finishes without an explicit rollback, =
the=20
Apache::DBI module uses a PerlCleanupHandler to issue a rollback at the
end of every request. Note, that this CleanupHandler will only be used, =
if=20
the initial data_source sets AutoCommit =3D 0. It will not be used, if =
AutoCommit=20
will be turned off, after the connect has been done.=20
This module plugs in a menu item for Apache::Status. The menu lists the=20
current database connections. It should be considered incomplete because =
of=20
the limitations explained above. It shows the current database =
connections=20
for one specific server, the one which happens to serve the current =
request.=20
Other servers might have other database connections. The Apache::Status =
module=20
has to be loaded before the Apache::DBI module !
=3Dhead1 CONFIGURATION
The module should be loaded upon startup of the Apache daemon.
Add the following line to your httpd.conf or startup.pl:
PerlModule Apache::DBI
It is important, to load this module before any other modules using DBI =
!=20
A common usage is to load the module in a startup file via the =
PerlRequire=20
directive. See eg/startup.pl for an example.=20
There are two configurations which are server-specific and which can be =
done=20
upon server startup:=20
Apache::DBI->connect_on_init($data_source, $username, $auth, \%attr)
This can be used as a simple way to have apache servers establish =
connections=20
on process startup.=20
Apache::DBI->setPingTimeOut($data_source, $timeout)
This configures the usage of the ping method, to validate a connection.=20
Setting the timeout to 0 will always validate the database connection=20
using the ping method (default). Setting the timeout < 0 will =
de-activate=20
the validation of the database handle. This can be used for drivers, =
which=20
do not implement the ping-method. Setting the timeout > 0 will ping the=20
database only if the last access was more than timeout seconds before.=20
For the menu item 'DBI connections' you need to call Apache::Status =
BEFORE=20
Apache::DBI ! For an example of the configuration order see startup.pl.=20
To enable debugging the variable $Apache::AuthDBI::DEBUG must be set. =
This=20
can either be done in startup.pl or in the user script. Setting the =
variable=20
to 1, just reports about a new connect. Setting the variable to 2 =
enables full=20
debug output.=20
=3Dhead1 PREREQUISITES
Note that this module needs mod_perl-1.08 or higher, apache_1.3.0 or =
higher=20
and that mod_perl needs to be configured with the appropriate call-back =
hooks:=20
PERL_CHILD_INIT=3D1 PERL_STACKED_HANDLERS=3D1.=20
=3Dhead1 SEE ALSO
L<Apache>, L<mod_perl>, L<DBI>
=3Dhead1 AUTHORS
=3Ditem *
mod_perl by Doug MacEachern <modperl@apache.org>
=3Ditem *
DBI by Tim Bunce <dbi-users@isc.org>
=3Ditem *
Apache::AuthenDBI by Edmund Mergl <E.Mergl@bawue.de>
=3Dhead1 COPYRIGHT
The Apache::DBI module is free software; you can redistribute it and/or
modify it under the same terms as Perl itself.
=3Dcut
#########################################################################=
###3
# $Log: DBI.pm,v $
# Revision 1.3 2000/08/12 19:24:48 jeff
# Added code to reset common and database handle attributes
# after reauthentication based on attribute hash passed in and
# DBI defaults.
#
# Revision 1.2 2000/06/15 14:19:40 jeff
# Got CVS tags right.
#
#########################################################################=
###3
------=_NextPart_000_000D_01C004C6.A6906310--
===
Subject: RE: Apache::DBI using 'reauthenticate' instead of caching
From: Geoffrey Young <gyoung@laserlink.net>
Date: Fri, 11 Aug 2000 15:00:58 -0400
very interesting. I might suggest a config option to toggle
the functionality within Apache::DBI instead of a new module
entirely. Or maybe including it as another module within
the Apache::DBI distribution. I don't know how everyone else
feels, but all the stuff in the Apache:: namespace is
generic and this being largly Apache::DBI except for an
Oracle specific enhancement doesn't sound like it warrants
it's own thing. But certainly don't let me be the final
word :)
your enhancement, if user configurable within Apache::DBI,
might make it possible to jump between cached or
reauthenticated connections on a vhosts basis. Now that's
cool...
BTW, I did submit a (rather ugly) fix for a (minor but important to me) bug
in Apache::DBI way back in december - it was never implemented by the author
for unknown reasons - see http://marc.theaimsgroup.com/?l=apache-modperl
<http://marc.theaimsgroup.com/?l=apache-modperl&m=94699007507299&w=2>
&m=94699007507299&w=2 for the details if you are interested.
===