The rants and ravings of a Mac developer

I Love programming, Macs, the iPhone, iPad, Apps & iOS! In my spare time I like Programming in ExtJS, PHP, MySQL / SQLite3 and Apache - in other words a Real Geek's geek. I've tinkered around with programing for iOS in the not so distant past . During the day I work for Motorola Solutions in IT as BSA for our Export Compliance system JPMorgan's TradeSphere. At night I freelance program on the Mac & Web. I'm also a techie who likes Sci-Fi and Horror. Some of my favorite authors include Stephen King, William Gibson and Frank Herbert as well as his son Brian Herbert and Kevin Anderson.

But my real passion is for Comic Book Collecting. My current freelance project is writing a web based application using ExtJS for collecting comics books. After I get the web application completed, I will be integrating some portion of the web application into a desktop client for the Mac through the use of web services for all data exchange & transaction. Eventually, the web site & Mac client would also include a social networking component as well as an iOS component.

I started collecting comic books as a kid when my grandfather passed away and we found my father's comic book collection when we were cleaning out my grandfather's basement. There were a number of early issues in that stash, such as Fantastic Four number one, The Amazing Spider-Man number one, number 22 of the Uncanny X-Men... and others. So needless to say, I was hooked.

After high school I stopped collecting - college, drinking and woman took priority (not necessarily in that order) over visiting the local comic book shop. Now that I have 2 kids of my own, I've gotten back into collecting.

Date Format used in iPhotoMain.db database

Not sure how many of you know that iPhoto uses SQLite3 as its database engine. You can access the database files by right clicking on ~/username/Pictures/iPhoto Library and selecting Show Package Contents. Once you have displayed the package, the SQLite3 datbase files are:

  • iPhotoMain.db
  • iPhotoAux.db
  • face.db
  • face_blob.db

The main database file, as you might have guessed, is the iPhotoMain.db file. In Terminal, you can open the file by changing to the directory of iPhoto Library package, e.g.:

cd /Users/gskluzacek/Pictures/iPhoto\ Library

Then execute the following

sqlite3 iPhotoMain.db

One of the initial problems that I faced was when reading date fields, like the photoDate field on the sqPhotoInfo table. After some experimenting, I determined that the date is stored as a FLOAT which represents the number of days since midnight Jan 1st 2000. To convert this to a Julian Day value, you need to add the Julian Day of 1/1/2000 - then you can use the datetime() function to convert this to a more human readable format. For example:

select primaryKey, 
datetime(photoDate +  julianday('2000-01-01 00:00:00')) as photoDate,
archiveFilename from SqPhotoInfo order by archiveFilename desc;

Hopes this saves someone a couple of hours, I googled all over and couldn’t find this info.