Dynamic Database Connections with Zend

Question

The result I'm trying to achieve involves having an application with a database with minimal tables that store data regarding users on the system, this database would be used to verify user's login details when they login to the application. What I want to be able to do is to dynamically connect to another database once the user has logged in, based on details stored about that user in the main database.

Basically I want each user on the system to have their own database, connect to that database once they login, and be able to use common db model classes (since each user's db will have the same tables as every other user's db).

Is this possible? And if so, how would I implement the dynamic db connection (let's say in the action that validates their login details).

Any help would be greatly appreciated.

Answer 1

Sure its possible. The implementation would really depend on more specific requirements than what you have given but i would probably make some sort of Db_Manager class. that abstracts away all the details to keep the actions short and seet... then you might simply have an action like:

public function loginAction()
{
   $request = $this->getRequest();
   $user = $request->getParam('username');
   $pass = $request->getParam('password');

   $auth = new My_Auth_Adapter($user, $pass);
   $authResult = Zend_Auth::getInstance()->authenticate($auth);

   if($authResult->isValid()){
      My_Db_Manager::connectForUser($authResult->getIdentity());
   }
}

This way you can handle all the actual sorting of which db to use and propagate to your models within the manager class. It would also give you an easy central point to handle working with multiple DB's in one request cycle if that comes up.

Answer 2

The simplest answer is not to make a second connection, but just change the default schema after you determine the name that the given user needs.

For example, fetch the name of the secondary db for a given user:

$db = Zend_Db::factory(...options...);
$secondary_db = $db->query("SELECT secondary_db 
    FROM user_data WHERE user = ?", $userid)
   ->fetchOne();

Then run a query to change the schema. Note the USE statement does not support being a prepared statement, so you have to execute it using the driver API:

$db->getConnection()->query("use $secondary_db");

Now if you reference a table without qualifying its schema, you'll get the table instance in the secondary database. This includes any query you run via the Zend_Db adapter or Table classes, etc.

But the USE statement only works if your secondary databases reside on the same database instance as your primary database with the user details. And also this assumes you don't need to re-authenticate the user against the database because each user has different database-level credentials.