knex/test/integration2/query/select/unions.spec.js
2021-10-10 17:23:01 +03:00

431 lines
12 KiB
JavaScript

'use strict';
const expect = require('chai').expect;
const {
isMssql,
isOracle,
isPgBased,
isSQLite,
} = require('../../../util/db-helpers');
const { assertNumberArray } = require('../../../util/assertHelper');
const {
dropTables,
createUsers,
createAccounts,
createCompositeKeyTable,
createTestTableTwo,
createDefaultTable,
} = require('../../../util/tableCreatorHelper');
const { insertAccounts } = require('../../../util/dataInsertHelper');
const {
getAllDbs,
getKnexForDb,
} = require('../../util/knex-instance-provider');
describe('unions', function () {
getAllDbs().forEach((db) => {
describe(db, () => {
let knex;
before(async () => {
knex = 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 () => {
await knex.destroy();
});
it('handles unions with a callback', function () {
return knex('accounts')
.select('*')
.where('id', '=', 1)
.union(function () {
this.select('*').from('accounts').where('id', 2);
});
});
it('handles unions with an array of callbacks', function () {
return knex('accounts')
.select('*')
.where('id', '=', 1)
.union([
function () {
this.select('*').from('accounts').where('id', 2);
},
function () {
this.select('*').from('accounts').where('id', 3);
},
]);
});
it('handles unions with a list of callbacks', function () {
return knex('accounts')
.select('*')
.where('id', '=', 1)
.union(
function () {
this.select('*').from('accounts').where('id', 2);
},
function () {
this.select('*').from('accounts').where('id', 3);
}
);
});
it('handles unions with an array of builders', function () {
return knex('accounts')
.select('*')
.where('id', '=', 1)
.union([
knex.select('*').from('accounts').where('id', 2),
knex.select('*').from('accounts').where('id', 3),
]);
});
it('handles unions with a list of builders', function () {
return knex('accounts')
.select('*')
.where('id', '=', 1)
.union(
knex.select('*').from('accounts').where('id', 2),
knex.select('*').from('accounts').where('id', 3)
);
});
it('handles unions with a raw query', function () {
return knex('accounts')
.select('*')
.where('id', '=', 1)
.union(
knex.raw('select * from ?? where ?? = ?', ['accounts', 'id', 2])
);
});
it('handles unions with an array raw queries', function () {
return knex('accounts')
.select('*')
.where('id', '=', 1)
.union([
knex.raw('select * from ?? where ?? = ?', ['accounts', 'id', 2]),
knex.raw('select * from ?? where ?? = ?', ['accounts', 'id', 3]),
]);
});
it('handles unions with a list of raw queries', function () {
return knex('accounts')
.select('*')
.where('id', '=', 1)
.union(
knex.raw('select * from ?? where ?? = ?', ['accounts', 'id', 2]),
knex.raw('select * from ?? where ?? = ?', ['accounts', 'id', 3])
);
});
describe('intersects', function () {
before(async function () {
await knex.schema.createTable('intersect_test', function (t) {
t.integer('id');
t.integer('test_col_1');
t.integer('test_col_2');
t.integer('test_col_3');
});
});
beforeEach(function () {
return knex('intersect_test').insert([
{
id: 1,
test_col_1: 1,
test_col_2: 2,
test_col_3: 1,
},
{
id: 2,
test_col_1: 2,
test_col_2: 3,
test_col_3: 1,
},
{
id: 3,
test_col_1: 2,
test_col_2: 3,
test_col_3: 2,
},
{
id: 4,
test_col_1: 1,
test_col_2: 2,
test_col_3: 2,
},
{
id: 5,
test_col_1: 1,
test_col_2: 2,
test_col_3: 1,
},
]);
});
after(function () {
return knex.schema.dropTable('intersect_test');
});
it('handles intersects with a callback', async function () {
if (
!isPgBased(knex) &&
!isMssql(knex) &&
!isOracle(knex) &&
!isSQLite(knex)
) {
return this.skip();
}
await knex('intersect_test')
.select('*')
.where('test_col_1', '=', 1)
.intersect(function () {
this.select('*').from('intersect_test').where('test_col_2', 2);
})
.then(function (result) {
expect(result.length).to.equal(3);
assertNumberArray(
knex,
result.map((r) => r.id),
[1, 4, 5]
);
});
});
it('handles intersects with an array of callbacks', async function () {
if (
!isPgBased(knex) &&
!isMssql(knex) &&
!isOracle(knex) &&
!isSQLite(knex)
) {
return this.skip();
}
await knex('intersect_test')
.select('*')
.where('test_col_1', '=', 1)
.intersect([
function () {
this.select('*').from('intersect_test').where('test_col_2', 2);
},
function () {
this.select('*').from('intersect_test').where('test_col_3', 1);
},
])
.then(function (result) {
expect(result.length).to.equal(2);
assertNumberArray(
knex,
result.map((r) => r.id),
[1, 5]
);
});
});
it('handles intersects with a list of callbacks', async function () {
if (
!isPgBased(knex) &&
!isMssql(knex) &&
!isOracle(knex) &&
!isSQLite(knex)
) {
return this.skip();
}
await knex('intersect_test')
.select('*')
.where('test_col_1', '=', 1)
.intersect(
function () {
this.select('*').from('intersect_test').where('test_col_2', 2);
},
function () {
this.select('*').from('intersect_test').where('test_col_3', 1);
}
)
.then(function (result) {
expect(result.length).to.equal(2);
assertNumberArray(
knex,
result.map((r) => r.id),
[1, 5]
);
});
});
it('handles intersects with an array of builders', async function () {
if (
!isPgBased(knex) &&
!isMssql(knex) &&
!isOracle(knex) &&
!isSQLite(knex)
) {
return this.skip();
}
await knex('intersect_test')
.select('*')
.where('test_col_1', '=', 1)
.intersect([
knex.select('*').from('intersect_test').where('test_col_2', 2),
knex.select('*').from('intersect_test').where('test_col_3', 1),
])
.then(function (result) {
expect(result.length).to.equal(2);
assertNumberArray(
knex,
result.map((r) => r.id),
[1, 5]
);
});
});
it('handles intersects with a list of builders', async function () {
if (
!isPgBased(knex) &&
!isMssql(knex) &&
!isOracle(knex) &&
!isSQLite(knex)
) {
return this.skip();
}
await knex('intersect_test')
.select('*')
.where('test_col_1', '=', 1)
.intersect(
knex.select('*').from('intersect_test').where('test_col_2', 2),
knex.select('*').from('intersect_test').where('test_col_3', 1)
)
.then(function (result) {
expect(result.length).to.equal(2);
assertNumberArray(
knex,
result.map((r) => r.id),
[1, 5]
);
});
});
it('handles intersects with a raw query', async function () {
if (
!isPgBased(knex) &&
!isMssql(knex) &&
!isOracle(knex) &&
!isSQLite(knex)
) {
return this.skip();
}
await knex('intersect_test')
.select('*')
.where('test_col_1', '=', 2)
.intersect(
knex.raw('select * from ?? where ?? = ?', [
'intersect_test',
'test_col_2',
3,
])
)
.then(function (result) {
expect(result.length).to.equal(2);
assertNumberArray(
knex,
result.map((r) => r.id),
[2, 3]
);
});
});
it('handles intersects with an array raw queries', async function () {
if (
!isPgBased(knex) &&
!isMssql(knex) &&
!isOracle(knex) &&
!isSQLite(knex)
) {
return this.skip();
}
await knex('intersect_test')
.select('*')
.where('test_col_1', '=', 1)
.intersect([
knex.raw('select * from ?? where ?? = ?', [
'intersect_test',
'test_col_2',
2,
]),
knex.raw('select * from ?? where ?? = ?', [
'intersect_test',
'test_col_3',
1,
]),
])
.then(function (result) {
expect(result.length).to.equal(2);
assertNumberArray(
knex,
result.map((r) => r.id),
[1, 5]
);
});
});
it('handles intersects with a list of raw queries', async function () {
if (
!isPgBased(knex) &&
!isMssql(knex) &&
!isOracle(knex) &&
!isSQLite(knex)
) {
return this.skip();
}
await knex('intersect_test')
.select('*')
.where('test_col_1', '=', 1)
.intersect(
knex.raw('select * from ?? where ?? = ?', [
'intersect_test',
'test_col_2',
2,
]),
knex.raw('select * from ?? where ?? = ?', [
'intersect_test',
'test_col_3',
1,
])
)
.then(function (result) {
expect(result.length).to.equal(2);
assertNumberArray(
knex,
result.map((r) => r.id),
[1, 5]
);
});
});
});
});
});
});