MobileRead Forums - View Single Post

chaley · 02-04-2010, 05:56 AM

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.

02-04-2010, 05:56 AM	#6
chaley Grand Sorcerer Posts: 12,452 Karma: 8012886 Join Date: Jan 2010 Location: Notts, England Device: Kobo Libra 2	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.