classification
Title: [sqlite3] Add support for R*Tree callbacks
Type: enhancement Stage:
Components: Library (Lib) Versions: Python 3.10
process
Status: open Resolution:
Dependencies: Superseder:
Assigned To: Nosy List: berker.peksag, erlendaasland
Priority: normal Keywords: patch

Created on 2021-03-09 21:54 by erlendaasland, last changed 2021-03-24 22:57 by erlendaasland.

Files
File name Uploaded Description Edit
patch.diff erlendaasland, 2021-03-11 00:36 Proof-of-concept
test_rtree.py erlendaasland, 2021-03-11 00:37
patch.diff erlendaasland, 2021-03-24 22:57 PoC patch with R*Tree autodetection
Messages (5)
msg388391 - (view) Author: Erlend E. Aasland (erlendaasland) * (Python triager) Date: 2021-03-09 21:54
Ref. bpo-43440

Now that both Windows and macOS builds compile SQLite with R*Tree support, we should consider adding support for R*Tree callbacks.

SQLite has two API's:
- sqlite3_rtree_query_callback() for SQLite 3.8.5 and newer.
- sqlite3_rtree_geometry_callback() for SQLite pre 3.8.5.

I suggest using the new API only, because it is more flexible, and it is also the one recommended by SQLite.

See https://sqlite.org/rtree.html


Python API:
sqlite3.Connection.create_rtree_query_function()

Too long function name?

As for the callback spec, I'm not sure what's the most pythonic approach?
callback(coords, *params, **query_info):
  coords   # array of coordinates of node or entry to check
  *params  # parameters passed to the SQL function
  **query_info # the rest of the relevant sqlite3_rtree_query_info members
  return (visibility, score)
msg388472 - (view) Author: Erlend E. Aasland (erlendaasland) * (Python triager) Date: 2021-03-11 00:36
FYI, PoC patch attached. Lacks tests and some #ifdefs. Let me know if I should create a PR out of it.
msg388473 - (view) Author: Erlend E. Aasland (erlendaasland) * (Python triager) Date: 2021-03-11 00:37
Test run output (see attached test file):

$ ./python.exe test_rtree.py
ARGS: ((-80.77490234375, -80.77469635009766, 35.377593994140625, 35.377803802490234), (45.3, 22.9, 5.0))
KWARGS: {'num_queued': [0, 1], 'context': None, 'level': 0, 'max_level': 1, 'rowid': 1, 'parent_score': 0.0, 'parent_visibility': 1}
ARGS: ((-81.0, -79.5999984741211, 35.0, 36.20000076293945), (45.3, 22.9, 5.0))
KWARGS: {'num_queued': [1, 1], 'context': ['stuff'], 'level': 0, 'max_level': 1, 'rowid': 2, 'parent_score': 0.0, 'parent_visibility': 1}
msg388507 - (view) Author: Erlend E. Aasland (erlendaasland) * (Python triager) Date: 2021-03-11 14:29
Unfortunately, there's no way to detect R*Tree support in sqlite3.h. We could run a script that dumps the compile options, and generate a config.h file from that:

$ for L in $(sqlite3 ":memory:" "pragma compile_options"); do echo #define SQLITE_$L >> config.h; done
$ cat config.h
#define SQLITE_COMPILER=clang-12.0.0
#define SQLITE_ENABLE_DBSTAT_VTAB
#define SQLITE_ENABLE_FTS4
#define SQLITE_ENABLE_FTS5
#define SQLITE_ENABLE_GEOPOLY
#define SQLITE_ENABLE_JSON1
#define SQLITE_ENABLE_RTREE
#define SQLITE_ENABLE_STMTVTAB
#define SQLITE_THREADSAFE=1


For Windows and macOS builds, we've already got the SQLite library compile options, so we can just reuse them in CFLAGS when we're building the sqlite3 module.
msg389492 - (view) Author: Erlend E. Aasland (erlendaasland) * (Python triager) Date: 2021-03-24 22:57
Attached is a patch that auto-detects R*Tree support. Tested on macOS only, but I'd guess it should work fine on any OS.
History
Date User Action Args
2021-03-24 22:57:09erlendaaslandsetfiles: + patch.diff

messages: + msg389492
2021-03-11 14:29:14erlendaaslandsetmessages: + msg388507
2021-03-11 00:37:47erlendaaslandsetfiles: + test_rtree.py

messages: + msg388473
2021-03-11 00:36:32erlendaaslandsetfiles: + patch.diff
keywords: + patch
messages: + msg388472
2021-03-09 21:54:25erlendaaslandcreate