ESP-IDFを使ってみる

sqlite3ライブラリ

ESP-IDFに対応するsqlite3ライブラリがこちらで公開されています。
但し、かなり古いのでesp-idf V5ではビルドできません。
そこで、V5でビルドできるforkをこちらに公開しています。
一番簡単に使えるのはspiffsのプロジェクトです。

$ git clone
              https://github.com/nopnop2002/esp32-idf-sqlite3

              

              $ cd esp32-idf-sqlite3

              

              $ cd spiffs

              

              $ idf.py flash monitor

              

              I (0) cpu_start: App cpu up.

              I (342) cpu_start: Pro cpu start user code

              I (342) cpu_start: cpu freq: 160000000 Hz

              I (342) cpu_start: Application information:

              I (347) cpu_start: Project name:    
              sqlite3

              I (352) cpu_start: App
              version:     
              esp-idf-v3-10-gf93f754-dirty

              I (359) cpu_start: Compile time:    
              Mar 14 2024 20:04:19

              I (365) cpu_start: ELF file SHA256: 
              6a42537cdbbeedb0...

              I (371) cpu_start:
              ESP-IDF:         
              v5.1.2-695-gd922d4178f-dirty

              I (378) cpu_start: Min chip rev:    
              v0.0

              I (382) cpu_start: Max chip rev:    
              v3.99

              I (387) cpu_start: Chip
              rev:         v1.0

              I (392) heap_init: Initializing. RAM available for dynamic
              allocation:

              I (399) heap_init: At 3FFAE6E0 len 00001920 (6 KiB): DRAM

              I (405) heap_init: At 3FFB3E08 len 0002C1F8 (176 KiB):
              DRAM

              I (411) heap_init: At 3FFE0440 len 00003AE0 (14 KiB):
              D/IRAM

              I (418) heap_init: At 3FFE4350 len 0001BCB0 (111 KiB):
              D/IRAM

              I (424) heap_init: At 4008C910 len 000136F0 (77 KiB): IRAM

              I (432) spi_flash: detected chip: generic

              I (435) spi_flash: flash io: dio

              W (439) spi_flash: Detected size(4096k) larger than the
              size in the binary image header(2048k). Using the size in
              the binary image header.

              I (453) app_start: Starting scheduler on CPU0

              I (457) app_start: Starting scheduler on CPU1

              I (457) main_task: Started on CPU0

              I (467) main_task: Calling app_main()

              I (467) MAIN: Initializing SPIFFS

              W (467) SPIFFS: mount failed, -10025. formatting...

              I (8767) MAIN: Partition size: total: 896321, used: 0

              I (9007) main_task: Returned from app_main()

              Opened database successfully

              Opened database successfully

              CREATE TABLE test1 (id INTEGER, content);

              Operation done successfully

              Time taken: 189772

              CREATE TABLE test2 (id INTEGER, content);

              Operation done successfully

              Time taken: 189477

              INSERT INTO test1 VALUES (1, 'Hello, World from test1');

              Operation done successfully

              Time taken: 65863

              INSERT INTO test2 VALUES (1, 'Hello, World from test2');

              Operation done successfully

              Time taken: 62830

              SELECT * FROM test1

              Callback function called: id = 1

              content = Hello, World from test1

              

              Operation done successfully

              Time taken: 15902

              SELECT * FROM test2

              Callback function called: id = 1

              content = Hello, World from test2

              

              Operation done successfully

              Time taken: 15477

              All Done

SPIFFSには最大でも0x2f0000バイト(約3M)しか領域を確保できません。
そこで、SDSPI(SPI-IF)のカードリーダーを使えば、大容量のマイクロSDカード上にDBを構築することができます。

マイクロSDカードをFAT32でフォーマットし、esp32-idf-sqlite3/sd_spi/dataに有る census2000names.db mdr512.dbをSDカードにコピーします。
以下の手順でこれらのDBを検索するサンプルが動きます。

$ git clone
              https://github.com/nopnop2002/esp32-idf-sqlite3

              

              $ cd esp32-idf-sqlite3

              

              $ cd sd_spi

              

              $ idf.py flash monitor

              

              I (0) cpu_start: App cpu up.

              I (353) cpu_start: Pro cpu start user code

              I (353) cpu_start: cpu freq: 160000000 Hz

              I (353) cpu_start: Application information:

              I (358) cpu_start: Project name:    
              sqlite3

              I (363) cpu_start: App
              version:     
              esp-idf-v3-10-gf93f754-dirty

              I (370) cpu_start: Compile time:    
              Mar 14 2024 20:09:33

              I (376) cpu_start: ELF file SHA256: 
              d1477895e1c7eccb...

              I (382) cpu_start:
              ESP-IDF:         
              v5.1.2-695-gd922d4178f-dirty

              I (389) cpu_start: Min chip rev:    
              v0.0

              I (393) cpu_start: Max chip rev:    
              v3.99

              I (398) cpu_start: Chip
              rev:         v1.0

              I (403) heap_init: Initializing. RAM available for dynamic
              allocation:

              I (410) heap_init: At 3FFAE6E0 len 00001920 (6 KiB): DRAM

              I (416) heap_init: At 3FFB3FD8 len 0002C028 (176 KiB):
              DRAM

              I (422) heap_init: At 3FFE0440 len 00003AE0 (14 KiB):
              D/IRAM

              I (429) heap_init: At 3FFE4350 len 0001BCB0 (111 KiB):
              D/IRAM

              I (435) heap_init: At 4008DFE4 len 0001201C (72 KiB): IRAM

              I (443) spi_flash: detected chip: generic

              I (446) spi_flash: flash io: dio

              W (450) spi_flash: Detected size(4096k) larger than the
              size in the binary image header(2048k). Using the size in
              the binary image header.

              I (464) app_start: Starting scheduler on CPU0

              I (468) app_start: Starting scheduler on CPU1

              I (468) main_task: Started on CPU0

              I (478) main_task: Calling app_main()

              I (478) MAIN: Initializing SD card

              I (478) MAIN: Using SPI peripheral

              I (488) MAIN: Mounting filesystem

              I (488) gpio: GPIO[13]| InputEn: 0| OutputEn: 1|
              OpenDrain: 0| Pullup: 0| Pulldown: 0| Intr:0

              I (538) sdspi_transaction: cmd=5, R1 response: command not
              supported

              I (608) MAIN: Filesystem mounted

              Name: SA08G

              Type: SDHC/SDXC

              Speed: 20.00 MHz (limit: 20.00 MHz)

              Size: 7420MB

              CSD: ver=2, sector_size=512, capacity=15196160
              read_bl_len=9

              SSR: bus_width=1

              I (618) main_task: Returned from app_main()

              Opened database successfully

              Opened database successfully

              Select * from surnames where name = 'MICHELLE'

              Callback function called: year = 2000

              name = MICHELLE

              rank = 28326

              count = 794

              prop100k = 0.29

              cum_prop100k = 78177.88

              pctwhite = 52.77

              pctblack = 27.83

              pctapi = 1.01

              pctaian = 1.89

              pct2prace = 5.67

              pcthispanic = 10.83

              

              Operation done successfully

              Time taken: 75505

              Select * from domain_rank where domain between
              'google.com' and 'google.com.z'

              Callback function called: domain = google.com

              rank = 1

              

              Callback function called: domain = google.com.af

              rank = 350686

              

              Callback function called: domain = google.com.ag

              rank = 655745

              

              Callback function called: domain = google.com.ar

              rank = 3276

              

              Callback function called: domain = google.com.au

              rank = 655

              

              Callback function called: domain = google.com.bd

              rank = 44216

              

              Callback function called: domain = google.com.bh

              rank = 184680

              

              Callback function called: domain = google.com.bn

              rank = 440748

              

              Callback function called: domain = google.com.bo

              rank = 66721

              

              Callback function called: domain = google.com.br

              rank = 1081

              

              Callback function called: domain = google.com.by

              rank = 513182

              

              Callback function called: domain = google.com.bz

              rank = 417790

              

              Callback function called: domain = google.com.cn

              rank = 289788

              

              Callback function called: domain = google.com.co

              rank = 10325

              

              Callback function called: domain = google.com.cu

              rank = 318754

              

              Callback function called: domain = google.com.cy

              rank = 257613

              

              Callback function called: domain = google.com.do

              rank = 39445

              

              Callback function called: domain = google.com.ec

              rank = 28830

              

              Callback function called: domain = google.com.eg

              rank = 10018

              

              Callback function called: domain = google.com.et

              rank = 292223

              

              Callback function called: domain = google.com.fj

              rank = 528036

              

              Callback function called: domain = google.com.gh

              rank = 181649

              

              Callback function called: domain = google.com.gi

              rank = 586356

              

              Callback function called: domain = google.com.gr

              rank = 803619

              

              Callback function called: domain = google.com.gt

              rank = 51123

              

              Callback function called: domain = google.com.hk

              rank = 714

              

              Callback function called: domain = google.com.jm

              rank = 214451

              

              Callback function called: domain = google.com.kh

              rank = 186972

              

              Callback function called: domain = google.com.kw

              rank = 69742

              

              Callback function called: domain = google.com.lb

              rank = 43721

              

              Callback function called: domain = google.com.ly

              rank = 234936

              

              Callback function called: domain = google.com.mm

              rank = 538257

              

              Callback function called: domain = google.com.mt

              rank = 77807

              

              Callback function called: domain = google.com.mx

              rank = 2165

              

              Callback function called: domain = google.com.my

              rank = 6832

              

              Callback function called: domain = google.com.na

              rank = 332291

              

              Callback function called: domain = google.com.nf

              rank = 486173

              

              Callback function called: domain = google.com.ng

              rank = 10715

              

              Callback function called: domain = google.com.ni

              rank = 227346

              

              Callback function called: domain = google.com.np

              rank = 173886

              

              Callback function called: domain = google.com.om

              rank = 289408

              

              Callback function called: domain = google.com.pa

              rank = 266317

              

              Callback function called: domain = google.com.pe

              rank = 9932

              

              Callback function called: domain = google.com.pg

              rank = 954622

              

              Callback function called: domain = google.com.ph

              rank = 5091

              

              Callback function called: domain = google.com.pk

              rank = 11266

              

              Callback function called: domain = google.com.pr

              rank = 28874

              

              Callback function called: domain = google.com.py

              rank = 204998

              

              Callback function called: domain = google.com.qa

              rank = 211744

              

              Callback function called: domain = google.com.ru

              rank = 357320

              

              Callback function called: domain = google.com.sa

              rank = 5154

              

              Callback function called: domain = google.com.sb

              rank = 662685

              

              Callback function called: domain = google.com.sg

              rank = 4652

              

              Callback function called: domain = google.com.sl

              rank = 974857

              

              Callback function called: domain = google.com.sv

              rank = 65243

              

              Callback function called: domain = google.com.tj

              rank = 587161

              

              Callback function called: domain = google.com.tr

              rank = 3316

              

              Callback function called: domain = google.com.tw

              rank = 1793

              

              Callback function called: domain = google.com.ua

              rank = 1565

              

              Callback function called: domain = google.com.uy

              rank = 36018

              

              Callback function called: domain = google.com.vc

              rank = 802050

              

              Callback function called: domain = google.com.vn

              rank = 8457

              

              Operation done successfully

              Time taken: 385331

              Select * from surnames where name = 'SPRINGER'

              Callback function called: year = 2000

              name = SPRINGER

              rank = 1031

              count = 31044

              prop100k = 11.51

              cum_prop100k = 40931.57

              pctwhite = 85.65

              pctblack = 9.61

              pctapi = 0.52

              pctaian = 0.73

              pct2prace = 1.76

              pcthispanic = 1.74

              

              Operation done successfully

              Time taken: 48203

              Select * from domain_rank where domain = 'zoho.com'

              Callback function called: domain = zoho.com

              rank = 1121

              

              Operation done successfully

              Time taken: 26833

              All Done

SDカードリーダーにはSDMMCインタフェースの物も有りますが、ESP32では非常に使いにくいです。
理由はこちらに詳しく公開されていますが、インタフェースで使用するGPIOが固定されていて、それらのGPIOがブートセレクター(ブートストラップ)になっています。

sqliteはLinuxサーバーでもよく使われているDBです。
そこで、ESP32からネットワーク経由で、Linuxサーバー上のsqliteにアクセスするアプリケーションをこちらで紹介しています。

ESP32からネットワーク経由で、Linuxサーバー上のMySQLにアクセスするアプリケーションをこちらで紹介しています。
本格的なDBを扱うことができます。

続く．．．