Quoting problems with schema and dots in table name mapping

[guilliamxavier]

Test entity:

/** @Entity @Table(TEST_VALUES_HERE) */
class Qux
{
    /** @Id @Column */
    public $id;
}

Test code:

echo $entityManager->createQuery('SELECT e FROM ' . Qux::class . ' e')->getSQL();

Full results:

• table_schema-2.6.xlsx
• table_schema-master_3.0.xlsx

Recap (master is simpler):

2.6 (current stable):

Issues:

• both name="`other-b`.foo" and schema="`other-b`", name="foo" generate SELECT ... FROM `other-b`.foo (invalid SQL)
  and one must do name="other-b.`foo`" or schema="other-b", name="`foo`" to get a FROM "other-b"."foo" (which is still not exactly the wanted FROM "other-b".foo)
• name="`foo.x`" generates SELECT ... FROM `foo.x` (invalid SQL)
  and there is no way to get a FROM "foo.x"
• name="other.`foo.x`" generates SELECT ... FROM "other"."foo"."x" and
  schema="other", name="`foo.x`" generates SELECT ... FROM other.x` (invalid SQL)

Thoughts:

• Option 1: Do not change anything, just document the current (surprising) behavior.
• Option 2: Disallow dots in table names/schemas (via mapping validation).
• Option 3: Handle backquotes on the schema [part] too, and properly handle backquoted dots:
  • both name="`other-b`.foo" and schema="`other-b`", name="foo" would generate a FROM "other-b".foo
    while name="other.`foo-a`" and schema="other", name="`foo-a`" would generate a FROM other."foo-a"
  • name="`foo.x`" would generate a FROM "foo.x"
    (name="other.foo" and schema="other", name="foo" still generating a FROM other.foo)
  • both name="other.`foo.x`" and schema="other", name="`foo.x`" would generate a FROM other."foo.x"

master (future 3.0):

Issues:

• name="foo.x" generates SELECT ... FROM "foo"."x"
  and there is no way to get a FROM "foo.x"
• schema="other.y", name="foo.x" generates SELECT ... FROM "other"."y"."foo"."x"
  and there is no way to get a FROM "other.y"."foo.x"

Thoughts:

• Option 1: same as above (document)
• Option 2: same as above (disallow)
• Option 3: Drop support of the "dot-notation" (implicit schema, name="myschema.mytable"), and quote the (explicit) schema and the name separately:
  • name="foo.x" would generate a FROM "foo.x"
    (schema="other", name="foo" still generating a FROM "other"."foo")
  • schema="other.y", name="foo.x" would generate a FROM "other.y"."foo.x"
    (and no place anymore for a FROM "other"."y"."foo"."x")

What do you think?

---

History of the features:

• dot-notation: since long (2.2?): DDC-1360: Reserved words escaping fails when using multiple reserved words #1979 (and the 2 commits linked in comments near the end)
• mapping schema attribute: since 2.5: [DDC-2825] Fix persistence on a table with a schema on a platform without schema support #881 (and the 3 issues referenced at the bottom)
• auto-quoting (auto-escaping) (and no backquotes in mappings anymore): for next 3.0

[guilliamxavier]

Additional info for master: it looks like support of implicit schema has been "implicitly" dropped in 8a4ca55 (search for '.' [2 occurrences] and for implicit_schema [6 occurrences])