Lost database -- Mass Import -- Album names as caption

d.dub

Joined: 2007-12-06
Posts: 7
Posted: Thu, 2008-03-27 17:46

We recently migrated from one domain/server to another and in the process, our internet host lost the G2 database. The pictures are all still intact and organized into albums and sub-albums. Our photo captions were generic and based on the year and event, which is also the name of each folder/album. All photos in a particular album had the name of the folder. In a few instances (perhaps 25 photos out of 900), we included more information in the caption. These 25 I'm happy to do by hand.

To get ourselves back up as quickly as possible, I plan on following the directions in the third bullet of the FAQ, "How can I export data from one G2 to another G2 installation?"

However, is there a way to expand on this and, during the import process, include the folder/album name as the caption? If that's not possible, is there a way, after the import, to do a mass change to turn the caption into the folder/album name?

Thanks

d.dub/
Login or register to post comments
beckett
beckett's picture

Joined: 2002-08-16
Posts: 3469
Posted: Fri, 2008-03-28 02:50

That's a bummer. :(

It sounds to me like the simplest thing to do would be to do a little SQL querying after the reupload, and issue some batch SQL updates right to the database. Are you okay with issuing batch SQL queries and writing a small script?

To fetch the album ids ==> pathnames (except for the root album):

SELECT g2_AlbumItem.g_id, g2_FileSystemEntity.g_pathComponent
FROM   g2_AlbumItem JOIN g2_FileSystemEntity USING(g_id)
WHERE g_pathComponent != "NULL";
Then loop over the results: 
SELECT g2_ChildEntity.g_id FROM g2_AlbumItem
     JOIN g2_ChildEntity ON(g2_AlbumItem.g_id=g2_ChildEntity.g_parentId)
WHERE g2_AlbumItem.g_id = (id from above);
Then loop over all of those children: 
UPDATE g2_Entity
SET g_summary (or g_description or g_title) = (name you've selected)
WHERE g_id = (child id from above);

You could write a quick script to fetch these, then batch out all the updates.

With that done, then you'll want to clear your database cache. There's probably a cleaner way to construct the queries, but the above queries are basically all you'd need to run. Is that too vague an answer?
Login or register to post comments
d.dub

Joined: 2007-12-06
Posts: 7
Posted: Fri, 2008-03-28 16:01

Thanks Beckett -- I'll give this a try over the weekend.

d.dub/
Login or register to post comments