SQLite schema namespace adjustment incorrectly implied for ManyToMany inserts.

Many databases provide schema namespacing that can be used to segregate tables. The schema name can be prepended to the tablename with a namespace separator, typically a dot. For example, if you created the USERS table in the APP schema namespace, your select might look like SELECT APP.USERS.ID FROM APP.USERS.

Doctrine recognizes this dot notation for databases like Oracle that support schema namespaces. However, since SQLite does not, Doctrine translates APP.USERS to APP__USERS to provide a sort of faux namespacing. This feature works correctly when using migrations to create the table and when using Doctrine to query it, but is broken when inserting via a ManyToMany relationship.

If all the following are true:

The SQLite driver is used.
Table names are overridden in the entity metadata definition in order to provide a schema namespace.
A ManyToMany relationship via a join table is created.
An insert is performed via the ManyToMany relationship.

Then the table name of the join table is not translated to SCHEMA__TABLE as expected, and the insert fails because it will incorrectly refer to SCHEMA.TABLE.

For example, if we define a SQLite database in our .env file:

DATABASE_URL="sqlite:///%kernel.project_dir%/var/data.db"

And create Student and Course entities like:

/**
 * @ORM\Entity()
 * @ORM\Table(name="APP.COURSE")
 */
class Course
{
    /**
     * @ORM\ManyToMany(targetEntity=Student::class, inversedBy="courses")
     * @ORM\JoinTable(name="APP.COURSE_STUDENT_JOIN")
     */
    private $students;

/**
 * @ORM\Entity()
 * @ORM\Table(name="APP.STUDENT")
 */
class Student
{
    /**
     * @ORM\ManyToMany(targetEntity=Course::class, mappedBy="students")
     */
    private $courses;

Then we can run migrations to create the database and we'll get the fake namespaced table names as expected:

% ./bin/console make:migration
% ./bin/console doctrine:migrations:migrate
% sqlite3 var/data.db ".tables"
APP__COURSE                  APP__STUDENT
APP__COURSE_STUDENT_JOIN     doctrine_migration_versions

We can successfully use the entities to create new rows:

 $em->persist((new Course())->setName('Barology'));
 $em->persist((new Student())->setName('Jim'));
 $em->flush();

And to query them:

$barology = $em->getRepository(Course::class)->findOneByName('Barology');
$jim = $em->getRepository(Student::class)->findOneByName('Jim');

However, we'll get an error if we create a relationship that would result in an insert to the join table:

$jim->addCourse($barology);

An exception occurred while executing 'SELECT t0.id AS id_1, t0.name AS name_2 FROM APP__COURSE t0 INNER JOIN APP.COURSE_STUDENT_JOIN ON t0.id = APP.COURSE_STUDENT_JOIN.course_id WHERE APP.COURSE_STUDENT_JOIN.student_id = ?' with params [1]:
SQLSTATE[HY000]: General error: 1 no such table: APP.COURSE_STUDENT_JOIN

Note in the query that Doctrine built above, APP.COURSE has been correctly translated into APP__COURSE, but APP.COURSE_STUDENT_JOIN has not. This occurs only for inserts into ManyToMany join tables -- inserts into other tables and read queries on the join tables (e.g., $jim->getCourses()) will be correctly translated.

The above code can be made to work by either removing the schema namespaces from the Entity metadata definitions or changing the database driver to something that supports schema namespaces. Thus, there is a simple workaround when using a single database platform. However, applying this workaround makes the entities no longer transportable or agnostic, and prevents me (for example) from using Oracle for production and SQLite for test.

I've got a minimal repo here to demonstrate: https://github.com/AlexHowansky/doctrine_sqlite_bug

See the src/Command/TestCommand.php file for an example containing the code noted here.

doctrine:common 3.1.0 doctrine:dbal 2.12.1 doctrine:orm 2.8.1

doctrine / orm

SQLite schema namespace adjustment incorrectly implied for ManyToMany inserts. #8411