Reading iPhone’s Database

This week’s post is really not something you will come accross when developing a business application with QlikView, but rather it is a bit random, and somwhat fun. We will go through the required steps to read data stored in your iPhone, such as Call History, SMS Messages, Contacts list, Notes, etc.

I can actually think of a real use for this: You can have a data model with the Calls Table associated to the Contacts List (to get the names) and the SMS table, maybe with a Link Table or master calendar to associate the Date stored in the SMS table with the Date stored in the Call Log. Then, you would use this information to answer a few questions:

1. Whom do I call the most?
2. On average, how many calls do I make per day?
3. How much time do I spend on the phone (call time) per month?
4. Did the Phone company charge me correctly according to the extra minutes I used?
5. What is the percentage of Outgoing vs Incoming call time?
6. You can even detect patterns in your SMS messages that make you realize you should instead make a phone call instead of sending 10 messages.

Before we start, you need to be aware of a few things:

• You DO NOT need a jailbroken iPhone, although you can use one.
• By default, the iPhone only stores the last 100 records in the call history. So maybe you should backup the retrieved data to use it or concatenate once you get new data. You can change that limit (only in jailbroken phones) following the steps described here: http://www.emiratesmac.com/forums/iphone/8679-iphone-call-history-limit-increase.html
• I did not notice a limit in the SMS history table.
• I will not go through the definition of each and every table or field in the database, because I really do not know the Databse that good, but you will get a basic idea.

OK! Lets do it!

The databse that is currently used in the iPhone is SQLite, so you will need to get the ODBC driver from here. There is a 64-bit version and the 32-bit one. Install the one you need and you should get a connection called SQLite 3 Datasource.

In order to get the files into your PC (I’m assuming you are using Windows, since you are most likely to be a QlikView developer), you need to make a backup of your iPhone via iTunes (Instructions here).

Once you’ve made the backup, you need to get the *.db files. iTunes places the backup files in the following places:

• Windows Vista and Windows 7: \Users\(username)\AppData\Roaming\Apple Computer\MobileSync\Backup\
• Windows XP:\Documents and Settings\(username)\Application Data\Apple Computer\MobileSync\Backup\
• Mac: ~/Library/Application Support/MobileSync/Backup/

Note: If you do not see the AppData or Application Data folders, you may need to show hidden files (Windows XP,  Windows Vista and Windows 7).

If you navigate to that folder, wou will see a bunch of files with rare/encoded names. These are the ones you will need:

iPhone BackUp - Encoded Names

So now, you have 1) the ODBC, and 2) the database (you just need to change/add the file extension to the original backup files).

I’m sure you know where to go from here, just two more things:

1. Make sure the connection string specifies the filename. You should have something like:
   • ODBC CONNECT TO [SQLite3 Datasource;Database=< full path to the db file >];
2. Timestamps are in Unix 10-digit format. I used this to convert it to readable format (this will give you the UTC Timestamp, you may need to substract/add certain number of hours in order to get it on your local time):
   • Date(MakeDate(1970, 1, 1) + (< field containing timestamp, usually “date” >/60/60/24), ‘DD/MM/YYYY hh:mm:ss[.fff] TT’) as date

Enjoy! And please, let me know what use you make of it.

Reference:

• SQLite Home Page: http://www.sqlite.org/
• SQLite ODBC Driver: www.ch-werner.de/sqliteodbc
• How To BackUp your iPhone: http://support.apple.com/kb/ht1766
• What the backed-up file names mean: www.iphonebackupextractor.com/blog/+how+to+get+call_history.db+iphone+backup&cd=1&hl=en&ct=clnk