[drupal-devel] [feature] Allow multiple database connections within a single system

[adrian]

Issue status update for http://drupal.org/node/7461

 Project:      Drupal
 Version:      cvs
 Component:    database system
 Category:     feature requests
 Priority:     normal
 Assigned to:  adrian
 Reported by:  adrian
 Updated by:   adrian
-Status:       active
+Status:       closed

I am closing this. 
Look here for a continuation : http://drupal.org/node/19522


adrian



Previous comments:
------------------------------------------------------------------------

April 29, 2004 - 23:33 : adrian

Attachment: http://drupal.org/files/issues/multi_db_connections.diff (5.23 KB)

This patch allows multiple database connections to be used within
Drupal. 
The method of specifying :

<?php
$db_url = "mysql://user:pass@localhost/dbname";
?>


will still work, it will create the 'default' connection.
However, if you need multiple connections .. you can specify : 

<?php
$db_url["default"]      = "mysql://user:pass@localhost/dbname";
$db_url["other_db"]     =
"mysql://user:pass@localhost/other_dbname";
$db_url["other_server"] = "mysql://user:pass@my.server.com/dbname";
?>


Within your module you can execute

<?php
set_active_db("other_db"); //or another named connection
?>


to switch the db_api to use that connection. If you do not remember to
switch back to default, you might cause interesting errors. so beware.
The set_active_db() function defaults it's first parameter to
'default', so you only need to run : 

<?php
set_active_db(); // defaults to 'default'
?>


to get back on the right track
*Caveats:*

If you specify a named connection that doesnt exist, it creates a
duplicate of the default. I am not sure if this is the wrong behaviour
though, as it is easily fixed , but creating a new pipe is perhaps the
desired functionality? I don't know. please comment.
Because of the nature of the DB Api , it does not allow you to have
both postgres and mysql connections running simultaneously, infact only
the first db api include loaded will function. It is beyond the scope of
this feature request to rewrite the db api as extensively as is needed
to fix this problem. More discussion is needed


------------------------------------------------------------------------

April 30, 2004 - 00:57 : adrian

Attachment: http://drupal.org/files/issues/multi_db_connections_0.diff (6.54 KB)

added some documentation as per Dries' request


------------------------------------------------------------------------

April 30, 2004 - 07:36 : Dries

Committed to HEAD.  Thanks Adrian.


------------------------------------------------------------------------

August 25, 2004 - 09:47 : sandeep

If I was writing a module that needed to connect to another databases...
for example my conf.php might look like this:

$db_url["default"]      = "mysql://user:pass@localhost/dbname";
$db_url["some_pgsql_db"]     =
"pgsql://user:pass@localhost/some_pgsql_db";

Within my module I execute

set_active_db("some_pgsql_db");

Then I would get the Fatal error "Cannot redeclare..." because now
there would be two sets of the db_* functions. One from
database.mysql.inc which Drupal will include by default and another
from database.pear.inc which my call to set_active_db() will include.


------------------------------------------------------------------------

August 25, 2004 - 10:06 : killes at www.drop.org

I think using databases running two different systems is a feature
rather than a bug. A feature that I don't think many people will
require. There is also no easy way of providing this feature I am
afraid.


------------------------------------------------------------------------

August 26, 2004 - 14:40 : sandeep

I know of a friend who uses Drupal to manage his entire business
process. He has written a custom reporting module that collates data
form the Drupal database (MySQL) and his clients database which is in
MSSQL. I do agree that not many people will require this feature but
its a cool feature to have in a web application platform.
I am currently working on a database abstration library based heavily
on the Drupal database abstraction library to achieve the above
feature. I think it should be fairly easy to implement. The idea is to
have a wrapper file that has all the db_* functions. Depending on the
current database connection type we can include the appropriate file
for that particular database type. Now, the db_* function will check
the current database type, for example if it is mysql, then
db_connect() will call db_mysql_connect() which is defined in the mysql
include file. Similarly, the other databases can be supported with the
PEAR db library by defining the db_pear_* functions in the pear include
file.
When I get enough time to complete my library I will contribute a patch
for the Drupal database system.


------------------------------------------------------------------------

September 8, 2004 - 15:55 : adrian

Firstly, this patch made it into core early on in the 4.5 work. 
Secondly, I made a note about not being able to use  postgres and mysql
in the same application. I think there's even a comment in the source.
Thirdly, we are moving away from PEAR in the next release, and mssql is
no longer supported by drupal.
Fourthly, changing the db api to have db_ functions that redirect to
the correct function is probably a good idea, but good luck trying to
convince people it's needed as it is such a specific use case.


------------------------------------------------------------------------

November 21, 2004 - 10:52 : Anonymous

I think the idea of being able to flick databases is a good one, we are
using it within our drupal code :)
It seems to have introduced a problem, raised on a seperate report
http://drupal.org/node/13115
Thanks,
Sil