Aura Database Size on Device

[ringo]

I've got a few thousand books on my Kobo Aura and have just noticed that the KoboReader.sqlite database on the reader is nearly 700MB in size. Does this seem excessive to anyone else? I compacted the database externally using SQLiteBrowser (there wasn't enough room for the journal file on the reader itself) but this only saved a few MB.

[PeterT]

Do you have any idea as to which table in the database might have a lot of records in it?

[ringo]

Peter - The 'Content' table appears to the be the culprit - over 400k records, each relating to a chapter/section within every individual epub file. Some epubs appear to have over 100 entries each in the table.

[PeterT]

Unfortunately, the Content table is one that is key to the whole Kobo device; it is what gets populated during the "Processing new content" phase after books are side-loaded, and also gets populated during the WiFi / Desktop sync process for kEpubs directly from Kobo.

I had wondered if the Activity table might be large as well?

[ringo]

Nope, only 21 records in 'Activity'. The only other table with a large number of records appears to be 'volume_shortcovers' with 262,173 entries.

[davidfor]

A quick description of the tables and contents might be handy. I'll follow up with some ideas on the counts you are seeing in another post.

- contents - Contains all the details of the books. There is one row per book plus one row per chapter in the book. Except kepubs have two rows per chapter. It also has rows for recommendations. The books have "ContentType=6" and the chapters have "ContentType=9 or ContentType=899". And

- content_keys - I think this is a encryption key for purchased kepubs. There is one row per chapter in the kepubs.

- volume_shortcovers - Has a row for each chapter in the book. It is a cross-reference between the book and chapter rows in the contents table. Plus it has an index to order them.

- volume_tabs - This seems to be a cross reference between books and recommendations for them.

- Activity - One for for things that happen that could be a tile on the home screen. The main issue for size will be the "RecentBook" rows. There should be one per book. Deleting any rows with "enabled='false'" is safe.

- Event - This is one row for each event that happens that Kobo thinks is worth recording. For a book, there will be a row for when it was first seen, opened, closed, finished, a counter of page turns. There are also rows for non-book specific activities. I've posted a list of the events that I have decoded in the past. Clearing this is safe except that you lose stats like the number of finished books.

- Bookmark - A row for each bookmark, annotation or highlight. If you do a lot of annotating, this can be large in both the number of rows and the size of some of the columns. When you delete annotations, they don't get removed immediately. The column "Hidden" is changed to "true". Deleting these is safe.

- Shelf - One row for each shelf. Doesn't tend to have many rows.

- ShelfContent - One row for each book that is on a shelf.

[davidfor]

400K for "a few thousand books" seems high. But, it depends on the number of chapters in the books and the formats. All formats have at least chapter row. epubs have one per chapter in the book and kepubs have two.

To see all the rows for the books (including previews):

SELECT * FROM content WHERE ContentType = 6 AND IsDownloaded = 'true'

The column "NumShortcovers" tells you how many chapter rows there will be for each book. Summing that will give you a quick number of the expected chapter rows. But that doesn't account for the double count for kepubs. It also should be how many rows are in volume_shortcovers.

Or to get the chapter rows:

SELECT * FROM content WHERE ContentType in (9, 899)

For my database, the content table has 9696 rows. There are 513 book rows and sum(NumShortcovers) is 7683. There is enough difference there that I checked and there are 1348 ContentType=899 rows. So, 513+7683+1384=9580. Most of the extra rows will be recommendations.

Again, 262173 rows in volume_shortcovers sounds high for a few thousand books. But again it depends on the number of chapters in the books. I have 7700 rows in volume_shortcovers which is close enough to the above to not worry me.

If you have a look at the tables and the numbers above don't match, then you would need to check the consistency between the tables. And you could remove the extra rows.

An alternative is to logout on the device. This replaces the database with an empty one and takes you through the basic setup. It then adds all the books again. If you have the time, you could take a copy of the database, do the logout and then compare the size. If there isn't much difference, put the copy back on to get back to where you were.

[ringo]

David - Thanks for the advice. By 'few' I meant a library of roughly 4,000 epubs. I've logged out of the device and will start reloading the books shortly. Will let you know how I go.

Cheers,
Rich

[ringo]

Well, after deleting the old database and reloading the same collection of around 4k books the device database is now 725mb in size - slightly larger than before. I have not yet tried to compact the database so that may explain the size difference. Guess I'll just have to keep the bulk of the books on the external micro sd card to ensure there's enough internal storage to accommodate the database.

[ringo]

David - thanks again for your feedback. I looked a bit further into the problem and realized that the Kobo Touch Extended plug-in for Calibre was converting all my epubs to kepubs on upload to the device, greatly increasing the size of the content table (as well as adding quite a bit of extra extra markup to files themselves). I disabled the plug-in, reloaded all the files, and my database is now down to 380mb (from 725mb).

[davidfor]

That does explain a lot of the size. If you don't want to read books as kepubs, it was wasting space. And probably slowing things down.

And just in case: If you want to read some books as kepubs, you can do a conversion to kepub and then send that to the device using the main driver.

[jackastor]

Originally Posted by ringo

I've got a few thousand books on my Kobo Aura and have just noticed that the KoboReader.sqlite database on the reader is nearly 700MB in size. Does this seem excessive to anyone else? I compacted the database externally using SQLiteBrowser (there wasn't enough room for the journal file on the reader itself) but this only saved a few MB.

My data base is currently at 447 mb for 4808 books with 900x1600 cover images on average for my collection. I prefer the higher resolution although I think a lower resolution cover would work as well. And compressing the data base did only save a few mbs as well for me


I think a lot of the data base size is due to the size of the covers, and my collection is strictly ebook no pdf etc., not sure if that makes a difference.

regards

Jack

[PeterT]

The covers don't get stored in the database...

[_pietro]

the db is terribly designed

[davidfor]

Originally Posted by _pietro

the db is terribly designed

The real issue is that Kobo has kept the database compatible with all versions of firmware. That means that while they have added tables and extra columns, they haven't removed anything.