Database Documentation

mfaine · 01-06-2011, 03:12 PM

Is the calibre library structure documented somewhere? I'm looking to do direct sqllite queries against the a library but it is slow going doing .schema on every table and writing down what columns are contained and how they relate. Surely there is a faster way? An ERD would also be great.

My goal is a single select statement that will retrieve values for specific columns (including some custom columns) for all the books in the library.

kovidgoyal · 01-06-2011, 03:46 PM

Nope, you have to read the schema, or the calibre source. custom column tables all fall into one or two very similar categories depending on the datatype.

You do know you can dump the entire schema at once, right?

mfaine · 01-06-2011, 04:29 PM

Quote:

Originally Posted by kovidgoyal

Nope, you have to read the schema, or the calibre source. custom column tables all fall into one or two very similar categories depending on the datatype.

You do know you can dump the entire schema at once, right?

Yes, and I'm getting closer now that I've installed a gui browser to view the tables. If anything the exercise will improve my sql skills.

Thanks

mfaine · 01-07-2011, 01:26 PM

I promise I'll stop asking questions (in this thread) about the database but I'm curious as to why author_sort is a field in books and sort is also a field in authors?

itimpi · 01-07-2011, 02:01 PM

A combination of legacy and practicality.

The one in the books has always been there.

The one in the authors table is newer, and is the default value applied to new books. However you can over-ride the value at the book level if you want to. This tends to be of most use in multiple author books where you want a particular author to be the primary sort key.

That is one of the reasons the Edit Metadata dialog highlights the author-sort field in pink when the value on the books table is different to the value on the authors table. Just a hint that you should check that you want the book sorted by the value currently displayed.

01-06-2011, 03:12 PM	#1
mfaine Member Retired Posts: 36 Karma: 10 Join Date: Oct 2009 Device: nook	Database Documentation Is the calibre library structure documented somewhere? I'm looking to do direct sqllite queries against the a library but it is slow going doing .schema on every table and writing down what columns are contained and how they relate. Surely there is a faster way? An ERD would also be great. My goal is a single select statement that will retrieve values for specific columns (including some custom columns) for all the books in the library. Last edited by mfaine; 01-06-2011 at 03:14 PM.

01-07-2011, 02:01 PM	#5
itimpi Wizard Posts: 4,552 Karma: 950151 Join Date: Nov 2008 Device: Sony PRS-950, iphone/ipad (Marvin/iBooks/QuickReader)	A combination of legacy and practicality. The one in the books has always been there. The one in the authors table is newer, and is the default value applied to new books. However you can over-ride the value at the book level if you want to. This tends to be of most use in multiple author books where you want a particular author to be the primary sort key. That is one of the reasons the Edit Metadata dialog highlights the author-sort field in pink when the value on the books table is different to the value on the authors table. Just a hint that you should check that you want the book sorted by the value currently displayed. Last edited by itimpi; 01-07-2011 at 02:03 PM.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Documentation for Sigil	Valloric	Sigil	15	11-12-2010 06:37 PM
Server Documentation?	Joe McPlumber	Calibre	4	07-05-2010 11:34 PM
Technical Documentation	Gremlin	HanLin eBook	4	07-16-2009 02:24 AM
Wiki documentation	mrdini	OpenInkpot	1	10-14-2008 09:50 AM
More Detailed Documentation?	TedPark	Sony Reader Dev Corner	3	06-10-2008 09:06 PM

01-06-2011, 03:46 PM	#2
kovidgoyal creator of calibre Posts: 43,826 Karma: 22666666 Join Date: Oct 2006 Location: Mumbai, India Device: Various	Nope, you have to read the schema, or the calibre source. custom column tables all fall into one or two very similar categories depending on the datatype. You do know you can dump the entire schema at once, right?

01-07-2011, 01:26 PM	#4
mfaine Member Retired Posts: 36 Karma: 10 Join Date: Oct 2009 Device: nook	I promise I'll stop asking questions (in this thread) about the database but I'm curious as to why author_sort is a field in books and sort is also a field in authors?