Tags

,

Earlier after working on a scenario where multiple Drupal interfaces were running on a single database, I was wondering, what if a single Drupal interface uses multiple databases?

In answering that, I did some investigation which I had composed in this blog.

i. Firstly we create a database named as “multidb1”.

ii. We use this database as our default database during our Drupal installation.

Selection_004

iii. We proceed with the furthur installation steps.

iv. Next we open the settings.php file of our current Drupal install and we navigate to the database configuration section.

$databases = array (
  'default' => 
  array (
    'default' => 
    array (
      'database' => 'multidb1',
      'username' => 'root',
      'password' => '<password>',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
);

Here we find that the database key for the default database is “default”.

v. We create another database named as “multidb2”.

vi. Next, we connect the database named as “multidb2” to our current Drupal install.

$databases = array (
  'default' =>  // main drupal db starts from here
  array (
    'default' =>
    array (
      'database' => 'multidb1', // main drupal db name
      'username' => 'root', // main drupal db username
      'password' => '<password>', // main drupal db password
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),'anotherdb' =>  // additional database starts here
  array (
    'default' =>
    array (
      'database' => 'multidb2', // additional database name
      'username' => 'root', // additional database username
      'password' => '<password>', // additional database password
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    )
  ),
);

Here we had added “multidb2” with the database key as “anotherdb”.

After this step we had successfully connected an additional database to our current Drupal install.

Using multiple databases in our custom module:

i. Firstly we create the “.info” file for our custom module.

name = testmodule
description = "Test the multi DB connectivity"
package = custom
core = 7.x

We had given “testmodule” as the name to our custom module.

ii. Next, we need to create the install file. In our install file we define the schema of the tables to be used by our custom module. If we want the tables for this module to be created into another database other than the default database then all we need is to give our “hook_schema” a different name than “_schema” (like _schema_otherdb).

The module that we are creating is named as “testmodule”. This module will use a table named as “testtable”, whose schema we will define in “testmodule.install” file.

We will install this table in “multidb2” instead of the default database “multidb1”. In order to install this table to “multidb2” we need to give our “hook_schema” a different name than “testmodule_schema”. Here we name “hook_schema” as “testmodule_schema_anotherdata”.

/**
 * Implements hook_schema().
 *
 */
function testmodule_schema_anotherdata() {
  $schema['testtable'] = array(
    'description' => 'TODO: please describe this table!',
    'fields' => array(
      'uid' => array(
        'description' => 'TODO: please describe this field!',
        'type' => 'int',
        'size' => 'big',
        'not null' => TRUE,
      ),
      'email_id' => array(
        'description' => 'TODO: please describe this field!',
        'type' => 'varchar',
        'length' => '50',
        'not null' => TRUE,
      ),
      'days_left' => array(
        'description' => 'TODO: please describe this field!',
        'type' => 'int',
        'size' => 'big',
        'not null' => TRUE,
      ),
      'flag' => array(
        'description' => 'TODO: please describe this field!',
        'type' => 'int',
        'not null' => TRUE,
        'default' => 0,
      ),
    ),
  );
  return $schema;
}

iii. Next, in “hook_install()”, first we switch our database, then we replicate what “drupal_install_schema()” does except
that we call our custom schema definition function and then we need to switch the database back to the default.

In order to switch database we use the “db_set_active($key = ‘default’)” function.

This function accepts only one argument which is the “database key”.

If we do not pass any arguement to this function then it will set the default database as active.

Here we will define “hook_install”. Since our module name is “testmodule” so we have our install hook as “testmodule_install()”.

function testmodule_install() {
  db_set_active("anotherdb");
  $schema = testmodule_schema_anotherdata();
  foreach ($schema as $name => $table) {
    db_create_table($name, $table);
  }
  db_set_active();
}

Previously, we had added an additional database “multidb2” with the database key as “anotherdb” in our settings.php file.

Now we switch the database to point to “multidb2” and then we install the schema defined in “testmodule_schema_anotherdata” into “multidb2”, when we enable our installed module.

Next, we switch back to our default database because if we don’t do that then Drupal will not be able to access its own data later on.

After this step we are able to perform operations on the additional database.

iv. In “hook_uninstall()”, first we switch our database, then we replicate what “drupal_uninstall_schema()” does except that we call our custom schema definition function and then we need to switch the database back to the default.

Here we will define “hook_uninstall”. Since our module name is “testmodule”, so we have our install hook as “testmodule_uninstall()”.

function testmodule_uninstall() {
  db_set_active("anotherdb");
  $schema = testmodule_schema_anotherdb();
  foreach ($schema as $name => $table) {
    db_drop_table($name);
  }
  db_set_active();
}

Previously, we had added an additional database “multidb2” with the database key as “anotherdb” in our settings.php file.

Now, we switch the database to point to “multidb2” and then the tables installed into “multidb2” will be removed, during the module uninstall operation.

Next, we switch back to our default database because if we don’t do that then Drupal will not be able to access its own data later on.

v. We could perform operations on the additional database by switching the database.

In our module:

<?php

  // Use the database we set up earlier
  db_set_active('anotherdb');
  
  // Run some queries, process some data
  // ...
  // Go back to the default database,
  // otherwise Drupal will not be able to access its own data later on.
  db_set_active();
Advertisements