Electrónica-arduino: base de datos para node red (guardar tus datos)

Hola, hoy les traigo algo que me tomó su tiempo encontrar (y lo encontré todo fragmentado) así que me pareció bueno compartirlo, sobre todo si se almacenan los datos de los sensores.

La base de datos que vamos a aplicar en el node red se llama SQLite, y lo vamos a buscar como "node-red-node-sqlite". Una vez que se termine de instalar nos va a aparecer el siguiente nodo:

Para poder usar este nodo vamos a mandarle mensajes en la entrada, pero en lugar de enviarlos por msg.payload como de costumbre, vamos a hacerlo a través del tópico (msg.topic).
Lo que voy a explicarles es como crear tablas nuevas, insertar valores, modificar esos valores, seleccionarlos, borrarlos, agregar nuevas columnas a la tabla y borrar por completo a la tabla.
Para todo esto vamos a usar un único ejemplo, por lo que siempre nos vamos a referir a la misma. El ejemplo va a ser una tabla con los datos del clima que podríamos obtener de nuestra estación meteorológica.

Crear una tabla

Para crear una tabla, el comando es "CREATE TABLE", luego hay que continuar con el nombre de la tabla, para nuestro ejemplo lo vamos a llamar "clima", luego vamos a poner, entre paréntesis, los nombres de las variables que se van a usar seguido por el tipo de variable, si tiene auto incremento y el valor por defecto, el auto incremento y el valor por defecto son opcionales.
Para continuar con nuestro ejemplo, la tabla se va a llamar "clima" y va a tener las variables id, temperatura y humedad, y nos va a quedar de la siguiente forma:

CREATE TABLE clima(id INTEGER PRIMARY KEY AUTOINCREMENT, temperatura NUMERIC, humedad NUMERIC)

Entonces, vamos a tener la variable "id" que va a ser del tipo entero (INTEGER) y que se va a incrementar sola cada vez que se agregue una nueva fila a la tabla (PRIMARY KEY AUTOINCREMENT), luego vamos a tener la variable "temperatura" que va a ser del tipo numérico y la variable "humedad" que al igual que la anterior también va a ser numérica.

Insertar nuevos valores en la tabla

El ingreso de nuevos datos en la tabla se va a realizar mediante el comando "INSERT INTO", luego hay que indicar el nombre de la tabla al que le queremos ingresar los nuevos datos, en nuestro caso "clima" y entre paréntesis las variables que querramos editar, cerramos paréntesis y se pone "VALUES" y entre paréntesis se ponen los valores que queremos asignarlo a cada variable (el orden importa mucho).
Para nuestro ejemplo supongamos que queremos agregar los datos de temperatura en 24,5º y humedad en 48%, entonces la línea a enviar sería la siguiente:

INSERT INTO clima(temperatura, humedad) VALUES(24.5, 48)

Acá hay que aclarar dos cosas, lo primero es que los valores numéricos no pueden llevar símbolos como º o %, sólo el valor numérico, y segundo, se usa el punto en lugar de la coma para los valores decimales.
Finalmente el valor de id, para este caso, se completa solo, si hubiesemos declarado un valor por defecto, se hubiese asignado sólo.

Modificar los valores ya ingresados

No siempre los valores ingresados se mantienen constantes, si nuestra tabla estuviese compuesta por nombres de personas y sus estados civiles, este último podría estar sujeto a cambios en función de (justamente) su estado civil. Para llevarlo a nuestro ejemplo, supongamos que el valor que se ingresó en la variable de la temperatura es incorrecto, entonces lo que vamos a hacer es editarlo. Esto lo vamos a conseguir con el comando "UPDATE", luego le vamos a decir el nombre de la tabla, para nuestro caso sería "clima", y le vamos a indicar cambie el valor de la variable temperatura con el comando "SET" seguido del nombre de la variable "temperatura", y para asignarle el valor vamos a poner un igual y el valor, ahora es cuando el valor de id entra en juego, porque nos va a ayudar a identificar en cuál de todas nuestras filas queremos hacer la modificación, como nosotros sabemos que tenemos una sola fila, el valor del id va a ser 1, entonces vamos a terminar nuestra sentencia con "WHERE id = 1", quedando de la siguiente forma:

UPDATE clima SET temperatura = 25 WHERE id = 1

Como dato, el autoincremento de una variable siempre comienza en 1.

Seleccionar los valores (mostrarlos)

Para poder acceder a los valores guardados en la tabla lo vamos a hacer a través del siguiente comando:

SELECT * FROM clima

en donde el asterisco (*) va a indicar que queremos recuperar todas las variables de la tabla de todas las filas, en caso de que querramos obtener solamente el id, o la temperatura, debemos ingresar el nombre de la variable en lugar del asterisco, y si queremos recuperar más de una variable, hay que separarlas con comas, ejemplo:

SELECT id FROM clima

Esto nos va a devolver todas las filas que tienen la variable id. Ahora si hacemos lo siguiente:

SELECT id, temperatura FROM clima

obtendremos tanto la variable "id" como "temperatura". Todo esto siempre se devuelve como un arreglo de objetos, es decir que el formato de la variable va a ser el siguiente:

msg.payload[0].temperatura;

En la linea de arriba accedemos a la variable temperatura del índice 0 del arreglo que se encuentra almacenado en la variable msg.payload. Al final de esta entrada les voy a dejar un ejemplo para que puedan exportar con las explicaciones necesarias.

Borrar filas

Antes que nada, voy a avisar que este comando no se puede deshacer, una vez que lo ejecutemos habremos perdido lo borrado, dependiendo del caso, quizá lo mejor sea no usarlo y guardar el índice o agregar una variable para saber qué índices no se quieren usar.

Supongamos que nuestra tabla está de la siguiente manera:

Para borrar una fila completa (es decir, todas las variables de un cierto índice) el comando que vamos a utilizar es:

DELETE from clima WHERE id = 7

Y nuestra tabla va a quedar así:

Como podemos apreciar, el comando es "DELETE from" luego indicamos el nombre de la tabla, seguido de "WHERE" y la variable con el valor que querramos buscar, en el ejemplo de arriba se va a borrar de la tabla clima la fila que contenga el valor 7 en la variable "id", es decir que si ponemos lo siguiente:

DELETE from clima WHERE temperatura = 23

Se van a borrar todas las filas cuya variable "temperatura" tenga un valor de 23, si parece confuso a continuación está la tabla actualizada:

Agregar nuevas columnas a la tabla

Para agregar columnas hay dos caminos, uno es más simple que el otro (esto me costó encontrarlo, al menos para mi que no sé (o no sabía) nada sobre bases de datos, y quizá sea la razón por la cuál estoy escribiendo esta entrada), el primero (el más simple) consiste en agregar una columna (hablando mal y pronto, una nueva variable) nueva al final de todas las demás y siguiendo el ejemplo de la base de datos del clima, supongamos que querramos ingresar la cantidad de luz medida en luxes (INGRESAR LINK DE LUXES), lo vamos a hacer bajo el comando:

ALTER TABLE clima ADD COLUMN luz NUMERIC

En donde el comando es "ALTER TABLE", seguido por el nombre de la table, en nuestro caso "clima" y luego le indicamos que queremos agregar una nueva columna agregarndo "ADD COLUMN" seguido del nombre de la misma y del tipo de variable (en nuestro caso el nombre sería "luz" y el tipo "NUMERIC", es decir, numérica). Luego de ingresar ese comando la tabla nos va a quedar así:

Opcionalmente si queremos agregar un valor por defecto, debemos poner, al final de la sentencia, DEFAULT y seguido por el valor, en el siguiente ejemplo vamos a asignarle el valor 1:

ALTER TABLE clima ADD COLUMN luz NUMERIC DEFAULT 1

Y quedaría así:

La segunda opción es la más compleja, porque debemos hacer varios pasos que involucran renombrar, crear, insertar y borrar tablas (este último lo voy a explicar al final de la entrada), pero nos permite agregar la nueva columna en donde querramos, lo que nos da una mayor prolijidad. Primero vamos a renombrar nuestra tabla con la línea:

ALTER TABLE clima RENAME TO TemporalClimaViejo

Como se puede apreciar utilizamos el "ALTER TABLE" pero esta vez lo que hacemos es cambiar el nombre mediante el comando "RENAME TO" seguido del nuevo nombre, en este caso "TemporalClimaViejo", luego vamos crear una nueva tabla con la variable "luz" en la posición que deseemos, en nuetro caso entre el "id" y "temperatura":

CREATE TABLE clima(id INTEGER PRIMARY KEY AUTOINCREMENT, luz NUMERIC, temperatura NUMERIC, humedad NUMERIC)

Luego lo que vamos a hacer es copiar los viejos datos en la nueva tabla:

INSERT INTO clima (id, temperatura, humedad) SELECT id, temperatura, humedad FROM TemporalClimaViejo

Acá utilizamos el comando "INSERT INTO" para asignar valores de "id", "temperatura" y "humedad" de la tabla "clima" (recordemos que esta no es la tabla clima original, la original la renombramos y luego creamos esta), desde la tabla TemporalClimaViejo. Quedando:

Hay que recordar que el órden que indicamos en dónde vamos a guardar y de dónde vamos a leer las variables importa, no es lo mismo:

INSERT INTO clima (id, temperatura, humedad) SELECT id, temperatura, humedad FROM TemporalClimaViejo

que:

INSERT INTO clima (id, temperatura, humedad) SELECT temperatura, id, humedad FROM TemporalClimaViejo

o:

INSERT INTO clima (temperatura, id, humedad) SELECT id, temperatura, humedad FROM TemporalClimaViejo

En el primer caso se van a realizar las siguientes asignaciones:
id = id
temperatura = temperatura
humedad = humedad

En el segundo:
id = temperatura
temperatura = id
humedad = humedad

Y en el tercero:

temperatura = id
id = temperatura
humedad = humedad

Podemos ver que el segundo y tercer caso tienen el mismo resultado, pero lo puse porque vale la pena mencionarlo. Gráficamente va a quedar así:

Finalmente lo que vamos a realizar es borrar la tabla vieja mediante el comando:

DROP TABLE TemporalClimaViejo

Esta opción es la más prolija porque nos permite editar la posición de las variables dentro de la tabla, aunque no siempre es necesario porque si queremos agregar la variable al final de todas las columnas simplemente utilizamos a primer opción.

Borrar por completo a la tabla

Antes que nada voy a avisar que este comando sirve para borrar la tabla completa, con todos sus datos y es irreversible, mi recomendación es que se renombre la lista para no perder los datos.
Si bien este punto en cierta medida ya lo vimos, lo voy a explicar un poco más en profundidad. El comando es:

DROP TABLE clima

En donde la parte "DROP TABLE" le indica a la base de datos que va a tener que borrar una tabla completa, y luego le sigue el nombre de la tabla, en nuestro caso "clima".

Como podemos apreciar en la imágen, esa es la estructura más básica para utilizar la base de datos, en los nodos de inyección tenemos los comandos mencionados más arriba, estos nodos pueden reemplazarse por un nodo de función para hacerlo más interesante. Lo primero que vamos a hacer es hacer doble click sobre el nodo de sqlite y vamos a crear una nueva base de datos en la ruta que querramos, luego vamos mandar los datos contenidos por el nodo de inyección llamado "CREATE" que va a crear la tabla propiamente dicha, y luego se puede jugar con los siguientes comandos, como podremos observar los resultados salen por la pestaña de debug, por lo que podremos ver qué está sucediendo.