Register Guidelines E-Books Search Today's Posts Mark Forums Read

Go Back   MobileRead Forums > E-Book Readers > Sony Reader > Sony Reader Dev Corner

Notices

Reply
 
Thread Tools Search this Thread
Old 07-20-2012, 04:25 PM   #1
pssquirrel
ebooknut
pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.
 
pssquirrel's Avatar
 
Posts: 205
Karma: 573170
Join Date: Oct 2011
Device: Sony T2 & T1 (previously PRS-350)
Removing "New" Badge from Unread Books

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?
Attached Thumbnails
Click image for larger version

Name:	screenshot.png
Views:	132
Size:	35.5 KB
ID:	89479  

Last edited by pssquirrel; 07-21-2012 at 05:09 PM. Reason: solution found
pssquirrel is offline   Reply With Quote
Old 07-20-2012, 04:51 PM   #2
MCSmarties
Enthusiast
MCSmarties has much to be proud ofMCSmarties has much to be proud ofMCSmarties has much to be proud ofMCSmarties has much to be proud ofMCSmarties has much to be proud ofMCSmarties has much to be proud ofMCSmarties has much to be proud ofMCSmarties has much to be proud ofMCSmarties has much to be proud ofMCSmarties has much to be proud ofMCSmarties has much to be proud of
 
Posts: 45
Karma: 27066
Join Date: Jun 2012
Location: Pittsburgh, PA
Device: Sony PRS-T1
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!
MCSmarties is offline   Reply With Quote
Old 07-20-2012, 07:14 PM   #3
rupor
Addict
rupor shares his or her toysrupor shares his or her toysrupor shares his or her toysrupor shares his or her toysrupor shares his or her toysrupor shares his or her toysrupor shares his or her toysrupor shares his or her toysrupor shares his or her toysrupor shares his or her toysrupor shares his or her toys
 
rupor's Avatar
 
Posts: 208
Karma: 5658
Join Date: Jul 2007
Location: USA
Device: PRS 500,505,900,650,T1,T2, Nook ST, Kindle PW2
$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...
rupor is offline   Reply With Quote
Old 07-20-2012, 11:32 PM   #4
pssquirrel
ebooknut
pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.
 
pssquirrel's Avatar
 
Posts: 205
Karma: 573170
Join Date: Oct 2011
Device: Sony T2 & T1 (previously PRS-350)
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.
pssquirrel is offline   Reply With Quote
Old 07-21-2012, 03:07 AM   #5
jarsto
Member
jarsto can program the VCR without an owner's manual.jarsto can program the VCR without an owner's manual.jarsto can program the VCR without an owner's manual.jarsto can program the VCR without an owner's manual.jarsto can program the VCR without an owner's manual.jarsto can program the VCR without an owner's manual.jarsto can program the VCR without an owner's manual.jarsto can program the VCR without an owner's manual.jarsto can program the VCR without an owner's manual.jarsto can program the VCR without an owner's manual.jarsto can program the VCR without an owner's manual.
 
Posts: 10
Karma: 191174
Join Date: Jul 2012
Device: PRS-T1
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.
jarsto is offline   Reply With Quote
Old 07-21-2012, 10:45 AM   #6
pssquirrel
ebooknut
pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.
 
pssquirrel's Avatar
 
Posts: 205
Karma: 573170
Join Date: Oct 2011
Device: Sony T2 & T1 (previously PRS-350)
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?

Last edited by pssquirrel; 07-21-2012 at 05:12 PM. Reason: updated info
pssquirrel is offline   Reply With Quote
Old 07-21-2012, 10:50 AM   #7
grimborg
Enthusiast
grimborg shares his or her toysgrimborg shares his or her toysgrimborg shares his or her toysgrimborg shares his or her toysgrimborg shares his or her toysgrimborg shares his or her toysgrimborg shares his or her toysgrimborg shares his or her toysgrimborg shares his or her toysgrimborg shares his or her toysgrimborg shares his or her toys
 
Posts: 41
Karma: 5514
Join Date: Oct 2009
Location: Groningen, Netherlands
Device: PRS-T1
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;
grimborg is offline   Reply With Quote
Old 07-21-2012, 10:55 AM   #8
pssquirrel
ebooknut
pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.
 
pssquirrel's Avatar
 
Posts: 205
Karma: 573170
Join Date: Oct 2011
Device: Sony T2 & T1 (previously PRS-350)
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.
pssquirrel is offline   Reply With Quote
Old 07-21-2012, 02:39 PM   #9
grimborg
Enthusiast
grimborg shares his or her toysgrimborg shares his or her toysgrimborg shares his or her toysgrimborg shares his or her toysgrimborg shares his or her toysgrimborg shares his or her toysgrimborg shares his or her toysgrimborg shares his or her toysgrimborg shares his or her toysgrimborg shares his or her toysgrimborg shares his or her toys
 
Posts: 41
Karma: 5514
Join Date: Oct 2009
Location: Groningen, Netherlands
Device: PRS-T1
Quote:
Originally Posted by pssquirrel View Post
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
grimborg is offline   Reply With Quote
Old 07-21-2012, 05:07 PM   #10
pssquirrel
ebooknut
pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.
 
pssquirrel's Avatar
 
Posts: 205
Karma: 573170
Join Date: Oct 2011
Device: Sony T2 & T1 (previously PRS-350)
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.

Last edited by pssquirrel; 07-28-2012 at 12:28 AM. Reason: updated info
pssquirrel is offline   Reply With Quote
Old 07-23-2012, 03:47 AM   #11
jarsto
Member
jarsto can program the VCR without an owner's manual.jarsto can program the VCR without an owner's manual.jarsto can program the VCR without an owner's manual.jarsto can program the VCR without an owner's manual.jarsto can program the VCR without an owner's manual.jarsto can program the VCR without an owner's manual.jarsto can program the VCR without an owner's manual.jarsto can program the VCR without an owner's manual.jarsto can program the VCR without an owner's manual.jarsto can program the VCR without an owner's manual.jarsto can program the VCR without an owner's manual.
 
Posts: 10
Karma: 191174
Join Date: Jul 2012
Device: PRS-T1
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.

Code:
#!/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.
jarsto is offline   Reply With Quote
Old 07-27-2012, 07:38 PM   #12
pssquirrel
ebooknut
pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.
 
pssquirrel's Avatar
 
Posts: 205
Karma: 573170
Join Date: Oct 2011
Device: Sony T2 & T1 (previously PRS-350)
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.
pssquirrel is offline   Reply With Quote
Old 09-12-2012, 09:11 PM   #13
Frescard
Enthusiast
Frescard plays well with othersFrescard plays well with othersFrescard plays well with othersFrescard plays well with othersFrescard plays well with othersFrescard plays well with othersFrescard plays well with othersFrescard plays well with othersFrescard plays well with othersFrescard plays well with othersFrescard plays well with others
 
Posts: 29
Karma: 2910
Join Date: Aug 2012
Location: Montreal, Canada
Device: Sony PRS-T1
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..
Attached Thumbnails
Click image for larger version

Name:	readingtime.gif
Views:	107
Size:	141.7 KB
ID:	92273  

Last edited by Frescard; 09-13-2012 at 01:56 PM.
Frescard is offline   Reply With Quote
Old 09-16-2012, 01:02 PM   #14
pssquirrel
ebooknut
pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.pssquirrel ought to be getting tired of karma fortunes by now.
 
pssquirrel's Avatar
 
Posts: 205
Karma: 573170
Join Date: Oct 2011
Device: Sony T2 & T1 (previously PRS-350)
Quote:
Originally Posted by Frescard View Post
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!
pssquirrel is offline   Reply With Quote
Old 09-16-2012, 04:52 PM   #15
Frescard
Enthusiast
Frescard plays well with othersFrescard plays well with othersFrescard plays well with othersFrescard plays well with othersFrescard plays well with othersFrescard plays well with othersFrescard plays well with othersFrescard plays well with othersFrescard plays well with othersFrescard plays well with othersFrescard plays well with others
 
Posts: 29
Karma: 2910
Join Date: Aug 2012
Location: Montreal, Canada
Device: Sony PRS-T1
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.)
Frescard is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Recipe for Safari "Reading List" unread items anoved Recipes 6 03-19-2013 01:21 PM
How change "Read" to "UnRead" on a SubForum? unboggling Feedback 3 05-20-2012 02:48 AM
KT "Ghost covers/files" again at 670 books, "stale" image entries in firmware VirgoGirl Kobo Reader 4 04-06-2012 02:10 PM
Yep. It's official. Sony Reader has "ruined" books for me. A final "review." WilliamG Sony Reader 48 01-14-2011 03:49 AM
How to remove "Fully read" books from "Last Open" list? pjeanetta PocketBook 4 12-08-2010 10:30 AM


All times are GMT -4. The time now is 01:53 PM.


MobileRead.com is a privately owned, operated and funded community.