Migrations cannot support mlutiple schemas

[willdurand]

The new 1.6 migrations cannot support multiple schemas. If you have 2 schemas:

schema1:

<database package="beta" name="scratch" schema="beta" defaultIdMethod="native">
  <table name="projects" phpName="Projects">
...

schema2:

<database package="analysisdbv2" name="scratch" schema="analysisdbv2" defaultIdMethod="native">
  <table name="analyzer_log" phpName="AnalyzerLog">
...

The diff task generates:

DROP TABLE IF EXISTS `analyzer_log`;
(snip)
CREATE TABLE `beta`.`projects`
(snip)
CREATE TABLE `analysisdbv2`.`analyzer_log`

---

Imported from: http://www.propelorm.org/ticket/1483

[drmikecrowe]

I'll take a crack at fixing this, if somebody can point me in the right direction.

[fzaninotto]

Look at PropelDatabaseComparator::compareTable(). I think it only compare table names, not schemas.

(https://github.com/propelorm/Propel/blob/master/generator/lib/model/diff/PropelDatabaseComparator.php)

[drmikecrowe]

Francois, Initially, looks like Database->hasTable() and getTable() should have some code like this:

                if ($this->schema && $this->getPlatform() && $this->getPlatform()->supportsSchemas()) {
                    $offset = strlen($this->schema)+1;
                    if ( substr(strtolower($name),0,$offset) == $this->schema . '.') {
                        $name = substr($name,$offset);
                    }
                }

However, in Table->setupReferrers(), there is this line:

    $foreignTable = $this->getDatabase()->getTable($foreignKey->getForeignTableName());

I am using multiple schemas to reference a foreign key to the other schema (and what's worse, in SQL only, not a real foreign key). XML looks like:

    <foreign-key name="beta_projects" foreignTable="projects" foreignSchema="beta" skipSql = "true" phpName="BetaProjects">
      <reference local="project_id" foreign="id"/>
    </foreign-key>

Any suggestions on how to handle a foreign key reference to a different schema?

[fzaninotto]

Well, with your example, that wouldn't work with 'hasTable(), but that would work with 'getTable()', right?

[drmikecrowe]

The issue is the foreign key references a table/field in another schema, and hence another Database (I believe). Therefore, when checking the database analysisdbv2, the above foreign key references the beta schema, which would be beta.projects.id. When you are in the analysisdbv2 database structure, inspecting foreign keys, how can/should we validate a reference to an external schema?

[FernandoFigueroa]

Hi all, i have the same problem:

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS fos_user_group;
RENAME TABLE fos_user TO master.fos_user;
RENAME TABLE fos_group TO master.fos_group;

And so on, is there any workaround for this issue (Using other tools e.g)?? I have symfony 2.6.11 and propel-bundle 1.4.5