Drupal shared tables, but from another database

Question

I have a Drupal Multisite set up with hundreds of sites. I want to make some tables shared (like banners, and roles) so I don't have to update hundreds of sites when changing a banner (for example).

I know this can be done using these strings in settings.php:

$db_url = 'mysql://user:pwd@localhost/example_db';

$db_prefix = array(
     'default'   => '',
     'users'     => 'subsite2_',
     'sessions'  => 'subsite2_',
     'authmap'   => 'subsite2_',
);

But ... what if I have a multiple database setup as well? I have one database that holds all shared tables .. So in that database, I have the Banners table - that I want to have all the other sites to use.

The reason for a multiple database setup is because the whole multisite setup consists of hundreds of sites (and will be thousands by the end of this year) .. so every database only holds about 20-40 sites.. I suspect a lot of these tables can be shared..

hope someone can help. thanks!

Marco

Answer 1

There is a walkthrough to setup something like this. It aims at sharing only the user data, but the principle can be extended to more or less all tables. Note that it uses a 'trick' on the table prefixing logic by prepending not only (or not at all) a table name prefix, but also/only a database selector using the dot notation, e.g. someDatabase.someTable. So I'm not sure how this would translate to a PostgreSQL backend. Also, it means that you're still restricted to a single database server, as there is (AFAIK) no mechanism to use two different database connections in a single Drupal instance.

As an alternative, you might try to find a solution from the database engine side of things by 'mapping' your shared tables into the different database instances via some kind of replication/mirroring feature. That way you could use the standard Drupal multisite setup, doing the sharing of tables 'behind the back' of Drupal. Not sure how/if this would work and what consequences this would have on locking and other concurrency issues, though.

Answer 2

the lullabots recently talked about these two modules which sound like they might help:

http://drupal.org/project/data
http://drupal.org/project/tw

Answer 3

If you are using mysql 5+ mysql views is a good way of sharing data across multiple sites in drupal. Not only it allows to have shared content but individual sites can have there own content. devbee tutorial on mysql views in drupal. contains the detailed tutorial on how to implement it using taxomony.