How to use multiple databases dynamically for one model in CakePHP

Question

Ok, my first question was modified so many times that I chose to delete it and reformulate my question. I've made a small test-case project with different model-names to find a proper solution for my problem.

Warning: Don't mix the databases up with tables

Motivation: I separated the user-data into multiple databases for legal & performance issues.

Currently I'm working on a CakePHP project that has multiple User's and each User has it's own database with multiple tables (cars is one of the tables). Now, I need to explain something first:

Every User has his own database (not a table, a database), so the database names are as follows.

• [DATABASE] app (This is the app's main database)
  • [TABLE] users
  • [TABLE] permissions (Not relevant for this question)
• [DATABASE] app_user1 (User.id 1 owns this entire database)
  • [TABLE] cars (a table entirely owned by User.id 1)
• [DATABASE] app_user2 (User.id 2 owns this entire database)
  • [TABLE] cars (a table entirely owned by User.id 2)
• etc...

I made a small drawing which might clarify the database / table -definitions and their relations to the models:

The problem!!!

I don't know which database to connect to until the User logs in. User's and their databases are created dynamically, so I cant use app/Config/database.php.

So I'm currently writing an extension on the Model and ConnectionManager classes to bypass CakePHP's basic database behaviors. So the Car model knows which database to use. But I just have a feeling this could be done easier!

So I guess it all boils down to one question:

Is there an easier way of doing this?!

Thanks to anyone who will take the time and effort of reading and understanding my problem!

Answer 1

This gentleman (Olivier) had the same problem! (A year ago) He wrote a small adaptation for the Controllers! It's pretty small and it turns out, it works in 1.3 and 2.x.

Anyhow, this is my final solution, that I put in the app/Model/AppModel.php:

class AppModel extends Model
{
  /**
   * Connects to specified database
   *
   * @param String name of different database to connect with.
   * @param String name of existing datasource
   * @return boolean true on success, false on failure
   * @access public
   */
    public function setDatabase($database, $datasource = 'default')
    {
      $nds = $datasource . '_' . $database;      
      $db  = &ConnectionManager::getDataSource($datasource);

      $db->setConfig(array(
        'name'       => $nds,
        'database'   => $database,
        'persistent' => false
      ));

      if ( $ds = ConnectionManager::create($nds, $db->config) ) {
        $this->useDbConfig  = $nds;
        $this->cacheQueries = false;
        return true;
      }

      return false;
    }
}

And here is how I used it in my app/Controller/CarsController.php:

class CarsController extends AppController
{
  public function index()
  {
    $this->Car->setDatabase('cake_sandbox_client3');

    $cars = $this->Car->find('all');

    $this->set('cars', $cars);
  }

}

I'm betting, I'm not the first or last one with this problem. So I really hope this information will find people & the CakePHP community.