Note to start: If you do anything with regards to the database, you should back it up. At least just make a copy of the package. Or create a new library, and use that one.
[Update]: See updated iPhoto SQL structure page for more recent information for digging around, or here.
Looked more at the SQL database. You can open it at the command line.
% sqlite3 ~/Pictures/iPhoto\ Library/iPhotoMain.db
These are the schema for the information regarding the image files and their location in the file system.
CREATE TABLE SqPhotoInfo (primaryKey INTEGER PRIMARY KEY AUTOINCREMENT, photoDate REAL, isVisible INT, showInLibrary INT, isUserHidden INT, isOpen INT, caption VARCHAR, comments VARCHAR, uid VARCHAR, ranking INT, readOnly INT, faceDetectionFromCached INT, faceDetectionRotationFromOriginal REAL, editState INT, thumbnailVersion INT, thumbCacheIndex INT, metaModDate REAL, modificationDate REAL, archiveFilename VARCHAR, cameraModel VARCHAR, isoSpeedRating INT, flash INT, shutterSpeed REAL, aperture REAL, focalLength REAL, needsLocationLookup INT, locationCountry VARCHAR, locationState VARCHAR, locationCounty VARCHAR, locationCity VARCHAR, locationPostalCode VARCHAR, locationStreet VARCHAR, gpsLatitude REAL, gpsLongitude REAL, manualLocation INT, ocean INT, country INT, province INT, county INT, city INT, neighborhood INT, aoi INT, poi INT, namedPlace INT, originalEvent INT, event INT);
CREATE TABLE SqFileImage (primaryKey INTEGER PRIMARY KEY AUTOINCREMENT, photoKey INT, imageType INT, version INT, imageWidth REAL, imageHeight REAL, rotation REAL, rasterToDisplayRotation REAL, currentToOriginalRotation REAL, fileSize INT, sqFileInfo INT);
CREATE TABLE SqFileInfo (primaryKey INTEGER PRIMARY KEY AUTOINCREMENT, format INT, relativePath VARCHAR, aliasPath VARCHAR);
Basically, as I can understand this, there is one SqPhotoInfo per image and the entry has all kinds of interesting information in it. For each image, there are at least two entries in the SqFileImage table. One is for the thumbnail, and one is for the image itself. There may also be one if the photo is also a key image for an event. The two are referenced to each other through the photoKey column in the table. It has the primaryKey for the proper entry in the SqPhotoInfo table. Each SqFileImage entry also has a reference to a SqFileInfo table entry. The sqFileInfo column in the SqFileImage table is the primaryKey in the SqFileInfo table.
It looks like if you import the image into your iPhoto Library, then it would be stored in the aliasPath column of the SqFileInfo table, otherwise if you import by reference then it would be read from the relativePath column of the SqFileInfo table. I’m using images referenced from an external location of the filesystem.
Also, there are multiple SqFileImage entries for an image(a SqPhotoInfo entry). There is an entry for the original image, one for the thumbnail, one for the key frame (if it is one), one that is modified (if applicable). More on those later.
To find the SqFileImage entries for the image in question
select * from SqFileImage where photoKey=XXXX
The XXXX would be the photo key.
To get the file location information, you would join it with the SqFileInfo table.
select SqFileInfo.* from SqFileInfo,SqFileImage where SqFileInfo.primaryKey=SqFileImage.sqFileInfo and SqFileImage.photoKey=XXXX
You can get the photoKey from searching the SqPhotoInfo table. It is in the primary key labeled column. Again, there are a number of different files that iPhoto stores. They have a corresponding SqFileImage.imageType associated with them.
- type=8: the original image if it is a raw data image
- type=7: key photo
- type=6: the image itself when you double clock on the thumbnail
- type=5: thumbnail
- type=1: the original if it is modified
So, sounds like I can update the image location for the originals doing something like:
update SqFileInfo set relativePath="" where primaryKey=XXXX
[Update]: OK, iPhoto is stupid, or is too smart for it’s own good. I’m not getting this. If I update the relativePath entry, the next time that I open iPhoto it will have overwritten the value with the old value. Or if I update the entry and move the file, then it asks me to locate the file as it’s missing at the old location. Even if I close iPhoto, update the database, read back the database to verify that everything is correct, and then open iPhoto. Even if I update the modification date in the globals table. Very frustrating.
Punctuated Noise 