PRS-T1 Removing "New" Badge from Unread Books

[pssquirrel]

UPDATE: To go straight to the solution, see Post #10 below.

=================================================

I've been trying to figure out a way to batch-remove all the "new" badges that appear on newly-loaded books so that when I re-format my T1 and re-load my library (which I seem to end up doing regularly) I don't have to manually open/close a bazillion books. (PRS+ does this very nicely on my 350, and it's a feature I miss.)

Thanks to some very kind people here on MobileRead, I've just started delving into the books.db, and I'm taking a guess that the "new" badge appears when reading_time is null???

I was thinking of overwriting the null fields with a number, but since I don't really know what I'm doing, I thought I'd run it by those of you who understand these things to make sure I'm not attempting anything stupid. So here's what I'm wondering:

1. Do you think it reasonable that inputting a number in the null fields in the reading_time column will make the "new" badge disappear?

2. What number do you think I should try since the numbers don't seem to correspond to any obvious timestamp that I'm familiar with? Perhaps one digit higher than the number in modified_date?

Screenshots of the pertinent database columns attached.

What think?

[MCSmarties]

I'd be interested in a fix for this too.

One thing I noticed, sometimes books that I know I have read even get the "new" badge back after synching my T1 with calibre!

[rupor]

$pssquirrel

The number is actually a time when book was opened. You could learn about its format by reading SQLLite documentation. It is used in multiple places in firmware, providing for example proper book sorting, proper "Continue reading" book on the front page, e.t.c. So proposed remedy may be much worse than disease...

[pssquirrel]

Thanks Rupor! Glad I asked before messing with my database.

I did figure the number was a timestamp for the last time the book was opened. That's why I thought it was tied to the "new" badge -- i.e., if the field was null, that meant the book had never been opened and would therefore get a "new" badge. At least, that was my thinking.

But I hadn't thought of all the other things the timestamp was used for, which might get messed up if I started adding my own timestamps -- especially if I wasn't sure how those timestamps were formatted. But if one could figure out the format then create a reasonable timestamp ... ???

Then again, inputting all those timestamps may take longer than simply manually opening and closing all my epubs.

[jarsto]

Just looking at your screenshot the timestamp format looks like Unix Time http://en.wikipedia.org/wiki/Unix_Time but with 3 extra digits (so expressed in milliseconds rather than seconds).

Since added_date is higher than modified_date I'd keep reading_time greater than added_date. Also going on my hunch and judging from added time a unix time converter gives me an estimated 'time added to reader' of: 07 / 06 / 12 @ 1:07:50am EST (for the first book in your screenshot)

If that seems pretty much right then my hunch about the timestamp format is probably right. And as I'm considering doing a full reload myself (sorting out my collections slightly differently) I'll be watching this thread with interest.

[pssquirrel]

Yes! Jarsto, I think you're right! Using a Unix time converter, I checked several books in my db and all the timestamps looked right.

So I decided to throw caution to the wind (unusual for me, I know), and did the following:

• Noted three books on my T1 with a "new" badge

• Opened my database and confirmed that those three books with "new" badge had "null" value in the reading_time column

• Copied the timestamp from the most recently read book: 1342846307263

• Replaced the "null" value on the three "new" books with: 1342846308263, 1342846309263, 1342846310263 (I decided to adjust the seconds, as I'm leery of those 3 extra digits, and I based it on the most recently read book to "pretend" that I opened these books after my most recently read book)

• Held my breath, copied the revised database back to my T1, and loaded it

Result:

• "New" badges on above three books disappeared!

• Above three books also appeared at the top of book list when sorted by "Last Read." The books also show "Last Read" times that correspond to the Unix numbers I input.

Based on this, it seems the "new" badges are indeed triggered by a "null" value in the reading_time column and the timestamps are Unix Time plus 3 extra digits (milliseconds?). It also appears that you can remove the "new" badges by inserting your own timestamps, BUT as Rupor noted, the full ramifications of doing so are unclear and possibly dire.

Finally, I'm guessing each timestamp should be unique -- i.e., you can't have opened more than one book at the exact same millisecond, so it may cause problems if you use the same timestamp for more than one book. This makes updating each timestamp more tedious.

Given the above, I'm not sure if it's worth it to remove "new" badges by messing with the database rather than simply opening/closing each book manually. Thoughts anyone?

[grimborg]

I've recently had the same problem you had and I did more or less the same thing, but using the command line. I set the reading time in the far past.

adb shell

cd /mnt/extsd/Sony_Reader/database

sqlite3 books.db

update books set reading_time=1 where reading_time is null;

[pssquirrel]

Interesting. Do I understand correctly that you used the same reading_time for all your books and set the reading_time before the added_date, and it didn't cause any problems? If so, that would make this process a lot easier.

[grimborg]

Originally Posted by pssquirrel

Interesting. Do I understand correctly that you used the same reading_time for all your books and set the reading_time before the added_date, and it didn't cause any problems?

Indeed! That's what I did, and no problems so far

[pssquirrel]

Thanks Grimborg! This is so cool.

Thought I'd summarize everything for those interested:

• "New" badges occur when there is a null value in the reading_time column in the books.db

• Replacing the null value with a Unix timestamp removes the "new" badge. 0 = January 1, 1970 00:00:00 GMT, 1342828800 = today

• The easiest way to remove all new badges in one fell swoop is to use the command line. The following code replaces null values with the added_date so that new badges are removed and the "last read" date is the same as the date the book was added:
  
  sqlite3 [INSERT FILEPATH]books.db "update books set reading_time=added_date where reading_time is null;"
  
  See also posts #7 and #11 by Grimborg and Jarsto above and below.
  

• You can also replace each null value using an SQLite client, such as MesaSQLite (Mac), SQLite Manager Firefox add-on (all platforms) or SQLiteSpy (PC)

• If you want to control the sort-order when sorting by "latest read," you can use sequential numbers: 1, 2, 3, etc. When I tried this, books showed a "last read" date of January 1, 1970, etc. and they appeared in the expected order when sorted by "latest read."

• If you want to restore the "new" badge on selected books (like the ones you haven't read yet), simply type "null" in the reading_time field.

• If you use Kolenka's nifty Recently Opened Hack, be aware that every time you update a reading_time value, it activates the recently_opened_trigger and changes the added_date. If you plan to make reading_time=added_date, it's probably a good idea to delete the trigger first, then re-create it when you're done.

[jarsto]

I did my planned wipe & reload yesterday. And thanks to this thread removing the 'New' badges was quite easy.

I modified grimborg's instructions slightly. I have a script manager installed on my PRS-T1 so I can run simple shellscripts easily when needed. Because I was adding the books back in batches I decided to script the sqlite3 stuff.

#!/system/bin/sh

sqlite3 /mnt/sdcard/Sony_Reader/database/books.db "update books set reading_time=1 where reading_time is null;"

#sqlite3 /mnt/extsd/Sony_Reader/database/books.db "update books set reading_time=1 where reading_time is null;"

I currently have books only in the internal memory (so mounted to /mnt/sdcard) but I decided to put the extsd line in there as well (but commented out) just in case I need it in the future.

I currently have that saved on the READER partition as allread.sh (short for 'mark all as read') and have a it marked as a favourite in my scriptmanager. When I've added something new I just open the scriptmanager, click the script, and then click 'run'. Once I do that all the 'new' markers are gone.

So that's my latest tweak to this. Finally a big thank you to pssquirrel for bringing this up, and to grimborg for sharing the sqlite3 commands. All in all this thread has made reloading my reader a lot easier than it might otherwise have been.

[pssquirrel]

I am loving this command line function (thanks again Grimborg and Jarsto!)

Here's another tweak: "update books set reading_time=added_date where reading_time is null;"

This way each book has a unique reading_time, the date is more meaningful than January 1, 1970, and it still takes only one command.

[Frescard]

What I've done to automatically get rid of the "New" mark is to define a default value for the database cell.
I've set it to 1, so it ends up at the end of the list, but current_timestamp (which inserts today's date, to put it at the top of the list) should work, too.

People tinkering with databases all the time will know what to do—for the rest here are some instructions:

1. You first need a database utility (I'm using SQLite Expert Personal, which is free).
2. Hook up the reader to the PC, and set it to "Data Transfer Mode".
3. Copy the database from [reader]\Sony_Reader\database\books.db to your harddrive (as a backup).
4. Start the SQL manager, and open that file (the one on the reader) in it.
5. Select the "books" table (list on left).
6. Click on the "Design" tab (top of screen, in the middle).
7. Right-click on the line that has "reading_time" in the "Name" column, and select "Modify".
8. In the value for "Default" enter a 1, and click "OK".
9. Click "Apply" (bottom of screen), and close the manager.
10. Copy a new book to the reader, disconnect it, and try it out..

[pssquirrel]

Originally Posted by Frescard

What I've done to automatically get rid of the "New" mark is to define a default value for the database cell.

Nice tweak!

I just wanted to check something, though, since what I know about databases could fit in a thimble. When I attempted the above using SQLite Manager for Firefox, I got this warning:

"This is a potentially dangerous operation. SQLite does not support statements that can alter a column in a table. Here, we attempt to reconstruct the new CREATE SQL statement by looking at the pragma table_info which does not contain complete information about the structure of the existing table. Do you still want to proceed?"

I don't understand what this warning means or what the potential consequences are if I ignore it.

Is it really okay to proceed? Thx!

[Frescard]

I'm not familiar with the Firefox SQL plugin, but setting a cell's default value is a pretty trivial action; and unless you put in the wrong data type, there's really nothing that can go wrong there.

But it seems the plugin is more aimed that at just looking at the data (and perhaps modifying it a bit), rather than doing actual database management.
If you want to play it safe, get a "real" SQL management tool—otherwise, just go ahead with the plugin, and tell it to do the modification. (After all, even if they screw up, you still have your backup.)