Sqlite3 Library for ESP32 Arduino Core

SparkFun ESP32 Thing

<h3 >Sqlite3 Arduino Library for ESP32</h3>This library enables access to SQLite database files from SPIFFS or SD cards through ESP32 SoC. Given below is a picture of a board that has a ready-made Micro SD slot:<figure><img src=https://projects.arduinocontent.cc/53cbef65-7fd1-449c-997c-fcdaea88b83d.png /><figcaption> </figcaption></figure>Also shown below is the wiring between ESP-WROOM-32 breakout board and Micro SD Shield (using SPI mode - see example sqlite3_sdspi):<figure><img src=https://projects.arduinocontent.cc/45fa2e2a-a1e4-49ba-86ce-8f5736bb0083.png /><figcaption> </figcaption></figure><h3 >Usage</h3>Sqlite3 C API such as <code >sqlite3_open</code> can be directly invoked. Before calling please invoke:<pre ><code >SD_MMC.begin(); // for Cards attached to the High speed 4-bit port SPI.begin(); SD.begin(); // for Cards attached to the SPI bus SPIFFS.begin(); // For SPIFFS </code></pre>as appropriate.The ESP32 Arduino core SDK has an excellent VFS layer. Even multiple cards can be supported on the SPI bus by specifying the pin number and mount point using the <code >begin()</code> method.The default mount points are:<pre ><code >'/sdcard' // for SD_MMC '/sd' // for SD on SPI '/spiffs' // For SPIFFS </code></pre>and the filenames are to be prefixed with these paths in the <code >sqlite3_open()</code> function (such as <code >sqlite3_open(&#34;/spiffs/my.db&#34;)</code>).Please see the examples for full illustration of usage for the different file systems. The sample databases given (under <code >examples/sqlite3_sdmmc/data</code> folder) need to be copied to the Micro SD card root folder before the SD examples can be used. Please see the comments section of the example.<h3 >Wiring</h3>While there is no wiring needed for SPIFFS, for attaching cards to SPI bus, please use the following connections:<pre ><code >* SD Card | ESP32 * DAT2 (1) - * DAT3 (2) SS (D5) * CMD (3) MOSI (D23) * VDD (4) 3.3V * CLK (5) SCK (D19) * VSS (6) GND * DAT0 (7) MISO (D18) * DAT1 (8) - </code></pre>And for SD card attached to High-speed 4-bit SD_MMC port, use:<pre ><code >* SD Card | ESP32 * DAT2 (1) D12 * DAT3 (2) D13 * CMD (3) D15 * VDD (4) 3.3V * CLK (5) D14 * VSS (6) GND * DAT0 (7) D2 * DAT1 (8) D4 </code></pre>If you are using a board such as shown in the picture above, this wiring is ready-made.<h3 >Installation</h3>Please download this library, unzip it to the libraries folder of your ESP32 sdk location. The location varies according to your OS. For example, it is usually found in the following locations:<pre ><code >Windows: C:\\Users\\(username)\\AppData\\Local\\Arduino15 Linux: /home/&lt;username&gt;/.arduino15 MacOS: /home/&lt;username&gt;/Library/Arduino15 </code></pre>Under Arduino15 folder please navigate to <code >packages/esp32/hardware/esp32/&lt;version&gt;/libraries</code>If you do not have the ESP32 sdk for Arduino, please see <a href="https://github.com/espressif/arduino-esp32">https://github.com/espressif/arduino-esp32 </a>for installing it.Open the examples from Arduino IDE, select board, port, upload and enjoy!! <h3 >Dependencies / Prerequisites</h3>No dependencies except for the Arduino SDK. The Sqlite3 code is included with the library.<h3 >Limitations on ESP32</h3><ul ><li >No serious limitations, except its a bit slow on large datasets. It takes around 700 ms to retrieve from a dataset containing 10 million rows, even using the index.</li></ul><h3 >Limitations of This Library</h3><ul ><li >Locking is not implemented. So it cannot be reliably used in a multi-threaded / multi-core code set, except for read-only operations.</li></ul><ul ><li >As of now many features of Sqlite3 have been omitted, except for basic table and index operations. These are expected to be made available shortly.</li></ul><h3 >Limitations of Flash Memory</h3>Any Flash memory such as those available on SPIFFS or Micro SD cards have limitation on number of writes / erase per sector. Usually the limitation is 10000 writes or 100000 writes (on the same sector). Although ESP32 supports wear-levelling, this is to be kept in mind before venturing into write-intensive database projects. There is no limitation on reading from Flash.<h3 >Acknowledgements</h3><ul ><li >This library was developed based on NodeMCU module developed by <a href="https://github.com/luizfeliperj">Luiz Felipe Silva.</a> The documentation can be found <a href="https://nodemcu.readthedocs.io/en/master/en/modules/sqlite3/">here.</a></li></ul><ul ><li >The census2000 and baby names databases were taken from here: <a href="http://2016.padjo.org/tutorials/sqlite-data-starterpacks/">http://2016.padjo.org/tutorials/sqlite-data-starterpacks/.</a> But no license information is available.</li></ul><ul ><li >The mdr512.db (Million Domain Rank database) was created with data from <a href="https://majestic.com/reports/majestic-million">The Majestic Million </a>and is provided under CC 3.0 Attribution license.</li></ul><ul ><li >The <a href="https://github.com/espressif/arduino-esp32">ESP32 core for Arduino</a></li></ul><ul ><li ><a href="https://arduino.cc/">The Arduino platform</a></li></ul><h3 >Screenshot (Output of Micro SD Example)</h3><figure><img src=https://projects.arduinocontent.cc/1be0b2d3-300f-4f4a-937b-ea010e7357d6.png /><figcaption> </figcaption></figure><h3 >Issues</h3>Please contact the author (Arundale Ramanathan) at <a href="http://arun@siara.cc/">arun@siara.cc</a> if you find any problem (or create issue on GitHub).

Sqlite3 Library for ESP32 Arduino Core

Retrieve and modify SQLite files. Retrieving from an indexed database table having 10 million rows takes less than a second.