[GUI Plugin] Multi-Column Search

[DaltonST]

Originally Posted by Ray99

I use the tag "_read" when I've finished reading a book. At any time I have many series on the go and I would like to be able to search for the first unread book in each of the series of which I have read at least one book.

This is an MCS feature that does what was requested above.


See the attached screenprint for an example.

SELECT book AS unread, series AS unreadseries
FROM books_series_link
WHERE
(unread NOT IN (SELECT book FROM _mcs_tags_concatenate WHERE tagsconcat LIKE '%read%') )
AND
(unreadseries IN (SELECT series FROM books_series_link WHERE book IN (SELECT book FROM _mcs_tags_concatenate WHERE tagsconcat LIKE '%read%') ) )

GROUP BY unreadseries

"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

[Tanjamuse]

I tried to run the following SQL Query

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 )

and got this error:

calibre, version 3.6.0
ERROR: MCS: Raw SQL Query Fatal Error:   SQLError: no such table: books_custom_column_9_link

Since I changed the 9. expression in the Regular Expression tab can I use one of the other expressions and what do I write?

[DaltonST]

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

/*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

[Tanjamuse]

Ah. Thanks. How come it changed from the last time I did a SQL Query?

[Ray99]

This is superb. Many thanks.

[Tanjamuse]

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.

[DaltonST]

Originally Posted by Tanjamuse

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.

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

[Tanjamuse]

Thanks. I'll have a look.

[Tanjamuse]

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.

[DaltonST]

Originally Posted by Tanjamuse

Can I compare size of files as well as a title or author?

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

[Tanjamuse]

I tried to use that column in a SQL query, but didn't get any results:

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 )

[DaltonST]

Originally Posted by Tanjamuse

I tried to use that column in a SQL query, but didn't get any results:

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 )

Tanjamuse:

[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

[Tanjamuse]

Ok.

There's something else I don't understand,

why in one library is this the correct search:

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 )

and in another it's this:

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 )

When it's the same custom column I need to search?

[DaltonST]

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.

[ilovejedd]

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

Summary[\s]+(.+)\s{4,}

Text

Dragons of Ice and Fire


by serpentguy

Summary

There are things hidden in the far north, secrets buried under ice. Jon Snow faces the white walkers early, and stumbles upon a power that could change the world. It forges a new path for him and everyone around him - and a new journey south, a journey as a dragon.

The world begins to change - with steel and snow, fire and blood...





Chapter 1

Set comment to the following:

There are things hidden in the far north, secrets buried under ice. Jon Snow faces the white walkers early, and stumbles upon a power that could change the world. It forges a new path for him and everyone around him - and a new journey south, a journey as a dragon.

The world begins to change - with steel and snow, fire and blood...

Thanks!