MobileRead Forums - View Single Post - [GUI Plugin] Multi-Column Search

[GUI Plugin] Multi-Column Search

Summary:

Advanced Search Tools: Inter-Book; Intra-Book; Cross-Library; Full-Text; Word-by-Book Index; Native SQL Queries; Cross-Library Duplicate Books Search; Similarity Queries; and more.

Search Across Multiple Columns Interactively, Collectively and Simultaneously. Designed to provide capabilities not found in standard Calibre, such as comparing multiple columns directly, plus searching across Libraries. Performs both 'Intra-Book' and 'Inter-Book' searches. Performs additional specialized searches. Considers the text of TXT formats or the Calibre FTS indexed text as a 'virtual column', so performs 'full-text' searches of that text. Performs 'word-book index' searches. Special "Search Comments" results viewer. Cross-Library Duplicate Books Search Using Custom Columns and/or Standard Columns. Metadata Variations. Duplicate values for any Custom Column. Similarity Queries for any textual Standard and Custom Columns.

Definitions, Usage, Examples, & Scope:

Spoiler:

Duplicate Custom Column Values :

Spoiler:

Full Text Search & Filename Search Queries Comments:

Spoiler:

Metadata Variation Queries:

Spoiler:

Book Awards Table:

Spoiler:

If you import the attached '_mcs_book_awards' table into your metadata.db, you will be able to use the SQL Query Tab of MCS to identify matching books. You then can then, for example, add a Tag to those books with the name of the Book Award.

I recommend using the Firefox SQL Manager to import the SQL file that is attached, which will also create the table.

There is also a CSV file if you prefer to use that, although you will have to manually create the table with this SQL:

Quote:

CREATE TABLE "_mcs_book_awards" (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , author TEXT NOT NULL , title TEXT NOT NULL , award TEXT NOT NULL, UNIQUE(author,title,award) )

Example SQL to use within the SQL Query Tab of MCS:

Quote:

SELECT book FROM _mcs_authors_by_book WHERE authorname IN (SELECT author FROM _mcs_book_awards) AND book IN (SELECT id FROM books WHERE id = book AND title IN (SELECT title FROM _mcs_book_awards WHERE award LIKE '%%' ))

The '%%' will select all books in your library matching the table. To narrow the search to, for example, the Hugo Award, simply change the '%%' to '%hugo%' in order to select all books that have been awarded the Hugo Award. Then, you can add a Tag of "Hugo Award" if you wish.

Obviously, your Author Names and Book Titles have to exactly match the table, which was populated from Wikipedia, GoodReads, and other sources. For that reason, you should probably use this SQL that is a little slower than the above, although optimized for speed:

Quote:

SELECT book FROM _mcs_authors_by_book,_mcs_book_awards WHERE award LIKE '%hugo%' AND substr(authorname,1,3) = substr(author,1,3) AND SIMILARTO(authorname,author) > 0.80 AND _mcs_authors_by_book.book IN ( SELECT id FROM books WHERE books.id = _mcs_authors_by_book.book AND SIMILARTO(books.title,_mcs_book_awards.title) > 0.80)

The "SIMILARTO" function used above in "SIMILARTO(authorname,author) > 0.80" is custom to MCS, and you will not find it in the SQLite documentation. It only works for textual columns, obviously. Also, table _mcs_authors_by_book is an MCS Search Accelerator table. Its columns are 'book' and 'authorname'.

n.b. There is also an MCS Search Accelerator table for Tags, _mcs_tags_by_book, which you can use for other purposes. Its columns are 'book' and 'tagname'.

Instructions: Refer to the attached User Guide.

Additional Documentation & Overviews: See the next Post for more overview and documentation images. https://www.mobileread.com/forums/sh...21&postcount=2

Requires Minimum Calibre Version: 6.7.0

Calibre Name: MultiColumnSearch

Use of MCS with Other Plug-ins:

Spoiler:

Version History:

Spoiler: