1 |
|
2 |
|
3 |
|
4 |
|
5 |
|
6 | 'use strict';
|
7 | const expect = require('chai').expect;
|
8 | const SQLConnector = require('../lib/sql');
|
9 | const ParameterizedSQL = SQLConnector.ParameterizedSQL;
|
10 | const testConnector = require('./connectors/test-sql-connector');
|
11 |
|
12 | const juggler = require('loopback-datasource-juggler');
|
13 | const ds = new juggler.DataSource({
|
14 | connector: testConnector,
|
15 | debug: true,
|
16 | });
|
17 |
|
18 | let connector;
|
19 | let Customer;
|
20 | let Order;
|
21 |
|
22 |
|
23 | describe('sql connector', function() {
|
24 | before(function() {
|
25 | connector = ds.connector;
|
26 | connector._tables = {};
|
27 | connector._models = {};
|
28 | Customer = ds.createModel('customer',
|
29 | {
|
30 | name: {
|
31 | id: true,
|
32 | type: String,
|
33 | testdb: {
|
34 | column: 'NAME',
|
35 | dataType: 'VARCHAR',
|
36 | dataLength: 32,
|
37 | },
|
38 | }, middleName: {
|
39 | type: Boolean,
|
40 | name: 'middle_name',
|
41 | postgresql: {
|
42 | column: 'MIDDLENAME',
|
43 | },
|
44 | }, lastName: {
|
45 | type: Boolean,
|
46 | name: 'last_name',
|
47 | testdb: {
|
48 | column: 'LASTNAME',
|
49 | },
|
50 | }, vip: {
|
51 | type: Boolean,
|
52 | testdb: {
|
53 | column: 'VIP',
|
54 | },
|
55 | }, primaryAddress: {
|
56 | type: String,
|
57 | name: 'primary_address',
|
58 | },
|
59 | address: String,
|
60 | },
|
61 | {testdb: {table: 'CUSTOMER'}});
|
62 | Order = ds.createModel('order',
|
63 | {
|
64 | id: {
|
65 | id: true,
|
66 | type: Number,
|
67 | testdb: {
|
68 | column: 'orderId',
|
69 | dataType: 'INTEGER',
|
70 | },
|
71 | }, des: {
|
72 | type: String,
|
73 | name: 'des',
|
74 | testdb: {
|
75 | column: 'description',
|
76 | },
|
77 | },
|
78 | },
|
79 | {testdb: {table: 'ORDER'}});
|
80 | });
|
81 |
|
82 |
|
83 |
|
84 | it('should map table name', function() {
|
85 | const table = connector.table('customer');
|
86 | expect(table).to.eql('CUSTOMER');
|
87 | });
|
88 |
|
89 | it('should find column metadata', function() {
|
90 | const column = connector.columnMetadata('customer', 'name');
|
91 | expect(column).to.eql({
|
92 | column: 'NAME',
|
93 | dataType: 'VARCHAR',
|
94 | dataLength: 32,
|
95 | });
|
96 | });
|
97 |
|
98 | it('should map property name', function() {
|
99 | const prop = connector.propertyName('customer', 'NAME');
|
100 | expect(prop).to.eql('name');
|
101 | });
|
102 |
|
103 | it('should find escaped id column name', function() {
|
104 | const idCol = connector.idColumnEscaped('customer');
|
105 | expect(idCol).to.eql('`NAME`');
|
106 | });
|
107 |
|
108 | it('should find escaped table name', function() {
|
109 | const table = connector.tableEscaped('customer');
|
110 | expect(table).to.eql('`CUSTOMER`');
|
111 | });
|
112 |
|
113 | it('should find escaped column name', function() {
|
114 | const column = connector.columnEscaped('customer', 'vip');
|
115 | expect(column).to.eql('`VIP`');
|
116 | });
|
117 |
|
118 | it('should convert to escaped id column value', function() {
|
119 | const column = connector.idColumnValue('customer', 'John');
|
120 | expect(column).to.eql('John');
|
121 | });
|
122 |
|
123 | it('builds where', function() {
|
124 | const where = connector.buildWhere('customer', {name: 'John'});
|
125 | expect(where.toJSON()).to.eql({
|
126 | sql: 'WHERE `NAME`=?',
|
127 | params: ['John'],
|
128 | });
|
129 | });
|
130 |
|
131 | it('builds where with null', function() {
|
132 | const where = connector.buildWhere('customer', {name: null});
|
133 | expect(where.toJSON()).to.eql({
|
134 | sql: 'WHERE `NAME` IS NULL',
|
135 | params: [],
|
136 | });
|
137 | });
|
138 |
|
139 | it('builds where with inq', function() {
|
140 | const where = connector.buildWhere('customer', {name: {inq: ['John', 'Mary']}});
|
141 | expect(where.toJSON()).to.eql({
|
142 | sql: 'WHERE `NAME` IN (?,?)',
|
143 | params: ['John', 'Mary'],
|
144 | });
|
145 | });
|
146 |
|
147 | it('builds where with or', function() {
|
148 | const where = connector.buildWhere('customer',
|
149 | {or: [{name: 'John'}, {name: 'Mary'}]});
|
150 | expect(where.toJSON()).to.eql({
|
151 | sql: 'WHERE (`NAME`=?) OR (`NAME`=?)',
|
152 | params: ['John', 'Mary'],
|
153 | });
|
154 | });
|
155 |
|
156 | it('builds where with and', function() {
|
157 | const where = connector.buildWhere('customer',
|
158 | {and: [{name: 'John'}, {vip: true}]});
|
159 | expect(where.toJSON()).to.eql({
|
160 | sql: 'WHERE (`NAME`=?) AND (`VIP`=?)',
|
161 | params: ['John', true],
|
162 | });
|
163 | });
|
164 |
|
165 | it('builds where with a regexp string that does not have flags', function() {
|
166 | const where = connector.buildWhere('customer', {
|
167 | name: {
|
168 | regexp: '^J',
|
169 | },
|
170 | });
|
171 | expect(where.toJSON()).to.eql({
|
172 | sql: 'WHERE `NAME` REGEXP ?',
|
173 | params: ['^J'],
|
174 | });
|
175 | });
|
176 |
|
177 | it('builds where with a regexp string that has flags', function() {
|
178 | const where = connector.buildWhere('customer', {
|
179 | name: {
|
180 | regexp: '^J/i',
|
181 | },
|
182 | });
|
183 | expect(where.toJSON()).to.eql({
|
184 | sql: 'WHERE `NAME` REGEXP ?',
|
185 | params: ['^J/i'],
|
186 | });
|
187 | });
|
188 |
|
189 | it('builds where with a regexp literal that does not have flags', function() {
|
190 | const where = connector.buildWhere('customer', {
|
191 | name: {
|
192 | regexp: /^J/,
|
193 | },
|
194 | });
|
195 | expect(where.toJSON()).to.eql({
|
196 | sql: 'WHERE `NAME` REGEXP ?',
|
197 | params: [/^J/],
|
198 | });
|
199 | });
|
200 |
|
201 | it('builds where with a regexp literal that has flags', function() {
|
202 | const where = connector.buildWhere('customer', {
|
203 | name: {
|
204 | regexp: /^J/i,
|
205 | },
|
206 | });
|
207 | expect(where.toJSON()).to.eql({
|
208 | sql: 'WHERE `NAME` REGEXP ?',
|
209 | params: [/^J/i],
|
210 | });
|
211 | });
|
212 |
|
213 | it('builds where with a regexp object that does not have flags', function() {
|
214 | const where = connector.buildWhere('customer', {
|
215 | name: {
|
216 | regexp: new RegExp(/^J/),
|
217 | },
|
218 | });
|
219 | expect(where.toJSON()).to.eql({
|
220 | sql: 'WHERE `NAME` REGEXP ?',
|
221 | params: [/^J/],
|
222 | });
|
223 | });
|
224 |
|
225 | it('builds where with a regexp object that has flags', function() {
|
226 | const where = connector.buildWhere('customer', {
|
227 | name: {
|
228 | regexp: new RegExp(/^J/i),
|
229 | },
|
230 | });
|
231 | expect(where.toJSON()).to.eql({
|
232 | sql: 'WHERE `NAME` REGEXP ?',
|
233 | params: [new RegExp(/^J/i)],
|
234 | });
|
235 | });
|
236 |
|
237 | it('builds where with nesting and/or', function() {
|
238 | const where = connector.buildWhere('customer',
|
239 | {and: [{name: 'John'}, {or: [{vip: true}, {address: null}]}]});
|
240 | expect(where.toJSON()).to.eql({
|
241 | sql: 'WHERE (`NAME`=?) AND ((`VIP`=?) OR (`ADDRESS` IS NULL))',
|
242 | params: ['John', true],
|
243 | });
|
244 | });
|
245 |
|
246 | it('builds where and ignores invalid clauses in or', function() {
|
247 | const where = connector.buildWhere('customer', {
|
248 | name: 'icecream',
|
249 | or: [{notAColumnName: ''}, {notAColumnNameEither: ''}],
|
250 | });
|
251 | expect(where.sql).to.not.match(/ AND $/);
|
252 | });
|
253 |
|
254 | it('builds order by with one field', function() {
|
255 | const orderBy = connector.buildOrderBy('customer', 'name');
|
256 | expect(orderBy).to.eql('ORDER BY `NAME`');
|
257 | });
|
258 |
|
259 | it('builds order by with two fields', function() {
|
260 | const orderBy = connector.buildOrderBy('customer', ['name', 'vip']);
|
261 | expect(orderBy).to.eql('ORDER BY `NAME`,`VIP`');
|
262 | });
|
263 |
|
264 | it('builds order by with two fields and dirs', function() {
|
265 | const orderBy = connector.buildOrderBy('customer', ['name ASC', 'vip DESC']);
|
266 | expect(orderBy).to.eql('ORDER BY `NAME` ASC,`VIP` DESC');
|
267 | });
|
268 |
|
269 | it('builds fields for columns', function() {
|
270 | const fields = connector.buildFields('customer',
|
271 | {name: 'John', vip: true, unknown: 'Random'});
|
272 | expect(fields.names).to.eql(['`NAME`', '`VIP`']);
|
273 | expect(fields.columnValues[0].toJSON()).to.eql(
|
274 | {sql: '?', params: ['John']},
|
275 | );
|
276 | expect(fields.columnValues[1].toJSON()).to.eql(
|
277 | {sql: '?', params: [true]},
|
278 | );
|
279 | });
|
280 |
|
281 | it('builds fields for UPDATE without ids', function() {
|
282 | const fields = connector.buildFieldsForUpdate('customer',
|
283 | {name: 'John', vip: true});
|
284 | expect(fields.toJSON()).to.eql({
|
285 | sql: 'SET `VIP`=?',
|
286 | params: [true],
|
287 | });
|
288 | });
|
289 |
|
290 | it('builds fields for UPDATE with ids', function() {
|
291 | const fields = connector.buildFieldsForUpdate('customer',
|
292 | {name: 'John', vip: true}, false);
|
293 | expect(fields.toJSON()).to.eql({
|
294 | sql: 'SET `NAME`=?,`VIP`=?',
|
295 | params: ['John', true],
|
296 | });
|
297 | });
|
298 |
|
299 | it('builds column names for SELECT', function() {
|
300 | const cols = connector.buildColumnNames('customer');
|
301 | expect(cols).to.eql('`NAME`,`middle_name`,`LASTNAME`,`VIP`,' +
|
302 | '`primary_address`,`ADDRESS`');
|
303 | });
|
304 |
|
305 | it('builds column names with true fields filter for SELECT', function() {
|
306 | const cols = connector.buildColumnNames('customer', {fields: {name: true}});
|
307 | expect(cols).to.eql('`NAME`');
|
308 | });
|
309 |
|
310 | it('builds column names with false fields filter for SELECT', function() {
|
311 | const cols = connector.buildColumnNames('customer', {
|
312 | fields: {
|
313 | name: false,
|
314 | primaryAddress: false,
|
315 | lastName: false,
|
316 | middleName: false,
|
317 | },
|
318 | });
|
319 | expect(cols).to.eql('`VIP`,`ADDRESS`');
|
320 | });
|
321 |
|
322 | it('builds column names with array fields filter for SELECT', function() {
|
323 | const cols = connector.buildColumnNames('customer', {fields: ['name']});
|
324 | expect(cols).to.eql('`NAME`');
|
325 | });
|
326 |
|
327 | it('builds DELETE', function() {
|
328 | const sql = connector.buildDelete('customer', {name: 'John'});
|
329 | expect(sql.toJSON()).to.eql({
|
330 | sql: 'DELETE FROM `CUSTOMER` WHERE `NAME`=$1',
|
331 | params: ['John'],
|
332 | });
|
333 | });
|
334 |
|
335 | it('builds UPDATE', function() {
|
336 | const sql = connector.buildUpdate('customer', {name: 'John'}, {vip: false});
|
337 | expect(sql.toJSON()).to.eql({
|
338 | sql: 'UPDATE `CUSTOMER` SET `VIP`=$1 WHERE `NAME`=$2',
|
339 | params: [false, 'John'],
|
340 | });
|
341 | });
|
342 |
|
343 | it('builds SELECT', function() {
|
344 | const sql = connector.buildSelect('customer',
|
345 | {order: 'name', limit: 5, where: {name: 'John'}});
|
346 | expect(sql.toJSON()).to.eql({
|
347 | sql: 'SELECT `NAME`,`middle_name`,`LASTNAME`,`VIP`,`primary_address`,`ADDRESS`' +
|
348 | ' FROM `CUSTOMER`' +
|
349 | ' WHERE `NAME`=$1 ORDER BY `NAME` LIMIT 5',
|
350 | params: ['John'],
|
351 | });
|
352 | });
|
353 |
|
354 | it('builds INSERT', function() {
|
355 | const sql = connector.buildInsert('customer', {name: 'John', vip: true});
|
356 | expect(sql.toJSON()).to.eql({
|
357 | sql: 'INSERT INTO `CUSTOMER`(`NAME`,`VIP`) VALUES($1,$2)',
|
358 | params: ['John', true],
|
359 | });
|
360 | });
|
361 |
|
362 | it('normalizes a SQL statement from string', function() {
|
363 | const sql = 'SELECT * FROM `CUSTOMER`';
|
364 | const stmt = new ParameterizedSQL(sql);
|
365 | expect(stmt.toJSON()).to.eql({sql: sql, params: []});
|
366 | });
|
367 |
|
368 | it('normalizes a SQL statement from object without params', function() {
|
369 | const sql = {sql: 'SELECT * FROM `CUSTOMER`'};
|
370 | const stmt = new ParameterizedSQL(sql);
|
371 | expect(stmt.toJSON()).to.eql({sql: sql.sql, params: []});
|
372 | });
|
373 |
|
374 | it('normalizes a SQL statement from object with params', function() {
|
375 | const sql =
|
376 | {sql: 'SELECT * FROM `CUSTOMER` WHERE `NAME`=?', params: ['John']};
|
377 | const stmt = new ParameterizedSQL(sql);
|
378 | expect(stmt.toJSON()).to.eql({sql: sql.sql, params: ['John']});
|
379 | });
|
380 |
|
381 | it('should throw if the statement is not a string or object', function() {
|
382 | expect(function() {
|
383 |
|
384 | const stmt = new ParameterizedSQL(true);
|
385 | }).to.throw('sql must be a string');
|
386 | });
|
387 |
|
388 | it('concats SQL statements', function() {
|
389 | let stmt1 = {sql: 'SELECT * from `CUSTOMER`'};
|
390 | const where = {sql: 'WHERE `NAME`=?', params: ['John']};
|
391 | stmt1 = ParameterizedSQL.append(stmt1, where);
|
392 | expect(stmt1.toJSON()).to.eql(
|
393 | {sql: 'SELECT * from `CUSTOMER` WHERE `NAME`=?', params: ['John']},
|
394 | );
|
395 | });
|
396 |
|
397 | it('concats string SQL statements', function() {
|
398 | let stmt1 = 'SELECT * from `CUSTOMER`';
|
399 | const where = {sql: 'WHERE `NAME`=?', params: ['John']};
|
400 | stmt1 = ParameterizedSQL.append(stmt1, where);
|
401 | expect(stmt1.toJSON()).to.eql(
|
402 | {sql: 'SELECT * from `CUSTOMER` WHERE `NAME`=?', params: ['John']},
|
403 | );
|
404 | });
|
405 |
|
406 | it('should throw if params does not match placeholders', function() {
|
407 | expect(function() {
|
408 | let stmt1 = 'SELECT * from `CUSTOMER`';
|
409 | const where = {sql: 'WHERE `NAME`=?', params: ['John', 'Mary']};
|
410 | stmt1 = ParameterizedSQL.append(stmt1, where);
|
411 | }).to.throw('must match the number of params');
|
412 | });
|
413 |
|
414 | it('should allow execute(sql, callback)', function(done) {
|
415 | connector.execute('SELECT * FROM `CUSTOMER`', done);
|
416 | });
|
417 |
|
418 | it('should allow execute(sql, params, callback)', function(done) {
|
419 | connector.execute('SELECT * FROM `CUSTOMER` WHERE `NAME`=$1',
|
420 | ['xyz'], done);
|
421 | });
|
422 |
|
423 | it('should allow execute(sql, params, options, callback)', function(done) {
|
424 | connector.execute('SELECT * FROM `CUSTOMER` WHERE `NAME`=$1',
|
425 | ['xyz'], {transaction: true}, done);
|
426 | });
|
427 |
|
428 | it('should throw if params is not an array for execute()', function() {
|
429 | expect(function() {
|
430 | connector.execute('SELECT * FROM `CUSTOMER`', 'xyz', function() {
|
431 | });
|
432 | }).to.throw('params must be an array');
|
433 | });
|
434 |
|
435 | it('should throw if options is not an object for execute()', function() {
|
436 | expect(function() {
|
437 | connector.execute('SELECT * FROM `CUSTOMER`', [], 'xyz', function() {
|
438 | });
|
439 | }).to.throw('options must be an object');
|
440 | });
|
441 |
|
442 | it('should throw if callback is not a function for execute()', function() {
|
443 | expect(function() {
|
444 | connector.execute('SELECT * FROM `CUSTOMER`', [], {}, 'xyz');
|
445 | }).to.throw('callback must be a function');
|
446 | });
|
447 |
|
448 | it('should invoke hooks', function(done) {
|
449 | const events = [];
|
450 | connector.observe('before execute', function(ctx, next) {
|
451 | expect(ctx.req.sql).be.a('string');
|
452 | expect(ctx.req.params).be.a('array');
|
453 | events.push('before execute');
|
454 | next();
|
455 | });
|
456 | connector.observe('after execute', function(ctx, next) {
|
457 | expect(ctx.res).be.an('array');
|
458 | events.push('after execute');
|
459 | next();
|
460 | });
|
461 | Customer.find(function(err, results) {
|
462 | expect(events).to.eql(['before execute', 'after execute']);
|
463 | done(err, results);
|
464 | });
|
465 | });
|
466 |
|
467 | it('should throw if the event listener limit is reached', function() {
|
468 | ds.connected = false;
|
469 | function runExecute() {
|
470 | return connector.execute('SELECT * FROM `CUSTOMER`', function(err) {
|
471 | throw err;
|
472 | });
|
473 | }
|
474 |
|
475 | for (let i = 0; i < 16; i++) {
|
476 | runExecute();
|
477 | }
|
478 |
|
479 | expect(function() { runExecute(); }).to.throw(
|
480 | 'Event listener limit reached. ' +
|
481 | 'Increase maxOfflineRequests value in datasources.json.',
|
482 | );
|
483 | ds.connected = true;
|
484 | ds.removeAllListeners(['connected']);
|
485 | });
|
486 |
|
487 | it('should not throw if the event listener limit is not reached', function() {
|
488 | ds.connected = false;
|
489 | function runExecute() {
|
490 | return connector.execute('SELECT * FROM `CUSTOMER`', function(err) {
|
491 | throw err;
|
492 | });
|
493 | }
|
494 |
|
495 | for (let i = 0; i < 15; i++) {
|
496 | runExecute();
|
497 | }
|
498 |
|
499 | expect(function() { runExecute(); }).to.not.throw();
|
500 | ds.connected = true;
|
501 | });
|
502 | });
|