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

Go Back   MobileRead Forums > E-Book Software > Calibre

Notices

Reply
 
Thread Tools Search this Thread
Old 05-23-2009, 08:41 AM   #1
iain_benson
Connoisseur
iain_benson began at the beginning.
 
iain_benson's Avatar
 
Posts: 58
Karma: 37
Join Date: Oct 2008
Device: PRS-600
SQL Queries

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.
Code:
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!
iain_benson is offline   Reply With Quote
Old 05-23-2009, 11:47 AM   #2
kovidgoyal
creator of calibre
kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.
 
kovidgoyal's Avatar
 
Posts: 26,154
Karma: 5381911
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
Simple filtering? calibre's search field allows full boolean logic in searches

author:whatever and tag:whatever
kovidgoyal is offline   Reply With Quote
Old 05-23-2009, 09:57 PM   #3
Colaveare
Member
Colaveare began at the beginning.
 
Posts: 22
Karma: 10
Join Date: Apr 2009
Device: iphone
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"
Colaveare is offline   Reply With Quote
Old 05-24-2009, 04:36 AM   #4
iain_benson
Connoisseur
iain_benson began at the beginning.
 
iain_benson's Avatar
 
Posts: 58
Karma: 37
Join Date: Oct 2008
Device: PRS-600
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.
iain_benson is offline   Reply With Quote
Old 05-24-2009, 01:54 PM   #5
kovidgoyal
creator of calibre
kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.
 
kovidgoyal's Avatar
 
Posts: 26,154
Karma: 5381911
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
If I understand you correctly, you want books by author x that have tag y. Just use the following search query

Code:
author:x and tag:y
There is no "main search term" in the above query
kovidgoyal is offline   Reply With Quote
Old 05-24-2009, 02:03 PM   #6
Jellby
frumious Bandersnatch
Jellby ought to be getting tired of karma fortunes by now.Jellby ought to be getting tired of karma fortunes by now.Jellby ought to be getting tired of karma fortunes by now.Jellby ought to be getting tired of karma fortunes by now.Jellby ought to be getting tired of karma fortunes by now.Jellby ought to be getting tired of karma fortunes by now.Jellby ought to be getting tired of karma fortunes by now.Jellby ought to be getting tired of karma fortunes by now.Jellby ought to be getting tired of karma fortunes by now.Jellby ought to be getting tired of karma fortunes by now.Jellby ought to be getting tired of karma fortunes by now.
 
Jellby's Avatar
 
Posts: 6,199
Karma: 4800739
Join Date: Jan 2008
Location: Spaniard in Sweden
Device: Cybook Orizon, Kobo Aura
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.
Jellby is offline   Reply With Quote
Old 05-24-2009, 05:20 PM   #7
kovidgoyal
creator of calibre
kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.
 
kovidgoyal's Avatar
 
Posts: 26,154
Karma: 5381911
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
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.
kovidgoyal is offline   Reply With Quote
Old 06-04-2009, 04:25 PM   #8
iain_benson
Connoisseur
iain_benson began at the beginning.
 
iain_benson's Avatar
 
Posts: 58
Karma: 37
Join Date: Oct 2008
Device: PRS-600
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.
iain_benson is offline   Reply With Quote
Old 06-04-2009, 04:32 PM   #9
kovidgoyal
creator of calibre
kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.
 
kovidgoyal's Avatar
 
Posts: 26,154
Karma: 5381911
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
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
kovidgoyal is offline   Reply With Quote
Old 06-04-2009, 06:08 PM   #10
iain_benson
Connoisseur
iain_benson began at the beginning.
 
iain_benson's Avatar
 
Posts: 58
Karma: 37
Join Date: Oct 2008
Device: PRS-600
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 "

Code:
    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

Code:
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
iain_benson is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using SQL against Calibre database jjansen Calibre 3 03-29-2010 10:14 AM
queries @ ROBERT N ALL asdx Astak EZReader 8 01-15-2010 05:16 PM
SD - memory stick queries tarq Sony Reader 6 06-29-2009 03:33 PM


All times are GMT -4. The time now is 03:14 AM.


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