Register Guidelines E-Books Search Today's Posts Mark Forums Read

Go Back   MobileRead Forums > E-Book Readers > Kobo Reader

Notices

Reply
 
Thread Tools Search this Thread
Old 11-27-2018, 10:34 PM   #1
pl8o
Junior Member
pl8o began at the beginning.
 
Posts: 5
Karma: 10
Join Date: Nov 2018
Device: Kobo Aura ONE (previously, original Nook, Nook Simple Touch, Kindle 7)
Question How do I combine multiple Kobo annotation tables?

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.
pl8o is offline   Reply With Quote
Old 11-28-2018, 01:17 AM   #2
davidfor
Grand Sorcerer
davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.
 
Posts: 16,217
Karma: 26150342
Join Date: Jul 2011
Location: Sydney, Australia
Device: Kobo:Touch,Glo,Aura H2O,Glo HD,Aura ONE,Clara HD,Forma;tolino epos
Quote:
Originally Posted by pl8o View Post
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
Quote:
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.
davidfor is offline   Reply With Quote
Old 12-04-2018, 06:23 PM   #3
pl8o
Junior Member
pl8o began at the beginning.
 
Posts: 5
Karma: 10
Join Date: Nov 2018
Device: Kobo Aura ONE (previously, original Nook, Nook Simple Touch, Kindle 7)
@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.
pl8o is offline   Reply With Quote
Old 12-04-2018, 07:31 PM   #4
davidfor
Grand Sorcerer
davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.
 
Posts: 16,217
Karma: 26150342
Join Date: Jul 2011
Location: Sydney, Australia
Device: Kobo:Touch,Glo,Aura H2O,Glo HD,Aura ONE,Clara HD,Forma;tolino epos
Quote:
Originally Posted by pl8o View Post
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.
davidfor is offline   Reply With Quote
Old 12-12-2018, 06:50 PM   #5
pl8o
Junior Member
pl8o began at the beginning.
 
Posts: 5
Karma: 10
Join Date: Nov 2018
Device: Kobo Aura ONE (previously, original Nook, Nook Simple Touch, Kindle 7)
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!
pl8o is offline   Reply With Quote
Old 12-12-2018, 07:13 PM   #6
davidfor
Grand Sorcerer
davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.
 
Posts: 16,217
Karma: 26150342
Join Date: Jul 2011
Location: Sydney, Australia
Device: Kobo:Touch,Glo,Aura H2O,Glo HD,Aura ONE,Clara HD,Forma;tolino epos
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.
davidfor is offline   Reply With Quote
Reply

Tags
annotations, ka1, kobo

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I combine multiple .PDF files into one book? Bobosmite Conversion 11 11-24-2014 07:42 PM
Combine multiple libraries EllieB Library Management 1 09-21-2013 02:17 PM
Multiple tables of contents e.loureiro Kindle Formats 0 02-08-2013 07:09 AM
Trying to combine multiple libraries on new pc hydin Calibre 11 10-01-2010 03:59 PM
Sony PRS900: Annotation across multiple file formats Theseus Sony Reader 3 01-27-2010 10:06 AM


All times are GMT -4. The time now is 07:14 AM.


MobileRead.com is a privately owned, operated and funded community.