![]() |
#1 |
Grand Sorcerer
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() Posts: 7,386
Karma: 66969631
Join Date: Feb 2009
Device: Kobo Glo HD
|
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 Thanks. |
![]() |
![]() |
![]() |
#2 | |
Connoisseur
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() Posts: 68
Karma: 13694
Join Date: Oct 2012
Location: Netherlands
Device: Kobo Sage, Kobo Forma (sold), Kobo Aura One, Kobo Glo, Kobo Touch
|
Quote:
|
|
![]() |
![]() |
Advert | |
|
![]() |
#3 |
Grand Sorcerer
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() Posts: 7,386
Karma: 66969631
Join Date: Feb 2009
Device: Kobo Glo HD
|
Thank you for the suggestion. I am using Calibre. And I'm still curious.
![]() |
![]() |
![]() |
![]() |
#4 |
Grand Sorcerer
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() Posts: 12,534
Karma: 74358018
Join Date: Nov 2007
Location: Toronto
Device: Libra H2O, Libra Colour
|
Try
Code:
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 10:10 AM. |
![]() |
![]() |
![]() |
#5 |
Grand Sorcerer
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() Posts: 7,386
Karma: 66969631
Join Date: Feb 2009
Device: Kobo Glo HD
|
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"? |
![]() |
![]() |
Advert | |
|
![]() |
#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
|
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 from content 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. From that: - 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. |
![]() |
![]() |
![]() |
#7 |
Grand Sorcerer
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() Posts: 7,386
Karma: 66969631
Join Date: Feb 2009
Device: Kobo Glo HD
|
Thanks
|
![]() |
![]() |
![]() |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
New Kobo- No Free Books in Database | razmichael | Kobo Reader | 6 | 01-01-2011 09:46 AM |
Deleting books from a database | candide | Calibre | 6 | 10-26-2010 03:04 PM |
how to remove database names with no books? | setherd | Calibre | 3 | 02-23-2010 09:26 PM |
Database Issues after removing books | mobelby | Calibre | 6 | 05-31-2009 01:28 AM |
Adding books to a large database | Student1 | Calibre | 31 | 04-07-2009 06:43 PM |