minte9

LearnRemember

Context

Database server crushed Catastrophe strikes your database server, but IT department is prepared. They are making full backup of database every day. During testing a problem appears, all your application images are gone.

Antipattern

Images stored as files Images where stored as files on /var, on the same server with database. Files doesn't obey backup tools. Files doesn't obey transactions. Files doesn't obey sql access privileges. Deleting the path from DB doesn't automaticaly removes the files.

Solution

Store images in database A bug may have multiple screenshots associated with it.

 
CREATE TABLE Screenshots (
    image_id SERIAL NOT NULL,
    bug_id BIGINT UNSIGNED NOT NULL,
    <span class='keyword_code'>screenshot_image BLOB,</span>
    PRIMARY KEY (bug_id, image_id),
    FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);

MySQL provides a data type MEDIUMBLOB (up to 16MB), which is good enough for most images. MySQL has a function LOAD_FILE used to read a file and store it in a BLOB column. Programmers commonly store files externaly, but there are good reasons not to do it. One advantage of using a database is that it helps preserve data integrity. You {may decide}} that it's right to store images in files if the project is not to complicated. The database is much leaner and the backup is faster. Make an informed decision.

 
UPDATE Screenshots
SET <span class='keyword_code'>screenshot_image</span> = LOAD_FILE('images/screenshot1234-1.jpg')
WHERE bug_id = 1234 AND image_id = 1;