Kobo sqlite database

[darkadept]

Originally Posted by corona

I just used SQLite Expert -- free graphical sqlite program -- and opened up the database to delete dozens of the freebie books -- worked great.

Just make sure you delete the corresponding book and chapter entries in the "content" table and the entries in "volume_shortcovers" and "shortcover_page" as well.

There is also an XML file that contains the id's of book on the device but I haven't looked into that yet.

While the Kobo and the desktop software *should* work with a out if sync database having cluttering entries in the database is less than optimal.

[corona]

Deleting the book and chapter entries in the "content" table does give a pleasing result, i.e. the books no longer show up in the directory on the Kobo. However, since the stuff is still in there, the database doesn't get any smaller. I don't know much about databases; I'm trying to figure out some sqlite routine that will get all of the linked chapter and page entries to go away (because doing it manually sure would take time -- well maybe not for a single book).

[karenv]

And, if this is a stupid question as well, I apologise.... When you are finished will it be fairly easy for techno-wienies to figure out? And, I have a Mac.

[yawar]

Hi karenv,

Originally Posted by karenv

And, if this is a stupid question as well, I apologise.... When you are finished will it be fairly easy for techno-wienies to figure out? And, I have a Mac.

It's still in very early development but I am following along and trying out whatever darkadept is putting up. The program runs for me on my Mac but there are a few issues (for example, see https://bugs.launchpad.net/koboko/+bug/576793). I'll try to work on getting it to run properly, hopefully this weekend.

[HarryH]

I am not in the db mode , looked at the db with Sqlite Expert

Not sure what to delete , I would like to tidy bu the db and my 'I am reading' page ... Have found this forum interesting

Impressed with the Kobo , but as mentioned , needs some fine tuning

Harry

[PeterT]

I removed a lot of stuff from the "I'm Reading" view by the following SQL

update content 
     set readstatus = 2 
where 
     ReadStatus = 0 and 
     Publisher = 'Gutenberg' and 
     booktitle is null

It's still all on the Kobe; just now marked as finished.

[darkadept]

Originally Posted by PeterT

I removed a lot of stuff from the "I'm Reading" view by the following SQL

update content 
     set readstatus = 2 
where 
     ReadStatus = 0 and 
     Publisher = 'Gutenberg' and 
     booktitle is null

It's still all on the Kobe; just now marked as finished.

If the readstatus is 0 you just need to set FirstTimeReading to true.
I'm not sure why they use both ReadStatus and FirstTimeReading but they do.

You can try queries like this:

UPDATE content SET ReadStatus=0, FirstTimeReading='True'
WHERE BookTitle is NULL and Title like '%A Book Title%'

Just replace A Book Title with the title of your book. The percent signs act as wildcards. You can remove them as you wish.

This will set the book as completely unread and new to the kobo.

[darkadept]

I've moved the wiki for Koboko to http://wiki.durbn.net/koboko/index.php/Main_Page. Anyone can edit it.

[davidkettle]

For anyone who wants to try, here's an open-source tool for editing Sqllite databases:

http://sqlitebrowser.sourceforge.net/

But I feel it's only fair to note that you can probably render your Kobo totally unuseable if you screw up the database on it. You should back it up before making any changes to it yourself.

I haven't tried this myself, although I would like to reverse-engineer the thing a little, just out of curiosity.

[darkadept]

Originally Posted by davidkettle

For anyone who wants to try, here's an open-source tool for editing Sqllite databases:

http://sqlitebrowser.sourceforge.net/

But I feel it's only fair to note that you can probably render your Kobo totally unuseable if you screw up the database on it. You should back it up before making any changes to it yourself.

I haven't tried this myself, although I would like to reverse-engineer the thing a little, just out of curiosity.

Here's a wiki I've been using to deconstruct the database. Feel free to add to it: http://wiki.durbn.net/koboko/index.php/DBSchemaV2.

[Ghoda]

Awesome info darkadapt - it helped me to remove a lot of the kruft books I didn't want and get some 60mb of storage back. I have figured out the format of the .parsed files and posted a quick description here. Feel free to add it to your site

[darkadept]

Originally Posted by Ghoda

Awesome info darkadapt - it helped me to remove a lot of the kruft books I didn't want and get some 60mb of storage back. I have figured out the format of the .parsed files and posted a quick description here. Feel free to add it to your site

Thanks! I've added that information to the wiki and added a section detailing the folder layout on the device.

[corona]

I have been finding this Firefox SQLite manager add-on very useful and speedy:

https://addons.mozilla.org/en-US/firefox/addon/5817/

[HarryH]

hi

I have a sqlite database browser om my linux machine , it opens in a three tab window (KoboReader.sqlite), structure , browse data and execute sql.. in the first tab is the sql tree , the second the contents data, all 4737 entries.. there is a delete record item on the upper right .. The file is a copy on my HD , not on my reader, If I mess it up not a problem .. Looking for a few ideas on what to do ...

And if it is possible to remove/ edit all the entries... how is or what happens if it is copied on the Kobo reader

Harry

[darkadept]

I've expanded on my initial findings at my wiki (http://wiki.durbn.net/koboko/index.php/DBSchemaV2). Feel free to add what you know as well.