How to Remove Books from the I'm Reading List without Scrolling to the End

[farrioth]

While playing around with my new Kobo, I came across the common problem of having no option to remove a book from the 'I'm Reading' list without scrolling to the last page of the last chapter.

So I worked out my own solution. It isn't that time-consuming, but might be a bit complex as it involves editing the SQLite database.

The steps are as follows:

1. Install sqlitebrowser or another sqlite editor.

I'm using Crunchbang, but any system that can run sqlitebrowser should work. On Ubuntu (and probably Debian) based systems, you can install sqlitebrowser with 'apt-get install sqlitebrowser'.

2. Back up your Kobo's database.

If your Kobo is mounted on /media/KOBOeReader, then the database will be at /media/KOBOeReader/.kobo/KoboReader.sqlite.

3. Open the Kobo's database with sqlitebrowser.

4. Go to the 'Browse Data' tab and select the table 'content'.

This may take a while to load.

You will find a record for each chapter of each book, followed by one for the book as a whole. This last entry is the one that needs editing. The fields 'BookID' and 'BookTitle' should be empty (unlike the chapters), and the book's title should be in the 'Title' field. You may use the find window to search for this record.

5. Once you have found the book's entry, set it to be unread.

Set 'FirstTimeReading' to 'true' and 'ReadStatus' to '0'.

If you want to find all books (and documents) that are in the reading list, search for 'ReadStatus' = '1'.

6. Save the database.

7. Unmount and disconnect your Kobo.

After the Kobo has processed new content, the books should no longer appear in the reading list. If something goes wrong, restore the backup of your database.

I take no responsibility for corrupting your Kobo's database, but please let me know if anything doesn't work with these instructions. I have tested them with version 1.4 of the firmware only.

[wrenn1]

Or delete it from your library and re-sync

[farrioth]

Originally Posted by wrenn1

Or delete it from your library and re-sync

Not so useful if you want to keep it in your library (just not on your reading list), or if it's one of the internal books that has no file.

[zoran]

Or simply using regexp 'ReadStatus'='1' and set it to <insert-whatever>?

[farrioth]

Originally Posted by zoran

Or simply using regexp 'ReadStatus'='1' and set it to <insert-whatever>?

If you're talking about using regular expressions on the database file, this would be a challenge, as the file isn't entirely human-readable. You can, however, use an SQL query, which will be something like the following:

UPDATE content
	SET ReadStatus = '0'
	SET FirstTimeReading = 'true'
	WHERE Title = 'Title of Book';

Or for all books by replacing the last line by:

WHERE ReadStatus = '1';

[tpw]

Thanks very much for this information farrioth. It'd really be great to see this little sqlite tip integrated into calibre one day.

[farrioth]

Originally Posted by tpw

Thanks very much for this information farrioth. It'd really be great to see this little sqlite tip integrated into calibre one day.

You're welcome. Indeed it would. I don't imagine it would be too challenging to write a plugin, although I have no idea what the Calibre plugin interface is like.

[Mememememe]

This actually sounds like a lot more work than just going to the last chapter and flipping pages.

[Jafo]

Thanks for sharing farrioth. The more we share, the more we learn! I'll give it a try if for no other reason than to play with the database!

[zoran]

Originally Posted by farrioth

If you're talking about using regular expressions on the database file, this would be a challenge, as the file isn't entirely human-readable. You can, however, use an SQL query, which will be something like the following:

UPDATE content
	SET ReadStatus = '0'
	SET FirstTimeReading = 'true'
	WHERE Title = 'Title of Book';

Or for all books by replacing the last line by:

WHERE ReadStatus = '1';

Used sqlite eons ago. My idea would be to either handle it with
sqlite directly:

http://linux.die.net/man/1/sqlite3

or something in between:

http://www.sqlitemanager.org/

Sounds better to mount the reader and approach the file from
the home box. Frankly, have to read manual for it to speak any
further.
Another topic might be "jump to page XX".

[farrioth]

Originally Posted by Mememememe

This actually sounds like a lot more work than just going to the last chapter and flipping pages.

Well, it depends how long your chapters are...

Originally Posted by Jafo

Thanks for sharing farrioth. The more we share, the more we learn! I'll give it a try if for no other reason than to play with the database!

You're welcome. Let me know if you come across anything else interesting in the database.

Originally Posted by zoran

Used sqlite eons ago. My idea would be to either handle it with
sqlite directly

This would indeed work, but would involve writing some interface code to make it nice. Doing something in Python with pysqlite would probably be quite good, and it appears Calibre uses Python for its plugin interface too.

Originally Posted by zoran

Another topic might be "jump to page XX".

The database seems to store the current position by the paragraph and word (fields are 'ParagraphBookmarked' and 'BookmarkWordOffset'), presumably to account for the font resizing, which presents a bit of a challenge.

[zoran]

Originally Posted by farrioth

This would indeed work, but would involve writing some interface code to make it nice. Doing something in Python with pysqlite would probably be quite good, and it appears Calibre uses Python for its plugin interface too.

Found pysqlite port for bsd. I'm more confident for perl version, but
see none. I assume regexp should search for the whole input of the
book and pipe it as the output.

The database seems to store the current position by the paragraph and word (fields are 'ParagraphBookmarked' and 'BookmarkWordOffset'), presumably to account for the font resizing, which presents a bit of a challenge.

You made your homework! Who said that sqlite implementation in the
next version should not be remade from the scratch?
Since the reader shows page numbers, I'm sure it could follow it and
put bookmark/pointer to it. Probably one function to both do that and
be called from "menu" to find the page in question.

[farrioth]

Originally Posted by zoran

Found pysqlite port for bsd. I'm more confident for perl version, but
see none. I assume regexp should search for the whole input of the
book and pipe it as the output.

Is this the sort of thing you need?

If you're using SQL queries, you shouldn't actually need a regular expression as such, unless you want to search for a partial title or similar.

Originally Posted by zoran

You made your homework! Who said that sqlite implementation in the
next version should not be remade from the scratch?
Since the reader shows page numbers, I'm sure it could follow it and
put bookmark/pointer to it. Probably one function to both do that and
be called from "menu" to find the page in question.

Are you talking about actually doing this device-side?

[zoran]

Originally Posted by farrioth

Is this the sort of thing you need?

Of course. Few more functions should be written to handle the thing.
More I read, less I want to go further. If you ask why, the answer would
be: no need for db of that kind. Simply follow directory tree, pick the
book, load the book, read the book. Bookmark could be tricky, but it
is not unsolvable.

Are you talking about actually doing this device-side?

Yep. The only place where function like that should live on. From time
to time I ask myself why things become complicated without any
reason. People need "go-to-page" more than cover on power down.

[farrioth]

Originally Posted by zoran

Of course. Few more functions should be written to handle the thing.
More I read, less I want to go further. If you ask why, the answer would
be: no need for db of that kind. Simply follow directory tree, pick the
book, load the book, read the book. Bookmark could be tricky, but it
is not unsolvable.

One of the reasons for the existence of the database is to save parsing / loading time. Extracting metadata from a filesystem entry becomes a lengthy process, especially when you have to repeat it a few thousand times. Another issue is the table of contents. It becomes painful to extract and parse a compressed file (toc.ncx) every time you need it, and storing an uncompressed copy would seem redundant when you can store the information you need in a database.

Why implement a database as a filesystem when you can implement it as a database?

These are of course all assumptions based on what little I know about the design of the system.

Originally Posted by zoran

Yep. The only place where function like that should live on. From time
to time I ask myself why things become complicated without any
reason. People need "go-to-page" more than cover on power down.

Indeed. As I'm not a Kobo developer, however, there's little more I can do than wait until the next release.