11-27-2018, 10:34 PM | #1 |
Junior Member
Posts: 8
Karma: 10
Join Date: Nov 2018
Device: Kobo Sage (prev, Nook, Nook Simple Touch, Kindle 7, Kobo Touch, KA1
|
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:
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. |
11-28-2018, 01:17 AM | #2 | ||
Grand Sorcerer
Posts: 24,905
Karma: 47303822
Join Date: Jul 2011
Location: Sydney, Australia
Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos
|
Quote:
Sorry, that's the way my sense of humour goes Quote:
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. |
||
Advert | |
|
12-04-2018, 06:23 PM | #3 |
Junior Member
Posts: 8
Karma: 10
Join Date: Nov 2018
Device: Kobo Sage (prev, Nook, Nook Simple Touch, Kindle 7, Kobo Touch, KA1
|
@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. |
12-04-2018, 07:31 PM | #4 | |
Grand Sorcerer
Posts: 24,905
Karma: 47303822
Join Date: Jul 2011
Location: Sydney, Australia
Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos
|
Quote:
|
|
12-12-2018, 06:50 PM | #5 |
Junior Member
Posts: 8
Karma: 10
Join Date: Nov 2018
Device: Kobo Sage (prev, Nook, Nook Simple Touch, Kindle 7, Kobo Touch, KA1
|
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! |
Advert | |
|
12-12-2018, 07:13 PM | #6 |
Grand Sorcerer
Posts: 24,905
Karma: 47303822
Join Date: Jul 2011
Location: Sydney, Australia
Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos
|
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. |
Tags |
annotations, ka1, kobo |
|
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 |