Trevor's IT Blog

Problem Introduction

I use an open-source solution for hosting my family’s photos, called Gallery. Recently, my gallery2 install crashed, and I lost my online database. “No big deal”, I thought. “I have all the pictures saved on my local computer.” WRONG-O!!! I hit two major snags: One, gallery can stumble very easily while doing a bulk upload from a local server. Two, all of the album dates were stamped with the time of my recent upload, which destroyed by chronological sorting of the albums.

I started manually “editing” each album through Gallery2’s web interface, but that gets old real fast, so I decided to try a little MySQL wizardry.

Solution

Gallery2 stores most of its data, except for the photos and movies, in a MySQL database on the web-server. Of course, you can use other back-ends, but MySQL is very popular. The database can be manipulated using your favorite MySQL monitor, whether that occurs through a command shell or a web-interface, like phpMyAdmin.

My solution was to use MySQL to search through each album, find the oldest picture, and update the album’s origination date to match the oldest picture.

Using my favorite MySQL interface, I crafted the following SQL statement to examine the problem:

SELECT g_id AS albumId, g_title, g_originationTimestamp, (
SELECT MIN(g_originationTimestamp)
FROM g2_Item
INNER JOIN g2_ItemAttributesMap
 
ON g2_Item.g_id = g2_ItemAttributesMap.g_itemId
WHERE g_parentSequence LIKE CONCAT( '%/', albumId, '/' )
) AS oldestPicture
FROM g2_Item
INNER JOIN g2_ItemAttributesMap ON g2_Item.g_id = g2_ItemAttributesMap.g_itemId
WHERE g_canContainChildren > 0

This produced results like:

As you can see, many of the albums had pictures much older than the timestamp on the folder. The theory appears sound. Let’s experiment!

Attempt #1

Since we are experimenting on your gallery database, obviously you want to back it up first. If I have to tell you that, you are in over your head. 😉

Using a modified version of the above query, I tried to update the origination timestamp to the value of the oldest picture, like so:

UPDATE g2_Item AS albumId SET g_originationTimestamp =
(
SELECT MIN(g_originationTimestamp)
FROM g2_Item INNER JOIN g2_ItemAttributesMap
ON g2_Item.g_id=g2_ItemAttributesMap.g_itemId
WHERE g_parentSequence LIKE CONCAT('%/', albumId.g_id, '/')
) WHERE g_canContainChildren > 0

Unfortunately, this produces the following error:

ERROR #1093 - You can't specify target table 'albumId' for update in FROM clause

Apparently, UPDATE will not allow you to modify a table that is part of the query. That seems reasonable. However, that is exactly what I needed to do. This forced me to create a temporary table to hold the intermediate results, and then use those results to update the desired table.

Attempt #2

Here was my final solution, which required 4 separate statements:

DROP TABLE IF EXISTS newAlbumTimes;

This first statement is only necessary, if you iterate and experiment with this approach. It deletes the temporary table, if it exists, which may happen after you tweak something and try again, depending on your connection method.

CREATE TEMPORARY TABLE newAlbumTimes (albumId INT(11), albumTitle VARCHAR(128), albumTimeStamp INT(11), oldestPicture INT(11))
SELECT g_id AS albumId, g_title AS albumTitle, g_originationTimestamp AS albumTimeStamp, (
SELECT MIN(g_originationTimestamp )
FROM g2_Item
INNER JOIN g2_ItemAttributesMap
 
ON g2_Item.g_id = g2_ItemAttributesMap.g_itemId
WHERE g_parentSequence LIKE CONCAT( '%/', albumId, '/' )
) AS oldestPicture
FROM g2_Item
INNER JOIN g2_ItemAttributesMap ON g2_Item.g_id = g2_ItemAttributesMap.g_itemId
WHERE g_canContainChildren >0
ORDER BY oldestPicture;

Now that is the “brains” of the operation. First, notice the select statement, very similar to the original query. However, these results are being fed into a “CREATE TEMPORARY TABLE” statement, which catches the results. Notice, we had to tell MySQL the structure of this temporary table, which should generally match the structure of the output columns. This temporary table will be destroyed when the connection closes. However, we will use this table in the meantime, to update the timestamps based on the oldest picture’s timestamp.

UPDATE g2_Item INNER JOIN newAlbumTimes
ON g2_Item.g_id = newAlbumTimes.albumId
SET g_originationTimestamp=oldestPicture
WHERE oldestPicture AND oldestPicture < albumTimeStamp;

Using the temporary table, the origination time is updated for all albums in the item table, but only if the oldest picture column is non-NULL and if the picture is stamped older than the album. Otherwise, we assume the time stamp for the album is better than what we have calculated, so we leave it alone.

UPDATE g2_Entity INNER JOIN newAlbumTimes
ON g2_Entity.g_id = newAlbumTimes.albumId
SET g_creationTimestamp=oldestPicture, g_modificationTimestamp=oldestPicture
WHERE oldestPicture AND oldestPicture < g_creationTimeStamp;

We also have to update the creation time stamps, which is the date actually displayed and permitted to be edited for the album. This is the statement that does what we want!

Conclusion

Running the above 4 statements in order produces the desired affect.  Preferably, these should be executed as a single entry to avoid the temporary table disappearing. However, this procedure only “bubbles up” the oldest time stamp by one level.  Most albums will be updated correctly after this.  However, if you have nested albums (albums inside albums), you will have to run this procedure at least once for each level of album (or folder) hierarchy.  This will cause your top level album to show a creation date equal to or older than the oldest picture anywhere in its hierarchy.

Notes

If you are running an older version (Gallery1) or newer version (Gallery3, currently in early development), you will obviously have to modify the above statements to match the your database structure. Also, these statements were executed using MySQL 5.0.70. If you use an older version, like MySQL 4.0, you may find the nested queries do not work. This will require you to create an additional temporary table to cache those intermediate results – much more complicated.  It may be better to upgrade, if you can.

You can include additional constraints on the WHERE clause of the final two statements to only modify certain albums.  This can be useful if you are only want to fix a certain subset of albums in your gallery.