15652ad800
* Don't care limit and offset constraints of user-defined virtual tables * Add a test case for #872
696 lines
30 KiB
JavaScript
696 lines
30 KiB
JavaScript
'use strict';
|
|
const Database = require('../.');
|
|
|
|
describe('Database#table()', function () {
|
|
beforeEach(function () {
|
|
this.db = new Database(util.next());
|
|
});
|
|
afterEach(function () {
|
|
this.db.close();
|
|
});
|
|
|
|
it('should throw an exception if the correct arguments are not provided', function () {
|
|
expect(() => this.db.table()).to.throw(TypeError);
|
|
expect(() => this.db.table(null)).to.throw(TypeError);
|
|
expect(() => this.db.table('a')).to.throw(TypeError);
|
|
expect(() => this.db.table({})).to.throw(TypeError);
|
|
expect(() => this.db.table({ rows: function*(){}, columns: ['x'] })).to.throw(TypeError);
|
|
expect(() => this.db.table({ name: 'b', rows: function*(){}, columns: ['x'] })).to.throw(TypeError);
|
|
expect(() => this.db.table(() => {})).to.throw(TypeError);
|
|
expect(() => this.db.table(function* c() {})).to.throw(TypeError);
|
|
expect(() => this.db.table({}, function d() {})).to.throw(TypeError);
|
|
expect(() => this.db.table({ name: 'e', rows: function* e() {}, columns: ['x'] }, function e() {})).to.throw(TypeError);
|
|
expect(() => this.db.table('f')).to.throw(TypeError);
|
|
expect(() => this.db.table('g', null)).to.throw(TypeError);
|
|
expect(() => this.db.table('h', {})).to.throw(TypeError);
|
|
expect(() => this.db.table('i', Object.create(Function.prototype))).to.throw(TypeError);
|
|
expect(() => this.db.table('j', { columns: ['x'] }, function j() {})).to.throw(TypeError);
|
|
expect(() => this.db.table('k', { name: 'k', columns: ['x'] }, function* k() {})).to.throw(TypeError);
|
|
expect(() => this.db.table('l', { name: 'l', rows: function* l() {} })).to.throw(TypeError);
|
|
expect(() => this.db.table(new String('m'), { columns: ['x'], rows: function* m() {} })).to.throw(TypeError);
|
|
expect(() => this.db.table(new String('n'), () => {})).to.throw(TypeError);
|
|
});
|
|
it('should throw an exception if boolean options are provided as non-booleans', function () {
|
|
expect(() => this.db.table('a', { columns: ['x'], rows: function*(){}, directOnly: undefined })).to.throw(TypeError);
|
|
expect(() => this.db.table('b', { columns: ['x'], rows: function*(){}, safeIntegers: undefined })).to.throw(TypeError);
|
|
});
|
|
it('should throw an exception if the "columns" option is invalid', function () {
|
|
expect(() => this.db.table('a', { rows: function*(){} })).to.throw(TypeError);
|
|
expect(() => this.db.table('b', { columns: undefined, rows: function*(){} })).to.throw(TypeError);
|
|
expect(() => this.db.table('c', { columns: 'x', rows: function*(){} })).to.throw(TypeError);
|
|
expect(() => this.db.table('d', { columns: { length: 1, 0: 'x', [Symbol.iterator]: () => ['x'].values() }, rows: function*(){} })).to.throw(TypeError);
|
|
expect(() => this.db.table('e', { columns: ['x',, 'y'], rows: function*(){} })).to.throw(TypeError);
|
|
expect(() => this.db.table('f', { columns: ['x', new String('y')], rows: function*(){} })).to.throw(TypeError);
|
|
expect(() => this.db.table('g', { columns: ['x', 'x'], rows: function*(){} })).to.throw(TypeError);
|
|
expect(() => this.db.table('h', { columns: [], rows: function*(){} })).to.throw(RangeError);
|
|
});
|
|
it('should throw an exception if the "parameters" option is invalid', function () {
|
|
expect(() => this.db.table('a', { parameters: undefined, columns: ['foo'], rows: function*(){} })).to.throw(TypeError);
|
|
expect(() => this.db.table('b', { parameters: 'x', columns: ['foo'], rows: function*(){} })).to.throw(TypeError);
|
|
expect(() => this.db.table('c', { parameters: { length: 1, 0: 'x', [Symbol.iterator]: () => ['x'].values() }, columns: ['foo'], rows: function*(){} })).to.throw(TypeError);
|
|
expect(() => this.db.table('d', { parameters: ['x',, 'y'], columns: ['foo'], rows: function*(){} })).to.throw(TypeError);
|
|
expect(() => this.db.table('e', { parameters: ['x', new String('y')], columns: ['foo'], rows: function*(){} })).to.throw(TypeError);
|
|
expect(() => this.db.table('f', { parameters: ['x', 'x'], columns: ['foo'], rows: function*(){} })).to.throw(TypeError);
|
|
expect(() => this.db.table('g', { parameters: ['x'], columns: ['x'], rows: function*(){} })).to.throw(TypeError);
|
|
expect(() => this.db.table('h', { parameters: [...Array(33)].map((_, i) => `p${i}`), columns: ['foo'], rows: function*(){} })).to.throw(RangeError);
|
|
});
|
|
it('should throw an exception if the "rows" option is invalid', function () {
|
|
expect(() => this.db.table('a', { columns: ['x'] })).to.throw(TypeError);
|
|
expect(() => this.db.table('b', { columns: ['x'], rows: undefined })).to.throw(TypeError);
|
|
expect(() => this.db.table('c', { columns: ['x'], rows: {} })).to.throw(TypeError);
|
|
expect(() => this.db.table('d', { columns: ['x'], rows: () => {} })).to.throw(TypeError);
|
|
expect(() => this.db.table('e', { columns: ['x'], rows: function () {} })).to.throw(TypeError);
|
|
expect(() => this.db.table('f', { columns: ['x'], rows: Object.create(Function.prototype) })).to.throw(TypeError);
|
|
expect(() => this.db.table('g', { columns: ['x'], rows: Object.create(Object.getPrototypeOf(function*(){})) })).to.throw(TypeError);
|
|
expect(() => this.db.table('h', { columns: ['x'], rows: Object.setPrototypeOf(() => {}, Object.create(Object.getPrototypeOf(function*(){}))) })).to.throw(TypeError);
|
|
});
|
|
it('should throw an exception if the provided name is empty', function () {
|
|
expect(() => this.db.table('', { columns: ['x'], rows: function* () {} })).to.throw(TypeError);
|
|
expect(() => this.db.table('', { name: 'a', columns: ['x'], rows: function* () {} })).to.throw(TypeError);
|
|
expect(() => this.db.table('', { name: 'b', columns: ['x'], rows: function* b() {} })).to.throw(TypeError);
|
|
expect(() => this.db.table('', function c() {})).to.throw(TypeError);
|
|
});
|
|
it('should throw an exception if generator.length is invalid', function () {
|
|
const length = x => Object.defineProperty(function*(){}, 'length', { value: x });
|
|
expect(() => this.db.table('a', { columns: ['x'], rows: length(undefined) })).to.throw(TypeError);
|
|
expect(() => this.db.table('b', { columns: ['x'], rows: length(null) })).to.throw(TypeError);
|
|
expect(() => this.db.table('c', { columns: ['x'], rows: length('1') })).to.throw(TypeError);
|
|
expect(() => this.db.table('d', { columns: ['x'], rows: length(NaN) })).to.throw(TypeError);
|
|
expect(() => this.db.table('e', { columns: ['x'], rows: length(Infinity) })).to.throw(TypeError);
|
|
expect(() => this.db.table('f', { columns: ['x'], rows: length(1.000000001) })).to.throw(TypeError);
|
|
expect(() => this.db.table('g', { columns: ['x'], rows: length(-0.000000001) })).to.throw(TypeError);
|
|
expect(() => this.db.table('h', { columns: ['x'], rows: length(-1) })).to.throw(TypeError);
|
|
expect(() => this.db.table('i', { columns: ['x'], rows: length(32.000000001) })).to.throw(TypeError);
|
|
expect(() => this.db.table('j', { columns: ['x'], rows: length(33) })).to.throw(RangeError);
|
|
});
|
|
it('should register a virtual table and return the database object', function () {
|
|
const length = x => Object.defineProperty(function*(){}, 'length', { value: x });
|
|
expect(this.db.table('a', { columns: ['x'], rows: function* () {} })).to.equal(this.db);
|
|
expect(this.db.table('b', { columns: ['x'], rows: length(1) })).to.equal(this.db);
|
|
expect(this.db.table('c', { columns: ['x'], rows: length(32) })).to.equal(this.db);
|
|
});
|
|
it('should enable the registered virtual table to be queried from SQL', function () {
|
|
const rows = [
|
|
{ a: null, b: 123, c: 456.789, d: 'foo', e: Buffer.from('bar') },
|
|
{ a: null, b: 987, c: 654.321, d: 'oof', e: Buffer.from('rab') },
|
|
];
|
|
this.db.table('vtab', {
|
|
columns: ['a', 'b', 'c', 'd', 'e'],
|
|
*rows() {
|
|
for (const obj of rows) {
|
|
yield Object.values(obj);
|
|
}
|
|
},
|
|
});
|
|
expect(this.db.prepare('SELECT * FROM vtab').all()).to.deep.equal(rows);
|
|
expect(this.db.prepare('SELECT * FROM vtab WHERE b < 500').all()).to.deep.equal(rows.slice(0, 1));
|
|
expect(this.db.prepare('SELECT * FROM vtab ORDER BY d DESC').all()).to.deep.equal(rows.slice().reverse());
|
|
});
|
|
it('should infer parameters for the virtual table', function () {
|
|
this.db.table('vtab', {
|
|
columns: ['a', 'b'],
|
|
*rows(x, y) {
|
|
yield [x, y];
|
|
yield [x * 2, y * 3];
|
|
},
|
|
});
|
|
expect(this.db.prepare('SELECT * FROM vtab(?, ?)').all(2, 3))
|
|
.to.deep.equal([{ a: 2, b: 3 }, { a: 4, b: 9 }]);
|
|
expect(this.db.prepare('SELECT * FROM vtab WHERE "$1" = ? AND "$2" = ?').all(2, 3))
|
|
.to.deep.equal([{ a: 2, b: 3 }, { a: 4, b: 9 }]);
|
|
expect(() => this.db.prepare('SELECT * FROM vtab(?, ?, ?)'))
|
|
.to.throw(Database.SqliteError);
|
|
expect(() => this.db.prepare('SELECT * FROM vtab WHERE "$1" = ? AND "$2" = ? AND "$3" = ?'))
|
|
.to.throw(Database.SqliteError);
|
|
});
|
|
it('should accept explicit parameters for the virtual table', function () {
|
|
this.db.table('vtab', {
|
|
columns: ['a', 'b'],
|
|
parameters: ['x', 'y', 'z'],
|
|
*rows(p1, p2, p3, p4) {
|
|
yield [arguments[0], arguments[1] + arguments[2]];
|
|
yield [arguments[0] * 2, (arguments[1] + arguments[2]) * 3];
|
|
},
|
|
});
|
|
expect(this.db.prepare('SELECT * FROM vtab(?, ?, ?)').all(2, 3, 4))
|
|
.to.deep.equal([{ a: 2, b: 7 }, { a: 4, b: 21 }]);
|
|
expect(this.db.prepare('SELECT * FROM vtab WHERE x = ? AND y = ? AND z = ?').all(2, 3, 4))
|
|
.to.deep.equal([{ a: 2, b: 7 }, { a: 4, b: 21 }]);
|
|
expect(() => this.db.prepare('SELECT * FROM vtab(?, ?, ?, ?)'))
|
|
.to.throw(Database.SqliteError);
|
|
expect(() => this.db.prepare('SELECT * FROM vtab WHERE "$1" = ? AND "$2" = ? AND "$3" = ?'))
|
|
.to.throw(Database.SqliteError);
|
|
});
|
|
it('should accept a large number of parameters for the virtual table', function () {
|
|
const args = ['foo', 'bar', 1, -2, Buffer.from('hello'), 5, -10, 'baz', 99.9, -0.5];
|
|
this.db.table('vtab', {
|
|
columns: ['x'],
|
|
*rows(p1, p2, p3, p4, p5, p6, p7, p8, p9, p10) {
|
|
yield [p10];
|
|
yield [p9];
|
|
yield [p8];
|
|
yield [p7];
|
|
yield [p6];
|
|
yield [p5];
|
|
yield [p4];
|
|
yield [p3];
|
|
yield [p2];
|
|
yield [p1];
|
|
},
|
|
});
|
|
expect(this.db.prepare('SELECT * FROM vtab(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)').pluck().all(args))
|
|
.to.deep.equal(args.slice().reverse());
|
|
expect(this.db.prepare('SELECT * FROM vtab(?, ?, ?, ?, ?, ?, ?, ?, ?)').pluck().all(args.slice(0, -1)))
|
|
.to.deep.equal([null].concat(args.slice(0, -1).reverse()));
|
|
expect(() => this.db.prepare('SELECT * FROM vtab(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'))
|
|
.to.throw(Database.SqliteError);
|
|
});
|
|
it('should correctly handle arguments even when used out of order', function () {
|
|
const calls = [];
|
|
this.db.table('vtab', {
|
|
columns: ['x', 'y'],
|
|
*rows(x, y) {
|
|
calls.push([...arguments]);
|
|
yield { x, y };
|
|
},
|
|
});
|
|
expect(this.db.prepare('SELECT * FROM vtab WHERE "$1" = ? AND "$2" = ?').get(10, 5))
|
|
.to.deep.equal({ x: 10, y: 5 });
|
|
expect(calls.splice(0)).to.deep.equal([[10, 5]]);
|
|
expect(this.db.prepare('SELECT * FROM vtab WHERE "$2" = ? AND "$1" = ?').get(5, 10))
|
|
.to.deep.equal({ x: 10, y: 5 });
|
|
expect(calls.splice(0)).to.deep.equal([[10, 5]]);
|
|
expect(this.db.prepare('SELECT * FROM vtab WHERE "$2" = ? AND "$2" = ? AND "$1" = ?').get(5, 5, 10))
|
|
.to.deep.equal({ x: 10, y: 5 });
|
|
expect(calls.splice(0)).to.deep.equal([[10, 5]]);
|
|
expect(this.db.prepare('SELECT * FROM vtab WHERE "$2" = ? AND "$2" = ? AND "$1" = ?').get(5, 9, 10))
|
|
.to.be.undefined;
|
|
expect(calls.splice(0)).to.deep.equal([]);
|
|
expect(this.db.prepare('SELECT * FROM vtab WHERE "$2" = ? AND "$2" = ? AND "$1" = ?').get(9, 5, 10))
|
|
.to.be.undefined;
|
|
expect(calls.splice(0)).to.deep.equal([]);
|
|
});
|
|
it('should correctly handle arguments that are constrained to other arguments', function () {
|
|
const calls = [];
|
|
this.db.table('vtab', {
|
|
columns: ['x', 'y'],
|
|
*rows(x, y) {
|
|
calls.push([...arguments]);
|
|
yield { x, y };
|
|
},
|
|
});
|
|
expect(this.db.prepare('SELECT * FROM vtab WHERE "$1" = ? AND "$2" = "$1"').get(10))
|
|
.to.deep.equal({ x: 10, y: 10 });
|
|
expect(calls.splice(0)).to.deep.equal([[10, 10]]);
|
|
expect(this.db.prepare('SELECT * FROM vtab WHERE "$2" = "$1" AND "$1" = ?').get(10))
|
|
.to.deep.equal({ x: 10, y: 10 });
|
|
expect(calls.splice(0)).to.deep.equal([[10, 10]]);
|
|
expect(this.db.prepare('SELECT * FROM vtab WHERE "$2" = ? AND "$2" = "$1" AND "$1" = ?').get(10, 10))
|
|
.to.deep.equal({ x: 10, y: 10 });
|
|
expect(calls.splice(0)).to.deep.equal([[10, 10]]);
|
|
expect(this.db.prepare('SELECT * FROM vtab WHERE "$2" = ? AND "$2" = "$1" AND "$1" = ?').get(5, 10))
|
|
.to.be.undefined;
|
|
expect(calls.splice(0)).to.deep.equal([]);
|
|
expect(this.db.prepare('SELECT * FROM vtab WHERE "$2" = "$1" AND "$2" = ? AND "$1" = ?').get(5, 10))
|
|
.to.be.undefined;
|
|
expect(calls.splice(0)).to.deep.equal([]);
|
|
});
|
|
it('should throw an exception if the database is busy', function () {
|
|
let ranOnce = false;
|
|
for (const x of this.db.prepare('SELECT 2').pluck().iterate()) {
|
|
expect(x).to.equal(2);
|
|
ranOnce = true;
|
|
expect(() => this.db.table('a', { columns: ['x'], rows: function* () {} })).to.throw(TypeError);
|
|
}
|
|
expect(ranOnce).to.be.true;
|
|
this.db.table('b', { columns: ['x'], rows: function* () {} });
|
|
});
|
|
it('should cause the database to become busy when querying the virtual table', function () {
|
|
let checkCount = 0;
|
|
const expectBusy = function* () {
|
|
for (let i = 0; i < 3; ++i) {
|
|
expect(() => this.db.exec('SELECT * FROM a')).to.throw(TypeError);
|
|
expect(() => this.db.prepare('SELECT 555')).to.throw(TypeError);
|
|
expect(() => this.db.pragma('cache_size')).to.throw(TypeError);
|
|
expect(() => this.db.function('x', () => {})).to.throw(TypeError);
|
|
expect(() => this.db.table('y', { columns: ['x'], rows: function* () {} })).to.throw(TypeError);
|
|
checkCount += 1;
|
|
yield [i];
|
|
}
|
|
};
|
|
this.db.table('a', { columns: ['x'], rows: function* () {} });
|
|
this.db.table('b', { columns: ['x'], rows: expectBusy });
|
|
|
|
expect(this.db.prepare('SELECT * FROM b').pluck().all()).to.deep.equal([0, 1, 2]);
|
|
expect(checkCount).to.equal(3);
|
|
|
|
this.db.exec('SELECT * FROM a');
|
|
this.db.prepare('SELECT 555');
|
|
this.db.pragma('cache_size');
|
|
this.db.function('xx', () => {});
|
|
this.db.table('yy', { columns: ['x'], rows: function* () {} })
|
|
});
|
|
it('should cause the virtual table to throw when yielding an invalid value', function () {
|
|
this.db.table('a', {
|
|
columns: ['x'],
|
|
*rows() { yield [42]; }
|
|
});
|
|
this.db.table('b', {
|
|
columns: ['x'],
|
|
*rows() { yield 42; }
|
|
});
|
|
this.db.table('c', {
|
|
columns: ['x'],
|
|
*rows() { yield; }
|
|
});
|
|
this.db.table('d', {
|
|
columns: ['x'],
|
|
*rows() { yield null; }
|
|
});
|
|
expect(this.db.prepare('SELECT * FROM a').get()).to.deep.equal({ x: 42 });
|
|
expect(() => this.db.prepare('SELECT * FROM b').get()).to.throw(TypeError);
|
|
expect(() => this.db.prepare('SELECT * FROM c').get()).to.throw(TypeError);
|
|
expect(() => this.db.prepare('SELECT * FROM d').get()).to.throw(TypeError);
|
|
});
|
|
it('should allow arrays to be yielded as rows', function () {
|
|
const rows = [
|
|
{ a: null, b: 123, c: 456.789, d: 'foo', e: Buffer.from('bar') },
|
|
{ a: null, b: 987, c: 654.321, d: 'oof', e: Buffer.from('rab') },
|
|
];
|
|
this.db.table('vtab', {
|
|
columns: ['a', 'b', 'c', 'd', 'e'],
|
|
*rows() {
|
|
for (const obj of rows) {
|
|
yield Object.values(obj);
|
|
}
|
|
},
|
|
});
|
|
expect(this.db.prepare('SELECT * FROM vtab').all()).to.deep.equal(rows);
|
|
});
|
|
it('should allow objects to be yielded as rows', function () {
|
|
const rows = [
|
|
{ a: null, b: 123, c: 456.789, d: 'foo', e: Buffer.from('bar') },
|
|
{ a: null, b: 987, c: 654.321, d: 'oof', e: Buffer.from('rab') },
|
|
{ e: Buffer.from('hello'), d: 'world', c: 0.1, b: 10, a: null },
|
|
{ d: 'old friend', c: -0.1, e: Buffer.from('goodbye'), a: null, b: -10 },
|
|
];
|
|
this.db.table('vtab', {
|
|
columns: ['a', 'b', 'c', 'd', 'e'],
|
|
*rows() {
|
|
for (const obj of rows) {
|
|
yield obj;
|
|
}
|
|
},
|
|
});
|
|
expect(this.db.prepare('SELECT * FROM vtab').all()).to.deep.equal(rows);
|
|
});
|
|
it('should throw an exception if an invalid array is yielded', function () {
|
|
const tests = [
|
|
[1, 2, 3, 4, 5],
|
|
[1, 2, 3, 4, 5, 6],
|
|
[1, 2, 3, 4],
|
|
[],
|
|
[1, 2, 3, 4, new Number(5)],
|
|
[1, 2, 3, 4, [5]],
|
|
[1, 2, 3, 4, new Date()],
|
|
];
|
|
this.db.table('vtab', {
|
|
columns: ['a', 'b', 'c', 'd', 'e'],
|
|
*rows(n) {
|
|
yield tests[n];
|
|
},
|
|
});
|
|
expect(this.db.prepare('SELECT * FROM vtab(?)').raw().all(0)).to.deep.equal([tests[0]]);
|
|
expect(() => this.db.prepare('SELECT * FROM vtab(?)').all(1)).to.throw(TypeError);
|
|
expect(() => this.db.prepare('SELECT * FROM vtab(?)').all(2)).to.throw(TypeError);
|
|
expect(() => this.db.prepare('SELECT * FROM vtab(?)').all(3)).to.throw(TypeError);
|
|
expect(() => this.db.prepare('SELECT * FROM vtab(?)').all(4)).to.throw(TypeError);
|
|
expect(() => this.db.prepare('SELECT * FROM vtab(?)').all(5)).to.throw(TypeError);
|
|
expect(() => this.db.prepare('SELECT * FROM vtab(?)').all(6)).to.throw(TypeError);
|
|
});
|
|
it('should throw an exception if an invalid object is yielded', function () {
|
|
const tests = [
|
|
{ a: 1, b: 2, c: 3, d: 4, e: 5 },
|
|
{ a: 1, b: 2, c: 3, d: 4, e: 5, f: 6 },
|
|
{ a: 1, b: 2, c: 3, d: 4 },
|
|
{},
|
|
{ a: 1, b: 2, c: 3, d: 4, e: new Number(5) },
|
|
{ a: 1, b: 2, c: 3, d: 4, e: [5] },
|
|
{ a: 1, b: 2, c: 3, d: 4, e: new Date() },
|
|
{ a: 1, b: 2, c: 3, d: 4, f: 5 },
|
|
];
|
|
this.db.table('vtab', {
|
|
columns: ['a', 'b', 'c', 'd', 'e'],
|
|
*rows(n) {
|
|
yield tests[n];
|
|
},
|
|
});
|
|
expect(this.db.prepare('SELECT * FROM vtab(?)').all(0)).to.deep.equal([tests[0]]);
|
|
expect(() => this.db.prepare('SELECT * FROM vtab(?)').all(1)).to.throw(TypeError);
|
|
expect(() => this.db.prepare('SELECT * FROM vtab(?)').all(2)).to.throw(TypeError);
|
|
expect(() => this.db.prepare('SELECT * FROM vtab(?)').all(3)).to.throw(TypeError);
|
|
expect(() => this.db.prepare('SELECT * FROM vtab(?)').all(4)).to.throw(TypeError);
|
|
expect(() => this.db.prepare('SELECT * FROM vtab(?)').all(5)).to.throw(TypeError);
|
|
expect(() => this.db.prepare('SELECT * FROM vtab(?)').all(6)).to.throw(TypeError);
|
|
expect(() => this.db.prepare('SELECT * FROM vtab(?)').all(7)).to.throw(TypeError);
|
|
});
|
|
it('should automatically assign rowids without affecting yielded objects', function () {
|
|
let rows = [{ x: 5 }, { x: 10 }];
|
|
this.db.table('a', {
|
|
columns: ['x'],
|
|
*rows() { yield* rows; },
|
|
});
|
|
expect(this.db.prepare('SELECT rowid, * FROM a').all())
|
|
.to.deep.equal([{ rowid: 1, x: 5 }, { rowid: 2, x: 10 }]);
|
|
expect(rows).to.deep.equal([{ x: 5 }, { x: 10 }]);
|
|
|
|
rows = [{ rowid: 5 }, { rowid: 10 }];
|
|
this.db.table('b', {
|
|
columns: ['rowid'],
|
|
*rows() { yield* rows; },
|
|
});
|
|
expect(this.db.prepare('SELECT oid AS oid, * FROM b').all())
|
|
.to.deep.equal([{ oid: 1, rowid: 5 }, { oid: 2, rowid: 10 }]);
|
|
expect(rows).to.deep.equal([{ rowid: 5 }, { rowid: 10 }]);
|
|
});
|
|
it('should be driven by stmt.iterate() one row at a time', function () {
|
|
let state = 0;
|
|
this.db.table('vtab', {
|
|
columns: ['x'],
|
|
*rows() {
|
|
state += 1;
|
|
yield ['foo'];
|
|
state += 1;
|
|
yield ['bar'];
|
|
state += 1;
|
|
yield ['baz'];
|
|
state += 1;
|
|
yield ['qux'];
|
|
state += 1;
|
|
},
|
|
});
|
|
const values = [];
|
|
for (const value of this.db.prepare('SELECT * FROM vtab').pluck().iterate()) {
|
|
values.push(value);
|
|
if (value === 'baz') break;
|
|
}
|
|
expect(values).to.deep.equal(['foo', 'bar', 'baz']);
|
|
expect(state).to.equal(3);
|
|
});
|
|
it('should throw an exception if preparing a statement that uses an unsupported operator on a parameter', function () {
|
|
this.db.table('vtab', {
|
|
columns: ['a', 'b'],
|
|
parameters: ['x', 'y', 'z'],
|
|
*rows(x, y, z) {
|
|
yield [x, y + z];
|
|
yield [x * 2, (y + z) * 3];
|
|
},
|
|
});
|
|
expect(this.db.prepare('SELECT * FROM vtab(?, ?, ?)').all(2, 3, 4))
|
|
.to.deep.equal([{ a: 2, b: 7 }, { a: 4, b: 21 }]);
|
|
expect(this.db.prepare('SELECT * FROM vtab WHERE x = ? AND y = ? AND z = ?').all(2, 3, 4))
|
|
.to.deep.equal([{ a: 2, b: 7 }, { a: 4, b: 21 }]);
|
|
expect(() => this.db.prepare('SELECT * FROM vtab WHERE x = ? AND y = ? AND z > ?'))
|
|
.to.throw(Database.SqliteError);
|
|
expect(() => this.db.prepare('SELECT * FROM vtab WHERE x = ? AND y < ? AND z = ?'))
|
|
.to.throw(Database.SqliteError);
|
|
expect(() => this.db.prepare('SELECT * FROM vtab WHERE x IS ? AND y = ? AND z = ?'))
|
|
.to.throw(Database.SqliteError);
|
|
});
|
|
it('should properly escape column and parameter names', function () {
|
|
this.db.table('vtab', {
|
|
columns: ['foo);'],
|
|
parameters: ['x"); SELECT "y', 'y'],
|
|
*rows(x, y) {
|
|
yield [x];
|
|
yield [y];
|
|
yield [x + y];
|
|
},
|
|
});
|
|
expect(this.db.prepare('SELECT "foo);" FROM vtab WHERE "x""); SELECT ""y" = ? AND y = ?').all(5, 10))
|
|
.to.deep.equal([{ 'foo);': 5 }, { 'foo);': 10 }, { 'foo);': 15 }]);
|
|
});
|
|
it('should not allow CREATE VIRTUAL TABLE statements by default', function () {
|
|
this.db.table('mod', {
|
|
columns: ['x'],
|
|
*rows() {},
|
|
});
|
|
expect(() => this.db.exec('CREATE VIRTUAL TABLE a USING mod')).to.throw(Database.SqliteError);
|
|
expect(() => this.db.exec('CREATE VIRTUAL TABLE b USING mod()')).to.throw(Database.SqliteError);
|
|
expect(() => this.db.exec('CREATE VIRTUAL TABLE c USING mod(foo)')).to.throw(Database.SqliteError);
|
|
});
|
|
it('should support CREATE VIRTUAL TABLE statements by accepting a factory function', function () {
|
|
let table = '';
|
|
this.db.table('mod', function (...args) {
|
|
expect(this).to.deep.equal({ module: 'mod', database: 'main', table });
|
|
return {
|
|
columns: ['x'],
|
|
*rows() { yield* args.map(x => [x]); },
|
|
};
|
|
});
|
|
expect(() => this.db.prepare('SELECT * FROM mod')).to.throw(Database.SqliteError);
|
|
table = 'foo';
|
|
this.db.exec(`CREATE VIRTUAL TABLE ${table} USING mod(hello world, how are you?)`);
|
|
table = 'bar';
|
|
this.db.exec(`CREATE VIRTUAL TABLE ${table} USING mod(1, 2, 3)`);
|
|
expect(this.db.prepare('SELECT x FROM foo').pluck().all()).to.deep.equal(['hello world', 'how are you?']);
|
|
expect(this.db.prepare('SELECT x FROM bar').pluck().all()).to.deep.equal(['1', '2', '3']);
|
|
expect(() => this.db.prepare('SELECT * FROM mod')).to.throw(Database.SqliteError);
|
|
});
|
|
it('should correctly handle omitted arguments in any order', function () {
|
|
this.db.table('vtab', {
|
|
columns: ['value'],
|
|
parameters: ['x', 'y', 'z'],
|
|
*rows(x = 100, y = 10, z = 1) {
|
|
expect(arguments.length).to.equal(3);
|
|
yield [x + y + z];
|
|
},
|
|
});
|
|
expect(this.db.prepare('SELECT * FROM vtab(?, ?, ?)').pluck().get(2.2, 3.3, 4.4)).to.equal(9.9);
|
|
expect(this.db.prepare('SELECT * FROM vtab(?, ?)').pluck().get(2.2, 3.3)).to.equal(6.5);
|
|
expect(this.db.prepare('SELECT * FROM vtab(?)').pluck().get(2.2)).to.equal(13.2);
|
|
expect(this.db.prepare('SELECT * FROM vtab').pluck().get()).to.equal(111);
|
|
expect(this.db.prepare('SELECT * FROM vtab WHERE x = ? AND y = ? AND z = ?').pluck().get(2.2, 3.3, 4.4)).to.equal(9.9);
|
|
expect(this.db.prepare('SELECT * FROM vtab WHERE x = ? AND y = ?').pluck().get(2.2, 3.3)).to.equal(6.5);
|
|
expect(this.db.prepare('SELECT * FROM vtab WHERE x = ? AND z = ?').pluck().get(2.2, 3.3)).to.equal(15.5);
|
|
expect(this.db.prepare('SELECT * FROM vtab WHERE y = ? AND z = ?').pluck().get(2.2, 3.3)).to.equal(105.5);
|
|
expect(this.db.prepare('SELECT * FROM vtab WHERE x = ?').pluck().get(2.2)).to.equal(13.2);
|
|
expect(this.db.prepare('SELECT * FROM vtab WHERE y = ?').pluck().get(2.2)).to.equal(103.2);
|
|
expect(this.db.prepare('SELECT * FROM vtab WHERE z = ?').pluck().get(2.2)).to.equal(112.2);
|
|
});
|
|
it('should not call the generator function if any arguments are NULL', function () {
|
|
let calls = 0;
|
|
this.db.table('vtab', {
|
|
columns: ['val'],
|
|
parameters: ['x', 'y', 'z'],
|
|
*rows(x = 0, y = 0, z = 0) {
|
|
calls += 1;
|
|
yield [x + y + z];
|
|
},
|
|
});
|
|
expect(this.db.prepare('SELECT val FROM vtab(?, ?, ?)').pluck().all(1, 10, 100)).to.deep.equal([111]);
|
|
expect(this.db.prepare('SELECT val FROM vtab(?, ?)').pluck().all(1, 10)).to.deep.equal([11]);
|
|
expect(this.db.prepare('SELECT val FROM vtab(?, ?, ?)').pluck().all(1, 10, null)).to.deep.equal([]);
|
|
expect(this.db.prepare('SELECT val FROM vtab(?, ?, ?)').pluck().all(1, null, 100)).to.deep.equal([]);
|
|
expect(this.db.prepare('SELECT val FROM vtab(?, ?, ?)').pluck().all(null, 10, 100)).to.deep.equal([]);
|
|
expect(this.db.prepare('SELECT val FROM vtab(?, ?)').pluck().all(1, null)).to.deep.equal([]);
|
|
expect(calls).to.equal(2);
|
|
});
|
|
it('should close a statement iterator that caused a virtual table to throw', function () {
|
|
this.db.prepare('CREATE TABLE iterable (x INTEGER)').run();
|
|
this.db.prepare('INSERT INTO iterable WITH RECURSIVE temp(x) AS (SELECT 1 UNION ALL SELECT x * 2 FROM temp LIMIT 10) SELECT * FROM temp').run();
|
|
|
|
let i = 0;
|
|
const err = new Error('foo');
|
|
this.db.table('vtab', {
|
|
columns: ['value'],
|
|
parameters: ['x'],
|
|
*rows(x) {
|
|
if (++i >= 5) throw err;
|
|
yield [x];
|
|
},
|
|
});
|
|
const iterator = this.db.prepare('SELECT value FROM vtab JOIN iterable USING (x)').pluck().iterate();
|
|
|
|
let total = 0;
|
|
expect(() => {
|
|
for (const value of iterator) {
|
|
total += value;
|
|
expect(() => this.db.exec('SELECT value FROM vtab JOIN iterable USING (x) LIMIT 4')).to.throw(TypeError);
|
|
}
|
|
}).to.throw(err);
|
|
|
|
expect(total).to.equal(1 + 2 + 4 + 8);
|
|
expect(iterator.next()).to.deep.equal({ value: undefined, done: true });
|
|
expect(total).to.equal(1 + 2 + 4 + 8);
|
|
|
|
i = 0;
|
|
this.db.exec('SELECT value FROM vtab JOIN iterable USING (x) LIMIT 4');
|
|
expect(i).to.equal(4);
|
|
});
|
|
it('should not be able to affect bound buffers mid-query', function () {
|
|
const input = Buffer.alloc(1024 * 8).fill(0xbb);
|
|
let called = false;
|
|
this.db.table('vtab', {
|
|
columns: ['x'],
|
|
*rows(arg) {
|
|
called = true;
|
|
input[0] = 2;
|
|
arg[0] = 2;
|
|
yield [123];
|
|
},
|
|
});
|
|
const [output, arg, num] = this.db.prepare('SELECT :input, "$1", x FROM vtab(:input)').raw().get({ input });
|
|
expect(called).to.be.true;
|
|
expect(output.equals(Buffer.alloc(1024 * 8).fill(0xbb))).to.be.true;
|
|
expect(arg.equals(Buffer.alloc(1024 * 8).fill(0xbb))).to.be.true;
|
|
expect(num).to.equal(123);
|
|
});
|
|
describe('should propagate exceptions', function () {
|
|
const exceptions = [new TypeError('foobar'), new Error('baz'), { yup: 'ok' }, 'foobarbazqux', '', null, 123.4];
|
|
const expectError = (exception, fn) => {
|
|
try { fn(); } catch (ex) {
|
|
expect(ex).to.equal(exception);
|
|
return;
|
|
}
|
|
throw new TypeError('Expected table to throw an exception');
|
|
};
|
|
|
|
specify('thrown in the factory function', function () {
|
|
exceptions.forEach((exception, index) => {
|
|
const calls = [];
|
|
this.db.table(`mod${index}`, () => {
|
|
calls.push('a');
|
|
throw exception;
|
|
calls.push('b');
|
|
return {
|
|
columns: ['x'],
|
|
*rows() {
|
|
calls.push('c');
|
|
yield [42];
|
|
calls.push('d');
|
|
},
|
|
};
|
|
});
|
|
expect(calls.splice(0)).to.deep.equal([]);
|
|
expectError(exception, () => this.db.exec(`CREATE VIRTUAL TABLE vtab${index} USING mod${index}()`));
|
|
expect(calls.splice(0)).to.deep.equal(['a']);
|
|
expect(() => this.db.prepare(`SELECT * FROM vtab${index}`)).to.throw(Database.SqliteError);
|
|
expect(calls.splice(0)).to.deep.equal([]);
|
|
});
|
|
});
|
|
specify('thrown in the rows() function', function () {
|
|
exceptions.forEach((exception, index) => {
|
|
const calls = [];
|
|
this.db.table(`mod${index}`, () => {
|
|
calls.push('a');
|
|
return {
|
|
columns: ['x'],
|
|
*rows() {
|
|
calls.push('b');
|
|
yield [42];
|
|
calls.push('c');
|
|
throw exception;
|
|
calls.push('d');
|
|
},
|
|
};
|
|
});
|
|
expect(calls.splice(0)).to.deep.equal([]);
|
|
this.db.exec(`CREATE VIRTUAL TABLE vtab${index} USING mod${index}()`);
|
|
expect(calls.splice(0)).to.deep.equal(['a']);
|
|
expect(this.db.prepare(`SELECT * FROM vtab${index}`).pluck().get()).to.equal(42);
|
|
expect(calls.splice(0)).to.deep.equal(['b']);
|
|
expectError(exception, () => this.db.prepare(`SELECT * FROM vtab${index}`).pluck().all());
|
|
expect(calls.splice(0)).to.deep.equal(['b', 'c']);
|
|
});
|
|
});
|
|
specify('thrown due to yielding an invalid value', function () {
|
|
const calls = [];
|
|
this.db.table('mod', () => {
|
|
calls.push('a');
|
|
return {
|
|
columns: ['x'],
|
|
*rows() {
|
|
calls.push('b');
|
|
yield [42];
|
|
calls.push('c');
|
|
yield [new Number(42)];
|
|
calls.push('d');
|
|
},
|
|
};
|
|
});
|
|
expect(calls.splice(0)).to.deep.equal([]);
|
|
this.db.exec('CREATE VIRTUAL TABLE vtab USING mod()');
|
|
expect(calls.splice(0)).to.deep.equal(['a']);
|
|
expect(this.db.prepare('SELECT * FROM vtab').pluck().get()).to.equal(42);
|
|
expect(calls.splice(0)).to.deep.equal(['b']);
|
|
expect(() => this.db.prepare('SELECT * FROM vtab').pluck().all()).to.throw(TypeError);
|
|
expect(calls.splice(0)).to.deep.equal(['b', 'c']);
|
|
});
|
|
});
|
|
describe('should not affect external environment', function () {
|
|
specify('busy state', function () {
|
|
this.db.table('vtab', {
|
|
columns: ['x'],
|
|
*rows(arg) {
|
|
expect(() => this.db.exec('SELECT 555')).to.throw(TypeError);
|
|
yield [arg * 2];
|
|
},
|
|
});
|
|
let ranOnce = false;
|
|
for (const x of this.db.prepare('SELECT * FROM vtab(555)').pluck().iterate()) {
|
|
ranOnce = true;
|
|
expect(x).to.equal(1110);
|
|
expect(() => this.db.exec('SELECT 555')).to.throw(TypeError);
|
|
}
|
|
expect(ranOnce).to.be.true;
|
|
this.db.exec('SELECT 555');
|
|
});
|
|
specify('was_js_error state', function () {
|
|
this.db.prepare('CREATE TABLE data (value INTEGER)').run();
|
|
const stmt = this.db.prepare('SELECT value FROM data');
|
|
this.db.prepare('DROP TABLE data').run();
|
|
|
|
const err = new Error('foo');
|
|
this.db.table('vtab', {
|
|
columns: ['x'],
|
|
*rows() { throw err; },
|
|
});
|
|
|
|
expect(() => this.db.prepare('SELECT * FROM vtab').get()).to.throw(err);
|
|
try { stmt.get(); } catch (ex) {
|
|
expect(ex).to.be.an.instanceof(Error);
|
|
expect(ex).to.not.equal(err);
|
|
expect(ex.message).to.not.equal(err.message);
|
|
expect(ex).to.be.an.instanceof(Database.SqliteError);
|
|
return;
|
|
}
|
|
throw new TypeError('Expected the statement to throw an exception');
|
|
});
|
|
});
|
|
it('should correctly handle limit and offset clause', function () {
|
|
let lastValue;
|
|
this.db.table('vtab', {
|
|
columns: ['x'],
|
|
*rows() {
|
|
lastValue = 1;
|
|
yield { x: lastValue };
|
|
lastValue = 2;
|
|
yield { x: lastValue };
|
|
lastValue = 3;
|
|
yield { x: lastValue };
|
|
lastValue = null;
|
|
},
|
|
});
|
|
expect(this.db.prepare('SELECT * FROM vtab LIMIT 1').all())
|
|
.to.deep.equal([{ x: 1 }]);
|
|
expect(lastValue).to.equal(1);
|
|
expect(this.db.prepare('SELECT * FROM vtab LIMIT 1 OFFSET 2').all())
|
|
.to.deep.equal([{ x: 3 }]);
|
|
expect(lastValue).to.equal(3);
|
|
expect(this.db.prepare('SELECT * FROM vtab LIMIT 100 OFFSET 1').all())
|
|
.to.deep.equal([{ x: 2 }, { x: 3 }]);
|
|
expect(lastValue).to.be.null;
|
|
});
|
|
});
|