Calibre Sync for Android & iOS

[jbusnengo]

Originally Posted by Glsparks2

I use a filesync program to periodically mirror my Calibre library on my PC to the internal memory of my Android phone. I use Calibre Sync to browse the books on my phone and open them with Moon+ Reader.
My workaround is to modify a COPY of the metadata.db as described earlier and transfer that to the internal memory of my phone. Calibre Sync works then. I have a Powershell script to do the mods. I used the Copilot feature to <help> draft the script. You have to know where System.Data.SQLite.dll is. I use a windows 11 machine.

I use Calibre Sync in largely the same way that you do, and this is an intriguing idea. Would you be willing to share your script?

[PeterT]

Originally Posted by JSWolf

Don't blame calibre for Calibre Sync's problem. No external program should be writing to the database. That's the fault of Calibre Sync.

Jon; calibre sync does NOT write to the calibre database; rather it's retrieving data from it.

In my usage I have no issues; I have Calibre Sync connect to a Calibre Content server instance and NOT a copy of the calibre library stored on a cloud drive.

Sent from my Pixel 9a using Tapatalk

[JSWolf]

Originally Posted by Wiggo

You're talking about things you don't understand again. CalibreSync doesn't write anything to the database.

Believe what you want, the update is to blame for the screw up

Why is CS reading from unused fields that never had any data in them?

[BetterRed]

Enough already, if Calibre Companion hadn't been sold, and then abandoned by the new owner, it likely would have the same issue as Calibre Sync.

The Calibre database API is only available on Linux, MacOS and Windows.

If the iccn, isbn and flags columns were accessible via that API prior to Version 9, they could have been updated by a plugin.

On IOS and Android there is no viable alternative to using the metadata.db directly via SQL SELECT statements, see ==>> SQL SELECT - WikiP .

An asterisk ("*") can be used to specify that the query should return all columns of all the queried tables.

BR

[Glsparks2]

Originally Posted by jbusnengo

I use Calibre Sync in largely the same way that you do, and this is an intriguing idea. Would you be willing to share your script?

Yes. I will post in a little while.

[Glsparks2]

Originally Posted by Glsparks2

Yes. I will post in a little while.

Powershell script to modify a copy of metadata.db:

# Path to your copy of metadata.db
$LibraryPath = "location of your copy of the metadata.db"
Set-Location $LibraryPath

# Backup metadata.db
$timestamp = Get-Date -Format "yyyyMMdd_HHmmss"
Copy-Item "metadata.db" "metadata.db.backup_$timestamp"

# Load SQLite assembly (adjust path if needed)
Add-Type -Path "location of System.Data.SQLite.dll"

$dbPath = Join-Path $LibraryPath "metadata.db"
$conn = New-Object System.Data.SQLite.SQLiteConnection("Data Source=$dbPath;Version=3;")
$conn.Open()

function Exec-SQL($sql) {
$cmd = $conn.CreateCommand()
$cmd.CommandText = $sql
$cmd.ExecuteNonQuery() | Out-Null
}

# Get existing columns from books table
$cmd = $conn.CreateCommand()
$cmd.CommandText = "PRAGMA table_info(books);"
$reader = $cmd.ExecuteReader()

$existingCols = @()
while ($reader.Read()) {
$existingCols += $reader["name"]
}
$reader.Close()

# Add flags INTEGER DEFAULT 1
if ($existingCols -notcontains "flags") {
Exec-SQL "ALTER TABLE books ADD COLUMN flags INTEGER DEFAULT 1;"
}

# Add isbn TEXT
if ($existingCols -notcontains "isbn") {
Exec-SQL "ALTER TABLE books ADD COLUMN isbn TEXT;"
}

# Add lccn TEXT (lowercase)
if ($existingCols -notcontains "lccn") {
Exec-SQL "ALTER TABLE books ADD COLUMN lccn TEXT;"
}

$conn.Close()

Write-Host "metadata.db updated successfully."

[PeterT]

** DEAD END **

Interesting. It looks as if Calibre_Sync was originally created by Seng Jea Lee and his original code is on GitHub at https://github.com/sengjea/calibre-sync This version supports Calibre Content Server, Calibre Wireless Device and (I believe) a locally stored copy of the calibre library; ie NO access to a cloud based version of the calibre library.

From what I cal tell, BIL Studio is UK based, with the "owner" being in Cambridge. Seng Dea Lee looks to be based in London. Make me think that there is some linkage between the two.

[slm]

LinkedIn shows Seng Jea Lee as still developing Calibre Sync:

Projects
Calibre Sync

Jan 2013 – Present

Show project
Calibre Sync was developed to facilitate eBook transfer from Calibre to a mobile device over Wi-Fi. Calibre is an eBook management application that sits on a user's desktop or laptop. This means that Newspapers that have been scheduled for download on Calibre would be automatically transferred to the mobile device once the device is on the same Wi-Fi network as Calibre. This App has been install by more than 50,000 users, has currently at least 20,000 active users and an average rating of 4.1 stars out of more than 300 reviews.

If you have LinkedIn premium, you may be able to message him through LinkedIn

[PeterT]

Originally Posted by slm

LinkedIn shows Seng Jea Lee as still developing Calibre Sync:

Projects

Calibre Sync



Jan 2013 – Present



Show project

Calibre Sync was developed to facilitate eBook transfer from Calibre to a mobile device over Wi-Fi. Calibre is an eBook management application that sits on a user's desktop or laptop. This means that Newspapers that have been scheduled for download on Calibre would be automatically transferred to the mobile device once the device is on the same Wi-Fi network as Calibre. This App has been install by more than 50,000 users, has currently at least 20,000 active users and an average rating of 4.1 stars out of more than 300 reviews.

If you have LinkedIn premium, you may be able to message him through LinkedIn

Oh well; I tried. Heard from the person

-----
I’m not the developer of the Calibre Sync by BIL studios. My app, similarly named, was removed by Google from the play store in 2024

Sent from my Pixel 9a using Tapatalk

[GeniusJ]

Ok I figured out what was wrong. See my next post.

[jbusnengo]

Originally Posted by GeniusJ

My execution of this powershell script failed spectacularly.

I haven't had the chance to try it for myself yet, but I think, in both cases where it asks for a path, that you need to include the filename at the end. In your case, like this: $LibraryPath = "R:\Jim\Dropbox\Books\Calibre\Main Library\metadata.db" adding the bold part.

[jbusnengo]

Originally Posted by jbusnengo

I haven't had the chance to try it for myself yet, but I think, in both cases where it asks for a path, that you need to include the filename at the end. In your case, like this: $LibraryPath = "R:\Jim\Dropbox\Books\Calibre\Main Library\metadata.db" adding the bold part.

It just occurred to me to mention that you should not be running this script on the metadata.db that Calibre actually uses. You should be running it on a copy of your metadata.db. If you run it on the file that Calibre actually uses, it could cause data loss.

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

[Glsparks2]

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

You are much better at developing a script than Me and Copilot! Glad you got something to work!