Thursday, June 9, 2011

RIP iphone: reteiving the Messages, call histroy and contact list

After giving me good service for 3 and half years the touch screen of my iphone finally gave away. It happened at night when it refused to turn itself off and there was no way for me to reset it.

By now, I knew my iphone was on its last breaths and it was living on borrowed time. I did not loose too much of sleep on this but my immediate concern was to retrieve my last few days messages, address books and call history.

I knew I had the lastest backup, as the iPhone was still syncing with the iTunes. So all i needed to do was to read the backup. Also from my previous experience I knew these files would be mostly stored as sqllite files.

After a bit of search on my machine the files were backed on my Vista up @ /user/AppData/Apple Computer/Roaming/MobileSync/Backup/adb3eaadbe258e41f1a045ae15ccb7d3cf66ee6b directory. Just check the latest directory on your computer.

Next was to figure out which files were the ones needed. Here after some search I found these files to be here.
ff1324e6b949111b2fb449ecddb50c89c3699a78.mddata Call history 3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata SMS Log
740b7eaf93d6ea5d305e88bb349c8e9643f48c3b.mddata Notes database
31bb7ba8914766d4ba40d6dfb6113c8b614be442.mddata Contact List

992df473bbb9e132f4b3b6e4d33f72171e97bc7a.mddata Voicemail list

I used the Sqllite manager, a firefox extension (which can be found here) to be able to read these files.
Most of the files are easy to read and does not really need any SQL query as such.

However the Contact list is more complicated with multiple tables and for that I used the query to build a flat file.
select p.first, p.last, p.middle, p.Organization, p.Department, p.note, p.JobTitle, p.Prefix, d1.value as mobile, d2.value as Home, d3.value as Work, d4.value as WorkFAX, d5.value as Other, d6.value as Main, d7.value as HomeEmail, d8.value as WorkEmail, a1.value as Street,a2.value as State,a3.value as CountryCode,a4.value as City,a5.value as Country,a6.value as Zip from abperson p
left outer join ABMultiValue d1 on d1.record_id=p.ROWID and d1.label=1
left outer join ABMultiValue d2 on d2.record_id=p.ROWID and d2.label=2 and
left outer join ABMultiValue d3 on d3.record_id=p.ROWID and d3.label=3 and
left outer join ABMultiValue d4 on d4.record_id=p.ROWID and d4.label=4
left outer join ABMultiValue d5 on d5.record_id=p.ROWID and d5.label=5
left outer join ABMultiValue d6 on d6.record_id=p.ROWID and d6.label=6
left outer join ABMultiValue d7 on d7.record_id=p.ROWID and d7.label=2 and
left outer join ABMultiValue d8 on d8.record_id=p.ROWID and d8.label=3 and
left outer join ABMultiValueEntry a1 on a1.parent_id=p.ROWID and a1.key=1
left outer join ABMultiValueEntry a2 on a2.parent_id=p.ROWID and a2.key=2
left outer join ABMultiValueEntry a3 on a3.parent_id=p.ROWID and a3.key=3
left outer join ABMultiValueEntry a4 on a4.parent_id=p.ROWID and a4.key=4
left outer join ABMultiValueEntry a5 on a5.parent_id=p.ROWID and a5.key=5
left outer join ABMultiValueEntry a6 on a6.parent_id=p.ROWID and a6.key=6;

Now too search for an iPhone alternative :-)