how to query from Calibre database?

Question

as you know calibre has a database called metadata.db and made of SQLite, I want to query from all books with this columns :

bookId  Title   Date    Size    Rating  Tags    Publisher   PublishedDate   Author  Cover           
------  -----   ----    ----    ------  ----    ---------   -------------   ------  -----

but, that database contains this tables :

Books Table
-----------
Name
id
title
sort
timestamp
pubdate
series_index
author_sort
isbn
lccn
path
flags
uuid
has_cover
last_modified


Authors Table
-------------
Name
id
name
sort
link


Publishers Table
----------------
Name
id
name
sort

there is no relationship between them, how can I get my query?

found this in the source code (python), there's little snippets of SQL that might aid you: http://bazaar.launchpad.net/~kovid/calibre/trunk/view/head:/src/calibre/db/tables.py — booyaa, Feb 06 '13 at 09:31

score 7 · Accepted Answer · answered Feb 15 '13 at 17:52

SELECT id, title,
               (SELECT name FROM books_authors_link AS bal JOIN authors ON(author = authors.id) WHERE book = books.id) authors,
               (SELECT name FROM publishers WHERE publishers.id IN (SELECT publisher from books_publishers_link WHERE book=books.id)) publisher,
               (SELECT rating FROM ratings WHERE ratings.id IN (SELECT rating from books_ratings_link WHERE book=books.id)) rating,
               (SELECT MAX(uncompressed_size) FROM data WHERE book=books.id) size,
               (SELECT name FROM tags WHERE tags.id IN (SELECT tag from books_tags_link WHERE book=books.id)) tags,
               (SELECT format FROM data WHERE data.book=books.id) formats,
               isbn,
               path,
               pubdate
        FROM books

how to query from Calibre database?

1 Answers1