better-sqlite3 - npm Package Compare versions

Comparing version 7.3.1 to 7.4.0

docs/api.md

@@ -16,2 +16,3 @@ # API
 - [Database#aggregate()](#aggregatename-options---this)
+- [Database#table()](#tablename-definition---this)
 - [Database#loadExtension()](#loadextensionpath-entrypoint---this)
@@ -26,2 +27,4 @@ - [Database#exec()](#execstring---this)
 
+> In-memory databases can also be created by passing a buffer returned by [`.serialize()`](#serializeoptions---buffer), instead of passing a string as the first argument.
+
 Various options are accepted:
@@ -249,2 +252,113 @@
 
+### .table(*name*, *definition*) -> *this*
+
+Registers a [virtual table](https://www.sqlite.org/vtab.html). Virtual tables can be queried just like real tables, except their results do not exist in the database file; instead, they are calculated on-the-fly by a [generator function](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/function*) in JavaScript.
+
+```js
+const fs = require('fs');
+
+db.table('filesystem_directory', {
+  columns: ['filename', 'data'],
+  rows: function* () {
+    for (const filename of fs.readdirSync(process.cwd())) {
+      const data = fs.readFileSync(filename);
+      yield { filename, data };
+    }
+  },
+});
+
+const files = db.prepare('SELECT * FROM filesystem_directory').all();
+// => [{ filename, data }, { filename, data }]
+```
+
+To generate a row in a virtual table, you can either yield an object whose keys correspond to column names, or yield an array whose elements represent columns in the order that they were declared. Every virtual table **must** declare its columns via the `columns` option.
+
+Virtual tables can be used like [table-valued functions](https://www.sqlite.org/vtab.html#tabfunc2); you can pass parameters to them, unlike regular tables.
+
+```js
+db.table('regex_matches', {
+  columns: ['match', 'capture'],
+  rows: function* (pattern, text) {
+    const regex = new RegExp(pattern, 'g');
+    let match;
+
+    while (match = regex.exec(text)) {
+      yield [match[0], match[1]];
+    }
+  },
+});
+
+const stmt = db.prepare("SELECT * FROM regex('\\$(\\d+)', ?)");
+
+stmt.all('Desks cost $500 and chairs cost $27');
+// => [{ match: '$500', capture: '500' }, { match: '$27', capture: '27' }]
+```
+
+By default, the number of parameters accepted by a virtual table is inferred by `function.length`, and the parameters are automatically named `$1`, `$2`, etc. However, you can optionally provide an explicit list of parameters via the `parameters` option.
+
+```js
+db.table('regex_matches', {
+  columns: ['match', 'capture'],
+  parameters: ['pattern', 'text'],
+  rows: function* (pattern, text) {
+    ...
+  },
+});
+```
+
+> In virtual tables, parameters are actually [*hidden columns*](https://www.sqlite.org/vtab.html#hidden_columns_in_virtual_tables), and they can be selected in the result set of a query, just like any other column. That's why it may sometimes be desirable to give them explicit names.
+
+When querying a virtual table, any omitted parameters will be `undefined`. You can use this behavior to implement required parameters and default parameter values.
+
+```js
+db.table('sequence', {
+  columns: ['value'],
+  parameters: ['length', 'start'],
+  rows: function* (length, start = 0) {
+    if (length === undefined) {
+      throw new TypeError('missing required parameter "length"');
+    }
+
+    const end = start + length;
+    for (let n = start; n < end; ++n) {
+      yield { value: n };
+    }
+  },
+});
+
+db.prepare('SELECT * FROM sequence(10)').pluck().all();
+// => [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
+```
+
+> Note that when using syntax like `start = 0` for default parameter values (shown above), the function's `.length` property does not include the optional parameter, so you need to explicitly declare `parameters` in this case.
+
+Normally, when you register a virtual table, the virtual table *automatically exists* without needing to run a `CREATE VIRTUAL TABLE` statement. However, if you provide a factory function as the second argument (a function that *returns* virtual table definitions), then no virtual table will be created automatically. Instead, you can create multiple similar virtual tables by running [`CREATE VIRTUAL TABLE`](https://sqlite.org/lang_createvtab.html) statements, each with their own module arguments. Think of it like defining a virtual table "class" that can be instantiated by running `CREATE VIRTUAL TABLE` statements.
+
+```js
+const fs = require('fs');
+
+db.table('csv', (filename) => {
+  const firstLine = getFirstLineOfFile(filename);
+  return {
+    columns: firstLine.split(','),
+    rows: function* () {
+      // This is just an example. Real CSV files are more complicated to parse.
+      const contents = fs.readFileSync(filename, 'utf8');
+      for (const line of contents.split('\n')) {
+        yield line.split(',');
+      }
+    },
+  };
+});
+
+db.exec('CREATE VIRTUAL TABLE my_data USING csv(my_data.csv)');
+const allData = db.prepare('SELECT * FROM my_data').all();
+```
+
+The factory function will be invoked each time a corresponding `CREATE VIRTUAL TABLE` statement runs. The arguments to the factory function correspond to the module arguments passed in the `CREATE VIRTUAL TABLE` statement; always a list of arbitrary strings separated by commas. It's your responsibility to parse and interpret those module arguments. Note that SQLite3 does not allow [bound parameters](#binding-parameters) inside module arguments.
+
+Just like [user-defined functions](#functionname-options-function---this) and [user-defined aggregates](#aggregatename-options---this), virtual tables support `options.directOnly`, which prevents the table from being used inside [VIEWs](https://sqlite.org/lang_createview.html), [TRIGGERs](https://sqlite.org/lang_createtrigger.html), or schema structures such as [CHECK constraints](https://www.sqlite.org/lang_createtable.html#ckconst), [DEFAULT clauses](https://www.sqlite.org/lang_createtable.html#dfltval), etc.
+
+> Some [extensions](#loadextensionpath-entrypoint---this) can provide virtual tables that have write capabilities, but `db.table()` is only capable of creating read-only virtual tables, primarily for the purpose of supporting table-valued functions.
+
 ### .loadExtension(*path*, [*entryPoint*]) -> *this*
@@ -251,0 +365,0 @@

docs/integer.md

@@ -55,2 +55,26 @@ # The `BigInt` primitive type
 
+Likewise, [user-defined aggregates](./api.md#aggregatename-options---this) and [virtual tables](./api.md#tablename-definition---this) can also receive `BigInts` as arguments:
+
+```js
+db.aggregate('addInts', {
+  safeIntegers: true,
+  start: 0n,
+  step: (total, nextValue) => total + nextValue,
+});
+```
+
+```js
+db.table('sequence', {
+  safeIntegers: true,
+  columns: ['value'],
+  parameters: ['length', 'start'],
+  rows: function* (length, start = 0n) {
+    const end = start + length;
+    for (let n = start; n < end; ++n) {
+      yield { value: n };
+    }
+  },
+});
+```
+
 It's worth noting that REAL (FLOAT) values returned from the database will always be represented as normal numbers.

lib/database.js

@@ -64,2 +64,3 @@ 'use strict';
 Database.prototype.aggregate = require('./methods/aggregate');
+Database.prototype.table = require('./methods/table');
 Database.prototype.loadExtension = wrappers.loadExtension;
@@ -66,0 +67,0 @@ Database.prototype.exec = wrappers.exec;

package.json

 {
   "name": "better-sqlite3",
-  "version": "7.3.1",
+  "version": "7.4.0",
   "description": "The fastest and simplest library for SQLite3 in Node.js.",
@@ -5,0 +5,0 @@ "homepage": "http://github.com/JoshuaWise/better-sqlite3",

README.md

@@ -8,3 +8,3 @@ # better-sqlite3 [![Build Status](https://github.com/JoshuaWise/better-sqlite3/actions/workflows/build.yml/badge.svg)](https://github.com/JoshuaWise/better-sqlite3/actions/workflows/build.yml?query=branch%3Amaster)
 - Easy-to-use synchronous API *(better concurrency than an asynchronous API... yes, you read that correctly)*
-- Support for user-defined functions, aggregates, and extensions
+- Support for user-defined functions, aggregates, virtual tables, and extensions
 - 64-bit integers *(invisible until you need them)*
@@ -11,0 +11,0 @@ - Worker thread support *(for large/slow queries)*

test/11.database.close.js

@@ -33,2 +33,3 @@ 'use strict';
 		expect(() => this.db.aggregate('foo', { step: () => {} })).to.throw(TypeError);
+		expect(() => this.db.table('foo', () => {})).to.throw(TypeError);
 	});
@@ -35,0 +36,0 @@ it('should prevent any existing statements from running', function () {

test/40.bigints.js

@@ -65,2 +65,12 @@ 'use strict';
 	});
+	it('should get passed to aggregates defined with the "safeIntegers" option', function () {
+		this.db.aggregate('customagg', { safeIntegers: true, step: (_, a) => { return (typeof a) + a; } });
+		expect(this.db.prepare('SELECT customagg(?)').pluck().get(2)).to.equal('number2');
+		expect(this.db.prepare('SELECT customagg(?)').pluck().get(BigInt(2))).to.equal('bigint2');
+	});
+	it('should get passed to virtual tables defined with the "safeIntegers" option', function () {
+		this.db.table('customvtab', { safeIntegers: true, columns: ['x'], *rows(a) { yield [(typeof a) + a]; } });
+		expect(this.db.prepare('SELECT * FROM customvtab(?)').pluck().get(2)).to.equal('number2');
+		expect(this.db.prepare('SELECT * FROM customvtab(?)').pluck().get(BigInt(2))).to.equal('bigint2');
+	});
 	it('should respect the default setting on the database', function () {
@@ -74,2 +84,12 @@ let arg;
 		};
+		const customAggregateArg = (name, options, dontDefine) => {
+			dontDefine || this.db.aggregate(name, { ...options, step: (_, a) => { arg = a; } });
+			this.db.prepare(`SELECT ${name}(?)`).get(int);
+			return arg;
+		};
+		const customTableArg = (name, options, dontDefine) => {
+			dontDefine || this.db.table(name, { ...options, columns: ['x'], *rows(a) { arg = a; } });
+			this.db.prepare(`SELECT * FROM ${name}(?)`).get(int);
+			return arg;
+		};
 		this.db.prepare('INSERT INTO entries VALUES (?, ?, ?)').run(int, int, int);
@@ -83,2 +103,6 @@ this.db.defaultSafeIntegers(true);
 		expect(customFunctionArg('a2', { safeIntegers: false })).to.equal(1006028374637854700);
+		expect(customAggregateArg('a1')).to.deep.equal(int);
+		expect(customAggregateArg('a2', { safeIntegers: false })).to.equal(1006028374637854700);
+		expect(customTableArg('a1')).to.deep.equal(int);
+		expect(customTableArg('a2', { safeIntegers: false })).to.equal(1006028374637854700);
 
@@ -92,2 +116,6 @@ this.db.defaultSafeIntegers(false);
 		expect(customFunctionArg('a4', { safeIntegers: true })).to.deep.equal(int);
+		expect(customAggregateArg('a3')).to.equal(1006028374637854700);
+		expect(customAggregateArg('a4', { safeIntegers: true })).to.deep.equal(int);
+		expect(customTableArg('a3')).to.equal(1006028374637854700);
+		expect(customTableArg('a4', { safeIntegers: true })).to.deep.equal(int);
 
@@ -102,2 +130,10 @@ this.db.defaultSafeIntegers();
 		expect(customFunctionArg('a4', {}, true)).to.deep.equal(int);
+		expect(customAggregateArg('a1', {}, true)).to.deep.equal(int);
+		expect(customAggregateArg('a2', {}, true)).to.equal(1006028374637854700);
+		expect(customAggregateArg('a3', {}, true)).to.equal(1006028374637854700);
+		expect(customAggregateArg('a4', {}, true)).to.deep.equal(int);
+		expect(customTableArg('a1', {}, true)).to.deep.equal(int);
+		expect(customTableArg('a2', {}, true)).to.equal(1006028374637854700);
+		expect(customTableArg('a3', {}, true)).to.equal(1006028374637854700);
+		expect(customTableArg('a4', {}, true)).to.deep.equal(int);
 
@@ -109,3 +145,7 @@ const stmt3 = this.db.prepare('SELECT a FROM entries').pluck();
 		expect(customFunctionArg('a6', { safeIntegers: false })).to.equal(1006028374637854700);
+		expect(customAggregateArg('a5')).to.deep.equal(int);
+		expect(customAggregateArg('a6', { safeIntegers: false })).to.equal(1006028374637854700);
+		expect(customTableArg('a5')).to.deep.equal(int);
+		expect(customTableArg('a6', { safeIntegers: false })).to.equal(1006028374637854700);
 	});
 });

test/42.integrity.js

@@ -170,2 +170,22 @@ 'use strict';
 
+	describe('Database#table()', function () {
+		specify('while iterating (blocked)', function () {
+			let i = 0;
+			whileIterating(this, blocked(() => this.db.table(`tbl_${++i}`, { columns: ['x'], *rows() {} })));
+			expect(i).to.equal(5);
+			normally(allowed(() => this.db.table(`tbl_${++i}`, { columns: ['x'], *rows() {} })));
+		});
+		specify('while busy (blocked)', function () {
+			let i = 0;
+			whileBusy(this, blocked(() => this.db.table(`tbl_${++i}`, { columns: ['x'], *rows() {} })));
+			expect(i).to.equal(5);
+			normally(allowed(() => this.db.table(`tbl_${++i}`, { columns: ['x'], *rows() {} })));
+		});
+		specify('while closed (blocked)', function () {
+			let i = 0;
+			whileClosed(this, blocked(() => this.db.table(`tbl_${++i}`, { columns: ['x'], *rows() {} })));
+			expect(i).to.equal(1);
+		});
+	});
+
 	describe('Database#loadExtension()', function () {
@@ -172,0 +192,0 @@ let filepath;

New alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Fixed alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

2846228

increased by3.01%

Number of package files

92

increased by4.55%

Lines of code

4977

increased by21.87%

No dependency changes