numbox.core.bindings.sqlite
SQLite bindings, grouped in the numbox.core.bindings.sqlite subpackage.
Import from the specific module, e.g.
from numbox.core.bindings.sqlite.conn import sqlite3_open. The package
__init__ is empty, so importing one module does not pull in (or compile)
the rest of the SQLite subsystem.
numbox.core.bindings.sqlite.conn
SQLite connection + metadata bindings.
Resolves the shared library via load_lib("sqlite3") from
utils._loaded_libs. Other sqlite/*.py modules (currently
column) call the same getter rather than importing
_sqlite3_lib from here, so there’s no cross-module dependency on
which file happens to load the library first.
Two functions are decorated with proxy_if_available:
sqlite3_changes64 and sqlite3_total_changes64, both added in
SQLite 3.37 (Nov 2021). Older library versions lack these symbols —
notably the SQLite 3.34 shipped with CPython 3.10 on Windows, and
distro-shipped system sqlite3 on Linux / macOS that predates 3.37.
The wrappers stub to NotImplementedError so callers can
hasattr(...,"as_func") to decide whether to use them or fall back
to the int32 variants.
numbox.core.bindings.sqlite.stmt
SQLite statement-lifecycle bindings: prepare_v2 / finalize / reset / step / sql / expanded_sql / stmt_busy.
Note: sqlite3_expanded_sql returns a char * the caller MUST free via
sqlite3_free (bound in sqlite/exec.py). Document this with each call site
rather than building a wrapper that auto-frees — the wrapper would hide
ownership in a way the rest of the bindings don’t.
numbox.core.bindings.sqlite.bind
SQLite parameter-binding bindings.
The destructor arg in bind_text / bind_blob (last intp) is one of: - SQLITE_STATIC = 0 -> SQLite assumes the buffer outlives the statement - SQLITE_TRANSIENT = -1 -> SQLite makes a copy - any other value -> a C function pointer SQLite calls to free the buffer
For numpy arrays passed via array_data_p, prefer SQLITE_TRANSIENT unless the caller can guarantee the array outlives the prepared statement.
numbox.core.bindings.sqlite.column
SQLite column accessors.
Three metadata accessors (column_database_name / column_table_name /
column_origin_name) require SQLite to be compiled with
SQLITE_ENABLE_COLUMN_METADATA. CPython’s bundled sqlite3.dll on Windows
has it enabled, and we use only that bundled DLL on Windows (see
_resolve_lib_path in utils.py); the system sqlite3 picked up
via find_library on Linux / macOS may or may not, depending on the
distribution. proxy_if_available stubs the accessors out when absent so
callers can hasattr-guard or fall back.
All other accessors are universally available across the matrix.
numbox.core.bindings.sqlite.exec
SQLite exec + free bindings.
sqlite3_exec is the one-shot SQL escape hatch — it parses, prepares, steps, and finalizes a (potentially multi-statement) SQL string. The third arg is a function pointer to a per-row callback; pass 0 for no callback.
sqlite3_free releases memory SQLite allocated and returned to the caller — notably the errmsg buffer from sqlite3_exec’s out-param, and the result of sqlite3_expanded_sql. sqlite3_malloc is its allocation counterpart (used by the virtual-table machinery to allocate the vtab and cursor structs).
Callback shape (informational):
int (*sqlite3_exec_callback)(void *ctx, int ncol,
char **col_values, char **col_names)
Return 0 to continue, nonzero to abort with SQLITE_ABORT.
Produce the callback address from Python via:
@cfunc(int32(voidptr, int32, intp, intp))
def my_row_cb(ctx, n, values_pp, names_pp): ...
sqlite3_exec(db, sql, my_row_cb.address, ctx_p, errmsg_pp)
numbox.core.bindings.sqlite.blob
SQLite BLOB incremental I/O bindings: blob_open / _close / _bytes / _read / _write / _reopen.
All functions present in SQLite 3.4.0 (2007) except _reopen which arrived in 3.7.4 (2010). No version gating needed — far below the matrix floor of 3.34.
numbox.core.bindings.sqlite.hooks
SQLite callback hooks: update_hook / progress_handler / busy_handler / commit_hook / rollback_hook / trace_v2.
Each takes a function pointer (intp) the caller produces via @cfunc(…).address. The cfunc instance MUST outlive the hook registration — keep it at module scope in the caller.
Callback shapes (informational; signatures are caller’s responsibility): - update_hook: void(void*, int op, const char* db, const char* tbl, sqlite3_int64 rowid) - progress_handler: int(void*) – nonzero aborts - busy_handler: int(void*, int) – 0 to abort, nonzero to retry - commit_hook: int(void*) – nonzero vetoes commit - rollback_hook: void(void*) - trace_v2: int(unsigned, void*, void*, void*)
numbox.core.bindings.sqlite.constants
SQLite numeric constants (result codes, type codes, open flags, blob flags, trace flags, destructor sentinels).
Public surface — import directly, e.g.
from numbox.core.bindings.sqlite.constants import SQLITE_OK.
All names are uppercase SQLITE_* to avoid collision with the lowercase
C-function-named wrappers.
Numba handles Python integer literals natively in @njit code, so these
constants are usable inside JITed functions without further wrapping. The
underlying SQLite values are API-stable across all matrix versions
(3.34.0 through current).
numbox.core.bindings.sqlite.value
SQLite value accessor bindings.
Read UDF arguments inside xFunc / xStep / xInverse callbacks. Each function takes a sqlite3_value* (as intp) obtained by dereferencing the argv_pp array at the appropriate index.
numbox.core.bindings.sqlite.result
SQLite result setter bindings.
Write the UDF return value inside xFunc / xFinal / xValue callbacks. Each function takes a sqlite3_context* (as intp) as the first argument.
The destructor arg in result_text / result_blob (last intp before any trailing args) is one of: - SQLITE_STATIC = 0 -> SQLite assumes the buffer outlives the call - SQLITE_TRANSIENT = -1 -> SQLite makes a copy - any other value -> a C function pointer SQLite calls to free the buffer
numbox.core.bindings.sqlite.udf
SQLite UDF registration and context bindings.
sqlite3_create_function_v2 – register scalar (xFunc) and aggregate (xStep/xFinal) UDFs. sqlite3_create_window_function – register window UDFs (xStep/xFinal/xValue/xInverse). sqlite3_aggregate_context – allocate per-group state for aggregate/window UDFs. sqlite3_user_data – retrieve pApp from context. sqlite3_context_db_handle – retrieve db pointer from context.
Callback function pointers are passed as intp obtained from @cfunc(…).address. Pass 0 for NULL (no callback / no pApp / no xDestroy).
- numbox.core.bindings.sqlite.udf.sqlite3_create_function_v2(db, name_p, n_arg, e_text_rep, p_app, x_func, x_step, x_final, x_destroy)[source]
numbox.core.bindings.sqlite.udf_helpers
Higher-level registration helpers for structref-backed SQLite UDAFs.
register_aggregate / register_window generate the SQLite callback
functions (xStep/xInverse/xValue/xFinal) that perform the per-group state
lifecycle – sqlite3_aggregate_context allocation + NULL guard, the single
intp slot, init-on-first-step via export_meminfo, borrow_structref, and
the release-in-xFinal-but-NOT-xValue rule – so callers write only their
init/step/finalize (and inverse/value for windows) state
logic, as plain Python or already-jitted (@njit/@proxy) functions.
Exception handling. Each generated callback wraps the user
step/inverse/value/finalize call in a try/except that reports a
descriptive error via sqlite3_result_error (e.g. “error in user step
callback”, with code SQLITE_ERROR) when the user callback raises.
A numba @cfunc otherwise SWALLOWS the exception (it prints “Exception
ignored” and returns the zero default without unwinding into SQLite), which would
be a silent wrong result; the in-body catch also lets numba run the borrowed
state’s reference-count decrement, which the unwind would otherwise skip – a
per-group meminfo leak. Only xFinal releases the slot.
Mechanism: per-UDAF callback source is generated with the state type and the user functions
baked in as module globals (so the calls inline), written to a content-addressed
anchor file under numba’s cache dir (reusing numbox.utils.preprocessing), and the
impls (@njit(**jit_options) – the numbox-wide config, default cache=True)
cache across processes. The anchor’s content hash
folds a cloudpickle of the user functions’ code objects so editing a body –
including a numeric literal – invalidates correctly.
Caller requirements. Callbacks may be plain Python functions or already-jitted
callables (@njit/@proxy); plain ones are compiled with njit (see
_prepare_callbacks). The generated impls (@njit(**jit_options), default
cache=True) cache and invalidate correctly even when the state-type class and
the callbacks are defined in __main__ – the anchor key is content-addressed
on a cloudpickle of each callback’s __code__ (plus repr(state_type),
the resolved jit_options, and the numba/numbox versions), never on
__module__ (see numbox.utils.digest.digest); the
subprocess tests test_xprocess_cache_no_growth /
test_invalidation_on_literal_edit exercise this __main__ path. The one
case that does require a stable __module__ is a caller-side
@njit(cache=True) callback – numba refuses to cache functions defined in
__main__ – so for deployments where callbacks are themselves cached, define
the state-type class and callbacks in an importable module.
- numbox.core.bindings.sqlite.udf_helpers.register_aggregate(db, name, n_arg, state_type, init, step, finalize, *, deterministic=False)[source]
Register a structref-backed aggregate UDAF.
Callbacks may be plain Python or already-jitted (
@njit/@proxy); plain functions are compiled withnjit.- Parameters:
db – connection pointer (intp), as returned by
sqlite3_open.name – SQL function name (str); the C-string lifetime is handled here.
n_arg – argument count, or -1 for variadic.
state_type – the numba structref instance type for per-group state.
init –
() -> statereturning a fresh state.step –
(state, ctx, argc, argv_pp)updating state.finalize –
(state, ctx)writing the result.deterministic – OR-in
SQLITE_DETERMINISTIC.
Returns
None; the generated callbacks need not be retained – numba keeps the compiled cfunc code and dispatchers resident for the process lifetime.
- numbox.core.bindings.sqlite.udf_helpers.register_window(db, name, n_arg, state_type, init, step, inverse, value, finalize, *, deterministic=False)[source]
Register a structref-backed window UDAF.
Same as
register_aggregate()plusinverse(state, ctx, argc, argv_pp)(un-applies a row; state already exists) andvalue(state, ctx)(emits the running result WITHOUT releasing). OnlyxFinalreleases the meminfo.
numbox.core.bindings.sqlite.vtable
Expose a numpy array or a mapping of 1-D arrays as a read-only SQLite virtual table (register_table).
A single generic sqlite3_module (built once at import) serves every table; the per-column base pointers / strides / dtype tags / schema live in a numpy structured-array descriptor whose data pointer is passed as pClientData.
The sqlite3_vtab and sqlite3_vtab_cursor that our xCreate/xOpen callbacks allocate are C structs whose first member is the SQLite-owned base; we append our own members after it. Each is modelled as a numpy structured dtype with that base nested as field ‘base’. See https://www.sqlite.org/vtab.html.
- numbox.core.bindings.sqlite.vtable.register_table(db, name, data, columns=None, *, text_as_blob=False)[source]
Expose tabular data as a read-only eponymous SQLite virtual table (queryable directly as
namewith no CREATE VIRTUAL TABLE, since the module’s xCreate is its xConnect).datamay be:a 1-D numpy structured array (row-major; column names from the dtype, optionally renamed by
columns(column order always follows the dtype field order; to reorder, reindex the array or pass a mapping));a 2-D numpy array (row-major;
columnsrequired, one name per column);a mapping (e.g. a
dict) of column-name -> 1-D numpy array (columnar; all arrays must share one length; the keys name the columns, so passingcolumnsraisesValueError– rename/reorder by building the mapping with the desired keys and order before calling). Mapping values must already benumpy.ndarray(no coercion); a non-array value raisesTypeErrorand a non-1-D array raisesValueError.
The registration’s keep-alive lives in the module-level
_DATA_ANCHORand is released by SQLite viaxDestroy(on connection close or re-registration of the same name). The caller must not mutate or resize the array(s) while the table is registered – the view is zero-copy, so queries read each column’s buffer directly (numeric reads alias it, and'S'/BLOB values are handed to SQLite asSQLITE_STATICpointers into it).Value semantics:
uint64values >= 2**63 are stored as SQLite’s signed INTEGER and therefore wrap to negative.Floats pass through as REAL, including +/-inf – EXCEPT NaN: SQLite coerces a NaN
REALto SQL NULL (viasqlite3IsNaN), so a NaN cell reads back as NULL. numpy has no missing value, so every non-NaN cell is non-NULL. Masked arrays are unsupported: the mask is ignored.
String columns:
text_as_blobaffects only bytes ('S') columns; unicode ('U') is always TEXT. By default'S'becomes TEXT and its raw bytes pass through unvalidated – passtext_as_blob=Truefor non-UTF-8 bytes so they are stored as BLOB rather than malformed TEXT.Fixed-width
'S'/'U'columns are NUL-padded by numpy; trailing NUL padding is trimmed on read while interior NULs are preserved.A TEXT value with an interior NUL is stored faithfully (an explicit byte length is passed), but C-string readers and most SQL text functions truncate at the first NUL; read it via
sqlite3_column_bytes+ the text/blob pointer, or usetext_as_blob=Truefor full fidelity.
numbox.core.bindings.sqlite.query
query_to_array: collect SELECT results into a numpy structured array.
- numbox.core.bindings.sqlite.query.query_to_array(db, sql_p, dtype)[source]
Run the NUL-terminated SQL text at pointer
sql_pondband return its rows as a 1-D numpy structured array ofdtype(one field per result column, by position).sql_pis a char* pointer (e.g. fromnumbox.utils.cstrings.c_stringorget_unicode_data_p), not a Python str. NULL -> NaN (float) / 0 (int) / empty (text/blob).
numbox.core.bindings.sqlite.tvf
Expose a per-query-computed numpy structured array as a SQLite table-valued function (register_tvf).
register_tvf(db, name, arg_types, out_dtype, fn) registers an eponymous
virtual table whose rows are NOT static – they are produced per query by the
user’s fn(*args), which returns a 1-D numpy structured array of out_dtype.
The function arguments are exposed as HIDDEN columns: SELECT * FROM f(2, 5)
turns 2/5 into EQ constraints on the hidden columns, which xBestIndex
binds into argv (in declaration order) and xFilter decodes and feeds to
fn. The returned array is held alive for the cursor’s lifetime via an
NRT-backed [meminfo_p, data_p] slot (pinned with the inlined
_incref_meminfo intrinsic so numba’s refcount pass cannot strip it), and
released exactly once in xClose.
Unlike the read-only register_table (one shared module), each
register_tvf GENERATES its own xFilter / xColumn (the user fn and
out_dtype are baked in as codegen globals so the allocator specialises on the
dtype and the impls cache cross-process) and builds its own sqlite3_module
from this registration’s cfunc addresses. The handle retains the module struct,
every cfunc object (SQLite stores their addresses), the descriptor buffers, and
fn; its keep-alive lives in the module-level _DATA_ANCHOR (released by
SQLite via xDestroy).
- numbox.core.bindings.sqlite.tvf.register_tvf(db, name, arg_types, out_dtype, fn)[source]
Register an eponymous table-valued function backed by a computed array.
SELECT * FROM name(<args>)callsfn(*args)– a plain Python or@njitcallable returning a 1-D numpy structured array ofout_dtype– and serves that array’s rows. Thearg_types(numpy scalar dtypes; integer kinds are read viasqlite3_value_int64, floating kinds viasqlite3_value_double) are exposed as trailing HIDDEN columns and must each be supplied with an equality value in the query; a call form that leaves a hidden arg unbound is rejected with a SQLite constraint error (no rows).The registration’s keep-alive lives in the module-level
_DATA_ANCHORand is released by SQLite viaxDestroy(on connection close or re-registration of the same name).out_dtypeis baked into the generated allocator (so it caches cross-process); a NaNfloatcell reads back as SQL NULL (SQLite coerces NaN REAL to NULL), as inregister_table.fnmust return a 1-D numpy structured array whose dtype isout_dtype; a slice / strided / offset view is handled (the row stride is honored), but a return whose dtype differs fromout_dtypeis read throughout_dtype’s layout and yields undefined values.
numbox.core.bindings.sqlite._typemap
Shared numpy-dtype ↔ SQLite type mapping + fixed-width string helpers.
Used by the read-only vtable (sqlite.vtable), the table-valued-function mechanism, and query_to_array. The dtype tags are the single source of truth for how a numpy column maps to a SQLite column type and how its bytes are read/written.
- numbox.core.bindings.sqlite._typemap.utf32_to_utf8(src, n_codepoints, dst)[source]
dstmust hold at least4 * n_codepoints + 1bytes.
- numbox.core.bindings.sqlite._typemap.utf8_to_utf32(src, nbytes, dst, width_cp)[source]
Decode the UTF-8 bytes at
src(lengthnbytes) into up towidth_cplittle-endian uint32 code points atdst; NUL-pad the remainder. Malformed input (bad continuation byte, surrogate, overlong encoding, out-of-range) decodes to U+FFFD. Returns the number of code points written.dstmay be misaligned (writes are align=1).