Skip to content

Commit 89b3a3a

Browse files
TrevorBurnhambyteforge38
TrevorBurnham
and
byteforge38
committed
sqlite: add virtual table support via createModule()
Expose SQLite's virtual table API through a new `database.createModule(name, options)` method, wrapping `sqlite3_create_module_v2()`. This enables read-only virtual tables backed by JavaScript data sources, usable either as an eponymous table (`SELECT * FROM module_name`) or via `CREATE VIRTUAL TABLE t USING module_name`. Hidden columns pass parameters using table-valued function syntax (`SELECT * FROM module_name(param1, param2)`). `options` accepts `columns`, `rows`, `directOnly`, and `useBigIntArguments`. Column types are validated against INTEGER, TEXT, REAL, BLOB, and ANY, and column names are quoted when building the `sqlite3_declare_vtab()` schema. Rebased from #61544, which was opened by byteforge38 and became inactive. Changes on top of that work: - xColumn reports the value each hidden column was constrained to, rather than NULL. SQLite treats xBestIndex's `omit` as a hint, so it may recheck a constraint it already handed to xFilter; against NULL that recheck rejected every row, and `gs(1, 3) WHERE start = 1` returned no rows. - xBestIndex lowers estimatedCost as it consumes constraints. With a constant cost the planner was free to pick the unconstrained plan and recheck afterwards, so a correlated parameter such as `FROM t, gs(t.a, t.a + 1)` also returned no rows. - Violations of the iteration protocol report a SQLite error instead of calling PropagateJSError with no JavaScript exception pending. That left `.all()` returning undefined and `exec()` reporting success. - xBestIndex passes the constrained hidden-column indices to xFilter through idxStr rather than an int bitmask, which previously aliased for parameter indices at or above the width of an int. - xFilter, xNext, and xColumn take a CallbackDepthGuard. Without it close() from inside rows(), an iterator's next(), or a row getter finalized the statement that SQLite was still stepping, crashing the process. - xClose calls the iterator's return() method so generator `finally` blocks run when SQLite stops stepping early, as it does for LIMIT or a `break` out of a for...of loop. It is skipped while tearing down from ~StatementSync or ~DatabaseSync, which run from garbage collection callbacks where JavaScript cannot be executed; an abandoned generator does not run `finally` in JavaScript either. It is also skipped when an error is already pending, so that error still reaches the caller. - VirtualTableModule holds a BaseObjectWeakPtr<DatabaseSync> to match UserDefinedFunction instead of a raw pointer. - createModule() rejects being called from an authorizer callback. - Documents that values yielded by rows() follow the usual conversion rules, so a number is stored as REAL and a BigInt as INTEGER even when a column declares INTEGER, since virtual tables do not apply column affinity to the values they return. Refs: #61544 Refs: #63826 Fixes: #61539 Co-authored-by: byteforge38 <stormcraft318@gmail.com> Signed-off-by: Trevor Burnham <trevorburnham@gmail.com> Assisted-by: Claude Opus 5
1 parent 68e0ce0 commit 89b3a3a

4 files changed

Lines changed: 1691 additions & 0 deletions

File tree

‎doc/api/sqlite.md‎

Lines changed: 109 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -864,6 +864,114 @@ console.log(allUsers);
864864
// ]
865865
```
866866

867+
### `database.createModule(name, options)`
868+
869+
<!-- YAML
870+
added: REPLACEME
871+
-->
872+
873+
* `name` {string} The name of the virtual table module. This name is used in
874+
`CREATE VIRTUAL TABLE ... USING name` statements and as an eponymous table
875+
name.
876+
* `options` {Object} Module configuration settings.
877+
* `columns` {Array} An array of column definitions. Each element is an object
878+
with the following properties:
879+
* `name` {string} The name of the column.
880+
* `type` {string} The declared type of the column. Must be one of
881+
`'INTEGER'`, `'TEXT'`, `'REAL'`, `'BLOB'`, or `'ANY'`.
882+
* `hidden` {boolean} If `true`, the column is hidden and acts as a
883+
parameter for table-valued function usage. **Default:** `false`.
884+
* `rows` {Function} A function called to produce rows when the virtual table
885+
is queried. The function receives values for hidden columns (parameters) as
886+
arguments, in the order they are defined. Must return an iterable (such as
887+
an array or generator) where each element is an array of column values.
888+
* `directOnly` {boolean} If `true`, the virtual table can only be used in
889+
top-level SQL statements and cannot be used inside triggers or views.
890+
**Default:** `false`.
891+
* `useBigIntArguments` {boolean} If `true`, integer parameters passed to
892+
`rows` are converted to `BigInt`s. **Default:** `false`.
893+
894+
Registers a virtual table module with the database. This method is a wrapper
895+
around [`sqlite3_create_module_v2()`][]. Virtual tables allow JavaScript code
896+
to provide the backing data for SQL tables. The registered module can be used
897+
in two ways:
898+
899+
* **Eponymous table**: Query the module name directly without creating a table
900+
(e.g., `SELECT * FROM module_name`).
901+
* **Named virtual table**: Use `CREATE VIRTUAL TABLE t USING module_name` to
902+
create a persistent virtual table.
903+
904+
Hidden columns can be used to pass parameters to the `rows` function using
905+
table-valued function syntax (e.g., `SELECT * FROM module_name(param1, param2)`).
906+
907+
Values yielded by `rows` follow the conversion rules in [Type conversion between
908+
JavaScript and SQLite][]: a {number} is stored as `REAL` and a {bigint} is
909+
stored as `INTEGER`, regardless of the column's declared `type`. Unlike an
910+
ordinary table, a virtual table does not apply column affinity to the values it
911+
returns, so yield a {bigint} when a column needs `INTEGER` storage:
912+
913+
```js
914+
db.createModule('counter', {
915+
columns: [{ name: 'value', type: 'INTEGER' }],
916+
*rows() {
917+
yield [1]; // typeof(value) is 'real'
918+
yield [2n]; // typeof(value) is 'integer'
919+
},
920+
});
921+
```
922+
923+
```cjs
924+
const { DatabaseSync } = require('node:sqlite');
925+
926+
const db = new DatabaseSync(':memory:');
927+
928+
db.createModule('generate_series', {
929+
columns: [
930+
{ name: 'value', type: 'INTEGER' },
931+
{ name: 'start', type: 'INTEGER', hidden: true },
932+
{ name: 'stop', type: 'INTEGER', hidden: true },
933+
{ name: 'step', type: 'INTEGER', hidden: true },
934+
],
935+
*rows(start, stop, step) {
936+
start ??= 0;
937+
stop ??= 10;
938+
step ??= 1;
939+
for (let i = start; i <= stop; i += step) {
940+
yield [i];
941+
}
942+
},
943+
});
944+
945+
console.log(db.prepare('SELECT * FROM generate_series(1, 5, 1)').all());
946+
// Prints: [ { value: 1 }, { value: 2 }, { value: 3 }, { value: 4 }, { value: 5 } ]
947+
```
948+
949+
```mjs
950+
import { DatabaseSync } from 'node:sqlite';
951+
952+
const db = new DatabaseSync(':memory:');
953+
954+
db.createModule('generate_series', {
955+
columns: [
956+
{ name: 'value', type: 'INTEGER' },
957+
{ name: 'start', type: 'INTEGER', hidden: true },
958+
{ name: 'stop', type: 'INTEGER', hidden: true },
959+
{ name: 'step', type: 'INTEGER', hidden: true },
960+
],
961+
*rows(start, stop, step) {
962+
start ??= 0;
963+
stop ??= 10;
964+
step ??= 1;
965+
for (let i = start; i <= stop; i += step) {
966+
yield [i];
967+
}
968+
},
969+
});
970+
971+
console.log(db.prepare('SELECT * FROM generate_series(1, 5, 1)').all());
972+
// Prints: [ { value: 1 }, { value: 2 }, { value: 3 }, { value: 4 }, { value: 5 } ]
973+
```
974+
867975
### `database.createSession([options])`
868976
869977
<!-- YAML
@@ -1935,6 +2043,7 @@ callback function to indicate what type of operation is being authorized.
19352043
[`sqlite3_column_origin_name()`]: https://www.sqlite.org/c3ref/column_database_name.html
19362044
[`sqlite3_column_table_name()`]: https://www.sqlite.org/c3ref/column_database_name.html
19372045
[`sqlite3_create_function_v2()`]: https://www.sqlite.org/c3ref/create_function.html
2046+
[`sqlite3_create_module_v2()`]: https://www.sqlite.org/c3ref/create_module.html
19382047
[`sqlite3_create_window_function()`]: https://www.sqlite.org/c3ref/create_function.html
19392048
[`sqlite3_db_filename()`]: https://sqlite.org/c3ref/db_filename.html
19402049
[`sqlite3_deserialize()`]: https://sqlite.org/c3ref/deserialize.html

0 commit comments

Comments
 (0)