Last active
April 1, 2022 18:38
-
-
Save jarekt/114f00f196d116caad09f2d12b298bf7 to your computer and use it in GitHub Desktop.
This script merges two coreprotect databases.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
""" | |
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() |
Author
jarekt
commented
Dec 19, 2021
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment