This article describes how to set up your own ebook server instead of using the inbuilt one in Calibre. A screenshot of what your finished book library will look like is below:

Ebook server screenshot

The benefits of this are:

  • You don't need to have the machine that manages your Calibre library on all the time.
  • The Raspberry Pi is fine to be left on all the time and uses hardly any electricity.
  • It's a lot faster to serve books if you have a large ebook library.
  • It's a lot faster to search for books yourself.

I have found that I tend to use my Raspberry Pi ebook server to search for books all the time now instead of Calibre and only use Calibre to add and manage books.

Currently, the ebook server described below will allow your users to search for books against either Author or Title. This functionality can be extended to include searching by ISBN, tag, description (metadata) etc. You can also add secure login as well to protect your book collection from naughty people.... If you're interested in these additional features, post a comment below and, if there is enough response, I'll make another post about it.

So, to create your own ebook server, you will need:

  • A server that is connected to the Internet (eg. a Raspberry Pi).
  • SQLite3 running on the Raspberry Pi.
  • A webserver with PHP (compiled with SQLite) running on the Raspberry Pi (eg. Lighttpd).
  • Calibre ebook management software on your PC/Mac/Linux box.
  • Imagemagick installed on your Raspberry Pi.
  • A few hours and a few cups of your favourite beverage.

There are four parts to the process:

  1. Set up the database
  2. Write the script
  3. Get the images
  4. Start using it!

Setting up the database

The first step is to take a copy of the Calibre database from your ebook library. This file is a SQLite database called Metadata.db and is located in the the top level folder of your Calibre library.

If you're not sure where your Calibre library is located, then click on the leaning books icon in Calibre and it will tell you the location. Now copy this library to somewhere safe where you can work with it. Remember kids, always work with the copy - not the live database :)

Now we need to create our own ebook database, which will be a stripped down version of the calibre database. This will speed it up for large book collections. I've called mine bukcase.db and it has one table called books.

The SQL to create this is as follows:

CREATE TABLE books ( 
id INTEGER PRIMARY KEY AUTOINCREMENT, 
title TEXT DEFAULT 'Unknown' COLLATE NOCASE, 
authors TEXT COLLATE NOCASE, 
size INTEGER , 
tags TEXT COLLATE NOCASE,
formats TEXT COLLATE NOCASE, 
comments TEXT COLLATE NOCASE, 
isbn TEXT DEFAULT "" COLLATE NOCASE, 
path TEXT DEFAULT "", 
has_cover BOOL DEFAULT 0, 
pubdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

You can create this SQL using the command line tool from sqlite3 or by using a web based interface.

Now we need to attach both the copy of the calibre database (metadata.db) and bukcase.db. This is best done by running sqlite3 from the [command line shell] (https://www.sqlite.org/sqlite.html) and running the following SQL:

ATTACH DATABASE 'metadata.db' as 'calibre';
ATTACH DATABASE 'bukcase.db' as 'bukcase';

Now that the two databases are attached, you can copy the data into your new bukcase.db using the following SQL:

INSERT INTO bukcase.books (id, title, authors, size, tags, formats, comments, isbn, path, has_cover, pubdate)
SELECT id, title,
(SELECT name FROM books_authors_link AS bal JOIN authors ON(author = authors.id) WHERE book = books.id) authors,
(SELECT MAX(uncompressed_size) FROM data WHERE book=books.id) size,
(SELECT name FROM tags WHERE tags.id IN (SELECT tag from books_tags_link WHERE book=books.id)) tags,
(SELECT format FROM data WHERE data.book=books.id) formats,
(SELECT text from comments where comments.book=books.id) comments,
isbn,
path,
has_cover,
pubdate
FROM calibre.books;

Now your database is ready to use, we're ready to move onto the next step, putting your images in the correct location.

Writing the script

Now you have your database ready, put it into the root directory of where you will serve your php files from. In this directory create a file called index.php. This is where all your code will go and you only need one other file to make it work (download.php which is described later): <? // index.php // // check to see if the user has searched if(array_key_exists('action', $_GET)) $action = $_GET ["action"]; if(array_key_exists('select_box', $_GET)) { $select_box = $_GET ["select_box"]; } else { $select_box=0; } if(array_key_exists('words', $_GET)) { $words = $_GET ["words"]; } else { $words=0; } if(array_key_exists('start', $_GET)) { $start = $_GET ["start"]; } else { $start=0; }

//  Convert bytes to human readable format @param integer bytes Size in bytes to convert @return string
function bytesToSize($bytes, $precision = 0) { $kilobyte = 1024; $megabyte = $kilobyte * 1024;  $gigabyte = $megabyte * 1024; $terabyte = $gi$

// You might want to change the html below to be more up to date and valid...
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<title>bukcase</title>
<meta http-equiv="content-type" content="text/html;charset=utf-8" />
<link rel="stylesheet" href="layout.css" type="text/css" />
<link rel="icon"       type="image/png"  href="favicon.ico">
</head>
<body>
<div id="pagewidth" >
     <div id="wrapper" class="clearfix">
    <div id="maincol">
<div>
<br/>
<form id="filter" name="filter" method="get" action="index.php">
<table>
<tr>
<td>
<? print "<a href='" . $_SERVER['SCRIPT_NAME']."'>"; ?> 
<img src="rect4142.png"></a></td>
<td>
<? print "<a href='" . $_SERVER['SCRIPT_NAME']."'>"; ?> 
<img src="text4051-5-8.png"></a></td>
<td class="topbar">Search for books where <select name="select_box">
<option value="1" <?php if ($select_box=="1") echo "selected='yes'"; ?>>Title</option>
<option value="2" <?php if ($select_box=="2") echo "selected='yes'"; ?>>Author</option></select> contains <input name="words" <?php if ($words<>"") echo "value='".$words."'"; ?>>
<input name="GO" value="GO" type="submit">
<input type="hidden" name="action" value="0">
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
</td>
</tr>
</table>
</form>
</div>
<?
// now set up the SQL
$query1="SELECT count(*) as count from books ";
$query2="SELECT * from books ";
if ($select_box == 1)
{
$query3=" WHERE title LIKE '%".$words."%'";
}
if ($select_box == 2)
{
$query3= " WHERE authors LIKE '%".$words."%'";
}
if ($select_box == 0)
{
$query3= " WHERE tags LIKE '%".$featured_tags."%'";
}
$query3.=" ORDER BY pubdate DESC";
$query4=" LIMIT " . $start . ", 10";

//open the database and get the number of rows in the query
$db = new PDO('sqlite:bukcase.db');
$query=$query1.$query3;
$result = $db->query($query);
foreach($result as $row)
        {
        $total_rows = $row['count'];
        }
if ($total_rows > 0)
{
$first_record=$start+1;
$last_record=$start+10;
print "<table><tr class='numresults'><td>Showing ";
if ($last_record>$total_rows) $last_record=$total_rows;
print $first_record." - ".$last_record." out of ".$total_rows." results"; 
print "</td></tr></table>"; 
$query=$query2.$query3.$query4;
$result = $db->query($query);
print "<table>";
foreach($result as $row)
        {
     print "<tr><td>";
     print "<a href=download.php?id=".$row['id']."&format=".$row['formats'].">";
print"<img src='images/".$row['id'].".jpg'></a>";
print "</td><td>";
$title_length=strlen ($row['title']);
if ($title_length>50) {$title_elipsis="...";} else {$title_elipsis="";}
print "<p><span class='booktitle' title='".htmlspecialchars($row['title'], ENT_QUOTES)."'>";
print "<a href=download.php?id=".$row['id']."&format=".$row['formats'].">";
print substr($row['title'],0,50).$title_elipsis."</a></span>";
print " by ";
$authors_length=strlen ($row['authors']);
if ($authors_length>20) {$title_elipsis="...";} else {$title_elipsis="";}
print "<span class='booktitle' title='".htmlspecialchars($row['authors'], ENT_QUOTES)."'>".substr($row['authors'],0,20).$title_elipsi$
print " (".substr($row['pubdate'],0,4).")";
print " <span class='booktype'>".$row['formats']."</span> "; 
print bytesToSize($row['size']);
print "</p><p><span class='bookdesc'>";
$comments = strip_tags(str_replace("<p>"," ",$row['comments']));
$comments_length=strlen ($comments);
if ($comments_length>400) {$title_elipsis="...";} else {$title_elipsis="";}
if ($comments_length==0) $comments="No further information is available for this publication";
if ($comments_length==0) $comments="No further information is available for this publication";
print "<span class='bookdesc' title='".htmlspecialchars($comments, ENT_QUOTES)."'>".substr($comments,0,400).$title_elipsis."</span>";
print "</p></td></tr>";
}
print "</table>";
// close the database connection
$db = NULL;
// now display the bottom results bar
print "<table><tr class='numresults'><td class='bottombar'>";
if($start > 0) 
print "<a href='" . $_SERVER['SCRIPT_NAME'] . "?start=" . ($start - 10) ."&select_box=".$select_box."&words=".$words."&GO=GO&action=0'>< Prev$
print "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
if($total_rows > ($start + 10)) echo "<a href='" . $_SERVER['SCRIPT_NAME'] . "?start=" . ($start + 10) . "&select_box=".    $select_box."&words="$
print "</td></tr></table>";
}
else
{
{
print "<table><tr class='numresults'><td>No results</td></tr></table>"; 
}
?>
</div></div>
<? echo"<font size='1' color='grey'>bukcase last updated xxx</font>";?>
</div>
</body>

As I mentioned in the code, apologies for the invalid html and inefficient php.... There is a css file for this too (layout.css), but you can lay it out however you want. The one I used is:

html,body{margin:0;padding:0;text-align:center;font:100%/1.2 Arial}table{width:100%}tr{outline:thin solid #e6e6e6}tr.noborder{outline:0}tr.numresults{background:#f8f8f8;text-align:left;outline:0;border-collapse:collapse}td{font:100%/1.2 Arial;vertical-align:top}td.topbar{vertical-align:middle;text-align:right}td.bottombar{font:700 110%/1.2 Arial;vertical-align:middle;text-align:center}p{margin:5px 0}h3.featured{vertical-align:bottom}a:link,a:visited{text-decoration:none;color:#333}a:hover,a:active{text-decoration:none;color:red}#pagewidth{width:80%;text-align:left;margin:0 auto}#maincol{background-color:#fff;position:relative}html,body{margin:0;padding:0;text-align:center}#pagewidth{width:80%;text-align:left;margin:0 auto}#maincol{background-color:#FFFFFF;position:relative;.booktitle{font:120%/1.2 Arial;font-weight:700;color:#000}.bookdesc{font:95%/1.2 Arial;color:#474747;vertical-align:middle}.booktype{font-weight:700;color:#fff;background-color:#4a4a4a;border:3px solid #4a4a4a;letter-spacing:1pt;word-spacing:2pt;font-size:10px;text-alig n:left;font-family:arial,helvetica,sans-serif;line-height:1;line-height:10px;vertical-align:center}.clearfix:after{content:".";display:block;height:0;clear:both;visibility:hidden}.clearfix{display:inline-block}* html .clearfix{height:1%}.clearfix{display:block}

Now you have the index.php code, you need to create another file in the same directory called download.php. This will take care of the file download for you.

// download.php
//
$id = htmlspecialchars($_GET["id"]);
$query="SELECT title, authors, path from books where id = ".$id.";";
$result = $db->query($query);
foreach($result as $row)
{
$path = stripslashes($row['path']);
$file_dir = "/xxx/".$path."/";
$values = explode('/', $path);
$values = str_replace(":","_",$values);
$author= $values['0']; 
$title = str_replace(":","_",$row['title']);
$file_name = $title." - ".$author.".".strtolower($format);
}
$file=$file_dir.$file_name;
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header("Content-Disposition: attachment; filename=\"$file_name\"");
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
header('Content-Length: ' . filesize($file));
ob_clean();
flush();
readfile($file);
exit;
?>

Now you have your three files (index.php, download.php and layout.css) you're ready to import your calibre book images.

Getting your book cover images ready

To resize your images and put them in the correct location, you can use the following PHP script that I cobbled together. It will run from the command line of your Raspberry Pi, so you'll need the command line version of PHP installed. I called my script update.php and I run it from the command line using:

sudo php update.php

Here's the code you need to include:

<?
echo "Enter starting number (or press enter if all) : ";
$handle = fopen ("php://stdin","r");
$start = fgets($handle);
$start = trim($start);
$start=$start+0;

echo "Enter end number (or press enter if all) : ";
$handle2 = fopen ("php://stdin","r");
$finish = fgets($handle2);
$finish=trim($finish);
$finish=$finish+0;

print "Updating images from ".$start." to ".$finish."\n";
$query="SELECT id, path, has_cover from books where id>=".$start." and id<=".$finish;

$db = new PDO('sqlite:metadata.db');
$app_dir="/xxx/";
$calibre_dir="/xxx/";

$result = $db->query($query);
foreach($result as $row)
        {
    echo "Looking at ".$row['id']." | ";
    $new_filename=$app_dir.$row['id'].".jpg";
    if ($row['has_cover'] == 1)
        {
        // convert file using imagemagick
        echo "Converting image: ".$row['id']."\n";
        $existing_filename = $calibre_dir.$row['path']."/cover.jpg";
    //echo $existing_filename; 
$resize = exec("sudo convert \"".$existing_filename."\" -resize 80x110 \"".$new_filename."\"");
    }
else
    {
    // create empty.jpg for this filename
    echo "This image doesn't exist, so using an empty one instead.\n";
    $existing_filename=$app_dir."empty.jpg";
    exec ("cp ".$existing_filename." ".$new_filename);
    }
    }
$db = NULL;
?>

NB. Be sure to change the $app_dir variable to where you want your images to be stored and the $calibre_dir variable to point to your metadata.db database copy. I stored my images on my Raspberry Pi SD card as I found that storing them on an external USB drive was a little too slow.

If you have a large Calibre library (more than 5000 books) I recommend only updating the images 5000 at a time as I have experienced either out of memory error when running the script, or the images don't convert correctly as Imagemagick has run out of memory.

If you don't have Imagemagick installed, simply do:

sudo apt-get install imagemagick

Now you should have a full directory of images of books from your Calibre library.

Start using it

After you've done the steps above you should have a directory on your web server with the following:

  • three files (index.php, download.php and layout.css)
  • two images (if you need them, if not modify the html code accordingly)
  • a database file (bukcase.db)
  • a sub directory called images/ with all your book covers in.

You should be able to point your browser to index.php and it should work.... fingers crossed :)

When you've added more books to your Calibre book library, you'll want to update this book library too, so to update it you'll need to do the following.

NB. Run any commands from the webserver directory:

  1. Put up a message saying that the site is being updated so that your users know.
  2. Copy the calibre live database to the webserver directory
  3. Run the image update script: sudo php update.php
  4. Take a backup of the live bukcase.db database just in case things go terribly wrong.
  5. Type in:

    sudo sqlite3

and then enter the following SQL

ATTACH DATABASE 'metadata.db' as 'calibre';
ATTACH DATABASE 'bukcase.db' as 'bukcase';
DELETE FROM bukcase.books;
INSERT INTO bukcase.books (id, title, authors, size, tags, formats, comments, isbn, path, has_cover, pubdate)
SELECT id, title,
(SELECT name FROM books_authors_link AS bal JOIN authors ON(author = authors.id) WHERE book = books.id) authors,
(SELECT MAX(uncompressed_size) FROM data WHERE book=books.id) size,
(SELECT name FROM tags WHERE tags.id IN (SELECT tag from books_tags_link WHERE book=books.id)) tags,
(SELECT format FROM data WHERE data.book=books.id) formats,
(SELECT text from comments where comments.book=books.id) comments,
isbn,
path,
has_cover,
pubdate
FROM calibre.books;
  1. When you're done, enter ctrl D to exit out of sqlite
  2. Test on live system
  3. Remove calibre database file

    sudo rm metadata.db

Phew. Well, thats it. I hope you found this useful. If you did, I'd appreciate a link to the site and a comment below. Thanks for reading.


Comments

comments powered by Disqus

About Gary Hall
I am a primary school teacher based in East Yorkshire, England, and my interests are travel and technology.

You can contact me through , Facebook and Twitter