messages

iMessage is the built-in system application to send messages. Initially (in earlier iOS versions) this was only for sending and receiving SMS (text) messages. The icon on the SpringBoard is named Messages, but Apple's official name is iMessage (see references). To backup or restore the data of this application without iTunes, the following information might be useful. Initial data for this analysis comes from an iPhone 3GS with firmware 3.1.3, which was later restored / upgraded to an iPhone 4 and finally to an iPhone 4S with iOS 5.0.1. On lower or higher firmware versions there are differences in the data.

In the folder /var/mobile/Library/SMS/ there are the following files:

• sms.db
• sms.db-shm
• sms.db-wal
• sms-legacy.db
• folder Attachments
• folder Drafts
• folder Parts

The files which end in -shm and -wal are probably used for indexing and can be recreated if deleted (?). The legacy file was not on my iPhone, but another user claimed to have this file (see jbqa reference). So we're just looking at the file sms.db, this is an SQLite database with the following tables in it:

• sqlite_master
• _SqliteDatabaseProperties
• msg_group
• group_member
• message
• msg_pieces
• madrid_attachment
• madrid_chat

The following indexes are defined:

index name	table name	field name(s)
sqlite_autoindex__SqliteDatabaseProperties_1	_SqliteDatabaseProperties
message_group_index	message	group_id, ROWID
message_flags_index	message	flags
pieces_message_index	msg_pieces	message_id
madrid_attachment_message_index	madrid_attachment	message_id
madrid_attachment_guid_index	madrid_attachment	attachment_guid
madrid_attachment_filename_index	madrid_attachment	filename
madrid_guid_index	message	madrid_guid
group_id_index	group_member	group_id
madrid_chat_style_index	madrid_chat	style
madrid_chat_state_index	madrid_chat	state
madrid_chat_account_id_index	madrid_chat	account_id
madrid_chat_chat_identifier_index	madrid_chat	chat_identifier
madrid_chat_service_name_index	madrid_chat	service_name
madrid_chat_guid_index	madrid_chat	guid
madrid_chat_room_name_index	madrid_chat	room_name
madrid_chat_account_login_index	madrid_chat	account_login
madrid_roomname_service_index	message	madrid_roomname, madrid_service
madrid_handle_service_index	message	madrid_handle, madrid_service

The following triggers are defined:

trigger name	criteria 1	criteria 2	action
insert_unread_message	AFTER INSERT	WHEN NOT read(new.flags)	UPDATE msg_group SET unread_count = (SELECT unread_count FROM msg_group WHERE ROWID = new.group_id) + 1 WHERE ROWID = new.group_id;
mark_message_unread	AFTER UPDATE	WHEN read(old.flags) AND NOT read(new.flags)	UPDATE msg_group SET unread_count = (SELECT unread_count FROM msg_group WHERE ROWID = new.group_id) + 1 WHERE ROWID = new.group_id;
mark_message_read	AFTER UPDATE	WHEN NOT read(old.flags) AND read(new.flags)	UPDATE msg_group SET unread_count = (SELECT unread_count FROM msg_group WHERE ROWID = new.group_id) - 1 WHERE ROWID = new.group_id;
delete_message	AFTER DELETE	WHEN NOT read(old.flags)	UPDATE msg_group SET unread_count = (SELECT unread_count FROM msg_group WHERE ROWID = old.group_id) - 1 WHERE ROWID = old.group_id;
insert_newest_message	AFTER INSERT	WHEN new.ROWID >= IFNULL((SELECT MAX(ROWID) FROM message WHERE message.group_id = new.group_id), 0)	UPDATE msg_group SET newest_message = new.ROWID WHERE ROWID = new.group_id;
delete_newest_message	AFTER DELETE	WHEN old.ROWID = (SELECT newest_message FROM msg_group WHERE ROWID = old.group_id)	UPDATE msg_group SET newest_message = (SELECT ROWID FROM message WHERE group_id = old.group_id AND ROWID = (SELECT max(ROWID) FROM message WHERE group_id = old.group_id)) WHERE ROWID = old.group_id;
delete_pieces	AFTER DELETE	-	DELETE from msg_pieces where old.ROWID == msg_pieces.message_id;

All defined triggers act on the table message, therefore this is not mentioned in this list with a separate column.

Please note that all date values are stored as a number which means the number of seconds since 1 Jan 2001.

The word "madrid" was the codename for iMessage, before this was a product name (according to pytey, see reference).

Tables

sqlite_master

This table is contained in every SQLite database and contains general information about the content. It has these fields:

field name	description
type	either 'table' or 'index' or 'trigger'
name	name of the table or index or trigger
tbl_name	same as name for table, related table name for index and trigger
rootpage	integer, internal id where to find the data (?), or 0 for trigger
sql	creation statement

This is the content of this table:

type	name	tbl_name	rootpage	sql
table	_SqliteDatabaseProperties	_SqliteDatabaseProperties	3	CREATE TABLE _SqliteDatabaseProperties (key TEXT, value TEXT, UNIQUE(key))
table	message	message	5	CREATE TABLE message (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, address TEXT, date INTEGER, text TEXT, flags INTEGER, replace INTEGER, svc_center TEXT, group_id INTEGER, association_id INTEGER, height INTEGER, UIFlags INTEGER, version INTEGER, subject TEXT, country TEXT, headers BLOB, recipients BLOB, read INTEGER, madrid_attributedBody BLOB, madrid_handle TEXT, madrid_version INTEGER, madrid_guid TEXT, madrid_type INTEGER, madrid_roomname TEXT, madrid_service TEXT, madrid_account TEXT, madrid_flags INTEGER, madrid_attachmentInfo BLOB, madrid_url TEXT, madrid_error INTEGER, is_madrid INTEGER, madrid_date_read INTEGER, madrid_date_delivered INTEGER, madrid_account_guid TEXT)
table	sqlite_sequence	sqlite_sequence	6	CREATE TABLE sqlite_sequence(name,seq)
table	msg_group	msg_group	7	CREATE TABLE msg_group (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, type INTEGER, newest_message INTEGER, unread_count INTEGER, hash INTEGER)
table	group_member	group_member	8	CREATE TABLE group_member (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, group_id INTEGER, address TEXT, country TEXT)
table	msg_pieces	msg_pieces	9	CREATE TABLE msg_pieces (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, message_id INTEGER, data BLOB, part_id INTEGER, preview_part INTEGER, content_type TEXT, height INTEGER, version INTEGER, flags INTEGER, content_id TEXT, content_loc TEXT, headers BLOB)
table	madrid_attachment	madrid_attachment	13	CREATE TABLE madrid_attachment (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, attachment_guid TEXT, created_date INTEGER, start_date INTEGER, filename TEXT, uti_type TEXT, mime_type TEXT, transfer_state INTEGER, is_incoming INTEGER, message_id INTEGER)
table	madrid_chat	madrid_chat	25	CREATE TABLE madrid_chat (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, style INTEGER, state INTEGER, account_id TEXT, properties BLOB, chat_identifier TEXT, service_name TEXT, guid TEXT, room_name TEXT, account_login TEXT, participants BLOB)
trigger	insert_unread_message	message	0	CREATE TRIGGER insert_unread_message AFTER INSERT ON message WHEN NOT read(new.flags) BEGIN UPDATE msg_group SET unread_count = (SELECT unread_count FROM msg_group WHERE ROWID = new.group_id) + 1 WHERE ROWID = new.group_id; END
trigger	mark_message_unread	message	0	CREATE TRIGGER mark_message_unread AFTER UPDATE ON message WHEN read(old.flags) AND NOT read(new.flags) BEGIN UPDATE msg_group SET unread_count = (SELECT unread_count FROM msg_group WHERE ROWID = new.group_id) + 1 WHERE ROWID = new.group_id; END
trigger	mark_message_read	message	0	CREATE TRIGGER mark_message_read AFTER UPDATE ON message WHEN NOT read(old.flags) AND read(new.flags) BEGIN UPDATE msg_group SET unread_count = (SELECT unread_count FROM msg_group WHERE ROWID = new.group_id) - 1 WHERE ROWID = new.group_id; END
trigger	delete_message	message	0	CREATE TRIGGER delete_message AFTER DELETE ON message WHEN NOT read(old.flags) BEGIN UPDATE msg_group SET unread_count = (SELECT unread_count FROM msg_group WHERE ROWID = old.group_id) - 1 WHERE ROWID = old.group_id; END
trigger	insert_newest_message	message	0	CREATE TRIGGER insert_newest_message AFTER INSERT ON message WHEN new.ROWID >= IFNULL((SELECT MAX(ROWID) FROM message WHERE message.group_id = new.group_id), 0) BEGIN UPDATE msg_group SET newest_message = new.ROWID WHERE ROWID = new.group_id; END
trigger	delete_newest_message	message	0	CREATE TRIGGER delete_newest_message AFTER DELETE ON message WHEN old.ROWID = (SELECT newest_message FROM msg_group WHERE ROWID = old.group_id) BEGIN UPDATE msg_group SET newest_message = (SELECT ROWID FROM message WHERE group_id = old.group_id AND ROWID = (SELECT max(ROWID) FROM message WHERE group_id = old.group_id)) WHERE ROWID = old.group_id; END
trigger	delete_pieces	message	0	CREATE TRIGGER delete_pieces AFTER DELETE ON message BEGIN DELETE from msg_pieces where old.ROWID == msg_pieces.message_id; END
index	sqlite_autoindex__SqliteDatabaseProperties_1	_SqliteDatabaseProperties	4	NULL
index	message_group_index	message	10	CREATE INDEX message_group_index ON message(group_id, ROWID)
index	message_flags_index	message	11	CREATE INDEX message_flags_index ON message(flags)
index	pieces_message_index	msg_pieces	12	CREATE INDEX pieces_message_index ON msg_pieces(message_id)
index	madrid_attachment_message_index	madrid_attachment	14	CREATE INDEX madrid_attachment_message_index ON madrid_attachment(message_id)
index	madrid_attachment_guid_index	madrid_attachment	15	CREATE INDEX madrid_attachment_guid_index ON madrid_attachment(attachment_guid)
index	madrid_attachment_filename_index	madrid_attachment	16	CREATE INDEX madrid_attachment_filename_index ON madrid_attachment(filename)
index	madrid_guid_index	message	18	CREATE INDEX madrid_guid_index ON message(madrid_guid)
index	group_id_index	group_member	24	CREATE INDEX group_id_index ON group_member(group_id)
index	madrid_chat_style_index	madrid_chat	26	CREATE INDEX madrid_chat_style_index ON madrid_chat(style)
index	madrid_chat_state_index	madrid_chat	27	CREATE INDEX madrid_chat_state_index ON madrid_chat(state)
index	madrid_chat_account_id_index	madrid_chat	23	CREATE INDEX madrid_chat_account_id_index ON madrid_chat(account_id)
index	madrid_chat_chat_identifier_index	madrid_chat	22	CREATE INDEX madrid_chat_chat_identifier_index ON madrid_chat(chat_identifier)
index	madrid_chat_service_name_index	madrid_chat	21	CREATE INDEX madrid_chat_service_name_index ON madrid_chat(service_name)
index	madrid_chat_guid_index	madrid_chat	20	CREATE INDEX madrid_chat_guid_index ON madrid_chat(guid)
index	madrid_chat_room_name_index	madrid_chat	19	CREATE INDEX madrid_chat_room_name_index ON madrid_chat(room_name)
index	madrid_chat_account_login_index	madrid_chat	17	CREATE INDEX madrid_chat_account_login_index ON madrid_chat(account_login)
index	madrid_roomname_service_index	message	28	CREATE INDEX madrid_roomname_service_index ON message(madrid_roomname, madrid_service)
index	madrid_handle_service_index	message	29	CREATE INDEX madrid_handle_service_index ON message(madrid_handle, madrid_service)

Please note that the values for rootpage might differ in every database.

_SqliteDatabaseProperties

This seems to be a general-purpose table to store some configuration values.

field name	type
key	TEXT
value	TEXT
UNIQUE(key)

These values are stored in the table:

key	value	description
counter_last_reset	0	?
_UniqueIdentifier	960B6637-167E-44A1-86E5-90E3DFE768AC	?
_ClientVersion	21	?
counter_out_all	254	?
counter_out_lifetime	254	?
counter_in_all	468	?
counter_in_lifetime	468	?
__CPRecordSequenceNumber	2835	?

message

This is the main table where all messages are stored:

field name	type	value / description
ROWID	INTEGER PRIMARY KEY AUTOINCREMENT	primary key
address	TEXT	NULL or a name or a phone number (with or without spaces)
date	INTEGER	message date
text	TEXT	message content
flags	INTEGER	unknown, possible values: 0, 2, 3, 5, 35, 16387.
replace	INTEGER	unknown, possible values: 0, 1, 2.
svc_center	TEXT	NULL
group_id	INTEGER	0 or foreign key to group
association_id	INTEGER	normally 0, but sometimes a big value like 1268160928
height	INTEGER	always 0
UIFlags	INTEGER	unknown, possible values: 0, 4, 5.
version	INTEGER	always 0
subject	TEXT	always NULL
country	TEXT	NULL or 'ch'
headers	BLOB	always NULL
recipients	BLOB	normally NULL, one entry had an xml value in it
read	INTEGER	0 or 1
madrid_attributedBody	BLOB	blob (?)
madrid_handle	TEXT	NULL or a phone number
madrid_version	INTEGER	NULL or 0
madrid_guid	TEXT	GUID or NULL
madrid_type	INTEGER	0 or NULL
madrid_roomname	TEXT	NULL
madrid_service	TEXT	'Madrid' or NULL
madrid_account	TEXT	NULL or 'p:' & own phone number
madrid_flags	INTEGER	unknown, poster says: NULL, 12289 (in), 45061 (?), 36869 (out).
madrid_attachmentInfo	BLOB	NULL or blob
madrid_url	TEXT	always an empty string
madrid_error	INTEGER	empty string or 0
is_madrid	INTEGER	value 0 or 1 (0=SMS, 1=iMessage)
madrid_date_read	INTEGER	empty string or 0 or an integer value
madrid_date_delivered	INTEGER	0 or 1
madrid_account_guid	TEXT	GUID or empty

sqlite_sequence

This table does not define data types. This is the data in the table:

name	seq
msg_group	71
message	784
group_member	71
msg_pieces	3
madrid_chat	3
madrid_attachment	3

msg_group

This defines a chat (SMS/text/message) conversation group.

field name	type	value / description
ROWID	INTEGER PRIMARY KEY AUTOINCREMENT	primary key
type	INTEGER	always 0
newest_message	INTEGER	foreign key to message
unread_count	INTEGER	always 0
hash	INTEGER	signed 32-bit integer value (calculation?)

group_member

This defines the member of the chat (SMS/text/message) conversation group. As you currently cannot define groups, there is always only one member in each group, so the group_id and ROWID values match.

field name	type	value / description
ROWID	INTEGER PRIMARY KEY AUTOINCREMENT	primary key
group_id	INTEGER	foreign key to msg_group, value matches the primary key from this table
address	TEXT	name or phone number (with or without spaces)
country	TEXT	'ch' for Switzerland

msg_pieces

This table contains information about MMS objects. (?)

field name	type	value / description
ROWID	INTEGER PRIMARY KEY AUTOINCREMENT	primary key
message_id	INTEGER	foreign key to message
data	BLOB	NULL
part_id	INTEGER	0
preview_part	INTEGER	0 or -1
content_type	TEXT	empty string or 'image/jpeg'
height	INTEGER	0
version	INTEGER	1
flags	INTEGER	0
content_id	TEXT	NULL or 1
content_loc	TEXT	image file name without path (like 'IMG_0104.JPG')
headers	BLOB	NULL

madrid_attachment

field name	type	value / description
ROWID	INTEGER PRIMARY KEY AUTOINCREMENT	primary key
attachment_guid	TEXT	GUID - this matches the subfolder name in the folder Attachments
created_date	INTEGER	unsigned integer value with the creation date
start_date	INTEGER	0
filename	TEXT	complete filename (with path)
uti_type	TEXT	'public.jpeg' or 'public.vcard'
mime_type	TEXT	'image/jpeg' or 'text/vcard'
transfer_state	INTEGER	5
is_incoming	INTEGER	0
message_id	INTEGER	-1

madrid_chat

field name	type	value / description
ROWID	INTEGER PRIMARY KEY AUTOINCREMENT	primary key
style	INTEGER	45
state	INTEGER	3
account_id	TEXT	GUID, always the same for me
properties	BLOB	NULL or a bplist (?)
chat_identifier	TEXT	like a phone number
service_name	TEXT	'Madrid'
guid	TEXT	almost same as chat_identifier (leading '-')
room_name	TEXT	NULL
account_login	TEXT	almost same as chat_identifier (leading 'P:')
participants	BLOB	bplist (?)

References

• Apple's description of iMessage
• SCIP article (german only)
• pytey explaining "Madrid"
• jbqa: How to backup/restore SMS without iTunes
• script to merge two SMS databases