CODEWITHSUNDEEP
phpmysqlimageblob
TheLearner
TheLearner

Reputation: 2863

Issues with storing images in filesystem vs DB

There are several questions with excellent answers on SO regarding the quintessential BLOB vs filesystem question. However, none of them seem to represent my scenario so I'm asking this.

Say there's a social network (a hypothetical scenario, of course) where users are all free to change anyone's profile picture. And each user's profile is stored in a MySQL table with the following schema:

ID [unsigned int, primary]
USERNAME [varchar(20)]
PROFILENAME [varchar(60)]
PROFILEPIC [blob]

Now, here's the thing: What if I want to store profile images as files on the server instead of BLOBs in the db? I can understand there will have to be some kind of naming convention to ensure all files have a unique name that also maps it to the primary key on the table for easy access. So, say, the primary key is the filename for the corresponding image stored on the disk. But in my context there could be simultaneous read/writes, and quite a lot of them. MySQL would typically handle that with no problems since it locks out the row while it's being updated. But how does one handle such situations in a filesystem model?

Upvotes: 1

Views: 211

Answers (1)

zedfoxus
zedfoxus

Reputation: 37039

In your application layer, you could lock the block that does DB transaction and file IO to alleviate concurrency issues (lock example in C#).

Within this block, run your inserts/updates/deletes in a transaction. Follow that with adding/replacing/deleting the photo on disk. Let's write some pseudo-code:

lock (obj) 
{
    connection.StartTransaction();

    connection.PerformAction();
    if failed, return false;

    photoMgmt.PerformAction();
    if failed, return false;

    connection.CommitTransaction();
}

Applying similar technique with PHP; additionally use flock to perform file locking.

In other words, commit to DB after committing to filesystem. If either DB or filesystem operation fails, perform cleansing so no change is saved.

I'd use bigint ID as the primary key and GUID filenames on disk. If users preferred the application to hold the name they provided, I'd create a field called user_filename to store the filename provided by the user, and for all other purposes I'd use the GUID.

Hopefully this will provide some direction.

Upvotes: 1

Related Questions