Making great progress with the Raspberry Pi despite not knowing Linux from a hole in the ground a week ago. We’re at our place in Spain for a short break with limited facilities (I’ve ordered a USB scope for the next time we’re here).
As of this morning, I’ve managed to get Node-Red running on the Pi 2 (well, the more important modules anyway including Twitter, email, ports and MQTT and I can turn the facility on and off as a service. A hell of a lot of the modules have dependencies that don’t seem to want to load). I also have MYSQL in there.
I have just finished a PHP page running on Apache with JP-Graphs (free and wonderful) all on the Pi 2, taking data from MYSQL and plotting.
So what you’re looking at above is dummy data on the Pi, served on a Pi web page – the blue line is coming from the MYSQL database but after I’ve finished writing this and entering more dummy data all 3 will come from the database. The next stage will be to take in MQTT message from ESP8266 devices which will be sent to mysql every minute but logged less frequently until I find how more about how the Pi uses the SD memory – bearing in mind this stuff has limited write life. writing rules for temperature control will be a different matter as I’ve no gut feeling for the scripting language yet.
Coming along quite nicely – between that and the discovery that I can get NETIO to talk to Node-Red, I might not need OpenHab after all.
On the Arduino side we’re looking at making a development of what we call the Aiduino, an Atmega1284-based 5v board, taking it down to 3v3 and sticking an ESP-12 on the end – why? Because I have it on good authority that we’re near to getting some code to handle both MQTT and serial control for the ESPs… which makes matching them with Arduino good sense – the Arduino can handle the intensive stuff like fading serial LEDs without interfering with incoming MQTT messages. Very exciting stuff.
Don’t ask me how I installed Node-Red, it was bodged but before long the Node-Red site itself will have updated info for Pi 2 no doubt.
It did not help that the Node-red block “mysql” doesn’t exactly have millions of examples – but with a little help from the one of the guys maintaining it – I cracked that, too. Here’s an example of getting a value into a mysql database from MQTT.
So I have an MQTT node taking in from the topic “testtemperature”. It passes this through a function node which turns that into a SQL INSERT. Here’s the code in the “function” block…(with luck this will save some of you a lot of messing about)
var newMsg = { payload: msg.payload };
newMsg.topic="insert into readings (location,value) values (\"Kitchen\","+newMsg.payload+")";
return newMsg;
And the final bit the MQTT node – it is simply expecting the query in the incoming topic – it simply connects to the local mysql database and runs that query.
A few values added using mqtt-spy….
Note that last one at 99… and the updated graph shows this! This is real and working, not a dummy.
I’m storing a title and a value in the database – so I can extract the latest values for each of 3 different inputs along with an automatic timestamp. I’ve not quite figured out how to ensure they all match up time-wise yet… but it’s a start….
Might I suggest you try chartjs (www.chartjs.org) which is also free and makes lovely graphs with curved lines.
Good to know that you are working on Aiduino. Few weeks back I took the decision to separate out the concerns and let ESP handle all the network related things and Arduino does what it does best. I have nodeMCU using MQTT on ESP and making calls from Arduino to ESP is as easy as calling a function. I am still working on making calls from ESP to Arduino and complete the whole protocol stack between them. Do you intend to make Aiduino information public or much better or you planning to source the hardware from someone/somewhere? It will be existing to know your progress on this.
There is no software in Aiduino – it’s just a board. I would like to extend the non-nodeMCU version of MQTT to handle communication with Arduino etc via serial.
What I meant was, are you going to make the design of the board public/open source or is it something just for your personal use? or are you going to produce it in mass for selling. Correct me if I am wrong but what I understand was that you are designing was arduino and ESP8266 on a single board.
i admire that you have what it takes to do this – in a way i am jealous – it is so hard to delve into new technologies and actually make them work. You know what they say about sufficiently advance stuff and magic
I do indeed know the saying about magic Syd… and believe me I’m right on the edge here, learning new stuff by the minute because, well, if I want results I have to. Right now I have a problem that anyone with MYSQL experience will grasp immediately but is escaping me. If I store 3 lots of temperature/humidity settings (along with the date and a unique identifier for filtering, ie “kitchen”, “outside” and “humidity” – or whatever… If I pull each of them out of MYSQL into an array – then fine – I can show a graph with 3 of them superimposed. BUT if any one of them MISSES the odd update, then they’ll be out of sync and I can’t right now get my head around this one…. if anyone finds that simple – by all means dive in here…
The way i did it (i think) a while ago – I had indoor and outdoor sensors with nrf24 radios – I was working in Windows and SQL server. I queried the database for each sensor separately and presented it to the graph tool I was using at the time (probably Excel). The key was the timestamp. so if a sensor missed a poll – the tool just went from the old point to the next point based on the time stamp. Like sending it to xively and getting the data merged into one graph. Depends on how intelligent you tool is.
If you are getting it into a multidimensional array (select 1,2,3 from table) the 0’s (or null) values can confuse the graphing program. there is this thing called a union query that might work based on your table layout. Let me dig around and find that code…
That would be good – so I store 3 items – the name of the location, the time, the value…. so I can filter by location… what I need is to be able to retrieve a series of values the same length and start and end times even if there was a missing value.