SQLite SQL Query for iPhone Text Message Backup

iphone-text-message-sqlite.sql

-- more info http://aaron-hoffman.blogspot.com/2017/02/iphone-text-message-sqlite-sql-query.html
select
 m.rowid
,coalesce(m.cache_roomnames, h.id) ThreadId
,m.is_from_me IsFromMe
,case when m.is_from_me = 1 then m.account
 else h.id end as FromPhoneNumber
,case when m.is_from_me = 0 then m.account
 else coalesce(h2.id, h.id) end as ToPhoneNumber
,m.service Service

/*,datetime(m.date + 978307200, 'unixepoch', 'localtime') as TextDate -- date stored as ticks since 2001-01-01 */
,datetime((m.date / 1000000000) + 978307200, 'unixepoch', 'localtime') as TextDate /* after iOS11 date needs to be / 1000000000 */

,m.text MessageText

,c.display_name RoomName

from
message as m
left join handle as h on m.handle_id = h.rowid
left join chat as c on m.cache_roomnames = c.room_name /* note: chat.room_name is not unique, this may cause one-to-many join */
left join chat_handle_join as ch on c.rowid = ch.chat_id
left join handle as h2 on ch.handle_id = h2.rowid

where
-- try to eliminate duplicates due to non-unique message.cache_roomnames/chat.room_name
(h2.service is null or m.service = h2.service)

order by
 2 -- ThreadId
,m.date

How would I limit the results to one specific chat?

Find a message from the conversation in the message table, grab the ROWID, for example 1234

You have to tie it in with the chat_message_join table with:

SELECT cmj.chat_id FROM chat_message_join cmj WHERE cmj.message_id=1234;

Now that you have the chat_id:

SELECT
    m.rowid AS MessageRowID,
    CASE
      WHEN m.is_from_me = 1 THEN 'Me'
      ELSE h.id
    END AS FromPhoneNumber,
    m.is_from_me AS IsFromMe,
    m.service AS Service,
    datetime((m.date / 1000000000) + 978307200, 'unixepoch', 'localtime') as MessageDateDate,
    CASE 
      WHEN m.text IS NULL THEN 'file : ' || a.filename
      ELSE m.text 
    END AS MessageText
FROM chat_message_join cmj
    JOIN message m 
        ON cmj.message_id = m.rowid
    LEFT JOIN handle h 
        ON m.handle_id = h.rowid
    LEFT JOIN message_attachment_join maj 
        ON maj.message_id = m.rowid
    LEFT JOIN attachment a 
        ON maj.attachment_id = a.rowid
WHERE cmj.chat_id = 54321  /* Put your chat_id here */
ORDER BY m.date;

Nice and to the point. Wonderfull, thank you!

…

On Thu, Dec 26, 2024, 17:45 Gabe ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ How would I limit the results to one specific chat? Find a message from the conversation in the message table, grab the ROWID, for example 1234 You have to tie it in with the chat_message_join table with: SELECT cmj.chat_id FROM chat_message_join cmj WHERE cmj.message_id=1234; Now that you have the chat_id: SELECT m.rowid AS MessageRowID, CASE WHEN m.is_from_me = 1 THEN 'Me' ELSE h.id END AS FromPhoneNumber, m.is_from_me AS IsFromMe, m.service AS Service, datetime((m.date / 1000000000) + 978307200, 'unixepoch', 'localtime') as MessageDateDate, CASE WHEN m.text IS NULL THEN 'file : ' || a.filename ELSE m.text END AS MessageTextFROM chat_message_join cmj JOIN message m ON cmj.message_id = m.rowid LEFT JOIN handle h ON m.handle_id = h.rowid LEFT JOIN message_attachment_join maj ON maj.message_id = m.rowid LEFT JOIN attachment a ON maj.attachment_id = a.rowidWHERE cmj.chat_id = 54321 /* Put your chat_id here */ORDER BY m.date;``` — Reply to this email directly, view it on GitHub <https://gist.github.com/aaronhoffman/cc7ee127f00b6b5462fa7fc742c23d4f#gistcomment-5363251> or unsubscribe <https://github.com/notifications/unsubscribe-auth/AXY74C5O5ITIFNGERWPVLTT2HSIKTBFKMF2HI4TJMJ2XIZLTSKBKK5TBNR2WLJDUOJ2WLJDOMFWWLO3UNBZGKYLEL5YGC4TUNFRWS4DBNZ2F6YLDORUXM2LUPGBKK5TBNR2WLJDHNFZXJJDOMFWWLK3UNBZGKYLEL52HS4DFVRZXKYTKMVRXIX3UPFYGLK2HNFZXIQ3PNVWWK3TUUZ2G64DJMNZZDAVEOR4XAZNEM5UXG5FFOZQWY5LFVA2DKMBYGY3TAMNHORZGSZ3HMVZKMY3SMVQXIZI> . You are receiving this email because you commented on the thread. Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub> .