Hello Lawrence:
If I were doing this with Sybase or MS SQL Server,
using SQL syntax like:
insert targetdb.tablename select * from
sourcedb.tablename
would have the performance advantage of avoiding
network traffic and avoiding passing your data through
the client libraries. Everything would be done within
the database server.
David
--- Lawrence Waterhouse
<[EMAIL PROTECTED]> wrote:
> Hello everyone,
>
> First I would like to apologise for this long mail
> but I really need
> some advices on a web application I am currently
> working on. This
> application makes extensive use of DBI and interacts
> with many
> databases at the same time.
>
> I will try to resume my setup the best way I can,
> English is not my
> native language so I hope everyone will understand
> me well even if my
> English is not perfect :P
>
> Server:
> Apache 1.3.29 + mod_perl 1.29 + Mason 1.26
> Perl 5.85 / DBI 1.45 + DBD::mysql-2.9004 +
> Apache::DBI 0.94
>
> This is a multi-client web application, each client
> can have more than
> one user, there is a 'master' database which is
> global to everyone and
> contain among other things a 'users' table used to
> store credentials
> of the users.
>
> Here is the complex part: each client has 2
> databases
> ('client-clientname' and 'archives-clientname')
> which are specific to
> the client. So basically, each client has access to
> three databases,
> the master and the 2 client specific database.
>
> At the moment, I use a global hash to aggregate all
> my dbh, and I am
> not sure if this is the right way to do it. The
> master dbh is
> initiated in my handler.pl, while the two client
> specific database
> connections are initiated after the user log-in to
> the web
> application.
>
> Here are the relevant portion of my handler.pl
>
> use Apache::DBI;
> use DBI;
>
> …
>
> # DSN declaration
> my $dsn =
> "DBI:$db_type:host=127.0.0.1;dbname=master";
>
> # Debug mode
> $Apache::DBI::DEBUG = 2;
>
> # Connect to master database
> Apache::DBI->connect_on_init( $dsn, 'db_user',
> 'db_pass' ) or die
> "Cant connect to master database: $DBI::errstr!\n";
> #Apache::DBI->setPingTimeOut( $dsn, 0 );
>
> sub handler
> {
> # Get the Apache request object
> my $r = shift;
>
> # Only handle text
> return -1 if $r->content_type and $r->content_type
> !~ m|^text/|i;
>
> # (re)-Connect to the master database
> $HTML::Mason::Commands::dbh{'master'} =
> DBI->connect($dsn, 'db_user',
> 'db_pass') or die "Cannot connect to master
> database:
> $DBI::errstr!\n";
>
> # Return to the browser
> return $ah->handle_request($r);
> }
>
>
> The application is used by the users to move data
> between is two
> client specific databases.
>
> Here an example:
>
> 1. A user log-in, the credentials are taken from the
> 'master' database
> and the user is authenticated, I use
> Apache::Session::MySQL and
> cookies to store session information (user_id,
> client_id and others…).
> 2. If the user is authenticated, I initiate a
> connection to the
> client-specific databases based on the value of
> client_id. User now
> have access to the three databases, all dbh are
> stored in a global
> %dbh hash.
> 3. User move data between is two table, (clientdb =>
> archivesdb).
>
> This setup is working but it is giving me some
> problems:
>
> Since the Apache::DBI module don't share dbh between
> the apache child
> processes, each user might end up using a total of
> 11 connections! One
> for the master and five for each client specific
> databases (my setup
> use 5 apache child processes, the apache default).
>
> The Apache::DBI recycle the 'master' dbh without
> problem but a new
> connection is initiated to each client specific
> database at every user
> request, thus not recycling it. I have seen a patch
> (http://mathforum.org/epigone/modperl/stroblangnerm)
> for Apache::DBI
> but I am not sure if I should use it.
>
> Here is a sanitized sample of my error_log with
> $Apache::DBI::DEBUG =
> 2 in my handler.pl :
>
> 16629 Apache::DBI new connect to
>
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> 20332 Apache::DBI new connect to
>
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> 29081 Apache::DBI new connect to
>
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> 12403 Apache::DBI new connect to
>
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> 7125 Apache::DBI new connect to
>
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> 24346 Apache::DBI new connect to
>
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> 20332 Apache::DBI already connected to
>
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> 20332 Apache::DBI already connected to
>
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> 20332 Apache::DBI already connected to
>
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> 20332 Apache::DBI new connect to
>
'host=127.0.0.1;dbname=client-client1db_userdb_passAutoCommit=1PrintError=1Username=db_user'
> 20332 Apache::DBI new connect to
>
'host=127.0.0.1;dbname=archives-client1db_userdb_passAutoCommit=1PrintError=1Username=db_user'
> 20332 Apache::DBI already connected to
>
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> 26657 Apache::DBI new connect to
>
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> 20332 Apache::DBI already connected to
>
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> 20332 Apache::DBI already connected to
>
'host=127.0.0.1;dbname=client-client1db_userdb_passAutoCommit=1PrintError=1Username=db_user'
> 20332 Apache::DBI already connected to
>
'host=127.0.0.1;dbname=archives-client1db_userdb_passAutoCommit=1PrintError=1Username=db_user'
> 29081 Apache::DBI already connected to
>
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> 29081 Apache::DBI already connected to
>
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> 29081 Apache::DBI new connect to
>
'host=127.0.0.1;dbname=client-client1db_userdb_passAutoCommit=1PrintError=1Username=db_user'
> 29081 Apache::DBI new connect to
>
'host=127.0.0.1;dbname=archives-client1db_userdb_passAutoCommit=1PrintError=1Username=db_user'
> 20332 Apache::DBI already connected to
>
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> 29081 Apache::DBI already connected to
>
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
> 29081 Apache::DBI already connected to
>
'host=127.0.0.1;dbname=client-client1db_userdb_passAutoCommit=1PrintError=1Username=db_user'
> 29081 Apache::DBI already connected to
>
'host=127.0.0.1;dbname=archives-client1db_userdb_passAutoCommit=1PrintError=1Username=db_user'
> 20332 Apache::DBI already connected to
>
'host=127.0.0.1;dbname=masterdb_userdb_passAutoCommit=1PrintError=1Username=db_user'
>
> So basically I am wondering if I should modify my
> application behaviour.
>
> I know that with DBI I can initiate a connection to
> the server instead
> of a database if I don't specify a tablename in my
> DSN and modify my
> code to use SQL query like the following:
>
> SELECT * FROM database.table …
>
> Instead of specifying a database in my DSN (like I
> do now) and using
> query like this:
>
> SELECT * FROM table …
>
> This approach could also allow me to move data
> between my databases in
> a more 'sexy' way using the new mysql 4.1 'INSERT
> … SELECT' syntax,
> like this:
>
> INSERT INTO archives-clientname.table (*) SELECT *
> FROM client-clientname.table
>
> Since this would require a lot of change to my code,
> I am wondering if
> I am heading in the right direction or if there a
> better solution
> (modyfing my Apache::DBI setup or application
> behaviour). I am also
> wondering if modifying my code to connect directly
> to the server and
> use database.table syntax would cause security
> issues since it would
> require me to use a mysql user who has access to all
> databases.
>
> Any idea, tips, pointers or recommendations will be
> much appreciated
> It is the first multi-databases application I am
> writing and I am
> still learning!
>
> And thanks in advance !
>
> Ps: I apologise for my not-so-perfect English, if
> something is not
> clear or if I need to provide more information's
> about my setup, just
> tell me.
>
