How to clean up referenced files after a cascaded delete in an RDMS?

Question

Zend_Db contains methods to cascade deletes and updates much like the way that most RDMS's will. The documentation states that if you are using a RDMS which supports forign keys that you should use that in place of the support in Zend_Db. The reasons for this are obvious; Zend_Db issues separate db queries for each delete/update so you lose both performance and (depending on transactional concurrency settings) atomicity.

What then is the best option given the following scenario:

A user has multiple albums, an album contains multiple photos. Each photo, album and user has a single row in equivilent db tables, however each photo is also stored on a CDN. If a user is deleted, the database cascade deletes their albums and photos. However the photos remain on the CDN and need to be removed.

Answer 1

maybe it would be possible to overwrite default cascade delete method with my own model's delete method but it souds like a lot of work so i've made a workaround for my project.

i have 'zadania' table and 'zadania_images' table. the second table has 'zadanie_id' column which relates the two tables.

'zadanie_images' rows contain filenames and after cascade delete of parent 'zadanie_id' the files were still there. here's my solution:

in 'Zadanie' model:

public function deleteZadanie($id) {

    $row = $this->find($id)->current();
    if($row) {
        $image = new Model_ZadanieImage();
        $image->deleteRelatedFiles($id);
        $row->delete();
    }
}

in 'ZadanieImage' model:

public function deleteImage($id) {

    $row = $this->find($id)->current();

    if($row) {
        //delete the record
        $filename = $row['name'];
        $row->delete();
        //and delete related files

        unlink('images/zadanie/' . $filename);
        unlink('images/zadanie/thumbs/' . $filename);
    } 
}


public function deleteRelatedFiles($zadanie_id) {

    $select = $this->select()->where('zadanie=?', $zadanie_id);
    $images = $this->fetchAll($select);
    foreach($images as $image) {
        $this->deleteImage($image->id);
    }
}