Basic Use of SQLite Node

README.md

Basic use of SQLite Node:

• Inject every 15 seconds a random number and inserting in the table the current timestamp for a time series data base.
• Select all inserted data and show in debug.

Table creation command:

CREATE TABLE "datos_aleatorios" ( "id" INTEGER UNIQUE, "fecha" TEXT, "dato" TEXT, PRIMARY KEY("id" AUTOINCREMENT) );

flow.json

[{"id":"80ef1349e05a0eab","type":"tab","label":"Flow 1","disabled":false,"info":"","env":[]},{"id":"80432c8079eb3106","type":"sqlite","z":"80ef1349e05a0eab","mydb":"a4bef7ed8db80639","sqlquery":"msg.topic","sql":"","name":"","x":520,"y":200,"wires":[["cae255bd8461499d"]]},{"id":"acb5479b46200079","type":"inject","z":"80ef1349e05a0eab","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"60","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":110,"y":200,"wires":[["da8dece2be573a04"]]},{"id":"da8dece2be573a04","type":"change","z":"80ef1349e05a0eab","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"\"INSERT INTO datos_aleatorios ('fecha','dato') VALUES ('\"&$now('[D01]/[M01]/[Y0001] [H01]:[m01]:[s01]','+0200')&\"','\"&$random()&\"')\"","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":280,"y":200,"wires":[["80432c8079eb3106"]]},{"id":"8026c6300b9e0c4c","type":"inject","z":"80ef1349e05a0eab","name":"Crea tabla","props":[{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"CREATE TABLE \"datos_aleatorios\" ( \"id\" INTEGER UNIQUE, \"fecha\" TEXT, \"dato\" TEXT, PRIMARY KEY(\"id\" AUTOINCREMENT) );","x":260,"y":80,"wires":[["80432c8079eb3106"]]},{"id":"e7010d6bd67f6655","type":"comment","z":"80ef1349e05a0eab","name":"Inserta datos cada 15 segundos","info":"","x":170,"y":140,"wires":[]},{"id":"a4eafa7556bb7b8e","type":"comment","z":"80ef1349e05a0eab","name":"Crea la tabla necesaria para insertar los datos","info":"","x":190,"y":40,"wires":[]},{"id":"56ef026a47e3eee4","type":"inject","z":"80ef1349e05a0eab","name":"Todos los datos","props":[{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT * FROM datos_aleatorios","x":300,"y":300,"wires":[["80432c8079eb3106"]]},{"id":"fee89fee6a1ef74e","type":"comment","z":"80ef1349e05a0eab","name":"Saca todos los datos de la tabla","info":"","x":170,"y":260,"wires":[]},{"id":"1f9107834c8ad0ef","type":"inject","z":"80ef1349e05a0eab","name":"Consulta > 0.8","props":[{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT COUNT(dato) FROM datos_aleatorios WHERE dato > 0.8","x":300,"y":400,"wires":[["80432c8079eb3106"]]},{"id":"992d3606222d9d65","type":"comment","z":"80ef1349e05a0eab","name":"Consulta el nº de datos mayores que 0.8","info":"","x":200,"y":360,"wires":[]},{"id":"4a29227291f92080","type":"inject","z":"80ef1349e05a0eab","name":"Borra datos tabla","props":[{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"DELETE FROM datos_aleatorios","x":480,"y":80,"wires":[["80432c8079eb3106"]]},{"id":"cae255bd8461499d","type":"debug","z":"80ef1349e05a0eab","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":690,"y":200,"wires":[]},{"id":"79e82be227be63a4","type":"comment","z":"80ef1349e05a0eab","name":"Borra la tabla","info":"","x":470,"y":40,"wires":[]},{"id":"a4bef7ed8db80639","type":"sqlitedb","db":"/data/bbdd.db","mode":"RWC"}]