query-generator.test.js
3.86 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
'use strict';
/* jshint -W110 */
var Support = require(__dirname + '/../../support')
, expectsql = Support.expectsql
, current = Support.sequelize
, QueryGenerator = require('../../../../lib/dialects/mssql/query-generator')
, _ = require('lodash');
if (current.dialect.name === 'mssql') {
suite('[MSSQL Specific] QueryGenerator', function () {
// Dialect would normally be set by the query interface that instantiates the query-generator, but here we specify it explicitly
QueryGenerator._dialect = current.dialect;
test('getDefaultConstraintQuery', function () {
expectsql(QueryGenerator.getDefaultConstraintQuery({tableName: 'myTable', schema: 'mySchema'}, 'myColumn'), {
mssql: "SELECT name FROM SYS.DEFAULT_CONSTRAINTS WHERE PARENT_OBJECT_ID = OBJECT_ID('[mySchema].[myTable]', 'U') AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns WHERE NAME = ('myColumn') AND object_id = OBJECT_ID('[mySchema].[myTable]', 'U'));"
});
});
test('dropConstraintQuery', function () {
expectsql(QueryGenerator.dropConstraintQuery({tableName: 'myTable', schema: 'mySchema'}, 'myConstraint'), {
mssql: "ALTER TABLE [mySchema].[myTable] DROP CONSTRAINT [myConstraint];"
});
});
test('selectFromTableFragment', function() {
var modifiedGen = _.clone(QueryGenerator);
// Test newer versions first
// Should be all the same since handling is done in addLimitAndOffset
// for SQL Server 2012 and higher (>= v11.0.0)
modifiedGen.sequelize = {
options: {
databaseVersion: '11.0.0'
}
};
// Base case
expectsql(modifiedGen.selectFromTableFragment({}, { primaryKeyField: 'id' }, ['id', 'name'], 'myTable', 'myOtherName', 'WHERE id=1'), {
mssql: "SELECT id, name FROM myTable AS myOtherName"
});
// With limit
expectsql(modifiedGen.selectFromTableFragment({ limit: 10 }, { primaryKeyField: 'id' }, ['id', 'name'], 'myTable', 'myOtherName'), {
mssql: "SELECT id, name FROM myTable AS myOtherName"
});
// With offset
expectsql(modifiedGen.selectFromTableFragment({ offset: 10 }, { primaryKeyField: 'id' }, ['id', 'name'], 'myTable', 'myOtherName'), {
mssql: "SELECT id, name FROM myTable AS myOtherName"
});
// With both limit and offset
expectsql(modifiedGen.selectFromTableFragment({ limit: 10, offset: 10 }, { primaryKeyField: 'id' }, ['id', 'name'], 'myTable', 'myOtherName'), {
mssql: "SELECT id, name FROM myTable AS myOtherName"
});
// Test older version (< v11.0.0)
modifiedGen.sequelize.options.databaseVersion = '10.0.0';
// Base case
expectsql(modifiedGen.selectFromTableFragment({}, { primaryKeyField: 'id' }, ['id', 'name'], 'myTable', 'myOtherName', 'WHERE id=1'), {
mssql: "SELECT id, name FROM myTable AS myOtherName"
});
// With limit
expectsql(modifiedGen.selectFromTableFragment({ limit: 10 }, { primaryKeyField: 'id' }, ['id', 'name'], 'myTable', 'myOtherName'), {
mssql: "SELECT TOP 10 id, name FROM myTable AS myOtherName"
});
// With offset
expectsql(modifiedGen.selectFromTableFragment({ offset: 10 }, { primaryKeyField: 'id' }, ['id', 'name'], 'myTable', 'myOtherName'), {
mssql: "SELECT TOP 100 PERCENT id, name FROM (SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY [id]) as row_num, * FROM myTable AS myOtherName) AS myOtherName WHERE row_num > 10) AS myOtherName"
});
// With both limit and offset
expectsql(modifiedGen.selectFromTableFragment({ limit: 10, offset: 10 }, { primaryKeyField: 'id' }, ['id', 'name'], 'myTable', 'myOtherName'), {
mssql: "SELECT TOP 100 PERCENT id, name FROM (SELECT TOP 10 * FROM (SELECT ROW_NUMBER() OVER (ORDER BY [id]) as row_num, * FROM myTable AS myOtherName) AS myOtherName WHERE row_num > 10) AS myOtherName"
});
});
});
}