|
#!/bin/env python |
|
|
|
import sqlite3, os, sys |
|
|
|
if os.popen("uname -sr").read() != "Darwin 14.0.0\n": |
|
print("This is not for your OS X version") |
|
sys.exit(1) |
|
|
|
def verify_format(cursor, name): |
|
tables = old.execute("SELECT name FROM sqlite_master WHERE type='table'").fetchall() |
|
if tables != [(u'_SqliteDatabaseProperties',), (u'chat',), (u'sqlite_sequence',), (u'attachment',), (u'handle',), (u'chat_handle_join',), (u'message',), (u'chat_message_join',), (u'message_attachment_join',)]: |
|
print("Unexpected database format (%s). Didn't iMessage convert it?" % name) |
|
sys.exit(1) |
|
|
|
def fetch(cursor, table): |
|
cursor.execute("SELECT * FROM " + table + " ORDER BY ROWID") |
|
return cursor.fetchall() |
|
|
|
def max_row_id(cursor, table): |
|
cursor.execute("SELECT Max(ROWID) FROM " + table) |
|
return cursor.fetchone()[0] |
|
|
|
def set_tuple_index(tup, index, value): |
|
return tuple(tup[0:index]) + (value,) + tuple(tup[index+1:]) |
|
|
|
def tuple_where(tuples, id_func, o): |
|
matches = filter(lambda x: id_func(x) == id_func(o), tuples) |
|
return matches[0][0] if len(matches) > 0 else None |
|
|
|
def merge(old_cursor, new_cursor, table, id_func): |
|
old = fetch(old_cursor, table) |
|
new = fetch(new_cursor, table) |
|
additional = [] |
|
row_map = {} |
|
next_row = max_row_id(old_cursor, table) + 1 |
|
for row in new: |
|
rowid = row[0] |
|
mapped_row = tuple_where(old, id_func, row) |
|
if not mapped_row: |
|
mapped_row = next_row |
|
next_row += 1 |
|
additional = additional + [set_tuple_index(row, 0, mapped_row)] |
|
print("Adding %s row %d" % (table, mapped_row)) |
|
else: |
|
print("Mapping %s row %d->%d" % (table, rowid, mapped_row)) |
|
row_map[rowid] = mapped_row |
|
return (row_map, additional) |
|
|
|
def apply_map_to_tuple_index(tuples, index, m): |
|
return map(lambda t: set_tuple_index(t, index, m[t[index]]), tuples) |
|
|
|
def merge_joins(old, new, table, map1, map2): |
|
print(table + ":") |
|
new_rows = fetch(new, table) |
|
old_rows = fetch(old, table) |
|
mapped = apply_map_to_join(new_rows, map1, map2) |
|
return filter(lambda x: x not in old_rows, mapped) |
|
|
|
def apply_map_to_join(tuples, map1, map2): |
|
for t in tuples: |
|
print("Mapping %d/%d->%d/%d" % (t[0], t[1], map1[t[0]], map2[t[1]])) |
|
return map(lambda t: (map1[t[0]], map2[t[1]]), tuples) |
|
|
|
def insert_values(cursor, values, db): |
|
for row in values: |
|
placeholder = ", ".join(map(lambda x: "?", list(row))) |
|
statement = "INSERT INTO %s VALUES (%s)" % (db, placeholder) |
|
cursor.execute(statement, row) |
|
|
|
with sqlite3.connect("chat.db") as old_db, sqlite3.connect("new.db") as new_db: |
|
old = old_db.cursor() |
|
new = new_db.cursor() |
|
|
|
verify_format(old, "chat.db") |
|
verify_format(new, "new.db") |
|
|
|
(attachment_map, attachments) = merge(old, new, "attachment", lambda x: x[1]) |
|
(chat_map, chats) = merge(old, new, "chat", lambda x: x[1]) |
|
(handle_map, handles) = merge(old, new, "handle", lambda x: (x[1], x[3])) |
|
(message_map, messages) = merge(old, new, "message", lambda x: x[1]) |
|
messages = apply_map_to_tuple_index(messages, 5, handle_map) |
|
|
|
chat_handle_joins = merge_joins(old, new, "chat_handle_join", chat_map, handle_map) |
|
chat_message_joins = merge_joins(old, new, "chat_message_join", chat_map, message_map) |
|
message_attachment_joins = merge_joins(old, new, "message_attachment_join", message_map, attachment_map) |
|
|
|
insert_values(old, attachments, "attachment") |
|
insert_values(old, chats, "chat") |
|
insert_values(old, handles, "handle") |
|
insert_values(old, messages, "message") |
|
|
|
insert_values(old, chat_handle_joins, "chat_handle_join") |
|
insert_values(old, chat_message_joins, "chat_message_join") |
|
insert_values(old, message_attachment_joins, "message_attachment_join") |
|
|
|
old_db.commit() |
|
print("OK") |
Hello everyone...I know this is an oldish thread, but I am brand new to this forum and am having this exact problem - after a recent clean install of OS X El Capitan and a total Time Machine fail, I realized the only way to get my Messages database history was to extract the sms.db from an iPhone backup. My Messages threads are super important to me and I never delete them - mine actually go back to my first iPhone in 2008! So, I figured this could at least be a good opportunity to get my Mac's Messages database to match up with my iPhone's, since it only had histories going back to the introduction of the Mac Messages app (whenever that was) and was missing all SMS conversations until last summer, when it SMS was introduced there.
Anyway, I changed the iPhone's sms.db file to chat.db and added that, along with the Attachments folder, to ~/Library/Messages, and deleted the com.Apple.iChat folder from ~/Library/Containers, and restarted. My Messages app remained empty, and now I have files in ~/Library/Messages named chat.db, chat.db-wal and chat.db-shm, along with chat.db.incompatible.v9006.sqlitedb, chat.db.incompatible.v9006.sqlitedb-shm, and chat.db.incompatible.v9006.sqlitedb-wal. The "incompatible" files are the larger ones of the bunch, so I think that's where my actual old Messages archive is located. After doing a lot of research over the course of a few days it appears as though what you guys have described above is exactly what could fix the issue - merging the "incompatible" files into the other ones to get Messages to read them.
However, please forgive me for this...the steps you've described above is a bit over my head! I'm generally pretty tech savvy and am sure I could figure it out if someone could be so kind as to spell it out for me a bit more. I would be very, very grateful if one of you guys could! I got a bit lost at the "running the script out of the folder" stuff, and would be so grateful for a little extra help, as I really do want my messages back!
Thanks everyone. :)