12-19-2009, 11:52 AM | #1 |
Wizard
Posts: 4,004
Karma: 177841
Join Date: Dec 2009
Device: WinMo: IPAQ; Android: HTC HD2, Archos 7o; Java:Gravity T
|
Firstname as author - metadata.db and SQLite3
I've got about 10,000 books imported into Calibre, and most were originally in the lastname, firstname - optional series name - title.ext format.
I customized the regex so the import (5 hours) went well, but now realize that I've inadvertently imported them as multiple author books where the firstname is a second author. I'd like to fix this, but could use some suggestions. I'm comfortable in SQL, and would like to simply run a query to change the author name in metadata.db from lastname| firstname to firstname lastname in the authors table. However, I realize there are several problems doing that. First, there is the author_sort field in the books table that would also need to be fixed. That's pretty easy too. The next issue is that the directory structure is now lastname_ firstname and it should be firstname lastname. That needs to be fixed in both the table and in the actual folder structure. I've noticed that all this is handled automatically inside Calibre when the author is changed, so I'm wondering if I can change just the author field inside metadata.db, then run Calibre or python to fix the directory. Alternatively, Does anyone have any suggestions for fixing this? I can manually change the database and folders, if necessary. Another alternative is to go back to the import (another 5 hours), but I wasn't sure if the regex would allow me to swap firstname and lastname using groups and \1 \2 etc. Another possibility is to modify the names of the files, then reimport. Finally, I can possibly create an xml file from the names, use macros in ultraedit to fix the xml file, then import from the xml. I'd appreciate some comments before I go forward. Thanks! |
12-19-2009, 12:05 PM | #2 |
creator of calibre
Posts: 44,324
Karma: 23661992
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
|
If you're comfortable with scripting you could use the calibredb command to do this
|
Advert | |
|
12-19-2009, 12:27 PM | #3 |
Wizard
Posts: 4,004
Karma: 177841
Join Date: Dec 2009
Device: WinMo: IPAQ; Android: HTC HD2, Archos 7o; Java:Gravity T
|
|
12-19-2009, 12:31 PM | #4 |
creator of calibre
Posts: 44,324
Karma: 23661992
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
|
The user manual contains full documentation for all command line calibre utilities. Using calibredb you can read/write metadata and the write operation will cause calibre to move files into the correct folders for the new names.
|
12-19-2009, 12:45 PM | #5 | |
Wizard
Posts: 4,004
Karma: 177841
Join Date: Dec 2009
Device: WinMo: IPAQ; Android: HTC HD2, Archos 7o; Java:Gravity T
|
Quote:
1)calibredb list into a text file (author names wrong, but get the id) 2)process the text file to change it into a batch to do multiple calibredb writes with the fixed author name and matching id? I've done step 1 already |
|
Advert | |
|
12-19-2009, 12:53 PM | #6 |
Wizard
Posts: 4,004
Karma: 177841
Join Date: Dec 2009
Device: WinMo: IPAQ; Android: HTC HD2, Archos 7o; Java:Gravity T
|
|
12-19-2009, 01:27 PM | #7 |
creator of calibre
Posts: 44,324
Karma: 23661992
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
|
use show_metadata to see the format of an OPF (it's just a simple XML file)
|
12-19-2009, 02:04 PM | #8 | |
Wizard
Posts: 4,004
Karma: 177841
Join Date: Dec 2009
Device: WinMo: IPAQ; Android: HTC HD2, Archos 7o; Java:Gravity T
|
Quote:
Do you have some procedure in mind that I might try? I'm thinking of something like this metascript: cycle through all possible id's, doing a show_metadata pipe to sed to fix firstname and lastname pipe to set_metadata. Last edited by Starson17; 12-20-2009 at 09:56 AM. |
|
12-20-2009, 09:54 AM | #9 | |
Wizard
Posts: 4,004
Karma: 177841
Join Date: Dec 2009
Device: WinMo: IPAQ; Android: HTC HD2, Archos 7o; Java:Gravity T
|
Quote:
I don't know if it's a bug, but for some reason, calibredb always seemed to stop execution of the batch file when it completed. This caused it to ignore any other lines in the same batch file. I had to do this job in two batch files, one to make the opf files, and one to sed process then set the metadata. The process time was about 10 seconds per book, so this is taking a while, (still running 8 hours later). Anyway, thanks for the help. |
|
12-20-2009, 10:58 AM | #10 |
creator of calibre
Posts: 44,324
Karma: 23661992
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
|
Ah yes on windows for some reason the calibre command line utilities dont play well with the cmd.exe interpreter. Better to use powershell.
|
12-20-2009, 01:11 PM | #11 | |
Wizard
Posts: 4,004
Karma: 177841
Join Date: Dec 2009
Device: WinMo: IPAQ; Android: HTC HD2, Archos 7o; Java:Gravity T
|
Quote:
Anyway, thanks again. If I do this again, I'll try powershell. |
|
12-20-2009, 02:01 PM | #12 | |
Well trained by Cats
Posts: 30,364
Karma: 58053698
Join Date: Aug 2009
Location: The Central Coast of California
Device: Kobo Libra2,Kobo Aura2v1, K4NT(Fixed: New Bat.), Galaxy Tab A
|
Quote:
The Gotcha is folder names are CASE sensitive in Linux and correcting a title case in Windows Calibre does NOT change the original (named) folder's case (since it matters naught to M$),but does matter to Calibre. Running the DB Integrity tool showed many errors. Looking in the file manager had me until I thought of Case I fixed the folder names externaly and Calibre was happy. I believe the "Use Lower case for folder names" might keep this from happening ON NEW ENTRIES. |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How To: Getting summary metadata and images, not tags, author, title | Starson17 | Calibre | 21 | 01-10-2011 09:51 AM |
List index out of range, 0.6.46 downloading metadata, no author. | highwaykind | Calibre | 4 | 04-04-2010 07:31 AM |
jetBook reads metadata with FB2 for title & author | tselling | Ectaco jetBook | 0 | 01-07-2010 09:54 PM |
Lastname, Firstname filenames - Any way to import correct author name? | Starson17 | Calibre | 5 | 12-23-2009 11:03 PM |
MetaData/Cover picture via librps? Author sort? | OUTATIME | Sony Reader Dev Corner | 5 | 02-23-2008 06:44 PM |