Re: [fw-db] Zend_Db_Table Issue

Good thought Hector, but I do acknowledge the value to clean up
promptly, if not online during a PHP request. I wouldn't let it go a
month.

Another option is that as you delete database rows that reference
associated files, add the filenames to a queue table of
"files_to_delete". If you roll back, these rows are un-inserted, so
it supports ACID.

Then write a script to be invoked by cron, which processes the queue
and removes both the files and the rows from the queue table.

Usually I fall into the camp of "the database is not a queue," but
it's more likely to work if you only have one client (the cron script)
reading this queue.

Regards,
Bill Karwin

On Feb 22, 2011, at 12:17 PM, Hector Virgen wrote:

> Another way to look at it: space is cheap, your time is not. I
> wouldn't
> worry too much about cascading deletes leaving dangling files/rows.
> You may
> be trying to solve a problem that really isn't a problem in the
> first place.
>
> But if you do find that your servers are running out of disk space
> or your
> indexes are becoming too large, write a cron script that does clean
> up once
> a month.
>
> --
> *Hector Virgen*
> Sr. Web Developer
> http://www.virgentech.com
>
>
>
> On Tue, Feb 22, 2011 at 12:11 PM, Bill Karwin <bill@karwin.com> wrote:
>
>>
>> On Feb 22, 2011, at 8:23 AM, Simone Cosci wrote:
>>
>> Il 22/02/2011 0.44, Bill Karwin ha scritto:
>>>
>>>> I coded the cascading functionality in Zend_Db_Table. I don't
>>>> recommend
>>>> using it for any RDBMS, whether the RDBMS supports DRI or not.
>>>>
>>> How can a RDBMS cascading delete unlink files on the filesystem (eg.
>>> images of a product) ?
>>>
>>
>> The correct solution is to unlink the files using application code,
>> but not
>> in a handler for the delete() method of the ORM.
>>
>> What if you unlink files as part of the delete() method, but then
>> you roll
>> back the transaction? You don't get the files back.
>>
>> You should unlink files *only* after you can confirm that the
>> corresponding
>> SQL delete operations have succeeded, and you have committed the
>> transaction
>> for those SQL operations.
>>
>> An alternative is to store files in BLOB columns in the database,
>> instead
>> of on the filesystem. Then you can use DRI to cascade deletes to
>> them, and
>> you can roll back deletes if necessary. It may make your database
>> larger,
>> and make it less convenient to access the files. But you asked for a
>> solution of cascading deletes to files. Storing files in BLOBs
>> works for
>> this requirement.
>>
>> Regards,
>> Bill Karwin
>>
>>