Multiple DBs in CakePHP

Edit : To avoid confusion, cake has quite a nice mechanism for defining multiple DBs already (the database.php file), and if you only want to define a finite set of DBs, this is not the way to do it. This method is only useful if you want to have multiple DBs with the same structure (a database for each customer, for example).

In my case i have 3 different databases - a main Base database, a stats database, and a cms database. The user initially logs in using the user/login information in the main base database, and thereafter each client has their own set of profile-specific and cms databases. The trick is to set the database dynamically at runtime on a per-model basis.

First, I defined 3 constants in app/config/core.php to represent the 3 database levels in my app.

define('DB_BASE_LEVEL_MODEL', 0);
define('DB_PROFILE_LEVEL_MODEL', 1);
define('DB_CMS_LEVEL_MODEL', 2);

Next, add $db_name and $db_level variables to your model base class (I subclassed the AppModel class, but you can change AppModel directly if you want), and over-ride the constructor to set the $db_name variable when the model is instantiated. I also needed to over-ride the Model::setSources function.

class MmkModel extends AppModel {

/**
 * Custom database name.
 *
 * @var string
 * @access public
 */
  var $db_name = null;

/**
 * Define the level of database to connect to (we use 3 different DBs)
 * Options are:
 * DB_BASE_LEVEL_MODEL
 * DB_PROFILE_LEVEL_MODEL
 * DB_CMS_LEVEL_MODEL
 *
 * @var string
 */
  var $db_level = DB_BASE_LEVEL_MODEL;

/**
 * Constructor. Binds the Model's database table to the object.
 * Over-rode to set database on a per-model basis
 *
 * @param integer $id
 * @param string $table Name of database table to use.
 * @param DataSource $ds DataSource connection object.
 */
  function __construct($id = false, $table = null, $ds = null) {
    if (isset($_SESSION['User'])) :
      $this->db_name = null;
      $config = $this->getConnectionInfo();

      if ($this->db_level == DB_PROFILE_LEVEL_MODEL) :
        $this->db_name = $config['database'].'_'.$_SESSION['User']['profile'];

      elseif ($this->db_level == DB_CMS_LEVEL_MODEL) :
        $this->db_name = $config['database'].'_'.$_SESSION['User']['profile'].'_cms';
      endif;
    endif;

    parent::__construct($id, $table, $ds);
  }

/**
 * Sets a custom table for your controller class. Used by your controller to select a database table.
 *
 * @param string $tableName Name of the custom table
 */
  function setSource($tableName) {
    $this->setDataSource($this->useDbConfig);
    $db =& ConnectionManager::getDataSource($this->useDbConfig);
    $db->cacheSources = $this->cacheSources;

    if ($db->isInterfaceSupported('listSources')) {
      $sources = $db->listSources($this);
      if (is_array($sources) && !in_array(low($this->tablePrefix . $tableName), array_map('low', $sources))) {
        return $this->cakeError('missingTable', array(array(
          'className' => $this->name,
          'table' => $this->tablePrefix . $tableName
        )));
      } else {
        $this->table = $tableName;
        $this->tableToModel[$this->table] = $this->name;
        $this->_tableInfo = null;
        $this->loadInfo();
      }
    } else {
      $this->table = $tableName;
      $this->tableToModel[$this->table] = $this->name;
      $this->loadInfo();
    }
  }
}

You will need to set the $db_name in the constructor depending on your needs - in my case the database names are based on the $_SESSION[‘User’][‘profile’]. If you don’t have multiple databases per-client, you could just save time and set the $db_name directly in the model and ignore all the $db_level stuff (i think it might be fairly specific to my case). The only important line in the setSource() function is :

$sources = $db->listSources($this);

This allows us access to this model’s $db_name and $db_level information in the database class.

The next step is to customize the dbo class for your particular database (in my case /cake/libs/model/datasources/dbo/dbo_mysql.php). Copy the appropriate file into your app’s dbo folder (in my case i created app/models/datasources/dbo/dbo_mysql_mmk.php), and slightly alter 2 of the functions. Most of the work is done in the fullTableName() function, but we also need to hack the listSources() function a little :

class DboMysqlMmk extends DboSource {
.
.
.

/**
 * Returns an array of sources (tables) in the database.
 *
 * @return array Array of tablenames in the database
 */
  function listSources($model = null, $enable_cache = true) {
    $cache = parent::listSources();
    if ($cache != null && $enable_cache) {

      // Check that this model's name is in the cache - we might
      // have cached the tables from a different database
      if (!in_array($model->useTable, $cache)) {
        // If not found, merge the table names from *this* database
        $cache = array_merge($cache, $this->listSources($model, false));
        parent::listSources($cache);
      }

      return $cache;
    }

    // Added - list tables for the specific DB
    $db_name = $this->config['database'];
    if (is_object($model) && isset($model->db_name) && !empty($model->db_name)) {
      $db_name = $model->db_name;
    }

    $result = $this->_execute('SHOW TABLES FROM ' . $this->name($db_name) . ';');

    if (!$result) {
      return array();
    } else {
      $tables = array();

      while ($line = mysql_fetch_array($result)) {
        $tables[] = $line[0];
      }
      parent::listSources($tables);
      return $tables;
    }
  }
/**
 * Gets full table name including prefix
 *
 * @param mixed $model
 * @param boolean $quote
 * @return string Full quoted table name
 */
  function fullTableName($model, $quote = true) {
    if (is_object($model)) {
      $table = $model->table;
      if ($model->tablePrefix != null && !empty($model->tablePrefix)) {
        $table = $model->tablePrefix . $table;
      }
      if ($model->db_name != null && !empty($model->db_name)) {
        $table = $model->db_name . '.' . $table;
      }
    } elseif (isset($this->config['prefix'])) {
      $table = $this->config['prefix'] . strval($model);
    } else {
      $table = strval($model);
    }
    if ($quote) {
      return $this->name($table);
    }
    return $table;
  }
.
.
.
}

The fullTableName() change is pretty straight-forward - it just pre-pends the database name to the table name:

$table = $model->db_name . '.' . $table;

The listSources() hack is a little more confusing. The problem i came across was that cake keeps a $cache array listing all the tables in a database ({‘users’, ‘roles’, ‘user_roles’, …} for example). When we try to use a table from a different database, the table name isn’t present in the table list, so cake throws a Missing Database Table error. The solution is to add the tables from all 3 databases to the list. The first time a particular table cannot be found in the $cache, do another listSources() call. If this model is using a different database, this part should load the extra tables into the $cache :

// Check that this model's name is in the cache - we might
// have cached the tables from a different database
if (!in_array($model->useTable, $cache)) {
  // If not found, merge the table names from *this* database
  $cache = array_merge($cache, $this->listSources($model, false));
  parent::listSources($cache);
}

When listing tables, the new listSources() function uses our new $db_name parameter from the model:

// Added - list tables for the specific DB
$db_name = $this->config['database'];
if (is_object($model) && isset($model->db_name) && !empty($model->db_name)) {
  $db_name = $model->db_name;
}

$result = $this->_execute('SHOW TABLES FROM ' . $this->name($db_name) . ';');

It’s quite hacky but it gets the job done. Initially I tried to sub-class dbo_mysql.php and just over-ride those functions, but kept getting Table Not Found errors for some reason. Eventually i just copied the whole file and changed the 2 functions - not ideal but again it gets the job done.

Next (almost there!), update your app/config/database.php to use your new dbo class :

var $default = array('driver' => 'mysql_mmk',
      'connect' => 'mysql_connect',
      'host' => DB_HOST,
      'login' => DB_USER,
      'password' => DB_PASSWORD,
      'database' => CONFIGURATION_DATABASE,
      'prefix' => '');

Now, in each of your models, set the db_level variable to show where the table can be found :

class Profile extends MmkModel {

/**
 * Name of the model.
 *
 * @var string
 */
    var $name = 'Profile';

/**
 * Define the level of database to connect to (we use 3 different DBs)
 *
 * @var string
 */
  var $db_level = DB_PROFILE_LEVEL_MODEL;
}

And you’re done! When a particular model is loaded, the new constructor will automatically set the $db_name variable based on $_SESSION[‘User’][‘profile’] and the $db_level variable. Whenever a query is executed, the database name will be included in the query, allowing us to use multiple databases without disconnecting or reconnecting the actual DB connection. I haven’t confirmed that cross-db hasMany, belongsTo etc are working 100% but i haven’t run into any problems yet. The effect of this hack is to turn a query such as

SELECT `Template`.`name`, `Template`.`status`
FROM `templates` AS `Template`
WHERE `id` = 11

into :

SELECT `Template`.`name`, `Template`.`status`
FROM `base_dev10_cms`.`templates` AS `Template`
WHERE `id` = 11

Disclaimer : I’ve only tried this on MySQL - I have no idea if this query syntax is valid in other databases, so you might get less mileage depending on which database you’re using.

Check out my new project

Follow me on Twitter

Multiple DBs in CakePHP