This script merges two coreprotect databases.

mergedb.py

"""
This script merges two coreprotect databases. It expects databases named '1.db'
and '2.db' and outputs 'out.db' (this can be changed on line 26).
It doesn't merge entity kills, and filters out hopper transactions and pistons,
everything else is merged 100%. It is a little rigid and simple as I wrote it
only for personal use and for use one time only. There is plenty of comments 
though, so it should be fairly easy to understand. Feel free to expand on the code
and make it into a real app.
"""
"""
MIT License
Copyright (c) 2022 Jarek (https://github.com/jarekt)
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.
"""
import sqlite3, os
ro_db = lambda a : "file:" + a + "?mode=ro"

old_db = ro_db("1.db")
new_db = ro_db("2.db")
out_db = "out.db"
# filter hoppers and mob drops and end world?

if os.path.isfile(out_db):
    os.remove(out_db)

db = sqlite3.connect("file::memory:", uri=True)
db.set_trace_callback(print)
c = db.cursor()
c.execute("PRAGMA sychronous = OFF") # we can afford potential db corruption caused by a crash as we are only writing to a new database
c.execute("PRAGMA journal_mode = OFF")

c.execute("ATTACH ? AS old", (old_db, ))
c.execute("ATTACH ? AS new", (new_db, ))
c.execute("ATTACH ? AS out", (out_db, ))

#print(c.execute("PRAGMA database_list").fetchall())

# copy general table structure to out_db
for (command, ) in c.execute("SELECT sql FROM new.sqlite_master WHERE type='table' OR type='index'").fetchall():
    c.execute(command.replace("INDEX", "INDEX out.")
                     .replace("TABLE", "TABLE out."))

# get max old time
max_old_time = c.execute("SELECT min(time) AS time FROM new.co_version").fetchall()[0]
offset = lambda a : c.execute("SELECT COUNT(*) AS offset FROM " + a + " WHERE time < ?", max_old_time).fetchall()[0][0]
copy_to_out = lambda a : c.execute("INSERT INTO out." + a + " SELECT * FROM old." + a + " WHERE time < ?", max_old_time)
# timeless versions of ↑
offset_tl = lambda a : c.execute("SELECT COUNT(*) AS offset FROM " + a).fetchall()[0][0]
copy_to_out_tl = lambda a : c.execute("INSERT INTO out." + a + " SELECT * FROM old." + a)
print(max_old_time)

# merge database lock
c.execute("INSERT INTO out.co_database_lock SELECT * FROM new.co_database_lock")

# copy world ids
c.execute("INSERT INTO out.co_world SELECT * FROM new.co_world")
print("Not checking if world ids the same! watch out")

# merge co_version version
c.execute("""
    INSERT INTO out.co_version SELECT * FROM old.co_version WHERE time < ?
                         UNION SELECT * FROM new.co_version
""", max_old_time)

# merge username log
# get username log id offset
ul_offset = offset("old.co_username_log")
copy_to_out("co_username_log")
from_new_ul = c.execute("SELECT * FROM new.co_username_log WHERE user NOT IN (SELECT user FROM out.co_username_log)").fetchall()
for i, (_, time, uuid, user) in enumerate(from_new_ul):
    c.execute("INSERT INTO out.co_username_log VALUES (?, ?, ?, ?)", (i + ul_offset +1, time, uuid, user))


# merge co_user
# copy from old -> merge from new (no duplicates) -> update nickname where duplicate was removed
us_offset = offset("old.co_user")
copy_to_out("co_user")
from_new_us = c.execute("""
    SELECT * FROM new.co_user
        WHERE user NOT IN (SELECT user FROM out.co_user)
        AND (uuid NOT IN (SELECT uuid FROM out.co_user WHERE uuid IS NOT NULL) OR uuid IS NULL)
""").fetchall()
for i, (_, time, user, uuid) in enumerate(from_new_us):
    c.execute("INSERT INTO out.co_user VALUES (?, ?, ? ,?)", (i + us_offset +1, time, user, uuid))
nicks_to_update = c.execute("""
    SELECT user, uuid FROM new.co_user WHERE user NOT IN (SELECT user FROM out.co_user)
                                       AND uuid IN (SELECT uuid FROM out.co_user WHERE uuid IS NOT NULL)
""").fetchall()
print(nicks_to_update)
for nick in nicks_to_update:
    c.execute("UPDATE out.co_user SET user = ? WHERE uuid = ?", nick)

# merge co_skull
skull_offset = offset("old.co_skull")
copy_to_out("co_skull")
from_new_sku = c.execute("SELECT * FROM new.co_skull").fetchall()
for i, (_, time, owner) in enumerate(from_new_sku):
    c.execute("INSERT INTO out.co_skull VALUES (?, ?, ?)", (i + skull_offset +1, time, owner))

# merge co_art_map
am_offset = offset_tl("old.co_art_map")
copy_to_out_tl("co_art_map")
from_new_am = c.execute("SELECT * FROM new.co_art_map WHERE art NOT IN (SELECT art FROM old.co_art_map)").fetchall()
for i, (_, art) in enumerate(from_new_am):
    c.execute("INSERT INTO out.co_art_map VALUES (?, ?)", (i + am_offset +1, art))

# merge co_chat
copy_to_out_tl("co_chat")
c.execute("""
    INSERT INTO out.co_chat SELECT new.co_chat.time, out.co_user.id, wid, x, y, z, message FROM new.co_chat
                                                                        LEFT JOIN new.co_user ON new.co_chat.user = new.co_user.id
                                                                        LEFT JOIN out.co_user ON out.co_user.user = new.co_user.user
""")

# merge co_command
copy_to_out_tl("co_command")
c.execute("""
    INSERT INTO out.co_command SELECT new.co_command.time, out.co_user.id, wid, x, y, z, message FROM new.co_command
                                                                        LEFT JOIN new.co_user ON new.co_command.user = new.co_user.id
                                                                        LEFT JOIN out.co_user ON out.co_user.user = new.co_user.user 
""")

# merge co_session
copy_to_out("co_session")
c.execute("""
    INSERT INTO out.co_session SELECT new.co_session.time, out.co_user.id, wid, x, y, z, action FROM new.co_session
                                                                        LEFT JOIN new.co_user ON new.co_session.user = new.co_user.id
                                                                        LEFT JOIN out.co_user ON out.co_user.user = new.co_user.user
""")

# merge co_sign
copy_to_out("co_sign")
c.execute("""
    INSERT INTO out.co_sign SELECT new.co_sign.time, out.co_user.id, wid, x, y, z, action, color, line_1, line_2, line_3, line_4, data FROM new.co_sign
                                                                        LEFT JOIN new.co_user ON new.co_sign.user = new.co_user.id
                                                                        LEFT JOIN out.co_user ON out.co_user.user = new.co_user.user  
""")

# merge co_material_map
mm_offset = offset_tl("old.co_material_map")
copy_to_out_tl("co_material_map")
from_new_mm = c.execute("SELECT * FROM new.co_material_map WHERE material NOT IN (SELECT material FROM old.co_material_map)").fetchall()
for i, (_, material) in enumerate(from_new_mm):
    c.execute("INSERT INTO out.co_material_map VALUES (?, ?)", (i + mm_offset +1, material))

# merge co_blockdata_map
bdm_offset = offset_tl("old.co_blockdata_map")
copy_to_out_tl("co_blockdata_map")
from_new_bdm = c.execute("SELECT * FROM new.co_blockdata_map WHERE data NOT IN (SELECT data FROM old.co_blockdata_map)").fetchall()
for i, (_, data) in enumerate(from_new_bdm):
    c.execute("INSERT INTO out.co_blockdata_map VALUES (?, ?)", (i + bdm_offset +1, data))

# merge co_entity_map
em_offset = offset_tl("old.co_entity_map")
copy_to_out_tl("co_entity_map")
from_new_em = c.execute("SELECT * FROM new.co_entity_map WHERE entity NOT IN (SELECT entity FROM old.co_entity_map)").fetchall()
for i, (_, entity) in enumerate(from_new_em):
    c.execute("INSERT INTO out.co_entity_map VALUES (?, ?)", (i + em_offset +1, entity))

# merge co_entity (not implemented as co_entity logs all sorts of redundant stuff and I don't have time to check if the entity was a named mob or a random enderman from a farm)

# merge co_item (uncomment ↓ for filtering)
co_item_filter = "1" #r" material LIKE '%netherite%' OR material LIKE '%diamond%' OR material LIKE '%iron%' OR material LIKE '%book%' OR material LIKE '%disk%' OR material LIKE '%shulker%' OR material LIKE '%lapis%' OR action = 4 OR action = 5"
c.execute("""
    INSERT INTO out.co_item SELECT time, user, wid, x, y, z, type, data, amount, action FROM old.co_item
                            LEFT JOIN old.co_material_map ON type = old.co_material_map.id
                            WHERE (""" + co_item_filter + ") AND time < ?", max_old_time)
co_item_filter = co_item_filter.replace(r"material", "new.co_material_map.material")
c.execute("""
    INSERT INTO out.co_item SELECT new.co_item.time, IFNULL(out.co_user.id, fallback.id), wid, x, y, z, out.co_material_map.id, data, amount, action FROM new.co_item
                            LEFT JOIN new.co_material_map ON new.co_item.type = new.co_material_map.id
                            LEFT JOIN out.co_material_map ON new.co_material_map.material = out.co_material_map.material
                            LEFT JOIN new.co_user ON new.co_item.user = new.co_user.id
                            LEFT JOIN out.co_user ON new.co_user.uuid = out.co_user.uuid
                            LEFT JOIN out.co_user fallback ON new.co_user.user = fallback.user
                            WHERE """ + co_item_filter)

# merge co_container
co_container_filter = r"user != '#hopper'"
c.execute("""
    INSERT INTO out.co_container SELECT old.co_container.time, old.co_container.user, wid, x, y, z, type, data, amount, metadata, action, rolled_back FROM old.co_container
                                 LEFT JOIN old.co_user ON old.co_container.user = old.co_user.id
                                 WHERE (""" + co_container_filter.replace("user", "old.co_user.user") + ") AND old.co_container.time < ?", max_old_time)
c.execute("""
    INSERT INTO out.co_container SELECT new.co_container.time, IFNULL(out.co_user.id, fallback.id), wid, x, y, z, out.co_material_map.id, data, amount, metadata, action, rolled_back FROM new.co_container
                                 LEFT JOIN new.co_material_map ON new.co_container.type = new.co_material_map.id
                                 LEFT JOIN out.co_material_map ON new.co_material_map.material = out.co_material_map.material
                                 LEFT JOIN new.co_user ON new.co_container.user = new.co_user.id
                                 LEFT JOIN out.co_user ON new.co_user.uuid = out.co_user.uuid
                                 LEFT JOIN out.co_user fallback ON new.co_user.user = fallback.user
                                 WHERE """ + co_container_filter.replace("user", "new.co_user.user"))

#merge co_block
co_block_filter = r"action != 2 AND (action != 3 OR (action = 3 AND type = 0))  AND user!= '#piston'"
c.execute("""
    INSERT INTO out.co_block SELECT old.co_block.time, old.co_block.user, wid, x, y , z, type, data, meta, blockdata, action, rolled_back FROM old.co_block
                             LEFT JOIN old.co_user ON old.co_block.user = old.co_user.id
                             WHERE old.co_block.time IS NOT NULL AND (""" + co_block_filter.replace("user", "old.co_user.user") + ") AND old.co_block.time < ?", max_old_time)
c.execute("""--                                                                                                    ↓not enough if you need to merge entities
    INSERT INTO out.co_block SELECT new.co_block.time, IFNULL(out.co_user.id, fallback.id), wid, x, y, z, IFNULL(out.co_material_map.id, 0), 
                                    COALESCE(out.co_art_map.id, out.co_skull.id, killed_player.id, item_frame.id, new.co_block.data), meta,
                                    new.co_block.blockdata, action, rolled_back
                                    FROM new.co_block
                             -- user
                             LEFT JOIN new.co_user ON new.co_block.user = new.co_user.id
                             LEFT JOIN out.co_user ON new.co_user.uuid = out.co_user.uuid
                             LEFT JOIN out.co_user fallback ON new.co_user.user = fallback.user
                             -- material
                             LEFT JOIN new.co_material_map ON new.co_block.type = new.co_material_map.id
                             LEFT JOIN out.co_material_map ON new.co_material_map.material = out.co_material_map.material
                             -- → painting
                             LEFT JOIN new.co_art_map ON new.co_block.data = new.co_art_map.id
                             LEFT JOIN out.co_art_map ON new.co_art_map.art = out.co_art_map.art AND out.co_material_map.id = (SELECT id FROM out.co_material_map WHERE material = 'minecraft:painting')
                             -- → skull
                             LEFT JOIN new.co_skull ON new.co_block.data = new.co_skull.id
                             LEFT JOIN out.co_skull ON new.co_skull.time = out.co_skull.time AND out.co_material_map.id = (SELECT id FROM out.co_material_map WHERE material = 'minecraft:player_head' OR material = 'minecraft:player_wall_head')
                             -- → player kill
                             LEFT JOIN new.co_user new_kill ON new.co_block.data = new_kill.id
                             LEFT JOIN out.co_user killed_player ON new_kill.uuid = killed_player.uuid
                             -- → item frame
                             LEFT JOIN new.co_material_map new_item_frame ON new.co_block.data = new_item_frame.id
                             LEFT JOIN out.co_material_map item_frame ON new_item_frame.material = item_frame.material
                             --
                             WHERE new.co_block.time IS NOT NULL AND (""" + co_block_filter.replace("user", "new.co_user.user") + ")")

# merge blockdata (more complex cus of the blob type)
db.set_trace_callback(None)
blockdata_map_tup = c.execute("""
    SELECT new.co_blockdata_map.id, out.co_blockdata_map.id FROM new.co_blockdata_map
    LEFT JOIN out.co_blockdata_map ON new.co_blockdata_map.data = out.co_blockdata_map.data
""").fetchall()
blockdata_map = {}
for keyval in blockdata_map_tup:
    blockdata_map[keyval[0]] = keyval[1]

bl_data = c.execute("""
    SELECT DISTINCT blockdata FROM new.co_block WHERE blockdata IS NOT NULL
""").fetchall()
update_amount = len(bl_data)
for i, (new_blockdata_bytes, ) in enumerate(bl_data):
    new_bl_id = list(map(lambda x : int(x), new_blockdata_bytes.decode().split(",")))
    out_bl_id = []
    for id in new_bl_id:
        out_bl_id.append(blockdata_map[id])
    c.execute("""
        UPDATE out.co_block SET blockdata = ? WHERE blockdata = ?
    """, (','.join(list(map(lambda x: str(x), out_bl_id))).encode(), new_blockdata_bytes))
    print("\rBlockdata update progress: %d%%" % (i/update_amount * 100), end='')

db.commit()
db.close()

*** in database main ***
On tree page 338763 cell 143: 2nd reference to page 338731
row 10508350 missing from index block_index
row 10508351 missing from index block_index
row 10508352 missing from index block_type_index
row 10508352 missing from index block_index
row 10508353 missing from index block_type_index
row 10508353 missing from index block_user_index
row 10508353 missing from index block_index
row 10508413 missing from index block_type_index
row 10508413 missing from index block_user_index
row 10508413 missing from index block_index
row 10508430 missing from index block_type_index
row 10508430 missing from index block_user_index
row 10508430 missing from index block_index
wrong # of entries in index block_type_index
wrong # of entries in index block_user_index
wrong # of entries in index block_index```