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 10-18-2017, 04:51 PM   #226
DaltonST
Deviser
DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!
 
DaltonST's Avatar
 
Posts: 1,062
Karma: 100454
Join Date: Aug 2013
Location: Texas
Device: 8" Win10 Tablet w/Calibre64
Next in series

Quote:
Originally Posted by Ray99 View Post
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.


Quote:
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
Attached Thumbnails
Click image for larger version

Name:	mcs_next_series_index_to_read_sql_query.jpg
Views:	58
Size:	507.7 KB
ID:	159538  
DaltonST is offline   Reply With Quote
Advert
Old 10-20-2017, 07:21 AM   #227
Tanjamuse
Guru
Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!
 
Posts: 954
Karma: 5258
Join Date: Jan 2014
Device: Samsung Galaxy Tab 3
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 )
and got this error:

Code:
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?
Tanjamuse is offline   Reply With Quote
Old 10-20-2017, 09:56 AM   #228
DaltonST
Deviser
DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!
 
DaltonST's Avatar
 
Posts: 1,062
Karma: 100454
Join Date: Aug 2013
Location: Texas
Device: 8" Win10 Tablet w/Calibre64
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
Attached Thumbnails
Click image for larger version

Name:	mcs_sql_query_tooltips_list_of_custom_columns.jpg
Views:	56
Size:	216.1 KB
ID:	159569  
DaltonST is offline   Reply With Quote
Old 10-20-2017, 10:00 AM   #229
Tanjamuse
Guru
Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!
 
Posts: 954
Karma: 5258
Join Date: Jan 2014
Device: Samsung Galaxy Tab 3
Ah. Thanks. How come it changed from the last time I did a SQL Query?
Tanjamuse is offline   Reply With Quote
Old 10-21-2017, 12:45 AM   #230
Ray99
Junior Member
Ray99 began at the beginning.
 
Posts: 5
Karma: 10
Join Date: Jun 2011
Device: Kindle
Thank you

This is superb. Many thanks.
Ray99 is offline   Reply With Quote
Advert
Old 10-23-2017, 10:08 AM   #231
Tanjamuse
Guru
Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!
 
Posts: 954
Karma: 5258
Join Date: Jan 2014
Device: Samsung Galaxy Tab 3
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.
Tanjamuse is offline   Reply With Quote
Old 10-23-2017, 12:04 PM   #232
DaltonST
Deviser
DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!
 
DaltonST's Avatar
 
Posts: 1,062
Karma: 100454
Join Date: Aug 2013
Location: Texas
Device: 8" Win10 Tablet w/Calibre64
Full-Text Query to Update Custom Column Example

Quote:
Originally Posted by Tanjamuse View Post
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
Attached Thumbnails
Click image for larger version

Name:	mcs_tanjamuse_answer_line_4_only.jpg
Views:	44
Size:	774.6 KB
ID:	159608  

Last edited by DaltonST; 10-23-2017 at 12:16 PM.
DaltonST is offline   Reply With Quote
Old 10-23-2017, 12:20 PM   #233
Tanjamuse
Guru
Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!
 
Posts: 954
Karma: 5258
Join Date: Jan 2014
Device: Samsung Galaxy Tab 3
Thanks. I'll have a look.
Tanjamuse is offline   Reply With Quote
Old 10-25-2017, 08:33 AM   #234
Tanjamuse
Guru
Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!
 
Posts: 954
Karma: 5258
Join Date: Jan 2014
Device: Samsung Galaxy Tab 3
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.
Tanjamuse is offline   Reply With Quote
Old 10-25-2017, 10:34 AM   #235
DaltonST
Deviser
DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!
 
DaltonST's Avatar
 
Posts: 1,062
Karma: 100454
Join Date: Aug 2013
Location: Texas
Device: 8" Win10 Tablet w/Calibre64
Quote:
Originally Posted by Tanjamuse View Post
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
Attached Thumbnails
Click image for larger version

Name:	mcs_booksize.jpg
Views:	35
Size:	89.1 KB
ID:	159638   Click image for larger version

Name:	mcs_booksize_2.jpg
Views:	35
Size:	75.6 KB
ID:	159639  
DaltonST is offline   Reply With Quote
Old 10-25-2017, 10:54 AM   #236
Tanjamuse
Guru
Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!
 
Posts: 954
Karma: 5258
Join Date: Jan 2014
Device: Samsung Galaxy Tab 3
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 )
Tanjamuse is offline   Reply With Quote
Old 10-25-2017, 12:14 PM   #237
DaltonST
Deviser
DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!
 
DaltonST's Avatar
 
Posts: 1,062
Karma: 100454
Join Date: Aug 2013
Location: Texas
Device: 8" Win10 Tablet w/Calibre64
Quote:
Originally Posted by Tanjamuse View Post
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 )
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
Attached Thumbnails
Click image for larger version

Name:	mcs_sqlquery_tooltips_composite.jpg
Views:	31
Size:	784.8 KB
ID:	159640  
DaltonST is offline   Reply With Quote
Old 10-25-2017, 12:18 PM   #238
Tanjamuse
Guru
Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!Tanjamuse , Klaatu Barada Niktu!
 
Posts: 954
Karma: 5258
Join Date: Jan 2014
Device: Samsung Galaxy Tab 3
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 )
and in another it's this:

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 )
When it's the same custom column I need to search?
Tanjamuse is offline   Reply With Quote
Old 10-25-2017, 12:23 PM   #239
DaltonST
Deviser
DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!DaltonST rocks like Gibraltar!
 
DaltonST's Avatar
 
Posts: 1,062
Karma: 100454
Join Date: Aug 2013
Location: Texas
Device: 8" Win10 Tablet w/Calibre64
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.
DaltonST is offline   Reply With Quote
Old 01-13-2018, 03:09 PM   #240
ilovejedd
hopeless n00b
ilovejedd ought to be getting tired of karma fortunes by now.ilovejedd ought to be getting tired of karma fortunes by now.ilovejedd ought to be getting tired of karma fortunes by now.ilovejedd ought to be getting tired of karma fortunes by now.ilovejedd ought to be getting tired of karma fortunes by now.ilovejedd ought to be getting tired of karma fortunes by now.ilovejedd ought to be getting tired of karma fortunes by now.ilovejedd ought to be getting tired of karma fortunes by now.ilovejedd ought to be getting tired of karma fortunes by now.ilovejedd ought to be getting tired of karma fortunes by now.ilovejedd ought to be getting tired of karma fortunes by now.
 
ilovejedd's Avatar
 
Posts: 2,635
Karma: 7263558
Join Date: Jan 2009
Location: in the middle of nowhere
Device: Paperwhite 3, iPad 9.7, 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:
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:
Quote:
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!
ilovejedd 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 293 02-19-2018 04:05 AM
[GUI Plugin] Walk Search History kiwidude Plugins 12 01-01-2017 10:31 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
[GUI Plugin] Clipboard Search kiwidude Plugins 9 08-12-2014 03:36 AM


All times are GMT -4. The time now is 08:34 PM.


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