Introduction: Sheevaplug + Python Daemon + MySQL = RS232 Logging Made Easy

About: Founder of Powerhouse Electronics. For more info goto: www.jimk3038.com

So, you've got some kinda RS232 device and you want to log a bunch of data from it. No problem. What you need is a little Sheevaplug. The Sheevaplug is a 5 watt full blown solid state Linux box running at 1.2 GHz. Now, how cool is that?

Here is a link for a bunch more info on the Sheevaplug:
http://en.wikipedia.org/wiki/SheevaPlug

I think the Sheevaplug is just so darn neat. All that juice in the palm of your hand.

Just recently I re-flashed my old Sheevaplug with the new Ubuntu 9.04 Arm build. After the re-flash the USB/RS232 cables started working for the first time. Seems there was some problem with the old Debian build.

OK, so here is the deal, I'm playing with a bunch of Zigbee radios that generate a few RS232 messages every second. I want to log all those messages so I can analyze them later. Just shoving all those strings into a text file would not be difficult. But, that makes it hard to handle. Plus, it would make it hard to look at the data while the data being collected. Also consider, I'm collecting data for weeks and weeks. Just how big that text file would grow is anybodies guess.

The solution to large amounts of data is a MySQL database. And the solution to pumping data into a MySQL database is Python.

Read on to understand all the plumbing involved in getting your Sheevaplug to pack all that data into a database for you.

Can you tell I'm rather smitten with the Sheevaplug? Disclaimer, although it sounds like I'm a salesman for the Sheevaplug, I'm not. I have no personal interest in the plug other than I think it is cool.

Step 1: Add Flash Memory to the Sheevaplug.

I wanted my data stored using the SD-Card slot on the plug. I think that just adds to the "coolness" factor.  With an extra 4Gig SD-Card on hand I used the following couple of commands to format and mount the new SD-Card into the file system.

Use this command to delete the FAT partition / make a partition new: "fdisk /dev/mmcblk0"
Use this to format the new partition: "mkfs -t ext3 /dev/mmcblk0p1"
Note, the format command takes a minute, or more, to execute. I had a hard time waiting.
Add this string to /etc/fstab: "/dev/mmcblk0p1 /usr2 ext3 noatime,commit=600,data=ordered,barrier=1 0 3"
Use this command to mount the new file system: "mount -a"
Your new SD-Card should now be mounted on /usr2

You can now test your new file system. I tested by copying some files onto, and off of, "/usr2" as a test.

I learned a lot of really good stuff from this English bloke's web site: http://www.earth.org.uk/note-on-SheevaPlug-setup.html This guy uses the Sheevaplug to log/control his home's solar panel installation. Seems to really know his stuff - very good info.

Step 2: Install: Python Serial Module / MySQL

The Sheevaplug comes with Python already installed. However, to read and write to the serial ports (USB serial ports included) an extra python module needs to be installed. Use the following apt-get command: "sudo apt-get install python-serial". The database stuff is just as easy: "sudo apt-get install mysql-common".

With these two added packages you now have almost all the secret sauce needed. All that remains is a little Python code to tie the serial port messages to the MySQL database.  

The following python code opens a serial port and a database connection:
ser = serial.Serial( '/dev/ttyUSB0', 115200, timeout=1 )
conn = MySQLdb.connect( host='localhost', db='vam', user='james', passwd='passwd' )
curs = conn.cursor()

Then, use following line to read lines of text from the serial port. If the length of "s" is greater than zero then you know there is new info in the string.
s = ser.readline().strip("\r\n")    # Either return a string, or timeout.

Now, parse your string into fields you want to pump into the database.

The next couple of commands do the heavy lifting of getting the data into the database.
t = datetime.datetime.now()
curs.execute( 'insert into RouterDat (MAC, udt, PMAC, RSSI, Temp, Voltage, Battery) \
    values (%s, %s, %s, %s, %s, %s, %s)', (mac, t, pmac, rssi, temp, volt, batt))
conn.commit()

Note, the Sheevaplug has a NTP (network time protocol) daemon running in the background. So, all the data is time-stamped with really accurate time values. Just more coolness.


Step 3: Getting MySQL to Use SD-Card

By default, the MySQL database will not get stored onto the external SD-Card. This is simple to change, edit the file using the following command "sudo vi /etc/mysql/my.cnf". Look for the line starting with "datadir" and change it to "datadir = /usr2/lib/mysql".

Then, make an exact copy of "/var/lib/mysql" to "/usr2/lib/mysql". It's important to get all the directory and file ownership permissions set correctly. If not set correctly you will get error messages when you restart the mysql daemon.

If your ready, restart the mysql daemon with "sudo /etc/init.d/mysql restart". If all goes well, your all set to create a new database on the SD-Card. It took me half a dozen attempts before I got all the permissions and ownerships set correctly. I'm sure there is some swift Linux command that preserves the owner and group permissions. But, I messed it up with my simple "cp" command. A lot of "sudo chown" commands got it all sorted out in the end.

One other quick note, you can also have remote machines log data into your new MySQL database. However, there is a trick that took me a long time to figure out. To fix the problem, find the line "bind-address = 127.0.0.1" in "/etc/mysql/my.cnf" and comment it out by prefixing the line with a "#". Another restart on the daemon and your all set.

Step 4: SQL Tools

There is one other tool your going to need to. The folks over at MySQL have a free open-source tool that runs on Linux/Mac/Windows. Here is a link: http://dev.mysql.com/downloads/gui-tools/5.0.html

I find the Query Browser tool really handy. With the Query Browser tool you can create databases, tables, and views. Very handy. The following query pulls all the data for one router out of my database. The data is sorted, filtered, and reduced. If you look closely, the query reduces the returned dataset to only those samples that fall at the top of the hour. Otherwise, with samples recorded every minute, I would get flooded with returning data.

The following example defines a "view" that is also stored in the database.

CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`james`@`%` SQL SECURITY DEFINER VIEW `RouterDatView` AS select hex(`RouterDat`.`MAC`) AS `0xMAC`,hex(`RouterDat`.`PMAC`) AS `0xPMAC`,`RouterDat`.`udt` AS `udt`,`RouterDat`.`RSSI` AS `RSSI`,format((`RouterDat`.`Temp` / 10.0),1) AS `Temp degC`,format((`RouterDat`.`Voltage` / 100.0),2) AS `Voltage`,format((`RouterDat`.`Battery` / 100.0),2) AS `Battery`,`NodeInfo`.`Notes` AS `Notes` from (`RouterDat` left join `NodeInfo` on((`RouterDat`.`MAC` = `NodeInfo`.`MAC`)))  where `NodeInfo`.`Notes`="Pole Mounted Solar Router" and extract( MINUTE from udt ) = "00" order by `RouterDat`.`udt` desc

Step 5: Final Thoughts

Just today I saw a new device come across Hack-A-Day called the GuruPlug.

Here is a link to the next generation of Sheevaplug called a GuruPlug.
http://www.globalscaletechnologies.com/p-32-guruplug-server-plus.aspx

With the new plug, you can have WiFi & Bluetooth build right in - still only 5 watts. The price did get bumped up a bit to $130 bucks. Still, to get WiFi and Bluetooth for only an extra $30 bucks, the price still sounds good to me.

I hope this got you motivated to play with the GuruPlug or SheevaPlug. Logging all your serial data into a database is not all that hard. Once you've got your logging working, using SQL to get just the right data back is really cool.

I'll also post some other info on my web site: "ph-elec.com". I'll put up some extra source code that takes your Python code and turns it into a daemon that runs in the background. So, once kicked off, the Python daemon just simply handles anything coming in on the serial port.

Now, how cool is that!