Log sensor data into SQLite databases faster than the SQLite library. Works with SPIFFS too.
Posted: Mon Jan 06, 2020 3:41 am
https://github.com/siara-cc/sqlite_micro_logger_arduino
Sqlite µLogger for Arduino
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.
Features
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.
Sqlite µLogger for Arduino
- esp32_analog_scr.png (345.84 KiB) Viewed 5582 times
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 5582 times
- 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.
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.