[GUI Plugin] Multi-Column Search

[DaltonST]

@Tanjamuse:

I use Windows 10 64. I just plugged in a USB 3.0 external hard-drive that I use for backing up everything Calibre-related. I ran Calibre, and Quick-Switched to an MCS-testing Library that was backed-up on that USB drive. I then clicked the MCS icon, switched to the "Text Queries" (Full-Text Search) Tab, entered the regular expression "\bDNA\W+(?:\w+\W+){0,100}?VIRUS\b", and got back (albeit a little slowly since the Library is on an external USB drive) the correct book containing text that matches that regular expression (the word 'DNA' within 100 words of the word 'virus').

Sorry, but I cannot reproduce your problem.


DaltonST

[Tanjamuse]

It's okay. I'm having trouble with this plugin. It works on every library except the one I need to use it on.

[Gary_M_Mugford]

DaltonST,

For what it's worth, I did dupe Tanjamuse's issue with my Win7 layout WITH the latest version. I set up shop on drive E and M for my libs. But I immediately clicked the sub-menu section of the button and clicked to Regenerate the accelerators. Then, I just used the sub-menu and chose MCS and it worked. I thus assume it would have worked if I just clicked it on the main toolbar.

I've been out of it for a bit. But I'm gleefully running the search for author's names in book titles. The heart's desire is this:

Search simultaneously for author names in Book title OR series name in book title and offer a pre-checked list of all matches. User proofs and then presses second button to delete those unnecessary additions (tricky since you have a one, either, or both match type) to the Title and THEN goes and deletes all braces, brackets and square brackets that are empty of all but spaces and punctuation. The TRICK of course is the detritus might NOT be down to just that. C'est la vie. So maybe the whole dream scenario has to include publisher as well. This would be the ULTIMATE clean-up tool and one I think you'd be wise to offer up as a standalone add-in that uses the MCS engine. Might I NOW DEMAND you offer up a PayPal tip jar for us to fill? If you aren't so inclined out of virtuousness, at least PM me and let me send you a coupon for a coffee, at least.

Thanks for all of your efforts, GM

[DaltonST]

@GM:

It already exists: QuarantineAndScrub.


Dalton

[Gary_M_Mugford]

Originally Posted by DaltonST

@GM:
It already exists: QuarantineAndScrub.
Dalton

Dalton,

I think what you have done with ALL of your Calibre addon work is spectacular. That you have responded here with MCS to just about every request and made MCS more utilitarian with every step is beyond laudable.

That said, Q&S is a difficult interface to learn. I've made two attempts and a combination of own lack of understanding and time to study it being taken away from me by some health issues, has resulted in failure. My fault. I think in my 'own' ways and they are different stylistically from yours. I will have to make that effort anew.

BUT, here's one thing about my little effort to detect author names in the title. I knew there would be a few. The Best Of series were going to be in the final results.

Unfortunately, when I ran the search against an admittedly absurdly large dataset, the result that came back was roughly five percent of the original data. And NONE had the author name(s) in the title. Where I had expected maybe a half-percent in all, I got ten times that ... of wrong answers. I couldn't see ANY rhyme or reason for why the results matched the query.

I tried it a second time (Special Queries ... Title Contains Authors Name) while having about 50 books selected, including The Best of Stanley G. Weinbaum. Naturally, the query doesn't go against selected books, but all of them. The query took a long time (no fault of yours, just too much data). And came back with the same roughly five percent and NO, The Best of Stanley G. Weinbaum was not included. Missed the Halcyon eBook edition and the paperback version data listing (three copies).

So, I now realize that MCS has its uses, which expand weekly. It's up to me to face the fact. I need Q&S. I guess. When time permits.

Thanks again, GM

[DaltonST]

@GM:

It is critical to hover a mouse over the various areas of each Tab in MCS so that the various ToolTips appear.

For simply asking "what books have their own author in their title", use an Intra-Book Query. You may choose either to execute this for "Selected Books" or "All Books" or "All Books in Another Library (Cross-Library)".

For asking "what books have any author that exists in any book in the current library in their title", use the Special Queries Tab. Of course, Special Queries are always for "All Books".

Note that Virtual Libraries can cause confusion (there is a ToolTip about that) since MCS does not 'see' or 'use' VLs at all, so when it returns the results of "All Books" to Calibre, any VL that was in play at the time of executing the query will show only the MCS results that also conform to the VL criteria. So, MCS gives you all of the results, but the VL then filters those results through the VL criteria and only shows you what is left.



DaltonST

[DaltonST]

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:

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:

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:

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'.


DaltonST

[DaltonST]

Version 1.0.59 - 06 May 2016 New special results dialog for Intra-Book searches of Comments for a specific string


Refer to: https://www.mobileread.com/forums/sho...64&postcount=9



DaltonST

[Vortex]

I'm trying to use MCS to find titles that contain the series in brackets in the title, but not having much luck. For example:

Author: Joe Bloggs
Title: The Book Title (The Series Name)
Series: The Series Name

Is this possible using MCS?
Thanks.

[DaltonST]

Absolutely.

Use the "Special Queries" Tab.

Read the ToolTips everywhere on that Tab.


DaltonST

[DaltonST]

Originally Posted by DaltonST

Absolutely.

Use the "Special Queries" Tab.

Read the ToolTips everywhere on that Tab.

Or, use Intra-Book searches with the CONTAINS operator.

Or, if you know a little SQL, you could use the RawSQL Tab. Look at the example SQL via that pushbutton.

I have designed MCS to do anything that I can conceive.

Read the ToolTips on each Tab for examples, do's and don'ts, advice, etc.

My QuarantineAndScrub add-on will automatically fix it too. Just first read the OP to determine if Q+S is for you.


DaltonST

[Vortex]

Thanks, Dalton. I can find the series name in the title with the Special Queries tab but cant figure out how to only find it if it's the exact series name in brackets. I tried QuarantineAndScrub when it first came out but it's out of my league.

[DaltonST]

Originally Posted by Vortex

Thanks, Dalton. I can find the series name in the title with the Special Queries tab but cant figure out how to only find it if it's the exact series name in brackets. I tried QuarantineAndScrub when it first came out but it's out of my league.

Add a FinalFilter for the brackets.

Title,is,like, %(%)%

DaltonST

[Vortex]

That finds the series in the title within and without brackets, plus with anything else that happens to be there, I want to find just books with the exact series info in brackets in the title. Also on big libraries it just hangs and says not responding for hours until I end task it.

I'm using these settings in <Special Queries> and <Final Filters>, should I be setting anything in <Intra/Inner Book Queries> or any other tabs?





Thanks.

[DaltonST]

@Vortex:

As the ToolTips for the Special Queries Tab say, that type of query will compare every single Series in that lilbrary with every single Title in that library. You will get the results of all books with any Series in that library in its title.

By "big libraries", I can imagine that if you have a 100,000 book library and are running Calibre on a pc without a SSD, but instead are using a typical hard-drive, it will take a couple of hours.

It does not "hang", but rather is doing the search that you asked for. 100,000 titles each compared to 100,000 series equals 10,000,000,000 comparisons. That takes some time, even on an SSD. As the saying goes, be careful what you ask for; you may get it.

If you want every book that has its own series in its own title, then do not use Special Queries.

Use Intra-Book Queries with "title" "CONTAINS" "series". This will run very fast because you are comparing columns within the same book, not all columns of all books to all other books (as does Special Queries). The ToolTips explain that.

A Final Filter of "book: title" "is" "like" "(%)" works perfectly for me. So, use a plain (%) in the value column of Final Filters. The ToolTips for the operator dropdown explain the nuances of the LIKE operator.




DaltonST