[DBAL-1241] Comparator generates wrong result if using database_name.table_name notation Created: 28/May/15  Updated: 28/May/15

Status: Open
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Pavlo Chipak Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: dbal, mysql, schematool
Environment:

Mysql 5.6.21, PHP 5.6.3, Windows



 Description   

At fist created issue in migrations https://github.com/doctrine/migrations/issues/258 but was referenced here.

I'm using Symfony2. In entities schemas configs I'm using dot notation in table names (database_name.table_name) for cross-database joins. When I create migration, there are no deletes of created foreign keys in down() method. Example (some unnecessary code removed):

    public function up(Schema $schema)
    {
        $this->addSql('CREATE TABLE import.article (id INT AUTO_INCREMENT NOT NULL, edition_id INT DEFAULT NULL, title VARCHAR(255) NOT NULL, subtitle LONGTEXT NOT NULL, theme VARCHAR(255) NOT NULL, bar VARCHAR(255) NOT NULL, text LONGTEXT NOT NULL, createdAt DATETIME NOT NULL, editedAt DATETIME DEFAULT NULL, creator_id INT DEFAULT NULL, editor_id INT DEFAULT NULL, INDEX IDX_71D0368461220EA6 (creator_id), INDEX IDX_71D036846995AC4C (editor_id), INDEX IDX_71D0368474281A5E (edition_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE import.author (id INT AUTO_INCREMENT NOT NULL, article_id INT DEFAULT NULL, name VARCHAR(255) NOT NULL, createdAt DATETIME NOT NULL, editedAt DATETIME DEFAULT NULL, creator_id INT DEFAULT NULL, editor_id INT DEFAULT NULL, INDEX IDX_8B81B94F61220EA6 (creator_id), INDEX IDX_8B81B94F6995AC4C (editor_id), INDEX IDX_8B81B94F7294869C (article_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE import.document (id INT AUTO_INCREMENT NOT NULL, edition_id INT DEFAULT NULL, title LONGTEXT NOT NULL, number VARCHAR(255) NOT NULL, theme VARCHAR(255) NOT NULL, department VARCHAR(255) NOT NULL, adoptedAt DATETIME NOT NULL, type INT NOT NULL, text LONGTEXT NOT NULL, createdAt DATETIME NOT NULL, editedAt DATETIME DEFAULT NULL, creator_id INT DEFAULT NULL, editor_id INT DEFAULT NULL, INDEX IDX_961EE31A61220EA6 (creator_id), INDEX IDX_961EE31A6995AC4C (editor_id), INDEX IDX_961EE31A74281A5E (edition_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE import.edition (id INT AUTO_INCREMENT NOT NULL, periodical_id INT DEFAULT NULL, publication_id INT DEFAULT NULL, region_id INT DEFAULT NULL, issue INT NOT NULL, number INT NOT NULL, publishedAt DATETIME NOT NULL, createdAt DATETIME NOT NULL, editedAt DATETIME DEFAULT NULL, creator_id INT DEFAULT NULL, editor_id INT DEFAULT NULL, INDEX IDX_DB7B20FD61220EA6 (creator_id), INDEX IDX_DB7B20FD6995AC4C (editor_id), INDEX IDX_DB7B20FD855A7B04 (periodical_id), INDEX IDX_DB7B20FD38B217A7 (publication_id), INDEX IDX_DB7B20FD98260155 (region_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE import.image (id INT AUTO_INCREMENT NOT NULL, storage_id INT NOT NULL, width VARCHAR(255) NOT NULL, height VARCHAR(255) NOT NULL, material INT NOT NULL, material_type VARCHAR(50) NOT NULL, creator_id INT DEFAULT NULL, editor_id INT DEFAULT NULL, INDEX IDX_E81675F561220EA6 (creator_id), INDEX IDX_E81675F56995AC4C (editor_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE import.periodical (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, alias VARCHAR(255) NOT NULL, createdAt DATETIME NOT NULL, editedAt DATETIME DEFAULT NULL, creator_id INT DEFAULT NULL, editor_id INT DEFAULT NULL, INDEX IDX_AF07D39961220EA6 (creator_id), INDEX IDX_AF07D3996995AC4C (editor_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE import.publication (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, createdAt DATETIME NOT NULL, editedAt DATETIME DEFAULT NULL, creator_id INT DEFAULT NULL, editor_id INT DEFAULT NULL, INDEX IDX_94AF610261220EA6 (creator_id), INDEX IDX_94AF61026995AC4C (editor_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE import.region (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, createdAt DATETIME NOT NULL, editedAt DATETIME DEFAULT NULL, creator_id INT DEFAULT NULL, editor_id INT DEFAULT NULL, INDEX IDX_394C90F161220EA6 (creator_id), INDEX IDX_394C90F16995AC4C (editor_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('ALTER TABLE import.article ADD CONSTRAINT FK_71D0368474281A5E FOREIGN KEY (edition_id) REFERENCES import.edition (id)');
        $this->addSql('ALTER TABLE import.author ADD CONSTRAINT FK_8B81B94F7294869C FOREIGN KEY (article_id) REFERENCES import.article (id)');
        $this->addSql('ALTER TABLE import.document ADD CONSTRAINT FK_961EE31A74281A5E FOREIGN KEY (edition_id) REFERENCES import.edition (id)');
        $this->addSql('ALTER TABLE import.edition ADD CONSTRAINT FK_DB7B20FD855A7B04 FOREIGN KEY (periodical_id) REFERENCES import.periodical (id)');
        $this->addSql('ALTER TABLE import.edition ADD CONSTRAINT FK_DB7B20FD38B217A7 FOREIGN KEY (publication_id) REFERENCES import.publication (id)');
        $this->addSql('ALTER TABLE import.edition ADD CONSTRAINT FK_DB7B20FD98260155 FOREIGN KEY (region_id) REFERENCES import.region (id)');
    }

    public function down(Schema $schema)
    {
        $this->addSql('DROP TABLE import.article');
        $this->addSql('DROP TABLE import.author');
        $this->addSql('DROP TABLE import.document');
        $this->addSql('DROP TABLE import.edition');
        $this->addSql('DROP TABLE import.image');
        $this->addSql('DROP TABLE import.periodical');
        $this->addSql('DROP TABLE import.publication');
        $this->addSql('DROP TABLE import.region');
    }

This is lost

        $this->addSql('ALTER TABLE import.article DROP FOREIGN KEY FK_71D0368474281A5E');
	$this->addSql('ALTER TABLE import.author DROP FOREIGN KEY FK_8B81B94F7294869C');
        $this->addSql('ALTER TABLE import.document DROP FOREIGN KEY FK_961EE31A74281A5E');
        $this->addSql('ALTER TABLE import.edition DROP FOREIGN KEY FK_DB7B20FD855A7B04');
        $this->addSql('ALTER TABLE import.edition DROP FOREIGN KEY FK_DB7B20FD38B217A7');
        $this->addSql('ALTER TABLE import.edition DROP FOREIGN KEY FK_DB7B20FD98260155');

I had make some research and concluded that in class Doctrine\DBAL\Schema\Comparator in line 91 (https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Schema/Comparator.php#L91) replacing getShortestName by getName fixes this problem. But I'm sure it's not complete fix of this problem.



 Comments   
Comment by mikeSimonson [ 28/May/15 ]

As I explained to you, your problem only exist because of the order of those delete statement.

Maybe the delete statement could be ordered taking into account the tree of dependencies created by the foreign keys ?





[DBAL-1168] Schema's getMigrateFromSql always adds CREATE SCHEMA Created: 11/Mar/15  Updated: 28/May/15

Status: Open
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.5.1
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Varga Bence Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 1
Labels: postgresql, schematool
Environment:

Postgresql



 Description   

I originally posted this to Migrations; noticing that all the generated down() methods start with a "CREATE SCHEMA public" line.

Inspecting the return from Schema#getMigrateFromSql it indeed contains the create statement.



 Comments   
Comment by Adam Sentner [ 19/May/15 ]

I am also having this issue. The down() method always adds: $this->addSql('CREATE SCHEMA public');

Same environment, also using Postgres.

Any chance this is on anyone's radar for a release in the near future?

Comment by Albert Casademont [ 28/May/15 ]

Hit by this too. The problem seems to be that the "public" namespace is not added to the table names by default and hence the diff between what postgres says (a "public" schema is created by default in the DB) and what our schema says.

I tried to solve this with a workaround by prepending "public." to all table names. It works for the first migration but then in the next migration will try to delete all tables without the "public." and create them again. So that's not working!

The solution is assuming that there's always a default 'public' namespace in the Schema.php class.





[DBAL-819] Schema-tools does not work on multiple Oracle's schemas Created: 21/Feb/14  Updated: 28/May/15

Status: Awaiting Feedback
Project: Doctrine DBAL
Component/s: Platforms, Schema Managers
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Antoine Descamps Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 1
Labels: Cli, mysql, oracle, orm, schematool
Environment:

DB: Oracle 11g



 Description   

The schema-tools, used via the CLI, is not able to detect schema's changes when working on multiple schemas.

For instance, the ORM is configured with a "global" Oracle's user, having permissions on every schemas. To specify which entities belong to which schema, I've prefixed the table name with the corresponding schema.

When trying to do the following command:

orm:schema-tool:update --dump-sql

Doctrine returns me the following message:

Nothing to update - your database is already in sync with the current entity metadata.

If, instead of using the "global" user in the Doctrine's configuration, I set the user of the specific schema I'm trying to generate a table on based from an entity, it works.



 Comments   
Comment by Steve Müller [ 21/Feb/14 ]

Moved this to DBAL for now. It seems to be related to schema prefixed table names not being evaluated in the platforms when generating the SQL for reverse engineering the database schema.

Comment by Steve Müller [ 22/Feb/14 ]

Antoine Descamps Okay after having investigated on this in detail and thinking about the possibilities we have to find a solution for this, I came to the following conclusion:
ORM's schema tool and DBAL's schema introspection are designed to be bound to what Doctrine defines as "database" for each platform. This is the scope of the whole operation. In case of Oracle it is the "user". You cannot break out of this scope in any way as the current DBAL implementation is not designed for a "complete" schema introspection and therefore does not allow it at some points. Fully understanding your concern I am afraid we cannot find a reasonable solution for your use case at this point in development (2.x). Furthermore there is a good reason for limiting the schema introspection to a certain layer. If the schema tool would introspect the "complete" schema without regard to the database it is connected to, it would suggest a lot of schema changes from other databases that do not belong to the context of the entity manager / database. This behaviour would even cause a lot more annoyance to users as it is the default use case that users are only interested in one database per entity manager.
Also this problem is completely independant from your mapping definitions. So it doesn't matter whether you prefix your table names or not. When running the "update" operation on the schema tool, it is the online schema introspection part that is preventing the schema tool from behaving as you would wish.
Changing the schema introspection behaviour in DBAL would completely break BC and is at some places in the code not even possible without changing the API.
I am sorry that I have to disappoint you with this conclusion but we I am afraid we cannot do anything about your issue until we start developing 3.x. We might reevaluate your use case their and see what we can do.

Comment by Pavlo Chipak [ 28/May/15 ]

Steve Müller I'm using Symfony2 and tying to do such things on MySQL (one entity manager, one user and multiple databases). I wrote my own realisation of schema:update based on code of original command. I have config parameter with list of schemas (databases) and then inside a command in the loop I reloading EM with selected database. Then, as usual, I'm getting diff of schema. At the end, a have diff (list of queries) for each database and can run it all at one. I know it's bad solution, but may be it can be done something similar and more elegant in the core? Like create white list of schemas parameter for EM. If thats can be done, there will not be a problem like:

If the schema tool would introspect the "complete" schema without regard to the database it is connected to, it would suggest a lot of schema changes from other databases that do not belong to the context of the entity manager / database.

At next, if in EM config are set more then one schema we always need use schema.table for tabe naming in the Comparator and other core module, and in generated queries. As profit, it must be not hard to allow using cross-database FK.





Generated at Fri May 29 16:47:44 UTC 2015 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.