Problem with dot in table name when using SQLite

[zmitic]

I have an entity like this:

<many-to-many field="someRelation" target-entity="App\Entity\MyEntity">
    <join-table name="some_prefix.my_relation_table">  <!-- notice the dot in table name -->

<!-- ... -->

This all works nice for PQL. SQLite is a bit different: schema:update --force will put __ in place of dot, no problems here. But running fixtures would throw this error:

[Doctrine\DBAL\Exception\TableNotFoundException (1)]     
An exception occurred while executing a query: SQLSTATE[HY000]: 
General error: 1 no such table: some_prefix.my_relation_table

It is pretty weird given that I use the usual persist and flush approach, no raw SQL. The exception is thrown in this line, and adding dd($this->connection) (to make sure I used correct env) did show sqlite as driver name:

^ PDO {#169
  inTransaction: true
  attributes: {
    CASE: NATURAL
    ERRMODE: EXCEPTION
    PERSISTENT: false
    DRIVER_NAME: "sqlite"
    ORACLE_NULLS: NATURAL
    CLIENT_VERSION: "3.34.1"
    SERVER_VERSION: "3.34.1"
    STATEMENT_CLASS: array:1 [
      0 => "PDOStatement"
    ]
    DEFAULT_FETCH_MODE: BOTH
  }
}

After hours of fiddling with setup, I think this is a bug. Not sure, maybe I made some mistake somewhere, but I don't have any other ideas. Is this something known? If it is a real bug, I guess I could go around it with some service decoration; suggestion to which would be greatly appreciated.

The config is nothing special:

# config/packages/test/doctrine.yaml
doctrine:
    dbal:
        driver: pdo_sqlite
        path: "%kernel.project_dir%/test.db"
        url: null

[derrabus]

My best guess is that data fixtures don't support DBAL's schema emulation. Given that this feature is deprecated and about to be removed, I don't think it's worth fixing, tbh.

[zmitic]

@derrabus Thanks, I am not even worried too much. Do you think service decoration could help here? I.e. replace . with __ somewhere, somehow, and leave it that way? The internals of Doctrine is completely new to me.

I am looking at loadClassMetadata event, hopefully that will help. Any idea is welcomed to overcome the problem, doesn't have to be pretty.

[derrabus]

Especially when working with SQLite, my best recommendation would be to keep all tables of your entity model within the same schema.

If you're certain you want to work with multiple schemas in SQLite, you'll need to attach multiple SQLite files, see ATTACH DATABASE, and opt-out of DBAL's schema emulation:

$connection->getDatabasePlatform()->disableSchemaEmulation();

My general advice: Don't build an entity model that spans across multiple schemas if you want portability.

[zmitic]

My general advice: Don't build an entity model that spans across multiple schemas if you want portability.

It is kinda late now, we already have plenty of namespaces. But I solved it with this code; it is not pretty, but works:

#[AsDoctrineListener(Events::loadClassMetadata)]
#[When(env: 'test')]
class LoadClassMetaDataListener
{
    public function loadClassMetadata(LoadClassMetadataEventArgs $event): void
    {
        $metadata = $event->getClassMetadata();
        $tableName = $metadata->getTableName();
        if (str_contains($tableName, '.')) {
            $metadata->setPrimaryTable([
                'name' => str_replace('.', '__', $tableName),
            ]);
        }

        foreach ($metadata->getAssociationMappings() as $associationMapping) {
            Assert::nullOrString($joinTable = $associationMapping['joinTable']['name'] ?? null);
            if (!$joinTable || !str_contains($joinTable, '.')) {
                continue;
            }

            $metadata->setAssociationOverride($associationMapping['fieldName'], [
                'joinTable' => [
                    'name' => str_replace('.', '__', $joinTable),
                ],
            ]);
        }
    }
}

---

I am closing the issue. It is rare case for anyone to encounter and the solution is here anyway.