[DDC-1105] Unable to persist entities implemented using Class Table Inheritance(CTI) strategy across multiple database tables Created: 07/Apr/11  Updated: 01/May/11  Resolved: 01/May/11

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Critical
Reporter: Ire Ogunsina Assignee: Benjamin Eberlei
Resolution: Won't Fix Votes: 0
Labels: None
Environment:

zend framework, Doctrine 2, Apache, MySQL, Windows



 Description   

I have 2 classes, a Parent class, Person and a Child class Employee using Class Table Inheritance mapping strategy. The classes' corresponding tables exist in 2 separate database: Person table exists in database: dbOne and Employee table is in dbTwo.

The classes are like:

/**

  • @Entity
  • @InheritanceType("JOINED")
  • @DiscriminatorColumn(name="discr", type="string")
  • @DiscriminatorMap( {"person" = "Person", "employee" = "Employee"}

    )

  • @Table(name="Person")
    */
    class Person
    {
    and the child class

/**

  • @Entity
  • @Table(name="Employee")
    */
    class Employee extends Person
    {
    The schema for the Employee table looks like so:

CREATE TABLE Employee (
id INT NOT NULL,
department VARCHAR(50) NOT NULL,
PRIMARY KEY(id)
) ENGINE = InnoDB;
ALTER TABLE Employee ADD FOREIGN KEY (id) REFERENCES dbOne.Person(id) ON DELETE CASCADE

where the reference table name is prefixed with the database name.

The main problem is that when I call

$this->entityManager->flush();

On Person object, it flags an error:

Message: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'dbOne.Employee' doesn't exist . Apparently, the expectation is for the base class to persist its instance and be able to automatically persist the sub class as well. This problem is absent when the tables are in the same database- which indicates that the CTI structure expects the entity tables to be within the same database. The reverse is true if I call the entity manager (for dbTwo) to save an Employee object.

Is there a workaround for this bug/feature?



 Comments   
Comment by Ire Ogunsina [ 07/Apr/11 ]

It appears that ORM technologies are limited in their ability to generate SQL queries that interrogate multiple databases in a single SQL statement (unit of work). This largely may be due to the fact that the entity manager is restricted to one database. This limitation may be responsible for this behavior.

Comment by Benjamin Eberlei [ 01/May/11 ]

Yes, the EntityManager is focused on one database, not multiple.

There are two workarounds:

1. @Table(name="dbOne.foo")
2. Create a View in the database.

Generated at Wed Nov 26 20:25:43 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.