|
#!/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") |
After doing some digging in trying to understand the chat.db format, @kopurando is right that handle_id = 0 means group chats, but from what I can see on El Capitan, it more specifically means you within the group chat. I didn't particularly like the solution before of just assigning that handle_id to something else and not maintaining group history, so I'm trying to continue trying to understand all this until I can modify the script to handle those cases properly.
So far the following query has made things pretty readable within the chat.db, so hopefully it helps some future traveler to this thread.
Now to break apart what's causing this script to fail, and then to fix it.