Mac Code Monkey

Reading Binary and XML PList files in PHP

For a generic solution to reading both XML PList files and Binary PList files check out Rodney Rehm’s CFPropertyList a PHP Implementation of Apple’s PList (binary and xml).

It has a nice object oriented API to read in, traverse, access, modify and save PList files.

Here’s a quick example that I used to read in the iPhoto binary PList file and get the directory where the iPhoto Library is stored.

require_once('CFPropertyList/CFPropertyList.php');
$plist = new CFPropertyList(
'/Users/xxxxxxx/Library/Preferences/com.apple.iPhoto.plist',
CFPropertyList::FORMAT_BINARY );
$pa = $plist->toArray();
print $pa['RootDirectory'] . "\n";

More on iPhotoMain.db / iPhotoAux.db - Date Formats!

There seems to be a dearth of information about the iPhoto SQLite3 database, so I am going to continue to post what I find out in hopes that it will help others. This is a follow up to my other posts about iPhotoMain.db SQLite3 database, which can be found here:

• iPhotoMain.db Table Relationships
• Finding the original photo file in the iPhoto Library database
• Date Format used in iPhotoMain.db database

Additionally, I found the information I needed from the following blog posts on Punctuated Noise.

• iPhoto Images
• iPhoto SQL Database
• iPhoto Structure – Revisit

The key info I needed that came from the last post above was:

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

From this I was able to determine how to ‘decode’ Apple’s floating point date representation. Not only does Apple use a floating point number to represent a date (it actually form a julian day), but they apparently have 2 different forms of it. UGH!

For those of you who just want the info, check this query out which selects records from the iPhotoAux.db table, SqPhotoInfoOther.

select primaryKey, 
archiveDate as rawArchiveDate, 
datetime((archiveDate / (60 * 60 * 24)) + julianday('2001-01-01 00:00:00')) 
   as archiveDate, 
originalPhotoDate as rawOriginalPhotoDate,
datetime(originalPhotoDate + julianday('2000-01-01 00:00:00')) 
   as originalPhotoDate, 
originalFilename
from SqPhotoInfoOther;

If you execute the following SQL with values from the SqPhotoInfoOther.archiveDate and SqPhotoInfoOther.orgiginalPhotoDate - you’ll get some nonsensical values back.

select datetime(335385037.045712) as archiveDate, datetime(4239.64527777778) 
as originalDate;

archiveDate: 913540-11228636-08 13:05:49
originalDate: -4701-07-04 03:29:12

From my Date Format used in iPhotoMain.db database post above, you can see I figured out the 2nd date (original photo date) a while ago… you just need to add the julian day for Jan 1st 2000 @ midnight to the floating point value, then use the datetime() function on the new value.

I tried this for the first date (archiveDate) and no luck: 920252-11228637-15 01:05:49. No improvement. When I came across the update statement above from the Punctuated Noise blog. I knew I had it!

For some strange reason you had to divide the floating point value by the number of seconds in a day (60 * 60 * 24 or 86,400) before adding the julian day for Jan 1st 2000 @ midnight. BINGO!

Now if some one could tell me why Apple, in there infinite wisdom, did it this way. I suspect its something to do with how the Core Data API handles time stamps fields versus date time fields as I have noticed that I have to adjust the archive date by -5 hrs (current offset from GMT for the Central Time Zone), that is the number is GMT, and I don’t have to do that for the original photo date…

Or may be it has something to do with iPhoto knows to store the archive date in GMT and then converts it to the local time zone when displaying it. But for the original photo date, the EXIF data from camera doesn’t have the time zone, so it stores it as it comes in.

Pity, it would be nice if both dates were stored in GMT for consistency.

iPhotoMain.db Table Relationships

I’m currently working on a program that reads iPhoto’s (version 8.1.2 [424] aka iPhoto ‘09]) main SQLite3 database, iPhotoMain.db. As part of the development I needed to determine the relationships between the various tables. The following SQL query shows the relationships between the following tables:

• SqEvent (list of events in your iPhoto Library)
• SqPhotoInfo (the photos in your iPhoto library)
• SqFileImage (one or more images used to represent each photo)
• SqFileInfo (additional information about each SqFileImage record)

To retrieve all information about the photos belonging to a specific event (name) in your iPhoto library execute the follow SQL SELECT statment:

select * 
from SqEvent     as e 
join SqPhotoInfo as pi  on pi.event       = e.primaryKey
join SqFileImage as fim on fim.photoKey   = pi.primaryKey
join SqFileInfo  as fi  on fim.sqFileInfo = fi.primaryKey
where e.name = 'At the Beach Hargar Park Coloma, MI 2011';

The above query will return multiple records (usually 3, but it can be more than 3 records if the photo was modified) for each photo.

One record for the Original (unmodified) photo, another record for the Thumbnail and a third record that only references the file name (not sure of the function of this record yet).

how to determine the default character set of a MySQl database

To show the default character set of a MySQL database use this query:

SHOW CREATE DATABASE `DB_NAME`