OS X iMessage (Messages) database merger

README.md

OS X iMessage (Messages) database merger

Use it at your own risk! You might end up worse than before. Backup everything beforehand. Twice.

If you have the Messages app setup in multiple Macs with the same Apple ID you may end up with iMessages (or SMS) scattered around all of these Macs. This is because after a certain time the new iMessages (or SMS) recevied will cease to push to devices afer a certain time has elapsed. Thus, if a computer has been offline for some period of time it won't get the new iMessages.

Each Messages instance stores the information in a SQLite database, to consolidate all these databases run the script below. This is where the Messages app stores the SQLite database under ~/Library/Messages, the folder contents will look like as follows:

• chat.db
• chat.db-shm
• chat.db-wal
• Attachments (folder)

Merging the databases

NOTE: Make sure that the Messages instance you want to merge are all on the same version of Mac OS X

Follow How to find your iMessage archive for each Messages instance. Copy all the folder contents. Back these up twice in multiple safe locations.

After you have backed these files up:

1. Create a folder called Merge in your Desktop
2. Create a new user account on your Mac
3. Sign in as the user you created in step 2 and then go to ~/Library/Messages and grab all the database files and copy them into a flash drive. These database files will be where the merged information will go - treat them with care.
4. Go back into the user account that has the Merge folder on the Desktop
5. Copy the database files from your flash drive that you retrived from step 6 into the Merge folder.
6. Copy the database files from instance A of Messages into the Merge folder
7. Rename the database files inside the Merge folder from step 6 to db_1.db, db_1.db-shm and db_1.db-wal,
8. Copy the database files from instance B of Messages into the Merge folder
9. Rename the database files inside the Merge folder from step 8 to db_2.db, db_2.db-shm and db_2.db-wal.
10. Copy the merge_iMessages.py script into the Merge folder
11. Run the merge_iMessages.py script from inside the Merge folder
    • cd ~/Desktop/Merge
    • python3 merge_iMessages.py
12. After the script finishes the db_1.db (from Messages instance A) and db_2.db (from Messages instance B) should be merged into the chat.db database.

What if I have more than 2 instances of Messages databases to merge?

• Delete all the files inside the Merge folder except the chat.db and merge_iMessages.py file.
• Rename the chat.db file to db_1.db.
• Copy the database files from another instance into the Merge folder and rename them to db_2.* as done in step 9.
• Perform step 5 and then step 11.
• Repeat until you have one chat.db file.

Merging the attachments

• Copy the Attachments from instance A of Messages into the Merge folder
• Then copy the Attachments from instance B of Messages into the Merge folder
• Merge them as instructed in this article under the heading "Merge two folders with the same name"

Placing the new database and attachments

1. Copy the chat.db file and the Attachments folder from the Merge folder
2. Go to ~/Library/Messages under the user account where you want this new merged Messages to live
3. Delete all the contents inside this folder and paste the file and folder from step 1
4. Reboot and log into the user account from step 2

You have now merged your all the scattered pieces of iMessage into one Messages instance. Rejoice!

To place the merged Messages instance into other user accounts simply follow the steps 1-5 for each respective user account

DISCLAIMER:

No guarantee about the completeness, reliability and accuracy of this information. Any action you take upon the information, is strictly at your own risk. I will not be liable for any losses and/or damages in connection with the use of this information.

For me the result was satisfactory - lost one chat that was trival which had 2 messages. 2 messages lost out of ~22k messages. Tested on Yosemite.

merge_iMessages.py

#!/usr/bin/env python3

import sqlite3
import sys


def add_map_column(cursor, dbname, *tables):
    for table in tables:
        if table == "message":
            cursor.execute("ALTER TABLE {} ADD COLUMN {} TEXT".format(table, 'DBNAME'))
            cursor.execute("UPDATE message SET DBNAME ='{}'".format(dbname))
        else:
            cursor.execute("ALTER TABLE {} ADD COLUMN {} TEXT".format(table, 'NEWID'))

    cursor.execute("ALTER TABLE {} ADD COLUMN {} TEXT".format('chat_handle_join', "NEW_chat_id"))
    cursor.execute("ALTER TABLE {} ADD COLUMN {} TEXT".format('chat_handle_join', "NEW_handle_id"))


def fetch_messages(cursor, table):
    cursor.execute("SELECT * FROM " + table + " ORDER BY date")
    return cursor.fetchall()


def merge_message(cursor_db_1, cursor_db_2):
    db_1_messages = fetch_messages(cursor_db_1, 'message')
    db_2_messages = fetch_messages(cursor_db_2, 'message')
    print("DB_1 Messages: {}, DB_2 Messages: {}".format(len(db_1_messages), len(db_2_messages)))
    merged_messages = db_1_messages + db_2_messages
    print("Merged Messages: {}".format(len(merged_messages)))
    sorted_by_date = sorted(merged_messages, key=lambda tup: tup['date'])
    de_duplicated = dup_clean_messages(sorted_by_date)
    return de_duplicated


def dup_clean_messages(messages):
    dup_cleaned = []
    seen_gui = []
    for x in messages:
        if x['guid'] not in seen_gui:
            dup_cleaned.append(x)
            seen_gui.append(x['guid'])
    print("De-Duplicated Merged Messages: {} \n".format(len(dup_cleaned)))
    return dup_cleaned


def set_db_cursor(db_name, cursor_db_1, cursor_db_2):
    if db_name == 'db1':
        cur_cursor = cursor_db_1
    elif db_name == 'db2':
        cur_cursor = cursor_db_2
    else:
        print('No DB Name for this message')
        sys.exit(1)
    return cur_cursor


def fetch_join_values(cursor, join_table, lookup_id):
    if join_table == 'chat_handle_join':
        cursor.execute("SELECT chat_id FROM chat_handle_join WHERE handle_id=:hid", {"hid": lookup_id})
    elif join_table == 'chat_message_join':
        cursor.execute("SELECT chat_id FROM chat_message_join WHERE message_id=:mid", {"mid": lookup_id})
    elif join_table == 'message_attachment_join':
        cursor.execute("SELECT attachment_id FROM message_attachment_join WHERE message_id=:mid", {"mid": lookup_id})
    return cursor.fetchall()


def fetch_id_obj(cursor, table, row_id):
    cursor.execute("SELECT * FROM {} WHERE ROWID=:id".format(table), {"id": row_id})
    return cursor.fetchone()


def check_new_id_accounting(lid, nid):
    if lid == nid:
        return
    else:
        print("Last Row ID: {} does not equal New Calculated ID: {}".format(lid, nid))
        sys.exit(1)


def check_in_new_db(cursor, table, *search):
    if table == 'chat':
        cursor.execute("SELECT ROWID FROM chat WHERE guid=:id", {"id": search[0]})
    elif table == 'handle':
        cursor.execute("SELECT ROWID FROM handle WHERE id = :id AND service = :service",
                       {"id": search[0], "service": search[1]})
    elif table == 'chat_handle_join':
        cursor.execute("SELECT ROWID FROM chat_handle_join WHERE chat_id = :chat_id AND handle_id = :handle_id",
                       {"chat_id": search[0], "handle_id": search[1]})
    elif table == 'attachment':
        cursor.execute("SELECT ROWID FROM attachment WHERE guid=:id", {"id": search[0]})
    return cursor.fetchone()


def insert_new_value(cursor, table, value, new_id, handle_id=None):
    oid = value["ROWID"]

    list_value = list(value)
    list_value.pop()
    list_value[0] = new_id

    # set handle_id for new message
    if table == 'message':
        list_value[5] = handle_id

    placeholder = ", ".join(map(lambda x: "?", list_value))

    try:
        cursor.execute("INSERT INTO {} VALUES({})".format(table, placeholder), list_value)
        print('Mapped old {}_id: {} -> new {}_id: {}'.format(table, oid, table, new_id))
        lid = cursor.lastrowid
        check_new_id_accounting(lid, new_id)
        return
    except sqlite3.Error as e:
        print(e)
        print("Error {}:".format(e.args[0]))
        print("Error inserting {} into {} ".format(value, table))
        sys.exit(1)


def insert_new_join(cursor, table, value1, value2):
    # insert val1, val2 into join table

    try:
        cursor.execute("INSERT INTO {} VALUES({}, {})".format(table, value1, value2))
        return
    except sqlite3.Error as e:
        print(e)
        print("Error {}:".format(e.args[0]))
        print("Error inserting {} into {} into join table: {}".format(value1, value2, table))
        sys.exit(1)


def merge_contents(messages, cursor_db_1, cursor_db_2, new_cursor):
    new_attachment_id = 0
    new_chat_id = 0
    new_handle_id = 0
    new_message_id = 0

    for x in messages:
        db_name = x['DBNAME']
        old_message_id = x['ROWID']
        has_attach = x['cache_has_attachments']

        cur_cursor = set_db_cursor(db_name, cursor_db_1, cursor_db_2)

        # Get OLD handle and handle_id
        old_handle_id = x['handle_id']
        old_handle = fetch_id_obj(cur_cursor, 'handle', old_handle_id)

        # insert NEW handle with NEW handle_id
        new_handle_id, final_handle_id = insert_new_handle(new_cursor, new_handle_id, old_handle, old_handle_id)

        # Get OLD chat IDs from chat-handle join
        old_chat_ids = fetch_join_values(cur_cursor, 'chat_handle_join', old_handle_id)
        # insert OLD chats into NEW DB gathered from chat-handle join
        final_chat_ids, new_chat_id = insert_new_chats(cur_cursor, new_chat_id, new_cursor, old_chat_ids)

        # insert into chat_handle_join NEW chat_id and NEW handle_id
        for final_chat_id in final_chat_ids:
            if not check_in_new_db(new_cursor, 'chat_handle_join', final_chat_id, final_handle_id):
                insert_new_join(new_cursor, 'chat_handle_join', final_chat_id, final_handle_id)

        # insert message into NEW DB with updated NEW handle_id
        new_message_id += 1
        insert_new_value(new_cursor, 'message', x, new_message_id, final_handle_id)

        # Get OLD chats IDs from old_message
        old_chat_ids = fetch_join_values(cur_cursor, 'chat_message_join', old_message_id)
        # insert OLD chats into NEW DB gathered from chat-message join
        final_chat_ids, new_chat_id = insert_new_chats(cur_cursor, new_chat_id, new_cursor, old_chat_ids)

        # insert into chat_message_join NEW chat_id and NEW message_id
        for final_chat_id in final_chat_ids:
            insert_new_join(new_cursor, 'chat_message_join', final_chat_id, new_message_id)

        # Insert OLD attachment into NEW DB if there is one
        if has_attach:
            new_attachment_id = insert_new_attachments(cur_cursor, new_attachment_id, new_cursor, new_message_id,
                                                       old_message_id)


def insert_new_chats(cur_cursor, new_chat_id, new_cursor, old_chat_ids):
    final_chat_ids = []
    for item in old_chat_ids:
        old_chat_id = item['chat_id']
        old_chat = fetch_id_obj(cur_cursor, 'chat', old_chat_id)

        # insert new chat with NEW chat_id
        cid_in_new_db = check_in_new_db(new_cursor, 'chat', old_chat['guid'])
        if cid_in_new_db:
            existing_chat_id = cid_in_new_db['ROWID']
            print('Existing chat_id: {}'.format(existing_chat_id))
            final_chat_id = existing_chat_id
            final_chat_ids.append(final_chat_id)
        else:
            print('Adding new chat_id')
            new_chat_id += 1
            insert_new_value(new_cursor, 'chat', old_chat, new_chat_id)
            final_chat_id = new_chat_id
            final_chat_ids.append(final_chat_id)

    return final_chat_ids, new_chat_id


def insert_new_handle(new_cursor, new_handle_id, old_handle, old_handle_id):
    # insert new handle with NEW handle_id
    final_handle_id = None
    if old_handle_id != 0:
        hid_in_new_db = check_in_new_db(new_cursor, 'handle', old_handle['id'], old_handle['service'])
        if hid_in_new_db:
            existing_handle_id = hid_in_new_db["ROWID"]
            print('Existing handle_id: {}'.format(existing_handle_id))
            final_handle_id = existing_handle_id
        else:
            new_handle_id += 1
            insert_new_value(new_cursor, 'handle', old_handle, new_handle_id)
            final_handle_id = new_handle_id
    elif old_handle_id == 0:
        final_handle_id = 0

    return new_handle_id, final_handle_id


def insert_new_attachments(cur_cursor, new_attachment_id, new_cursor, new_message_id, old_message_id):
    old_attachment_ids = fetch_join_values(cur_cursor, 'message_attachment_join', old_message_id)
    for item in old_attachment_ids:
        old_attachment_id = item['attachment_id']
        old_attachment = fetch_id_obj(cur_cursor, 'attachment', old_attachment_id)

        # insert attachment
        new_attachment_id += 1
        insert_new_value(new_cursor, 'attachment', old_attachment, new_attachment_id)
        # insert message_attachment NEW attachment_id and new message_id
        insert_new_join(new_cursor, 'message_attachment_join', new_message_id, new_attachment_id)
        return new_attachment_id


with sqlite3.connect("db_1.db") as db_1_db, sqlite3.connect("db_2.db") as db_2_db, sqlite3.connect("chat.db") as new_db:
    db_1_db.row_factory = sqlite3.Row
    db_2_db.row_factory = sqlite3.Row
    new_db.row_factory = sqlite3.Row

    db_1 = db_1_db.cursor()
    db_2 = db_2_db.cursor()
    new = new_db.cursor()

    tables = ['attachment', 'chat', 'handle', 'message']
    join_tables = ['chat_handle_join', 'chat_message_join', 'message_attachment_join']

    add_map_column(db_1, 'db1', *tables)
    add_map_column(db_2, 'db2', *tables)

    merged_message = merge_message(db_1, db_2)

    merge_contents(merged_message, db_1, db_2, new)

    new_db.execute("VACUUM")
    new_db.commit()
    db_1.close()
    db_2.close()

    print("\n\nDone merging db_1.db and db_2.db into chat.db")

Hello. I am trying to create a cron job to automatically delete spam messages through iMessage (Korean telecom companies earn a lot of revenue from spam messages, so I get a lot of stock-related spam messages). I succeeded in fetching data with Python using sqlite3, but I encountered the following error during the deletion process:

OperationalError: no such function: after_delete_message_plugin

Is it possible to solve the above problem by copying and merging the chat.db you posted?