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