DDC-2825: SQlite - Table names are not escaped when inserting data

[doctrinebot]

Jira issue originally created by user michael.perrin:

When a table name is defined with a dot (example: schemaname.tablename) in a Doctrine entity, it is created with a double underscode in SQlite (example : schemaname**tablename).

However, when a Doctrine entity is persisted (and saved with INSERT INTO), the dot is not converted to a double underscore, leading to "no such table: schemaname.tablename" exception.

[doctrinebot]

• is referenced by DDC-2861: [GH-881] Fix persistence exception on a table with a schema on a platform without schema support

[doctrinebot]

Comment created by michael.perrin:

Sorry, it actually duplicates #DDC-2636

[doctrinebot]

Issue was closed with resolution "Duplicate"

[doctrinebot]

Comment created by @Ocramius:

[~michael.perrin] doctrine does not escape table names by default. You will need a custom naming and/or quoting strategy for that.

[doctrinebot]

Comment created by michael.perrin:

@marco Pivetta That's right but that is a problem, isn't it?

Here is an example.

An database schema is created with this definition :

MyNamespace\Mytable:
    type: entity
    table: myschema.mytable
    # ...

If I do this :

$myTable = new MyNamespace\Mytable();
$entityManager->persist($myTable);
$entityManager->flush();

This will work for PostgreSQL for instance, but not SQlite, because Doctrine DBAL converted "myschema.mytable" to "myschema**mytable" when creating the database, but the ORM will still use "myschema.mytable".

Did I miss something or is it an issue?
Sorry if it is a feature and that there is a way to avoid this.

[doctrinebot]

Comment created by @Ocramius:

No, it is indeed a problem. I'm wondering if the default naming strategy should handle this for SQLite...

[doctrinebot]

Comment created by michael.perrin:

I started to explore the code and see how this could be solved, but unfortunately I don't have an enough broad knowledge of the whole Doctrine architecture and how new things should be solved the right way.
But I would happy to help when I'm back in a few days!

[doctrinebot]

Comment created by michael.perrin:

@marco Pivetta I am wondering if the best solution to handle this would be to handle the "schema" attribute the right way.

I mean: instead of defining a table with a name like "myschema.mytable", wouldn't it be better to define it with "name: mytable, schema: myschema" (either in YAML, or Annotation, ...).
Database systems that handle schemas like PostgreSQL will transform such table definition to "myschema.mytable", and those which don't would use "myschema**mytable" or even "mytable" if the schema name is ignored in this case (it could be configurable).
In all cases, the table name would be processed to be transformed to something, whatever the database system being used.

This would be used both in the ORM and DBAL.

[doctrinebot]

Comment created by @doctrinebot:

A related Github Pull-Request [GH-881] was closed:
#881

[doctrinebot]

Comment created by @doctrinebot:

A related Github Pull-Request [GH-881] was assigned:
#881