Eponymous Virtual Table Behavior Across Attached Databases in SQLITE

Ephemeral Nature of Eponymous Virtual Tables in Non-Main Schemas

Issue Overview

The core challenge revolves around the inability to directly reference eponymous virtual tables (EVTs) in attached SQLite databases using schema-qualified syntax (e.g., SELECT * FROM temp.dbstat). EVTs like dbstat are designed to exist implicitly in the main schema and do not automatically propagate to other attached schemas (e.g., temp, test). When attempting to access temp.dbstat, the query returns data from the main schema’s dbstat instead of generating a schema-specific instance. This contradicts the behavior of non-virtual system tables like sqlite_schema, which are schema-aware and return metadata specific to the attached database when qualified (e.g., temp.sqlite_schema).

Eponymous virtual tables rely on a registration mechanism that binds them to the main schema by default. Their implementation lacks the ability to dynamically resolve the target schema from the qualifier in the SQL statement. The xOpen method of the virtual table module, responsible for initializing cursor instances, does not receive the schema name as an argument. This omission prevents the virtual table from distinguishing between attached databases during query execution. The absence of schema context in xOpen forces EVTs to operate under the assumption that all interactions occur within main, leading to misdirected queries when schema qualifiers are used.

Architectural Constraints and Versioning Limitations

The root cause lies in SQLite’s virtual table API design and the lifecycle of schema attachments. Three critical factors contribute to this behavior:

1. Schema Binding of Eponymous Virtual Tables:
   EVTs are registered exclusively in the main schema during database initialization. The SQLite engine does not automatically create EVT instances in attached schemas because their existence depends on explicit registration via the sqlite3_create_module_v2() API. This registration is not replicated when new schemas are attached, as the module’s xCreate or xConnect methods are not invoked for schemas beyond main.

2. xOpen Argument Signature:
   The xOpen method of a virtual table module receives only two arguments: a pointer to the virtual table object and a pointer to the cursor object. Unlike xCreate/xConnect, which receive an array of arguments (argv) including the schema name, xOpen lacks access to the schema context. This prevents cursor initialization from tailoring results to the schema specified in the query. For example, when querying temp.dbstat, the xOpen method cannot discern that the temp schema was requested, leading it to default to main.

3. Virtual Table API Versioning (iVersion):
   SQLite’s virtual table API uses the iVersion field to manage backward compatibility. Modules targeting API versions prior to 3 require xOpen to conform to the legacy two-argument signature. Increasing iVersion to 5 (or higher) would enable the use of an extended xOpen method with additional parameters, such as the schema name. However, this is not currently supported in SQLite’s public API, and existing modules like dbstat are hardcoded to use the legacy xOpen signature.

These constraints create a mismatch between user expectations (schema-aware EVTs) and the actual implementation (schema-agnostic EVTs). The assumption that EVTs are singletons tied to main is baked into both the API and the modules themselves, making ad-hoc queries across schemas impossible without workarounds.

Implementing Schema-Aware EVT Access and Workarounds

Step 1: Explicitly Create Temporary Virtual Table Instances

To access EVT-like functionality in non-main schemas, create a temporary virtual table that proxies the target schema. For example, instead of relying on the implicit dbstat EVT, explicitly define a virtual table in the desired schema:

CREATE VIRTUAL TABLE temp.stat USING dbstat(main);
SELECT * FROM temp.stat;  -- Accesses dbstat for main via temp.stat

This workaround binds the virtual table to the temp schema and explicitly specifies the target database (main) as a module argument. While this requires additional setup, it grants precise control over which schema’s data is accessed.

Step 2: Custom Virtual Table Modules with Schema Tracking

For custom EVTs requiring schema awareness, modify the module’s xConnect method to capture the schema name during initialization. Store this name in the virtual table’s instance data structure, and reference it during xBestIndex and xFilter to constrain results to the target schema.

Example modifications:

1. Extend xConnect to Capture Schema Name:

   static int xConnect(
     sqlite3 *db,
     void *pAux,
     int argc, const char *const*argv,
     sqlite3_vtab **ppVTab,
     char **pzErr
   ) {
     // argv[0] = schema name (e.g., "temp")
     // argv[1] = table name (e.g., "dbstat")
     MyVTab *pNew = sqlite3_malloc(sizeof(MyVTab));
     pNew->zSchema = sqlite3_mprintf("%s", argv[0]);
     *ppVTab = (sqlite3_vtab*)pNew;
     return SQLITE_OK;
   }
   

2. Use Schema Name During Query Execution:

   static int xFilter(
     sqlite3_vtab_cursor *pCursor,
     int idxNum, const char *idxStr,
     int argc, sqlite3_value **argv
   ) {
     MyCursor *pCur = (MyCursor*)pCursor;
     MyVTab *pVTab = (MyVTab*)pCursor->pVtab;
     // Use pVTab->zSchema to filter results
     return SQLITE_OK;
   }
   

Step 3: Schema Qualification in Application Code

When modifying the virtual table module is impractical, enforce schema context at the application layer. Prefix EVT queries with main. and use aliases or views to simulate schema-specific access:

CREATE VIEW temp.dbstat AS SELECT * FROM main.dbstat WHERE schema = 'temp';  -- Hypothetical

While this approach is limited by SQLite’s inability to override EVT bindings, it demonstrates how schema context can be propagated through auxiliary objects.

Step 4: Monitor SQLite API Updates for Extended xOpen Signature

Track changes to SQLite’s virtual table API, particularly the addition of schema context to xOpen. If a future version introduces an xOpen method with a schema parameter, migrate custom modules to leverage this for dynamic schema resolution.

Step 5: Utilize In-Memory Databases for Isolated EVT Instances

For testing or scenarios requiring multiple EVT instances, attach in-memory databases (:memory:) and register EVTs separately in each schema. This isolates EVT instances but requires manual schema management:

ATTACH ':memory:' AS mem1;
ATTACH ':memory:' AS mem2;
-- Register EVT in mem1 and mem2 via custom application code

By systematically addressing the architectural limitations through module customization, explicit virtual table creation, and schema-aware query patterns, developers can approximate the desired behavior of schema-qualified EVTs while adhering to SQLite’s current constraints.