[Hacking Mac]: iPhoto SQL Events

See here for more information regarding iPhoto database

Photos are separated into events which typically get created when photos are imported into iPhoto. For scripting in Applescript, the photos can be imported to a specific album, but not to a specific event. Events also don’t have any hooks in Applescript to modify them.

Internally, in the iPhoto database the events are done as follows. There is a table name SqEvent with all of the event listed as rows. It looks like:

CREATE TABLE SqEvent (primaryKey INTEGER PRIMARY KEY AUTOINCREMENT, keyPhotoKey INT, name VARCHAR, comment VARCHAR, rollDate REAL, rollDirectories BLOB, rollID INT, currentPhotoKey INT, displayOrder INT, emptyRoll INT, uid VARCHAR, locationStreet VARCHAR, locationCity VARCHAR, locationCounty VARCHAR, locationState VARCHAR, locationPostalCode VARCHAR, locationCountry VARCHAR, locationLatitude REAL, locationLongitude REAL, locationRadius REAL, locationKey INT, manualLocation INT, ocean INT, country INT, province INT, county INT, city INT, neighborhood INT, aoi INT, poi INT, namedPlace INT, attributes BLOB);

The event has a code to identify it in the primaryKey. The individual images are tagged with an event using the SqEvent.primaryKey in the SqPhotoInfo.sqEvent column. You can get all photos with a certain event, or all of the events for photos using this relationship. For example, the following returns all images from an event named <name>
select * from SqPhotoInfo,SqEvent where SqPhotoInfo.event = SqEvent.primaryKey and SqEvent.name='<name>';
Mac Code Monkey also has good information here.

Ok, so, what do I want to do? What I would like to do is import images to a specific event. I can’t import to a specific event, but I can manipulate the table after the fact by altering the event key for an image from one event to another.
update SqPhotoInfo set event=2095 where primaryKey=3500;
In this case, it will update the event key to the primaryKey of the event that I want to move into (2095 in this case) for the photo identified by the primary key, in this case 3500.

After I import the images, they are in the last import album. It has an id of 999001. I can get the events of these photos with:
select event from AlbumsPhotosJoin,SqPhotoInfo where AlbumsPhotosJoin.sqAlbum=999001 and SqPhotoInfo.primaryKey=AlbumsPhotosJoin.sqPhotoInfo

Advertisements