Archive for August, 2010

Fixing the iPhone’s photo library

The other day my iPhone photo library got corrupted. It quit out whilst saving a photo, and thereafter when opening Photos, it tried to rebuild the database. Some of my photos were gone! Oh no.

Actually, it turns out they were still there and were importable into Aperture (iPhoto and others would have been similar). But the photo library was corrupt. It was missing some photos and others were out of order.

I decided to poke about. Of course I could restore my iPhone, but I’d loose new photos and that’s no fun. I really wanted a way to fix it in situ. I used PhoneView to mount the phone and view it as a drive. It doesn’t require jailbreaking. It’s best to turn on “Show Entire Disk (Advance Mode)”.

Here you’ll find some folders:

  • DCIM – the actual photos
  • PhotoData – the iPhone’s databases and thumbnails

The interesting things are:

  • DCIM/.MISC/Info.plist – contains counts of maximum file number per folder (See below)
  • PhotoData/MISC/Info.plist – contains counts of maximum file number per folder, seem to be identical to that above
  • PhotoData/Photos.sqlite – the photo db which Photos uses to display stuff
  • PhotoData/PhotosAux.sqlite – Another db which Photos uses to display stuff ( contains location data, needs to exist and be kept in sync with Photos)

Here’s what happened: The phone stores it’s photos in a folder called DCIM/XYZAPPLE/ where xyz are a three digit number starting 100,101,102 etc. This affects the numbering scheme of the photos inside. Each folder can contain 1000 shots, so 100APPLE can contain IMG_0000.jpg thru IMG_0999.jpg and 200APPLE can contain IMG_1000.jpg thru IMG_1999.jpg, and so on.

Step 1.

If the database files get corrupt, they can be rebuilt. There are issues however. The Info.plist file can get corrupted. That prevents the phone from knowing the maximum file number in each DCIM folder. It seems like the phone can recreate this file if it’s gone. I just updated mine to make sure it was correct.

It looks something like this (in PropertyList Editor, WP stripped the xml markup of the xml plist file;) :

DCIMChangedExternally
LastFileGroupNumber-100
999
LastFileGroupNumber-101
173
LastFileGroupNumber-102
102
LastFileGroupNumber-103
60
LastFileGroupNumber-104
127
LastFileGroupNumber-105
58
LastFileGroupNumber-106
7
LastFileGroupNumber-107
17
LastFileGroupNumber-108
38
LastFileGroupNumber-109
51

Step 2.

Copy that Info.plist back after modifying it to both DCIM/.MISC/ and PhotoData/MISC/.

Step 3.

What if the db is corrupt? Well, that’s actually quite likely (in my experience) to happen. Delete it and it’ll get recreated as the phone traverses the DCIM folders and examines photos. But here’s the rub, the order they get added is important. And the order the phone traverses the DCIM folders is basically alphabetical. Why is this an issue? Because the iPhone decides to bump which folder it stores photos in each time you update it / restore. Which means your photos are likely scattered in a random order over these folders, with batches of consecutive numbers mixed up. Apple uses the primary key to order the photos displayed in the App – not the date they were captured (seriously, bad apple, there’s even a capture date field it could use). I don’t know why and it sucks, but a rebuilt database will have your photos in a weird order.

Lets fix the db. Long story short, the db layout is a bit odd. There’s a bolt-on lat-long db for iPhone 4 maps, and there’s an PhotoAux table that records some metadata for quick access. The App uses the primary key to order the photos, so we’re going to have to reorder primary keys. That sucks.

Delete those db files from your phone (back up if you like). Open Photos app on the phone and let it recreate them. Quit it when it’s done (make sure it’s really quit using the task switcher in iOS4).

Grab Photos.sqlite and PhotosAux.sqlite and put them somewhere together.

Run this script… Caveat emptor it’s poorly written, requires sqlite3 to be installed (you can get it from fink). And it may or may not be correct.


#!/bin/sh

cp Photos.sqlite Photos-mod.sqlite
cp PhotosAux.sqlite PhotosAux-mod.sqlite

sqlite3 Photos-mod.sqlite "DELETE FROM Photo;"
sqlite3 PhotosAux-mod.sqlite "DELETE FROM AuxPhoto;"
sqlite3 Photos.sqlite "SELECT primaryKey FROM Photo ORDER BY captureTime;" > keys.txt

# move all the ids fwd (better hope 999999 is max, could always get max fk)
sqlite3 Photos-mod.sqlite "UPDATE PhotoExtras SET foreignKey=foreignKey+999999";

vars="type,title,captureTime,width,height,userRating,flagged,thumbnailIndex,orientation,directory,filename,duration,recordModDate"
let i=1
cat keys.txt | while read line; do
echo "${line}";
r=`sqlite3 -csv Photos.sqlite "SELECT $vars FROM Photo WHERE primaryKey=${line};"`;
r=`echo $r | sed -e 's/\([^,]*\)/"\1"/g'`
echo "INSERT INTO Photo (primaryKey,${vars}) VALUES ($i,$r);";
sqlite3 Photos-mod.sqlite "INSERT INTO Photo (primaryKey,${vars}) VALUES ($i,$r);";

fk=$((${line}+999999));
echo "UPDATE PhotoExtras SET foreignKey=$i WHERE foreignKey=${fk}";
sqlite3 Photos-mod.sqlite "UPDATE PhotoExtras SET foreignKey=$i WHERE foreignKey=${fk}";

r=`sqlite3 -csv PhotosAux.sqlite "SELECT latitude,longitude FROM AuxPhoto WHERE primaryKey=$line"`;
if [[ $r != "," ]]; then
r=`echo $r | sed -e 's/\([^,]*\)/"\1"/g'`;
echo "INSERT INTO AuxPhoto (primaryKey,latitude,longitude) VALUES ($i,$r);";
sqlite3 PhotosAux-mod.sqlite "INSERT INTO AuxPhoto (primaryKey,latitude,longitude) VALUES ($i,$r);";
else
echo "INSERT INTO AuxPhoto (primaryKey) VALUES ($i);";
sqlite3 PhotosAux-mod.sqlite "INSERT INTO AuxPhoto (primaryKey) VALUES ($i);";
fi

let i=$i+1;
done

mv Photos.sqlite Photos-old.sqlite
mv PhotosAux.sqlite PhotosAux-old.sqlite

mv Photos-mod.sqlite Photos.sqlite
mv PhotosAux-mod.sqlite PhotosAux.sqlite

It’ll make two new db files to copy back to the iPhone. Better make sure you quit Photos app on the phone first. First of all, it’s sucking the Photo table into a new db because we need to reorder the primary keys. It now occurs to me we might have been able to shift the primary keys range (though this might bork things for subsequently added photos). The could be improved upon in terms of it’s escaping here. We also update the PhotoExtras to shift it’s references to the reordered Photo table. Finally we keep the lat long data in the second db in sync. Note that there are also PhotoAlbum tables and a table which notes how to join the albums together. I didn’t mess with these since I don’t sync back albums to the phone. If I did, it’s possible this would just work, maybe not – YMMV. But hey, it’s a start. Took a long while to figure out how to muck with stuff to have a hope in hell of not having to do a restore. If only apple rebuilt the db in date order, or queried it with a captureDate index… this wouldn’t have been needed.

That’s it. Restart your phone. Everything should be good again.

Posted by: geohar on Saturday, 14th Aug, 2010

Categories

Subscribe

Links

Meta