Wednesday, April 15, 2009

Dynamically pick a database in cakephp.

While doing some research into sharding and cakephp I was looking for a way to change a model's database connection.

The docs specify in your models you can use:
var $useDbConfig = 'default';
which will find a matching variable in DATABASE_CONFIG and use the connection settings.

in database.php
class DATABASE_CONFIG {
var $default = array(
'driver' => 'mysql',
'persistent' => false,
'host' => 'localhost',
'port' => '',
'login' => 'myuser',
'password' => 'yeahright',
'database' => 'mycake',
'schema' => '',
'prefix' => '',
'encoding' => ''
);
...
}

Note: Difference between Sharding and Partitioning is shards resides on different servers. However both separate data depending on some field or attribute.

So to make this dynamic you start in bootstrap.php and create a hashing alg like this to separate your data:
$shardId = $id % 10;
$shardHostArray = array(5 => '192.168.0.25');

Configure::write('shard.host', $shardHostArray[5]);
or a
define('SHARDHOST', $shardHostArray[5]);
The next change you make is in database.php.

Add a constructor to class DATABASE_CONFIG
 public function __construct()
{
$this->shard = $this->default;
$this->shard['host'] = SHARDHOST;
}
Now when you have a model in cakephp such as GroupModel that you want to shard. You specify:
var $useDbConfig = 'shard'; // this is the name of a class member in
// DATABASE_CONFIG. I created this var in
// the above constructor.
And the correct server address will be used. This allows you to stay within the cakephp conventions and not break the schema caching.

1 comment:

blacklizard said...

can you explain more about the selector hash.. Thanks