• Introduction
• Managing Pointer-to-Struct Lifetimes
• sqlite3_module
• sqlite3_index_info

See also: C structs in JS

Virtual tables are one of sqlite's most powerful features, providing clients with a way to access near-arbitrary data through SQL. With great power, however, comes non-trivial complexity.

The virtual table (a.k.a. vtab) API is an advanced feature of the library with a great deal of documentation written about it and example programs demonstrating it. These docs do not endeavor to teach users about how to write their own virtual tables, and instead focus only on the JS-specific components of doing so.

The JS API for virtual tables is close to a one-to-one mapping of the C API, differing only in the addition of some helper routines for accessing and manipulating the C-side state used by the APIs. The APIs described here are not necessary for defining virtual tables in JS, but are provided solely to simplify the process. Virtual tables can be implemented using only the C-style API if clients prefer.

Managing Pointer-to-Struct Lifetimes

The sqlite3_module interface has clear lifetime rules for sqlite3_vtab and sqlite3_vtab_cursor objects which work like this:

• sqlite3_vtab:

  • xCreate() or xConnect() allocate an instance and write its pointer into that function's output pointer argument.
  • Several other methods receive that pointer as an argument.
  • xDestroy() or xDisconnect() are passed the same pointer and are expected to free its memory.

• sqlite3_vtab_cursor:

  • xOpen() allocates an instance and writes its pointer into that function's output pointer argument.
  • Several other methods receive that pointer as an argument.
  • xClose() is passed the same pointer and is expected to free its memory.

JS code cannot do very much with the raw pointers and requires wrapper objects in order to access and manipulate them, as described in the C structs docs. Similarly, JS-side instances cannot "extend" those types in the way C code would, but they can attach any implementation-specific state to the JS instances (so, in effect, have an easier way to customize their state than their C counterparts do).

The sqlite3.vtab object provides APIs to simplify the lifetime management and pointer-mapping of these objects in JS. These APIs work identically for sqlite3_vtab and sqlite3_vtab_cursor but are used at different points in the sqlite3_module interface.

The API objects are named vtab.xVtab and vtab.xCursor, for sqlite3_vtab and sqlite3_vtab_cursor types, respectively, and their methods are described below.

The main sqlite3 JS unit tests have example virtual tables which demonstrate the use of these APIs.

create()

Passed the output pointer argument from an appropriate sqlite3_module method (see below), this creates a new struct instance, writes its pointer value to the given output pointer, and returns that object. It will throw if allocation of the struct instance fails.

Uses:

• sqlite3.vtab.xVtab.create(): to be called from sqlite3_module::xConnect() or xCreate() implementations.
• sqlite3.vtab.xCursor.create(): to be called from xOpen().

get()

Passed a struct pointer from an appropriate sqlite3_module method (see below), this returns the same struct instance wrapper which was created for that pointer and returned by create().

Uses:

• sqlite3.vtab.xVtab.get(): to be called from sqlite3_module methods which take a (sqlite3_vtab*) pointer except for xDestroy()/xDisconnect(), in which case unget() or dispose().
• sqlite3.vtab.xCursor.get(): to be called from any sqlite3_module methods which take a sqlite3_vtab_cursor* argument except xClose(), in which case use unget() or dispose().

Rule to remember: never call dispose() on an instance returned by this function. Delay that until calling...

unget() and dispose()

unget() is identical to get() but also disconnects the mapping between the given pointer and the returned struct object, such that future calls to this function or get() with the same pointer will return the undefined value. It is up to the caller to call theStruct.dispose() on the returned object before the enclosing function returns.

dispose() works like unget() but calls theStruct.dispose() instead of returning the object. Calling unget() obligates the caller to call dispose() on the returned object when they're done with it.

Uses:

• sqlite3.vtab.xVtab.unget/dispose(): to be called from sqlite3_module::xDisconnect() or xDestroy() implementations or in error handling of a failed xCreate() or xConnect().
• sqlite3.vtab.xCursor.unget/dispose(): to be called from xClose() or during cleanup in a failed xOpen().

sqlite3_module Methods

The sqlite3_module class inherits from the core C struct type and extends the hierarchy with...

setupModule()

sqlite3_module setupModule(options)

Sets up this module object.

A helper to initialize and set up an sqlite3_module() object for later installation into individual databases using sqlite3_create_module(). Requires an object with the following properties:

• methods: an object containing a mapping of properties with the C-side names of the sqlite3_module methods, e.g. xCreate, xBestIndex, etc., to JS implementations for those functions. Certain special-case handling is performed, as described below.

• catchExceptions (default=false): if truthy, the given methods are not mapped as-is, but are instead wrapped inside wrappers which translate exceptions into result codes of SQLITE_ERROR or SQLITE_NOMEM, depending on whether the exception is an sqlite3.WasmAllocError. In the case of the xConnect and xCreate methods, the exception handler also sets the output error string to the exception's error string.
  If catchExceptions is false, it is up to the client to ensure that no exceptions escape the methods, as doing so would move them through the C API, leading to undefined behavior. (VtabHelper.xError() is intended to assist in reporting such exceptions.)

• OPTIONAL struct: a sqlite3_module instance. If not set, one will be created automatically. If the current this is-a sqlite3_module then it is unconditionally used in place of struct.

• OPTIONAL iVersion: if set, it must be an integer value and it gets assigned to the $iVersion member of the struct object. If it's not set then this function attempts to define a value for that property based on the list of methods it has.

Certain methods may refer to the same implementation. To simplify the definition of such methods:

• If methods.xConnect is true then the value of methods.xCreate is used in its place, and vice versa. sqlite treats xConnect/xCreate functions specially if they are exactly the same function (same pointer value).

• If methods.xDisconnect is true then the value of methods.xDestroy is used in its place, and vice versa.

This is to facilitate creation of those methods inline in the passed-in object without requiring the client to explicitly get a reference to one of them in order to assign it to the other one.

The catchExceptions-installed handlers will account for identical references to the above functions and will install the same wrapper function for both so that the underlying library sees the same pointer value for both.

The given methods are expected to return integer values, as expected by the C API. If catchExceptions is truthy, the return value of the wrapped function will be used as-is and will be translated to 0 if the function returns a falsy value (e.g. if it does not have an explicit return). If catchExceptions is not active, the method implementations must explicitly return integer values. Failing to do so will lead to failures, in particular in error cases because those will go unreported.

Throws on error. On success, returns a sqlite3_module object: this or opt.struct or a new sqlite3_module instance, depending on how it's called.

sqlite3_index_info

sqlite3_index_info objects are used for conveying information between sqlite3_module::xBestIndex() implementations and the sqlite3 engine. JS code does not manage lifetimes of these and must not hold on to pointers to them for longer than a function call which receives the pointer as an argument. JS code needs to use capi.sqlite3_index_info wrapper objects to interact with the C-level struct state.

Example:

const xBestIndex = function(pVtab, pIdxInfo){
  const vtab = sqlite3.vtab.xVtab.get(pVtab); // documented above
  const sii = new sqlite3.capi.sqlite3_index_info(pIdxInfo);
  ...
  sii.dispose(); // see notes below
  return 0;
};

When C struct wrappers are constructed with a pointer argument, as shown above, they do not allocate new instances, but instead provide access to existing instances. In such cases, it is not strictly necessary to call their dispose() method, but it is generally a good habit to be in to avoid memory leaks for the cases where it is important.

In additional to the struct-related APIs available to all JS-bound C structs, sqlite3_index_info has the following helper methods installed in its prototype, available to all instances:

• nthConstraint(n, asPtr=false)
  If n is >=0 and less than this.$nConstraint, this function returns either a WASM pointer to the 0-based nth entry of this.$aConstraint (if passed a truthy 2nd argument) or an sqlite3_index_info.sqlite3_index_constraint object wrapping that address (if passed a falsy value or no 2nd argument). Returns a falsy value if n is out of range.
• nthConstraintUsage(n, asPtr=false)
  Works identically to nthConstraint() but returns state from this.$aConstraintUsage, so returns an sqlite3_index_info.sqlite3_index_constraint_usage instance if passed no 2nd argument or a falsy 2nd argument.
• nthOrderBy(n, asPtr=false)
  If n is >=0 and less than this.$nOrderBy, this function returns either a WASM pointer to the 0-based nth entry of this.$aOrderBy (if passed a truthy 2nd argument) or an sqlite3_index_info.sqlite3_index_orderby object wrapping that address (if passed a falsy value or no 2nd argument). Returns a falsy value if n is out of range.

These are all demonstrated in the main unit test script.