Aura H2O 2 KoboReader.sqlite - Event - Reading Stats

[Karmylla]

Hey everyone,

Long time lurker, I've finally decided to make an account and join your ranks, and I'm glad I did!

I come to you with an issue I've seen others fall victim to: I logged off my device and all my reading stats and awards went back to zero.
I went and checked back all the books I had read, but the total amount of hours remained zero; since I'm someone who loves statistics and checking the numbers, this reset broke my heart.

And so started my journey in search for an answer!
I found, thanks to this forum, that I must edit the EVENT page in the KoboReader.sqlite file. So far, so good! The issue is that I don't know how or where to add those numbers that translate to hours!
No, that's a lie, I know I have to edit the ExtraData but I'm a little lost regarding the binary code; blobs kinda suck.
Also, to add the total amount of hours - or the hours of each book, rather - I should edit the ones who have the EventType 5, correct? Or maybe 46? (Or maybe both, idk guys, I'm lost, can you tell?)

If someone could give me some guidance, I would very much appreciate it!

Cheers, and Happy New Year!

[davidfor]

I would recommend just stopping. I have looked at the event table a lot over the years and posted what I know. But, I have never decoded the contents of the ExtraData column. I can see some things in it, but, not enough to do anything. And, there is also Checksum column. This is a checksum across more than one column in the table. I have no idea how it is calculated, but I know when I have tried fiddling with some of the data, it just didn't work.

If you have a backup of the database, then you can probably restore the data. But, I wouldn't recommend anything else.

[Karmylla]

Welp, if davidfor says so, I must believe it.
All the answers regarding this issue have been given by you, so I'll just leave things as they are, sad I lost my statistics but alas, nothing can be done.
Thanks for replying, I'll be sure to backup my database all the time. Praise Kobo Utilities

[geek1011]

No, there is a way I found a few versions ago. I made a patch which allows you to add an arbitrary amount to the reading stats
I just need to update it for the latest firmware. I'll post it here later today.

[geek1011]

Here is the patch (for libnickel.so.1.0.0.yaml):

Manipulate reading stats:
  - Enabled: yes
  - Description: Always add 10 hours to the reading time every time you close a book.
  # in EventEngine::endReadingSession(*Volume) when recording end of reading session stats, change QDateTime::secsTo calls to a fixed value
  - ReplaceBytes: {Offset: 0x4D53A0, FindBLX: 0x425270, ReplaceH: 4F F6 FF 70} # MOV  r0, #65535
  - ReplaceBytes: {Offset: 0x4D53C0, FindBLX: 0x425270, ReplaceH: 4F F6 FF 70} # MOV  r0, #65535

Just apply it, open and close any epub book the # of hours you want / 10, then remove the patch.

[Karmylla]

Originally Posted by geek1011

Here is the patch (for libnickel.so.1.0.0.yaml):

Manipulate reading stats:
  - Enabled: yes
  - Description: Always add 10 hours to the reading time every time you close a book.
  # in EventEngine::endReadingSession(*Volume) when recording end of reading session stats, change QDateTime::secsTo calls to a fixed value
  - ReplaceBytes: {Offset: 0x4D53A0, FindBLX: 0x425270, ReplaceH: 4F F6 FF 70} # MOV  r0, #65535
  - ReplaceBytes: {Offset: 0x4D53C0, FindBLX: 0x425270, ReplaceH: 4F F6 FF 70} # MOV  r0, #65535

Just apply it, open and close any epub book the # of hours you want / 10, then remove the patch.

Thanks for helping geek1011, but the truth is I don't know how to patch Kobo as you say!
I checked your patches but I truly don't know how to make them work. Do you perhaps have a tutorial for noobs like me?

Edit 1: Ok, I'm following your instructions at "Instructions for patching firmware 4.12.12111" (https://www.mobileread.com/forums/sh...=reading+stats)
Hopefully I'll be able to get this going!

Edit 2: Ok, I got it to work, surprisingly! Though, the hours went up 30 instead of 10, don't know what that was about, but it was all good, thank you!
I have a question you might be able to answer: I was more interested that, every time I tagged a book as finished, the number of hours from that book would be added the book's statistics as well as the total of hours; maybe I'm being picky, but might this be something that can be done?

[geek1011]

Originally Posted by Karmylla

Thanks for helping geek1011, but the truth is I don't know how to patch Kobo as you say!
I checked your patches but I truly don't know how to make them work. Do you perhaps have a tutorial for noobs like me?

Edit 1: Ok, I'm following your instructions at "Instructions for patching firmware 4.12.12111" (https://www.mobileread.com/forums/sh...=reading+stats)
Hopefully I'll be able to get this going!

Edit 2: Ok, I got it to work, surprisingly! Though, the hours went up 30 instead of 10, don't know what that was about, but it was all good, thank you!
I have a question you might be able to answer: I was more interested that, every time I tagged a book as finished, the number of hours from that book would be added the book's statistics as well as the total of hours; maybe I'm being picky, but might this be something that can be done?

No problem! And as for the 30 hours ... oops, I forgot to update the comment when I modified the patch.

I don't completely understand your last question. Could you give an example?

[Karmylla]

Originally Posted by geek1011

No problem! And as for the 30 hours ... oops, I forgot to update the comment when I modified the patch.

I don't completely understand your last question. Could you give an example?

Ok, so, you know how in each book's statistics, it shows the total of hours you spent reading it (which correlates with the total amount of hours it takes to read each book)? Is there a way to add those numbers?
This would imply changing the number of hours in each finished book, so I don't know if it's manageable.

[geek1011]

Originally Posted by Karmylla

Ok, so, you know how in each book's statistics, it shows the total of hours you spent reading it (which correlates with the total amount of hours it takes to read each book)? Is there a way to add those numbers?
This would imply changing the number of hours in each finished book, so I don't know if it's manageable.

Yes, but you'd need to do it for each book manually (until I finish reverse-engineering the checksum, which is quite a fun [but time consuming] challenge). To set the amount of time added in the patch, convert it to seconds, then go here: http://armconverter.com/ and enter MOVW R0, #65535, where 65535 is the number of seconds to add (65535 is the max). Then, copy the output in the Thumb HEX box into the ReplaceH part of the patch.

[rigogsilva]

why? why? Why I don't have access to my reading data. Why does it have to be a blob.

Well, I am a data engineer and I live for data. I am new to e-readers, and I have spent a good amount of time trying to get my reading stats data. It should just be available!

Well. Here it goes. I am taking stab at the ExtraData. Seems like a lot of people have failed at it, or not tried hard enough.

Thinking I can maybe put together a "god" python library to decode this blob. Here is what I have so far:

```
wordsRead {'length': 9, 'value': 35, 'start_position': 4, 'end_position': 26}
wordCounts {'length': 2871, 'value': 2930, 'start_position': 35, 'end_position': 59}
eventTimestamps {'length': 2871, 'value': 5835, 'start_position': 2930, 'end_position': 2964}
ViewType {'length': 5, 'value': 5860, 'start_position': 5835, 'end_position': 5855}
Annotations {'length': 0, 'value': 5886, 'start_position': 5860, 'end_position': 5886}
Orientation {'length': 5, 'value': 5917, 'start_position': 5886, 'end_position': 5912}
Portrait {'length': 0, 'value': 5937, 'start_position': 5917, 'end_position': 5937}
ExtraDataReadingSessions {'length': 9, 'value': 5998, 'start_position': 5937, 'end_position': 5989}
ExtraDataReadingSeconds {'length': 9, 'value': 6057, 'start_position': 5998, 'end_position': 6048}
ExtraDataDateCreated {'length': 49, 'value': 6150, 'start_position': 6057, 'end_position': 6101}
ContentType {'length': 5, 'value': 6181, 'start_position': 6150, 'end_position': 6176}
plcto/-ooeu+i {'length': 0, 'value': 6212, 'start_position': 6181, 'end_position': 6212}
ButtonPressCount {'length': 6266, 'value': 6248, 'start_position': 6212, 'end_position': 6248}
```

I know most of the values with length 9 are good. The others need some work.

[geek1011]

Originally Posted by rigogsilva

why? why? Why I don't have access to my reading data. Why does it have to be a blob.

Well, I am a data engineer and I live for data. I am new to e-readers, and I have spent a good amount of time trying to get my reading stats data. It should just be available!

Well. Here it goes. I am taking stab at the ExtraData. Seems like a lot of people have failed at it, or not tried hard enough.

Thinking I can maybe put together a "god" python library to decode this blob. Here is what I have so far:

```
wordsRead {'length': 9, 'value': 35, 'start_position': 4, 'end_position': 26}
wordCounts {'length': 2871, 'value': 2930, 'start_position': 35, 'end_position': 59}
eventTimestamps {'length': 2871, 'value': 5835, 'start_position': 2930, 'end_position': 2964}
ViewType {'length': 5, 'value': 5860, 'start_position': 5835, 'end_position': 5855}
Annotations {'length': 0, 'value': 5886, 'start_position': 5860, 'end_position': 5886}
Orientation {'length': 5, 'value': 5917, 'start_position': 5886, 'end_position': 5912}
Portrait {'length': 0, 'value': 5937, 'start_position': 5917, 'end_position': 5937}
ExtraDataReadingSessions {'length': 9, 'value': 5998, 'start_position': 5937, 'end_position': 5989}
ExtraDataReadingSeconds {'length': 9, 'value': 6057, 'start_position': 5998, 'end_position': 6048}
ExtraDataDateCreated {'length': 49, 'value': 6150, 'start_position': 6057, 'end_position': 6101}
ContentType {'length': 5, 'value': 6181, 'start_position': 6150, 'end_position': 6176}
plcto/-ooeu+i {'length': 0, 'value': 6212, 'start_position': 6181, 'end_position': 6212}
ButtonPressCount {'length': 6266, 'value': 6248, 'start_position': 6212, 'end_position': 6248}
```

I know most of the values with length 9 are good. The others need some work.

Sorry, but your solution is quite fragile and is a bit more complex than needed. The blob is just a QDataStream. If you don't want to use Qt (through C++, PyQt, or whatever), I've written a decoder for it in Go here: https://gist.github.com/geek1011/a41...1dc481020c5290.

[rigogsilva]

Originally Posted by geek1011

Sorry, but your solution is quite fragile and is a bit more complex than needed. The blob is just a QDataStream. If you don't want to use Qt (through C++, PyQt, or whatever), I've written a decoder for it in Go here: https://gist.github.com/geek1011/a41...1dc481020c5290.

Thanks a lot. I will take a look!

[rigogsilva]

Originally Posted by geek1011

Sorry, but your solution is quite fragile and is a bit more complex than needed. The blob is just a QDataStream. If you don't want to use Qt (through C++, PyQt, or whatever), I've written a decoder for it in Go here: https://gist.github.com/geek1011/a41...1dc481020c5290.

So, when selecting the blob from the sqlite, I get this value:

```
b'000000030000001E006500760065006E007400540069006D 0065007300740061006D007000730000000900000000010000 0003005EBF873C000000080057006F007200640000000A0000 00001200700072006F0073006500630075007400650000001C 00440069006300740069006F006E006100720079004E006100 6D00650000000A00000000040065006E'
```

Which doesn't work with your script. Wondering if the example string used in the go script was converted to something else?

[geek1011]

Originally Posted by rigogsilva

So, when selecting the blob from the sqlite, I get this value:

```
b'000000030000001E006500760065006E007400540069006D 0065007300740061006D007000730000000900000000010000 0003005EBF873C000000080057006F007200640000000A0000 00001200700072006F0073006500630075007400650000001C 00440069006300740069006F006E006100720079004E006100 6D00650000000A00000000040065006E'
```

Which doesn't work with your script. Wondering if the example string used in the go script was converted to something else?

It does work. I got '{"DictionaryName":"en","Word":"prosecute","eventT imestamps":[1589610300]}'.

https://play.golang.org/p/EnWAEYdmunk

[rigogsilva]

Originally Posted by geek1011

It does work. I got '{"DictionaryName":"en","Word":"prosecute","eventT imestamps":[1589610300]}'.

https://play.golang.org/p/EnWAEYdmunk

it does work!!! You are the best! Can I use your script in my python library? Will definitely put your name on it.

I was using a bad one that hit the `panic(not all read)` line.

This is the one:

```
00000007000000100056006900650077005400790070006500 00000A0000000014004D007900410072007400690063006C00 65007300000016004F007200690065006E0074006100740069 006F006E0000000A00000000100050006F0072007400720061 00690074000000300045007800740072006100440061007400 6100520065006100640069006E006700530065007300730069 006F006E00730000000200000000010000002E004500780074 00720061004400610074006100520065006100640069006E00 67005300650063006F006E0064007300000002000000014800 00002800450078007400720061004400610074006100440061 0074006500430072006500610074006500640000000A000000 00280032003000320030002D00300035002D00310034005400 310039003A00320031003A00310039005A000000160043006F 006E00740065006E007400540079007000650000000C000000 001E6170706C69636174696F6E2F782D6B6F626F2D68746D6C 2B706F636B6574000000200042007500740074006F006E0050 00720065007300730043006F0075006E00740000000001FFFF FFFF
```