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 10-13-2011, 11:58 PM   #1
Kolenka
<Insert Wit Here>
Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.
 
Kolenka's Avatar
 
Posts: 973
Karma: 1254645
Join Date: Jan 2008
Location: Puget Sound
Device: Sony T2, Kindle Paperwhite
Recently Opened on Homescreen Hack for T1

So, I've been spoiled by PRS+ on my 950 letting me show the most recently opened books on the home screen. I wanted something similar on my T1 when I suddenly had some inspiration on how it can be done: SQL Triggers!

A little bit of time, and now I have my home screen behaving differently. Instead of just the recently added books there, it also includes recently opened books. So even when I download a new book, it will also display, making it quite handy.

The following SQL code needs to be run against books.db on both internal memory, and the micro-SD card (if you have one). How to run it is unfortunately left up to the reader at this point. You will need some sort of SQLite client that lets you run SQL queries directly (like MesaSQLite on OS X).

Not entirely user-friendly, but for those who with a bit of know-how, it's been working fine for me on the stock firmware.

Code:
CREATE TRIGGER recently_opened_trigger AFTER UPDATE OF reading_time ON books
BEGIN
   UPDATE books SET added_date = 0 WHERE _id = new._id;
   UPDATE books SET added_date = reading_time WHERE reading_time NOT NULL AND _id <> new._id;
END
Some caveats:
  • Firmware updates may break this.
  • Hack will need to be reapplied if you format memory, or delete books.db.

I guess the upside to SQLite being used is that we can actually create SQL triggers to customize the behavior a little here and there. Within some limits anyhow.
Kolenka is offline   Reply With Quote
Old 10-14-2011, 06:32 AM   #2
igobythenameofCL
Junior Member
igobythenameofCL began at the beginning.
 
Posts: 2
Karma: 10
Join Date: Oct 2011
Device: Sony PRS T1
Hi Kolenka,
I found it really annoying how it listed recently added rather than recently read/opened
Your code worked like a charm,
thanks!
igobythenameofCL is offline   Reply With Quote
Old 10-14-2011, 09:34 AM   #3
log
...
log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.
 
Posts: 226
Karma: 514506
Join Date: Sep 2011
Device: Sony PRS-T1
Nice work Kolenka, thanks for sharing!

Will definitely set this up on my books db, I also would rather have recently opened show up there.
log is offline   Reply With Quote
Old 10-14-2011, 10:48 AM   #4
Kolenka
<Insert Wit Here>
Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.
 
Kolenka's Avatar
 
Posts: 973
Karma: 1254645
Join Date: Jan 2008
Location: Puget Sound
Device: Sony T2, Kindle Paperwhite
As written, recently added still appears there too. It will take a second trigger to block it.
Kolenka is offline   Reply With Quote
Old 10-14-2011, 02:01 PM   #5
log
...
log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.
 
Posts: 226
Karma: 514506
Join Date: Sep 2011
Device: Sony PRS-T1
I understand, but my response was not clear, sorry.

I like that this will not interfere with built in operation. Clever idea using the trigger for this, got me thinking of other possibilities now, thanks again.
log is offline   Reply With Quote
Old 10-14-2011, 02:03 PM   #6
Kolenka
<Insert Wit Here>
Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.
 
Kolenka's Avatar
 
Posts: 973
Karma: 1254645
Join Date: Jan 2008
Location: Puget Sound
Device: Sony T2, Kindle Paperwhite
I'm honestly surprised the T1 doesn't occasionally scrub the DB to make sure only their triggers are present. It seems like a way to completely destabilize or break the device in some ways.

That said, perhaps they saw that this was a case of users only really being able to hang themselves, and only until they formatted memory or deleted books.db, and just thought it wasn't worth the effort to lock this down.

Kolenka is offline   Reply With Quote
Old 10-14-2011, 08:18 PM   #7
log
...
log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.log ought to be getting tired of karma fortunes by now.
 
Posts: 226
Karma: 514506
Join Date: Sep 2011
Device: Sony PRS-T1
Guess they figure users must know what they're doing if they update database so it's ok

Tried it out, works exactly as you described, very nice enhancement
log is offline   Reply With Quote
Old 10-19-2011, 08:55 AM   #8
jackie_w
Wizard
jackie_w ought to be getting tired of karma fortunes by now.jackie_w ought to be getting tired of karma fortunes by now.jackie_w ought to be getting tired of karma fortunes by now.jackie_w ought to be getting tired of karma fortunes by now.jackie_w ought to be getting tired of karma fortunes by now.jackie_w ought to be getting tired of karma fortunes by now.jackie_w ought to be getting tired of karma fortunes by now.jackie_w ought to be getting tired of karma fortunes by now.jackie_w ought to be getting tired of karma fortunes by now.jackie_w ought to be getting tired of karma fortunes by now.jackie_w ought to be getting tired of karma fortunes by now.
 
Posts: 2,831
Karma: 4193095
Join Date: Sep 2009
Location: UK
Device: Sony PRS-350, PB360, Kobo Glo/AuraHD/Aura6"/AuraH2O
Just a note to any Windows user interested in this hack...

I am using Windows XP SP3 and I was able to add the necessary trigger very easily using the free program SQLiteSpy.

I can post a more detailed step-by-step if anyone needs it.

Last edited by jackie_w; 10-19-2011 at 08:58 AM.
jackie_w is offline   Reply With Quote
Old 10-20-2011, 11:39 PM   #9
anesthezea
Zealot
anesthezea ought to be getting tired of karma fortunes by now.anesthezea ought to be getting tired of karma fortunes by now.anesthezea ought to be getting tired of karma fortunes by now.anesthezea ought to be getting tired of karma fortunes by now.anesthezea ought to be getting tired of karma fortunes by now.anesthezea ought to be getting tired of karma fortunes by now.anesthezea ought to be getting tired of karma fortunes by now.anesthezea ought to be getting tired of karma fortunes by now.anesthezea ought to be getting tired of karma fortunes by now.anesthezea ought to be getting tired of karma fortunes by now.anesthezea ought to be getting tired of karma fortunes by now.
 
anesthezea's Avatar
 
Posts: 127
Karma: 488602
Join Date: Aug 2010
Location: Alabama, USA
Device: Kindle 3G, KFHD, Kindle PW2, iPhone 5S, iPad Mini (Marvin)
Quote:
Originally Posted by jackie_w View Post
I can post a more detailed step-by-step if anyone needs it.
Yes, please?
anesthezea is offline   Reply With Quote
Old 10-21-2011, 03:39 AM   #10
Terisa de morgan
Wizard
Terisa de morgan ought to be getting tired of karma fortunes by now.Terisa de morgan ought to be getting tired of karma fortunes by now.Terisa de morgan ought to be getting tired of karma fortunes by now.Terisa de morgan ought to be getting tired of karma fortunes by now.Terisa de morgan ought to be getting tired of karma fortunes by now.Terisa de morgan ought to be getting tired of karma fortunes by now.Terisa de morgan ought to be getting tired of karma fortunes by now.Terisa de morgan ought to be getting tired of karma fortunes by now.Terisa de morgan ought to be getting tired of karma fortunes by now.Terisa de morgan ought to be getting tired of karma fortunes by now.Terisa de morgan ought to be getting tired of karma fortunes by now.
 
Terisa de morgan's Avatar
 
Posts: 2,285
Karma: 1982066
Join Date: Jun 2009
Location: Madrid, Spain
Device: Kobo Mini, iPhone, iPad, Kobo Aura, Galaxy Tab3 8
Thank you about the trigger and the program for windows
Nice trigger, BTW
Terisa de morgan is offline   Reply With Quote
Old 10-21-2011, 09:30 AM   #11
jackie_w
Wizard
jackie_w ought to be getting tired of karma fortunes by now.jackie_w ought to be getting tired of karma fortunes by now.jackie_w ought to be getting tired of karma fortunes by now.jackie_w ought to be getting tired of karma fortunes by now.jackie_w ought to be getting tired of karma fortunes by now.jackie_w ought to be getting tired of karma fortunes by now.jackie_w ought to be getting tired of karma fortunes by now.jackie_w ought to be getting tired of karma fortunes by now.jackie_w ought to be getting tired of karma fortunes by now.jackie_w ought to be getting tired of karma fortunes by now.jackie_w ought to be getting tired of karma fortunes by now.
 
Posts: 2,831
Karma: 4193095
Join Date: Sep 2009
Location: UK
Device: Sony PRS-350, PB360, Kobo Glo/AuraHD/Aura6"/AuraH2O
Detaile step-by-step for Windows

As requested, this is how I applied the hack on my Windows XP PC.
  1. Install SQLiteSpy free program.
    Note: I used version 1.8.11, which is not the current version, so I hope this step-by-step is still correct.
  2. Attach the PRST1 to the PC and copy the file
    [int memory:]\Sony_Reader\database\books.db
    to the PC. I also took a 2nd copy for backup 'just in case'.
  3. Launch SQLiteSpy and open the copied books.db file using
    File - Open Database
  4. Copy Kolenka's code, from post #1, into the code panel - see attachment
  5. Run the code, Execute - Execute SQL
  6. Check it has run correctly by expanding the left panel (see attachment)
    main - Tables - books - Triggers
    You should see 2 triggers. The one you have just added is the 2nd one, called recently_added_trigger
  7. Exit SQLiteSpy. When it asks if you want to 'Save Changes to CREATE TRIGGER ...?' You can say No as the database has already been updated.
  8. Copy the updated file, books.db back to the PRST1 overwriting the original.
  9. I don't use an SD card, but if you do you will need to update the books.db file on the SD card in the same way as for the internal memory one.
  10. Disconnect the PRST1 in the normal way.
  11. Read Kolenka's caveat in post #1
Attached Thumbnails
Click image for larger version

Name:	sqltrigger.jpg
Views:	4537
Size:	96.1 KB
ID:	78023  

Last edited by jackie_w; 10-25-2011 at 05:31 PM. Reason: typo
jackie_w is offline   Reply With Quote
Old 10-21-2011, 01:50 PM   #12
Kolenka
<Insert Wit Here>
Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.
 
Kolenka's Avatar
 
Posts: 973
Karma: 1254645
Join Date: Jan 2008
Location: Puget Sound
Device: Sony T2, Kindle Paperwhite
Just note that if you have an SD card in your T1, you will need to do this on the SD card as well.
Kolenka is offline   Reply With Quote
Old 10-22-2011, 12:50 AM   #13
anesthezea
Zealot
anesthezea ought to be getting tired of karma fortunes by now.anesthezea ought to be getting tired of karma fortunes by now.anesthezea ought to be getting tired of karma fortunes by now.anesthezea ought to be getting tired of karma fortunes by now.anesthezea ought to be getting tired of karma fortunes by now.anesthezea ought to be getting tired of karma fortunes by now.anesthezea ought to be getting tired of karma fortunes by now.anesthezea ought to be getting tired of karma fortunes by now.anesthezea ought to be getting tired of karma fortunes by now.anesthezea ought to be getting tired of karma fortunes by now.anesthezea ought to be getting tired of karma fortunes by now.
 
anesthezea's Avatar
 
Posts: 127
Karma: 488602
Join Date: Aug 2010
Location: Alabama, USA
Device: Kindle 3G, KFHD, Kindle PW2, iPhone 5S, iPad Mini (Marvin)
Quote:
Originally Posted by jackie_w View Post
As requested, this is how I applied the hack on my Windows XP PC.
Just wanted to drop a line to say thank you for the instructions and that this worked on my Windows 7 machine.

Many thanks also to Kolenka for the code!
anesthezea is offline   Reply With Quote
Old 10-24-2011, 04:47 PM   #14
Insalata
Connoisseur
Insalata knows what time it isInsalata knows what time it isInsalata knows what time it isInsalata knows what time it isInsalata knows what time it isInsalata knows what time it isInsalata knows what time it isInsalata knows what time it isInsalata knows what time it isInsalata knows what time it isInsalata knows what time it is
 
Posts: 90
Karma: 2092
Join Date: Sep 2011
Location: UK
Device: Sony PRS-T1, Kobo Aura HD
Would those instructions work (more or less) on a Mac? I'm a complete SQL novice - but I assume there must be some similar software out there?

I love my new reader (the first I've had), but it's a pain if you pop out of the book you're reading to have a quick look at a different one, then have to go through five pages of clunky navigation to dig it out again!
Insalata is offline   Reply With Quote
Old 10-24-2011, 05:01 PM   #15
Kolenka
<Insert Wit Here>
Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.Kolenka ought to be getting tired of karma fortunes by now.
 
Kolenka's Avatar
 
Posts: 973
Karma: 1254645
Join Date: Jan 2008
Location: Puget Sound
Device: Sony T2, Kindle Paperwhite
Quote:
Originally Posted by Insalata View Post
Would those instructions work (more or less) on a Mac? I'm a complete SQL novice - but I assume there must be some similar software out there?

I love my new reader (the first I've had), but it's a pain if you pop out of the book you're reading to have a quick look at a different one, then have to go through five pages of clunky navigation to dig it out again!
Yeah, use a tool like MesaSQLite on the Mac instead. It also has a tool to execute SQL statements. I do all my work on OS X, and I used MesaSQLite to create/test the trigger.
Kolenka 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
Accessing the most recently opened ebooks africalass Library Management 11 04-03-2011 04:14 AM
Sideloaded book on homescreen solution for Rooted NC mad5427 Nook Developer's Corner 17 03-14-2011 08:20 AM
I don't know what to put on my homescreen. SCION Nook Developer's Corner 3 01-16-2011 01:09 AM
Content PDF: Homescreen shows Filename instead of Title (Metadata) bear4hunter Amazon Kindle 1 08-02-2010 12:35 AM
Feature request: show recently opened e-books yegorich Calibre 1 01-18-2010 11:35 AM


All times are GMT -4. The time now is 07:07 AM.


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