Clara BW Automated way to keep track of books read?

[Shohreh]

Hello,

Title says it all: Is there an automated way to keep track of books I read and removed, considering I only read side-loaded books?

If possible, I'd rather avoid re-installing KOReader because the stock firmware is good enough for my needs. Also, I just copy files manually to the Kobo through a file manager — I only bother with Calibre to remove DRM's when needed.

Maybe in the KoboReader.sqlite file?

sqlite> .tables
AbTest                 KoboPlusAssetGroup     Tab
Achievement            KoboPlusAssets         Wishlist
Activity               OverDriveCards         WordList
AnalyticsEvents        OverDriveCheckoutBook  content
Authors                OverDriveLibrary       content_keys
BookAuthors            Reviews                content_settings
Bookmark               Rules                  ratings
DbVersion              Shelf                  shortcover_page
DropboxItem            ShelfContent           user
Event                  SubscriptionProducts   volume_shortcovers
GDriveItem             SyncQueue              volume_tabs

Thank you.

[Sirtel]

With calibre it's easy. Otherwise, no idea.

[Shohreh]

Provided there's no other way to keep track of books, can copying a file to the Kobo through Calibre be done through the command-line?

[Sirtel]

Originally Posted by Shohreh

Provided there's no other way to keep track of books, can copying a file to the Kobo through Calibre be done through the command-line?

I think so, but you'll still need to use the UI to save the reading progress from your Kobo to calibre (the Kobo Utilities plugin does that). I don't think you can do that via command line. I may be wrong, of course.

[Shohreh]

It's possible to list files added to Calibre, but it obviously misses DRM-free books I copied directly to the Kobo

"C:\Program Files\Calibre2\calibredb.exe" list

Maybe someone knows if the Kobo firmware keeps track of all EPUBs ever copied into the reader, which would solve the problem.

[Shohreh]

What about the Event table in KoboReader.sqlite, as copied just after a reboot to avoid the "Runtime error: database disk image is malformed (11)"?

sqlite> .schema Event
CREATE TABLE Event (
        EventType INTEGER NOT NULL,
        FirstOccurrence TEXT,
        LastOccurrence TEXT,
        EventCount INTEGER DEFAULT 0,
        ContentID TEXT,
        ExtraData BLOB, Checksum TEXT,
        PRIMARY KEY (EventType, ContentID) );
		
SELECT DISTINCT ContentID FROM Event ORDER BY LastOccurrence DESC LIMIT 10

import sqlite3, re

conn = sqlite3.connect("KoboReader.sqlite")
cur = conn.cursor()

pattern  = re.compile("^file:///mnt/onboard/(.+)$")

cur.execute("SELECT DISTINCT ContentID FROM Event WHERE ContentID IS NOT '' ORDER BY LastOccurrence LIMIT 10")
for row in cur.fetchall():
	book = m.group(1) if (m := pattern.search(row[0])) else "(not found)"
	print(book)

conn.close()

The wxPython version:

import sys,os, wx
import sqlite3
import re

class ListBoxFrame(wx.Frame):
	def __init__(self, *args, **kwargs):  
		super().__init__(None, title='Books read on Kobo', size=(600, 800))
		
		self.Centre()

		panel = wx.Panel(self, id=wx.ID_ANY)

		sizer = wx.BoxSizer(wx.VERTICAL)

		self.lb1 = wx.ListBox(panel, style=(wx.LB_SINGLE | wx.LB_ALWAYS_SB))
		sizer.Add(self.lb1,1, wx.ALL | wx.EXPAND ,5)

		self.run_btn = wx.Button(panel, label="Read")
		sizer.Add(self.run_btn,0, wx.EXPAND)
		self.run_btn.Bind(wx.EVT_BUTTON, self.OnRunButtonClick)

		panel.SetSizer(sizer)
		panel.Layout()

	def OnRunButtonClick(self,event):
		#clear, just in case
		self.lb1.SetItems([])
		
		self.run_btn.Disable()

		conn = sqlite3.connect("KoboReader.sqlite")
		cur = conn.cursor()

		pattern  = re.compile("^file:///mnt/onboard/(.+)$")
		
		#ignore any .PNG and .TXT
		cur.execute("SELECT DISTINCT ContentID FROM Event WHERE ContentID IS NOT '' AND  ContentID NOT LIKE '%.png' AND ContentID NOT LIKE '%.txt' ORDER BY LastOccurrence")
		for row in cur.fetchall():
			book = m.group(1) if (m := pattern.search(row[0])) else "(not found)"
			self.lb1.Append(book)

		conn.close()

		self.run_btn.Enable()
			
app = wx.App()
ListBoxFrame().Show()
app.MainLoop()

Query DB for string in title:

#Read string from clipboard, find if book in Kobo
import pyperclip
import sqlite3

book = pyperclip.paste()

conn = sqlite3.connect("KoboReader.sqlite")
cur = conn.cursor()

cur.execute(f"SELECT DISTINCT ContentID FROM Event WHERE ContentID LIKE '%{book}%'")
for row in cur.fetchall():
	print(row[0])

conn.close()

[JSWolf]

You cannot do this on the Kobo. You need to use calibre with Kobo Utilities to get the reading stats. Then you have another column to show you've read it when the reading stat for the book is at 100%.

It's not difficult at all.

[Shohreh]

If I can avoid it, I'd rather not bother with that beast of Calibre when I can just use my favorite file manager to copy files from my computer to the Kobo.

Copying KoboReader.sqlite after a fresh reboot (to avoid the "Runtime error: database disk image is malformed" error) and reading its Event table seems to do the job.