Yii Dynamic DB Connection according to user?

Question

My project is on the basis of multi-tenant.

I have multiple clients (companies) and each client has multiple users.

Each client has their own database, so during user authentication, I discover the name of associated database for that user.

The structure of each database is identical... only the data is different.

So that we can keep the different database for the different company, that will not going to mix in data in database.

The number of clients (and therefor the number of databases) is unknown when the application is written, so it is not possible to include all the connections in the bootstrap script.

Now, what I want to do is, dynamically alter the DB connection that is in the bootstrap or have the ability to dynamically create a new connection for the user signing in. Is there a simple solution for this in Yii and still use AR , query builder ?

The same question was asked on yii forum that still not answered clearly,.... you can find this question here Yii dynamic dabatabase connection

Answer 1

I'd do the same as qiang posted on the forum. You need a list of db connections and a property of the logged in user at Yii::app()->user that tells you which connection to use (I name it connectionId for my example). You then overide getDbConnection() in a ActiveRecord base class:

public function getDbConnection()
{
    if(self::$db!==null)
        return self::$db;
    else
    {
        // list of connections is an array of CDbConnection configurations indexed by connectionId
        $listOfConnections=/* to be loaded somehow */;
        // create DB connection based on your spec here:
        self::$db=new CDbConnection($listOfConnections[Yii::app()->user->connectionId]);
        self::$db->active=true;
        return self::$db;
    }
}

Answer 2

Thanks for your valuable help ,

For my application I need multiple database,

a) master database which contains all the information about company say it as 'projectmaster'

b) database for company sa 'company1'

So,I have implimented code in yii\framework\db\ar\CActiveRecord.php as below,

self::$db=Yii::app()->getDb();
if(self::$db instanceof CDbConnection)
{
    $db=Yii::app()->db;
    $constring=array();
    $constring=$db->createCommand()
        ->select('dbname,host,dbusername,dbpassword')
        ->from('projectmaster')
        ->where('company_name =company1')
        ->queryRow();
    self::$db=new CDbConnection('mysql:host='.$constring['host'].';dbname='.$constring['dbname'],$constring['dbusername'],$constring['dbpassword']);

self::$db->active=true;
return self::$db;

}

Here I am creating the new connection object 'self::$db' using existing connection, is this override the old connection or it creates the new connection

and when I have to use 'createCommand()' for some data fetching I am not using the active record class,

so that time I am connection object as,

$connection=Yii::app()->db; $command=$connection->createCommand($sql);

here I am geting the old connection '$connection' which is created by yii.... But for application it should use new connection so it will get data from 'company1' database.

So I want create connection which should reusable. is it possible to change the yiii connection or we have to cerate new connection every time when we want use custom query.