Node-RED SQLite DataBase

Create database db_004.db   in  C:\Users\alex\AppData\Roaming\npm  directory

CREATE TABLE RANDOMNUM( id INT PRIMARY KEY NOT NULL, uidname TEXT NOT NULL) 

Insert Function

msg.topic = "INSERT INTO RANDOMNUM (id,uidname) VALUES (?,?)";
var id= Date.now() ;
msg.payload = [id , msg.payload];
return msg;

先將Node-red 程式停止 用  DB Browser for SQLite 程式 瀏覽資料庫的資料

Flow JSON Source code

[{"id":"e20757bf.7a8bd8","type":"comment","z":"e589c8e1.a6c6e8","name":"sqlite.org","info":"https://fred.sensetecnic.com/\nhttp://noderedguide.com/tutorial-sqlite-and-node-red/\nhttps://www.sqlite.org/lang.html\nhttps://www.sqlite.org/datatype3.html#expraff\n\nCREATE TABLE RANDOMNUM( TIMESTAMP INT PRIMARY KEY NOT NULL, VALUE INT NOT NULL, BOOL INT NOT NULL)\n\n//INSERT\nvar randomNum = Math.round(Math.random()*100);\nvar largeBool = (randomNum > 50)?1:0;\nvar newMsg = {\n \"topic\": \"INSERT INTO RANDOMNUM VALUES ( \" + msg.payload + \", \" + randomNum + \", \" + largeBool + \")\"\n}\nreturn newMsg;\n\nDELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-30 seconds')*1000\nDELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-24 hours')*1000\nDELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-7 days')*1000\n\nSELECT COUNT(*) FROM RANDOMNUM\n\nUPDATE RANDOMNUM SET BOOL = 1 WHERE VALUE > 80 AND BOOL = 0\n\nSELECT * FROM RANDOMNUM ORDER BY TIMESTAMP DESC LIMIT 100;\n<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>{{$index}}</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>\n\n","x":120,"y":20,"wires":[]},{"id":"3d894a61.e39596","type":"inject","z":"e589c8e1.a6c6e8","name":"CREATE DB","topic":"CREATE TABLE RANDOMNUM( id INT PRIMARY KEY NOT NULL, uidname TEXT NOT NULL)  ","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":80,"wires":[["5555653.e543f9c"]]},{"id":"c36fd839.ffe478","type":"debug","z":"e589c8e1.a6c6e8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":570,"y":80,"wires":[]},{"id":"34673595.cf49da","type":"inject","z":"e589c8e1.a6c6e8","name":"INSERT","topic":"","payload":"Alex_test","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":220,"wires":[["5e024b52.b7b894"]]},{"id":"5e024b52.b7b894","type":"function","z":"e589c8e1.a6c6e8","name":"INSERT","func":"\nmsg.topic = \"INSERT INTO RANDOMNUM (id,uidname) VALUES (?,?)\";\nvar id= Date.now() ;\nmsg.payload = [id , msg.payload];\nreturn msg;\n","outputs":1,"noerr":0,"x":460,"y":220,"wires":[["e896b4d1.a91068"]]},{"id":"94d53ffd.cc12f","type":"debug","z":"e589c8e1.a6c6e8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":750,"y":220,"wires":[]},{"id":"4892e705.435ad8","type":"inject","z":"e589c8e1.a6c6e8","name":"DELETE 30s","topic":"DELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-30 seconds')*1000","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":280,"wires":[["1b74bd9.e807b42"]]},{"id":"b7961ad7.ebc218","type":"inject","z":"e589c8e1.a6c6e8","name":"DELETE 24h","topic":"DELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-24 hours')*1000","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":320,"wires":[["1b74bd9.e807b42"]]},{"id":"80c34500.0ec9b8","type":"inject","z":"e589c8e1.a6c6e8","name":"DELETE 7d","topic":"DELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-7 days')*1000","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":360,"wires":[["1b74bd9.e807b42"]]},{"id":"b0726f0e.9de2e","type":"debug","z":"e589c8e1.a6c6e8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":530,"y":340,"wires":[]},{"id":"9344f59.05f8e08","type":"debug","z":"e589c8e1.a6c6e8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":610,"y":540,"wires":[]},{"id":"e11209f0.f0b0d8","type":"inject","z":"e589c8e1.a6c6e8","name":"COUNT","topic":"SELECT COUNT(*) FROM RANDOMNUM","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":130,"y":560,"wires":[["a802c882.9e8368"]]},{"id":"b0d564b2.35d6f8","type":"inject","z":"e589c8e1.a6c6e8","name":"SELECT","topic":"SELECT * FROM RANDOMNUM ORDER BY  id  DESC LIMIT 100;","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":620,"wires":[["f1cdb8b0.ff1078"]]},{"id":"a92b7df2.2f457","type":"ui_template","z":"e589c8e1.a6c6e8","group":"320899a7.844536","name":"","order":0,"width":"6","height":"7","format":"<table style=\"width:100%\">\n  <tr>\n    <th>Index</th> \n    <th>id</th>\n    <th>uidname</th> \n   \n  </tr>\n  <tr ng-repeat=\"x in msg.payload | limitTo:20\">\n    <td>{{$index}}</td>\n    <td>{{msg.payload[$index].id}}</td>\n    <td>{{msg.payload[$index].uidname}}</td> \n \n  </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"templateScope":"local","x":600,"y":580,"wires":[[]]},{"id":"850b713a.65bde","type":"debug","z":"e589c8e1.a6c6e8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":610,"y":620,"wires":[]},{"id":"5555653.e543f9c","type":"sqlite","z":"e589c8e1.a6c6e8","mydb":"d2b794d0.abbc68","sqlquery":"msg.topic","sql":"","name":"db_004","x":340,"y":80,"wires":[["c36fd839.ffe478"]]},{"id":"e896b4d1.a91068","type":"sqlite","z":"e589c8e1.a6c6e8","mydb":"d2b794d0.abbc68","sqlquery":"msg.topic","sql":"","name":"db_004","x":600,"y":220,"wires":[["94d53ffd.cc12f"]]},{"id":"1b74bd9.e807b42","type":"sqlite","z":"e589c8e1.a6c6e8","mydb":"d2b794d0.abbc68","sqlquery":"msg.topic","sql":"","name":"db_004","x":380,"y":340,"wires":[["b0726f0e.9de2e"]]},{"id":"a802c882.9e8368","type":"sqlite","z":"e589c8e1.a6c6e8","mydb":"d2b794d0.abbc68","sqlquery":"msg.topic","sql":"","name":"db_004","x":380,"y":560,"wires":[["9344f59.05f8e08"]]},{"id":"f1cdb8b0.ff1078","type":"sqlite","z":"e589c8e1.a6c6e8","mydb":"d2b794d0.abbc68","sqlquery":"msg.topic","sql":"","name":"db_004","x":380,"y":620,"wires":[["a92b7df2.2f457","850b713a.65bde"]]},{"id":"74154e1b.94744","type":"mqtt out","z":"e589c8e1.a6c6e8","name":"","topic":"ale9ufo/sqlite4","qos":"","retain":"","broker":"e4d9b72d.d14398","x":440,"y":400,"wires":[]},{"id":"c0e847f1.f45d78","type":"mqtt in","z":"e589c8e1.a6c6e8","name":"","topic":"ale9ufo/sqlite4","qos":"2","datatype":"auto","broker":"e4d9b72d.d14398","x":130,"y":160,"wires":[["e3ee04a6.c3c8b8","62720a94.0617f4"]]},{"id":"bb882ae1.a38818","type":"inject","z":"e589c8e1.a6c6e8","name":"","topic":"","payload":"{\"RFIDNo\":\"Alex9ufo\"}","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":400,"wires":[["74154e1b.94744"]]},{"id":"e3ee04a6.c3c8b8","type":"json","z":"e589c8e1.a6c6e8","name":"","property":"payload","action":"str","pretty":false,"x":290,"y":160,"wires":[["5e024b52.b7b894"]]},{"id":"6eb6cf26.f828d","type":"ui_text","z":"e589c8e1.a6c6e8","group":"320899a7.844536","order":5,"width":0,"height":0,"name":"","label":"text","format":"{{msg.payload}}","layout":"row-spread","x":510,"y":120,"wires":[]},{"id":"62720a94.0617f4","type":"function","z":"e589c8e1.a6c6e8","name":"json+時分秒","func":"var date = new Date();\nvar h = date.getHours();\nvar m = date.getMinutes();\nvar s = date.getSeconds();\nif(h<10){\n    h = '0'+h;\n}\nif(m<10){\n    m = '0' + m;\n}\nif(s<10){\n    s = '0' + s;\n}\nmsg.payload = 'Time:(' + h + ':' + m + ':' + s + ')'+ msg.payload ;\nreturn msg;\n","outputs":1,"noerr":0,"x":330,"y":120,"wires":[["6eb6cf26.f828d"]]},{"id":"17d785ed.5ff65a","type":"inject","z":"e589c8e1.a6c6e8","name":"","topic":"","payload":"{\"RFIDNo\":\"RaserryPI\"}","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":440,"wires":[["74154e1b.94744"]]},{"id":"c10a901d.fd08e","type":"ui_button","z":"e589c8e1.a6c6e8","name":"Alex9ufo","group":"320899a7.844536","order":2,"width":0,"height":0,"passthru":false,"label":"insert1","tooltip":"","color":"","bgcolor":"","icon":"","payload":"{\"RFIDNo\":\"Alex9ufo\"}","payloadType":"json","topic":"","x":120,"y":480,"wires":[["74154e1b.94744"]]},{"id":"10169ee9.450341","type":"ui_button","z":"e589c8e1.a6c6e8","name":"Select","group":"320899a7.844536","order":9,"width":0,"height":0,"passthru":false,"label":"select","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"SELECT * FROM RANDOMNUM ORDER BY  id  DESC LIMIT 100;","x":130,"y":660,"wires":[["f1cdb8b0.ff1078"]]},{"id":"8304c0d6.ffefa","type":"ui_button","z":"e589c8e1.a6c6e8","name":"RaserryPI","group":"320899a7.844536","order":3,"width":0,"height":0,"passthru":false,"label":"insert2","tooltip":"","color":"","bgcolor":"","icon":"","payload":"{\"RFIDNo\":\"RaserryPI\"}","payloadType":"json","topic":"","x":120,"y":520,"wires":[["74154e1b.94744"]]},{"id":"15c90a8c.4be805","type":"ui_button","z":"e589c8e1.a6c6e8","name":"Alex9ufo","group":"320899a7.844536","order":2,"width":0,"height":0,"passthru":false,"label":"insert3","tooltip":"","color":"","bgcolor":"","icon":"","payload":"Alex_test","payloadType":"str","topic":"","x":300,"y":260,"wires":[["5e024b52.b7b894","6eb6cf26.f828d"]]},{"id":"320899a7.844536","type":"ui_group","z":"","name":"SQLITE4","tab":"ef1e941f.54b368","order":1,"disp":true,"width":"6","collapse":false},{"id":"d2b794d0.abbc68","type":"sqlitedb","z":"","db":"db_004.db"},{"id":"e4d9b72d.d14398","type":"mqtt-broker","broker":"broker.mqtt-dashboard.com","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":15,"cleansession":true,"birthQos":"0","willQos":"0"},{"id":"ef1e941f.54b368","type":"ui_tab","z":"","name":"SQLite","icon":"dashboard","order":1,"disabled":false,"hidden":false}]