View Single Post
Old 07-21-2012, 06: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: 233
Karma: 644088
Join Date: Oct 2011
Device: newbie Kindle Voyage, oldbie Sony T2
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 01:28 AM. Reason: updated info
pssquirrel is offline   Reply With Quote