Open alexdanilowicz opened 1 year ago
@alexdanilowicz
Love what you're working on (I'm working on something iMessage related too). I solved this issue by using the attributedBody
field
That field is a bunch of gibberish (some kind of objective-c object). Anyway, I cast it to text in sqlite. And then in JS extracted the text like this:
attributedBody?.substring(attributedBody?.search('NSString')+14, attributedBody?.search('NSDictionary')-12)
Hacky as fuck but it works 🤷
hey @ju-li! Thank you so much for sharing. I'll have to add this, or feel free to open up a PR, but this is super super helpful! Really appreciate you commenting.
Would love to check out your product too. :)
@ju-li — following up here, do you happen to have a code snippet? I'd love to finally improve this. Thanks for your original message, Automata looks very cool!
@alexdanilowicz hi sorry I was busy and forgot to respond
So this is my code for generating a CSV export of the message history:
const exportData = async (filePath: string, startdate: string, enddate: string) => {
const createCsvWriter = csv.createObjectCsvWriter
const csvWriter = createCsvWriter({
path: filePath,
header: [
{id: 'guid', title: 'GUID'},
{id: 'date', title: 'Date'},
{id: 'time', title: 'Time'},
{id: 'recipientId', title: 'Recipient'},
{id: 'roomName', title: 'Room'},
{id: 'displayName', title: 'Display Name'},
{id: 'message', title: 'Message'},
{id: 'service', title: 'Service'},
{id: 'direction', title: 'Direction'},
{id: 'status', title: 'Status'},
]
});
const query = `WITH data AS (
SELECT
m.guid,
CASE WHEN m.date > 10000000000 THEN
-- date is the time elapsed in nanoseconds since 1/1/2001.
-- We then convert it to our localtime
-- Not sure why this is 1 hour ahead though, even in UTC time
-- See: https://apple.stackexchange.com/questions/114168/dates-format-in-messages-chat-db
datetime((m.date/1000000000) + strftime('%s','2001-01-01 00:00:00'), 'unixepoch', 'localtime')
ELSE
datetime(m.date + strftime('%s','2001-01-01 00:00:00'), 'unixepoch', 'localtime')
END AS datetime,
c.chat_identifier as recipientId,
c.room_name as roomName,
c.display_name as displayName,
CASE
WHEN maj.attachment_id IS NOT NULL THEN '[Attachment]'
ELSE m."text"
END as message,
m.service,
CASE
WHEN m.is_from_me = 1 THEN 'outbound'
ELSE 'inbound'
END as direction,
CASE
WHEN m.error = 1 THEN 'Error'
WHEN m.is_read = 1 THEN 'Read'
WHEN m.is_delivered = 1 THEN 'Delivered'
ELSE 'Sent'
END as status,
CAST(m.attributedBody as TEXT) as attributedBody2
FROM chat as c
JOIN chat_message_join cmj ON c.ROWID = cmj.chat_id
JOIN message m ON cmj.message_id = m.ROWID
LEFT JOIN message_attachment_join maj ON maj.message_id = m.ROWID )
SELECT guid, date(datetime) as date, time(datetime) as time, recipientId, roomName, displayName, message, service, direction, status, attributedBody2 FROM data
WHERE date(datetime) BETWEEN '${startdate.substring(0,10)}' AND '${enddate.substring(0,10)}'
ORDER BY 2 DESC, 3 DESC
`
const data: Data[] = await getSQLData(query)
const data2 = data.map((d) => {
const fixed = {
...d,
message: d.message ?? d.attributedBody2?.substring(d.attributedBody2?.search('NSString')+14, d.attributedBody2?.search('NSDictionary')-12)
}
delete fixed.attributedBody2
return fixed
})
await csvWriter.writeRecords(data2)
}
If the text
field is missing, I'd do a search for NSString
and NSDictionary
and hack it that way =X
The reason I did that is because the attributedBody field that contains the message data is packed with this thing called NSArchiver, which is specific to Macs: https://stackoverflow.com/questions/75330393/how-can-i-read-the-attributedbody-column-in-macos-imessage-database I didn't find any easy NSArchive reader available on NPM But since it's a JSON-like data type (i.e., not compressed/encrypted and delimited somewhat), this hacky method works perfectly. Haven't had any issues in production lol
A user reported that after upgrading to Ventura, the
text
column on themessage
table wasNULL