Aura ONE How do I combine multiple Kobo annotation tables?

[pl8o]

Hello all,

I currently read on a Kobo Aura ONE. I’ve had it replaced under warranty three times: once due to dud front-light, screen failure, and once after it went insane when I plugged it into a Samsung USB charger.

Each time I had an up-to-date backup of my annotations, though, and now I’d like to combine the annotations from my three devices so that I can have all my annotations available on my current reader, and can export them using this (sadly unmaintained) Python script.

(I didn’t want my current reader to inherit the various psychological ailments my previous readers had suffered from, which is why I didn’t load SD card backup from the last reader onto the new one each time I got a replacement.)

As I understand it, the annotations are all stored in the Bookmark table of the KoboReader.sqlite database. So:

1. Would it be it reasonably safe to combine the bookmarks tables from the different devices?
   
2. Is there anything else I would need from other tables of the database in order for the annotations to work?
   
3. Has anyone come up with a simple, safe way to combine annotations — or do I need to hack around with SQLite on my own?

Thanks for your help!


P.S. I’m new to the forum, and I’m not sure whether this belongs here or in the developer corner. Feel free to move it if it would fit better there.

[davidfor]

Originally Posted by pl8o

I currently read on a Kobo Aura ONE. I’ve had it replaced under warranty three times: once due to dud front-light, screen failure, and once after it went insane when I plugged it into a Samsung USB charger.

Why do I have a picture of the Aura ONE attacking the Samsung phone that was sitting next to the charger? And the desire to tell it to "pick on someone its own size".

Sorry, that's the way my sense of humour goes

Each time I had an up-to-date backup of my annotations, though, and now I’d like to combine the annotations from my three devices so that I can have all my annotations available on my current reader, and can export them using this (sadly unmaintained) Python script.

(I didn’t want my current reader to inherit the various psychological ailments my previous readers had suffered from, which is why I didn’t load SD card backup from the last reader onto the new one each time I got a replacement.)

As I understand it, the annotations are all stored in the Bookmark table of the KoboReader.sqlite database. So:

1. Would it be it reasonably safe to combine the bookmarks tables from the different devices?
   
2. Is there anything else I would need from other tables of the database in order for the annotations to work?
   
3. Has anyone come up with a simple, safe way to combine annotations — or do I need to hack around with SQLite on my own?

Sorry, but I haven't seen a method to reload annotations. I have done it very occasionally, but in a very manual method. It is reasonably safe. The main issue is the ContentId and the VolumeId. For sideloaded books, these are basically the full path to where the book is. If the books are in the same location and have the same file names, this can be done. If these are different, you would need to get the new ids from the current database. There is another id, but it is a GUID, so you would have to be extremely unlucky to have a clash.

It really depends on how you have the old annotations. If you have a CSV file produced by the script you referred to, it might be a problem. Looking at the data it stores, there isn't really enough to completely restore the annotation. The text and book is there, but not the chapter or location in the chapter. That means it could be recreated and put on the correct book, but you would basically have all bookmarks on the first chapter.

If you have a backup of the databases from each of the devices, I would start from them. In this case I would:
- Dump the contents of the Bookmark table from backups of the databases.
- Massage that into an appropriate file to load into the current database.
- Do the load.

The massaging depends on the format dump. I'd probably find a tool that did the dump as a set of INSERT statements, or created a CSV. The massaging would then just be making sure the ids are correct. Then use the tool to load the CSV or run the inserts.

There is another store for annotations for sideloaded books. This is in the "Digital Editions" directory on the device. If you had these, restoring the annotations is as simple as restoring these files (assuming file paths are the same). But, they are only reread when the books are opened.

Hopefully that helps. If you are going ahead with this, I'm happy to answer any questions about the steps.

[pl8o]

@davidfor Thanks for your very helpful reply!

My replacement Aura ONE arrived today. I have the SQLite DBs from my previous devices, and the digital editions folder from at least one of them, but not all. Are the annotations that are in the digital editions folder just duplicates of what is in the Bookmarks table? If so, I will ignore the digital editions annotations and just merge the Bookmark tables from the different DBs.

[davidfor]

Originally Posted by pl8o

My replacement Aura ONE arrived today. I have the SQLite DBs from my previous devices, and the digital editions folder from at least one of them, but not all. Are the annotations that are in the digital editions folder just duplicates of what is in the Bookmarks table? If so, I will ignore the digital editions annotations and just merge the Bookmark tables from the different DBs.

The annotations files are basically duplicates of what is in the database. But, they are only for epubs and PDFs. If you are annotating any other formats, you will need to use the database. In any case, that is what I would choose to do even if I had the complete set of Digital Editions files.

[pl8o]

Thanks for your help @davidfor. I think I’ve done it!

I exported all ~7 backed up Bookmark tables to SQL using DB Browser for SQLite (default settings.)

The only prep that seemed to be necessary (fingers crossed) before importing the SQL dumps:

- Remove the lines before the first INSERT statement, which would cause the transaction to fail.

- Search for "INSERT INTO" in all the SQL dumps and replace it with "INSERT OR IGNORE INTO". (All the DBs had the annotations for the few books I purchased from the Kobo store, and, naturally, those had duplicate BookmarkIDs (the primary key).)

I then loaded the books with heavy annotations onto my Kobo from SD card backups, rather than Calibre, to ensure I ended up with the same edition of the book in the same place, where Kobo could match it up with the appropriate annotations.

I haven’t done much testing but, so far it’s looking good: It didn’t brick my reader, and all the books that I have tried seem to have annotations where there ought to be annotations.

Thanks again @davidfor!

---

P.S. As someone who has been an active member of a number of forums over the years, and run a few forums as well, I know how much difference super helpful and knowledgeable people such as yourself make to newbies, and the folks who run the forum — as well as how much time it takes to help all the entitled newbies who come crying for help. So, thanks!

[davidfor]

That all sounds good. I hadn't thought of adding the "OR IGNORE" and would have just edited them out. But, that is simpler.

And I'm glad I could help.