8000 sqlite: add `StatementSync.prototype.iterate` method · nodejs/node@b4041e5 · GitHub
[go: up one dir, main page]
Skip to content

Commit b4041e5

Browse files
tpoisseauruyadorno
tpoisseau
authored and
ruyadorno
committed
sqlite: add StatementSync.prototype.iterate method
PR-URL: #54213 Reviewed-By: Zeyu "Alex" Yang <himself65@outlook.com> Reviewed-By: Colin Ihrig <cjihrig@gmail.com>
1 parent 0b3ac05 commit b4041e5

File tree

5 files changed

+243
-0
lines changed

5 files changed

+243
-0
lines changed

‎doc/api/sqlite.md‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -307,6 +307,25 @@ object. If the prepared statement does not return any results, this method
307307
returns `undefined`. The prepared statement [parameters are bound][] using the
308308
values in `namedParameters` and `anonymousParameters`.
309309

310+
### `statement.iterate([namedParameters][, ...anonymousParameters])`
311+
312+
<!-- YAML
313+
added: REPLACEME
314+
-->
315+
316+
* `namedParameters` {Object} An optional object used to bind named parameters.
317+
The keys of this object are used to configure the mapping.
318+
* `...anonymousParameters` {null|number|bigint|string|Buffer|Uint8Array} Zero or
319+
more values to bind to anonymous parameters.
320+
* Returns: {Iterator} An iterable iterator of objects. Each object corresponds to a row
321+
returned by executing the prepared statement. The keys and values of each
322+
object correspond to the column names and values of the row.
323+
324+
This method executes a prepared statement and returns an iterator of
325+
objects. If the prepared statement does not return any results, this method
326+
returns an empty iterator. The prepared statement [parameters are bound][] using
327+
the values in `namedParameters` and `anonymousParameters`.
328+
310329
### `statement.run([namedParameters][, ...anonymousParameters])`
311330

312331
<!-- YAML

‎src/env_properties.h‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -194,8 +194,10 @@
194194
V(ipv4_string, "IPv4") \
195195
V(ipv6_string, "IPv6") \
196196
V(isclosing_string, "isClosing") \
197+
V(isfinished_string, "isFinished") \
197198
V(issuer_string, "issuer") \
198199
V(issuercert_string, "issuerCertificate") \
200+
V(iterator_string, "Iterator") \
199201
V(jwk_crv_string, "crv") \
200202
V(jwk_d_string, "d") \
201203
V(jwk_dp_string, "dp") \
@@ -241,6 +243,7 @@
241243
V(nistcurve_string, "nistCurve") \
242244
V(node_string, "node") \
243245
V(nsname_string, "nsname") \
246+
V(num_cols_string, "num_cols") \
244247
V(object_string, "Object") \
245248
V(ocsp_request_string, "OCSPRequest") \
246249
V(oncertcb_string, "oncertcb") \
@@ -288,6 +291,7 @@
288291
V(priority_string, "priority") \
289292
V(process_string, "process") \
290293
V(promise_string, "promise") \
294+
V(prototype_string, "prototype") \
291295
V(psk_string, "psk") \
292296
V(pubkey_string, "pubkey") \
293297
V(public_exponent_string, "publicExponent") \
@@ -309,6 +313,7 @@
309313
V(require_string, "require") \
310314
V(resource_string, "resource") \
311315
V(retry_string, "retry") \
316+
V(return_string, "return") \
312317
V(salt_length_string, "saltLength") \
313318
V(scheme_string, "scheme") \
314319
V(scopeid_string, "scopeid") \
@@ -332,6 +337,7 @@
332337
V(standard_name_string, "standardName") \
333338
V(start_time_string, "startTime") \
334339
V(state_string, "state") \
340+
V(statement_string, "statement") \
335341
V(stats_string, "stats") \
336342
V(status_string, "status") \
337343
V(stdio_string, "stdio") \

‎src/node_sqlite.cc‎

Lines 3262 changed: 176 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -22,6 +22,7 @@ using v8::ConstructorBehavior;
2222
using v8::Context;
2323
using v8::DontDelete;
2424
using v8::Exception;
25+
using v8::External;
2526
using v8::Function;
2627
using v8::FunctionCallback;
2728
using v8::FunctionCallbackInfo;
@@ -790,6 +791,180 @@ void StatementSync::All(const FunctionCallbackInfo<Value>& args) {
790791
args.GetReturnValue().Set(Array::New(isolate, rows.data(), rows.size()));
791792
}
792793

794+
void StatementSync::IterateReturnCallback(
795+
const FunctionCallbackInfo<Value>& args) {
796+
Environment* env = Environment::GetCurrent(args);
797+
auto isolate = env->isolate();
798+
auto context = isolate->GetCurrentContext();
799+
800+
auto self = args.This();
801+
// iterator has fetch all result or break, prevent next func to return result
802+
self->Set(context, env->isfinished_string(), Boolean::New(isolate, true))
803+
.ToChecked();
804+
805+
auto external_stmt = Local<External>::Cast(
806+
self->Get(context, env->statement_string()).ToLocalChecked());
807+
auto stmt = static_cast<StatementSync*>(external_stmt->Value());
808+
if (!stmt->IsFinalized()) {
809+
sqlite3_reset(stmt->statement_);
810+
}
811+
812+
LocalVector<Name> keys(isolate, {env->done_string(), env->value_string()});
813+
LocalVector<Value> values(isolate,
814+
{Boolean::New(isolate, true), Null(isolate)});
815+
816+
DCHECK_EQ(keys.size(), values.size());
817+
Local<Object> result = Object::New(
818+
isolate, Null(isolate), keys.data(), values.data(), keys.size());
819+
args.GetReturnValue().Set(result);
820+
}
821+
822+
void StatementSync::IterateNextCallback(
823+
const FunctionCallbackInfo<Value>& args) {
824+
Environment* env = Environment::GetCurrent(args);
825+
auto isolate = env->isolate();
826+
auto context = isolate->GetCurrentContext();
827+
828+
auto self = args.This();
829+
830+
// skip iteration if is_finished
831+
auto is_finished = Local<Boolean>::Cast(
832+
self->Get(context, env->isfinished_string()).ToLocalChecked());
833+
if (is_finished->Value()) {
834+
LocalVector<Name> keys(isolate, {env->done_string(), env->value_string()});
835+
LocalVector<Value> values(isolate,
836+
{Boolean::New(isolate, true), Null(isolate)});
837+
838+
DCHECK_EQ(keys.size(), values.size());
839+
Local<Object> result = Object::New(
840+
isolate, Null(isolate), keys.data(), values.data(), keys.size());
841+
args.GetReturnValue().Set(result);
842+
return;
843+
}
844+
845+
auto external_stmt = Local<External>::Cast(
846+
self->Get(context, env->statement_string()).ToLocalChecked());
847+
auto stmt = static_cast<StatementSync*>(external_stmt->Value());
848+
auto num_cols =
849+
Local<Integer>::Cast(
850+
self->Get(context, env->num_cols_string()).ToLocalChecked())
851+
->Value();
852+
853+
THROW_AND_RETURN_ON_BAD_STATE(
854+
env, stmt->IsFinalized(), "statement has been finalized");
855+
856+
int r = sqlite3_step(stmt->statement_);
857+
if (r != SQLITE_ROW) {
858+
CHECK_ERROR_OR_THROW(
859+
env->isolate(), stmt->db_->Connection(), r, SQLITE_DONE, void());
860+
861+
// cleanup when no more rows to fetch
862+
sqlite3_reset(stmt->statement_);
863+
self->Set(context, env->isfinished_string(), Boolean::New(isolate, true))
864+
.ToChecked();
865+
866+
LocalVector<Name> keys(isolate, {env->done_string(), env->value_string()});
867+
LocalVector<Value> values(isolate,
868+
{Boolean::New(isolate, true), Null(isolate)});
869+
870+
DCHECK_EQ(keys.size(), values.size());
871+
Local<Object> result = Object::New(
872+
isolate, Null(isolate), keys.data(), values.data(), keys.size());
873+
args.GetReturnValue().Set(result);
874+
return;
875+
}
876+
877+
LocalVector<Name> row_keys(isolate);
878+
row_keys.reserve(num_cols);
879+
LocalVector<Value> row_values(isolate);
880+
row_values.reserve(num_cols);
881+
for (int i = 0; i < num_cols; ++i) {
882+
Local<Name> key;
883+
if (!stmt->ColumnNameToName(i).ToLocal(&key)) return;
884+
Local<Value> val;
885+
if (!stmt->ColumnToValue(i).ToLocal(&val)) return;
886+
row_keys.emplace_back(key);
887+
row_values.emplace_back(val);
888+
}
889+
890+
Local<Object> row = Object::New(
891+
isolate, Null(isolate), row_keys.data(), row_values.data(), num_cols);
892+
893+
LocalVector<Name> keys(isolate, {env->done_string(), env->value_string()});
894+
LocalVector<Value> values(isolate, {Boolean::New(isolate, false), row});
895+
896+
DCHECK_EQ(keys.size(), values.size());
897+
Local<Object> result = Object::New(
898+
isolate, Null(isolate), keys.data(), values.data(), keys.size());
899+
args.GetReturnValue().Set(result);
900+
}
901+
902+
void StatementSync::Iterate(const FunctionCallbackInfo<Value>& args) {
903+
StatementSync* stmt;
904+
ASSIGN_OR_RETURN_UNWRAP(&stmt, args.This());
905+
Environment* env = Environment::GetCurrent(args);
906+
THROW_AND_RETURN_ON_BAD_STATE(
907+
env, stmt->IsFinalized(), "statement has been finalized");
908+
auto isolate = env->isolate();
909+
auto context = env->context();
910+
int r = sqlite3_reset(stmt->statement_);
911+
CHECK_ERROR_OR_THROW(
912+
env->isolate(), stmt->db_->Connection(), r, SQLITE_OK, void());
913+
914+
if (!stmt->BindParams(args)) {
915+
return;
916+
}
917+
918+
Local<Function> next_func =
919+
Function::New(context, StatementSync::IterateNextCallback)
920+
.ToLocalChecked();
921+
Local<Function> return_func =
922+
Function::New(context, StatementSync::IterateReturnCallback)
923+
.ToLocalChecked();
924+
925+
LocalVector<Name> keys(isolate, {env->next_string(), env->return_string()});
926+
LocalVector<Value> values(isolate, {next_func, return_func});
927+
928+
Local<Object> global = context->Global();
929+
Local<Value> js_iterator;
930+
Local<Value> js_iterator_prototype;
931+
if (!global->Get(context, env->iterator_string()).ToLocal(&js_iterator))
932+
return;
933+
if (!js_iterator.As<Object>()
934+
->Get(context, env->prototype_string())
935+
.ToLocal(&js_iterator_prototype))
936+
return;
937+
938+
DCHECK_EQ(keys.size(), values.size());
939+
Local<Object> iterable_iterator = Object::New(
940+
isolate, js_iterator_prototype, keys.data(), values.data(), keys.size());
941+
942+
auto num_cols_pd = v8::PropertyDescriptor(
943+
v8::Integer::New(isolate, sqlite3_column_count(stmt->statement_)), false);
944+
num_cols_pd.set_enumerable(false);
945+
num_cols_pd.set_configurable(false);
946+
iterable_iterator
947+
->DefineProperty(context, env->num_cols_string(), num_cols_pd)
948+
.ToChecked();
949+
950+
auto stmt_pd =
951+
v8::PropertyDescriptor(v8::External::New(isolate, stmt), false);
952+
stmt_pd.set_enumerable(false);
953+
stmt_pd.set_configurable(false);
954+
iterable_iterator->DefineProperty(context, env->statement_string(), stmt_pd)
955+
.ToChecked();
956+
957+
auto is_finished_pd =
958+
v8::PropertyDescriptor(v8::Boolean::New(isolate, false), true);
959+
stmt_pd.set_enumerable(false);
960+
stmt_pd.set_configurable(false);
961+
iterable_iterator
962+
->DefineProperty(context, env->isfinished_string(), is_finished_pd)
963+
.ToChecked();
964+
965+
args.GetReturnValue().Set(iterable_iterator);
966+
}
967+
793968
void StatementSync::Get(const FunctionCallbackInfo<Value>& args) {
794969
StatementSync* stmt;
795970
ASSIGN_OR_RETURN_UNWRAP(&stmt, args.This());
@@ -987,6 +1162,7 @@ Local<FunctionTemplate> StatementSync::GetConstructorTemplate(
9871162
tmpl->SetClassName(FIXED_ONE_BYTE_STRING(isolate, "StatementSync"));
9881163
tmpl->InstanceTemplate()->SetInternalFieldCount(
9891164
StatementSync::kInternalFieldCount);
1165+
SetProtoMethod(isolate, tmpl, "iterate", StatementSync::Iterate);
9901166
SetProtoMethod(isolate, tmpl, "all", StatementSync::All);
9911167
SetProtoMethod(isolate, tmpl, "get", StatementSync::Get);
9921168
SetProtoMethod(isolate, tmpl, "run", StatementSync::Run);

‎src/node_sqlite.h‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -93,6 +93,7 @@ class StatementSync : public BaseObject {
9393
DatabaseSync* db,
9494
sqlite3_stmt* stmt);
9595
static void All(const v8::FunctionCallbackInfo<v8::Value>& args);
96+
static void Iterate(const v8::FunctionCallbackInfo<v8::Value>& args);
9697
static void Get(const v8::FunctionCallbackInfo<v8::Value>& args);
9798
static void Run(const v8::FunctionCallbackInfo<v8::Value>& args);
9899
static void SourceSQLGetter(const v8::FunctionCallbackInfo<v8::Value>& args);
@@ -118,6 +119,11 @@ class StatementSync : public BaseObject {
118119
bool BindValue(const v8::Local<v8::Value>& value, const int index);
119120
v8::MaybeLocal<v8::Value> ColumnToValue(const int column);
120121
v8::MaybeLocal<v8::Name> ColumnNameToName(const int column);
122+
123+
static void IterateNextCallback(
124+
const v8::FunctionCallbackInfo<v8::Value>& args);
125+
static void IterateReturnCallback(
126+
const v8::FunctionCallbackInfo<v8::Value>& args);
121127
};
122128

123129
using Sqlite3ChangesetGenFunc = int (*)(sqlite3_session*, int*, void**);

‎test/parallel/test-sqlite-statement-sync.js‎

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -84,6 +84,42 @@ suite('StatementSync.prototype.all()', () => {
8484
});
8585
});
8686

87+
suite('StatementSync.prototype.iterate()', () => {
88+
test('executes a query and returns an empty iterator on no results', (t) => {
89+
const db = new DatabaseSync(nextDb());
90+
const stmt = db.prepare('CREATE TABLE storage(key TEXT, val TEXT)');
91+
t.assert.deepStrictEqual(stmt.iterate().toArray(), []);
92+
});
93+
94+
test('executes a query and returns all results', (t) => {
95+
const db = new DatabaseSync(nextDb());
96+
let stmt = db.prepare('CREATE TABLE storage(key TEXT, val TEXT)');
97+
t.assert.deepStrictEqual(stmt.run(), { changes: 0, lastInsertRowid: 0 });
98+
stmt = db.prepare('INSERT INTO storage (key, val) VALUES (?, ?)');
99+
t.assert.deepStrictEqual(
100+
stmt.run('key1', 'val1'),
101+
{ changes: 1, lastInsertRowid: 1 },
102+
);
103+
t.assert.deepStrictEqual(
104+
stmt.run('key2', 'val2'),
105+
{ changes: 1, lastInsertRowid: 2 },
106+
);
107+
108+
const items = [
109+
{ __proto__: null, key: 'key1', val: 'val1' },
110+
{ __proto__: null, key: 'key2', val: 'val2' },
111+
];
112+
113+
stmt = db.prepare('SELECT * FROM storage ORDER BY key');
114+
t.assert.deepStrictEqual(stmt.iterate().toArray(), items);
115+
116+
const itemsLoop = items.slice();
117+
for (const item of stmt.iterate()) {
118+
t.assert.deepStrictEqual(item, itemsLoop.shift());
119+
}
120+
});
121+
});
122+
87123
suite('StatementSync.prototype.run()', () => {
88124
test('executes a query and returns change metadata', (t) => {
89125
const db = new DatabaseSync(nextDb());

0 commit comments

Comments
 (0)
0