view release on metacpan or  search on metacpan

lib/Apache2/Controller/DBI/Connector.pm  view on Meta::CPAN

subclass this module and implement your own C<<dbi_connect_args()>>
subroutine, which returns argument list for C<<DBI->connect()>>.

 PerlLoadModule Apache::DBI
 <Location '/'>
     SetHandler                 modperl

     PerlInitHandler            MyApp::Dispatch
     PerlHeaderParserHandler    MyApp::DBIConnect
 </Location>

 package MyApp::DBIConnect;
 use base qw( Apache2::Controller::DBI::Connector );
 sub dbi_connect_args {
     my ($self) = @_;
     return (
         'DBI:mysql:database=foobar;host=localhost',
         'heebee', 'jeebee',
         { RaiseError => 1, AutoCommit => 0 }
     );
 }
 sub dbi_cleanup { 1 }
 sub dbi_pnotes_name { 'dbh' }

 1;

You also have to use overloaded subs in a subclass if you want
to set up multiple DBH handles by specifying the name for the
key in pnotes using C<< A2C_DBI_PNOTES_NAME >> or C<< dbi_pnotes_name() >>.

=head1 DESCRIPTION

Connects a package-space L<DBI> handle to C<< $r->pnotes->{a2c}{dbh} >>.

You only need this where you need a database handle for every
request, for example to connect to a session database regardless of
whether the user does anything.

You can load it only for certain locations, so the handle will get
connected only there.

Otherwise you probably just want to use L<Apache::DBI> and connect
your database handles on an ad-hoc basis from your controllers.

If directive C<< A2C_DBI_Cleanup >> is set, a C<< PerlLogHandler >>
gets pushed which will roll back any open transactions.  So if your
controller does some inserts and then screws up, you don't have to 
worry about trapping these in eval if you want the DBI errors to
bubble up.  They will be automatically rolled back since C<< commit() >>
was never called.

(This used to be a PerlCleanupHandler, but it appears that Apache
hands this off to a thread even if running under prefork, and
cleanup doesn't always get processed before the child handles
the next request.  At least, this is true under L<Apache::Test>.
Wacky.  So, it's a PerlLogHandler to make sure the commit or
rollback gets done before the connection dies.)

If you subclass, you can set up multiple dbh handles with different params:

 <Location '/busy/database/page'>
     SetHandler modperl

     PerlInitHandler         MyApp::Dispatch
     PerlHeaderParserHandler MyApp::DBI::Writer MyApp::DBI::Read
 </Location>

If you use a tiered database structure with one master record
and many replicated nodes, you can do it this way.  Then you 
overload C<< dbi_pnotes_name >> to provide the pnotes key,
say "dbh_write" and "dbh_read".  In the controller get them
with C<< $self->pnotes->{a2c}{dbh_write} >> and
C<< $self->pnotes->{a2c}{dbh_read} >>, etc.

If you subclass DBI, specify your DBI subclass name with
the directive C<< A2C_DBI_Class >>.  Note that this has
to be connected using a string C<< eval() >> instead of
the block C<< eval() >> used for normal L<DBI> if you
do not specify this directive.

=head1 Accessing $dbh from controller

In your L<Apache2::Controller> module for the URI, access the
database handle with C<< $self->pnotes->{a2c}{dbh} >>, or instead of
"dbh", whatever you set in directive C<< A2C_DBI_PNOTES_NAME >> 
or return from your overloaded C<< dbi_pnotes_name() >> method.

=head1 WARNING - DATABASE MEMORY USAGE

Because a reference persists in package space, the database handle
will remain connected after a request ends.

Usually Apache will rotate requests through child processes.

This means that on a lightly-loaded server with a lot of spare child processes,
you will quickly get a large number of idle database connections, one per child.

To solve this you need to set your database handle idle timeout
to some small number of seconds, say 5 or 10.  Then you load
L<Apache::DBI> in your Apache config file so they automatically
get reconnected if needed.  

Then when you get a load increase, handles are connected that persist
across requests long enough to handle the next request, but during
idle times, your database server conserves resources.

There are various formulas for determining how much memory is
needed for the maximum number of connections your database server 
provides.  MySQL has a formula in their docs somewhere to calculate
memory needed for InnoDB handles. It is weird. 

When using
persistent database connections, it's a good idea to limit the
max number of Apache children to the max number of database connections
that your server can provide.  Find a formula from your vendor's 
documentation, if one exists, or wing it.

=cut

use strict;
use warnings FATAL => 'all';