SQLite with Node-RED

 SQLite with Node-RED

SQL 指令（用於操作資料庫）

• CREATE TABLE： 

  建立一個新資料表。

  • 範例：CREATE TABLE member (id char(20) not null, name varchar(255), number int(10));
• INSERT INTO： 

  向資料表中新增資料。

  • 範例：INSERT INTO member (id,name,number) values('123', 'abc', '555');
• UPDATE： 

  修改資料表中現有的資料。

  • 範例：UPDATE member SET id='048' WHERE name='abc';
• DELETE FROM： 

  從資料表中刪除資料。

  • 範例：DELETE FROM member WHERE name='abc';
• SELECT： 

  查詢資料表中的資料。

  • 範例：SELECT columns FROM table_name [WHERE expression];

SQLite 的 DROP 指令是 DROP TABLE，用於永久刪除一個資料表及其所有相關的資料、索引、觸發器和權限。 使用此指令時務必謹慎，因為一旦執行，所有資料都將遺失。 

DROP TABLE table_name;

• table_name：要刪除的資料表的名稱。 
• DROP TABLE：指令本身。
• ;：表示指令結束，這是SQLite 指令的標準結束符號。 

Node-RED 流程 (Flow) 其目的是提供一個完整的 SQLite 資料庫操作示範，並透過 Node-RED Dashboard UI 讓使用者能夠執行這些操作並看到結果。

這個流程主要分為三個部分：資料庫配置、UI 控制面板，以及數據操作邏輯。

---

流程概要與功能說明

流程分頁 (tab)
標籤 (Label)	SQLite 功能示範 (test.db)
功能	包含所有用於對 SQLite 資料庫 test.db 進行 CREATE (建立), INSERT (插入), SELECT (查詢), DELETE (刪除), 和 DROP (銷毀) 的節點。

核心節點詳解

1. 資料庫連接與核心節點

節點類型		名稱/配置	說明
SQLite 配置節點		test.db	這是資料庫本身的配置節點。它指定了資料庫檔案的名稱為 test.db。這個檔案會在 Node-RED 的使用者目錄 (.node-red) 中自動創建。
SQLite 節點 (操作)		SQLite 資料庫 (test.db)	這是實際執行 SQL 語句的節點。它配置為從接收到的訊息 msg.topic 中獲取要執行的 SQL 查詢語句。
Debug 節點		SQLite 執行結果	用於顯示 SQLite 節點執行後的結果。不論是執行成功 (如 INSERT/DELETE) 還是查詢結果 (SELECT)，都會在 Debug 側邊欄顯示。
UI Table 節點		資料表格 (ui-table)	這是 Dashboard UI 上的表格組件。它接收 SELECT 查詢的結果（通常是陣列格式的 msg.payload），並將其格式化顯示在瀏覽器上。

2. UI 控制與 CRUD 操作邏輯

所有的 UI 節點都放在名為 "SQLite 資料庫功能示範" 的組 (Group) 中，該組屬於名為 "資料庫示範面板" 的 UI Tab。

節點類型	功能/名稱	邏輯說明
UI Button  Change 節點	CREATE (建立)	Change 節點將 msg.topic 設定為 CREATE TABLE IF NOT EXISTS my_data (...) 的 SQL 語句，然後傳給 SQLite 節點執行，用來建立名為 my_data 的資料表。
UI Button  Function 節點	INSERT (插入)	Function 節點 (產生 INSERT SQL) 會動態產生包含當前時間和 隨機數字 (0-100) 的 INSERT INTO my_data (...) 語句，並將其賦值給 msg.topic 執行。
UI Button Change 節點	SELECT (查詢)	Change 節點將 msg.topic 設定為 SELECT id, date, time, number FROM my_data，查詢所有資料並輸出到 UI Table 顯示。
UI Button Change 節點	DELETE (< 50)	執行批量刪除：Change 節點將 msg.topic 設定為 DELETE FROM my_data WHERE number < 50。特別注意：此節點會輸出兩條連線，一條執行 DELETE，另一條執行 SELECT (立即刷新表格)。
UI Button  Change 節點	DROP (銷毀)	執行資料表銷毀：Change 節點將 msg.topic 設定為 DROP TABLE IF EXISTS my_data。

3. 新增的「透過 ID 刪除」功能（圖片所示）

這部分實現了圖片中「刪除一筆 id=」的精確控制刪除功能。

節點類型	功能/名稱	邏輯說明
UI Text Input	刪除一筆 id=	用於接收使用者在 Dashboard 輸入的 ID 號碼 (msg.payload)，並將 msg.topic 設為 "deleteId" 傳遞給 Function 節點。
UI Button	DELETE (刪除一筆)	觸發刪除操作。它發送一個 payload: 1 的訊息到 Function 節點。
Function 節點	function  (DELETE ID SQL)	1. 接收 ID： 如果收到 topic == 'deleteId'，它會將 ID 儲存到 流程上下文 (Flow Context) 中 (flow.set('deleteID', msg.payload))。

Function 節點

function     (DELETE ID SQL)

2. 執行刪除： 如果收到 payload == 1 (來自按鈕)，它會從 Flow Context 中取出 ID，建立 DELETE FROM my_data WHERE id = [ID] 的 SQL 語句，並輸出。輸出後同樣連線到 SELECT 節點以刷新表格。

[{"id":"6175eaf79545705a","type":"tab","label":"SQLite 功能示範 (test.db)","disabled":false,"info":"示範 SQLite 資料庫的 CREATE, INSERT, SELECT, DELETE, DROP 功能。資料庫檔案：test.db"},{"id":"940528c3e3697601","type":"sqlite","z":"6175eaf79545705a","mydb":"799a9fda3d078940","sqlquery":"msg.topic","sql":"","name":"SQLite 資料庫 (test.db)","x":670,"y":140,"wires":[["6c89c25ba1b79a98","c79aea60f978db5e"]]},{"id":"6c89c25ba1b79a98","type":"debug","z":"6175eaf79545705a","name":"SQLite 執行結果","active":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":900,"y":140,"wires":[]},{"id":"b320e61d0a2d30a5","type":"ui_button","z":"6175eaf79545705a","name":"CREATE (建立資料表)","group":"216515e83bf6ead4","order":1,"width":3,"height":1,"passthru":false,"label":"CREATE","tooltip":"建立 my_data 資料表","color":"","bgcolor":"green","className":"","icon":"","payload":"","payloadType":"str","topic":"","topicType":"msg","x":140,"y":80,"wires":[["5943ddf87dc315ac"]]},{"id":"5943ddf87dc315ac","type":"change","z":"6175eaf79545705a","name":"SQL: CREATE (建立)","rules":[{"t":"set","p":"topic","pt":"msg","to":"CREATE TABLE IF NOT EXISTS my_data (id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, time TEXT, number INTEGER)","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":380,"y":80,"wires":[["940528c3e3697601"]]},{"id":"f8fdbe63af332cf9","type":"ui_button","z":"6175eaf79545705a","name":"INSERT (插入)","group":"216515e83bf6ead4","order":2,"width":3,"height":1,"passthru":false,"label":"INSERT  ","tooltip":"插入一筆隨機資料","color":"","bgcolor":"blue","className":"","icon":"","payload":"","payloadType":"str","topic":"","topicType":"msg","x":120,"y":140,"wires":[["3d023e070064238f"]]},{"id":"3d023e070064238f","type":"function","z":"6175eaf79545705a","name":"產生 INSERT SQL","func":"// 獲取目前時間\nconst now = new Date();\n// 格式化日期：YYYY-MM-DD\nconst date = now.getFullYear() + '-' + ('0' + (now.getMonth() + 1)).slice(-2) + '-' + ('0' + now.getDate()).slice(-2);\n// 格式化時間：HH:mm:ss\nconst time = ('0' + now.getHours()).slice(-2) + ':' + ('0' + now.getMinutes()).slice(-2) + ':' + ('0' + now.getSeconds()).slice(-2);\n\n// 產生 0 到 100 之間的亂數\nconst randomNumber = Math.floor(Math.random() * 101);\n\n// 建構 INSERT INTO SQL 語句\nmsg.topic = `INSERT INTO my_data (date, time, number) VALUES ('${date}', '${time}', ${randomNumber})`;\n\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":370,"y":140,"wires":[["940528c3e3697601"]]},{"id":"d61eed9277aa032d","type":"ui_button","z":"6175eaf79545705a","name":"SELECT (查詢)","group":"216515e83bf6ead4","order":3,"width":3,"height":1,"passthru":false,"label":"SELECT #1","tooltip":"查詢所有資料並顯示","color":"","bgcolor":"orange","className":"","icon":"","payload":"","payloadType":"str","topic":"","topicType":"msg","x":120,"y":200,"wires":[["0925d7f1e6bf7d57"]]},{"id":"0925d7f1e6bf7d57","type":"change","z":"6175eaf79545705a","name":"SQL: SELECT (查詢)","rules":[{"t":"set","p":"topic","pt":"msg","to":"SELECT id, date, time, number FROM my_data","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":380,"y":200,"wires":[["940528c3e3697601"]]},{"id":"c79aea60f978db5e","type":"ui_table","z":"6175eaf79545705a","group":"216515e83bf6ead4","name":"資料表格 (ui-table)","order":9,"width":12,"height":8,"columns":[{"field":"id","title":"ID","width":"10%","align":"left","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"date","title":"日期","width":"20%","align":"left","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"time","title":"時間","width":"20%","align":"left","formatter":"html","formatterParams":{"target":"_blank"}},{"field":"number","title":"亂數 (0-100)","width":"20%","align":"left","formatter":"html","formatterParams":{"target":"_blank"}}],"outputs":0,"cts":false,"x":910,"y":200,"wires":[]},{"id":"2650d6e9ad63a5c5","type":"ui_button","z":"6175eaf79545705a","name":"DELETE (刪除)","group":"216515e83bf6ead4","order":4,"width":3,"height":1,"passthru":false,"label":"DELETE","tooltip":"刪除 number < 50 的紀錄，並自動刷新表格","color":"","bgcolor":"red","className":"","icon":"","payload":"","payloadType":"str","topic":"","topicType":"msg","x":120,"y":300,"wires":[["14f91b0687ef609b"]]},{"id":"14f91b0687ef609b","type":"change","z":"6175eaf79545705a","name":"SQL: DELETE (刪除)","rules":[{"t":"set","p":"topic","pt":"msg","to":"DELETE FROM my_data WHERE number < 50","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":380,"y":300,"wires":[["940528c3e3697601","0925d7f1e6bf7d57"]]},{"id":"0c96e812e72f3f84","type":"ui_button","z":"6175eaf79545705a","name":"DROP (刪除資料表)","group":"216515e83bf6ead4","order":5,"width":3,"height":1,"passthru":false,"label":"DROP","tooltip":"刪除 my_data 資料表 (請謹慎操作)","color":"","bgcolor":"darkred","className":"","icon":"","payload":"","payloadType":"str","topic":"","topicType":"msg","x":130,"y":400,"wires":[["ebd49403e7375a09"]]},{"id":"ebd49403e7375a09","type":"change","z":"6175eaf79545705a","name":"SQL: DROP (銷毀)","rules":[{"t":"set","p":"topic","pt":"msg","to":"DROP TABLE IF EXISTS my_data","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":370,"y":400,"wires":[["940528c3e3697601"]]},{"id":"a0e788235c2d259a","type":"comment","z":"6175eaf79545705a","name":"","info":"DELETE FROM my_data WHERE number < 50","x":330,"y":340,"wires":[]},{"id":"51c5c320038f5c68","type":"comment","z":"6175eaf79545705a","name":"","info":"DROP TABLE IF EXISTS my_data","x":330,"y":440,"wires":[]},{"id":"bac6457e8cc2c3c5","type":"comment","z":"6175eaf79545705a","name":"","info":"CREATE TABLE IF NOT EXISTS my_data \n(\nid INTEGER PRIMARY KEY AUTOINCREMENT, \ndate TEXT, \ntime TEXT, \nnumber INTEGER\n)","x":370,"y":40,"wires":[]},{"id":"d005ea88d8cac469","type":"ui_button","z":"6175eaf79545705a","name":"DELETE (刪除一筆)","group":"216515e83bf6ead4","order":7,"width":3,"height":1,"passthru":false,"label":"DELETE by ID","tooltip":"執行單筆 ID 刪除，並刷新表格","color":"","bgcolor":"darkred","className":"","icon":"fa-eraser","payload":"1","payloadType":"num","topic":"","topicType":"str","x":140,"y":540,"wires":[["24cb44a4c6383834"]]},{"id":"9b934b2d46e35ffc","type":"function","z":"6175eaf79545705a","name":"function  (DELETE ID SQL)","func":"// 這個節點從 Dashboard 的輸入和按鈕接收訊息\n// 步驟一：儲存輸入的 ID\n// 如果 msg.topic 是 'deleteId' (來自輸入框)，則儲存 payload (ID)\nif (msg.topic === 'deleteId') {\n    flow.set('deleteID', msg.payload);\n    // 不繼續傳遞，因為還沒按刪除按鈕\n    return msg;\n}\n\nreturn msg; // 其他情況不執行操作","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":400,"y":480,"wires":[[]]},{"id":"333ccf32fcdfbf92","type":"ui_text_input","z":"6175eaf79545705a","name":"刪除一筆 id=","label":"刪除 ID:","tooltip":"","group":"216515e83bf6ead4","order":6,"width":3,"height":1,"passthru":true,"mode":"number","delay":0,"topic":"deleteId","sendOnBlur":true,"className":"","topicType":"str","x":110,"y":480,"wires":[["9b934b2d46e35ffc"]]},{"id":"24cb44a4c6383834","type":"function","z":"6175eaf79545705a","name":"function  (DELETE ID SQL)","func":"// 這個節點從 Dashboard 的輸入和按鈕接收訊息\n\n// 步驟二：執行刪除操作（來自按鈕）\n// 如果 msg.payload 是 1 (來自按鈕)，則執行刪除\nif (msg.payload === 1) {\n    const idToDelete = flow.get('deleteID');\n    \n    // 檢查是否有有效的 ID\n    if (idToDelete && !isNaN(parseInt(idToDelete))) {\n        // 創建 DELETE SQL 語句\n        msg.topic = `DELETE FROM my_data WHERE id = ${parseInt(idToDelete)}`;\n        msg.payload = {}; // 清空 payload，確保後續操作正常\n        return msg;\n    } else {\n        node.warn(\"請先在輸入框中輸入有效的 ID 號碼！\");\n        return null; // 無效 ID，不執行操作\n    }\n}\n\nreturn msg; // 其他情況不執行操作","outputs":1,"timeout":"","noerr":0,"initialize":"","finalize":"","libs":[],"x":400,"y":540,"wires":[["940528c3e3697601"]]},{"id":"799a9fda3d078940","type":"sqlitedb","db":"test.db"},{"id":"216515e83bf6ead4","type":"ui_group","name":"SQLite 資料庫功能示範","tab":"1871b13b611a78bb","order":1,"disp":true,"width":"12","collapse":false,"className":""},{"id":"1871b13b611a78bb","type":"ui_tab","name":"資料庫示範面板","icon":"dashboard","order":1,"disabled":false,"hidden":false},{"id":"a1924b424d10b9ed","type":"global-config","env":[],"modules":{"node-red-node-sqlite":"1.1.1","node-red-dashboard":"3.6.6","node-red-node-ui-table":"0.4.5"}}]