List all #iMessage attachments associated with a given buddy

attachments-for-buddy.sql

-- sqlite3 ~/Library/Messages/chat.db < attachments-for-buddy.sql
-- dates in the DB are NSDate. NSDate.h says: NSTimeIntervalSince1970 978328800

-- To list attachments for a specific buddy, put their handle in the 'where' clause at the end.

SELECT Datetime(created_date + 978328800, 'unixepoch', 'localtime'),
       mime_type,
       total_bytes,
       filename
FROM   attachment
       JOIN message_attachment_join
         ON message_attachment_join.attachment_id = attachment.rowid
       JOIN chat_message_join
         ON chat_message_join.message_id = message_attachment_join.message_id
       JOIN chat_handle_join
         ON chat_handle_join.chat_id = chat_message_join.chat_id
       JOIN handle
         ON handle.rowid = chat_handle_join.handle_id
WHERE  handle.id = 'BUDDY ID';

-- or just list them all:

SELECT Datetime(created_date + 978328800, 'unixepoch', 'localtime') as date,
       mime_type,
       total_bytes as bytes,
       handle.id as buddy,
       filename,
       is_outgoing
FROM   attachment
       JOIN message_attachment_join
         ON message_attachment_join.attachment_id = attachment.rowid
       JOIN chat_message_join
         ON chat_message_join.message_id = message_attachment_join.message_id
       JOIN chat_handle_join
         ON chat_handle_join.chat_id = chat_message_join.chat_id
       JOIN handle
         ON handle.rowid = chat_handle_join.handle_id;