# Base de datos para almacenar los mensajes MQTT

A pesar de haber comentado previamente que consideramos que una base de datos de tipo no relacional es mejor a la hora de almacenar los mensajes que llegan a nuestro broker, podemos utilizar una base de datos de tipo relacional sin ningún inconveniente.

En nuestro caso, utilizaremos una base de datos de tipo relacional debido a que varios servicios solo poseen extensiones o plugins que permiten la obtención de datos de este tipo de bases de datos de forma gratuita. Por ejemplo, Grafana sólo permite utilizar como fuentes de datos bases de tipo relacional, por lo que si queremos utilizar bases de datos no relacionales como mongoDB deberemos pagar por la licencia 'Enterprise'.

A continuación detallaremos cuál es el esquema de base de datos que utilizaremos para el almacenamiento de los datos.

## Esquema de la base de datos (DER)


Como se observa en la anterior figura, la base de datos dispone de tres tablas: mqtt_user, mqtt_acl y mqtt_data.

La tabla mqtt_user contiene información acerca de las credenciales de los usuarios que pueden usar el broker. Los datos que se almacenan son: ID, nombre de usuario, contraseña, salt (adición que se le agrega a la contraseña al momento de encriptarla y almacenarla), si es un superusuario y la fecha de creación de este.

La tabla mqtt_acl, por su parte, contiene las reglas que le permiten a un usuario suscribirse o publicar en un determinado tópico. Los datos que se almacenan en esta tabla son: el ID de la regla, el tipo de regla que es (permitir o denegar), la dirección IP de origen (si se quisiera especificar), el nombre de usuario al que aplicará la regla, el cliente ID o identificador del dispositivo (por si quisieramos crear reglas para un dispositivo en particular), acceso (que determina si la regla es para publicar, suscrbirse o ambas) y el tópico al que aplica.

La tabla mqtt_data permite la persistencia de los datos que son enviados a nuestro broker MQTT. Para el caso de esta tabla, los campos de cada registro son: ID del registro, tópico del que provino el mensaje, el valor de la medida que se desea almacenar, la fecha y hora de creación del registro y, por último, latitud, longitud y altitud (para almacenar, si se requiere, el lugar de donde provino la medición).

## Scrips de MariaDB para la creación de las tablas y la database

Creación de la database:

CREATE DATABASE mqtt;

Seleccionamos la database que creamos:

USE mqtt;

Creación de la tabla mqtt_user:

CREATE TABLE mqtt_user (   
    id int(11) unsigned NOT NULL AUTO_INCREMENT,   
    username varchar(100) DEFAULT NULL,
    password varchar(100) DEFAULT NULL,
    salt varchar(35) DEFAULT NULL,
    is_superuser tinyint(1) DEFAULT 0,
    created datetime DEFAULT NULL,
    PRIMARY KEY (id), 
    UNIQUE KEY mqtt_username (username) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Creación de la tabla mqtt_acl:

CREATE TABLE mqtt_acl (  
     id int(11) unsigned NOT NULL AUTO_INCREMENT, 
     allow int(1) DEFAULT 1 COMMENT '0: deny, 1: allow', 
     ipaddr varchar(60) DEFAULT NULL COMMENT 'IpAddress', 
     username varchar(100) DEFAULT NULL COMMENT 'Username',
     clientid varchar(100) DEFAULT NULL COMMENT 'ClientId', 
     access int(2) NOT NULL COMMENT '1: subscribe, 2: publish, 3: pubsub', 
     topic varchar(100) NOT NULL DEFAULT '' COMMENT 'Topic Filter',
     PRIMARY KEY (id),  
     INDEX (ipaddr),  
     INDEX (username),  
     INDEX (clientid)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Creación de la tabla mqtt_data:

CREATE TABLE mqtt_data (
    id int(15) unsigned NOT NULL AUTO_INCREMENT,
    topic varchar(100) NOT NULL COMMENT 'Topic',
    value double NOT NULL COMMENT 'Value',
    context varchar(255) DEFAULT NULL COMMENT 'Context',
    created datetime DEFAULT NULL COMMENT 'Created',
    latitude decimal(10,7) DEFAULT NULL COMMENT 'Latitude',
    longitude decimal(10,7) DEFAULT NULL COMMENT 'Longitude',
    altitude decimal(11,2) DEFAULT NULL COMMENT 'Altitude',
    PRIMARY KEY (id),
    INDEX (topic),
    INDEX (created)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

## Esquema de nodos en Node-RED


## Almacenamiento de los datos

Se realiza mediante Node-RED.

Nodo 'MQTT in' que lee todos los mensajes que recibe el broker. Si tienen el formato adecuado, los parsea y almacena en la base de datos mediante un nuevo registro en la tabla mqtt_data.

El código que se encuentra dentro de un nodo de tipo function es:

if (msg.payload == null) {
    return null;
}

const topic = msg.topic;
const payload = JSON.parse(msg.payload);

let value = null;
if ('value' in payload && typeof payload.value == 'number') {
    value = payload.value;
} else {
    return null;
}

let context = null;
if ('context' in payload && typeof context.comment == 'string') {
    context = context.latitude;
}

let timestamp = null;
if ('timestamp' in payload && typeof payload.timestamp == 'number') {
    timestamp = new Date(payload.timestamp * 1e3).toISOString().slice(0, 19).replace('T', ' ');
} else {
    timestamp = new Date().toISOString().slice(0, 19).replace('T', ' ');
}

let latitude = null;
if ('latitude' in payload && typeof payload.latitude == 'number') {
    latitude = payload.latitude.toFixed(7);
}

let longitude = null;
if ('longitude' in payload && typeof payload.longitude == 'number') {
    longitude = payload.longitude.toFixed(7);
}

let altitude = null;
if ('altitude' in payload && typeof payload.altitude == 'number') {
    altitude = payload.altitude.toFixed(2);
}

msg.topic = "INSERT INTO mqtt_data (topic,value,context,created,latitude,longitude,altitude) VALUES ('"+topic+"','"+value+"','"+context+"','"+timestamp+"',"+latitude+","+longitude+","+altitude+")";

return msg;

Esta función se encarga de tomar el tópico y los valores recibidos por medio del nodo 'MQTT in' de Node-RED.

En primer lugar, se toma el valor de la medición hecha con el sensor para posteriormente almacenarlo en la base de datos.

Luego, chequea si existe una cadena de texto de tipo string que brinde información adicional acerca de la medición y la almacena en una variable llamada 'context'.

A continuación se realiza lo mismo para el timestamp. En caso de existir, se guarda este valor en la variable 'timestamp' y, si no existe, se lo crea y almacena en esta misma variable.

Por último, se revisa si se envió la localización de dónde se realizó la medición.

Una vez chequeados todos los posibles datos, se crea un objeto 'msg' que contiene dentro de la variable 'topic' el comando que utilizará Node-RED para crear un registro en nuestra base de datos (en este caso, la tabla *mqtt_data* de la base de datos *mqtt*). Finalmente, se retorna este 'msg'.