Re: [CDBI] Re: odd swapping database issue on vhost / mason install

On Dienstag 03 April 2007, you wrote:
> On 4/3/07, Rolf Schaufelberger <[email protected]> wrote:
> > I'm using this setup since years and have many vhosts running in one
> > apache/mod_perl setup and with identical CDBI-Source without problems
> > (and withut disabling the object index!).
>
> Do they all use the same database connection parameters?

No. each vhost has another user/password

>
> > The search_path is set in db_Main, no
> > problems with that at all.
>
> So you're overriding db_Main to change schemas to the right one for
> the current vhost?

Yes. 
Here is my db_Main, adapted from the mod_perl entry of the wiki:


sub db_Main {
    my $self = shift;
    my $dbh;
    my $key = 'dbh_' . $app->{DbUser};

    if ($ENV{'MOD_PERL'} and !$Apache::Server::Starting) {
        $dbh = Apache->request()->pnotes($key);
    }

    if (!$dbh) {
        my $dsn = _getDSN;

        # $app is my config hash
        $dbh = DBI->connect_cached($dsn, $app->{DbUser}, $app->{DbPass}, 
$db_options);
        if ($ENV{'MOD_PERL'} and !$Apache::Server::Starting) {
            Apache->request()->pnotes($key, $dbh);
        }
        $dbh->do("set search_path to " . 
$app->{DbSearchPath} . "public,web,system,orders")
          or die $dbh->errstr;
        $dbh->do("set client_encoding to 'utf-8'");
    }
    return $dbh;
}

All connection parameters are taken from a  HASH  $app, and this hash is set 
per request via a method from my mason handler.

 In my DB.pm:
..
===========
my $app; 

sub app {
    my $class = shift;
    $app = shift;
}

sub db_Main {...}

============

and I have spltted my data in the schemas  web, system, orders. public is for 
the session only, and the vhost specific tables (company , user, prices, etc. 
are in a schema specific to each vhost and set with the param 
$app->{DbSearchPath}. 

That way i have 100% identical  source for all vhosts and can keep my vhost 
specific data totally isolated from each other.  I have  granted USAGE to the 
vhost specific schema only to the specific user, while the other schemas have 
USAGE to all users. This goes even further : I' m doing some calculation with 
pl/pgsql and so I can keep a function "calculate_price" in each vhost 
specific schema and thus implement different calculation logic for each 
application. 

Well, the background is,  that I have different variations of the same 
applicaton running like this (well otherwise an identical database would make 
no sense ;-), but for instance, one app belongs to a company in germany 
another to a comp. in UK and they may have different logic in calculating 
taxes. 

As said, works without problems that way.

Rolf