Register Guidelines E-Books Search Today's Posts Mark Forums Read

Go Back   MobileRead Forums > E-Book Readers > Kobo Reader > Kobo Developer's Corner

Notices

Reply
 
Thread Tools Search this Thread
Old 10-25-2012, 10:24 AM   #1
John F
Wizard
John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.
 
Posts: 2,370
Karma: 8581888
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

Thanks.
John F is offline   Reply With Quote
Old 10-25-2012, 10:41 AM   #2
echwel
Connoisseur
echwel is a rising star in the heavensechwel is a rising star in the heavensechwel is a rising star in the heavensechwel is a rising star in the heavensechwel is a rising star in the heavensechwel is a rising star in the heavensechwel is a rising star in the heavensechwel is a rising star in the heavensechwel is a rising star in the heavensechwel is a rising star in the heavensechwel is a rising star in the heavens
 
Posts: 64
Karma: 13694
Join Date: Oct 2012
Location: Netherlands
Device: kobo glo
Quote:
Originally Posted by John F View Post
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.
echwel is offline   Reply With Quote
 
Advertisement
Old 10-25-2012, 10:55 AM   #3
John F
Wizard
John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.
 
Posts: 2,370
Karma: 8581888
Join Date: Feb 2009
Device: Kobo Glo
Thank you for the suggestion. I am using Calibre. And I'm still curious.
John F is offline   Reply With Quote
Old 10-25-2012, 11:04 AM   #4
PeterT
Taking a break; Fed up
PeterT ought to be getting tired of karma fortunes by now.PeterT ought to be getting tired of karma fortunes by now.PeterT ought to be getting tired of karma fortunes by now.PeterT ought to be getting tired of karma fortunes by now.PeterT ought to be getting tired of karma fortunes by now.PeterT ought to be getting tired of karma fortunes by now.PeterT ought to be getting tired of karma fortunes by now.PeterT ought to be getting tired of karma fortunes by now.PeterT ought to be getting tired of karma fortunes by now.PeterT ought to be getting tired of karma fortunes by now.PeterT ought to be getting tired of karma fortunes by now.
 
PeterT's Avatar
 
Posts: 7,371
Karma: 46288753
Join Date: Nov 2007
Location: Toronto
Device: Wife: Touch, Arc, Vox Me: Nexus 7, Glo
Try
Code:
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.

Last edited by PeterT; 10-25-2012 at 11:10 AM.
PeterT is offline   Reply With Quote
Old 10-25-2012, 11:23 AM   #5
John F
Wizard
John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.
 
Posts: 2,370
Karma: 8581888
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"?
John F is offline   Reply With Quote
Old 10-25-2012, 07:57 PM   #6
davidfor
Grand Sorcerer
davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.
 
Posts: 6,090
Karma: 6238033
Join Date: Jul 2011
Location: Sydney, Australia
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
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.
davidfor is offline   Reply With Quote
Old 10-26-2012, 08:22 AM   #7
John F
Wizard
John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.John F ought to be getting tired of karma fortunes by now.
 
Posts: 2,370
Karma: 8581888
Join Date: Feb 2009
Device: Kobo Glo
Thanks
John F is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Forum Jump

Similar Threads
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


All times are GMT -4. The time now is 10:33 AM.


MobileRead.com is a privately owned, operated and funded community.