Passing Virtual Table Column Metadata to sqlite3_exec Callback in SQLite

Bridging State Between xColumn and sqlite3_exec Callback in Virtual Table Modules

Virtual Table Column Metadata Isolation in xColumn and Callback Contexts

The core challenge revolves around propagating metadata associated with virtual table column values from the xColumn method of a virtual table implementation to the callback function invoked by sqlite3_exec. In SQLite’s virtual table architecture, xColumn is responsible for dynamically generating column values during query execution, while sqlite3_exec operates at a higher level, processing rows through a user-defined callback. The disconnect arises because the xColumn method operates within the context of a cursor iterating over rows, whereas the sqlite3_exec callback operates in a separate context with no direct access to the internal state of the virtual table or its cursors. This separation prevents direct sharing of metadata (e.g., pointers to auxiliary data structures, formatting details, or external resource identifiers) between these two critical phases of query execution.

The problem is exacerbated by the fact that the sqlite3_exec callback’s user data parameter (provided as the fourth argument to sqlite3_exec and passed as the first argument to the callback) is not directly accessible during xColumn execution. Conversely, any state managed within xColumn (e.g., transient metadata generated per column) is not automatically preserved for the callback. This creates a state propagation gap where metadata generated during column value materialization cannot be directly utilized during row processing in the callback.

Limitations of Subtype Encoding and Global/Local Function Overloading

One documented workaround involves using sqlite3_result_subtype in xColumn to tag column values with an 8-bit identifier (0–255) and later retrieving this identifier via sqlite3_value_subtype in a custom scalar function (e.g., attr()) invoked within the query. This approach associates metadata with column values by encoding a reference (e.g., a column index) into the subtype field, which is then used to look up metadata in a structure accessible to the callback. However, this method is constrained by the 8-bit subtype limit, making it unsuitable for virtual tables with more than 255 columns or scenarios requiring complex metadata beyond simple indices.

Another limitation stems from reliance on global and local function overloading. For instance, a virtual table module might define a global attr() function during module initialization and override it with a table-specific version during virtual table creation. This allows metadata retrieval functions to resolve contextually, but it introduces fragility in multi-threaded environments or when multiple virtual table instances are active simultaneously. Furthermore, this approach necessitates embedding metadata retrieval logic directly into the SQL query (e.g., SELECT a, attr(a, 'format') FROM table), which may not align with use cases where the callback itself requires implicit access to metadata without query modification.

Context-Aware Metadata Propagation via Auxiliary Data and Row Identification

A robust solution requires a mechanism to bind metadata to the runtime context of a specific query execution. SQLite’s auxiliary data API (sqlite3_set_auxdata/sqlite3_get_auxdata) provides a way to attach transient, statement-specific data to column values, which can be retrieved later during the same statement’s execution. By storing metadata as auxiliary data in xColumn and accessing it via a custom function within the query, the callback can receive metadata as part of the result set. However, this still requires explicit inclusion of metadata-retrieval functions in the SQL query.

For scenarios where modifying the query is impractical, an alternative approach involves leveraging the virtual table’s rowid and column indices to map metadata in a global or connection-specific hash table. During xColumn, the module stores metadata in a structure keyed by the current rowid and column index. The callback then retrieves metadata using the rowid (exposed as a hidden column or explicitly selected) and column indices. This method avoids subtype limitations but introduces complexity in managing the lifecycle of the metadata store, particularly in concurrent environments.

Step-by-Step Implementation of Subtype-Based Metadata Tagging

Step 1: Define a Custom Scalar Function for Metadata Retrieval
Create a scalar function attr() that accepts a column value and a metadata key (e.g., 'format', 'formula'). This function will resolve the metadata associated with the column value using its subtype as an index:

static void attrFunc(
  sqlite3_context *ctx,
  int argc,
  sqlite3_value **argv
) {
  int colIndex = sqlite3_value_subtype(argv[0]);
  const char *key = (const char*)sqlite3_value_text(argv[1]);
  // Fetch metadata from a structure indexed by colIndex and key
  MyMetadata *metadata = getMetadata(colIndex, key);
  sqlite3_result_text(ctx, metadata->value, -1, SQLITE_TRANSIENT);
}

Step 2: Tag Column Values with Subtype Indices in xColumn
In the virtual table’s xColumn method, use sqlite3_result_subtype to embed the column index into the result value:

static int xColumn(
  sqlite3_vtab_cursor *cur,
  sqlite3_context *ctx,
  int colIndex
) {
  // Generate column value (e.g., from an Excel cell)
  sqlite3_result_text(ctx, cellValue, -1, SQLITE_TRANSIENT);
  // Tag the value with the column index via subtype
  sqlite3_result_subtype(ctx, (unsigned char)colIndex);
  return SQLITE_OK;
}

Step 3: Invoke the Metadata Function in the Query
Include the attr() function in the SQL query to retrieve metadata alongside column values:

SELECT a, attr(a, 'format') AS format FROM vtab;

Step 4: Manage Metadata Storage
Maintain a global or module-specific data structure (e.g., a hash table) that maps (column index, metadata key) to metadata values. Populate this structure during virtual table initialization or row materialization.

Implementing Rowid-Keyed Metadata Storage for Large-Scale Data

For virtual tables exceeding 255 columns or requiring richer metadata, replace subtype tagging with a rowid-keyed metadata store:

Step 1: Expose Rowid in the Virtual Table
Ensure the virtual table’s xRowid method is implemented to provide a unique row identifier:

static int xRowid(sqlite3_vtab_cursor *cur, sqlite3_int64 *pRowid) {
  *pRowid = cur->currentRowId;
  return SQLITE_OK;
}

Step 2: Store Metadata During xColumn Execution
During xColumn, use the rowid and column index as a composite key to store metadata in a thread-safe data structure:

static int xColumn(
  sqlite3_vtab_cursor *cur,
  sqlite3_context *ctx,
  int colIndex
) {
  sqlite3_int64 rowid;
  xRowid(cur, &rowid);
  // Store metadata in a global map: MetadataMap[(rowid, colIndex)] = metadata
  storeMetadata(rowid, colIndex, metadata);
  // Generate column value
  sqlite3_result_text(ctx, value, -1, SQLITE_TRANSIENT);
  return SQLITE_OK;
}

Step 3: Retrieve Metadata in the Callback
In the sqlite3_exec callback, extract the rowid from the result set (if selected) and use it to fetch metadata:

int callback(void *userData, int argc, char **argv, char **colNames) {
  sqlite3_int64 rowid = atoll(argv[0]); // Assuming rowid is the first column
  for (int i = 1; i < argc; i++) {
    MyMetadata *metadata = getMetadata(rowid, i - 1);
    // Process metadata
  }
  return SQLITE_OK;
}

Step 4: Manage Metadata Lifecycle
Implement cleanup logic to remove metadata entries when rows are deleted or the cursor is closed, possibly via the virtual table’s xClose or xFilter methods.

Hybrid Approach: Combining Subtype and Rowid for Scalability

For scenarios requiring both scalability and minimal query modification, combine subtype tagging with rowid-based lookups:

1. Use the subtype to encode a unique identifier for a metadata block rather than a column index.
2. During xColumn, store metadata in a structure keyed by this identifier and the rowid.
3. In the callback, extract both the subtype identifier and rowid from the result set to fetch metadata.

This approach decouples metadata identification from column count limitations but requires careful management of identifier uniqueness and lifecycle.

Conclusion

The optimal strategy for passing metadata from xColumn to sqlite3_exec callbacks depends on the specific constraints of column count, metadata complexity, and query flexibility. Subtype-based tagging suits smaller-scale scenarios with query control, while rowid-keyed storage offers scalability at the cost of increased architectural complexity. Developers must weigh these trade-offs against their application’s requirements to implement a robust and efficient solution.