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

These two modules which sound like they might help (quotes are from their project pages):

• Data:

  Data module helps you model, manage and query related sets of tables. It offers an administration interface and a low level API for manipulating tables and accessing their contents. Data module provides Views integration for displaying table data and Drupal search integration for searching table content.

• Table Wizard:

  The Table Wizard facilitates dealing with database tables:

  • It allows surfacing any table in the Drupal default database through Views 2.
  • Relationships between the tables it manages can be defined, so views combining data in the tables can be constructed.
  • It performs analysis of the tables it manages, reporting on empty fields, data ranges, ranges of string lengths, etc.
  • It provides an API for other modules to views-enable their tables.
  • It provides an API for importing data into tables in the Drupal default database (automatically doing the views integration above).
  • It is bundled with an implementation of this API, for importing comma- and tab-delimited files.

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.