iPhone Database Details

A useful resource for the different databases in the iPhone
iPhone databases

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

TABLE	_SqliteDatabaseProperties
	key (TEXT)	value (TEXT)
	_ClientVersion	2
	_UniqueIdentifier	GUID

TABLE	message
	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

TABLE	citycode
	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)

INDEX	citycode_codeIndex
	citycode (code)

TABLE	npa
	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)

TABLE	npalocation
	npa (TEXT)
		Numbering Plan Area, aka Area Code
		(415)
	location (TEXT)
		Descriptive location info
		(San Francisco/North Bay Area)

TABLE	npanxx
	npa (INTEGER)
		Numbering Plan Area, aka Area Code
		(907)
	nxx (INTEGER)
		Unknown
		(200)
	rate_center (INTEGER)
		Unknown
		(1)

INDEX	npanxx_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_limit	100
	timer_last	60
	timer_outgoing	900
	timer_incoming	540
	timer_all	1440
	timer_lifetime	1440
	timer_last_reset
	data_up_last	2.5439454125
	data_down_last	20.86328125
	data_up_all	719.9228515625
	data_down_all	8677.8427734375
	data_up_lifetime	719.9228515625
	data_down_lifetime	8677.8427734375
	data_last_reset
	_ClientVersion	3
	_UniqueIdentifier	GUID

TABLE	call
	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)

INDEX	date_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)
	_ClientVersion	2
	_UniqueIdentifier	GUID

TABLE	Note
	creation_date (INTEGER)
	title (TEXT)
	summary (TEXT)

TABLE	note_bodies
	note_id (INTEGER UNIQUE)
	data

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

TABLE	_SqliteDatabaseProperties
	key (TEXT)	value (TEXT)
	VMVersion	4
	_UniqueIdentifier	GUID
	token	string containing various values, including your phone number
	uid_validity	1183172695
	mailboxusage	57

TABLE	voicemail
	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

INDEX	date_index
	voicemail (date)

INDEX	remote_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