Calibre Sync for Android & iOS

[Terisa de morgan]

Originally Posted by KhenemetHeru

This works as a good stopgap with my library which resides on my computer and is shared on Dropbox. It also does not screw with Calibre 9 when the altered database is reopened, though I assume I'll have to run it every time I update the database with added books. Thank you.

Not necessary, structure does not change just for adding books.

[GeniusJ]

Originally Posted by KhenemetHeru

This works as a good stopgap with my library which resides on my computer and is shared on Dropbox. It also does not screw with Calibre 9 when the altered database is reopened, though I assume I'll have to run it every time I update the database with added books. Thank you.

No, you shouldn't have to do that. If there's another version update that changes the db schema, then you'll have to run it then. Otherwise, once the db is updated, you're good to go for a while.

[msr]

As PeterT mentioned, one workaround for those who have the option is to switch to a Calibre Content Server. Syncing CalibreSync with that still works. I don't like it as much as syncing with my library in Dropbox, but at least it provides functionality while we wait (perhaps forever) to see if CalibreSync itself will be fixed, and doesn't require posthoc modification of the Calibre DB to add the removed columns back.

This means I can only update CalibreSync while the content server is running (something which I do rarely and only when necessary for particular tasks and only while I'm actively at home), rather than allowing me to update the DB on the fly from anywhere I have internet access, but it is better than having no access at all.

[jtmart]

Originally Posted by GeniusJ

Glsparks2, your code didn't work for me as expected (I blame myself) so I tweaked it a bit. This is now working for me.

PowerShell script to restore missing columns in Calibre metadata.db across all libraries (with backups)

If a third-party tool expects older columns in Calibre’s metadata.db and they are missing, this PowerShell script will add them back to the books table if needed. It does this for every Calibre library under a root folder. It also makes timestamped backups of each metadata.db in a separate DBbackups folder.

What you need

Windows
This is for Windows PowerShell.

sqlite3.exe (the command-line tool)
Download the SQLite tools zip from sqlite.org. You want the zip named like: sqlite-tools-win-x64-<version>.zip
Extract it and make sure you have sqlite3.exe.

Place sqlite3.exe somewhere stable, then update the script path. Example location:
C:\Tools\sqlite\sqlite3.exe
You can also put it in another folder if you prefer. Just point the script at it.

Your Calibre library root folder
This is the folder that contains your library folders, each with a metadata.db inside.

Important safety note
Close Calibre before running this. Calibre can keep metadata.db open.

What it does

Finds every library folder under a root path that contains metadata.db

Creates a matching backup folder under DBbackups<LibraryName>\

Copies metadata.db to a timestamped backup file

Adds these columns to the books table if they do not exist:

flags INTEGER DEFAULT 1
isbn TEXT
lccn TEXT

You should only need to run this script once after Calibre updates to 9.x or any minor revision after that. So if you apply a Calibre update, open the updated version of Calibre, close it, then run this script to put the columns back in the database.

Script
Copy this into a file named something like: Calibre-Fix-AllLibraries.ps1

# -------------------------------------------------------------------
# Calibre-Fix-AllLibraries.ps1
#
# Purpose:
# - Scan a Calibre root folder for library directories ending in " Library"
# - For each library:
#   - Back up metadata.db into DBbackups\<LibraryName>\
#   - Add missing legacy columns (flags, isbn, lccn) to the books table
# - Safe to re-run:
#   - If columns already exist, no schema change occurs
# - Keeps only the most recent N backups per library
#
# IMPORTANT:
# - Close Calibre before running this script
# -------------------------------------------------------------------

# Path to sqlite3 command-line executable
# This must be the sqlite3.exe tool, not the DLL
$Sqlite3 = "C:\Windows\System32\sqlite3.exe"

# Root folder that contains all Calibre libraries
# Each actual library is a subfolder ending in " Library"
$CalibreRoot = "R:\Jim\Dropbox\Books\Calibre"

# Folder where database backups are stored
# Each library gets its own subfolder under here
$BackupsRoot = Join-Path $CalibreRoot "DBbackups"

# Number of backups to keep per library
# Older backups beyond this count are deleted
$KeepBackups = 10

# -------------------------------------------------------------------
# Sanity checks
# -------------------------------------------------------------------

# Ensure sqlite3.exe exists
if (-not (Test-Path $Sqlite3)) {
  throw "sqlite3.exe not found: $Sqlite3"
}

# Ensure the Calibre root folder exists
if (-not (Test-Path $CalibreRoot)) {
  throw "Calibre root not found: $CalibreRoot"
}

# Ensure the DBbackups root folder exists
# -Force means it will not error if it already exists
New-Item -ItemType Directory -Force -Path $BackupsRoot | Out-Null

# -------------------------------------------------------------------
# Discover library folders
# -------------------------------------------------------------------

# Find all directories under the Calibre root that:
# - End with " Library"
# - Contain a metadata.db file
$libraries = Get-ChildItem -Path $CalibreRoot -Directory -Force |
  Where-Object { $_.Name -like "* Library" } |
  Where-Object { Test-Path (Join-Path $_.FullName "metadata.db") }

# If no libraries are found, stop immediately
if (-not $libraries) {
  throw "No library folders ending with ' Library' containing metadata.db found under: $CalibreRoot"
}

# -------------------------------------------------------------------
# Process each library
# -------------------------------------------------------------------

foreach ($lib in $libraries) {

  # Friendly name of the library folder
  $libName = $lib.Name

  # Full path to this library's metadata.db
  $dbPath = Join-Path $lib.FullName "metadata.db"

  # Folder where backups for this library will live
  $backupDir = Join-Path $BackupsRoot $libName

  # Ensure the library-specific backup folder exists
  New-Item -ItemType Directory -Force -Path $backupDir | Out-Null

  # Generate a timestamp for the backup filename
  $timestamp = Get-Date -Format "yyyyMMdd_HHmmss"

  # Full path to the backup file
  $backupPath = Join-Path $backupDir "metadata.db.backup_$timestamp"

  try {

    # ---------------------------------------------------------------
    # Step 1: Back up metadata.db
    # ---------------------------------------------------------------

    # Copy metadata.db to the backup folder
    # -ErrorAction Stop ensures we do not proceed if backup fails
    Copy-Item -Path $dbPath -Destination $backupPath -ErrorAction Stop

    # ---------------------------------------------------------------
    # Step 2: Read existing columns from the books table
    # ---------------------------------------------------------------

    # Query SQLite for column names in the books table
    $cols = & $Sqlite3 $dbPath "SELECT name FROM pragma_table_info('books');"

    # Normalize output:
    # - Trim whitespace
    # - Remove empty lines
    $cols = $cols | ForEach-Object { $_.Trim() } | Where-Object { $_ }

    # Track whether any schema change is made
    $changed = $false

    # ---------------------------------------------------------------
    # Step 3: Add missing columns
    # ---------------------------------------------------------------

    # Add flags column if missing
    if ($cols -notcontains "flags") {
      & $Sqlite3 $dbPath "ALTER TABLE books ADD COLUMN flags INTEGER DEFAULT 1;"
      $changed = $true
    }

    # Add isbn column if missing
    if ($cols -notcontains "isbn") {
      & $Sqlite3 $dbPath "ALTER TABLE books ADD COLUMN isbn TEXT;"
      $changed = $true
    }

    # Add lccn column if missing
    if ($cols -notcontains "lccn") {
      & $Sqlite3 $dbPath "ALTER TABLE books ADD COLUMN lccn TEXT;"
      $changed = $true
    }

    # ---------------------------------------------------------------
    # Step 4: Backup retention
    # ---------------------------------------------------------------

    # Get all backup files for this library, newest first
    $backups = Get-ChildItem -Path $backupDir -File -Filter "metadata.db.backup_*" -ErrorAction SilentlyContinue |
      Sort-Object LastWriteTime -Descending

    # If there are more backups than allowed, delete the oldest ones
    if ($backups.Count -gt $KeepBackups) {
      $backups | Select-Object -Skip $KeepBackups | Remove-Item -Force -ErrorAction SilentlyContinue
    }

    # ---------------------------------------------------------------
    # Step 5: Status output
    # ---------------------------------------------------------------

    if ($changed) {
      Write-Host "UPDATED:" $libName
    } else {
      Write-Host "NO CHANGE:" $libName
    }
  }
  catch {
    # Catch and report any error for this library without stopping the script
    Write-Host "FAILED:" $libName "-" $_.Exception.Message
  }
}

Can confirm this works brilliantly well, though it caused some anguish...and you only need to run the script once: https://www.mobileread.com/forums/sh...5&postcount=50 !

[MysteryMan2]

I have been using Calibre Sync on my Android devices for quite a while and has been working fine. However, just tried to Sync my library, which is on Dropbox and getting an error.....

"Metadata: true: Failed to load library info, reason: Database Exception (no such column: isbn (code 1): while compiling: SELECT id, title, timestamp, pubdate, series_index, author_sort, isbn, iccn, path, has_cover, last_modified, uuid FROM books)....."

Any ideas of what has changed recently and/or how I can fix this?

I have added a user-defined column called isbn to my Calibre library, but this has not made any difference

[MysteryMan2]

You beauty! This has saved me so much heartache! Thank you so much

Originally Posted by GeniusJ

Glsparks2, your code didn't work for me as expected (I blame myself) so I tweaked it a bit. This is now working for me.

PowerShell script to restore missing columns in Calibre metadata.db across all libraries (with backups)

If a third-party tool expects older columns in Calibre’s metadata.db and they are missing, this PowerShell script will add them back to the books table if needed. It does this for every Calibre library under a root folder. It also makes timestamped backups of each metadata.db in a separate DBbackups folder.

What you need

Windows
This is for Windows PowerShell.

sqlite3.exe (the command-line tool)
Download the SQLite tools zip from sqlite.org. You want the zip named like: sqlite-tools-win-x64-<version>.zip
Extract it and make sure you have sqlite3.exe.

Place sqlite3.exe somewhere stable, then update the script path. Example location:
C:\Tools\sqlite\sqlite3.exe
You can also put it in another folder if you prefer. Just point the script at it.

Your Calibre library root folder
This is the folder that contains your library folders, each with a metadata.db inside.

Important safety note
Close Calibre before running this. Calibre can keep metadata.db open.

What it does

Finds every library folder under a root path that contains metadata.db

Creates a matching backup folder under DBbackups<LibraryName>\

Copies metadata.db to a timestamped backup file

Adds these columns to the books table if they do not exist:

flags INTEGER DEFAULT 1
isbn TEXT
lccn TEXT

You should only need to run this script once after Calibre updates to 9.x or any minor revision after that. So if you apply a Calibre update, open the updated version of Calibre, close it, then run this script to put the columns back in the database.

Script
Copy this into a file named something like: Calibre-Fix-AllLibraries.ps1

# -------------------------------------------------------------------
# Calibre-Fix-AllLibraries.ps1
#
# Purpose:
# - Scan a Calibre root folder for library directories ending in " Library"
# - For each library:
#   - Back up metadata.db into DBbackups\<LibraryName>\
#   - Add missing legacy columns (flags, isbn, lccn) to the books table
# - Safe to re-run:
#   - If columns already exist, no schema change occurs
# - Keeps only the most recent N backups per library
#
# IMPORTANT:
# - Close Calibre before running this script
# -------------------------------------------------------------------

# Path to sqlite3 command-line executable
# This must be the sqlite3.exe tool, not the DLL
$Sqlite3 = "C:\Windows\System32\sqlite3.exe"

# Root folder that contains all Calibre libraries
# Each actual library is a subfolder ending in " Library"
$CalibreRoot = "R:\Jim\Dropbox\Books\Calibre"

# Folder where database backups are stored
# Each library gets its own subfolder under here
$BackupsRoot = Join-Path $CalibreRoot "DBbackups"

# Number of backups to keep per library
# Older backups beyond this count are deleted
$KeepBackups = 10

# -------------------------------------------------------------------
# Sanity checks
# -------------------------------------------------------------------

# Ensure sqlite3.exe exists
if (-not (Test-Path $Sqlite3)) {
  throw "sqlite3.exe not found: $Sqlite3"
}

# Ensure the Calibre root folder exists
if (-not (Test-Path $CalibreRoot)) {
  throw "Calibre root not found: $CalibreRoot"
}

# Ensure the DBbackups root folder exists
# -Force means it will not error if it already exists
New-Item -ItemType Directory -Force -Path $BackupsRoot | Out-Null

# -------------------------------------------------------------------
# Discover library folders
# -------------------------------------------------------------------

# Find all directories under the Calibre root that:
# - End with " Library"
# - Contain a metadata.db file
$libraries = Get-ChildItem -Path $CalibreRoot -Directory -Force |
  Where-Object { $_.Name -like "* Library" } |
  Where-Object { Test-Path (Join-Path $_.FullName "metadata.db") }

# If no libraries are found, stop immediately
if (-not $libraries) {
  throw "No library folders ending with ' Library' containing metadata.db found under: $CalibreRoot"
}

# -------------------------------------------------------------------
# Process each library
# -------------------------------------------------------------------

foreach ($lib in $libraries) {

  # Friendly name of the library folder
  $libName = $lib.Name

  # Full path to this library's metadata.db
  $dbPath = Join-Path $lib.FullName "metadata.db"

  # Folder where backups for this library will live
  $backupDir = Join-Path $BackupsRoot $libName

  # Ensure the library-specific backup folder exists
  New-Item -ItemType Directory -Force -Path $backupDir | Out-Null

  # Generate a timestamp for the backup filename
  $timestamp = Get-Date -Format "yyyyMMdd_HHmmss"

  # Full path to the backup file
  $backupPath = Join-Path $backupDir "metadata.db.backup_$timestamp"

  try {

    # ---------------------------------------------------------------
    # Step 1: Back up metadata.db
    # ---------------------------------------------------------------

    # Copy metadata.db to the backup folder
    # -ErrorAction Stop ensures we do not proceed if backup fails
    Copy-Item -Path $dbPath -Destination $backupPath -ErrorAction Stop

    # ---------------------------------------------------------------
    # Step 2: Read existing columns from the books table
    # ---------------------------------------------------------------

    # Query SQLite for column names in the books table
    $cols = & $Sqlite3 $dbPath "SELECT name FROM pragma_table_info('books');"

    # Normalize output:
    # - Trim whitespace
    # - Remove empty lines
    $cols = $cols | ForEach-Object { $_.Trim() } | Where-Object { $_ }

    # Track whether any schema change is made
    $changed = $false

    # ---------------------------------------------------------------
    # Step 3: Add missing columns
    # ---------------------------------------------------------------

    # Add flags column if missing
    if ($cols -notcontains "flags") {
      & $Sqlite3 $dbPath "ALTER TABLE books ADD COLUMN flags INTEGER DEFAULT 1;"
      $changed = $true
    }

    # Add isbn column if missing
    if ($cols -notcontains "isbn") {
      & $Sqlite3 $dbPath "ALTER TABLE books ADD COLUMN isbn TEXT;"
      $changed = $true
    }

    # Add lccn column if missing
    if ($cols -notcontains "lccn") {
      & $Sqlite3 $dbPath "ALTER TABLE books ADD COLUMN lccn TEXT;"
      $changed = $true
    }

    # ---------------------------------------------------------------
    # Step 4: Backup retention
    # ---------------------------------------------------------------

    # Get all backup files for this library, newest first
    $backups = Get-ChildItem -Path $backupDir -File -Filter "metadata.db.backup_*" -ErrorAction SilentlyContinue |
      Sort-Object LastWriteTime -Descending

    # If there are more backups than allowed, delete the oldest ones
    if ($backups.Count -gt $KeepBackups) {
      $backups | Select-Object -Skip $KeepBackups | Remove-Item -Force -ErrorAction SilentlyContinue
    }

    # ---------------------------------------------------------------
    # Step 5: Status output
    # ---------------------------------------------------------------

    if ($changed) {
      Write-Host "UPDATED:" $libName
    } else {
      Write-Host "NO CHANGE:" $libName
    }
  }
  catch {
    # Catch and report any error for this library without stopping the script
    Write-Host "FAILED:" $libName "-" $_.Exception.Message
  }
}