Difference between revisions of "Messages"

From The iPhone Wiki
Jump to: navigation, search
(added a little more information)
m (Updating.)
 
(20 intermediate revisions by 7 users not shown)
Line 1: Line 1:
  +
[[Image:IMessage icon.png|thumb|Messages in iOS 5]]
{{lowercase}}
 
[[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. Support for sending and receiving MMS messages was introduced in iOS 3.0 The icon on the [[SpringBoard]] is named '''Messages''', but Apple's official name is '''iMessage''' (see references).
+
[[Messages]] is the built-in system application to send messages. Initially (in earlier iOS versions) this was only for sending and receiving SMS (text) messages. Support for sending and receiving MMS messages was introduced in iOS 3.0, while support for sending and receiving iMessages was introduced in iOS 5. The application itself is located in <code>/Applications/MobileSMS.app/</code>.
  +
  +
==iMessage==
  +
With iOS 5 Apple introduced the iCloud based instant messaging service, iMessage, which works across different iOS devices and Mac OS X, and is the first native messaging service for iPod touch and iPad. Messages for Mac OS X was introduced with OS X Mountain Lion, and replaced the iChat instant messaging program.
  +
  +
== Activation ==
 
iMessages on iOS is activated in the same way as [[FaceTime]], with a silent SMS being sent to a carrier defined number and a successful registration SMS being returned. This 'verifies' the phone number for sending and receiving iMessages. Email (AppleID) verification does not use silent SMS, and only sends an email containing a verification link. This is the only method for iMessages on the iPad, iPod touch and Mac OS X.
 
iMessages on iOS is activated in the same way as [[FaceTime]], with a silent SMS being sent to a carrier defined number and a successful registration SMS being returned. This 'verifies' the phone number for sending and receiving iMessages. Email (AppleID) verification does not use silent SMS, and only sends an email containing a verification link. This is the only method for iMessages on the iPad, iPod touch and Mac OS X.
   
 
iMessage is deeply integrated and built around Apple's Push Notification Server over port 5223.
 
iMessage is deeply integrated and built around Apple's Push Notification Server over port 5223.
   
  +
== Serialization ==
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 [[N88ap|iPhone 3GS]] with firmware 3.1.3, which was later restored / upgraded to an [[N90ap|iPhone 4]] and finally to an [[N94ap|iPhone 4S]] with iOS 5.0.1. On lower or higher firmware versions there are differences in the data.
 
  +
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 [[N88AP|iPhone 3GS]] with firmware 3.1.3, which was later restored / upgraded to an [[N90AP|iPhone 4]] and finally to an [[N94AP|iPhone 4S]] with iOS 5.0.1. On lower or higher firmware versions there are differences in the data.
   
 
In the folder <code>/var/mobile/Library/SMS/</code> there are the following files:
 
In the folder <code>/var/mobile/Library/SMS/</code> there are the following files:
Line 15: Line 21:
 
*folder <code>Drafts</code>
 
*folder <code>Drafts</code>
 
*folder <code>Parts</code>
 
*folder <code>Parts</code>
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 <code>sms.db</code>, this is an [http://www.sqlite.org/ SQLite database] with the following tables in it:
+
The files <code>sms.db-shm</code> and <code>sms.db-wal</code> are probably used for indexing or search and can be recreated if deleted (?). If the database is updated manually, it's important to recreate these files. On a jailbroken device, they could be recreated using the <code>sqlite3</code> program, for example by issuing the following command: <code>sqlite3 sms.db .dump>/dev/null</code>.<br />
  +
The <code>sms-legacy.db</code> file was not on my iPhone, but another user claimed to have this file (see jbqa reference).<br />
  +
The [[iTunes Backup]] filename of sms.db is <code>3d0d7e5fb2ce288813306e4d4636395e047a3d28</code>.
  +
  +
The <code>Attachments</code> folder contains subfolders and files, which are referenced in the main database file in the table madrid_attachment (see there).
  +
  +
=== Drafts ===
  +
In the folder <code>Drafts</code> there is a file <code>PENDING-recipients.plist</code> (if there is a pending draft with already recipients added) and the content is a plist with an array of strings with the recipients (phone numbers). There is also a subfolder <code>PENDING.draft</code> with a file <code>message.plist</code> containing a '''dict''' with the following three values:
  +
*'''markupString''': The text in HTML format (HTML encoded) that has been entered already
  +
*'''resources''': array of dict for every image:
  +
**'''duration''' (integer): '''0''' for images, number of seconds (duration) for videos
  +
**'''exportedFilename''': (filename, without path, ending in .jpg, .png or .3gp)
  +
**'''mimeType''': '''image/jpeg''' or '''image/png''' or '''video/x-internal-iphone'''
  +
*'''textString''': The text that has been entered already (white spaces and line breaks retained)
  +
Images are put into the markupString like this:
  +
<img id="n"
  +
style="display:block;margin-left:-6px;padding-top:5px;padding-bottom:3px"
  +
width="97px" height="110px"
  +
src="x-ckmsgpart:4-n-0">
  +
n is the image number, starting with 0. The width varies slightly, depending on the image width/height ratio, but the height is always 110px (90px for videos). The images theirself are stored in the same folder with corresponding names like <code>data-0</code>, <code>data-1</code>, etc.
  +
  +
In case of a video, the data file is not the video itself, but a bplist with this content (dict):
  +
*'''CKSMSComposeOptionFilenameKey''' (string): '''/var/tmp/capture/capturedvideo.MOV''' or '''/var/tmp/capture-T0x11d520.tmp.OEBOK7/capturedvideo.MOV''' (or similar)
  +
*'''CKSMSComposeOptionPreviewImageDataKey''' (data): big blob (base64 encoded)
  +
For the blob, this was in my test only 5510 byte long, so it's not the movie itself. The only strings in the blob were "JFIF" and "Exif" somewhere near the beginning, so it can be assumed this is metadata or the screenshot shown in the message bubble.
  +
  +
===Main database file===
  +
So we're just looking at the file <code>sms.db</code>, this is an [http://www.sqlite.org/ SQLite database] with the following tables in it:
 
*sqlite_master
 
*sqlite_master
 
*_SqliteDatabaseProperties
 
*_SqliteDatabaseProperties
Line 25: Line 58:
 
*madrid_chat
 
*madrid_chat
   
  +
===Indexes===
 
The following indexes are defined:
 
The following indexes are defined:
 
{| class="wikitable" style="font-size: smaller; text-align: center; table-layout: fixed; border-collapse: collapse;" border="1"
 
{| class="wikitable" style="font-size: smaller; text-align: center; table-layout: fixed; border-collapse: collapse;" border="1"
Line 109: Line 143:
 
|}
 
|}
   
  +
===Triggers===
 
The following triggers are defined:
 
The following triggers are defined:
 
{| class="wikitable" style="font-size: smaller; text-align: center; table-layout: fixed; border-collapse: collapse;" border="1"
 
{| class="wikitable" style="font-size: smaller; text-align: center; table-layout: fixed; border-collapse: collapse;" border="1"
 
|-
 
|-
 
!trigger name
 
!trigger name
!criteria 1
+
!criteria&nbsp;1
!criteria 2
+
!criteria&nbsp;2
 
!action
 
!action
 
|-
 
|-
 
|insert_unread_message
 
|insert_unread_message
  +
|
|AFTER INSERT
 
  +
AFTER INSERT
|style="text-align:left;" | WHEN NOT read(new.flags)
 
  +
|style="text-align:left;" |
|style="text-align:left;" | UPDATE msg_group SET unread_count = (SELECT unread_count FROM msg_group WHERE ROWID = new.group_id) + 1 WHERE ROWID = new.group_id;
 
  +
WHEN NOT read(new.flags)
  +
|style="text-align:left;" |
  +
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
 
|mark_message_unread
  +
|
|AFTER UPDATE
 
  +
AFTER UPDATE
|style="text-align:left;" | WHEN read(old.flags) AND NOT read(new.flags)
 
  +
|style="text-align:left;" |
|style="text-align:left;" | UPDATE msg_group SET unread_count = (SELECT unread_count FROM msg_group WHERE ROWID = new.group_id) + 1 WHERE ROWID = new.group_id;
 
  +
WHEN read(old.flags)
  +
AND NOT read(new.flags)
  +
|style="text-align:left;" |
  +
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
 
|mark_message_read
  +
|
|AFTER UPDATE
 
  +
AFTER UPDATE
|style="text-align:left;" | WHEN NOT read(old.flags) AND read(new.flags)
 
  +
|style="text-align:left;" |
|style="text-align:left;" | UPDATE msg_group SET unread_count = (SELECT unread_count FROM msg_group WHERE ROWID = new.group_id) - 1 WHERE ROWID = new.group_id;
 
  +
WHEN NOT read(old.flags)
  +
AND read(new.flags)
  +
|style="text-align:left;" |
  +
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
 
|delete_message
  +
|
|AFTER DELETE
 
  +
AFTER DELETE
|style="text-align:left;" | WHEN NOT read(old.flags)
 
  +
|style="text-align:left;" |
|style="text-align:left;" | UPDATE msg_group SET unread_count = (SELECT unread_count FROM msg_group WHERE ROWID = old.group_id) - 1 WHERE ROWID = old.group_id;
 
  +
WHEN NOT read(old.flags)
  +
|style="text-align:left;" |
  +
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
 
|insert_newest_message
  +
|
|AFTER INSERT
 
  +
AFTER INSERT
|style="text-align:left;" | WHEN new.ROWID >= IFNULL((SELECT MAX(ROWID) FROM message WHERE message.group_id = new.group_id), 0)
 
|style="text-align:left;" | UPDATE msg_group SET newest_message = new.ROWID WHERE ROWID = new.group_id;
+
|style="text-align:left;" |
  +
WHEN new.ROWID >= IFNULL(
  +
(
  +
SELECT MAX(ROWID)
  +
FROM message
  +
WHERE message.group_id = new.group_id
  +
), 0
  +
)
  +
|style="text-align:left;" |
  +
UPDATE msg_group
  +
SET newest_message = new.ROWID
  +
WHERE ROWID = new.group_id;
 
|-
 
|-
 
|delete_newest_message
 
|delete_newest_message
  +
|
|AFTER DELETE
 
  +
AFTER DELETE
|style="text-align:left;" | WHEN old.ROWID = (SELECT newest_message FROM msg_group WHERE ROWID = old.group_id)
 
  +
|style="text-align:left;" |
|style="text-align:left;" | 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;
 
  +
WHEN old.ROWID = (
  +
SELECT newest_message
  +
FROM msg_group
  +
WHERE ROWID = old.group_id
  +
)
  +
|style="text-align:left;" |
  +
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
 
|delete_pieces
  +
|
|AFTER DELETE
 
  +
AFTER DELETE
|style="text-align:left;" | <nowiki>-</nowiki>
 
  +
|<nowiki>-</nowiki>
|style="text-align:left;" | DELETE from msg_pieces where old.ROWID == msg_pieces.message_id;
 
  +
|style="text-align:left;" |
  +
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.
 
All defined triggers act on the table ''message'', therefore this is not mentioned in this list with a separate column.
  +
  +
From the trigger statements it is visible that there must be a function called read(x) which is used on the field message.flags. As SQLite does not have this function, but is open-sourced, this has probably been added into the code to facilitate handling the read-flag of a message. Please note that there is also a column called 'read'.
   
 
Please note that all date values are stored as a number which means the number of seconds since 1 Jan 2001.
 
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 [[User:pytey|pytey]], see reference).
+
The word "madrid" was the codename for [[iMessage]], before this was a product name (according to [[pytey]], see reference).
   
 
==Tables==
 
==Tables==
Line 434: Line 542:
 
|counter_out_all
 
|counter_out_all
 
|254
 
|254
|style="text-align:left;" | ?
+
|style="text-align:left;" | counts the number of outgoing messages (since last counter reset)
 
|-
 
|-
 
|counter_out_lifetime
 
|counter_out_lifetime
 
|254
 
|254
|style="text-align:left;" | ?
+
|style="text-align:left;" | counts the number of outgoing messages (since forever, isn't affected by an counter reset)
 
|-
 
|-
 
|counter_in_all
 
|counter_in_all
 
|468
 
|468
|style="text-align:left;" | ?
+
|style="text-align:left;" | counts the number of incoming messages (since last counter reset)
 
|-
 
|-
 
|counter_in_lifetime
 
|counter_in_lifetime
 
|468
 
|468
|style="text-align:left;" | ?
+
|style="text-align:left;" | counts the number of incoming messages (since forever, isn't affected by an counter reset)
 
|-
 
|-
 
|__CPRecordSequenceNumber
 
|__CPRecordSequenceNumber
Line 467: Line 575:
 
|address
 
|address
 
|TEXT
 
|TEXT
|style="text-align:left;" | NULL or a name or a phone number (with or without spaces)
+
|style="text-align:left;" | NULL or a name or a phone number (with or without spaces) of the other person (sent to or received from)
 
|-
 
|-
 
|date
 
|date
Line 479: Line 587:
 
|flags
 
|flags
 
|INTEGER
 
|INTEGER
|style="text-align:left;" | unknown, possible values: 0, 2, 3, 5, 35, 16387.
+
|style="text-align:left;" | unknown, possible values: 0, 2, 3, 5, 35, 16387. Probably a bit-set. The value 35 was set in a SMS that couldn't get sent out and is stil marked with a red exclamation mark letting you send it again.
 
|-
 
|-
 
|replace
 
|replace
 
|INTEGER
 
|INTEGER
|style="text-align:left;" | unknown, possible values: 0, 1, 2.
+
|style="text-align:left;" | unknown, possible values: 0, 1, 2
 
|-
 
|-
 
|svc_center
 
|svc_center
Line 491: Line 599:
 
|group_id
 
|group_id
 
|INTEGER
 
|INTEGER
|style="text-align:left;" | 0 or foreign key to group
+
|style="text-align:left;" | 0 or foreign key to msg_group.ROWID
 
|-
 
|-
 
|association_id
 
|association_id
 
|INTEGER
 
|INTEGER
|style="text-align:left;" | normally 0, but sometimes a big value like 1268160928
+
|style="text-align:left;" | often 0, but sometimes a copy of the date field
 
|-
 
|-
 
|height
 
|height
Line 503: Line 611:
 
|UIFlags
 
|UIFlags
 
|INTEGER
 
|INTEGER
|style="text-align:left;" | unknown, possible values: 0, 4, 5.
+
|style="text-align:left;" | unknown, possible values: 0, 4, 5, 6, 7
 
|-
 
|-
 
|version
 
|version
Line 511: Line 619:
 
|subject
 
|subject
 
|TEXT
 
|TEXT
|style="text-align:left;" | always NULL
+
|style="text-align:left;" | The subject of an iMessage/MMS-message, or NULL if it's an SMS or if subject is not used on a iMessage/MMS-message
 
|-
 
|-
 
|country
 
|country
 
|TEXT
 
|TEXT
|style="text-align:left;" | NULL or 'ch'
+
|style="text-align:left;" | NULL or an ISO country code (eg: 'ch' for Switzerland)
 
|-
 
|-
 
|headers
 
|headers
Line 527: Line 635:
 
|read
 
|read
 
|INTEGER
 
|INTEGER
|style="text-align:left;" | 0 or 1
+
|style="text-align:left;" | 0 or 1 (Assume 0 = unread and 1 is read though Madrid messages are always 0 so it probably doesn't apply to them)
 
|-
 
|-
 
|madrid_attributedBody
 
|madrid_attributedBody
 
|BLOB
 
|BLOB
|style="text-align:left;" | blob (?)
+
|style="text-align:left;" | blob, content unknown. The only strings in it are "JFIF" and "Exif", so this is probably meta-data.
 
|-
 
|-
 
|madrid_handle
 
|madrid_handle
 
|TEXT
 
|TEXT
|style="text-align:left;" | NULL or a phone number
+
|style="text-align:left;" | NULL if not an iMessage or a phone number of the other person (sender or receiver)
 
|-
 
|-
 
|madrid_version
 
|madrid_version
 
|INTEGER
 
|INTEGER
|style="text-align:left;" | NULL or 0
+
|style="text-align:left;" | NULL if pre-iOS 5.0 or 0
 
|-
 
|-
 
|madrid_guid
 
|madrid_guid
 
|TEXT
 
|TEXT
|style="text-align:left;" | GUID or NULL
+
|style="text-align:left;" | GUID (unique to the message) or NULL if not an iMessage
 
|-
 
|-
 
|madrid_type
 
|madrid_type
 
|INTEGER
 
|INTEGER
|style="text-align:left;" | 0 or NULL
+
|style="text-align:left;" | 0 or NULL if pre-iOS 5.0
 
|-
 
|-
 
|madrid_roomname
 
|madrid_roomname
Line 555: Line 663:
 
|madrid_service
 
|madrid_service
 
|TEXT
 
|TEXT
|style="text-align:left;" | 'Madrid' or NULL
+
|style="text-align:left;" | 'Madrid' or NULL if not an iMessage
 
|-
 
|-
 
|madrid_account
 
|madrid_account
 
|TEXT
 
|TEXT
|style="text-align:left;" | NULL or 'p:' & own phone number
+
|style="text-align:left;" | NULL if not an iMessage or 'p:' & own phone number or 'e:' & email registered for iMessage
 
|-
 
|-
 
|madrid_flags
 
|madrid_flags
 
|INTEGER
 
|INTEGER
|style="text-align:left;" | unknown, poster says: NULL, 12289 (in), 45061 (?), 36869 (out).
+
|style="text-align:left;" | message type. Known values: NULL (if not an iMessage), 12289 (received), 32773 (send error), 36869 (sent), 45061 (sent), 77825 (received message containing parsed data eg: phone, email, website), 102405 (sent message containing parsed data eg: phone, email, website)
 
|-
 
|-
 
|madrid_attachmentInfo
 
|madrid_attachmentInfo
 
|BLOB
 
|BLOB
|style="text-align:left;" | NULL or blob
+
|style="text-align:left;" | NULL or blob. The blob contains these strings: streamtyped, NSMutableArray, NSArray, NSObject, NSMutableString, NSString, and a GUID. Format unknown.
 
|-
 
|-
 
|madrid_url
 
|madrid_url
Line 575: Line 683:
 
|madrid_error
 
|madrid_error
 
|INTEGER
 
|INTEGER
|style="text-align:left;" | empty string or 0
+
|style="text-align:left;" | empty string if message is pre-iOS 5.0 or 0 if after
 
|-
 
|-
 
|is_madrid
 
|is_madrid
 
|INTEGER
 
|INTEGER
|style="text-align:left;" | value 0 or 1 (0=SMS, 1=iMessage)
+
|style="text-align:left;" | specifies if it's an iMessage or not, value 0 or 1 (0=SMS/MMS, 1=iMessage)
 
|-
 
|-
 
|madrid_date_read
 
|madrid_date_read
 
|INTEGER
 
|INTEGER
|style="text-align:left;" | empty string or 0 or an integer value
+
|style="text-align:left;" | null if message is pre-iOS 5.0, 0 if sms or a sent iMessage, integer value representing the date read
 
|-
 
|-
 
|madrid_date_delivered
 
|madrid_date_delivered
 
|INTEGER
 
|INTEGER
|style="text-align:left;" | 0 or 1
+
|style="text-align:left;" | null if message is pre-iOS 5.0, 0 if sms or a received iMessage, integer value representing the date sent
 
|-
 
|-
 
|madrid_account_guid
 
|madrid_account_guid
 
|TEXT
 
|TEXT
|style="text-align:left;" | GUID or empty
+
|style="text-align:left;" | GUID of account used (multiple entries may be found representing either the phone or email registered with iMessage) or empty if not an iMessage
 
|}
 
|}
   
Line 634: Line 742:
 
|type
 
|type
 
|INTEGER
 
|INTEGER
|style="text-align:left;" | always 0
+
|style="text-align:left;" | 0 if only one recipient, 1 if multiple recipients
 
|-
 
|-
 
|newest_message
 
|newest_message
 
|INTEGER
 
|INTEGER
|style="text-align:left;" | foreign key to message
+
|style="text-align:left;" | foreign key to message. This value is auto-updated by triggers when inserting / deleting a message, so that this always points to the newest message.
 
|-
 
|-
 
|unread_count
 
|unread_count
 
|INTEGER
 
|INTEGER
  +
|style="text-align:left;" | The number of unread messages in this group. This value is auto-incremented/decremented by triggers when inserting or deleting an unread message or when marking a message as read or unread.
|style="text-align:left;" | always 0
 
 
|-
 
|-
 
|hash
 
|hash
Line 663: Line 771:
 
|group_id
 
|group_id
 
|INTEGER
 
|INTEGER
|style="text-align:left;" | foreign key to msg_group, value matches the primary key from this table
+
|style="text-align:left;" | foreign key to msg_group.ROWID
 
|-
 
|-
 
|address
 
|address
Line 671: Line 779:
 
|country
 
|country
 
|TEXT
 
|TEXT
|style="text-align:left;" | 'ch' for Switzerland
+
|style="text-align:left;" | ISO country code eg: 'ch' for Switzerland
 
|}
 
|}
   
 
===msg_pieces===
 
===msg_pieces===
 
This table contains information about MMS objects. (?)
 
This table contains information about MMS objects. (?)
  +
  +
When a message is deleted, a trigger checks if there are related entries in this table here and deletes them also.
 
{| class="wikitable" style="font-size: smaller; text-align: center; table-layout: fixed; border-collapse: collapse;" border="1"
 
{| class="wikitable" style="font-size: smaller; text-align: center; table-layout: fixed; border-collapse: collapse;" border="1"
 
|-
 
|-
Line 780: Line 890:
   
 
===madrid_chat===
 
===madrid_chat===
  +
In my case there are three entries in this table.
 
{| class="wikitable" style="font-size: smaller; text-align: center; table-layout: fixed; border-collapse: collapse;" border="1"
 
{| class="wikitable" style="font-size: smaller; text-align: center; table-layout: fixed; border-collapse: collapse;" border="1"
 
|-
 
|-
Line 792: Line 903:
 
|style
 
|style
 
|INTEGER
 
|INTEGER
|style="text-align:left;" | 45
+
|style="text-align:left;" | Only known value is 45
 
|-
 
|-
 
|state
 
|state
 
|INTEGER
 
|INTEGER
|style="text-align:left;" | 3
+
|style="text-align:left;" | Only known value is 3
 
|-
 
|-
 
|account_id
 
|account_id
 
|TEXT
 
|TEXT
|style="text-align:left;" | GUID, always the same for me
+
|style="text-align:left;" | GUID of the iMessage account you used
 
|-
 
|-
 
|properties
 
|properties
 
|BLOB
 
|BLOB
|style="text-align:left;" | NULL or a bplist (?)
+
|style="text-align:left;" | NULL for the second row or a bplist (see below)
 
|-
 
|-
 
|chat_identifier
 
|chat_identifier
 
|TEXT
 
|TEXT
|style="text-align:left;" | like a phone number
+
|style="text-align:left;" | phone number of the other person in international format, no spaces
 
|-
 
|-
 
|service_name
 
|service_name
Line 816: Line 927:
 
|guid
 
|guid
 
|TEXT
 
|TEXT
|style="text-align:left;" | almost same as chat_identifier (leading '-')
+
|style="text-align:left;" | same as chat_identifier, but with a '-' in front of it
 
|-
 
|-
 
|room_name
 
|room_name
Line 824: Line 935:
 
|account_login
 
|account_login
 
|TEXT
 
|TEXT
|style="text-align:left;" | almost same as chat_identifier (leading 'P:')
+
|style="text-align:left;" | 'P:' & own phone number or 'E:' & email registered for iMessage
 
|-
 
|-
 
|participants
 
|participants
 
|BLOB
 
|BLOB
|style="text-align:left;" | bplist (?)
+
|style="text-align:left;" | bplist (see below)
 
|}
 
|}
  +
  +
For the properties bplist this contains the following settings for the first row (dict):
  +
*CKMadridServiceConsecutiveCanceledMessageCount (integer) = 2
  +
*CKMadridServiceLastCanceledMessageTime (real) = date/time value
  +
or this for the third row (dict):
  +
*CKPlaceholderTimeSince1970Property (real) = date/time value
  +
Please note that the date/time values here are the number of seconds since 1&nbsp;Jan&nbsp;1970 and not since 2001 like in the other database fields. There are also fractions of a second (up to seven digits).
  +
  +
For the participants bplist, in all three rows there is the same content (array):
  +
*(string): a phone number, not my own, in international format, no spaces
  +
  +
The interesting thing is that the phone number of the participants list matches the phone number of the first row. The people in the second and third row don't know the person from the participants list. Probably this is garbage in this table or its implementation is not finished yet. I also don't have any related messages.
  +
  +
The field ''chat_identifier'' seems to be unique. This table is probably an index of iMessage contacts.
   
 
===References===
 
===References===
*[http://imfreedom.org/wiki/IMessage Information based on my examination of the Messages Beta application for Mac OS X]
+
*[http://imfreedom.org/wiki/IMessage Information based on examination of the Messages Beta application for Mac OS X]
*[http://www.apple.com/ios/features.html#imessage Apple's description of iMessage]
+
*<del>[http://www.apple.com/ios/features.html#imessage Apple's description of iMessage]</del> (broken) ([http://web.archive.org/web/20120630170444/http://www.apple.com/ios/features.html#imessage Archive.org's archive of page])
  +
*[http://blog.cryptographyengineering.com/2012/08/dear-apple-please-set-imessage-free.html Critics on protocol by Matthew Green]
 
*[http://www.scip.ch/?labs.20111103 SCIP article (german only)]
 
*[http://www.scip.ch/?labs.20111103 SCIP article (german only)]
 
*[http://twitter.com/#!/pytey/status/127644352091664384 pytey explaining "Madrid"]
 
*[http://twitter.com/#!/pytey/status/127644352091664384 pytey explaining "Madrid"]
 
*[http://www.jailbreakqa.com/questions/92827/how-to-backuprestore-sms-without-itunes jbqa: How to backup/restore SMS without iTunes]
 
*[http://www.jailbreakqa.com/questions/92827/how-to-backuprestore-sms-without-itunes jbqa: How to backup/restore SMS without iTunes]
 
*[http://smsmerge.homedns.org/ script to merge two SMS databases]
 
*[http://smsmerge.homedns.org/ script to merge two SMS databases]
  +
  +
[[Category:Application]].

Latest revision as of 09:14, 13 October 2015

Messages in iOS 5

Messages is the built-in system application to send messages. Initially (in earlier iOS versions) this was only for sending and receiving SMS (text) messages. Support for sending and receiving MMS messages was introduced in iOS 3.0, while support for sending and receiving iMessages was introduced in iOS 5. The application itself is located in /Applications/MobileSMS.app/.

iMessage

With iOS 5 Apple introduced the iCloud based instant messaging service, iMessage, which works across different iOS devices and Mac OS X, and is the first native messaging service for iPod touch and iPad. Messages for Mac OS X was introduced with OS X Mountain Lion, and replaced the iChat instant messaging program.

Activation

iMessages on iOS is activated in the same way as FaceTime, with a silent SMS being sent to a carrier defined number and a successful registration SMS being returned. This 'verifies' the phone number for sending and receiving iMessages. Email (AppleID) verification does not use silent SMS, and only sends an email containing a verification link. This is the only method for iMessages on the iPad, iPod touch and Mac OS X.

iMessage is deeply integrated and built around Apple's Push Notification Server over port 5223.

Serialization

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 sms.db-shm and sms.db-wal are probably used for indexing or search and can be recreated if deleted (?). If the database is updated manually, it's important to recreate these files. On a jailbroken device, they could be recreated using the sqlite3 program, for example by issuing the following command: sqlite3 sms.db .dump>/dev/null.
The sms-legacy.db file was not on my iPhone, but another user claimed to have this file (see jbqa reference).
The iTunes Backup filename of sms.db is 3d0d7e5fb2ce288813306e4d4636395e047a3d28.

The Attachments folder contains subfolders and files, which are referenced in the main database file in the table madrid_attachment (see there).

Drafts

In the folder Drafts there is a file PENDING-recipients.plist (if there is a pending draft with already recipients added) and the content is a plist with an array of strings with the recipients (phone numbers). There is also a subfolder PENDING.draft with a file message.plist containing a dict with the following three values:

  • markupString: The text in HTML format (HTML encoded) that has been entered already
  • resources: array of dict for every image:
    • duration (integer): 0 for images, number of seconds (duration) for videos
    • exportedFilename: (filename, without path, ending in .jpg, .png or .3gp)
    • mimeType: image/jpeg or image/png or video/x-internal-iphone
  • textString: The text that has been entered already (white spaces and line breaks retained)

Images are put into the markupString like this:

<img id="n"
     style="display:block;margin-left:-6px;padding-top:5px;padding-bottom:3px"
     width="97px" height="110px"
     src="x-ckmsgpart:4-n-0">

n is the image number, starting with 0. The width varies slightly, depending on the image width/height ratio, but the height is always 110px (90px for videos). The images theirself are stored in the same folder with corresponding names like data-0, data-1, etc.

In case of a video, the data file is not the video itself, but a bplist with this content (dict):

  • CKSMSComposeOptionFilenameKey (string): /var/tmp/capture/capturedvideo.MOV or /var/tmp/capture-T0x11d520.tmp.OEBOK7/capturedvideo.MOV (or similar)
  • CKSMSComposeOptionPreviewImageDataKey (data): big blob (base64 encoded)

For the blob, this was in my test only 5510 byte long, so it's not the movie itself. The only strings in the blob were "JFIF" and "Exif" somewhere near the beginning, so it can be assumed this is metadata or the screenshot shown in the message bubble.

Main database file

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

Indexes

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

Triggers

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.

From the trigger statements it is visible that there must be a function called read(x) which is used on the field message.flags. As SQLite does not have this function, but is open-sourced, this has probably been added into the code to facilitate handling the read-flag of a message. Please note that there is also a column called 'read'.

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 counts the number of outgoing messages (since last counter reset)
counter_out_lifetime 254 counts the number of outgoing messages (since forever, isn't affected by an counter reset)
counter_in_all 468 counts the number of incoming messages (since last counter reset)
counter_in_lifetime 468 counts the number of incoming messages (since forever, isn't affected by an counter reset)
__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) of the other person (sent to or received from)
date INTEGER message date
text TEXT message content
flags INTEGER unknown, possible values: 0, 2, 3, 5, 35, 16387. Probably a bit-set. The value 35 was set in a SMS that couldn't get sent out and is stil marked with a red exclamation mark letting you send it again.
replace INTEGER unknown, possible values: 0, 1, 2
svc_center TEXT NULL
group_id INTEGER 0 or foreign key to msg_group.ROWID
association_id INTEGER often 0, but sometimes a copy of the date field
height INTEGER always 0
UIFlags INTEGER unknown, possible values: 0, 4, 5, 6, 7
version INTEGER always 0
subject TEXT The subject of an iMessage/MMS-message, or NULL if it's an SMS or if subject is not used on a iMessage/MMS-message
country TEXT NULL or an ISO country code (eg: 'ch' for Switzerland)
headers BLOB always NULL
recipients BLOB normally NULL, one entry had an xml value in it
read INTEGER 0 or 1 (Assume 0 = unread and 1 is read though Madrid messages are always 0 so it probably doesn't apply to them)
madrid_attributedBody BLOB blob, content unknown. The only strings in it are "JFIF" and "Exif", so this is probably meta-data.
madrid_handle TEXT NULL if not an iMessage or a phone number of the other person (sender or receiver)
madrid_version INTEGER NULL if pre-iOS 5.0 or 0
madrid_guid TEXT GUID (unique to the message) or NULL if not an iMessage
madrid_type INTEGER 0 or NULL if pre-iOS 5.0
madrid_roomname TEXT NULL
madrid_service TEXT 'Madrid' or NULL if not an iMessage
madrid_account TEXT NULL if not an iMessage or 'p:' & own phone number or 'e:' & email registered for iMessage
madrid_flags INTEGER message type. Known values: NULL (if not an iMessage), 12289 (received), 32773 (send error), 36869 (sent), 45061 (sent), 77825 (received message containing parsed data eg: phone, email, website), 102405 (sent message containing parsed data eg: phone, email, website)
madrid_attachmentInfo BLOB NULL or blob. The blob contains these strings: streamtyped, NSMutableArray, NSArray, NSObject, NSMutableString, NSString, and a GUID. Format unknown.
madrid_url TEXT always an empty string
madrid_error INTEGER empty string if message is pre-iOS 5.0 or 0 if after
is_madrid INTEGER specifies if it's an iMessage or not, value 0 or 1 (0=SMS/MMS, 1=iMessage)
madrid_date_read INTEGER null if message is pre-iOS 5.0, 0 if sms or a sent iMessage, integer value representing the date read
madrid_date_delivered INTEGER null if message is pre-iOS 5.0, 0 if sms or a received iMessage, integer value representing the date sent
madrid_account_guid TEXT GUID of account used (multiple entries may be found representing either the phone or email registered with iMessage) or empty if not an iMessage

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 0 if only one recipient, 1 if multiple recipients
newest_message INTEGER foreign key to message. This value is auto-updated by triggers when inserting / deleting a message, so that this always points to the newest message.
unread_count INTEGER The number of unread messages in this group. This value is auto-incremented/decremented by triggers when inserting or deleting an unread message or when marking a message as read or unread.
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.ROWID
address TEXT name or phone number (with or without spaces)
country TEXT ISO country code eg: 'ch' for Switzerland

msg_pieces

This table contains information about MMS objects. (?)

When a message is deleted, a trigger checks if there are related entries in this table here and deletes them also.

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

In my case there are three entries in this table.

field name type value / description
ROWID INTEGER PRIMARY KEY AUTOINCREMENT primary key
style INTEGER Only known value is 45
state INTEGER Only known value is 3
account_id TEXT GUID of the iMessage account you used
properties BLOB NULL for the second row or a bplist (see below)
chat_identifier TEXT phone number of the other person in international format, no spaces
service_name TEXT 'Madrid'
guid TEXT same as chat_identifier, but with a '-' in front of it
room_name TEXT NULL
account_login TEXT 'P:' & own phone number or 'E:' & email registered for iMessage
participants BLOB bplist (see below)

For the properties bplist this contains the following settings for the first row (dict):

  • CKMadridServiceConsecutiveCanceledMessageCount (integer) = 2
  • CKMadridServiceLastCanceledMessageTime (real) = date/time value

or this for the third row (dict):

  • CKPlaceholderTimeSince1970Property (real) = date/time value

Please note that the date/time values here are the number of seconds since 1 Jan 1970 and not since 2001 like in the other database fields. There are also fractions of a second (up to seven digits).

For the participants bplist, in all three rows there is the same content (array):

  • (string): a phone number, not my own, in international format, no spaces

The interesting thing is that the phone number of the participants list matches the phone number of the first row. The people in the second and third row don't know the person from the participants list. Probably this is garbage in this table or its implementation is not finished yet. I also don't have any related messages.

The field chat_identifier seems to be unique. This table is probably an index of iMessage contacts.

References