MySQL-based scalable RFID reader with a WebServer(AJAX) configurator exposed by Raspberry PI

Disclaimer: this is the translated counterpart of this article, still written by your author and formerly published by the Elettronica Open Source web magazine (thank to them about the nulla osta). Any reproduction or quotation of this page, both as a whole or partially, in any form is explicitly forbidden.

RaspberryRFIDSystem-680x340

This article presents a RFID badge/key reader developed using the Raspberry PI board with the skill to monitor in real-time by a Web page, exposed by a web server, several ID accesses stored onto a MySQL database. Using an asynchronous AJAX callback (through an XMLHttpRequest) the browser will poll the Raspberry (once per second) about the DB status and it will render it in a properly formatted HTML page by the WebServer. Even the system can be used out of the box without big changes, the goal of this article is focused about the educational value of its underlying technologies.

Introduction

The whole project has been designed to be strictly Open Source, both concerning the software libraries and the sources implementing the application. The source code is available at the following  github link. Another important aspect to be underlined is the system being CrossPlatform, i.e. it can be compiled and run on different platforms. Specifically, the project has been developed and tested at the beginning on a MacBook Pro, providing the two readers been plugged in the two available USB ports. After that, the sources have ported and tested onto the Raspberry PI, with very few changes. As many reader will know, the Os X operating system is a Unix system indeed, being accessible by a shell, which the most enterprising readers will have tested for sure. Being a sort of Linux big brother (or a younger one, according to the POV) which is present on the Raspberry PI, it has been really easy to port the source code which, natively, compiles under Linux as well. In order to be able to port and compile the system on different platforms, your author didn’t used the typical OsX development tools, i.e. Xcode & Co., but the code was entirely developed with a simple text editor (mate) and compiled using the default tool under Unix/Linux, i.e makefile & Co. Actually, the system has been not yet ported under the Windows platform, both for lack of time and for technical problems about the HidApi library compatibility under the Win platform (more details on this under the section dedicated to the used software libraries). If you’d really need to run this system under the Windows platform, you can use the trick to install Linux emulators under Windows, like cygwin or similar. In the following article we will describe the project, starting from scratch with the Raspberry Linux preparation, installing the Raspbian operating system together with all the needed libraries to compile the sources. Some of these libraries will be installed more easily using the packet manager deployed with the Linux distribution (distro in jargon), i.e. apt from the command prompt. Some other ones will be will be downloaded as sources, compiled and configured with a more advanced method (but less friendly and less automatic than the apt).

How to remotely access the Raspberry PI shell

There are two main ways to do that: using the ssh command or using a telnet client like Putty or similar. When you work with OsX, its shell natively exposes the ssh command without installing anything else. First of all you have to determine the IP address assigned by the net DHCP (after the very first log in you will be able to assign a static IP avoiding any conflicts with other net devices, in order to have an IP known a priori to be used). To develop this system, the Raspberry PI (Model B+, Pi 2 Model B) has be plugged to the net using the ETH cable (you can use a WiFi dongle as well, after having set the password using the wpa supplicant, or even the Raspberry Pi 3).Thanks to the net router, your author discovered the assigned IP was 192.168.1.7. Soon after, remembering the Raspbian default user is “pi” and its default password is “raspberry”, you can use the following command:

> ssh -lpi 192.168.1.7

The board will ask the user for the password and she will be able to access the Raspberry PI directly from the bash shell. This procedure will be used both to launch and using the application, which will be compiled following the instructions, and for executing the libraries installation and compilation procedure, described in the dedicated section. About the hardware and gadget oriented readers, they can follow a method in which a USB -> TTL serial device will be used, which will automatically connect itself to the Raspberry PI free PINs. At that point, they will be able to work directly by the shell exposed by the serial monitor, which runs echoing the one described above by the board TCP/IP stack. In figure 1 you can see a similar device, available for about ten euros on eBay.

DebuggerSeriale-1024x765.pngFigure 1: serial debugger

You will be able to find easily this device pin color map on many sites. Despite this project is really straightforward in its use (it’s enough to plug the readers, use the badge and see its data collected and updated in real-time on any web browser working on the page exposed by the Raspberry PI), it involves a lot of technologies and software libraries which can be reused in many other contexts and projects as well.

Functional description

The main component the system is based upon is the Raspberry PI board, specifically the B+ version of it. The system actually works using some RFID with 125 KHz of frequency (EM4100 standard compatible transponder), but there’s no any constraint to use some other different standards as well (just make sure you don’t mix the frequencies). The main point is these readers are detected by the system as HID (Human Interface Devices) like a normal keyboard, receiving on every RFID tag detection a bunch of characters like on a serial cable. The system is able to recognise both the readers on start-up (where with start up we mean the application launch from the shell), and to recognise them on hot plug through the “Recognise” button exposed by the configuration page from the web browser. At that point, putting different RFID tags in front of the readers (badge or keys) you will be able to see on the browser all the informations logged in real-time about the DB status (about every second). You can see a typical web page in the figure 2.

TabellaSuBrowser.pngFigure 2: web page exposed by the web browser

Under the button “recognise” is shown the number of RFID readers found by the system. The first column (keeping the 0th column the “Tag” one) shows the reader number as it has been detected on start-up or after the “recognise” button pressure. The second column relates to the Tag ID as detected by the reader. The third column shows the time coordinates at about the reading has been done. Under the table (which is the only one to be refreshed using a really simple AJAX trick by the XMLHttpRequest, so to avoid the page flashing on every reload), there’s a field which shows the number of frame refreshing sent by the web browser up to that time and other two debug fields, in order to be able to check the table is the only refreshed one without collecting any flash. To launch the application you have to type on the shell:

> sudo ./HIDWebServer
inserting then the superuser password. To see the web page, it’s enough to link to it writing on the browser the Raspberry IP address (either a static IP or a DHCP assigned on the local network). If you are testing the code from a Linux/Unix host, then you have to put the machine loopback address, i.e. 127.0.0.1, usually resolved as localhost. From shell the application will interact usually in the following way:
>sudo ./HIDWebServer
MySQL client version: 5.7.10
Recognize readers...
VendorId = 8ff, ProductId = 9, Serial = 0
0x7fbdd16060d0: Open successful
VendorId = 8ff, ProductId = 9, Serial = 0
0x7fbdd1700530: Open successful
Thread 1 started, pHID=0x7fbdd16060d0
Thread 2 started, pHID=0x7fbdd1700530
HIDWebServer is listening on port 80
Then you can see the MySQL version and the number of found readers (further on the article, for every reader a software thread will be launched onto which the hidapi library guarantees a sealed communication channel to every reader, in order to avoid mixing the incoming characters). For each reader, mainly for Debug purposes, you can also read whether the HID device has been successfully opened or not and if the thread related to each reader has been successfully opened as well. At last you can see the web server is listening on the default port 80 and it’s ready to accept incoming connections. These are Debug info which could be hidden in case of production use of this system. When you expose a web server, it puts itself in listening mode on a specific port (and for doing it you should have superuser rights, i.e. why the sudo). In our case, the default port used by the HIDWebServer is the port 80, i.e. the default one onto which the http protocol is running onto. If you are running the system on Raspberry already installed, you may find the port 80 already listening MySQL service and so the application would fail since it would find the port already busy. To overcome this problem, you can tell the web server onto which port to listen to specifying it as the last parameter, i.e:
> sudo ./HIDWebServer 90
Similarly, on the web browser you will have to choose the right port to link to, if different from the 80 default one, as depicted in figure 3.
PortaDalBrowser.png
                                Figure 3: how to choose a port from the web browser
, assuming in this case the Raspberry has been assigned by the 192.168.1.7 IP. Being a web server, it has been designed to support N simultaneous connections from many web browsers. As you will see in a further section about system implementation, the server is actually used in order to create a request queue which are processed in their arrival order (the other option, actually not used, would need to use the server in multithreaded mode, but taken the didactic aims of this article as granted, we avoided to overdesign the system in that way). The system will have to be able to manage the asynchronous update requests from each browser in parallel. The MySQL database will be then queried by the main thread (the one in which the web server is running) being filled up by the reader threads (which run in parallel). The MySQL system is natively multithreaded, so it can serve a reading query while other threads are writing on the same DB, opening more connections on the same DB. Assuming two HID readers, a software functional diagram describing this is shown in figure 4.
SchemaFunzionale.png
                                                    Figure 4: SW functional diagram
One of the faced problems is the simultaneous “Recognise” button pressure from many web browsers. As a workaround to this, the button pressure is actually emptying the DB content, in order to restart with a new acquisition from scratch. This has been mandatory, thinking about the routine which scans the readers actually works. Since, it doesn’t guarantee the same sequence on every execution, we would have faced the risk to see two readers swapped, leading to a table no more coherent about the readers ID numbers. The table is then emptied on every recognition. It may happen a user with an opened web browser can see its table to be emptied suddenly, due to “recognise” button pressed on a remote web browser. During the execution, it has been taken into account one or more readers can be unplugged from the board (even them all, eventually). In this case, the system will report the following Debug warning on the shell:
> HIDWebServer is listening on port 90
> Exiting Thread 1
where the number near the Thread is related to the reader ID number as detected on the HIDWebServer start-up. Notice how on the web browser the number of readers will be decreased accordingly. At that point, the Thread where was running the channel related to that reader will be closed as well, but the remaining numbers will stay as they are until the very next recognition by the dedicated button (clearly, this is to keep the table consistent between two consecutive recognitions). The system is then scalable, i.e. it can detect any number of readers (up to fours on the Raspberry, having found USB ports available), self-configuring itself on start-up or on the “Recognise” button pressure, dynamically creating the threads hosting the many found readers.

DESCRIPTION AND IMPLEMENTATION

From a functional POV, the system is made by the Raspberry PI and the RFID readers, plugged to it by the USB bus. The board can join the network by an ETH port or a WiFi dongle, as shown in figure 5.

SchemaHW.pngFigure 5: HW functional diagram

One of the most important libraries for this project is the Database Management System for sure: MySQL, i.e the most famous database manager in the world. In this case, your author chose to install it directly from the Raspbian available package, instead of downloading its sources and compiling them from scratch (this because the library is quite wide and your author wanted to keep it simple). About the HID device recognition and management part, the choice is the HidApi library. This is a multithreaded library (i.e. capable to run different program parts in parallel within the same process, called threads) which can detect HID devices connected by a USB to a given Host where the library is running, telling the library which devices have to be found given their VendorID and ProductID couple. Since your author chose to keep the system simple without constraining it to a given reader, all HID devices are detected.
At that point, the library returns a handle (i.e. a C language pointer) to the device and all library functions used further on that device will have to be supplied by this handle, in order to know where to work onto. The power of this library guarantees each channel opened on each device is sealed, i.e. the characters arriving from the to devices would not be mixed leading to troubles. The choice to put further each channel in a Thread, guarantees to be able to work in parallel listening on the two Threads without they even didn’t know about each other. Each Thread is then made by a simple loop (look to the ThreadCbk function in Command.cpp) where the library function which reads the characters put itself in wait sending the Thread to sleep until something has been read: this really unloads the Raspberry PI microprocessor which can focus itself in the MainThread (the one in which the web server is running) while it’s sleeping in the other threads for the most of the time (consider a RFID badge human interaction which last definitely longer than the microprocessor time ticks). The system has not been ported to Windows  because the library has known multithreading problems on this platform (the library author seems to have worked on them in a recent past) and so this OS is not fitting the native multithreaded nature of this project. In the following section, you will be told about how to compile these libraries from their sources instead of downloading their packages by the Raspbian apt.
About the web server, your author chose to use the multithreaded library microhttpd,
a really light web server mostly used for embedded application like this project. As told, the server can work either with a waiting queue, serving a request by another (this is the choice adopted for the sake of simplicity) or managing a connection for each Thread. Also this library will be compiled and installed from sources. The last library used, but not less important, is the pthread standard library, which stand for Posix Threads and it’s the most used on Linux: it’s thanks to this library if we can use the above depicted thread management and which supplies an interface towards the OS kernel. This can be installed from the Raspbian repository, while when compiling under OsX is already there.

HOW TO INSTALL THE RASPBIAN DISTRO AND THE DEVELOPMENT AUTOTOOLS

The distro mounted on the Raspberry SD is the Raspbian, derived from Debian, which will be downloaded from the net and copied on FAT32 formatted SD. Once prepared and mounted the SD on the board, all is ready for the first system switch on. All the installation up to the application launching will be done directly from the Raspberry shell exposed by the telnet client (ssh or similar) or the serial debug cable, as described in the previous sections. After the login, typically you will be under /home/pi, since it’s the user with whom you will have accessed the system, as shown in figure 6.

RaspiLogin-1024x422.png

Figure 6: login as PI user

The first trick to be applied soon after the installation is to tell the Raspberry you want to use all the available space on the SD. Without this care, the system would use by default   the minimum space needed by the distro image only, without letting the user to add new further files anymore (without this trick, the MySQL installation in the next section would raise space errors).

You can tell the Raspberry to use the maximum available space like this:

> sudo raspi-config expand_roots

and selecting the first menu entry, like in the figure 7.

RaspiExpandRootfs-1024x663.pngFigure 7: how to expand the SD filesystem

Some libraries will be downloaded directly form the Internet, while for other ones you will use the sources, compiling them from scratch. To achieve this last operation you will have to use some development tools, called autotools, which will be downloaded and installed by this command:

> sudo apt-get install autoconf automake libtool

At that point, all is ready to compile and install the libraries needed by this project.

 MYSQL DBMS INSTALLATION

The first must have library for the project is the DBMS (Database Management System) MySQL. Similarly to all other DBMS, this library is split in a server side, which abstracts the databases towards one or more clients. In the current project, each launched HDIWebServer becomes a client of the unique server installed on the Raspberry. The library to be installed will be the libmysqlclient-dev, where the -dev postfix means it’s a development library containing all the include, the static and run time libraries needed to compile the application. You can achieve this using this command:

> sudo apt-get install libmysqlclient-dev

The bash shell will answer asking to install the dependencies, i.e. the library libmysqlserver, specifically the runtime version (not the development one), since the application will have to link against it. During the installation, the server will ask to set the Database root user password. You can skip this password leaving it empty, since it will be configured further when the installation will be ended, with this command:

> mysqladmin -u root password gandalf

The password “gandalf” can be found within the sources as well, when it’s opened the connection in CRFIDDB::CreateDBAndTable, which is the wrapper which hides the MySQL details. Obviously, if you chose a different password here you will have to update it within the sources as well, i.e. in CreateDBAndTable. You can now finally enter the MySQL environment, executing the commands by the interpreter, as you can see from the figure 8, where you entered the dedicated environment and you ask the list of  existing databases (between them the RFIDDB):

MySQL-1024x659.pngFigure 8: the MySQL interpreter

Notice how this environment can be used in parallel to the running application, without they even know each other. This is possible since MySQL is a transactional system which admits more parallel connections and automatically resolves the conflicts, mirroring the updates among many clients.

COMPILING THE HIDAPI LIBRARY

We are going to install the hidapi directly from the source package. First of all, let’s download the sources under the folder /home/pi:

> git clone https://github.com/signal11/hidapi.git

This will create a hidapi subfolder under the /home/pi, which will be populated as shown in figure 9.

hidapifolder-1024x425Figure 9: /hidapi folder content

Now you can configure and install the whole library writing:

> cd hiapi
 > ./boostrap
 > ./configure

The ./configure execution will complain with some errors since it will not be able to find some libraries from the hidapi depends. You will have to install them in advance by the Raspbian package manager like this:

> sudo apt-get install libusb-1.0-0-dev
 > sudo apt-get install libudev-dev

At this point you will be able to rerun the configure finalizing the installation by:

 > ./configure
 > make
 > sudo make install

This will populate the hidapi subfolder under /home/pi. as depicted in figure 9.

COMPILING THE MICROHTTPD LIBRARY

The last library we are going to install is the microhttpd, which will manage the web server application side. Let’s first download the source package like this:

> wget http://ftp.gnu.org/gnu/libmicrohttpd/libmicrohttpd-0.9.34.tar.gz

Navigating the download library page, you will see the last version is the 0.9.49 (at the time of writing), while we have decided to keep the 0.9.34, as you can see from the following figure 10.

libmicrohttpd-1024x678.pngFigure 10: web server available libraries

This is because when this project started, the 0.9.34 was the most recent one. You can try to use the last one for sure, but you can bet you should make some small change to the function prototypes, creating some new compilation errors. Once downloaded the zipped sources, you will be able to unzip them by:

> tar -xvf libmicrohttpd-0.9.16.tar.gz

Entering in the just created folder, you will write the usual command triple used in the majority of package configurations:

> ./configure
 > make
 > sudo make install

COMPILING AND RUNNING THE APPLICATION

The last missing puzzle piece is the application source package, which can be downloaded from github by git, like this:

> git clone https://github.com/riccardoventrella/HIDWebServer

To finalize the whole thing, you can enter the just created folder writing:

> cd HIDWebServer
 > make

If all went properly, you will find an executable called HIDWebServer, which will be launched like in figure 11.

HowToLaunch-1024x428.pngFigure 11: how to launch the application

The application is now listening to the default port 80. Opening with a browser the page from the Raspberry, you will be able to see the RFID badge updating in real-time, as depicted in figure 12.

RFIDTags.pngFigure 12: Browser with the list of RFIDs

CONCLUSIONS

In this article has been described a simple RFID badge or key reader system, using the Raspberry as data collector by a MySQL DBMS. The system lets the data inspection and navigation exposing a web server which page can be browsed by any device connected by the net to the Raspberry. Thanks to both the web server and MySQL multiuser and multiconnection nature, we have been able to let many users to read data in parallel from many devices at the same time. There can be many possible system extension, oriented to didactic purposes. Even if  any Android device with a web browser could be used without the need of installing anything, we can think about a future Android app targeting this behaviour. Mounting some push mechanisms on this app could be interesting as well, since the Android device can be remotely notified that a RFID badge has just been presented and registered by the Raspberry. A similar approach can be applied for iOS devices as well, keeping into account their push mechanisms are pretty superimposable. To sum it up, this system could be used for production purposes as well, like a company badge access system to an office restricted area. A similar technology is used in professional animal welfare systems, where the RFID are swallowed by the animals as RFID transponders.