Recently upgrading to iOS6 broke all the queries I used on my database - the sneaky devils look like they've changed the database format.
Nothing stays the same
The most obvious change is that there are no longer non-normalised numbers in the message table; previously you could have '+44 7966 123 456' and '+447966123456' in the address field. This has sensibly been replaced with the id column in the handle table.As you can see below the tables are castly different after upgrading from iOS5.
The 'handle' table
$ sqlite3 -header db/sms.db
sqlite> SELECT * FROM handle WHERE id LIKE '%68' LIMIT 1;
ROWID|id|country|service|uncanonicalized_id
266|+4479xxxxxx68|gb|SMS|
The 'message' table
Most of the columns appear to be self-explanatory, except for the date fields. Some further searching reveals an existing article about the iOS6 sms.db and that the dates are in fact Mac Absolute time.
Extracting useful data
You can explore the tables and learn much more than I'm writing in this post, but you'll probably want a quick way to extract messages and senders.The X most recent messages
sqlite> SELECT h.id, m.text, m.service FROM message m JOIN handle h ON m.handle_id=h.ROWID ORDER BY m.date DESC LIMIT 1;
id = +4479xxxxxx68
text = Testing No Cellular Data
service = SMS
All messages from +4479xxyyzz58
sqlite> SELECT h.id, m.text, m.service FROM message m JOIN handle h ON m.handle_id=h.ROWID WHERE h.id='+4479xxyyzz58' ORDER BY m.date ASC LIMIT 1;
id = +4479xxyyzz58
text = Testing
service = SMS
What services have I received messages with?
sqlite> .mode column
sqlite> SELECT DISTINCT(m.service) FROM message m;
service
----------
SMS
iMessage
What accounts have I received messages for?
sqlite> .width 40
sqlite> SELECT DISTINCT(m.account) FROM message m;
account
----------------------------------------
p:+4479xxyyzz68
e:chisel@xxyyzz.net
p:+4479xxyyzz22
p:+4479xxyyzz58
Once I realised that this database had been examined and written about in far more detail than I currently have time for I decided to stop; if you want to read a far more thorough article describing the database, please check out "Who's Texting? The iOS6 sms.db".
No comments:
Post a Comment