Database snafu, way to clean it up?

[Gwen Morse]

I'm using Dropbox with Calibre. I'm not sure if this is related to Dropbox or not, but, I've ended up with a _number_ of empty folders (a few hundred in a collection of several thousand ebooks). In some cases the folders are completely empty, in others I just have a jpg (cover art). I think the latter may be after I grabbed metadata and covers for "missing" books.

Is there a way to cleanly remove these entries from my Calibre database? I still have all my ebooks backed up elsewhere so I haven't lost anything. But, I'd like to find a way to clean the bad entries out so I can re-import the books I'm missing.

I was hoping for a way to search for entries with...perhaps..."no" format type and entries with only cover art (or only jpg)...any search options that would be useful?

[DoctorOhh]

Originally Posted by Gwen Morse

Is there a way to cleanly remove these entries from my Calibre database? I still have all my ebooks backed up elsewhere so I haven't lost anything. But, I'd like to find a way to clean the bad entries out so I can re-import the books I'm missing.

I recall someone yesterday cleaning their library of rogue folders by using the move library feature, found by clicking on the library icon. You did say you have everything backed up, right?

If I was going to try this I would first go to Preferences - advanced and Check Database integrity. Upon completion of this check I would deal with anything calibre pointed out first.

Then I would choose, via calibre's library icon, to move my library to another directory in my Dropbox.

Calibre would then move everything it is aware of and leave the junk behind and I would now have a clean library structure.

I haven't done this yet myself but it was recently reported to have worked great.

Good Luck.

Update: Here is the post from the person that did this yesterday.

[itimpi]

FYI: I am in the middle of coding/testing a new function to move out of the Calibre library any folders/files that do not appear to belong to Calibre according to the metadata.db database.

This is in effect the converse of the suggested (and already available) method of moving the library to leave behind the potentially garbage files. Its intent is to help with tidying up Calibre libraries that have gathered up superflous folders/files over time or due to operator error. Once the unknown files have been extracted the user can then peruse them to decide what to do with them (if anything). It should be a lot faster than having to move the whole library to separate the good files from the (potentially) bad ones.

If Kovid accepts the changes it should appear in Calibre in the not too distant future.

[Starson17]

Originally Posted by itimpi

FYI: I am in the middle of coding/testing a new function to move out of the Calibre library any folders/files that do not appear to belong to Calibre according to the metadata.db database.

I would find this useful.

[itimpi]

Originally Posted by Starson17

I would find this useful.

I was surprised by the number of superfluous folders/files I had accumulated over time that my testing has shown up

[DoctorOhh]

Originally Posted by itimpi

FYI: I am in the middle of coding/testing a new function to move out of the Calibre library any folders/files that do not appear to belong to Calibre according to the metadata.db database.

Excellent!

[jackie_w]

Originally Posted by Gwen Morse

I was hoping for a way to search for entries with...perhaps..."no" format type ...

@Gwen... In the meantime a simple search will show you books with no formats. Type format:false in the search bar.

[Starson17]

Originally Posted by jackie_w

@Gwen... In the meantime a simple search will show you books with no formats. Type format:false in the search bar.

Yes, but this won't show folders that Calibre doesn't know about, which may be empty of books as well (or full of books). I've been recommending "Move Library|Check DB Integrity" after doing a few tests on my test DB. If the library is not too large, taking those steps first, followed by the format:false search, should give good results.

[jackie_w]

Originally Posted by Starson17

Yes, but this won't show folders that Calibre doesn't know about, which may be empty of books as well (or full of books). ...

Sorry, I know that. I should have been more specific.

I did a library move a few days ago using the new features. It worked really well and left a couple of "orphan" directories behind in the old library directory which I could examine at leisure.

[jackie_w]

Originally Posted by Starson17

I've been recommending "Move Library|Check DB Integrity" ...

@Starson... just asking for my future info. Is the Check Database Integrity actually necessary if you have just done a library move? Wouldn't the fact that Calibre knew enough about the files to move them mean that the database was clean?

[Starson17]

Originally Posted by jackie_w

@Starson... just asking for my future info. Is the Check Database Integrity actually necessary if you have just done a library move? Wouldn't the fact that Calibre knew enough about the files to move them mean that the database was clean?

Excellent question - one that I don't know the answer to. I did several Move tests, but no integrity tests. I didn't test all conditions I could think of, just those that convinced me it was safe and did what I wanted it to do. I remember thinking at the time that I wasn't sure whether a library "Move" operated at the directory or file level (directories = a "book"; files = a "format"), while the Check Database Integrity definitely checked files, so I suggested both.

[jackie_w]

Thank you.

[kovidgoyal]

An integrity check (apart from checking for the existence of individual formats) also does an SQL integrity check, it basically dumps and reloads the sql from the metadata.db file, so it is generally a good idea to do it.

A move library simply copies all folders that calibre knows about, but does not check if the files are correctly linked. This is because calibre has a feature where if in metadata.db a format is specified, but for some reason the user changes the filename of the file in the calibre library, while leaving it in the correct folder, calibre will automatically pick up the changed file.

In other words, there isn't a strict one to one relationship between formats in the db and files on the disk.

[Gwen Morse]

Thanks for all the answers. I'll be able to work on my database over the upcoming weekend.

I'll make sure to go through all the steps (checking database integrity before moving the folders).

Yes, everything is backed up. What isn't backed up is the actual calibre database...by putting it in dropbox I only have one version.

Thanks for the tag to find ebook-less folders. That will really help!

Gwen

[DoctorOhh]

Originally Posted by Gwen Morse

Yes, everything is backed up. What isn't backed up is the actual calibre database...by putting it in dropbox I only have one version.

To be on the safe side I'd back up the entire libray in a folder outside of the dropbox. If for some reason there is a catastrophic loss you can recover via dropbox but you can only recover the files in one folder at a time. It would be a real chore if you had to recover an entire library this way.