[What we’re into right now]: Musical Update Edition

Going back, this is a list of albums that have spent a long time being dumped out to the iPod and in the CD players.

The Black Keys - BrothersThe Black Keys – Brothers: Wonderful Bluesy Deliciousness

PJ Harvey - Let England ShakePJ Harvey – Let England Shake: Explorations of England and the First World War

LCD Soundsystem - This Is HappeningLCD Soundsystem – This Is Happening: An exhibit to flaunt a knowledge of musical history


[What we’re into right now]: Arcade Fire

Arcade Fire - The SuburbsIt’s been a while since we’ve written anything in these series of posts, but we’ll try to keep them going.

I had resisted the Arcade Fire for a very long time now. If fact, I began to do it stubbornly so simply maintaining that I did not care for them and leaving it at that. However the album The Suburbs had come into my house and I had spent some time hearing it as it was played here and there. Eventually, I had sat down to listen to it in it’s entirety and it has since grown on me. In fact, this may have been one of the best albums to come out last year.

Arcade Fire has surrounded this album with suburbs of it’s own giving it a monumental scale. This album is meant to be a statement. From the website to a short film directed by Spike Jonze that premiered at SXSW this year. There is even HTML5 interactive web site where you can type in your childhood address and it creates a film using Google Earth to the song “We Used To Wait”. Of course, any album called The Suburbs is going to ignite emotions from almost anyone. Suburbia in America has become a barometer of sorts for so many social/economic/political views. The most prominent of which is the urbanite looking down on the manicured lawns as a sign of emptyness and soul-crushing sameness, so often something akin to the militant ex-smoker.

The album opens with the song “The Suburbs” where the levity of the music and tempo mask the lyrics over it, and the songs that are still to come. It builds slowly until becomes momentous arena-rock. So much of this album is infused with the feeling of disillusionment and the nostalgia for the escapement and innocence of your childhood. Of course, as a child the boredom seemed insufferable in the suburbs, and you would daydream of the time for you to escape to someplace more exciting. Now looking back, by the time that it’s your time to have children, you realize that perhaps you haven’t changed that much from your parents. Personally, much of the feeling of the album is wrapped up in the lyric “But do you think your righteousness could pay the interest on your debt? I have my doubts about it.” from “City With No Children”. Perhaps you yourself are caught in the same trap as your parents where you worry about the circumstances of your life, but see that you cannot necessarily change it without giving up everything.

[Hacking Mac]: iPhoto Structure – Revisit

See here for more information regarding iPhoto database

Looks like I didn’t have the proper understanding of the structure of the iPhoto library package. iPhoto appears to use the internal structure even if the photos are imported by reference. In the directory iPhoto Library/Originals/ an alias to the original photo is still stored where the image would have been imported. This alias file also needs to be updated for iPhoto to properly reference the new file location instead of just updating the iPhoto database with the new file location.
This alias location can be queried from either applescript, or the SQL database. From applescript you can get the filename with the following:
repeat with i_photo in (get every photo of (get album s_album))
get original path of i_photo
end repeat

From the database, the location can be queried from the column aliasPath in the SqFileInfo table using:
select SqFileInfo.primaryKey,SqFileInfo.aliasPath from SqFileInfo,SqFileImage where SqFileImage.sqFileInfo=SqFileInfo.primaryKey and SqFileImage.photoKey=ID
You will need the primaryKey later if you want to update the image location. For using the SQL database query, the photoKey ID can be queried via applescript as
get id of i_photo as unsigned integer
The Id for the photo needs be be converted to unsigned integer from what is normally returned by applescript as it’s going to appear as a floating point number due to the number of bits used.

Both of these return the full path to either the original file that was imported, or the reference to the original file if it’s imported by reference. In the case of importing by reference, iPhoto will store a file at that location
-rw-r--r--@ 1 user staff 168340 May 26 12:38 DSC_0001.JPG
That file is basically a mac alias to where the image is actually stored.

sidenote: This is different than a hard or symbolic link in UNIX. You can see that it doesn’t dereference to the target location as a normal symlink would. It’s differentiated with the “@” after the list of UNIX file attributes. This alias appears to be a holdover from OS9. You can view the attributes that are associated with it using the xattr command like:
attr -vrx DSC_0001.JPG
DSC_0001.JPG: com.apple.FinderInfo
DSC_0001.JPG: com.apple.ResourceFork

The ResourceFork attribute contains the target information for the alias. Using applescript you can get the target also with
if kind of fp_alias is "Alias" then
get original item of fp_alias as alias)

The location of the original referenced file can be updated by correcting the location of the file in the database, and the alias file that iPhoto uses. This would involve correcting the datebase entry such as:

update SqFileInfo set relativePath=UPDATED_PATH where primaryKey=ID

The UPDATED_PATH would be where the image is being moved to, and the id ID would be returned as the primaryKey from the appropriate entry in the table returned by the database query above.

One the database is updated, the alias file that iPhoto uses would have to also be updated to reflect the new location. This could be done in applescript by creating a new alias such as:

set fp_newAlias to make new alias file at fp_dir to targetName with properties {name:s_name}

For good measure you can also update the timestamp of the database with:

update SqGlobals set modificationDate=(julianday(\"now\") - julianday(\"2001-01-01 00:00:00\")) * 60 * 60 * 24 ;

[Hacking Mac]: iPhoto SQL Database

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);


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.