natario1 / whatsapp-database-merger Goto Github PK

Small command-line utility to safely merge multiple WhatsApp backups (msgstore.db) into one.

Kotlin 100.00%

whatsapp kotlin database whatsapp-backup backup

whatsapp-database-merger's Introduction

whatsapp-database-merger

A small command-line utility that can be used to merge multiple WhatsApp databases (msgstore.db) into one. A few notes:

input databases must be already decrypted
output database will also be in decrypted form. To use it, it must either be moved to /data/data/com.whatsapp/databases on a rooted phone, or otherwise encrypted again and restored as usual.
Java 11 should be installed to build and run the program.

Usage

You can either build the tool yourself or download the binary from the releases page. The tool requires a single argument - a path to a directory on your local machine. For example:

./whatsapp-database-merger /Users/Me/Desktop/WhatsApp

In this example, the program will look for input databases in the /Users/Me/Desktop/WhatsApp/input folder, and will create the merged database in /Users/Me/Desktop/WhatsApp/output.

How it works

The program is inspired by whapa. WhatsApp msgstore.db is just a SQLite database and as such it can be read and written to with common tools.

The program will merge all relevant tables, not just messages and chats. See src/main/kotlin/dev/natario/Table for a list.

When merging them, extra care is taken:

primary keys, when possible, are given the appropriate offset in order to not collide with existing entries in the other database(s).
When this happens, we also apply the same offset in every other column of the database that were referencing them, ensuring consistency of data.
Special logic addresses the case where entries could not be inserted due to unique constraint defined in the database, in which case all references to the skipped entry will be updated to match the existing entry which prevailed.

For example, messages._id will receive an offset to avoid collision, then the program will also modify all other columns pointing to messages, like chat.last_read_message_row_id.

Known issues

The tool can have troubles with binary data, especially in the message_thumbnails table. If the data contains many null bytes (\u0000), the SQL string will include many concatenations (like 'foo'||char(0)||'bar') and there is a hard limit in the driver about how many concatenation operations it can handle for a single statement.

By default, the merger will simply drop thumbnails that it was not able to copy.

It doesn't work for me!

I'm sorry. This happened to work for me in March 2022, but I am unlikely to spend too much time on this utility in the future, especially since the database schema can change very quickly. However, I am happy to review and accept pull requests. The source code should be very easy to understand.

whatsapp-database-merger's People

Contributors

Stargazers

Watchers

Forkers

mrbarbie dhrleandro

whatsapp-database-merger's Issues

Consistency Error

Hello sir,

I wanted to utilise this tool for merging whatsapp databases but I'm getting consistency error. What should I do?

How to use?

Can you tell me a detail instruction on how to use the tool? Especially for windows user with little knowledge.

For example, how to arrange the msgstore.db;
eg: I want to combine mgstore (6March) and mgstore (7March), how to do it? How about the naming scheme?
naming it mgstore1.db for 7 March, and mgstore2.db for 6 March?

What command to make in cmd (windows) to combine it?
How to define output file?

Hope you can answer this ASAP as I really on my end needs here :D
Sorry for any bad English used.

Tool not working since mid july due to changes in database schema

Seems like in version 2.22.7.74 the schema changed a lot, most likely to this feature of being able to switch between iOS and Android more seamlessly.

This cool author blogpost has some info to get started with the changes, the TLDR is that the messages (plural) tables have been dropped and now they are called message and message_* and there are a few more.

https://thebinaryhick.blog/2022/06/09/new-msgstore-who-dis-a-look-at-an-updated-whatsapp-on-android/

Do you think you could find some time to update the tool? Ideally it would detect the version and would be able to merge both and migrate to the new schema for the output database.

I'm very interested in this so if you say that you won't be able to look at it anytime soon I might try to give it a chance but I'm new to Kotlin, only being able to run the project in IntelliJ took me an hour LOL.

Thanks in any case for this contribution!

i keep getting consistency error

Hello, i've 4 databases that i was able to convert their schemas to the same schema of WhatsApp version (with very minor difference in tables & rows)

but when i try to run this program in binary, it keeps failing.

May you please provide a way or method for us to update the binary file to keep it updated to the latest schema provided in the app?
i've very basic knowledge of SQLite databases but i've managed to export database schema and notice difference in schema and different table
it will be of great help as the script and binary file needs to be updates and bring more issues every time WhatsApp updates. so it will be good for us to update it our needs. thank you a lot @natario1 @MrBarbie please help if you could.

this is the error i get when i try to merge all of the 4 databases at once. (i've filtered out lines that has no consistency error):

Details

[] Input databases:
- input/msgstore1.db
- input/msgstore2.db
- input/msgstore3.db
- input/msgstore4.db
[] Checking consistency of input/msgstore1.db

Warning: Database input/msgstore1.db is not consistent. Column chat.last_important_message_row_id references table message, where we expected to find a list of 1 entries, but only 0 were found.

Total count (source): 221617, total count (target): 675
Missing=[1]

Warning: Database input/msgstore1.db is not consistent. Column chat.change_number_notified_message_row_id references table message, where we expected to find a list of 4 entries, but only 3 were found.

Total count (source): 221617, total count (target): 675
Missing=[1]

Warning: Database input/msgstore1.db is not consistent. Column message.chat_row_id references table chat, where we expected to find a list of 594 entries, but only 593 were found.

Total count (source): 675, total count (target): 221617
Missing=[435]

Warning: Database input/msgstore1.db is not consistent. Column message_media.message_row_id references table message, where we expected to find a list of 13092 entries, but only 13043 were found.

Total count (source): 221617, total count (target): 13092
Missing=[158422, 162576, 163874, 165349, 165351, 167591, 168793, 169064, 171436, 172427, 173128, 173432, 173435, 173775, 175538, 178296, 178304, 178528, 187036, 189588, 189854, 190203, 190350, 191425, 191839, 191840, 191844, 192005, 192007, 195417, 195418, 198920, 198922, 198923, 200273, 200274, 200338, 200339, 203245, 204262, 211003, 212967, 213947, 215571, 223007, 223310, 233684, 257996, 259504]

[*] Checking consistency of input/msgstore2.db

Warning: Database input/msgstore2.db is not consistent. Column message.chat_row_id references table chat, where we expected to find a list of 51 entries, but only 50 were found.

Total count (source): 53, total count (target): 10317
Missing=[-1]

Warning: Database input/msgstore2.db is not consistent. Column message_media.message_row_id references table message, where we expected to find a list of 472 entries, but only 465 were found.

Total count (source): 10317, total count (target): 472
Missing=[402, 1097, 2187, 8031, 8032, 8033, 10653]

[*] Checking consistency of input/msgstore3.db

Warning: Database input/msgstore3.db is not consistent. Column chat.last_read_message_row_id references table message, where we expected to find a list of 70 entries, but only 69 were found.

Total count (source): 57612, total count (target): 235
Missing=[50141]

Warning: Database input/msgstore3.db is not consistent. Column chat.last_read_receipt_sent_message_row_id references table message, where we expected to find a list of 70 entries, but only 69 were found.

Total count (source): 57612, total count (target): 235
Missing=[14]

Warning: Database input/msgstore3.db is not consistent. Column chat.last_read_message_sort_id references table message, where we expected to find a list of 53 entries, but only 52 were found.

Total count (source): 57612, total count (target): 235
Missing=[50141]

Warning: Database input/msgstore3.db is not consistent. Column message.chat_row_id references table chat, where we expected to find a list of 123 entries, but only 122 were found.

Total count (source): 235, total count (target): 57612
Missing=[-1]

Warning: Database input/msgstore3.db is not consistent. Column message_vcard_jid.vcard_row_id references table message_vcard, where we expected to find a list of 3 entries, but only 2 were found.

Total count (source): 2, total count (target): 4
Missing=[-1]

Warning: Database input/msgstore3.db is not consistent. Column message_add_on.sender_jid_row_id references table jid, where we expected to find a list of 11 entries, but only 10 were found.

Total count (source): 1267, total count (target): 226
Missing=[-1]

Warning: Database input/msgstore3.db is not consistent. Column message_media.message_row_id references table message, where we expected to find a list of 3683 entries, but only 3639 were found.

Total count (source): 57612, total count (target): 3683
Missing=[1002, 1792, 1889, 10542, 10693, 12784, 12787, 27335, 27588, 29974, 33087, 33088, 33332, 33348, 33416, 33419, 33420, 33421, 34719, 36360, 38277, 39298, 39534, 39948, 40149, 40379, 42412, 44654, 45568, 48736, 49250, 49626, 49658, 50002, 50315, 51649, 54862, 56191, 58540, 59450, 59604, 59611, 63079, 64171]

[*] Checking consistency of input/msgstore4.db

Warning: Database input/msgstore4.db is not consistent. Column chat.last_read_message_row_id references table message, where we expected to find a list of 81 entries, but only 80 were found.

Total count (source): 61576, total count (target): 383
Missing=[63844]

Warning: Database input/msgstore4.db is not consistent. Column chat.last_read_receipt_sent_message_row_id references table message, where we expected to find a list of 82 entries, but only 77 were found.

Total count (source): 61576, total count (target): 383
Missing=[57046, 51870, 35056, 68089, 63046]

Warning: Database input/msgstore4.db is not consistent. Column chat.last_message_reaction_row_id references table message, where we expected to find a list of 59 entries, but only 44 were found.

Total count (source): 61576, total count (target): 383
Missing=[2669, 3749, 566, 3163, 80, 3499, 2227, 3488, 3067, 3431, 2532, 3024, 1563, 3262, 3741]

Warning: Database input/msgstore4.db is not consistent. Column chat.last_seen_message_reaction_row_id references table message, where we expected to find a list of 59 entries, but only 44 were found.

Total count (source): 61576, total count (target): 383
Missing=[2669, 3749, 566, 3163, 80, 3499, 2227, 3488, 3067, 3431, 2532, 3024, 1563, 3262, 3741]

Warning: Database input/msgstore4.db is not consistent. Column chat.last_read_message_sort_id references table message, where we expected to find a list of 81 entries, but only 79 were found.

Total count (source): 61576, total count (target): 383
Missing=[63844, -9223372036854775808]

Warning: Database input/msgstore4.db is not consistent. Column chat.last_read_receipt_sent_message_sort_id references table message, where we expected to find a list of 82 entries, but only 77 were found.

Total count (source): 61576, total count (target): 383
Missing=[57046, 51870, 35056, 68089, 63046]

Warning: Database input/msgstore4.db is not consistent. Column message.chat_row_id references table chat, where we expected to find a list of 363 entries, but only 362 were found.

Total count (source): 383, total count (target): 61576
Missing=[-1]

Exception in thread "main" java.lang.IllegalStateException: Database input/msgstore4.db is not consistent. Column message_thumbnail.message_row_id references table message, where we expected to find a list of 1893 entries, but only 1888 were found.
Total count (source): 61576, total count (target): 1893
Missing=[25366, 30891, 33486, 40834, 56080]

at dev.natario.ConsistencyKt.verifyConsistency(Consistency.kt:21)
at dev.natario.ConsistencyKt.verifyConsistency$default(Consistency.kt:3)
at dev.natario.MainKt.main(Main.kt:50)

Disabling Consistency Checks

Hi guys,
as I'm still getting consistency errors, I'd like to disable the checks to see what is going to happend.

As mentioned https://github.com/natario1/whatsapp-database-merger/issues/9#issuecomment-1432719716, one should recompile with ignoreConsistencyChecks = true .

But I could not finy any information where to set that option.

I tried the following:
Setting ignoreConsistencyChecks = true in \src\main\kotlin\dev\natario\Table.kt, then

┌──(julian㉿julian-legion)-[/mnt/c/Users/admin/Downloads/whatsapp-database-merger-0.2.0b2(1)/whatsapp-database-merger-0.2.0b2]
└─$ gradle build
openjdk version "17.0.10" 2024-01-16
OpenJDK Runtime Environment (build 17.0.10+7-Debian-1)
OpenJDK 64-Bit Server VM (build 17.0.10+7-Debian-1, mixed mode, sharing)
Starting a Gradle Daemon (subsequent builds will be faster)

> Task :buildEnvironment

------------------------------------------------------------
Root project
------------------------------------------------------------

classpath
No dependencies


BUILD SUCCESSFUL in 1s
1 actionable task: 1 executed

┌──(julian㉿julian-legion)-[/mnt/c/Users/admin/Downloads/whatsapp-database-merger-0.2.0b2(1)/whatsapp-database-merger-0.2.0b2]
└─$ ./gradlew tasks
Downloading https://services.gradle.org/distributions/gradle-7.1-bin.zip
..........10%...........20%...........30%..........40%...........50%...........60%..........70%...........80%...........90%...........100%

Welcome to Gradle 7.1!

Here are the highlights of this release:
 - Faster incremental Java compilation
 - Easier source set configuration in the Kotlin DSL

For more details see https://docs.gradle.org/7.1/release-notes.html

Starting a Gradle Daemon (subsequent builds will be faster)

> Task :tasks

------------------------------------------------------------
Tasks runnable from root project 'whatsapp-database-merger'
------------------------------------------------------------

Application tasks
-----------------
run - Runs this project as a JVM application

Build tasks
-----------
assemble - Assembles the outputs of this project.
build - Assembles and tests this project.
buildDependents - Assembles and tests this project and all projects that depend on it.
buildKotlinToolingMetadata - Build metadata json file containing information about the used Kotlin tooling
buildNeeded - Assembles and tests this project and all projects it depends on.
classes - Assembles main classes.
clean - Deletes the build directory.
jar - Assembles a jar archive containing the main classes.
testClasses - Assembles test classes.

Build Setup tasks
-----------------
init - Initializes a new Gradle build.
wrapper - Generates Gradle wrapper files.

Distribution tasks
------------------
assembleDist - Assembles the main distributions
distTar - Bundles the project as a distribution.
distZip - Bundles the project as a distribution.
installDist - Installs the project as a distribution as-is.

Documentation tasks
-------------------
javadoc - Generates Javadoc API documentation for the main source code.

Help tasks
----------
buildEnvironment - Displays all buildscript dependencies declared in root project 'whatsapp-database-merger'.
dependencies - Displays all dependencies declared in root project 'whatsapp-database-merger'.
dependencyInsight - Displays the insight into a specific dependency in root project 'whatsapp-database-merger'.
help - Displays a help message.
javaToolchains - Displays the detected java toolchains.
kotlinDslAccessorsReport - Prints the Kotlin code for accessing the currently available project extensions and conventions.
outgoingVariants - Displays the outgoing variants of root project 'whatsapp-database-merger'.
projects - Displays the sub-projects of root project 'whatsapp-database-merger'.
properties - Displays the properties of root project 'whatsapp-database-merger'.
tasks - Displays the tasks runnable from root project 'whatsapp-database-merger'.

Verification tasks
------------------
check - Runs all checks.
test - Runs the unit tests.

Rules
-----
Pattern: clean<TaskName>: Cleans the output files of a task.
Pattern: build<ConfigurationName>: Assembles the artifacts of a configuration.

To see all tasks and more detail, run gradlew tasks --all

To see more detail about a task, run gradlew help --task <task>

BUILD SUCCESSFUL in 31s
1 actionable task: 1 executed

┌──(julian㉿julian-legion)-[/mnt/c/Users/admin/Downloads/whatsapp-database-merger-0.2.0b2(1)/whatsapp-database-merger-0.2.0b2]
└─$ ./gradlew run --args=databases

> Task :run
[*] Input databases:
        - input/A54_msgstore.db
        - input/G8s_msgstore.db
[*] Checking consistency of input/A54_msgstore.db
        Column chat.jid_row_id is consistent! All 6570 unique entries (out of 6570) exist in the referenced table jid.
Exception in thread "main" java.lang.IllegalStateException: Database input/A54_msgstore.db is not consistent. Column chat.display_message_row_id references table message, where we expected to find a list of 983 entries, but only 979 were found.
Total count (source): 203730, total count (target): 6570
Missing=[11, 13, 15, 19]
        at dev.natario.ConsistencyKt.verifyConsistency(Consistency.kt:21)
        at dev.natario.ConsistencyKt.verifyConsistency$default(Consistency.kt:3)
        at dev.natario.MainKt.main(Main.kt:50)

> Task :run FAILED

FAILURE: Build failed with an exception.

* What went wrong:
Execution failed for task ':run'.
> Process 'command '/usr/lib/jvm/java-17-openjdk-amd64/bin/java'' finished with non-zero exit value 1

* Try:
Run with --stacktrace option to get the stack trace. Run with --info or --debug option to get more log output. Run with --scan to get full insights.

* Get more help at https://help.gradle.org

BUILD FAILED in 3s
2 actionable tasks: 1 executed, 1 up-to-date

Which obviously did not work.

How can I recompile the whatsapp-database-merger with ignoreConsistencyChecks set to true?

Thank you :)

Convert old database to new schema

Sorry if this is a bit out of topic.

Currently I want to merge two databases, an old one where messages are stored in messages table, and a new db where messages are stored in message table.

I read the new changes made by MrBarbie that makes merging between databases with the new, mid-2022 schema possible. But mine is on different schemas.

I tried restoring the old db into the latest whatsapp version, and creating a backup, then restoring again using the newly generated backup file. But the new backup file is still using old schema, and I'm at a loss here.

So back to the question, is there a way to convert the old db schema into the new one? I know that it is possible to generate a SQL query to do that but the whatsapp db structure is a bit overwhelming for me.

Database is not consistent

After my whatsapp databases are already using the latest/new schema, tried using the version updated by @MrBarbie.

But got some database consistency error, some are just as warning and stopped at RuntimeException for column message_vcard_jid.vcard_row_id. Reading from issue #2, @natario1 suggested to add the column into suggestion.

The thing is I don't know how to compile using gradle and kotlin. Want to try using sencodemaker's merger but it still not updated to account for the latest database schema.

The log is as below.

[*] Input databases:
        - input\msgstore.db
        - input\msgstore1.db
[*] Checking consistency of input\msgstore.db
        Column chat.jid_row_id is consistent! All 698 unique entries (out of 698) exist in the referenced table jid.
        Column chat.display_message_row_id is consistent! All 286 unique entries (out of 698) exist in the referenced table message.
        Column chat.last_message_row_id is consistent! All 286 unique entries (out of 698) exist in the referenced table message.
        Warning: Database input\msgstore.db is not consistent. Column chat.last_read_message_row_id references table message, where we expected to find a list of 235 entries, but only 234 were found.
Total count (source): 95991, total count (target): 698
Missing=[100846]
        Warning: Database input\msgstore.db is not consistent. Column chat.last_read_receipt_sent_message_row_id references table message, where we expected to find a list of 286 entries, but only 285 were found.
Total count (source): 95991, total count (target): 698
Missing=[100846]
        Column chat.last_important_message_row_id is consistent! All 2 unique entries (out of 698) exist in the referenced table message.
        Column chat.change_number_notified_message_row_id is consistent! All 3 unique entries (out of 698) exist in the referenced table message.
        Column chat.last_read_ephemeral_message_row_id is consistent! All 0 unique entries (out of 698) exist in the referenced table message.
        Column chat.last_message_reaction_row_id is consistent! All 9 unique entries (out of 698) exist in the referenced table message.
        Column chat.last_seen_message_reaction_row_id is consistent! All 9 unique entries (out of 698) exist in the referenced table message.
        Warning: Database input\msgstore.db is not consistent. Column chat.last_read_message_sort_id references table message, where we expected to find a list of 196 entries, but only 195 were found.
Total count (source): 95991, total count (target): 698
Missing=[100846]
        Column chat.display_message_sort_id is consistent! All 217 unique entries (out of 698) exist in the referenced table message.
        Column chat.last_message_sort_id is consistent! All 218 unique entries (out of 698) exist in the referenced table message.
        Warning: Database input\msgstore.db is not consistent. Column chat.last_read_receipt_sent_message_sort_id references table message, where we expected to find a list of 187 entries, but only 186 were found.
Total count (source): 95991, total count (target): 698
Missing=[100846]
        Warning: Database input\msgstore.db is not consistent. Column message.chat_row_id references table chat, where we expected to find a list of 457 entries, but only 456 were found.
Total count (source): 698, total count (target): 95991
Missing=[-1]
        Column message.sender_jid_row_id is consistent! All 702 unique entries (out of 95991) exist in the referenced table jid.
        Column message.sort_id is consistent! All 95990 unique entries (out of 95991) exist in the referenced table message.
        Column message_quoted.message_row_id is consistent! All 10651 unique entries (out of 10651) exist in the referenced table message.
        Column message_quoted.chat_row_id is consistent! All 118 unique entries (out of 10651) exist in the referenced table chat.
        Column message_quoted.parent_message_chat_row_id is consistent! All 152 unique entries (out of 10651) exist in the referenced table chat.
        Column message_quoted.sender_jid_row_id is consistent! All 303 unique entries (out of 10651) exist in the referenced table jid.
        Column message_vcard.message_row_id is consistent! All 67 unique entries (out of 71) exist in the referenced table message.
        Column message_vcard_jid.message_row_id is consistent! All 57 unique entries (out of 86) exist in the referenced table message.
Exception in thread "main" java.lang.IllegalStateException: Database input\msgstore.db is not consistent. Column message_vcard_jid.vcard_row_id references table message_vcard, where we expected to find a list of 61 entries, but only 60 were found.
Total count (source): 71, total count (target): 86
Missing=[-1]
        at dev.natario.ConsistencyKt.verifyConsistency(Consistency.kt:21)
        at dev.natario.ConsistencyKt.verifyConsistency$default(Consistency.kt:3)
        at dev.natario.MainKt.main(Main.kt:50)

Too soon for an issue

Hey, thanks for the work. I have very limited experience with Java, tried building the project but got some error. I know very little to conclude what went wrong. Here's the screenshot.

Would it be possible for you to release a jar for this?