|10-25-2012, 10:24 AM||#1|
Join Date: Feb 2009
Device: Kobo Glo
database and books on reader
I want to generate a list of books that are on my touch (Home->Library->Books) by what is in the database.
Looking at the "contents" table it looks like I want to look at entries where ContentType=6, but this returns A LOT of stuff that doesn't show up in the Home->Library->Books.
I'm not seeing any other fields that indicate whether a book is "active". Anyone have a SELECT statement that will only show "active" books? FWIW, I'm on 2.1.1
|10-25-2012, 10:41 AM||#2|
Join Date: Oct 2012
Device: kobo glo
|10-25-2012, 11:04 AM||#4|
Taking a break; Fed up
Join Date: Nov 2007
Device: Wife: Touch, Arc, Vox Me: Nexus 7, Glo
select Title from CONTENT where BookID is null and ContentType=6 and Accessibility <> 6 and Accessibility <> 4 order by title;
EDIT: From browsing DavidFor's calibre driver it looks like 4 is Recommendations and 6 Previews.
Last edited by PeterT; 10-25-2012 at 11:10 AM.
|10-25-2012, 11:23 AM||#5|
Join Date: Feb 2009
Device: Kobo Glo
Thank you Peter. That gives me a lot of rows that are not in Library->Books (User's Guide, Read On, welcome, ...).
It looks like I want to check that DateCreated isn't NULL and ContentID begins with "file"?
|10-25-2012, 07:57 PM||#6|
Join Date: Jul 2011
Device: Kobo Touch, Kobo Glo
With Peter's help, it looks like you have it worked out. But, the following is what I work with in the driver.
The following is the query that the calibre driver runs:
select Title, Attribution, DateCreated, ContentID, MimeType, ContentType, ImageID, ReadStatus, ___ExpirationStatus, FavouritesIndex, Accessibility, IsDownloaded
where BookID is Null
and Accessibility <> 6
and IsDownloaded in ('true', 1)
and not ((___ExpirationStatus=3 or ___ExpirationStatus is Null) )
That is with preview, expired and recommendations options unchecked.
- Rows with null BookID are describe an actual book. If BookID is not null, it is a chapter in the book.
- Accessibility == 6 is a preview, Accessibility == 4 is a recommendation.
- IsDownloaded in ('true', 1) means the book is in the device. Not true means it is not downloaded which means it is a recommendation. With different firmware, this could be false under
- ContentID = 6 is a book. ContendID = 10 is a newspaper or periodical. The driver doesn't actually use this. I only found out about ContendID = 10 recently. If I remember, I'll add these as a shelf "Newspaper/Periodical" in the device list.
- I'm not completely sure about the ___ExpirationStatus test. This is carried over from the old driver. But, the null rows are the chapter rows. I assume 3 means an expired preview or some other sort of time limited book. If that is right, they probably disappear with the next sync with Kobo.
- ReadStatus: "Im_Reading":1, "Read":2,"Closed":3
- FavouritesIndex was used for older firmware to indicate the book is on the Shortlist. Not used with 2.0.0 or later.
If you exclude anything with a contentId starting with "file:///usr/local/" you will exclude the help which aren't listed in the library. That leaves you with all the "physical" books. Excluding mimeType = "application/x-kobo-epub+zip" will cut out the kepubs.
You can see the query the driver uses if you run calibre in debug mode. Look for "KoboTouch:books - query" in the output.
|Thread Tools||Search this Thread|
|Thread||Thread Starter||Forum||Replies||Last Post|
|New Kobo- No Free Books in Database||razmichael||Kobo Reader||6||01-01-2011 10:46 AM|
|Deleting books from a database||candide||Calibre||6||10-26-2010 04:04 PM|
|how to remove database names with no books?||setherd||Calibre||3||02-23-2010 10:26 PM|
|Database Issues after removing books||mobelby||Calibre||6||05-31-2009 02:28 AM|
|Adding books to a large database||Student1||Calibre||31||04-07-2009 07:43 PM|