Statistics / Reading Life lost

tempest@de · 12-30-2012, 02:32 PM

After trying the firmware 2.3.1 I decided that I was better with the 2.1.5 so I changed it back, but doing so I lost all the Statistics / Reading Life, is there a way to get them back or is it already to late and I can forget them?

I red in the forums that the statistics are in the file KoboReader.sqlite so next time I change the software should I save this file in my PC and then copy it to the glo after having changed the firmware?

Thanks in advance for your help and a Happy new year!

murg · 12-30-2012, 07:27 PM

You can backup your device to your hard drive.

If you do this before installing a later version of the firmware and you want to go backwards: you can factory reset, install the firmware version that the backup matches and then copy the backed up files back to the device.

WARNING: the firmware version and the restored backed-up files MUST match. The firmware is expecting the database to be the same version as it is. If the versions are mismatched, the results are unpredictable, but probably will result in a poorly (or non-) functioning device.

tempest@de · 12-31-2012, 01:52 PM

thanks Murg, next time I will do a backup. Altough I now prefere to not sinc the device so I can control it, and decide if I want to update or not.

buffaloseven · 12-31-2012, 03:36 PM

I was poking around the database trying to find it, since I lost mine in the update to 1.3.1 and would like to restore the values, but can't seem to find it. Any tips?

davidfor · 12-31-2012, 06:33 PM

Not many.

The "content" table holds the details of all the books. This includes the reading status and the last reading position.

The awards are in "Achievement".

I haven't really looked at "Rules" before, but it appears to be a link between the Achievement and Event tables.

The "Event" table must have all the info for the stats. I have had a brief look at it, but have never decoded anything out of it. Except I can now see that event 3 is probably open a new book, event 46 is turn a page and event 9 is did a dictionary lookup. And some of the events don't map to Rules.

buffaloseven · 04-15-2013, 05:41 PM

I've been poking around the Event table trying to find a link between my displayed reading time and can't find much. I'm still trying to experiment and see if I can update my new database with a backup of the database I had before I had to do a wipe.

davidfor · 04-15-2013, 09:05 PM

As mentioned above, I have done some looking at the Event table. The details of the events I have are:

Code:

Id		Description
0	Sync with Kobo  - One for each book synced, but only when a new book is downloaded? LastOccurence is updated
3	Open book
4	Started a digital library  - One for each kepub
5	Finished book
46	Turn page
47	Read all night
48	Read all night
80 	Page Turner
1001	Read 12am-1am
1002	Read 1am-5am
1004	Read 7am-9am
1005	Read 9am-12am
1006	Read 12pm-2pm
1007	Read 2pm-4pm
1008	Read 4pm-6pm
1009	Read 6pm-8pm
1010	Read 8pm-10pm
1011	Read 10pm-12pm
1020	?Close book?
1021	?Open book?

Events 47 to 1011 listed above, are used for the Achievements. But, they might also be used for other things. The blob that is stored with the 46 and 80 events are fairly large. And they look like they hold timestamps.

Also, the list isn't complete. I just noticed events in the range 51 to 68. And I might have the meaning of the last two events swapped.

As to restoring the database, it is reasonably safe to do so as long as it isn't corrupt in some way. If the books are in the same place, then it will be OK. You might have a problem if any kepubs are not on place. But, you can probably delete these and force a download.

buffaloseven · 04-15-2013, 11:17 PM

Thanks David.

I'm starting to wonder if the device actually does some calculations based on events instead of actually just storing the value. I happened to be at 7 hours on the nose (I lost so many hours when I had to wipe the device), which is 0.28 days, 420 minutes or 25200 seconds. I couldn't find any of those values in any fields in the entire SQL database, even with some pretty broad SQL queries. I also looked through and played with entries that had First/Last Occurred values of NULL, thinking that it could logically be stored under one of those events, but could find no entires that seemed to match.

davidfor · 04-16-2013, 12:13 AM

I think the info is in the "ExtraData" column. Unfortunately, that is a blob and needs to be decoded somehow. Exporting the value and looking at it in a text editor, it is probably a serialised object. You can see some names and interpret some values, but not all.

My guess would be that the 1021 stores the start time and 1022 stores the finish time of each reading session for a book. Then the total time for the book is the difference of the pairs summed together. And the total read is the total of these for all books. But, the total for each book might be put in the 5 event record when it is finished. That would make sense as the books finished count is the count the 5's, not the count of books marked as finished in the library.

Jellby · 08-20-2014, 02:55 PM

Quote:

Originally Posted by davidfor

That would make sense as the books finished count is the count the 5's, not the count of books marked as finished in the library.

Would it be possible then add a "5" event to all books marked as read, so that they are counted as "finished" in the stats?

davidfor · 08-20-2014, 08:27 PM

Quote:

Originally Posted by Jellby

Would it be possible then add a "5" event to all books marked as read, so that they are counted as "finished" in the stats?

That's what you would think, but I when I tried, I couldn't get it to work. There are two columns that cause problems.

The "ExtraData" column is a BLOB. I don't know exactly what is in it an how it is encoded.

The "CheckSum" column contains a checksum. But, I don't know what it is calculated on. Everything else in the row? Or just the ExtraData?

If someone knows how to handle those columns, then adding the row should be easy.

rudgas · 12-18-2015, 07:12 AM

I've also started diggin around in the sqlite database and want to expand this a little bit. Especially regarding reading life. (It bugs me that this isn't correctly working when transitioning from mini to glo hd)

Code:

Id		Description
0	Sync with Kobo  - One for each book synced, but only when a new book is downloaded? LastOccurence is updated
3	Open book
4	Started a digital library  - One for each kepub
5	Finished book
46	Turn page
47	Read all night
48	Read all night
80 	Page Turner
1001	Read 12am-1am
1002	Read 1am-5am
1004	Read 7am-9am
1005	Read 9am-12am
1006	Read 12pm-2pm
1007	Read 2pm-4pm
1008	Read 4pm-6pm
1009	Read 6pm-8pm
1010	Read 8pm-10pm
1011	Read 10pm-12pm
1020	?Close book?
1021	?Open book?

Added:

Code:

Id Description
1 got achievement
6 highlight something
9 read between 1am and 5am

27 connnect to facebook
28 share something on facebook
35,36 sign in to account or create kobo account
79 long-press a word in a book (see dictionary)

These are the known event id's.

In the Rules database there is an id corresponding to each reading life achievement followed by an EventType and a GoalValue. Lets look at the Juggernaut:

Id: 1014 EventType: 46 GoalValue: 10000
The EventType is as listed above page turning (i.e. ereader pages)

Now to the checksum.. anyone ANY idea how it is calculated?

I guess this is the point where the reader might overwrite any inserted values if it doesn't match, so my guess is we have to find the correct values the checksum is based on so we can caluculate our own and insert new values.

rudgas · 12-18-2015, 07:38 AM

Just confirmed changing values in the Achievement Table hides the achievement when opening the awards page in the reader.
Tried this with changing the text, hidden value and percent complete.

davidfor · 12-18-2015, 08:13 AM

Quote:

Originally Posted by rudgas

I've also started diggin around in the sqlite database and want to expand this a little bit. Especially regarding reading life. (It bugs me that this isn't correctly working when transitioning from mini to glo hd)

Added:

Code:

Id Description
1 got achievement
6 highlight something
9 read between 1am and 5am

Sorry, that's not what 6 and 9 are.

Event 6 is selecting a quote. The award is "Want the Inverted Comma award? Select your favourite passages using our highlighting feature."

Event 9 is dictionary look up. Originally, this was one Event row counting the number of lookups. But, there was a bug in how that was done. Now, there is a row for each book you have done a lookup in.

Also in my notes since the above post:

Code:

38 - One per kepub. Appears to record each time a book is downloaded from the server
99999 - One for each purchased kepubs that has been opened. No idea what it is counting.

Quote:

These are the known event id's.

In the Rules database there is an id corresponding to each reading life achievement followed by an EventType and a GoalValue. Lets look at the Juggernaut:

Id: 1014 EventType: 46 GoalValue: 10000
The EventType is as listed above page turning (i.e. ereader pages)

Correct. And I'm at 82019 on one of my devices.

And the simplest way to see them is with the query:

Code:

SELECT *
FROM Achievement a LEFT OUTER JOIN Rules r ON a.Id = r.AchievementId

Quote:

Now to the checksum.. anyone ANY idea how it is calculated?

I guess this is the point where the reader might overwrite any inserted values if it doesn't match, so my guess is we have to find the correct values the checksum is based on so we can caluculate our own and insert new values.

Sorry, no real idea. I'm pretty sure it is calculated using all the other columns, but I don't know how they are combined.

rudgas · 12-18-2015, 08:58 AM

As far as I can tell it is nothing obvious like everthing in the row written in a line. At least not if it's a md5 checksum.

This is hugely annoying. Especially since if I look at my events table it is pretty messed up since the transition from mini to glohd.

12-30-2012, 02:32 PM	#1
tempest@de Fanatic Posts: 516 Karma: 4442943 Join Date: May 2009 Location: Portugal Device: Kobo Glo, kindle	Statistics / Reading Life lost After trying the firmware 2.3.1 I decided that I was better with the 2.1.5 so I changed it back, but doing so I lost all the Statistics / Reading Life, is there a way to get them back or is it already to late and I can forget them? I red in the forums that the statistics are in the file KoboReader.sqlite so next time I change the software should I save this file in my PC and then copy it to the glo after having changed the firmware? Thanks in advance for your help and a Happy new year!

04-15-2013, 09:05 PM	#7
davidfor Grand Sorcerer Posts: 24,907 Karma: 47303748 Join Date: Jul 2011 Location: Sydney, Australia Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos	As mentioned above, I have done some looking at the Event table. The details of the events I have are: Code: Id Description 0 Sync with Kobo - One for each book synced, but only when a new book is downloaded? LastOccurence is updated 3 Open book 4 Started a digital library - One for each kepub 5 Finished book 46 Turn page 47 Read all night 48 Read all night 80 Page Turner 1001 Read 12am-1am 1002 Read 1am-5am 1004 Read 7am-9am 1005 Read 9am-12am 1006 Read 12pm-2pm 1007 Read 2pm-4pm 1008 Read 4pm-6pm 1009 Read 6pm-8pm 1010 Read 8pm-10pm 1011 Read 10pm-12pm 1020 ?Close book? 1021 ?Open book? Events 47 to 1011 listed above, are used for the Achievements. But, they might also be used for other things. The blob that is stored with the 46 and 80 events are fairly large. And they look like they hold timestamps. Also, the list isn't complete. I just noticed events in the range 51 to 68. And I might have the meaning of the last two events swapped. As to restoring the database, it is reasonably safe to do so as long as it isn't corrupt in some way. If the books are in the same place, then it will be OK. You might have a problem if any kepubs are not on place. But, you can probably delete these and force a download. Last edited by davidfor; 04-16-2013 at 12:14 AM. Reason: Fixed formatting and had description of 80 wrong.

12-18-2015, 07:12 AM	#12
rudgas Member Posts: 16 Karma: 13138 Join Date: Dec 2015 Device: Kobo mini/Glo HD	I've also started diggin around in the sqlite database and want to expand this a little bit. Especially regarding reading life. (It bugs me that this isn't correctly working when transitioning from mini to glo hd) Code: Id Description 0 Sync with Kobo - One for each book synced, but only when a new book is downloaded? LastOccurence is updated 3 Open book 4 Started a digital library - One for each kepub 5 Finished book 46 Turn page 47 Read all night 48 Read all night 80 Page Turner 1001 Read 12am-1am 1002 Read 1am-5am 1004 Read 7am-9am 1005 Read 9am-12am 1006 Read 12pm-2pm 1007 Read 2pm-4pm 1008 Read 4pm-6pm 1009 Read 6pm-8pm 1010 Read 8pm-10pm 1011 Read 10pm-12pm 1020 ?Close book? 1021 ?Open book? Added: Code: Id Description 1 got achievement 6 highlight something 9 read between 1am and 5am 27 connnect to facebook 28 share something on facebook 35,36 sign in to account or create kobo account 79 long-press a word in a book (see dictionary) These are the known event id's. In the Rules database there is an id corresponding to each reading life achievement followed by an EventType and a GoalValue. Lets look at the Juggernaut: Id: 1014 EventType: 46 GoalValue: 10000 The EventType is as listed above page turning (i.e. ereader pages) Now to the checksum.. anyone ANY idea how it is calculated? I guess this is the point where the reader might overwrite any inserted values if it doesn't match, so my guess is we have to find the correct values the checksum is based on so we can caluculate our own and insert new values.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Glo How to import reading Life Statistics from Kobo Touch to Kobo Glo?	the_m	Kobo Reader	5	12-13-2012 03:40 PM
Touch Reading Life under 1.9.17	nogle	Kobo Reader	4	03-31-2012 05:18 PM
Reading Statistics !	danmagay	Kobo Tablets	0	01-08-2012 04:33 PM
Troubleshooting K3 almost lost response while reading large (10+M) pdf	fifth	Amazon Kindle	2	10-09-2010 12:41 AM
Where can I find reading statistics?	charlieperry	Lounge	1	09-05-2008 04:03 AM

12-30-2012, 07:27 PM	#2
murg No Comment Posts: 3,238 Karma: 23878043 Join Date: Jan 2012 Location: Australia Device: Kobo: Not just an eReader, it's an adventure!	You can backup your device to your hard drive. If you do this before installing a later version of the firmware and you want to go backwards: you can factory reset, install the firmware version that the backup matches and then copy the backed up files back to the device. WARNING: the firmware version and the restored backed-up files MUST match. The firmware is expecting the database to be the same version as it is. If the versions are mismatched, the results are unpredictable, but probably will result in a poorly (or non-) functioning device.

12-31-2012, 01:52 PM	#3
tempest@de Fanatic Posts: 516 Karma: 4442943 Join Date: May 2009 Location: Portugal Device: Kobo Glo, kindle	thanks Murg, next time I will do a backup. Altough I now prefere to not sinc the device so I can control it, and decide if I want to update or not.

12-31-2012, 03:36 PM	#4
buffaloseven Watching the Sky Posts: 234 Karma: 634112 Join Date: Sep 2012 Location: Winnipeg, MB Device: Kobo Aura	I was poking around the database trying to find it, since I lost mine in the update to 1.3.1 and would like to restore the values, but can't seem to find it. Any tips?

12-31-2012, 06:33 PM	#5
davidfor Grand Sorcerer Posts: 24,907 Karma: 47303748 Join Date: Jul 2011 Location: Sydney, Australia Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos	Not many. The "content" table holds the details of all the books. This includes the reading status and the last reading position. The awards are in "Achievement". I haven't really looked at "Rules" before, but it appears to be a link between the Achievement and Event tables. The "Event" table must have all the info for the stats. I have had a brief look at it, but have never decoded anything out of it. Except I can now see that event 3 is probably open a new book, event 46 is turn a page and event 9 is did a dictionary lookup. And some of the events don't map to Rules.

04-15-2013, 05:41 PM	#6
buffaloseven Watching the Sky Posts: 234 Karma: 634112 Join Date: Sep 2012 Location: Winnipeg, MB Device: Kobo Aura	I've been poking around the Event table trying to find a link between my displayed reading time and can't find much. I'm still trying to experiment and see if I can update my new database with a backup of the database I had before I had to do a wipe.

04-15-2013, 11:17 PM	#8
buffaloseven Watching the Sky Posts: 234 Karma: 634112 Join Date: Sep 2012 Location: Winnipeg, MB Device: Kobo Aura	Thanks David. I'm starting to wonder if the device actually does some calculations based on events instead of actually just storing the value. I happened to be at 7 hours on the nose (I lost so many hours when I had to wipe the device), which is 0.28 days, 420 minutes or 25200 seconds. I couldn't find any of those values in any fields in the entire SQL database, even with some pretty broad SQL queries. I also looked through and played with entries that had First/Last Occurred values of NULL, thinking that it could logically be stored under one of those events, but could find no entires that seemed to match.

04-16-2013, 12:13 AM	#9
davidfor Grand Sorcerer Posts: 24,907 Karma: 47303748 Join Date: Jul 2011 Location: Sydney, Australia Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos	I think the info is in the "ExtraData" column. Unfortunately, that is a blob and needs to be decoded somehow. Exporting the value and looking at it in a text editor, it is probably a serialised object. You can see some names and interpret some values, but not all. My guess would be that the 1021 stores the start time and 1022 stores the finish time of each reading session for a book. Then the total time for the book is the difference of the pairs summed together. And the total read is the total of these for all books. But, the total for each book might be put in the 5 event record when it is finished. That would make sense as the books finished count is the count the 5's, not the count of books marked as finished in the library.

12-18-2015, 07:38 AM	#13
rudgas Member Posts: 16 Karma: 13138 Join Date: Dec 2015 Device: Kobo mini/Glo HD	Just confirmed changing values in the Achievement Table hides the achievement when opening the awards page in the reader. Tried this with changing the text, hidden value and percent complete.

12-18-2015, 08:58 AM	#15
rudgas Member Posts: 16 Karma: 13138 Join Date: Dec 2015 Device: Kobo mini/Glo HD	As far as I can tell it is nothing obvious like everthing in the row written in a line. At least not if it's a md5 checksum. This is hugely annoying. Especially since if I look at my events table it is pretty messed up since the transition from mini to glohd.