Sunday, December 29, 2013

Yii Framework: Using multiple db connections in a controller method

I have a mixed database where the majority of the tables are UTF-8 encoding but there are a few tables that are required to store the data in a specific encoding that this not UTF-8. 

In Yii you are defining you global database connection in the protected/config/main.php file, and this is global for the entire web application.This is causing some challenges for me since it is very important that I deliver data bitwise compatible according to the data stored in the table. 

I was not able to find any solution to this in my first few Google attempts, so I had to dive into the code.

First I tried in the controller action method by changing the charset on the global CDbConnection directly using 

Yii::app()->db->charset='latin1';

this did not work since the database connection is already initialized at this point.

Going through the Yii Framework source code I first found CApplication->getDB(), then searching for the function getComponent() lead me to CModule->getComponent().

My next though was "If there is a getComponent(), I wonder if there is not a setComponent() function as well?" - and behold CModule->setComponent().

Armed with this knowledge it was easy to change the connection temporarily to use a new initialized database connection with a different charset.

public function loadModel($id)
{
    // Save the original database connection for later reuse
    $originalDbConnection = Yii::app()->db;
    
    // Create a new database connection based on the original database connection
    // change the charset to latin1
    $latin1DbConnection = Yii::createComponent(array(      
        'class'=>'CDbConnection',
        'connectionString' => Yii::app()->db->connectionString,
        'emulatePrepare' => Yii::app()->db->emulatePrepare,
        'username' => Yii::app()->db->username,
        'password' => Yii::app()->db->password,
        'charset' => 'latin1',
    ));

    // Set the application wide database connection for this Apache/PHP webrequest to use this special database connection
    Yii::app()->setComponent("db",$latin1DbConnection);
    
    // Do the queries against the database
    $someModel=SomeModel::model();
    $model = $someModel->findByPk($id);
    
    // Restore the original database connection 
    // note: 
    // This is actually not needed in this simple case, due to the fact that Apache/PHP rebuilds the entire web application on each request
    // Since I am not doing additional request in this logic here after returning the result everything is torn down and discarded.
    // However I have added it as part of this code for completeness and also in case someone else out there is doing more complex logic with multiple 
    // request that needs different charsets
    Yii::app()->setComponent("db",$originalDbConnection);

    if($model===null)
        throw new CHttpException(404,'The requested page does not exist.');
    return $model;
}