moving books and database from a device to another: a crazy thought?

[paola]

I have just bought an Aura, and as my Mini is still going strong, my husband has cast an eye on it... I have a lot of sideloaded books on the mini, for which I took a zillion notes, so to avoid losing them, I wonder: what if I sync both devices and then move (via usb) sideloaded books and .sqlite file from the Mini to the Aura (going through my laptop)? Would it work?

[Wolfsbruder]

You also need to copy over your device.salt.conf file as well I believe for your database to work.

[davidfor]

It would probably work. The only place I can think of that would be an issue is the user table. It has a column called "UserKey". The values are different for Touch and my Glo. I don't know how this is generated. But, saving the values from the Aura, copying the database and restoring the value would probably work.

You could dump Bookmark table from one and load it into the other. That is the only table with anything related to the bookmarks.

For either of these to work, you would have to make sure the books are in exactly the same place on both devices. Calibre would do that for you, or connect both at the same time (without calibre or the Kobo desktop running) and copy the books directly from one to the other.

[TechniSol]

Hey,

Be sure to let us all know how the brain transplant works out...

- Dr. Frankenstein(that's pronounced Fraaahnkinsteeen)

Igor, whose brain was that? Abby somebody... bwah, ha, ha!

[paola]

thank you all, sounds like the perfect week-end job! :-)

[davidfor]

Originally Posted by paola

thank you all, sounds like the perfect week-end job! :-)

My first thought was that you have a weird idea of what a "perfect week-end job" is. But, as I spend a lot of weekends writing and testing code, I shouldn't say anything

[paola]

Originally Posted by davidfor

My first thought was that you have a weird idea of what a "perfect week-end job" is. But, as I spend a lot of weekends writing and testing code, I shouldn't say anything

I admit to some morbid pleasusre in playing around with things I don't know much about - in addition, anything which sounds like a good excuse to stay away from the lawn mower is the perfect week-end job

[speakingtohe]

If you copy the database, you will lose the current database on the device you are coping to.

But other than that it should be fine.

First I would just copy the books though and see if that was adequate.

Oddly though, I put my Aura HD SD card in my Sony and the Sony processed the books fine even with reading position intact. I was surprised.

Helen

[paola]

losing the reading position would not be a major problem, as the books I am currently reading are bought from Kobo, so I can always re-sync. What I am really keen upon is preserving the notes and highlights I have made on finished books, many of which were not kobo purchases.

Actually, for some of them I have an additional problem - they were bought from Sony, but the whole sony library has now been transferred to Kobo: I wonder whether my Aura will see the old sideloaded kepub and the imported, original Kepub as two different books....

[speakingtohe]

Originally Posted by paola

losing the reading position would not be a major problem, as the books I am currently reading are bought from Kobo, so I can always re-sync. What I am really keen upon is preserving the notes and highlights I have made on finished books, many of which were not kobo purchases.

Actually, for some of them I have an additional problem - they were bought from Sony, but the whole sony library has now been transferred to Kobo: I wonder whether my Aura will see the old sideloaded kepub and the imported, original Kepub as two different books....

Maybe ask in the kobo utilities plugin thread about the best way to do it. Davidfor would know if anyone does and he is very helpful.

Helen

[davidfor]

Originally Posted by paola

losing the reading position would not be a major problem, as the books I am currently reading are bought from Kobo, so I can always re-sync. What I am really keen upon is preserving the notes and highlights I have made on finished books, many of which were not kobo purchases.

Bookmarks for any purchased kepubs should be synced via the Kobo server. That includes any that you remove from the device but are still in your library shown on the Kobo site.

For sideloaded books, the bookmarks have to be copied from one device to the other somehow. Copying the database would work for this. But, I would dump the data from the Bookmarks table and then load that into the new device. As I said before, if the file paths for the books are identical on both devices, its easy. If they change, it needs a little extra work. But, it would probably be a good idea not to copy the bookmarks for the purchased kepubs. The following SQL statement will do what is needed:

SELECT * 
FROM Bookmark
WHERE VolumeID like 'file%'
AND NOT Hidden = 'true'

Running that against the database, dumping the results to a file and then importing them to the new database will copy the bookmarks for the sideloaded books.

Actually, for some of them I have an additional problem - they were bought from Sony, but the whole sony library has now been transferred to Kobo: I wonder whether my Aura will see the old sideloaded kepub and the imported, original Kepub as two different books....

Yes, the device will see them as two different books. The database uses the books file name as the key when referring to the book. For the purchased kepubs, the file name is a GUID. Something like, "09953760-5b32-4d22-8d12-9e1c7177f26e". The firmware knows what directory to look in for these books. For sideloaded books, it is the full path as the firmware sees it. As an example, this is "file:///mnt/onboard/Bagley, Desmond/Landslide - Desmond Bagley.kepub.epub". If the Sony books get added to your Kobo library and downloaded to the device, they will appear in the library as kepubs with all the extras. The sideloaded versions will still be there.

[paola]

thanks a million davidfor!

So if I get you I should:

1. sync both devices with the cloud
2. connect both devices to my laptop, and transfer sideloaded books from the Mini to the Aura;
3. save "UserKey" from Aura's KoboReader.sqlite ([I]to do so I've gone into "edit" and copied the value in a text file)
4. run the SQL statement you gave me on the Mini KoboReader.sqlite file and save it in a file (CSV I think - this I tried on a mock file and I think I can do that)
5. copy the Mini's KoboReader.sqlite onto the Aura.
6. restore the csv file created in 4.
7. restore (again, with edit, I copied back the cell value) the "UserKey" data saved in 3.
8. disconnect the two readers with fingers crossed

Does this sound ok? Thank you!

EDIT Oops! Something went wrong...
I did it all, but the import of the "dumped" bookmarks did not work (got an error message). Nevertheless, with my fingers tightly crossed, I disconnected the Aura, and trying to open a book I had finished, I got an error message (the book could not be found). Powered off and on, same error message. So did a sync, the Aura downloaded a ton of books. It does see the pdf files, but it won't open the sideloaded kepubs, although they are listed (I guess because of the database). So I'd say it did not work! Never mind, that'll teach me to dump the Mini...

[TechniSol]

Well, at least the Aura didn't break free of the table and start terrorizing the villagers. They really seem to dislike that...

[davidfor]

Originally Posted by paola

thanks a million davidfor!

So if I get you I should:

1. sync both devices with the cloud
2. connect both devices to my laptop, and transfer sideloaded books from the Mini to the Aura;
3. save "UserKey" from Aura's KoboReader.sqlite ([I]to do so I've gone into "edit" and copied the value in a text file)
4. run the SQL statement you gave me on the Mini KoboReader.sqlite file and save it in a file (CSV I think - this I tried on a mock file and I think I can do that)
5. copy the Mini's KoboReader.sqlite onto the Aura.
6. restore the csv file created in 4.
7. restore (again, with edit, I copied back the cell value) the "UserKey" data saved in 3.
8. disconnect the two readers with fingers crossed

Does this sound ok? Thank you!

That's right except that if you are going to copy the complete database, you don't need to dump and reload the bookmarks table. But, if you do the database copy, you should

EDIT Oops! Something went wrong...
I did it all, but the import of the "dumped" bookmarks did not work (got an error message). Nevertheless, with my fingers tightly crossed, I disconnected the Aura, and trying to open a book I had finished, I got an error message (the book could not be found). Powered off and on, same error message. So did a sync, the Aura downloaded a ton of books. It does see the pdf files, but it won't open the sideloaded kepubs, although they are listed (I guess because of the database). So I'd say it did not work! Never mind, that'll teach me to dump the Mini...

I just realised we forgot something. The database has changed with the firmware. Unless you have manually upgraded the Mini, it will have had FW 2.6.2 with dbversion 79. The Aura should have FW3.2.0 and dbversion 95. There are enough differences between these that there could be a problem. The database does get upgraded by the desktop app, but the older firmware would not have been maintaining the extra data in the way the new firmware expected it.

What I would do at this point is to log out and back in on the Aura. The books on the device will be processed and added to the database and library. Take a backup of this database and then load the Bookmark data into this database.

[paola]

Originally Posted by TechniSol

Well, at least the Aura didn't break free of the table and start terrorizing the villagers. They really seem to dislike that...

LOL :-)

Originally Posted by davidfor

I just realised we forgot something. The database has changed with the firmware. Unless you have manually upgraded the Mini, it will have had FW 2.6.2 with dbversion 79. The Aura should have FW3.2.0 and dbversion 95. There are enough differences between these that there could be a problem. The database does get upgraded by the desktop app, but the older firmware would not have been maintaining the extra data in the way the new firmware expected it.

I actually have 3.2.0 on the Mini, too, so on that front we should be ok.

Originally Posted by davidfor

What I would do at this point is to log out and back in on the Aura. The books on the device will be processed and added to the database and library. Take a backup of this database and then load the Bookmark data into this database.

thank you, I will try that tonight.