Export attachments from an iPhone backup

exportAttachments

#!/bin/bash
# Construct a tree of folders to match the structure 
# (below the root) given for iPhone text message 
# attachments, using symbolic links to the actual files
# in the backup.
#
# The text message file is 3d0d7e5fb2ce288813306e4d4636395e047a3d28
# See also https://osxdaily.com/2010/07/08/read-iphone-sms-backup/
# https://commons.erau.edu/cgi/viewcontent.cgi?article=1099&context=jdfsl


# USAGE
#  exportAttachments [backupPath] <listOfAttachments

# AUTHOR
# James E. Scarborough ke4roh@gmail.com
#
# LICENSE
# CC-BY-NC

set -e

backup_path=$1

[ -d $backup_path ] || (echo "Pass the backup path as the first parameter." && exit 1)

read p # skip headers

function get_backup_filename {
    # implementing https://apple.stackexchange.com/questions/77432/location-of-message-attachments-in-ios-6-backup/117230
    echo -n "$1" | sed 's#~/#MediaDomain-#' | sha1sum | cut -f1 -d\ 
}

while read ln; do
   afile="$(echo $ln | cut -d, -f1)"
   backup_file=$(get_backup_filename "$afile")
   backup_dir=$backup_path/$(echo $backup_file | cut -c1-2)
   target_file=$(echo $afile | sed 's#^~#.#')
   target_path=$(dirname "$target_file")
   [ -f $backup_dir/$backup_file ] || echo "Backup file $backup_file is missing"
   [ -d "$target_path" ] || mkdir -p "$target_path"
   [ -h "$target_file" ] || ln -s $backup_dir/$backup_file "$target_file"
done

worksheet.sql

-- Querying from iphone backup file 3d0d7e5fb2ce288813306e4d4636395e047a3d28.mddata
-- Run with dbeaver or your favorite database access tool
-- Author: James E. Scarborough ke4roh@gmail.com
-- License: CC-BY-NC

select distinct  c.chat_identifier, datetime(m.date/1000000000 + strftime("%s", "2001-01-01"),"unixepoch","localtime") tm, m."text" 
from message m 
join chat_message_join cmj on m.ROWID = cmj.message_id 
join chat_handle_join chj on cmj.chat_id = chj.chat_id 
join chat c on cmj.chat_id = c.ROWID 
join handle h on chj.handle_id = h.ROWID 
where lower(m.text) like '%tara%' 
group by chat_identifier, tm

-- Messages mentioning foo
select 
   cmj.chat_id, datetime(m.date/1000000000 + strftime("%s", "2001-01-01"),"unixepoch","localtime") tm, m.date, 
   lag(m.date,1) OVER (partition by chat_id order by m.date) ldate, m."text" 
from message m 
join chat_message_join cmj on m.ROWID = cmj.message_id 
where lower(m.text) like '%foo%'

-- find the first message
select min(datetime(m.date/1000000000 + strftime("%s", "2001-01-01"),"unixepoch","localtime")) firstMsg, 
       max(datetime(m.date/1000000000 + strftime("%s", "2001-01-01"),"unixepoch","localtime")) lastMsg 
       from message m

-- Identify all chat sessions           
select c.chat_identifier, s.chat_id, tm, is_from_me, "text", payload_data, 
 SUM(is_new_session) OVER (ORDER BY s.chat_id, date) global_session_id
 from (
 SELECT *,
      CASE WHEN ldate is null 
        or date - ldate >= 60 * 60 * 1000000000 THEN 1 ELSE 0 END is_new_session
      FROM (
          select 
   			chat_id, datetime(m.date/1000000000 + strftime("%s", "2001-01-01"),"unixepoch","localtime") tm, m.date, 
   				lag(m.date,1) OVER (partition by chat_id order by m.date) ldate, m.is_from_me, m."text", m.payload_data 
                from message m 
                join chat_message_join cmj on m.ROWID = cmj.message_id 
       )
       order by chat_id, date
   ) s
join chat_handle_join chj on s.chat_id = chj.chat_id 
join chat c on s.chat_id = c.ROWID 
join handle h on chj.handle_id = h.ROWID 

-- select all messages and attachments
select 
   			chat_identifier, datetime(m.date/1000000000 + strftime("%s", "2001-01-01"),"unixepoch","localtime") tm, m.date, 
   				m.is_from_me, m."text", m.payload_data, att.filename, att.mime_type
                from message m 
                join chat_message_join cmj on m.ROWID = cmj.message_id
                join chat on cmj.chat_id = chat.ROWID 
                left outer join message_attachment_join maj on m.ROWID = maj.message_id 
                left outer join attachment att on maj.attachment_id = att.ROWID 
                order by chat_identifier, date

-- Subselecting by the global_session_id doesn't work.
select chat_id, tm, "text", 
 SUM(is_new_session) OVER (ORDER BY chat_id, date) global_session_id
 from (
 SELECT *,
      CASE WHEN ldate is null 
        or date - ldate >= 60 * 60 * 1000000000 THEN 1 ELSE 0 END is_new_session
      FROM (
          select 
   			cmj.chat_id, datetime(m.date/1000000000 + strftime("%s", "2001-01-01"),"unixepoch","localtime") tm, m.date, 
   				lag(m.date,1) OVER (partition by chat_id order by m.date) ldate, m."text" 
                from message m 
                join chat_message_join cmj on m.ROWID = cmj.message_id 
       )
       order by chat_id, date
   )
join (
select  
 SUM(is_new_session) OVER (ORDER BY chat_id, date) gsid
 from (
 SELECT *,
      CASE WHEN ldate is null 
        or date - ldate >= 60 * 60 * 1000000000 THEN 1 ELSE 0 END is_new_session
      FROM (
          select 
   			cmj.chat_id, datetime(m.date/1000000000 + strftime("%s", "2001-01-01"),"unixepoch","localtime") tm, m.date, 
   				lag(m.date,1) OVER (partition by chat_id order by m.date) ldate, m."text" 
                from message m 
                join chat_message_join cmj on m.ROWID = cmj.message_id 
       )
       order by chat_id, date
   )
where lower(text) like '%foo%'
)
on gsid=global_session_id