130 lines
5.3 KiB
JavaScript
130 lines
5.3 KiB
JavaScript
'use strict';
|
|
const Database = require('../.');
|
|
|
|
describe('Statement#all()', function () {
|
|
beforeEach(function () {
|
|
this.db = new Database(util.next());
|
|
this.db.prepare('CREATE TABLE entries (a TEXT, b INTEGER, c REAL, d BLOB, e TEXT)').run();
|
|
this.db.prepare("INSERT INTO entries WITH RECURSIVE temp(a, b, c, d, e) AS (SELECT 'foo', 1, 3.14, x'dddddddd', NULL UNION ALL SELECT a, b + 1, c, d, e FROM temp LIMIT 10) SELECT * FROM temp").run();
|
|
});
|
|
afterEach(function () {
|
|
this.db.close();
|
|
});
|
|
|
|
it('should throw an exception when used on a statement that returns no data', function () {
|
|
let stmt = this.db.prepare("INSERT INTO entries VALUES ('foo', 1, 3.14, x'dddddddd', NULL)");
|
|
expect(stmt.reader).to.be.false;
|
|
expect(() => stmt.all()).to.throw(TypeError);
|
|
|
|
stmt = this.db.prepare("CREATE TABLE IF NOT EXISTS entries (a TEXT, b INTEGER, c REAL, d BLOB, e TEXT)");
|
|
expect(stmt.reader).to.be.false;
|
|
expect(() => stmt.all()).to.throw(TypeError);
|
|
|
|
stmt = this.db.prepare("BEGIN TRANSACTION");
|
|
expect(stmt.reader).to.be.false;
|
|
expect(() => stmt.all()).to.throw(TypeError);
|
|
});
|
|
it('should return an array of every matching row', function () {
|
|
const row = { a: 'foo', b: 1, c: 3.14, d: Buffer.alloc(4).fill(0xdd), e: null };
|
|
|
|
let stmt = this.db.prepare("SELECT * FROM entries ORDER BY rowid");
|
|
expect(stmt.reader).to.be.true;
|
|
matchesFrom(stmt.all(), 1);
|
|
|
|
stmt = this.db.prepare("SELECT * FROM entries WHERE b > 5 ORDER BY rowid");
|
|
matchesFrom(stmt.all(), 6);
|
|
|
|
function matchesFrom(rows, i) {
|
|
let index = 0;
|
|
for (; i <= 10; ++i, ++index) {
|
|
row.b = i;
|
|
expect(rows[index]).to.deep.equal(row);
|
|
}
|
|
expect(index).to.equal(rows.length);
|
|
}
|
|
});
|
|
it('should work with RETURNING clause', function () {
|
|
let stmt = this.db.prepare("INSERT INTO entries (a, b) VALUES ('bar', 888), ('baz', 999) RETURNING *");
|
|
expect(stmt.reader).to.be.true;
|
|
expect(stmt.all()).to.deep.equal([
|
|
{ a: 'bar', b: 888, c: null, d: null, e: null },
|
|
{ a: 'baz', b: 999, c: null, d: null, e: null },
|
|
]);
|
|
|
|
stmt = this.db.prepare("SELECT * FROM entries WHERE b > 800 ORDER BY rowid");
|
|
expect(stmt.all()).to.deep.equal([
|
|
{ a: 'bar', b: 888, c: null, d: null, e: null },
|
|
{ a: 'baz', b: 999, c: null, d: null, e: null },
|
|
]);
|
|
});
|
|
it('should obey the current pluck and expand settings', function () {
|
|
const stmt = this.db.prepare("SELECT *, 2 + 3.5 AS c FROM entries ORDER BY rowid");
|
|
const expanded = new Array(10).fill().map((_, i) => ({
|
|
entries: { a: 'foo', b: i + 1, c: 3.14, d: Buffer.alloc(4).fill(0xdd), e: null },
|
|
$: { c: 5.5 },
|
|
}));
|
|
const rows = expanded.map(x => Object.assign({}, x.entries, x.$));
|
|
const plucked = expanded.map(x => x.entries.a);
|
|
const raw = expanded.map(x => Object.values(x.entries).concat(x.$.c))
|
|
expect(stmt.all()).to.deep.equal(rows);
|
|
expect(stmt.pluck(true).all()).to.deep.equal(plucked);
|
|
expect(stmt.all()).to.deep.equal(plucked);
|
|
expect(stmt.pluck(false).all()).to.deep.equal(rows);
|
|
expect(stmt.all()).to.deep.equal(rows);
|
|
expect(stmt.pluck().all()).to.deep.equal(plucked);
|
|
expect(stmt.all()).to.deep.equal(plucked);
|
|
expect(stmt.expand().all()).to.deep.equal(expanded);
|
|
expect(stmt.all()).to.deep.equal(expanded);
|
|
expect(stmt.expand(false).all()).to.deep.equal(rows);
|
|
expect(stmt.all()).to.deep.equal(rows);
|
|
expect(stmt.expand(true).all()).to.deep.equal(expanded);
|
|
expect(stmt.all()).to.deep.equal(expanded);
|
|
expect(stmt.pluck(true).all()).to.deep.equal(plucked);
|
|
expect(stmt.all()).to.deep.equal(plucked);
|
|
expect(stmt.raw().all()).to.deep.equal(raw);
|
|
expect(stmt.all()).to.deep.equal(raw);
|
|
expect(stmt.raw(false).all()).to.deep.equal(rows);
|
|
expect(stmt.all()).to.deep.equal(rows);
|
|
expect(stmt.raw(true).all()).to.deep.equal(raw);
|
|
expect(stmt.all()).to.deep.equal(raw);
|
|
expect(stmt.expand(true).all()).to.deep.equal(expanded);
|
|
expect(stmt.all()).to.deep.equal(expanded);
|
|
});
|
|
it('should return an empty array when no rows were found', function () {
|
|
const stmt = this.db.prepare("SELECT * FROM entries WHERE b == 999");
|
|
expect(stmt.all()).to.deep.equal([]);
|
|
expect(stmt.pluck().all()).to.deep.equal([]);
|
|
});
|
|
it('should accept bind parameters', function () {
|
|
const rows = [{ a: 'foo', b: 1, c: 3.14, d: Buffer.alloc(4).fill(0xdd), e: null }];
|
|
const SQL1 = 'SELECT * FROM entries WHERE a=? AND b=? AND c=? AND d=? AND e IS ?';
|
|
const SQL2 = 'SELECT * FROM entries WHERE a=@a AND b=@b AND c=@c AND d=@d AND e IS @e';
|
|
let result = this.db.prepare(SQL1).all('foo', 1, 3.14, Buffer.alloc(4).fill(0xdd), null);
|
|
expect(result).to.deep.equal(rows);
|
|
|
|
result = this.db.prepare(SQL1).all(['foo', 1, 3.14, Buffer.alloc(4).fill(0xdd), null]);
|
|
expect(result).to.deep.equal(rows);
|
|
|
|
result = this.db.prepare(SQL1).all(['foo', 1], [3.14], Buffer.alloc(4).fill(0xdd), [,]);
|
|
expect(result).to.deep.equal(rows);
|
|
|
|
result = this.db.prepare(SQL2).all({ a: 'foo', b: 1, c: 3.14, d: Buffer.alloc(4).fill(0xdd), e: undefined });
|
|
expect(result).to.deep.equal(rows);
|
|
|
|
result = this.db.prepare(SQL2).all({ a: 'foo', b: 1, c: 3.14, d: Buffer.alloc(4).fill(0xaa), e: undefined });
|
|
expect(result).to.deep.equal([]);
|
|
|
|
expect(() =>
|
|
this.db.prepare(SQL2).all({ a: 'foo', b: 1, c: 3.14, d: Buffer.alloc(4).fill(0xdd) })
|
|
).to.throw(RangeError);
|
|
|
|
expect(() =>
|
|
this.db.prepare(SQL1).all()
|
|
).to.throw(RangeError);
|
|
|
|
expect(() =>
|
|
this.db.prepare(SQL2).all({})
|
|
).to.throw(RangeError);
|
|
});
|
|
});
|