Kobo Cover Fixer

[TheSaint]

This program is a project I have only just started work on, though I have been investigating things for a while, and done some experiments. So it is very much a work-in-progress right now, without much to show but a bit for discussion etc.

While I have shortened the program title to just Kobo Cover Fixer, the full name would be something like - Kobo Device Ebook Cover Fixer. Basically it will address missing and badly sized ebook covers. This would be for ebook purchases from Kobo or those side-loaded from other sources (Smashwords, Tor Books, etc).

I am developing this program, because I eventually got fed up with the continual problem of those two issues. Kobo Support pretend they have fixed the missing cover issue, but they haven't. According to them, it is as simple as using the Repair option on your device, and when you can get that to work and complete, then yes for a short period the missing covers are restored, but come maybe even the next sync, some start going missing again. So it is an ongoing annoyance, where you don't see a cover for some of your ebooks when browsing on your device, or when putting the device to sleep, where you should see the cover of the ebook you are currently reading on your E-Ink Kobo device ... instead you see a plain text cover.

At least this is my experience with my Kobo device, which didn't start happening until after my first year with it.

The second issue, I don't know who is at fault. This is the case where some ebooks you sideload, show a smaller offset cover image. I have experimented with replacing an ebook cover image I sourced myself from Kobo or Amazon etc, which required finding where the images were stored on my device, three images for each ebook, and then creating my own gray-scaled versions at the correct dimensions. I then replaced the flawed ones, and rebooted my Kobo device, to see the changes accepted. The program I am developing is based on this success, and today I started to play around with the database file from my Kobo device - KoboReader.sqlite. I viewed that sqlite file using the free SQLiteDatabaseBrowserPortable program, which helped me create a script to view some of the basics. That script is as follows. PLEASE SEE A BETTER VERSION as coded by @jchd in a later post of this first page. Latest version is now at the foot of this first post.

expandcollapse popup

#cs ----------------------------------------------------------------------------

 AutoIt Version: 3.3.14.2
 Author:         TheSaint

 Script Function: Find & Fix missing or wrong size ebook covers on a Kobo device.
    Template AutoIt script.

#ce ----------------------------------------------------------------------------

; FUNCTIONS
; GetContent()

#include <Constants.au3>
#include <WindowsConstants.au3>
#include <Misc.au3>
#include <File.au3>
#include <Array.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>

_Singleton("kobo-cover-fixer-thsaint")

Global $author, $authors, $cnt, $dbfle, $devfile, $ebooks, $entries, $entry, $file, $imageID, $inifile, $ISBN, $pathID, $query, $result, $title, $titles

$authors = @ScriptDir & "\Authors.txt"
$ebooks = @ScriptDir & "\Ebooks.txt"
$inifile = @ScriptDir & "\Settings.ini"
$devfile = @ScriptDir & "\KoboReader.sqlite"
$sqlite = @ScriptDir & "\sqlite3.dll"
$titles = @ScriptDir & "\Titles.txt"

If FileExists($sqlite) Then
    If FileExists($devfile) Then
        GetContent()
    Else
        MsgBox(262192, "Program Error", "The required 'KoboReader.sqlite' file could not be found." & @LF _
            & @LF & "This sqlite file needs to be in the 'Kobo Cover Fixer' folder." _
            & @LF & "The file is located in a folder of your device. Copy the file" _
            & @LF & "from your device to this program's folder.", 0)
    EndIf
Else
    MsgBox(262192, "Program Error", "The required 'sqlite3.dll' file could not be found." & @LF _
        & @LF & "This DLL file needs to be in the 'Kobo Cover Fixer'" _
        & @LF & "folder. It is freely available online.", 0)
EndIf

Exit


Func GetContent()
    _SQLite_Startup()
    If @error Then
        MsgBox(262192, "SQLite Error", "SQLite3.dll cannot be Loaded!")
        Exit
    EndIf
    ;
    $dbfle = _SQLite_Open($devfile)
    If @error Then
        SplashOff()
        MsgBox(262192, "SQLite Error", "Cannot open the existing Database file!", 0)
    Else
        SplashTextOn("", "Please Wait", 180, 100, Default, Default, 33)
        _FileCreate($ebooks)
        $cnt = 0
        $entries = ""
        Sleep(1000)
        $file = FileOpen($ebooks, 1)
        _SQlite_Query($dbfle, 'SELECT Title, Attribution, ISBN, ImageID, ContentID FROM content WHERE Attribution <> ""', $query)
        While _SQLite_FetchData($query, $result) = $SQLITE_OK
            $title = $result[0]
            $author = $result[1]
            $ISBN = $result[2]
            $imageID = $result[3]
            $pathID = $result[4]
            $entry = $author & " - " & $title & " - " & $ISBN & " - " & $imageID & " - " & $pathID
            $entries &= "|" & $entry
            $cnt = $cnt + 1
        WEnd
        $entries = StringSplit($entries, "|", 2)
        ;_ArrayDisplay($entries)
        _ArraySort($entries, 0, 1)
        ;_ArrayDisplay($entries)
        $entries = _ArrayToString($entries, @CRLF, 1)
        FileWriteLine($file, $entries)
        FileWriteLine($file, @CRLF & "Total Ebooks = " & $cnt)
        FileClose($file)
        SplashOff()
    EndIf
    ;
    _SQLite_Close($dbfle)
    _SQLite_Shutdown()
EndFunc ;==> GetContent

The Kobo developers don't make things simple of course. Even the images files found in the hidden kobo-images folder, that has numeric sub folders inside numeric sub folders, and those image files when found have the extension .parsed. I have yet to work out how I can auto discover what is needed without having to browse every sub-folder.

As for the troubleshooting actions with my device, that I have previously engaged in, acting on the advice of Kobo Support. When they work, it is as a I said only a temporary fix. Even when I have done a full Restore, and had to download a few hundred ebooks to my device again, that usually only lasts until the next sync. It doesn't take long before things are screwed again, and I vowed I would never do a full restore again ... too painful, especially with the degree of accuracy demanded for your finger touches. And overall it is just a lot of work, and of course things are no longer the same as ebook order and Collections.

So I need my own solution, that is a more permanent fix.

When you couple the two above issues, with the fact, that after not even a month of having applied a Kobo recommended fix, my device fails to Sync again, I am having to either side-load all my ebook purchases from Kobo, instead of using Wifi, or just force each ebook to download by pretending I want to read each now, otherwise they get stuck in a Pending queue that never completes. They'd likely complete if Sync every completed, but that doesn't happen even after a full day of trying. Just badly implemented software.

So stay tuned for further code etc, as I get the time. I am currently recovering from a health issue, so it will likely be in dribs and drabs.

P.S. I have discussed at length my Kobo issues, in my Reading List for 2023 topic here in Chat.

P.S.S. I don't recall ever having to Restore either of my Kindle E-Ink reader devices, but I've had issues with Amazon over the years that eventually drove me to mostly purchase my ebooks at Kobo ... and I much prefer my Kobo device for reading.

DOWNLOAD

Latest Version: Kobo Cover Fixer.au3

Edited September 11, 2023 by TheSaint

[jchd]

Help file:

Prior to calling _SQLite_Close(), the application must invoke _SQLite_QueryFinalize() for each query explicitely left unfinalized.

Also why process the result in AutoIt instead of having fast SQLite code do it? You don't need all the array sort, string/array massaging in AutoIt. Use SQL!

[TheSaint]

Thanks for that info.

I haven't used SQLITE since probably the last time we discussed ... some years ago probably. So I never used the Help file, just studied some code from my old CalibBrowser program, for a quick implementation, so I probably missed that Finalize command. And likewise I just found it quicker and easier to just do the string array stuff at this point, rather than look into what SQLITE provides. I may improve things, depending on where my code ends up going.

EDIT

You are no doubt using a more recent version of the Help file, and AutoIt too of course. I am currently using AutoIt v3.3.14.2, and while the _SQLite_QueryFinalize() function certainly exists, it is not present in my Help file in the _SQLite_Query function example. Perhaps an oversight or an implementation change?

On further investigation of my Help file, it seems the Finalize command is only required if you want to stop (interrupt) the query, whereas I let the query continue until complete, so according to the example given for that function, I don't see how it is needed.

Edited May 25, 2023 by TheSaint

[Andreik]

If you don't finalize the query then _SQLite_Close() should return SQLITE_BUSY or database connection it's marked as an unusable "zombie". So @jchd it's right, all prepared statements should be finalized in order to avoid resource leaks.

 

Edit: even in AutoIt help file it's stated that.

Quote

Prior to calling _SQLite_Close(), the application must invoke _SQLite_QueryFinalize() for each query explicitely left unfinalized.

 

Edited May 25, 2023 by Andreik

[jchd]

No. Finalize is used to delete a prepared statement from within the library space.

Your code is only using once one statement so there no risk of lockout or memory leak. But in more complex programs, you may have dozens or even hundreds of statements, each run once or a large number of times during the program run. In such case, one typically use sqlite3_prepare_v2 once, run the statement and then use sqlite3_reset to reset the prepared statement to a ready-to-use state for the next time it will be required. This avoids re-preparing (converting statement text to internal bytecode) every time the statement is run.

AutoIt DllCall() is slow so that binding parameters isn't beneficial in most use cases. Ideally one should bind every parameter, I've posted years ago AutoIt functions to that effect.

Yet in your use case, you should be done with something like this:

expandcollapse popup

Func GetContent()
    _SQLite_Startup()
    If @error Then
        MsgBox(262192, "SQLite Error", "SQLite3.dll cannot be Loaded!")
        Exit
    EndIf
    ;
    $dbfle = _SQLite_Open($devfile)
    If @error Then
        SplashOff()
        MsgBox(262192, "SQLite Error", "Cannot open the existing Database file!", 0)
    Else
        SplashTextOn("", "Please Wait", 180, 100, Default, Default, 33)
        _FileCreate($ebooks)
;~         $cnt = 0
;~         $entries = ""
        Sleep(1000)
        _SQLite_QuerySingleRow( _
            $dbfle, _
            "select group_concat(txt, char(13, 10)) || char(13, 10) || 'Total Ebooks = ' || count(*)" & _
            "       from (select Title || ' - ' || Attribution || ' - ' || ISBN || ' - ' || ImageID || ' - ' || ContentID as txt" & _
            "                    from content where Attribution <> '' order by txt)" & _
            "       group by 'abc'", _
            $entries)

;~         _SQlite_Query($dbfle, 'SELECT Title, Attribution, ISBN, ImageID, ContentID FROM content WHERE Attribution <> ""', $query)
;~         While _SQLite_FetchData($query, $result) = $SQLITE_OK
;~             $title = $result[0]
;~             $author = $result[1]
;~             $ISBN = $result[2]
;~             $imageID = $result[3]
;~             $pathID = $result[4]
;~             $entry = $author & " - " & $title & " - " & $ISBN & " - " & $imageID & " - " & $pathID
;~             $entries &= "|" & $entry
;~             $cnt = $cnt + 1
;~         WEnd
;~         $entries = StringSplit($entries, "|", 2)
;~         ;_ArrayDisplay($entries)
;~         _ArraySort($entries, 0, 1)
;~         ;_ArrayDisplay($entries)
;~         $entries = _ArrayToString($entries, @CRLF, 1)

        $file = FileOpen($ebooks, 1)
        FileWriteLine($file, $entries[0])
;~         FileWriteLine($file, @CRLF & "Total Ebooks = " & $cnt)
        FileClose($file)
        SplashOff()
    EndIf
    ;
    _SQLite_Close($dbfle)
    _SQLite_Shutdown()
EndFunc ;==> GetContent

Use the power of SQLite as much as possible, it's free and fast.

EDIT: forgot to mention that SQL string literals use single quotes like in 'abc', while double quotes (or square brackets or backticks, SQLite supports all 3) are used to specify DDL names (tables, fields, indices, ...) to avoid collision with reserved words or operators or when names include spaces or illegal characters.

create table "my little table" ([my unique ID] integer primary key, `the precious value` text, "||" text);
insert into [my little table] values (1, 'abc', 'not an operator!');

 

Edited May 25, 2023 by jchd

[Jos]

There is an _ missing at the end of this line:

26 minutes ago, jchd said:

            $dbfle,

.. and you probably want to use char(13, 10) in stead of char(10, 13).  

[jchd]

Oops. Typed on the fly as I've nothing to test with. Thanks.

Fixed now.

BTW I wonder why the text file would be necessary. Searching the database is way more flexible. It's trivial to add a virtual text search table, so searching for words or segments of text in title or author would be immediate, even with 500,000 books listed.

Edited May 25, 2023 by jchd

[TheSaint]

1 hour ago, Andreik said:

Edit: even in AutoIt help file it's stated that.

Quote

Prior to calling _SQLite_Close(), the application must invoke _SQLite_QueryFinalize() for each query explicitely left unfinalized.

 

As I stated, not in my Help file, for AutoIt v3.3.14.2

And I did read the comments in _SQLiteQueryFinalize in my Help file, and it is my interpretation of them, that I don't need to use it in my scenario.

_SQLiteQueryFinalize is not mentioned in _SQLiteQuery or used in the given example, in my Help file.

[jchd]

The examples are old and badly written. Forget _SQLite_Query, it's a dangerous friend. Prefer SQLite_QuerySingleRow, SQLite_GetTable[2D], _SQLite_GetTableData2D unless you have very specific needs and know what you're doing.

[TheSaint]

44 minutes ago, jchd said:

Yet in your use case, you should be done with something like this:

Thanks for that.

44 minutes ago, jchd said:

Use the power of SQLite as much as possible, it's free and fast.

I would if I am going to use it significantly. My code only takes a few seconds to return with all I need (at this stage). So we will see where I need to go with my program. That single group query might be all I use of SQLite, providing it returns the reference data for folders that I need ... or it may not return what I want, and so I will not use SQLite at all.

Edited May 25, 2023 by TheSaint

[TheSaint]

2 minutes ago, jchd said:

The examples are old and badly written. Forget _SQLite_Query, it's a dangerous friend. Prefer SQLite_QuerySingleRow, SQLite_GetTable[2D], _SQLite_GetTableData2D unless you have very specific needs and know what you're doing.

Okay, I will bear all that in mind.

Once again, thanks for the code and advice.  

Edited May 25, 2023 by TheSaint

[Andreik]

8 minutes ago, jchd said:

Oops. Typed on the fly as I've nothing to test with. Thanks.

Fixed now.

BTW I wonder why the text file would be necessary. Searching the database is way more flexible. It's trivial to add a virtual text search table, so searching for words or segments of text in title or author would be immediate, even with 500,000 books listed.

I usually work with MSSQL so I am not extremely familiar with SQLite since I use it just with AutoIt. When you say virtual text search, do you mean FTS5 extension or something similar to Full-Text Search in MSSQL?

[jchd]

Yes, I mean FTS (3, 4 or5). FTSx is a (set of) virtual tables, at least as SQLite implement them.

[TheSaint]

15 minutes ago, jchd said:

BTW I wonder why the text file would be necessary. Searching the database is way more flexible.

Indeed. I was just using the text file to help with development, and it will likely not be used in the final version.

[jchd]

In case you need, send me a D/L link to your database, list your requirements and I'll try to help as much as I can.

[TheSaint]

Yesterday I spent a lot of time investigating the sqlite file, and the end result was I could only find one column, after looking at all the tables, that had values anything like what I needed for number named sub-folders. I cross-checked many of those and never got a match, so I am left with the conclusion that some kind of formula is being used to determine folder names for the ebook cover images. I also need two numbers not one.

Each ebook on my device, should have three different sized image files. One large one, no doubt used for the sleep cover. A small one used for browsing. And one medium sized one used for store browsing it seems. 

Here is an example, in order of Large, Medium, Small.

G:\.kobo-images\1\211\5e373ce7-f89f-4ef2-aa0b-6f57569aa5fa - N3_FULL.parsed
G:.kobo-images\1\211\5e373ce7-f89f-4ef2-aa0b-6f57569aa5fa - N3_LIBRARY_FULL.parsed
G:\.kobo-images\1\211\5e373ce7-f89f-4ef2-aa0b-6f57569aa5fa - N3_LIBRARY_GRID.parsed

The .parsed files are actually .jpg files.

So at the moment, I have no idea how the '1' sub-folder name is determined or its sub-folder '211'. And that first sub-folder can contain several other number sub-folders, each for a different ebook. The first part of the filename is the actual ImageID, that I have been able to extract for each ebook from the sqlite file. That allowed me to do some checking via code to see what ebook has images and how many, and which ones don't have any or not all three.

 

So I have made some headway, but no idea what ebook belongs to each empty folder. I currently have enough information to fix an ebook where one of the three sizes is missing, so those could be fixed automatically with the click of a button. But at this point it looks like manual interaction will be required to determine and fix the completely empty folders. I guess that once things have been determined, I will have a record to refer to if any of them go missing again or any of the existing ones do ... newer ones excepted.

I've got to tidy up my code a bit before providing it in this topic, as the focus has been on testing various things, rather than a finished script at this point.

So far my script does the following.

1. It creates a file called Ebooks.txt, which contains required elements from the sqlite file. One thing I discovered, was that the sqlite file also contained entries for ebooks not on my device in the 'contents' table. Some of these are just ebooks I have yet to download to my Kobo device, but own, and others appear to be adverts. The only way I could find to differentiate what was what, was that those not on my device, were zero bytes in file size. My device also has about 40 side-loaded ebooks, that have a file name based ImageID that includes author and title.

 

2. It creates a file called Folders.txt, which contains the paths of all ebook images (.parsed files) on my device.

 

As you can see from this last image, folders '185' and '117' and '67' are empty folders.

My script tells me that something like 43 ebooks are missing images, and that's not counting those ebooks that don't have the full complement of three images.

3. A comparison then occurs, a kind of search I guess, that looks for any file in the image folders that has the ImageID in the filename. The result of which you see in the first image of this post, and are stored in a Results.ini file.

My thoughts at this point, are to create placebo image files, with identity data as text in the image, to allow for manual checking and determination, which will require filling each empty folder with at least one of these placebo image files for every ebook with missing cover images. I would then need to reboot my device and browse it to see what is what where. Painful to be sure, but I see no other solution at this point.

Currently I am using my program via a Dropbox, dragging & dropping the '.kobo' device folder onto it. In reality I am using a local 'Kobo' folder on my PC for testing, where I have copied all my device content to, which is why you see it in the paths of that last image. The '.kobo' and '.kobo-images' folders are directly at the root of my Kobo device, with the KoboReader.sqlite file found in the first, and all ebook image files found in the second.

   which after 3 seconds changes to  

P.S. In yet another annoyance with my Kobo device, it has a flaky USB connection, so keeps losing it. Not sure what is going on, because the device still has enough of a USB connection to charge, but just loses its ability to be a drive without being manually reconnected. As you can imagine this makes everything I am trying to do much harder, hence another reason I painfully copied content to a folder on my PC. Sometimes the connection only persists for a few seconds, sometimes I get a few minutes. I am beginning to think I bought a lemon. My wife's Kobo device is also a lemon, and a cheaper smaller model. If the device wasn't so great to actually read with, I would go back to using my Kindle devices. I had something like over a year of use with no trouble, and I treat my devices with kid gloves, and store them in padded sleeves when not in use, and they always have an all-round padded cover when in use.

Edited June 16, 2023 by TheSaint

[jchd]

Missing images you should be able to recreate. Examine the case where all 3 formats exist, look at image parameters (color depth, size, ..., exif data) and resize existing images to the required missing format.

I don't get the folder/subfolder logic either.

[TheSaint]

18 hours ago, jchd said:

Missing images you should be able to recreate. Examine the case where all 3 formats exist, look at image parameters (color depth, size, ..., exif data) and resize existing images to the required missing format.

For sure, and I had already done that prior to starting this project, as a proof of concept.

The issue really, is not so much when one or two of the cover images are missing, as they are easy enough to recreate from backup cover images I have ... sourced in some cases from the image shown on the Kobo web page for the ebook, or from somewhere else like Amazon Kindle web pages, etc. You create the missing images to the same dimensions etc as seen for other ebooks in the .kobo-images folder, rename them from .jpg to .parsed, then copy them to that ebook folder, then reboot the Kobo device to see the results displayed. A copy of trials worked for me.

The issue, is where no images exist, and you don't know what folder they should be in, because no reference can be found. It may even be the case, that the required folders don't even exist, which puts a big spanner in the works right there. I am hoping that is not the case. Currently I have 74 empty folders on my Kobo device, and only 39 ebooks with no images. I am guessing that some of those empty folders are from removed ebooks, maybe all of them. At this stage, until I am ready to do the next level of the program, creating identifiable images in all empty folders, I don't know how elusive the fix might be.

[TheSaint]

Another day of coding and tidying code and adding further elements, plus testing etc.

Here is the revised Results window.

The ADD, CREATE and FIX buttons, don't yet have any code, but the others work.

DOWNLOAD

Kobo Cover Fixer.au3  (47 downloads)  SEE THE FIRST POST in this topic for the latest version.
22.59 kB

Edited September 11, 2023 by TheSaint

[jchd]

Could you send me the original Kobo database if it isn't too big? Either by mail to jcd in the domain antichoc.net or by sending a DL link from some repository.