Jira issue originally created by user @beberlei:
The column identifiers are not quoted in the SchemaTool/Platform.
Funny is Doctrine\Tests\ORM\Functional\SchemaTool\MysqlSchemaToolTest::testGetCreateSchemaSql2() suffers from this "problem" by asserting mysql invalid syntax as correct creation code.
All identifiers should be escaped by the Platform.
Comment created by romanb:
This is actually intended, I mean not the wrong test case but the fact that the DBAL itself does not escape anything. If someone is using the DBAL directly he can quote problematic names himself ($platform->quoteIdentifier(...)). Identifier quoting is discouraged and not even a 100% reliable solution for reserved words.
There is also no global switch anymore like in D1 to "quote all identifiers" as this is like breaking a butterfly on the wheel if there is just 1 problematic column name.
How quoting a specific column works from the ORM is described here:
So, the ORM applies quoting on individual columns that have been marked as such. Apart from that, there is no quoting going on, neither in the ORM, nor in the DBAL.
The test case needs to be fixed, of course.
Comment created by @beberlei:
But the DDL SQL is generated inside DBAL Platform, i don't see a way to affect its quoting identifiers or not strategy.
All identifiers should be escaped by the platform maybe a bit harsh, i meant all the DDL sql. :)
If we would do that we would then need to take care of not quoting twice.
I'll try to explain how it currently works, its quite simple. Yes, the platform constructs the DDL, based on some parameters and these may already be quoted or escaped or whatever. The platform doesnt do anything with it, except embed it into the DDL statement.
I'll try to quickly outline the process of a quoted table/column name:
1) @Column(name="number", ...)
2) Inside ClassMetadata/AssociactionMapping where the mapping is validated & completed, the following happens: If the column name starts with a backtick , apply trim('', $name) before storing the name (never store quoted names anywhere!) and mark 'quoted' => true for that column.
, apply trim('
3) Whenever a table or column name is placed in an SQL statement or passed to the DBAL use one of the following methods, depending on who owns the column:
Now, SchemaTool, for example uses (or should use!) These methods when it constructs the parameters that are passed to the DBAL.
Identifier quoting is a very ugly problem and we tried to minimize the effect it has on our code. The only solid way to fix the problem of a reserved name is to change the name and not to quote it.
Of course, if you have a better idea/solution, just shoot! :)
Btw. What is the reserved word in testGetCreateSchemaSql2 ? "decimal" ?
Yes its decimal, thanks for your explanation, i get it why its not necessary to quote at this point then.
Ok, then its probably just that decimal should be quoted in the DecimalModel.
Just did that! Thanks for finding this.
Issue was closed with resolution "Fixed"