symfony2 + doctrine2@postgresql setting a schema

Question

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?

Symfony2 just uses doctrine, you should be able to solve it the exact same way. — hakre
– hakre, Commented May 18, 2012 at 14:44
yeah. but i dont know where i should hook into :( im absolutely new to symfony2... — Michael Mezger
– Michael Mezger, Commented May 18, 2012 at 15:02
Look for the EntityManager as well. If nothing goes, grep is your friend. — hakre
– hakre, Commented May 18, 2012 at 22:05
yeah, but i dont want to modify the symfony2 files :) so i think i have to add some special code to a special place. maybe i need to add a bundle that inherits from the doctrine bundle? — Michael Mezger
– Michael Mezger, Commented May 30, 2012 at 13:07
I don't know, but I'll place a bounty on your question, maybe it helps to gain some attention. — hakre
– hakre, Commented May 30, 2012 at 13:08

Patryk Kordylewski · Accepted Answer · 2012-05-31 08:09:31Z

5

+100

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

answered May 31, 2012 at 8:09

Patryk Kordylewski

1,2691 gold badge7 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cristhian Boujon Over a year ago

Why not extend from \Doctrine\DBAL\Driver\PDOPgSql\Driver.php?

ddegasperi Over a year ago

The class Doctrine\DBAL\Driver\PDOPgSql\Driver is declared as final and therefore cannot be derived. However, the customer driver could be built as a wrapper class around the original driver in order to use all of its official options.

Joshua · Accepted Answer · 2015-02-06 09:51:05Z

1

Since Doctrine 2.5 you can specify the schema name in the @Table annotation:

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

The only downside is, the symfony console can't do that, you have to specify it by hand.

answered Feb 6, 2015 at 9:51

Joshua

2,9922 gold badges27 silver badges40 bronze badges

1 Comment

Loenix Over a year ago

This is not working for table migrations and this is not excluding others schemas from doctrine management.

Kirill · Accepted Answer · 2024-02-06 07:06:56Z

0

In stack PostgreSQL, Symfony 6.4.3 and Doctrine 2.17 you can use attribute for set schema name in Entity class.

Example

#[ORM\Table(schema: 'SCHEMA_NAME')]

But when you make migrations, migration versions table created in public schema, for fix it, you need add this parameters to doctrine_migrations.yaml

storage:
  # Default (SQL table) metadata storage configuration
  table_storage:
    table_name: '%env(resolve:SCHENA_NAME)%.doctrine_migration_versions'

Also you need to .env file variable SCHEMA_NAME and set value for this variable.

Sorry for my English, it's not my native language. Hope it's help sombody.

answered Feb 6, 2024 at 7:06

Kirill

134 bronze badges

Collectives™ on Stack Overflow

symfony2 + doctrine2@postgresql setting a schema

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related