[GUI Plugin] Kobo Utilities

[davidfor]

Originally Posted by nicolas161

, that helps a lot. If I understand correctly, you have already worked with database triggers, haven't you? If so, any advice on how one would implement something like this?

Advice? Take a backup of the database before you start.

I have written the trigger like this in the past. I'll have a look through some notes as I might have kept it. It has been discussed here, so a sample might have been posted. Other than that, the table you need to look at is called "Activity".

[Elaxon]

I was restoring read states and noticed that some books have the percentage but not the location but just at the beginning of the book? I noticed it says on the first page restore states for epubs. Does this mean kepub will not function with it and will not bring you to the same location you left of in the book?

[davidfor]

Originally Posted by nicolas161

, that helps a lot. If I understand correctly, you have already worked with database triggers, haven't you? If so, any advice on how one would implement something like this?

This is an example I have for a trigger on the Activity table. This should keep the stats tile as the first one. As long as it gets updated by the system at least once a month, it will stay.

CREATE TRIGGER Activity_KeepTileFirst_UPDATE
AFTER UPDATE ON Activity
FOR EACH ROW
WHEN ( new.Type = 'GlobalStats')
BEGIN
UPDATE Activity
SET Date    = datetime(Date,'+1 month')
WHERE rowid = new.rowid;
END

It has been a while since I tried this. It should work, but I have vague memory that there is a problem with it.

[davidfor]

Originally Posted by Elaxon

I was restoring read states and noticed that some books have the percentage but not the location but just at the beginning of the book? I noticed it says on the first page restore states for epubs. Does this mean kepub will not function with it and will not bring you to the same location you left of in the book?

Where are you seeing that it goes to the beginning of the book? Are you looking at the stored location id, or when you open the book after restoring the reading status?

It should work for sideloaded epubs and kepubs. I have to admit I don't use the restore very often, but it worked the last time I did try. The last time I did a big lot was just before Christmas as I was changing which of my ereaders I was going to use daily. That was for a about 100 books and they all seemed to be OK.

For purchased kepubs, it should work, but you might never see it. When you open a purchased kepub, it attempts to retrieve the current status from the server. If there is an update, it will prompt you to use this. And during a sync, it will quietly replace the status. I don't remember if I ever tried this.

[ItalianUruguayan]

Originally Posted by davidfor

This is an example I have for a trigger on the Activity table. This should keep the stats tile as the first one. As long as it gets updated by the system at least once a month, it will stay.

CREATE TRIGGER Activity_KeepTileFirst_UPDATE
AFTER UPDATE ON Activity
FOR EACH ROW
WHEN ( new.Type = 'GlobalStats')
BEGIN
UPDATE Activity
SET Date    = datetime(Date,'+1 month')
WHERE rowid = new.rowid;
END

It has been a while since I tried this. It should work, but I have vague memory that there is a problem with it.

Thanks a lot for this. If I wanted to keep the browser tile as the first one, how would I do it?

I have tried this, but it doesn't work:

CREATE TRIGGER Activity_KeepTileFirst_UPDATE
AFTER UPDATE ON Activity
FOR EACH ROW
WHEN ( new.Type = 'browser')
BEGIN
UPDATE Activity
SET Date    = datetime(Date,'+1 month')
WHERE rowid = new.rowid;
END

[davidfor]

Originally Posted by ItalianUruguayan

Thanks a lot for this. If I wanted to keep the browser tile as the first one, how would I do it?

I have tried this, but it doesn't work:

CREATE TRIGGER Activity_KeepTileFirst_UPDATE
AFTER UPDATE ON Activity
FOR EACH ROW
WHEN ( new.Type = 'browser')
BEGIN
UPDATE Activity
SET Date    = datetime(Date,'+1 month')
WHERE rowid = new.rowid;
END

You need to look at the Activity table and find the entry for the browser. The value in the "Type" column should be obvious. In this case, I think it is "Browser". And the case is important.

[Cootey]

It's a great tool. Thanks for writing it, davidfor.

I was experimenting tonight. I first checked the database. I also backed it up. I also side loaded a book through Calibre. Then I ran Order Series Shelves. I fetched the shelves and removed one that I wasn't using. Then I ejected my KA1 LE to see the changes. I immediately noticed that dozens of books had been removed from their collections. They're still on the device, but no longer associated with the collections I chose. My iPad collections remained unchanged. At this point, the KA1 and the iPad Kobo app are not in sync. Also, the shelf I removed came back on my KA1 and iPad. This last one was likely because Calibre forced it back on, so I've removed the shelf from Calibre. But do you know of an easy fix to get the two devices to sync their collections again?

One way is to log out of my KA1 and log back in. This will reinitialize the database which seems to be corrupt now. What do you think?

[davidfor]

Originally Posted by Cootey

It's a great tool. Thanks for writing it, davidfor.

I was experimenting tonight. I first checked the database. I also backed it up. I also side loaded a book through Calibre. Then I ran Order Series Shelves. I fetched the shelves and removed one that I wasn't using. Then I ejected my KA1 LE to see the changes. I immediately noticed that dozens of books had been removed from their collections. They're still on the device, but no longer associated with the collections I chose. My iPad collections remained unchanged. At this point, the KA1 and the iPad Kobo app are not in sync. Also, the shelf I removed came back on my KA1 and iPad. This last one was likely because Calibre forced it back on, so I've removed the shelf from Calibre. But do you know of an easy fix to get the two devices to sync their collections again?

Using the order shelves function shouldn't affect the books on a collection. It should just change a timestamp in the database.

For books to disappear from a collection, one of the following has to happen:

Manually remove the book using the device.
Change to collections column definition in the calibre driver.
Changes in the values in the collections columns.
Changes made on other devices/apps logged into the same account and synced.

Usually, it's the third option that strikes, but, you need to check.

Having multiple devices and apps can cause a problem. Plug a device in and let calibre do the collections. Sync via the server to the other device and plug that in. Calibre will do the collections again and might delete some if that device doesn't have the same books. Having the app around just adds an extra place for collections to be created and maybe conflict with what is happening elsewhere.

One way is to log out of my KA1 and log back in. This will reinitialize the database which seems to be corrupt now. What do you think?

If the database on the device is corrupt, then you need to do something like this to fix it. Otherwise, syncing the devices and apps should bring the collections in to line.

[Cootey]

Originally Posted by davidfor

If the database on the device is corrupt, then you need to do something like this to fix it. Otherwise, syncing the devices and apps should bring the collections in to line.

Thanks for the detailed reply. I have no idea why the actions above affected my collections, nor which one was the culprit. They shouldn’t have. Since syncing wasn’t working (as I stated, the device and app were out of sync) I ended up logging out and setting the KA1 as a new device.

Completely unrelated to your tool, but it’s interesting that the sideloaded books remain on a reset. The new setup has no idea where to put them anymore, but at least I don’t have to copy them over again.

I didn’t realize your tool made backups until last night. If I had restored the backup manually, would that have fixed my corrupt collections database?

[davidfor]

Originally Posted by Cootey

Thanks for the detailed reply. I have no idea why the actions above affected my collections, nor which one was the culprit. They shouldn’t have. Since syncing wasn’t working (as I stated, the device and app were out of sync) I ended up logging out and setting the KA1 as a new device.

It sounds like the database was broken enough that this would have been the only way to fix it.

Completely unrelated to your tool, but it’s interesting that the sideloaded books remain on a reset. The new setup has no idea where to put them anymore, but at least I don’t have to copy them over again.

Signing out of the account only touches the Kobo stuff. It removes all the downloaded books, the covers and resets the database. The sideloaded books aren't touched and are reloaded once you have signed in again. The collections names are synced from the server, and the downloaded books will be in collections. But, the collections status for sideloaded books are not synced, so they don't get put back into the collections.

I didn’t realize your tool made backups until last night. If I had restored the backup manually, would that have fixed my corrupt collections database?

It should be OK. If you have a recent valid backup, restoring it should fix it.

[Cootey]

Originally Posted by davidfor

It sounds like the database was broken enough that this would have been the only way to fix it.

Signing out of the account only touches the Kobo stuff. It removes all the downloaded books, the covers and resets the database. The sideloaded books aren't touched and are reloaded once you have signed in again. The collections names are synced from the server, and the downloaded books will be in collections. But, the collections status for sideloaded books are not synced, so they don't get put back into the collections.


It should be OK. If you have a recent valid backup, restoring it should fix it.

As an update, I don't think any of this problem was caused by your plugin as I originally feared. Books disappeared from collections again yesterday, and my KA1 LE wasn't anywhere near Calibre. I've manually fixed all the collections now, then added some new books, so things seem OK, but I still don't understand why or how it happened, so I can't prevent it from happening again.

[PeterT]

Are you managing collections on both the Kobo and in calibre?

[davidfor]

@Cootey: The question that PeterT asks is important as each time you connect, calibre will do the collection management. Also, are the books that are disappearing from collections purchased kepubs or sideloaded books?

[Cootey]

Originally Posted by PeterT

Are you managing collections on both the Kobo and in calibre?

No. I haven't had time to dig into calibre. It's got a steep learning curve, and I'm too busy. Just managing collections on my devices.

[davidfor]

Originally Posted by Cootey

No. I haven't had time to dig into calibre. It's got a steep learning curve, and I'm too busy. Just managing collections on my devices.

Then go into the driver configuration and turn off the collection management options. Do it either from the devices button when the device is connected or the Kobo Utilities button to make sure you get the correct driver.