John Davidson

Message:

I have a script file, that is downloading images for products in my database. The products on which the file is working are stored in a Products table. Each row in the table (ie, each product), may have 1 or more images that needs to be downloaded.

Until now, the downloaded images were stored in a separate table, named ProductImages, where each downloaded image were connected to a single product from the product database via a foreign key. The ProductImages table were storing the path to the downloaded image file, and besides that, some meta information about the image that was downloaded.

We realized, that we have other images also, which needs to be stored in the database, besides images for products, so I am trying to separate the information relating to the images from the ProductImages table, to a separate Images table, which in turn will be connected back to the Product table, via a relational table.

My problem lies in the addition of the downloaded images and their data to the database. To make things faster, rows in the ProductImages table were inserted to the database in batches, with a LOAD DATA LOCAL INFILE call: my script knew and knows which image belongs to which product, so I build a CSV file from the data in the scripts memory, then load that file into the database.

But with the new database structure, I would first need to load the image information in it's separate table (where MariaDB will assign a unique, autoincrementing primary key to each image), then populate the relational table with relations between Products and Images. The problem is that I will not know in advance the primary keys that are beign associated to each inserted image, thus I can not populate the relational table in this way any more.

My only idea was to insert a helper column to the Images table, a unique id generated by my downloader script, which can be used to populate the relations, but this seems a bit dirty to me. Is there any other way in which I can keep using LOAD DATA LOCAL INFILE and populate the relational table also?

---

My Answer:
One possible solution is to use a temporary table to store the image data before inserting it into the Images table. You can then use the temporary table to populate the relational table with the correct associations between products and images.

Here's a step-by-step guide on how you can achieve this:

1. Create a temporary table to store the image data:

sql
CREATE TEMPORARY TABLE temp_images (
id INT AUTO_INCREMENT PRIMARY KEY,
image_path VARCHAR(255),
meta_info VARCHAR(255)
);


2. Use LOAD DATA LOCAL INFILE to populate the temporary table with the image data:

sql
LOAD DATA LOCAL INFILE 'image_data.csv'
INTO TABLE temp_images
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(image_path, meta_info);


3. Insert the image data from the temporary table into the Images table:

sql
INSERT INTO Images (image_path, meta_info)
SELECT image_path, meta_info
FROM temp_images;


4. Populate the relational table with the correct associations between products and images:

Assuming you have a relational table named ProductImages with columns product_id and image_id, you can populate it using the following query:

sql
INSERT INTO ProductImages (product_id, image_id)
SELECT p.id, i.id
FROM Products p
JOIN temp_images i ON p.product_id = i.temp_id;


By following these steps, you can continue to use LOAD DATA LOCAL INFILE to populate the Images table and then populate the relational table with the correct associations between products and images. This approach allows you to maintain the performance benefits of batch inserting while still achieving the desired database structure.