Hacker News Reader

Comment by megraf | Hacker News Reader

Funny, I've actually spent a good amount of time tearing apart the iMessage database back in 2016-2017. The queries you end up with are hilarious. Here's one from my website's sample code

```

INNER JOIN chat_message_join ON message.ROWID = chat_message_join.message_id INNER JOIN chat ON chat.ROWID = chat_message_join.chat_id INNER JOIN chat_handle_join ON chat.ROWID = chat_handle_join.chat_id INNER JOIN handle ON handle.ROWID = chat_handle_join.handle_id LEFT JOIN message_attachment_join ON message.ROWID = message_attachment_join.message_id LEFT JOIN attachment ON attachment.ROWID = message_attachment_join.attachment_id ...

```

But it really wasn't that hard. I was able to programmatically send and receive iMessages. The problem (as the author pointed out) was that this changed, and didn't really work for the long. Although, I'm sure it could still be reveng'd – it's a pain in the ass, and (for me) not really worth it... =/

View on HN

I've been working on a new version of [my iMessage bot](https://github.com/inculi/Sue) recently. Previously it was using Applescript handlers on Sierra, now sqlite calls to iMessage's chat.db (I have to give credit to another bot, [Jared](https://github.com/ZekeSnider/Jared) for help in this area).

I get latest messages with:

```

SELECT handle.id, handle.person_centric_id, message.cache_has_attachments, message.text, message.ROWID, message.cache_roomnames, message.is_from_me, message.date/1000000000 + strftime("%s", "2001-01-01") AS utc_date FROM message INNER JOIN handle ON message.handle_id = handle.ROWID WHERE message.ROWID > #{rowid};

```

and their attachments with:

```

SELECT attachment.ROWID AS a_id, message_attachment_join.message_id AS m_id, attachment.filename, attachment.mime_type, attachment.total_bytes FROM attachment INNER JOIN message_attachment_join ON attachment.ROWID == message_attachment_join.attachment_id WHERE message_attachment_join.message_id >= #{rowid};

```

where rowid is the max rowid from the previous search, and attachments with null mime_types get ignored (YouTube previews, etc). Sending through Applescript still.

Nice job. I'll take a look at your project and see if I can contrib

I did something very similar, extracting the iMessage database. It works by you making an iTunes backup of your phone, and then accessing a specific file in the backup. I ended up with a query that looks like this (only tested with iOS 8, as I sadly lost interest afterwards):

        WITH groupchat_membership AS (
            SELECT cache_roomnames,
            group_concat(id, ', ') AS members
            FROM (
                SELECT DISTINCT message.cache_roomnames, handle.id
                FROM message
                JOIN handle ON message.handle_id=handle.ROWID
                WHERE message.cache_roomnames IS NOT NULL AND message.is_from_me=0
            )
            GROUP BY cache_roomnames
        ),
        unsorted_messages AS (
            SELECT message.ROWID AS rowid,
            CASE WHEN message.cache_roomnames IS NOT NULL THEN groupchat_membership.members ELSE handle.id END AS recipient,
            CASE WHEN message.is_from_me THEN 'Me' ELSE handle.id END AS sender,
            message.date,
            strftime('%d/%m/%Y %H:%M:%S', datetime(message.date + 978307200, 'unixepoch', 'localtime')) AS human_date,
            CASE WHEN message.cache_has_attachments THEN '<Attachment>' || message.text ELSE message.text END AS text
            FROM message
            LEFT JOIN handle ON message.handle_id=handle.ROWID
            NATURAL LEFT JOIN groupchat_membership
        )
        SELECT unsorted_messages.recipient, sender, human_date, text
        FROM unsorted_messages
        NATURAL LEFT JOIN (
            SELECT recipient, max(date) AS latest_date FROM unsorted_messages GROUP BY recipient
        )
        ORDER BY latest_date DESC, date ASC;

Reply on news.ycombinator.com