Touch database and books on reader

[John F]

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.

[echwel]

Originally Posted by John F

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.

Why don't you just use Calibre? With the new driver for the Touch it works very good. Seems very much easier than directly viewing the database.

[John F]

Thank you for the suggestion. I am using Calibre. And I'm still curious.

[PeterT]

Try

select Title from CONTENT 
     where BookID is null and ContentType=6 and Accessibility <> 6 and Accessibility <> 4
     order by title;

I'm not certain about Accessibility but a quick perusal seemed to show those were books I did not actually have, possibly previews

EDIT: From browsing DavidFor's calibre driver it looks like 4 is Recommendations and 6 Previews.

[John F]

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"?

[davidfor]

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.

[John F]

Thanks