[Hacking Mac]: Media Database

I’ve got a new project right now. What I want is a central repository for all media. From music to movies and pictures and ultimately PDFs. The Home Sharing feature of iTunes is close but the problem with it is that our iTunes libraries are on laptops and they are either not always powered on, or are not physically around and on the network. Also, i would love to merge multiple personal libraries into a single common library, but maybe not everything is worth pooling together.

I picture it as a SQL back end that different instances of iTunes can communicate with. Of course, what would make the whole thing work is that the Mac version of iTunes (and additionally iPhoto, etc) have scripting hooks built in for AppleScript manipulation/queries.

[Hacking Mac]: Compiling Objective-C

I’ve been looking at compiling up some code for OS X on the Mac. Some things that are a little more involved than just AppleScript. The developer tools from Apple are pretty nice, but for various reasons, I wanted to use a makefile since there is some C-code, and some other steps that I wanted to automate based on the dependencies.

Ultimately, I wanted to use the scripting bridge which means that I would have to use Objective-C at least, even if I don’t use the full Cocoa suite. I looked for a while, but there didn’t seem to be anything about using gcc/g++ for compiling objective-C at the command line. Actually, it turns out that the gcc that comes with the developer tools is what Xcode has underneath to compile the source code.
You can generate the object file just like you would with any C/C++ file. The following will generate an object file for linking written in Objective-C with the standard file suffix for the source code.

gcc -c src/test.m -o objs/test.o

The automatically recognized Objective-C filetypes by suffix are (see the gcc man page for more information):

       file.m
           Objective-C source code.  Note that you must link with the libobjc
           library to make an Objective-C program work.

       file.mi
           Objective-C source code which should not be preprocessed.

       file.mm
       file.M
           Objective-C++ source code.  Note that you must link with the
           libobjc library to make an Objective-C++ program work.  Note that
           .M refers to a literal capital M.

       file.mii
           Objective-C++ source code which should not be preprocessed.

If necessary, the file type is also accepted on the command line with the either the flags “-ObjC”/”-ObjC++” or the “-x” flag and one of the following:

objective-c  objective-c-header  objective-c-cpp-output
objective-c++ objective-c++-header objective-c++-cpp-output

Gcc takes the following Objective-C and Objective-C++ options

       Objective-C and Objective-C++ Language Options
           -fconstant-string-class=class-name -fgnu-runtime  -fnext-runtime
           -fno-nil-receivers -fobjc-call-cxx-cdtors -fobjc-direct-dispatch
           -fobjc-sjlj-exceptions -fobjc-gc -freplace-objc-classes -fzero-link
           -gen-decls -Wassign-intercept -Wno-protocol  -Wselector
           -Wno-property-assign-default -Wstrict-selector-match
           -Wundeclared-selector

If you need the frameworks (like Foundation or Cocoa), you will need to specify them during the linking step. The frameworks can be specified with the “-frameworks” option. The following will link the object file above with the Foundation and Scripting Bridge frameworks

gcc objs/test.o -framework Foundation -framework ScriptingBridge -o bin/test

You may need to specify the search directories for the frameworks with the “-F” option. See the gcc man page for more information.

[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

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

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.

[Hacking Mac]: iPhoto Structure


[Update]: See updated iPhoto SQL structure page for more recent information from digging around, or here.

Spent some more time working out how iPhoto works. Here is some of what appears to be going on. By the way, I’m using iPhoto ’09, just for reference.

The default iPhoto library is stored in the “~/Pictures/iPhoto Library”. I’m going to refer to this from now on just as “iPhoto Library”. This is a package, so you will need to tell Finder to open the package contents so that you can see inside. From the command line, you can just change (cd) right into the directory. There are some files such as AlbumData.xml that contain an XML description of the iPhoto database. The one thing to note is that this file is basically just written out for reference. It doesn’t actually get read in. There is a good write-up of the different files that iPhoto uses here. (Fat Cat software makes an application called iPhoto Library Manager that sounds nice, but not quite what I’m looking for.) The iPhoto Library sits in “iPhoto Library/iPhotoMain.db”. It’s an SQLite3 database.

I’m not importing my images into the library and I’m mainly concerned with the original files to update the locations, so I’m less interested in the data directories “Data”,”Originals”, and “Modified”

For reference, there are also a couple of options for starting iPhoto.
First, you can select a different library when iPhoto starts up.
Second, you can run maintenance on the iPhoto Library.

[Hacking Mac]: iPhoto Images

See here for more information regarding iPhoto database

I want to move my iPhoto images from one directory to a different one on the file system. I currently have them imported to iPhoto by reference so they have not been copied into iPhoto itself. I don’t want them all imported to the iPhoto Library because I have them on a shared server. I can’t find a way to batch move all of the images in an event or album.

One thing that I can do is to move them, and then re-locate the image when I try to access them in iPhoto but that is a huge pain. I don’t want to re-import them because many of them have metadata associated with the image in iPhoto, and these would then be lost.

Normally, this would not be a big deal, but it appears that all of the image properties are read-only through the Applescript API. Ugh, sometimes I hate Apple. I just want to move where the images are located on the disk. Why does this have to be so hard…