Cómo utilizar SQLite con ESP32 PLC

La integración de SQLite en ESP32, un potente microcontrolador ampliamente utilizado en aplicaciones IoT industriales, revoluciona las capacidades de almacenamiento de los sistemas embebidos. Con la incorporación de SQLite, un sistema de gestión de bases de datos relacionales ligero y rico en funciones, los desarrolladores pueden mejorar la persistencia de datos, la funcionalidad de consulta y la seguridad en el extremo.

Este blog explora las razones para aprovechar SQLite en ESP32, profundiza en sus técnicas de implementación práctica y destaca su idoneidad para entornos con recursos limitados. Explore con nosotros cómo la fusión de SQLite y ESP32 potencia las soluciones IoT industriales con una gestión eficaz de los datos y la toma de decisiones en tiempo real.

Debido a que SQLite tiene un enfoque sin servidor, puede integrar SQLite en proyectos basados en ESP32. Esto le permite aprovechar la potencia de un sistema de gestión de bases de datos relacionales directamente en el dispositivo ESP32, para cosas como el registro ligero, consultas de datos avanzadas y ahorro de variables a través de reinicios.

Para esta demostración, utilizamos el ESP32 21+ Industrial PLC, pero cualquier PLC ESP32 con una tarjeta SD será compatible.

Tendrás que instalar la herramienta de línea de comandos sqlite3 en tu PC, utilizando un gestor de paquetes (como "apt") o descargándola del sitio web oficial de SQLite en https://sqlite.org/cli.html.

Por último, para instalar la librería esp32_arduino_sqlite3_lib library tendrás que utilizar el Gestor de Bibliotecas del IDE de Arduino (busca "esp32_sqlite").

El objetivo del programa ESP32 que desarrollaremos es registrar los valores de dos pines específicos en el microcontrolador ESP32 y almacenarlos en una tabla. Esta funcionalidad sirve para una amplia gama de aplicaciones en las que es necesaria la monitorización continua y el registro de los cambios de estado de los pines.

Mientras que el caso de uso específico de registro de valores de pin puede ser de interés para ciertos lectores, la más amplia toma de este blog es la integración de SQLite con el ESP32 y el potencial que tiene para diversas aplicaciones de registro de datos. Si usted está monitoreando las condiciones ambientales, el seguimiento de las lecturas del sensor, o la recogida de cualquier otro tipo de datos, la comprensión de cómo utilizar SQLite con un ESP32 y una tarjeta SD abre un mundo de posibilidades.

En primer lugar, tenemos que crear la base de datos en el PC con un esquema SQL. Abra un símbolo del sistema o terminal y navegue hasta el directorio donde desea crear el archivo de base de datos. Crea un archivo schema.sql y pega este código:

PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS analog_pins (
       id INTEGER PRIMARY KEY,
       pin VARCHAR(5) UNIQUE
);

CREATE TABLE IF NOT EXISTS analog_log (
       id_pin INTEGER,
       value INTEGER,
       timestamp_log DATETIME,
       FOREIGN KEY (id_pin) REFERENCES analog_pins(id)
);

INSERT INTO analog_pins (pin) VALUES("I0.12");
INSERT INTO analog_pins (pin) VALUES("I0.11");

Con el esquema definido, puedes crear la base de datos con el comando sqlite3 web.db < schema.sql. Ahora, formatea una tarjeta SD usando el sistema de archivos FAT32 (si no está ya en FAT32), y pon el archivo web.db en la raíz de la SD. Extrae la SD y ponla dentro del PLC ESP32, y carga el siguiente programa:

#include "SD.h"

#include <sqlite3.h>
#define DB_NAME "web.db"

#include <RTC2.h>

int openDb(sqlite3** db) {
  int rc = sqlite3_open("/sd/" DB_NAME, db);
  if (rc) {
     Serial.print("Can't open database: ");
     Serial.println(sqlite3_errmsg(*db));
  }
  else {
    Serial.println("Opened database successfully");
  }
  return rc;
}

void closeDb(sqlite3* db) {
  sqlite3_close(db);
}

int execDb(sqlite3* db, const char* query) {
  char *zErrMsg = NULL;
  int rc = sqlite3_exec(db, query, NULL, NULL, &zErrMsg);
  if (rc != SQLITE_OK) {
     Serial.print("SQL error: ");
     Serial.println(zErrMsg);
     sqlite3_free(zErrMsg);
   }
   else {
     Serial.printf("Operation done successfully\n");
   }
   return rc;
}

#define START_QUERY "INSERT INTO analog_log SELECT (SELECT id FROM analog_pins WHERE pin='"
#define MAX_LENGTH_QUERY sizeof(START_QUERY)+32
template <const int PIN>
void read_analog(void* pvParameter) {
  pinMode(PIN, INPUT);
  sqlite3 *db;
  char query[MAX_LENGTH_QUERY];
  const int occupied_query_bytes = snprintf(query, sizeof(query), START_QUERY "%s'),", (const char* const)pvParameter); 
  char* parameters = query + occupied_query_bytes;
  while (1) {
    int r = analogRead(PIN);
    auto timestamp = RTC.getTime();
    snprintf(parameters, MAX_LENGTH_QUERY-occupied_query_bytes, "%d,%ld;", r, timestamp);
    if (openDb(&db) != SQLITE_OK) break;
    execDb(db, query);
    closeDb(db);
    vTaskDelay(5000);
  }
}

const char* const sI0_12 = "I0.12";
const char* const sI0_11 = "I0.11";
void setup() {
  Serial.begin(115200);
  SD.begin(13);

  if (!RTC.read()) {
    RTC.setTime(0);
  }
  
  sqlite3_initialize();

  xTaskCreate(read_analog<I0_12>, "TaskI0.12", 8192, (void*)sI0_12, 0, NULL);
  xTaskCreate(read_analog<I0_11>, "TaskI0.11", 8192, (void*)sI0_11, 0, NULL);
}

void loop() {}

Este programa crea dos tareas. Cada tarea es responsable de leer el valor de un pin analógico cada 5 segundos y almacenar las lecturas dentro de una base de datos SQLite. También utiliza la librería RTC para obtener las marcas de tiempo de cada lectura, de forma que puedas saber con certeza cuándo se ha leído el valor.