MyWind MariaDb / MySQL Database

I was both surprised and pleased to see a flury of recent interest in my MyWind database on GitHub, there have been 18 forks in the last month!

MyWind is a re-engineering of the Northwind database provided with Microsoft Access for use with MariaDb and MySQL. Northwind was a sample database and tutorial schema for managing small business customers, orders, inventory, purchasing, suppliers, shipping, and employees.

I provided MyWind using the BSD license, meaning you are free to use MyWind as you please, including commercially, so long as you keep my copyright notice and accept my disclaimer of liability. Enjoy!

Using GTKWave and DatPlot to View Time Series Data

Sensor-based IoT devices often measure attributes of their environment, such as temperature, battery voltage, signal strength, etc. This data can be presented as a time series, which is a sequence of values obtained at successive points in time. The time between values may be fixed, such as every 1 ms, or it may be irregular or event-driven, such as only times when the value exceeded some threshold.

Many times it can be instructive to view time series data graphically to more readily perceive “the big picture”. Time series data can be plotted using a spreadsheet program (e.g. Excel®) or general purpose numerical analysis application (e.g. R and MATLAB®-compatible GNU Octave). However, a domain-specific program can focus on the job at hand and be more effective.

I naively first thought there would be no shortage of open-source or free-for-use applications to select from, but web searching proved that was not the case. When my eyes started to blur, only GTKWave and DatPlot appeared potentially suitable.

GTKWave

GTKWave was originally developed for viewing Verilog digital-only circuit simulations, although it now can also display real-valued signals. GTKWave will read Verilog VCD/EVCD format files, as well as LXT, LXT2, VZT, FST, and GHW formats. It is provided under the GPL open-source license, and is cross-platform (BSD/Linux, 32-bit Windows®, and OS X®).

GTKWave supports dynamic zooming and panning, has a time marker to display values at a specific time, and can be used with datafiles too large to fit into memory without loss of usability. It is also possible to use GTKWave with streaming data instead of a static input file. However, GTKWave will not read simple delimited text data, such as a CSV-format file. Also, while the vertical axis scales automatically based on the value range of a signal, the scale is not labelled and cannot be set manually.

Based on the user manual and provided data files, GTKWave appears to be useable with a large number of signals and very large data sets. The following screenshot shows the magnetic field around a small toroidal magnet.

tlv493-arduino-02-gtkwave

DatPlot

DatPlot was developed by an aerospace engineer as a better solution for visualizing flight test. The DatPlot tagline is From raw data to report ready plots in under five minutes. Like GTKWave, DatPlot supports dynamic zooming and panning and the vertical plot axis scales automatically like GTKWave, but unlike GTKWave the vertical axis is labelled and an arbitrary number of time markers (called Event Lines) can be displayed. In addition, DatPlot supports annotations which can be used to improve clarity. DatPlot is not open-source, although it is still free to use.

I have not yet been able to explore the usability of DatPlot with a large number of signals (Data Curves) or with very large data sets. The following screenshot again shows the 3D magnetic field around a small toroidal magnet (although not the same dataset shown by GTKWave).

tlv493-arduino-03-datplot

Summary

While GTKWave showed promise based on support for very large data sets, and for being open-source, its lack of vertical scale ultimately made it unacceptable for my immediate use case. GTKWave’s lack of support for simple delimited text files was only a minor inconvenience, as it was not difficult to write data values to a VCD-format data file.

DatPlot does what it claims to do, and is a nice compromise between static graph creation and interactive analysis. I will continue using DatPlot, but a few additional features would really make it shine.

  • Paging quickly forwards and backwards in time (e.g. by holding down Page Up or Page Down keys, instead of only being able to drag left or right in a Graph Pane).
  • Display a specific time in a dataset (instead of dragging to the desired point).
  • Support for CLI operation (e.g. using command parameters or a parameter file to specify source data file, desired Graph Panes and Data Curves, and output image file name).

Epilogue

During my research into a time series data viewing, I came across ROS.org (Robot Operating System). ROS uses bag files to store serialized ROS message data, and provides rqt_bag and rqt_plot utilities to view the data, including plotting fields on a graph. Embedded systems in general (not just robots) often need to store system messages for debugging or forensic analysis, and using the existing ROS bag-file and utilities could save significant effort compared to developing a similar system from scratch.

Manipulating CSV-format files on FreeBSD

Conclusion Updated 2013-08-16.

The initial Maestro implementation site uses CSV files for intermediate data exported from legacy systems. I’m using CSV files because they are human readable and provide a point of control between the legacy systems and Maestro. They also provide an opportunity to manipulate and report on the data using simple command-line Unix tools.

Doing some cursory research on command-line CSV-file manipulation, solutions mentioned most often included:

  • csvfix is a command line tool that reportedly does everything you’d want, including order/select columns, split/merge, generating SQL inserts from CSV data and diffing CSV data, and has a permissive license.
  • csvkit is a collection of programs adhering to the Unix philosophy (a program should do one thing and do it well), intended to be to CSV files what grep, sed, cut, sort… are to text. csvkit is licensed under the permissive MIT license.
  • csvprintf is a simple UNIX command line utility for parsing CSV files, similar to the printf utility. cvsprintf is written in C, and is licensed under the permissive Apache License, Version 2.0.
  • csvtool is included with the OCaml CSV library.
  • GNU Recutils is a set of tools and libraries to access human-editable, plain text databases called recfiles, with support for CSV file import and export (the recfile concept seems similar to SQLite, but recutils seems to have better csv support than SQLite – e.g. escaping the field delimiter when it occurs within text, supporting \r\n (carriage return – line feed) within a text string, etc.
  • Perl, AWK and R were also mentioned, but working solutions appeared non-trivial for the uninitiated (sorry Randall).

so let’s see what compiles on FreeBSD 8.3…..

csvfix

csvfix is available as a Windows binary from the project site with the csvfix source on BitBucket. Compiling (using “make lin” for Unix/Linux) resulted in a g++ option error.

From the csvfix wiki, compiling requires a modern version of g++ with C++2011 support – and reportedly uncompilable on Mac OS X by people who know a lot more about compilers than me, so that’s as far as I’ll be going with csvfix for now.

csvkit

csvkit is written in Python, and requires mkvirtualenv and pip, which I unfortunately don’t have any experience with (yet). I’ll postpone further csvkit investigation for now.

csvprintf

First, I tried the instructions in the download archive:

> tar -xzf csvprintf-1.0.3.tar.gz
 > ls
 csvprintf-1.0.3 csvprintf-1.0.3.tar.gz
 > cd csvprintf-1.0.3
 > ls
 CHANGES Makefile.in configure csvprintf.h xml2csv
 COPYING README configure.ac main.c
 INSTALL aclocal.m4 csv.xsl scripts
 Makefile.am config.h.in csvprintf.1.in svnrev.c
 > ./configure
 checking for a BSD-compatible install... /usr/bin/install -c
 checking whether build environment is sane... yes
 checking for a thread-safe mkdir -p... scripts/install-sh -c -d
 checking for gawk... gawk
 ...
 checking for iconv_open in -lc... no
 configure: error: required function iconv_open missing
 >

Making a wild stab in the dark (because I don’t know better), I installed iconv-2.0_4 from the ports tree – and got the same error (you’re probably saying “I could have told you so!”).

Next, although from googling it seemed there was a possibility of GNU Linux autoconf miss-reading my FreeBSD system (and iconv_open capability), I tried updating libiconv to see if that changed anything. Unfortunately, portmaster reported my version of Perl (5.10) was out-dated, which I tried updating to 5.16 (instead of the current 5.18, because I read a May 2013 post saying 5.18 broke a lot of modules).

portmaster -o lang/perl5.16 lang/perl5.10

However, that didn’t end in success because I have apparently installed modules from both CPAN and BSDPan, and BSDPan modules can’t be updated using portmaster. I don’t have time to hunt where these modules came from, and if I even need then anymore! Grrrr.

csvtool

On the plus side, OCaml CSV is in the FreeBSD ports (ocaml-csv), but on the other hand, it pulls in a *lot* of dependencies I won’t use again (including graphics stuff). I went ahead and compiled the port anyway. It completed without error, but it seems csvtool isn’t included in the FreeBSD port (or at least it didn’t get installed). Grrrr.

Conclusions

I’m rolling my own. I’m following tutorials on the Python CMS module, and adapting them to my simple but unique needs – and I need to learn Python anyway for OpenERP (and to hang out with the cool kids). However, I would like to have a general-purpose go-to CVS utility, and look forward to eventually testing csvkit.