DDC-352: JOINED inheritance doesn't remove child object

[doctrinebot]

Jira issue originally created by user cloun:

I have a two class with joined inheritance:

/****
 * @Entity(repositoryClass="Repository_Picture")
 * @Table(name="picture_ordered")
 * @InheritanceType("JOINED")
 * @DiscriminatorColumn(name="type", type="string")
 * @DiscriminatorMap({"url" = "Picture", "file" = "PictureFile"})
 */
class Picture extends Database_Entity {...}

/****
 * @Entity
 * @Table(name="picture_file")
 */
class PictureFile extends Picture {...}

I created PictureFile instance, added it to the Repository_Picture, flushed em. All worked fine. I saw in database one rows in the picture_ordered table and one in the picture_file table.
After that I tried to remove picture from Repository_Picture, and took the error: Integrity constraint violation
#0 E:...\library\Doctrine\DBAL\Connection.php(627): PDOStatement->execute(Array)
#1 E:...\library\Doctrine\DBAL\Connection.php(388): Doctrine\DBAL\Connection->executeUpdate('DELETE FROM pic...', Array)
#2 E:...\library\Doctrine\ORM\Persisters\JoinedSubclassPersister.php(283): Doctrine\DBAL\Connection->delete('picture_ordered', Array)
#3 E:...\library\Doctrine\ORM\UnitOfWork.php(785): Doctrine\ORM\Persisters\JoinedSubclassPersister->delete(Object(PictureFile))
#4 E:...\library\Doctrine\ORM\UnitOfWork.php(312): Doctrine\ORM\UnitOfWork->_executeDeletions(Object(Doctrine\ORM\Mapping\ClassMetadata))
#5 E:...\library\Doctrine\ORM\EntityManager.php(280): Doctrine\ORM\UnitOfWork->commit()
#6 E:...\application\models\Database.php(93): Doctrine\ORM\EntityManager->flush()
#7 E:...\tests\maksidom\PictureTest.php(28): Database::flush()

I think that problem is that Doctrine doesn't remove child (PictrueFile) object before removing parent (Pictrue) object.

[doctrinebot]

Comment created by romanb:

Did you create the tables manually or through Doctrine? Does the picture_file table have a proper foreign key constraint to the parent table?

What does "SHOW CREATE TABLE picture_file" give you?

[doctrinebot]

Comment created by cloun:

Here is a full error message: SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (picture*file, CONSTRAINT picture_file_picture FOREIGN KEY (picture_id) REFERENCES picture (picture*id))

Strange question.. what is a 'proper foreign key'? If I take this error, it means, that table has restricted foreign key. All of FK in my DB is restricted, because cascade deletion is a BL logic (imho).
Here is DDL:

CREATE TABLE  `picture` (
  `picture*id` int(11) NOT NULL AUTO*INCREMENT,
  `type` varchar(255) NOT NULL COMMENT 'FILE or URL',
  `url` varchar(255) DEFAULT NULL,
  `serial` int(11) NOT NULL,
  `alternate` varchar(1024) DEFAULT NULL COMMENT 'alternate text',
  PRIMARY KEY (`picture_id`),
  KEY `picture*product` (`product*id`),
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `picture_file`;
CREATE TABLE  `picture_file` (
  `picture_id` int(11) NOT NULL,
  `source*file*id` int(11) NOT NULL,
  `small*file*id` int(11) DEFAULT NULL,
  `width` int(11) NOT NULL,
  `height` int(11) NOT NULL,
  `extension` varchar(30) NOT NULL COMMENT 'image file extension (jpeg, png, bmp etc)',
  PRIMARY KEY (`picture_id`),
  KEY `picture*file_source_file_id` (`source_file*id`),
  KEY `picture*file_small_file_id` (`small_file*id`),
  KEY `picture*file_picture` (`picture*id`),
  CONSTRAINT `picture*file_source_file_id` FOREIGN KEY (`source_file_id`) REFERENCES `file` (`file*id`),
  CONSTRAINT `picture*file_small_file_id` FOREIGN KEY (`small_file_id`) REFERENCES `file` (`file*id`),
  CONSTRAINT `picture*file_picture` FOREIGN KEY (`picture_id`) REFERENCES `picture` (`picture*id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[doctrinebot]

Comment created by cloun:

I have found out what the root of the problem. Doctrine checks whether database supported cascade deletion or not, and if it supported, then it removes only root entity.. May be with inheritance in database the principle 'restrict FK's' is not fit. I agree with your, constraint is not proper =)

But also I found another issue: if PK of child table will be not equal with PK of parent table, I think, this code will not work properly:

    public function delete($entity)
    {
        $id = array_combine(
            $this->_class->getIdentifierColumnNames(),
            $this->_em->getUnitOfWork()->getEntityIdentifier($entity)
        );
...
        if ($this->_conn->getDatabasePlatform()->supportsForeignKeyConstraints()) {
            $this->*conn->delete($this->_em->getClassMetadata($this->*class->rootEntityName)
                    ->primaryTable['name'], $id);
        } else {
...
        }
    }

because $id contains PK of child table, but deletion executes from parent table.

[doctrinebot]

Comment created by cloun:

Perhaps it would be useful to document this specific requirement (I tell about constraint with cascade deletion)?

[doctrinebot]

Comment created by romanb:

Yes this should probably be better documented.

One question though: Did you create the schema manually or through Doctrine? Because if it was through Doctrine there might be a bug. Doctrine should create the proper foreign keys with ON DELETE CASCADE.

As you correctly found out, in the presence of foreign key constraints Doctrine only deletes the root row and lets the database delete cascade do the rest. This is the most efficient.

If you create the database schema not through Doctrine, you must ensure that the foreign keys are properly set up with ON DELETE CASCADE. That should be added to the documentation.

[doctrinebot]

Comment created by cloun:

Not, I created schema manually (through design toolkit).

[doctrinebot]

Comment created by @beberlei:

Is there are foreign key on the inheritance tables that has a ON DELETE CASCADE property? If not there is the issue :-)

[doctrinebot]

Comment created by romanb:

@benjamin: You can see in an earlier comment from Valery that ON DELETE CASCADE is indeed missing.

This is a documentation issue then.

[doctrinebot]

Comment created by @beberlei:

Documented and closed

[doctrinebot]

Issue was closed with resolution "Fixed"