Dynamic Multiple Databases

My project is on the basis of multi-tenent SaaS.

I have multiple clients (companies) and each client has multiple users - they all will use the same database layout.

Each client has their own database, so during user authentication, I want to Build a master database that associates the user with a company 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 ?

I saw this solution but not working for me http://www.yiiframework.com/forum/index.php?/topic/5385-dynamic-db-connection/

This is how my config file looks today for the script running one database, i want to call a master database that controls which database the user belongs to and the app uses in Yii, any idea?

<?php

// Configure for production.


&#036;language         = 'en'; 


&#036;currencyBaseCode = 'USD';


&#036;theme            = 'default';


&#036;connectionString = 'mysql:host=localhost;port=3306;dbname=master';


&#036;username         = 'root';


&#036;password         = 'YOUR PASS';


&#036;memcacheServers  = array( // An empty array means memcache is not used.


                        array(


                            'host'   =&gt; '127.0.0.1',


                            'port'   =&gt; 11211,  // This is the default memcached port.


                            'weight' =&gt; 100,


                        ),


                    );


&#036;adminEmail       = 'EMAIL ADDRESS';


&#036;installed = true; // Set to true by the installation process.


&#036;maintenanceMode  = false; // Set to true during upgrade process or other maintenance tasks.


&#036;instanceConfig   = array(); //Set any parameters you want to have merged into configuration array.


                             //@see CustomManagement


&#036;instanceConfig['components']['request']['hostInfo']         = 'website url';


&#036;instanceConfig['components']['request']['scriptUrl']         = '/app/index.php';





&#036;urlManager = array (); // Set any parameters you want to customize url manager.

Dear Friend

I have no idea about Saas.

I am not a professional in web development.

I once succeeded in switching between databases depending on the values stored in sessionin my localhost.

I hope we can apply that in your scenario.

If user has right to have separate database, when he gets logged in we are storing his id as database name.

SiteController.php




public function actionLogin()

        {       

            $model=new LoginForm;

            if(isset($_POST['LoginForm']))

            {

                $model->attributes=$_POST['LoginForm'];


                if($model->validate() && $model->login())  {


                        $user=User::model()->findByAttributes(array('username'=>Yii::app()->user->id));


                        if($user->hasOwnDatabase)

                             Yii::app()->user->setState('database',Yii::app()->user->id);


                        $this->redirect(Yii::app()->user->returnUrl);  }

            }

                

                $this->render('login',array('model'=>$model));

        }



[b]Create an application behavior and attach it to main application.

[/b]

applicationBehavior.php in components directory




<?php

class applicationBehavior extends CBehavior

{      

    private $_owner;

    public function events() {


                        return  array(

                                'onBeginRequest'=>'assignDb',        

                        );

                             }

        

    public function assignDb()

    {

        $owner=$this->_owner=$this->getOwner();

                

        if(!$owner->user->getIsGuest && $owner->user->hasState('database'))

        {

            $database=Yii::app()->user->getState('database');                

            $owner->db->setActive(FALSE);   


$owner->setComponent('db',new CDbConnection("mysql:host=localhost;dbname=$database",'root','yourpassword'));

                                                   

            $owner->db->setActive(TRUE);

                                

        }

    }

        

}

?>



Attach this behavior instance as a property to main configuration file. Leave the ‘db’ component intact.




return array(

......................................

.......................................

'behaviors'=>array(

'class'=>'application.components.applicationBehavior',

),

......................................

......................................

'components'=>array(

.........................................

........................................

'db'=>array(

                        'connectionString' => 'mysql:host=localhost;dbname=routine',//routine-default database

                        'emulatePrepare' => true,

                        'username' => 'root',

                        'password' => 'password',

                        'charset' => 'utf8',

                ),

.....................................................

....................................................

),

)



So when user is created, if he has right to have separate database it is stored as a boolean. Then we create a separate database on his name. After successful registration, when he login, he will use his own database on the next immediate request. Otherwise he will work on default database.

I hope this helps you a bit.

Regards.

It didn’t seem to work for me, it is like when i call the “Master” database it doesn’t know what to do the master database is only forwarding to the users assigned database… so only have Username, Email, Password and Databasename that it calls in it… any idea? and table name in database is “companylist”

Dear Friend

sorry about that.

The following is my inference.

Every user belongs to a company.

Every company has a separate database.

every user belonging to a particular company will use the database of that company.

Now we have to define relationship between user and company.

every user will have a comany_id.




public function actionLogin()

        {       

            $model=new LoginForm;

            if(isset($_POST['LoginForm']))

            {

                $model->attributes=$_POST['LoginForm'];


                if($model->validate() && $model->login())  {


                        $user=User::model()->findByAttributes(array('username'=>Yii::app()->user->id));


                        if($user->company->hasOwnDatabase)

                             Yii::app()->user->setState('database',Yii::app()->user->company->id); //This way we are grouping users to use database of the company they belong.


                        $this->redirect(Yii::app()->user->returnUrl);  }

            }

                

                $this->render('login',array('model'=>$model));

        }



Tried no succes… do i need to modify what you send me?

Dear Friend

The code below is very primitive indeed.




public function actionLogin()

        {       

            $model=new LoginForm;

            if(isset($_POST['LoginForm']))

            {

                $model->attributes=$_POST['LoginForm'];


                if($model->validate() && $model->login())  {


                        $user=User::model()->findByAttributes(array('username'=>Yii::app()->user->id));


                        if($user->company->hasOwnDatabase)

                             Yii::app()->user->setState('database',Yii::app()->user->company->id); //This way we are grouping users to use database of the company they belong.


                        $this->redirect(Yii::app()->user->returnUrl);  }

            }

                

                $this->render('login',array('model'=>$model));

        }



Try to infuse your own logic indeed.

The above code assumes the following.

1.company HAS_MANY relation with user..table has a boolean field(hasOwnDatabase).

which grants permission to have a separate database.

2.We also have to ensure that a database exists in the comany name or id beforehand.

regards.

Another solution: connect to a different db depending on the subdomain:

http://www.yiiframework.com/wiki/78/multiple-databases-and-multiple-domains

http://www.yiiframework.com/wiki/200/sub-domains-with-different-databases-in-yii/

http://www.yiiframework.com/forum/index.php/topic/35739-working-with-dynamic-subdomain/

http://www.yiiframework.com/wiki/123/multiple-database-support-in-yii/

https://www.google.de/search?q=yii+multi+tenant

Actually I choose the config file depending on the domain. I will see the last post to check if I can find a better solution.