Multiple DB Schema & migrations with Doctrine2 & PostgreSQL

Question

I have a PG database containing multiple schemas.

I have a symfony2 application which need to connect to only one of them.

If I don't configure anything, by default Doctrine2 searches in all the schemas so it try to make SELECT on table where it haven't the right.

I followed this answer : symfony2 + doctrine2@postgresql setting a schema

Now it seems to go only in the shema I specified but it is still looking for some tables no present in my schema but in other ones.

When I execute something ldoctrine:migrations:diff

[Doctrine\DBAL\DBALException]                                                                     
  An exception occurred while executing 'SELECT min_value, increment_by FROM "pgstatspacknameid"':  
  SQLSTATE[42P01]: Undefined table: 7 ERROR:  relation "pgstatspacknameid" does not exist           
  LINE 1: SELECT min_value, increment_by FROM "pgstatspacknameid"                                   
                                              ^                                                     

  [PDOException]                                                                           
  SQLSTATE[42P01]: Undefined table: 7 ERROR:  relation "pgstatspacknameid" does not exist  
  LINE 1: SELECT min_value, increment_by FROM "pgstatspacknameid"                          
                                              ^

So following the doctrine migration documentation, I added this line in the connection configuration :

        schema_filter: ~^(?!pgstats)~

It still always try to make SELECT in those tables...

user993553 · Answer 1 · 2015-10-13T03:53:01.560

You need the option --db-configuration to the migrate command. It takes a configuration file as argument. Try with the following content of the configuration file.

<?php
return array(
    "driverClass"=>"AppBundle\Driver",
    "host" => "localhost",
    "user" => "test",
    "password" => "test",
    "dbname" => "test"
        );
?>

Furthermore I changed vendor/doctrine/dbal/lib/Doctrine/DBAL/SchemaPostgreSqlSchemaManager.php to always prepend the schema to the sequence names.

like this:

/**
 * {@inheritdoc}
 */
protected function _getPortableSequencesList($sequences)
{
    $sequenceDefinitions = array();
    foreach ($sequences as $sequence) {//+ || true
        if ($sequence['schemaname'] != 'public' || true) {
            $sequenceName = $sequence['schemaname'] . "." . $sequence['relname'];
        } else {
            $sequenceName = $sequence['relname'];
        }

        $sequenceDefinitions[$sequenceName] = $sequence;
    }
    $list = array();

    foreach ($this->filterAssetNames(array_keys($sequenceDefinitions)) as $sequenceName) {
        $list[] = $this->_getPortableSequenceDefinition($sequenceDefinitions[$sequenceName]);
    }
    return $list;
}

/**
 * {@inheritdoc}
 */
protected function getPortableNamespaceDefinition(array $namespace)
{
    return $namespace['nspname'];
}

/**
 * {@inheritdoc}
 */
protected function _getPortableSequenceDefinition($sequence)
{//+ || true
    if ($sequence['schemaname'] != 'public' || true) {
        $sequenceName = $sequence['schemaname'] . "." . $sequence['relname'];
    } else {
        $sequenceName = $sequence['relname'];
    }

    $data = $this->_conn->fetchAll('SELECT min_value, increment_by FROM ' . $this->_platform->quoteIdentifier($sequenceName));

    return new Sequence($sequenceName, $data[0]['increment_by'], $data[0]['min_value']);
}

(added the or true both places where assumed that schema name != 'public')

Thanks i am going to test this solution ! Do you have an idea of how override this class without change the file in vendor ? — BastienSander, Oct 13 '15 at 06:53
Not tested but you might be able to override getSchemaManager from AbstractPostgresqlDriver to return a custom SchemaManager with the above changes. — user993553, Oct 14 '15 at 23:58

Multiple DB Schema & migrations with Doctrine2 & PostgreSQL

1 Answers1