[GUI Plugin] Multi-Column Search

[DaltonST]

Originally Posted by Angelo.Mascaro

I searched the table _mcs_authors_by_book with DB Browser for SQLite with this query:
SELECT authorname, count(book) AS totalbook
FROM _mcs_authors_by_book
GROUP BY authorname
ORDER BY totalbook DESC
because I wanted to know the authors with more books.
I discovered that this table does not reflect the true content of the library and all the deleted/moved books are still considered. The field book in this table points to id of books that are no longer in the table books.
Would it be possible to fix this modifying the TRIGGER books_delete_trg in one of the next versions? Or maybe there is a way to realign the table to the library?

If you bother to read the MCS icon menu's tooltips, you will discover that the MCS search acceleration tables are automatically maintained by MCS. In other words, you have to actually use MCS to have MCS keep them refreshed and up-to-date.




DaltonST

[Tanjamuse]

Originally Posted by DaltonST

[1] The unique similarity group ID custom column is tag-like, so there only needs to be one (1) of it. To compare multiple columns for each book, simply execute the query for each column sequentially. The image shows that many queries for different comparison value columns have already been executed, with the many unique similarity group IDs for each book, if any, so indicated.


[2] The Value Comparison Percentage specifies how similar a comparison value has to be for one book to another book for a specific comparison value from the specified comparison value column. 100% means "identical".


Attached is an example that includes the Debug Log.

DaltonST

Would it be possible to add the percentage from the debug-log to Calibre? Or base the different tag-names on the percentages?

If it's a 100% match: Title_100_1 or so?

[kcarscadden]

@DaltonST, could you please review thread searching for books modified in the main Calibre forum.

As you see, I would like to do a search on a column that appears to only have data in the memory resident database that is used for search, sorting etc and by plugins, not in metadata.db So far, I have not been able to get MCS to search this column. Could you take a look and see if you have any suggestions?

[DaltonST]

Originally Posted by kcarscadden

@DaltonST, could you please review thread searching for books modified in the main Calibre forum.

As you see, I would like to do a search on a column that appears to only have data in the memory resident database that is used for search, sorting etc and by plugins, not in metadata.db So far, I have not been able to get MCS to search this column. Could you take a look and see if you have any suggestions?

MCS searches metadata.db standard and custom column tables using SQL. Composite columns are virtual and do not get stored in metadata.db in their assigned table(s). SQL finds nothing for them. This is not a bug. Virtual means virtual.

[kcarscadden]

@DaltonST, OK, thanks. I expected it searched metadata.db, but still hoped . ..

[DaltonST]

Version 1.0.80 - 10 April 2020 Ability to search table books_plugin_data using new Raw SQL Query user function keyword "PARSEJSON".

See the attached image.

I have tested this in both Python 2 and Python 3.



DaltonST


BTW: the plugin that updates table books_plugin_data, SaveCompositeCustomColumns, will not compile as of this writing in Python 3 Calibre 4.99.4.

[kcarscadden]

@DalsonST, after loading the new version of MCS 1.0.80, attempting to use PARSEJSON gives error "Raw SQL Query Fatal Error: SQLError: no such function parsejson'

Do I need to do something additional to get parsejson?

[DaltonST]

Originally Posted by kcarscadden

@DalsonST, after loading the new version of MCS 1.0.80, attempting to use PARSEJSON gives error "Raw SQL Query Fatal Error: SQLError: no such function parsejson'

Do I need to do something additional to get parsejson?

Spell it as instructed: PARSEJSON not parsejson.


Added: I will add automatic correction of PARSEJSON capitalization errors in the next version of MCS.




DaltonST

[kcarscadden]

@DaltonST, Oops, thanks. Of course, python is case sensitive, even though SQL is not.

[GruberMa]

Hi DaltonST,
Hi everyone,

Since some time, indexing new/unindexed books in TXT formats on my system results in the Calibre error

force_refresh_of_cache() takes exactly 2 arguments (1 given)

Details:

calibre, version 4.16.0
FEHLER: Unbehandelter Ausnahmefehler: <b>TypeError</b>:force_refresh_of_cache() takes exactly 2 arguments (1 given)

calibre 4.16 Portable embedded-python: True is64bit: False
Windows-10-10.0.19041-SP0 Windows ('32bit', 'WindowsPE')
32bit process running on 64bit windows
('Windows', '10', '10.0.19041')
Python 2.7.16+
Windows: ('10', '10.0.19041', 'SP0', u'Multiprocessor Free')
Interface language: de
Successfully initialized third party plugins: Gather KFX-ZIP (from KFX Input) (1, 31, 0) && Package KFX (from KFX Input) (1, 31, 0) && Modify ePub (1, 4, 1) && KindleUnpack - The Plugin (0, 82, 1) && KFX metadata reader (from KFX Input) (1, 31, 0) && KFX Input (1, 31, 0) && Kindle hi-res covers (0, 4, 0) && Set KFX metadata (from KFX Output) (1, 41, 1) && KFX Output (1, 41, 1) && MultiColumnSearch (1, 0, 81)
Traceback (most recent call last):
  File "site-packages\calibre\gui2\__init__.py", line 484, in dispatch
  File "calibre_plugins.multi_column_search.ui", line 6384, in mcs_finish_with_refresh
TypeError: force_refresh_of_cache() takes exactly 2 arguments (1 given)

The job log of MCS shows the following error message

SQLError: unknown database _mcs_word_book_index

All library and database checks within Calibre are successful, I do not recognize any other errors.

Removing the search accelerators, uninstalling MCS and installing it again did not help.

When searching for the error message, I found https://www.mobileread.com/forums/sh...d.php?t=246486 - as this thread was created and answered by DaltonST himself, it might not be of help in this case.


Does anyone have an idea, how to get rid of this error? If tests or logs are needed, I will be glad to provide them.

Cheers,

GruberMa

[DaltonST]

You need to post your entire Calibre MCS debug log. Run Calibre in debug mode, then recreate your error.

Also, post your Job Log of the indexing job that is causing this error.

This error is being caused by a callback from the indexing Job to MCS.

You said "Removing the search accelerators" did not work. That is only for permanently uninstalling MCS. To reinstall all search accelerators, reinstall MCS and then execute the MCS dialog to automatically reinstall them.


Added: The indexing job works perfectly for me. See the attached job log.


Where is your metadata.db? In the cloud? On a LAN?






DaltonST

[GruberMa]

Thanks for your fast answer!

My database is located on a hard drive folder, synchronized with OneDrive. The error also occurs, if the OneDrive process is not running or if I move the whole folder to USB and open Calibre Portable on another computer.

The debug output is as follows:

calibre Fehlerdiagnoseprotokoll
calibre 4.16 Portable embedded-python: True is64bit: False
Windows-10-10.0.19041-SP0 Windows ('32bit', 'WindowsPE')
32bit process running on 64bit windows
('Windows', '10', '10.0.19041')
Python 2.7.16+
Windows: ('10', '10.0.19041', 'SP0', u'Multiprocessor Free')
Interface language: de
Successfully initialized third party plugins: Gather KFX-ZIP (from KFX Input) (1, 31, 0) && Package KFX (from KFX Input) (1, 31, 0) && MultiColumnSearch (1, 0, 81) && Set KFX metadata (from KFX Output) (1, 41, 1) && KFX Output (1, 41, 1) && Kindle hi-res covers (0, 4, 0) && KindleUnpack - The Plugin (0, 82, 1) && KFX metadata reader (from KFX Input) (1, 31, 0) && KFX Input (1, 31, 0) && Modify ePub (1, 4, 1)
calibre 4.16 Portable embedded-python: True is64bit: False
Windows-10-10.0.19041-SP0 Windows ('32bit', 'WindowsPE')
32bit process running on 64bit windows
('Windows', '10', '10.0.19041')
Python 2.7.16+
Windows: ('10', '10.0.19041', 'SP0', u'Multiprocessor Free')
Interface language: de
Successfully initialized third party plugins: Gather KFX-ZIP (from KFX Input) (1, 31, 0) && Package KFX (from KFX Input) (1, 31, 0) && MultiColumnSearch (1, 0, 81) && Set KFX metadata (from KFX Output) (1, 41, 1) && KFX Output (1, 41, 1) && Kindle hi-res covers (0, 4, 0) && KindleUnpack - The Plugin (0, 82, 1) && KFX metadata reader (from KFX Input) (1, 31, 0) && KFX Input (1, 31, 0) && Modify ePub (1, 4, 1)
Turning off automatic hidpi scaling
devicePixelRatio: 1.0
logicalDpi: 144.0 x 144.0
physicalDpi: 46.4457142857 x 46.4949152542
Using calibre Qt style: False
[0.00] Starting up...
[0.00] Showing splash screen...
[0.49] splash screen shown
[0.49] Initializing db...
[0.53] db initialized
[0.53] Constructing main UI...
[0] un-optimized number of undesirable characters accumulated:  757
[1] optimized number of undesirable characters accumulated:  597
MCS has finished initialization...
[2.62] main UI initialized...
[2.62] Hiding splash screen
[19.20] splash screen hidden
[19.22] Started up in 19.22 seconds with 722 books
mcs_build_index:  D:\OneDrive\Buecher\Calibre Library
starting vacuum
calibre 4.16 Portable embedded-python: True is64bit: False
Windows-10-10.0.19041-SP0 Windows ('32bit', 'WindowsPE')
32bit process running on 64bit windows
('Windows', '10', '10.0.19041')
Python 2.7.16+
Windows: ('10', '10.0.19041', 'SP0', u'Multiprocessor Free')
Interface language: de
Successfully initialized third party plugins: Gather KFX-ZIP (from KFX Input) (1, 31, 0) && Package KFX (from KFX Input) (1, 31, 0) && MultiColumnSearch (1, 0, 81) && Set KFX metadata (from KFX Output) (1, 41, 1) && KFX Output (1, 41, 1) && Kindle hi-res covers (0, 4, 0) && KindleUnpack - The Plugin (0, 82, 1) && KFX metadata reader (from KFX Input) (1, 31, 0) && KFX Input (1, 31, 0) && Modify ePub (1, 4, 1)
Traceback (most recent call last):
  File "site-packages\calibre\gui2\__init__.py", line 484, in dispatch
  File "calibre_plugins.multi_column_search.ui", line 6384, in mcs_finish_with_refresh
TypeError: force_refresh_of_cache() takes exactly 2 arguments (1 given)

Job: 0 MultiColumnSearch: Build Index finished
Starting job: MultiColumnSearch: Build Index 
	  
	D:/OneDrive/Buecher/Calibre Library/metadata.db 
	Thu May 21 18:42:25 2020 
	Python: Windows   CPython   2.7.16+ 
	SQLite Version: 3031001    [APSW] 
	PRAGMA main.busy_timeout = 15000; 
	  
	Beginning 'Index Words by Book'  
	═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════ 
	  
	Indexing Action chosen: Index All Unindexed Books [TXT Only] 
	  
	  
	Number of books available to be indexed if needed and possible:    722 
	Number of unindexed books that need to be indexed if possible: 5 
	  
	Word lengths in this min/max range will be indexed:  3 to 50 
	  
	  
	  
	  
	  
	Custom Column #mcs_was_indexed has been synchronized with the word-book index for non-MCS (i.e., user) index deletions or insertions, if any. 
	  
	  
	  
	Total number of books indexed: 0 
	  
	Total number of words indexed: 0 
	  
	  
	Deleted Books or Invalid Books added via a CSV file, if any, have been removed from the Index. 
	  
	  
	SQLError: unknown database _mcs_word_book_index 
	You should now defragment/vacuum this library's metadata.db by invoking Calibre menu path: Library > Library Maintenance > Check Library. 
	  
	  
	MCS Word by Book Indexing is Complete.

[DaltonST]

Just found a regression error caused by changes for Python 3 used by Calibre 4.99.5. I will upload a new version of MCS after I have fully retested it in Calibre 4.99.5 and 4.16.


DaltonST

[GruberMa]

Originally Posted by DaltonST

Just found a regression error caused by changes for Python 3 used by Calibre 4.99.5. I will upload a new version of MCS after I have fully retested it in Calibre 4.99.5 and 4.16.


DaltonST

Thank you very much!

[DaltonST]

Version 1.0.82 - 21 May 2020 Python 3 regression errors fixed.


As a reminder, the post immediately after the Original Post has additional documentation and overview images that Mobileread.com does not allow in the OP due to the "18 attachments maximum" rule.



DaltonST