FazBrowse GitHub Viewer | Trending |
URL:
| Home
Tools: [Download Repo ZIP]   [Original HTTPS Page]

sqlite: add support for custom functions · nodejs/node@4745798 · GitHub

/ node Public

Commit 4745798

Browse files
cjihrig
authored and
aduh95
committed
sqlite: add support for custom functions
This commit adds support to node:sqlite for defining custom functions that can be invoked from SQL. Fixes: #54349 PR-URL: #55985 Reviewed-By: Zeyu "Alex" Yang <himself65@outlook.com> Reviewed-By: James M Snell <jasnell@gmail.com>
1 parent 075b36b commit 4745798

4 files changed

Lines changed: 667 additions & 0 deletions

File tree

‎doc/api/sqlite.md‎

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -160,6 +160,31 @@ This method allows one or more SQL statements to be executed without returning
160160
any results. This method is useful when executing SQL statements read from a
161161
file. This method is a wrapper around [`sqlite3_exec()`][].
162162

163+
### `database.function(name[, options], function)`
164+
165+
<!-- YAML
166+
added: REPLACEME
167+
-->
168+
169+
* `name` {string} The name of the SQLite function to create.
170+
* `options` {Object} Optional configuration settings for the function. The
171+
following properties are supported:
172+
* `deterministic` {boolean} If `true`, the [`SQLITE_DETERMINISTIC`][] flag is
173+
set on the created function. **Default:** `false`.
174+
* `directOnly` {boolean} If `true`, the [`SQLITE_DIRECTONLY`][] flag is set on
175+
the created function. **Default:** `false`.
176+
* `useBigIntArguments` {boolean} If `true`, integer arguments to `function`
177+
are converted to `BigInt`s. If `false`, integer arguments are passed as
178+
JavaScript numbers. **Default:** `false`.
179+
* `varargs` {boolean} If `true`, `function` can accept a variable number of
180+
arguments. If `false`, `function` must be invoked with exactly
181+
`function.length` arguments. **Default:** `false`.
182+
* `function` {Function} The JavaScript function to call when the SQLite
183+
function is invoked.
184+
185+
This method is used to create SQLite user-defined functions. This method is a
186+
wrapper around [`sqlite3_create_function_v2()`][].
187+
163188
### `database.open()`
164189

165190
<!-- YAML
@@ -480,8 +505,11 @@ The following constants are meant for use with [`database.applyChangeset()`](#da
480505
[SQL injection]: https://en.wikipedia.org/wiki/SQL_injection
481506
[`ATTACH DATABASE`]: https://www.sqlite.org/lang_attach.html
482507
[`PRAGMA foreign_keys`]: https://www.sqlite.org/pragma.html#pragma_foreign_keys
508+
[`SQLITE_DETERMINISTIC`]: https://www.sqlite.org/c3ref/c_deterministic.html
509+
[`SQLITE_DIRECTONLY`]: https://www.sqlite.org/c3ref/c_deterministic.html
483510
[`sqlite3_changes64()`]: https://www.sqlite.org/c3ref/changes.html
484511
[`sqlite3_close_v2()`]: https://www.sqlite.org/c3ref/close.html
512+
[`sqlite3_create_function_v2()`]: https://www.sqlite.org/c3ref/create_function.html
485513
[`sqlite3_exec()`]: https://www.sqlite.org/c3ref/exec.html
486514
[`sqlite3_expanded_sql()`]: https://www.sqlite.org/c3ref/expanded_sql.html
487515
[`sqlite3_last_insert_rowid()`]: https://www.sqlite.org/c3ref/last_insert_rowid.html

‎src/node_sqlite.cc‎

Lines changed: 265 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -28,6 +28,8 @@ using v8::Function;
2828
using v8::FunctionCallback;
2929
using v8::FunctionCallbackInfo;
3030
using v8::FunctionTemplate;
31+
using v8::Global;
32+
using v8::Int32;
3133
using v8::Integer;
3234
using v8::Isolate;
3335
using v8::Local;
@@ -112,6 +114,123 @@ inline void THROW_ERR_SQLITE_ERROR(Isolate* isolate, const char* message) {
112114
}
113115
}
114116

117+
class UserDefinedFunction {
118+
public:
119+
explicit UserDefinedFunction(Environment* env,
120+
Local<Function> fn,
121+
bool use_bigint_args)
122+
: env_(env), fn_(env->isolate(), fn), use_bigint_args_(use_bigint_args) {}
123+
virtual ~UserDefinedFunction() {}
124+
125+
static void xFunc(sqlite3_context* ctx, int argc, sqlite3_value** argv) {
126+
UserDefinedFunction* self =
127+
static_cast<UserDefinedFunction*>(sqlite3_user_data(ctx));
128+
Environment* env = self->env_;
129+
Isolate* isolate = env->isolate();
130+
auto recv = Undefined(isolate);
131+
auto fn = self->fn_.Get(isolate);
132+
LocalVector<Value> js_argv(isolate);
133+
134+
for (int i = 0; i < argc; ++i) {
135+
sqlite3_value* value = argv[i];
136+
MaybeLocal<Value> js_val;
137+
138+
switch (sqlite3_value_type(value)) {
139+
case SQLITE_INTEGER: {
140+
sqlite3_int64 val = sqlite3_value_int64(value);
141+
if (self->use_bigint_args_) {
142+
js_val = BigInt::New(isolate, val);
143+
} else if (std::abs(val) <= kMaxSafeJsInteger) {
144+
js_val = Number::New(isolate, val);
145+
} else {
146+
THROW_ERR_OUT_OF_RANGE(isolate,
147+
"Value is too large to be represented as a "
148+
"JavaScript number: %" PRId64,
149+
val);
150+
return;
151+
}
152+
break;
153+
}
154+
case SQLITE_FLOAT:
155+
js_val = Number::New(isolate, sqlite3_value_double(value));
156+
break;
157+
case SQLITE_TEXT: {
158+
const char* v =
159+
reinterpret_cast<const char*>(sqlite3_value_text(value));
160+
js_val = String::NewFromUtf8(isolate, v).As<Value>();
161+
break;
162+
}
163+
case SQLITE_NULL:
164+
js_val = Null(isolate);
165+
break;
166+
case SQLITE_BLOB: {
167+
size_t size = static_cast<size_t>(sqlite3_value_bytes(value));
168+
auto data =
169+
reinterpret_cast<const uint8_t*>(sqlite3_value_blob(value));
170+
auto store = ArrayBuffer::NewBackingStore(isolate, size);
171+
memcpy(store->Data(), data, size);
172+
auto ab = ArrayBuffer::New(isolate, std::move(store));
173+
js_val = Uint8Array::New(ab, 0, size);
174+
break;
175+
}
176+
default:
177+
UNREACHABLE("Bad SQLite value");
178+
}
179+
180+
Local<Value> local;
181+
if (!js_val.ToLocal(&local)) {
182+
return;
183+
}
184+
185+
js_argv.emplace_back(local);
186+
}
187+
188+
MaybeLocal<Value> retval =
189+
fn->Call(env->context(), recv, argc, js_argv.data());
190+
Local<Value> result;
191+
if (!retval.ToLocal(&result)) {
192+
return;
193+
}
194+
195+
if (result->IsUndefined() || result->IsNull()) {
196+
sqlite3_result_null(ctx);
197+
} else if (result->IsNumber()) {
198+
sqlite3_result_double(ctx, result.As<Number>()->Value());
199+
} else if (result->IsString()) {
200+
Utf8Value val(isolate, result.As<String>());
201+
sqlite3_result_text(ctx, *val, val.length(), SQLITE_TRANSIENT);
202+
} else if (result->IsUint8Array()) {
203+
ArrayBufferViewContents<uint8_t> buf(result);
204+
sqlite3_result_blob(ctx, buf.data(), buf.length(), SQLITE_TRANSIENT);
205+
} else if (result->IsBigInt()) {
206+
bool lossless;
207+
int64_t as_int = result.As<BigInt>()->Int64Value(&lossless);
208+
if (!lossless) {
209+
sqlite3_result_error(ctx, "BigInt value is too large for SQLite", -1);
210+
return;
211+
}
212+
sqlite3_result_int64(ctx, as_int);
213+
} else if (result->IsPromise()) {
214+
sqlite3_result_error(
215+
ctx, "Asynchronous user-defined functions are not supported", -1);
216+
} else {
217+
sqlite3_result_error(
218+
ctx,
219+
"Returned JavaScript value cannot be converted to a SQLite value",
220+
-1);
221+
}
222+
}
223+
224+
static void xDestroy(void* self) {
225+
delete static_cast<UserDefinedFunction*>(self);
226+
}
227+
228+
private:
229+
Environment* env_;
230+
Global<Function> fn_;
231+
bool use_bigint_args_;
232+
};
233+
115234
DatabaseSync::DatabaseSync(Environment* env,
116235
Local<Object> object,
117236
DatabaseOpenConfiguration&& open_config,
@@ -400,6 +519,151 @@ void DatabaseSync::Exec(const FunctionCallbackInfo<Value>& args) {
400519
CHECK_ERROR_OR_THROW(env->isolate(), db->connection_, r, SQLITE_OK, void());
401520
}
402521

522+
void DatabaseSync::CustomFunction(const FunctionCallbackInfo<Value>& args) {
523+
DatabaseSync* db;
524+
ASSIGN_OR_RETURN_UNWRAP(&db, args.This());
525+
Environment* env = Environment::GetCurrent(args);
526+
THROW_AND_RETURN_ON_BAD_STATE(env, !db->IsOpen(), "database is not open");
527+
528+
if (!args[0]->IsString()) {
529+
THROW_ERR_INVALID_ARG_TYPE(env->isolate(),
530+
"The \"name\" argument must be a string.");
531+
return;
532+
}
533+
534+
int fn_index = args.Length() < 3 ? 1 : 2;
535+
bool use_bigint_args = false;
536+
bool varargs = false;
537+
bool deterministic = false;
538+
bool direct_only = false;
539+
540+
if (fn_index > 1) {
541+
if (!args[1]->IsObject()) {
542+
THROW_ERR_INVALID_ARG_TYPE(env->isolate(),
543+
"The \"options\" argument must be an object.");
544+
return;
545+
}
546+
547+
Local<Object> options = args[1].As<Object>();
548+
Local<Value> use_bigint_args_v;
549+
if (!options
550+
->Get(env->context(),
551+
FIXED_ONE_BYTE_STRING(env->isolate(), "useBigIntArguments"))
552+
.ToLocal(&use_bigint_args_v)) {
553+
return;
554+
}
555+
556+
if (!use_bigint_args_v->IsUndefined()) {
557+
if (!use_bigint_args_v->IsBoolean()) {
558+
THROW_ERR_INVALID_ARG_TYPE(
559+
env->isolate(),
560+
"The \"options.useBigIntArguments\" argument must be a boolean.");
561+
return;
562+
}
563+
use_bigint_args = use_bigint_args_v.As<Boolean>()->Value();
564+
}
565+
566+
Local<Value> varargs_v;
567+
if (!options
568+
->Get(env->context(),
569+
FIXED_ONE_BYTE_STRING(env->isolate(), "varargs"))
570+
.ToLocal(&varargs_v)) {
571+
return;
572+
}
573+
574+
if (!varargs_v->IsUndefined()) {
575+
if (!varargs_v->IsBoolean()) {
576+
THROW_ERR_INVALID_ARG_TYPE(
577+
env->isolate(),
578+
"The \"options.varargs\" argument must be a boolean.");
579+
return;
580+
}
581+
varargs = varargs_v.As<Boolean>()->Value();
582+
}
583+
584+
Local<Value> deterministic_v;
585+
if (!options
586+
->Get(env->context(),
587+
FIXED_ONE_BYTE_STRING(env->isolate(), "deterministic"))
588+
.ToLocal(&deterministic_v)) {
589+
return;
590+
}
591+
592+
if (!deterministic_v->IsUndefined()) {
593+
if (!deterministic_v->IsBoolean()) {
594+
THROW_ERR_INVALID_ARG_TYPE(
595+
env->isolate(),
596+
"The \"options.deterministic\" argument must be a boolean.");
597+
return;
598+
}
599+
deterministic = deterministic_v.As<Boolean>()->Value();
600+
}
601+
602+
Local<Value> direct_only_v;
603+
if (!options
604+
->Get(env->context(),
605+
FIXED_ONE_BYTE_STRING(env->isolate(), "directOnly"))
606+
.ToLocal(&direct_only_v)) {
607+
return;
608+
}
609+
610+
if (!direct_only_v->IsUndefined()) {
611+
if (!direct_only_v->IsBoolean()) {
612+
THROW_ERR_INVALID_ARG_TYPE(
613+
env->isolate(),
614+
"The \"options.directOnly\" argument must be a boolean.");
615+
return;
616+
}
617+
direct_only = direct_only_v.As<Boolean>()->Value();
618+
}
619+
}
620+
621+
if (!args[fn_index]->IsFunction()) {
622+
THROW_ERR_INVALID_ARG_TYPE(env->isolate(),
623+
"The \"function\" argument must be a function.");
624+
return;
625+
}
626+
627+
Utf8Value name(env->isolate(), args[0].As<String>());
628+
Local<Function> fn = args[fn_index].As<Function>();
629+
630+
int argc = 0;
631+
if (varargs) {
632+
argc = -1;
633+
} else {
634+
Local<Value> js_len;
635+
if (!fn->Get(env->context(),
636+
FIXED_ONE_BYTE_STRING(env->isolate(), "length"))
637+
.ToLocal(&js_len)) {
638+
return;
639+
}
640+
argc = js_len.As<Int32>()->Value();
641+
}
642+
643+
UserDefinedFunction* user_data =
644+
new UserDefinedFunction(env, fn, use_bigint_args);
645+
int text_rep = SQLITE_UTF8;
646+
647+
if (deterministic) {
648+
text_rep |= SQLITE_DETERMINISTIC;
649+
}
650+
651+
if (direct_only) {
652+
text_rep |= SQLITE_DIRECTONLY;
653+
}
654+
655+
int r = sqlite3_create_function_v2(db->connection_,
656+
*name,
657+
argc,
658+
text_rep,
659+
user_data,
660+
UserDefinedFunction::xFunc,
661+
nullptr,
662+
nullptr,
663+
UserDefinedFunction::xDestroy);
664+
CHECK_ERROR_OR_THROW(env->isolate(), db->connection_, r, SQLITE_OK, void());
665+
}
666+
403667
void DatabaseSync::CreateSession(const FunctionCallbackInfo<Value>& args) {
404668
std::string table;
405669
std::string db_name = "main";
@@ -1409,6 +1673,7 @@ static void Initialize(Local<Object> target,
14091673
SetProtoMethod(isolate, db_tmpl, "close", DatabaseSync::Close);
14101674
SetProtoMethod(isolate, db_tmpl, "prepare", DatabaseSync::Prepare);
14111675
SetProtoMethod(isolate, db_tmpl, "exec", DatabaseSync::Exec);
1676+
SetProtoMethod(isolate, db_tmpl, "function", DatabaseSync::CustomFunction);
14121677
SetProtoMethod(
14131678
isolate, db_tmpl, "createSession", DatabaseSync::CreateSession);
14141679
SetProtoMethod(

‎src/node_sqlite.h‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,7 @@ class DatabaseSync : public BaseObject {
5757
static void Close(const v8::FunctionCallbackInfo<v8::Value>& args);
5858
static void Prepare(const v8::FunctionCallbackInfo<v8::Value>& args);
5959
static void Exec(const v8::FunctionCallbackInfo<v8::Value>& args);
60+
static void CustomFunction(const v8::FunctionCallbackInfo<v8::Value>& args);
6061
static void CreateSession(const v8::FunctionCallbackInfo<v8::Value>& args);
6162
static void ApplyChangeset(const v8::FunctionCallbackInfo<v8::Value>& args);
6263
static void EnableLoadExtension(

0 commit comments

Comments
 (0)
Back | FazBrowse Home | New Git URL