Go for an Open Source Database in a Lab

This article seeks to answer the question, “Does a contract analysis laboratory need big, commercial software to manage the workflow shown in Figure 1?” The answer is a very short and emphatic, “No” And this is because of FOSS.

The workflow
A contract analysis laboratory operates in the fields of analytical chemistry, microbiology and industrial hygiene. It provides its customers chemical and microbiological analysis, physically measures (noise, vibrations, etc), and offers various types of technical consultancy related to water, soil, air, workplace hygiene, food, textiles, home products and industrial products. In other words, it is closely associated with the environment and a lot of other things.

The workflow shown in Figure 1 is a bit simplified and has the following explanation. An analysis request comes from a customer, and one or more samples are sent to the lab. When the sample arrives in the lab, it must be registered and stored before the next step is taken. Then the analysis is carried out using traditional methods like titration, etc, or instrumental methods (chromatography, spectroscopy, etc). This step requires materials, reagents and instruments supplied from some suppliers. The results of each measurement are written in a sample notebook (either printed on paper sheets or recorded digitally). When the analysis is completed, the results are reviewed by one or more persons. This step depends on the company’s policy about quality assurance and can also be called the validation (primary and/or secondary) process. Last of all, an analysis report is written with all the results and comments, and is signed – usually by one or two people. The signing step depends on local laws. The person who signs the report is usually a biologist, a chemist or both. Other professionals can also analyse and sign, depending on what the law of the country requires and the report’s contents. The report can be sent to the customer on paper or in a digital format that’s electronically signed.

The toolbox

In my work experience, I have encountered only one company that uses GNU Linux as a desktop system (which had been installed by me!). So, I think that to have something that is usable by all, FOSS on Windows should be considered at least from a desktop point of view. For this application, the following software has been chosen:

• MariaDB, as database engine, available for GNU Linux too (obviously). Due to the simple database structure, this application can be quickly transferred to MySQL, PostgreSQL, etc. It would probably be necessary to begin a discussion about the choice of MariaDB instead of MySQL. In this example, the choice has been made mainly because with MariaDB, I get all that I need for basic use, if PHP is an option, without installing any other software.
• HeidiSQL, as a database manager. It is written in Object Pascal and is for Windows only. In my opinion, it’s the best available. I can install HeidiSQL with MariaDB (it’s an option during the installation process) or the last release as independent software. I haven’t tested it under Wine, so I can’t say more.
• GNU Emacs, as a notebook and report builder via org-mode. An org-mode file is a text file saved with the extension ‘org’ instead of ‘txt’.
• Uniform Server (http://www.uniformserver.com) as provider of the Apache Web server. This is necessary to run some PHP scripts. No installation is required – just unpack and use it. Or Wampserver (http://www.wampserver.com/en) as an alternative. I haven’t tested other alternatives.

The database
The database structure is shown in Figure 2. The primary keys are highlighted in blue and the foreign keys in green. There are six tables. The table called Inventory is about the reagents. What’s most important about this table is the expiry date and the hazards information. With a simple query, I can know the reagents stored in a certain place, e.g., in a fridge, and the expiry date for each of them. All the information about the instruments is stored in the table called Instruments. Even in this case, with a simple query, I can know, for instance, all the programmed maintenance dates. The table Samples is the most interesting, because it stores all the information about the samples. In this table, the most important things are: the five dates for registration, delivery, sampling, analysis start and analysis end; the customer’s name and the alternative for it (in case the report needs to be addressed to a company that’s not the customer), the sample notebook and the analysis report (see the next section). For a small company, this table has about 3000 to 5000 rows per year (each row is a sample). The other three tables (Customers, Suppliers and Resources) are useful but not very interesting because they are, more or less, like a phone book. The database credentials can be settled via the User manager, an option of HeidiSQL. Some easy SQL query examples are shown below:

# Reagents expired
SELECT id,product_name,quantity,brand,no_lot,date_exp,stg_device FROM inventory WHERE date_exp < CURDATE() ORDER BY product_name

# Reagents that will expire within the next 30 days
SELECT id,product_name,quantity,brand,no_lot,date_exp,stg_device FROM inventory WHERE date_exp BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY) ORDER BY product_name

# Reagents stored in a certain device
SELECT id,product_name,quantity,brand,no_lot,date_exp FROM inventory WHERE stg_device=’storage-device-name’ ORDER BY product_name

# Samples from a certain customer
SELECT id,date_rgt,type,label,status FROM samples WHERE customer_name=’customer-name’ ORDER BY date_rgt

The database structure is too long for a printed article, but it’s freely (as in freedom and in beer) available at https://github.com/astonfe/mariadb.

GNU Emacs

GNU Emacs, among other things, has a very powerful org-mode. Building and formatting tables in this mode is very fast (http://orgmode.org/worg/org-tutorials/tables.html). The result is a text file that can be read with a lot of other software. Here, the possibility of exporting a file from org to html is a very useful feature. The sample notebook (Figure 3) is an org-file containing the parameters to be measured on the sample and some other information about it. The analysis report (Figure 4) is an enlarged version of the notebook with other information also (for example, the customer’s name and report comments, if any). The notebook and the report’s ‘htmlized’ file paths are both stored in the database (the word ‘htmlised’ is a bit unusual, and is taken from http://orgmode.org/manual/Publishing-action.html). The files on the hard disk can be organised into two directories and some sub-directories: notebooks (or templates) and reports, each with sub-directories (for example: generic, customer A, customer B, customer …).

Print with PHP

A printable version of the notebook and of the report can be built using a PHP script. This script contains a query to extract the information to be put on the document and includes the html file corresponding to the notebook or the report. The two tables shown in Figure 5 have been a little shortened to have a document of one page (two pages would be too long to be presented here) and the numeric values are for explanation purposes only. The methods are taken from Reference 2 and the report notes from Reference 3, given at the end of the article. If an error is recognised when the report has already been sent to the customer, to trace the two different versions of the printed report, the date and time of printing are at the bottom of the page. In this case, it’s appropriate to also take some notes in the field notes and/or in the field report notes.
Due to the high flexibility of this system, I can write all the PHP scripts that I need and my www directory on Uniform Server can contain a lot of PHP files (Figure 6). The same holds for the SQL queries to be used directly in HeidiSQL. Because the database is used mostly for reading information from it, the PHP files are very useful to view the tables and to extract information from them. HeidiSQL can be used mainly as writing software. Obviously, as an alternative, more complex PHP scripts (or other languages/technologies) can be written to edit and add/delete records. For example, the information can be summarised by ‘list’ or by ‘form’. A ‘list’ is a table containing all the records, in which each record is listed in one row and the columns are composed of some particular fields only. A ‘form’ is a table containing all the fields for each record (something like a key-value table). This is explained by the following code; in the ‘list’, only seven fields are used while the ‘form’ has 21 (all) fields:

// Inventory “list”
<?php
$con=mysql_connect(“localhost”,”root”,”qwerty”);
mysql_select_db(“lims”,$con);
$query=mysql_query(“your-query-here”);
mysql_close($con);
$num_rows=mysql_num_rows($query);
echo”Total: $num_rows records”;
while($row=mysql_fetch_assoc($query)) {
echo”<tr>”;
echo”<td>”.$row[‘id’].”</td>”;
echo”<td>”.$row[‘product_name’].”</td>”;
echo”<td>”.$row[‘quantity’].”</td>”;
echo”<td>”.$row[‘brand’].”</td>”;
echo”<td>”.$row[‘no_lot’].”</td>”;
echo”<td>”.$row[‘date_exp’].”</td>”;
echo”<td>”.$row[‘stg_device’].”</td>”;
echo”<td><a href=’inventory_form.php?id=”.$row[‘id’].”’ target=’_blank’><img src=’pics/form.png’></a></td>”;
echo”</tr>”;
}
?>

// Inventory “form”
<?php
$con=mysql_connect(“localhost”,”root”,”qwerty”);
mysql_select_db(“lims”,$con);
$id=$_GET[“id”];
$query=mysql_query(“SELECT * FROM inventory WHERE id=”.$id);
$row=mysql_fetch_row($query);
mysql_close($con);
echo”<tr><td>Id </td><td>”.$row[0].”</td></tr>”;
echo”<tr><td>Product name </td><td>”.$row[1].”</td></tr>”;
echo”<tr><td>Product name alt</td><td>”.$row[2].”</td></tr>”;
echo”<tr><td>CAS number </td><td>”.$row[3].”</td></tr>”;
...
echo”<tr><td>Signature </td><td>”.$row[19].”</td></tr>”;
echo”<tr><td>Notes </td><td>”.$row[20].”</td></tr>”;
?>

The database presented here is very basic and there is much scope for improvement. A system based on ‘something-SQL’ and PHP has high flexibility and is adaptable to specific needs. Considering the same ISO standards about quality assurance, different contract analysis laboratories can have quality policies very different from each other. So the choice of software and how to use it is not a problem. It’s always possible to find a way that satisfies both the technical standards and the company’s needs. You can visit http://en.wikipedia.org/wiki/List_of_LIMS_software_packages for some interesting examples of much more complex software. In my opinion, for the kind of applications described here, commercial software do not have any significant advantages.

References
[1] http://orgmode.org/worg/org-tutorials/tables.html, last visited on 10/05/2015.
[2] Clesceri, Greenberg, Eaton (editors), Standard methods for the examination of water and wastewater, APHA, Washington, 1998.
[3] http://it.lipsum.com, last visited on 10/05/2015.
[4] http://en.wikipedia.org/wiki/List_of_LIMS_software_packages, last visited on 10/05/2015.
[5] Mercer, Kent, Nowicki, Mercer, Squier, Choi, PHP 5, Hoepli, Milan, 2004.
[6] Zakas, McPeak, Fawcett, Ajax, Hoepli, Milan, 2007.