symfony2 + doctrine2 @ postgresql设置一个模式 [英] symfony2 + doctrine2@postgresql setting a schema

问题描述

我想使用symfony2 + doctrine2作为新项目。我遇到了一个postgresql方案的一个小问题。与mysql相反，您可以在postgres（如其他数据库）中指定不同的方案。我们的产品数据库有大约200个方案。

我必须为当前的教义连接设置一个模式。我如何做到这一点？

我在几个月前在另一个项目中解决了这个问题，仅使用了doctrine2。我做了以下：

  $ em = Doctrine\ORM\EntityManager :: create（$ connectionOptions，$ config）; 
 $ em-> getConnection（） - > exec（'SET SEARCH_PATH TO foobar'）; 
  

但是我不知道在symfony2中应该怎么做？

解决方案

您可以尝试实现和使用您自己的driver_class，并将PDO DriverOptions中的search_path传递，例如在您的symfony配置中：

 ＃Doctrine配置
 doctrine：
 dbal：
驱动程序： pdo_pgsql 
 driver_class：YourNamespace\YourBundle\Doctrine\DBAL\Driver\PDOPgSql\Driver 
选项：
 search_path：YOUR_SEARCH_PATH 
  

驱动程序可能看起来像这样：

 命名空间YourNamespace\YourBundle\Doctrine\DBAL\Driver\PDOPgSql; 
 
请使用Doctrine\DBAL\Platforms; 
 
 class Driver extends \Doctrine\DBAL\Driver\PDOPgSql\Driver implements \Doctrine\DBAL\Driver 
 {
 public function connect（array $ params，$ username = null，$ password = null，array $ driverOptions = array（））
 {
 //添加一些错误处理当SEARCH_PATH丢失... 
 $ searchPath = $ driverOptions ['search_path']; 
 unset（$ driverOptions ['search_path']）; 
 
 $ connection = new \Doctrine\DBAL\Driver\PDOConnection（
 $ this-> _constructPdoDsn（$ params），
 $ username，
 $ password，
 $ driverOptions 
）; 
 
 $ connection-> exec（SET SEARCH_PATH TO {$ searchPath};）; 
 
 return $ connection; 
} 
 
 / ** 
 *构造Postgres PDO DSN。 
 * 
 * @return string DSN。 
 * / 
 protected function _constructPdoDsn（array $ params）
 {
 $ dsn ='pgsql：'; 
 if（isset（$ params ['host']）&& $ params ['host']！=''）{
 $ dsn。='host ='。 $ params ['host']。  
} 
 if（isset（$ params ['port']）&& $ params ['port']！=''）{
 $ dsn。='port = 。 $ params ['port']。  
} 
 if（isset（$ params ['dbname']））{
 $ dsn。='dbname ='。 $ params ['dbname']。  
} 
 
 return $ dsn; 
} 
} 
  

您需要_constructPdoDsn方法，因为它没有定义在\Doctrine\DBAL\Driver\PDOPgSql\Driver中受保护。这是有点笨蛋，因为我们使用PDO DriverOptions，我不知道这是否是一个很好的方法 - 但似乎有效。

希望这有帮助。 p>

最好的问候，

Patryk

I want to use symfony2+doctrine2 for a new project. I ran into a little issue with postgresql-schemes. In contrast to mysql you can specify in postgres (like other databases) different schemes. Our productiv database has around 200 schemes for example.

I have to set a schema for my current doctrine connection. How can I do that?

I solved this issue a few months ago in another project, that uses doctrine2 only. I did the following:

$em = Doctrine\ORM\EntityManager::create($connectionOptions, $config);
$em->getConnection()->exec('SET SEARCH_PATH TO foobar');

But I dont know where I should do that in symfony2?

解决方案

you could try to implement and use your own driver_class and pass the search_path in the PDO DriverOptions, e.g. in your symfony config:

# Doctrine Configuration
doctrine:
    dbal:
        driver:   pdo_pgsql
        driver_class: YourNamespace\YourBundle\Doctrine\DBAL\Driver\PDOPgSql\Driver
        options:
            search_path: YOUR_SEARCH_PATH

The driver could look something like this:

namespace YourNamespace\YourBundle\Doctrine\DBAL\Driver\PDOPgSql;

use Doctrine\DBAL\Platforms;

class Driver extends \Doctrine\DBAL\Driver\PDOPgSql\Driver implements \Doctrine\DBAL\Driver
{
    public function connect(array $params, $username = null, $password = null, array $driverOptions = array())
    {
        // ADD SOME ERROR HANDLING WHEN THE SEARCH_PATH IS MISSING...
        $searchPath = $driverOptions['search_path'];
        unset($driverOptions['search_path']);

        $connection = new \Doctrine\DBAL\Driver\PDOConnection(
            $this->_constructPdoDsn($params),
            $username,
            $password,
            $driverOptions
        );

        $connection->exec("SET SEARCH_PATH TO {$searchPath};");

        return $connection;
    }

    /**
     * Constructs the Postgres PDO DSN.
     *
     * @return string The DSN.
     */
    protected function _constructPdoDsn(array $params)
    {
        $dsn = 'pgsql:';
        if (isset($params['host']) && $params['host'] != '') {
            $dsn .= 'host=' . $params['host'] . ' ';
        }
        if (isset($params['port']) && $params['port'] != '') {
            $dsn .= 'port=' . $params['port'] . ' ';
        }
        if (isset($params['dbname'])) {
            $dsn .= 'dbname=' . $params['dbname'] . ' ';
        }

        return $dsn;
    }
}

You need the _constructPdoDsn method because it isn't defined as protected in \Doctrine\DBAL\Driver\PDOPgSql\Driver. It's bit "hacky" because we are using PDO DriverOptions and i'm not sure if that's a good way - but it seems to work.

Hope this helps.

Best regards,

Patryk

这篇关于symfony2 + doctrine2 @ postgresql设置一个模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！