12-15-2013, 04:53 PM | #1 |
Junior Member
Posts: 8
Karma: 12028
Join Date: Dec 2013
Device: Kobo Aura
|
Database Size on Device
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.
|
12-15-2013, 05:04 PM | #2 |
Grand Sorcerer
Posts: 12,662
Karma: 74532212
Join Date: Nov 2007
Location: Toronto
Device: Libra H2O, Libra Colour
|
Do you have any idea as to which table in the database might have a lot of records in it?
|
Advert | |
|
12-15-2013, 05:20 PM | #3 |
Junior Member
Posts: 8
Karma: 12028
Join Date: Dec 2013
Device: Kobo Aura
|
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.
|
12-15-2013, 05:32 PM | #4 |
Grand Sorcerer
Posts: 12,662
Karma: 74532212
Join Date: Nov 2007
Location: Toronto
Device: Libra H2O, Libra Colour
|
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? |
12-15-2013, 05:51 PM | #5 |
Junior Member
Posts: 8
Karma: 12028
Join Date: Dec 2013
Device: Kobo Aura
|
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.
|
Advert | |
|
12-15-2013, 07:53 PM | #6 |
Grand Sorcerer
Posts: 24,905
Karma: 47303822
Join Date: Jul 2011
Location: Sydney, Australia
Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos
|
Tables in device database
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. |
12-15-2013, 08:45 PM | #7 |
Grand Sorcerer
Posts: 24,905
Karma: 47303822
Join Date: Jul 2011
Location: Sydney, Australia
Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos
|
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): Code:
SELECT * FROM content WHERE ContentType = 6 AND IsDownloaded = 'true' Or to get the chapter rows: Code:
SELECT * FROM content WHERE ContentType in (9, 899) 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. |
12-15-2013, 09:35 PM | #8 |
Junior Member
Posts: 8
Karma: 12028
Join Date: Dec 2013
Device: Kobo Aura
|
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 |
12-16-2013, 04:56 PM | #9 |
Junior Member
Posts: 8
Karma: 12028
Join Date: Dec 2013
Device: Kobo Aura
|
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.
|
12-17-2013, 09:40 PM | #10 |
Junior Member
Posts: 8
Karma: 12028
Join Date: Dec 2013
Device: Kobo Aura
|
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).
|
12-17-2013, 10:43 PM | #11 |
Grand Sorcerer
Posts: 24,905
Karma: 47303822
Join Date: Jul 2011
Location: Sydney, Australia
Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos
|
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. |
12-17-2013, 11:08 PM | #12 | |
Wizard
Posts: 1,847
Karma: 3212428
Join Date: Jun 2011
Device: iphone stanza, kobo touch,ASUS TF300,KOBO GLO, Kobo Aura HD, Kobo Mini
|
Quote:
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 |
|
12-17-2013, 11:47 PM | #13 |
Grand Sorcerer
Posts: 12,662
Karma: 74532212
Join Date: Nov 2007
Location: Toronto
Device: Libra H2O, Libra Colour
|
The covers don't get stored in the database...
|
12-18-2013, 07:50 AM | #14 |
Connoisseur
Posts: 61
Karma: 8174
Join Date: Mar 2012
Device: none
|
the db is terribly designed
|
12-18-2013, 08:02 AM | #15 |
Grand Sorcerer
Posts: 24,905
Karma: 47303822
Join Date: Jul 2011
Location: Sydney, Australia
Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos
|
|
Tags |
aura, database, sqlite |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Books not in device database | davidfor | Development | 5 | 11-07-2012 06:59 AM |
Error communicating with device(Kobo Wireless)/database disk image is malformed | cotej1977 | Devices | 2 | 04-26-2011 09:34 PM |
Error communicating with device(Kobo Wireless)/database disk image is malformed | cotej1977 | Kobo Reader | 1 | 04-24-2011 08:14 AM |
Kobo Desktop erased entire device database! - Don't dowload update | MrsJoseph | Kobo Reader | 9 | 03-23-2011 11:38 AM |
We are unable to update your device's database. Syncing cannot be completed. | talios | Kobo Reader | 1 | 12-10-2010 03:16 AM |