Symfony2 + doctrine2 @postgresql setting the schema

Question

I want to use symfony2 + doctrine2 for a new project. I have a little problem with postgresql schemas. Unlike mysql, you can specify different schemes in postgres (like other databases). For example, our productiv database has about 200 schemas.

I need to set up a scheme for my current doctrine mix. How can i do this?

I solved this problem a few months ago in another project that uses only doctrine2. I have done the following:

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

But I do not know where I should do this in symfony2?

Answer 1

you can try to implement and use your own class driver and pass the search path to PDO DriverOptions, for example. in symfony config:

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

A driver might 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 is not defined as protected in \ Doctrine \ DBAL \ Driver \ PDOPgSql \ Driver. This is a “hacked” bit because we use PDO DriverOptions, and I'm not sure if this is a good way, but it works.

Hope this helps.

Yours faithfully,

Patryk

Answer 2

I created a shorter version of the same code.

https://gist.github.com/jkuchar/13c640fb2ac071da14f4

Answer 3

Starting with Doctrine 2.5, you can specify the schema name in the @Table annotation:

 /** * Clerk * * @Table(schema="schema") */ class Clerk { } 

The only drawback is that the Symfony console cannot do this, you must specify it manually.