I have been messing with MySQL views - see http://drupal.org/node/291373

This approach is all well and good, but because Drupal is not aware the viewed tables are in a different database entirely, updates must be handled with extreme care. Now here's my idea:

It would _awesome_ if you could take the whole table prefixes model one further. At the moment, in settings.php, I can do something like this:

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

This is great, but _this_ would rock my world!!

   $db_tables = array(
     'default'   => array(
            'server' => 'mysql4',
            'database' => 'site1',
            'prefix' => '',
             ),
     'users'     => array(
            'server' => 'mysql1',
            'database' => 'shared_database',
            'prefix' => 'shared_',
             ),
     'sessions'  => array(
            'server' => 'mysql1',
            'database' => 'shared_database',
            'prefix' => 'shared_',
             ),
     'role'      => array(
            'server' => 'mysql1',
            'database' => 'shared_database',
            'prefix' => 'shared_',
             ),
     'authmap'   => array(
            'server' => 'mysql1',
            'database' => 'shared_database',
            'prefix' => 'shared_',
          ),
     'sequences' => array(
            'server' => 'mysql1',
            'database' => 'shared_database',
            'prefix' => 'shared_',
          ),
   );

I can define a database server, database name and table prefix (if required). This way I can distribute my content across multiple machines and Drupal can still manage updates safely, which would be brilliant and (combined with some of the excellent work going on in caching) would be a large step towards sealing Drupal as an enterprise-level CMS. =)

Comments

greg.harvey’s picture

Component: base system » database system

Mis-categorised. Sorry!

Damien Tournoud’s picture

Status: Active » Closed (won't fix)

At this time, you can use the "database.table" syntax of MySQL to share tables between databases:

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

Making queries between different *database servers* is of course unsupported, so I'm "won't fix"-ing this.

greg.harvey’s picture

Wow, I didn't know that. Will re-read the documentation, but I don't think anyone else knows that either!

Out of curiosity, why "of course"? Why wouldn't Drupal want to support me keeping my user tables on a different machine if I needed to scale out bits of my database for, say, load reasons?

Damien Tournoud’s picture

Well, because you can do queries across database servers (JOIN, etc.).

What you are looking for is called "database partitioning" [1].

[1] http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

greg.harvey’s picture

Thanks! Will read up. Interesting stuff. =)

idcm’s picture

I am interested in what you are saying here but want to be sure I understand correctly. I am an old 4.x user and now am in in 5.x. I have used the method of 'shared_' so that I can have two sites using one database. But what you have here seems to say that I can have two sites each with their own database BUT ... one database has all the tables (thus the settings file remains generic format) and the second site settings file tells it to point to the first database for certain tables. Right? If so, is it correct to say that 'shareddb.' is the name of the first database - the one with all the tables as they normally appear? AND that all this goes in the settings file - just like with the 'shared_' approach?

If I am understanding, this will be very cool and might solve some problems we are having. Please advise. thanks

greg.harvey’s picture

Sounds like you get it. There's a detailed explanation here, about half way down the page: http://drupal.org/node/291373