import sms (text messages) from iphone (ios backup)

flx

Hi all, I would like to be able to import my text messages from my old ios device.
Can someone advise me on how ubuntu touch stores them? Any caveats about messing with that storage?

Let me start off with a quick howto covering the export side:

use iTunes software to backup the phone to a computer (other options, such as ifuse for linux, icloud backup with subsequent gdpr request) will not work any more
in the backup folder, there are randomly named text and binary files; grep -l SQL * will get you a list of the sqlite files, batch export them using sqlite3 [filename] .dump > [filename].sql
grep -l INSERT.INTO..message will get you the file with your text messages.

Apple's "message" table has the following structure (IOS 9.3.6). Bear with me, it's an interesting read:
CREATE TABLE message (ROWID INTEGER, guid TEXT, text TEXT, replace INTEGER, service_center TEXT, handle_id INTEGER, subject TEXT, country TEXT, attributedBody BLOB, version INTEGER, type INTEGER, service TEXT, account TEXT, account_guid TEXT, error INTEGER, date INTEGER, date_read INTEGER, date_delivered INTEGER, is_delivered INTEGER, is_finished INTEGER, is_emote INTEGER, is_from_me INTEGER, is_empty INTEGER, is_delayed INTEGER, is_auto_reply INTEGER, is_prepared INTEGER, is_read INTEGER, is_system_message INTEGER, is_sent INTEGER, has_dd_results INTEGER, is_service_message INTEGER, is_forward INTEGER, was_downgraded INTEGER, is_archive INTEGER, cache_has_attachments INTEGER, cache_roomnames TEXT, was_data_detected INTEGER, was_deduplicated INTEGER, is_audio_message INTEGER, is_played INTEGER, date_played INTEGER, item_type INTEGER, other_handle INTEGER, group_title TEXT, group_action_type INTEGER, share_status INTEGER, share_direction INTEGER, is_expirable INTEGER, expire_state INTEGER, message_action_type INTEGER, message_source INTEGER);

For SMS, I assume, just a few of these will have to be dealt with: "text" (the message proper), "account_guid" (a text key that is unique for the sender of the message), "date" (a cocoa core data timestamp). IOS timestamps are measured in seconds since 20010101 00:00, GMT.

Then, there is "handle_id": There is a blog entry somewhere (filename: parsing-iphone-sms-database-ios6.html) that shows how to look up the sender's phone number using "handle_id" as a foreign key into a table "handle". In my backup, "handle" table was stored in the same sqlite db file.

Lastly, there is a table "chat", possibly about message threading.

Can anybody comment on the ubuntu side of things?

kugiigi

@flx I have no input about iOS text messages but I was able to manually migrate mine from blackberry to UT years ago so it's definitely possible I guess you just need to figure out how they are stored on iOS. If I remember correctly UT's structure is not too complicated. And if ever and if you're capable, maybe you can create a simple tool for others to use

Emphrath

@flx i'm pretty sure they're also stored in a sql database although i don't remember where

flx

In the messaging app source code, I see no reference to database storage,
but apart from that I don't really get what they're up to.
https://github.com/ubports/messaging-app

There IS reference to ubports/history-service.

kugiigi

history-service is used for sms and calls data. The sqlite file is located at ~/.local/share/history-service/

lduboeuf

@flx said in import sms (text messages) from iphone (ios backup):

Can anybody comment on the ubuntu side of things?

I think you already get the info. text messages are stored in history.sqlite in text_events table, but got to have some info in the threads and threads-participant tables to get them displayed.

Maybe you should use messaging-app first and see what have been filled in the db.

If your a programmer, that PR might hep you: https://github.com/ubports/history-service/pull/8

flx

@lduboeuf, @kugiigi
Thank you, this should get me going!
flx.