« I-35W Bridge Collapse | Main | Fairtime, It's Fairtime! »

iPhone Database Details

I’m a digital (as well as physical) packrat. I have all my emails going back to 1994, most of the documents I’ve created in that time (as many as allowed by my previous employers) and a whole collection of IMs. The text messaging on my phone has always been a missing bit of data - until now there’s been no good way to get it off of my phone.

As details have emerged on the iPhone’s internal data structures, I realized that there might be a chance to extract those text messages and archive them offline. Also looks like voicemails can be archived in a similar fashion. I’m almost ready to share the utility with the world, but until then I thought I’d share my iPhone database structure notes. For those geeks interested in such matters, click through to the extended entry. (Thanks to Erica Sadun for porting sqlite3 to the iPhone - made this whole thing much easier)

SMS DB
/private/var/root/Library/SMS/sms.db

TABLE_SqliteDatabaseProperties 
key (TEXT)value (TEXT)
_ClientVersion2
_UniqueIdentifierGUID
TABLEmessage 
ROWID (INTEGER PRIMARY KEY AUTOINCREMENT)
Auto-incrementing field/counter
address (TEXT)
International-formatted foreign address
(18005551212)
date (INTEGER)
OSX-epoch based datetime, convertable via date -r
(1187200801)
text (TEXT)
Content of text message
(This is the text message)
flags (INTEGER)
Flags controlling the type of record
2 - Message sent from address to iPhone
3 - Message sent from iPhone to address
129 - Message log erased from iPhone but addressee still in SMS index
replace (INTEGER)
Unknown, always 0 in my case
svc_center (TEXT)
service center, seems null in my case

CallData DB
/System/Library/Frameworks/AppSupport.framework/calldata.db

TABLE_SqliteDatabaseProperties
TABLEcitycode 
code (INTEGER)
Not sure what the significance of these entries are, I’m wondering if they have something to do with the geocoding of calls not made from your phonebook?
(3888)
city (TEXT)
Not sure what the significance of these entries are, I’m wondering if they have something to do with the geocoding of calls not made from your phonebook?
(RED CLOUD)
INDEXcitycode_codeIndex
citycode (code)
TABLEnpa 
npa (TEXT)
Numbering Plan Area, aka Area Code
(415)
location (TEXT)
State/Province Assigned to the NPA
(CA)
country (TEXT)
Country Assigned to the NPA, may be null if “location” is specific enough
(USA)
TABLEnpalocation 
npa (TEXT)
Numbering Plan Area, aka Area Code
(415)
location (TEXT)
Descriptive location info
(San Francisco/North Bay Area)
TABLEnpanxx 
npa (INTEGER)
Numbering Plan Area, aka Area Code
(907)
nxx (INTEGER)
Unknown
(200)
rate_center (INTEGER)
Unknown
(1)
INDEXnpanxx_npanxxIndex
npanxx (npa,nxx)

CallHistory DB
/private/var/root/Library/CallHistory/call_history.db

TABLE_SqliteDatabaseProperties 
your values will certainly be different here…when you “restore” your iPhone from iTunes the counters all reset
key (TEXT)value (TEXT)
call_history_limit100
timer_last60
timer_outgoing900
timer_incoming540
timer_all1440
timer_lifetime1440
timer_last_reset
data_up_last2.5439454125
data_down_last20.86328125
data_up_all719.9228515625
data_down_all8677.8427734375
data_up_lifetime719.9228515625
data_down_lifetime8677.8427734375
data_last_reset
_ClientVersion3
_UniqueIdentifierGUID
TABLEcall 
ROWID (INTEGER PRIMARY KEY AUTOINCREMENT)
Auto-incrementing field/counter
address (TEXT)
International-formatted foreign address
(18005551212)
date (INTEGER)
OSX-epoch based datetime, convertable via date -r
(1187200801)
duration (INTEGER)
Length of call in seconds rounded to next minute, 0 = missed call
(60)
flags (INTEGER)
Flags controlling the type of record
5 - Outgoing call
4 - Incoming call
id (INTEGER)
AddressBook ID for outgoing calls selected from AddressBook, otherwise -1
(67)
INDEXdate_index
call (date)

KeyChain DB
/private/var/root/Library/Keychains/keychain-2.db
Encrypted, I don’t know how to parse this yet

Notes DB
/private/var/root/Library/Notes/notes.db

TABLE_SqliteDatabaseProperties 
key (TEXT)value (TEXT)
_ClientVersion2
_UniqueIdentifierGUID
TABLENote 
creation_date (INTEGER)
title (TEXT)
summary (TEXT)
TABLEnote_bodies 
note_id (INTEGER UNIQUE)
data

Voicemail DB
/private/var/root/Library/Voicemail/voicemail.db

TABLE_SqliteDatabaseProperties 
key (TEXT)value (TEXT)
VMVersion4
_UniqueIdentifierGUID
tokenstring containing various values, including your phone number
uid_validity1183172695
mailboxusage57
TABLEvoicemail 
ROWID (INTEGER PRIMARY KEY AUTOINCREMENT)
Auto-incrementing field/counter
remote_uid (INTEGER)
International-formatted foreign address
(18005551212)
date (INTEGER)
OSX-epoch based datetime, convertable via date -r
(1187200801)
token (TEXT)
Always reads “Complete” from what I can tell
sender (TEXT)
CallerID from the calling party leaving the voicemail message
(8885551212)
callback_num (TEXT)
Callback number left by calling party, usually caller ID
(8885551212)
duration (INTEGER)
Duration in seconds
(5)
expiration (INTEGER)
OSX-epoch based datetime, convertable via date -r
(1189431482)
trashed_date (INTEGER)
definitely based in seconds, haven’t figured out the epoch yet or why it isn’t the same as the other dates based on OSX’s epoch
flags (INTEGER)
Voicemail flags
0 - Not downloaded yet
1 - Partially downloaded
2 - New, unlistened or only partially listened to
3 - Listened completely
11 - Pending delete, in “Deleted Items”
15 - Deleted from iPhone, pending delete from voicemail hq
INDEXdate_index
voicemail (date)
INDEXremote_uid_index
voicemail (remote_uid)

Other, more complicated DBs, involved in syncing
AddressBook DB
/private/var/root/Library/AddressBook/AddressBook.sqlitedb
AddressBook Images DB
/private/var/root/Library/AddressBook/AddressBookImages.sqlitedb
Maptiles DB
/private/var/root/Library/Caches/MapTiles/MapTiles.sqlitedb
Calendar DB
/private/var/root/Library/Calendar/Calendar.sqlitedb

Comments (1)

Jesse Garcia:

Have you been able to extract the SMS messages from the iPhone database. I have seen a few posts elsewhere claiming to run a script but I do not know how to do that yet...help would be greatly appreciated

Post a comment

About

This page contains a single entry from the blog posted on August 21, 2007 2:29 AM in iPhone.

The previous post in this blog was I-35W Bridge Collapse.

The next post in this blog is Fairtime, It's Fairtime!.

Many more can be found on the main index page or by looking through the archives.

Creative Commons License
This weblog is licensed under a Creative Commons License.
Powered by
Movable Type 3.34