Register Guidelines E-Books Search Today's Posts Mark Forums Read

Go Back   MobileRead Forums > E-Book Software > Calibre > Plugins

Notices

Reply
 
Thread Tools Search this Thread
Old 06-13-2015, 12:17 PM   #1
DaltonST
Deviser
DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.
 
DaltonST's Avatar
 
Posts: 2,265
Karma: 2090983
Join Date: Aug 2013
Location: Texas
Device: none
[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:
  • 'Intra-Book' Search: Compare the columns within a single book.
  • 'Inter-Book' Search: Compare the columns of each book to the same columns in all other books in the same Library.

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

Example: Intra-Book query: (#int_cc1 > #int_cc2) AND (title CONTAINS series) in the Current Library. Also show all of the books by all of the Authors who are represented in the query results.

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

Example: Inter-Book query: Find all books that have the same Author and the same value in a particular Custom Column without having to specify either the Author or the value of the Custom Column. Let MCS do all of the work by comparing each book to every other book in the Current Library. Also show all of the books by all of the Authors who are represented in the query results.

Scope:
Intra-Book queries: The 8 Standard Columns listed below plus any and all Custom Columns are supported:
Spoiler:
  • custom columns (Any) (#........)
  • authors (but 'author' will work too)
  • title
  • series
  • tags
  • publishers
  • comments
  • pubdate (but 'published' will work too)
  • path



Scope: Inter-Book queries: The 3 Standard Columns listed below plus any and all Custom Columns are supported:
Spoiler:

  • custom columns (Any) (#........)
  • authors (but 'author' will work too)
  • title
  • series




Duplicate Custom Column Values :
Spoiler:
See the attached example image of a Raw SQL Query crafted to find Custom Column values that are duplicates. The basic SQL query (that you must customize for your own Custom Column names) is:
Code:
SELECT book  FROM books_custom_column_27_link 
 WHERE value IN
 ( SELECT value 
 FROM books_custom_column_27_link 
 GROUP BY value 
 HAVING COUNT(*) > 1 )




Full Text Search & Filename Search Queries Comments:



Metadata Variation Queries:
Spoiler:

See the attached example image of a Raw SQL Query crafted to find Author metadata variations. This example can easily be changed for other metadata by modifying the basic Query, shown below.
Code:
SELECT book FROM _mcs_authors_by_book,authors WHERE authorname <> authors.name 
 AND SIMILARTO(authorname,authors.name) > 0.85
Caution: if you have a large library (e.g. 50,000 books) with many Authors (e.g. 15,000), then 15,000 Authors will have to be checked against every other Author (14,999) for every book (50,000). That is (15,000 times 14,999 times 50,000) checks. That will take a very long time unless you add some SQL to limit the number of books selected per search. You could limit by one or more metadata attributes to do so. The limitation should be placed immediately after the WHERE keyword to increase performance.



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:
  • If you use a Custom Column for "Original Title" or "Translator", and MCS for Inter-Book Searches for one of those Custom Columns, then you might be interested in my Job Spy/JS+ GUI Tool "Extract Original Title/Translator to Update Custom Columns (EPUBs)".
  • If you use my CALM - Consolidate All Library Metadata plug-in, I suggest that you might also benefit from installing this MCS plug-in so you may directly compare any and all of the Standard and Custom Columns consolidated into the Target Library, which Standard Calibre cannot do.
  • If you use my QuarantineAndScrub add-on, I suggest that you might also benefit from installing this MCS plug-in so you may directly compare the #work_... columns to the "real" columns, which Standard Calibre cannot do.



Version History:
Spoiler:

Version 1.0.96 - 10 January 2023 Qt.core.

Version 1.0.95 - 03 December 2022 Raw SQL Queries Tab: fixed regression error when selecting Final Filters be used; added support for using the FTS db for completeness. However, the TXT Queries Tab is still the preferred MCS tool for FTS queries.
Version 1.0.94 - 15 November 2022 TXT Queries Tab: technical tweaks for Qt behavior.
Version 1.0.93 - 10 November 2022 Technical tweaks to optimize memory usage.
Version 1.0.92 - 08 November 2022 TXT Queries Tab:improved ToolTips; improved GUI responsiveness while executing searches with
highly complex regular expressions and long books.
Version 1.0.91 - 05 November 2022 TXT Queries Tab: improved ToolTips & miscellany.
Version 1.0.90 - 30 October 2022 TXT Queries Tab: Add searching of full-text-search.db if a format of .txt does not already exist. Minimum Calibre Version 6.7.0.
Version 1.0.89 - 14 April 2022 Qt6 Compatibility. Minimum Calibre Version 5.99.8.
Version 1.0.87 - 17 December 2020 Miscellaneous technical changes to improve performance.
Version 1.0.86 - 07 December 2020 New: Saved (named) criteria for each Query Type/Tab Function.
Version 1.0.85 - 22 October 2020 Zipfile strings.
Version 1.0.84 - 08 August 2020 "Post-Search" Tab: technical changes.
Version 1.0.83 - 27 July 2020 Inter/Intra Book Search Query: 'OPERATOR1' and 'OPERATOR2' defaulted to '='.
Version 1.0.82 - 21 May 2020 Python 3 regression errors fixed.
Version 1.0.81 - 11 April 2020 Raw SQL Automatic Capitalization Enhanced for PARSEJSON keyword.
Version 1.0.80 - 10 April 2020 Ability to search table books_plugin_data using new Raw SQL Query user function keyword "PARSEJSON".
Version 1.0.79 - 07 January 2020 Technical changes after Python 3.8 testing with Calibre 4.99.3
Version 1.0.78 - 03 January 2020 Technical changes after Python 3.8 testing with Calibre 4.99.2
Version 1.0.77 - 13 December 2019 Similarity Queries: enhancements.
Version 1.0.76 - 12 December 2019 Similarity Queries Tab.
Version 1.0.75 - 08 July 2019 Python 3 Compatibility. Minimum Calibre Version 3.41.3.
Version 1.0.74 - 13 October 2017 Full-Text Search enhancements for Tag-like Custom Columns to be updated by merging existing values with the new final search value.
Version 1.0.73 - 12 October 2017 Configuration preferences change.
Version 1.0.72 - 11 October 2017 Major Full-Text Search Enhancements. Option to additionally compare the initial search results to the value in a specified Custom Column, optionally using a Regular Expression to filter the initial search results. Another new option to update a Custom Column using the initial search results after further refining the search results text to be updated by using a Regular expression. These options may be used separately or simultaneously. Refer to the ToolTips MCS.
Version 1.0.71 - 05 May 2017 Allow themes with user-defined icons.
Version 1.0.70 - 13 April 2017 Changes to way null standard column values are handled in CL queries
Version 1.0.69 - 25 December 2016 Ignore corrupt UTF8 text in Cross-Library Duplicate Books Search
Version 1.0.68 - 03 August 2016 Cross-Library Duplicate Books Search with Current Library using Custom Columns and/or Standard Columns
Version 1.0.67 - 30 July 2016 Allow () in a library path.
Version 1.0.66 - 29 July 2016 Cross-Library Duplicate Books Search Using Custom Columns and/or Standard Columns
Version 1.0.64 - 07 June 2016 Miscellany.
Version 1.0.63 - 02 June 2016 Final Filters: Added a new operator, "REGEX".
Version 1.0.62 - 22 May 2016 Miscellany.
Version 1.0.61 - 16 May 2016 Miscellany.
Version 1.0.60 - 15 May 2016 Changes for Favourites plug-in.
Version 1.0.59 - 06 May 2016 New special results dialog for Intra-Book searches of Comments for a specific string.
Version 1.0.58 - 11 March 2016 SQL Tab user-defined function added: SIMILARTO.
Version 1.0.57 - 08 March 2016 Miscellaneous tweaks.
Version 1.0.56 - 07 March 2016 "Special Queries" enhanced to include more simultaneous comparisons.
Version 1.0.55 - 05 March 2016 Word-by-Book Index Queries: new 'NOT' operator, '!', to use at the beginning of a word when the 'bar' ('|') means 'AND' not 'OR'.
Version 1.0.54 - 04 March 2016 Miscellaneous tweaks.
Version 1.0.53 - 03 March 2016 Miscellaneous tweaks.
Version 1.0.52 - 02 March 2016 Miscellaneous tweaks.
Version 1.0.51 - 02 March 2016 Technical changes when switching libraries to ensure the Word-Book Index gets created in the 'new' library.
Version 1.0.50 - 01 March 2016 New: Word Queries using a book-word index; Revised User Instructions.
Version 1.0.49 - 20 February 2016 User Instructions revised and expanded.
Version 1.0.48 - 19 February 2016 Miscellaneous enhancements.
Version 1.0.47 - 18 February 2016 Miscellaneous enhancements.
Version 1.0.46 - 18 February 2016 Miscellaneous enhancements.
Version 1.0.45 - 17 February 2016 New: Full-Text Search of Books with TXT Formats using Regular Expressions.
Version 1.0.44 - 16 February 2016 New: Raw SELECT SQL Queries can now use the SQLite Regular Expression keyword REGEXP.
Version 1.0.43 - 12 February 2016 New: Raw SELECT SQL Query Tab
Version 1.0.42 - 02 February 2016 Special Queries now supports Series-Like Custom Columns in addition to all Text Custom Columns.
Version 1.0.41 - 01 February 2016 Minimize Button added to MCS dialog.
Version 1.0.40 - 28 January 2016 Special Queries now include as choices all Custom Columns with a datatype of "text".
Version 1.0.39 - 28 January 2016 Special Query execution now confirms that Final Filters passed validation the last time they were saved; otherwise, execution is canceled.
Version 1.0.38 - 27 January 2016 Enhancements to Final Filter validations and ToolTips.
Version 1.0.37 - 27 January 2016 Miscellaneous changes.
Version 1.0.36 - 26 January 2016 New: 'Special Queries' Tab (refer to the ToolTips for details); Bug Fix in Final Filters evaluation logic.
Version 1.0.35 - 20 January 2016 Miscellaneous Final Filter logic improvements.
Version 1.0.34 - 11 January 2016 New Search Option: 'Final Filters'. This will apply a maximum of 10 additional filters at the very end to all books that would otherwise have been displayed.
Version 1.0.33 - 5 January 2016 New Search Option: 'Show All Authors' Books'. This will also mark and display all books from the authors the search has otherwise found.
Version 1.0.32 - 3 January 2016 Improved Cancellation of Inter-Book Searches.
Version 1.0.31 - 29 December 2015 Autocompletion of Standard Column Names and Custom Column Names.
Version 1.0.30 - 28 December 2015 Technical tweaks.
Version 1.0.29 - 13 December 2015 New: MCS Search Accelerator Functionality for search criteria involving Tags. This functionality is automatic and routine. The user has to do nothing to use it.
Version 1.0.28 - 12 December 2015 New: MCS Search Accelerator Functionality for search criteria involving Authors. This functionality is automatic and routine. The user has to do nothing special to use it.
Version 1.0.27 - 12 December 2015 Inter-Book Search regression error fixed.
Version 1.0.26 - 12 December 2015 Major performance enhancements, especially if your queries do NOT need to analyze Authors or Tags. That is because a single book may have many Authors and many Tags.
Version 1.0.25 - 11 December 2015 New: Progress Indicator and ability to Cancel an MCS Search when the number of selected books > 149. Performance enhancements.
Version 1.0.24 - 10 December 2015 New: Double-Metaphone 'Sounds-Like' Fuzzy Equality Comparison Function.
Version 1.0.23 - 07 December 2015 User Instructions Revised.
Version 1.0.22 - 06 December 2015 Additional New 'Fuzzy Logic' Comparison Functions. Refer to the attached examples and also the ToolTips for an explanation.
Version 1.0.21 - 05 December 2015 New 'Fuzzy Logic' Comparison Functions. Refer to the attached examples and also the ToolTips for an explanation.
Version 1.0.20 - 29 November 2015 New 'Comparison Transform Function': 'Compare as: Decomposed & Normalized Alphabet'. This allows searching for non-English metadata that may or may not have been mangled into something English-like. Basically, it changes complex letters into simple letters, removes diacritics, removes spaces, and removes punctuation.
Version 1.0.19 - 27 November 2015 Addition of 'Comparison Transform Functions' that are applied to the values to be compared prior to their comparison.
Version 1.0.18 - 09 November 2015 Major enhancement to support the Post-Search updating of a single Custom Column for books that are both 'Marked' and 'Selected'.
Version 1.0.17 - 06 November 2015 Technical tweaks.
Version 1.0.16 - 05 November 2015 Major enhancement to support Inter-Book queries. Prior versions implicitly supported only Intra-Book queries.
Version 1.0.15 - 21 September 2015 Technical tweak.
Version 1.0.14 - 3 September 2015 New: ToolTips for key fields; Improved Error Messages when an invalid column choice is made.
Version 1.0.13 - 31 August 2015 Technical tweaks.
Version 1.0.12 - 24 June 2015 New: Cross-Library Search Results can be copied to the Clipboard as tab-delimited text for pasting "special" into a spreadsheet or normal pasting into any text document.
Version 1.0.11 - 23 June 2015 Various Enhancements; Revised User Guide.Version 1.0.10 - 23 June 2015 Cross-Library Search Results enhanced.
Version 1.0.9 - 21 June 2015 New: Regular Expressions; Revised User Guide.
Version 1.0.8 - 19 June 2015 New: Cross-Library Searches; Revised User Guide.
Version 1.0.7 - 17 June 2015 Regression error fixed.
Version 1.0.6 - 16 June 2015 Revised User Guide; MCS now validates User Criteria, and auto-corrects it if possible.
Version 1.0.5 - 16 June 2015 Many tweaks.
Version 1.0.4 - 15 June 2015 Added a "Swap Criteria" Button.
Version 1.0.3 - 15 June 2015 Improved handling of blanks, integers, floats and datetime values manually specified as Search Text.
Version 1.0.2 - 14 June 2015 Miscellany
Version 1.0.1 - 13 June 2015 Technical Changes
Version 1.0.0 - 13 June 2015 Initial Release

Attached Thumbnails
Click image for larger version

Name:	mcs_decomposed_and_normalized_alphabet_example.jpg
Views:	4170
Size:	912.9 KB
ID:	144171   Click image for larger version

Name:	mcs_fuzzy_equality_token_set_ratio_example.JPG
Views:	3504
Size:	274.1 KB
ID:	144328   Click image for larger version

Name:	mcs_cross_library_search_using_fuzzy_equality_function_example.JPG
Views:	3133
Size:	564.3 KB
ID:	144374   Click image for larger version

Name:	mcs_doublemetaphone_sounds_like_example.jpg
Views:	2824
Size:	263.2 KB
ID:	144492   Click image for larger version

Name:	mcs_raw_select_sql_tab.JPG
Views:	2733
Size:	279.1 KB
ID:	146256   Click image for larger version

Name:	mcs_word_book_index_queries_tab.JPG
Views:	2538
Size:	130.9 KB
ID:	146810   Click image for larger version

Name:	_mcs_book_awards_table.jpg
Views:	2440
Size:	429.3 KB
ID:	147039   Click image for larger version

Name:	mcs_sql_query_template_for_book_awards.jpg
Views:	2359
Size:	172.8 KB
ID:	147040   Click image for larger version

Name:	mcs_comments_results_viewer.jpg
Views:	2306
Size:	735.2 KB
ID:	150311   Click image for larger version

Name:	AuthorMetadataVariationsExampleUsing MCS.jpg
Views:	2098
Size:	438.0 KB
ID:	154795   Click image for larger version

Name:	mcs_sql_search_duplicates_in_single_custom_column.jpg
Views:	2168
Size:	1.01 MB
ID:	159218   Click image for larger version

Name:	Multi-Column Search TXT Queries Using Either Calibre FTS DB or Book TXT Formats as in FF.jpg
Views:	1264
Size:	540.8 KB
ID:	197467  
Attached Files
File Type: pdf mcs_instructions.pdf (2.65 MB, 2816 views)
File Type: txt mcs_word_book_index_example_csv_files_rename_to_zip_to_extract.txt (17.4 KB, 1961 views)
File Type: txt mcs_regular_expression_that_emulates_the_NEAR_operator_for_full_text_search_queries.txt (140 Bytes, 2116 views)
File Type: txt _mcs_book_awards_csv.txt (265.2 KB, 3525 views)
File Type: txt _mcs_book_awards_sql.txt (465.1 KB, 2765 views)
File Type: zip multi_column_search.zip (2.79 MB, 34750 views)

Last edited by DaltonST; 01-10-2023 at 01:04 PM. Reason: Version 1.0.96
DaltonST is offline   Reply With Quote
Old 06-13-2015, 12:18 PM   #2
DaltonST
Deviser
DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.
 
DaltonST's Avatar
 
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
Click image for larger version

Name:	mcs_dates_sql_search_example.jpg
Views:	2312
Size:	425.8 KB
ID:	160844   Click image for larger version

Name:	mcs_sql_query_parsjson_keyword_composite_custom_columns.jpg
Views:	1640
Size:	1.24 MB
ID:	178276   Click image for larger version

Name:	mcs_comments_search_results_viewer_example.jpg
Views:	1572
Size:	702.4 KB
ID:	178277   Click image for larger version

Name:	mcs_similarity_queries_20191212.jpg
Views:	1518
Size:	1.08 MB
ID:	178278   Click image for larger version

Name:	mcs_sqlquery_tooltips_composite.jpg
Views:	1513
Size:	784.8 KB
ID:	178279   Click image for larger version

Name:	mcs_next_series_index_to_read_sql_query.jpg
Views:	1476
Size:	507.7 KB
ID:	178280   Click image for larger version

Name:	mcs_full_text_search_using_optional_filtering_and_cc_updating.jpg
Views:	1579
Size:	1.48 MB
ID:	178281   Click image for larger version

Name:	mcs_sql_search_duplicates_in_single_custom_column.jpg
Views:	1526
Size:	1.01 MB
ID:	178282   Click image for larger version

Name:	mcs_ratings.jpg
Views:	1523
Size:	461.7 KB
ID:	178283  

Last edited by DaltonST; 04-11-2020 at 11:18 AM. Reason: Added Additional Overflow Example Images
DaltonST is offline   Reply With Quote
Advert
Old 06-13-2015, 12:18 PM   #3
DaltonST
Deviser
DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.
 
DaltonST's Avatar
 
Posts: 2,265
Karma: 2090983
Join Date: Aug 2013
Location: Texas
Device: none
For future use.
DaltonST is offline   Reply With Quote
Old 06-21-2015, 06:40 PM   #4
DaltonST
Deviser
DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.
 
DaltonST's Avatar
 
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
DaltonST is offline   Reply With Quote
Old 09-02-2015, 02:46 PM   #5
laji
Banned
laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.
 
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.
laji is offline   Reply With Quote
Advert
Old 09-02-2015, 11:11 PM   #6
DaltonST
Deviser
DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.
 
DaltonST's Avatar
 
Posts: 2,265
Karma: 2090983
Join Date: Aug 2013
Location: Texas
Device: none
Calibre Columns Available for Searching using MCS

Quote:
Originally Posted by laji View Post
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
DaltonST is offline   Reply With Quote
Old 09-03-2015, 04:02 AM   #7
eschwartz
Ex-Helpdesk Junkie
eschwartz ought to be getting tired of karma fortunes by now.eschwartz ought to be getting tired of karma fortunes by now.eschwartz ought to be getting tired of karma fortunes by now.eschwartz ought to be getting tired of karma fortunes by now.eschwartz ought to be getting tired of karma fortunes by now.eschwartz ought to be getting tired of karma fortunes by now.eschwartz ought to be getting tired of karma fortunes by now.eschwartz ought to be getting tired of karma fortunes by now.eschwartz ought to be getting tired of karma fortunes by now.eschwartz ought to be getting tired of karma fortunes by now.eschwartz ought to be getting tired of karma fortunes by now.
 
eschwartz's Avatar
 
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.
eschwartz is offline   Reply With Quote
Old 09-03-2015, 05:01 AM   #8
laji
Banned
laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.
 
Posts: 74
Karma: 20416
Join Date: Nov 2010
Location: Hungary, Budapest
Device: monitor
Quote:
Originally Posted by DaltonST View Post
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

Last edited by laji; 09-03-2015 at 06:36 AM. Reason: adding: "it's really very useful"
laji is offline   Reply With Quote
Old 09-03-2015, 05:22 AM   #9
eschwartz
Ex-Helpdesk Junkie
eschwartz ought to be getting tired of karma fortunes by now.eschwartz ought to be getting tired of karma fortunes by now.eschwartz ought to be getting tired of karma fortunes by now.eschwartz ought to be getting tired of karma fortunes by now.eschwartz ought to be getting tired of karma fortunes by now.eschwartz ought to be getting tired of karma fortunes by now.eschwartz ought to be getting tired of karma fortunes by now.eschwartz ought to be getting tired of karma fortunes by now.eschwartz ought to be getting tired of karma fortunes by now.eschwartz ought to be getting tired of karma fortunes by now.eschwartz ought to be getting tired of karma fortunes by now.
 
eschwartz's Avatar
 
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!
eschwartz is offline   Reply With Quote
Old 09-03-2015, 03:13 PM   #10
DaltonST
Deviser
DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.
 
DaltonST's Avatar
 
Posts: 2,265
Karma: 2090983
Join Date: Aug 2013
Location: Texas
Device: none
MCS: Scope, Usage, and Tooltips: Version 1.0.14

@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
DaltonST is offline   Reply With Quote
Old 09-03-2015, 07:17 PM   #11
BetterRed
null operator (he/him)
BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.
 
Posts: 21,067
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
BetterRed is offline   Reply With Quote
Old 09-03-2015, 10:57 PM   #12
laji
Banned
laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.laji can self-interpret dreams as they happen.
 
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
laji is offline   Reply With Quote
Old 09-07-2015, 02:33 PM   #13
enkirkby
Junior Member
enkirkby began at the beginning.
 
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
enkirkby is offline   Reply With Quote
Old 09-07-2015, 02:45 PM   #14
DaltonST
Deviser
DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.
 
DaltonST's Avatar
 
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.
DaltonST is offline   Reply With Quote
Old 09-07-2015, 03:36 PM   #15
enkirkby
Junior Member
enkirkby began at the beginning.
 
Posts: 8
Karma: 10
Join Date: Sep 2015
Device: kindle paperwhite
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 View Post
@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 Dr. Drib; 09-07-2015 at 08:00 PM. Reason: adding more details
enkirkby is offline   Reply With Quote
Reply

Tags
columns, search

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Forum Jump

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


All times are GMT -4. The time now is 02:01 PM.


MobileRead.com is a privately owned, operated and funded community.