Restore custom columns?

[the_Pan]

Hi folks!

Because I have a problem with one of the Calibre plugins, I deleted several of my self created columns to test if that helps - of course without backup.
(I backed up the ~/.config folder (Linux), but I wasn't aware, the metadata is stored in a different folder).

Is it still somehow possible to restore the deleted columns?

[BetterRed]

Custom column definitions are in the library to which you added them (in the metadata.db sqlite database). So, if you restore the relevant library in its entirety you should get them back.

BR

[the_Pan]

Originally Posted by BetterRed

Custom column definitions are in the library to which you added them (in the metadata.db sqlite database). So, if you restore the relevant library in its entirety, you should get them back.

BR

If you meant Library Maintenance→Restore Database, this does not seem to work.
Even if I create the columns by hand before the restore process, they do not contain any data.

[chaley]

Originally Posted by the_Pan

If you meant Library Maintenance→Restore Database, this does not seem to work.
Even if I create the columns by hand before the restore process, they do not contain any data.

You need a copy of the file metadata.db made before the columns were deleted. That is where the column definitions and the data in the columns are stored.

Library Maintenance→Restore Database almost surely won't work in your case. The problem: calibre backs up information for books in a file metadata.opf stored in each book folder. When you deleted the columns, calibre started making new backups with the columns removed. It writes these backup files at a rate of 1 backup file every 2 seconds. If calibre was running long enough after you deleted the columns then all the data for those columns are gone. There is no way to recover the deleted data short of restoring all the metadata.opf files from before you deleted the columns. But if you can do that then you can restore the metadata.db file as well.

[the_Pan]

Damn, I was afraid of that!
I have now found a ~1 year old backup of the database, which is definitely better than nothing.
But of course this database is missing all since then added books.
Could I copy the old .db file into the newer library and run restore function?
Or would it be easier to edit the database itself with sqlite browser?

[chaley]

Originally Posted by the_Pan

Damn, I was afraid of that!
I have now found a ~1 year old backup of the database, which is definitely better than nothing.
But of course this database is missing all since then added books.
Could I copy the old .db file into the newer library and run restore function?

The restore function will replace the old (good) database, so no.

Or would it be easier to edit the database itself with sqlite browser?

I can't imagine you being successful with this approach. The problem: you must get *everything* right when hacking the database to add books, including all of the row ids and data formats.

One approach suggested by others on this forum is to:

1. Backup the existing library! It might take a couple of runs through this process to get it right.
2. Restore the metadata.db file.
3. Open calibre
4. Run Library maintenance / Check Library. This will tell you about books that are "missing" because you changed the title or the authors in the last year. Make a note of them and then delete them. It will also tell you about "extra books", which are the book folders made when you renamed something that calibre no longer knows about. Keep them!
5. Use "Library Maintenance / Switch/Create library" to make a new library. Check the "Copy structure from the current library" option.
6. Still in the original library, use "Copy to library" to *move* (not copy) the books calibre knows about to the new library. This will leave behind all the books calibre doesn't know about, presumably the books you added in the last year.
7. Switch to the new library.
8. Use "Add books from folders and subfolders". Say "Yes" to "Assume all e-book files in a single folder are multiple formats of the same book?" Select the old library. Personally I wouldn't use any merge option at this step, instead manually merging later if needed. At the end of this calibre should know about all your books.
9. For extra certainty, while still in the new library, run Library maintenance / check library, including the second pass.
10. Delete the old library when you are satisfied.

I haven't personally followed these steps, but the technique has been suggested many times and I see no reason why it shouldn't work.

[the_Pan]

Okay, thanks a lot for now, I will that when I find the time.

[DaltonST]

Originally Posted by the_Pan

Hi folks!

Because I have a problem with one of the Calibre plugins, I deleted several of my self created columns to test if that helps - of course without backup.
(I backed up the ~/.config folder (Linux), but I wasn't aware, the metadata is stored in a different folder).

Is it still somehow possible to restore the deleted columns?

Before you start flailing, there is an "easy" way to restore your custom column data from an old metadata.db database that you have.


[1] View Job Spy thread: https://www.mobileread.com/forums/sh...18&postcount=1


[2] Look at the Tool named "Import Metadata from CSV File". An image is attached here for your convenience.


[3] Read the Tool's ToolTips by hovering your mouse everywhere in its dialog box for all of its widgets.


[4] Open your "old" metadata.db with Calibre.


[5] Use the Calibre "create catalog" function, specifying as a minimum the Book ID and the Custom Columns that you wish to restore.


[6] Export the Catalog to a .csv file.


[7] Open the .csv file in Calc/Excel (NOT a text editor) to ensure it has what you want in it. Ensure that all of the cells are "text", including Book ID. Create Column Header Names if they are missing. The Tool uses the Header Names. Remember to save the .csv as a UTF-8 file when saving the file as a .csv (e.g. Save Filter option in Calc).



[8] Run Calibre. Quick Switch to your "broken" Library.


[9] Recreate your missing custom columns, which will of course be empty. Restart Calibre. Back up your new metadata.db AFTER restarting.


[10] Click the JS Icon dropdown menu arrow, and navigate to the Metadata submenu to find the "Import Metadata from CSV File" tool. Run it.


[11] Point it to your new, valid .csv file as described and generated above.


[12] "CSV Column Values to Match" is Book ID. "CSV Column Values to Import" from the .csv data is the first custom column to repair. "Target to Update" that same first missing custom column that you just created. "Match Expression" should be "EQUALS".


[13] Execute in "Preview" mode, which is a "pretend" execution so you verify that the .csv's Book IDs match a large subset of your broken Library.


[14] You should specify via the criteria that you only update certain or selected books. Do a few, then examine a few to verify all is well.


[15] Update all matched books for that first custom column, and then repeat the process by only changing the "source" and "target" .csv columns for the other custom columns. Always execute in "Preview" before doing it for real. No need to reimport the .csv file unless you need to make changes to it in Calc/Excel. Remember to save the .csv as a UTF-8 file when saving the file as a .csv (e.g. Save Filter option in Calc).




DaltonST

[the_Pan]

@DaltonST you almost saved my day!
I was able to restore the columns except from one now.
The one I can't restore is a column with integers which shows me the percent I read in the book(it's from the kobotouchextend)

[DaltonST]

Create a new Short Text custom column, then update that with the csv %read in text format. Then try to use Calibre Mass Edit to copy from Text to Float %. Might get very lucky that it converts for you. Still, you have the old data to view although in text format.

DaltonST

[BetterRed]

Originally Posted by the_Pan

If you meant Library Maintenance→Restore Database, this does not seem to work.

No, I meant what I said "restore the library in its entirety", I should have added "from your backup copy".

In my head calibre's Restore database feature gets re-branded as Rebuild or Recover database

BR

[ownedbycats]

Originally Posted by BetterRed

In my head calibre's Restore database feature gets re-branded as Rebuild or Recover database

BR

I've thought the same thing myself. Perhaps something to open a ticket about? I'm not sure that Kovid will go for it though.

[the_Pan]

Originally Posted by DaltonST

Create a new Short Text custom column, then update that with the csv %read in text format. Then try to use Calibre Mass Edit to copy from Text to Float %. Might get very lucky that it converts for you. Still, you have the old data to view although in text format.

DaltonST

All good now, thank you a lot!
I converted all the 100% finished books into a normal true/false column and have them back again now too.
Only a handful of unfinished books is still missing, but I'll get them into the library by hand.
Again, thanks a lot.