[GUI Plugin] Multi-Column Search

DaltonST · 06-13-2015, 12:17 PM

[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:

DaltonST · 06-13-2015, 12:18 PM

The Original Post just above is only allowed 18 attachments. Additional examples are attached to this post for that reason.

DaltonST · 06-13-2015, 12:18 PM

For future use.

DaltonST · 06-21-2015, 06:40 PM

New: Regular Expressions in addition to the existing 6 algebraic operators (=,<=, etc.) plus "contains" and "does not contain".

New: Cross-Library Multi-Column Searches (i.e., search a Library that is not the Library from which you are executing the Search).

DaltonST

laji · 09-02-2015, 02:46 PM

Have you got a more explained help, my be, with examples? To say the truth, I can not understand anything, how it works. All the help, when anything fails, is a word "ERROR"...

For example, how must I fill the fields and choose the relations, if I want to find books, where title<>title_sort? Thank you in advance.

Edit: or, maybe, is there a way to do this in standard Calibre? This like searches were the case to install your plugin, but I can not use it.

DaltonST · 09-02-2015, 11:11 PM

Quote:

Originally Posted by laji

Have you got a more explained help, my be, with examples? To say the truth, I can not understand anything, how it works. All the help, when anything fails, is a word "ERROR"...

For example, how must I fill the fields and choose the relations, if I want to find books, where title<>title_sort? Thank you in advance.

Edit: or, maybe, is there a way to do this in standard Calibre? This like searches were the case to install your plugin, but I can not use it.

@laji:

It would seem that you are not choosing from the "Calibre Columns Available for Searching using MCS", each of which is explained in detail on Page 2 of the User Guide. To summarize, they are:

custom columns (Any) (#........)
authors (but 'author' will work too)
title
series
tags
publishers
comments
pubdate (but 'published' will work too)
path

For example, you are asking why you get an error when you search using "title<>title_sort". Nowhere above, nor on Page 2 of the User Guide, exists "title_sort" as a "Calibre Column Available for Searching using MCS".

If you wish to learn more about the definition and use of "Lookup/Search Name(s)" in the Calibre GUI, which is key to using MCS, I highly recommend this link: http://manual.calibre-ebook.com/gui.html#the-search-interface .

Hovering your mouse over a "Column" in the GUI will expose its "Lookup/Search Name". That is the name to use in MCS if you wish to use that particular "Column".

Since "title_sort" is not a "Standard Column", the only way to use it is to make it visible in the Calibre GUI as a "Custom Column" called, for example, "#titlesort". You could then use it in MCS. Of course, you will have to populate it first from "books.sort".

To populate a "Custom Column" from an arbitrary SQLite table column in metadata.db, I refer you to useful documentation: embedded within the Calibre GUI; http://manual.calibre-ebook.com; http://manual.calibre-ebook.com/db_api.html; and others.

I would recommend to you a much faster and easier way to answer transient questions about the data. Simply create a list using 'calibredb --list' as outlined in http://manual.calibre-ebook.com/cli/calibredb.html.

Import the list created above into a spreadsheet application, and use the standard spreadsheet text functions to manipulate the data in order to answer your questions.

Personally, I would use an even faster and easier way than the prior method. Connect to metadata.db using a SQLite browser, and then execute this native SQL: SELECT id,title,sort FROM books WHERE title <> sort . Then, export the results.

Example output: "2631" "The 9th Judgment" "9th Judgment, The"

Best of luck.

DaltonST

eschwartz · 09-03-2015, 04:02 AM

Code:

title_sort:"=14 Years of Loyal Service in a Fabric-Covered Box"

Returns the right result for me... at least when using calibre's native search.
That is "Standard" enough for me.

laji · 09-03-2015, 05:01 AM

Quote:

Originally Posted by DaltonST

Connect to metadata.db using a SQLite browser, and then execute this native SQL: SELECT id,title,sort FROM books WHERE title <> sort . Then, export the results.

DaltonST, thank you for detailed and quick help; it's really very useful. Using of database (or excel for easier, as my example is, questions), of course, give a big power. I'm sorry because of no way to do this in Calibre themselve.

I can export the list with any column data from db or Excel table, but yet I can not imagine, how to show the resulted list in Calibre. Studying the functions...

Best regards.
laji

eschwartz · 09-03-2015, 05:22 AM

In native calibre, the only thing I can think of is using a custom column template like "{title} :: {title_sort}".

This plugin *should* be able to handle any columns calibre can handle -- pity it can't!

DaltonST · 09-03-2015, 03:13 PM

@laji:

I just uploaded a new version that has "ToolTips" for the 4 fields that were causing you trouble. I also improved the error messages related to using non-supported column names.

I also improved the Original Post by adding a new "Usage" section and a new "Scope" section, the latter from Page 2 of the User Guide.

Quote:

Summary: 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.

Usage: MCS is designed to do things that Standard Calibre cannot do. If Standard Calibre can do it, then use Standard Calibre.

Example: (#int_cc1 > #int_cc2) AND (title CONTAINS series) in the Current Library.

Example: (#int_cc1 > #int_cc2) AND (title CONTAINS series) in Another Library that is not the Current Library.

Scope: The 8 Standard Columns listed below plus any and all Custom Columns are supported:

custom columns (Any) (#........)
authors (but 'author' will work too)
title
series
tags
publishers
comments
pubdate (but 'published' will work too)
path

DaltonST

BetterRed · 09-03-2015, 07:17 PM

Moderator Notice
OT discussion moved to Book Details in Library Management

@DaltonST - I presume that's okay by you

laji · 09-03-2015, 10:57 PM

DaltonST, thank you very much for your quick response. I shall try the plugin again.

Best regards.
laji

enkirkby · 09-07-2015, 02:33 PM

Hello DaltonST,

I am trying to find a way how to make your plugin work. I would like to search in 3 columns:
- author = Author names
- title = Titles of the books in French and in English
- original_title = title of the book in original language, mostly in English
I want to find all books which has the same author and the title equal to original_title. The result should be the list of all books for which I have English equivalents. Based on finding then I want to set up flag in column _traduction, for which I have special virtual library (I know this last task is not in your plugin, just to show all work I am trying to accomplish).
But I can't make it working using multicolumn search. What am I doing wrong, please?

Example or returned result:
Author ------------------ Name ----------------- Original_name
Jules Verne ---- Five weeks in ballon -------- Cinq semaines en ballon
Jules Verne ---- Cinq semaines en ballon ----Cinq semaines en ballon

Thank you for help. Enkirkby

DaltonST · 09-07-2015, 02:45 PM

@enkirkby:

Since you did not attach a screen-snip of your MCS parameter dialog, I can only guess that your problem is caused by not using "#original_name" for (what I presume to be) the custom column that has a descriptive label of "Original_name".

Page 1 of the User Guide has a picture of how to search Custom Columns. Note they all have a # in the front. "Original_name" is not the "Search/Lookup Name" that you see when you hover your mouse over a Calibre GUI Column.

Page 2 of the User Guide shows the valid "Search/Lookup Name" for Standard Columns, and of course a leading "#" for Custom Columns.

The MCS Cross-Library results example shown on Page 3 of the User Guide shows the Custom Column # name.

DaltonST

enkirkby · 09-07-2015, 03:36 PM

Hello DaltonST,

please see search settings. Thank you.
Edit: The Original_name column has search name defined as #original_title, just for sure to differentiate.

[Image violates Posting Guidelines for size - MODERATOR]

Quote:

Originally Posted by DaltonST

@enkirkby:

Since you did not attach a screen-snip of your MCS parameter dialog, I can only guess that your problem is caused by not using "#original_name" for (what I presume to be) the custom column that has a descriptive label of "Original_name".

Page 1 of the User Guide has a picture of how to search Custom Columns. Note they all have a # in the front. "Original_name" is not the "Search/Lookup Name" that you see when you hover your mouse over a Calibre GUI Column.

Page 2 of the User Guide shows the valid "Search/Lookup Name" for Standard Columns, and of course a leading "#" for Custom Columns.

The MCS Cross-Library results example shown on Page 3 of the User Guide shows the Custom Column # name.

DaltonST

06-13-2015, 12:18 PM	#2
DaltonST Deviser Posts: 2,265 Karma: 2090983 Join Date: Aug 2013 Location: Texas Device: none	Documentation: Additional Example Images The Original Post just above is only allowed 18 attachments. Additional examples are attached to this post for that reason. Attached Thumbnails Last edited by DaltonST; 04-11-2020 at 11:18 AM. Reason: Added Additional Overflow Example Images

06-21-2015, 06:40 PM	#4
DaltonST Deviser Posts: 2,265 Karma: 2090983 Join Date: Aug 2013 Location: Texas Device: none	MCS: Cross-Library Searches & Regular Expressions New: Regular Expressions in addition to the existing 6 algebraic operators (=,<=, etc.) plus "contains" and "does not contain". New: Cross-Library Multi-Column Searches (i.e., search a Library that is not the Library from which you are executing the Search). DaltonST

09-03-2015, 04:02 AM	#7
eschwartz Ex-Helpdesk Junkie Posts: 19,421 Karma: 85397180 Join Date: Nov 2012 Location: The Beaten Path, USA, Roundworld, This Side of Infinity Device: Kindle Touch fw5.3.7 (Wifi only)	Code: title_sort:"=14 Years of Loyal Service in a Fabric-Covered Box" Returns the right result for me... at least when using calibre's native search. That is "Standard" enough for me.

09-07-2015, 02:45 PM	#14
DaltonST Deviser Posts: 2,265 Karma: 2090983 Join Date: Aug 2013 Location: Texas Device: none	MCS Uses the "Search/Lookup Name", Not the "Descriptive Name" @enkirkby: Since you did not attach a screen-snip of your MCS parameter dialog, I can only guess that your problem is caused by not using "#original_name" for (what I presume to be) the custom column that has a descriptive label of "Original_name". Page 1 of the User Guide has a picture of how to search Custom Columns. Note they all have a # in the front. "Original_name" is not the "Search/Lookup Name" that you see when you hover your mouse over a Calibre GUI Column. Page 2 of the User Guide shows the valid "Search/Lookup Name" for Standard Columns, and of course a leading "#" for Custom Columns. The MCS Cross-Library results example shown on Page 3 of the User Guide shows the Custom Column # name. DaltonST Last edited by DaltonST; 09-07-2015 at 02:48 PM.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
[GUI Plugin] Search the Internet	kiwidude	Plugins	434	09-30-2024 04:04 AM
[GUI Plugin] Clipboard Search	kiwidude	Plugins	29	04-02-2024 11:05 PM
[GUI Plugin] Walk Search History	kiwidude	Plugins	38	03-17-2024 01:47 AM
[GUI Plugin] Recoll Full Text Search	Satas	Plugins	16	08-05-2016 04:54 AM
[GUI Plugin] Full Text Search (SOLR)	peterpisljar	Plugins	2	08-09-2015 09:16 AM

09-02-2015, 02:46 PM	#5
laji Banned Posts: 74 Karma: 20416 Join Date: Nov 2010 Location: Hungary, Budapest Device: monitor	Have you got a more explained help, my be, with examples? To say the truth, I can not understand anything, how it works. All the help, when anything fails, is a word "ERROR"... For example, how must I fill the fields and choose the relations, if I want to find books, where title<>title_sort? Thank you in advance. Edit: or, maybe, is there a way to do this in standard Calibre? This like searches were the case to install your plugin, but I can not use it.

09-03-2015, 05:22 AM	#9
eschwartz Ex-Helpdesk Junkie Posts: 19,421 Karma: 85397180 Join Date: Nov 2012 Location: The Beaten Path, USA, Roundworld, This Side of Infinity Device: Kindle Touch fw5.3.7 (Wifi only)	In native calibre, the only thing I can think of is using a custom column template like "{title} :: {title_sort}". This plugin should be able to handle any columns calibre can handle -- pity it can't!

09-03-2015, 07:17 PM	#11
BetterRed null operator (he/him) Posts: 21,110 Karma: 27620706 Join Date: Mar 2012 Location: Sydney Australia Device: none	Moderator Notice OT discussion moved to Book Details in Library Management @DaltonST - I presume that's okay by you

09-03-2015, 10:57 PM	#12
laji Banned Posts: 74 Karma: 20416 Join Date: Nov 2010 Location: Hungary, Budapest Device: monitor	DaltonST, thank you very much for your quick response. I shall try the plugin again. Best regards. laji

09-07-2015, 02:33 PM	#13
enkirkby Junior Member Posts: 8 Karma: 10 Join Date: Sep 2015 Device: kindle paperwhite	Hello DaltonST, I am trying to find a way how to make your plugin work. I would like to search in 3 columns: - author = Author names - title = Titles of the books in French and in English - original_title = title of the book in original language, mostly in English I want to find all books which has the same author and the title equal to original_title. The result should be the list of all books for which I have English equivalents. Based on finding then I want to set up flag in column _traduction, for which I have special virtual library (I know this last task is not in your plugin, just to show all work I am trying to accomplish). But I can't make it working using multicolumn search. What am I doing wrong, please? Example or returned result: Author ------------------ Name ----------------- Original_name Jules Verne ---- Five weeks in ballon -------- Cinq semaines en ballon Jules Verne ---- Cinq semaines en ballon ----Cinq semaines en ballon Thank you for help. Enkirkby

Advert

Advert