Question about future "Notes"

[un_pogaz]

I saw that a "Note" feature associated to the value of different fields is being developed. I tested it quickly and it looks nice, thanks.

However, I have a purely curious question about the current implementation:
Why put them in a separate DB?
My understanding of the calibre DB structure is superficial, but there are really "few" new tables in notes.db, "few" enough to be simply added in the main, like the "annotations". So why?

[kovidgoyal]

Because they support full text search which will make the db file very large.

[un_pogaz]

Okay.
So why create this merged DB directly? Why not add the 'main table' to the main DB and create a 'full text search db' separately? Any particular interaction? Or is just a non-final implementation?
I'm wondering because I'm synchronizing a backup of my library with exclusion of full-text-search.db

One idea that comes to mind is that you're planning to have massive "wikipedia biography" size notes, and so yes, it could be relevant to physically put them apart.


Also, is it normal for the 'full text search' annotation tables to be in the main DB, or is this an artifact of my testing with the indev code?

[BetterRed]

The annotations_* tables in metadata.db relate to the Book annotations made in the viewer.

The changes that Kovid and Chaley are working on now appear to be notes pertinent to metadata columns such as Authors, Series, Publishers etc - i.e. an adjunct/alternative to the existing Link fields. They were briefly discussed here ==>> https://www.mobileread.com/forums/sh...46#post4309946

BR

[kovidgoyal]

Because having so many separate db files is unneccessary clutter and putting tables into separate database files has an overhead in terms of query complexity and performance. Since notes supports FTS and can contain potentially large amounts of text it cannot be in metadata.db so it must be separate. Not to mention I like to develop things incrementally and doing that
wit a separate db file is much easier. And then there are considerations of backup, currently the .calnotes folder is complete including main data and backups. If I were to move some tables into metadata.db there becomes a cross dependence.

Splitting it further into a separate fts means you then need to deal with user deleteing only the fts.db and other complications. FTS is optional for the main database not for notes.

[un_pogaz]

Okay, I get it.
Thanks.

So I imagine that when we do a search for "Plastic demons, Jose Berube" in the notes, it will return results even if the source text is "<strong>Plastic demons</strong>, Jose Berube" right? If true, Great, this will be more handy to do a search.

Spoiler:

Which makes me think the same thing could be done for the "comments" field, which only matches raw html, so you need to include '</strong>' on the query (that would be really handy, but at your discretion).

In the same logic as you presented: Can I say that the table 'annotations' and 'annotations_fts_*' inside the main DB was a "mistake"? I mean, if you did it now, you'd be storing them in a .calannotations folder rather than the current implementation? (probably too late to move them)


Oh, and idea:
Since there doesn't yet seem to be an implementation for searching notes from the GUI, I'll make the following proposal: In the search bar, use the prefix ":note:" to search inside the notes.
So :notes:authors:"search query" will search in the Authors notes, and :notes:"search query" will do in all notes.
(Colon at the start is to specified a special sub search type)

[kovidgoyal]

yes searching ignores markup.

No not really. Annotations are not large amounts of text, so putting them into the main database is harmless and they are per book so they integrate with the backup schema of OPF files very well.

You cant have search via the main bar. The main bar searches for books. Here we are searching for categories such as an author/tag. There will be a dedicated browse notes tool just as there is for annotations that will use the FTS data.

[un_pogaz]

Little bug report:
Impossible to create/edit new notes.
Probably Windows specific.

apsw.SQLError: SQLError: unknown function: unixepoch()

Spoiler:

calibre, version 6.26.0
ERREUR : Exception non gérée: <b>SQLError</b>:SQLError: unknown function: unixepoch()

calibre 6.26* embedded-python: True
Windows-10-10.0.19045-SP0 Windows ('64bit', 'WindowsPE')
('Windows', '10', '10.0.19045')
Python 3.10.1
Windows: ('10', '10.0.19045', 'SP0', 'Multiprocessor Free')
Interface language: fr
Successfully initialized third party plugins: DeACSM (0, 0, 16) && LCPL Input (0, 0, 5) && Gather KFX-ZIP (from KFX Input) (2, 5, 0) && DeDRM (10, 0, 3) && Package KFX (from KFX Input) (2, 5, 0) && ACE (1, 1, 6) && Access Aide (0, 1, 15) && Action Chains (1, 18, 12) && Comments Cleaner (1, 12, 0) && Count Pages (1, 13, 2) && DOC Input (1, 0, 1) && Diaps Editing Toolbag (0, 4, 0) && Editor Chains (0, 9, 1) && Embed Comic Metadata (1, 6, 6) && EpubMerge (2, 16, 0) && EpubSplit (3, 6, 0) && Extract ISBN (1, 6, 0) && Find Duplicates (1, 10, 8) && Generate Cover (2, 3, 3) && KFX metadata reader (from KFX Input) (2, 5, 0) && KFX Input (2, 5, 0) && KePub Input (3, 6, 6) && KePub Metadata Reader (3, 4, 3) && KindleUnpack - The Plugin (0, 83, 1) && Kobo Books (1, 9, 2) && Kobo Utilities (2, 16, 10) && Manage Series (1, 5, 1) && Manga (0, 1, 0) && Markdown Output (1, 0, 3) && Mass Search-Replace (1, 7, 4) && Modify ePub (1, 8, 1) && OPDS Reader (2, 2, 1) && Obok DeDRM (10, 0, 3) && Open With (1, 8, 1) && Perrypedia (1, 8, 1) && Quality Check (1, 13, 6) && Reformat plugin (2, 4, 0) && Search The Internet (1, 10, 1) && View Manager (1, 10, 2) && Wiki Reader (2, 2, 1) && ePub Extended Metadata {Writer} (0, 10, 1) && ePub Extended Metadata {Reader} (0, 10, 1) && ePub Extended Metadata (0, 10, 1) && noosfere DB (1, 2, 0)
Traceback (most recent call last):
File "F:\Users\un_pogaz\Projet\GitHub\calibre\src\calib re\gui2\dialogs\edit_category_notes.py", line 324, in accept
File "F:\Users\un_pogaz\Projet\GitHub\calibre\src\calib re\gui2\dialogs\edit_category_notes.py", line 284, in commit
File "F:\Users\un_pogaz\Projet\GitHub\calibre\src\calib re\db\cache.py", line 88, in call_func_with_lock
File "F:\Users\un_pogaz\Projet\GitHub\calibre\src\calib re\db\cache.py", line 705, in set_notes_for
File "F:\Users\un_pogaz\Projet\GitHub\calibre\src\calib re\db\backend.py", line 983, in set_notes_for
File "F:\Users\un_pogaz\Projet\GitHub\calibre\src\calib re\db\notes\connect.py", line 246, in set_note
File "F:\Users\un_pogaz\Projet\GitHub\calibre\src\calib re\db\backend.py", line 376, in get
apsw.SQLError: SQLError: unknown function: unixepoch()

[chaley]

Originally Posted by un_pogaz

Little bug report:
Impossible to create/edit new notes.
Probably Windows specific.

apsw.SQLError: SQLError: unknown function: unixepoch()

Spoiler:

calibre, version 6.26.0
ERREUR : Exception non gérée: <b>SQLError</b>:SQLError: unknown function: unixepoch()

calibre 6.26* embedded-python: True
Windows-10-10.0.19045-SP0 Windows ('64bit', 'WindowsPE')
('Windows', '10', '10.0.19045')
Python 3.10.1
Windows: ('10', '10.0.19045', 'SP0', 'Multiprocessor Free')
Interface language: fr
Successfully initialized third party plugins: DeACSM (0, 0, 16) && LCPL Input (0, 0, 5) && Gather KFX-ZIP (from KFX Input) (2, 5, 0) && DeDRM (10, 0, 3) && Package KFX (from KFX Input) (2, 5, 0) && ACE (1, 1, 6) && Access Aide (0, 1, 15) && Action Chains (1, 18, 12) && Comments Cleaner (1, 12, 0) && Count Pages (1, 13, 2) && DOC Input (1, 0, 1) && Diaps Editing Toolbag (0, 4, 0) && Editor Chains (0, 9, 1) && Embed Comic Metadata (1, 6, 6) && EpubMerge (2, 16, 0) && EpubSplit (3, 6, 0) && Extract ISBN (1, 6, 0) && Find Duplicates (1, 10, 8) && Generate Cover (2, 3, 3) && KFX metadata reader (from KFX Input) (2, 5, 0) && KFX Input (2, 5, 0) && KePub Input (3, 6, 6) && KePub Metadata Reader (3, 4, 3) && KindleUnpack - The Plugin (0, 83, 1) && Kobo Books (1, 9, 2) && Kobo Utilities (2, 16, 10) && Manage Series (1, 5, 1) && Manga (0, 1, 0) && Markdown Output (1, 0, 3) && Mass Search-Replace (1, 7, 4) && Modify ePub (1, 8, 1) && OPDS Reader (2, 2, 1) && Obok DeDRM (10, 0, 3) && Open With (1, 8, 1) && Perrypedia (1, 8, 1) && Quality Check (1, 13, 6) && Reformat plugin (2, 4, 0) && Search The Internet (1, 10, 1) && View Manager (1, 10, 2) && Wiki Reader (2, 2, 1) && ePub Extended Metadata {Writer} (0, 10, 1) && ePub Extended Metadata {Reader} (0, 10, 1) && ePub Extended Metadata (0, 10, 1) && noosfere DB (1, 2, 0)
Traceback (most recent call last):
File "F:\Users\un_pogaz\Projet\GitHub\calibre\src\calib re\gui2\dialogs\edit_category_notes.py", line 324, in accept
File "F:\Users\un_pogaz\Projet\GitHub\calibre\src\calib re\gui2\dialogs\edit_category_notes.py", line 284, in commit
File "F:\Users\un_pogaz\Projet\GitHub\calibre\src\calib re\db\cache.py", line 88, in call_func_with_lock
File "F:\Users\un_pogaz\Projet\GitHub\calibre\src\calib re\db\cache.py", line 705, in set_notes_for
File "F:\Users\un_pogaz\Projet\GitHub\calibre\src\calib re\db\backend.py", line 983, in set_notes_for
File "F:\Users\un_pogaz\Projet\GitHub\calibre\src\calib re\db\notes\connect.py", line 246, in set_note
File "F:\Users\un_pogaz\Projet\GitHub\calibre\src\calib re\db\backend.py", line 376, in get
apsw.SQLError: SQLError: unknown function: unixepoch()

You need a new calibre binary. Use the beta or 6.27.

EDIT: I haven't yet tried 6.27. Doing that now.
EDIT 2: 6.27 works, as Kovid said it would.

[kovidgoyal]

6.27 has new enough sqlite for unixepoch()

[un_pogaz]

In sight of some commit, just to be sure: the Category Note will be in a upcoming Calibre 7?

[kovidgoyal]

yes .