• MySQL/MariaDB to excel

    Sometimes you just need to export some data and have it ready in a spreadsheet to email to sales and marketing. Yes i know a csv is a better storage format but the objective is to send the data in excel format so that people use to *just* using excel can carry on doing their work.

    Turns out this is really easy to do on a linux server.

    First create some sql query, for example pulling sales data from a sales table, and using the stock_details table to get its name where the data is a specific brand.. It does not matter really its going to return a fixed no of columns based on the query, lets call this query.sql

    use stock;
    select sales.barcode,stock_name,delta from sales,stock_details where sales.barcode=stock_details.barcode AND stock_details.brandid=1;

    Next we have a little script to do the magic (getsales.sh) :-

    dir=$(mktemp -d)
    mysql -u ROUSER --batch < query.sql > "$dir/sales.txt"
    tr '\t' ',' < $dir/sales.txt > $dir/sales.csv
    libreoffice --headless --convert-to xls:"MS Excel 97" $dir/sales.csv --outdir $dir
    echo "sales report" | mutt -a "$dir/sales.xls" -s "Sales report" -- sales@example.com

    This has a number of steps. Firstly run the mysql query in “batch” mode this dumps the results as tab separated values. Next libreoffice choked on tab separated so we use tr to translate to comma separated, may be an infilter option would cure this. Then we feed the comma separated into libreoffice in “headless” mode and this generates the Excel file for us. Finally mutt is used to write an email with the attachment.

    One thing to note, the msql user in this example ROUSER must have prompt-less  READ ONLY access to the required database/table(s) By prompt-less i mean that the password is not required to be entered each time. To achieve this create a file ~/.my.cfg with the following contents


    where thepassword is the actual password for ROUSER.  Remember to set permissions on this file to be read/write only to the user. Also remember to make the user in the database only have read only access to the things it needs for additional safety.

    Now stick that in a cron job and bingo, automatic spreadsheets via email!


  • Simple (c#) message broker using Nanomsg

    I was attempting to do some CanOpen development using CanOpenNode in a 100% windows simulated environment where different apps all pretend to be different nodes and talk to each other via a simulated bus. Now message busses in software are not uncommon things but many are quite heavy going and for the magnitude of the product a think like DBUS was just not needed. Enter Nanomsgnanomsg is a socket library that provides several common communication patterns. It aims to make the networking layer fast, scalable, and easy to use. Implemented in C, it works on a wide range of operating systems with no further dependencies.” It also has support for a BUS type socket so seemed awesome on the surface.

    First problem is the exact implementation of the bus socket is not exactly the same as one would expect when creating an electrical bus. Its simply not possible to just wire all the test apps together and have them talking with out some kind of extra glue in the middle. With nanomsg it is necessary to manually connect every node to every other node to form the bus. Some info is given here but the implementation detail is missed and stumped me for a while.

    If we consider 3 apps all trying to form a bus via IPC then we have to do the following to make the system work :-

    App 1


    App 2


    App 3



    App1 has nothing to connect to so we create a socket with bind, App2 Can connect to app1 but also creates a socket. App3 just binds to app1 and app2’s socket. Nanomsg will take care of the rest and ensure that no matter who broadcasts all nodes receive. Clearly this is only suitable for a fixed implementation where number of nodes and node locations is hard coded. Not really what i had in mind. Enter the message broker. Now i’m not claiming this is efficient or the most elegant way to solve this problem, but its simple and flexible which are the key goals here for the testing i wish to do.

    The idea with the simple message broker is to open a number of sockets (in the example 10) then each app will just connect to the appropriate socket. We can decide which one to connect to as i’m simulating a canopen node bus so each node/app has an ID from 1-127 so that makes a perfect sufix to use on the IPC eg can_id1 can_id2 etc.. If we wanted to be more elegant and remove hardcoded things to the next level we could add an interface to the broker that can be contacted by a new node that allocates a new socket on demand and returns its id. But this is good enough for the my requirements… The broker will listen to each of the multiple sockets then rebroadcast anything it gets to all OTHER sockets. As the sockets are all bi-directional it works as expected for a bus topology

    using System;
    using System.Threading.Tasks;
    using NNanomsg;
    using NNanomsg.Protocols;
    namespace MessageBroker
        class Program
            const int nosockets = 10;
            static BusSocket[] s = new BusSocket[nosockets];
            static NanomsgListener[] l = new NanomsgListener[nosockets];
            static void Main(string[] args)
                Console.WriteLine("Starting message broker");
                for(int x=0;x< nosockets;x++) { s[x] = new BusSocket(); s[x].Bind(string.Format("ipc://can_id{0}",x)); l[x] = new NanomsgListener(); l[x].AddSocket(s[x]); l[x].ReceivedMessage += Listener_ReceivedMessage; } while (true) { Parallel.ForEach(l, (lx) =>
                         lx.Listen(new TimeSpan(0));
            private static void Listener_ReceivedMessage(int socketID)
                //New data on socket
                byte[] payload = s[socketID].ReceiveImmediate();
                //Send new data to everyone other than the sender
                Parallel.ForEach(s, (sx) =>
                    if (sx.SocketID == socketID)
  • First test of the RaspiHats Isolated IO module


    First tests of the Isolated IO boards from http://raspihats.com/ This is the 6 in/out module featuring 6 relay isolated outputs and 6 opto isolated inputs. 3 Boards are currently being produced. A relay output board, an isolated input board and the mixed board shown here.

    I’ve connected it here to a system under test to simulate a PLC interface for verification. The Raspberry PI is pretending to do the PLC handshake with our equipment to verify its all working to specification.



    Taking to the board from the Raspberry PI is exceeding easy, for a start its a HAT module so supports the auto set up that newer PIs support. The only thing you need to do is enable the PIs I2C from the rasp-config tool (see here https://www.raspberrypi.org/documentation/configuration/raspi-config.md)

    After I2C is enabled ensure you have the following packages installed via apt-get

    sudo apt-get install python-smbus python-pip

    then using pip install the raspihats package

    sudo pip install raspihats

    If you want a video walk through of the setup you can find one below :-


    And here is my python test script simulating some of the PLC. The pupose of this script is to simulate some exteral hardware they we do not have to test the machine against. The particular handshake is defined by our specifications but it shows how easy a test script can be written and the RaspiHats modules used

    from raspihats.i2c_hats import Di6Rly6
    from time import sleep
    import time
    board = Di6Rly6(0x60)
    #force reset
    #board.do_set_state(7,1) # only 6 relays this is forced 0
    while (1):
    print("Waiting for ready signal")
     #wait for ready signal
     print("Not ready")
    print("system is ready")
     print(" .. Moving head to load position and requesting vac")
    board.do_set_state(0,1) # req vac
    #wait for vac on signal
     #do nothing
    print("VAC on requesting a cal cycle")
    board.do_set_state(2,1) # req cal cycle
     #wait for done signal
     #do nothing
    #read passfail
    if board.di_get_state(5)==1:
     print("** ITS A PASS **")
     print("** ITS A FAIL **")
     board.do_set_state(2,0) # clear cal cycle request
    print("robot head in position, signal vac off")
    #move head and turn on robot vac then say our vac is on by deasserting vac req signal
    print("waiting for vac off comfirm")
    #wait for vac off signal
     #do nothing
    print("Cycle complete")
    #loop around and wait for ready signal
  • Fix orphan image attachments in wordpress/woocommerce

    I ended up with a load of orphan image attachments. By which i mean the post_parent of the image attachment was 0. This is bad in one particular situation. Although all the images were in use on posts, Google has a habit of indexing the attachment pages as well and often these are not themed and do not contain the info you want end users to see, For example http://example.org/product/attachment/1234/. Its possible to use plugins to add no-index no-follow meta tags to the attachment pages that will remove them from search. But this seemed a bit half-arsed. Plugins such as Yoast SEO have options to redirect attachment pages back to the parent post. But this is useless if the post_parent is 0.

    So i came up with the following quick hack to run on the server from a ssh prompt.

    $sapi_type = php_sapi_name(); 
    if (substr($sapi_type, 0, 3) == 'cgi') 
    { die("You are using CGI PHP\n"); } 
    require ( ABSPATH . 'wp-admin/includes/image.php' ); 
    $args = array( 'posts_per_page' => 1000,
            'offset'           => 0,
            'category'         => '',
            'category_name'    => '',
            'orderby'          => 'date',
            'order'            => 'DESC',
            'include'          => '',
            'exclude'          => '',
            'meta_key'         => '',
            'meta_value'       => '',
            'post_type'        => 'product',
            'post_mime_type'   => '',
            'post_parent'      => '',
            'author'           => '',
            'post_status'      => 'publish',
            'suppress_filters' => true
    $posts = get_posts($args);
    foreach ($posts as $post) {
       // Get post title
       $post_thumbnail_id = get_post_thumbnail_id( $post->ID );
       $parentid = wp_get_post_parent_id( $post_thumbnail_id );
       if($parentid == 0)
                            'ID' =>  $post_thumbnail_id,
                            'post_parent' =>  $post->ID

    This script finds each post of post_type, gets the featured image ID, then looks up this to see if the post_parent is 0. If it is 0 it sets the post_parent of the image to the post ID determined before. The first few lines ensure that this script is only executable from the command line/ssh prompt so that if you leave this on your web-server it cannot be executed by the http process. However i would encourage removing it directly after use.

    The things to note with this are:-

    1. This will run on the first 1000 posts, if you need more, probably best to create an outer loop and use that offset parameter
    2. post_type is set to “product” this is good for woocommerce product posts, for regular posts make this “post”
  • CanOpen on a Raspberry PI using CanFestival

    I wanted to hook a Raspberry PI into a CanOpen network and have it as a slave device, mainly for logging purposes. I had previously identified CanFestival  as a potential CanOpen stack to be used for this project as it describes itsself as “CanFestival focuses on providing an ANSI-C platform independent CANOpen® stack that can be built as master or slave nodes on PCs, Real-time IPCs, and Microcontrollers” so that is sounding promising.

    The other potential stack I had considered was CanOpenNode, this stack wins hands down for implementing on a PIC32/24 as it pretty much works out of the box on those platforms. The devil is always in the details and in this case the detail is the driver, or how the stack connects to the actual can hardware. Both stacks have certain “drivers” that allow them to interface to the host, both stacks as C implementations that just seem to work. But the winner was CanFestival supported socket_can.

    Socket_can is an implementation of can using unix sockets, this might not seem like a big deal, but its very efficient. Sockets have built in queuing mechanisms and expose them selves to usespace in a nice friendly way. Through put is not going to be an issue. Other CAN systems use character devices, eg they emulate a serial port and you talk one byte at a time to the character device. This is inefficient and has no queuing, so on busy networks things could go wrong.

    The hardware selected was the PICAN board from SK Pang (UK Supplier). Quick delivery, the board arrived next day and I was excited to start playing with it. I am using a Raspberry PI 2 and there are slight differences between the 1 and 2 when setting up the drivers on the PI (hint PI2 is much improved)

    Can board on raspberry pi 2

    Complete instructions can be found on SK Pang’s blog but the TLDR; version is in raspi-config, advanced -> turn on SPI. then add to /boot/config.txt the following


    Thats it, a reboot later everything should be good the correct kernel modules will load at boot and the can interface will be ready to go.


    The purpose of this was CanOpen not just raw can so lets proceed with Festival, we will do this on the raspberry PI as it should be ready to go with code compiling and will save the hassle of a cross compile setup for such a trivial thing.

    Download the source code from http://dev.automforge.net/CanFestival-3/ , you can check out the code as a Mecurial repository, or if you don’t know what that is, just download the latest source from http://dev.automforge.net/CanFestival-3/archive/tip.tar.bz2 which will get the latest code as a tar ball with bz2 compression.

    wget http://dev.automforge.net/CanFestival-3/archive/tip.tar.bz2
    Unpack the code
    tar -xvjf tip.tar.bz2
    Change directory to move into the source tree, Note at the time of writing the tip ID was 8bfe0ac00cdb when you do this it may have changed so just cd into the folder just unpacked which will be named CanFestival-3-?????????????
    cd CanFestival-3-8bfe0ac00cdb
    Now configure and build the code
    ./configure --arch=armv6 --target=unix --can=socket
    sudo make install

    So thats good to go at this point and you should be able to test, an annoying niggle is that the can interface needs to be brought up with the “link” command, i am sure this can be fixed in festival but as it stands out of the box to run the first thing you need to do is :-

    sudo /sbin/ip link set can0 up type can bitrate 500000
    CanOpenShell load#libcanfestival_can_socket.so,can0,500k,1,1

    Where the paramaters for load are library name, can channel, bitrate, nodeID and master(1)/slave(0)

    If CanOpenShell starts correctly you should see


    at this point press enter

    You can then sent NMT commands with the ssta/ssto/srst/scan commands eg ssta#0 start all nodes, and you can read and write SDOs with the rsdo and wsdo commands
    so you can talk to other CanOpen devices and prove you have a working network.

    Creating a node with festival

    So how do you actually use festival to do something real? The best example to start with and the simplest is in examples/SillySlave and is a very basic demo of a CanOpen slave on the network. Of cause sillyslave didn’t compile out of the box with the rest of festival and to get it to work do the following, firsty move to the /examples/sillyslave directory and edit the Makefile( eg use nano Makefile -c) on line 42 go to the very end and remove “-lcanlib”

    Next you also need to edit main.h and change the following defines in the section marked “Please tune the following defines to suit your needs:”

    #define NODE_MASTER 0x1
    #define NODE_SLAVE 0x40
    #define DRIVER_LIBRARY "libcanfestival_can_socket.so"
    #define BAUDRATE 500
    #define BUS 0

    Next you need to build as follows :-
    make mrproper

    This is important as the SillySlave.c file is out of date and old and needs to be regenerated, only the mrproper target will remove it, not just make make all or clean. What it actually does is generate SillySlave.c from an XML representation of the object dictionay and using another tool this can be converted to/from the EDS/electronic data sheet files that is part of the CanOpen standard.

    To run the sample just do :-


    If you have another can node, you can start/stop the node with NMT commands, and it will send a PDO for every SYNC it receives.

    The important thing to take home here is main.c is trivial, its the minimum needed to start up the application, SillySlave.c is machine generated and this is the goodness of the object dictionary and how you configure the main parts of your node.

    Creating an object dictionary with festival

    So the time has come to make the Sillyslave something real, your friend in this process is the objdicteditor tool, which is a python tool

    You need to run this from a GUI, either the PI’s X windows or another system (even windows) with python and python wx installed. Lets assume you are using the PI still. Firstly ensure you have the wx widgets for python
    sudo apt-get install python-wxtools
    Then change to X windows and use a keyboard and mouse to continue
    Open a terminal window and CD to the directory containing the canfestival source, then cd into the objdictgen directory and do the following python objdictedit.py Then if you go to File->Open abd browse to the examples/SillySlave directory and open SillySlave.od you can edit the object dictionary from a nice GUI with ease, any changes you make if you save them back to the od file you can then build this into the slave by following the previous steps to build sillyslave with your new changes.

    Canfestival object dictionary editor

    A few finishing words

    Sillyslave is very basic, the bare minimum and really the defines for the nodeid and the bitrate are wrong and these should be read from the object dictionary directly. If you need to add more advanced functionality CanFestival has callback hooks which can be used, but i leave developing a real application as an exercise for you to do on your own as this is where the business end of what ever you are trying to do actually starts now all the boiler plate is taken care of.

  • Ustream videos from the command line on a RaspberryPi

    As i’ve just got my hands on a 7″ offical Raspberry PI display and a PI 2.0 i was playing around and thought what is the easiest way to get some interesting video on the screen. So i though as a test i would use the live ISS HD feed from NASA as that is on Ustream and is a pretty cool thing to have on.

    After some quick research omxplayer was the way to do this. And http://blog.miguelgrinberg.com/post/watch-live-video-of-earth-on-your-raspberry-pi did exactly what i needed

    So I installed omxviewer, python-pip and livestreamer (using python-pip) and the basic command is livestreamer http://ustream.tv/channel/iss-hdev-payload mobile_480p --player omxplayer --fifo

    I changed it to

    livestreamer http://ustream.tv/channel/iss-hdev-payload mobile_480p --player omxplayer --fifo --player-args "--blank {filename}" to ensure it blanked the unused parts of the screen.


    NASA ISS HD feed running on omxplayer on a Raspberry Pi

    NASA ISS HD feed running on omxplayer on a Raspberry Pi

    Lots of playing to be done, but for the moment on with some serious work…

  • Add facebook posts to wordpress blog?

    I could not find any Facebook feed readers I was happy with. There are loads of plugins that provide sort of what I wanted, Facebook content could be displayed on a specific page using tags. Or you could use a rss feed reader and try and parse the Facebook rss for a page. This was not that easy and ended up with small images and incomplete sets of images.

    The only real way to get Facebook data is via Graph API

    So i wrote a WordPress plugin and its is here


  • Ban hammer, fail2ban geo ip on google maps

    Though it would be fun to display the banned IP addressed on the server from fail2ban using google maps and geo ip, a quick google revealed this was already done so my solution is based on two following code bases for inspiration:-



    I started off with the dbsysnet code but modifed this to use google maps V3 API, then i stole the nice UI and map images and other stats ideas from fail2ban-geo, then rewrote the data fetcher to use JSON and ended up with the current version.

    The current live version is at


    [Read More…]

  • A z80 assembler in C# with IDE

    Having a number of legacy z80 projects that still need supporting it was about time I wrote a modern z80 assembler and IDE to manage this problem.

    We had been using AD2500’s z80 assembler which was all fine, but only 16 bit, no ide no other fancy features. But of its day it seemed like a pretty rock solid assembler. I can’t really be bothered to install XP in a VM just to run the old 16 bit apps. So i’ve started a new project to create a modern C# based IDE and assembler to help out those that still need this stuff or those who just want to learn some assembler for education. z80 is a nice place to start with assembler to learn some basics before diving in to more complex processors.

    Below is an image of the work so far :-


    The code can be found at



  • node.js securing the username/password database

    Following on from my last post, I decided to have a look at a better solution for password hashing as the last example just used SHA1 which is far from optimal, so here are some changes to handle hashing via a pbkdf2 function which is far far better and far more secure.

    [Read More…]