SQLite Library (VFS) for Teensy 4.1

Sam Halvoe

Sam Halvoe

Active member
Hello everyone,

I started working on a SQLite Library for Teensy 4.1 --> see Platform IO Project on GitHub.
I took the test_demovfs.c from SQLite Website and used the Teensy SdFat library to implement the VFS.
I am using the built in SD card slot of Teensy 4.1. VFS stands for Virtual File System, it's the OS Interface,
which is used by SQLite to interact with the underlying file system and/or os. (see SQLite Website)
(I also looked at the ESP32 version from this repository, to learn some things: see on GitHub)

It's not polished and certaintly not finished. But a simple test succeded, running the main.cpp file of the project.
I am open for all questions and suggestion.
 
I would strongly recommend using the FS Interface instead. This is an existing problem with quite a few projects; they use the SdFat library directly rather than the generic filesystem interface and it prevents them from being usable with other devices/filesystem types.
 
Did not know about FS Interface. Would I have to implement FileImpl myself or exists an implementation for SdFat/SD cards?
 
You don't have to implement FileImpl. That is the interface provided by File objects, which are what you want to use instead of FsBaseFile. Generally you don't create them directly though, but rather get them by calling open on the FS object (which for SdFat/SD cards would be the "SDClass SD" global provided by the SD library).
 
Unfortunately, the current File design doesn’t return or let you access filesystem errors. This is because read() and write() functions return size_t and there is no “get last error” call.

For example, all LittleFS errors are actually converted to zero.

When I use the File interface, I make modifications so that these functions return ssize_t instead. This has helped me track down things like “file is full” errors.
 
shawn said:
When I use the File interface, I make modifications so that these functions return ssize_t instead. This has helped me track down things like “file is full” errors.
Click to expand...
Isn't that more a deficiency of the individual filesystem libraries (or at least their wrappers) not making use of errno?
 
Would it be a good idea to provide a mechanism for error handling with a callback that converts error codes of the used filesystem library to error codes that are understood by my library or by SQLite?
I could provide default callbacks for some libraries that implement the FS interface. In addition users could provide their own callbacks (when there is no default callback).
 
jmarsh said:
Isn't that more a deficiency of the individual filesystem libraries (or at least their wrappers) not making use of errno?
Click to expand...
Yes, that’s one way of looking at it.

The return value from write() is easy enough to cast, but read() and many of the bool-returning functions destroy the value. This is in LittleFS.h.

One of these days, I should submit a PR to at least set errno in there, as one possibility.

Another is some sort of “get last error” function in File or FS.
 
Last edited:
jmarsh said:
Isn't that more a deficiency of the individual filesystem libraries (or at least their wrappers) not making use of errno?
Click to expand...

shawn said:
Yes, that’s one way of looking at it.

The return value from write() is easy enough to cast, but read() and many of the bool-returning functions destroy the value. This is in LittleFS.h.

One of these days, I should submit a PR to at least set errno in there, as one possibility.

Another is some sort of “get last error” function in File or FS.
Click to expand...

I just made a PR to add setting errno to LittleFS:
github.com

Set errno for low-level negative returns by ssilverman · Pull Request #58 · PaulStoffregen/LittleFS

This improves the usability of the FS adapter by enabling the ability to get specific errors.
github.com github.com

Comments and suggestions welcome.
 
Sam Halvoe said:
I switched in my Library to FS and made SdFat an optional dependency.
Thank you for your replies.
Click to expand...
Do you have an example sketch for this?
In test_main.cpp you are still referencing the SD object in the begin function T41SQLite::getInstance().begin(&SD);
Perhaps just updating the library would be helpful :)
 
I want to update the library and add an example sketch. But unfortunately I don't have time for that at the moment.
 
@Sam Halvoe quick question
Is there a way to tell SQlite to allocate memory in extmem?
Specifically when it needs to load a rather large blob from a table.
 
At the moment it is not possible. But I have planned to add this feature.
Maybe I will have time to look into this. Some day I want to explore SQLite on Teensy more and refactor/rewrite my SQLite port. Then I will add extmen support.
 
I'm hitting a bit of a snag with this
Only way to overcome it is to move the heap to extemem. But that is causing some other issues for me in other parts of my app
 
Looks like we have to supply this struct with "extmem functions" and set it as the current allocation interface for SQLite. xMalloc, xFree and xRealloc should be set to the extmem variants, but I do not know to what we should set the other members of the struct (allocation interface).
 
Last edited:
This might work?

Code: 
#include <sqlite3.h>

// Teensy 4.1 EXTMEM functions (from smalloc library)
// These are already available in the Teensy core
extern "C" {
    void *sm_malloc(size_t size);
    void *sm_calloc(size_t nmemb, size_t size);
    void *sm_realloc(void *ptr, size_t size);
    void sm_free(void *ptr);
    size_t sm_malloc_usable_size(void *ptr);
}

// SQLite malloc wrapper for EXTMEM
static void* extmem_malloc(int size) {
    if (size <= 0) return NULL;
    return sm_malloc((size_t)size);
}

// SQLite free wrapper for EXTMEM
static void extmem_free(void* ptr) {
    if (ptr) sm_free(ptr);
}

// SQLite realloc wrapper for EXTMEM
static void* extmem_realloc(void* ptr, int new_size) {
    if (new_size <= 0) {
        sm_free(ptr);
        return NULL;
    }
    return sm_realloc(ptr, (size_t)new_size);
}

// Return the size of an allocation
static int extmem_size(void* ptr) {
    if (!ptr) return 0;
    return (int)sm_malloc_usable_size(ptr);
}

// Round up request size to allocation size
// smalloc handles alignment internally, so just return the size
static int extmem_roundup(int size) {
    return size;
}

// Initialize the memory allocator
static int extmem_init(void* pAppData) {
    // smalloc is already initialized by Teensy
    return SQLITE_OK;
}

// Shutdown the memory allocator
static void extmem_shutdown(void* pAppData) {
    // Nothing to do - smalloc manages itself
}

// SQLite memory methods structure
static sqlite3_mem_methods extmem_methods = {
    extmem_malloc,
    extmem_free,
    extmem_realloc,
    extmem_size,
    extmem_roundup,
    extmem_init,
    extmem_shutdown,
    NULL  // pAppData
};

// Function to configure SQLite to use EXTMEM via smalloc
int sqlite3_use_extmem() {
    return sqlite3_config(SQLITE_CONFIG_MALLOC, &extmem_methods);
}

I generated it with Claude4.5
 
That is my first attempt, though not tested.
sqlite3_use_extmem() must be called before T41SQLite::getInstance().begin(...)! (If this works, I will integrate sqlite3_use_extmem() as opt-in into begin().)

C++: 
#include "Arduino.h" // for: Teensy extmem functions

#include "sqlite3.h" // for: SQLite related stuff

#include "smalloc.h" // for: size_t sm_szalloc(const void *); /* get size of allocation */

// SQLite malloc wrapper for EXTMEM
static void* sqlite3_extmem_malloc(int size) {
  return extmem_malloc(size);
}

// SQLite free wrapper for EXTMEM
static void sqlite3_extmem_free(void* ptr) {
  extmem_free(ptr);
}

// SQLite realloc wrapper for EXTMEM
static void* sqlite3_extmem_realloc(void* ptr, int new_size) {
  return extmem_realloc(ptr, new_size);
}

// Return the size of an allocation
static int sqlite3_extmem_size(void* ptr) {
  return static_cast<int>(sm_szalloc(ptr));
}

// Round up request size to allocation size
static int sqlite3_extmem_roundup(int size) {
    return size;
}

// Initialize the memory allocator
static int sqlite3_extmem_init(void* pAppData) {
    // is already initialized by Teensy
    return SQLITE_OK;
}

// Shutdown the memory allocator
static void sqlite3_extmem_shutdown(void* pAppData) {
    // Nothing to do - managed by Teensy
}

// SQLite memory methods structure
static sqlite3_mem_methods sqlite3_extmem_methods = {
    sqlite3_extmem_malloc,
    sqlite3_extmem_free,
    sqlite3_extmem_realloc,
    sqlite3_extmem_size,
    sqlite3_extmem_roundup,
    sqlite3_extmem_init,
    sqlite3_extmem_shutdown,
    nullptr  // pAppData - not needed
};

// Function to configure SQLite to use EXTMEM allocators
int sqlite3_use_extmem() {
    return sqlite3_config(SQLITE_CONFIG_MALLOC, &sqlite3_extmem_methods);
}
 
Last edited:
I did not test either yours nor mine. Just used your code as starting point. Think your version won't work. My version could work, but I do not have a Teensy at hand at the moment. Maybe I can test it tomorrow.
 
I tried to implement EXTMEM support today. I can init SQLite, but got a crash at the first SQL statement execution. I will continue my effort at the weekend.
 
I created a new repository, because I switched (sometime ago) from Platform IO to Visual Micro, here is the repo.
I think I got SQLite to allocate on EXTMEM. By printing memory info (like this, see MemoryInfo.hpp/.cpp in repo) I see no allocations on the heap, but no allocations on the PSRAM either.

To use EXTMEM, you have to pass true as second argument to T41SQLite::getInstance().begin().
 
@Rezo I confirmed the allocations in PSRAM with sm_malloc_stats_pool(). Now it should be worth a try in your project. It would be great if it helps with your project. :)
 
You must log in or register to reply here.
Back
Top