Skip to content

Commit e00acab

Browse files
barryhammenbrianc
barryhammen
authored and
brianc
committed
Add support for basic date functions (brianc#318)
1 parent 297daf3 commit e00acab

6 files changed

Lines changed: 232 additions & 14 deletions

File tree

‎lib/dialect/mssql.js‎

Lines changed: 20 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -246,10 +246,26 @@ Mssql.prototype.visitDrop = function(drop) {
246246

247247
Mssql.prototype.visitFunctionCall = function(functionCall) {
248248
this._visitingFunctionCall = true;
249-
var name=functionCall.name;
250-
// override the LENGTH function since mssql calls it LEN
251-
if (name=="LENGTH") name="LEN";
252-
var txt = name + '(' + functionCall.nodes.map(this.visit.bind(this)).join(', ') + ')';
249+
var _this = this;
250+
251+
function _extract() {
252+
var nodes = functionCall.nodes.map(_this.visit.bind(_this));
253+
if (nodes.length != 1) throw new Error('Not enough parameters passed to ' + functionCall.name + ' function');
254+
var txt = 'DATEPART(' + functionCall.name.toLowerCase() + ', ' + (nodes[0]+'') + ')';
255+
return txt;
256+
}
257+
258+
var txt;
259+
// Override date functions since mssql uses datepart
260+
if (['YEAR', 'MONTH', 'DAY', 'HOUR'].indexOf(functionCall.name) >= 0) txt = _extract();
261+
// Override CURRENT_TIMESTAMP function to remove parens
262+
else if ('CURRENT_TIMESTAMP' == functionCall.name) txt = functionCall.name;
263+
else {
264+
var name = functionCall.name;
265+
// override the LENGTH function since mssql calls it LEN
266+
if (name == "LENGTH") name = "LEN";
267+
txt = name + '(' + functionCall.nodes.map(this.visit.bind(this)).join(', ') + ')';
268+
}
253269
this._visitingFunctionCall = false;
254270
return [txt];
255271
};

‎lib/dialect/mysql.js‎

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -102,4 +102,29 @@ Mysql.prototype.visitBinary = function(binary) {
102102
return Mysql.super_.prototype.visitBinary.call(this, binary);
103103
};
104104

105+
Mysql.prototype.visitFunctionCall = function(functionCall) {
106+
var _this=this;
107+
108+
this._visitingFunctionCall = true;
109+
110+
function _extract() {
111+
var nodes = functionCall.nodes.map(_this.visit.bind(_this));
112+
if (nodes.length != 1) throw new Error('Not enough parameters passed to ' + functionCall.name + ' function');
113+
var txt = functionCall.name + '(' + (nodes[0]+'') + ')';
114+
return txt;
115+
}
116+
117+
var txt="";
118+
var name = functionCall.name;
119+
// Override date functions since mysql is different than postgres
120+
if (['YEAR', 'MONTH', 'DAY', 'HOUR'].indexOf(functionCall.name) >= 0) txt = _extract();
121+
// Override CURRENT_TIMESTAMP function to remove parens
122+
else if ('CURRENT_TIMESTAMP' == functionCall.name) txt = functionCall.name;
123+
else txt = name + '(' + functionCall.nodes.map(this.visit.bind(this)).join(', ') + ')';
124+
125+
this._visitingFunctionCall = false;
126+
return [txt];
127+
};
128+
129+
105130
module.exports = Mysql;

‎lib/dialect/postgres.js‎

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -917,7 +917,21 @@ Postgres.prototype.visitForeignKey = function(foreignKeyNode)
917917

918918
Postgres.prototype.visitFunctionCall = function(functionCall) {
919919
this._visitingFunctionCall = true;
920-
var txt = functionCall.name + '(' + functionCall.nodes.map(this.visit.bind(this)).join(', ') + ')';
920+
var _this = this;
921+
922+
function _extract() {
923+
var nodes = functionCall.nodes.map(_this.visit.bind(_this));
924+
if (nodes.length != 1) throw new Error('Not enough parameters passed to ' + functionCall.name + ' function');
925+
var txt = 'EXTRACT(' + functionCall.name + ' FROM ' + (nodes[0]+'') + ')';
926+
return txt;
927+
}
928+
929+
var txt = "";
930+
// Override date functions since postgres (and others) uses extract
931+
if (['YEAR', 'MONTH', 'DAY', 'HOUR'].indexOf(functionCall.name) >= 0) txt = _extract();
932+
// Override CURRENT_TIMESTAMP function to remove parens
933+
else if ('CURRENT_TIMESTAMP' == functionCall.name) txt = functionCall.name;
934+
else txt = functionCall.name + '(' + functionCall.nodes.map(this.visit.bind(this)).join(', ') + ')';
921935
this._visitingFunctionCall = false;
922936
return [txt];
923937
};

‎lib/dialect/sqlite.js‎

Lines changed: 33 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -42,35 +42,60 @@ Sqlite.prototype.visitDropColumn = function() {
4242
throw new Error('SQLite does not allow dropping columns.');
4343
};
4444

45-
Sqlite.prototype.visitFunctionCall = function(functionCall) {
46-
var _this=this;
45+
Sqlite.prototype.visitFunctionCall = function (functionCall) {
46+
var _this = this;
4747

48-
this._visitingFunctionCall = true;
48+
this._visitingFunctionCall = true;
4949

5050
function _left() {
5151
// convert LEFT(column,4) to SUBSTR(column,1,4)
5252
var nodes = functionCall.nodes.map(_this.visit.bind(_this));
5353
if (nodes.length != 2) throw new Error('Not enough parameters passed to LEFT function.');
54-
var txt = "SUBSTR(" + (nodes[0]+'') + ', 1, ' + (nodes[1]+'') + ')';
54+
var txt = "SUBSTR(" + (nodes[0] + '') + ', 1, ' + (nodes[1] + '') + ')';
5555
return txt;
5656
}
5757

5858
function _right() {
5959
// convert RIGHT(column,4) to SUBSTR(column,-4)
6060
var nodes = functionCall.nodes.map(_this.visit.bind(_this));
6161
if (nodes.length != 2) throw new Error('Not enough parameters passed to RIGHT function.');
62-
var txt = "SUBSTR(" + (nodes[0]+'') + ', -' + (nodes[1]+'') + ')';
62+
var txt = "SUBSTR(" + (nodes[0] + '') + ', -' + (nodes[1] + '') + ')';
6363
return txt;
6464
}
6565

66-
var txt="";
67-
var name=functionCall.name;
66+
function _extract() {
67+
var nodes = functionCall.nodes.map(_this.visit.bind(_this));
68+
if (nodes.length != 1) throw new Error('Not enough parameters passed to ' + functionCall.name + ' function');
69+
var format;
70+
switch (functionCall.name) {
71+
case 'YEAR':
72+
format = "'%Y'";
73+
break;
74+
case 'MONTH':
75+
format = "'%m'";
76+
break;
77+
case 'DAY':
78+
format = "'%d'";
79+
break;
80+
case 'HOUR':
81+
format = "'%H'";
82+
break;
83+
}
84+
var txt = 'strftime(' + format + ', ' + (nodes[0] + '') + ')';
85+
return txt;
86+
}
87+
88+
var txt = "";
89+
var name = functionCall.name;
6890
// Override LEFT and RIGHT and convert to SUBSTR
6991
if (name == "LEFT") txt = _left();
7092
else if (name == "RIGHT") txt = _right();
93+
// Override date functions since sqlite uses strftime
94+
else if (['YEAR', 'MONTH', 'DAY', 'HOUR'].indexOf(functionCall.name) >= 0) txt = _extract();
95+
else if ('CURRENT_TIMESTAMP' == functionCall.name) txt = functionCall.name;
7196
else txt = name + '(' + functionCall.nodes.map(this.visit.bind(this)).join(', ') + ')';
7297

73-
this._visitingFunctionCall = false;
98+
this._visitingFunctionCall = false;
7499
return [txt];
75100
};
76101

‎lib/functions.js‎

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -47,13 +47,21 @@ var scalarFunctions = [
4747
'UPPER'
4848
];
4949

50+
var dateFunctions = [
51+
'YEAR',
52+
'MONTH',
53+
'DAY',
54+
'HOUR',
55+
'CURRENT_TIMESTAMP'
56+
];
57+
5058
// hstore function available to Postgres
5159
var hstoreFunction = 'HSTORE';
5260

5361
//text search functions available to Postgres
5462
var textsearchFunctions = ['TS_RANK','TS_RANK_CD', 'PLAINTO_TSQUERY', 'TO_TSQUERY', 'TO_TSVECTOR', 'SETWEIGHT'];
5563

56-
var standardFunctionNames = aggregateFunctions.concat(scalarFunctions).concat(hstoreFunction).concat(textsearchFunctions);
64+
var standardFunctionNames = aggregateFunctions.concat(scalarFunctions).concat(hstoreFunction).concat(textsearchFunctions).concat(dateFunctions);
5765

5866
// creates a hash of standard functions for a sql instance
5967
var getStandardFunctions = function() {

‎test/dialects/date-tests.js‎

Lines changed: 130 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,130 @@
1+
'use strict';
2+
3+
var Harness = require('./support');
4+
var customer = Harness.defineCustomerTable();
5+
var Sql = require('../../lib');
6+
7+
Harness.test({
8+
query: customer.select(Sql.functions.YEAR(customer.metadata)),
9+
pg: {
10+
text : 'SELECT EXTRACT(YEAR FROM "customer"."metadata") FROM "customer"',
11+
string: 'SELECT EXTRACT(YEAR FROM "customer"."metadata") FROM "customer"'
12+
},
13+
sqlite: {
14+
text : 'SELECT strftime(\'%Y\', "customer"."metadata") FROM "customer"',
15+
string: 'SELECT strftime(\'%Y\', "customer"."metadata") FROM "customer"'
16+
},
17+
mysql: {
18+
text : 'SELECT YEAR(`customer`.`metadata`) FROM `customer`',
19+
string: 'SELECT YEAR(`customer`.`metadata`) FROM `customer`'
20+
},
21+
mssql: {
22+
text : 'SELECT DATEPART(year, [customer].[metadata]) FROM [customer]',
23+
string: 'SELECT DATEPART(year, [customer].[metadata]) FROM [customer]'
24+
},
25+
oracle: {
26+
text : 'SELECT EXTRACT(YEAR FROM "customer"."metadata") FROM "customer"',
27+
string: 'SELECT EXTRACT(YEAR FROM "customer"."metadata") FROM "customer"'
28+
},
29+
params: []
30+
});
31+
32+
Harness.test({
33+
query: customer.select(Sql.functions.MONTH(customer.metadata)),
34+
pg: {
35+
text : 'SELECT EXTRACT(MONTH FROM "customer"."metadata") FROM "customer"',
36+
string: 'SELECT EXTRACT(MONTH FROM "customer"."metadata") FROM "customer"'
37+
},
38+
sqlite: {
39+
text : 'SELECT strftime(\'%m\', "customer"."metadata") FROM "customer"',
40+
string: 'SELECT strftime(\'%m\', "customer"."metadata") FROM "customer"'
41+
},
42+
mysql: {
43+
text : 'SELECT MONTH(`customer`.`metadata`) FROM `customer`',
44+
string: 'SELECT MONTH(`customer`.`metadata`) FROM `customer`'
45+
},
46+
mssql: {
47+
text : 'SELECT DATEPART(month, [customer].[metadata]) FROM [customer]',
48+
string: 'SELECT DATEPART(month, [customer].[metadata]) FROM [customer]'
49+
},
50+
oracle: {
51+
text : 'SELECT EXTRACT(MONTH FROM "customer"."metadata") FROM "customer"',
52+
string: 'SELECT EXTRACT(MONTH FROM "customer"."metadata") FROM "customer"'
53+
},
54+
params: []
55+
});
56+
57+
Harness.test({
58+
query: customer.select(Sql.functions.DAY(customer.metadata)),
59+
pg: {
60+
text : 'SELECT EXTRACT(DAY FROM "customer"."metadata") FROM "customer"',
61+
string: 'SELECT EXTRACT(DAY FROM "customer"."metadata") FROM "customer"'
62+
},
63+
sqlite: {
64+
text : 'SELECT strftime(\'%d\', "customer"."metadata") FROM "customer"',
65+
string: 'SELECT strftime(\'%d\', "customer"."metadata") FROM "customer"'
66+
},
67+
mysql: {
68+
text : 'SELECT DAY(`customer`.`metadata`) FROM `customer`',
69+
string: 'SELECT DAY(`customer`.`metadata`) FROM `customer`'
70+
},
71+
mssql: {
72+
text : 'SELECT DATEPART(day, [customer].[metadata]) FROM [customer]',
73+
string: 'SELECT DATEPART(day, [customer].[metadata]) FROM [customer]'
74+
},
75+
oracle: {
76+
text : 'SELECT EXTRACT(DAY FROM "customer"."metadata") FROM "customer"',
77+
string: 'SELECT EXTRACT(DAY FROM "customer"."metadata") FROM "customer"'
78+
},
79+
params: []
80+
});
81+
82+
Harness.test({
83+
query: customer.select(Sql.functions.HOUR(customer.metadata)),
84+
pg: {
85+
text : 'SELECT EXTRACT(HOUR FROM "customer"."metadata") FROM "customer"',
86+
string: 'SELECT EXTRACT(HOUR FROM "customer"."metadata") FROM "customer"'
87+
},
88+
sqlite: {
89+
text : 'SELECT strftime(\'%H\', "customer"."metadata") FROM "customer"',
90+
string: 'SELECT strftime(\'%H\', "customer"."metadata") FROM "customer"'
91+
},
92+
mysql: {
93+
text : 'SELECT HOUR(`customer`.`metadata`) FROM `customer`',
94+
string: 'SELECT HOUR(`customer`.`metadata`) FROM `customer`'
95+
},
96+
mssql: {
97+
text : 'SELECT DATEPART(hour, [customer].[metadata]) FROM [customer]',
98+
string: 'SELECT DATEPART(hour, [customer].[metadata]) FROM [customer]'
99+
},
100+
oracle: {
101+
text : 'SELECT EXTRACT(HOUR FROM "customer"."metadata") FROM "customer"',
102+
string: 'SELECT EXTRACT(HOUR FROM "customer"."metadata") FROM "customer"'
103+
},
104+
params: []
105+
});
106+
107+
Harness.test({
108+
query: customer.select(Sql.functions.CURRENT_TIMESTAMP()),
109+
pg: {
110+
text : 'SELECT CURRENT_TIMESTAMP FROM "customer"',
111+
string: 'SELECT CURRENT_TIMESTAMP FROM "customer"'
112+
},
113+
sqlite: {
114+
text : 'SELECT CURRENT_TIMESTAMP FROM "customer"',
115+
string: 'SELECT CURRENT_TIMESTAMP FROM "customer"'
116+
},
117+
mysql: {
118+
text : 'SELECT CURRENT_TIMESTAMP FROM `customer`',
119+
string: 'SELECT CURRENT_TIMESTAMP FROM `customer`'
120+
},
121+
mssql: {
122+
text : 'SELECT CURRENT_TIMESTAMP FROM [customer]',
123+
string: 'SELECT CURRENT_TIMESTAMP FROM [customer]'
124+
},
125+
oracle: {
126+
text : 'SELECT CURRENT_TIMESTAMP FROM "customer"',
127+
string: 'SELECT CURRENT_TIMESTAMP FROM "customer"'
128+
},
129+
params: []
130+
});

0 commit comments

Comments
 (0)