I have a project where I would like to write a database on an SD Card
connected to an Arduino Nano.
I know I could write a text file on an SD Card but I would like to know if it's possible to write a structured database on an SD Card instead of just a text file.
Is there a library that can do that ?
That would allow me to run queries much faster and easier than with a text file.
For instance, a lookup search on a text file would have to be sequential whereas on a structure database that would be much easier with a SQL query. Same things with deleting records.
I could copy the text file over wifi or Bluetooth or USB connection
and then convert it to a database. But I'm not keen to do that because I would end up with duplicates of the database in different locations instead of managing the database that is located on the SD Card.
Study database design, how to design the datasets in order to search and find objects. Index files pointing into the mass data could do a lot.
In order to be able to search for data the data needs to be organised to fit the search engine. Random data in the base calls for sequentially reading the entire database and it takes time, lots of time.
sounds as if you would like to use SQL statements on your Arduino ... ?
SQL Databases use tables, queries and transactions to store, retrieve, filter and combine data ... This is not an application for microcontrollers but PCs or Servers.
I am with @Railroader. If you can come up with a reasonable data structure you can speed up finding data on SD card with the DB that @mrburnette mentioned in post #2 on Github, possibly with the assistance of lookup tables!
But it will not be a SQL DB, only a tool to handle a hugh amount of data of the same structure.
A little more information on the data might also be helpful ...
ec2021
P.S.: If you really require SQL you could of course use an ESP32. There is actually a lib for sqlite:
"Structured" is an interesting word ... like, "pre-sorted" data? Then, yes, you can and I did an ESP8266 SPIFFS (could be SD) that put the entire OUI database (about 680 KB) into the file system for querry. The indexing scheme is done at power-on, there after querries resolve in < 1 second.
/*
Arduino 1.6.13 on Linux Mint 17.3 (1.8.1 tested)
Open Source demo source by M. Ray Burnette (ray.burne) @ https://www.hackster.io/rayburne/projects
*/
#define maxBlks 7680 // determined by direct analysis
#include "FS.h"
#include <ESP8266WiFi.h>
const char* ssid = "ABCDEFG";
const char* password = "010203040506";
unsigned char ASCII2HEX( char c) // http://stackoverflow.com/questions/2499000/how-to-convert-an-ascii-hex-character-to-its-value-0-15
{
return ( c <= '9' ) ? ( c - '0' ) : ( (c | '\x60') - 'a' + 10 ) ;
}
struct OUI { // Custom data structure to hold linked list of O1::02 file pointers
unsigned char c_Octet1 ;
unsigned char c_Octet2 ;
uint16_t f_offset ; // linked list: beginning of current record TO beginning of next line /n included
} ;
OUI oui_array[maxBlks] ; // define the array type and size
String OUI_matchup ;
char cArrayOUI[7] ;
char currentCharacter ;
char * pEnd ;
unsigned char cOctet1 ;
unsigned char cOctet2 ;
unsigned char cOctet3 ;
unsigned long fileByteOffset = 0 ;
unsigned long iSum = 0 ;
unsigned long previousPosition = 0 ;
unsigned long currentPosition = 0 ;
unsigned long timeMarker ;
uint16_t iRecIdx = 0 ;
uint8_t OUI_match_Index = 0 ;
uint8_t io1 ;
uint8_t io2 ;
uint8_t io3 ;
bool ok ;
bool lFlag = true ;
bool lFound = true ;;
File FileRead ;
WiFiServer server(80);
void setup() {
Serial.begin(115200) ;
delay(100) ;
Serial.print("\n\r ***** Starting Up *****\r\n") ;
Serial.print("Connecting to ");
Serial.println(ssid);
WiFi.begin(ssid, password);
while (WiFi.status() != WL_CONNECTED) {
delay(1000);
Serial.print(".");
}
Serial.println("");
Serial.println("WiFi connected");
server.begin(); // Start the server
Serial.println("Server started");
Serial.println(WiFi.localIP()); // Print the IP address
OUI_matchup.reserve(8) ; // xx-xx-xx
ok = SPIFFS.begin() ; // https://github.com/esp8266/Arduino/blob/master/doc/filesystem.md
oui_array[0].c_Octet1 = 0x0000 ; // initialize array as a placeholder
oui_array[0].f_offset = 0 ;
previousPosition = 0 ; // Walking var for varying length descriptions field
iRecIdx = 1 ; // Beginning array indexpreviousPosition
Serial.print("\r\nBegin reading OUI.txt located in SPIFFS and building array... about 50 seconds\r\n") ;
timeMarker = millis() + 5000;
FileRead = SPIFFS.open("/oui.txt", "r") ;
FileRead.seek(fileByteOffset, SeekSet) ; // Seek to record
while (ok && FileRead.available() > 0) // While not EOF
{
currentCharacter = FileRead.read() ;
if (OUI_match_Index < 8 ) { // gather the first 8 characters of a line as: xx-xx-xx
cArrayOUI[OUI_match_Index] = currentCharacter ;
++OUI_match_Index ;
}
if (currentCharacter == 9) { // TAB ?
cOctet1 = (ASCII2HEX(cArrayOUI[1])) + (ASCII2HEX(cArrayOUI[0]) * 16) ;
cOctet2 = (ASCII2HEX(cArrayOUI[4])) + (ASCII2HEX(cArrayOUI[3]) * 16) ;
// the magic in the test below is only possible because the imported OUI list is in order by 1st::2nd octet
if ((cOctet2 > oui_array[iRecIdx - 1].c_Octet2) || ( cOctet1 > oui_array[iRecIdx - 1].c_Octet1))
{
oui_array[iRecIdx].c_Octet1 = cOctet1 ;
oui_array[iRecIdx].c_Octet2 = cOctet2 ; // iRecIdx points to last-built array elements
oui_array[iRecIdx].f_offset = (currentPosition - previousPosition) ;
previousPosition = currentPosition ;
if( millis() > timeMarker ) {
Serial.print(".") ; // Let user know something is happening
timeMarker = millis() + 5000 ;
}
OUI_match_Index = 0 ; // prepare for next OUI parsing
OUI_matchup = "" ; // null out the capture String;
++iRecIdx ; // Block count
}
}
if (currentCharacter == 10) // End-of-line ?
{
// previousPosition = currentPosition ; // Delta
currentPosition = FileRead.position() ; // DO NOT nest this function
OUI_match_Index = 0 ; // prepare for next OUI parsing
OUI_matchup = "" ; // null out the capture String;
}
} // EOF
Serial.print( "\n\rReached End Of File\n\r") ;
}
The reason I was looking to create a database
was to have a timestamp record sorted out chronologically.
So I could plot a graph later.
The structure of the data is actually simple:
timestamp (that would serve as primary key too) + value from sensor 1 + value from sensor 2 + value from sensor 3
and that's pretty much it, really.
This part is easy because the record would be taken
chronologically anyway.
But I want to be able to add records manually later.
Kind of like a manual adjustment of the data collected.
And adding observation too in form of notes.
Thinking about it now, I could actually use a text file (instead of a DB)
and use a temp file to sort out the data including the newly
added manual record + notes.
But that would mean going through the text file sequentially
from top to bottom every time there is a record added or a note.
Same thing when deleting a record, I would have to recreate
the whole text file.
I'm probably overthinking but would a database be faster?
I was under the impression I could create a SQL DB
and then use SQL queries.
If I remember correctly SQL uses Dichotomic search
and if I'm not completely wrong, this principle uses sequential search within indexes
with an efficient strategy of divide and conquer.
But at least, you don't have to deal with that, so that's one piece of code less to write.
Just out of curiosity, why is it possible to use SQL on an ESP32 and not on an Arduino ?
Other than the fact that the sqlite library is already available for the ESP32.
It is because it's got a faster microcontroller than the Arduino ?
Yes, you got it right.
I was really just interested in having pre-sorted data.
But I want to add data manually too later.
And I want to be able to delete some records too.
Usually when talking about "Arduino" it is about relatively low clocked controllers with with very limited memory compared to a PC.
Therefore a lot of functionality we know from PCs are not available in microcontrollers. They are made for different purposes and do have their specific advantages over "computers".
The ESP32 is a very powerful microcontroller compared to an Uno or Nano and the like. Just look up the datasheets and compare memory and clock speed.
Today there is no such thing as "an Arduino" anymore as there are plenty of boards using quite different controllers/processors . So it is not unlikely that one if the more powerful can also handle SQLite. A Nano is definitely not the choice. It can write data in a format that a SQL application can read it, but not more...
Would be a nice attempt to write a SQL application for it making intensive use of the SD Card to read and store partial data and even use dynamically loaded code... But it would quite likely be pretty slow...
Microcontrollers are excellent for data capture and real-time manipulation of the value(s); but, post-processing is best for a capable PC with an OS and highly versitle tools: Access, Excel, or real databases.
Why copy? Just move the database elements as JSON and let a capable product such as NodeRED digest the serial stream (serial-USB or BT-serial.)
No sneaker-Net from SD-card transfer, no silliness from attempting to run everything on a microcontroller. And many associated benefits from NodeRED including running on multiple platforms, real webserver, and data graphing and analysis tools. Node-RED (nodered.org)
I implemented on a RPi for one project but found that an old Intel HP netbook running the x86 Linux provided ample horsepower on a PC destined for the recycle-center.
But it would quite likely be pretty slow...