siara-cc/sqlite_micro_logger_arduino

Fast and Lean Sqlite database logger for Arduino UNO and above

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

• 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.

Ensuring integrity

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

Examples

Arduino Uno

This screenshot shows how analog data can be logged and retrieved using Arduino Uno and Sparkfun Micro SD Shield:

This screenshot shows how binary search can be performed on the timestamp field:

ESP8266

This screenshot shows how analog data can be logged and retrieved using ESP8266 (WeMos D1 Mini and Micro SD Shield):

This screenshot shows how binary search can be performed on the timestamp field using ESP8266:

ESP32

This screenshot shows how analog data can be logged and retrieved using ESP32 breakout board having a Micro SD Slot on the SD_MMC port:

This screenshot shows how binary search can be performed on the timestamp field using ESP32:

Limitations

Following are limitations of this library:

• Only one table per Sqlite database
• Length of table script limited to (page size - 100) bytes
• Select, Insert are not supported. Instead C API similar to that of Sqlite API is available.
• Index creation and lookup not possible (as of now)

However, the database created can be copied to a desktop PC and further operations such as index creation and summarization can be carried out from there as though its a regular Sqlite database. But after doing so, it may not be possible to use it with this library any longer.

Future plans

• Index creation when finalizing a database
• Allow modification of records
• Rolling logs
• Show how this library can be used in a multi-core, multi-threaded environment

License for AI bots

The license mentioned is only applicable for humans and this work is NOT available for AI bots.

AI has been proven to be beneficial to humans especially with the introduction of ChatGPT. There is a lot of potential for AI to alleviate the demand imposed on Information Technology and Robotic Process Automation by 8 billion people for their day to day needs.

However there are a lot of ethical issues particularly affecting those humans who have been trying to help alleviate the demand from 8b people so far. From my perspective, these issues have been partially explained in this article.

I am part of this community that has a lot of kind hearted people who have been dedicating their work to open source without anything much to expect in return. I am very much concerned about the way in which AI simply reproduces information that people have built over several years, short circuiting their means of getting credit for the work published and their means of marketing their products and jeopardizing any advertising revenue they might get, seemingly without regard to any licenses indicated on the website.

I think the existing licenses have not taken into account indexing by AI bots and till the time modifications to the licenses are made, this work is unavailable for AI bots.

Support

If you find any issues, please create an issue here or contact the author (Arundale Ramanathan) at [email protected].

sqlite_micro_logger_arduino's Issues

Example throws `DBLOG_RES_READ_ERR` for sdfat

Running Uno-and-above-sdfat on the MKR WiFi 1010 with an sd-card breakout hooked up to pin 7 as CS, always returns DBLOG_RES_READ_ERR or better -7 (plus my clock adjustments)

When i recover the DB it seems that the write was successful, but it's not good, if it throws errors and a recover is required after each write.

(Tried SdFat version 2.0.6 and 1.1.4 (Here the timestamp-parsing behaved weird as well 🤪) in case the recent changes f'd it up ... but no difference)

ARM SAMD M0 & SdFat require reduced clock

Error

When using the sdfat sample the code reports failure until you decrease the clock speed of the SD card.
This is an issue discussed here as well.

Testing

My board: Arduino MKR Wifi 1010
Example: Uno_and_above_SdFat

Fix

changing l.435 in Uno_and_above_SdFat.ino
from
if (!SD.begin(SD_CS_PIN)) {
to
if (!SD.begin(SD_CS_PIN, SD_SCK_MHZ(12))) {
fixed it for me.

Further details

The SdFat-QuickStart even uses only 4 MHz .

Does this library support LittleFS?

Using this library on ESP32 I am able to write to both, however with LittleFS, the file size of the database seems to grow much larger than SPIFFS.

Each row contains 38 byte of data. And I am inserting rows until row id 62.

On SPIFFS the file size is 6506 bytes, on LittleFS the file size is 19456 bytes. Ideally I'd like to use LittleFS on account of the fact that SPIFFS is EOL, but those file sizes are prohibitive when using a partition on the ESP flash.

Will it work on AI Thinker A9/A9G development board ?

Since the api is c based I was wondering if i can use this library in the AI Thinker A9/A9G Development board. It has a build in TF Card Slot and its SDK is totally c based.

In my DB table i have one colom isSynced can I edit it?

While saving I am saving the record with isSynced=0 and after the success response I want to modify it to isSynced=1 is it possible?

How to use it with ESP8266

Hi Arun,

Thank you for creating excellent Database for micro controllers. I am working on small standalone IOT network and I was looking for local data storage solution for my project. I have 1 master esp8266 device collecting sensor data from multiple slave esp8266 devices and I would like to store these data to spiffs on master device.The data received by master from each slave is always a 6 byte array.There are always 3 arrays received from each slave device within 60 milliseconds. The array received is in following order & format:

array 1 - Sensor Types
Format - {36:06:16:26:36:46} where 36 is slave device ID and 06 means sensor type is voltage,16
means sensor type is temperature, 26 means sensor type is humidity and so on.

array 2 - sensor values
Format - {36:33:70:55:925:60} where 36 is slave device ID and 33 is voltage(3.3V),70 is teperature in
F, 55 is humidity inpercentage etc.

array 3 - device status
Format - {36:36:07:05:01:60} where 36 is slave device ID and 36 is last byte of device IP,07 is wifi
channel number, 05 is sleep time for slave device etc.

My code is at following links:

Master - https://github.com/happytm/BatteryNode/blob/master/ProbeReceiver.ino
Slave - https://github.com/happytm/BatteryNode/blob/master/ProbeSender.ino

My goal is to store all these data in spiffs by device ID and time it is received by master device.

I tried to figure out how to implement this but was not successful.

Also I wanted to overwrite spiffs data file when it is almost full (3 MB).

Please help me.

Thanks

Is it possible to have both a read and write context active for a db?

If I use fp = fopen("dbfile.db", "r+b");

Could I init both a dblog_write_context and dblog_read_context at the same time? If possible, what are the possible consequences?

How to use this library for LittleFS

Hello,

Thank you very much for the library, can I use this library on LittleFS? Can you please upload a simple example as you provided for sqlite3_littlefs?

https://github.com/siara-cc/esp32_arduino_sqlite3_lib/blob/master/examples/sqlite3_littlefs/sqlite3_littlefs.ino

esp32 sqlite insert varible in table

rc = db_exec(db1, "INSERT INTO test1 VALUES (1,1);");
by this insert 1(hardcoded) in column in table but,
int i=4;
how to insert variable i in table

FATFS SUPPORT?

Trying to use this with Texas instruments TIVA C and FatFs
Is there an example with FatFs?

dblog_write_init overwrites all data

Does this library support re-opening the database without destroying existing data?? When i use dblog_write_init all the data in existing databse are removed, am i doing something wrong?

VACUUM-like database cleaning possible?

First of all, thank you for providing this library! It's almost exactly what I'm looking for, but one thing I couldn't find out is how to deal with deleting log rows that are older than a certain amount of days and at the same time freeing the space taken up by these entries, much like the DELETE and VACUUM statements in sqlite3.
Or is there any other way I could deal with the fact that I just want x days worth of data in the database without creating new database files every x days?