Figure 1: LibraryDB user interface

Even though I'm not in the library business, I've always owned a lot of books, and since first acquiring a computer I've been episodically either planning or attempting to create an accurate book list. In the early days this meant a lot of manual data entry, and there was the inevitable chasm between the real and the ideal.

Over the years my enthusiasm for this project has waxed and waned as various schemes collided with reality, but some recent developments have made the idea of a library database much more practical:

• Open-source databases like SQLite3 can efficiently and easily manage any conceivable book list.
• Modern books are likely to have a bar code printed on them containing a unique identifier, something increasingly likely to be true as time passes.
• To elaborate, the ISBN book indexing and identification scheme is finally becoming reliable, and newer books have a 13-digit ISBN (International Standard Book Number) coded into the book's bar code instead of the older, less useful ISBN10 or (worse) UPC (Universal Product Code).
• Small, portable, battery-operated Android devices are now reasonably priced and include cameras as standard equipment.
• There are free bar-code scanner programs that run under Android, that use the Android built-in camera to decode book bar codes in seconds.

I've owned a few Android devices and have used them to navigate around the countryside using free maps and the GPS satellite navigation receiver built into most such devices. But I recently realized that such a small, inexpensive Android device would make an effective book-scanning tool, and might make a book database a more practical undertaking than hand-entering the title, author and publisher for each of my 400 books.

Here's how the library database system works, in sequential order:

A: Use an Android device to scan book bar codes and convert them into plain text:

Figure 2: Scanning ISBN bar codes

B: Transfer the scanned plain-text results to a laptop or desktop machine, either immediately or after a delay:

Figure 3: Transferring scanned codes

C: Process and decode the results, use the scanned ISBN codes as indices to online book databases, and create library database records for inclusion in your personal library database:

Figure 4: Processing scanned codes

In the following sections I provide details for each of the above steps.

For this phase we need an Android device equipped with certain free programs. Android devices are now reasonably priced — I just acquired a Nexus 7 for about US$270. To some readers this may not seem like such a bargain, but I should tell you that in 1977 I spent $500 for an Apple II floppy disk drive that was able to store 140 kilobytes with reasonable reliability. Consider that US$500 (1977) is US$1,929.68 (2013) (source), so one can assume that I have somewhat bleak expectations about price versus performance.

A typical Android device has, among other technical wonders, a camera or two, and there are free bar code scanner programs that use the camera as their input device. The scanner programs work rather well, athough many such programs instantly take you to an online shopping mall to buy what you've just scanned. So one must choose scanner programs carefully, and in this case, we just want the program to scan a bar code, convert it to plain text, and place the result on the Android device's clipboard for the next step.

I have experimented with most of the available free scanner programs, the majority are disappointing, or too aggressively commercial, or both. I recommend BarCode Scanner, because (1) it's the scanner application behind most of the other offerings, (2) it's much better than the average application, and (3) it will copy its results onto the clipboard without also sweeping you away on an unexpected shoppping trip. Click here to install this program on your Android device. Once the program is installed, enter its "Settings" menu and choose the following options:

• Deselect "QR Codes" to speed up decoding of bar codes.
• Select both the "1D barcodes" and "Data Matrix" options.
• Select "Copy to Clipboard".
• Deselect both "Remember Duplicates" and "Retrieve more info".
• Select "Use auto focus".
• Optionally deselect "Bulk scan mode" (see below for a discussion of this choice).

The test of correct settings is to pick up a book published in the last 20 years and scan its barcode. If the settings are right, the program will autofocus, scan, beep, and show a large reproduction of the ISBN code (and copy the code onto the Android clipboard). To move on to the next scan, reactivate the scanner by pressing the left-arrow symbol on the Android display.

In this phase we discuss ways to transfer scanned codes to a computer running either Windows or Linux, using one of two methods:

1. Bulk scan mode enabled, so the user can transfer a collection of scans taken while out of reach of a wireless network.
2. Bulk scan mode disabled, while connected wirelessly, so the scanned codes are made immediately available to the database software located on the main machine. This method, called "interactive scanning", is the preferred option when possible.

For option (1) above (no wireless connection):

• Enable "Bulk scan mode" in the Barcode Scanner program's option menu.
• Scan some codes — the program will remember all the scanned codes in its history.
• When once again in range of a wireless network, transfer the codes all at once by selecting "History", then "Send History", then choosing a method — email, storage in a cloud account, etc. A cloud account is preferred because it reduces the steps required to make the codes available to the library database processing software.
• Move to the laptop or desktop responsible for managing the database and retrieve the codes.
• The following steps are explained below.

For option (2) above (connected to a wireless network):

• Disable "Bulk scan mode" in the Barcode Scanner program's option menu.
• Install on your Android device one fo the free utility programs that monitors the clipboard and makes its contents available to the computer hosting the database, by way of the wireless network.
• One program that can provide clipboard access is my own SSHelper, but because SSHelper is much larger than typical clipboard utilities, I don't recommend it unless you want SSHelper's other features.
• Run ClipboardAssistant or SSHelper. The program will print an address and port by which the program can be accessed on your local network. An example might be "http://192.168.0.5:32789" (your address may be different). Copy this number down.
• Move to the main machine, the machine responsible for maintaining the book database, open a browser, and type in the address you copied above. If all is well (and if your main machine is on the same network as your Android device), a clipboard display will appear from the ClipboardAssistant server.
• This is just a confirmation test and you won't need to manually copy scans from the clipboard display — as explained below, the Python program that manages the database will automatically access the shared clipboard and process each scanned ISBN code.
• Your main machine's browser doesn't need to be running for this scheme to work, it's only used to verify that things are working.

Quick summary

In option (1), out of range of a wireless network, you accumulate scans in the scanner program's history. Once within range of a wireless network, you "Send History" using any convenient method — email, cloud storage, and others. Then you read the transferred codes into the main processing application discussed in the next section.

In option (2), within range of a wireless network and on the same network as the computer hosting the database software, we can use the interactive scanning method, and the results are immediately transmitted to the host computer for immediate processesing.

This step describes the Library Database program that converts the scanned ISBN codes into MySQL database records. Setting it up is simpler than installing the Android applications described above. Here's how to proceed on Linux and Windows:

• Linux:

  • Assuming the Library Database program will only be used by you (the simplest installation), navigate to your home directory.
  • Download this ZIP archive and place it in your home directory.
  • UnZIP the ZIP archive by right-clicking it and choosing "Extract Here", or "Extract Archive here, Autodetect Subfolder" or words to that effect — the extraction option that creates a new folder to receive the ZIP archive's contents.
  • The above step should create a new folder named "LibraryDB" containing the Library Database program with the same name.
  • For the next step, move to the the section below named "Both Linux and Windows".

• Windows:

  • For the simplest installation, navigate to the root directory of your system, i.e. C:\. (The location you choose is unimportant — any convenient location will do as well.)
  • Download this ZIP file and place it in the chosen directory.
  • UnZIP the ZIP file by right-clicking it and choosing "Extract All" — the extraction option that creates a new folder to receive the ZIP archive's contents.
  • The above step will create a new directory named "LibraryDB" containing the Library Database program "LibraryDB.exe".

• Both Linux and Windows:

  • Enter the LibraryDB directory and click the program icon. If your system is compatible with the Library Database version you've downloaded, a user interface will appear as shown in Figure 1 above.
  • Notice that the program has created two new directories named "isbn_scanned_codes" and "isbnwc_retrieved_json".
  • When the user quits, the program also creates a file named "LibraryDB.ini", which preserves any choices you've made for next time.
  • The "isbn_scanned_codes" directory holds files containing codes acquired in bulk from the Android scanner, or entered by hand into a plain-text data file. The names of the files don't matter, only that they be plain text files and contain one ISBN code per line (the numbers may be ISBN-10 or ISBN-13).
  • The "isbnwc_retrieved_json" directory will contain data retrieved from an online database once you begin converting codes into database records.

  • Now let's run a little test.

    • To verify the present setup and familiarize yourself with how the system operates, click here to download an example data file (you may need to right-click the link and choose "Save as ..."). The file contains these codes (two of the codes are legitimate, two are not):

      9781451648539
      0374522596
      9781451648538
      978145164853
                              

    • Place the downloaded file in the "isbn_scanned_codes" directory.
    • Run the Library Database program by clicking its icon, then press the "Start" button.
    • If things have been set up correctly (apart from not yet entering MySQL credentials), and if your system has an Internet connection, the log display will show:

      Processing files in "isbn_scanned_codes" subdirectory...
      No SQLite3 server connection.
      No SQLite3 server connection.
      Error: ISBN 9781451648538 failed checksum.
      Error: ISBN 978145164853 neither 10 nor 13 digits.
      Done.
      Results:
            Records processed 0
            New records 0
            Duplicate records 0
            Online queries 2
            Data errors 0
            ISBN code errors 2
      
                              

    UPC code scanning
    Some readers may wonder whether the Library Database program couldn't be expanded to read the UPC codes that appear on older books and turn them into ISBN codes by means of online searches. Well, I tried it, and it doesn't work. What I discovered is that, after an item is no longer actively being sold, its UPC code may be recycled.
    Here's an example. I have a copy of "Arctic Dreams" by Barry Lopez. It has an ISBN-10 code of 0-553-26396-X, but the bar code printed on the book is a UPC code (076783006997), not an ISBN code:
    
    If I scan the book and use the resulting UPC code (076783006997) for an online search, I access a completely different book. And if I refuse to be dissuaded, if I search further, I encounter yet another book with the same UPC.
    • 0-553-26396-X -> "Arctic Dreams", Barry Lopez
    • 076783006997 -> "Death Rattle", Terry Johnston
    • 076783006997 -> "Days of Blood and Fire", Katharine Kerr
    
    This is one of the reasons for the ISBN-13 scheme — it avoids numbers with no fixed meaning.

    • After the above events, and assuming you have an active Internet connection, the "isbnwc_retrieved_json" directory will contain two database files whose names are the same as their ISBN-13 codes:

      9780374522599.json
      9781451648539.json
                              

    • Here's what happened:
      • The first code in the test data file, "9781451648539", is a legitimate ISBN-13 code that identifies a book named "Steve Jobs" by Walter Isaacson (a first-rate book, speaking as someone who knew Mr. Jobs personally).
      • The second code is a legitimate ISBN-10 code that identifies an older book titled "The Control of Nature" by John McPhee.
      • The third code is a modified version of the first — I changed the last digit to make it invalid (the final digit in the ISBN-13 scheme is a checksum).
      • The fourth code isn't either 10 or 13 digits long, so it's invalid for that reason.
    • So, given those facts, in the program run we should have seen two successful online queries and two failure messages, and we do. But what is the query result file named "9780374522599.json"? That number doesn't appear in the input file.
    • The answer is that 9780374522599 is the ISBN-13 equivalent of the older ISBN-10 code 0374522596, for the McPhee book — the Library Database program automatically converts older codes to their modern 13-digit equivalents before performing online queries (and all ISBN-10 codes have ISBN-13 equivalents).
  • The conclusion you should draw from this test is that you can place plain-text files in the "isbn_scanned_codes" directory and the Library Database program will turn any legitimate ISBN codes into database records. You may also see that, if other kinds of bar codes are scanned, for example the UPC codes that appear on older books, the Library Database program will keep them out of your database (and see box item on this page).

• Interactive Scanning

  • Earlier, we discussed two methods for scanning and transmitting records: in bulk, and separately. We've just shown how to perform the bulk method — scan some books with the bar code scanner program in bulk storage mode, then transmit the results any convenient way — email, cloud storage, whatever — then copy the results into a plain-text file as in the above experiment.
  • In interactive scanning, codes are processed immediately as they are scanned, by way of a real-time data link between your Android device and the Library Database program. Interactive scanning is an efficient way to process books, because (1) any difficulties become immediately obvious, and (2) there's no need to copy files from one place to another as in the above method. Indeed, once the system has been set up properly, one can progress from picking up a book to having a database record in a matter of seconds.
  • The hardware setup for this method has been covered above in B: Data Processing, option (2). The reader may want to review that section, make sure things are in order and the browser test succeeds.
  • Run the Library Database program, select the "Settings" tab, and enter the address provided by the ClipboardAssistant program in the "Server URL" entry.
  • Select the "URL Reader Mode" checkbox.
  • Click "Start" and watch what appears in the activity log. If communications have been established, you will see:

    Opening URL monitoring mode.
    Monitoring Android "ClipboardAssistant" at (address):(port) ...
                        

  • If instead the program reports a failure, check to make sure the Android ClipboardAssistant program is actually running, the Android device is communicating with the main computer (use the browser test described above to verify this), and that the entered address, port and password are correct.
  • Assuming the above procedure was successful, move to the bar code scanner program, disable "Bulk scan mode" in the program's settings, select "Copy to clipboard", and scan a book's ISBN code.
  • If all is in order, for each new scan, the Library Database program log will show:

    Received ISBN from URL server: (10- or 13-digit number)
                        

  • If the received code is not already present in the database, a full description will be sought online, and assuming the search is successful, a new database record will be created.
  • A very important note. This clipboard monitoring method relies on new scans being different than old ones. The only way the program can tell that a new scan has taken place is by detecting a change in the clipboard's contents. This means repeated scans of the same book won't create repeated online searches.
  • For both Linux and Windows platforms and in most cases, the Library Database program can provide voice feedback as scanning proceeds (Linux systems should have the Festival voice synthezizer installed — it should be available in your distributions's package management system). Just click the "Voice Prompts" checkbox. This is an easy way to keep track of online search results while within hearing range of the main machine.
  • It's also useful to observe the scanner program processing its own scans. A successful ISBN-13 scan looks like this:
  
  

  Figure 5: Successful ISBN-13 scan

  • By monitoring scan outcomes, one can determine successful scans before the Library Database program comes to the same conclusion. In bulk scans, away from a network connection, this kind of observing is more important.

Starting in Version 1.5, the online database LibraryDB uses is Google (https://www.googleapis.com/books). Until version 1.5, the WorldCat database was being used, but WorldCat decided to limit access to their database and require a signup, in my opinion the first step toward monetizing their database.

This project satisfies a number of goals. One was to have a reliable book database, literally after decades of wanting one. Another was to minimize the effort level — I could obviously have hand-entered the titles and authors of 400 books, but that would have been both painful and humbling in this computer age.

The minimum-effort goal works on more than one level. In thinking about this project I decided on a building-block approach — I would do all in my power to avoid writing software that already exists. After some research and evaluations of different ways to communicate scan results, I settled on a method that marries a free scanner program to a free, lightweight Android server program. The free scanner program obligingly places its results on the Android clipboard, and the free server program reads the clipboard. All that remained was for me to persuade the server to transmit the clipboard's contents to an arbitrary destination in near real-time. That turned out to be relatively easy.

I'm an advocate of free, open-source software. Like all my programs, this program is free, it doesn't have advertisements, and its GPL-licensed source is freely available as well.

For learning Sqlite3 in a general way, I can recommend my own MySQL Tutorial, although there are any number of excellent MySQL and SQLite3 tutorials available.

If you detect a bug in Library Database, feel free to report it, but please make sure you're reporting a bug, not asking how to run the program or learn about databases.

• Version 1.6 01.24.2018. Converted the Linux version of this program to use SQLite3 instead of MySQL — much easier setup and operational flexibility.
• Version 1.5 10.18.2014. Updated the PyInstaller version used in the creation of the standalone version of this program, then recreated the Linux and Windows executable packages. Users are stil better off running LibaryDB.py directly if they're willing to install all its package requirements.
• Version 1.4 09.09.2014. Increased the number of Android programs that can be used for clipboard access.
• Version 1.3 09.09.2014. Recoded the Android data reader to be more responsive.
• Version 1.2 09.08.2014. Rewrote the application to dump the no-longer-available Android 3CX application and use the more straightforward Android ClipboardAssistant program instead.
• Version 1.1 02.22.2014. Updated LibraryDB to accommodate changes in Android 3CX application.
• Version 1.0 09.28.2013. Initial Public Release.