Build OPDS 2.0 Catalog from Calibre Library.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook experiments with a method to read a Calibre Library, produce an OPDS 2.0 catalog, and package catalog, ebook files, and cover art into a zip file for deployment on a web site. I wrote about the approach in a [blog post](https://skybristol.com/blog/books/). I may eventually package this thing up in proper Python way, but for now, it operates as a simple experimental way of going about this chore."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import os\n",
    "import sqlite3\n",
    "import pandas as pd\n",
    "import magic\n",
    "from zipfile import ZipFile\n",
    "from PIL import Image\n",
    "import math\n",
    "\n",
    "mime = magic.Magic(mime=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To operate, we need to know the path to a Calibre library we want to operate on and some target paths for deploying the catalog. The target paths are used to a) set up the catalog with http links that will work once files are deployed and b) package up files into a zip for deployment."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "calibre_library = \"/Users/sky/Documents/SkyBooks/\"\n",
    "\n",
    "target_paths = {\n",
    "    \"web_base\": \"https://skybristol.com/books/\",\n",
    "    \"books\": \"books/\",\n",
    "    \"covers\": \"covers/\"\n",
    "}\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There might be better ways of doing this, but Calibre is pretty nice in its simplicity and consistency over time. I use sqlite to connect to the metadata.db file containing everything that is done to build out and improve a given library and then Pandas to stitch everything together. The library I'm working against here is about 4700 titles, so it's big enough but not massive. Pandas is efficient enough at this scale to not have to worry about any performance issues."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 189 ms, sys: 32.9 ms, total: 222 ms\n",
      "Wall time: 288 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "library = sqlite3.connect(f'{calibre_library}metadata.db')\n",
    "\n",
    "df_books = pd.read_sql_query(\"SELECT * FROM books\", library)\n",
    "\n",
    "df_comments = pd.read_sql_query(\"SELECT * FROM comments\", library)\n",
    "df_books = pd.merge(\n",
    "    left=df_books,\n",
    "    right=df_comments[['book','text']], \n",
    "    left_on='id', \n",
    "    right_on='book',\n",
    "    how=\"left\"\n",
    ")\n",
    "\n",
    "df_book_publisher_links = pd.read_sql_query(\"select * from books_publishers_link\", library)\n",
    "df_books = pd.merge(\n",
    "    left=df_books, \n",
    "    right=df_book_publisher_links[['book','publisher']], \n",
    "    left_on='id', \n",
    "    right_on='book',\n",
    "    how=\"left\"\n",
    ")\n",
    "\n",
    "df_publishers = pd.read_sql_query(\"SELECT * FROM publishers\", library)\n",
    "df_publishers = df_publishers.rename(columns={'id': 'publisher_id', 'name': 'publisher_name'})\n",
    "df_books = pd.merge(\n",
    "    left=df_books,\n",
    "    right=df_publishers[['publisher_id','publisher_name']], \n",
    "    left_on='publisher', \n",
    "    right_on='publisher_id',\n",
    "    how=\"left\"\n",
    ")\n",
    "\n",
    "df_books.drop(['sort','timestamp','author_sort','isbn','lccn','flags','publisher','publisher_id','book_x', 'book_y'], axis='columns', inplace=True)\n",
    "\n",
    "df_authors = pd.read_sql_query(\"SELECT * FROM authors\", library)\n",
    "df_books_authors_link = pd.read_sql_query(\"SELECT * from books_authors_link\", library)\n",
    "df_data = pd.read_sql_query(\"SELECT * FROM data\", library)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I went ahead and set up a few key functions here that do the business of building the OPDS catalog/manifest. I know these are clunky and not very efficient at this point, but I'm still getting to know and understand the specification and they let me clearly see what's happening and tweak it as I want to make the system better."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert_size(size_bytes):\n",
    "    if size_bytes == 0:\n",
    "        return \"0B\"\n",
    "    size_name = (\"B\", \"KB\", \"MB\", \"GB\", \"TB\", \"PB\", \"EB\", \"ZB\", \"YB\")\n",
    "    i = int(math.floor(math.log(size_bytes, 1024)))\n",
    "    p = math.pow(1024, i)\n",
    "    s = round(size_bytes / p, 2)\n",
    "    return \"%s %s\" % (s, size_name[i])\n",
    "\n",
    "def opds_link_constructor(file_object, url_base):\n",
    "    if not url_base.endswith(\"/\"):\n",
    "        url_base = f'{url_base}/'\n",
    "    \n",
    "    return {\n",
    "        \"rel\": \"publication\",\n",
    "        \"href\": f'{url_base}{file_object[\"relative_path\"]}',\n",
    "        \"type\": file_object[\"mime_type\"]\n",
    "    }\n",
    "\n",
    "def opds_cover_constructor(file_object, url_base):\n",
    "    if not url_base.endswith(\"/\"):\n",
    "        url_base = f'{url_base}/'\n",
    "\n",
    "    return {\n",
    "        \"rel\": \"cover\",\n",
    "        \"href\": f'{url_base}{file_object[\"relative_path\"]}',\n",
    "        \"type\": file_object[\"mime_type\"],\n",
    "        \"height\": file_object[\"height\"],\n",
    "        \"width\": file_object[\"width\"]\n",
    "    }\n",
    "\n",
    "def opds_pub_metadata_from_calibre(book, authors, files, covers, url_base):\n",
    "    book_meta = {\n",
    "        \"metadata\": {\n",
    "            \"@type\": \"http://schema.org/Book\",\n",
    "            \"title\": book[2],\n",
    "            \"author\": {\n",
    "                \"name\": authors[0][0],\n",
    "                \"sortAs\": authors[0][1]\n",
    "            },\n",
    "            \"identifier\": book[6],\n",
    "            \"language\": \"en\",\n",
    "            \"modified\": book[8],\n",
    "            \"published\": book[3],\n",
    "            \"publisher\": book[10],\n",
    "            \"description\": book[9]\n",
    "        },\n",
    "        \"links\": [],\n",
    "        \"resources\": []\n",
    "    }\n",
    "    \n",
    "    if files is not None:\n",
    "        for file in files:\n",
    "            book_meta[\"links\"].append(opds_link_constructor(file, f'{url_base[\"web_base\"]}{url_base[\"books\"]}'))\n",
    "    \n",
    "    if covers is not None:\n",
    "        for cover in covers:\n",
    "            book_meta[\"resources\"].append(opds_cover_constructor(cover, f'{url_base[\"web_base\"]}{url_base[\"covers\"]}'))\n",
    "\n",
    "    return book_meta"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is the main process that builds out the catalog. The reason it takes a little bit of time is that I go in and touch every file that's referenced in my Calibre library to a) make sure it's there and b) gather mime type and image size details to flesh out the catalog. I also realize that this process is fairly ugly at this point with way too much conditional processing, but it let's me keep track of what's going on and tweak it as I experiment with how the catalog actually works. At this scale, it doesn't really take that long to run.\n",
    "\n",
    "Some things I'm thinking about:\n",
    "* I store really large images in some cases because I'm slighly OCD about cover art. For an online catalog implementation, I probably need to scale down my images to a standard-ish size as part of this packaging process.\n",
    "* I should really compare onboard metadata in the epub/mobi files here with extracted metadata from the Calibre library and harmonize.\n",
    "* The catalog needs better browse organization. The OPDS spec seems to use the ideas of groups to help implement things like browse lists by author, genre, series, and other dynamics. I could fairly easily keep building out a giant catalog file with all those dimensions or use the navigation idea to link to \"subcatalogs.\" But it seems like there should be a better way to use the metadata to implement those dynamics. I'll experiment with some OPDS clients out there to see what they do in this regard already.\n",
    "* I certainly need to abstract out the other config details for building the catalog so it is more extensible to other use cases/libraries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 23.3 s, sys: 4.36 s, total: 27.7 s\n",
      "Wall time: 36.5 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "catalog = {\n",
    "    \"metadata\": {\n",
    "        \"title\": \"Sky Books\"\n",
    "    },\n",
    "    \"links\": [\n",
    "        {\n",
    "            \"rel\": \"self\",\n",
    "            \"href\": \"https://skybristol.com/books/catalog.json\",\n",
    "            \"type\": \"application/opds+json\"\n",
    "        }\n",
    "    ],\n",
    "    \"navigation\": [\n",
    "        {\n",
    "            \"title\": \"Full Catalog\",\n",
    "            \"href\": \"https://skybristol.com/books/catalog.json\",\n",
    "            \"type\": \"application/opds+json\"\n",
    "        }\n",
    "    ],\n",
    "    \"publications\": list()\n",
    "}\n",
    "\n",
    "packaged_files = list()\n",
    "\n",
    "for row in df_books.itertuples():    \n",
    "    book_authors = list()\n",
    "    for author_record in df_authors.loc[df_authors.id.isin(df_books_authors_link.loc[df_books_authors_link[\"book\"] == row[1]][\"author\"].to_list())].itertuples():\n",
    "        book_authors.append((author_record[2], author_record[3]))\n",
    "\n",
    "    file_base = f'{calibre_library}{row[5]}'\n",
    "\n",
    "    file_paths = list()\n",
    "    for data_record in df_data.loc[df_data[\"book\"] == row[1]].itertuples():\n",
    "        relative_path_book = f'{row[5]}/{data_record[5]}.{data_record[3].lower()}'\n",
    "        fs_path_book = f'{calibre_library}{relative_path_book}'\n",
    "        if os.path.exists(fs_path_book):\n",
    "            packaged_files.append({\n",
    "                \"local_path\": fs_path_book,\n",
    "                \"remote_path\": f'{target_paths[\"books\"]}{relative_path_book}'\n",
    "            })\n",
    "            file_paths.append({\n",
    "                \"relative_path\": relative_path_book,\n",
    "                \"mime_type\": mime.from_file(fs_path_book)\n",
    "            })\n",
    "    if len(file_paths) == 0:\n",
    "        file_paths = None\n",
    "\n",
    "    if row[7]:\n",
    "        cover_paths = list()\n",
    "        fs_path_cover = f'{file_base}/cover.jpg'\n",
    "        relative_path_cover = f'{row[5]}/cover.jpg'\n",
    "        if os.path.exists(fs_path_cover):\n",
    "            im = Image.open(fs_path_cover)\n",
    "            width, height = im.size\n",
    "            packaged_files.append({\n",
    "                \"local_path\": fs_path_cover,\n",
    "                \"remote_path\": f'{target_paths[\"covers\"]}{relative_path_cover}'\n",
    "            })\n",
    "            cover_paths.append({\n",
    "                \"relative_path\": relative_path_cover,\n",
    "                \"mime_type\": mime.from_file(fs_path_cover),\n",
    "                \"width\": width,\n",
    "                \"height\": height\n",
    "            })\n",
    "    if len(cover_paths) == 0:\n",
    "        cover_paths = None\n",
    "        \n",
    "    catalog[\"publications\"].append(\n",
    "        opds_pub_metadata_from_calibre(row, book_authors, file_paths, cover_paths, target_paths)\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This last step runs through and builds out a zip package to facilitate deployment to a web server. It essentially changes the structure that Calibre sets up slightly to facilitate having a separate, publicly accessible directory of cover art to go along with a public catalog and a closed directory of the ebooks themselves. It maintains the same nested hierarchy of author/book names that Calibre uses. I toyed with the idea of flattening everything out and using unique identifiers to rename files and keep everything linked up (I really hate all the whacky directory names). However, it seemed best to not introduce that further level of abstraction at this point, particularly if I wanted to do something like run my actual Calibre-managed library on the server or use rsync to keep it up to date directly and not run this packaging step.\n",
    "\n",
    "I also have not yet dug into any of the other files that Calibre maintains such as individual OPF metadata files (and how those may or may not jive with Calibre metadata or onboard file metadata) and resized images. This process just eliminates everything but what I'm actually going to serve online in my particular use case."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4768 books in books_and_covers.zip 2.1 GB\n",
      "CPU times: user 4.23 s, sys: 3.98 s, total: 8.21 s\n",
      "Wall time: 11.3 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "catalog_file = \"catalog.json\"\n",
    "archive_file = \"books_and_covers.zip\"\n",
    "\n",
    "if os.path.exists(archive_file):\n",
    "    os.remove(archive_file)\n",
    "\n",
    "with open(catalog_file, \"w\") as f_catalog:\n",
    "    f_catalog.write(json.dumps(catalog))\n",
    "    f_catalog.close()\n",
    "\n",
    "with ZipFile(archive_file,'w') as f_archive:\n",
    "    f_archive.write(catalog_file, catalog_file.split(\"/\")[-1])\n",
    "    for file_object in packaged_files: \n",
    "        f_archive.write(file_object[\"local_path\"], file_object[\"remote_path\"])\n",
    "    f_archive.close()\n",
    "    \n",
    "os.remove(catalog_file)\n",
    "\n",
    "print(len(catalog[\"publications\"]), \"books in\", archive_file, convert_size(os.stat(archive_file).st_size))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}