Creating empty DB file from ESP32 itself

[simkard69]

Hello,

I was wondering if there is any way to create empty DB files from the ESP32 itself ? A workaround would be to have a default DB file stored somewhere on the SD card, copying it, and using it.

Thanks

[Fischkopppp]

Hi simkard69,

I am no sqlite expert or c++ expert. But I believe you only need to create an empty file with a ".db" at the end. You can do this with the SD or LittleFS or SPIFF library. I tested this and it worked for me. I hope this will help you:

    //create variable and create empty file in next step: 
    File myFile; 
    myFile = SD.open("/sd/movies2000.db", FILE_WRITE); 
    // close the file:
    myFile.close(); 
    // Test if the file exist:
    if (SD.exists("/sd/movies2000.db")) {
      Serial.println("/sd/movies2000.db exists.");
    } else {
      Serial.println("/sd/movies2000.db doesn't exist.");
    }
    // Open the DB with sqlite:
     if (openDb("/sd/movies2000.db", &db1))
     return;

[siara-cc]

@simkard69 @Fischkopppp I guess the purpose is to fix the page size. So I think having an empty database pre-created and copied whenever needed is the way to go.

[simkard69]

Hello @siara-cc , Thanks for your feedback, I was on pause with this project. Anyway, my goal is basically to store/datalog sensor values at various frequencies on SD card. I though of using "classic/basic" CSV files at first, which are pretty easy to use as this kind of file type doesn't require nothing in new CSV files to store data inside. Fact is that SQLite files seem/tend to be very different as they act as DB files and even when they come empty, they still contain DB/table structure(s).

So from these perspectives, I'm wondering what is the best option ... should I :

1. create a new SQLite file for each data-set
2. create a new table for each data-set in the same SQLite file (finally, maybe having 1 SQLite file is easier ?)

Fact is I can have big datasets of maybe around 150k lines with maybe 30-50 columns (depending on the actual needs/use cases). How does this library acts with as much data as let say 50 tables of 150k rows and max. 50 columns ?

What is the best option from your perspective ?

Thanks