12

When using multiple databases in Drupal 7, how can I specify that a table is to be created in a different database on a different server?

By default, when you install a module, Drupal assumes that everything in hook_schema() should be installed in the default database. Is there a way to specify that a table should be created on a different database, or is there some kind of manual workaround I can use?

  • My initial thought is that you can't do this at the API level. Reason being that your module would be tied to a specific and rare database configuration. I assume this is a site specific module? I believe your fix will also need to be site/db-configuration specific. – Letharion Mar 5 '12 at 15:01
4

I don't think there is an official API; it doesn't make much sense to do this in general.

That said, all you need to do is give your hook_schema a different name than yourmodule_schema (basically whatever you want, like yourmodule_schema_otherdb) and then in hook_install(), first switch your database, then replicate what drupal_install_schema() does except that you call your custom schema definition function and then switch the database back to the default.

Also, remember to implement hook_uninstall().

No idea why you'd want to do this, though. :)

  • The purpose (in this case) is sharding, but I can think of lots of valid reasons you'd do this. Given how much trouble it would be working around this shortcoming, I think I’ll end up just writing a buch of CREATE TABLE statements for this. – mikl Mar 6 '12 at 19:29
  • For which you will need to write corresponding hook_uninstall() as well if you want to do it properly :) Not sure I see the troubles you're talking about, all you need to do is implement hook_enable (which you had to do in 6.x as well) and copy and adjust the few lines of code in drupal_install_schema(). Especially if you want your tables to show up in multiple databases, you can even use hook_schema() to have it in the default database and in hook_install() do it for the other databases as well. You might even be able to simply call drupal_install_schema($yourmodule) between switching the db. – Berdir Mar 6 '12 at 19:40
11

I have achieved this with the information provided by Berdir. My code looks like:

<?php
function mymodule_schema_otherdb() {
  $schema['mytable'] = array(
    'description' => 'My table description',
    'fields' => array(
      'myfield' => array(
        'description' => 'My field description',
        'type' => 'serial',
        'size' => 'medium',
        'not null' => TRUE,
        'unsigned' => TRUE,
      ),
    ),
    'primary key' => array('myfield'),
  );
  return $schema;
}

function mymodule_install() {
  db_set_active('otherdb');
  $schema = mymodule_schema_otherdb();
  foreach ($schema as $name => $table) {
    db_create_table($name, $table);
  }
  db_set_active();
}

function mymodule_uninstall() {
  db_set_active('otherdb');
  $schema = mymodule_schema_otherdb();
  foreach ($schema as $name => $table) {
    db_drop_table($name);
  }
  db_set_active();
}
  • It wouldn't be too much work to get a module that looks for that hook and fires on install and uninstall. with other db being a key of the schema structure – Jeremy French May 7 '13 at 11:26
  • @JeremyFrench, does this setup require the empty database to be created in advance? I am assuming so. – zkent Jun 10 '15 at 18:48
1

A Drupal 8 version of the *.install file code provided by @Елин Й.:

Note: Database must exist and be specified in settings.php.

<?php
function mymodule_schema_otherdb() {
  $schema['mytable'] = array(
    'description' => 'My table description',
    'fields' => array(
      'myfield' => array(
        'description' => 'My field description',
        'type' => 'serial',
        'size' => 'medium',
        'not null' => TRUE,
        'unsigned' => TRUE,
      ),
    ),
    'primary key' => array('myfield'),
  );
  return $schema;
}

/**
 * Implements hook_install().
 */
function mymodule_install() {
  \Drupal\Core\Database\Database::setActiveConnection('otherdb');
  $connection = \Drupal\Core\Database\Database::getConnection();

  $schema = mymodule_schema_shared();
  foreach ($schema as $name => $table) {
    $connection->schema()->createTable($name, $table);
  }

  \Drupal\Core\Database\Database::setActiveConnection();
}

/**
 * Implements hook_uninstall().
 */
function mymodule_uninstall() {
  \Drupal\Core\Database\Database::setActiveConnection('otherdb');
  $connection = \Drupal\Core\Database\Database::getConnection();

  $schema = mymodule_schema_shared();
  foreach ($schema as $name => $table) {
    $connection->schema()->dropTable($name);
  }

  \Drupal\Core\Database\Database::setActiveConnection();
}

Here's a Gist.

-2

In Settings.php
$db_url=array('default'=>$db_url, 'sec_db'=>$sec_db_url);
In hook_schema db_set_active('sec_db') will now connect to your another DB , not sure if this is recommended or not try it at your own risk. And you can make use of db_prefix to query from this secondary DB. You can make use db prefix in settings.php

  • That would be system wide though, which the OP can already do – Clive Mar 5 '12 at 14:20
  • what would be system wide ? please clarify – GoodSp33d Mar 5 '12 at 14:24
  • 1
    These semantics ($db_url, $db_prefix as a global setting are for Drupal 6. And the dot-notation only works when using a different database on the same MySQL server, not when (as in my case) you're accessing different servers. – mikl Mar 5 '12 at 14:25
  • @kantu OP is asking how to change the database for a specific table. Your original solution (before you edited) would have made the change system-wide, not on a table-by-table basis. – Clive Mar 5 '12 at 14:26
  • 2
    And as I mentioned before, there is no $db_url or a global $db_prefix variable in Drupal 7, and even if there were, it would not solve the problem. – mikl Mar 5 '12 at 14:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.