dale lane

I’ve written about my CurrentCost meter that I’m using to monitor my home electricity usage, and the small home server that I’ve set up to collect the data.

Yesterday, I decided to make a start on collecting the data. My plan was to copy what Nick had done and create a MySQL database to store the info, with a table to store a timestamp and the watt reading from the CurrentCost meter.

CREATE TABLE currentcostdl (  
    time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  
    power FLOAT NOT NULL,  
    primary key(time));

I started with Nick’s Python scripts, but didn’t get very far.

The server, a NSLU-2 (or “slug”) is running SlugOS.

The problem was that there is no python-mysqldb package in the SlugOS repositories. I did try downloading the source for it from sourceforge to build it myself, but struggled to get the dependencies I needed to make it – urllib in particular was a big pain.

I also tried the debian package but again dependencies on SlugOS got in the way.

So I gave up on that and decided to do it myself using Perl – Perl and MySQL had to be easier, right?

Erm… not so much 🙂

In short, I ran into the same problem. There isn’t a MySQL module for Perl in the SlugOS repositories.

And building the standard Perl ones runs into dependency problems.

Then I found a pure Perl implementation on CPAN. From the module description:

This module implements network protocol between server and client of MySQL, thus you don’t need external MySQL client library like libmysqlclient for this module to work. It means this module enables you to connect to MySQL server from some operation systems which MySQL is not ported.

Hurrah, this sounded like what I needed.

So I downloaded the archive, and unzipped it.

There was a Makefile.PL perl script to run. Before I could run it, I needed to install a few pre-requisite Perl modules:

ipkg install perl-module-io perl-module-extutils-makemaker perl-module-extutils-makemaker-config perl-module-vars perl-module-config-heavy perl-module-autosplit perl-module-cwd perl-module-extutils-install perl-module-extutils-packlist perl-module-extutils-command perl-module-extutils-command-mm perl-module-extutils-my perl-module-extutils-manifest perl-module-extutils-mm perl-module-extutils-mm-unix perl-module-file-spec-unix perl-module-exporter-heavy perl-module-carp-heavy perl-module-file-glob perl-dev perl-module-pod-man perl-module-getopt-long perl-module-test perl-module-test-harness 

Once run, this creates the make files you need – so it’s make and make install as normal.

It seemed to build okay, but my first attempt to use it failed with a runtime dependency:

dalelane@minipooter:~$ perl -e 'use Net::MySQL;' 
Can't locate Digest/SHA1.pm in @INC  
BEGIN failed--compilation aborted at /usr/local/share/perl/5.8.8/Net/MySQL.pm line 681. 
Compilation failed in require at -e line 1. 
BEGIN failed--compilation aborted at -e line 1.

I needed Digest/SHA1 – but that’s not available in the SlugOS repositories:

dalelane@minipooter:~$ ipkg list perl-module-digest* 
perl-module-digest - 5.8.8-r25 - perl module digest 
perl-module-digest-base - 5.8.8-r25 - perl module digest-base 
perl-module-digest-file - 5.8.8-r25 - perl module digest-file 
perl-module-digest-md5 - 5.8.8-r25 - perl module digest-md5 
dalelane@minipooter:~$

I started looking for a digest-sha1 module. I found the source for an Optware one on ipkgfind.

First attempt to build it failed because of missing dependency:

root@minipooter:/var/tmp/Digest-SHA1-2.11$ make 
cp SHA1.pm blib/lib/Digest/SHA1.pm 
make: *** No rule to make target `/usr/share/perl/5.8/ExtUtils/typemap', needed by `SHA1.c'.  Stop.

There is no perl-module-extutils-typemap for SlugOS, so I got my own copy of the two files I needed from CPAN:

wget http://ftp.funet.fi/pub/CPAN/src/perl-5.8.8.tar.gz 
tar -xzvf perl-5.8.8.tar.gz 
cp perl-5.8.8/lib/ExtUtils/typemap /usr/lib/perl5/5.8.8/ExtUtils/typemap 
cp perl-5.8.8/lib/ExtUtils/xsubpp /usr/lib/perl5/5.8.8/ExtUtils/xsubpp

Then I edited the Digest-SHA1 Makefile so that it wouldn’t try and build them itself – replacing the XSUBPP line with:

XSUBPP = /usr/share/perl/5.8/ExtUtils/xsubpp

Running the make for Digest-SHA1 needed one more pre-req module:

ipkg install perl-module-re

Unfortunately, it then failed due to missing executable:

/bin/sh: ccache: not found

I needed to find ccache for SlugOS. I couldn’t find a pre-compiled version anywhere, so I downloaded the source for ccache from samba.org.

Initial attempts to compile it failed because it was missing ‘as’ (assembler?)

arm-linux-gcc: error trying to exec 'as': execvp: No such file or directory

I hadn’t done any compiling on the slug before, and a quick attempt to compile a ‘Hello World’ C app showed that I hadn’t got a working C compiler. From what I’ve read, SlugOS-Native is a good way to get your system set up for this.

I downloaded SlugOS-Native from ipkgfind and tried installing it. It is a tiny package that includes a ton of pre-reqs – the idea is that installing this forces ipkg to go and install everything you need for native development on the Slug.

Note: I had to ignore a dependency on the monotone source control package, but that didn’t seem to be a problem – it continued to successfully install a lot of development/compiler packages. In fact, so many that I Ctrl-C’d it after a while, as it was installing a ton of perl modules that I didn’t think I’d want.

With a proper development environment now set up, I was back to trying to build ccache:

./configure 
make 
make install

This worked fine, so I was back to trying to make digest-sha1. It failed on another perl module dependency:

ipkg install perl-module-extutils-mkbootstrap

But with that out of the way, I was then able to:

make 
make install

So I was back to trying out the Perl MySQL module – which had failed on the runtime dependency on Digest/SHA1 in the first place!

perl -e 'use Net::MySQL;'

Success!

Or so I thought. A quick perl test script to connect to my currentcost database failed:

Can't locate auto/Digest/SHA1/reset.al in @INC 

The reset method in SHA1 called the method in Digest base.pm, so this was easily fixed by installing

ipkg install perl-module-digest-base

And it now works fine.

My code to insert an update into my MySQL table looks something like this:

use Net::MySQL; 

my $mysql = Net::MySQL->new( 
   database => 'currentcostdb', 
   user     => 'dalelane', 
   password => 'myPassw0rd' 
); 

my $insertquery = "INSERT INTO currentcostdl (power) VALUES ($watts)"; 

$mysql->query($insertquery); 

printf "Affected row: %d\en", $mysql->get_affected_rows_length; 

$mysql->close;

Hurrah – it works.

Okay, as Gareth pointed out this is really overkill. The reason why API support for MySQL on the Slug isn’t great is probably because MySQL isn’t the best choice of DB to run! Support for other less resource-intensive databases, such as SQLite is much better, and I could have got up and running in a fraction of the time with a couple of ‘ipkg install’ commands:

root@minipooter:/var/tmp$ ipkg list | grep -i sqlite 
libsqlite-bin - 2.8.17-r2 - An Embeddable SQL Database Engine 
libsqlite-dbg - 2.8.17-r2 - An Embeddable SQL Database Engine 
libsqlite-dev - 2.8.17-r2 - An Embeddable SQL Database Engine 
libsqlite0 - 2.8.17-r2 - An Embeddable SQL Database Engine 
libsqlite3-0 - 3.5.2-r0 - An Embeddable SQL Database Engine 
libsqlite3-dev - 3.5.2-r0 - An Embeddable SQL Database Engine 
python-sqlite3 - 2.5.1-ml5 - Python Sqlite3 Database Support 
sqlite3 - 3.5.2-r0 - An Embeddable SQL Database Engine 
sqlite3-dbg - 3.5.2-r0 - An Embeddable SQL Database Engine

But that wouldn’t have been nearly as much fun 😉

The slug is now sending updates to the realtime server here, and storing them in a local MySQL database.

Now I’ve just gotta think of something to do with the data!

Tags: currentcost, mysql, nslu, nslu2, perl, python, slug, slugos

This entry was posted on Tuesday, May 27th, 2008 at 2:52 pm and is filed under code, tech. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.