2023年11月24日 星期五

SQLite and Node-RED

 

SQLite and Node-RED







名稱

內容

Create Table

inject

msg.topic = CREATE TABLE RANDOMNUM( ID INTEGER PRIMARY KEY AUTOINCREMENT, TIMESTAMP TEXT , VALUE INT ,  BOOL INT  )

Create Table

button

msg.topic = "CREATE TABLE RANDOMNUM( ID INTEGER PRIMARY KEY AUTOINCREMENT, TIMESTAMP TEXT , VALUE INT ,  BOOL INT)";

return msg;

INSERT inject

&

INSERT function

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+' '+h+':'+m+':'+s;

 

var randomNum = Math.round(Math.random()*100);

var largeBool = (randomNum > 50)?1:0;

 

msg.topic = "INSERT INTO RANDOMNUM (TIMESTAMP , VALUE , BOOL ) VALUES ($var_date ,  $randomNum ,  $largeBool ) " ;

msg.payload = [var_date, randomNum , largeBool ]

return msg;

 

//CREATE TABLE RANDOMNUM(

//ID INTEGER PRIMARY KEY AUTOINCREMENT ,

//TIMESTAMP TEXT NOT NULL ,

//VALUE INT NOT NULL,

//BOOL INT NOT NULL

//)

 

View Data inject

msg.topic = SELECT * FROM RANDOMNUM ORDER BY ID DESC LIMIT 100;

SELECT *  button

msg.topic = "SELECT * FROM RANDOMNUM ORDER BY ID DESC LIMIT 100";

return msg;

DELETE inject

msg.topic = DELETE from RANDOMNUM

 

DELETE function

msg.topic = "DELETE from RANDOMNUM";

return msg;

DROP TABLE inject

msg.topic = DROP TABLE RANDOMNUM

 

DROP TABLE function

msg.topic = "DROP TABLE RANDOMNUM";

return msg;

 


[{"id":"fdc70b54b6b1a6ad","type":"sqlite","z":"e5871d216d80bff1","mydb":"a87c732dba25794c","sqlquery":"msg.topic","sql":"","name":"testDB","x":770,"y":220,"wires":[["c03f66e948805298"]]},{"id":"25a9609806d69b88","type":"inject","z":"e5871d216d80bff1","name":"Create Table","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"CREATE TABLE RANDOMNUM( ID INTEGER PRIMARY KEY AUTOINCREMENT, TIMESTAMP TEXT , VALUE INT ,  BOOL INT  )","payload":"","payloadType":"date","x":550,"y":240,"wires":[["fdc70b54b6b1a6ad"]]},{"id":"9aacd9f70c7b5080","type":"comment","z":"e5871d216d80bff1","name":"","info":"CREATE TABLE RANDOMNUM(\nID INTEGER PRIMARY KEY AUTOINCREMENT,\nTIMESTAMP TEXT ,\nVALUE INT , \nBOOL INT \n,PRIMARY KEY (id)\n)","x":90,"y":180,"wires":[]},{"id":"c03f66e948805298","type":"debug","z":"e5871d216d80bff1","name":"debug  ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":930,"y":220,"wires":[]},{"id":"d0cfc81736e68c9a","type":"function","z":"e5871d216d80bff1","name":"INSERT data into database","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+' '+h+':'+m+':'+s;\n\nvar randomNum = Math.round(Math.random()*100);\nvar largeBool = (randomNum > 50)?1:0;\n\nmsg.topic = \"INSERT INTO RANDOMNUM (TIMESTAMP , VALUE , BOOL ) VALUES ($var_date ,  $randomNum ,  $largeBool ) \" ;\nmsg.payload = [var_date, randomNum , largeBool ]\nreturn msg;\n\n//CREATE TABLE RANDOMNUM(\n//ID INT PRIMARY KEY ,\n//TIMESTAMP TEXT NOT NULL ,\n//VALUE INT NOT NULL, \n//BOOL INT NOT NULL\n//)","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":400,"y":360,"wires":[["fc701fe3114ba8a9"]]},{"id":"89f60a44a343e629","type":"inject","z":"e5871d216d80bff1","name":"INSERT","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":100,"y":360,"wires":[["d0cfc81736e68c9a"]]},{"id":"f150897647cf6b4c","type":"inject","z":"e5871d216d80bff1","name":"SELECT *","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT * FROM RANDOMNUM ORDER BY id DESC LIMIT 100;","payload":"","payloadType":"date","x":560,"y":200,"wires":[["fdc70b54b6b1a6ad"]]},{"id":"cc8462dbcfe365b9","type":"inject","z":"e5871d216d80bff1","name":"DELETE","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"DELETE from RANDOMNUM","payload":"","payloadType":"date","x":620,"y":80,"wires":[["fdc70b54b6b1a6ad"]]},{"id":"86478f76dcf68e1c","type":"inject","z":"e5871d216d80bff1","name":"DROP TABLE","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"DROP TABLE RANDOMNUM","payload":"","payloadType":"date","x":570,"y":160,"wires":[["fdc70b54b6b1a6ad"]]},{"id":"39288d1b0e9a36f4","type":"comment","z":"e5871d216d80bff1","name":"","info":"","x":90,"y":400,"wires":[]},{"id":"37580c3214ea7c6c","type":"ui_template","z":"e5871d216d80bff1","group":"3d571d5edb075d0c","name":"","order":1,"width":6,"height":8,"format":"<table style=\"width:100%\">\n  <tr>\n    <th>Index</th> \n    <th>Timestamp</th>\n    <th>Value</th> \n    <th>Bool</th>\n  </tr>\n  <tr ng-repeat=\"x in msg.payload | limitTo:20\">\n    <td>{{msg.payload[$index].ID}}</td>\n    <td>{{msg.payload[$index].TIMESTAMP}}</td>\n    <td>{{msg.payload[$index].VALUE}}</td> \n    <td>{{msg.payload[$index].BOOL}}</td>\n  </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","className":"","x":540,"y":480,"wires":[["05c2f8ddb9f90cf6"]]},{"id":"2cd3919d33042f98","type":"inject","z":"e5871d216d80bff1","name":"View Data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT * FROM RANDOMNUM ORDER BY ID DESC LIMIT 100;","payload":"","payloadType":"date","x":100,"y":500,"wires":[["72e262dfee532416"]]},{"id":"72e262dfee532416","type":"sqlite","z":"e5871d216d80bff1","mydb":"a87c732dba25794c","sqlquery":"msg.topic","sql":"","name":"testDB","x":350,"y":500,"wires":[["37580c3214ea7c6c","3ef3da019561f4db"]]},{"id":"05c2f8ddb9f90cf6","type":"debug","z":"e5871d216d80bff1","name":"debug  ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":670,"y":480,"wires":[]},{"id":"3ef3da019561f4db","type":"ui_table","z":"e5871d216d80bff1","group":"866f959e6470a02c","name":"","order":1,"width":"9","height":8,"columns":[{"field":"ID","title":"序號","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"TIMESTAMP","title":"日期時間","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"VALUE","title":"數值","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"BOOL","title":"布林","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}}],"outputs":0,"cts":false,"x":530,"y":520,"wires":[]},{"id":"52fb1b72dc427336","type":"ui_button","z":"e5871d216d80bff1","name":"","group":"ac7347ff53d34ce3","order":1,"width":0,"height":0,"passthru":false,"label":"DELETE ALL ","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"DELETE ALL  data","payloadType":"str","topic":"topic","topicType":"msg","x":110,"y":40,"wires":[["98ee6738ca53e25d"]]},{"id":"98ee6738ca53e25d","type":"ui_toast","z":"e5871d216d80bff1","position":"prompt","displayTime":"3","highlight":"","sendall":true,"outputs":1,"ok":"OK","cancel":"Cancel","raw":true,"className":"","topic":"","name":"","x":270,"y":40,"wires":[["11aa9c122e9a4341"]]},{"id":"11aa9c122e9a4341","type":"function","z":"e5871d216d80bff1","name":"ok ?","func":"var topic=msg.payload;\nif (topic==\"\"){\n    return [msg,null];\n    \n}\nif (topic==\"Cancel\"){\n    return [null,msg];\n    \n}\nreturn msg;\n","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":430,"y":40,"wires":[["0907c0c3ee331722"],[]]},{"id":"0907c0c3ee331722","type":"function","z":"e5871d216d80bff1","name":"DELETE","func":"msg.topic = \"DELETE from RANDOMNUM\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":680,"y":40,"wires":[["fdc70b54b6b1a6ad"]]},{"id":"fea43c8882f7c611","type":"ui_button","z":"e5871d216d80bff1","name":"","group":"ac7347ff53d34ce3","order":2,"width":0,"height":0,"passthru":false,"label":"DROP TABLE","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"DROP TABLE","payloadType":"str","topic":"topic","topicType":"msg","x":120,"y":120,"wires":[["280a24efdfb869ec"]]},{"id":"280a24efdfb869ec","type":"ui_toast","z":"e5871d216d80bff1","position":"prompt","displayTime":"3","highlight":"","sendall":true,"outputs":1,"ok":"OK","cancel":"Cancel","raw":true,"className":"","topic":"","name":"","x":290,"y":120,"wires":[["0c1789304c8cbdc4"]]},{"id":"0c1789304c8cbdc4","type":"function","z":"e5871d216d80bff1","name":"ok ?","func":"var topic=msg.payload;\nif (topic==\"\"){\n    return [msg,null];\n    \n}\nif (topic==\"Cancel\"){\n    return [null,msg];\n    \n}\nreturn msg;\n","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":430,"y":120,"wires":[["0ea24c2427871d8d"],[]]},{"id":"0ea24c2427871d8d","type":"function","z":"e5871d216d80bff1","name":"DROP TABLE ","func":"msg.topic = \"DROP TABLE RANDOMNUM\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":580,"y":120,"wires":[["fdc70b54b6b1a6ad"]]},{"id":"17ba4ecdebd82736","type":"ui_button","z":"e5871d216d80bff1","name":"","group":"ac7347ff53d34ce3","order":3,"width":0,"height":0,"passthru":false,"label":"SELECT *","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":90,"y":440,"wires":[["dee563ebbe341ad2"]]},{"id":"dee563ebbe341ad2","type":"function","z":"e5871d216d80bff1","name":"SELECT *","func":"msg.topic = \"SELECT * FROM RANDOMNUM ORDER BY ID DESC LIMIT 100\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":290,"y":440,"wires":[["72e262dfee532416"]]},{"id":"51d6e19fc98acb03","type":"ui_button","z":"e5871d216d80bff1","name":"","group":"ac7347ff53d34ce3","order":4,"width":0,"height":0,"passthru":false,"label":"Create Table","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":110,"y":220,"wires":[["7de36e59aaa57793"]]},{"id":"7de36e59aaa57793","type":"function","z":"e5871d216d80bff1","name":"CREATE TABLE","func":"\nmsg.topic = \"CREATE TABLE RANDOMNUM( ID INTEGER PRIMARY KEY AUTOINCREMENT, TIMESTAMP TEXT , VALUE INT ,  BOOL INT)\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":320,"y":220,"wires":[["fdc70b54b6b1a6ad"]]},{"id":"6b44741d7a4c8885","type":"ui_button","z":"e5871d216d80bff1","name":"","group":"ac7347ff53d34ce3","order":5,"width":0,"height":0,"passthru":false,"label":"INSERT","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":100,"y":300,"wires":[["d0cfc81736e68c9a"]]},{"id":"fc701fe3114ba8a9","type":"sqlite","z":"e5871d216d80bff1","mydb":"a87c732dba25794c","sqlquery":"msg.topic","sql":"","name":"testDB","x":650,"y":360,"wires":[["dee563ebbe341ad2"]]},{"id":"a87c732dba25794c","type":"sqlitedb","db":"C:\\Users\\User\\.node-red\\testDB.db","mode":"RWC"},{"id":"3d571d5edb075d0c","type":"ui_group","name":"View_Database","tab":"2f449046556f64a6","order":2,"disp":true,"width":6,"collapse":false,"className":""},{"id":"866f959e6470a02c","type":"ui_group","name":"Database Table","tab":"2f449046556f64a6","order":3,"disp":true,"width":"9","collapse":false,"className":""},{"id":"ac7347ff53d34ce3","type":"ui_group","name":"COMMAND","tab":"2f449046556f64a6","order":1,"disp":true,"width":"6","collapse":false,"className":""},{"id":"2f449046556f64a6","type":"ui_tab","name":"testDB","icon":"dashboard","order":92,"disabled":false,"hidden":false}]


於
標籤：

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)

2024_09 作業3 以Node-Red 為主

 2024_09 作業3  (以Node-Red 為主  Arduino 可能需要配合修改 ) Arduino 可能需要修改的部分 1)mqtt broker  2) 主題Topic (發行 接收) 3) WIFI ssid , password const char br...

  • python pip 不是内部或外部命令--解決方法
    python pip 不是内部或外部命令 -- 解決方法 要安裝 Pyqt5 1. 首先，開啟命令提示字元。 2. 輸入 pip3 install pyqt5 好像不能執行 ! ! 錯誤顯示 : ‘ pip3 ’ 不是內部或外部命令、可執行的程式或批...
  • 2017 勤益科大 課程講義 下載
    課程講義 下載 11/20 1)  PPT 下載  +  程式下載    http://www.mediafire.com/file/cru4py7e8pptfda/106%E5%8B%A4%E7%9B%8A2-1.rar 11/27 2)  PPT 下載...
  • 認 識 PreFix、InFix、PostFix
    • 認 識 PreFix、InFix、PostFix PreFix（前序式）：* + 1 2 + 3 4 InFix（中序式）： (1+2)*(3+4) PostFix（後序式）：1 2 + 3 4 + * 後 序式的運算 例如： 運算時由 後序式的...