Export books with tags with SQL

[Nicolas.Laurent]

Hello

And first of all thank you for this extraordinary software that is Calibre.

It's probably very simple, but my knowledge in SQL being limited, I'm looking for the query to export a book with all its tags.

Thanks in advance.

[BetterRed]

Not sure why you think you need to use SQL to save a book.

The Preferences->Save to Disk settings are used to define what gets saved, check the Update metadata in saved copies to put the tags etc into the saved format file(s).

Then use one of the Save to Disk options to save the book to the selected location.

BR

[ownedbycats]

Could they be using calibredb rather than the GUI? I don't mess around much with the cmdline tools myself, so I'm not sure.

[theducks]

Calibre has 2 main parts per book:
1) the DB entry (actually many pieces: as it is a relational DB)
2) The books format(s) just stored in the file system
The DB entry contains a PATH to the book as well as lots of metadata.

Ownedbycats is referring to the command line tools, that can be used to perform tasks.(see the online help) No need to know SQL as all the tools do the needed lifting (without risking library integrity).

[Nicolas.Laurent]

Originally Posted by Nicolas.Laurent

Hello

And first of all thank you for this extraordinary software that is Calibre.

It's probably very simple, but my knowledge in SQL being limited, I'm looking for the query to export a book with all its tags.

Thanks in advance.

Sorry, I wasn't clear.
What I want is to export the book's metadata, not save it. And I want to do it with an SQL query on the db database because it takes a lot of time to do it from Calibre considering the size of my library (more than 100,000 books).

[BetterRed]

Not sure if using SQL against the database file would be any faster than exporting a CSV catalog from the calibre GUI (you may need to add the Create Catalog tool to a Toolbar or menu), or via the calibredb list command ==>> The Command Line Interface

The database is loaded into memory when calibre is running.

However if you're determined to use SQL direct, I would suggest you copy the metadata.db file at the root of the library, and use something like DB Browser for SQLite to do queries against it to export CSV, HTML etc. You'll need to spend a few minutes examining the schema but its fairly straightforward.

BR

[ownedbycats]

Originally Posted by BetterRed

However if you're determined to use SQL direct, I would suggest you copy the metadata.db file at the root of the library, and use something like DB Browser for SQLite to do queries against it to export CSV, HTML etc. You'll need to spend a few minutes examining the schema but its fairly straightforward.

BR

My software of choice is SQLiteStudio.

[Nicolas.Laurent]

Originally Posted by ownedbycats

My software of choice is SQLiteStudio.

I also use SQLiteStudio

And my question is what is the query to export ALL the tags. I use this one and it doesn't work.

SELECT id,
author_sort,
title,
(SELECT name FROM publishers WHERE publishers.id IN (SELECT publisher from books_publishers_link WHERE book=books.id)) publisher,
(SELECT format FROM data WHERE data.book=books.id) formats,
path,
(SELECT name FROM tags WHERE tags.id IN (SELECT tag from books_tags_link WHERE book=books.id)) tags
FROM books

[chaley]

Originally Posted by Nicolas.Laurent

I also use SQLiteStudio

And my question is what is the query to export ALL the tags. I use this one and it doesn't work.

SELECT id,
author_sort,
title,
(SELECT name FROM publishers WHERE publishers.id IN (SELECT publisher from books_publishers_link WHERE book=books.id)) publisher,
(SELECT format FROM data WHERE data.book=books.id) formats,
path,
(SELECT name FROM tags WHERE tags.id IN (SELECT tag from books_tags_link WHERE book=books.id)) tags
FROM books

I really don't see why you want to use SQL unless simply as a learning experience. A CSV catalog gives you the same information, except the path, with no hassle at all. Example attached.

[chaley]

BTW, the reason your SQL doesn't work is that some of the subqueries return multiple rows. You only see the first one. To see all of them you need to use group_concat(), like this:

SELECT
    id,
    author_sort,
    title,
    (SELECT name FROM publishers WHERE publishers.id IN (SELECT publisher from books_publishers_link WHERE book=books.id)) publisher,
    (SELECT group_concat(format) FROM data WHERE data.book=books.id) formats,
    path,
    (SELECT group_concat(name) FROM tags WHERE tags.id IN (SELECT tag from books_tags_link WHERE book=books.id)) tags
FROM books

[Nicolas.Laurent]

Originally Posted by chaley

BTW, the reason your SQL doesn't work is that some of the subqueries return multiple rows. You only see the first one. To see all of them you need to use group_concat(), like this:

SELECT
    id,
    author_sort,
    title,
    (SELECT name FROM publishers WHERE publishers.id IN (SELECT publisher from books_publishers_link WHERE book=books.id)) publisher,
    (SELECT group_concat(format) FROM data WHERE data.book=books.id) formats,
    path,
    (SELECT group_concat(name) FROM tags WHERE tags.id IN (SELECT tag from books_tags_link WHERE book=books.id)) tags
FROM books

you are a genius (which you probably already knew)!
The query works perfectly
Thanks again for your help.

[theducks]

Originally Posted by Nicolas.Laurent

you are a genius (which you probably already knew)!
The query works perfectly
Thanks again for your help.

Charles is one of the longer time contributing developers.
https://calibre-ebook.com/about#contributors
(and yes!, he is brilliant)