Just thank you :-)

[Pittiplatsch]

I just want to leave a big "THANK YOU".

After 2 days, I finally made it to merge two backups which partly overlapped.

Unfortunately, the --removedoubles switch didn't help me - I always ended with an error message about duplicate keys "not ending well" - funny message btw 👍

As a software developer I am well-familiar with relational data bases - I finally ended in merging the message tables from both backups which overlapped for some weeks. Making use of existing unique keys during import of the second (partly overlapping) backup, and with the help of some additional ON CHANGE CASCADE onto _id primary fields I finally made it to a single consistent message table with primary ids in correct order with respect to their temporal creation.

The file I actually generated could immediately be imported to Signal without any issues.

So again: Thank you very much for that powerful tool which preserved all my precious messages 🙏

[bepaald]

Hi! Thanks for the feedback. And glad you got it working. Did you actually just manually merge the backups in sqlite or something? That's quite a feat.

The current Signal database has a constraint on messages that dictates no two messages can be in the same thread, from the same contact, at the same time. Of course, when merging overlapping backups from the same phone this constraint will be violated. Also, since this change, the --removedoubles option is practically useless as it fixes a problem that can not occur.

The 'correct' way to deal with this is to just crop one of the backups before merging so they don't overlap anymore (https://github.com/bepaald/signalbackup-tools#crop-to-dates). I should probably make a note of that in the readme sometime.

So again: Thank you very much for that powerful tool which preserved all my precious messages 🙏

Thank you for your kind words. I hope you never need this tool again :)

[Pittiplatsch]

Uh, I wasn't aware of the possibilty of cropping backups.

I did all that merging in SQLite.

I think, I had a very special case. My old phone broke, and my backup was a few months old. So I restored that old backup on my new phone, with the hope in mind I could somehow fetch the latest backup from my old phone and merge it later. Therefore, I intentionally didn't add contacts, open new groups, threads etc. with my new phone. Fortunately, I could revive my old phone and access a newer backup - this was the initial situation for the need to merge these backups.

Yepp, I saw - and intentionally used - the message constraint prohibiting dups.

My merge steps:

1. copy existing SQLite db
2. truncate all tables
3. add ON UPDATE CASCADE constraint onto all primary keys
4. import backup 1
5. add 100.000 to all message._ids
6. import backup 2
   1. this filters out all contentually identically messages
   2. allows to import missing messages
7. no problem with identities, groups, threads as I didn't add any of these in the meantime - see above
8. re-sort message records by sent timestamp, assigning new consecutive IDs did this because I excepected Signal to just sort messages simply by _id rather than sent -> this order should be identical if nobody fiddles with the data :wink:
9. forget to remove ON UPDATE CASCADE :grin:
10. DONE (almost)

The only caveat I shortly noticed is that some attachments are now assigned to wrong messages... But this wasn't important enough to spend another couple of hours to additionally fix the attachment files.

Could this be accomplished by cropping the backups, perhaps into three (!) isolated parts ("common period" = old backup; "old phone only period"; "new phone only period")? -> Remember "old phone only period" and "new phone only period" overlap in terms of primary keys!

If you confirm this might work I'll maybe give it a try just for the sake of fiddling :grin:

[bepaald]

My merge steps:

Yes, that mostly makes sense. For step 5, this program actually does _id *= -1.

The only caveat I shortly noticed is that some attachments are now assigned to wrong messages...

Indeed, this is expected. Merging is a very complicated procedure, but is made a lot easier if the backups are nearly identical (all recipient-references in the database are the same in both backups). But still, some other tables that do change (like message) are still referenced. Apart from incorrect attachments, you may also see incorrect @mentions and emoji-reactions. Other bad references are also probably in your database, though less visible (see here for a list of references to the message table _id's).

8. re-sort message records by sent timestamp, assigning new consecutive IDs did this because I excepected Signal to just sort messages simply by _id rather than sent -> this order should be identical if nobody fiddles with the data 😉

Yes, that's smart, even though messages are sorted by date, the _id-order is (at least in the past has been) important for various reasons as well. This tool calls reorderMmsSmsIds() at the end of each import for this reason, this function is also available standalone by running the tool with the (undocumented) --reordermmssmsids switch. This function will do the reordering while keeping the attachment (and other) references correct, though in your case they were pointing to wrong messages at this point anyway.

Could this be accomplished by cropping the backups, perhaps into three (!) isolated parts ("common period" = old backup; "old phone only period"; "new phone only period")?

While merging is a complicated procedure, cropping is not far behind. So I always get slightly nervous when recommending this, but yes, you should be able to get everything working by cropping and merging. No need to crop to three backups, just 2 should be enough. So if you have backup 1 (with time period A+C), and backup 2 (with time period A+B), it should be possible to:

1. signalbackup-tools [backup2] [passphrase] --croptodates "periodBstart-periodB-end" -o [backup2_cropped]
2. signalbackup-tools [backup1] [passphrase] --importthreads all -s [backup2cropped] -sp [passphrase] -o [finalouput]

If you try it, let me know how it goes!

Thanks!

[Pittiplatsch]

Just gave it a try, and for the first glance everything looks good, including attachments have been assigned to correct messages again. -> seems hours of SQLite fiddling have been unnecessary :tired_face:

At least I learned advanced SQLite techniques :satisfied:

So again: thank you so much 👍

[bepaald]

Cool, that's good to hear. Thanks for reporting back!