Choosing a screensaver cover by adding an image into a Shelf

[cramoisi]

Hi,
Following an idea of davidfor discussed here ; I figure this out :

This is working, basis on database tests only
(where i simulated the adding, the updating, etc by sql cmd) assuming :

1. the shelf is named Screensaver (watch the capital S)
2. the images are in a root directory named screensaver OR are named screensaverXX.ext
3. the images are added into the shelf by a insert (how could be)
4. remove an item from a shelf doesn’t delete the line in ShefContent but sets the _IsDeleted value at ‘true’
5. re-adding the item updates the line with ‘false’ value.
6. the kobo is configured to display the cover of the book which is reading
7. It’s not designed to have more than one image inside the Screensaver shelf at the same time but I’m also curious to see what happen if two images have exactly the same content DateLastRead and ReadStatus...
Options I could imagine are 1. choose randomly (which could be great but from my experience, the system don’t do that) 2. take another image which has an earlier date 3. take always the same image by choosing on another thing (the row, the id, alphabetically ?).


Won’t be able to test it on a kobo device before friday. If somebody want to try... (at your own risk and after a backup of koboreader.sqlite)

create trigger add_screensaver
after insert on ShelfContent
for each row
when new.ShelfName = 'Screensaver'
and new.ContentId like '%screensaver%'
BEGIN
update content
set DateLastRead = '2032-01-01T12:00:00Z', ReadStatus = '1'
where ContentID = new.ContentId
and ContentType = '6';
END

create trigger delete_screensaver
after update of _IsDeleted
on ShelfContent
for each row
when new._IsDeleted = 'true'
and old._IsDeleted = 'false'
and old.ShelfName = 'Screensaver'
and old.ContentId like '%screensaver%'
BEGIN
update content
set DateLastRead = '2015-01-01T12:00:00Z', ReadStatus = '2'
where ContentID = old.ContentId
and ContentType = '6';
END

create trigger addagain_screensaver
after update of _IsDeleted
on ShelfContent
for each row
when new._IsDeleted = 'false'
and old._IsDeleted = 'true'
and new.ShelfName = 'Screensaver'
and new.ContentId like '%screensaver%'
BEGIN
update content
set DateLastRead = '2032-01-01T12:00:00Z', ReadStatus = '1'
where ContentID = new.ContentId
and ContentType = '6';
END

heavily used the doc available here for the relation (even if it’s a little old now)

[davidfor]

Drats, it doesn't quite work. The database gets updated OK, but the device is working on an in-memory copy of the data. That means it doesn't see the changes. If you restart the device, it rereads the database and the selected image is used as the screensaver. Connecting and disconnecting from the also does it.

I think there is also a problem with using 2032 as the date. But, it would be better to calculate a date. My suggestion for the triggers would be:

create trigger add_screensaver
after insert on ShelfContent
for each row
when new.ShelfName = 'Screensaver'
and new.ContentId like '%screensaver%'
BEGIN
update content
set DateLastRead = datetime('now', '+1 month'), ReadStatus = '1'
where ContentID = new.ContentId
and ContentType = '6';
END;

create trigger delete_screensaver
after update of _IsDeleted
on ShelfContent
for each row
when new._IsDeleted = 'true'
and old._IsDeleted = 'false'
and old.ShelfName = 'Screensaver'
and old.ContentId like '%screensaver%'
BEGIN
update content
set DateLastRead = datetime('now', '-1 month'), ReadStatus = '2'
where ContentID = old.ContentId
and ContentType = '6';
END;

create trigger addagain_screensaver
after update of _IsDeleted
on ShelfContent
for each row
when new._IsDeleted = 'false'
and old._IsDeleted = 'true'
and new.ShelfName = 'Screensaver'
and new.ContentId like '%screensaver%'
BEGIN
update content
set DateLastRead = datetime('now', '+1 month'), ReadStatus = '1'
where ContentID = new.ContentId
and ContentType = '6';
END

With that, you would be safe to not removing the image from the shelf as the calculated timestamp will always be different.

[cramoisi]

Thanks for your test !

There is no other way to force the device to reread the database without plug it in or shutdown it ? I was thinking about inserting a fake line in a thing as sensitive as content and deleting afterward.

If the update are done but not reread, what about the delete ? (I think about your trigger on analytic event) The whole process could be inverted : remove a image from Screensaver collection (and delete the line) for setting it as screensaver...


---
Random thoughts & questions : If the trigger was on update content, should the device read it?
If it’s the case, the datetime(now, -1 month) should do the trick with one trigger only.


create trigger add_screensaver
after update of DateLastRead, ReadStatus
on content
for each row
when new.ReadStatus = '2'
and new.ContentType = '6'
and new.ContentID like '%screensaver%'
BEGIN
update content
set DateLastRead = datetime('now', '+1 month'), ReadStatus = '1'
where ContentID = old.ContentID
-- = new.ContentID should work too.
and ContentType = '6';
END;


Only one trigger, but all the screensaver named and used images will stay at ReadStatus=1. The only way to remove an image, by this way, is to add (read) another one. Still, it depends how the image is processed when added the first time to the device. If there is only one insert and no update on the main content line, it’s OK. If not, it should mess with files adding on the device, hence my reluctance to use a trigger on content table. Nevertheless, all of this is dependent on the capability to reread the data...

EDIT : this one doesn’t work at all. the data in content does not even bother to update