Building Your Own ESP8266/ESP32 Over-The-Air Firmware Updater [A How-To Guide] Part II

mysql> CREATE DATABASE OTA;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE Ping (
    -> mac_id CHAR(17) PRIMARY KEY NOT NULL,
    -> available_firmware_version INT(2) NOT NULL,
    -> last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO Ping (mac_id, available_firmware_version) VALUES ('CC:50:E3:DC:90:2A',2);
Query OK, 1 row affected (0.01 sec)

var mysql = require('mysql')
var config = require('../js/config')

var connection = mysql.createConnection({
	host: 'localhost',
	user: 'root',
	password: config.db_password,
	database: 'OTA'
})

var ping = function(mac_id, callback){

	var sql = "SELECT * FROM Ping WHERE mac_id = ?"

	connection.query(sql, mac_id, function(err, rows, fields) {
		if (!err) {
			if (rows.length === 0) {
				callback(null, null)
			} else {
				callback(null, rows)
			}
		} else {
			console.log(err)
			callback(err, null)
		}
	})

}

module.exports = {
	ping: ping
}

var express = require('express')
var path = require('path')
var router = express.Router()
var md5 = require('md5-file')
var db = require('../js/databaseHandler')

router.get('/update', function(req, res, next) {

	var updateVersion = req.headers['x-esp8266-version']

	var filePath = path.join(__dirname, '../updates/plant_sensor_v'+updateVersion+'.bin')

	var options = {
		headers: {
			"x-MD5":  md5.sync(filePath)
		}
	}

	res.sendFile(filePath, function (err) {
		if (err) {
			next(err)
		} else {
			console.log('Sent:', filePath)
		}
	})

})

router.post('/ping', function(req, res, next) {

	var mac_id = req.body.mac_id

	console.log(req.body)
	console.log(mac_id)

	db.ping(mac_id,function(err, results){
		(results) ? res.send({'mac_id':results[0].mac_id,'available_firmware_version':results[0].available_firmware_version}) : res.send({'error':err}) 
	})

})

module.exports = router

#define firmware_version 1 // For the first sketch
#define firmware_version 2 // For the second sketch

#include <ESP8266httpUpdate.h>
#include <ESP8266HTTPClient.h>
#include <ArduinoJson.h>
#include "DHT.h"

// Set out Wifi auth constants.
const char* ssid =     "Yolo";
const char* password = "password";

// Define our constants.
#define DHTPIN 5
#define DHTTYPE DHT11
#define CSMS A0

// Set your firmware version here. Your other sketch should have a different version number.
#define firmware_version 1

// Initialise the DHT11 sensor.
DHT dht(DHTPIN, DHTTYPE);

// Declare DHT output value variables.
int output_value;
int output_raw;

void setup() {

  // Initialise Serial connection.
  Serial.begin(74880);
  Serial.setDebugOutput(true);

  // Start HTTPClient.
  HTTPClient http;

  // Start Wifi.
  WiFi.begin(ssid, password);
  while (WiFi.status() != WL_CONNECTED) {
    Serial.print(".");
    delay(1000);
  }

  // Record our MAC address.
  String mac = "mac_id=" + String(WiFi.macAddress());

  // Allocate the JSON document.
  const size_t capacity = JSON_OBJECT_SIZE(2) + 60;
  DynamicJsonDocument doc(capacity);

  // Set up our HTTP request.
  http.begin("http://test.derkzomer.com/ping");      //Specify request destination
  http.addHeader("Content-Type", "application/x-www-form-urlencoded");  //Specify content-type header

  // Send the request and get the response payload.
  int httpCode = http.POST(mac);
  String payload = http.getString();

  //Close HTTP connection.
  http.end();

  // Parse the received JSON object.
  deserializeJson(doc, payload);
  const char* macId = doc["macId"];
  int available_firmware_version = doc["available_firmware_version"];
  String fwv = String(available_firmware_version);

  // Check whether your firmware is outdated.
  if (available_firmware_version > firmware_version) {

    Serial.println("Your firmware version is V"+String(firmware_version)+", the latest available firmware version is V"+available_firmware_version)+".";
    Serial.println("Installing the new update now...");
  
    t_httpUpdate_return ret = ESPhttpUpdate.update("http://test.derkzomer.com/update",fwv);
    
    switch(ret) {
        case HTTP_UPDATE_FAILED:
            Serial.printf("[update] Update failed (%d): %s", ESPhttpUpdate.getLastError(), ESPhttpUpdate.getLastErrorString().c_str());
            break;
        case HTTP_UPDATE_NO_UPDATES:
            Serial.println("[update] Update no Update.");
            break;
        case HTTP_UPDATE_OK:
            Serial.println("[update] Update ok."); // may not be called since we reboot the ESP
            break;
    }

  } else {
    Serial.println("Your firmware version is V"+String(firmware_version)+", the latest available firmware version is V"+available_firmware_version)+".";
    Serial.println("You have the latest version.");
  }

  // Start the DHT11 sensor.
  dht.begin();

}

void loop() {

  // Measure and map the raw moisture output from the moisture sensor.
  output_raw = analogRead(CSMS);
  output_value = map(output_raw, 725, 330, 0, 100);

  // Print the mapped output from the moisture sensor.
  Serial.print("Moisture : ");
  Serial.print(output_value);
  Serial.println("%");

  // Measure the humidity and temperature from the DHT11.
  float h = dht.readHumidity();
  float t = dht.readTemperature();
  float f = dht.readTemperature(true);

  // Check whether the DHT11 sensor is working.
  if (isnan(h) || isnan(t) || isnan(f)) {
    Serial.println(F("Failed to read from DHT sensor!"));
    delay(2000);
    return;
  }

  // Index the DHT11 values.
  float hif = dht.computeHeatIndex(f, h);
  float hic = dht.computeHeatIndex(t, h, false);

  // Print the DHT11 humidity and temperature values.
  Serial.print(F("Humidity: "));
  Serial.print(h);
  Serial.print(F("%  Temperature: "));
  Serial.print(t);

  // Wait five seconds before the next measurement.
  delay(5000);

}