MQTT + WOKWI ESP32 + Node-Red (Dashboard , SQLite)
WOKWI-ESP32程式
#include <WiFi.h>
#include <PubSubClient.h>
#include <DHT.h>
#include <DHT_U.h> // 需要同時包含 DHT_U.h
// --- Wi-Fi 設定 ---
const char* ssid = "Wokwi-GUEST";
const char* password = "";
// --- MQTT 設定 ---
const char* mqtt_server = "broker.mqttgo.io"; // 或 "mqtt.eclipseprojects.io"
const int mqtt_port = 1883;
const char* mqtt_client_id = "ESP32_Wokwi_Client";
// MQTT 主題
const char* mqtt_topic_led_control = "wokwi/led/control";
const char* mqtt_topic_temperature = "wokwi/dht/temperature";
const char* mqtt_topic_humidity = "wokwi/dht/humidity";
WiFiClient espClient;
PubSubClient client(espClient);
// --- LED 設定 ---
const int ledPin = 2; // 連接到 GPIO 2
enum LedMode { ON, OFF, FLASH, TIMER };
volatile LedMode currentLedMode = OFF;
volatile unsigned long timerStartTime = 0;
volatile bool ledState = false; // 用於閃爍模式
// --- DHT22 設定 ---
#define DHTPIN 4 // 連接到 GPIO 4
#define DHTTYPE DHT22 // DHT 22 (AM2302), AM2321
DHT dht(DHTPIN, DHTTYPE);
// --- 任務句柄 ---
TaskHandle_t TaskLEDControl = NULL;
TaskHandle_t TaskDHTSensor = NULL;
// --- 函式宣告 ---
void setup_wifi();
void reconnect_mqtt();
void callback(char* topic, byte* payload, unsigned int length);
void ledControlTask(void *pvParameters);
void dhtSensorTask(void *pvParameters);
void setup() {
Serial.begin(115200);
pinMode(ledPin, OUTPUT);
digitalWrite(ledPin, LOW); // 確保初始是關閉的
setup_wifi();
client.setServer(mqtt_server, mqtt_port);
client.setCallback(callback);
dht.begin(); // 初始化 DHT 感測器
// 創建 LED 控制任務，運行在 Core 0
xTaskCreatePinnedToCore(
ledControlTask, /* 任務函式 */
"LED Control", /* 任務名稱 */
2048, /* 堆疊大小 (字節) */
NULL, /* 任務參數 */
1, /* 任務優先級 */
&TaskLEDControl, /* 任務句柄 */
0 /* 運行在 Core 0 */
);
// 創建 DHT 感測器任務，運行在 Core 1
xTaskCreatePinnedToCore(
dhtSensorTask, /* 任務函式 */
"DHT Sensor", /* 任務名稱 */
4096, /* 堆疊大小 (字節) */
NULL, /* 任務參數 */
1, /* 任務優先級 */
&TaskDHTSensor, /* 任務句柄 */
1 /* 運行在 Core 1 */
);
}
void loop() {
// 主循環中只負責維持 MQTT 連線
if (!client.connected()) {
reconnect_mqtt();
}
client.loop(); // 處理 MQTT 訊息
delay(10); // 短暫延遲，避免佔用太多 CPU
}
// --- Wi-Fi 連線函式 ---
void setup_wifi() {
delay(10);
Serial.println();
Serial.print("Connecting to ");
Serial.println(ssid);
WiFi.begin(ssid, password);
while (WiFi.status() != WL_CONNECTED) {
delay(500);
Serial.print(".");
}
Serial.println("");
Serial.println("WiFi connected");
Serial.print("IP address: ");
Serial.println(WiFi.localIP());
}
// --- MQTT 重連函式 ---
void reconnect_mqtt() {
while (!client.connected()) {
Serial.print("Attempting MQTT connection...");
// 嘗試連線
if (client.connect(mqtt_client_id)) {
Serial.println("connected");
// 訂閱 LED 控制主題
client.subscribe(mqtt_topic_led_control);
Serial.print("Subscribed to: ");
Serial.println(mqtt_topic_led_control);
} else {
Serial.print("failed, rc=");
Serial.print(client.state());
Serial.println(" try again in 5 seconds");
// 等待 5 秒後重試
delay(5000);
}
}
}
// --- MQTT 訊息回調函式 ---
void callback(char* topic, byte* payload, unsigned int length) {
Serial.print("Message arrived [");
Serial.print(topic);
Serial.print("] ");
String message = "";
for (int i = 0; i < length; i++) {
message += (char)payload[i];
}
Serial.println(message);
if (String(topic) == mqtt_topic_led_control) {
if (message == "on") {
currentLedMode = ON;
digitalWrite(ledPin, HIGH);
Serial.println("LED Mode: ON");
} else if (message == "off") {
currentLedMode = OFF;
digitalWrite(ledPin, LOW);
Serial.println("LED Mode: OFF");
} else if (message == "flash") {
currentLedMode = FLASH;
Serial.println("LED Mode: FLASH");
} else if (message == "timer") {
currentLedMode = TIMER;
digitalWrite(ledPin, HIGH); // 定時模式開始時先開啟 LED
timerStartTime = millis();
Serial.println("LED Mode: TIMER (10s)");
} else {
Serial.println("Unknown LED command.");
}
}
}
// --- LED 控制任務 (運行在 Core 0) ---
void ledControlTask(void *pvParameters) {
(void) pvParameters; // 避免編譯器警告
for (;;) { // 無限循環
switch (currentLedMode) {
case ON:
// LED 保持亮著，由 callback 函式設置
break;
case OFF:
// LED 保持熄滅，由 callback 函式設置
break;
case FLASH:
digitalWrite(ledPin, ledState);
ledState = !ledState;
vTaskDelay(pdMS_TO_TICKS(500)); // 每 500ms 改變一次狀態
break;
case TIMER:
if (millis() - timerStartTime >= 10000) { // 10 秒
digitalWrite(ledPin, LOW);
currentLedMode = OFF; // 定時結束後轉為 OFF 模式
Serial.println("LED Timer finished. LED OFF.");
}
vTaskDelay(pdMS_TO_TICKS(10)); // 短暫延遲
break;
default:
digitalWrite(ledPin, LOW); // 預設為關閉
break;
}
vTaskDelay(pdMS_TO_TICKS(10)); // 短暫延遲，讓其他任務有機會執行
}
}
// --- DHT 感測器任務 (運行在 Core 1) ---
void dhtSensorTask(void *pvParameters) {
(void) pvParameters; // 避免編譯器警告
for (;;) { // 無限循環
delay(2000); // 每 2 秒讀取一次數據，避免頻繁讀取導致錯誤
float h = dht.readHumidity();
float t = dht.readTemperature(); // 讀取攝氏溫度
// 檢查是否讀取失敗，如果是則嘗試重讀
if (isnan(h) || isnan(t)) {
Serial.println(F("Failed to read from DHT sensor!"));
} else {
Serial.print(F("Humidity: "));
Serial.print(h);
Serial.print(F("% Temperature: "));
Serial.print(t);
Serial.println(F("°C"));
// 發布溫度
char tempString[8];
dtostrf(t, 4, 2, tempString); // 浮點數轉字串
client.publish(mqtt_topic_temperature, tempString);
// 發布濕度
char humString[8];
dtostrf(h, 4, 2, humString); // 浮點數轉字串
delay(250);
client.publish(mqtt_topic_humidity, humString);
}
vTaskDelay(pdMS_TO_TICKS(5000)); // 每 5 秒執行一次，避免 MQTT 發布過於頻繁
}
}
Node-Red Function程式
var now = new Date();
var vdate = now.toLocaleDateString('zh-TW', { year: 'numeric', month: '2-digit', day: '2-digit' }).replace(/\//g, '-');
var vtime = now.toLocaleTimeString('zh-TW', { hour12: false });
var vevent = "LED TIMER";
msg.topic = "INSERT INTO events (date, time, event) VALUES ($vdate , $vtime , $venevt)";
msg.payload = [vdate, vtime, vevent];
return msg;
//CREATE TABLE "events" (
// "id" INTEGER,
// "date" TEXT,
// "time" TEXT,
// "event" TEXT,
// PRIMARY KEY("id" AUTOINCREMENT)
//);
Node-Red程式
[
{
"id": "61815866096b154f",
"type": "ui_button",
"z": "d084cb454b3df16f",
"name": "ON",
"group": "539f4088006e8909",
"order": 1,
"width": 0,
"height": 0,
"passthru": false,
"label": "ON",
"tooltip": "",
"color": "",
"bgcolor": "",
"className": "",
"icon": "",
"payload": "on",
"payloadType": "str",
"topic": "wokwi/led/control",
"topicType": "str",
"x": 130,
"y": 100,
"wires": [
[
"c324a750b628fc7b",
"cc6d203836faad80",
"70a95c4ab761acc2",
"620c661bbb3af567"
]
]
},
{
"id": "c324a750b628fc7b",
"type": "mqtt out",
"z": "d084cb454b3df16f",
"name": "LED Control",
"topic": "wokwi/led/control",
"qos": "0",
"retain": "false",
"respTopic": "",
"contentType": "",
"userProps": "",
"correl": "",
"expiry": "",
"broker": "192c2b20bef1e71a",
"x": 410,
"y": 180,
"wires": []
},
{
"id": "4828f29d421795f8",
"type": "ui_button",
"z": "d084cb454b3df16f",
"name": "OFF",
"group": "539f4088006e8909",
"order": 2,
"width": 0,
"height": 0,
"passthru": false,
"label": "OFF",
"tooltip": "",
"color": "",
"bgcolor": "",
"className": "",
"icon": "",
"payload": "off",
"payloadType": "str",
"topic": "wokwi/led/control",
"topicType": "str",
"x": 130,
"y": 160,
"wires": [
[
"c324a750b628fc7b",
"cc6d203836faad80",
"5a842298ea492e96",
"620c661bbb3af567"
]
]
},
{
"id": "fef947525a77314a",
"type": "ui_button",
"z": "d084cb454b3df16f",
"name": "FLASH",
"group": "539f4088006e8909",
"order": 3,
"width": 0,
"height": 0,
"passthru": false,
"label": "FLASH",
"tooltip": "",
"color": "",
"bgcolor": "",
"className": "",
"icon": "",
"payload": "flash",
"payloadType": "str",
"topic": "wokwi/led/control",
"topicType": "str",
"x": 140,
"y": 220,
"wires": [
[
"c324a750b628fc7b",
"cc6d203836faad80",
"7153ecdf7496b330",
"620c661bbb3af567"
]
]
},
{
"id": "3306ac069e278ad5",
"type": "ui_button",
"z": "d084cb454b3df16f",
"name": "TIMER (10s)",
"group": "539f4088006e8909",
"order": 4,
"width": 0,
"height": 0,
"passthru": false,
"label": "TIMER (10s)",
"tooltip": "",
"color": "",
"bgcolor": "",
"className": "",
"icon": "",
"payload": "timer",
"payloadType": "str",
"topic": "wokwi/led/control",
"topicType": "str",
"x": 150,
"y": 280,
"wires": [
[
"c324a750b628fc7b",
"cc6d203836faad80",
"14d6604ca2b5e367",
"620c661bbb3af567"
]
]
},
{
"id": "ce4ed3f2ed123c3e",
"type": "mqtt in",
"z": "d084cb454b3df16f",
"name": "Temperature",
"topic": "wokwi/dht/temperature",
"qos": "1",
"datatype": "utf8",
"broker": "192c2b20bef1e71a",
"nl": false,
"rap": true,
"rh": 0,
"inputs": 0,
"x": 130,
"y": 640,
"wires": [
[
"c59bfae2b132cc5d",
"f4ca533264449435",
"4b9c47e826f9c4a7",
"857738e03e4f7ea3"
]
]
},
{
"id": "0718c4b08cfe5894",
"type": "mqtt in",
"z": "d084cb454b3df16f",
"name": "Humidity",
"topic": "wokwi/dht/humidity",
"qos": "1",
"datatype": "utf8",
"broker": "192c2b20bef1e71a",
"nl": false,
"rap": true,
"rh": 0,
"inputs": 0,
"x": 120,
"y": 740,
"wires": [
[
"47fd1089d0930f49",
"68a296c2051dad29",
"bbe3f770af5ebcdd",
"857738e03e4f7ea3"
]
]
},
{
"id": "a1326a650285d760",
"type": "ui_chart",
"z": "d084cb454b3df16f",
"name": "Temperature Chart",
"group": "182b827e8d2e8b0b",
"order": 1,
"width": 5,
"height": 4,
"label": "Temperature (°C)",
"chartType": "line",
"legend": "true",
"xformat": "HH:mm:ss",
"interpolate": "step",
"nodata": "",
"dot": false,
"ymin": "-40",
"ymax": "80",
"removeOlder": "10",
"removeOlderPoints": "",
"removeOlderUnit": "60",
"cutout": "",
"useOneColor": false,
"useUTC": false,
"colors": [
"#ff0000",
"#aec7e8",
"#ff7f0e",
"#2ca02c",
"#98df8a",
"#d62728",
"#ff9896",
"#9467bd",
"#c5b0d5"
],
"outputs": 1,
"useDifferentColor": false,
"className": "",
"x": 630,
"y": 600,
"wires": [
[]
]
},
{
"id": "c1188f9185e11303",
"type": "ui_chart",
"z": "d084cb454b3df16f",
"name": "Humidity Chart",
"group": "182b827e8d2e8b0b",
"order": 2,
"width": 5,
"height": 4,
"label": "Humidity (%)",
"chartType": "line",
"legend": "true",
"xformat": "HH:mm:ss",
"interpolate": "step",
"nodata": "",
"dot": false,
"ymin": "0",
"ymax": "100",
"removeOlder": "10",
"removeOlderPoints": "",
"removeOlderUnit": "60",
"cutout": "",
"useOneColor": false,
"useUTC": false,
"colors": [
"#1f77b4",
"#aec7e8",
"#ff7f0e",
"#2ca02c",
"#98df8a",
"#d62728",
"#ff9896",
"#9467bd",
"#c5b0d5"
],
"outputs": 1,
"useDifferentColor": false,
"className": "",
"x": 620,
"y": 640,
"wires": [
[]
]
},
{
"id": "f4ca533264449435",
"type": "debug",
"z": "d084cb454b3df16f",
"name": "Debug Temperature",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 350,
"y": 500,
"wires": []
},
{
"id": "68a296c2051dad29",
"type": "debug",
"z": "d084cb454b3df16f",
"name": "Debug Humidity",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 320,
"y": 840,
"wires": []
},
{
"id": "c59bfae2b132cc5d",
"type": "function",
"z": "d084cb454b3df16f",
"name": "To Number (Temp)",
"func": "msg.payload = parseFloat(msg.payload);\nreturn msg;",
"outputs": 1,
"timeout": "",
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 350,
"y": 620,
"wires": [
[
"a1326a650285d760",
"3dd77fbd0f8077c3"
]
]
},
{
"id": "47fd1089d0930f49",
"type": "function",
"z": "d084cb454b3df16f",
"name": "To Number (Hum)",
"func": "msg.payload = parseFloat(msg.payload);\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 330,
"y": 700,
"wires": [
[
"c1188f9185e11303",
"7c3e0007df7586ec"
]
]
},
{
"id": "3dd77fbd0f8077c3",
"type": "ui_gauge",
"z": "d084cb454b3df16f",
"name": "",
"group": "182b827e8d2e8b0b",
"order": 3,
"width": 5,
"height": 4,
"gtype": "gage",
"title": "Temperature",
"label": "℃",
"format": "{{value}}",
"min": "-40",
"max": "80",
"colors": [
"#00b500",
"#e6e600",
"#ca3838"
],
"seg1": "25",
"seg2": "40",
"diff": false,
"className": "",
"x": 610,
"y": 560,
"wires": []
},
{
"id": "7c3e0007df7586ec",
"type": "ui_gauge",
"z": "d084cb454b3df16f",
"name": "",
"group": "182b827e8d2e8b0b",
"order": 4,
"width": 5,
"height": 4,
"gtype": "gage",
"title": "Humidity",
"label": "%",
"format": "{{value}}",
"min": 0,
"max": "100",
"colors": [
"#00b500",
"#e6e600",
"#ca3838"
],
"seg1": "65",
"seg2": "80",
"diff": false,
"className": "",
"x": 600,
"y": 680,
"wires": []
},
{
"id": "cc6d203836faad80",
"type": "debug",
"z": "d084cb454b3df16f",
"name": "debug LED payload",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 390,
"y": 40,
"wires": []
},
{
"id": "a4610ad3699752b5",
"type": "inject",
"z": "d084cb454b3df16f",
"name": "Create Table Once",
"props": [
{
"p": "topic",
"v": "CREATE TABLE IF NOT EXISTS events (id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, time TEXT, event TEXT)",
"vt": "str"
},
{
"p": "payload"
}
],
"repeat": "",
"crontab": "",
"once": true,
"onceDelay": "0.1",
"topic": "CREATE TABLE IF NOT EXISTS events (id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, time TEXT, event TEXT)",
"payload": "",
"payloadType": "date",
"x": 670,
"y": 80,
"wires": [
[
"e07591aef7cb5876"
]
]
},
{
"id": "e07591aef7cb5876",
"type": "sqlite",
"z": "d084cb454b3df16f",
"mydb": "f5c97c74cc496505",
"sqlquery": "msg.topic",
"sql": "",
"name": "DB Init/Write",
"x": 850,
"y": 160,
"wires": [
[]
]
},
{
"id": "70a95c4ab761acc2",
"type": "function",
"z": "d084cb454b3df16f",
"name": "Log LED ON",
"func": "var now = new Date();\nvar vdate = now.toLocaleDateString('zh-TW', { year: 'numeric', month: '2-digit', day: '2-digit' }).replace(/\\//g, '-');\nvar vtime = now.toLocaleTimeString('zh-TW', { hour12: false });\nvar vevent = \"LED ON\";\n\nmsg.topic = \"INSERT INTO events (date, time, event) VALUES ($vdate , $vtime , $venevt)\";\nmsg.payload = [vdate, vtime , vevent ];\nreturn msg;\n\n//CREATE TABLE \"events\" (\n//\t\"id\"\tINTEGER,\n//\t\"date\"\tTEXT,\n//\t\"time\"\tTEXT,\n//\t\"event\"\tTEXT,\n//\tPRIMARY KEY(\"id\" AUTOINCREMENT)\n//);\n",
"outputs": 1,
"timeout": "",
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 370,
"y": 80,
"wires": [
[
"e07591aef7cb5876",
"2fcca3f3fd4f5d34"
]
]
},
{
"id": "5a842298ea492e96",
"type": "function",
"z": "d084cb454b3df16f",
"name": "Log LED OFF",
"func": "var now = new Date();\nvar vdate = now.toLocaleDateString('zh-TW', { year: 'numeric', month: '2-digit', day: '2-digit' }).replace(/\\//g, '-');\nvar vtime = now.toLocaleTimeString('zh-TW', { hour12: false });\nvar vevent = \"LED OFF\";\n\nmsg.topic = \"INSERT INTO events (date, time, event) VALUES ($vdate , $vtime , $venevt)\";\nmsg.payload = [vdate, vtime , vevent ];\nreturn msg;\n\n//CREATE TABLE \"events\" (\n//\t\"id\"\tINTEGER,\n//\t\"date\"\tTEXT,\n//\t\"time\"\tTEXT,\n//\t\"event\"\tTEXT,\n//\tPRIMARY KEY(\"id\" AUTOINCREMENT)\n//);\n",
"outputs": 1,
"timeout": "",
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 400,
"y": 140,
"wires": [
[
"e07591aef7cb5876",
"2fcca3f3fd4f5d34"
]
]
},
{
"id": "7153ecdf7496b330",
"type": "function",
"z": "d084cb454b3df16f",
"name": "Log LED FLASH",
"func": "var now = new Date();\nvar vdate = now.toLocaleDateString('zh-TW', { year: 'numeric', month: '2-digit', day: '2-digit' }).replace(/\\//g, '-');\nvar vtime = now.toLocaleTimeString('zh-TW', { hour12: false });\nvar vevent = \"LED FLASH\";\n\nmsg.topic = \"INSERT INTO events (date, time, event) VALUES ($vdate , $vtime , $venevt)\";\nmsg.payload = [vdate, vtime , vevent ];\nreturn msg;\n\n//CREATE TABLE \"events\" (\n//\t\"id\"\tINTEGER,\n//\t\"date\"\tTEXT,\n//\t\"time\"\tTEXT,\n//\t\"event\"\tTEXT,\n//\tPRIMARY KEY(\"id\" AUTOINCREMENT)\n//);\n",
"outputs": 1,
"timeout": "",
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 400,
"y": 220,
"wires": [
[
"e07591aef7cb5876",
"2fcca3f3fd4f5d34"
]
]
},
{
"id": "14d6604ca2b5e367",
"type": "function",
"z": "d084cb454b3df16f",
"name": "Log LED TIMER",
"func": "var now = new Date();\nvar vdate = now.toLocaleDateString('zh-TW', { year: 'numeric', month: '2-digit', day: '2-digit' }).replace(/\\//g, '-');\nvar vtime = now.toLocaleTimeString('zh-TW', { hour12: false });\nvar vevent = \"LED TIMER\";\n\nmsg.topic = \"INSERT INTO events (date, time, event) VALUES ($vdate , $vtime , $venevt)\";\nmsg.payload = [vdate, vtime, vevent];\nreturn msg;\n\n//CREATE TABLE \"events\" (\n//\t\"id\"\tINTEGER,\n//\t\"date\"\tTEXT,\n//\t\"time\"\tTEXT,\n//\t\"event\"\tTEXT,\n//\tPRIMARY KEY(\"id\" AUTOINCREMENT)\n//);\n",
"outputs": 1,
"timeout": "",
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 400,
"y": 260,
"wires": [
[
"e07591aef7cb5876",
"2fcca3f3fd4f5d34"
]
]
},
{
"id": "4b9c47e826f9c4a7",
"type": "function",
"z": "d084cb454b3df16f",
"name": "Log Temperature",
"func": "var now = new Date();\nvar vdate = now.toLocaleDateString('zh-TW', { year: 'numeric', month: '2-digit', day: '2-digit' }).replace(/\\//g, '-');\nvar vtime = now.toLocaleTimeString('zh-TW', { hour12: false });\nvar vtemp= \"Temperature: \" + msg.payload + \" °C\" ;\n\nmsg.topic = \"INSERT INTO events (date, time, event) VALUES ($vdate , $vtime , $vtemp)\";\nmsg.payload = [vdate, vtime, vtemp];\nreturn msg;\n\n//CREATE TABLE \"events\" (\n//\t\"id\"\tINTEGER,\n//\t\"date\"\tTEXT,\n//\t\"time\"\tTEXT,\n//\t\"event\"\tTEXT,\n//\tPRIMARY KEY(\"id\" AUTOINCREMENT)\n//);\n",
"outputs": 1,
"timeout": "",
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 350,
"y": 540,
"wires": [
[
"122005c4ee1469fa"
]
]
},
{
"id": "bbe3f770af5ebcdd",
"type": "function",
"z": "d084cb454b3df16f",
"name": "Log Humidity",
"func": "\nvar now = new Date();\nvar vdate = now.toLocaleDateString('zh-TW', { year: 'numeric', month: '2-digit', day: '2-digit' }).replace(/\\//g, '-');\nvar vtime = now.toLocaleTimeString('zh-TW', { hour12: false });\nvar vhumi= \"Humidity: \" + msg.payload + \" %\" ;\n\nmsg.topic = \"INSERT INTO events (date, time, event) VALUES ($vdate , $vtime , $vhumi)\";\nmsg.payload = [vdate, vtime, vhumi];\nreturn msg;\n\n//CREATE TABLE \"events\" (\n//\t\"id\"\tINTEGER,\n//\t\"date\"\tTEXT,\n//\t\"time\"\tTEXT,\n//\t\"event\"\tTEXT,\n//\tPRIMARY KEY(\"id\" AUTOINCREMENT)\n//);\n",
"outputs": 1,
"timeout": "",
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 330,
"y": 580,
"wires": [
[
"122005c4ee1469fa"
]
]
},
{
"id": "857738e03e4f7ea3",
"type": "function",
"z": "d084cb454b3df16f",
"name": "Auto Refresh DB View",
"func": "// Trigger the database query after any log event\nmsg.topic = \"SELECT id, date, time, event FROM events ORDER BY id DESC LIMIT 50\";\nmsg.payload = [];\nreturn msg;",
"outputs": 1,
"timeout": "",
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 640,
"y": 740,
"wires": [
[
"c840152769c3e7b7"
]
]
},
{
"id": "c840152769c3e7b7",
"type": "sqlite",
"z": "d084cb454b3df16f",
"mydb": "f5c97c74cc496505",
"sqlquery": "msg.topic",
"sql": "SELECT id, date, time, event FROM events ORDER BY id DESC LIMIT 50",
"name": "Query DB",
"x": 850,
"y": 740,
"wires": [
[
"f49decc60a32479b"
]
]
},
{
"id": "f49decc60a32479b",
"type": "ui_table",
"z": "d084cb454b3df16f",
"group": "7eef0909fee51fb0",
"name": "Event Log Table",
"order": 2,
"width": "12",
"height": "10",
"columns": [
{
"field": "id",
"title": "ID",
"width": "10px",
"align": "left",
"formatter": "html",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "date",
"title": "Date",
"width": "20px",
"align": "left",
"formatter": "html",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "time",
"title": "Time",
"width": "20px",
"align": "left",
"formatter": "html",
"formatterParams": {
"target": "_blank"
}
},
{
"field": "event",
"title": "Event/Value",
"width": "40px",
"align": "left",
"formatter": "html",
"formatterParams": {
"target": "_blank"
}
}
],
"outputs": 0,
"cts": false,
"x": 1040,
"y": 740,
"wires": []
},
{
"id": "8ed130bf4b23f0db",
"type": "inject",
"z": "d084cb454b3df16f",
"name": "Refresh DB",
"props": [
{
"p": "topic",
"v": "SELECT id, date, time, event FROM events ORDER BY id DESC LIMIT 50",
"vt": "str"
},
{
"p": "payload"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "SELECT id, date, time, event FROM events ORDER BY id DESC LIMIT 50",
"payload": "",
"payloadType": "date",
"x": 640,
"y": 780,
"wires": [
[
"c840152769c3e7b7"
]
]
},
{
"id": "620c661bbb3af567",
"type": "link out",
"z": "d084cb454b3df16f",
"name": "link out 76",
"mode": "link",
"links": [
"86d256c91ed57b50"
],
"x": 365,
"y": 360,
"wires": []
},
{
"id": "86d256c91ed57b50",
"type": "link in",
"z": "d084cb454b3df16f",
"name": "link in 70",
"links": [
"620c661bbb3af567"
],
"x": 455,
"y": 760,
"wires": [
[
"857738e03e4f7ea3"
]
]
},
{
"id": "122005c4ee1469fa",
"type": "link out",
"z": "d084cb454b3df16f",
"name": "link out 77",
"mode": "link",
"links": [
"f83eb8e9418a776e"
],
"x": 585,
"y": 500,
"wires": []
},
{
"id": "f83eb8e9418a776e",
"type": "link in",
"z": "d084cb454b3df16f",
"name": "link in 71",
"links": [
"122005c4ee1469fa"
],
"x": 675,
"y": 480,
"wires": [
[
"e07591aef7cb5876"
]
]
},
{
"id": "70a296820bc4cd21",
"type": "comment",
"z": "d084cb454b3df16f",
"name": "2025LEDDHT.db ",
"info": "CREATE TABLE \"events\" (\n\t\"id\"\tINTEGER,\n\t\"date\"\tTEXT,\n\t\"time\"\tTEXT,\n\t\"event\"\tTEXT,\n\tPRIMARY KEY(\"id\" AUTOINCREMENT)\n);",
"x": 860,
"y": 220,
"wires": []
},
{
"id": "2fcca3f3fd4f5d34",
"type": "debug",
"z": "d084cb454b3df16f",
"name": "debug 350",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 610,
"y": 300,
"wires": []
},
{
"id": "539f4088006e8909",
"type": "ui_group",
"name": "LED Control",
"tab": "31b64e525281561f",
"order": 1,
"disp": true,
"width": 3,
"collapse": false,
"className": ""
},
{
"id": "192c2b20bef1e71a",
"type": "mqtt-broker",
"name": "mqttgo",
"broker": "broker.mqttgo.io",
"port": "1883",
"clientid": "",
"autoConnect": true,
"usetls": false,
"protocolVersion": "4",
"keepalive": "60",
"cleansession": true,
"autoUnsubscribe": true,
"birthTopic": "",
"birthQos": "0",
"birthRetain": "false",
"birthPayload": "",
"birthMsg": {},
"closeTopic": "",
"closeQos": "0",
"closeRetain": "false",
"closePayload": "",
"closeMsg": {},
"willTopic": "",
"willQos": "0",
"willRetain": "false",
"willPayload": "",
"willMsg": {},
"userProps": "",
"sessionExpiry": ""
},
{
"id": "182b827e8d2e8b0b",
"type": "ui_group",
"name": "DHT22 Sensor Data",
"tab": "31b64e525281561f",
"order": 2,
"disp": true,
"width": 10,
"collapse": false,
"className": ""
},
{
"id": "f5c97c74cc496505",
"type": "sqlitedb",
"db": "2025LEDDHT.db",
"mode": "RWC"
},
{
"id": "7eef0909fee51fb0",
"type": "ui_group",
"name": "Database Browser (sq.db)",
"tab": "31b64e525281561f",
"order": 3,
"disp": true,
"width": "12",
"collapse": false,
"className": ""
},
{
"id": "31b64e525281561f",
"type": "ui_tab",
"name": "ESP32 Control",
"icon": "dashboard",
"disabled": false,
"hidden": false
}
]
沒有留言:
張貼留言