WOKWI ESP32 LED Control , Node-Red MQTT SQLITE 　

const char broker[] = "test.mosquitto.org";
//const char broker[] = "broker.mqtt-dashboard.com";
int        port     = 1883;

const char *SubTopic1 = "alex9ufo/LED_control";
const char *PubTopic2 = "alex9ufo/LED_status";

Wokwi硬體

Wokwi 程式庫

從程式 #include <ArduinoMqttClient.h> copy ArduinoMqttClient

Wokwi程式 (控制led on ,off , timer ,flash)

// Wifi 與 MQttClient 程式庫
#include <ArduinoMqttClient.h>
#include <WiFi.h>
//#include "arduino_secrets1.h"


//#define LED 13           //定義LED接腳
int LED = 13;
///////please enter your sensitive data in the Secret tab/arduino_secrets.h
//char ssid[] = "TOTOLINK_A3002MU";    // your network SSID (name)
//char pass[] = "24063173";    // your network password (use for WPA, or use as key for WEP)
// WiFi SSID password , SSID 和密碼進行Wi-Fi 設定 

///////please enter your sensitive data in the Secret tab/arduino_secrets.h
char ssid[] =  "Wokwi-GUEST"; // your network SSID (name)
char pass[] =  "" ;           // your network password (use for WPA, or use as key for WEP)


//char ssid[] = "dlink-103A";    // your network SSID (name)
//char pass[] = "bdcce12882";    // your network password (use for WPA, or use as key for WEP)

WiFiClient wifiClient;
MqttClient mqttClient(wifiClient);

const char broker[] = "test.mosquitto.org";
//const char broker[] = "broker.mqtt-dashboard.com";
int        port     = 1883;
String json = "";

const char *SubTopic1 = "alex9ufo/LED_control";
const char *PubTopic2 = "alex9ufo/LED_status";
//const char *PubTopic3 = "alex9ufo/2024/RFID/RFID_UID";
//const char *PubTopic4 = "alex9ufo/2024/RFID/RFID_PICC";

const char willTopic[] = "alex9ufo/2024/RFID/Starting";

//===========================================================
//布林代數 LED狀態 是否連上網路ESP32 ready ?
bool ledState = false;
bool atwork = false;
bool Send = false;  //true
String LEDjson = "";
int Count= 0;
bool Flash = false;  //true
bool Timer = false;  //true

//===========================================================
void onMqttMessage(int messageSize) {
  // we received a message, print out the topic and contents
  Serial.print("Received a message with topic '");
  Serial.print(mqttClient.messageTopic());
  String Topic= mqttClient.messageTopic();
  Serial.print("', duplicate = ");
  Serial.print(mqttClient.messageDup() ? "true" : "false");
  Serial.print(", QoS = ");
  Serial.print(mqttClient.messageQoS());
  Serial.print(", retained = ");
  Serial.print(mqttClient.messageRetain() ? "true" : "false");
  Serial.print("', length ");
  Serial.print(messageSize);
  Serial.println(" bytes:");
  String message="";
  // use the Stream interface to print the contents
  while (mqttClient.available()) {
    //Serial.print((char)mqttClient.read());
    message += (char)mqttClient.read();
  }

  Serial.println(message);
  message.trim();
  Topic.trim();

  if (Topic=="alex9ufo/LED_control") {
  if (message == "on") {
    digitalWrite(LED, LOW);  // Turn on the LED
    //ledState = true;  //ledState = ture HIGH
    //設定 各個 旗號
    LEDjson ="ON";
    Send = true ;
    Serial.print("LED =");
    Serial.println(LEDjson);
  }

  if (message == "off" ) {
    digitalWrite(LED, HIGH); // Turn off the LED
    //ledState = false; //ledState = false LOW
    LEDjson ="OFF";
    Send = true ;
    Serial.print("LED =");
    Serial.println(LEDjson);
  }
  
  if (message == "flash" ) {
    digitalWrite(LED, HIGH); // Turn off the LED
    Flash = true;
    Timer = false;
    LEDjson ="FLASH";
    Send = true ;  
    Serial.print("LED =");
    Serial.println(LEDjson);      
  }

  if (message == "timer" ) {
    digitalWrite(LED, LOW); // Turn off the LED
    Flash = false;
    Timer = true;
    LEDjson ="TIMER";
    Send = true ;
    Count= 11;
    Serial.print("LED =");
    Serial.println(LEDjson);   
  }
  
    Serial.println();
    Serial.println("-----------------------");
  }  

}

//===========================================================
String printHex(byte *buffer, byte bufferSize) {
      String id = "";
      for (byte i = 0; i < bufferSize; i++) {
        id += buffer[i] < 0x10 ? "0" : "";
        id += String(buffer[i], HEX);
        id +=" ";
      }
      return id;
}
//===========================================================
//副程式  setup wifi
void setup_wifi() {
  delay(10);
  // We start by connecting to a WiFi network
  Serial.println();
  Serial.print("Connecting to ");
  Serial.println(ssid);     //print ssid
  WiFi.begin(ssid, pass);  //初始化WiFi 函式庫並回傳目前的網路狀態
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }   //假設 wifi 未連接 show ………

  Serial.println("");
  Serial.println("WiFi connected");
  Serial.println("IP address: ");
  Serial.println(WiFi.localIP());
}  
//===========================================================
//判斷 旗號Flash , Timer 是否為真
void LED_Message() {
  //判斷 旗號 Flash / timer  是否為真 ? 閃爍 定時
  
  if (Flash){
    digitalWrite(LED, !digitalRead(LED));
    delay(500);
    if (digitalRead(LED))
      ledState = true;
    else
      ledState = false;

  } //(Flash)

  if (Timer) {
    digitalWrite(LED, LOW);
    delay(500);
    if (digitalRead(LED))
      ledState = true;
    else
      ledState = false;

  Count=Count-1;
  if (Count == 0 ){
    Timer=false;
    digitalWrite(LED, HIGH);
    ledState = false;
    }
  } //(Timer)
  
  
  ////判斷 旗號 Send 是否為真 回傳MQTT訊息到MQTT Broker　
  if (Send) {
    // Convert JSON string to character array
    Serial.print("Publish message: ");
    Serial.println(LEDjson);
    LEDjson.trim();

    bool retained = false;
    int qos = 1;
    bool dup = false;
    
    // Publish JSON character array to MQTT topic
    mqttClient.beginMessage(PubTopic2,  LEDjson.length(), retained, qos, dup);  //LED Status
    mqttClient.print(LEDjson);
    mqttClient.endMessage();
    Send = false;    //處理過後 旗號 Send為假
  }

} 
//=========================================================== 
void setup() {
  pinMode(LED, OUTPUT);
  digitalWrite(LED, HIGH);  // Turn off the LED initially
  //Initialize serial and wait for port to open: 
  Serial.begin(115200);   // Initialize serial communications with the PC
  while (!Serial);    // Do nothing if no serial port is opened (added for Arduinos based on ATMEGA32U4)
  
  setup_wifi();
  Serial.println("You're connected to the network");
  Serial.println();
  
  String willPayload = "ESP32 Start working....!";
  bool willRetain = true;
  int willQos = 1;

  mqttClient.beginWill(willTopic, willPayload.length(), willRetain, willQos);
  mqttClient.print(willPayload);
  mqttClient.endWill();

  Serial.print("Attempting to connect to the MQTT broker: ");
  Serial.println(broker);

  if (!mqttClient.connect(broker, port)) {
    Serial.print("MQTT connection failed! Error code = ");
    Serial.println(mqttClient.connectError());

    while (1);
  }

  Serial.println("You're connected to the MQTT broker!");
  Serial.println();

  // set the message receive callback
  mqttClient.onMessage(onMqttMessage);
  Serial.print("Subscribing to topic: ");
  Serial.println(SubTopic1);
  // subscribe to a topic
  // the second parameter sets the QoS of the subscription,
  // the the library supports subscribing at QoS 0, 1, or 2
  int subscribeQos = 1;
  mqttClient.subscribe(SubTopic1, subscribeQos);

  Serial.println();
}
//===========================================================
void loop() {
  // call poll() regularly to allow the library to receive MQTT messages and
  // send MQTT keep alives which avoids being disconnected by the broker
  mqttClient.poll();
  LED_Message();
  Serial.println();

}

////===========================================================

Node-Red程式及設定畫面

需要的節點有 dashboard , sqilte , mqtt in , mqtt out ,node-red-node-ui-table

匯入程式暨節點安裝

SQLite DB Browser : https://sqlitebrowser.org/dl/

Downloads

(Please consider sponsoring us on Patreon 😄)

Windows

Our latest release (3.13.1) for Windows:

Free code signing provided by SignPath.io, certificate by SignPath Foundation.

資料庫位置 C:\Users\User\.node-red\202409-LED_STATUS.db

用dbbrower 開啟檔案 C:\Users\User\.node-red\202409-LED_STATUS.db

Node-Red程式

資料庫系統建立資料庫(Create) 新增一筆(insert) 檢視資料庫(Select) 刪除所有資料(Delete) 刪除資料庫(Drop )

資料庫位置	C:\Users\User\.node-red\202409-LED_STATUS.db
建立資料庫(Create)	CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id));
新增一筆(insert)	INSERT INTO LEDSTATUS (STATUS , Date , Time )values("on", "11/01" , "21:05")
檢視資料庫(Select)	SELECT * FROM LEDSTATUS
刪除所有資料(Delete)	DELETE from LEDSTATUS
刪除資料庫(Drop table )	DROP TABLE LEDSTATUS

LED控制命令

MQTT out	Broker : test.mosquitto.org :1883 Topic : alex9ufo/LED_control
ON	Payload : on
OFF	Payload : off
TIMER	Payload : timer
FLASH	Payload : flash
SQLite
Dashboard Text

資料庫

LED_STATUS

SQLite

</> SQL Query : Via msg.topic

建立資料庫

//CREATE TABLE LEDSTATUS (

//id INTEGER,

//STATUS TEXT,

//Date DATE,

//Time TIME,

//PRIMARY KEY (id)

//);

//CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id));

msg.topic = "CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id))";

return msg;

檢視資料庫資料

//INSERT INTO LEDSTATUS (

//id INTEGER,

//STATUS TEXT,

//Date DATE,

//Time TIME,

//PRIMARY KEY (id)

//);

//SELECT * FROM LEDSTATUS ORDER BY id DESC LIMIT 50;

msg.topic = "SELECT * FROM LEDSTATUS ORDER BY id DESC LIMIT 50";

return msg;

刪除所有資料

//CREATE TABLE LEDSTATUS (

//id INTEGER,

//STATUS TEXT,

//Date DATE,

//Time TIME,

//PRIMARY KEY (id)

//);

//CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id));

msg.topic = "DELETE from LEDSTATUS";

return msg;

Function 100

Function 99

確認

Yes , Cancel

var topic=msg.payload;

if (topic==""){

return [msg,null];

}

if (topic=="Cancel"){

return [null,msg];

}

return msg;

刪除資料庫

DROP Database

//CREATE TABLE LEDSTATUS (

//id INTEGER,

//STATUS TEXT,

//Date DATE,

//Time TIME,

//PRIMARY KEY (id)

//);

//CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id));

msg.topic = "DROP TABLE LEDSTATUS";

return msg;

查詢一筆資料

var id = msg.payload.id;

var s=global.get("SEL1")

msg.topic="";

var temp="";

if (s==1)

{

temp ="SELECT * FROM LEDSTATUS";

temp=temp+" WHERE id LIKE '"+ id +"'";

}

msg.topic=temp;

global.set("SEL1",0);

return msg;

輸入id

刪除一筆資料

var id = msg.payload.id;

var s=global.get("SEL2")

msg.topic="";

var temp="";

if (s==2)

{

temp ="DELETE FROM LEDSTATUS";

temp=temp+" WHERE id LIKE '"+ id +"'";

}

msg.topic=temp;

global.set("SEL2",0)

return msg;

Store ID資料 function

var id = msg.payload.id;

global.set("ID",id)

return msg;

查詢一筆資料 function

var id = global.get("ID");

msg.topic="";

var temp="";

temp ="SELECT * FROM LEDSTATUS";

temp=temp+" WHERE id LIKE '"+ id +"'";

msg.topic=temp;

return msg;

Line Notify的發行

MQTT in
Function	msg.payload=" ---ESP32回來資料---" +msg.payload; return msg;
Set Line API	msg.headers = {'content-type':'application/x-www-form-urlencoded','Authorization':'Bearer A4wwPNh12WqB7dlfeQyyIAwtggn1kfZSI5LkkCdia1gB'}; msg.payload = {"message":msg.payload}; return msg; //oR7KdXvK1eobRr2sRRgsl4PMq23DjDlhfUs96SyUBZu
http 請求	POST https://notify-api.line.me/api/notify
Dashboard Text	{{msg.payload}}