Went on a massive detour trying to get SQLite onto the ESP32

Anki allows you to export all your cards into a single .apkg file, which is essentially a zip file containing media files along with Anki’s SQLite database. I want it to be possible to import apkg files directly into flashpoint so I figured if I could get SQLite working on the XTeink X4 directly then this would be easy.

Issue #1 - I have no idea what I’m doing

I have never used PlatformIO before, so I wasn’t sure how to actually include libraries. Initially, I tried just adding #include <sqlite.h> where I needed it, but this failed to build so I installed SQLite onto my laptop which didn’t work either. I discovered that since the XTeink X4 uses an ESP32 I needed specific libraries for it — I found siara-cc/Sqlite3Esp32 and added it to the dependencies section in platformio.ini (PlatformIO’s config file). This brings us to Issue #2.

Issue #2 - The ESP32 is so small!

The ESP32 only has 16MiB of flash memory. This is enough to store around 48 novels, 4 photos, or 1/20th of a YouTube video*. This memory is typically used to store configuration, assets, and firmware. Here is the partition scheme used in CrossInk (the project I forked).

# Name,   Type, SubType, Offset,  Size, Flags
nvs,      data, nvs,     0x9000,  0x5000,   #    20KiB - For settings, like the WiFi password
otadata,  data, ota,     0xe000,  0x2000,   #     8KiB - Tracks OTA update progress
app0,     app,  ota_0,   0x10000, 0x640000, #  6.25MiB - First firmware partition
app1,     app,  ota_1,   0x650000,0x640000, #  6.25MiB - Second firmware partition
spiffs,   data, spiffs,  0xc90000,0x360000, # 3.375MiB - Filesystem for assets, such as those in the webserver
coredump, data, coredump,0xFF0000,0x10000,  #    64KiB - Crash reports for debugging

Overall, this reduces the space actually available for the firmware down to 6.25MiB. SQLite adds roughly 470KiB to the firmware image, bringing it beyond the 6.25MiB partition limit. To solve this I shrunk the spiffs partition, which allowed me to expand app0 and app1 to 7MiB and build an image with SQLite. However, I realised that I still couldn’t use SQLite yet.

Issue #3 - SQLite doesn’t understand the filesystem I’m using

Under many abstractions, it seems that CrossInk uses SdFat to manage the filesystem, rather than the simpler SD library. As I understand it, this means that files cannot be accessed using stdio.h commands like fopen() and instead must be accessed through the SdFat library. This means that for SQLite to work I would need to write a sort of ‘translation’ class which maps SQLite’s desired filesystem actions onto SdFat methods. This is called a VFS (Virtual File System), and looks very tedious to implement. In a last ditch effort, I tried to use the SD library and SdFat library simultaneously, which resulted in the whole device crashing whenever I opened my flashcards (pictured below) - this is not very useful. I’m sure I’m misunderstanding something and there’s probably an easier solution which works, but I’ve decided to try a different approach instead.

A different Approach

CrossInk already persists structured data in many places. For example, bookmarks appear to be stored in a binary file format using some sort of serialisation utility. I’ll look into this and probably implement my own storage solution which only stores the data I need rather than using an entire SQLite database.

In hindsight, SQLite was probably the wrong decision, but it led me to better understand PlatformIO, ESP32 partition tables, and CrossInk’s storage architecture.

Notes

Rough calculations:

• 75,000 word novels, 4.7 characters per word, one-byte characters
• 4MiB photos
• 10-minute 720p video, which is around 332MB according to this website