NAME
Apache2::Controller::DBI::Connector - connects DBI to $r->pnotes->{dbh}.
VERSION
Version 1.000.010 - FIRST RELEASE
SYNOPSIS
CONFIG ALTERNATIVE 1: APACHE CONF
# virtualhost.conf:
PerlLoadModule Apache::DBI
PerlLoadModule Apache2::Controller::Directives
<Location '/'>
A2C_DBI_DSN DBI:mysql:database=foobar;host=localhost
A2C_DBI_User heebee
A2C_DBI_Password jeebee
A2C_DBI_Options RaiseError 1
A2C_DBI_Options AutoCommit 0
# this boolean pushes a PerlLogHandler to run rollback if in_txn
A2C_DBI_Cleanup 1
SetHandler modperl
PerlInitHandler MyApp::Dispatch
PerlHeaderParserHandler Apache2::Controller::DBI::Connector
</Location>CONFIG ALTERNATIVE 2: SUBCLASS
If you need to hide details from the server configuration tree, for example to hide password from access by untrusted cgi scripts, subclass this module and implement your own <dbi_connect_args()> subroutine, which returns argument list for <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 }
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 A2C_DBI_PNOTES_NAME or dbi_pnotes_name().
DESCRIPTION
Connects a package-space DBI handle to $r->pnotes->{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 Apache::DBI and connect your database handles on an ad-hoc basis from your controllers.
If directive A2C_DBI_Cleanup is set, a 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 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 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 dbi_pnotes_name to provide the pnotes key, say "dbh_write" and "dbh_read". In the controller get them with $self->pnotes->{dbh_write} and $self->pnotes->{dbh_read}, etc.
If you subclass DBI, specify your DBI subclass name with the directive A2C_DBI_Class. Note that this has to be connected using a string eval() instead of the block eval() used for normal DBI if you do not specify this directive.
Accessing $dbh from controller
In your Apache2::Controller module for the URI, access the database handle with $self->pnotes->{dbh}, or instead of "dbh", whatever you set in directive A2C_DBI_PNOTES_NAME or return from your overloaded dbi_pnotes_name() method.
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 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.
METHODS
process
Gets DBI connect arguments by calling $self->dbi_connect_args(), then connects $dbh and stashes it in $r->pnotes->{dbh}.
The $dbh has a reference in package space, so controllers using it should always call commit or rollback. It's good practice to use eval anyway and throw an Apache2::Controller::X or your subclass of it (using a2cx(), so you can see the function path trace in the logs when the error occurs.
The package-space $dbh for the child persists across requests, so it is never destroyed. However, it is assigned with DBI->connect() on every request, so that Apache::DBI will cache the database handle and actually connect it only if it cannot be pinged.
dbi_connect_args
Default interprets directives. Apache2::Controller::Directives. You can override this in a subclass to provide your own connect args.
dbi_cleanup
Default interprets directive. "A2C_DBI_Cleanup" in Apache2::Controller::Directives. You can override this in a subclass.
dbi_pnotes_name
Maybe it would be useful to you to overload this. But you'd probably better use the directive "A2C_DBI_Pnotes_Name" in Apache2::Controller::Directives in case other modules (like session) depend on it.
SEE ALSO
Apache2::Controller::Directives
Apache2::Controller::SQL::MySQL
AUTHOR
Mark Hedges, hedges +(a t)- scriptdolphin.org
COPYRIGHT AND LICENSE
Copyright 2008 Mark Hedges. CPAN: markle
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.