How to use multiple databases dynamically for one

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 is a folk of the accepted answer. This will prevent the error if the new datasource has already existed:

public function setDatabase($database, $datasource = 'default')
{
    $newDatasource = $datasource . '_' . $database;
    try {
        // return the new datasource if it's already existed
        $db = ConnectionManager::getDataSource($newDatasource);
        $this->useDbConfig  = $newDatasource;
        $this->cacheQueries = false;
        return true;
    } catch (Exception $e) {
        // debug($e->getMessage());
    }

    $db  = ConnectionManager::getDataSource($datasource);
    $db->setConfig(array(
        'name'       => $newDatasource,
        'database'   => $database,
        'persistent' => false
    ));

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

    return false;
}

Answer 2

In your database.php

public $mongo = array(
    'datasource' => 'Mongodb.MongodbSource',
    'database' => 'database_name_mongo',
    'host' => 'localhost',
    'port' => 27017,
);

In your controller you can use

$this->Organisation->setDataSource('mongo');

then apply query like

this->Organisation->find('all');

Answer 3

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.

Answer 4

I don't like the idea of writing down the actual name of the database in the code. For multiple databases, you have the database.php file where you can set as many databases as you need.

If you want to "switch" a database for a specific model on the fly, use the setDataSource method. (see here)

For example, if you have two databases, you can define them in the database.php file as "default" and "sandbox", as an example.

Then, in your code:

$this->Car->setDataSource('sandbox');

The sandbox is the name of the configuration, and the actual name of the database is written only once in the database.php file.

Answer 5

You can always query any databases using full notation in mysql. F.ex.:

SELECT * FROM my_schema_name.table_name;

Cake way:

$db = $this->getDataSource();
$query = array(
    'fields' => array('*'),
    'table' => 'my_schema_name.table_name'
);
$stmt = $db->buildStatement($query, $this);
$result = $db->execute($stmt);