收到的數據寫入 SQLite 資料庫 CRUD（建立、新增、查詢、刪除）操作功能
產生亂數 -> 發佈到 MQTT -> 訂閱 MQTT -> 將收到的數據寫入 SQLite 資料庫，並提供完整的 CRUD（建立、新增、查詢、刪除）操作功能。
流程總覽與核心功能
這個流程可以分為三個主要部分：
數據產生與發佈 (Publishing)： 產生亂數並將其送往 MQTT Broker。
數據接收與儲存 (Subscribing & Inserting)： 從 MQTT 訂閱數據，轉換格式後存入 SQLite 資料庫。
資料庫管理與顯示 (CRUD & UI)： 提供手動的建立、查詢、刪除等功能，並透過 UI 介面顯示結果。
I. 數據產生與發佈 (Publishing)
| 節點 ID | 類型 | 說明 |
inject | 每 30 秒發布一次： 作為流程的時脈，每 30 秒觸發一次。 | |
function | 產生亂數 (0-100)： 產生 $0 \sim 100$ 之間的隨機整數，並將其設置為 msg.payload。 | |
mqtt out | 發行亂數： 將亂數發佈到主題 alex9ufo/sqlite/number。 | |
debug | 顯示亂數生成後的輸出訊息，用於驗證發佈前的數據。 |
II. 數據接收與 SQLite 插入 (Inserting)
| 節點 ID | 類型 | 說明 |
mqtt in | 訂閱主題： 訂閱主題 alex9ufo/sqlite/number，接收發佈的亂數。 | |
function | 格式化為 INSERT 語句： 這是關鍵的數據處理環節：
1. 獲取當前系統的 日期 (date) 和 時間 (time)。
2. 將接收到的亂數 (msg.payload) 轉換為浮點數 (number)。
3. 構建 SQL 語句 (msg.topic)：INSERT INTO data_log (date, time, number) VALUES ($date, $time, $number)。
4. 將日期、時間和數字組成陣列作為 msg.payload，準備給 SQLite 節點作為參數使用。 | |
sqlite | SQLite 資料庫 (寫入)： 接收 INSERT 語句，將數據新增到 data_log 資料表中。 | |
debug | 顯示 INSERT 語句的輸入（msg.topic 和 msg.payload），用於調試數據庫寫入的參數。 |
III. 資料庫管理與 UI 顯示 (CRUD)
這個區塊使用 ui_button 和 inject 節點來手動觸發資料庫操作，結果會顯示在 Dashboard UI 的 節點上。
1. 建立、查詢、刪除資料表
| 節點 ID | 類型 | 功能 | SQL 語句 (設定於 msg.topic) |
ui_button / function | CREATE (建立資料表) | CREATE TABLE IF NOT EXISTS data_log (id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, time TEXT, number REAL) | |
ui_button / function | SELECT (顯示資料) | SELECT * FROM data_log ORDER BY id DESC LIMIT 20 | |
ui_button / function | DROP (刪除資料表) | DROP TABLE data_log |
2. 刪除單筆記錄 (DELETE)
| 節點 ID | 類型 | 說明 |
ui_text_input | 用於在 UI 介面輸入要刪除的記錄 ID 號碼。 | |
function | 儲存 ID： 將輸入的 ID 儲存到 flow 變數 (flow.get('deleteId')) 中，供 DELETE 按鈕使用。 | |
ui_button | DELETE 按鈕： 點擊時觸發刪除操作。 | |
function | 構建 DELETE 語句： 從 flow 變數中讀取 ID，並構建 DELETE FROM data_log WHERE id = [ID] 的 SQL 語句給 SQLite 節點。 |
3. 輸出節點
| 節點 ID | 類型 | 說明 |
debug | SQL 結果 DEBUG： 顯示所有 CRUD 操作（建立、新增、查詢、刪除）後的回應訊息，可查看是否成功。 | |
ui_table | 資料庫內容： 接收 SELECT 查詢的結果，並以表格形式顯示在 Node-RED Dashboard 介面。 |
IV. 資料庫與 Broker 配置
| 節點 ID | 類型 | 說明 |
sqlitedb | SQLite 數據庫配置： 定義數據庫檔案名稱為 SQLite.db (路徑依 Node-RED 實例而定)。 | |
mqtt-broker | MQTT Broker 配置： 服務器設置為 broker.mqttgo.io，端口為 1883。注意：您的流程已將 Broker 從 CloudAMQP 更換為 broker.mqttgo.io。 |
[{"id":"d0a4589ea33075d7","type":"tab","label":"MQTT 與 SQLite 數據管理 (完整版)","disabled":false,"info":"Node-RED 流程：\n1. 產生亂數並發布到 mqttgi MQTT (alex9ufo/sqlite/number)。\n2. 訂閱相同主題，將數據儲存到 SQLite 資料庫 (sqlite.db)。\n3. 提供 CRUD (建立/插入/更新/刪除/查詢/顯示) 功能。\n\n**使用步驟:**\n1. 點擊 [CREATE] 建立資料表。\n2. 點擊 [每 30 秒發布一次] 開始數據採集。\n3. 點擊 [SELECT] 查看資料表結果 (於 /ui 顯示)。"},{"id":"a292e8d7c427a029","type":"sqlite","z":"d0a4589ea33075d7","mydb":"4220b302c08287c8","sqlquery":"msg.topic","sql":"","name":"SQLite 資料庫","x":670,"y":480,"wires":[["d9a2246e5698a97f","a365427e795d95b4"]]},{"id":"685e28a473fca5d4","type":"inject","z":"d0a4589ea33075d7","name":"每 30 秒發布一次","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"30","crontab":"","once":true,"onceDelay":"0.1","topic":"","payload":"","payloadType":"date","x":120,"y":80,"wires":[["a83526f86443ff6d"]]},{"id":"a83526f86443ff6d","type":"function","z":"d0a4589ea33075d7","name":"產生亂數 (0-100)","func":"// 產生一個 0 到 100 之間的整數亂數\nmsg.payload = Math.floor(Math.random() * 101);\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":370,"y":80,"wires":[["40c5ca37a808730c","ca9df93787adc9a9"]]},{"id":"40c5ca37a808730c","type":"mqtt out","z":"d0a4589ea33075d7","name":"發行亂數到 alex9ufo/sqlite/number","topic":"alex9ufo/sqlite/number","qos":"1","retain":"false","respTopic":"","contentType":"","userProps":"","correl":"","expiry":"","broker":"192c2b20bef1e71a","x":670,"y":80,"wires":[]},{"id":"c0560f95e8289a3a","type":"mqtt in","z":"d0a4589ea33075d7","name":"訂閱 alex9ufo/sqlite/number","topic":"alex9ufo/sqlite/number","qos":"1","datatype":"utf8","broker":"192c2b20bef1e71a","nl":false,"rap":false,"inputs":0,"x":140,"y":200,"wires":[["b190ebcdd683a427","d9882d4f2790a8c9"]]},{"id":"b190ebcdd683a427","type":"function","z":"d0a4589ea33075d7","name":"格式化為 INSERT 語句","func":"// 獲取當前的日期和時間\nvar now = new Date();\n// SQLite 常用格式\nvar date = now.toISOString().slice(0, 10); // YYYY-MM-DD\nvar time = now.toTimeString().slice(0, 8); // HH:MM:SS\n\n// 確保 msg.payload 是數字\nvar number = parseFloat(msg.payload);\n\n// 構建 SQL INSERT 語句 (使用 ? 佔位符)\nmsg.topic = \"INSERT INTO data_log (date, time, number) VALUES ($date, $time, $number)\";\n\n// 參數陣列，將取代 msg.topic 中的 ?\nmsg.payload = [date, time, number]; \n\nreturn msg;\n","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":400,"y":200,"wires":[["5b5082c2dfbe01cc","a292e8d7c427a029"]]},{"id":"5b5082c2dfbe01cc","type":"debug","z":"d0a4589ea33075d7","name":"插入 DEBUG","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":680,"y":200,"wires":[]},{"id":"d585e25d1a726f9b","type":"inject","z":"d0a4589ea33075d7","name":"CREATE (建立資料表)","props":[{"p":"topic","vt":"str"},{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"CREATE TABLE IF NOT EXISTS data_log (id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, time TEXT, number REAL)","payload":"","payloadType":"str","x":140,"y":340,"wires":[["117d692c46a1566c"]]},{"id":"d24aef34cd50846d","type":"inject","z":"d0a4589ea33075d7","name":"SELECT (顯示所有資料)","props":[{"p":"topic","vt":"str"},{"p":"payload"}],"repeat":"3","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT * FROM data_log ORDER BY id DESC LIMIT 20","payload":"","payloadType":"str","x":150,"y":420,"wires":[["117d692c46a1566c"]]},{"id":"b46d7681a74bf536","type":"ui_text_input","z":"d0a4589ea33075d7","name":"","label":"要刪除的 ID:","tooltip":"","group":"252f82c4.b88234","order":1,"width":3,"height":1,"passthru":true,"mode":"number","delay":300,"topic":"delete_id","sendOnBlur":true,"className":"","topicType":"str","x":90,"y":620,"wires":[["0463ccea6baa6301","eb60591aa61ceab8"]]},{"id":"b16bafdc9232deb5","type":"ui_button","z":"d0a4589ea33075d7","name":"DELETE","group":"252f82c4.b88234","order":2,"width":2,"height":1,"passthru":false,"label":"DELETE","tooltip":"","color":"","bgcolor":"red","className":"","icon":"","payload":"true","payloadType":"bool","topic":"","topicType":"msg","x":80,"y":680,"wires":[["8d413f255542edc1"]]},{"id":"0463ccea6baa6301","type":"change","z":"d0a4589ea33075d7","name":"儲存 ID","rules":[{"t":"set","p":"delete_id","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":260,"y":620,"wires":[[]]},{"id":"8d413f255542edc1","type":"function","z":"d0a4589ea33075d7","name":"構建 DELETE 語句","func":"// 檢查是否有有效的 ID\nvar id_to_delete = flow.get('deleteId');\n\nif (id_to_delete && typeof id_to_delete === 'number' && id_to_delete > 0) {\n // 構建 DELETE 語句\n msg.topic = \"DELETE FROM data_log WHERE id = \" + id_to_delete;\n\n // 清空 msg.payload\n msg.payload = null;\n return msg;\n} else {\n node.warn(\"DELETE 失敗：請在上方欄位輸入有效的 ID 號碼。\");\n return null;\n}","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":390,"y":680,"wires":[["a292e8d7c427a029","d9a2246e5698a97f"]]},{"id":"b9d507df25e0baad","type":"inject","z":"d0a4589ea33075d7","name":"DROP (刪除資料表)","props":[{"p":"topic","v":"DROP TABLE data_log","vt":"str"},{"p":"payload","v":"","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"DROP TABLE data_log","payload":"","payloadType":"str","x":130,"y":520,"wires":[["a292e8d7c427a029"]]},{"id":"d9a2246e5698a97f","type":"debug","z":"d0a4589ea33075d7","name":"SQL 結果 DEBUG","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":890,"y":680,"wires":[]},{"id":"a365427e795d95b4","type":"ui_table","z":"d0a4589ea33075d7","group":"33ed6b40.675684","name":"資料庫內容 (ui-table)","order":1,"width":"12","height":"8","columns":[{"field":"id","title":"ID","width":"10%","align":"left","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"date","title":"Date","width":"20%","align":"left","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"time","title":"Time","width":"20%","align":"left","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"number","title":"Number","width":"50%","align":"left","formatter":"html","formatterParams":{"target":"_blank"}}],"outputs":0,"x":880,"y":480,"wires":[],"info":"自動顯示 SELECT * 查詢結果"},{"id":"ca9df93787adc9a9","type":"debug","z":"d0a4589ea33075d7","name":"debug 362","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":630,"y":140,"wires":[]},{"id":"d9882d4f2790a8c9","type":"debug","z":"d0a4589ea33075d7","name":"debug 363","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":350,"y":140,"wires":[]},{"id":"0e43c3e64fd3383a","type":"ui_button","z":"d0a4589ea33075d7","name":"","group":"252f82c4.b88234","order":2,"width":0,"height":0,"passthru":false,"label":"CREATE (建立資料表)","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":120,"y":300,"wires":[["745a686a01142c2f"]]},{"id":"9c9c268a271baf02","type":"link in","z":"d0a4589ea33075d7","name":"link in 72","links":["117d692c46a1566c"],"x":575,"y":400,"wires":[["a292e8d7c427a029"]]},{"id":"117d692c46a1566c","type":"link out","z":"d0a4589ea33075d7","name":"link out 78","mode":"link","links":["9c9c268a271baf02"],"x":515,"y":380,"wires":[]},{"id":"745a686a01142c2f","type":"function","z":"d0a4589ea33075d7","name":"CREATE (建立資料表)","func":"msg.topic = 'CREATE TABLE IF NOT EXISTS data_log (id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, time TEXT, number REAL)';\nreturn msg;\n\n//'CREATE TABLE IF NOT EXISTS data_log (\n// id INTEGER PRIMARY KEY AUTOINCREMENT, \n// date TEXT, \n// time TEXT, \n// number REAL)';","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":360,"y":300,"wires":[["117d692c46a1566c"]]},{"id":"2f895391523a81de","type":"ui_button","z":"d0a4589ea33075d7","name":"","group":"252f82c4.b88234","order":3,"width":0,"height":0,"passthru":false,"label":"SELECT (顯示所有資料)","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":130,"y":380,"wires":[["1315728c0590f659"]]},{"id":"1315728c0590f659","type":"function","z":"d0a4589ea33075d7","name":"SELECT (顯示所有資料)","func":"msg.topic = 'SELECT * FROM data_log ORDER BY id DESC LIMIT 20 ';\nreturn msg;\n\n//'CREATE TABLE IF NOT EXISTS data_log (\n// id INTEGER PRIMARY KEY AUTOINCREMENT, \n// date TEXT, \n// time TEXT, \n// number REAL)';","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":370,"y":380,"wires":[["117d692c46a1566c"]]},{"id":"699181681614f749","type":"ui_button","z":"d0a4589ea33075d7","name":"","group":"252f82c4.b88234","order":3,"width":0,"height":0,"passthru":false,"label":"DROP (刪除資料表)","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":110,"y":480,"wires":[["fd02c27fbe7e83be"]]},{"id":"fd02c27fbe7e83be","type":"function","z":"d0a4589ea33075d7","name":"DROP (刪除資料表)","func":"msg.topic = 'DROP TABLE data_log';\nreturn msg;\n\n//'CREATE TABLE IF NOT EXISTS data_log (\n// id INTEGER PRIMARY KEY AUTOINCREMENT, \n// date TEXT, \n// time TEXT, \n// number REAL)';","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":350,"y":480,"wires":[["a292e8d7c427a029"]]},{"id":"3998ebca051b7175","type":"ui_button","z":"d0a4589ea33075d7","name":"","group":"252f82c4.b88234","order":2,"width":0,"height":0,"passthru":false,"label":"INSERT (手動新增一筆)","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":130,"y":40,"wires":[["a83526f86443ff6d"]]},{"id":"eb60591aa61ceab8","type":"function","z":"d0a4589ea33075d7","name":"儲存 ID","func":"flow.set('deleteId', msg.payload);\nreturn msg;","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":260,"y":580,"wires":[[]]},{"id":"4220b302c08287c8","type":"sqlitedb","db":"SQLite.db","mode":"RWC"},{"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":"252f82c4.b88234","type":"ui_group","name":"CRUD 操作","tab":"771e843c.96131c","order":2,"disp":true,"width":"6","collapse":false,"className":""},{"id":"33ed6b40.675684","type":"ui_group","name":"SQLite 資料顯示","tab":"771e843c.96131c","order":1,"disp":true,"width":"12","collapse":false,"className":""},{"id":"771e843c.96131c","type":"ui_tab","name":"數據監控","icon":"dashboard","disabled":false,"hidden":false}]
沒有留言:
張貼留言