Imagine a distributed cloud computing Network spread all over your campus. The main server is sitting somewhere on the Internet (or Intranet) and maintaining a big database having a huge number of tables. The tiny upload nodes are running on ESP8266 or ESP32 and are connected to several sensors which are uploading data at regular intervals to the respective tables of the database.
The huge campus is spread over several zones and there are several WiFi hotspots available all across. Our ESP nodes are intelligent enough to connect whatever WiFi is available at that zone and then start uploading data. After uploading data at a particular time interval it goes for deep sleep. Being a low power device (2.5 volts to 3.3 volts) combined with deep sleep state the power consumption is pretty manageable on LiPo (or latest LiFePo4) battery for a couple of days. For maintaining time we can manage the ESPs to connect the NTP (National Time Protocol) server and get time from there or we can have a small DS1307 RTC (Real Time Clock – $0.5) connected with each ESP.
[In the prototype section we have both the models]
Server Side for Distributed Cloud Computing
The server is having Linux-Apache-MySql-PHP installed. Linux is not compulsory but Apache-MySQL & PHP is essential to have the system works. It gets data all across the nodes. Each table has an ‘id’ field which is an ‘auto_increment’ type. For those tables which are having the same periodicity can be joined on the ‘id’ fields.
The output of the data is available for view on ChartDirector or any other program of yours.
The cloud is ready now!
One-liner
Those who have seen my “Cloud computing with Thingspeak.com” for them this is just that now you are having your own ‘ThingSpeak.com server” which is completely under your control. Nobody can access your data unless you allow. Two levels of authentications are used – One with the WiFi connection and the other with the MySQL database connections. Many more levels of authentications can be inserted with the power of Apache-PHP combinations. All these authentications are hardcoded/burnt inside the sketches, therefore, it’s pretty safe!
Principles of operation
There is some claimant on the Internet who claims to have successfully integrated MySQL with Arduino but to be frank I’ve tried many of them and though few alpha libraries did work on Ethernet connections none of them worked on WiFi connections. So I discarded all of them. But the HTTPClient.h library on ESP32 and ESP8266HTTPClient.h library on ESP8266 is very powerful and works flawlessly for GET connections. These two libraries are now in tandem with a small PHP script does the trick for uploading the MySQL tables for us and that’s the crux of this project – Tapping the Power of PHP scripting by ESPs! Thus you can do many more things with this library combined with PHP.
Data output
Once the data is available in MySQL database with date-time stamp along with id field, it’s now easy to create & display output program – either by using PHP or by using the powerful ChartDirector which is available in many languages – VB+, C, Java, PHP and many more. Download your choice and deploy the database connection to get on line data output.
Getting started
A. Create the MySQL database & tables
Experts create MySQL database & tables by the stroke of a few SQL lines on a terminal but for me, it’s rocket science. Hence so I rely on PHPMyAdmin for creating my database and tables. Here’s the out of PHPMyAdmin – by clicking here and there [in the root user mode] I do create my MySQL database and table.
B. Create the PHP scripts for uploading
We create two tiny PHP scripts of barely 25 lines – one for each table: c1z1.php and c1z2.php, these files are attached herewith. These files are to be inside the document directory of Apache (browser page sort of things) so that the ESPs can open them from outside. The ESPs open these pages, add sensor data to it and then post it so that the data goes straight to the MySQL tables.
C. Install ChartDirector
It has many flavors – C++, .net, java, asp /com/vb, Ruby, ColdFusion, Python, and PHP. The download page is available here – www.advsofteng.com/download.html
Select your flavor & operating system and then download. Read the instructions, it’s very easy. So is easy to create a chart out of it. It’s not fully open source. You may register it for a fee to get rid of a small banner at the bottom of the chart otherwise it’s neat and clean.
D. Schematic
I’ve used a simple DHT-22 sensor on ESP8266 (WeMos D1) board and a DHT-22, RTC-DS1307 & TGS2600 (general purpose smoke sensor) module on an ESP32 to upload to two different tables of MySQL database hosted on my remote server at – berapi.ddns.net
ESP32 & ESP8266
ESP8266_uploader:
uploads→ temp, humidity,batt voltage.
ESP32_uploader
uploads→ temp, humidity, smoke level, onboard temp & batt voltage. TGS-2600 probe needs 5 volt supply to operate unlike 3.3 volts for the others like DHT-22 or DS1307 sensors. However, it’s output is 3.3Volt compatible for the ESP32 input pins. The 3 pin regulator – 7805 reduces input 9V – 12V to 5Volt level. The 3.3 volt is made by the ESP32 onboard regulator.
Timekeeping
For timekeeping I’ve used two ways – One RTC (DS1307) connected on the ESP32 and the other ESP8266 to connect to the NTP server to get time periodically. Both ways it works but I prefer the RTC way because it comes for almost naught ($0.5 – $1 a piece from aliexpress.com ) and you are on your own!
Software
The software is divided into two parts – The PHP scripts & the Arduino sketches.
- PHP scripts: There are two PHP scripts – c1z1.php for cloud-1, zone-1 data and c1z2.php for cloud-1 zone-2 data. The c1z1.php uploads 5 data which are handled by the ESP32 while the c1z2.php uploads 3 data which are handled by the ESP8266. These scripts are to reside in the document directory of Apache server so that it can be a browser ready page which is called periodically by the ESPs for uploading data. The server preferably has to have access from the Internet so that the nodes can directly send data there.
- Arduino sketches: These are two Arduino sketches – esp8266_mysql_2.ino for the ESP8266 and esp32_mysql_2.ino for ESP32.
Upload these sketches to the respective boards. Keep the server ready and then see that the data gets dumped into the MySQL tables every now and then. The RTC may need to reset the time when it is started the first time. Uncomment the line to make it ready for setting time then upload the sketch to set time first time. Next, comment out that line and then upload the sketch again. The RTC will keep on supplying time henceforth uninterruptedly because it has a small onboard battery back up.
Output on ChartDirector
I have my output designed on PHP type ChartDirector. First, download ChartDirector on your chosen language and then see the code part in your favorite language and you can build a beautiful online trend curve just in no time. However, most of the curves of ChartDirector are a static type, to make them live just insert the PHP refresh code somewhere towards the bottom of the code.
# Output the chart
header(“Content-type: image/png”);
header(“Refresh:240”); // This is for online refresh ;once in 240 seconds
print($c→makeChart2(PNG));
//end of code
The sample chart can be seen here
http://berapi.ddns.net:8082/ChartDirector6/phpdemo/cloud1.php
http://berapi.ddns.net:8082/ChartDirector6/phpdemo/cloud2.php
http://berapi.ddns.net:8082/ChartDirector6/phpdemo/cloud3.php
Prototype
Cost / BOM:
ESP8266 = $3.5
ESP32 = $7.5
DHT-22=$2.5
DS01307 RTC = $0.7
TGS-2600=$4.0
Typical Use for
The typical use of this system can vary from simple upload to conditional complex uploads into MySQL tables. Door open/close, AC Hall temp & humidity, VAT – empty/full, Hall light on / off , airspeed, air direction, ambient temperature, drain overflow, Hopper empty / full , smell in the toilet , overhead tank level , Boiler roof / Penthouse temperature, Dust SPM at chimney top, Battery voltage, Current, CT Cell temperature , Chlorine gas leakage at PT Plant etc. are the type of data where one can expect automated logging without much interference.
The same feats can be achieved using single board computers like Raspberry Pi, Beagle bone, Orange Pi, Banana Pi others. While the cost of those runs in $50 – $ 100, the cost of these systems is hardly $3.5 for ESP8266 and $7.5 for ESP32. The single board computer takes minimum 2-watt power to run beside may hang or slow down at times but this system once burnt into the memory will never hang.
ChartDirector output
Curve2.php
Curve1.php
