SQL Queries

[iain_benson]

Because of the way I import and tag my books, I occasionally want to go through the database and find the authors with a certain tag on any of their books, and then then find all books by those authors. The simple filtering facility provided by calibre don't allow for such things, but it is (relatively) straightforward in SQL. I came up with the following query which gives me what I want, but it would be nice to be able to enter it into calibre somehow so that I can interact directly with it's results.

SELECT b.title,a.name FROM(
 SELECT DISTINCT ba.author AS author
 FROM tags AS t JOIN books_tags_link AS bt ON t.id=bt.tag
  JOIN books AS b ON bt.book=b.id
  JOIN books_authors_link AS ba ON b.id=ba.book
 WHERE t.name="my tag"
 ) AS x
 JOIN books_authors_link AS ba ON x.author=ba.author
 JOIN books AS b ON ba.book=b.id
 JOIN authors AS a ON ba.author=a.id
ORDER BY b.author_sort

Possibly one way to do it would be to provide an SQL entry box which provides a fixed 'SELECT DISTINCT books.id FROM' at the start, allowing you to then enter any arbitrary query, and thus return a list of book IDs, which can then be used to populate the table as usual.
I know it's a fairly specialized desire, but I suspect there are a few people around who would use it if it were available!

[kovidgoyal]

Simple filtering? calibre's search field allows full boolean logic in searches

author:whatever and tag:whatever

[Colaveare]

Just click the "Click to Browse Books by Tags" Icon on the lower right side of calibre.

You can pretty much add as many different search fields to calibre search logic.

click once (green) on a selection once to search for it.
click twice (red) to exclude it.

Rince and repeat with as many different selection at the same time.
Ex:
author:"Agatha Christie" and not series:"Miss Marple" and not format:"HTML" and tag:"Murder" and not tag:"Supernatural"

[iain_benson]

Boolean logic is quite a simple filter, it doesn't allow complex ones as in my first example.
Given
Book1,Author1,tag1
Book2,Author1,no tags
Book3,Author2,tag1
Book4,Author3,no tags

My query, using tag1 as the main search term, would pull out books 1, 2 & 3. With the current search/filter system I would have to filter on tag1, make a list of all the authors it returned, then clear the filter and re-filter on the complete list of authors.

[kovidgoyal]

If I understand you correctly, you want books by author x that have tag y. Just use the following search query

author:x and tag:y

There is no "main search term" in the above query

[Jellby]

No, he wants all books by all authors who have at least one book with tag x (I think). That is:

1. Identify books with tag x.
2. Get a list of the authors of these books.
3. Get all books by those authors.

[kovidgoyal]

Ah, that's certainly not possible with boolean logic alone. As for adding the ability to inject arbitrary SQL into calibre search queries. That's not going to happen because eventually calibre may have a non sql based stirage engibe as well as the current sqlite based one.

[iain_benson]

Ah, I see, now I've looked at the code I see that even if SQL was there to stay, it still wouldn't be very nice

It looks like it only really queries the SQL database once to get a big table of all the books, and then relies on python itself to do the searching and filtering ... I'd assumed that all the queries would be SQL.

Just playing around with database2.py and calibre-debug --update-module and extending the "SELECT * from meta" in the refresh function I've managed to make it do what I wanted, but as this is changing the master query the reported number of books in the database drops to only the number returned, and I could possibly damage my database by editing anything in this state. Looking through, though, it is probably safe as all the updates are done directly into the SQL and read back in.

[kovidgoyal]

SQL isn't quite flexible enough for some of the filtering needed (or atleast I'm not good enough with it). Also I've found that using SQL queries can be slow, especially since people often put the database on network shares.

I think (though I'm not a 100%) that you should be fine using a modified meta view and editing things

[iain_benson]

Thanks for the reassurance, but I decided I didn't like doing it that way as changing the filter requires a redeploy of the py file each time.

I did a bit more hacking and found an even better way which allowed me to enter sql directly into the search field. I changed search(self, query) in database2 to run an sql query (in the same way refresh does) if the filter string begins with "JOIN "

    def search(self, query):
        if not query or not query.strip():
            self._map_filtered = list(self._map)
            return
	if query.find("JOIN ") != -1:
            temp = self._db.conn.get('SELECT DISTINCT meta.* FROM meta ' + query)
            self._data_filtered = list(itertools.repeat(None, temp[-1][0]+2)) if temp else []
            for r in temp:
        	self._data_filtered[r[0]] = r
            self._map_filtered = [i[0] for i in self._data_filtered if i is not None]
            self._map_filtered = [id for id in self._map if id in self._map_filtered]
	    return
        matches = sorted(self.parse(query))
        self._map_filtered = [id for id in self._map if id in matches]

I also changed refresh to store db in self._db so I can get at it.

I can then just cut and paste a query such as the following to get what I want

JOIN books_authors_link ON books_authors_link.book=meta.id
JOIN (
 SELECT DISTINCT books_authors_link.author AS author_filter
  FROM tags
   JOIN books_tags_link ON tags.id=books_tags_link.tag
   JOIN books ON books_tags_link.book=books.id
   JOIN books_authors_link ON books.id=books_authors_link.book
  WHERE tags.name="my tag"
) ON author_filter=books_authors_link.author

I suspect the last two lines of code in my if JOIN bit - the copy from data into map and the sort - can probably be combined into one but as I have never programmed in python (just C, C++, Ada, Pascal, Java, Perl, bash ... ahem ) I've just stuck to simple cut&paste editing.

P.S. I'm just posting this for public interest ... it's not a feature request as I suspect most people wouldn't want / need it, I'm just awkward