Register Guidelines E-Books Search Today's Posts Mark Forums Read

Go Back   MobileRead Forums > E-Book Software > Calibre

Notices

Reply
 
Thread Tools Search this Thread
Old 02-03-2010, 02:51 PM   #1
teje
Junior Member
teje began at the beginning.
 
Posts: 1
Karma: 10
Join Date: Feb 2010
Device: Sony touch
manually editing database

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
teje is offline   Reply With Quote
Old 02-03-2010, 05:57 PM   #2
itimpi
Wizard
itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.
 
Posts: 4,048
Karma: 777825
Join Date: Nov 2008
Device: Sony PRS-950, iphone/ipad (Marvin/iBooks/QuickReader)
You cannot access the Calibre database in that way without constructing some very complicated SQL queries.
itimpi is offline   Reply With Quote
 
Enthusiast
Old 02-03-2010, 06:08 PM   #3
theducks
Grand Sorcerer
theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.theducks ought to be getting tired of karma fortunes by now.
 
theducks's Avatar
 
Posts: 14,520
Karma: 5567081
Join Date: Aug 2009
Location: The (original) Silicon Valley, USA
Device: Galaxy Tab 2, Astak Pocket Pro, K4NT
Quote:
Originally Posted by teje View Post
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
theducks is online now   Reply With Quote
Old 02-04-2010, 04:23 AM   #4
alexxxm
Addict
alexxxm has a complete set of Star Wars action figures.alexxxm has a complete set of Star Wars action figures.alexxxm has a complete set of Star Wars action figures.alexxxm has a complete set of Star Wars action figures.
 
Posts: 205
Karma: 356
Join Date: Aug 2007
Device: Rocket; Hiebook; N700; Sony 505; Kindle DX ...
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
alexxxm is offline   Reply With Quote
Old 02-04-2010, 04:32 AM   #5
itimpi
Wizard
itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.itimpi ought to be getting tired of karma fortunes by now.
 
Posts: 4,048
Karma: 777825
Join Date: Nov 2008
Device: Sony PRS-950, iphone/ipad (Marvin/iBooks/QuickReader)
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.
itimpi is offline   Reply With Quote
Old 02-04-2010, 05:56 AM   #6
chaley
"chaley", not "charley"
chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.
 
Posts: 5,286
Karma: 821512
Join Date: Jan 2010
Location: France
Device: Many android devices
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.
chaley is offline   Reply With Quote
Old 02-04-2010, 11:33 AM   #7
Starson17
Wizard
Starson17 can program the VCR without an owner's manual.Starson17 can program the VCR without an owner's manual.Starson17 can program the VCR without an owner's manual.Starson17 can program the VCR without an owner's manual.Starson17 can program the VCR without an owner's manual.Starson17 can program the VCR without an owner's manual.Starson17 can program the VCR without an owner's manual.Starson17 can program the VCR without an owner's manual.Starson17 can program the VCR without an owner's manual.Starson17 can program the VCR without an owner's manual.Starson17 can program the VCR without an owner's manual.
 
Posts: 4,004
Karma: 177841
Join Date: Dec 2009
Device: WinMo: IPAQ; Android: HTC HD2, Archos 7o; Java:Gravity T
Quote:
Originally Posted by chaley View Post
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.
Starson17 is offline   Reply With Quote
Old 02-04-2010, 11:55 AM   #8
kovidgoyal
creator of calibre
kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.
 
kovidgoyal's Avatar
 
Posts: 25,811
Karma: 5006091
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
calibre provides a full programmatic interface to the database via the calibre-debug command

Code:
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
kovidgoyal is offline   Reply With Quote
Old 02-04-2010, 01:45 PM   #9
Starson17
Wizard
Starson17 can program the VCR without an owner's manual.Starson17 can program the VCR without an owner's manual.Starson17 can program the VCR without an owner's manual.Starson17 can program the VCR without an owner's manual.Starson17 can program the VCR without an owner's manual.Starson17 can program the VCR without an owner's manual.Starson17 can program the VCR without an owner's manual.Starson17 can program the VCR without an owner's manual.Starson17 can program the VCR without an owner's manual.Starson17 can program the VCR without an owner's manual.Starson17 can program the VCR without an owner's manual.
 
Posts: 4,004
Karma: 177841
Join Date: Dec 2009
Device: WinMo: IPAQ; Android: HTC HD2, Archos 7o; Java:Gravity T
Quote:
Originally Posted by kovidgoyal View Post
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.
Starson17 is offline   Reply With Quote
Old 02-05-2010, 02:11 AM   #10
kovidgoyal
creator of calibre
kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.
 
kovidgoyal's Avatar
 
Posts: 25,811
Karma: 5006091
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
LibraryDatabse2 has a full interface you can use to do these kinds of tasks.
kovidgoyal is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Troubleshooting Manually setting clock Kumabjorn Amazon Kindle 7 08-09-2010 09:11 AM
Firmware Update UNOFFICIAL How to Manually Update Your Kindle to 2.0.3 sirbruce Amazon Kindle 34 07-04-2010 10:37 AM
Manually add/delete ebooks to a database sariel2413 Calibre 3 03-18-2010 04:45 PM
Handling (deleting, editing) tags manually with Calibre lakicsv Calibre 20 01-14-2010 05:09 AM
Creating media.xml manually pepak Sony Reader 5 11-28-2008 10:26 AM


All times are GMT -4. The time now is 03:33 PM.


MobileRead.com is a privately owned, operated and funded community.