Month: April 2018

Posted on

Creating a Small Data Storage Server

A friend of mine is creating an Arduino-based monitoring system for their garden. The input will be a humidity sensor and the output will be a simple flow controller to a tank of water (solenoid).

Sure, that is great when you want to just have everything “work” but I’d like to take it a step further. I want to be able to verify that it works (aside from, of course, looking at whether the plants are dead.) So to achieve this, we’ll need plots.

The Desired Goal

Over time, we’ll be collecting data. This could be live (via a wifi connection) or on-demand (via a USB connection or an SD card.) Either way, a simple plotting interface where a metric could be selected, and a date range could be specified would be an ideal interface for this type of system. Essentially a simple look like this:

From this, a metric like “humidity” or “valve state” could be selected, and a date range specified. A super crude MSPaint-like example would be something like:

From that type of plot you could easily infer things like when it rained and whether or not the system is actually functioning.

The Building Blocks

In order to achieve this goal, there are four essential pieces that need to fall in line. These are:

  1. Data recording
  2. Data transfer
  3. Data storage
  4. Data visualization

Data recording is inherent to the Arduino, and is not something that I’m concerned with.

Data transfer is another piece of the architecture I won’t be concentrating on. This is because it should be trivial when 1 and 3 are in place. It’ll either be a periodic MYSQL function call, or a script that parses and inserts data to a databse.

Where my (very limited) experience can be of most benefit is in the data storage realm. This will likely bleed into the (Pun Alert!) uncharted territory of Data Visualization.

An Aside

To me, this seems like it must be a problem that everyone has solved a million times over. There should be a pre-built LAMP instance that does this. Unless I’m looking in the wrong places, I can’t seem to find a simple one. There are things like Weave that seem to be an open-source alternative for Tableau. This seems like overkill.

There is no shortage of potential solutions. Other suggestions include GNU’s gnuplot, or the Python MatPlotLib, or even my favorite: GNU Octave. but all of this seems horribly inefficient. Maybe my ignorance of web development is really showing, but none of these feel like the right solution. Alas, I’m moving forward with my intuition that a MySQL database will be necessary and the last piece of the puzzle will just fall in place.

(Note: After starting this post, I found this solution, which seems to be a pretty awesome starting point)

Data Storage

Starting with what I know, I’ll want to implement a database. My experience has been almost exclusively in MySQL, so I’ll start with that. I should start with a container, but at the risk of losing some 1337 points, I’ll start with a VM. In this case, Xubuntu 18.04 because… why not?

Install MySQL

Here’s the easiest part of it all. Just install MySQL Server on the newly-created VM with the command

sudo apt install mysql-server

Another helpful tool is mysql-workbench. Install that with

sudo apt install mysql-workbench

Those two installs take up a cool 350MB of disk space, so it takes a minute or two minutes to install.

Then you’ll need to create a user:

echo "CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';" | sudo mysql

After that, you can run mysql from your user’s account with:

mysql -u username -p

Create The Database

We’ll need a simple database, with a simple table as a proof of concept. When the project blows up to include multiple solenoids, sensors, arduinos, etc. this won’t suffice. But that’ll be another project of its own.

In order to not break MySQL completely open, we’ll want to use the root account to create a new database. This DB will be fully controllable by the account we just created, but we don’t want EVERY database on the server to have this hole. So for this:

echo "CREATE DATABASE sampleDB;" | sudo mysql
echo "GRANT ALL PRIVILEGES TO sampleDB.* TO 'username'@'localhost';" | sudo mysql

Now you have the ability to move to the next step:

Create The Table

Now we can start using the database. For this instance, it might help to think about the “database” as an Excel Spreadsheet, and the “table” as “Sheet 1” of the XLS. Many Excel spreadsheets don’t go past Sheet 1. At lest most of the ones I use.

Anyway, from here we can enter the MySQL prompt. Every line will have this at the beginning, which I’ll omit in the future.

mysql>

Create the simple table that contains a date, a humidity value, and a control value:

CREATE TABLE sampleTable (pk INT NOT NULL AUTO_INCREMENT, time DATETIME, humidity INT, control INT, PRIMARY KEY (pk));

You should then see that the table was created from the command

SHOW TABLES;

Add some data

To finish this side of things, we need to add a couple of sample values. At least two so that a plot can be made. For this, a simple insert should suffice.

INSERT INTO sampleTable(time, humidity, control) VALUES (CURDATE(), 100, 0);
INSERT INTO sampleTable(time, humidity, control) VALUES ("2018-04-28 01:00:00", 95, 1);

This should leave you with two values inserted in the table. From here, simple 2D plots can be made!

These insert commands are essentially what will be used to log all data moving forward.

Prepare The Front End

At this point, I’m in uncharted territory. Fortunately, the post I found has some pretty straightforward suggestions, which I’ll use as a base. First, install the webserver.

sudo apt install apache2

After this, you should be able to navigate to the loopback address (127.0.0.1) from a web browser. Look for the “It works!” message.

At this point, we have the LAM part of the LAMP server functional. The last piece of that which is necessary is the PHP. This can be installed by running

sudo apt install php7.2 php7.2-gd libapache2-mod-php7.2 ttf-mscorefonts-installer

After this, restart apache with

sudo service apache restart

We can then get the latest build of JPGraph from their website, or by using wget:

wget https://jpgraph.net/download/download.php?p=18

Untar this and install it to /var/lib/php:

tar -xf jpgraph-4.2.0.tar.gz
sudo mkdir /usr/share/php
sudo mv jpgraph-4.2.0 /usr/share/php
sudo ln -s /usr/share/php/jpgraph-4.2.0/src /usr/share/php/jpgraph

In the /usr/share/php/jpgraph directory, theres a jpg-config file. This needs to be updated with the following lines

define('CACHE_DIR','/tmp/jpgraph_cache/');
define('TTF_DIR','/usr/share/fonts/truetype/msttcorefonts/');
define('MBTTF_DIR','/usr/share/fonts/treutype/');

Lastly, test this installation by adding the JPGraph examples to your website:

sudo cp -r /usr/share/php/jpgraph/Examples /var/www/html/

The site http://127.0.0.1/Examples/example0.php should now show you a simple graph. Hooray!

Tie It All Together

We’re finally able to plot stuff. The last thing is to request what we want to plot from MySQL. For this, I came up with the smallest example I could make to a file inside var/www/html:

<?php
require_once ('jpgraph/jpgraph.php');
require_once ('jpgraph/jpgraph_line.php');
require_once ('jpgraph/jpgraph_error.php');

$servername = "localhost";
$username = "colin";
$password = "password";
$dbname = "sampleDB";

$y_values = array();
$x_values = array();
$i = 0;

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
}

// Run the query
$sql = "SELECT time, humidity FROM sampleTable";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
        while($row = $result->fetch_assoc()) {
                //echo "when: " . $row["time"]. " humidity " . $row["humidity"] . "
";
                $y_values[$i] = $row["humidity"];
                $i++;
        }
}

// Close the connection
mysqli_close($conn);

// Make the graph
$graph = new Graph(800,500);
$graph->img->SetMargin(40,40,40,40);
$graph->img->SetAntiAliasing();
$graph->SetScale("textlin");
$graph->SetShadow();
$graph->title->set("Example");
$graph->title->SetFont(FF_FONT1,FS_BOLD);

$graph->yscale->SetGrace(0);

// Make the plot
$p1 = new LinePlot($y_values);
$p1->mark->SetType(MARK_FILLEDCIRCLE);
$p1->mark->SetFillColor("red");
$p1->mark->SetWidth(4);
$p1->SetColor("blue");
$p1->SetCenter();
$graph->Add($p1);

// Draw everything!
$graph->Stroke();
?>

This creates the simple plot of the two values we’ve entered.

What’s Next

To learn PHP, I guess…

It seemed like I couldn’t print info and plot from the same interface. Perhaps that means that everything shown on the screen has to be it’s own PHP file. In that case, this graph could be embedded into any other site in a hierarchical manner.

Otherwise, the next steps would be to:

  1. Add the ability to export the data to CSV
  2. Correct the X-axis. It currently plots against the index
  3. Add a start date and stop date
    1. This would be a parameter passed into the PHP script?
  4. Automate data importing from the Arduino

To be continued…