Monthly Archives: September 2015

ESP8266 Remote Access

Notice I titled that remote access as against remote programming – but that’s part of it.

Years ago, having gotten sick and tired of dismantling my home control Arduinos just to upgrade the code, I purchased a couple of small units from China which I’ve just thrown in the bin.   One had a USB plug on it and plugged into the PC, the other had an FTDI output on it. Both had those black 2.4Ghz antenna.

So the purpose of these units was to wirelessly replace an FTDI – or serial programmer, the idea being that instead of bringing the Arduino project back to wherever one programs the devices, you plugged the unit into the project and over the air programmed it.

At the time I was using Arduinos and those CHEAP £1.50 radio modules – the NRF24L01s, the worst radio boards in the world who’s range could be so bad you’d be lucky to get through one wall. I had high hopes for them with a great mesh network called RADIOHEAD, but ultimately their range and lack of ability to read signal strength doomed them.   However, as you can imagine they were dotted all over the house and I thought this little radio pair would solve the issue of programming them without ripping everything up.

It turns out the “radio FTDI” had the same problem – it was based on the NRF24L01.

Fast forward to September 2015. Here I am in Spain with a new setup of ESP-12 modules controlling lights, watering system and a lot more.  I’ve made some improvements to our “home control 2015” software in the ESPs and once again I find myself having to dismantle kit.

Now, I know that is it possible to program over the air – a few guys have tackled this successfully and indeed much of the code is built into the Espressif SDK. Right now my friend Aidan is working on adding this to our code but there are two issues, he’s not had luck up to now and secondly as our code comes to something like 300k you’re never going to be able to add this to the ESP-01 modules (we use ESP-01 sometimes, ESP-12 other times…)  as you need as much spare storage as you have code for OTA programming!

It was with that in mind that last night I dragged out the old FTDI boards to see if I could save myself the effort of dismantling the Pergola lighting system to update it.  Suffice it to say that I managed to get a few bytes programmed before that all came to an end despite several attempts. With around 25 WIFI units of one description or another dotted around the place, the old wireless FDTI (same frequency range) simply could not handle more than a few feet.


I figured it would not hurt to look around and see if someone has alternative wireless solutions – lots of time has passed since I bought that kit.

And so it was that I stumbled upon this. – as it happens it is quite new – essentially what we are looking at is a single unit (yes, that had me going for a while) which will hook into the WIFI system and act like an FTDI – given that you can fire the serial out of your PC over TCP to a port – port 23.   I grabbed the code (the stable 1.04 version) and very excitedly blew it into an ESP-01 unit I had lying around.

Really, nicely done web interface (with remnants of Martin Hanzarov’s code in there) and a really good write-up – indeed there may be enough info in there to help us implement our own FOTO (over the air) updates to our code.

But this has far wider implications – a virtual serial link would also let you monitor the serial line of a remote unit for debugging info – and our units do indeed put out lots of serial debugging info. It would also let you remote program and monitor un-wirelessly enabled Arduino projects.

So the first problem was – I had this unit sitting as on my network and I needed to blast info into port 23 to have it come out as 115K serial.  I have ECLIPSE sitting on the PC running my projects and there’s a line in the MAKE file that says “COM6”.  I have absolutely NO idea how to turn that into a TCP statement if indeed it can be done (FEEL FREE TO START TYPING IF YOU KNOW HOW).

So I turned to a 30 day trial of a PC program that promised to provide a “virtual com port” – and that it did. Serial/IP Redirector let me create a link to port 23 and ship raw data back and forth to COM22 (which doesn’t exist on my PC)- LO AND BEHOLD I set it running, put PUTTY on the PC talking to COM22 and with a real FTDI attached to the little ESP-01 board on real COM6 I set that running on a terminal –at 115K. Utterly flawless raw serial communications – rock solid.

I was getting excited by now. I took my ESP-01 board and cross-connected the serial to an ESP-12 test board. I held GPIO0 to ground and turned the power on, putting the ESP12 into programming mode.

I punched COM22 into the ECLIPSE project and hit the FLASH button on the project. Lights flashed on both units. With a scope attached  to the serial in on the ESP-12 I could see clean 115k data going into my project and… nowt, zilch, nothing.

I was up until the early hours ensuring the baud rate was ok and that my level conversion wasn’t getting in the way – you see all my projects use a resistive divider on the serial input of ESP boards so the whole lot will work just fine with 5v logic on the FTDI and elsewhere.  I wondered if hooking two of these units together with serial cross connected might prove too much but the scope says no – it says I’ve a perfectly reasonable serial signal going into the ESP-12 – but it’s not having it – no responding output whatsoever. Eclipse tries sending out data over and over and eventually gives up just as if there’s nothing connected.

So near and yet so far.

I got up this morning and the unit was still rock-solidly connected to the WIFI and typing a few characters provided perfect results at the other end – but I cannot program ESP units – yet.

Very exciting, yet very frustrating.

Perhaps coffee and a new start to the day will help. BUT I had to bring this to you as the article is really good, the software seems to WORK and there’s a super-bumper version with it’s own OTA updating in the works for larger targets like an ESP-12 (more FLASH) than the ESP-01.

I’m sure this is going to be a winner – just need to get it to work. After all, the ability to add remote programming to any FTDI-enabled project for what, a couple of quid.. well, you just can’t ignore it.

Meanwhile if anyone reading this firstly knows how to set COMX on Eclipse (on Windows) to TCP – do let me know – and if you really think you know what I’m doing wrong here and want to jump in before I figure it out – by all means – you have the keyboard…



ESP-12FThe ESP-12F seems to have appeared out of no-where. It appears to be nothing more than the ESP-12E – perhaps someone can enlighten us. It is cheap, no doubt about it at $2 inc. shipping or around what, £1.32 UK money? This things are now so cheap that the support circuitry around them is now the major cost….

So, nothing concrete but if you want to take a chance, AliExpress have them.

Between that and the SDK v1.4 it’s been an eventful week.

For beginners, there are a host of tiny WIFI-enabled microcontrollers under the generic name of ESP8266 – by ESPRESSIF (they make the basic chip). Variations between the various boards, ESP-01, ESP-03, ESP-07, ESP12/E/F etc. are minimal and involve varying amounts of FLASH memory (RAM etc. is the same in all of them) and different types of aerial, different pinouts etc.

The picture is straight off the Ali-Express site – so don’t take it too literally. If I understand correctly the pins along the bottom are not of too much use as they control the FLASH.


Raspberry Pi Monster Script Part 2

Updated 10/10/2015: This won’t make sense without reading part 1 first.  In the first of these articles I hopefully got you to the point of having basic access to your Pi2 from a PC – and actually running a simple script that says “Hello”. From there on it’s just a case of building on this.

There are tons of ways to say “Hello” on a BASH script (simple text file) and echo is one of them – but it’s a bit limiting and some of the flags confuse the hell out of me – another way is to use “printf” – if you’re a C person this will be immediately obvious – it’s SIMILAR but not the same as the C printf command. On your command line you can have a go at printing “Hello”


See that last line. This follows on from the last blog entry… where we were logging in as ROOT and testing a script – that finally produced “Hello”.

So what’s gone wrong here? The “printf” version seems to have buried “Hello” in a line. That’s because printf does NOT assume you want a carriage return or line feed after your text… Like the C version you put them in with \r and \n as needed – and trust me they both have their uses.


This is the kind of control I like. In the first instance I’ve entered “Hello there” followed by newline – that works just fine in Linux… and you can see the result. In the SECOND example, I’ve entered “Hello there” followed by return – which takes the cursor back to the start of the SAME LINE –  overwrites with some spaces – and then enters a new line.

So what could you use that for? Well, showing an incrementing timer on the same line for one thing – we’ll see that in use later. I is also easy to do right justification, colours and more.

I started to write scripts – then realised there’s a lot more to it than that – let’s say you want to capture a user password. There’s a command called READ which can put text you enter into a variable. In BASH scripts, variables are a bit weird, you update them by using them by name – but to access the information therein – you have to prefix them with $ –  here’s a great example – in a script – collect someone’s name – and print it out….

But first I should explain quotes… single quotes take everything literally, double quotes are special and let you “embed” things in there, hopefully a picture is worth 1000 words.


I actually did this on the command line – and you can right now if you’re all set up – but the general idea would be to do this in a script. So the READ command lets me enter “Hello there” into a variable called fred.   I can access fred as $fred… and you can see the text buried in there because I forgot the new line… but the magic is where I used double quotes and VERY simply accessed fred in the string!!! If it were not for the $ there would be no way for the interpreter to know that this was not simply the word FRED… method in the madness.

So being the type that thinks ahead instead of getting on with the job – I was thinking.. but… if I have this script and I use READ, what happens if I screw up the password?  Well, with many scripts – you’re stuffed – and sadly you will come across scripts – within installation packages that do this – one mistake and you’re done… I can’t help that but I’m not having that in MY script.

So it turns out that you can have FUNCTIONS in scripts – and that you can have a text file full of functions – and even, if long and complicated enough, you can hide them away by simply “include” the file  at the top of your script!!!

source  ./;

I discovered this lot by accident – in this case I’ve entered all of this on the command line.

colour scripts

Note that I’ve defined some variables (don’t ask how this works) and I’ve used one of them in my PRINTF command to add colour to part of the text !!!!

I also wrote a function to take in a user password twice, compare the two and only let the script progress when they are both the same!

So armed with this knowledge I set about making a script which would be modular – that would ask you for each part if you wanted to continue, skip the current section or give up altogether. At first I had all sorts of coding but soon realised you could hide much of the work away in an include.

So I ended up with 2 files – one called which had my scripts in it – and another called which had all the nasty bits hidden away – there is absolutely no reason why this could not be in one file.

So – taking one of the simpler examples… upgrading Linux to the latest version. As it happens I do this LAST in my setup script – but it’s an easy one to explain.

task_start “Linux Upgrade” “Upgrading Linux version…”
if [ $skip -eq 0 ]
cd /root

My function task_start has 2 arguments —-and this is what you see when it runs… in this case I skipped doing the actual operation.


My “task_start” function does all the work here. It draws a pretty box and puts the FIRST argument into it – i.e. “Linux upgrade” – it then asks you if you want to continue, skip or cancel (Y/S/N)  – and if you hit S for skip it goes onto the next section – remember my comments about moving the cursor on the same line – well, the elapsed time indicator uses as that – and the options are deleted when they are no longer in use showing a cleaner screen.

So – I call up my starter function – if I decide to go ahead – the SECOND argument is displayed – in this case “Upgrading Linux system”.  The whole lot is wrapped up in a weird Linux IF statement (see the IF –THEN and the FI lines) – and in between is the actual scripting.. the second last line “task_end” merely puts an “[OK]” in colour in th right column if you go ahead and do a task.  so the actual work is the simple instruction “rpi-update” and that is a built-in function in your Pi.

Most jobs are way more complex than that, involving installing something, maybe tweaking a config file, deleting some zip files when you are done etc. There is a tool called SED for replacing text in files – nearly killed me getting to grips with that – the worst part was that it operates slightly differently when you are running it on the command line to how it operates in a script!!!! However – the work is done. Having mastered just a few tools and making my own script functions, I can now just keep adding stuff into this script so that when it comes to starting from scratch with a new Pi2, I can sit back with a beer and simply fill in passwords on request, letting the Pi do all the work.

No doubt the Linux guys will come in here and tell me what I’ve done wrong – in which case the script will improve with time – and of course I’ll find new things to install etc….

It is beyond this simple blog to tell you how to use all of the various packages installed…. I’ll leave you to look them up on Google…. but if you get so far as to successfully run the script, the first thing you might want to do is go into webmin and update it to the latest version and maybe change the default template if you don’t like it  – I could not figure out how to do that in the script. Webmin runs at port 10000… so in a browser you’d access your Pi by IP number and port …  192.168.xx.xx:10000 – you’ll be asked for the password you put in for Webmin and the rest is documented out there – I could not be without Webmin – good tool.

You may notice in my task-start, I spread some text over several lines – be careful with the placement of the quotes…  you can have as many lines of text as you like but the quotes need to be on the same line as “task_start” or it will think you’ve not added any arguments and will then wonder what the hell that text is doing.

No doubt there will be bits I’ve missed out – and the script will change – but right now having written both parts of this in one early-morning start, I need coffee and food.

“The Script” has since been developed considerably – read this article.


Raspberry Pi Monster Script Part 1

It all started innocently enough…

Pi2Updated 10/10/2015: All I wanted to do was control some ESP8266 and Arduino boards – I spent hours looking at various packages and ultimately decided that none of them really did what I wanted – I resolved to learning about the Raspberry Pi (though frankly any small Linux device would have done – but for power to cost ratio the Pi2 is pretty hard to beat unless the Orange Pi comes up trumps  – still waiting for delivery).

Having gone through the cycle of installing Raspbian on my little Pi2 and then manually installing the likes of Apache, PHP, Node-Red, MySQL and more, over and over again either because I screwed up the installation or tried an experiment too far…. after a while I was really starting to tire of the repetition. I decided to look into SCRIPTS.

So I’ll try not to make too many assumptions here, I’ll assume you are able to get Raspbian on your Pi2. I’m a Windows user so I’ll explain this from the perspective of someone sitting at a Windows machine though most of this does not concern such dependency.

So typically looking around, having installed the basic setup and now wanting to move on, people use the default PI login and then use the SUDO (Super User Do) command to give root privileges – so – I made the decision to log in as ROOT and save a lot of typing – still – you’ll note I used it so often I actually know what it means – that’s sad). I’m sure I’m just about to get arguments back about security. Save it guys.

SO – following this article you should be able to end up with a Raspberry Pi setup like mine which has all the tools I need to make a central control base for home control.. but the point of the article really is to give you a base so you can make your own script to do what you want.

My installation consists of the following:

  • The latest Raspbian on the latest base.
  • TightVNC Server (for remote PC graphical access which I use less and less)
  • Apache webserver with PHP
  • SQLITE server with some basic databases
  • Sqlite with PHPLiteAdmin
  • Webmin
  • Node
  • Node-Red
  • A ton of nodes for Node-Red including my own, nodes for MySQL, SQLite, Email, Serial and much more
  • Mosquitto MQTT
  • I’ve installed the Internet time updating software for Webmin so that the Pi2 will go off to the web for the correct time
  • Email (SMTP)
  • Sound including speech and MP3

So clearly some questions come out of this – why two types of SQL? I started with MySQL – a powerful database which works well on the Pi2, but I starting to get concerns about it’s effect on the SD memory – MySQL does a lot of writing.  With that in mind I went off looking to alternatives and found SQLite – which is very different, not entirely compatible but lends itself to being a lot lighter on SD writes!

Getting Raspbian onto the Pi in the first place

First things first,  I’m assuming you can put Raspbian Wheezy on the Pi2. There is a slightly later version but I had minor issues, it was new so I figured… let’s stick with the one that works for now. There are a ton of ways to do this – usually involving formatting an SD with SDFORMATTER then copying some files over to the SD, putting it into the Pi2, turning on and following instructions. EASYBOOT is my choice and is Excellent as it not only does the downloading of Raspbian for you but also allows you to do backups to USB memory. Format the SD, grab the EASYBOOT zip, copy the files onto the SD and run the PI with network connection – I prefer wired – select Raspbian Wheezy and some time later when it is ready to go, make it the default and reboot – and you’re up and running.

PERSONALLY at this point I reboot again – and in BerryBoot do a backup to USB memory – makes things a lot easier if you screw up later on.

You can run Raspbian from the SD, or from a memory stick or even from a Diskstation or similar on your network…   If you are running off the internal SD card, buy a decent, fast, brand name SD.  I’m using 16Gbyte SDs and I have a 16GB USB in there too for backups (the backups don’t take the full 16GB, mine tend to be 2-3gig at a time).

I have an installation which starts on a card and actually runs everything on a 2.5” hard drive – an old 120GB job.  You could use a larger SD but then if you wanted to back this up with something like Win32DiskManager, it just takes that much longer. Windows tools for backing up this stuff are not very sophisticated. Berryboot 2 is the best solution I’ve come across so far.

The next step

So you’re sitting with a Pi2 and some kind of monitor, it has asked you to login as Pi user and you’re there with a prompt or Raspi-Config running. If not – you can just run “sudo raspi-config”. You’ll probably want to change the password for PI user and under advanced you may or not want to enable SPI, I2C and SERIAL. Unless you have a reason not to, I suggest enabling them. You may or may not wish to use the last option to update the raspi-config package itself. If not you can always do that later.

Back at the prompt:

sudo passwd root

This will ask you for a password – and in my case that is the last time I ever have to use “sudo”.

AT THIS POINT you do not ever need to look at the Pi2 through it’s own monitor again except for backup and restore – you can do it all from a PC – which most likely means a bigger screen, better keyboard and mouse !

So, when the Pi was powering up I hope you made a note of it’s IP ADDRESS as you’ll need that.  Indeed if you know how to fix IP addresses in your router, I suggest doing that so the IP address will remain fixed. You can do that all in the Pi but it’s a lot easier if your router supports associating an IP address with a MAC address – to ensure you get the same address whenever you power up. If you never intend to access the Pi externally all of this is probably irrelevant – but if you want external access then you’ll need to fix the address so that you can set up port forwarding on your router.

Armed with an IP address, on the PC, grab WinSCP and also install Putty somewhere on the PC. Why? Because WinSCP is the best way by far to access the Pi (easiest) and it has  rather awful terminal but will automatically use Putty if it is available – trust me – copy-paste, ease of use, I’ve tried LOADS of programs and after months, this combination works best.

Graphical desktop or Terminal?

When I started off, I wanted graphical access on the Pi so everything I did was oriented that way. Experience has shown that this is not a good idea… why? Well, the editors on the Pi are NEVER going to be as good as the tools you likely have on your PC.  The browsers for the Pi are AWFUL – far better to access on a PC with a proper browser – and so on and so on. So now, with the benefit of experience I’m wondering why I was so intent on filling up the Pi with visual tools I was unlikely ever to use. Don’t get me wrong, remote access to the Pi will be very visual, no more so than with Node-Red – but that is browser-based, no need for graphical ability on the Pi itself.

WinSCP is easy enough to setup – you mainly tell it about the IP address of your Pi (or PIs if you have a few) and the root name (“root”) and password you setup earlier – that’s it. From there on you are in a file explorer-type window with drag and drop and if you need a terminal it’s a button press away.


Typical login screen above – on the left, my list of devices (WinSCP does a lot more than just talking to Pis – I use it to connect to my service provider, my DiskStation and much more.  In this case, the protocol is SCP (this is already built into the Pi) I provide IP, user name and password and that’s it.

WinSCP Connected

Here we are connected up to one of my Pi units – as it happens the one that uses a hard drive – though that is utterly irrelevant here as Linux doesn’t use “disk drives” as such – everything is just a folder. the BOOT folder happens to be on the SD and everything else happens to be on the hard drive – but that’s quite irrelevant here. Left hand side is my PC, right hand side is the Pi. You can move files back and forth or indeed take files and folder on your desktop and drag and drop into the right window (but beware, PERMISSIONS on Linux are not the same as a PC – I’m making life there a little easier by being ROOT user at the expense of a little security).

So there are two or three main methods of installing stuff – one involves APT-GET (Advanced Packaging Tool) which will go off and get an installation for you. Another involves WGET which basically grabs, say a ZIP file or a whole directory and dumps it on the Pi, so you can subsequently unzip it and put it’s innards somewhere. It is worth while looking up these two commands as you’ll see them a lot… mind you – the point of a script is so that you don’t actually have to know much about this – but really if you want to modify my work to suit your own needs, the more you know the better and no I’m NOT a Linux fanatic, I’m a PC guy…  but a little knowledge goes a long way.

Once you have NODEJS up and running there’s a package called NPM which is also used for installation. But don’t let any of this worry you.

So a SCRIPT is a plain text file, usually with suffix .SH which you run – and it goes off and does things for you almost as if you’d typed instructions by hand – but with a lot more capability such as variables and functions. I had to learn this the hard way – all I’ll say is thank goodness for Google.

Here’s a simple script we’ll call “” which contains:

echo “hello”

and when you run the script it will do… nothing. My first run-in with permissions. A script has to “execute” and if you don’t give it permission to do that, it will fail by default!  But hey, WinSCP to the rescue./

So I’m assuming at this point you have Rasbian running, you’ve done the root password thing and you’re sitting with WinSCP waiting to have some fun.

I use NotePad++ on my PC for good reason and so I’ll assume you have that.

You should have put PUTTY somewhere on your PC (the one I found didn’t have a proper installation program so I just put it in Program Files(86) and under  PREFERENCES – integration, applications tell it where PUTTY is. Under PREFERENCES in WinSCP you might want to set the default EDITOR to be Nodepad++

Here we are creating a file on the Pi. It can be edited on the PC just by double-clicking it and when you hit save it will save back to the Pi.

WinSCP creating a file

So – TWO things we need to get out of the way… The file ( needs PERMISSION to execute and needs to be saved in a LINUX format – that is with new lines (\n) but no returns (\r)

When editing a file in NotePad++, the latter is easy. EDIT – EOL CONVERSION – UNIX – simple. Here’s our file.

file edit

In the file put the echo statement above.  Save (control-S). You should now see the file on your Pi.

File on Pi

So now we access the Pi – under COMMANDS – OPEN IN PUTTY – you gain access to your PC for actually DOING things – it is going to ask you for the root password you created earlier – I’ve not found a way around this.. You don’t need to get into any Putty setup nonsense – it just opens up , you enter a password and you are in.

At this point I should explain PASTE in Putty… anything in your clipboard – just right click the mouse and it will be pasted into the command line in putty. Easy.

And so here we are in the root directory… you will try your new file – and it will fail.


I think this was the point at which I considered giving up and installing Windows on the Pi – but only briefly.

Back in WinSCP –  right click the file – PROPERTIES – thank heavens – tick-box properties –

WinSCP File Properties

See that X I’ve ticked for user OWNER – that will allow scripts to operate.

and now….


Doddle. So now we have the ability to edit a text file – and make things happen in a script!

In the next part, we’ll go into this in depth and end up with a working script which will install all the goodies I’ve mentioned above – reducing the complex job of installing loads of packages to basically a nice coloured screen with the need to type in some passwords while drinking coffee.

Honestly it gets easier and a lot more fun from here on. And now – onto part 2.

But please note that “the script” has moved on a LONG way from this – see the current article on the subject..


SQLite on the Pi

This weekend gone I was having a play with dSQLite for my Raspberry Pi2 and thanks to some helpful guys here in the comments section and at Node-Red Google forums – and a little inspiration of my own –  I manage to get everything up and running – so I’ve changed what was originally a question into short piece on using SQLLite on the Pi.

Why would you want to use SQLite?  Well, my Pi is running Node-Red to control my own little ESP8266 gadgets as some of you know (software for these under the Home Control 2015 page just took a turn for the better) – and I store info in MYSQL.  I’m aware from the various forums that MySQL can be quite heavy on writing and of course that’s not really good for solid state microSD memory  (HOW bad is debatable but we can all agree that microSD does not have unlimited WRITES). And so it was that one of the guys here suggested I look at SQLite.

Now, I don’t know if I was getting confused with a similarly named Microsoft product but I really didn’t know much about this until this weekend. Essentially this is a somewhat lightweight SQL – simple to install, simple to use, works with PHP etc… and works with Node-Red.

On my Raspberry Pi I installed SQLite (as root)

I created a database and put something in there – I tested it on the command line and in node-red – no problem.

I tried a simple PHP page to view the data – nothing. So, I enabled error messages in the page – and it said it did not know anything about the SQLite class.

So I installed that and then I got permissions errors – turns out that putting the database file in the /root directory isn’t too good an idea so I simply moved it to /var/www and Bob’s your uncle!

Here’s the sequence..

Install SQLite

apt-get install sqlite3

Create a database

sqlite3 test.db

Create a table

CREATE TABLE temp1 (name TEXT, temperature NUMERIC);

insert into temp1 values(‘first entry’,45);
insert into temp1 values(‘second entry’,56);


Install Node-Red Node

npm install node-red-node-sqlite

Add SQLite to PHP

apt-get install php5-sqlite

Write a page in PHP

ini_set(‘display_errors’, true);
$db = new SQLite3(‘/root/test.db’);
var_dump($db->querySingle(‘SELECT name FROM temp1’ ));
print_r($db->querySingle(‘SELECT name FROM temp1’, true));

I’ve no idea yet how that PHP is actually working – but that will come in time –  I’m thinking about all the MESS involved in installing MYSQL and the passwords etc, just to store a few vars away to draw some graphs – this might be a better solution… and there is an option to make RAM-based databases as well. Overall, worth a look I’d say.

BUT that still left me with a problem….

I have a logging table for my ESP8266 boards..

`device_name` varchar(80) NOT NULL DEFAULT ”,
`device_description` varchar(80) DEFAULT NULL,
`device_attribute` varchar(80) DEFAULT NULL,
`logins` int(11) DEFAULT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
PRIMARY KEY (`device_name`)

Simple enough – and when a unit logs in, I either store the record OR update the counter (logins) if the record already exists.  In MYSQL this is easy and it is a fairly common requirement.

insert into device_list(device_name, device_description, device_attribute,logins,last_update,creation_date) values(‘998′,’empty’,”,1,NOW(),NOW()) on duplicate key update logins = logins+1, device_description=’empty’, device_attribute=”, last_update=NOW()

In SQLite – ALMOST as easy… two statements…

insert or ignore into device_list(device_name, device_description, device_attribute,logins,last_update,creation_date) values(‘998′,’empty’,”,0,(datetime(‘now’,’localtime’)),(datetime(‘now’,’localtime’)));

update device_list set logins = logins+1, last_update=(datetime(‘now’,’localtime’)) where device_name=’998′;

So you start with a count of zero IF a new record is created – and the second statement, which will always operate, updated the latest date and increments the counter (in the case of a new record to 1).

But therein lies the rub… the Node-Red node for SQLite3 will not let you enter more than one statement! I found a way to put this into a single statement but it wasn’t flexible, was VERY hieroglyphic and I figured 6 months later it would mean nothing to me… So the next best thing – a node-red function to split any multiple statements up and fire them out one at a time – easy.


and the code inside that function…. this will allow as many statements as needed… actually that really would make a good addition to the SQLite3 node…

var arr=msg.topic.split(‘;’);
for(var i=0;i<arr.length;i++) {


Works a treat. NOW- that’s all very nice I hear you say but what about fancy graphical tools!!  MyPHPAdmin and others come to mind. So off I went looking – I tried 2 or 3, one of which used up loads of space on my PI but I never could find out where it was to complete the install.

phpLiteAdmin is the kind of program I like – one PHP page, one config page and Bob’s your uncle. Optionally you can have a themes directory full of theme .css files – but basically it’s a 2 file program – and yet…


As you can see, not at ALL shabby. Here’s a link – this is where I grabbed the THEMES directory from.. – that is 1.97 but it says “dev” so I didn’t actually use that code, just the themes – my 1.9.6 came from here.

permissionsI put it into /var/www/phpliteadmin and renamed the main phpliteadmin.php to index.php because I’m lazy.

Oh and the database directory and file must have 0755 permissions… well, tha’ts the minimum I could get away with on experimenting.

Have fun – and if you know BETTER than the above – a better yet simple admin, something I’ve done wrong – DO write in here – improvement is the goal!


QD Tech Displays on the ESP8266

QDTECHI’ve seen a number of projects using an Arduino and an ESP8266 to drive LCD displays. You may know that my home control has used a 1284-based board (MEGA type) to run a nice Thermostat display for some time now. The displays look very pretty in the right box.

THIS article will tell you about the display but you’ve seen them on Ebay no doubt.

Banggood do the displays (I can’t help thinking the price has gone up – at £3 inc postage – but still cheap)

MAKE SURE you get the display marked 1.8 TFT module using the s6d02a1 chip – there are some marked differently and using a different chip which does not work with my software. Anyway, they are 160*120 and very bright and colourful displays, excellent for simple stuff.

displayI’ve always wanted one of these to run on the ESP-12… on the Arduino assuming 5v you need series resistors for the outputs – as it’s a 3v3 device internally, but with an ESP you can run them directly. So, the device and it’s backlight go straight to 5v, the 4 inputs go straight to ESP8266 pins and the reset can go to the ESP8266 reset pin – again directly.

That’s the easy part – the software not so – there are not a lot of drivers out there for this board for the Arduino – one that does work offers a choice of hardware or software SPI and that’s where the magic comes in – if you eliminate the hardware SPI code it all starts to look quite easy. So yesterday I set about (with no anticipation of success) converting the code firstly from nicely object-oriented Arduino code – to basic C – and secondly to use ESP8266 port pins. I cheated slightly by using EASYGPIO to make the port operations a little more obvious but I don’t think that has cost too much in speed.

Anyway to cut a long story short at THIS point I’m off to the coast for a couple of days sunbathing and I’ll leave you with my work in progress. I’ve NOT added in the code to handle characters/fonts yet but I HAVE got this working completely reliably – drawing boxes and lines.  Right now there is some setup data in RAM, I’ll move that to FLASH shortly (you need a function to access 8-bit FLASH arrays to stop the ESP crashing so I left that until last). Why bother with FLASH? Well, you’ll definitely need to put fonts in FLASH as they get big, quickly.

I’m assuming you’re programming in C – don’t ask me about getting this running in Arduino code or Lua.

Here’s the header

Here’s the C code

I’ll assume you already have a project running and just want to add this to it. I’ve reduced includes to a minimum..

My test includes a red screen background, a green box and a blue diagonal line. So with the includes this is all I put in my code (I slightly altered the init code to include ports (not including RST line as you fasten that to the ESP reset – hence needing only 4 port bits – you’re not stuck with my choice – the last two parameters are the screen size).

for (int qq=0; qq<160;qq++) QD_drawPixel(qq,qq,QD_Color565(0,0,255));

There it is – a starter only.. but it works – if this were taken to an extreme it would include character handling and hardware SPI – I’m guessing compared to Arduino use it would be pretty FAST..

Now if anyone wants to take this on and add the bells and whistles..

Oh and how would you run the data in FLASH? not so hard… here’s a typical FLASH-based array snippet.

static const uint8_t ICACHE_RODATA_ATTR  kelvin[] = {
255,  51,   0, //    1000
255, 109,   0, //    1500
255, 137,  18, //    2000

And to access that…

// This routine courtesy Richard A Burton – way better than using 32 bit flash arrays (you can’t directly
// access 8 bit FLASH arrays directly – will crash the processor)
uint8 ICACHE_FLASH_ATTR read_rom_uint8(const uint8* addr){
uint32 bytes;
bytes = *(uint32*)((uint32)addr & ~3);
return ((uint8*)&bytes)[(uint32)addr & 3];

Typical use;;;


So you can see it won’t be TOO hard to get a load of font data into FLASH – sure, it’ll slow things down a little accessing it – but how that actually affects operation is something we’ll discover in the future.



I recently put out a question about the RAM in the ESP8266. In a typical compile, for me I might see…

Idx Name Size VMA LMA File off Algn
0 .data 00000894 3ffe8000 3ffe8000 000000e0 2**4
1 .rodata 0000275c 3ffe88a0 3ffe88a0 00000980 2**4
3 .bss 00009a80 3ffeb008 3ffeb008 000030e8 2**4
4 .text 00007786 40100000 40100000 000030e8 2**2
5 .irom0.text 0003ce04 40202010 40202010 0000a870 2**4

All very confusing.  So .irom0.text is FLASH – why they can’t just call it FLASH I’ll never understand. The rest are various kinds of RAM.

.data is used for “initialised” data, i.e. that which needs copying from FLASH on powerup. I’ve used 0x894 in the above example.

.rodata is used read only data – this is in ram (again I don’t understand, if it is read only why it’s not in flash – most likely for speed). I’ve used 0x275c.

.bss is used for variables which are not initiased. I’ve used 0x9a80.

.text is used for CODE that is not in FLASH – i.e. it is in FLASH on powerup – but gets copied to RAM for speed.  Sadly due to lack of RAM we usually end up using a directive to put code into FLASH to save space – but REALLY time critical code should be left in RAM. Of course it does get copied into RAM (cacheing) but that could mean execution times which are different first time around – not good for RGB LED lighting etc.

A fellow by the name of Don Kinzer replied and here’s what he had to say.. very useful.

“There are no individual limits for the sizes of .data, .rodata and .bss (but see caveat below). The ESP8266 has 80K of User Ram which is occupied by .data, .rodata, .bss, the stack and the heap. Basically, the space represented by (80K – sizeof(.data) – sizeof(.rodata) – sizeof(.bss)) is used by the heap growing from one end and the stack growing from the other end. If the heap and stack collide bad things will happen (probably an exception, eventually, and a reboot).

The caveat that I mentioned is that the .data and .rodata sections, along with the .text section (itself limited to 32K) must fit in the first 64K of Flash. The sizes of each of these sections is rounded up to an integral multiple of 4 bytes and an additional 8 bytes of overhead are added for each section plus an additional 8 bytes of overhead. Finally, space for the checksum is added (between 1 and 16 bytes) so that the overall size is an integral multiple of 16 bytes. The total of all that must be 64K or less. The image containing all of this data is often called app.v6.flash.bin but the name itself is not important.

As for the limit on the size of .irom0.text, that depends on 1) the size of your Flash chip (commonly 512K but can be 16MB or more), and 2) where you load it into Flash and 3) if you want OTA upgrade or not. For non-OTA upgrade, it is common to load the .irom0.text at 0x40000 but it can be loaded as low as 0x10000. Since the system parameter area exists at 16K from the end of the Flash chip, the size of .irom0.text is limited to sizeof(Flash) – 16K – loadAddress.

If you want to load the (non-OTA) .irom0.text at an address other than 0x40000 you’ll need to modify the linker script accordingly.”


Backing up Node-Red

Node-RedFor anyone looking in for the first time – I have a home control system based around ESP8266 boards and the odd Arduino. They talk MQTT to a Raspberry Pi which acts as a central controller – using Node-Red – the greatest thing since sliced bread.  I’m in the process of putting together a (Linux) script to replicate my Raspberry Pi setup – as this is now central to controlling my various ESP8266 and Arduino gadgets around the place.  I’ve learned a lot in the past few days but one last thing that was niggling me was Node-Red.

So installing Node-Red is easy enough and restoring nodes is easy enough – even specials like mine are a couple of simple operations to retrieve… for example my duskdawn node which in turn needs the sunrise node.….

cd /usr/lib/node_modules
npm install suncalc
wget –no-verbose -O
unzip -q
mv scargill-duskdawn* node-red-contrib-duskdawn
npm install node-red-contrib-duskdawn

Looks complicated but you don’t need to understand it because it can be put in a batch file (or run a line at a time).

So all of that is “easy” enough (I put easy in quotes as it is bloody hard when you are first faced with Linux). So to install you should be in the right directory, pull in suncalc, grab my code, unzip it, move it and install it. Doddle.

flowsWhat’s not so obvious are the actual flows you use in Node-Red – if you’ve seen mine you’ll see I have several pages of them – items feeding websocket to mqtt, http to mysql, experiments, comments – there is no way you’d want to manually recreate this stuff – thankfully within node-red there is the ability to select a page full of flows (or a single one)  and copy and paste them – but that doesn’t help if you have many pages and you want to recreate your work in another machine.

Well, it turns out (certainly in my case) that the info is all stored in 2 files – and it also explains why all my work disappears temporarily if I log in as someone else!!!

in your user home page (might be root, might be /home/pi there is a node red directory – or maybe a hidden .node-red directory (the dot hides directories – I have winSCP set to show them all) there are a couple of files…and in the file name is embedded your hostname – so they’re not the same on all machines – and copying them won’t work unless you change the name.

SO on my main Pi2 there are files in /home/pi/.node-red called flows_OtherPi.json and flows_otherPi_cred.json –  yes you guessed it – the second file contains all the login details etc you you put into your nodes.  My target machine is called raspnas and in my /root/.node-red directory there were a couple of files called…. as you might suspect now, flows_raspnas.json and flows_raspnas_cred.json.

With Node-Red STOPPED, I wiped the latter, copied the files form OtherPi over and renamed them. Started Node-Red and LO – all my flows and pages work with credentials intact.

If only life in general were so easy…



In case anyone was thinking I’d fallen off a cliff..  I’m waiting for my Orange Pi PC to turn up so I can find out if it works of if the doubters are correct. In the meantime I’m working on my script to install everything but the kitchen sink – and learning a lot about BASH scripts – things like how to stop someone entering Control-C when you want them to select Y/N,  how to clear the buffer so your script is not entirely screwed up by accidental key presses – etc. How to put the script in pretty colours, how to make sure passwords aren’t messed up – the list goes on  -anyway, right now I’m just starting another 1 hour+ installation and waiting to see what I’ve done wrong this time.

I will put the script in here later on with an explanation  – once I get it to actually work correctly – so that’s why I’m not writing about ESP8266s etc.. Meanwhile I’m wiring up my Pergola with RGB strip when it’s not raining. More on all of this later.



DiskStation Pi

PiJust in case you thought I’d gone off on holiday..  I’ve been sat at the terminal over the last couple of days, experimenting.

You will have seen concerns mentioned here and elsewhere about using a Raspberry Pi as a central hub to control ESP8266s using MQTT and Node-Red. The issue is that the Pi uses an SD card and these don’t last forever.  You can minimise use by using RAMLOG which keeps logs in memory and once a day backs them up to the SD.  That in itself is fine (but not perfect, it’s caused me to lose an Apache log which in turn stopped Apache from powering up!) but there is also the MYSQL server –  and while it should be possible to run THAT in RAM and back it up every night – I’ve not found anything remotely like plans to do that anywhere.

Swapping the internal SD for a USB stick doesn’t help, you could of course use a USB drive. Another way is to put the entire Pi (except boot) on a NAS drive. This is EASILY done if you happen to have something like a Synergy DiskStation – using BERRYBOOT which makes it easy to set up your NAS drive as a destination.

So – I did all that and decided I wanted a script like the one my friend Aidan has done so that once the drive is running – the script will do all the installation of everything I use. NOT as easy as it sounds and thanks to backup and restore facilities on the NAS, having made a backup of the basic setup (just Raspbian)  I’ve been stuck in a testing scripts – restoring drive – testing scripts loop for much of the day.

Nothing to report yet, my scripts are the prettiest colours but don’t completely work yet. More soon. The idea being that the Raspberry Pi will be wall mounted – showing temperature and allowing control of temperature and lighting via a touch screen (on it’s way) while simultaneously being the hub for data, Node-Red etc with the actual code and data on my Diskstation which is RAID and can regularly back up the volume (sadly not incrementally).


BASIC for ESP8266

Don’t get me wrong, we don’t yet have a fully working interpreter, but a link appeared on my ESP8266WIFI Facebook page thanks to Michael Molinari this morning and I thought I’d give it a whirl.

I grabbed the Windows loader (Windows 10 put up a SEVERE warning about this which I totally ignored), plugged an ESP-01 into my FTDI and blew in the interpreter, all of which took a couple of minutes and zero effort.

At first I didn’t know what to expect, I looked at the serial port – nothing.. then realised it outputs at 9600 baud, not my usual 115K…. I then started typing stuff into the serial – nothing – at which point I noted it already knew about my router – I can only assume that is because the WIFI passwords were already in the board, normally you’d have to use the board as an access point just to put in your WIFI details… anyway, I managed to bypass that.

I checked the examples on the page – hello world and a graphics example – which worked right out of the box!! Imagine that – simple BASIC commands putting out graphics on a web browser from a twopenny WIFI board all on it’s own!!

At this point, things went down a little. I tried editing the code (in the web browser!!) and it wasn’t having a SAVE – I noted on the serial that it had crashed… but then if you look at the dates this is all very new.

Seriously this has to be worth a few minutes of your time – assuming the project continues and these niggles go away, you just don’t get a quicker, simpler and cheaper way to get a web page with buttons into an embedded device – even if just to do some simple stuff. This is now ENTIRELY down to how much effort the author puts into this.

Go take a look! These images should enlarge on click/touch. Well impressed IF development comes quickly enough….

Basic Interpreter for ESP8266   Graphics on the ESP8266


Raspberry Pi Run from USB

What  a day…   I decided this morning that I would run my Raspberry Pi (well one of them anyway) from USB for the following reasons: I have lots of tiny microSD cards and lots of USB sticks or USB adaptors for full size SD cards.

And that’s all fine and good – but the instructions out there can be confusing.

Followed this no problem – putting a copy of everything onto a USB stick (aside from references to SDA2 which in my case turned out to be SD1).

All that remained was to make the boot disk (microSD). I had some 512Meg and smaller cards so I formatted one as FAT32 using SD formatter on the PC.  Some kind fellow provided a copy of his boot files which I promptly put onto the SD card – plugged it in and…

Nothing – it took me a little while to twig that this would be for the ORIGINAL RASPBERRRY PI, not the Pi2. SO I mounted my backup microSD, rebooted the Pi, copied the files from BOOT to the new SD, put THAT in the Pi and tried again.

Success… but for a complaint from the PI that required a CONTROL_D to continue. Did that – and I was in action – it turns out that you have to tell etc/fstab about the new USB drive as well.


So now I have a fully working system on SD but for a minor niggle. When the Pi boots up it complains that:

Mount point /boot does not exist… and sure enough when you look at the file system in WINSCP – there is no boot directory – I’m guessing because I’m looking at the USB stick…..

Don’t get me wrong – everything works – but it would be really nice to clear up that bootup error message – anyone know what to do?