Kobo Aura HD: Importing existing shelves into Calibre

currentsitguy · 09-03-2014, 01:10 PM

After being forced into reloading my PC I am now recreating my Kobo library. I have a substantial number of shelves on my device and I have recreated the myshelves column in calibre. my question is how can i go about importing all of the shelf labels that already exist on my Kobo back into my new installation of Calibre?

davidfor · 09-04-2014, 01:19 AM

Sorry, there isn't any way to do it. The shelf management has been written as a one-way change. It might be possible to do in the utilities plugin, but, I'm not likely to do it any time soon.

Doing this manually is easier in calibre than on the device. You can select all the books you want in a shelf and use the bulk metadata editor to add them in one go.

currentsitguy · 09-04-2014, 08:47 AM

Quote:

Originally Posted by davidfor

Sorry, there isn't any way to do it. The shelf management has been written as a one-way change. It might be possible to do in the utilities plugin, but, I'm not likely to do it any time soon.

Doing this manually is easier in calibre than on the device. You can select all the books you want in a shelf and use the bulk metadata editor to add them in one go.

Thanks anyways. Adding them bulk is exactly what I was doing prior to the loss and reload of my PC. I have about 1600 books on various subjects stored on the Kobo, of which about half are categorized, and no way of knowing what has and has not been shelved, or what shelves they are even on.

Let me ask you a question. Do we know the database type and structure used in both Calibre and Kobo? Are they both mysql based? I'm just wondering if it would be possible to do a cross-db query and populate out the #myshelves field on the Calibre from the existing data on the Kobo. Maybe I'll have a look at that and see. If I have any level of success I'll let everybody know.

davidfor · 09-04-2014, 10:45 PM

I had a think about this last night and started to add this function to the plugin. I'm about half-way through it and should have something to test tomorrow.

The idea is:
- Display dialog with list of custom columns suitable for the shelves. Also have an option to do for all books. Otherwise, it will be the books selected in the library.
- When OK is pressed, read the shelves from the device for each book. Put these into the column as a comma separated list.

This will only work in any sensible way if the driver is not configured to manage shelves. That means it is only useful when doing something like you are doing.

And writing the above, I just realised I need another option. That is whether to replace the values in the column or append to the current value.

currentsitguy · 09-05-2014, 09:48 AM

Wow, thanks!! I spent a good part of yesterday basically reinventing the wheel poking around through the schemas of both Calibre and Kobo. I'm a Network Admin, not a coder or DB Admin, so it's been a bit slow going for me. I'm truly appreciative that you are taking the time to look at this. Just a couple of technical questions to help further my understanding of what the Kobo is actually doing. I've been attempting to ascertain what table actually store a master list of all books stored on the device. as best as I can determine this seems to be in the "content" table, with shelf names in "Shelf" with cross referencing in "ShelfContent". Am I on the right track so far?

davidfor · 09-06-2014, 02:14 AM

Yes, you are on track. The SQL I am using for this is basically:

Code:

SELECT c.ContentId, c.Title, c.Attribution, sc.ShelfName
FROM content c LEFT OUTER JOIN ShelfContent sc 
		ON c.ContentId = sc.ContentId AND c.ContentType = 6  AND sc._IsDeleted = 'false' 
		JOIN Shelf s ON s.Name = sc.ShelfName AND s._IsDeleted = 'false' 
ORDER BY c.ContentId, sc.ShelfName

That will list each book with the shelves they are on. The "c.ContentType = 6" are the rows that describe each the book. And the "_IsDeleted" columns are a complication to the shelves that Kobo uses for their syncing.

As to adding the function, getting the shelves from the device has been asked for before. But, usually as part of the sync. I have just posted a beta version in the plugins thread. I think it will do what you want. Tell me how it goes and if you have any suggestions to improve it.

currentsitguy · 09-09-2014, 08:42 AM

OK, it seems to have brought in all of the Metadata tags for books, but not the custom shelves I originally created in Calibre. In fact, it seems to have brought in everything except those custom shelves.

davidfor · 09-09-2014, 09:26 AM

To check what you are saying: there are shelves on the device that have books on them that are in the current calibre library and these shelves were not copied back to the column you selected?

And by "brought in all of the Metadata tags for books", what exactly do you mean? "tags" is one of the metadata columns in the library. Did you previously have this as a shelf column?

To help me understand what is happening, can you run calibre in debug mode and do the get the shelves and post the log? If you have a lot of books on the device, it will produce a lot of output, so it is better if you attach the log.

09-03-2014, 01:10 PM	#1
currentsitguy Junior Member Posts: 6 Karma: 10 Join Date: Sep 2014 Device: Kobo Aura HD	Kobo Aura HD: Importing existing shelves into Calibre After being forced into reloading my PC I am now recreating my Kobo library. I have a substantial number of shelves on my device and I have recreated the myshelves column in calibre. my question is how can i go about importing all of the shelf labels that already exist on my Kobo back into my new installation of Calibre?

09-06-2014, 02:14 AM	#6
davidfor 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	Yes, you are on track. The SQL I am using for this is basically: Code: SELECT c.ContentId, c.Title, c.Attribution, sc.ShelfName FROM content c LEFT OUTER JOIN ShelfContent sc ON c.ContentId = sc.ContentId AND c.ContentType = 6 AND sc._IsDeleted = 'false' JOIN Shelf s ON s.Name = sc.ShelfName AND s._IsDeleted = 'false' ORDER BY c.ContentId, sc.ShelfName That will list each book with the shelves they are on. The "c.ContentType = 6" are the rows that describe each the book. And the "_IsDeleted" columns are a complication to the shelves that Kobo uses for their syncing. As to adding the function, getting the shelves from the device has been asked for before. But, usually as part of the sync. I have just posted a beta version in the plugins thread. I think it will do what you want. Tell me how it goes and if you have any suggestions to improve it.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
4 identical shelves for each Tag in Kobo Aura HD	omnimodis78	Calibre	3	12-07-2013 09:05 PM
Aura Can I sync non Kobo shelves between Aura and HDAura?	crochetgeek2010	Kobo Reader	1	10-08-2013 08:37 PM
Kobo Aura HD: No shelves through Calibre	Prepper	Library Management	7	06-22-2013 06:36 AM
Calibre tags My Shelves not importing to Kobo Glo any more?	CherylRainfield	Library Management	17	06-21-2013 08:57 AM
Importing existing Calibre + database?	jphphotography	Calibre	2	10-09-2010 03:35 AM

09-04-2014, 01:19 AM	#2
davidfor 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	Sorry, there isn't any way to do it. The shelf management has been written as a one-way change. It might be possible to do in the utilities plugin, but, I'm not likely to do it any time soon. Doing this manually is easier in calibre than on the device. You can select all the books you want in a shelf and use the bulk metadata editor to add them in one go.

09-04-2014, 10:45 PM	#4
davidfor 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	I had a think about this last night and started to add this function to the plugin. I'm about half-way through it and should have something to test tomorrow. The idea is: - Display dialog with list of custom columns suitable for the shelves. Also have an option to do for all books. Otherwise, it will be the books selected in the library. - When OK is pressed, read the shelves from the device for each book. Put these into the column as a comma separated list. This will only work in any sensible way if the driver is not configured to manage shelves. That means it is only useful when doing something like you are doing. And writing the above, I just realised I need another option. That is whether to replace the values in the column or append to the current value.

09-05-2014, 09:48 AM	#5
currentsitguy Junior Member Posts: 6 Karma: 10 Join Date: Sep 2014 Device: Kobo Aura HD	Wow, thanks!! I spent a good part of yesterday basically reinventing the wheel poking around through the schemas of both Calibre and Kobo. I'm a Network Admin, not a coder or DB Admin, so it's been a bit slow going for me. I'm truly appreciative that you are taking the time to look at this. Just a couple of technical questions to help further my understanding of what the Kobo is actually doing. I've been attempting to ascertain what table actually store a master list of all books stored on the device. as best as I can determine this seems to be in the "content" table, with shelf names in "Shelf" with cross referencing in "ShelfContent". Am I on the right track so far?

09-09-2014, 08:42 AM	#7
currentsitguy Junior Member Posts: 6 Karma: 10 Join Date: Sep 2014 Device: Kobo Aura HD	OK, it seems to have brought in all of the Metadata tags for books, but not the custom shelves I originally created in Calibre. In fact, it seems to have brought in everything except those custom shelves.

09-09-2014, 09:26 AM	#8
davidfor 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	To check what you are saying: there are shelves on the device that have books on them that are in the current calibre library and these shelves were not copied back to the column you selected? And by "brought in all of the Metadata tags for books", what exactly do you mean? "tags" is one of the metadata columns in the library. Did you previously have this as a shelf column? To help me understand what is happening, can you run calibre in debug mode and do the get the shelves and post the log? If you have a lot of books on the device, it will produce a lot of output, so it is better if you attach the log.

Advert

Advert