|
| 1 | +/* global suite, test */ |
| 2 | +'use strict'; |
| 3 | +var assert = require('assert'); |
| 4 | + |
| 5 | +var sql = require(__dirname + '/../lib').setDialect('postgres'); |
| 6 | + |
| 7 | +var user = sql.define({ |
| 8 | + name: 'user', |
| 9 | + columns: ['id', 'email', 'name'] |
| 10 | +}); |
| 11 | + |
| 12 | +suite('function', function() { |
| 13 | + test('creating function call works', function() { |
| 14 | + var upper = sql.functionCallCreator('UPPER'); |
| 15 | + var functionCall = upper('hello', 'world').toQuery(); |
| 16 | + |
| 17 | + assert.equal(functionCall.text, 'UPPER($1, $2)'); |
| 18 | + assert.equal(functionCall.values[0], 'hello'); |
| 19 | + assert.equal(functionCall.values[1], 'world'); |
| 20 | + }); |
| 21 | + |
| 22 | + test('creating function call on columns works', function() { |
| 23 | + var upper = sql.functionCallCreator('UPPER'); |
| 24 | + var functionCall = upper(user.id, user.email).toQuery(); |
| 25 | + |
| 26 | + assert.equal(functionCall.text, 'UPPER("user"."id", "user"."email")'); |
| 27 | + assert.equal(functionCall.values.length, 0); |
| 28 | + }); |
| 29 | + |
| 30 | + test('function call inside select works', function() { |
| 31 | + var upper = sql.functionCallCreator('UPPER'); |
| 32 | + var query = sql.select(upper(user.id, user.email)).from(user).where(user.email.equals('brian.m.carlson@gmail.com')).toQuery(); |
| 33 | + |
| 34 | + assert.equal(query.text, 'SELECT UPPER("user"."id", "user"."email") FROM "user" WHERE ("user"."email" = $1)'); |
| 35 | + assert.equal(query.values[0], 'brian.m.carlson@gmail.com'); |
| 36 | + }); |
| 37 | + |
| 38 | + test('standard aggregate functions with having clause', function() { |
| 39 | + var count = sql.functions.COUNT; |
| 40 | + var distinct = sql.functions.DISTINCT; |
| 41 | + var distinctEmailCount = count(distinct(user.email)); |
| 42 | + |
| 43 | + var query = user.select(user.id, distinctEmailCount).group(user.id).having(distinctEmailCount.gt(100)).toQuery(); |
| 44 | + |
| 45 | + assert.equal(query.text, 'SELECT "user"."id", COUNT(DISTINCT("user"."email")) FROM "user" GROUP BY "user"."id" HAVING (COUNT(DISTINCT("user"."email")) > $1)'); |
| 46 | + assert.equal(query.values[0], 100); |
| 47 | + }); |
| 48 | + |
| 49 | + test('custom and standard functions behave the same', function() { |
| 50 | + var standardUpper = sql.functions.UPPER; |
| 51 | + var customUpper = sql.functionCallCreator('UPPER'); |
| 52 | + |
| 53 | + var standardQuery = user.select(standardUpper(user.name)).toQuery(); |
| 54 | + var customQuery = user.select(customUpper(user.name)).toQuery(); |
| 55 | + |
| 56 | + var expectedQuery = 'SELECT UPPER("user"."name") FROM "user"'; |
| 57 | + assert.equal(standardQuery.text, expectedQuery); |
| 58 | + assert.equal(customQuery.text, expectedQuery); |
| 59 | + }); |
| 60 | + |
| 61 | + test('combine function with operations', function() { |
| 62 | + var f = sql.functions; |
| 63 | + var query = user.select(f.AVG(f.DISTINCT(f.COUNT(user.id).plus(f.MAX(user.id))).minus(f.MIN(user.id))).multiply(100)).toQuery(); |
| 64 | + |
| 65 | + assert.equal(query.text, 'SELECT (AVG((DISTINCT((COUNT("user"."id") + MAX("user"."id"))) - MIN("user"."id"))) * $1) FROM "user"'); |
| 66 | + assert.equal(query.values[0], 100); |
| 67 | + }); |
| 68 | +}); |
0 commit comments