Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-2825

SQlite - Table names are not escaped when inserting data

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Duplicate
    • Affects Version/s: 2.4.1
    • Fix Version/s: None
    • Component/s: ORM
    • Security Level: All
    • Labels:
    • Environment:
      SQlite

      Description

      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.

        Activity

        Hide
        Michaël Perrin added a comment -

        Sorry, it actually duplicates #DDC-2636

        Show
        Michaël Perrin added a comment - Sorry, it actually duplicates # DDC-2636
        Hide
        Marco Pivetta added a comment -

        Michaël Perrin doctrine does not escape table names by default. You will need a custom naming and/or quoting strategy for that.

        Show
        Marco Pivetta added a comment - Michaël Perrin doctrine does not escape table names by default. You will need a custom naming and/or quoting strategy for that.
        Hide
        Michaël Perrin added a comment - - edited

        @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.

        Show
        Michaël Perrin added a comment - - edited @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.
        Hide
        Marco Pivetta added a comment -

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

        Show
        Marco Pivetta added a comment - No, it is indeed a problem. I'm wondering if the default naming strategy should handle this for SQLite...
        Hide
        Michaël Perrin added a comment -

        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!

        Show
        Michaël Perrin added a comment - 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!
        Hide
        Michaël Perrin added a comment -

        @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.

        Show
        Michaël Perrin added a comment - @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.

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Michaël Perrin
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: