Support for FATFS

[jason7sc]

I have successfully ran this on an ESP32-WROVER (IDF v3.3.1) with a SPIFFS partition. However, it does not appear to work on a FAT partition using VFS with wear leveling. There are a number of runtime exceptions when trying to access the sqlite database file.

I was wondering if there is support or will be support for FATFS? What changes would need to be made for this to work properly?

FAT support would also enable encryption support (#1), since SPIFFS cannot be encrypted.

I was also able to pair this with fnc12/sqlite_orm (C++) to make an awesome embedded sqlite orm.

[siara-cc]

could you share sample code for FATFS?

[jason7sc]

Here is the code:

wl_handle_t wl_handle;
esp_vfs_fat_mount_config_t fatConfig = {
    .format_if_mount_failed = true,
    .max_files = 5,
    .allocation_unit_size = 4096
};

esp_err_t err = esp_vfs_fat_spiflash_mount("/storage", "storage", &fatConfig, &wl_handle);
if(err != ESP_OK){
    ESP_LOGE(TAG, "Failed to mount FATVFS");
}else{
    ESP_LOGI(TAG, "Mounted");
}

/***** SQL Operations ****
try {
    sqlite_initialize()
    sqlite3_open(filename, db);
    etc...
catch(...){
    print error
}
*/

esp_vfs_fat_spiflash_unmount("/storage", wl_handle);

Here is the output with file I/O debugging enabled:

I (54) [AppMain]: Mounted
esp32_FullPathname: /storage/test.sqlite
esp32_Open: 0o /storage/test.sqlite r
esp32_Access: /storage/test.sqlite 0 -1 0
esp32_Open: 1o /storage/test.sqlite w+
Error: unable to open database file: unable to open database file

Partition table:

# Name,         Type,   SubType,    Offset,     Size
nvs,            data,   nvs,        0x9000,     0x4000
otadata,        data,   ota,        0xd000,     0x2000
phy_init,       data,   phy,        0xf000,     0x1000
ota_0,          0,      ota_0,      0x10000,    0x200000
storage,        data,   fat,        0x210000,   0x100000

[Yardie-]

my guess here is something I noticed in the sqlite compile documentation

SQLITE_ENABLE_8_3_NAMES=<1 or 2>

If this C-preprocessor macro is defined, then extra code is included that allows SQLite to function on a filesystem that only support 8+3 filenames. If the value of this macro is 1, then the default behavior is to continue to use long filenames and to only use 8+3 filenames if the database connection is opened using URI filenames with the "8_3_names=1" query parameter. If the value of this macro is 2, then the use of 8+3 filenames becomes the default but may be disabled on using the 8_3_names=0 query parameter. 

thus if this directive was set to 1 the code could simply use the URI syntax in the open query setting names=0 for fat file systems. and omitting it or using a file name directly for spiffs file systems.

https://sqlite.org/uri.html

[rjjrbatarao]

@jason7sc How did you used the fnc12/sqlite_orm? do you have references how to port this to esp32? btw we have same problem and I solved mine using this directory /ffat/test.sqlite make sure to include "FFat.h" as well as selecting xmb app xmb fatfs in arduino partition scheme. You can also edit your existing code to esp_vfs_fat_spiflash_mount("/ffat", "ffat", &fatConfig, &wl_handle); that should work. Here is a sample void setup I modified from default sample of the sqlite3 arduino library you can try. const char *db_name = "/fuxion.db"; const char *sq_name = "/ffat/fuxion.db"; `void setup() {

Serial.begin(115200);

if defined(ESP32)

//if (FORMAT_FFAT) FFat.format(); //if (!SPIFFS.begin(FORMAT_SPIFFS_IF_FAILED)) if (!FFat.begin()) { Serial.println(F("Failed to mount file system")); return; }

elif defined(ESP8266)

system_update_cpu_freq(SYS_CPU_160MHZ); WiFiOff(); if (!SPIFFS.begin()) { Serial.println(F("Failed to mount file system")); return; } else { Serial.println(F("spiffs initialized")); }

endif

if (!FFat.exists(db_name)) { Serial.println(F("Database fuxion does not exist, please create")); } else { Serial.println(F("db exist")); }

if defined(ESP32)

File root = FFat.open("/"); if (!root) { Serial.println(F("- failed to open directory")); return; } root.close();

endif

// list SPIFFS contents

if defined(ESP32)

File file = root.openNextFile(); while (file) { if (file.isDirectory()) { Serial.print(" DIR : "); Serial.println(file.name()); } else { Serial.print(" FILE: "); Serial.print(file.name()); Serial.print("\tSIZE: "); Serial.println(file.size() / 1000); } file = root.openNextFile(); }

elif defined(ESP8266)

Serial.println(F("showing files")); Dir dir = SPIFFS.openDir(""); while (dir.next()) { String fileName = dir.fileName(); size_t fileSize = dir.fileSize(); Serial.printf_P(PSTR("FS File: %s, size: %ld\n"), fileName.c_str(), (long) fileSize); } Serial.printf("\n");

endif

sqlite3_initialize();

}`

[wuyuanyi135]

I am working on FATFS SQLite but it seems there is some issue of FATFS: https://github.com/espressif/esp-idf/issues/9660. This prevents the library from initializing.