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 sete 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.
this looks great, actually exactly what i need for my app.
tonight i will implement this and let you know if i have it working or not ;)
hey,
i have tried to implement your extended model, but it does not work…
but i have found another way, which is much easier than alot of people might think.
step 1.
edit app\config\database.php
—————————————————————
var $default = array(‘driver’ => ‘mysql’,
‘connect’ => ‘mysql_connect’,
‘host’ => ‘localhost’,
‘login’ => ‘USER1’,
‘password’ => ‘PASS1’,
‘database’ => ‘DB1’,
‘prefix’ => ‘’);
var $usersettings = array(‘driver’ => ‘mysql’,
‘connect’ => ‘mysql_connect’,
‘host’ => ‘localhost’,
‘login’ => ‘USER2’,
‘password’ => ‘PASS2’,
‘database’ => ‘DB2’,
‘prefix’ => ‘’);
—————————————————————
step 2.
make a controller called app\controller\v1_controller.php
—————————————————————
set(‘user’,$this->V1->find(‘User.username = $USERNAME’), array(‘User.*’, $order=’‘,0));
$this->set(‘usersettings’,$this->UserSetting->find(‘UserSetting.username = $USERNAME” , array(“UserSetting.*”), $order=’‘, 0));
}
}
?>
—————————————————————
step 3.
make another controller called app\controller\user_settings_controller.php
—————————————————————
—————————————————————
step 4.
make the model called app\model\v1.php
—————————————————————
—————————————————————
step 5.
make the model called app\model\user_setting.php
—————————————————————
—————————————————————
step 6.
make the views for v1 in app\views\v1\index.thtml
—————————————————————
that is all folks, now you have the following :
from within 1 controller, you access to another controller, which is accessing another DB! How cool is this?
this should get you guys going …..
thanks for the feedback joren – defining multiple DB connections is definitely the way to go if you have a finite number of databases, and i would do it in a heartbeat if it could solve my particular problem. In the system i’m working on, every customer has their own DB, and i was trying to come up with a way to avoid adding a new entry to the database.php file every time a new customer was added. I think we’re solving different problems here :)
Hey bro,
I couldn’t get your point.
You know db names in every create operation, right?
If this is the situation, it would be quite better, open database.php add for added db and known parameters as db names and close to file :D
When db dropped, you could drop the records from database.php.
Anyway, your way is a way too :)
Hi Kunthar, thanks for the feedback :) The point of this approach is that you can have a single model access different DBs at runtime depending on who the logged-in user is – this isn’t possible by editing database.php. Say I’ve got an online timesheet application (for example) that tracks employee times for 20 different companies, with a separate database for each company. I want my application to use a ‘times’ table to look up the times, but the table is in a different database depending on what company the logged-in user works for. With this technique the ‘times.php’ model can decide which database to look up at runtime. I can’t see any way to achieve this using database.php.
Youre surely right after this explanation. Take cake, bro ;)
Yes you’re on the right solution track with this one. I’m trying to solve a very similar problem atm. I’ve got two databases one is a failover and instead of the session based stuff that you have here I need it to check to see if the main one (off site) is up and if it’s not then fail over to the backup (localhost). I’m pretty sure your solution will solve my problem once I figure out exactly what your doing here.
For those of you who are looking for automatic dB failover putting this code in your models and following the names that you set up in databases.php will provide such.
class Test extends AppModel
{
var $name = ‘Test’;
function __construct()
{
$table = Inflector::tableize($this->name);
$db = ConnectionManager::getInstance();
$connected = $db->getDataSource(‘default’);
if($connected->isConnected() == true)
{
$ds = ‘default’;
}else{
$ds = ‘test’;
}
parent::__construct(false, $table, $ds);
}
}
Ok what i finally came up with works perfect, I didn’t need dynamic models names like you so It didn’t need to be quiet as complex but here was my problem. I’ve got a Master to many Slave topology. The master is in the clients HQ the slaves are in the remote offices. We set them up two applications one is very write intensive as it does a LOT of logging so it needs to write to the master – dB A well call it, the other application is a real-time web gui that can control the other application and being real-time and it reads the info for ALL the branches and HQ it’s very read intensive, we’ll call it dB B. When the network goes down I then need to read and write to yet again another local dB C (a temp dB). So here we go with what sounds to be a very complex problem (i spent days coming up with this) first of all Cake 1.1.19 bug: you have to define default in databases.php and if default goes down your application will break (so you should always run a dB on your application box with a dummy dB to appease Cake). Ok so now to the code in my model I added a method based on model_php5.php
function changeDataSource($newSource)
{
parent::setDataSource($newSource);
}
then in my controller:
$db = ConnectionManager::getInstance();
$connected = $db->getDataSource(‘master’);
$connectionTest = $connected->isConnected();
if($connectionTest == true)
{
$write_dB = ‘master’;
$read_dB = ‘slave’;
}else{
$write_dB = $read_dB = ‘scratch’;
}
// CHANGE TO THE WRITE (MASTER) dB
$this->Test->changeDataSource($write_dB);
$temp = $this->Test->findAll();
echo “This would be the database we are writing to: “;
echo var_dump($temp);
echo “”;
// CHANGE TO THE READ (SLAVE) dB
$this->Test->changeDataSource($read_dB);
$temp = $this->Test->findAll();
echo “This would be the database we are reading from: “;
echo var_dump($temp);
works beautiful is my primary write server goes down everything switches to my scratch dB and while everything is working good I have access to use both my write and read configs on will just by dynamically changing the datasource. I know that would have solved your problem dave as yours needed much more dynamic complexity but mine solves most peoples multi-dB type questions (at least I think)
Thanks Justin!
Hello, this article helps me out a ton. Thanks, I need to do something similar. However I’m having problems accessing $_SESSION from app_model. I created app_model.php in my app root folder and it has similar contents to your mmkmodel, but for some reason it is telling me $_SESSION is an undefined variable. Any ideas? I can access $_SESSION fine from controllers.
I had to make a few changes to to some of the code you posted because I am using CakePHP 1.2RC3. Had to add session_start(); to the bootstrap to be able to access $_SESSION from the model as well, but I finally got it working smoothly. Thank you very much!!