Combining databases in Drupal

Question

So here's the scenario:

I am building a series of event websites that need to be separate Drupal-6 installations on the same server. Each uses the same architecture but different URLs and themes.

What I'd like the sites to be able to share is content in 2 content types, teachers and sponsors. So that there would be one database with all the teachers and sponsors that each individual event site could then pull from by means of nodequeues.

So that new teacher nodes and sponsor nodes can be created and edited from within any of the Drupal installations.

It would also be convenient to share a user table as well, but not absolutely necessary.

Any ideas?

Answer 1

Does this help you?

The comments in settings.txt explain how to use $db_prefix to share tables:

 * To provide prefixes for specific tables, set $db_prefix as an array.
 * The array's keys are the table names and the values are the prefixes.
 * The 'default' element holds the prefix for any tables not specified
 * elsewhere in the array. Example:
 *
 *   $db_prefix = array(
 *     'default'   => 'main_',
 *     'users'     => 'shared_',
 *     'sessions'  => 'shared_',
 *     'role'      => 'shared_',
 *     'authmap'   => 'shared_',
 *   );

---

Another way, although I don't know if that would work, is to create one database per Drupal installation and create in each database a VIEW eg. install1_users, install2_users that refers to one shared table shared_users in a shared database. A carefully constructed view should be updatable just like a normal table.

Answer 2

Sounds like a job for the Domain Access project, a suite of modules that provide tools for running a group of affiliated sites from one Drupal installation and a single shared database. The module allows you to share users, content, and configurations across a number of sites. By default, these sites share all tables in your Drupal installation but the Domain Prefix module allows for selective, dynamic table prefixing for advanced users.

IMHO this is much better than rolling a custom solution which poses considerable complexity and risk of not being able to upgrade your site. See Share tables across instances (not recommended).

Answer 3

There are a couple of ways of doing this.

If it's okay to share a single database, you can use prefixing as follows:

$db_prefix = array(
  'default'   => 'main_',
  'users'     => 'shared_',
  'sessions'  => 'shared_',
  'role'      => 'shared_',
  'authmap'   => 'shared_',
);

However, keep in mind that there is a hard limit to the number of tables that a MySQL database can hold. According to this thread, it's 1792. If you suspect that you will reach this limit, you can use this hack/bug.

$db_prefix = array(
  'default'   => '',
  'users'     => 'maindb.',
  'sessions'  => 'maindb.',
  'role'      => 'maindb.',
  'authmap'   => 'maindb.',
);

where maindb is another shared database that contains the data that you need. This is not best practice, but it works in Drupal 6 (haven't tested in D7).