First off some back story for this post. My niece bought a Samsung galaxy S4. She used it for about a month, then decided to go back to her iPhone and give the Samsung to her brother. This meant that as a programmer, I had to find some way to transfer her android messages to her iPhone.
My basic plan is:
- backup iPhone via iTunes
- backup android SMSs via SMS Backup and Restore
- transfer android SMSs into the iPhone backup using sqlite.
- restore iPhone from altered backup
So first off I have to take a look at the sms database. It can be found in your iTunes backup folder (for me it was %APPDATA%/Apple Computer/mobile sync/backup/). there’s one more folder with all the backups, the one you are looking for is named “3d0d7e5fb2ce288813306e4d4636395e047a3d28”.
Here are the tables inside:
[Show Tables][Hide Tables]
Table |
Description |
_SqliteDatabaseProperties |
Database properties |
attachment |
message attachments |
chat |
message conversations |
chat_handle_join |
Joins chat and handle table |
chat_message_join |
Joins chat and message table |
handle |
Phone addresses |
message |
The main message table |
message_attachment_join |
Joins message and attachment table |
First lets look at how they all relate:

Just from making up this chart of how the tables relate, I have already seen areas where I made mistakes when I first tried to inject messages into the database. If you don’t understand the lines joining each table, here is a quick refernce sheet: http://www.vivekmchawla.com/2013/04/erd-crows-foot-relationship-symbols-quick-reference.html
So a message is made up of: the body of the message (message), an address (handle), a conversation (chat), and sometimes an attachment (attachment).
Lets look at the tables:
[Show Message Table][Hide Message Table]
Column |
Type |
Description |
ROWID |
int |
auto incrementing id |
guid |
text |
random identifier, maybe used by iMessage |
text |
text |
message body |
replace |
int |
unknown, however is always set to ‘0’ |
service_center |
text |
unknown, though often null/blank |
handle_id |
int |
reference to ROWID in handle, used for address |
subject |
text |
used for mms, otherwise null |
country |
text |
the country code, e.g. “au” |
attributedBody |
blob |
unknown |
version |
int |
either 10 or 1. usually 10 in newer messages< |
type |
int |
unknown, usually 0 |
service |
text |
SMS or “iMessage” |
account |
text |
phone number or email, e.g. “p:+614112222” or “e:email@email.com”, used in iMessage |
account_guid |
text |
a guid somehow linked to account |
error |
int |
usually 0 |
date |
int |
Mac Absolute Time, (Unix time = date + 978307200) |
date_read |
int |
time message was read |
date_delivered |
int |
time message was delivered |
is_delivered |
int |
if date_delivered is not 0 then this is 1, else 0 |
is_finished |
int |
unknown, usually 1 |
is_emote |
int |
usually 0, I have no cases of this being 1 |
is_finished |
int |
unknown, usually 1 |
is_from_me |
int |
1 if you are the sender, else 0 |
is_empty |
int |
usually 0 |
is_delayed |
int |
unknown, usually 0 |
is_auto_reply |
int |
unknown, usually 0 |
is_prepared |
int |
unknown, mostly 0, sometimes 1 |
is_read |
int |
if date_read is not 0 then 1, else 0 |
is_system_message |
int |
unknown, usually 0 |
is_sent |
int |
unknown, either 0 or 1, could be related to is_from_me |
has_dd_results |
int |
unknown, usually 0 |
is_service_message |
int |
unknown, usually 0 |
is_forward |
int |
unknown, usually 0 |
was_downgraded |
int |
unknown, usually 0, rarely 1 |
is_archive |
int |
unknown, usually 0 |
cache_has_attachments |
int |
unknown, usually 0 |
cache_roomnames |
text |
unknown, usually null |
was_data_detected |
int |
unknown, usually 0 |
was_deduplicated |
int |
unknown, usually 0 |
[Show Handle Table][Hide Handle Table]
Column |
Type |
Description |
ROWID |
int |
auto incrementing id |
id |
text |
phone number |
country |
text |
country code e.g. ‘au’ |
service |
text |
‘SMS’ or ‘iMessage’ |
uncanonicalized_id |
text |
non formatted version of phone number. usually blank. |
[Show Chat Table][Hide Chat Table]
Column |
Type |
Description |
ROWID |
int |
auto incrementing id |
guid |
text |
unique identifier used by iMessage |
style |
int |
unknown, seems to be 45 or 43 |
state |
int |
unknown, mostly 3, sometimes 2 or 0 |
account_id |
text |
seems to be a guid for the account |
properties |
blob |
unknown contents |
chat_identifier |
text |
recipient phone/chat room name/email |
service_name |
text |
‘SMS’ or ‘iMessage’ |
room_name |
text |
‘chat’ + number, e.g. chat246660806475640255 |
account_login |
text |
account used, e.g. P:+61411222333 or E:email@email.com |
is_archived |
int |
0 |
last_addressed_handle |
text |
seems to be the user’s phone number or email |
display_name |
text |
null |
That’s it for now. I’ll continue this blog post another time, though I hope you have found this helpful so far.
Edit. I spent about 2 hours creating custom shortcodes for wordpress to implement those [Show/Hide] tags. I’ll finish this another day.