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
  }
}

[nephtys59]

Originally Posted by 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

No solution, the app creator doesn't answer emails requesting an update to the app. I even offered to pay a second time, if he fixes it, but nothing. Use the workarounds suggested in this thread.

[bobkoure]

Originally Posted by GeniusJ

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

This assumes the file structure
[some path]\Calibre
\[some name] library
\[some name]library
\[some name] library
etc.

And most of us have a single (or maybe two) libraries, name created when we installed Calibre. I happened to name mine "Calibre Library" but it's in a root directory - so I need to make the line
$CalibreRoot ="D:"

------------------------------------------------------
The important bits (for me) were
1) columns got deleted (because Calibre wasn't using them?)
2) Calibre Sync does use those columns
3) here are the SQL statements to re-create them

At one point in my career (mid 80s) I was 'toolsmith' for a software development group and got to be really good at .BAT files. 40 years later I'm pretty rusty, but to the extent that they syntax is the same (exceptions are new) this looks really good.

For my own peace of mind I ran a test:
1) created c:\test
2) copied sqlite3.exe and Calibre-Fix-AllLibraries.ps1 to c:\test
3) created c:\test library
4) copied metadata.db to c:\test library
5) opened powershell
6) CD'd to c:\test
7) entered the line .\Calibre-Fix-AllLibraries.ps1 (note the ".\")
...and got "UPDATED: test library"

I copied the modified metadata.db to my calibre library folder, started Calibre and the app appears to run fine.
I copied metadata.db to the calibre library folders I have on my android tablet and phone and refresh is now working.

For anyone else looking to replicate what I did here are the lines I changed

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

# Root folder that contains all Calibre libraries
# Each actual library is a subfolder ending in " Library"
$CalibreRoot = "C:\test"

And THANK YOU!!

[bobkoure]

Originally Posted by MysteryMan2

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

Based on the fix that does work you need to add "flags, "isbn", and "lccn".
Simplest thing is to just modify the ps1 file to point at the root of your calibre library folder(s) and run it. But if you've already added 'isbn' maybe just add the other two?

[Knightreader]

Thanks for this script! Now I can use my library again via OneDrive and without a connection to a running Calibre Content Server on my Laptop. Great work!

[justinmiller87]

For those of us on Linux, here's a bash script that I made based on your PS script that does the same thing:

#!/bin/bash
# -------------------------------------------------------------------
# calibre-fix-libraries.sh
#
# Purpose:
# - Back up metadata.db into DBbackups/<LibraryName>/
# - Add missing legacy columns (flags, isbn, lccn) to the books table
# - Safe to re-run
# - Keeps only the most recent N backups per library
#
# IMPORTANT:
# - Close Calibre before running this script
# -------------------------------------------------------------------

# Path to sqlite3 command-line executable
SQLITE3=$(command -v sqlite3)

# Calibre libraries to process
LIBRARIES=(
    "/path/to/Calibre_Library"
)

# Central folder where database backups are stored
BACKUPS_ROOT="/path/to/DBbackups"

# Number of backups to keep per library
KEEP_BACKUPS=10

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

if [ -z "$SQLITE3" ] || [ ! -x "$SQLITE3" ]; then
    echo "Error: sqlite3 not found or not executable. Please install sqlite3."
    exit 1
fi

mkdir -p "$BACKUPS_ROOT"

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

for lib in "${LIBRARIES[@]}"; do
    if [ ! -d "$lib" ]; then
        echo "FAILED: Library directory not found: $lib"
        continue
    fi

    # Friendly name of the library folder
    libName=$(basename "$lib")

    # Full path to this library's metadata.db
    dbPath="$lib/metadata.db"

    if [ ! -f "$dbPath" ]; then
        echo "FAILED: metadata.db not found in $lib"
        continue
    fi

    # Folder where backups for this library will live
    backupDir="$BACKUPS_ROOT/$libName"
    mkdir -p "$backupDir"

    # Generate a timestamp for the backup filename
    timestamp=$(date +"%Y%m%d_%H%M%S")
    backupPath="$backupDir/metadata.db.backup_$timestamp"

    # Step 1: Back up metadata.db
    if ! cp "$dbPath" "$backupPath"; then
        echo "FAILED: $libName - Could not back up metadata.db"
        continue
    fi

    # 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');" 2>/dev/null)
    if [ $? -ne 0 ]; then
         echo "FAILED: $libName - Could not read from database"
         continue
    fi

    changed=false

    # Step 3: Add missing columns
    # Check for 'flags'
    if ! echo "$cols" | grep -qx "flags"; then
        "$SQLITE3" "$dbPath" "ALTER TABLE books ADD COLUMN flags INTEGER DEFAULT 1;"
        changed=true
    fi

    # Check for 'isbn'
    if ! echo "$cols" | grep -qx "isbn"; then
        "$SQLITE3" "$dbPath" "ALTER TABLE books ADD COLUMN isbn TEXT;"
        changed=true
    fi

    # Check for 'lccn'
    if ! echo "$cols" | grep -qx "lccn"; then
        "$SQLITE3" "$dbPath" "ALTER TABLE books ADD COLUMN lccn TEXT;"
        changed=true
    fi

    # Step 4: Backup retention
    # Get all backup files for this library, newest first
    # using ls -t (sort by modification time, newest first)
    backups=($(ls -t "$backupDir"/metadata.db.backup_* 2>/dev/null))
    
    # If there are more backups than allowed, delete the oldest ones
    if [ "${#backups[@]}" -gt "$KEEP_BACKUPS" ]; then
        for (( i=$KEEP_BACKUPS; i<${#backups[@]}; i++ )); do
            rm -f "${backups[$i]}"
        done
    fi

    # Step 5: Status output
    if [ "$changed" = true ]; then
        echo "UPDATED: $libName"
    else
        echo "NO CHANGE: $libName"
    fi
done

You can confirm that they have been added with one or both of the following tests:

sqlite3 /path/to/Calibre_Library/metadata.db "PRAGMA table_info(books);" | grep -E "flags|isbn|lccn"

sqlite3 /path/to/Calibre_Library/metadata.db "Select name FROM pragma_table_info('books');"

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
  }
}

[JimmXinu]

It has been stated before, but bears repeating:

You should not be changing the database in your actual Calibre library. You should be making a copy first and modifying that.

Just as the DB schema change broke Calibre Sync, changing your library DB might prevent Calibre itself from using your library in a future version.

My personal script uses rsync to quickly update the copy of my library, then changes the DB file before sync'ing it to my device.

I also want to reiterate: the issue isn't the change to Calibre, it's the fact Calibre Sync is not being maintained now.

[eastcoastelle]

Is there anyway to have Calibre Sync use Moonreader as the default app for reading? I have a Boox Palma 2 and while I've made everything else work and got my library synced up, as soon as I download a book and hit "read" it opens in NeoReader. I've already made Moonreader my default app for these types of files, so I can't figure out the issue.

Thanks in advance for any help!

[birkmaggs]

please delete.
I didn't know the app was not being maintained

[dunhill]

I'm building a plugin that, from calibre, copies your library to a cloud service and patches the metadata.db file to use calibre-sync without breaking the local calibre database. It would work something like this:

The main purpose of CalibreSync Bridge is to connect the desktop version of calibre with the CalibreSync Android app.

1. Full Library Synchronization (Not Just the Database)
Unlike other scripts that only modify the metadata file, this plugin performs a full library synchronization.

It scans your local calibre library and replicates the folder structure and book files (EPUB, PDF, MOBI, etc.) to your designated cloud folder (Dropbox, Google Drive, etc.).

It uses incremental logic: it only copies new or modified files, preserving timestamps to avoid unnecessary uploads.

2. The "Compatibility Patch"
The main problem is that CalibreSync on Android often requires specific database columns (ISBN, LCCN, and flags) that might be missing or handled differently in the main metadata.db file after certain updates.

Priority: The plugin never modifies your library's original database.

Process: It copies your metadata.db file to the cloud folder and then performs SQL injection to add the missing columns to that copy.

Result: You get a patched and fully functional library in the cloud that CalibreSync can read without problems, while your master library remains intact.

https://www.mobileread.com/forums/sh...95#post4576095