nodejs/node · #65787
sqlite: add virtual table support via createModule()
doc/api/sqlite.md109 + / 0 −
@@ -864,6 +864,114 @@ console.log(allUsers); // ] ``` +### `database.createModule(name, options)`++<!-- YAML+added: REPLACEME+-->++* `name` {string} The name of the virtual table module. This name is used in+ `CREATE VIRTUAL TABLE ... USING name` statements and as an eponymous table+ name.+* `options` {Object} Module configuration settings.+ * `columns` {Array} An array of column definitions. Each element is an object+ with the following properties:+ * `name` {string} The name of the column.+ * `type` {string} The declared type of the column. Must be one of+ `'INTEGER'`, `'TEXT'`, `'REAL'`, `'BLOB'`, or `'ANY'`.+ * `hidden` {boolean} If `true`, the column is hidden and acts as a+ parameter for table-valued function usage. **Default:** `false`.+ * `rows` {Function} A function called to produce rows when the virtual table+ is queried. The function receives values for hidden columns (parameters) as+ arguments, in the order they are defined. Must return an iterable (such as+ an array or generator) where each element is an array of column values.+ * `directOnly` {boolean} If `true`, the virtual table can only be used in+ top-level SQL statements and cannot be used inside triggers or views.+ **Default:** `false`.+ * `useBigIntArguments` {boolean} If `true`, integer parameters passed to+ `rows` are converted to `BigInt`s. **Default:** `false`.++Registers a virtual table module with the database. This method is a wrapper+around [`sqlite3_create_module_v2()`][]. Virtual tables allow JavaScript code+to provide the backing data for SQL tables. The registered module can be used+in two ways:++* **Eponymous table**: Query the module name directly without creating a table+ (e.g., `SELECT * FROM module_name`).+* **Named virtual table**: Use `CREATE VIRTUAL TABLE t USING module_name` to+ create a persistent virtual table.++Hidden columns can be used to pass parameters to the `rows` function using+table-valued function syntax (e.g., `SELECT * FROM module_name(param1, param2)`).++Values yielded by `rows` follow the conversion rules in [Type conversion between+JavaScript and SQLite][]: a {number} is stored as `REAL` and a {bigint} is+stored as `INTEGER`, regardless of the column's declared `type`. Unlike an+ordinary table, a virtual table does not apply column affinity to the values it+returns, so yield a {bigint} when a column needs `INTEGER` storage:++```js+db.createModule('counter', {+ columns: [{ name: 'value', type: 'INTEGER' }],+ *rows() {+ yield [1]; // typeof(value) is 'real'+ yield [2n]; // typeof(value) is 'integer'+ },+});+```++```cjs+const { DatabaseSync } = require('node:sqlite');++const db = new DatabaseSync(':memory:');++db.createModule('generate_series', {+ columns: [+ { name: 'value', type: 'INTEGER' },+ { name: 'start', type: 'INTEGER', hidden: true },+ { name: 'stop', type: 'INTEGER', hidden: true },+ { name: 'step', type: 'INTEGER', hidden: true },+ ],+ *rows(start, stop, step) {+ start ??= 0;+ stop ??= 10;+ step ??= 1;+ for (let i = start; i <= stop; i += step) {+ yield [i];+ }+ },+});++console.log(db.prepare('SELECT * FROM generate_series(1, 5, 1)').all());+// Prints: [ { value: 1 }, { value: 2 }, { value: 3 }, { value: 4 }, { value: 5 } ]+```++```mjs+import { DatabaseSync } from 'node:sqlite';++const db = new DatabaseSync(':memory:');++db.createModule('generate_series', {+ columns: [+ { name: 'value', type: 'INTEGER' },+ { name: 'start', type: 'INTEGER', hidden: true },+ { name: 'stop', type: 'INTEGER', hidden: true },+ { name: 'step', type: 'INTEGER', hidden: true },+ ],+ *rows(start, stop, step) {+ start ??= 0;+ stop ??= 10;+ step ??= 1;+ for (let i = start; i <= stop; i += step) {+ yield [i];+ }+ },+});++console.log(db.prepare('SELECT * FROM generate_series(1, 5, 1)').all());+// Prints: [ { value: 1 }, { value: 2 }, { value: 3 }, { value: 4 }, { value: 5 } ]+```+ ### `database.createSession([options])` <!-- YAML@@ -1935,6 +2043,7 @@ callback function to indicate what type of operation is being authorized. [`sqlite3_column_origin_name()`]: https://www.sqlite.org/c3ref/column_database_name.html [`sqlite3_column_table_name()`]: https://www.sqlite.org/c3ref/column_database_name.html [`sqlite3_create_function_v2()`]: https://www.sqlite.org/c3ref/create_function.html+[`sqlite3_create_module_v2()`]: https://www.sqlite.org/c3ref/create_module.html [`sqlite3_create_window_function()`]: https://www.sqlite.org/c3ref/create_function.html [`sqlite3_db_filename()`]: https://sqlite.org/c3ref/db_filename.html [`sqlite3_deserialize()`]: https://sqlite.org/c3ref/deserialize.htmlsrc/node_sqlite.cc718 + / 0 −
@@ -16,9 +16,12 @@ #include "util-inl.h" #include <array>+#include <charconv> #include <cinttypes> #include <cmath>+#include <cstring> #include <limits>+#include <string> namespace node { namespace sqlite {@@ -1012,6 +1015,483 @@ BaseObjectPtr<DatabaseSyncLimits> DatabaseSyncLimits::Create( return MakeBaseObject<DatabaseSyncLimits>(env, obj, std::move(database)); } +// ---------------------------------------------------------------------------+// VirtualTableModule+// ---------------------------------------------------------------------------++VirtualTableModule::VirtualTableModule(Environment* env,+ BaseObjectWeakPtr<DatabaseSync> db,+ Local<Function> rows_fn,+ std::string&& schema_sql,+ int num_columns,+ std::vector<int>&& hidden_col_indices,+ bool use_bigint_args,+ bool direct_only)+ : env_(env),+ db_(std::move(db)),+ rows_fn_(env->isolate(), rows_fn),+ schema_sql_(std::move(schema_sql)),+ num_columns_(num_columns),+ hidden_col_indices_(std::move(hidden_col_indices)),+ use_bigint_args_(use_bigint_args),+ direct_only_(direct_only),+ module_def_({}) {+ // Initialize the sqlite3_module definition. Each VirtualTableModule instance+ // gets its own copy to avoid thread-safety issues with worker threads.+ module_def_.iVersion = 1;+ module_def_.xCreate = VirtualTableModule::xCreate;+ module_def_.xConnect = VirtualTableModule::xCreate;+ module_def_.xBestIndex = VirtualTableModule::xBestIndex;+ module_def_.xDisconnect = VirtualTableModule::xDisconnect;+ module_def_.xDestroy = VirtualTableModule::xDestroy;+ module_def_.xOpen = VirtualTableModule::xOpen;+ module_def_.xClose = VirtualTableModule::xClose;+ module_def_.xFilter = VirtualTableModule::xFilter;+ module_def_.xNext = VirtualTableModule::xNext;+ module_def_.xEof = VirtualTableModule::xEof;+ module_def_.xColumn = VirtualTableModule::xColumn;+ module_def_.xRowid = VirtualTableModule::xRowid;++ // Build mapping from schema column index to row array index.+ // Visible columns are numbered sequentially; hidden columns map to -1.+ col_index_map_.assign(num_columns, 0);+ for (int idx : hidden_col_indices_) {+ col_index_map_[idx] = -1;+ }+ int visible_idx = 0;+ for (int i = 0; i < num_columns; i++) {+ if (col_index_map_[i] < 0) {+ continue;+ }+ col_index_map_[i] = visible_idx++;+ }+}++VirtualTableModule::~VirtualTableModule() {}++int VirtualTableModule::PropagateJSError() {+ if (db_) {+ db_->SetIgnoreNextSQLiteError(true);+ }+ return SQLITE_ERROR;+}++int VirtualTableModule::ReportProtocolError(sqlite3_vtab* vtab,+ const char* message) {+ sqlite3_free(vtab->zErrMsg);+ vtab->zErrMsg = sqlite3_mprintf("%s", message);+ return SQLITE_ERROR;+}++bool VirtualTableModule::CanCallIntoJS() const {+ return db_ && !db_->IsInDestructor();+}++void VirtualTableModule::ReleaseHiddenValues(NodeVTabCursor* cursor) {+ for (sqlite3_value*& value : cursor->hidden_values) {+ if (value != nullptr) {+ sqlite3_value_free(value);+ value = nullptr;+ }+ }+}++int VirtualTableModule::xCreate(sqlite3* db,+ void* pAux,+ int argc,+ const char* const* argv,+ sqlite3_vtab** ppVTab,+ char** pzErr) {+ VirtualTableModule* mod = static_cast<VirtualTableModule*>(pAux);++ int rc = sqlite3_declare_vtab(db, mod->schema_sql_.c_str());+ if (rc != SQLITE_OK) {+ *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(db));+ return rc;+ }++ if (mod->direct_only_) {+ sqlite3_vtab_config(db, SQLITE_VTAB_DIRECTONLY);+ }++ NodeVTab* vtab = new NodeVTab();+ memset(&vtab->base, 0, sizeof(vtab->base));+ vtab->module = mod;+ *ppVTab = &vtab->base;+ return SQLITE_OK;+}++int VirtualTableModule::xBestIndex(sqlite3_vtab* pVTab,+ sqlite3_index_info* pInfo) {+ NodeVTab* vtab = reinterpret_cast<NodeVTab*>(pVTab);+ VirtualTableModule* mod = vtab->module;+ int num_hidden = static_cast<int>(mod->hidden_col_indices_.size());+ int argv_index = 0;+ // Comma-separated list of the hidden column indices that received a+ // constraint, in argv order. Passed to xFilter via idxStr so it can map each+ // argv value back to the right parameter. A bitmask in idxNum would cap the+ // number of parameters at the width of an int.+ std::string idx_str;++ // For each hidden column (parameter), look for a usable EQ constraint.+ for (int hidden_idx = 0; hidden_idx < num_hidden; hidden_idx++) {+ int col = mod->hidden_col_indices_[hidden_idx];++ for (int i = 0; i < pInfo->nConstraint; i++) {+ if (pInfo->aConstraint[i].iColumn == col &&+ pInfo->aConstraint[i].usable &&+ pInfo->aConstraint[i].op == SQLITE_INDEX_CONSTRAINT_EQ) {+ argv_index++;+ pInfo->aConstraintUsage[i].argvIndex = argv_index;+ pInfo->aConstraintUsage[i].omit = 1;+ if (!idx_str.empty()) {+ idx_str += ',';+ }+ idx_str += std::to_string(hidden_idx);+ break;+ }+ }+ }++ if (!idx_str.empty()) {+ pInfo->idxStr = sqlite3_mprintf("%s", idx_str.c_str());+ if (pInfo->idxStr == nullptr) {+ return SQLITE_NOMEM;+ }+ pInfo->needToFreeIdxStr = 1;+ }++ pInfo->idxNum = argv_index;++ // Each consumed constraint has to make the plan look cheaper, or the planner+ // is free to choose the unconstrained plan and recheck the constraints+ // afterwards. That recheck is what turns an unpicked plan into an empty+ // result for table-valued syntax.+ double estimated_rows = 1000.0;+ for (int i = 0; i < argv_index; i++) {+ estimated_rows /= 10.0;+ }+ estimated_rows = std::max(estimated_rows, 1.0);+ pInfo->estimatedRows = static_cast<sqlite3_int64>(estimated_rows);+ pInfo->estimatedCost = estimated_rows;+ return SQLITE_OK;+}++int VirtualTableModule::xDisconnect(sqlite3_vtab* pVTab) {+ NodeVTab* vtab = reinterpret_cast<NodeVTab*>(pVTab);+ delete vtab;+ return SQLITE_OK;+}++int VirtualTableModule::xDestroy(sqlite3_vtab* pVTab) {+ return xDisconnect(pVTab);+}++int VirtualTableModule::xOpen(sqlite3_vtab* pVTab,+ sqlite3_vtab_cursor** ppCursor) {+ NodeVTab* vtab = reinterpret_cast<NodeVTab*>(pVTab);+ NodeVTabCursor* cursor = new NodeVTabCursor();+ memset(&cursor->base, 0, sizeof(cursor->base));+ cursor->module = vtab->module;+ cursor->hidden_values.assign(vtab->module->num_columns_, nullptr);+ cursor->rowid = 0;+ cursor->done = true;+ *ppCursor = &cursor->base;+ return SQLITE_OK;+}++int VirtualTableModule::xClose(sqlite3_vtab_cursor* pCursor) {+ NodeVTabCursor* cursor = reinterpret_cast<NodeVTabCursor*>(pCursor);+ VirtualTableModule* mod = cursor->module;++ // Close the iterator so generator `finally` blocks still run when SQLite+ // stops stepping early, as it does for LIMIT or a `break` out of a for...of+ // loop. Skipped in two cases:+ //+ // - While the database is being torn down from a destructor, because those+ // run from a garbage collection callback where JavaScript cannot be+ // executed. An abandoned generator does not run `finally` in JavaScript+ // either, so skipping matches the language.+ // - When an error is already pending, because calling into JavaScript would+ // discard it and the caller would see an empty result instead of the error.+ // A generator whose own body threw has already run its `finally` as part of+ // that throw, so this only affects an iterator abandoned while suspended+ // because something else failed.+ if (!cursor->iterator.IsEmpty() && mod->CanCallIntoJS() &&+ !mod->env_->isolate()->HasPendingException()) {+ Environment* env = mod->env_;+ Isolate* isolate = env->isolate();+ HandleScope handle_scope(isolate);+ CallbackDepthGuard callback_guard(mod->db_.get());++ // Scoped above the property lookup so a throwing `return` getter is+ // handled the same way as a throwing `return()` method.+ TryCatch try_catch(isolate);+ Local<Object> iterator = cursor->iterator.Get(isolate);+ Local<Value> return_method;+ if (iterator->Get(env->context(), FIXED_ONE_BYTE_STRING(isolate, "return"))+ .ToLocal(&return_method) &&+ return_method->IsFunction()) {+ USE(return_method.As<Function>()->Call(+ env->context(), iterator, 0, nullptr));+ }++ // Re-throw so that a throwing `finally` is not silently discarded. SQLite+ // discards xClose's return value, so there is no SQLite error here to+ // suppress; calling PropagateJSError would leave the suppression flag set+ // and swallow the next unrelated SQLite error.+ if (try_catch.HasCaught() && !try_catch.HasTerminated()) {+ try_catch.ReThrow();+ }+ }++ ReleaseHiddenValues(cursor);+ cursor->iterator.Reset();+ cursor->current_row.Reset();+ delete cursor;+ return SQLITE_OK;+}++int VirtualTableModule::xFilter(sqlite3_vtab_cursor* pCursor,+ int idxNum,+ const char* idxStr,+ int argc,+ sqlite3_value** argv) {+ NodeVTabCursor* cursor = reinterpret_cast<NodeVTabCursor*>(pCursor);+ VirtualTableModule* mod = cursor->module;+ Environment* env = mod->env_;+ Isolate* isolate = env->isolate();+ HandleScope handle_scope(isolate);+ if (!mod->CanCallIntoJS()) {+ return SQLITE_ERROR;+ }+ CallbackDepthGuard callback_guard(mod->db_.get());++ cursor->rowid = 0;+ cursor->done = false;+ cursor->iterator.Reset();+ cursor->current_row.Reset();+ ReleaseHiddenValues(cursor);++ // Build arguments for rows() from hidden column constraint values.+ // idxStr (set in xBestIndex) lists the hidden column indices that received+ // an EQ constraint, in argv order. Unconstrained parameters stay null.+ int num_hidden = static_cast<int>(mod->hidden_col_indices_.size());+ LocalVector<Value> js_args(isolate, num_hidden);+ for (int i = 0; i < num_hidden; i++) {+ js_args[i] = Null(isolate);+ }++ const char* p = idxStr;+ const char* idx_end = p == nullptr ? nullptr : p + std::strlen(p);+ for (int argv_pos = 0; argv_pos < argc && p != nullptr && p != idx_end;+ argv_pos++) {+ int hidden_idx = 0;+ auto [next, ec] = std::from_chars(p, idx_end, hidden_idx);+ if (ec != std::errc() || hidden_idx >= num_hidden) {+ return SQLITE_ERROR;+ }+ p = (next != idx_end && *next == ',') ? next + 1 : next;++ MaybeLocal<Value> js_val;+ SQLITE_VALUE_TO_JS(+ value, isolate, mod->use_bigint_args_, js_val, argv[argv_pos]);+ Local<Value> local;+ if (!js_val.ToLocal(&local)) {+ return mod->PropagateJSError();+ }+ js_args[hidden_idx] = local;++ // Keep a copy so xColumn can report what the column was constrained to.+ // SQLite treats `omit` as a hint, so it may still recheck the constraint+ // against the value xColumn returns.+ int schema_idx = mod->hidden_col_indices_[hidden_idx];+ cursor->hidden_values[schema_idx] = sqlite3_value_dup(argv[argv_pos]);+ if (cursor->hidden_values[schema_idx] == nullptr) {+ return SQLITE_NOMEM;+ }+ }++ // Call the rows() function.+ auto recv = Undefined(isolate);+ auto fn = mod->rows_fn_.Get(isolate);+ MaybeLocal<Value> retval =+ fn->Call(env->context(), recv, js_args.size(), js_args.data());+ Local<Value> result;+ if (!retval.ToLocal(&result)) {+ return mod->PropagateJSError();+ }++ // Get an iterator from the result. If the result has Symbol.iterator,+ // call it. Otherwise, assume the result is already an iterator.+ Local<Object> iterator_obj;+ if (result->IsObject()) {+ Local<Object> result_obj = result.As<Object>();+ Local<Value> iter_method_val;+ if (!result_obj->Get(env->context(), v8::Symbol::GetIterator(isolate))+ .ToLocal(&iter_method_val)) {+ return mod->PropagateJSError();+ }++ if (iter_method_val->IsFunction()) {+ MaybeLocal<Value> iter_result = iter_method_val.As<Function>()->Call(+ env->context(), result_obj, 0, nullptr);+ Local<Value> iter_val;+ if (!iter_result.ToLocal(&iter_val)) {+ return mod->PropagateJSError();+ }+ if (!iter_val->IsObject()) {+ return ReportProtocolError(+ pCursor->pVtab,+ "The \"options.rows\" iterable's Symbol.iterator method must "+ "return an object");+ }+ iterator_obj = iter_val.As<Object>();+ } else {+ // Assume result is already an iterator (has .next()).+ iterator_obj = result_obj;+ }+ } else {+ return ReportProtocolError(+ pCursor->pVtab,+ "The \"options.rows\" function must return an iterable object");+ }++ cursor->iterator.Reset(isolate, iterator_obj);++ // Advance to the first row.+ return xNext(pCursor);+}++int VirtualTableModule::xNext(sqlite3_vtab_cursor* pCursor) {+ NodeVTabCursor* cursor = reinterpret_cast<NodeVTabCursor*>(pCursor);+ VirtualTableModule* mod = cursor->module;+ Environment* env = mod->env_;+ Isolate* isolate = env->isolate();+ HandleScope handle_scope(isolate);+ if (!mod->CanCallIntoJS()) {+ return SQLITE_ERROR;+ }+ CallbackDepthGuard callback_guard(mod->db_.get());++ Local<Object> iterator = cursor->iterator.Get(isolate);++ // Call iterator.next().+ Local<Value> next_method_val;+ if (!iterator->Get(env->context(), FIXED_ONE_BYTE_STRING(isolate, "next"))+ .ToLocal(&next_method_val)) {+ return mod->PropagateJSError();+ }+ if (!next_method_val->IsFunction()) {+ return ReportProtocolError(+ pCursor->pVtab,+ "The \"options.rows\" iterator must have a next() method");+ }++ MaybeLocal<Value> next_result = next_method_val.As<Function>()->Call(+ env->context(), iterator, 0, nullptr);+ Local<Value> next_val;+ if (!next_result.ToLocal(&next_val)) {+ return mod->PropagateJSError();+ }+ if (!next_val->IsObject()) {+ return ReportProtocolError(+ pCursor->pVtab,+ "The \"options.rows\" iterator's next() method must return an object");+ }++ Local<Object> next_obj = next_val.As<Object>();++ // Read "done" property.+ Local<Value> done_val;+ if (!next_obj->Get(env->context(), env->done_string()).ToLocal(&done_val)) {+ return mod->PropagateJSError();+ }++ if (done_val->BooleanValue(isolate)) {+ cursor->done = true;+ cursor->current_row.Reset();+ } else {+ cursor->done = false;+ cursor->rowid++;++ // Read "value" property.+ Local<Value> value_val;+ if (!next_obj->Get(env->context(), env->value_string())+ .ToLocal(&value_val)) {+ return mod->PropagateJSError();+ }++ cursor->current_row.Reset(isolate, value_val);+ }++ return SQLITE_OK;+}++int VirtualTableModule::xEof(sqlite3_vtab_cursor* pCursor) {+ NodeVTabCursor* cursor = reinterpret_cast<NodeVTabCursor*>(pCursor);+ return cursor->done ? 1 : 0;+}++int VirtualTableModule::xColumn(sqlite3_vtab_cursor* pCursor,+ sqlite3_context* ctx,+ int i) {+ NodeVTabCursor* cursor = reinterpret_cast<NodeVTabCursor*>(pCursor);+ VirtualTableModule* mod = cursor->module;++ if (i < 0 || i >= mod->num_columns_) {+ sqlite3_result_null(ctx);+ return SQLITE_OK;+ }++ // Hidden columns are parameters rather than data, so they are not present in+ // the row array. Report the value the query constrained the column to, so+ // that a recheck of that constraint still matches.+ if (mod->col_index_map_[i] < 0) {+ if (cursor->hidden_values[i] != nullptr) {+ sqlite3_result_value(ctx, cursor->hidden_values[i]);+ } else {+ sqlite3_result_null(ctx);+ }+ return SQLITE_OK;+ }++ Environment* env = mod->env_;+ Isolate* isolate = env->isolate();+ HandleScope handle_scope(isolate);+ if (!mod->CanCallIntoJS()) {+ return SQLITE_ERROR;+ }+ CallbackDepthGuard callback_guard(mod->db_.get());++ Local<Value> row = cursor->current_row.Get(isolate);+ if (!row->IsObject()) {+ sqlite3_result_null(ctx);+ return SQLITE_OK;+ }++ Local<Object> row_obj = row.As<Object>();+ Local<Value> col_val;+ if (!row_obj->Get(env->context(), mod->col_index_map_[i]).ToLocal(&col_val)) {+ sqlite3_result_error(ctx, "", 0);+ return mod->PropagateJSError();+ }++ JSValueToSQLiteResult(isolate, ctx, col_val);+ return SQLITE_OK;+}++int VirtualTableModule::xRowid(sqlite3_vtab_cursor* pCursor,+ sqlite3_int64* pRowid) {+ NodeVTabCursor* cursor = reinterpret_cast<NodeVTabCursor*>(pCursor);+ *pRowid = cursor->rowid;+ return SQLITE_OK;+}++void VirtualTableModule::xDestroyModule(void* pAux) {+ delete static_cast<VirtualTableModule*>(pAux);+}+ DatabaseSync::DatabaseSync(Environment* env, Local<Object> object, DatabaseOpenConfiguration&& open_config,@@ -1057,6 +1537,10 @@ void DatabaseSync::DeleteSessions() { } DatabaseSync::~DatabaseSync() {+ // See the note in ~StatementSync: closing the connection here must not reach+ // back into JavaScript.+ DestructorScope destructor_scope(this);+ BindingData* binding = env()->principal_realm()->GetBindingData<BindingData>(); if (binding != nullptr) binding->open_databases.erase(this);@@ -2330,6 +2814,235 @@ void DatabaseSync::AggregateFunction(const FunctionCallbackInfo<Value>& args) { CHECK_ERROR_OR_THROW(env->isolate(), db, r, SQLITE_OK, void()); } +void DatabaseSync::CreateModule(const FunctionCallbackInfo<Value>& args) {+ DatabaseSync* db;+ ASSIGN_OR_RETURN_UNWRAP(&db, args.This());+ Environment* env = Environment::GetCurrent(args);+ THROW_AND_RETURN_ON_BAD_STATE(env, !db->IsOpen(), "database is not open");+ THROW_AND_RETURN_IF_IN_AUTHORIZER(env, db);++ if (!args[0]->IsString()) {+ THROW_ERR_INVALID_ARG_TYPE(env->isolate(),+ "The \"name\" argument must be a string.");+ return;+ }++ if (!args[1]->IsObject()) {+ THROW_ERR_INVALID_ARG_TYPE(env->isolate(),+ "The \"options\" argument must be an object.");+ return;+ }++ Utf8Value name(env->isolate(), args[0].As<String>());+ Local<Object> options = args[1].As<Object>();++ // Extract columns array.+ Local<Value> columns_v;+ if (!options+ ->Get(env->context(),+ FIXED_ONE_BYTE_STRING(env->isolate(), "columns"))+ .ToLocal(&columns_v)) {+ return;+ }++ if (!columns_v->IsArray()) {+ THROW_ERR_INVALID_ARG_TYPE(+ env->isolate(), "The \"options.columns\" argument must be an array.");+ return;+ }++ Local<Array> columns = columns_v.As<Array>();+ uint32_t num_columns = columns->Length();++ if (num_columns == 0) {+ THROW_ERR_INVALID_ARG_VALUE(+ env->isolate(), "The \"options.columns\" array must not be empty.");+ return;+ }++ // Extract rows function.+ Local<Value> rows_v;+ if (!options+ ->Get(env->context(), FIXED_ONE_BYTE_STRING(env->isolate(), "rows"))+ .ToLocal(&rows_v)) {+ return;+ }++ if (!rows_v->IsFunction()) {+ THROW_ERR_INVALID_ARG_TYPE(+ env->isolate(), "The \"options.rows\" argument must be a function.");+ return;+ }++ Local<Function> rows_fn = rows_v.As<Function>();++ // Extract optional boolean options.+ bool direct_only = false;+ Local<Value> direct_only_v;+ if (!options+ ->Get(env->context(),+ FIXED_ONE_BYTE_STRING(env->isolate(), "directOnly"))+ .ToLocal(&direct_only_v)) {+ return;+ }++ if (!direct_only_v->IsUndefined()) {+ if (!direct_only_v->IsBoolean()) {+ THROW_ERR_INVALID_ARG_TYPE(+ env->isolate(),+ "The \"options.directOnly\" argument must be a boolean.");+ return;+ }+ direct_only = direct_only_v.As<Boolean>()->Value();+ }++ bool use_bigint_args = false;+ Local<Value> use_bigint_args_v;+ if (!options+ ->Get(env->context(),+ FIXED_ONE_BYTE_STRING(env->isolate(), "useBigIntArguments"))+ .ToLocal(&use_bigint_args_v)) {+ return;+ }++ if (!use_bigint_args_v->IsUndefined()) {+ if (!use_bigint_args_v->IsBoolean()) {+ THROW_ERR_INVALID_ARG_TYPE(+ env->isolate(),+ "The \"options.useBigIntArguments\" argument must be a boolean.");+ return;+ }+ use_bigint_args = use_bigint_args_v.As<Boolean>()->Value();+ }++ // Build CREATE TABLE schema SQL from columns.+ std::string schema_sql = "CREATE TABLE x(";+ std::vector<int> hidden_col_indices;++ for (uint32_t i = 0; i < num_columns; i++) {+ Local<Value> col_v;+ if (!columns->Get(env->context(), i).ToLocal(&col_v)) {+ return;+ }++ if (!col_v->IsObject()) {+ THROW_ERR_INVALID_ARG_TYPE(+ env->isolate(),+ "Each column in \"options.columns\" must be an object.");+ return;+ }++ Local<Object> col = col_v.As<Object>();++ // Get column name.+ Local<Value> col_name_v;+ if (!col->Get(env->context(), env->name_string()).ToLocal(&col_name_v)) {+ return;+ }++ if (!col_name_v->IsString()) {+ THROW_ERR_INVALID_ARG_TYPE(+ env->isolate(), "The column \"name\" property must be a string.");+ return;+ }++ Utf8Value col_name(env->isolate(), col_name_v.As<String>());++ // Get column type.+ Local<Value> col_type_v;+ if (!col->Get(env->context(), env->type_string()).ToLocal(&col_type_v)) {+ return;+ }++ if (!col_type_v->IsString()) {+ THROW_ERR_INVALID_ARG_TYPE(+ env->isolate(), "The column \"type\" property must be a string.");+ return;+ }++ Utf8Value col_type(env->isolate(), col_type_v.As<String>());++ // Get optional hidden flag.+ bool hidden = false;+ Local<Value> hidden_v;+ if (!col->Get(env->context(),+ FIXED_ONE_BYTE_STRING(env->isolate(), "hidden"))+ .ToLocal(&hidden_v)) {+ return;+ }++ if (!hidden_v->IsUndefined()) {+ if (!hidden_v->IsBoolean()) {+ THROW_ERR_INVALID_ARG_TYPE(+ env->isolate(),+ "The column \"hidden\" property must be a boolean.");+ return;+ }+ hidden = hidden_v.As<Boolean>()->Value();+ }++ if (hidden) {+ hidden_col_indices.push_back(static_cast<int>(i));+ }++ if (i > 0) {+ schema_sql += ", ";+ }++ // Validate column type against allowed SQLite type names.+ std::string type_str = col_type.ToString();+ if (type_str != "INTEGER" && type_str != "TEXT" && type_str != "REAL" &&+ type_str != "BLOB" && type_str != "ANY") {+ THROW_ERR_INVALID_ARG_VALUE(+ env->isolate(),+ "The column \"type\" property must be one of "+ "'INTEGER', 'TEXT', 'REAL', 'BLOB', or 'ANY'.");+ return;+ }++ // Quote column name to prevent SQL injection.+ schema_sql += "\"";+ std::string name_str = col_name.ToString();+ for (char c : name_str) {+ if (c == '"') {+ schema_sql += "\"\"";+ } else {+ schema_sql += c;+ }+ }+ schema_sql += "\" ";+ schema_sql += type_str;++ if (hidden) {+ schema_sql += " HIDDEN";+ }+ }++ schema_sql += ")";++ // Reading the options bag and the column definitions above can run user+ // JavaScript through a property getter, which may have closed the database+ // since it was checked.+ THROW_AND_RETURN_ON_BAD_STATE(env, !db->IsOpen(), "database is not open");++ VirtualTableModule* vtab_mod =+ new VirtualTableModule(env,+ BaseObjectWeakPtr<DatabaseSync>(db),+ rows_fn,+ std::move(schema_sql),+ num_columns,+ std::move(hidden_col_indices),+ use_bigint_args,+ direct_only);++ int r = sqlite3_create_module_v2(db->connection_.get(),+ *name,+ &vtab_mod->module_def_,+ vtab_mod,+ VirtualTableModule::xDestroyModule);+ CHECK_ERROR_OR_THROW(env->isolate(), db, r, SQLITE_OK, void());+}+ void DatabaseSync::CreateSession(const FunctionCallbackInfo<Value>& args) { DatabaseSync* db; ASSIGN_OR_RETURN_UNWRAP(&db, args.This());@@ -3002,6 +3715,10 @@ StatementSync::StatementSync(Environment* env, } StatementSync::~StatementSync() {+ // Runs from a garbage collection callback, so finalizing the statement here+ // must not reach back into JavaScript. The scope has to live here rather than+ // in Close(), which is shared with the JS-facing close() and dispose().+ DestructorScope destructor_scope(db_.get()); Close(); } @@ -4605,6 +5322,7 @@ static void Initialize(Local<Object> target, SetProtoMethod(isolate, db_tmpl, "deserialize", DatabaseSync::Deserialize); SetProtoMethod( isolate, db_tmpl, "setAuthorizer", DatabaseSync::SetAuthorizer);+ SetProtoMethod(isolate, db_tmpl, "createModule", DatabaseSync::CreateModule); SetSideEffectFreeGetter(isolate, db_tmpl, FIXED_ONE_BYTE_STRING(isolate, "isOpen"),src/node_sqlite.h108 + / 0 −
@@ -250,6 +250,7 @@ class DatabaseSync : public BaseObject { static void Serialize(const v8::FunctionCallbackInfo<v8::Value>& args); static void Deserialize(const v8::FunctionCallbackInfo<v8::Value>& args); static void SetAuthorizer(const v8::FunctionCallbackInfo<v8::Value>& args);+ static void CreateModule(const v8::FunctionCallbackInfo<v8::Value>& args); static int AuthorizerCallback(void* user_data, int action_code, const char* param1,@@ -289,6 +290,10 @@ class DatabaseSync : public BaseObject { void DecrementCallbackDepth() { --callback_depth_; } bool IsInCallback() const { return callback_depth_ > 0; } + void IncrementDestructorDepth() { ++destructor_depth_; }+ void DecrementDestructorDepth() { --destructor_depth_; }+ bool IsInDestructor() const { return destructor_depth_ > 0; }+ // SQLite reaches back into JavaScript from inside its pre-update hook, while // it is still walking this connection's session list. Session objects are // weak, so a garbage collection during such a callback could collect one and@@ -336,6 +341,7 @@ class DatabaseSync : public BaseObject { std::unique_ptr<sqlite3, ConnectionDeleter> connection_; bool ignore_next_sqlite_error_; int callback_depth_ = 0;+ int destructor_depth_ = 0; int authorizer_depth_ = 0; int trace_suppression_depth_ = 0; std::vector<sqlite3_stmt*> stepping_statements_;@@ -349,6 +355,7 @@ class DatabaseSync : public BaseObject { friend class Session; friend class SQLTagStore; friend class StatementExecutionHelper;+ friend class VirtualTableModule; }; class StatementSync : public BaseObject {@@ -530,6 +537,23 @@ class CallbackDepthGuard { std::vector<BaseObjectPtr<Session>> pinned_sessions_; }; +// Marks a window in which SQLite is being torn down from a C++ destructor.+// Those destructors run from V8 garbage collection callbacks, where executing+// JavaScript is forbidden, so callbacks reached through them must not call back+// into JavaScript.+class DestructorScope {+ public:+ explicit DestructorScope(DatabaseSync* db) : db_(db) {+ db_->IncrementDestructorDepth();+ }+ ~DestructorScope() { db_->DecrementDestructorDepth(); }+ DestructorScope(const DestructorScope&) = delete;+ DestructorScope& operator=(const DestructorScope&) = delete;++ private:+ DatabaseSync* db_;+};+ class TraceEventSuppressionGuard { public: explicit TraceEventSuppressionGuard(DatabaseSync* db) : db_(db) {@@ -618,6 +642,90 @@ class DatabaseSyncLimits : public BaseObject { BaseObjectWeakPtr<DatabaseSync> database_; }; +struct NodeVTab {+ sqlite3_vtab base;+ class VirtualTableModule* module;+};++struct NodeVTabCursor {+ sqlite3_vtab_cursor base;+ class VirtualTableModule* module;+ v8::Global<v8::Object> iterator;+ v8::Global<v8::Value> current_row;+ // The value each hidden column was constrained to, indexed by schema column+ // index and empty for visible columns. These are owned copies, since the+ // values SQLite passes to xFilter are only valid for that call.+ std::vector<sqlite3_value*> hidden_values;+ sqlite3_int64 rowid;+ bool done;+};++class VirtualTableModule {+ public:+ VirtualTableModule(Environment* env,+ BaseObjectWeakPtr<DatabaseSync> db,+ v8::Local<v8::Function> rows_fn,+ std::string&& schema_sql,+ int num_columns,+ std::vector<int>&& hidden_col_indices,+ bool use_bigint_args,+ bool direct_only);+ ~VirtualTableModule();++ static int xCreate(sqlite3* db,+ void* pAux,+ int argc,+ const char* const* argv,+ sqlite3_vtab** ppVTab,+ char** pzErr);+ static int xBestIndex(sqlite3_vtab* pVTab, sqlite3_index_info* pInfo);+ static int xDisconnect(sqlite3_vtab* pVTab);+ static int xDestroy(sqlite3_vtab* pVTab);+ static int xOpen(sqlite3_vtab* pVTab, sqlite3_vtab_cursor** ppCursor);+ static int xClose(sqlite3_vtab_cursor* pCursor);+ static int xFilter(sqlite3_vtab_cursor* pCursor,+ int idxNum,+ const char* idxStr,+ int argc,+ sqlite3_value** argv);+ static int xNext(sqlite3_vtab_cursor* pCursor);+ static int xEof(sqlite3_vtab_cursor* pCursor);+ static int xColumn(sqlite3_vtab_cursor* pCursor, sqlite3_context* ctx, int i);+ static int xRowid(sqlite3_vtab_cursor* pCursor, sqlite3_int64* pRowid);+ static void xDestroyModule(void* pAux);++ private:+ // Suppresses the SQLite error text so the pending JavaScript exception is+ // what surfaces to the caller. Always returns SQLITE_ERROR.+ int PropagateJSError();++ // Reports a violation of the iteration protocol by the `rows` function.+ // Unlike PropagateJSError there is no JavaScript exception to surface, so an+ // error message has to be supplied. Always returns SQLITE_ERROR.+ static int ReportProtocolError(sqlite3_vtab* vtab, const char* message);++ // False while the database is being torn down from a destructor, which runs+ // from a garbage collection callback where JavaScript cannot be executed.+ bool CanCallIntoJS() const;++ static void ReleaseHiddenValues(NodeVTabCursor* cursor);++ Environment* env_;+ BaseObjectWeakPtr<DatabaseSync> db_;+ v8::Global<v8::Function> rows_fn_;+ std::string schema_sql_;+ int num_columns_;+ std::vector<int> hidden_col_indices_;+ // Maps schema column index to row array index for visible columns.+ // Hidden columns are mapped to -1.+ std::vector<int> col_index_map_;+ bool use_bigint_args_;+ bool direct_only_;+ sqlite3_module module_def_;++ friend class DatabaseSync;+};+ } // namespace sqlite } // namespace node test/parallel/test-sqlite-options-getter-reentry.js34 + / 0 −
@@ -84,6 +84,40 @@ suite('closing the database from an options getter', () => { }, invalidState); }); + test('createModule() throws instead of using a closed connection', (t) => {+ const db = new DatabaseSync(':memory:');+ t.assert.throws(() => {+ db.createModule('mod', {+ columns: [{ name: 'value', type: 'INTEGER' }],+ *rows() {+ yield [1];+ },+ get directOnly() {+ db.close();+ return false;+ },+ });+ }, invalidState);+ });++ test('createModule() throws when a column getter closes the database', (t) => {+ const db = new DatabaseSync(':memory:');+ t.assert.throws(() => {+ db.createModule('mod', {+ columns: [{+ name: 'value',+ get type() {+ db.close();+ return 'INTEGER';+ },+ }],+ *rows() {+ yield [1];+ },+ });+ }, invalidState);+ });+ test('deserialize() throws instead of using a closed connection', (t) => { const source = new DatabaseSync(':memory:'); source.exec('CREATE TABLE data(value TEXT)');test/parallel/test-sqlite-virtual-table.jsadded816 + / 0 −
@@ -0,0 +1,816 @@+// Flags: --expose-gc+'use strict';+const { skipIfSQLiteMissing } = require('../common');+skipIfSQLiteMissing();+const assert = require('node:assert');+const { DatabaseSync } = require('node:sqlite');+const { suite, test } = require('node:test');++suite('DatabaseSync.prototype.createModule()', () => {+ suite('input validation', () => {+ const db = new DatabaseSync(':memory:');++ test('throws if name is not a string', () => {+ assert.throws(() => {+ db.createModule();+ }, {+ code: 'ERR_INVALID_ARG_TYPE',+ message: /The "name" argument must be a string/,+ });+ });++ test('throws if options is not an object', () => {+ assert.throws(() => {+ db.createModule('mod', null);+ }, {+ code: 'ERR_INVALID_ARG_TYPE',+ message: /The "options" argument must be an object/,+ });+ });++ test('throws if options.columns is not an array', () => {+ assert.throws(() => {+ db.createModule('mod', { columns: 'bad', rows() {} });+ }, {+ code: 'ERR_INVALID_ARG_TYPE',+ message: /The "options\.columns" argument must be an array/,+ });+ });++ test('throws if options.columns is empty', () => {+ assert.throws(() => {+ db.createModule('mod', { columns: [], rows() {} });+ }, {+ code: 'ERR_INVALID_ARG_VALUE',+ message: /The "options\.columns" array must not be empty/,+ });+ });++ test('throws if options.rows is not a function', () => {+ assert.throws(() => {+ db.createModule('mod', {+ columns: [{ name: 'x', type: 'TEXT' }],+ rows: 'bad',+ });+ }, {+ code: 'ERR_INVALID_ARG_TYPE',+ message: /The "options\.rows" argument must be a function/,+ });+ });++ test('throws if column name is not a string', () => {+ assert.throws(() => {+ db.createModule('mod', {+ columns: [{ name: 123, type: 'TEXT' }],+ rows() {},+ });+ }, {+ code: 'ERR_INVALID_ARG_TYPE',+ message: /The column "name" property must be a string/,+ });+ });++ test('throws if column type is not a string', () => {+ assert.throws(() => {+ db.createModule('mod', {+ columns: [{ name: 'x', type: 123 }],+ rows() {},+ });+ }, {+ code: 'ERR_INVALID_ARG_TYPE',+ message: /The column "type" property must be a string/,+ });+ });++ test('throws if column type is not a valid SQLite type', () => {+ assert.throws(() => {+ db.createModule('mod', {+ columns: [{ name: 'x', type: 'INVALID' }],+ rows() {},+ });+ }, {+ code: 'ERR_INVALID_ARG_VALUE',+ message: /The column "type" property must be one of/,+ });+ });++ test('throws if column hidden is not a boolean', () => {+ assert.throws(() => {+ db.createModule('mod', {+ columns: [{ name: 'x', type: 'TEXT', hidden: 'yes' }],+ rows() {},+ });+ }, {+ code: 'ERR_INVALID_ARG_TYPE',+ message: /The column "hidden" property must be a boolean/,+ });+ });++ test('throws if options.directOnly is not a boolean', () => {+ assert.throws(() => {+ db.createModule('mod', {+ columns: [{ name: 'x', type: 'TEXT' }],+ rows() {},+ directOnly: 'yes',+ });+ }, {+ code: 'ERR_INVALID_ARG_TYPE',+ message: /The "options\.directOnly" argument must be a boolean/,+ });+ });++ test('throws if options.useBigIntArguments is not a boolean', () => {+ assert.throws(() => {+ db.createModule('mod', {+ columns: [{ name: 'x', type: 'TEXT' }],+ rows() {},+ useBigIntArguments: 'yes',+ });+ }, {+ code: 'ERR_INVALID_ARG_TYPE',+ message: /The "options\.useBigIntArguments" argument must be a boolean/,+ });+ });++ test('throws if database is not open', () => {+ const closedDb = new DatabaseSync(':memory:');+ closedDb.close();+ assert.throws(() => {+ closedDb.createModule('mod', {+ columns: [{ name: 'x', type: 'TEXT' }],+ rows() {},+ });+ }, {+ code: 'ERR_INVALID_STATE',+ message: /database is not open/,+ });+ });+ });++ suite('basic virtual table', () => {+ test('creates a simple read-only virtual table', () => {+ const db = new DatabaseSync(':memory:');++ db.createModule('simple', {+ columns: [+ { name: 'id', type: 'INTEGER' },+ { name: 'name', type: 'TEXT' },+ ],+ *rows() {+ yield [1, 'Alice'];+ yield [2, 'Bob'];+ yield [3, 'Charlie'];+ },+ });++ db.exec('CREATE VIRTUAL TABLE t1 USING simple');+ const result = db.prepare('SELECT * FROM t1').all();+ assert.deepStrictEqual(result, [+ { __proto__: null, id: 1, name: 'Alice' },+ { __proto__: null, id: 2, name: 'Bob' },+ { __proto__: null, id: 3, name: 'Charlie' },+ ]);+ });++ test('works as eponymous table (without CREATE VIRTUAL TABLE)', () => {+ const db = new DatabaseSync(':memory:');++ db.createModule('eponymous', {+ columns: [+ { name: 'value', type: 'TEXT' },+ ],+ *rows() {+ yield ['hello'];+ yield ['world'];+ },+ });++ const result = db.prepare('SELECT * FROM eponymous').all();+ assert.deepStrictEqual(result, [+ { __proto__: null, value: 'hello' },+ { __proto__: null, value: 'world' },+ ]);+ });++ test('supports rows() returning an array', () => {+ const db = new DatabaseSync(':memory:');++ db.createModule('array_mod', {+ columns: [+ { name: 'val', type: 'INTEGER' },+ ],+ rows() {+ return [[10], [20], [30]];+ },+ });++ const result = db.prepare('SELECT * FROM array_mod').all();+ assert.deepStrictEqual(result, [+ { __proto__: null, val: 10 },+ { __proto__: null, val: 20 },+ { __proto__: null, val: 30 },+ ]);+ });++ test('supports empty result set', () => {+ const db = new DatabaseSync(':memory:');++ db.createModule('empty_mod', {+ columns: [+ { name: 'x', type: 'TEXT' },+ ],+ *rows() {+ // yields nothing+ },+ });++ const result = db.prepare('SELECT * FROM empty_mod').all();+ assert.deepStrictEqual(result, []);+ });+ });++ suite('table-valued function with parameters', () => {+ test('passes hidden column values as arguments to rows()', () => {+ const db = new DatabaseSync(':memory:');++ db.createModule('gen_series', {+ columns: [+ { name: 'value', type: 'INTEGER' },+ { name: 'start', type: 'INTEGER', hidden: true },+ { name: 'stop', type: 'INTEGER', hidden: true },+ { name: 'step', type: 'INTEGER', hidden: true },+ ],+ *rows(start, stop, step) {+ start ??= 0;+ stop ??= 10;+ step ??= 1;+ for (let i = start; i <= stop; i += step) {+ yield [i];+ }+ },+ });++ const result = db.prepare(+ 'SELECT value FROM gen_series(1, 5, 1)'+ ).all();+ assert.deepStrictEqual(result, [+ { __proto__: null, value: 1 },+ { __proto__: null, value: 2 },+ { __proto__: null, value: 3 },+ { __proto__: null, value: 4 },+ { __proto__: null, value: 5 },+ ]);+ });++ test('passes step parameter', () => {+ const db = new DatabaseSync(':memory:');++ db.createModule('gen_step', {+ columns: [+ { name: 'value', type: 'INTEGER' },+ { name: 'start', type: 'INTEGER', hidden: true },+ { name: 'stop', type: 'INTEGER', hidden: true },+ { name: 'step', type: 'INTEGER', hidden: true },+ ],+ *rows(start, stop, step) {+ start ??= 0;+ stop ??= 10;+ step ??= 1;+ for (let i = start; i <= stop; i += step) {+ yield [i];+ }+ },+ });++ const result = db.prepare(+ 'SELECT value FROM gen_step(0, 10, 3)'+ ).all();+ assert.deepStrictEqual(result, [+ { __proto__: null, value: 0 },+ { __proto__: null, value: 3 },+ { __proto__: null, value: 6 },+ { __proto__: null, value: 9 },+ ]);+ });++ test('handles partial parameters (some hidden cols unconstrained)', () => {+ const db = new DatabaseSync(':memory:');++ db.createModule('partial_params', {+ columns: [+ { name: 'value', type: 'INTEGER' },+ { name: 'count', type: 'INTEGER', hidden: true },+ ],+ *rows(count) {+ const n = count ?? 3;+ for (let i = 0; i < n; i++) {+ yield [i];+ }+ },+ });++ // Without parameter (uses default).+ db.exec('CREATE VIRTUAL TABLE pp USING partial_params');+ const result1 = db.prepare('SELECT * FROM pp').all();+ assert.strictEqual(result1.length, 3);++ // With parameter via table-valued function syntax.+ const result2 = db.prepare('SELECT * FROM partial_params(5)').all();+ assert.strictEqual(result2.length, 5);+ });++ test('maps parameters correctly beyond 32 hidden columns', () => {+ const db = new DatabaseSync(':memory:');+ const paramCount = 40;+ const columns = [{ name: 'value', type: 'INTEGER' }];+ for (let i = 0; i < paramCount; i++) {+ columns.push({ name: `p${i}`, type: 'INTEGER', hidden: true });+ }++ let received;+ db.createModule('many_params', {+ columns,+ rows(...args) {+ received = args;+ return [[0]];+ },+ });++ // Constrain only the last parameter. A bitmask in an int would not be+ // able to represent an index this high.+ db.prepare(`SELECT value FROM many_params(${+ Array.from({ length: paramCount }, (_, i) => (i === paramCount - 1 ? '7' : 'NULL')).join(', ')+ })`).all();++ assert.strictEqual(received.length, paramCount);+ assert.strictEqual(received[paramCount - 1], 7);+ });+ });++ suite('type conversions', () => {+ test('handles various SQLite data types', () => {+ const db = new DatabaseSync(':memory:');++ db.createModule('types_mod', {+ columns: [+ { name: 'int_col', type: 'INTEGER' },+ { name: 'real_col', type: 'REAL' },+ { name: 'text_col', type: 'TEXT' },+ { name: 'blob_col', type: 'BLOB' },+ { name: 'null_col', type: 'TEXT' },+ ],+ *rows() {+ yield [42, 3.14, 'hello', new Uint8Array([1, 2, 3]), null];+ },+ });++ const result = db.prepare('SELECT * FROM types_mod').get();+ assert.strictEqual(result.int_col, 42);+ assert.strictEqual(result.real_col, 3.14);+ assert.strictEqual(result.text_col, 'hello');+ assert.deepStrictEqual(+ new Uint8Array(result.blob_col),+ new Uint8Array([1, 2, 3])+ );+ assert.strictEqual(result.null_col, null);+ });+ });++ suite('useBigIntArguments', () => {+ test('passes INTEGER parameters as BigInts when enabled', () => {+ const db = new DatabaseSync(':memory:');+ let receivedType;++ db.createModule('bigint_mod', {+ columns: [+ { name: 'result', type: 'TEXT' },+ { name: 'input', type: 'INTEGER', hidden: true },+ ],+ useBigIntArguments: true,+ *rows(input) {+ receivedType = typeof input;+ yield [String(input)];+ },+ });++ db.prepare('SELECT * FROM bigint_mod(42)').get();+ assert.strictEqual(receivedType, 'bigint');+ });++ test('passes INTEGER parameters as numbers by default', () => {+ const db = new DatabaseSync(':memory:');+ let receivedType;++ db.createModule('number_mod', {+ columns: [+ { name: 'result', type: 'TEXT' },+ { name: 'input', type: 'INTEGER', hidden: true },+ ],+ *rows(input) {+ receivedType = typeof input;+ yield [String(input)];+ },+ });++ db.prepare('SELECT * FROM number_mod(42)').get();+ assert.strictEqual(receivedType, 'number');+ });+ });++ suite('re-entrancy', () => {+ // Closing the database while SQLite is stepping the statement that owns+ // the cursor would finalize that statement from under sqlite3_step().+ for (const [name, makeRows] of [+ ['rows()', (db) => function* () { db.close(); yield [1]; }],+ ['iterator next()', (db) => () => ({+ [Symbol.iterator]() { return this; },+ next() { db.close(); return { value: [1], done: false }; },+ })],+ ['a row getter', (db) => () => [{ get 0() { db.close(); return 1; } }]],+ ]) {+ test(`throws when close() is called from ${name}`, () => {+ const db = new DatabaseSync(':memory:');+ db.createModule('closer', {+ columns: [{ name: 'value', type: 'INTEGER' }],+ rows: makeRows(db),+ });++ assert.throws(() => {+ db.prepare('SELECT value FROM closer').all();+ }, {+ code: 'ERR_INVALID_STATE',+ message: /database cannot be closed while in a callback/,+ });+ });+ }++ test('throws when createModule() is called from an authorizer', () => {+ const db = new DatabaseSync(':memory:');+ db.exec('CREATE TABLE t(a)');+ let err;+ db.setAuthorizer(() => {+ try {+ db.createModule('from_authz', {+ columns: [{ name: 'v', type: 'INTEGER' }],+ *rows() { yield [1]; },+ });+ } catch (e) {+ err = e;+ }+ return 0;+ });++ db.prepare('SELECT * FROM t').all();+ assert.strictEqual(err?.code, 'ERR_INVALID_STATE');+ assert.match(err.message, /cannot be accessed from an authorizer/);+ });+ });++ suite('iterator cleanup', () => {+ test('closes the iterator when SQLite stops stepping early', () => {+ const db = new DatabaseSync(':memory:');+ let cleanedUp = false;++ db.createModule('early_stop', {+ columns: [{ name: 'value', type: 'INTEGER' }],+ *rows() {+ try {+ for (let i = 0; i < 100; i++) {+ yield [i];+ }+ } finally {+ cleanedUp = true;+ }+ },+ });++ const rows = db.prepare('SELECT value FROM early_stop LIMIT 2').all();+ assert.strictEqual(rows.length, 2);+ assert.strictEqual(cleanedUp, true);+ });++ test('keeps the original error when rows() throws', () => {+ const db = new DatabaseSync(':memory:');++ db.createModule('cleanup_throws', {+ columns: [{ name: 'value', type: 'INTEGER' }],+ *rows() {+ try {+ yield [1];+ throw new Error('boom');+ } finally {+ // Cleanup must not mask the error above.+ }+ },+ });++ assert.throws(() => {+ db.prepare('SELECT value FROM cleanup_throws').all();+ }, /boom/);+ });++ test('closes the iterator on break out of a for...of loop', () => {+ const db = new DatabaseSync(':memory:');+ let cleanedUp = false;++ db.createModule('breaker', {+ columns: [{ name: 'v', type: 'INTEGER' }],+ *rows() {+ try {+ for (let i = 0; i < 1000; i++) {+ yield [i];+ }+ } finally {+ cleanedUp = true;+ }+ },+ });++ for (const row of db.prepare('SELECT v FROM breaker').iterate()) {+ if (row.v === 1) break;+ }+ assert.strictEqual(cleanedUp, true);+ });++ test('surfaces an error thrown by the iterator\'s return()', () => {+ const db = new DatabaseSync(':memory:');++ for (const [label, descriptor] of [+ ['method', { value() { throw new Error('return boom'); } }],+ ['getter', { get() { throw new Error('return boom'); } }],+ ]) {+ db.createModule(`ret_${label}`, {+ columns: [{ name: 'v', type: 'INTEGER' }],+ rows() {+ let i = 0;+ const it = {+ [Symbol.iterator]() { return this; },+ next() { return { value: [i++], done: i > 50 }; },+ };+ Object.defineProperty(it, 'return', descriptor);+ return it;+ },+ });++ assert.throws(() => {+ db.prepare(`SELECT v FROM ret_${label} LIMIT 2`).all();+ }, /return boom/, `throwing return ${label} should surface`);+ }+ });++ test('does not run cleanup when the statement is collected', () => {+ // The destructor runs from a GC callback, where JavaScript cannot be+ // executed. An abandoned generator does not run `finally` in JavaScript+ // either, so the expected outcome is no crash and no cleanup.+ const db = new DatabaseSync(':memory:');+ let cleanedUp = false;++ db.createModule('abandoned', {+ columns: [{ name: 'v', type: 'INTEGER' }],+ *rows() {+ try {+ for (let i = 0; i < 1000; i++) {+ yield [i];+ }+ } finally {+ cleanedUp = true;+ }+ },+ });++ (function abandon() {+ db.prepare('SELECT v FROM abandoned').iterate().next();+ })();++ for (let i = 0; i < 5; i++) {+ globalThis.gc({ execution: 'sync' });+ }+ assert.strictEqual(cleanedUp, false);+ });++ test('a throwing cleanup does not swallow the next SQLite error', () => {+ // SQLite discards xClose's return value, so a cleanup error there has no+ // SQLite error to pair with. Suppressing one would leave the suppression+ // flag set for the next unrelated statement.+ const db = new DatabaseSync(':memory:');++ db.createModule('cleanup_leak', {+ columns: [{ name: 'value', type: 'INTEGER' }],+ rows() {+ let i = 0;+ return {+ [Symbol.iterator]() { return this; },+ next() { return { value: [i++], done: i > 50 }; },+ return() { throw new Error('cleanup boom'); },+ };+ },+ });++ assert.throws(() => {+ db.prepare('SELECT value FROM cleanup_leak LIMIT 1').all();+ }, /cleanup boom/);++ assert.throws(() => {+ db.prepare('SELECT * FROM no_such_table');+ }, { code: 'ERR_SQLITE_ERROR', message: /no such table: no_such_table/ });+ });++ test('a SQLite error outranks a throwing cleanup', () => {+ // Both fail at once: the statement aborts on a constraint violation+ // while the cursor is open, and the cursor's return() then throws. Only+ // one can surface, and xClose cannot choose based on the statement's+ // state, because SQLite closes cursors before transferring the error to+ // the connection. The constraint violation is the actionable one.+ const db = new DatabaseSync(':memory:');++ db.createModule('both_fail', {+ columns: [{ name: 'value', type: 'INTEGER' }],+ rows() {+ let i = 0;+ return {+ [Symbol.iterator]() { return this; },+ next() { return { value: [i++], done: i > 50 }; },+ return() { throw new Error('cleanup boom'); },+ };+ },+ });++ db.exec('CREATE TABLE t(v INTEGER PRIMARY KEY)');+ db.exec('INSERT INTO t VALUES (5)');++ assert.throws(() => {+ db.prepare('INSERT INTO t SELECT value FROM both_fail').run();+ }, { code: 'ERR_SQLITE_ERROR', message: /UNIQUE constraint failed: t\.v/ });+ });+ });++ suite('iteration protocol violations', () => {+ // Each of these is a misuse with no pending JavaScript exception, so the+ // module has to report a SQLite error of its own rather than relying on one.+ for (const [name, rows, expected] of [+ [+ 'rows() returns a non-object',+ () => 42,+ /must return an iterable object/,+ ],+ [+ 'Symbol.iterator returns a non-object',+ () => ({ [Symbol.iterator]() { return 7; } }),+ /Symbol\.iterator method must return an object/,+ ],+ [+ 'iterator has no next()',+ () => ({ [Symbol.iterator]() { return this; } }),+ /must have a next\(\) method/,+ ],+ [+ 'next() returns a non-object',+ () => ({ [Symbol.iterator]() { return this; }, next() { return 42; } }),+ /next\(\) method must return an object/,+ ],+ ]) {+ test(`reports an error when ${name}`, () => {+ const db = new DatabaseSync(':memory:');+ db.createModule('bad', {+ columns: [{ name: 'v', type: 'INTEGER' }],+ rows,+ });++ // Both entry points must fail; exec() has no return value to inspect,+ // so a missing error there would look like success.+ assert.throws(() => {+ db.prepare('SELECT v FROM bad').all();+ }, { code: 'ERR_SQLITE_ERROR', message: expected });++ assert.throws(() => {+ db.exec('SELECT v FROM bad');+ }, { code: 'ERR_SQLITE_ERROR', message: expected });+ });+ }+ });++ suite('hidden column constraints', () => {+ const makeDb = () => {+ const db = new DatabaseSync(':memory:');+ db.createModule('gs', {+ columns: [+ { name: 'value', type: 'INTEGER' },+ { name: 'start', type: 'INTEGER', hidden: true },+ { name: 'stop', type: 'INTEGER', hidden: true },+ ],+ *rows(start, stop) {+ start ??= 1;+ stop ??= 3;+ for (let i = start; i <= stop; i++) {+ yield [i];+ }+ },+ });+ return db;+ };++ test('reports the constrained value back from a hidden column', () => {+ const db = makeDb();+ assert.deepStrictEqual(+ db.prepare('SELECT start, value FROM gs(1, 3)').all(),+ [+ { __proto__: null, start: 1, value: 1 },+ { __proto__: null, start: 1, value: 2 },+ { __proto__: null, start: 1, value: 3 },+ ]);+ });++ test('survives a recheck of a constraint it already consumed', () => {+ // SQLite treats `omit` as a hint, so it may recheck `start = 1` against+ // whatever xColumn reports. Returning NULL there rejects every row.+ const db = makeDb();+ assert.deepStrictEqual(+ db.prepare('SELECT value FROM gs(1, 3) WHERE start = 1').all(),+ [+ { __proto__: null, value: 1 },+ { __proto__: null, value: 2 },+ { __proto__: null, value: 3 },+ ]);+ });++ test('uses the constrained plan when parameters come from a join', () => {+ const db = makeDb();+ db.exec('CREATE TABLE t(a); INSERT INTO t VALUES (1), (2)');+ assert.deepStrictEqual(+ db.prepare(+ 'SELECT t.a, gs.value FROM t, gs(t.a, t.a + 1) AS gs ORDER BY t.a, gs.value'+ ).all(),+ [+ { __proto__: null, a: 1, value: 1 },+ { __proto__: null, a: 1, value: 2 },+ { __proto__: null, a: 2, value: 2 },+ { __proto__: null, a: 2, value: 3 },+ ]);+ });+ });++ suite('error handling', () => {+ test('propagates errors thrown in rows()', () => {+ const db = new DatabaseSync(':memory:');++ db.createModule('error_mod', {+ columns: [+ { name: 'x', type: 'TEXT' },+ ],+ rows() {+ throw new Error('rows error');+ },+ });++ assert.throws(() => {+ db.prepare('SELECT * FROM error_mod').all();+ }, {+ message: /rows error/,+ });+ });++ test('propagates errors thrown during iteration', () => {+ const db = new DatabaseSync(':memory:');++ db.createModule('iter_error_mod', {+ columns: [+ { name: 'x', type: 'INTEGER' },+ ],+ *rows() {+ yield [1];+ throw new Error('iteration error');+ },+ });++ assert.throws(() => {+ db.prepare('SELECT * FROM iter_error_mod').all();+ }, {+ message: /iteration error/,+ });+ });+ });++ suite('multiple queries', () => {+ test('supports querying the virtual table multiple times', () => {+ const db = new DatabaseSync(':memory:');+ let callCount = 0;++ db.createModule('multi_mod', {+ columns: [+ { name: 'value', type: 'INTEGER' },+ ],+ *rows() {+ callCount++;+ yield [callCount];+ },+ });++ db.exec('CREATE VIRTUAL TABLE m USING multi_mod');+ const r1 = db.prepare('SELECT * FROM m').get();+ const r2 = db.prepare('SELECT * FROM m').get();+ assert.strictEqual(r1.value, 1);+ assert.strictEqual(r2.value, 2);+ assert.strictEqual(callCount, 2);+ });+ });+});