GeezerVison is showing github.com — sqlite: add sqlite prepare options args · nodejs/node@069f360
File treeExpand file treeCollapse file tree Open diff view settings Expand file treeCollapse file tree Open diff view settings Collapse file Copy file name to clipboardExpand all lines: doc/api/sqlite.md +11-1Lines changed: 11 additions & 1 deletion - Display the source diff
- Display the rich diff
| Original file line number | Diff line number | Diff line change |
|---|
@@ -451,13 +451,23 @@ Opens the database specified in the `path` argument of the `DatabaseSync` | 451 | 451 | constructor. This method should only be used when the database is not opened via | 452 | 452 | the constructor. An exception is thrown if the database is already open. | 453 | 453 |
| 454 | | -### `database.prepare(sql)` | | 454 | +### `database.prepare(sql[, options])` | 455 | 455 |
| 456 | 456 | <!-- YAML | 457 | 457 | added: v22.5.0 | 458 | 458 | --> | 459 | 459 |
| 460 | 460 | * `sql` {string} A SQL string to compile to a prepared statement. | | 461 | +* `options` {Object} Optional configuration for the prepared statement. | | 462 | + * `readBigInts` {boolean} If `true`, integer fields are read as `BigInt`s. | | 463 | + **Default:** inherited from database options or `false`. | | 464 | + * `returnArrays` {boolean} If `true`, results are returned as arrays. | | 465 | + **Default:** inherited from database options or `false`. | | 466 | + * `allowBareNamedParameters` {boolean} If `true`, allows binding named | | 467 | + parameters without the prefix character. **Default:** inherited from | | 468 | + database options or `true`. | | 469 | + * `allowUnknownNamedParameters` {boolean} If `true`, unknown named parameters | | 470 | + are ignored. **Default:** inherited from database options or `false`. | 461 | 471 | * Returns: {StatementSync} The prepared statement. | 462 | 472 |
| 463 | 473 | Compiles a SQL statement into a [prepared statement][]. This method is a wrapper | |
Collapse file Copy file name to clipboardExpand all lines: src/node_sqlite.cc +100Lines changed: 100 additions & 0 deletions | Original file line number | Diff line number | Diff line change |
|---|
@@ -1147,6 +1147,92 @@ void DatabaseSync::Prepare(const FunctionCallbackInfo<Value>& args) { | 1147 | 1147 | return; | 1148 | 1148 | } | 1149 | 1149 |
| | 1150 | + std::optional<bool> return_arrays; | | 1151 | + std::optional<bool> use_big_ints; | | 1152 | + std::optional<bool> allow_bare_named_params; | | 1153 | + std::optional<bool> allow_unknown_named_params; | | 1154 | + | | 1155 | + if (args.Length() > 1 && !args[1]->IsUndefined()) { | | 1156 | + if (!args[1]->IsObject()) { | | 1157 | + THROW_ERR_INVALID_ARG_TYPE(env->isolate(), | | 1158 | + "The \"options\" argument must be an object."); | | 1159 | + return; | | 1160 | + } | | 1161 | + Local<Object> options = args[1].As<Object>(); | | 1162 | + | | 1163 | + Local<Value> return_arrays_v; | | 1164 | + if (!options | | 1165 | + ->Get(env->context(), | | 1166 | + FIXED_ONE_BYTE_STRING(env->isolate(), "returnArrays")) | | 1167 | + .ToLocal(&return_arrays_v)) { | | 1168 | + return; | | 1169 | + } | | 1170 | + if (!return_arrays_v->IsUndefined()) { | | 1171 | + if (!return_arrays_v->IsBoolean()) { | | 1172 | + THROW_ERR_INVALID_ARG_TYPE( | | 1173 | + env->isolate(), | | 1174 | + "The \"options.returnArrays\" argument must be a boolean."); | | 1175 | + return; | | 1176 | + } | | 1177 | + return_arrays = return_arrays_v->IsTrue(); | | 1178 | + } | | 1179 | + | | 1180 | + Local<Value> read_big_ints_v; | | 1181 | + if (!options | | 1182 | + ->Get(env->context(), | | 1183 | + FIXED_ONE_BYTE_STRING(env->isolate(), "readBigInts")) | | 1184 | + .ToLocal(&read_big_ints_v)) { | | 1185 | + return; | | 1186 | + } | | 1187 | + if (!read_big_ints_v->IsUndefined()) { | | 1188 | + if (!read_big_ints_v->IsBoolean()) { | | 1189 | + THROW_ERR_INVALID_ARG_TYPE( | | 1190 | + env->isolate(), | | 1191 | + "The \"options.readBigInts\" argument must be a boolean."); | | 1192 | + return; | | 1193 | + } | | 1194 | + use_big_ints = read_big_ints_v->IsTrue(); | | 1195 | + } | | 1196 | + | | 1197 | + Local<Value> allow_bare_named_params_v; | | 1198 | + if (!options | | 1199 | + ->Get(env->context(), | | 1200 | + FIXED_ONE_BYTE_STRING(env->isolate(), | | 1201 | + "allowBareNamedParameters")) | | 1202 | + .ToLocal(&allow_bare_named_params_v)) { | | 1203 | + return; | | 1204 | + } | | 1205 | + if (!allow_bare_named_params_v->IsUndefined()) { | | 1206 | + if (!allow_bare_named_params_v->IsBoolean()) { | | 1207 | + THROW_ERR_INVALID_ARG_TYPE( | | 1208 | + env->isolate(), | | 1209 | + "The \"options.allowBareNamedParameters\" argument must be a " | | 1210 | + "boolean."); | | 1211 | + return; | | 1212 | + } | | 1213 | + allow_bare_named_params = allow_bare_named_params_v->IsTrue(); | | 1214 | + } | | 1215 | + | | 1216 | + Local<Value> allow_unknown_named_params_v; | | 1217 | + if (!options | | 1218 | + ->Get(env->context(), | | 1219 | + FIXED_ONE_BYTE_STRING(env->isolate(), | | 1220 | + "allowUnknownNamedParameters")) | | 1221 | + .ToLocal(&allow_unknown_named_params_v)) { | | 1222 | + return; | | 1223 | + } | | 1224 | + if (!allow_unknown_named_params_v->IsUndefined()) { | | 1225 | + if (!allow_unknown_named_params_v->IsBoolean()) { | | 1226 | + THROW_ERR_INVALID_ARG_TYPE( | | 1227 | + env->isolate(), | | 1228 | + "The \"options.allowUnknownNamedParameters\" argument must be a " | | 1229 | + "boolean."); | | 1230 | + return; | | 1231 | + } | | 1232 | + allow_unknown_named_params = allow_unknown_named_params_v->IsTrue(); | | 1233 | + } | | 1234 | + } | | 1235 | + | 1150 | 1236 | Utf8Value sql(env->isolate(), args[0].As<String>()); | 1151 | 1237 | sqlite3_stmt* s = nullptr; | 1152 | 1238 | int r = sqlite3_prepare_v2(db->connection_, *sql, -1, &s, 0); | @@ -1155,6 +1241,20 @@ void DatabaseSync::Prepare(const FunctionCallbackInfo<Value>& args) { | 1155 | 1241 | BaseObjectPtr<StatementSync> stmt = | 1156 | 1242 | StatementSync::Create(env, BaseObjectPtr<DatabaseSync>(db), s); | 1157 | 1243 | db->statements_.insert(stmt.get()); | | 1244 | + | | 1245 | + if (return_arrays.has_value()) { | | 1246 | + stmt->return_arrays_ = return_arrays.value(); | | 1247 | + } | | 1248 | + if (use_big_ints.has_value()) { | | 1249 | + stmt->use_big_ints_ = use_big_ints.value(); | | 1250 | + } | | 1251 | + if (allow_bare_named_params.has_value()) { | | 1252 | + stmt->allow_bare_named_params_ = allow_bare_named_params.value(); | | 1253 | + } | | 1254 | + if (allow_unknown_named_params.has_value()) { | | 1255 | + stmt->allow_unknown_named_params_ = allow_unknown_named_params.value(); | | 1256 | + } | | 1257 | + | 1158 | 1258 | args.GetReturnValue().Set(stmt->object()); | 1159 | 1259 | } | 1160 | 1260 |
| |
Collapse file Copy file name to clipboardExpand all lines: src/node_sqlite.h +1Lines changed: 1 addition & 0 deletions | Original file line number | Diff line number | Diff line change |
|---|
@@ -246,6 +246,7 @@ class StatementSync : public BaseObject { | 246 | 246 | bool BindParams(const v8::FunctionCallbackInfo<v8::Value>& args); | 247 | 247 | bool BindValue(const v8::Local<v8::Value>& value, const int index); | 248 | 248 |
| | 249 | + friend class DatabaseSync; | 249 | 250 | friend class StatementSyncIterator; | 250 | 251 | friend class SQLTagStore; | 251 | 252 | friend class StatementExecutionHelper; | |
Collapse file Copy file name to clipboardExpand all lines: test/parallel/test-sqlite-named-parameters.js +100Lines changed: 100 additions & 0 deletions | Original file line number | Diff line number | Diff line change |
|---|
@@ -119,3 +119,103 @@ suite('StatementSync.prototype.setAllowUnknownNamedParameters()', () => { | 119 | 119 | }); | 120 | 120 | }); | 121 | 121 | }); | | 122 | + | | 123 | +suite('options.allowUnknownNamedParameters', () => { | | 124 | + test('unknown named parameters are allowed when input is true', (t) => { | | 125 | + const db = new DatabaseSync(':memory:'); | | 126 | + t.after(() => { db.close(); }); | | 127 | + const setup = db.exec( | | 128 | + 'CREATE TABLE data(key INTEGER, val INTEGER) STRICT;' | | 129 | + ); | | 130 | + t.assert.strictEqual(setup, undefined); | | 131 | + const stmt = db.prepare( | | 132 | + 'INSERT INTO data (key, val) VALUES ($k, $v)', | | 133 | + { allowUnknownNamedParameters: true } | | 134 | + ); | | 135 | + const params = { $a: 1, $b: 2, $k: 42, $y: 25, $v: 84, $z: 99 }; | | 136 | + t.assert.deepStrictEqual( | | 137 | + stmt.run(params), | | 138 | + { changes: 1, lastInsertRowid: 1 }, | | 139 | + ); | | 140 | + }); | | 141 | + | | 142 | + test('unknown named parameters throw when input is false', (t) => { | | 143 | + const db = new DatabaseSync(':memory:'); | | 144 | + t.after(() => { db.close(); }); | | 145 | + const setup = db.exec( | | 146 | + 'CREATE TABLE data(key INTEGER, val INTEGER) STRICT;' | | 147 | + ); | | 148 | + t.assert.strictEqual(setup, undefined); | | 149 | + const stmt = db.prepare( | | 150 | + 'INSERT INTO data (key, val) VALUES ($k, $v)', | | 151 | + { allowUnknownNamedParameters: false } | | 152 | + ); | | 153 | + const params = { $a: 1, $b: 2, $k: 42, $y: 25, $v: 84, $z: 99 }; | | 154 | + t.assert.throws(() => { | | 155 | + stmt.run(params); | | 156 | + }, { | | 157 | + code: 'ERR_INVALID_STATE', | | 158 | + message: /Unknown named parameter '\$a'/, | | 159 | + }); | | 160 | + }); | | 161 | + | | 162 | + test('unknown named parameters throws error by default', (t) => { | | 163 | + const db = new DatabaseSync(':memory:'); | | 164 | + t.after(() => { db.close(); }); | | 165 | + const setup = db.exec( | | 166 | + 'CREATE TABLE data(key INTEGER, val INTEGER) STRICT;' | | 167 | + ); | | 168 | + t.assert.strictEqual(setup, undefined); | | 169 | + const stmt = db.prepare('INSERT INTO data (key, val) VALUES ($k, $v)'); | | 170 | + const params = { $a: 1, $b: 2, $k: 42, $y: 25, $v: 84, $z: 99 }; | | 171 | + t.assert.throws(() => { | | 172 | + stmt.run(params); | | 173 | + }, { | | 174 | + code: 'ERR_INVALID_STATE', | | 175 | + message: /Unknown named parameter '\$a'/, | | 176 | + }); | | 177 | + }); | | 178 | + | | 179 | + test('throws when option is not a boolean', (t) => { | | 180 | + const db = new DatabaseSync(':memory:'); | | 181 | + t.after(() => { db.close(); }); | | 182 | + const setup = db.exec( | | 183 | + 'CREATE TABLE data(key INTEGER PRIMARY KEY, val INTEGER) STRICT;' | | 184 | + ); | | 185 | + t.assert.strictEqual(setup, undefined); | | 186 | + t.assert.throws(() => { | | 187 | + db.prepare( | | 188 | + 'INSERT INTO data (key, val) VALUES ($k, $v)', | | 189 | + { allowUnknownNamedParameters: 'true' } | | 190 | + ); | | 191 | + }, { | | 192 | + code: 'ERR_INVALID_ARG_TYPE', | | 193 | + message: /The "options\.allowUnknownNamedParameters" argument must be a boolean/, | | 194 | + }); | | 195 | + }); | | 196 | + | | 197 | + test('setAllowUnknownNamedParameters can override prepare option', (t) => { | | 198 | + const db = new DatabaseSync(':memory:'); | | 199 | + t.after(() => { db.close(); }); | | 200 | + const setup = db.exec( | | 201 | + 'CREATE TABLE data(key INTEGER, val INTEGER) STRICT;' | | 202 | + ); | | 203 | + t.assert.strictEqual(setup, undefined); | | 204 | + const stmt = db.prepare( | | 205 | + 'INSERT INTO data (key, val) VALUES ($k, $v)', | | 206 | + { allowUnknownNamedParameters: true } | | 207 | + ); | | 208 | + const params = { $a: 1, $b: 2, $k: 42, $y: 25, $v: 84, $z: 99 }; | | 209 | + t.assert.deepStrictEqual( | | 210 | + stmt.run(params), | | 211 | + { changes: 1, lastInsertRowid: 1 }, | | 212 | + ); | | 213 | + t.assert.strictEqual(stmt.setAllowUnknownNamedParameters(false), undefined); | | 214 | + t.assert.throws(() => { | | 215 | + stmt.run(params); | | 216 | + }, { | | 217 | + code: 'ERR_INVALID_STATE', | | 218 | + message: /Unknown named parameter '\$a'/, | | 219 | + }); | | 220 | + }); | | 221 | +}); |
|