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-19-2012, 04:07 AM   #46
snarkophilus
Wannabe Connoisseur
snarkophilus ought to be getting tired of karma fortunes by now.snarkophilus ought to be getting tired of karma fortunes by now.snarkophilus ought to be getting tired of karma fortunes by now.snarkophilus ought to be getting tired of karma fortunes by now.snarkophilus ought to be getting tired of karma fortunes by now.snarkophilus ought to be getting tired of karma fortunes by now.snarkophilus ought to be getting tired of karma fortunes by now.snarkophilus ought to be getting tired of karma fortunes by now.snarkophilus ought to be getting tired of karma fortunes by now.snarkophilus ought to be getting tired of karma fortunes by now.snarkophilus ought to be getting tired of karma fortunes by now.
 
Posts: 241
Karma: 1009530
Join Date: Apr 2011
Location: Geelong, Australia
Device: Sony PRS-T1, Sony PRS-350, Palm TX
Quote:
Originally Posted by pssquirrel View Post
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.
snarkophilus is offline   Reply With Quote
Old 07-19-2012, 09:59 AM   #47
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)
How to Create "Recently Opened" Trigger on Mac using MesaSQLite

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

Name:	Create Trigger.png
Views:	155
Size:	58.6 KB
ID:	89391   Click image for larger version

Name:	Resulting Trigger.png
Views:	117
Size:	17.7 KB
ID:	89392  

Last edited by pssquirrel; 07-19-2012 at 02:09 PM. Reason: updated info, including solution
pssquirrel is offline   Reply With Quote
Old 07-19-2012, 08:23 PM   #48
J.L.C.
Junior Member
J.L.C. knows what time it isJ.L.C. knows what time it isJ.L.C. knows what time it isJ.L.C. knows what time it isJ.L.C. knows what time it isJ.L.C. knows what time it isJ.L.C. knows what time it isJ.L.C. knows what time it isJ.L.C. knows what time it isJ.L.C. knows what time it isJ.L.C. knows what time it is
 
Posts: 4
Karma: 2034
Join Date: Dec 2011
Device: Sony PRS T1
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!!
J.L.C. is offline   Reply With Quote
Old 07-20-2012, 12:04 AM   #49
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 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!
Attached Thumbnails
Click image for larger version

Name:	SQLite Manager.png
Views:	102
Size:	44.8 KB
ID:	89428   Click image for larger version

Name:	RESULTING TRIGGER.png
Views:	75
Size:	20.9 KB
ID:	89429  

Last edited by pssquirrel; 07-20-2012 at 12:57 AM. Reason: added screenshots and more details
pssquirrel is offline   Reply With Quote
Old 07-20-2012, 10:20 AM   #50
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,710
Karma: 3818505
Join Date: Sep 2009
Location: UK
Device: Sony PRS-350, PB360, Kobo Glo/AuraHD/Aura6"
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.
Attached Thumbnails
Click image for larger version

Name:	sqlitemgr.jpg
Views:	148
Size:	98.2 KB
ID:	89451  
jackie_w is online now   Reply With Quote
Old 07-20-2012, 11:27 AM   #51
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)
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!
pssquirrel is offline   Reply With Quote
Old 07-20-2012, 11:36 AM   #52
J.L.C.
Junior Member
J.L.C. knows what time it isJ.L.C. knows what time it isJ.L.C. knows what time it isJ.L.C. knows what time it isJ.L.C. knows what time it isJ.L.C. knows what time it isJ.L.C. knows what time it isJ.L.C. knows what time it isJ.L.C. knows what time it isJ.L.C. knows what time it isJ.L.C. knows what time it is
 
Posts: 4
Karma: 2034
Join Date: Dec 2011
Device: Sony PRS T1
Quote:
Originally Posted by pssquirrel View Post
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.
Attached Thumbnails
Click image for larger version

Name:	SQLite Manager.png
Views:	154
Size:	121.0 KB
ID:	89452  
J.L.C. is offline   Reply With Quote
Old 07-20-2012, 11:47 AM   #53
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 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!
pssquirrel is offline   Reply With Quote
Old 09-03-2012, 04:32 PM   #54
Xosen
Junior Member
Xosen began at the beginning.
 
Posts: 5
Karma: 10
Join Date: Aug 2012
Device: Sony PRS-T1
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!
Xosen is offline   Reply With Quote
Old 09-03-2012, 05:44 PM   #55
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 Xosen View Post
I'm having problems when I switch between books in different storages ... ¿Any solution to this?!
Does the following help?

Quote:
Originally Posted by Kolenka View Post
Just note that if you have an SD card in your T1, you will need to do this on the SD card as well.
Quote:
Originally Posted by darseno View Post
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.
Quote:
Originally Posted by Kolenka View Post
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.
pssquirrel is offline   Reply With Quote
Old 09-04-2012, 05:09 AM   #56
Xosen
Junior Member
Xosen began at the beginning.
 
Posts: 5
Karma: 10
Join Date: Aug 2012
Device: Sony PRS-T1
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):
Code:
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 is offline   Reply With Quote
Old 09-04-2012, 06:11 AM   #57
Xosen
Junior Member
Xosen began at the beginning.
 
Posts: 5
Karma: 10
Join Date: Aug 2012
Device: Sony PRS-T1
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.

Code:
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.

Last edited by Xosen; 09-04-2012 at 12:38 PM.
Xosen is offline   Reply With Quote
Old 09-05-2012, 08:31 AM   #58
adbosch
Addict
adbosch ought to be getting tired of karma fortunes by now.adbosch ought to be getting tired of karma fortunes by now.adbosch ought to be getting tired of karma fortunes by now.adbosch ought to be getting tired of karma fortunes by now.adbosch ought to be getting tired of karma fortunes by now.adbosch ought to be getting tired of karma fortunes by now.adbosch ought to be getting tired of karma fortunes by now.adbosch ought to be getting tired of karma fortunes by now.adbosch ought to be getting tired of karma fortunes by now.adbosch ought to be getting tired of karma fortunes by now.adbosch ought to be getting tired of karma fortunes by now.
 
Posts: 271
Karma: 511516
Join Date: Jun 2004
Device: Kobo Aura; Galaxy S4; Lenovo Yoga Tablet 8
Hack still works on a T2! Great.
adbosch is offline   Reply With Quote
Old 09-06-2012, 03:54 PM   #59
redneck eyeball
Member
redneck eyeball began at the beginning.
 
Posts: 12
Karma: 10
Join Date: Aug 2012
Device: PRS-T2
Just used it on my T2 !
I don't understand why Sony doesn't do this by default.
redneck eyeball is offline   Reply With Quote
Old 10-31-2012, 09:14 AM   #60
mediax
Readaholic
mediax ought to be getting tired of karma fortunes by now.mediax ought to be getting tired of karma fortunes by now.mediax ought to be getting tired of karma fortunes by now.mediax ought to be getting tired of karma fortunes by now.mediax ought to be getting tired of karma fortunes by now.mediax ought to be getting tired of karma fortunes by now.mediax ought to be getting tired of karma fortunes by now.mediax ought to be getting tired of karma fortunes by now.mediax ought to be getting tired of karma fortunes by now.mediax ought to be getting tired of karma fortunes by now.mediax ought to be getting tired of karma fortunes by now.
 
mediax's Avatar
 
Posts: 249
Karma: 1058454
Join Date: Jul 2009
Location: Swindon, UK
Device: Sony PRS-T2 (previously 505 and 650)
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.
mediax 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 03:45 PM.


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