RTree on ESP32 SQLite Version
Hello I'm working on a ESP32 project that requires a database with RTree extension. I 'm using this library : https://github.com/siara-cc/esp32-idf-sqlite3 which is based on amalgation 3.31.1 For tables with no rtree it's working fine. For RTree, I added this line in config_ext.h :
#define SQLITE_ENABLE_RTREE 1
No problem when I create the virtual table using rtree nor when I add line to this table. When I try to request this table, I got this error :
logSQLError undersize RTree blobs in "AIRSPACERTREE_node"
Could you please me to understand this error message ?
No problem when I create the virtual table using rtree
nor when I add line to this table
What is the result of
SELECT length(data) FROM AIRSPACERTREE_node WHERE nodeno = 1; after both steps?
Creation of the table :
CREATE VIRTUAL TABLE AIRSPACERTREE USING RTREE ( ID, MINX , MAXX, MINY , MAXY);
Insertion in the table :
INSERT INTO AIRSPACERTREE VALUES ('test', 1, 1, 3, 3);
Result of SELECT length(data) FROM AIRSPACERTREE_node WHERE nodeno = 1;
The R-tree extension uses incremental blob I/O functions. Please check that
pragma compile_options; does not mention OMIT_INCRBLOB. If it does, you have to recompile the SQLite library without that setting.
OMIT_INCRBLOB is not defined
Here are my defines :
#define SQLITE_OMIT_LOAD_EXTENSION 1
#define SQLITE_DQS 0
#define SQLITE_OS_OTHER 1
#define SQLITE_NO_SYNC 1
#define SQLITE_TEMP_STORE 1
#define SQLITE_DISABLE_LFS 1
#define SQLITE_DISABLE_DIRSYNC 1
#define SQLITE_SECURE_DELETE 0
#define SQLITE_DEFAULT_LOOKASIDE 512,64
#define YYSTACKDEPTH 20
#define SQLITE_SMALL_STACK 1
#define SQLITE_DEFAULT_PAGE_SIZE 4096
#define SQLITE_SORTER_PMASZ 4
#define SQLITE_DEFAULT_CACHE_SIZE -1
#define SQLITE_DEFAULT_MEMSTATUS 0
#define SQLITE_DEFAULT_MMAP_SIZE 0
#define SQLITE_CORE 1
#define SQLITE_SYSTEM_MALLOC 1
#define SQLITE_THREADSAFE 0
#define SQLITE_MUTEX_APPDEF 1
#define SQLITE_OMIT_WAL 1
#define SQLITE_DISABLE_FTS3_UNICODE 1
#define SQLITE_DISABLE_FTS4_DEFERRED 1
#define SQLITE_LIKE_DOESNT_MATCH_BLOBS 1
#define SQLITE_DEFAULT_FOREIGN_KEYS 0
#define SQLITE_DEFAULT_LOCKING_MODE 1
#define SQLITE_DEFAULT_PAGE_SIZE 4096
#define SQLITE_DEFAULT_PCACHE_INITSZ 8
#define SQLITE_MAX_DEFAULT_PAGE_SIZE 32768
#define SQLITE_POWERSAFE_OVERWRITE 1
#define SQLITE_MAX_EXPR_DEPTH 0
#define SQLITE_ENABLE_RTREE 1
I did not solve my problem. Could someone help ?
Read the documentation found at https://sqlite.org/rtree.html and pay particular attention to the BOLD text.
3. Using the R*Tree Module
The SQLite R*Tree module is implemented as a virtual table. Each R*Tree index is a virtual table with an odd number of columns between 3 and 11. The first column is always a 64-bit signed integer primary key. The other columns are pairs, one pair per dimension, containing the minimum and maximum values for that dimension, respectively. A 1-dimensional R*Tree thus has 3 columns. A 2-dimensional R*Tree has 5 columns. A 3-dimensional R*Tree has 7 columns. A 4-dimensional R*Tree has 9 columns. And a 5-dimensional R*Tree has 11 columns. The SQLite R*Tree implementation does not support R*Trees wider than 5 dimensions.
The first column of an SQLite R*Tree is similar to an integer primary key column of a normal SQLite table. It may only store a 64-bit signed integer value. Inserting a NULL value into this column causes SQLite to automatically generate a new unique primary key value. If an attempt is made to insert any other non-integer value into this column, the r-tree module silently converts it to an integer before writing it into the database.
The min/max-value pair columns are stored as 32-bit floating point values for "rtree" virtual tables or as 32-bit signed integers in "rtree_i32" virtual tables. Unlike regular SQLite tables which can store data in a variety of datatypes and formats, the R*Tree rigidly enforce these storage types. If any other type of value is inserted into such a column, the r-tree module silently converts it to the required type before writing the new record to the database.
(8.1) By Keith Medcalf (kmedcalf) on 2020-11-16 00:46:55 edited from 8.0 in reply to 6 [link] [source]
Note also that you need to have pragma's enabled, in particular, pragma page_size. If the page_size cannot be retrieved when you create/connect to the rtree virtual table then you will get an error logged. Presumably the error may be "blank" if the reason for the error is that there is no error.
use "pragma compile_options;" to find out the options in actual effect rather than the compile options you think are in effect.
The result of PRAGMA COMPILE_OPTIONS :
COMPILER=gcc-9.3.0 ENABLE_COLUMN_METADATA ENABLE_DBSTAT_VTAB ENABLE_FTS3 ENABLE_FTS3_PARENTHESIS ENABLE_FTS3_TOKENIZER ENABLE_FTS4 ENABLE_FTS5 ENABLE_JSON1 ENABLE_LOAD_EXTENSION ENABLE_PREUPDATE_HOOK ENABLE_RTREE ENABLE_SESSION ENABLE_STMTVTAB ENABLE_UNLOCK_NOTIFY ENABLE_UPDATE_DELETE_LIMIT HAVE_ISNAN LIKE_DOESNT_MATCH_BLOBS MAX_SCHEMA_RETRY=25 MAX_VARIABLE_NUMBER=250000 OMIT_LOOKASIDE SECURE_DELETE SOUNDEX TEMP_STORE=1 THREADSAFE=1 USE_URI
I changed the first column of my rtree table to match integer type.
No change, same error.
Fascinating. I assume that means that "pragma page_size;" actually returns a value. Note that the payload of the first B-Tree node should be created as soon as a new RTree virtual table if defined even though it is all empty:
sqlite> create virtual table x using rtree(id, minx, maxx, miny, maxy); sqlite> select *, length(data) from x_node; ┌────────┬──────┬──────────────┐ │ nodeno │ data │ length(data) │ ├────────┼──────┼──────────────┤ │ 1 │ │ 1228 │ └────────┴──────┴──────────────┘
(data shows as empty because it should be 1228 bytes of all zero which makes it a zero-length string).
This is with a page_size of 4096. The size of the BTree payloads varies depending on the page_size and the number of dimensions. For two dimensions the maximum node size is 1228 bytes and that is reached with a page size of 2048.
Note that the node size is computed by function getNodeSize at line 3644 in rtree.c. The only way for the node size to be 0 without causing an error is if either
pragma page_size returns 0 or if the value of the define RTREE_MAXCELLS is fubar.
Note that the creation of the virtual table WILL NOT FAIL just because the computed nodesize is zero. (see the rtreeSqlInit function at line 3484 which will quite contentedly insert a zeroblob(0) as _node 1).
If this happens then the RTRee is unusable.
So see if
pragma page_size returns something rational, and if it does then if it is bigger than 1024 bytes the only other way to get a nodesize of 0 is if the RTREE_MAXCELLS define is fubar (ie, 0).
Just in case also make sure the zeroblob function is working:
sqlite> select length(zeroblob(1228)); ┌────────────────────────┐ │ length(zeroblob(1228)) │ ├────────────────────────┤ │ 1228 │ └────────────────────────┘
ie, when called as above the result value should be the same as the argument given.
I am not sure about the issue with
RTree, but would like to remind that ESP32 is a memory constrained environment and not all features of SQLite are expected work without issues.
If you have PSRAM on your board you might try enabling it for the sqlite3 library using the info mentioned in this link: https://github.com/siara-cc/esp32_arduino_sqlite3_lib/issues/45#issue-899170152