I like to distribute my read and write db edits on two different db connections. What should be the right approach to set this up?
The adhoc system I am looking to build right now is to do a createCommand everytime I need to redirect the query to the read-only database. I am looking to have a more abstract method somewhere inside the dbconnection such that it automatically sends the select queries to the read-only db and the update and insert to the write-enabled db.
Hi, you could try something like this:
In your config file
return array(
...
'behaviors' => array(
// This behaviour will contain a function that is executed for
// each request.
'class' => 'application.components.applicationBehavior',
),
'components'=>array(
/* Set default Database connection to the db that can only be read */
'db'=>array(
'connectionString' => 'mysql:host=localhost;dbname=iccapmain',
'emulatePrepare' => true,
'username' => 'myUsername',
'password' => 'myPassword',
'charset' => 'utf8',
),
/* Create a second CDbConnection and make its connection identical to
that of the default db.
The request will be inspected in applicationBehavior and if data
needs to be written, this CDbConnection's details will be changed
to that of the write db */
'dbWrite' => array(
'connectionString' => 'mysql:host=localhost;dbname=iccapmain',
'emulatePrepare' => true,
'username' => 'myUsername',
'password' => 'myPassword',
'charset' => 'utf8',
'class' => 'CDbConnection',
),
...
),
...
);
Here is the behavior:
class applicationBehavior extends CBehavior
{
public function events(){
return array(
'onBeginRequest'=>'assignDb',
);
}
public function assignDb()
{
$owner=$this->getOwner();
if(!strstr($_SERVER['REQUEST_URI'],"captcha")) /*ignore requests involving the captcha*/
{
if(...inspect the request to determine if data should be written ...))
{
$dsn = 'mysql:host=localhost;dbname=dbwriterecords';
$username = 'myWriteUsername';
$password = 'myWritePassword';
$owner->dbWrite->setActive(FALSE);
$component=Yii::createComponent(array(
'class'=>'CDbConnection',
'tablePrefix' => 'cap_',
'connectionString' => $dsn,
'emulatePrepare' => true,
'username' => $username,
'password' => $password,
'charset' => 'utf8',
'enableParamLogging'=>true,
));
$owner->setComponent('dbWrite',$component);
$owner->dbWrite->setActive(TRUE);
}
}
}
}
In the behavior, you can also set flags to know what models to use.
Then do what is done in this wiki to create models that use the different DBs.
I’m not 100% sure what you required in your question. The code above is for using separate databases. If you only want to use different connection strings to one DB, then do not change the db name in the connection string in the assignDb() function.
Are you sure is the best way?
There might be better ways. Have a look at other wiki’s on this topic.