How can I merge Chatty databases?

Hardware Librem 5
1

While switching between the L5 and Pinephone I’ve ended up with two sets of SMS/MMS messages. I would like to merge these so I have one unified set of messages.

I see from Chatty and storage of messages that there’s a database in ~/.purple/chatty/db/chatty-history.db, and looking through this in a text editor, I can see the text of my messages and my contacts’ information. However, I have zero experience with SQL, and I don’t know if the variant (sqlite, mysql) matters, or even how to interact with a database using commands. My starting point here is that I’ve made a complete copy of the home directory from both phones and have these copies on a third computer. So, two questions:

  1. How can I merge two of these databases?
  2. If merging does not preserve MMS messages, is there a way to preserve those too?
1 Like
2

I would start with making a copy of the file and explore the structures of the tables with:

sqlite3 /tmp/chatty-history.db
sqlite> .help
sqlite> .tables
accounts        files           messages        mm_messages     threads
file_metadata   message_files   mime_type       thread_members  users
sqlite> .headers 1
sqlite> select * from users;
...

Then I would create with data from the other database load files, perhaps CSV, for the above named tables.

I’ve done such things for PostgreSQL and Sybase, but never for sqlite or mysql.

3

That sounds like a hazardous starting point. :wink:

The previous post gets you heading in the direction. However unless it is a really simple database you could end up having to read source code in order to understand how the tables are used.

If that proves to be all too hard then a fun alternative (for the future) might be to have two logins to the Librem 5. The default login would get you your ongoing and existing content on the Librem 5. The alternative login would get you stuff migrated from the Pinephone.

Another (future) approach could be that Chatty itself supports exporting and importing.