Deleting files and corresponding entries from database

Question

I have a web site which handles files management. User can upload file, add description, edit and delete. What are the best practices for that kind of scenario?

1. I store files in the file system.

2. How should I handle deleting of the file? In this case I have to entities to delete: file and entry in database. First scenario is that I delete file and if there was no error I delete the entry from database. But then if the entry from database couldn't be deleted I cannot restore my file. So the second scenario is oposite: first entry from the database and then file. But again, when file cannot be deleted I cannot restore the entry in db. Which approach is better? Or is there any other?

I think the problem is universal for all web programming languages and all databases engines. But let's say that I have MySQL and PHP, so deleting the file from the level of database store procedure is not possible.

Answer 1

I usually find it best to go with doing soft deletes, especially for data in the database.

Doing it this way you could simply put the file in a new location to denote it as being deleted, and mark the entry in the database as being removed. This then allows you to still work with the file if the database delete fails for some reason.

Once you have the file in a new location, you can either backup that location to another place or set something up to periodically delete items from that location.

Answer 2

I would personally make sure the database gets the priority as it is more fundamental to the system. So i'd make sure the db row gets deleted, then delete the file. If the file deletion fails, i'd make it fail silently. I would then have a cronjob checking against all files if they have their db counterpart, and if not, marked them for deletion, so the system stays clean and coherent.

Answer 3

There is often a garbage collection phase, and in fact your database has something similar called the "transaction log" which it can use to rewind or play forward a transaction.

In the case of your file delete you will have a clean-up process that runs periodically (perhaps manually in the event of a crash, or automatically every so often) that compares what is on the disk with what is in the database and makes an appropriate correction.

In order for any operation to be "atomic" there must be a method of cleaning up in the event of a crash. The key is finding a method that cleans up consistently such that a failure at any point within the "atomic" operation doesn't leave the system unrecoverable.

Answer 4

Here is one possibility.

You could first move the file to a 'Deleted' folder, then delete the entry in the database. If that fails, restore the file from the 'Deleted' folder.

To get rid of the files in the 'Deleted' folder, either do it right after the entry from the database is deleted. If that fails, then you end up you orphan files in a 'Deleted' folder... Depending on you requirement, this might not be a problem.

Another option would be to have a call (maybe on SessionEnd, or a service) that would do the clean up of the database.

Answer 5

Vista introduced Transactional NTFS that will allow you to wrap file system operations along with database operations into a transaction that can be rolled back if either failed. This is not in .Net 3.5 and I do not know if it will be part of .Net 4.0, but there is evidence that it works today with a bit of leg work, i.e. by using Win32 calls (see this article).

I know nothing about transaction management in PHP.