Getting Started with NuttX: Running SQLite on ESP32

Overview: The last time I wrote an article about NuttX was in 2021. At that time, I had only been in contact with NuttX for a few months, and I had only written a simple guide on how to compile a “hello world” program.
Now, I have been involved in NuttX development for almost three years, and my understanding of NuttX has changed significantly during this time. I have also done some projects based on NuttX, so it seems this series can be updated again.

Introduction to NuttX

NuttX is a feature-rich real-time operating system (RTOS) that can run on small microcontrollers. It supports a complete multi-threading/multi-processing environment, file systems, and TCP/IP protocol stacks, and implements all commonly used POSIX APIs, allowing for a high degree of consistency with Linux development environments.

The following article will demonstrate the advantages of this interface compatibility. We will port a complete SQLite database to NuttX and run the SQLite3 command-line tool on ESP32 just like on Linux.

Quick Start

The development environment is primarily recommended to use Ubuntu 20.04 or above. Alternatively, WSL2 can be used for development.

Download the Code

This repository uses NuttX release-12.0 code and integrates commonly used repositories as git submodules to avoid downloading them each time you need to recompile.

git clone --recursive [email protected]:Gary-Hobson/NXOS.git

After downloading the code, you need to install some basic dependency libraries to compile.

Install Dependencies

sudo apt update
sudo apt install -y \
curl bison flex gettext texinfo libncurses5-dev libncursesw5-dev xxd \
gperf automake libtool pkg-config build-essential genromfs libx11-dev\
libgmp-dev libmpc-dev libmpfr-dev libisl-dev binutils-dev libelf-dev \
libexpat-dev gcc-multilib g++-multilib picocom u-boot-tools util-linux \
kconfig-frontends gcc-arm-none-eabi binutils-arm-none-eabi zlib1g-dev
pip install pyelftools cxxfilt

Compile the Code

NuttX has a powerful feature: it has a complete simulator support. If the code is hardware-independent, it can run on hardware without any modifications after successfully running on the simulator.

First, we will compile a simple demo on the simulator:

./nx.sh boards/sim/configs/hello V=1

This will compile the sim configuration for boards/sim/configs/hello. This configuration enables common functions, including networking in the sim environment, allowing most hardware-independent code to be compiled and run using this configuration.

Run the Code

After compilation, an executable file for NuttX will appear in the NuttX directory.

./nuttx/nuttx

NSH uses a shell that appears after NuttX starts, supporting basic commands. Type help in nsh to see all supported commands.

help: View all supported command-line commands, where Builtin Apps are executable tasks. For example, hello is an independent task that can run, which can be understood as a process. ps: NSH built-in command, view the information of running threads ls: NSH built-in command, view files in the file system demo: Start the demo process, executing the code in projects/demo/main.c poweroff: Exit the system

Running the result:

NuttShell (NSH)
nsh>
nsh> help
help usage:  help [-v] []
    .          break      dmesg      hexdump    mkfifo     readlink   true
    [          cat        echo       insmod     mkrd       rm         truncate
    ?          cd         env        kill       mount      rmdir      uname
    alias      cp         exec       losetup    mv         rmmod      umount
    unalias    cmp        exit       ln         nslookup   set        unset
    arp        dirname    false      ls         poweroff   sleep      uptime
    base64dec  date       free       lsmod      printf     source     usleep
    base64enc  dd         memdump    md5        ps         test       wget
    basename   df         help       mkdir      pwd        time       xd
Builtin Apps:
    demo        hello       ping        sh
    dumpstack   nsh         setlogmask  telnetd
nsh>
nsh> ping www.baidu.com
PING 110.242.68.4 56 bytes of data
56 bytes from 110.242.68.4: icmp_seq=0 time=19.9 ms
56 bytes from 110.242.68.4: icmp_seq=1 time=11.3 ms
56 bytes from 110.242.68.4: icmp_seq=2 time=22.7 ms
3 packets transmitted, 3 received, 0% packet loss, time 2322 ms
rtt min/avg/max/mdev = 11.300/17.966/22.700/4.853 ms
nsh>
nsh> ls
/: 
 bin/
 data/
 dev/
 etc/
 proc/
 tmp/
nsh>
nsh> demo
Hello, World!!
nsh>
nsh> poweroff

In the above running example, a demo process (pseudo-process, no independent address space) is executed and prints a hello world.

Project Structure

The code is located in projects/demo/main.c, and you can modify the code inside to perform different tasks. The project directory structure is as follows:

projects/demo
├── Kconfig
├── main.c
├── Make.defs
└── Makefile

Make.def and Makefile are files related to the compilation system. PROGNAME is the process name displayed in nsh, PRIORITY is the task priority (0 is the lowest priority, 255 is the highest priority, default is 100), and STACKSIZE is the process stack size, determined by the Kconfig configuration item.

cat project/demo/main.c
...
#include <stdio.h>
int main(int argc, char *argv[])
{
  printf("Hello, World!!\n");
  return 0;
}

# Demo project
...
PROGNAME  = demo
PRIORITY  = 100
STACKSIZE = CONFIG_PROJECT_DEMO_STACKSIZE
MAINSRC = main.c

Porting SQLite3

SQLite is a lightweight relational database that stores data in a single file in the file system. This time, we will demonstrate how to port SQLite to run on NuttX.

SQLite has good support for cross-platform, and the official provides a porting guide at https://www.SQLite.org/custombuild.html. Porting SQLite only requires implementing memory, file system, and lock-related support.

However, with NuttX, these tasks are unnecessary because its interface is completely consistent with Linux, and no modifications are needed for use.

Download SQLite Source Code

The default SQLite code has over 100 files. We use the merged files provided by SQLite at https://www.SQLite.org/getthecode.html, which only contains four files, merging all the code together (after merging, one file has over 200,000 lines, compiling is extremely slow…)

SQLite(master) ✗: tree -h
[4.0K]  .
├── [862K]  shell.c
├── [8.4M]  SQLite3.c
├── [ 37K]  SQLite3ext.h
└── [614K]  SQLite3.h

Then we will reference the demo project structure and add it to the compilation system:


├── Kconfig
├── Make.defs
├── Makefile
├── SQLite
├── SQLite_cfg.h

cat Makefile

CSRCS += SQLite/SQLite3.c
CFLAGS += -I${LIBRARIESDIR}/SQLite
CFLAGS += -D_HAVE_SQLITE_CONFIG_H

ifeq ($(CONFIG_TOOLS_SQLITE), y)
PROGNAME  = SQLite3
PRIORITY  = 100
STACKSIZE = ${CONFIG_TOOLS_SQLITE_STACKSIZE}
MAINSRC = SQLite/shell.c
endif

SQLite provides a command-line file and a library file. We will add these two to the compilation system and then compile. SQLite_cfg.h is the configuration file for SQLite, which can be modified to trim and select the required functions. Its optional configurations can refer to the compilation options.

Run SQLite

Similarly, we first run in sim, enabling the following two configurations based on board/sim/configs/hello, and then compile and run.

CONFIG_LIB_SQLITE=y
CONFIG_TOOLS_SQLITE=y

Then we can run the SQLite command-line tool in NuttX just like in Linux. After starting, it will enter the NSH Shell, and then we can enter sqlite3 to access the SQLite command-line tool.

NuttShell (NSH)
nsh>
nsh> uname -a
NuttX 0.0.0 6cdde97822 Oct  5 2023 17:11:53 xtensa ESP32-devkitc
nsh>
nsh> cd data
nsh> help
Builtin Apps:
    demo        hello       ping        sh          telnetd
    dumpstack   nsh         setlogmask  sqlite3
nsh>

We first enter the /data directory so that the subsequent operations can be persisted to the file system. In the sqlite3 command line, entering .help will show all supported commands.

Using the .open command to open a database file will create a new database file if it does not exist. After exiting the sqlite3 command-line tool, a test.db file will appear in the /data directory.

nsh> sqlite3
SQLite version 3.43.1 2023-09-11 12:01:27
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open test.db
sqlite>.quit
nsh> ls
/data:
 .
 ..
 test.db

We can enter the sqlite3 command-line tool again and create a table. Using the .tables command will show all tables, and we can see the COMPANY table we just created.

sqlite>  CREATE TABLE COMPANY(
(x1...>    ID INT PRIMARY KEY     NOT NULL,
(x1...>    NAME           TEXT    NOT NULL,
(x1...>    AGE            INT     NOT NULL,
(x1...>    ADDRESS        CHAR(50),
(x1...>    SALARY         REAL
(x1...> );
sqlite>
sqlite> .tables
COMPANY
sqlite>

Next, we can insert some data and use the SELECT command to view the data.

sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   ...> VALUES (1, 'Paul', 32, 'California', 20000.00 );
sqlite>
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
sqlite>
sqlite>
sqlite> .header on
sqlite> .mode column
sqlite> SELECT * FROM COMPANY;
ID  NAME   AGE  ADDRESS     SALARY
--  -----  ---  ----------  -------
1   Paul   32   California  20000.0
2   Allen  25   Texas       15000.0

The usage of the sqlite3 command-line tool is completely consistent with that on Linux. You can even copy the .db files from Linux to use in NuttX.

Running SQLite on ESP32

Install the Compilation Environment

pip install esptool

mkdir -p ~/.toolchain;cd ~/.toolchain
wget https://github.com/espressif/crosstool-NG/releases/download/esp-12.2.0_20230208/xtensa-ESP32-elf-12.2.0_20230208-x86_64-Linux-gnu.tar.xz
tar -xf ./xtensa-ESP32-elf-12.2.0_20230208-x86_64-Linux-gnu.tar.xz
rm xtensa-ESP32-elf-12.2.0_20230208-x86_64-Linux-gnu.tar.xz
echo "export PATH=\"$HOME/.toolchain/xtensa-ESP32-elf/bin:\$PATH\"" >> ~/.profile

The ESP32 development board I am using is the ESP-WROOM-32, which has 4MB (32Mb) of SPI Flash and 520KB SRAM.

Getting Started with NuttX: Running SQLite on ESP32

Compile and Download

In this configuration, 1M of FLASH is used as the code space, and 1M of FLASH is used as the file system (Littlefs). It is mounted to the /data directory.

# Compile SQLite configuration
./nx.sh boards/ESP32/configs/sqlite

After compiling with the above command, a NuttX.bin file will be generated in the NuttX directory. We then need to flash it to the device. The /dev/ttyUSB0 below needs to be filled in according to the actual situation.

# On Ubuntu, download the firmware to the device
esptool.py -c ESP32 -p /dev/ttyUSB0 -b 921600  write_flash -fs detect -fm dio -ff 40m 0x1000 boards/ESP32/boot/bootloader-ESP32.bin 0x8000 boards/ESP32/boot/partition-table-ESP32.bin 0x10000 NuttX/NuttX.bin

After flashing, open the serial terminal, and you can experience the SQLite command-line tool on ESP32 by executing the same steps as above. (The above is actually the result running on ESP32)

Using in Code

Using SQLite in NuttX is also the same as in Linux. You just need to include the sqlite3.h header file in the code. For example, we add a sqlite3 demo in the demo project, as follows:

#include <sqlite3.h>
#include <stdio.h>

int callback(void *NotUsed, int argc, char **argv,
                    char **azColName) {
    NotUsed = 0;
    for (int i = 0; i < argc; i++) {
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}

int main(void) {
    sqlite3 *db;
    char *err_msg = 0;
    int rc = sqlite3_open("test.db", &db);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Cannot open database: %s\n",
                sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }
    char *sql = "SELECT * FROM Cars";
    rc = sqlite3_exec(db, sql, callback, 0, &err_msg);
    if (rc != SQLITE_OK ) {
        fprintf(stderr, "Failed to select data\n");
        fprintf(stderr, "SQL error: %s\n", err_msg);
        sqlite3_free(err_msg);
        sqlite3_close(db);
        return 1;
    }
    sqlite3_close(db);
    return 0;
}

In the above code, we used a callback function, which is called after executing the SELECT command. It prints the query results. The running result is as follows:

nsh> cd data
nsh> demo
Id = 1
Name = Audi
Price = 52642

Id = 2
Name = Mercedes
Price = 57127

Id = 3
Name = Skoda
Price = 9000
...

Resource Consumption

After compiling on ESP32, the resource consumption before and after enabling SQLite is compared. SQLite occupies approximately 600K of FLASH and 70K of RAM. If you configure sqlite_cfg.h, you can further trim and reduce FLASH usage.

NXOS(master) ✗: size hello.elf
   text    data     bss     dec     hex filename
 110672     532    9632  120836   1d804 hello.elf
NXOS(master) ✗: size sqlite.elf
   text    data     bss     dec     hex filename
 671310    7664   10680  689654   a85f6 sqlite.elf

Postscript

Recently, someone asked me in the public account’s backend whether there are any videos about NuttX. I searched online and found that there are very few videos about NuttX usage.

In the flag I set in 2021, I mentioned publishing a video, and finally, I had the opportunity to remove this flag during this National Day holiday.

The video will be released tomorrow. If you have any questions about this article, you can click to read the original text and comment on GitHub (the public account registration was too late, and there is no comment function 😢)

end

Love technology, love life

This is an atypical tech geek, click on the blue word above to follow

Welcome to click on “Looking” 👀,Like 👍, Collect ⭐

Getting Started with NuttX: Running SQLite on ESP32

WeChat Official Account|Atypical Tech Geek

Getting Started with NuttX: Running SQLite on ESP32Previous Recommendations
  • X-TRACK Software Architecture Analysis

  • Analysis of the MiJia Thermometer

  • Introduction to Nuttx and Quick Start

Leave a Comment

×