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}]
沒有留言:
張貼留言