annotation_count() displaying value for deleted annotations

[ownedbycats]

Calibre 5.5.0 (x64)

I have this set in a composite column:

program:
 if annotation_count() >0 then 'True'
 fi

It's returning "true" for a few books that have deleted bookmarks/annotations. I'm unsure whether I deleted those annotations in ebook-viewer or the annotation browser.

[ownedbycats (ownedbycats)]

I opened metadata.db in SQLite Spy and looked at the Annotations table. The removed annotations are still present in there (confirmed with book ID), although the annot_data begins with "removed":true.

[ownedbycats (ownedbycats)]

I'm unsure of whether deleted annotations need to remain in the database (I didn't attempt to test it), but perhaps a solution would be to remove the phantom annotations upon library cleanup.

[ownedbycats (ownedbycats)]

sorry, I mean "Check Library," when the vacuuming is done.

[Kovid Goyal (kovid)]

Yes, removed annotations need to remain. Without that syncing to
different places would not work. If you remove an annotation from
the calibre local viewer then that removal has to be synced to the
browser viewer and vice versa. Removed annotations is the mechanism this
is done by.

 status wontfix

[ownedbycats (ownedbycats)]

That makes annotation_count() a bit less useful though. Hm.

[Charles Haley (cbhaley)]

@Kovid: do you object to me changing annotation_count_for_book to:
        count = 0
        for (data,) in self.execute('SELECT annot_data FROM annotations WHERE book=?', (book_id,)):
            if '"removed": true' not in data:
                count += 1
        return count

I could unpack the json and check that but it seems that it takes time for no good reason.

[Kovid Goyal (kovid)]

@charles: There is JSON query sqlite extension, that IIRC is included in
calibre, might be faster to use that. Avoids a trip to python land.

[Charles Haley (cbhaley)]

Thanks. That extension is cool.

[Kovid Goyal (kovid)]

Fixed in branch master. The fix will be in the next release. calibre is usually released every alternate Friday.

 status fixreleased