I have a table that stores additional data pertaining to files/images saved in files elsewhere. Each entry can be associated with another type in the system.
For users there may be one or more entries depending on how many files have been uploaded.
For depots, deliveries the same applies, a stripped down version of the table looks like this:
CREATE TABLE IF NOT EXISTS `files` ( `file_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '...', `file_company_id` int(11) NOT NULL DEFAULT '0' COMMENT '...', `file_relation` varchar(100) NOT NULL COMMENT '...', `file_relation_id` int(11) NOT NULL COMMENT '...', `file_name` varchar(200) NOT NULL COMMENT '...', `file_mime_type` varchar(150) NOT NULL COMMENT '...', PRIMARY KEY (`file_id`), KEY `file_relation` (`file_relation`), KEY `file_relation_id (`file_relation_id`), KEY `file_name` (`file_name`), KEY `file_mime_type` (`file_mime_type`), KEY `file_company_id` (`file_company_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
To get an individual entry is simple enough to query the file_id, to get a list of all entries for another entry I do something like the following:
User 123 SELECT .... FROM files WHERE file_relation='user' AND file_relation_id=123
When users upload images they are displayed in the UI either in their original form, or a variety of sizes which are registered in the back end:
<img src="path/to/image/1/200" style="width:200px;" />
The above snippet requests image 1 with a width of 200px ... if the size does not exist yet it is created on the fly (if it is a registered size).
The problem I now have is ensuring that only one entry for the new size is created when first inserting.
#when creating new sizes they reference the original file file_id:99, file_relation:'files',file_relation_id:1,file_dimension:200 #for another file - this is valid file_id:100, file_relation:'files',file_relation_id:5,file_dimension:200 #this entry should not have been created because a previous one with the dimension exists file_id:101, file_relation:'files',file_relation_id:1,file_dimension:200
There can be duplicate file_dimension entries for different parent relations, but if another thread is inserting at the same time it should be able to pick up that an entry already exists and not create a new one.
I am not sure how row locking would work for this scenario (when
INSERT is used) or if I even need to worry about it, here is what happens currently:
SELECT * FROM `files` WHERE `file_relation`='files AND `file_relation_id`=1 AND `file_dimension`=200 #if no entry is found INSERT INTO FILES (...,`file_dimension`) VALUES (...,200)
I am quite certain that there are other places in my application where this type of thing also applies, any advice appreciated.