10-18-2017, 04:51 PM | #226 | ||
Deviser
Posts: 2,265
Karma: 2090983
Join Date: Aug 2013
Location: Texas
Device: none
|
Next in series
Quote:
See the attached screenprint for an example. Quote:
"LIKE" ignores the case of the letters, but uses both _ and % as a wildcard. "_read" starts with an underscore, so as to avoid complications in my testing, I used "ReadAlready" instead. Finally, table _mcs_tags_concatenate is an MCS Search Accelerator Table is automatically updated by MCS in most cases. However, if you make a lot of Tag changes and immediately want to run this MCS query, it might be a good idea to force a manual update of the MCS Search Accelerator Tables via the menu path MCS > Refresh MCS Search Accelerators. There are search accelerator tables for both Tags and Authors. The menu ToolTips mention Authors, but the same is true for Tags. DaltonST |
||
10-20-2017, 07:21 AM | #227 |
Wizard
Posts: 1,327
Karma: 5306
Join Date: Jan 2014
Device: none
|
I tried to run the following SQL Query
Code:
SELECT book FROM books_custom_column_9_link WHERE value IN ( SELECT value FROM books_custom_column_9_link GROUP BY value HAVING COUNT(*) > 1 ) Code:
calibre, version 3.6.0 ERROR: MCS: Raw SQL Query Fatal Error: SQLError: no such table: books_custom_column_9_link |
Advert | |
|
10-20-2017, 09:56 AM | #228 |
Deviser
Posts: 2,265
Karma: 2090983
Join Date: Aug 2013
Location: Texas
Device: none
|
Tanjamuse:
You are using the SQL Query Tab. The "9" in that Custom Column table number has nothing whatsoever to do with anything else. It is a coincidence that the Regular Expression Tab regular expression that you used in the Full Text Query Tab was a "9". See the attached picture of the ToolTips for my SQL Query Tab. The numbers on the left are the Custom Column table numbers. They are Calibre-Library specific. Your numbers will be different than mine, and even different among your own Libraries (unless they are copies of each other). The SQL Query Tab can use regular expressions, but only as part of the SQL that you write. The "example SQL" button in that Tab has a lot of examples, including one with the REGEXP operator (I wrote just for MCS, by the way; it is not standard SQL that comes with Calibre). Code:
/*Comments: An example that finds ISBNs within Tags: SELECT book FROM _mcs_tags_by_book WHERE tagname LIKE '%978%' OR tagname LIKE '%045%' OR tagname LIKE '%isbn%' OR tagname REGEXP '^[0-9][-]*[0-9][-]*[0-9][-]*[0-9][-]*[0-9][-]*[0-9][-]*[0-9][-]*[0-9][-]*[0-9][-]*[0-9]+$'*/ Again, the SQL Query Tab does not use any of the Regular Expression Tab's regular expressions. Those regular expressions are used only by the Intra/Inter Book Query Tab and the Full-Text Query Tab. DaltonST |
10-20-2017, 10:00 AM | #229 |
Wizard
Posts: 1,327
Karma: 5306
Join Date: Jan 2014
Device: none
|
Ah. Thanks. How come it changed from the last time I did a SQL Query?
|
10-21-2017, 12:45 AM | #230 |
Junior Member
Posts: 5
Karma: 10
Join Date: Jun 2011
Device: Kindle
|
Thank you
This is superb. Many thanks.
|
Advert | |
|
10-23-2017, 10:08 AM | #231 |
Wizard
Posts: 1,327
Karma: 5306
Join Date: Jan 2014
Device: none
|
In the batch of files that I sent you, sometimes the title of the file in calibre doesn't match with what's in the txt-files (neither does the authors)
Can I edit the title batch so that it matches what's in a specific line in a txt-file? It's always line #4 for the title and line #6 for the authors. |
10-23-2017, 12:04 PM | #232 | |
Deviser
Posts: 2,265
Karma: 2090983
Join Date: Aug 2013
Location: Texas
Device: none
|
Full-Text Query to Update Custom Column Example
Quote:
See the attached. Note that it is not "always" where it should be; just almost always. Also, 2 of the 20 books had no match at all (per the lower left corner statistics), so they have some other nonconformity. I highly recommend Pythex to prototype and test your regular expressions. MCS always uses RE options Multiline, Ignorecase and Dotall, so you should always activate those options in Pythex (as shown in the image attached below). Added: you may need to add some special characters to the regular expression to account for Titles that have quotes, semi-colons, periods, etc. So, "^\s+[a-zA-Z0-9 ]+\n" may need to be something like "^\s+[a-zA-Z0-9 ,';"]+\n" and so forth. Regular expressions use odd characters as its control characters, so you may need to "escape" them in your regular expression so it treats them as your characters, and not its control characters. Pythex will tell you when you get it right (or wrong). DaltonST Last edited by DaltonST; 10-23-2017 at 12:16 PM. |
|
10-23-2017, 12:20 PM | #233 |
Wizard
Posts: 1,327
Karma: 5306
Join Date: Jan 2014
Device: none
|
Thanks. I'll have a look.
|
10-25-2017, 08:33 AM | #234 |
Wizard
Posts: 1,327
Karma: 5306
Join Date: Jan 2014
Device: none
|
Can I compare size of files as well as a title or author? Because I couldn't find it in the Book Queries? I also tried searching for binary duplicates because I thought that it meant it could find files based on either what's in them or the size of them.
|
10-25-2017, 10:34 AM | #235 |
Deviser
Posts: 2,265
Karma: 2090983
Join Date: Aug 2013
Location: Texas
Device: none
|
MCS has no direct support in its query for standard column "size".
However, it has indirect support for "size" if you do this: [1] Create a Composite Custom Column that looks like the one shown in the attached image. [2] Use that Custom Column as you would any other Custom Column in MCS. Note that you will see the "uncompressed size" from metadata.db table "data", and not the user-friendly "size" shown in the Library View. An example is shown in the second image. DaltonST |
10-25-2017, 10:54 AM | #236 |
Wizard
Posts: 1,327
Karma: 5306
Join Date: Jan 2014
Device: none
|
I tried to use that column in a SQL query, but didn't get any results:
Code:
SELECT book FROM books_custom_column_37_link WHERE value IN ( SELECT value FROM books_custom_column_37_link GROUP BY value HAVING COUNT(*) > 1 ) |
10-25-2017, 12:14 PM | #237 | |
Deviser
Posts: 2,265
Karma: 2090983
Join Date: Aug 2013
Location: Texas
Device: none
|
Quote:
[1] Composite Custom Columns do not have a book...link table, so your books_custom_column_37_link is wrong. It should be custom_column_37 per the ToolTips that you have if you hover your mouse over the right side of the SQL Query Tab. [2] Unfortunately, #1 would not work even if it were correct, because Calibre does not update a Composite Custom Column in the SQLite metadata.db database; it only updates the GUI cache. Composite Custom Columns tables are always empty unless you use the Calibre Companion special "Library-Closed" plug-in, that does not work for you here, because your Library is open, not closed. [3] You need to create a "real" Comments Custom Column and use Search & Replace to copy the Composite CC to the new "real" CC. The S&R function first uses the GUI cache, not the SQLite database, but then does update the SQLite database at the end. [4] Change your SQL Query to search the "real" Comments CC instead of the "virtual" Composite CC. Please study the attached image which is a comprehensive example of the above. Note that my MCS test Library's Custom Columns and their numbers are different than yours. You must use your ToolTips, not mine that are shown in the example. DaltonST |
|
10-25-2017, 12:18 PM | #238 |
Wizard
Posts: 1,327
Karma: 5306
Join Date: Jan 2014
Device: none
|
Ok.
There's something else I don't understand, why in one library is this the correct search: Code:
SELECT book FROM books_custom_column_32_link WHERE value IN ( SELECT value FROM books_custom_column_32_link GROUP BY value HAVING COUNT(*) > 1 ) Code:
SELECT book FROM books_custom_column_9_link WHERE value IN ( SELECT value FROM books_custom_column_9_link GROUP BY value HAVING COUNT(*) > 1 ) |
10-25-2017, 12:23 PM | #239 |
Deviser
Posts: 2,265
Karma: 2090983
Join Date: Aug 2013
Location: Texas
Device: none
|
I answered that previously. Your Custom Columns are numbered in the numerical sequence that they were first created in each library. That is why I always refer to the need to hover your mouse over the ToolTips to see what the CC numbers are for your current library. You must pay attention to those ToolTips.
Last edited by DaltonST; 10-25-2017 at 01:41 PM. |
01-13-2018, 03:09 PM | #240 | ||
hopeless n00b
Posts: 5,110
Karma: 19597086
Join Date: Jan 2009
Location: in the middle of nowhere
Device: PW4, PW3, Libra H2O, iPad 10.5, iPad 11, iPad 12.9
|
New to MCS. Thank you for this wonderful plugin! Helped me a lot with tagging books.
Question, is there a way to use the regex results from TXT Queries to automatically populate ebook metadata? For example: Regex Code:
Summary[\s]+(.+)\s{4,} Text Quote:
Set comment to the following: Quote:
Thanks! |
||
Tags |
columns, search |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
[GUI Plugin] Search the Internet | kiwidude | Plugins | 434 | 09-30-2024 03:04 AM |
[GUI Plugin] Clipboard Search | kiwidude | Plugins | 29 | 04-02-2024 10:05 PM |
[GUI Plugin] Walk Search History | kiwidude | Plugins | 38 | 03-17-2024 12:47 AM |
[GUI Plugin] Recoll Full Text Search | Satas | Plugins | 16 | 08-05-2016 03:54 AM |
[GUI Plugin] Full Text Search (SOLR) | peterpisljar | Plugins | 2 | 08-09-2015 08:16 AM |