DDC-732: Class Table Inheritance - wrong table order on insert with more than one level of inheritance #5245

Closed
doctrinebot opened this Issue Aug 6, 2010 · 14 comments

2 participants

@doctrinebot

Jira issue originally created by user vigor_bg:

This is the error that i get when i try to create new entity Employee

$newEmployee = new Users*Entity_Model*Employee;
//here i set the data
.....

$em->persist($newEmployee);
$em->flush();

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (crm/Employees, CONSTRAINT employee_person FOREIGN KEY (contactID) REFERENCES Persons (contactID) ON DELETE NO ACTION ON UPDATE NO ACTION)

/****
 *
 * @Entity
 * @InheritanceType("JOINED")
 * @Table(name="Contacts")
 * @DiscriminatorColumn(name="contactType", type="string")
 * @DiscriminatorMap({"PERSON" = "Users*Model_Entity_Person", "COMPANY" = "Users_Model_Entity_Company", "EMPLOYEE" = "Users_Model_Entity_Employee", "FIRM" = "Users_Model_Entity*Firm"})
 */
class Users*Model_Entity*Contacts 
{


    /****
     *  @Id  @Column(name="contactID", type="integer")
     *  @GeneratedValue(strategy="AUTO")
     *
     */
    protected $contactID;

    // ...

}

/****
 *
 * @Table(name="Persons")
 * @entity(repositoryClass="Users*Model*PersonRepo")


 */
class Users*Model_Entity_Person extends Users_Model_Entity*Contacts
{
 // ...
}




/****
 *
 * @Table(name="Employees")
 * @entity
 */
class Users*Model_Entity_Employee extends Users_Model_Entity*Person
{
    // .....
}
@doctrinebot

Comment created by vigor_bg:

I managed to fix it but i am not sure that this is the wright solution. The problem is in the
Doctrine\ORM\Persisters\JoinedSubclassPersister in the function executeInserts()

if ( ! $this->_queuedInserts) {
            return;
        }

        if ($this->_class->isVersioned) {
            $versionedClass = $this->_getVersionedClassMetadata();
        }

        $postInsertIds = array();
        $idGen = $this->_class->idGenerator;
        $isPostInsertId = $idGen->isPostInsertGenerator();

        // Prepare statement for the root table
        $rootClass = $this->*class->name == $this->*class->rootEntityName ?
                $this->*class : $this->_em->getClassMetadata($this->*class->rootEntityName);
        $rootPersister = $this->_em->getUnitOfWork()->getEntityPersister($rootClass->name);
        $rootTableName = $rootClass->table['name'];
        $rootTableStmt = $this->*conn->prepare($rootPersister->*getInsertSQL());

        // Prepare statements for sub tables.
        $subTableStmts = array();
        if ($rootClass !== $this->_class) {
            $subTableStmts[$this->*class->table['name']] = $this->_conn->prepare($this->*getInsertSQL());
        }
        foreach ($this->_class->parentClasses as $parentClassName) {
            $parentClass = $this->_em->getClassMetadata($parentClassName);
            $parentTableName = $parentClass->table['name'];
            if ($parentClass !== $rootClass) {
                $parentPersister = $this->_em->getUnitOfWork()->getEntityPersister($parentClassName);
                $subTableStmts[$parentTableName] = $this->*conn->prepare($parentPersister->*getInsertSQL());
            }
        }


        // Execute all inserts. For each entity:
        // 1) Insert on root table
        // 2) Insert on sub tables
        foreach ($this->_queuedInserts as $entity) {
            $insertData = $this->_prepareInsertData($entity);

            // Execute insert on root table
            $paramIndex = 1;
            foreach ($insertData[$rootTableName] as $columnName => $value) {
                $rootTableStmt->bindValue($paramIndex<ins></ins>, $value, $this->_columnTypes[$columnName]);
            }
            $rootTableStmt->execute();

            if ($isPostInsertId) {
                $id = $idGen->generate($this->_em, $entity);
                $postInsertIds[$id] = $entity;
            } else {
                $id = $this->_em->getUnitOfWork()->getEntityIdentifier($entity);
            }

            /****
             * Here is the fix that i made
             */
           $subTableStmts = array_reverse($subTableStmts);
            /****
             * end 
             */

            // Execute inserts on subtables.
            // The order doesn't matter because all child tables link to the root table via FK.
            foreach ($subTableStmts as $tableName => $stmt) {
                $data = isset($insertData[$tableName]) ? $insertData[$tableName] : array();
                $paramIndex = 1;
                foreach ((array) $id as $idVal) {
                    $stmt->bindValue($paramIndex<ins></ins>, $idVal);
                }
                foreach ($data as $columnName => $value) {
                    $stmt->bindValue($paramIndex<ins></ins>, $value, $this->_columnTypes[$columnName]);
                }
                $stmt->execute();
            }
        }

        $rootTableStmt->closeCursor();
        foreach ($subTableStmts as $stmt) {
            $stmt->closeCursor();
        }

        if (isset($versionedClass)) {
            $this->_assignDefaultVersionValue($versionedClass, $entity, $id);
        }

        $this->_queuedInserts = array();

        return $postInsertIds;
    }

the order of the queue for insert was not correct.

@doctrinebot

Comment created by @beberlei:

thanks for the report, but posting large bunches of changed code isnt very helpful. You should post patches/diffs instead using "git diff" or "diff" against the original file.

I checked your patch and it cannot be right, you check the reverse inside the loop each time. Without a failing test-case i cant do anything here, i will look into it in more detail later.

@doctrinebot

Comment created by vigor_bg:

Hi there sorry for that you are wright the reverse should be before the foreach. I will prepare the test- case and post it a bit later.

@doctrinebot

Comment created by vigor_bg:

This is the database and the test case for inserting data and we have Class Table Inheritance. the problematic ones are public function testInsertInheritedTwo(){ } and public function testInsertInheritedThree(){ }

@doctrinebot

Comment created by vigor_bg:

Also i just updated to BETA3 and found one more problem in /Doctrine/DBAL/Statement.php in the 'function execute' on line 126. $this->_params = array(); This bit of code is braking the insert statements. Because it clears the currently bound parameters. Hope that is all the info you need if there is more information that is required please let me know.

@doctrinebot

Comment created by @beberlei:

What database and driver are you using? All three tests pass for me

@doctrinebot

Comment created by @beberlei:

can you please use the SQL generated from Doctrine Schema-Tool and try again if this issue occurs to you? You have generated your own SQL, this might be the issue.

@doctrinebot

Comment created by vigor_bg:

Hi there,

I will try with the sql generated by Doctrine. But the problem I got was when I have more than 2 levels of the inheritance hierarchy. When try to make an insert trough the lowest level (in my example the DDC732Driver. Actually it was not working with the DDC732Employee entity as well) the order of inserting was not correct. First it went trough the correct entity (in my example the DDC732Contacts), but then instead of going through DDC732Person it try to go trough the last level (DDC732Driver or DDC732Employee depending on the example). And then the MySql said that it can not go trough. Also maybe the reason is that I am using InnoDB, but then it shouldn't really matter because I thought that the hierarchy inheritance is supposed to work with it. Actually I am almost 100% sure the problem is in the InnoDB could you please try with it. If it is still working for you i will reexamine my application and find the mistake.

Thanks in advance for the help and sorry. Now on second thought I should have explained all that in the beginning.

@doctrinebot

Comment created by @beberlei:

Doctrine2 only creates InnoDB tables. Are you using foreign keys in a different way?

@doctrinebot

Comment created by @beberlei:

Closed because we can't reproduce this. Schema of the user differs from the ORM SchemaTool and causes this issue.

@doctrinebot

Comment created by vigor_bg:

Hi there i am reopening this ticket, because i found problem with the logic of creating inherited join tables from your create schema tool. I was expecting when we have more than one level of inherited tables the foreign key constrain to go to the parent and not to the root table. And because of that with the sql generated from your tool it was ok and it was not working with my sql. I would like to know are you going to fix that or it was intended to work that way from the beginning? Because logically for me and my colleagues if Employee extends Person then the foreign key constrain in Employee should go to Person and at the same time Person extends Contact then foreign key constrain in Person should go to Contact. And with your schema tool in that instance the foreign key constrain in Employee go to Contact which is wrong.

@doctrinebot

Comment created by @beberlei:

This is a documentation issue.

I have now documented the SQL schema requirements for inheritance scenarios.

Closing this issue.

@doctrinebot

Issue was closed with resolution "Fixed"

@beberlei beberlei was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot added this to the 2.0-RC1 milestone Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
@doctrinebot doctrinebot added the Bug label Dec 7, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment