3

I recently discovered the wonders of foreign keys and deletion cascading with InnoDB and I love it. It helps me easily delete intricate data structures without worrying about leaving junk behind.

I'm currently storing images for a products database (like you would have on a web-shop) in the following manner:

My products table is what you would expect, it has an id primary column. My product_images table has a product_id column with a foreign key to the products.id column, and an image column of type varchar storing the image file name.

The file names are created by computing the SHA1 hash of the image, then storing in a folder named after the first 2 characters of the hash: 61/6153A6FA0E4880D9B8D0BE4720F78E895265D0A9.jpg. In the database, only 6153A6FA0E4880D9B8D0BE4720F78E895265D0A9.jpg is stored.

Now, if I were to delete a product from the database, the cascading DELETE rule would delete the record in the product_images table, but would leave the file on my server.

Is there a way to automate the deletion of the image file whenever a record in the product_images table is deleted, be it specifically or by cascading? Maybe link some kind of trigger or routine to the deletion operation?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
beeb
  • 1,187
  • 11
  • 32
  • I don't think feature like this exists in MySQL, but would love to hear ways to solve this problem – akuzminsky Jul 03 '16 at 15:11
  • This is yet another reason why I don't use `CASCADE` (or `FOREIGN KEYs`, for that matter), but rather do all the work in the app. – Rick James Jul 09 '16 at 00:36

3 Answers3

5

There is no native functionality built into mysql to perform this. If there were, there would be no end in sight to turning it into feature-creep and a full blown high-level programming language. Bloat-ware, getting away from its core competency.

People have used UDF libraries compiled in C that integrate into mysql either as object files or compiled into the server source. See the sections Adding New Functions to MySQL, and UDF Repository for MySQL. It become like a science fair project, where after you get it to work once, you probably won't want to roll it out to a fleet of other servers.

So what are you alternatives? The safe bets are always to write to another table (outside of your cascade delete hierarchy) the directory/filename combo. And have another mini-system do the housecleaning for you. For instance, twice a day, in Java or c# or python (any language). Those environments have db libraries, and robust file operations. It will be tremendously easier that way anyway.

I do these type of things to extend the functionality of mysql-related activities that are primarily file based or forbidden calls inside of stored procedures and events.

Community
  • 1
  • 1
Drew
  • 24,851
  • 10
  • 43
  • 78
  • Thanks for an in-depth answer. I was hoping there would be some kind of analogue to the TRIGGERs that would allow me to execute a script or interact with the file system. Too bad :) As you said, I guess I could create a trigger that would log the deletion in a separate table, then poll that table periodically to know which files I should delete. – beeb Jul 04 '16 at 18:18
  • The use of UDF's are sketchy. Then how do I easily deploy. You will find much more power and happiness I believe with your code nestled elsewhere. Sorry it was not what you hoped for (at the moment). – Drew Jul 04 '16 at 18:22
  • Check out [cron](http://stackoverflow.com/q/21789148) which is wildly popular for running a task, whatever you put in an appropriate string with a schedule via `crontab`. – Drew Jul 04 '16 at 18:39
0

You would use below code for deleting the specific file.
Note: your file name ($filName) would come from your query.

$filName;
$dirName = substr($filName, 0, 2);

chown($filName,465);
unlink("../".dirName."/" . $filName);
RCode
  • 379
  • 3
  • 12
  • I don't think you understood my request. I know how to delete a file on a server, but I want to know how to automatically perform this deletion if a row is deleted from my table (hence the word cascading). I want to somehow link a script to the mysql deleting operation. – beeb Jul 03 '16 at 10:50
0

when inserting images to DB change it's name to be linked with it's product or it's category . when deleting category search for imaged that have names involve cat id then unlink it

this code can help 

        foreach(glob(IMG_PATH.'cat_'.$_POST['id'].'*.*') as $file)
        {
            if(is_file($file))
            {
                @unlink($file);
            }


    }
Mohammed Habib
  • 102
  • 4