Audit whether Calibre custom columns can survive before Restore database

Make a verified copy before any repair

Confirm that you are looking at the intended library folder. A wrong path or inaccessible drive can make a healthy library appear empty. If metadata.db is missing or damaged, stop Calibre and copy the complete library folder to a different local location. Preserve the original bytes; run every experiment on a separate copy.

Do not delete metadata.db, run restore_database or open the only copy in a Calibre build you are merely testing. The official FAQ recommends copying the complete library folder when moving libraries, while the calibredb reference warns that restore_database rebuilds the database and cannot preserve every library-level setting.

Understand the three evidence layers

metadata.db is the live database, but this audit deliberately never opens it. metadata_db_prefs_backup.json can contain definitions for custom columns. Each book folder may also contain metadata.opf with Calibre user metadata. Current Calibre source supports an OPF3 calibre:user_metadata JSON mapping and a legacy OPF2 calibre:user_metadata:#lookup representation.

A definition says that a lookup key such as #read or #genre existed. A per-book #value# says that one OPF records a value for that key. Neither proves that every book was backed up recently, and an empty value can be legitimate. Conflicting datatype, multiplicity, display or name metadata across OPFs is a stop sign, not something this audit silently resolves.

Run the read-only inventory

Save the following audited Python 3 script outside the library as calibre_custom_columns_audit.py. Run python3 calibre_custom_columns_audit.py "/path/to/Calibre Library" > calibre-column-audit.json, writing the report outside the library. The script recursively reads metadata.opf and the preferences backup, prints JSON to stdout, never opens metadata.db and never writes inside the library.

#!/usr/bin/env python3 """Read-only audit of Calibre custom-column recovery evidence. The script never opens metadata.db and never writes inside the library. It prints JSON to stdout; redirect it outside the library if desired. """ from __future__ import annotations import argparse import json import sys from collections import Counter, defaultdict from pathlib import Path from xml.etree import ElementTree as ET def custom_defs_from_json(node): """Find definition-like dictionaries in the prefs backup defensively.""" found = {} def walk(value): if isinstance(value, dict): label = value.get("label") datatype = value.get("datatype") name = value.get("name") if isinstance(label, str) and label and isinstance(datatype, str): key = label if label.startswith("#") else f"#{label}" found.setdefault(key, { "label": label.lstrip("#"), "name": name if isinstance(name, str) else None, "datatype": datatype, "is_multiple": value.get("is_multiple"), }) for child in value.values(): walk(child) elif isinstance(value, list): for child in value: walk(child) walk(node) return found def parse_opf(path: Path): """Return {lookup_key: field_metadata} from OPF2 or OPF3 Calibre tags.""" root = ET.parse(path).getroot() result = {} for elem in root.iter(): if elem.tag.rsplit("}", 1)[-1] != "meta": continue name = elem.attrib.get("name", "") prop = elem.attrib.get("property", "") if name.startswith("calibre:user_metadata:#"): key = name.split("calibre:user_metadata:", 1)[1] raw = elem.attrib.get("content", "") value = json.loads(raw) if isinstance(value, dict): result[key] = value elif prop == "calibre:user_metadata": value = json.loads((elem.text or "").strip()) if isinstance(value, dict): for key, field in value.items(): if isinstance(key, str) and key.startswith("#") and isinstance(field, dict): result[key] = field return result def value_present(field): value = field.get("#value#") if value is None: return False if isinstance(value, (str, list, tuple, dict, set)): return len(value) > 0 return True def signature(field): return json.dumps({ "name": field.get("name"), "label": str(field.get("label", "")).lstrip("#") or None, "datatype": field.get("datatype"), "is_multiple": field.get("is_multiple"), "display": field.get("display"), }, ensure_ascii=False, sort_keys=True, separators=(",", ":")) def audit(library: Path): if not library.is_dir(): raise ValueError("library path is not a directory") prefs_path = library / "metadata_db_prefs_backup.json" prefs_error = None prefs_defs = {} if prefs_path.is_file(): try: prefs_defs = custom_defs_from_json(json.loads(prefs_path.read_text(encoding="utf-8"))) except Exception as exc: # report; do not repair or rewrite prefs_error = f"{type(exc).__name__}: {exc}" opfs = sorted(library.rglob("metadata.opf")) parse_errors = [] per_key_files = Counter() per_key_values = Counter() per_key_signatures = defaultdict(Counter) paths_by_key = defaultdict(list) for path in opfs: try: fields = parse_opf(path) except Exception as exc: parse_errors.append({"path": str(path.relative_to(library)), "error": f"{type(exc).__name__}: {exc}"}) continue for key, field in fields.items(): per_key_files[key] += 1 if value_present(field): per_key_values[key] += 1 per_key_signatures[key][signature(field)] += 1 if len(paths_by_key[key]) < 5: paths_by_key[key].append(str(path.relative_to(library))) all_keys = sorted(set(prefs_defs) | set(per_key_files)) columns = [] for key in all_keys: sigs = per_key_signatures[key] columns.append({ "lookup_key": key, "definition_in_prefs_backup": key in prefs_defs, "prefs_definition": prefs_defs.get(key), "opf_files_with_definition": per_key_files[key], "opf_files_with_nonempty_value": per_key_values[key], "definition_variants_in_opfs": len(sigs), "definition_conflict": len(sigs) > 1, "sample_paths": paths_by_key[key], }) stop_reasons = [] if not opfs: stop_reasons.append("no metadata.opf files found") if prefs_error: stop_reasons.append("preferences backup is unreadable") if parse_errors: stop_reasons.append("one or more metadata.opf files are unreadable") if any(c["definition_conflict"] for c in columns): stop_reasons.append("conflicting custom-column definitions exist across OPFs") if any(not c["definition_in_prefs_backup"] for c in columns): stop_reasons.append("some OPF custom columns are absent from the preferences backup") return { "library": str(library.resolve()), "read_only": True, "metadata_db_present": (library / "metadata.db").is_file(), "prefs_backup_present": prefs_path.is_file(), "prefs_backup_error": prefs_error, "metadata_opf_count": len(opfs), "metadata_opf_parse_errors": parse_errors, "columns": columns, "stop_before_restore": bool(stop_reasons), "stop_reasons": stop_reasons, "notice": "This audit does not prove that a restore will succeed. Test Calibre Restore database only on a verified complete copy.", } def main(): parser = argparse.ArgumentParser(description="Audit Calibre custom-column recovery evidence without modifying the library") parser.add_argument("library", type=Path, help="path to the Calibre library folder") args = parser.parse_args() try: result = audit(args.library) except Exception as exc: print(json.dumps({"ok": False, "error": f"{type(exc).__name__}: {exc}"}, ensure_ascii=False), file=sys.stderr) return 2 print(json.dumps(result, ensure_ascii=False, indent=2, sort_keys=True)) return 1 if result["stop_before_restore"] else 0 if __name__ == "__main__": raise SystemExit(main())

Read the report as a stop/go preflight

For each #lookup key, compare definition_in_prefs_backup, opf_files_with_definition, opf_files_with_nonempty_value and definition_variants_in_opfs. A low nonempty-value count is not automatically an error: many columns are intentionally blank. Compare the count with known examples in your library and manually inspect a few reported sample_paths on the copy.

Stop before restore if metadata.opf files are missing or unreadable, the preferences backup cannot be parsed, a key is absent from the preferences backup or definition_conflict is true. Also stop if the report is simply inconsistent with books you know contain values. Preserve the JSON report and the copied library before seeking help.

A clean report means only that this limited evidence set is internally consistent. It does not prove that every library-level feature or custom-column value can be rebuilt, that OPF backups are current, or that a Restore database run will succeed.

Test restoration only on another copy

If the inventory is plausible, make a second verified copy and perform the supported Restore database procedure there. Keep the untouched source copy offline. Afterward, compare custom-column definitions, known filled and blank values, book counts, formats and covers. Separately verify saved searches, user categories, plugboards, conversion settings and recipes because the official command warns that these can be lost.

If the restored copy changes evidence you still need, stop. Do not repeat the process against the original and do not treat a successful database open as proof that custom metadata is complete. The useful result of this preflight is a bounded evidence record and a reversible decision, not an automatic repair.

What this audit actually tested

The exact script above has SHA-256 83b54e367d7aad731b6ba15d3ec2d487e369cda4975611613e015b8d2b00d949. Four synthetic unit tests passed: consistent OPF3 plus preferences, legacy OPF2, conflicting definitions missing from preferences, and malformed OPF reporting with the input bytes unchanged. The test file SHA-256 is 61a53983de02dcd7aa5c37b4b0ba8fc828bbcfe3367241165c6e4c920f7a349d. Python byte-code compilation also succeeded.

These are synthetic filesystem tests, not a run on a damaged real library and not a Restore database trial. The recursive preference search is defensive because backup structure can vary; it may miss an unfamiliar future structure. Review the script and report before relying on them, and keep the original library untouched.