Node-Red MQTT SQLite

 Node-Red MQTT SQLite 

CREATE TABLE "LED" (

"id" INTEGER UNIQUE,

"time_led" TEXT,

"led_status" TEXT,

PRIMARY KEY("id")

)

insert into LED (time_led, led_status) values ($thetime , $thevalue)

// Create a Params variable

// with a time and value component

//

var date = new Date();

var h = date.getHours();

var m = date.getMinutes();

var s = date.getSeconds();

if(h<10)

{

   h = '0'+h;

}

if(m<10)

{

  m = '0' + m;

}

if(s<10)

{

  s = '0' + s;

}

var hms= h + '-' + m + '-' + s ;

msg.params = { $thetime:hms, $thevalue:msg.payload }

return msg;

[{"id":"fa41d7f64d5ad546","type":"mqtt in","z":"502ea5d35ca99ee3","name":"","topic":"alex9ufo/led/led_event","qos":"1","datatype":"auto","broker":"841df58d.ee5e98","inputs":0,"x":140,"y":100,"wires":[["f7cc0a033bc0947b","051c35ea58e88ffd","3be6ab6314a120c5","9d4879569a7910cf"]]},{"id":"f7cc0a033bc0947b","type":"ui_text","z":"502ea5d35ca99ee3","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"name":"","label":"MQTT Suscribe Data","format":"{{msg.payload}}","layout":"col-center","x":400,"y":100,"wires":[]},{"id":"f253317f9274bcd0","type":"ui_button","z":"502ea5d35ca99ee3","name":"","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"passthru":false,"label":"LED ON","tooltip":"","color":"white","bgcolor":"","icon":"fa-circle","payload":"ON","payloadType":"str","topic":"","x":120,"y":320,"wires":[["3b22cabfd05ef400"]]},{"id":"4a93f372a51961b8","type":"ui_button","z":"502ea5d35ca99ee3","name":"","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"passthru":false,"label":"LED OFF","tooltip":"","color":"black","bgcolor":"","icon":"fa-circle-o","payload":"OFF","payloadType":"str","topic":"","x":120,"y":360,"wires":[["3b22cabfd05ef400"]]},{"id":"3b22cabfd05ef400","type":"mqtt out","z":"502ea5d35ca99ee3","name":"","topic":"alex9ufo/led/led_event","qos":"1","retain":"false","broker":"841df58d.ee5e98","x":400,"y":360,"wires":[]},{"id":"051c35ea58e88ffd","type":"function","z":"502ea5d35ca99ee3","name":"Format timestamp","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 = msg.payload + ' --> Time:(' + h + ':' + m + ':' + s + ')' ;\n\nreturn msg;","outputs":1,"noerr":0,"x":150,"y":260,"wires":[["36d2c202b7ea04b5"]]},{"id":"36d2c202b7ea04b5","type":"function","z":"502ea5d35ca99ee3","name":"Set Line API ","func":"msg.headers = {'content-type':'application/x-www-form-urlencoded','Authorization':'Bearer A4wwPNh2WqB7dlfeQyyIAwtggn1kfZSI5LkkCdia1gB'};\nmsg.payload = {\"message\":msg.payload};\nreturn msg;\n\n//oR7KdXvK1eobRr2sRRgsl4PMq23DjDlhfUs96SyUBZu","outputs":1,"noerr":0,"x":310,"y":300,"wires":[["2b3ee0b40ab0e1fe"]]},{"id":"2b3ee0b40ab0e1fe","type":"http request","z":"502ea5d35ca99ee3","name":"","method":"POST","ret":"txt","paytoqs":false,"url":"https://notify-api.line.me/api/notify","tls":"","persist":false,"proxy":"","authType":"","x":500,"y":300,"wires":[["8bd9f85426538efa"]]},{"id":"8bd9f85426538efa","type":"debug","z":"502ea5d35ca99ee3","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":710,"y":300,"wires":[]},{"id":"3be6ab6314a120c5","type":"debug","z":"502ea5d35ca99ee3","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":390,"y":40,"wires":[]},{"id":"54c326221f8367ff","type":"ui_audio","z":"502ea5d35ca99ee3","name":"","group":"6c9116b.b62d4e8","voice":"zh-TW","always":true,"x":520,"y":140,"wires":[]},{"id":"038b2578a5c1a5f5","type":"ui_button","z":"502ea5d35ca99ee3","name":"","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"passthru":false,"label":"LED Flashing","tooltip":"","color":"blue","bgcolor":"","icon":"fa-circle-o","payload":"FLASH","payloadType":"str","topic":"","x":140,"y":400,"wires":[["3b22cabfd05ef400"]]},{"id":"9d4879569a7910cf","type":"function","z":"502ea5d35ca99ee3","name":"","func":"var st1;\nif (msg.payload === \"ON\") {\n   st1=\"LED開\"; \n} \nelse if (msg.payload === \"OFF\") {\n  st1=\"LED-off\";\n}\nelse if (msg.payload === \"FLASH\") {\n  st1=\"LED閃爍\";\n}\nelse if (msg.payload === \"TIMER\") {\n  st1=\"LED開五秒鐘\";\n}\n\nmsg.payload=st1;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":310,"y":140,"wires":[["54c326221f8367ff","f95b0a69f8dcc42c"]]},{"id":"37f13b9f059edfe8","type":"ui_button","z":"502ea5d35ca99ee3","name":"","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"passthru":false,"label":"LED ON 5 Sec","tooltip":"","color":"purple","bgcolor":"","icon":"fa-circle-o","payload":"TIMER","payloadType":"str","topic":"","x":140,"y":440,"wires":[["3b22cabfd05ef400"]]},{"id":"037ff977cb9ec423","type":"debug","z":"502ea5d35ca99ee3","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":790,"y":200,"wires":[]},{"id":"811cc21d8074d652","type":"ui_button","z":"502ea5d35ca99ee3","name":"","group":"6c9116b.b62d4e8","order":35,"width":0,"height":0,"passthru":false,"label":"CREATE DB","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":130,"y":480,"wires":[["bee381e10ebe359a"]]},{"id":"bee381e10ebe359a","type":"sqlite","z":"502ea5d35ca99ee3","mydb":"af927aba.0dc108","sqlquery":"fixed","sql":"CREATE TABLE \"LED\" (\n\t\"id\"\tINTEGER UNIQUE,\n\t\"time_led\"\tTEXT,\n\t\"led_status\"\tTEXT,\n\tPRIMARY KEY(\"id\")\n)","name":"CREATE ","x":360,"y":480,"wires":[["6cc649aad6f09d7b"]]},{"id":"6cc649aad6f09d7b","type":"debug","z":"502ea5d35ca99ee3","name":"debug  ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":570,"y":480,"wires":[]},{"id":"f95b0a69f8dcc42c","type":"function","z":"502ea5d35ca99ee3","name":"msg.params","func":"// Create a Params variable\n// with a time and value component\n//\n\n\n//msg.topic = \"INSERT INTO LED (id, time_led, led_status) VALUES (?,?,?)\";\n\nvar date = new Date();\nvar h = date.getHours();\nvar m = date.getMinutes();\nvar s = date.getSeconds();\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}\n\nvar hms= h + '-' + m + '-' + s ;\n\nmsg.params = { $thetime:hms, $thevalue:msg.payload }\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":410,"y":200,"wires":[["31c50117da20fa57"]]},{"id":"31c50117da20fa57","type":"sqlite","z":"502ea5d35ca99ee3","mydb":"af927aba.0dc108","sqlquery":"prepared","sql":"insert into LED (time_led, led_status) values ($thetime , $thevalue)","name":"INSERT INTO SQLITE","x":600,"y":200,"wires":[["037ff977cb9ec423"]]},{"id":"841df58d.ee5e98","type":"mqtt-broker","name":"","broker":"broker.mqtt-dashboard.com","port":"1883","clientid":"","usetls":false,"compatmode":false,"keepalive":"15","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"6c9116b.b62d4e8","type":"ui_group","name":"Storing IOT Data ","tab":"eeb8e179.a47a4","order":1,"disp":true,"width":"6","collapse":true},{"id":"af927aba.0dc108","type":"sqlitedb","db":"C:\\Users\\User\\.node-red\\sensor_0611_led.db","mode":"RWC"},{"id":"eeb8e179.a47a4","type":"ui_tab","name":"eWeLink","icon":"dashboard","disabled":false,"hidden":false}]