PRS-T1 Recently Opened on Homescreen Hack for T1

[snarkophilus]

Originally Posted by pssquirrel

Thanks Snarkophilus (and jackie_w, who has been helping me via PM). Unfortunately, the semi-colon and / didn't help.

But after some experimenting, I discovered that if I change the pull-down menu from "UPDATE OF" to simply "UPDATE" (see attached screenshot) the trigger will save without error. However, the resulting trigger code is different!!! than Kolenka's original code. Here's what I end up with:

CREATE TRIGGER recently_opened_trigger AFTER UPDATE 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

With not a little trepidation, I loaded this new db on my T1 and it appears to work (really?!), but since I know nothing about SQL, I'm worried my altered code is going to back-fire on me.

What think? Have I done something foolish or stupid?

I'm worried that the above trigger will update the added_date every time a record is changed, instead of just when the reading_time value of a record is changed. The problem is that I'm not sure when else a record is updated.

At worst, you might find that some books mysteriously jump to the top of your recently opened list. At best, you won't notice a thing . Either way, I don't think that trigger will cause any damage.

Cheers,
Simon.

[pssquirrel]

@Snarkophilus: I think you're right that the database is being updated a lot. I'm getting the spinning arrows more than usual, plus for some reason the alphabetization when sorting by filename has gone wonky (it seems to be using "date added" rather than "alphabetical" for some, but not all, books).

I experimented some more -- this is like a puzzle where you know you have all the pieces but you're not sure where to put them. But I think I finally figured out which snippets of code go in which MesaSQLite box. Screenshots attached. So far everything seems to be working properly. Yippee!

Thanks Simon and jackie_w for your help -- and of course, thank you Kolenka for the nice piece of code!

[J.L.C.]

I ran the code in the books.db from both the reader and my SD card using the 'SQLite Manager' add-on for Firefox, and everything worked perfectly

Thanks so much for the code and the great step-by-step!!

[pssquirrel]

I feel like a complete idiot. After successfully figuring out how to create the trigger using MesaSQLite, I decided to try it using SQLite Manager (which I wasn't aware of before, and after taking a look, I rather like it). Should be simple, right? Well, I've tried for over an hour, and every time I try, I get an error -- most commonly "near UPDATE: syntax error"

J.L.C., would it be possible for you to upload a screenshot of what you did?

The closest I get without an error is attached. When I remove the quote marks from the WHEN clause, I get an error message. But the resulting trigger with quote marks isn't exactly the same as Kolenka's original code, and I don't know enough about this stuff to know if it makes a difference. Help!

[jackie_w]

in the absence of JLC, re: the SQLite Manager Firefox add-on, you could try doing it this way:

1. Open books.db, click on the tab 'Execute SQL',
2. Copy/paste Kolenka's code into the 'Enter SQL' panel underneath
3. press the 'Run SQL' button. You should see the new trigger name appear in the left-hand panel after the existing 5 cleanup triggers

The attached image is before the trigger is created, not after.

[pssquirrel]

OMG - thank you so much, jackie_w!!!

That was sooooooooooooooooooooo much easier than messing with the "create trigger" interface.

You are the very, very best! Mucho karma to you!

[J.L.C.]

Originally Posted by pssquirrel

I feel like a complete idiot. After successfully figuring out how to create the trigger using MesaSQLite, I decided to try it using SQLite Manager (which I wasn't aware of before, and after taking a look, I rather like it). Should be simple, right? Well, I've tried for over an hour, and every time I try, I get an error -- most commonly "near UPDATE: syntax error"

J.L.C., would it be possible for you to upload a screenshot of what you did?

The closest I get without an error is attached. When I remove the quote marks from the WHEN clause, I get an error message. But the resulting trigger with quote marks isn't exactly the same as Kolenka's original code, and I don't know enough about this stuff to know if it makes a difference. Help!

Sorry for the late post, it looks liek jackie_w came to the rescue though!

Attached is my screenshot.

I am on Ubuntu 11.10 64-bit.

I opened the books.db using the 'All files' option from the open file dialogue. Then just copied the code as written from the OP into the 'Enter SQL' field on the 'Execute SQL' tab. Then I just copied the books.db files back over to the reader and over-wrote the existing ones.

[pssquirrel]

Thanks JLC! Karma to you, too!

I have to laugh because both you and jackie_w seem to have gone immediately to the Execute SQL tab, where this task was easy as can be, while I spent hours and hours messing with the "Create Trigger" tool, which, from its name, would seem to be the best way to create a trigger -- not!

In any event, I am so very pleased to have learned a few things about SQLite databases and creating triggers. Happy Day!

[Xosen]

Hi!

I'm having problems when I switch between books in different storages. After some tests, it seems to me that the trigger is being launched when I stop reading a book and I open the next. If the book is in the same storage, there is no problem, and the update is done. However, if the book is in the SD card, the trigger can't update the previous book since it's in a different database.

¿Any solution to this?

Cheers!

[pssquirrel]

Originally Posted by Xosen

I'm having problems when I switch between books in different storages ... ¿Any solution to this?!

Does the following help?

Originally Posted by Kolenka

Just note that if you have an SD card in your T1, you will need to do this on the SD card as well.

Originally Posted by darseno

This hack behaves weird when you switch between a book stored in internal memory and another stored on the sd card. The last book you were reading only shows up if the current book is from the same memory source as the last.

Originally Posted by Kolenka

Hmm, I can see that. The trigger has an extra query to make sure that your current read doesn't also appear in the "Recently Added" list, since it looks goofy.

But the behavior you see is the side effect of that extra query. You can make that trade off by removing the query that sets 'added_date = 0'. It will cause the current read to show up twice on the homescreen, but you won't have the book disappearing like that.

[Xosen]

Hi pssquirrel,

Thanks for the tips. Of course I had already tried all those options before my previous post. This is the trigger I currently have in both databases (internal and SD):

CREATE TRIGGER recently_opened_trigger AFTER UPDATE OF reading_time ON books
BEGIN
   UPDATE books SET added_date = reading_time WHERE reading_time NOT NULL AND _id <> new._id;
END;

I have used SQLiteSpy to check it and it's there. Also, when switching between books of the same storage, the update is done correctly, so I know it is launching. What surprises me is that it seems the update is done when opening the next book, or maybe the home display is not updated until you change from one book to another.

[Xosen]

Hi!

Ok, it seems I have solved the problem. For whatever reason, the original trigger simply does not work for me, and I don't really understand how it works for everyone else :P

Here is the trigger with some tweaks so that it will work with books in both storages. It has the problem of seeing the current book twice, but it's an improvement. I'll try to solve that later.

CREATE TRIGGER recently_opened_trigger AFTER UPDATE OF reading_time ON books
BEGIN
   UPDATE books SET added_date = new.reading_time WHERE new.reading_time NOT NULL AND _id = old._id;
END

UPDATE: After some thought, I don't think it's possible to prevent the current book from showing twice in the home, because of the impossibility of updating both databases when the next book is read.

[adbosch]

Hack still works on a T2! Great.

[redneck eyeball]

Just used it on my T2 !
I don't understand why Sony doesn't do this by default.

[mediax]

I applied this to my T2 and whilst it worked as advertised, my device took a *really* long time to restart afterwards (I have a large number of books on SD card, so that may well be the cause). More problematically, the change also lost my collections and notes (bookmarks).

I imagine that I could have got round this by reloading my library from Calibre, but given the number of books involved I decided to put that on my "to be done sometime" pile.

The good news is that copying my backup copies (paranoia can be good!) of the books.db files back onto the device and sd card worked perfectly, so I've lost nothing.