Log sensor data into SQLite databases faster than the SQLite library. Works with SPIFFS too.

[arundale]

https://github.com/siara-cc/sqlite_micro_logger_arduino

Sqlite µLogger for Arduino

esp32_analog_scr.png (345.84 KiB) Viewed 5579 times

Sqlite µLogger is a Fast and Lean database logger that can log data into Sqlite databases even with SRAM as low as 2kb as in an Arduino Uno. The source code can be ported to use with any Microcontroller having at least 2kb RAM.

The library can also be used to retrieve logged data. Apart from retrieving row by row, it can also locate a record using Row ID. If timestamp is logged, any record can be looked up using binary search on timestamp in O(log n) time, which is not possible when logging to text files or using any other logger (even desktop loggers).

This repo is an Arduino library that can work with Arduino Uno board or any Arduino board that has minimum 2kb RAM and a SD Shield attached.

It has been tested with Arduino Uno with SparkFun MicroSD Shield, WeMos ESP8266 D1 Mini with WeMos MicroSD Shield and ESP32 SD_MMC breakout board.

banner.png (572.03 KiB) Viewed 5579 times

Features

• Low Memory requirement: page_size + some stack

• Can log using Arduino UNO (2kb RAM) with 512 bytes page size

• Can do quick binary search on RowID or Timestamp without any index in logarithmic time

• Recovery possible in case of power failure

• Rolling logs are possible (not implemented yet)

• Can use any media using any IO library/API or even network filesystem

• DMA writes possible (not shown)

• Virtually any board and any media can be used as IO is done through callback functions.

Getting started

The example Uno_and_above shows how data read from Analog pins can be stored along with Timestamp into Sqlite database and retrieved by RowId.

Records can also be located using Timestamp in logarithmic time by doing a Binary Search on the data logged. This is not possible using conventional loggers.

For example, locating any record in a 70 MB db having 1 million records on Arduino UNO with SparkFun microSD Shield took only 1.6 seconds.

The examples ESP8266_Console and ESP32_Console can be used to log and retrieve from ESP8266 and ESP32 boards respectively on Micro SD and SPIFFS filesystems.

API

For finding out how the logger works and a complete description of API visit Sqlite Micro Logger C Library (https://github.com/siara-cc/sqlite_micro_logger_c).

Ensuring integrity

If there is power failure during logging, the data can be recovered using Recover database option in the menu.