mirror of
https://github.com/knex/knex.git
synced 2025-07-11 11:01:54 +00:00
1981 lines
62 KiB
JavaScript
1981 lines
62 KiB
JavaScript
'use strict';
|
|
|
|
const { expect } = require('chai');
|
|
|
|
const assert = require('assert');
|
|
const Runner = require('../../../../lib/execution/runner');
|
|
|
|
const { TEST_TIMESTAMP } = require('../../../util/constants');
|
|
const {
|
|
isMysql,
|
|
isPostgreSQL,
|
|
isPgNative,
|
|
isMssql,
|
|
isSQLite,
|
|
isOracle,
|
|
isPgBased,
|
|
isCockroachDB,
|
|
} = require('../../../util/db-helpers');
|
|
const {
|
|
createUsers,
|
|
createAccounts,
|
|
createCompositeKeyTable,
|
|
createTestTableTwo,
|
|
dropTables,
|
|
createDefaultTable,
|
|
} = require('../../../util/tableCreatorHelper');
|
|
const { insertAccounts } = require('../../../util/dataInsertHelper');
|
|
const { assertNumberArrayStrict } = require('../../../util/assertHelper');
|
|
const {
|
|
getAllDbs,
|
|
getKnexForDb,
|
|
} = require('../../util/knex-instance-provider');
|
|
const logger = require('../../../integration/logger');
|
|
|
|
describe('Selects', function () {
|
|
getAllDbs().forEach((db) => {
|
|
describe(db, () => {
|
|
let knex;
|
|
|
|
before(async () => {
|
|
knex = logger(getKnexForDb(db));
|
|
|
|
await dropTables(knex);
|
|
await createUsers(knex);
|
|
await createAccounts(knex);
|
|
await createCompositeKeyTable(knex);
|
|
await createTestTableTwo(knex);
|
|
await createDefaultTable(knex);
|
|
await createDefaultTable(knex, true);
|
|
});
|
|
|
|
beforeEach(async () => {
|
|
await knex('accounts').truncate();
|
|
await insertAccounts(knex);
|
|
});
|
|
|
|
after(async () => {
|
|
return knex.destroy();
|
|
// ToDo we can do this after other tests are fixed
|
|
// await dropTables(knex);
|
|
});
|
|
|
|
it('runs with no conditions', function () {
|
|
return knex('accounts').select();
|
|
});
|
|
|
|
it('returns an array of a single column with `pluck`', async () => {
|
|
return knex
|
|
.pluck('id')
|
|
.orderBy('id')
|
|
.from('accounts')
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'mysql',
|
|
'select `id` from `accounts` order by `id` asc',
|
|
[],
|
|
[1, 2, 3, 4, 5, 6]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'select "id" from "accounts" order by "id" asc',
|
|
[],
|
|
['1', '2', '3', '4', '5', '6']
|
|
);
|
|
tester(
|
|
'pgnative',
|
|
'select "id" from "accounts" order by "id" asc',
|
|
[],
|
|
['1', '2', '3', '4', '5', '6']
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'select "id" from "accounts" order by "id" asc',
|
|
[],
|
|
['1', '2', '3', '4', '5', '6']
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'select `id` from `accounts` order by `id` asc',
|
|
[],
|
|
[1, 2, 3, 4, 5, 6]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'select "id" from "accounts" order by "id" asc',
|
|
[],
|
|
[1, 2, 3, 4, 5, 6]
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'select [id] from [accounts] order by [id] asc',
|
|
[],
|
|
['1', '2', '3', '4', '5', '6']
|
|
);
|
|
});
|
|
});
|
|
|
|
it('can pluck a qualified column name, #1619', function () {
|
|
return knex
|
|
.pluck('accounts.id')
|
|
.from('accounts')
|
|
.orderBy('accounts.id')
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'mysql',
|
|
'select `accounts`.`id` from `accounts` order by `accounts`.`id` asc',
|
|
[],
|
|
[1, 2, 3, 4, 5, 6]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'select "accounts"."id" from "accounts" order by "accounts"."id" asc',
|
|
[],
|
|
['1', '2', '3', '4', '5', '6']
|
|
);
|
|
tester(
|
|
'pgnative',
|
|
'select "accounts"."id" from "accounts" order by "accounts"."id" asc',
|
|
[],
|
|
['1', '2', '3', '4', '5', '6']
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'select "accounts"."id" from "accounts" order by "accounts"."id" asc',
|
|
[],
|
|
['1', '2', '3', '4', '5', '6']
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'select `accounts`.`id` from `accounts` order by `accounts`.`id` asc',
|
|
[],
|
|
[1, 2, 3, 4, 5, 6]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'select "accounts"."id" from "accounts" order by "accounts"."id" asc',
|
|
[],
|
|
[1, 2, 3, 4, 5, 6]
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'select [accounts].[id] from [accounts] order by [accounts].[id] asc',
|
|
[],
|
|
['1', '2', '3', '4', '5', '6']
|
|
);
|
|
});
|
|
});
|
|
|
|
it('starts selecting at offset', function () {
|
|
return knex
|
|
.pluck('id')
|
|
.orderBy('id')
|
|
.from('accounts')
|
|
.offset(2)
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'mysql',
|
|
'select `id` from `accounts` order by `id` asc limit 18446744073709551615 offset ?',
|
|
[2],
|
|
[3, 4, 5, 6]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'select "id" from "accounts" order by "id" asc offset ?',
|
|
[2],
|
|
['3', '4', '5', '6']
|
|
);
|
|
tester(
|
|
'pgnative',
|
|
'select "id" from "accounts" order by "id" asc offset ?',
|
|
[2],
|
|
['3', '4', '5', '6']
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'select "id" from "accounts" order by "id" asc offset ?',
|
|
[2],
|
|
['3', '4', '5', '6']
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'select `id` from `accounts` order by `id` asc limit ? offset ?',
|
|
[-1, 2],
|
|
[3, 4, 5, 6]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'select * from (select row_.*, ROWNUM rownum_ from (select "id" from "accounts" order by "id" asc) row_ where rownum <= ?) where rownum_ > ?',
|
|
[10000000000002, 2],
|
|
[3, 4, 5, 6]
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'select [id] from [accounts] order by [id] asc offset ? rows',
|
|
[2],
|
|
['3', '4', '5', '6']
|
|
);
|
|
});
|
|
});
|
|
|
|
it('#4335 - should throw an error when negative offset provided', function (ok) {
|
|
try {
|
|
knex.from('accounts').limit(20).offset(-20);
|
|
throw new Error('no error was thrown for negative offset!');
|
|
} catch (error) {
|
|
if (
|
|
error.message ===
|
|
'A non-negative integer must be provided to offset.'
|
|
) {
|
|
ok();
|
|
} else {
|
|
throw error;
|
|
}
|
|
}
|
|
});
|
|
|
|
it('#4199 - adheres to hint comments', async function () {
|
|
const expectedErrors = {
|
|
mysql: {
|
|
code: 'ER_QUERY_TIMEOUT',
|
|
errno: 3024,
|
|
sqlMessage:
|
|
'Query execution was interrupted, maximum statement execution time exceeded',
|
|
},
|
|
mysql2: {
|
|
errno: 3024,
|
|
sqlMessage:
|
|
'Query execution was interrupted, maximum statement execution time exceeded',
|
|
},
|
|
};
|
|
if (!expectedErrors[knex.client.driverName]) {
|
|
return this.skip();
|
|
}
|
|
const baseQuery = knex('accounts')
|
|
.select('id', knex.raw('sleep(0.1)'))
|
|
.limit(2);
|
|
await expect(
|
|
baseQuery.clone()
|
|
).to.eventually.be.fulfilled.and.to.have.lengthOf(2);
|
|
await expect(
|
|
baseQuery.clone().hintComment('max_execution_time(10)')
|
|
).to.eventually.be.rejected.and.to.deep.include(
|
|
expectedErrors[knex.client.driverName]
|
|
);
|
|
});
|
|
|
|
it('#4199 - ignores invalid hint comments', async function () {
|
|
return knex
|
|
.select('id')
|
|
.orderBy('id')
|
|
.from('accounts')
|
|
.hintComment('invalid()')
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'mysql',
|
|
'select /*+ invalid() */ `id` from `accounts` order by `id` asc',
|
|
[],
|
|
[{ id: 1 }, { id: 2 }, { id: 3 }, { id: 4 }, { id: 5 }, { id: 6 }]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'select /*+ invalid() */ "id" from "accounts" order by "id" asc',
|
|
[],
|
|
[
|
|
{ id: '1' },
|
|
{ id: '2' },
|
|
{ id: '3' },
|
|
{ id: '4' },
|
|
{ id: '5' },
|
|
{ id: '6' },
|
|
]
|
|
);
|
|
tester(
|
|
'pgnative',
|
|
'select /*+ invalid() */ "id" from "accounts" order by "id" asc',
|
|
[],
|
|
[
|
|
{ id: '1' },
|
|
{ id: '2' },
|
|
{ id: '3' },
|
|
{ id: '4' },
|
|
{ id: '5' },
|
|
{ id: '6' },
|
|
]
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'select /*+ invalid() */ "id" from "accounts" order by "id" asc',
|
|
[],
|
|
[
|
|
{ id: '1' },
|
|
{ id: '2' },
|
|
{ id: '3' },
|
|
{ id: '4' },
|
|
{ id: '5' },
|
|
{ id: '6' },
|
|
]
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'select /*+ invalid() */ `id` from `accounts` order by `id` asc',
|
|
[],
|
|
[{ id: 1 }, { id: 2 }, { id: 3 }, { id: 4 }, { id: 5 }, { id: 6 }]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'select /*+ invalid() */ "id" from "accounts" order by "id" asc',
|
|
[],
|
|
[{ id: 1 }, { id: 2 }, { id: 3 }, { id: 4 }, { id: 5 }, { id: 6 }]
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'select /*+ invalid() */ [id] from [accounts] order by [id] asc',
|
|
[],
|
|
[
|
|
{ id: '1' },
|
|
{ id: '2' },
|
|
{ id: '3' },
|
|
{ id: '4' },
|
|
{ id: '5' },
|
|
{ id: '6' },
|
|
]
|
|
);
|
|
});
|
|
});
|
|
|
|
it('returns a single entry with first', function () {
|
|
return knex
|
|
.first('id', 'first_name')
|
|
.orderBy('id')
|
|
.from('accounts')
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'mysql',
|
|
'select `id`, `first_name` from `accounts` order by `id` asc limit ?',
|
|
[1],
|
|
{ id: 1, first_name: 'Test' }
|
|
);
|
|
tester(
|
|
'pg',
|
|
'select "id", "first_name" from "accounts" order by "id" asc limit ?',
|
|
[1],
|
|
{ id: '1', first_name: 'Test' }
|
|
);
|
|
tester(
|
|
'pgnative',
|
|
'select "id", "first_name" from "accounts" order by "id" asc limit ?',
|
|
[1],
|
|
{ id: '1', first_name: 'Test' }
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'select "id", "first_name" from "accounts" order by "id" asc limit ?',
|
|
[1],
|
|
{ id: '1', first_name: 'Test' }
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'select `id`, `first_name` from `accounts` order by `id` asc limit ?',
|
|
[1],
|
|
{ id: 1, first_name: 'Test' }
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'select * from (select "id", "first_name" from "accounts" order by "id" asc) where rownum <= ?',
|
|
[1],
|
|
{ id: 1, first_name: 'Test' }
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'select top (?) [id], [first_name] from [accounts] order by [id] asc',
|
|
[1],
|
|
{ id: '1', first_name: 'Test' }
|
|
);
|
|
});
|
|
});
|
|
|
|
it('allows you to stream', function () {
|
|
if (isPgNative(knex)) {
|
|
return this.skip();
|
|
}
|
|
let count = 0;
|
|
return knex('accounts')
|
|
.stream(function (rowStream) {
|
|
rowStream.on('data', function () {
|
|
count++;
|
|
});
|
|
})
|
|
.then(function () {
|
|
assert(count === 6, 'Six rows should have been streamed');
|
|
});
|
|
});
|
|
|
|
it('returns a stream if not passed a function', function (done) {
|
|
if (isPgNative(knex)) {
|
|
return this.skip();
|
|
}
|
|
|
|
let count = 0;
|
|
const stream = knex('accounts').stream();
|
|
stream.on('data', function () {
|
|
count++;
|
|
if (count === 6) done();
|
|
});
|
|
});
|
|
|
|
it('allows you to stream with mysql dialect options', function () {
|
|
if (!isMysql(knex)) {
|
|
return this.skip();
|
|
}
|
|
const rows = [];
|
|
return knex('accounts')
|
|
.options({
|
|
typeCast(field, next) {
|
|
let val;
|
|
if (field.type === 'VAR_STRING') {
|
|
val = field.string();
|
|
return val == null ? val : val.toUpperCase();
|
|
}
|
|
return next();
|
|
},
|
|
})
|
|
.stream(function (rowStream) {
|
|
rowStream.on('data', function (row) {
|
|
rows.push(row);
|
|
});
|
|
})
|
|
.then(function () {
|
|
expect(rows).to.have.lengthOf(6);
|
|
rows.forEach((row) => {
|
|
['first_name', 'last_name', 'email'].forEach((field) =>
|
|
expect(row[field]).to.equal(row[field].toUpperCase())
|
|
);
|
|
});
|
|
});
|
|
});
|
|
|
|
it('emits error on the stream, if not passed a function, and connecting fails', function () {
|
|
const expected = new Error();
|
|
const original = Runner.prototype.ensureConnection;
|
|
Runner.prototype.ensureConnection = function () {
|
|
return Promise.reject(expected);
|
|
};
|
|
|
|
const restore = () => {
|
|
Runner.prototype.ensureConnection = original;
|
|
};
|
|
|
|
const promise = new Promise((resolve, reject) => {
|
|
const timeout = setTimeout(() => {
|
|
reject(new Error('Timeout'));
|
|
}, 5000);
|
|
|
|
const stream = knex('accounts').stream();
|
|
stream.on('error', function (actual) {
|
|
clearTimeout(timeout);
|
|
|
|
if (actual === expected) {
|
|
resolve();
|
|
} else {
|
|
reject(new Error('Stream emitted unexpected error'));
|
|
}
|
|
});
|
|
});
|
|
|
|
promise.then(restore, restore);
|
|
return promise;
|
|
});
|
|
|
|
it('emits error on the stream, if not passed a function, and query fails', function (done) {
|
|
const stream = knex('accounts').select('invalid_field').stream();
|
|
stream.on('error', function (err) {
|
|
assert(err instanceof Error);
|
|
done();
|
|
});
|
|
});
|
|
|
|
it('emits error if not passed a function and the query has wrong bindings', function (done) {
|
|
const stream = knex('accounts')
|
|
.whereRaw('id = ? and first_name = ?', ['2'])
|
|
.stream();
|
|
stream.on('error', function (err) {
|
|
assert(err instanceof Error);
|
|
done();
|
|
});
|
|
});
|
|
|
|
it('properly escapes postgres queries on streaming', async function () {
|
|
const result = await knex('accounts').select();
|
|
|
|
let count = 0;
|
|
await knex('accounts')
|
|
.where('id', result[0].id)
|
|
.stream(function (rowStream) {
|
|
rowStream.on('data', function () {
|
|
count++;
|
|
});
|
|
});
|
|
assert(count === 1, 'One row should have been streamed');
|
|
});
|
|
|
|
it('throws errors on the asCallback if uncaught in the last block', function (ok) {
|
|
const listeners = process.listeners('uncaughtException');
|
|
|
|
process.removeAllListeners('uncaughtException');
|
|
|
|
process.on('uncaughtException', function () {
|
|
process.removeAllListeners('uncaughtException');
|
|
for (let i = 0, l = listeners.length; i < l; i++) {
|
|
process.on('uncaughtException', listeners[i]);
|
|
}
|
|
ok();
|
|
});
|
|
|
|
knex('accounts')
|
|
.select()
|
|
.asCallback(function () {
|
|
this.undefinedVar.test;
|
|
});
|
|
});
|
|
|
|
it('uses "orderBy"', function () {
|
|
return knex('accounts')
|
|
.pluck('id')
|
|
.orderBy('id', 'desc')
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'oracledb',
|
|
'select "id" from "accounts" order by "id" desc',
|
|
[],
|
|
[6, 5, 4, 3, 2, 1]
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'select [id] from [accounts] order by [id] desc',
|
|
[],
|
|
['6', '5', '4', '3', '2', '1']
|
|
);
|
|
});
|
|
});
|
|
|
|
it('order by with null', async () => {
|
|
await knex.schema
|
|
.dropTableIfExists('OrderByNullTest')
|
|
.createTable('OrderByNullTest', function (table) {
|
|
table.increments('id').primary();
|
|
table.string('null_col').nullable().defaultTo(null);
|
|
});
|
|
|
|
await knex('OrderByNullTest').insert([
|
|
{
|
|
null_col: 'test',
|
|
},
|
|
{
|
|
null_col: 'test2',
|
|
},
|
|
{
|
|
null_col: null,
|
|
},
|
|
{
|
|
null_col: null,
|
|
},
|
|
]);
|
|
|
|
await knex('OrderByNullTest')
|
|
.pluck('id')
|
|
.orderBy('null_col', 'asc', 'first')
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'mysql',
|
|
'select `id` from `OrderByNullTest` order by (`null_col` is not null) asc',
|
|
[],
|
|
[3, 4, 1, 2]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'select "id" from "OrderByNullTest" order by ("null_col" is not null) asc',
|
|
[],
|
|
[3, 4, 1, 2]
|
|
);
|
|
tester(
|
|
'pgnative',
|
|
'select "id" from "OrderByNullTest" order by ("null_col" is not null) asc',
|
|
[],
|
|
[3, 4, 1, 2]
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'select "id" from "OrderByNullTest" order by ("null_col" is not null) asc',
|
|
[],
|
|
['3', '4', '1', '2']
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'select `id` from `OrderByNullTest` order by (`null_col` is not null) asc',
|
|
[],
|
|
[3, 4, 1, 2]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'select "id" from "OrderByNullTest" order by ("null_col" is not null) asc',
|
|
[],
|
|
[3, 4, 1, 2]
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'select [id] from [OrderByNullTest] order by IIF([null_col] is null,0,1) asc',
|
|
[],
|
|
[3, 4, 1, 2]
|
|
);
|
|
});
|
|
|
|
await knex('OrderByNullTest')
|
|
.pluck('id')
|
|
.orderBy('null_col', 'asc', 'last')
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'mysql',
|
|
'select `id` from `OrderByNullTest` order by (`null_col` is null) asc',
|
|
[],
|
|
[1, 2, 3, 4]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'select "id" from "OrderByNullTest" order by ("null_col" is null) asc',
|
|
[],
|
|
[1, 2, 3, 4]
|
|
);
|
|
tester(
|
|
'pgnative',
|
|
'select "id" from "OrderByNullTest" order by ("null_col" is null) asc',
|
|
[],
|
|
[1, 2, 3, 4]
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'select "id" from "OrderByNullTest" order by ("null_col" is null) asc',
|
|
[],
|
|
['1', '2', '3', '4']
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'select `id` from `OrderByNullTest` order by (`null_col` is null) asc',
|
|
[],
|
|
[1, 2, 3, 4]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'select "id" from "OrderByNullTest" order by ("null_col" is null) asc',
|
|
[],
|
|
[1, 2, 3, 4]
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'select [id] from [OrderByNullTest] order by IIF([null_col] is null,1,0) asc',
|
|
[],
|
|
[1, 2, 3, 4]
|
|
);
|
|
});
|
|
await knex.schema.dropTable('OrderByNullTest');
|
|
});
|
|
|
|
describe('simple "where" cases', function () {
|
|
it('allows key, value', function () {
|
|
return knex('accounts')
|
|
.where('id', 1)
|
|
.select('first_name', 'last_name')
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'mysql',
|
|
'select `first_name`, `last_name` from `accounts` where `id` = ?',
|
|
[1],
|
|
[
|
|
{
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'select "first_name", "last_name" from "accounts" where "id" = ?',
|
|
[1],
|
|
[
|
|
{
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pgnative',
|
|
'select "first_name", "last_name" from "accounts" where "id" = ?',
|
|
[1],
|
|
[
|
|
{
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'select "first_name", "last_name" from "accounts" where "id" = ?',
|
|
[1],
|
|
[
|
|
{
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'select `first_name`, `last_name` from `accounts` where `id` = ?',
|
|
[1],
|
|
[
|
|
{
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'select "first_name", "last_name" from "accounts" where "id" = ?',
|
|
[1],
|
|
[
|
|
{
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'select [first_name], [last_name] from [accounts] where [id] = ?',
|
|
[1],
|
|
[
|
|
{
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
},
|
|
]
|
|
);
|
|
});
|
|
});
|
|
|
|
it('allows key, operator, value', function () {
|
|
return knex('accounts')
|
|
.where('id', 1)
|
|
.select('first_name', 'last_name')
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'mysql',
|
|
'select `first_name`, `last_name` from `accounts` where `id` = ?',
|
|
[1],
|
|
[
|
|
{
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'select "first_name", "last_name" from "accounts" where "id" = ?',
|
|
[1],
|
|
[
|
|
{
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pgnative',
|
|
'select "first_name", "last_name" from "accounts" where "id" = ?',
|
|
[1],
|
|
[
|
|
{
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'select "first_name", "last_name" from "accounts" where "id" = ?',
|
|
[1],
|
|
[
|
|
{
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'select `first_name`, `last_name` from `accounts` where `id` = ?',
|
|
[1],
|
|
[
|
|
{
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'select "first_name", "last_name" from "accounts" where "id" = ?',
|
|
[1],
|
|
[
|
|
{
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'select [first_name], [last_name] from [accounts] where [id] = ?',
|
|
[1],
|
|
[
|
|
{
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
},
|
|
]
|
|
);
|
|
});
|
|
});
|
|
|
|
it('allows selecting columns with an array', function () {
|
|
return knex('accounts')
|
|
.where('id', '>', 1)
|
|
.select(['email', 'logins'])
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'mysql',
|
|
'select `email`, `logins` from `accounts` where `id` > ?',
|
|
[1]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'select "email", "logins" from "accounts" where "id" > ?',
|
|
[1]
|
|
);
|
|
tester(
|
|
'pgnative',
|
|
'select "email", "logins" from "accounts" where "id" > ?',
|
|
[1]
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'select "email", "logins" from "accounts" where "id" > ?',
|
|
[1]
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'select `email`, `logins` from `accounts` where `id` > ?',
|
|
[1]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'select "email", "logins" from "accounts" where "id" > ?',
|
|
[1]
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'select [email], [logins] from [accounts] where [id] > ?',
|
|
[1]
|
|
);
|
|
});
|
|
});
|
|
|
|
it('allows a hash of where attrs', function () {
|
|
return knex('accounts')
|
|
.where({ id: 1 })
|
|
.select('*')
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'mysql',
|
|
'select * from `accounts` where `id` = ?',
|
|
[1],
|
|
[
|
|
{
|
|
id: 1,
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
email: 'test1@example.com',
|
|
logins: 1,
|
|
balance: 0,
|
|
about: 'Lorem ipsum Dolore labore incididunt enim.',
|
|
created_at: TEST_TIMESTAMP,
|
|
updated_at: TEST_TIMESTAMP,
|
|
phone: null,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'select * from "accounts" where "id" = ?',
|
|
[1],
|
|
[
|
|
{
|
|
id: '1',
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
email: 'test1@example.com',
|
|
logins: 1,
|
|
balance: 0,
|
|
about: 'Lorem ipsum Dolore labore incididunt enim.',
|
|
created_at: TEST_TIMESTAMP,
|
|
updated_at: TEST_TIMESTAMP,
|
|
phone: null,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pgnative',
|
|
'select * from "accounts" where "id" = ?',
|
|
[1],
|
|
[
|
|
{
|
|
id: '1',
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
email: 'test1@example.com',
|
|
logins: 1,
|
|
balance: 0,
|
|
about: 'Lorem ipsum Dolore labore incididunt enim.',
|
|
created_at: TEST_TIMESTAMP,
|
|
updated_at: TEST_TIMESTAMP,
|
|
phone: null,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'select * from "accounts" where "id" = ?',
|
|
[1],
|
|
[
|
|
{
|
|
id: '1',
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
email: 'test1@example.com',
|
|
logins: 1,
|
|
balance: 0,
|
|
about: 'Lorem ipsum Dolore labore incididunt enim.',
|
|
created_at: TEST_TIMESTAMP,
|
|
updated_at: TEST_TIMESTAMP,
|
|
phone: null,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'select * from `accounts` where `id` = ?',
|
|
[1],
|
|
[
|
|
{
|
|
id: 1,
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
email: 'test1@example.com',
|
|
logins: 1,
|
|
balance: 0,
|
|
about: 'Lorem ipsum Dolore labore incididunt enim.',
|
|
created_at: TEST_TIMESTAMP,
|
|
updated_at: TEST_TIMESTAMP,
|
|
phone: null,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'select * from "accounts" where "id" = ?',
|
|
[1],
|
|
[
|
|
{
|
|
id: 1,
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
email: 'test1@example.com',
|
|
logins: 1,
|
|
balance: 0,
|
|
about: 'Lorem ipsum Dolore labore incididunt enim.',
|
|
created_at: TEST_TIMESTAMP,
|
|
updated_at: TEST_TIMESTAMP,
|
|
phone: null,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'select * from [accounts] where [id] = ?',
|
|
[1],
|
|
[
|
|
{
|
|
id: '1',
|
|
first_name: 'Test',
|
|
last_name: 'User',
|
|
email: 'test1@example.com',
|
|
logins: 1,
|
|
balance: 0,
|
|
about: 'Lorem ipsum Dolore labore incididunt enim.',
|
|
created_at: TEST_TIMESTAMP,
|
|
updated_at: TEST_TIMESTAMP,
|
|
phone: null,
|
|
},
|
|
]
|
|
);
|
|
});
|
|
});
|
|
|
|
it('allows where id: undefined or id: null as a where null clause', function () {
|
|
return knex('accounts')
|
|
.where({ id: null })
|
|
.select('first_name', 'email')
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'mysql',
|
|
'select `first_name`, `email` from `accounts` where `id` is null',
|
|
[],
|
|
[]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'select "first_name", "email" from "accounts" where "id" is null',
|
|
[],
|
|
[]
|
|
);
|
|
tester(
|
|
'pgnative',
|
|
'select "first_name", "email" from "accounts" where "id" is null',
|
|
[],
|
|
[]
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'select "first_name", "email" from "accounts" where "id" is null',
|
|
[],
|
|
[]
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'select `first_name`, `email` from `accounts` where `id` is null',
|
|
[],
|
|
[]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'select "first_name", "email" from "accounts" where "id" is null',
|
|
[],
|
|
[]
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'select [first_name], [email] from [accounts] where [id] is null',
|
|
[],
|
|
[]
|
|
);
|
|
});
|
|
});
|
|
|
|
it('allows where id = 0', function () {
|
|
return knex('accounts')
|
|
.where({ id: 0 })
|
|
.select()
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'mysql',
|
|
'select * from `accounts` where `id` = ?',
|
|
[0],
|
|
[]
|
|
);
|
|
tester('pg', 'select * from "accounts" where "id" = ?', [0], []);
|
|
tester(
|
|
'pgnative',
|
|
'select * from "accounts" where "id" = ?',
|
|
[0],
|
|
[]
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'select * from "accounts" where "id" = ?',
|
|
[0],
|
|
[]
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'select * from `accounts` where `id` = ?',
|
|
[0],
|
|
[]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'select * from "accounts" where "id" = ?',
|
|
[0],
|
|
[]
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'select * from [accounts] where [id] = ?',
|
|
[0],
|
|
[]
|
|
);
|
|
});
|
|
});
|
|
});
|
|
|
|
it('#1276 - Dates NULL should be returned as NULL, not as new Date(null)', function () {
|
|
return knex.schema
|
|
.dropTableIfExists('DatesTest')
|
|
.createTable('DatesTest', function (table) {
|
|
table.increments('id').primary();
|
|
table.dateTime('dateTimeCol');
|
|
table.timestamp('timeStampCol').nullable().defaultTo(null); // MySQL defaults TIMESTAMP columns to current timestamp
|
|
table.date('dateCol');
|
|
table.time('timeCol');
|
|
})
|
|
.then(function () {
|
|
return knex('DatesTest').insert([
|
|
{
|
|
dateTimeCol: null,
|
|
timeStampCol: null,
|
|
dateCol: null,
|
|
timeCol: null,
|
|
},
|
|
]);
|
|
})
|
|
.then(function () {
|
|
return knex('DatesTest').select();
|
|
})
|
|
.then(function (rows) {
|
|
expect(rows[0].dateTimeCol).to.equal(null);
|
|
expect(rows[0].timeStampCol).to.equal(null);
|
|
expect(rows[0].dateCol).to.equal(null);
|
|
expect(rows[0].timeCol).to.equal(null);
|
|
});
|
|
});
|
|
|
|
it('has a "distinct" clause', function () {
|
|
return Promise.all([
|
|
knex('accounts')
|
|
.select()
|
|
.distinct('email')
|
|
.where('logins', 2)
|
|
.orderBy('email'),
|
|
knex('accounts').distinct('email').select().orderBy('email'),
|
|
]);
|
|
});
|
|
|
|
it('supports "distinct on"', async function () {
|
|
const builder = knex('accounts')
|
|
.select('email', 'logins')
|
|
.distinctOn('id')
|
|
.orderBy('id');
|
|
if (!isPgBased(knex)) {
|
|
let error;
|
|
try {
|
|
await builder;
|
|
} catch (e) {
|
|
error = e;
|
|
}
|
|
expect(error.message).to.eql(
|
|
'.distinctOn() is currently only supported on PostgreSQL'
|
|
);
|
|
return;
|
|
}
|
|
return builder.testSql(function (tester) {
|
|
tester(
|
|
'pg',
|
|
'select distinct on ("id") "email", "logins" from "accounts" order by "id" asc',
|
|
[],
|
|
[
|
|
{
|
|
email: 'test1@example.com',
|
|
logins: 1,
|
|
},
|
|
{
|
|
email: 'test2@example.com',
|
|
logins: 1,
|
|
},
|
|
{
|
|
email: 'test3@example.com',
|
|
logins: 2,
|
|
},
|
|
{
|
|
email: 'test4@example.com',
|
|
logins: 2,
|
|
},
|
|
{
|
|
email: 'test5@example.com',
|
|
logins: 2,
|
|
},
|
|
{
|
|
email: 'test6@example.com',
|
|
logins: 2,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pgnative',
|
|
'select distinct on ("id") "email", "logins" from "accounts" order by "id" asc',
|
|
[],
|
|
[
|
|
{
|
|
email: 'test1@example.com',
|
|
logins: 1,
|
|
},
|
|
{
|
|
email: 'test2@example.com',
|
|
logins: 1,
|
|
},
|
|
{
|
|
email: 'test3@example.com',
|
|
logins: 2,
|
|
},
|
|
{
|
|
email: 'test4@example.com',
|
|
logins: 2,
|
|
},
|
|
{
|
|
email: 'test5@example.com',
|
|
logins: 2,
|
|
},
|
|
{
|
|
email: 'test6@example.com',
|
|
logins: 2,
|
|
},
|
|
]
|
|
);
|
|
});
|
|
});
|
|
|
|
it('does "orWhere" cases', function () {
|
|
return knex('accounts')
|
|
.where('id', 1)
|
|
.orWhere('id', '>', 2)
|
|
.select('first_name', 'last_name');
|
|
});
|
|
|
|
it('does "andWhere" cases', function () {
|
|
return knex('accounts')
|
|
.select('first_name', 'last_name', 'about')
|
|
.where('id', 1)
|
|
.andWhere('email', 'test1@example.com');
|
|
});
|
|
|
|
it('takes a function to wrap nested where statements', function () {
|
|
return Promise.all([
|
|
knex('accounts')
|
|
.where(function () {
|
|
this.where('id', 2);
|
|
this.orWhere('id', 3);
|
|
})
|
|
.select('*'),
|
|
]);
|
|
});
|
|
|
|
it('handles "where in" cases', function () {
|
|
return Promise.all([
|
|
knex('accounts').whereIn('id', [1, 2, 3]).select(),
|
|
]);
|
|
});
|
|
|
|
it('handles "or where in" cases', function () {
|
|
return knex('accounts')
|
|
.where('email', 'test1@example.com')
|
|
.orWhereIn('id', [2, 3, 4])
|
|
.select();
|
|
});
|
|
|
|
it('handles multi-column "where in" cases', async function () {
|
|
await knex('composite_key_test').insert([
|
|
{
|
|
column_a: 1,
|
|
column_b: 1,
|
|
details: 'One, One, One',
|
|
status: 1,
|
|
},
|
|
{
|
|
column_a: 1,
|
|
column_b: 2,
|
|
details: 'One, Two, Zero',
|
|
status: 0,
|
|
},
|
|
{
|
|
column_a: 2,
|
|
column_b: 2,
|
|
details: 'Two, Two, Zero',
|
|
status: 0,
|
|
},
|
|
]);
|
|
|
|
if (!isMssql(knex)) {
|
|
await knex('composite_key_test')
|
|
.whereIn(
|
|
['column_a', 'column_b'],
|
|
[
|
|
[1, 1],
|
|
[1, 2],
|
|
]
|
|
)
|
|
.orderBy('status', 'desc')
|
|
.select()
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'mysql',
|
|
'select * from `composite_key_test` where (`column_a`, `column_b`) in ((?, ?), (?, ?)) order by `status` desc',
|
|
[1, 1, 1, 2],
|
|
[
|
|
{
|
|
column_a: 1,
|
|
column_b: 1,
|
|
details: 'One, One, One',
|
|
status: 1,
|
|
},
|
|
{
|
|
column_a: 1,
|
|
column_b: 2,
|
|
details: 'One, Two, Zero',
|
|
status: 0,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'select * from "composite_key_test" where ("column_a", "column_b") in ((?, ?), (?, ?)) order by "status" desc',
|
|
[1, 1, 1, 2],
|
|
[
|
|
{
|
|
column_a: 1,
|
|
column_b: 1,
|
|
details: 'One, One, One',
|
|
status: 1,
|
|
},
|
|
{
|
|
column_a: 1,
|
|
column_b: 2,
|
|
details: 'One, Two, Zero',
|
|
status: 0,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pgnative',
|
|
'select * from "composite_key_test" where ("column_a", "column_b") in ((?, ?), (?, ?)) order by "status" desc',
|
|
[1, 1, 1, 2],
|
|
[
|
|
{
|
|
column_a: 1,
|
|
column_b: 1,
|
|
details: 'One, One, One',
|
|
status: 1,
|
|
},
|
|
{
|
|
column_a: 1,
|
|
column_b: 2,
|
|
details: 'One, Two, Zero',
|
|
status: 0,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'select * from "composite_key_test" where ("column_a", "column_b") in ((?, ?), (?, ?)) order by "status" desc',
|
|
[1, 1, 1, 2],
|
|
[
|
|
{
|
|
column_a: 1,
|
|
column_b: 1,
|
|
details: 'One, One, One',
|
|
status: 1,
|
|
},
|
|
{
|
|
column_a: 1,
|
|
column_b: 2,
|
|
details: 'One, Two, Zero',
|
|
status: 0,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'select * from "composite_key_test" where ("column_a", "column_b") in ((?, ?), (?, ?)) order by "status" desc',
|
|
[1, 1, 1, 2],
|
|
[
|
|
{
|
|
column_a: 1,
|
|
column_b: 1,
|
|
details: 'One, One, One',
|
|
status: 1,
|
|
},
|
|
{
|
|
column_a: 1,
|
|
column_b: 2,
|
|
details: 'One, Two, Zero',
|
|
status: 0,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'select * from `composite_key_test` where (`column_a`, `column_b`) in ( values (?, ?), (?, ?)) order by `status` desc',
|
|
[1, 1, 1, 2],
|
|
[
|
|
{
|
|
column_a: 1,
|
|
column_b: 1,
|
|
details: 'One, One, One',
|
|
status: 1,
|
|
},
|
|
{
|
|
column_a: 1,
|
|
column_b: 2,
|
|
details: 'One, Two, Zero',
|
|
status: 0,
|
|
},
|
|
]
|
|
);
|
|
});
|
|
}
|
|
});
|
|
|
|
it('handles multi-column "where in" cases with where', function () {
|
|
if (!isSQLite(knex) && !isMssql(knex)) {
|
|
return knex('composite_key_test')
|
|
.where('status', 1)
|
|
.whereIn(
|
|
['column_a', 'column_b'],
|
|
[
|
|
[1, 1],
|
|
[1, 2],
|
|
]
|
|
)
|
|
.select()
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'mysql',
|
|
'select * from `composite_key_test` where `status` = ? and (`column_a`, `column_b`) in ((?, ?), (?, ?))',
|
|
[1, 1, 1, 1, 2],
|
|
[
|
|
{
|
|
column_a: 1,
|
|
column_b: 1,
|
|
details: 'One, One, One',
|
|
status: 1,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'select * from "composite_key_test" where "status" = ? and ("column_a", "column_b") in ((?, ?), (?, ?))',
|
|
[1, 1, 1, 1, 2],
|
|
[
|
|
{
|
|
column_a: 1,
|
|
column_b: 1,
|
|
details: 'One, One, One',
|
|
status: 1,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pgnative',
|
|
'select * from "composite_key_test" where "status" = ? and ("column_a", "column_b") in ((?, ?), (?, ?))',
|
|
[1, 1, 1, 1, 2],
|
|
[
|
|
{
|
|
column_a: 1,
|
|
column_b: 1,
|
|
details: 'One, One, One',
|
|
status: 1,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'select * from "composite_key_test" where "status" = ? and ("column_a", "column_b") in ((?, ?), (?, ?))',
|
|
[1, 1, 1, 1, 2],
|
|
[
|
|
{
|
|
column_a: 1,
|
|
column_b: 1,
|
|
details: 'One, One, One',
|
|
status: 1,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'select * from "composite_key_test" where "status" = ? and ("column_a", "column_b") in ((?, ?), (?, ?))',
|
|
[1, 1, 1, 1, 2],
|
|
[
|
|
{
|
|
column_a: 1,
|
|
column_b: 1,
|
|
details: 'One, One, One',
|
|
status: 1,
|
|
},
|
|
]
|
|
);
|
|
});
|
|
}
|
|
});
|
|
|
|
it('handles "where exists"', function () {
|
|
return knex('accounts')
|
|
.whereExists(function () {
|
|
this.select('id').from('test_table_two').where({ id: 1 });
|
|
})
|
|
.select();
|
|
});
|
|
|
|
it('handles "where between"', function () {
|
|
return knex('accounts').whereBetween('id', [1, 100]).select();
|
|
});
|
|
|
|
it('handles "or where between"', function () {
|
|
return knex('accounts')
|
|
.whereBetween('id', [1, 100])
|
|
.orWhereBetween('id', [200, 300])
|
|
.select();
|
|
});
|
|
|
|
it('does where(raw)', function () {
|
|
if (isOracle(knex)) {
|
|
// special case for oracle
|
|
return knex('accounts')
|
|
.whereExists(function () {
|
|
this.select(knex.raw(1))
|
|
.from('test_table_two')
|
|
.where(
|
|
knex.raw('"test_table_two"."account_id" = "accounts"."id"')
|
|
);
|
|
})
|
|
.select();
|
|
} else {
|
|
return knex('accounts')
|
|
.whereExists(function () {
|
|
this.select(knex.raw(1))
|
|
.from('test_table_two')
|
|
.where(knex.raw('test_table_two.account_id = accounts.id'));
|
|
})
|
|
.select();
|
|
}
|
|
});
|
|
|
|
it('does sub-selects', function () {
|
|
return knex('accounts')
|
|
.whereIn('id', function () {
|
|
this.select('account_id').from('test_table_two').where('status', 1);
|
|
})
|
|
.select('first_name', 'last_name');
|
|
});
|
|
|
|
describe('recursive CTE support', function () {
|
|
before(async function () {
|
|
await knex.schema.dropTableIfExists('rcte');
|
|
await knex.schema.createTable('rcte', (table) => {
|
|
table.string('name');
|
|
table.string('parentName').nullable();
|
|
});
|
|
|
|
// We will check later that this name was found by chaining up parentId using an rCTE.
|
|
await knex('rcte').insert({ name: 'parent' });
|
|
let parentName = 'parent';
|
|
for (const name of ['child', 'grandchild']) {
|
|
await knex('rcte').insert({ name, parentName });
|
|
parentName = name;
|
|
}
|
|
|
|
// We will check later that this name is not returned.
|
|
await knex('rcte').insert({ name: 'nope' });
|
|
});
|
|
it('supports recursive CTEs', async function () {
|
|
const results = await knex
|
|
.withRecursive('family', ['name', 'parentName'], (qb) => {
|
|
qb.select('name', 'parentName')
|
|
.from('rcte')
|
|
.where({ name: 'grandchild' })
|
|
.unionAll((qb) =>
|
|
qb
|
|
.select('rcte.name', 'rcte.parentName')
|
|
.from('rcte')
|
|
.join(
|
|
'family',
|
|
knex.ref('family.parentName'),
|
|
knex.ref('rcte.name')
|
|
)
|
|
);
|
|
})
|
|
.select('name')
|
|
.from('family');
|
|
const names = results.map(({ name }) => name);
|
|
|
|
expect(names).to.have.length(
|
|
'parent child grandchild'.split(' ').length
|
|
);
|
|
expect(names).to.contain('parent');
|
|
expect(names).not.to.contain('nope');
|
|
});
|
|
});
|
|
|
|
it('supports the <> operator', function () {
|
|
return knex('accounts').where('id', '<>', 2).select('email', 'logins');
|
|
});
|
|
|
|
it('Allows for knex.Raw passed to the `where` clause', function () {
|
|
if (isOracle(knex)) {
|
|
return knex('accounts')
|
|
.where(knex.raw('"id" = 2'))
|
|
.select('email', 'logins');
|
|
} else {
|
|
return knex('accounts')
|
|
.where(knex.raw('id = 2'))
|
|
.select('email', 'logins');
|
|
}
|
|
});
|
|
|
|
it('Retains array bindings, #228', function () {
|
|
const raw = knex.raw(
|
|
'select * from table t where t.id = ANY( ?::int[] )',
|
|
[[1, 2, 3]]
|
|
);
|
|
const raw2 = knex.raw('select "stored_procedure"(?, ?, ?)', [
|
|
1,
|
|
2,
|
|
['a', 'b', 'c'],
|
|
]);
|
|
const expected1 = [[1, 2, 3]];
|
|
const expected2 = [1, 2, ['a', 'b', 'c']];
|
|
expect(raw.toSQL().bindings).to.eql(
|
|
knex.client.prepBindings(expected1)
|
|
);
|
|
expect(raw2.toSQL().bindings).to.eql(
|
|
knex.client.prepBindings(expected2)
|
|
);
|
|
//Also expect raw's bindings to not have been modified by calling .toSQL() (preserving original bindings)
|
|
expect(raw.bindings).to.eql(expected1);
|
|
expect(raw2.bindings).to.eql(expected2);
|
|
});
|
|
|
|
it('always returns the response object from raw', function () {
|
|
if (isPostgreSQL(knex)) {
|
|
return knex.raw('select id from accounts').then(function (resp) {
|
|
assert(Array.isArray(resp.rows) === true);
|
|
});
|
|
}
|
|
});
|
|
|
|
it('properly escapes identifiers, #737', function () {
|
|
if (isPostgreSQL(knex)) {
|
|
const query = knex.select('id","name').from('test').toSQL();
|
|
assert(query.sql === 'select "id"",""name" from "test"');
|
|
}
|
|
});
|
|
|
|
it('knex.ref() as column in .select()', async function () {
|
|
const result = await knex('accounts').select();
|
|
|
|
const row = await knex('accounts')
|
|
.select([knex.ref('accounts.id').as('userid')])
|
|
.select(['accounts.id'])
|
|
.where(knex.ref('accounts.id'), result[0].id)
|
|
.first();
|
|
expect(String(row.userid)).to.equal(String(result[0].id));
|
|
});
|
|
|
|
it.skip('select forUpdate().first() bug in oracle (--------- TODO: FIX)', function () {
|
|
return knex('accounts').where('id', 1).forUpdate().first();
|
|
});
|
|
|
|
it('select for update locks selected row', function () {
|
|
if (isSQLite(knex)) {
|
|
return this.skip();
|
|
}
|
|
|
|
return knex('test_default_table')
|
|
.insert({ string: 'making sure there is a row to lock' })
|
|
.then(() => {
|
|
return knex
|
|
.transaction((trx) => {
|
|
// select all from test table and lock
|
|
return trx('test_default_table')
|
|
.forUpdate()
|
|
.then((res) => {
|
|
// try to select stuff from table in other connection should just hang...
|
|
return knex('test_default_table').forUpdate().timeout(100);
|
|
});
|
|
})
|
|
.then((res) => {
|
|
expect('Second query should have timed out').to.be.false;
|
|
})
|
|
.catch((err) => {
|
|
expect(err.message).to.be.contain(
|
|
'Defined query timeout of 100ms exceeded when running query'
|
|
);
|
|
});
|
|
});
|
|
});
|
|
|
|
it('select for update locks only some tables, #2834', function () {
|
|
if (!isPostgreSQL(knex)) {
|
|
return this.skip();
|
|
}
|
|
|
|
return knex('test_default_table')
|
|
.insert({ string: 'making sure there is a row to lock', tinyint: 1 })
|
|
.then(() => {
|
|
return knex('test_default_table2')
|
|
.insert({
|
|
string: 'making sure there is a row to lock',
|
|
tinyint: 1,
|
|
})
|
|
.then(() => {
|
|
return knex
|
|
.transaction((trx) => {
|
|
// select all from two test tables and lock only one table
|
|
return trx('test_default_table')
|
|
.innerJoin(
|
|
'test_default_table2',
|
|
'test_default_table.tinyint',
|
|
'test_default_table2.tinyint'
|
|
)
|
|
.forUpdate('test_default_table')
|
|
.then((res) => {
|
|
// try to select stuff from unlocked table should not hang...
|
|
return knex('test_default_table2')
|
|
.forUpdate()
|
|
.timeout(150);
|
|
})
|
|
.then((res) => {
|
|
// try to select stuff from table in other connection should just hang...
|
|
return knex('test_default_table')
|
|
.forUpdate()
|
|
.timeout(100);
|
|
});
|
|
})
|
|
.then((res) => {
|
|
expect('Second query should have timed out').to.be.false;
|
|
})
|
|
.catch((err) => {
|
|
expect(err.message).to.be.contain(
|
|
'Defined query timeout of 100ms exceeded when running query'
|
|
);
|
|
});
|
|
});
|
|
});
|
|
});
|
|
|
|
it('select for share prevents updating in other transaction', function () {
|
|
// Query cancellation is not yet implemented for CockroachDB
|
|
if (isSQLite(knex) || isOracle(knex) || isCockroachDB(knex)) {
|
|
return this.skip();
|
|
}
|
|
|
|
return knex('test_default_table')
|
|
.insert({ string: 'making sure there is a row to lock' })
|
|
.then(() => {
|
|
return knex
|
|
.transaction((trx) => {
|
|
// select all from test table and lock
|
|
return trx('test_default_table')
|
|
.forShare()
|
|
.then((res) => {
|
|
// try to update row that was selected for share should just hang...
|
|
return knex.transaction((trx2) => {
|
|
return trx2('test_default_table')
|
|
.update({ string: 'foo' })
|
|
.timeout(100);
|
|
});
|
|
});
|
|
})
|
|
.then((res) => {
|
|
expect('Second query should have timed out').to.be.false;
|
|
})
|
|
.catch((err) => {
|
|
// mssql fails because it tries to rollback at the same time when update query is running
|
|
// hopefully for share really works though...
|
|
if (isMssql(knex)) {
|
|
expect(err.message).to.be.contain(
|
|
"Can't rollback transaction. There is a request in progress"
|
|
);
|
|
} else {
|
|
expect(err.message).to.be.contain(
|
|
'Defined query timeout of 100ms exceeded when running query'
|
|
);
|
|
}
|
|
});
|
|
});
|
|
});
|
|
|
|
it('forUpdate().skipLocked() with order by should return the first non-locked row', async function () {
|
|
// Note: this test doesn't work properly on MySQL - see https://bugs.mysql.com/bug.php?id=67745
|
|
if (!isPostgreSQL(knex)) {
|
|
return this.skip();
|
|
}
|
|
|
|
const rowName = 'row for skipLocked() test #1';
|
|
await knex('test_default_table').delete().where({ string: rowName });
|
|
await knex('test_default_table').insert([
|
|
{ string: rowName, tinyint: 1 },
|
|
{ string: rowName, tinyint: 2 },
|
|
]);
|
|
|
|
const res = await knex.transaction(async (trx) => {
|
|
// lock the first row in the test
|
|
await trx('test_default_table')
|
|
.where({ string: rowName })
|
|
.orderBy('tinyint', 'asc')
|
|
.forUpdate()
|
|
.first();
|
|
|
|
// try to lock the next available row from outside of the transaction
|
|
return await knex('test_default_table')
|
|
.where({ string: rowName })
|
|
.orderBy('tinyint', 'asc')
|
|
.forUpdate()
|
|
.skipLocked()
|
|
.first();
|
|
});
|
|
|
|
// assert that we got the second row because the first one was locked
|
|
expect(res.tinyint).to.equal(2);
|
|
});
|
|
|
|
it('forUpdate().skipLocked() should return an empty set when all rows are locked', async function () {
|
|
if (!isPostgreSQL(knex) && !isMysql(knex)) {
|
|
return this.skip();
|
|
}
|
|
|
|
const rowName = 'row for skipLocked() test #2';
|
|
await knex('test_default_table').delete().where({ string: rowName });
|
|
await knex('test_default_table').insert([
|
|
{ string: rowName, tinyint: 1 },
|
|
{ string: rowName, tinyint: 2 },
|
|
]);
|
|
|
|
const res = await knex.transaction(async (trx) => {
|
|
// lock all of the test rows
|
|
await trx('test_default_table')
|
|
.where({ string: rowName })
|
|
.forUpdate();
|
|
|
|
// try to aquire the lock on one more row (which isn't available) from another transaction
|
|
return await knex('test_default_table')
|
|
.where({ string: rowName })
|
|
.forUpdate()
|
|
.skipLocked()
|
|
.first();
|
|
});
|
|
|
|
expect(res).to.be.undefined;
|
|
});
|
|
|
|
it('forUpdate().noWait() should throw an error immediately when a row is locked', async function () {
|
|
if (!isPostgreSQL(knex) && !isMysql(knex)) {
|
|
return this.skip();
|
|
}
|
|
|
|
const rowName = 'row for noWait() test';
|
|
await knex('test_default_table').delete().where({ string: rowName });
|
|
await knex('test_default_table').insert([
|
|
{ string: rowName, tinyint: 1 },
|
|
{ string: rowName, tinyint: 2 },
|
|
]);
|
|
|
|
try {
|
|
await knex.transaction(async (trx) => {
|
|
// select and lock the first row from this test
|
|
// note: MySQL may lock both rows depending on how the results are fetched
|
|
await trx('test_default_table')
|
|
.where({ string: rowName })
|
|
.orderBy('tinyint', 'asc')
|
|
.forUpdate()
|
|
.first();
|
|
|
|
// try to lock it again (it should fail here)
|
|
await knex('test_default_table')
|
|
.where({ string: rowName })
|
|
.orderBy('tinyint', 'asc')
|
|
.forUpdate()
|
|
.noWait()
|
|
.first();
|
|
});
|
|
|
|
// fail the test if the query finishes with no errors
|
|
throw new Error(
|
|
'The query should have been cancelled when trying to select a locked row with .noWait()'
|
|
);
|
|
} catch (err) {
|
|
// check if we got the correct error from each db
|
|
if (isPostgreSQL(knex)) {
|
|
expect(err.message).to.contain('could not obtain lock on row');
|
|
} else if (isMysql(knex)) {
|
|
// mysql
|
|
expect(err.message).to.contain(
|
|
'lock(s) could not be acquired immediately'
|
|
);
|
|
// mariadb
|
|
// TODO: detect if test is being run on mysql or mariadb to check for the correct error message
|
|
// expect(err.message).to.contain('Lock wait timeout exceeded');
|
|
} else {
|
|
// unsupported database
|
|
throw err;
|
|
}
|
|
}
|
|
});
|
|
|
|
it('select from subquery', async function () {
|
|
const result = await knex('accounts').select().orderBy('id');
|
|
|
|
const subquery = knex
|
|
.from('accounts')
|
|
.whereBetween('id', [result[0].id, result[2].id]);
|
|
return knex
|
|
.pluck('id')
|
|
.orderBy('id')
|
|
.from(subquery)
|
|
.then(
|
|
(rows) => {
|
|
expect(knex.client.driverName).to.oneOf([
|
|
'sqlite3',
|
|
'oracledb',
|
|
'cockroachdb',
|
|
]);
|
|
|
|
if (knex.client.driverName !== 'cockroachdb') {
|
|
assertNumberArrayStrict(knex, rows, [
|
|
result[0].id,
|
|
result[1].id,
|
|
result[2].id,
|
|
]);
|
|
} else {
|
|
expect(rows.length).to.equal(3);
|
|
}
|
|
},
|
|
(e) => {
|
|
if (isMysql(knex)) {
|
|
expect(e.errno).to.equal(1248);
|
|
} else if (isPostgreSQL(knex)) {
|
|
expect(e.message).to.contain('must have an alias');
|
|
} else if (isMssql(knex)) {
|
|
expect(e.message).to.contain(
|
|
"Incorrect syntax near the keyword 'order'"
|
|
);
|
|
} else {
|
|
throw e;
|
|
}
|
|
}
|
|
);
|
|
});
|
|
});
|
|
});
|
|
});
|