mp-

Here's one way to manage your connections. You can create a wrapper class around the actual connection. i've done some *basic* connection management for Win32::ODBC.

(1) have a single place in the code where all connections are made so that it is easy to change connection parameters later;

I kept a default connection string in the dbConnectionClass. All new connections that do not have a connection string specified use the default connection string.

(2) have connections be persistent (Apache::DBI);
The (Win32::ODBC) connections persist in connection pools hashed on their connectionString. a pool is an array of db connections.

(3) have one point in each module at which the connection is made for that module;
I propose that you call new dbConnection liberally and let the underlying connection pooling manage the number of connections kept open.

(4) in a given process, share all connections (i.e. connection is a singleton or is managed by a singleton);
yep...

(5) Have modules that require database access manage their own connection rather than require that a database handle be passed to them;
yes. each method that requires db access should just create a new dbConnection. (you dont care wether a new one is really created or one is pulled from a pool)

package myFoo;
use strict;
use warnings;
use dbConnection;

sub new
{
    my $self  = bless( {}, shift );
    $self->{id} = shift;
    my $db = new dbConnection();

    #
    # build sql
    # load from $db
    #
 

}
[download]

---

#
# this is not my production code... i had to chop it down significantl
+y to for the purpose of this example
# although i welcome comments/suggestions!
#
package dbConnection;
use strict;
use warnings;
use Win32::ODBC;

my %_ConnectionPools;
my $_DefaultConnectionString;


sub DefaultConnectionString
{
    @_ ? $_DefaultConnectionString = shift
       : $_DefaultConnectionString;
}

sub _PoolConnection
{
    my $poolname = shift;
    my $connection = shift;
    my $pool = _GetPool($poolname);
    push @$pool,$connection;
}

sub _GetPool
{
    my $poolname = shift;
    my $pool = $_ConnectionPools{$poolname};
    if (!defined $pool)
    {
        my @connections;
        $pool = \@connections;
        $_ConnectionPools{$poolname} =  $pool;
    }
    return $pool;
}

sub new
{
    my $self  = bless( {}, shift );
    $self->{_ConnectionString} = shift;

    #
    # check if any connection exist in pool
    #
    my $pool = _GetPool( $self->{_ConnectionString} );
    my $connection = shift @$pool;
    if ( ! $connection )
    {
        #
        # no connection exists, create new connection
        #
        $connection = new Win32::ODBC( $self->{_ConnectionString} );
        if ( ! $connection )
        {
            die "Could not connect to [" . $self->{_ConnectionString} 
+. "]";
        }
    }

    $self->{_Connection} = $connection;

    return $self;
}

sub DESTROY
{
    my $self = shift;
    _PoolConnection($self->{_ConnectionString}, $self->{_Connection} )
+;
    $self->{_Connection} = 0;
}


1;
[download]

Hope this was helpful!