2023年 RFID 作業2 之 作業2-0 (Node-Red & SQLite )

 2023年 RFID 作業2 之 作業2-0 

 (Node-Red 與 SQLite)

1) A Node-RED-Dashboard UI widget node that displays a table of data.

需安裝 node-red-node-ui-table

2)  資料庫位置 C:\Users\User\.node-red\EX2_0.db

若是不像同的目錄 或 路徑 請修改成自己的目錄 或 路徑

每一個 SQlite 都要修改成一樣的 資料庫位置

名稱	msg.topic 內容
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
資料庫位置 C:\Users\User\.node-red\EX2_0.db

 

名稱	function 內容
INSERT	var Today = new Date(); var yyyy = Today.getFullYear(); //年 var MM = Today.getMonth()+1;    //月 var dd = Today.getDate();       //日 var h = Today.getHours();       //時 var m = Today.getMinutes();     //分 var s = Today.getSeconds();     //秒 if(MM<10) {    MM = '0'+MM; }   if(dd<10) {    dd = '0'+dd; }   if(h<10) {    h = '0'+h; }   if(m<10) {   m = '0' + m; }   if(s<10) {   s = '0' + s; } var var_date = yyyy+'/'+MM+'/'+dd; var var_time = h+':'+m+':'+s;   var myLED = msg.payload;     msg.topic = "INSERT INTO LEDSTATUS ( STATUS , Date , Time ) VALUES ($myLED,  $var_date ,  $var_time ) " ; msg.payload = [myLED, var_date , var_time ] return msg;     //INSERT INTO LEDSTATUS ( //id INTEGER, //STATUS TEXT, //Date DATE, //Time TIME, //PRIMARY KEY (id) //);
CREATE　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 = "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;
DELETE ALL DATA	//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;
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;

 Node-Red程式 

[{"id":"504b34dbb70192be","type":"inject","z":"21f10ca2c6adbdbb","name":"CREATE建立","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id));","payload":"","payloadType":"date","x":130,"y":80,"wires":[["9c15336e7e8ae007"]]},{"id":"4ed4c59d2d08a490","type":"inject","z":"21f10ca2c6adbdbb","name":"SELECT檢視","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT * FROM LEDSTATUS","payload":"","payloadType":"date","x":130,"y":160,"wires":[["9c15336e7e8ae007"]]},{"id":"271a58f256f18852","type":"inject","z":"21f10ca2c6adbdbb","name":"INSERT新增","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"INSERT INTO LEDSTATUS (STATUS ,  Date , Time )values(\"on\", \"11/01\" , \"21:05\") ","payload":"","payloadType":"date","x":130,"y":120,"wires":[["9c15336e7e8ae007"]]},{"id":"518871d8ede6068e","type":"inject","z":"21f10ca2c6adbdbb","name":"DELETE刪除所有資料","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"DELETE from LEDSTATUS","payload":"","payloadType":"date","x":160,"y":200,"wires":[["9c15336e7e8ae007"]]},{"id":"954df0d3b8c71e39","type":"inject","z":"21f10ca2c6adbdbb","name":"DROP TABLE刪除資料庫","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"DROP TABLE LEDSTATUS","payload":"","payloadType":"date","x":170,"y":240,"wires":[["9c15336e7e8ae007"]]},{"id":"9c15336e7e8ae007","type":"sqlite","z":"21f10ca2c6adbdbb","mydb":"f5c97c74cc496505","sqlquery":"msg.topic","sql":"","name":"LED_STATUS","x":400,"y":160,"wires":[["cd0ff24cc0ccfb40"]]},{"id":"cd0ff24cc0ccfb40","type":"debug","z":"21f10ca2c6adbdbb","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":590,"y":160,"wires":[]},{"id":"8613a1879fd4c2f5","type":"comment","z":"21f10ca2c6adbdbb","name":"資料表 :TABLE LEDSTATUS","info":"CREATE TABLE LEDSTATUS (\nid INTEGER,\nSTATUS TEXT,\nDate DATE,\nTime TIME,\nPRIMARY KEY (id)\n);","x":160,"y":40,"wires":[]},{"id":"7c456b93caee4a57","type":"sqlite","z":"21f10ca2c6adbdbb","mydb":"f5c97c74cc496505","sqlquery":"msg.topic","sql":"","name":"LED_STATUS","x":560,"y":540,"wires":[["acc67961e184f157"]]},{"id":"60e293440680470e","type":"function","z":"21f10ca2c6adbdbb","name":"CREATE　DATABASE","func":"//CREATE TABLE LEDSTATUS (\n//id INTEGER,\n//STATUS TEXT,\n//Date DATE,\n//Time TIME,\n//PRIMARY KEY (id)\n//);\n//CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id));\nmsg.topic = \"CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id))\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":320,"y":540,"wires":[["7c456b93caee4a57"]]},{"id":"57e3fca85ae1f2ad","type":"ui_button","z":"21f10ca2c6adbdbb","name":"","group":"41fea68a29ee6dab","order":3,"width":2,"height":1,"passthru":false,"label":"建立資料庫","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"建立資料庫","payloadType":"str","topic":"topic","topicType":"msg","x":110,"y":540,"wires":[["60e293440680470e","e5657b7e48feff85"]]},{"id":"e5657b7e48feff85","type":"ui_audio","z":"21f10ca2c6adbdbb","name":"","group":"11d8514.a44dcaf","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":265,"y":500,"wires":[],"l":false},{"id":"3f2f186e77216794","type":"ui_button","z":"21f10ca2c6adbdbb","name":"","group":"a46cce9233c736c8","order":1,"width":0,"height":0,"passthru":false,"label":"ON","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"on","payloadType":"str","topic":"topic","topicType":"msg","x":90,"y":320,"wires":[["6c299066a671deca","19f9959749499d3d"]]},{"id":"e183db2682058a13","type":"ui_button","z":"21f10ca2c6adbdbb","name":"","group":"a46cce9233c736c8","order":2,"width":0,"height":0,"passthru":false,"label":"OFF","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"off","payloadType":"str","topic":"topic","topicType":"msg","x":90,"y":360,"wires":[["6c299066a671deca","19f9959749499d3d"]]},{"id":"2f7c026547f28568","type":"ui_button","z":"21f10ca2c6adbdbb","name":"","group":"a46cce9233c736c8","order":3,"width":0,"height":0,"passthru":false,"label":"TOGGLE","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"toggle","payloadType":"str","topic":"topic","topicType":"msg","x":100,"y":400,"wires":[["6c299066a671deca","19f9959749499d3d"]]},{"id":"49c226737c468e22","type":"ui_button","z":"21f10ca2c6adbdbb","name":"","group":"a46cce9233c736c8","order":4,"width":0,"height":0,"passthru":false,"label":"TIMER","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"timer","payloadType":"str","topic":"topic","topicType":"msg","x":100,"y":440,"wires":[["6c299066a671deca","19f9959749499d3d"]]},{"id":"492f8e569c839cd2","type":"ui_button","z":"21f10ca2c6adbdbb","name":"","group":"a46cce9233c736c8","order":5,"width":0,"height":0,"passthru":false,"label":"FLASH","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"flash","payloadType":"str","topic":"topic","topicType":"msg","x":100,"y":480,"wires":[["19f9959749499d3d","6c299066a671deca"]]},{"id":"19f9959749499d3d","type":"function","z":"21f10ca2c6adbdbb","name":"INSERT","func":"var Today = new Date();\nvar yyyy = Today.getFullYear(); //年\nvar MM = Today.getMonth()+1;    //月\nvar dd = Today.getDate();       //日\nvar h = Today.getHours();       //時\nvar m = Today.getMinutes();     //分\nvar s = Today.getSeconds();     //秒\nif(MM<10)\n{\n   MM = '0'+MM;\n}\n\nif(dd<10)\n{\n   dd = '0'+dd;\n}\n\nif(h<10)\n{\n   h = '0'+h;\n}\n\nif(m<10)\n{\n  m = '0' + m;\n}\n\nif(s<10)\n{\n  s = '0' + s;\n}\nvar var_date = yyyy+'/'+MM+'/'+dd;\nvar var_time = h+':'+m+':'+s;\n\nvar myLED = msg.payload;\n\n\nmsg.topic = \"INSERT INTO LEDSTATUS ( STATUS , Date , Time ) VALUES ($myLED,  $var_date ,  $var_time ) \" ;\nmsg.payload = [myLED, var_date , var_time ]\nreturn msg;\n\n\n//INSERT INTO LEDSTATUS (\n//id INTEGER,\n//STATUS TEXT,\n//Date DATE,\n//Time TIME,\n//PRIMARY KEY (id)\n//);\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":280,"y":420,"wires":[["09baac1f5b6444a9"]]},{"id":"6c299066a671deca","type":"ui_audio","z":"21f10ca2c6adbdbb","name":"","group":"11d8514.a44dcaf","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":265,"y":320,"wires":[],"l":false},{"id":"09baac1f5b6444a9","type":"sqlite","z":"21f10ca2c6adbdbb","mydb":"f5c97c74cc496505","sqlquery":"msg.topic","sql":"","name":"LED_STATUS","x":460,"y":420,"wires":[["d60116fac2440a07","25d7e745541ee3ab"]]},{"id":"acc67961e184f157","type":"debug","z":"21f10ca2c6adbdbb","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":730,"y":540,"wires":[]},{"id":"d60116fac2440a07","type":"debug","z":"21f10ca2c6adbdbb","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":730,"y":420,"wires":[]},{"id":"da50366bd1b0248a","type":"ui_button","z":"21f10ca2c6adbdbb","name":"","group":"41fea68a29ee6dab","order":4,"width":6,"height":2,"passthru":false,"label":"檢視資料庫資料","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"檢視資料","payloadType":"str","topic":"topic","topicType":"msg","x":120,"y":620,"wires":[["25d7e745541ee3ab","d93f94fe15c20834"]]},{"id":"25d7e745541ee3ab","type":"function","z":"21f10ca2c6adbdbb","name":"檢視資料","func":"//INSERT INTO LEDSTATUS (\n//id INTEGER,\n//STATUS TEXT,\n//Date DATE,\n//Time TIME,\n//PRIMARY KEY (id)\n//);\n\n//SELECT * FROM LEDSTATUS ORDER BY  id DESC LIMIT 50;\n\nmsg.topic = \"SELECT * FROM LEDSTATUS ORDER BY id DESC LIMIT 50\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":400,"y":620,"wires":[["ea2067681bfd708e"]]},{"id":"6e1902f5d27a9a41","type":"ui_table","z":"21f10ca2c6adbdbb","group":"a4f9ca546387a8e9","name":"","order":1,"width":12,"height":10,"columns":[],"outputs":0,"cts":false,"x":730,"y":620,"wires":[]},{"id":"ea2067681bfd708e","type":"sqlite","z":"21f10ca2c6adbdbb","mydb":"f5c97c74cc496505","sqlquery":"msg.topic","sql":"","name":"LED_STATUS","x":580,"y":620,"wires":[["6e1902f5d27a9a41"]]},{"id":"e08d64cfcd2d908b","type":"ui_button","z":"21f10ca2c6adbdbb","name":"","group":"41fea68a29ee6dab","order":1,"width":2,"height":1,"passthru":false,"label":"刪除資料庫 ","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"刪除資料庫 ","payloadType":"str","topic":"topic","topicType":"msg","x":110,"y":860,"wires":[["141fa0283370415f","decba2add2cef346"]]},{"id":"46e9c55c7eb663ff","type":"function","z":"21f10ca2c6adbdbb","name":"DROP　DATABASE","func":"//CREATE TABLE LEDSTATUS (\n//id INTEGER,\n//STATUS TEXT,\n//Date DATE,\n//Time TIME,\n//PRIMARY KEY (id)\n//);\n//CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id));\nmsg.topic = \"DROP TABLE LEDSTATUS\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":680,"y":860,"wires":[["9c0012828707dc87"]]},{"id":"9c0012828707dc87","type":"sqlite","z":"21f10ca2c6adbdbb","mydb":"f5c97c74cc496505","sqlquery":"msg.topic","sql":"","name":"LED_STATUS","x":880,"y":820,"wires":[["a92ba5c06e2c6da8"]]},{"id":"681585b745c04a98","type":"ui_button","z":"21f10ca2c6adbdbb","name":"","group":"41fea68a29ee6dab","order":2,"width":2,"height":1,"passthru":false,"label":"刪除所有資料 ","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"刪除所有資料 ","payloadType":"str","topic":"topic","topicType":"msg","x":120,"y":760,"wires":[["decba2add2cef346","1b23c7d4d9b36bae"]]},{"id":"7ebbafd85b9b7836","type":"function","z":"21f10ca2c6adbdbb","name":"DELETE ALL DATA","func":"//CREATE TABLE LEDSTATUS (\n//id INTEGER,\n//STATUS TEXT,\n//Date DATE,\n//Time TIME,\n//PRIMARY KEY (id)\n//);\n//CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id));\nmsg.topic = \"DELETE from LEDSTATUS\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":690,"y":760,"wires":[["9c0012828707dc87"]]},{"id":"141fa0283370415f","type":"ui_toast","z":"21f10ca2c6adbdbb","position":"prompt","displayTime":"3","highlight":"","sendall":true,"outputs":1,"ok":"OK","cancel":"Cancel","raw":true,"className":"","topic":"","name":"","x":310,"y":860,"wires":[["34f66ad14e796c6f"]]},{"id":"34f66ad14e796c6f","type":"function","z":"21f10ca2c6adbdbb","name":"OK or Cancel","func":"var topic=msg.payload;\nif (topic==\"\"){\n    return [msg,null];\n    \n}\nif (topic==\"Cancel\"){\n    return [null,msg];\n    \n}\nreturn msg;","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":480,"y":860,"wires":[["46e9c55c7eb663ff"],[]]},{"id":"decba2add2cef346","type":"ui_audio","z":"21f10ca2c6adbdbb","name":"","group":"11d8514.a44dcaf","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":245,"y":820,"wires":[],"l":false},{"id":"1b23c7d4d9b36bae","type":"ui_toast","z":"21f10ca2c6adbdbb","position":"prompt","displayTime":"3","highlight":"","sendall":true,"outputs":1,"ok":"OK","cancel":"Cancel","raw":true,"className":"","topic":"","name":"","x":310,"y":760,"wires":[["22441762e5e605fe"]]},{"id":"22441762e5e605fe","type":"function","z":"21f10ca2c6adbdbb","name":"OK or Cancel","func":"var topic=msg.payload;\nif (topic==\"\"){\n    return [msg,null];\n    \n}\nif (topic==\"Cancel\"){\n    return [null,msg];\n    \n}\nreturn msg;","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":480,"y":760,"wires":[["7ebbafd85b9b7836"],[]]},{"id":"d93f94fe15c20834","type":"ui_audio","z":"21f10ca2c6adbdbb","name":"","group":"11d8514.a44dcaf","voice":"Microsoft Hanhan - Chinese (Traditional, Taiwan)","always":true,"x":265,"y":580,"wires":[],"l":false},{"id":"a92ba5c06e2c6da8","type":"link out","z":"21f10ca2c6adbdbb","name":"link out 39","mode":"link","links":["4d4011fd2f43a427"],"x":935,"y":700,"wires":[]},{"id":"4d4011fd2f43a427","type":"link in","z":"21f10ca2c6adbdbb","name":"link in 36","links":["a92ba5c06e2c6da8"],"x":255,"y":660,"wires":[["25d7e745541ee3ab"]]},{"id":"46f24d83042e75d5","type":"comment","z":"21f10ca2c6adbdbb","name":"資料庫位置 C:\\Users\\User\\.node-red\\EX2_0.db","info":"","x":460,"y":120,"wires":[]},{"id":"d1972469b9de97e6","type":"comment","z":"21f10ca2c6adbdbb","name":"CREATE建立","info":"CREATE TABLE LEDSTATUS (id INTEGER,STATUS TEXT,Date DATE,Time TIME,PRIMARY KEY (id));","x":390,"y":220,"wires":[]},{"id":"8627e647c0d6e321","type":"comment","z":"21f10ca2c6adbdbb","name":"INSERT新增","info":"INSERT INTO LEDSTATUS (STATUS ,  Date , Time )values(\"on\", \"11/01\" , \"21:05\") ","x":550,"y":200,"wires":[]},{"id":"7965eed9c32ad3ec","type":"comment","z":"21f10ca2c6adbdbb","name":"SELECT檢視","info":"SELECT * FROM LEDSTATUS","x":390,"y":260,"wires":[]},{"id":"4dc71124d34953c2","type":"comment","z":"21f10ca2c6adbdbb","name":"DELETE刪除所有資料","info":"DELETE from LEDSTATUS","x":580,"y":240,"wires":[]},{"id":"302ff06ab41aa632","type":"comment","z":"21f10ca2c6adbdbb","name":"DROP TABLE 刪除資料庫","info":"DROP TABLE LEDSTATUS","x":590,"y":280,"wires":[]},{"id":"f5c97c74cc496505","type":"sqlitedb","db":"C:\\Users\\User\\.node-red\\EX2_0.db","mode":"RWC"},{"id":"41fea68a29ee6dab","type":"ui_group","name":"資料庫命令區","tab":"4f9e6f64a188a71a","order":3,"disp":true,"width":"6","collapse":false,"className":""},{"id":"11d8514.a44dcaf","type":"ui_group","name":"新增","tab":"8f1ada5fa4df30e2","order":2,"disp":true,"width":"4","collapse":false,"className":""},{"id":"a46cce9233c736c8","type":"ui_group","name":"控制命令區","tab":"4f9e6f64a188a71a","order":1,"disp":true,"width":"6","collapse":false,"className":""},{"id":"a4f9ca546387a8e9","type":"ui_group","name":"顯示區","tab":"4f9e6f64a188a71a","order":2,"disp":true,"width":"12","collapse":false,"className":""},{"id":"4f9e6f64a188a71a","type":"ui_tab","name":"作業2_0","icon":"dashboard","disabled":false,"hidden":false},{"id":"8f1ada5fa4df30e2","type":"ui_tab","name":"控制","icon":"dashboard","disabled":false,"hidden":false}]