Combining Histories from 3 installations

[DL Dan]

I have SABnzbd installed on three computers, the last one being the only one I will use going forward. I would really like this most recent installation (4.1.0) to contain the histories of all three installations; thousands of dl's in total. Is there any way I can combine all three histories into the latest installation?

Thanks!

Dan

[sander]

Not from SABnzbd itself.

But: history is in .sabnzbd/admin/history1.db, which is a sqlite3 database.

So maybe you can merge with sqlite3 or sqlitebrowser ?
See https://stackoverflow.com/a/9349763

[vthokies96]

So I went through this process and learned a few things to share in case anyone else runs into issues.

Before merging, just try re-creating a single DB, with no merging at all:

Code: Select all

$ cp history1.db history1.db.bak
$ sqlite3 history1.db .dump > /tmp/dump1.sql
$ sqlite3 new_history1.db < /tmp/dump1.sql
$ cp new_history1.db history1.db

Starting sabnzbd with this new DB resulted in some errors for me about duplicate column names or some such and it created an empty DB to use. The errors were nonsense because sqldiff showed no diffs and the file sizes were the same. However, plain diff did report binary differences. As it turns out, there were a few bytes in the headers that were different, but at least in my case, the only one that mattered was the "user version" starting at bytes 60-63. Modifying it to match the original allowed sabnzbd to load with the new DB correctly. Makes sense as it's ignored by sqlite and is application-specific. I can't post URLs, but search for "sqlite database file format" on the details.

That was the biggest head scratcher. You also need to modify each SQL file that gets generated from the .dump step, though this wasn't hard to figure out based on the error messages:

1. Make sure all CREATE TABLE statements have the IF NOT EXISTS clause.
2. In each INSERT INTO statement, modify the IDs to be unique; each dump will start with ID 1, but the merged will need to have unique IDs.
3. Make sure the CREATE TABLE statements have matching schema (columns). My oldest DB was missing a column the newer ones had (archive). If you have to do this step, make sure to add appropriate NULL values (or whatever defaults) into each INSERT INTO statement.

After merging, modify the header on the merged DB before starting sabnzbd with it. HTH.

[sander]

Well done. Impressive.