manually editing database

[teje]

Hello

I have just installed Calibre and it seems to be the software i need. The problem is that I did a bunch of cataloguing in another software.

The question is: How can i open the Caliber database file as a spreadsheet, copy the information in the right fields (author, title, path) and save it again so that caliber recognizes the information next time i open it?

Many thanks

[itimpi]

You cannot access the Calibre database in that way without constructing some very complicated SQL queries.

[theducks]

Originally Posted by teje

Hello

I have just installed Calibre and it seems to be the software i need. The problem is that I did a bunch of cataloguing in another software.

The question is: How can i open the Caliber database file as a spreadsheet, copy the information in the right fields (author, title, path) and save it again so that caliber recognizes the information next time i open it?

Many thanks

Calibre is more than just a DB.

It has its own file management (adds the DB ID to the file folder it keeps all the versions in).

Don't muck with the file names or their individual placement if you still want things to work afterwards

[alexxxm]

If I may insert myself in this thread,
I wondered about the same things for some time...

Given the time I spent inserting & manually editing the records for my books, I'd like the result to stay alive&working as long as possible.
Of course I wish Kovid a long and productive life, but what happens if he for some reason stops developing calibre (or is kidnapped by aliens, or ...)?
That's the reason we like open formats - as Calibre is, luckily.

Therefore I am very interested in any info regarding <alternative> ways to delve into its structure. And since this structure is documented, any problems will surely be due to the operator mishandling it - backups are there for a reason...

So, any info for hacking the database is welcome (I saw some people in other threads mention SQL queries, time ago), and if the thread can be made sticky, all the better


alessandro

[itimpi]

As somebody mentioned it is not just a database issue. The names of the files and folders on disk in the calibre library area that holds the ebooks themselves has to have names that match entries in the database exactly. Any mismatch and the books will not be there as far as Calibre is concerned.

[chaley]

I have used SQL successfully to browse the calibre DB. Starson17 suggested using SQLiteSpy, and it works well for browsing.

Modifying is a completely different proposition. Not only does one have the integrity issues raised by itimpi and theducks, but there are problems with triggers. Calibre uses SQLite triggers to handle several complex conditions; one is computing title_sort. Several of the triggers make reference to python functions in calibre, which means that SQL update queries outside of calibre will fail. For example, it is very difficult to modify a row in the book table because the triggers fail.

My advice: browse the database to understand how calibre stores its information, but don't plan on doing any modifications outside of calibre. The probability of an update query not working or breaking some calibre integrity constraint is very high.

[Starson17]

Originally Posted by chaley

I have used SQL successfully to browse the calibre DB. Starson17 suggested using SQLiteSpy, and it works well for browsing.

Personally, I only use SQLiteSpy to get a list of all duplicate books, and for really complicated regex queries, not to modify the database. However, it is possible to modify it, despite the triggers. If you really want to do this, you can save the triggers, drop them, make your db changes and recreate them.

The triggers for each table can be seen by selecting the trigger (at the left), right click and choose the "Show CREATE" option. Then you can save that CREATE as an SQL. To remove it, choose DROP instead of Show CREATE. After dropping the triggers, normal SQL commands will work.

When done making database changes, you would need to add the dropped trigger(s) back by running the saved SQL CREATE for each trigger. Changes to author or title will break your database without corresponding changes in the file structure. Changes in any table will need to replicate the functions in the triggers, and changes in the book table are particularly tricky (note the uuid related trigger).

If you try this, you are playing with fire. If you break it, you get to keep both halves (or even more pieces), so make lots of backups and test very, very, carefully. Running the database integrity before and after would also be advisable.

[kovidgoyal]

calibre provides a full programmatic interface to the database via the calibre-debug command

calibre-debug -c "from calibrelibrary.database2 import LibraryDatabase2; db = LibraryDatabase2('/path/to/library/folder'); db.execute('some sql statement');"

You can also use

calibre-debug -e path_to_script.py

to execute long scripts

[Starson17]

Originally Posted by kovidgoyal

calibre provides a full programmatic interface to the database via the calibre-debug command ...

Am I correct in thinking that this simply handles the triggers, but nothing else?

There are a lot of interrelated fields that include the author or title field, from the path to the books to various sort fields.

IOW, would you need to separately fix the directory/filename in the library if you changed the title or author in the database? Would you need to update the path field if you changed the author, or could you leave the original path and not change the directory/filename in the library?

This is mainly just curiosity, but the one feature that I've been tempted to deal with externally is merging different records for different formats of the same book into a single record. I see an open feature enhancement request for that, so perhaps it's going to be implemented.

[kovidgoyal]

LibraryDatabse2 has a full interface you can use to do these kinds of tasks.