Calibre: Database question

edembowski · 06-21-2008, 09:13 AM

I've found the database files, and I've been able to open it using the sqlite odbc driver. The database seems to be pretty large relatively speaking. As a test, I've used the 'copy book' tool in the calibre gui to compare sizes, and the db is just a little bigger than teh size of all the books. It looks like the 'data' table also holds the book as well. Is this the case?

I think it's very interesting if the actual book is stored in the database, then the source files would not be as necessary to keep (other than for backups). I'm also looking at the command line 'calibredb'. It seems that it's become functional in this release, and that it will do the db import as well. This would be very useful for preparing bulk conversion scripts, as well as timed feed downloads.

Can anyone confirm if the actual book is stored in the db?

- Ed

astrodad · 06-21-2008, 10:55 AM

Ed,

Yes I do believe the book is stored in the db. I have to look at the tables, but I've seen them there. If you store a LIT, and then convert to LRF, it will store both.

kovidgoyal · 06-21-2008, 03:44 PM

yeah books are stored in the db. I'm actually thinking of changing that to books being stored in the filesystem (but in calibre's own directory the original sources will not be touched) as I think this will greatly help network performance.

edembowski · 06-21-2008, 05:41 PM

OK, if you store a reference to the file instead of the file, that's great. I like the idea of copying the file to a configurable storage location. I'm thinking of something like itunes, where you have the option of having itunes manage the directory structure & files for your music collection. I think that lets backup/restore work much more cleanly.

For the current version, there's no clean way of extracting the book from the command line, is there? That would have to be done directly in python, right? I don't think it would be that hard to work something out from the existing code to do that.

- Ed

kovidgoyal · 06-21-2008, 06:47 PM

Yeah adding an export command to calibredb would be like 10 lines of code

JeffElkins · 06-21-2008, 07:26 PM

Quote:

Originally Posted by kovidgoyal

yeah books are stored in the db. I'm actually thinking of changing that to books being stored in the filesystem (but in calibre's own directory the original sources will not be touched) as I think this will greatly help network performance.

I think you'll be very pleased with overall speedup if you do this. I regularly run another app (XBMC for Linux) that has a large 2000+ record sqlite database. Speedwise it flies.

kovidgoyal · 06-21-2008, 07:42 PM

Actually it's already done, I'm just not ready to merge the code into the publically released code base. I'll probably put it off until post 0.5.0

pilotbob · 06-21-2008, 09:59 PM

Quote:

Originally Posted by JeffElkins

I think you'll be very pleased with overall speedup if you do this. I regularly run another app (XBMC for Linux) that has a large 2000+ record sqlite database. Speedwise it flies.

I'm confused... he already stores stuff in a database. He is thinking about changing it to NOT be in a database.

You are saying how great that would be because this other app uses a database and it is fast.

BOb

kovidgoyal · 06-21-2008, 10:34 PM

The idea is that I store only references to the files in the database. What that means is that the database will be so small that it will be loaded entirely into memory. So read only operations (like searching or sorting the list of books) will be very fast.

JeffElkins · 06-21-2008, 11:05 PM

Quote:

Originally Posted by kovidgoyal

The idea is that I store only references to the files in the database. What that means is that the database will be so small that it will be loaded entirely into memory. So read only operations (like searching or sorting the list of books) will be very fast.

Exactly. If my 2000 record XBMC database had to store the contents of my 2TB video collection it would obviously be ridiculously unworkable.

Kovid, personally speaking, I'm very jazzed by your announcement here. I have a collection of about 3000+ .mobi/oeb files and this will make it practical to catalog my entire collection. I for one don't care a whit about abandoning my current smallish calibre database and starting afresh. I wouldn't even worry about any lib conversion utilities.

If you need anyone to beta-test the new lib I'd be happy to do it and re-do it if required.

kovidgoyal · 06-21-2008, 11:45 PM

when I'm ready, I'll make an announcement calling for beta testers. But since you're on linux (the platform that receives the most testing from me, things should go smoothly)

astrodad · 06-22-2008, 01:15 PM

A question about this. What if I want to access the database and books remotely? Will the file be relative to some location? I would probably put all of the files on an SSH mounted drive, like I do with the db now, but they have different mount points on Windows and OS/X. If the preferences are stored in the database, that might mess things up for me.

edembowski · 06-22-2008, 01:40 PM

I'm importing a lot of books in lit format, and the data quality is fairly low. I'd like to be able to edit data more easily, but I'm nervous about messing up the db.

For example, in the 'books' table, it's not clear to me what the 'sort' column is used for, and hos this is different from the 'title' column. The 'authors' table has a 'name' and a 'sort' column, but 'sort' appears to be unused.

The main fields I'd like to work with are isbn and series. From there, I'm fine using the gui to fetch the rest of the data using isbndb. I'm finding that I can't edit the data easily in Access, probably due to limitations in the odbc driver.

Any thoughts?

- Ed

kovidgoyal · 06-22-2008, 05:18 PM

@jplumey

The database and ebooks will all be stored in the same directory, the ebooks organized in sub directories by author. You have to keep both database and ebook files on the same server.

@edembowski
sort is basically an automatically generated version of title that ignores leading prepositions. You shouldn't have to worry about it, it should be automatically updated when you change title.

Why dont you use the bulk metadata dialog to edit series and the metadata dialog to edit ISBN?

pilotbob · 06-22-2008, 05:23 PM

Quote:

Originally Posted by kovidgoyal

@edembowski
sort is basically an automatically generated version of title that ignores leading prepositions. You shouldn't have to worry about it, it should be automatically updated when you change title.

I think he is talking about the author sort field. I have figured out that field is used when you save the books to disk as the author's folder name. Any other places it is used?

BOb

06-21-2008, 09:13 AM	#1
edembowski Zealot Posts: 138 Karma: 372 Join Date: Apr 2008 Location: New York, NY Device: Sony PRS-600, Nook Color, iPad	Calibre: Database question I've found the database files, and I've been able to open it using the sqlite odbc driver. The database seems to be pretty large relatively speaking. As a test, I've used the 'copy book' tool in the calibre gui to compare sizes, and the db is just a little bigger than teh size of all the books. It looks like the 'data' table also holds the book as well. Is this the case? I think it's very interesting if the actual book is stored in the database, then the source files would not be as necessary to keep (other than for backups). I'm also looking at the command line 'calibredb'. It seems that it's become functional in this release, and that it will do the db import as well. This would be very useful for preparing bulk conversion scripts, as well as timed feed downloads. Can anyone confirm if the actual book is stored in the db? - Ed Last edited by edembowski; 06-21-2008 at 09:34 AM.

06-22-2008, 01:40 PM	#13
edembowski Zealot Posts: 138 Karma: 372 Join Date: Apr 2008 Location: New York, NY Device: Sony PRS-600, Nook Color, iPad	More DB questions I'm importing a lot of books in lit format, and the data quality is fairly low. I'd like to be able to edit data more easily, but I'm nervous about messing up the db. For example, in the 'books' table, it's not clear to me what the 'sort' column is used for, and hos this is different from the 'title' column. The 'authors' table has a 'name' and a 'sort' column, but 'sort' appears to be unused. The main fields I'd like to work with are isbn and series. From there, I'm fine using the gui to fetch the rest of the data using isbndb. I'm finding that I can't edit the data easily in Access, probably due to limitations in the odbc driver. Any thoughts? - Ed

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Calibre Database Creation	RockstarPenguin	Calibre	0	07-08-2010 12:17 PM
Calibre 7.7 cant open database	Patricia1066	Calibre	17	07-05-2010 10:23 AM
If Calibre can't find the database...	guyanonymous	Calibre	7	02-27-2010 02:48 PM
Reference Database in Calibre?	nnxbtz	Calibre	5	09-27-2009 06:16 PM
Possible to get tags from the calibre database?	GRiker	Calibre	4	05-04-2009 02:30 PM

06-21-2008, 10:55 AM	#2
astrodad Guru Posts: 962 Karma: 568242 Join Date: Dec 2007 Device: Kindle Oasis 3, Kindle Paperwhite 1, iPad, iPhone	Ed, Yes I do believe the book is stored in the db. I have to look at the tables, but I've seen them there. If you store a LIT, and then convert to LRF, it will store both.

06-21-2008, 03:44 PM	#3
kovidgoyal creator of calibre Posts: 43,844 Karma: 22666666 Join Date: Oct 2006 Location: Mumbai, India Device: Various	yeah books are stored in the db. I'm actually thinking of changing that to books being stored in the filesystem (but in calibre's own directory the original sources will not be touched) as I think this will greatly help network performance.

06-21-2008, 05:41 PM	#4
edembowski Zealot Posts: 138 Karma: 372 Join Date: Apr 2008 Location: New York, NY Device: Sony PRS-600, Nook Color, iPad	OK, if you store a reference to the file instead of the file, that's great. I like the idea of copying the file to a configurable storage location. I'm thinking of something like itunes, where you have the option of having itunes manage the directory structure & files for your music collection. I think that lets backup/restore work much more cleanly. For the current version, there's no clean way of extracting the book from the command line, is there? That would have to be done directly in python, right? I don't think it would be that hard to work something out from the existing code to do that. - Ed

06-21-2008, 06:47 PM	#5
kovidgoyal creator of calibre Posts: 43,844 Karma: 22666666 Join Date: Oct 2006 Location: Mumbai, India Device: Various	Yeah adding an export command to calibredb would be like 10 lines of code

06-21-2008, 07:42 PM	#7
kovidgoyal creator of calibre Posts: 43,844 Karma: 22666666 Join Date: Oct 2006 Location: Mumbai, India Device: Various	Actually it's already done, I'm just not ready to merge the code into the publically released code base. I'll probably put it off until post 0.5.0

06-21-2008, 10:34 PM	#9
kovidgoyal creator of calibre Posts: 43,844 Karma: 22666666 Join Date: Oct 2006 Location: Mumbai, India Device: Various	The idea is that I store only references to the files in the database. What that means is that the database will be so small that it will be loaded entirely into memory. So read only operations (like searching or sorting the list of books) will be very fast.

06-21-2008, 11:45 PM	#11
kovidgoyal creator of calibre Posts: 43,844 Karma: 22666666 Join Date: Oct 2006 Location: Mumbai, India Device: Various	when I'm ready, I'll make an announcement calling for beta testers. But since you're on linux (the platform that receives the most testing from me, things should go smoothly)

06-22-2008, 01:15 PM	#12
astrodad Guru Posts: 962 Karma: 568242 Join Date: Dec 2007 Device: Kindle Oasis 3, Kindle Paperwhite 1, iPad, iPhone	A question about this. What if I want to access the database and books remotely? Will the file be relative to some location? I would probably put all of the files on an SSH mounted drive, like I do with the db now, but they have different mount points on Windows and OS/X. If the preferences are stored in the database, that might mess things up for me.

06-22-2008, 05:18 PM	#14
kovidgoyal creator of calibre Posts: 43,844 Karma: 22666666 Join Date: Oct 2006 Location: Mumbai, India Device: Various	@jplumey The database and ebooks will all be stored in the same directory, the ebooks organized in sub directories by author. You have to keep both database and ebook files on the same server. @edembowski sort is basically an automatically generated version of title that ignores leading prepositions. You shouldn't have to worry about it, it should be automatically updated when you change title. Why dont you use the bulk metadata dialog to edit series and the metadata dialog to edit ISBN?

Advert

Advert