Description

$objects->find(1)->current()->delete() it deletes domain1 and section1 and not item1.
Probably issue is in _cascadeDelete method that deletes rows in dependent table using table->delete($where), not by selecting a rowset and calling ->delete for each row.

In DBMS cascade operations affect all depended tables and their subtables.

Comments

Posted by Bill Karwin (bkarwin) on 2007-03-22T12:22:26.000+0000

Assign to Bill Karwin.

Posted by Pelle Wessman (voxpelli) on 2007-09-24T15:15:04.000+0000

Came across this bug also when I removed a parent of another table which in turn was the parent of a third table from which the affected rows wasn't removed.

On row 887 of the Zend_Table_Abstract in version 1.0.1 within the function _cascadeDelete a row is constructed like this:

By the way - shouldn't this be flagged as a bug rather than an improvement?

Posted by Pelle Wessman (voxpelli) on 2007-09-26T04:03:22.000+0000

Another effect of the current way of deleting rows in depending tables is that it bypasses eventual pre- and post-operations that may have been added to those tables row-classes. Is that an issue perhaps?

Posted by Wil Sinclair (wil) on 2008-03-25T20:43:59.000+0000

Please categorize/fix as needed.

Posted by Wil Sinclair (wil) on 2008-04-18T13:11:49.000+0000

This doesn't appear to have been fixed in 1.5.0. Please update if this is not correct.

Posted by Rob Howard (rhoward) on 2008-07-22T17:34:51.000+0000

Seconding Pelle Wessman's comment regarding the status of this issue; this issue should be marked as a Bug, as the current method of cascading results in unexpected behaviour (ie. it behaves in a manner contrary to every other "cascade" operation I can think of, RDBMS or otherwise).

Posted by Jaka Jancar (jaka) on 2008-11-21T01:53:58.000+0000

This indeed is a bug. I was 100% sure that it works like normal cascades in RDBMSs do, until reading it.

Cascades on the PHP level are also useful if you're using triggers in MySQL, because native MySQL cascades don't trigger them on cascaded changes.

Posted by Wil Sinclair (wil) on 2008-12-04T13:17:30.000+0000

Reassigning to Ralph since he's the new maintainer of Zend_Db

Posted by Ralph Schindler (ralph) on 2009-01-09T13:34:06.000+0000

Ill attempt to confirm this with unit tests within 2 weeks.

Posted by Frank Groeneveld (frenkel) on 2009-04-17T23:54:30.000+0000

Ralph Schindler, did you confirm this?

Posted by Ralph Schindler (ralph) on 2009-07-17T15:32:48.000+0000

Not confirmed yet. But just a thought: what is to prevent circular references from being an issue?

Posted by Konr Ness (konrness) on 2009-09-02T11:52:53.000+0000

Do you need more information to resolve this issue?

I'd be happy to provide some test cases or anything else you may need. I believe this to be a rather serious issue with Zend_Db_Table.

Posted by Ralph Schindler (ralph) on 2009-09-17T16:41:39.000+0000

Yes, can you provide a simple test case that demonstrates the problem? Do you know if it can be demonstrated with the existing database in the unit tests?

Posted by John Cokos (jcokos) on 2009-10-14T09:13:30.000+0000

I have the same issue. Below is an example of how you can replicate it ...

Test Case

Create an object of TestTable, grab one row (find by rowid) and then call $row->delete()

It will delete the record from testtable, testtable_sub and testtable_sibling (both are immediate children of testttable), but any records in testtable_sub_sub, which is a child of testtable_sub are not deleted as you would expect them to be, following the deletion of their parent rows from testtable_sub by the cascade operation.

If you set these same tables up in mySQL as INNODB, using Foreign Keys and cascading deletes, everything happens as it should when you delete the parent record ... would be nice to have reliable functionality at the Zend Level as well for those cases where you cannot rely on the DB to think for you.

HTH, John

Posted by Ralph Schindler (ralph) on 2009-12-30T20:56:51.000+0000

Attachment is against trunk.

To use this feature, set the 'onDelete' key of any reference map element to self::CASCADE_RECURSE instead of self::CASCADE.

Posted by Konr Ness (konrness) on 2010-01-01T19:27:54.000+0000

Changing the _getPrimaryKey() function in Zend_Db_Table_Row_Abstract to have public scope instead of protected is problematic because the Zend coding standard's naming convention does not allow an underscore to preceed a public method's name.

Perhaps you change the _getPrimaryKey() back to protected and add a public getPrimaryKey() that calls the protected function?

Posted by Ralph Schindler (ralph) on 2010-01-02T12:05:26.000+0000

I can do that, yes. We do have some places in ZF that have public underscored methods, they are mainly there as a way to "enforce" or demonstrate that they are to be used internally only. Imagine if PHP had class level (or namespace visibility).

But I agree with you, and I can change this. I guess it's not a big deal if getPrimaryKey() is visibly available to consumers of the class.

How does it work for you though? :)

-ralph

Posted by Konr Ness (konrness) on 2010-01-02T22:48:17.000+0000

In my initial tests, the recursive deletes are working on my test data.

Two Minor Changes:
1) There are three typos in the comments, both with the spelling of CASCADE. You have spelled it CASECADE.
2) The method comment for _cascadeDelete says "Called by parent table's class during delete() method.", but it should actually be something along the lines of:
Called by table row during delete() method, and the parent table's class during _cascadeDelete() method for recursive cascading deletes.

I did have one possibly unrelated question/issue. I tried creating a table relationship where the refColumn defined in the child table's referenceMap is not one of the parent table's primary keys. A cascading and/or cascadingRecursive delete does not work because when the table's _cascadeDelete() method is called, only the primary keys are passed as the second parameter. I'm wondering why only the primary keys were sent and not all of the data. This would allow the following scenario:

The reason I organized the data like this is to allow several different entities (profiles, albums, users, groups, etc.) to store their photo in one table. The cascading deletes, however, do not work because the photoId column from the Photos table doesn't exist in the _cascadeDelete method because only the profile's ID column is passed.

I know this is off-topic for the recursive cascading deletes, but perhaps this new capability could be added simultaneously.

Attached fix appears to work as expected. After applying the patch, change the onDelete parameter value in the file {{tests/Zend/Db/Table/_files/My/ZendDbTable/TableCascadeRecursive.php}} from {{self::CASCADE}} to {{self::CASCADE_RECURSE}} and the tests will pass.

A similar fix and unit test suite will need to be implemented for {{onUpdate}} as well.