Connecting to different databases in a script

Yii 1.1 General Discussion
1

Hello there,

I wondered if someone could offer me some advice.

Each of my clients connects to a different database. Sometimes I need to perform tasks using scripts that run on the CRON and connect to each database one at a time in a loop. Because many of the methods called are also used within the application through the front end I cannot always use name spacing for the queries e.g. SELECT * FROM database.tablename. I therefore need to change the Yii::app()->db component dynamically for example:




foreach($databases as $database){

//Override just the db connectionString (DOES NOT WORK)

Yii::app()->db->connectionString="mysql:host=localhost;dbname=$database";


$sql="SELECT * FROM sometable";

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

$column = $command->queryColumn();

}

The above clearly does not work but it is the kind of functionality I am trying to achieve. I can’t really use:




$connection=new CDbConnection($dsn,$username,$password);

$connection->active=true;

Because when the routines are run via the web front end it needs to read the db component from the main.php config file.

I am currently a bit lost as to how to achieve this properly. Should my script be some sort of console application that would allow me to do this more easily? Is there a way to change the db connectionString and connect to different DBs in a loop?

Any advice would be appreciated.

2

A possible solution (not tested):

Config all your dbconnections in the config/main.php




  array(

    ......

    'components'=>array(

        ......

        'db1'=>array(

            'class'=>'CDbConnection',

            'connectionString'=>'mysql:host=localhost;dbname=testdb1',

            'username'=>'root',

            'password'=>'password',

            'emulatePrepare'=>true,  // needed by some MySQL installations

        ),

        'db2'=>array(

            'class'=>'CDbConnection',

            'connectionString'=>'mysql:host=localhost;dbname=testdb2',

             ...

        ),

      ...

    ),

  ...

)

and then iterate through all application components:





 foreach(Yii::app()->getComponents() as $component)

  if ($component instanceof CDbConnection) 

  {

    ...

     $component->createCommand($sql);

    ...

  }
3

Thanks very much. I had given that kind of approach a thought, but my db instances will not be hard coded in main.php because they are generated from the sub domain. I will be looking up the names of all databases from the database and then iterating through them. Looks like I will somehow have to dynamically create the db array before making the db calls.

4

you may add this in CApplication onBeginRequest handler, or extend CWebApplication and overload init() method… or you can write your own application component “databaseConnectionFactory” which will have method ‘getDbConnectionForCurrentSubdomain’… many ways to solve this

5

Many thanks redguy. One can assume that the I have the database names in an array after collecting them from an ‘admin’ database table. When CRON runs it will look up the databases that I need to loop through. Assuming that I have:




$dbNames=array('db1','db2','db3');

And the host, username, password etc all remain the same. Could you suggest the best method of changing the database I connect to on each iteration of my script. I am not sure which one of the methods you suggest would possibly provide me with this functionality. I just need a light switching on. At the moment I am still in the dark :slight_smile: Many thanks.

PS. I am using DAO not AR for this.There is no getDbConnection() for CDbCommand?

6

I suppose what I should be asking is how would I re-initialize the db component on each iteration? Mmmm seems this is impossible as db is read only.

Seems like Jobio’s way is the only way of actually iterating through the db components. It would seem that I need to workout some way of generating the db components dynamically. My first thought is to use Yii as a 3rd party library to get the array of database names from the admin table and then use that array to build the config file that I then pass to Yii::createWebApplication($config)->run(); in the index.php file. This way I can loop through instances of CDbConnection in my script. Of course I will need some way for index.php to know that it has been called via CRON rather than by apache.

If I am barking up the wrong tree completely then would someone please put me out of my misery.

7

you can create components dynamically:




$connection=new CDbConnection($dsn,$username,$password);

$connection->active=true;

Yii::app()->setComponent( 'new_db_component_id', $connection );


...


Yii::app()->new_db_component_id->createCommand()...

you can put that in onBeginRequest or any other place you must decide which db you want access.

You can keep main ‘db’ connection to your admin database and configure other component to needed database

8

Thanks again redguy. I think I am maybe trying to do the impossible. My code is already written. I.e. I have a bunch of classes that return data etc that all call Yii::app()->db. These methods can be called through the web application. What I want to do now is call the same methods from a script, but for a few different databases. The only possible way to do it (it would seem) is to change the config file connectionString parameter for db before calling the classes. How I go about writing that in a loop I am not sure as this is at the application instantiation level. Maybe I should not be calling Yii::app()->db in my classes and I need to extend CDbConnection to provide more flexible functionality.

In my last app I just called $conObj->makeConnection(‘dbName’]); This was very easy.

Thanks for your help.

9

Thanks again to both of you as you put me in the right direction along with the forum thread here

The pseudo code below has been tested and is a proof of concept. The code is in a controller action, but could be anywhere.





	$databases = array('db2','db3'); //Fetched from somewhere

		

	foreach($databases as $database){

		

		//Create a new component

		$component=Yii::createComponent(array(	

			'class'=>'CDbConnection',

			'connectionString' => 'mysql:host=localhost;dbname='.$database,

			'emulatePrepare' => true,

			'username' => Yii::app()->db->username,

			'password' => Yii::app()->db->password,

			'charset' => 'utf8',

			'enableParamLogging'=>true,

		   'schemaCachingDuration'=>3600,

		));

		//Override the existing db component

		Yii::app()->setComponent('db',$component);


		$sql="SELECT something FROM sometable";

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

		$column = $command->queryColumn();

		var_dump($column);

	}
10

Can I use AR in Your method, instead of createCommand?

11

I found the solution and adding it below, Hope it will help other.

  1. First of all. Create 4 fields in users table in your master database. with name like dbhost, dbusername, dbpwd, and dbname, So that each user will have their separate database.
  2. Create a dump of current database and save that in .sql file and keep it inside your upload/dump folder on root.
  3. Use below code when user is signup in system.

private function createUserDatabase($user_id) {
try{
$userModel = User::findOne($user_id);
if($userModel->dbname != NULL && $userModel->dbname != “”){
return true;
}
$path = Yii::getAlias(’@webroot’).’/uploads/dump’;
$sqlDumpPath = $path.’/userdatabase.sql’;

			$databaseName	= "userdb_".$user_id."_".time();
			$db				= Yii::$app->getDb();
			$dbUserName		= $db->username;
			$dbPassword		= $db->password;
			$dbHost			= $this->getDsnAttribute('host', $db->dsn);		
			$con			= mysqli_connect($dbHost, $dbUserName, $dbPassword);
			 if(! $con ){
				echo 'Connected failure<br>';
				exit;
			 }
			$query			= "CREATE DATABASE `".$databaseName."`";
			if(mysqli_query($con, $query)){	
				
				echo "Database $databaseName created successfully";
			  //$command='mysql -h' .$dbHost .' -u' .$dbUserName .' -p' .$dbPassword .' ' .$databaseName .' < ' .$sqlDump;
			  $command="(D:\wamp\bin\mysql\mysql5.7.21\bin\mysql --user=$dbUserName --password=$dbPassword --host=$dbHost --database $databaseName < $sqlDumpPath) 2>&1";
			    exec($command,$output,$worked);
			  	
			   if($worked==0) {
				  
				  $userModel->db_host =$dbHost;
				  $userModel->db_port ="";
				  $userModel->db_user =$dbUserName;
				  $userModel->db_pwd =$dbPassword;
				  $userModel->dbname =$databaseName;
				  $userModel->save();
				  echo "Information saved successfully";
			   }else{
			   		$dropquery	= "DROP DATABASE `".$databaseName."`";
					mysqli_query($con, $dropquery);
			   }
			   Yii::$app->session->setFlash('success', "Database created successfully");
			}
			else {
				 Yii::$app->session->setFlash('error', "Database creation failed");		  
			}
	}catch(Exception $e){
		 $dropquery			= "DROP DATABASE IF EXISTS `".$databaseName."`";
		 mysqli_query($con, $dropquery);
		 Yii::$app->session->setFlash('error', $e->getMessage());	
	}
	return true;
}
private function getDsnAttribute($name, $dsn)
{
    if (preg_match('/' . $name . '=([^;]*)/', $dsn, $match)) {
        return $match[1];
    } else {
        return null;
    }
}

add these functions in your required controller, May be in siteController. It will create new database for user.
4) Create a new model inside frontend/models with name save UserActiveRecord.php and add below code.

<?php namespace frontend\models; use Yii; use yii\data\ActiveDataProvider; use yii\web\Session; use \DateTime; class UserActiveRecord extends \yii\db\ActiveRecord { public static function getDb() { return Yii::$app->db2; } } ?>

  1. And then in each of your model replace extends \yii\db\ActiveRecord with extends UserActiveRecord.

  2. In index.php add below code.
    $application= (new yii\web\Application($config));

if (!Yii::$app->user->isGuest) :
$userData = Yii::$app->user->identity;
$user_id = $userData->id;
$host = $userData->db_host;
$db_user = $userData->db_user;
$db_pwd = $userData->db_pwd;
$db_name = $userData->dbname;
$dsn = “mysql:host=$host;dbname=$db_name”;
Yii::$app->db2->close();
\Yii::$app->db2->dsn = $dsn;

\Yii::$app->db2->username = $db_user;

\Yii::$app->db2->password = $db_pwd;

\Yii::$app->db2->charset = ‘utf8’;
\Yii::$app->db2->open();

endif;
$application->run();