php CakePHP中如何为一个模型动态使用多个数据库

How to use multiple databases dynamically for one model in CakePHP

提问by Ariaan

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 databasesup with tables

警告：不要将数据库与表混淆

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

动机：为了法律和性能问题，我将用户数据分成多个数据库。

Currently I'm working on a CakePHPproject that has multiple User's and each Userhas it's own databasewith multiple tables(carsis one of the tables). Now, I need to explain something first:

目前我正在开发一个CakePHP项目，该项目有多个User's ，每个项目User都有自己的数据库，其中包含多个表（cars是其中一个表）。现在，我需要先解释一下：

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

每个人User都有自己的数据库（不是表，是数据库），所以数据库名称如下。

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

• [DATABASE] app（这是应用程序的主数据库）
  • [桌子] users
  • [表格] permissions（与此问题无关）
• [DATABASE] app_user1（User.id1 拥有整个数据库）
  • [TABLE] cars（完全由1拥有的表User.id）
• [DATABASE] app_user2（User.id2 拥有整个数据库）
  • [TABLE] cars（完全由2拥有的表User.id）
• 等等...

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 databaseto connect to until the Userlogs in. User's and their databasesare created dynamically, so I cant use app/Config/database.php.

在登录之前，我不知道要连接到哪个数据库User。User's 及其数据库是动态创建的，因此我无法使用app/Config/database.php.

So I'm currently writing an extension on the Modeland ConnectionManagerclasses to bypass CakePHP's basic database behaviors. So the Carmodel knows which databaseto use. But I just have a feeling this could be done easier!

因此，我目前正在编写Model和ConnectionManager类的扩展，以绕过 CakePHP 的基本数据库行为。所以Car模型知道使用哪个数据库。但我只是觉得这可以更容易！

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!

感谢任何愿意花时间和精力阅读和理解我的问题的人！

回答by Ariaan

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

这位先生（奥利维尔）也有同样的问题！（一年前）他为Controllers写了一个小改编！它非常小，事实证明，它适用于1.3和2.x。

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

无论如何，这是我的最终解决方案，我放入了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:

这是我如何在我的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.

我敢打赌，我不是第一个或最后一个遇到这个问题的人。所以我真的希望这些信息能找到人们和 CakePHP 社区。

回答by noamicko

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.

我不喜欢在代码中写下数据库实际名称的想法。对于多个数据库，您可以在 database.php 文件中根据需要设置任意数量的数据库。

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

如果你想“开关”特定模型的数据库在运行，使用的setDataSource方法。（见这里）

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

例如，如果您有两个数据库，您可以在database.php 文件中将它们定义为“default”和“sandbox”，作为示例。

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.

沙箱是配置的名称，数据库的实际名称只在database.php文件中写入一次。

回答by bancer

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

您始终可以在 mysql 中使用完整表示法查询任何数据库。例如：

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);

回答by teju c

In your database.php

在你的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');

回答by trank

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;
}